ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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, $a_obj_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_obj_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 case "mcst":
198 include_once './Services/Object/classes/class.ilObjectLP.php';
199 $olp = ilObjectLP::getInstance($a_parent_obj_id);
200 $collection = $olp->getCollectionInstance();
201 if($collection)
202 {
203 $ref_ids = ilObject::_getAllReferences($a_parent_obj_id);
204 $ref_id = end($ref_ids);
205 $item_data = $collection->getPossibleItems($ref_id);
206 }
207 break;
208
209 default:
210 return array();
211 }
212
213 include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
214 $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
215
216 $items = array();
217 foreach($a_item_ids as $item_id)
218 {
219 if(!isset($item_data[$item_id]))
220 {
221 continue;
222 }
223
224 if(in_array($a_user_id, $status_info["completed"][$item_id]))
225 {
226 $status = ilLPStatus::LP_STATUS_COMPLETED;
227 }
228 elseif(in_array($a_user_id, $status_info["in_progress"][$item_id]))
229 {
230 $status = ilLPStatus::LP_STATUS_IN_PROGRESS;
231 }
232 else
233 {
234 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED;
235 }
236
237 $items[$item_id] = array(
238 "title" => $item_data[$item_id]["title"],
239 "status" => $status,
240 "type" => self::getSubItemType($a_parent_obj_id)
241 );
242 }
243
244 return $items;
245 }
246
261 static function getUserDataForObject($a_ref_id, $a_order_field = "", $a_order_dir = "",
262 $a_offset = 0, $a_limit = 9999, array $a_filters = NULL, array $a_additional_fields = NULL,
263 $check_agreement = false, $privacy_fields = NULL)
264 {
265 global $ilDB;
266
267 $fields = array("usr_data.usr_id", "login", "active");
268 $udf = self::buildColumns($fields, $a_additional_fields);
269
270 $where = array();
271 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
272
273 // users
274 $left = "";
275 $a_users = self::getParticipantsForObject($a_ref_id);
276
277 $obj_id = ilObject::_lookupObjectId($a_ref_id);
278 self::refreshObjectsStatus(array($obj_id), $a_users);
279
280 if (is_array($a_users))
281 {
282 $left = "LEFT";
283 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
284 }
285
286 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
287 " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
288 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
289 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
290 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
291 self::buildFilters($where, $a_filters);
292
293 $queries = array(array("fields"=>$fields, "query"=>$query));
294
295 // #9598 - if language is not in fields alias is missing
296 if($a_order_field == "language")
297 {
298 $a_order_field = "usr_pref.value";
299 }
300
301 // udf data is added later on, not in this query
302 $udf_order = null;
303 if(!$a_order_field)
304 {
305 $a_order_field = "login";
306 }
307 else if(substr($a_order_field, 0, 4) == "udf_")
308 {
309 $udf_order = $a_order_field;
310 $a_order_field = null;
311 }
312
313 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
314
315 self::getUDFAndHandlePrivacy($result, $udf, $check_agreement, $privacy_fields, $a_filters);
316
317 // as we cannot do this in the query, sort by custom field here
318 // this will not work with pagination!
319 if($udf_order)
320 {
321 include_once "Services/Utilities/classes/class.ilStr.php";
322 $result["set"] = ilUtil::stableSortArray($result["set"],
323 $udf_order, $a_order_dir);
324 }
325
326 return $result;
327 }
328
338 protected static function getUDFAndHandlePrivacy(array &$a_result, array $a_udf = null,
339 $a_check_agreement = null, array $a_privacy_fields = null, array $a_filters = null)
340 {
341 global $ilDB;
342
343 if(!$a_result["cnt"])
344 {
345 return;
346 }
347
348 if(sizeof($a_udf))
349 {
350 $query = "SELECT usr_id, field_id, value FROM udf_text WHERE ".$ilDB->in("field_id", $a_udf, false, "integer");
351 $set = $ilDB->query($query);
352 $udf = array();
353 while($row = $ilDB->fetchAssoc($set))
354 {
355 $udf[$row["usr_id"]]["udf_".$row["field_id"]] = $row["value"];
356 }
357 }
358
359 // (course/group) user agreement
360 if($a_check_agreement)
361 {
362 // admins/tutors (write-access) will never have agreement ?!
363 include_once "Services/Membership/classes/class.ilMemberAgreement.php";
364 $agreements = ilMemberAgreement::lookupAcceptedAgreements($a_check_agreement);
365
366 // public information for users
367 $query = "SELECT usr_id FROM usr_pref WHERE keyword = ".$ilDB->quote("public_profile", "text").
368 " AND value = ".$ilDB->quote("y", "text")." OR value = ".$ilDB->quote("g", "text");
369 $set = $ilDB->query($query);
370 $all_public = array();
371 while($row = $ilDB->fetchAssoc($set))
372 {
373 $all_public[] = $row["usr_id"];
374 }
375 $query = "SELECT usr_id,keyword FROM usr_pref WHERE ".$ilDB->like("keyword", "text", "public_%", false).
376 " AND value = ".$ilDB->quote("y", "text")." AND ".$ilDB->in("usr_id", $all_public, "", "integer");
377 $set = $ilDB->query($query);
378 $public = array();
379 while($row = $ilDB->fetchAssoc($set))
380 {
381 $public[$row["usr_id"]][] = substr($row["keyword"], 7);
382 }
383 unset($all_public);
384 }
385
386 foreach($a_result["set"] as $idx => $row)
387 {
388 // add udf data
389 if(isset($udf[$row["usr_id"]]))
390 {
391 $a_result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
392 }
393
394 // remove all private data - if active agreement and agreement not given by user
395 if(sizeof($a_privacy_fields) && $a_check_agreement && !in_array($row["usr_id"], $agreements))
396 {
397 foreach($a_privacy_fields as $field)
398 {
399 // check against public profile
400 if(isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
401 !in_array($field, $public[$row["usr_id"]])))
402 {
403 // remove complete entry - offending field was filtered
404 if(isset($a_filters[$field]))
405 {
406 // we cannot remove row because of pagination!
407 foreach(array_keys($row) as $col_id)
408 {
409 $a_result["set"][$idx][$col_id] = null;
410 }
411 $a_result["set"][$idx]["privacy_conflict"] = true;
412 // unset($a_result["set"][$idx]);
413 break;
414 }
415 // remove offending field
416 else
417 {
418 $a_result["set"][$idx][$field] = false;
419 }
420 }
421 }
422 }
423 }
424
425 // $a_result["cnt"] = sizeof($a_result["set"]);
426 }
427
443 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,
444 array $a_filters = NULL, array $a_additional_fields = NULL, $use_collection = true)
445 {
446 global $ilDB;
447
448 $fields = array("object_data.obj_id", "title", "type");
449 self::buildColumns($fields, $a_additional_fields);
450
451 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection, true, array($a_user_id));
452
453 $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND".
454 " read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
455 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer")." AND".
456 " ut_lp_marks.obj_id = object_data.obj_id)".
457 " WHERE ".$ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer").
458 self::buildFilters(array(), $a_filters);
459
460 $queries = array();
461 $queries[] = array("fields"=>$fields, "query"=>$query);
462
463 /* objectives data
464 if($objects["objectives_parent_id"])
465 {
466 $objective_fields = array("crs_objectives.objective_id AS obj_id", "title",
467 $ilDB->quote("lobj", "text")." as type");
468
469 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
470
471 if (is_array($a_additional_fields))
472 {
473 foreach($a_additional_fields as $field)
474 {
475 if($field != "status")
476 {
477 $objective_fields[] = "NULL AS ".$field;
478 }
479 else
480 {
481 // #15873 - see ilLOUserResults::getObjectiveStatusForLP()
482 include_once("Services/Tracking/classes/class.ilLPStatus.php");
483 $objective_fields[] = "CASE WHEN status = ".$ilDB->quote(ilLOUserResults::STATUS_COMPLETED, "integer").
484 " THEN ".ilLPStatus::LP_STATUS_COMPLETED_NUM.
485 " WHEN (status = ".$ilDB->quote(ilLOUserResults::STATUS_FAILED, "integer").
486 " AND is_final = ".$ilDB->quote(1, "integer").")".
487 " THEN ".ilLPStatus::LP_STATUS_FAILED_NUM.
488 " WHEN status = ".$ilDB->quote(ilLOUserResults::STATUS_FAILED, "integer").
489 " THEN ".ilLPStatus::LP_STATUS_IN_PROGRESS_NUM.
490 " ELSE NULL END AS status";
491 }
492 }
493 }
494
495 $where = array();
496 $where[] = "crs_objectives.crs_id = ".$ilDB->quote($objects["objectives_parent_id"], "integer");
497 $where[] = "crs_objectives.active = ".$ilDB->quote(1, "integer");
498
499 $objectives_query = " FROM crs_objectives".
500 " LEFT JOIN loc_user_results ON (crs_objectives.objective_id = loc_user_results.objective_id".
501 " AND loc_user_results.user_id = ".$ilDB->quote($a_user_id, "integer").
502 " AND loc_user_results.type = ".$ilDB->quote(ilLOUserResults::TYPE_QUALIFIED, "integer").")".
503 self::buildFilters($where, $a_filters);
504
505 $queries[] = array("fields"=>$objective_fields, "query"=>$objectives_query, "count"=>"crs_objectives.objective_id");
506 }
507 */
508
509 if(!in_array($a_order_field, $fields))
510 {
511 $a_order_field = "title";
512 }
513
514 $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
515 if($result["cnt"])
516 {
517 // session data
518 $sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
519
520 foreach($result["set"] as $idx => $item)
521 {
522 if($item["type"] == "sess")
523 {
524 $session = $sessions[$item["obj_id"]];
525 $result["set"][$idx]["title"] = $session["title"];
526 $result["set"][$idx]["sort_title"] = $session["e_start"];
527 // $result["set"][$idx]["status"] = (int)$session["status"];
528 }
529
530 $result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
531 }
532
533 // scos data (:TODO: will not be part of offset/limit)
534 if($objects["scorm"])
535 {
536 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
537 $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
538 if($subtype == "scorm2004")
539 {
540 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
541 $sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
542 $scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
543 }
544 else
545 {
546 include_once("./Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php");
547 $sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
548 $scos_tracking = array();
549 foreach($sobj->getTrackingDataAgg($a_user_id) as $item)
550 {
551 // format: hhhh:mm:ss ?!
552 if($item["time"])
553 {
554 $time = explode(":", $item["time"]);
555 $item["time"] = $time[0]*60*60+$time[1]*60+$time[2];
556 }
557 $scos_tracking[$item["sco_id"]] = array("session_time"=>$item["time"]);
558 }
559 }
560
561 foreach($objects["scorm"]["scos"] as $sco)
562 {
563 $row = array("title" => $objects["scorm"]["scos_title"][$sco],
564 "type" => "sco");
565
566 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
567 if(in_array($a_user_id, $objects["scorm"]["completed"][$sco]))
568 {
569 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
570 }
571 else if(in_array($a_user_id, $objects["scorm"]["failed"][$sco]))
572 {
573 $status = ilLPStatus::LP_STATUS_FAILED_NUM;
574 }
575 else if(in_array($a_user_id, $objects["scorm"]["in_progress"][$sco]))
576 {
577 $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
578 }
579 $row["status"] = $status;
580
581 // add available tracking data
582 if(isset($scos_tracking[$sco]))
583 {
584 if(isset($scos_tracking[$sco]["last_access"]))
585 {
586 $date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
587 $row["last_access"] = $date->get(IL_CAL_UNIX);
588 }
589 $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
590 }
591
592 $result["set"][] = $row;
593 $result["cnt"]++;
594 }
595 }
596
597 // #15379 - objectives data
598 if($objects["objectives_parent_id"])
599 {
600 include_once "Modules/Course/classes/class.ilCourseObjective.php";
601 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
602 $objtv_ids = ilCourseObjective::_getObjectiveIds($objects["objectives_parent_id"], true);
603 foreach(self::getObjectivesStatusForUser($a_user_id, $objects["objectives_parent_id"], $objtv_ids) as $item)
604 {
605 $result["set"][] = $item;
606 $result["cnt"]++;
607 }
608 }
609
610 // subitem data
611 if($objects["subitems"])
612 {
613 $sub_type = self::getSubItemType($a_parent_obj_id);
614 foreach($objects["subitems"]["items"] as $item_id)
615 {
616 $row = array("title" => $objects["subitems"]["item_titles"][$item_id],
617 "type" => $sub_type);
618
619 $status = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
620 if(in_array($a_user_id, $objects["subitems"]["completed"][$item_id]))
621 {
622 $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
623 }
624 $row["status"] = $status;
625
626 $result["set"][] = $row;
627 $result["cnt"]++;
628 }
629 }
630 }
631 return $result;
632 }
633
639 public static function getSubItemType($a_parent_obj_id)
640 {
641 switch(ilObject::_lookupType($a_parent_obj_id))
642 {
643 case "lm":
644 return "st";
645
646 case "mcst":
647 return "mob";
648 }
649 }
650
658 protected static function getSessionData($a_user_id, array $obj_ids)
659 {
660 global $ilDB;
661
662 $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,".
663 " mark, e_comment".
664 " FROM event".
665 " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)".
666 " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = ".$ilDB->quote($a_user_id, "integer").")".
667 " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
668 $set = $ilDB->query($query);
669 $sessions = array();
670 while($rec = $ilDB->fetchAssoc($set))
671 {
672 $rec["comment"] = $rec["e_comment"];
673 unset($rec["e_comment"]);
674
675 $date = ilDatePresentation::formatPeriod(
676 new ilDateTime($rec["e_start"], IL_CAL_DATETIME),
677 new ilDateTime($rec["e_end"], IL_CAL_DATETIME));
678
679 if($rec["title"])
680 {
681 $rec["title"] = $date.': '.$rec["title"];
682 }
683 else
684 {
685 $rec["title"] = $date;
686 }
687 $sessions[$rec["obj_id"]] = $rec;
688 }
689 return $sessions;
690 }
691
708 static function getObjectsSummaryForObject($a_parent_obj_id, $a_parent_ref_id, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999,
709 array $a_filters = NULL, array $a_additional_fields = NULL, $a_preselected_obj_ids = NULL)
710 {
711 global $ilDB;
712
713 $fields = array();
714 self::buildColumns($fields, $a_additional_fields, true);
715
716 $objects = array();
717 if($a_preselected_obj_ids === NULL)
718 {
719 $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
720 }
721 else
722 {
723 foreach($a_preselected_obj_ids as $obj_id => $ref_ids)
724 {
725 $objects["object_ids"][] = $obj_id;
726 $objects["ref_ids"][$obj_id] = array_pop($ref_ids);
727 }
728 }
729
730 $result = array();
731 if($objects)
732 {
733 // object data
734 $set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
735 " WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
736 while($rec = $ilDB->fetchAssoc($set))
737 {
738 $object_data[$rec["obj_id"]] = $rec;
739 if($a_preselected_obj_ids)
740 {
741 $object_data[$rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[$rec["obj_id"]];
742 }
743 else
744 {
745 $object_data[$rec["obj_id"]]["ref_ids"] = array($objects["ref_ids"][$rec["obj_id"]]);
746 }
747 }
748
749 foreach($objects["ref_ids"] as $object_id => $ref_id)
750 {
751 $object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
752 if(sizeof($object_result))
753 {
754 if($object_data[$object_id])
755 {
756 $result[] = array_merge($object_data[$object_id], $object_result);
757 }
758 }
759 }
760
761 // :TODO: objectives
762 if($objects["objectives_parent_id"])
763 {
764
765 }
766 }
767
768 return array("cnt"=>sizeof($result), "set"=>$result);
769 }
770
779 protected static function getSummaryDataForObject($a_ref_id, array $fields, array $a_filters = NULL)
780 {
781 global $ilDB;
782
783 $where = array();
784 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
785
786 // users
787 $a_users = self::getParticipantsForObject($a_ref_id);
788 $left = "";
789 if (is_array($a_users)) // #14840
790 {
791 $left = "LEFT";
792 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
793 }
794
795 $obj_id = ilObject::_lookupObjectId($a_ref_id);
796 self::refreshObjectsStatus(array($obj_id), $a_users);
797
798 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
799 " AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
800 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
801 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
802 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
803 self::buildFilters($where, $a_filters, true);
804
805 $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
806
807 $queries = array();
808 $queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
809
810 $result = self::executeQueries($queries);
811 $result = $result["set"][0];
812 $users_no = $result["user_count"];
813
814 $valid = true;
815 if(!$users_no)
816 {
817 $valid = false;
818 }
819 else if(isset($a_filters["user_total"]))
820 {
821 if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
822 {
823 $valid = false;
824 }
825 else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
826 {
827 $valid = false;
828 }
829 }
830
831 if($valid)
832 {
833 $result["country"] = self::getSummaryPercentages("country", $query);
834 $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
835 $result["city"] = self::getSummaryPercentages("city", $query);
836 $result["gender"] = self::getSummaryPercentages("gender", $query);
837 $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
838 $result["status"] = self::getSummaryPercentages("status", $query);
839 $result["mark"] = self::getSummaryPercentages("mark", $query);
840 }
841 else
842 {
843 $result = array();
844 }
845
846 if($result)
847 {
848 $result["user_total"] = $users_no;
849 }
850
851 return $result;
852 }
853
862 protected static function getSummaryPercentages($field, $base_query, $alias = NULL)
863 {
864 global $ilDB;
865
866 if(!$alias)
867 {
868 $field_alias = $field;
869 }
870 else
871 {
872 $field_alias = $alias;
873 $alias = " AS ".$alias;
874 }
875
876 // move having BEHIND group by
877 $having = "";
878 if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
879 {
880 $having = " HAVING ".$hits[1];
881 $base_query = str_replace($hits[0], "", $base_query);
882 }
883
884 $query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
885 $set = $ilDB->query($query);
886 $result = array();
887 while($rec = $ilDB->fetchAssoc($set))
888 {
889 $result[$rec[$field_alias]] = (int)$rec["counter"];
890 }
891 return $result;
892 }
893
900 public static function getParticipantsForObject($a_ref_id)
901 {
902 global $tree;
903
904 $obj_id = ilObject::_lookupObjectId($a_ref_id);
905 $obj_type = ilObject::_lookupType($obj_id);
906
907 // try to get participants from (parent) course/group
908 switch($obj_type)
909 {
910 case "crs":
911 include_once "Modules/Course/classes/class.ilCourseParticipants.php";
912 $member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
913 return $member_obj->getMembers();
914
915 case "grp":
916 include_once "Modules/Group/classes/class.ilGroupParticipants.php";
917 $member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
918 return $member_obj->getMembers();
919
920 default:
921 // walk path to find course or group object and use members of that object
922 $path = $tree->getPathId($a_ref_id);
923 array_pop($path);
924 foreach(array_reverse($path) as $path_ref_id)
925 {
926 $type = ilObject::_lookupType($path_ref_id, true);
927 if($type == "crs" || $type == "grp")
928 {
929 return self::getParticipantsForObject($path_ref_id);
930 }
931 }
932 break;
933 }
934
935 $a_users = null;
936
937 // no participants possible: use tracking/object data where possible
938 switch($obj_type)
939 {
940 case "sahs":
941 include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
942 $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
943 if ($subtype == "scorm2004")
944 {
945 // based on cmi_node/cp_node, used for scorm tracking data views
946 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
947 $mod = new ilObjSCORM2004LearningModule($obj_id, false);
948 $all = $mod->getTrackedUsers("");
949 if($all)
950 {
951 $a_users = array();
952 foreach($all as $item)
953 {
954 $a_users[] = $item["user_id"];
955 }
956 }
957 }
958 else
959 {
960 include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
961 $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
962 }
963 break;
964
965 case "exc":
966 include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
967 include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
968 $exc = new ilObjExercise($obj_id, false);
969 $members = new ilExerciseMembers($exc);
970 $a_users = $members->getMembers();
971 break;
972
973 case "tst":
974 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
975 $class = ilLPStatusFactory::_getClassById($obj_id, ilLPObjSettings::LP_MODE_TEST_FINISHED);
976 $a_users = $class::getParticipants($obj_id);
977 break;
978
979 case "svy":
980 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
981 $class = ilLPStatusFactory::_getClassById($obj_id, ilLPObjSettings::LP_MODE_SURVEY_FINISHED);
982 $a_users = $class::getParticipants($obj_id);
983 break;
984
985 case "prg":
986 include_once("Modules/StudyProgramme/classes/class.ilObjStudyProgramme.php");
987 $prg = new ilObjStudyProgramme($obj_id, false);
988 $a_users = $prg->getIdsOfUsersWithRelevantProgress();
989 break;
990
991 default:
992 // no sensible data: return null
993 break;
994 }
995
996 return $a_users;
997 }
998
1007 static protected function buildFilters(array $where, array $a_filters = NULL, $a_aggregate = false)
1008 {
1009 global $ilDB;
1010
1011 $having = array();
1012
1013 if(sizeof($a_filters))
1014 {
1015 foreach($a_filters as $id => $value)
1016 {
1017 switch($id)
1018 {
1019 case "login":
1020 case "firstname":
1021 case "lastname":
1022 case "institution":
1023 case "department":
1024 case "street":
1025 case "email":
1026 case "matriculation":
1027 case "country":
1028 case "city":
1029 case "title":
1030 $where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
1031 break;
1032
1033 case "gender":
1034 case "zipcode":
1035 case "sel_country":
1036 $where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
1037 break;
1038
1039 case "u_comment":
1040 $where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
1041 break;
1042
1043 case "status":
1044 if($value == ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM)
1045 {
1046 // #10645 - not_attempted is default
1047 $where[] = "(ut_lp_marks.status = ".$ilDB->quote(ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM ,"text").
1048 " OR ut_lp_marks.status IS NULL)";
1049 break;
1050 }
1051 // fallthrough
1052
1053 case "mark":
1054 $where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
1055 break;
1056
1057 case "percentage":
1058 if(!$a_aggregate)
1059 {
1060 if($value["from"])
1061 {
1062 $where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
1063 }
1064 if($value["to"])
1065 {
1066 $where[] = "(ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer").
1067 " OR ut_lp_marks.".$id." IS NULL)";
1068 }
1069 }
1070 else
1071 {
1072 if($value["from"])
1073 {
1074 $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
1075 }
1076 if($value["to"])
1077 {
1078 $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
1079 }
1080 }
1081 break;
1082
1083 case "language":
1084 $where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
1085 break;
1086
1087 // timestamp
1088 case "last_access":
1089 if($value["from"])
1090 {
1091 $value["from"] = substr($value["from"], 0, -2)."00";
1092 $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
1093 $value["from"] = $value["from"]->get(IL_CAL_UNIX);
1094 }
1095 if($value["to"])
1096 {
1097 if(strlen($value["to"]) == 19)
1098 {
1099 $value["to"] = substr($value["to"], 0, -2)."59"; // #14858
1100 }
1101 $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
1102 $value["to"] = $value["to"]->get(IL_CAL_UNIX);
1103 }
1104 // fallthrough
1105
1106 case 'status_changed':
1107 // fallthrough
1108
1109 case "registration":
1110 if($id == "registration")
1111 {
1112 $id = "create_date";
1113 }
1114 // fallthrough
1115
1116 case "create_date":
1117 case "first_access":
1118 case "birthday":
1119 if($value["from"])
1120 {
1121 $where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
1122 }
1123 if($value["to"])
1124 {
1125 if(strlen($value["to"]) == 19)
1126 {
1127 $value["to"] = substr($value["to"], 0, -2)."59"; // #14858
1128 }
1129 $where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
1130 }
1131 break;
1132
1133 case "read_count":
1134 if(!$a_aggregate)
1135 {
1136 if($value["from"])
1137 {
1138 $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1139 }
1140 if($value["to"])
1141 {
1142 $where[] = "((read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer").
1143 " OR (read_event.".$id."+read_event.childs_".$id.") IS NULL)";
1144 }
1145 }
1146 else
1147 {
1148 if($value["from"])
1149 {
1150 $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1151 }
1152 if($value["to"])
1153 {
1154 $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
1155 }
1156 }
1157 break;
1158
1159 case "spent_seconds":
1160 if(!$a_aggregate)
1161 {
1162 if($value["from"])
1163 {
1164 $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1165 }
1166 if($value["to"])
1167 {
1168 $where[] = "((read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer").
1169 " OR (read_event.".$id."+read_event.childs_".$id.") IS NULL)";
1170 }
1171 }
1172 else
1173 {
1174 if($value["from"])
1175 {
1176 $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
1177 }
1178 if($value["to"])
1179 {
1180 $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
1181 }
1182 }
1183 break;
1184
1185 default:
1186 // var_dump("unknown: ".$id);
1187 break;
1188 }
1189 }
1190 }
1191
1192 $sql = "";
1193 if(sizeof($where))
1194 {
1195 $sql .= " WHERE ".implode(" AND ", $where);
1196 }
1197 if(sizeof($having))
1198 {
1199 // ugly "having" hack because of summary view
1200 $sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
1201 }
1202
1203 return $sql;
1204 }
1205
1214 static protected function buildColumns(array &$a_fields, array $a_additional_fields = NULL, $a_aggregate = false)
1215 {
1216 if(sizeof($a_additional_fields))
1217 {
1218 $udf = NULL;
1219 foreach($a_additional_fields as $field)
1220 {
1221 if(substr($field, 0, 4) != "udf_")
1222 {
1223 $function = NULL;
1224 if($a_aggregate)
1225 {
1226 $pos = strrpos($field, "_");
1227 if($pos === false)
1228 {
1229 continue;
1230 }
1231 $function = strtoupper(substr($field, $pos+1));
1232 $field = substr($field, 0, $pos);
1233 if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
1234 {
1235 continue;
1236 }
1237 }
1238
1239 switch($field)
1240 {
1241 case "language":
1242 if($function)
1243 {
1244 $a_fields[] = $function."(value) ".$field."_".strtolower($function);
1245 }
1246 else
1247 {
1248 $a_fields[] = "value ".$field;
1249 }
1250 break;
1251
1252 case "read_count":
1253 case "spent_seconds":
1254 if(!$function)
1255 {
1256 $a_fields[] = "(".$field."+childs_".$field.") ".$field;
1257 }
1258 else
1259 {
1260 if($function == "AVG")
1261 {
1262 $a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) ".$field."_".strtolower($function);
1263 }
1264 else
1265 {
1266 $a_fields[] = $function."(".$field."+childs_".$field.") ".$field."_".strtolower($function);
1267 }
1268 }
1269 break;
1270
1271 case "read_count_spent_seconds":
1272 if($function == "AVG")
1273 {
1274 $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) ".$field."_".strtolower($function);
1275 }
1276 break;
1277
1278 default:
1279 if($function)
1280 {
1281 if($function == "AVG")
1282 {
1283 $a_fields[] = "ROUND(AVG(".$field."), 2) ".$field."_".strtolower($function);
1284 }
1285 else
1286 {
1287 $a_fields[] = $function."(".$field.") ".$field."_".strtolower($function);
1288 }
1289 }
1290 else
1291 {
1292 $a_fields[] = $field;
1293 }
1294 break;
1295 }
1296 }
1297 else
1298 {
1299 $udf[] = substr($field, 4);
1300 }
1301 }
1302
1303 // clean-up
1304 $a_fields = array_unique($a_fields);
1305 if(is_array($udf))
1306 {
1307 $udf = array_unique($udf);
1308 }
1309
1310 return $udf;
1311 }
1312 }
1313
1324 static public function getObjectIds($a_parent_obj_id, $a_parent_ref_id = false, $use_collection = true, $a_refresh_status = true, $a_user_ids = null)
1325 {
1326 include_once "Services/Object/classes/class.ilObjectLP.php";
1327
1328 $object_ids = array($a_parent_obj_id);
1329 $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1330 $objectives_parent_id = $scorm = $subitems = false;
1331
1332 $olp = ilObjectLP::getInstance($a_parent_obj_id);
1333 $mode = $olp->getCurrentMode();
1334 switch($mode)
1335 {
1336 // what about LP_MODE_SCORM_PACKAGE ?
1337 case ilLPObjSettings::LP_MODE_SCORM:
1338 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1339 $status_scorm = ilLPStatusFactory::_getInstance($a_parent_obj_id, ilLPObjSettings::LP_MODE_SCORM);
1340 $scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
1341 break;
1342
1343 case ilLPObjSettings::LP_MODE_OBJECTIVES:
1344 if(ilObject::_lookupType($a_parent_obj_id) == "crs")
1345 {
1346 $objectives_parent_id = $a_parent_obj_id;
1347 }
1348 break;
1349
1350 case ilLPObjSettings::LP_MODE_COLLECTION_MANUAL:
1351 case ilLPObjSettings::LP_MODE_COLLECTION_TLT:
1352 case ilLPObjSettings::LP_MODE_COLLECTION_MOBS:
1353 include_once "Services/Tracking/classes/class.ilLPStatusFactory.php";
1354 $status_coll_tlt = ilLPStatusFactory::_getInstance($a_parent_obj_id, $mode);
1355 $subitems = $status_coll_tlt->_getStatusInfo($a_parent_obj_id);
1356 break;
1357
1358 default:
1359 // lp collection
1360 if($use_collection)
1361 {
1362 $collection = $olp->getCollectionInstance();
1363 if($collection)
1364 {
1365 foreach($collection->getItems() as $child_ref_id)
1366 {
1367 $child_id = ilObject::_lookupObjId($child_ref_id);
1368 $object_ids[] = $child_id;
1369 $ref_ids[$child_id] = $child_ref_id;
1370 }
1371 }
1372 }
1373 // all objects in branch
1374 else
1375 {
1376 self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1377 $object_ids = array_unique($object_ids);
1378 }
1379
1380 foreach($object_ids as $idx => $object_id)
1381 {
1382 if(!$object_id)
1383 {
1384 unset($object_ids[$idx]);
1385 }
1386 }
1387 break;
1388 }
1389
1390 if($a_refresh_status)
1391 {
1392 self::refreshObjectsStatus($object_ids, $a_user_ids);
1393 }
1394
1395 return array("object_ids" => $object_ids,
1396 "ref_ids" => $ref_ids,
1397 "objectives_parent_id" => $objectives_parent_id,
1398 "scorm" => $scorm,
1399 "subitems" => $subitems);
1400 }
1401
1409 static protected function getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
1410 {
1411 global $tree;
1412
1413 $children = $tree->getChilds($a_parent_ref_id);
1414 if($children)
1415 {
1416 foreach($children as $child)
1417 {
1418 if($child["type"] == "adm" || $child["type"] == "rolf")
1419 {
1420 continue;
1421 }
1422
1423 // as there can be deactivated items in the collection
1424 // we should allow them here too
1425
1426 $olp = ilObjectLP::getInstance($child["obj_id"]);
1427 $cmode = $olp->getCurrentMode();
1428
1429 /* see ilPluginLP
1430 if($cmode == ilLPObjSettings::LP_MODE_PLUGIN)
1431 {
1432 // #11368
1433 include_once "Services/Repository/classes/class.ilRepositoryObjectPluginSlot.php";
1434 if(ilRepositoryObjectPluginSlot::isTypePluginWithLP($child["type"], false))
1435 {
1436 $a_object_ids[] = $child["obj_id"];
1437 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1438 }
1439 }
1440 */
1441
1442 if(/* $cmode != ilLPObjSettings::LP_MODE_DEACTIVATED && */ $cmode != ilLPObjSettings::LP_MODE_UNDEFINED)
1443 {
1444 $a_object_ids[] = $child["obj_id"];
1445 $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1446 }
1447
1448 self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1449 }
1450 }
1451 }
1452
1463 static function executeQueries(array $queries, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999)
1464 {
1465 global $ilDB;
1466
1467 $cnt = 0;
1468 $subqueries = array();
1469 foreach($queries as $item)
1470 {
1471 // ugly "having" hack because of summary view
1472 $item = str_replace("[[--HAVING", "HAVING", $item);
1473 $item = str_replace("HAVING--]]", "", $item);
1474
1475 if(!isset($item["count"]))
1476 {
1477 $count_field = $item["fields"];
1478 $count_field = array_shift($count_field);
1479 }
1480 else
1481 {
1482 $count_field = $item["count"];
1483 }
1484 $count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
1485 $set = $ilDB->query($count_query);
1486 if ($rec = $ilDB->fetchAssoc($set))
1487 {
1488 $cnt += $rec["cnt"];
1489 }
1490
1491 $subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
1492 }
1493
1494 // set query
1495 $result = array();
1496 if($cnt > 0)
1497 {
1498 if(sizeof($subqueries) > 1)
1499 {
1500 $base = array_shift($subqueries);
1501 $query = $base." UNION (".implode(") UNION (", $subqueries).")";
1502 }
1503 else
1504 {
1505 $query = $subqueries[0];
1506 }
1507
1508 if ($a_order_dir != "asc" && $a_order_dir != "desc")
1509 {
1510 $a_order_dir = "asc";
1511 }
1512 if($a_order_field)
1513 {
1514 $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
1515 }
1516
1517 $offset = (int) $a_offset;
1518 $limit = (int) $a_limit;
1519 $ilDB->setLimit($limit, $offset);
1520
1521 $set = $ilDB->query($query);
1522 while($rec = $ilDB->fetchAssoc($set))
1523 {
1524 $result[] = $rec;
1525 }
1526 }
1527
1528 return array("cnt" => $cnt, "set" => $result);
1529 }
1530
1542 static function getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter = NULL,
1543 array $a_additional_fields = null, array $a_privacy_fields = null, $a_check_agreement = null)
1544 {
1545 global $ilDB;
1546
1547 $result = array("cnt"=>0, "set"=>NULL);
1548 if(sizeof($a_obj_ids))
1549 {
1550 $where = array();
1551 $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
1552 if($a_user_filter)
1553 {
1554 $where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
1555 }
1556
1557 // users
1558 $left = "";
1559 $a_users = self::getParticipantsForObject($a_parent_ref_id);
1560 if (is_array($a_users))
1561 {
1562 $left = "LEFT";
1563 $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1564 }
1565
1566 $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1567 self::refreshObjectsStatus($a_obj_ids, $a_users);
1568
1569 $fields = array("usr_data.usr_id", "login", "active");
1570 $udf = self::buildColumns($fields, $a_additional_fields);
1571
1572 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1573
1574 // #18673 - if parent supports percentage does not matter for "sub-items"
1575 $fields[] = "percentage";
1576
1577 $raw = array();
1578 foreach($a_obj_ids as $obj_id)
1579 {
1580 // one request for each object
1581 $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
1582 " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1583 " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
1584 " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1585 " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
1586 self::buildFilters($where);
1587
1588 $raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), "login");
1589 if($raw["cnt"])
1590 {
1591 // convert to final structure
1592 foreach($raw["set"] as $row)
1593 {
1594 $result["set"][$row["usr_id"]]["login"] = $row["login"];
1595 $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1596
1597 // #14953
1598 $result["set"][$row["usr_id"]]["obj_".$obj_id] = $row["status"];
1599 $result["set"][$row["usr_id"]]["obj_".$obj_id."_perc"] = $row["percentage"];
1600
1601 if($obj_id == $parent_obj_id)
1602 {
1603 $result["set"][$row["usr_id"]]["status_changed"] = $row["status_changed"];
1604 $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1605 $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1606 $result["set"][$row["usr_id"]]["read_count"] = $row["read_count"];
1607 }
1608
1609 foreach($fields as $field)
1610 {
1611 // #14957 - value [as] language
1612 if(stristr($field, "language"))
1613 {
1614 $field = "language";
1615 }
1616
1617 if(isset($row[$field]))
1618 {
1619 // #14955
1620 if($obj_id == $parent_obj_id ||
1621 !in_array($field, array("mark", "u_comment")))
1622 {
1623 $result["set"][$row["usr_id"]][$field] = $row[$field];
1624 }
1625 }
1626 }
1627 }
1628 }
1629 }
1630
1631 $result["cnt"] = sizeof($result["set"]);
1632 $result["users"] = $a_users;
1633
1634 self::getUDFAndHandlePrivacy($result, $udf, $a_check_agreement, $a_privacy_fields, $a_additional_fields);
1635 }
1636 return $result;
1637 }
1638
1639 static public function getUserObjectiveMatrix($a_parent_obj_id, $a_users)
1640 {
1641 global $ilDB;
1642
1643 if($a_parent_obj_id && $a_users)
1644 {
1645 $res = array();
1646
1647 include_once "Services/Tracking/classes/class.ilLPStatus.php";
1648 include_once "Modules/Course/classes/Objectives/class.ilLOUserResults.php";
1649 include_once "Modules/Course/classes/class.ilCourseObjective.php";
1650 $objective_ids = ilCourseObjective::_getObjectiveIds($a_parent_obj_id,true);
1651
1652 // #17402 - are initital test(s) qualifying?
1653 include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
1654 $lo_set = ilLOSettings::getInstanceByObjId($a_parent_obj_id);
1655 $initial_qualifying = $lo_set->isInitialTestQualifying();
1656
1657 // there may be missing entries for any user / objective combination
1658 foreach($objective_ids as $objective_id)
1659 {
1660 foreach($a_users as $user_id)
1661 {
1662 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_NOT_ATTEMPTED_NUM;
1663 }
1664 }
1665
1666 $query = "SELECT * FROM loc_user_results".
1667 " WHERE ".$ilDB->in("objective_id", $objective_ids, "", "integer").
1668 " AND ".$ilDB->in("user_id", $a_users, "", "integer");
1669 if(!(bool)$initial_qualifying)
1670 {
1671 $query .= " AND type = ".$ilDB->quote(ilLOUserResults::TYPE_QUALIFIED, "integer");
1672 }
1673 $query .= " ORDER BY type"; // qualified must come last!
1674 $set = $ilDB->query($query);
1675 while($row = $ilDB->fetchAssoc($set))
1676 {
1677 $objective_id = $row["objective_id"];
1678 $user_id = $row["user_id"];
1679
1680 // if both initial and qualified, qualified will overwrite initial
1681
1682 // #15873 - see ilLOUserResults::getObjectiveStatusForLP()
1683 if($row["status"] == ilLOUserResults::STATUS_COMPLETED)
1684 {
1685 $res[$user_id][$objective_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
1686 }
1687 else if($row["status"] == ilLOUserResults::STATUS_FAILED)
1688 {
1689 $res[$user_id][$objective_id] = $row["is_final"]
1690 ? ilLPStatus::LP_STATUS_FAILED_NUM
1691 : ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
1692 }
1693 }
1694
1695 return $res;
1696 }
1697 }
1698
1699 static public function getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month = null)
1700 {
1701 global $ilDB;
1702
1703 $obj_ids = array_keys($a_ref_ids);
1704
1705 if($a_month)
1706 {
1707 $column = "dd";
1708 }
1709 else
1710 {
1711 $column = "mm";
1712 }
1713
1714 $res = array();
1715 $sql = "SELECT obj_id,".$column.",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1716 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1717 " FROM obj_stat".
1718 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1719 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1720 if($a_month)
1721 {
1722 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1723 }
1724 $sql .= " GROUP BY obj_id,".$column;
1725 $set = $ilDB->query($sql);
1726 while($row = $ilDB->fetchAssoc($set))
1727 {
1728 $row["read_count"] += $row["childs_read_count"];
1729 $row["spent_seconds"] += $row["childs_spent_seconds"];
1730 $res[$row["obj_id"]][$row[$column]]["read_count"] += $row["read_count"];
1731 $res[$row["obj_id"]][$row[$column]]["spent_seconds"] += $row["spent_seconds"];
1732 }
1733
1734
1735 // add user data
1736
1737 $sql = "SELECT obj_id,".$column.",SUM(counter) counter".
1738 " FROM obj_user_stat".
1739 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1740 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1741 if($a_month)
1742 {
1743 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1744 }
1745 $sql .= " GROUP BY obj_id,".$column;
1746 $set = $ilDB->query($sql);
1747 while($row = $ilDB->fetchAssoc($set))
1748 {
1749 $res[$row["obj_id"]][$row[$column]]["users"] += $row["counter"];
1750 }
1751
1752 return $res;
1753 }
1754
1755 function getObjectTypeStatistics()
1756 {
1757 global $ilDB, $objDefinition;
1758
1759 // re-use add new item selection (folder is not that important)
1760 $types = array_keys($objDefinition->getCreatableSubObjects("root", ilObjectDefinition::MODE_REPOSITORY));
1761
1762 include_once "Services/Tree/classes/class.ilTree.php";
1763 $tree = new ilTree(1);
1764 $sql = "SELECT ".$tree->table_obj_data.".obj_id,".$tree->table_obj_data.".type,".
1765 $tree->table_tree.".".$tree->tree_pk.",".$tree->table_obj_reference.".ref_id".
1766 " FROM ".$tree->table_tree.
1767 " ".$tree->buildJoin().
1768 " WHERE ".$ilDB->in($tree->table_obj_data.".type", $types, "", "text");
1769 $set = $ilDB->query($sql);
1770 $res = array();
1771 while($row = $ilDB->fetchAssoc($set))
1772 {
1773 $res[$row["type"]]["type"] = $row["type"];
1774 $res[$row["type"]]["references"]++;
1775 $res[$row["type"]]["objects"][] = $row["obj_id"];
1776 if($row[$tree->tree_pk] < 0)
1777 {
1778 $res[$row["type"]]["deleted"]++;
1779 }
1780 }
1781
1782 foreach($res as $type => $values)
1783 {
1784 $res[$type]["objects"] = sizeof(array_unique($values["objects"]));
1785 }
1786
1787 return $res;
1788 }
1789
1790 static public function getObjectDailyStatistics(array $a_ref_ids, $a_year, $a_month = null)
1791 {
1792 global $ilDB;
1793
1794 $obj_ids = array_keys($a_ref_ids);
1795
1796 $res = array();
1797 $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1798 "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1799 " FROM obj_stat".
1800 " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1801 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1802 if($a_month)
1803 {
1804 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1805 }
1806 $sql .= " GROUP BY obj_id,hh";
1807 $set = $ilDB->query($sql);
1808 while($row = $ilDB->fetchAssoc($set))
1809 {
1810 $row["read_count"] += $row["childs_read_count"];
1811 $row["spent_seconds"] += $row["childs_spent_seconds"];
1812 $res[$row["obj_id"]][(int)$row["hh"]]["read_count"] += $row["read_count"];
1813 $res[$row["obj_id"]][(int)$row["hh"]]["spent_seconds"] += $row["spent_seconds"];
1814 }
1815 return $res;
1816 }
1817
1818 static public function getObjectStatisticsMonthlySummary()
1819 {
1820 global $ilDB;
1821
1822 $set = $ilDB->query("SELECT COUNT(*) AS COUNTER,yyyy,mm".
1823 " FROM obj_stat".
1824 " GROUP BY yyyy, mm".
1825 " ORDER BY yyyy DESC, mm DESC");
1826 $res = array();
1827 while($row = $ilDB->fetchAssoc($set))
1828 {
1829 $res[] = array("month"=>$row["yyyy"]."-".$row["mm"],
1830 "count"=>$row["counter"]);
1831 }
1832 return $res;
1833 }
1834
1835 static public function deleteObjectStatistics(array $a_months)
1836 {
1837 global $ilDB;
1838
1839 // no combined column, have to concat
1840 $date_compare = $ilDB->in($ilDB->concat(array(array("yyyy", ""),
1841 array($ilDB->quote("-", "text"), ""),
1842 array("mm", ""))), $a_months, "", "text");
1843 $sql = "DELETE FROM obj_stat".
1844 " WHERE ".$date_compare;
1845 $ilDB->manipulate($sql);
1846
1847 // fulldate == YYYYMMDD
1848 $tables = array("obj_lp_stat", "obj_type_stat", "obj_user_stat");
1849 foreach($a_months as $month)
1850 {
1851 $year = substr($month, 0, 4);
1852 $month = substr($month, 5);
1853 $from = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."01";
1854 $to = $year.str_pad($month, 2, "0", STR_PAD_LEFT)."31";
1855
1856 foreach($tables as $table)
1857 {
1858 $sql = "DELETE FROM ".$table.
1859 " WHERE fulldate >= ".$ilDB->quote($from, "integer").
1860 " AND fulldate <= ".$ilDB->quote($to, "integer");
1861 $ilDB->manipulate($sql);
1862 }
1863 }
1864 }
1865
1866 static public function searchObjects($a_type, $a_title = null, $a_root = null, $a_hidden = null, $a_preset_obj_ids = null)
1867 {
1868 global $ilDB, $tree;
1869
1870 if($a_type == "lres")
1871 {
1872 $a_type = array('lm','sahs','htlm','dbk');
1873 }
1874
1875 $sql = "SELECT r.ref_id,r.obj_id".
1876 " FROM object_data o".
1877 " JOIN object_reference r ON (o.obj_id = r.obj_id)".
1878 " JOIN tree t ON (t.child = r.ref_id)".
1879 " WHERE t.tree = ".$ilDB->quote(1, "integer");
1880
1881 if(!is_array($a_type))
1882 {
1883 $sql .= " AND o.type = ".$ilDB->quote($a_type, "text");
1884 }
1885 else
1886 {
1887 $sql .= " AND ".$ilDB->in("o.type", $a_type, "", "text");
1888 }
1889
1890 if($a_title)
1891 {
1892 $sql .= " AND (".$ilDB->like("o.title", "text", "%".$a_title."%").
1893 " OR ".$ilDB->like("o.description", "text", "%".$a_title."%").")";
1894 }
1895
1896 if(is_array($a_hidden))
1897 {
1898 $sql .= " AND ".$ilDB->in("o.obj_id", $a_hidden, true, "integer");
1899 }
1900
1901 if(is_array($a_preset_obj_ids))
1902 {
1903 $sql .= " AND ".$ilDB->in("o.obj_id", $a_preset_obj_ids, false, "integer");
1904 }
1905
1906 $set = $ilDB->query($sql);
1907 $res = array();
1908 while($row = $ilDB->fetchAssoc($set))
1909 {
1910 if($a_root && $a_root != ROOT_FOLDER_ID)
1911 {
1912 foreach(ilObject::_getAllReferences($row['obj_id']) as $ref_id)
1913 {
1914 if($tree->isGrandChild($a_root, $ref_id))
1915 {
1916 $res[$row["obj_id"]][] = $row["ref_id"];
1917 continue;
1918 }
1919 }
1920 }
1921 else
1922 {
1923 $res[$row["obj_id"]][] = $row["ref_id"];
1924 }
1925 }
1926 return $res;
1927 }
1928
1935 protected static function refreshObjectsStatus(array $a_obj_ids, $a_users = null)
1936 {
1937 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1938 foreach($a_obj_ids as $obj_id)
1939 {
1940 ilLPStatus::checkStatusForObject($obj_id, $a_users);
1941 }
1942 }
1943
1949 public static function getObjectStatisticsLogInfo()
1950 {
1951 global $ilDB;
1952
1953 $set = $ilDB->query("SELECT COUNT(*) counter, MIN(tstamp) tstamp".
1954 " FROM obj_stat_log");
1955 return $ilDB->fetchAssoc($set);
1956 }
1957
1958 static public function getObjectLPStatistics(array $a_obj_ids, $a_year, $a_month = null, $a_group_by_day = false)
1959 {
1960 global $ilDB;
1961
1962 if($a_group_by_day)
1963 {
1964 $column = "dd";
1965 }
1966 else
1967 {
1968 $column = "mm,yyyy";
1969 }
1970
1971 $res = array();
1972 $sql = "SELECT obj_id,".$column.",".
1973 "MIN(mem_cnt) mem_cnt_min,AVG(mem_cnt) mem_cnt_avg, MAX(mem_cnt) mem_cnt_max,".
1974 "MIN(in_progress) in_progress_min,AVG(in_progress) in_progress_avg,MAX(in_progress) in_progress_max,".
1975 "MIN(completed) completed_min,AVG(completed) completed_avg,MAX(completed) completed_max,".
1976 "MIN(failed) failed_min,AVG(failed) failed_avg,MAX(failed) failed_max,".
1977 "MIN(not_attempted) not_attempted_min,AVG(not_attempted) not_attempted_avg,MAX(not_attempted) not_attempted_max".
1978 " FROM obj_lp_stat".
1979 " WHERE ".$ilDB->in("obj_id", $a_obj_ids, "", "integer").
1980 " AND yyyy = ".$ilDB->quote($a_year, "integer");
1981 if($a_month)
1982 {
1983 $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1984 }
1985 $sql .= " GROUP BY obj_id,".$column;
1986 $set = $ilDB->query($sql);
1987 while($row = $ilDB->fetchAssoc($set))
1988 {
1989 $res[] = $row;
1990 }
1991
1992 return $res;
1993 }
1994
1995 function getObjectTypeStatisticsPerMonth($a_aggregation, $a_year = null)
1996 {
1997 global $ilDB;
1998
1999 if(!$a_year)
2000 {
2001 $a_year = date("Y");
2002 }
2003
2004 $agg = strtoupper($a_aggregation);
2005
2006 $res = array();
2007 $sql = "SELECT type,yyyy,mm,".$agg."(cnt_objects) cnt_objects,".$agg."(cnt_references) cnt_references,".
2008 "".$agg."(cnt_deleted) cnt_deleted FROM obj_type_stat".
2009 " WHERE yyyy = ".$ilDB->quote($a_year, "integer").
2010 " GROUP BY type,yyyy,mm";
2011 $set = $ilDB->query($sql);
2012 while($row = $ilDB->fetchAssoc($set))
2013 {
2014 $row["mm"] = str_pad($row["mm"], 2, "0", STR_PAD_LEFT);
2015 $res[$row["type"]][$row["yyyy"]."-".$row["mm"]] = array(
2016 "objects" => (int)$row["cnt_objects"],
2017 "references" => (int)$row["cnt_references"],
2018 "deleted" => (int)$row["cnt_deleted"]
2019 );
2020 }
2021
2022 return $res;
2023 }
2024}
2025
2026?>
$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:518
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
ilLOSettings\getInstanceByObjId
static getInstanceByObjId($a_obj_id)
get singleton instance
Definition: class.ilLOSettings.php:82
ilLOUserResults\getObjectiveStatusForLP
static getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
Definition: class.ilLOUserResults.php:354
ilLPStatusFactory\_getClassById
_getClassById($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:23
ilLPStatusFactory\_getInstance
_getInstance($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:92
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:240
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:158
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:588
ilObjStudyProgramme
Class ilObjStudyProgramme.
Definition: class.ilObjStudyProgramme.php:18
ilObjectLP\getInstance
static getInstance($a_obj_id)
Definition: class.ilObjectLP.php:25
ilObject\_lookupObjId
static _lookupObjId($a_id)
Definition: class.ilObject.php:1041
ilObject\_lookupObjectId
static _lookupObjectId($a_ref_id)
lookup object id
Definition: class.ilObject.php:1239
ilObject\_getAllReferences
static _getAllReferences($a_id)
get all reference ids of object
Definition: class.ilObject.php:922
ilObject\_lookupType
static _lookupType($a_id, $a_reference=false)
lookup object type
Definition: class.ilObject.php:1196
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:1790
ilTrQuery\getSummaryDataForObject
static getSummaryDataForObject($a_ref_id, array $fields, array $a_filters=NULL)
Get all aggregated tracking data for object.
Definition: class.ilTrQuery.php:779
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:338
ilTrQuery\buildColumns
static buildColumns(array &$a_fields, array $a_additional_fields=NULL, $a_aggregate=false)
Build sql from field definition.
Definition: class.ilTrQuery.php:1214
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:1542
ilTrQuery\getUserObjectiveMatrix
static getUserObjectiveMatrix($a_parent_obj_id, $a_users)
Definition: class.ilTrQuery.php:1639
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:1324
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:443
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:708
ilTrQuery\getObjectStatisticsMonthlySummary
static getObjectStatisticsMonthlySummary()
Definition: class.ilTrQuery.php:1818
ilTrQuery\refreshObjectsStatus
static refreshObjectsStatus(array $a_obj_ids, $a_users=null)
check whether status (for all relevant users) exists
Definition: class.ilTrQuery.php:1935
ilTrQuery\getObjectAccessStatistics
static getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month=null)
Definition: class.ilTrQuery.php:1699
ilTrQuery\getSessionData
static getSessionData($a_user_id, array $obj_ids)
Get session data for given objects and user.
Definition: class.ilTrQuery.php:658
ilTrQuery\getObjectLPStatistics
static getObjectLPStatistics(array $a_obj_ids, $a_year, $a_month=null, $a_group_by_day=false)
Definition: class.ilTrQuery.php:1958
ilTrQuery\searchObjects
static searchObjects($a_type, $a_title=null, $a_root=null, $a_hidden=null, $a_preset_obj_ids=null)
Definition: class.ilTrQuery.php:1866
ilTrQuery\getParticipantsForObject
static getParticipantsForObject($a_ref_id)
Get participant ids for given object.
Definition: class.ilTrQuery.php:900
ilTrQuery\getObjectTypeStatisticsPerMonth
getObjectTypeStatisticsPerMonth($a_aggregation, $a_year=null)
Definition: class.ilTrQuery.php:1995
ilTrQuery\getObjectStatisticsLogInfo
static getObjectStatisticsLogInfo()
Get last update info for object statistics.
Definition: class.ilTrQuery.php:1949
ilTrQuery\getSubItemType
static getSubItemType($a_parent_obj_id)
Get sub-item object type for parent.
Definition: class.ilTrQuery.php:639
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:1409
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:1463
ilTrQuery\buildFilters
static buildFilters(array $where, array $a_filters=NULL, $a_aggregate=false)
Build sql from filter definition.
Definition: class.ilTrQuery.php:1007
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:261
ilTrQuery\getObjectsStatusForUser
getObjectsStatusForUser($a_user_id, array $obj_refs)
Definition: class.ilTrQuery.php:14
ilTrQuery\getObjectivesStatusForUser
getObjectivesStatusForUser($a_user_id, $a_obj_id, array $a_objective_ids)
Definition: class.ilTrQuery.php:91
ilTrQuery\getSummaryPercentages
static getSummaryPercentages($field, $base_query, $alias=NULL)
Get aggregated data for field.
Definition: class.ilTrQuery.php:862
ilTrQuery\deleteObjectStatistics
static deleteObjectStatistics(array $a_months)
Definition: class.ilTrQuery.php:1835
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:3460
$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