ILIAS  release_8 Revision v8.24
class.ilSCORMTrackingItems.php
Go to the documentation of this file.
1<?php
2
3declare(strict_types=1);
25class 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}
ilObjUser
User class.
Definition: class.ilObjUser.php:28
ilObjectLP\getInstance
static getInstance(int $obj_id)
Definition: class.ilObjectLP.php:54
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
This file is part of ILIAS, a powerful learning management system published by ILIAS open source e-Le...
Definition: class.ilSCORMTrackingItems.php:26
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\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected(array $a_scos, int $obj_id)
Definition: class.ilSCORMTrackingItems.php:246
ilSCORMTrackingItems\exportSelectedObjectives
exportSelectedObjectives(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:637
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:796
ilSCORMTrackingItems\exportSelectedCoreColumns
static exportSelectedCoreColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORMTrackingItems.php:73
ilSCORMTrackingItems\userDataArrayForExport
static userDataArrayForExport(int $user, bool $b_allowExportPrivacy=false)
Definition: class.ilSCORMTrackingItems.php:263
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue)
Definition: class.ilSCORMTrackingItems.php:384
ilSCORMTrackingItems\getScormTrackingValueForInteractionsOrObjectives
getScormTrackingValueForInteractionsOrObjectives(int $obj_id, array $a_user, array $a_sco, string $lvalue, int $counter, string $topic)
Definition: class.ilSCORMTrackingItems.php:604
ilSCORMTrackingItems\exportSelectedCore
exportSelectedCore(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:289
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
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:411
$DIC
global $DIC
Definition: feed.php:28
$ilUser
$ilUser
Definition: imgupload.php:34
$res
$res
Definition: ltiservices.php:69
$i
$i
Definition: metadata.php:41
$query
$query
Definition: proxy_ylocal.php:13
$lng
$lng
Definition: save_question_post_data.php:24
$cols
$cols
Definition: xhr_table.php:11