ILIAS  trunk Revision v11.0_alpha-1689-g66c127b4ae8
•All Data Structures Namespaces Files Functions Variables Enumerations Enumerator Modules Pages
class.ilSCORMTrackingItems.php
Go to the documentation of this file.
1 <?php
2 
19 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 
243  public function markedLearningStatusForExportSelected(array $a_scos, int $obj_id): array
244  {
245  global $DIC;
246  $lng = $DIC->language();
247  $olp = ilObjectLP::getInstance($obj_id);
248  $collection = $olp->getCollectionInstance();
249 
250  foreach ($a_scos as $sco_id => $value) {
251  if ($collection && $collection->isAssignedEntry($sco_id)) {
252  $a_scos[$sco_id] = $lng->txt('yes');
253  } else {
254  $a_scos[$sco_id] = $lng->txt('no');
255  }
256  }
257  return $a_scos;
258  }
259 
260  public static function userDataArrayForExport(int $user, bool $b_allowExportPrivacy = false): array
261  {
262  $userArray = array();
263  if ($b_allowExportPrivacy == false) {
264  $userArray["user"] = $user;
265  } else {
266  global $DIC;
267  $ilUser = $DIC->user();
268  $userArray["login"] = "";
269  $userArray["user"] = "";
270  $userArray["email"] = "";
271  $userArray["department"] = "";
272  if (ilObject::_exists($user) && ilObject::_lookUpType($user) === 'usr') {
273  $e_user = new ilObjUser($user);
274  $userArray["login"] = $e_user->getLogin();
275  $userArray["user"] = $e_user->getLastname() . ', ' . $e_user->getFirstname();
276  $userArray["email"] = "" . $e_user->getEmail();
277  $userArray["department"] = "" . $e_user->getDepartment();
278  }
279  }
280  return $userArray;
281  }
282 
286  public function exportSelectedCore(
287  array $a_user,
288  array $a_sco,
289  bool $b_orderBySCO,
290  bool $allowExportPrivacy,
291  int $obj_id,
292  string $lmTitle
293  ): array {
294  global $DIC;
295  $ilDB = $DIC->database();
296  $lng = $DIC->language();
297  $lng->loadLanguageModule("scormtrac");
298 
299  $returnData = array();
300 
301  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
302 
303  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
304 
305  //data-arrays to fill for all users
306  $a_empty = array();
307  foreach ($a_user as $value) {
308  $a_empty[$value] = array();
309  }
310 
311  $dbdata = array();
312  $query = 'SELECT user_id, sco_id, max(c_timestamp) as c_timestamp '
313  . 'FROM scorm_tracking '
314  . 'WHERE ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
315  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
316  . 'GROUP BY user_id, sco_id '
317  . 'ORDER BY ';
318  if ($b_orderBySCO) {
319  $query .= 'sco_id, user_id';
320  } else {
321  $query .= 'user_id, sco_id';
322  }
323  $res = $ilDB->query($query);
324  while ($row = $ilDB->fetchAssoc($res)) {
325  $dbdata[] = $row;
326  $a_empty[$row["user_id"]][$row["sco_id"]] = "";
327  }
328 
329  $a_lesson_status = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.lesson_status');
330  $a_credit = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.credit');
331  $a_c_entry = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.entry');
332  $a_c_exit = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.exit');
333  $a_c_max = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.max');
334  $a_c_min = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.min');
335  $a_c_raw = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.raw');
336  $a_session_time = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.session_time');
337  $a_total_time = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.total_time');
338  $a_suspend_data = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.suspend_data');
339  $a_launch_data = $this->getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.launch_data');
340 
341  foreach ($dbdata as $data) {
342  $data["lm_id"] = $obj_id;
343  $data["lm_title"] = $lmTitle;
344 
345  $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.
346 
347  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
348  $data["sco_title"] = $scoTitles[$data["sco_id"]];
349 
350  // $data["audio_captioning"] = "".$data["audio_captioning"];
351  // $data["audio_level"] = "".$data["audio_level"];
352  $data["lesson_status"] = $a_lesson_status[$data['user_id']][$data['sco_id']];
353  $data["credit"] = $a_credit[$data['user_id']][$data['sco_id']];
354  // $data["delivery_speed"] = "".$data["delivery_speed"];
355  $data["c_entry"] = $a_c_entry[$data['user_id']][$data['sco_id']];
356  $data["c_exit"] = $a_c_exit[$data['user_id']][$data['sco_id']];
357  // $data["c_language"] = "".$data["c_language"];
358  // $data["c_location"] = "".str_replace('"','',$data["c_location"]);
359  // $data["c_mode"] = "".$data["c_mode"];
360  $data["c_max"] = $a_c_max[$data['user_id']][$data['sco_id']];
361  $data["c_min"] = $a_c_min[$data['user_id']][$data['sco_id']];
362  $data["c_raw"] = $a_c_raw[$data['user_id']][$data['sco_id']];
363  $data["session_time"] = $a_session_time[$data['user_id']][$data['sco_id']];
364  // $data["session_time_seconds"] = "";
365  // if ($data["session_time"] != "") $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"])/100);
366  $data["total_time"] = $a_total_time[$data['user_id']][$data['sco_id']];
367  // $data["total_time_seconds"] = "";
368  // if ($data["total_time"] != "") $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"])/100);
369  $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
370  $data["suspend_data"] = $a_suspend_data[$data['user_id']][$data['sco_id']];
371  $data["launch_data"] = $a_launch_data[$data['user_id']][$data['sco_id']];
372  $returnData[] = $data;
373  }
374 
375  return $returnData;
376  }
377 
378  public function getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue): array
379  {
380  global $DIC;
381  $ilDB = $DIC->database();
382 
383  $query = 'SELECT user_id, sco_id, rvalue '
384  . 'FROM scorm_tracking '
385  . 'WHERE obj_id = %s '
386  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
387  . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
388  . 'AND lvalue=%s';
389  $res = $ilDB->queryF(
390  $query,
391  array('integer', 'text'),
392  array($obj_id, $lvalue)
393  );
394  while ($data = $ilDB->fetchAssoc($res)) {
395  if (!is_null($data['rvalue'])) {
396  $a_empty[$data['user_id']][$data['sco_id']] = $data['rvalue'];
397  }
398  }
399  return $a_empty;
400  }
401 
405  public function exportSelectedInteractions(
406  array $a_user,
407  array $a_sco,
408  bool $b_orderBySCO,
409  bool $allowExportPrivacy,
410  int $obj_id,
411  string $lmTitle
412  ): array {
413  global $DIC;
414  $ilDB = $DIC->database();
415 
416  $returnData = array();
417 
418  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
419 
420  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
421 
422  $dbdata = array();
423 
424  $interactionsCounter = array();
425  $prevcounter = -1;
426 
427  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
428  . 'FROM scorm_tracking '
429  . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
430  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
431  . 'AND left(lvalue,17) = %s '
432  . 'ORDER BY ';
433  if ($b_orderBySCO) {
434  $query .= 'sco_id, user_id, lvalue';
435  } else {
436  $query .= 'user_id, sco_id, lvalue';
437  }
438  $res = $ilDB->queryF(
439  $query,
440  array('integer', 'text'),
441  array($obj_id, 'cmi.interactions.')
442  );
443 
444  while ($row = $ilDB->fetchAssoc($res)) {
445  $tmpar = explode('.', $row["lvalue"]);
446  $tmpcounter = $tmpar[2];
447  if (in_array($tmpcounter, $interactionsCounter) == false) {
448  $interactionsCounter[] = $tmpcounter;
449  }
450  if ($tmpcounter != $prevcounter) {
451  $tmpar = array();
452  $tmpar["user_id"] = $row["user_id"];
453  $tmpar["sco_id"] = $row["sco_id"];
454  $tmpar["counter"] = $tmpcounter;
455  $tmpar["id"] = "";
456  $tmpar["weighting"] = "";
457  $tmpar["type"] = "";
458  $tmpar["result"] = "";
459  $tmpar["student_response"] = "";
460  $tmpar["latency"] = "";
461  $tmpar["time"] = "";
462  $tmpar["c_timestamp"] = $row["c_timestamp"];
463  $dbdata[] = $tmpar;
464  $prevcounter = $tmpcounter;
465  }
466  }
467  // id,weighting,type,result,student_response,latency,time
468 
469  $a_id = array();
470  $a_weighting = array();
471  $a_type = array();
472  $a_result = array();
473  $a_student_response = array();
474  $a_latency = array();
475  $a_time = array();
476  foreach ($interactionsCounter as $value) {
477  $a_id = array_merge(
478  $a_id,
479  $this->getScormTrackingValueForInteractionsOrObjectives(
480  $obj_id,
481  $a_user,
482  $a_sco,
483  'id',
484  (int) $value,
485  'interactions'
486  )
487  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
488  $a_weighting = array_merge(
489  $a_weighting,
490  $this->getScormTrackingValueForInteractionsOrObjectives(
491  $obj_id,
492  $a_user,
493  $a_sco,
494  'weighting',
495  (int) $value,
496  'interactions'
497  )
498  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
499  $a_type = array_merge(
500  $a_type,
501  $this->getScormTrackingValueForInteractionsOrObjectives(
502  $obj_id,
503  $a_user,
504  $a_sco,
505  'type',
506  (int) $value,
507  'interactions'
508  )
509  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
510  $a_result = array_merge(
511  $a_result,
512  $this->getScormTrackingValueForInteractionsOrObjectives(
513  $obj_id,
514  $a_user,
515  $a_sco,
516  'result',
517  (int) $value,
518  'interactions'
519  )
520  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
521  $a_student_response = array_merge(
522  $a_student_response,
523  $this->getScormTrackingValueForInteractionsOrObjectives(
524  $obj_id,
525  $a_user,
526  $a_sco,
527  'student_response',
528  (int) $value,
529  'interactions'
530  )
531  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
532  $a_latency = array_merge(
533  $a_latency,
534  $this->getScormTrackingValueForInteractionsOrObjectives(
535  $obj_id,
536  $a_user,
537  $a_sco,
538  'latency',
539  (int) $value,
540  'interactions'
541  )
542  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
543  $a_time = array_merge(
544  $a_time,
545  $this->getScormTrackingValueForInteractionsOrObjectives(
546  $obj_id,
547  $a_user,
548  $a_sco,
549  'time',
550  (int) $value,
551  'interactions'
552  )
553  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
554  }
555  foreach ($dbdata as $data) {
556  $data["lm_id"] = $obj_id;
557  $data["lm_title"] = $lmTitle;
558 
559  $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.
560 
561  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
562  $data["sco_title"] = $scoTitles[$data["sco_id"]];
563 
564  $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
565  if (array_key_exists($combinedId, $a_id)) {
566  $data["id"] = $a_id[$combinedId];
567  }
568  if (array_key_exists($combinedId, $a_weighting)) {
569  $data["weighting"] = $a_weighting[$combinedId];
570  }
571  if (array_key_exists($combinedId, $a_type)) {
572  $data["type"] = $a_type[$combinedId];
573  }
574  if (array_key_exists($combinedId, $a_result)) {
575  $data["result"] = $a_result[$combinedId];
576  }
577  if (array_key_exists($combinedId, $a_student_response)) {
578  $data["student_response"] = $a_student_response[$combinedId];
579  }
580  if (array_key_exists($combinedId, $a_latency)) {
581  $data["latency"] = $a_latency[$combinedId];
582  }
583  if (array_key_exists($combinedId, $a_time)) {
584  $data["time"] = $a_time[$combinedId];
585  }
586 
587  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
588  $returnData[] = $data;
589  }
590 
591  // var_dump($returnData);
592  return $returnData;
593  }
594 
598  public function getScormTrackingValueForInteractionsOrObjectives(
599  int $obj_id,
600  array $a_user,
601  array $a_sco,
602  string $lvalue,
603  int $counter,
604  string $topic
605  ): array {
606  global $DIC;
607  $ilDB = $DIC->database();
608  $a_return = array();
609  $query = 'SELECT user_id, sco_id, rvalue '
610  . 'FROM scorm_tracking '
611  . 'WHERE obj_id = %s '
612  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
613  . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
614  . 'AND lvalue = %s';
615  $res = $ilDB->queryF(
616  $query,
617  array('integer', 'text'),
618  array($obj_id, 'cmi.' . $topic . '.' . $counter . '.' . $lvalue)
619  );
620  while ($data = $ilDB->fetchAssoc($res)) {
621  if (!is_null($data['rvalue'])) {
622  $a_return['' . $data['user_id'] . '-' . $data['sco_id'] . '-' . $counter] = $data['rvalue'];
623  }
624  }
625  return $a_return;
626  }
627 
631  public function exportSelectedObjectives(
632  array $a_user,
633  array $a_sco,
634  bool $b_orderBySCO,
635  bool $allowExportPrivacy,
636  int $obj_id,
637  string $lmTitle
638  ): array {
639  global $DIC;
640  $ilDB = $DIC->database();
641 
642  $returnData = array();
643 
644  $scoTitles = $this->scoTitlesForExportSelected($obj_id);
645 
646  $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
647 
648  $dbdata = array();
649 
650  $objectivesCounter = array();
651  $prevcounter = -1;
652 
653  $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
654  . 'FROM scorm_tracking '
655  . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
656  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
657  . 'AND left(lvalue,15) = %s '
658  . 'ORDER BY ';
659  if ($b_orderBySCO) {
660  $query .= 'sco_id, user_id, lvalue';
661  } else {
662  $query .= 'user_id, sco_id, lvalue';
663  }
664  $res = $ilDB->queryF(
665  $query,
666  array('integer', 'text'),
667  array($obj_id, 'cmi.objectives.')
668  );
669 
670  while ($row = $ilDB->fetchAssoc($res)) {
671  $tmpar = explode('.', $row["lvalue"]);
672  $tmpcounter = $tmpar[2];
673  if (in_array($tmpcounter, $objectivesCounter) == false) {
674  $objectivesCounter[] = $tmpcounter;
675  }
676  if ($tmpcounter != $prevcounter) {
677  $tmpar = array();
678  $tmpar["user_id"] = $row["user_id"];
679  $tmpar["sco_id"] = $row["sco_id"];
680  $tmpar["counter"] = $tmpcounter;
681  $tmpar["id"] = "";
682  $tmpar["c_max"] = "";
683  $tmpar["c_min"] = "";
684  $tmpar["c_raw"] = "";
685  $tmpar["ostatus"] = "";
686  $tmpar["c_timestamp"] = $row["c_timestamp"];
687  $dbdata[] = $tmpar;
688  $prevcounter = $tmpcounter;
689  }
690  }
691  $a_id = array();
692  $a_c_max = array();
693  $a_c_min = array();
694  $a_c_raw = array();
695  $a_status = array();
696  foreach ($objectivesCounter as $value) {
697  $a_id = array_merge(
698  $a_id,
699  $this->getScormTrackingValueForInteractionsOrObjectives(
700  $obj_id,
701  $a_user,
702  $a_sco,
703  'id',
704  (int) $value,
705  'objectives'
706  )
707  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
708  $a_c_max = array_merge(
709  $a_c_max,
710  $this->getScormTrackingValueForInteractionsOrObjectives(
711  $obj_id,
712  $a_user,
713  $a_sco,
714  'score.max',
715  (int) $value,
716  'objectives'
717  )
718  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
719  $a_c_min = array_merge(
720  $a_c_min,
721  $this->getScormTrackingValueForInteractionsOrObjectives(
722  $obj_id,
723  $a_user,
724  $a_sco,
725  'score.min',
726  (int) $value,
727  'objectives'
728  )
729  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
730  $a_c_raw = array_merge(
731  $a_c_raw,
732  $this->getScormTrackingValueForInteractionsOrObjectives(
733  $obj_id,
734  $a_user,
735  $a_sco,
736  'score.raw',
737  (int) $value,
738  'objectives'
739  )
740  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
741  $a_status = array_merge(
742  $a_status,
743  $this->getScormTrackingValueForInteractionsOrObjectives(
744  $obj_id,
745  $a_user,
746  $a_sco,
747  'status',
748  (int) $value,
749  'objectives'
750  )
751  );//PHP8Review: Just a notice that this may cause huge perfomance issues. But im not sure hiw this is refactorable.
752  }
753  foreach ($dbdata as $data) {
754  $data["lm_id"] = $obj_id;
755  $data["lm_title"] = $lmTitle;
756 
757  $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.
758 
759  $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
760  $data["sco_title"] = $scoTitles[$data["sco_id"]];
761 
762  $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
763  if (array_key_exists($combinedId, $a_id)) {
764  $data["id"] = $a_id[$combinedId];
765  }
766  if (array_key_exists($combinedId, $a_c_max)) {
767  $data["c_max"] = $a_c_max[$combinedId];
768  }
769  if (array_key_exists($combinedId, $a_c_min)) {
770  $data["c_min"] = $a_c_min[$combinedId];
771  }
772  if (array_key_exists($combinedId, $a_c_raw)) {
773  $data["c_raw"] = $a_c_raw[$combinedId];
774  }
775  if (array_key_exists($combinedId, $a_status)) {
776  $data["ostatus"] = $a_status[$combinedId];
777  }
778 
779  //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
780  $returnData[] = $data;
781  }
782 
783  // var_dump($returnData);
784  return $returnData;
785  }
786 
790  public function exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle): array
791  {
792  global $DIC;
793  $ilDB = $DIC->database();
794 
795  $scoCounter = 0;
796  $query = 'SELECT count(distinct(scorm_object.obj_id)) counter '
797  . 'FROM scorm_object, sc_item, sc_resource '
798  . 'WHERE scorm_object.slm_id = %s '
799  . 'AND scorm_object.obj_id = sc_item.obj_id '
800  . 'AND sc_item.identifierref = sc_resource.import_id '
801  . 'AND (sc_resource.scormtype = %s OR sc_resource.scormtype is null)';
802  $res = $ilDB->queryF(
803  $query,
804  array('integer', 'text'),
805  array($obj_id, 'sco')
806  );
807  while ($row = $ilDB->fetchAssoc($res)) {
808  $scoCounter = (int) $row['counter'];
809  }
810 
811  //data-arrays for all users
812  $u_startedSCO = array();
813  $u_completedSCO = array();
814  $u_passedSCO = array();
815  foreach ($a_user as $value) {
816  $u_startedSCO[$value] = 0;
817  $u_completedSCO[$value] = 0;
818  $u_passedSCO[$value] = 0;
819  }
820 
821  $query = 'SELECT user_id, count(distinct(SCO_ID)) counter '
822  . 'FROM scorm_tracking '
823  . 'WHERE obj_id = %s '
824  . 'AND SCO_ID > 0 '
825  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
826  . 'GROUP BY user_id';
827  $res = $ilDB->queryF(
828  $query,
829  array('integer'),
830  array($obj_id)
831  );
832  while ($data = $ilDB->fetchAssoc($res)) {
833  $u_startedSCO[$data['user_id']] = $data['counter'];
834  }
835 
836  $query = 'SELECT user_id, count(*) counter '
837  . 'FROM scorm_tracking '
838  . 'WHERE obj_id = %s AND lvalue = %s AND rvalue like %s '
839  . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
840  . 'GROUP BY user_id';
841  $res = $ilDB->queryF(
842  $query,
843  array('integer', 'text', 'text'),
844  array($obj_id, 'cmi.core.lesson_status', 'completed')
845  );
846  while ($data = $ilDB->fetchAssoc($res)) {
847  $u_completedSCO[$data['user_id']] = $data['counter'];
848  }
849 
850  $res = $ilDB->queryF(
851  $query,
852  array('integer', 'text', 'text'),
853  array($obj_id, 'cmi.core.lesson_status', 'passed')
854  );
855  while ($data = $ilDB->fetchAssoc($res)) {
856  $u_passedSCO[$data['user_id']] = $data['counter'];
857  }
858 
859  $dbdata = array();
860 
861  $query = 'SELECT * FROM sahs_user WHERE obj_id = ' . $ilDB->quote($obj_id, 'integer')
862  . ' AND ' . $ilDB->in('user_id', $a_user, false, 'integer')
863  . ' ORDER BY user_id';
864  $res = $ilDB->query($query);
865  while ($row = $ilDB->fetchAssoc($res)) {
866  $dbdata[] = $row;
867  }
868  return $this->exportSelectedSuccessRows(
869  $a_user,
870  $allowExportPrivacy,
871  $dbdata,
872  $scoCounter,
873  $u_startedSCO,
874  $u_completedSCO,
875  $u_passedSCO,
876  $obj_id,
877  $lmTitle
878  );
879  }
880 
884  public function exportSelectedSuccessRows(
885  array $a_user,
886  bool $allowExportPrivacy,
887  array $dbdata,
888  int $scoCounter,
889  array $u_startedSCO,
890  array $u_completedSCO,
891  array $u_passedSCO,
892  int $obj_id,
893  string $lmTitle
894  ): array {
895  $returnData = array();
896  foreach ($dbdata as $data) {
897  $dat = array();
898  $dat["LearningModuleId"] = $obj_id;
899  $dat["LearningModuleTitle"] = "" . $lmTitle;
900  $dat["LearningModuleVersion"] = "" . $data["module_version"];
901 
902  $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.
903 
904  $dat["status"] = "" . $data["status"];
905  $dat["Percentage"] = "" . $data["percentage_completed"];
906  $dat["Attempts"] = "" . $data["package_attempts"];
907  $dat["existingSCOs"] = "" . $scoCounter;
908  $dat["startedSCOs"] = "" . $u_startedSCO[$data["user_id"]];
909  $dat["completedSCOs"] = "" . $u_completedSCO[$data["user_id"]];
910  $dat["passedSCOs"] = "" . $u_passedSCO[$data["user_id"]];
911  $dat["roundedTotal_timeSeconds"] = "" . $data["sco_total_time_sec"];
912  if (is_null($data["offline_mode"])) {
913  $dat["offline_mode"] = "";
914  } else {
915  $dat["offline_mode"] = $data["offline_mode"];
916  }
917  $dat["last_access"] = "" . $data["last_access"];
918  $returnData[] = $dat;
919  }
920  return $returnData;
921  }
922 
923  public function SCORMTimeToSeconds(string $a_time): float|string
924  {
925  if ($a_time == "") {
926  return "";
927  }
928  $tarr = explode(":", $a_time);
929  // $sec = (int) $tarr[2] + (int) $tarr[1] * 60 + (int) substr($tarr[0], strlen($tarr[0]) - 3) * 3600;
930  if (count($tarr) != 3 || is_nan((float) $tarr[0]) || is_nan((float) $tarr[1]) || is_nan((float) $tarr[2])) {
931  return "";
932  }
933  $csec = (int) $tarr[0] * 360000 + (int) $tarr[1] * 6000 + $tarr[2] * 100;
934  return round($csec / 100);
935  }
936 }
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:884
$res
$res
Definition: ltiservices.php:66
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:405
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:631
ilObject\_exists
static _exists(int $id, bool $reference=false, ?string $type=null)
checks if an object exists in object_data
Definition: class.ilObject.php:1433
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue)
Definition: class.ilSCORMTrackingItems.php:378
ilSCORMTrackingItems\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected(array $a_scos, int $obj_id)
Definition: class.ilSCORMTrackingItems.php:243
ilSCORMTrackingItems\userDataArrayForExport
static userDataArrayForExport(int $user, bool $b_allowExportPrivacy=false)
Definition: class.ilSCORMTrackingItems.php:260
$DIC
global $DIC
Definition: shib_login.php:22
ilSCORMTrackingItems\exportSelectedCore
exportSelectedCore(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:286
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:790
$lng
global $lng
Definition: privfeed.php:31
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
Definition: class.ilSCORMTrackingItems.php:25
ilObjectLP\getInstance
static getInstance(int $obj_id)
Definition: class.ilObjectLP.php:57
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:598