Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
77.94% covered (warning)
77.94%
219 / 281
50.00% covered (danger)
50.00%
11 / 22
CRAP
0.00% covered (danger)
0.00%
0 / 1
Slot
77.94% covered (warning)
77.94%
219 / 281
50.00% covered (danger)
50.00%
11 / 22
162.61
0.00% covered (danger)
0.00%
0 / 1
 readByAppointment
100.00% covered (success)
100.00%
8 / 8
100.00% covered (success)
100.00%
1 / 1
5
 readByAvailability
100.00% covered (success)
100.00%
15 / 15
100.00% covered (success)
100.00%
1 / 1
3
 hasScopeRelevantChanges
86.67% covered (warning)
86.67%
13 / 15
0.00% covered (danger)
0.00%
0 / 1
5.06
 isAvailabilityOutdated
100.00% covered (success)
100.00%
42 / 42
100.00% covered (success)
100.00%
1 / 1
17
 writeByAvailability
82.50% covered (warning)
82.50%
33 / 40
0.00% covered (danger)
0.00%
0 / 1
13.91
 writeByScope
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
12
 writeSlotListForDate
91.30% covered (success)
91.30%
21 / 23
0.00% covered (danger)
0.00%
0 / 1
7.03
 writeAncestorIDs
100.00% covered (success)
100.00%
12 / 12
100.00% covered (success)
100.00%
1 / 1
3
 readLastChangedTime
83.33% covered (warning)
83.33%
5 / 6
0.00% covered (danger)
0.00%
0 / 1
2.02
 readLastChangedTimeByScope
0.00% covered (danger)
0.00%
0 / 9
0.00% covered (danger)
0.00%
0 / 1
6
 readLastChangedTimeByAvailability
88.89% covered (warning)
88.89%
8 / 9
0.00% covered (danger)
0.00%
0 / 1
2.01
 updateSlotProcessMapping
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
12
 deleteSlotProcessOnSlot
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 deleteSlotProcessOnProcess
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
12
 writeSlotProcessMappingFor
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
1
 deleteSlotProcessMappingFor
100.00% covered (success)
100.00%
4 / 4
100.00% covered (success)
100.00%
1 / 1
1
 writeCanceledByTimeAndScope
100.00% covered (success)
100.00%
11 / 11
100.00% covered (success)
100.00%
1 / 1
2
 writeCanceledByTime
100.00% covered (success)
100.00%
9 / 9
100.00% covered (success)
100.00%
1 / 1
2
 deleteSlotsOlderThan
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
2
 readRowsByScopeAndDate
100.00% covered (success)
100.00%
7 / 7
100.00% covered (success)
100.00%
1 / 1
1
 writeOptimizedSlotTables
76.92% covered (warning)
76.92%
10 / 13
0.00% covered (danger)
0.00%
0 / 1
4.20
 getLastGeneratedSlotDate
100.00% covered (success)
100.00%
10 / 10
100.00% covered (success)
100.00%
1 / 1
2
1<?php
2
3namespace BO\Zmsdb;
4
5use BO\Zmsdldb\Helper\DateTime;
6use BO\Zmsentities\Slot as Entity;
7use BO\Zmsentities\Collection\SlotList as Collection;
8use BO\Zmsentities\Availability as AvailabilityEntity;
9use BO\Zmsentities\Scope as ScopeEntity;
10
11/**
12 * @SuppressWarnings(Public)
13 * @SuppressWarnings(Complexity)
14 * @SuppressWarnings(Coupling)
15 */
16class Slot extends Base
17{
18    /**
19     * maximum number of slots per appointment
20     */
21    const MAX_SLOTS = 25;
22
23    const MAX_DAYS_OF_SLOT_CALCULATION = 180;
24
25    /**
26     * @return \BO\Zmsentities\Collection\SlotList
27     *
28     */
29    public function readByAppointment(
30        \BO\Zmsentities\Appointment $appointment,
31        $overwriteSlotsCount = null,
32        $extendSlotList = false,
33        $lockSlots = false
34    ) {
35        $appointment = clone $appointment;
36        $availability = (new Availability())->readByAppointment($appointment);
37        // Check if availability allows multiple slots, but allow overwrite
38        if (!$availability->multipleSlotsAllowed || $overwriteSlotsCount >= 1) {
39            $appointment->slotCount = ($overwriteSlotsCount >= 1) ? $overwriteSlotsCount : 1;
40        }
41        $slotList = $availability->getSlotList()->withSlotsForAppointment($appointment, $extendSlotList);
42        foreach ($slotList as $slot) {
43            $this->readByAvailability($slot, $availability, $appointment->toDateTime(), $lockSlots);
44        }
45        return $slotList;
46    }
47
48    public function readByAvailability(
49        \BO\Zmsentities\Slot $slot,
50        AvailabilityEntity $availability,
51        \DateTimeInterface $date,
52        $getLock = false
53    ) {
54        $data = array();
55        $data['scopeID'] = $availability->scope->id;
56        $data['availabilityID'] = $availability->id;
57        $data['year'] = $date->format('Y');
58        $data['month'] = $date->format('m');
59        $data['day'] = $date->format('d');
60        $data['time'] = $slot->getTimeString();
61        $sql = Query\Slot::QUERY_SELECT_SLOT;
62        if ($getLock) {
63            $sql .= ' FOR UPDATE';
64        }
65        $slotID = $this->fetchRow(
66            $sql,
67            $data
68        );
69        return $slotID ? $slotID['slotID'] : false ;
70    }
71
72    public function hasScopeRelevantChanges(
73        \BO\Zmsentities\Scope $scope,
74        \DateTimeInterface $slotLastChange = null
75    ) {
76        $startInDaysDefault = (new Preferences())
77            ->readProperty('scope', $scope->id, 'appointment', 'startInDaysDefault');
78        $endInDaysDefault = (new Preferences())
79            ->readProperty('scope', $scope->id, 'appointment', 'endInDaysDefault');
80        if (
81            $scope->preferences['appointment']['startInDaysDefault'] != $startInDaysDefault
82            || $scope->preferences['appointment']['endInDaysDefault'] != $endInDaysDefault
83        ) {
84            (new Scope())->replacePreferences($scope); //TODO remove after ZMS1 is deactivated
85            return true;
86        }
87        $startInDaysChange = (new Preferences())
88            ->readChangeDateTime('scope', $scope->id, 'appointment', 'startInDaysDefault');
89        $endInDaysChange = (new Preferences())
90            ->readChangeDateTime('scope', $scope->id, 'appointment', 'endInDaysDefault');
91        if (
92            $startInDaysChange->getTimestamp() > $slotLastChange->getTimestamp()
93            || $endInDaysChange->getTimestamp() > $slotLastChange->getTimestamp()
94        ) {
95            return true;
96        }
97    }
98
99    public function isAvailabilityOutdated(
100        \BO\Zmsentities\Availability $availability,
101        \DateTimeInterface $now,
102        \DateTimeInterface $slotLastChange = null
103    ) {
104        $proposedChange = new Helper\AvailabilitySnapShot($availability, $now);
105        $formerChange = new Helper\AvailabilitySnapShot($availability, $slotLastChange);
106
107        if ($formerChange->hasOutdatedAvailability()) {
108            $availability['processingNote'][] = 'outdated: availability change';
109            return true;
110        }
111        if (
112            $formerChange->hasOutdatedScope()
113            && $this->hasScopeRelevantChanges($availability->scope, $slotLastChange)
114        ) {
115            $availability['processingNote'][] = 'outdated: scope change';
116            return true;
117        }
118        if ($formerChange->hasOutdatedDayoff()) {
119            $availability['processingNote'][] = 'outdated: dayoff change';
120            return true;
121        }
122        // Be aware, that last slot change and current time might differ serval days
123        //  if the rebuild fails in some way
124        if (
125            1
126            // First check if the bookable end date on current time was already calculated on last slot change
127            && !$formerChange->hasBookableDateTime($proposedChange->getLastBookableDateTime())
128            // Second check if between last slot change and current time could be a bookable slot
129            && (
130                (
131                    !$formerChange->isOpenedOnLastBookableDay()
132                    && $proposedChange->hasBookableDateTimeAfter($formerChange->getLastBookableDateTime())
133                )
134                // if calculation already happened the day before, check if lastChange time was before opening
135                || (
136                    $formerChange->isOpenedOnLastBookableDay()
137                    && (
138                        !$formerChange->isTimeOpenedOnLastBookableDay()
139                        || $proposedChange->hasBookableDateTimeAfter(
140                            $formerChange->getLastBookableDateTime()->modify('+1day 00:00:00')
141                        )
142                    )
143                )
144            )
145            // Check if daytime is after booking start time if bookable end of now is calculated
146            && (
147                !$proposedChange->isOpenedOnLastBookableDay()
148                || $proposedChange->isTimeOpenedOnLastBookableDay()
149            )
150        ) {
151            $availability['processingNote'][] = 'outdated: new slots required';
152            return true;
153        }
154        if (
155            $availability->getBookableStart($slotLastChange) != $availability->getBookableStart($now)
156            // First check, if bookable start from lastChange was not included in bookable time from now
157            && !$availability->hasDate($availability->getBookableStart($slotLastChange), $now)
158            // Second check, if availability had a bookable time on lastChange before bookable start from now
159            && $availability->hasDateBetween(
160                $availability->getBookableStart($slotLastChange),
161                $availability->getBookableStart($now),
162                $slotLastChange
163            )
164        ) {
165            $availability['processingNote'][] = 'outdated: slots invalidated by bookable start';
166            return true;
167        }
168        $availability['processingNote'][] = 'not outdated';
169        return false;
170    }
171
172    /**
173     * @return bool TRUE if there were changes on slots
174     */
175    public function writeByAvailability(
176        \BO\Zmsentities\Availability $availability,
177        \DateTimeInterface $now,
178        \DateTimeInterface $slotLastChange = null
179    ) {
180        $now = \BO\Zmsentities\Helper\DateTime::create($now);
181        $calculateSlotsUntilDate = \BO\Zmsentities\Helper\DateTime::create($now)->modify('+' . self::MAX_DAYS_OF_SLOT_CALCULATION . ' days');
182        if (!$slotLastChange) {
183            $slotLastChange = $this->readLastChangedTimeByAvailability($availability);
184        }
185        $lastGeneratedSlotDate = $this->getLastGeneratedSlotDate($availability);
186
187        $availability['processingNote'][] = 'lastchange=' . $slotLastChange->format('c');
188        if (!$this->isAvailabilityOutdated($availability, $now, $slotLastChange)) {
189            return false;
190        }
191        $startDate = $availability->getBookableStart($now)->modify('00:00:00');
192        $stopDate = $availability->getBookableEnd($now);
193        $generateNew = $availability->isNewerThan($slotLastChange);
194        (new Availability())->readLock($availability->id);
195        $cancelledSlots = $this->fetchAffected(Query\Slot::QUERY_CANCEL_AVAILABILITY_BEFORE_BOOKABLE, [
196            'availabilityID' => $availability->id,
197            'providedDate' => $startDate->format('Y-m-d')
198        ]);
199        if ($generateNew) {
200            $cancelledSlots = $this->fetchAffected(Query\Slot::QUERY_CANCEL_AVAILABILITY, [
201                'availabilityID' => $availability->id,
202            ]);
203
204            if (!$availability->withData(['bookable' => ['startInDays' => 0]])->hasBookableDates($now)) {
205                $availability['processingNote'][] = "cancelled $cancelledSlots slots: availability not bookable ";
206                return ($cancelledSlots > 0) ? true : false;
207            }
208            $availability['processingNote'][] = "cancelled $cancelledSlots slots";
209        }
210
211        $slotlist = $availability->getSlotList();
212        $slotlistIntern = $slotlist->withValueFor('callcenter', 0)->withValueFor('public', 0);
213        $time = $now->modify('00:00:00');
214        if (!$generateNew) {
215            $time = $lastGeneratedSlotDate->modify('+1 day')->modify('00:00:00');
216        }
217        $status = false;
218        do {
219            if ($availability->withData(['bookable' => ['startInDays' => 0]])->hasDate($time, $now)) {
220                $writeStatus = $this->writeSlotListForDate(
221                    $time,
222                    ($time->getTimestamp() < $startDate->getTimestamp()) ? $slotlistIntern : $slotlist,
223                    $availability
224                );
225                $status = $writeStatus ? $writeStatus : $status;
226            }
227            $time = $time->modify('+1day');
228        } while ($time->getTimestamp() <= $stopDate->getTimestamp() && $time->getTimestamp() < $calculateSlotsUntilDate->getTimestamp());
229
230        return $status || (isset($cancelledSlots) && $cancelledSlots > 0);
231    }
232
233    public function writeByScope(\BO\Zmsentities\Scope $scope, \DateTimeInterface $now)
234    {
235        $slotLastChange = $this->readLastChangedTimeByScope($scope);
236        $availabilityList = (new \BO\Zmsdb\Availability())
237            ->readAvailabilityListByScope($scope, 0, $slotLastChange->modify('-1 day'))
238            ;
239        $updatedList = new \BO\Zmsentities\Collection\AvailabilityList();
240        foreach ($availabilityList as $availability) {
241            $availability->scope = clone $scope; //dayoff is required
242            if ($this->writeByAvailability($availability, $now)) {
243                $updatedList->addEntity($availability);
244            }
245        }
246        return $updatedList;
247    }
248
249    protected function writeSlotListForDate(
250        \DateTimeInterface $time,
251        Collection $slotlist,
252        AvailabilityEntity $availability
253    ) {
254        $ancestors = [];
255        $hasAddedSlots = false;
256
257        foreach ($slotlist as $slot) {
258            $slot = clone $slot;
259            $slotID = $this->readByAvailability($slot, $availability, $time);
260            if ($slotID) {
261                $query = new Query\Slot(Query\Base::UPDATE);
262                $query->addConditionSlotId($slotID);
263            } else {
264                $query = new Query\Slot(Query\Base::INSERT);
265                $hasAddedSlots = true;
266            }
267            $slot->status = 'free';
268            $values = $query->reverseEntityMapping($slot, $availability, $time);
269            $values['createTimestamp'] = time();
270            $query->addValues($values);
271            $writeStatus = $this->writeItem($query);
272            if ($writeStatus && !$slotID) {
273                $slotID = $this->getWriter()->lastInsertId();
274            }
275            $ancestors[] = $slotID;
276            // TODO: Check if slot changed before writing ancestor IDs
277            $this->writeAncestorIDs($slotID, $ancestors);
278            $status = $writeStatus ? $writeStatus : $status;
279        }
280        if ($hasAddedSlots) {
281            $availability['processingNote'][] = 'Added ' . $time->format('Y-m-d');
282        }
283        return $status;
284    }
285
286    protected function writeAncestorIDs($slotID, array $ancestors)
287    {
288        $this->perform(Query\Slot::QUERY_DELETE_ANCESTOR, [
289            'slotID' => $slotID,
290        ]);
291        $ancestorLevel = count($ancestors);
292        foreach ($ancestors as $ancestorID) {
293            if ($ancestorLevel <= self::MAX_SLOTS) {
294                $this->perform(Query\Slot::QUERY_INSERT_ANCESTOR, [
295                    'slotID' => $slotID,
296                    'ancestorID' => $ancestorID,
297                    'ancestorLevel' => $ancestorLevel,
298                ]);
299            }
300            $ancestorLevel--;
301        }
302    }
303
304    public function readLastChangedTime()
305    {
306        $last = $this->fetchRow(
307            Query\Slot::QUERY_LAST_CHANGED
308        );
309        if (!$last['dateString']) {
310            $last['dateString'] = '1970-01-01 12:00';
311        }
312        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsdb\Connection\Select::$connectionTimezone);
313    }
314
315    public function readLastChangedTimeByScope(ScopeEntity $scope)
316    {
317        $last = $this->fetchRow(
318            Query\Slot::QUERY_LAST_CHANGED_SCOPE,
319            [
320                'scopeID' => $scope->id,
321            ]
322        );
323        if (!$last['dateString']) {
324            $last['dateString'] = '1970-01-01 12:00';
325        }
326        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsdb\Connection\Select::$connectionTimezone);
327    }
328
329    public function readLastChangedTimeByAvailability(AvailabilityEntity $availabiliy)
330    {
331        $last = $this->fetchRow(
332            Query\Slot::QUERY_LAST_CHANGED_AVAILABILITY,
333            [
334                'availabilityID' => $availabiliy->id,
335            ]
336        );
337        if (!$last['dateString']) {
338            $last['dateString'] = '1970-01-01 12:00';
339        }
340        return new \DateTimeImmutable($last['dateString'] . \BO\Zmsdb\Connection\Select::$connectionTimezone);
341    }
342
343    public function updateSlotProcessMapping($scopeID = null)
344    {
345        if ($scopeID) {
346            $processIdList = $this->fetchAll(
347                Query\Slot::QUERY_SELECT_MISSING_PROCESS
348                . Query\Slot::QUERY_SELECT_MISSING_PROCESS_BY_SCOPE,
349                ['scopeID' => $scopeID]
350            );
351        } else {
352            $processIdList = $this->fetchAll(Query\Slot::QUERY_SELECT_MISSING_PROCESS, []);
353        }
354        // Client side INSERT ... SELECT ... to reduce table locking
355        foreach ($processIdList as $processId) {
356            $this->perform(Query\Slot::QUERY_INSERT_SLOT_PROCESS, array_values($processId));
357        }
358        return count($processIdList);
359    }
360
361    public function deleteSlotProcessOnSlot($scopeID = null)
362    {
363        if ($scopeID) {
364            $this->perform(
365                Query\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED
366                . Query\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED_BY_SCOPE,
367                ['scopeID' => $scopeID]
368            );
369        } else {
370            $this->perform(Query\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED, []);
371        }
372    }
373
374    public function deleteSlotProcessOnProcess($scopeID = null)
375    {
376        if ($scopeID) {
377            $processIdList = $this->fetchAll(
378                Query\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS
379                . Query\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS_BY_SCOPE,
380                ['scopeID' => $scopeID]
381            );
382        } else {
383            $processIdList = $this->fetchAll(Query\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS);
384        }
385        // Client side INSERT ... SELECT ... to reduce table locking
386        foreach ($processIdList as $processId) {
387            $this->perform(Query\Slot::QUERY_DELETE_SLOT_PROCESS_ID, $processId);
388        }
389        return count($processIdList);
390    }
391
392    public function writeSlotProcessMappingFor($processId)
393    {
394        $this->perform(Query\Slot::QUERY_INSERT_SLOT_PROCESS_ID, [
395            'processId' => $processId,
396        ]);
397        return $this;
398    }
399
400    public function deleteSlotProcessMappingFor($processId)
401    {
402        $this->perform(Query\Slot::QUERY_DELETE_SLOT_PROCESS_ID, [
403            'processId' => $processId,
404        ]);
405        return $this;
406    }
407
408    public function writeCanceledByTimeAndScope(\DateTimeInterface $dateTime, \BO\Zmsentities\Scope $scope)
409    {
410        $status = $this->perform(Query\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY_BY_SCOPE, [
411            'dateString' => $dateTime->format('Y-m-d'),
412            'scopeID' => $scope->id,
413        ]);
414
415        return $this->perform(Query\Slot::QUERY_CANCEL_SLOT_OLD_BY_SCOPE, [
416            'year' => $dateTime->format('Y'),
417            'month' => $dateTime->format('m'),
418            'day' => $dateTime->format('d'),
419            'time' => $dateTime->format('H:i:s'),
420            'scopeID' => $scope->id,
421        ]) && $status;
422    }
423
424    public function writeCanceledByTime(\DateTimeInterface $dateTime)
425    {
426        $status = $this->perform(Query\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY, [
427            'dateString' => $dateTime->format('Y-m-d'),
428        ]);
429        return $this->perform(Query\Slot::QUERY_CANCEL_SLOT_OLD, [
430            'year' => $dateTime->format('Y'),
431            'month' => $dateTime->format('m'),
432            'day' => $dateTime->format('d'),
433            'time' => $dateTime->format('H:i:s'),
434        ]) && $status;
435    }
436
437    public function deleteSlotsOlderThan(\DateTimeInterface $dateTime)
438    {
439        $status = $this->perform(Query\Slot::QUERY_DELETE_SLOT_OLD, [
440            'year' => $dateTime->format('Y'),
441            'month' => $dateTime->format('m'),
442            'day' => $dateTime->format('d'),
443        ]);
444        $status = ($status && $this->perform(Query\Slot::QUERY_DELETE_SLOT_HIERA));
445        return $status;
446    }
447
448    /**
449     * This function is for debugging
450     */
451    public function readRowsByScopeAndDate(
452        \BO\Zmsentities\Scope $scope,
453        \DateTimeInterface $dateTime
454    ) {
455        $list = $this->fetchAll(Query\Slot::QUERY_SELECT_BY_SCOPE_AND_DAY, [
456            'year' => $dateTime->format('Y'),
457            'month' => $dateTime->format('m'),
458            'day' => $dateTime->format('d'),
459            'scopeID' => $scope->id,
460        ]);
461        return $list;
462    }
463
464    public function writeOptimizedSlotTables()
465    {
466        $queries = [
467            Query\Slot::QUERY_OPTIMIZE_SLOT,
468            Query\Slot::QUERY_OPTIMIZE_SLOT_HIERA,
469            Query\Slot::QUERY_OPTIMIZE_SLOT_PROCESS,
470            Query\Slot::QUERY_OPTIMIZE_PROCESS,
471        ];
472
473        $status = true;
474        foreach ($queries as $query) {
475            try {
476                $status = $status && $this->perform($query);
477            } catch (\PDOException $e) {
478                \App::$log->error("Failed to optimize table with query: $query. Error: " . $e->getMessage(), []);
479
480                return false;
481            }
482        }
483
484        return $status;
485    }
486
487    private function getLastGeneratedSlotDate(AvailabilityEntity $availability)
488    {
489        $date = '1970-01-01 12:00';
490        $last = $this->fetchRow(
491            Query\Slot::QUERY_LAST_IN_AVAILABILITY,
492            [
493                'availabilityID' => $availability->id,
494            ]
495        );
496
497        if (isset($last['dateString'])) {
498            $date = $last['dateString'];
499        }
500
501        return new \DateTimeImmutable($date . \BO\Zmsdb\Connection\Select::$connectionTimezone);
502    }
503}