ILIAS  trunk Revision v12.0_alpha-377-g3641b37b9db
class.ilTrQuery.php
Go to the documentation of this file.
1<?php
2
19declare(strict_types=0);
20
21class ilTrQuery
22{
23 public static function getObjectsStatusForUser(
24 int $a_user_id,
25 array $obj_refs
26 ): array {
27 global $DIC;
28
29 $ilDB = $DIC->database();
30
31 if (sizeof($obj_refs)) {
32 $obj_ids = array_keys($obj_refs);
33 self::refreshObjectsStatus($obj_ids, array($a_user_id));
34
35 // prepare object view modes
36 $view_modes = array();
37 $query = "SELECT obj_id, view_mode FROM crs_settings" .
38 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer");
39 $set = $ilDB->query($query);
40 while ($rec = $ilDB->fetchAssoc($set)) {
41 $view_modes[(int) $rec["obj_id"]] = (int) $rec["view_mode"];
42 }
43
44 $sessions = self::getSessionData($a_user_id, $obj_ids);
45
46 $query = "SELECT object_data.obj_id, title, CASE WHEN status IS NULL THEN " . ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM . " ELSE status END AS status," .
47 " status_changed, percentage, read_count+childs_read_count AS read_count, spent_seconds+childs_spent_seconds AS spent_seconds," .
48 " u_mode, type, visits, mark, u_comment" .
49 " FROM object_data" .
50 " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)" .
51 " LEFT JOIN read_event ON (read_event.obj_id = object_data.obj_id AND read_event.usr_id = " . $ilDB->quote(
52 $a_user_id,
53 "integer"
54 ) . ")" .
55 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.obj_id = object_data.obj_id AND ut_lp_marks.usr_id = " . $ilDB->quote(
56 $a_user_id,
57 "integer"
58 ) . ")" .
59 // " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(ilLPObjSettings::LP_MODE_DEACTIVATED, "integer").")".
60 " WHERE " . $ilDB->in(
61 "object_data.obj_id",
62 $obj_ids,
63 false,
64 "integer"
65 ) .
66 " ORDER BY title";
67 $set = $ilDB->query($query);
68 $result = array();
69 while ($rec = $ilDB->fetchAssoc($set)) {
70 $rec["comment"] = $rec["u_comment"];
71 unset($rec["u_comment"]);
72
73 $rec["ref_ids"] = $obj_refs[(int) $rec["obj_id"]];
74 $rec["status"] = (int) $rec["status"];
75 $rec["percentage"] = (int) $rec["percentage"];
76 $rec["read_count"] = (int) $rec["read_count"];
77 $rec["spent_seconds"] = (int) $rec["spent_seconds"];
78 $rec["u_mode"] = (int) $rec["u_mode"];
79
80 if ($rec["type"] == "sess") {
81 $session = $sessions[(int) $rec["obj_id"]];
82 $rec["title"] = $session["title"];
83 // $rec["status"] = (int)$session["status"];
84 }
85
86 // lp mode might not match object/course view mode
87 if ($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == ilCourseConstants::IL_CRS_VIEW_OBJECTIVE) {
88 $rec["u_mode"] = ilLPObjSettings::LP_MODE_OBJECTIVES;
89 } elseif (!$rec["u_mode"]) {
90 $olp = ilObjectLP::getInstance($rec["obj_id"]);
91 $rec["u_mode"] = $olp->getCurrentMode();
92 }
93
94 // can be default mode
95 if (/*$rec["u_mode"] != ilLPObjSettings::LP_MODE_DEACTIVATE*/ true) {
96 $result[] = $rec;
97 }
98 }
99 return $result;
100 }
101 return [];
102 }
103
104 public static function getObjectivesStatusForUser(
105 int $a_user_id,
106 int $a_obj_id,
107 array $a_objective_ids
108 ): array {
109 global $DIC;
110
111 $ilDB = $DIC->database();
112
113 $lo_lp_status = ilLOUserResults::getObjectiveStatusForLP(
114 $a_user_id,
115 $a_obj_id,
116 $a_objective_ids
117 );
118
119 $query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title," . $ilDB->quote(
120 "lobj",
121 "text"
122 ) . " AS type" .
123 " FROM crs_objectives" .
124 " WHERE " . $ilDB->in(
125 "crs_objectives.objective_id",
126 $a_objective_ids,
127 false,
128 "integer"
129 ) .
130 " AND active = " . $ilDB->quote(1, "integer") .
131 " ORDER BY position";
132 $set = $ilDB->query($query);
133 $result = array();
134 while ($rec = $ilDB->fetchAssoc($set)) {
135 $rec['crs_id'] = (int) $rec['crs_id'];
136 $rec['obj_id'] = (int) $rec['obj_id'];
137 if (array_key_exists($rec["obj_id"], $lo_lp_status)) {
138 $rec["status"] = $lo_lp_status[$rec["obj_id"]];
139 } else {
140 $rec["status"] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
141 }
142 $result[] = $rec;
143 }
144
145 return $result;
146 }
147
148 public static function getSCOsStatusForUser(
149 int $a_user_id,
150 int $a_parent_obj_id,
151 array $a_sco_ids
152 ): array {
153 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
154
155 // import score from tracking data
156 $scores_raw = $scores = array();
157 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
158 switch ($subtype) {
159 case 'hacp':
160 case 'aicc':
161 case 'scorm':
162 $module = new ilObjSCORMLearningModule($a_parent_obj_id, false);
163 $scores_raw = $module->getTrackingDataAgg($a_user_id);
164 break;
165
166 case 'scorm2004':
167 $module = new ilObjSCORM2004LearningModule(
168 $a_parent_obj_id,
169 false
170 );
171 $scores_raw = $module->getTrackingDataAgg($a_user_id);
172 break;
173 }
174 if ($scores_raw) {
175 foreach ($scores_raw as $item) {
176 $scores[$item["sco_id"]] = $item["score"];
177 }
178 unset($module);
179 unset($scores_raw);
180 }
181
182 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
183
184 $items = array();
185 foreach ($a_sco_ids as $sco_id) {
186 // #9719 - can have in_progress AND failed/completed
187 if (in_array($a_user_id, $status_info["failed"][$sco_id])) {
188 $status = ilLPStatus::LP_STATUS_FAILED_NUM;
189 } elseif (in_array(
190 $a_user_id,
191 $status_info["completed"][$sco_id]
192 )) {
193 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
194 } elseif (in_array(
195 $a_user_id,
196 $status_info["in_progress"][$sco_id]
197 )) {
198 $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
199 } else {
200 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
201 }
202
203 $items[$sco_id] = array(
204 "title" => $status_info["scos_title"][$sco_id],
205 "status" => $status,
206 "type" => "sahs",
207 "score" => (int) ($scores[$sco_id] ?? 0)
208 );
209 }
210 return $items;
211 }
212
216 public static function getSubItemsStatusForUser(
217 int $a_user_id,
218 int $a_parent_obj_id,
219 array $a_item_ids
220 ): array {
221 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
222
223 switch (ilObject::_lookupType($a_parent_obj_id)) {
224 case "lm":
225 case "mcst":
226 $olp = ilObjectLP::getInstance($a_parent_obj_id);
227 $collection = $olp->getCollectionInstance();
228 if ($collection) {
229 $ref_ids = ilObject::_getAllReferences($a_parent_obj_id);
230 $ref_id = end($ref_ids);
231 $item_data = $collection->getPossibleItems($ref_id);
232 }
233 break;
234
235 default:
236 return array();
237 }
238
239 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
240
241 $items = array();
242 foreach ($a_item_ids as $item_id) {
243 if (!isset($item_data[$item_id])) {
244 continue;
245 }
246
247 if (in_array($a_user_id, ($status_info["completed"][$item_id] ?? []))) {
248 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
249 } elseif (in_array($a_user_id, ($status_info["in_progress"][$item_id] ?? []))) {
250 $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
251 } else {
252 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
253 }
254
255 $items[$item_id] = array(
256 "title" => ($item_data[$item_id]["title"] ?? ''),
257 "status" => (int) $status,
258 "type" => self::getSubItemType($a_parent_obj_id)
259 );
260 }
261
262 return $items;
263 }
264
265 public static function getUserDataForObject(
266 int $a_ref_id,
267 string $a_order_field = "",
268 string $a_order_dir = "",
269 int $a_offset = 0,
270 int $a_limit = 9999,
271 ?array $a_filters = null,
272 ?array $a_additional_fields = null,
273 ?int $check_agreement = null,
274 ?array $privacy_fields = null
275 ): array {
276 global $DIC;
277
278 $ilDB = $DIC->database();
279
280 $fields = array("usr_data.usr_id", "login", "active");
281 $udf = self::buildColumns($fields, $a_additional_fields);
282
283 $where = array();
284 $where[] = "usr_data.usr_id <> " . $ilDB->quote(
285 ANONYMOUS_USER_ID,
286 "integer"
287 );
288
289 // users
290 $left = "";
291 $a_users = self::getParticipantsForObject($a_ref_id);
292
293 $obj_id = ilObject::_lookupObjectId($a_ref_id);
294 self::refreshObjectsStatus(array($obj_id), $a_users);
295
296 if (is_array($a_users)) {
297 $left = "LEFT";
298 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
299 }
300
301 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
302 " AND read_event.obj_id = " . $ilDB->quote(
303 $obj_id,
304 "integer"
305 ) . ")" .
306 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
307 " AND ut_lp_marks.obj_id = " . $ilDB->quote(
308 $obj_id,
309 "integer"
310 ) . ")" .
311 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote(
312 "language",
313 "text"
314 ) . ")" .
315 self::buildFilters($where, $a_filters);
316
317 $queries = array(array("fields" => $fields, "query" => $query));
318
319 // #9598 - if language is not in fields alias is missing
320 if ($a_order_field == "language") {
321 $a_order_field = "usr_pref.value";
322 }
323
324 // udf data is added later on, not in this query
325 $udf_order = null;
326 if (!$a_order_field) {
327 $a_order_field = "login";
328 } elseif (substr($a_order_field, 0, 4) == "udf_") {
329 $udf_order = $a_order_field;
330 $a_order_field = '';
331 }
332 $result = self::executeQueries(
333 $queries,
334 $a_order_field,
335 $a_order_dir,
336 $a_offset,
337 $a_limit
338 );
339
340 self::getUDFAndHandlePrivacy(
341 $result,
342 $udf,
343 $check_agreement,
344 $privacy_fields,
345 $a_filters
346 );
347
348 // as we cannot do this in the query, sort by custom field here
349 // this will not work with pagination!
350 if ($udf_order) {
351 $result["set"] = ilArrayUtil::stableSortArray(
352 $result["set"],
353 $udf_order,
354 $a_order_dir
355 );
356 }
357
358 return $result;
359 }
360
364 protected static function getUDFAndHandlePrivacy(
365 array &$a_result,
366 ?array $a_udf = null,
367 ?int $a_check_agreement = null,
368 ?array $a_privacy_fields = null,
369 ?array $a_filters = null
370 ): array {
371 global $DIC;
372
373 $ilDB = $DIC->database();
374
375 if (!$a_result["cnt"]) {
376 return [];
377 }
378
379 if (is_array($a_udf) && count($a_udf) > 0) {
380 $query = "SELECT usr_id, field_id, value FROM udf_text WHERE " . $ilDB->in(
381 "field_id",
382 $a_udf,
383 false,
384 "integer"
385 );
386 $set = $ilDB->query($query);
387 $udf = array();
388 while ($row = $ilDB->fetchAssoc($set)) {
389 $udf[(int) $row["usr_id"]]["udf_" . $row["field_id"]] = $row["value"];
390 }
391 }
392
393 // (course/group) user agreement
394 if ($a_check_agreement) {
395 // admins/tutors (write-access) will never have agreement ?!
396 $agreements = ilMemberAgreement::lookupAcceptedAgreements(
397 $a_check_agreement
398 );
399
400 // public information for users
401 $query = "SELECT usr_id FROM usr_pref WHERE keyword = " . $ilDB->quote(
402 "public_profile",
403 "text"
404 ) .
405 " AND value = " . $ilDB->quote(
406 "y",
407 "text"
408 ) . " OR value = " . $ilDB->quote("g", "text");
409 $set = $ilDB->query($query);
410 $all_public = array();
411 while ($row = $ilDB->fetchAssoc($set)) {
412 $all_public[] = $row["usr_id"];
413 }
414 $query = "SELECT usr_id,keyword FROM usr_pref WHERE " . $ilDB->like(
415 "keyword",
416 "text",
417 "public_%",
418 false
419 ) .
420 " AND value = " . $ilDB->quote(
421 "y",
422 "text"
423 ) . " AND " . $ilDB->in(
424 "usr_id",
425 $all_public,
426 false,
427 "integer"
428 );
429 $set = $ilDB->query($query);
430 $public = array();
431 while ($row = $ilDB->fetchAssoc($set)) {
432 $public[$row["usr_id"]][] = substr($row["keyword"], 7);
433 }
434 unset($all_public);
435 }
436
437 foreach ($a_result["set"] as $idx => $row) {
438 // add udf data
439 if (isset($udf[$row["usr_id"]])) {
440 $a_result["set"][$idx] = $row = array_merge(
441 $row,
442 $udf[$row["usr_id"]]
443 );
444 }
445
446 // remove all private data - if active agreement and agreement not given by user
447 if (sizeof($a_privacy_fields) && $a_check_agreement && !in_array(
448 $row["usr_id"],
449 $agreements
450 )) {
451 foreach ($a_privacy_fields as $field) {
452 // check against public profile
453 if (isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
454 !in_array($field, $public[$row["usr_id"]]))) {
455 // remove complete entry - offending field was filtered
456 if (isset($a_filters[$field])) {
457 // we cannot remove row because of pagination!
458 foreach (array_keys($row) as $col_id) {
459 $a_result["set"][$idx][$col_id] = null;
460 }
461 $a_result["set"][$idx]["privacy_conflict"] = true;
462 // unset($a_result["set"][$idx]);
463 break;
464 } // remove offending field
465 else {
466 $a_result["set"][$idx][$field] = false;
467 }
468 }
469 }
470 }
471 }
472 return [];
473 }
474
478 public static function getObjectsDataForUser(
479 int $a_user_id,
480 int $a_parent_obj_id,
481 int $a_parent_ref_id,
482 string $a_order_field = "",
483 string $a_order_dir = "",
484 int $a_offset = 0,
485 int $a_limit = 9999,
486 ?array $a_filters = null,
487 ?array $a_additional_fields = null,
488 bool $use_collection = true
489 ): array {
490 global $DIC;
491
492 $ilDB = $DIC->database();
493
494 $fields = array("object_data.obj_id", "title", "type");
495 self::buildColumns($fields, $a_additional_fields);
496
497 $objects = self::getObjectIds(
498 $a_parent_obj_id,
499 $a_parent_ref_id,
500 $use_collection,
501 true,
502 array($a_user_id)
503 );
504
505 $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND" .
506 " read_event.usr_id = " . $ilDB->quote(
507 $a_user_id,
508 "integer"
509 ) . ")" .
510 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = " . $ilDB->quote(
511 $a_user_id,
512 "integer"
513 ) . " AND" .
514 " ut_lp_marks.obj_id = object_data.obj_id)" .
515 " WHERE " . $ilDB->in(
516 "object_data.obj_id",
517 $objects["object_ids"],
518 false,
519 "integer"
520 ) .
521 self::buildFilters(array(), $a_filters);
522
523 $queries = array();
524 $queries[] = array("fields" => $fields, "query" => $query);
525
526 if (!in_array($a_order_field, $fields)) {
527 $a_order_field = "title";
528 }
529
530 $result = self::executeQueries(
531 $queries,
532 $a_order_field,
533 $a_order_dir,
534 $a_offset,
535 $a_limit
536 );
537 if ($result["cnt"]) {
538 // session data
539 $sessions = self::getSessionData(
540 $a_user_id,
541 $objects["object_ids"]
542 );
543
544 foreach ($result["set"] as $idx => $item) {
545 if ($item["type"] == "sess") {
546 $session = $sessions[(int) $item["obj_id"]];
547 $result["set"][$idx]["title"] = $session["title"];
548 $result["set"][$idx]["sort_title"] = $session["e_start"];
549 // $result["set"][$idx]["status"] = (int)$session["status"];
550 }
551
552 $result["set"][$idx]["ref_id"] = $objects["ref_ids"][(int) $item["obj_id"]];
553
554 // BT 35475: set titles of referenced objects correctly
555 if (
556 $item['title'] == '' &&
557 ($item['type'] == 'catr' ||
558 $item['type'] == 'crsr' ||
559 $item['type'] == 'grpr')
560 ) {
561 $result['set'][$idx]['title'] =
562 ilContainerReference::_lookupTargetTitle(
563 (int) $item["obj_id"]
564 );
565 }
566 }
567
568 // scos data (:TODO: will not be part of offset/limit)
569 if ($objects["scorm"]) {
570 $subtype = ilObjSAHSLearningModule::_lookupSubType(
571 $a_parent_obj_id
572 );
573 if ($subtype == "scorm2004") {
574 $sobj = new ilObjSCORM2004LearningModule(
575 $a_parent_ref_id,
576 true
577 );
578 $scos_tracking = $sobj->getTrackingDataAgg(
579 $a_user_id,
580 true
581 );
582 } else {
583 $sobj = new ilObjSCORMLearningModule(
584 $a_parent_ref_id,
585 true
586 );
587 $scos_tracking = array();
588 foreach ($sobj->getTrackingDataAgg($a_user_id) as $item) {
589 // format: hhhh:mm:ss ?!
590 if ($item["time"]) {
591 $time = explode(":", $item["time"]);
592 $item["time"] = $time[0] * 60 * 60 + $time[1] * 60 + $time[2];
593 }
594 $scos_tracking[(int) $item["sco_id"]] = array("session_time" => $item["time"]);
595 }
596 }
597
598 foreach ($objects["scorm"]["scos"] as $sco) {
599 $row = array("title" => $objects["scorm"]["scos_title"][$sco],
600 "type" => "sco"
601 );
602
603 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
604 if (in_array(
605 $a_user_id,
606 $objects["scorm"]["completed"][$sco]
607 )) {
608 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
609 } elseif (in_array(
610 $a_user_id,
611 $objects["scorm"]["failed"][$sco]
612 )) {
613 $status = ilLPStatus::LP_STATUS_FAILED_NUM;
614 } elseif (in_array(
615 $a_user_id,
616 $objects["scorm"]["in_progress"][$sco]
617 )) {
618 $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
619 }
620 $row["status"] = $status;
621
622 // add available tracking data
623 if (isset($scos_tracking[$sco])) {
624 if (isset($scos_tracking[$sco]["last_access"])) {
625 $date = new ilDateTime(
626 $scos_tracking[$sco]["last_access"],
627 IL_CAL_DATETIME
628 );
629 $row["last_access"] = $date->get(IL_CAL_UNIX);
630 }
631 $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
632 }
633
634 $result["set"][] = $row;
635 $result["cnt"] = ($result["cnt"] ?? 0) + 1;
636 }
637 }
638
639 // #15379 - objectives data
640 if ($objects["objectives_parent_id"]) {
641 $objtv_ids = ilCourseObjective::_getObjectiveIds(
642 $objects["objectives_parent_id"],
643 true
644 );
645 foreach (self::getObjectivesStatusForUser(
646 $a_user_id,
647 $objects["objectives_parent_id"],
648 $objtv_ids
649 ) as $item) {
650 $result["set"][] = $item;
651 $result["cnt"] = ($result["cnt"] ?? 0) + 1;
652 }
653 }
654
655 // subitem data
656 if ($objects["subitems"]) {
657 $sub_type = self::getSubItemType($a_parent_obj_id);
658 foreach ($objects["subitems"]["items"] as $item_id) {
659 $row = array("title" => $objects["subitems"]["item_titles"][$item_id],
660 "type" => $sub_type
661 );
662
663 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
664 if (in_array(
665 $a_user_id,
666 $objects["subitems"]["completed"][(int) $item_id]
667 )) {
668 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
669 }
670 $row["status"] = $status;
671
672 $result["set"][] = $row;
673 $result["cnt"] = ($result["cnt"] ?? 0) + 1;
674 }
675 }
676 }
677 return $result;
678 }
679
683 public static function getSubItemType(int $a_parent_obj_id): string
684 {
685 switch (ilObject::_lookupType($a_parent_obj_id)) {
686 case "lm":
687 return "st";
688
689 case "mcst":
690 return "mob";
691 }
692 return '';
693 }
694
698 protected static function getSessionData(
699 int $a_user_id,
700 array $obj_ids
701 ): array {
702 global $DIC;
703
704 $ilDB = $DIC->database();
705 $query = "SELECT obj_id, title, e_start, e_end, CASE WHEN participated = 1 THEN 2 WHEN registered = 1 THEN 1 ELSE NULL END AS status," .
706 " mark, e_comment" .
707 " FROM event" .
708 " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)" .
709 " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = " . $ilDB->quote(
710 $a_user_id,
711 "integer"
712 ) . ")" .
713 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer");
714 $set = $ilDB->query($query);
715 $sessions = array();
716 while ($rec = $ilDB->fetchAssoc($set)) {
717 $rec["comment"] = $rec["e_comment"];
718 unset($rec["e_comment"]);
719
720 $date = ilDatePresentation::formatPeriod(
721 new ilDateTime(
722 $rec["e_start"],
723 IL_CAL_DATETIME,
724 ilTimeZone::UTC
725 ),
726 new ilDateTime($rec["e_end"], IL_CAL_DATETIME, ilTimeZone::UTC)
727 );
728
729 if ($rec["title"]) {
730 $rec["title"] = $date . ': ' . $rec["title"];
731 } else {
732 $rec["title"] = $date;
733 }
734 $sessions[(int) $rec["obj_id"]] = $rec;
735 }
736 return $sessions;
737 }
738
743 public static function getObjectsSummaryForObject(
744 int $a_parent_obj_id,
745 int $a_parent_ref_id,
746 string $a_order_field = "",
747 string $a_order_dir = "",
748 int $a_offset = 0,
749 int $a_limit = 9999,
750 ?array $a_filters = null,
751 ?array $a_additional_fields = null,
752 ?array $a_preselected_obj_ids = null
753 ): array {
754 global $DIC;
755
756 $ilDB = $DIC->database();
757
758 $fields = array();
759 self::buildColumns($fields, $a_additional_fields, true);
760
761 $objects = array();
762 if ($a_preselected_obj_ids === null) {
763 $objects = self::getObjectIds(
764 $a_parent_obj_id,
765 $a_parent_ref_id,
766 false,
767 false
768 );
769 } else {
770 foreach ($a_preselected_obj_ids as $obj_id => $ref_ids) {
771 $objects["object_ids"][] = $obj_id;
772 $objects["ref_ids"][$obj_id] = array_pop($ref_ids);
773 }
774 }
775
776 $result = array();
777 $object_data = [];
778 if ($objects) {
779 // object data
780 $set = $ilDB->query(
781 "SELECT obj_id,title,type FROM object_data" .
782 " WHERE " . $ilDB->in(
783 "obj_id",
784 $objects["object_ids"],
785 false,
786 "integer"
787 )
788 );
789 while ($rec = $ilDB->fetchAssoc($set)) {
790 $object_data[(int) $rec["obj_id"]] = $rec;
791 if ($a_preselected_obj_ids) {
792 $object_data[(int) $rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[(int) $rec["obj_id"]];
793 } else {
794 $object_data[(int) $rec["obj_id"]]["ref_ids"] = array($objects["ref_ids"][(int) $rec["obj_id"]]);
795 }
796 }
797
798 foreach ($objects["ref_ids"] as $object_id => $ref_id) {
799 $object_result = self::getSummaryDataForObject(
800 $ref_id,
801 $fields,
802 $a_filters
803 );
804 if (sizeof($object_result)) {
805 if ($object_data[$object_id]) {
806 $result[] = array_merge(
807 $object_data[$object_id],
808 $object_result
809 );
810 }
811 }
812 }
813 // @todo: old to do objectives ?
814 }
815
816 return array("cnt" => sizeof($result), "set" => $result);
817 }
818
819 protected static function getSummaryDataForObject(
820 int $a_ref_id,
821 array $fields,
822 ?array $a_filters = null
823 ): array {
824 global $DIC;
825
826 $ilDB = $DIC['ilDB'];
827
828 $where = array();
829 $where[] = "usr_data.usr_id <> " . $ilDB->quote(
830 ANONYMOUS_USER_ID,
831 "integer"
832 );
833
834 // users
835 $a_users = self::getParticipantsForObject($a_ref_id);
836
837 $left = "";
838 if (is_array($a_users)) { // #14840
839 $left = "LEFT";
840 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
841 }
842
843 $obj_id = ilObject::_lookupObjectId($a_ref_id);
844 self::refreshObjectsStatus(array($obj_id), $a_users);
845
846 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
847 " AND obj_id = " . $ilDB->quote($obj_id, "integer") . ")" .
848 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
849 " AND ut_lp_marks.obj_id = " . $ilDB->quote(
850 $obj_id,
851 "integer"
852 ) . ")" .
853 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote(
854 "language",
855 "text"
856 ) . ")" .
857 self::buildFilters($where, $a_filters, true);
858
859 $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
860
861 $queries = array();
862 $queries[] = array("fields" => $fields,
863 "query" => $query,
864 "count" => "*"
865 );
866
867 $result = self::executeQueries($queries);
868 $result = (array) ($result['set'][0] ?? []);
869 $users_no = $result["user_count"] ?? 0;
870
871 $valid = true;
872 if (!$users_no) {
873 $valid = false;
874 } elseif (isset($a_filters["user_total"])) {
875 if ($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"]) {
876 $valid = false;
877 } elseif ($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"]) {
878 $valid = false;
879 }
880 }
881
882 if ($valid) {
883 $result["country"] = self::getSummaryPercentages(
884 "country",
885 $query
886 );
887 $result["city"] = self::getSummaryPercentages("city", $query);
888 $result["gender"] = self::getSummaryPercentages("gender", $query);
889 $result["language"] = self::getSummaryPercentages(
890 "usr_pref.value",
891 $query,
892 "language"
893 );
894 $result["status"] = self::getSummaryPercentages("status", $query);
895 $result["mark"] = self::getSummaryPercentages("mark", $query);
896 } else {
897 $result = array();
898 }
899
900 if ($result) {
901 $result["user_total"] = $users_no;
902 }
903
904 return $result;
905 }
906
910 protected static function getSummaryPercentages(
911 string $field,
912 string $base_query,
913 ?string $alias = null
914 ): array {
915 global $DIC;
916
917 $ilDB = $DIC['ilDB'];
918
919 if (!$alias) {
920 $field_alias = $field;
921 } else {
922 $field_alias = $alias;
923 $alias = " AS " . $alias;
924 }
925
926 // move having BEHIND group by
927 $having = "";
928 if (preg_match(
929 "/" . preg_quote(" [[--HAVING") . "(.+)" . preg_quote(
930 "HAVING--]]"
931 ) . "/",
932 $base_query,
933 $hits
934 )) {
935 $having = " HAVING " . $hits[1];
936 $base_query = str_replace($hits[0], "", $base_query);
937 }
938
939 $query = "SELECT COUNT(*) AS counter, " . $field . $alias . " " . $base_query . " GROUP BY " . $field . $having . " ORDER BY counter DESC";
940 $set = $ilDB->query($query);
941 $result = array();
942 while ($rec = $ilDB->fetchAssoc($set)) {
943 $result[$rec[$field_alias]] = (int) $rec["counter"];
944 }
945 return $result;
946 }
947
953 public static function getParticipantsForObject(int $a_ref_id): ?array
954 {
955 global $DIC;
956
957 $tree = $DIC['tree'];
958
959 $obj_id = ilObject::_lookupObjectId($a_ref_id);
960 $obj_type = ilObject::_lookupType($obj_id);
961
962 $members = [];
963
964 // try to get participants from (parent) course/group
965 $members_read = false;
966 switch ($obj_type) {
967 case 'crsr':
968 $members_read = true;
969 $olp = \ilObjectLP::getInstance($obj_id);
970 $members = $olp->getMembers();
971 break;
972
973 case 'crs':
974 case 'grp':
975 $members_read = true;
976 $member_obj = ilParticipants::getInstance($a_ref_id);
977 $members = $member_obj->getMembers();
978 break;
979
980 /* Mantis 19296: Individual Assessment can be subtype of crs.
981 * But for LP view only his own members should be displayed.
982 * We need to return the members without checking the parent path. */
983 case "iass":
984 $members_read = true;
985 $iass = new ilObjIndividualAssessment($obj_id, false);
986 $members = $iass->loadMembers()->membersIds();
987 break;
988
989 default:
990 // walk path to find course or group object and use members of that object
991 $path = $tree->getPathId($a_ref_id);
992 array_pop($path);
993 foreach (array_reverse($path) as $path_ref_id) {
994 $type = ilObject::_lookupType($path_ref_id, true);
995 if ($type == "crs" || $type == "grp") {
996 $members_read = true;
997 $members = self::getParticipantsForObject($path_ref_id);
998 }
999 }
1000 break;
1001 }
1002
1003 // begin-patch ouf
1004 if ($members_read) {
1005 // BT 35452: failsafe against invalid users without an entry in usr_data
1006 $members = self::filterOutUsersWithoutData($members);
1007
1008 return $GLOBALS['DIC']->access(
1009 )->filterUserIdsByRbacOrPositionOfCurrentUser(
1010 'read_learning_progress',
1011 'read_learning_progress',
1012 $a_ref_id,
1013 $members
1014 );
1015 }
1016
1017 $a_users = null;
1018
1019 // no participants possible: use tracking/object data where possible
1020 switch ($obj_type) {
1021 case "sahs":
1022 $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
1023 if ($subtype == "scorm2004") {
1024 // based on cmi_node/cp_node, used for scorm tracking data views
1025 $mod = new ilObjSCORM2004LearningModule($obj_id, false);
1026 $all = $mod->getTrackedUsers("");
1027 if ($all) {
1028 $a_users = array();
1029 foreach ($all as $item) {
1030 $a_users[] = $item["user_id"];
1031 }
1032 }
1033 } else {
1034 $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
1035 }
1036 break;
1037
1038 case "exc":
1039 $exc = new ilObjExercise($obj_id, false);
1040 $members = new ilExerciseMembers($exc);
1041 $a_users = $members->getMembers();
1042 break;
1043
1044 case "tst":
1045 $class = ilLPStatusFactory::_getClassById(
1046 $obj_id,
1047 ilLPObjSettings::LP_MODE_TEST_FINISHED
1048 );
1049 $a_users = $class::getParticipants($obj_id);
1050 break;
1051
1052 case "svy":
1053 $class = ilLPStatusFactory::_getClassById(
1054 $obj_id,
1055 ilLPObjSettings::LP_MODE_SURVEY_FINISHED
1056 );
1057 $a_users = $class::getParticipants($obj_id);
1058 break;
1059
1060 case "prg":
1061 $prg = new ilObjStudyProgramme($obj_id, false);
1062 $a_users = $prg->getIdsOfUsersWithRelevantProgress();
1063 break;
1064 default:
1065 // keep null
1066 break;
1067 }
1068
1069 if (is_null($a_users)) {
1070 return $a_users;
1071 }
1072
1073 // BT 35452: failsafe against invalid users without an entry in usr_data
1074 $a_users = self::filterOutUsersWithoutData($a_users);
1075
1076 // begin-patch ouf
1077 return $GLOBALS['DIC']->access(
1078 )->filterUserIdsByRbacOrPositionOfCurrentUser(
1079 'read_learning_progress',
1080 'read_learning_progress',
1081 $a_ref_id,
1082 $a_users
1083 );
1084 }
1085
1090 protected static function filterOutUsersWithoutData(array $user_ids): array
1091 {
1092 if (ilObjUser::userExists($user_ids)) {
1093 return $user_ids;
1094 }
1095
1096 $res = [];
1097 foreach ($user_ids as $user_id) {
1098 if (ilObjUser::userExists([$user_id])) {
1099 $res[] = $user_id;
1100 continue;
1101 }
1102 global $DIC;
1103 $DIC->logger()->trac()->info(
1104 'Excluded user with id ' . $user_id .
1105 ' from participants, because they do not have an entry in usr_data.'
1106 );
1107 }
1108 return $res;
1109 }
1110
1111 protected static function buildFilters(
1112 array $where,
1113 ?array $a_filters = null,
1114 bool $a_aggregate = false
1115 ): string {
1116 global $DIC;
1117
1118 $ilDB = $DIC->database();
1119
1120 $having = array();
1121
1122 if (is_array($a_filters) && sizeof($a_filters) > 0) {
1123 foreach ($a_filters as $id => $value) {
1124 switch ($id) {
1125 case "login":
1126 case "firstname":
1127 case "lastname":
1128 case "institution":
1129 case "department":
1130 case "street":
1131 case "email":
1132 case "matriculation":
1133 case "city":
1134 case "title":
1135 $where[] = $ilDB->like(
1136 "usr_data." . $id,
1137 "text",
1138 "%" . $value . "%"
1139 );
1140 break;
1141
1142 case "gender":
1143 case "zipcode":
1144 case "country":
1145 $where[] = "usr_data." . $id . " = " . $ilDB->quote(
1146 $value,
1147 "text"
1148 );
1149 break;
1150
1151 case "u_comment":
1152 $where[] = $ilDB->like(
1153 "ut_lp_marks." . $id,
1154 "text",
1155 "%" . $value . "%"
1156 );
1157 break;
1158
1159 case "status":
1160 if ($value == ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM) {
1161 // #10645 - not_attempted is default
1162 $where[] = "(ut_lp_marks.status = " . $ilDB->quote(
1163 ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM,
1164 "text"
1165 ) .
1166 " OR ut_lp_marks.status IS NULL)";
1167 break;
1168 }
1169 // fallthrough
1170
1171 // no break
1172 case "mark":
1173 $where[] = "ut_lp_marks." . $id . " = " . $ilDB->quote(
1174 $value,
1175 "text"
1176 );
1177 break;
1178
1179 case "percentage":
1180 if (!$a_aggregate) {
1181 if (isset($value["from"])) {
1182 $where[] = "ut_lp_marks." . $id . " >= " . $ilDB->quote(
1183 $value["from"],
1184 "integer"
1185 );
1186 }
1187 if (isset($value["to"])) {
1188 $where[] = "(ut_lp_marks." . $id . " <= " . $ilDB->quote(
1189 $value["to"],
1190 "integer"
1191 ) .
1192 " OR ut_lp_marks." . $id . " IS NULL)";
1193 }
1194 } else {
1195 if (isset($value["from"])) {
1196 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) >= " . $ilDB->quote(
1197 $value["from"],
1198 "integer"
1199 );
1200 }
1201 if (isset($value["to"])) {
1202 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) <= " . $ilDB->quote(
1203 $value["to"],
1204 "integer"
1205 );
1206 }
1207 }
1208 break;
1209
1210 case "language":
1211 $where[] = "usr_pref.value = " . $ilDB->quote(
1212 $value,
1213 "text"
1214 );
1215 break;
1216
1217 // timestamp
1218 case "last_access":
1219 if (isset($value["from"])) {
1220 $value["from"] = substr(
1221 $value["from"],
1222 0,
1223 -2
1224 ) . "00";
1225 $value["from"] = new ilDateTime(
1226 $value["from"],
1227 IL_CAL_DATETIME
1228 );
1229 $value["from"] = $value["from"]->get(IL_CAL_UNIX);
1230 }
1231 if (isset($value["to"])) {
1232 if (strlen($value["to"]) == 19) {
1233 $value["to"] = substr(
1234 $value["to"],
1235 0,
1236 -2
1237 ) . "59"; // #14858
1238 }
1239 $value["to"] = new ilDateTime(
1240 $value["to"],
1241 IL_CAL_DATETIME
1242 );
1243 $value["to"] = $value["to"]->get(IL_CAL_UNIX);
1244 }
1245 // fallthrough
1246
1247 // no break
1248 case 'status_changed':
1249 // fallthrough
1250
1251 case "registration":
1252 if ($id == "registration") {
1253 $id = "create_date";
1254 }
1255 // fallthrough
1256
1257 // no break
1258 case "create_date":
1259 case "first_access":
1260 case "birthday":
1261 if (isset($value["from"])) {
1262 $where[] = $id . " >= " . $ilDB->quote(
1263 $value["from"],
1264 "date"
1265 );
1266 }
1267 if (isset($value["to"])) {
1268 if (strlen($value["to"]) == 19) {
1269 $value["to"] = substr(
1270 $value["to"],
1271 0,
1272 -2
1273 ) . "59"; // #14858
1274 }
1275 $where[] = $id . " <= " . $ilDB->quote(
1276 $value["to"],
1277 "date"
1278 );
1279 }
1280 break;
1281
1282 case "read_count":
1283 if (!$a_aggregate) {
1284 if (isset($value["from"]) && $value["from"] > 0) {
1285 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote(
1286 $value["from"],
1287 "integer"
1288 );
1289 }
1290 if (isset($value["to"])) {
1291 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote(
1292 $value["to"],
1293 "integer"
1294 ) .
1295 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1296 }
1297 } else {
1298 if (isset($value["from"]) && $value["from"] > 0) {
1299 $having[] = "IFNULL(SUM(read_event." . $id . "+read_event.childs_" . $id . "),0) >= " . $ilDB->quote(
1300 $value["from"],
1301 "integer"
1302 );
1303 }
1304 if (isset($value["to"])) {
1305 $having[] = "IFNULL(SUM(read_event." . $id . "+read_event.childs_" . $id . "),0) <= " . $ilDB->quote(
1306 $value["to"],
1307 "integer"
1308 );
1309 }
1310 }
1311 break;
1312
1313 case "spent_seconds":
1314 if (!$a_aggregate) {
1315 if (isset($value["from"]) && $value["from"] > 0) {
1316 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote(
1317 $value["from"],
1318 "integer"
1319 );
1320 }
1321 if (isset($value["to"]) && $value["to"] > 0) {
1322 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote(
1323 $value["to"],
1324 "integer"
1325 ) .
1326 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1327 }
1328 } else {
1329 if (isset($value["from"]) && $value["from"] > 0) {
1330 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) >= " . $ilDB->quote(
1331 $value["from"],
1332 "integer"
1333 );
1334 }
1335 if (isset($value["to"]) && $value["to"] > 0) {
1336 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) <= " . $ilDB->quote(
1337 $value["to"],
1338 "integer"
1339 );
1340 }
1341 }
1342 break;
1343
1344 default:
1345 // var_dump("unknown: ".$id);
1346 break;
1347 }
1348 }
1349 }
1350
1351 $sql = "";
1352 if (sizeof($where)) {
1353 $sql .= " WHERE " . implode(" AND ", $where);
1354 }
1355 if (sizeof($having)) {
1356 // ugly "having" hack because of summary view
1357 $sql .= " [[--HAVING " . implode(" AND ", $having) . " HAVING--]]";
1358 }
1359
1360 return $sql;
1361 }
1362
1363 protected static function buildColumns(
1364 array &$a_fields,
1365 ?array $a_additional_fields = null,
1366 bool $a_aggregate = false
1367 ): array {
1368 if ($a_additional_fields === null || !count($a_additional_fields)) {
1369 return [];
1370 }
1371 $udf = [];
1372 foreach ($a_additional_fields as $field) {
1373 if (substr($field, 0, 4) != "udf_") {
1374 $function = null;
1375 if ($a_aggregate) {
1376 $pos = strrpos($field, "_");
1377 if ($pos === false) {
1378 continue;
1379 }
1380 $function = strtoupper(substr($field, $pos + 1));
1381 $field = substr($field, 0, $pos);
1382 if (!in_array(
1383 $function,
1384 array("MIN", "MAX", "SUM", "AVG", "COUNT")
1385 )) {
1386 continue;
1387 }
1388 }
1389
1390 switch ($field) {
1391 case 'org_units':
1392 break;
1393
1394 case "language":
1395 if ($function) {
1396 $a_fields[] = $function . "(value) " . $field . "_" . strtolower(
1397 $function
1398 );
1399 } else {
1400 $a_fields[] = "value as " . $field;
1401 }
1402 break;
1403
1404 case "read_count":
1405 case "spent_seconds":
1406 if (!$function) {
1407 $a_fields[] = "(" . $field . "+childs_" . $field . ") " . $field;
1408 } else {
1409 if ($function == "AVG") {
1410 $a_fields[] = "ROUND(AVG(" . $field . "+childs_" . $field . "), 2) " . $field . "_" . strtolower(
1411 $function
1412 );
1413 } else {
1414 $a_fields[] = $function . "(COALESCE(" . $field . ", 0) + COALESCE(childs_" . $field . ", 0)) " . $field . "_" . strtolower(
1415 $function
1416 );
1417 }
1418 }
1419 break;
1420
1421 case "read_count_spent_seconds":
1422 if ($function == "AVG") {
1423 $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) " . $field . "_" . strtolower(
1424 $function
1425 );
1426 }
1427 break;
1428
1429 default:
1430 if ($function) {
1431 if ($function == "AVG") {
1432 $a_fields[] = "ROUND(AVG(" . $field . "), 2) " . $field . "_" . strtolower(
1433 $function
1434 );
1435 } else {
1436 $a_fields[] = $function . "(" . $field . ") " . $field . "_" . strtolower(
1437 $function
1438 );
1439 }
1440 } else {
1441 $a_fields[] = $field;
1442 }
1443 break;
1444 }
1445 } else {
1446 $udf[] = substr($field, 4);
1447 }
1448 }
1449
1450 // clean-up
1451 $a_fields = array_unique($a_fields);
1452 if (count($udf)) {
1453 $udf = array_unique($udf);
1454 }
1455 return $udf;
1456 }
1457
1467 public static function getObjectIds(
1468 int $a_parent_obj_id,
1469 int $a_parent_ref_id,
1470 bool $use_collection = true,
1471 bool $a_refresh_status = true,
1472 ?array $a_user_ids = null
1473 ): array {
1474 $object_ids = array($a_parent_obj_id);
1475 $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1476 $objectives_parent_id = $scorm = $subitems = false;
1477
1478 $olp = ilObjectLP::getInstance($a_parent_obj_id);
1479 $mode = $olp->getCurrentMode();
1480 switch ($mode) {
1481 // what about LP_MODE_SCORM_PACKAGE ?
1482 case ilLPObjSettings::LP_MODE_SCORM:
1483 $status_scorm = get_class(
1484 ilLPStatusFactory::_getInstance(
1485 $a_parent_obj_id,
1486 ilLPObjSettings::LP_MODE_SCORM
1487 )
1488 );
1489 $scorm = $status_scorm::_getStatusInfo($a_parent_obj_id);
1490 break;
1491
1492 case ilLPObjSettings::LP_MODE_OBJECTIVES:
1493 if (ilObject::_lookupType($a_parent_obj_id) == "crs") {
1494 $objectives_parent_id = $a_parent_obj_id;
1495 }
1496 break;
1497
1498 case ilLPObjSettings::LP_MODE_COLLECTION_MANUAL:
1499 case ilLPObjSettings::LP_MODE_COLLECTION_TLT:
1500 case ilLPObjSettings::LP_MODE_COLLECTION_MOBS:
1501 $status_coll_tlt = get_class(
1502 ilLPStatusFactory::_getInstance($a_parent_obj_id, $mode)
1503 );
1504 $subitems = $status_coll_tlt::_getStatusInfo($a_parent_obj_id);
1505 break;
1506
1507 default:
1508 // lp collection
1509 if ($use_collection) {
1510 $collection = $olp->getCollectionInstance();
1511 if ($collection) {
1512 foreach ($collection->getItems() as $child_ref_id) {
1513 $child_id = ilObject::_lookupObjId($child_ref_id);
1514 $object_ids[] = $child_id;
1515 $ref_ids[$child_id] = $child_ref_id;
1516 }
1517 }
1518 } // all objects in branch
1519 else {
1520 self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1521 $object_ids = array_unique($object_ids);
1522 }
1523
1524 foreach ($object_ids as $idx => $object_id) {
1525 if (!$object_id) {
1526 unset($object_ids[$idx]);
1527 }
1528 }
1529 break;
1530 }
1531
1532 if ($a_refresh_status) {
1533 self::refreshObjectsStatus($object_ids, $a_user_ids);
1534 }
1535
1536 return array("object_ids" => $object_ids,
1537 "ref_ids" => $ref_ids,
1538 "objectives_parent_id" => $objectives_parent_id,
1539 "scorm" => $scorm,
1540 "subitems" => $subitems
1541 );
1542 }
1543
1547 protected static function getSubTree(
1548 int $a_parent_ref_id,
1549 array &$a_object_ids,
1550 array &$a_ref_ids
1551 ): void {
1552 global $DIC;
1553
1554 $tree = $DIC['tree'];
1555
1556 $children = $tree->getChilds($a_parent_ref_id);
1557 if ($children) {
1558 foreach ($children as $child) {
1559 if ($child["type"] == "adm" || $child["type"] == "rolf") {
1560 continue;
1561 }
1562
1563 // as there can be deactivated items in the collection
1564 // we should allow them here too
1565
1566 $olp = ilObjectLP::getInstance($child["obj_id"]);
1567 $cmode = $olp->getCurrentMode();
1568
1569 if ($cmode != ilLPObjSettings::LP_MODE_UNDEFINED) {
1570 $a_object_ids[] = $child["obj_id"];
1571 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1572 }
1573
1574 self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1575 }
1576 }
1577 }
1578
1588 public static function executeQueries(
1589 array $queries,
1590 string $a_order_field = "",
1591 string $a_order_dir = "",
1592 int $a_offset = 0,
1593 int $a_limit = 9999
1594 ): array {
1595 global $DIC;
1596
1597 $ilDB = $DIC->database();
1598 $cnt = 0;
1599 $subqueries = array();
1600 foreach ($queries as $item) {
1601 // ugly "having" hack because of summary view
1602 $item['query'] = str_replace("[[--HAVING", "HAVING", $item['query']);
1603 $item['query'] = str_replace("HAVING--]]", "", $item['query']);
1604
1605 if (!isset($item["count"])) {
1606 $count_field = $item["fields"];
1607 $count_field = array_shift($count_field);
1608 } else {
1609 $count_field = $item["count"];
1610 }
1611 $count_query = "SELECT COUNT(" . $count_field . ") AS cnt" . $item["query"];
1612 $set = $ilDB->query($count_query);
1613 if ($rec = $ilDB->fetchAssoc($set)) {
1614 $cnt += $rec["cnt"];
1615 }
1616
1617 $subqueries[] = "SELECT " . implode(
1618 ",",
1619 $item["fields"]
1620 ) . $item["query"];
1621 }
1622
1623 // set query
1624 $result = array();
1625 if ($cnt > 0) {
1626 if (sizeof($subqueries) > 1) {
1627 $base = array_shift($subqueries);
1628 $query = $base . " UNION (" . implode(
1629 ") UNION (",
1630 $subqueries
1631 ) . ")";
1632 } else {
1633 $query = $subqueries[0];
1634 }
1635
1636 if ($a_order_dir != "asc" && $a_order_dir != "desc") {
1637 $a_order_dir = "asc";
1638 }
1639 if ($a_order_field) {
1640 $query .= " ORDER BY " . $a_order_field . " " . strtoupper(
1641 $a_order_dir
1642 );
1643 }
1644
1645 $offset = $a_offset;
1646 $limit = $a_limit;
1647 $ilDB->setLimit($limit, $offset);
1648 $set = $ilDB->query($query);
1649 while ($rec = $ilDB->fetchAssoc($set)) {
1650 $result[] = $rec;
1651 }
1652 }
1653
1654 return array("cnt" => $cnt, "set" => $result);
1655 }
1656
1667 public static function getUserObjectMatrix(
1668 int $a_parent_ref_id,
1669 array $a_obj_ids,
1670 ?string $a_user_filter = null,
1671 ?array $a_additional_fields = null,
1672 ?array $a_privacy_fields = null,
1673 ?int $a_check_agreement = null
1674 ): array {
1675 global $DIC;
1676 $ilDB = $DIC->database();
1677
1678 $result = array("cnt" => 0, "set" => null);
1679 if (sizeof($a_obj_ids)) {
1680 $where = array();
1681 $where[] = "usr_data.usr_id <> " . $ilDB->quote(
1682 ANONYMOUS_USER_ID,
1683 "integer"
1684 );
1685 if ($a_user_filter) {
1686 $where[] = $ilDB->like(
1687 "usr_data.login",
1688 "text",
1689 "%" . $a_user_filter . "%"
1690 );
1691 }
1692
1693 // users
1694 $left = "";
1695 $a_users = self::getParticipantsForObject($a_parent_ref_id);
1696 if (is_array($a_users)) {
1697 $left = "LEFT";
1698 $where[] = $ilDB->in(
1699 "usr_data.usr_id",
1700 $a_users,
1701 false,
1702 "integer"
1703 );
1704 }
1705
1706 $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1707 self::refreshObjectsStatus($a_obj_ids, $a_users);
1708
1709 $fields = array("usr_data.usr_id", "login", "active");
1710 $udf = self::buildColumns($fields, $a_additional_fields);
1711
1712 // #18673 - if parent supports percentage does not matter for "sub-items"
1713 $fields[] = "percentage";
1714
1715 $raw = array();
1716 foreach ($a_obj_ids as $obj_id) {
1717 // one request for each object
1718 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
1719 " AND read_event.obj_id = " . $ilDB->quote(
1720 $obj_id,
1721 "integer"
1722 ) . ")" .
1723 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
1724 " AND ut_lp_marks.obj_id = " . $ilDB->quote(
1725 $obj_id,
1726 "integer"
1727 ) . ")" .
1728 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote(
1729 "language",
1730 "text"
1731 ) . ")" .
1732 self::buildFilters($where);
1733
1734 $raw = self::executeQueries(
1735 array(array("fields" => $fields, "query" => $query)),
1736 "login"
1737 );
1738 if ($raw["cnt"]) {
1739 // convert to final structure
1740 foreach ($raw["set"] as $row) {
1741 $result["set"][(int) $row["usr_id"]]["login"] = ($row["login"] ?? '');
1742 $result["set"][(int) $row["usr_id"]]["usr_id"] = (int) ($row["usr_id"] ?? 0);
1743
1744 // #14953
1745 $result["set"][(int) $row["usr_id"]]["obj_" . $obj_id] = (int) ($row["status"] ?? 0);
1746 $result["set"][(int) $row["usr_id"]]["obj_" . $obj_id . "_perc"] = (int) ($row["percentage"] ?? 0);
1747 if ($obj_id == $parent_obj_id) {
1748 $result["set"][(int) $row["usr_id"]]["status_changed"] = (int) ($row["status_changed"] ?? 0);
1749 $result["set"][(int) $row["usr_id"]]["last_access"] = (int) ($row["last_access"] ?? 0);
1750 $result["set"][(int) $row["usr_id"]]["spent_seconds"] = (int) ($row["spent_seconds"] ?? 0);
1751 $result["set"][(int) $row["usr_id"]]["read_count"] = (int) ($row["read_count"] ?? 0);
1752 }
1753
1754 // @todo int cast?
1755 foreach ($fields as $field) {
1756 // #14957 - value [as] language
1757 if (stristr($field, "language")) {
1758 $field = "language";
1759 }
1760
1761 if (isset($row[$field])) {
1762 // #14955
1763 if ($obj_id == $parent_obj_id ||
1764 !in_array(
1765 $field,
1766 array("mark", "u_comment")
1767 )) {
1768 $result["set"][(int) $row["usr_id"]][$field] = $row[$field];
1769 }
1770 }
1771 }
1772 }
1773 }
1774 }
1775
1776 $result["cnt"] = 0;
1777 if (is_array($result["set"])) {
1778 $result["cnt"] = count($result["set"]);
1779 }
1780 $result["users"] = $a_users;
1781
1782 self::getUDFAndHandlePrivacy(
1783 $result,
1784 $udf,
1785 $a_check_agreement,
1786 $a_privacy_fields,
1787 $a_additional_fields
1788 );
1789 }
1790 return $result;
1791 }
1792
1793 public static function getUserObjectiveMatrix(
1794 int $a_parent_obj_id,
1795 array $a_users
1796 ): array {
1797 global $DIC;
1798
1799 $ilDB = $DIC->database();
1800
1801 if ($a_parent_obj_id && $a_users) {
1802 $res = array();
1803
1804 $objective_ids = ilCourseObjective::_getObjectiveIds(
1805 $a_parent_obj_id,
1806 true
1807 );
1808
1809 // #17402 - are initital test(s) qualifying?
1810 $lo_set = ilLOSettings::getInstanceByObjId($a_parent_obj_id);
1811 $initial_qualifying = $lo_set->isInitialTestQualifying();
1812
1813 // there may be missing entries for any user / objective combination
1814 foreach ($objective_ids as $objective_id) {
1815 foreach ($a_users as $user_id) {
1816 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
1817 }
1818 }
1819
1820 $query = "SELECT * FROM loc_user_results" .
1821 " WHERE " . $ilDB->in(
1822 "objective_id",
1823 $objective_ids,
1824 false,
1825 "integer"
1826 ) .
1827 " AND " . $ilDB->in("user_id", $a_users, false, "integer");
1828 if (!$initial_qualifying) {
1829 $query .= " AND type = " . $ilDB->quote(
1830 ilLOUserResults::TYPE_QUALIFIED,
1831 "integer"
1832 );
1833 }
1834 $query .= " ORDER BY type"; // qualified must come last!
1835 $set = $ilDB->query($query);
1836 while ($row = $ilDB->fetchAssoc($set)) {
1837 $objective_id = (int) $row["objective_id"];
1838 $user_id = (int) $row["user_id"];
1839
1840 // if both initial and qualified, qualified will overwrite initial
1841
1842 // #15873 - see ilLOUserResults::getObjectiveStatusForLP()
1843 if ($row["status"] == ilLOUserResults::STATUS_COMPLETED) {
1844 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
1845 } elseif ($row["status"] == ilLOUserResults::STATUS_FAILED) {
1846 $res[$user_id][$objective_id] = (int) $row["is_final"]
1847 ? ilLPStatus::LP_STATUS_FAILED_NUM
1848 : ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
1849 }
1850 }
1851
1852 return $res;
1853 }
1854 return [];
1855 }
1856
1857 public static function getObjectAccessStatistics(
1858 array $a_ref_ids,
1859 string $a_year,
1860 ?string $a_month = null
1861 ): array {
1862 global $DIC;
1863
1864 $ilDB = $DIC['ilDB'];
1865
1866 $obj_ids = array_keys($a_ref_ids);
1867
1868 if ($a_month) {
1869 $column = "dd";
1870 } else {
1871 $column = "mm";
1872 }
1873
1874 $res = array();
1875 $sql = "SELECT obj_id," . $column . ",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
1876 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
1877 " FROM obj_stat" .
1878 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1879 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1880 if ($a_month) {
1881 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1882 }
1883 $sql .= " GROUP BY obj_id," . $column;
1884 $set = $ilDB->query($sql);
1885 while ($row = $ilDB->fetchAssoc($set)) {
1886 $row["read_count"] += (int) $row["childs_read_count"];
1887 $row["spent_seconds"] += (int) $row["childs_spent_seconds"];
1888 $res[$row["obj_id"]][$row[$column]]["read_count"] =
1889 ($res[$row["obj_id"]][$row[$column]]["read_count"] ?? 0) + $row["read_count"];
1890 $res[$row["obj_id"]][$row[$column]]["spent_seconds"] =
1891 ($res[$row["obj_id"]][$row[$column]]["spent_seconds"] ?? 0) + $row["spent_seconds"];
1892 }
1893
1894 // add user data
1895
1896 $sql = "SELECT obj_id," . $column . ",SUM(counter) counter" .
1897 " FROM obj_user_stat" .
1898 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1899 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1900 if ($a_month) {
1901 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1902 }
1903 $sql .= " GROUP BY obj_id," . $column;
1904 $set = $ilDB->query($sql);
1905 while ($row = $ilDB->fetchAssoc($set)) {
1906 if (!isset($res[(int) $row["obj_id"]][$row[$column]]["users"])) {
1907 $res[(int) $row["obj_id"]][$row[$column]]["users"] = 0;
1908 }
1909 $res[(int) $row["obj_id"]][$row[$column]]["users"] += (int) $row["counter"];
1910 }
1911
1912 return $res;
1913 }
1914
1915 public static function getObjectTypeStatistics(): array
1916 {
1917 global $DIC;
1918
1919 $ilDB = $DIC['ilDB'];
1920 $objDefinition = $DIC['objDefinition'];
1921
1922 // re-use add new item selection (folder is not that important)
1923 $types = array_keys(
1924 $objDefinition->getCreatableSubObjects(
1925 "root",
1926 ilObjectDefinition::MODE_REPOSITORY
1927 )
1928 );
1929
1930 // repository
1931 $tree = new ilTree(1);
1932 $sql = "SELECT " . $tree->getObjectDataTable(
1933 ) . ".obj_id," . $tree->getObjectDataTable() . ".type," .
1934 $tree->getTreeTable() . "." . $tree->getTreePk(
1935 ) . "," . $tree->getTableReference() . ".ref_id" .
1936 " FROM " . $tree->getTreeTable() .
1937 " " . $tree->buildJoin() .
1938 " WHERE " . $ilDB->in(
1939 $tree->getObjectDataTable() . ".type",
1940 $types,
1941 "",
1942 "text"
1943 );
1944 $set = $ilDB->query($sql);
1945 $res = array();
1946 while ($row = $ilDB->fetchAssoc($set)) {
1947 $res[$row["type"]]["type"] = (string) $row["type"];
1948 $res[$row["type"]]["references"] = ($res[$row["type"]]["references"] ?? 0) + 1;
1949 $res[$row["type"]]["objects"][] = (int) $row["obj_id"];
1950 if ($row[$tree->getTreePk()] < 0) {
1951 $res[$row["type"]]["deleted"] = ($res[$row["type"]]["deleted"] ?? 0) + 1;
1952 } else {
1953 $res[$row["type"]]["deleted"] = ($res[$row["type"]]["deleted"] ?? 0);
1954 }
1955 }
1956
1957 foreach ($res as $type => $values) {
1958 $res[$type]["objects"] = count((array_unique($values["objects"] ?? [])));
1959 }
1960
1961 // portfolios (not part of repository)
1962 foreach (self::getPortfolios() as $obj_id) {
1963 $res["prtf"]["type"] = "prtf";
1964 $res["prtf"]["references"] = ($res["prtf"]["references"] ?? 0) + 1;
1965 $res["prtf"]["objects"] = ($res["prtf"]["objects"] ?? 0) + 1;
1966 }
1967
1968 foreach (self::getWorkspaceBlogs() as $obj_id) {
1969 $res["blog"]["type"] = "blog";
1970 $res["blog"]["references"] = ($res["blog"]["references"] ?? 0) + 1;
1971 $res["blog"]["objects"] = ($res["blog"]["objects"] ?? 0) + 1;
1972 }
1973 return $res;
1974 }
1975
1976 public static function getWorkspaceBlogs(?string $a_title = null): array
1977 {
1978 global $DIC;
1979
1980 $ilDB = $DIC->database();
1981
1982 $res = array();
1983
1984 // blogs in workspace?
1985 $sql = "SELECT od.obj_id,oref.wsp_id,od.type" .
1986 " FROM tree_workspace wst" .
1987 " JOIN object_reference_ws oref ON (oref.wsp_id = wst.child)" .
1988 " JOIN object_data od ON (oref.obj_id = od.obj_id)" .
1989 " WHERE od.type = " . $ilDB->quote("blog", "text");
1990
1991 if ($a_title) {
1992 $sql .= " AND " . $ilDB->like(
1993 "od.title",
1994 "text",
1995 "%" . $a_title . "%"
1996 );
1997 }
1998
1999 $set = $ilDB->query($sql);
2000 while ($row = $ilDB->fetchAssoc($set)) {
2001 $res[] = (int) $row["obj_id"];
2002 }
2003 return $res;
2004 }
2005
2006 public static function getPortfolios(?string $a_title = null): array
2007 {
2008 global $DIC;
2009
2010 $ilDB = $DIC['ilDB'];
2011
2012 $res = array();
2013
2014 $sql = "SELECT od.obj_id" .
2015 " FROM usr_portfolio prtf" .
2016 " JOIN object_data od ON (od.obj_id = prtf.id)";
2017
2018 if ($a_title) {
2019 $sql .= " WHERE " . $ilDB->like(
2020 "od.title",
2021 "text",
2022 "%" . $a_title . "%"
2023 );
2024 }
2025
2026 $set = $ilDB->query($sql);
2027 while ($row = $ilDB->fetchAssoc($set)) {
2028 $res[] = (int) $row["obj_id"];
2029 }
2030
2031 return $res;
2032 }
2033
2034 public static function getObjectDailyStatistics(
2035 array $a_ref_ids,
2036 string $a_year,
2037 ?string $a_month = null
2038 ): array {
2039 global $DIC;
2040
2041 $ilDB = $DIC->database();
2042 $obj_ids = array_keys($a_ref_ids);
2043
2044 $res = array();
2045 $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
2046 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
2047 " FROM obj_stat" .
2048 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer") .
2049 " AND yyyy = " . $ilDB->quote($a_year, "integer");
2050 if ($a_month) {
2051 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
2052 }
2053 $sql .= " GROUP BY obj_id,hh";
2054 $set = $ilDB->query($sql);
2055 while ($row = $ilDB->fetchAssoc($set)) {
2056 $row["read_count"] += (int) $row["childs_read_count"];
2057 $row["spent_seconds"] += (int) $row["childs_spent_seconds"];
2058 $res[$row["obj_id"]][(int) $row["hh"]]["read_count"] =
2059 ($res[$row["obj_id"]][(int) $row["hh"]]["read_count"] ?? 0) + $row["read_count"];
2060 $res[$row["obj_id"]][(int) $row["hh"]]["spent_seconds"] =
2061 ($res[$row["obj_id"]][(int) $row["hh"]]["spent_seconds"] ?? 0) + $row["spent_seconds"];
2062 }
2063 return $res;
2064 }
2065
2066 public static function getObjectStatisticsMonthlySummary(): array
2067 {
2068 global $DIC;
2069
2070 $ilDB = $DIC['ilDB'];
2071
2072 $set = $ilDB->query(
2073 "SELECT COUNT(*) AS COUNTER,yyyy,mm" .
2074 " FROM obj_stat" .
2075 " GROUP BY yyyy, mm" .
2076 " ORDER BY yyyy DESC, mm DESC"
2077 );
2078 $res = array();
2079 while ($row = $ilDB->fetchAssoc($set)) {
2080 $res[] = array("month" => $row["yyyy"] . "-" . $row["mm"],
2081 "count" => (int) ($row["COUNTER"] ?? 0)
2082 );
2083 }
2084 return $res;
2085 }
2086
2087 public static function deleteObjectStatistics(array $a_months): void
2088 {
2089 global $DIC;
2090
2091 $ilDB = $DIC->database();
2092
2093 // no combined column, have to concat
2094 $date_compare = $ilDB->in(
2095 $ilDB->concat(
2096 array(array("yyyy", ""),
2097 array($ilDB->quote("-", "text"), ""),
2098 array("mm", "")
2099 )
2100 ),
2101 $a_months,
2102 false,
2103 "text"
2104 );
2105 $sql = "DELETE FROM obj_stat" .
2106 " WHERE " . $date_compare;
2107 $ilDB->manipulate($sql);
2108
2109 // fulldate == YYYYMMDD
2110 $tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
2111 foreach ($a_months as $month) {
2112 $year = substr($month, 0, 4);
2113 $month = substr($month, 5);
2114 $from = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "01";
2115 $to = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "31";
2116
2117 foreach ($tables as $table) {
2118 $sql = "DELETE FROM " . $table .
2119 " WHERE fulldate >= " . $ilDB->quote($from, "integer") .
2120 " AND fulldate <= " . $ilDB->quote($to, "integer");
2121 $ilDB->manipulate($sql);
2122 }
2123 }
2124 }
2125
2126 public static function searchObjects(
2127 string $a_type,
2128 ?string $a_title = null,
2129 ?int $a_root = null,
2130 ?array $a_hidden = null,
2131 ?array $a_preset_obj_ids = null
2132 ): array {
2133 global $DIC;
2134
2135 $ilDB = $DIC->database();
2136 $tree = $DIC->repositoryTree();
2137
2138 if ($a_type == "lres") {
2139 $a_type = array('lm', 'sahs', 'htlm');
2140 }
2141
2142 $sql = "SELECT r.ref_id,r.obj_id" .
2143 " FROM object_data o" .
2144 " JOIN object_reference r ON (o.obj_id = r.obj_id)" .
2145 " JOIN tree t ON (t.child = r.ref_id)" .
2146 " WHERE t.tree = " . $ilDB->quote(1, "integer");
2147
2148 if (!is_array($a_type)) {
2149 $sql .= " AND o.type = " . $ilDB->quote($a_type, "text");
2150 } else {
2151 $sql .= " AND " . $ilDB->in("o.type", $a_type, false, "text");
2152 }
2153
2154 if ($a_title) {
2155 $sql .= " AND (" . $ilDB->like(
2156 "o.title",
2157 "text",
2158 "%" . $a_title . "%"
2159 ) .
2160 " OR " . $ilDB->like(
2161 "o.description",
2162 "text",
2163 "%" . $a_title . "%"
2164 ) . ")";
2165 }
2166
2167 if (is_array($a_hidden)) {
2168 $sql .= " AND " . $ilDB->in("o.obj_id", $a_hidden, true, "integer");
2169 }
2170
2171 if (is_array($a_preset_obj_ids)) {
2172 $sql .= " AND " . $ilDB->in(
2173 "o.obj_id",
2174 $a_preset_obj_ids,
2175 false,
2176 "integer"
2177 );
2178 }
2179
2180 $set = $ilDB->query($sql);
2181 $res = array();
2182 while ($row = $ilDB->fetchAssoc($set)) {
2183 if ($a_root && $a_root != ROOT_FOLDER_ID) {
2184 foreach (ilObject::_getAllReferences(
2185 $row['obj_id']
2186 ) as $ref_id) {
2187 if ($tree->isGrandChild($a_root, $ref_id)) {
2188 $res[$row["obj_id"]][] = (int) $row["ref_id"];
2189 }
2190 }
2191 } else {
2192 $res[$row["obj_id"]][] = (int) $row["ref_id"];
2193 }
2194 }
2195 return $res;
2196 }
2197
2201 protected static function refreshObjectsStatus(
2202 array $a_obj_ids,
2203 ?array $a_users = null
2204 ): void {
2205 foreach ($a_obj_ids as $obj_id) {
2206 ilLPStatus::checkStatusForObject($obj_id, $a_users);
2207 }
2208 }
2209
2213 public static function getObjectStatisticsLogInfo(): array
2214 {
2215 global $DIC;
2216
2217 $ilDB = $DIC->database();
2218 $set = $ilDB->query(
2219 "SELECT COUNT(*) counter, MIN(tstamp) tstamp" .
2220 " FROM obj_stat_log"
2221 );
2222 return $ilDB->fetchAssoc($set);
2223 }
2224
2225 public static function getObjectLPStatistics(
2226 array $a_obj_ids,
2227 int $a_year,
2228 ?int $a_month = null,
2229 bool $a_group_by_day = false
2230 ): array {
2231 global $DIC;
2232
2233 $ilDB = $DIC->database();
2234 if ($a_group_by_day) {
2235 $column = "dd";
2236 } else {
2237 $column = "mm,yyyy";
2238 }
2239
2240 $res = array();
2241 $sql = "SELECT obj_id," . $column . "," .
2242 "MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max," .
2243 "MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max," .
2244 "MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max," .
2245 "MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max," .
2246 "MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max" .
2247 " FROM obj_lp_stat" .
2248 " WHERE " . $ilDB->in("obj_id", $a_obj_ids, false, "integer") .
2249 " AND yyyy = " . $ilDB->quote($a_year, "integer");
2250 if ($a_month) {
2251 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
2252 }
2253 $sql .= " GROUP BY obj_id," . $column;
2254 $set = $ilDB->query($sql);
2255 while ($row = $ilDB->fetchAssoc($set)) {
2256 $row['obj_id'] = (int) $row['obj_id'];
2257 $res[] = $row;
2258 }
2259
2260 return $res;
2261 }
2262
2263 public static function getObjectTypeStatisticsPerMonth(
2264 string $a_aggregation,
2265 ?string $a_year = null
2266 ): array {
2267 global $DIC;
2268
2269 $ilDB = $DIC['ilDB'];
2270
2271 if (!$a_year) {
2272 $a_year = date("Y");
2273 }
2274
2275 $agg = strtoupper($a_aggregation);
2276
2277 $res = array();
2278 $sql = "SELECT type,yyyy,mm," . $agg . "(cnt_objects) cnt_objects," . $agg . "(cnt_references) cnt_references," .
2279 "" . $agg . "(cnt_deleted) cnt_deleted FROM obj_type_stat" .
2280 " WHERE yyyy = " . $ilDB->quote($a_year, "integer") .
2281 " GROUP BY type,yyyy,mm";
2282 $set = $ilDB->query($sql);
2283 while ($row = $ilDB->fetchAssoc($set)) {
2284 $row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
2285 $res[$row["type"]][$row["yyyy"] . "-" . $row["mm"]] = array(
2286 "objects" => (int) $row["cnt_objects"],
2287 "references" => (int) $row["cnt_references"],
2288 "deleted" => (int) $row["cnt_deleted"]
2289 );
2290 }
2291
2292 return $res;
2293 }
2294
2295 public static function getObjectTypeStatisticsMinYear()
2296 {
2297 global $DIC;
2298
2299 $db = $DIC->database();
2300 $query = 'select min(yyyy) min from obj_type_stat';
2301 $res = $db->query($query);
2302 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
2303 return $row->min;
2304 }
2305 return date('Y');
2306 }
2307}
$id
$id
plugin.php for ilComponentBuildPluginInfoObjectiveTest::testAddPlugins
Definition: plugin.php:23
IL_CAL_UNIX
const IL_CAL_UNIX
Definition: class.ilDateTime.php:23
IL_CAL_DATETIME
const IL_CAL_DATETIME
Definition: class.ilDateTime.php:21
ilArrayUtil\stableSortArray
static stableSortArray(array $array, string $a_array_sortby, string $a_array_sortorder="asc", bool $a_numeric=false)
Sort an aray using a stable sort algorithm, which preveserves the sequence of array elements which ha...
Definition: class.ilArrayUtil.php:230
ilContainerReference\_lookupTargetTitle
static _lookupTargetTitle(int $a_obj_id)
Definition: class.ilContainerReference.php:93
ilCourseObjective\_getObjectiveIds
static _getObjectiveIds(int $course_id, bool $a_activated_only=false)
Definition: class.ilCourseObjective.php:437
ilDatePresentation\formatPeriod
static formatPeriod(ilDateTime $start, ilDateTime $end, bool $a_skip_starting_day=false, ?ilObjUser $user=null)
Format a period of two dates Shows: 14.
Definition: class.ilDatePresentation.php:174
ilDateTime
@classDescription Date and time handling
Definition: class.ilDateTime.php:43
ilExerciseMembers
Class ilExerciseMembers.
Definition: class.ilExerciseMembers.php:27
ilLOSettings\getInstanceByObjId
static getInstanceByObjId(int $a_obj_id)
Definition: class.ilLOSettings.php:88
ilLOUserResults\getObjectiveStatusForLP
static getObjectiveStatusForLP(int $a_user_id, int $a_obj_id, array $a_objective_ids)
Definition: class.ilLOUserResults.php:337
ilLPStatusFactory\_getInstance
static _getInstance(int $a_obj_id, ?int $a_mode=null)
Definition: class.ilLPStatusFactory.php:110
ilLPStatusFactory\_getClassById
static _getClassById(int $a_obj_id, ?int $a_mode=null)
Definition: class.ilLPStatusFactory.php:55
ilLPStatusWrapper\_getStatusInfo
static _getStatusInfo(int $a_obj_id)
Reads informations about the object e.g test results, tlt, number of visits.
Definition: class.ilLPStatusWrapper.php:137
ilLPStatus\LP_STATUS_COMPLETED_NUM
const LP_STATUS_COMPLETED_NUM
Definition: class.ilLPStatus.php:44
ilLPStatus\LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_IN_PROGRESS_NUM
Definition: class.ilLPStatus.php:43
ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM
const LP_STATUS_NOT_ATTEMPTED_NUM
Definition: class.ilLPStatus.php:42
ilLPStatus\checkStatusForObject
static checkStatusForObject(int $a_obj_id, ?array $a_users=null)
This function checks whether the status for a given number of users is dirty and must be recalculated...
Definition: class.ilLPStatus.php:287
ilLPStatus\LP_STATUS_FAILED_NUM
const LP_STATUS_FAILED_NUM
Definition: class.ilLPStatus.php:45
ilMemberAgreement\lookupAcceptedAgreements
static lookupAcceptedAgreements(int $a_obj_id)
Lookup users who have accepted the agreement.
Definition: class.ilMemberAgreement.php:128
ilObjExercise
Class ilObjExercise.
Definition: class.ilObjExercise.php:35
ilObjIndividualAssessment
For the purpose of streamlining the grading and learning-process status definition outside of tests,...
Definition: class.ilObjIndividualAssessment.php:30
ilObjSAHSLearningModule\_lookupSubType
static _lookupSubType(int $a_obj_id)
lookup subtype id (scorm, )
Definition: class.ilObjSAHSLearningModule.php:202
ilObjSCORM2004LearningModule
Class ilObjSCORM2004LearningModule.
Definition: class.ilObjSCORM2004LearningModule.php:28
ilObjSCORMLearningModule
Class ilObjSCORMLearningModule.
Definition: class.ilObjSCORMLearningModule.php:30
ilObjSCORMTracking\_getTrackedUsers
static _getTrackedUsers(int $a_obj_id)
Get all tracked users.
Definition: class.ilObjSCORMTracking.php:395
ilObjUser\userExists
static userExists(array $a_usr_ids=[])
Definition: class.ilObjUser.php:2347
ilObjectLP\getInstance
static getInstance(int $obj_id)
Definition: class.ilObjectLP.php:57
ilObject\_lookupObjectId
static _lookupObjectId(int $ref_id)
Definition: class.ilObject.php:1133
ilObject\_lookupType
static _lookupType(int $id, bool $reference=false)
Definition: class.ilObject.php:1101
ilObject\_getAllReferences
static _getAllReferences(int $id)
get all reference ids for object ID
Definition: class.ilObject.php:826
ilObject\_lookupObjId
static _lookupObjId(int $ref_id)
Definition: class.ilObject.php:940
ilParticipants\getInstance
static getInstance(int $a_ref_id)
Definition: class.ilParticipants.php:94
ilTrQuery\getObjectLPStatistics
static getObjectLPStatistics(array $a_obj_ids, int $a_year, ?int $a_month=null, bool $a_group_by_day=false)
Definition: class.ilTrQuery.php:2225
ilTrQuery\filterOutUsersWithoutData
static filterOutUsersWithoutData(array $user_ids)
Definition: class.ilTrQuery.php:1090
ilTrQuery\getUserDataForObject
static getUserDataForObject(int $a_ref_id, string $a_order_field="", string $a_order_dir="", int $a_offset=0, int $a_limit=9999, ?array $a_filters=null, ?array $a_additional_fields=null, ?int $check_agreement=null, ?array $privacy_fields=null)
Definition: class.ilTrQuery.php:265
ilTrQuery\getSCOsStatusForUser
static getSCOsStatusForUser(int $a_user_id, int $a_parent_obj_id, array $a_sco_ids)
Definition: class.ilTrQuery.php:148
ilTrQuery\getParticipantsForObject
static getParticipantsForObject(int $a_ref_id)
Get participant ids for given object.
Definition: class.ilTrQuery.php:953
ilTrQuery\getUserObjectiveMatrix
static getUserObjectiveMatrix(int $a_parent_obj_id, array $a_users)
Definition: class.ilTrQuery.php:1793
ilTrQuery\getObjectAccessStatistics
static getObjectAccessStatistics(array $a_ref_ids, string $a_year, ?string $a_month=null)
Definition: class.ilTrQuery.php:1857
ilTrQuery\getSessionData
static getSessionData(int $a_user_id, array $obj_ids)
Get session data for given objects and user.
Definition: class.ilTrQuery.php:698
ilTrQuery\getObjectsSummaryForObject
static getObjectsSummaryForObject(int $a_parent_obj_id, int $a_parent_ref_id, string $a_order_field="", string $a_order_dir="", int $a_offset=0, int $a_limit=9999, ?array $a_filters=null, ?array $a_additional_fields=null, ?array $a_preselected_obj_ids=null)
Get all aggregated tracking data for parent object :TODO: sorting, offset, limit, objectives,...
Definition: class.ilTrQuery.php:743
ilTrQuery\buildColumns
static buildColumns(array &$a_fields, ?array $a_additional_fields=null, bool $a_aggregate=false)
Definition: class.ilTrQuery.php:1363
ilTrQuery\getSummaryDataForObject
static getSummaryDataForObject(int $a_ref_id, array $fields, ?array $a_filters=null)
Definition: class.ilTrQuery.php:819
ilTrQuery\getObjectTypeStatistics
static getObjectTypeStatistics()
Definition: class.ilTrQuery.php:1915
ilTrQuery\getObjectTypeStatisticsPerMonth
static getObjectTypeStatisticsPerMonth(string $a_aggregation, ?string $a_year=null)
Definition: class.ilTrQuery.php:2263
ilTrQuery\getObjectStatisticsMonthlySummary
static getObjectStatisticsMonthlySummary()
Definition: class.ilTrQuery.php:2066
ilTrQuery\getUDFAndHandlePrivacy
static getUDFAndHandlePrivacy(array &$a_result, ?array $a_udf=null, ?int $a_check_agreement=null, ?array $a_privacy_fields=null, ?array $a_filters=null)
Handle privacy and add udf data to (user) result data.
Definition: class.ilTrQuery.php:364
ilTrQuery\buildFilters
static buildFilters(array $where, ?array $a_filters=null, bool $a_aggregate=false)
Definition: class.ilTrQuery.php:1111
ilTrQuery\getObjectTypeStatisticsMinYear
static getObjectTypeStatisticsMinYear()
Definition: class.ilTrQuery.php:2295
ilTrQuery\getWorkspaceBlogs
static getWorkspaceBlogs(?string $a_title=null)
Definition: class.ilTrQuery.php:1976
ilTrQuery\getObjectivesStatusForUser
static getObjectivesStatusForUser(int $a_user_id, int $a_obj_id, array $a_objective_ids)
Definition: class.ilTrQuery.php:104
ilTrQuery\getPortfolios
static getPortfolios(?string $a_title=null)
Definition: class.ilTrQuery.php:2006
ilTrQuery\searchObjects
static searchObjects(string $a_type, ?string $a_title=null, ?int $a_root=null, ?array $a_hidden=null, ?array $a_preset_obj_ids=null)
Definition: class.ilTrQuery.php:2126
ilTrQuery\getObjectsDataForUser
static getObjectsDataForUser(int $a_user_id, int $a_parent_obj_id, int $a_parent_ref_id, string $a_order_field="", string $a_order_dir="", int $a_offset=0, int $a_limit=9999, ?array $a_filters=null, ?array $a_additional_fields=null, bool $use_collection=true)
Get all object-based tracking data for user and parent object.
Definition: class.ilTrQuery.php:478
ilTrQuery\getSubTree
static getSubTree(int $a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
Get complete branch of tree (recursively)
Definition: class.ilTrQuery.php:1547
ilTrQuery\getObjectIds
static getObjectIds(int $a_parent_obj_id, int $a_parent_ref_id, bool $use_collection=true, bool $a_refresh_status=true, ?array $a_user_ids=null)
Get (sub)objects for given object, also handles learning objectives (course only)
Definition: class.ilTrQuery.php:1467
ilTrQuery\getObjectStatisticsLogInfo
static getObjectStatisticsLogInfo()
Get last update info for object statistics.
Definition: class.ilTrQuery.php:2213
ilTrQuery\getSummaryPercentages
static getSummaryPercentages(string $field, string $base_query, ?string $alias=null)
Get aggregated data for field.
Definition: class.ilTrQuery.php:910
ilTrQuery\refreshObjectsStatus
static refreshObjectsStatus(array $a_obj_ids, ?array $a_users=null)
check whether status (for all relevant users) exists
Definition: class.ilTrQuery.php:2201
ilTrQuery\getSubItemsStatusForUser
static getSubItemsStatusForUser(int $a_user_id, int $a_parent_obj_id, array $a_item_ids)
Get subitems status.
Definition: class.ilTrQuery.php:216
ilTrQuery\executeQueries
static executeQueries(array $queries, string $a_order_field="", string $a_order_dir="", int $a_offset=0, int $a_limit=9999)
Execute given queries, including count query.
Definition: class.ilTrQuery.php:1588
ilTrQuery\getUserObjectMatrix
static getUserObjectMatrix(int $a_parent_ref_id, array $a_obj_ids, ?string $a_user_filter=null, ?array $a_additional_fields=null, ?array $a_privacy_fields=null, ?int $a_check_agreement=null)
Get status matrix for users on objects.
Definition: class.ilTrQuery.php:1667
ilTrQuery\getSubItemType
static getSubItemType(int $a_parent_obj_id)
Get sub-item object type for parent.
Definition: class.ilTrQuery.php:683
ilTrQuery\getObjectDailyStatistics
static getObjectDailyStatistics(array $a_ref_ids, string $a_year, ?string $a_month=null)
Definition: class.ilTrQuery.php:2034
ilTrQuery\getObjectsStatusForUser
static getObjectsStatusForUser(int $a_user_id, array $obj_refs)
Definition: class.ilTrQuery.php:23
ilTrQuery\deleteObjectStatistics
static deleteObjectStatistics(array $a_months)
Definition: class.ilTrQuery.php:2087
ilTree
Tree class data representation in hierachical trees using the Nested Set Model with Gaps by Joe Celco...
Definition: class.ilTree.php:30
ANONYMOUS_USER_ID
const ANONYMOUS_USER_ID
Definition: constants.php:27
ROOT_FOLDER_ID
const ROOT_FOLDER_ID
Definition: constants.php:32
$valid
$valid
Definition: dummy_client.php:59
$ref_id
$ref_id
Definition: ltiauth.php:66
$path
$path
Definition: ltiservices.php:30
$res
$res
Definition: ltiservices.php:69
if
if(!file_exists('../ilias.ini.php'))
Definition: sessioncheck.php:23
$DIC
global $DIC
Definition: shib_login.php:26
$GLOBALS
$GLOBALS["DIC"]
Definition: wac.php:54