Lines 75.00% 39 / 52
Functions and Methods 55.55% 5 / 9
Classes and Traits 0.00% 0 / 1
Name Lines Functions and Methods CRAP Classes and Traits
Slot 75.00% 39 / 52 55.55% 5 / 9 21.52 0.00% 0 / 1
 getEntityMapping 0.00% 0 / 2 0.00% 0 / 1 2
 reverseEntityMapping 100.00% 13 / 13 100.00% 1 / 1 3
 addConditionSlotId 0.00% 0 / 2 0.00% 0 / 1 2
 insertAncestorChainsSql 100.00% 3 / 3 100.00% 1 / 1 1
 upsertSlotRowsSql 0.00% 0 / 8 0.00% 0 / 1 6
 insertSlotRowsSql 100.00% 4 / 4 100.00% 1 / 1 1
 packSlotRow 83.33% 5 / 6 0.00% 0 / 1 3.04
 quotedColumns 100.00% 1 / 1 100.00% 1 / 1 1
 chunkedStatements 100.00% 13 / 13 100.00% 1 / 1 4
1<?php
2
3namespace BO\Zmsbackend\Slot\Repository;
4
5class Slot extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface
6{
7    /**
8     *
9     * @var String TABLE mysql table reference
10     */
11    const string TABLE = 'slot';
12
13    const string QUERY_OPTIMIZE_SLOT = 'OPTIMIZE TABLE slot;';
14    const string QUERY_OPTIMIZE_SLOT_HIERA = 'OPTIMIZE TABLE slot_hiera;';
15    const string QUERY_OPTIMIZE_SLOT_PROCESS = 'OPTIMIZE TABLE slot_proces;';
16    const string QUERY_OPTIMIZE_PROCESS = 'OPTIMIZE TABLE buerger;';
17
18    const string QUERY_LAST_CHANGED = 'SELECT MAX(updateTimestamp) AS dateString FROM slot;';
19
20    const string QUERY_LAST_CHANGED_AVAILABILITY = '
21        SELECT MAX(updateTimestamp) AS dateString FROM slot WHERE availabilityID = :availabilityID AND status="free";';
22
23    const string QUERY_LAST_IN_AVAILABILITY = '
24        SELECT CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) AS dateString
25        FROM slot
26        WHERE availabilityID = :availabilityID AND status="free"
27        ORDER BY year DESC, month DESC, day DESC
28        LIMIT 1;';
29
30    const string QUERY_OLDEST_VERSION_IN_AVAILABILITY = '
31        SELECT `version`
32        FROM slot
33        WHERE availabilityID = :availabilityID AND status="free"
34        ORDER BY `version` ASC
35        LIMIT 1;';
36
37    const string QUERY_LAST_CHANGED_SCOPE = '
38        SELECT MAX(updateTimestamp) AS dateString FROM slot WHERE scopeID = :scopeID;';
39
40    /**
41     * Active (free or full, not cancelled) slots of one scope and day with their current occupancy,
42     * used to decide which of several overlapping availabilities gets an appointment.
43     * Full slots are needed, otherwise a full slot looks like a gap in its availability.
44     */
45    const string QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY = '
46        SELECT
47            s.slotID,
48            s.availabilityID,
49            s.time,
50            s.slotTimeInMinutes,
51            s.public,
52            s.intern,
53            COUNT(sp.processID) AS confirmed
54        FROM slot s
55            LEFT JOIN slot_process sp ON sp.slotID = s.slotID
56        WHERE
57            s.scopeID = :scopeID
58            AND s.year = :year
59            AND s.month = :month
60            AND s.day = :day
61            AND s.status IN ("free", "full")
62        GROUP BY s.slotID
63        ORDER BY s.availabilityID, s.time
64    ';
65
66    const string QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING = '
67        SELECT
68            b.BuergerID,
69            b.StandortID,
70            b.Datum,
71            b.Uhrzeit,
72            b.istFolgeterminvon,
73            b.hatFolgetermine,
74            b.apiClientID,
75            ac.accesslevel
76        FROM buerger b
77            LEFT JOIN apiclient ac ON ac.apiClientID = b.apiClientID
78        WHERE b.BuergerID = :processId
79    ';
80
81    const string QUERY_SELECT_FOLLOWING_PROCESS_IDS = '
82        SELECT b.BuergerID
83        FROM buerger b
84        WHERE b.istFolgeterminvon = :processId
85        ORDER BY b.Uhrzeit, b.BuergerID
86    ';
87
88    const string QUERY_SELECT_MAPPED_AVAILABILITY_BY_PROCESS = '
89        SELECT s.availabilityID
90        FROM slot_process sp
91            INNER JOIN slot s ON s.slotID = sp.slotID
92        WHERE
93            sp.processID = :processId
94            AND s.status IN ("free", "full")
95        LIMIT 1
96    ';
97
98    const string QUERY_SELECT_BY_SCOPE_AND_DAY = '
99        SELECT
100            s.*
101        FROM slot s
102        WHERE
103            s.scopeID = :scopeID
104            AND s.year = :year
105            AND s.month = :month
106            AND s.day = :day
107    ';
108
109    const string QUERY_SELECT_MISSING_PROCESS = '
110        SELECT DISTINCT
111          b.BuergerID,
112          b.istFolgeterminvon
113        FROM slot s
114          INNER JOIN buerger b ON
115            s.scopeID = b.StandortID
116            AND s.status = "free"
117            AND (
118              (
119                s.slotTimeInMinutes = 5
120                AND (b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0)
121                AND (
122                  (
123                    s.year = YEAR(b.Datum)
124                    AND s.month = MONTH(b.Datum)
125                    AND s.day = DAY(b.Datum)
126                    AND s.time < ADDTIME(
127                        b.Uhrzeit,
128                        SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
129                    )
130                    AND ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) > b.Uhrzeit
131                  )
132                  OR (
133                    ADDTIME(
134                        b.Uhrzeit,
135                        SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
136                    ) >= "24:00:00"
137                    AND STR_TO_DATE(CONCAT(s.year, "-", s.month, "-", s.day), "%Y-%m-%d")
138                        = DATE_ADD(b.Datum, INTERVAL 1 DAY)
139                    AND s.time < SUBTIME(
140                        ADDTIME(
141                            b.Uhrzeit,
142                            SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
143                        ),
144                        "24:00:00"
145                    )
146                  )
147                )
148              )
149              OR (
150                IFNULL(s.slotTimeInMinutes, 0) <> 5
151                AND s.year = YEAR(b.Datum)
152                AND s.month = MONTH(b.Datum)
153                AND s.day = DAY(b.Datum)
154                AND b.Uhrzeit BETWEEN s.time
155                  AND SEC_TO_TIME(TIME_TO_SEC(s.time) + (s.slotTimeInMinutes * 60) - 1)
156              )
157            )
158          LEFT JOIN slot_process sp ON sp.processID = b.BuergerID
159            AND (IFNULL(s.slotTimeInMinutes, 0) <> 5 OR sp.slotID = s.slotID)
160        WHERE
161          sp.processID IS NULL
162    ';
163    const string QUERY_SELECT_MISSING_PROCESS_BY_SCOPE = '
164          AND s.scopeID = :scopeID
165    ';
166
167    /**
168     * Book every step of this appointment immediately, on the given availability only.
169     * hatFolgetermine is the extra-step count, so one process row covers the whole visit.
170     */
171    const string QUERY_INSERT_SLOT_PROCESS_ID = '
172        INSERT INTO slot_process (slotID, processID, updateTimestamp)
173        SELECT
174          s.slotID,
175          b.BuergerID,
176          NOW()
177        FROM buerger b
178          INNER JOIN slot s ON s.scopeID = b.StandortID
179            AND s.status IN ("free", "full")
180            AND (
181              (
182                s.year = YEAR(b.Datum)
183                AND s.month = MONTH(b.Datum)
184                AND s.day = DAY(b.Datum)
185                AND s.time < ADDTIME(
186                  b.Uhrzeit,
187                  SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
188                )
189                AND ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) > b.Uhrzeit
190              )
191              OR (
192                ADDTIME(
193                  b.Uhrzeit,
194                  SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
195                ) >= "24:00:00"
196                AND s.year = YEAR(DATE_ADD(b.Datum, INTERVAL 1 DAY))
197                AND s.month = MONTH(DATE_ADD(b.Datum, INTERVAL 1 DAY))
198                AND s.day = DAY(DATE_ADD(b.Datum, INTERVAL 1 DAY))
199                AND s.time < SUBTIME(
200                  ADDTIME(
201                    b.Uhrzeit,
202                    SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
203                  ),
204                  "24:00:00"
205                )
206              )
207            )
208        WHERE
209          b.BuergerID = :processId
210          AND s.availabilityID = :availabilityId
211    ';
212    const string QUERY_DELETE_SLOT_PROCESS_CANCELLED = '
213        DELETE sp 
214            FROM slot_process sp LEFT JOIN slot s USING (slotID)
215            WHERE (s.status = "cancelled" OR s.status IS NULL)
216    ';
217    const string QUERY_DELETE_SLOT_PROCESS_CANCELLED_BY_SCOPE = '
218                AND s.scopeID = :scopeID
219    ';
220
221
222    const string QUERY_UPDATE_SLOT_MISSING_AVAILABILITY_BY_SCOPE = '
223    UPDATE
224         slot s
225           LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID
226           SET s.status = "cancelled"
227           WHERE
228             (
229               a.OeffnungszeitID IS NULL
230               OR a.end_date < :dateString
231             )
232             AND s.scopeID = :scopeID
233    ';
234
235    const string QUERY_UPDATE_SLOT_MISSING_AVAILABILITY = '
236    UPDATE
237         slot s
238           LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID
239           SET s.status = "cancelled"
240           WHERE
241             a.OeffnungszeitID IS NULL
242               OR a.end_date < :dateString
243    ';
244
245    const string QUERY_SELECT_DELETABLE_SLOT_PROCESS = '
246        SELECT sp.slotID AS slotID, sp.processID AS processID
247            FROM slot_process sp
248              LEFT JOIN buerger b ON sp.processID = b.BuergerID
249              LEFT JOIN slot s ON sp.slotID = s.slotID
250            WHERE (
251                b.BuergerID IS NULL
252                OR (
253                  s.slotTimeInMinutes = 5
254                  AND b.istFolgeterminvon IS NOT NULL
255                  AND b.istFolgeterminvon <> 0
256                )
257                OR (
258                  b.updateTimestamp > sp.updateTimestamp
259                  AND (
260                    s.scopeID != b.StandortID
261                    OR (
262                      NOT (
263                        s.year = YEAR(b.Datum)
264                        AND s.month = MONTH(b.Datum)
265                        AND s.day = DAY(b.Datum)
266                      )
267                      AND NOT (
268                        s.slotTimeInMinutes = 5
269                        AND (b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0)
270                        AND ADDTIME(
271                            b.Uhrzeit,
272                            SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
273                        ) >= "24:00:00"
274                        AND STR_TO_DATE(CONCAT(s.year, "-", s.month, "-", s.day), "%Y-%m-%d")
275                            = DATE_ADD(b.Datum, INTERVAL 1 DAY)
276                        AND s.time < SUBTIME(
277                            ADDTIME(
278                                b.Uhrzeit,
279                                SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
280                            ),
281                            "24:00:00"
282                        )
283                      )
284                    )
285                    OR (
286                      s.slotTimeInMinutes = 5
287                      AND s.year = YEAR(b.Datum)
288                      AND s.month = MONTH(b.Datum)
289                      AND s.day = DAY(b.Datum)
290                      AND (
291                        s.time >= ADDTIME(
292                            b.Uhrzeit,
293                            SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60)
294                        )
295                        OR ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) <= b.Uhrzeit
296                      )
297                    )
298                    OR (
299                      IFNULL(s.slotTimeInMinutes, 0) <> 5
300                      AND b.Uhrzeit NOT BETWEEN s.time
301                        AND SEC_TO_TIME(TIME_TO_SEC(s.time) + (s.slotTimeInMinutes * 60) - 1)
302                    )
303                  )
304                )
305              ) 
306    ';
307    const string QUERY_SELECT_DELETABLE_SLOT_PROCESS_BY_SCOPE = '
308              AND b.StandortID = :scopeID
309    ';
310
311    const string QUERY_DELETE_SLOT_PROCESS_ID = '
312        DELETE sp 
313            FROM slot_process sp 
314            WHERE sp.processID = :processId
315    ';
316
317    const string QUERY_UPDATE_SLOT_STATUS = "
318        UPDATE slot
319          LEFT JOIN (
320          SELECT s.slotID,
321          IF(s.status IN ('free', 'full'), IF(IFNULL(COUNT(p.slotID), 0) < intern, 'free', 'full'), s.status) newstatus
322          FROM slot s
323            LEFT JOIN slot_process p ON s.slotID = p.slotID
324          GROUP BY s.slotID
325          ) calc ON slot.slotID = calc.slotID
326        SET
327          slot.status = calc.newstatus
328        WHERE slot.status != calc.newstatus
329";
330
331    const string QUERY_SELECT_SLOT = '
332    SELECT slotID FROM slot WHERE
333      scopeID = :scopeID
334      AND year = :year
335      AND month = :month
336      AND day = :day
337      AND time = :time
338      AND availabilityID = :availabilityID
339    LIMIT 1
340';
341
342    const string QUERY_SELECT_SLOTS_IN_RANGE = '
343    SELECT slotID, time, year, month, day FROM slot WHERE
344      scopeID = :scopeID
345      AND availabilityID = :availabilityID
346      AND year >= :yearFrom
347      AND year <= :yearTo
348';
349
350    /**
351     * Columns written for one slot row, apart from slotID.
352     *
353     * @var list<string>
354     */
355    const array SLOT_WRITE_COLUMNS = [
356        'scopeID',
357        'availabilityID',
358        'version',
359        'year',
360        'month',
361        'day',
362        'time',
363        'public',
364        'intern',
365        'status',
366        'slotTimeInMinutes',
367        'createTimestamp',
368    ];
369
370    const string QUERY_CREATE_CALC_SLOT_ORDER = '
371        CREATE TEMPORARY TABLE IF NOT EXISTS `zms_calc_slot_order` (
372            `dayKey` CHAR(10) NOT NULL,
373            `sequence` SMALLINT UNSIGNED NOT NULL,
374            `slotID` INT UNSIGNED NOT NULL,
375            PRIMARY KEY (`dayKey`, `sequence`)
376        ) ENGINE=MEMORY
377    ';
378
379    const string QUERY_DELETE_CALC_SLOT_ORDER = 'DELETE FROM `zms_calc_slot_order`';
380
381    const string QUERY_INSERT_CALC_SLOT_ORDER = '
382        INSERT INTO `zms_calc_slot_order` (`dayKey`, `sequence`, `slotID`) VALUES
383    ';
384
385    const string QUERY_DELETE_HIERA_FOR_CALC_ORDER = '
386        DELETE `hiera` FROM `slot_hiera` `hiera`
387        INNER JOIN `zms_calc_slot_order` `orderedSlot` ON `orderedSlot`.`slotID` = `hiera`.`slotID`
388    ';
389
390    /**
391     * Lock multiple appointment slots in one statement.
392     * Placeholders for time IN (...) are injected by Slot::lockSlotsForAppointment.
393     * ORDER BY slotID prefers a deterministic primary-key lock order.
394     */
395    public const string QUERY_SELECT_SLOTS_FOR_UPDATE = '
396    SELECT slotID FROM slot WHERE
397      scopeID = ?
398      AND availabilityID = ?
399      AND year = ?
400      AND month = ?
401      AND day = ?
402      AND time IN (%s)
403    ORDER BY slotID ASC
404    FOR UPDATE
405';
406
407    /**
408     * Lock the slots of several overlapping availabilities in one statement (consistent slotID order)
409     */
410    public const string QUERY_SELECT_CANDIDATE_SLOTS_FOR_UPDATE = '
411    SELECT slotID FROM slot WHERE
412      scopeID = ?
413      AND year = ?
414      AND month = ?
415      AND day = ?
416      AND availabilityID IN (%s)
417      AND time IN (%s)
418    ORDER BY slotID ASC
419    FOR UPDATE
420';
421
422    const string QUERY_CANCEL_AVAILABILITY = '
423        UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID
424';
425
426    const string QUERY_CANCEL_AVAILABILITY_BEFORE_BOOKABLE = '
427            UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID 
428            AND CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) < :providedDate
429';
430
431    const string QUERY_CANCEL_AVAILABILITY_AFTER_BOOKABLE = '
432            UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID 
433            AND CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) > :providedDate
434    ';
435
436    const string QUERY_CANCEL_SLOT_OLD_BY_SCOPE = '
437    UPDATE slot SET status =  "cancelled" 
438        WHERE scopeID = :scopeID AND (
439            (year < :year)
440            OR (year = :year AND  month < :month) 
441            OR (year = :year AND  month = :month AND  day <= :day AND time < :time)
442        )
443';
444
445    const string QUERY_CANCEL_SLOT_OLD = '
446    UPDATE slot SET status =  "cancelled" 
447        WHERE (year < :year)
448            OR (year = :year AND  month < :month) 
449            OR (year = :year AND  month = :month AND  day <= :day AND time < :time)
450';
451
452    const string QUERY_DELETE_SLOT_OLD = '
453    DELETE FROM slot 
454        WHERE (year < :year) 
455            OR (year = :year AND  month < :month) 
456            OR (year = :year AND  month = :month AND  day < :day)
457';
458
459    const string QUERY_DELETE_SLOT_HIERA = '
460        DELETE sh 
461            FROM slot_hiera sh LEFT JOIN slot s USING(slotID)
462            WHERE s.slotID IS NULL
463    ';
464
465
466    /**
467     * @return array
468     *
469     */
470    #[\Override]
471    public function getEntityMapping()
472    {
473        return [
474        ];
475    }
476
477    /**
478     * @return (mixed|string)[]
479     *
480     */
481    public function reverseEntityMapping(
482        \BO\Zmsentities\Slot $slot,
483        \BO\Zmsentities\Availability $availability,
484        \DateTimeInterface $date
485    ): array {
486        $data = array();
487        $data['scopeID'] = $availability->scope->id;
488        $data['availabilityID'] = $availability->id;
489        $data['version'] = $availability->version;
490        $data['year'] = $date->format('Y');
491        $data['month'] = $date->format('m');
492        $data['day'] = $date->format('d');
493        $data['time'] = $slot->getTimeString();
494        $data['public'] = isset($slot['public']) ? $slot['public'] : $availability->workstationCount['public'];
495        $data['intern'] = isset($slot['intern']) ? $slot['intern'] : $availability->workstationCount['intern'];
496        $data['status'] = $slot->status;
497        $data['slotTimeInMinutes'] = $availability->slotTimeInMinutes;
498        return $data;
499    }
500
501    public function addConditionSlotId(string|int $slotID): static
502    {
503        $this->query->where('slot.slotID', '=', $slotID);
504        return $this;
505    }
506
507    /**
508     * Ancestor level 1 is the slot itself. Higher levels are the earlier slots that day.
509     */
510    public static function insertAncestorChainsSql(int $maximumEarlierSlots): string
511    {
512        return '
513            INSERT INTO `slot_hiera` (`slotID`, `ancestorID`, `ancestorLevel`)
514            SELECT `slotRow`.`slotID`, `ancestorRow`.`slotID`, `slotRow`.`sequence` - `ancestorRow`.`sequence` + 1
515            FROM `zms_calc_slot_order` `slotRow`
516            INNER JOIN `zms_calc_slot_order` `ancestorRow`
517              ON `ancestorRow`.`dayKey` = `slotRow`.`dayKey`
518             AND `ancestorRow`.`sequence` <= `slotRow`.`sequence`
519             AND `ancestorRow`.`sequence` + ' . $maximumEarlierSlots . ' >= `slotRow`.`sequence`
520        ';
521    }
522
523    /**
524     * @return array{prefix: string, suffix: string}
525     */
526    public static function upsertSlotRowsSql(): array
527    {
528        $columns = array_merge(['slotID'], self::SLOT_WRITE_COLUMNS);
529        $assignments = [];
530        foreach (self::SLOT_WRITE_COLUMNS as $column) {
531            $assignments[] = sprintf('`%s` = VALUES(`%s`)', $column, $column);
532        }
533
534        return [
535            'prefix' => 'INSERT INTO `slot` (' . self::quotedColumns($columns) . ') VALUES ',
536            'suffix' => ' ON DUPLICATE KEY UPDATE ' . implode(', ', $assignments),
537        ];
538    }
539
540    /**
541     * @return array{prefix: string, suffix: string}
542     */
543    public static function insertSlotRowsSql(): array
544    {
545        return [
546            'prefix' => 'INSERT INTO `slot` (' . self::quotedColumns(self::SLOT_WRITE_COLUMNS) . ') VALUES ',
547            'suffix' => '',
548        ];
549    }
550
551    /**
552     * @param array<string, int|string> $values
553     * @return list<int|string>
554     */
555    public static function packSlotRow(array $values, ?int $slotId = null): array
556    {
557        $packedRow = [];
558        foreach (self::SLOT_WRITE_COLUMNS as $column) {
559            $packedRow[] = $values[$column];
560        }
561        if ($slotId !== null) {
562            array_unshift($packedRow, $slotId);
563        }
564
565        return $packedRow;
566    }
567
568    /**
569     * @param list<string> $columns
570     */
571    public static function quotedColumns(array $columns): string
572    {
573        return implode(',', array_map(static fn (string $column): string => '`' . $column . '`', $columns));
574    }
575
576    /**
577     * @param list<list<int|string>> $rows
578     * @return list<array{sql: string, parameters: list<int|string>}>
579     */
580    public static function chunkedStatements(string $prefix, string $suffix, array $rows): array
581    {
582        $statements = [];
583        foreach (array_chunk($rows, 1000) as $chunk) {
584            $columnCount = count($chunk[0]);
585            $rowPlaceholder = '(' . implode(',', array_fill(0, $columnCount, '?')) . ')';
586            $parameters = [];
587            foreach ($chunk as $row) {
588                foreach ($row as $value) {
589                    $parameters[] = $value;
590                }
591            }
592            $statements[] = [
593                'sql' => $prefix . implode(',', array_fill(0, count($chunk), $rowPlaceholder)) . $suffix,
594                'parameters' => $parameters,
595            ];
596        }
597
598        return $statements;
599    }
600}