ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
class.ilSCORMTrackingItems.php
Go to the documentation of this file.
1<?php
2
19declare(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
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}
ilObjUser
User class.
Definition: class.ilObjUser.php:46
ilObjectLP\getInstance
static getInstance(int $obj_id)
Definition: class.ilObjectLP.php:57
ilObject\_exists
static _exists(int $id, bool $reference=false, ?string $type=null)
checks if an object exists in object_data
Definition: class.ilObject.php:1452
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
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:243
ilSCORMTrackingItems\exportSelectedObjectives
exportSelectedObjectives(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:631
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:790
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:260
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue(int $obj_id, array $a_user, array $a_sco, array $a_empty, string $lvalue)
Definition: class.ilSCORMTrackingItems.php:378
ilSCORMTrackingItems\getScormTrackingValueForInteractionsOrObjectives
getScormTrackingValueForInteractionsOrObjectives(int $obj_id, array $a_user, array $a_sco, string $lvalue, int $counter, string $topic)
Definition: class.ilSCORMTrackingItems.php:598
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\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
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORMTrackingItems.php:405
$res
$res
Definition: ltiservices.php:69
$lng
global $lng
Definition: privfeed.php:31
$DIC
global $DIC
Definition: shib_login.php:26
$counter
$counter
Definition: shib_logout.php:170