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