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