statistics.lib.php 35 KB

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