ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
class.ilObjSCORMLearningModule.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
5require_once "./Services/Object/classes/class.ilObject.php";
6require_once "./Modules/ScormAicc/classes/class.ilObjSCORMValidator.php";
7require_once "./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php";
8
18{
19 public $validator;
20
27 public function __construct($a_id = 0, $a_call_by_reference = true)
28 {
29 $this->type = "sahs";
30 parent::__construct($a_id, $a_call_by_reference);
31 }
32
33
40 public function validate($directory)
41 {
42 $this->validator = new ilObjSCORMValidator($directory);
43 $returnValue = $this->validator->validate();
44 return $returnValue;
45 }
46
47 public function getValidationSummary()
48 {
49 if (is_object($this->validator)) {
50 return $this->validator->getSummary();
51 }
52 return "";
53 }
54
55 public function getTrackingItems()
56 {
58 }
59
60
65 public static function _getTrackingItems($a_obj_id)
66 {
67 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMTree.php");
68 $tree = new ilSCORMTree($a_obj_id);
69 $root_id = $tree->readRootId();
70
71 $items = array();
72 $childs = $tree->getSubTree($tree->getNodeData($root_id));
73
74 foreach ($childs as $child) {
75 if ($child["c_type"] == "sit") {
76 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
77 $sc_item = new ilSCORMItem($child["obj_id"]);
78 if ($sc_item->getIdentifierRef() != "") {
79 $items[count($items)] = $sc_item;
80 }
81 }
82 }
83
84 return $items;
85 }
86
91 public function readObject()
92 {
93 global $ilErr;
94
95 $needs_convert = false;
96
97 // convert imsmanifest.xml file in iso to utf8 if needed
98
99 $manifest_file = $this->getDataDirectory() . "/imsmanifest.xml";
100
101 // check if manifestfile exists and space left on device...
102 $check_for_manifest_file = is_file($manifest_file);
103
104 // if no manifestfile
105 if (!$check_for_manifest_file) {
106 $this->ilias->raiseError($this->lng->txt("Manifestfile $manifest_file not found!"), $this->ilias->error_obj->MESSAGE);
107 return;
108 }
109
110 if ($check_for_manifest_file) {
111 $manifest_file_array = file($manifest_file);
112 foreach ($manifest_file_array as $mfa) {
113 // if (seems_not_utf8($mfa))
114 if (@iconv('UTF-8', 'UTF-8', $mfa) != $mfa) {
115 $needs_convert = true;
116 break;
117 }
118 }
119
120 // to copy the file we need some extraspace, counted in bytes *2 ... we need 2 copies....
121 $estimated_manifest_filesize = filesize($manifest_file) * 2;
122
123 // i deactivated this, because it seems to fail on some windows systems (see bug #1795)
124 //$check_disc_free = disk_free_space($this->getDataDirectory()) - $estimated_manifest_filesize;
125 $check_disc_free = 2;
126 }
127
128 // if $manifest_file needs to be converted to UTF8
129 if ($needs_convert) {
130 // if file exists and enough space left on device
131 if ($check_for_manifest_file && ($check_disc_free > 1)) {
132
133 // create backup from original
134 if (!copy($manifest_file, $manifest_file . ".old")) {
135 echo "Failed to copy $manifest_file...<br>\n";
136 }
137
138 // read backupfile, convert each line to utf8, write line to new file
139 // php < 4.3 style
140 $f_write_handler = fopen($manifest_file . ".new", "w");
141 $f_read_handler = fopen($manifest_file . ".old", "r");
142 while (!feof($f_read_handler)) {
143 $zeile = fgets($f_read_handler);
144 //echo mb_detect_encoding($zeile);
145 fputs($f_write_handler, utf8_encode($zeile));
146 }
147 fclose($f_read_handler);
148 fclose($f_write_handler);
149
150 // copy new utf8-file to imsmanifest.xml
151 if (!copy($manifest_file . ".new", $manifest_file)) {
152 echo "Failed to copy $manifest_file...<br>\n";
153 }
154
155 if (!@is_file($manifest_file)) {
156 $this->ilias->raiseError(
157 $this->lng->txt("cont_no_manifest"),
158 $this->ilias->error_obj->WARNING
159 );
160 }
161 } else {
162 // gives out the specific error
163
164 if (!($check_disc_free > 1)) {
165 $this->ilias->raiseError($this->lng->txt("Not enough space left on device!"), $this->ilias->error_obj->MESSAGE);
166 }
167 return;
168 }
169 } else {
170 // check whether file starts with BOM (that confuses some sax parsers, see bug #1795)
171 $hmani = fopen($manifest_file, "r");
172 $start = fread($hmani, 3);
173 if (strtolower(bin2hex($start)) == "efbbbf") {
174 $f_write_handler = fopen($manifest_file . ".new", "w");
175 while (!feof($hmani)) {
176 $n = fread($hmani, 900);
177 fputs($f_write_handler, $n);
178 }
179 fclose($f_write_handler);
180 fclose($hmani);
181
182 // copy new utf8-file to imsmanifest.xml
183 if (!copy($manifest_file . ".new", $manifest_file)) {
184 echo "Failed to copy $manifest_file...<br>\n";
185 }
186 } else {
187 fclose($hmani);
188 }
189 }
190
191 //validate the XML-Files in the SCORM-Package
192 if ($_POST["validate"] == "y") {
193 if (!$this->validate($this->getDataDirectory())) {
194 $ilErr->raiseError("<b>Validation Error(s):</b><br>" . $this->getValidationSummary(), $ilErr->MESSAGE);
195 }
196 }
197
198 // start SCORM package parser
199 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMPackageParser.php");
200 // todo determine imsmanifest.xml path here...
201 $slmParser = new ilSCORMPackageParser($this, $manifest_file);
202 $slmParser->startParsing();
203 return $slmParser->getPackageTitle();
204 }
205
210 {
211 global $ilSetting;
212 //condition 1
213 $lm_set = new ilSetting("lm");
214 if ($lm_set->get('scorm_lp_auto_activate') != 1) {
215 return;
216 }
217 //condition 2
218 include_once("./Services/Tracking/classes/class.ilObjUserTracking.php");
220 return;
221 }
222
223 //set Learning Progress to Automatic by Collection of SCORM Items
224 include_once("./Services/Tracking/classes/class.ilLPObjSettings.php");
225 $lm_set = new ilLPObjSettings($this->getId());
227 $lm_set->insert();
228
229 //select all SCOs as relevant for Learning Progress
230 include_once("Services/Tracking/classes/collection/class.ilLPCollectionOfSCOs.php");
231 $collection = new ilLPCollectionOfSCOs($this->getId(), ilLPObjSettings::LP_MODE_SCORM);
232 $scos = array();
233 foreach ($collection->getPossibleItems() as $sco_id => $item) {
234 $scos[] = $sco_id;
235 }
236 $collection->activateEntries($scos);
237 }
241 public function getTrackedItems()
242 {
243 global $ilDB, $ilUser;
244
245 $sco_set = $ilDB->queryF(
246 '
247 SELECT DISTINCT sco_id FROM scorm_tracking WHERE obj_id = %s',
248 array('integer'),
249 array($this->getId())
250 );
251
252 $items = array();
253 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
254 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
255 $sc_item = new ilSCORMItem($sco_rec["sco_id"]);
256 if ($sc_item->getIdentifierRef() != "") {
257 $items[count($items)] = $sc_item;
258 }
259 }
260
261 return $items;
262 }
263
271 public static function _lookupLastAccess($a_obj_id, $a_usr_id)
272 {
273 global $ilDB;
274
275 $result = $ilDB->queryF(
276 '
277 SELECT last_access FROM sahs_user
278 WHERE obj_id = %s
279 AND user_id = %s',
280 array('integer','integer'),
281 array($a_obj_id,$a_usr_id)
282 );
283
284 if ($ilDB->numRows($result)) {
285 $row = $ilDB->fetchAssoc($result);
286 return $row["last_access"];
287 }
288 return "";
289 }
290
291 public function getTrackedUsers($a_search)
292 {
293 global $ilDB, $ilUser;
294 //TODO: UK last_access is not correct if no Commit or last_visited_sco
295 // $query = 'SELECT user_id,MAX(c_timestamp) last_access, lastname, firstname FROM scorm_tracking st ' .
296 $query = 'SELECT user_id, last_access, lastname, firstname FROM sahs_user st ' .
297 'JOIN usr_data ud ON st.user_id = ud.usr_id ' .
298 'WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer');
299 if ($a_search) {
300 // $query .= ' AND (' . $ilDB->like('lastname', 'text', '%' . $a_search . '%') . ' OR ' . $ilDB->like('firstname', 'text', '%' . $a_search . '%') .')';
301 $query .= ' AND ' . $ilDB->like('lastname', 'text', '%' . $a_search . '%');
302 }
303 $query .= ' GROUP BY user_id, lastname, firstname, last_access';
304 $sco_set = $ilDB->query($query);
305
306 $items = array();
307 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
308 $items[] = $sco_rec;
309 }
310 return $items;
311 }
312
313
319 public function getAttemptsForUsers()
320 {
321 global $ilDB;
322 $query = 'SELECT user_id, package_attempts FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
323 $res = $ilDB->query($query);
324
325 $attempts = array();
326 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
327 $attempts[$row['user_id']] = (int) $row['package_attempts'];
328 }
329 return $attempts;
330 }
331
332
336 public function getAttemptsForUser($a_user_id)
337 {
338 global $ilDB;
339 $val_set = $ilDB->queryF(
340 'SELECT package_attempts FROM sahs_user WHERE obj_id = %s AND user_id = %s',
341 array('integer','integer'),
342 array($this->getId(),$a_user_id,0)
343 );
344
345 $val_rec = $ilDB->fetchAssoc($val_set);
346
347 if ($val_rec["package_attempts"] == null) {
348 $val_rec["package_attempts"]="";
349 }
350 return $val_rec["package_attempts"];
351 }
352
353
358 public function getModuleVersionForUsers()
359 {
360 global $ilDB;
361 $query = 'SELECT user_id, module_version FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
362 $res = $ilDB->query($query);
363
364 $versions = array();
365 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
366 $versions[$row['user_id']] = (int) $row['module_version'];
367 }
368 return $versions;
369 }
370
371
375 public function getModuleVersionForUser($a_user_id)
376 {
377 global $ilDB;
378 $val_set = $ilDB->queryF(
379 'SELECT module_version FROM sahs_user WHERE obj_id = %s AND user_id = %s',
380 array('integer','integer'),
381 array($this->getId(),$a_user_id,0)
382 );
383
384 $val_rec = $ilDB->fetchAssoc($val_set);
385
386 if ($val_rec["module_version"] == null) {
387 $val_rec["module_version"]="";
388 }
389 return $val_rec["module_version"];
390 }
391
399 public function getTrackingDataPerUser($a_sco_id, $a_user_id)
400 {
401 global $ilDB;
402
403 $data_set = $ilDB->queryF(
404 '
405 SELECT * FROM scorm_tracking
406 WHERE user_id = %s
407 AND sco_id = %s
408 AND obj_id = %s
409 ORDER BY lvalue',
410 array('integer','integer','integer'),
411 array($a_user_id,$a_sco_id,$this->getId())
412 );
413
414 $data = array();
415 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
416 $data[] = $data_rec;
417 }
418
419 return $data;
420 }
421
422 public function getTrackingDataAgg($a_user_id)
423 {
424 global $ilDB;
425
426 // get all users with any tracking data
427 $sco_set = $ilDB->queryF(
428 '
429 SELECT DISTINCT sco_id FROM scorm_tracking
430 WHERE obj_id = %s
431 AND user_id = %s
432 AND sco_id <> %s',
433 array('integer','integer','integer'),
434 array($this->getId(),$a_user_id,0)
435 );
436
437 $data = array();
438 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
439 $data_set = $ilDB->queryF(
440 '
441 SELECT * FROM scorm_tracking
442 WHERE obj_id = %s
443 AND sco_id = %s
444 AND user_id = %s
445 AND lvalue <> %s
446 AND (lvalue = %s
447 OR lvalue = %s
448 OR lvalue = %s)',
449 array('integer','integer','integer','text','text','text','text'),
450 array($this->getId(),
451 $sco_rec["sco_id"],
452 $a_user_id,
453 "package_attempts",
454 "cmi.core.lesson_status",
455 "cmi.core.total_time",
456 "cmi.core.score.raw")
457 );
458
459 $score = $time = $status = "";
460
461 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
462 switch ($data_rec["lvalue"]) {
463 case "cmi.core.lesson_status":
464 $status = $data_rec["rvalue"];
465 break;
466
467 case "cmi.core.total_time":
468 $time = $data_rec["rvalue"];
469 break;
470
471 case "cmi.core.score.raw":
472 $score = $data_rec["rvalue"];
473 break;
474 }
475 }
476 //create sco_object
477 include_once './Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php';
478 $sc_item = new ilSCORMItem($sco_rec["sco_id"]);
479 $data[] = array("sco_id"=>$sco_rec["sco_id"], "title" => $sc_item->getTitle(),
480 "score" => $score, "time" => $time, "status" => $status);
481 }
482 return (array) $data;
483 }
484
485 public function getTrackingDataAggSco($a_sco_id)
486 {
487 global $ilDB;
488
489 // get all users with any tracking data
490 $user_set = $ilDB->queryF(
491 '
492 SELECT DISTINCT user_id FROM scorm_tracking
493 WHERE obj_id = %s
494 AND sco_id = %s',
495 array('integer','integer'),
496 array($this->getId(),$a_sco_id)
497 );
498
499 $data = array();
500 while ($user_rec = $ilDB->fetchAssoc($user_set)) {
501 $data_set = $ilDB->queryF(
502 '
503 SELECT * FROM scorm_tracking
504 WHERE obj_id = %s
505 AND sco_id = %s
506 AND user_id = %s
507 AND (lvalue = %s
508 OR lvalue = %s
509 OR lvalue = %s)',
510 array('integer','integer','integer','text','text','text'),
511 array($this->getId(),
512 $a_sco_id,
513 $user_rec["user_id"],
514 "cmi.core.lesson_status",
515 "cmi.core.total_time",
516 "cmi.core.score.raw")
517 );
518
519 $score = $time = $status = "";
520
521 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
522 switch ($data_rec["lvalue"]) {
523 case "cmi.core.lesson_status":
524 $status = $data_rec["rvalue"];
525 break;
526
527 case "cmi.core.total_time":
528 $time = $data_rec["rvalue"];
529 break;
530
531 case "cmi.core.score.raw":
532 $score = $data_rec["rvalue"];
533 break;
534 }
535 }
536
537 $data[] = array("user_id" => $user_rec["user_id"],
538 "score" => $score, "time" => $time, "status" => $status);
539 }
540
541 return $data;
542 }
543
544
545
553 public function exportSelected($a_all, $a_users = array())
554 {
555 global $ilDB, $ilUser;
556 include_once('./Modules/ScormAicc/classes/class.ilSCORMTrackingItems.php');
557 include_once("./Services/Tracking/classes/class.ilLearningProgressBaseGUI.php");
558 include_once('./Services/PrivacySecurity/classes/class.ilPrivacySettings.php');
560 $allowExportPrivacy = $privacy->enabledExportSCORM();
561
562 $csv = "";
563 $query = 'SELECT * FROM sahs_user WHERE obj_id = %s';
564 if (count($a_users) >0) {
565 $query .= ' AND ' . $ilDB->in('user_id', $a_users, false, 'integer');
566 }
567 $res = $ilDB->queryF(
568 $query,
569 array('integer'),
570 array($this->getId())
571 );
572 while ($data = $ilDB->fetchAssoc($res)) {
573 $csv = $csv . $data["obj_id"]
574 . ";\"" . $this->getTitle() . "\""
575 . ";" . $data["module_version"]
576 . ";\"" . implode("\";\"", ilSCORMTrackingItems::userDataArrayForExport($data["user_id"], $allowExportPrivacy)) . "\""
577 . ";\"" . $data["last_access"] . "\""
578 . ";\"" . ilLearningProgressBaseGUI::__readStatus($data["obj_id"], $data["user_id"]) . "\"" //not $data["status"] because modifications to learning progress could have made before export
579 . ";" . $data["package_attempts"]
580 . ";" . $data["percentage_completed"]
581 . ";" . $data["sco_total_time_sec"]
582// . ";\"" . $certificateDate ."\""
583 . "\n";
584 }
586 $header = "LearningModuleId;LearningModuleTitle;LearningModuleVersion;" . str_replace(',', ';', $udh["cols"]) . ";"
587 . "LastAccess;Status;Attempts;percentageCompletedSCOs;SumTotal_timeSeconds\n";
588
589 $this->sendExportFile($header, $csv);
590 }
591
592
593 public function importTrackingData($a_file)
594 {
595 global $ilDB, $ilUser;
596
597 $error = 0;
598 //echo file_get_contents($a_file);
599 $method = null;
600
601 //lets import
602 $fhandle = fopen($a_file, "r");
603
604 //the top line is the field names
605 $fields = fgetcsv($fhandle, pow(2, 16), ';');
606 //lets check the import method
607 fclose($fhandle);
608
609 switch ($fields[0]) {
610 case "Scoid":
611 case "SCO-Identifier":
612 $error = $this->importRaw($a_file);
613 break;
614 case "Department":
615 case "LearningModuleId":
616 $error = $this->importSuccess($a_file);
617 break;
618 default:
619 return -1;
620 break;
621 }
622 return $error;
623 }
624
625 public function importSuccess($a_file)
626 {
627 global $ilDB, $ilUser;
628 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
629 $scos = array();
630 //get all SCO's of this object ONLY RELEVANT!
631 include_once './Services/Object/classes/class.ilObjectLP.php';
632 $olp = ilObjectLP::getInstance($this->getId());
633 $collection = $olp->getCollectionInstance();
634 if ($collection) {
635 $scos = $collection->getItems();
636 }
637
638 $fhandle = fopen($a_file, "r");
639
640 $obj_id = $this->getID();
641 $fields = fgetcsv($fhandle, pow(2, 16), ';');
642 $users = array();
643 $usersToDelete = array();
644 while (($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== false) {
645 $data = array_combine($fields, $csv_rows);
646 //no check the format - sufficient to import users
647 if ($data["Login"]) {
648 $user_id = $this->get_user_id($data["Login"]);
649 }
650 if ($data["login"]) {
651 $user_id = $this->get_user_id($data["login"]);
652 }
653 //add mail in future
654 if ($data["user"] && is_numeric($data["user"])) {
655 $user_id = (int) $data["user"];
656 }
657
658 if ($user_id>0) {
659 $last_access = ilUtil::now();
660 if ($data['Date']) {
661 $date_ex = explode('.', $data['Date']);
662 $last_access = implode('-', array($date_ex[2], $date_ex[1], $date_ex[0]));
663 }
664 if ($data['LastAccess']) {
665 $last_access = $data['LastAccess'];
666 }
667
669
670 if ($data["Status"]) {
671 if (is_int($data["Status"])) {
672 $status = $data["Status"];
673 } elseif ($data["Status"] == "0" || $data["Status"] == "1" || $data["Status"] == "2" || $data["Status"] == "3") {
674 $status = (int) $data["Status"];
675 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_NOT_ATTEMPTED) {
677 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_IN_PROGRESS) {
679 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_FAILED) {
681 }
682 }
683
684 $attempts = null;
685 if ($data["Attempts"]) {
686 $attempts = $data["Attempts"];
687 }
688
689 $percentage_completed = 0;
691 $percentage_completed = 100;
692 }
693 if ($data['percentageCompletedSCOs']) {
694 $percentage_completed = $data['percentageCompletedSCOs'];
695 }
696
697 $sco_total_time_sec = null;
698 if ($data['SumTotal_timeSeconds']) {
699 $sco_total_time_sec = $data['SumTotal_timeSeconds'];
700 }
701
703 $usersToDelete[] = $user_id;
704 } else {
705 $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
706 $users[] = $user_id;
707 }
708
710 foreach ($scos as $sco_id) {
711 $statement = $ilDB->queryF(
712 '
713 SELECT * FROM scorm_tracking
714 WHERE user_id = %s
715 AND sco_id = %s
716 AND lvalue = %s
717 AND obj_id = %s',
718 array('integer','integer','text','integer'),
719 array($user_id, $sco_id, 'cmi.core.lesson_status',$obj_id)
720 );
721 if ($ilDB->numRows($statement) > 0) {
722 $ilDB->update(
723 'scorm_tracking',
724 array(
725 'rvalue' => array('clob', 'completed'),
726 'c_timestamp' => array('timestamp', $last_access)
727 ),
728 array(
729 'user_id' => array('integer', $user_id),
730 'sco_id' => array('integer', $sco_id),
731 'lvalue' => array('text', 'cmi.core.lesson_status'),
732 'obj_id' => array('integer', $obj_id)
733 )
734 );
735 } else {
736 $ilDB->insert('scorm_tracking', array(
737 'obj_id' => array('integer', $obj_id),
738 'user_id' => array('integer', $user_id),
739 'sco_id' => array('integer', $sco_id),
740 'lvalue' => array('text', 'cmi.core.lesson_status'),
741 'rvalue' => array('clob', 'completed'),
742 'c_timestamp' => array('timestamp', $last_access)
743 ));
744 }
745 }
746 }
747 } else {
748 //echo "Warning! User $csv_rows[0] does not exist in ILIAS. Data for this user was skipped.\n";
749 }
750 }
751 if (count($usersToDelete)>0) {
752 // include_once("./Services/Tracking/classes/class.ilLPMarks.php");
753 // ilLPMarks::_deleteForUsers($this->getId(), $usersToDelete);
754 $this->deleteTrackingDataOfUsers($usersToDelete);
755 }
756 include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
758 return 0;
759 }
760
761 public function importSuccessForSahsUser($user_id, $last_access, $status, $attempts=null, $percentage_completed=null, $sco_total_time_sec=null)
762 {
763 global $ilDB;
764 $statement = $ilDB->queryF(
765 'SELECT * FROM sahs_user WHERE obj_id = %s AND user_id = %s',
766 array('integer','integer'),
767 array($this->getID(),$user_id)
768 );
769 if ($ilDB->numRows($statement) > 0) {
770 $ilDB->update(
771 'sahs_user',
772 array(
773 'last_access' => array('timestamp', $last_access),
774 'status' => array('integer', $status),
775 'package_attempts' => array('integer', $attempts),
776 'percentage_completed' => array('integer', $percentage_completed),
777 'sco_total_time_sec' => array('integer', $sco_total_time_sec)
778 ),
779 array(
780 'obj_id' => array('integer', $this->getID()),
781 'user_id' => array('integer', $user_id)
782 )
783 );
784 } else {
785 $ilDB->insert('sahs_user', array(
786 'obj_id' => array('integer', $this->getID()),
787 'user_id' => array('integer', $user_id),
788 'last_access' => array('timestamp', $last_access),
789 'status' => array('integer', $status),
790 'package_attempts' => array('integer', $attempts),
791 'percentage_completed' => array('integer', $percentage_completed),
792 'sco_total_time_sec' => array('integer', $sco_total_time_sec)
793 ));
794 }
795
796 include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
797 ilChangeEvent::_recordReadEvent("sahs", (int) $_GET["ref_id"], $this->getID(), $user_id, false, $attempts, $sco_total_time_sec);
798 }
799
805 private function parseUserId($il_id)
806 {
807 global $ilSetting;
808
809 $parts = explode('_', $il_id);
810
811 if (!count((array) $parts)) {
812 return 0;
813 }
814 if (!isset($parts[2]) or !isset($parts[3])) {
815 return 0;
816 }
817 if ($parts[2] != $ilSetting->get('inst_id', $parts[2])) {
818 return 0;
819 }
820 return $parts[3];
821 }
822
830 private function importRaw($a_file)
831 {
832 global $ilDB, $ilUser,$lng;
833 $lng->loadLanguageModule("scormtrac");
834
835 $fhandle = fopen($a_file, "r");
836
837 $fields = fgetcsv($fhandle, pow(2, 16), ';');
838 $users = array();
839 $a_last_access = array();
840 $a_time = array();
841 $a_package_attempts = array();
842 $a_module_version = array();
843 while (($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== false) {
844 $data = array_combine($fields, $csv_rows);
845 if ($data['Userid']) {
846 $user_id = $this->parseUserId($data['Userid']);
847 } elseif ($data[$lng->txt("user")]) {
848 if (is_int($data[$lng->txt("user")])) {
849 $user_id = $data[$lng->txt("user")];
850 }
851 }
852 if ($data[$lng->txt("login")]) {
853 $user_id = $this->get_user_id($data[$lng->txt("login")]);
854 }
855 if (!$user_id) {
856 continue;
857 }
858
859 if ($data['Scoid']) {
860 $il_sco_id = $this->lookupSCOId($data['Scoid']);
861 }
862 if ($data[$lng->txt("identifierref")]) {
863 $il_sco_id = $this->lookupSCOId($data[$lng->txt("identifierref")]);
864 }
865 if (!$il_sco_id) {
866 continue;
867 }
868
869 $c_timestamp="";
870 if ($data['Timestamp']) {
871 $c_timestamp = $data['Timestamp'];
872 }
873 if ($data[$lng->txt("c_timestamp")]) {
874 $c_timestamp = $data[$lng->txt("c_timestamp")];
875 }
876 if ($c_timestamp == "") {
877 $date = new DateTime();
878 $c_timestamp = $date->getTimestamp();
879 } else {
880 if ($a_last_access[$user_id]) {
881 if ($a_last_access[$user_id] < $c_timestamp) {
882 $a_last_access[$user_id] = $c_timestamp;
883 }
884 } else {
885 $a_last_access[$user_id] = $c_timestamp;
886 }
887 }
888
889 if (!$data['Key']) {
890 continue;
891 }
892 if (!$data['Value']) {
893 $data['Value'] = "";
894 }
895
896 if ($data['Key'] == "cmi.core.total_time" && $data['Value'] != "") {
897 $tarr = explode(":", $data['Value']);
898 $sec = (int) $tarr[2] + (int) $tarr[1] * 60 +
899 (int) substr($tarr[0], strlen($tarr[0]) - 3) * 60 * 60;
900 if ($a_time[$user_id]) {
901 $a_time[$user_id] += $sec;
902 } else {
903 $a_time[$user_id] = $sec;
904 }
905 }
906 //do the actual import
907 if ($il_sco_id > 0) {
908 $statement = $ilDB->queryF(
909 '
910 SELECT * FROM scorm_tracking
911 WHERE user_id = %s
912 AND sco_id = %s
913 AND lvalue = %s
914 AND obj_id = %s',
915 array('integer', 'integer', 'text', 'integer'),
916 array($user_id, $il_sco_id, $data['Key'], $this->getID())
917 );
918 if ($ilDB->numRows($statement) > 0) {
919 $ilDB->update(
920 'scorm_tracking',
921 array(
922 'rvalue' => array('clob', $data['Value']),
923 'c_timestamp' => array('timestamp', $c_timestamp)
924 ),
925 array(
926 'user_id' => array('integer', $user_id),
927 'sco_id' => array('integer', $il_sco_id),
928 'lvalue' => array('text', $data['Key']),
929 'obj_id' => array('integer', $this->getId())
930 )
931 );
932 } else {
933 $ilDB->insert('scorm_tracking', array(
934 'obj_id' => array('integer', $this->getId()),
935 'user_id' => array('integer', $user_id),
936 'sco_id' => array('integer', $il_sco_id),
937 'lvalue' => array('text', $data['Key']),
938 'rvalue' => array('clob', $data['Value']),
939 'c_timestamp' => array('timestamp', $data['Timestamp'])
940 ));
941 }
942 }
943 // $package_attempts = 1;
944 if ($il_sco_id == 0) {
945 if ($data['Key'] == "package_attempts") {
946 $a_package_attempts[$user_id] = $data['Value'];
947 }
948 // if ($data['Key'] == "module_version") $a_module_version[$user_id] = $data['Value'];
949 }
950 if (!in_array($user_id, $users)) {
951 $users[] = $user_id;
952 }
953 }
954 fclose($fhandle);
955
956 //UK determineStatus, percentage_completed and syncGlobalStatus
957 include_once './Services/Tracking/classes/class.ilLPStatusWrapper.php';
959
960 // include_once './Services/Tracking/classes/status/class.ilLPStatusSCORM.php';
961 include_once './Services/Tracking/classes/class.ilLPStatus.php';
962 foreach ($users as $user_id) {
963 $attempts = 1;
964 if ($a_package_attempts[$user_id]) {
965 $attempts = $a_package_attempts[$user_id];
966 }
967 // $module_version = 1;
968 // if ($a_module_version[$user_id]) $module_version = $a_module_version[$user_id];
969 $sco_total_time_sec = null;
970 if ($a_time[$user_id]) {
971 $sco_total_time_sec = $a_time[$user_id];
972 }
973 $last_access = null;
974 if ($a_last_access[$user_id]) {
975 $last_access = $a_last_access[$user_id];
976 }
977 // $status = ilLPStatusWrapper::_determineStatus($this->getId(),$user_id);
978 $status = ilLPStatus::_lookupStatus($this->getId(), $user_id);
979 // $percentage_completed = ilLPStatusSCORM::determinePercentage($this->getId(),$user_id);
980 $percentage_completed = ilLPStatus::_lookupPercentage($this->getId(), $user_id);
981
982 $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
983 }
984
985 return 0;
986 }
987
988
994 public function decreaseAttemptsForUser($a_user_id)
995 {
996 global $ilDB;
997
998 foreach ($a_user_id as $user) {
999 //first check if there is a package_attempts entry
1000 $val_set = $ilDB->queryF(
1001 'SELECT package_attempts FROM sahs_user WHERE user_id = %s AND obj_id = %s',
1002 array('integer','integer'),
1003 array($user,$this->getID())
1004 );
1005
1006 $val_rec = $ilDB->fetchAssoc($val_set);
1007
1008 if ($val_rec["package_attempts"] != null && $val_rec["package_attempts"] != 0) {
1009 $new_rec = 0;
1010 //decrease attempt by 1
1011 if ((int) $val_rec["package_attempts"] > 0) {
1012 $new_rec = (int) $val_rec["package_attempts"]-1;
1013 }
1014 $ilDB->manipulateF(
1015 'UPDATE sahs_user SET package_attempts = %s WHERE user_id = %s AND obj_id = %s',
1016 array('integer','integer','integer'),
1017 array($new_rec,$user,$this->getID())
1018 );
1019
1020 //following 2 lines were before 4.4 only for SCORM 1.2
1021 include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
1022 ilLPStatusWrapper::_updateStatus($this->getId(), $user);
1023 }
1024 }
1025 }
1026
1027
1028 //helper function
1029 public function get_user_id($a_login)
1030 {
1031 global $ilDB, $ilUser;
1032
1033 $val_set = $ilDB->queryF(
1034 'SELECT * FROM usr_data WHERE(login=%s)',
1035 array('text'),
1036 array($a_login)
1037 );
1038 $val_rec = $ilDB->fetchAssoc($val_set);
1039
1040 if (count($val_rec)>0) {
1041 return $val_rec['usr_id'];
1042 } else {
1043 return null;
1044 }
1045 }
1046
1047
1051 private function lookupSCOId($a_referrer)
1052 {
1053 global $ilDB, $ilUser;
1054
1055 //non specific SCO entries
1056 if ($a_referrer=="0") {
1057 return 0;
1058 }
1059
1060 $val_set = $ilDB->queryF(
1061 '
1062 SELECT obj_id FROM sc_item,scorm_tree
1063 WHERE (obj_id = child
1064 AND identifierref = %s
1065 AND slm_id = %s)',
1066 array('text','integer'),
1067 array($a_referrer,$this->getID())
1068 );
1069 $val_rec = $ilDB->fetchAssoc($val_set);
1070
1071 return $val_rec["obj_id"];
1072 }
1073
1077 public function getUserIdEmail($a_mail)
1078 {
1079 global $ilDB, $ilUser;
1080
1081 $val_set = $ilDB->queryF(
1082 'SELECT usr_id FROM usr_data WHERE(email=%s)',
1083 array('text'),
1084 array($a_mail)
1085 );
1086 $val_rec = $ilDB->fetchAssoc($val_set);
1087
1088
1089 return $val_rec["usr_id"];
1090 }
1091
1092
1096 public function sendExportFile($a_header, $a_content)
1097 {
1098 $timestamp = time();
1099 $refid = $this->getRefId();
1100 $filename = "scorm_tracking_" . $refid . "_" . $timestamp . ".csv";
1102 exit;
1103 }
1104
1110 public static function _getAllScoIds($a_id)
1111 {
1112 global $ilDB;
1113
1114 $scos = array();
1115
1116 $val_set = $ilDB->queryF(
1117 '
1118 SELECT scorm_object.obj_id,
1119 scorm_object.title,
1120 scorm_object.c_type,
1121 scorm_object.slm_id,
1122 scorm_object.obj_id scoid
1123 FROM scorm_object,sc_item,sc_resource
1124 WHERE(scorm_object.slm_id = %s
1125 AND scorm_object.obj_id = sc_item.obj_id
1126 AND sc_item.identifierref = sc_resource.import_id
1127 AND sc_resource.scormtype = %s)
1128 GROUP BY scorm_object.obj_id,
1129 scorm_object.title,
1130 scorm_object.c_type,
1131 scorm_object.slm_id,
1132 scorm_object.obj_id ',
1133 array('integer', 'text'),
1134 array($a_id,'sco')
1135 );
1136
1137 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1138 array_push($scos, $val_rec['scoid']);
1139 }
1140 return $scos;
1141 }
1142
1151 public static function _getStatusForUser($a_id, $a_user, $a_allScoIds, $a_numerical=false)
1152 {
1153 global $ilDB, $lng;
1154
1155 $scos = $a_allScoIds;
1156 //check if all SCO's are completed
1157 $scos_c = implode(',', $scos);
1158
1159 $val_set = $ilDB->queryF(
1160 '
1161 SELECT * FROM scorm_tracking
1162 WHERE (user_id = %s
1163 AND obj_id = %s
1164 AND ' . $ilDB->in('sco_id', $scos, false, 'integer') . '
1165 AND ((lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'completed') . ')
1166 OR (lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'passed') . ')))',
1167 array('integer','integer','text','text'),
1168 array($a_user,$a_id,'cmi.core.lesson_status', 'cmi.core.lesson_status')
1169 );
1170 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1171 $key = array_search($val_rec['sco_id'], $scos);
1172 unset($scos[$key]);
1173 }
1174 //check for completion
1175 if (count($scos) == 0) {
1176 $completion = ($a_numerical===true) ? true: $lng->txt("cont_complete");
1177 }
1178 if (count($scos) > 0) {
1179 $completion = ($a_numerical===true) ? false: $lng->txt("cont_incomplete");
1180 }
1181 return $completion;
1182 }
1183
1190 public static function _getCourseCompletionForUser($a_id, $a_user)
1191 {
1193 }
1194
1195 public function getAllScoIds()
1196 {
1197 global $ilDB;
1198
1199 $scos = array();
1200 //get all SCO's of this object
1201 $val_set = $ilDB->queryF(
1202 '
1203 SELECT scorm_object.obj_id,
1204 scorm_object.title,
1205 scorm_object.c_type,
1206 scorm_object.slm_id,
1207 scorm_object.obj_id scoid
1208 FROM scorm_object, sc_item,sc_resource
1209 WHERE(scorm_object.slm_id = %s
1210 AND scorm_object.obj_id = sc_item.obj_id
1211 AND sc_item.identifierref = sc_resource.import_id
1212 AND sc_resource.scormtype = %s )
1213 GROUP BY scorm_object.obj_id,
1214 scorm_object.title,
1215 scorm_object.c_type,
1216 scorm_object.slm_id,
1217 scorm_object.obj_id',
1218 array('integer','text'),
1219 array($this->getId(),'sco')
1220 );
1221
1222 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1223 array_push($scos, $val_rec['scoid']);
1224 }
1225 return $scos;
1226 }
1227
1228 public function getStatusForUser($a_user, $a_allScoIds, $a_numerical=false)
1229 {
1230 global $ilDB;
1231 $scos = $a_allScoIds;
1232 //loook up status
1233 //check if all SCO's are completed
1234 $scos_c = implode(',', $scos);
1235
1236 $val_set = $ilDB->queryF(
1237 '
1238 SELECT sco_id FROM scorm_tracking
1239 WHERE (user_id = %s
1240 AND obj_id = %s
1241 AND ' . $ilDB->in('sco_id', $scos, false, 'integer') . '
1242 AND ((lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'completed') . ') OR (lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'passed') . ') ) )',
1243 array('integer','integer','text','text',),
1244 array($a_user,$this->getID(),'cmi.core.lesson_status','cmi.core.lesson_status')
1245 );
1246 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1247 $key = array_search($val_rec['sco_id'], $scos);
1248 unset($scos[$key]);
1249 }
1250 //check for completion
1251 if (count($scos) == 0) {
1252 $completion = ($a_numerical===true) ? true: $this->lng->txt("cont_complete");
1253 }
1254 if (count($scos) > 0) {
1255 $completion = ($a_numerical===true) ? false: $this->lng->txt("cont_incomplete");
1256 }
1257 return $completion;
1258 }
1259
1260 public function getCourseCompletionForUser($a_user)
1261 {
1262 return $this->getStatusForUser($a_user, $this->getAllScoIds, true);
1263 }
1264
1265 //to be called from IlObjUser
1266 public static function _removeTrackingDataForUser($user_id)
1267 {
1268 global $ilDB;
1269 //gobjective
1270 $ilDB->manipulateF(
1271 'DELETE FROM scorm_tracking WHERE user_id = %s',
1272 array('integer'),
1273 array($user_id)
1274 );
1275 $ilDB->manipulateF(
1276 'DELETE FROM sahs_user WHERE user_id = %s',
1277 array('integer'),
1278 array($user_id)
1279 );
1280 }
1281
1282 public static function _getScoresForUser($a_item_id, $a_user_id)
1283 {
1284 global $ilDB;
1285
1286 $retAr = array("raw" => null, "max" => null, "scaled" => null);
1287 $val_set = $ilDB->queryF(
1288 "
1289 SELECT lvalue, rvalue FROM scorm_tracking
1290 WHERE sco_id = %s
1291 AND user_id = %s
1292 AND (lvalue = 'cmi.core.score.raw' OR lvalue = 'cmi.core.score.max')",
1293 array('integer', 'integer'),
1294 array($a_item_id, $a_user_id)
1295 );
1296 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1297 if ($val_rec['lvalue'] == "cmi.core.score.raw") {
1298 $retAr["raw"] = $val_rec["rvalue"];
1299 }
1300 if ($val_rec['lvalue'] == "cmi.core.score.max") {
1301 $retAr["max"] = $val_rec["rvalue"];
1302 }
1303 }
1304 if ($retAr["raw"] != null && $retAr["max"] != null) {
1305 $retAr["scaled"] = ($retAr["raw"] / $retAr["max"]);
1306 }
1307
1308 return $retAr;
1309 }
1310
1311
1312 public function getLastVisited($user_id)
1313 {
1314 global $ilDB;
1315 $val_set = $ilDB->queryF(
1316 'SELECT last_visited FROM sahs_user WHERE obj_id = %s AND user_id = %s',
1317 array('integer','integer'),
1318 array($this->getID(),$user_id)
1319 );
1320 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1321 if ($val_rec["last_visited"] != null) {
1322 return "" . $val_rec["last_visited"];
1323 }
1324 }
1325 return '0';
1326 }
1327
1328 public function deleteTrackingDataOfUsers($a_users)
1329 {
1330 global $ilDB;
1331 include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
1332 include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
1333
1335
1336 foreach ($a_users as $user) {
1337 $ilDB->manipulateF(
1338 '
1339 DELETE FROM scorm_tracking
1340 WHERE user_id = %s
1341 AND obj_id = %s',
1342 array('integer', 'integer'),
1343 array($user, $this->getID())
1344 );
1345
1346 $ilDB->manipulateF(
1347 '
1348 DELETE FROM sahs_user
1349 WHERE user_id = %s
1350 AND obj_id = %s',
1351 array('integer', 'integer'),
1352 array($user, $this->getID())
1353 );
1354
1355 ilLPStatusWrapper::_updateStatus($this->getId(), $user);
1356 }
1357 }
1358}
$result
$n
Definition: RandomTest.php:85
$users
Definition: authpage.php:44
foreach($mandatory_scripts as $file) $timestamp
Definition: buildRTE.php:81
$_GET["client_id"]
$_POST["username"]
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 _deleteReadEventsForUsers($a_obj_id, array $a_user_ids)
static _updateStatus($a_obj_id, $a_usr_id, $a_obj=null, $a_percentage=false, $a_force_raise=false)
Update status.
static _refreshStatus($a_obj_id, $a_users=null)
Set dirty.
const LP_STATUS_COMPLETED_NUM
static _lookupStatus($a_obj_id, $a_user_id, $a_create=true)
Lookup status.
const LP_STATUS_FAILED
const LP_STATUS_IN_PROGRESS_NUM
const LP_STATUS_NOT_ATTEMPTED_NUM
const LP_STATUS_FAILED_NUM
static _lookupPercentage($a_obj_id, $a_user_id)
Lookup percentage.
const LP_STATUS_NOT_ATTEMPTED
const LP_STATUS_IN_PROGRESS
static __readStatus($a_obj_id, $user_id)
Class ilObjSCORMLearningModule.
getDataDirectory($mode="filesystem")
get data directory of lm
Class ilObjSCORMLearningModule.
static _getScoresForUser($a_item_id, $a_user_id)
getStatusForUser($a_user, $a_allScoIds, $a_numerical=false)
setLearningProgressSettingsAtUpload()
set settings for learning progress determination per default at upload
getModuleVersionForUsers()
Get module version for users.
importRaw($a_file)
Import raw data @global ilDB $ilDB @global ilObjUser $ilUser.
exportSelected($a_all, $a_users=array())
Export selected user tracking data @global ilDB $ilDB @global ilObjUser $ilUser.
static _getTrackingItems($a_obj_id)
get all tracking items of scorm object @access static
static _getStatusForUser($a_id, $a_user, $a_allScoIds, $a_numerical=false)
Get the status of a SCORM module for a given user.
getAttemptsForUser($a_user_id)
get number of atttempts for a certain user and package
lookupSCOId($a_referrer)
resolves manifest SCOID to internal ILIAS SCO ID
getAttemptsForUsers()
Get attempts for all users @global ilDB $ilDB.
parseUserId($il_id)
Parse il_usr_123_6 id.
static _lookupLastAccess($a_obj_id, $a_usr_id)
Return the last access timestamp for a given user.
importSuccessForSahsUser($user_id, $last_access, $status, $attempts=null, $percentage_completed=null, $sco_total_time_sec=null)
validate($directory)
Validate all XML-Files in a SCOM-Directory.
getTrackingDataPerUser($a_sco_id, $a_user_id)
Get tracking data per user @global ilDB $ilDB.
sendExportFile($a_header, $a_content)
send export file to browser
__construct($a_id=0, $a_call_by_reference=true)
Constructor @access public.
static _getCourseCompletionForUser($a_id, $a_user)
Get the completion of a SCORM module for a given user.
readObject()
read manifest file @access public
getTrackedItems()
get all tracked items of current user
static _getAllScoIds($a_id)
Get an array of id's for all Sco's in the module.
decreaseAttemptsForUser($a_user_id)
Decrease attempts for user @global ilDB $ilDB.
getUserIdEmail($a_mail)
assumes that only one account exists for a mailadress
getModuleVersionForUser($a_user_id)
get module version that tracking data for a user was recorded on
Validation of SCORM-XML Files.
static _enabledLearningProgress()
check wether learing progress is enabled or not
static getInstance($a_obj_id)
getRefId()
get reference id @access public
getId()
get object id @access public
getTitle()
get object title @access public
static _getInstance()
Get instance of ilPrivacySettings.
static userDataArrayForExport($user, $b_allowExportPrivacy=false)
SCORM Object Tree.
ILIAS Setting Class.
static deliverData($a_data, $a_filename, $mime="application/octet-stream", $charset="")
deliver data for download via browser.
static now()
Return current timestamp in Y-m-d H:i:s format.
$key
Definition: croninfo.php:18
$time
Definition: cron.php:21
redirection script todo: (a better solution should control the processing via a xml file)
global $ilSetting
Definition: privfeed.php:17
$query
global $ilErr
Definition: raiseError.php:16
foreach($_POST as $key=> $value) $res
global $ilDB
$lm_set
$ilUser
Definition: imgupload.php:18
$a_content
Definition: workflow.php:93