ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
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;
189 } elseif (in_array(
190 $a_user_id,
191 $status_info["completed"][$sco_id]
192 )) {
193 $status = ilLPStatus::LP_STATUS_COMPLETED;
194 } elseif (in_array(
195 $a_user_id,
196 $status_info["in_progress"][$sco_id]
197 )) {
198 $status = ilLPStatus::LP_STATUS_IN_PROGRESS;
199 } else {
200 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED;
201 }
202
203 $items[$sco_id] = array(
204 "title" => $status_info["scos_title"][$sco_id],
205 "status" => (int) $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("country", $query);
884 $result["sel_country"] = self::getSummaryPercentages(
885 "sel_country",
886 $query
887 );
888 $result["city"] = self::getSummaryPercentages("city", $query);
889 $result["gender"] = self::getSummaryPercentages("gender", $query);
890 $result["language"] = self::getSummaryPercentages(
891 "usr_pref.value",
892 $query,
893 "language"
894 );
895 $result["status"] = self::getSummaryPercentages("status", $query);
896 $result["mark"] = self::getSummaryPercentages("mark", $query);
897 } else {
898 $result = array();
899 }
900
901 if ($result) {
902 $result["user_total"] = $users_no;
903 }
904
905 return $result;
906 }
907
911 protected static function getSummaryPercentages(
912 string $field,
913 string $base_query,
914 ?string $alias = null
915 ): array {
916 global $DIC;
917
918 $ilDB = $DIC['ilDB'];
919
920 if (!$alias) {
921 $field_alias = $field;
922 } else {
923 $field_alias = $alias;
924 $alias = " AS " . $alias;
925 }
926
927 // move having BEHIND group by
928 $having = "";
929 if (preg_match(
930 "/" . preg_quote(" [[--HAVING") . "(.+)" . preg_quote(
931 "HAVING--]]"
932 ) . "/",
933 $base_query,
934 $hits
935 )) {
936 $having = " HAVING " . $hits[1];
937 $base_query = str_replace($hits[0], "", $base_query);
938 }
939
940 $query = "SELECT COUNT(*) AS counter, " . $field . $alias . " " . $base_query . " GROUP BY " . $field . $having . " ORDER BY counter DESC";
941 $set = $ilDB->query($query);
942 $result = array();
943 while ($rec = $ilDB->fetchAssoc($set)) {
944 $result[$rec[$field_alias]] = (int) $rec["counter"];
945 }
946 return $result;
947 }
948
954 public static function getParticipantsForObject(int $a_ref_id): ?array
955 {
956 global $DIC;
957
958 $tree = $DIC['tree'];
959
960 $obj_id = ilObject::_lookupObjectId($a_ref_id);
961 $obj_type = ilObject::_lookupType($obj_id);
962
963 $members = [];
964
965 // try to get participants from (parent) course/group
966 $members_read = false;
967 switch ($obj_type) {
968 case 'crsr':
969 $members_read = true;
970 $olp = \ilObjectLP::getInstance($obj_id);
971 $members = $olp->getMembers();
972 break;
973
974 case 'crs':
975 case 'grp':
976 $members_read = true;
977 $member_obj = ilParticipants::getInstance($a_ref_id);
978 $members = $member_obj->getMembers();
979 break;
980
981 /* Mantis 19296: Individual Assessment can be subtype of crs.
982 * But for LP view only his own members should be displayed.
983 * We need to return the members without checking the parent path. */
984 case "iass":
985 $members_read = true;
986 $iass = new ilObjIndividualAssessment($obj_id, false);
987 $members = $iass->loadMembers()->membersIds();
988 break;
989
990 default:
991 // walk path to find course or group object and use members of that object
992 $path = $tree->getPathId($a_ref_id);
993 array_pop($path);
994 foreach (array_reverse($path) as $path_ref_id) {
995 $type = ilObject::_lookupType($path_ref_id, true);
996 if ($type == "crs" || $type == "grp") {
997 $members_read = true;
998 $members = self::getParticipantsForObject($path_ref_id);
999 }
1000 }
1001 break;
1002 }
1003
1004 // begin-patch ouf
1005 if ($members_read) {
1006 // BT 35452: failsafe against invalid users without an entry in usr_data
1007 $members = self::filterOutUsersWithoutData($members);
1008
1009 return $GLOBALS['DIC']->access(
1010 )->filterUserIdsByRbacOrPositionOfCurrentUser(
1011 'read_learning_progress',
1012 'read_learning_progress',
1013 $a_ref_id,
1014 $members
1015 );
1016 }
1017
1018 $a_users = null;
1019
1020 // no participants possible: use tracking/object data where possible
1021 switch ($obj_type) {
1022 case "sahs":
1023 $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
1024 if ($subtype == "scorm2004") {
1025 // based on cmi_node/cp_node, used for scorm tracking data views
1026 $mod = new ilObjSCORM2004LearningModule($obj_id, false);
1027 $all = $mod->getTrackedUsers("");
1028 if ($all) {
1029 $a_users = array();
1030 foreach ($all as $item) {
1031 $a_users[] = $item["user_id"];
1032 }
1033 }
1034 } else {
1035 $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
1036 }
1037 break;
1038
1039 case "exc":
1040 $exc = new ilObjExercise($obj_id, false);
1041 $members = new ilExerciseMembers($exc);
1042 $a_users = $members->getMembers();
1043 break;
1044
1045 case "tst":
1046 $class = ilLPStatusFactory::_getClassById(
1047 $obj_id,
1048 ilLPObjSettings::LP_MODE_TEST_FINISHED
1049 );
1050 $a_users = $class::getParticipants($obj_id);
1051 break;
1052
1053 case "svy":
1054 $class = ilLPStatusFactory::_getClassById(
1055 $obj_id,
1056 ilLPObjSettings::LP_MODE_SURVEY_FINISHED
1057 );
1058 $a_users = $class::getParticipants($obj_id);
1059 break;
1060
1061 case "prg":
1062 $prg = new ilObjStudyProgramme($obj_id, false);
1063 $a_users = $prg->getIdsOfUsersWithRelevantProgress();
1064 break;
1065 default:
1066 // keep null
1067 break;
1068 }
1069
1070 if (is_null($a_users)) {
1071 return $a_users;
1072 }
1073
1074 // BT 35452: failsafe against invalid users without an entry in usr_data
1075 $a_users = self::filterOutUsersWithoutData($a_users);
1076
1077 // begin-patch ouf
1078 return $GLOBALS['DIC']->access(
1079 )->filterUserIdsByRbacOrPositionOfCurrentUser(
1080 'read_learning_progress',
1081 'read_learning_progress',
1082 $a_ref_id,
1083 $a_users
1084 );
1085 }
1086
1091 protected static function filterOutUsersWithoutData(array $user_ids): array
1092 {
1093 if (ilObjUser::userExists($user_ids)) {
1094 return $user_ids;
1095 }
1096
1097 $res = [];
1098 foreach ($user_ids as $user_id) {
1099 if (ilObjUser::userExists([$user_id])) {
1100 $res[] = $user_id;
1101 continue;
1102 }
1103 global $DIC;
1104 $DIC->logger()->trac()->info(
1105 'Excluded user with id ' . $user_id .
1106 ' from participants, because they do not have an entry in usr_data.'
1107 );
1108 }
1109 return $res;
1110 }
1111
1112 protected static function buildFilters(
1113 array $where,
1114 ?array $a_filters = null,
1115 bool $a_aggregate = false
1116 ): string {
1117 global $DIC;
1118
1119 $ilDB = $DIC->database();
1120
1121 $having = array();
1122
1123 if (is_array($a_filters) && sizeof($a_filters) > 0) {
1124 foreach ($a_filters as $id => $value) {
1125 switch ($id) {
1126 case "login":
1127 case "firstname":
1128 case "lastname":
1129 case "institution":
1130 case "department":
1131 case "street":
1132 case "email":
1133 case "matriculation":
1134 case "country":
1135 case "city":
1136 case "title":
1137 $where[] = $ilDB->like(
1138 "usr_data." . $id,
1139 "text",
1140 "%" . $value . "%"
1141 );
1142 break;
1143
1144 case "gender":
1145 case "zipcode":
1146 case "sel_country":
1147 $where[] = "usr_data." . $id . " = " . $ilDB->quote(
1148 $value,
1149 "text"
1150 );
1151 break;
1152
1153 case "u_comment":
1154 $where[] = $ilDB->like(
1155 "ut_lp_marks." . $id,
1156 "text",
1157 "%" . $value . "%"
1158 );
1159 break;
1160
1161 case "status":
1162 if ($value == ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM) {
1163 // #10645 - not_attempted is default
1164 $where[] = "(ut_lp_marks.status = " . $ilDB->quote(
1165 ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM,
1166 "text"
1167 ) .
1168 " OR ut_lp_marks.status IS NULL)";
1169 break;
1170 }
1171 // fallthrough
1172
1173 // no break
1174 case "mark":
1175 $where[] = "ut_lp_marks." . $id . " = " . $ilDB->quote(
1176 $value,
1177 "text"
1178 );
1179 break;
1180
1181 case "percentage":
1182 if (!$a_aggregate) {
1183 if (isset($value["from"])) {
1184 $where[] = "ut_lp_marks." . $id . " >= " . $ilDB->quote(
1185 $value["from"],
1186 "integer"
1187 );
1188 }
1189 if (isset($value["to"])) {
1190 $where[] = "(ut_lp_marks." . $id . " <= " . $ilDB->quote(
1191 $value["to"],
1192 "integer"
1193 ) .
1194 " OR ut_lp_marks." . $id . " IS NULL)";
1195 }
1196 } else {
1197 if (isset($value["from"])) {
1198 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) >= " . $ilDB->quote(
1199 $value["from"],
1200 "integer"
1201 );
1202 }
1203 if (isset($value["to"])) {
1204 $having[] = "ROUND(AVG(ut_lp_marks." . $id . ")) <= " . $ilDB->quote(
1205 $value["to"],
1206 "integer"
1207 );
1208 }
1209 }
1210 break;
1211
1212 case "language":
1213 $where[] = "usr_pref.value = " . $ilDB->quote(
1214 $value,
1215 "text"
1216 );
1217 break;
1218
1219 // timestamp
1220 case "last_access":
1221 if (isset($value["from"])) {
1222 $value["from"] = substr(
1223 $value["from"],
1224 0,
1225 -2
1226 ) . "00";
1227 $value["from"] = new ilDateTime(
1228 $value["from"],
1229 IL_CAL_DATETIME
1230 );
1231 $value["from"] = $value["from"]->get(IL_CAL_UNIX);
1232 }
1233 if (isset($value["to"])) {
1234 if (strlen($value["to"]) == 19) {
1235 $value["to"] = substr(
1236 $value["to"],
1237 0,
1238 -2
1239 ) . "59"; // #14858
1240 }
1241 $value["to"] = new ilDateTime(
1242 $value["to"],
1243 IL_CAL_DATETIME
1244 );
1245 $value["to"] = $value["to"]->get(IL_CAL_UNIX);
1246 }
1247 // fallthrough
1248
1249 // no break
1250 case 'status_changed':
1251 // fallthrough
1252
1253 case "registration":
1254 if ($id == "registration") {
1255 $id = "create_date";
1256 }
1257 // fallthrough
1258
1259 // no break
1260 case "create_date":
1261 case "first_access":
1262 case "birthday":
1263 if (isset($value["from"])) {
1264 $where[] = $id . " >= " . $ilDB->quote(
1265 $value["from"],
1266 "date"
1267 );
1268 }
1269 if (isset($value["to"])) {
1270 if (strlen($value["to"]) == 19) {
1271 $value["to"] = substr(
1272 $value["to"],
1273 0,
1274 -2
1275 ) . "59"; // #14858
1276 }
1277 $where[] = $id . " <= " . $ilDB->quote(
1278 $value["to"],
1279 "date"
1280 );
1281 }
1282 break;
1283
1284 case "read_count":
1285 if (!$a_aggregate) {
1286 if (isset($value["from"]) && $value["from"] > 0) {
1287 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote(
1288 $value["from"],
1289 "integer"
1290 );
1291 }
1292 if (isset($value["to"])) {
1293 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote(
1294 $value["to"],
1295 "integer"
1296 ) .
1297 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1298 }
1299 } else {
1300 if (isset($value["from"]) && $value["from"] > 0) {
1301 $having[] = "IFNULL(SUM(read_event." . $id . "+read_event.childs_" . $id . "),0) >= " . $ilDB->quote(
1302 $value["from"],
1303 "integer"
1304 );
1305 }
1306 if (isset($value["to"])) {
1307 $having[] = "IFNULL(SUM(read_event." . $id . "+read_event.childs_" . $id . "),0) <= " . $ilDB->quote(
1308 $value["to"],
1309 "integer"
1310 );
1311 }
1312 }
1313 break;
1314
1315 case "spent_seconds":
1316 if (!$a_aggregate) {
1317 if (isset($value["from"]) && $value["from"] > 0) {
1318 $where[] = "(read_event." . $id . "+read_event.childs_" . $id . ") >= " . $ilDB->quote(
1319 $value["from"],
1320 "integer"
1321 );
1322 }
1323 if (isset($value["to"]) && $value["to"] > 0) {
1324 $where[] = "((read_event." . $id . "+read_event.childs_" . $id . ") <= " . $ilDB->quote(
1325 $value["to"],
1326 "integer"
1327 ) .
1328 " OR (read_event." . $id . "+read_event.childs_" . $id . ") IS NULL)";
1329 }
1330 } else {
1331 if (isset($value["from"]) && $value["from"] > 0) {
1332 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) >= " . $ilDB->quote(
1333 $value["from"],
1334 "integer"
1335 );
1336 }
1337 if (isset($value["to"]) && $value["to"] > 0) {
1338 $having[] = "ROUND(AVG(read_event." . $id . "+read_event.childs_" . $id . ")) <= " . $ilDB->quote(
1339 $value["to"],
1340 "integer"
1341 );
1342 }
1343 }
1344 break;
1345
1346 default:
1347 // var_dump("unknown: ".$id);
1348 break;
1349 }
1350 }
1351 }
1352
1353 $sql = "";
1354 if (sizeof($where)) {
1355 $sql .= " WHERE " . implode(" AND ", $where);
1356 }
1357 if (sizeof($having)) {
1358 // ugly "having" hack because of summary view
1359 $sql .= " [[--HAVING " . implode(" AND ", $having) . " HAVING--]]";
1360 }
1361
1362 return $sql;
1363 }
1364
1365 protected static function buildColumns(
1366 array &$a_fields,
1367 ?array $a_additional_fields = null,
1368 bool $a_aggregate = false
1369 ): array {
1370 if ($a_additional_fields === null || !count($a_additional_fields)) {
1371 return [];
1372 }
1373 $udf = [];
1374 foreach ($a_additional_fields as $field) {
1375 if (substr($field, 0, 4) != "udf_") {
1376 $function = null;
1377 if ($a_aggregate) {
1378 $pos = strrpos($field, "_");
1379 if ($pos === false) {
1380 continue;
1381 }
1382 $function = strtoupper(substr($field, $pos + 1));
1383 $field = substr($field, 0, $pos);
1384 if (!in_array(
1385 $function,
1386 array("MIN", "MAX", "SUM", "AVG", "COUNT")
1387 )) {
1388 continue;
1389 }
1390 }
1391
1392 switch ($field) {
1393 case 'org_units':
1394 break;
1395
1396 case "language":
1397 if ($function) {
1398 $a_fields[] = $function . "(value) " . $field . "_" . strtolower(
1399 $function
1400 );
1401 } else {
1402 $a_fields[] = "value as " . $field;
1403 }
1404 break;
1405
1406 case "read_count":
1407 case "spent_seconds":
1408 if (!$function) {
1409 $a_fields[] = "(" . $field . "+childs_" . $field . ") " . $field;
1410 } else {
1411 if ($function == "AVG") {
1412 $a_fields[] = "ROUND(AVG(" . $field . "+childs_" . $field . "), 2) " . $field . "_" . strtolower(
1413 $function
1414 );
1415 } else {
1416 $a_fields[] = $function . "(COALESCE(" . $field . ", 0) + COALESCE(childs_" . $field . ", 0)) " . $field . "_" . strtolower(
1417 $function
1418 );
1419 }
1420 }
1421 break;
1422
1423 case "read_count_spent_seconds":
1424 if ($function == "AVG") {
1425 $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) " . $field . "_" . strtolower(
1426 $function
1427 );
1428 }
1429 break;
1430
1431 default:
1432 if ($function) {
1433 if ($function == "AVG") {
1434 $a_fields[] = "ROUND(AVG(" . $field . "), 2) " . $field . "_" . strtolower(
1435 $function
1436 );
1437 } else {
1438 $a_fields[] = $function . "(" . $field . ") " . $field . "_" . strtolower(
1439 $function
1440 );
1441 }
1442 } else {
1443 $a_fields[] = $field;
1444 }
1445 break;
1446 }
1447 } else {
1448 $udf[] = substr($field, 4);
1449 }
1450 }
1451
1452 // clean-up
1453 $a_fields = array_unique($a_fields);
1454 if (count($udf)) {
1455 $udf = array_unique($udf);
1456 }
1457 return $udf;
1458 }
1459
1469 public static function getObjectIds(
1470 int $a_parent_obj_id,
1471 int $a_parent_ref_id,
1472 bool $use_collection = true,
1473 bool $a_refresh_status = true,
1474 ?array $a_user_ids = null
1475 ): array {
1476 $object_ids = array($a_parent_obj_id);
1477 $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1478 $objectives_parent_id = $scorm = $subitems = false;
1479
1480 $olp = ilObjectLP::getInstance($a_parent_obj_id);
1481 $mode = $olp->getCurrentMode();
1482 switch ($mode) {
1483 // what about LP_MODE_SCORM_PACKAGE ?
1484 case ilLPObjSettings::LP_MODE_SCORM:
1485 $status_scorm = get_class(
1486 ilLPStatusFactory::_getInstance(
1487 $a_parent_obj_id,
1488 ilLPObjSettings::LP_MODE_SCORM
1489 )
1490 );
1491 $scorm = $status_scorm::_getStatusInfo($a_parent_obj_id);
1492 break;
1493
1494 case ilLPObjSettings::LP_MODE_OBJECTIVES:
1495 if (ilObject::_lookupType($a_parent_obj_id) == "crs") {
1496 $objectives_parent_id = $a_parent_obj_id;
1497 }
1498 break;
1499
1500 case ilLPObjSettings::LP_MODE_COLLECTION_MANUAL:
1501 case ilLPObjSettings::LP_MODE_COLLECTION_TLT:
1502 case ilLPObjSettings::LP_MODE_COLLECTION_MOBS:
1503 $status_coll_tlt = get_class(
1504 ilLPStatusFactory::_getInstance($a_parent_obj_id, $mode)
1505 );
1506 $subitems = $status_coll_tlt::_getStatusInfo($a_parent_obj_id);
1507 break;
1508
1509 default:
1510 // lp collection
1511 if ($use_collection) {
1512 $collection = $olp->getCollectionInstance();
1513 if ($collection) {
1514 foreach ($collection->getItems() as $child_ref_id) {
1515 $child_id = ilObject::_lookupObjId($child_ref_id);
1516 $object_ids[] = $child_id;
1517 $ref_ids[$child_id] = $child_ref_id;
1518 }
1519 }
1520 } // all objects in branch
1521 else {
1522 self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1523 $object_ids = array_unique($object_ids);
1524 }
1525
1526 foreach ($object_ids as $idx => $object_id) {
1527 if (!$object_id) {
1528 unset($object_ids[$idx]);
1529 }
1530 }
1531 break;
1532 }
1533
1534 if ($a_refresh_status) {
1535 self::refreshObjectsStatus($object_ids, $a_user_ids);
1536 }
1537
1538 return array("object_ids" => $object_ids,
1539 "ref_ids" => $ref_ids,
1540 "objectives_parent_id" => $objectives_parent_id,
1541 "scorm" => $scorm,
1542 "subitems" => $subitems
1543 );
1544 }
1545
1549 protected static function getSubTree(
1550 int $a_parent_ref_id,
1551 array &$a_object_ids,
1552 array &$a_ref_ids
1553 ): void {
1554 global $DIC;
1555
1556 $tree = $DIC['tree'];
1557
1558 $children = $tree->getChilds($a_parent_ref_id);
1559 if ($children) {
1560 foreach ($children as $child) {
1561 if ($child["type"] == "adm" || $child["type"] == "rolf") {
1562 continue;
1563 }
1564
1565 // as there can be deactivated items in the collection
1566 // we should allow them here too
1567
1568 $olp = ilObjectLP::getInstance($child["obj_id"]);
1569 $cmode = $olp->getCurrentMode();
1570
1571 if ($cmode != ilLPObjSettings::LP_MODE_UNDEFINED) {
1572 $a_object_ids[] = $child["obj_id"];
1573 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1574 }
1575
1576 self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1577 }
1578 }
1579 }
1580
1590 public static function executeQueries(
1591 array $queries,
1592 string $a_order_field = "",
1593 string $a_order_dir = "",
1594 int $a_offset = 0,
1595 int $a_limit = 9999
1596 ): array {
1597 global $DIC;
1598
1599 $ilDB = $DIC->database();
1600 $cnt = 0;
1601 $subqueries = array();
1602 foreach ($queries as $item) {
1603 // ugly "having" hack because of summary view
1604 $item['query'] = str_replace("[[--HAVING", "HAVING", $item['query']);
1605 $item['query'] = str_replace("HAVING--]]", "", $item['query']);
1606
1607 if (!isset($item["count"])) {
1608 $count_field = $item["fields"];
1609 $count_field = array_shift($count_field);
1610 } else {
1611 $count_field = $item["count"];
1612 }
1613 $count_query = "SELECT COUNT(" . $count_field . ") AS cnt" . $item["query"];
1614 $set = $ilDB->query($count_query);
1615 if ($rec = $ilDB->fetchAssoc($set)) {
1616 $cnt += $rec["cnt"];
1617 }
1618
1619 $subqueries[] = "SELECT " . implode(
1620 ",",
1621 $item["fields"]
1622 ) . $item["query"];
1623 }
1624
1625 // set query
1626 $result = array();
1627 if ($cnt > 0) {
1628 if (sizeof($subqueries) > 1) {
1629 $base = array_shift($subqueries);
1630 $query = $base . " UNION (" . implode(
1631 ") UNION (",
1632 $subqueries
1633 ) . ")";
1634 } else {
1635 $query = $subqueries[0];
1636 }
1637
1638 if ($a_order_dir != "asc" && $a_order_dir != "desc") {
1639 $a_order_dir = "asc";
1640 }
1641 if ($a_order_field) {
1642 $query .= " ORDER BY " . $a_order_field . " " . strtoupper(
1643 $a_order_dir
1644 );
1645 }
1646
1647 $offset = $a_offset;
1648 $limit = $a_limit;
1649 $ilDB->setLimit($limit, $offset);
1650 $set = $ilDB->query($query);
1651 while ($rec = $ilDB->fetchAssoc($set)) {
1652 $result[] = $rec;
1653 }
1654 }
1655
1656 return array("cnt" => $cnt, "set" => $result);
1657 }
1658
1669 public static function getUserObjectMatrix(
1670 int $a_parent_ref_id,
1671 array $a_obj_ids,
1672 ?string $a_user_filter = null,
1673 ?array $a_additional_fields = null,
1674 ?array $a_privacy_fields = null,
1675 ?int $a_check_agreement = null
1676 ): array {
1677 global $DIC;
1678 $ilDB = $DIC->database();
1679
1680 $result = array("cnt" => 0, "set" => null);
1681 if (sizeof($a_obj_ids)) {
1682 $where = array();
1683 $where[] = "usr_data.usr_id <> " . $ilDB->quote(
1684 ANONYMOUS_USER_ID,
1685 "integer"
1686 );
1687 if ($a_user_filter) {
1688 $where[] = $ilDB->like(
1689 "usr_data.login",
1690 "text",
1691 "%" . $a_user_filter . "%"
1692 );
1693 }
1694
1695 // users
1696 $left = "";
1697 $a_users = self::getParticipantsForObject($a_parent_ref_id);
1698 if (is_array($a_users)) {
1699 $left = "LEFT";
1700 $where[] = $ilDB->in(
1701 "usr_data.usr_id",
1702 $a_users,
1703 false,
1704 "integer"
1705 );
1706 }
1707
1708 $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1709 self::refreshObjectsStatus($a_obj_ids, $a_users);
1710
1711 $fields = array("usr_data.usr_id", "login", "active");
1712 $udf = self::buildColumns($fields, $a_additional_fields);
1713
1714 // #18673 - if parent supports percentage does not matter for "sub-items"
1715 $fields[] = "percentage";
1716
1717 $raw = array();
1718 foreach ($a_obj_ids as $obj_id) {
1719 // one request for each object
1720 $query = " FROM usr_data " . $left . " JOIN read_event ON (read_event.usr_id = usr_data.usr_id" .
1721 " AND read_event.obj_id = " . $ilDB->quote(
1722 $obj_id,
1723 "integer"
1724 ) . ")" .
1725 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id " .
1726 " AND ut_lp_marks.obj_id = " . $ilDB->quote(
1727 $obj_id,
1728 "integer"
1729 ) . ")" .
1730 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = " . $ilDB->quote(
1731 "language",
1732 "text"
1733 ) . ")" .
1734 self::buildFilters($where);
1735
1736 $raw = self::executeQueries(
1737 array(array("fields" => $fields, "query" => $query)),
1738 "login"
1739 );
1740 if ($raw["cnt"]) {
1741 // convert to final structure
1742 foreach ($raw["set"] as $row) {
1743 $result["set"][(int) $row["usr_id"]]["login"] = ($row["login"] ?? '');
1744 $result["set"][(int) $row["usr_id"]]["usr_id"] = (int) ($row["usr_id"] ?? 0);
1745
1746 // #14953
1747 $result["set"][(int) $row["usr_id"]]["obj_" . $obj_id] = (int) ($row["status"] ?? 0);
1748 $result["set"][(int) $row["usr_id"]]["obj_" . $obj_id . "_perc"] = (int) ($row["percentage"] ?? 0);
1749 if ($obj_id == $parent_obj_id) {
1750 $result["set"][(int) $row["usr_id"]]["status_changed"] = (int) ($row["status_changed"] ?? 0);
1751 $result["set"][(int) $row["usr_id"]]["last_access"] = (int) ($row["last_access"] ?? 0);
1752 $result["set"][(int) $row["usr_id"]]["spent_seconds"] = (int) ($row["spent_seconds"] ?? 0);
1753 $result["set"][(int) $row["usr_id"]]["read_count"] = (int) ($row["read_count"] ?? 0);
1754 }
1755
1756 // @todo int cast?
1757 foreach ($fields as $field) {
1758 // #14957 - value [as] language
1759 if (stristr($field, "language")) {
1760 $field = "language";
1761 }
1762
1763 if (isset($row[$field])) {
1764 // #14955
1765 if ($obj_id == $parent_obj_id ||
1766 !in_array(
1767 $field,
1768 array("mark", "u_comment")
1769 )) {
1770 $result["set"][(int) $row["usr_id"]][$field] = $row[$field];
1771 }
1772 }
1773 }
1774 }
1775 }
1776 }
1777
1778 $result["cnt"] = 0;
1779 if (is_array($result["set"])) {
1780 $result["cnt"] = count($result["set"]);
1781 }
1782 $result["users"] = $a_users;
1783
1784 self::getUDFAndHandlePrivacy(
1785 $result,
1786 $udf,
1787 $a_check_agreement,
1788 $a_privacy_fields,
1789 $a_additional_fields
1790 );
1791 }
1792 return $result;
1793 }
1794
1795 public static function getUserObjectiveMatrix(
1796 int $a_parent_obj_id,
1797 array $a_users
1798 ): array {
1799 global $DIC;
1800
1801 $ilDB = $DIC->database();
1802
1803 if ($a_parent_obj_id && $a_users) {
1804 $res = array();
1805
1806 $objective_ids = ilCourseObjective::_getObjectiveIds(
1807 $a_parent_obj_id,
1808 true
1809 );
1810
1811 // #17402 - are initital test(s) qualifying?
1812 $lo_set = ilLOSettings::getInstanceByObjId($a_parent_obj_id);
1813 $initial_qualifying = $lo_set->isInitialTestQualifying();
1814
1815 // there may be missing entries for any user / objective combination
1816 foreach ($objective_ids as $objective_id) {
1817 foreach ($a_users as $user_id) {
1818 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
1819 }
1820 }
1821
1822 $query = "SELECT * FROM loc_user_results" .
1823 " WHERE " . $ilDB->in(
1824 "objective_id",
1825 $objective_ids,
1826 false,
1827 "integer"
1828 ) .
1829 " AND " . $ilDB->in("user_id", $a_users, false, "integer");
1830 if (!$initial_qualifying) {
1831 $query .= " AND type = " . $ilDB->quote(
1832 ilLOUserResults::TYPE_QUALIFIED,
1833 "integer"
1834 );
1835 }
1836 $query .= " ORDER BY type"; // qualified must come last!
1837 $set = $ilDB->query($query);
1838 while ($row = $ilDB->fetchAssoc($set)) {
1839 $objective_id = (int) $row["objective_id"];
1840 $user_id = (int) $row["user_id"];
1841
1842 // if both initial and qualified, qualified will overwrite initial
1843
1844 // #15873 - see ilLOUserResults::getObjectiveStatusForLP()
1845 if ($row["status"] == ilLOUserResults::STATUS_COMPLETED) {
1846 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
1847 } elseif ($row["status"] == ilLOUserResults::STATUS_FAILED) {
1848 $res[$user_id][$objective_id] = (int) $row["is_final"]
1849 ? ilLPStatus::LP_STATUS_FAILED_NUM
1850 : ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
1851 }
1852 }
1853
1854 return $res;
1855 }
1856 return [];
1857 }
1858
1859 public static function getObjectAccessStatistics(
1860 array $a_ref_ids,
1861 string $a_year,
1862 ?string $a_month = null
1863 ): array {
1864 global $DIC;
1865
1866 $ilDB = $DIC['ilDB'];
1867
1868 $obj_ids = array_keys($a_ref_ids);
1869
1870 if ($a_month) {
1871 $column = "dd";
1872 } else {
1873 $column = "mm";
1874 }
1875
1876 $res = array();
1877 $sql = "SELECT obj_id," . $column . ",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
1878 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
1879 " FROM obj_stat" .
1880 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1881 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1882 if ($a_month) {
1883 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1884 }
1885 $sql .= " GROUP BY obj_id," . $column;
1886 $set = $ilDB->query($sql);
1887 while ($row = $ilDB->fetchAssoc($set)) {
1888 $row["read_count"] += (int) $row["childs_read_count"];
1889 $row["spent_seconds"] += (int) $row["childs_spent_seconds"];
1890 $res[$row["obj_id"]][$row[$column]]["read_count"] =
1891 ($res[$row["obj_id"]][$row[$column]]["read_count"] ?? 0) + $row["read_count"];
1892 $res[$row["obj_id"]][$row[$column]]["spent_seconds"] =
1893 ($res[$row["obj_id"]][$row[$column]]["spent_seconds"] ?? 0) + $row["spent_seconds"];
1894 }
1895
1896 // add user data
1897
1898 $sql = "SELECT obj_id," . $column . ",SUM(counter) counter" .
1899 " FROM obj_user_stat" .
1900 " WHERE " . $ilDB->in("obj_id", $obj_ids, "", "integer") .
1901 " AND yyyy = " . $ilDB->quote($a_year, "integer");
1902 if ($a_month) {
1903 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
1904 }
1905 $sql .= " GROUP BY obj_id," . $column;
1906 $set = $ilDB->query($sql);
1907 while ($row = $ilDB->fetchAssoc($set)) {
1908 if (!isset($res[(int) $row["obj_id"]][$row[$column]]["users"])) {
1909 $res[(int) $row["obj_id"]][$row[$column]]["users"] = 0;
1910 }
1911 $res[(int) $row["obj_id"]][$row[$column]]["users"] += (int) $row["counter"];
1912 }
1913
1914 return $res;
1915 }
1916
1917 public static function getObjectTypeStatistics(): array
1918 {
1919 global $DIC;
1920
1921 $ilDB = $DIC['ilDB'];
1922 $objDefinition = $DIC['objDefinition'];
1923
1924 // re-use add new item selection (folder is not that important)
1925 $types = array_keys(
1926 $objDefinition->getCreatableSubObjects(
1927 "root",
1928 ilObjectDefinition::MODE_REPOSITORY
1929 )
1930 );
1931
1932 // repository
1933 $tree = new ilTree(1);
1934 $sql = "SELECT " . $tree->getObjectDataTable(
1935 ) . ".obj_id," . $tree->getObjectDataTable() . ".type," .
1936 $tree->getTreeTable() . "." . $tree->getTreePk(
1937 ) . "," . $tree->getTableReference() . ".ref_id" .
1938 " FROM " . $tree->getTreeTable() .
1939 " " . $tree->buildJoin() .
1940 " WHERE " . $ilDB->in(
1941 $tree->getObjectDataTable() . ".type",
1942 $types,
1943 "",
1944 "text"
1945 );
1946 $set = $ilDB->query($sql);
1947 $res = array();
1948 while ($row = $ilDB->fetchAssoc($set)) {
1949 $res[$row["type"]]["type"] = (string) $row["type"];
1950 $res[$row["type"]]["references"] = ($res[$row["type"]]["references"] ?? 0) + 1;
1951 $res[$row["type"]]["objects"][] = (int) $row["obj_id"];
1952 if ($row[$tree->getTreePk()] < 0) {
1953 $res[$row["type"]]["deleted"] = ($res[$row["type"]]["deleted"] ?? 0) + 1;
1954 } else {
1955 $res[$row["type"]]["deleted"] = ($res[$row["type"]]["deleted"] ?? 0);
1956 }
1957 }
1958
1959 foreach ($res as $type => $values) {
1960 $res[$type]["objects"] = count((array_unique($values["objects"] ?? [])));
1961 }
1962
1963 // portfolios (not part of repository)
1964 foreach (self::getPortfolios() as $obj_id) {
1965 $res["prtf"]["type"] = "prtf";
1966 $res["prtf"]["references"] = ($res["prtf"]["references"] ?? 0) + 1;
1967 $res["prtf"]["objects"] = ($res["prtf"]["objects"] ?? 0) + 1;
1968 }
1969
1970 foreach (self::getWorkspaceBlogs() as $obj_id) {
1971 $res["blog"]["type"] = "blog";
1972 $res["blog"]["references"] = ($res["blog"]["references"] ?? 0) + 1;
1973 $res["blog"]["objects"] = ($res["blog"]["objects"] ?? 0) + 1;
1974 }
1975 return $res;
1976 }
1977
1978 public static function getWorkspaceBlogs(?string $a_title = null): array
1979 {
1980 global $DIC;
1981
1982 $ilDB = $DIC->database();
1983
1984 $res = array();
1985
1986 // blogs in workspace?
1987 $sql = "SELECT od.obj_id,oref.wsp_id,od.type" .
1988 " FROM tree_workspace wst" .
1989 " JOIN object_reference_ws oref ON (oref.wsp_id = wst.child)" .
1990 " JOIN object_data od ON (oref.obj_id = od.obj_id)" .
1991 " WHERE od.type = " . $ilDB->quote("blog", "text");
1992
1993 if ($a_title) {
1994 $sql .= " AND " . $ilDB->like(
1995 "od.title",
1996 "text",
1997 "%" . $a_title . "%"
1998 );
1999 }
2000
2001 $set = $ilDB->query($sql);
2002 while ($row = $ilDB->fetchAssoc($set)) {
2003 $res[] = (int) $row["obj_id"];
2004 }
2005 return $res;
2006 }
2007
2008 public static function getPortfolios(?string $a_title = null): array
2009 {
2010 global $DIC;
2011
2012 $ilDB = $DIC['ilDB'];
2013
2014 $res = array();
2015
2016 $sql = "SELECT od.obj_id" .
2017 " FROM usr_portfolio prtf" .
2018 " JOIN object_data od ON (od.obj_id = prtf.id)";
2019
2020 if ($a_title) {
2021 $sql .= " WHERE " . $ilDB->like(
2022 "od.title",
2023 "text",
2024 "%" . $a_title . "%"
2025 );
2026 }
2027
2028 $set = $ilDB->query($sql);
2029 while ($row = $ilDB->fetchAssoc($set)) {
2030 $res[] = (int) $row["obj_id"];
2031 }
2032
2033 return $res;
2034 }
2035
2036 public static function getObjectDailyStatistics(
2037 array $a_ref_ids,
2038 string $a_year,
2039 ?string $a_month = null
2040 ): array {
2041 global $DIC;
2042
2043 $ilDB = $DIC->database();
2044 $obj_ids = array_keys($a_ref_ids);
2045
2046 $res = array();
2047 $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count," .
2048 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds" .
2049 " FROM obj_stat" .
2050 " WHERE " . $ilDB->in("obj_id", $obj_ids, false, "integer") .
2051 " AND yyyy = " . $ilDB->quote($a_year, "integer");
2052 if ($a_month) {
2053 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
2054 }
2055 $sql .= " GROUP BY obj_id,hh";
2056 $set = $ilDB->query($sql);
2057 while ($row = $ilDB->fetchAssoc($set)) {
2058 $row["read_count"] += (int) $row["childs_read_count"];
2059 $row["spent_seconds"] += (int) $row["childs_spent_seconds"];
2060 $res[$row["obj_id"]][(int) $row["hh"]]["read_count"] =
2061 ($res[$row["obj_id"]][(int) $row["hh"]]["read_count"] ?? 0) + $row["read_count"];
2062 $res[$row["obj_id"]][(int) $row["hh"]]["spent_seconds"] =
2063 ($res[$row["obj_id"]][(int) $row["hh"]]["spent_seconds"] ?? 0) + $row["spent_seconds"];
2064 }
2065 return $res;
2066 }
2067
2068 public static function getObjectStatisticsMonthlySummary(): array
2069 {
2070 global $DIC;
2071
2072 $ilDB = $DIC['ilDB'];
2073
2074 $set = $ilDB->query(
2075 "SELECT COUNT(*) AS COUNTER,yyyy,mm" .
2076 " FROM obj_stat" .
2077 " GROUP BY yyyy, mm" .
2078 " ORDER BY yyyy DESC, mm DESC"
2079 );
2080 $res = array();
2081 while ($row = $ilDB->fetchAssoc($set)) {
2082 $res[] = array("month" => $row["yyyy"] . "-" . $row["mm"],
2083 "count" => (int) ($row["COUNTER"] ?? 0)
2084 );
2085 }
2086 return $res;
2087 }
2088
2089 public static function deleteObjectStatistics(array $a_months): void
2090 {
2091 global $DIC;
2092
2093 $ilDB = $DIC->database();
2094
2095 // no combined column, have to concat
2096 $date_compare = $ilDB->in(
2097 $ilDB->concat(
2098 array(array("yyyy", ""),
2099 array($ilDB->quote("-", "text"), ""),
2100 array("mm", "")
2101 )
2102 ),
2103 $a_months,
2104 false,
2105 "text"
2106 );
2107 $sql = "DELETE FROM obj_stat" .
2108 " WHERE " . $date_compare;
2109 $ilDB->manipulate($sql);
2110
2111 // fulldate == YYYYMMDD
2112 $tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
2113 foreach ($a_months as $month) {
2114 $year = substr($month, 0, 4);
2115 $month = substr($month, 5);
2116 $from = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "01";
2117 $to = $year . str_pad($month, 2, "0", STR_PAD_LEFT) . "31";
2118
2119 foreach ($tables as $table) {
2120 $sql = "DELETE FROM " . $table .
2121 " WHERE fulldate >= " . $ilDB->quote($from, "integer") .
2122 " AND fulldate <= " . $ilDB->quote($to, "integer");
2123 $ilDB->manipulate($sql);
2124 }
2125 }
2126 }
2127
2128 public static function searchObjects(
2129 string $a_type,
2130 ?string $a_title = null,
2131 ?int $a_root = null,
2132 ?array $a_hidden = null,
2133 ?array $a_preset_obj_ids = null
2134 ): array {
2135 global $DIC;
2136
2137 $ilDB = $DIC->database();
2138 $tree = $DIC->repositoryTree();
2139
2140 if ($a_type == "lres") {
2141 $a_type = array('lm', 'sahs', 'htlm');
2142 }
2143
2144 $sql = "SELECT r.ref_id,r.obj_id" .
2145 " FROM object_data o" .
2146 " JOIN object_reference r ON (o.obj_id = r.obj_id)" .
2147 " JOIN tree t ON (t.child = r.ref_id)" .
2148 " WHERE t.tree = " . $ilDB->quote(1, "integer");
2149
2150 if (!is_array($a_type)) {
2151 $sql .= " AND o.type = " . $ilDB->quote($a_type, "text");
2152 } else {
2153 $sql .= " AND " . $ilDB->in("o.type", $a_type, false, "text");
2154 }
2155
2156 if ($a_title) {
2157 $sql .= " AND (" . $ilDB->like(
2158 "o.title",
2159 "text",
2160 "%" . $a_title . "%"
2161 ) .
2162 " OR " . $ilDB->like(
2163 "o.description",
2164 "text",
2165 "%" . $a_title . "%"
2166 ) . ")";
2167 }
2168
2169 if (is_array($a_hidden)) {
2170 $sql .= " AND " . $ilDB->in("o.obj_id", $a_hidden, true, "integer");
2171 }
2172
2173 if (is_array($a_preset_obj_ids)) {
2174 $sql .= " AND " . $ilDB->in(
2175 "o.obj_id",
2176 $a_preset_obj_ids,
2177 false,
2178 "integer"
2179 );
2180 }
2181
2182 $set = $ilDB->query($sql);
2183 $res = array();
2184 while ($row = $ilDB->fetchAssoc($set)) {
2185 if ($a_root && $a_root != ROOT_FOLDER_ID) {
2186 foreach (ilObject::_getAllReferences(
2187 $row['obj_id']
2188 ) as $ref_id) {
2189 if ($tree->isGrandChild($a_root, $ref_id)) {
2190 $res[$row["obj_id"]][] = (int) $row["ref_id"];
2191 }
2192 }
2193 } else {
2194 $res[$row["obj_id"]][] = (int) $row["ref_id"];
2195 }
2196 }
2197 return $res;
2198 }
2199
2203 protected static function refreshObjectsStatus(
2204 array $a_obj_ids,
2205 ?array $a_users = null
2206 ): void {
2207 foreach ($a_obj_ids as $obj_id) {
2208 ilLPStatus::checkStatusForObject($obj_id, $a_users);
2209 }
2210 }
2211
2215 public static function getObjectStatisticsLogInfo(): array
2216 {
2217 global $DIC;
2218
2219 $ilDB = $DIC->database();
2220 $set = $ilDB->query(
2221 "SELECT COUNT(*) counter, MIN(tstamp) tstamp" .
2222 " FROM obj_stat_log"
2223 );
2224 return $ilDB->fetchAssoc($set);
2225 }
2226
2227 public static function getObjectLPStatistics(
2228 array $a_obj_ids,
2229 int $a_year,
2230 ?int $a_month = null,
2231 bool $a_group_by_day = false
2232 ): array {
2233 global $DIC;
2234
2235 $ilDB = $DIC->database();
2236 if ($a_group_by_day) {
2237 $column = "dd";
2238 } else {
2239 $column = "mm,yyyy";
2240 }
2241
2242 $res = array();
2243 $sql = "SELECT obj_id," . $column . "," .
2244 "MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max," .
2245 "MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max," .
2246 "MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max," .
2247 "MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max," .
2248 "MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max" .
2249 " FROM obj_lp_stat" .
2250 " WHERE " . $ilDB->in("obj_id", $a_obj_ids, false, "integer") .
2251 " AND yyyy = " . $ilDB->quote($a_year, "integer");
2252 if ($a_month) {
2253 $sql .= " AND mm = " . $ilDB->quote($a_month, "integer");
2254 }
2255 $sql .= " GROUP BY obj_id," . $column;
2256 $set = $ilDB->query($sql);
2257 while ($row = $ilDB->fetchAssoc($set)) {
2258 $row['obj_id'] = (int) $row['obj_id'];
2259 $res[] = $row;
2260 }
2261
2262 return $res;
2263 }
2264
2265 public static function getObjectTypeStatisticsPerMonth(
2266 string $a_aggregation,
2267 ?string $a_year = null
2268 ): array {
2269 global $DIC;
2270
2271 $ilDB = $DIC['ilDB'];
2272
2273 if (!$a_year) {
2274 $a_year = date("Y");
2275 }
2276
2277 $agg = strtoupper($a_aggregation);
2278
2279 $res = array();
2280 $sql = "SELECT type,yyyy,mm," . $agg . "(cnt_objects) cnt_objects," . $agg . "(cnt_references) cnt_references," .
2281 "" . $agg . "(cnt_deleted) cnt_deleted FROM obj_type_stat" .
2282 " WHERE yyyy = " . $ilDB->quote($a_year, "integer") .
2283 " GROUP BY type,yyyy,mm";
2284 $set = $ilDB->query($sql);
2285 while ($row = $ilDB->fetchAssoc($set)) {
2286 $row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
2287 $res[$row["type"]][$row["yyyy"] . "-" . $row["mm"]] = array(
2288 "objects" => (int) $row["cnt_objects"],
2289 "references" => (int) $row["cnt_references"],
2290 "deleted" => (int) $row["cnt_deleted"]
2291 );
2292 }
2293
2294 return $res;
2295 }
2296
2297 public static function getObjectTypeStatisticsMinYear()
2298 {
2299 global $DIC;
2300
2301 $db = $DIC->database();
2302 $query = 'select min(yyyy) min from obj_type_stat';
2303 $res = $db->query($query);
2304 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
2305 return $row->min;
2306 }
2307 return date('Y');
2308 }
2309}
$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_COMPLETED
const LP_STATUS_COMPLETED
Definition: class.ilLPStatus.php:39
ilLPStatus\LP_STATUS_FAILED
const LP_STATUS_FAILED
Definition: class.ilLPStatus.php:40
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
ilLPStatus\LP_STATUS_NOT_ATTEMPTED
const LP_STATUS_NOT_ATTEMPTED
Definition: class.ilLPStatus.php:37
ilLPStatus\LP_STATUS_IN_PROGRESS
const LP_STATUS_IN_PROGRESS
Definition: class.ilLPStatus.php:38
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:397
ilObjUser\userExists
static userExists(array $a_usr_ids=[])
Definition: class.ilObjUser.php:2439
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:2227
ilTrQuery\filterOutUsersWithoutData
static filterOutUsersWithoutData(array $user_ids)
Definition: class.ilTrQuery.php:1091
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:954
ilTrQuery\getUserObjectiveMatrix
static getUserObjectiveMatrix(int $a_parent_obj_id, array $a_users)
Definition: class.ilTrQuery.php:1795
ilTrQuery\getObjectAccessStatistics
static getObjectAccessStatistics(array $a_ref_ids, string $a_year, ?string $a_month=null)
Definition: class.ilTrQuery.php:1859
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:1365
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:1917
ilTrQuery\getObjectTypeStatisticsPerMonth
static getObjectTypeStatisticsPerMonth(string $a_aggregation, ?string $a_year=null)
Definition: class.ilTrQuery.php:2265
ilTrQuery\getObjectStatisticsMonthlySummary
static getObjectStatisticsMonthlySummary()
Definition: class.ilTrQuery.php:2068
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:1112
ilTrQuery\getObjectTypeStatisticsMinYear
static getObjectTypeStatisticsMinYear()
Definition: class.ilTrQuery.php:2297
ilTrQuery\getWorkspaceBlogs
static getWorkspaceBlogs(?string $a_title=null)
Definition: class.ilTrQuery.php:1978
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:2008
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:2128
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:1549
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:1469
ilTrQuery\getObjectStatisticsLogInfo
static getObjectStatisticsLogInfo()
Get last update info for object statistics.
Definition: class.ilTrQuery.php:2215
ilTrQuery\getSummaryPercentages
static getSummaryPercentages(string $field, string $base_query, ?string $alias=null)
Get aggregated data for field.
Definition: class.ilTrQuery.php:911
ilTrQuery\refreshObjectsStatus
static refreshObjectsStatus(array $a_obj_ids, ?array $a_users=null)
check whether status (for all relevant users) exists
Definition: class.ilTrQuery.php:2203
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:1590
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:1669
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:2036
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:2089
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