Lines 100.00% 181 / 181
Functions and Methods 100.00% 19 / 19
Classes and Traits 100.00% 1 / 1
Name Lines Functions and Methods CRAP Classes and Traits
Availability 100.00% 181 / 181 100.00% 19 / 19 28 100.00% 1 / 1
 addRequiredJoins 100.00% 6 / 6 100.00% 1 / 1 1
 getEntityMapping 100.00% 40 / 40 100.00% 1 / 1 2
 getReferenceMapping 100.00% 3 / 3 100.00% 1 / 1 1
 addConditionAvailabilityId 100.00% 2 / 2 100.00% 1 / 1 1
 addConditionScopeId 100.00% 2 / 2 100.00% 1 / 1 1
 addConditionAppointmentHours 100.00% 4 / 4 100.00% 1 / 1 1
 addConditionOpeningHours 100.00% 4 / 4 100.00% 1 / 1 1
 addConditionDoubleTypes 100.00% 6 / 6 100.00% 1 / 1 1
 addConditionSkipOld 100.00% 4 / 4 100.00% 1 / 1 1
 addConditionOnlyOld 100.00% 4 / 4 100.00% 1 / 1 1
 addConditionTimeframe 100.00% 6 / 6 100.00% 1 / 1 1
 addConditionDate 100.00% 11 / 11 100.00% 1 / 1 1
 addConditionAppointmentTime 100.00% 4 / 4 100.00% 1 / 1 1
 reverseEntityMapping 100.00% 42 / 42 100.00% 1 / 1 6
 getJoinExpression 100.00% 11 / 11 100.00% 1 / 1 1
 getStartTimeExpression 100.00% 2 / 2 100.00% 1 / 1 1
 getEndTimeExpression 100.00% 2 / 2 100.00% 1 / 1 1
 getProcessMatchCondition 100.00% 18 / 18 100.00% 1 / 1 1
 postProcess 100.00% 10 / 10 100.00% 1 / 1 4
1<?php
2
3namespace BO\Zmsbackend\Availability\Repository;
4
5/**
6 * @SuppressWarnings(Public)
7 */
8class Availability extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface
9{
10    /**
11     * @var String TABLE mysql table reference
12     */
13    const string TABLE = 'oeffnungszeit';
14
15    const string TEMPORARY_DELETE = 'DELETE FROM oeffnungszeit WHERE `comment` = "--temporary--"';
16
17    const string QUERY_GET_LOCK = '
18        SELECT OeffnungszeitID FROM oeffnungszeit WHERE OeffnungszeitID = :availabilityId FOR UPDATE
19    ';
20
21    /**
22     * @return void
23     */
24    #[\Override]
25    public function addRequiredJoins()
26    {
27         $this->leftJoin(
28             new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Query\Scope::TABLE, 'availabilityscope'),
29             'availability.scope_id',
30             '=',
31             'availabilityscope.StandortID'
32         );
33    }
34
35    /**
36     * @return (\BO\Zmsbackend\Query\Builder\Expression|string)[]
37     *
38     */
39    #[\Override]
40    public function getEntityMapping(?string $type = null)
41    {
42        $mapping = [
43            'id' => 'availability.OeffnungszeitID',
44            'scope__id' => 'availability.scope_id',
45            'bookable__startInDays' => self::expression(
46                'CAST(
47                    IF(`availability`.`open_from_days` = "0" OR `availability`.`open_from_days`, `availability`.`open_from_days`, `availabilityscope`.`Termine_ab`)
48                    AS SIGNED)'
49            ),
50            'bookable__endInDays' => self::expression(
51                'IF((`availability`.`open_from_days` = "0" AND `availability`.`open_until_days` = "0") OR `availability`.`open_until_days`, `availability`.`open_until_days`, `availabilityscope`.`Termine_bis`)'
52            ),
53            'description' => 'availability.comment',
54            'startDate' => 'availability.start_date',
55            'startTime' => self::expression(static::getStartTimeExpression('`availability`')),
56            'endDate' => 'availability.end_date',
57            'endTime' => self::expression(static::getEndTimeExpression('`availability`')),
58            'lastChange' => 'availability.updated_at',
59            'version' => 'availability.version',
60            'multipleSlotsAllowed' => 'availability.multiple_slots_allowed',
61            'repeat__afterWeeks' => 'availability.every_x_weeks',
62            'repeat__weekOfMonth' => 'availability.every_other_week',
63            'slotTimeInMinutes' => self::expression('FLOOR(TIME_TO_SEC(`availability`.`time_slot`) / 60)') ,
64            // dependant function on this IF(): \BO\Zmsbackend\Availablity\Service\Availablity::readList()
65            'type' => self::expression(
66                "IF(`availability`.`appointment_start_time`, 'appointment', 'openinghours')"
67            ),
68            'weekday__monday' => self::expression('`availability`.`weekday` & 2'),
69            'weekday__tuesday' => self::expression('`availability`.`weekday` & 4'),
70            'weekday__wednesday' => self::expression('`availability`.`weekday` & 8'),
71            'weekday__thursday' => self::expression('`availability`.`weekday` & 16'),
72            'weekday__friday' => self::expression('`availability`.`weekday` & 32'),
73            'weekday__saturday' => self::expression('`availability`.`weekday` & 64'),
74            'weekday__sunday' => self::expression('`availability`.`weekday` & 1'),
75            'workstationCount__intern' => 'availability.appointment_workstation_count',
76            'workstationCount__public' => self::expression(
77                'GREATEST(0, `availability`.`appointment_workstation_count` - `availability`.`internet_reduction`)'
78            )
79        ];
80        if ('openinghours' == $type) {
81            // Test if following line is needed: type mapping with IF() a few lines before
82            //$mapping['type'] = self::expression('"openinghours"');
83            $mapping['startTime'] = 'availability.start_time';
84            $mapping['endTime'] = 'availability.end_time';
85        }
86        return $mapping;
87    }
88
89    /**
90     * @return \BO\Zmsbackend\Query\Builder\Expression[]
91     *
92     */
93    #[\Override]
94    public function getReferenceMapping()
95    {
96        return [
97            'scope__$ref' => self::expression('CONCAT("/scope/", `availability`.`scope_id`, "/")'),
98        ];
99    }
100
101    public function addConditionAvailabilityId(int|string $availabilityId): static
102    {
103        $this->query->where('availability.OeffnungszeitID', '=', $availabilityId);
104        return $this;
105    }
106
107    public function addConditionScopeId(string|int $scopeId): static
108    {
109        $this->query->where('availabilityscope.StandortID', '=', $scopeId);
110        return $this;
111    }
112
113    public function addConditionAppointmentHours(): static
114    {
115        $this->query
116            ->where('availability.appointment_start_time', '!=', '00:00:00')
117            ->where('availability.appointment_end_time', '!=', '00:00:00');
118        return $this;
119    }
120
121    public function addConditionOpeningHours(): static
122    {
123        $this->query
124            ->where('availability.start_time', '!=', '00:00:00')
125            ->where('availability.end_time', '!=', '00:00:00');
126        return $this;
127    }
128
129    /**
130     * Used to identify old availabilities as appointment and openinghours
131     *
132     * @psalm-api
133     */
134    public function addConditionDoubleTypes(): static
135    {
136        $this->query
137            ->where('availability.appointment_start_time', '!=', '00:00:00')
138            ->where('availability.appointment_end_time', '!=', '00:00:00')
139            ->where('availability.start_time', '!=', '00:00:00')
140            ->where('availability.end_time', '!=', '00:00:00');
141        return $this;
142    }
143
144    public function addConditionSkipOld(\DateTimeInterface $dateTime): static
145    {
146        $date = $dateTime->format('Y-m-d');
147        $this->query
148            ->where('availability.end_date', '>=', $date);
149        return $this;
150    }
151
152    /**
153     * Availabilities whose end date is on or before the given date.
154     *
155     * @psalm-api
156     */
157    public function addConditionOnlyOld(\DateTimeInterface $dateTime): static
158    {
159        $date = $dateTime->format('Y-m-d');
160        $this->query
161            ->where('availability.end_date', '<=', $date);
162        return $this;
163    }
164
165   /**
166     * Identify availabilities between two dates
167     */
168    public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate): static
169    {
170        $this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
171            $condition
172                ->andWith('availability.start_date', '<=', $endDate->format('Y-m-d'))
173                ->andWith('availability.end_date', '>=', $startDate->format('Y-m-d'));
174        });
175        return $this;
176    }
177
178    public function addConditionDate(\DateTimeInterface $dateTime): static
179    {
180        $date = $dateTime->format('Y-m-d');
181        $this->query
182            ->where('availability.start_date', '<=', $date)
183            ->where('availability.end_date', '>=', $date);
184        //-- match weekday
185        $this->query->where(self::expression("availability.weekday & POW(2, DAYOFWEEK('$date') - 1)"), '>=', '1');
186        //-- match week
187        $this->query->where(self::expression("
188            (
189                (
190                    availability.every_x_weeks
191                    AND FLOOR(
192                        (
193                            FLOOR(UNIX_TIMESTAMP('$date'))
194                            - FLOOR(UNIX_TIMESTAMP(availability.start_date)))
195                            / 86400
196                            / 7
197                        )
198                        % availability.every_x_weeks = 0
199                )
200                OR (
201                    availability.every_other_week
202                    AND (
203                        CEIL(DAYOFMONTH('$date') / 7) = availability.every_other_week
204                        OR (
205                            availability.every_other_week = 5
206                            AND CEIL(LAST_DAY('$date') / 7) = CEIL(DAYOFMONTH('$date') / 7)
207                        )
208                    )
209                )
210                OR (availability.every_x_weeks = 0 AND availability.every_other_week = 0)
211            ) AND 1
212            "), '=', '1');
213        return $this;
214    }
215
216    public function addConditionAppointmentTime(\DateTimeInterface $dateTime): static
217    {
218        $time = $dateTime->format('H:i:s');
219        $this->query->where("availability.appointment_start_time", '<=', $time);
220        $this->query->where("availability.appointment_end_time", '>', $time);
221
222        return $this;
223    }
224
225    /**
226     * @return (int|mixed|string)[]
227     *
228     */
229    public function reverseEntityMapping(\BO\Zmsentities\Availability $entity): array
230    {
231        $data = array();
232        $data['scope_id'] = $entity['scope']['id'];
233        $data['open_from_days'] = $entity['bookable']['startInDays'];
234        $data['open_until_days'] = $entity['bookable']['endInDays'];
235        $data['comment'] = $entity['description'];
236        $data['start_date'] = $entity->getStartDateTime()->format('Y-m-d');
237        $data['end_date'] = $entity->getEndDateTime()->format('Y-m-d');
238        $data['version'] = $entity['version'];
239        if ('openinghours' == $entity['type']) {
240            $data['start_time'] = $entity['startTime'];
241            $data['end_time'] = $entity['endTime'];
242            $data['appointment_start_time'] = 0;
243            $data['appointment_end_time'] = 0;
244        } else {
245            $data['start_time'] = 0;
246            $data['end_time'] = 0;
247            $data['appointment_start_time'] = $entity['startTime'];
248            $data['appointment_end_time'] = $entity['endTime'];
249        }
250        $data['every_x_weeks'] = $entity['repeat']['afterWeeks'];
251        $data['every_other_week'] = $entity['repeat']['weekOfMonth'];
252        $data['time_slot'] = gmdate("H:i", $entity['slotTimeInMinutes'] * 60);
253        $data['multiple_slots_allowed'] = $entity['multipleSlotsAllowed'] ? 1 : 0;
254        $wochentagBinaryCoded = 0;
255        $binaryCodes = [
256            'sunday' => 1,
257            'monday' => 2,
258            'tuesday' => 4,
259            'wednesday' => 8,
260            'thursday' => 16,
261            'friday' => 32,
262            'saturday' => 64,
263            ];
264        foreach ($entity['weekday'] as $weekday => $isActive) {
265            if ($isActive) {
266                $wochentagBinaryCoded |= $binaryCodes[$weekday];
267            }
268        }
269        $data['weekday'] = $wochentagBinaryCoded;
270        $data['appointment_workstation_count'] = $entity['workstationCount']['intern'];
271        $data['internet_reduction'] =
272            $entity['workstationCount']['intern'] - $entity['workstationCount']['public'];
273
274        $data = array_filter($data, function ($value) {
275            return ($value !== null && $value !== false);
276        });
277            return $data;
278    }
279
280    public static function getJoinExpression(string $process, string $availability): \BO\Zmsbackend\Query\Builder\Expression
281    {
282        $matchesProcess = static::getProcessMatchCondition($process, $availability);
283        $fallbackMatchesProcess = static::getProcessMatchCondition($process, 'fallback_availability');
284        return self::expression("
285            $matchesProcess
286
287            -- overlapping availabilities: use the availability the process is mapped to,
288            -- unmapped processes use the shortest matching availability, then the lower id.
289            -- SQL counterpart of AvailabilityList::getSortedByDuration(), keep both in sync
290            AND $availability.OeffnungszeitID = IFNULL(
291                (
292                    SELECT mapped_slot.availabilityID
293                    FROM slot_process mapped_process
294                        INNER JOIN slot mapped_slot ON mapped_slot.slotID = mapped_process.slotID
295                    WHERE mapped_process.processID = $process.BuergerID
296                    LIMIT 1
297                ),
298                (
299                    SELECT fallback_availability.OeffnungszeitID
300                    FROM " . self::getTablename() . " fallback_availability
301                    WHERE $fallbackMatchesProcess
302                    ORDER BY
303                        TIMEDIFF(
304                            " . static::getEndTimeExpression('fallback_availability') . ",
305                            " . static::getStartTimeExpression('fallback_availability') . "
306                        ),
307                        fallback_availability.OeffnungszeitID
308                    LIMIT 1
309                )
310            )
311            ");
312    }
313
314    /**
315     * Start time as the entity sees it (startTime), appointment times win over opening times
316     */
317    protected static function getStartTimeExpression(string $availability): string
318    {
319        return "IF($availability.`appointment_start_time`, $availability.`appointment_start_time`, "
320            . "$availability.`start_time`)";
321    }
322
323    /**
324     * End time as the entity sees it (endTime), appointment times win over opening times
325     */
326    protected static function getEndTimeExpression(string $availability): string
327    {
328        return "IF($availability.`appointment_start_time`, $availability.`appointment_end_time`, "
329            . "$availability.`end_time`)";
330    }
331
332    protected static function getProcessMatchCondition(string $process, string $availability): string
333    {
334        // UNIX_TIMESTAMP is relative here, no dependency to TIMEZONE
335        return "
336            $availability.scope_id = $process.StandortID
337            AND $availability.OeffnungszeitID IS NOT NULL
338
339            -- match weekday
340            AND $availability.weekday & POW(2, DAYOFWEEK($process.Datum) - 1)
341
342            -- match week
343            AND (
344                (
345                    $availability.every_x_weeks
346                    AND FLOOR(
347                        (
348                            FLOOR(UNIX_TIMESTAMP($process.Datum))
349                            - FLOOR(UNIX_TIMESTAMP($availability.start_date)))
350                            / 86400
351                            / 7
352                        )
353                        % $availability.every_x_weeks = 0
354                )
355                OR (
356                    $availability.every_other_week
357                    AND (
358                        CEIL(DAYOFMONTH($process.Datum) / 7) = $availability.every_other_week
359                        OR (
360                            $availability.every_other_week = 5
361                            AND CEIL(LAST_DAY($process.Datum) / 7) = CEIL(DAYOFMONTH($process.Datum) / 7)
362                        )
363                    )
364                )
365                OR ($availability.every_x_weeks = 0 AND $availability.every_other_week = 0)
366            )
367
368            -- match time and date
369            AND $process.Uhrzeit >= $availability.appointment_start_time
370            AND $process.Uhrzeit < $availability.appointment_end_time
371            AND $process.Datum >= $availability.start_date
372            AND $process.Datum <= $availability.end_date
373            ";
374    }
375
376    #[\Override]
377    public function postProcess(array $data)
378    {
379        $startDateKey = $this->getPrefixed("startDate");
380        $endDateKey = $this->getPrefixed("endDate");
381        $lastChangeKey = $this->getPrefixed("lastChange");
382        $startDate = $data[$startDateKey] ?? null;
383        $endDate = $data[$endDateKey] ?? null;
384        $lastChange = $data[$lastChangeKey] ?? null;
385        $data[$startDateKey] = $startDate !== null ? (new \DateTime($startDate))->getTimestamp() : null;
386        $data[$endDateKey] = $endDate !== null ? (new \DateTime($endDate))->getTimestamp() : null;
387        $data[$lastChangeKey] = $lastChange !== null ? (new \DateTime($lastChange . \BO\Zmsbackend\Connection\Select::$connectionTimezone))->getTimestamp() : null;
388        return $data;
389    }
390}