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 
4 include_once './Modules/ScormAicc/classes/class.ilSCORMTrackingItems.php';
5 include_once './Modules/Scorm2004/classes/class.ilObjSCORM2004LearningModule.php';
13 class 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 }
ilSCORM2004TrackingItems\tracInteractionUserAnswers
tracInteractionUserAnswers($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:644
ilSCORM2004TrackingItems
Class ilSCORM2004TrackingItems.
Definition: class.ilSCORM2004TrackingItems.php:13
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\tracInteractionItemColumns
tracInteractionItemColumns($b_orderBySCO, $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:344
ilSCORM2004TrackingItems\exportSelectedObjectives
exportSelectedObjectives($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:226
ilObjSCORM2004LearningModule\_ISODurationToCentisec
_ISODurationToCentisec($str)
convert ISO 8601 Timeperiods to centiseconds ta
Definition: class.ilObjSCORM2004LearningModule.php:670
ilSCORM2004TrackingItems\tracInteractionItem
tracInteractionItem($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:364
$data
$data
Definition: example_011.php:126
ilSCORM2004TrackingItems\tracInteractionUserColumns
tracInteractionUserColumns($b_orderBySCO, $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:434
$lng
global $lng
Definition: privfeed.php:40
$ilDB
global $ilDB
Definition: storeScorm2004.php:16
ilSCORM2004TrackingItems\exportObjGlobalToSystem
exportObjGlobalToSystem($a_user=array(), $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:302
ilSCORM2004TrackingItems\exportSelectedCore
exportSelectedCore($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:58
ilSCORM2004TrackingItems\tracInteractionUser
tracInteractionUser($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:462
ilSCORMTrackingItems
Class ilSCORMTrackingItems.
Definition: class.ilSCORMTrackingItems.php:13
ilSCORM2004TrackingItems\exportSelectedInteractions
exportSelectedInteractions($a_user=array(), $a_sco=array(), $b_orderBySCO=false, $allowExportPrivacy=false)
Definition: class.ilSCORM2004TrackingItems.php:151
ilSCORM2004TrackingItems\tracInteractionUserAnswersColumns
tracInteractionUserAnswersColumns($a_user=array(), $a_sco=array(), $b_orderBySCO, $b_allowExportPrivacy)
Definition: class.ilSCORM2004TrackingItems.php:589