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