ILIAS  release_6 Revision v6.24-5-g0c8bfefb3b8
class.ilSCORMTrackingItems.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.ilObjSCORMLearningModule.php';
5
13class ilSCORMTrackingItems
14{
15 public function scoTitlesForExportSelected($obj_id)
16 {
17 global $DIC;
18 $ilDB = $DIC['ilDB'];
19 $scoTitles = array();
20
21 $query = 'SELECT obj_id, title
22 FROM scorm_object
23 WHERE slm_id = %s AND c_type = %s';
24 $res = $ilDB->queryF(
25 $query,
26 array('integer', 'text'),
27 array($obj_id,'sit')
28 );
29 while ($row = $ilDB->fetchAssoc($res)) {
30 $scoTitles[$row['obj_id']] = $row['title'];
31 }
32 return $scoTitles;
33 }
34
35
36 public function markedLearningStatusForExportSelected($a_scos, $obj_id)
37 {
38 global $DIC;
39 $lng = $DIC['lng'];
40 include_once 'Services/Object/classes/class.ilObjectLP.php';
41 $olp = ilObjectLP::getInstance($obj_id);
42 $collection = $olp->getCollectionInstance();
43
44 foreach ($a_scos as $sco_id => $value) {
45 if ($collection && $collection->isAssignedEntry($sco_id)) {
46 $a_scos[$sco_id] = $lng->txt('yes');
47 } else {
48 $a_scos[$sco_id] = $lng->txt('no');
49 }
50 }
51 return $a_scos;
52 }
53
54 public static function userDataArrayForExport($user, $b_allowExportPrivacy = false)
55 {
56 $userArray = array();
57 if ($b_allowExportPrivacy == false) {
58 $userArray["user"] = $user;
59 } else {
60 global $DIC;
61 $ilUser = $DIC['ilUser'];
62 $userArray["login"] = "";
63 $userArray["user"] = "";
64 $userArray["email"] = "";
65 $userArray["department"] = "";
66 if (ilObject::_exists($user) && ilObject::_lookUpType($user) == 'usr') {
67 $e_user = new ilObjUser($user);
68 $userArray["login"] = $e_user->getLogin();
69 $userArray["user"] = $e_user->getLastname() . ', ' . $e_user->getFirstname();
70 $userArray["email"] = "" . $e_user->getEmail();
71 $userArray["department"] = "" . $e_user->getDepartment();
72 }
73 }
74 return $userArray;
75 }
76
77
78 public function getScormTrackingValue($obj_id, $a_user = array(), $a_sco = array(), $a_empty = array(), $lvalue)
79 {
80 global $DIC;
81 $ilDB = $DIC['ilDB'];
82
83 $query = 'SELECT user_id, sco_id, rvalue '
84 . 'FROM scorm_tracking '
85 . 'WHERE obj_id = %s '
86 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
87 . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
88 . 'AND lvalue=%s';
89 $res = $ilDB->queryF(
90 $query,
91 array('integer','text'),
92 array($obj_id,$lvalue)
93 );
94 while ($data = $ilDB->fetchAssoc($res)) {
95 if (!is_null($data['rvalue'])) {
96 $a_empty[$data['user_id']][$data['sco_id']] = $data['rvalue'];
97 }
98 }
99 return $a_empty;
100 }
101
102 public function getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user = array(), $a_sco = array(), $lvalue, $counter, $topic)
103 {
104 global $DIC;
105 $ilDB = $DIC['ilDB'];
106 $a_return = array();
107 $query = 'SELECT user_id, sco_id, rvalue '
108 . 'FROM scorm_tracking '
109 . 'WHERE obj_id = %s '
110 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
111 . 'AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
112 . 'AND lvalue = %s';
113 $res = $ilDB->queryF(
114 $query,
115 array('integer','text'),
116 array($obj_id,'cmi.' . $topic . '.' . $counter . '.' . $lvalue)
117 );
118 while ($data = $ilDB->fetchAssoc($res)) {
119 if (!is_null($data['rvalue'])) {
120 $a_return['' . $data['user_id'] . '-' . $data['sco_id'] . '-' . $counter] = $data['rvalue'];
121 }
122 }
123 return $a_return;
124 }
125
126 public static function exportSelectedRawColumns()
127 {
128 global $DIC;
129 $lng = $DIC['lng'];
130 $lng->loadLanguageModule("scormtrac");
131 // default fields
132 $cols = array();
133 $udh = self::userDataHeaderForExport();
134 $a_cols = explode(
135 ',',
136 'lm_id,lm_title,identifierref,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
137 . ',c_timestamp,lvalue,rvalue'
138 );
139 $a_true = explode(',', $udh["default"] . ",identifierref,c_timestamp,lvalue,rvalue");
140 for ($i = 0;$i < count($a_cols);$i++) {
141 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
142 }
143 for ($i = 0;$i < count($a_true);$i++) {
144 $cols[$a_true[$i]]["default"] = true;
145 }
146 return $cols;
147 }
148
149 public function exportSelectedRaw($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
150 {
151 global $DIC;
152 $ilDB = $DIC['ilDB'];
153 $lng = $DIC['lng'];
154 $lng->loadLanguageModule("scormtrac");
155
156 $returnData = array();
157
158 $scoTitles = self::scoTitlesForExportSelected($obj_id);
159
160 $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
161
162 $query = 'SELECT user_id, st.obj_id, sco_id, identifierref, c_timestamp, lvalue, rvalue '
163 . 'FROM scorm_tracking st '
164 . 'JOIN sc_item si ON st.sco_id = si.obj_id '
165 . 'WHERE ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
166 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
167// . 'AND st.obj_id = '.$ilDB->quote($this->getId(),'integer') .' '
168 . 'ORDER BY ';
169 if ($b_orderBySCO) {
170 $query .= 'sco_id, user_id';
171 } else {
172 $query .= 'user_id, sco_id';
173 }
174 $res = $ilDB->query($query);
175 while ($data = $ilDB->fetchAssoc($res)) {
176 $data["lm_id"] = $obj_id;
177 $data["lm_title"] = $lmTitle;
178 $data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
179 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
180 $data["sco_title"] = $scoTitles[$data["sco_id"]];
181 $data["rvalue"] = "" . $data["rvalue"];
182 // $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
183 $returnData[] = $data;
184 }
185
186 return $returnData;
187 }
188
189 public static function exportSelectedCoreColumns($b_orderBySCO, $b_allowExportPrivacy)
190 {
191 global $DIC;
192 $lng = $DIC['lng'];
193 $lng->loadLanguageModule("scormtrac");
194 // default fields
195 $cols = array();
196 $udh = self::userDataHeaderForExport();
197 $a_cols = explode(
198 ',',
199 'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
200 . ',lesson_status,credit,c_entry,c_exit,c_max,c_min,c_raw,session_time,total_time,c_timestamp,suspend_data,launch_data'
201 );
202 $a_true = explode(',', $udh["default"] . ",sco_title,lesson_status");
203 for ($i = 0;$i < count($a_cols);$i++) {
204 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
205 }
206 for ($i = 0;$i < count($a_true);$i++) {
207 $cols[$a_true[$i]]["default"] = true;
208 }
209 return $cols;
210 }
211
212 public function exportSelectedCore($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
213 {
214 global $DIC;
215 $ilDB = $DIC['ilDB'];
216 $lng = $DIC['lng'];
217 $lng->loadLanguageModule("scormtrac");
218
219 $returnData = array();
220
221 $scoTitles = self::scoTitlesForExportSelected($obj_id);
222
223 $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
224
225 //data-arrays to fill for all users
226 $a_empty = array();
227 for ($i = 0; $i < count($a_user); $i++) {
228 $a_empty[$a_user[$i]] = array();
229 }
230
231 $dbdata = array();
232 $query = 'SELECT user_id, sco_id, max(c_timestamp) as c_timestamp '
233 . 'FROM scorm_tracking '
234 . 'WHERE ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
235 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
236 . 'GROUP BY user_id, sco_id '
237 . 'ORDER BY ';
238 if ($b_orderBySCO) {
239 $query .= 'sco_id, user_id';
240 } else {
241 $query .= 'user_id, sco_id';
242 }
243 $res = $ilDB->query($query);
244 while ($row = $ilDB->fetchAssoc($res)) {
245 $dbdata[] = $row;
246 $a_empty[$row["user_id"]][$row["sco_id"]] = "";
247 }
248
249 $a_lesson_status = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.lesson_status');
250 $a_credit = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.credit');
251 $a_c_entry = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.entry');
252 $a_c_exit = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.exit');
253 $a_c_max = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.max');
254 $a_c_min = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.min');
255 $a_c_raw = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.score.raw');
256 $a_session_time = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.session_time');
257 $a_total_time = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.core.total_time');
258 $a_suspend_data = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.suspend_data');
259 $a_launch_data = self::getScormTrackingValue($obj_id, $a_user, $a_sco, $a_empty, 'cmi.launch_data');
260
261 foreach ($dbdata as $data) {
262 $data["lm_id"] = $obj_id;
263 $data["lm_title"] = $lmTitle;
264
265 $data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
266
267 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
268 $data["sco_title"] = $scoTitles[$data["sco_id"]];
269
270 // $data["audio_captioning"] = "".$data["audio_captioning"];
271 // $data["audio_level"] = "".$data["audio_level"];
272 $data["lesson_status"] = $a_lesson_status[$data['user_id']][$data['sco_id']];
273 $data["credit"] = $a_credit[$data['user_id']][$data['sco_id']];
274 // $data["delivery_speed"] = "".$data["delivery_speed"];
275 $data["c_entry"] = $a_c_entry[$data['user_id']][$data['sco_id']];
276 $data["c_exit"] = $a_c_exit[$data['user_id']][$data['sco_id']];
277 // $data["c_language"] = "".$data["c_language"];
278 // $data["c_location"] = "".str_replace('"','',$data["c_location"]);
279 // $data["c_mode"] = "".$data["c_mode"];
280 $data["c_max"] = $a_c_max[$data['user_id']][$data['sco_id']];
281 $data["c_min"] = $a_c_min[$data['user_id']][$data['sco_id']];
282 $data["c_raw"] = $a_c_raw[$data['user_id']][$data['sco_id']];
283 $data["session_time"] = $a_session_time[$data['user_id']][$data['sco_id']];
284 // $data["session_time_seconds"] = "";
285 // if ($data["session_time"] != "") $data["session_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["session_time"])/100);
286 $data["total_time"] = $a_total_time[$data['user_id']][$data['sco_id']];
287 // $data["total_time_seconds"] = "";
288 // if ($data["total_time"] != "") $data["total_time_seconds"] = round(ilObjSCORM2004LearningModule::_ISODurationToCentisec($data["total_time"])/100);
289 $data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
290 $data["suspend_data"] = $a_suspend_data[$data['user_id']][$data['sco_id']];
291 $data["launch_data"] = $a_launch_data[$data['user_id']][$data['sco_id']];
292 $returnData[] = $data;
293 }
294
295 return $returnData;
296 }
297
298 public static function exportSelectedInteractionsColumns()
299 {
300 global $DIC;
301 $lng = $DIC['lng'];
302 $lng->loadLanguageModule("scormtrac");
303 $cols = array();
304 $udh = self::userDataHeaderForExport();
305 $a_cols = explode(
306 ',',
307 'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
308 . ',counter,id,weighting,type,result,student_response,latency,time,c_timestamp'
309 );//,latency_seconds
310 $a_true = explode(',', $udh["default"] . ",sco_title,id,result,student_response");
311 for ($i = 0;$i < count($a_cols);$i++) {
312 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
313 }
314 for ($i = 0;$i < count($a_true);$i++) {
315 $cols[$a_true[$i]]["default"] = true;
316 }
317 return $cols;
318 }
319
320 public function exportSelectedInteractions($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
321 {
322 global $DIC;
323 $ilDB = $DIC['ilDB'];
324
325 $returnData = array();
326
327 $scoTitles = self::scoTitlesForExportSelected($obj_id);
328
329 $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
330
331 $dbdata = array();
332
333 $interactionsCounter = array();
334 $prevcounter = -1;
335
336 $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
337 . 'FROM scorm_tracking '
338 . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
339 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
340 . 'AND left(lvalue,17) = %s '
341 . 'ORDER BY ';
342 if ($b_orderBySCO) {
343 $query .= 'sco_id, user_id, lvalue';
344 } else {
345 $query .= 'user_id, sco_id, lvalue';
346 }
347 $res = $ilDB->queryF(
348 $query,
349 array('integer','text'),
350 array($obj_id,'cmi.interactions.')
351 );
352
353 while ($row = $ilDB->fetchAssoc($res)) {
354 $tmpar = explode('.', $row["lvalue"]);
355 $tmpcounter = $tmpar[2];
356 if (in_array($tmpcounter, $interactionsCounter) == false) {
357 $interactionsCounter[] = $tmpcounter;
358 }
359 if ($tmpcounter != $prevcounter) {
360 $tmpar = array();
361 $tmpar["user_id"] = $row["user_id"];
362 $tmpar["sco_id"] = $row["sco_id"];
363 $tmpar["counter"] = $tmpcounter;
364 $tmpar["id"] = "";
365 $tmpar["weighting"] = "";
366 $tmpar["type"] = "";
367 $tmpar["result"] = "";
368 $tmpar["student_response"] = "";
369 $tmpar["latency"] = "";
370 $tmpar["time"] = "";
371 $tmpar["c_timestamp"] = $row["c_timestamp"];
372 $dbdata[] = $tmpar;
373 $prevcounter = $tmpcounter;
374 }
375 }
376 // id,weighting,type,result,student_response,latency,time
377
378 $a_id = array();
379 $a_weighting = array();
380 $a_type = array();
381 $a_result = array();
382 $a_student_response = array();
383 $a_latency = array();
384 $a_time = array();
385 for ($i = 0;$i < count($interactionsCounter);$i++) {
386 $a_id = array_merge($a_id, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'id', $interactionsCounter[$i], 'interactions'));
387 $a_weighting = array_merge($a_weighting, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'weighting', $interactionsCounter[$i], 'interactions'));
388 $a_type = array_merge($a_type, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'type', $interactionsCounter[$i], 'interactions'));
389 $a_result = array_merge($a_result, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'result', $interactionsCounter[$i], 'interactions'));
390 $a_student_response = array_merge($a_student_response, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'student_response', $interactionsCounter[$i], 'interactions'));
391 $a_latency = array_merge($a_latency, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'latency', $interactionsCounter[$i], 'interactions'));
392 $a_time = array_merge($a_time, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'time', $interactionsCounter[$i], 'interactions'));
393 }
394 foreach ($dbdata as $data) {
395 $data["lm_id"] = $obj_id;
396 $data["lm_title"] = $lmTitle;
397
398 $data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
399
400 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
401 $data["sco_title"] = $scoTitles[$data["sco_id"]];
402
403 $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
404 if (array_key_exists($combinedId, $a_id)) {
405 $data["id"] = $a_id[$combinedId];
406 }
407 if (array_key_exists($combinedId, $a_weighting)) {
408 $data["weighting"] = $a_weighting[$combinedId];
409 }
410 if (array_key_exists($combinedId, $a_type)) {
411 $data["type"] = $a_type[$combinedId];
412 }
413 if (array_key_exists($combinedId, $a_result)) {
414 $data["result"] = $a_result[$combinedId];
415 }
416 if (array_key_exists($combinedId, $a_student_response)) {
417 $data["student_response"] = $a_student_response[$combinedId];
418 }
419 if (array_key_exists($combinedId, $a_latency)) {
420 $data["latency"] = $a_latency[$combinedId];
421 }
422 if (array_key_exists($combinedId, $a_time)) {
423 $data["time"] = $a_time[$combinedId];
424 }
425
426 //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
427 $returnData[] = $data;
428 }
429
430 // var_dump($returnData);
431 return $returnData;
432 }
433 /*
434 */
435 public static function exportSelectedObjectivesColumns()
436 {
437 global $DIC;
438 $lng = $DIC['lng'];
439 $lng->loadLanguageModule("scormtrac");
440 $cols = array();
441 $udh = self::userDataHeaderForExport();
442 $a_cols = explode(
443 ',',
444 'lm_id,lm_title,sco_id,sco_marked_for_learning_progress,sco_title,' . $udh["cols"]
445 . ',counter,id,c_max,c_min,c_raw,ostatus,c_timestamp'
446 );
447 $a_true = explode(',', $udh["default"] . ",sco_title,id,c_raw,ostatus");
448 for ($i = 0;$i < count($a_cols);$i++) {
449 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
450 }
451 for ($i = 0;$i < count($a_true);$i++) {
452 $cols[$a_true[$i]]["default"] = true;
453 }
454 return $cols;
455 }
456
457 public function exportSelectedObjectives($a_user = array(), $a_sco = array(), $b_orderBySCO = false, $allowExportPrivacy = false, $obj_id, $lmTitle)
458 {
459 global $DIC;
460 $ilDB = $DIC['ilDB'];
461
462 $returnData = array();
463
464 $scoTitles = self::scoTitlesForExportSelected($obj_id);
465
466 $scoProgress = self::markedLearningStatusForExportSelected($scoTitles, $obj_id);
467
468 $dbdata = array();
469
470 $objectivesCounter = array();
471 $prevcounter = -1;
472
473 $query = 'SELECT user_id, sco_id, lvalue, c_timestamp '
474 . 'FROM scorm_tracking '
475 . 'WHERE obj_id = %s AND ' . $ilDB->in('sco_id', $a_sco, false, 'integer') . ' '
476 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
477 . 'AND left(lvalue,15) = %s '
478 . 'ORDER BY ';
479 if ($b_orderBySCO) {
480 $query .= 'sco_id, user_id, lvalue';
481 } else {
482 $query .= 'user_id, sco_id, lvalue';
483 }
484 $res = $ilDB->queryF(
485 $query,
486 array('integer','text'),
487 array($obj_id,'cmi.objectives.')
488 );
489
490 while ($row = $ilDB->fetchAssoc($res)) {
491 $tmpar = explode('.', $row["lvalue"]);
492 $tmpcounter = $tmpar[2];
493 if (in_array($tmpcounter, $objectivesCounter) == false) {
494 $objectivesCounter[] = $tmpcounter;
495 }
496 if ($tmpcounter != $prevcounter) {
497 $tmpar = array();
498 $tmpar["user_id"] = $row["user_id"];
499 $tmpar["sco_id"] = $row["sco_id"];
500 $tmpar["counter"] = $tmpcounter;
501 $tmpar["id"] = "";
502 $tmpar["c_max"] = "";
503 $tmpar["c_min"] = "";
504 $tmpar["c_raw"] = "";
505 $tmpar["ostatus"] = "";
506 $tmpar["c_timestamp"] = $row["c_timestamp"];
507 $dbdata[] = $tmpar;
508 $prevcounter = $tmpcounter;
509 }
510 }
511 $a_id = array();
512 $a_c_max = array();
513 $a_c_min = array();
514 $a_c_raw = array();
515 $a_status = array();
516 for ($i = 0;$i < count($objectivesCounter);$i++) {
517 $a_id = array_merge($a_id, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'id', $objectivesCounter[$i], 'objectives'));
518 $a_c_max = array_merge($a_c_max, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.max', $objectivesCounter[$i], 'objectives'));
519 $a_c_min = array_merge($a_c_min, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.min', $objectivesCounter[$i], 'objectives'));
520 $a_c_raw = array_merge($a_c_raw, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'score.raw', $objectivesCounter[$i], 'objectives'));
521 $a_status = array_merge($a_status, self::getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user, $a_sco, 'status', $objectivesCounter[$i], 'objectives'));
522 }
523 foreach ($dbdata as $data) {
524 $data["lm_id"] = $obj_id;
525 $data["lm_title"] = $lmTitle;
526
527 $data = array_merge($data, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
528
529 $data["sco_marked_for_learning_progress"] = $scoProgress[$data["sco_id"]];
530 $data["sco_title"] = $scoTitles[$data["sco_id"]];
531
532 $combinedId = '' . $data["user_id"] . '-' . $data["sco_id"] . '-' . $data["counter"];
533 if (array_key_exists($combinedId, $a_id)) {
534 $data["id"] = $a_id[$combinedId];
535 }
536 if (array_key_exists($combinedId, $a_c_max)) {
537 $data["c_max"] = $a_c_max[$combinedId];
538 }
539 if (array_key_exists($combinedId, $a_c_min)) {
540 $data["c_min"] = $a_c_min[$combinedId];
541 }
542 if (array_key_exists($combinedId, $a_c_raw)) {
543 $data["c_raw"] = $a_c_raw[$combinedId];
544 }
545 if (array_key_exists($combinedId, $a_status)) {
546 $data["ostatus"] = $a_status[$combinedId];
547 }
548
549 //$data["c_timestamp"] = $data["c_timestamp"];//ilDatePresentation::formatDate(new ilDateTime($data["c_timestamp"],IL_CAL_UNIX));
550 $returnData[] = $data;
551 }
552
553 // var_dump($returnData);
554 return $returnData;
555 }
556
557 public static function exportSelectedSuccessColumns()
558 {
559 global $DIC;
560 $lng = $DIC['lng'];
561 $lng->loadLanguageModule("scormtrac");
562 // default fields
563 $cols = array();
564
565 $udh = self::userDataHeaderForExport();
566 $a_cols = explode(',', 'LearningModuleId,LearningModuleTitle,LearningModuleVersion,' . $udh["cols"]
567 . ',status,Percentage,Attempts,existingSCOs,startedSCOs,completedSCOs,passedSCOs,roundedTotal_timeSeconds,offline_mode,last_access');
568 $a_true = explode(',', $udh["default"] . ",LearningModuleTitle,status,Percentage,Attempts");
569
570 for ($i = 0;$i < count($a_cols);$i++) {
571 $cols[$a_cols[$i]] = array("txt" => $lng->txt($a_cols[$i]),"default" => false);
572 }
573 for ($i = 0;$i < count($a_true);$i++) {
574 $cols[$a_true[$i]]["default"] = true;
575 }
576 return $cols;
577 }
578
579 public function exportSelectedSuccessRows($a_user = array(), $allowExportPrivacy = false, $dbdata = array(), $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id, $lmTitle)
580 {
581 $returnData = array();
582 foreach ($dbdata as $data) {
583 $dat = array();
584 $dat["LearningModuleId"] = $obj_id;
585 $dat["LearningModuleTitle"] = "" . $lmTitle;
586 $dat["LearningModuleVersion"] = "" . $data["module_version"];
587
588 $dat = array_merge($dat, self::userDataArrayForExport($data["user_id"], $allowExportPrivacy));
589
590 $dat["status"] = "" . $data["status"];
591 $dat["Percentage"] = "" . $data["percentage_completed"];
592 $dat["Attempts"] = "" . $data["package_attempts"];
593 $dat["existingSCOs"] = "" . $scoCounter;
594 $dat["startedSCOs"] = "" . $u_startedSCO[$data["user_id"]];
595 $dat["completedSCOs"] = "" . $u_completedSCO[$data["user_id"]];
596 $dat["passedSCOs"] = "" . $u_passedSCO[$data["user_id"]];
597 $dat["roundedTotal_timeSeconds"] = "" . $data["sco_total_time_sec"];
598 if (is_null($data["offline_mode"])) {
599 $dat["offline_mode"] = "";
600 } else {
601 $dat["offline_mode"] = $data["offline_mode"];
602 }
603 $dat["last_access"] = "" . $data["last_access"];
604 $returnData[] = $dat;
605 }
606 return $returnData;
607 }
608
609 public function exportSelectedSuccess($a_user = array(), $allowExportPrivacy = false, $obj_id, $lmTitle)
610 {
611 global $DIC;
612 $ilDB = $DIC['ilDB'];
613
614 $scoCounter = 0;
615 $query = 'SELECT count(distinct(scorm_object.obj_id)) counter '
616 . 'FROM scorm_object, sc_item, sc_resource '
617 . 'WHERE scorm_object.slm_id = %s '
618 . 'AND scorm_object.obj_id = sc_item.obj_id '
619 . 'AND sc_item.identifierref = sc_resource.import_id '
620 . 'AND (sc_resource.scormtype = %s OR sc_resource.scormtype is null)';
621 $res = $ilDB->queryF(
622 $query,
623 array('integer', 'text'),
624 array($obj_id,'sco')
625 );
626 while ($row = $ilDB->fetchAssoc($res)) {
627 $scoCounter = $row['counter'];
628 }
629
630 //data-arrays for all users
631 $u_startedSCO = array();
632 $u_completedSCO = array();
633 $u_passedSCO = array();
634 for ($i = 0; $i < count($a_user); $i++) {
635 $u_startedSCO[$a_user[$i]] = 0;
636 $u_completedSCO[$a_user[$i]] = 0;
637 $u_passedSCO[$a_user[$i]] = 0;
638 }
639
640 $query = 'SELECT user_id, count(distinct(SCO_ID)) counter '
641 . 'FROM scorm_tracking '
642 . 'WHERE obj_id = %s '
643 . 'AND SCO_ID > 0 '
644 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
645 . 'GROUP BY user_id';
646 $res = $ilDB->queryF(
647 $query,
648 array('integer'),
649 array($obj_id)
650 );
651 while ($data = $ilDB->fetchAssoc($res)) {
652 $u_startedSCO[$data['user_id']] = $data['counter'];
653 }
654
655 $query = 'SELECT user_id, count(*) counter '
656 . 'FROM scorm_tracking '
657 . 'WHERE obj_id = %s AND lvalue = %s AND rvalue like %s '
658 . 'AND ' . $ilDB->in('user_id', $a_user, false, 'integer') . ' '
659 . 'GROUP BY user_id';
660 $res = $ilDB->queryF(
661 $query,
662 array('integer','text','text'),
663 array($obj_id,'cmi.core.lesson_status','completed')
664 );
665 while ($data = $ilDB->fetchAssoc($res)) {
666 $u_completedSCO[$data['user_id']] = $data['counter'];
667 }
668
669 $res = $ilDB->queryF(
670 $query,
671 array('integer','text','text'),
672 array($obj_id,'cmi.core.lesson_status','passed')
673 );
674 while ($data = $ilDB->fetchAssoc($res)) {
675 $u_passedSCO[$data['user_id']] = $data['counter'];
676 }
677
678 $dbdata = array();
679
680 $query = 'SELECT * FROM sahs_user WHERE obj_id = ' . $ilDB->quote($obj_id, 'integer')
681 . ' AND ' . $ilDB->in('user_id', $a_user, false, 'integer')
682 . ' ORDER BY user_id';
683 $res = $ilDB->query($query);
684 while ($row = $ilDB->fetchAssoc($res)) {
685 $dbdata[] = $row;
686 }
687 return $this->exportSelectedSuccessRows($a_user, $allowExportPrivacy, $dbdata, $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id, $lmTitle);
688 }
689
690 public static function userDataHeaderForExport()
691 {
692 include_once('./Services/PrivacySecurity/classes/class.ilPrivacySettings.php');
693 $privacy = ilPrivacySettings::_getInstance();
694 $allowExportPrivacy = $privacy->enabledExportSCORM();
695 $returnData = array();
696 if ($allowExportPrivacy == true) {
697 $returnData["cols"] = 'login,user,email,department';
698 } else {
699 $returnData["cols"] = 'user';
700 }
701 $returnData["default"] = 'user';
702 return $returnData;
703 }
704
705
706 public function SCORMTimeToSeconds($a_time)
707 {
708 if ($a_time == "") {
709 return "";
710 }
711 $tarr = explode(":", $a_time);
712 // $sec = (int) $tarr[2] + (int) $tarr[1] * 60 + (int) substr($tarr[0], strlen($tarr[0]) - 3) * 3600;
713 if (count($tarr) != 3 || is_nan($tarr[0]) || is_nan($tarr[1]) || is_nan($tarr[2])) {
714 return "";
715 }
716 $csec = (int) $tarr[0] * 360000 + (int) $tarr[1] * 6000 + $tarr[2] * 100;
717 return round($csec / 100);
718 }
719}
php
An exception for terminatinating execution or to throw for unit testing.
ilObjectLP\getInstance
static getInstance($a_obj_id)
Definition: class.ilObjectLP.php:45
ilObject\_exists
static _exists($a_id, $a_reference=false, $a_type=null)
checks if an object exists in object_data@access public
Definition: class.ilObject.php:1675
ilPrivacySettings\_getInstance
static _getInstance()
Get instance of ilPrivacySettings.
Definition: class.ilPrivacySettings.php:89
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
Definition: class.ilSCORMTrackingItems.php:14
ilSCORMTrackingItems\exportSelectedObjectives
exportSelectedObjectives($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:457
ilSCORMTrackingItems\exportSelectedSuccess
exportSelectedSuccess($a_user=array(), $allowExportPrivacy=false, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:609
ilSCORMTrackingItems\exportSelectedSuccessRows
exportSelectedSuccessRows($a_user=array(), $allowExportPrivacy=false, $dbdata=array(), $scoCounter, $u_startedSCO, $u_completedSCO, $u_passedSCO, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:579
ilSCORMTrackingItems\getScormTrackingValueForInteractionsOrObjectives
getScormTrackingValueForInteractionsOrObjectives($obj_id, $a_user=array(), $a_sco=array(), $lvalue, $counter, $topic)
Definition: class.ilSCORMTrackingItems.php:102
ilSCORMTrackingItems\exportSelectedCore
exportSelectedCore($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:212
ilSCORMTrackingItems\exportSelectedRaw
exportSelectedRaw($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:149
ilSCORMTrackingItems\exportSelectedCoreColumns
static exportSelectedCoreColumns($b_orderBySCO, $b_allowExportPrivacy)
Definition: class.ilSCORMTrackingItems.php:189
ilSCORMTrackingItems\userDataArrayForExport
static userDataArrayForExport($user, $b_allowExportPrivacy=false)
Definition: class.ilSCORMTrackingItems.php:54
ilSCORMTrackingItems\markedLearningStatusForExportSelected
markedLearningStatusForExportSelected($a_scos, $obj_id)
Definition: class.ilSCORMTrackingItems.php:36
ilSCORMTrackingItems\getScormTrackingValue
getScormTrackingValue($obj_id, $a_user=array(), $a_sco=array(), $a_empty=array(), $lvalue)
Definition: class.ilSCORMTrackingItems.php:78
ilSCORMTrackingItems\exportSelectedInteractions
exportSelectedInteractions($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false, $obj_id, $lmTitle)
Definition: class.ilSCORMTrackingItems.php:320
$i
$i
Definition: metadata.php:24
$query
$query
Definition: proxy_ylocal.php:13
$lng
$lng
Definition: save_question_post_data.php:23
$res
foreach($_POST as $key=> $value) $res
Definition: save_question_post_data.php:15
$ilDB
global $ilDB
Definition: storeScorm2004.php:19
$data
$data
Definition: storeScorm.php:23
$ilUser
$ilUser
Definition: imgupload.php:18
$a_type
$a_type
Definition: workflow.php:92
$DIC
$DIC
Definition: xapitoken.php:46
$cols
$cols
Definition: xhr_table.php:11