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