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