ILIAS  release_4-4 Revision
class.ilTestExport.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 require_once './Modules/Test/classes/inc.AssessmentConstants.php';
5 require_once './Services/Utilities/classes/class.ilFormat.php';
6 
18 {
20  var $err; // error object
21 
23  var $db; // database object
24 
26  var $ilias; // ilias object
27 
29  var $test_obj; // test object
30 
31  var $inst_id; // installation id
32  var $mode;
33 
35  private $lng;
36 
37  private $resultsfile;
38 
42  public function __construct(&$a_test_obj, $a_mode = "xml")
43  {
44  global $ilErr, $ilDB, $ilias, $lng;
45 
46  $this->test_obj =& $a_test_obj;
47 
48  $this->err =& $ilErr;
49  $this->ilias =& $ilias;
50  $this->db =& $ilDB;
51  $this->mode = $a_mode;
52  $this->lng =& $lng;
53 
54  $this->inst_id = IL_INST_ID;
55 
56  $date = time();
57  $this->export_dir = $this->test_obj->getExportDirectory();
58  switch($this->mode)
59  {
60  case "results":
61  $this->subdir = $date."__".$this->inst_id."__".
62  "tst__results_".$this->test_obj->getId();
63  break;
64  case "aggregated":
65  $this->subdir = $date."__".$this->inst_id."__".
66  "test__aggregated__results_".$this->test_obj->getId();
67  break;
68  default:
69  $this->subdir = $date."__".$this->inst_id."__".
70  "tst"."_".$this->test_obj->getId();
71  $this->filename = $this->subdir.".xml";
72  $this->resultsfile = $date."__".$this->inst_id."__".
73  "results"."_".$this->test_obj->getId().".xml";
74  $this->qti_filename = $date."__".$this->inst_id."__".
75  "qti"."_".$this->test_obj->getId().".xml";
76  break;
77  }
78  $this->filename = $this->subdir.".".$this->getExtension();
79  }
80 
81  function getExtension () {
82  switch ($this->mode) {
83  case "results":
84  return "csv"; break;
85  default:
86  return "xml"; break;
87  }
88  }
89 
90  function getInstId()
91  {
92  return $this->inst_id;
93  }
94 
95 
102  function buildExportFile()
103  {
104  switch ($this->mode)
105  {
106  case "results":
107  return $this->buildExportResultFile();
108  break;
109  default:
110  return $this->buildExportFileXML();
111  break;
112  }
113  }
114 
119  {
120  global $ilBench;
121  global $log;
122 
123  //get Log File
124  $expDir = $this->test_obj->getExportDirectory();
125 
126  // make_directories
127  $this->test_obj->createExportDirectory();
128  include_once "./Services/Utilities/classes/class.ilUtil.php";
129  ilUtil::makeDir($this->export_dir);
130 
131  $expLog = new ilLog($expDir, "export.log");
132  $expLog->delete();
133  $expLog->setLogFormat("");
134  $expLog->write(date("[y-m-d H:i:s] ")."Start Export Of Results");
135 
136  $data = $this->exportToCSV($deliver = FALSE);
137  $file = fopen($this->export_dir."/".$this->filename, "w");
138  fwrite($file, $data);
139  fclose($file);
140 
141  $excelfile = $this->exportToExcel($deliver = FALSE);
142  @copy($excelfile, $this->export_dir . "/" . str_replace($this->getExtension(), "xls", $this->filename));
143  @unlink($excelfile);
144  // end
145  $expLog->write(date("[y-m-d H:i:s] ")."Finished Export of Results");
146 
147  return $this->export_dir."/".$this->filename;
148  }
149 
155  protected function aggregatedResultsToExcel($deliver = TRUE)
156  {
157  $data = $this->test_obj->getAggregatedResultsData();
158  include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
159  $excelfile = ilUtil::ilTempnam();
160  $adapter = new ilExcelWriterAdapter($excelfile, FALSE);
161  $testname = ilUtil::getASCIIFilename(preg_replace("/\s/", "_", $this->test_obj->getTitle() . '_aggregated')) . ".xls";
162  $workbook = $adapter->getWorkbook();
163  $workbook->setVersion(8); // Use Excel97/2000 Format
164  // Creating a worksheet
165  $format_percent =& $workbook->addFormat();
166  $format_percent->setNumFormat("0.00%");
167  $format_title =& $workbook->addFormat();
168  $format_title->setBold();
169  $format_title->setColor('black');
170  $format_title->setPattern(1);
171  $format_title->setFgColor('silver');
172  include_once "./Services/Excel/classes/class.ilExcelUtils.php";
173  $worksheet =& $workbook->addWorksheet(ilExcelUtils::_convert_text($this->lng->txt("tst_results_aggregated")));
174  $row = 0;
175  $col = 0;
176  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("result")), $format_title);
177  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("value")), $format_title);
178  $row++;
179  foreach ($data["overview"] as $key => $value)
180  {
181  $col = 0;
182  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($key));
183  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value));
184  $row++;
185  }
186  $row++;
187  $col = 0;
188  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("question_id")), $format_title);
189  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("question_title")), $format_title);
190  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("average_reached_points")), $format_title);
191  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("points")), $format_title);
192  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("percentage")), $format_title);
193  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("number_of_answers")), $format_title);
194  $row++;
195  foreach ($data["questions"] as $key => $value)
196  {
197  $col = 0;
198  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($key));
199  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value[0]));
200  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value[4]));
201  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value[5]));
202  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value[6]), $format_percent);
203  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($value[3]));
204  $row++;
205  }
206  $workbook->close();
207  if ($deliver)
208  {
209  ilUtil::deliverFile($excelfile, $testname, "application/vnd.ms-excel", false, true);
210  exit;
211  }
212  else
213  {
214  return $excelfile;
215  }
216  }
217 
223  protected function aggregatedResultsToCSV($deliver = TRUE)
224  {
225  $data = $this->test_obj->getAggregatedResultsData();
226  $rows = array();
227  array_push($rows, array(
228  $this->lng->txt("result"),
229  $this->lng->txt("value")
230  ));
231  foreach ($data["overview"] as $key => $value)
232  {
233  array_push($rows, array(
234  $key,
235  $value
236  ));
237  }
238  array_push($rows, array(
239  $this->lng->txt("question_title"),
240  $this->lng->txt("average_reached_points"),
241  $this->lng->txt("points"),
242  $this->lng->txt("percentage"),
243  $this->lng->txt("number_of_answers")
244  ));
245  foreach ($data["questions"] as $key => $value)
246  {
247  array_push($rows, array(
248  $value[0],
249  $value[4],
250  $value[5],
251  $value[6],
252  $value[3]
253  ));
254  }
255  $csv = "";
256  $separator = ";";
257  foreach ($rows as $evalrow)
258  {
259  $csvrow =& $this->test_obj->processCSVRow($evalrow, TRUE, $separator);
260  $csv .= join($csvrow, $separator) . "\n";
261  }
262  if ($deliver)
263  {
264  ilUtil::deliverData($csv, ilUtil::getASCIIFilename($this->test_obj->getTitle() . "_aggregated.csv"));
265  exit;
266  }
267  else
268  {
269  return $csv;
270  }
271  }
272 
283  public function exportToExcel($deliver = TRUE, $filterby = "", $filtertext = "", $passedonly = FALSE)
284  {
285  if (strcmp($this->mode, "aggregated") == 0) return $this->aggregatedResultsToExcel($deliver);
286 
287  require_once './Services/Excel/classes/class.ilExcelWriterAdapter.php';
288  $excelfile = ilUtil::ilTempnam();
289  $adapter = new ilExcelWriterAdapter($excelfile, FALSE);
290  $testname = $this->test_obj->getTitle();
291  switch($this->mode)
292  {
293  case 'results':
294  $testname .= '_results';
295  break;
296  }
297  $testname = ilUtil::getASCIIFilename(preg_replace("/\s/", "_", $testname)) . ".xls";
298  $workbook = $adapter->getWorkbook();
299  $workbook->setVersion(8); // Use Excel97/2000 Format
300  // Creating a worksheet
301  $format_bold =& $workbook->addFormat();
302  $format_bold->setBold();
303  $format_percent =& $workbook->addFormat();
304  $format_percent->setNumFormat("0.00%");
305  $format_datetime =& $workbook->addFormat();
306  $format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
307  $format_title =& $workbook->addFormat();
308  $format_title->setBold();
309  $format_title->setColor('black');
310  $format_title->setPattern(1);
311  $format_title->setFgColor('silver');
312  require_once './Services/Excel/classes/class.ilExcelUtils.php';
313  $worksheet =& $workbook->addWorksheet(ilExcelUtils::_convert_text($this->lng->txt("tst_results")));
314  $additionalFields = $this->test_obj->getEvaluationAdditionalFields();
315  $row = 0;
316  $col = 0;
317 
318  if ($this->test_obj->getAnonymity())
319  {
320  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("counter")), $format_title);
321  }
322  else
323  {
324  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("name")), $format_title);
325  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("login")), $format_title);
326  }
327  if (count($additionalFields))
328  {
329  foreach ($additionalFields as $fieldname)
330  {
331  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt($fieldname)), $format_title);
332  }
333  }
334  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_resultspoints")), $format_title);
335  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("maximum_points")), $format_title);
336  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_resultsmarks")), $format_title);
337  if ($this->test_obj->ects_output)
338  {
339  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("ects_grade")), $format_title);
340  }
341  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_qworkedthrough")), $format_title);
342  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_qmax")), $format_title);
343  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_pworkedthrough")), $format_title);
344  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_timeofwork")), $format_title);
345  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_atimeofwork")), $format_title);
346  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_firstvisit")), $format_title);
347  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_lastvisit")), $format_title);
348 
349  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_mark_median")), $format_title);
350  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_rank_participant")), $format_title);
351  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_rank_median")), $format_title);
352  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_total_participants")), $format_title);
353  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_median")), $format_title);
354  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("scored_pass")), $format_title);
355 
356  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("pass")), $format_title);
357 
358  $counter = 1;
359  $data =& $this->test_obj->getCompleteEvaluationData(TRUE, $filterby, $filtertext);
360  $firstrowwritten = false;
361  foreach ($data->getParticipants() as $active_id => $userdata)
362  {
363  $remove = FALSE;
364  if ($passedonly)
365  {
366  if ($data->getParticipant($active_id)->getPassed() == FALSE)
367  {
368  $remove = TRUE;
369  }
370  }
371  if (!$remove)
372  {
373  $row++;
374  if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions())
375  {
376  $row++;
377  }
378  $col = 0;
379  if ($this->test_obj->getAnonymity())
380  {
381  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($counter));
382  }
383  else
384  {
385  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getName()));
386  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getLogin()));
387  }
388  if (count($additionalFields))
389  {
390  $userfields = ilObjUser::_lookupFields($userdata->getUserID());
391  foreach ($additionalFields as $fieldname)
392  {
393  if (strcmp($fieldname, "gender") == 0)
394  {
395  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("gender_" . $userfields[$fieldname])));
396  }
397  else
398  {
399  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
400  }
401  }
402  }
403  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getReached()));
404  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getMaxpoints()));
405  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getMark()));
406  if ($this->test_obj->ects_output)
407  {
408  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getECTSMark()));
409  }
410  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getQuestionsWorkedThrough()));
411  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getNumberOfQuestions()));
412  $worksheet->write($row, $col++, $data->getParticipant($active_id)->getQuestionsWorkedThroughInPercent() / 100.0, $format_percent);
413  $time = $data->getParticipant($active_id)->getTimeOfWork();
414  $time_seconds = $time;
415  $time_hours = floor($time_seconds/3600);
416  $time_seconds -= $time_hours * 3600;
417  $time_minutes = floor($time_seconds/60);
418  $time_seconds -= $time_minutes * 60;
419  $worksheet->write($row, $col++, ilExcelUtils::_convert_text(sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds)));
420  $time = $data->getParticipant($active_id)->getQuestionsWorkedThrough() ? $data->getParticipant($active_id)->getTimeOfWork() / $data->getParticipant($active_id)->getQuestionsWorkedThrough() : 0;
421  $time_seconds = $time;
422  $time_hours = floor($time_seconds/3600);
423  $time_seconds -= $time_hours * 3600;
424  $time_minutes = floor($time_seconds/60);
425  $time_seconds -= $time_minutes * 60;
426  $worksheet->write($row, $col++, ilExcelUtils::_convert_text(sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds)));
427  $fv = getdate($data->getParticipant($active_id)->getFirstVisit());
428  $firstvisit = ilUtil::excelTime(
429  $fv["year"],
430  $fv["mon"],
431  $fv["mday"],
432  $fv["hours"],
433  $fv["minutes"],
434  $fv["seconds"]
435  );
436  $worksheet->write($row, $col++, $firstvisit, $format_datetime);
437  $lv = getdate($data->getParticipant($active_id)->getLastVisit());
438  $lastvisit = ilUtil::excelTime(
439  $lv["year"],
440  $lv["mon"],
441  $lv["mday"],
442  $lv["hours"],
443  $lv["minutes"],
444  $lv["seconds"]
445  );
446  $worksheet->write($row, $col++, $lastvisit, $format_datetime);
447 
448  $median = $data->getStatistics()->getStatistics()->median();
449  $pct = $data->getParticipant($active_id)->getMaxpoints() ? $median / $data->getParticipant($active_id)->getMaxpoints() * 100.0 : 0;
450  $mark = $this->test_obj->mark_schema->getMatchingMark($pct);
451  $mark_short_name = "";
452  if (is_object($mark))
453  {
454  $mark_short_name = $mark->getShortName();
455  }
456  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($mark_short_name));
457  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->rank($data->getParticipant($active_id)->getReached())));
458  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->rank_median()));
459  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->count()));
460  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($median));
461  if ($this->test_obj->getPassScoring() == SCORE_BEST_PASS)
462  {
463  $worksheet->write($row, $col++, $data->getParticipant($active_id)->getBestPass() + 1);
464  }
465  else
466  {
467  $worksheet->write($row, $col++, $data->getParticipant($active_id)->getLastPass() + 1);
468  }
469  $startcol = $col;
470  for ($pass = 0; $pass <= $data->getParticipant($active_id)->getLastPass(); $pass++)
471  {
472  $col = $startcol;
473  $finishdate = $this->test_obj->getPassFinishDate($active_id, $pass);
474  if ($finishdate > 0)
475  {
476  if ($pass > 0)
477  {
478  $row++;
479  if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions())
480  {
481  $row++;
482  }
483  }
484  $worksheet->write($row, $col++, ilExcelUtils::_convert_text($pass+1));
485  if (is_object($data->getParticipant($active_id)) && is_array($data->getParticipant($active_id)->getQuestions($pass)))
486  {
487  foreach ($data->getParticipant($active_id)->getQuestions($pass) as $question)
488  {
489  $question_data = $data->getParticipant($active_id)->getPass($pass)->getAnsweredQuestionByQuestionId($question["id"]);
490  $worksheet->write($row, $col, ilExcelUtils::_convert_text($question_data["reached"]));
491  if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions())
492  {
493  $worksheet->write($row-1, $col, ilExcelUtils::_convert_text(preg_replace("/<.*?>/", "", $data->getQuestionTitle($question["id"]))), $format_title);
494  }
495  else
496  {
497  if ($pass == 0 && !$firstrowwritten)
498  {
499  $worksheet->write(0, $col, ilExcelUtils::_convert_text(preg_replace("/<.*?>/", "", $data->getQuestionTitle($question["id"]))), $format_title);
500  }
501  }
502  $col++;
503  }
504  $firstrowwritten = true;
505  }
506  }
507  }
508  $counter++;
509  }
510  }
511  if ($this->test_obj->getExportSettingsSingleChoiceShort() && !$this->test_obj->isRandomTest() && $this->test_obj->hasSingleChoiceQuestions())
512  {
513  // special tab for single choice tests
514  $titles =& $this->test_obj->getQuestionTitlesAndIndexes();
515  $positions = array();
516  $pos = 0;
517  $row = 0;
518  foreach ($titles as $id => $title)
519  {
520  $positions[$id] = $pos;
521  $pos++;
522  }
523  $usernames = array();
524  $participantcount = count($data->getParticipants());
525  $allusersheet = false;
526  $pages = 0;
527  $resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users"));
528 
529  $col = 0;
530  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('name')), $format_title);
531  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('login')), $format_title);
532  if (count($additionalFields))
533  {
534  foreach ($additionalFields as $fieldname)
535  {
536  if (strcmp($fieldname, "matriculation") == 0)
537  {
538  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('matriculation')), $format_title);
539  }
540  }
541  }
542  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('test')), $format_title);
543  foreach ($titles as $title)
544  {
545  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($title), $format_title);
546  }
547  $row++;
548 
549  foreach ($data->getParticipants() as $active_id => $userdata)
550  {
551  $username = (!is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName())) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID $active_id";
552  if (array_key_exists($username, $usernames))
553  {
554  $usernames[$username]++;
555  $username .= " ($i)";
556  }
557  else
558  {
559  $usernames[$username] = 1;
560  }
561  $col = 0;
562  $resultsheet->write($row, $col++, $username);
563  $resultsheet->write($row, $col++, $userdata->getLogin());
564  if (count($additionalFields))
565  {
566  $userfields = ilObjUser::_lookupFields($userdata->getUserID());
567  foreach ($additionalFields as $fieldname)
568  {
569  if (strcmp($fieldname, "matriculation") == 0)
570  {
571  if (strlen($userfields[$fieldname]))
572  {
573  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
574  }
575  else
576  {
577  $col++;
578  }
579  }
580  }
581  }
582  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->test_obj->getTitle()));
583  $pass = $userdata->getScoredPass();
584  if (is_object($userdata) && is_array($userdata->getQuestions($pass)))
585  {
586  foreach ($userdata->getQuestions($pass) as $question)
587  {
588  $objQuestion =& $this->test_obj->_instanciateQuestion($question["aid"]);
589  if (is_object($objQuestion) && strcmp($objQuestion->getQuestionType(), 'assSingleChoice') == 0)
590  {
591  $solution = $objQuestion->getSolutionValues($active_id, $pass);
592  $pos = $positions[$question["aid"]];
593  $selectedanswer = "x";
594  foreach ($objQuestion->getAnswers() as $id => $answer)
595  {
596  if (strlen($solution[0]["value1"]) && $id == $solution[0]["value1"])
597  {
598  $selectedanswer = $answer->getAnswertext();
599  }
600  }
601  $resultsheet->write($row, $col+$pos, ilExcelUtils::_convert_text($selectedanswer));
602  }
603  }
604  }
605  $row++;
606  }
607  if ($this->test_obj->isSingleChoiceTestWithoutShuffle())
608  {
609  // special tab for single choice tests without shuffle option
610  $pos = 0;
611  $row = 0;
612  $usernames = array();
613  $allusersheet = false;
614  $pages = 0;
615  $resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users") . " (2)");
616 
617  $col = 0;
618  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('name')), $format_title);
619  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('login')), $format_title);
620  if (count($additionalFields))
621  {
622  foreach ($additionalFields as $fieldname)
623  {
624  if (strcmp($fieldname, "matriculation") == 0)
625  {
626  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('matriculation')), $format_title);
627  }
628  }
629  }
630  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('test')), $format_title);
631  foreach ($titles as $title)
632  {
633  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($title), $format_title);
634  }
635  $row++;
636 
637  foreach ($data->getParticipants() as $active_id => $userdata)
638  {
639  $username = (!is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName())) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID $active_id";
640  if (array_key_exists($username, $usernames))
641  {
642  $usernames[$username]++;
643  $username .= " ($i)";
644  }
645  else
646  {
647  $usernames[$username] = 1;
648  }
649  $col = 0;
650  $resultsheet->write($row, $col++, $username);
651  $resultsheet->write($row, $col++, $userdata->getLogin());
652  if (count($additionalFields))
653  {
654  $userfields = ilObjUser::_lookupFields($userdata->getUserID());
655  foreach ($additionalFields as $fieldname)
656  {
657  if (strcmp($fieldname, "matriculation") == 0)
658  {
659  if (strlen($userfields[$fieldname]))
660  {
661  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
662  }
663  else
664  {
665  $col++;
666  }
667  }
668  }
669  }
670  $resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->test_obj->getTitle()));
671  $pass = $userdata->getScoredPass();
672  if (is_object($userdata) && is_array($userdata->getQuestions($pass)))
673  {
674  foreach ($userdata->getQuestions($pass) as $question)
675  {
676  $objQuestion =& $this->test_obj->_instanciateQuestion($question["aid"]);
677  if (is_object($objQuestion) && strcmp($objQuestion->getQuestionType(), 'assSingleChoice') == 0)
678  {
679  $solution = $objQuestion->getSolutionValues($active_id, $pass);
680  $pos = $positions[$question["aid"]];
681  $selectedanswer = chr(65+$solution[0]["value1"]);
682  $resultsheet->write($row, $col+$pos, ilExcelUtils::_convert_text($selectedanswer));
683  }
684  }
685  }
686  $row++;
687  }
688  }
689  }
690  else
691  {
692  // test participant result export
693  $usernames = array();
694  $participantcount = count($data->getParticipants());
695  $allusersheet = false;
696  $pages = 0;
697  $i = 0;
698  foreach ($data->getParticipants() as $active_id => $userdata)
699  {
700  $i++;
701 
702  $username = (!is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName())) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID $active_id";
703  if (array_key_exists($username, $usernames))
704  {
705  $usernames[$username]++;
706  $username .= " ($i)";
707  }
708  else
709  {
710  $usernames[$username] = 1;
711  }
712  if ($participantcount > 250) {
713  if (!$allusersheet || ($pages-1) < floor($row / 64000)) {
714  $resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users") . (($pages > 0) ? " (".($pages+1).")" : ""));
715  $allusersheet = true;
716  $row = 0;
717  $pages++;
718  }
719  } else {
720  $resultsheet =& $workbook->addWorksheet(utf8_decode($username));
721  }
722  if (method_exists($resultsheet, "writeString"))
723  {
724  $pass = $userdata->getScoredPass();
725  $row = ($allusersheet) ? $row : 0;
726  $resultsheet->writeString($row, 0, ilExcelUtils::_convert_text(sprintf($this->lng->txt("tst_result_user_name_pass"), $pass+1, $userdata->getName())), $format_bold);
727  $row += 2;
728  if (is_object($userdata) && is_array($userdata->getQuestions($pass)))
729  {
730  foreach ($userdata->getQuestions($pass) as $question)
731  {
732  require_once "./Modules/TestQuestionPool/classes/class.assQuestion.php";
733  $question = assQuestion::_instanciateQuestion($question["id"]);
734  if (is_object($question))
735  {
736  $row = $question->setExportDetailsXLS($resultsheet, $row, $active_id, $pass, $format_title, $format_bold);
737  }
738  }
739  }
740  }
741  }
742  }
743  $workbook->close();
744  if ($deliver)
745  {
746  ilUtil::deliverFile($excelfile, $testname, "application/vnd.ms-excel", false, true);
747  exit;
748  }
749  else
750  {
751  return $excelfile;
752  }
753  }
754 
764  function exportToCSV($deliver = TRUE, $filterby = "", $filtertext = "", $passedonly = FALSE)
765  {
766  global $ilLog;
767 
768  if (strcmp($this->mode, "aggregated") == 0) return $this->aggregatedResultsToCSV($deliver);
769 
770  $rows = array();
771  $datarow = array();
772  $col = 1;
773  if ($this->test_obj->getAnonymity())
774  {
775  array_push($datarow, $this->lng->txt("counter"));
776  $col++;
777  }
778  else
779  {
780  array_push($datarow, $this->lng->txt("name"));
781  $col++;
782  array_push($datarow, $this->lng->txt("login"));
783  $col++;
784  }
785  $additionalFields = $this->test_obj->getEvaluationAdditionalFields();
786  if (count($additionalFields))
787  {
788  foreach ($additionalFields as $fieldname)
789  {
790  array_push($datarow, $this->lng->txt($fieldname));
791  $col++;
792  }
793  }
794  array_push($datarow, $this->lng->txt("tst_stat_result_resultspoints"));
795  $col++;
796  array_push($datarow, $this->lng->txt("maximum_points"));
797  $col++;
798  array_push($datarow, $this->lng->txt("tst_stat_result_resultsmarks"));
799  $col++;
800  if ($this->test_obj->ects_output)
801  {
802  array_push($datarow, $this->lng->txt("ects_grade"));
803  $col++;
804  }
805  array_push($datarow, $this->lng->txt("tst_stat_result_qworkedthrough"));
806  $col++;
807  array_push($datarow, $this->lng->txt("tst_stat_result_qmax"));
808  $col++;
809  array_push($datarow, $this->lng->txt("tst_stat_result_pworkedthrough"));
810  $col++;
811  array_push($datarow, $this->lng->txt("tst_stat_result_timeofwork"));
812  $col++;
813  array_push($datarow, $this->lng->txt("tst_stat_result_atimeofwork"));
814  $col++;
815  array_push($datarow, $this->lng->txt("tst_stat_result_firstvisit"));
816  $col++;
817  array_push($datarow, $this->lng->txt("tst_stat_result_lastvisit"));
818  $col++;
819 
820  array_push($datarow, $this->lng->txt("tst_stat_result_mark_median"));
821  $col++;
822  array_push($datarow, $this->lng->txt("tst_stat_result_rank_participant"));
823  $col++;
824  array_push($datarow, $this->lng->txt("tst_stat_result_rank_median"));
825  $col++;
826  array_push($datarow, $this->lng->txt("tst_stat_result_total_participants"));
827  $col++;
828  array_push($datarow, $this->lng->txt("tst_stat_result_median"));
829  $col++;
830  array_push($datarow, $this->lng->txt("scored_pass"));
831  $col++;
832 
833  array_push($datarow, $this->lng->txt("pass"));
834  $col++;
835 
836  $data =& $this->test_obj->getCompleteEvaluationData(TRUE, $filterby, $filtertext);
837  $headerrow = $datarow;
838  $counter = 1;
839  foreach ($data->getParticipants() as $active_id => $userdata)
840  {
841  $datarow = $headerrow;
842  $remove = FALSE;
843  if ($passedonly)
844  {
845  if ($data->getParticipant($active_id)->getPassed() == FALSE)
846  {
847  $remove = TRUE;
848  }
849  }
850  if (!$remove)
851  {
852  $datarow2 = array();
853  if ($this->test_obj->getAnonymity())
854  {
855  array_push($datarow2, $counter);
856  }
857  else
858  {
859  array_push($datarow2, $data->getParticipant($active_id)->getName());
860  array_push($datarow2, $data->getParticipant($active_id)->getLogin());
861  }
862  if (count($additionalFields))
863  {
864  $userfields = ilObjUser::_lookupFields($userdata->getUserID());
865  foreach ($additionalFields as $fieldname)
866  {
867  if (strcmp($fieldname, "gender") == 0)
868  {
869  array_push($datarow2, $this->lng->txt("gender_" . $userfields[$fieldname]));
870  }
871  else
872  {
873  array_push($datarow2, $userfields[$fieldname]);
874  }
875  }
876  }
877  array_push($datarow2, $data->getParticipant($active_id)->getReached());
878  array_push($datarow2, $data->getParticipant($active_id)->getMaxpoints());
879  array_push($datarow2, $data->getParticipant($active_id)->getMark());
880  if ($this->test_obj->ects_output)
881  {
882  array_push($datarow2, $data->getParticipant($active_id)->getECTSMark());
883  }
884  array_push($datarow2, $data->getParticipant($active_id)->getQuestionsWorkedThrough());
885  array_push($datarow2, $data->getParticipant($active_id)->getNumberOfQuestions());
886  array_push($datarow2, $data->getParticipant($active_id)->getQuestionsWorkedThroughInPercent() / 100.0);
887  $time = $data->getParticipant($active_id)->getTimeOfWork();
888  $time_seconds = $time;
889  $time_hours = floor($time_seconds/3600);
890  $time_seconds -= $time_hours * 3600;
891  $time_minutes = floor($time_seconds/60);
892  $time_seconds -= $time_minutes * 60;
893  array_push($datarow2, sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds));
894  $time = $data->getParticipant($active_id)->getQuestionsWorkedThrough() ? $data->getParticipant($active_id)->getTimeOfWork() / $data->getParticipant($active_id)->getQuestionsWorkedThrough() : 0;
895  $time_seconds = $time;
896  $time_hours = floor($time_seconds/3600);
897  $time_seconds -= $time_hours * 3600;
898  $time_minutes = floor($time_seconds/60);
899  $time_seconds -= $time_minutes * 60;
900  array_push($datarow2, sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds));
901 
902  $fv = $data->getParticipant($active_id)->getFirstVisit();
903  $lv = $data->getParticipant($active_id)->getLastVisit();
904  foreach(array($fv, $lv) as $ts)
905  {
906  if($ts)
907  {
908  $visit = ilFormat::formatDate(date('Y-m-d H:i:s', $ts), "datetime", false, false);
909  array_push($datarow2, $visit);
910  }
911  else
912  {
913  array_push($datarow2, "");
914  }
915  }
916 
917  $median = $data->getStatistics()->getStatistics()->median();
918  $pct = $data->getParticipant($active_id)->getMaxpoints() ? $median / $data->getParticipant($active_id)->getMaxpoints() * 100.0 : 0;
919  $mark = $this->test_obj->mark_schema->getMatchingMark($pct);
920  $mark_short_name = "";
921  if (is_object($mark))
922  {
923  $mark_short_name = $mark->getShortName();
924  }
925  array_push($datarow2, $mark_short_name);
926  array_push($datarow2, $data->getStatistics()->getStatistics()->rank($data->getParticipant($active_id)->getReached()));
927  array_push($datarow2, $data->getStatistics()->getStatistics()->rank_median());
928  array_push($datarow2, $data->getStatistics()->getStatistics()->count());
929  array_push($datarow2, $median);
930  if ($this->test_obj->getPassScoring() == SCORE_BEST_PASS)
931  {
932  array_push($datarow2, $data->getParticipant($active_id)->getBestPass() + 1);
933  }
934  else
935  {
936  array_push($datarow2, $data->getParticipant($active_id)->getLastPass() + 1);
937  }
938  for ($pass = 0; $pass <= $data->getParticipant($active_id)->getLastPass(); $pass++)
939  {
940  $finishdate = $this->test_obj->getPassFinishDate($active_id, $pass);
941  if ($finishdate > 0)
942  {
943  if ($pass > 0)
944  {
945  for ($i = 1; $i < $col-1; $i++)
946  {
947  array_push($datarow2, "");
948  array_push($datarow, "");
949  }
950  array_push($datarow, "");
951  }
952  array_push($datarow2, $pass+1);
953  if (is_object($data->getParticipant($active_id)) && is_array($data->getParticipant($active_id)->getQuestions($pass)))
954  {
955  foreach ($data->getParticipant($active_id)->getQuestions($pass) as $question)
956  {
957  $question_data = $data->getParticipant($active_id)->getPass($pass)->getAnsweredQuestionByQuestionId($question["id"]);
958  array_push($datarow2, $question_data["reached"]);
959  array_push($datarow, preg_replace("/<.*?>/", "", $data->getQuestionTitle($question["id"])));
960  }
961  }
962  if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions() || ($counter == 1 && $pass == 0))
963  {
964  array_push($rows, $datarow);
965  }
966  $datarow = array();
967  array_push($rows, $datarow2);
968  $datarow2 = array();
969  }
970  }
971  $counter++;
972  }
973  }
974  $csv = "";
975  $separator = ";";
976  foreach ($rows as $evalrow)
977  {
978  $csvrow =& $this->test_obj->processCSVRow($evalrow, TRUE, $separator);
979  $csv .= join($csvrow, $separator) . "\n";
980  }
981  if ($deliver)
982  {
983  ilUtil::deliverData($csv, ilUtil::getASCIIFilename($this->test_obj->getTitle() . "_results.csv"));
984  exit;
985  }
986  else
987  {
988  return $csv;
989  }
990  }
991 
996  {
997  global $ilBench;
998 
999  $ilBench->start("TestExport", "buildExportFile");
1000 
1001  include_once("./Services/Xml/classes/class.ilXmlWriter.php");
1002  $this->xml = new ilXmlWriter;
1003 
1004  // set dtd definition
1005  $this->xml->xmlSetDtdDef("<!DOCTYPE Test SYSTEM \"http://www.ilias.uni-koeln.de/download/dtd/ilias_co.dtd\">");
1006 
1007  // set generated comment
1008  $this->xml->xmlSetGenCmt("Export of ILIAS Test ".
1009  $this->test_obj->getId()." of installation ".$this->inst.".");
1010 
1011  // set xml header
1012  $this->xml->xmlHeader();
1013 
1014  // create directories
1015  $this->test_obj->createExportDirectory();
1016  include_once "./Services/Utilities/classes/class.ilUtil.php";
1017  ilUtil::makeDir($this->export_dir."/".$this->subdir);
1018  ilUtil::makeDir($this->export_dir."/".$this->subdir."/objects");
1019 
1020  // get Log File
1021  $expDir = $this->test_obj->getExportDirectory();
1022  include_once "./Services/Logging/classes/class.ilLog.php";
1023  $expLog = new ilLog($expDir, "export.log");
1024  $expLog->delete();
1025  $expLog->setLogFormat("");
1026  $expLog->write(date("[y-m-d H:i:s] ")."Start Export");
1027 
1028  // write qti file
1029  $qti_file = fopen($this->export_dir."/".$this->subdir."/".$this->qti_filename, "w");
1030  fwrite($qti_file, $this->test_obj->toXML());
1031  fclose($qti_file);
1032 
1033  // get xml content
1034  $ilBench->start("TestExport", "buildExportFile_getXML");
1035  $this->test_obj->exportPagesXML($this->xml, $this->inst_id,
1036  $this->export_dir."/".$this->subdir, $expLog);
1037  $ilBench->stop("TestExport", "buildExportFile_getXML");
1038 
1039  // dump xml document to screen (only for debugging reasons)
1040  /*
1041  echo "<PRE>";
1042  echo htmlentities($this->xml->xmlDumpMem($format));
1043  echo "</PRE>";
1044  */
1045 
1046  // dump xml document to file
1047  $ilBench->start("TestExport", "buildExportFile_dumpToFile");
1048  $this->xml->xmlDumpFile($this->export_dir."/".$this->subdir."/".$this->filename
1049  , false);
1050  $ilBench->stop("TestExport", "buildExportFile_dumpToFile");
1051 
1052  if (@file_exists("./Modules/Test/classes/class.ilTestResultsToXML.php"))
1053  {
1054  // dump results xml document to file
1055  include_once "./Modules/Test/classes/class.ilTestResultsToXML.php";
1056  $resultwriter = new ilTestResultsToXML($this->test_obj->getTestId(), $this->test_obj->getAnonymity());
1057  $ilBench->start("TestExport", "buildExportFile_results");
1058  $resultwriter->xmlDumpFile($this->export_dir."/".$this->subdir."/".$this->resultsfile, false);
1059  $ilBench->stop("TestExport", "buildExportFile_results");
1060  }
1061 
1062  // add media objects which were added with tiny mce
1063  $ilBench->start("QuestionpoolExport", "buildExportFile_saveAdditionalMobs");
1064  $this->exportXHTMLMediaObjects($this->export_dir."/".$this->subdir);
1065  $ilBench->stop("QuestionpoolExport", "buildExportFile_saveAdditionalMobs");
1066 
1067  // zip the file
1068  $ilBench->start("TestExport", "buildExportFile_zipFile");
1069  ilUtil::zip($this->export_dir."/".$this->subdir,
1070  $this->export_dir."/".$this->subdir.".zip");
1071  $ilBench->stop("TestExport", "buildExportFile_zipFile");
1072 
1073  // destroy writer object
1074  $this->xml->_XmlWriter;
1075 
1076  $expLog->write(date("[y-m-d H:i:s] ")."Finished Export");
1077  $ilBench->stop("TestExport", "buildExportFile");
1078 
1079  return $this->export_dir."/".$this->subdir.".zip";
1080  }
1081 
1082  function exportXHTMLMediaObjects($a_export_dir)
1083  {
1084  include_once("./Services/MediaObjects/classes/class.ilObjMediaObject.php");
1085 
1086  $mobs = ilObjMediaObject::_getMobsOfObject("tst:html", $this->test_obj->getId());
1087  foreach ($mobs as $mob)
1088  {
1089  if (ilObjMediaObject::_exists($mob))
1090  {
1091  $mob_obj =& new ilObjMediaObject($mob);
1092  $mob_obj->exportFiles($a_export_dir);
1093  unset($mob_obj);
1094  }
1095  }
1096  foreach ($this->test_obj->questions as $question_id)
1097  {
1098  $mobs = ilObjMediaObject::_getMobsOfObject("qpl:html", $question_id);
1099  foreach ($mobs as $mob)
1100  {
1101  if (ilObjMediaObject::_exists($mob))
1102  {
1103  $mob_obj =& new ilObjMediaObject($mob);
1104  $mob_obj->exportFiles($a_export_dir);
1105  unset($mob_obj);
1106  }
1107  }
1108  }
1109  }
1110 
1111 }
1112 
1113 ?>
static deliverData($a_data, $a_filename, $mime="application/octet-stream", $charset="")
deliver data for download via browser.
print $file
static _instanciateQuestion($question_id)
Creates an instance of a question with a given question id.
exit
Definition: login.php:54
$separator
exportToExcel($deliver=TRUE, $filterby="", $filtertext="", $passedonly=FALSE)
Exports the evaluation data to the Microsoft Excel file format.
aggregatedResultsToExcel($deliver=TRUE)
Exports the aggregated results to the Microsoft Excel file format.
xmlSetDtdDef($dtdDef)
Sets dtd definition.
_lookupFields($a_user_id)
lookup fields (deprecated; use more specific methods instead)
_convert_text($a_text, $a_target="has been removed")
XML writer class.
buildExportFileXML()
build xml export file
Export class for tests.
Test results to XML class.
logging
Definition: class.ilLog.php:18
aggregatedResultsToCSV($deliver=TRUE)
Exports the aggregated results to CSV.
exportToCSV($deliver=TRUE, $filterby="", $filtertext="", $passedonly=FALSE)
Exports the evaluation data to the CSV file format.
static getASCIIFilename($a_filename)
convert utf8 to ascii filename
__construct(&$a_test_obj, $a_mode="xml")
Constructor.
Class ilExcelWriterAdapter.
$mobs
buildExportFile()
build export file (complete zip file)
static _exists($a_id)
checks wether a lm content object with specified id exists or not
exportXHTMLMediaObjects($a_export_dir)
redirection script todo: (a better solution should control the processing via a xml file) ...
Class ilObjMediaObject.
static deliverFile($a_file, $a_filename, $a_mime='', $isInline=false, $removeAfterDelivery=false, $a_exit_after=true)
deliver file for download via browser.
static zip($a_dir, $a_file, $compress_content=false)
zips given directory/file into given zip.file
$filename
Definition: buildRTE.php:89
_getMobsOfObject($a_type, $a_id, $a_usage_hist_nr=0, $a_lang="-")
get mobs of object
static makeDir($a_dir)
creates a new directory and inherits all filesystem permissions of the parent directory You may pass ...
while($lm_rec=$ilDB->fetchAssoc($lm_set)) $data
formatDate($a_date, $a_mode="datetime", $a_omit_seconds=false, $a_relative=TRUE)
format a date according to the user language shortcut for Format::fmtDateTime public ...
static ilTempnam()
Create a temporary file in an ILIAS writable directory.
global $ilBench
Definition: ilias.php:18
buildExportResultFile()
build xml export file
static excelTime($year="", $month="", $day="", $hour="", $minute="", $second="")
Calculates a Microsoft Excel date/time value.
const SCORE_BEST_PASS
xmlDumpFile($file, $format=TRUE)