1 | <?php |
||
2 | /* For licensing terms, see /license.txt */ |
||
3 | |||
4 | use Chamilo\CoreBundle\Component\Utils\ChamiloApi; |
||
5 | |||
6 | /** |
||
7 | * This class provides some functions for statistics. |
||
8 | */ |
||
9 | class Statistics |
||
10 | { |
||
11 | /** |
||
12 | * Converts a number of bytes in a formatted string. |
||
13 | * |
||
14 | * @param int $size |
||
15 | * |
||
16 | * @return string Formatted file size |
||
17 | */ |
||
18 | public static function makeSizeString($size) |
||
19 | { |
||
20 | if ($size < pow(2, 10)) { |
||
21 | return $size." bytes"; |
||
22 | } |
||
23 | if ($size >= pow(2, 10) && $size < pow(2, 20)) { |
||
24 | return round($size / pow(2, 10), 0)." KB"; |
||
25 | } |
||
26 | if ($size >= pow(2, 20) && $size < pow(2, 30)) { |
||
27 | return round($size / pow(2, 20), 1)." MB"; |
||
28 | } |
||
29 | if ($size > pow(2, 30)) { |
||
30 | return round($size / pow(2, 30), 2)." GB"; |
||
31 | } |
||
32 | } |
||
33 | |||
34 | /** |
||
35 | * Count courses. |
||
36 | * |
||
37 | * @param string $categoryCode Code of a course category. |
||
38 | * Default: count all courses. |
||
39 | * |
||
40 | * @return int Number of courses counted |
||
41 | */ |
||
42 | public static function countCourses($categoryCode = null) |
||
43 | { |
||
44 | $course_table = Database::get_main_table(TABLE_MAIN_COURSE); |
||
45 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
46 | $urlId = api_get_current_access_url_id(); |
||
47 | if (api_is_multiple_url_enabled()) { |
||
48 | $sql = "SELECT COUNT(*) AS number |
||
49 | FROM ".$course_table." as c, $access_url_rel_course_table as u |
||
50 | WHERE u.c_id = c.id AND access_url_id='".$urlId."'"; |
||
51 | if (isset($categoryCode)) { |
||
52 | $sql .= " AND category_code = '".Database::escape_string($categoryCode)."'"; |
||
53 | } |
||
54 | } else { |
||
55 | $sql = "SELECT COUNT(*) AS number |
||
56 | FROM $course_table"; |
||
57 | if (isset($categoryCode)) { |
||
58 | $sql .= " WHERE category_code = '".Database::escape_string($categoryCode)."'"; |
||
59 | } |
||
60 | } |
||
61 | |||
62 | $res = Database::query($sql); |
||
63 | $obj = Database::fetch_object($res); |
||
64 | |||
65 | return $obj->number; |
||
66 | } |
||
67 | |||
68 | /** |
||
69 | * Count courses by visibility. |
||
70 | * |
||
71 | * @param int $visibility visibility (0 = closed, 1 = private, 2 = open, 3 = public) all courses |
||
72 | * |
||
73 | * @return int Number of courses counted |
||
74 | */ |
||
75 | public static function countCoursesByVisibility($visibility = null) |
||
76 | { |
||
77 | if (!isset($visibility)) { |
||
78 | return 0; |
||
79 | } |
||
80 | $course_table = Database::get_main_table(TABLE_MAIN_COURSE); |
||
81 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
82 | $urlId = api_get_current_access_url_id(); |
||
83 | if (api_is_multiple_url_enabled()) { |
||
84 | $sql = "SELECT COUNT(*) AS number |
||
85 | FROM $course_table as c, $access_url_rel_course_table as u |
||
86 | WHERE u.c_id = c.id AND access_url_id='".$urlId."'"; |
||
87 | if (isset($visibility)) { |
||
88 | $sql .= " AND visibility = ".intval($visibility); |
||
89 | } |
||
90 | } else { |
||
91 | $sql = "SELECT COUNT(*) AS number FROM $course_table "; |
||
92 | if (isset($visibility)) { |
||
93 | $sql .= " WHERE visibility = ".intval($visibility); |
||
94 | } |
||
95 | } |
||
96 | $res = Database::query($sql); |
||
97 | $obj = Database::fetch_object($res); |
||
98 | |||
99 | return $obj->number; |
||
100 | } |
||
101 | |||
102 | /** |
||
103 | * Count users. |
||
104 | * |
||
105 | * @param int $status user status (COURSEMANAGER or STUDENT) if not setted it'll count all users |
||
106 | * @param string $categoryCode course category code. Default: count only users without filtering category |
||
107 | * @param bool $countInvisibleCourses Count invisible courses (todo) |
||
108 | * @param bool $onlyActive Count only active users (false to only return currently active users) |
||
109 | * |
||
110 | * @return int Number of users counted |
||
111 | */ |
||
112 | public static function countUsers( |
||
113 | $status = null, |
||
114 | $categoryCode = null, |
||
115 | $countInvisibleCourses = true, |
||
116 | $onlyActive = false |
||
117 | ) { |
||
118 | // Database table definitions |
||
119 | $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
120 | $course_table = Database::get_main_table(TABLE_MAIN_COURSE); |
||
121 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
122 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
123 | $urlId = api_get_current_access_url_id(); |
||
124 | $active_filter = $onlyActive ? ' AND active=1' : ''; |
||
125 | $status_filter = isset($status) ? ' AND status = '.intval($status) : ''; |
||
126 | |||
127 | if (api_is_multiple_url_enabled()) { |
||
128 | $sql = "SELECT COUNT(DISTINCT(u.user_id)) AS number |
||
129 | FROM $user_table as u, $access_url_rel_user_table as url |
||
130 | WHERE |
||
131 | u.user_id = url.user_id AND |
||
132 | access_url_id = '".$urlId."' |
||
133 | $status_filter $active_filter"; |
||
134 | if (isset($categoryCode)) { |
||
135 | $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number |
||
136 | FROM $course_user_table cu, $course_table c, $access_url_rel_user_table as url |
||
137 | WHERE |
||
138 | c.id = cu.c_id AND |
||
139 | c.category_code = '".Database::escape_string($categoryCode)."' AND |
||
140 | cu.user_id = url.user_id AND |
||
141 | access_url_id='".$urlId."' |
||
142 | $status_filter $active_filter"; |
||
143 | } |
||
144 | } else { |
||
145 | $sql = "SELECT COUNT(DISTINCT(user_id)) AS number |
||
146 | FROM $user_table |
||
147 | WHERE 1=1 $status_filter $active_filter"; |
||
148 | if (isset($categoryCode)) { |
||
149 | $status_filter = isset($status) ? ' AND status = '.intval($status) : ''; |
||
150 | $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number |
||
151 | FROM $course_user_table cu, $course_table c |
||
152 | WHERE |
||
153 | c.id = cu.c_id AND |
||
154 | c.category_code = '".Database::escape_string($categoryCode)."' |
||
155 | $status_filter |
||
156 | $active_filter |
||
157 | "; |
||
158 | } |
||
159 | } |
||
160 | |||
161 | $res = Database::query($sql); |
||
162 | $obj = Database::fetch_object($res); |
||
163 | |||
164 | return $obj->number; |
||
165 | } |
||
166 | |||
167 | /** |
||
168 | * @param string $startDate |
||
169 | * @param string $endDate |
||
170 | * |
||
171 | * @return array |
||
172 | */ |
||
173 | public static function getCoursesWithActivity($startDate, $endDate) |
||
174 | { |
||
175 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
176 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS); |
||
177 | $startDate = Database::escape_string($startDate); |
||
178 | $endDate = Database::escape_string($endDate); |
||
179 | |||
180 | $urlId = api_get_current_access_url_id(); |
||
181 | |||
182 | if (api_is_multiple_url_enabled()) { |
||
183 | $sql = "SELECT DISTINCT(t.c_id) FROM $table t , $access_url_rel_course_table a |
||
184 | WHERE |
||
185 | t.c_id = a.c_id AND |
||
186 | access_url_id='".$urlId."' AND |
||
187 | access_date BETWEEN '$startDate' AND '$endDate' |
||
188 | "; |
||
189 | } else { |
||
190 | $sql = "SELECT DISTINCT(t.c_id) FROM $table t |
||
191 | access_date BETWEEN '$startDate' AND '$endDate' "; |
||
192 | } |
||
193 | |||
194 | $result = Database::query($sql); |
||
195 | |||
196 | return Database::store_result($result); |
||
197 | } |
||
198 | |||
199 | /** |
||
200 | * Count activities from track_e_default_table. |
||
201 | * |
||
202 | * @return int Number of activities counted |
||
203 | */ |
||
204 | public static function getNumberOfActivities($courseId = 0, $sessionId = 0) |
||
205 | { |
||
206 | // Database table definitions |
||
207 | $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
208 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
209 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
210 | $urlId = api_get_current_access_url_id(); |
||
211 | if (api_is_multiple_url_enabled()) { |
||
212 | $sql = "SELECT count(default_id) AS total_number_of_items |
||
213 | FROM $track_e_default, $table_user user, $access_url_rel_user_table url |
||
214 | WHERE |
||
215 | default_user_id = user.user_id AND |
||
216 | user.user_id=url.user_id AND |
||
217 | access_url_id = '".$urlId."'"; |
||
218 | } else { |
||
219 | $sql = "SELECT count(default_id) AS total_number_of_items |
||
220 | FROM $track_e_default, $table_user user |
||
221 | WHERE default_user_id = user.user_id "; |
||
222 | } |
||
223 | |||
224 | if (!empty($courseId)) { |
||
225 | $courseId = (int) $courseId; |
||
226 | $sql .= " AND c_id = $courseId"; |
||
227 | $sql .= api_get_session_condition($sessionId); |
||
228 | } |
||
229 | |||
230 | if (isset($_GET['keyword'])) { |
||
231 | $keyword = Database::escape_string(trim($_GET['keyword'])); |
||
232 | $sql .= " AND ( |
||
233 | user.username LIKE '%".$keyword."%' OR |
||
234 | default_event_type LIKE '%".$keyword."%' OR |
||
235 | default_value_type LIKE '%".$keyword."%' OR |
||
236 | default_value LIKE '%".$keyword."%') "; |
||
237 | } |
||
238 | $res = Database::query($sql); |
||
239 | $obj = Database::fetch_object($res); |
||
240 | |||
241 | return $obj->total_number_of_items; |
||
242 | } |
||
243 | |||
244 | /** |
||
245 | * Get activities data to display. |
||
246 | * |
||
247 | * @param int $from |
||
248 | * @param int $numberOfItems |
||
249 | * @param int $column |
||
250 | * @param string $direction |
||
251 | * @param int $courseId |
||
252 | * @param int $sessionId |
||
253 | * |
||
254 | * @return array |
||
255 | */ |
||
256 | public static function getActivitiesData( |
||
257 | $from, |
||
258 | $numberOfItems, |
||
259 | $column, |
||
260 | $direction, |
||
261 | $courseId = 0, |
||
262 | $sessionId = 0 |
||
263 | ) { |
||
264 | $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
265 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
266 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
267 | $urlId = api_get_current_access_url_id(); |
||
268 | $column = (int) $column; |
||
269 | $from = (int) $from; |
||
270 | $numberOfItems = (int) $numberOfItems; |
||
271 | $direction = strtoupper($direction); |
||
272 | |||
273 | if (!in_array($direction, ['ASC', 'DESC'])) { |
||
274 | $direction = 'DESC'; |
||
275 | } |
||
276 | |||
277 | if (api_is_multiple_url_enabled()) { |
||
278 | $sql = "SELECT |
||
279 | default_event_type as col0, |
||
280 | default_value_type as col1, |
||
281 | default_value as col2, |
||
282 | c_id as col3, |
||
283 | session_id as col4, |
||
284 | user.username as col5, |
||
285 | user.user_id as col6, |
||
286 | default_date as col7 |
||
287 | FROM $track_e_default as track_default, |
||
288 | $table_user as user, |
||
289 | $access_url_rel_user_table as url |
||
290 | WHERE |
||
291 | track_default.default_user_id = user.user_id AND |
||
292 | url.user_id = user.user_id AND |
||
293 | access_url_id= $urlId "; |
||
294 | } else { |
||
295 | $sql = "SELECT |
||
296 | default_event_type as col0, |
||
297 | default_value_type as col1, |
||
298 | default_value as col2, |
||
299 | c_id as col3, |
||
300 | session_id as col4, |
||
301 | user.username as col5, |
||
302 | user.user_id as col6, |
||
303 | default_date as col7 |
||
304 | FROM $track_e_default track_default, $table_user user |
||
305 | WHERE track_default.default_user_id = user.user_id "; |
||
306 | } |
||
307 | |||
308 | if (!empty($_GET['keyword'])) { |
||
309 | $keyword = Database::escape_string(trim($_GET['keyword'])); |
||
310 | $sql .= " AND (user.username LIKE '%".$keyword."%' OR |
||
311 | default_event_type LIKE '%".$keyword."%' OR |
||
312 | default_value_type LIKE '%".$keyword."%' OR |
||
313 | default_value LIKE '%".$keyword."%') "; |
||
314 | } |
||
315 | |||
316 | if (!empty($courseId)) { |
||
317 | $courseId = (int) $courseId; |
||
318 | $sql .= " AND c_id = $courseId"; |
||
319 | $sql .= api_get_session_condition($sessionId); |
||
320 | } |
||
321 | |||
322 | if (!empty($column) && !empty($direction)) { |
||
323 | $sql .= " ORDER BY col$column $direction"; |
||
324 | } else { |
||
325 | $sql .= " ORDER BY col7 DESC "; |
||
326 | } |
||
327 | $sql .= " LIMIT $from, $numberOfItems "; |
||
328 | |||
329 | $res = Database::query($sql); |
||
330 | $activities = []; |
||
331 | while ($row = Database::fetch_row($res)) { |
||
332 | if (strpos($row[1], '_object') === false && |
||
333 | strpos($row[1], '_array') === false |
||
334 | ) { |
||
335 | $row[2] = $row[2]; |
||
336 | } else { |
||
337 | if (!empty($row[2])) { |
||
338 | $originalData = str_replace('\\', '', $row[2]); |
||
339 | $row[2] = UnserializeApi::unserialize('not_allowed_classes', $originalData); |
||
340 | if (is_array($row[2]) && !empty($row[2])) { |
||
341 | $row[2] = implode_with_key(', ', $row[2]); |
||
342 | } else { |
||
343 | $row[2] = $originalData; |
||
344 | } |
||
345 | } |
||
346 | } |
||
347 | |||
348 | if (!empty($row['default_date'])) { |
||
349 | $row['default_date'] = api_get_local_time($row['default_date']); |
||
350 | } else { |
||
351 | $row['default_date'] = '-'; |
||
352 | } |
||
353 | |||
354 | if (!empty($row[7])) { |
||
355 | $row[7] = api_get_local_time($row[7]); |
||
356 | } else { |
||
357 | $row[7] = '-'; |
||
358 | } |
||
359 | |||
360 | if (!empty($row[5])) { |
||
361 | // Course |
||
362 | if (!empty($row[3])) { |
||
363 | $row[3] = Display::url( |
||
364 | $row[3], |
||
365 | api_get_path(WEB_CODE_PATH).'admin/course_edit.php?id='.$row[3] |
||
366 | ); |
||
367 | } else { |
||
368 | $row[3] = '-'; |
||
369 | } |
||
370 | |||
371 | // session |
||
372 | if (!empty($row[4])) { |
||
373 | $row[4] = Display::url( |
||
374 | $row[4], |
||
375 | api_get_path(WEB_CODE_PATH).'session/resume_session.php?id_session='.$row[4] |
||
376 | ); |
||
377 | } else { |
||
378 | $row[4] = '-'; |
||
379 | } |
||
380 | |||
381 | // User id. |
||
382 | $row[5] = Display::url( |
||
383 | $row[5], |
||
384 | api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&user_id='.$row[6], |
||
385 | ['class' => 'ajax'] |
||
386 | ); |
||
387 | |||
388 | $row[6] = Tracking::get_ip_from_user_event( |
||
389 | $row[6], |
||
390 | $row[7], |
||
391 | true |
||
392 | ); |
||
393 | if (empty($row[6])) { |
||
394 | $row[6] = get_lang('Unknown'); |
||
395 | } |
||
396 | } |
||
397 | $activities[] = $row; |
||
398 | } |
||
399 | |||
400 | return $activities; |
||
401 | } |
||
402 | |||
403 | /** |
||
404 | * Get all course categories. |
||
405 | * |
||
406 | * @return array All course categories (code => name) |
||
407 | */ |
||
408 | public static function getCourseCategories() |
||
409 | { |
||
410 | $categoryTable = Database::get_main_table(TABLE_MAIN_CATEGORY); |
||
411 | $sql = "SELECT code, name |
||
412 | FROM $categoryTable |
||
413 | ORDER BY tree_pos"; |
||
414 | $res = Database::query($sql); |
||
415 | $categories = []; |
||
416 | while ($category = Database::fetch_object($res)) { |
||
417 | $categories[$category->code] = $category->name; |
||
418 | } |
||
419 | |||
420 | return $categories; |
||
421 | } |
||
422 | |||
423 | /** |
||
424 | * Rescale data. |
||
425 | * |
||
426 | * @param array $data The data that should be rescaled |
||
427 | * @param int $max The maximum value in the rescaled data (default = 500); |
||
428 | * |
||
429 | * @return array The rescaled data, same key as $data |
||
430 | */ |
||
431 | public static function rescale($data, $max = 500) |
||
432 | { |
||
433 | $data_max = 1; |
||
434 | foreach ($data as $index => $value) { |
||
435 | $data_max = ($data_max < $value ? $value : $data_max); |
||
436 | } |
||
437 | reset($data); |
||
438 | $result = []; |
||
439 | $delta = $max / $data_max; |
||
440 | foreach ($data as $index => $value) { |
||
441 | $result[$index] = (int) round($value * $delta); |
||
442 | } |
||
443 | |||
444 | return $result; |
||
445 | } |
||
446 | |||
447 | /** |
||
448 | * Show statistics. |
||
449 | * |
||
450 | * @param string $title The title |
||
451 | * @param array $stats |
||
452 | * @param bool $showTotal |
||
453 | * @param bool $isFileSize |
||
454 | * |
||
455 | * @return string HTML table |
||
456 | */ |
||
457 | public static function printStats( |
||
458 | $title, |
||
459 | $stats, |
||
460 | $showTotal = true, |
||
461 | $isFileSize = false |
||
462 | ) { |
||
463 | $total = 0; |
||
464 | $content = '<table class="table table-hover table-striped data_table" cellspacing="0" cellpadding="3" width="90%"> |
||
465 | <thead><tr><th colspan="'.($showTotal ? '4' : '3').'">'.$title.'</th></tr></thead><tbody>'; |
||
466 | $i = 0; |
||
467 | foreach ($stats as $subtitle => $number) { |
||
468 | $total += $number; |
||
469 | } |
||
470 | |||
471 | foreach ($stats as $subtitle => $number) { |
||
472 | if (!$isFileSize) { |
||
473 | $number_label = number_format($number, 0, ',', '.'); |
||
474 | } else { |
||
475 | $number_label = self::makeSizeString($number); |
||
476 | } |
||
477 | $percentage = ($total > 0 ? number_format(100 * $number / $total, 1, ',', '.') : '0'); |
||
478 | |||
479 | $content .= '<tr class="row_'.($i % 2 == 0 ? 'odd' : 'even').'"> |
||
480 | <td width="25%" style="vertical-align:top;">'.$subtitle.'</td> |
||
481 | <td width="60%">'.Display::bar_progress($percentage, false).'</td> |
||
482 | <td width="5%" align="right" style="vertical-align:top;">'.$number_label.'</td>'; |
||
483 | if ($showTotal) { |
||
484 | $content .= '<td width="5%" align="right"> '.$percentage.'%</td>'; |
||
485 | } |
||
486 | $content .= '</tr>'; |
||
487 | $i++; |
||
488 | } |
||
489 | $content .= '</tbody>'; |
||
490 | if ($showTotal) { |
||
491 | if (!$isFileSize) { |
||
492 | $total_label = number_format($total, 0, ',', '.'); |
||
493 | } else { |
||
494 | $total_label = self::makeSizeString($total); |
||
495 | } |
||
496 | $content .= ' |
||
497 | <tfoot><tr><th colspan="4" align="right">'.get_lang('Total').': '.$total_label.'</td></tr></tfoot> |
||
498 | '; |
||
499 | } |
||
500 | $content .= '</table>'; |
||
501 | |||
502 | return $content; |
||
503 | } |
||
504 | |||
505 | /** |
||
506 | * Show some stats about the number of logins. |
||
507 | * |
||
508 | * @param string $type month, hour or day |
||
509 | */ |
||
510 | public static function printLoginStats($type) |
||
511 | { |
||
512 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
513 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
514 | $urlId = api_get_current_access_url_id(); |
||
515 | |||
516 | $table_url = null; |
||
517 | $where_url = null; |
||
518 | $now = api_get_utc_datetime(); |
||
519 | $where_url_last = ' WHERE login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)'; |
||
520 | if (api_is_multiple_url_enabled()) { |
||
521 | $table_url = ", $access_url_rel_user_table"; |
||
522 | $where_url = " WHERE login_user_id=user_id AND access_url_id='".$urlId."'"; |
||
523 | $where_url_last = ' AND login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)'; |
||
524 | } |
||
525 | |||
526 | $period = get_lang('PeriodMonth'); |
||
527 | $periodCollection = api_get_months_long(); |
||
528 | $sql = "SELECT |
||
529 | DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , |
||
530 | count( login_id ) AS number_of_logins |
||
531 | FROM $table $table_url $where_url |
||
532 | GROUP BY stat_date |
||
533 | ORDER BY login_date DESC"; |
||
534 | $sql_last_x = null; |
||
535 | |||
536 | switch ($type) { |
||
537 | case 'hour': |
||
538 | $period = get_lang('PeriodHour'); |
||
539 | $sql = "SELECT |
||
540 | DATE_FORMAT( login_date, '%H') AS stat_date, |
||
541 | count( login_id ) AS number_of_logins |
||
542 | FROM $table $table_url $where_url |
||
543 | GROUP BY stat_date |
||
544 | ORDER BY stat_date "; |
||
545 | $sql_last_x = "SELECT |
||
546 | DATE_FORMAT( login_date, '%H' ) AS stat_date, |
||
547 | count( login_id ) AS number_of_logins |
||
548 | FROM $table $table_url $where_url ".sprintf($where_url_last, 'DAY')." |
||
549 | GROUP BY stat_date |
||
550 | ORDER BY stat_date "; |
||
551 | break; |
||
552 | case 'day': |
||
553 | $periodCollection = api_get_week_days_long(); |
||
554 | $period = get_lang('PeriodDay'); |
||
555 | $sql = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , |
||
556 | count( login_id ) AS number_of_logins |
||
557 | FROM $table $table_url $where_url |
||
558 | GROUP BY stat_date |
||
559 | ORDER BY DATE_FORMAT( login_date, '%w' ) "; |
||
560 | $sql_last_x = "SELECT |
||
561 | DATE_FORMAT( login_date, '%w' ) AS stat_date, |
||
562 | count( login_id ) AS number_of_logins |
||
563 | FROM $table $table_url $where_url ".sprintf($where_url_last, 'WEEK')." |
||
564 | GROUP BY stat_date |
||
565 | ORDER BY DATE_FORMAT( login_date, '%w' ) "; |
||
566 | break; |
||
567 | } |
||
568 | |||
569 | $content = ''; |
||
570 | if ($sql_last_x) { |
||
571 | $res_last_x = Database::query($sql_last_x); |
||
572 | $result_last_x = []; |
||
573 | while ($obj = Database::fetch_object($res_last_x)) { |
||
574 | $stat_date = ($type === 'day') ? $periodCollection[$obj->stat_date] : $obj->stat_date; |
||
575 | $result_last_x[$stat_date] = $obj->number_of_logins; |
||
576 | } |
||
577 | $content .= self::printStats(get_lang('LastLogins').' ('.$period.')', $result_last_x, true); |
||
578 | flush(); //flush web request at this point to see something already while the full data set is loading |
||
579 | $content .= '<br />'; |
||
580 | } |
||
581 | $res = Database::query($sql); |
||
582 | $result = []; |
||
583 | while ($obj = Database::fetch_object($res)) { |
||
584 | $stat_date = $obj->stat_date; |
||
585 | switch ($type) { |
||
586 | case 'month': |
||
587 | $stat_date = explode('-', $stat_date); |
||
588 | $stat_date[1] = $periodCollection[$stat_date[1] - 1]; |
||
589 | $stat_date = implode(' ', $stat_date); |
||
590 | break; |
||
591 | case 'day': |
||
592 | $stat_date = $periodCollection[$stat_date]; |
||
593 | break; |
||
594 | } |
||
595 | $result[$stat_date] = $obj->number_of_logins; |
||
596 | } |
||
597 | $content .= self::printStats(get_lang('AllLogins').' ('.$period.')', $result, true); |
||
598 | |||
599 | return $content; |
||
600 | } |
||
601 | |||
602 | /** |
||
603 | * Print the number of recent logins. |
||
604 | * |
||
605 | * @param bool $distinct whether to only give distinct users stats, or *all* logins |
||
606 | * @param int $sessionDuration Number of minutes a session must have lasted at a minimum to be taken into account |
||
607 | * @param array $periods List of number of days we want to query (default: [1, 7, 31] for last 1 day, last 7 days, last 31 days) |
||
608 | * |
||
609 | * @throws Exception |
||
610 | * |
||
611 | * @return string HTML table |
||
612 | */ |
||
613 | public static function printRecentLoginStats($distinct = false, $sessionDuration = 0, $periods = []) |
||
614 | { |
||
615 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
616 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
617 | $urlId = api_get_current_access_url_id(); |
||
618 | $table_url = ''; |
||
619 | $where_url = ''; |
||
620 | if (api_is_multiple_url_enabled()) { |
||
621 | $table_url = ", $access_url_rel_user_table"; |
||
622 | $where_url = " AND login_user_id=user_id AND access_url_id='".$urlId."'"; |
||
623 | } |
||
624 | |||
625 | $now = api_get_utc_datetime(); |
||
626 | $field = 'login_id'; |
||
627 | if ($distinct) { |
||
628 | $field = 'DISTINCT(login_user_id)'; |
||
629 | } |
||
630 | |||
631 | if (empty($periods)) { |
||
632 | $periods = [1, 7, 31]; |
||
633 | } |
||
634 | $sqlList = []; |
||
635 | |||
636 | $sessionDuration = (int) $sessionDuration * 60; // convert from minutes to seconds |
||
637 | foreach ($periods as $day) { |
||
638 | $date = new DateTime($now); |
||
639 | $startDate = $date->format('Y-m-d').' 00:00:00'; |
||
640 | $endDate = $date->format('Y-m-d').' 23:59:59'; |
||
641 | |||
642 | if ($day > 1) { |
||
643 | $startDate = $date->sub(new DateInterval('P'.$day.'D')); |
||
644 | $startDate = $startDate->format('Y-m-d').' 00:00:00'; |
||
645 | } |
||
646 | |||
647 | $localDate = api_get_local_time($startDate, null, null, false, false); |
||
648 | $localEndDate = api_get_local_time($endDate, null, null, false, false); |
||
649 | |||
650 | $label = sprintf(get_lang('LastXDays'), $day); |
||
651 | if ($day == 1) { |
||
652 | $label = get_lang('Today'); |
||
653 | } |
||
654 | $label .= " <span class=\"muted right\" style=\"float: right; margin-right: 5px;\">[$localDate - $localEndDate]</span>"; |
||
655 | $sql = "SELECT count($field) AS number |
||
656 | FROM $table $table_url |
||
657 | WHERE "; |
||
658 | if ($sessionDuration == 0) { |
||
659 | $sql .= " logout_date != login_date AND "; |
||
660 | } else { |
||
661 | $sql .= " UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration AND "; |
||
662 | } |
||
663 | $sql .= "login_date BETWEEN '$startDate' AND '$endDate' |
||
664 | $where_url"; |
||
665 | $sqlList[$label] = $sql; |
||
666 | } |
||
667 | |||
668 | $sql = "SELECT count($field) AS number |
||
669 | FROM $table $table_url "; |
||
670 | if ($sessionDuration == 0) { |
||
671 | $sql .= " WHERE logout_date != login_date $where_url"; |
||
672 | } else { |
||
673 | $sql .= " WHERE UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration $where_url"; |
||
674 | } |
||
675 | $sqlList[get_lang('Total')] = $sql; |
||
676 | $totalLogin = []; |
||
677 | foreach ($sqlList as $label => $query) { |
||
678 | $res = Database::query($query); |
||
679 | $obj = Database::fetch_object($res); |
||
680 | $totalLogin[$label] = $obj->number; |
||
681 | } |
||
682 | |||
683 | if ($distinct) { |
||
684 | $content = self::printStats(get_lang('DistinctUsersLogins'), $totalLogin, false); |
||
685 | } else { |
||
686 | $content = self::printStats(get_lang('Logins'), $totalLogin, false); |
||
687 | } |
||
688 | |||
689 | return $content; |
||
690 | } |
||
691 | |||
692 | /** |
||
693 | * Get the number of recent logins. |
||
694 | * |
||
695 | * @param bool $distinct Whether to only give distinct users stats, or *all* logins |
||
696 | * @param int $sessionDuration Number of minutes a session must have lasted at a minimum to be taken into account |
||
697 | * @param bool $completeMissingDays Whether to fill the daily gaps (if any) when getting a list of logins |
||
698 | * |
||
699 | * @throws Exception |
||
700 | * |
||
701 | * @return array |
||
702 | */ |
||
703 | public static function getRecentLoginStats($distinct = false, $sessionDuration = 0, $completeMissingDays = true) |
||
704 | { |
||
705 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
706 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
707 | $urlId = api_get_current_access_url_id(); |
||
708 | $table_url = ''; |
||
709 | $where_url = ''; |
||
710 | if (api_is_multiple_url_enabled()) { |
||
711 | $table_url = ", $access_url_rel_user_table"; |
||
712 | $where_url = " AND login_user_id=user_id AND access_url_id='".$urlId."'"; |
||
713 | } |
||
714 | |||
715 | $now = api_get_utc_datetime(); |
||
716 | $date = new DateTime($now); |
||
717 | $date->sub(new DateInterval('P31D')); |
||
718 | $newDate = $date->format('Y-m-d h:i:s'); |
||
719 | $totalLogin = self::buildDatesArray($newDate, $now, true); |
||
720 | |||
721 | $field = 'login_id'; |
||
722 | if ($distinct) { |
||
723 | $field = 'DISTINCT(login_user_id)'; |
||
724 | } |
||
725 | $sessionDuration = (int) $sessionDuration * 60; //Convert from minutes to seconds |
||
726 | |||
727 | $sql = "SELECT count($field) AS number, date(login_date) as login_date |
||
728 | FROM $table $table_url |
||
729 | WHERE "; |
||
730 | if ($sessionDuration == 0) { |
||
731 | $sql .= " logout_date != login_date AND "; |
||
732 | } else { |
||
733 | $sql .= " UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration AND "; |
||
734 | } |
||
735 | $sql .= " login_date >= '$newDate' $where_url |
||
736 | GROUP BY date(login_date)"; |
||
737 | |||
738 | $res = Database::query($sql); |
||
739 | while ($row = Database::fetch_array($res, 'ASSOC')) { |
||
740 | $monthAndDay = substr($row['login_date'], 5, 5); |
||
741 | $totalLogin[$monthAndDay] = $row['number']; |
||
742 | } |
||
743 | |||
744 | return $totalLogin; |
||
745 | } |
||
746 | |||
747 | /** |
||
748 | * Get course tools usage statistics for the whole platform (by URL if multi-url). |
||
749 | */ |
||
750 | public static function getToolsStats() |
||
751 | { |
||
752 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS); |
||
753 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
754 | $urlId = api_get_current_access_url_id(); |
||
755 | |||
756 | $tools = [ |
||
757 | 'announcement', |
||
758 | 'assignment', |
||
759 | 'calendar_event', |
||
760 | 'chat', |
||
761 | 'course_description', |
||
762 | 'document', |
||
763 | 'dropbox', |
||
764 | 'group', |
||
765 | 'learnpath', |
||
766 | 'link', |
||
767 | 'quiz', |
||
768 | 'student_publication', |
||
769 | 'user', |
||
770 | 'forum', |
||
771 | ]; |
||
772 | $tool_names = []; |
||
773 | foreach ($tools as $tool) { |
||
774 | $tool_names[$tool] = get_lang(ucfirst($tool), ''); |
||
775 | } |
||
776 | if (api_is_multiple_url_enabled()) { |
||
777 | $sql = "SELECT access_tool, count( access_id ) AS number_of_logins |
||
778 | FROM $table t , $access_url_rel_course_table a |
||
779 | WHERE |
||
780 | access_tool IN ('".implode("','", $tools)."') AND |
||
781 | t.c_id = a.c_id AND |
||
782 | access_url_id='".$urlId."' |
||
783 | GROUP BY access_tool |
||
784 | "; |
||
785 | } else { |
||
786 | $sql = "SELECT access_tool, count( access_id ) AS number_of_logins |
||
787 | FROM $table |
||
788 | WHERE access_tool IN ('".implode("','", $tools)."') |
||
789 | GROUP BY access_tool "; |
||
790 | } |
||
791 | |||
792 | $res = Database::query($sql); |
||
793 | $result = []; |
||
794 | while ($obj = Database::fetch_object($res)) { |
||
795 | $result[$tool_names[$obj->access_tool]] = $obj->number_of_logins; |
||
796 | } |
||
797 | |||
798 | return $result; |
||
799 | } |
||
800 | |||
801 | /** |
||
802 | * Show some stats about the accesses to the different course tools. |
||
803 | * |
||
804 | * @param array $result If defined, this serves as data. Otherwise, will get the data from getToolsStats() |
||
805 | * |
||
806 | * @return string HTML table |
||
807 | */ |
||
808 | public static function printToolStats($result = null) |
||
809 | { |
||
810 | if (empty($result)) { |
||
811 | $result = self::getToolsStats(); |
||
812 | } |
||
813 | |||
814 | return self::printStats(get_lang('PlatformToolAccess'), $result, true); |
||
815 | } |
||
816 | |||
817 | /** |
||
818 | * Show some stats about the number of courses per language. |
||
819 | */ |
||
820 | public static function printCourseByLanguageStats() |
||
821 | { |
||
822 | $table = Database::get_main_table(TABLE_MAIN_COURSE); |
||
823 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
824 | $urlId = api_get_current_access_url_id(); |
||
825 | if (api_is_multiple_url_enabled()) { |
||
826 | $sql = "SELECT course_language, count( c.code ) AS number_of_courses |
||
827 | FROM $table as c, $access_url_rel_course_table as u |
||
828 | WHERE u.c_id = c.id AND access_url_id='".$urlId."' |
||
829 | GROUP BY course_language |
||
830 | ORDER BY number_of_courses DESC"; |
||
831 | } else { |
||
832 | $sql = "SELECT course_language, count( code ) AS number_of_courses |
||
833 | FROM $table GROUP BY course_language |
||
834 | ORDER BY number_of_courses DESC"; |
||
835 | } |
||
836 | $res = Database::query($sql); |
||
837 | $result = []; |
||
838 | while ($obj = Database::fetch_object($res)) { |
||
839 | $result[$obj->course_language] = $obj->number_of_courses; |
||
840 | } |
||
841 | |||
842 | return $result; |
||
843 | } |
||
844 | |||
845 | /** |
||
846 | * Shows the number of users having their picture uploaded in Dokeos. |
||
847 | */ |
||
848 | public static function printUserPicturesStats() |
||
849 | { |
||
850 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
851 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
852 | $urlId = api_get_current_access_url_id(); |
||
853 | $url_condition = null; |
||
854 | $url_condition2 = null; |
||
855 | $table = null; |
||
856 | if (api_is_multiple_url_enabled()) { |
||
857 | $url_condition = ", $access_url_rel_user_table as url WHERE url.user_id=u.user_id AND access_url_id='".$urlId."'"; |
||
858 | $url_condition2 = " AND url.user_id=u.user_id AND access_url_id='".$urlId."'"; |
||
859 | $table = ", $access_url_rel_user_table as url "; |
||
860 | } |
||
861 | $sql = "SELECT COUNT(*) AS n FROM $user_table as u ".$url_condition; |
||
862 | $res = Database::query($sql); |
||
863 | $count1 = Database::fetch_object($res); |
||
864 | $sql = "SELECT COUNT(*) AS n FROM $user_table as u $table ". |
||
865 | "WHERE LENGTH(picture_uri) > 0 $url_condition2"; |
||
866 | $res = Database::query($sql); |
||
867 | $count2 = Database::fetch_object($res); |
||
868 | // #users without picture |
||
869 | $result[get_lang('No')] = $count1->n - $count2->n; |
||
870 | $result[get_lang('Yes')] = $count2->n; // #users with picture |
||
871 | |||
872 | return self::printStats(get_lang('CountUsers').' ('.get_lang('UserPicture').')', $result, true); |
||
873 | } |
||
874 | |||
875 | /** |
||
876 | * Important activities. |
||
877 | */ |
||
878 | public static function printActivitiesStats() |
||
879 | { |
||
880 | $content = '<h4>'.get_lang('ImportantActivities').'</h4>'; |
||
881 | // Create a search-box |
||
882 | $form = new FormValidator( |
||
883 | 'search_simple', |
||
884 | 'get', |
||
885 | api_get_path(WEB_CODE_PATH).'admin/statistics/index.php', |
||
886 | '', |
||
887 | 'width=200px', |
||
888 | false |
||
889 | ); |
||
890 | $renderer = &$form->defaultRenderer(); |
||
891 | $renderer->setCustomElementTemplate('<span>{element}</span> '); |
||
892 | $form->addHidden('report', 'activities'); |
||
893 | $form->addHidden('activities_direction', 'DESC'); |
||
894 | $form->addHidden('activities_column', '4'); |
||
895 | $form->addElement('text', 'keyword', get_lang('Keyword')); |
||
896 | $form->addButtonSearch(get_lang('Search'), 'submit'); |
||
897 | $content .= '<div class="actions">'; |
||
898 | $content .= $form->returnForm(); |
||
899 | $content .= '</div>'; |
||
900 | |||
901 | $table = new SortableTable( |
||
902 | 'activities', |
||
903 | ['Statistics', 'getNumberOfActivities'], |
||
904 | ['Statistics', 'getActivitiesData'], |
||
905 | 7, |
||
906 | 50, |
||
907 | 'DESC' |
||
908 | ); |
||
909 | $parameters = []; |
||
910 | |||
911 | $parameters['report'] = 'activities'; |
||
912 | if (isset($_GET['keyword'])) { |
||
913 | $parameters['keyword'] = Security::remove_XSS($_GET['keyword']); |
||
914 | } |
||
915 | |||
916 | $table->set_additional_parameters($parameters); |
||
917 | $table->set_header(0, get_lang('EventType')); |
||
918 | $table->set_header(1, get_lang('DataType')); |
||
919 | $table->set_header(2, get_lang('Value')); |
||
920 | $table->set_header(3, get_lang('Course')); |
||
921 | $table->set_header(4, get_lang('Session')); |
||
922 | $table->set_header(5, get_lang('UserName')); |
||
923 | $table->set_header(6, get_lang('IPAddress')); |
||
924 | $table->set_header(7, get_lang('Date')); |
||
925 | $content .= $table->return_table(); |
||
926 | |||
927 | return $content; |
||
928 | } |
||
929 | |||
930 | /** |
||
931 | * Shows statistics about the time of last visit to each course. |
||
932 | */ |
||
933 | public static function printCourseLastVisit() |
||
934 | { |
||
935 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
936 | $urlId = api_get_current_access_url_id(); |
||
937 | |||
938 | $columns[0] = 't.c_id'; |
||
939 | $columns[1] = 'access_date'; |
||
940 | $sql_order[SORT_ASC] = 'ASC'; |
||
941 | $sql_order[SORT_DESC] = 'DESC'; |
||
942 | $per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 10; |
||
943 | $page_nr = isset($_GET['page_nr']) ? intval($_GET['page_nr']) : 1; |
||
944 | $column = isset($_GET['column']) ? intval($_GET['column']) : 0; |
||
945 | $direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC; |
||
946 | |||
947 | if (!in_array($direction, [SORT_ASC, SORT_DESC])) { |
||
948 | $direction = SORT_ASC; |
||
949 | } |
||
950 | $form = new FormValidator('courselastvisit', 'get'); |
||
951 | $form->addElement('hidden', 'report', 'courselastvisit'); |
||
952 | $form->addText('date_diff', get_lang('Days'), true); |
||
953 | $form->addRule('date_diff', 'InvalidNumber', 'numeric'); |
||
954 | $form->addButtonSearch(get_lang('Search'), 'submit'); |
||
955 | if (!isset($_GET['date_diff'])) { |
||
956 | $defaults['date_diff'] = 60; |
||
957 | } else { |
||
958 | $defaults['date_diff'] = Security::remove_XSS($_GET['date_diff']); |
||
959 | } |
||
960 | $form->setDefaults($defaults); |
||
961 | $content = $form->returnForm(); |
||
962 | |||
963 | $values = $form->exportValues(); |
||
964 | $date_diff = $values['date_diff']; |
||
965 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS); |
||
966 | if (api_is_multiple_url_enabled()) { |
||
967 | $sql = "SELECT * FROM $table t , $access_url_rel_course_table a |
||
968 | WHERE |
||
969 | t.c_id = a.c_id AND |
||
970 | access_url_id='".$urlId."' |
||
971 | GROUP BY t.c_id |
||
972 | HAVING t.c_id <> '' |
||
973 | AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff; |
||
974 | } else { |
||
975 | $sql = "SELECT * FROM $table t |
||
976 | GROUP BY t.c_id |
||
977 | HAVING t.c_id <> '' |
||
978 | AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff; |
||
979 | } |
||
980 | $sql .= ' ORDER BY `'.$columns[$column].'` '.$sql_order[$direction]; |
||
981 | $from = ($page_nr - 1) * $per_page; |
||
982 | $sql .= ' LIMIT '.$from.','.$per_page; |
||
983 | |||
984 | $content .= '<p>'.get_lang('LastAccess').' >= '.$date_diff.' '.get_lang('Days').'</p>'; |
||
985 | $res = Database::query($sql); |
||
986 | if (Database::num_rows($res) > 0) { |
||
987 | $courses = []; |
||
988 | while ($obj = Database::fetch_object($res)) { |
||
989 | $courseInfo = api_get_course_info_by_id($obj->c_id); |
||
990 | $course = []; |
||
991 | $course[] = '<a href="'.api_get_path(WEB_COURSE_PATH).$courseInfo['code'].'">'.$courseInfo['code'].' <a>'; |
||
992 | // Allow sort by date hiding the numerical date |
||
993 | $course[] = '<span style="display:none;">'.$obj->access_date.'</span>'.api_convert_and_format_date($obj->access_date); |
||
994 | $courses[] = $course; |
||
995 | } |
||
996 | $parameters['date_diff'] = $date_diff; |
||
997 | $parameters['report'] = 'courselastvisit'; |
||
998 | $table_header[] = [get_lang("CourseCode"), true]; |
||
0 ignored issues
–
show
Comprehensibility
Best Practice
introduced
by
Loading history...
|
|||
999 | $table_header[] = [get_lang("LastAccess"), true]; |
||
1000 | |||
1001 | ob_start(); |
||
1002 | Display:: display_sortable_table( |
||
1003 | $table_header, |
||
1004 | $courses, |
||
1005 | ['column' => $column, 'direction' => $direction], |
||
1006 | [], |
||
1007 | $parameters |
||
1008 | ); |
||
1009 | $content .= ob_get_contents(); |
||
1010 | ob_end_clean(); |
||
1011 | } else { |
||
1012 | $content = get_lang('NoSearchResults'); |
||
1013 | } |
||
1014 | |||
1015 | return $content; |
||
1016 | } |
||
1017 | |||
1018 | /** |
||
1019 | * Displays the statistics of the messages sent and received by each user in the social network. |
||
1020 | * |
||
1021 | * @param string $messageType Type of message: 'sent' or 'received' |
||
1022 | * |
||
1023 | * @return array Message list |
||
1024 | */ |
||
1025 | public static function getMessages($messageType) |
||
1026 | { |
||
1027 | $message_table = Database::get_main_table(TABLE_MESSAGE); |
||
1028 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
1029 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
1030 | |||
1031 | $urlId = api_get_current_access_url_id(); |
||
1032 | |||
1033 | switch ($messageType) { |
||
1034 | case 'sent': |
||
1035 | $field = 'user_sender_id'; |
||
1036 | break; |
||
1037 | case 'received': |
||
1038 | $field = 'user_receiver_id'; |
||
1039 | break; |
||
1040 | } |
||
1041 | |||
1042 | if (api_is_multiple_url_enabled()) { |
||
1043 | $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message |
||
1044 | FROM $access_url_rel_user_table as url, $message_table m |
||
1045 | LEFT JOIN $user_table u ON m.$field = u.user_id |
||
1046 | WHERE url.user_id = m.$field AND access_url_id='".$urlId."' |
||
1047 | GROUP BY m.$field |
||
1048 | ORDER BY count_message DESC "; |
||
1049 | } else { |
||
1050 | $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message |
||
1051 | FROM $message_table m |
||
1052 | LEFT JOIN $user_table u ON m.$field = u.user_id |
||
1053 | GROUP BY m.$field ORDER BY count_message DESC "; |
||
1054 | } |
||
1055 | $res = Database::query($sql); |
||
1056 | $messages_sent = []; |
||
1057 | while ($messages = Database::fetch_array($res)) { |
||
1058 | if (empty($messages['username'])) { |
||
1059 | $messages['username'] = get_lang('Unknown'); |
||
1060 | } |
||
1061 | $users = api_get_person_name( |
||
1062 | $messages['firstname'], |
||
1063 | $messages['lastname'] |
||
1064 | ).'<br />('.$messages['username'].')'; |
||
1065 | $messages_sent[$users] = $messages['count_message']; |
||
1066 | } |
||
1067 | |||
1068 | return $messages_sent; |
||
1069 | } |
||
1070 | |||
1071 | /** |
||
1072 | * Count the number of friends for social network users. |
||
1073 | */ |
||
1074 | public static function getFriends() |
||
1075 | { |
||
1076 | $user_friend_table = Database::get_main_table(TABLE_MAIN_USER_REL_USER); |
||
1077 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
1078 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
1079 | $urlId = api_get_current_access_url_id(); |
||
1080 | |||
1081 | if (api_is_multiple_url_enabled()) { |
||
1082 | $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend |
||
1083 | FROM $access_url_rel_user_table as url, $user_friend_table uf |
||
1084 | LEFT JOIN $user_table u |
||
1085 | ON (uf.user_id = u.user_id) |
||
1086 | WHERE |
||
1087 | uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' AND |
||
1088 | uf.user_id = url.user_id AND |
||
1089 | access_url_id = '".$urlId."' |
||
1090 | GROUP BY uf.user_id |
||
1091 | ORDER BY count_friend DESC "; |
||
1092 | } else { |
||
1093 | $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend |
||
1094 | FROM $user_friend_table uf |
||
1095 | LEFT JOIN $user_table u |
||
1096 | ON (uf.user_id = u.user_id) |
||
1097 | WHERE uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' |
||
1098 | GROUP BY uf.user_id |
||
1099 | ORDER BY count_friend DESC "; |
||
1100 | } |
||
1101 | $res = Database::query($sql); |
||
1102 | $list_friends = []; |
||
1103 | while ($friends = Database::fetch_array($res)) { |
||
1104 | $users = api_get_person_name($friends['firstname'], $friends['lastname']).'<br />('.$friends['username'].')'; |
||
1105 | $list_friends[$users] = $friends['count_friend']; |
||
1106 | } |
||
1107 | |||
1108 | return $list_friends; |
||
1109 | } |
||
1110 | |||
1111 | /** |
||
1112 | * Print the number of users that didn't login for a certain period of time. |
||
1113 | */ |
||
1114 | public static function printUsersNotLoggedInStats() |
||
1115 | { |
||
1116 | $totalLogin = []; |
||
1117 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
1118 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
1119 | $urlId = api_get_current_access_url_id(); |
||
1120 | $total = self::countUsers(); |
||
1121 | if (api_is_multiple_url_enabled()) { |
||
1122 | $table_url = ", $access_url_rel_user_table"; |
||
1123 | $where_url = " AND login_user_id=user_id AND access_url_id='".$urlId."'"; |
||
1124 | } else { |
||
1125 | $table_url = ''; |
||
1126 | $where_url = ''; |
||
1127 | } |
||
1128 | $now = api_get_utc_datetime(); |
||
1129 | $sql[get_lang('ThisDay')] = |
||
1130 | "SELECT count(distinct(login_user_id)) AS number ". |
||
1131 | " FROM $table $table_url ". |
||
1132 | " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= '$now' $where_url"; |
||
1133 | $sql[get_lang('Last7days')] = |
||
1134 | "SELECT count(distinct(login_user_id)) AS number ". |
||
1135 | " FROM $table $table_url ". |
||
1136 | " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= '$now' $where_url"; |
||
1137 | $sql[get_lang('Last31days')] = |
||
1138 | "SELECT count(distinct(login_user_id)) AS number ". |
||
1139 | " FROM $table $table_url ". |
||
1140 | " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= '$now' $where_url"; |
||
1141 | $sql[sprintf(get_lang('LastXMonths'), 6)] = |
||
1142 | "SELECT count(distinct(login_user_id)) AS number ". |
||
1143 | " FROM $table $table_url ". |
||
1144 | " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= '$now' $where_url"; |
||
1145 | $sql[get_lang('NeverConnected')] = |
||
1146 | "SELECT count(distinct(login_user_id)) AS number ". |
||
1147 | " FROM $table $table_url WHERE 1=1 $where_url"; |
||
1148 | foreach ($sql as $index => $query) { |
||
1149 | $res = Database::query($query); |
||
1150 | $obj = Database::fetch_object($res); |
||
1151 | $r = $total - $obj->number; |
||
1152 | $totalLogin[$index] = $r < 0 ? 0 : $r; |
||
1153 | } |
||
1154 | |||
1155 | return self::printStats( |
||
1156 | get_lang('StatsUsersDidNotLoginInLastPeriods'), |
||
1157 | $totalLogin, |
||
1158 | false |
||
1159 | ); |
||
1160 | } |
||
1161 | |||
1162 | /** |
||
1163 | * Returns an array with indexes as the 'yyyy-mm-dd' format of each date |
||
1164 | * within the provided range (including limits). Dates are assumed to be |
||
1165 | * given in UTC. |
||
1166 | * |
||
1167 | * @param string $startDate Start date, in Y-m-d or Y-m-d h:i:s format |
||
1168 | * @param string $endDate End date, in Y-m-d or Y-m-d h:i:s format |
||
1169 | * @param bool $removeYear Whether to remove the year in the results (for easier reading) |
||
1170 | * |
||
1171 | * @return array|bool False on error in the params, array of [date1 => 0, date2 => 0, ...] otherwise |
||
1172 | */ |
||
1173 | public static function buildDatesArray($startDate, $endDate, $removeYear = false) |
||
1174 | { |
||
1175 | if (strlen($startDate) > 10) { |
||
1176 | $startDate = substr($startDate, 0, 10); |
||
1177 | } |
||
1178 | if (strlen($endDate) > 10) { |
||
1179 | $endDate = substr($endDate, 0, 10); |
||
1180 | } |
||
1181 | if (!preg_match('/\d\d\d\d-\d\d-\d\d/', $startDate)) { |
||
1182 | return false; |
||
1183 | } |
||
1184 | if (!preg_match('/\d\d\d\d-\d\d-\d\d/', $startDate)) { |
||
1185 | return false; |
||
1186 | } |
||
1187 | $startTimestamp = strtotime($startDate); |
||
1188 | $endTimestamp = strtotime($endDate); |
||
1189 | $list = []; |
||
1190 | for ($time = $startTimestamp; $time < $endTimestamp; $time += 86400) { |
||
1191 | $datetime = api_get_utc_datetime($time); |
||
1192 | if ($removeYear) { |
||
1193 | $datetime = substr($datetime, 5, 5); |
||
1194 | } else { |
||
1195 | $dateTime = substr($datetime, 0, 10); |
||
1196 | } |
||
1197 | $list[$datetime] = 0; |
||
1198 | } |
||
1199 | |||
1200 | return $list; |
||
1201 | } |
||
1202 | |||
1203 | /** |
||
1204 | * Prepare the JS code to load a chart. |
||
1205 | * |
||
1206 | * @param string $url URL for AJAX data generator |
||
1207 | * @param string $type bar, line, pie, etc |
||
1208 | * @param string $options Additional options to the chart (see chart-specific library) |
||
1209 | * @param string A JS code for loading the chart together with a call to AJAX data generator |
||
1210 | */ |
||
1211 | public static function getJSChartTemplate($url, $type = 'pie', $options = '', $elementId = 'canvas') |
||
1212 | { |
||
1213 | $chartCode = ' |
||
1214 | <script> |
||
1215 | $(function() { |
||
1216 | $.ajax({ |
||
1217 | url: "'.$url.'", |
||
1218 | type: "POST", |
||
1219 | success: function(data) { |
||
1220 | Chart.defaults.global.responsive = true; |
||
1221 | var ctx = document.getElementById("'.$elementId.'").getContext("2d"); |
||
1222 | var chart = new Chart(ctx, { |
||
1223 | type: "'.$type.'", |
||
1224 | data: data, |
||
1225 | options: {'.$options.'} |
||
1226 | }); |
||
1227 | var title = chart.options.title.text; |
||
1228 | $("#'.$elementId.'_title").html(title); |
||
1229 | $("#'.$elementId.'_table").html(data.table); |
||
1230 | } |
||
1231 | }); |
||
1232 | }); |
||
1233 | </script>'; |
||
1234 | |||
1235 | return $chartCode; |
||
1236 | } |
||
1237 | |||
1238 | public static function getJSChartTemplateWithData($data, $type = 'pie', $options = '', $elementId = 'canvas') |
||
1239 | { |
||
1240 | $data = json_encode($data); |
||
1241 | $chartCode = ' |
||
1242 | <script> |
||
1243 | $(function() { |
||
1244 | Chart.defaults.global.responsive = true; |
||
1245 | var ctx = document.getElementById("'.$elementId.'").getContext("2d"); |
||
1246 | var chart = new Chart(ctx, { |
||
1247 | type: "'.$type.'", |
||
1248 | data: '.$data.', |
||
1249 | options: {'.$options.'} |
||
1250 | }); |
||
1251 | }); |
||
1252 | </script>'; |
||
1253 | |||
1254 | return $chartCode; |
||
1255 | } |
||
1256 | |||
1257 | public static function buildJsChartData($all, $chartName) |
||
1258 | { |
||
1259 | $list = []; |
||
1260 | $palette = ChamiloApi::getColorPalette(true, true); |
||
1261 | foreach ($all as $tick => $tock) { |
||
1262 | $list['labels'][] = $tick; |
||
1263 | } |
||
1264 | |||
1265 | $list['datasets'][0]['label'] = $chartName; |
||
1266 | $list['datasets'][0]['borderColor'] = 'rgba(255,255,255,1)'; |
||
1267 | |||
1268 | $i = 0; |
||
1269 | foreach ($all as $tick => $tock) { |
||
1270 | $j = $i % count($palette); |
||
1271 | $list['datasets'][0]['data'][] = $tock; |
||
1272 | $list['datasets'][0]['backgroundColor'][] = $palette[$j]; |
||
1273 | $i++; |
||
1274 | } |
||
1275 | |||
1276 | $scoreDisplay = ScoreDisplay::instance(); |
||
1277 | $table = new HTML_Table(['class' => 'table table-hover table-striped data_table']); |
||
1278 | $headers = [ |
||
1279 | get_lang('Name'), |
||
1280 | get_lang('Count'), |
||
1281 | get_lang('Percentage'), |
||
1282 | ]; |
||
1283 | $row = 0; |
||
1284 | $column = 0; |
||
1285 | foreach ($headers as $header) { |
||
1286 | $table->setHeaderContents($row, $column, $header); |
||
1287 | $column++; |
||
1288 | } |
||
1289 | |||
1290 | $total = 0; |
||
1291 | foreach ($all as $name => $value) { |
||
1292 | $total += $value; |
||
1293 | } |
||
1294 | $row++; |
||
1295 | foreach ($all as $name => $value) { |
||
1296 | $table->setCellContents($row, 0, $name); |
||
1297 | $table->setCellContents($row, 1, $value); |
||
1298 | $table->setCellContents($row, 2, $scoreDisplay->display_score([$value, $total], SCORE_PERCENT)); |
||
1299 | $row++; |
||
1300 | } |
||
1301 | $table = Display::page_subheader2($chartName).$table->toHtml(); |
||
1302 | |||
1303 | return ['chart' => $list, 'table' => $table]; |
||
1304 | } |
||
1305 | |||
1306 | /** |
||
1307 | * Display the Logins By Date report and allow export its result to XLS. |
||
1308 | */ |
||
1309 | public static function printLoginsByDate() |
||
1310 | { |
||
1311 | if (isset($_GET['export']) && 'xls' === $_GET['export']) { |
||
1312 | $result = self::getLoginsByDate($_GET['start'], $_GET['end']); |
||
1313 | $data = [[get_lang('Username'), get_lang('FirstName'), get_lang('LastName'), get_lang('TotalTime')]]; |
||
1314 | |||
1315 | foreach ($result as $i => $item) { |
||
1316 | $data[] = [ |
||
1317 | $item['username'], |
||
1318 | $item['firstname'], |
||
1319 | $item['lastname'], |
||
1320 | api_time_to_hms($item['time_count']), |
||
1321 | ]; |
||
1322 | } |
||
1323 | |||
1324 | Export::arrayToXls($data); |
||
1325 | exit; |
||
1326 | } |
||
1327 | |||
1328 | $content = Display::page_header(get_lang('LoginsByDate')); |
||
1329 | |||
1330 | $actions = ''; |
||
1331 | $form = new FormValidator('frm_logins_by_date', 'get'); |
||
1332 | $form->addDateRangePicker( |
||
1333 | 'daterange', |
||
1334 | get_lang('DateRange'), |
||
1335 | true, |
||
1336 | ['format' => 'YYYY-MM-DD', 'timePicker' => 'false', 'validate_format' => 'Y-m-d'] |
||
1337 | ); |
||
1338 | $form->addHidden('report', 'logins_by_date'); |
||
1339 | $form->addButtonFilter(get_lang('Search')); |
||
1340 | |||
1341 | if ($form->validate()) { |
||
1342 | $values = $form->exportValues(); |
||
1343 | |||
1344 | $result = self::getLoginsByDate($values['daterange_start'], $values['daterange_end']); |
||
1345 | |||
1346 | if (!empty($result)) { |
||
1347 | $actions = Display::url( |
||
1348 | Display::return_icon('excel.png', get_lang('ExportToXls'), [], ICON_SIZE_MEDIUM), |
||
1349 | api_get_self().'?'.http_build_query( |
||
1350 | [ |
||
1351 | 'report' => 'logins_by_date', |
||
1352 | 'export' => 'xls', |
||
1353 | 'start' => Security::remove_XSS($values['daterange_start']), |
||
1354 | 'end' => Security::remove_XSS($values['daterange_end']), |
||
1355 | ] |
||
1356 | ) |
||
1357 | ); |
||
1358 | } |
||
1359 | |||
1360 | $table = new HTML_Table(['class' => 'table table-hover table-striped data_table']); |
||
1361 | $table->setHeaderContents(0, 0, get_lang('Username')); |
||
1362 | $table->setHeaderContents(0, 1, get_lang('FirstName')); |
||
1363 | $table->setHeaderContents(0, 2, get_lang('LastName')); |
||
1364 | $table->setHeaderContents(0, 3, get_lang('TotalTime')); |
||
1365 | |||
1366 | foreach ($result as $i => $item) { |
||
1367 | $table->setCellContents($i + 1, 0, $item['username']); |
||
1368 | $table->setCellContents($i + 1, 1, $item['firstname']); |
||
1369 | $table->setCellContents($i + 1, 2, $item['lastname']); |
||
1370 | $table->setCellContents($i + 1, 3, api_time_to_hms($item['time_count'])); |
||
1371 | } |
||
1372 | |||
1373 | $table->setColAttributes(0, ['class' => 'text-center']); |
||
1374 | $table->setColAttributes(3, ['class' => 'text-center']); |
||
1375 | $content = $table->toHtml(); |
||
1376 | } |
||
1377 | |||
1378 | $content .= $form->returnForm(); |
||
1379 | |||
1380 | if (!empty($actions)) { |
||
1381 | $content .= Display::toolbarAction('logins_by_date_toolbar', [$actions]); |
||
1382 | } |
||
1383 | |||
1384 | return $content; |
||
1385 | } |
||
1386 | |||
1387 | public static function getBossTable($bossId) |
||
1388 | { |
||
1389 | $students = UserManager::getUsersFollowedByStudentBoss($bossId); |
||
1390 | |||
1391 | if (!empty($students)) { |
||
1392 | $table = new HTML_Table(['class' => 'table table-responsive', 'id' => 'table_'.$bossId]); |
||
1393 | $headers = [ |
||
1394 | get_lang('Name'), |
||
1395 | //get_lang('LastName'), |
||
1396 | ]; |
||
1397 | $row = 0; |
||
1398 | $column = 0; |
||
1399 | foreach ($headers as $header) { |
||
1400 | $table->setHeaderContents($row, $column, $header); |
||
1401 | $column++; |
||
1402 | } |
||
1403 | $row++; |
||
1404 | foreach ($students as $student) { |
||
1405 | $column = 0; |
||
1406 | $content = api_get_person_name($student['firstname'], $student['lastname']).''; |
||
1407 | $content = '<div style="width: 200px; overflow-wrap: break-word;">'.$content.'</div>'; |
||
1408 | $table->setCellContents( |
||
1409 | $row, |
||
1410 | $column++, |
||
1411 | $content |
||
1412 | ); |
||
1413 | $row++; |
||
1414 | } |
||
1415 | |||
1416 | return $table->toHtml(); |
||
1417 | } |
||
1418 | |||
1419 | return '<table id="table_'.$bossId.'"></table>'; |
||
1420 | } |
||
1421 | |||
1422 | /** |
||
1423 | * @param string $startDate |
||
1424 | * @param string $endDate |
||
1425 | * |
||
1426 | * @return array |
||
1427 | */ |
||
1428 | private static function getLoginsByDate($startDate, $endDate) |
||
1429 | { |
||
1430 | /** @var DateTime $startDate */ |
||
1431 | $startDate = api_get_utc_datetime("$startDate 00:00:00"); |
||
1432 | /** @var DateTime $endDate */ |
||
1433 | $endDate = api_get_utc_datetime("$endDate 23:59:59"); |
||
1434 | |||
1435 | if (empty($startDate) || empty($endDate)) { |
||
1436 | return []; |
||
1437 | } |
||
1438 | |||
1439 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
1440 | $tblLogin = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
1441 | $urlJoin = ''; |
||
1442 | $urlWhere = ''; |
||
1443 | |||
1444 | if (api_is_multiple_url_enabled()) { |
||
1445 | $tblUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
1446 | |||
1447 | $urlJoin = "INNER JOIN $tblUrlUser au ON u.id = au.user_id"; |
||
1448 | $urlWhere = 'AND au.access_url_id = '.api_get_current_access_url_id(); |
||
1449 | } |
||
1450 | |||
1451 | $sql = "SELECT u.id, |
||
1452 | u.firstname, |
||
1453 | u.lastname, |
||
1454 | u.username, |
||
1455 | SUM(TIMESTAMPDIFF(SECOND, l.login_date, l.logout_date)) AS time_count |
||
1456 | FROM $tblUser u |
||
1457 | INNER JOIN $tblLogin l ON u.id = l.login_user_id |
||
1458 | $urlJoin |
||
1459 | WHERE l.login_date BETWEEN '$startDate' AND '$endDate' |
||
1460 | $urlWhere |
||
1461 | GROUP BY u.id"; |
||
1462 | |||
1463 | $stmt = Database::query($sql); |
||
1464 | $result = Database::store_result($stmt, 'ASSOC'); |
||
1465 | |||
1466 | return $result; |
||
1467 | } |
||
1468 | } |
||
1469 |