statistics.lib.php 35 KB

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