ILIAS  eassessment Revision 61809
 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 
22  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
23  include_once "Services/Tracking/classes/class.ilLPStatus.php";
24 
25  // prepare object view modes
26  include_once 'Modules/Course/classes/class.ilObjCourse.php';
27  $view_modes = array();
28  $query = "SELECT obj_id, view_mode FROM crs_settings".
29  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
30  $set = $ilDB->query($query);
31  while($rec = $ilDB->fetchAssoc($set))
32  {
33  $view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
34  }
35 
36  $sessions = self::getSessionData($a_user_id, $obj_ids);
37 
38  $query = "SELECT object_data.obj_id, title, CASE WHEN status IS NULL THEN ".LP_STATUS_NOT_ATTEMPTED_NUM." ELSE status END AS status,".
39  " percentage, read_count+childs_read_count AS read_count, spent_seconds+childs_spent_seconds AS spent_seconds,".
40  " u_mode, type, visits, mark, u_comment AS comment".
41  " FROM object_data".
42  " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
43  " LEFT JOIN read_event ON (read_event.obj_id = object_data.obj_id AND read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
44  " 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").")".
45  // " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(LP_MODE_DEACTIVATED, "integer").")".
46  " WHERE ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
47  " ORDER BY title";
48  $set = $ilDB->query($query);
49  $result = array();
50  while($rec = $ilDB->fetchAssoc($set))
51  {
52  $rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
53  $rec["status"] = (int)$rec["status"];
54  $rec["percentage"] = (int)$rec["percentage"];
55  $rec["read_count"] = (int)$rec["read_count"];
56  $rec["spent_seconds"] = (int)$rec["spent_seconds"];
57  $rec["u_mode"] = (int)$rec["u_mode"];
58 
59  if($rec["type"] == "sess")
60  {
61  $session = $sessions[$rec["obj_id"]];
62  $rec["title"] = $session["title"];
63  // $rec["status"] = (int)$session["status"];
64  }
65 
66  // lp mode might not match object/course view mode
67  if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
68  {
69  $rec["u_mode"] = LP_MODE_OBJECTIVES;
70  }
71  else if(!$rec["u_mode"])
72  {
73  $rec["u_mode"] = ilLPObjSettings::__getDefaultMode($rec["obj_id"], $rec["type"]);
74  }
75 
76  // can be default mode
77  if(/*$rec["u_mode"] != LP_MODE_DEACTIVATE*/ true)
78  {
79  $result[] = $rec;
80  }
81  }
82  return $result;
83  }
84  }
85 
86  function getObjectivesStatusForUser($a_user_id, array $obj_ids)
87  {
88  global $ilDB;
89 
90  $query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title, status, ".$ilDB->quote("lobj", "text")." AS type".
91  " FROM crs_objectives".
92  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id AND user_id = ".$a_user_id.")".
93  " WHERE ".$ilDB->in("crs_objectives.objective_id", $obj_ids, false, "integer").
94  " ORDER BY position";
95  $set = $ilDB->query($query);
96  $result = array();
97  while($rec = $ilDB->fetchAssoc($set))
98  {
99  if($rec["status"])
100  {
101  $rec["status"] = LP_STATUS_COMPLETED_NUM;
102  }
103  $result[] = $rec;
104  }
105 
106  return $result;
107  }
108 
109  function getSCOsStatusForUser($a_user_id, $a_parent_obj_id, array $a_sco_ids)
110  {
111  include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
112  $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
113 
114  $items = array();
115  foreach($a_sco_ids as $sco_id)
116  {
117  if(in_array($a_user_id, $status_info["in_progress"][$sco_id]))
118  {
119  $status = LP_STATUS_IN_PROGRESS;
120  }
121  elseif(in_array($a_user_id, $status_info["completed"][$sco_id]))
122  {
123  $status = LP_STATUS_COMPLETED;
124  }
125  elseif(in_array($a_user_id, $status_info["failed"][$sco_id]))
126  {
127  $status = LP_STATUS_FAILED;
128  }
129  else
130  {
131  $status = LP_STATUS_NOT_ATTEMPTED;
132  }
133 
134  $items[$sco_id] = array(
135  "title" => $status_info["scos_title"][$sco_id],
136  "status" => $status,
137  "type" => "sahs"
138  );
139  }
140 
141  return $items;
142  }
143 
144  function getObjectsStatus(array $obj_refs)
145  {
146  global $ilDB;
147 
148  if(sizeof($obj_refs))
149  {
150  $obj_ids = array_keys($obj_refs);
151 
152  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
153  include_once "Services/Tracking/classes/class.ilLPStatus.php";
154 
155  // prepare object view modes
156  include_once 'Modules/Course/classes/class.ilObjCourse.php';
157  $view_modes = array();
158  $query = "SELECT obj_id, view_mode FROM crs_settings".
159  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
160  $set = $ilDB->query($query);
161  while($rec = $ilDB->fetchAssoc($set))
162  {
163  $view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
164  }
165 
166  $query = "SELECT object_data.obj_id, title, u_mode, type".
167  " FROM object_data".
168  " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
169  " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(LP_MODE_DEACTIVATED, "integer").")".
170  " AND ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
171  " GROUP BY object_data.obj_id, title, u_mode, type".
172  " ORDER BY title";
173  $set = $ilDB->query($query);
174  $result = array();
175  while($rec = $ilDB->fetchAssoc($set))
176  {
177  $rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
178  $rec["status"] = (int)$rec["status"];
179  $rec["u_mode"] = (int)$rec["u_mode"];
180 
181  $rec['status_in_progress'] = ilLPStatusWrapper::_getCountInProgress((int)$rec["obj_id"]);
182  $rec['status_completed'] = ilLPStatusWrapper::_getCountCompleted((int)$rec["obj_id"]);
183  $rec['status_failed'] = ilLPStatusWrapper::_getCountFailed((int)$rec["obj_id"]);
184  $rec['status_not_attempted'] = ilLPStatusWrapper::_getCountNotAttempted((int)$rec["obj_id"]);
185 
186  // lp mode might not match object/course view mode
187  if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
188  {
189  $rec["u_mode"] = LP_MODE_OBJECTIVES;
190  }
191  else if(!$rec["u_mode"])
192  {
193  $rec["u_mode"] = ilLPObjSettings::__getDefaultMode($rec["obj_id"], $rec["type"]);
194  }
195 
196  // can be default mode
197  if($rec["u_mode"] != LP_MODE_DEACTIVATE)
198  {
199  $result[$rec["obj_id"]] = $rec;
200  }
201  }
202  return $result;
203  }
204  }
205 
220  static function getUserDataForObject($a_ref_id, $a_order_field = "", $a_order_dir = "",
221  $a_offset = 0, $a_limit = 9999, array $a_filters = NULL, array $a_additional_fields = NULL,
222  $check_agreement = false, $privacy_fields = NULL)
223  {
224  global $ilDB;
225 
226  $fields = array("usr_data.usr_id", "login");
227  $udf = self::buildColumns($fields, $a_additional_fields);
228 
229  $where = array();
230  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
231 
232  // users
233  $left = "";
234  $a_users = self::getParticipantsForObject($a_ref_id);
235 
236  $obj_id = ilObject::_lookupObjectId($a_ref_id);
237 
238  // check whether status (for all relevant users) exists
239  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
240  ilLPStatus::checkStatusForObject($obj_id, $a_users);
241 
242  if (is_array($a_users))
243  {
244  $left = "LEFT";
245  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
246  }
247 
248  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
249  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
250  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
251  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
252  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
253  self::buildFilters($where, $a_filters);
254 
255  $queries = array(array("fields"=>$fields, "query"=>$query));
256 
257  $udf_order = null;
258  if(!$a_order_field)
259  {
260  $a_order_field = "login";
261  }
262  else if(substr($a_order_field, 0, 4) == "udf_")
263  {
264  $udf_order = $a_order_field;
265  $a_order_field = null;
266  }
267 
268  $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
269  if($result["cnt"])
270  {
271  if(sizeof($udf))
272  {
273  $query = "SELECT usr_id, field_id, value FROM udf_text WHERE ".$ilDB->in("field_id", $udf, false, "integer");
274  $set = $ilDB->query($query);
275  $udf = array();
276  while($row = $ilDB->fetchAssoc($set))
277  {
278  $udf[$row["usr_id"]]["udf_".$row["field_id"]] = $row["value"];
279  }
280  }
281 
282  // (course) user agreement
283  if($check_agreement)
284  {
285  // admins/tutors (write-access) will never have agreement ?!
286  include_once "Services/Membership/classes/class.ilMemberAgreement.php";
287  $agreements = ilMemberAgreement::lookupAcceptedAgreements($obj_id);
288 
289 
290 
291  // public information for users
292  $query = "SELECT usr_id FROM usr_pref WHERE keyword = ".$ilDB->quote("public_profile", "text").
293  " AND value = ".$ilDB->quote("y", "text")." OR value = ".$ilDB->quote("g", "text");
294  $set = $ilDB->query($query);
295  $all_public = array();
296  while($row = $ilDB->fetchAssoc($set))
297  {
298  $all_public[] = $row["usr_id"];
299  }
300  $query = "SELECT usr_id,keyword FROM usr_pref WHERE ".$ilDB->like("keyword", "text", "public_%", false).
301  " AND value = ".$ilDB->quote("y", "text")." AND ".$ilDB->in("usr_id", $all_public, "", "integer");
302  $set = $ilDB->query($query);
303  $public = array();
304  while($row = $ilDB->fetchAssoc($set))
305  {
306  $public[$row["usr_id"]][] = substr($row["keyword"], 7);
307  }
308  unset($all_public);
309  }
310 
311  foreach($result["set"] as $idx => $row)
312  {
313  // add udf data
314  if(isset($udf[$row["usr_id"]]))
315  {
316  $result["set"][$idx] = $row = array_merge($row, $udf[$row["usr_id"]]);
317  }
318 
319  // remove all private data - if active agreement and agreement not given by user
320  if(sizeof($privacy_fields) && $check_agreement && !in_array($row["usr_id"], $agreements))
321  {
322  foreach($privacy_fields as $field)
323  {
324  // check against public profile
325  if(isset($row[$field]) && (!isset($public[$row["usr_id"]]) ||
326  !in_array($field, $public[$row["usr_id"]])))
327  {
328  // remove complete entry - offending field was filtered
329  if(isset($a_filters[$field]))
330  {
331  unset($result["set"][$idx]);
332  break;
333  }
334  // remove offending field
335  else
336  {
337  $result["set"][$idx][$field] = false;
338  }
339  }
340  }
341  }
342  }
343 
344  // as we cannot do this in the query, sort by custom field here
345  if($udf_order)
346  {
347  include_once "Services/Utilities/classes/class.ilStr.php";
348  $result["set"] = ilUtil::stableSortArray($result["set"],
349  $udf_order, $a_order_dir);
350  }
351  }
352  return $result;
353  }
354 
370  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,
371  array $a_filter = NULL, array $a_additional_fields = NULL, $use_collection = true)
372  {
373  global $ilDB;
374 
375  $fields = array("object_data.obj_id", "title", "type");
376  self::buildColumns($fields, $a_additional_fields);
377 
378  $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, $use_collection);
379 
380  $query = " FROM object_data LEFT JOIN read_event ON (object_data.obj_id = read_event.obj_id AND".
381  " read_event.usr_id = ".$ilDB->quote($a_user_id, "integer").")".
382  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = ".$ilDB->quote($a_user_id, "integer")." AND".
383  " ut_lp_marks.obj_id = object_data.obj_id)".
384  " WHERE ".$ilDB->in("object_data.obj_id", $objects["object_ids"], false, "integer").
385  self::buildFilters(array(), $a_filters);
386 
387  $queries = array();
388  $queries[] = array("fields"=>$fields, "query"=>$query);
389 
390  // objectives data
391  if($objects["objectives_parent_id"])
392  {
393  $objective_fields = array("crs_objectives.objective_id AS obj_id", "title",
394  $ilDB->quote("lobj", "text")." as type");
395 
396  if (is_array($a_additional_fields))
397  {
398  foreach($a_additional_fields as $field)
399  {
400  if($field != "status")
401  {
402  $objective_fields[] = "NULL AS ".$field;
403  }
404  else
405  {
406  include_once("Services/Tracking/classes/class.ilLPStatus.php");
407  $objective_fields[] = "(CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END) AS status";
408  }
409  }
410  }
411 
412  $where = array();
413  $where[] = "crs_objectives.crs_id = ".$ilDB->quote($objects["objectives_parent_id"], "integer");
414 
415  $objectives_query = " FROM crs_objectives".
416  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
417  " AND crs_objective_status.user_id = ".$ilDB->quote($a_user_id, "integer").")".
418  self::buildFilters($where, $a_filters);
419 
420  $queries[] = array("fields"=>$objective_fields, "query"=>$objectives_query, "count"=>"crs_objectives.objective_id");
421  }
422 
423  if(!in_array($a_order_field, $fields))
424  {
425  $a_order_field = "title";
426  }
427 
428  $result = self::executeQueries($queries, $a_order_field, $a_order_dir, $a_offset, $a_limit);
429  if($result["cnt"])
430  {
431  // session data
432  $sessions = self::getSessionData($a_user_id, $objects["object_ids"]);
433 
434  foreach($result["set"] as $idx => $item)
435  {
436  if($item["type"] == "sess")
437  {
438  $session = $sessions[$item["obj_id"]];
439  $result["set"][$idx]["title"] = $session["title"];
440  $result["set"][$idx]["sort_title"] = $session["e_start"];
441  // $result["set"][$idx]["status"] = (int)$session["status"];
442  }
443 
444  $result["set"][$idx]["ref_id"] = $objects["ref_ids"][$item["obj_id"]];
445  }
446 
447  // scos data (:TODO: will not be part of offset/limit)
448  if($objects["scorm"])
449  {
450  include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
451  $subtype = ilObjSAHSLearningModule::_lookupSubType($a_parent_obj_id);
452  if($subtype == "scorm2004")
453  {
454  include_once("./Modules/Scorm2004/classes/class.ilObjScorm2004LearningModule.php");
455  $sobj = new ilObjSCORM2004LearningModule($a_parent_ref_id, true);
456  $scos_tracking = $sobj->getTrackingDataAgg($a_user_id, true);
457  }
458  else
459  {
460  include_once("./Modules/ScormAicc/classes/class.ilObjScormLearningModule.php");
461  $sobj = new ilObjSCORMLearningModule($a_parent_ref_id, true);
462  $scos_tracking = array();
463  foreach($sobj->getTrackingDataAgg($a_user_id) as $item)
464  {
465  // format: hhhh:mm:ss ?!
466  if($item["time"])
467  {
468  $time = explode(":", $item["time"]);
469  $item["time"] = $time[0]*60*60+$time[1]*60+$time[2];
470  }
471  $scos_tracking[$item["sco_id"]] = array("session_time"=>$item["time"]);
472  }
473  }
474 
475  foreach($objects["scorm"]["scos"] as $sco)
476  {
477  $row = array("title" => $objects["scorm"]["scos_title"][$sco],
478  "type" => "sco");
479 
480  $status = LP_STATUS_NOT_ATTEMPTED_NUM;
481  if(in_array($a_user_id, $objects["scorm"]["completed"][$sco]))
482  {
483  $status = LP_STATUS_COMPLETED_NUM;
484  }
485  else if(in_array($a_user_id, $objects["scorm"]["failed"][$sco]))
486  {
487  $status = LP_STATUS_FAILED_NUM;
488  }
489  else if(in_array($a_user_id, $objects["scorm"]["in_progress"][$sco]))
490  {
491  $status = LP_STATUS_IN_PROGRESS_NUM;
492  }
493  $row["status"] = $status;
494 
495  // add available tracking data
496  if(isset($scos_tracking[$sco]))
497  {
498  if(isset($scos_tracking[$sco]["last_access"]))
499  {
500  $date = new ilDateTime($scos_tracking[$sco]["last_access"], IL_CAL_DATETIME);
501  $row["last_access"] = $date->get(IL_CAL_UNIX);
502  }
503  $row["spent_seconds"] = $scos_tracking[$sco]["session_time"];
504  }
505 
506  $result["set"][] = $row;
507  $result["cnt"]++;
508  }
509  }
510  }
511  return $result;
512  }
513 
521  protected static function getSessionData($a_user_id, array $obj_ids)
522  {
523  global $ilDB;
524 
525  $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,".
526  " mark, e_comment AS comment".
527  " FROM event".
528  " JOIN event_appointment ON (event.obj_id = event_appointment.event_id)".
529  " LEFT JOIN event_participants ON (event_participants.event_id = event.obj_id AND usr_id = ".$ilDB->quote($a_user_id, "integer").")".
530  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
531  $set = $ilDB->query($query);
532  $sessions = array();
533  while($rec = $ilDB->fetchAssoc($set))
534  {
535  $date = ilDatePresentation::formatPeriod(
536  new ilDateTime($rec["e_start"], IL_CAL_DATETIME),
537  new ilDateTime($rec["e_end"], IL_CAL_DATETIME));
538 
539  if($rec["title"])
540  {
541  $rec["title"] = $date.': '.$rec["title"];
542  }
543  else
544  {
545  $rec["title"] = $date;
546  }
547  $sessions[$rec["obj_id"]] = $rec;
548  }
549  return $sessions;
550  }
551 
568  static function getObjectsSummaryForObject($a_parent_obj_id, $a_parent_ref_id, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999,
569  array $a_filters = NULL, array $a_additional_fields = NULL, $use_collection = true)
570  {
571  global $ilDB;
572 
573  $fields = array();
574  self::buildColumns($fields, $a_additional_fields, true);
575 
576  $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false);
577 
578  // object data
579  $set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
580  " WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
581  while($rec = $ilDB->fetchAssoc($set))
582  {
583  $object_data[$rec["obj_id"]] = $rec;
584  }
585 
586  $result = array();
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  $result[] = array_merge($object_data[$object_id], $object_result);
593  }
594  }
595 
596  // :TODO: objectives
597  if($objects["objectives_parent_id"])
598  {
599 
600  }
601 
602  return array("cnt"=>sizeof($result), "set"=>$result);
603  }
604 
613  protected static function getSummaryDataForObject($a_ref_id, array $fields, array $a_filters = NULL)
614  {
615  global $ilDB;
616 
617  $where = array();
618  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
619 
620  // users
621  $a_users = self::getParticipantsForObject($a_ref_id);
622  $left = "";
623  if (is_array($a_users) && sizeof($a_users))
624  {
625  $left = "LEFT";
626  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
627  }
628 
629  $obj_id = ilObject::_lookupObjectId($a_ref_id);
630  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
631  " AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
632  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
633  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
634  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
635  self::buildFilters($where, $a_filters, true);
636 
637  $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
638 
639  $queries = array();
640  $queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
641 
642  $result = self::executeQueries($queries);
643  $result = $result["set"][0];
644  $users_no = $result["user_count"];
645 
646  $valid = true;
647  if(!$users_no)
648  {
649  $valid = false;
650  }
651  else if(isset($a_filters["user_total"]))
652  {
653  if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
654  {
655  $valid = false;
656  }
657  else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
658  {
659  $valid = false;
660  }
661  }
662 
663  if($valid)
664  {
665  $result["country"] = self::getSummaryPercentages("country", $query);
666  $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
667  $result["city"] = self::getSummaryPercentages("city", $query);
668  $result["gender"] = self::getSummaryPercentages("gender", $query);
669  $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
670  $result["status"] = self::getSummaryPercentages("status", $query);
671  $result["mark"] = self::getSummaryPercentages("mark", $query);
672  }
673  else
674  {
675  $result = array();
676  }
677 
678  if($result)
679  {
680  $result["user_total"] = $users_no;
681  }
682 
683  return $result;
684  }
685 
694  protected static function getSummaryPercentages($field, $base_query, $alias = NULL)
695  {
696  global $ilDB;
697 
698  if(!$alias)
699  {
700  $field_alias = $field;
701  }
702  else
703  {
704  $field_alias = $alias;
705  $alias = " AS ".$alias;
706  }
707 
708  // move having BEHIND group by
709  $having = "";
710  if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
711  {
712  $having = " HAVING ".$hits[1];
713  $base_query = str_replace($hits[0], "", $base_query);
714  }
715 
716  $query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
717  $set = $ilDB->query($query);
718  $result = array();
719  while($rec = $ilDB->fetchAssoc($set))
720  {
721  $result[$rec[$field_alias]] = (int)$rec["counter"];
722  }
723  return $result;
724  }
725 
732  public static function getParticipantsForObject($a_ref_id)
733  {
734  global $tree;
735 
736  $a_users = NULL;
737  $obj_id = ilObject::_lookupObjectId($a_ref_id);
738 
739  // @todo: move this to a parent or type related class later
740  switch(ilObject::_lookupType($obj_id))
741  {
742  case "crs":
743  include_once "Modules/Course/classes/class.ilCourseParticipants.php";
744  $member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
745  $a_users = $member_obj->getMembers();
746  break;
747 
748  case "grp":
749  include_once "Modules/Group/classes/class.ilGroupParticipants.php";
750  $member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
751  $a_users = $member_obj->getMembers();
752  break;
753 
754  case "sahs":
755  include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
756  $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
757  switch ($subtype)
758  {
759  case 'scorm2004':
760  /* based on cmi_gobjective, data is not mandatory?
761  include_once("./Modules/Scorm2004/classes/class.ilSCORM2004Tracking.php");
762  $a_users = ilSCORM2004Tracking::_getTrackedUsers($obj_id);
763  */
764 
765  // based on cmi_node/cp_node, used for scorm tracking data views
766  include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
767  $mod = new ilObjSCORM2004LearningModule($obj_id, false);
768  $all = $mod->getTrackedUsers("");
769  $a_users = array();
770  if($all)
771  {
772  foreach($all as $item)
773  {
774  $a_users[] = $item["user_id"];
775  }
776  }
777  break;
778 
779  default:
780  include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
781  $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
782  break;
783  }
784  break;
785 
786  case "exc":
787  include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
788  include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
789  $exc = new ilObjExercise($obj_id, false);
790  $members = new ilExerciseMembers($exc);
791  $a_users = $members->getMembers();
792  break;
793 
794  case "tst":
795  include_once("./Services/Tracking/classes/class.ilLPStatusTestFinished.php");
796  $a_users = ilLPStatusTestFinished::getParticipants($obj_id);
797  break;
798 
799  case "fold":
800  case "lm":
801  case "htlm":
802  case "dbk":
803  case "sess":
804  // walk path to find course or group object and use members of that object
805  $path = $tree->getPathId($a_ref_id);
806  array_pop($path);
807  foreach(array_reverse($path) as $path_ref_id)
808  {
809  $type = ilObject::_lookupType($path_ref_id, true);
810  if($type == "crs" || $type == "grp")
811  {
812  return self::getParticipantsForObject($path_ref_id);
813  }
814  }
815  break;
816  }
817 
818  return $a_users;
819  }
820 
829  static protected function buildFilters(array $where, array $a_filters = NULL, $a_aggregate = false)
830  {
831  global $ilDB;
832 
833  $having = array();
834 
835  if(sizeof($a_filters))
836  {
837  foreach($a_filters as $id => $value)
838  {
839  switch($id)
840  {
841  case "login":
842  case "firstname":
843  case "lastname":
844  case "institution":
845  case "department":
846  case "street":
847  case "email":
848  case "matriculation":
849  case "country":
850  case "city":
851  case "title":
852  $where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
853  break;
854 
855  case "gender":
856  case "zipcode":
857  case "sel_country":
858  $where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
859  break;
860 
861  case "u_comment":
862  $where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
863  break;
864 
865  case "status":
866  case "mark":
867  $where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
868  break;
869 
870 
871  case "percentage":
872  if(!$a_aggregate)
873  {
874  if($value["from"])
875  {
876  $where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
877  }
878  if($value["to"])
879  {
880  $where[] = "ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer");
881  }
882  }
883  else
884  {
885  if($value["from"])
886  {
887  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
888  }
889  if($value["to"])
890  {
891  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
892  }
893  }
894  break;
895 
896  case "language":
897  $where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
898  break;
899 
900  // timestamp
901  case "last_access":
902  if($value["from"])
903  {
904  $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
905  $value["from"] = $value["from"]->get(IL_CAL_UNIX);
906  }
907  if($value["to"])
908  {
909  $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
910  $value["to"] = $value["to"]->get(IL_CAL_UNIX);
911  }
912  // fallthrough
913 
914  case "registration":
915  if($id == "registration")
916  {
917  $id = "create_date";
918  }
919  // fallthrough
920 
921  case "create_date":
922  case "first_access":
923  case "birthday":
924  if($value["from"])
925  {
926  $where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
927  }
928  if($value["to"])
929  {
930  $where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
931  }
932  break;
933 
934  case "read_count":
935  if(!$a_aggregate)
936  {
937  if($value["from"])
938  {
939  $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
940  }
941  if($value["to"])
942  {
943  $where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
944  }
945  }
946  else
947  {
948  if($value["from"])
949  {
950  $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
951  }
952  if($value["to"])
953  {
954  $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
955  }
956  }
957  break;
958 
959  case "spent_seconds":
960  if(!$a_aggregate)
961  {
962  if($value["from"])
963  {
964  $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
965  }
966  if($value["to"])
967  {
968  $where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
969  }
970  }
971  else
972  {
973  if($value["from"])
974  {
975  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
976  }
977  if($value["to"])
978  {
979  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
980  }
981  }
982  break;
983 
984  default:
985  // var_dump("unknown: ".$id);
986  break;
987  }
988  }
989  }
990 
991  $sql = "";
992  if(sizeof($where))
993  {
994  $sql .= " WHERE ".implode(" AND ", $where);
995  }
996  if(sizeof($having))
997  {
998  // ugly "having" hack because of summary view
999  $sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
1000  }
1001 
1002  return $sql;
1003  }
1004 
1013  static protected function buildColumns(array &$a_fields, array $a_additional_fields = NULL, $a_aggregate = false)
1014  {
1015  if(sizeof($a_additional_fields))
1016  {
1017  $udf = NULL;
1018  foreach($a_additional_fields as $field)
1019  {
1020  if(substr($field, 0, 4) != "udf_")
1021  {
1022  $function = NULL;
1023  if($a_aggregate)
1024  {
1025  $pos = strrpos($field, "_");
1026  if($pos === false)
1027  {
1028  continue;
1029  }
1030  $function = strtoupper(substr($field, $pos+1));
1031  $field = substr($field, 0, $pos);
1032  if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
1033  {
1034  continue;
1035  }
1036  }
1037 
1038  switch($field)
1039  {
1040  case "language":
1041  if($function)
1042  {
1043  $a_fields[] = $function."(value) AS ".$field."_".strtolower($function);
1044  }
1045  else
1046  {
1047  $a_fields[] = "value AS ".$field;
1048  }
1049  break;
1050 
1051  case "read_count":
1052  case "spent_seconds":
1053  if(!$function)
1054  {
1055  $a_fields[] = "(".$field."+childs_".$field.") AS ".$field;
1056  }
1057  else
1058  {
1059  if($function == "AVG")
1060  {
1061  $a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) AS ".$field."_".strtolower($function);
1062  }
1063  else
1064  {
1065  $a_fields[] = $function."(".$field."+childs_".$field.") AS ".$field."_".strtolower($function);
1066  }
1067  }
1068  break;
1069 
1070  default:
1071  if($function)
1072  {
1073  if($function == "AVG")
1074  {
1075  $a_fields[] = "ROUND(AVG(".$field."), 2) AS ".$field."_".strtolower($function);
1076  }
1077  else
1078  {
1079  $a_fields[] = $function."(".$field.") AS ".$field."_".strtolower($function);
1080  }
1081  }
1082  else
1083  {
1084  $a_fields[] = $field;
1085  }
1086  break;
1087  }
1088  }
1089  else
1090  {
1091  $udf[] = substr($field, 4);
1092  }
1093  }
1094  return $udf;
1095  }
1096  }
1097 
1106  static public function getObjectIds($a_parent_obj_id, $a_parent_ref_id = false, $use_collection = true)
1107  {
1108  global $tree;
1109 
1110  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
1111 
1112  $object_ids = array($a_parent_obj_id);
1113  $ref_ids = array($a_parent_obj_id=>$a_parent_ref_id);
1114  $objectives_parent_id = $scorm = false;
1115 
1116  $mode = ilLPObjSettings::_lookupMode($a_parent_obj_id);
1117  switch($mode)
1118  {
1119  // what about LP_MODE_SCORM_PACKAGE ?
1120  case LP_MODE_SCORM:
1121  include_once "Services/Tracking/classes/class.ilLPStatusSCORM.php";
1122  $status_scorm = new ilLPStatusSCORM($a_parent_obj_id);
1123  $scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
1124  break;
1125 
1126  case LP_MODE_OBJECTIVES:
1127  if(ilObject::_lookupType($a_parent_obj_id) == "crs")
1128  {
1129  $objectives_parent_id = $a_parent_obj_id;
1130  }
1131  break;
1132 
1133  default:
1134  // lp collection
1135  if($use_collection)
1136  {
1137  include_once 'Services/Tracking/classes/class.ilLPCollectionCache.php';
1138  foreach(ilLPCollectionCache::_getItems($a_parent_obj_id) as $child_ref_id)
1139  {
1140  $child_id = ilObject::_lookupObjId($child_ref_id);
1141  $object_ids[] = $child_id;
1142  $ref_ids[$child_id] = $child_ref_id;
1143  }
1144  }
1145  // all objects in branch
1146  else
1147  {
1148  self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1149  $object_ids = array_unique($object_ids);
1150  }
1151 
1152  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1153  foreach($object_ids as $idx => $object_id)
1154  {
1155  if($object_id)
1156  {
1157  ilLPStatus::checkStatusForObject($object_id);
1158  }
1159  else
1160  {
1161  unset($object_ids[$idx]);
1162  }
1163  }
1164  break;
1165  }
1166 
1167  return array("object_ids" => $object_ids,
1168  "ref_ids" => $ref_ids,
1169  "objectives_parent_id" => $objectives_parent_id,
1170  "scorm" => $scorm);
1171  }
1172 
1180  static protected function getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
1181  {
1182  global $tree;
1183 
1184  $children = $tree->getChilds($a_parent_ref_id);
1185  if($children)
1186  {
1187  foreach($children as $child)
1188  {
1189  // as there can be deactivated items in the collection
1190  // we should allow them here too
1191  $cmode = ilLPObjSettings::_lookupMode($child["obj_id"]);
1192  if(/* $cmode != LP_MODE_DEACTIVATED && */ $cmode != LP_MODE_UNDEFINED)
1193  {
1194  $a_object_ids[] = $child["obj_id"];
1195  $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1196  }
1197 
1198  self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1199  }
1200  }
1201  }
1202 
1213  static function executeQueries(array $queries, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999)
1214  {
1215  global $ilDB;
1216 
1217  $cnt = 0;
1218  $subqueries = array();
1219  foreach($queries as $item)
1220  {
1221  // ugly "having" hack because of summary view
1222  $item = str_replace("[[--HAVING", "HAVING", $item);
1223  $item = str_replace("HAVING--]]", "", $item);
1224 
1225  if(!isset($item["count"]))
1226  {
1227  $count_field = $item["fields"];
1228  $count_field = array_shift($count_field);
1229  }
1230  else
1231  {
1232  $count_field = $item["count"];
1233  }
1234  $count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
1235  $set = $ilDB->query($count_query);
1236  if ($rec = $ilDB->fetchAssoc($set))
1237  {
1238  $cnt += $rec["cnt"];
1239  }
1240 
1241  $subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
1242  }
1243 
1244  // set query
1245  $result = array();
1246  if($cnt > 0)
1247  {
1248  if(sizeof($subqueries) > 1)
1249  {
1250  $base = array_shift($subqueries);
1251  $query = $base." UNION (".implode(") UNION (", $subqueries).")";
1252  }
1253  else
1254  {
1255  $query = $subqueries[0];
1256  }
1257 
1258  if ($a_order_dir != "asc" && $a_order_dir != "desc")
1259  {
1260  $a_order_dir = "asc";
1261  }
1262  if($a_order_field)
1263  {
1264  $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
1265  }
1266 
1267  $offset = (int) $a_offset;
1268  $limit = (int) $a_limit;
1269  $ilDB->setLimit($limit, $offset);
1270 
1271  $set = $ilDB->query($query);
1272  while($rec = $ilDB->fetchAssoc($set))
1273  {
1274  $result[] = $rec;
1275  }
1276  }
1277 
1278  return array("cnt" => $cnt, "set" => $result);
1279  }
1280 
1289  static function getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter = NULL)
1290  {
1291  global $ilDB;
1292 
1293  $result = array("cnt"=>0, "set"=>NULL);
1294  if(sizeof($a_obj_ids))
1295  {
1296  $where = array();
1297  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
1298  if($a_user_filter)
1299  {
1300  $where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
1301  }
1302 
1303  // users
1304  $left = "";
1305  $a_users = self::getParticipantsForObject($a_parent_ref_id);
1306  if (is_array($a_users))
1307  {
1308  $left = "LEFT";
1309  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1310  }
1311 
1312  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1313 
1314  $fields = array("usr_data.usr_id", "login", "status", "percentage",
1315  "last_access", "spent_seconds+childs_spent_seconds as spent_seconds");
1316 
1317  if(!$a_order_field)
1318  {
1319  $a_order_field = "login";
1320  }
1321 
1322  $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1323  $raw = array();
1324  foreach($a_obj_ids as $obj_id)
1325  {
1326  // check status
1327  ilLPStatus::checkStatusForObject($obj_id, $a_users);
1328 
1329  // one request for each object
1330  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
1331  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1332  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
1333  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1334  self::buildFilters($where, $a_filters);
1335 
1336  $raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), $a_order_field);
1337  if($raw["cnt"])
1338  {
1339  // convert to final structure
1340  foreach($raw["set"] as $idx => $row)
1341  {
1342  $result["set"][$row["usr_id"]]["login"] = $row["login"];
1343  $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1344  $result["set"][$row["usr_id"]]["objects"][$obj_id] = array("status"=>$row["status"],
1345  "percentage"=>$row["percentage"]);
1346  if($obj_id == $parent_obj_id)
1347  {
1348  $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1349  $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1350  }
1351  }
1352  }
1353  }
1354 
1355  $result["cnt"] = sizeof($result["set"]);
1356  $result["users"] = $a_users;
1357  }
1358  return $result;
1359  }
1360 
1361  static public function getUserObjectiveMatrix($a_parent_obj_id, $a_users)
1362  {
1363  global $ilDB;
1364 
1365  if($a_parent_obj_id && $a_users)
1366  {
1367  include_once("Services/Tracking/classes/class.ilLPStatus.php");
1368 
1369  $fields = array("crs_objectives.objective_id AS obj_id", "crs_objective_status.user_id AS usr_id", "title");
1370  $fields[] = "(CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END) AS status";
1371 
1372  $where = array();
1373  $where[] = "crs_objectives.crs_id = ".$ilDB->quote($a_parent_obj_id, "integer");
1374 
1375  $query = " FROM crs_objectives".
1376  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
1377  " AND ".$ilDB->in("crs_objective_status.user_id", $a_users, "", "integer").")".
1378  self::buildFilters($where);
1379 
1380  return self::executeQueries(array(array("fields"=>$fields, "query"=>$query, "count"=>"crs_objectives.objective_id")));
1381  }
1382  }
1383 }
1384 
1385 ?>