ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
class.ilSCORM2004TrackingItems.php
Go to the documentation of this file.
1<?php
2
19declare(strict_types=1);
27class ilSCORM2004TrackingItems extends ilSCORMTrackingItems
28{
32 protected ilDBInterface $db;
33
37 protected ilLanguage $lng;
38
39 public function __construct()
40 {
41 global $DIC;
42
43 $this->db = $DIC->database();
44 $this->lng = $DIC->language();
45 }
46
50 public function scoTitlesForExportSelected(int $obj_id): array
51 {
52 $ilDB = $this->db;
53 $scoTitles = array();
54 $query = 'SELECT cp_item.cp_node_id, cp_item.title '
55 . 'FROM cp_item, cmi_node, cp_node '
56 . 'WHERE cp_node.slm_id = %s '
57 . 'AND cp_item.cp_node_id = cmi_node.cp_node_id '
58 . 'AND cp_node.cp_node_id = cmi_node.cp_node_id '
59 . 'GROUP BY cp_item.cp_node_id, cp_item.title';
60 $res = $ilDB->queryF(
61 $query,
62 array('integer'),
63 array($obj_id)
64 );
65 while ($row = $ilDB->fetchAssoc($res)) {
66 $scoTitles[$row['cp_node_id']] = $row['title'];
67 }
68 return $scoTitles;
69 }
70
74 public static function exportSelectedCoreColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy): array
75 {
76 global $DIC;
77
78 $lng = $DIC->language();
79 $lng->loadLanguageModule("scormtrac");
80 // default fields
81 $cols = array();
82 $udh = self::userDataHeaderForExport();
83 $a_cols = explode(
84 ',',
85 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
86 . ',audio_captioning,audio_level,completion_status,completion_threshold,credit,delivery_speed'
87 . ',c_entry,c_exit,c_language,c_location,c_mode,progress_measure,c_max,c_min,c_raw,scaled'
88 . ',scaled_passing_score,session_time,session_time_seconds,success_status,total_time,total_time_seconds,c_timestamp,suspend_data,launch_data'
89 );
90 $a_true = explode(',', $udh["default"] . ",sco_title,success_status,completion_status");
91 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
92 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
93 }
94 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
95 $cols[$a_true[$i]]["default"] = true;
96 }
97 return $cols;
98 }
99
103 public function exportSelectedCore(
104 array $a_user,
105 array $a_sco,
106 bool $b_orderBySCO,
107 bool $allowExportPrivacy,
108 int $obj_id,
109 string $lmTitle
110 ): array {
111 $ilDB = $this->db;
112 $lng = $this->lng;
113 $lng->loadLanguageModule("scormtrac");
114
115 $returnData = array();
116
117 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
118
119 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
120
121
122 $dbdata = array();
123 $query = 'SELECT user_id, cp_node_id, '
124 . 'audio_captioning, audio_level, completion_status, completion_threshold, credit, delivery_speed, '
125 . 'c_entry, c_exit, c_language, location as c_location, c_mode, progress_measure, c_max, c_min, c_raw, scaled, '
126 . 'scaled_passing_score, session_time, success_status, total_time, c_timestamp, suspend_data, launch_data '
127 . 'FROM cmi_node '
128 . 'WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . ' '
129 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
130 . 'ORDER BY ';
131 if ($b_orderBySCO) {
132 $query .= 'cp_node_id, user_id';
133 } else {
134 $query .= 'user_id, cp_node_id';
135 }
136 $res = $ilDB->query($query);
137 while ($row = $ilDB->fetchAssoc($res)) {
138 $dbdata[] = $row;
139 }
140 foreach ($dbdata as $data) {
141 $data["lm_id"] = $obj_id;
142 $data["lm_title"] = $lmTitle;
143 $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.
144 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
145 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
146 $data["audio_captioning"] = "" . $data["audio_captioning"];
147 $data["audio_level"] = "" . $data["audio_level"];
148 $data["completion_status"] = "" . $data["completion_status"];
149 $data["completion_threshold"] = "" . $data["completion_threshold"];
150 $data["credit"] = "" . $data["credit"];
151 $data["delivery_speed"] = "" . $data["delivery_speed"];
152 $data["c_entry"] = "" . $data["c_entry"];
153 $data["c_exit"] = "" . $data["c_exit"];
154 $data["c_language"] = "" . $data["c_language"];
155 $data["c_location"] = "" . str_replace('"', '', (string) $data["c_location"]);
156 $data["c_mode"] = "" . $data["c_mode"];
157 $data["progress_measure"] = "" . $data["progress_measure"];
158 $data["c_max"] = "" . $data["c_max"];
159 $data["c_min"] = "" . $data["c_min"];
160 $data["c_raw"] = "" . $data["c_raw"];
161 $data["scaled"] = "" . $data["scaled"];//$data["scaled"]*100)
162 $data["scaled_passing_score"] = "" . $data["scaled_passing_score"];
163 $data["session_time"] = "" . $data["session_time"];
164 $data["session_time_seconds"] = "";
165 if ($data["session_time"] != "") {
166 $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec((string) $data["session_time"]) / 100);
167 }
168 $data["success_status"] = "" . $data["success_status"];
169 $data["total_time"] = "" . $data["total_time"];
170 $data["total_time_seconds"] = "";
171 if ($data["total_time"] != "") {
172 $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec((string) $data["total_time"]) / 100);
173 }
174 $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
175 $data["suspend_data"] = "" . $data["suspend_data"];
176 $data["launch_data"] = "" . $data["launch_data"];
177 // if ($data["success_status"]!="" && $data["success_status"]!="unknown") {
178 // $status = $data["success_status"];
179 // } else {
180 // if ($data["completion_status"]=="") {
181 // $status="unknown";
182 // } else {
183 // $status = $data["completion_status"];
184 // }
185 // }
186 $returnData[] = $data;
187 }
188
189 return $returnData;
190 }
191
195 public static function exportSelectedInteractionsColumns(): array
196 {
197 global $DIC;
198
199 $lng = $DIC->language();
200 $lng->loadLanguageModule("scormtrac");
201 $cols = array();
202 $udh = self::userDataHeaderForExport();
203 $a_cols = explode(
204 ',',
205 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
206 . ',id,description,weighting,c_type,result,latency,latency_seconds,c_timestamp,learner_response'
207 );
208 $a_true = explode(
209 ',',
210 $udh["default"] . ",sco_title,id,result,learner_response"
211 );//note for trunk: id instead of description
212 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
213 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
214 }
215 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
216 $cols[$a_true[$i]]["default"] = true;
217 }
218 return $cols;
219 }
220
224 public function exportSelectedInteractions(
225 array $a_user,
226 array $a_sco,
227 bool $b_orderBySCO,
228 bool $allowExportPrivacy,
229 int $obj_id,
230 string $lmTitle
231 ): array {
232 $ilDB = $this->db;
233 $lng = $this->lng;
234 $lng->loadLanguageModule("scormtrac");
235
236 $returnData = array();
237
238 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
239
240 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
241
242 $dbdata = array();
243 $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
244 cmi_interaction.cmi_interaction_id,
245 cmi_interaction.id,
246 cmi_interaction.description,
247 cmi_interaction.weighting,
248 cmi_interaction.c_type,
249 cmi_interaction.result,
250 cmi_interaction.latency,
251 cmi_interaction.c_timestamp,
252 cmi_interaction.learner_response,
253 cmi_interaction.cmi_interaction_id,
254 cmi_interaction.cmi_node_id
255 FROM cmi_interaction, cmi_node
256 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
257 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
258 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
259 ORDER BY ';
260 if ($b_orderBySCO) {
261 $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
262 } else {
263 $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
264 }
265 $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.cmi_node_id';
266 $res = $ilDB->query($query);
267 while ($row = $ilDB->fetchAssoc($res)) {
268 $dbdata[] = $row;
269 }
270 foreach ($dbdata as $data) {
271 $data["lm_id"] = $obj_id;
272 $data["lm_title"] = $lmTitle;
273 $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.
274 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
275 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
276 $data["description"] = "" . $data["description"];
277 $data["weighting"] = "" . $data["weighting"];
278 $data["c_type"] = "" . $data["c_type"];
279 $data["result"] = "" . $data["result"];
280 $data["latency"] = "" . $data["latency"];
281 $data["latency_seconds"] = "";
282 if ($data["latency"] != "") {
283 $data["latency_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["latency"]) / 100);
284 }
285 $data["c_timestamp"] = "" . $data["c_timestamp"];
286 $data["learner_response"] = str_replace('"', '', (string) ($data["learner_response"] ?? ''));
287 $returnData[] = $data;
288 }
289 // var_dump($returnData);
290 return $returnData;
291 }
292
296 public static function exportSelectedObjectivesColumns(): array
297 {
298 global $DIC;
299
300 $lng = $DIC->language();
301 $lng->loadLanguageModule("scormtrac");
302 $cols = array();
303 $udh = self::userDataHeaderForExport();
304 $a_cols = explode(
305 ',',
306 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
307 . ',id,description,completion_status,progress_measure,success_status,scaled,c_max,c_min,c_raw,scope'
308 );
309 $a_true = explode(',', $udh["default"] . ",sco_title,id,completion_status,success_status");
310 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
311 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
312 }
313 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
314 $cols[$a_true[$i]]["default"] = true;
315 }
316 return $cols;
317 }
318
322 public function exportSelectedObjectives(
323 array $a_user,
324 array $a_sco,
325 bool $b_orderBySCO,
326 bool $allowExportPrivacy,
327 int $obj_id,
328 string $lmTitle
329 ): array {
330 $ilDB = $this->db;
331 $lng = $this->lng;
332 $lng->loadLanguageModule("scormtrac");
333
334 $returnData = array();
335
336 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
337
338 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
339
340 $dbdata = array();
341 $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
342 cmi_objective.cmi_objective_id,
343 cmi_objective.id,
344 cmi_objective.description,
345 cmi_objective.completion_status,
346 cmi_objective.progress_measure,
347 cmi_objective.success_status,
348 cmi_objective.scaled,
349 cmi_objective.c_max,
350 cmi_objective.c_min,
351 cmi_objective.c_raw,
352 cmi_objective.scope
353 FROM cmi_objective, cmi_node
354 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
355 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
356 AND cmi_node.cmi_node_id = cmi_objective.cmi_node_id
357 AND cmi_interaction_id is null
358 ORDER BY ';
359 if ($b_orderBySCO) {
360 $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
361 } else {
362 $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
363 }
364 $query .= ', cmi_objective.cmi_node_id';
365 $res = $ilDB->query($query);
366 while ($row = $ilDB->fetchAssoc($res)) {
367 $dbdata[] = $row;
368 }
369 foreach ($dbdata as $data) {
370 $data["lm_id"] = $obj_id;
371 $data["lm_title"] = $lmTitle;
372 $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.
373 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
374 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
375 $data["description"] = "" . $data["description"];
376 $data["completion_status"] = "" . $data["completion_status"];
377 $data["progress_measure"] = "" . $data["progress_measure"];
378 $data["success_status"] = "" . $data["success_status"];
379 $data["scaled"] = "" . $data["scaled"];
380 $data["c_max"] = "" . $data["c_max"];
381 $data["c_min"] = "" . $data["c_min"];
382 $data["c_raw"] = "" . $data["c_raw"];
383 $data["scope"] = "" . $data["scope"];
384 $returnData[] = $data;
385 }
386 // var_dump($returnData);
387 return $returnData;
388 }
389
393 public static function exportObjGlobalToSystemColumns(): array
394 {
395 global $DIC;
396
397 $lng = $DIC->language();
398 $lng->loadLanguageModule("scormtrac");
399 $cols = array();
400 $udh = self::userDataHeaderForExport();
401 $a_cols = explode(
402 ',',
403 'lm_id,lm_title,' . $udh["cols"]
404 . ',Status,satisfied,measure,c_raw,c_min,c_max,completion_status,progress_measure'
405 );
406 $a_true = explode(',', $udh["default"] . ",lm_title,Status,satisfied,completion_status");
407 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
408 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
409 }
410 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
411 $cols[$a_true[$i]]["default"] = true;
412 }
413 return $cols;
414 }
415
419 public function exportObjGlobalToSystem(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle): array
420 {
421 $ilDB = $this->db;
422 $lng = $this->lng;
423 $lng->loadLanguageModule("scormtrac");
424 $returnData = array();
425 $dbdata = array();
426 $query = 'SELECT user_id, scope_id,
427 status,
428 satisfied,
429 measure,
430 score_raw as c_raw,
431 score_min as c_min,
432 score_max as c_max,
433 completion_status,
434 progress_measure
435 FROM cmi_gobjective
436 WHERE scope_id = %s
437 AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . '
438 ORDER BY user_id, scope_id';
439 $res = $ilDB->queryF($query, array('integer'), array($obj_id));
440 while ($row = $ilDB->fetchAssoc($res)) {
441 $dbdata[] = $row;
442 }
443 foreach ($dbdata as $data) {
444 $data["lm_id"] = $data["scope_id"];
445 $data["lm_title"] = $lmTitle;
446 $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.
447 $data["Status"] = "" . $data["status"];
448 $data["satisfied"] = "" . $data["satisfied"];
449 $data["measure"] = "" . $data["measure"];
450 $data["c_raw"] = "" . $data["c_raw"];
451 $data["c_min"] = "" . $data["c_min"];
452 $data["c_max"] = "" . $data["c_max"];
453 $data["completion_status"] = "" . $data["completion_status"];
454 $data["progress_measure"] = "" . $data["progress_measure"];
455 $returnData[] = $data;
456 }
457 // var_dump($returnData);
458 return $returnData;
459 }
460
464 public static function tracInteractionItemColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy): array
465 {
466 global $DIC;
467
468 $lng = $DIC->language();
469 $lng->loadLanguageModule("scormtrac");
470 $cols = array();
471 $a_cols = explode(
472 ',',
473 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title'
474 . ',id,description,counter_all'
475 . ',counter_correct,counter_correct_percent'
476 . ',counter_incorrect,counter_incorrect_percent'
477 . ',counter_other,counter_other_percent'
478 );
479 $a_true = explode(',', "sco_title,description,counter_correct,counter_incorrect");
480 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
481 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
482 }
483 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
484 $cols[$a_true[$i]]["default"] = true;
485 }
486 return $cols;
487 }
488
492 public function tracInteractionItem(
493 array $a_user,
494 array $a_sco,
495 bool $b_orderBySCO,
496 bool $allowExportPrivacy,
497 int $obj_id,
498 string $lmTitle
499 ): array {
500 $ilDB = $this->db;
501 $lng = $this->lng;
502 $lng->loadLanguageModule("scormtrac");
503
504 $returnData = array();
505
506 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
507
508 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
509
510 $a_correct = array();
511 $a_incorrect = array();
512 $query = 'SELECT cmi_node.cp_node_id, cmi_interaction.id, count(*) as counter
513 FROM cmi_interaction, cmi_node
514 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
515 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
516 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
517 AND cmi_interaction.result = %s
518 GROUP BY cmi_node.cp_node_id,cmi_interaction.id';
519
520 $res = $ilDB->queryF($query, array('text'), array('correct'));
521 while ($row = $ilDB->fetchAssoc($res)) {
522 $a_correct[$row['cp_node_id'] . ':' . $row['id']] = $row['counter'];
523 }
524
525 $res = $ilDB->queryF($query, array('text'), array('incorrect'));
526 while ($row = $ilDB->fetchAssoc($res)) {
527 $a_incorrect[$row['cp_node_id'] . ':' . $row['id']] = $row['counter'];
528 }
529
530 $dbdata = array();
531 $query = 'SELECT cmi_node.cp_node_id, cmi_interaction.id, cmi_interaction.description, count(*) as counter_all
532 FROM cmi_interaction, cmi_node
533 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
534 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
535 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
536 GROUP BY cmi_node.cp_node_id,cmi_interaction.id,cmi_interaction.description';
537 $res = $ilDB->query($query);
538 while ($row = $ilDB->fetchAssoc($res)) {
539 $dbdata[] = $row;
540 }
541 foreach ($dbdata as $data) {
542 $skey = $data["cp_node_id"] . ':' . $data["id"];
543 $all = $data["counter_all"];
544 $correct = 0;
545 if ($a_correct[$skey] != null) {
546 $correct = $a_correct[$skey];
547 }
548 $incorrect = 0;
549 if ($a_incorrect[$skey] != null) {
550 $incorrect = $a_incorrect[$skey];
551 }
552 $other = $all - ($correct + $incorrect);
553 $data["lm_id"] = $obj_id;
554 $data["lm_title"] = $lmTitle;
555 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
556 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
557 // $data["id"] = "".$data["id"];
558 $data["description"] = "" . $data["description"];
559 // $data["counter_all"] = $data["counter"];
560 $data["counter_correct"] = $correct;
561 $data["counter_correct_percent"] = $correct * 100 / $all;
562 $data["counter_incorrect"] = $incorrect;
563 $data["counter_incorrect_percent"] = $incorrect * 100 / $all;
564 $data["counter_other"] = $other;
565 $data["counter_other_percent"] = $other * 100 / $all;
566 $returnData[] = $data;
567 }
568 return $returnData;
569 }
570
574 public static function tracInteractionUserColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy): array
575 {
576 global $DIC;
577
578 $lng = $DIC->language();
579 $lng->loadLanguageModule("scormtrac");
580 // default fields
581 $cols = array();
582 $udh = self::userDataHeaderForExport();
583 $a_cols = explode(
584 ',',
585 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
586 . ',counter_i_correct,counter_i_correct_percent'
587 . ',counter_i_incorrect,counter_i_incorrect_percent'
588 . ',counter_i_other,counter_i_other_percent'
589 . ',audio_captioning,audio_level,completion_status,completion_threshold,credit,delivery_speed'
590 . ',c_entry,c_exit,c_language,c_location,c_mode,progress_measure,c_max,c_min,c_raw,scaled'
591 . ',scaled_passing_score,session_time,session_time_seconds,success_status,total_time,total_time_seconds,c_timestamp,suspend_data,launch_data'
592 );
593 $a_true = explode(',', $udh["default"] . ',sco_title'
594 . ',counter_i_correct,counter_i_correct_percent'
595 . ',counter_i_incorrect,counter_i_incorrect_percent'
596 . ',counter_i_other,counter_i_other_percent'
597 . ',c_raw,scaled');
598 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
599 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
600 }
601 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
602 $cols[$a_true[$i]]["default"] = true;
603 }
604 return $cols;
605 }
606
610 public function tracInteractionUser(
611 array $a_user,
612 array $a_sco,
613 bool $b_orderBySCO,
614 bool $allowExportPrivacy,
615 int $obj_id,
616 string $lmTitle
617 ): array {
618 $ilDB = $this->db;
619 $lng = $this->lng;
620 $lng->loadLanguageModule("scormtrac");
621
622 $returnData = array();
623
624 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
625
626 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
627
628 $a_correct = array();
629 $a_incorrect = array();
630 $a_other = array();
631 $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id, count(*) as counter
632 FROM cmi_interaction, cmi_node
633 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
634 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
635 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
636 AND cmi_interaction.result = %s
637 GROUP BY cmi_node.user_id,cmi_node.cp_node_id';
638
639 $res = $ilDB->queryF($query, array('text'), array('correct'));
640 while ($row = $ilDB->fetchAssoc($res)) {
641 $a_correct[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
642 }
643
644 $res = $ilDB->queryF($query, array('text'), array('incorrect'));
645 while ($row = $ilDB->fetchAssoc($res)) {
646 $a_incorrect[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
647 }
648
649 $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id, count(*) as counter
650 FROM cmi_interaction, cmi_node
651 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
652 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
653 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
654 AND cmi_interaction.result <> %s AND cmi_interaction.result <> %s
655 GROUP BY cmi_node.user_id,cmi_node.cp_node_id';
656 $res = $ilDB->queryF($query, array('text', 'text'), array('correct', 'incorrect'));
657 while ($row = $ilDB->fetchAssoc($res)) {
658 $a_other[$row['user_id'] . ':' . $row['cp_node_id']] = $row['counter'];
659 }
660
661 $dbdata = array();
662 $query = 'SELECT user_id, cp_node_id, '
663 . 'audio_captioning, audio_level, completion_status, completion_threshold, credit, delivery_speed, '
664 . 'c_entry, c_exit, c_language, location as c_location, c_mode, progress_measure, c_max, c_min, c_raw, scaled, '
665 . 'scaled_passing_score, session_time, success_status, total_time, c_timestamp, suspend_data, launch_data '
666 . 'FROM cmi_node '
667 . 'WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . ' '
668 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
669 . 'ORDER BY ';
670 if ($b_orderBySCO) {
671 $query .= 'cp_node_id, user_id';
672 } else {
673 $query .= 'user_id, cp_node_id';
674 }
675 $res = $ilDB->query($query);
676 while ($row = $ilDB->fetchAssoc($res)) {
677 $dbdata[] = $row;
678 }
679 foreach ($dbdata as $data) {
680 $skey = $data["user_id"] . ':' . $data["cp_node_id"];
681 $correct = 0;
682 if ($a_correct[$skey] != null) {
683 $correct = $a_correct[$skey];
684 }
685 $incorrect = 0;
686 if ($a_incorrect[$skey] != null) {
687 $incorrect = $a_incorrect[$skey];
688 }
689 $other = 0;
690 if ($a_other[$skey] != null) {
691 $other = $a_other[$skey];
692 }
693 $all = $correct + $incorrect + $other;
694 $data["lm_id"] = $obj_id;
695 $data["lm_title"] = $lmTitle;
696 $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.
697 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
698 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
699 $data["counter_i_correct"] = $correct;
700 if ($all > 0) {
701 $data["counter_i_correct_percent"] = $correct * 100 / $all;
702 } else {
703 $data["counter_i_correct_percent"] = 0;
704 }
705 $data["counter_i_incorrect"] = $incorrect;
706 if ($all > 0) {
707 $data["counter_i_incorrect_percent"] = $incorrect * 100 / $all;
708 } else {
709 $data["counter_i_incorrect_percent"] = 0;
710 }
711 $data["counter_i_other"] = $other;
712 if ($all > 0) {
713 $data["counter_i_other_percent"] = $other * 100 / $all;
714 } else {
715 $data["counter_i_other_percent"] = 0;
716 }
717 $data["audio_captioning"] = "" . $data["audio_captioning"];
718 $data["audio_level"] = "" . $data["audio_level"];
719 $data["completion_status"] = "" . $data["completion_status"];
720 $data["completion_threshold"] = "" . $data["completion_threshold"];
721 $data["credit"] = "" . $data["credit"];
722 $data["delivery_speed"] = "" . $data["delivery_speed"];
723 $data["c_entry"] = "" . $data["c_entry"];
724 $data["c_exit"] = "" . $data["c_exit"];
725 $data["c_language"] = "" . $data["c_language"];
726 $data["c_location"] = "" . str_replace('"', '', $data["c_location"]);
727 $data["c_mode"] = "" . $data["c_mode"];
728 $data["progress_measure"] = "" . $data["progress_measure"];
729 $data["c_max"] = "" . $data["c_max"];
730 $data["c_min"] = "" . $data["c_min"];
731 $data["c_raw"] = "" . $data["c_raw"];
732 $data["scaled"] = "" . $data["scaled"];//$data["scaled"]*100)
733 $data["scaled_passing_score"] = "" . $data["scaled_passing_score"];
734 $data["session_time"] = "" . $data["session_time"];
735 $data["session_time_seconds"] = "";
736 if ($data["session_time"] != "") {
737 $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"]) / 100);
738 }
739 $data["success_status"] = "" . $data["success_status"];
740 $data["total_time"] = "" . $data["total_time"];
741 $data["total_time_seconds"] = "";
742 if ($data["total_time"] != "") {
743 $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"]) / 100);
744 }
745 $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
746 $data["suspend_data"] = "" . $data["suspend_data"];
747 $data["launch_data"] = "" . $data["launch_data"];
748 // if ($data["success_status"]!="" && $data["success_status"]!="unknown") {
749 // $status = $data["success_status"];
750 // } else {
751 // if ($data["completion_status"]=="") {
752 // $status="unknown";
753 // } else {
754 // $status = $data["completion_status"];
755 // }
756 // }
757 $returnData[] = $data;
758 }
759 return $returnData;
760 }
761
765 public static function tracInteractionUserAnswersColumns(
766 array $a_user,
767 array $a_sco,
768 bool $b_orderBySCO,
769 bool $b_allowExportPrivacy
770 ): array {
771 global $DIC;
772
773 $lng = $DIC->language();
774 $ilDB = $DIC->database();
775 $lng->loadLanguageModule("scormtrac");
776 // default fields
777 $cols = array();
778 $a_interaction = array();
779 $a_interactionDescription = array();
780 $dbdata = array();
781 $query = 'SELECT cmi_node.cp_node_id,
782 cmi_interaction.cmi_interaction_id,
783 cmi_interaction.id,
784 cmi_interaction.description
785 FROM cmi_interaction, cmi_node
786 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
787 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
788 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
789 ORDER BY ';
790 if ($b_orderBySCO) {
791 $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
792 } else {
793 $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
794 }
795 $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.id';
796
797 $res = $ilDB->query($query);
798 while ($row = $ilDB->fetchAssoc($res)) {
799 $dbdata[] = $row;
800 }
801 foreach ($dbdata as $data) {
802 $key = $data["cp_node_id"] . ':' . $data["id"];
803 $exist = false;
804 for ($i = 0, $iMax = count($a_interaction); $i < $iMax; $i++) {
805 if ($a_interaction[$i] == $key) {
806 $exist = true;
807 }
808 }
809 if ($exist == false) {
810 $a_interaction[] = $key;
811 }
812 if ($a_interactionDescription[$key] == null) {
813 $a_interactionDescription[$key] = "" . $data["description"];
814 }
815 }
816 $udh = self::userDataHeaderForExport();
817 $a_cols = explode(',', 'lm_id,lm_title,cp_node_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]);
818 $a_true = explode(',', $udh["default"] . ",sco_title");
819 for ($i = 0, $iMax = count($a_cols); $i < $iMax; $i++) {
820 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]), "default" => false);
821 }
822 for ($i = 0, $iMax = count($a_true); $i < $iMax; $i++) {
823 $cols[$a_true[$i]]["default"] = true;
824 }
825 for ($i = 0, $iMax = count($a_interaction); $i < $iMax; $i++) {
826 // $cols["interaction_id".$i] = array("txt" => $lng->txt("interaction_id").' '.$i,"default" => false);
827 // if ($a_interactionDescription[$a_interaction[$i]] != "") {
828 // $cols["interaction_description".$i] = array("txt" => $lng->txt("interaction_description").' '.$i,"default" => false);
829 // }
830 // $cols["interaction_value".$i] = array("txt" => $lng->txt("interaction_value").' '.$i,"default" => true);//$a_interactionDescription[$a_interaction[$i]]
831 $cols["interaction_value" . $i . " " . $a_interactionDescription[$a_interaction[$i]]] = array(
832 "txt" => sprintf(
833 $lng->txt("interaction_value"),
834 $i
835 ) . " " . $a_interactionDescription[$a_interaction[$i]],
836 "default" => true
837 );
838 }
839 return $cols;
840 }
841
845 public function tracInteractionUserAnswers(
846 array $a_user,
847 array $a_sco,
848 bool $b_orderBySCO,
849 bool $allowExportPrivacy,
850 int $obj_id,
851 string $lmTitle
852 ): array {
853 $ilDB = $this->db;
854 $lng = $this->lng;
855 $lng->loadLanguageModule("scormtrac");
856
857 $returnData = array();
858
859 $scoTitles = $this->scoTitlesForExportSelected($obj_id);
860
861 $scoProgress = $this->markedLearningStatusForExportSelected($scoTitles, $obj_id);
862
863 $a_interaction = array();
864 $a_interactionId = array();
865 $a_interactionDescription = array();
866 $a_interactionUser = array();
867 $dbdata = array();
868 $query = 'SELECT cmi_node.user_id, cmi_node.cp_node_id,
869 cmi_interaction.cmi_interaction_id,
870 cmi_interaction.id,
871 cmi_interaction.result,
872 cmi_interaction.description
873 FROM cmi_interaction, cmi_node
874 WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . '
875 AND ' . $ilDB->in('cmi_node.user_id', $a_user, false, 'integer') . '
876 AND cmi_node.cmi_node_id = cmi_interaction.cmi_node_id
877 ORDER BY ';
878 if ($b_orderBySCO) {
879 $query .= 'cmi_node.cp_node_id, cmi_node.user_id';
880 } else {
881 $query .= 'cmi_node.user_id, cmi_node.cp_node_id';
882 }
883 $query .= ', cmi_interaction.cmi_interaction_id, cmi_interaction.id';
884 $res = $ilDB->query($query);
885 while ($row = $ilDB->fetchAssoc($res)) {
886 $dbdata[] = $row;
887 }
888 foreach ($dbdata as $data) {
889 $key = $data["cp_node_id"] . ':' . $data["id"];
890 $exist = false;
891 for ($i = 0, $iMax = count($a_interaction); $i < $iMax; $i++) {
892 if ($a_interaction[$i] == $key) {
893 $exist = true;
894 }
895 }
896 // if ($exist==false) $a_interaction[] = $key;
897 // if ($a_interactionId[$key]==null) $a_interactionId[$key] = "".$data["id"];
898 // if ($a_interactionDescription[$key]==null) $a_interactionDescription[$key] = "".$data["description"];
899 if ($exist == false) {
900 $a_interaction[] = $key;
901 $a_interactionId[$key] = "" . $data["id"];
902 $a_interactionDescription[$key] = "" . $data["description"];
903 }
904 $key .= ':' . $data["user_id"];
905 $a_interactionUser[$key] = "" . $data["result"];
906 }
907 // var_dump($a_interactionUser);
908
909 $dbdata = array();
910 $query = 'SELECT user_id, cp_node_id '
911 . 'FROM cmi_node '
912 . 'WHERE ' . $ilDB->in('cp_node_id', $a_sco, false, 'integer') . ' '
913 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
914 // . 'GROUP BY user_id '
915 . 'ORDER BY ';
916 if ($b_orderBySCO) {
917 $query .= 'cp_node_id, user_id';
918 } else {
919 $query .= 'user_id, cp_node_id';
920 }
921 $res = $ilDB->query($query);
922 while ($row = $ilDB->fetchAssoc($res)) {
923 $dbdata[] = $row;
924 }
925 foreach ($dbdata as $data) {
926 for ($i = 0, $iMax = count($a_interaction); $i < $iMax; $i++) {
927 // $data["interaction_id".$i] = $a_interactionId[$a_interaction[$i]];
928 // $data["interaction_description".$i] = $a_interactionDescription[$a_interaction[$i]];
929 // $data["interaction_value".$i] = "";
930 // $ukey=$a_interaction[$i].':'.$data["user_id"];
931 // if ($a_interactionUser[$ukey] != null) $data["interaction_value".$i] = $a_interactionUser[$ukey];
932 $intdesc = "interaction_value" . $i . " " . $a_interactionDescription[$a_interaction[$i]];
933 $data[$intdesc] = "";
934 $ukey = $a_interaction[$i] . ':' . $data["user_id"];
935 if ($a_interactionUser[$ukey] != null) {
936 $data[$intdesc] = $a_interactionUser[$ukey];
937 }
938 }
939 $data["lm_id"] = $obj_id;
940 $data["lm_title"] = $lmTitle;
941 $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.
942 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["cp_node_id"]];
943 $data["sco_title"] = $scoTitles[$data["cp_node_id"]];
944 $returnData[] = $data;
945 }
946 // var_dump($returnData);
947 return $returnData;
948 }
949
953 public function exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle): array
954 {
955 $ilDB = $this->db;
956
957 $scoCounter = 0;
958 $query = 'SELECT count(distinct(cp_node.cp_node_id)) counter '
959 . 'FROM cp_node, cp_resource, cp_item '
960 . 'WHERE cp_item.cp_node_id = cp_node.cp_node_id '
961 . 'AND cp_item.resourceid = cp_resource.id AND scormtype = %s '
962 . 'AND nodename = %s AND cp_node.slm_id = %s';
963 $res = $ilDB->queryF(
964 $query,
965 array('text', 'text', 'integer'),
966 array('sco', 'item', $obj_id)
967 );
968 while ($row = $ilDB->fetchAssoc($res)) {
969 $scoCounter = (int) $row['counter'];
970 }
971
972 $u_startedSCO = array();
973 $u_completedSCO = array();
974 $u_passedSCO = array();
975 foreach ($a_user as $value) {
976 $u_startedSCO[$value] = 0;
977 $u_completedSCO[$value] = 0;
978 $u_passedSCO[$value] = 0;
979 }
980
981 $query = 'SELECT user_id, count(*) counter '
982 . 'FROM cmi_node, cp_node '
983 . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
984 . 'AND cp_node.slm_id = %s '
985 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
986 . 'GROUP BY user_id';
987 $res = $ilDB->queryF(
988 $query,
989 array('integer'),
990 array($obj_id)
991 );
992 while ($data = $ilDB->fetchAssoc($res)) {
993 $u_startedSCO[$data['user_id']] = $data['counter'];
994 }
995
996 $query = 'SELECT user_id, count(*) counter '
997 . 'FROM cmi_node, cp_node '
998 . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
999 . 'AND cp_node.slm_id = %s '
1000 . "AND cmi_node.completion_status = 'completed' "
1001 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
1002 . 'GROUP BY user_id';
1003 $res = $ilDB->queryF(
1004 $query,
1005 array('integer'),
1006 array($obj_id)
1007 );
1008 while ($data = $ilDB->fetchAssoc($res)) {
1009 $u_completedSCO[$data['user_id']] = $data['counter'];
1010 }
1011
1012 $query = 'SELECT user_id, count(*) counter '
1013 . 'FROM cmi_node, cp_node '
1014 . 'WHERE cmi_node.cp_node_id = cp_node.cp_node_id '
1015 . 'AND cp_node.slm_id = %s '
1016 . "AND cmi_node.success_status = 'passed' "
1017 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
1018 . 'GROUP BY user_id';
1019 $res = $ilDB->queryF(
1020 $query,
1021 array('integer'),
1022 array($obj_id)
1023 );
1024 while ($data = $ilDB->fetchAssoc($res)) {
1025 $u_passedSCO[$data['user_id']] = $data['counter'];
1026 }
1027
1028 $dbdata = array();
1029
1030 $query = 'SELECT * FROM sahs_user WHERE obj_id = ' . $ilDB->quote($obj_id, 'integer')
1031 . ' AND ' . $ilDB->in('user_id', $a_user, false, 'integer')
1032 . ' ORDER BY user_id';
1033 $res = $ilDB->query($query);
1034 while ($row = $ilDB->fetchAssoc($res)) {
1035 $dbdata[] = $row;
1036 }
1037
1038 return $this->exportSelectedSuccessRows(
1039 $a_user,
1040 $allowExportPrivacy,
1041 $dbdata,
1042 $scoCounter,
1043 $u_startedSCO,
1044 $u_completedSCO,
1045 $u_passedSCO,
1046 $obj_id,
1047 $lmTitle
1048 );
1049 //CertificateDate?
1050 }
1051}
ilLanguage
language handling
Definition: class.ilLanguage.php:43
ilLanguage\loadLanguageModule
loadLanguageModule(string $a_module)
Load language module.
Definition: class.ilLanguage.php:226
ilLanguage\txt
txt(string $a_topic, string $a_default_lang_fallback_mod="")
gets the text for a given topic if the topic is not in the list, the topic itself with "-" will be re...
Definition: class.ilLanguage.php:170
ilObjSCORM2004LearningModule\_ISODurationToCentisec
static _ISODurationToCentisec(string $str)
convert ISO 8601 Timeperiods to centiseconds
Definition: class.ilObjSCORM2004LearningModule.php:614
ilSCORM2004TrackingItems
Class ilSCORM2004TrackingItems.
Definition: class.ilSCORM2004TrackingItems.php:28
ilSCORM2004TrackingItems\exportSelectedSuccess
exportSelectedSuccess(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:953
ilSCORM2004TrackingItems\exportObjGlobalToSystem
exportObjGlobalToSystem(array $a_user, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:419
ilSCORM2004TrackingItems\tracInteractionItem
tracInteractionItem(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:492
ilSCORM2004TrackingItems\exportSelectedInteractions
exportSelectedInteractions(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:224
ilSCORM2004TrackingItems\exportSelectedCoreColumns
static exportSelectedCoreColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:74
ilSCORM2004TrackingItems\exportSelectedObjectives
exportSelectedObjectives(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:322
ilSCORM2004TrackingItems\tracInteractionUserAnswersColumns
static tracInteractionUserAnswersColumns(array $a_user, array $a_sco, bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:765
ilSCORM2004TrackingItems\tracInteractionUser
tracInteractionUser(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:610
ilSCORM2004TrackingItems\tracInteractionUserAnswers
tracInteractionUserAnswers(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:845
ilSCORM2004TrackingItems\tracInteractionItemColumns
static tracInteractionItemColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:464
ilSCORM2004TrackingItems\exportSelectedCore
exportSelectedCore(array $a_user, array $a_sco, bool $b_orderBySCO, bool $allowExportPrivacy, int $obj_id, string $lmTitle)
Definition: class.ilSCORM2004TrackingItems.php:103
ilSCORM2004TrackingItems\tracInteractionUserColumns
static tracInteractionUserColumns(bool $b_orderBySCO, bool $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:574
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
Definition: class.ilSCORMTrackingItems.php:26
ilSCORMTrackingItems\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected(array $a_scos, int $obj_id)
Definition: class.ilSCORMTrackingItems.php:243
ilDBInterface
Interface ilDBInterface.
Definition: interface.ilDBInterface.php:30
$res
$res
Definition: ltiservices.php:69
$lng
global $lng
Definition: privfeed.php:31
$DIC
global $DIC
Definition: shib_login.php:26