ILIAS  release_6 Revision v6.24-5-g0c8bfefb3b8
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
13{
14 public static function _getInProgress($scorm_item_id, $a_obj_id)
15 {
16 die("Not Implemented: ilSCORM2004Tracking_getInProgress");
17 /*
18 global $DIC;
19
20 $ilDB = $DIC->database();
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 public static function _getCompleted($scorm_item_id, $a_obj_id)
52 {
53 global $DIC;
54
55 $ilDB = $DIC->database();
56
57 die("Not Implemented: ilSCORM2004Tracking_getCompleted");
58 /*
59 if(is_array($scorm_item_id))
60 {
61 $where = "WHERE sco_id IN(".implode(",",ilUtil::quoteArray($scorm_item_id)).") ";
62 }
63 else
64 {
65 $where = "sco_id = ".$ilDB->quote($scorm_item_id)." ";
66 }
67
68 $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
69 $where.
70 "AND obj_id = ".$ilDB->quote($a_obj_id)." ".
71 "AND lvalue = 'cmi.core.lesson_status' ".
72 "AND ( rvalue = 'completed' ".
73 "OR rvalue = 'passed')";
74 $res = $ilDB->query($query);
75 while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
76 {
77 $user_ids[] = $row->user_id;
78 }
79 return $user_ids ? $user_ids : array();
80 */
81 }
82
83 public static function _getFailed($scorm_item_id, $a_obj_id)
84 {
85 global $DIC;
86
87 $ilDB = $DIC->database();
88
89 die("Not Implemented: ilSCORM2004Tracking_getFailed");
90 /*
91 if(is_array($scorm_item_id))
92 {
93 $where = "WHERE sco_id IN('".implode("','",$scorm_item_id)."') ";
94 }
95 else
96 {
97 $where = "sco_id = '".$scorm_item_id."' ";
98 }
99
100 $query = "SELECT DISTINCT(user_id) FROM scorm_tracking ".
101 $where.
102 "AND obj_id = '".$a_obj_id."' ".
103 "AND lvalue = 'cmi.core.lesson_status' ".
104 "AND rvalue = 'failed'";
105 $res = $ilDB->query($query);
106 while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
107 {
108 $user_ids[] = $row->user_id;
109 }
110 return $user_ids ? $user_ids : array();
111 */
112 }
113
121 public static function _getCountCompletedPerUser($a_scorm_item_ids, $a_obj_id, $a_omit_failed = false)
122 {
123 global $DIC;
124
125 $ilDB = $DIC->database();
126
127 $in = $ilDB->in('cp_node.cp_node_id', $a_scorm_item_ids, false, 'integer');
128
129 // #8171: success_status vs. completion status
130 $omit_failed = '';
131 if ($a_omit_failed) {
132 $omit_failed = ' AND success_status <> ' . $ilDB->quote('failed', 'text');
133 }
134
135 $res = $ilDB->queryF(
136 '
137 SELECT cmi_node.user_id user_id, COUNT(user_id) completed FROM cp_node, cmi_node
138 WHERE ' . $in . $omit_failed . '
139 AND cp_node.cp_node_id = cmi_node.cp_node_id
140 AND cp_node.slm_id = %s
141 AND completion_status = %s
142 GROUP BY cmi_node.user_id',
143 array('integer', 'text'),
144 array($a_obj_id, 'completed')
145 );
146 while ($row = $ilDB->fetchObject($res)) {
147 $users[$row->user_id] = $row->completed;
148 }
149
150 return $users ? $users : array();
151 }
152
153
159 public static function _getProgressInfo($a_obj_id)
160 {
161 global $DIC;
162
163 $ilDB = $DIC->database();
164
165 $res = $ilDB->queryF(
166 '
167 SELECT user_id, status, satisfied FROM cmi_gobjective
168 WHERE objective_id = %s
169 AND scope_id = %s',
170 array('text', 'integer'),
171 array('-course_overall_status-', $a_obj_id)
172 );
173
174 $info['completed'] = array();
175 $info['failed'] = array();
176 $info['in_progress'] = array();
177
178 while ($row = $ilDB->fetchAssoc($res)) {
179 if (self::_isCompleted($row["status"], $row["satisfied"])) {
180 $info['completed'][] = $row["user_id"];
181 }
182 if (self::_isInProgress($row["status"], $row["satisfied"])) {
183 $info['in_progress'][] = $row["user_id"];
184 }
185 if (self::_isFailed($row["status"], $row["satisfied"])) {
186 $info['failed'][] = $row["user_id"];
187 }
188 }
189
190 return $info;
191 }
192
198 public static function _getProgressInfoOfUser($a_obj_id, $a_user_id)
199 {
200 global $DIC;
201
202 $ilDB = $DIC->database();
203 $ilLog = $DIC["ilLog"];
204
205 $res = $ilDB->queryF(
206 '
207 SELECT status, satisfied FROM cmi_gobjective
208 WHERE objective_id = %s
209 AND scope_id = %s AND user_id = %s',
210 array('text', 'integer', 'integer'),
211 array('-course_overall_status-', $a_obj_id, $a_user_id)
212 );
213
214 $status = "not_attempted";
215 if ($row = $ilDB->fetchAssoc($res)) {
216 if (self::_isInProgress($row["status"], $row["satisfied"])) {
217 $status = "in_progress";
218 }
219 if (self::_isCompleted($row["status"], $row["satisfied"])) {
220 $status = "completed";
221 }
222 if (self::_isFailed($row["status"], $row["satisfied"])) {
223 $status = "failed";
224 }
225 }
226 return $status;
227 }
228
234 public static function _getTrackedUsers($a_obj_id)
235 {
236 global $DIC;
237
238 $ilDB = $DIC->database();
239 $ilLog = $DIC["ilLog"];
240
241 $res = $ilDB->queryF(
242 '
243 SELECT DISTINCT user_id FROM cmi_gobjective
244 WHERE objective_id = %s
245 AND scope_id = %s',
246 array('text', 'integer'),
247 array('-course_overall_status-', $a_obj_id)
248 );
249
250 $users = array();
251 while ($row = $ilDB->fetchAssoc($res)) {
252 $users[] = $row["user_id"];
253 }
254 return $users;
255 }
256
257 public static function _getItemProgressInfo($a_scorm_item_ids, $a_obj_id, $a_omit_failed = false)
258 {
259 global $DIC;
260
261 $ilDB = $DIC->database();
262
263 $in = $ilDB->in('cp_node.cp_node_id', $a_scorm_item_ids, false, 'integer');
264
265 $res = $ilDB->queryF(
266 'SELECT cp_node.cp_node_id id,
267 cmi_node.user_id user_id,
268 cmi_node.completion_status completion,
269 cmi_node.success_status success
270 FROM cp_node, cmi_node
271 WHERE ' . $in . '
272 AND cp_node.cp_node_id = cmi_node.cp_node_id
273 AND cp_node.slm_id = %s',
274 array('integer'),
275 array($a_obj_id)
276 );
277
278 $info['completed'] = array();
279 $info['failed'] = array();
280 $info['in_progress'] = array();
281
282 while ($row = $ilDB->fetchAssoc($res)) {
283 // if any data available, set in progress.
284 $info['in_progress'][$row["id"]][] = $row["user_id"];
285 if ($row["completion"] == "completed" || $row["success"] == "passed") {
286 // #8171: success_status vs. completion status
287 if (!$a_omit_failed || $row["success"] != "failed") {
288 $info['completed'][$row["id"]][] = $row["user_id"];
289 }
290 }
291 if ($row["success"] == "failed") {
292 $info['failed'][$row["id"]][] = $row["user_id"];
293 }
294 }
295 return $info;
296 }
297
298 public static function _getCollectionStatus($a_scos, $a_obj_id, $a_user_id)
299 {
300 global $DIC;
301
302 $ilDB = $DIC->database();
303
304 $status = "not_attempted";
305
306 if (is_array($a_scos)) {
307 $in = $ilDB->in('cp_node.cp_node_id', $a_scos, false, 'integer');
308
309 $res = $ilDB->queryF(
310 'SELECT cp_node.cp_node_id id,
311 cmi_node.completion_status completion,
312 cmi_node.success_status success
313 FROM cp_node, cmi_node
314 WHERE ' . $in . '
315 AND cp_node.cp_node_id = cmi_node.cp_node_id
316 AND cp_node.slm_id = %s
317 AND cmi_node.user_id = %s',
318 array('integer', 'integer'),
319 array($a_obj_id, $a_user_id)
320 );
321
322 $started = false;
323 $cntcompleted = 0;
324 $failed = false;
325 while ($rec = $ilDB->fetchAssoc($res)) {
326 if ($rec["completion"] == "completed" || $rec["success"] == "passed") {
327 $cntcompleted++;
328 }
329 if ($rec["success"] == "failed") {
330 $failed = true;
331 }
332 $started = true;
333 }
334 if ($started == true) {
335 $status = "in_progress";
336 }
337 if ($failed == true) {
338 $status = "failed";
339 } elseif ($cntcompleted == count($a_scos)) {
340 $status = "completed";
341 }
342 }
343 return $status;
344 }
345
346 public static function _countCompleted(
347 $a_scos,
348 $a_obj_id,
349 $a_user_id,
350 $a_omit_failed = false
351 ) {
352 global $DIC;
353
354 $ilDB = $DIC->database();
355
356 if (is_array($a_scos)) {
357 $in = $ilDB->in('cp_node.cp_node_id', $a_scos, false, 'integer');
358
359 $res = $ilDB->queryF(
360 'SELECT cp_node.cp_node_id id,
361 cmi_node.completion_status completion,
362 cmi_node.success_status success
363 FROM cp_node, cmi_node
364 WHERE ' . $in . '
365 AND cp_node.cp_node_id = cmi_node.cp_node_id
366 AND cp_node.slm_id = %s
367 AND cmi_node.user_id = %s',
368 array('integer', 'integer'),
369 array($a_obj_id, $a_user_id)
370 );
371
372
373 $cnt = 0;
374 while ($rec = $ilDB->fetchAssoc($res)) {
375 // #8171: alex, added (!$a_omit_failed || $rec["success"] != "failed")
376 // since completed/failed combination should not be included in
377 // percentage calculation at ilLPStatusSCOM::determinePercentage
378 if (($rec["completion"] == "completed" || $rec["success"] == "passed")
379 && (!$a_omit_failed || $rec["success"] != "failed")) {
380 $cnt++;
381 }
382 }
383 }
384 return $cnt;
385 }
386
393 public static function _syncReadEvent($a_obj_id, $a_user_id, $a_type, $a_ref_id, $time_from_lms = null)
394 {
395 global $DIC;
396
397 $ilDB = $DIC->database();
398
399 //get condition to select time
400 $val_set = $ilDB->queryF(
401 'SELECT time_from_lms FROM sahs_lm WHERE id = %s',
402 array('integer'),
403 array($a_obj_id)
404 );
405 $val_rec = $ilDB->fetchAssoc($val_set);
406 $time_from_lms = (ilUtil::yn2tf($val_rec["time_from_lms"]));
407
408 // get attempts and time
409 $val_set = $ilDB->queryF(
410 '
411 SELECT package_attempts, sco_total_time_sec, total_time_sec
412 FROM sahs_user WHERE obj_id = %s AND user_id = %s',
413 array('integer','integer'),
414 array($a_obj_id,$a_user_id)
415 );
416 $val_rec = $ilDB->fetchAssoc($val_set);
417 if ($time_from_lms == false) {
418 $time = $val_rec["sco_total_time_sec"];
419 } else {
420 $time = $val_rec["total_time_sec"];
421 }
422 $attempts = $val_rec["package_attempts"];
423 if ($attempts == null) {
424 $attempts = "";
425 } //??
426
427 if ($attempts != "" && $time == null) { //use old way
428 $time = self::getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, true);
429 }
430
431 include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
433 $a_type,
434 $a_ref_id,
435 $a_obj_id,
436 $a_user_id,
437 false,
438 $attempts,
439 $time
440 );
441 }
442
446 public static function _isCompleted($a_status, $a_satisfied)
447 {
448 if ($a_status == "completed" || $a_satisfied == "satisfied") {
449 return true;
450 }
451
452 return false;
453 }
454
458 public static function _isInProgress($a_status, $a_satisfied)
459 {
460 if ($a_status != "completed") {
461 return true;
462 }
463
464 return false;
465 }
466
470 public static function _isFailed($a_status, $a_satisfied)
471 {
472 if ($a_status == "completed" && $a_satisfied == "notSatisfied") {
473 return true;
474 }
475
476 return false;
477 }
478
482 public static function getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, $a_write = false)
483 {
484 global $DIC;
485
486 $ilDB = $DIC->database();
487 $ilLog = $DIC["ilLog"];
488 $scos = array();
489 $val_set = $ilDB->queryF(
490 'SELECT cp_node_id FROM cp_node
491 WHERE nodename = %s
492 AND cp_node.slm_id = %s',
493 array('text', 'integer'),
494 array('item', $a_obj_id)
495 );
496 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
497 array_push($scos, $val_rec['cp_node_id']);
498 }
499 $time = 0;
500 foreach ($scos as $sco) {
501 include_once("./Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php");
502 $data_set = $ilDB->queryF(
503 '
504 SELECT total_time
505 FROM cmi_node
506 WHERE cp_node_id = %s
507 AND user_id = %s',
508 array('integer','integer'),
509 array($sco, $a_user_id)
510 );
511
512 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
513 $sec = ilObjSCORM2004LearningModule::_ISODurationToCentisec($data_rec["total_time"]) / 100;
514 }
515 $time += (int) $sec;
516 $sec = 0;
517 //$ilLog->write("++".$time);
518 }
519 if ($a_write && $time > 0) {
520 $ilDB->queryF(
521 'UPDATE sahs_user SET sco_total_time_sec=%s WHERE obj_id = %s AND user_id = %s',
522 array('integer', 'integer', 'integer'),
523 array($time, $a_obj_id, $a_user_id)
524 );
525 }
526 return $time;
527 }
528}
529// END class.ilSCORM2004Tracking
$failed
Definition: Utf8Test.php:85
if(php_sapi_name() !='cli') $in
Definition: Utf8Test.php:37
An exception for terminatinating execution or to throw for unit testing.
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.
static _ISODurationToCentisec($str)
convert ISO 8601 Timeperiods to centiseconds ta
Class ilSCORM2004Tracking.
static _getProgressInfoOfUser($a_obj_id, $a_user_id)
Get overall scorm status.
static _getCollectionStatus($a_scos, $a_obj_id, $a_user_id)
static getSumTotalTimeSecondsFromScos($a_obj_id, $a_user_id, $a_write=false)
should be avoided; store value to increase performance for further requests
static _isFailed($a_status, $a_satisfied)
static _getCountCompletedPerUser($a_scorm_item_ids, $a_obj_id, $a_omit_failed=false)
Get progress of selected scos.
static _getCompleted($scorm_item_id, $a_obj_id)
static _getTrackedUsers($a_obj_id)
Get all tracked users.
static _syncReadEvent($a_obj_id, $a_user_id, $a_type, $a_ref_id, $time_from_lms=null)
Synch read event table.
static _countCompleted( $a_scos, $a_obj_id, $a_user_id, $a_omit_failed=false)
static _getProgressInfo($a_obj_id)
Get overall scorm status.
static _getInProgress($scorm_item_id, $a_obj_id)
static _isInProgress($a_status, $a_satisfied)
static _isCompleted($a_status, $a_satisfied)
static _getFailed($scorm_item_id, $a_obj_id)
static _getItemProgressInfo($a_scorm_item_ids, $a_obj_id, $a_omit_failed=false)
static yn2tf($a_yn)
convert "y"/"n" to true/false
foreach($_POST as $key=> $value) $res
global $ilDB
$a_type
Definition: workflow.php:92
$DIC
Definition: xapitoken.php:46