ILIAS  Release_4_4_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilObjExercise.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 require_once "./Services/Object/classes/class.ilObject.php";
5 require_once "./Modules/Exercise/classes/class.ilFileDataExercise.php";
6 require_once "./Modules/Exercise/classes/class.ilExerciseMembers.php";
7 
20 class ilObjExercise extends ilObject
21 {
22  var $file_obj;
23  var $members_obj;
24  var $files;
25 
26  var $timestamp;
27  var $hour;
28  var $minutes;
29  var $day;
30  var $month;
31  var $year;
32  var $instruction;
33  var $certificate_visibility;
34 
43  protected $completion_by_submission = false;
44 
51  function ilObjExercise($a_id = 0,$a_call_by_reference = true)
52  {
53  $this->setPassMode("all");
54  $this->type = "exc";
55  $this->ilObject($a_id,$a_call_by_reference);
56  }
57 
58  // SET, GET METHODS
59  function setDate($a_hour,$a_minutes,$a_day,$a_month,$a_year)
60  {
61  $this->hour = (int) $a_hour;
62  $this->minutes = (int) $a_minutes;
63  $this->day = (int) $a_day;
64  $this->month = (int) $a_month;
65  $this->year = (int) $a_year;
66  $this->timestamp = mktime($this->hour,$this->minutes,0,$this->month,$this->day,$this->year);
67  return true;
68  }
69  function getTimestamp()
70  {
71  return $this->timestamp;
72  }
73  function setTimestamp($a_timestamp)
74  {
75  $this->timestamp = $a_timestamp;
76  }
77  function setInstruction($a_instruction)
78  {
79  $this->instruction = $a_instruction;
80  }
81  function getInstruction()
82  {
83  return $this->instruction;
84  }
85 
91  function setPassMode($a_val)
92  {
93  $this->pass_mode = $a_val;
94  }
95 
101  function getPassMode()
102  {
103  return $this->pass_mode;
104  }
105 
111  function setPassNr($a_val)
112  {
113  $this->pass_nr = $a_val;
114  }
115 
121  function getPassNr()
122  {
123  return $this->pass_nr;
124  }
125 
131  function setShowSubmissions($a_val)
132  {
133  $this->show_submissions = $a_val;
134  }
135 
141  function getShowSubmissions()
142  {
143  return $this->show_submissions;
144  }
145 
146 
147 /* function getFiles()
148  {
149  return $this->files;
150  }*/
151 
152  function checkDate()
153  {
154  return $this->hour == (int) date("H",$this->timestamp) and
155  $this->minutes == (int) date("i",$this->timestamp) and
156  $this->day == (int) date("d",$this->timestamp) and
157  $this->month == (int) date("m",$this->timestamp) and
158  $this->year == (int) date("Y",$this->timestamp);
159 
160  }
161 
165  function deliverFile($a_http_post_files, $a_ass_id, $user_id, $unzip = false)
166  {
167  global $ilDB;
168 
169  include_once("./Modules/Exercise/classes/class.ilFSStorageExercise.php");
170  $storage = new ilFSStorageExercise($this->getId(), $a_ass_id);
171  $deliver_result = $storage->deliverFile($a_http_post_files, $user_id, $unzip);
172 //var_dump($deliver_result);
173  if ($deliver_result)
174  {
175  $next_id = $ilDB->nextId("exc_returned");
176  $query = sprintf("INSERT INTO exc_returned ".
177  "(returned_id, obj_id, user_id, filename, filetitle, mimetype, ts, ass_id) ".
178  "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
179  $ilDB->quote($next_id, "integer"),
180  $ilDB->quote($this->getId(), "integer"),
181  $ilDB->quote($user_id, "integer"),
182  $ilDB->quote($deliver_result["fullname"], "text"),
183  $ilDB->quote($a_http_post_files["name"], "text"),
184  $ilDB->quote($deliver_result["mimetype"], "text"),
185  $ilDB->quote(ilUtil::now(), "timestamp"),
186  $ilDB->quote($a_ass_id, "integer")
187  );
188  $ilDB->manipulate($query);
189 
190  // team upload?
191  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($a_ass_id, $user_id);
192  if(!$user_ids)
193  {
194  $user_ids = array($user_id);
195  }
196  else
197  {
198  $team_id = ilExAssignment::getTeamIdByAssignment($a_ass_id, $user_id);
199  ilExAssignment::writeTeamLog($team_id, ilExAssignment::TEAM_LOG_ADD_FILE, $a_http_post_files["name"]);
200  }
201 
202  foreach($user_ids as $user_id)
203  {
204  if (!$this->members_obj->isAssigned($user_id))
205  {
206  $this->members_obj->assignMember($user_id);
207  }
208  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, 1);
209  ilExerciseMembers::_writeReturned($this->getId(), $user_id, 1);
210  }
211  }
212  return true;
213  }
214 
218  function addUploadedFile($a_http_post_files, $unzipUploadedFile = false)
219  {
220  global $lng;
221  if ($unzipUploadedFile && preg_match("/zip/", $a_http_post_files["type"]) == 1)
222  {
223 
224  $this->processUploadedFile($a_http_post_files["tmp_name"], "storeUploadedFile", true);
225  return true;
226 
227 
228  }
229  else
230  {
231  $this->file_obj->storeUploadedFile($a_http_post_files, true);
232  return true;
233  }
234  }
235  function deleteFiles($a_files)
236  {
237  $this->file_obj->unlinkFiles($a_files);
238  }
239 
240  function saveData()
241  {
242  global $ilDB;
243 
244  // SAVE ONLY EXERCISE SPECIFIC DATA
245  /*$query = "INSERT INTO exc_data SET ".
246  "obj_id = ".$ilDB->quote($this->getId()).", ".
247  "instruction = ".$ilDB->quote($this->getInstruction()).", ".
248  "time_stamp = ".$ilDB->quote($this->getTimestamp());
249  $this->ilias->db->query($query);*/
250 
251  $ilDB->insert("exc_data", array(
252  "obj_id" => array("integer", $this->getId()),
253  "instruction" => array("clob", $this->getInstruction()),
254  "time_stamp" => array("integer", $this->getTimestamp()),
255  "pass_mode" => array("text", $this->getPassMode()),
256  "pass_nr" => array("text", $this->getPassNr()),
257  "show_submissions" => array("integer", (int) $this->getShowSubmissions()),
258  'compl_by_submission' => array('integer', (int)$this->isCompletionBySubmissionEnabled()),
259  "certificate_visibility" => array("integer", (int)$this->getCertificateVisibility())
260  ));
261  return true;
262  }
263 
271  public function cloneObject($a_target_id,$a_copy_id = 0)
272  {
273  global $ilDB;
274 
275  // Copy settings
276  $new_obj = parent::cloneObject($a_target_id,$a_copy_id);
277  $new_obj->setInstruction($this->getInstruction());
278  $new_obj->setTimestamp($this->getTimestamp());
279  $new_obj->setPassMode($this->getPassMode());
280  $new_obj->saveData();
281  $new_obj->setPassNr($this->getPassNr());
282  $new_obj->setShowSubmissions($this->getShowSubmissions());
283  $new_obj->setCompletionBySubmission($this->isCompletionBySubmissionEnabled());
284 
285 
286  $new_obj->update();
287 
288  // Copy assignments
289  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
290  ilExAssignment::cloneAssignmentsOfExercise($this->getId(), $new_obj->getId());
291  //$tmp_file_obj =& new ilFileDataExercise($this->getId());
292  //$tmp_file_obj->ilClone($new_obj->getId());
293  //unset($tmp_file_obj);
294 
295  // Copy learning progress settings
296  include_once('Services/Tracking/classes/class.ilLPObjSettings.php');
297  $obj_settings = new ilLPObjSettings($this->getId());
298  $obj_settings->cloneSettings($new_obj->getId());
299  unset($obj_settings);
300 
301  return $new_obj;
302  }
303 
310  function deleteDeliveredFiles($a_exc_id, $a_ass_id, $file_id_array, $user_id)
311  {
312  ilExAssignment::deleteDeliveredFiles($a_exc_id, $a_ass_id, $file_id_array, $user_id);
313 
314  // Finally update status 'returned' of member if no file exists
315  if(!count(ilExAssignment::getDeliveredFiles($a_exc_id, $a_ass_id, $user_id)))
316  {
317  // team upload?
318  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($a_ass_id, $user_id);
319  if(!$user_ids)
320  {
321  $user_ids = array($user_id);
322  }
323 
324  foreach($user_ids as $user_id)
325  {
326  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, 0);
327  }
328  }
329  }
330 
336  function deliverReturnedFiles($user_id)
337  {
338  require_once "./Services/Utilities/classes/class.ilUtil.php";
339  }
340 
347  function delete()
348  {
349  global $ilDB;
350 
351  // always call parent delete function first!!
352  if (!parent::delete())
353  {
354  return false;
355  }
356  // put here course specific stuff
357  $ilDB->manipulate("DELETE FROM exc_data ".
358  "WHERE obj_id = ".$ilDB->quote($this->getId(), "integer"));
359 
360  //$this->ilias->db->query($query);
361 
362  //$this->file_obj->delete();
363  //$this->members_obj->delete();
364 
365  // remove all notifications
366  include_once "./Services/Notification/classes/class.ilNotification.php";
367  ilNotification::removeForObject(ilNotification::TYPE_EXERCISE_SUBMISSION, $this->getId());
368 
369  return true;
370  }
371 
382  function notify($a_event,$a_ref_id,$a_node_id,$a_params = 0)
383  {
384  // object specific event handling
385 
386  parent::notify($a_event,$a_ref_id,$a_node_id,$a_params);
387  }
388 
389  function read()
390  {
391  global $ilDB;
392 
393  parent::read();
394 
395  $query = "SELECT * FROM exc_data ".
396  "WHERE obj_id = ".$ilDB->quote($this->getId(), "integer");
397 
398  $res = $ilDB->query($query);
399  while($row = $ilDB->fetchObject($res))
400  {
401  $this->setInstruction($row->instruction);
402  $this->setTimestamp($row->time_stamp);
403  $pm = ($row->pass_mode == "")
404  ? "all"
405  : $row->pass_mode;
406  $this->setPassMode($pm);
407  $this->setShowSubmissions($row->show_submissions);
408  if ($row->pass_mode == "nr")
409  {
410  $this->setPassNr($row->pass_nr);
411  }
412  $this->setCompletionBySubmission($row->compl_by_submission == 1 ? true : false);
413  $this->setCertificateVisibility($row->certificate_visibility);
414  }
415 
416  $this->members_obj = new ilExerciseMembers($this);
417 
418  // GET FILE ASSIGNED TO EXERCISE
419 // $this->file_obj = new ilFileDataExercise($this->getId());
420 // $this->files = $this->file_obj->getFiles();
421 
422  return true;
423  }
424 
425  function update()
426  {
427  global $ilDB;
428 
429  parent::update();
430 
431  /*$query = "UPDATE exc_data SET ".
432  "instruction = ".$ilDB->quote($this->getInstruction()).", ".
433  "time_stamp = ".$ilDB->quote($this->getTimestamp())." ".
434  "WHERE obj_id = ".$ilDB->quote($this->getId());
435  */
436 
437  if ($this->getPassMode() == "all")
438  {
439  $pass_nr = null;
440  }
441  else
442  {
443  $pass_nr = $this->getPassNr();
444  }
445 
446  $ilDB->update("exc_data", array(
447  "instruction" => array("clob", $this->getInstruction()),
448  "time_stamp" => array("integer", $this->getTimestamp()),
449  "pass_mode" => array("text", $this->getPassMode()),
450  "pass_nr" => array("integer", $this->getPassNr()),
451  "show_submissions" => array("integer", (int) $this->getShowSubmissions()),
452  'compl_by_submission' => array('integer', (int)$this->isCompletionBySubmissionEnabled())
453  ), array(
454  "obj_id" => array("integer", $this->getId())
455  ));
456 
457  $this->updateAllUsersStatus();
458 
459  //$res = $this->ilias->db->query($query);
460 
461  #$this->members_obj->update();
462  return true;
463  }
464 
468  function sendAssignment($a_exc_id, $a_ass_id, $a_members)
469  {
470  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
471  $ass_title = ilExAssignment::lookupTitle($a_ass_id);
472 
473  include_once("./Modules/Exercise/classes/class.ilFSStorageExercise.php");
474  $storage = new ilFSStorageExercise($a_exc_id, $a_ass_id);
475  $files = $storage->getFiles();
476 
477  if(count($files))
478  {
479  include_once "./Services/Mail/classes/class.ilFileDataMail.php";
480 
481  $mfile_obj = new ilFileDataMail($_SESSION["AccountId"]);
482  foreach($files as $file)
483  {
484  $mfile_obj->copyAttachmentFile($file["fullpath"], $file["name"]);
485  $file_names[] = $file["name"];
486  }
487  }
488 
489  include_once "Services/Mail/classes/class.ilMail.php";
490 
491  $tmp_mail_obj = new ilMail($_SESSION["AccountId"]);
492  $message = $tmp_mail_obj->sendMail(
493  $this->__formatRecipients($a_members),"","",
494  $this->__formatSubject($ass_title), $this->__formatBody($a_ass_id),
495  count($file_names) ? $file_names : array(),array("normal"));
496 
497  unset($tmp_mail_obj);
498 
499  if(count($file_names))
500  {
501  $mfile_obj->unlinkFiles($file_names);
502  unset($mfile_obj);
503  }
504 
505 
506  // SET STATUS SENT FOR ALL RECIPIENTS
507  foreach($a_members as $member_id => $value)
508  {
509  ilExAssignment::updateStatusSentForUser($a_ass_id, $member_id, 1);
510  }
511 
512  return true;
513  }
514 
518  function _lookupStatusTime($exc_id, $member_id)
519  {
520 
521  global $ilDB, $lng;
522 
523  $q = "SELECT * ".
524  "FROM exc_members ".
525  "WHERE obj_id= ".$ilDB->quote($exc_id, "integer").
526  " AND usr_id= ".$ilDB->quote($member_id, "integer");
527 
528  $set = $ilDB->query($q);
529  if ($rec = $ilDB->fetchAssoc($set))
530  {
531  return ilUtil::getMySQLTimestamp($rec["status_time"]);
532  }
533  }
534 
535  // PRIVATE METHODS
536  function __formatBody($a_ass_id)
537  {
538  global $lng;
539 
540  $lng->loadLanguageModule("exc");
541 
542  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
543  $ass = new ilExAssignment($a_ass_id);
544 
545  $body = $ass->getInstruction();
546  $body .= "\n\n";
547  if ($ass->getDeadline() == 0)
548  {
549  $body .= $lng->txt("exc_edit_until") . ": ".
550  $lng->txt("exc_no_deadline_specified");
551  }
552  else
553  {
554  $body .= $lng->txt("exc_edit_until") . ": ".
555  ilFormat::formatDate(date("Y-m-d H:i:s",$ass->getDeadline()), "datetime", true);
556  }
557  $body .= "\n\n";
558  $body .= ILIAS_HTTP_PATH.
559  "/goto.php?target=".
560  $this->getType().
561  "_".$this->getRefId()."&client_id=".CLIENT_ID;
562 
563  return $body;
564  }
565 
566  function __formatSubject($a_ass_title = "")
567  {
568  $subject = $this->getTitle();
569 
570  if ($a_ass_title != "")
571  {
572  $subject.= ": ".$a_ass_title;
573  }
574 
575  return $subject;
576  }
577 
578  function __formatRecipients($a_members)
579  {
580  foreach($a_members as $member_id => $value)
581  {
582  $tmp_obj = ilObjectFactory::getInstanceByObjId($member_id);
583  $tmp_members[] = $tmp_obj->getLogin();
584 
585  unset($tmp_obj);
586  }
587 
588  return implode(',',$tmp_members ? $tmp_members : array());
589  }
590 
591  function _checkCondition($a_exc_id,$a_operator,$a_value,$a_usr_id = 0)
592  {
593  global $ilUser;
594 
595  $a_usr_id = $a_usr_id ? $a_usr_id : $ilUser->getId();
596 
597  switch($a_operator)
598  {
599  case 'passed':
600  if (ilExerciseMembers::_lookupStatus($a_exc_id, $a_usr_id) == "passed")
601  {
602  return true;
603  }
604  else
605  {
606  return false;
607  }
608  break;
609 
610  default:
611  return true;
612  }
613  return true;
614  }
615 
622  function processUploadedFile ($fileTmp, $storageMethod, $persistentErrorMessage,
623  $a_ass_id)
624  {
625  global $lng, $ilUser;
626 
627  // Create unzip-directory
628  $newDir = ilUtil::ilTempnam();
629  ilUtil::makeDir($newDir);
630 
631  include_once ("Services/Utilities/classes/class.ilFileUtils.php");
632 
633  try
634  {
635  $processDone = ilFileUtils::processZipFile($newDir,$fileTmp, false);
636  ilFileUtils::recursive_dirscan($newDir, $filearray);
637 
638  foreach ($filearray["file"] as $key => $filename)
639  {
640  $a_http_post_files["name"] = ilFileUtils::utf8_encode($filename);
641  $a_http_post_files["type"] = "other";
642  $a_http_post_files["tmp_name"] = $filearray["path"][$key]."/".$filename;
643  $a_http_post_files["error"] = 0;
644  $a_http_post_files["size"] = filesize($filearray["path"][$key]."/".$filename);
645 
646  if ($storageMethod == "deliverFile")
647  {
648  $this->$storageMethod($a_http_post_files, $a_ass_id, $ilUser->id, true);
649  }
650  else if ($storageMethod == "storeUploadedFile")
651  {
652  $this->file_obj->$storageMethod($a_http_post_files, true, true);
653  }
654  }
655  ilExerciseMembers::_writeReturned($this->getId(), $ilUser->id, 1);
656  ilUtil::sendSuccess($this->lng->txt("file_added"),$persistentErrorMessage);
657  }
658  catch (ilFileUtilsException $e)
659  {
660  ilUtil::sendFailure($e->getMessage(), $persistentErrorMessage);
661  }
662 
663 
664  ilUtil::delDir($newDir);
665  return $processDone;
666 
667  }
668 
679  static function _fixFilename($a_filename)
680  {
681  $ex_pos = strrpos($a_filename, "/exercise/");
682  if ($ex_pos > 0)
683  {
684  $a_filename = CLIENT_DATA_DIR.substr($a_filename, $ex_pos);
685  }
686  return $a_filename;
687  }
688 
693  static function _fixFilenameArray($a_array)
694  {
695  if (is_array($a_array))
696  {
697  foreach ($a_array as $k => $v)
698  {
699  if ($v["filename"] != "")
700  {
701  $a_array[$k]["filename"] = ilObjExercise::_fixFilename($a_array[$k]["filename"]);
702  }
703  }
704  }
705 
706  return $a_array;
707  }
708 
712  function determinStatusOfUser($a_user_id = 0)
713  {
714  global $ilUser;
715 
716  if ($a_user_id == 0)
717  {
718  $a_user_id = $ilUser->getId();
719  }
720 
721  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
722  $ass = ilExAssignment::getAssignmentDataOfExercise($this->getId());
723 
724  $passed_all_mandatory = true;
725  $failed_a_mandatory = false;
726  $cnt_passed = 0;
727  $cnt_notgraded = 0;
728  $passed_at_least_one = false;
729 
730  foreach ($ass as $a)
731  {
732  $stat = ilExAssignment::lookupStatusOfUser($a["id"], $a_user_id);
733  if ($a["mandatory"] && ($stat == "failed" || $stat == "notgraded"))
734  {
735  $passed_all_mandatory = false;
736  }
737  if ($a["mandatory"] && ($stat == "failed"))
738  {
739  $failed_a_mandatory = true;
740  }
741  if ($stat == "passed")
742  {
743  $cnt_passed++;
744  }
745  if ($stat == "notgraded")
746  {
747  $cnt_notgraded++;
748  }
749  }
750 
751  if (count($ass) == 0)
752  {
753  $passed_all_mandatory = false;
754  }
755 
756  if ($this->getPassMode() != "nr")
757  {
758 //echo "5";
759  $overall_stat = "notgraded";
760  if ($failed_a_mandatory)
761  {
762 //echo "6";
763  $overall_stat = "failed";
764  }
765  else if ($passed_all_mandatory && $cnt_passed > 0)
766  {
767 //echo "7";
768  $overall_stat = "passed";
769  }
770  }
771  else
772  {
773 //echo "8";
774  $min_nr = $this->getPassNr();
775  $overall_stat = "notgraded";
776 //echo "*".$cnt_passed."*".$cnt_notgraded."*".$min_nr."*";
777  if ($failed_a_mandatory || ($cnt_passed + $cnt_notgraded < $min_nr))
778  {
779 //echo "9";
780  $overall_stat = "failed";
781  }
782  else if ($passed_all_mandatory && $cnt_passed >= $min_nr)
783  {
784 //echo "A";
785  $overall_stat = "passed";
786  }
787  }
788 
789  $ret = array(
790  "overall_status" => $overall_stat,
791  "failed_a_mandatory" => $failed_a_mandatory);
792 //echo "<br>p:".$cnt_passed.":ng:".$cnt_notgraded;
793 //var_dump($ret);
794  return $ret;
795  }
796 
800  function updateUserStatus($a_user_id = 0)
801  {
802  global $ilUser;
803 
804  if ($a_user_id == 0)
805  {
806  $a_user_id = $ilUser->getId();
807  }
808 
809  $st = $this->determinStatusOfUser($a_user_id);
810 
811  include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
812  ilExerciseMembers::_writeStatus($this->getId(), $a_user_id,
813  $st["overall_status"]);
814  }
815 
819  function updateAllUsersStatus()
820  {
821  if (!is_object($this->members_obj));
822  {
823  $this->members_obj = new ilExerciseMembers($this);
824  }
825 
826  $mems = $this->members_obj->getMembers();
827  foreach ($mems as $mem)
828  {
829  $this->updateUserStatus($mem);
830  }
831  }
832 
836  function exportGradesExcel()
837  {
838  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
839  $ass_data = ilExAssignment::getAssignmentDataOfExercise($this->getId());
840 
841  include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
842  $excelfile = ilUtil::ilTempnam();
843  $adapter = new ilExcelWriterAdapter($excelfile, FALSE);
844  $workbook = $adapter->getWorkbook();
845  $workbook->setVersion(8); // Use Excel97/2000 Format
846  include_once ("./Services/Excel/classes/class.ilExcelUtils.php");
847 
848  //
849  // status
850  //
851  $mainworksheet = $workbook->addWorksheet();
852 
853  // header row
854  $mainworksheet->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
855  $cnt = 1;
856  foreach ($ass_data as $ass)
857  {
858  $mainworksheet->writeString(0, $cnt, $cnt);
859  $cnt++;
860  }
861  $mainworksheet->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
862 
863  // data rows
864  $this->mem_obj = new ilExerciseMembers($this);
865  $getmems = $this->mem_obj->getMembers();
866  $mems = array();
867  foreach ($getmems as $user_id)
868  {
869  $mems[$user_id] = ilObjUser::_lookupName($user_id);
870  }
871  $mems = ilUtil::sortArray($mems, "lastname", "asc", false, true);
872 
873  $data = array();
874  $row_cnt = 1;
875  foreach ($mems as $user_id => $d)
876  {
877  $col_cnt = 1;
878 
879  // name
880  $mainworksheet->writeString($row_cnt, 0,
881  ilExcelUtils::_convert_text($d["lastname"].", ".$d["firstname"]." [".$d["login"]."]"));
882 
883  reset($ass_data);
884 
885  foreach ($ass_data as $ass)
886  {
887  $status = ilExAssignment::lookupStatusOfUser($ass["id"], $user_id);
888  $mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_".$status)));
889  $col_cnt++;
890  }
891 
892  // total status
893  $status = ilExerciseMembers::_lookupStatus($this->getId(), $user_id);
894  $mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_".$status)));
895 
896  $row_cnt++;
897  }
898 
899  //
900  // mark
901  //
902  $worksheet2 = $workbook->addWorksheet();
903 
904  // header row
905  $worksheet2->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
906  $cnt = 1;
907  foreach ($ass_data as $ass)
908  {
909  $worksheet2->writeString(0, $cnt, $cnt);
910  $cnt++;
911  }
912  $worksheet2->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
913 
914  // data rows
915  $data = array();
916  $row_cnt = 1;
917  reset($mems);
918  foreach ($mems as $user_id => $d)
919  {
920  $col_cnt = 1;
921  $d = ilObjUser::_lookupName($user_id);
922 
923  // name
924  $worksheet2->writeString($row_cnt, 0,
925  ilExcelUtils::_convert_text($d["lastname"].", ".$d["firstname"]." [".$d["login"]."]"));
926 
927  reset($ass_data);
928 
929  foreach ($ass_data as $ass)
930  {
931  $worksheet2->writeString($row_cnt, $col_cnt,
932  ilExcelUtils::_convert_text(ilExAssignment::lookupMarkOfUser($ass["id"], $user_id)));
933  $col_cnt++;
934  }
935 
936  // total mark
937  include_once 'Services/Tracking/classes/class.ilLPMarks.php';
938  $worksheet2->writeString($row_cnt, $col_cnt,
939  ilExcelUtils::_convert_text(ilLPMarks::_lookupMark($user_id, $this->getId())));
940 
941  $row_cnt++;
942  }
943 
944 
945  $workbook->close();
946  $exc_name = ilUtil::getASCIIFilename(preg_replace("/\s/", "_", $this->getTitle()));
947  ilUtil::deliverFile($excelfile, $exc_name.".xls", "application/vnd.ms-excel");
948  }
949 
953  function sendFeedbackFileNotification($a_feedback_file, $a_user_id, $a_ass_id)
954  {
955  $user_ids = $a_user_id;
956  if(!is_array($user_ids))
957  {
958  $user_ids = array($user_ids);
959  }
960 
961  include_once("./Modules/Exercise/classes/class.ilExerciseMailNotification.php");
962  $not = new ilExerciseMailNotification();
963  $not->setType(ilExerciseMailNotification::TYPE_FEEDBACK_FILE_ADDED);
964  $not->setAssignmentId($a_ass_id);
965  $not->setObjId($this->getId());
966  if ($this->getRefId() > 0)
967  {
968  $not->setRefId($this->getRefId());
969  }
970  $not->setRecipients($user_ids);
971  $not->send();
972  }
973 
982  public function isCompletionBySubmissionEnabled()
983  {
984  return $this->completion_by_submission;
985  }
986 
996  public function setCompletionBySubmission($bool)
997  {
998  $this->completion_by_submission = (bool)$bool;
999 
1000  return $this;
1001  }
1002 
1013  public function handleSubmission($ass_id)
1014  {
1015  global $ilUser, $ilDB;
1016 
1017  if($this->isCompletionBySubmissionEnabled())
1018  {
1019  include_once 'Modules/Exercise/classes/class.ilExAssignment.php';
1020 
1021  // #17673 - team upload?
1022  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($ass_id, $ilUser->getId());
1023  if(!$user_ids)
1024  {
1025  $user_ids = array($ilUser->getId());
1026  }
1027 
1028  $res = $ilDB->query(
1029  'SELECT returned_id'.
1030  ' FROM exc_returned'.
1031  ' WHERE obj_id = '.$ilDB->quote($this->getId(), 'integer').
1032  ' AND ass_id = '.$ilDB->quote($ass_id, 'integer').
1033  ' AND '.$ilDB->in('user_id', $user_ids, '', 'integer')
1034  );
1035 
1036  if($ilDB->numRows($res))
1037  {
1038  $status = 'passed';
1039  }
1040  else
1041  {
1042  $status = 'notgraded';
1043  }
1044  foreach($user_ids as $user_id)
1045  {
1046  ilExAssignment::updateStatusOfUser($ass_id, $user_id, $status);
1047  }
1048  }
1049  }
1050 
1057  public static function _lookupFinishedUserExercises($a_user_id)
1058  {
1059  global $ilDB;
1060 
1061  $set = $ilDB->query("SELECT obj_id, status FROM exc_members".
1062  " WHERE usr_id = ".$ilDB->quote($a_user_id, "integer").
1063  " AND (status = ".$ilDB->quote("passed", "text").
1064  " OR status = ".$ilDB->quote("failed", "text").")");
1065 
1066  $all = array();
1067  while($row = $ilDB->fetchAssoc($set))
1068  {
1069  $all[$row["obj_id"]] = ($row["status"] == "passed");
1070  }
1071  return $all;
1072  }
1073 
1082  function addResourceObject($a_wsp_id, $a_ass_id, $user_id, $a_text = null)
1083  {
1084  global $ilDB;
1085 
1086  $next_id = $ilDB->nextId("exc_returned");
1087  $query = sprintf("INSERT INTO exc_returned ".
1088  "(returned_id, obj_id, user_id, filetitle, ass_id, ts, atext) ".
1089  "VALUES (%s, %s, %s, %s, %s, %s, %s)",
1090  $ilDB->quote($next_id, "integer"),
1091  $ilDB->quote($this->getId(), "integer"),
1092  $ilDB->quote($user_id, "integer"),
1093  $ilDB->quote($a_wsp_id, "text"),
1094  $ilDB->quote($a_ass_id, "integer"),
1095  $ilDB->quote(ilUtil::now(), "timestamp"),
1096  $ilDB->quote($a_text, "text")
1097  );
1098  $ilDB->manipulate($query);
1099  if (!$this->members_obj->isAssigned($user_id))
1100  {
1101  $this->members_obj->assignMember($user_id);
1102  }
1103  // no submission (of blog/portfolio) yet (unless text assignment)
1104  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, (bool)$a_text);
1105  ilExerciseMembers::_writeReturned($this->getId(), $user_id, (bool)$a_text);
1106 
1107  return $next_id;
1108  }
1109 
1117  public function deleteResourceObject($a_ass_id, $user_id, $a_returned_id)
1118  {
1119  global $ilDB;
1120 
1121  $ilDB->manipulate("DELETE FROM exc_returned".
1122  " WHERE obj_id = ".$ilDB->quote($this->getId(), "integer").
1123  " AND user_id = ".$ilDB->quote($user_id, "integer").
1124  " AND ass_id = ".$ilDB->quote($a_ass_id, "integer").
1125  " AND returned_id = ".$ilDB->quote($a_returned_id, "integer"));
1126  }
1127 
1137  function updateTextSubmission($a_exc_id, $a_ass_id, $a_user_id, $a_text)
1138  {
1139  global $ilDB;
1140 
1141  $files = ilExAssignment::getDeliveredFiles($a_exc_id, $a_ass_id, $a_user_id);
1142 
1143  // no text = remove submission
1144  if(!trim($a_text))
1145  {
1146  if($files)
1147  {
1148  $files = array_shift($files);
1149  $id = $files["returned_id"];
1150  if($id)
1151  {
1152  $this->deleteDeliveredFiles($a_exc_id, $a_ass_id, array($id), $a_user_id);
1153  return;
1154  }
1155  }
1156  }
1157 
1158  if(!$files)
1159  {
1160  return $this->addResourceObject("TEXT", $a_ass_id, $a_user_id, $a_text);
1161  }
1162  else
1163  {
1164  $files = array_shift($files);
1165  $id = $files["returned_id"];
1166  if($id)
1167  {
1168  $ilDB->manipulate("UPDATE exc_returned".
1169  " SET atext = ".$ilDB->quote($a_text, "text").
1170  ", ts = ".$ilDB->quote(ilUtil::now(), "timestamp").
1171  " WHERE returned_id = ".$ilDB->quote($id, "integer"));
1172  return $id;
1173  }
1174  }
1175  }
1176 
1177  public static function lookupExerciseIdForReturnedId($a_returned_id)
1178  {
1179  global $ilDB;
1180 
1181  $set = $ilDB->query("SELECT obj_id".
1182  " FROM exc_returned".
1183  " WHERE returned_id = ".$ilDB->quote($a_returned_id, "integer"));
1184  $row = $ilDB->fetchAssoc($set);
1185  return (int)$row["obj_id"];
1186  }
1187 
1193  function deleteAllDeliveredFilesOfUser($a_user_id)
1194  {
1195  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
1196  ilExAssignment::deleteAllDeliveredFilesOfUser($this->getId(), $a_user_id);
1197  }
1198 
1205  public static function findUserFiles($a_user_id, $a_filetitle)
1206  {
1207  global $ilDB;
1208 
1209  $set = $ilDB->query("SELECT obj_id, ass_id".
1210  " FROM exc_returned".
1211  " WHERE user_id = ".$ilDB->quote($a_user_id, "integer").
1212  " AND filetitle = ".$ilDB->quote($a_filetitle, "text"));
1213  $res = array();
1214  while($row = $ilDB->fetchAssoc($set))
1215  {
1216  $res[$row["ass_id"]] = $row;
1217  }
1218  return $res;
1219  }
1220 
1227  function getCertificateVisibility()
1228  {
1229  return (strlen($this->certificate_visibility)) ? $this->certificate_visibility : 0;
1230  }
1231 
1238  function setCertificateVisibility($a_value)
1239  {
1240  $this->certificate_visibility = $a_value;
1241  }
1242 
1249  function saveCertificateVisibility($a_value)
1250  {
1251  global $ilDB;
1252 
1253  $affectedRows = $ilDB->manipulateF("UPDATE exc_data SET certificate_visibility = %s WHERE obj_id = %s",
1254  array('integer', 'integer'),
1255  array($a_value, $this->getId())
1256  );
1257  }
1258 
1265  function hasUserCertificate($a_user_id)
1266  {
1267  // show certificate?
1268  include_once "Services/Certificate/classes/class.ilCertificate.php";
1269  if(ilCertificate::isActive() && ilCertificate::isObjectActive($this->getId()))
1270  {
1271  $certificate_visible = $this->getCertificateVisibility();
1272  // if not never
1273  if($certificate_visible != 2)
1274  {
1275  // if passed only
1276  include_once 'Modules/Exercise/classes/class.ilExerciseMembers.php';
1277  $status = ilExerciseMembers::_lookupStatus($this->getId(), $a_user_id);
1278  if($certificate_visible == 1 && $status == "passed")
1279  {
1280  return true;
1281  }
1282  // always (excluding notgraded)
1283  else if($certificate_visible == 0 && $status != "notgraded")
1284  {
1285  return true;
1286  }
1287  }
1288  }
1289  return false;
1290  }
1291 }
1292 
1293 ?>