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