ILIAS  release_8 Revision v8.19
•All Data Structures Namespaces Files Functions Variables Modules Pages
class.ilSCORMTrackingItems.php
Go to the documentation of this file.
1 <?php
2 
3 declare(strict_types=1);
25 class ilSCORMTrackingItems
26 {
30  public static function exportSelectedRawColumns(): array
31  {
32  global $DIC;
33  $lng = $DIC->language();
34  $lng->loadLanguageModule("scormtrac");
35  // default fields
36  $cols = array();
37  $udh = self::userDataHeaderForExport();
38  $a_cols = explode(
39  ',',
40  'lm_id,lm_title,identifierref,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
41  . ',c_timestamp,lvalue,rvalue'
42  );
43  $a_true = explode(',', $udh["default"] . ",identifierref,c_timestamp,lvalue,rvalue");
44  for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
45  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
46  }
47  for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
48  $cols[$a_true[$i]]["default"] = true;
49  }
50  return $cols;
51  }
52 
56  public static function userDataHeaderForExport(): array
57  {
58  $privacy = ilPrivacySettings::getInstance();
59  $allowExportPrivacy = $privacy->enabledExportSCORM();
60  $returnData = array();
61  if ($allowExportPrivacy == true) {
62  $returnData["cols"] = 'login,user,email,department';
63  } else {
64  $returnData["cols"] = 'user';
65  }
66  $returnData["default"] = 'user';
67  return $returnData;
68  }
69 
73  public static function exportSelectedCoreColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy): array
74  {
75  global $DIC;
76  $lng = $DIC->language();
77  $lng->loadLanguageModule("scormtrac");
78  // default fields
79  $cols = array();
80  $udh = self::userDataHeaderForExport();
81  $a_cols = explode(
82  ',',
83  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
84  . ',lesson_status,credit,c_entry,c_exit,c_max,c_min,c_raw,session_time,total_time,c_timestamp,suspend_data,launch_data'
85  );
86  $a_true = explode(',', $udh["default"] . ",sco_title,lesson_status");
87  for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
88  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
89  }
90  for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
91  $cols[$a_true[$i]]["default"] = true;
92  }
93  return $cols;
94  }
95 
99  public static function exportSelectedInteractionsColumns(): array
100  {
101  global $DIC;
102  $lng = $DIC->language();
103  $lng->loadLanguageModule("scormtrac");
104  $cols = array();
105  $udh = self::userDataHeaderForExport();
106  $a_cols = explode(
107  ',',
108  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
109  . ',counter,id,weighting,type,result,student_response,latency,time,c_timestamp'
110  );//,latency_seconds
111  $a_true = explode(',', $udh["default"] . ",sco_title,id,result,student_response");
112  for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
113  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
114  }
115  for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
116  $cols[$a_true[$i]]["default"] = true;
117  }
118  return $cols;
119  }
120 
124  public static function exportSelectedObjectivesColumns(): array
125  {
126  global $DIC;
127  $lng = $DIC->language();
128  $lng->loadLanguageModule("scormtrac");
129  $cols = array();
130  $udh = self::userDataHeaderForExport();
131  $a_cols = explode(
132  ',',
133  'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
134  . ',counter,id,c_max,c_min,c_raw,ostatus,c_timestamp'
135  );
136  $a_true = explode(',', $udh["default"] . ",sco_title,id,c_raw,ostatus");
137  for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
138  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
139  }
140  for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
141  $cols[$a_true[$i]]["default"] = true;
142  }
143  return $cols;
144  }
145 
149  public static function exportSelectedSuccessColumns(): array
150  {
151  global $DIC;
152  $lng = $DIC->language();
153  $lng->loadLanguageModule("scormtrac");
154  // default fields
155  $cols = array();
156 
157  $udh = self::userDataHeaderForExport();
158  $a_cols = explode(',', 'LearningModuleId,LearningModuleTitle,LearningModuleVersion,' . $udh["cols"]
159  . ',status,Percentage,Attempts,existingSCOs,startedSCOs,completedSCOs,passedSCOs,roundedTotal_timeSeconds,offline_mode,last_access');
160  $a_true = explode(',', $udh["default"] . ",LearningModuleTitle,status,Percentage,Attempts");
161 
162  for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
163  $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
164  }
165  for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
166  $cols[$a_true[$i]]["default"] = true;
167  }
168  return $cols;
169  }
170 
174  public function exportSelectedRaw(
175  array $a_user,
176  array $a_sco,
177  bool $b_orderBySCO,
178  bool $allowExportPrivacy,
179  int $obj_id,
180  string $lmTitle
181  ): array {
182  global $DIC;
183  $ilDB = $DIC->database();
184  $lng = $DIC->language();
185  $lng->loadLanguageModule("scormtrac");
186 
187  $returnData = array();
188 
189  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
190 
191  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
192 
193  $query = 'SELECT user_id, st.obj_id, sco_id, identifierref, c_timestamp, lvalue, rvalue '
194  . 'FROM scorm_tracking st '
195  . 'JOIN sc_item si ON st.sco_id = si.obj_id '
196  . 'WHERE ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
197  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
198 // . 'AND st.obj_id = '.$ilDB->quote($this->getId(),'integer') .' '
199  . 'ORDER BY ';
200  if ($b_orderBySCO) {
201  $query .= 'sco_id, user_id';
202  } else {
203  $query .= 'user_id, sco_id';
204  }
205  $res = $ilDB->query($query);
206  while ($data = $ilDB->fetchAssoc($res)) {
207  $data["lm_id"] = $obj_id;
208  $data["lm_title"] = $lmTitle;
209  $data = array_merge($data, self::userDataArrayForExport((int) $data["user_id"], $allowExportPrivacy));//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
210  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
211  $data["sco_title"] = $scoTitles[$data["sco_id"]];
212  $data["rvalue"] = "" . $data["rvalue"];
213  // $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
214  $returnData[] = $data;
215  }
216 
217  return $returnData;
218  }
219 
223  public function scoTitlesForExportSelected(int $obj_id): array
224  {
225  global $DIC;
226  $ilDB = $DIC->database();
227  $scoTitles = array();
228 
229  $query = 'SELECT obj_id, title
230  FROM scorm_object
231  WHERE slm_id = %s AND c_type = %s';
232  $res = $ilDB->queryF(
233  $query,
234  array('integer', 'text'),
235  array($obj_id, 'sit')
236  );
237  while ($row = $ilDB->fetchAssoc($res)) {
238  $scoTitles[$row['obj_id']] = $row['title'];
239  }
240  return $scoTitles;
241  }
242 
246  public function markedLearningStatusForExportSelected(array $a_scos, int $obj_id): array
247  {
248  global $DIC;
249  $lng = $DIC->language();
250  $olp = ilObjectLP::getInstance($obj_id);
251  $collection = $olp->getCollectionInstance();
252 
253  foreach ($a_scos as $sco_id => $value) {
254  if ($collection && $collection->isAssignedEntry($sco_id)) {
255  $a_scos[$sco_id] = $lng->txt('yes');
256  } else {
257  $a_scos[$sco_id] = $lng->txt('no');
258  }
259  }
260  return $a_scos;
261  }
262 
263  public static function userDataArrayForExport(int $user, bool $b_allowExportPrivacy = false): array
264  {
265  $userArray = array();
266  if ($b_allowExportPrivacy == false) {
267  $userArray["user"] = $user;
268  } else {
269  global $DIC;
270  $ilUser = $DIC->user();
271  $userArray["login"] = "";
272  $userArray["user"] = "";
273  $userArray["email"] = "";
274  $userArray["department"] = "";
275  if (ilObject::_exists($user) && ilObject::_lookUpType($user) === 'usr') {
276  $e_user = new ilObjUser($user);
277  $userArray["login"] = $e_user->getLogin();
278  $userArray["user"] = $e_user->getLastname() . ', ' . $e_user->getFirstname();
279  $userArray["email"] = "" . $e_user->getEmail();
280  $userArray["department"] = "" . $e_user->getDepartment();
281  }
282  }
283  return $userArray;
284  }
285 
289  public function exportSelectedCore(
290  array $a_user,
291  array $a_sco,
292  bool $b_orderBySCO,
293  bool $allowExportPrivacy,
294  int $obj_id,
295  string $lmTitle
296  ): array {
297  global $DIC;
298  $ilDB = $DIC->database();
299  $lng = $DIC->language();
300  $lng->loadLanguageModule("scormtrac");
301 
302  $returnData = array();
303 
304  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
305 
306  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
307 
308  //data-arrays to fill for all users
309  $a_empty = array();
310  foreach ($a_user as $value) {
311  $a_empty[$value] = array();
312  }
313 
314  $dbdata = array();
315  $query = 'SELECT user_id, sco_id, max(c_timestamp) as c_timestamp '
316  . 'FROM scorm_tracking '
317  . 'WHERE ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
318  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
319  . 'GROUP BY user_id, sco_id '
320  . 'ORDER BY ';
321  if ($b_orderBySCO) {
322  $query .= 'sco_id, user_id';
323  } else {
324  $query .= 'user_id, sco_id';
325  }
326  $res = $ilDB->query($query);
327  while ($row = $ilDB->fetchAssoc($res)) {
328  $dbdata[] = $row;
329  $a_empty[$row["user_id"]][$row["sco_id"]] = "";
330  }
331 
332  $a_lesson_status = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.lesson_status');
333  $a_credit = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.credit');
334  $a_c_entry = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.entry');
335  $a_c_exit = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.exit');
336  $a_c_max = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.max');
337  $a_c_min = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.min');
338  $a_c_raw = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.raw');
339  $a_session_time = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.session_time');
340  $a_total_time = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.total_time');
341  $a_suspend_data = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.suspend_data');
342  $a_launch_data = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.launch_data');
343 
344  foreach ($dbdata as $data) {
345  $data["lm_id"] = $obj_id;
346  $data["lm_title"] = $lmTitle;
347 
348  $data = array_merge($data, self::userDataArrayForExport((int) $data["user_id"], $allowExportPrivacy));//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
349 
350  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
351  $data["sco_title"] = $scoTitles[$data["sco_id"]];
352 
353  // $data["audio_captioning"] = "".$data["audio_captioning"];
354  // $data["audio_level"] = "".$data["audio_level"];
355  $data["lesson_status"] = $a_lesson_status[$data['user_id']][$data['sco_id']];
356  $data["credit"] = $a_credit[$data['user_id']][$data['sco_id']];
357  // $data["delivery_speed"] = "".$data["delivery_speed"];
358  $data["c_entry"] = $a_c_entry[$data['user_id']][$data['sco_id']];
359  $data["c_exit"] = $a_c_exit[$data['user_id']][$data['sco_id']];
360  // $data["c_language"] = "".$data["c_language"];
361  // $data["c_location"] = "".str_replace('"','',$data["c_location"]);
362  // $data["c_mode"] = "".$data["c_mode"];
363  $data["c_max"] = $a_c_max[$data['user_id']][$data['sco_id']];
364  $data["c_min"] = $a_c_min[$data['user_id']][$data['sco_id']];
365  $data["c_raw"] = $a_c_raw[$data['user_id']][$data['sco_id']];
366  $data["session_time"] = $a_session_time[$data['user_id']][$data['sco_id']];
367  // $data["session_time_seconds"] = "";
368  // if ($data["session_time"] != "") $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"])/100);
369  $data["total_time"] = $a_total_time[$data['user_id']][$data['sco_id']];
370  // $data["total_time_seconds"] = "";
371  // if ($data["total_time"] != "") $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"])/100);
372  $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
373  $data["suspend_data"] = $a_suspend_data[$data['user_id']][$data['sco_id']];
374  $data["launch_data"] = $a_launch_data[$data['user_id']][$data['sco_id']];
375  $returnData[] = $data;
376  }
377 
378  return $returnData;
379  }
380 
384  public function getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue): array
385  {
386  global $DIC;
387  $ilDB = $DIC->database();
388 
389  $query = 'SELECT user_id, sco_id, rvalue '
390  . 'FROM scorm_tracking '
391  . 'WHERE obj_id = %s '
392  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
393  . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
394  . 'AND lvalue=%s';
395  $res = $ilDB->queryF(
396  $query,
397  array('integer', 'text'),
398  array($obj_id, $lvalue)
399  );
400  while ($data = $ilDB->fetchAssoc($res)) {
401  if (!is_null($data['rvalue'])) {
402  $a_empty[$data['user_id']][$data['sco_id']] = $data['rvalue'];
403  }
404  }
405  return $a_empty;
406  }
407 
411  public function exportSelectedInteractions(
412  array $a_user,
413  array $a_sco,
414  bool $b_orderBySCO,
415  bool $allowExportPrivacy,
416  int $obj_id,
417  string $lmTitle
418  ): array {
419  global $DIC;
420  $ilDB = $DIC->database();
421 
422  $returnData = array();
423 
424  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
425 
426  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
427 
428  $dbdata = array();
429 
430  $interactionsCounter = array();
431  $prevcounter = -1;
432 
433  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
434  . 'FROM scorm_tracking '
435  . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
436  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
437  . 'AND left(lvalue,17) = %s '
438  . 'ORDER BY ';
439  if ($b_orderBySCO) {
440  $query .= 'sco_id, user_id, lvalue';
441  } else {
442  $query .= 'user_id, sco_id, lvalue';
443  }
444  $res = $ilDB->queryF(
445  $query,
446  array('integer', 'text'),
447  array($obj_id, 'cmi.interactions.')
448  );
449 
450  while ($row = $ilDB->fetchAssoc($res)) {
451  $tmpar = explode('.', $row["lvalue"]);
452  $tmpcounter = $tmpar[2];
453  if (in_array($tmpcounter, $interactionsCounter) == false) {
454  $interactionsCounter[] = $tmpcounter;
455  }
456  if ($tmpcounter != $prevcounter) {
457  $tmpar = array();
458  $tmpar["user_id"] = $row["user_id"];
459  $tmpar["sco_id"] = $row["sco_id"];
460  $tmpar["counter"] = $tmpcounter;
461  $tmpar["id"] = "";
462  $tmpar["weighting"] = "";
463  $tmpar["type"] = "";
464  $tmpar["result"] = "";
465  $tmpar["student_response"] = "";
466  $tmpar["latency"] = "";
467  $tmpar["time"] = "";
468  $tmpar["c_timestamp"] = $row["c_timestamp"];
469  $dbdata[] = $tmpar;
470  $prevcounter = $tmpcounter;
471  }
472  }
473  // id,weighting,type,result,student_response,latency,time
474 
475  $a_id = array();
476  $a_weighting = array();
477  $a_type = array();
478  $a_result = array();
479  $a_student_response = array();
480  $a_latency = array();
481  $a_time = array();
482  foreach ($interactionsCounter as $value) {
483  $a_id = array_merge(
484  $a_id,
485  $this->getScormTrackingValueForInteractionsOrObjectives(
486  $obj_id,
487  $a_user,
488  $a_sco,
489  'id',
490  (int) $value,
491  'interactions'
492  )
493  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
494  $a_weighting = array_merge(
495  $a_weighting,
496  $this->getScormTrackingValueForInteractionsOrObjectives(
497  $obj_id,
498  $a_user,
499  $a_sco,
500  'weighting',
501  (int) $value,
502  'interactions'
503  )
504  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
505  $a_type = array_merge(
506  $a_type,
507  $this->getScormTrackingValueForInteractionsOrObjectives(
508  $obj_id,
509  $a_user,
510  $a_sco,
511  'type',
512  (int) $value,
513  'interactions'
514  )
515  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
516  $a_result = array_merge(
517  $a_result,
518  $this->getScormTrackingValueForInteractionsOrObjectives(
519  $obj_id,
520  $a_user,
521  $a_sco,
522  'result',
523  (int) $value,
524  'interactions'
525  )
526  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
527  $a_student_response = array_merge(
528  $a_student_response,
529  $this->getScormTrackingValueForInteractionsOrObjectives(
530  $obj_id,
531  $a_user,
532  $a_sco,
533  'student_response',
534  (int) $value,
535  'interactions'
536  )
537  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
538  $a_latency = array_merge(
539  $a_latency,
540  $this->getScormTrackingValueForInteractionsOrObjectives(
541  $obj_id,
542  $a_user,
543  $a_sco,
544  'latency',
545  (int) $value,
546  'interactions'
547  )
548  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
549  $a_time = array_merge(
550  $a_time,
551  $this->getScormTrackingValueForInteractionsOrObjectives(
552  $obj_id,
553  $a_user,
554  $a_sco,
555  'time',
556  (int) $value,
557  'interactions'
558  )
559  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
560  }
561  foreach ($dbdata as $data) {
562  $data["lm_id"] = $obj_id;
563  $data["lm_title"] = $lmTitle;
564 
565  $data = array_merge($data, self::userDataArrayForExport((int) $data["user_id"], $allowExportPrivacy));//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
566 
567  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
568  $data["sco_title"] = $scoTitles[$data["sco_id"]];
569 
570  $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
571  if (array_key_exists($combinedId, $a_id)) {
572  $data["id"] = $a_id[$combinedId];
573  }
574  if (array_key_exists($combinedId, $a_weighting)) {
575  $data["weighting"] = $a_weighting[$combinedId];
576  }
577  if (array_key_exists($combinedId, $a_type)) {
578  $data["type"] = $a_type[$combinedId];
579  }
580  if (array_key_exists($combinedId, $a_result)) {
581  $data["result"] = $a_result[$combinedId];
582  }
583  if (array_key_exists($combinedId, $a_student_response)) {
584  $data["student_response"] = $a_student_response[$combinedId];
585  }
586  if (array_key_exists($combinedId, $a_latency)) {
587  $data["latency"] = $a_latency[$combinedId];
588  }
589  if (array_key_exists($combinedId, $a_time)) {
590  $data["time"] = $a_time[$combinedId];
591  }
592 
593  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
594  $returnData[] = $data;
595  }
596 
597  // var_dump($returnData);
598  return $returnData;
599  }
600 
604  public function getScormTrackingValueForInteractionsOrObjectives(
605  int $obj_id,
606  array $a_user,
607  array $a_sco,
608  string $lvalue,
609  int $counter,
610  string $topic
611  ): array {
612  global $DIC;
613  $ilDB = $DIC->database();
614  $a_return = array();
615  $query = 'SELECT user_id, sco_id, rvalue '
616  . 'FROM scorm_tracking '
617  . 'WHERE obj_id = %s '
618  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
619  . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
620  . 'AND lvalue = %s';
621  $res = $ilDB->queryF(
622  $query,
623  array('integer', 'text'),
624  array($obj_id, 'cmi.' . $topic . '.' . $counter . '.' . $lvalue)
625  );
626  while ($data = $ilDB->fetchAssoc($res)) {
627  if (!is_null($data['rvalue'])) {
628  $a_return['' . $data['user_id'] . '-' . $data['sco_id'] . '-' . $counter] = $data['rvalue'];
629  }
630  }
631  return $a_return;
632  }
633 
637  public function exportSelectedObjectives(
638  array $a_user,
639  array $a_sco,
640  bool $b_orderBySCO,
641  bool $allowExportPrivacy,
642  int $obj_id,
643  string $lmTitle
644  ): array {
645  global $DIC;
646  $ilDB = $DIC->database();
647 
648  $returnData = array();
649 
650  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
651 
652  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
653 
654  $dbdata = array();
655 
656  $objectivesCounter = array();
657  $prevcounter = -1;
658 
659  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
660  . 'FROM scorm_tracking '
661  . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
662  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
663  . 'AND left(lvalue,15) = %s '
664  . 'ORDER BY ';
665  if ($b_orderBySCO) {
666  $query .= 'sco_id, user_id, lvalue';
667  } else {
668  $query .= 'user_id, sco_id, lvalue';
669  }
670  $res = $ilDB->queryF(
671  $query,
672  array('integer', 'text'),
673  array($obj_id, 'cmi.objectives.')
674  );
675 
676  while ($row = $ilDB->fetchAssoc($res)) {
677  $tmpar = explode('.', $row["lvalue"]);
678  $tmpcounter = $tmpar[2];
679  if (in_array($tmpcounter, $objectivesCounter) == false) {
680  $objectivesCounter[] = $tmpcounter;
681  }
682  if ($tmpcounter != $prevcounter) {
683  $tmpar = array();
684  $tmpar["user_id"] = $row["user_id"];
685  $tmpar["sco_id"] = $row["sco_id"];
686  $tmpar["counter"] = $tmpcounter;
687  $tmpar["id"] = "";
688  $tmpar["c_max"] = "";
689  $tmpar["c_min"] = "";
690  $tmpar["c_raw"] = "";
691  $tmpar["ostatus"] = "";
692  $tmpar["c_timestamp"] = $row["c_timestamp"];
693  $dbdata[] = $tmpar;
694  $prevcounter = $tmpcounter;
695  }
696  }
697  $a_id = array();
698  $a_c_max = array();
699  $a_c_min = array();
700  $a_c_raw = array();
701  $a_status = array();
702  foreach ($objectivesCounter as $value) {
703  $a_id = array_merge(
704  $a_id,
705  $this->getScormTrackingValueForInteractionsOrObjectives(
706  $obj_id,
707  $a_user,
708  $a_sco,
709  'id',
710  (int) $value,
711  'objectives'
712  )
713  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
714  $a_c_max = array_merge(
715  $a_c_max,
716  $this->getScormTrackingValueForInteractionsOrObjectives(
717  $obj_id,
718  $a_user,
719  $a_sco,
720  'score.max',
721  (int) $value,
722  'objectives'
723  )
724  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
725  $a_c_min = array_merge(
726  $a_c_min,
727  $this->getScormTrackingValueForInteractionsOrObjectives(
728  $obj_id,
729  $a_user,
730  $a_sco,
731  'score.min',
732  (int) $value,
733  'objectives'
734  )
735  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
736  $a_c_raw = array_merge(
737  $a_c_raw,
738  $this->getScormTrackingValueForInteractionsOrObjectives(
739  $obj_id,
740  $a_user,
741  $a_sco,
742  'score.raw',
743  (int) $value,
744  'objectives'
745  )
746  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
747  $a_status = array_merge(
748  $a_status,
749  $this->getScormTrackingValueForInteractionsOrObjectives(
750  $obj_id,
751  $a_user,
752  $a_sco,
753  'status',
754  (int) $value,
755  'objectives'
756  )
757  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
758  }
759  foreach ($dbdata as $data) {
760  $data["lm_id"] = $obj_id;
761  $data["lm_title"] = $lmTitle;
762 
763  $data = array_merge($data, self::userDataArrayForExport((int) $data["user_id"], $allowExportPrivacy));//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
764 
765  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
766  $data["sco_title"] = $scoTitles[$data["sco_id"]];
767 
768  $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
769  if (array_key_exists($combinedId, $a_id)) {
770  $data["id"] = $a_id[$combinedId];
771  }
772  if (array_key_exists($combinedId, $a_c_max)) {
773  $data["c_max"] = $a_c_max[$combinedId];
774  }
775  if (array_key_exists($combinedId, $a_c_min)) {
776  $data["c_min"] = $a_c_min[$combinedId];
777  }
778  if (array_key_exists($combinedId, $a_c_raw)) {
779  $data["c_raw"] = $a_c_raw[$combinedId];
780  }
781  if (array_key_exists($combinedId, $a_status)) {
782  $data["ostatus"] = $a_status[$combinedId];
783  }
784 
785  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
786  $returnData[] = $data;
787  }
788 
789  // var_dump($returnData);
790  return $returnData;
791  }
792 
796  public function exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle): array
797  {
798  global $DIC;
799  $ilDB = $DIC->database();
800 
801  $scoCounter = 0;
802  $query = 'SELECT count(distinct(scorm_object.obj_id)) counter '
803  . 'FROM scorm_object, sc_item, sc_resource '
804  . 'WHERE scorm_object.slm_id = %s '
805  . 'AND scorm_object.obj_id = sc_item.obj_id '
806  . 'AND sc_item.identifierref = sc_resource.import_id '
807  . 'AND (sc_resource.scormtype = %s OR sc_resource.scormtype is null)';
808  $res = $ilDB->queryF(
809  $query,
810  array('integer', 'text'),
811  array($obj_id, 'sco')
812  );
813  while ($row = $ilDB->fetchAssoc($res)) {
814  $scoCounter = (int) $row['counter'];
815  }
816 
817  //data-arrays for all users
818  $u_startedSCO = array();
819  $u_completedSCO = array();
820  $u_passedSCO = array();
821  foreach ($a_user as $value) {
822  $u_startedSCO[$value] = 0;
823  $u_completedSCO[$value] = 0;
824  $u_passedSCO[$value] = 0;
825  }
826 
827  $query = 'SELECT user_id, count(distinct(SCO_ID)) counter '
828  . 'FROM scorm_tracking '
829  . 'WHERE obj_id = %s '
830  . 'AND SCO_ID > 0 '
831  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
832  . 'GROUP BY user_id';
833  $res = $ilDB->queryF(
834  $query,
835  array('integer'),
836  array($obj_id)
837  );
838  while ($data = $ilDB->fetchAssoc($res)) {
839  $u_startedSCO[$data['user_id']] = $data['counter'];
840  }
841 
842  $query = 'SELECT user_id, count(*) counter '
843  . 'FROM scorm_tracking '
844  . 'WHERE obj_id = %s AND lvalue = %s AND rvalue like %s '
845  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
846  . 'GROUP BY user_id';
847  $res = $ilDB->queryF(
848  $query,
849  array('integer', 'text', 'text'),
850  array($obj_id, 'cmi.core.lesson_status', 'completed')
851  );
852  while ($data = $ilDB->fetchAssoc($res)) {
853  $u_completedSCO[$data['user_id']] = $data['counter'];
854  }
855 
856  $res = $ilDB->queryF(
857  $query,
858  array('integer', 'text', 'text'),
859  array($obj_id, 'cmi.core.lesson_status', 'passed')
860  );
861  while ($data = $ilDB->fetchAssoc($res)) {
862  $u_passedSCO[$data['user_id']] = $data['counter'];
863  }
864 
865  $dbdata = array();
866 
867  $query = 'SELECT * FROM sahs_user WHERE obj_id = ' . $ilDB->quote($obj_id, 'integer')
868  . ' AND ' . $ilDB->in('user_id', $a_user, false, 'integer')
869  . ' ORDER BY user_id';
870  $res = $ilDB->query($query);
871  while ($row = $ilDB->fetchAssoc($res)) {
872  $dbdata[] = $row;
873  }
874  return $this->exportSelectedSuccessRows(
875  $a_user,
876  $allowExportPrivacy,
877  $dbdata,
878  $scoCounter,
879  $u_startedSCO,
880  $u_completedSCO,
881  $u_passedSCO,
882  $obj_id,
883  $lmTitle
884  );
885  }
886 
890  public function exportSelectedSuccessRows(
891  array $a_user,
892  bool $allowExportPrivacy,
893  array $dbdata,
894  int $scoCounter,
895  array $u_startedSCO,
896  array $u_completedSCO,
897  array $u_passedSCO,
898  int $obj_id,
899  string $lmTitle
900  ): array {
901  $returnData = array();
902  foreach ($dbdata as $data) {
903  $dat = array();
904  $dat["LearningModuleId"] = $obj_id;
905  $dat["LearningModuleTitle"] = "" . $lmTitle;
906  $dat["LearningModuleVersion"] = "" . $data["module_version"];
907 
908  $dat = array_merge($dat, self::userDataArrayForExport((int) $data["user_id"], $allowExportPrivacy));//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
909 
910  $dat["status"] = "" . $data["status"];
911  $dat["Percentage"] = "" . $data["percentage_completed"];
912  $dat["Attempts"] = "" . $data["package_attempts"];
913  $dat["existingSCOs"] = "" . $scoCounter;
914  $dat["startedSCOs"] = "" . $u_startedSCO[$data["user_id"]];
915  $dat["completedSCOs"] = "" . $u_completedSCO[$data["user_id"]];
916  $dat["passedSCOs"] = "" . $u_passedSCO[$data["user_id"]];
917  $dat["roundedTotal_timeSeconds"] = "" . $data["sco_total_time_sec"];
918  if (is_null($data["offline_mode"])) {
919  $dat["offline_mode"] = "";
920  } else {
921  $dat["offline_mode"] = $data["offline_mode"];
922  }
923  $dat["last_access"] = "" . $data["last_access"];
924  $returnData[] = $dat;
925  }
926  return $returnData;
927  }
928 
932  public function SCORMTimeToSeconds(string $a_time)
933  {
934  if ($a_time == "") {
935  return "";
936  }
937  $tarr = explode(":", $a_time);
938  // $sec = (int) $tarr[2] + (int) $tarr[1] * 60 + (int) substr($tarr[0], strlen($tarr[0]) - 3) * 3600;
939  if (count($tarr) != 3 || is_nan((float) $tarr[0]) || is_nan((float) $tarr[1]) || is_nan((float) $tarr[2])) {
940  return "";
941  }
942  $csec = (int) $tarr[0] * 360000 + (int) $tarr[1] * 6000 + $tarr[2] * 100;
943  return round($csec / 100);
944  }
945 }
ilSCORMTrackingItems\exportSelectedSuccessRows
exportSelectedSuccessRows(array $a_user, bool $allowExportPrivacy, array $dbdata, int $scoCounter, array $u_startedSCO, array $u_completedSCO, array $u_passedSCO, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:890
$res
$res
Definition: ltiservices.php:69
$lng
$lng
Definition: save_question_post_data.php:24
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:411
ilSCORMTrackingItems\exportSelectedCoreColumns
static exportSelectedCoreColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORMTrackingItems.php:73
ilSCORMTrackingItems\exportSelectedObjectives
exportSelectedObjectives(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:637
$DIC
global $DIC
Definition: feed.php:28
ilObject\_exists
static _exists(int $id, bool $reference=false, ?string $type=null)
checks if an object exists in object_data
Definition: class.ilObject.php:1440
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue)
Definition: class.ilSCORMTrackingItems.php:384
ilSCORMTrackingItems\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected(array $a_scos, int $obj_id)
Definition: class.ilSCORMTrackingItems.php:246
ilSCORMTrackingItems\userDataArrayForExport
static userDataArrayForExport(int $user, bool $b_allowExportPrivacy=false)
Definition: class.ilSCORMTrackingItems.php:263
ilSCORMTrackingItems\exportSelectedCore
exportSelectedCore(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:289
$query
$query
Definition: proxy_ylocal.php:13
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:796
$ilUser
$ilUser
Definition: imgupload.php:34
ilSCORMTrackingItems
This file is part of ILIAS, a powerful learning management system published by ILIAS open source e-Le...
Definition: class.ilSCORMTrackingItems.php:25
ilObjectLP\getInstance
static getInstance(int $obj_id)
Definition: class.ilObjectLP.php:54
$cols
$cols
Definition: xhr_table.php:11
ilSCORMTrackingItems\exportSelectedRaw
exportSelectedRaw(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:174
ilSCORMTrackingItems\getScormTrackingValueForInteractionsOrObjectives
getScormTrackingValueForInteractionsOrObjectives(int $obj_id, array $a_user, array $a_sco, string $lvalue, int $counter, string $topic)
Definition: class.ilSCORMTrackingItems.php:604
$i
$i
Definition: metadata.php:41