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