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