ILIAS  Release_4_1_x_branch Revision 61804
 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  " 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 $obj_ids)
91  {
92  global $ilDB;
93 
94  self::refreshObjectsStatus($obj_ids, array($a_user_id));
95 
96  $query = "SELECT crs_id, crs_objectives.objective_id AS obj_id, title, status, ".$ilDB->quote("lobj", "text")." AS type".
97  " FROM crs_objectives".
98  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id AND user_id = ".$a_user_id.")".
99  " WHERE ".$ilDB->in("crs_objectives.objective_id", $obj_ids, false, "integer").
100  " ORDER BY position";
101  $set = $ilDB->query($query);
102  $result = array();
103  while($rec = $ilDB->fetchAssoc($set))
104  {
105  if($rec["status"])
106  {
107  $rec["status"] = LP_STATUS_COMPLETED_NUM;
108  }
109  $result[] = $rec;
110  }
111 
112  return $result;
113  }
114 
115  function getSCOsStatusForUser($a_user_id, $a_parent_obj_id, array $a_sco_ids)
116  {
117  self::refreshObjectsStatus(array($a_parent_obj_id), array($a_user_id));
118 
119  include_once 'Services/Tracking/classes/class.ilLPStatusWrapper.php';
120  $status_info = ilLPStatusWrapper::_getStatusInfo($a_parent_obj_id);
121 
122  $items = array();
123  foreach($a_sco_ids as $sco_id)
124  {
125  if(in_array($a_user_id, $status_info["in_progress"][$sco_id]))
126  {
127  $status = LP_STATUS_IN_PROGRESS;
128  }
129  elseif(in_array($a_user_id, $status_info["completed"][$sco_id]))
130  {
131  $status = LP_STATUS_COMPLETED;
132  }
133  elseif(in_array($a_user_id, $status_info["failed"][$sco_id]))
134  {
135  $status = LP_STATUS_FAILED;
136  }
137  else
138  {
139  $status = LP_STATUS_NOT_ATTEMPTED;
140  }
141 
142  $items[$sco_id] = array(
143  "title" => $status_info["scos_title"][$sco_id],
144  "status" => $status,
145  "type" => "sahs"
146  );
147  }
148 
149  return $items;
150  }
151 
152  function getObjectsStatus(array $obj_refs)
153  {
154  global $ilDB;
155 
156  if(sizeof($obj_refs))
157  {
158  $obj_ids = array_keys($obj_refs);
159  self::refreshObjectsStatus($obj_ids);
160 
161  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
162  include_once "Services/Tracking/classes/class.ilLPStatus.php";
163 
164  // prepare object view modes
165  include_once 'Modules/Course/classes/class.ilObjCourse.php';
166  $view_modes = array();
167  $query = "SELECT obj_id, view_mode FROM crs_settings".
168  " WHERE ".$ilDB->in("obj_id", $obj_ids , false, "integer");
169  $set = $ilDB->query($query);
170  while($rec = $ilDB->fetchAssoc($set))
171  {
172  $view_modes[(int)$rec["obj_id"]] = (int)$rec["view_mode"];
173  }
174 
175  $query = "SELECT object_data.obj_id, title, u_mode, type".
176  " FROM object_data".
177  " LEFT JOIN ut_lp_settings ON (ut_lp_settings.obj_id = object_data.obj_id)".
178  " WHERE (u_mode IS NULL OR u_mode <> ".$ilDB->quote(LP_MODE_DEACTIVATED, "integer").")".
179  " AND ".$ilDB->in("object_data.obj_id", $obj_ids, false, "integer").
180  " GROUP BY object_data.obj_id, title, u_mode, type".
181  " ORDER BY title";
182  $set = $ilDB->query($query);
183  $result = array();
184  while($rec = $ilDB->fetchAssoc($set))
185  {
186  $rec["ref_ids"] = $obj_refs[(int)$rec["obj_id"]];
187  $rec["status"] = (int)$rec["status"];
188  $rec["u_mode"] = (int)$rec["u_mode"];
189 
190  $rec['status_in_progress'] = ilLPStatusWrapper::_getCountInProgress((int)$rec["obj_id"]);
191  $rec['status_completed'] = ilLPStatusWrapper::_getCountCompleted((int)$rec["obj_id"]);
192  $rec['status_failed'] = ilLPStatusWrapper::_getCountFailed((int)$rec["obj_id"]);
193  $rec['status_not_attempted'] = ilLPStatusWrapper::_getCountNotAttempted((int)$rec["obj_id"]);
194 
195  // lp mode might not match object/course view mode
196  if($rec["type"] == "crs" && $view_modes[$rec["obj_id"]] == IL_CRS_VIEW_OBJECTIVE)
197  {
198  $rec["u_mode"] = LP_MODE_OBJECTIVES;
199  }
200  else if(!$rec["u_mode"])
201  {
202  $rec["u_mode"] = ilLPObjSettings::__getDefaultMode($rec["obj_id"], $rec["type"]);
203  }
204 
205  // can be default mode
206  if($rec["u_mode"] != LP_MODE_DEACTIVATE)
207  {
208  $result[$rec["obj_id"]] = $rec;
209  }
210  }
211  return $result;
212  }
213  }
214 
229  static function getUserDataForObject($a_ref_id, $a_order_field = "", $a_order_dir = "",
230  $a_offset = 0, $a_limit = 9999, array $a_filters = NULL, array $a_additional_fields = NULL,
231  $check_agreement = false, $privacy_fields = NULL)
232  {
233  global $ilDB;
234 
235  $fields = array("usr_data.usr_id", "login");
236  $udf = self::buildColumns($fields, $a_additional_fields);
237 
238  $where = array();
239  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
240 
241  // users
242  $left = "";
243  $a_users = self::getParticipantsForObject($a_ref_id);
244 
245  $obj_id = ilObject::_lookupObjectId($a_ref_id);
246  self::refreshObjectsStatus(array($obj_id), $a_users);
247 
248  if (is_array($a_users))
249  {
250  $left = "LEFT";
251  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
252  }
253 
254  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
255  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
256  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
257  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
258  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
259  self::buildFilters($where, $a_filters);
260 
261  $queries = array(array("fields"=>$fields, "query"=>$query));
262 
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, $use_collection = true)
577  {
578  global $ilDB;
579 
580  $fields = array();
581  self::buildColumns($fields, $a_additional_fields, true);
582 
583  $objects = self::getObjectIds($a_parent_obj_id, $a_parent_ref_id, false, false);
584 
585  // object data
586  $set = $ilDB->query("SELECT obj_id,title,type FROM object_data".
587  " WHERE ".$ilDB->in("obj_id", $objects["object_ids"], false, "integer"));
588  while($rec = $ilDB->fetchAssoc($set))
589  {
590  $object_data[$rec["obj_id"]] = $rec;
591  }
592 
593  $result = array();
594  foreach($objects["ref_ids"] as $object_id => $ref_id)
595  {
596  $object_result = self::getSummaryDataForObject($ref_id, $fields, $a_filters);
597  if(sizeof($object_result))
598  {
599  $result[] = array_merge($object_data[$object_id], $object_result);
600  }
601  }
602 
603  // :TODO: objectives
604  if($objects["objectives_parent_id"])
605  {
606 
607  }
608 
609  return array("cnt"=>sizeof($result), "set"=>$result);
610  }
611 
620  protected static function getSummaryDataForObject($a_ref_id, array $fields, array $a_filters = NULL)
621  {
622  global $ilDB;
623 
624  $where = array();
625  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
626 
627  // users
628  $a_users = self::getParticipantsForObject($a_ref_id);
629  $left = "";
630  if (is_array($a_users) && sizeof($a_users))
631  {
632  $left = "LEFT";
633  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
634  }
635 
636  $obj_id = ilObject::_lookupObjectId($a_ref_id);
637  self::refreshObjectsStatus(array($obj_id), $a_users);
638 
639  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
640  " AND obj_id = ".$ilDB->quote($obj_id, "integer").")".
641  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
642  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
643  " LEFT JOIN usr_pref ON (usr_pref.usr_id = usr_data.usr_id AND keyword = ".$ilDB->quote("language", "text").")".
644  self::buildFilters($where, $a_filters, true);
645 
646  $fields[] = 'COUNT(usr_data.usr_id) AS user_count';
647 
648  $queries = array();
649  $queries[] = array("fields"=>$fields, "query"=>$query, "count"=>"*");
650 
651  $result = self::executeQueries($queries);
652  $result = $result["set"][0];
653  $users_no = $result["user_count"];
654 
655  $valid = true;
656  if(!$users_no)
657  {
658  $valid = false;
659  }
660  else if(isset($a_filters["user_total"]))
661  {
662  if($a_filters["user_total"]["from"] && $users_no < $a_filters["user_total"]["from"])
663  {
664  $valid = false;
665  }
666  else if($a_filters["user_total"]["to"] && $users_no > $a_filters["user_total"]["to"])
667  {
668  $valid = false;
669  }
670  }
671 
672  if($valid)
673  {
674  $result["country"] = self::getSummaryPercentages("country", $query);
675  $result["sel_country"] = self::getSummaryPercentages("sel_country", $query);
676  $result["city"] = self::getSummaryPercentages("city", $query);
677  $result["gender"] = self::getSummaryPercentages("gender", $query);
678  $result["language"] = self::getSummaryPercentages("usr_pref.value", $query, "language");
679  $result["status"] = self::getSummaryPercentages("status", $query);
680  $result["mark"] = self::getSummaryPercentages("mark", $query);
681  }
682  else
683  {
684  $result = array();
685  }
686 
687  if($result)
688  {
689  $result["user_total"] = $users_no;
690  }
691 
692  return $result;
693  }
694 
703  protected static function getSummaryPercentages($field, $base_query, $alias = NULL)
704  {
705  global $ilDB;
706 
707  if(!$alias)
708  {
709  $field_alias = $field;
710  }
711  else
712  {
713  $field_alias = $alias;
714  $alias = " AS ".$alias;
715  }
716 
717  // move having BEHIND group by
718  $having = "";
719  if(preg_match("/".preg_quote(" [[--HAVING")."(.+)".preg_quote("HAVING--]]")."/", $base_query, $hits))
720  {
721  $having = " HAVING ".$hits[1];
722  $base_query = str_replace($hits[0], "", $base_query);
723  }
724 
725  $query = "SELECT COUNT(*) AS counter, ".$field.$alias." ".$base_query. " GROUP BY ".$field.$having." ORDER BY counter DESC";
726  $set = $ilDB->query($query);
727  $result = array();
728  while($rec = $ilDB->fetchAssoc($set))
729  {
730  $result[$rec[$field_alias]] = (int)$rec["counter"];
731  }
732  return $result;
733  }
734 
741  public static function getParticipantsForObject($a_ref_id)
742  {
743  global $tree;
744 
745  $a_users = NULL;
746  $obj_id = ilObject::_lookupObjectId($a_ref_id);
747 
748  // @todo: move this to a parent or type related class later
749  switch(ilObject::_lookupType($obj_id))
750  {
751  case "crs":
752  include_once "Modules/Course/classes/class.ilCourseParticipants.php";
753  $member_obj = ilCourseParticipants::_getInstanceByObjId($obj_id);
754  $a_users = $member_obj->getMembers();
755  break;
756 
757  case "grp":
758  include_once "Modules/Group/classes/class.ilGroupParticipants.php";
759  $member_obj = ilGroupParticipants::_getInstanceByObjId($obj_id);
760  $a_users = $member_obj->getMembers();
761  break;
762 
763  case "sahs":
764  include_once("./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php");
765  $subtype = ilObjSAHSLearningModule::_lookupSubType($obj_id);
766  switch ($subtype)
767  {
768  case 'scorm2004':
769  /* based on cmi_gobjective, data is not mandatory?
770  include_once("./Modules/Scorm2004/classes/class.ilSCORM2004Tracking.php");
771  $a_users = ilSCORM2004Tracking::_getTrackedUsers($obj_id);
772  */
773 
774  // based on cmi_node/cp_node, used for scorm tracking data views
775  include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
776  $mod = new ilObjSCORM2004LearningModule($obj_id, false);
777  $all = $mod->getTrackedUsers("");
778  $a_users = array();
779  if($all)
780  {
781  foreach($all as $item)
782  {
783  $a_users[] = $item["user_id"];
784  }
785  }
786  break;
787 
788  default:
789  include_once("./Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php");
790  $a_users = ilObjSCORMTracking::_getTrackedUsers($obj_id);
791  break;
792  }
793  break;
794 
795  case "exc":
796  include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
797  include_once("./Modules/Exercise/classes/class.ilObjExercise.php");
798  $exc = new ilObjExercise($obj_id, false);
799  $members = new ilExerciseMembers($exc);
800  $a_users = $members->getMembers();
801  break;
802 
803  case "tst":
804  include_once("./Services/Tracking/classes/class.ilLPStatusTestFinished.php");
805  $a_users = ilLPStatusTestFinished::getParticipants($obj_id);
806  break;
807 
808  case "fold":
809  case "lm":
810  case "htlm":
811  case "dbk":
812  case "sess":
813  // walk path to find course or group object and use members of that object
814  $path = $tree->getPathId($a_ref_id);
815  array_pop($path);
816  foreach(array_reverse($path) as $path_ref_id)
817  {
818  $type = ilObject::_lookupType($path_ref_id, true);
819  if($type == "crs" || $type == "grp")
820  {
821  return self::getParticipantsForObject($path_ref_id);
822  }
823  }
824  break;
825  }
826 
827  return $a_users;
828  }
829 
838  static protected function buildFilters(array $where, array $a_filters = NULL, $a_aggregate = false)
839  {
840  global $ilDB;
841 
842  $having = array();
843 
844  if(sizeof($a_filters))
845  {
846  foreach($a_filters as $id => $value)
847  {
848  switch($id)
849  {
850  case "login":
851  case "firstname":
852  case "lastname":
853  case "institution":
854  case "department":
855  case "street":
856  case "email":
857  case "matriculation":
858  case "country":
859  case "city":
860  case "title":
861  $where[] = $ilDB->like("usr_data.".$id, "text", "%".$value."%");
862  break;
863 
864  case "gender":
865  case "zipcode":
866  case "sel_country":
867  $where[] = "usr_data.".$id." = ".$ilDB->quote($value ,"text");
868  break;
869 
870  case "u_comment":
871  $where[] = $ilDB->like("ut_lp_marks.".$id, "text", "%".$value."%");
872  break;
873 
874  case "status":
875  case "mark":
876  $where[] = "ut_lp_marks.".$id." = ".$ilDB->quote($value ,"text");
877  break;
878 
879 
880  case "percentage":
881  if(!$a_aggregate)
882  {
883  if($value["from"])
884  {
885  $where[] = "ut_lp_marks.".$id." >= ".$ilDB->quote($value["from"] ,"integer");
886  }
887  if($value["to"])
888  {
889  $where[] = "ut_lp_marks.".$id." <= ".$ilDB->quote($value["to"] ,"integer");
890  }
891  }
892  else
893  {
894  if($value["from"])
895  {
896  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
897  }
898  if($value["to"])
899  {
900  $having[] = "ROUND(AVG(ut_lp_marks.".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
901  }
902  }
903  break;
904 
905  case "language":
906  $where[] = "usr_pref.value = ".$ilDB->quote($value ,"text");
907  break;
908 
909  // timestamp
910  case "last_access":
911  if($value["from"])
912  {
913  $value["from"] = new ilDateTime($value["from"], IL_CAL_DATETIME);
914  $value["from"] = $value["from"]->get(IL_CAL_UNIX);
915  }
916  if($value["to"])
917  {
918  $value["to"] = new ilDateTime($value["to"], IL_CAL_DATETIME);
919  $value["to"] = $value["to"]->get(IL_CAL_UNIX);
920  }
921  // fallthrough
922 
923  case "registration":
924  if($id == "registration")
925  {
926  $id = "create_date";
927  }
928  // fallthrough
929 
930  case "create_date":
931  case "first_access":
932  case "birthday":
933  if($value["from"])
934  {
935  $where[] = $id." >= ".$ilDB->quote($value["from"] ,"date");
936  }
937  if($value["to"])
938  {
939  $where[] = $id." <= ".$ilDB->quote($value["to"] ,"date");
940  }
941  break;
942 
943  case "read_count":
944  if(!$a_aggregate)
945  {
946  if($value["from"])
947  {
948  $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
949  }
950  if($value["to"])
951  {
952  $where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
953  }
954  }
955  else
956  {
957  if($value["from"])
958  {
959  $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
960  }
961  if($value["to"])
962  {
963  $having[] = "SUM(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
964  }
965  }
966  break;
967 
968  case "spent_seconds":
969  if(!$a_aggregate)
970  {
971  if($value["from"])
972  {
973  $where[] = "(read_event.".$id."+read_event.childs_".$id.") >= ".$ilDB->quote($value["from"] ,"integer");
974  }
975  if($value["to"])
976  {
977  $where[] = "(read_event.".$id."+read_event.childs_".$id.") <= ".$ilDB->quote($value["to"] ,"integer");
978  }
979  }
980  else
981  {
982  if($value["from"])
983  {
984  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) >= ".$ilDB->quote($value["from"] ,"integer");
985  }
986  if($value["to"])
987  {
988  $having[] = "ROUND(AVG(read_event.".$id."+read_event.childs_".$id.")) <= ".$ilDB->quote($value["to"] ,"integer");
989  }
990  }
991  break;
992 
993  default:
994  // var_dump("unknown: ".$id);
995  break;
996  }
997  }
998  }
999 
1000  $sql = "";
1001  if(sizeof($where))
1002  {
1003  $sql .= " WHERE ".implode(" AND ", $where);
1004  }
1005  if(sizeof($having))
1006  {
1007  // ugly "having" hack because of summary view
1008  $sql .= " [[--HAVING ".implode(" AND ", $having)."HAVING--]]";
1009  }
1010 
1011  return $sql;
1012  }
1013 
1022  static protected function buildColumns(array &$a_fields, array $a_additional_fields = NULL, $a_aggregate = false)
1023  {
1024  if(sizeof($a_additional_fields))
1025  {
1026  $udf = NULL;
1027  foreach($a_additional_fields as $field)
1028  {
1029  if(substr($field, 0, 4) != "udf_")
1030  {
1031  $function = NULL;
1032  if($a_aggregate)
1033  {
1034  $pos = strrpos($field, "_");
1035  if($pos === false)
1036  {
1037  continue;
1038  }
1039  $function = strtoupper(substr($field, $pos+1));
1040  $field = substr($field, 0, $pos);
1041  if(!in_array($function, array("MIN", "MAX", "SUM", "AVG", "COUNT")))
1042  {
1043  continue;
1044  }
1045  }
1046 
1047  switch($field)
1048  {
1049  case "language":
1050  if($function)
1051  {
1052  $a_fields[] = $function."(value) AS ".$field."_".strtolower($function);
1053  }
1054  else
1055  {
1056  $a_fields[] = "value AS ".$field;
1057  }
1058  break;
1059 
1060  case "read_count":
1061  case "spent_seconds":
1062  if(!$function)
1063  {
1064  $a_fields[] = "(".$field."+childs_".$field.") AS ".$field;
1065  }
1066  else
1067  {
1068  if($function == "AVG")
1069  {
1070  $a_fields[] = "ROUND(AVG(".$field."+childs_".$field."), 2) AS ".$field."_".strtolower($function);
1071  }
1072  else
1073  {
1074  $a_fields[] = $function."(".$field."+childs_".$field.") AS ".$field."_".strtolower($function);
1075  }
1076  }
1077  break;
1078 
1079  default:
1080  if($function)
1081  {
1082  if($function == "AVG")
1083  {
1084  $a_fields[] = "ROUND(AVG(".$field."), 2) AS ".$field."_".strtolower($function);
1085  }
1086  else
1087  {
1088  $a_fields[] = $function."(".$field.") AS ".$field."_".strtolower($function);
1089  }
1090  }
1091  else
1092  {
1093  $a_fields[] = $field;
1094  }
1095  break;
1096  }
1097  }
1098  else
1099  {
1100  $udf[] = substr($field, 4);
1101  }
1102  }
1103  return $udf;
1104  }
1105  }
1106 
1117  static public function getObjectIds($a_parent_obj_id, $a_parent_ref_id = false, $use_collection = true, $a_refresh_status = true, $a_user_ids = null)
1118  {
1119  include_once "Services/Tracking/classes/class.ilLPObjSettings.php";
1120 
1121  $object_ids = array($a_parent_obj_id);
1122  $ref_ids = array($a_parent_obj_id => $a_parent_ref_id);
1123  $objectives_parent_id = $scorm = false;
1124 
1125  $mode = ilLPObjSettings::_lookupMode($a_parent_obj_id);
1126  switch($mode)
1127  {
1128  // what about LP_MODE_SCORM_PACKAGE ?
1129  case LP_MODE_SCORM:
1130  include_once "Services/Tracking/classes/class.ilLPStatusSCORM.php";
1131  $status_scorm = new ilLPStatusSCORM($a_parent_obj_id);
1132  $scorm = $status_scorm->_getStatusInfo($a_parent_obj_id);
1133  break;
1134 
1135  case LP_MODE_OBJECTIVES:
1136  if(ilObject::_lookupType($a_parent_obj_id) == "crs")
1137  {
1138  $objectives_parent_id = $a_parent_obj_id;
1139  }
1140  break;
1141 
1142  default:
1143  // lp collection
1144  if($use_collection)
1145  {
1146  include_once 'Services/Tracking/classes/class.ilLPCollectionCache.php';
1147  foreach(ilLPCollectionCache::_getItems($a_parent_obj_id) as $child_ref_id)
1148  {
1149  $child_id = ilObject::_lookupObjId($child_ref_id);
1150  $object_ids[] = $child_id;
1151  $ref_ids[$child_id] = $child_ref_id;
1152  }
1153  }
1154  // all objects in branch
1155  else
1156  {
1157  self::getSubTree($a_parent_ref_id, $object_ids, $ref_ids);
1158  $object_ids = array_unique($object_ids);
1159  }
1160 
1161  foreach($object_ids as $idx => $object_id)
1162  {
1163  if(!$object_id)
1164  {
1165  unset($object_ids[$idx]);
1166  }
1167  }
1168  break;
1169  }
1170 
1171  if($a_refresh_status)
1172  {
1173  self::refreshObjectsStatus($object_ids, $a_user_ids);
1174  }
1175 
1176  return array("object_ids" => $object_ids,
1177  "ref_ids" => $ref_ids,
1178  "objectives_parent_id" => $objectives_parent_id,
1179  "scorm" => $scorm);
1180  }
1181 
1189  static protected function getSubTree($a_parent_ref_id, array &$a_object_ids, array &$a_ref_ids)
1190  {
1191  global $tree;
1192 
1193  $children = $tree->getChilds($a_parent_ref_id);
1194  if($children)
1195  {
1196  foreach($children as $child)
1197  {
1198  // as there can be deactivated items in the collection
1199  // we should allow them here too
1200  $cmode = ilLPObjSettings::_lookupMode($child["obj_id"]);
1201  if(/* $cmode != LP_MODE_DEACTIVATED && */ $cmode != LP_MODE_UNDEFINED)
1202  {
1203  $a_object_ids[] = $child["obj_id"];
1204  $a_ref_ids[$child["obj_id"]] = $child["ref_id"];
1205  }
1206 
1207  self::getSubTree($child["ref_id"], $a_object_ids, $a_ref_ids);
1208  }
1209  }
1210  }
1211 
1222  static function executeQueries(array $queries, $a_order_field = "", $a_order_dir = "", $a_offset = 0, $a_limit = 9999)
1223  {
1224  global $ilDB;
1225 
1226  $cnt = 0;
1227  $subqueries = array();
1228  foreach($queries as $item)
1229  {
1230  // ugly "having" hack because of summary view
1231  $item = str_replace("[[--HAVING", "HAVING", $item);
1232  $item = str_replace("HAVING--]]", "", $item);
1233 
1234  if(!isset($item["count"]))
1235  {
1236  $count_field = $item["fields"];
1237  $count_field = array_shift($count_field);
1238  }
1239  else
1240  {
1241  $count_field = $item["count"];
1242  }
1243  $count_query = "SELECT COUNT(".$count_field.") AS cnt".$item["query"];
1244  $set = $ilDB->query($count_query);
1245  if ($rec = $ilDB->fetchAssoc($set))
1246  {
1247  $cnt += $rec["cnt"];
1248  }
1249 
1250  $subqueries[] = "SELECT ".implode(",", $item["fields"]).$item["query"];
1251  }
1252 
1253  // set query
1254  $result = array();
1255  if($cnt > 0)
1256  {
1257  if(sizeof($subqueries) > 1)
1258  {
1259  $base = array_shift($subqueries);
1260  $query = $base." UNION (".implode(") UNION (", $subqueries).")";
1261  }
1262  else
1263  {
1264  $query = $subqueries[0];
1265  }
1266 
1267  if ($a_order_dir != "asc" && $a_order_dir != "desc")
1268  {
1269  $a_order_dir = "asc";
1270  }
1271  if($a_order_field)
1272  {
1273  $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
1274  }
1275 
1276  $offset = (int) $a_offset;
1277  $limit = (int) $a_limit;
1278  $ilDB->setLimit($limit, $offset);
1279 
1280  $set = $ilDB->query($query);
1281  while($rec = $ilDB->fetchAssoc($set))
1282  {
1283  $result[] = $rec;
1284  }
1285  }
1286 
1287  return array("cnt" => $cnt, "set" => $result);
1288  }
1289 
1298  static function getUserObjectMatrix($a_parent_ref_id, $a_obj_ids, $a_user_filter = NULL)
1299  {
1300  global $ilDB;
1301 
1302  $result = array("cnt"=>0, "set"=>NULL);
1303  if(sizeof($a_obj_ids))
1304  {
1305  $where = array();
1306  $where[] = "usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
1307  if($a_user_filter)
1308  {
1309  $where[] = $ilDB->like("usr_data.login", "text", "%".$a_user_filter."%");
1310  }
1311 
1312  // users
1313  $left = "";
1314  $a_users = self::getParticipantsForObject($a_parent_ref_id);
1315  if (is_array($a_users))
1316  {
1317  $left = "LEFT";
1318  $where[] = $ilDB->in("usr_data.usr_id", $a_users, false, "integer");
1319  }
1320 
1321  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1322 
1323  $fields = array("usr_data.usr_id", "login", "status", "percentage",
1324  "last_access", "spent_seconds+childs_spent_seconds as spent_seconds");
1325 
1326  if(!$a_order_field)
1327  {
1328  $a_order_field = "login";
1329  }
1330 
1331  $parent_obj_id = ilObject::_lookupObjectId($a_parent_ref_id);
1332  self::refreshObjectsStatus(array($parent_obj_id), $a_users);
1333 
1334  self::refreshObjectsStatus($a_obj_ids, $a_users);
1335 
1336  $raw = array();
1337  foreach($a_obj_ids as $obj_id)
1338  {
1339  // one request for each object
1340  $query = " FROM usr_data ".$left." JOIN read_event ON (read_event.usr_id = usr_data.usr_id".
1341  " AND read_event.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1342  " LEFT JOIN ut_lp_marks ON (ut_lp_marks.usr_id = usr_data.usr_id ".
1343  " AND ut_lp_marks.obj_id = ".$ilDB->quote($obj_id, "integer").")".
1344  self::buildFilters($where, $a_filters);
1345 
1346  $raw = self::executeQueries(array(array("fields"=>$fields, "query"=>$query)), $a_order_field);
1347  if($raw["cnt"])
1348  {
1349  // convert to final structure
1350  foreach($raw["set"] as $idx => $row)
1351  {
1352  $result["set"][$row["usr_id"]]["login"] = $row["login"];
1353  $result["set"][$row["usr_id"]]["usr_id"] = $row["usr_id"];
1354  $result["set"][$row["usr_id"]]["objects"][$obj_id] = array("status"=>$row["status"],
1355  "percentage"=>$row["percentage"]);
1356  if($obj_id == $parent_obj_id)
1357  {
1358  $result["set"][$row["usr_id"]]["last_access"] = $row["last_access"];
1359  $result["set"][$row["usr_id"]]["spent_seconds"] = $row["spent_seconds"];
1360  }
1361  }
1362  }
1363  }
1364 
1365  $result["cnt"] = sizeof($result["set"]);
1366  $result["users"] = $a_users;
1367  }
1368  return $result;
1369  }
1370 
1371  static public function getUserObjectiveMatrix($a_parent_obj_id, $a_users)
1372  {
1373  global $ilDB;
1374 
1375  if($a_parent_obj_id && $a_users)
1376  {
1377  include_once("Services/Tracking/classes/class.ilLPStatus.php");
1378 
1379  $fields = array("crs_objectives.objective_id AS obj_id", "crs_objective_status.user_id AS usr_id", "title");
1380  $fields[] = "(CASE WHEN status IS NOT NULL THEN ".LP_STATUS_COMPLETED_NUM." ELSE NULL END) AS status";
1381 
1382  $where = array();
1383  $where[] = "crs_objectives.crs_id = ".$ilDB->quote($a_parent_obj_id, "integer");
1384 
1385  $query = " FROM crs_objectives".
1386  " LEFT JOIN crs_objective_status ON (crs_objectives.objective_id = crs_objective_status.objective_id".
1387  " AND ".$ilDB->in("crs_objective_status.user_id", $a_users, "", "integer").")".
1388  self::buildFilters($where);
1389 
1390  return self::executeQueries(array(array("fields"=>$fields, "query"=>$query, "count"=>"crs_objectives.objective_id")));
1391  }
1392  }
1393 
1400  protected static function refreshObjectsStatus(array $a_obj_ids, $a_users = null)
1401  {
1402  include_once("./Services/Tracking/classes/class.ilLPStatus.php");
1403  foreach($a_obj_ids as $obj_id)
1404  {
1405  ilLPStatus::checkStatusForObject($obj_id, $a_users);
1406  }
1407  }
1408 }
1409 
1410 ?>