statistics.lib.php 33 KB

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