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