ILIAS  release_5-0 Revision 5.0.0-1144-gc4397b1f870
class.ilTrQuery.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3
12class ilTrQuery
13{
14 function getObjectsStatusForUser($a_user_id, array $obj_refs)
15 {
16 global $ilDB;
17
18 if(sizeof($obj_refs))
19 {
20 $obj_ids = array_keys($obj_refs);
21 self::refreshObjectsStatus($obj_ids, array($a_user_id));
22
23 include_once "Services/Object/classes/class.ilObjectLP.php";
24 include_once "Services/Tracking/classes/class.ilLPStatus.php";
25
26 // prepare object view modes
27 include_once 'Modules/Course/classes/class.ilObjCourse.php';
28 $view_modes = array();
29 $query = "SELECT obj_id, view_mode FROM crs_settings".
30 " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
31 $set = $ilDB->query($query);
32 while($rec = $ilDB->fetchAssoc($set))
33 {
34 $view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
35 }
36
37 $sessions = self::getSessionData($a_user_id, $obj_ids);
38
39 $query = "SELECT object_data.obj_id, title, CASE WHEN status IS NULL THEN ".ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM." ELSE status END AS status,".
40 " status_changed, percentage, read_count+childs_read_count AS read_count, spent_seconds+childs_spent_seconds AS spent_seconds,".
41 " u_mode, type, visits, mark, u_comment".
42 " FROM object_data".
43 " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
44 " LEFT JOIN read_event ON (read_event.obj_id = object_data.obj_id AND read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
45 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.obj_id = object_data.obj_id AND ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
46 // " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(ilLPObjSettings::LP_MODE_DEACTIVATED, "integer").")".
47 " WHERE ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
48 " ORDER BY title";
49 $set = $ilDB->query($query);
50 $result = array();
51 while($rec = $ilDB->fetchAssoc($set))
52 {
53 $rec["comment"] = $rec["u_comment"];
54 unset($rec["u_comment"]);
55
56 $rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
57 $rec["status"] = (int)$rec["status"];
58 $rec["percentage"] = (int)$rec["percentage"];
59 $rec["read_count"] = (int)$rec["read_count"];
60 $rec["spent_seconds"] = (int)$rec["spent_seconds"];
61 $rec["u_mode"] = (int)$rec["u_mode"];
62
63 if($rec["type"] == "sess")
64 {
65 $session = $sessions[$rec["obj_id"]];
66 $rec["title"] = $session["title"];
67 // $rec["status"] = (int)$session["status"];
68 }
69
70 // lp mode might not match object/course view mode
71 if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
72 {
73 $rec["u_mode"] = ilLPObjSettings::LP_MODE_OBJECTIVES;
74 }
75 else if(!$rec["u_mode"])
76 {
77 $olp = ilObjectLP::getInstance($rec["obj_id"]);
78 $rec["u_mode"] = $olp->getCurrentMode();
79 }
80
81 // can be default mode
82 if(/*$rec["u_mode"] != ilLPObjSettings::LP_MODE_DEACTIVATE*/ true)
83 {
84 $result[] = $rec;
85 }
86 }
87 return $result;
88 }
89 }
90
91 function getObjectivesStatusForUser($a_user_id, array $a_objective_ids)
92 {
93 global $ilDB;
94
95 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
96 $lo_lp_status = ilLOUserResults::getObjectiveStatusForLP($a_user_id, $a_objective_ids);
97
98 $query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title,".$ilDB->quote("lobj", "text")." AS type".
99 " FROM crs_objectives".
100 " WHERE ".$ilDB->in("crs_objectives.objective_id", $a_objective_ids, false, "integer").
101 " AND active = ".$ilDB->quote(1, "integer").
102 " ORDER BY position";
103 $set = $ilDB->query($query);
104 $result = array();
105 while($rec = $ilDB->fetchAssoc($set))
106 {
107 if(array_key_exists($rec["obj_id"], $lo_lp_status))
108 {
109 $rec["status"] = $lo_lp_status[$rec["obj_id"]];
110 }
111 else
112 {
113 $rec["status"] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
114 }
115 $result[] = $rec;
116 }
117
118 return $result;
119 }
120
121 function getSCOsStatusForUser($a_user_id, $a_parent_obj_id, array $a_sco_ids)
122 {
123 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
124
125 // import score from tracking data
126 $scores_raw = $scores = array();
127 include_once './Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php';
128 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
129 switch($subtype)
130 {
131 case 'hacp':
132 case 'aicc':
133 case 'scorm':
134 include_once './Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php';
135 $module = new ilObjSCORMLearningModule($a_parent_obj_id, false);
136 $scores_raw = $module->getTrackingDataAgg($a_user_id);
137 break;
138
139 case 'scorm2004':
140 include_once './Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php';
141 $module = new ilObjSCORM2004LearningModule($a_parent_obj_id, false);
142 $scores_raw = $module->getTrackingDataAgg($a_user_id);
143 break;
144 }
145 if($scores_raw)
146 {
147 foreach($scores_raw as $item)
148 {
149 $scores[$item["sco_id"]] = $item["score"];
150 }
151 unset($module);
152 unset($scores_raw);
153 }
154
155 include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
156 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
157
158 $items = array();
159 foreach($a_sco_ids as $sco_id)
160 {
161 // #9719 - can have in_progress AND failed/completed
162 if(in_array($a_user_id, $status_info["failed"][$sco_id]))
163 {
164 $status = ilLPStatus::LP_STATUS_FAILED;
165 }
166 elseif(in_array($a_user_id, $status_info["completed"][$sco_id]))
167 {
168 $status = ilLPStatus::LP_STATUS_COMPLETED;
169 }
170 elseif(in_array($a_user_id, $status_info["in_progress"][$sco_id]))
171 {
172 $status = ilLPStatus::LP_STATUS_IN_PROGRESS;
173 }
174 else
175 {
176 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED;
177 }
178
179 $items[$sco_id] = array(
180 "title" => $status_info["scos_title"][$sco_id],
181 "status" => $status,
182 "type" => "sahs",
183 "score" => (int)$scores[$sco_id]
184 );
185 }
186
187 return $items;
188 }
189
190 function getSubItemsStatusForUser($a_user_id, $a_parent_obj_id, array $a_item_ids)
191 {
192 self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
193
194 switch(ilObject::_lookupType($a_parent_obj_id))
195 {
196 case "lm":
197 include_once './Services/Object/classes/class.ilObjectLP.php';
198 $olp = ilObjectLP::getInstance($a_parent_obj_id);
199 $collection = $olp->getCollectionInstance();
200 if($collection)
201 {
202 $ref_ids = ilObject::_getAllReferences($a_parent_obj_id);
203 $ref_id = end($ref_ids);
204 $item_data = $collection->getPossibleItems($ref_id);
205 }
206 break;
207
208 default:
209 return array();
210 }
211
212 include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
213 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
214
215 $items = array();
216 foreach($a_item_ids as $item_id)
217 {
218 if(!isset($item_data[$item_id]))
219 {
220 continue;
221 }
222
223 if(in_array($a_user_id, $status_info["completed"][$item_id]))
224 {
225 $status = ilLPStatus::LP_STATUS_COMPLETED;
226 }
227 elseif(in_array($a_user_id, $status_info["in_progress"][$item_id]))
228 {
229 $status = ilLPStatus::LP_STATUS_IN_PROGRESS;
230 }
231 else
232 {
233 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED;
234 }
235
236 $items[$item_id] = array(
237 "title" => $item_data[$item_id]["title"],
238 "status" => $status,
239 "type" => "st"
240 );
241 }
242
243 return $items;
244 }
245
260 static function getUserDataForObject($a_ref_id, $a_order_field = "", $a_order_dir = "",
261 $a_offset = 0, $a_limit = 9999, array $a_filters = NULL, array $a_additional_fields = NULL,
262 $check_agreement = false, $privacy_fields = NULL)
263 {
264 global $ilDB;
265
266 $fields = array("usr_data.usr_id", "login", "active");
267 $udf = self::buildColumns($fields, $a_additional_fields);
268
269 $where = array();
270 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
271
272 // users
273 $left = "";
274 $a_users = self::getParticipantsForObject($a_ref_id);
275
276 $obj_id = ilObject::_lookupObjectId($a_ref_id);
277 self::refreshObjectsStatus(array($obj_id), $a_users);
278
279 if (is_array($a_users))
280 {
281 $left = "LEFT";
282 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
283 }
284
285 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
286 " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
287 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
288 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
289 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
290 self::buildFilters($where, $a_filters);
291
292 $queries = array(array("fields"=>$fields, "query"=>$query));
293
294 // #9598 - if language is not in fields alias is missing
295 if($a_order_field == "language")
296 {
297 $a_order_field = "usr_pref.value";
298 }
299
300 // udf data is added later on, not in this query
301 $udf_order = null;
302 if(!$a_order_field)
303 {
304 $a_order_field = "login";
305 }
306 else if(substr($a_order_field, 0, 4) == "udf_")
307 {
308 $udf_order = $a_order_field;
309 $a_order_field = null;
310 }
311
312 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
313
314 self::getUDFAndHandlePrivacy($result, $udf, $check_agreement, $privacy_fields, $a_filters);
315
316 // as we cannot do this in the query, sort by custom field here
317 // this will not work with pagination!
318 if($udf_order)
319 {
320 include_once "Services/Utilities/classes/class.ilStr.php";
321 $result["set"] = ilUtil::stableSortArray($result["set"],
322 $udf_order, $a_order_dir);
323 }
324
325 return $result;
326 }
327
337 protected static function getUDFAndHandlePrivacy(array &$a_result, array $a_udf = null,
338 $a_check_agreement = null, array $a_privacy_fields = null, array $a_filters = null)
339 {
340 global $ilDB;
341
342 if(!$a_result["cnt"])
343 {
344 return;
345 }
346
347 if(sizeof($a_udf))
348 {
349 $query = "SELECT usr_id, field_id, value FROM udf_text WHERE ".$ilDB->in("field_id", $a_udf, false, "integer");
350 $set = $ilDB->query($query);
351 $udf = array();
352 while($row = $ilDB->fetchAssoc($set))
353 {
354 $udf[$row["usr_id"]]["udf_".$row["field_id"]] = $row["value"];
355 }
356 }
357
358 // (course/group) user agreement
359 if($a_check_agreement)
360 {
361 // admins/tutors (write-access) will never have agreement ?!
362 include_once "Services/Membership/classes/class.ilMemberAgreement.php";
363 $agreements = ilMemberAgreement::lookupAcceptedAgreements($a_check_agreement);
364
365 // public information for users
366 $query = "SELECT usr_id FROM usr_pref WHERE keyword = ".$ilDB->quote("public_profile", "text").
367 " AND value = ".$ilDB->quote("y", "text")." OR value = ".$ilDB->quote("g", "text");
368 $set = $ilDB->query($query);
369 $all_public = array();
370 while($row = $ilDB->fetchAssoc($set))
371 {
372 $all_public[] = $row["usr_id"];
373 }
374 $query = "SELECT usr_id,keyword FROM usr_pref WHERE ".$ilDB->like("keyword", "text", "public_%", false).
375 " AND value = ".$ilDB->quote("y", "text")." AND ".$ilDB->in("usr_id", $all_public, "", "integer");
376 $set = $ilDB->query($query);
377 $public = array();
378 while($row = $ilDB->fetchAssoc($set))
379 {
380 $public[$row["usr_id"]][] = substr($row["keyword"], 7);
381 }
382 unset($all_public);
383 }
384
385 foreach($a_result["set"] as $idx => $row)
386 {
387 // add udf data
388 if(isset($udf[$row["usr_id"]]))
389 {
390 $a_result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
391 }
392
393 // remove all private data - if active agreement and agreement not given by user
394 if(sizeof($a_privacy_fields) && $a_check_agreement && !in_array($row["usr_id"], $agreements))
395 {
396 foreach($a_privacy_fields as $field)
397 {
398 // check against public profile
399 if(isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
400 !in_array($field, $public[$row["usr_id"]])))
401 {
402 // remove complete entry - offending field was filtered
403 if(isset($a_filters[$field]))
404 {
405 // we cannot remove row because of pagination!
406 foreach(array_keys($row) as $col_id)
407 {
408 $a_result["set"][$idx][$col_id] = null;
409 }
410 $a_result["set"][$idx]["privacy_conflict"] = true;
411 // unset($a_result["set"][$idx]);
412 break;
413 }
414 // remove offending field
415 else
416 {
417 $a_result["set"][$idx][$field] = false;
418 }
419 }
420 }
421 }
422 }
423
424 // $a_result["cnt"] = sizeof($a_result["set"]);
425 }
426
442 static function getObjectsDataForUser($a_user_id, $a_parent_obj_id, $a_parent_ref_id, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999,
443 array $a_filters = NULL, array $a_additional_fields = NULL, $use_collection = true)
444 {
445 global $ilDB;
446
447 $fields = array("object_data.obj_id", "title", "type");
448 self::buildColumns($fields, $a_additional_fields);
449
450 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection, true, array($a_user_id));
451
452 $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND".
453 " read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
454 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer")." AND".
455 " ut_lp_marks.obj_id = object_data.obj_id)".
456 " WHERE ".$ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer").
457 self::buildFilters(array(), $a_filters);
458
459 $queries = array();
460 $queries[] = array("fields"=>$fields, "query"=>$query);
461
462 // objectives data
463 if($objects["objectives_parent_id"])
464 {
465 $objective_fields = array("crs_objectives.objective_id AS obj_id", "title",
466 $ilDB->quote("lobj", "text")." as type");
467
468 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
469
470 if (is_array($a_additional_fields))
471 {
472 foreach($a_additional_fields as $field)
473 {
474 if($field != "status")
475 {
476 $objective_fields[] = "NULL AS ".$field;
477 }
478 else
479 {
480 include_once("Services/Tracking/classes/class.ilLPStatus.php");
481 $objective_fields[] = "CASE WHEN status = ".$ilDB->quote(ilLOUserResults::STATUS_COMPLETED, "integer").
482 " THEN ".ilLPStatus::LP_STATUS_COMPLETED_NUM.
483 " WHEN status = ".$ilDB->quote(ilLOUserResults::STATUS_FAILED, "integer").
484 " THEN ".ilLPStatus::LP_STATUS_FAILED_NUM.
485 " ELSE NULL END AS status";
486 }
487 }
488 }
489
490 $where = array();
491 $where[] = "crs_objectives.crs_id = ".$ilDB->quote($objects["objectives_parent_id"], "integer");
492 $where[] = "crs_objectives.active = ".$ilDB->quote(1, "integer");
493
494 $objectives_query = " FROM crs_objectives".
495 " LEFT JOIN loc_user_results ON (crs_objectives.objective_id = loc_user_results.objective_id".
496 " AND loc_user_results.user_id = ".$ilDB->quote($a_user_id, "integer").
497 " AND loc_user_results.type = ".$ilDB->quote(ilLOUserResults::TYPE_QUALIFIED, "integer").")".
498 self::buildFilters($where, $a_filters);
499
500 $queries[] = array("fields"=>$objective_fields, "query"=>$objectives_query, "count"=>"crs_objectives.objective_id");
501 }
502
503 if(!in_array($a_order_field, $fields))
504 {
505 $a_order_field = "title";
506 }
507
508 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
509 if($result["cnt"])
510 {
511 // session data
512 $sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
513
514 foreach($result["set"] as $idx => $item)
515 {
516 if($item["type"] == "sess")
517 {
518 $session = $sessions[$item["obj_id"]];
519 $result["set"][$idx]["title"] = $session["title"];
520 $result["set"][$idx]["sort_title"] = $session["e_start"];
521 // $result["set"][$idx]["status"] = (int)$session["status"];
522 }
523
524 $result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
525 }
526
527 // scos data (:TODO: will not be part of offset/limit)
528 if($objects["scorm"])
529 {
530 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
531 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
532 if($subtype == "scorm2004")
533 {
534 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
535 $sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
536 $scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
537 }
538 else
539 {
540 include_once("./Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php");
541 $sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
542 $scos_tracking = array();
543 foreach($sobj->getTrackingDataAgg($a_user_id) as $item)
544 {
545 // format: hhhh:mm:ss ?!
546 if($item["time"])
547 {
548 $time = explode(":", $item["time"]);
549 $item["time"] = $time[0]*60*60+$time[1]*60+$time[2];
550 }
551 $scos_tracking[$item["sco_id"]] = array("session_time"=>$item["time"]);
552 }
553 }
554
555 foreach($objects["scorm"]["scos"] as $sco)
556 {
557 $row = array("title" => $objects["scorm"]["scos_title"][$sco],
558 "type" => "sco");
559
560 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
561 if(in_array($a_user_id, $objects["scorm"]["completed"][$sco]))
562 {
563 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
564 }
565 else if(in_array($a_user_id, $objects["scorm"]["failed"][$sco]))
566 {
567 $status = ilLPStatus::LP_STATUS_FAILED_NUM;
568 }
569 else if(in_array($a_user_id, $objects["scorm"]["in_progress"][$sco]))
570 {
571 $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
572 }
573 $row["status"] = $status;
574
575 // add available tracking data
576 if(isset($scos_tracking[$sco]))
577 {
578 if(isset($scos_tracking[$sco]["last_access"]))
579 {
580 $date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
581 $row["last_access"] = $date->get(IL_CAL_UNIX);
582 }
583 $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
584 }
585
586 $result["set"][] = $row;
587 $result["cnt"]++;
588 }
589 }
590 }
591 return $result;
592 }
593
601 protected static function getSessionData($a_user_id, array $obj_ids)
602 {
603 global $ilDB;
604
605 $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,".
606 " mark, e_comment".
607 " FROM event".
608 " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)".
609 " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = ".$ilDB->quote($a_user_id, "integer").")".
610 " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
611 $set = $ilDB->query($query);
612 $sessions = array();
613 while($rec = $ilDB->fetchAssoc($set))
614 {
615 $rec["comment"] = $rec["e_comment"];
616 unset($rec["e_comment"]);
617
618 $date = ilDatePresentation::formatPeriod(
619 new ilDateTime($rec["e_start"], IL_CAL_DATETIME),
620 new ilDateTime($rec["e_end"], IL_CAL_DATETIME));
621
622 if($rec["title"])
623 {
624 $rec["title"] = $date.': '.$rec["title"];
625 }
626 else
627 {
628 $rec["title"] = $date;
629 }
630 $sessions[$rec["obj_id"]] = $rec;
631 }
632 return $sessions;
633 }
634
651 static function getObjectsSummaryForObject($a_parent_obj_id, $a_parent_ref_id, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999,
652 array $a_filters = NULL, array $a_additional_fields = NULL, $a_preselected_obj_ids = NULL)
653 {
654 global $ilDB;
655
656 $fields = array();
657 self::buildColumns($fields, $a_additional_fields, true);
658
659 $objects = array();
660 if($a_preselected_obj_ids === NULL)
661 {
662 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
663 }
664 else
665 {
666 foreach($a_preselected_obj_ids as $obj_id => $ref_ids)
667 {
668 $objects["object_ids"][] = $obj_id;
669 $objects["ref_ids"][$obj_id] = array_pop($ref_ids);
670 }
671 }
672
673 $result = array();
674 if($objects)
675 {
676 // object data
677 $set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
678 " WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
679 while($rec = $ilDB->fetchAssoc($set))
680 {
681 $object_data[$rec["obj_id"]] = $rec;
682 if($a_preselected_obj_ids)
683 {
684 $object_data[$rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[$rec["obj_id"]];
685 }
686 else
687 {
688 $object_data[$rec["obj_id"]]["ref_ids"] = array($objects["ref_ids"][$rec["obj_id"]]);
689 }
690 }
691
692 foreach($objects["ref_ids"] as $object_id => $ref_id)
693 {
694 $object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
695 if(sizeof($object_result))
696 {
697 if($object_data[$object_id])
698 {
699 $result[] = array_merge($object_data[$object_id], $object_result);
700 }
701 }
702 }
703
704 // :TODO: objectives
705 if($objects["objectives_parent_id"])
706 {
707
708 }
709 }
710
711 return array("cnt"=>sizeof($result), "set"=>$result);
712 }
713
722 protected static function getSummaryDataForObject($a_ref_id, array $fields, array $a_filters = NULL)
723 {
724 global $ilDB;
725
726 $where = array();
727 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
728
729 // users
730 $a_users = self::getParticipantsForObject($a_ref_id);
731 $left = "";
732 if (is_array($a_users)) // #14840
733 {
734 $left = "LEFT";
735 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
736 }
737
738 $obj_id = ilObject::_lookupObjectId($a_ref_id);
739 self::refreshObjectsStatus(array($obj_id), $a_users);
740
741 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
742 " AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
743 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
744 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
745 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
746 self::buildFilters($where, $a_filters, true);
747
748 $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
749
750 $queries = array();
751 $queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
752
753 $result = self::executeQueries($queries);
754 $result = $result["set"][0];
755 $users_no = $result["user_count"];
756
757 $valid = true;
758 if(!$users_no)
759 {
760 $valid = false;
761 }
762 else if(isset($a_filters["user_total"]))
763 {
764 if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
765 {
766 $valid = false;
767 }
768 else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
769 {
770 $valid = false;
771 }
772 }
773
774 if($valid)
775 {
776 $result["country"] = self::getSummaryPercentages("country", $query);
777 $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
778 $result["city"] = self::getSummaryPercentages("city", $query);
779 $result["gender"] = self::getSummaryPercentages("gender", $query);
780 $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
781 $result["status"] = self::getSummaryPercentages("status", $query);
782 $result["mark"] = self::getSummaryPercentages("mark", $query);
783 }
784 else
785 {
786 $result = array();
787 }
788
789 if($result)
790 {
791 $result["user_total"] = $users_no;
792 }
793
794 return $result;
795 }
796
805 protected static function getSummaryPercentages($field, $base_query, $alias = NULL)
806 {
807 global $ilDB;
808
809 if(!$alias)
810 {
811 $field_alias = $field;
812 }
813 else
814 {
815 $field_alias = $alias;
816 $alias = " AS ".$alias;
817 }
818
819 // move having BEHIND group by
820 $having = "";
821 if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
822 {
823 $having = " HAVING ".$hits[1];
824 $base_query = str_replace($hits[0], "", $base_query);
825 }
826
827 $query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
828 $set = $ilDB->query($query);
829 $result = array();
830 while($rec = $ilDB->fetchAssoc($set))
831 {
832 $result[$rec[$field_alias]] = (int)$rec["counter"];
833 }
834 return $result;
835 }
836
843 public static function getParticipantsForObject($a_ref_id)
844 {
845 global $tree;
846
847 $obj_id = ilObject::_lookupObjectId($a_ref_id);
848 $obj_type = ilObject::_lookupType($obj_id);
849
850 // try to get participants from (parent) course/group
851 switch($obj_type)
852 {
853 case "crs":
854 include_once "Modules/Course/classes/class.ilCourseParticipants.php";
855 $member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
856 return $member_obj->getMembers();
857
858 case "grp":
859 include_once "Modules/Group/classes/class.ilGroupParticipants.php";
860 $member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
861 return $member_obj->getMembers();
862
863 default:
864 // walk path to find course or group object and use members of that object
865 $path = $tree->getPathId($a_ref_id);
866 array_pop($path);
867 foreach(array_reverse($path) as $path_ref_id)
868 {
869 $type = ilObject::_lookupType($path_ref_id, true);
870 if($type == "crs" || $type == "grp")
871 {
872 return self::getParticipantsForObject($path_ref_id);
873 }
874 }
875 break;
876 }
877
878 $a_users = null;
879
880 // no participants possible: use tracking/object data where possible
881 switch($obj_type)
882 {
883 case "sahs":
884 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
885 $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
886 if ($subtype == "scorm2004")
887 {
888 // based on cmi_node/cp_node, used for scorm tracking data views
889 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
890 $mod = new ilObjSCORM2004LearningModule($obj_id, false);
891 $all = $mod->getTrackedUsers("");
892 if($all)
893 {
894 $a_users = array();
895 foreach($all as $item)
896 {
897 $a_users[] = $item["user_id"];
898 }
899 }
900 }
901 else
902 {
903 include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
904 $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
905 }
906 break;
907
908 case "exc":
909 include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
910 include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
911 $exc = new ilObjExercise($obj_id, false);
912 $members = new ilExerciseMembers($exc);
913 $a_users = $members->getMembers();
914 break;
915
916 case "tst":
917 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
918 $class = ilLPStatusFactory::_getClassById($obj_id, ilLPObjSettings::LP_MODE_TEST_FINISHED);
919 $a_users = $class::getParticipants($obj_id);
920 break;
921
922 default:
923 // no sensible data: return null
924 break;
925 }
926
927 return $a_users;
928 }
929
938 static protected function buildFilters(array $where, array $a_filters = NULL, $a_aggregate = false)
939 {
940 global $ilDB;
941
942 $having = array();
943
944 if(sizeof($a_filters))
945 {
946 foreach($a_filters as $id => $value)
947 {
948 switch($id)
949 {
950 case "login":
951 case "firstname":
952 case "lastname":
953 case "institution":
954 case "department":
955 case "street":
956 case "email":
957 case "matriculation":
958 case "country":
959 case "city":
960 case "title":
961 $where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
962 break;
963
964 case "gender":
965 case "zipcode":
966 case "sel_country":
967 $where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
968 break;
969
970 case "u_comment":
971 $where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
972 break;
973
974 case "status":
975 if($value == ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM)
976 {
977 // #10645 - not_attempted is default
978 $where[] = "(ut_lp_marks.status = ".$ilDB->quote(ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM ,"text").
979 " OR ut_lp_marks.status IS NULL)";
980 break;
981 }
982 // fallthrough
983
984 case "mark":
985 $where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
986 break;
987
988 case "percentage":
989 if(!$a_aggregate)
990 {
991 if($value["from"])
992 {
993 $where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
994 }
995 if($value["to"])
996 {
997 $where[] = "(ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer").
998 " OR ut_lp_marks.".$id." IS NULL)";
999 }
1000 }
1001 else
1002 {
1003 if($value["from"])
1004 {
1005 $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
1006 }
1007 if($value["to"])
1008 {
1009 $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
1010 }
1011 }
1012 break;
1013
1014 case "language":
1015 $where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
1016 break;
1017
1018 // timestamp
1019 case "last_access":
1020 if($value["from"])
1021 {
1022 $value["from"] = substr($value["from"], 0, -2)."00";
1023 $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
1024 $value["from"] = $value["from"]->get(IL_CAL_UNIX);
1025 }
1026 if($value["to"])
1027 {
1028 if(strlen($value["to"]) == 19)
1029 {
1030 $value["to"] = substr($value["to"], 0, -2)."59"; // #14858
1031 }
1032 $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
1033 $value["to"] = $value["to"]->get(IL_CAL_UNIX);
1034 }
1035 // fallthrough
1036
1037 case 'status_changed':
1038 // fallthrough
1039
1040 case "registration":
1041 if($id == "registration")
1042 {
1043 $id = "create_date";
1044 }
1045 // fallthrough
1046
1047 case "create_date":
1048 case "first_access":
1049 case "birthday":
1050 if($value["from"])
1051 {
1052 $where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
1053 }
1054 if($value["to"])
1055 {
1056 if(strlen($value["to"]) == 19)
1057 {
1058 $value["to"] = substr($value["to"], 0, -2)."59"; // #14858
1059 }
1060 $where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
1061 }
1062 break;
1063
1064 case "read_count":
1065 if(!$a_aggregate)
1066 {
1067 if($value["from"])
1068 {
1069 $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1070 }
1071 if($value["to"])
1072 {
1073 $where[] = "((read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer").
1074 " OR (read_event.".$id."+read_event.childs_".$id.") IS NULL)";
1075 }
1076 }
1077 else
1078 {
1079 if($value["from"])
1080 {
1081 $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1082 }
1083 if($value["to"])
1084 {
1085 $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
1086 }
1087 }
1088 break;
1089
1090 case "spent_seconds":
1091 if(!$a_aggregate)
1092 {
1093 if($value["from"])
1094 {
1095 $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1096 }
1097 if($value["to"])
1098 {
1099 $where[] = "((read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer").
1100 " OR (read_event.".$id."+read_event.childs_".$id.") IS NULL)";
1101 }
1102 }
1103 else
1104 {
1105 if($value["from"])
1106 {
1107 $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
1108 }
1109 if($value["to"])
1110 {
1111 $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
1112 }
1113 }
1114 break;
1115
1116 default:
1117 // var_dump("unknown: ".$id);
1118 break;
1119 }
1120 }
1121 }
1122
1123 $sql = "";
1124 if(sizeof($where))
1125 {
1126 $sql .= " WHERE ".implode(" AND ", $where);
1127 }
1128 if(sizeof($having))
1129 {
1130 // ugly "having" hack because of summary view
1131 $sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
1132 }
1133
1134 return $sql;
1135 }
1136
1145 static protected function buildColumns(array &$a_fields, array $a_additional_fields = NULL, $a_aggregate = false)
1146 {
1147 if(sizeof($a_additional_fields))
1148 {
1149 $udf = NULL;
1150 foreach($a_additional_fields as $field)
1151 {
1152 if(substr($field, 0, 4) != "udf_")
1153 {
1154 $function = NULL;
1155 if($a_aggregate)
1156 {
1157 $pos = strrpos($field, "_");
1158 if($pos === false)
1159 {
1160 continue;
1161 }
1162 $function = strtoupper(substr($field, $pos+1));
1163 $field = substr($field, 0, $pos);
1164 if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
1165 {
1166 continue;
1167 }
1168 }
1169
1170 switch($field)
1171 {
1172 case "language":
1173 if($function)
1174 {
1175 $a_fields[] = $function."(value) ".$field."_".strtolower($function);
1176 }
1177 else
1178 {
1179 $a_fields[] = "value ".$field;
1180 }
1181 break;
1182
1183 case "read_count":
1184 case "spent_seconds":
1185 if(!$function)
1186 {
1187 $a_fields[] = "(".$field."+childs_".$field.") ".$field;
1188 }
1189 else
1190 {
1191 if($function == "AVG")
1192 {
1193 $a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) ".$field."_".strtolower($function);
1194 }
1195 else
1196 {
1197 $a_fields[] = $function."(".$field."+childs_".$field.") ".$field."_".strtolower($function);
1198 }
1199 }
1200 break;
1201
1202 case "read_count_spent_seconds":
1203 if($function == "AVG")
1204 {
1205 $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) ".$field."_".strtolower($function);
1206 }
1207 break;
1208
1209 default:
1210 if($function)
1211 {
1212 if($function == "AVG")
1213 {
1214 $a_fields[] = "ROUND(AVG(".$field."), 2) ".$field."_".strtolower($function);
1215 }
1216 else
1217 {
1218 $a_fields[] = $function."(".$field.") ".$field."_".strtolower($function);
1219 }
1220 }
1221 else
1222 {
1223 $a_fields[] = $field;
1224 }
1225 break;
1226 }
1227 }
1228 else
1229 {
1230 $udf[] = substr($field, 4);
1231 }
1232 }
1233
1234 // clean-up
1235 $a_fields = array_unique($a_fields);
1236 if(is_array($udf))
1237 {
1238 $udf = array_unique($udf);
1239 }
1240
1241 return $udf;
1242 }
1243 }
1244
1255 static public function getObjectIds($a_parent_obj_id, $a_parent_ref_id = false, $use_collection = true, $a_refresh_status = true, $a_user_ids = null)
1256 {
1257 include_once "Services/Object/classes/class.ilObjectLP.php";
1258
1259 $object_ids = array($a_parent_obj_id);
1260 $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1261 $objectives_parent_id = $scorm = $subitems = false;
1262
1263 $olp = ilObjectLP::getInstance($a_parent_obj_id);
1264 $mode = $olp->getCurrentMode();
1265 switch($mode)
1266 {
1267 // what about LP_MODE_SCORM_PACKAGE ?
1268 case ilLPObjSettings::LP_MODE_SCORM:
1269 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1270 $status_scorm = ilLPStatusFactory::_getInstance($a_parent_obj_id, ilLPObjSettings::LP_MODE_SCORM);
1271 $scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
1272 break;
1273
1274 case ilLPObjSettings::LP_MODE_OBJECTIVES:
1275 if(ilObject::_lookupType($a_parent_obj_id) == "crs")
1276 {
1277 $objectives_parent_id = $a_parent_obj_id;
1278 }
1279 break;
1280
1281 case ilLPObjSettings::LP_MODE_COLLECTION_MANUAL:
1282 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1283 $status_coll_man = ilLPStatusFactory::_getInstance($a_parent_obj_id, ilLPObjSettings::LP_MODE_COLLECTION_MANUAL);
1284 $subitems = $status_coll_man->_getStatusInfo($a_parent_obj_id);
1285 break;
1286
1287 case ilLPObjSettings::LP_MODE_COLLECTION_TLT:
1288 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1289 $status_coll_tlt = ilLPStatusFactory::_getInstance($a_parent_obj_id, ilLPObjSettings::LP_MODE_COLLECTION_TLT);
1290 $subitems = $status_coll_tlt->_getStatusInfo($a_parent_obj_id);
1291 break;
1292
1293 default:
1294 // lp collection
1295 if($use_collection)
1296 {
1297 $collection = $olp->getCollectionInstance();
1298 if($collection)
1299 {
1300 foreach($collection->getItems() as $child_ref_id)
1301 {
1302 $child_id = ilObject::_lookupObjId($child_ref_id);
1303 $object_ids[] = $child_id;
1304 $ref_ids[$child_id] = $child_ref_id;
1305 }
1306 }
1307 }
1308 // all objects in branch
1309 else
1310 {
1311 self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1312 $object_ids = array_unique($object_ids);
1313 }
1314
1315 foreach($object_ids as $idx => $object_id)
1316 {
1317 if(!$object_id)
1318 {
1319 unset($object_ids[$idx]);
1320 }
1321 }
1322 break;
1323 }
1324
1325 if($a_refresh_status)
1326 {
1327 self::refreshObjectsStatus($object_ids, $a_user_ids);
1328 }
1329
1330 return array("object_ids" => $object_ids,
1331 "ref_ids" => $ref_ids,
1332 "objectives_parent_id" => $objectives_parent_id,
1333 "scorm" => $scorm,
1334 "subitems" => $subitems);
1335 }
1336
1344 static protected function getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
1345 {
1346 global $tree;
1347
1348 $children = $tree->getChilds($a_parent_ref_id);
1349 if($children)
1350 {
1351 foreach($children as $child)
1352 {
1353 if($child["type"] == "adm" || $child["type"] == "rolf")
1354 {
1355 continue;
1356 }
1357
1358 // as there can be deactivated items in the collection
1359 // we should allow them here too
1360
1361 $olp = ilObjectLP::getInstance($child["obj_id"]);
1362 $cmode = $olp->getCurrentMode();
1363
1364 /* see ilPluginLP
1365 if($cmode == ilLPObjSettings::LP_MODE_PLUGIN)
1366 {
1367 // #11368
1368 include_once "Services/Repository/classes/class.ilRepositoryObjectPluginSlot.php";
1369 if(ilRepositoryObjectPluginSlot::isTypePluginWithLP($child["type"], false))
1370 {
1371 $a_object_ids[] = $child["obj_id"];
1372 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1373 }
1374 }
1375 */
1376
1377 if(/* $cmode != ilLPObjSettings::LP_MODE_DEACTIVATED && */ $cmode != ilLPObjSettings::LP_MODE_UNDEFINED)
1378 {
1379 $a_object_ids[] = $child["obj_id"];
1380 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1381 }
1382
1383 self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1384 }
1385 }
1386 }
1387
1398 static function executeQueries(array $queries, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999)
1399 {
1400 global $ilDB;
1401
1402 $cnt = 0;
1403 $subqueries = array();
1404 foreach($queries as $item)
1405 {
1406 // ugly "having" hack because of summary view
1407 $item = str_replace("[[--HAVING", "HAVING", $item);
1408 $item = str_replace("HAVING--]]", "", $item);
1409
1410 if(!isset($item["count"]))
1411 {
1412 $count_field = $item["fields"];
1413 $count_field = array_shift($count_field);
1414 }
1415 else
1416 {
1417 $count_field = $item["count"];
1418 }
1419 $count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
1420 $set = $ilDB->query($count_query);
1421 if ($rec = $ilDB->fetchAssoc($set))
1422 {
1423 $cnt += $rec["cnt"];
1424 }
1425
1426 $subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
1427 }
1428
1429 // set query
1430 $result = array();
1431 if($cnt > 0)
1432 {
1433 if(sizeof($subqueries) > 1)
1434 {
1435 $base = array_shift($subqueries);
1436 $query = $base." UNION (".implode(") UNION (", $subqueries).")";
1437 }
1438 else
1439 {
1440 $query = $subqueries[0];
1441 }
1442
1443 if ($a_order_dir != "asc" && $a_order_dir != "desc")
1444 {
1445 $a_order_dir = "asc";
1446 }
1447 if($a_order_field)
1448 {
1449 $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
1450 }
1451
1452 $offset = (int) $a_offset;
1453 $limit = (int) $a_limit;
1454 $ilDB->setLimit($limit, $offset);
1455
1456 $set = $ilDB->query($query);
1457 while($rec = $ilDB->fetchAssoc($set))
1458 {
1459 $result[] = $rec;
1460 }
1461 }
1462
1463 return array("cnt" => $cnt, "set" => $result);
1464 }
1465
1477 static function getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter = NULL,
1478 array $a_additional_fields = null, array $a_privacy_fields = null, $a_check_agreement = null)
1479 {
1480 global $ilDB;
1481
1482 $result = array("cnt"=>0, "set"=>NULL);
1483 if(sizeof($a_obj_ids))
1484 {
1485 $where = array();
1486 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
1487 if($a_user_filter)
1488 {
1489 $where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
1490 }
1491
1492 // users
1493 $left = "";
1494 $a_users = self::getParticipantsForObject($a_parent_ref_id);
1495 if (is_array($a_users))
1496 {
1497 $left = "LEFT";
1498 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1499 }
1500
1501 $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1502 self::refreshObjectsStatus($a_obj_ids, $a_users);
1503
1504 $fields = array("usr_data.usr_id", "login", "active");
1505 $udf = self::buildColumns($fields, $a_additional_fields);
1506
1507 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1508
1509 $raw = array();
1510 foreach($a_obj_ids as $obj_id)
1511 {
1512 // one request for each object
1513 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
1514 " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1515 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
1516 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1517 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
1518 self::buildFilters($where);
1519
1520 $raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), "login");
1521 if($raw["cnt"])
1522 {
1523 // convert to final structure
1524 foreach($raw["set"] as $row)
1525 {
1526 $result["set"][$row["usr_id"]]["login"] = $row["login"];
1527 $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1528
1529 // #14953
1530 $result["set"][$row["usr_id"]]["obj_".$obj_id] = $row["status"];
1531 $result["set"][$row["usr_id"]]["obj_".$obj_id."_perc"] = $row["percentage"];
1532
1533 if($obj_id == $parent_obj_id)
1534 {
1535 $result["set"][$row["usr_id"]]["status_changed"] = $row["status_changed"];
1536 $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1537 $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1538 $result["set"][$row["usr_id"]]["read_count"] = $row["read_count"];
1539 }
1540
1541 foreach($fields as $field)
1542 {
1543 // #14957 - value [as] language
1544 if(stristr($field, "language"))
1545 {
1546 $field = "language";
1547 }
1548
1549 if(isset($row[$field]))
1550 {
1551 // #14955
1552 if($obj_id == $parent_obj_id ||
1553 !in_array($field, array("mark", "u_comment")))
1554 {
1555 $result["set"][$row["usr_id"]][$field] = $row[$field];
1556 }
1557 }
1558 }
1559 }
1560 }
1561 }
1562
1563 $result["cnt"] = sizeof($result["set"]);
1564 $result["users"] = $a_users;
1565
1566 self::getUDFAndHandlePrivacy($result, $udf, $a_check_agreement, $a_privacy_fields, $a_additional_fields);
1567 }
1568 return $result;
1569 }
1570
1571 static public function getUserObjectiveMatrix($a_parent_obj_id, $a_users)
1572 {
1573 global $ilDB;
1574
1575 if($a_parent_obj_id && $a_users)
1576 {
1577 $res = array();
1578
1579 include_once "Services/Tracking/classes/class.ilLPStatus.php";
1580 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
1581 include_once "Modules/Course/classes/class.ilCourseObjective.php";
1582 $objective_ids = ilCourseObjective::_getObjectiveIds($a_parent_obj_id,true);
1583
1584 // there may be missing entries for any user / objective combination
1585 foreach($objective_ids as $objective_id)
1586 {
1587 foreach($a_users as $user_id)
1588 {
1589 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
1590 }
1591 }
1592
1593 $query = "SELECT * FROM loc_user_results".
1594 " WHERE ".$ilDB->in("objective_id", $objective_ids, "", "integer").
1595 " AND ".$ilDB->in("user_id", $a_users, "", "integer").
1596 " AND type = ".$ilDB->quote(ilLOUserResults::TYPE_QUALIFIED, "integer");
1597 $set = $ilDB->query($query);
1598 while($row = $ilDB->fetchAssoc($set))
1599 {
1600 $objective_id = $row["objective_id"];
1601 $user_id = $row["user_id"];
1602
1603 // see ilLOUserResults::getObjectiveStatusForLP()
1604 if($row["status"] == ilLOUserResults::STATUS_COMPLETED)
1605 {
1606 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
1607 }
1608 else
1609 {
1610 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_FAILED_NUM;
1611 }
1612 }
1613
1614 return $res;
1615 }
1616 }
1617
1618 static public function getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month = null)
1619 {
1620 global $ilDB;
1621
1622 $obj_ids = array_keys($a_ref_ids);
1623
1624 if($a_month)
1625 {
1626 $column = "dd";
1627 }
1628 else
1629 {
1630 $column = "mm";
1631 }
1632
1633 $res = array();
1634 $sql = "SELECT obj_id,".$column.",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1635 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1636 " FROM obj_stat".
1637 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1638 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1639 if($a_month)
1640 {
1641 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1642 }
1643 $sql .= " GROUP BY obj_id,".$column;
1644 $set = $ilDB->query($sql);
1645 while($row = $ilDB->fetchAssoc($set))
1646 {
1647 $row["read_count"] += $row["childs_read_count"];
1648 $row["spent_seconds"] += $row["childs_spent_seconds"];
1649 $res[$row["obj_id"]][$row[$column]]["read_count"] += $row["read_count"];
1650 $res[$row["obj_id"]][$row[$column]]["spent_seconds"] += $row["spent_seconds"];
1651 }
1652
1653
1654 // add user data
1655
1656 $sql = "SELECT obj_id,".$column.",SUM(counter) counter".
1657 " FROM obj_user_stat".
1658 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1659 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1660 if($a_month)
1661 {
1662 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1663 }
1664 $sql .= " GROUP BY obj_id,".$column;
1665 $set = $ilDB->query($sql);
1666 while($row = $ilDB->fetchAssoc($set))
1667 {
1668 $res[$row["obj_id"]][$row[$column]]["users"] += $row["counter"];
1669 }
1670
1671 return $res;
1672 }
1673
1674 function getObjectTypeStatistics()
1675 {
1676 global $ilDB, $objDefinition;
1677
1678 // re-use add new item selection (folder is not that important)
1679 $types = array_keys($objDefinition->getCreatableSubObjects("root", ilObjectDefinition::MODE_REPOSITORY));
1680
1681 include_once "Services/Tree/classes/class.ilTree.php";
1682 $tree = new ilTree(1);
1683 $sql = "SELECT ".$tree->table_obj_data.".obj_id,".$tree->table_obj_data.".type,".
1684 $tree->table_tree.".".$tree->tree_pk.",".$tree->table_obj_reference.".ref_id".
1685 " FROM ".$tree->table_tree.
1686 " ".$tree->buildJoin().
1687 " WHERE ".$ilDB->in($tree->table_obj_data.".type", $types, "", "text");
1688 $set = $ilDB->query($sql);
1689 $res = array();
1690 while($row = $ilDB->fetchAssoc($set))
1691 {
1692 $res[$row["type"]]["type"] = $row["type"];
1693 $res[$row["type"]]["references"]++;
1694 $res[$row["type"]]["objects"][] = $row["obj_id"];
1695 if($row[$tree->tree_pk] < 0)
1696 {
1697 $res[$row["type"]]["deleted"]++;
1698 }
1699 }
1700
1701 foreach($res as $type => $values)
1702 {
1703 $res[$type]["objects"] = sizeof(array_unique($values["objects"]));
1704 }
1705
1706 return $res;
1707 }
1708
1709 static public function getObjectDailyStatistics(array $a_ref_ids, $a_year, $a_month = null)
1710 {
1711 global $ilDB;
1712
1713 $obj_ids = array_keys($a_ref_ids);
1714
1715 $res = array();
1716 $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1717 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1718 " FROM obj_stat".
1719 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1720 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1721 if($a_month)
1722 {
1723 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1724 }
1725 $sql .= " GROUP BY obj_id,hh";
1726 $set = $ilDB->query($sql);
1727 while($row = $ilDB->fetchAssoc($set))
1728 {
1729 $row["read_count"] += $row["childs_read_count"];
1730 $row["spent_seconds"] += $row["childs_spent_seconds"];
1731 $res[$row["obj_id"]][(int)$row["hh"]]["read_count"] += $row["read_count"];
1732 $res[$row["obj_id"]][(int)$row["hh"]]["spent_seconds"] += $row["spent_seconds"];
1733 }
1734 return $res;
1735 }
1736
1737 static public function getObjectStatisticsMonthlySummary()
1738 {
1739 global $ilDB;
1740
1741 $set = $ilDB->query("SELECT COUNT(*) AS COUNTER,yyyy,mm".
1742 " FROM obj_stat".
1743 " GROUP BY yyyy, mm".
1744 " ORDER BY yyyy DESC, mm DESC");
1745 $res = array();
1746 while($row = $ilDB->fetchAssoc($set))
1747 {
1748 $res[] = array("month"=>$row["yyyy"]."-".$row["mm"],
1749 "count"=>$row["counter"]);
1750 }
1751 return $res;
1752 }
1753
1754 static public function deleteObjectStatistics(array $a_months)
1755 {
1756 global $ilDB;
1757
1758 // no combined column, have to concat
1759 $date_compare = $ilDB->in($ilDB->concat(array(array("yyyy", ""),
1760 array($ilDB->quote("-", "text"), ""),
1761 array("mm", ""))), $a_months, "", "text");
1762 $sql = "DELETE FROM obj_stat".
1763 " WHERE ".$date_compare;
1764 $ilDB->manipulate($sql);
1765
1766 // fulldate == YYYYMMDD
1767 $tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
1768 foreach($a_months as $month)
1769 {
1770 $year = substr($month, 0, 4);
1771 $month = substr($month, 5);
1772 $from = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."01";
1773 $to = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."31";
1774
1775 foreach($tables as $table)
1776 {
1777 $sql = "DELETE FROM ".$table.
1778 " WHERE fulldate >= ".$ilDB->quote($from, "integer").
1779 " AND fulldate <= ".$ilDB->quote($to, "integer");
1780 $ilDB->manipulate($sql);
1781 }
1782 }
1783 }
1784
1785 static public function searchObjects($a_type, $a_title = null, $a_root = null, $a_hidden = null, $a_preset_obj_ids = null)
1786 {
1787 global $ilDB, $tree;
1788
1789 if($a_type == "lres")
1790 {
1791 $a_type = array('lm','sahs','htlm','dbk');
1792 }
1793
1794 $sql = "SELECT r.ref_id,r.obj_id".
1795 " FROM object_data o".
1796 " JOIN object_reference r ON (o.obj_id = r.obj_id)".
1797 " JOIN tree t ON (t.child = r.ref_id)".
1798 " WHERE t.tree = ".$ilDB->quote(1, "integer");
1799
1800 if(!is_array($a_type))
1801 {
1802 $sql .= " AND o.type = ".$ilDB->quote($a_type, "text");
1803 }
1804 else
1805 {
1806 $sql .= " AND ".$ilDB->in("o.type", $a_type, "", "text");
1807 }
1808
1809 if($a_title)
1810 {
1811 $sql .= " AND (".$ilDB->like("o.title", "text", "%".$a_title."%").
1812 " OR ".$ilDB->like("o.description", "text", "%".$a_title."%").")";
1813 }
1814
1815 if(is_array($a_hidden))
1816 {
1817 $sql .= " AND ".$ilDB->in("o.obj_id", $a_hidden, true, "integer");
1818 }
1819
1820 if(is_array($a_preset_obj_ids))
1821 {
1822 $sql .= " AND ".$ilDB->in("o.obj_id", $a_preset_obj_ids, false, "integer");
1823 }
1824
1825 $set = $ilDB->query($sql);
1826 $res = array();
1827 while($row = $ilDB->fetchAssoc($set))
1828 {
1829 if($a_root && $a_root != ROOT_FOLDER_ID)
1830 {
1831 foreach(ilObject::_getAllReferences($row['obj_id']) as $ref_id)
1832 {
1833 if($tree->isGrandChild($a_root, $ref_id))
1834 {
1835 $res[$row["obj_id"]][] = $row["ref_id"];
1836 continue;
1837 }
1838 }
1839 }
1840 else
1841 {
1842 $res[$row["obj_id"]][] = $row["ref_id"];
1843 }
1844 }
1845 return $res;
1846 }
1847
1854 protected static function refreshObjectsStatus(array $a_obj_ids, $a_users = null)
1855 {
1856 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1857 foreach($a_obj_ids as $obj_id)
1858 {
1859 ilLPStatus::checkStatusForObject($obj_id, $a_users);
1860 }
1861 }
1862
1868 public static function getObjectStatisticsLogInfo()
1869 {
1870 global $ilDB;
1871
1872 $set = $ilDB->query("SELECT COUNT(*) counter, MIN(tstamp) tstamp".
1873 " FROM obj_stat_log");
1874 return $ilDB->fetchAssoc($set);
1875 }
1876
1877 static public function getObjectLPStatistics(array $a_obj_ids, $a_year, $a_month = null, $a_group_by_day = false)
1878 {
1879 global $ilDB;
1880
1881 if($a_group_by_day)
1882 {
1883 $column = "dd";
1884 }
1885 else
1886 {
1887 $column = "mm,yyyy";
1888 }
1889
1890 $res = array();
1891 $sql = "SELECT obj_id,".$column.",".
1892 "MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max,".
1893 "MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max,".
1894 "MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max,".
1895 "MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max,".
1896 "MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max".
1897 " FROM obj_lp_stat".
1898 " WHERE ".$ilDB->in("obj_id", $a_obj_ids, "", "integer").
1899 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1900 if($a_month)
1901 {
1902 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1903 }
1904 $sql .= " GROUP BY obj_id,".$column;
1905 $set = $ilDB->query($sql);
1906 while($row = $ilDB->fetchAssoc($set))
1907 {
1908 $res[] = $row;
1909 }
1910
1911 return $res;
1912 }
1913
1914 function getObjectTypeStatisticsPerMonth($a_aggregation, $a_year = null)
1915 {
1916 global $ilDB;
1917
1918 if(!$a_year)
1919 {
1920 $a_year = date("Y");
1921 }
1922
1923 $agg = strtoupper($a_aggregation);
1924
1925 $res = array();
1926 $sql = "SELECT type,yyyy,mm,".$agg."(cnt_objects) cnt_objects,".$agg."(cnt_references) cnt_references,".
1927 "".$agg."(cnt_deleted) cnt_deleted FROM obj_type_stat".
1928 " WHERE yyyy = ".$ilDB->quote($a_year, "integer").
1929 " GROUP BY type,yyyy,mm";
1930 $set = $ilDB->query($sql);
1931 while($row = $ilDB->fetchAssoc($set))
1932 {
1933 $row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
1934 $res[$row["type"]][$row["yyyy"]."-".$row["mm"]] = array(
1935 "objects" => (int)$row["cnt_objects"],
1936 "references" => (int)$row["cnt_references"],
1937 "deleted" => (int)$row["cnt_deleted"]
1938 );
1939 }
1940
1941 return $res;
1942 }
1943}
1944
1945?>
$result
$result
Definition: CleanUpTest.php:407
IL_CRS_VIEW_OBJECTIVE
const IL_CRS_VIEW_OBJECTIVE
Definition: class.ilCourseConstants.php:39
IL_CAL_UNIX
const IL_CAL_UNIX
Definition: class.ilDateTime.php:11
IL_CAL_DATETIME
const IL_CAL_DATETIME
Definition: class.ilDateTime.php:9
ilCourseObjective\_getObjectiveIds
static _getObjectiveIds($course_id, $a_activated_only=false)
Definition: class.ilCourseObjective.php:468
ilCourseParticipants\_getInstanceByObjId
static _getInstanceByObjId($a_obj_id)
Get singleton instance.
Definition: class.ilCourseParticipants.php:77
ilDatePresentation\formatPeriod
static formatPeriod(ilDateTime $start, ilDateTime $end)
Format a period of two date Shows: 14.
Definition: class.ilDatePresentation.php:182
ilDateTime
@classDescription Date and time handling
Definition: class.ilDateTime.php:33
ilExerciseMembers
Class ilExerciseMembers.
Definition: class.ilExerciseMembers.php:13
ilGroupParticipants\_getInstanceByObjId
static _getInstanceByObjId($a_obj_id)
Get singleton instance.
Definition: class.ilGroupParticipants.php:63
ilLOUserResults\getObjectiveStatusForLP
static getObjectiveStatusForLP($a_user_id, array $a_objective_ids)
Definition: class.ilLOUserResults.php:340
ilLPStatusFactory\_getClassById
_getClassById($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:25
ilLPStatusFactory\_getInstance
_getInstance($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:142
ilLPStatusWrapper\_getStatusInfo
_getStatusInfo($a_obj_id)
Reads informations about the object e.g test results, tlt, number of visits.
Definition: class.ilLPStatusWrapper.php:128
ilLPStatus\LP_STATUS_COMPLETED_NUM
const LP_STATUS_COMPLETED_NUM
Definition: class.ilLPStatus.php:30
ilLPStatus\LP_STATUS_COMPLETED
const LP_STATUS_COMPLETED
Definition: class.ilLPStatus.php:25
ilLPStatus\LP_STATUS_FAILED
const LP_STATUS_FAILED
Definition: class.ilLPStatus.php:26
ilLPStatus\LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_IN_PROGRESS_NUM
Definition: class.ilLPStatus.php:29
ilLPStatus\checkStatusForObject
static checkStatusForObject($a_obj_id, $a_users=false)
This function checks whether the status for a given number of users is dirty and must be recalculated...
Definition: class.ilLPStatus.php:241
ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM
const LP_STATUS_NOT_ATTEMPTED_NUM
Definition: class.ilLPStatus.php:28
ilLPStatus\LP_STATUS_FAILED_NUM
const LP_STATUS_FAILED_NUM
Definition: class.ilLPStatus.php:31
ilLPStatus\LP_STATUS_NOT_ATTEMPTED
const LP_STATUS_NOT_ATTEMPTED
Definition: class.ilLPStatus.php:23
ilLPStatus\LP_STATUS_IN_PROGRESS
const LP_STATUS_IN_PROGRESS
Definition: class.ilLPStatus.php:24
ilMemberAgreement\lookupAcceptedAgreements
static lookupAcceptedAgreements($a_obj_id)
Lookup users who have accepted the agreement.
Definition: class.ilMemberAgreement.php:160
ilObjExercise
Class ilObjExercise.
Definition: class.ilObjExercise.php:20
ilObjSAHSLearningModule\_lookupSubType
_lookupSubType($a_obj_id)
lookup subtype id (scorm, aicc, hacp)
Definition: class.ilObjSAHSLearningModule.php:157
ilObjSCORM2004LearningModule
Class ilObjSCORM2004LearningModule.
Definition: class.ilObjSCORM2004LearningModule.php:16
ilObjSCORMLearningModule
Class ilObjSCORMLearningModule.
Definition: class.ilObjSCORMLearningModule.php:18
ilObjSCORMTracking\_getTrackedUsers
_getTrackedUsers($a_obj_id)
Get all tracked users.
Definition: class.ilObjSCORMTracking.php:621
ilObjectLP\getInstance
static getInstance($a_obj_id)
Definition: class.ilObjectLP.php:25
ilObject\_lookupObjId
static _lookupObjId($a_id)
Definition: class.ilObject.php:1028
ilObject\_lookupObjectId
static _lookupObjectId($a_ref_id)
lookup object id
Definition: class.ilObject.php:1226
ilObject\_getAllReferences
static _getAllReferences($a_id)
get all reference ids of object
Definition: class.ilObject.php:909
ilObject\_lookupType
static _lookupType($a_id, $a_reference=false)
lookup object type
Definition: class.ilObject.php:1183
ilTrQuery
Tracking query class.
Definition: class.ilTrQuery.php:13
ilTrQuery\getObjectDailyStatistics
static getObjectDailyStatistics(array $a_ref_ids, $a_year, $a_month=null)
Definition: class.ilTrQuery.php:1709
ilTrQuery\getSummaryDataForObject
static getSummaryDataForObject($a_ref_id, array $fields, array $a_filters=NULL)
Get all aggregated tracking data for object.
Definition: class.ilTrQuery.php:722
ilTrQuery\getUDFAndHandlePrivacy
static getUDFAndHandlePrivacy(array &$a_result, array $a_udf=null, $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:337
ilTrQuery\buildColumns
static buildColumns(array &$a_fields, array $a_additional_fields=NULL, $a_aggregate=false)
Build sql from field definition.
Definition: class.ilTrQuery.php:1145
ilTrQuery\getUserObjectMatrix
static getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter=NULL, array $a_additional_fields=null, array $a_privacy_fields=null, $a_check_agreement=null)
Get status matrix for users on objects.
Definition: class.ilTrQuery.php:1477
ilTrQuery\getObjectivesStatusForUser
getObjectivesStatusForUser($a_user_id, array $a_objective_ids)
Definition: class.ilTrQuery.php:91
ilTrQuery\getUserObjectiveMatrix
static getUserObjectiveMatrix($a_parent_obj_id, $a_users)
Definition: class.ilTrQuery.php:1571
ilTrQuery\getObjectIds
static getObjectIds($a_parent_obj_id, $a_parent_ref_id=false, $use_collection=true, $a_refresh_status=true, $a_user_ids=null)
Get (sub)objects for given object, also handles learning objectives (course only)
Definition: class.ilTrQuery.php:1255
ilTrQuery\getObjectsDataForUser
static getObjectsDataForUser($a_user_id, $a_parent_obj_id, $a_parent_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=NULL, array $a_additional_fields=NULL, $use_collection=true)
Get all object-based tracking data for user and parent object.
Definition: class.ilTrQuery.php:442
ilTrQuery\getObjectsSummaryForObject
static getObjectsSummaryForObject($a_parent_obj_id, $a_parent_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=NULL, array $a_additional_fields=NULL, $a_preselected_obj_ids=NULL)
Get all aggregated tracking data for parent object.
Definition: class.ilTrQuery.php:651
ilTrQuery\getObjectStatisticsMonthlySummary
static getObjectStatisticsMonthlySummary()
Definition: class.ilTrQuery.php:1737
ilTrQuery\refreshObjectsStatus
static refreshObjectsStatus(array $a_obj_ids, $a_users=null)
check whether status (for all relevant users) exists
Definition: class.ilTrQuery.php:1854
ilTrQuery\getObjectAccessStatistics
static getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month=null)
Definition: class.ilTrQuery.php:1618
ilTrQuery\getSessionData
static getSessionData($a_user_id, array $obj_ids)
Get session data for given objects and user.
Definition: class.ilTrQuery.php:601
ilTrQuery\getObjectLPStatistics
static getObjectLPStatistics(array $a_obj_ids, $a_year, $a_month=null, $a_group_by_day=false)
Definition: class.ilTrQuery.php:1877
ilTrQuery\searchObjects
static searchObjects($a_type, $a_title=null, $a_root=null, $a_hidden=null, $a_preset_obj_ids=null)
Definition: class.ilTrQuery.php:1785
ilTrQuery\getParticipantsForObject
static getParticipantsForObject($a_ref_id)
Get participant ids for given object.
Definition: class.ilTrQuery.php:843
ilTrQuery\getObjectTypeStatisticsPerMonth
getObjectTypeStatisticsPerMonth($a_aggregation, $a_year=null)
Definition: class.ilTrQuery.php:1914
ilTrQuery\getObjectStatisticsLogInfo
static getObjectStatisticsLogInfo()
Get last update info for object statistics.
Definition: class.ilTrQuery.php:1868
ilTrQuery\getSubTree
static getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
Get complete branch of tree (recursively)
Definition: class.ilTrQuery.php:1344
ilTrQuery\getSubItemsStatusForUser
getSubItemsStatusForUser($a_user_id, $a_parent_obj_id, array $a_item_ids)
Definition: class.ilTrQuery.php:190
ilTrQuery\getSCOsStatusForUser
getSCOsStatusForUser($a_user_id, $a_parent_obj_id, array $a_sco_ids)
Definition: class.ilTrQuery.php:121
ilTrQuery\executeQueries
static executeQueries(array $queries, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999)
Execute given queries, including count query.
Definition: class.ilTrQuery.php:1398
ilTrQuery\buildFilters
static buildFilters(array $where, array $a_filters=NULL, $a_aggregate=false)
Build sql from filter definition.
Definition: class.ilTrQuery.php:938
ilTrQuery\getUserDataForObject
static getUserDataForObject($a_ref_id, $a_order_field="", $a_order_dir="", $a_offset=0, $a_limit=9999, array $a_filters=NULL, array $a_additional_fields=NULL, $check_agreement=false, $privacy_fields=NULL)
Get all user-based tracking data for object.
Definition: class.ilTrQuery.php:260
ilTrQuery\getObjectsStatusForUser
getObjectsStatusForUser($a_user_id, array $obj_refs)
Definition: class.ilTrQuery.php:14
ilTrQuery\getSummaryPercentages
static getSummaryPercentages($field, $base_query, $alias=NULL)
Get aggregated data for field.
Definition: class.ilTrQuery.php:805
ilTrQuery\deleteObjectStatistics
static deleteObjectStatistics(array $a_months)
Definition: class.ilTrQuery.php:1754
ilTree
Tree class data representation in hierachical trees using the Nested Set Model with Gaps by Joe Celco...
Definition: class.ilTree.php:25
ilUtil\stableSortArray
static stableSortArray($array, $a_array_sortby, $a_array_sortorder=0, $a_numeric=false)
Definition: class.ilUtil.php:3442
$valid
$valid
Definition: dummy_client.php:53
$ref_id
$ref_id
Definition: sahs_server.php:39
$path
$path
Definition: index.php:22
$ilDB
global $ilDB
Definition: storeScorm2004.php:19