1<?php 2require_once 'Modules/IndividualAssessment/interfaces/Members/interface.ilIndividualAssessmentMembersStorage.php'; 3require_once 'Modules/IndividualAssessment/classes/Members/class.ilIndividualAssessmentMembers.php'; 4require_once 'Modules/IndividualAssessment/classes/Members/class.ilIndividualAssessmentMember.php'; 5require_once 'Modules/IndividualAssessment/classes/class.ilObjIndividualAssessment.php'; 6/** 7 * Store member infos to DB 8 * 9 * @author Denis Klöpfer <denis.kloepfer@concepts-and-training.de> 10 * @author Stefan Hecken <stefan.hecken@concepts-and-training.de> 11 * 12 * @inheritdoc 13 */ 14class ilIndividualAssessmentMembersStorageDB implements ilIndividualAssessmentMembersStorage 15{ 16 const MEMBERS_TABLE = "iass_members"; 17 18 protected $db; 19 20 public function __construct($ilDB) 21 { 22 $this->db = $ilDB; 23 } 24 25 /** 26 * @inheritdoc 27 */ 28 public function loadMembers(ilObjIndividualAssessment $obj) 29 { 30 $members = new ilIndividualAssessmentMembers($obj); 31 $obj_id = $obj->getId(); 32 $sql = $this->loadMembersQuery($obj_id); 33 $res = $this->db->query($sql); 34 while ($rec = $this->db->fetchAssoc($res)) { 35 $members = $members->withAdditionalRecord($rec); 36 } 37 return $members; 38 } 39 40 /** 41 * @inheritdoc 42 */ 43 public function loadMembersAsSingleObjects( 44 ilObjIndividualAssessment $obj, 45 string $filter = null, 46 string $sort = null 47 ) : array { 48 $members = []; 49 $sql = $this->loadMemberQuery(); 50 $sql .= " WHERE obj_id = " . $this->db->quote($obj->getId(), 'integer'); 51 52 if (!is_null($filter)) { 53 $sql .= $this->getWhereFromFilter($filter); 54 } 55 56 if (!is_null($sort)) { 57 $sql .= $this->getOrderByFromSort($sort); 58 } 59 $res = $this->db->query($sql); 60 while ($rec = $this->db->fetchAssoc($res)) { 61 $usr = new ilObjUser($rec["usr_id"]); 62 $members[] = $this->createAssessmentMember($obj, $usr, $rec); 63 } 64 return $members; 65 } 66 67 /** 68 * @inheritdoc 69 */ 70 public function loadMember(ilObjIndividualAssessment $obj, ilObjUser $usr) 71 { 72 $obj_id = $obj->getId(); 73 $usr_id = $usr->getId(); 74 $sql = $this->loadMemberQuery(); 75 $sql .= " WHERE obj_id = " . $this->db->quote($obj_id, 'integer') . "\n" 76 . " AND iassme.usr_id = " . $this->db->quote($usr_id, 'integer'); 77 78 $rec = $this->db->fetchAssoc($this->db->query($sql)); 79 if ($rec) { 80 return $this->createAssessmentMember($obj, $usr, $rec); 81 } else { 82 throw new ilIndividualAssessmentException("invalid usr-obj combination"); 83 } 84 } 85 86 protected function createAssessmentMember( 87 ilObjIndividualAssessment $obj, 88 ilObjUser $usr, 89 array $record 90 ) : ilIndividualAssessmentMember { 91 $changer_id = $record[ilIndividualAssessmentMembers::FIELD_CHANGER_ID]; 92 if (!is_null($changer_id)) { 93 $changer_id = (int) $changer_id; 94 } 95 $change_time = null; 96 $change_time_db = $record[ilIndividualAssessmentMembers::FIELD_CHANGE_TIME]; 97 if (!is_null($change_time_db)) { 98 $change_time = new DateTime($change_time_db); 99 } 100 $examiner_id = $record[ilIndividualAssessmentMembers::FIELD_EXAMINER_ID]; 101 if (!is_null($examiner_id)) { 102 $examiner_id = (int) $examiner_id; 103 } 104 return new ilIndividualAssessmentMember( 105 $obj, 106 $usr, 107 $this->createGrading($record, $usr->getFullname()), 108 $examiner_id, 109 (int) $record[ilIndividualAssessmentMembers::FIELD_NOTIFICATION_TS], 110 $changer_id, 111 $change_time 112 ); 113 } 114 115 protected function createGrading(array $record, string $user_fullname) : ilIndividualAssessmentUserGrading 116 { 117 $event_time = null; 118 $event_time_db = $record[ilIndividualAssessmentMembers::FIELD_EVENTTIME]; 119 if (!is_null($event_time_db)) { 120 $event_time = new DateTimeImmutable(); 121 $event_time = $event_time->setTimestamp($event_time_db); 122 } 123 return new ilIndividualAssessmentUserGrading( 124 $user_fullname, 125 (string) $record[ilIndividualAssessmentMembers::FIELD_RECORD], 126 (string) $record[ilIndividualAssessmentMembers::FIELD_INTERNAL_NOTE], 127 (string) $record[ilIndividualAssessmentMembers::FIELD_FILE_NAME], 128 (bool) $record[ilIndividualAssessmentMembers::FIELD_USER_VIEW_FILE], 129 (string) $record[ilIndividualAssessmentMembers::FIELD_LEARNING_PROGRESS], 130 (string) $record[ilIndividualAssessmentMembers::FIELD_PLACE], 131 $event_time, 132 (bool) $record[ilIndividualAssessmentMembers::FIELD_NOTIFY], 133 (bool) $record[ilIndividualAssessmentMembers::FIELD_FINALIZED] 134 ); 135 } 136 137 /** 138 * @inheritdoc 139 */ 140 public function updateMember(ilIndividualAssessmentMember $member) 141 { 142 $where = array("obj_id" => array("integer", $member->assessmentId()) 143 , "usr_id" => array("integer", $member->id()) 144 ); 145 $event_time = $member->eventTime(); 146 if (!is_null($event_time)) { 147 $event_time = $event_time->getTimestamp(); 148 } 149 150 $values = [ 151 ilIndividualAssessmentMembers::FIELD_LEARNING_PROGRESS => array("text", $member->LPStatus()), 152 ilIndividualAssessmentMembers::FIELD_EXAMINER_ID => array("integer", $member->examinerId()), 153 ilIndividualAssessmentMembers::FIELD_RECORD => array("text", $member->record()), 154 ilIndividualAssessmentMembers::FIELD_INTERNAL_NOTE => array("text", $member->internalNote()), 155 ilIndividualAssessmentMembers::FIELD_PLACE => array("text", $member->place()), 156 ilIndividualAssessmentMembers::FIELD_EVENTTIME => array("integer", $event_time), 157 ilIndividualAssessmentMembers::FIELD_NOTIFY => array("integer", $member->notify()), 158 ilIndividualAssessmentMembers::FIELD_FINALIZED => array("integer", $member->finalized()), 159 ilIndividualAssessmentMembers::FIELD_NOTIFICATION_TS => array("integer", $member->notificationTS()), 160 ilIndividualAssessmentMembers::FIELD_FILE_NAME => array("text", $member->fileName()), 161 ilIndividualAssessmentMembers::FIELD_USER_VIEW_FILE => array("integer", $member->viewFile()), 162 ilIndividualAssessmentMembers::FIELD_CHANGER_ID => array("integer", $member->changerId()), 163 ilIndividualAssessmentMembers::FIELD_CHANGE_TIME => array("string", date("Y-m-d H:i:s")) 164 ]; 165 166 $this->db->update(self::MEMBERS_TABLE, $values, $where); 167 } 168 169 /** 170 * @inheritdoc 171 */ 172 public function deleteMembers(ilObjIndividualAssessment $obj) 173 { 174 $sql = "DELETE FROM " . self::MEMBERS_TABLE . " WHERE obj_id = " . $this->db->quote($obj->getId(), 'integer'); 175 $this->db->manipulate($sql); 176 } 177 178 protected function loadMemberQuery() 179 { 180 return "SELECT " 181 . "iassme.obj_id," 182 . "iassme.usr_id," 183 . "iassme.examiner_id," 184 . "iassme.record," 185 . "iassme.internal_note," 186 . "iassme.notify," 187 . "iassme.notification_ts," 188 . "iassme.learning_progress," 189 . "iassme.finalized," 190 . "iassme.place," 191 . "iassme.event_time," 192 . "iassme.user_view_file," 193 . "iassme.file_name," 194 . "iassme.changer_id," 195 . "iassme.change_time," 196 . "usr.lastname AS user_lastname," 197 . "ex.login AS examiner_login" 198 . " FROM " . self::MEMBERS_TABLE . " iassme\n" 199 . " JOIN usr_data usr ON iassme.usr_id = usr.usr_id\n" 200 . " LEFT JOIN usr_data ex ON iassme.examiner_id = ex.usr_id\n" 201 ; 202 } 203 204 /** 205 * @inheritdoc 206 */ 207 protected function loadMembersQuery($obj_id) 208 { 209 return "SELECT ex.firstname as " . ilIndividualAssessmentMembers::FIELD_EXAMINER_FIRSTNAME 210 . " , ex.lastname as " . ilIndividualAssessmentMembers::FIELD_EXAMINER_LASTNAME 211 . " , ud.firstname as " . ilIndividualAssessmentMembers::FIELD_CHANGER_FIRSTNAME 212 . " , ud.lastname as " . ilIndividualAssessmentMembers::FIELD_CHANGER_LASTNAME 213 . " ,usr.firstname as " . ilIndividualAssessmentMembers::FIELD_FIRSTNAME 214 . " ,usr.lastname as " . ilIndividualAssessmentMembers::FIELD_LASTNAME 215 . " ,usr.login as " . ilIndividualAssessmentMembers::FIELD_LOGIN 216 . " ,iassme." . ilIndividualAssessmentMembers::FIELD_FILE_NAME 217 . " ,iassme.obj_id, iassme.usr_id, iassme.examiner_id, iassme.record, iassme.internal_note, iassme.notify" 218 . " ,iassme.notification_ts, iassme.learning_progress, iassme.finalized,iassme.place" 219 . " ,iassme.event_time, iassme.changer_id, iassme.change_time\n" 220 . " FROM iass_members iassme" 221 . " JOIN usr_data usr ON iassme.usr_id = usr.usr_id" 222 . " LEFT JOIN usr_data ex ON iassme.examiner_id = ex.usr_id" 223 . " LEFT JOIN usr_data ud ON iassme.changer_id = ud.usr_id" 224 . " WHERE obj_id = " . $this->db->quote($obj_id, 'integer'); 225 } 226 227 /** 228 * @inheritdoc 229 */ 230 public function insertMembersRecord(ilObjIndividualAssessment $iass, array $record) 231 { 232 $values = array("obj_id" => array("integer", $iass->getId()) 233 , "usr_id" => array("integer", $record[ilIndividualAssessmentMembers::FIELD_USR_ID]) 234 , ilIndividualAssessmentMembers::FIELD_LEARNING_PROGRESS => array("text", $record[ilIndividualAssessmentMembers::FIELD_LEARNING_PROGRESS]) 235 , ilIndividualAssessmentMembers::FIELD_EXAMINER_ID => array("integer", $record[ilIndividualAssessmentMembers::FIELD_EXAMINER_ID]) 236 , ilIndividualAssessmentMembers::FIELD_RECORD => array("text", $record[ilIndividualAssessmentMembers::FIELD_RECORD]) 237 , ilIndividualAssessmentMembers::FIELD_INTERNAL_NOTE => array("text", $record[ilIndividualAssessmentMembers::FIELD_INTERNAL_NOTE]) 238 , ilIndividualAssessmentMembers::FIELD_PLACE => array("text", $record[ilIndividualAssessmentMembers::FIELD_PLACE]) 239 , ilIndividualAssessmentMembers::FIELD_EVENTTIME => array("integer", $record[ilIndividualAssessmentMembers::FIELD_EVENTTIME]) 240 , ilIndividualAssessmentMembers::FIELD_NOTIFY => array("integer", $record[ilIndividualAssessmentMembers::FIELD_NOTIFY]) 241 , ilIndividualAssessmentMembers::FIELD_FINALIZED => array("integer", 0) 242 , ilIndividualAssessmentMembers::FIELD_NOTIFICATION_TS => array("integer", -1) 243 , ilIndividualAssessmentMembers::FIELD_FILE_NAME => array("text", $record[ilIndividualAssessmentMembers::FIELD_FILE_NAME]) 244 , ilIndividualAssessmentMembers::FIELD_USER_VIEW_FILE => array("integer", $record[ilIndividualAssessmentMembers::FIELD_USER_VIEW_FILE]) 245 , ilIndividualAssessmentMembers::FIELD_CHANGER_ID => array("integer", $record[ilIndividualAssessmentMembers::FIELD_CHANGER_ID]) 246 , ilIndividualAssessmentMembers::FIELD_CHANGE_TIME => array("text", $record[ilIndividualAssessmentMembers::FIELD_CHANGE_TIME]) 247 ); 248 249 $this->db->insert(self::MEMBERS_TABLE, $values); 250 } 251 252 /** 253 * @inheritdoc 254 */ 255 public function removeMembersRecord(ilObjIndividualAssessment $iass, array $record) 256 { 257 $sql = "DELETE FROM " . self::MEMBERS_TABLE . "\n" 258 . " WHERE obj_id = " . $this->db->quote($iass->getId(), 'integer') . "\n" 259 . " AND usr_id = " . $this->db->quote($record[ilIndividualAssessmentMembers::FIELD_USR_ID], 'integer'); 260 261 $this->db->manipulate($sql); 262 } 263 264 /** 265 * @param int|string 266 */ 267 protected function getWhereFromFilter($filter) : string 268 { 269 switch ($filter) { 270 case ilIndividualAssessmentMembers::LP_ASSESSMENT_NOT_COMPLETED: 271 return " AND finalized = 0 AND examiner_id IS NULL\n"; 272 break; 273 case ilIndividualAssessmentMembers::LP_IN_PROGRESS: 274 return " AND finalized = 0 AND examiner_id IS NOT NULL\n"; 275 break; 276 case ilIndividualAssessmentMembers::LP_COMPLETED: 277 return " AND finalized = 1 AND learning_progress = 2\n"; 278 break; 279 case ilIndividualAssessmentMembers::LP_FAILED: 280 return " AND finalized = 1 AND learning_progress = 3\n"; 281 break; 282 } 283 } 284 285 protected function getOrderByFromSort(string $sort) : string 286 { 287 $vals = explode(":", $sort); 288 289 return " ORDER BY " . $vals[0] . " " . $vals[1]; 290 } 291} 292