Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00%
170 / 170
100.00%
16 / 16
 CRAP
100.00%
1 / 1
Availability
100.00%
170 / 170
100.00%
16 / 16
 25
100.00%
1 / 1
 addRequiredJoins
100.00%
6 / 6
100.00%
1 / 1
 1
 getEntityMapping
100.00%
44 / 44
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%
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($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(
56                'IF(`availability`.`appointment_start_time`,`availability`.`appointment_start_time`,`availability`.`start_time`)'
57            ),
58            'endDate' => 'availability.end_date',
59            'endTime' => self::expression(
60                'IF(`availability`.`appointment_start_time`, `availability`.`appointment_end_time`, `availability`.`end_time`)'
61            ),
62            'lastChange' => 'availability.updated_at',
63            'version' => 'availability.version',
64            'multipleSlotsAllowed' => 'availability.multiple_slots_allowed',
65            'repeat__afterWeeks' => 'availability.every_x_weeks',
66            'repeat__weekOfMonth' => 'availability.every_other_week',
67            'slotTimeInMinutes' => self::expression('FLOOR(TIME_TO_SEC(`availability`.`time_slot`) / 60)') ,
68            // dependant function on this IF(): \BO\Zmsbackend\Availablity\Service\Availablity::readList()
69            'type' => self::expression(
70                "IF(`availability`.`appointment_start_time`, 'appointment', 'openinghours')"
71            ),
72            'weekday__monday' => self::expression('`availability`.`weekday` & 2'),
73            'weekday__tuesday' => self::expression('`availability`.`weekday` & 4'),
74            'weekday__wednesday' => self::expression('`availability`.`weekday` & 8'),
75            'weekday__thursday' => self::expression('`availability`.`weekday` & 16'),
76            'weekday__friday' => self::expression('`availability`.`weekday` & 32'),
77            'weekday__saturday' => self::expression('`availability`.`weekday` & 64'),
78            'weekday__sunday' => self::expression('`availability`.`weekday` & 1'),
79            'workstationCount__intern' => 'availability.appointment_workstation_count',
80            'workstationCount__public' => self::expression(
81                'GREATEST(0, `availability`.`appointment_workstation_count` - `availability`.`internet_reduction`)'
82            )
83        ];
84        if ('openinghours' == $type) {
85            // Test if following line is needed: type mapping with IF() a few lines before
86            //$mapping['type'] = self::expression('"openinghours"');
87            $mapping['startTime'] = 'availability.start_time';
88            $mapping['endTime'] = 'availability.end_time';
89        }
90        return $mapping;
91    }
92
93    /**
94     * @return \BO\Zmsbackend\Query\Builder\Expression[]
95     *
96     */
97    #[\Override]
98    public function getReferenceMapping()
99    {
100        return [
101            'scope__$ref' => self::expression('CONCAT("/scope/", `availability`.`scope_id`, "/")'),
102        ];
103    }
104
105    public function addConditionAvailabilityId(int|string $availabilityId): static
106    {
107        $this->query->where('availability.OeffnungszeitID', '=', $availabilityId);
108        return $this;
109    }
110
111    public function addConditionScopeId($scopeId): static
112    {
113        $this->query->where('availabilityscope.StandortID', '=', $scopeId);
114        return $this;
115    }
116
117    public function addConditionAppointmentHours(): static
118    {
119        $this->query
120            ->where('availability.appointment_start_time', '!=', '00:00:00')
121            ->where('availability.appointment_end_time', '!=', '00:00:00');
122        return $this;
123    }
124
125    public function addConditionOpeningHours(): static
126    {
127        $this->query
128            ->where('availability.start_time', '!=', '00:00:00')
129            ->where('availability.end_time', '!=', '00:00:00');
130        return $this;
131    }
132
133    /**
134     * Used to identify old availabilities as appointment and openinghours
135     *
136     * @psalm-api
137     */
138    public function addConditionDoubleTypes(): static
139    {
140        $this->query
141            ->where('availability.appointment_start_time', '!=', '00:00:00')
142            ->where('availability.appointment_end_time', '!=', '00:00:00')
143            ->where('availability.start_time', '!=', '00:00:00')
144            ->where('availability.end_time', '!=', '00:00:00');
145        return $this;
146    }
147
148    public function addConditionSkipOld(\DateTimeInterface $dateTime): static
149    {
150        $date = $dateTime->format('Y-m-d');
151        $this->query
152            ->where('availability.end_date', '>=', $date);
153        return $this;
154    }
155
156    /**
157     * Used to identify availabilities whose End Date was more than 4 weeks ago
158     *
159     * @psalm-api
160     */
161    public function addConditionOnlyOld(\DateTimeInterface $dateTime): static
162    {
163        $date = $dateTime->format('Y-m-d');
164        $this->query
165            ->where('availability.end_date', '<=', $date);
166        return $this;
167    }
168
169   /**
170     * Identify availabilities between two dates
171     */
172    public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate): static
173    {
174        $this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
175            $condition
176                ->andWith('availability.start_date', '<=', $endDate->format('Y-m-d'))
177                ->andWith('availability.end_date', '>=', $startDate->format('Y-m-d'));
178        });
179        return $this;
180    }
181
182    public function addConditionDate(\DateTimeInterface $dateTime): static
183    {
184        $date = $dateTime->format('Y-m-d');
185        $this->query
186            ->where('availability.start_date', '<=', $date)
187            ->where('availability.end_date', '>=', $date);
188        //-- match weekday
189        $this->query->where(self::expression("availability.weekday & POW(2, DAYOFWEEK('$date') - 1)"), '>=', '1');
190        //-- match week
191        $this->query->where(self::expression("
192            (
193                (
194                    availability.every_x_weeks
195                    AND FLOOR(
196                        (
197                            FLOOR(UNIX_TIMESTAMP('$date'))
198                            - FLOOR(UNIX_TIMESTAMP(availability.start_date)))
199                            / 86400
200                            / 7
201                        )
202                        % availability.every_x_weeks = 0
203                )
204                OR (
205                    availability.every_other_week
206                    AND (
207                        CEIL(DAYOFMONTH('$date') / 7) = availability.every_other_week
208                        OR (
209                            availability.every_other_week = 5
210                            AND CEIL(LAST_DAY('$date') / 7) = CEIL(DAYOFMONTH('$date') / 7)
211                        )
212                    )
213                )
214                OR (availability.every_x_weeks = 0 AND availability.every_other_week = 0)
215            ) AND 1
216            "), '=', '1');
217        return $this;
218    }
219
220    public function addConditionAppointmentTime(\DateTimeInterface $dateTime): static
221    {
222        $time = $dateTime->format('H:i:s');
223        $this->query->where("availability.appointment_start_time", '<=', $time);
224        $this->query->where("availability.appointment_end_time", '>', $time);
225
226        return $this;
227    }
228
229    /**
230     * @return (int|mixed|string)[]
231     *
232     */
233    public function reverseEntityMapping(\BO\Zmsentities\Availability $entity): array
234    {
235        $data = array();
236        $data['scope_id'] = $entity['scope']['id'];
237        $data['open_from_days'] = $entity['bookable']['startInDays'];
238        $data['open_until_days'] = $entity['bookable']['endInDays'];
239        $data['comment'] = $entity['description'];
240        $data['start_date'] = $entity->getStartDateTime()->format('Y-m-d');
241        $data['end_date'] = $entity->getEndDateTime()->format('Y-m-d');
242        $data['version'] = $entity['version'];
243        if ('openinghours' == $entity['type']) {
244            $data['start_time'] = $entity['startTime'];
245            $data['end_time'] = $entity['endTime'];
246            $data['appointment_start_time'] = 0;
247            $data['appointment_end_time'] = 0;
248        } else {
249            $data['start_time'] = 0;
250            $data['end_time'] = 0;
251            $data['appointment_start_time'] = $entity['startTime'];
252            $data['appointment_end_time'] = $entity['endTime'];
253        }
254        $data['every_x_weeks'] = $entity['repeat']['afterWeeks'];
255        $data['every_other_week'] = $entity['repeat']['weekOfMonth'];
256        $data['time_slot'] = gmdate("H:i", $entity['slotTimeInMinutes'] * 60);
257        $data['multiple_slots_allowed'] = $entity['multipleSlotsAllowed'] ? 1 : 0;
258        $wochentagBinaryCoded = 0;
259        $binaryCodes = [
260            'sunday' => 1,
261            'monday' => 2,
262            'tuesday' => 4,
263            'wednesday' => 8,
264            'thursday' => 16,
265            'friday' => 32,
266            'saturday' => 64,
267            ];
268        foreach ($entity['weekday'] as $weekday => $isActive) {
269            if ($isActive) {
270                $wochentagBinaryCoded |= $binaryCodes[$weekday];
271            }
272        }
273        $data['weekday'] = $wochentagBinaryCoded;
274        $data['appointment_workstation_count'] = $entity['workstationCount']['intern'];
275        $data['internet_reduction'] =
276            $entity['workstationCount']['intern'] - $entity['workstationCount']['public'];
277
278        $data = array_filter($data, function ($value) {
279            return ($value !== null && $value !== false);
280        });
281            return $data;
282    }
283
284    public static function getJoinExpression(string $process, string $availability): \BO\Zmsbackend\Query\Builder\Expression
285    {
286        // UNIX_TIMESTAMP is relative here, no dependency to TIMEZONE
287        return self::expression("
288            $availability.scope_id = $process.StandortID
289            AND $availability.OeffnungszeitID IS NOT NULL
290
291            -- match weekday
292            AND $availability.weekday & POW(2, DAYOFWEEK($process.Datum) - 1)
293
294            -- match week
295            AND (
296                (
297                    $availability.every_x_weeks
298                    AND FLOOR(
299                        (
300                            FLOOR(UNIX_TIMESTAMP($process.Datum))
301                            - FLOOR(UNIX_TIMESTAMP($availability.start_date)))
302                            / 86400
303                            / 7
304                        )
305                        % $availability.every_x_weeks = 0
306                )
307                OR (
308                    $availability.every_other_week
309                    AND (
310                        CEIL(DAYOFMONTH($process.Datum) / 7) = $availability.every_other_week
311                        OR (
312                            $availability.every_other_week = 5
313                            AND CEIL(LAST_DAY($process.Datum) / 7) = CEIL(DAYOFMONTH($process.Datum) / 7)
314                        )
315                    )
316                )
317                OR ($availability.every_x_weeks = 0 AND $availability.every_other_week = 0)
318            )
319
320            -- match time and date
321            AND $process.Uhrzeit >= $availability.appointment_start_time
322            AND $process.Uhrzeit < $availability.appointment_end_time
323            AND $process.Datum >= $availability.start_date
324            AND $process.Datum <= $availability.end_date
325            ");
326    }
327
328    #[\Override]
329    public function postProcess($data)
330    {
331        $startDateKey = $this->getPrefixed("startDate");
332        $endDateKey = $this->getPrefixed("endDate");
333        $lastChangeKey = $this->getPrefixed("lastChange");
334        $startDate = $data[$startDateKey] ?? null;
335        $endDate = $data[$endDateKey] ?? null;
336        $lastChange = $data[$lastChangeKey] ?? null;
337        $data[$startDateKey] = $startDate !== null ? (new \DateTime($startDate))->getTimestamp() : null;
338        $data[$endDateKey] = $endDate !== null ? (new \DateTime($endDate))->getTimestamp() : null;
339        $data[$lastChangeKey] = $lastChange !== null ? (new \DateTime($lastChange . \BO\Zmsbackend\Connection\Select::$connectionTimezone))->getTimestamp() : null;
340        return $data;
341    }
342}