chamilo /
chamilo-lms
| Total Complexity | 229 |
| Total Lines | 2164 |
| Duplicated Lines | 0 % |
| Changes | 7 | ||
| Bugs | 0 | Features | 1 |
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 |
||
| 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) |
||
| 31 | } |
||
| 32 | } |
||
| 33 | |||
| 34 | /** |
||
| 35 | * Count courses. |
||
| 36 | * |
||
| 37 | * @param string|null $categoryCode Code of a course category. |
||
| 38 | * Default: count all courses. |
||
| 39 | * @param string|null $dateFrom dateFrom |
||
| 40 | * @param string|null $dateUntil dateUntil |
||
| 41 | * |
||
| 42 | * @return int Number of courses counted |
||
| 43 | */ |
||
| 44 | public static function countCourses(string $categoryCode = null, string $dateFrom = null, string $dateUntil = null) |
||
| 78 | } |
||
| 79 | |||
| 80 | /** |
||
| 81 | * Count courses by visibility. |
||
| 82 | * |
||
| 83 | * @param array|null $visibility visibility (0 = closed, 1 = private, 2 = open, 3 = public) all courses |
||
| 84 | * @param string|null $dateFrom dateFrom |
||
| 85 | * @param string|null $dateUntil dateUntil |
||
| 86 | * |
||
| 87 | * @return int Number of courses counted |
||
| 88 | */ |
||
| 89 | public static function countCoursesByVisibility( |
||
| 90 | array $visibility = null, |
||
| 91 | string $dateFrom = null, |
||
| 92 | string $dateUntil = null |
||
| 93 | ) { |
||
| 94 | if (empty($visibility)) { |
||
| 95 | return 0; |
||
| 96 | } else { |
||
| 97 | $visibilityString = ''; |
||
| 98 | $auxArrayVisibility = []; |
||
| 99 | if (!is_array($visibility)) { |
||
| 100 | $visibility = [$visibility]; |
||
| 101 | } |
||
| 102 | foreach ($visibility as $item) { |
||
| 103 | $auxArrayVisibility[] = (int) $item; |
||
| 104 | } |
||
| 105 | $visibilityString = implode(',', $auxArrayVisibility); |
||
| 106 | } |
||
| 107 | |||
| 108 | $courseTable = Database::get_main_table(TABLE_MAIN_COURSE); |
||
| 109 | $accessUrlRelCourseTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
| 110 | $urlId = api_get_current_access_url_id(); |
||
| 111 | if (api_is_multiple_url_enabled()) { |
||
| 112 | $sql = "SELECT COUNT(*) AS number |
||
| 113 | FROM $courseTable AS c, $accessUrlRelCourseTable AS u |
||
| 114 | WHERE u.c_id = c.id AND u.access_url_id = $urlId"; |
||
| 115 | } else { |
||
| 116 | $sql = "SELECT COUNT(*) AS number |
||
| 117 | FROM $courseTable AS c |
||
| 118 | WHERE 1 = 1"; |
||
| 119 | } |
||
| 120 | $sql .= " AND visibility IN ($visibilityString) "; |
||
| 121 | |||
| 122 | if (!empty($dateFrom)) { |
||
| 123 | $dateFrom = api_get_utc_datetime("$dateFrom 00:00:00"); |
||
| 124 | $sql .= " AND c.creation_date >= '$dateFrom' "; |
||
| 125 | } |
||
| 126 | |||
| 127 | if (!empty($dateUntil)) { |
||
| 128 | $dateUntil = api_get_utc_datetime("$dateUntil 23:59:59"); |
||
| 129 | $sql .= " AND c.creation_date <= '$dateUntil' "; |
||
| 130 | } |
||
| 131 | |||
| 132 | $res = Database::query($sql); |
||
| 133 | $obj = Database::fetch_object($res); |
||
| 134 | |||
| 135 | return $obj->number; |
||
| 136 | } |
||
| 137 | |||
| 138 | /** |
||
| 139 | * Count users. |
||
| 140 | * |
||
| 141 | * @param int $status user status (COURSEMANAGER or STUDENT) if not setted it'll count all users |
||
| 142 | * @param string $categoryCode course category code. Default: count only users without filtering category |
||
| 143 | * @param bool $countInvisibleCourses Count invisible courses (todo) |
||
| 144 | * @param bool $onlyActive Count only active users (false to only return currently active users) |
||
| 145 | * |
||
| 146 | * @return int Number of users counted |
||
| 147 | */ |
||
| 148 | public static function countUsers( |
||
| 149 | $status = null, |
||
| 150 | $categoryCode = null, |
||
| 151 | $countInvisibleCourses = true, |
||
| 152 | $onlyActive = false |
||
| 153 | ) { |
||
| 154 | // Database table definitions |
||
| 155 | $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
| 156 | $course_table = Database::get_main_table(TABLE_MAIN_COURSE); |
||
| 157 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
| 158 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 159 | $urlId = api_get_current_access_url_id(); |
||
| 160 | $active_filter = $onlyActive ? ' AND active=1' : ''; |
||
| 161 | $status_filter = isset($status) ? ' AND status = '.intval($status) : ''; |
||
| 162 | |||
| 163 | if (api_is_multiple_url_enabled()) { |
||
| 164 | $sql = "SELECT COUNT(DISTINCT(u.user_id)) AS number |
||
| 165 | FROM $user_table as u, $access_url_rel_user_table as url |
||
| 166 | WHERE |
||
| 167 | u.user_id = url.user_id AND |
||
| 168 | access_url_id = $urlId |
||
| 169 | $status_filter $active_filter"; |
||
| 170 | if (isset($categoryCode)) { |
||
| 171 | $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number |
||
| 172 | FROM $course_user_table cu, $course_table c, $access_url_rel_user_table as url |
||
| 173 | WHERE |
||
| 174 | c.id = cu.c_id AND |
||
| 175 | c.category_code = '".Database::escape_string($categoryCode)."' AND |
||
| 176 | cu.user_id = url.user_id AND |
||
| 177 | access_url_id = $urlId |
||
| 178 | $status_filter $active_filter"; |
||
| 179 | } |
||
| 180 | } else { |
||
| 181 | $sql = "SELECT COUNT(DISTINCT(user_id)) AS number |
||
| 182 | FROM $user_table |
||
| 183 | WHERE 1=1 $status_filter $active_filter"; |
||
| 184 | if (isset($categoryCode)) { |
||
| 185 | $status_filter = isset($status) ? ' AND status = '.intval($status) : ''; |
||
| 186 | $sql = "SELECT COUNT(DISTINCT(cu.user_id)) AS number |
||
| 187 | FROM $course_user_table cu, $course_table c |
||
| 188 | WHERE |
||
| 189 | c.id = cu.c_id AND |
||
| 190 | c.category_code = '".Database::escape_string($categoryCode)."' |
||
| 191 | $status_filter |
||
| 192 | $active_filter |
||
| 193 | "; |
||
| 194 | } |
||
| 195 | } |
||
| 196 | |||
| 197 | $res = Database::query($sql); |
||
| 198 | $obj = Database::fetch_object($res); |
||
| 199 | |||
| 200 | return $obj->number; |
||
| 201 | } |
||
| 202 | |||
| 203 | /** |
||
| 204 | * @param string $startDate |
||
| 205 | * @param string $endDate |
||
| 206 | * |
||
| 207 | * @return array |
||
| 208 | */ |
||
| 209 | public static function getCoursesWithActivity($startDate, $endDate) |
||
| 210 | { |
||
| 211 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
| 212 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS); |
||
| 213 | $startDate = Database::escape_string($startDate); |
||
| 214 | $endDate = Database::escape_string($endDate); |
||
| 215 | |||
| 216 | $urlId = api_get_current_access_url_id(); |
||
| 217 | |||
| 218 | if (api_is_multiple_url_enabled()) { |
||
| 219 | $sql = "SELECT DISTINCT(t.c_id) FROM $table t , $access_url_rel_course_table a |
||
| 220 | WHERE |
||
| 221 | t.c_id = a.c_id AND |
||
| 222 | access_url_id = $urlId AND |
||
| 223 | access_date BETWEEN '$startDate' AND '$endDate' |
||
| 224 | "; |
||
| 225 | } else { |
||
| 226 | $sql = "SELECT DISTINCT(t.c_id) FROM $table t |
||
| 227 | access_date BETWEEN '$startDate' AND '$endDate' "; |
||
| 228 | } |
||
| 229 | |||
| 230 | $result = Database::query($sql); |
||
| 231 | |||
| 232 | return Database::store_result($result); |
||
| 233 | } |
||
| 234 | |||
| 235 | /** |
||
| 236 | * Count activities from track_e_default_table. |
||
| 237 | * |
||
| 238 | * @return int Number of activities counted |
||
| 239 | */ |
||
| 240 | public static function getNumberOfActivities($courseId = 0, $sessionId = 0) |
||
| 241 | { |
||
| 242 | // Database table definitions |
||
| 243 | $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
| 244 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
| 245 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 246 | $urlId = api_get_current_access_url_id(); |
||
| 247 | if (api_is_multiple_url_enabled()) { |
||
| 248 | $sql = "SELECT count(default_id) AS total_number_of_items |
||
| 249 | FROM $track_e_default, $table_user user, $access_url_rel_user_table url |
||
| 250 | WHERE |
||
| 251 | default_user_id = user.user_id AND |
||
| 252 | user.user_id=url.user_id AND |
||
| 253 | access_url_id = $urlId"; |
||
| 254 | } else { |
||
| 255 | $sql = "SELECT count(default_id) AS total_number_of_items |
||
| 256 | FROM $track_e_default, $table_user user |
||
| 257 | WHERE default_user_id = user.user_id "; |
||
| 258 | } |
||
| 259 | |||
| 260 | if (!empty($courseId)) { |
||
| 261 | $courseId = (int) $courseId; |
||
| 262 | $sql .= " AND c_id = $courseId"; |
||
| 263 | $sql .= api_get_session_condition($sessionId); |
||
| 264 | } |
||
| 265 | |||
| 266 | if (isset($_GET['keyword'])) { |
||
| 267 | $keyword = Database::escape_string(trim($_GET['keyword'])); |
||
| 268 | $sql .= " AND ( |
||
| 269 | user.username LIKE '%".$keyword."%' OR |
||
| 270 | default_event_type LIKE '%".$keyword."%' OR |
||
| 271 | default_value_type LIKE '%".$keyword."%' OR |
||
| 272 | default_value LIKE '%".$keyword."%') "; |
||
| 273 | } |
||
| 274 | $res = Database::query($sql); |
||
| 275 | $obj = Database::fetch_object($res); |
||
| 276 | |||
| 277 | return $obj->total_number_of_items; |
||
| 278 | } |
||
| 279 | |||
| 280 | /** |
||
| 281 | * Get activities data to display. |
||
| 282 | * |
||
| 283 | * @param int $from |
||
| 284 | * @param int $numberOfItems |
||
| 285 | * @param int $column |
||
| 286 | * @param string $direction |
||
| 287 | * @param int $courseId |
||
| 288 | * @param int $sessionId |
||
| 289 | * |
||
| 290 | * @return array |
||
| 291 | */ |
||
| 292 | public static function getActivitiesData( |
||
| 293 | $from, |
||
| 294 | $numberOfItems, |
||
| 295 | $column, |
||
| 296 | $direction, |
||
| 297 | $courseId = 0, |
||
| 298 | $sessionId = 0 |
||
| 299 | ) { |
||
| 300 | $track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
| 301 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
| 302 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 303 | $urlId = api_get_current_access_url_id(); |
||
| 304 | $column = (int) $column; |
||
| 305 | $from = (int) $from; |
||
| 306 | $numberOfItems = (int) $numberOfItems; |
||
| 307 | $direction = strtoupper($direction); |
||
| 308 | |||
| 309 | if (!in_array($direction, ['ASC', 'DESC'])) { |
||
| 310 | $direction = 'DESC'; |
||
| 311 | } |
||
| 312 | |||
| 313 | if (api_is_multiple_url_enabled()) { |
||
| 314 | $sql = "SELECT |
||
| 315 | default_event_type as col0, |
||
| 316 | default_value_type as col1, |
||
| 317 | default_value as col2, |
||
| 318 | c_id as col3, |
||
| 319 | session_id as col4, |
||
| 320 | user.username as col5, |
||
| 321 | user.user_id as col6, |
||
| 322 | default_date as col7 |
||
| 323 | FROM $track_e_default as track_default, |
||
| 324 | $table_user as user, |
||
| 325 | $access_url_rel_user_table as url |
||
| 326 | WHERE |
||
| 327 | track_default.default_user_id = user.user_id AND |
||
| 328 | url.user_id = user.user_id AND |
||
| 329 | access_url_id = $urlId"; |
||
| 330 | } else { |
||
| 331 | $sql = "SELECT |
||
| 332 | default_event_type as col0, |
||
| 333 | default_value_type as col1, |
||
| 334 | default_value as col2, |
||
| 335 | c_id as col3, |
||
| 336 | session_id as col4, |
||
| 337 | user.username as col5, |
||
| 338 | user.user_id as col6, |
||
| 339 | default_date as col7 |
||
| 340 | FROM $track_e_default track_default, $table_user user |
||
| 341 | WHERE track_default.default_user_id = user.user_id "; |
||
| 342 | } |
||
| 343 | |||
| 344 | if (!empty($_GET['keyword'])) { |
||
| 345 | $keyword = Database::escape_string(trim($_GET['keyword'])); |
||
| 346 | $sql .= " AND (user.username LIKE '%".$keyword."%' OR |
||
| 347 | default_event_type LIKE '%".$keyword."%' OR |
||
| 348 | default_value_type LIKE '%".$keyword."%' OR |
||
| 349 | default_value LIKE '%".$keyword."%') "; |
||
| 350 | } |
||
| 351 | |||
| 352 | if (!empty($courseId)) { |
||
| 353 | $courseId = (int) $courseId; |
||
| 354 | $sql .= " AND c_id = $courseId"; |
||
| 355 | $sql .= api_get_session_condition($sessionId); |
||
| 356 | } |
||
| 357 | |||
| 358 | if (!empty($column) && !empty($direction)) { |
||
| 359 | $sql .= " ORDER BY col$column $direction"; |
||
| 360 | } else { |
||
| 361 | $sql .= " ORDER BY col7 DESC "; |
||
| 362 | } |
||
| 363 | $sql .= " LIMIT $from, $numberOfItems "; |
||
| 364 | |||
| 365 | $res = Database::query($sql); |
||
| 366 | $activities = []; |
||
| 367 | while ($row = Database::fetch_row($res)) { |
||
| 368 | if (strpos($row[1], '_object') === false && |
||
| 369 | strpos($row[1], '_array') === false |
||
| 370 | ) { |
||
| 371 | $row[2] = $row[2]; |
||
| 372 | } else { |
||
| 373 | if (!empty($row[2])) { |
||
| 374 | $originalData = str_replace('\\', '', $row[2]); |
||
| 375 | $row[2] = UnserializeApi::unserialize('not_allowed_classes', $originalData); |
||
| 376 | if (is_array($row[2]) && !empty($row[2])) { |
||
| 377 | $row[2] = implode_with_key(', ', $row[2]); |
||
| 378 | } else { |
||
| 379 | $row[2] = $originalData; |
||
| 380 | } |
||
| 381 | } |
||
| 382 | } |
||
| 383 | |||
| 384 | if (!empty($row['default_date'])) { |
||
| 385 | $row['default_date'] = api_get_local_time($row['default_date']); |
||
| 386 | } else { |
||
| 387 | $row['default_date'] = '-'; |
||
| 388 | } |
||
| 389 | |||
| 390 | if (!empty($row[7])) { |
||
| 391 | $row[7] = api_get_local_time($row[7]); |
||
| 392 | } else { |
||
| 393 | $row[7] = '-'; |
||
| 394 | } |
||
| 395 | |||
| 396 | if (!empty($row[5])) { |
||
| 397 | // Course |
||
| 398 | if (!empty($row[3])) { |
||
| 399 | $row[3] = Display::url( |
||
| 400 | $row[3], |
||
| 401 | api_get_path(WEB_CODE_PATH).'admin/course_edit.php?id='.$row[3] |
||
| 402 | ); |
||
| 403 | } else { |
||
| 404 | $row[3] = '-'; |
||
| 405 | } |
||
| 406 | |||
| 407 | // session |
||
| 408 | if (!empty($row[4])) { |
||
| 409 | $row[4] = Display::url( |
||
| 410 | $row[4], |
||
| 411 | api_get_path(WEB_CODE_PATH).'session/resume_session.php?id_session='.$row[4] |
||
| 412 | ); |
||
| 413 | } else { |
||
| 414 | $row[4] = '-'; |
||
| 415 | } |
||
| 416 | |||
| 417 | // User id. |
||
| 418 | $userIdHash = UserManager::generateUserHash($row[6]); |
||
| 419 | $row[5] = Display::url( |
||
| 420 | $row[5], |
||
| 421 | api_get_path(WEB_AJAX_PATH).'user_manager.ajax.php?a=get_user_popup&hash='.$userIdHash, |
||
| 422 | ['class' => 'ajax'] |
||
| 423 | ); |
||
| 424 | |||
| 425 | $row[6] = Tracking::get_ip_from_user_event( |
||
| 426 | $row[6], |
||
| 427 | $row[7], |
||
| 428 | true |
||
| 429 | ); |
||
| 430 | if (empty($row[6])) { |
||
| 431 | $row[6] = get_lang('Unknown'); |
||
| 432 | } |
||
| 433 | } |
||
| 434 | $activities[] = $row; |
||
| 435 | } |
||
| 436 | |||
| 437 | return $activities; |
||
| 438 | } |
||
| 439 | |||
| 440 | /** |
||
| 441 | * Get all course categories. |
||
| 442 | * |
||
| 443 | * @return array All course categories (code => name) |
||
| 444 | */ |
||
| 445 | public static function getCourseCategories() |
||
| 446 | { |
||
| 447 | $categoryTable = Database::get_main_table(TABLE_MAIN_CATEGORY); |
||
| 448 | $sql = "SELECT code, name |
||
| 449 | FROM $categoryTable |
||
| 450 | ORDER BY tree_pos"; |
||
| 451 | $res = Database::query($sql); |
||
| 452 | $categories = [null => get_lang('NoCategory')]; |
||
| 453 | while ($category = Database::fetch_object($res)) { |
||
| 454 | $categories[$category->code] = $category->name; |
||
| 455 | } |
||
| 456 | |||
| 457 | return $categories; |
||
| 458 | } |
||
| 459 | |||
| 460 | /** |
||
| 461 | * Rescale data. |
||
| 462 | * |
||
| 463 | * @param array $data The data that should be rescaled |
||
| 464 | * @param int $max The maximum value in the rescaled data (default = 500); |
||
| 465 | * |
||
| 466 | * @return array The rescaled data, same key as $data |
||
| 467 | */ |
||
| 468 | public static function rescale($data, $max = 500) |
||
| 469 | { |
||
| 470 | $data_max = 1; |
||
| 471 | foreach ($data as $index => $value) { |
||
| 472 | $data_max = ($data_max < $value ? $value : $data_max); |
||
| 473 | } |
||
| 474 | reset($data); |
||
| 475 | $result = []; |
||
| 476 | $delta = $max / $data_max; |
||
| 477 | foreach ($data as $index => $value) { |
||
| 478 | $result[$index] = (int) round($value * $delta); |
||
| 479 | } |
||
| 480 | |||
| 481 | return $result; |
||
| 482 | } |
||
| 483 | |||
| 484 | /** |
||
| 485 | * Get the number of users by access url . |
||
| 486 | * |
||
| 487 | * @param $currentmonth |
||
| 488 | * @param $lastmonth |
||
| 489 | * @param $invoicingMonth |
||
| 490 | * @param $invoicingYear |
||
| 491 | * |
||
| 492 | * @return string |
||
| 493 | */ |
||
| 494 | public static function printInvoicingByAccessUrl( |
||
| 495 | $currentMonth, |
||
| 496 | $lastMonth, |
||
| 497 | $invoicingMonth, |
||
| 498 | $invoicingYear |
||
| 499 | ) { |
||
| 500 | $tblTrackAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS); |
||
| 501 | $tblAccessUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 502 | $tblAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL); |
||
| 503 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
| 504 | $tblCourse = Database::get_main_table(TABLE_MAIN_COURSE); |
||
| 505 | $tblSession = Database::get_main_table(TABLE_MAIN_SESSION); |
||
| 506 | |||
| 507 | $urls = api_get_access_url_from_user(api_get_user_id()); |
||
| 508 | $allowFullUrlAccess = array_search(1, $urls); |
||
| 509 | $whereAccessUrl = ''; |
||
| 510 | if (!empty($urls) && false === $allowFullUrlAccess) { |
||
| 511 | $whereAccessUrl = ' AND access_url_rel_user.access_url_id IN('.implode(',', $urls).')'; |
||
| 512 | } |
||
| 513 | |||
| 514 | $sql = ' |
||
| 515 | SELECT |
||
| 516 | DISTINCT access_url.description AS client, |
||
| 517 | user.lastname, |
||
| 518 | user.firstname, |
||
| 519 | course.code AS course_code, |
||
| 520 | MIN(DATE_FORMAT(track_e_access.access_date,"%d/%m/%Y")) AS start_date, |
||
| 521 | access_session_id, |
||
| 522 | CONCAT(coach.lastname,\' \',coach.firstname) AS trainer |
||
| 523 | FROM '.$tblTrackAccess.' AS track_e_access |
||
| 524 | JOIN '.$tblAccessUrlUser.' AS access_url_rel_user ON access_url_rel_user.user_id=track_e_access.access_user_id |
||
| 525 | JOIN '.$tblAccessUrl.' AS access_url ON access_url.id=access_url_rel_user.access_url_id |
||
| 526 | JOIN '.$tblUser.' AS user ON user.user_id=access_user_id |
||
| 527 | JOIN '.$tblCourse.' AS course ON course.id=track_e_access.c_id |
||
| 528 | JOIN '.$tblSession.' AS session ON session.id=track_e_access.access_session_id |
||
| 529 | JOIN '.$tblUser.' AS coach ON coach.user_id=session.id_coach |
||
| 530 | WHERE |
||
| 531 | access_session_id > 0 AND |
||
| 532 | access_date LIKE \''.$currentMonth.'%\' AND |
||
| 533 | user.status = '.STUDENT.' AND |
||
| 534 | CONCAT(access_user_id,\'-\',access_session_id) NOT IN (SELECT CONCAT(access_user_id,\'-\',access_session_id) FROM '.$tblTrackAccess.' WHERE access_session_id > 0 |
||
| 535 | AND access_date LIKE \''.$lastMonth.'%\') |
||
| 536 | '.$whereAccessUrl.' |
||
| 537 | GROUP BY |
||
| 538 | user.lastname,code |
||
| 539 | ORDER BY |
||
| 540 | access_url.description, |
||
| 541 | user.lastname, |
||
| 542 | user.firstname, |
||
| 543 | track_e_access.access_date |
||
| 544 | DESC,course.code |
||
| 545 | '; |
||
| 546 | $result = Database::query($sql); |
||
| 547 | |||
| 548 | $monthList = api_get_months_long(); |
||
| 549 | array_unshift($monthList, ''); |
||
| 550 | |||
| 551 | $nMonth = (int) $invoicingMonth; |
||
| 552 | $content = '<h2>'.get_lang('NumberOfUsers').' '.strtolower($monthList[$nMonth]).' '.$invoicingYear.'</h2><br>'; |
||
| 553 | |||
| 554 | $form = new FormValidator('invoice_month', 'get', api_get_self().'?report=invoicing&invoicing_month='.$invoicingMonth.'&invoicing_year='.$invoicingYear); |
||
| 555 | $form->addSelect('invoicing_month', get_lang('Month'), $monthList); |
||
| 556 | $currentYear = date("Y"); |
||
| 557 | $yearList = ['']; |
||
| 558 | for ($i = 0; $i < 3; $i++) { |
||
| 559 | $y = $currentYear - $i; |
||
| 560 | $yearList[$y] = $y; |
||
| 561 | } |
||
| 562 | $form->addSelect('invoicing_year', get_lang('Year'), $yearList); |
||
| 563 | $form->addButtonSend(get_lang('Filter')); |
||
| 564 | $form->addHidden('report', 'invoicing'); |
||
| 565 | $content .= $form->returnForm(); |
||
| 566 | |||
| 567 | $content .= '<br>'; |
||
| 568 | $content .= '<table border=1 class="table table-bordered data_table">'; |
||
| 569 | $content .= '<tr> |
||
| 570 | <th class="th-header">'.get_lang('Portal').'</th> |
||
| 571 | <th class="th-header">'.get_lang('LastName').'</th> |
||
| 572 | <th class="th-header">'.get_lang('FirstName').'</th> |
||
| 573 | <th class="th-header">'.get_lang('Code').'</th> |
||
| 574 | <th class="th-header">'.get_lang('StartDate').'</th> |
||
| 575 | <th class="th-header">'.get_lang('SessionId').'</th> |
||
| 576 | <th class="th-header">'.get_lang('Trainer').'</th> |
||
| 577 | </tr>'; |
||
| 578 | $countusers = 0; |
||
| 579 | $lastname = ''; |
||
| 580 | $lastportal = ''; |
||
| 581 | while ($row = Database::fetch_array($result)) { |
||
| 582 | if (($row['client'] != $lastportal) && ($countusers > 0)) { |
||
| 583 | $content .= '<tr class="row_odd"><td colspan=7>'.get_lang('TotalUser').' '.$lastportal.' : '.$countusers.'</td></tr>'; |
||
| 584 | $countusers = 0; |
||
| 585 | } |
||
| 586 | $content .= '<tr> |
||
| 587 | <td>'.$row['client'].'</td> |
||
| 588 | <td>'.$row['lastname'].'</td> |
||
| 589 | <td>'.$row['firstname'].'</td> |
||
| 590 | <td>'.$row['course_code'].'</td> |
||
| 591 | <td>'.$row['start_date'].'</td> |
||
| 592 | <td>'.$row['access_session_id'].'</td> |
||
| 593 | <td>'.$row['trainer'].'</td> |
||
| 594 | </tr>'; |
||
| 595 | if ($lastname != $row['lastname'].$row['firstname']) { |
||
| 596 | $countusers++; |
||
| 597 | } |
||
| 598 | $lastname = $row['lastname'].$row['firstname']; |
||
| 599 | $lastportal = $row['client']; |
||
| 600 | } |
||
| 601 | $content .= '<tr class="row_odd"> |
||
| 602 | <td colspan=7>'.get_lang('TotalUser').' '.$lastportal.' : '.$countusers.'</td> |
||
| 603 | </tr>'; |
||
| 604 | $content .= '</table>'; |
||
| 605 | |||
| 606 | return $content; |
||
| 607 | } |
||
| 608 | |||
| 609 | /** |
||
| 610 | * Show statistics. |
||
| 611 | * |
||
| 612 | * @param string $title The title |
||
| 613 | * @param array $stats |
||
| 614 | * @param bool $showTotal |
||
| 615 | * @param bool $isFileSize |
||
| 616 | * |
||
| 617 | * @return string HTML table |
||
| 618 | */ |
||
| 619 | public static function printStats( |
||
| 620 | $title, |
||
| 621 | $stats, |
||
| 622 | $showTotal = true, |
||
| 623 | $isFileSize = false |
||
| 624 | ) { |
||
| 625 | $total = 0; |
||
| 626 | $content = '<table class="table table-hover table-striped data_table" cellspacing="0" cellpadding="3" width="90%"> |
||
| 627 | <thead><tr><th colspan="'.($showTotal ? '4' : '3').'">'.$title.'</th></tr></thead><tbody>'; |
||
| 628 | $i = 0; |
||
| 629 | foreach ($stats as $subtitle => $number) { |
||
| 630 | $total += $number; |
||
| 631 | } |
||
| 632 | |||
| 633 | foreach ($stats as $subtitle => $number) { |
||
| 634 | if (!$isFileSize) { |
||
| 635 | $number_label = number_format($number, 0, ',', '.'); |
||
| 636 | } else { |
||
| 637 | $number_label = self::makeSizeString($number); |
||
| 638 | } |
||
| 639 | $percentage = ($total > 0 ? number_format(100 * $number / $total, 1, ',', '.') : '0'); |
||
| 640 | |||
| 641 | $content .= '<tr class="row_'.($i % 2 == 0 ? 'odd' : 'even').'"> |
||
| 642 | <td width="25%" style="vertical-align:top;">'.$subtitle.'</td> |
||
| 643 | <td width="60%">'.Display::bar_progress($percentage, false).'</td> |
||
| 644 | <td width="5%" align="right" style="vertical-align:top;">'.$number_label.'</td>'; |
||
| 645 | if ($showTotal) { |
||
| 646 | $content .= '<td width="5%" align="right"> '.$percentage.'%</td>'; |
||
| 647 | } |
||
| 648 | $content .= '</tr>'; |
||
| 649 | $i++; |
||
| 650 | } |
||
| 651 | $content .= '</tbody>'; |
||
| 652 | if ($showTotal) { |
||
| 653 | if (!$isFileSize) { |
||
| 654 | $total_label = number_format($total, 0, ',', '.'); |
||
| 655 | } else { |
||
| 656 | $total_label = self::makeSizeString($total); |
||
| 657 | } |
||
| 658 | $content .= ' |
||
| 659 | <tfoot><tr><th colspan="4" align="right">'.get_lang('Total').': '.$total_label.'</td></tr></tfoot> |
||
| 660 | '; |
||
| 661 | } |
||
| 662 | $content .= '</table>'; |
||
| 663 | |||
| 664 | return $content; |
||
| 665 | } |
||
| 666 | |||
| 667 | /** |
||
| 668 | * Show some stats about the number of logins. |
||
| 669 | * |
||
| 670 | * @param string $type month, hour or day |
||
| 671 | */ |
||
| 672 | public static function printLoginStats($type) |
||
| 673 | { |
||
| 674 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
| 675 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 676 | $urlId = api_get_current_access_url_id(); |
||
| 677 | |||
| 678 | $table_url = null; |
||
| 679 | $where_url = null; |
||
| 680 | $now = api_get_utc_datetime(); |
||
| 681 | $where_url_last = ' WHERE login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)'; |
||
| 682 | if (api_is_multiple_url_enabled()) { |
||
| 683 | $table_url = ", $access_url_rel_user_table"; |
||
| 684 | $where_url = " WHERE login_user_id=user_id AND access_url_id = $urlId"; |
||
| 685 | $where_url_last = ' AND login_date > DATE_SUB("'.$now.'",INTERVAL 1 %s)'; |
||
| 686 | } |
||
| 687 | |||
| 688 | $period = get_lang('PeriodMonth'); |
||
| 689 | $periodCollection = api_get_months_long(); |
||
| 690 | $sql = "SELECT |
||
| 691 | DATE_FORMAT( login_date, '%Y-%m' ) AS stat_date , |
||
| 692 | count( login_id ) AS number_of_logins |
||
| 693 | FROM $table $table_url $where_url |
||
| 694 | GROUP BY stat_date |
||
| 695 | ORDER BY login_date DESC"; |
||
| 696 | $sql_last_x = null; |
||
| 697 | |||
| 698 | switch ($type) { |
||
| 699 | case 'hour': |
||
| 700 | $period = get_lang('PeriodHour'); |
||
| 701 | $sql = "SELECT |
||
| 702 | DATE_FORMAT( login_date, '%H') AS stat_date, |
||
| 703 | count( login_id ) AS number_of_logins |
||
| 704 | FROM $table $table_url $where_url |
||
| 705 | GROUP BY stat_date |
||
| 706 | ORDER BY stat_date "; |
||
| 707 | $sql_last_x = "SELECT |
||
| 708 | DATE_FORMAT( login_date, '%H' ) AS stat_date, |
||
| 709 | count( login_id ) AS number_of_logins |
||
| 710 | FROM $table $table_url $where_url ".sprintf($where_url_last, 'DAY')." |
||
| 711 | GROUP BY stat_date |
||
| 712 | ORDER BY stat_date "; |
||
| 713 | break; |
||
| 714 | case 'day': |
||
| 715 | $periodCollection = api_get_week_days_long(); |
||
| 716 | $period = get_lang('PeriodDay'); |
||
| 717 | $sql = "SELECT DATE_FORMAT( login_date, '%w' ) AS stat_date , |
||
| 718 | count( login_id ) AS number_of_logins |
||
| 719 | FROM $table $table_url $where_url |
||
| 720 | GROUP BY stat_date |
||
| 721 | ORDER BY DATE_FORMAT( login_date, '%w' ) "; |
||
| 722 | $sql_last_x = "SELECT |
||
| 723 | DATE_FORMAT( login_date, '%w' ) AS stat_date, |
||
| 724 | count( login_id ) AS number_of_logins |
||
| 725 | FROM $table $table_url $where_url ".sprintf($where_url_last, 'WEEK')." |
||
| 726 | GROUP BY stat_date |
||
| 727 | ORDER BY DATE_FORMAT( login_date, '%w' ) "; |
||
| 728 | break; |
||
| 729 | } |
||
| 730 | |||
| 731 | $content = ''; |
||
| 732 | if ($sql_last_x) { |
||
| 733 | $res_last_x = Database::query($sql_last_x); |
||
| 734 | $result_last_x = []; |
||
| 735 | while ($obj = Database::fetch_object($res_last_x)) { |
||
| 736 | $stat_date = ($type === 'day') ? $periodCollection[$obj->stat_date] : $obj->stat_date; |
||
| 737 | $result_last_x[$stat_date] = $obj->number_of_logins; |
||
| 738 | } |
||
| 739 | $content .= self::printStats(get_lang('LastLogins').' ('.$period.')', $result_last_x, true); |
||
| 740 | flush(); //flush web request at this point to see something already while the full data set is loading |
||
| 741 | $content .= '<br />'; |
||
| 742 | } |
||
| 743 | $res = Database::query($sql); |
||
| 744 | $result = []; |
||
| 745 | while ($obj = Database::fetch_object($res)) { |
||
| 746 | $stat_date = $obj->stat_date; |
||
| 747 | switch ($type) { |
||
| 748 | case 'month': |
||
| 749 | $stat_date = explode('-', $stat_date); |
||
| 750 | $stat_date[1] = $periodCollection[$stat_date[1] - 1]; |
||
| 751 | $stat_date = implode(' ', $stat_date); |
||
| 752 | break; |
||
| 753 | case 'day': |
||
| 754 | $stat_date = $periodCollection[$stat_date]; |
||
| 755 | break; |
||
| 756 | } |
||
| 757 | $result[$stat_date] = $obj->number_of_logins; |
||
| 758 | } |
||
| 759 | $content .= self::printStats(get_lang('AllLogins').' ('.$period.')', $result, true); |
||
| 760 | |||
| 761 | return $content; |
||
| 762 | } |
||
| 763 | |||
| 764 | /** |
||
| 765 | * Print the number of recent logins. |
||
| 766 | * |
||
| 767 | * @param bool $distinct whether to only give distinct users stats, or *all* logins |
||
| 768 | * @param int $sessionDuration Number of minutes a session must have lasted at a minimum to be taken into account |
||
| 769 | * @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) |
||
| 770 | * |
||
| 771 | * @throws Exception |
||
| 772 | * |
||
| 773 | * @return string HTML table |
||
| 774 | */ |
||
| 775 | public static function printRecentLoginStats($distinct = false, $sessionDuration = 0, $periods = []) |
||
| 776 | { |
||
| 777 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
| 778 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 779 | $urlId = api_get_current_access_url_id(); |
||
| 780 | $table_url = ''; |
||
| 781 | $where_url = ''; |
||
| 782 | if (api_is_multiple_url_enabled()) { |
||
| 783 | $table_url = ", $access_url_rel_user_table"; |
||
| 784 | $where_url = " AND login_user_id=user_id AND access_url_id = $urlId"; |
||
| 785 | } |
||
| 786 | |||
| 787 | $now = api_get_utc_datetime(); |
||
| 788 | $field = 'login_id'; |
||
| 789 | if ($distinct) { |
||
| 790 | $field = 'DISTINCT(login_user_id)'; |
||
| 791 | } |
||
| 792 | |||
| 793 | if (empty($periods)) { |
||
| 794 | $periods = [1, 7, 31]; |
||
| 795 | } |
||
| 796 | $sqlList = []; |
||
| 797 | |||
| 798 | $sessionDuration = (int) $sessionDuration * 60; // convert from minutes to seconds |
||
| 799 | foreach ($periods as $day) { |
||
| 800 | $date = new DateTime($now); |
||
| 801 | $startDate = $date->format('Y-m-d').' 00:00:00'; |
||
| 802 | $endDate = $date->format('Y-m-d').' 23:59:59'; |
||
| 803 | |||
| 804 | if ($day > 1) { |
||
| 805 | $startDate = $date->sub(new DateInterval('P'.$day.'D')); |
||
| 806 | $startDate = $startDate->format('Y-m-d').' 00:00:00'; |
||
| 807 | } |
||
| 808 | |||
| 809 | $localDate = api_get_local_time($startDate, null, null, false, false); |
||
| 810 | $localEndDate = api_get_local_time($endDate, null, null, false, false); |
||
| 811 | |||
| 812 | $label = sprintf(get_lang('LastXDays'), $day); |
||
| 813 | if ($day == 1) { |
||
| 814 | $label = get_lang('Today'); |
||
| 815 | } |
||
| 816 | $label .= " <span class=\"muted right\" style=\"float: right; margin-right: 5px;\">[$localDate - $localEndDate]</span>"; |
||
| 817 | $sql = "SELECT count($field) AS number |
||
| 818 | FROM $table $table_url |
||
| 819 | WHERE "; |
||
| 820 | if ($sessionDuration == 0) { |
||
| 821 | $sql .= " logout_date != login_date AND "; |
||
| 822 | } else { |
||
| 823 | $sql .= " UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration AND "; |
||
| 824 | } |
||
| 825 | $sql .= "login_date BETWEEN '$startDate' AND '$endDate' |
||
| 826 | $where_url"; |
||
| 827 | $sqlList[$label] = $sql; |
||
| 828 | } |
||
| 829 | |||
| 830 | $sql = "SELECT count($field) AS number |
||
| 831 | FROM $table $table_url "; |
||
| 832 | if ($sessionDuration == 0) { |
||
| 833 | $sql .= " WHERE logout_date != login_date $where_url"; |
||
| 834 | } else { |
||
| 835 | $sql .= " WHERE UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration $where_url"; |
||
| 836 | } |
||
| 837 | $sqlList[get_lang('Total')] = $sql; |
||
| 838 | $totalLogin = []; |
||
| 839 | foreach ($sqlList as $label => $query) { |
||
| 840 | $res = Database::query($query); |
||
| 841 | $obj = Database::fetch_object($res); |
||
| 842 | $totalLogin[$label] = $obj->number; |
||
| 843 | } |
||
| 844 | |||
| 845 | if ($distinct) { |
||
| 846 | $content = self::printStats(get_lang('DistinctUsersLogins'), $totalLogin, false); |
||
| 847 | } else { |
||
| 848 | $content = self::printStats(get_lang('Logins'), $totalLogin, false); |
||
| 849 | } |
||
| 850 | |||
| 851 | return $content; |
||
| 852 | } |
||
| 853 | |||
| 854 | /** |
||
| 855 | * Get the number of recent logins. |
||
| 856 | * |
||
| 857 | * @param bool $distinct Whether to only give distinct users stats, or *all* logins |
||
| 858 | * @param int $sessionDuration Number of minutes a session must have lasted at a minimum to be taken into account |
||
| 859 | * @param bool $completeMissingDays Whether to fill the daily gaps (if any) when getting a list of logins |
||
| 860 | * |
||
| 861 | * @throws Exception |
||
| 862 | * |
||
| 863 | * @return array |
||
| 864 | */ |
||
| 865 | public static function getRecentLoginStats($distinct = false, $sessionDuration = 0, $completeMissingDays = true) |
||
| 866 | { |
||
| 867 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
| 868 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 869 | $urlId = api_get_current_access_url_id(); |
||
| 870 | $table_url = ''; |
||
| 871 | $where_url = ''; |
||
| 872 | if (api_is_multiple_url_enabled()) { |
||
| 873 | $table_url = ", $access_url_rel_user_table"; |
||
| 874 | $where_url = " AND login_user_id=user_id AND access_url_id = $urlId"; |
||
| 875 | } |
||
| 876 | |||
| 877 | $now = api_get_utc_datetime(); |
||
| 878 | $date = new DateTime($now); |
||
| 879 | $date->sub(new DateInterval('P31D')); |
||
| 880 | $newDate = $date->format('Y-m-d h:i:s'); |
||
| 881 | $totalLogin = self::buildDatesArray($newDate, $now, true); |
||
| 882 | |||
| 883 | $field = 'login_id'; |
||
| 884 | if ($distinct) { |
||
| 885 | $field = 'DISTINCT(login_user_id)'; |
||
| 886 | } |
||
| 887 | $sessionDuration = (int) $sessionDuration * 60; //Convert from minutes to seconds |
||
| 888 | |||
| 889 | $sql = "SELECT count($field) AS number, date(login_date) as login_date |
||
| 890 | FROM $table $table_url |
||
| 891 | WHERE "; |
||
| 892 | if ($sessionDuration == 0) { |
||
| 893 | $sql .= " logout_date != login_date AND "; |
||
| 894 | } else { |
||
| 895 | $sql .= " UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date) > $sessionDuration AND "; |
||
| 896 | } |
||
| 897 | $sql .= " login_date >= '$newDate' $where_url |
||
| 898 | GROUP BY date(login_date)"; |
||
| 899 | |||
| 900 | $res = Database::query($sql); |
||
| 901 | while ($row = Database::fetch_array($res, 'ASSOC')) { |
||
| 902 | $monthAndDay = substr($row['login_date'], 5, 5); |
||
| 903 | $totalLogin[$monthAndDay] = $row['number']; |
||
| 904 | } |
||
| 905 | |||
| 906 | return $totalLogin; |
||
| 907 | } |
||
| 908 | |||
| 909 | /** |
||
| 910 | * Get course tools usage statistics for the whole platform (by URL if multi-url). |
||
| 911 | */ |
||
| 912 | public static function getToolsStats() |
||
| 961 | } |
||
| 962 | |||
| 963 | /** |
||
| 964 | * Show some stats about the accesses to the different course tools. |
||
| 965 | * |
||
| 966 | * @param array $result If defined, this serves as data. Otherwise, will get the data from getToolsStats() |
||
| 967 | * |
||
| 968 | * @return string HTML table |
||
| 969 | */ |
||
| 970 | public static function printToolStats($result = null) |
||
| 971 | { |
||
| 972 | if (empty($result)) { |
||
| 973 | $result = self::getToolsStats(); |
||
| 974 | } |
||
| 975 | |||
| 976 | return self::printStats(get_lang('PlatformToolAccess'), $result, true); |
||
| 977 | } |
||
| 978 | |||
| 979 | /** |
||
| 980 | * Show some stats about the number of courses per language. |
||
| 981 | */ |
||
| 982 | public static function printCourseByLanguageStats() |
||
| 1005 | } |
||
| 1006 | |||
| 1007 | /** |
||
| 1008 | * Shows the number of users having their picture uploaded in Dokeos. |
||
| 1009 | */ |
||
| 1010 | public static function printUserPicturesStats() |
||
| 1035 | } |
||
| 1036 | |||
| 1037 | /** |
||
| 1038 | * Important activities. |
||
| 1039 | */ |
||
| 1040 | public static function printActivitiesStats() |
||
| 1041 | { |
||
| 1042 | $content = '<h4>'.get_lang('ImportantActivities').'</h4>'; |
||
| 1043 | // Create a search-box |
||
| 1044 | $form = new FormValidator( |
||
| 1045 | 'search_simple', |
||
| 1046 | 'get', |
||
| 1047 | api_get_path(WEB_CODE_PATH).'admin/statistics/index.php', |
||
| 1048 | '', |
||
| 1049 | 'width=200px', |
||
| 1050 | false |
||
| 1051 | ); |
||
| 1052 | $renderer = &$form->defaultRenderer(); |
||
| 1053 | $renderer->setCustomElementTemplate('<span>{element}</span> '); |
||
| 1054 | $form->addHidden('report', 'activities'); |
||
| 1055 | $form->addHidden('activities_direction', 'DESC'); |
||
| 1056 | $form->addHidden('activities_column', '4'); |
||
| 1057 | $form->addElement('text', 'keyword', get_lang('Keyword')); |
||
| 1058 | $form->addButtonSearch(get_lang('Search'), 'submit'); |
||
| 1059 | $content .= '<div class="actions">'; |
||
| 1060 | $content .= $form->returnForm(); |
||
| 1061 | $content .= '</div>'; |
||
| 1062 | |||
| 1063 | if (!empty($_GET['keyword'])) { |
||
| 1064 | $table = new SortableTable( |
||
| 1065 | 'activities', |
||
| 1066 | ['Statistics', 'getNumberOfActivities'], |
||
| 1067 | ['Statistics', 'getActivitiesData'], |
||
| 1068 | 7, |
||
| 1069 | 50, |
||
| 1070 | 'DESC' |
||
| 1071 | ); |
||
| 1072 | $parameters = []; |
||
| 1073 | |||
| 1074 | $parameters['report'] = 'activities'; |
||
| 1075 | if (isset($_GET['keyword'])) { |
||
| 1076 | $parameters['keyword'] = Security::remove_XSS($_GET['keyword']); |
||
| 1077 | } |
||
| 1078 | |||
| 1079 | $table->set_additional_parameters($parameters); |
||
| 1080 | $table->set_header(0, get_lang('EventType')); |
||
| 1081 | $table->set_header(1, get_lang('DataType')); |
||
| 1082 | $table->set_header(2, get_lang('Value')); |
||
| 1083 | $table->set_header(3, get_lang('Course')); |
||
| 1084 | $table->set_header(4, get_lang('Session')); |
||
| 1085 | $table->set_header(5, get_lang('UserName')); |
||
| 1086 | $table->set_header(6, get_lang('IPAddress')); |
||
| 1087 | $table->set_header(7, get_lang('Date')); |
||
| 1088 | $content .= $table->return_table(); |
||
| 1089 | } |
||
| 1090 | |||
| 1091 | $content .= '<div class="alert alert-info">'.get_lang('ImportantActivities').' : '.'<br>'; |
||
| 1092 | $prefix = 'LOG_'; |
||
| 1093 | $userDefinedConstants = get_defined_constants(true)['user']; |
||
| 1094 | $filteredConstants = array_filter($userDefinedConstants, function ($constantName) use ($prefix) { |
||
| 1095 | return strpos($constantName, $prefix) === 0; |
||
| 1096 | }, ARRAY_FILTER_USE_KEY); |
||
| 1097 | $constantNames = array_keys($filteredConstants); |
||
| 1098 | $link = api_get_self().'?report=activities&activities_direction=DESC&activities_column=7&keyword='; |
||
| 1099 | foreach ($constantNames as $constantName) { |
||
| 1100 | if ($constantName != 'LOG_WS') { |
||
| 1101 | if (substr($constantName, -3) == '_ID') { |
||
| 1102 | continue; |
||
| 1103 | } |
||
| 1104 | $content .= '- <a href="'.$link.constant($constantName).'">'.constant($constantName).'</a><br>'.PHP_EOL; |
||
| 1105 | } else { |
||
| 1106 | $constantValue = constant($constantName); |
||
| 1107 | $reflection = new ReflectionClass('Rest'); |
||
| 1108 | $constants = $reflection->getConstants(); |
||
| 1109 | foreach ($constants as $name => $value) { |
||
| 1110 | $content .= '- <a href="'.$link.$constantValue.$value.'">'.$constantValue.$value.'</a><br>'.PHP_EOL; |
||
| 1111 | } |
||
| 1112 | } |
||
| 1113 | } |
||
| 1114 | $content .= '</div>'; |
||
| 1115 | |||
| 1116 | return $content; |
||
| 1117 | } |
||
| 1118 | |||
| 1119 | /** |
||
| 1120 | * Shows statistics about the time of last visit to each course. |
||
| 1121 | */ |
||
| 1122 | public static function printCourseLastVisit() |
||
| 1123 | { |
||
| 1124 | $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
| 1125 | $urlId = api_get_current_access_url_id(); |
||
| 1126 | |||
| 1127 | $columns[0] = 't.c_id'; |
||
| 1128 | $columns[1] = 'access_date'; |
||
| 1129 | $sql_order[SORT_ASC] = 'ASC'; |
||
| 1130 | $sql_order[SORT_DESC] = 'DESC'; |
||
| 1131 | $per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 10; |
||
| 1132 | $page_nr = isset($_GET['page_nr']) ? intval($_GET['page_nr']) : 1; |
||
| 1133 | $column = isset($_GET['column']) ? intval($_GET['column']) : 0; |
||
| 1134 | $direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC; |
||
| 1135 | |||
| 1136 | if (!in_array($direction, [SORT_ASC, SORT_DESC])) { |
||
| 1137 | $direction = SORT_ASC; |
||
| 1138 | } |
||
| 1139 | $form = new FormValidator('courselastvisit', 'get'); |
||
| 1140 | $form->addElement('hidden', 'report', 'courselastvisit'); |
||
| 1141 | $form->addText('date_diff', get_lang('Days'), true); |
||
| 1142 | $form->addRule('date_diff', 'InvalidNumber', 'numeric'); |
||
| 1143 | $form->addButtonSearch(get_lang('Search'), 'submit'); |
||
| 1144 | if (!isset($_GET['date_diff'])) { |
||
| 1145 | $defaults['date_diff'] = 60; |
||
| 1146 | } else { |
||
| 1147 | $defaults['date_diff'] = Security::remove_XSS($_GET['date_diff']); |
||
| 1148 | } |
||
| 1149 | $form->setDefaults($defaults); |
||
| 1150 | $content = $form->returnForm(); |
||
| 1151 | |||
| 1152 | $values = $form->exportValues(); |
||
| 1153 | $date_diff = $values['date_diff']; |
||
| 1154 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS); |
||
| 1155 | if (api_is_multiple_url_enabled()) { |
||
| 1156 | $sql = "SELECT * FROM $table t , $access_url_rel_course_table a |
||
| 1157 | WHERE |
||
| 1158 | t.c_id = a.c_id AND |
||
| 1159 | access_url_id = $urlId |
||
| 1160 | GROUP BY t.c_id |
||
| 1161 | HAVING t.c_id <> '' |
||
| 1162 | AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff; |
||
| 1163 | } else { |
||
| 1164 | $sql = "SELECT * FROM $table t |
||
| 1165 | GROUP BY t.c_id |
||
| 1166 | HAVING t.c_id <> '' |
||
| 1167 | AND DATEDIFF( '".api_get_utc_datetime()."' , access_date ) <= ".$date_diff; |
||
| 1168 | } |
||
| 1169 | $sql .= ' ORDER BY '.$columns[$column].' '.$sql_order[$direction]; |
||
| 1170 | $from = ($page_nr - 1) * $per_page; |
||
| 1171 | $sql .= ' LIMIT '.$from.','.$per_page; |
||
| 1172 | |||
| 1173 | $content .= '<p>'.get_lang('LastAccess').' >= '.$date_diff.' '.get_lang('Days').'</p>'; |
||
| 1174 | $res = Database::query($sql); |
||
| 1175 | if (Database::num_rows($res) > 0) { |
||
| 1176 | $courses = []; |
||
| 1177 | while ($obj = Database::fetch_object($res)) { |
||
| 1178 | $courseInfo = api_get_course_info_by_id($obj->c_id); |
||
| 1179 | $course = []; |
||
| 1180 | $course[] = '<a href="'.api_get_path(WEB_COURSE_PATH).$courseInfo['code'].'">'.$courseInfo['code'].' <a>'; |
||
| 1181 | // Allow sort by date hiding the numerical date |
||
| 1182 | $course[] = '<span style="display:none;">'.$obj->access_date.'</span>'.api_convert_and_format_date($obj->access_date); |
||
| 1183 | $courses[] = $course; |
||
| 1184 | } |
||
| 1185 | $parameters['date_diff'] = $date_diff; |
||
| 1186 | $parameters['report'] = 'courselastvisit'; |
||
| 1187 | $table_header[] = [get_lang("CourseCode"), true]; |
||
| 1188 | $table_header[] = [get_lang("LastAccess"), true]; |
||
| 1189 | |||
| 1190 | ob_start(); |
||
| 1191 | Display::display_sortable_table( |
||
| 1192 | $table_header, |
||
| 1193 | $courses, |
||
| 1194 | ['column' => $column, 'direction' => $direction], |
||
| 1195 | [], |
||
| 1196 | $parameters |
||
| 1197 | ); |
||
| 1198 | $content .= ob_get_contents(); |
||
| 1199 | ob_end_clean(); |
||
| 1200 | } else { |
||
| 1201 | $content = get_lang('NoSearchResults'); |
||
| 1202 | } |
||
| 1203 | |||
| 1204 | return $content; |
||
| 1205 | } |
||
| 1206 | |||
| 1207 | /** |
||
| 1208 | * Displays the statistics of the messages sent and received by each user in the social network. |
||
| 1209 | * |
||
| 1210 | * @param string $messageType Type of message: 'sent' or 'received' |
||
| 1211 | * |
||
| 1212 | * @return array Message list |
||
| 1213 | */ |
||
| 1214 | public static function getMessages($messageType) |
||
| 1215 | { |
||
| 1216 | $message_table = Database::get_main_table(TABLE_MESSAGE); |
||
| 1217 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
| 1218 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 1219 | |||
| 1220 | $urlId = api_get_current_access_url_id(); |
||
| 1221 | |||
| 1222 | switch ($messageType) { |
||
| 1223 | case 'sent': |
||
| 1224 | $field = 'user_sender_id'; |
||
| 1225 | break; |
||
| 1226 | case 'received': |
||
| 1227 | $field = 'user_receiver_id'; |
||
| 1228 | break; |
||
| 1229 | } |
||
| 1230 | |||
| 1231 | if (api_is_multiple_url_enabled()) { |
||
| 1232 | $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message |
||
| 1233 | FROM $access_url_rel_user_table as url, $message_table m |
||
| 1234 | LEFT JOIN $user_table u ON m.$field = u.user_id |
||
| 1235 | WHERE url.user_id = m.$field AND access_url_id = $urlId |
||
| 1236 | GROUP BY m.$field |
||
| 1237 | ORDER BY count_message DESC "; |
||
| 1238 | } else { |
||
| 1239 | $sql = "SELECT lastname, firstname, username, COUNT($field) AS count_message |
||
| 1240 | FROM $message_table m |
||
| 1241 | LEFT JOIN $user_table u ON m.$field = u.user_id |
||
| 1242 | GROUP BY m.$field ORDER BY count_message DESC "; |
||
| 1243 | } |
||
| 1244 | $res = Database::query($sql); |
||
| 1245 | $messages_sent = []; |
||
| 1246 | while ($messages = Database::fetch_array($res)) { |
||
| 1247 | if (empty($messages['username'])) { |
||
| 1248 | $messages['username'] = get_lang('Unknown'); |
||
| 1249 | } |
||
| 1250 | $users = api_get_person_name( |
||
| 1251 | $messages['firstname'], |
||
| 1252 | $messages['lastname'] |
||
| 1253 | ).'<br />('.$messages['username'].')'; |
||
| 1254 | $messages_sent[$users] = $messages['count_message']; |
||
| 1255 | } |
||
| 1256 | |||
| 1257 | return $messages_sent; |
||
| 1258 | } |
||
| 1259 | |||
| 1260 | /** |
||
| 1261 | * Count the number of friends for social network users. |
||
| 1262 | */ |
||
| 1263 | public static function getFriends() |
||
| 1264 | { |
||
| 1265 | $user_friend_table = Database::get_main_table(TABLE_MAIN_USER_REL_USER); |
||
| 1266 | $user_table = Database::get_main_table(TABLE_MAIN_USER); |
||
| 1267 | $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 1268 | $urlId = api_get_current_access_url_id(); |
||
| 1269 | |||
| 1270 | if (api_is_multiple_url_enabled()) { |
||
| 1271 | $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend |
||
| 1272 | FROM $access_url_rel_user_table as url, $user_friend_table uf |
||
| 1273 | LEFT JOIN $user_table u |
||
| 1274 | ON (uf.user_id = u.user_id) |
||
| 1275 | WHERE |
||
| 1276 | uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' AND |
||
| 1277 | uf.user_id = url.user_id AND |
||
| 1278 | access_url_id = $urlId |
||
| 1279 | GROUP BY uf.user_id |
||
| 1280 | ORDER BY count_friend DESC "; |
||
| 1281 | } else { |
||
| 1282 | $sql = "SELECT lastname, firstname, username, COUNT(friend_user_id) AS count_friend |
||
| 1283 | FROM $user_friend_table uf |
||
| 1284 | LEFT JOIN $user_table u |
||
| 1285 | ON (uf.user_id = u.user_id) |
||
| 1286 | WHERE uf.relation_type <> '".USER_RELATION_TYPE_RRHH."' |
||
| 1287 | GROUP BY uf.user_id |
||
| 1288 | ORDER BY count_friend DESC "; |
||
| 1289 | } |
||
| 1290 | $res = Database::query($sql); |
||
| 1291 | $list_friends = []; |
||
| 1292 | while ($friends = Database::fetch_array($res)) { |
||
| 1293 | $users = api_get_person_name($friends['firstname'], $friends['lastname']).'<br />('.$friends['username'].')'; |
||
| 1294 | $list_friends[$users] = $friends['count_friend']; |
||
| 1295 | } |
||
| 1296 | |||
| 1297 | return $list_friends; |
||
| 1298 | } |
||
| 1299 | |||
| 1300 | /** |
||
| 1301 | * Print the number of users that didn't login for a certain period of time. |
||
| 1302 | */ |
||
| 1303 | public static function printUsersNotLoggedInStats() |
||
| 1348 | ); |
||
| 1349 | } |
||
| 1350 | |||
| 1351 | /** |
||
| 1352 | * Returns an array with indexes as the 'yyyy-mm-dd' format of each date |
||
| 1353 | * within the provided range (including limits). Dates are assumed to be |
||
| 1354 | * given in UTC. |
||
| 1355 | * |
||
| 1356 | * @param string $startDate Start date, in Y-m-d or Y-m-d h:i:s format |
||
| 1357 | * @param string $endDate End date, in Y-m-d or Y-m-d h:i:s format |
||
| 1358 | * @param bool $removeYear Whether to remove the year in the results (for easier reading) |
||
| 1359 | * |
||
| 1360 | * @return array|bool False on error in the params, array of [date1 => 0, date2 => 0, ...] otherwise |
||
| 1361 | */ |
||
| 1362 | public static function buildDatesArray($startDate, $endDate, $removeYear = false) |
||
| 1363 | { |
||
| 1364 | if (strlen($startDate) > 10) { |
||
| 1365 | $startDate = substr($startDate, 0, 10); |
||
| 1366 | } |
||
| 1367 | if (strlen($endDate) > 10) { |
||
| 1368 | $endDate = substr($endDate, 0, 10); |
||
| 1369 | } |
||
| 1370 | if (!preg_match('/\d\d\d\d-\d\d-\d\d/', $startDate)) { |
||
| 1371 | return false; |
||
| 1372 | } |
||
| 1373 | if (!preg_match('/\d\d\d\d-\d\d-\d\d/', $startDate)) { |
||
| 1374 | return false; |
||
| 1375 | } |
||
| 1376 | $startTimestamp = strtotime($startDate); |
||
| 1377 | $endTimestamp = strtotime($endDate); |
||
| 1378 | $list = []; |
||
| 1379 | for ($time = $startTimestamp; $time < $endTimestamp; $time += 86400) { |
||
| 1380 | $datetime = api_get_utc_datetime($time); |
||
| 1381 | if ($removeYear) { |
||
| 1382 | $datetime = substr($datetime, 5, 5); |
||
| 1383 | } else { |
||
| 1384 | $dateTime = substr($datetime, 0, 10); |
||
| 1385 | } |
||
| 1386 | $list[$datetime] = 0; |
||
| 1387 | } |
||
| 1388 | |||
| 1389 | return $list; |
||
| 1390 | } |
||
| 1391 | |||
| 1392 | /** |
||
| 1393 | * Prepare the JS code to load a chart. |
||
| 1394 | * |
||
| 1395 | * @param string $url URL for AJAX data generator |
||
| 1396 | * @param string $type bar, line, pie, etc |
||
| 1397 | * @param string $options Additional options to the chart (see chart-specific library) |
||
| 1398 | * @param string A JS code for loading the chart together with a call to AJAX data generator |
||
| 1399 | */ |
||
| 1400 | public static function getJSChartTemplate($url, $type = 'pie', $options = '', $elementId = 'canvas') |
||
| 1401 | { |
||
| 1402 | $chartCode = ' |
||
| 1403 | <script> |
||
| 1404 | $(function() { |
||
| 1405 | $.ajax({ |
||
| 1406 | url: "'.$url.'", |
||
| 1407 | type: "POST", |
||
| 1408 | success: function(data) { |
||
| 1409 | Chart.defaults.global.responsive = true; |
||
| 1410 | var ctx = document.getElementById("'.$elementId.'").getContext("2d"); |
||
| 1411 | var chart = new Chart(ctx, { |
||
| 1412 | type: "'.$type.'", |
||
| 1413 | data: data, |
||
| 1414 | options: {'.$options.'} |
||
| 1415 | }); |
||
| 1416 | var title = chart.options.title.text; |
||
| 1417 | $("#'.$elementId.'_title").html(title); |
||
| 1418 | $("#'.$elementId.'_table").html(data.table); |
||
| 1419 | } |
||
| 1420 | }); |
||
| 1421 | }); |
||
| 1422 | </script>'; |
||
| 1423 | |||
| 1424 | return $chartCode; |
||
| 1425 | } |
||
| 1426 | |||
| 1427 | public static function getJSChartTemplateWithData($data, $type = 'pie', $options = '', $elementId = 'canvas') |
||
| 1428 | { |
||
| 1429 | $data = json_encode($data); |
||
| 1430 | $chartCode = ' |
||
| 1431 | <script> |
||
| 1432 | $(function() { |
||
| 1433 | Chart.defaults.global.responsive = true; |
||
| 1434 | var ctx = document.getElementById("'.$elementId.'").getContext("2d"); |
||
| 1435 | var chart = new Chart(ctx, { |
||
| 1436 | type: "'.$type.'", |
||
| 1437 | data: '.$data.', |
||
| 1438 | options: {'.$options.'} |
||
| 1439 | }); |
||
| 1440 | }); |
||
| 1441 | </script>'; |
||
| 1442 | |||
| 1443 | return $chartCode; |
||
| 1444 | } |
||
| 1445 | |||
| 1446 | public static function buildJsChartData($all, $chartName) |
||
| 1447 | { |
||
| 1448 | $list = []; |
||
| 1449 | $palette = ChamiloApi::getColorPalette(true, true); |
||
| 1450 | foreach ($all as $tick => $tock) { |
||
| 1451 | $list['labels'][] = $tick; |
||
| 1452 | } |
||
| 1453 | |||
| 1454 | $list['datasets'][0]['label'] = $chartName; |
||
| 1455 | $list['datasets'][0]['borderColor'] = 'rgba(255,255,255,1)'; |
||
| 1456 | |||
| 1457 | $i = 0; |
||
| 1458 | foreach ($all as $tick => $tock) { |
||
| 1459 | $j = $i % count($palette); |
||
| 1460 | $list['datasets'][0]['data'][] = $tock; |
||
| 1461 | $list['datasets'][0]['backgroundColor'][] = $palette[$j]; |
||
| 1462 | $i++; |
||
| 1463 | } |
||
| 1464 | |||
| 1465 | $scoreDisplay = ScoreDisplay::instance(); |
||
| 1466 | $table = new HTML_Table(['class' => 'table table-hover table-striped data_table']); |
||
| 1467 | $headers = [ |
||
| 1468 | get_lang('Name'), |
||
| 1469 | get_lang('Count'), |
||
| 1470 | get_lang('Percentage'), |
||
| 1471 | ]; |
||
| 1472 | $row = 0; |
||
| 1473 | $column = 0; |
||
| 1474 | foreach ($headers as $header) { |
||
| 1475 | $table->setHeaderContents($row, $column, $header); |
||
| 1476 | $column++; |
||
| 1477 | } |
||
| 1478 | |||
| 1479 | $total = 0; |
||
| 1480 | foreach ($all as $name => $value) { |
||
| 1481 | $total += $value; |
||
| 1482 | } |
||
| 1483 | $row++; |
||
| 1484 | foreach ($all as $name => $value) { |
||
| 1485 | $table->setCellContents($row, 0, $name); |
||
| 1486 | $table->setCellContents($row, 1, $value); |
||
| 1487 | $table->setCellContents($row, 2, $scoreDisplay->display_score([$value, $total], SCORE_PERCENT)); |
||
| 1488 | $row++; |
||
| 1489 | } |
||
| 1490 | $table = Display::page_subheader2($chartName).$table->toHtml(); |
||
| 1491 | |||
| 1492 | return ['chart' => $list, 'table' => $table]; |
||
| 1493 | } |
||
| 1494 | |||
| 1495 | /** |
||
| 1496 | * Display learnpath results from lti provider. |
||
| 1497 | * |
||
| 1498 | * @return false|string |
||
| 1499 | */ |
||
| 1500 | public static function printLtiLearningPath() |
||
| 1501 | { |
||
| 1502 | $pluginLtiProvider = ('true' === api_get_plugin_setting('lti_provider', 'enabled')); |
||
| 1503 | |||
| 1504 | if (!$pluginLtiProvider) { |
||
| 1505 | return false; |
||
| 1506 | } |
||
| 1507 | |||
| 1508 | $content = Display::page_header(get_lang('LearningPathLTI')); |
||
| 1509 | $actions = ''; |
||
| 1510 | $form = new FormValidator('frm_lti_tool_lp', 'get'); |
||
| 1511 | $form->addDateRangePicker( |
||
| 1512 | 'daterange', |
||
| 1513 | get_lang('DateRange'), |
||
| 1514 | true, |
||
| 1515 | ['format' => 'YYYY-MM-DD', 'timePicker' => 'false', 'validate_format' => 'Y-m-d'] |
||
| 1516 | ); |
||
| 1517 | $form->addHidden('report', 'lti_tool_lp'); |
||
| 1518 | $form->addButtonFilter(get_lang('Search')); |
||
| 1519 | |||
| 1520 | if ($form->validate()) { |
||
| 1521 | $values = $form->exportValues(); |
||
| 1522 | |||
| 1523 | $result = self::getLtiLearningPathByDate($values['daterange_start'], $values['daterange_end']); |
||
| 1524 | |||
| 1525 | $table = new HTML_Table(['class' => 'table table-bordered data_table']); |
||
| 1526 | $table->setHeaderContents(0, 0, get_lang('URL')); |
||
| 1527 | $table->setHeaderContents(0, 1, get_lang('ToolLp')); |
||
| 1528 | $table->setHeaderContents(0, 2, get_lang('LastName')); |
||
| 1529 | $table->setHeaderContents(0, 3, get_lang('FirstName')); |
||
| 1530 | $table->setHeaderContents(0, 4, get_lang('FirstAccess')); |
||
| 1531 | $i = 1; |
||
| 1532 | foreach ($result as $item) { |
||
| 1533 | if (!empty($item['learnpaths'])) { |
||
| 1534 | foreach ($item['learnpaths'] as $lpId => $lpValues) { |
||
| 1535 | $lpName = learnpath::getLpNameById($lpId); |
||
| 1536 | if (count($lpValues['users']) > 0) { |
||
| 1537 | foreach ($lpValues['users'] as $user) { |
||
| 1538 | $table->setCellContents($i, 0, $item['issuer']); |
||
| 1539 | $table->setCellContents($i, 1, $lpName); |
||
| 1540 | $table->setCellContents($i, 2, $user['lastname']); |
||
| 1541 | $table->setCellContents($i, 3, $user['firstname']); |
||
| 1542 | $table->setCellContents($i, 4, $user['first_access']); |
||
| 1543 | $i++; |
||
| 1544 | } |
||
| 1545 | } |
||
| 1546 | } |
||
| 1547 | } |
||
| 1548 | } |
||
| 1549 | $content = $table->toHtml(); |
||
| 1550 | } |
||
| 1551 | |||
| 1552 | $content .= $form->returnForm(); |
||
| 1553 | if (!empty($actions)) { |
||
| 1554 | $content .= Display::toolbarAction('lti_tool_lp_toolbar', [$actions]); |
||
| 1555 | } |
||
| 1556 | |||
| 1557 | return $content; |
||
| 1558 | } |
||
| 1559 | |||
| 1560 | /** |
||
| 1561 | * Display the Logins By Date report and allow export its result to XLS. |
||
| 1562 | */ |
||
| 1563 | public static function printLoginsByDate() |
||
| 1564 | { |
||
| 1565 | if (isset($_GET['export']) && 'xls' === $_GET['export']) { |
||
| 1566 | $result = self::getLoginsByDate($_GET['start'], $_GET['end']); |
||
| 1567 | $data = [[get_lang('Username'), get_lang('FirstName'), get_lang('LastName'), get_lang('TotalTime')]]; |
||
| 1568 | |||
| 1569 | foreach ($result as $i => $item) { |
||
| 1570 | $data[] = [ |
||
| 1571 | $item['username'], |
||
| 1572 | $item['firstname'], |
||
| 1573 | $item['lastname'], |
||
| 1574 | api_time_to_hms($item['time_count']), |
||
| 1575 | ]; |
||
| 1576 | } |
||
| 1577 | |||
| 1578 | Export::arrayToXls($data); |
||
| 1579 | exit; |
||
|
|
|||
| 1580 | } |
||
| 1581 | |||
| 1582 | $content = Display::page_header(get_lang('LoginsByDate')); |
||
| 1583 | |||
| 1584 | $actions = ''; |
||
| 1585 | $form = new FormValidator('frm_logins_by_date', 'get'); |
||
| 1586 | $form->addDateRangePicker( |
||
| 1587 | 'daterange', |
||
| 1588 | get_lang('DateRange'), |
||
| 1589 | true, |
||
| 1590 | ['format' => 'YYYY-MM-DD', 'timePicker' => 'false', 'validate_format' => 'Y-m-d'] |
||
| 1591 | ); |
||
| 1592 | $form->addHidden('report', 'logins_by_date'); |
||
| 1593 | $form->addButtonFilter(get_lang('Search')); |
||
| 1594 | |||
| 1595 | if ($form->validate()) { |
||
| 1596 | $values = $form->exportValues(); |
||
| 1597 | |||
| 1598 | $result = self::getLoginsByDate($values['daterange_start'], $values['daterange_end']); |
||
| 1599 | |||
| 1600 | if (!empty($result)) { |
||
| 1601 | $actions = Display::url( |
||
| 1602 | Display::return_icon('excel.png', get_lang('ExportToXls'), [], ICON_SIZE_MEDIUM), |
||
| 1603 | api_get_self().'?'.http_build_query( |
||
| 1604 | [ |
||
| 1605 | 'report' => 'logins_by_date', |
||
| 1606 | 'export' => 'xls', |
||
| 1607 | 'start' => Security::remove_XSS($values['daterange_start']), |
||
| 1608 | 'end' => Security::remove_XSS($values['daterange_end']), |
||
| 1609 | ] |
||
| 1610 | ) |
||
| 1611 | ); |
||
| 1612 | } |
||
| 1613 | |||
| 1614 | $table = new HTML_Table(['class' => 'table table-hover table-striped data_table']); |
||
| 1615 | $table->setHeaderContents(0, 0, get_lang('Username')); |
||
| 1616 | $table->setHeaderContents(0, 1, get_lang('FirstName')); |
||
| 1617 | $table->setHeaderContents(0, 2, get_lang('LastName')); |
||
| 1618 | $table->setHeaderContents(0, 3, get_lang('TotalTime')); |
||
| 1619 | |||
| 1620 | foreach ($result as $i => $item) { |
||
| 1621 | $table->setCellContents($i + 1, 0, $item['username']); |
||
| 1622 | $table->setCellContents($i + 1, 1, $item['firstname']); |
||
| 1623 | $table->setCellContents($i + 1, 2, $item['lastname']); |
||
| 1624 | $table->setCellContents($i + 1, 3, api_time_to_hms($item['time_count'])); |
||
| 1625 | } |
||
| 1626 | |||
| 1627 | $table->setColAttributes(0, ['class' => 'text-center']); |
||
| 1628 | $table->setColAttributes(3, ['class' => 'text-center']); |
||
| 1629 | $content = $table->toHtml(); |
||
| 1630 | } |
||
| 1631 | |||
| 1632 | $content .= $form->returnForm(); |
||
| 1633 | |||
| 1634 | if (!empty($actions)) { |
||
| 1635 | $content .= Display::toolbarAction('logins_by_date_toolbar', [$actions]); |
||
| 1636 | } |
||
| 1637 | |||
| 1638 | return $content; |
||
| 1639 | } |
||
| 1640 | |||
| 1641 | public static function getBossTable($bossId): string |
||
| 1642 | { |
||
| 1643 | $students = UserManager::getUsersFollowedByStudentBoss($bossId); |
||
| 1644 | |||
| 1645 | if (!empty($students)) { |
||
| 1646 | $table = new HTML_Table(['class' => 'table table-responsive', 'id' => 'table_'.$bossId]); |
||
| 1647 | $headers = [ |
||
| 1648 | get_lang('Name'), |
||
| 1649 | //get_lang('LastName'), |
||
| 1650 | ]; |
||
| 1651 | $row = 0; |
||
| 1652 | $column = 0; |
||
| 1653 | foreach ($headers as $header) { |
||
| 1654 | $table->setHeaderContents($row, $column, $header); |
||
| 1655 | $column++; |
||
| 1656 | } |
||
| 1657 | $row++; |
||
| 1658 | foreach ($students as $student) { |
||
| 1659 | $column = 0; |
||
| 1660 | $content = api_get_person_name($student['firstname'], $student['lastname']).''; |
||
| 1661 | $content = '<div style="width: 200px; overflow-wrap: break-word;">'.$content.'</div>'; |
||
| 1662 | $table->setCellContents( |
||
| 1663 | $row, |
||
| 1664 | $column++, |
||
| 1665 | $content |
||
| 1666 | ); |
||
| 1667 | $row++; |
||
| 1668 | } |
||
| 1669 | |||
| 1670 | return $table->toHtml(); |
||
| 1671 | } |
||
| 1672 | |||
| 1673 | return '<table id="table_'.$bossId.'"></table>'; |
||
| 1674 | } |
||
| 1675 | |||
| 1676 | /** |
||
| 1677 | * Return a list of logins by date. |
||
| 1678 | * |
||
| 1679 | * @param string $startDate Start date in YYYY-MM-DD format |
||
| 1680 | * @param string $endDate End date in YYYY-MM-DD format |
||
| 1681 | */ |
||
| 1682 | public static function getLoginsByDate($startDate, $endDate): array |
||
| 1683 | { |
||
| 1684 | /** @var DateTime $startDate */ |
||
| 1685 | $startDate = api_get_utc_datetime("$startDate 00:00:00"); |
||
| 1686 | /** @var DateTime $endDate */ |
||
| 1687 | $endDate = api_get_utc_datetime("$endDate 23:59:59"); |
||
| 1688 | |||
| 1689 | if (empty($startDate) || empty($endDate)) { |
||
| 1690 | return []; |
||
| 1691 | } |
||
| 1692 | |||
| 1693 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
| 1694 | $tblLogin = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
| 1695 | $urlJoin = ''; |
||
| 1696 | $urlWhere = ''; |
||
| 1697 | |||
| 1698 | if (api_is_multiple_url_enabled()) { |
||
| 1699 | $tblUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
| 1700 | |||
| 1701 | $urlJoin = "INNER JOIN $tblUrlUser au ON u.id = au.user_id"; |
||
| 1702 | $urlWhere = 'AND au.access_url_id = '.api_get_current_access_url_id(); |
||
| 1703 | } |
||
| 1704 | |||
| 1705 | $sql = "SELECT u.id, |
||
| 1706 | u.firstname, |
||
| 1707 | u.lastname, |
||
| 1708 | u.username, |
||
| 1709 | SUM(TIMESTAMPDIFF(SECOND, l.login_date, l.logout_date)) AS time_count |
||
| 1710 | FROM $tblUser u |
||
| 1711 | INNER JOIN $tblLogin l ON u.id = l.login_user_id |
||
| 1712 | $urlJoin |
||
| 1713 | WHERE l.login_date BETWEEN '$startDate' AND '$endDate' |
||
| 1714 | $urlWhere |
||
| 1715 | GROUP BY u.id"; |
||
| 1716 | |||
| 1717 | $stmt = Database::query($sql); |
||
| 1718 | $result = Database::store_result($stmt, 'ASSOC'); |
||
| 1719 | |||
| 1720 | return $result; |
||
| 1721 | } |
||
| 1722 | |||
| 1723 | /** |
||
| 1724 | * Return de number of certificates generated. |
||
| 1725 | * This function is resource intensive. |
||
| 1726 | */ |
||
| 1727 | public static function countCertificatesByQuarter(string $dateFrom = null, string $dateUntil = null): int |
||
| 1728 | { |
||
| 1729 | $tableGradebookCertificate = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE); |
||
| 1730 | |||
| 1731 | $condition = ""; |
||
| 1732 | |||
| 1733 | if (!empty($dateFrom) && !empty($dateUntil)) { |
||
| 1734 | $dateFrom = api_get_utc_datetime("$dateFrom 00:00:00"); |
||
| 1735 | $dateUntil = api_get_utc_datetime("$dateUntil 23:59:59"); |
||
| 1736 | $condition = "WHERE (created_at BETWEEN '$dateFrom' AND '$dateUntil')"; |
||
| 1737 | } elseif (!empty($dateFrom)) { |
||
| 1738 | $dateFrom = api_get_utc_datetime("$dateFrom 00:00:00"); |
||
| 1739 | $condition = "WHERE created_at >= '$dateFrom'"; |
||
| 1740 | } elseif (!empty($dateUntil)) { |
||
| 1741 | $dateUntil = api_get_utc_datetime("$dateUntil 23:59:59"); |
||
| 1742 | $condition = "WHERE created_at <= '$dateUntil'"; |
||
| 1743 | } |
||
| 1744 | |||
| 1745 | $sql = " |
||
| 1746 | SELECT count(*) AS count |
||
| 1747 | FROM $tableGradebookCertificate |
||
| 1748 | $condition |
||
| 1749 | "; |
||
| 1750 | |||
| 1751 | $response = Database::query($sql); |
||
| 1752 | $obj = Database::fetch_object($response); |
||
| 1753 | |||
| 1754 | return $obj->count; |
||
| 1755 | } |
||
| 1756 | |||
| 1757 | /** |
||
| 1758 | * Get the number of logins by dates. |
||
| 1759 | * This function is resource intensive. |
||
| 1760 | */ |
||
| 1761 | public static function getSessionsByDuration(string $dateFrom, string $dateUntil): array |
||
| 1814 | } |
||
| 1815 | |||
| 1816 | /** |
||
| 1817 | * Return duplicate users at a SortableTableFromArray object. |
||
| 1818 | * |
||
| 1819 | * @param string $type The type of duplication we are checking for ('name' or 'email') |
||
| 1820 | */ |
||
| 1821 | public static function returnDuplicatedUsersTable( |
||
| 1822 | string $type = 'name', |
||
| 1823 | array $additionalExtraFieldsInfo |
||
| 1824 | ): SortableTableFromArray { |
||
| 1825 | if ($type == 'email') { |
||
| 1826 | $usersInfo = Statistics::getDuplicatedUserMails($additionalExtraFieldsInfo); |
||
| 1827 | } else { |
||
| 1828 | $usersInfo = Statistics::getDuplicatedUsers($additionalExtraFieldsInfo); |
||
| 1916 | } |
||
| 1917 | |||
| 1918 | /** |
||
| 1919 | * Exports a user report by course and session to an Excel file. |
||
| 1920 | */ |
||
| 1921 | public static function exportUserReportByCourseSession(int $courseId, ?string $startDate = null, ?string $endDate = null): void |
||
| 1922 | { |
||
| 1923 | $courseInfo = api_get_course_info_by_id($courseId); |
||
| 1924 | $sessions = SessionManager::get_session_by_course($courseId, $startDate, $endDate); |
||
| 1925 | |||
| 1926 | $headers = [ |
||
| 1927 | get_lang('CourseName'), |
||
| 1928 | get_lang('SessionName'), |
||
| 1929 | get_lang('LastName'), |
||
| 1930 | get_lang('FirstName'), |
||
| 1931 | get_lang('UserName'), |
||
| 1932 | get_lang('Email'), |
||
| 1933 | get_lang('EndDate'), |
||
| 1934 | get_lang('Score'), |
||
| 1935 | get_lang('Progress'), |
||
| 1936 | ]; |
||
| 1937 | |||
| 1938 | $extraField = new ExtraField('user'); |
||
| 1939 | $extraFields = $extraField->get_all(['filter = ?' => 1], 'option_order'); |
||
| 1940 | |||
| 1941 | foreach ($extraFields as $field) { |
||
| 1942 | $headers[] = $field['variable']; |
||
| 1943 | } |
||
| 1944 | |||
| 1945 | $exportData = [$headers]; |
||
| 1946 | foreach ($sessions as $session) { |
||
| 1947 | $sessionId = (int) $session['id']; |
||
| 1948 | $students = SessionManager::get_users_by_session($sessionId); |
||
| 1949 | $extraValueObj = new ExtraFieldValue('user'); |
||
| 1950 | |||
| 1951 | foreach ($students as $student) { |
||
| 1952 | $studentId = $student['user_id']; |
||
| 1953 | $studentInfo = api_get_user_info($studentId); |
||
| 1954 | $courseCode = $courseInfo['code']; |
||
| 1955 | |||
| 1956 | $lastConnection = Tracking::getLastConnectionTimeInSessionCourseLp($studentId, $courseCode, $sessionId); |
||
| 1957 | $lastConnectionFormatted = $lastConnection ? date('Y-m-d', $lastConnection) : ''; |
||
| 1958 | |||
| 1959 | $averageScore = round(Tracking::getAverageStudentScore($studentId, $courseCode, [], $sessionId)); |
||
| 1960 | $averageProgress = round(Tracking::get_avg_student_progress($studentId, $courseCode, [], $sessionId)); |
||
| 1961 | |||
| 1962 | $userData = [ |
||
| 1963 | $courseInfo['name'], |
||
| 1964 | $session['name'], |
||
| 1965 | $studentInfo['lastname'], |
||
| 1966 | $studentInfo['firstname'], |
||
| 1967 | $studentInfo['username'], |
||
| 1968 | $studentInfo['mail'], |
||
| 1969 | $lastConnectionFormatted, |
||
| 1970 | $averageScore, |
||
| 1971 | $averageProgress, |
||
| 1972 | ]; |
||
| 1973 | |||
| 1974 | foreach ($extraFields as $field) { |
||
| 1975 | $extraValue = $extraValueObj->get_values_by_handler_and_field_id($studentId, $field['id'], true); |
||
| 1976 | $userData[] = $extraValue['value'] ?? ''; |
||
| 1977 | } |
||
| 1978 | |||
| 1979 | $exportData[] = $userData; |
||
| 1980 | } |
||
| 1981 | } |
||
| 1982 | |||
| 1983 | Export::arrayToXls($exportData, 'session_report_'.$courseInfo['code'].'_'.date('Y-m-d')); |
||
| 1984 | } |
||
| 1985 | |||
| 1986 | /** |
||
| 1987 | * It gets lti learnpath results by date. |
||
| 1988 | * |
||
| 1989 | * @param string $startDate Start date in YYYY-MM-DD format |
||
| 1990 | * @param string $endDate End date in YYYY-MM-DD format |
||
| 1991 | */ |
||
| 1992 | private static function getLtiLearningPathByDate(string $startDate, string $endDate): array |
||
| 1993 | { |
||
| 1994 | /** @var DateTime $startDate */ |
||
| 1995 | $startDate = api_get_utc_datetime("$startDate 00:00:00"); |
||
| 1996 | /** @var DateTime $endDate */ |
||
| 1997 | $endDate = api_get_utc_datetime("$endDate 23:59:59"); |
||
| 1998 | |||
| 1999 | if (empty($startDate) || empty($endDate)) { |
||
| 2000 | return []; |
||
| 2001 | } |
||
| 2002 | |||
| 2003 | require_once api_get_path(SYS_PLUGIN_PATH).'lti_provider/LtiProviderPlugin.php'; |
||
| 2004 | |||
| 2005 | $plugin = LtiProviderPlugin::create(); |
||
| 2006 | |||
| 2007 | $result = $plugin->getToolLearnPathResult($startDate, $endDate); |
||
| 2008 | |||
| 2009 | return $result; |
||
| 2010 | } |
||
| 2011 | |||
| 2012 | /** |
||
| 2013 | * Get a list of users duplicated (firstname and lastname are both the same). |
||
| 2014 | * |
||
| 2015 | * @param array $additionalExtraFieldsInfo A list of extra fields we want to get in return, additional to the user details |
||
| 2016 | */ |
||
| 2017 | private static function getDuplicatedUsers(array $additionalExtraFieldsInfo): array |
||
| 2018 | { |
||
| 2019 | $sql = "SELECT firstname, lastname, COUNT(*) as count |
||
| 2020 | FROM user |
||
| 2021 | GROUP BY firstname, lastname |
||
| 2022 | HAVING count > 1 |
||
| 2023 | ORDER BY lastname, firstname" |
||
| 2024 | ; |
||
| 2025 | |||
| 2026 | $result = Database::query($sql); |
||
| 2027 | |||
| 2028 | if (1 > Database::num_rows($result)) { |
||
| 2029 | return []; |
||
| 2030 | } |
||
| 2031 | |||
| 2032 | $usersInfo = []; |
||
| 2033 | |||
| 2034 | while ($rowStat = Database::fetch_assoc($result)) { |
||
| 2035 | $firstname = Database::escape_string($rowStat['firstname']); |
||
| 2036 | $lastname = Database::escape_string($rowStat['lastname']); |
||
| 2037 | $subsql = "SELECT id, email, registration_date, status, active |
||
| 2038 | FROM user WHERE firstname = '$firstname' AND lastname = '$lastname'" |
||
| 2039 | ; |
||
| 2040 | |||
| 2041 | $subResult = Database::query($subsql); |
||
| 2042 | |||
| 2043 | if (1 > Database::num_rows($subResult)) { |
||
| 2044 | continue; |
||
| 2045 | } |
||
| 2046 | |||
| 2047 | $objExtraValue = new ExtraFieldValue('user'); |
||
| 2048 | |||
| 2049 | while ($rowUser = Database::fetch_assoc($subResult)) { |
||
| 2050 | $studentId = $rowUser['id']; |
||
| 2051 | |||
| 2052 | $studentInfo = []; |
||
| 2053 | $studentInfo[] = $rowUser['id']; |
||
| 2054 | |||
| 2055 | if (api_is_western_name_order()) { |
||
| 2056 | $studentInfo[] = $rowStat['firstname']; |
||
| 2057 | $studentInfo[] = $rowStat['lastname']; |
||
| 2058 | } else { |
||
| 2059 | $studentInfo[] = $rowStat['lastname']; |
||
| 2060 | $studentInfo[] = $rowStat['firstname']; |
||
| 2061 | } |
||
| 2062 | |||
| 2063 | $studentInfo[] = $rowUser['email']; |
||
| 2064 | $studentInfo[] = $rowUser['registration_date']; |
||
| 2065 | $studentInfo[] = Tracking::get_first_connection_date( |
||
| 2066 | $studentId, |
||
| 2067 | DATE_TIME_FORMAT_LONG |
||
| 2068 | ); |
||
| 2069 | $studentInfo[] = Tracking::get_last_connection_date( |
||
| 2070 | $studentId, |
||
| 2071 | true, |
||
| 2072 | false, |
||
| 2073 | DATE_TIME_FORMAT_LONG |
||
| 2074 | ); |
||
| 2075 | $studentInfo[] = $rowUser['status']; |
||
| 2076 | $studentInfo[] = Tracking::count_course_per_student($studentId); |
||
| 2077 | $studentInfo[] = Tracking::countSessionsPerStudent($studentId); |
||
| 2078 | |||
| 2079 | foreach ($additionalExtraFieldsInfo as $fieldInfo) { |
||
| 2080 | $extraValue = $objExtraValue->get_values_by_handler_and_field_id($studentId, $fieldInfo['id'], true); |
||
| 2081 | $studentInfo[] = $extraValue['value'] ?? null; |
||
| 2082 | } |
||
| 2083 | |||
| 2084 | $studentInfo[] = $rowUser['active']; // once to show status |
||
| 2085 | $studentInfo[] = $rowUser['active']; // twice to show actions |
||
| 2086 | |||
| 2087 | $usersInfo[] = $studentInfo; |
||
| 2088 | } |
||
| 2089 | } |
||
| 2090 | |||
| 2091 | return $usersInfo; |
||
| 2092 | } |
||
| 2093 | |||
| 2094 | /** |
||
| 2095 | * Get a list of duplicated user emails. |
||
| 2096 | * |
||
| 2097 | * @param array $additionalExtraFieldsInfo A list of extra fields we want to get in return, additional to the user details |
||
| 2098 | */ |
||
| 2099 | private static function getDuplicatedUserMails(array $additionalExtraFieldsInfo): array |
||
| 2173 | } |
||
| 2174 | } |
||
| 2175 |
In general, usage of exit should be done with care and only when running in a scripting context like a CLI script.