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