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\Zmsdb\Query;
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 Base
14{
15    const 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.Anzahlterminarbeitsplaetze - o.reduktionTermineImInternet - COUNT(b.Datum))
31                AS `freeAppointments__public`,
32            o.Anzahlterminarbeitsplaetze - 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.Terminanfangszeit)) / TIME_TO_SEC(o.Timeslot))) AS `slotnr`,
37
38            -- collect settings for the availability to calculate missing slots
39            o.OeffnungszeitID AS availability__id,
40            o.erlaubemehrfachslots AS availability__multipleSlotsAllowed,
41            o.allexWochen AS availability__repeat__afterWeeks,
42            o.jedexteWoche AS availability__repeat__weekOfMonth,
43            FLOOR(TIME_TO_SEC(o.Timeslot) / 60) AS availability__slotTimeInMinutes,
44            o.Startdatum AS availability__startDate,
45            o.Endedatum AS availability__endDate,
46            o.Terminanfangszeit     AS availability__startTime,
47            o.Terminendzeit     AS availability__endTime,
48
49            -- weekday is saved bitwise
50            o.Wochentag & 2 AS availability__weekday__monday,
51            o.Wochentag & 4 AS availability__weekday__tuesday,
52            o.Wochentag & 8 AS availability__weekday__wednesday,
53            o.Wochentag & 16 AS availability__weekday__thursday,
54            o.Wochentag & 32 AS availability__weekday__friday,
55            o.Wochentag & 64 AS availability__weekday__saturday,
56            o.Wochentag & 1 AS availability__weekday__sunday,
57
58            -- calculate available slots, do not use reduction values
59            o.Anzahlterminarbeitsplaetze - o.reduktionTermineImInternet AS availability__workstationCount__public,
60            o.Anzahlterminarbeitsplaetze AS availability__workstationCount__intern,
61
62            -- availability overwrites scope settings if greater zero
63            IF(o.Offen_ab, o.Offen_ab, s.Termine_ab) AS availability__bookable__startInDays,
64            IF(o.Offen_bis, o.Offen_bis, s.Termine_bis) AS availability__bookable__endInDays
65        FROM
66            standort s
67            LEFT JOIN oeffnungszeit o USING(StandortID)
68            LEFT JOIN buerger b ON
69                (
70                    b.StandortID = o.StandortID
71
72                    -- match weekday
73                    AND o.Wochentag & POW(2, DAYOFWEEK(b.Datum) - 1)
74
75                    -- match week
76                    AND (
77                        (
78                            o.allexWochen
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.Startdatum)))
83                                    / 86400
84                                    / 7
85                                ) % o.allexWochen = 0
86                        )
87                        OR (
88                            o.jedexteWoche
89                            AND (
90                                CEIL(DAYOFMONTH(b.Datum) / 7) = o.jedexteWoche
91                                OR (
92                                    o.jedexteWoche = 5
93                                    AND CEIL(LAST_DAY(b.Datum) / 7) = CEIL(DAYOFMONTH(b.Datum) / 7)
94                                )
95                            )
96                        )
97                        OR (o.allexWochen = 0 AND o.jedexteWoche = 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.Terminanfangszeit
105                    AND b.Uhrzeit < o.Terminendzeit
106                    AND b.Datum >= o.Startdatum
107                    AND b.Datum <= o.Endedatum
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.Endedatum) - UNIX_TIMESTAMP(o.Startdatum) < 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.Endedatum >= :start_availability
124            AND o.Startdatum <= :end_availability
125
126            -- ignore availability on midnight
127            AND o.Terminanfangszeit != "00:00:00"
128            AND o.Terminendzeit != "00:00:00"
129
130            -- ignore availability without appointment slots
131            AND o.Anzahlterminarbeitsplaetze != 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 $scope
161     */
162    protected $scope = null;
163
164    /**
165     *
166     * @var \BO\Zmsentities\Availability $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    public static function getQuery()
194    {
195        return self::QUERY;
196    }
197
198    public static function getParametersMonth($scopeId, \DateTimeInterface $monthDateTime, \DateTimeInterface $now)
199    {
200        $now = DateTime::create($now);
201        $monthDateTime = DateTime::create($monthDateTime);
202        $parameters = [
203            'scope_id' => $scopeId,
204            'start_process' => $monthDateTime->format('Y-m-1'),
205            'end_process' => $monthDateTime->format('Y-m-t'),
206            'start_availability' => $monthDateTime->format('Y-m-1'),
207            'end_availability' => $monthDateTime->format('Y-m-t'),
208            'nowStart' => $now->format('Y-m-d'),
209            'nowEnd' => $now->format('Y-m-d'),
210            'nowCompare' => $now->format('Y-m-d'),
211            'nowTime' => $now->format('H:i:s'),
212        ];
213        return $parameters;
214    }
215
216    public static function getParametersDay($scopeId, \DateTimeInterface $dateTime, \DateTimeInterface $now)
217    {
218        $now = DateTime::create($now);
219        $dateTime = DateTime::create($dateTime);
220        //\App::$log->error("FreeProcess", [$dateTime->format('c')]);
221        $parameters = [
222            'scope_id' => $scopeId,
223            'start_process' => $dateTime->format('Y-m-d'),
224            'end_process' => $dateTime->format('Y-m-d'),
225            'start_availability' => $dateTime->format('Y-m-d'),
226            'end_availability' => $dateTime->format('Y-m-d'),
227            'nowStart' => $now->format('Y-m-d'),
228            'nowEnd' => $now->format('Y-m-d'),
229            'nowCompare' => $now->format('Y-m-d'),
230            'nowTime' => $now->format('H:i:s'),
231        ];
232        return $parameters;
233    }
234
235    /**
236     * To avoid a db query for availability,
237     * we use the scope data to add missing values
238     * and try to use availability data in query result
239     */
240    public function setSlotData(array $slotData)
241    {
242        $this->slotData = $slotData;
243        if (null === $this->availability) {
244            $availability = [ ];
245            foreach ($slotData as $key => $value) {
246                if (0 === strpos($key, 'availability__')) {
247                    $newkey = str_replace('availability__', '', $key);
248                    $availability[$newkey] = $value;
249                }
250            }
251            $this->availability = new \BO\Zmsentities\Availability($availability);
252        }
253        if (null !== $this->scope) {
254            $this->availability['scope'] = $this->scope;
255        }
256        return $this;
257    }
258
259    /**
260     * add data from a mysql result set
261     * @see self::QUERY
262     *
263     */
264    public function addQueryData(array $slotData)
265    {
266        if (isset($slotData['slotnr'])) {
267            $slotnumber = $slotData['slotnr'];
268            $slotdate = $slotData['slotdate'];
269            if (!isset($this->slots[$slotdate])) {
270                $slotDebug = "$slotdate #$slotnumber @" . $slotData['slottime'] . " on " . $this->availability;
271                throw new \BO\Zmsdb\Exception\SlotDataWithoutPreGeneratedSlot(
272                    "Found database entry without a generated date for $slotDebug"
273                );
274            }
275            $slotList = $this->slots[$slotdate];
276            $slot = $slotList->getSlot($slotnumber);
277            if (null === $slot) {
278                $slotDebug = "$slotdate #$slotnumber @" . $slotData['slottime'] . " on " . $this->availability;
279                // error_log("Debugdata: Found database entry without a pre-generated slot $slotDebug");
280                throw new \BO\Zmsdb\Exception\SlotDataWithoutPreGeneratedSlot(
281                    "Found database entry without a pre-generated slot $slotDebug"
282                );
283            }
284            //if ($slot->type !== Slot::FREE) {
285                // We do not throw an exception, cause availability slotTime might have changed
286            //}
287            $slotList[$slotnumber] = $this->getCalculatedSlot($slot, $slotData);
288        } elseif (isset($slotData['availability__id'])) {
289            // Only availability data for available slots, do nothing
290        } else {
291            throw new \BO\Zmsdb\Exception\SlotDataEmpty("Found empty slot: " . var_export($slotData, true));
292        }
293        return $this;
294    }
295
296    protected function getCalculatedSlot(Slot $slot, $slotData)
297    {
298        $slot->public += $slotData['freeAppointments__public'] -
299            $slotData['availability__workstationCount__public'];
300        $slot->intern += $slotData['freeAppointments__intern'] -
301            $slotData['availability__workstationCount__intern'];
302        $slot->time = (new DateTime($slotData['slottime']))->format('H:i');
303        $slot->type = Slot::TIMESLICE;
304        return $slot;
305    }
306
307    public function addToCalendar(
308        \BO\Zmsentities\Calendar $calendar,
309        \DateTimeInterface $now,
310        $freeProcessesDate,
311        $slotType = 'public',
312        $slotsRequired = 1
313    ) {
314        $nowDate = $now->format('Y-m-d');
315        foreach ($this->slots as $date => $slotList) {
316            if ($nowDate == $date) {
317                $slotList = ('intern' != $slotType) ? $slotList->withTimeGreaterThan($now, $slotType) : $slotList;
318                $this->slots[$date] = $slotList;
319            }
320            $this->addFreeProcessesToCalendar($calendar, $freeProcessesDate, $date, $slotType, $slotsRequired);
321            $datetime = new \DateTimeImmutable($date);
322            $day = $calendar->getDayByDateTime($datetime);
323            $day['freeAppointments'] = $slotList->getSummerizedSlot($day['freeAppointments']);
324            $day->getWithStatus($slotType, $now);
325        }
326        return $calendar;
327    }
328
329    protected function addFreeProcessesToCalendar(
330        \BO\Zmsentities\Calendar $calendar,
331        $freeProcessesDate,
332        $date,
333        $slotType = 'public',
334        $slotsRequired = 1
335    ) {
336        if (null !== $freeProcessesDate && $date == $freeProcessesDate->format('Y-m-d')) {
337            $freeProcesses = $this->getFreeProcesses($calendar, $freeProcessesDate, $slotType, $slotsRequired);
338            foreach ($freeProcesses as $process) {
339                if ($process instanceof \BO\Zmsentities\Process) {
340                    $calendar['freeProcesses']->addEntity($process);
341                }
342            }
343        }
344    }
345
346    /**
347     * TODO Unterscheidung nach intern/public sollte erst nach der API erfolgen!
348     */
349    public function getFreeProcesses(
350        \BO\Zmsentities\Calendar $calendar,
351        \DateTimeImmutable $freeProcessesDate = null,
352        $slotType = 'public',
353        $slotsRequired = 1
354    ) {
355        $selectedDate = $freeProcessesDate->format('Y-m-d');
356        $slotList = $this->slots[$selectedDate];
357        return $slotList->getFreeProcesses(
358            $selectedDate,
359            $this->scope,
360            $this->availability,
361            $slotType,
362            $calendar['requests'],
363            $slotsRequired
364        );
365    }
366
367    /**
368     * Create slots based on availability
369     */
370    public function createSlots(\DateTimeInterface $startDate, \DateTimeInterface $stopDate, \DateTimeInterface $now)
371    {
372        $startDate = ($startDate < $now) ? $now->modify('00:00:00') : $startDate;
373        $stopDate = $stopDate->modify('00:00:00');
374        $time = DateTime::create($startDate);
375        $slotlist = $this->availability->getSlotList();
376        do {
377            $date = $time->format('Y-m-d');
378            if ($this->availability->hasDate($time, $now)) {
379                $this->slots[$date] = clone $slotlist;
380            }
381            $time = $time->modify('+1day');
382        } while ($time->getTimestamp() <= $stopDate->getTimestamp());
383    }
384
385    public function isSameAvailability(array $slotData)
386    {
387        return $this->slotData['availability__id'] == $slotData['availability__id'];
388    }
389
390    /**
391     * Reduce available slots
392     * On given amount of required slots reduce the amount of available slots by comparing continous slots available
393     *
394     * @param Int $slotsRequired
395     * @return self
396     */
397    public function toReducedBySlots($slotsRequired)
398    {
399        if (count($this->slots) && $slotsRequired > 1) {
400            foreach ($this->slots as $date => $slotList) {
401                $reduced = $slotList->withReducedSlots($slotsRequired);
402                $this->slots[$date] = $reduced;
403            }
404        }
405        return $this;
406    }
407
408    public function postProcess($data)
409    {
410        $data[$this->getPrefixed("appointment__date")] = strtotime($data[$this->getPrefixed("appointment__date")]);
411        $data[$this->getPrefixed("availability__startDate")] =
412            strtotime($data[$this->getPrefixed("availability__startDate")]);
413        $data[$this->getPrefixed("availability__endDate")] =
414            strtotime($data[$this->getPrefixed("availability__endDate")]);
415        return $data;
416    }
417
418    public function __toString()
419    {
420        return "Query_SlotList: {$this->availability} {$this->scope}";
421    }
422}