Lines 86.40% 648 / 750
Methods 59.01% 36 / 61
Classes 0.00% 0 / 1
Name Lines Methods CRAP
 readByAppointment 0.00% 0 / 7 0.00% 0 / 1 2
 readReservationByAppointment 100.00% 19 / 19 100.00% 1 / 1 4
 readAvailabilityByAppointment 97.05% 33 / 34 0.00% 0 / 1 7
 getRequiredSlotsByAvailability 94.11% 16 / 17 0.00% 0 / 1 5.01
 getSlotCountForAvailability 100.00% 3 / 3 100.00% 1 / 1 4
 readPreferredAvailabilityId 100.00% 15 / 15 100.00% 1 / 1 1
 selectPreferredAvailabilityId 97.61% 41 / 42 0.00% 0 / 1 17
 getSecondsOfDay 100.00% 2 / 2 100.00% 1 / 1 1
 readByAvailability 93.33% 14 / 15 0.00% 0 / 1 3.00
 lockSlotsForAppointment 91.66% 22 / 24 0.00% 0 / 1 4.01
 lockCandidateSlots 92.85% 13 / 14 0.00% 0 / 1 3.00
 hasScopeRelevantChanges 86.66% 13 / 15 0.00% 0 / 1 5.06
 isAvailabilityOutdated 84.78% 39 / 46 0.00% 0 / 1 19.14
 writeByAvailability 96.36% 53 / 55 0.00% 0 / 1 18
 writeByScope 0.00% 0 / 19 0.00% 0 / 1 20
 writeSlotLists 95.08% 58 / 61 0.00% 0 / 1 14
 readSlotIdsBetween 100.00% 18 / 18 100.00% 1 / 1 6
 clocksMatchStored 33.33% 2 / 6 0.00% 0 / 1 8.74
 slotClock 100.00% 2 / 2 100.00% 1 / 1 1
 writeExistingSlotRows 50.00% 2 / 4 0.00% 0 / 1 2.50
 writeNewSlotRows 75.00% 3 / 4 0.00% 0 / 1 2.06
 writeAncestorChains 93.75% 15 / 16 0.00% 0 / 1 4.00
 writeChunks 100.00% 2 / 2 100.00% 1 / 1 2
 readLastChangedTime 83.33% 5 / 6 0.00% 0 / 1 2.02
 readLastChangedTimeByScope 0.00% 0 / 9 0.00% 0 / 1 6
 readLastChangedTimeByAvailability 100.00% 9 / 9 100.00% 1 / 1 2
 updateSlotProcessMapping 85.71% 12 / 14 0.00% 0 / 1 4.05
 deleteSlotProcessOnSlot 0.00% 0 / 7 0.00% 0 / 1 6
 deleteSlotProcessOnProcess 0.00% 0 / 9 0.00% 0 / 1 6
 writeSlotProcessMappingFor 68.42% 13 / 19 0.00% 0 / 1 5.79
 writeSlotProcessMappingForProcessGroup 94.73% 36 / 38 0.00% 0 / 1 9.01
 getSlotTypeForMapping 100.00% 3 / 3 100.00% 1 / 1 3
 writeSlotProcessMappingForAvailability 100.00% 5 / 5 100.00% 1 / 1 1
 readMappedAvailabilityId 100.00% 4 / 4 100.00% 1 / 1 2
 deleteSlotProcessMappingFor 100.00% 4 / 4 100.00% 1 / 1 1
 writeCanceledByTimeAndScope 100.00% 11 / 11 100.00% 1 / 1 2
 writeCanceledByTime 100.00% 9 / 9 100.00% 1 / 1 2
 deleteSlotsOlderThan 100.00% 7 / 7 100.00% 1 / 1 2
 readRowsByScopeAndDate 100.00% 7 / 7 100.00% 1 / 1 1
 writeOptimizedSlotTables 62.50% 10 / 16 0.00% 0 / 1 4.84
 getLastGeneratedSlotDate 100.00% 9 / 9 100.00% 1 / 1 2
 getOldestSlotVersionByAvailability 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] __construct 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] init 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] getWriter 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] getReader 100.00% 5 / 5 100.00% 1 / 1 3
 [BO\Zmsbackend\Base] fetchPreparedStatement 100.00% 9 / 9 100.00% 1 / 1 3
 [BO\Zmsbackend\Base] startExecute 100.00% 5 / 5 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] pdoExceptionHandler 100.00% 13 / 13 100.00% 1 / 1 4
 [BO\Zmsbackend\Base] fetchStatement 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchOne 100.00% 7 / 7 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] fetchRow 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchValue 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchAll 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchHandle 100.00% 5 / 5 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchList 100.00% 8 / 8 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] writeItem 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] deleteItem 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] perform 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchAffected 100.00% 8 / 8 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] readResolvedReferences n/a 0 / 0 n/a 0 / 0 1
 [BO\Zmsbackend\Base] hashStringValue 50.00% 5 / 10 0.00% 0 / 1 8.12
17class Slot extends \BO\Zmsbackend\Base
18{
19    const int DEFAULT_API_CLIENT_ID = 1;
20
21    /**
22     * maximum number of slots per appointment
23     */
24    const int MAX_SLOTS = 96;
25
26    const int MAX_DAYS_OF_SLOT_CALCULATION = 180;
27
28    /**
29     * @return \BO\Zmsentities\Collection\SlotList
30     *
31     */
32    public function readByAppointment(
33        \BO\Zmsentities\Appointment $appointment,
34        int|null $overwriteSlotsCount = null,
35        bool $extendSlotList = false,
36        bool $lockSlots = false,
37        string $slotType = 'intern'
38    ) {
39        return $this->readReservationByAppointment(
40            $appointment,
41            $overwriteSlotsCount,
42            $extendSlotList,
43            $lockSlots,
44            $slotType
45        )['slotList'];
46    }
47
48    /**
49     * Like readByAppointment(), but also returns the availability the slots belong to. A reservation maps the
50     * process to exactly this availability, so the choice made under the slot locks is the one that is stored.
51     *
52     * @return array{availability: AvailabilityEntity, slotList: Collection}
53     */
54    public function readReservationByAppointment(
55        \BO\Zmsentities\Appointment $appointment,
56        int|null $overwriteSlotsCount = null,
57        bool $extendSlotList = false,
58        bool $lockSlots = false,
59        string $slotType = 'intern'
60    ): array {
61        $appointment = clone $appointment;
62        $availability = $this->readAvailabilityByAppointment(
63            $appointment,
64            $overwriteSlotsCount,
65            $extendSlotList,
66            $lockSlots,
67            $slotType
68        );
69        $appointment->slotCount = static::getSlotCountForAvailability(
70            $availability,
71            $appointment,
72            $overwriteSlotsCount
73        );
74        $slotList = $availability->getSlotList()->withSlotsForAppointment($appointment, $extendSlotList);
75        if ($lockSlots && count($slotList) > 0) {
76            // One FOR UPDATE for all slots avoids row-by-row lock interleaving (deadlocks with calculateSlots).
77            $this->lockSlotsForAppointment($slotList, $availability, $appointment->toDateTime());
78        } else {
79            foreach ($slotList as $slot) {
80                $this->readByAvailability($slot, $availability, $appointment->toDateTime(), false);
81            }
82        }
83        return ['availability' => $availability, 'slotList' => $slotList];
84    }
85
86    /**
87     * Choose the availability an appointment is booked on. Overlapping availabilities offer the same time,
88     * so the same rule as in writeSlotProcessMappingFor() decides which one gets the appointment.
89     *
90     * With $lockSlots the slots of all candidates are locked before their occupancy is read, so concurrent
91     * reservations for the same time wait for each other instead of choosing on outdated occupancy.
92     */
93    public function readAvailabilityByAppointment(
94        \BO\Zmsentities\Appointment $appointment,
95        int|null $overwriteSlotsCount = null,
96        bool $extendSlotList = false,
97        bool $lockSlots = false,
98        string $slotType = 'intern'
99    ): AvailabilityEntity {
100        $availabilityService = new \BO\Zmsbackend\Availability\Service\Availability();
101        $availabilityList = $availabilityService->readListByAppointment($appointment);
102        if (count($availabilityList) === 0) {
103            return $availabilityService->readByAppointment($appointment);
104        }
105        if (count($availabilityList) === 1) {
106            return $availabilityList->getFirst();
107        }
108        $candidateTimes = [];
109        $requiredSlots = static::getRequiredSlotsByAvailability(
110            $availabilityList,
111            $appointment,
112            $overwriteSlotsCount,
113            $extendSlotList,
114            false,
115            $candidateTimes
116        );
117        $scopeId = (int) $appointment->toProperty()->scope->id->get();
118        if ($lockSlots) {
119            $this->lockCandidateSlots(
120                $scopeId,
121                array_keys($requiredSlots),
122                $candidateTimes,
123                $appointment->toDateTime()
124            );
125        }
126        $availabilityId = $this->readPreferredAvailabilityId(
127            $scopeId,
128            $appointment->toDateTime(),
129            $requiredSlots,
130            $slotType
131        );
132        foreach ($availabilityList as $candidate) {
133            if ($availabilityId !== null && $candidate->getId() == $availabilityId) {
134                return $candidate;
135            }
136        }
137        $sorted = $availabilityList->getSortedByDuration();
138        return $sorted[array_key_first($sorted)];
139    }
140
141    /**
142     * Required slots per candidate availability, in the order of AvailabilityList::getSortedByDuration().
143     * This order is the only place that decides which of several overlapping availabilities is preferred.
144     *
145     * @param bool $keepNotFitting keep candidates the appointment does not fit in completely (an existing
146     *        booking must still be mapped somewhere), otherwise they are skipped
147     * @param array<string> $candidateTimes filled with the slot times of all candidates
148     *
149     * @return array<int, int> availability id => required slots
150     */
151    protected static function getRequiredSlotsByAvailability(
152        \BO\Zmsentities\Collection\AvailabilityList $availabilityList,
153        \BO\Zmsentities\Appointment $appointment,
154        int|null $overwriteSlotsCount,
155        bool $extendSlotList,
156        bool $keepNotFitting,
157        array &$candidateTimes = []
158    ): array {
159        $requiredSlots = [];
160        foreach ($availabilityList->getSortedByDuration() as $candidate) {
161            $candidateAppointment = clone $appointment;
162            $candidateAppointment->slotCount = static::getSlotCountForAvailability(
163                $candidate,
164                $appointment,
165                $overwriteSlotsCount
166            );
167            try {
168                $slotList = $candidate->getSlotList()->withSlotsForAppointment($candidateAppointment, $extendSlotList);
169            } catch (\BO\Zmsentities\Exception\AppointmentNotFitInSlotList) {
170                if ($keepNotFitting) {
171                    $requiredSlots[(int) $candidate->getId()] = max(1, (int) $candidateAppointment->slotCount);
172                }
173                continue;
174            }
175            $requiredSlots[(int) $candidate->getId()] = count($slotList);
176            foreach ($slotList as $slot) {
177                $candidateTimes[] = $slot->getTimeString();
178            }
179        }
180        return $requiredSlots;
181    }
182
183    protected static function getSlotCountForAvailability(
184        AvailabilityEntity $availability,
185        \BO\Zmsentities\Appointment $appointment,
186        int|null $overwriteSlotsCount = null
187    ): mixed {
188        // Check if availability allows multiple slots, but allow overwrite
189        if (!$availability->multipleSlotsAllowed || $overwriteSlotsCount >= 1) {
190            return ($overwriteSlotsCount >= 1) ? $overwriteSlotsCount : 1;
191        }
192        return $appointment->slotCount;
193    }
194
195    /**
196     * @param array<int, int> $requiredSlots candidate availability id => required slots, preferred first
197     *        (see getRequiredSlotsByAvailability())
198     * @param string $slotType "public" bookings need public capacity, all other bookings intern capacity
199     */
200    public function readPreferredAvailabilityId(
201        int $scopeId,
202        \DateTimeInterface $dateTime,
203        array $requiredSlots,
204        string $slotType = 'intern'
205    ): ?int {
206        $slotRows = $this->fetchAll(
207            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY,
208            [
209                'scopeID' => $scopeId,
210                'year' => $dateTime->format('Y'),
211                'month' => $dateTime->format('m'),
212                'day' => $dateTime->format('d'),
213            ]
214        );
215        return static::selectPreferredAvailabilityId(
216            $slotRows,
217            $dateTime->format('H:i:s'),
218            $requiredSlots,
219            $slotType
220        );
221    }
222
223    /**
224     * Pick one availability for an appointment starting at $time from the free slots of that day.
225     *
226     * Order: all required slots in a row with free capacity, then all required slots in a row,
227     * then only the first slot. Within each group the order of $requiredSlots decides (shorter availability
228     * first, see AvailabilityList::getSortedByDuration()).
229     * Free capacity is checked against the workstations of the booking type: public bookings must not
230     * use workstations that are only open for intern bookings.
231     *
232     * @param array<array<string, mixed>> $slotRows rows of QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY
233     * @param array<int, int> $requiredSlots candidate availability id => required slots, preferred first
234     */
235    public static function selectPreferredAvailabilityId(
236        array $slotRows,
237        string $time,
238        array $requiredSlots,
239        string $slotType = 'intern'
240    ): ?int {
241        $preference = array_flip(array_map('intval', array_keys($requiredSlots)));
242        $capacityField = ($slotType === 'public') ? 'public' : 'intern';
243        $slotsByAvailability = [];
244        foreach ($slotRows as $row) {
245            $slotsByAvailability[(int) $row['availabilityID']][] = $row;
246        }
247        $startSeconds = static::getSecondsOfDay($time);
248        $candidates = [];
249        foreach ($slotsByAvailability as $availabilityId => $slots) {
250            if (!array_key_exists($availabilityId, $preference)) {
251                continue;
252            }
253            $slotCount = max(1, (int) $requiredSlots[$availabilityId]);
254            usort($slots, fn($left, $right) => static::getSecondsOfDay($left['time'])
255                <=> static::getSecondsOfDay($right['time']));
256            $startIndex = null;
257            foreach ($slots as $index => $slot) {
258                $slotStart = static::getSecondsOfDay($slot['time']);
259                if ($slotStart <= $startSeconds && $startSeconds < $slotStart + (int) $slot['slotTimeInMinutes'] * 60) {
260                    $startIndex = $index;
261                    break;
262                }
263            }
264            if ($startIndex === null) {
265                continue;
266            }
267            $chain = array_slice($slots, $startIndex, $slotCount);
268            $isComplete = count($chain) === $slotCount;
269            $hasCapacity = true;
270            $previousEnd = null;
271            foreach ($chain as $slot) {
272                $slotStart = static::getSecondsOfDay($slot['time']);
273                if ($previousEnd !== null && $previousEnd !== $slotStart) {
274                    $isComplete = false;
275                }
276                $previousEnd = $slotStart + (int) $slot['slotTimeInMinutes'] * 60;
277                if ((int) $slot['confirmed'] >= (int) ($slot[$capacityField] ?? $slot['intern'])) {
278                    $hasCapacity = false;
279                }
280            }
281            $candidates[] = [
282                'availabilityId' => $availabilityId,
283                'rank' => ($isComplete && $hasCapacity) ? 0 : ($isComplete ? 1 : 2),
284                'preference' => $preference[$availabilityId],
285            ];
286        }
287        if ($candidates === []) {
288            return null;
289        }
290        usort($candidates, fn($left, $right) => [$left['rank'], $left['preference']]
291            <=> [$right['rank'], $right['preference']]);
292        return $candidates[0]['availabilityId'];
293    }
294
295    protected static function getSecondsOfDay(string $time): int
296    {
297        $parts = array_map('intval', explode(':', $time));
298        return ($parts[0] ?? 0) * 3600 + ($parts[1] ?? 0) * 60 + ($parts[2] ?? 0);
299    }
300
301    public function readByAvailability(
302        \BO\Zmsentities\Slot $slot,
303        AvailabilityEntity $availability,
304        \DateTimeInterface $date,
305        bool $getLock = false
306    ) {
307        $data = array();
308        $data['scopeID'] = $availability['scope']['id'];
309        $data['availabilityID'] = $availability['id'];
310        $data['year'] = $date->format('Y');
311        $data['month'] = $date->format('m');
312        $data['day'] = $date->format('d');
313        $data['time'] = $slot->getTimeString();
314        $sql = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOT;
315        if ($getLock) {
316            $sql .= ' FOR UPDATE';
317        }
318        $slotID = $this->fetchRow(
319            $sql,
320            $data
321        );
322        return $slotID ? $slotID['slotID'] : false ;
323    }
324
325    /**
326     * Lock all slots required for an appointment in a single SELECT ... FOR UPDATE.
327     * Uses ORDER BY slotID ASC so lock acquisition prefers primary-key order.
328     */
329    protected function lockSlotsForAppointment(
330        Collection $slotList,
331        AvailabilityEntity $availability,
332        \DateTimeInterface $date
333    ): void {
334        $times = [];
335        foreach ($slotList as $slot) {
336            if (!$slot instanceof Entity) {
337                continue;
338            }
339            $times[] = $slot->getTimeString();
340        }
341        $times = array_values(array_unique($times));
342        if ($times === []) {
343            return;
344        }
345
346        $placeholders = implode(',', array_fill(0, count($times), '?'));
347        $sql = sprintf(
348            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOTS_FOR_UPDATE,
349            $placeholders
350        );
351        $params = array_merge(
352            [
353                $availability['scope']['id'],
354                $availability['id'],
355                $date->format('Y'),
356                $date->format('m'),
357                $date->format('d'),
358            ],
359            $times
360        );
361        $this->fetchAll($sql, $params);
362    }
363
364    /**
365     * @param array<int|string> $availabilityIds
366     * @param array<string> $times
367     */
368    protected function lockCandidateSlots(
369        int $scopeId,
370        array $availabilityIds,
371        array $times,
372        \DateTimeInterface $date
373    ): void {
374        $availabilityIds = array_values(array_unique(array_map('intval', $availabilityIds)));
375        $times = array_values(array_unique($times));
376        if ($availabilityIds === [] || $times === []) {
377            return;
378        }
379        $sql = sprintf(
380            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_CANDIDATE_SLOTS_FOR_UPDATE,
381            implode(',', array_fill(0, count($availabilityIds), '?')),
382            implode(',', array_fill(0, count($times), '?'))
383        );
384        $this->fetchAll($sql, array_merge(
385            [$scopeId, $date->format('Y'), $date->format('m'), $date->format('d')],
386            $availabilityIds,
387            $times
388        ));
389    }
390
391    /**
392     * @return null|true
393     */
394    public function hasScopeRelevantChanges(
395        \BO\Zmsentities\Scope $scope,
396        ?\DateTimeInterface $slotLastChange = null
397    ) {
398        $startInDaysDefault = (new \BO\Zmsbackend\Preferences\Service\Preferences())
399            ->readProperty('scope', $scope->id, 'appointment', 'startInDaysDefault');
400        $endInDaysDefault = (new \BO\Zmsbackend\Preferences\Service\Preferences())
401            ->readProperty('scope', $scope->id, 'appointment', 'endInDaysDefault');
402        if (
403            $scope->preferences['appointment']['startInDaysDefault'] != $startInDaysDefault
404            || $scope->preferences['appointment']['endInDaysDefault'] != $endInDaysDefault
405        ) {
406            (new \BO\Zmsbackend\Scope\Service\Scope())->replacePreferences($scope); //TODO remove after ZMS1 is deactivated
407            return true;
408        }
409        $startInDaysChange = (new \BO\Zmsbackend\Preferences\Service\Preferences())
410            ->readChangeDateTime('scope', $scope->id, 'appointment', 'startInDaysDefault');
411        $endInDaysChange = (new \BO\Zmsbackend\Preferences\Service\Preferences())
412            ->readChangeDateTime('scope', $scope->id, 'appointment', 'endInDaysDefault');
413        if (
414            $startInDaysChange->getTimestamp() > $slotLastChange->getTimestamp()
415            || $endInDaysChange->getTimestamp() > $slotLastChange->getTimestamp()
416        ) {
417            return true;
418        }
419    }
420
421    public function isAvailabilityOutdated(
422        \BO\Zmsentities\Availability $availability,
423        \DateTimeInterface $now,
424        ?\DateTimeInterface $slotLastChange = null,
425        int $oldestSlotVersion = 1
426    ): bool {
427        $proposedChange = new \BO\Zmsbackend\Helper\AvailabilitySnapShot($availability, $now);
428        $formerChange = new \BO\Zmsbackend\Helper\AvailabilitySnapShot($availability, $slotLastChange);
429
430        if ($availability->version > $oldestSlotVersion) {
431            $availability['processingNote'][] = 'outdated: availability version change';
432            return true;
433        }
434
435        if ($formerChange->hasOutdatedAvailability()) {
436            $availability['processingNote'][] = 'outdated: availability change';
437            return true;
438        }
439        if (
440            $formerChange->hasOutdatedScope()
441            && $this->hasScopeRelevantChanges($availability->scope, $slotLastChange)
442        ) {
443            $availability['processingNote'][] = 'outdated: scope change';
444            return true;
445        }
446        if ($formerChange->hasOutdatedDayoff()) {
447            $availability['processingNote'][] = 'outdated: dayoff change';
448            return true;
449        }
450        // Be aware, that last slot change and current time might differ serval days
451        //  if the rebuild fails in some way
452        if (
453            1
454            // First check if the bookable end date on current time was already calculated on last slot change
455            && !$formerChange->hasBookableDateTime($proposedChange->getLastBookableDateTime())
456            // Second check if between last slot change and current time could be a bookable slot
457            && (
458                (
459                    !$formerChange->isOpenedOnLastBookableDay()
460                    && $proposedChange->hasBookableDateTimeAfter($formerChange->getLastBookableDateTime())
461                )
462                // if calculation already happened the day before, check if lastChange time was before opening
463                || (
464                    $formerChange->isOpenedOnLastBookableDay()
465                    && (
466                        !$formerChange->isTimeOpenedOnLastBookableDay()
467                        || $proposedChange->hasBookableDateTimeAfter(
468                            $formerChange->getLastBookableDateTime()->modify('+1day 00:00:00')
469                        )
470                    )
471                )
472            )
473            // Check if daytime is after booking start time if bookable end of now is calculated
474            && (
475                !$proposedChange->isOpenedOnLastBookableDay()
476                || $proposedChange->isTimeOpenedOnLastBookableDay()
477            )
478        ) {
479            $availability['processingNote'][] = 'outdated: new slots required';
480            return true;
481        }
482        if (
483            $availability->getBookableStart($slotLastChange) != $availability->getBookableStart($now)
484            // First check, if bookable start from lastChange was not included in bookable time from now
485            && !$availability->hasDate($availability->getBookableStart($slotLastChange), $now)
486            // Second check, if availability had a bookable time on lastChange before bookable start from now
487            && $availability->hasDateBetween(
488                $availability->getBookableStart($slotLastChange),
489                $availability->getBookableStart($now),
490                $slotLastChange
491            )
492        ) {
493            $availability['processingNote'][] = 'outdated: slots invalidated by bookable start';
494            return true;
495        }
496        $availability['processingNote'][] = 'not outdated';
497        return false;
498    }
499
500    /**
501     * @return bool TRUE if there were changes on slots
502     */
503    public function writeByAvailability(
504        \BO\Zmsentities\Availability $availability,
505        \DateTimeInterface $now,
506        ?\DateTimeInterface $slotLastChange = null
507    ) {
508        $now = \BO\Zmsentities\Helper\DateTime::create($now);
509        $calculateSlotsUntilDate = \BO\Zmsentities\Helper\DateTime::create($now)->modify('+' . self::MAX_DAYS_OF_SLOT_CALCULATION . ' days');
510        if (!$slotLastChange) {
511            $slotLastChange = $this->readLastChangedTimeByAvailability($availability);
512        }
513        $lastGeneratedSlotDate = $this->getLastGeneratedSlotDate($availability);
514        $oldestSlotVersion = $this->getOldestSlotVersionByAvailability($availability);
515        $availability['processingNote'][] = 'lastchange=' . $slotLastChange->format('c');
516        if (!$this->isAvailabilityOutdated($availability, $now, $slotLastChange, $oldestSlotVersion)) {
517            return false;
518        }
519        $startDate = $availability->getBookableStart($now)->modify('00:00:00');
520        $stopDate = $availability->getBookableEnd($now);
521        $generateNew = empty($lastGeneratedSlotDate) || $availability->version > $oldestSlotVersion;
522        (new \BO\Zmsbackend\Availability\Service\Availability())->readLock($availability->id);
523        $cancelledSlots = 0;
524        $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY_BEFORE_BOOKABLE, [
525            'availabilityID' => $availability->id,
526            'providedDate' => $startDate->format('Y-m-d')
527        ]);
528        // Cancel slots only if previously generated beyond new bookable end
529        if ($lastGeneratedSlotDate && $lastGeneratedSlotDate->getTimestamp() > $stopDate->getTimestamp()) {
530            $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY_AFTER_BOOKABLE, [
531                'availabilityID' => $availability->id,
532                'providedDate' => $stopDate->format('Y-m-d')
533            ]);
534        }
535        if ($generateNew) {
536            $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY, [
537                'availabilityID' => $availability->id,
538            ]);
539
540            if (!$availability->withData(['bookable' => ['startInDays' => 0]])->hasBookableDates($now)) {
541                $availability['processingNote'][] = "cancelled $cancelledSlots slots: availability not bookable ";
542                return ($cancelledSlots > 0) ? true : false;
543            }
544
545            $scope = $availability->scope;
546            \App::$log->info('availability: ', [
547                'generate_new' => $generateNew,
548                'scope_id' => is_object($scope) && isset($scope->id) ? (int) $scope->id : null,
549                'availability_id' => (int) $availability->id,
550                'cancelledSlots' => $cancelledSlots
551            ]);
552
553            $availability['processingNote'][] = "cancelled $cancelledSlots slots";
554        } elseif ($cancelledSlots > 0) {
555            $availability['processingNote'][] = "cancelled $cancelledSlots slots";
556        }
557
558        $slotlist = $availability->getSlotList();
559        $slotlistIntern = $slotlist->withValueFor('public', 0);
560        $time = $now->modify('00:00:00');
561        if (!$generateNew) {
562            $time = $lastGeneratedSlotDate->modify('+1 day')->modify('00:00:00');
563        }
564        $days = [];
565        do {
566            if ($availability->withData(['bookable' => ['startInDays' => 0]])->hasDate($time, $now)) {
567                $days[] = [
568                    'time' => $time,
569                    'slotlist' => ($time->getTimestamp() < $startDate->getTimestamp()) ? $slotlistIntern : $slotlist,
570                ];
571            }
572            $time = $time->modify('+1day');
573        } while ($time->getTimestamp() <= $stopDate->getTimestamp() && $time->getTimestamp() < $calculateSlotsUntilDate->getTimestamp());
574
575        $status = $this->writeSlotLists($days, $availability);
576
577        return $status || (isset($cancelledSlots) && $cancelledSlots > 0);
578    }
579
580    public function writeByScope(\BO\Zmsentities\Scope $scope, \DateTimeInterface $now): \BO\Zmsentities\Collection\AvailabilityList
581    {
582        $slotLastChange = $this->readLastChangedTimeByScope($scope);
583        $availabilityList = (new \BO\Zmsbackend\Availability\Service\Availability())
584            ->readAvailabilityListByScope($scope, 0, $slotLastChange->modify('-1 day'))
585            ;
586        $updatedList = new \BO\Zmsentities\Collection\AvailabilityList();
587        foreach ($availabilityList as $availability) {
588            $availability->scope = clone $scope; //dayoff is required
589            $availabilityStarted = microtime(true);
590            $changed = $this->writeByAvailability($availability, $now);
591            \App::$log->info($changed ? 'Updated availability' : 'Availability unchanged', [
592                'scope_id' => (int) $scope->id,
593                'availability_id' => (int) $availability->id,
594                'duration_seconds' => round(microtime(true) - $availabilityStarted, 2),
595                'changed' => $changed,
596                'reason' => $availability['processingNote'] ?? [],
597            ]);
598            if ($changed) {
599                $updatedList->addEntity($availability);
600            }
601        }
602        return $updatedList;
603    }
604
605    /**
606     * Write every open day in a few statements.
607     * A day whose stored times already match keeps its ancestor rows.
608     * Days that need a new chain are written together: level 1 is the slot itself,
609     * higher levels are the earlier slots that day, at most 96.
610     *
611     * @param list<array{time: \DateTimeInterface, slotlist: Collection}> $days
612     */
613    private function writeSlotLists(array $days, AvailabilityEntity $availability): bool
614    {
615        $createdAt = time();
616        $slotRepository = new \BO\Zmsbackend\Slot\Repository\Slot(\BO\Zmsbackend\Query\Base::INSERT);
617        $prepared = [];
618        foreach ($days as $day) {
619            $rows = [];
620            foreach ($day['slotlist'] as $slot) {
621                $slot = clone $slot;
622                $slot->status = 'free';
623                $values = $slotRepository->reverseEntityMapping($slot, $availability, $day['time']);
624                $values['createTimestamp'] = $createdAt;
625                $rows[] = $values;
626            }
627            if ($rows !== []) {
628                $prepared[] = ['time' => $day['time'], 'rows' => $rows];
629            }
630        }
631        if ($prepared === []) {
632            return false;
633        }
634
635        $storedByDay = $this->readSlotIdsBetween(
636            $availability,
637            $prepared[0]['time'],
638            $prepared[array_key_last($prepared)]['time']
639        );
640        $updates = [];
641        $inserts = [];
642        $added = [];
643        $unchanged = [];
644        foreach ($prepared as $day) {
645            $dateKey = $day['time']->format('Y-m-d');
646            $stored = $storedByDay[$dateKey] ?? [];
647            $clocks = [];
648            foreach ($day['rows'] as $values) {
649                $clock = $this->slotClock((string) $values['time']);
650                $clocks[] = $clock;
651                if (isset($stored[$clock])) {
652                    $updates[] = \BO\Zmsbackend\Slot\Repository\Slot::packSlotRow($values, $stored[$clock]);
653                } else {
654                    $inserts[] = \BO\Zmsbackend\Slot\Repository\Slot::packSlotRow($values);
655                    $added[$dateKey] = true;
656                }
657            }
658            $unchanged[$dateKey] = $this->clocksMatchStored($stored, $clocks);
659        }
660
661        $this->writeExistingSlotRows($updates);
662        $this->writeNewSlotRows($inserts);
663        if ($inserts !== []) {
664            $storedByDay = $this->readSlotIdsBetween(
665                $availability,
666                $prepared[0]['time'],
667                $prepared[array_key_last($prepared)]['time']
668            );
669            foreach (array_keys($added) as $dateKey) {
670                $availability['processingNote'][] = 'Added ' . $dateKey;
671            }
672        }
673
674        $ancestorChains = [];
675        foreach ($prepared as $day) {
676            $dateKey = $day['time']->format('Y-m-d');
677            if ($unchanged[$dateKey]) {
678                continue;
679            }
680            $stored = $storedByDay[$dateKey] ?? [];
681            $orderedSlotIds = [];
682            foreach ($day['rows'] as $values) {
683                $clock = $this->slotClock((string) $values['time']);
684                if (!isset($stored[$clock])) {
685                    throw new \RuntimeException('Slot calculation did not store every slot for ' . $dateKey);
686                }
687                $orderedSlotIds[] = $stored[$clock];
688            }
689            $ancestorChains[] = ['date' => $dateKey, 'slotIds' => $orderedSlotIds];
690        }
691        $this->writeAncestorChains($ancestorChains);
692
693        return true;
694    }
695
696    /**
697     * @return array<string, array<string, int>> date => clock time => slotID, first row wins when a time is stored twice
698     */
699    private function readSlotIdsBetween(
700        AvailabilityEntity $availability,
701        \DateTimeInterface $from,
702        \DateTimeInterface $to
703    ): array {
704        $scope = $availability->scope;
705        $rows = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOTS_IN_RANGE, [
706            'scopeID' => $scope instanceof ScopeEntity ? (int) $scope->id : (int) ($scope['id'] ?? 0),
707            'availabilityID' => $availability->id,
708            'yearFrom' => $from->format('Y'),
709            'yearTo' => $to->format('Y'),
710        ]);
711        $fromKey = $from->format('Y-m-d');
712        $toKey = $to->format('Y-m-d');
713        $byDay = [];
714        foreach ($rows as $row) {
715            $dateKey = sprintf('%04d-%02d-%02d', $row['year'], $row['month'], $row['day']);
716            if ($dateKey < $fromKey || $dateKey > $toKey) {
717                continue;
718            }
719            $clock = $this->slotClock((string) $row['time']);
720            if (!isset($byDay[$dateKey][$clock])) {
721                $byDay[$dateKey][$clock] = (int) $row['slotID'];
722            }
723        }
724
725        return $byDay;
726    }
727
728    /**
729     * @param array<string, int> $stored
730     * @param list<string> $clocks
731     */
732    private function clocksMatchStored(array $stored, array $clocks): bool
733    {
734        if (count($stored) !== count($clocks)) {
735            return false;
736        }
737        foreach ($clocks as $clock) {
738            if (!isset($stored[$clock])) {
739                return false;
740            }
741        }
742
743        return true;
744    }
745
746    private function slotClock(string $time): string
747    {
748        $parts = explode(':', $time);
749
750        return sprintf('%02d:%02d:%02d', (int) ($parts[0] ?? 0), (int) ($parts[1] ?? 0), (int) ($parts[2] ?? 0));
751    }
752
753    /**
754     * @param list<list<int|string>> $rows slotID plus SLOT_WRITE_COLUMNS
755     */
756    private function writeExistingSlotRows(array $rows): void
757    {
758        if ($rows === []) {
759            return;
760        }
761        $statement = \BO\Zmsbackend\Slot\Repository\Slot::upsertSlotRowsSql();
762        $this->writeChunks($statement['prefix'], $statement['suffix'], $rows);
763    }
764
765    /**
766     * @param list<list<int|string>> $rows slot columns, without slotID
767     */
768    private function writeNewSlotRows(array $rows): void
769    {
770        if ($rows === []) {
771            return;
772        }
773        $statement = \BO\Zmsbackend\Slot\Repository\Slot::insertSlotRowsSql();
774        $this->writeChunks($statement['prefix'], $statement['suffix'], $rows);
775    }
776
777    /**
778     * One chain for every day that changed. The pairs match the old per-slot loop.
779     *
780     * @param list<array{date: string, slotIds: list<int>}> $days
781     */
782    private function writeAncestorChains(array $days): void
783    {
784        if ($days === []) {
785            return;
786        }
787        $repository = \BO\Zmsbackend\Slot\Repository\Slot::class;
788        $this->perform($repository::QUERY_CREATE_CALC_SLOT_ORDER);
789        $this->perform($repository::QUERY_DELETE_CALC_SLOT_ORDER);
790        $slotOrderRows = [];
791        foreach ($days as $day) {
792            $sequence = 1;
793            foreach ($day['slotIds'] as $slotId) {
794                $slotOrderRows[] = [$day['date'], $sequence, $slotId];
795                $sequence++;
796            }
797        }
798        $this->writeChunks($repository::QUERY_INSERT_CALC_SLOT_ORDER, '', $slotOrderRows);
799        $this->perform($repository::QUERY_DELETE_HIERA_FOR_CALC_ORDER);
800        $maximumEarlierSlots = self::MAX_SLOTS - 1;
801        $this->perform($repository::insertAncestorChainsSql($maximumEarlierSlots));
802        $this->perform($repository::QUERY_DELETE_CALC_SLOT_ORDER);
803    }
804
805    /**
806     * @param list<list<int|string>> $rows
807     */
808    private function writeChunks(string $sql, string $suffix, array $rows): void
809    {
810        foreach (\BO\Zmsbackend\Slot\Repository\Slot::chunkedStatements($sql, $suffix, $rows) as $statement) {
811            $this->perform($statement['sql'], $statement['parameters']);
812        }
813    }
814
815    public function readLastChangedTime(): \DateTimeImmutable
816    {
817        $last = $this->fetchRow(
818            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED
819        );
820        if (!$last['dateString']) {
821            $last['dateString'] = '1970-01-01 12:00';
822        }
823        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone);
824    }
825
826    public function readLastChangedTimeByScope(ScopeEntity $scope): \DateTimeImmutable
827    {
828        $last = $this->fetchRow(
829            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED_SCOPE,
830            [
831                'scopeID' => $scope->id,
832            ]
833        );
834        if (!$last['dateString']) {
835            $last['dateString'] = '1970-01-01 12:00';
836        }
837        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone);
838    }
839
840    public function readLastChangedTimeByAvailability(AvailabilityEntity $availabiliy): \DateTimeImmutable
841    {
842        $last = $this->fetchRow(
843            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED_AVAILABILITY,
844            [
845                'availabilityID' => $availabiliy->id,
846            ]
847        );
848        if (!$last['dateString']) {
849            $last['dateString'] = '1970-01-01 12:00';
850        }
851        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone);
852    }
853
854    public function updateSlotProcessMapping(int|string|null $scopeID = null, ?\DateTimeInterface $now = null): int
855    {
856        $sql = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MISSING_PROCESS;
857        $params = [];
858        if ($now !== null) {
859            $sql .= ' AND b.Datum >= :fromDate';
860            $params['fromDate'] = \DateTimeImmutable::createFromInterface($now)->modify('-1 day')->format('Y-m-d');
861        }
862        if ($scopeID) {
863            $sql .= \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MISSING_PROCESS_BY_SCOPE;
864            $params['scopeID'] = $scopeID;
865        }
866        $processIdList = $this->fetchAll($sql, $params);
867        // Map main processes first, so following processes can take the availability of their main process
868        usort($processIdList, fn($left, $right) => [(int) $left['istFolgeterminvon'] > 0, (int) $left['BuergerID']]
869            <=> [(int) $right['istFolgeterminvon'] > 0, (int) $right['BuergerID']]);
870        foreach ($processIdList as $process) {
871            $this->writeSlotProcessMappingFor($process['BuergerID']);
872        }
873
874        return count($processIdList);
875    }
876
877    public function deleteSlotProcessOnSlot(int|string|null $scopeID = null): void
878    {
879        if ($scopeID) {
880            $this->perform(
881                \BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED
882                . \BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED_BY_SCOPE,
883                ['scopeID' => $scopeID]
884            );
885        } else {
886            $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED, []);
887        }
888    }
889
890    public function deleteSlotProcessOnProcess(int|string|null $scopeID = null): int
891    {
892        $select = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS;
893        $params = [];
894        if ($scopeID) {
895            $select .= \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS_BY_SCOPE;
896            $params['scopeID'] = $scopeID;
897        }
898        // Delete the stale link only. A slot that still belongs to the appointment stays mapped.
899        $sql = 'DELETE sp_del FROM slot_process sp_del INNER JOIN (
900            SELECT slotID, processID FROM (' . $select . ') stale_ids
901        ) stale ON stale.processID = sp_del.processID AND stale.slotID = sp_del.slotID';
902
903        return $this->fetchAffected($sql, $params);
904    }
905
906    /**
907     * Map a process to exactly one slot. With overlapping availabilities a process and its following
908     * processes are mapped to slots of the same availability, otherwise the booking would be counted twice.
909     *
910     * @param int|null $availabilityId availability already chosen (and locked) by the reservation, otherwise the
911     *        availability is chosen like in readAvailabilityByAppointment()
912     */
913    public function writeSlotProcessMappingFor(string|int $processId, ?int $availabilityId = null): static
914    {
915        $process = $this->fetchRow(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING, [
916            'processId' => $processId,
917        ]);
918        if (!$process) {
919            return $this;
920        }
921        $mainProcessId = (int) $process['istFolgeterminvon'];
922        if ($mainProcessId > 0) {
923            $mainAvailabilityId = $this->readMappedAvailabilityId($mainProcessId);
924            if ($mainAvailabilityId) {
925                $this->deleteSlotProcessMappingFor($process['BuergerID']);
926                $this->writeSlotProcessMappingForAvailability($process['BuergerID'], $mainAvailabilityId);
927                return $this;
928            }
929            $mainProcess = $this->fetchRow(
930                \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING,
931                ['processId' => $mainProcessId]
932            );
933            $process = $mainProcess ? $mainProcess : $process;
934        }
935        $this->writeSlotProcessMappingForProcessGroup($process, $availabilityId);
936        return $this;
937    }
938
939    /**
940     * @param array<string, mixed> $mainProcess row of QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING
941     */
942    protected function writeSlotProcessMappingForProcessGroup(array $mainProcess, ?int $availabilityId = null): void
943    {
944        $processIdList = [$mainProcess['BuergerID']];
945        if ((int) $mainProcess['istFolgeterminvon'] === 0) {
946            $following = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_FOLLOWING_PROCESS_IDS, [
947                'processId' => $mainProcess['BuergerID'],
948            ]);
949            foreach ($following as $row) {
950                $processIdList[] = $row['BuergerID'];
951            }
952        }
953        // Remove existing mappings first, so the group does not count against its own capacity
954        foreach ($processIdList as $processId) {
955            $this->deleteSlotProcessMappingFor($processId);
956        }
957        if (!$mainProcess['Datum'] || str_starts_with((string) $mainProcess['Datum'], '0000')) {
958            return;
959        }
960        if ($availabilityId === null) {
961            $slotCount = max(1 + (int) $mainProcess['hatFolgetermine'], count($processIdList));
962            $dateTime = new \DateTimeImmutable($mainProcess['Datum'] . ' ' . $mainProcess['Uhrzeit']);
963            $appointment = new \BO\Zmsentities\Appointment([
964                'date' => $dateTime->getTimestamp(),
965                'scope' => ['id' => (int) $mainProcess['StandortID']],
966                'slotCount' => $slotCount,
967            ]);
968            $availabilityList = (new \BO\Zmsbackend\Availability\Service\Availability())
969                ->readListByAppointment($appointment);
970            // The booking already exists, so it is mapped even if it does not fit completely
971            $requiredSlots = static::getRequiredSlotsByAvailability(
972                $availabilityList,
973                $appointment,
974                $slotCount,
975                false,
976                true
977            );
978            $availabilityId = $this->readPreferredAvailabilityId(
979                (int) $mainProcess['StandortID'],
980                $dateTime,
981                $requiredSlots,
982                static::getSlotTypeForMapping($mainProcess)
983            );
984        }
985        if ($availabilityId === null) {
986            return;
987        }
988        foreach ($processIdList as $processId) {
989            $this->writeSlotProcessMappingForAvailability($processId, $availabilityId);
990        }
991    }
992
993    /**
994     * Capacity type of an existing booking. The default client is public by column default and is shared by
995     * admin bookings and bookings without their own client key, so its access level does not describe them.
996     *
997     * @param array<string, mixed> $process row of QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING
998     */
999    public static function getSlotTypeForMapping(array $process): string
1000    {
1001        $isOwnApiClient = (int) ($process['apiClientID'] ?? 0) > static::DEFAULT_API_CLIENT_ID;
1002        $accessLevel = (string) ($process['accesslevel'] ?? '');
1003        return ($isOwnApiClient && $accessLevel !== '') ? $accessLevel : 'intern';
1004    }
1005
1006    protected function writeSlotProcessMappingForAvailability(string|int $processId, int $availabilityId): void
1007    {
1008        $this->deleteSlotProcessMappingFor($processId);
1009        $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_INSERT_SLOT_PROCESS_ID, [
1010            'processId' => $processId,
1011            'availabilityId' => $availabilityId,
1012        ]);
1013    }
1014
1015    public function readMappedAvailabilityId($processId): ?int
1016    {
1017        $row = $this->fetchRow(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MAPPED_AVAILABILITY_BY_PROCESS, [
1018            'processId' => $processId,
1019        ]);
1020        return $row ? (int) $row['availabilityID'] : null;
1021    }
1022
1023    public function deleteSlotProcessMappingFor(string|int $processId): static
1024    {
1025        $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_ID, [
1026            'processId' => $processId,
1027        ]);
1028        return $this;
1029    }
1030
1031    public function writeCanceledByTimeAndScope(\DateTimeInterface $dateTime, \BO\Zmsentities\Scope $scope): bool
1032    {
1033        $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY_BY_SCOPE, [
1034            'dateString' => $dateTime->format('Y-m-d'),
1035            'scopeID' => $scope->id,
1036        ]);
1037
1038        return $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_SLOT_OLD_BY_SCOPE, [
1039            'year' => $dateTime->format('Y'),
1040            'month' => $dateTime->format('m'),
1041            'day' => $dateTime->format('d'),
1042            'time' => $dateTime->format('H:i:s'),
1043            'scopeID' => $scope->id,
1044        ]) && $status;
1045    }
1046
1047    public function writeCanceledByTime(\DateTimeInterface $dateTime): bool
1048    {
1049        $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY, [
1050            'dateString' => $dateTime->format('Y-m-d'),
1051        ]);
1052        return $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_SLOT_OLD, [
1053            'year' => $dateTime->format('Y'),
1054            'month' => $dateTime->format('m'),
1055            'day' => $dateTime->format('d'),
1056            'time' => $dateTime->format('H:i:s'),
1057        ]) && $status;
1058    }
1059
1060    public function deleteSlotsOlderThan(\DateTimeInterface $dateTime): bool
1061    {
1062        $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_OLD, [
1063            'year' => $dateTime->format('Y'),
1064            'month' => $dateTime->format('m'),
1065            'day' => $dateTime->format('d'),
1066        ]);
1067        $status = ($status && $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_HIERA));
1068        return $status;
1069    }
1070
1071    /**
1072     * This function is for debugging
1073     */
1074    public function readRowsByScopeAndDate(
1075        \BO\Zmsentities\Scope $scope,
1076        \DateTimeInterface $dateTime
1077    ) {
1078        $list = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_BY_SCOPE_AND_DAY, [
1079            'year' => $dateTime->format('Y'),
1080            'month' => $dateTime->format('m'),
1081            'day' => $dateTime->format('d'),
1082            'scopeID' => $scope->id,
1083        ]);
1084        return $list;
1085    }
1086
1087    public function writeOptimizedSlotTables(): bool
1088    {
1089        $queries = [
1090            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT,
1091            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT_HIERA,
1092            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT_PROCESS,
1093            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_PROCESS,
1094        ];
1095
1096        $status = true;
1097        foreach ($queries as $query) {
1098            try {
1099                $status = $status && $this->perform($query);
1100            } catch (\PDOException $e) {
1101                \App::$log->error("Failed to optimize table with query: $query. Error: " . $e->getMessage(), [
1102                    'scope_id' => null,
1103                    'availability_id' => null,
1104                ]);
1105
1106                return false;
1107            }
1108        }
1109
1110        return $status;
1111    }
1112
1113    private function getLastGeneratedSlotDate(AvailabilityEntity $availability): \DateTimeImmutable|null
1114    {
1115        $last = $this->fetchRow(
1116            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_IN_AVAILABILITY,
1117            [
1118                'availabilityID' => $availability->id,
1119            ]
1120        );
1121
1122        if (!isset($last['dateString'])) {
1123            return null;
1124        }
1125
1126        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone);
1127    }
1128
1129    private function getOldestSlotVersionByAvailability(AvailabilityEntity $availability)
1130    {
1131        $last = $this->fetchRow(
1132            \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OLDEST_VERSION_IN_AVAILABILITY,
1133            [
1134                'availabilityID' => $availability->id,
1135            ]
1136        );
1137
1138        return $last['version'] ?? 1;
1139    }
1140}

Inherited from BO\Zmsbackend\Base

38    public function __construct(?Connection\Pdo $writeConnection = null, ?Connection\Pdo $readConnection = null)
39    {
40        $this->writeDb = $writeConnection;
41        $this->readDb = $readConnection;
42    }
44    public static function init(?Connection\Pdo $writeConnection = null, ?Connection\Pdo $readConnection = null): static
45    {
46        $instance = new static($writeConnection, $readConnection);
47        return $instance;
48    }
50    public function getWriter(): Connection\Pdo
51    {
52        if (null === $this->writeDb) {
53            $this->writeDb = Connection\Select::getWriteConnection();
54            $this->readDb = $this->writeDb;
55        }
56        return $this->writeDb;
57    }
59    public function getReader(): Connection\Pdo
60    {
61        if (null !== $this->writeDb) {
62            // if readDB gets a reset, still use writeDB
63            return $this->writeDb;
64        }
65        if (null === $this->readDb) {
66            $this->readDb = Connection\Select::getReadConnection();
67        }
68        return $this->readDb;
69    }
71    public function fetchPreparedStatement(Query\Base|string $query)
72    {
73        $sql = "$query";
74        $reader = $this->getReader();
75        if (spl_object_hash($reader) != static::$preparedConnectionId) {
76            // do not use prepared statements on a different connection
77            static::$preparedCache = [];
78            static::$preparedConnectionId = spl_object_hash($reader);
79        }
80        if (!isset(static::$preparedCache[$sql])) {
81            $prepared = $this->getReader()->prepare($sql);
82            static::$preparedCache[$sql] = $prepared;
83        }
84        return static::$preparedCache[$sql];
85    }
87    public function startExecute(\PDOStatement $statement, array $parameters)
88    {
89        $statement = static::pdoExceptionHandler(function () use ($statement, $parameters) {
90            $statement->execute($parameters);
91            return $statement;
92        });
93        return $statement;
94    }
96    protected static function pdoExceptionHandler(\Closure $pdoFunction, array $parameters = [])
97    {
98        try {
99            $statement = $pdoFunction($parameters);
100        } catch (\PDOException $pdoException) {
101            if (stripos($pdoException->getMessage(), 'Lock wait timeout') !== false) {
102                throw new \BO\Zmsbackend\Exception\Pdo\LockTimeout();
103            }
104            //@codeCoverageIgnoreStart
105            if (stripos($pdoException->getMessage(), 'Deadlock found') !== false) {
106                throw new \BO\Zmsbackend\Exception\Pdo\DeadLockFound();
107            }
108            //@codeCoverageIgnoreEnd
109            $message = "SQL: "
110                . " Err: "
111                . $pdoException->getMessage()
112                //. " || Statement: "
113                //.$statement->queryString
114                //." || Parameters=". var_export($parameters, true)
115                ;
116            throw new \BO\Zmsbackend\Exception\Pdo\PDOFailed($message, 0, $pdoException);
117        }
118        return $statement;
119    }
121    public function fetchStatement(Query\Base $query)
122    {
123        $parameters = $query->getParameters();
124        $statement = $this->startExecute($this->fetchPreparedStatement($query), $parameters);
125        return $statement;
126    }
133    public function fetchOne(Query\Base $query, \BO\Zmsentities\Schema\Entity $entity)
134    {
135        $statement = $this->fetchStatement($query);
136        $data = $statement->fetch(\PDO::FETCH_ASSOC);
137        if ($data) {
138            $entity->exchangeArray($query->postProcessJoins($data));
139            $entity->setResolveLevel($query->getResolveLevel());
140        }
141        $statement->closeCursor();
142        return $entity;
143    }
145    public function fetchRow(Query\Base|string $query, array|null $parameters = null)
146    {
147        return static::pdoExceptionHandler(function () use ($query, $parameters) {
148            $prepared = $this->fetchPreparedStatement($query);
149            $prepared->execute($parameters);
150            $row = $prepared->fetch(\PDO::FETCH_ASSOC);
151            $prepared->closeCursor();
152            return $row;
153        });
154    }
156    public function fetchValue(Query\Base|string $query, array|null $parameters = null)
157    {
158        return static::pdoExceptionHandler(function () use ($query, $parameters) {
159            $prepared = $this->fetchPreparedStatement($query);
160            $prepared->execute($parameters);
161            $value = $prepared->fetchColumn();
162            $prepared->closeCursor();
163            return $value;
164        });
165    }
167    public function fetchAll(Query\Base|string $query, array|null $parameters = null)
168    {
169        return static::pdoExceptionHandler(function () use ($query, $parameters) {
170            $prepared = $this->fetchPreparedStatement($query);
171            $prepared->execute($parameters);
172            $list = $prepared->fetchAll(\PDO::FETCH_ASSOC);
173            $prepared->closeCursor();
174            return $list;
175        });
176    }
178    public function fetchHandle(Query\Base|string $query, array|null $parameters = null)
179    {
180        return static::pdoExceptionHandler(function () use ($query, $parameters) {
181            $prepared = $this->fetchPreparedStatement($query);
182            $prepared->execute($parameters);
183            return $prepared;
184        });
185    }
187    public function fetchList(Query\Base $query, \BO\Zmsentities\Schema\Entity $entity, array|\ArrayAccess $resultList = [])
188    {
189        $statement = $this->fetchStatement($query);
190        while ($data = $statement->fetch(\PDO::FETCH_ASSOC)) {
191            $dataEntity = clone $entity;
192            $dataEntity->exchangeArray($query->postProcessJoins($data));
193            $dataEntity->setResolveLevel($query->getResolveLevel());
194            $resultList[] = $dataEntity;
195        }
196        $statement->closeCursor();
197        return $resultList;
198    }
206    public function writeItem(Query\Base $query)
207    {
208        return static::pdoExceptionHandler(function () use ($query) {
209            $this->getWriter(); //Switch to writer for write/delete
210            $statement = $this->fetchPreparedStatement($query);
211            $status = $statement->execute($query->getParameters());
212            $statement->closeCursor();
213            return $status;
214        });
215    }
221    public function deleteItem(Query\Base $query)
222    {
223        return $this->writeItem($query);
224    }
226    public function perform(string $sql, array|null $parameters = null)
227    {
228        return static::pdoExceptionHandler(function () use ($sql, $parameters) {
229            $this->getWriter(); //Switch to writer for perform
230            $prepared = $this->fetchPreparedStatement($sql);
231            $status = $prepared->execute($parameters);
232            $prepared->closeCursor();
233            return $status;
234        });
235    }
237    public function fetchAffected(string $sql, array|null $parameters = null)
238    {
239        return static::pdoExceptionHandler(function () use ($sql, $parameters) {
240            $this->getWriter(); //Switch to writer for perform
241            $prepared = $this->fetchPreparedStatement($sql);
242            $prepared->execute($parameters);
243            $count = $prepared->rowCount();
244            $prepared->closeCursor();
245            return $count;
246        });
247    }
256    public function readResolvedReferences(\BO\Zmsentities\Schema\Entity $entity, int $resolveReferences)
257    {
258        return $entity;
259    }
268    public function hashStringValue(string $value, ?callable $callable = null): string
269    {
270        if ($callable === null) {
271            $callable = 'sha1';
272        }
273
274        if (is_callable($callable)) {
275            $hash = $callable($value);
276
277            if (is_string($callable)) {
278                return $callable . ':' . $hash;
279            }
280            if ($callable instanceof \Closure) {
281                return 'closure:' . $hash;
282            }
283            // else
284            return 'custom:' . $hash;
285        }
286
287        throw new \InvalidArgumentException('hashStringValue() should be called with a callable as second parameter.');
288    }