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