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