statistics.lib.php 41 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This class provides some functions for statistics
  5. * @package chamilo.statistics
  6. */
  7. class Statistics
  8. {
  9. /**
  10. * Converts a number of bytes in a formatted string
  11. * @param int $size
  12. * @return string Formatted file size
  13. */
  14. public static function makeSizeString($size)
  15. {
  16. if ($size < pow(2, 10)) {
  17. return $size." bytes";
  18. }
  19. if ($size >= pow(2, 10) && $size < pow(2, 20)) {
  20. return round($size / pow(2, 10), 0)." KB";
  21. }
  22. if ($size >= pow(2, 20) && $size < pow(2, 30)) {
  23. return round($size / pow(2, 20), 1)." MB";
  24. }
  25. if ($size > pow(2, 30)) {
  26. return round($size / pow(2, 30), 2)." GB";
  27. }
  28. }
  29. /**
  30. * Count courses
  31. * @param string $categoryCode Code of a course category.
  32. * Default: count all courses.
  33. * @return int Number of courses counted
  34. */
  35. public static function countCourses($categoryCode = null)
  36. {
  37. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  38. $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  39. $current_url_id = api_get_current_access_url_id();
  40. if (api_is_multiple_url_enabled()) {
  41. $sql = "SELECT COUNT(*) AS number
  42. FROM ".$course_table." as c, $access_url_rel_course_table as u
  43. WHERE u.c_id = c.id AND access_url_id='".$current_url_id."'";
  44. if (isset($categoryCode)) {
  45. $sql .= " AND category_code = '".Database::escape_string($categoryCode)."'";
  46. }
  47. } else {
  48. $sql = "SELECT COUNT(*) AS number
  49. FROM $course_table";
  50. if (isset($categoryCode)) {
  51. $sql .= " WHERE category_code = '".Database::escape_string($categoryCode)."'";
  52. }
  53. }
  54. $res = Database::query($sql);
  55. $obj = Database::fetch_object($res);
  56. return $obj->number;
  57. }
  58. /**
  59. * Count courses by visibility
  60. * @param int $visibility Visibility (0 = closed, 1 = private, 2 = open, 3 = public) all courses.
  61. * @return int Number of courses counted
  62. */
  63. public static function countCoursesByVisibility($visibility = null)
  64. {
  65. if (!isset($visibility)) {
  66. return 0;
  67. }
  68. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  69. $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  70. $current_url_id = api_get_current_access_url_id();
  71. if (api_is_multiple_url_enabled()) {
  72. $sql = "SELECT COUNT(*) AS number
  73. FROM $course_table as c, $access_url_rel_course_table as u
  74. WHERE u.c_id = c.id AND access_url_id='".$current_url_id."'";
  75. if (isset($visibility)) {
  76. $sql .= " AND visibility = ".intval($visibility);
  77. }
  78. } else {
  79. $sql = "SELECT COUNT(*) AS number FROM ".$course_table." ";
  80. if (isset($visibility)) {
  81. $sql .= " WHERE visibility = ".intval($visibility);
  82. }
  83. }
  84. $res = Database::query($sql);
  85. $obj = Database::fetch_object($res);
  86. return $obj->number;
  87. }
  88. /**
  89. * Count users
  90. * @param int $status Optional user status (COURSEMANAGER or STUDENT), if it's not setted it'll count all users.
  91. * @param string $categoryCode Optional, code of a course category. Default: count only users without filtering category
  92. * @param bool $countInvisibleCourses Count invisible courses (todo)
  93. * @param bool $onlyActive Count only active users (false to only return currently active users)
  94. * @return int Number of users counted
  95. */
  96. public static function countUsers(
  97. $status = null,
  98. $categoryCode = null,
  99. $countInvisibleCourses = true,
  100. $onlyActive = false
  101. ) {
  102. // Database table definitions
  103. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  104. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  105. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  106. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  107. $current_url_id = api_get_current_access_url_id();
  108. $active_filter = $onlyActive ? ' AND active=1' : '';
  109. $status_filter = isset($status) ? ' AND status = '.intval($status) : '';
  110. if (api_is_multiple_url_enabled()) {
  111. $sql = "SELECT COUNT(DISTINCT(u.user_id)) AS number
  112. FROM $user_table as u, $access_url_rel_user_table as url
  113. WHERE
  114. u.user_id = url.user_id AND
  115. access_url_id = '".$current_url_id."'
  116. $status_filter $active_filter";
  117. if (isset($categoryCode)) {
  118. $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number
  119. FROM $course_user_table cu, $course_table c, $access_url_rel_user_table as url
  120. WHERE
  121. c.id = cu.c_id AND
  122. c.category_code = '".Database::escape_string($categoryCode)."' AND
  123. cu.user_id = url.user_id AND
  124. access_url_id='".$current_url_id."'
  125. $status_filter $active_filter";
  126. }
  127. } else {
  128. $sql = "SELECT COUNT(DISTINCT(user_id)) AS number
  129. FROM $user_table
  130. WHERE 1=1 $status_filter $active_filter";
  131. if (isset($categoryCode)) {
  132. $status_filter = isset($status) ? ' AND status = '.intval($status) : '';
  133. $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number
  134. FROM $course_user_table cu, $course_table c
  135. WHERE
  136. c.id = cu.c_id AND
  137. c.category_code = '".Database::escape_string($categoryCode)."'
  138. $status_filter
  139. $active_filter
  140. ";
  141. }
  142. }
  143. $res = Database::query($sql);
  144. $obj = Database::fetch_object($res);
  145. return $obj->number;
  146. }
  147. /**
  148. * Count sessions
  149. * @return int Number of sessions counted
  150. */
  151. public static function countSessions()
  152. {
  153. $session_table = Database::get_main_table(TABLE_MAIN_SESSION);
  154. $access_url_rel_session_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  155. if (api_is_multiple_url_enabled()) {
  156. $current_url_id = api_get_current_access_url_id();
  157. $sql = "SELECT COUNT(id) AS number
  158. FROM $session_table as s, $access_url_rel_session_table as u
  159. WHERE
  160. u.session_id = s.id AND
  161. access_url_id = '".$current_url_id."'";
  162. } else {
  163. $sql = "SELECT COUNT(id) AS number
  164. FROM $session_table";
  165. }
  166. $res = Database::query($sql);
  167. $obj = Database::fetch_object($res);
  168. return $obj->number;
  169. }
  170. /**
  171. * Count activities from track_e_default_table
  172. * @return int Number of activities counted
  173. */
  174. public static function getNumberOfActivities()
  175. {
  176. // Database table definitions
  177. $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  178. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  179. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  180. $current_url_id = api_get_current_access_url_id();
  181. if (api_is_multiple_url_enabled()) {
  182. $sql = "SELECT count(default_id) AS total_number_of_items
  183. FROM $track_e_default, $table_user user, $access_url_rel_user_table url
  184. WHERE
  185. default_user_id = user.user_id AND
  186. user.user_id=url.user_id AND
  187. access_url_id = '".$current_url_id."'";
  188. } else {
  189. $sql = "SELECT count(default_id) AS total_number_of_items
  190. FROM $track_e_default, $table_user user
  191. WHERE default_user_id = user.user_id ";
  192. }
  193. if (isset($_GET['keyword'])) {
  194. $keyword = Database::escape_string(trim($_GET['keyword']));
  195. $sql .= " AND (
  196. user.username LIKE '%".$keyword."%' OR
  197. default_event_type LIKE '%".$keyword."%' OR
  198. default_value_type LIKE '%".$keyword."%' OR
  199. default_value LIKE '%".$keyword."%') ";
  200. }
  201. $res = Database::query($sql);
  202. $obj = Database::fetch_object($res);
  203. return $obj->total_number_of_items;
  204. }
  205. /**
  206. * Get activities data to display
  207. * @param int $from
  208. * @param int $numberOfItems
  209. * @param int $column
  210. * @param string $direction
  211. * @return array
  212. */
  213. public static function getActivitiesData(
  214. $from,
  215. $numberOfItems,
  216. $column,
  217. $direction
  218. ) {
  219. $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  220. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  221. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  222. $current_url_id = api_get_current_access_url_id();
  223. $column = intval($column);
  224. $from = intval($from);
  225. $numberOfItems = intval($numberOfItems);
  226. if (!in_array($direction, array('ASC', 'DESC'))) {
  227. $direction = 'DESC';
  228. }
  229. if (api_is_multiple_url_enabled()) {
  230. $sql = "SELECT
  231. default_event_type as col0,
  232. default_value_type as col1,
  233. default_value as col2,
  234. c_id as col3,
  235. session_id as col4,
  236. user.username as col5,
  237. user.user_id as col6,
  238. default_date as col7
  239. FROM $track_e_default as track_default,
  240. $table_user as user,
  241. $access_url_rel_user_table as url
  242. WHERE
  243. track_default.default_user_id = user.user_id AND
  244. url.user_id = user.user_id AND
  245. access_url_id= $current_url_id ";
  246. } else {
  247. $sql = "SELECT
  248. default_event_type as col0,
  249. default_value_type as col1,
  250. default_value as col2,
  251. c_id as col3,
  252. session_id as col4,
  253. user.username as col5,
  254. user.user_id as col6,
  255. default_date as col7
  256. FROM $track_e_default track_default, $table_user user
  257. WHERE track_default.default_user_id = user.user_id ";
  258. }
  259. if (isset($_GET['keyword'])) {
  260. $keyword = Database::escape_string(trim($_GET['keyword']));
  261. $sql .= " AND (user.username LIKE '%".$keyword."%' OR
  262. default_event_type LIKE '%".$keyword."%' OR
  263. default_value_type LIKE '%".$keyword."%' OR
  264. default_value LIKE '%".$keyword."%') ";
  265. }
  266. if (!empty($column) && !empty($direction)) {
  267. $sql .= " ORDER BY col$column $direction";
  268. } else {
  269. $sql .= " ORDER BY col5 DESC ";
  270. }
  271. $sql .= " LIMIT $from, $numberOfItems ";
  272. $res = Database::query($sql);
  273. $activities = array();
  274. while ($row = Database::fetch_row($res)) {
  275. if (strpos($row[1], '_object') === false &&
  276. strpos($row[1], '_array') === false
  277. ) {
  278. $row[2] = $row[2];
  279. } else {
  280. if (!empty($row[2])) {
  281. $originalData = str_replace('\\', '', $row[2]);
  282. $row[2] = unserialize($originalData);
  283. if (is_array($row[2]) && !empty($row[2])) {
  284. $row[2] = implode_with_key(', ', $row[2]);
  285. } else {
  286. $row[2] = $originalData;
  287. }
  288. }
  289. }
  290. if (!empty($row['default_date'])) {
  291. $row['default_date'] = api_get_local_time($row['default_date']);
  292. } else {
  293. $row['default_date'] = '-';
  294. }
  295. if (!empty($row[5])) {
  296. //course
  297. if (!empty($row[3])) {
  298. $row[3] = Display::url($row[3], api_get_path(WEB_CODE_PATH).'admin/course_edit.php?id='.$row[3]);
  299. } else {
  300. $row[3] = '-';
  301. }
  302. // session
  303. if (!empty($row[4])) {
  304. $row[4] = Display::url(
  305. $row[4],
  306. api_get_path(WEB_CODE_PATH).'session/resume_session.php?id_session='.$row[4]
  307. );
  308. } else {
  309. $row[4] = '-';
  310. }
  311. // User id.
  312. $row[5] = Display::url(
  313. $row[5],
  314. api_get_path(WEB_CODE_PATH).'admin/user_information.php?user_id='.$row[6],
  315. array('title' => get_lang('UserInfo'))
  316. );
  317. $row[6] = Tracking::get_ip_from_user_event(
  318. $row[6],
  319. $row[7],
  320. true
  321. );
  322. if (empty($row[6])) {
  323. $row[6] = get_lang('Unknown');
  324. }
  325. }
  326. $activities[] = $row;
  327. }
  328. return $activities;
  329. }
  330. /**
  331. * Get all course categories
  332. * @return array All course categories (code => name)
  333. */
  334. public static function getCourseCategories()
  335. {
  336. $categoryTable = Database::get_main_table(TABLE_MAIN_CATEGORY);
  337. $sql = "SELECT code, name
  338. FROM $categoryTable
  339. ORDER BY tree_pos";
  340. $res = Database::query($sql);
  341. $categories = array();
  342. while ($category = Database::fetch_object($res)) {
  343. $categories[$category->code] = $category->name;
  344. }
  345. return $categories;
  346. }
  347. /**
  348. * Rescale data
  349. * @param array $data The data that should be rescaled
  350. * @param int $max The maximum value in the rescaled data (default = 500);
  351. * @return array The rescaled data, same key as $data
  352. */
  353. public static function rescale($data, $max = 500)
  354. {
  355. $data_max = 1;
  356. foreach ($data as $index => $value) {
  357. $data_max = ($data_max < $value ? $value : $data_max);
  358. }
  359. reset($data);
  360. $result = array();
  361. $delta = $max / $data_max;
  362. foreach ($data as $index => $value) {
  363. $result[$index] = (int) round($value * $delta);
  364. }
  365. return $result;
  366. }
  367. /**
  368. * Show statistics
  369. * @param string $title The title
  370. * @param array $stats
  371. * @param bool $showTotal
  372. * @param bool $isFileSize
  373. */
  374. public static function printStats(
  375. $title,
  376. $stats,
  377. $showTotal = true,
  378. $isFileSize = false
  379. ) {
  380. $total = 0;
  381. $data = self::rescale($stats);
  382. echo '<table class="data_table" cellspacing="0" cellpadding="3">
  383. <tr><th colspan="'.($showTotal ? '4' : '3').'">'.$title.'</th></tr>';
  384. $i = 0;
  385. foreach ($stats as $subtitle => $number) {
  386. $total += $number;
  387. }
  388. foreach ($stats as $subtitle => $number) {
  389. if (!$isFileSize) {
  390. $number_label = number_format($number, 0, ',', '.');
  391. } else {
  392. $number_label = self::makeSizeString($number);
  393. }
  394. $percentage = ($total > 0 ? number_format(100 * $number / $total, 1, ',', '.') : '0');
  395. echo '<tr class="row_'.($i % 2 == 0 ? 'odd' : 'even').'">
  396. <td width="150">'.$subtitle.'</td>
  397. <td width="550">'.Display::bar_progress($percentage, false).'</td>
  398. <td align="right">'.$number_label.'</td>';
  399. if ($showTotal) {
  400. echo '<td align="right"> '.$percentage.'%</td>';
  401. }
  402. echo '</tr>';
  403. $i++;
  404. }
  405. if ($showTotal) {
  406. if (!$isFileSize) {
  407. $total_label = number_format($total, 0, ',', '.');
  408. } else {
  409. $total_label = self::makeSizeString($total);
  410. }
  411. echo '<tr><th colspan="4" align="right">'.get_lang('Total').': '.$total_label.'</td></tr>';
  412. }
  413. echo '</table>';
  414. }
  415. /**
  416. * Show some stats about the number of logins
  417. * @param string $type month, hour or day
  418. */
  419. public static function printLoginStats($type)
  420. {
  421. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  422. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  423. $current_url_id = api_get_current_access_url_id();
  424. $table_url = null;
  425. $where_url = null;
  426. $now = api_get_utc_datetime();
  427. $where_url_last = ' WHERE login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)';
  428. if (api_is_multiple_url_enabled()) {
  429. $table_url = ", $access_url_rel_user_table";
  430. $where_url = " WHERE login_user_id=user_id AND access_url_id='".$current_url_id."'";
  431. $where_url_last = ' AND login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)';
  432. }
  433. $period = get_lang('PeriodMonth');
  434. $periodCollection = api_get_months_long();
  435. $sql = "SELECT DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , count( login_id ) AS number_of_logins
  436. FROM ".$table.$table_url.$where_url."
  437. GROUP BY stat_date
  438. ORDER BY login_date DESC";
  439. $sql_last_x = null;
  440. switch ($type) {
  441. case 'hour':
  442. $period = get_lang('PeriodHour');
  443. $sql = "SELECT DATE_FORMAT( login_date, '%H' ) AS stat_date , count( login_id ) AS number_of_logins
  444. FROM $table.$table_url $where_url
  445. GROUP BY stat_date
  446. ORDER BY stat_date ";
  447. $sql_last_x = "SELECT DATE_FORMAT( login_date, '%H' ) AS stat_date , count( login_id ) AS number_of_logins
  448. FROM ".$table.$table_url.$where_url.sprintf($where_url_last, 'DAY')."
  449. GROUP BY stat_date
  450. ORDER BY stat_date ";
  451. break;
  452. case 'day':
  453. $periodCollection = api_get_week_days_long();
  454. $period = get_lang('PeriodDay');
  455. $sql = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , count( login_id ) AS number_of_logins
  456. FROM ".$table.$table_url.$where_url."
  457. GROUP BY stat_date
  458. ORDER BY DATE_FORMAT( login_date, '%w' ) ";
  459. $sql_last_x = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date, count( login_id ) AS number_of_logins
  460. FROM ".$table.$table_url.$where_url.sprintf($where_url_last, 'WEEK')."
  461. GROUP BY stat_date
  462. ORDER BY DATE_FORMAT( login_date, '%w' ) ";
  463. break;
  464. }
  465. if ($sql_last_x) {
  466. $res_last_x = Database::query($sql_last_x);
  467. $result_last_x = array();
  468. while ($obj = Database::fetch_object($res_last_x)) {
  469. $stat_date = ($type === 'day') ? $periodCollection[$obj->stat_date] : $obj->stat_date;
  470. $result_last_x[$stat_date] = $obj->number_of_logins;
  471. }
  472. self::printStats(get_lang('LastLogins').' ('.$period.')', $result_last_x, true);
  473. flush(); //flush web request at this point to see something already while the full data set is loading
  474. echo '<br />';
  475. }
  476. $res = Database::query($sql);
  477. $result = array();
  478. while ($obj = Database::fetch_object($res)) {
  479. $stat_date = $obj->stat_date;
  480. switch ($type) {
  481. case 'month':
  482. $stat_date = explode('-', $stat_date);
  483. $stat_date[1] = $periodCollection[$stat_date[1] - 1];
  484. $stat_date = implode(' ', $stat_date);
  485. break;
  486. case 'day':
  487. $stat_date = $periodCollection[$stat_date];
  488. break;
  489. }
  490. $result[$stat_date] = $obj->number_of_logins;
  491. }
  492. self::printStats(get_lang('AllLogins').' ('.$period.')', $result, true);
  493. }
  494. /**
  495. * Print the number of recent logins
  496. * @param bool $distinct Whether to only give distinct users stats, or *all* logins
  497. * @return void
  498. */
  499. public static function printRecentLoginStats($distinct = false)
  500. {
  501. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  502. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  503. $current_url_id = api_get_current_access_url_id();
  504. if (api_is_multiple_url_enabled()) {
  505. $table_url = ", $access_url_rel_user_table";
  506. $where_url = " AND login_user_id=user_id AND access_url_id='".$current_url_id."'";
  507. } else {
  508. $table_url = '';
  509. $where_url = '';
  510. }
  511. $now = api_get_utc_datetime();
  512. $field = 'login_id';
  513. if ($distinct) {
  514. $field = 'DISTINCT(login_user_id)';
  515. }
  516. $days = [
  517. 1,
  518. 7,
  519. 15,
  520. 31,
  521. ];
  522. $sqlList = [];
  523. foreach ($days as $day) {
  524. $date = new DateTime($now);
  525. if ($day > 1) {
  526. $date->sub(new DateInterval('P'.$day.'D'));
  527. }
  528. $startDate = $date->format('Y-m-d').' 00:00:00';
  529. $endDate = $date->format('Y-m-d').' 23:59:59';
  530. $localDate = api_get_local_time($startDate, null, null, false, false);
  531. if ($day == 1) {
  532. $label = get_lang('Today');
  533. } else {
  534. $label = sprintf(get_lang('LastXDays'), $day);
  535. }
  536. $label .= ' <br /> ('.$localDate.')';
  537. $sql = "SELECT count($field) AS number
  538. FROM $table $table_url
  539. WHERE
  540. login_date BETWEEN '$startDate' AND '$endDate'
  541. $where_url";
  542. $sqlList[$label] = $sql;
  543. }
  544. $sqlList[get_lang('Total')] = "SELECT count($field) AS number FROM $table $table_url WHERE 1=1 $where_url";
  545. $totalLogin = [];
  546. foreach ($sqlList as $label => $query) {
  547. $res = Database::query($query);
  548. $obj = Database::fetch_object($res);
  549. $totalLogin[$label] = $obj->number;
  550. }
  551. if ($distinct) {
  552. self::printStats(get_lang('DistinctUsersLogins'), $totalLogin, false);
  553. } else {
  554. self::printStats(get_lang('Logins'), $totalLogin, false);
  555. }
  556. }
  557. /**
  558. * get the number of recent logins
  559. * @param bool $distinct Whether to only give distinct users stats, or *all* logins
  560. * @return array
  561. */
  562. public static function getRecentLoginStats($distinct = false)
  563. {
  564. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  565. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  566. $current_url_id = api_get_current_access_url_id();
  567. if (api_is_multiple_url_enabled()) {
  568. $table_url = ", $access_url_rel_user_table";
  569. $where_url = " AND login_user_id=user_id AND access_url_id='".$current_url_id."'";
  570. } else {
  571. $table_url = '';
  572. $where_url = '';
  573. }
  574. $now = api_get_utc_datetime();
  575. $date = new DateTime($now);
  576. $date->sub(new DateInterval('P15D'));
  577. $newDate = $date->format('Y-m-d').' 00:00:00';
  578. $field = 'login_id';
  579. if ($distinct) {
  580. $field = 'DISTINCT(login_user_id)';
  581. }
  582. $sql = "SELECT count($field) AS number, date(login_date) as login_date
  583. FROM $table $table_url
  584. WHERE login_date >= '$newDate' $where_url
  585. GROUP BY date(login_date)";
  586. $res = Database::query($sql);
  587. $totalLogin = [];
  588. while ($row = Database::fetch_array($res, 'ASSOC')) {
  589. $totalLogin[$row['login_date']] = $row['number'];
  590. }
  591. return $totalLogin;
  592. }
  593. /**
  594. * Show some stats about the accesses to the different course tools
  595. */
  596. public static function printToolStats()
  597. {
  598. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  599. $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  600. $current_url_id = api_get_current_access_url_id();
  601. $tools = array(
  602. 'announcement',
  603. 'assignment',
  604. 'calendar_event',
  605. 'chat',
  606. 'course_description',
  607. 'document',
  608. 'dropbox',
  609. 'group',
  610. 'learnpath',
  611. 'link',
  612. 'quiz',
  613. 'student_publication',
  614. 'user',
  615. 'forum'
  616. );
  617. $tool_names = array();
  618. foreach ($tools as $tool) {
  619. $tool_names[$tool] = get_lang(ucfirst($tool), '');
  620. }
  621. if (api_is_multiple_url_enabled()) {
  622. $sql = "SELECT access_tool, count( access_id ) AS number_of_logins
  623. FROM $table t , $access_url_rel_course_table a
  624. WHERE
  625. access_tool IN ('".implode("','", $tools)."') AND
  626. t.c_id = a.c_id AND
  627. access_url_id='".$current_url_id."'
  628. GROUP BY access_tool
  629. ";
  630. } else {
  631. $sql = "SELECT access_tool, count( access_id ) AS number_of_logins
  632. FROM $table
  633. WHERE access_tool IN ('".implode("','", $tools)."')
  634. GROUP BY access_tool ";
  635. }
  636. $res = Database::query($sql);
  637. $result = array();
  638. while ($obj = Database::fetch_object($res)) {
  639. $result[$tool_names[$obj->access_tool]] = $obj->number_of_logins;
  640. }
  641. self::printStats(get_lang('PlatformToolAccess'), $result, true);
  642. }
  643. /**
  644. * Show some stats about the number of courses per language
  645. */
  646. public static function printCourseByLanguageStats()
  647. {
  648. $table = Database::get_main_table(TABLE_MAIN_COURSE);
  649. $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  650. $current_url_id = api_get_current_access_url_id();
  651. if (api_is_multiple_url_enabled()) {
  652. $sql = "SELECT course_language, count( c.code ) AS number_of_courses
  653. FROM $table as c, $access_url_rel_course_table as u
  654. WHERE u.c_id = c.id AND access_url_id='".$current_url_id."'
  655. GROUP BY course_language
  656. ORDER BY number_of_courses DESC";
  657. } else {
  658. $sql = "SELECT course_language, count( code ) AS number_of_courses
  659. FROM $table GROUP BY course_language
  660. ORDER BY number_of_courses DESC";
  661. }
  662. $res = Database::query($sql);
  663. $result = array();
  664. while ($obj = Database::fetch_object($res)) {
  665. $result[$obj->course_language] = $obj->number_of_courses;
  666. }
  667. self::printStats(get_lang('CountCourseByLanguage'), $result, true);
  668. }
  669. /**
  670. * Shows the number of users having their picture uploaded in Dokeos.
  671. */
  672. public static function printUserPicturesStats()
  673. {
  674. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  675. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  676. $current_url_id = api_get_current_access_url_id();
  677. $url_condition = null;
  678. $url_condition2 = null;
  679. $table = null;
  680. if (api_is_multiple_url_enabled()) {
  681. $url_condition = ", $access_url_rel_user_table as url WHERE url.user_id=u.user_id AND access_url_id='".$current_url_id."'";
  682. $url_condition2 = " AND url.user_id=u.user_id AND access_url_id='".$current_url_id."'";
  683. $table = ", $access_url_rel_user_table as url ";
  684. }
  685. $sql = "SELECT COUNT(*) AS n FROM $user_table as u ".$url_condition;
  686. $res = Database::query($sql);
  687. $count1 = Database::fetch_object($res);
  688. $sql = "SELECT COUNT(*) AS n FROM $user_table as u $table ".
  689. "WHERE LENGTH(picture_uri) > 0 $url_condition2";
  690. $res = Database::query($sql);
  691. $count2 = Database::fetch_object($res);
  692. // #users without picture
  693. $result[get_lang('No')] = $count1->n - $count2->n;
  694. $result[get_lang('Yes')] = $count2->n; // #users with picture
  695. self::printStats(get_lang('CountUsers').' ('.get_lang('UserPicture').')', $result, true);
  696. }
  697. /**
  698. * Important activities
  699. */
  700. public static function printActivitiesStats()
  701. {
  702. echo '<h4>'.get_lang('ImportantActivities').'</h4>';
  703. // Create a search-box
  704. $form = new FormValidator(
  705. 'search_simple',
  706. 'get',
  707. api_get_path(WEB_CODE_PATH).'admin/statistics/index.php',
  708. '',
  709. 'width=200px',
  710. false
  711. );
  712. $renderer = & $form->defaultRenderer();
  713. $renderer->setCustomElementTemplate('<span>{element}</span> ');
  714. $form->addElement('hidden', 'report', 'activities');
  715. $form->addElement('hidden', 'activities_direction', 'DESC');
  716. $form->addElement('hidden', 'activities_column', '4');
  717. $form->addElement('text', 'keyword', get_lang('Keyword'));
  718. $form->addButtonSearch(get_lang('Search'), 'submit');
  719. echo '<div class="actions">';
  720. $form->display();
  721. echo '</div>';
  722. $table = new SortableTable(
  723. 'activities',
  724. array('Statistics', 'getNumberOfActivities'),
  725. array('Statistics', 'getActivitiesData'),
  726. 5,
  727. 50,
  728. 'DESC'
  729. );
  730. $parameters = array();
  731. $parameters['report'] = 'activities';
  732. if (isset($_GET['keyword'])) {
  733. $parameters['keyword'] = Security::remove_XSS($_GET['keyword']);
  734. }
  735. $table->set_additional_parameters($parameters);
  736. $table->set_header(0, get_lang('EventType'));
  737. $table->set_header(1, get_lang('DataType'));
  738. $table->set_header(2, get_lang('Value'));
  739. $table->set_header(3, get_lang('Course'));
  740. $table->set_header(4, get_lang('Session'));
  741. $table->set_header(5, get_lang('UserName'));
  742. $table->set_header(6, get_lang('IPAddress'));
  743. $table->set_header(7, get_lang('Date'));
  744. $table->display();
  745. }
  746. /**
  747. * Shows statistics about the time of last visit to each course.
  748. */
  749. public static function printCourseLastVisit()
  750. {
  751. $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  752. $current_url_id = api_get_current_access_url_id();
  753. $columns[0] = 't.c_id';
  754. $columns[1] = 'access_date';
  755. $sql_order[SORT_ASC] = 'ASC';
  756. $sql_order[SORT_DESC] = 'DESC';
  757. $per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 10;
  758. $page_nr = isset($_GET['page_nr']) ? intval($_GET['page_nr']) : 1;
  759. $column = isset($_GET['column']) ? intval($_GET['column']) : 0;
  760. $direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC;
  761. if (!in_array($direction, array(SORT_ASC, SORT_DESC))) {
  762. $direction = SORT_ASC;
  763. }
  764. $form = new FormValidator('courselastvisit', 'get');
  765. $form->addElement('hidden', 'report', 'courselastvisit');
  766. $form->addText('date_diff', get_lang('Days'), true);
  767. $form->addRule('date_diff', 'InvalidNumber', 'numeric');
  768. $form->addButtonSearch(get_lang('Search'), 'submit');
  769. if (!isset($_GET['date_diff'])) {
  770. $defaults['date_diff'] = 60;
  771. } else {
  772. $defaults['date_diff'] = Security::remove_XSS($_GET['date_diff']);
  773. }
  774. $form->setDefaults($defaults);
  775. $form->display();
  776. $values = $form->exportValues();
  777. $date_diff = $values['date_diff'];
  778. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  779. if (api_is_multiple_url_enabled()) {
  780. $sql = "SELECT * FROM $table t , $access_url_rel_course_table a
  781. WHERE
  782. t.c_id = a.c_id AND
  783. access_url_id='".$current_url_id."'
  784. GROUP BY t.c_id
  785. HAVING t.c_id <> ''
  786. AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff;
  787. } else {
  788. $sql = "SELECT * FROM $table t
  789. GROUP BY t.c_id
  790. HAVING t.c_id <> ''
  791. AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff;
  792. }
  793. $sql .= ' ORDER BY '.$columns[$column].' '.$sql_order[$direction];
  794. $from = ($page_nr - 1) * $per_page;
  795. $sql .= ' LIMIT '.$from.','.$per_page;
  796. echo '<p>'.get_lang('LastAccess').' &gt;= '.$date_diff.' '.get_lang('Days').'</p>';
  797. $res = Database::query($sql);
  798. if (Database::num_rows($res) > 0) {
  799. $courses = array();
  800. while ($obj = Database::fetch_object($res)) {
  801. $courseInfo = api_get_course_info_by_id($obj->c_id);
  802. $course = array();
  803. $course[] = '<a href="'.api_get_path(WEB_COURSE_PATH).$courseInfo['code'].'">'.$courseInfo['code'].' <a>';
  804. // Allow sort by date hiding the numerical date
  805. $course[] = '<span style="display:none;">'.$obj->access_date.'</span>'.api_convert_and_format_date($obj->access_date);
  806. $courses[] = $course;
  807. }
  808. $parameters['date_diff'] = $date_diff;
  809. $parameters['report'] = 'courselastvisit';
  810. $table_header[] = array(get_lang("CourseCode"), true);
  811. $table_header[] = array(get_lang("LastAccess"), true);
  812. Display:: display_sortable_table(
  813. $table_header,
  814. $courses,
  815. array('column' => $column, 'direction' => $direction),
  816. array(),
  817. $parameters
  818. );
  819. } else {
  820. echo get_lang('NoSearchResults');
  821. }
  822. }
  823. /**
  824. * Displays the statistics of the messages sent and received by each user in the social network
  825. * @param string $messageType Type of message: 'sent' or 'received'
  826. * @return array Message list
  827. */
  828. public static function getMessages($messageType)
  829. {
  830. $message_table = Database::get_main_table(TABLE_MESSAGE);
  831. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  832. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  833. $current_url_id = api_get_current_access_url_id();
  834. switch ($messageType) {
  835. case 'sent':
  836. $field = 'user_sender_id';
  837. break;
  838. case 'received':
  839. $field = 'user_receiver_id';
  840. break;
  841. }
  842. if (api_is_multiple_url_enabled()) {
  843. $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message
  844. FROM $access_url_rel_user_table as url, $message_table m
  845. LEFT JOIN $user_table u ON m.$field = u.user_id
  846. WHERE url.user_id = m.$field AND access_url_id='".$current_url_id."'
  847. GROUP BY m.$field
  848. ORDER BY count_message DESC ";
  849. } else {
  850. $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message
  851. FROM $message_table m
  852. LEFT JOIN $user_table u ON m.$field = u.user_id
  853. GROUP BY m.$field ORDER BY count_message DESC ";
  854. }
  855. $res = Database::query($sql);
  856. $messages_sent = array();
  857. while ($messages = Database::fetch_array($res)) {
  858. if (empty($messages['username'])) {
  859. $messages['username'] = get_lang('Unknown');
  860. }
  861. $users = api_get_person_name(
  862. $messages['firstname'],
  863. $messages['lastname']
  864. ).'<br />('.$messages['username'].')';
  865. $messages_sent[$users] = $messages['count_message'];
  866. }
  867. return $messages_sent;
  868. }
  869. /**
  870. * Count the number of friends for social network users
  871. */
  872. public static function getFriends()
  873. {
  874. $user_friend_table = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
  875. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  876. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  877. $current_url_id = api_get_current_access_url_id();
  878. if (api_is_multiple_url_enabled()) {
  879. $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend
  880. FROM $access_url_rel_user_table as url, $user_friend_table uf
  881. LEFT JOIN $user_table u
  882. ON (uf.user_id = u.user_id)
  883. WHERE
  884. uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' AND
  885. uf.user_id = url.user_id AND
  886. access_url_id = '".$current_url_id."'
  887. GROUP BY uf.user_id
  888. ORDER BY count_friend DESC ";
  889. } else {
  890. $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend
  891. FROM $user_friend_table uf
  892. LEFT JOIN $user_table u
  893. ON (uf.user_id = u.user_id)
  894. WHERE uf.relation_type <> '".USER_RELATION_TYPE_RRHH."'
  895. GROUP BY uf.user_id
  896. ORDER BY count_friend DESC ";
  897. }
  898. $res = Database::query($sql);
  899. $list_friends = array();
  900. while ($friends = Database::fetch_array($res)) {
  901. $users = api_get_person_name($friends['firstname'], $friends['lastname']).'<br />('.$friends['username'].')';
  902. $list_friends[$users] = $friends['count_friend'];
  903. }
  904. return $list_friends;
  905. }
  906. /**
  907. * Print the number of users that didn't login for a certain period of time
  908. */
  909. public static function printUsersNotLoggedInStats()
  910. {
  911. $totalLogin = array();
  912. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  913. $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  914. $current_url_id = api_get_current_access_url_id();
  915. $total = self::countUsers();
  916. if (api_is_multiple_url_enabled()) {
  917. $table_url = ", $access_url_rel_user_table";
  918. $where_url = " AND login_user_id=user_id AND access_url_id='".$current_url_id."'";
  919. } else {
  920. $table_url = '';
  921. $where_url = '';
  922. }
  923. $now = api_get_utc_datetime();
  924. $sql[get_lang('ThisDay')] =
  925. "SELECT count(distinct(login_user_id)) AS number ".
  926. " FROM $table $table_url ".
  927. " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= '$now' $where_url";
  928. $sql[get_lang('Last7days')] =
  929. "SELECT count(distinct(login_user_id)) AS number ".
  930. " FROM $table $table_url ".
  931. " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= '$now' $where_url";
  932. $sql[get_lang('Last31days')] =
  933. "SELECT count(distinct(login_user_id)) AS number ".
  934. " FROM $table $table_url ".
  935. " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= '$now' $where_url";
  936. $sql[sprintf(get_lang('LastXMonths'), 6)] =
  937. "SELECT count(distinct(login_user_id)) AS number ".
  938. " FROM $table $table_url ".
  939. " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= '$now' $where_url";
  940. $sql[get_lang('NeverConnected')] =
  941. "SELECT count(distinct(login_user_id)) AS number ".
  942. " FROM $table $table_url WHERE 1=1 $where_url";
  943. foreach ($sql as $index => $query) {
  944. $res = Database::query($query);
  945. $obj = Database::fetch_object($res);
  946. $r = $total - $obj->number;
  947. $totalLogin[$index] = $r < 0 ? 0 : $r;
  948. }
  949. self::printStats(
  950. get_lang('StatsUsersDidNotLoginInLastPeriods'),
  951. $totalLogin,
  952. false
  953. );
  954. }
  955. }