ILIAS  Release_5_0_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.ilExerciseMembers.php";
6 
19 class ilObjExercise extends ilObject
20 {
21  var $file_obj;
22  var $members_obj;
23  var $files;
24 
25  var $timestamp;
26  var $hour;
27  var $minutes;
28  var $day;
29  var $month;
30  var $year;
31  var $instruction;
32  var $certificate_visibility;
33 
42  protected $completion_by_submission = false;
43 
50  function ilObjExercise($a_id = 0,$a_call_by_reference = true)
51  {
52  $this->setPassMode("all");
53  $this->type = "exc";
54  $this->ilObject($a_id,$a_call_by_reference);
55  }
56 
57  // SET, GET METHODS
58  function setDate($a_hour,$a_minutes,$a_day,$a_month,$a_year)
59  {
60  $this->hour = (int) $a_hour;
61  $this->minutes = (int) $a_minutes;
62  $this->day = (int) $a_day;
63  $this->month = (int) $a_month;
64  $this->year = (int) $a_year;
65  $this->timestamp = mktime($this->hour,$this->minutes,0,$this->month,$this->day,$this->year);
66  return true;
67  }
68  function getTimestamp()
69  {
70  return $this->timestamp;
71  }
72  function setTimestamp($a_timestamp)
73  {
74  $this->timestamp = $a_timestamp;
75  }
76  function setInstruction($a_instruction)
77  {
78  $this->instruction = $a_instruction;
79  }
80  function getInstruction()
81  {
82  return $this->instruction;
83  }
84 
90  function setPassMode($a_val)
91  {
92  $this->pass_mode = $a_val;
93  }
94 
100  function getPassMode()
101  {
102  return $this->pass_mode;
103  }
104 
110  function setPassNr($a_val)
111  {
112  $this->pass_nr = $a_val;
113  }
114 
120  function getPassNr()
121  {
122  return $this->pass_nr;
123  }
124 
130  function setShowSubmissions($a_val)
131  {
132  $this->show_submissions = $a_val;
133  }
134 
140  function getShowSubmissions()
141  {
142  return $this->show_submissions;
143  }
144 
145 
146 /* function getFiles()
147  {
148  return $this->files;
149  }*/
150 
151  function checkDate()
152  {
153  return $this->hour == (int) date("H",$this->timestamp) and
154  $this->minutes == (int) date("i",$this->timestamp) and
155  $this->day == (int) date("d",$this->timestamp) and
156  $this->month == (int) date("m",$this->timestamp) and
157  $this->year == (int) date("Y",$this->timestamp);
158 
159  }
160 
164  function deliverFile($a_http_post_files, $a_ass_id, $user_id, $unzip = false)
165  {
166  global $ilDB;
167 
168  include_once("./Modules/Exercise/classes/class.ilFSStorageExercise.php");
169  $storage = new ilFSStorageExercise($this->getId(), $a_ass_id);
170  $deliver_result = $storage->deliverFile($a_http_post_files, $user_id, $unzip);
171 //var_dump($deliver_result);
172  if ($deliver_result)
173  {
174  $next_id = $ilDB->nextId("exc_returned");
175  $query = sprintf("INSERT INTO exc_returned ".
176  "(returned_id, obj_id, user_id, filename, filetitle, mimetype, ts, ass_id) ".
177  "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
178  $ilDB->quote($next_id, "integer"),
179  $ilDB->quote($this->getId(), "integer"),
180  $ilDB->quote($user_id, "integer"),
181  $ilDB->quote($deliver_result["fullname"], "text"),
182  $ilDB->quote($a_http_post_files["name"], "text"),
183  $ilDB->quote($deliver_result["mimetype"], "text"),
184  $ilDB->quote(ilUtil::now(), "timestamp"),
185  $ilDB->quote($a_ass_id, "integer")
186  );
187  $ilDB->manipulate($query);
188 
189  // team upload?
190  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($a_ass_id, $user_id);
191  if(!$user_ids)
192  {
193  $user_ids = array($user_id);
194  }
195  else
196  {
197  $team_id = ilExAssignment::getTeamIdByAssignment($a_ass_id, $user_id);
198  ilExAssignment::writeTeamLog($team_id, ilExAssignment::TEAM_LOG_ADD_FILE, $a_http_post_files["name"]);
199  }
200 
201  foreach($user_ids as $user_id)
202  {
203  if (!$this->members_obj->isAssigned($user_id))
204  {
205  $this->members_obj->assignMember($user_id);
206  }
207  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, 1);
208  ilExerciseMembers::_writeReturned($this->getId(), $user_id, 1);
209  }
210  }
211  return true;
212  }
213 
217  function addUploadedFile($a_http_post_files, $unzipUploadedFile = false)
218  {
219  global $lng;
220  if ($unzipUploadedFile && preg_match("/zip/", $a_http_post_files["type"]) == 1)
221  {
222 
223  $this->processUploadedFile($a_http_post_files["tmp_name"], "storeUploadedFile", true);
224  return true;
225 
226 
227  }
228  else
229  {
230  $this->file_obj->storeUploadedFile($a_http_post_files, true);
231  return true;
232  }
233  }
234  function deleteFiles($a_files)
235  {
236  $this->file_obj->unlinkFiles($a_files);
237  }
238 
239  function saveData()
240  {
241  global $ilDB;
242 
243  // SAVE ONLY EXERCISE SPECIFIC DATA
244  /*$query = "INSERT INTO exc_data SET ".
245  "obj_id = ".$ilDB->quote($this->getId()).", ".
246  "instruction = ".$ilDB->quote($this->getInstruction()).", ".
247  "time_stamp = ".$ilDB->quote($this->getTimestamp());
248  $this->ilias->db->query($query);*/
249 
250  $ilDB->insert("exc_data", array(
251  "obj_id" => array("integer", $this->getId()),
252  "instruction" => array("clob", $this->getInstruction()),
253  "time_stamp" => array("integer", $this->getTimestamp()),
254  "pass_mode" => array("text", $this->getPassMode()),
255  "pass_nr" => array("text", $this->getPassNr()),
256  "show_submissions" => array("integer", (int) $this->getShowSubmissions()),
257  'compl_by_submission' => array('integer', (int)$this->isCompletionBySubmissionEnabled()),
258  "certificate_visibility" => array("integer", (int)$this->getCertificateVisibility())
259  ));
260  return true;
261  }
262 
270  public function cloneObject($a_target_id,$a_copy_id = 0)
271  {
272  global $ilDB;
273 
274  // Copy settings
275  $new_obj = parent::cloneObject($a_target_id,$a_copy_id);
276  $new_obj->setInstruction($this->getInstruction());
277  $new_obj->setTimestamp($this->getTimestamp());
278  $new_obj->setPassMode($this->getPassMode());
279  $new_obj->saveData();
280  $new_obj->setPassNr($this->getPassNr());
281  $new_obj->setShowSubmissions($this->getShowSubmissions());
282  $new_obj->setCompletionBySubmission($this->isCompletionBySubmissionEnabled());
283 
284 
285  $new_obj->update();
286 
287  // Copy assignments
288  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
289  ilExAssignment::cloneAssignmentsOfExercise($this->getId(), $new_obj->getId());
290 
291  // Copy learning progress settings
292  include_once('Services/Tracking/classes/class.ilLPObjSettings.php');
293  $obj_settings = new ilLPObjSettings($this->getId());
294  $obj_settings->cloneSettings($new_obj->getId());
295  unset($obj_settings);
296 
297  return $new_obj;
298  }
299 
306  function deleteDeliveredFiles($a_exc_id, $a_ass_id, $file_id_array, $user_id)
307  {
308  ilExAssignment::deleteDeliveredFiles($a_exc_id, $a_ass_id, $file_id_array, $user_id);
309 
310  // Finally update status 'returned' of member if no file exists
311  if(!count(ilExAssignment::getDeliveredFiles($a_exc_id, $a_ass_id, $user_id)))
312  {
313  // team upload?
314  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($a_ass_id, $user_id);
315  if(!$user_ids)
316  {
317  $user_ids = array($user_id);
318  }
319 
320  foreach($user_ids as $user_id)
321  {
322  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, 0);
323  }
324  }
325  }
326 
332  function deliverReturnedFiles($user_id)
333  {
334  require_once "./Services/Utilities/classes/class.ilUtil.php";
335  }
336 
343  function delete()
344  {
345  global $ilDB, $ilAppEventHandler;
346 
347  // always call parent delete function first!!
348  if (!parent::delete())
349  {
350  return false;
351  }
352  // put here course specific stuff
353  $ilDB->manipulate("DELETE FROM exc_data ".
354  "WHERE obj_id = ".$ilDB->quote($this->getId(), "integer"));
355 
356  //$this->ilias->db->query($query);
357 
358  //$this->file_obj->delete();
359  //$this->members_obj->delete();
360 
361  // remove all notifications
362  include_once "./Services/Notification/classes/class.ilNotification.php";
363  ilNotification::removeForObject(ilNotification::TYPE_EXERCISE_SUBMISSION, $this->getId());
364 
365  $ilAppEventHandler->raise('Modules/Exercise',
366  'delete',
367  array('obj_id'=>$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  return true;
419  }
420 
421  function update()
422  {
423  global $ilDB;
424 
425  parent::update();
426 
427  /*$query = "UPDATE exc_data SET ".
428  "instruction = ".$ilDB->quote($this->getInstruction()).", ".
429  "time_stamp = ".$ilDB->quote($this->getTimestamp())." ".
430  "WHERE obj_id = ".$ilDB->quote($this->getId());
431  */
432 
433  if ($this->getPassMode() == "all")
434  {
435  $pass_nr = null;
436  }
437  else
438  {
439  $pass_nr = $this->getPassNr();
440  }
441 
442  $ilDB->update("exc_data", array(
443  "instruction" => array("clob", $this->getInstruction()),
444  "time_stamp" => array("integer", $this->getTimestamp()),
445  "pass_mode" => array("text", $this->getPassMode()),
446  "pass_nr" => array("integer", $this->getPassNr()),
447  "show_submissions" => array("integer", (int) $this->getShowSubmissions()),
448  'compl_by_submission' => array('integer', (int)$this->isCompletionBySubmissionEnabled())
449  ), array(
450  "obj_id" => array("integer", $this->getId())
451  ));
452 
453  $this->updateAllUsersStatus();
454 
455  //$res = $this->ilias->db->query($query);
456 
457  #$this->members_obj->update();
458  return true;
459  }
460 
464  function sendAssignment($a_exc_id, $a_ass_id, $a_members)
465  {
466  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
467  $ass_title = ilExAssignment::lookupTitle($a_ass_id);
468 
469  include_once("./Modules/Exercise/classes/class.ilFSStorageExercise.php");
470  $storage = new ilFSStorageExercise($a_exc_id, $a_ass_id);
471  $files = $storage->getFiles();
472 
473  if(count($files))
474  {
475  include_once "./Services/Mail/classes/class.ilFileDataMail.php";
476 
477  $mfile_obj = new ilFileDataMail($_SESSION["AccountId"]);
478  foreach($files as $file)
479  {
480  $mfile_obj->copyAttachmentFile($file["fullpath"], $file["name"]);
481  $file_names[] = $file["name"];
482  }
483  }
484 
485  include_once "Services/Mail/classes/class.ilMail.php";
486 
487  $tmp_mail_obj = new ilMail($_SESSION["AccountId"]);
488  $message = $tmp_mail_obj->sendMail(
489  $this->__formatRecipients($a_members),"","",
490  $this->__formatSubject($ass_title), $this->__formatBody($a_ass_id),
491  count($file_names) ? $file_names : array(),array("normal"));
492 
493  unset($tmp_mail_obj);
494 
495  if(count($file_names))
496  {
497  $mfile_obj->unlinkFiles($file_names);
498  unset($mfile_obj);
499  }
500 
501 
502  // SET STATUS SENT FOR ALL RECIPIENTS
503  foreach($a_members as $member_id => $value)
504  {
505  ilExAssignment::updateStatusSentForUser($a_ass_id, $member_id, 1);
506  }
507 
508  return true;
509  }
510 
514  function _lookupStatusTime($exc_id, $member_id)
515  {
516 
517  global $ilDB, $lng;
518 
519  $q = "SELECT * ".
520  "FROM exc_members ".
521  "WHERE obj_id= ".$ilDB->quote($exc_id, "integer").
522  " AND usr_id= ".$ilDB->quote($member_id, "integer");
523 
524  $set = $ilDB->query($q);
525  if ($rec = $ilDB->fetchAssoc($set))
526  {
527  return ilUtil::getMySQLTimestamp($rec["status_time"]);
528  }
529  }
530 
531  // PRIVATE METHODS
532  function __formatBody($a_ass_id)
533  {
534  global $lng;
535 
536  $lng->loadLanguageModule("exc");
537 
538  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
539  $ass = new ilExAssignment($a_ass_id);
540 
541  $body = $ass->getInstruction();
542  $body .= "\n\n";
543  if ($ass->getDeadline() == 0)
544  {
545  $body .= $lng->txt("exc_edit_until") . ": ".
546  $lng->txt("exc_no_deadline_specified");
547  }
548  else
549  {
550  $body .= $lng->txt("exc_edit_until") . ": ".
551  ilFormat::formatDate(date("Y-m-d H:i:s",$ass->getDeadline()), "datetime", true);
552  }
553  $body .= "\n\n";
554  $body .= ILIAS_HTTP_PATH.
555  "/goto.php?target=".
556  $this->getType().
557  "_".$this->getRefId()."&client_id=".CLIENT_ID;
558 
559  return $body;
560  }
561 
562  function __formatSubject($a_ass_title = "")
563  {
564  $subject = $this->getTitle();
565 
566  if ($a_ass_title != "")
567  {
568  $subject.= ": ".$a_ass_title;
569  }
570 
571  return $subject;
572  }
573 
574  function __formatRecipients($a_members)
575  {
576  foreach($a_members as $member_id => $value)
577  {
578  $tmp_obj = ilObjectFactory::getInstanceByObjId($member_id);
579  $tmp_members[] = $tmp_obj->getLogin();
580 
581  unset($tmp_obj);
582  }
583 
584  return implode(',',$tmp_members ? $tmp_members : array());
585  }
586 
593  function processUploadedFile ($fileTmp, $storageMethod, $persistentErrorMessage,
594  $a_ass_id)
595  {
596  global $lng, $ilUser;
597 
598  // Create unzip-directory
599  $newDir = ilUtil::ilTempnam();
600  ilUtil::makeDir($newDir);
601 
602  include_once ("Services/Utilities/classes/class.ilFileUtils.php");
603 
604  try
605  {
606  $processDone = ilFileUtils::processZipFile($newDir,$fileTmp, false);
607  ilFileUtils::recursive_dirscan($newDir, $filearray);
608 
609  foreach ($filearray["file"] as $key => $filename)
610  {
611  $a_http_post_files["name"] = ilFileUtils::utf8_encode($filename);
612  $a_http_post_files["type"] = "other";
613  $a_http_post_files["tmp_name"] = $filearray["path"][$key]."/".$filename;
614  $a_http_post_files["error"] = 0;
615  $a_http_post_files["size"] = filesize($filearray["path"][$key]."/".$filename);
616 
617  if ($storageMethod == "deliverFile")
618  {
619  $this->$storageMethod($a_http_post_files, $a_ass_id, $ilUser->id, true);
620  }
621  else if ($storageMethod == "storeUploadedFile")
622  {
623  $this->file_obj->$storageMethod($a_http_post_files, true, true);
624  }
625  }
626  ilExerciseMembers::_writeReturned($this->getId(), $ilUser->id, 1);
627  ilUtil::sendSuccess($this->lng->txt("file_added"),$persistentErrorMessage);
628  }
629  catch (ilFileUtilsException $e)
630  {
631  ilUtil::sendFailure($e->getMessage(), $persistentErrorMessage);
632  }
633 
634 
635  ilUtil::delDir($newDir);
636  return $processDone;
637 
638  }
639 
650  static function _fixFilename($a_filename)
651  {
652  $ex_pos = strrpos($a_filename, "/exercise/");
653  if ($ex_pos > 0)
654  {
655  $a_filename = CLIENT_DATA_DIR.substr($a_filename, $ex_pos);
656  }
657  return $a_filename;
658  }
659 
664  static function _fixFilenameArray($a_array)
665  {
666  if (is_array($a_array))
667  {
668  foreach ($a_array as $k => $v)
669  {
670  if ($v["filename"] != "")
671  {
672  $a_array[$k]["filename"] = ilObjExercise::_fixFilename($a_array[$k]["filename"]);
673  }
674  }
675  }
676 
677  return $a_array;
678  }
679 
683  function determinStatusOfUser($a_user_id = 0)
684  {
685  global $ilUser;
686 
687  if ($a_user_id == 0)
688  {
689  $a_user_id = $ilUser->getId();
690  }
691 
692  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
693  $ass = ilExAssignment::getAssignmentDataOfExercise($this->getId());
694 
695  $passed_all_mandatory = true;
696  $failed_a_mandatory = false;
697  $cnt_passed = 0;
698  $cnt_notgraded = 0;
699  $passed_at_least_one = false;
700 
701  foreach ($ass as $a)
702  {
703  $stat = ilExAssignment::lookupStatusOfUser($a["id"], $a_user_id);
704  if ($a["mandatory"] && ($stat == "failed" || $stat == "notgraded"))
705  {
706  $passed_all_mandatory = false;
707  }
708  if ($a["mandatory"] && ($stat == "failed"))
709  {
710  $failed_a_mandatory = true;
711  }
712  if ($stat == "passed")
713  {
714  $cnt_passed++;
715  }
716  if ($stat == "notgraded")
717  {
718  $cnt_notgraded++;
719  }
720  }
721 
722  if (count($ass) == 0)
723  {
724  $passed_all_mandatory = false;
725  }
726 
727  if ($this->getPassMode() != "nr")
728  {
729 //echo "5";
730  $overall_stat = "notgraded";
731  if ($failed_a_mandatory)
732  {
733 //echo "6";
734  $overall_stat = "failed";
735  }
736  else if ($passed_all_mandatory && $cnt_passed > 0)
737  {
738 //echo "7";
739  $overall_stat = "passed";
740  }
741  }
742  else
743  {
744 //echo "8";
745  $min_nr = $this->getPassNr();
746  $overall_stat = "notgraded";
747 //echo "*".$cnt_passed."*".$cnt_notgraded."*".$min_nr."*";
748  if ($failed_a_mandatory || ($cnt_passed + $cnt_notgraded < $min_nr))
749  {
750 //echo "9";
751  $overall_stat = "failed";
752  }
753  else if ($passed_all_mandatory && $cnt_passed >= $min_nr)
754  {
755 //echo "A";
756  $overall_stat = "passed";
757  }
758  }
759 
760  $ret = array(
761  "overall_status" => $overall_stat,
762  "failed_a_mandatory" => $failed_a_mandatory);
763 //echo "<br>p:".$cnt_passed.":ng:".$cnt_notgraded;
764 //var_dump($ret);
765  return $ret;
766  }
767 
771  function updateUserStatus($a_user_id = 0)
772  {
773  global $ilUser;
774 
775  if ($a_user_id == 0)
776  {
777  $a_user_id = $ilUser->getId();
778  }
779 
780  $st = $this->determinStatusOfUser($a_user_id);
781 
782  include_once("./Modules/Exercise/classes/class.ilExerciseMembers.php");
783  ilExerciseMembers::_writeStatus($this->getId(), $a_user_id,
784  $st["overall_status"]);
785  }
786 
790  function updateAllUsersStatus()
791  {
792  if (!is_object($this->members_obj));
793  {
794  $this->members_obj = new ilExerciseMembers($this);
795  }
796 
797  $mems = $this->members_obj->getMembers();
798  foreach ($mems as $mem)
799  {
800  $this->updateUserStatus($mem);
801  }
802  }
803 
807  function exportGradesExcel()
808  {
809  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
810  $ass_data = ilExAssignment::getAssignmentDataOfExercise($this->getId());
811 
812  include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
813  $excelfile = ilUtil::ilTempnam();
814  $adapter = new ilExcelWriterAdapter($excelfile, FALSE);
815  $workbook = $adapter->getWorkbook();
816  $workbook->setVersion(8); // Use Excel97/2000 Format
817  include_once ("./Services/Excel/classes/class.ilExcelUtils.php");
818 
819  //
820  // status
821  //
822  $mainworksheet = $workbook->addWorksheet();
823 
824  // header row
825  $mainworksheet->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
826  $cnt = 1;
827  foreach ($ass_data as $ass)
828  {
829  $mainworksheet->writeString(0, $cnt, $cnt);
830  $cnt++;
831  }
832  $mainworksheet->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
833 
834  // data rows
835  $this->mem_obj = new ilExerciseMembers($this);
836  $getmems = $this->mem_obj->getMembers();
837  $mems = array();
838  foreach ($getmems as $user_id)
839  {
840  $mems[$user_id] = ilObjUser::_lookupName($user_id);
841  }
842  $mems = ilUtil::sortArray($mems, "lastname", "asc", false, true);
843 
844  $data = array();
845  $row_cnt = 1;
846  foreach ($mems as $user_id => $d)
847  {
848  $col_cnt = 1;
849 
850  // name
851  $mainworksheet->writeString($row_cnt, 0,
852  ilExcelUtils::_convert_text($d["lastname"].", ".$d["firstname"]." [".$d["login"]."]"));
853 
854  reset($ass_data);
855 
856  foreach ($ass_data as $ass)
857  {
858  $status = ilExAssignment::lookupStatusOfUser($ass["id"], $user_id);
859  $mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_".$status)));
860  $col_cnt++;
861  }
862 
863  // total status
864  $status = ilExerciseMembers::_lookupStatus($this->getId(), $user_id);
865  $mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_".$status)));
866 
867  $row_cnt++;
868  }
869 
870  //
871  // mark
872  //
873  $worksheet2 = $workbook->addWorksheet();
874 
875  // header row
876  $worksheet2->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
877  $cnt = 1;
878  foreach ($ass_data as $ass)
879  {
880  $worksheet2->writeString(0, $cnt, $cnt);
881  $cnt++;
882  }
883  $worksheet2->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
884 
885  // data rows
886  $data = array();
887  $row_cnt = 1;
888  reset($mems);
889  foreach ($mems as $user_id => $d)
890  {
891  $col_cnt = 1;
892  $d = ilObjUser::_lookupName($user_id);
893 
894  // name
895  $worksheet2->writeString($row_cnt, 0,
896  ilExcelUtils::_convert_text($d["lastname"].", ".$d["firstname"]." [".$d["login"]."]"));
897 
898  reset($ass_data);
899 
900  foreach ($ass_data as $ass)
901  {
902  $worksheet2->writeString($row_cnt, $col_cnt,
903  ilExcelUtils::_convert_text(ilExAssignment::lookupMarkOfUser($ass["id"], $user_id)));
904  $col_cnt++;
905  }
906 
907  // total mark
908  include_once 'Services/Tracking/classes/class.ilLPMarks.php';
909  $worksheet2->writeString($row_cnt, $col_cnt,
910  ilExcelUtils::_convert_text(ilLPMarks::_lookupMark($user_id, $this->getId())));
911 
912  $row_cnt++;
913  }
914 
915 
916  $workbook->close();
917  $exc_name = ilUtil::getASCIIFilename(preg_replace("/\s/", "_", $this->getTitle()));
918  ilUtil::deliverFile($excelfile, $exc_name.".xls", "application/vnd.ms-excel");
919  }
920 
924  function sendFeedbackFileNotification($a_feedback_file, $a_user_id, $a_ass_id, $a_is_text_feedback = false)
925  {
926  $user_ids = $a_user_id;
927  if(!is_array($user_ids))
928  {
929  $user_ids = array($user_ids);
930  }
931 
932  include_once("./Modules/Exercise/classes/class.ilExerciseMailNotification.php");
933 
934  $type = (bool)$a_is_text_feedback
935  ? ilExerciseMailNotification::TYPE_FEEDBACK_TEXT_ADDED
936  : ilExerciseMailNotification::TYPE_FEEDBACK_FILE_ADDED;
937 
938  $not = new ilExerciseMailNotification();
939  $not->setType($type);
940  $not->setAssignmentId($a_ass_id);
941  $not->setObjId($this->getId());
942  if ($this->getRefId() > 0)
943  {
944  $not->setRefId($this->getRefId());
945  }
946  $not->setRecipients($user_ids);
947  $not->send();
948  }
949 
958  public function isCompletionBySubmissionEnabled()
959  {
960  return $this->completion_by_submission;
961  }
962 
972  public function setCompletionBySubmission($bool)
973  {
974  $this->completion_by_submission = (bool)$bool;
975 
976  return $this;
977  }
978 
989  public function handleSubmission($ass_id)
990  {
991  global $ilUser, $ilDB;
992 
993  if($this->isCompletionBySubmissionEnabled())
994  {
995  include_once 'Modules/Exercise/classes/class.ilExAssignment.php';
996 
997  // #17673 - team upload?
998  $user_ids = ilExAssignment::getTeamMembersByAssignmentId($ass_id, $ilUser->getId());
999  if(!$user_ids)
1000  {
1001  $user_ids = array($ilUser->getId());
1002  }
1003 
1004  $res = $ilDB->query(
1005  'SELECT returned_id'.
1006  ' FROM exc_returned'.
1007  ' WHERE obj_id = '.$ilDB->quote($this->getId(), 'integer').
1008  ' AND ass_id = '.$ilDB->quote($ass_id, 'integer').
1009  ' AND '.$ilDB->in('user_id', $user_ids, '', 'integer')
1010  );
1011 
1012  if($ilDB->numRows($res))
1013  {
1014  $status = 'passed';
1015  }
1016  else
1017  {
1018  $status = 'notgraded';
1019  }
1020  foreach($user_ids as $user_id)
1021  {
1022  ilExAssignment::updateStatusOfUser($ass_id, $user_id, $status);
1023  }
1024  }
1025  }
1026 
1033  public static function _lookupFinishedUserExercises($a_user_id)
1034  {
1035  global $ilDB;
1036 
1037  $set = $ilDB->query("SELECT obj_id, status FROM exc_members".
1038  " WHERE usr_id = ".$ilDB->quote($a_user_id, "integer").
1039  " AND (status = ".$ilDB->quote("passed", "text").
1040  " OR status = ".$ilDB->quote("failed", "text").")");
1041 
1042  $all = array();
1043  while($row = $ilDB->fetchAssoc($set))
1044  {
1045  $all[$row["obj_id"]] = ($row["status"] == "passed");
1046  }
1047  return $all;
1048  }
1049 
1058  function addResourceObject($a_wsp_id, $a_ass_id, $user_id, $a_text = null)
1059  {
1060  global $ilDB;
1061 
1062  $next_id = $ilDB->nextId("exc_returned");
1063  $query = sprintf("INSERT INTO exc_returned ".
1064  "(returned_id, obj_id, user_id, filetitle, ass_id, ts, atext) ".
1065  "VALUES (%s, %s, %s, %s, %s, %s, %s)",
1066  $ilDB->quote($next_id, "integer"),
1067  $ilDB->quote($this->getId(), "integer"),
1068  $ilDB->quote($user_id, "integer"),
1069  $ilDB->quote($a_wsp_id, "text"),
1070  $ilDB->quote($a_ass_id, "integer"),
1071  $ilDB->quote(ilUtil::now(), "timestamp"),
1072  $ilDB->quote($a_text, "text")
1073  );
1074  $ilDB->manipulate($query);
1075  if (!$this->members_obj->isAssigned($user_id))
1076  {
1077  $this->members_obj->assignMember($user_id);
1078  }
1079  // no submission (of blog/portfolio) yet (unless text assignment)
1080  ilExAssignment::updateStatusReturnedForUser($a_ass_id, $user_id, (bool)$a_text);
1081  ilExerciseMembers::_writeReturned($this->getId(), $user_id, (bool)$a_text);
1082 
1083  return $next_id;
1084  }
1085 
1093  public function deleteResourceObject($a_ass_id, $user_id, $a_returned_id)
1094  {
1095  global $ilDB;
1096 
1097  $ilDB->manipulate("DELETE FROM exc_returned".
1098  " WHERE obj_id = ".$ilDB->quote($this->getId(), "integer").
1099  " AND user_id = ".$ilDB->quote($user_id, "integer").
1100  " AND ass_id = ".$ilDB->quote($a_ass_id, "integer").
1101  " AND returned_id = ".$ilDB->quote($a_returned_id, "integer"));
1102  }
1103 
1113  function updateTextSubmission($a_exc_id, $a_ass_id, $a_user_id, $a_text)
1114  {
1115  global $ilDB;
1116 
1117  $files = ilExAssignment::getDeliveredFiles($a_exc_id, $a_ass_id, $a_user_id);
1118 
1119  // no text = remove submission
1120  if(!trim($a_text))
1121  {
1122  if($files)
1123  {
1124  $files = array_shift($files);
1125  $id = $files["returned_id"];
1126  if($id)
1127  {
1128  $this->deleteDeliveredFiles($a_exc_id, $a_ass_id, array($id), $a_user_id);
1129  return;
1130  }
1131  }
1132  }
1133 
1134  if(!$files)
1135  {
1136  return $this->addResourceObject("TEXT", $a_ass_id, $a_user_id, $a_text);
1137  }
1138  else
1139  {
1140  $files = array_shift($files);
1141  $id = $files["returned_id"];
1142  if($id)
1143  {
1144  $ilDB->manipulate("UPDATE exc_returned".
1145  " SET atext = ".$ilDB->quote($a_text, "text").
1146  ", ts = ".$ilDB->quote(ilUtil::now(), "timestamp").
1147  " WHERE returned_id = ".$ilDB->quote($id, "integer"));
1148  return $id;
1149  }
1150  }
1151  }
1152 
1153  public static function lookupExerciseIdForReturnedId($a_returned_id)
1154  {
1155  global $ilDB;
1156 
1157  $set = $ilDB->query("SELECT obj_id".
1158  " FROM exc_returned".
1159  " WHERE returned_id = ".$ilDB->quote($a_returned_id, "integer"));
1160  $row = $ilDB->fetchAssoc($set);
1161  return (int)$row["obj_id"];
1162  }
1163 
1169  function deleteAllDeliveredFilesOfUser($a_user_id)
1170  {
1171  include_once("./Modules/Exercise/classes/class.ilExAssignment.php");
1172  ilExAssignment::deleteAllDeliveredFilesOfUser($this->getId(), $a_user_id);
1173  }
1174 
1181  public static function findUserFiles($a_user_id, $a_filetitle)
1182  {
1183  global $ilDB;
1184 
1185  $set = $ilDB->query("SELECT obj_id, ass_id".
1186  " FROM exc_returned".
1187  " WHERE user_id = ".$ilDB->quote($a_user_id, "integer").
1188  " AND filetitle = ".$ilDB->quote($a_filetitle, "text"));
1189  $res = array();
1190  while($row = $ilDB->fetchAssoc($set))
1191  {
1192  $res[$row["ass_id"]] = $row;
1193  }
1194  return $res;
1195  }
1196 
1203  function getCertificateVisibility()
1204  {
1205  return (strlen($this->certificate_visibility)) ? $this->certificate_visibility : 0;
1206  }
1207 
1214  function setCertificateVisibility($a_value)
1215  {
1216  $this->certificate_visibility = $a_value;
1217  }
1218 
1225  function saveCertificateVisibility($a_value)
1226  {
1227  global $ilDB;
1228 
1229  $affectedRows = $ilDB->manipulateF("UPDATE exc_data SET certificate_visibility = %s WHERE obj_id = %s",
1230  array('integer', 'integer'),
1231  array($a_value, $this->getId())
1232  );
1233  }
1234 
1241  function hasUserCertificate($a_user_id)
1242  {
1243  // show certificate?
1244  include_once "Services/Certificate/classes/class.ilCertificate.php";
1245  if(ilCertificate::isActive() && ilCertificate::isObjectActive($this->getId()))
1246  {
1247  $certificate_visible = $this->getCertificateVisibility();
1248  // if not never
1249  if($certificate_visible != 2)
1250  {
1251  // if passed only
1252  include_once 'Modules/Exercise/classes/class.ilExerciseMembers.php';
1253  $status = ilExerciseMembers::_lookupStatus($this->getId(), $a_user_id);
1254  if($certificate_visible == 1 && $status == "passed")
1255  {
1256  return true;
1257  }
1258  // always (excluding notgraded)
1259  else if($certificate_visible == 0 && $status != "notgraded")
1260  {
1261  return true;
1262  }
1263  }
1264  }
1265  return false;
1266  }
1267 
1273  function hasAddToDesktop()
1274  {
1275  $exc_set = new ilSetting("excs");
1276  return (bool)$exc_set->get("add_to_pd", true);
1277  }
1278 }
1279 
1280 ?>