statistics.lib.php 36 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This class provides some functions for statistics
  5. * @package chamilo.statistics
  6. */
  7. class Statistics
  8. {
  9. /**
  10. * Converts a number of bytes in a formatted string
  11. * @param int $size
  12. * @return string Formatted file size
  13. */
  14. static function make_size_string($size)
  15. {
  16. if ($size < pow(2,10)) return $size." bytes";
  17. if ($size >= pow(2,10) && $size < pow(2,20)) return round($size / pow(2,10), 0)." KB";
  18. if ($size >= pow(2,20) && $size < pow(2,30)) return round($size / pow(2,20), 1)." MB";
  19. if ($size > pow(2,30)) return round($size / pow(2,30), 2)." GB";
  20. }
  21. /**
  22. * Count courses
  23. * @param string $category_code Code of a course category. Default: count
  24. * all courses.
  25. * @return int Number of courses counted
  26. */
  27. static function count_courses($category_code = NULL)
  28. {
  29. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  30. $access_url_rel_course_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  31. $current_url_id = api_get_current_access_url_id();
  32. if (api_is_multiple_url_enabled()) {
  33. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." as c, ".$access_url_rel_course_table." as u WHERE u.course_code=c.code AND access_url_id='".$current_url_id."'";
  34. if (isset ($category_code)) {
  35. $sql .= " AND category_code = '".Database::escape_string($category_code)."'";
  36. }
  37. } else {
  38. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." ";
  39. if (isset ($category_code)) {
  40. $sql .= " WHERE category_code = '".Database::escape_string($category_code)."'";
  41. }
  42. }
  43. $res = Database::query($sql);
  44. $obj = Database::fetch_object($res);
  45. return $obj->number;
  46. }
  47. /**
  48. * Count courses by visibility
  49. * @param int Visibility (0 = closed, 1 = private, 2 = open, 3 = public)
  50. * all courses.
  51. * @return int Number of courses counted
  52. */
  53. static function count_courses_by_visibility($vis = null)
  54. {
  55. if (!isset($vis)) {
  56. return 0;
  57. }
  58. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  59. $access_url_rel_course_table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  60. $current_url_id = api_get_current_access_url_id();
  61. if (api_is_multiple_url_enabled()) {
  62. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." as c, ".$access_url_rel_course_table." as u
  63. WHERE u.course_code=c.code AND access_url_id='".$current_url_id."'";
  64. if (isset ($vis)) {
  65. $sql .= " AND visibility = ".intval($vis);
  66. }
  67. } else {
  68. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." ";
  69. if (isset ($vis)) {
  70. $sql .= " WHERE visibility = ".intval($vis);
  71. }
  72. }
  73. $res = Database::query($sql);
  74. $obj = Database::fetch_object($res);
  75. return $obj->number;
  76. }
  77. /**
  78. * Count users
  79. * @param int optional, user status (COURSEMANAGER or STUDENT), if it's not setted it'll count all users.
  80. * @param string optional, code of a course category. Default: count only users without filtering category
  81. * @param bool count invisible courses (todo)
  82. * @param bool count only active users (false to only return currently active users)
  83. * @return int Number of users counted
  84. */
  85. static function count_users($status = null, $category_code = null, $count_invisible_courses = true, $only_active = false)
  86. {
  87. // Database table definitions
  88. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  89. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  90. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  91. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  92. $current_url_id = api_get_current_access_url_id();
  93. $active_filter = $only_active?' AND active=1':'';
  94. $status_filter = isset($status)?' AND status = '.intval($status):'';
  95. if (api_is_multiple_url_enabled()) {
  96. $sql = "SELECT COUNT(DISTINCT(u.user_id)) AS number FROM $user_table as u, $access_url_rel_user_table as url WHERE u.user_id=url.user_id AND access_url_id='".$current_url_id."' $status_filter $active_filter";
  97. if (isset ($category_code)) {
  98. $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number FROM $course_user_table cu, $course_table c, $access_url_rel_user_table as url WHERE c.code = cu.course_code AND c.category_code = '".Database::escape_string($category_code)."' AND cu.user_id=url.user_id AND access_url_id='".$current_url_id."' $status_filter $active_filter";
  99. }
  100. } else {
  101. $sql = "SELECT COUNT(DISTINCT(user_id)) AS number FROM $user_table WHERE 1=1 $status_filter $active_filter";
  102. if (isset ($category_code)) {
  103. $status_filter = isset($status)?' AND status = '.intval($status):'';
  104. $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number FROM $course_user_table cu, $course_table c WHERE c.code = cu.course_code AND c.category_code = '".Database::escape_string($category_code)."' $status_filter $active_filter";
  105. }
  106. }
  107. $res = Database::query($sql);
  108. $obj = Database::fetch_object($res);
  109. return $obj->number;
  110. }
  111. /**
  112. * Count sessions
  113. * @return int Number of sessions counted
  114. */
  115. static function count_sessions()
  116. {
  117. $session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  118. $access_url_rel_session_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  119. if (api_is_multiple_url_enabled()) {
  120. $current_url_id = api_get_current_access_url_id();
  121. $sql = "SELECT COUNT(id) AS number FROM ".$session_table." as s, ".$access_url_rel_session_table." as u WHERE u.session_id=s.id AND access_url_id='".$current_url_id."'";
  122. } else {
  123. $sql = "SELECT COUNT(id) AS number FROM ".$session_table." ";
  124. }
  125. $res = Database::query($sql);
  126. $obj = Database::fetch_object($res);
  127. return $obj->number;
  128. }
  129. /**
  130. * Count activities from track_e_default_table
  131. * @return int Number of activities counted
  132. */
  133. static function get_number_of_activities()
  134. {
  135. // Database table definitions
  136. global $_configuration;
  137. $track_e_default = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  138. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  139. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  140. $current_url_id = api_get_current_access_url_id();
  141. if ($_configuration['multiple_access_urls']) {
  142. $sql = "SELECT count(default_id) AS total_number_of_items FROM $track_e_default, $table_user user, $access_url_rel_user_table url WHERE default_user_id = user.user_id AND user.user_id=url.user_id AND access_url_id='".$current_url_id."'";
  143. } else {
  144. $sql = "SELECT count(default_id) AS total_number_of_items FROM $track_e_default, $table_user user WHERE default_user_id = user.user_id ";
  145. }
  146. if (isset($_GET['keyword'])) {
  147. $keyword = Database::escape_string(trim($_GET['keyword']));
  148. $sql .= " AND (user.username LIKE '%".$keyword."%' OR default_event_type LIKE '%".$keyword."%' OR default_value_type LIKE '%".$keyword."%' OR default_value LIKE '%".$keyword."%') ";
  149. }
  150. $res = Database::query($sql);
  151. $obj = Database::fetch_object($res);
  152. return $obj->total_number_of_items;
  153. }
  154. /**
  155. * Get activities data to display
  156. * @param int $from
  157. * @param int $number_of_items
  158. * @param int $column
  159. * @param string $direction
  160. * @return array
  161. */
  162. static function get_activities_data($from, $number_of_items, $column, $direction)
  163. {
  164. global $dateTimeFormatLong;
  165. $track_e_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  166. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  167. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  168. $current_url_id = api_get_current_access_url_id();
  169. $column = intval($column);
  170. $from = intval($from);
  171. $number_of_items = intval($number_of_items);
  172. if (!in_array($direction, array('ASC','DESC'))) {
  173. $direction = 'DESC';
  174. }
  175. if (api_is_multiple_url_enabled()) {
  176. $sql = "SELECT ".
  177. "default_event_type as col0, ".
  178. "default_value_type as col1, ".
  179. "default_value as col2, ".
  180. "user.username as col3, ".
  181. "user.user_id as col4, ".
  182. "default_date as col5 ".
  183. "FROM $track_e_default as track_default, $table_user as user, $access_url_rel_user_table as url
  184. WHERE track_default.default_user_id = user.user_id AND
  185. url.user_id = user.user_id AND
  186. access_url_id='".$current_url_id."'";
  187. } else {
  188. $sql = "SELECT ".
  189. "default_event_type as col0, ".
  190. "default_value_type as col1, ".
  191. "default_value as col2, ".
  192. "user.username as col3, ".
  193. "user.user_id as col4, ".
  194. "default_date as col5 ".
  195. "FROM $track_e_default track_default, $table_user user ".
  196. "WHERE track_default.default_user_id = user.user_id ";
  197. }
  198. if (isset($_GET['keyword'])) {
  199. $keyword = Database::escape_string(trim($_GET['keyword']));
  200. $sql .= " AND (user.username LIKE '%".$keyword."%' OR
  201. default_event_type LIKE '%".$keyword."%' OR
  202. default_value_type LIKE '%".$keyword."%' OR
  203. default_value LIKE '%".$keyword."%') ";
  204. }
  205. if (!empty($column) && !empty($direction)) {
  206. $sql .= " ORDER BY col$column $direction";
  207. } else {
  208. $sql .= " ORDER BY col5 DESC ";
  209. }
  210. $sql .= " LIMIT $from,$number_of_items ";
  211. $res = Database::query($sql);
  212. $activities = array ();
  213. while ($row = Database::fetch_row($res)) {
  214. if (strpos($row[1], '_object') === false && strpos($row[1], '_array') === false) {
  215. $row[2] = $row[2];
  216. } else {
  217. if (!empty($row[2])) {
  218. $originalData = $row[2];
  219. $row[2] = unserialize($originalData);
  220. if (is_array($row[2]) && !empty($row[2])) {
  221. $row[2] = implode_with_key(', ', $row[2]);
  222. } else {
  223. $row[2] = $originalData;
  224. }
  225. }
  226. }
  227. if (!empty($row['default_date']) && $row['default_date'] != '0000-00-00 00:00:00') {
  228. $row['default_date'] = api_get_local_time($row['default_date']);
  229. } else {
  230. $row['default_date'] = '-';
  231. }
  232. if (!empty($row[4])) {
  233. // User id.
  234. $row[3] = Display::url(
  235. $row[3],
  236. api_get_path(WEB_CODE_PATH).'admin/user_information?user_id='.$row[4], array('title' => get_lang('UserInfo'))
  237. );
  238. $row[4] = TrackingUserLog::get_ip_from_user_event($row[4], $row[5], true);
  239. if (empty($row[4])) {
  240. $row[4] = get_lang('Unknown');
  241. }
  242. }
  243. $activities[] = $row;
  244. }
  245. return $activities;
  246. }
  247. /**
  248. * Get all course categories
  249. * @return array All course categories (code => name)
  250. */
  251. static function get_course_categories()
  252. {
  253. $category_table = Database :: get_main_table(TABLE_MAIN_CATEGORY);
  254. $sql = "SELECT code, name FROM $category_table ORDER BY tree_pos";
  255. $res = Database::query($sql);
  256. $categories = array ();
  257. while ($category = Database::fetch_object($res)) {
  258. $categories[$category->code] = $category->name;
  259. }
  260. return $categories;
  261. }
  262. /**
  263. * Rescale data
  264. * @param array $data The data that should be rescaled
  265. * @param int $max The maximum value in the rescaled data (default = 500);
  266. * @return array The rescaled data, same key as $data
  267. */
  268. static function rescale($data, $max = 500) {
  269. $data_max = 1;
  270. foreach ($data as $index => $value) {
  271. $data_max = ($data_max < $value ? $value : $data_max);
  272. }
  273. reset($data);
  274. $result = array ();
  275. $delta = $max / $data_max;
  276. foreach ($data as $index => $value) {
  277. $result[$index] = (int) round($value * $delta);
  278. }
  279. return $result;
  280. }
  281. /**
  282. * Show statistics
  283. * @param string $title The title
  284. * @param array $stats
  285. * @param bool $show_total
  286. * @param bool $is_file_size
  287. */
  288. static function print_stats($title, $stats, $show_total = true, $is_file_size = false) {
  289. $total = 0;
  290. $data = Statistics::rescale($stats);
  291. echo '<table class="data_table" cellspacing="0" cellpadding="3">
  292. <tr><th colspan="'.($show_total ? '4' : '3').'">'.$title.'</th></tr>';
  293. $i = 0;
  294. foreach ($stats as $subtitle => $number) {
  295. $total += $number;
  296. }
  297. foreach ($stats as $subtitle => $number) {
  298. if (!$is_file_size) {
  299. $number_label = number_format($number, 0, ',', '.');
  300. } else {
  301. $number_label = Statistics::make_size_string($number);
  302. }
  303. $percentage = ($total>0?number_format(100*$number/$total, 1, ',', '.'):'0');
  304. echo '<tr class="row_'.($i%2 == 0 ? 'odd' : 'even').'">
  305. <td width="150">'.$subtitle.'</td>
  306. <td width="550">'.Display::bar_progress($percentage, false).'</td>
  307. <td align="right">'.$number_label.'</td>';
  308. if ($show_total) {
  309. echo '<td align="right"> '.$percentage.'%</td>';
  310. }
  311. echo '</tr>';
  312. $i ++;
  313. }
  314. if ($show_total) {
  315. if (!$is_file_size) {
  316. $total_label = number_format($total, 0, ',', '.');
  317. } else {
  318. $total_label = Statistics::make_size_string($total);
  319. }
  320. echo '<tr><th colspan="4" align="right">'.get_lang('Total').': '.$total_label.'</td></tr>';
  321. }
  322. echo '</table>';
  323. }
  324. /**
  325. * Show some stats about the number of logins
  326. * @param string $type month, hour or day
  327. */
  328. static function print_login_stats($type)
  329. {
  330. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  331. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  332. $current_url_id = api_get_current_access_url_id();
  333. if (api_is_multiple_url_enabled()) {
  334. $table_url = ", $access_url_rel_user_table";
  335. $where_url = " WHERE login_user_id=user_id AND access_url_id='".$current_url_id."'";
  336. $where_url_last = ' AND login_date > DATE_SUB(NOW(),INTERVAL 1 %s)';
  337. } else {
  338. $table_url = '';
  339. $where_url = '';
  340. $where_url_last = ' WHERE login_date > DATE_SUB(NOW(),INTERVAL 1 %s)';
  341. }
  342. switch ($type) {
  343. case 'month':
  344. $months = api_get_months_long();
  345. $period = get_lang('PeriodMonth');
  346. $sql = "SELECT DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url." GROUP BY stat_date ORDER BY login_date ";
  347. $sql_last_x = "SELECT DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url.sprintf($where_url_last,'YEAR')." GROUP BY stat_date ORDER BY login_date ";
  348. break;
  349. case 'hour':
  350. $period = get_lang('PeriodHour');
  351. $sql = "SELECT DATE_FORMAT( login_date, '%H' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url." GROUP BY stat_date ORDER BY stat_date ";
  352. $sql_last_x = "SELECT DATE_FORMAT( login_date, '%H' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url.sprintf($where_url_last,'DAY')." GROUP BY stat_date ORDER BY stat_date ";
  353. break;
  354. case 'day':
  355. $week_days = api_get_week_days_long();
  356. $period = get_lang('PeriodDay');
  357. $sql = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url." GROUP BY stat_date ORDER BY DATE_FORMAT( login_date, '%w' ) ";
  358. $sql_last_x = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table.$table_url.$where_url.sprintf($where_url_last,'WEEK')." GROUP BY stat_date ORDER BY DATE_FORMAT( login_date, '%w' ) ";
  359. break;
  360. }
  361. $res_last_x = Database::query($sql_last_x);
  362. $result_last_x = array();
  363. while ($obj = Database::fetch_object($res_last_x)) {
  364. $stat_date = $obj->stat_date;
  365. switch ($type) {
  366. case 'month':
  367. $stat_date = explode('-', $stat_date);
  368. $stat_date[1] = $months[$stat_date[1] - 1];
  369. $stat_date = implode(' ', $stat_date);
  370. break;
  371. case 'day':
  372. $stat_date = $week_days[$stat_date];
  373. break;
  374. }
  375. $result_last_x[$stat_date] = $obj->number_of_logins;
  376. }
  377. Statistics::print_stats(get_lang('LastLogins').' ('.$period.')', $result_last_x, true);
  378. flush(); //flush web request at this point to see something already while the full data set is loading
  379. echo '<br />';
  380. $res = Database::query($sql);
  381. $result = array();
  382. while ($obj = Database::fetch_object($res)) {
  383. $stat_date = $obj->stat_date;
  384. switch ($type) {
  385. case 'month':
  386. $stat_date = explode('-', $stat_date);
  387. $stat_date[1] = $months[$stat_date[1] - 1];
  388. $stat_date = implode(' ', $stat_date);
  389. break;
  390. case 'day':
  391. $stat_date = $week_days[$stat_date];
  392. break;
  393. }
  394. $result[$stat_date] = $obj->number_of_logins;
  395. }
  396. Statistics::print_stats(get_lang('AllLogins').' ('.$period.')', $result, true);
  397. }
  398. /**
  399. * Print the number of recent logins
  400. */
  401. static function print_recent_login_stats()
  402. {
  403. $total_logins = array();
  404. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  405. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  406. $current_url_id = api_get_current_access_url_id();
  407. if (api_is_multiple_url_enabled()) {
  408. $table_url = ", $access_url_rel_user_table";
  409. $where_url = " AND login_user_id=user_id AND access_url_id='".$current_url_id."'";
  410. } else {
  411. $table_url = '';
  412. $where_url='';
  413. }
  414. $sql[get_lang('Thisday')] = "SELECT count(login_user_id) AS number FROM $table $table_url WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() $where_url";
  415. $sql[get_lang('Last7days')] = "SELECT count(login_user_id) AS number FROM $table $table_url WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() $where_url";
  416. $sql[get_lang('Last31days')] = "SELECT count(login_user_id) AS number FROM $table $table_url WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() $where_url";
  417. $sql[get_lang('Total')] = "SELECT count(login_user_id) AS number FROM $table $table_url WHERE 1=1 $where_url";
  418. foreach ($sql as $index => $query) {
  419. $res = Database::query($query);
  420. $obj = Database::fetch_object($res);
  421. $total_logins[$index] = $obj->number;
  422. }
  423. Statistics::print_stats(get_lang('Logins'),$total_logins,false);
  424. }
  425. /**
  426. * Show some stats about the accesses to the different course tools
  427. */
  428. static function print_tool_stats()
  429. {
  430. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  431. $access_url_rel_course_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  432. $current_url_id = api_get_current_access_url_id();
  433. $tools = array('announcement','assignment','calendar_event',
  434. 'chat','conference','course_description','document',
  435. 'dropbox','group','learnpath','link','quiz',
  436. 'student_publication','user','forum');
  437. $tool_names = array();
  438. foreach ($tools as $tool) {
  439. $tool_names[$tool] = get_lang(ucfirst($tool), '');
  440. }
  441. if (api_is_multiple_url_enabled()) {
  442. $sql = "SELECT access_tool, count( access_id ) ".
  443. "AS number_of_logins FROM $table, $access_url_rel_course_table ".
  444. "WHERE access_tool IN ('".implode("','",$tools)."') AND course_code = access_cours_code AND access_url_id='".$current_url_id."' ".
  445. "GROUP BY access_tool ";
  446. } else {
  447. $sql = "SELECT access_tool, count( access_id ) ".
  448. "AS number_of_logins FROM $table ".
  449. "WHERE access_tool IN ('".implode("','",$tools)."') ".
  450. "GROUP BY access_tool ";
  451. }
  452. $res = Database::query($sql);
  453. $result = array();
  454. while ($obj = Database::fetch_object($res)) {
  455. $result[$tool_names[$obj->access_tool]] = $obj->number_of_logins;
  456. }
  457. Statistics::print_stats(get_lang('PlatformToolAccess'),$result,true);
  458. }
  459. /**
  460. * Show some stats about the number of courses per language
  461. */
  462. static function print_course_by_language_stats()
  463. {
  464. $table = Database :: get_main_table(TABLE_MAIN_COURSE);
  465. $access_url_rel_course_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  466. $current_url_id = api_get_current_access_url_id();
  467. if (api_is_multiple_url_enabled()) {
  468. $sql = "SELECT course_language, count( c.code ) AS number_of_courses ".
  469. "FROM $table as c, $access_url_rel_course_table as u
  470. WHERE u.course_code=c.code AND access_url_id='".$current_url_id."'
  471. GROUP BY course_language ORDER BY number_of_courses DESC";
  472. } else {
  473. $sql = "SELECT course_language, count( code ) AS number_of_courses ".
  474. "FROM $table GROUP BY course_language ORDER BY number_of_courses DESC";
  475. }
  476. $res = Database::query($sql);
  477. $result = array();
  478. while ($obj = Database::fetch_object($res)) {
  479. $result[$obj->course_language] = $obj->number_of_courses;
  480. }
  481. Statistics::print_stats(get_lang('CountCourseByLanguage'),$result,true);
  482. }
  483. /**
  484. * Shows the number of users having their picture uploaded in Dokeos.
  485. */
  486. static function print_user_pictures_stats()
  487. {
  488. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  489. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  490. $current_url_id = api_get_current_access_url_id();
  491. if (api_is_multiple_url_enabled()) {
  492. $url_condition = ", $access_url_rel_user_table as url WHERE url.user_id=u.user_id AND access_url_id='".$current_url_id."'";
  493. $url_condition2 = " AND url.user_id=u.user_id AND access_url_id='".$current_url_id."'";
  494. $table = ", $access_url_rel_user_table as url ";
  495. }
  496. $sql = "SELECT COUNT(*) AS n FROM $user_table as u ".$url_condition;
  497. $res = Database::query($sql);
  498. $count1 = Database::fetch_object($res);
  499. $sql = "SELECT COUNT(*) AS n FROM $user_table as u $table ".
  500. "WHERE LENGTH(picture_uri) > 0 $url_condition2";
  501. $res = Database::query($sql);
  502. $count2 = Database::fetch_object($res);
  503. // #users without picture
  504. $result[get_lang('No')] = $count1->n - $count2->n;
  505. $result[get_lang('Yes')] = $count2->n; // #users with picture
  506. Statistics::print_stats(get_lang('CountUsers').' ('.get_lang('UserPicture').')',$result,true);
  507. }
  508. /**
  509. * Important activities
  510. */
  511. static function print_activities_stats()
  512. {
  513. echo '<h4>'.get_lang('ImportantActivities').'</h4>';
  514. // Create a search-box
  515. $form = new FormValidator('search_simple','get',api_get_path(WEB_CODE_PATH).'admin/statistics/index.php','','width=200px',false);
  516. $renderer =& $form->defaultRenderer();
  517. $renderer->setElementTemplate('<span>{element}</span> ');
  518. $form->addElement('hidden','report','activities');
  519. $form->addElement('hidden','activities_direction','DESC');
  520. $form->addElement('hidden','activities_column','4');
  521. $form->addElement('text','keyword',get_lang('keyword'));
  522. $form->addElement('style_submit_button', 'submit', get_lang('Search'),'class="search"');
  523. echo '<div class="actions">';
  524. $form->display();
  525. echo '</div>';
  526. $table = new SortableTable('activities', array('Statistics','get_number_of_activities'), array('Statistics','get_activities_data'),5,50,'DESC');
  527. $parameters = array();
  528. $parameters['report'] = 'activities';
  529. if (isset($_GET['keyword'])) {
  530. $parameters['keyword'] = Security::remove_XSS($_GET['keyword']);
  531. }
  532. $table->set_additional_parameters($parameters);
  533. $table->set_header(0, get_lang('EventType'));
  534. $table->set_header(1, get_lang('DataType'));
  535. $table->set_header(2, get_lang('Value'));
  536. $table->set_header(3, get_lang('UserName'));
  537. $table->set_header(4, get_lang('IPAddress'));
  538. $table->set_header(5, get_lang('Date'));
  539. $table->display();
  540. }
  541. /**
  542. * Shows statistics about the time of last visit to each course.
  543. */
  544. static function print_course_last_visit()
  545. {
  546. $access_url_rel_course_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  547. $current_url_id = api_get_current_access_url_id();
  548. $columns[0] = 'access_cours_code';
  549. $columns[1] = 'access_date';
  550. $sql_order[SORT_ASC] = 'ASC';
  551. $sql_order[SORT_DESC] = 'DESC';
  552. $per_page = isset($_GET['per_page'])?intval($_GET['per_page']) : 10;
  553. $page_nr = isset($_GET['page_nr'])?intval($_GET['page_nr']) : 1;
  554. $column = isset($_GET['column'])?intval($_GET['column']) : 0;
  555. $date_diff = isset($_GET['date_diff'])?intval($_GET['date_diff']) : 60;
  556. if (!in_array($_GET['direction'],array(SORT_ASC,SORT_DESC))) {
  557. $direction = SORT_ASC;
  558. } else {
  559. $direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC;
  560. }
  561. $form = new FormValidator('courselastvisit', 'get');
  562. $form->addElement('hidden','report','courselastvisit');
  563. $form->add_textfield('date_diff',get_lang('Days'),true);
  564. $form->addRule('date_diff','InvalidNumber','numeric');
  565. $form->addElement('style_submit_button', 'submit', get_lang('Search'),'class="search"');
  566. if (!isset($_GET['date_diff'])) {
  567. $defaults['date_diff'] = 60;
  568. } else {
  569. $defaults['date_diff'] = Security::remove_XSS($_GET['date_diff']);
  570. }
  571. $form->setDefaults($defaults);
  572. $form->display();
  573. $values = $form->exportValues();
  574. $date_diff = $values['date_diff'];
  575. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  576. if (api_is_multiple_url_enabled()) {
  577. $sql = "SELECT * FROM $table, $access_url_rel_course_table WHERE course_code = access_cours_code AND access_url_id='".$current_url_id."' ".
  578. "GROUP BY access_cours_code ".
  579. "HAVING access_cours_code <> '' ".
  580. "AND DATEDIFF( '".date('Y-m-d h:i:s')."' , access_date ) <= ". $date_diff;
  581. } else {
  582. $sql = "SELECT * FROM $table ".
  583. "GROUP BY access_cours_code ".
  584. "HAVING access_cours_code <> '' ".
  585. "AND DATEDIFF( '".date('Y-m-d h:i:s')."' , access_date ) <= ". $date_diff;
  586. }
  587. $res = Database::query($sql);
  588. $number_of_courses = Database::num_rows($res);
  589. $sql .= ' ORDER BY '.$columns[$column].' '.$sql_order[$direction];
  590. $from = ($page_nr -1) * $per_page;
  591. $sql .= ' LIMIT '.$from.','.$per_page;
  592. echo '<p>'.get_lang('LastAccess').' &gt;= '.$date_diff.' '.get_lang('Days').'</p>';
  593. $res = Database::query($sql);
  594. if (Database::num_rows($res) > 0) {
  595. $courses = array ();
  596. while ($obj = Database::fetch_object($res)) {
  597. $course = array ();
  598. $course[]= '<a href="'.api_get_path(WEB_PATH).'courses/'.$obj->access_cours_code.'">'.$obj->access_cours_code.' <a>';
  599. //Allow sort by date hiding the numerical date
  600. $course[] = '<span style="display:none;">'.$obj->access_date.'</span>'.api_convert_and_format_date($obj->access_date);
  601. $courses[] = $course;
  602. }
  603. $parameters['date_diff'] = $date_diff;
  604. $parameters['report'] = 'courselastvisit';
  605. $table_header[] = array (get_lang("CourseCode"), true);
  606. $table_header[] = array (get_lang("LastAccess"), true);
  607. Display :: display_sortable_table($table_header, $courses, array ('column'=>$column,'direction'=>$direction), array (), $parameters);
  608. } else {
  609. echo get_lang('NoSearchResults');
  610. }
  611. }
  612. /**
  613. * Displays the statistics of the messages sent and received by each user in the social network
  614. * @param string Type of message: 'sent' or 'received'
  615. * @return array Message list
  616. */
  617. static function get_messages($message_type)
  618. {
  619. $message_table = Database::get_main_table(TABLE_MAIN_MESSAGE);
  620. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  621. $access_url_rel_user_table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  622. $current_url_id = api_get_current_access_url_id();
  623. switch ($message_type) {
  624. case 'sent':
  625. $field = 'user_sender_id';
  626. break;
  627. case 'received':
  628. $field = 'user_receiver_id';
  629. break;
  630. }
  631. if (api_is_multiple_url_enabled()) {
  632. $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message ".
  633. "FROM ".$access_url_rel_user_table." as url, ".$message_table." m ".
  634. "LEFT JOIN ".$user_table." u ON m.$field = u.user_id ".
  635. "WHERE url.user_id = m.$field AND access_url_id='".$current_url_id."' ".
  636. "GROUP BY m.$field ORDER BY count_message DESC ";
  637. } else {
  638. $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message ".
  639. "FROM ".$message_table." m ".
  640. "LEFT JOIN ".$user_table." u ON m.$field = u.user_id ".
  641. "GROUP BY m.$field ORDER BY count_message DESC ";
  642. }
  643. $res = Database::query($sql);
  644. $messages_sent = array();
  645. while ($messages = Database::fetch_array($res)) {
  646. if (empty($messages['username'])) {
  647. $messages['username'] = get_lang('Unknown');
  648. }
  649. $users = api_get_person_name($messages['firstname'], $messages['lastname']).'<br />('.$messages['username'].')';
  650. $messages_sent[$users] = $messages['count_message'];
  651. }
  652. return $messages_sent;
  653. }
  654. /**
  655. * Count the number of friends for social network users
  656. */
  657. static function get_friends()
  658. {
  659. $user_friend_table = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
  660. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  661. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  662. $current_url_id = api_get_current_access_url_id();
  663. if (api_is_multiple_url_enabled()) {
  664. $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend ".
  665. "FROM ".$access_url_rel_user_table." as url, ".$user_friend_table." uf ".
  666. "LEFT JOIN ".$user_table." u ON uf.user_id = u.user_id ".
  667. "WHERE uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' AND uf.user_id = url.user_id AND access_url_id='".$current_url_id."' ".
  668. "GROUP BY uf.user_id ORDER BY count_friend DESC ";
  669. } else {
  670. $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend ".
  671. "FROM ".$user_friend_table." uf ".
  672. "LEFT JOIN ".$user_table." u ON uf.user_id = u.user_id ".
  673. "WHERE uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' ".
  674. "GROUP BY uf.user_id ORDER BY count_friend DESC ";
  675. }
  676. $res = Database::query($sql);
  677. $list_friends = array();
  678. while ($friends = Database::fetch_array($res)) {
  679. $users = api_get_person_name($friends['firstname'], $friends['lastname']).'<br />('.$friends['username'].')';
  680. $list_friends[$users] = $friends['count_friend'];
  681. }
  682. return $list_friends;
  683. }
  684. /**
  685. * Print the number of users that didn't login for a certain period of time
  686. */
  687. static function print_users_not_logged_in_stats()
  688. {
  689. $total_logins = array();
  690. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  691. $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  692. $current_url_id = api_get_current_access_url_id();
  693. $total = self::count_users();
  694. if (api_is_multiple_url_enabled()) {
  695. $table_url = ", $access_url_rel_user_table";
  696. $where_url = " AND login_user_id=user_id AND access_url_id='".$current_url_id."'";
  697. } else {
  698. $table_url = '';
  699. $where_url='';
  700. }
  701. $sql[get_lang('Thisday')] =
  702. "SELECT count(distinct(login_user_id)) AS number ".
  703. " FROM $table $table_url ".
  704. " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() $where_url";
  705. $sql[get_lang('Last7days')] =
  706. "SELECT count(distinct(login_user_id)) AS number ".
  707. " FROM $table $table_url ".
  708. " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() $where_url";
  709. $sql[get_lang('Last31days')] =
  710. "SELECT count(distinct(login_user_id)) AS number ".
  711. " FROM $table $table_url ".
  712. " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() $where_url";
  713. $sql[sprintf(get_lang('LastXMonths'),6)] =
  714. "SELECT count(distinct(login_user_id)) AS number ".
  715. " FROM $table $table_url ".
  716. " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= NOW() $where_url";
  717. $sql[get_lang('NeverConnected')] =
  718. "SELECT count(distinct(login_user_id)) AS number ".
  719. " FROM $table $table_url WHERE 1=1 $where_url";
  720. foreach ($sql as $index => $query) {
  721. $res = Database::query($query);
  722. $obj = Database::fetch_object($res);
  723. $r = $total - $obj->number;
  724. $total_logins[$index] = $r < 0 ? 0 : $r;
  725. }
  726. Statistics::print_stats(get_lang('StatsUsersDidNotLoginInLastPeriods'),$total_logins,false);
  727. }
  728. }