1<?php 2 3declare(strict_types=1); 4 5class ilStudyProgrammeProgressDBRepository implements ilStudyProgrammeProgressRepository 6{ 7 protected static $cache = []; 8 protected $db; 9 10 const TABLE = 'prg_usr_progress'; 11 12 const FIELD_ID = 'id'; 13 const FIELD_ASSIGNMENT_ID = 'assignment_id'; 14 const FIELD_PRG_ID = 'prg_id'; 15 const FIELD_USR_ID = 'usr_id'; 16 const FIELD_POINTS = 'points'; 17 const FIELD_POINTS_CUR = 'points_cur'; 18 const FIELD_STATUS = 'status'; 19 const FIELD_COMPLETION_BY = 'completion_by'; 20 const FIELD_ASSIGNMENT_DATE = 'assignment_date'; 21 const FIELD_LAST_CHANGE = 'last_change'; 22 const FIELD_LAST_CHANGE_BY = 'last_change_by'; 23 const FIELD_COMPLETION_DATE = 'completion_date'; 24 const FIELD_DEADLINE = 'deadline'; 25 const FIELD_VQ_DATE = 'vq_date'; 26 const FIELD_INVALIDATED = 'invalidated'; 27 const FIELD_MAIL_SEND = 'risky_to_fail_mail_send'; 28 29 public function __construct(ilDBInterface $db) 30 { 31 $this->db = $db; 32 } 33 34 /** 35 * @inheritdoc 36 * 37 * @throws ilException 38 */ 39 public function createFor( 40 ilStudyProgrammeSettings $prg, 41 ilStudyProgrammeAssignment $ass 42 ) : ilStudyProgrammeProgress { 43 $id = $this->nextId(); 44 $row = [ 45 self::FIELD_ID => $id, 46 self::FIELD_ASSIGNMENT_ID => $ass->getId(), 47 self::FIELD_PRG_ID => $prg->getObjId(), 48 self::FIELD_USR_ID => $ass->getUserId(), 49 self::FIELD_POINTS => $prg->getAssessmentSettings()->getPoints(), 50 self::FIELD_POINTS_CUR => 0, 51 self::FIELD_STATUS => ilStudyProgrammeProgress::STATUS_IN_PROGRESS, 52 self::FIELD_COMPLETION_BY => null, 53 self::FIELD_LAST_CHANGE => ilUtil::now(), 54 self::FIELD_ASSIGNMENT_DATE => ilUtil::now(), 55 self::FIELD_LAST_CHANGE_BY => null, 56 self::FIELD_COMPLETION_DATE => null, 57 self::FIELD_DEADLINE => null, 58 self::FIELD_VQ_DATE => null, 59 self::FIELD_INVALIDATED => 0 60 ]; 61 $this->insertRowDB($row); 62 return $this->buildByRow($row); 63 } 64 65 /** 66 * @inheritdoc 67 * 68 * @throws ilException 69 */ 70 public function read(int $id) : ilStudyProgrammeProgress 71 { 72 foreach ($this->loadByFilter([self::FIELD_ID => $id]) as $row) { 73 return $this->buildByRow($row); 74 } 75 throw new ilException('invalid id ' . $id); 76 } 77 78 79 /** 80 * @inheritdoc 81 * 82 * @throws ilException 83 */ 84 public function readByIds( 85 int $prg_id, 86 int $assignment_id, 87 int $usr_id 88 ) : ilStudyProgrammeProgress { 89 return $this->readByPrgIdAndAssignmentId($prg_id, $assignment_id); 90 } 91 92 /** 93 * @inheritdoc 94 * 95 * @throws ilException 96 * 97 * @return ilStudyProgrammeProgress | void 98 */ 99 public function readByPrgIdAndAssignmentId(int $prg_id, int $assignment_id) 100 { 101 $rows = $this->loadByFilter( 102 [ 103 self::FIELD_PRG_ID => $prg_id, 104 self::FIELD_ASSIGNMENT_ID => $assignment_id 105 ] 106 ); 107 108 foreach ($rows as $row) { 109 return $this->buildByRow($row); 110 } 111 } 112 113 /** 114 * @inheritdoc 115 * 116 * @throws ilException 117 */ 118 public function readByPrgIdAndUserId(int $prg_id, int $usr_id) : array 119 { 120 $return = []; 121 foreach ($this->loadByFilter([self::FIELD_PRG_ID => $prg_id, self::FIELD_USR_ID => $usr_id]) as $row) { 122 $return[] = $this->buildByRow($row); 123 } 124 return $return; 125 } 126 127 /** 128 * @inheritdoc 129 * 130 * @throws ilException 131 */ 132 public function readByPrgId(int $prg_id) : array 133 { 134 $return = []; 135 foreach ($this->loadByFilter([self::FIELD_PRG_ID => $prg_id]) as $row) { 136 $return[] = $this->buildByRow($row); 137 } 138 return $return; 139 } 140 141 /** 142 * @return ilStudyProgrammeProgress | void 143 * @throws ilException 144 */ 145 public function readFirstByPrgId(int $prg_id) 146 { 147 foreach ($this->loadByFilter([self::FIELD_PRG_ID => $prg_id]) as $row) { 148 return $this->buildByRow($row); 149 } 150 } 151 152 /** 153 * @inheritdoc 154 * 155 * @throws ilException 156 */ 157 public function readByAssignmentId(int $assignment_id) : array 158 { 159 $return = []; 160 foreach ($this->loadByFilter([self::FIELD_ASSIGNMENT_ID => $assignment_id]) as $row) { 161 $return[] = $this->buildByRow($row); 162 } 163 return $return; 164 } 165 166 /** 167 * @inheritdoc 168 * 169 * @throws ilException 170 */ 171 public function readExpiredSuccessfull() : array 172 { 173 $return = []; 174 foreach ($this->loadExpiredSuccessful() as $row) { 175 $return[] = $this->buildByRow($row); 176 } 177 return $return; 178 } 179 180 /** 181 * @inheritDoc 182 * 183 * @throws ilException 184 */ 185 public function readPassedDeadline() : array 186 { 187 $return = []; 188 foreach ($this->loadPassedDeadline() as $row) { 189 $return[] = $this->buildByRow($row); 190 } 191 return $return; 192 } 193 194 /** 195 * @inheritdoc 196 * 197 * @throws ilException 198 */ 199 public function readRiskyToFailInstances() : array 200 { 201 $return = []; 202 foreach ($this->loadRiskyToFailInstance() as $row) { 203 $return[] = $this->buildByRow($row); 204 } 205 return $return; 206 } 207 208 /** 209 * @inheritdoc 210 */ 211 public function update(ilStudyProgrammeProgress $progress) 212 { 213 $this->updateRowDB( 214 [ 215 self::FIELD_ID => $progress->getId(), 216 self::FIELD_ASSIGNMENT_ID => $progress->getAssignmentId(), 217 self::FIELD_PRG_ID => $progress->getNodeId(), 218 self::FIELD_USR_ID => $progress->getUserId(), 219 self::FIELD_STATUS => $progress->getStatus(), 220 self::FIELD_POINTS => $progress->getAmountOfPoints(), 221 self::FIELD_POINTS_CUR => $progress->getCurrentAmountOfPoints(), 222 self::FIELD_COMPLETION_BY => $progress->getCompletionBy(), 223 self::FIELD_LAST_CHANGE_BY => $progress->getLastChangeBy(), 224 self::FIELD_LAST_CHANGE => $progress->getLastChange()->format(ilStudyProgrammeProgress::DATE_TIME_FORMAT), 225 self::FIELD_ASSIGNMENT_DATE => $progress->getAssignmentDate()->format(ilStudyProgrammeProgress::DATE_TIME_FORMAT), 226 self::FIELD_COMPLETION_DATE => 227 $progress->getCompletionDate() ? 228 $progress->getCompletionDate()->format(ilStudyProgrammeProgress::DATE_TIME_FORMAT) : null, 229 self::FIELD_DEADLINE => $progress->getDeadline() ? $progress->getDeadline()->format(ilStudyProgrammeProgress::DATE_FORMAT) : null, 230 self::FIELD_VQ_DATE => $progress->getValidityOfQualification() ? $progress->getValidityOfQualification()->format(ilStudyProgrammeProgress::DATE_TIME_FORMAT) : null, 231 self::FIELD_INVALIDATED => $progress->isInvalidated() ? 1 : 0 232 ] 233 ); 234 } 235 236 /** 237 * @inheritdoc 238 */ 239 public function delete(ilStudyProgrammeProgress $progress) 240 { 241 $this->deleteDB($progress->getId()); 242 } 243 244 protected function insertRowDB(array $row) 245 { 246 $this->db->insert( 247 self::TABLE, 248 [ 249 self::FIELD_ID => ['integer', $row[self::FIELD_ID]] 250 , self::FIELD_ASSIGNMENT_ID => ['integer', $row[self::FIELD_ASSIGNMENT_ID]] 251 , self::FIELD_PRG_ID => ['integer', $row[self::FIELD_PRG_ID]] 252 , self::FIELD_USR_ID => ['integer', $row[self::FIELD_USR_ID]] 253 , self::FIELD_STATUS => ['integer', $row[self::FIELD_STATUS]] 254 , self::FIELD_POINTS => ['integer', $row[self::FIELD_POINTS]] 255 , self::FIELD_POINTS_CUR => ['integer', $row[self::FIELD_POINTS_CUR]] 256 , self::FIELD_COMPLETION_BY => ['integer', $row[self::FIELD_COMPLETION_BY]] 257 , self::FIELD_LAST_CHANGE_BY => ['integer', $row[self::FIELD_LAST_CHANGE_BY]] 258 , self::FIELD_LAST_CHANGE => ['text', $row[self::FIELD_LAST_CHANGE]] 259 , self::FIELD_ASSIGNMENT_DATE => ['timestamp', $row[self::FIELD_ASSIGNMENT_DATE]] 260 , self::FIELD_COMPLETION_DATE => ['timestamp', $row[self::FIELD_COMPLETION_DATE]] 261 , self::FIELD_DEADLINE => ['text', $row[self::FIELD_DEADLINE]] 262 , self::FIELD_VQ_DATE => ['timestamp', $row[self::FIELD_VQ_DATE]] 263 , self::FIELD_INVALIDATED => ['timestamp', $row[self::FIELD_INVALIDATED]] 264 ] 265 ); 266 } 267 268 public function deleteDB(int $id) 269 { 270 $this->db->manipulate( 271 'DELETE FROM ' . self::TABLE . ' WHERE ' . self::FIELD_ID . ' = ' . $this->db->quote($id, 'integer') 272 ); 273 } 274 275 public function reminderSendFor(int $progress_id) : void 276 { 277 $where = [ 278 self::FIELD_ID => [ 279 'integer', 280 $progress_id 281 ] 282 ]; 283 284 $values = [ 285 self::FIELD_MAIL_SEND => [ 286 'timestamp', 287 date('Y-m-d H:i:s') 288 ] 289 ]; 290 291 $this->db->update(self::TABLE, $values, $where); 292 } 293 294 protected function updateRowDB(array $data) 295 { 296 $where = [ 297 self::FIELD_ID => [ 298 'integer', 299 $data[self::FIELD_ID] 300 ] 301 ]; 302 303 $values = [ 304 self::FIELD_ASSIGNMENT_ID => [ 305 'integer', 306 $data[self::FIELD_ASSIGNMENT_ID] 307 ], 308 self::FIELD_PRG_ID => [ 309 'integer', 310 $data[self::FIELD_PRG_ID] 311 ], 312 self::FIELD_USR_ID => [ 313 'integer', 314 $data[self::FIELD_USR_ID] 315 ], 316 self::FIELD_STATUS => [ 317 'integer', 318 $data[self::FIELD_STATUS] 319 ], 320 self::FIELD_POINTS => [ 321 'integer', 322 $data[self::FIELD_POINTS] 323 ], 324 self::FIELD_POINTS_CUR => [ 325 'integer', 326 $data[self::FIELD_POINTS_CUR] 327 ], 328 self::FIELD_COMPLETION_BY => [ 329 'integer', 330 $data[self::FIELD_COMPLETION_BY] 331 ], 332 self::FIELD_LAST_CHANGE_BY => [ 333 'integer', 334 $data[self::FIELD_LAST_CHANGE_BY] 335 ], 336 self::FIELD_LAST_CHANGE => [ 337 'text', 338 $data[self::FIELD_LAST_CHANGE] 339 ], 340 self::FIELD_ASSIGNMENT_DATE => [ 341 'timestamp', 342 $data[self::FIELD_ASSIGNMENT_DATE] 343 ], 344 self::FIELD_COMPLETION_DATE => [ 345 'timestamp', 346 $data[self::FIELD_COMPLETION_DATE] 347 ], 348 self::FIELD_DEADLINE => [ 349 'text', 350 $data[self::FIELD_DEADLINE] 351 ], 352 self::FIELD_VQ_DATE => [ 353 'timestamp', 354 $data[self::FIELD_VQ_DATE] 355 ], 356 self::FIELD_INVALIDATED => [ 357 'integer', 358 $data[self::FIELD_INVALIDATED] 359 ], 360 ]; 361 362 $this->db->update(self::TABLE, $values, $where); 363 } 364 365 /** 366 * @throws ilException 367 */ 368 protected function buildByRow(array $row) : ilStudyProgrammeProgress 369 { 370 $prgrs = (new ilStudyProgrammeProgress((int) $row[self::FIELD_ID])) 371 ->setAssignmentId((int) $row[self::FIELD_ASSIGNMENT_ID]) 372 ->setNodeId((int) $row[self::FIELD_PRG_ID]) 373 ->setUserId((int) $row[self::FIELD_USR_ID]) 374 ->setStatus((int) $row[self::FIELD_STATUS]) 375 ->setAmountOfPoints((int) $row[self::FIELD_POINTS]) 376 ->setCurrentAmountOfPoints((int) $row[self::FIELD_POINTS_CUR]) 377 ->setCompletionBy((int) $row[self::FIELD_COMPLETION_BY]) 378 ->setDeadline( 379 $row[self::FIELD_DEADLINE] ? 380 DateTime::createFromFormat(ilStudyProgrammeProgress::DATE_FORMAT, $row[self::FIELD_DEADLINE]) : 381 null 382 ) 383 ->setAssignmentDate( 384 DateTime::createFromFormat(ilStudyProgrammeProgress::DATE_TIME_FORMAT, $row[self::FIELD_ASSIGNMENT_DATE]) 385 ) 386 ->setCompletionDate( 387 $row[self::FIELD_COMPLETION_DATE] ? 388 DateTime::createFromFormat(ilStudyProgrammeProgress::DATE_TIME_FORMAT, $row[self::FIELD_COMPLETION_DATE]) : 389 null 390 ) 391 ->setLastChange( 392 $row[self::FIELD_LAST_CHANGE] ? 393 DateTime::createFromFormat(ilStudyProgrammeProgress::DATE_TIME_FORMAT, $row[self::FIELD_LAST_CHANGE]) : 394 null 395 ) 396 ->setValidityOfQualification( 397 $row[self::FIELD_VQ_DATE] ? 398 DateTime::createFromFormat(ilStudyProgrammeProgress::DATE_TIME_FORMAT, $row[self::FIELD_VQ_DATE]) : 399 null 400 ); 401 if ((int) $row[self::FIELD_INVALIDATED] === 1) { 402 $prgrs = $prgrs->invalidate(); 403 } 404 405 if (!is_null($row[self::FIELD_LAST_CHANGE_BY])) { 406 $prgrs = $prgrs->setLastChangeBy((int) $row[self::FIELD_LAST_CHANGE_BY]); 407 } 408 409 return $prgrs; 410 } 411 412 protected function loadByFilter(array $filter) 413 { 414 $q = $this->getSQLHeader() 415 . ' WHERE TRUE'; 416 foreach ($filter as $field => $value) { 417 $q .= ' AND ' . $field . ' = ' . $this->db->quote($value, 'text'); 418 } 419 $res = $this->db->query($q); 420 while ($rec = $this->db->fetchAssoc($res)) { 421 yield $rec; 422 } 423 } 424 425 protected function loadExpiredSuccessful() 426 { 427 $q = $this->getSQLHeader() 428 . ' WHERE ' . $this->db->in( 429 self::FIELD_STATUS, 430 [ 431 ilStudyProgrammeProgress::STATUS_ACCREDITED, 432 ilStudyProgrammeProgress::STATUS_COMPLETED 433 ], 434 false, 435 'integer' 436 ) 437 . ' AND ' . self::FIELD_VQ_DATE . ' IS NOT NULL' 438 . ' AND DATE(' . self::FIELD_VQ_DATE . ') < ' 439 . $this->db->quote( 440 (new DateTime())->format(ilStudyProgrammeProgress::DATE_FORMAT), 441 'text' 442 ) 443 . ' AND ' . self::FIELD_INVALIDATED . ' != 1 OR ' . self::FIELD_INVALIDATED . ' IS NULL'; 444 445 $res = $this->db->query($q); 446 while ($rec = $this->db->fetchAssoc($res)) { 447 yield $rec; 448 } 449 } 450 451 protected function loadPassedDeadline() 452 { 453 $q = 454 $this->getSQLHeader() . PHP_EOL 455 . 'WHERE ' . $this->db->in( 456 self::FIELD_STATUS, 457 [ 458 ilStudyProgrammeProgress::STATUS_IN_PROGRESS, 459 ilStudyProgrammeProgress::STATUS_ACCREDITED 460 ], 461 false, 462 'integer' 463 ) . PHP_EOL 464 . 'AND ' . self::FIELD_DEADLINE . ' IS NOT NULL' . PHP_EOL 465 . 'AND DATE(' . self::FIELD_DEADLINE . ') < ' . $this->db->quote( 466 (new DateTime())->format(ilStudyProgrammeProgress::DATE_FORMAT), 467 'text' 468 ) . PHP_EOL 469 ; 470 $res = $this->db->query($q); 471 while ($rec = $this->db->fetchAssoc($res)) { 472 yield $rec; 473 } 474 } 475 476 protected function loadRiskyToFailInstance() 477 { 478 $q = $this->getSQLHeader() 479 . ' WHERE ' . $this->db->in( 480 self::FIELD_STATUS, 481 [ 482 ilStudyProgrammeProgress::STATUS_ACCREDITED, 483 ilStudyProgrammeProgress::STATUS_COMPLETED 484 ], 485 true, 486 'integer' 487 ) 488 . ' AND ' . self::FIELD_DEADLINE . ' IS NOT NULL' 489 . ' AND DATE(' . self::FIELD_DEADLINE . ') < ' 490 . $this->db->quote( 491 (new DateTime())->format(ilStudyProgrammeProgress::DATE_FORMAT), 492 'text' 493 ) 494 . ' AND ' . self::FIELD_MAIL_SEND . ' IS NULL' 495 ; 496 $res = $this->db->query($q); 497 while ($rec = $this->db->fetchAssoc($res)) { 498 yield $rec; 499 } 500 } 501 502 protected function getSQLHeader() : string 503 { 504 return 'SELECT ' . self::FIELD_ID 505 . ', ' . self::FIELD_ASSIGNMENT_ID 506 . ', ' . self::FIELD_PRG_ID 507 . ', ' . self::FIELD_USR_ID 508 . ', ' . self::FIELD_STATUS 509 . ', ' . self::FIELD_POINTS 510 . ', ' . self::FIELD_POINTS_CUR 511 . ', ' . self::FIELD_COMPLETION_BY 512 . ', ' . self::FIELD_LAST_CHANGE 513 . ', ' . self::FIELD_LAST_CHANGE_BY 514 . ', ' . self::FIELD_ASSIGNMENT_DATE 515 . ', ' . self::FIELD_COMPLETION_DATE 516 . ', ' . self::FIELD_DEADLINE 517 . ', ' . self::FIELD_VQ_DATE 518 . ', ' . self::FIELD_INVALIDATED 519 . ' FROM ' . self::TABLE; 520 } 521 522 protected function nextId() : int 523 { 524 return (int) $this->db->nextId(self::TABLE); 525 } 526} 527