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 
12 class 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 ?>
ilLPStatus\LP_STATUS_COMPLETED_NUM
const LP_STATUS_COMPLETED_NUM
Definition: class.ilLPStatus.php:30
ilLOUserResults\getObjectiveStatusForLP
static getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
Definition: class.ilLOUserResults.php:354
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\getSCOsStatusForUser
getSCOsStatusForUser($a_user_id, $a_parent_obj_id, array $a_sco_ids)
Definition: class.ilTrQuery.php:121
ilLOSettings\getInstanceByObjId
static getInstanceByObjId($a_obj_id)
get singleton instance
Definition: class.ilLOSettings.php:82
ilTrQuery\getObjectLPStatistics
static getObjectLPStatistics(array $a_obj_ids, $a_year, $a_month=null, $a_group_by_day=false)
Definition: class.ilTrQuery.php:1958
IL_CRS_VIEW_OBJECTIVE
const IL_CRS_VIEW_OBJECTIVE
Definition: class.ilCourseConstants.php:39
IL_CAL_DATETIME
const IL_CAL_DATETIME
Definition: class.ilDateTime.php:9
ilExerciseMembers
Class ilExerciseMembers.
Definition: class.ilExerciseMembers.php:12
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
$result
$result
Definition: CleanUpTest.php:407
ilTrQuery\buildFilters
static buildFilters(array $where, array $a_filters=NULL, $a_aggregate=false)
Build sql from filter definition.
Definition: class.ilTrQuery.php:1007
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
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
ilTrQuery\getObjectStatisticsMonthlySummary
static getObjectStatisticsMonthlySummary()
Definition: class.ilTrQuery.php:1818
ilTrQuery\getSummaryPercentages
static getSummaryPercentages($field, $base_query, $alias=NULL)
Get aggregated data for field.
Definition: class.ilTrQuery.php:862
ilLPStatus\LP_STATUS_NOT_ATTEMPTED
const LP_STATUS_NOT_ATTEMPTED
Definition: class.ilLPStatus.php:23
$valid
$valid
Definition: dummy_client.php:53
ilDatePresentation\formatPeriod
static formatPeriod(ilDateTime $start, ilDateTime $end)
Format a period of two date Shows: 14.
Definition: class.ilDatePresentation.php:182
ilTrQuery\getSubItemType
static getSubItemType($a_parent_obj_id)
Get sub-item object type for parent.
Definition: class.ilTrQuery.php:639
ilTrQuery\deleteObjectStatistics
static deleteObjectStatistics(array $a_months)
Definition: class.ilTrQuery.php:1835
ilCourseParticipants\_getInstanceByObjId
static _getInstanceByObjId($a_obj_id)
Get singleton instance.
Definition: class.ilCourseParticipants.php:77
ilLPStatus\LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_IN_PROGRESS_NUM
Definition: class.ilLPStatus.php:29
ilCourseObjective\_getObjectiveIds
static _getObjectiveIds($course_id, $a_activated_only=false)
Definition: class.ilCourseObjective.php:518
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
IL_CAL_UNIX
const IL_CAL_UNIX
Definition: class.ilDateTime.php:11
ilObject\_getAllReferences
static _getAllReferences($a_id)
get all reference ids of object
Definition: class.ilObject.php:922
ilLPStatus\LP_STATUS_IN_PROGRESS
const LP_STATUS_IN_PROGRESS
Definition: class.ilLPStatus.php:24
ilLPStatusWrapper\_getStatusInfo
_getStatusInfo($a_obj_id)
Reads informations about the object e.g test results, tlt, number of visits.
Definition: class.ilLPStatusWrapper.php:128
ilObject\_lookupObjectId
static _lookupObjectId($a_ref_id)
lookup object id
Definition: class.ilObject.php:1239
ilTrQuery
Tracking query class.
Definition: class.ilTrQuery.php:12
ilTrQuery\getObjectDailyStatistics
static getObjectDailyStatistics(array $a_ref_ids, $a_year, $a_month=null)
Definition: class.ilTrQuery.php:1790
ilObjExercise
Class ilObjExercise.
Definition: class.ilObjExercise.php:19
ilLPStatus\LP_STATUS_FAILED
const LP_STATUS_FAILED
Definition: class.ilLPStatus.php:26
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
ilLPStatusFactory\_getInstance
_getInstance($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:92
ilUtil\stableSortArray
static stableSortArray($array, $a_array_sortby, $a_array_sortorder=0, $a_numeric=false)
Sort an aray using a stable sort algorithm, which preveserves the sequence of array elements which h...
Definition: class.ilUtil.php:3460
ilTrQuery\getObjectTypeStatisticsPerMonth
getObjectTypeStatisticsPerMonth($a_aggregation, $a_year=null)
Definition: class.ilTrQuery.php:1995
ilTrQuery\getObjectAccessStatistics
static getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month=null)
Definition: class.ilTrQuery.php:1699
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
ilObjStudyProgramme
Class ilObjStudyProgramme.
Definition: class.ilObjStudyProgramme.php:18
ilObject\_lookupObjId
static _lookupObjId($a_id)
Definition: class.ilObject.php:1041
ilObjSAHSLearningModule\_lookupSubType
_lookupSubType($a_obj_id)
lookup subtype id (scorm, aicc, hacp)
Definition: class.ilObjSAHSLearningModule.php:158
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
ilDateTime
Date and time handling
Definition: class.ilDateTime.php:32
ilTrQuery\getObjectivesStatusForUser
getObjectivesStatusForUser($a_user_id, $a_obj_id, array $a_objective_ids)
Definition: class.ilTrQuery.php:91
ilTrQuery\getSessionData
static getSessionData($a_user_id, array $obj_ids)
Get session data for given objects and user.
Definition: class.ilTrQuery.php:658
ilTree
Tree class data representation in hierachical trees using the Nested Set Model with Gaps by Joe Celco...
Definition: class.ilTree.php:24
ilGroupParticipants\_getInstanceByObjId
static _getInstanceByObjId($a_obj_id)
Get singleton instance.
Definition: class.ilGroupParticipants.php:63
ilObject\_lookupType
static _lookupType($a_id, $a_reference=false)
lookup object type
Definition: class.ilObject.php:1196
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\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\getUserObjectiveMatrix
static getUserObjectiveMatrix($a_parent_obj_id, $a_users)
Definition: class.ilTrQuery.php:1639
ilLPStatus\LP_STATUS_NOT_ATTEMPTED_NUM
const LP_STATUS_NOT_ATTEMPTED_NUM
Definition: class.ilLPStatus.php:28
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\getParticipantsForObject
static getParticipantsForObject($a_ref_id)
Get participant ids for given object.
Definition: class.ilTrQuery.php:900
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
$ref_id
$ref_id
Definition: sahs_server.php:39
ilObjSCORM2004LearningModule
Class ilObjSCORM2004LearningModule.
Definition: class.ilObjSCORM2004LearningModule.php:15
$path
$path
Definition: index.php:22
$ilDB
global $ilDB
Definition: storeScorm2004.php:16
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\getObjectsStatusForUser
getObjectsStatusForUser($a_user_id, array $obj_refs)
Definition: class.ilTrQuery.php:14
ilObjSCORMTracking\_getTrackedUsers
_getTrackedUsers($a_obj_id)
Get all tracked users.
Definition: class.ilObjSCORMTracking.php:588
ilLPStatus\LP_STATUS_COMPLETED
const LP_STATUS_COMPLETED
Definition: class.ilLPStatus.php:25
ilObjectLP\getInstance
static getInstance($a_obj_id)
Definition: class.ilObjectLP.php:25
ilTrQuery\getSubItemsStatusForUser
getSubItemsStatusForUser($a_user_id, $a_parent_obj_id, array $a_item_ids)
Definition: class.ilTrQuery.php:190
ilObjSCORMLearningModule
Class ilObjSCORMLearningModule.
Definition: class.ilObjSCORMLearningModule.php:17
ilMemberAgreement\lookupAcceptedAgreements
static lookupAcceptedAgreements($a_obj_id)
Lookup users who have accepted the agreement.
Definition: class.ilMemberAgreement.php:160
ilTrQuery\getObjectStatisticsLogInfo
static getObjectStatisticsLogInfo()
Get last update info for object statistics.
Definition: class.ilTrQuery.php:1949
ilLPStatus\LP_STATUS_FAILED_NUM
const LP_STATUS_FAILED_NUM
Definition: class.ilLPStatus.php:31
ilLPStatusFactory\_getClassById
_getClassById($a_obj_id, $a_mode=NULL)
Definition: class.ilLPStatusFactory.php:23