ILIAS  release_7 Revision v7.30-3-g800a261c036
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 $DIC;
94 $ilErr = $DIC['ilErr'];
95
96 $needs_convert = false;
97
98 // convert imsmanifest.xml file in iso to utf8 if needed
99
100 $manifest_file = $this->getDataDirectory() . "/imsmanifest.xml";
101
102 // check if manifestfile exists and space left on device...
103 $check_for_manifest_file = is_file($manifest_file);
104
105 // if no manifestfile
106 if (!$check_for_manifest_file) {
107 $this->ilias->raiseError($this->lng->txt("Manifestfile $manifest_file not found!"), $this->ilias->error_obj->MESSAGE);
108 return;
109 }
110
111 if ($check_for_manifest_file) {
112 $manifest_file_array = file($manifest_file);
113 foreach ($manifest_file_array as $mfa) {
114 // if (seems_not_utf8($mfa))
115 if (@iconv('UTF-8', 'UTF-8', $mfa) != $mfa) {
116 $needs_convert = true;
117 break;
118 }
119 }
120
121 // to copy the file we need some extraspace, counted in bytes *2 ... we need 2 copies....
122 $estimated_manifest_filesize = filesize($manifest_file) * 2;
123
124 // i deactivated this, because it seems to fail on some windows systems (see bug #1795)
125 //$check_disc_free = disk_free_space($this->getDataDirectory()) - $estimated_manifest_filesize;
126 $check_disc_free = 2;
127 }
128
129 // if $manifest_file needs to be converted to UTF8
130 if ($needs_convert) {
131 // if file exists and enough space left on device
132 if ($check_for_manifest_file && ($check_disc_free > 1)) {
133
134 // create backup from original
135 if (!copy($manifest_file, $manifest_file . ".old")) {
136 echo "Failed to copy $manifest_file...<br>\n";
137 }
138
139 // read backupfile, convert each line to utf8, write line to new file
140 // php < 4.3 style
141 $f_write_handler = fopen($manifest_file . ".new", "w");
142 $f_read_handler = fopen($manifest_file . ".old", "r");
143 while (!feof($f_read_handler)) {
144 $zeile = fgets($f_read_handler);
145 //echo mb_detect_encoding($zeile);
146 fputs($f_write_handler, utf8_encode($zeile));
147 }
148 fclose($f_read_handler);
149 fclose($f_write_handler);
150
151 // copy new utf8-file to imsmanifest.xml
152 if (!copy($manifest_file . ".new", $manifest_file)) {
153 echo "Failed to copy $manifest_file...<br>\n";
154 }
155
156 if (!@is_file($manifest_file)) {
157 $this->ilias->raiseError(
158 $this->lng->txt("cont_no_manifest"),
159 $this->ilias->error_obj->WARNING
160 );
161 }
162 } else {
163 // gives out the specific error
164
165 if (!($check_disc_free > 1)) {
166 $this->ilias->raiseError($this->lng->txt("Not enough space left on device!"), $this->ilias->error_obj->MESSAGE);
167 }
168 return;
169 }
170 } else {
171 // check whether file starts with BOM (that confuses some sax parsers, see bug #1795)
172 $hmani = fopen($manifest_file, "r");
173 $start = fread($hmani, 3);
174 if (strtolower(bin2hex($start)) == "efbbbf") {
175 $f_write_handler = fopen($manifest_file . ".new", "w");
176 while (!feof($hmani)) {
177 $n = fread($hmani, 900);
178 fputs($f_write_handler, $n);
179 }
180 fclose($f_write_handler);
181 fclose($hmani);
182
183 // copy new utf8-file to imsmanifest.xml
184 if (!copy($manifest_file . ".new", $manifest_file)) {
185 echo "Failed to copy $manifest_file...<br>\n";
186 }
187 } else {
188 fclose($hmani);
189 }
190 }
191
192 //validate the XML-Files in the SCORM-Package
193 if ($_POST["validate"] == "y") {
194 if (!$this->validate($this->getDataDirectory())) {
195 $ilErr->raiseError("<b>Validation Error(s):</b><br>" . $this->getValidationSummary(), $ilErr->MESSAGE);
196 }
197 }
198
199 // start SCORM package parser
200 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMPackageParser.php");
201 // todo determine imsmanifest.xml path here...
202 $slmParser = new ilSCORMPackageParser($this, $manifest_file);
203 $slmParser->startParsing();
204 return $slmParser->getPackageTitle();
205 }
206
211 {
212 global $DIC;
213 $ilSetting = $DIC['ilSetting'];
214 //condition 1
215 $lm_set = new ilSetting("lm");
216 if ($lm_set->get('scorm_lp_auto_activate') != 1) {
217 return;
218 }
219 //condition 2
220 include_once("./Services/Tracking/classes/class.ilObjUserTracking.php");
222 return;
223 }
224
225 //set Learning Progress to Automatic by Collection of SCORM Items
226 include_once("./Services/Tracking/classes/class.ilLPObjSettings.php");
227 $lm_set = new ilLPObjSettings($this->getId());
229 $lm_set->insert();
230
231 //select all SCOs as relevant for Learning Progress
232 include_once("Services/Tracking/classes/collection/class.ilLPCollectionOfSCOs.php");
233 $collection = new ilLPCollectionOfSCOs($this->getId(), ilLPObjSettings::LP_MODE_SCORM);
234 $scos = array();
235 foreach ($collection->getPossibleItems() as $sco_id => $item) {
236 $scos[] = $sco_id;
237 }
238 $collection->activateEntries($scos);
239 }
243 public function getTrackedItems()
244 {
245 global $DIC;
246 $ilDB = $DIC['ilDB'];
247 $ilUser = $DIC['ilUser'];
248
249 $sco_set = $ilDB->queryF(
250 '
251 SELECT DISTINCT sco_id FROM scorm_tracking WHERE obj_id = %s',
252 array('integer'),
253 array($this->getId())
254 );
255
256 $items = array();
257 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
258 include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
259 $sc_item = new ilSCORMItem($sco_rec["sco_id"]);
260 if ($sc_item->getIdentifierRef() != "") {
261 $items[count($items)] = $sc_item;
262 }
263 }
264
265 return $items;
266 }
267
275 public static function _lookupLastAccess($a_obj_id, $a_usr_id)
276 {
277 global $DIC;
278 $ilDB = $DIC['ilDB'];
279
280 $result = $ilDB->queryF(
281 '
282 SELECT last_access FROM sahs_user
283 WHERE obj_id = %s
284 AND user_id = %s',
285 array('integer','integer'),
286 array($a_obj_id,$a_usr_id)
287 );
288
289 if ($ilDB->numRows($result)) {
290 $row = $ilDB->fetchAssoc($result);
291 return $row["last_access"];
292 }
293 return "";
294 }
295
296 public function getTrackedUsers($a_search)
297 {
298 global $DIC;
299 $ilDB = $DIC['ilDB'];
300 $ilUser = $DIC['ilUser'];
301 //TODO: UK last_access is not correct if no Commit or last_visited_sco
302 // $query = 'SELECT user_id,MAX(c_timestamp) last_access, lastname, firstname FROM scorm_tracking st ' .
303 $query = 'SELECT user_id, last_access, lastname, firstname FROM sahs_user st ' .
304 'JOIN usr_data ud ON st.user_id = ud.usr_id ' .
305 'WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer');
306 if ($a_search) {
307 // $query .= ' AND (' . $ilDB->like('lastname', 'text', '%' . $a_search . '%') . ' OR ' . $ilDB->like('firstname', 'text', '%' . $a_search . '%') .')';
308 $query .= ' AND ' . $ilDB->like('lastname', 'text', '%' . $a_search . '%');
309 }
310 $query .= ' GROUP BY user_id, lastname, firstname, last_access';
311 $sco_set = $ilDB->query($query);
312
313 $items = array();
314 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
315 $items[] = $sco_rec;
316 }
317 return $items;
318 }
319
320
326 public function getAttemptsForUsers()
327 {
328 global $DIC;
329 $ilDB = $DIC['ilDB'];
330 $query = 'SELECT user_id, package_attempts FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
331 $res = $ilDB->query($query);
332
333 $attempts = array();
334 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
335 $attempts[$row['user_id']] = (int) $row['package_attempts'];
336 }
337 return $attempts;
338 }
339
340
344 public function getAttemptsForUser($a_user_id)
345 {
346 global $DIC;
347 $ilDB = $DIC['ilDB'];
348 $val_set = $ilDB->queryF(
349 'SELECT package_attempts FROM sahs_user WHERE obj_id = %s AND user_id = %s',
350 array('integer','integer'),
351 array($this->getId(),$a_user_id)
352 );
353
354 $val_rec = $ilDB->fetchAssoc($val_set);
355
356 if ($val_rec["package_attempts"] == null) {
357 $val_rec["package_attempts"] = "";
358 }
359 return $val_rec["package_attempts"];
360 }
361
362
367 public function getModuleVersionForUsers()
368 {
369 global $DIC;
370 $ilDB = $DIC['ilDB'];
371 $query = 'SELECT user_id, module_version FROM sahs_user WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
372 $res = $ilDB->query($query);
373
374 $versions = array();
375 while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
376 $versions[$row['user_id']] = (int) $row['module_version'];
377 }
378 return $versions;
379 }
380
381
385 public function getModuleVersionForUser($a_user_id)
386 {
387 global $DIC;
388 $ilDB = $DIC['ilDB'];
389 $val_set = $ilDB->queryF(
390 'SELECT module_version FROM sahs_user WHERE obj_id = %s AND user_id = %s',
391 array('integer','integer'),
392 array($this->getId(),$a_user_id,0)
393 );
394
395 $val_rec = $ilDB->fetchAssoc($val_set);
396
397 if ($val_rec["module_version"] == null) {
398 $val_rec["module_version"] = "";
399 }
400 return $val_rec["module_version"];
401 }
402
410 public function getTrackingDataPerUser($a_sco_id, $a_user_id)
411 {
412 global $DIC;
413 $ilDB = $DIC['ilDB'];
414
415 $data_set = $ilDB->queryF(
416 '
417 SELECT * FROM scorm_tracking
418 WHERE user_id = %s
419 AND sco_id = %s
420 AND obj_id = %s
421 ORDER BY lvalue',
422 array('integer','integer','integer'),
423 array($a_user_id,$a_sco_id,$this->getId())
424 );
425
426 $data = array();
427 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
428 $data[] = $data_rec;
429 }
430
431 return $data;
432 }
433
434 public function getTrackingDataAgg($a_user_id)
435 {
436 global $DIC;
437 $ilDB = $DIC['ilDB'];
438
439 // get all users with any tracking data
440 $sco_set = $ilDB->queryF(
441 '
442 SELECT DISTINCT sco_id FROM scorm_tracking
443 WHERE obj_id = %s
444 AND user_id = %s
445 AND sco_id <> %s',
446 array('integer','integer','integer'),
447 array($this->getId(),$a_user_id,0)
448 );
449
450 $data = array();
451 while ($sco_rec = $ilDB->fetchAssoc($sco_set)) {
452 $data_set = $ilDB->queryF(
453 '
454 SELECT * FROM scorm_tracking
455 WHERE obj_id = %s
456 AND sco_id = %s
457 AND user_id = %s
458 AND lvalue <> %s
459 AND (lvalue = %s
460 OR lvalue = %s
461 OR lvalue = %s)',
462 array('integer','integer','integer','text','text','text','text'),
463 array($this->getId(),
464 $sco_rec["sco_id"],
465 $a_user_id,
466 "package_attempts",
467 "cmi.core.lesson_status",
468 "cmi.core.total_time",
469 "cmi.core.score.raw")
470 );
471
472 $score = $time = $status = "";
473
474 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
475 switch ($data_rec["lvalue"]) {
476 case "cmi.core.lesson_status":
477 $status = $data_rec["rvalue"];
478 break;
479
480 case "cmi.core.total_time":
481 $time = $data_rec["rvalue"];
482 break;
483
484 case "cmi.core.score.raw":
485 $score = $data_rec["rvalue"];
486 break;
487 }
488 }
489 //create sco_object
490 include_once './Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php';
491 $sc_item = new ilSCORMItem($sco_rec["sco_id"]);
492 $data[] = array("sco_id" => $sco_rec["sco_id"], "title" => $sc_item->getTitle(),
493 "score" => $score, "time" => $time, "status" => $status);
494 }
495 return (array) $data;
496 }
497
498 public function getTrackingDataAggSco($a_sco_id)
499 {
500 global $DIC;
501 $ilDB = $DIC['ilDB'];
502
503 // get all users with any tracking data
504 $user_set = $ilDB->queryF(
505 '
506 SELECT DISTINCT user_id FROM scorm_tracking
507 WHERE obj_id = %s
508 AND sco_id = %s',
509 array('integer','integer'),
510 array($this->getId(),$a_sco_id)
511 );
512
513 $data = array();
514 while ($user_rec = $ilDB->fetchAssoc($user_set)) {
515 $data_set = $ilDB->queryF(
516 '
517 SELECT * FROM scorm_tracking
518 WHERE obj_id = %s
519 AND sco_id = %s
520 AND user_id = %s
521 AND (lvalue = %s
522 OR lvalue = %s
523 OR lvalue = %s)',
524 array('integer','integer','integer','text','text','text'),
525 array($this->getId(),
526 $a_sco_id,
527 $user_rec["user_id"],
528 "cmi.core.lesson_status",
529 "cmi.core.total_time",
530 "cmi.core.score.raw")
531 );
532
533 $score = $time = $status = "";
534
535 while ($data_rec = $ilDB->fetchAssoc($data_set)) {
536 switch ($data_rec["lvalue"]) {
537 case "cmi.core.lesson_status":
538 $status = $data_rec["rvalue"];
539 break;
540
541 case "cmi.core.total_time":
542 $time = $data_rec["rvalue"];
543 break;
544
545 case "cmi.core.score.raw":
546 $score = $data_rec["rvalue"];
547 break;
548 }
549 }
550
551 $data[] = array("user_id" => $user_rec["user_id"],
552 "score" => $score, "time" => $time, "status" => $status);
553 }
554
555 return $data;
556 }
557
558
559
567 public function exportSelected($a_all, $a_users = array())
568 {
569 global $DIC;
570 $ilDB = $DIC['ilDB'];
571 $ilUser = $DIC['ilUser'];
572 include_once('./Modules/ScormAicc/classes/class.ilSCORMTrackingItems.php');
573 include_once("./Services/Tracking/classes/class.ilLearningProgressBaseGUI.php");
574 include_once('./Services/PrivacySecurity/classes/class.ilPrivacySettings.php');
576 $allowExportPrivacy = $privacy->enabledExportSCORM();
577
578 $csv = "";
579 $query = 'SELECT * FROM sahs_user WHERE obj_id = %s';
580 if (count($a_users) > 0) {
581 $query .= ' AND ' . $ilDB->in('user_id', $a_users, false, 'integer');
582 }
583 $res = $ilDB->queryF(
584 $query,
585 array('integer'),
586 array($this->getId())
587 );
588 while ($data = $ilDB->fetchAssoc($res)) {
589 $csv = $csv . $data["obj_id"]
590 . ";\"" . $this->getTitle() . "\""
591 . ";" . $data["module_version"]
592 . ";\"" . implode("\";\"", ilSCORMTrackingItems::userDataArrayForExport($data["user_id"], $allowExportPrivacy)) . "\""
593 . ";\"" . $data["last_access"] . "\""
594 . ";\"" . ilLearningProgressBaseGUI::__readStatus($data["obj_id"], $data["user_id"]) . "\"" //not $data["status"] because modifications to learning progress could have made before export
595 . ";" . $data["package_attempts"]
596 . ";" . $data["percentage_completed"]
597 . ";" . $data["sco_total_time_sec"]
598// . ";\"" . $certificateDate ."\""
599 . "\n";
600 }
602 $header = "LearningModuleId;LearningModuleTitle;LearningModuleVersion;" . str_replace(',', ';', $udh["cols"]) . ";"
603 . "LastAccess;Status;Attempts;percentageCompletedSCOs;SumTotal_timeSeconds\n";
604
605 $this->sendExportFile($header, $csv);
606 }
607
608
609 public function importTrackingData($a_file)
610 {
611 global $DIC;
612 $ilDB = $DIC['ilDB'];
613 $ilUser = $DIC['ilUser'];
614
615 $error = 0;
616 //echo file_get_contents($a_file);
617 $method = null;
618
619 //lets import
620 $fhandle = fopen($a_file, "r");
621
622 //the top line is the field names
623 $fields = fgetcsv($fhandle, pow(2, 16), ';');
624 //lets check the import method
625 fclose($fhandle);
626
627 switch ($fields[0]) {
628 case "Scoid":
629 case "SCO-Identifier":
630 $error = $this->importRaw($a_file);
631 break;
632 case "Department":
633 case "LearningModuleId":
634 $error = $this->importSuccess($a_file);
635 break;
636 default:
637 return -1;
638 break;
639 }
640 return $error;
641 }
642
643 public function importSuccess($a_file)
644 {
645 global $DIC;
646 $ilDB = $DIC['ilDB'];
647 $ilUser = $DIC['ilUser'];
648 include_once("./Services/Tracking/classes/class.ilLPStatus.php");
649 $scos = array();
650 //get all SCO's of this object ONLY RELEVANT!
651 include_once './Services/Object/classes/class.ilObjectLP.php';
652 $olp = ilObjectLP::getInstance($this->getId());
653 $collection = $olp->getCollectionInstance();
654 if ($collection) {
655 $scos = $collection->getItems();
656 }
657
658 $fhandle = fopen($a_file, "r");
659
660 $obj_id = $this->getID();
661 $fields = fgetcsv($fhandle, pow(2, 16), ';');
662 $users = array();
663 $usersToDelete = array();
664 while (($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== false) {
665 $data = array_combine($fields, $csv_rows);
666 //no check the format - sufficient to import users
667 if ($data["Login"]) {
668 $user_id = $this->get_user_id($data["Login"]);
669 }
670 if ($data["login"]) {
671 $user_id = $this->get_user_id($data["login"]);
672 }
673 //add mail in future
674 if ($data["user"] && is_numeric($data["user"])) {
675 $user_id = (int) $data["user"];
676 }
677
678 if ($user_id > 0) {
679 $last_access = ilUtil::now();
680 if ($data['Date']) {
681 $date_ex = explode('.', $data['Date']);
682 $last_access = implode('-', array($date_ex[2], $date_ex[1], $date_ex[0]));
683 }
684 if ($data['LastAccess']) {
685 $last_access = $data['LastAccess'];
686 }
687
689
690 if ($data["Status"]) {
691 if (is_int($data["Status"])) {
692 $status = $data["Status"];
693 } elseif ($data["Status"] == "0" || $data["Status"] == "1" || $data["Status"] == "2" || $data["Status"] == "3") {
694 $status = (int) $data["Status"];
695 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_NOT_ATTEMPTED) {
697 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_IN_PROGRESS) {
699 } elseif ($data["Status"] == ilLPStatus::LP_STATUS_FAILED) {
701 }
702 }
703
704 $attempts = null;
705 if ($data["Attempts"]) {
706 $attempts = $data["Attempts"];
707 }
708
709 $percentage_completed = 0;
711 $percentage_completed = 100;
712 }
713 if ($data['percentageCompletedSCOs']) {
714 $percentage_completed = $data['percentageCompletedSCOs'];
715 }
716
717 $sco_total_time_sec = null;
718 if ($data['SumTotal_timeSeconds']) {
719 $sco_total_time_sec = $data['SumTotal_timeSeconds'];
720 }
721
723 $usersToDelete[] = $user_id;
724 } else {
725 $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
726 $users[] = $user_id;
727 }
728
730 foreach ($scos as $sco_id) {
731 $statement = $ilDB->queryF(
732 '
733 SELECT * FROM scorm_tracking
734 WHERE user_id = %s
735 AND sco_id = %s
736 AND lvalue = %s
737 AND obj_id = %s',
738 array('integer','integer','text','integer'),
739 array($user_id, $sco_id, 'cmi.core.lesson_status',$obj_id)
740 );
741 if ($ilDB->numRows($statement) > 0) {
742 $ilDB->update(
743 'scorm_tracking',
744 array(
745 'rvalue' => array('clob', 'completed'),
746 'c_timestamp' => array('timestamp', $last_access)
747 ),
748 array(
749 'user_id' => array('integer', $user_id),
750 'sco_id' => array('integer', $sco_id),
751 'lvalue' => array('text', 'cmi.core.lesson_status'),
752 'obj_id' => array('integer', $obj_id)
753 )
754 );
755 } else {
756 $ilDB->insert('scorm_tracking', array(
757 'obj_id' => array('integer', $obj_id),
758 'user_id' => array('integer', $user_id),
759 'sco_id' => array('integer', $sco_id),
760 'lvalue' => array('text', 'cmi.core.lesson_status'),
761 'rvalue' => array('clob', 'completed'),
762 'c_timestamp' => array('timestamp', $last_access)
763 ));
764 }
765 }
766 }
767 } else {
768 //echo "Warning! User $csv_rows[0] does not exist in ILIAS. Data for this user was skipped.\n";
769 }
770 }
771 if (count($usersToDelete) > 0) {
772 // include_once("./Services/Tracking/classes/class.ilLPMarks.php");
773 // ilLPMarks::_deleteForUsers($this->getId(), $usersToDelete);
774 $this->deleteTrackingDataOfUsers($usersToDelete);
775 }
776 include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
778 return 0;
779 }
780
781 public function importSuccessForSahsUser($user_id, $last_access, $status, $attempts = null, $percentage_completed = null, $sco_total_time_sec = null)
782 {
783 global $DIC;
784 $ilDB = $DIC['ilDB'];
785 $statement = $ilDB->queryF(
786 'SELECT * FROM sahs_user WHERE obj_id = %s AND user_id = %s',
787 array('integer','integer'),
788 array($this->getID(),$user_id)
789 );
790 if ($ilDB->numRows($statement) > 0) {
791 $ilDB->update(
792 'sahs_user',
793 array(
794 'last_access' => array('timestamp', $last_access),
795 'status' => array('integer', $status),
796 'package_attempts' => array('integer', $attempts),
797 'percentage_completed' => array('integer', $percentage_completed),
798 'sco_total_time_sec' => array('integer', $sco_total_time_sec)
799 ),
800 array(
801 'obj_id' => array('integer', $this->getID()),
802 'user_id' => array('integer', $user_id)
803 )
804 );
805 } else {
806 $ilDB->insert('sahs_user', array(
807 'obj_id' => array('integer', $this->getID()),
808 'user_id' => array('integer', $user_id),
809 'last_access' => array('timestamp', $last_access),
810 'status' => array('integer', $status),
811 'package_attempts' => array('integer', $attempts),
812 'percentage_completed' => array('integer', $percentage_completed),
813 'sco_total_time_sec' => array('integer', $sco_total_time_sec)
814 ));
815 }
816
817 include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
818 ilChangeEvent::_recordReadEvent("sahs", (int) $_GET["ref_id"], $this->getID(), $user_id, false, $attempts, $sco_total_time_sec);
819 }
820
826 private function parseUserId($il_id)
827 {
828 global $DIC;
829 $ilSetting = $DIC['ilSetting'];
830
831 $parts = explode('_', $il_id);
832
833 if (!count((array) $parts)) {
834 return 0;
835 }
836 if (!isset($parts[2]) or !isset($parts[3])) {
837 return 0;
838 }
839 if ($parts[2] != $ilSetting->get('inst_id', $parts[2])) {
840 return 0;
841 }
842 return $parts[3];
843 }
844
852 private function importRaw($a_file)
853 {
854 global $DIC;
855 $ilDB = $DIC['ilDB'];
856 $ilUser = $DIC['ilUser'];
857 $lng = $DIC['lng'];
858 $lng->loadLanguageModule("scormtrac");
859
860 $fhandle = fopen($a_file, "r");
861
862 $fields = fgetcsv($fhandle, pow(2, 16), ';');
863 $users = array();
864 $a_last_access = array();
865 $a_time = array();
866 $a_package_attempts = array();
867 $a_module_version = array();
868 while (($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== false) {
869 $data = array_combine($fields, $csv_rows);
870 if ($data['Userid']) {
871 $user_id = $this->parseUserId($data['Userid']);
872 } elseif ($data[$lng->txt("user")]) {
873 if (is_int($data[$lng->txt("user")])) {
874 $user_id = $data[$lng->txt("user")];
875 }
876 }
877 if ($data[$lng->txt("login")]) {
878 $user_id = $this->get_user_id($data[$lng->txt("login")]);
879 }
880 if (!$user_id) {
881 continue;
882 }
883
884 if ($data['Scoid']) {
885 $il_sco_id = $this->lookupSCOId($data['Scoid']);
886 }
887 if ($data[$lng->txt("identifierref")]) {
888 $il_sco_id = $this->lookupSCOId($data[$lng->txt("identifierref")]);
889 }
890 if (!$il_sco_id) {
891 continue;
892 }
893
894 $c_timestamp = "";
895 if ($data['Timestamp']) {
896 $c_timestamp = $data['Timestamp'];
897 }
898 if ($data[$lng->txt("c_timestamp")]) {
899 $c_timestamp = $data[$lng->txt("c_timestamp")];
900 }
901 if ($c_timestamp == "") {
902 $date = new DateTime();
903 $c_timestamp = $date->getTimestamp();
904 } else {
905 if ($a_last_access[$user_id]) {
906 if ($a_last_access[$user_id] < $c_timestamp) {
907 $a_last_access[$user_id] = $c_timestamp;
908 }
909 } else {
910 $a_last_access[$user_id] = $c_timestamp;
911 }
912 }
913
914 if (!$data['Key']) {
915 continue;
916 }
917 if (!$data['Value']) {
918 $data['Value'] = "";
919 }
920
921 if ($data['Key'] == "cmi.core.total_time" && $data['Value'] != "") {
922 $tarr = explode(":", $data['Value']);
923 $sec = (int) $tarr[2] + (int) $tarr[1] * 60 +
924 (int) substr($tarr[0], strlen($tarr[0]) - 3) * 60 * 60;
925 if ($a_time[$user_id]) {
926 $a_time[$user_id] += $sec;
927 } else {
928 $a_time[$user_id] = $sec;
929 }
930 }
931 //do the actual import
932 if ($il_sco_id > 0) {
933 $statement = $ilDB->queryF(
934 '
935 SELECT * FROM scorm_tracking
936 WHERE user_id = %s
937 AND sco_id = %s
938 AND lvalue = %s
939 AND obj_id = %s',
940 array('integer', 'integer', 'text', 'integer'),
941 array($user_id, $il_sco_id, $data['Key'], $this->getID())
942 );
943 if ($ilDB->numRows($statement) > 0) {
944 $ilDB->update(
945 'scorm_tracking',
946 array(
947 'rvalue' => array('clob', $data['Value']),
948 'c_timestamp' => array('timestamp', $c_timestamp)
949 ),
950 array(
951 'user_id' => array('integer', $user_id),
952 'sco_id' => array('integer', $il_sco_id),
953 'lvalue' => array('text', $data['Key']),
954 'obj_id' => array('integer', $this->getId())
955 )
956 );
957 } else {
958 $ilDB->insert('scorm_tracking', array(
959 'obj_id' => array('integer', $this->getId()),
960 'user_id' => array('integer', $user_id),
961 'sco_id' => array('integer', $il_sco_id),
962 'lvalue' => array('text', $data['Key']),
963 'rvalue' => array('clob', $data['Value']),
964 'c_timestamp' => array('timestamp', $data['Timestamp'])
965 ));
966 }
967 }
968 // $package_attempts = 1;
969 if ($il_sco_id == 0) {
970 if ($data['Key'] == "package_attempts") {
971 $a_package_attempts[$user_id] = $data['Value'];
972 }
973 // if ($data['Key'] == "module_version") $a_module_version[$user_id] = $data['Value'];
974 }
975 if (!in_array($user_id, $users)) {
976 $users[] = $user_id;
977 }
978 }
979 fclose($fhandle);
980
981 //UK determineStatus, percentage_completed and syncGlobalStatus
982 include_once './Services/Tracking/classes/class.ilLPStatusWrapper.php';
984
985 // include_once './Services/Tracking/classes/status/class.ilLPStatusSCORM.php';
986 include_once './Services/Tracking/classes/class.ilLPStatus.php';
987 foreach ($users as $user_id) {
988 $attempts = 1;
989 if ($a_package_attempts[$user_id]) {
990 $attempts = $a_package_attempts[$user_id];
991 }
992 // $module_version = 1;
993 // if ($a_module_version[$user_id]) $module_version = $a_module_version[$user_id];
994 $sco_total_time_sec = null;
995 if ($a_time[$user_id]) {
996 $sco_total_time_sec = $a_time[$user_id];
997 }
998 $last_access = null;
999 if ($a_last_access[$user_id]) {
1000 $last_access = $a_last_access[$user_id];
1001 }
1002 // $status = ilLPStatusWrapper::_determineStatus($this->getId(),$user_id);
1003 $status = ilLPStatus::_lookupStatus($this->getId(), $user_id);
1004 // $percentage_completed = ilLPStatusSCORM::determinePercentage($this->getId(),$user_id);
1005 $percentage_completed = ilLPStatus::_lookupPercentage($this->getId(), $user_id);
1006
1007 $this->importSuccessForSahsUser($user_id, $last_access, $status, $attempts, $percentage_completed, $sco_total_time_sec);
1008 }
1009
1010 return 0;
1011 }
1012
1013
1014
1015 //helper function
1016 public function get_user_id($a_login)
1017 {
1018 global $DIC;
1019 $ilDB = $DIC['ilDB'];
1020 $ilUser = $DIC['ilUser'];
1021
1022 $val_set = $ilDB->queryF(
1023 'SELECT * FROM usr_data WHERE(login=%s)',
1024 array('text'),
1025 array($a_login)
1026 );
1027 $val_rec = $ilDB->fetchAssoc($val_set);
1028
1029 if (count($val_rec) > 0) {
1030 return $val_rec['usr_id'];
1031 } else {
1032 return null;
1033 }
1034 }
1035
1036
1040 private function lookupSCOId($a_referrer)
1041 {
1042 global $DIC;
1043 $ilDB = $DIC['ilDB'];
1044 $ilUser = $DIC['ilUser'];
1045
1046 //non specific SCO entries
1047 if ($a_referrer == "0") {
1048 return 0;
1049 }
1050
1051 $val_set = $ilDB->queryF(
1052 '
1053 SELECT obj_id FROM sc_item,scorm_tree
1054 WHERE (obj_id = child
1055 AND identifierref = %s
1056 AND slm_id = %s)',
1057 array('text','integer'),
1058 array($a_referrer,$this->getID())
1059 );
1060 $val_rec = $ilDB->fetchAssoc($val_set);
1061
1062 return $val_rec["obj_id"];
1063 }
1064
1068 public function getUserIdEmail($a_mail)
1069 {
1070 global $DIC;
1071 $ilDB = $DIC['ilDB'];
1072 $ilUser = $DIC['ilUser'];
1073
1074 $val_set = $ilDB->queryF(
1075 'SELECT usr_id FROM usr_data WHERE(email=%s)',
1076 array('text'),
1077 array($a_mail)
1078 );
1079 $val_rec = $ilDB->fetchAssoc($val_set);
1080
1081
1082 return $val_rec["usr_id"];
1083 }
1084
1085
1089 public function sendExportFile($a_header, $a_content)
1090 {
1091 $timestamp = time();
1092 $refid = $this->getRefId();
1093 $filename = "scorm_tracking_" . $refid . "_" . $timestamp . ".csv";
1094 ilUtil::deliverData($a_header . $a_content, $filename);
1095 exit;
1096 }
1097
1103 public static function _getAllScoIds($a_id)
1104 {
1105 global $DIC;
1106 $ilDB = $DIC['ilDB'];
1107
1108 $scos = array();
1109
1110 $val_set = $ilDB->queryF(
1111 '
1112 SELECT scorm_object.obj_id,
1113 scorm_object.title,
1114 scorm_object.c_type,
1115 scorm_object.slm_id,
1116 scorm_object.obj_id scoid
1117 FROM scorm_object,sc_item,sc_resource
1118 WHERE(scorm_object.slm_id = %s
1119 AND scorm_object.obj_id = sc_item.obj_id
1120 AND sc_item.identifierref = sc_resource.import_id
1121 AND sc_resource.scormtype = %s)
1122 GROUP BY scorm_object.obj_id,
1123 scorm_object.title,
1124 scorm_object.c_type,
1125 scorm_object.slm_id,
1126 scorm_object.obj_id ',
1127 array('integer', 'text'),
1128 array($a_id,'sco')
1129 );
1130
1131 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1132 array_push($scos, $val_rec['scoid']);
1133 }
1134 return $scos;
1135 }
1136
1145 public static function _getStatusForUser($a_id, $a_user, $a_allScoIds, $a_numerical = false)
1146 {
1147 global $DIC;
1148 $ilDB = $DIC['ilDB'];
1149 $lng = $DIC['lng'];
1150
1151 $scos = $a_allScoIds;
1152 //check if all SCO's are completed
1153 $scos_c = implode(',', $scos);
1154
1155 $val_set = $ilDB->queryF(
1156 '
1157 SELECT * FROM scorm_tracking
1158 WHERE (user_id = %s
1159 AND obj_id = %s
1160 AND ' . $ilDB->in('sco_id', $scos, false, 'integer') . '
1161 AND ((lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'completed') . ')
1162 OR (lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'passed') . ')))',
1163 array('integer','integer','text','text'),
1164 array($a_user,$a_id,'cmi.core.lesson_status', 'cmi.core.lesson_status')
1165 );
1166 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1167 $key = array_search($val_rec['sco_id'], $scos);
1168 unset($scos[$key]);
1169 }
1170 //check for completion
1171 if (count($scos) == 0) {
1172 $completion = ($a_numerical === true) ? true: $lng->txt("cont_complete");
1173 }
1174 if (count($scos) > 0) {
1175 $completion = ($a_numerical === true) ? false: $lng->txt("cont_incomplete");
1176 }
1177 return $completion;
1178 }
1179
1186 public static function _getCourseCompletionForUser($a_id, $a_user)
1187 {
1189 }
1190
1191 public function getAllScoIds()
1192 {
1193 global $DIC;
1194 $ilDB = $DIC['ilDB'];
1195
1196 $scos = array();
1197 //get all SCO's of this object
1198 $val_set = $ilDB->queryF(
1199 '
1200 SELECT scorm_object.obj_id,
1201 scorm_object.title,
1202 scorm_object.c_type,
1203 scorm_object.slm_id,
1204 scorm_object.obj_id scoid
1205 FROM scorm_object, sc_item,sc_resource
1206 WHERE(scorm_object.slm_id = %s
1207 AND scorm_object.obj_id = sc_item.obj_id
1208 AND sc_item.identifierref = sc_resource.import_id
1209 AND sc_resource.scormtype = %s )
1210 GROUP BY scorm_object.obj_id,
1211 scorm_object.title,
1212 scorm_object.c_type,
1213 scorm_object.slm_id,
1214 scorm_object.obj_id',
1215 array('integer','text'),
1216 array($this->getId(),'sco')
1217 );
1218
1219 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1220 array_push($scos, $val_rec['scoid']);
1221 }
1222 return $scos;
1223 }
1224
1225 public function getStatusForUser($a_user, $a_allScoIds, $a_numerical = false)
1226 {
1227 global $DIC;
1228 $ilDB = $DIC['ilDB'];
1229 $scos = $a_allScoIds;
1230 //loook up status
1231 //check if all SCO's are completed
1232 $scos_c = implode(',', $scos);
1233
1234 $val_set = $ilDB->queryF(
1235 '
1236 SELECT sco_id FROM scorm_tracking
1237 WHERE (user_id = %s
1238 AND obj_id = %s
1239 AND ' . $ilDB->in('sco_id', $scos, false, 'integer') . '
1240 AND ((lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'completed') . ') OR (lvalue = %s AND ' . $ilDB->like('rvalue', 'clob', 'passed') . ') ) )',
1241 array('integer','integer','text','text',),
1242 array($a_user,$this->getID(),'cmi.core.lesson_status','cmi.core.lesson_status')
1243 );
1244 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1245 $key = array_search($val_rec['sco_id'], $scos);
1246 unset($scos[$key]);
1247 }
1248 //check for completion
1249 if (count($scos) == 0) {
1250 $completion = ($a_numerical === true) ? true: $this->lng->txt("cont_complete");
1251 }
1252 if (count($scos) > 0) {
1253 $completion = ($a_numerical === true) ? false: $this->lng->txt("cont_incomplete");
1254 }
1255 return $completion;
1256 }
1257
1258 public function getCourseCompletionForUser($a_user)
1259 {
1260 return $this->getStatusForUser($a_user, $this->getAllScoIds, true);
1261 }
1262
1263 //to be called from IlObjUser
1264 public static function _removeTrackingDataForUser($user_id)
1265 {
1266 global $DIC;
1267 $ilDB = $DIC['ilDB'];
1268 //gobjective
1269 $ilDB->manipulateF(
1270 'DELETE FROM scorm_tracking WHERE user_id = %s',
1271 array('integer'),
1272 array($user_id)
1273 );
1274 $ilDB->manipulateF(
1275 'DELETE FROM sahs_user WHERE user_id = %s',
1276 array('integer'),
1277 array($user_id)
1278 );
1279 }
1280
1281 public static function _getScoresForUser($a_item_id, $a_user_id)
1282 {
1283 global $DIC;
1284 $ilDB = $DIC['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 $DIC;
1315 $ilDB = $DIC['ilDB'];
1316 $val_set = $ilDB->queryF(
1317 'SELECT last_visited FROM sahs_user WHERE obj_id = %s AND user_id = %s',
1318 array('integer','integer'),
1319 array($this->getID(),$user_id)
1320 );
1321 while ($val_rec = $ilDB->fetchAssoc($val_set)) {
1322 if ($val_rec["last_visited"] != null) {
1323 return "" . $val_rec["last_visited"];
1324 }
1325 }
1326 return '0';
1327 }
1328
1329 public function deleteTrackingDataOfUsers(array $a_users)
1330 {
1331 global $DIC;
1332 $ilDB = $DIC['ilDB'];
1333 include_once("./Services/Tracking/classes/class.ilChangeEvent.php");
1334 include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
1335
1337
1338 foreach ($a_users as $user) {
1339 $ilDB->manipulateF(
1340 '
1341 DELETE FROM scorm_tracking
1342 WHERE user_id = %s
1343 AND obj_id = %s',
1344 array('integer', 'integer'),
1345 array($user, $this->getID())
1346 );
1347
1348 $ilDB->manipulateF(
1349 '
1350 DELETE FROM sahs_user
1351 WHERE user_id = %s
1352 AND obj_id = %s',
1353 array('integer', 'integer'),
1354 array($user, $this->getID())
1355 );
1356
1357 ilLPStatusWrapper::_updateStatus($this->getId(), $user);
1358 }
1359 }
1360}
$result
$n
Definition: RandomTest.php:85
$filename
Definition: buildRTE.php:89
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 attempts 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.
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.
global $DIC
Definition: goto.php:24
$ilUser
Definition: imgupload.php:18
exit
Definition: login.php:29
__construct(Container $dic, ilPlugin $plugin)
@inheritDoc
redirection script todo: (a better solution should control the processing via a xml file)
global $ilSetting
Definition: privfeed.php:17
$query
$ilErr
Definition: raiseError.php:18
foreach($_POST as $key=> $value) $res
global $ilDB
$lm_set
$data
Definition: storeScorm.php:23