ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
5 require_once "classes/class.ilObject.php";
6 require_once "./Modules/ScormAicc/classes/class.ilObjSCORMValidator.php";
7 require_once "./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php";
8 //require_once "Services/MetaData/classes/class.ilMDLanguageItem.php";
9 
18 class ilObjSCORMLearningModule extends ilObjSAHSLearningModule
19 {
20  var $validator;
21 // var $meta_data;
22 
29  function ilObjSCORMLearningModule($a_id = 0, $a_call_by_reference = true)
30  {
31  $this->type = "sahs";
32  parent::ilObject($a_id,$a_call_by_reference);
33  }
34 
35 
42  function validate($directory)
43  {
44  $this->validator =& new ilObjSCORMValidator($directory);
45  $returnValue = $this->validator->validate();
46  return $returnValue;
47  }
48 
49  function getValidationSummary()
50  {
51  if(is_object($this->validator))
52  {
53  return $this->validator->getSummary();
54  }
55  return "";
56  }
57 
58  function getTrackingItems()
59  {
60  return ilObjSCORMLearningModule::_getTrackingItems($this->getId());
61  }
62 
63 
68  function _getTrackingItems($a_obj_id)
69  {
70  include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMTree.php");
71  $tree = new ilSCORMTree($a_obj_id);
72  $root_id = $tree->readRootId();
73 
74  $items = array();
75  $childs = $tree->getSubTree($tree->getNodeData($root_id));
76 
77  foreach($childs as $child)
78  {
79  if($child["c_type"] == "sit")
80  {
81  include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
82  $sc_item =& new ilSCORMItem($child["obj_id"]);
83  if ($sc_item->getIdentifierRef() != "")
84  {
85  $items[count($items)] =& $sc_item;
86  }
87  }
88  }
89 
90  return $items;
91  }
92 
97  function readObject()
98  {
99  global $ilErr;
100 
101  // the seems_utf8($str) function
102  include_once("include/inc.utf8checker.php");
103  $needs_convert = false;
104 
105  // convert imsmanifest.xml file in iso to utf8 if needed
106  // include_once("include/inc.convertcharset.php");
107  $manifest_file = $this->getDataDirectory()."/imsmanifest.xml";
108 
109  // check if manifestfile exists and space left on device...
110  $check_for_manifest_file = is_file($manifest_file);
111 
112  // if no manifestfile
113  if (!$check_for_manifest_file)
114  {
115  $this->ilias->raiseError($this->lng->txt("Manifestfile $manifest_file not found!"), $this->ilias->error_obj->MESSAGE);
116  return;
117  }
118 
119  if ($check_for_manifest_file)
120  {
121  $manifest_file_array = file($manifest_file);
122  foreach($manifest_file_array as $mfa)
123  {
124  if (seems_not_utf8($mfa))
125  {
126  $needs_convert = true;
127  break;
128  }
129  }
130 
131  // to copy the file we need some extraspace, counted in bytes *2 ... we need 2 copies....
132  $estimated_manifest_filesize = filesize($manifest_file) * 2;
133 
134  // i deactivated this, because it seems to fail on some windows systems (see bug #1795)
135  //$check_disc_free = disk_free_space($this->getDataDirectory()) - $estimated_manifest_filesize;
136  $check_disc_free = 2;
137  }
138 
139  // if $manifest_file needs to be converted to UTF8
140  if ($needs_convert)
141  {
142  // if file exists and enough space left on device
143  if ($check_for_manifest_file && ($check_disc_free > 1))
144  {
145 
146  // create backup from original
147  if (!copy($manifest_file, $manifest_file.".old"))
148  {
149  echo "Failed to copy $manifest_file...<br>\n";
150  }
151 
152  // read backupfile, convert each line to utf8, write line to new file
153  // php < 4.3 style
154  $f_write_handler = fopen($manifest_file.".new", "w");
155  $f_read_handler = fopen($manifest_file.".old", "r");
156  while (!feof($f_read_handler))
157  {
158  $zeile = fgets($f_read_handler);
159  //echo mb_detect_encoding($zeile);
160  fputs($f_write_handler, utf8_encode($zeile));
161  }
162  fclose($f_read_handler);
163  fclose($f_write_handler);
164 
165  // copy new utf8-file to imsmanifest.xml
166  if (!copy($manifest_file.".new", $manifest_file))
167  {
168  echo "Failed to copy $manifest_file...<br>\n";
169  }
170 
171  if (!@is_file($manifest_file))
172  {
173  $this->ilias->raiseError($this->lng->txt("cont_no_manifest"),
174  $this->ilias->error_obj->WARNING);
175  }
176  }
177  else
178  {
179  // gives out the specific error
180 
181  if (!($check_disc_free > 1))
182  $this->ilias->raiseError($this->lng->txt("Not enough space left on device!"),$this->ilias->error_obj->MESSAGE);
183  return;
184  }
185 
186  }
187  else
188  {
189  // check whether file starts with BOM (that confuses some sax parsers, see bug #1795)
190  $hmani = fopen($manifest_file, "r");
191  $start = fread($hmani, 3);
192  if (strtolower(bin2hex($start)) == "efbbbf")
193  {
194  $f_write_handler = fopen($manifest_file.".new", "w");
195  while (!feof($hmani))
196  {
197  $n = fread($hmani, 900);
198  fputs($f_write_handler, $n);
199  }
200  fclose($f_write_handler);
201  fclose($hmani);
202 
203  // copy new utf8-file to imsmanifest.xml
204  if (!copy($manifest_file.".new", $manifest_file))
205  {
206  echo "Failed to copy $manifest_file...<br>\n";
207  }
208  }
209  else
210  {
211  fclose($hmani);
212  }
213  }
214 
215  //validate the XML-Files in the SCORM-Package
216  if ($_POST["validate"] == "y")
217  {
218  if (!$this->validate($this->getDataDirectory()))
219  {
220  $ilErr->raiseError("<b>Validation Error(s):</b><br>".$this->getValidationSummary(),$ilErr->MESSAGE);
221  }
222  }
223 
224  // start SCORM package parser
225  include_once ("./Modules/ScormAicc/classes/SCORM/class.ilSCORMPackageParser.php");
226  // todo determine imsmanifest.xml path here...
227  $slmParser = new ilSCORMPackageParser($this, $manifest_file);
228  $slmParser->startParsing();
229  return $slmParser->getPackageTitle();
230  }
231 
232 
236  function getTrackedItems()
237  {
238  global $ilDB, $ilUser;
239 
240  $sco_set = $ilDB->queryF('
241  SELECT DISTINCT sco_id FROM scorm_tracking WHERE obj_id = %s',
242  array('integer'),array($this->getId()));
243 
244  $items = array();
245  while($sco_rec = $ilDB->fetchAssoc($sco_set))
246  {
247  include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
248  $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
249  if ($sc_item->getIdentifierRef() != "")
250  {
251  $items[count($items)] =& $sc_item;
252  }
253  }
254 
255  return $items;
256  }
257 
265  public static function _lookupLastAccess($a_obj_id, $a_usr_id)
266  {
267  global $ilDB;
268 
269  $result = $ilDB->queryF('
270  SELECT user_id,c_timestamp last_access FROM scorm_tracking
271  WHERE obj_id = %s
272  AND user_id = %s
273  ORDER BY c_timestamp DESC',
274  array('integer','integer'), array($a_obj_id,$a_usr_id));
275 
276  if ($ilDB->numRows($result))
277  {
278  $row = $ilDB->fetchAssoc($result);
279  return $row["last_access"];
280  }
281  return "";
282  }
283 
284  function getTrackedUsers($a_search)
285  {
286  global $ilUser, $ilDB, $ilUser;
287 
288  $sco_set = $ilDB->queryF('
289  SELECT user_id,MAX(c_timestamp) last_access FROM scorm_tracking
290  WHERE obj_id = %s
291  GROUP BY user_id',
292  array('integer'), array($this->getId()));
293 
294  $items = array();
295  while($sco_rec = $ilDB->fetchAssoc($sco_set))
296  {
297  if ($sco_rec['last_access'] != 0) {
298  $sco_rec['last_access'] = ilDatePresentation::formatDate(new ilDateTime($sco_rec['last_access'],IL_CAL_DATETIME));
299  } else {
300  $sco_rec['last_access'] = "";
301  }
302 
303  if (ilObject::_exists($sco_rec['user_id']) && ilObject::_lookUpType($sco_rec["user_id"])=="usr" ) {
304  $user = new ilObjUser($sco_rec['user_id']);
305  //$sco_rec['status'] = $this->getStatusForUser($sco_rec["user_id"]);
306  $sco_rec['version'] = $this->getModuleVersionForUser($sco_rec["user_id"]);
307  $sco_rec['attempts'] = $this->getAttemptsForUser($sco_rec["user_id"]);
308  $sco_rec['username'] = $user->getLastname().", ".$user->getFirstname();
309  if ($a_search != "" && (strpos(strtolower($user->getLastname()), strtolower($a_search)) !== false || strpos(strtolower($user->getFirstname()), strtolower($a_search)) !== false ) ) {
310  $items[] = $sco_rec;
311  } else if ($a_search == "") {
312  $items[] = $sco_rec;
313  }
314 
315  }
316  }
317 
318  return $items;
319  }
320 
324  function getAttemptsForUser($a_user_id){
325  global $ilDB;
326 
327  $val_set = $ilDB->queryF('
328  SELECT * FROM scorm_tracking
329  WHERE user_id = %s
330  AND sco_id = %s
331  AND lvalue = %s
332  AND obj_id = %s',
333  array('integer','integer','text','integer'),
334  array($a_user_id,0,'package_attempts',$this->getId()));
335 
336  $val_rec = $ilDB->fetchAssoc($val_set);
337 
338  $val_rec["rvalue"] = str_replace("\r\n", "\n", $val_rec["rvalue"]);
339  if ($val_rec["rvalue"] == null) {
340  $val_rec["rvalue"]="";
341  }
342  return $val_rec["rvalue"];
343  }
344 
345 
349  function getModuleVersionForUser($a_user_id){
350  global $ilDB;
351 
352  $val_set = $ilDB->queryF('
353  SELECT * FROM scorm_tracking
354  WHERE user_id = %s
355  AND sco_id = %s
356  AND lvalue = %s
357  AND obj_id = %s',
358  array('integer','integer','text','integer'),
359  array($a_user_id,0,'module_version',$this->getId()));
360 
361  $val_rec = $ilDB->fetchAssoc($val_set);
362 
363 
364  $val_rec["rvalue"] = str_replace("\r\n", "\n", $val_rec["rvalue"]);
365  if ($val_rec["rvalue"] == null) {
366  $val_rec["rvalue"]="";
367  }
368  return $val_rec["rvalue"];
369  }
370 
371  function getTrackingDataPerUser($a_sco_id, $a_user_id)
372  {
373  global $ilDB;
374 
375  $data_set = $ilDB->queryF('
376  SELECT * FROM scorm_tracking
377  WHERE user_id = %s
378  AND sco_id = %s
379  AND obj_id = %s
380  ORDER BY lvalue',
381  array('integer','integer','integer'),
382  array($a_user_id,$a_sco_id,$this->getId()));
383 
384  $data = array();
385  while($data_rec = $ilDB->fetchAssoc($data_set))
386 
387  {
388  $data[] = $data_rec;
389  }
390 
391  return $data;
392  }
393 
394  function getTrackingDataAgg($a_user_id)
395  {
396  global $ilDB;
397 
398  // get all users with any tracking data
399  $sco_set = $ilDB->queryF('
400  SELECT DISTINCT sco_id FROM scorm_tracking
401  WHERE obj_id = %s
402  AND user_id = %s
403  AND sco_id <> %s',
404  array('integer','integer','integer'),
405  array($this->getId(),$a_user_id,0));
406 
407  $data = array();
408  while($sco_rec = $ilDB->fetchAssoc($sco_set))
409  {
410  $data_set = $ilDB->queryF('
411  SELECT * FROM scorm_tracking
412  WHERE obj_id = %s
413  AND sco_id = %s
414  AND user_id = %s
415  AND lvalue <> %s
416  AND (lvalue = %s
417  OR lvalue = %s
418  OR lvalue = %s)',
419  array('integer','integer','integer','text','text','text','text'),
420  array($this->getId(),
421  $sco_rec["sco_id"],
422  $a_user_id,
423  "package_attempts",
424  "cmi.core.lesson_status",
425  "cmi.core.total_time",
426  "cmi.core.score.raw")
427  );
428 
429  $score = $time = $status = "";
430 
431  while($data_rec = $ilDB->fetchAssoc($data_set))
432  {
433  switch($data_rec["lvalue"])
434  {
435  case "cmi.core.lesson_status":
436  $status = $data_rec["rvalue"];
437  break;
438 
439  case "cmi.core.total_time":
440  $time = $data_rec["rvalue"];
441  break;
442 
443  case "cmi.core.score.raw":
444  $score = $data_rec["rvalue"];
445  break;
446  }
447  }
448  //create sco_object
449  $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
450  $data[] = array("sco_id"=>$sco_rec["sco_id"], "title" => $sc_item->getTitle(),
451  "score" => $score, "time" => $time, "status" => $status);
452 
453  }
454 
455  return $data;
456  }
457 
458  function getTrackingDataAggSco($a_sco_id)
459  {
460  global $ilDB;
461 
462  // get all users with any tracking data
463  $user_set = $ilDB->queryF('
464  SELECT DISTINCT user_id FROM scorm_tracking
465  WHERE obj_id = %s
466  AND sco_id = %s',
467  array('integer','integer'),
468  array($this->getId(),$a_sco_id));
469 
470  $data = array();
471  while($user_rec = $ilDB->fetchAssoc($user_set))
472  {
473 
474  $data_set = $ilDB->queryF('
475  SELECT * FROM scorm_tracking
476  WHERE obj_id = %s
477  AND sco_id = %s
478  AND user_id = %s
479  AND (lvalue = %s
480  OR lvalue = %s
481  OR lvalue = %s)',
482  array('integer','integer','integer','text','text','text'),
483  array($this->getId(),
484  $a_sco_id,
485  $user_rec["user_id"],
486  "cmi.core.lesson_status",
487  "cmi.core.total_time",
488  "cmi.core.score.raw")
489  );
490 
491  $score = $time = $status = "";
492 
493  while($data_rec = $ilDB->fetchAssoc($data_set))
494  {
495  switch($data_rec["lvalue"])
496  {
497  case "cmi.core.lesson_status":
498  $status = $data_rec["rvalue"];
499  break;
500 
501  case "cmi.core.total_time":
502  $time = $data_rec["rvalue"];
503  break;
504 
505  case "cmi.core.score.raw":
506  $score = $data_rec["rvalue"];
507  break;
508  }
509  }
510 
511  $data[] = array("user_id" => $user_rec["user_id"],
512  "score" => $score, "time" => $time, "status" => $status);
513  }
514 
515  return $data;
516  }
517 
518  function exportSelectedRaw($a_exportall=0, $a_user = array()) {
519 
520  global $ilDB, $ilUser;
521 
522  $csv = null;
523 
524  $user_array = array();
525 
526  if(!isset($_POST['user']) && $a_exportall == 0)
527  {
528  $this->ilias->raiseError($this->lng->txt('no_checkbox'), $this->ilias->error_obj->MESSAGE);
529  }
530 
531  if($a_exportall == 1)
532  {
533  $res = $ilDB->queryF(
534  'SELECT user_id FROM scorm_tracking WHERE obj_id = %s GROUP BY user_id',
535  array('integer'),
536  array($this->getId())
537  );
538  while($row = $ilDB->fetchAssoc($res))
539  {
540  $user_array[] = $row['user_id'];
541  }
542  }
543  else
544  {
545  $user_array = $a_user;
546  }
547 
548  //loop through users and get all data
549  foreach($user_array as $user)
550  {
551  //get user e-mail
552  if(ilObject::_exists($user) && ilObject::_lookUpType($user) == 'usr')
553  {
554  $e_user = new ilObjUser($user);
555  $email = $e_user->getEmail();
556 
557  //get sco related information
558  $query = 'SELECT rvalue, lvalue, identifierref, c_timestamp '
559  . 'FROM scorm_tracking '
560  . 'INNER JOIN sc_item ON sc_item.obj_id = scorm_tracking.sco_id '
561  . 'WHERE scorm_tracking.sco_id <> 0 AND user_id = %s AND scorm_tracking.obj_id = %s';
562  $res = $ilDB->queryF(
563  $query,
564  array('integer', 'integer'),
565  array($user, $this->getId())
566  );
567  while($row = $ilDB->fetchAssoc($res))
568  {
569  //get mail address for user-id
570  $sco_id = $row['identifierref'];
571  $key = $row['lvalue'];
572  $value = $row['rvalue'];
573  $timestamp = $row['c_timestamp'];
574  $csv = $csv. "$sco_id;$key;$value;$email;$timestamp;$user\n";
575  }
576 
577  //get sco unrelated information
578  $query = 'SELECT rvalue, lvalue, c_timestamp FROM scorm_tracking '
579  . 'WHERE sco_id = 0 AND user_id = %s AND obj_id = %s';
580  $res = $ilDB->queryF(
581  $query,
582  array('integer', 'integer'),
583  array($user, $this->getId())
584  );
585  while($row = $ilDB->fetchAssoc($res))
586  {
587  $key = $row['lvalue'];
588  $value = $row['rvalue'];
589  $timestamp = $row['c_timestamp'];
590  $csv = $csv. "0;$key;$value;$email;$timestamp;$user\n";
591  }
592  }
593  }
594  $header = "Scoid;Key;Value;Email;Timestamp;Userid\n";
595  $this->sendExportFile($header, $csv);
596  }
597 
598 
599  function exportSelected($a_exportall = 0, $a_user = array())
600  {
601  global $ilDB, $ilUser;
602 
603  $scos = array();
604 
605  //get all SCO's of this object
606  $query = 'SELECT scorm_object.obj_id, scorm_object.title, '
607  . 'scorm_object.c_type, scorm_object.slm_id, scorm_object.obj_id scoid '
608  . 'FROM scorm_object, sc_item, sc_resource '
609  . 'WHERE (scorm_object.slm_id = %s '
610  . 'AND scorm_object.obj_id = sc_item.obj_id '
611  . 'AND sc_item.identifierref = sc_resource.import_id '
612  . 'AND sc_resource.scormtype = %s) '
613  . 'GROUP BY scorm_object.obj_id, scorm_object.title, scorm_object.c_type, '
614  . 'scorm_object.slm_id, scorm_object.obj_id ';
615  $res = $ilDB->queryF(
616  $query,
617  array('integer', 'text'),
618  array($this->getId(), 'sco')
619  );
620  while($row = $ilDB->fetchAssoc($res))
621  {
622  $scos[] = $row['scoid'];
623  }
624 
625  $csv = null;
626 
627  //a module is completed when all SCO's are completed
628  $user_array = array();
629 
630  if($a_exportall == 1)
631  {
632  $query = 'SELECT user_id FROM scorm_tracking WHERE obj_id = %s GROUP BY user_id';
633  $res = $ilDB->queryF(
634  $query,
635  array('integer'),
636  array($this->getId())
637  );
638  while($row = $ilDB->fetchAssoc($res))
639  {
640  $user_array[] = $row['user_id'];
641  }
642  }
643  else
644  {
645  $user_array = $a_user;
646  }
647 
648  foreach($user_array as $user)
649  {
650  $scos_c = $scos;
651  //copy SCO_array
652  //check if all SCO's are completed
653  for($i = 0; $i < count($scos); $i++)
654  {
655  $query = 'SELECT * FROM scorm_tracking '
656  . 'WHERE (user_id = %s AND obj_id = %s AND sco_id = %s '
657  . 'AND ( (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').') OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').') ) )';
658  $res = $ilDB->queryF(
659  $query,
660  array('integer', 'integer', 'integer', 'text', 'text'),
661  array($user, $this->getId(), $scos[$i], 'cmi.core.lesson_status', 'cmi.core.lesson_status')
662  );
663  $data = $ilDB->fetchAssoc($res);
664  if(is_array($data) && count($data))
665  {
666  //delete from array
667  $key = array_search($scos[$i], $scos_c);
668  unset($scos_c[$key]);
669  }
670  }
671 
672  //check for completion
673  if(count($scos_c) == 0)
674  {
675  $completion = 1;
676  }
677  else
678  {
679  $completion = 0;
680  }
681 
682  if(ilObject::_exists($user) && ilObject::_lookUpType($user) == 'usr')
683  {
684  //write export entry
685  $e_user = new ilObjUser($user);
686  $login = $e_user->getLogin();
687  $firstname = $e_user->getFirstname();
688  $lastname = $e_user->getLastname();
689  $email = $e_user->getEmail();
690  $department = $e_user->getDepartment();
691 
692  //get the date for csv export
693  $query = 'SELECT MAX(c_timestamp) exp_date '
694  . 'FROM scorm_tracking '
695  . 'WHERE user_id = %s AND obj_id = %s';
696  $res = $ilDB->queryF(
697  $query,
698  array('integer', 'integer'),
699  array($user, $this->getId())
700  );
701  $data = $ilDB->fetchAssoc($res);
702  if(is_array($data) && count($data))
703  {
704  $validDate = false;
705 
706  $datetime = explode(' ', $data['exp_date']);
707  if(count($datetime) == 2)
708  {
709  $date = explode('-', $datetime[0]);
710  if(count($date) == 3 && checkdate($date[1], $date[2], $date[0]))
711  $validDate = true;
712  }
713 
714  if($validDate)
715  $date = date('d.m.Y', strtotime($data['exp_date']));
716  else
717  $date = '';
718  }
719  else
720  {
721  $date = '';
722  }
723  $csv = $csv. "$department;$login;$lastname;$firstname;$email;$date;$completion\n";
724  }
725  }
726  $header = "Department;Login;Lastname;Firstname;Email;Date;Status\n";
727  $this->sendExportFile($header, $csv);
728  }
729 
730 
731  function importTrackingData($a_file)
732  {
733  global $ilDB, $ilUser;
734 
735  $error = 0;
736  //echo file_get_contents($a_file);
737  $method = null;
738 
739  //lets import
740  $fhandle = fopen($a_file, "r");
741 
742  //the top line is the field names
743  $fields = fgetcsv($fhandle, 4096, ';');
744  //lets check the import method
745  fclose($fhandle);
746 
747  switch($fields[0])
748  {
749  case "Scoid":
750  $error = $this->importRaw($a_file);
751  break;
752  case "Department":
753  $error = $this->importSuccess($a_file);
754  break;
755  default:
756  return -1;
757  break;
758  }
759  return $error;
760  }
761 
762  function importSuccess($a_file) {
763 
764  global $ilDB, $ilUser;
765 
766  $scos = array();
767  //get all SCO's of this object
768 
769  $val_set = $ilDB->queryF('
770  SELECT scorm_object.obj_id,
771  scorm_object.title,
772  scorm_object.c_type,
773  scorm_object.slm_id,
774  scorm_object.obj_id scoid
775  FROM scorm_object,sc_item,sc_resource
776  WHERE(scorm_object.slm_id = %s
777  AND scorm_object.obj_id=sc_item.obj_id
778  AND sc_item.identifierref = sc_resource.import_id
779  AND sc_resource.scormtype = %s)
780  GROUP BY scorm_object.obj_id,
781  scorm_object.title,
782  scorm_object.c_type,
783  scorm_object.slm_id,
784  scorm_object.obj_id ',
785  array('integer','text'),
786  array($this->getId(),'sco')
787  );
788 
789  if (count($val_set)<1)
790  {
791  return -1;
792  }
793  while($rows_sco = $ilDB->fetchAssoc($val_set))
794  {
795  array_push($scos,$rows_sco['scoid']);
796  }
797 
798  $fhandle = fopen($a_file, "r");
799 
800  $obj_id = $ilDB->quote($this->getID());
801 
802  $fields = fgetcsv($fhandle, 4096, ';');
803  $users = array();
804  while(($csv_rows = fgetcsv($fhandle, 4096, ";")) !== FALSE) {
805  $data = array_combine($fields, $csv_rows);
806  //check the format
807  $statuscheck = 0;
808  if (count($csv_rows) == 6) {$statuscheck = 1;}
809 
810  if ($this->get_user_id($data["Login"])>0) {
811 
812  $user_id = $ilDB->quote($this->get_user_id($data["Login"]));
813  $users[] = $user_id;
814  $import = $data["Status"];
815  if ($import == "") {$import = 1;}
816  //iterate over all SCO's
817  if ($import == 1) {
818  foreach ($scos as $sco)
819  {
820  $sco_id = $sco;
821 
822  $date_ex = explode('.', $data['Date']);
823  $date = implode('-', array($date_ex[2], $date_ex[1], $date_ex[0]));
824 
825  $statement = $ilDB->queryF('
826  SELECT * FROM scorm_tracking
827  WHERE user_id = %s
828  AND sco_id = %s
829  AND lvalue = %s
830  AND obj_id = %s',
831  array('integer','integer','text','integer'),
832  array($user_id, $sco_id, 'cmi.core.lesson_status',$obj_id)
833  );
834  if($ilDB->numRows($statement) > 0)
835  {
836  $ilDB->update('scorm_tracking',
837  array(
838  'rvalue' => array('clob', 'completed'),
839  'c_timestamp' => array('timestamp', $date)
840  ),
841  array(
842  'user_id' => array('integer', $user_id),
843  'sco_id' => array('integer', $sco_id),
844  'lvalue' => array('text', 'cmi.core.lesson_status'),
845  'obj_id' => array('integer', $obj_id)
846  )
847  );
848  }
849  else
850  {
851  $ilDB->insert('scorm_tracking', array(
852  'obj_id' => array('integer', $obj_id),
853  'user_id' => array('integer', $user_id),
854  'sco_id' => array('integer', $sco_id),
855  'lvalue' => array('text', 'cmi.core.lesson_status'),
856  'rvalue' => array('clob', 'completed'),
857  'c_timestamp' => array('timestamp', $date)
858  ));
859  }
860 
861  $statement = $ilDB->queryF('
862  SELECT * FROM scorm_tracking
863  WHERE user_id = %s
864  AND sco_id = %s
865  AND lvalue = %s
866  AND obj_id = %s',
867  array('integer','integer','text','integer'),
868  array($user_id, $sco_id, 'cmi.core.entry',$obj_id)
869  );
870  if($ilDB->numRows($statement) > 0)
871  {
872  $ilDB->update('scorm_tracking',
873  array(
874  'rvalue' => array('clob', 'completed'),
875  'c_timestamp' => array('timestamp', $date)
876  ),
877  array(
878  'user_id' => array('integer', $user_id),
879  'sco_id' => array('integer', $sco_id),
880  'lvalue' => array('text', 'cmi.core.entry'),
881  'obj_id' => array('integer', $obj_id)
882  )
883  );
884  }
885  else
886  {
887  $ilDB->insert('scorm_tracking', array(
888  'obj_id' => array('integer', $obj_id),
889  'user_id' => array('integer', $user_id),
890  'sco_id' => array('integer', $sco_id),
891  'lvalue' => array('text', 'cmi.core.entry'),
892  'rvalue' => array('clob', 'completed'),
893  'c_timestamp' => array('timestamp', $date)
894  ));
895  }
896  }
897  }
898  } else {
899  //echo "Warning! User $csv_rows[0] does not exist in ILIAS. Data for this user was skipped.\n";
900  }
901  }
902 
903  foreach ($users as $user_id)
904  {
905  include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
906  ilLPStatusWrapper::_updateStatus($obj_id, $user_id);
907  }
908  return 0;
909  }
910 
911  private function importRaw($a_file)
912  {
913  global $ilDB, $ilUser;
914 
915  $fhandle = fopen($a_file, "r");
916 
917  $fields = fgetcsv($fhandle, 4096, ';');
918  $users = array();
919  while(($csv_rows = fgetcsv($fhandle, 4096, ";")) !== FALSE)
920  {
921  $data = array_combine($fields, $csv_rows);
922  $il_sco_id = $this->lookupSCOId($data['Scoid']);
923  //look for required data for an import
924  $user_id = $data['Userid'];
925  $users[] = $user_id;
926  if ($user_id == "" || $user_id == null) {
927  //look for Email
928  $user_id = $this->getUserIdEmail($data['Email']);
929  }
930  //do the actual import
931  if ($user_id != "" && $il_sco_id>=0)
932  {
933  $statement = $ilDB->queryF('
934  SELECT * FROM scorm_tracking
935  WHERE user_id = %s
936  AND sco_id = %s
937  AND lvalue = %s
938  AND obj_id = %s',
939  array('integer','integer','text','integer'),
940  array($user_id, $il_sco_id, $data['Key'],$this->getID())
941  );
942  if($ilDB->numRows($statement) > 0)
943  {
944  $ilDB->update('scorm_tracking',
945  array(
946  'rvalue' => array('clob', $data['Value']),
947  'c_timestamp' => array('timestamp', $data['Timestamp'])
948  ),
949  array(
950  'user_id' => array('integer', $user_id),
951  'sco_id' => array('integer', $il_sco_id),
952  'lvalue' => array('text', $data['Key']),
953  'obj_id' => array('integer', $this->getId())
954  )
955  );
956  }
957  else
958  {
959  $ilDB->insert('scorm_tracking', array(
960  'obj_id' => array('integer', $this->getId()),
961  'user_id' => array('integer', $user_id),
962  'sco_id' => array('integer', $il_sco_id),
963  'lvalue' => array('text', $data['Key']),
964  'rvalue' => array('clob', $data['Value']),
965  'c_timestamp' => array('timestamp', $data['Timestamp'])
966  ));
967  }
968  }
969  }
970  fclose($fhandle);
971 
972  // update learning progress
973  foreach ($users as $user_id)
974  {
975  include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
976  ilLPStatusWrapper::_updateStatus($this->getId(), $user_id);
977  }
978 
979  return 0;
980  }
981 
982  //helper function
983  function get_user_id($a_login) {
984  global $ilDB, $ilUser;
985 
986  $val_set = $ilDB->queryF('SELECT * FROM usr_data WHERE(login=%s)',
987  array('text'),array($a_login));
988  $val_rec = $ilDB->fetchAssoc($val_set);
989 
990  if (count($val_rec)>0) {
991  return $val_rec['usr_id'];
992  } else {
993  return null;
994  }
995  }
996 
997 
1001  private function lookupSCOId($a_referrer){
1002  global $ilDB, $ilUser;
1003 
1004  //non specific SCO entries
1005  if ($a_referrer=="0") {
1006  return 0;
1007  }
1008 
1009  $val_set = $ilDB->queryF('
1010  SELECT obj_id FROM sc_item,scorm_tree
1011  WHERE (obj_id = child
1012  AND identifierref = %s
1013  AND slm_id = %s)',
1014  array('text','integer'), array($a_referrer,$this->getID()));
1015  $val_rec = $ilDB->fetchAssoc($val_set);
1016 
1017  return $val_rec["obj_id"];
1018  }
1019 
1023  function getUserIdEmail($a_mail)
1024  {
1025  global $ilDB, $ilUser;
1026 
1027  $val_set = $ilDB->queryF('SELECT usr_id FROM usr_data WHERE(email=%s)',
1028  array('text'),array($a_mail));
1029  $val_rec = $ilDB->fetchAssoc($val_set);
1030 
1031 
1032  return $val_rec["usr_id"];
1033  }
1034 
1035 
1039  function sendExportFile($a_header,$a_content)
1040  {
1041  $timestamp = time();
1042  $refid = $this->getRefId();
1043  $filename = "scorm_tracking_".$refid."_".$timestamp.".csv";
1044  //Header
1045  header("Expires: 0");
1046  header("Cache-control: private");
1047  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
1048  header("Content-Description: File Transfer");
1049  header("Content-Type: application/octet-stream");
1050  header("Content-disposition: attachment; filename=$filename");
1051  echo $a_header.$a_content;
1052  exit;
1053  }
1054 
1060  public static function _getAllScoIds($a_id)
1061  {
1062  global $ilDB;
1063 
1064  $scos = array();
1065 
1066  $val_set = $ilDB->queryF('
1067  SELECT scorm_object.obj_id,
1068  scorm_object.title,
1069  scorm_object.c_type,
1070  scorm_object.slm_id,
1071  scorm_object.obj_id scoid
1072  FROM scorm_object,sc_item,sc_resource
1073  WHERE(scorm_object.slm_id = %s
1074  AND scorm_object.obj_id = sc_item.obj_id
1075  AND sc_item.identifierref = sc_resource.import_id
1076  AND sc_resource.scormtype = %s)
1077  GROUP BY scorm_object.obj_id,
1078  scorm_object.title,
1079  scorm_object.c_type,
1080  scorm_object.slm_id,
1081  scorm_object.obj_id ',
1082  array('integer', 'text'),
1083  array($a_id,'sco'));
1084 
1085  while ($val_rec = $ilDB->fetchAssoc($val_set))
1086  {
1087  array_push($scos,$val_rec['scoid']);
1088  }
1089  return $scos;
1090  }
1091 
1100  public static function _getStatusForUser($a_id, $a_user,$a_allScoIds,$a_numerical=false)
1101  {
1102  global $ilDB, $lng;
1103 
1104  $scos = $a_allScoIds;
1105  //check if all SCO's are completed
1106  $scos_c = implode(',',$scos);
1107 
1108  $val_set = $ilDB->queryF('
1109  SELECT * FROM scorm_tracking
1110  WHERE (user_id = %s
1111  AND obj_id = %s
1112  AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
1113  AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').')
1114  OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').')))',
1115  array('integer','integer','text','text'),
1116  array($a_user,$a_id,'cmi.core.lesson_status', 'cmi.core.lesson_status'));
1117  while ($val_rec = $ilDB->fetchAssoc($val_set))
1118  {
1119  $key = array_search($val_rec['sco_id'], $scos);
1120  unset ($scos[$key]);
1121  }
1122  //check for completion
1123  if (count($scos) == 0) {
1124  $completion = ($a_numerical===true) ? true: $lng->txt("cont_complete");
1125  }
1126  if (count($scos) > 0) {
1127  $completion = ($a_numerical===true) ? false: $lng->txt("cont_incomplete");
1128  }
1129  return $completion;
1130  }
1131 
1138  public static function _getCourseCompletionForUser($a_id, $a_user)
1139  {
1140  return ilObjSCORMLearningModule::_getStatusForUser($a_id, $a_user, ilObjSCORMLearningModule::_getAllScoIds($a_id), true);
1141  }
1142 
1143  function getAllScoIds(){
1144  global $ilDB;
1145 
1146  $scos = array();
1147  //get all SCO's of this object
1148  $val_set = $ilDB->queryF('
1149  SELECT scorm_object.obj_id,
1150  scorm_object.title,
1151  scorm_object.c_type,
1152  scorm_object.slm_id,
1153  scorm_object.obj_id scoid
1154  FROM scorm_object, sc_item,sc_resource
1155  WHERE(scorm_object.slm_id = %s
1156  AND scorm_object.obj_id = sc_item.obj_id
1157  AND sc_item.identifierref = sc_resource.import_id
1158  AND sc_resource.scormtype = %s )
1159  GROUP BY scorm_object.obj_id,
1160  scorm_object.title,
1161  scorm_object.c_type,
1162  scorm_object.slm_id,
1163  scorm_object.obj_id',
1164  array('integer','text'),
1165  array($this->getId(),'sco'));
1166 
1167  while ($val_rec = $ilDB->fetchAssoc($val_set))
1168  {
1169  array_push($scos,$val_rec['scoid']);
1170  }
1171  return $scos;
1172  }
1173 
1174  function getStatusForUser($a_user,$a_allScoIds,$a_numerical=false){
1175  global $ilDB;
1176  $scos = $a_allScoIds;
1177  //loook up status
1178  //check if all SCO's are completed
1179  $scos_c = implode(',',$scos);
1180 
1181  $val_set = $ilDB->queryF('
1182  SELECT sco_id FROM scorm_tracking
1183  WHERE (user_id = %s
1184  AND obj_id = %s
1185  AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
1186  AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').') OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').') ) )',
1187  array('integer','integer','text','text',),
1188  array($a_user,$this->getID(),'cmi.core.lesson_status','cmi.core.lesson_status'));
1189  while ($val_rec = $ilDB->fetchAssoc($val_set))
1190  {
1191  $key = array_search($val_rec['sco_id'], $scos);
1192  unset ($scos[$key]);
1193  }
1194  //check for completion
1195  if (count($scos) == 0) {
1196  $completion = ($a_numerical===true) ? true: $this->lng->txt("cont_complete");
1197  }
1198  if (count($scos) > 0) {
1199  $completion = ($a_numerical===true) ? false: $this->lng->txt("cont_incomplete");
1200  }
1201  return $completion;
1202  }
1203 
1204  function getCourseCompletionForUser($a_user) {
1205  return $this->getStatusForUser($a_user,$this->getAllScoIds,true);
1206  }
1207 
1208  //to be called from IlObjUser
1209  public static function _removeTrackingDataForUser($user_id) {
1210  global $ilDB;
1211  //gobjective
1212  $ilDB->manipulateF(
1213  'DELETE FROM scorm_tracking WHERE user_id = %s',
1214  array('integer'),
1215  array($user_id)
1216  );
1217  }
1218 
1219 
1220 } // END class.ilObjSCORMLearningModule
1221 ?>