statistics.lib.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458
  1. <?php
  2. // $Id: index.php 8216 2006-11-3 18:03:15 NushiFirefox $
  3. /*
  4. ==============================================================================
  5. Dokeos - elearning and course management software
  6. Copyright (c) 2006 Bart Mollet <bart.mollet@hogent.be>
  7. For a full list of contributors, see "credits.txt".
  8. The full license can be read in "license.txt".
  9. This program is free software; you can redistribute it and/or
  10. modify it under the terms of the GNU General Public License
  11. as published by the Free Software Foundation; either version 2
  12. of the License, or (at your option) any later version.
  13. See the GNU General Public License for more details.
  14. Contact: Dokeos, 181 rue Royale, B-1000 Brussels, Belgium, info@dokeos.com
  15. ==============================================================================
  16. */
  17. require_once (api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php');
  18. /**
  19. ==============================================================================
  20. * This class provides some functions for statistics
  21. * @package dokeos.statistics
  22. ==============================================================================
  23. */
  24. class Statistics
  25. {
  26. /**
  27. * Converts a number of bytes in a formatted string
  28. * @param int $size
  29. * @return string Formatted file size
  30. */
  31. function make_size_string($size) {
  32. if ($size < pow(2,10)) return $size." bytes";
  33. if ($size >= pow(2,10) && $size < pow(2,20)) return round($size / pow(2,10), 0)." KB";
  34. if ($size >= pow(2,20) && $size < pow(2,30)) return round($size / pow(2,20), 1)." MB";
  35. if ($size > pow(2,30)) return round($size / pow(2,30), 2)." GB";
  36. }
  37. /**
  38. * Count courses
  39. * @param string $category_code Code of a course category. Default: count
  40. * all courses.
  41. * @return int Number of courses counted
  42. */
  43. function count_courses($category_code = NULL)
  44. {
  45. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  46. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." ";
  47. if (isset ($category_code))
  48. {
  49. $sql .= " WHERE category_code = '".Database::escape_string($category_code)."'";
  50. }
  51. $res = Database::query($sql, __FILE__, __LINE__);
  52. $obj = Database::fetch_object($res);
  53. return $obj->number;
  54. }
  55. /**
  56. * Count users
  57. * @param int $status COURSEMANAGER or STUDENT
  58. * @param string $category_code Code of a course category. Default: count
  59. * all users.
  60. * @return int Number of users counted
  61. */
  62. function count_users($status, $category_code = NULL, $count_invisible_courses = true)
  63. {
  64. // Database table definitions
  65. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  66. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  67. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  68. $sql = "SELECT COUNT(DISTINCT(user_id)) AS number FROM $user_table WHERE status = ".intval(Database::escape_string($status))." ";
  69. if (isset ($category_code))
  70. {
  71. $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number FROM $course_user_table cu, $course_table c WHERE cu.status = ".intval(Database::escape_string($status))." AND c.code = cu.course_code AND c.category_code = '".Database::escape_string($category_code)."'";
  72. }
  73. $res = Database::query($sql, __FILE__, __LINE__);
  74. $obj = Database::fetch_object($res);
  75. return $obj->number;
  76. }
  77. /**
  78. * Count activities from track_e_default_table
  79. * @return int Number of activities counted
  80. */
  81. function get_number_of_activities()
  82. {
  83. // Database table definitions
  84. $track_e_default = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  85. $sql = "SELECT count(default_id) AS total_number_of_items FROM $track_e_default, $table_user user WHERE default_user_id = user.user_id ";
  86. if (isset($_GET['keyword'])) {
  87. $keyword = Database::escape_string($_GET['keyword']);
  88. $sql .= " AND (user.username LIKE '%".$keyword."%' OR default_event_type LIKE '%".$keyword."%' OR default_value_type LIKE '%".$keyword."%' OR default_value LIKE '%".$keyword."%') ";
  89. }
  90. $res = Database::query($sql, __FILE__, __LINE__);
  91. $obj = Database::fetch_object($res);
  92. return $obj->total_number_of_items;
  93. }
  94. /**
  95. * Get activities data to display
  96. */
  97. function get_activities_data($from, $number_of_items, $column, $direction)
  98. {
  99. global $dateTimeFormatLong;
  100. $track_e_default = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  101. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  102. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  103. $sql = "SELECT
  104. default_event_type as col0,
  105. default_value_type as col1,
  106. default_value as col2,
  107. user.username as col3,
  108. default_date as col4
  109. FROM $track_e_default track_default, $table_user user
  110. WHERE track_default.default_user_id = user.user_id ";
  111. if (isset($_GET['keyword'])) {
  112. $keyword = Database::escape_string($_GET['keyword']);
  113. $sql .= " AND (user.username LIKE '%".$keyword."%' OR default_event_type LIKE '%".$keyword."%' OR default_value_type LIKE '%".$keyword."%' OR default_value LIKE '%".$keyword."%') ";
  114. }
  115. if (!empty($column) && !empty($direction)) {
  116. $sql .= " ORDER BY col$column $direction";
  117. } else {
  118. $sql .= " ORDER BY col4 DESC ";
  119. }
  120. $sql .= " LIMIT $from,$number_of_items ";
  121. $res = Database::query($sql, __FILE__, __LINE__);
  122. $activities = array ();
  123. while ($row = Database::fetch_row($res)) {
  124. $row[4] = api_format_date(DATE_TIME_FORMAT_LONG, strtotime($row[4]));
  125. $activities[] = $row;
  126. }
  127. return $activities;
  128. }
  129. /**
  130. * Get all course categories
  131. * @return array All course categories (code => name)
  132. */
  133. function get_course_categories()
  134. {
  135. $category_table = Database :: get_main_table(TABLE_MAIN_CATEGORY);
  136. $sql = "SELECT * FROM $category_table ORDER BY tree_pos";
  137. $res = Database::query($sql, __FILE__, __LINE__);
  138. $categories = array ();
  139. while ($category = Database::fetch_object($res))
  140. {
  141. $categories[$category->code] = $category->name;
  142. }
  143. return $categories;
  144. }
  145. /**
  146. * Rescale data
  147. * @param array $data The data that should be rescaled
  148. * @param int $max The maximum value in the rescaled data (default = 500);
  149. * @return array The rescaled data, same key as $data
  150. */
  151. function rescale($data, $max = 500)
  152. {
  153. $data_max = 1;
  154. foreach ($data as $index => $value)
  155. {
  156. $data_max = ($data_max < $value ? $value : $data_max);
  157. }
  158. reset($data);
  159. $result = array ();
  160. $delta = $max / $data_max;
  161. foreach ($data as $index => $value)
  162. {
  163. $result[$index] = (int) round($value * $delta);
  164. }
  165. return $result;
  166. }
  167. /**
  168. * Show statistics
  169. * @param string $title The title
  170. * @param array $stats
  171. * @param bool $show_total
  172. * @param bool $is_file_size
  173. */
  174. function print_stats($title, $stats, $show_total = true, $is_file_size = false)
  175. {
  176. $total = 0;
  177. $data = Statistics::rescale($stats);
  178. echo '<table class="data_table" cellspacing="0" cellpadding="3">
  179. <tr><th colspan="'.($show_total ? '4' : '3').'">'.$title.'</th></tr>';
  180. $i = 0;
  181. foreach($stats as $subtitle => $number)
  182. {
  183. $total += $number;
  184. }
  185. foreach ($stats as $subtitle => $number)
  186. {
  187. $i = $i % 13;
  188. if (api_strlen($subtitle) > 30)
  189. {
  190. $subtitle = '<acronym title="'.$subtitle.'">'.api_substr($subtitle, 0, 27).'...</acronym>';
  191. }
  192. if(!$is_file_size)
  193. {
  194. $number_label = number_format($number, 0, ',', '.');
  195. }
  196. else
  197. {
  198. $number_label = Statistics::make_size_string($number);
  199. }
  200. echo '<tr class="row_'.($i%2 == 0 ? 'odd' : 'even').'">
  201. <td width="150">'.$subtitle.'</td>
  202. <td width="550">
  203. '.Display::return_icon('bar_1u.gif', get_lang('Statistics') ,array('width' => $data[$subtitle], 'height' => '10')).'
  204. </td>
  205. <td align="right">'.$number_label.'</td>';
  206. if($show_total)
  207. {
  208. echo '<td align="right"> '.($total>0?number_format(100*$number/$total, 1, ',', '.'):'0').'%</td>';
  209. }
  210. echo '</tr>';
  211. $i ++;
  212. }
  213. if ($show_total)
  214. {
  215. if(!$is_file_size)
  216. {
  217. $total_label = number_format($total, 0, ',', '.');
  218. }
  219. else
  220. {
  221. $total_label = Statistics::make_size_string($total);
  222. }
  223. echo '<tr><th colspan="4" align="right">'.get_lang('Total').': '.$total_label.'</td></tr>';
  224. }
  225. echo '</table>';
  226. }
  227. /**
  228. * Show some stats about the number of logins
  229. * @param string $type month, hour or day
  230. */
  231. function print_login_stats($type)
  232. {
  233. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  234. switch($type)
  235. {
  236. case 'month':
  237. $months = api_get_months_long();
  238. $period = get_lang('PeriodMonth');
  239. $sql = "SELECT DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table." GROUP BY stat_date ORDER BY login_date ";
  240. break;
  241. case 'hour':
  242. $period = get_lang('PeriodHour');
  243. $sql = "SELECT DATE_FORMAT( login_date, '%H' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table." GROUP BY stat_date ORDER BY stat_date ";
  244. break;
  245. case 'day':
  246. $week_days = api_get_week_days_long();
  247. $period = get_lang('PeriodDay');
  248. $sql = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , count( login_id ) AS number_of_logins FROM ".$table." GROUP BY stat_date ORDER BY DATE_FORMAT( login_date, '%w' ) ";
  249. break;
  250. }
  251. $res = Database::query($sql,__FILE__,__LINE__);
  252. $result = array();
  253. while($obj = Database::fetch_object($res))
  254. {
  255. $stat_date = $obj->stat_date;
  256. switch($type)
  257. {
  258. case 'month':
  259. $stat_date = explode('-', $stat_date);
  260. $stat_date[1] = $months[$stat_date[1] - 1];
  261. $stat_date = implode(' ', $stat_date);
  262. break;
  263. case 'day':
  264. $stat_date = $week_days[$stat_date];
  265. break;
  266. }
  267. $result[$stat_date] = $obj->number_of_logins;
  268. }
  269. Statistics::print_stats(get_lang('Logins').' ('.$period.')', $result, true);
  270. }
  271. /**
  272. * Print the number of recent logins
  273. */
  274. function print_recent_login_stats()
  275. {
  276. $total_logins = array();
  277. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  278. $sql[get_lang('Thisday')] = "SELECT count(login_user_id) AS number FROM $table WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW()";
  279. $sql[get_lang('Last7days')] = "SELECT count(login_user_id) AS number FROM $table WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW()";
  280. $sql[get_lang('Last31days')] = "SELECT count(login_user_id) AS number FROM $table WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW()";
  281. $sql[get_lang('Total')] = "SELECT count(login_user_id) AS number FROM $table";
  282. foreach($sql as $index => $query)
  283. {
  284. $res = Database::query($query,__FILE__,__LINE__);
  285. $obj = Database::fetch_object($res);
  286. $total_logins[$index] = $obj->number;
  287. }
  288. Statistics::print_stats(get_lang('Logins'),$total_logins,false);
  289. }
  290. /**
  291. * Show some stats about the accesses to the different course tools
  292. */
  293. function print_tool_stats()
  294. {
  295. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  296. $tools = array('announcement','assignment','calendar_event','chat','conference','course_description','document','dropbox','group','learnpath','link','quiz','student_publication','user','forum');
  297. $tool_names = array();
  298. foreach ($tools as $tool) {
  299. $tool_names[$tool] = get_lang(ucfirst($tool), '');
  300. }
  301. $sql = "SELECT access_tool, count( access_id ) AS number_of_logins FROM $table WHERE access_tool IN ('".implode("','",$tools)."') GROUP BY access_tool ";
  302. $res = Database::query($sql,__FILE__,__LINE__);
  303. $result = array();
  304. while($obj = Database::fetch_object($res))
  305. {
  306. $result[$tool_names[$obj->access_tool]] = $obj->number_of_logins;
  307. }
  308. Statistics::print_stats(get_lang('PlatformToolAccess'),$result,true);
  309. }
  310. /**
  311. * Show some stats about the number of courses per language
  312. */
  313. function print_course_by_language_stats()
  314. {
  315. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  316. $sql = "SELECT course_language, count( code ) AS number_of_courses FROM $table GROUP BY course_language ";
  317. $res = Database::query($sql,__FILE__,__LINE__);
  318. $result = array();
  319. while($obj = Database::fetch_object($res))
  320. {
  321. $result[$obj->course_language] = $obj->number_of_courses;
  322. }
  323. Statistics::print_stats(get_lang('CountCourseByLanguage'),$result,true);
  324. }
  325. /**
  326. * Shows the number of users having their picture uploaded in Dokeos.
  327. */
  328. function print_user_pictures_stats()
  329. {
  330. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  331. $sql = "SELECT COUNT(*) AS n FROM $user_table";
  332. $res = Database::query($sql,__FILE__,__LINE__);
  333. $count1 = Database::fetch_object($res);
  334. $sql = "SELECT COUNT(*) AS n FROM $user_table WHERE LENGTH(picture_uri) > 0";
  335. $res = Database::query($sql,__FILE__,__LINE__);
  336. $count2 = Database::fetch_object($res);
  337. $result[get_lang('No')] = $count1->n - $count2->n; // #users without picture
  338. $result[get_lang('Yes')] = $count2->n; // #users with picture
  339. Statistics::print_stats(get_lang('CountUsers').' ('.get_lang('UserPicture').')',$result,true);
  340. }
  341. function print_activities_stats() {
  342. echo '<h4>'.get_lang('ImportantActivities').'</h4>';
  343. // Create a search-box
  344. $form = new FormValidator('search_simple','get',api_get_path(WEB_CODE_PATH).'admin/statistics/index.php?action=activities','','width=200px',false);
  345. $renderer =& $form->defaultRenderer();
  346. $renderer->setElementTemplate('<span>{element}</span> ');
  347. $form->addElement('hidden','action','activities');
  348. $form->addElement('hidden','activities_direction','DESC');
  349. $form->addElement('hidden','activities_column','4');
  350. $form->addElement('text','keyword',get_lang('keyword'));
  351. $form->addElement('style_submit_button', 'submit', get_lang('SearchActivities'),'class="search"');
  352. echo '<div class="actions">';
  353. $form->display();
  354. echo '</div>';
  355. $table = new SortableTable('activities', array('Statistics','get_number_of_activities'), array('Statistics','get_activities_data'),4,50,'DESC');
  356. $parameters = array();
  357. $parameters['action'] = 'activities';
  358. if (isset($_GET['keyword'])) {
  359. $parameters['keyword'] = Security::remove_XSS($_GET['keyword']);
  360. }
  361. $table->set_additional_parameters($parameters);
  362. $table->set_header(0, get_lang('EventType'));
  363. $table->set_header(1, get_lang('DataType'));
  364. $table->set_header(2, get_lang('Value'));
  365. $table->set_header(3, get_lang('UserName'));
  366. $table->set_header(4, get_lang('Date'));
  367. $table->display();
  368. }
  369. /**
  370. * Shows statistics about the time of last visit to each course.
  371. */
  372. function print_course_last_visit()
  373. {
  374. $columns[0] = 'access_cours_code';
  375. $columns[1] = 'access_date';
  376. $sql_order[SORT_ASC] = 'ASC';
  377. $sql_order[SORT_DESC] = 'DESC';
  378. $per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 10;
  379. $page_nr = isset($_GET['page_nr']) ? intval($_GET['page_nr']) : 1;
  380. $column = isset($_GET['column']) ? intval($_GET['column']) : 0;
  381. $date_diff = isset($_GET['date_diff'])? intval($_GET['date_diff']) : 60;
  382. if(!in_array($_GET['direction'],array(SORT_ASC,SORT_DESC))){
  383. $direction = SORT_ASC;
  384. } else {
  385. $direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC;
  386. }
  387. $form = new FormValidator('courselastvisit','get');
  388. $form->addElement('hidden','action','courselastvisit');
  389. $form->add_textfield('date_diff',get_lang('Days'),true);
  390. $form->addRule('date_diff','InvalidNumber','numeric');
  391. $form->addElement('submit','ok',get_lang('Ok'));
  392. $defaults['date_diff'] = 60;
  393. $form->setDefaults($defaults);
  394. if($form->validate()) {
  395. $form->display();
  396. $values = $form->exportValues();
  397. $date_diff = $values['date_diff'];
  398. $table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  399. $sql = "SELECT * FROM $table GROUP BY access_cours_code HAVING access_cours_code <> '' AND DATEDIFF( NOW() , access_date ) >= ". $date_diff;
  400. $res = Database::query($sql,__FILE__,__LINE__);
  401. $number_of_courses = Database::num_rows($res);
  402. $sql .= ' ORDER BY '.$columns[$column].' '.$sql_order[$direction];
  403. $from = ($page_nr -1) * $per_page;
  404. $sql .= ' LIMIT '.$from.','.$per_page;
  405. echo '<p>'.get_lang('LastAccess').' &gt;= '.$date_diff.' '.get_lang('Days').'</p>';
  406. $res = Database::query($sql, __FILE__, __LINE__);
  407. if (Database::num_rows($res) > 0)
  408. {
  409. $courses = array ();
  410. while ($obj = Database::fetch_object($res))
  411. {
  412. $course = array ();
  413. $course[]= '<a href="'.api_get_path(WEB_PATH).'courses/'.$obj->access_cours_code.'">'.$obj->access_cours_code.' <a>';
  414. $course[] = $obj->access_date;
  415. $courses[] = $course;
  416. }
  417. $parameters['action'] = 'courselastvisit';
  418. $parameters['date_diff'] = $date_diff;
  419. $table_header[] = array ("Coursecode", true);
  420. $table_header[] = array ("Last login", true);
  421. Display :: display_sortable_table($table_header, $courses, array ('column'=>$column,'direction'=>$direction), array (), $parameters);
  422. }
  423. else
  424. {
  425. echo get_lang('NoSearchResults');
  426. }
  427. }
  428. else
  429. {
  430. $form->display();
  431. }
  432. }
  433. }
  434. ?>