ILIAS  Release_4_2_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  include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
118  $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
119 
120  $items = array();
121  foreach($a_sco_ids as $sco_id)
122  {
123  // #9719 - can have in_progress AND failed/completed
124  if(in_array($a_user_id, $status_info["failed"][$sco_id]))
125  {
126  $status = LP_STATUS_FAILED;
127  }
128  elseif(in_array($a_user_id, $status_info["completed"][$sco_id]))
129  {
130  $status = LP_STATUS_COMPLETED;
131  }
132  elseif(in_array($a_user_id, $status_info["in_progress"][$sco_id]))
133  {
134  $status = LP_STATUS_IN_PROGRESS;
135  }
136  else
137  {
138  $status = LP_STATUS_NOT_ATTEMPTED;
139  }
140 
141  $items[$sco_id] = array(
142  "title" => $status_info["scos_title"][$sco_id],
143  "status" => $status,
144  "type" => "sahs"
145  );
146  }
147 
148  return $items;
149  }
150 
151  function getObjectsStatus(array $obj_refs)
152  {
153  global $ilDB;
154 
155  if(sizeof($obj_refs))
156  {
157  $obj_ids = array_keys($obj_refs);
158  self::refreshObjectsStatus($obj_ids);
159 
160  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
161  include_once "Services/Tracking/classes/class.ilLPStatus.php";
162 
163  // prepare object view modes
164  include_once 'Modules/Course/classes/class.ilObjCourse.php';
165  $view_modes = array();
166  $query = "SELECT obj_id, view_mode FROM crs_settings".
167  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
168  $set = $ilDB->query($query);
169  while($rec = $ilDB->fetchAssoc($set))
170  {
171  $view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
172  }
173 
174  $query = "SELECT object_data.obj_id, title, u_mode, type".
175  " FROM object_data".
176  " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
177  " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(LP_MODE_DEACTIVATED, "integer").")".
178  " AND ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
179  " GROUP BY object_data.obj_id, title, u_mode, type".
180  " ORDER BY title";
181  $set = $ilDB->query($query);
182  $result = array();
183  while($rec = $ilDB->fetchAssoc($set))
184  {
185  $rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
186  $rec["status"] = (int)$rec["status"];
187  $rec["u_mode"] = (int)$rec["u_mode"];
188 
189  $rec['status_in_progress'] = ilLPStatusWrapper::_getCountInProgress((int)$rec["obj_id"]);
190  $rec['status_completed'] = ilLPStatusWrapper::_getCountCompleted((int)$rec["obj_id"]);
191  $rec['status_failed'] = ilLPStatusWrapper::_getCountFailed((int)$rec["obj_id"]);
192  $rec['status_not_attempted'] = ilLPStatusWrapper::_getCountNotAttempted((int)$rec["obj_id"]);
193 
194  // lp mode might not match object/course view mode
195  if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
196  {
197  $rec["u_mode"] = LP_MODE_OBJECTIVES;
198  }
199  else if(!$rec["u_mode"])
200  {
201  $rec["u_mode"] = ilLPObjSettings::__getDefaultMode($rec["obj_id"], $rec["type"]);
202  }
203 
204  // can be default mode
205  if($rec["u_mode"] != LP_MODE_DEACTIVATE)
206  {
207  $result[$rec["obj_id"]] = $rec;
208  }
209  }
210  return $result;
211  }
212  }
213 
228  static function getUserDataForObject($a_ref_id, $a_order_field = "", $a_order_dir = "",
229  $a_offset = 0, $a_limit = 9999, array $a_filters = NULL, array $a_additional_fields = NULL,
230  $check_agreement = false, $privacy_fields = NULL)
231  {
232  global $ilDB;
233 
234  $fields = array("usr_data.usr_id", "login", "active");
235  $udf = self::buildColumns($fields, $a_additional_fields);
236 
237  $where = array();
238  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
239 
240  // users
241  $left = "";
242  $a_users = self::getParticipantsForObject($a_ref_id);
243 
244  $obj_id = ilObject::_lookupObjectId($a_ref_id);
245  self::refreshObjectsStatus(array($obj_id), $a_users);
246 
247  if (is_array($a_users))
248  {
249  $left = "LEFT";
250  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
251  }
252 
253  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
254  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
255  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
256  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
257  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
258  self::buildFilters($where, $a_filters);
259 
260  $queries = array(array("fields"=>$fields, "query"=>$query));
261 
262  // udf data is added later on, not in this query
263  $udf_order = null;
264  if(!$a_order_field)
265  {
266  $a_order_field = "login";
267  }
268  else if(substr($a_order_field, 0, 4) == "udf_")
269  {
270  $udf_order = $a_order_field;
271  $a_order_field = null;
272  }
273 
274  $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
275  if($result["cnt"])
276  {
277  if(sizeof($udf))
278  {
279  $query = "SELECT usr_id, field_id, value FROM udf_text WHERE ".$ilDB->in("field_id", $udf, false, "integer");
280  $set = $ilDB->query($query);
281  $udf = array();
282  while($row = $ilDB->fetchAssoc($set))
283  {
284  $udf[$row["usr_id"]]["udf_".$row["field_id"]] = $row["value"];
285  }
286  }
287 
288  // (course) user agreement
289  if($check_agreement)
290  {
291  // admins/tutors (write-access) will never have agreement ?!
292  include_once "Services/Membership/classes/class.ilMemberAgreement.php";
293  $agreements = ilMemberAgreement::lookupAcceptedAgreements($check_agreement);
294 
295  // public information for users
296  $query = "SELECT usr_id FROM usr_pref WHERE keyword = ".$ilDB->quote("public_profile", "text").
297  " AND value = ".$ilDB->quote("y", "text")." OR value = ".$ilDB->quote("g", "text");
298  $set = $ilDB->query($query);
299  $all_public = array();
300  while($row = $ilDB->fetchAssoc($set))
301  {
302  $all_public[] = $row["usr_id"];
303  }
304  $query = "SELECT usr_id,keyword FROM usr_pref WHERE ".$ilDB->like("keyword", "text", "public_%", false).
305  " AND value = ".$ilDB->quote("y", "text")." AND ".$ilDB->in("usr_id", $all_public, "", "integer");
306  $set = $ilDB->query($query);
307  $public = array();
308  while($row = $ilDB->fetchAssoc($set))
309  {
310  $public[$row["usr_id"]][] = substr($row["keyword"], 7);
311  }
312  unset($all_public);
313  }
314 
315  foreach($result["set"] as $idx => $row)
316  {
317  // add udf data
318  if(isset($udf[$row["usr_id"]]))
319  {
320  $result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
321  }
322 
323  // remove all private data - if active agreement and agreement not given by user
324  if(sizeof($privacy_fields) && $check_agreement && !in_array($row["usr_id"], $agreements))
325  {
326  foreach($privacy_fields as $field)
327  {
328  // check against public profile
329  if(isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
330  !in_array($field, $public[$row["usr_id"]])))
331  {
332  // remove complete entry - offending field was filtered
333  if(isset($a_filters[$field]))
334  {
335  unset($result["set"][$idx]);
336  break;
337  }
338  // remove offending field
339  else
340  {
341  $result["set"][$idx][$field] = false;
342  }
343  }
344  }
345  }
346  }
347 
348  // as we cannot do this in the query, sort by custom field here
349  if($udf_order)
350  {
351  include_once "Services/Utilities/classes/class.ilStr.php";
352  $result["set"] = ilUtil::stableSortArray($result["set"],
353  $udf_order, $a_order_dir);
354  }
355  }
356  return $result;
357  }
358 
374  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,
375  array $a_filters = NULL, array $a_additional_fields = NULL, $use_collection = true)
376  {
377  global $ilDB;
378 
379  $fields = array("object_data.obj_id", "title", "type");
380  self::buildColumns($fields, $a_additional_fields);
381 
382  $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection, true, array($a_user_id));
383 
384  $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND".
385  " read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
386  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer")." AND".
387  " ut_lp_marks.obj_id = object_data.obj_id)".
388  " WHERE ".$ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer").
389  self::buildFilters(array(), $a_filters);
390 
391  $queries = array();
392  $queries[] = array("fields"=>$fields, "query"=>$query);
393 
394  // objectives data
395  if($objects["objectives_parent_id"])
396  {
397  $objective_fields = array("crs_objectives.objective_id AS obj_id", "title",
398  $ilDB->quote("lobj", "text")." as type");
399 
400  if (is_array($a_additional_fields))
401  {
402  foreach($a_additional_fields as $field)
403  {
404  if($field != "status")
405  {
406  $objective_fields[] = "NULL AS ".$field;
407  }
408  else
409  {
410  include_once("Services/Tracking/classes/class.ilLPStatus.php");
411  $objective_fields[] = "CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END AS status";
412  }
413  }
414  }
415 
416  $where = array();
417  $where[] = "crs_objectives.crs_id = ".$ilDB->quote($objects["objectives_parent_id"], "integer");
418 
419  $objectives_query = " FROM crs_objectives".
420  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
421  " AND crs_objective_status.user_id = ".$ilDB->quote($a_user_id, "integer").")".
422  self::buildFilters($where, $a_filters);
423 
424  $queries[] = array("fields"=>$objective_fields, "query"=>$objectives_query, "count"=>"crs_objectives.objective_id");
425  }
426 
427  if(!in_array($a_order_field, $fields))
428  {
429  $a_order_field = "title";
430  }
431 
432  $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
433  if($result["cnt"])
434  {
435  // session data
436  $sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
437 
438  foreach($result["set"] as $idx => $item)
439  {
440  if($item["type"] == "sess")
441  {
442  $session = $sessions[$item["obj_id"]];
443  $result["set"][$idx]["title"] = $session["title"];
444  $result["set"][$idx]["sort_title"] = $session["e_start"];
445  // $result["set"][$idx]["status"] = (int)$session["status"];
446  }
447 
448  $result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
449  }
450 
451  // scos data (:TODO: will not be part of offset/limit)
452  if($objects["scorm"])
453  {
454  include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
455  $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
456  if($subtype == "scorm2004")
457  {
458  include_once("./Modules/Scorm2004/classes/class.ilObjScorm2004LearningModule.php");
459  $sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
460  $scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
461  }
462  else
463  {
464  include_once("./Modules/ScormAicc/classes/class.ilObjScormLearningModule.php");
465  $sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
466  $scos_tracking = array();
467  foreach($sobj->getTrackingDataAgg($a_user_id) as $item)
468  {
469  // format: hhhh:mm:ss ?!
470  if($item["time"])
471  {
472  $time = explode(":", $item["time"]);
473  $item["time"] = $time[0]*60*60+$time[1]*60+$time[2];
474  }
475  $scos_tracking[$item["sco_id"]] = array("session_time"=>$item["time"]);
476  }
477  }
478 
479  foreach($objects["scorm"]["scos"] as $sco)
480  {
481  $row = array("title" => $objects["scorm"]["scos_title"][$sco],
482  "type" => "sco");
483 
484  $status = LP_STATUS_NOT_ATTEMPTED_NUM;
485  if(in_array($a_user_id, $objects["scorm"]["completed"][$sco]))
486  {
487  $status = LP_STATUS_COMPLETED_NUM;
488  }
489  else if(in_array($a_user_id, $objects["scorm"]["failed"][$sco]))
490  {
491  $status = LP_STATUS_FAILED_NUM;
492  }
493  else if(in_array($a_user_id, $objects["scorm"]["in_progress"][$sco]))
494  {
495  $status = LP_STATUS_IN_PROGRESS_NUM;
496  }
497  $row["status"] = $status;
498 
499  // add available tracking data
500  if(isset($scos_tracking[$sco]))
501  {
502  if(isset($scos_tracking[$sco]["last_access"]))
503  {
504  $date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
505  $row["last_access"] = $date->get(IL_CAL_UNIX);
506  }
507  $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
508  }
509 
510  $result["set"][] = $row;
511  $result["cnt"]++;
512  }
513  }
514  }
515  return $result;
516  }
517 
525  protected static function getSessionData($a_user_id, array $obj_ids)
526  {
527  global $ilDB;
528 
529  $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,".
530  " mark, e_comment".
531  " FROM event".
532  " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)".
533  " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = ".$ilDB->quote($a_user_id, "integer").")".
534  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
535  $set = $ilDB->query($query);
536  $sessions = array();
537  while($rec = $ilDB->fetchAssoc($set))
538  {
539  $rec["comment"] = $rec["e_comment"];
540  unset($rec["e_comment"]);
541 
542  $date = ilDatePresentation::formatPeriod(
543  new ilDateTime($rec["e_start"], IL_CAL_DATETIME),
544  new ilDateTime($rec["e_end"], IL_CAL_DATETIME));
545 
546  if($rec["title"])
547  {
548  $rec["title"] = $date.': '.$rec["title"];
549  }
550  else
551  {
552  $rec["title"] = $date;
553  }
554  $sessions[$rec["obj_id"]] = $rec;
555  }
556  return $sessions;
557  }
558 
575  static function getObjectsSummaryForObject($a_parent_obj_id, $a_parent_ref_id, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999,
576  array $a_filters = NULL, array $a_additional_fields = NULL, $a_preselected_obj_ids = NULL)
577  {
578  global $ilDB;
579 
580  $fields = array();
581  self::buildColumns($fields, $a_additional_fields, true);
582 
583  $objects = array();
584  if($a_preselected_obj_ids === NULL)
585  {
586  $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
587  }
588  else
589  {
590  foreach($a_preselected_obj_ids as $obj_id => $ref_ids)
591  {
592  $objects["object_ids"][] = $obj_id;
593  $objects["ref_ids"][$obj_id] = array_pop($ref_ids);
594  }
595  }
596 
597  $result = array();
598  if($objects)
599  {
600  // object data
601  $set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
602  " WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
603  while($rec = $ilDB->fetchAssoc($set))
604  {
605  $object_data[$rec["obj_id"]] = $rec;
606  if($a_preselected_obj_ids)
607  {
608  $object_data[$rec["obj_id"]]["ref_ids"] = $a_preselected_obj_ids[$rec["obj_id"]];
609  }
610  }
611 
612  foreach($objects["ref_ids"] as $object_id => $ref_id)
613  {
614  $object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
615  if(sizeof($object_result))
616  {
617  if($object_data[$object_id])
618  {
619  $result[] = array_merge($object_data[$object_id], $object_result);
620  }
621  }
622  }
623 
624  // :TODO: objectives
625  if($objects["objectives_parent_id"])
626  {
627 
628  }
629  }
630 
631  return array("cnt"=>sizeof($result), "set"=>$result);
632  }
633 
642  protected static function getSummaryDataForObject($a_ref_id, array $fields, array $a_filters = NULL)
643  {
644  global $ilDB;
645 
646  $where = array();
647  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
648 
649  // users
650  $a_users = self::getParticipantsForObject($a_ref_id);
651  $left = "";
652  if (is_array($a_users) && sizeof($a_users))
653  {
654  $left = "LEFT";
655  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
656  }
657 
658  $obj_id = ilObject::_lookupObjectId($a_ref_id);
659  self::refreshObjectsStatus(array($obj_id), $a_users);
660 
661  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
662  " AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
663  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
664  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
665  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
666  self::buildFilters($where, $a_filters, true);
667 
668  $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
669 
670  $queries = array();
671  $queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
672 
673  $result = self::executeQueries($queries);
674  $result = $result["set"][0];
675  $users_no = $result["user_count"];
676 
677  $valid = true;
678  if(!$users_no)
679  {
680  $valid = false;
681  }
682  else if(isset($a_filters["user_total"]))
683  {
684  if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
685  {
686  $valid = false;
687  }
688  else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
689  {
690  $valid = false;
691  }
692  }
693 
694  if($valid)
695  {
696  $result["country"] = self::getSummaryPercentages("country", $query);
697  $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
698  $result["city"] = self::getSummaryPercentages("city", $query);
699  $result["gender"] = self::getSummaryPercentages("gender", $query);
700  $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
701  $result["status"] = self::getSummaryPercentages("status", $query);
702  $result["mark"] = self::getSummaryPercentages("mark", $query);
703  }
704  else
705  {
706  $result = array();
707  }
708 
709  if($result)
710  {
711  $result["user_total"] = $users_no;
712  }
713 
714  return $result;
715  }
716 
725  protected static function getSummaryPercentages($field, $base_query, $alias = NULL)
726  {
727  global $ilDB;
728 
729  if(!$alias)
730  {
731  $field_alias = $field;
732  }
733  else
734  {
735  $field_alias = $alias;
736  $alias = " AS ".$alias;
737  }
738 
739  // move having BEHIND group by
740  $having = "";
741  if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
742  {
743  $having = " HAVING ".$hits[1];
744  $base_query = str_replace($hits[0], "", $base_query);
745  }
746 
747  $query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
748  $set = $ilDB->query($query);
749  $result = array();
750  while($rec = $ilDB->fetchAssoc($set))
751  {
752  $result[$rec[$field_alias]] = (int)$rec["counter"];
753  }
754  return $result;
755  }
756 
763  public static function getParticipantsForObject($a_ref_id)
764  {
765  global $tree;
766 
767  $obj_id = ilObject::_lookupObjectId($a_ref_id);
768  $obj_type = ilObject::_lookupType($obj_id);
769 
770  // try to get participants from (parent) course/group
771  switch($obj_type)
772  {
773  case "crs":
774  include_once "Modules/Course/classes/class.ilCourseParticipants.php";
775  $member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
776  return $member_obj->getMembers();
777 
778  case "grp":
779  include_once "Modules/Group/classes/class.ilGroupParticipants.php";
780  $member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
781  return $member_obj->getMembers();
782 
783  default:
784  // walk path to find course or group object and use members of that object
785  $path = $tree->getPathId($a_ref_id);
786  array_pop($path);
787  foreach(array_reverse($path) as $path_ref_id)
788  {
789  $type = ilObject::_lookupType($path_ref_id, true);
790  if($type == "crs" || $type == "grp")
791  {
792  return self::getParticipantsForObject($path_ref_id);
793  }
794  }
795  break;
796  }
797 
798  $a_users = null;
799 
800  // no participants possible: use tracking/object data where possible
801  switch($obj_type)
802  {
803  case "sahs":
804  include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
805  $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
806  if ($subtype == "scorm2004")
807  {
808  // based on cmi_node/cp_node, used for scorm tracking data views
809  include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
810  $mod = new ilObjSCORM2004LearningModule($obj_id, false);
811  $all = $mod->getTrackedUsers("");
812  if($all)
813  {
814  $a_users = array();
815  foreach($all as $item)
816  {
817  $a_users[] = $item["user_id"];
818  }
819  }
820  }
821  else
822  {
823  include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
824  $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
825  }
826  break;
827 
828  case "exc":
829  include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
830  include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
831  $exc = new ilObjExercise($obj_id, false);
832  $members = new ilExerciseMembers($exc);
833  $a_users = $members->getMembers();
834  break;
835 
836  case "tst":
837  include_once("./Services/Tracking/classes/class.ilLPStatusTestFinished.php");
838  $a_users = ilLPStatusTestFinished::getParticipants($obj_id);
839  break;
840 
841  default:
842  // no sensible data: return null
843  break;
844  }
845 
846  return $a_users;
847  }
848 
857  static protected function buildFilters(array $where, array $a_filters = NULL, $a_aggregate = false)
858  {
859  global $ilDB;
860 
861  $having = array();
862 
863  if(sizeof($a_filters))
864  {
865  foreach($a_filters as $id => $value)
866  {
867  switch($id)
868  {
869  case "login":
870  case "firstname":
871  case "lastname":
872  case "institution":
873  case "department":
874  case "street":
875  case "email":
876  case "matriculation":
877  case "country":
878  case "city":
879  case "title":
880  $where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
881  break;
882 
883  case "gender":
884  case "zipcode":
885  case "sel_country":
886  $where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
887  break;
888 
889  case "u_comment":
890  $where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
891  break;
892 
893  case "status":
894  if($value == LP_STATUS_NOT_ATTEMPTED_NUM)
895  {
896  // #10645 - not_attempted is default
897  $where[] = "(ut_lp_marks.status = ".$ilDB->quote(LP_STATUS_NOT_ATTEMPTED_NUM ,"text").
898  " OR ut_lp_marks.status IS NULL)";
899  break;
900  }
901  // fallthrough
902 
903  case "mark":
904  $where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
905  break;
906 
907  case "percentage":
908  if(!$a_aggregate)
909  {
910  if($value["from"])
911  {
912  $where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
913  }
914  if($value["to"])
915  {
916  $where[] = "ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer");
917  }
918  }
919  else
920  {
921  if($value["from"])
922  {
923  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
924  }
925  if($value["to"])
926  {
927  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
928  }
929  }
930  break;
931 
932  case "language":
933  $where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
934  break;
935 
936  // timestamp
937  case "last_access":
938  if($value["from"])
939  {
940  $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
941  $value["from"] = $value["from"]->get(IL_CAL_UNIX);
942  }
943  if($value["to"])
944  {
945  $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
946  $value["to"] = $value["to"]->get(IL_CAL_UNIX);
947  }
948  // fallthrough
949 
950  case 'status_changed':
951  // fallthrough
952 
953  case "registration":
954  if($id == "registration")
955  {
956  $id = "create_date";
957  }
958  // fallthrough
959 
960  case "create_date":
961  case "first_access":
962  case "birthday":
963  if($value["from"])
964  {
965  $where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
966  }
967  if($value["to"])
968  {
969  $where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
970  }
971  break;
972 
973  case "read_count":
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[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
990  }
991  if($value["to"])
992  {
993  $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
994  }
995  }
996  break;
997 
998  case "spent_seconds":
999  if(!$a_aggregate)
1000  {
1001  if($value["from"])
1002  {
1003  $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
1004  }
1005  if($value["to"])
1006  {
1007  $where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
1008  }
1009  }
1010  else
1011  {
1012  if($value["from"])
1013  {
1014  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
1015  }
1016  if($value["to"])
1017  {
1018  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
1019  }
1020  }
1021  break;
1022 
1023  default:
1024  // var_dump("unknown: ".$id);
1025  break;
1026  }
1027  }
1028  }
1029 
1030  $sql = "";
1031  if(sizeof($where))
1032  {
1033  $sql .= " WHERE ".implode(" AND ", $where);
1034  }
1035  if(sizeof($having))
1036  {
1037  // ugly "having" hack because of summary view
1038  $sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
1039  }
1040 
1041  return $sql;
1042  }
1043 
1052  static protected function buildColumns(array &$a_fields, array $a_additional_fields = NULL, $a_aggregate = false)
1053  {
1054  if(sizeof($a_additional_fields))
1055  {
1056  $udf = NULL;
1057  foreach($a_additional_fields as $field)
1058  {
1059  if(substr($field, 0, 4) != "udf_")
1060  {
1061  $function = NULL;
1062  if($a_aggregate)
1063  {
1064  $pos = strrpos($field, "_");
1065  if($pos === false)
1066  {
1067  continue;
1068  }
1069  $function = strtoupper(substr($field, $pos+1));
1070  $field = substr($field, 0, $pos);
1071  if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
1072  {
1073  continue;
1074  }
1075  }
1076 
1077  switch($field)
1078  {
1079  case "language":
1080  if($function)
1081  {
1082  $a_fields[] = $function."(value) AS ".$field."_".strtolower($function);
1083  }
1084  else
1085  {
1086  $a_fields[] = "value AS ".$field;
1087  }
1088  break;
1089 
1090  case "read_count":
1091  case "spent_seconds":
1092  if(!$function)
1093  {
1094  $a_fields[] = "(".$field."+childs_".$field.") AS ".$field;
1095  }
1096  else
1097  {
1098  if($function == "AVG")
1099  {
1100  $a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) AS ".$field."_".strtolower($function);
1101  }
1102  else
1103  {
1104  $a_fields[] = $function."(".$field."+childs_".$field.") AS ".$field."_".strtolower($function);
1105  }
1106  }
1107  break;
1108 
1109  case "read_count_spent_seconds":
1110  if($function == "AVG")
1111  {
1112  $a_fields[] = "ROUND(AVG((spent_seconds+childs_spent_seconds)/(read_count+childs_read_count)), 2) AS ".$field."_".strtolower($function);
1113  }
1114  break;
1115 
1116  default:
1117  if($function)
1118  {
1119  if($function == "AVG")
1120  {
1121  $a_fields[] = "ROUND(AVG(".$field."), 2) AS ".$field."_".strtolower($function);
1122  }
1123  else
1124  {
1125  $a_fields[] = $function."(".$field.") AS ".$field."_".strtolower($function);
1126  }
1127  }
1128  else
1129  {
1130  $a_fields[] = $field;
1131  }
1132  break;
1133  }
1134  }
1135  else
1136  {
1137  $udf[] = substr($field, 4);
1138  }
1139  }
1140 
1141  // clean-up
1142  $a_fields = array_unique($a_fields);
1143  if(is_array($udf))
1144  {
1145  $udf = array_unique($udf);
1146  }
1147 
1148  return $udf;
1149  }
1150  }
1151 
1162  static public function getObjectIds($a_parent_obj_id, $a_parent_ref_id = false, $use_collection = true, $a_refresh_status = true, $a_user_ids = null)
1163  {
1164  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
1165 
1166  $object_ids = array($a_parent_obj_id);
1167  $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1168  $objectives_parent_id = $scorm = false;
1169 
1170  $mode = ilLPObjSettings::_lookupMode($a_parent_obj_id);
1171  switch($mode)
1172  {
1173  // what about LP_MODE_SCORM_PACKAGE ?
1174  case LP_MODE_SCORM:
1175  include_once "Services/Tracking/classes/class.ilLPStatusSCORM.php";
1176  $status_scorm = new ilLPStatusSCORM($a_parent_obj_id);
1177  $scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
1178  break;
1179 
1180  case LP_MODE_OBJECTIVES:
1181  if(ilObject::_lookupType($a_parent_obj_id) == "crs")
1182  {
1183  $objectives_parent_id = $a_parent_obj_id;
1184  }
1185  break;
1186 
1187  default:
1188  // lp collection
1189  if($use_collection)
1190  {
1191  include_once 'Services/Tracking/classes/class.ilLPCollectionCache.php';
1192  foreach(ilLPCollectionCache::_getItems($a_parent_obj_id) as $child_ref_id)
1193  {
1194  $child_id = ilObject::_lookupObjId($child_ref_id);
1195  $object_ids[] = $child_id;
1196  $ref_ids[$child_id] = $child_ref_id;
1197  }
1198  }
1199  // all objects in branch
1200  else
1201  {
1202  self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1203  $object_ids = array_unique($object_ids);
1204  }
1205 
1206  foreach($object_ids as $idx => $object_id)
1207  {
1208  if(!$object_id)
1209  {
1210  unset($object_ids[$idx]);
1211  }
1212  }
1213  break;
1214  }
1215 
1216  if($a_refresh_status)
1217  {
1218  self::refreshObjectsStatus($object_ids, $a_user_ids);
1219  }
1220 
1221  return array("object_ids" => $object_ids,
1222  "ref_ids" => $ref_ids,
1223  "objectives_parent_id" => $objectives_parent_id,
1224  "scorm" => $scorm);
1225  }
1226 
1234  static protected function getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
1235  {
1236  global $tree;
1237 
1238  $children = $tree->getChilds($a_parent_ref_id);
1239  if($children)
1240  {
1241  foreach($children as $child)
1242  {
1243  if($child["type"] == "adm" || $child["type"] == "rolf")
1244  {
1245  continue;
1246  }
1247 
1248  // as there can be deactivated items in the collection
1249  // we should allow them here too
1250  $cmode = ilLPObjSettings::_lookupMode($child["obj_id"]);
1251  if(/* $cmode != LP_MODE_DEACTIVATED && */ $cmode != LP_MODE_UNDEFINED)
1252  {
1253  $a_object_ids[] = $child["obj_id"];
1254  $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1255  }
1256 
1257  self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1258  }
1259  }
1260  }
1261 
1272  static function executeQueries(array $queries, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999)
1273  {
1274  global $ilDB;
1275 
1276  $cnt = 0;
1277  $subqueries = array();
1278  foreach($queries as $item)
1279  {
1280  // ugly "having" hack because of summary view
1281  $item = str_replace("[[--HAVING", "HAVING", $item);
1282  $item = str_replace("HAVING--]]", "", $item);
1283 
1284  if(!isset($item["count"]))
1285  {
1286  $count_field = $item["fields"];
1287  $count_field = array_shift($count_field);
1288  }
1289  else
1290  {
1291  $count_field = $item["count"];
1292  }
1293  $count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
1294  $set = $ilDB->query($count_query);
1295  if ($rec = $ilDB->fetchAssoc($set))
1296  {
1297  $cnt += $rec["cnt"];
1298  }
1299 
1300  $subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
1301  }
1302 
1303  // set query
1304  $result = array();
1305  if($cnt > 0)
1306  {
1307  if(sizeof($subqueries) > 1)
1308  {
1309  $base = array_shift($subqueries);
1310  $query = $base." UNION (".implode(") UNION (", $subqueries).")";
1311  }
1312  else
1313  {
1314  $query = $subqueries[0];
1315  }
1316 
1317  if ($a_order_dir != "asc" && $a_order_dir != "desc")
1318  {
1319  $a_order_dir = "asc";
1320  }
1321  if($a_order_field)
1322  {
1323  $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
1324  }
1325 
1326  $offset = (int) $a_offset;
1327  $limit = (int) $a_limit;
1328  $ilDB->setLimit($limit, $offset);
1329 
1330  $set = $ilDB->query($query);
1331  while($rec = $ilDB->fetchAssoc($set))
1332  {
1333  $result[] = $rec;
1334  }
1335  }
1336 
1337  return array("cnt" => $cnt, "set" => $result);
1338  }
1339 
1348  static function getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter = NULL)
1349  {
1350  global $ilDB;
1351 
1352  $result = array("cnt"=>0, "set"=>NULL);
1353  if(sizeof($a_obj_ids))
1354  {
1355  $where = array();
1356  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
1357  if($a_user_filter)
1358  {
1359  $where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
1360  }
1361 
1362  // users
1363  $left = "";
1364  $a_users = self::getParticipantsForObject($a_parent_ref_id);
1365  if (is_array($a_users))
1366  {
1367  $left = "LEFT";
1368  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1369  }
1370 
1371  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1372 
1373  $fields = array("usr_data.usr_id", "login", "active", "status",
1374  "status_changed", "percentage", "last_access",
1375  "spent_seconds+childs_spent_seconds as spent_seconds");
1376 
1377  $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1378  self::refreshObjectsStatus(array($parent_obj_id), $a_users);
1379 
1380  self::refreshObjectsStatus($a_obj_ids, $a_users);
1381 
1382  $raw = array();
1383  foreach($a_obj_ids as $obj_id)
1384  {
1385  // one request for each object
1386  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
1387  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1388  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
1389  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1390  self::buildFilters($where, $a_filters);
1391 
1392  $raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), "login");
1393  if($raw["cnt"])
1394  {
1395  // convert to final structure
1396  foreach($raw["set"] as $row)
1397  {
1398  $result["set"][$row["usr_id"]]["login"] = $row["login"];
1399  $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1400  $result["set"][$row["usr_id"]]["objects"][$obj_id] = array("status"=>$row["status"],
1401  "percentage"=>$row["percentage"]);
1402  if($obj_id == $parent_obj_id)
1403  {
1404  $result["set"][$row["usr_id"]]["status_changed"] = $row["status_changed"];
1405  $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1406  $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1407  }
1408  }
1409  }
1410  }
1411  $result["cnt"] = sizeof($result["set"]);
1412  $result["users"] = $a_users;
1413  }
1414  return $result;
1415  }
1416 
1417  static public function getUserObjectiveMatrix($a_parent_obj_id, $a_users)
1418  {
1419  global $ilDB;
1420 
1421  if($a_parent_obj_id && $a_users)
1422  {
1423  include_once("Services/Tracking/classes/class.ilLPStatus.php");
1424 
1425  $fields = array("crs_objectives.objective_id AS obj_id", "crs_objective_status.user_id AS usr_id", "title");
1426  $fields[] = "CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END AS status";
1427 
1428  $where = array();
1429  $where[] = "crs_objectives.crs_id = ".$ilDB->quote($a_parent_obj_id, "integer");
1430 
1431  $query = " FROM crs_objectives".
1432  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
1433  " AND ".$ilDB->in("crs_objective_status.user_id", $a_users, "", "integer").")".
1434  self::buildFilters($where);
1435 
1436  return self::executeQueries(array(array("fields"=>$fields, "query"=>$query, "count"=>"crs_objectives.objective_id")));
1437  }
1438  }
1439 
1440  static public function getObjectAccessStatistics(array $a_ref_ids, $a_year, $a_month = null)
1441  {
1442  global $ilDB;
1443 
1444  $obj_ids = array_keys($a_ref_ids);
1445 
1446  if($a_month)
1447  {
1448  $column = "dd";
1449  }
1450  else
1451  {
1452  $column = "mm";
1453  }
1454 
1455  $res = array();
1456  $sql = "SELECT obj_id,".$column.",SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1457  "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1458  " FROM obj_stat".
1459  " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1460  " AND yyyy = ".$ilDB->quote($a_year, "integer");
1461  if($a_month)
1462  {
1463  $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1464  }
1465  $sql .= " GROUP BY obj_id,".$column;
1466  $set = $ilDB->query($sql);
1467  while($row = $ilDB->fetchAssoc($set))
1468  {
1469  $row["read_count"] += $row["childs_read_count"];
1470  $row["spent_seconds"] += $row["childs_spent_seconds"];
1471  $res[$row["obj_id"]][$row[$column]]["read_count"] += $row["read_count"];
1472  $res[$row["obj_id"]][$row[$column]]["spent_seconds"] += $row["spent_seconds"];
1473  }
1474  return $res;
1475  }
1476 
1477  function getObjectTypeStatistics()
1478  {
1479  global $ilDB, $objDefinition;
1480 
1481  // re-use add new item selection (folder is not that important)
1482  $types = array_keys($objDefinition->getCreatableSubObjects("root", ilObjectDefinition::MODE_REPOSITORY));
1483 
1484  include_once "Services/Tree/classes/class.ilTree.php";
1485  $tree = new ilTree(1);
1486  $sql = "SELECT ".$tree->table_obj_data.".obj_id,".$tree->table_obj_data.".type,".
1487  $tree->table_tree.".".$tree->tree_pk.",".$tree->table_obj_reference.".ref_id".
1488  " FROM ".$tree->table_tree.
1489  " ".$tree->buildJoin().
1490  " WHERE ".$ilDB->in($tree->table_obj_data.".type", $types, "", "text");
1491  $set = $ilDB->query($sql);
1492  $res = array();
1493  while($row = $ilDB->fetchAssoc($set))
1494  {
1495  $res[$row["type"]]["type"] = $row["type"];
1496  $res[$row["type"]]["references"]++;
1497  $res[$row["type"]]["objects"][] = $row["obj_id"];
1498  if($row[$tree->tree_pk] < 0)
1499  {
1500  $res[$row["type"]]["deleted"]++;
1501  }
1502  }
1503 
1504  foreach($res as $type => $values)
1505  {
1506  $res[$type]["objects"] = sizeof(array_unique($values["objects"]));
1507  }
1508 
1509  return $res;
1510  }
1511 
1512  static public function getObjectDailyStatistics(array $a_ref_ids, $a_year, $a_month = null)
1513  {
1514  global $ilDB;
1515 
1516  $obj_ids = array_keys($a_ref_ids);
1517 
1518  $res = array();
1519  $sql = "SELECT obj_id,hh,SUM(read_count) read_count,SUM(childs_read_count) childs_read_count,".
1520  "SUM(spent_seconds) spent_seconds,SUM(childs_spent_seconds) childs_spent_seconds".
1521  " FROM obj_stat".
1522  " WHERE ".$ilDB->in("obj_id", $obj_ids, "", "integer").
1523  " AND yyyy = ".$ilDB->quote($a_year, "integer");
1524  if($a_month)
1525  {
1526  $sql .= " AND mm = ".$ilDB->quote($a_month, "integer");
1527  }
1528  $sql .= " GROUP BY obj_id,hh";
1529  $set = $ilDB->query($sql);
1530  while($row = $ilDB->fetchAssoc($set))
1531  {
1532  $row["read_count"] += $row["childs_read_count"];
1533  $row["spent_seconds"] += $row["childs_spent_seconds"];
1534  $res[$row["obj_id"]][(int)$row["hh"]]["read_count"] += $row["read_count"];
1535  $res[$row["obj_id"]][(int)$row["hh"]]["spent_seconds"] += $row["spent_seconds"];
1536  }
1537  return $res;
1538  }
1539 
1540  static public function getObjectStatisticsMonthlySummary()
1541  {
1542  global $ilDB;
1543 
1544  $set = $ilDB->query("SELECT COUNT(*) AS COUNTER,yyyy,mm".
1545  " FROM obj_stat".
1546  " GROUP BY yyyy, mm".
1547  " ORDER BY yyyy DESC, mm DESC");
1548  $res = array();
1549  while($row = $ilDB->fetchAssoc($set))
1550  {
1551  $res[] = array("month"=>$row["yyyy"]."-".$row["mm"],
1552  "count"=>$row["counter"]);
1553  }
1554  return $res;
1555  }
1556 
1557  static public function deleteObjectStatistics(array $a_months)
1558  {
1559  global $ilDB;
1560 
1561  $sql = "DELETE FROM obj_stat".
1562  " WHERE ".$ilDB->in($ilDB->concat(array(array("yyyy", ""), array($ilDB->quote("-", "text"), ""),
1563  array("mm", ""))), $a_months, "", "text");
1564  return $ilDB->manipulate($sql);
1565  }
1566 
1567  static public function searchObjects($a_type, $a_title = null, $a_root = null)
1568  {
1569  global $ilDB, $tree;
1570 
1571  if($a_type == "lres")
1572  {
1573  $a_type = array('lm','sahs','htlm','dbk');
1574  }
1575 
1576  $sql = "SELECT r.ref_id,r.obj_id".
1577  " FROM object_data o".
1578  " JOIN object_reference r ON (o.obj_id = r.obj_id)".
1579  " JOIN tree t ON (t.child = r.ref_id)".
1580  " WHERE t.tree = ".$ilDB->quote(1, "integer");
1581 
1582  if(!is_array($a_type))
1583  {
1584  $sql .= " AND o.type = ".$ilDB->quote($a_type, "text");
1585  }
1586  else
1587  {
1588  $sql .= " AND ".$ilDB->in("o.type", $a_type, "", "text");
1589  }
1590 
1591  if($a_title)
1592  {
1593  $sql .= " AND (".$ilDB->like("o.title", "text", "%".$a_title."%").
1594  " OR ".$ilDB->like("o.description", "text", "%".$a_title."%").")";
1595  }
1596 
1597  $set = $ilDB->query($sql);
1598  $res = array();
1599  while($row = $ilDB->fetchAssoc($set))
1600  {
1601  if($a_root && $a_root != ROOT_FOLDER_ID)
1602  {
1603  foreach(ilObject::_getAllReferences($row['obj_id']) as $ref_id)
1604  {
1605  if($tree->isGrandChild($a_root, $ref_id))
1606  {
1607  $res[$row["obj_id"]][] = $row["ref_id"];
1608  continue;
1609  }
1610  }
1611  }
1612  else
1613  {
1614  $res[$row["obj_id"]][] = $row["ref_id"];
1615  }
1616  }
1617  return $res;
1618  }
1619 
1626  protected static function refreshObjectsStatus(array $a_obj_ids, $a_users = null)
1627  {
1628  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1629  foreach($a_obj_ids as $obj_id)
1630  {
1631  ilLPStatus::checkStatusForObject($obj_id, $a_users);
1632  }
1633  }
1634 
1640  public static function getObjectStatisticsLogInfo()
1641  {
1642  global $ilDB;
1643 
1644  $set = $ilDB->query("SELECT COUNT(*) counter, MIN(tstamp) tstamp".
1645  " FROM obj_stat_log");
1646  return $ilDB->fetchAssoc($set);
1647  }
1648 }
1649 
1650 ?>