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