ILIAS  release_4-3 Revision
 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 "./Services/Object/classes/class.ilObject.php";
6 require_once "./Modules/ScormAicc/classes/class.ilObjSCORMValidator.php";
7 require_once "./Modules/ScormAicc/classes/class.ilObjSAHSLearningModule.php";
8 
17 class ilObjSCORMLearningModule extends ilObjSAHSLearningModule
18 {
19  var $validator;
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 
47  function getValidationSummary()
48  {
49  if(is_object($this->validator))
50  {
51  return $this->validator->getSummary();
52  }
53  return "";
54  }
55 
56  function getTrackingItems()
57  {
58  return ilObjSCORMLearningModule::_getTrackingItems($this->getId());
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 
229 
233  function getTrackedItems()
234  {
235  global $ilDB, $ilUser;
236 
237  $sco_set = $ilDB->queryF('
238  SELECT DISTINCT sco_id FROM scorm_tracking WHERE obj_id = %s',
239  array('integer'),array($this->getId()));
240 
241  $items = array();
242  while($sco_rec = $ilDB->fetchAssoc($sco_set))
243  {
244  include_once("./Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php");
245  $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
246  if ($sc_item->getIdentifierRef() != "")
247  {
248  $items[count($items)] =& $sc_item;
249  }
250  }
251 
252  return $items;
253  }
254 
262  public static function _lookupLastAccess($a_obj_id, $a_usr_id)
263  {
264  global $ilDB;
265 
266  $result = $ilDB->queryF('
267  SELECT MAX(c_timestamp) last_access FROM scorm_tracking
268  WHERE obj_id = %s
269  AND user_id = %s',
270  array('integer','integer'), array($a_obj_id,$a_usr_id));
271 
272  if ($ilDB->numRows($result))
273  {
274  $row = $ilDB->fetchAssoc($result);
275  return $row["last_access"];
276  }
277  return "";
278  }
279 
280  function getTrackedUsers($a_search)
281  {
282  global $ilDB, $ilUser;
283 
284  $query = 'SELECT user_id,MAX(c_timestamp) last_access, lastname, firstname FROM scorm_tracking st ' .
285  'JOIN usr_data ud ON st.user_id = ud.usr_id ' .
286  'WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer');
287  if($a_search) {
288 // $query .= ' AND (' . $ilDB->like('lastname', 'text', '%' . $a_search . '%') . ' OR ' . $ilDB->like('firstname', 'text', '%' . $a_search . '%') .')';
289  $query .= ' AND ' . $ilDB->like('lastname', 'text', '%' . $a_search . '%');
290  }
291  $query .= ' GROUP BY user_id, lastname, firstname';
292  $sco_set = $ilDB->query($query);
293 
294  $items = array();
295  while($sco_rec = $ilDB->fetchAssoc($sco_set))
296  {
297  $items[] = $sco_rec;
298  }
299  return $items;
300  }
301 
302 
308  public function getAttemptsForUsers()
309  {
310  global $ilDB;
311 
312  $query = 'SELECT user_id,rvalue FROM scorm_tracking ' .
313  'WHERE lvalue = ' . $ilDB->quote('package_attempts', 'text') . ' ' .
314  'AND obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
315  $res = $ilDB->query($query);
316 
317  $attempts = array();
318  while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
319  {
320  $attempts[$row['user_id']] = (int) $row['rvalue'];
321  }
322  return $attempts;
323  }
324 
325 
329  function getAttemptsForUser($a_user_id){
330  global $ilDB;
331 
332  $val_set = $ilDB->queryF('
333  SELECT * FROM scorm_tracking
334  WHERE user_id = %s
335  AND sco_id = %s
336  AND lvalue = %s
337  AND obj_id = %s',
338  array('integer','integer','text','integer'),
339  array($a_user_id,0,'package_attempts',$this->getId()));
340 
341  $val_rec = $ilDB->fetchAssoc($val_set);
342 
343  $val_rec["rvalue"] = str_replace("\r\n", "\n", $val_rec["rvalue"]);
344  if ($val_rec["rvalue"] == null) {
345  $val_rec["rvalue"]="";
346  }
347  return $val_rec["rvalue"];
348  }
349 
350 
355  public function getModuleVersionForUsers()
356  {
357  global $ilDB;
358 
359  $query = 'SELECT user_id,rvalue FROM scorm_tracking ' .
360  'WHERE lvalue = ' . $ilDB->quote('module_version', 'text') . ' ' .
361  'AND obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ';
362  $res = $ilDB->query($query);
363 
364  $versions = array();
365  while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
366  {
367  $versions[$row['user_id']] = (int) $row['rvalue'];
368  }
369  return $versions;
370  }
371 
372 
376  function getModuleVersionForUser($a_user_id){
377  global $ilDB;
378 
379  $val_set = $ilDB->queryF('
380  SELECT * FROM scorm_tracking
381  WHERE user_id = %s
382  AND sco_id = %s
383  AND lvalue = %s
384  AND obj_id = %s',
385  array('integer','integer','text','integer'),
386  array($a_user_id,0,'module_version',$this->getId()));
387 
388  $val_rec = $ilDB->fetchAssoc($val_set);
389 
390 
391  $val_rec["rvalue"] = str_replace("\r\n", "\n", $val_rec["rvalue"]);
392  if ($val_rec["rvalue"] == null) {
393  $val_rec["rvalue"]="";
394  }
395  return $val_rec["rvalue"];
396  }
397 
405  function getTrackingDataPerUser($a_sco_id, $a_user_id)
406  {
407  global $ilDB;
408 
409  $data_set = $ilDB->queryF('
410  SELECT * FROM scorm_tracking
411  WHERE user_id = %s
412  AND sco_id = %s
413  AND obj_id = %s
414  ORDER BY lvalue',
415  array('integer','integer','integer'),
416  array($a_user_id,$a_sco_id,$this->getId()));
417 
418  $data = array();
419  while($data_rec = $ilDB->fetchAssoc($data_set)) {
420  $data[] = $data_rec;
421  }
422 
423  return $data;
424  }
425 
426  function getTrackingDataAgg($a_user_id)
427  {
428  global $ilDB;
429 
430  // get all users with any tracking data
431  $sco_set = $ilDB->queryF('
432  SELECT DISTINCT sco_id FROM scorm_tracking
433  WHERE obj_id = %s
434  AND user_id = %s
435  AND sco_id <> %s',
436  array('integer','integer','integer'),
437  array($this->getId(),$a_user_id,0));
438 
439  $data = array();
440  while($sco_rec = $ilDB->fetchAssoc($sco_set))
441  {
442  $data_set = $ilDB->queryF('
443  SELECT * FROM scorm_tracking
444  WHERE obj_id = %s
445  AND sco_id = %s
446  AND user_id = %s
447  AND lvalue <> %s
448  AND (lvalue = %s
449  OR lvalue = %s
450  OR lvalue = %s)',
451  array('integer','integer','integer','text','text','text','text'),
452  array($this->getId(),
453  $sco_rec["sco_id"],
454  $a_user_id,
455  "package_attempts",
456  "cmi.core.lesson_status",
457  "cmi.core.total_time",
458  "cmi.core.score.raw")
459  );
460 
461  $score = $time = $status = "";
462 
463  while($data_rec = $ilDB->fetchAssoc($data_set))
464  {
465  switch($data_rec["lvalue"])
466  {
467  case "cmi.core.lesson_status":
468  $status = $data_rec["rvalue"];
469  break;
470 
471  case "cmi.core.total_time":
472  $time = $data_rec["rvalue"];
473  break;
474 
475  case "cmi.core.score.raw":
476  $score = $data_rec["rvalue"];
477  break;
478  }
479  }
480  //create sco_object
481  include_once './Modules/ScormAicc/classes/SCORM/class.ilSCORMItem.php';
482  $sc_item =& new ilSCORMItem($sco_rec["sco_id"]);
483  $data[] = array("sco_id"=>$sco_rec["sco_id"], "title" => $sc_item->getTitle(),
484  "score" => $score, "time" => $time, "status" => $status);
485 
486  }
487  return (array) $data;
488  }
489 
490  function getTrackingDataAggSco($a_sco_id)
491  {
492  global $ilDB;
493 
494  // get all users with any tracking data
495  $user_set = $ilDB->queryF('
496  SELECT DISTINCT user_id FROM scorm_tracking
497  WHERE obj_id = %s
498  AND sco_id = %s',
499  array('integer','integer'),
500  array($this->getId(),$a_sco_id));
501 
502  $data = array();
503  while($user_rec = $ilDB->fetchAssoc($user_set))
504  {
505 
506  $data_set = $ilDB->queryF('
507  SELECT * FROM scorm_tracking
508  WHERE obj_id = %s
509  AND sco_id = %s
510  AND user_id = %s
511  AND (lvalue = %s
512  OR lvalue = %s
513  OR lvalue = %s)',
514  array('integer','integer','integer','text','text','text'),
515  array($this->getId(),
516  $a_sco_id,
517  $user_rec["user_id"],
518  "cmi.core.lesson_status",
519  "cmi.core.total_time",
520  "cmi.core.score.raw")
521  );
522 
523  $score = $time = $status = "";
524 
525  while($data_rec = $ilDB->fetchAssoc($data_set))
526  {
527  switch($data_rec["lvalue"])
528  {
529  case "cmi.core.lesson_status":
530  $status = $data_rec["rvalue"];
531  break;
532 
533  case "cmi.core.total_time":
534  $time = $data_rec["rvalue"];
535  break;
536 
537  case "cmi.core.score.raw":
538  $score = $data_rec["rvalue"];
539  break;
540  }
541  }
542 
543  $data[] = array("user_id" => $user_rec["user_id"],
544  "score" => $score, "time" => $time, "status" => $status);
545  }
546 
547  return $data;
548  }
549 
550 
558  public function exportSelectedRaw($a_exportall, $a_user = array())
559  {
560  global $ilDB, $ilUser, $ilSetting;
561 
562  $inst_id = $ilSetting->get('inst_id',0);
563 
564  include_once './Services/Utilities/classes/class.ilCSVWriter.php';
565  $csv = new ilCSVWriter();
566  $csv->setSeparator(';');
567  $csv->addColumn('Scoid');
568  $csv->addColumn('Key');
569  $csv->addColumn('Value');
570  $csv->addColumn('Email');
571  $csv->addColumn('Timestamp');
572  $csv->addColumn('Userid');
573 
574  // Collect users
575  $user_array = array();
576  if($a_exportall)
577  {
578  $res = $ilDB->queryF(
579  'SELECT user_id FROM scorm_tracking WHERE obj_id = %s GROUP BY user_id',
580  array('integer'),
581  array($this->getId())
582  );
583  while($row = $ilDB->fetchAssoc($res))
584  {
585  $user_array[] = $row['user_id'];
586  }
587  }
588  else
589  {
590  $user_array = $a_user;
591  }
592 
593  // Read user data
594  $query = "SELECT usr_id,email FROM usr_data ".
595  "WHERE ".$ilDB->in('usr_id', $user_array, FALSE, 'integer');
596  $res = $ilDB->query($query);
597  $emails = array();
598  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
599  {
600  $emails[$row->usr_id] = $row->email;
601  }
602 
603  foreach($user_array as $user_id)
604  {
605  // Sco related information
606  $query = 'SELECT rvalue, lvalue, identifierref, c_timestamp FROM scorm_tracking st '.
607  'JOIN sc_item si ON st.sco_id = si.obj_id '.
608  'WHERE user_id = '.$ilDB->quote($user_id,'integer'). ' '.
609  'AND st.obj_id = '.$ilDB->quote($this->getId(),'integer');
610  $res = $ilDB->query($query);
611  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
612  {
613  $csv->addRow();
614  $csv->addColumn($row->identifierref);
615  $csv->addColumn($row->lvalue);
616  $csv->addColumn($row->rvalue);
617  $csv->addColumn(isset($emails[$user_id]) ? (string) $emails[$user_id] : '');
618  $csv->addColumn($row->c_timestamp);
619  $csv->addColumn('il_usr_'.$inst_id.'_'.$user_id);
620  }
621  // Sco unrelated information
622  $query = 'SELECT rvalue, lvalue, c_timestamp FROM scorm_tracking '.
623  'WHERE sco_id = 0 AND user_id = '.$ilDB->quote($user_id,'integer').' '.
624  'AND obj_id = '.$ilDB->quote($this->getId(),'integer');
625  $res = $ilDB->query($query);
626  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
627  {
628  $csv->addRow();
629  $csv->addColumn(0);
630  $csv->addColumn($row->lvalue);
631  $csv->addColumn($row->rvalue);
632  $csv->addColumn(isset($emails[$user_id]) ? (string) $emails[$user_id] : '');
633  $csv->addColumn($row->c_timestamp);
634  $csv->addColumn('il_usr_'.$inst_id.'_'.$user_id);
635  }
636  }
637 
638  ilUtil::deliverData(
639  $csv->getCSVString(),
640  'scorm_tracking_raw_' . $this->getRefId() . '_' . time() . '.csv'
641  );
642  return;
643  }
644 
645 
646 
654  public function exportSelected($a_all, $a_users = array())
655  {
656  global $ilDB, $ilUser, $ilSetting;
657 
658  $inst_id = $ilSetting->get('inst_id',0);
659 
660  // Get all scos
661  $scos = array();
662 
663  //get all SCO's of this object
664  $query = 'SELECT scorm_object.obj_id, scorm_object.title, '
665  . 'scorm_object.c_type, scorm_object.slm_id, scorm_object.obj_id scoid '
666  . 'FROM scorm_object, sc_item, sc_resource '
667  . 'WHERE (scorm_object.slm_id = %s '
668  . 'AND scorm_object.obj_id = sc_item.obj_id '
669  . 'AND sc_item.identifierref = sc_resource.import_id '
670  . 'AND sc_resource.scormtype = %s) '
671  . 'GROUP BY scorm_object.obj_id, scorm_object.title, scorm_object.c_type, '
672  . 'scorm_object.slm_id, scorm_object.obj_id ';
673  $res = $ilDB->queryF(
674  $query,
675  array('integer', 'text'),
676  array($this->getId(), 'sco')
677  );
678  while($row = $ilDB->fetchAssoc($res))
679  {
680  $scos[] = $row['scoid'];
681  }
682 
683 
684  $users = array();
685  if($a_all)
686  {
687  $query = 'SELECT user_id FROM scorm_tracking ' .
688  'WHERE obj_id = ' . $ilDB->quote($this->getId(), 'integer') . ' ' .
689  'GROUP BY user_id';
690  $res = $ilDB->query($query);
691  while($row = $ilDB->fetchAssoc($res))
692  {
693  $users[] = $row['user_id'];
694  }
695  }
696  else
697  {
698  $users = $a_users;
699  }
700 
701  // get all completed
702  include_once './Modules/ScormAicc/classes/SCORM/class.ilObjSCORMTracking.php';
703  $completed = ilObjSCORMTracking::_getCompleted($scos, $this->getId());
704  $last = ilObjSCORMTracking::lookupLastAccessTimes($this->getId());
705 
706  include_once './Services/Utilities/classes/class.ilCSVWriter.php';
707  $csv = new ilCSVWriter();
708  $csv->setSeparator(';');
709  foreach(array('Department', 'Login', 'Lastname', 'Firstname', 'Email', 'Date', 'Status') as $col)
710  {
711  $csv->addColumn($col);
712  }
713 
714  // Read user data
715  $query = 'SELECT usr_id,login,firstname,lastname,department,email ' .
716  'FROM usr_data ' .
717  'WHERE ' . $ilDB->in('usr_id', $users, false, 'integer');
718  $res = $ilDB->query($query);
719 
720  while($row = $res->fetchRow(DB_FETCHMODE_OBJECT))
721  {
722  $csv->addRow();
723  $csv->addColumn((string) $row->department);
724  $csv->addColumn((string) $row->login);
725  $csv->addColumn((string) $row->lastname);
726  $csv->addColumn((string) $row->firstname);
727  $csv->addColumn((string) $row->email);
728  if(isset($last[$row->usr_id]))
729  {
730  $dt = new ilDateTime($last[$row->usr_id], IL_CAL_DATETIME);
731  $csv->addColumn((string) $dt->get(IL_CAL_FKT_DATE, 'd.m.Y'));
732  }
733  else
734  {
735  $csv->addColumn('');
736  }
737  $csv->addColumn(in_array($row->usr_id, $completed) ? 1 : 0);
738  }
739 
740 
741  ilUtil::deliverData(
742  $csv->getCSVString(),
743  'scorm_tracking_' . $this->getRefId() . '_' . time() . '.csv'
744  );
745  }
746 
747 
748  function importTrackingData($a_file)
749  {
750  global $ilDB, $ilUser;
751 
752  $error = 0;
753  //echo file_get_contents($a_file);
754  $method = null;
755 
756  //lets import
757  $fhandle = fopen($a_file, "r");
758 
759  //the top line is the field names
760  $fields = fgetcsv($fhandle, pow(2, 16), ';');
761  //lets check the import method
762  fclose($fhandle);
763 
764  switch($fields[0])
765  {
766  case "Scoid":
767  $error = $this->importRaw($a_file);
768  break;
769  case "Department":
770  $error = $this->importSuccess($a_file);
771  break;
772  default:
773  return -1;
774  break;
775  }
776  return $error;
777  }
778 
779  function importSuccess($a_file) {
780 
781  global $ilDB, $ilUser;
782 
783  $scos = array();
784  //get all SCO's of this object
785 
786  $val_set = $ilDB->queryF('
787  SELECT scorm_object.obj_id,
788  scorm_object.title,
789  scorm_object.c_type,
790  scorm_object.slm_id,
791  scorm_object.obj_id scoid
792  FROM scorm_object,sc_item,sc_resource
793  WHERE(scorm_object.slm_id = %s
794  AND scorm_object.obj_id=sc_item.obj_id
795  AND sc_item.identifierref = sc_resource.import_id
796  AND sc_resource.scormtype = %s)
797  GROUP BY scorm_object.obj_id,
798  scorm_object.title,
799  scorm_object.c_type,
800  scorm_object.slm_id,
801  scorm_object.obj_id ',
802  array('integer','text'),
803  array($this->getId(),'sco')
804  );
805 
806  if (count($val_set)<1)
807  {
808  return -1;
809  }
810  while($rows_sco = $ilDB->fetchAssoc($val_set))
811  {
812  array_push($scos,$rows_sco['scoid']);
813  }
814 
815  $fhandle = fopen($a_file, "r");
816 
817  $obj_id = $this->getID();
818 
819  $fields = fgetcsv($fhandle, pow(2, 16), ';');
820  $users = array();
821  while(($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== FALSE)
822  {
823  $data = array_combine($fields, $csv_rows);
824  //check the format
825  $statuscheck = 0;
826  if (count($csv_rows) == 6) {$statuscheck = 1;}
827 
828  if ($this->get_user_id($data["Login"])>0) {
829 
830  $user_id = $this->get_user_id($data["Login"]);
831  $users[] = $user_id;
832  $import = $data["Status"];
833  if ($import == "") {$import = 1;}
834  //iterate over all SCO's
835  if ($import == 1) {
836  foreach ($scos as $sco)
837  {
838  $sco_id = $sco;
839 
840  $date_ex = explode('.', $data['Date']);
841  $date = implode('-', array($date_ex[2], $date_ex[1], $date_ex[0]));
842 
843  $statement = $ilDB->queryF('
844  SELECT * FROM scorm_tracking
845  WHERE user_id = %s
846  AND sco_id = %s
847  AND lvalue = %s
848  AND obj_id = %s',
849  array('integer','integer','text','integer'),
850  array($user_id, $sco_id, 'cmi.core.lesson_status',$obj_id)
851  );
852  if($ilDB->numRows($statement) > 0)
853  {
854  $ilDB->update('scorm_tracking',
855  array(
856  'rvalue' => array('clob', 'completed'),
857  'c_timestamp' => array('timestamp', $date)
858  ),
859  array(
860  'user_id' => array('integer', $user_id),
861  'sco_id' => array('integer', $sco_id),
862  'lvalue' => array('text', 'cmi.core.lesson_status'),
863  'obj_id' => array('integer', $obj_id)
864  )
865  );
866  }
867  else
868  {
869  $ilDB->insert('scorm_tracking', array(
870  'obj_id' => array('integer', $obj_id),
871  'user_id' => array('integer', $user_id),
872  'sco_id' => array('integer', $sco_id),
873  'lvalue' => array('text', 'cmi.core.lesson_status'),
874  'rvalue' => array('clob', 'completed'),
875  'c_timestamp' => array('timestamp', $date)
876  ));
877  }
878 
879  $statement = $ilDB->queryF('
880  SELECT * FROM scorm_tracking
881  WHERE user_id = %s
882  AND sco_id = %s
883  AND lvalue = %s
884  AND obj_id = %s',
885  array('integer','integer','text','integer'),
886  array($user_id, $sco_id, 'cmi.core.entry',$obj_id)
887  );
888  if($ilDB->numRows($statement) > 0)
889  {
890  $ilDB->update('scorm_tracking',
891  array(
892  'rvalue' => array('clob', 'completed'),
893  'c_timestamp' => array('timestamp', $date)
894  ),
895  array(
896  'user_id' => array('integer', $user_id),
897  'sco_id' => array('integer', $sco_id),
898  'lvalue' => array('text', 'cmi.core.entry'),
899  'obj_id' => array('integer', $obj_id)
900  )
901  );
902  }
903  else
904  {
905  $ilDB->insert('scorm_tracking', array(
906  'obj_id' => array('integer', $obj_id),
907  'user_id' => array('integer', $user_id),
908  'sco_id' => array('integer', $sco_id),
909  'lvalue' => array('text', 'cmi.core.entry'),
910  'rvalue' => array('clob', 'completed'),
911  'c_timestamp' => array('timestamp', $date)
912  ));
913  }
914  }
915  }
916  } else {
917  //echo "Warning! User $csv_rows[0] does not exist in ILIAS. Data for this user was skipped.\n";
918  }
919  }
920 
921  include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
922  ilLPStatusWrapper::_refreshStatus($this->getId());
923 // <4.2.6: foreach ($users as $user_id) {ilLPStatusWrapper::_updateStatus($obj_id, $user_id);}
924  return 0;
925  }
926 
932  private function parseUserId($il_id)
933  {
934  global $ilSetting;
935 
936  $parts = explode('_', $il_id);
937 
938  if(!count((array) $parts))
939  {
940  return 0;
941  }
942  if(!isset($parts[2]) or !isset($parts[3]))
943  {
944  return 0;
945  }
946  if($parts[2] != $ilSetting->get('inst_id',$parts[2]))
947  {
948  return 0;
949  }
950  return $parts[3];
951  }
952 
960  private function importRaw($a_file)
961  {
962  global $ilDB, $ilUser;
963 
964  $fhandle = fopen($a_file, "r");
965 
966  $fields = fgetcsv($fhandle, pow(2, 16), ';');
967  $users = array();
968  while(($csv_rows = fgetcsv($fhandle, pow(2, 16), ";")) !== FALSE)
969  {
970  $data = array_combine($fields, $csv_rows);
971  $user_id = $this->parseUserId($data['Userid']);
972 
973  if(!$user_id)
974  {
975  continue;
976  }
977 
978  $il_sco_id = $this->lookupSCOId($data['Scoid']);
979 
980  //do the actual import
981  if($il_sco_id >= 0)
982  {
983  $statement = $ilDB->queryF('
984  SELECT * FROM scorm_tracking
985  WHERE user_id = %s
986  AND sco_id = %s
987  AND lvalue = %s
988  AND obj_id = %s',
989  array('integer', 'integer', 'text', 'integer'),
990  array($user_id, $il_sco_id, $data['Key'], $this->getID())
991  );
992  if($ilDB->numRows($statement) > 0)
993  {
994  $ilDB->update('scorm_tracking',
995  array(
996  'rvalue' => array('clob', $data['Value']),
997  'c_timestamp' => array('timestamp', $data['Timestamp'])
998  ),
999  array(
1000  'user_id' => array('integer', $user_id),
1001  'sco_id' => array('integer', $il_sco_id),
1002  'lvalue' => array('text', $data['Key']),
1003  'obj_id' => array('integer', $this->getId())
1004  )
1005  );
1006  }
1007  else
1008  {
1009  $ilDB->insert('scorm_tracking', array(
1010  'obj_id' => array('integer', $this->getId()),
1011  'user_id' => array('integer', $user_id),
1012  'sco_id' => array('integer', $il_sco_id),
1013  'lvalue' => array('text', $data['Key']),
1014  'rvalue' => array('clob', $data['Value']),
1015  'c_timestamp' => array('timestamp', $data['Timestamp'])
1016  ));
1017  }
1018  }
1019  }
1020  fclose($fhandle);
1021 
1022  include_once './Services/Tracking/classes/class.ilLPStatusWrapper.php';
1023  ilLPStatusWrapper::_refreshStatus($this->getId());
1024 
1025  return 0;
1026  }
1027 
1028  //helper function
1029  function get_user_id($a_login) {
1030  global $ilDB, $ilUser;
1031 
1032  $val_set = $ilDB->queryF('SELECT * FROM usr_data WHERE(login=%s)',
1033  array('text'),array($a_login));
1034  $val_rec = $ilDB->fetchAssoc($val_set);
1035 
1036  if (count($val_rec)>0) {
1037  return $val_rec['usr_id'];
1038  } else {
1039  return null;
1040  }
1041  }
1042 
1043 
1047  private function lookupSCOId($a_referrer){
1048  global $ilDB, $ilUser;
1049 
1050  //non specific SCO entries
1051  if ($a_referrer=="0") {
1052  return 0;
1053  }
1054 
1055  $val_set = $ilDB->queryF('
1056  SELECT obj_id FROM sc_item,scorm_tree
1057  WHERE (obj_id = child
1058  AND identifierref = %s
1059  AND slm_id = %s)',
1060  array('text','integer'), array($a_referrer,$this->getID()));
1061  $val_rec = $ilDB->fetchAssoc($val_set);
1062 
1063  return $val_rec["obj_id"];
1064  }
1065 
1069  function getUserIdEmail($a_mail)
1070  {
1071  global $ilDB, $ilUser;
1072 
1073  $val_set = $ilDB->queryF('SELECT usr_id FROM usr_data WHERE(email=%s)',
1074  array('text'),array($a_mail));
1075  $val_rec = $ilDB->fetchAssoc($val_set);
1076 
1077 
1078  return $val_rec["usr_id"];
1079  }
1080 
1081 
1085  function sendExportFile($a_header,$a_content)
1086  {
1087  $timestamp = time();
1088  $refid = $this->getRefId();
1089  $filename = "scorm_tracking_".$refid."_".$timestamp.".csv";
1090  //Header
1091  header("Expires: 0");
1092  header("Cache-control: private");
1093  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
1094  header("Content-Description: File Transfer");
1095  header("Content-Type: application/octet-stream");
1096  header("Content-disposition: attachment; filename=$filename");
1097  echo $a_header.$a_content;
1098  exit;
1099  }
1100 
1106  public static function _getAllScoIds($a_id)
1107  {
1108  global $ilDB;
1109 
1110  $scos = array();
1111 
1112  $val_set = $ilDB->queryF('
1113  SELECT scorm_object.obj_id,
1114  scorm_object.title,
1115  scorm_object.c_type,
1116  scorm_object.slm_id,
1117  scorm_object.obj_id scoid
1118  FROM scorm_object,sc_item,sc_resource
1119  WHERE(scorm_object.slm_id = %s
1120  AND scorm_object.obj_id = sc_item.obj_id
1121  AND sc_item.identifierref = sc_resource.import_id
1122  AND sc_resource.scormtype = %s)
1123  GROUP BY scorm_object.obj_id,
1124  scorm_object.title,
1125  scorm_object.c_type,
1126  scorm_object.slm_id,
1127  scorm_object.obj_id ',
1128  array('integer', 'text'),
1129  array($a_id,'sco'));
1130 
1131  while ($val_rec = $ilDB->fetchAssoc($val_set))
1132  {
1133  array_push($scos,$val_rec['scoid']);
1134  }
1135  return $scos;
1136  }
1137 
1146  public static function _getStatusForUser($a_id, $a_user,$a_allScoIds,$a_numerical=false)
1147  {
1148  global $ilDB, $lng;
1149 
1150  $scos = $a_allScoIds;
1151  //check if all SCO's are completed
1152  $scos_c = implode(',',$scos);
1153 
1154  $val_set = $ilDB->queryF('
1155  SELECT * FROM scorm_tracking
1156  WHERE (user_id = %s
1157  AND obj_id = %s
1158  AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
1159  AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').')
1160  OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').')))',
1161  array('integer','integer','text','text'),
1162  array($a_user,$a_id,'cmi.core.lesson_status', 'cmi.core.lesson_status'));
1163  while ($val_rec = $ilDB->fetchAssoc($val_set))
1164  {
1165  $key = array_search($val_rec['sco_id'], $scos);
1166  unset ($scos[$key]);
1167  }
1168  //check for completion
1169  if (count($scos) == 0) {
1170  $completion = ($a_numerical===true) ? true: $lng->txt("cont_complete");
1171  }
1172  if (count($scos) > 0) {
1173  $completion = ($a_numerical===true) ? false: $lng->txt("cont_incomplete");
1174  }
1175  return $completion;
1176  }
1177 
1184  public static function _getCourseCompletionForUser($a_id, $a_user)
1185  {
1186  return ilObjSCORMLearningModule::_getStatusForUser($a_id, $a_user, ilObjSCORMLearningModule::_getAllScoIds($a_id), true);
1187  }
1188 
1189  function getAllScoIds(){
1190  global $ilDB;
1191 
1192  $scos = array();
1193  //get all SCO's of this object
1194  $val_set = $ilDB->queryF('
1195  SELECT scorm_object.obj_id,
1196  scorm_object.title,
1197  scorm_object.c_type,
1198  scorm_object.slm_id,
1199  scorm_object.obj_id scoid
1200  FROM scorm_object, sc_item,sc_resource
1201  WHERE(scorm_object.slm_id = %s
1202  AND scorm_object.obj_id = sc_item.obj_id
1203  AND sc_item.identifierref = sc_resource.import_id
1204  AND sc_resource.scormtype = %s )
1205  GROUP BY scorm_object.obj_id,
1206  scorm_object.title,
1207  scorm_object.c_type,
1208  scorm_object.slm_id,
1209  scorm_object.obj_id',
1210  array('integer','text'),
1211  array($this->getId(),'sco'));
1212 
1213  while ($val_rec = $ilDB->fetchAssoc($val_set))
1214  {
1215  array_push($scos,$val_rec['scoid']);
1216  }
1217  return $scos;
1218  }
1219 
1220  function getStatusForUser($a_user,$a_allScoIds,$a_numerical=false){
1221  global $ilDB;
1222  $scos = $a_allScoIds;
1223  //loook up status
1224  //check if all SCO's are completed
1225  $scos_c = implode(',',$scos);
1226 
1227  $val_set = $ilDB->queryF('
1228  SELECT sco_id FROM scorm_tracking
1229  WHERE (user_id = %s
1230  AND obj_id = %s
1231  AND '.$ilDB->in('sco_id', $scos, false, 'integer').'
1232  AND ((lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'completed').') OR (lvalue = %s AND '.$ilDB->like('rvalue', 'clob', 'passed').') ) )',
1233  array('integer','integer','text','text',),
1234  array($a_user,$this->getID(),'cmi.core.lesson_status','cmi.core.lesson_status'));
1235  while ($val_rec = $ilDB->fetchAssoc($val_set))
1236  {
1237  $key = array_search($val_rec['sco_id'], $scos);
1238  unset ($scos[$key]);
1239  }
1240  //check for completion
1241  if (count($scos) == 0) {
1242  $completion = ($a_numerical===true) ? true: $this->lng->txt("cont_complete");
1243  }
1244  if (count($scos) > 0) {
1245  $completion = ($a_numerical===true) ? false: $this->lng->txt("cont_incomplete");
1246  }
1247  return $completion;
1248  }
1249 
1250  function getCourseCompletionForUser($a_user) {
1251  return $this->getStatusForUser($a_user,$this->getAllScoIds,true);
1252  }
1253 
1254  //to be called from IlObjUser
1255  public static function _removeTrackingDataForUser($user_id) {
1256  global $ilDB;
1257  //gobjective
1258  $ilDB->manipulateF(
1259  'DELETE FROM scorm_tracking WHERE user_id = %s',
1260  array('integer'),
1261  array($user_id)
1262  );
1263  }
1264 
1265  function _getScoresForUser($a_item_id, $a_user_id)
1266  {
1267  global $ilDB;
1268 
1269  $retAr = array("raw" => null, "max" => null, "scaled" => null);
1270  $val_set = $ilDB->queryF("
1271  SELECT lvalue, rvalue FROM scorm_tracking
1272  WHERE sco_id = %s
1273  AND user_id = %s
1274  AND (lvalue = 'cmi.core.score.raw' OR lvalue = 'cmi.core.score.max')",
1275  array('integer', 'integer'),
1276  array($a_item_id, $a_user_id)
1277  );
1278  while ($val_rec = $ilDB->fetchAssoc($val_set))
1279  {
1280  if ($val_rec['lvalue'] == "cmi.core.score.raw") $retAr["raw"] = $val_rec["rvalue"];
1281  if ($val_rec['lvalue'] == "cmi.core.score.max") $retAr["max"] = $val_rec["rvalue"];
1282  }
1283  if ($retAr["raw"] != null && $retAr["max"] != null) $retAr["scaled"] = ($retAr["raw"] / $retAr["max"]);
1284 
1285  return $retAr;
1286  }
1287 
1288 
1289  public function getLastVisited($user_id)
1290  {
1291  global $ilDB;
1292  $val_set = $ilDB->queryF('
1293  SELECT rvalue FROM scorm_tracking
1294  WHERE user_id = %s
1295  AND sco_id = %s
1296  AND lvalue = %s
1297  AND obj_id = %s',
1298  array('integer','integer','text','integer'),
1299  array($user_id,0,'last_visited',$this->getID())
1300  );
1301  while ($val_rec = $ilDB->fetchAssoc($val_set))
1302  {
1303  return "".$val_rec["rvalue"];
1304  }
1305  return '0';
1306  }
1307 
1308 }
1309 ?>