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