Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
43.33%
52 / 120
33.33%
5 / 15
 CRAP
0.00%
0 / 1
SlotList
43.33%
52 / 120
33.33%
5 / 15
 286.11
0.00%
0 / 1
 __construct
100.00%
6 / 6
100.00%
1 / 1
 3
 getQuery
0.00%
0 / 1
0.00%
0 / 1
 2
 getParametersMonth
0.00%
0 / 14
0.00%
0 / 1
 2
 getParametersDay
0.00%
0 / 14
0.00%
0 / 1
 2
 setSlotData
90.91%
10 / 11
0.00%
0 / 1
 5.02
 addQueryData
100.00%
19 / 19
100.00%
1 / 1
 5
 getCalculatedSlot
100.00%
7 / 7
100.00%
1 / 1
 1
 addToCalendar
0.00%
0 / 11
0.00%
0 / 1
 20
 addFreeProcessesToCalendar
0.00%
0 / 5
0.00%
0 / 1
 30
 getFreeProcesses
0.00%
0 / 10
0.00%
0 / 1
 2
 createSlots
100.00%
9 / 9
100.00%
1 / 1
 3
 isSameAvailability
0.00%
0 / 1
0.00%
0 / 1
 2
 toReducedBySlots
0.00%
0 / 5
0.00%
0 / 1
 20
 postProcess
0.00%
0 / 6
0.00%
0 / 1
 2
 __toString
100.00%
1 / 1
100.00%
1 / 1
 1
1<?php
2
3namespace BO\Zmsbackend\Slot\Repository;
4
5use BO\Zmsentities\Helper\DateTime;
6use BO\Zmsentities\Slot;
7
8/**
9 *
10 * @SuppressWarnings(CouplingBetweenObjects)
11 * Calculate Slots for available booking times
12 */
13class SlotList extends \BO\Zmsbackend\Query\Base
14{
15    const string QUERY = 'SELECT
16
17            -- collect some important settings, especially from the scope, use the appointment key
18            CONCAT(b.Datum, " ", b.Uhrzeit) AS appointment__date,
19            s.StandortID AS appointment__scope__id,
20            s.mehrfachtermine AS appointment__scope__preferences__appointment__multipleSlotsEnabled,
21
22            -- results are used slots, collect some information to match calculated open slots
23            DAYOFMONTH(b.Datum) AS `day`,
24            MONTH(b.Datum) AS `month`,
25            YEAR(b.Datum) AS `year`,
26            b.Uhrzeit AS slottime,
27            b.Datum AS slotdate,
28
29            -- as grouped by slot, we can calculate available free appointments
30            GREATEST(0, o.appointment_workstation_count - o.internet_reduction - COUNT(b.Datum))
31                AS `freeAppointments__public`,
32            o.appointment_workstation_count - COUNT(b.Datum)
33                AS `freeAppointments__intern`,
34
35            -- calculate the incrementing slotnr for the availability
36            FLOOR(((TIME_TO_SEC(b.Uhrzeit) - TIME_TO_SEC(o.appointment_start_time)) / TIME_TO_SEC(o.time_slot))) AS `slotnr`,
37
38            -- collect settings for the availability to calculate missing slots
39            o.OeffnungszeitID AS availability__id,
40            o.multiple_slots_allowed AS availability__multipleSlotsAllowed,
41            o.every_x_weeks AS availability__repeat__afterWeeks,
42            o.every_other_week AS availability__repeat__weekOfMonth,
43            FLOOR(TIME_TO_SEC(o.time_slot) / 60) AS availability__slotTimeInMinutes,
44            o.start_date AS availability__startDate,
45            o.end_date AS availability__endDate,
46            o.appointment_start_time AS availability__startTime,
47            o.appointment_end_time AS availability__endTime,
48
49            -- weekday is saved bitwise
50            o.weekday & 2 AS availability__weekday__monday,
51            o.weekday & 4 AS availability__weekday__tuesday,
52            o.weekday & 8 AS availability__weekday__wednesday,
53            o.weekday & 16 AS availability__weekday__thursday,
54            o.weekday & 32 AS availability__weekday__friday,
55            o.weekday & 64 AS availability__weekday__saturday,
56            o.weekday & 1 AS availability__weekday__sunday,
57
58            -- calculate available slots, do not use reduction values
59            o.appointment_workstation_count - o.internet_reduction AS availability__workstationCount__public,
60            o.appointment_workstation_count AS availability__workstationCount__intern,
61
62            -- availability overwrites scope settings if greater zero
63            IF(o.open_from_days, o.open_from_days, s.Termine_ab) AS availability__bookable__startInDays,
64            IF(o.open_until_days, o.open_until_days, s.Termine_bis) AS availability__bookable__endInDays
65        FROM
66            standort s
67            LEFT JOIN oeffnungszeit o ON o.scope_id = s.StandortID
68            LEFT JOIN buerger b ON
69                (
70                    b.StandortID = o.scope_id
71
72                    -- match weekday
73                    AND o.weekday & POW(2, DAYOFWEEK(b.Datum) - 1)
74
75                    -- match week
76                    AND (
77                        (
78                            o.every_x_weeks
79                            -- The following line would be correct by logic, but does not work :-/
80                                AND FLOOR(
81                                    (FLOOR(UNIX_TIMESTAMP(b.Datum))
82                                    - FLOOR(UNIX_TIMESTAMP(o.start_date)))
83                                    / 86400
84                                    / 7
85                                ) % o.every_x_weeks = 0
86                        )
87                        OR (
88                            o.every_other_week
89                            AND (
90                                CEIL(DAYOFMONTH(b.Datum) / 7) = o.every_other_week
91                                OR (
92                                    o.every_other_week = 5
93                                    AND CEIL(LAST_DAY(b.Datum) / 7) = CEIL(DAYOFMONTH(b.Datum) / 7)
94                                )
95                            )
96                        )
97                        OR (o.every_x_weeks = 0 AND o.every_other_week = 0)
98                    )
99
100                    -- ignore slots out of date range
101                    AND b.Datum BETWEEN :start_process AND :end_process
102
103                    -- match time and date
104                    AND b.Uhrzeit >= o.appointment_start_time
105                    AND b.Uhrzeit < o.appointment_end_time
106                    AND b.Datum >= o.start_date
107                    AND b.Datum <= o.end_date
108
109                    -- match day off
110                    AND (
111                        b.Datum NOT IN (
112                            SELECT Datum FROM feiertage f WHERE f.BehoerdenID = s.BehoerdenID OR f.BehoerdenID = 0
113                        )
114                        -- ignore day off if availabilty is valid for two or less days
115                        OR UNIX_TIMESTAMP(o.end_date) - UNIX_TIMESTAMP(o.start_date) < 172800
116                    )
117                )
118        WHERE
119            s.StandortID = :scope_id
120            AND o.OeffnungszeitID IS NOT NULL
121
122            -- ignore availability out of date range
123            AND o.end_date >= :start_availability
124            AND o.start_date <= :end_availability
125
126            -- ignore availability on midnight
127            AND o.appointment_start_time != "00:00:00"
128            AND o.appointment_end_time != "00:00:00"
129
130            -- ignore availability without appointment slots
131            AND o.appointment_workstation_count != 0
132        GROUP BY o.OeffnungszeitID, b.Datum, `slotnr`
133        HAVING
134            -- reduce results cause processing them costs time even with query cache
135            (
136                appointment__date BETWEEN
137                    DATE_ADD(:nowStart, INTERVAL availability__bookable__startInDays DAY)
138                    -- appointment__date includes midnight time, so take the following day to include the last day
139                    AND DATE_ADD(:nowEnd, INTERVAL availability__bookable__endInDays + 1 DAY)
140                AND
141                (
142                    slotdate !=  DATE_ADD(:nowCompare, INTERVAL availability__bookable__endInDays DAY)
143                    OR availability__startTime < :nowTime
144                )
145            )
146            OR appointment__date IS NULL
147
148        -- ordering is important for processing later on (slot reduction)
149        ORDER BY o.OeffnungszeitID, b.Datum, `slotnr`
150        ';
151
152    /**
153     *
154     * @var array $slotData Single result row from the query
155     */
156    protected $slotData = null;
157
158    /**
159     *
160     * @var \BO\Zmsentities\Scope|null $scope
161     */
162    protected $scope = null;
163
164    /**
165     *
166     * @var \BO\Zmsentities\Availability|null $availability
167     */
168    protected $availability = null;
169
170    /**
171     *
172     * @var Array $slots
173     */
174    protected $slots = array();
175
176    public function __construct(
177        array $slotData = ['availability__id' => null],
178        \DateTimeImmutable $start = null,
179        \DateTimeImmutable $stop = null,
180        \DateTimeInterface $now = null,
181        \BO\Zmsentities\Availability $availability = null,
182        \BO\Zmsentities\Scope $scope = null
183    ) {
184        $this->availability = $availability;
185        $this->scope = $scope;
186        $this->setSlotData($slotData);
187        if ($this->availability && isset($this->availability['id'])) {
188            $this->createSlots($start, $stop, $now);
189            $this->addQueryData($slotData);
190        }
191    }
192
193    /**
194     * @psalm-api
195     */
196    public static function getQuery(): string
197    {
198        return self::QUERY;
199    }
200
201    /**
202     * @psalm-api
203     *
204     * @return (mixed|string)[]
205     *
206     */
207    public static function getParametersMonth($scopeId, \DateTimeInterface $monthDateTime, \DateTimeInterface $now): array
208    {
209        $now = DateTime::create($now);
210        $monthDateTime = DateTime::create($monthDateTime);
211        $parameters = [
212            'scope_id' => $scopeId,
213            'start_process' => $monthDateTime->format('Y-m-1'),
214            'end_process' => $monthDateTime->format('Y-m-t'),
215            'start_availability' => $monthDateTime->format('Y-m-1'),
216            'end_availability' => $monthDateTime->format('Y-m-t'),
217            'nowStart' => $now->format('Y-m-d'),
218            'nowEnd' => $now->format('Y-m-d'),
219            'nowCompare' => $now->format('Y-m-d'),
220            'nowTime' => $now->format('H:i:s'),
221        ];
222        return $parameters;
223    }
224
225    /**
226     * @psalm-api
227     *
228     * @return (mixed|string)[]
229     *
230     */
231    public static function getParametersDay($scopeId, \DateTimeInterface $dateTime, \DateTimeInterface $now): array
232    {
233        $now = DateTime::create($now);
234        $dateTime = DateTime::create($dateTime);
235        //\App::$log->error("FreeProcess", [$dateTime->format('c')]);
236        $parameters = [
237            'scope_id' => $scopeId,
238            'start_process' => $dateTime->format('Y-m-d'),
239            'end_process' => $dateTime->format('Y-m-d'),
240            'start_availability' => $dateTime->format('Y-m-d'),
241            'end_availability' => $dateTime->format('Y-m-d'),
242            'nowStart' => $now->format('Y-m-d'),
243            'nowEnd' => $now->format('Y-m-d'),
244            'nowCompare' => $now->format('Y-m-d'),
245            'nowTime' => $now->format('H:i:s'),
246        ];
247        return $parameters;
248    }
249
250    /**
251     * To avoid a db query for availability,
252     * we use the scope data to add missing values
253     * and try to use availability data in query result
254     */
255    public function setSlotData(array $slotData): static
256    {
257        $this->slotData = $slotData;
258        if (null === $this->availability) {
259            $availability = [ ];
260            foreach ($slotData as $key => $value) {
261                if (0 === strpos($key, 'availability__')) {
262                    $newkey = str_replace('availability__', '', $key);
263                    $availability[$newkey] = $value;
264                }
265            }
266            $this->availability = new \BO\Zmsentities\Availability($availability);
267        }
268        if (null !== $this->scope) {
269            $this->availability['scope'] = $this->scope;
270        }
271        return $this;
272    }
273
274    /**
275     * add data from a mysql result set
276     *
277     * @see self::QUERY
278     */
279    public function addQueryData(array $slotData): static
280    {
281        if (isset($slotData['slotnr'])) {
282            $slotnumber = $slotData['slotnr'];
283            $slotdate = $slotData['slotdate'];
284            if (!isset($this->slots[$slotdate])) {
285                $slotDebug = "$slotdate #$slotnumber @" . $slotData['slottime'] . " on " . $this->availability;
286                throw new \BO\Zmsbackend\Slot\Exception\SlotDataWithoutPreGeneratedSlot(
287                    "Found database entry without a generated date for $slotDebug"
288                );
289            }
290            $slotList = $this->slots[$slotdate];
291            $slot = $slotList->getSlot($slotnumber);
292            if (null === $slot) {
293                $slotDebug = "$slotdate #$slotnumber @" . $slotData['slottime'] . " on " . $this->availability;
294                throw new \BO\Zmsbackend\Slot\Exception\SlotDataWithoutPreGeneratedSlot(
295                    "Found database entry without a pre-generated slot $slotDebug"
296                );
297            }
298            //if ($slot->type !== \BO\Zmsbackend\Slot\Service\Slot::FREE) {
299                // We do not throw an exception, cause availability slotTime might have changed
300            //}
301            $slotList[$slotnumber] = $this->getCalculatedSlot($slot, $slotData);
302        } elseif (isset($slotData['availability__id'])) {
303            // Only availability data for available slots, do nothing
304        } else {
305            throw new \BO\Zmsbackend\Slot\Exception\SlotDataEmpty("Found empty slot: " . var_export($slotData, true));
306        }
307        return $this;
308    }
309
310    protected function getCalculatedSlot(Slot $slot, array $slotData): Slot
311    {
312        $slot->public += $slotData['freeAppointments__public'] -
313            $slotData['availability__workstationCount__public'];
314        $slot->intern += $slotData['freeAppointments__intern'] -
315            $slotData['availability__workstationCount__intern'];
316        $slot->time = (new DateTime($slotData['slottime']))->format('H:i');
317        $slot->type = Slot::TIMESLICE;
318        return $slot;
319    }
320
321    /**
322     * @psalm-api
323     */
324    public function addToCalendar(
325        \BO\Zmsentities\Calendar $calendar,
326        \DateTimeInterface $now,
327        $freeProcessesDate,
328        $slotType = 'public',
329        $slotsRequired = 1
330    ): \BO\Zmsentities\Calendar {
331        $nowDate = $now->format('Y-m-d');
332        foreach ($this->slots as $date => $slotList) {
333            if ($nowDate == $date) {
334                $slotList = ('intern' != $slotType) ? $slotList->withTimeGreaterThan($now, $slotType) : $slotList;
335                $this->slots[$date] = $slotList;
336            }
337            $this->addFreeProcessesToCalendar($calendar, $freeProcessesDate, $date, $slotType, $slotsRequired);
338            $datetime = new \DateTimeImmutable($date);
339            $day = $calendar->getDayByDateTime($datetime);
340            $day['freeAppointments'] = $slotList->getSummerizedSlot($day['freeAppointments']);
341            $day->getWithStatus($slotType, $now);
342        }
343        return $calendar;
344    }
345
346    protected function addFreeProcessesToCalendar(
347        \BO\Zmsentities\Calendar $calendar,
348        $freeProcessesDate,
349        $date,
350        $slotType = 'public',
351        $slotsRequired = 1
352    ): void {
353        if (null !== $freeProcessesDate && $date == $freeProcessesDate->format('Y-m-d')) {
354            $freeProcesses = $this->getFreeProcesses($calendar, $freeProcessesDate, $slotType, $slotsRequired);
355            foreach ($freeProcesses as $process) {
356                if ($process instanceof \BO\Zmsentities\Process) {
357                    $calendar['freeProcesses']->addEntity($process);
358                }
359            }
360        }
361    }
362
363    /**
364     * TODO Unterscheidung nach intern/public sollte erst nach der API erfolgen!
365     */
366    public function getFreeProcesses(
367        \BO\Zmsentities\Calendar $calendar,
368        \DateTimeImmutable $freeProcessesDate = null,
369        $slotType = 'public',
370        $slotsRequired = 1
371    ) {
372        $selectedDate = $freeProcessesDate->format('Y-m-d');
373        $slotList = $this->slots[$selectedDate];
374        return $slotList->getFreeProcesses(
375            $selectedDate,
376            $this->scope,
377            $this->availability,
378            $slotType,
379            $calendar['requests'],
380            $slotsRequired
381        );
382    }
383
384    /**
385     * Create slots based on availability
386     */
387    public function createSlots(\DateTimeInterface $startDate, \DateTimeInterface $stopDate, \DateTimeInterface $now): void
388    {
389        $startDate = ($startDate < $now) ? $now->modify('00:00:00') : $startDate;
390        $stopDate = $stopDate->modify('00:00:00');
391        $time = DateTime::create($startDate);
392        $slotlist = $this->availability->getSlotList();
393        do {
394            $date = $time->format('Y-m-d');
395            if ($this->availability->hasDate($time, $now)) {
396                $this->slots[$date] = clone $slotlist;
397            }
398            $time = $time->modify('+1day');
399        } while ($time->getTimestamp() <= $stopDate->getTimestamp());
400    }
401
402    /**
403     * @psalm-api
404     */
405    public function isSameAvailability(array $slotData): bool
406    {
407        return $this->slotData['availability__id'] == $slotData['availability__id'];
408    }
409
410    /**
411     * Reduce available slots
412     * On given amount of required slots reduce the amount of available slots by comparing continous slots available
413     *
414     * @param Int $slotsRequired
415     * @return self
416     * @psalm-api
417     */
418    public function toReducedBySlots($slotsRequired)
419    {
420        if (count($this->slots) && $slotsRequired > 1) {
421            foreach ($this->slots as $date => $slotList) {
422                $reduced = $slotList->withReducedSlots($slotsRequired);
423                $this->slots[$date] = $reduced;
424            }
425        }
426        return $this;
427    }
428
429    #[\Override]
430    public function postProcess($data)
431    {
432        $data[$this->getPrefixed("appointment__date")] = strtotime($data[$this->getPrefixed("appointment__date")]);
433        $data[$this->getPrefixed("availability__startDate")] =
434            strtotime($data[$this->getPrefixed("availability__startDate")]);
435        $data[$this->getPrefixed("availability__endDate")] =
436            strtotime($data[$this->getPrefixed("availability__endDate")]);
437        return $data;
438    }
439
440    public function __toString()
441    {
442        return "Query_SlotList: {$this->availability} {$this->scope}";
443    }
444}