statistics.lib.php 36 KB

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