ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
class.ilSCORM2004Tracking.php
Go to the documentation of this file.
1 <?php
2 
3 /* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
4 
12 class ilSCORM2004Tracking
13 {
14 
15  static function _getInProgress($scorm_item_id,$a_obj_id)
16  {
17 
18 die("Not Implemented: ilSCORM2004Tracking_getInProgress");
19 /*
20  global $ilDB;
21 
22  if(is_array($scorm_item_id))
23  {
24  $where = "WHERE sco_id IN(";
25  $where .= implode(",",ilUtil::quoteArray($scorm_item_id));
26  $where .= ") ";
27  $where .= ("AND obj_id = ".$ilDB->quote($a_obj_id)." ");
28 
29  }
30  else
31  {
32  $where = "WHERE sco_id = ".$ilDB->quote($scorm_item_id)." ";
33  $where .= ("AND obj_id = ".$ilDB->quote($a_obj_id)." ");
34  }
35 
36 
37  $query = "SELECT user_id,sco_id FROM scorm_tracking ".
38  $where.
39  "GROUP BY user_id, sco_id";
40 
41 
42  $res = $ilDB->query($query);
43  while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
44  {
45  $in_progress[$row->sco_id][] = $row->user_id;
46  }
47  return is_array($in_progress) ? $in_progress : array();
48 */
49  }
50 
51  static function _getCompleted($scorm_item_id,$a_obj_id)
52  {
53  global $ilDB;
54 
55 die("Not Implemented: ilSCORM2004Tracking_getCompleted");
56 /*
57  if(is_array($scorm_item_id))
58  {
59  $where = "WHERE sco_id IN(".implode(",",ilUtil::quoteArray($scorm_item_id)).") ";
60  }
61  else
62  {
63  $where = "sco_id = ".$ilDB->quote($scorm_item_id)." ";
64  }
65 
66  $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
67  $where.
68  "AND obj_id = ".$ilDB->quote($a_obj_id)." ".
69  "AND lvalue = 'cmi.core.lesson_status' ".
70  "AND ( rvalue = 'completed' ".
71  "OR rvalue = 'passed')";
72  $res = $ilDB->query($query);
73  while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
74  {
75  $user_ids[] = $row->user_id;
76  }
77  return $user_ids ? $user_ids : array();
78 */
79  }
80 
81  static function _getFailed($scorm_item_id,$a_obj_id)
82  {
83  global $ilDB;
84 
85 die("Not Implemented: ilSCORM2004Tracking_getFailed");
86 /*
87  if(is_array($scorm_item_id))
88  {
89  $where = "WHERE sco_id IN('".implode("','",$scorm_item_id)."') ";
90  }
91  else
92  {
93  $where = "sco_id = '".$scorm_item_id."' ";
94  }
95 
96  $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
97  $where.
98  "AND obj_id = '".$a_obj_id."' ".
99  "AND lvalue = 'cmi.core.lesson_status' ".
100  "AND rvalue = 'failed'";
101  $res = $ilDB->query($query);
102  while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
103  {
104  $user_ids[] = $row->user_id;
105  }
106  return $user_ids ? $user_ids : array();
107 */
108  }
109 
117  static function _getCountCompletedPerUser($a_scorm_item_ids, $a_obj_id, $a_omit_failed = false)
118  {
119  global $ilDB;
120 
121  $in = $ilDB->in('cp_node.cp_node_id', $a_scorm_item_ids, false, 'integer');
122 
123  // #8171: success_status vs. completion status
124  $omit_failed = '';
125  if($a_omit_failed)
126  {
127  $omit_failed = ' AND success_status <> '.$ilDB->quote('failed', 'text');
128  }
129 
130  $res = $ilDB->queryF('
131  SELECT cmi_node.user_id user_id, COUNT(user_id) completed FROM cp_node, cmi_node
132  WHERE '.$in.$omit_failed.'
133  AND cp_node.cp_node_id = cmi_node.cp_node_id
134  AND cp_node.slm_id = %s
135  AND completion_status = %s
136  GROUP BY cmi_node.user_id',
137  array('integer', 'text'),
138  array($a_obj_id, 'completed')
139  );
140  while($row = $ilDB->fetchObject($res))
141  {
142  $users[$row->user_id] = $row->completed;
143  }
144 
145  return $users ? $users : array();
146  }
147 
148 
154  static function _getProgressInfo($a_obj_id)
155  {
156  global $ilDB;
157 
158  $res = $ilDB->queryF('
159  SELECT user_id, status, satisfied FROM cmi_gobjective
160  WHERE objective_id = %s
161  AND scope_id = %s',
162  array('text', 'integer'),
163  array('-course_overall_status-', $a_obj_id)
164  );
165 
166  $info['completed'] = array();
167  $info['failed'] = array();
168  $info['in_progress'] = array();
169 
170  while($row = $ilDB->fetchAssoc($res))
171  {
172  if (self::_isCompleted($row["status"], $row["satisfied"]))
173  {
174  $info['completed'][] = $row["user_id"];
175  }
176  if (self::_isInProgress($row["status"], $row["satisfied"]))
177  {
178  $info['in_progress'][] = $row["user_id"];
179  }
180  if (self::_isFailed($row["status"], $row["satisfied"]))
181  {
182  $info['failed'][] = $row["user_id"];
183  }
184  }
185 
186  return $info;
187  }
188 
194  static function _getProgressInfoOfUser($a_obj_id, $a_user_id)
195  {
196  global $ilDB, $ilLog;
197 
198  $res = $ilDB->queryF('
199  SELECT status, satisfied FROM cmi_gobjective
200  WHERE objective_id = %s
201  AND scope_id = %s AND user_id = %s',
202  array('text', 'integer', 'integer'),
203  array('-course_overall_status-', $a_obj_id, $a_user_id)
204  );
205 
206  $status = "not_attempted";
207  if ($row = $ilDB->fetchAssoc($res))
208  {
209  if (self::_isInProgress($row["status"], $row["satisfied"]))
210  {
211  $status = "in_progress";
212  }
213  if (self::_isCompleted($row["status"], $row["satisfied"]))
214  {
215  $status = "completed";
216  }
217  if (self::_isFailed($row["status"], $row["satisfied"]))
218  {
219  $status = "failed";
220  }
221  }
222  return $status;
223  }
224 
230  static function _getTrackedUsers($a_obj_id)
231  {
232  global $ilDB, $ilLog;
233 
234  $res = $ilDB->queryF('
235  SELECT DISTINCT user_id FROM cmi_gobjective
236  WHERE objective_id = %s
237  AND scope_id = %s',
238  array('text', 'integer'),
239  array('-course_overall_status-', $a_obj_id)
240  );
241 
242  $users = array();
243  while ($row = $ilDB->fetchAssoc($res))
244  {
245  $users[] = $row["user_id"];
246  }
247  return $users;
248  }
249 
250  static function _getItemProgressInfo($a_scorm_item_ids, $a_obj_id, $a_omit_failed = false)
251  {
252  global $ilDB;
253 
254  $in = $ilDB->in('cp_node.cp_node_id', $a_scorm_item_ids, false, 'integer');
255 
256  $res = $ilDB->queryF(
257  'SELECT cp_node.cp_node_id id,
258  cmi_node.user_id user_id,
259  cmi_node.completion_status completion,
260  cmi_node.success_status success
261  FROM cp_node, cmi_node
262  WHERE '.$in.'
263  AND cp_node.cp_node_id = cmi_node.cp_node_id
264  AND cp_node.slm_id = %s',
265  array('integer'),
266  array($a_obj_id)
267  );
268 
269  $info['completed'] = array();
270  $info['failed'] = array();
271  $info['in_progress'] = array();
272 
273  while($row = $ilDB->fetchAssoc($res))
274  {
275  // if any data available, set in progress.
276  $info['in_progress'][$row["id"]][] = $row["user_id"];
277  if ($row["completion"] == "completed" || $row["success"] == "passed")
278  {
279  // #8171: success_status vs. completion status
280  if(!$a_omit_failed || $row["success"] != "failed")
281  {
282  $info['completed'][$row["id"]][] = $row["user_id"];
283  }
284  }
285  if ($row["success"] == "failed")
286  {
287  $info['failed'][$row["id"]][] = $row["user_id"];
288  }
289  }
290  return $info;
291  }
292 
293  public static function _getCollectionStatus($a_scos, $a_obj_id, $a_user_id)
294  {
295  global $ilDB;
296 
297  $status = "not_attempted";
298 
299  if (is_array($a_scos))
300  {
301  $in = $ilDB->in('cp_node.cp_node_id', $a_scos, false, 'integer');
302 
303  $res = $ilDB->queryF(
304  'SELECT cp_node.cp_node_id id,
305  cmi_node.completion_status completion,
306  cmi_node.success_status success
307  FROM cp_node, cmi_node
308  WHERE '.$in.'
309  AND cp_node.cp_node_id = cmi_node.cp_node_id
310  AND cp_node.slm_id = %s
311  AND cmi_node.user_id = %s',
312  array('integer', 'integer'),
313  array($a_obj_id, $a_user_id)
314  );
315 
316  $started = false;
317  $cntcompleted = 0;
318  $failed = false;
319  while ($rec = $ilDB->fetchAssoc($res))
320  {
321  if ($rec["completion"] == "completed" || $rec["success"] == "passed")
322  {
323  $cntcompleted++;
324  }
325  if ($rec["success"] == "failed") $failed = true;
326  $started = true;
327  }
328  if ($started == true) $status = "in_progress";
329  if ($failed == true) $status = "failed";
330  else if ($cntcompleted == count($a_scos)) $status = "completed";
331 
332  }
333  return $status;
334  }
335 
336  public static function _countCompleted($a_scos, $a_obj_id, $a_user_id,
337  $a_omit_failed = false)
338  {
339  global $ilDB;
340 
341  if (is_array($a_scos))
342  {
343  $in = $ilDB->in('cp_node.cp_node_id', $a_scos, false, 'integer');
344 
345  $res = $ilDB->queryF(
346  'SELECT cp_node.cp_node_id id,
347  cmi_node.completion_status completion,
348  cmi_node.success_status success
349  FROM cp_node, cmi_node
350  WHERE '.$in.'
351  AND cp_node.cp_node_id = cmi_node.cp_node_id
352  AND cp_node.slm_id = %s
353  AND cmi_node.user_id = %s',
354  array('integer', 'integer'),
355  array($a_obj_id, $a_user_id)
356  );
357 
358 
359  $cnt = 0;
360  while ($rec = $ilDB->fetchAssoc($res))
361  {
362  // #8171: alex, added (!$a_omit_failed || $rec["success"] != "failed")
363  // since completed/failed combination should not be included in
364  // percentage calculation at ilLPStatusSCOM::determinePercentage
365  if (($rec["completion"] == "completed" || $rec["success"] == "passed")
366  && (!$a_omit_failed || $rec["success"] != "failed"))
367  {
368  $cnt++;
369  }
370  }
371 
372  }
373  return $cnt;
374  }
375 
382  static function _syncReadEvent($a_obj_id, $a_user_id, $a_type, $a_ref_id, $time_from_lms = null)
383  {
384  global $ilDB;
385 
386  //get condition to select time
387  $val_set = $ilDB->queryF(
388  'SELECT time_from_lms FROM sahs_lm WHERE id = %s',
389  array('integer'),array($a_obj_id));
390  $val_rec = $ilDB->fetchAssoc($val_set);
391  $time_from_lms=(ilUtil::yn2tf($val_rec["time_from_lms"]));
392 
393  // get attempts and time
394  $val_set = $ilDB->queryF('
395  SELECT package_attempts, sco_total_time_sec, total_time_sec
396  FROM sahs_user WHERE obj_id = %s AND user_id = %s',
397  array('integer','integer'), array($a_obj_id,$a_user_id));
398  $val_rec = $ilDB->fetchAssoc($val_set);
399  if ($time_from_lms == false) $time = $val_rec["sco_total_time_sec"];
400  else $time = $val_rec["total_time_sec"];
401  $attempts = $val_rec["package_attempts"];
402  if ($attempts == null) $attempts = ""; //??
403 
404  if ($attempts != "" && $time == null) { //use old way
405  $time = self::getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id,true);
406  }
407 
408  include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
409  ilChangeEvent::_recordReadEvent($a_type, $a_ref_id,
410  $a_obj_id, $a_user_id, false, $attempts, $time);
411  }
412 
416  static function _isCompleted($a_status, $a_satisfied)
417  {
418  if ($a_status == "completed" || $a_satisfied == "satisfied")
419  {
420  return true;
421  }
422 
423  return false;
424  }
425 
429  static function _isInProgress($a_status, $a_satisfied)
430  {
431  if ($a_status != "completed")
432  {
433  return true;
434  }
435 
436  return false;
437  }
438 
442  static function _isFailed($a_status, $a_satisfied)
443  {
444  if ($a_status == "completed" && $a_satisfied == "notSatisfied")
445  {
446  return true;
447  }
448 
449  return false;
450  }
451 
455  static function getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, $a_write=false)
456  {
457  global $ilDB, $ilLog;
458  $scos = array();
459  $val_set = $ilDB->queryF(
460  'SELECT cp_node_id FROM cp_node
461  WHERE nodename = %s
462  AND cp_node.slm_id = %s',
463  array('text', 'integer'),
464  array('item', $a_obj_id)
465  );
466  while($val_rec = $ilDB->fetchAssoc($val_set))
467  {
468  array_push($scos,$val_rec['cp_node_id']);
469  }
470  $time = 0;
471  foreach ($scos as $sco)
472  {
473  include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
474  $data_set = $ilDB->queryF('
475  SELECT total_time
476  FROM cmi_node
477  WHERE cp_node_id = %s
478  AND user_id = %s',
479  array('integer','integer'),
480  array($sco, $a_user_id)
481  );
482 
483  while($data_rec = $ilDB->fetchAssoc($data_set))
484  {
485  $sec = ilObjSCORM2004LearningModule::_ISODurationToCentisec($data_rec["total_time"]) / 100;
486  }
487  $time += (int) $sec;
488  $sec = 0;
489 //$ilLog->write("++".$time);
490  }
491  if ($a_write && $time>0) {
492  $ilDB->queryF('UPDATE sahs_user SET sco_total_time_sec=%s WHERE obj_id = %s AND user_id = %s',
493  array('integer', 'integer', 'integer'),
494  array($time, $a_obj_id, $a_user_id));
495  }
496  return $time;
497  }
498 
499 }
500 // END class.ilSCORM2004Tracking
501 ?>
ilSCORM2004Tracking\_getItemProgressInfo
static _getItemProgressInfo($a_scorm_item_ids, $a_obj_id, $a_omit_failed=false)
Definition: class.ilSCORM2004Tracking.php:250
ilSCORM2004Tracking\_getProgressInfo
static _getProgressInfo($a_obj_id)
Get overall scorm status.
Definition: class.ilSCORM2004Tracking.php:154
ilSCORM2004Tracking\_getProgressInfoOfUser
static _getProgressInfoOfUser($a_obj_id, $a_user_id)
Get overall scorm status.
Definition: class.ilSCORM2004Tracking.php:194
ilSCORM2004Tracking\_isInProgress
static _isInProgress($a_status, $a_satisfied)
Definition: class.ilSCORM2004Tracking.php:429
$a_type
$a_type
Definition: workflow.php:93
$info
$info
Definition: example_052.php:80
ilSCORM2004Tracking\_getTrackedUsers
static _getTrackedUsers($a_obj_id)
Get all tracked users.
Definition: class.ilSCORM2004Tracking.php:230
ilSCORM2004Tracking\_getCountCompletedPerUser
static _getCountCompletedPerUser($a_scorm_item_ids, $a_obj_id, $a_omit_failed=false)
Get progress of selected scos.
Definition: class.ilSCORM2004Tracking.php:117
ilSCORM2004Tracking\_getInProgress
static _getInProgress($scorm_item_id, $a_obj_id)
Definition: class.ilSCORM2004Tracking.php:15
ilSCORM2004Tracking\_countCompleted
static _countCompleted($a_scos, $a_obj_id, $a_user_id, $a_omit_failed=false)
Definition: class.ilSCORM2004Tracking.php:336
ilSCORM2004Tracking\_isCompleted
static _isCompleted($a_status, $a_satisfied)
Definition: class.ilSCORM2004Tracking.php:416
ilChangeEvent\_recordReadEvent
static _recordReadEvent($a_type, $a_ref_id, $obj_id, $usr_id, $isCatchupWriteEvents=true, $a_ext_rc=false, $a_ext_time=false)
Records a read event and catches up with write events.
Definition: class.ilChangeEvent.php:112
ilSCORM2004Tracking\_getCompleted
static _getCompleted($scorm_item_id, $a_obj_id)
Definition: class.ilSCORM2004Tracking.php:51
ilSCORM2004Tracking
Class ilSCORM2004Tracking.
Definition: class.ilSCORM2004Tracking.php:12
$ilLog
$ilLog
Definition: inc.setup_header.php:130
$failed
$failed
Definition: Utf8Test.php:85
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
$in
if(php_sapi_name() !='cli') $in
Definition: Utf8Test.php:37
ilSCORM2004Tracking\_getCollectionStatus
static _getCollectionStatus($a_scos, $a_obj_id, $a_user_id)
Definition: class.ilSCORM2004Tracking.php:293
$ilDB
global $ilDB
Definition: storeScorm2004.php:16
ilObjSCORM2004LearningModule\_ISODurationToCentisec
static _ISODurationToCentisec($str)
convert ISO 8601 Timeperiods to centiseconds ta
Definition: class.ilObjSCORM2004LearningModule.php:670
ilSCORM2004Tracking\_isFailed
static _isFailed($a_status, $a_satisfied)
Definition: class.ilSCORM2004Tracking.php:442
ilSCORM2004Tracking\_getFailed
static _getFailed($scorm_item_id, $a_obj_id)
Definition: class.ilSCORM2004Tracking.php:81
ilUtil\yn2tf
static yn2tf($a_yn)
convert "y"/"n" to true/false
Definition: class.ilUtil.php:3228
ilSCORM2004Tracking\getSumTotalTimeSecondsFromScos
static getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, $a_write=false)
should be avoided; store value to increase performance for further requests
Definition: class.ilSCORM2004Tracking.php:455
ilSCORM2004Tracking\_syncReadEvent
static _syncReadEvent($a_obj_id, $a_user_id, $a_type, $a_ref_id, $time_from_lms=null)
Synch read event table.
Definition: class.ilSCORM2004Tracking.php:382