ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
class.ilSCORMTrackingItems.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 include_once './Modules/ScormAicc/classes/class.ilObjSCORMLearningModule.php';
5 
13 class ilSCORMTrackingItems
14 {
15 
16  function scoTitlesForExportSelected($obj_id) {
17  global $ilDB;
18  $scoTitles = array();
19 
20  $query = 'SELECT obj_id, title
21  FROM scorm_object
22  WHERE slm_id = %s AND c_type = %s';
23  $res = $ilDB->queryF(
24  $query,
25  array('integer', 'text'),
26  array($obj_id,'sit')
27  );
28  while($row = $ilDB->fetchAssoc($res))
29  {
30  $scoTitles[$row['obj_id']] = $row['title'];
31  }
32  return $scoTitles;
33  }
34 
35 
36  function markedLearningStatusForExportSelected($a_scos, $obj_id) {
37  global $lng;
38  include_once 'Services/Object/classes/class.ilObjectLP.php';
39  $olp = ilObjectLP::getInstance($obj_id);
40  $collection = $olp->getCollectionInstance();
41 
42  foreach($a_scos as $sco_id=>$value) {
43  if ($collection && $collection->isAssignedEntry($sco_id)) $a_scos[$sco_id] = $lng->txt('yes');
44  else $a_scos[$sco_id]=$lng->txt('no');
45  }
46  return $a_scos;
47  }
48 
49  static function userDataArrayForExport($user, $b_allowExportPrivacy=false) {
50  $userArray = array();
51  if ($b_allowExportPrivacy == false) {
52  $userArray["user"]=$user;
53  } else {
54  global $ilUser;
55  $userArray["login"] = "";
56  $userArray["user"] = "";
57  $userArray["email"] = "";
58  $userArray["department"] = "";
59  if(ilObject::_exists($user) && ilObject::_lookUpType($user) == 'usr') {
60  $e_user = new ilObjUser($user);
61  $userArray["login"] = $e_user->getLogin();
62  $userArray["user"] = $e_user->getLastname() . ', ' . $e_user->getFirstname();
63  $userArray["email"] = "".$e_user->getEmail();
64  $userArray["department"] = "".$e_user->getDepartment();
65  }
66  }
67  return $userArray;
68  }
69 
70 
71  function getScormTrackingValue($obj_id, $a_user = array(), $a_sco = array(), $a_empty = array(), $lvalue) {
72  global $ilDB;
73 
74  $query = 'SELECT user_id, sco_id, rvalue '
75  . 'FROM scorm_tracking '
76  . 'WHERE obj_id = %s '
77  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
78  . 'AND '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
79  . 'AND lvalue=%s';
80  $res = $ilDB->queryF(
81  $query,
82  array('integer','text'),
83  array($obj_id,$lvalue)
84  );
85  while ($data = $ilDB->fetchAssoc($res)) {
86  if(!is_null($data['rvalue'])) $a_empty[$data['user_id']][$data['sco_id']] = $data['rvalue'];
87  }
88  return $a_empty;
89  }
90 
91  function getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user = array(), $a_sco = array(), $lvalue, $counter, $topic) {
92  global $ilDB;
93  $a_return = array();
94  $query = 'SELECT user_id, sco_id, rvalue '
95  . 'FROM scorm_tracking '
96  . 'WHERE obj_id = %s '
97  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
98  . 'AND '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
99  . 'AND lvalue = %s';
100  $res = $ilDB->queryF(
101  $query,
102  array('integer','text'),
103  array($obj_id,'cmi.'.$topic.'.'.$counter.'.'.$lvalue)
104  );
105  while ($data = $ilDB->fetchAssoc($res)) {
106  if(!is_null($data['rvalue'])) $a_return[''.$data['user_id'].'-'.$data['sco_id'].'-'.$counter] = $data['rvalue'];
107  }
108  return $a_return;
109  }
110 
111  static function exportSelectedRawColumns() {
112  global $lng;
113  $lng->loadLanguageModule("scormtrac");
114  // default fields
115  $cols = array();
116  $udh=self::userDataHeaderForExport();
117  $a_cols=explode(',',
118  'lm_id,lm_title,identifierref,sco_id,sco_marked_for_learning_progress,sco_title,'.$udh["cols"]
119  .',c_timestamp,lvalue,rvalue');
120  $a_true=explode(',',$udh["default"].",identifierref,c_timestamp,lvalue,rvalue");
121  for ($i=0;$i<count($a_cols);$i++) {
122  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
123  }
124  for ($i=0;$i<count($a_true);$i++) {
125  $cols[$a_true[$i]]["default"] = true;
126  }
127  return $cols;
128  }
129 
130  function exportSelectedRaw($a_user = array(), $a_sco = array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id) {
131  global $ilDB, $lng;
132  $lng->loadLanguageModule("scormtrac");
133 
134  $returnData = array();
135 
136  $scoTitles = self::scoTitlesForExportSelected($obj_id);
137 
138  $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
139 
140  $query = 'SELECT user_id, st.obj_id, sco_id, identifierref, c_timestamp, lvalue, rvalue '
141  . 'FROM scorm_tracking st '
142  . 'JOIN sc_item si ON st.sco_id = si.obj_id '
143  . 'WHERE '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
144  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
145 // . 'AND st.obj_id = '.$ilDB->quote($this->getId(),'integer') .' '
146  . 'ORDER BY ';
147  if ($b_orderBySCO) $query.='sco_id, user_id';
148  else $query.='user_id, sco_id';
149  $res = $ilDB->query($query);
150  while($data = $ilDB->fetchAssoc($res))
151  {
152  $data["lm_id"] = $obj_id;
153  $data["lm_title"] = $this->lmTitle;
154  $data=array_merge($data,self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
155  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
156  $data["sco_title"] = $scoTitles[$data["sco_id"]];
157  $data["rvalue"] = "".$data["rvalue"];
158  // $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
159  $returnData[]=$data;
160  }
161 
162  return $returnData;
163  }
164 
165  static function exportSelectedCoreColumns($b_orderBySCO, $b_allowExportPrivacy) {
166  global $lng;
167  $lng->loadLanguageModule("scormtrac");
168  // default fields
169  $cols = array();
170  $udh=self::userDataHeaderForExport();
171  $a_cols=explode(',',
172  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,'.$udh["cols"]
173  .',lesson_status,credit,c_entry,c_exit,c_max,c_min,c_raw,session_time,total_time,c_timestamp,suspend_data,launch_data');
174  $a_true=explode(',',$udh["default"].",sco_title,lesson_status");
175  for ($i=0;$i<count($a_cols);$i++) {
176  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
177  }
178  for ($i=0;$i<count($a_true);$i++) {
179  $cols[$a_true[$i]]["default"] = true;
180  }
181  return $cols;
182  }
183 
184  function exportSelectedCore($a_user = array(), $a_sco = array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id) {
185  global $ilDB, $lng;
186  $lng->loadLanguageModule("scormtrac");
187 
188  $returnData = array();
189 
190  $scoTitles = self::scoTitlesForExportSelected($obj_id);
191 
192  $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
193 
194  //data-arrays to fill for all users
195  $a_empty = array();
196  for($i=0; $i<count($a_user); $i++) {
197  $a_empty[$a_user[$i]] = array();
198  }
199 
200  $dbdata = array();
201  $query = 'SELECT user_id, sco_id, max(c_timestamp) as c_timestamp '
202  . 'FROM scorm_tracking '
203  . 'WHERE '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
204  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
205  . 'GROUP BY user_id, sco_id '
206  . 'ORDER BY ';
207  if ($b_orderBySCO) $query.='sco_id, user_id';
208  else $query.='user_id, sco_id';
209  $res = $ilDB->query($query);
210  while($row = $ilDB->fetchAssoc($res))
211  {
212  $dbdata[] = $row;
213  $a_empty[$row["user_id"]][$row["sco_id"]]="";
214  }
215 
216  $a_lesson_status = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.lesson_status');
217  $a_credit = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.credit');
218  $a_c_entry = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.entry');
219  $a_c_exit = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.exit');
220  $a_c_max = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.max');
221  $a_c_min = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.min');
222  $a_c_raw = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.raw');
223  $a_session_time = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.session_time');
224  $a_total_time = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.total_time');
225  $a_suspend_data = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.suspend_data');
226  $a_launch_data = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.launch_data');
227 
228  foreach($dbdata as $data) {
229  $data["lm_id"] = $obj_id;
230  $data["lm_title"] = $this->lmTitle;
231 
232  $data=array_merge($data,self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
233 
234  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
235  $data["sco_title"] = $scoTitles[$data["sco_id"]];
236 
237  // $data["audio_captioning"] = "".$data["audio_captioning"];
238  // $data["audio_level"] = "".$data["audio_level"];
239  $data["lesson_status"] = $a_lesson_status[$data['user_id']][$data['sco_id']];
240  $data["credit"] = $a_credit[$data['user_id']][$data['sco_id']];
241  // $data["delivery_speed"] = "".$data["delivery_speed"];
242  $data["c_entry"] = $a_c_entry[$data['user_id']][$data['sco_id']];
243  $data["c_exit"] = $a_c_exit[$data['user_id']][$data['sco_id']];
244  // $data["c_language"] = "".$data["c_language"];
245  // $data["c_location"] = "".str_replace('"','',$data["c_location"]);
246  // $data["c_mode"] = "".$data["c_mode"];
247  $data["c_max"] = $a_c_max[$data['user_id']][$data['sco_id']];
248  $data["c_min"] = $a_c_min[$data['user_id']][$data['sco_id']];
249  $data["c_raw"] = $a_c_raw[$data['user_id']][$data['sco_id']];
250  $data["session_time"] = $a_session_time[$data['user_id']][$data['sco_id']];
251  // $data["session_time_seconds"] = "";
252  // if ($data["session_time"] != "") $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"])/100);
253  $data["total_time"] = $a_total_time[$data['user_id']][$data['sco_id']];
254  // $data["total_time_seconds"] = "";
255  // if ($data["total_time"] != "") $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"])/100);
256  $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
257  $data["suspend_data"] = $a_suspend_data[$data['user_id']][$data['sco_id']];
258  $data["launch_data"] = $a_launch_data[$data['user_id']][$data['sco_id']];
259  $returnData[]=$data;
260  }
261 
262  return $returnData;
263  }
264 
265  static function exportSelectedInteractionsColumns() {
266  global $lng;
267  $lng->loadLanguageModule("scormtrac");
268  $cols = array();
269  $udh=self::userDataHeaderForExport();
270  $a_cols=explode(',',
271  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,'.$udh["cols"]
272  .',counter,id,weighting,type,result,student_response,latency,time,c_timestamp');//,latency_seconds
273  $a_true=explode(',',$udh["default"].",sco_title,id,result,student_response");
274  for ($i=0;$i<count($a_cols);$i++) {
275  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
276  }
277  for ($i=0;$i<count($a_true);$i++) {
278  $cols[$a_true[$i]]["default"] = true;
279  }
280  return $cols;
281  }
282 
283  function exportSelectedInteractions($a_user = array(), $a_sco = array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id) {
284  global $ilDB;
285 
286  $returnData = array();
287 
288  $scoTitles = self::scoTitlesForExportSelected($obj_id);
289 
290  $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
291 
292  $dbdata = array();
293 
294  $interactionsCounter = array();
295  $prevcounter = -1;
296 
297  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
298  . 'FROM scorm_tracking '
299  . 'WHERE obj_id = %s AND '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
300  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
301  . 'AND left(lvalue,17) = %s '
302  . 'ORDER BY ';
303  if ($b_orderBySCO) $query.='sco_id, user_id, lvalue';
304  else $query.='user_id, sco_id, lvalue';
305  $res = $ilDB->queryF(
306  $query,
307  array('integer','text'),
308  array($obj_id,'cmi.interactions.'));
309 
310  while($row = $ilDB->fetchAssoc($res))
311  {
312  $tmpar = explode('.',$row["lvalue"]);
313  $tmpcounter = $tmpar[2];
314  if (in_array($tmpcounter,$interactionsCounter) == false) $interactionsCounter[] = $tmpcounter;
315  if ($tmpcounter != $prevcounter) {
316  $tmpar = array();
317  $tmpar["user_id"] = $row["user_id"];
318  $tmpar["sco_id"] = $row["sco_id"];
319  $tmpar["counter"] = $tmpcounter;
320  $tmpar["id"] = "";
321  $tmpar["weighting"] = "";
322  $tmpar["type"] = "";
323  $tmpar["result"] = "";
324  $tmpar["student_response"] = "";
325  $tmpar["latency"] = "";
326  $tmpar["time"] = "";
327  $tmpar["c_timestamp"] = $row["c_timestamp"];
328  $dbdata[] = $tmpar;
329  $prevcounter = $tmpcounter;
330  }
331  }
332 // id,weighting,type,result,student_response,latency,time
333 
334  $a_id = array();
335  $a_weighting = array();
336  $a_type = array();
337  $a_result = array();
338  $a_student_response = array();
339  $a_latency = array();
340  $a_time = array();
341  for($i=0;$i<count($interactionsCounter);$i++) {
342  $a_id=array_merge($a_id,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'id', $interactionsCounter[$i],'interactions'));
343  $a_weighting=array_merge($a_weighting,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'weighting', $interactionsCounter[$i],'interactions'));
344  $a_type=array_merge($a_type,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'type', $interactionsCounter[$i],'interactions'));
345  $a_result=array_merge($a_result,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'result', $interactionsCounter[$i],'interactions'));
346  $a_student_response=array_merge($a_student_response,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'student_response', $interactionsCounter[$i],'interactions'));
347  $a_latency=array_merge($a_latency,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'latency', $interactionsCounter[$i],'interactions'));
348  $a_time=array_merge($a_time,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'time', $interactionsCounter[$i],'interactions'));
349  }
350  foreach($dbdata as $data) {
351  $data["lm_id"] = $obj_id;
352  $data["lm_title"] = $this->lmTitle;
353 
354  $data=array_merge($data,self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
355 
356  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
357  $data["sco_title"] = $scoTitles[$data["sco_id"]];
358 
359  $combinedId = ''.$data["user_id"].'-'.$data["sco_id"].'-'.$data["counter"];
360  if (array_key_exists($combinedId,$a_id)) $data["id"] = $a_id[$combinedId];
361  if (array_key_exists($combinedId,$a_weighting)) $data["weighting"] = $a_weighting[$combinedId];
362  if (array_key_exists($combinedId,$a_type)) $data["type"] = $a_type[$combinedId];
363  if (array_key_exists($combinedId,$a_result)) $data["result"] = $a_result[$combinedId];
364  if (array_key_exists($combinedId,$a_student_response)) $data["student_response"] = $a_student_response[$combinedId];
365  if (array_key_exists($combinedId,$a_latency)) $data["latency"] = $a_latency[$combinedId];
366  if (array_key_exists($combinedId,$a_time)) $data["time"] = $a_time[$combinedId];
367 
368  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
369  $returnData[]=$data;
370  }
371 
372 // var_dump($returnData);
373  return $returnData;
374  }
375  /*
376  */
377  static function exportSelectedObjectivesColumns() {
378  global $lng;
379  $lng->loadLanguageModule("scormtrac");
380  $cols = array();
381  $udh=self::userDataHeaderForExport();
382  $a_cols=explode(',',
383  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,'.$udh["cols"]
384  .',counter,id,c_max,c_min,c_raw,ostatus,c_timestamp');
385  $a_true=explode(',',$udh["default"].",sco_title,id,c_raw,ostatus");
386  for ($i=0;$i<count($a_cols);$i++) {
387  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
388  }
389  for ($i=0;$i<count($a_true);$i++) {
390  $cols[$a_true[$i]]["default"] = true;
391  }
392  return $cols;
393  }
394 
395  function exportSelectedObjectives($a_user = array(), $a_sco = array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id) {
396  global $ilDB;
397 
398  $returnData = array();
399 
400  $scoTitles = self::scoTitlesForExportSelected($obj_id);
401 
402  $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
403 
404  $dbdata = array();
405 
406  $objectivesCounter = array();
407  $prevcounter = -1;
408 
409  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
410  . 'FROM scorm_tracking '
411  . 'WHERE obj_id = %s AND '.$ilDB->in('sco_id', $a_sco, false, 'integer') .' '
412  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
413  . 'AND left(lvalue,15) = %s '
414  . 'ORDER BY ';
415  if ($b_orderBySCO) $query.='sco_id, user_id, lvalue';
416  else $query.='user_id, sco_id, lvalue';
417  $res = $ilDB->queryF(
418  $query,
419  array('integer','text'),
420  array($obj_id,'cmi.objectives.'));
421 
422  while($row = $ilDB->fetchAssoc($res))
423  {
424  $tmpar = explode('.',$row["lvalue"]);
425  $tmpcounter = $tmpar[2];
426  if (in_array($tmpcounter,$objectivesCounter) == false) $objectivesCounter[] = $tmpcounter;
427  if ($tmpcounter != $prevcounter) {
428  $tmpar = array();
429  $tmpar["user_id"] = $row["user_id"];
430  $tmpar["sco_id"] = $row["sco_id"];
431  $tmpar["counter"] = $tmpcounter;
432  $tmpar["id"] = "";
433  $tmpar["c_max"] = "";
434  $tmpar["c_min"] = "";
435  $tmpar["c_raw"] = "";
436  $tmpar["ostatus"] = "";
437  $tmpar["c_timestamp"] = $row["c_timestamp"];
438  $dbdata[] = $tmpar;
439  $prevcounter = $tmpcounter;
440  }
441  }
442  $a_id = array();
443  $a_c_max = array();
444  $a_c_min = array();
445  $a_c_raw = array();
446  $a_status = array();
447  for($i=0;$i<count($objectivesCounter);$i++) {
448  $a_id=array_merge($a_id,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'id', $objectivesCounter[$i],'objectives'));
449  $a_c_max=array_merge($a_c_max,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.max', $objectivesCounter[$i],'objectives'));
450  $a_c_min=array_merge($a_c_min,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.min', $objectivesCounter[$i],'objectives'));
451  $a_c_raw=array_merge($a_c_raw,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.raw', $objectivesCounter[$i],'objectives'));
452  $a_status=array_merge($a_status,self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'status', $objectivesCounter[$i],'objectives'));
453  }
454  foreach($dbdata as $data) {
455  $data["lm_id"] = $obj_id;
456  $data["lm_title"] = $this->lmTitle;
457 
458  $data=array_merge($data,self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
459 
460  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
461  $data["sco_title"] = $scoTitles[$data["sco_id"]];
462 
463  $combinedId = ''.$data["user_id"].'-'.$data["sco_id"].'-'.$data["counter"];
464  if (array_key_exists($combinedId,$a_id)) $data["id"] = $a_id[$combinedId];
465  if (array_key_exists($combinedId,$a_c_max)) $data["c_max"] = $a_c_max[$combinedId];
466  if (array_key_exists($combinedId,$a_c_min)) $data["c_min"] = $a_c_min[$combinedId];
467  if (array_key_exists($combinedId,$a_c_raw)) $data["c_raw"] = $a_c_raw[$combinedId];
468  if (array_key_exists($combinedId,$a_status)) $data["ostatus"] = $a_status[$combinedId];
469 
470  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
471  $returnData[]=$data;
472  }
473 
474 // var_dump($returnData);
475  return $returnData;
476  }
477 
478  static function exportSelectedSuccessColumns() {
479  global $lng;
480  $lng->loadLanguageModule("scormtrac");
481  // default fields
482  $cols = array();
483 
484  $udh=self::userDataHeaderForExport();
485  $a_cols=explode(',','LearningModuleId,LearningModuleTitle,LearningModuleVersion,'.$udh["cols"]
486  .',status,Percentage,Attempts,existingSCOs,startedSCOs,completedSCOs,passedSCOs,roundedTotal_timeSeconds,offline_mode,last_access');
487  $a_true=explode(',',$udh["default"].",LearningModuleTitle,status,Percentage,Attempts");
488 
489  for ($i=0;$i<count($a_cols);$i++) {
490  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
491  }
492  for ($i=0;$i<count($a_true);$i++) {
493  $cols[$a_true[$i]]["default"] = true;
494  }
495  return $cols;
496  }
497 
498  function exportSelectedSuccessRows($a_user = array(), $allowExportPrivacy=false, $dbdata = array(), $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id) {
499  $returnData=array();
500  foreach($dbdata as $data) {
501  $dat=array();
502  $dat["LearningModuleId"] = $obj_id;
503  $dat["LearningModuleTitle"] = "".$this->lmTitle;
504  $dat["LearningModuleVersion"]="".$data["module_version"];
505 
506  $dat=array_merge($dat,self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
507 
508  $dat["status"]="".$data["status"];
509  $dat["Percentage"]="".$data["percentage_completed"];
510  $dat["Attempts"]="".$data["package_attempts"];
511  $dat["existingSCOs"]="".$scoCounter;
512  $dat["startedSCOs"]="".$u_startedSCO[$data["user_id"]];
513  $dat["completedSCOs"]="".$u_completedSCO[$data["user_id"]];
514  $dat["passedSCOs"]="".$u_passedSCO[$data["user_id"]];
515  $dat["roundedTotal_timeSeconds"]="".$data["sco_total_time_sec"];
516  if (is_null($data["offline_mode"])) $dat["offline_mode"]="";
517  else $dat["offline_mode"]=$data["offline_mode"];
518  $dat["last_access"]="".$data["last_access"];
519  $returnData[]=$dat;
520  }
521  return $returnData;
522  }
523 
524  function exportSelectedSuccess($a_user = array(), $allowExportPrivacy=false, $obj_id) {
525  global $ilDB;
526 
527  $scoCounter = 0;
528  $query = 'SELECT count(distinct(scorm_object.obj_id)) counter '
529  .'FROM scorm_object, sc_item, sc_resource '
530  .'WHERE scorm_object.slm_id = %s '
531  .'AND scorm_object.obj_id = sc_item.obj_id '
532  .'AND sc_item.identifierref = sc_resource.import_id '
533  .'AND (sc_resource.scormtype = %s OR sc_resource.scormtype is null)';
534  $res = $ilDB->queryF(
535  $query,
536  array('integer', 'text'),
537  array($obj_id,'sco')
538  );
539  while($row = $ilDB->fetchAssoc($res))
540  {
541  $scoCounter = $row['counter'];
542  }
543 
544  //data-arrays for all users
545  $u_startedSCO = array();
546  $u_completedSCO = array();
547  $u_passedSCO = array();
548  for($i=0; $i<count($a_user); $i++) {
549  $u_startedSCO[$a_user[$i]] = 0;
550  $u_completedSCO[$a_user[$i]] = 0;
551  $u_passedSCO[$a_user[$i]] = 0;
552  }
553 
554  $query = 'SELECT user_id, count(distinct(SCO_ID)) counter '
555  . 'FROM scorm_tracking '
556  . 'WHERE obj_id = %s '
557  . 'AND SCO_ID > 0 '
558  . 'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
559  . 'GROUP BY user_id';
560  $res = $ilDB->queryF(
561  $query,
562  array('integer'),
563  array($obj_id)
564  );
565  while ($data = $ilDB->fetchAssoc($res)) {
566  $u_startedSCO[$data['user_id']] = $data['counter'];
567  }
568 
569  $query = 'SELECT user_id, count(*) counter '
570  .'FROM scorm_tracking '
571  .'WHERE obj_id = %s AND lvalue = %s AND rvalue like %s '
572  .'AND '.$ilDB->in('user_id', $a_user, false, 'integer') .' '
573  .'GROUP BY user_id';
574  $res = $ilDB->queryF(
575  $query,
576  array('integer','text','text'),
577  array($obj_id,'cmi.core.lesson_status','completed')
578  );
579  while ($data = $ilDB->fetchAssoc($res)) {
580  $u_completedSCO[$data['user_id']] = $data['counter'];
581  }
582 
583  $res = $ilDB->queryF(
584  $query,
585  array('integer','text','text'),
586  array($obj_id,'cmi.core.lesson_status','passed')
587  );
588  while ($data = $ilDB->fetchAssoc($res)) {
589  $u_passedSCO[$data['user_id']] = $data['counter'];
590  }
591 
592  $dbdata = array();
593 
594  $query = 'SELECT * FROM sahs_user WHERE obj_id = '.$ilDB->quote($obj_id, 'integer')
595  .' AND '.$ilDB->in('user_id', $a_user, false, 'integer')
596  .' ORDER BY user_id';
597  $res = $ilDB->query($query);
598  while($row = $ilDB->fetchAssoc($res))
599  {
600  $dbdata[] = $row;
601  }
602  //return self::exportSelectedSuccessRows($a_user, $allowExportPrivacy, $dbdata, $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO);
603  return $this->exportSelectedSuccessRows($a_user, $allowExportPrivacy, $dbdata, $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id);
604  }
605 
606  public static function userDataHeaderForExport() {
607  include_once('./Services/PrivacySecurity/classes/class.ilPrivacySettings.php');
608  $privacy = ilPrivacySettings::_getInstance();
609  $allowExportPrivacy = $privacy->enabledExportSCORM();
610  $returnData = array();
611  if ($allowExportPrivacy == true) {
612  $returnData["cols"] = 'login,user,email,department';
613  } else {
614  $returnData["cols"] = 'user';
615  }
616  $returnData["default"] = 'user';
617  return $returnData;
618  }
619 
620 
621  function SCORMTimeToSeconds($a_time){
622  if ($a_time == "") return "";
623  $tarr = explode(":", $a_time);
624 // $sec = (int) $tarr[2] + (int) $tarr[1] * 60 + (int) substr($tarr[0], strlen($tarr[0]) - 3) * 3600;
625  if (count($tarr) != 3 || is_nan($tarr[0]) || is_nan($tarr[1]) || is_nan($tarr[2])) return "";
626  $csec = (int) $tarr[0] * 360000 + (int) $tarr[1] * 6000 + $tarr[2] * 100;
627  return round($csec/100);
628  }
629 
630 
631 
632 }
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id)
Definition: class.ilSCORMTrackingItems.php:283
ilObject\_exists
static _exists($a_id, $a_reference=false, $a_type=null)
checks if an object exists in object_data
Definition: class.ilObject.php:1552
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue($obj_id, $a_user=array(), $a_sco=array(), $a_empty=array(), $lvalue)
Definition: class.ilSCORMTrackingItems.php:71
ilSCORMTrackingItems\exportSelectedObjectives
exportSelectedObjectives($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id)
Definition: class.ilSCORMTrackingItems.php:395
ilSCORMTrackingItems\getScormTrackingValueForInteractionsOrObjectives
getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user=array(), $a_sco=array(), $lvalue, $counter, $topic)
Definition: class.ilSCORMTrackingItems.php:91
ilSCORMTrackingItems\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected($a_scos, $obj_id)
Definition: class.ilSCORMTrackingItems.php:36
ilSCORMTrackingItems\exportSelectedCoreColumns
static exportSelectedCoreColumns($b_orderBySCO, $b_allowExportPrivacy)
Definition: class.ilSCORMTrackingItems.php:165
$counter
$counter
Definition: config-scanner.php:59
$a_type
$a_type
Definition: workflow.php:93
ilSCORMTrackingItems\exportSelectedSuccessRows
exportSelectedSuccessRows($a_user=array(), $allowExportPrivacy=false, $dbdata=array(), $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id)
Definition: class.ilSCORMTrackingItems.php:498
ilSCORMTrackingItems\exportSelectedCore
exportSelectedCore($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id)
Definition: class.ilSCORMTrackingItems.php:184
$ilUser
$ilUser
Definition: imgupload.php:18
array
Create styles array
The data for the language used.
Definition: 40duplicateStyle.php:19
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess($a_user=array(), $allowExportPrivacy=false, $obj_id)
Definition: class.ilSCORMTrackingItems.php:524
$lng
global $lng
Definition: privfeed.php:17
ilSCORMTrackingItems\exportSelectedRaw
exportSelectedRaw($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id)
Definition: class.ilSCORMTrackingItems.php:130
$ilDB
global $ilDB
Definition: storeScorm2004.php:16
ilPrivacySettings\_getInstance
static _getInstance()
Get instance of ilPrivacySettings.
Definition: class.ilPrivacySettings.php:77
ilSCORMTrackingItems\userDataArrayForExport
static userDataArrayForExport($user, $b_allowExportPrivacy=false)
Definition: class.ilSCORMTrackingItems.php:49
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
Definition: class.ilSCORMTrackingItems.php:13
ilObjectLP\getInstance
static getInstance($a_obj_id)
Definition: class.ilObjectLP.php:27