Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
96.45%
163 / 169
93.75%
15 / 16
 CRAP
0.00%
0 / 1
Availability
96.45%
163 / 169
93.75%
15 / 16
 25
0.00%
0 / 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
0.00%
0 / 6
0.00%
0 / 1
 2
 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%
17 / 17
100.00%
1 / 1
 1
 postProcess
100.00%
10 / 10
100.00%
1 / 1
 4
1<?php
2
3namespace BO\Zmsdb\Query;
4
5/**
6 * @SuppressWarnings(Public)
7 */
8class Availability extends Base implements MappingInterface
9{
10    /**
11     * @var String TABLE mysql table reference
12     */
13    const TABLE = 'oeffnungszeit';
14
15    const TEMPORARY_DELETE = 'DELETE FROM oeffnungszeit WHERE kommentar = "--temporary--"';
16
17    const QUERY_GET_LOCK = '
18        SELECT OeffnungszeitID FROM oeffnungszeit WHERE OeffnungszeitID = :availabilityId FOR UPDATE
19    ';
20
21    #[\Override]
22    public function addRequiredJoins()
23    {
24         $this->leftJoin(
25             new Alias(Scope::TABLE, 'availabilityscope'),
26             'availability.StandortID',
27             '=',
28             'availabilityscope.StandortID'
29         );
30    }
31
32    #[\Override]
33    public function getEntityMapping($type = null)
34    {
35        $mapping = [
36            'id' => 'availability.OeffnungszeitID',
37            'scope__id' => 'availability.StandortID',
38            'bookable__startInDays' => self::expression(
39                'CAST(
40                    IF(`availability`.`Offen_ab` = "0" OR `availability`.`Offen_ab`, `availability`.`Offen_ab`, `availabilityscope`.`Termine_ab`)
41                    AS SIGNED)'
42            ),
43            'bookable__endInDays' => self::expression(
44                'IF((`availability`.`Offen_ab` = "0" AND `availability`.`Offen_bis` = "0") OR `availability`.`Offen_bis`, `availability`.`Offen_bis`, `availabilityscope`.`Termine_bis`)'
45            ),
46            'description' => 'availability.kommentar',
47            'startDate' => 'availability.Startdatum',
48            'startTime' => self::expression(
49                'IF(`availability`.`Terminanfangszeit`,`availability`.`Terminanfangszeit`,`availability`.`Anfangszeit`)'
50            ),
51            'endDate' => 'availability.Endedatum',
52            'endTime' => self::expression(
53                'IF(`availability`.`Terminanfangszeit`, `availability`.`Terminendzeit`, `availability`.`Endzeit`)'
54            ),
55            'lastChange' => 'availability.updateTimestamp',
56            'version' => 'availability.version',
57            'multipleSlotsAllowed' => 'availability.erlaubemehrfachslots',
58            'repeat__afterWeeks' => 'availability.allexWochen',
59            'repeat__weekOfMonth' => 'availability.jedexteWoche',
60            'slotTimeInMinutes' => self::expression('FLOOR(TIME_TO_SEC(`availability`.`Timeslot`) / 60)') ,
61            // dependant function on this IF(): \BO\Zmsdb\Availablity::readList()
62            'type' => self::expression(
63                "IF(`availability`.`Terminanfangszeit`, 'appointment', 'openinghours')"
64            ),
65            'weekday__monday' => self::expression('`availability`.`Wochentag` & 2'),
66            'weekday__tuesday' => self::expression('`availability`.`Wochentag` & 4'),
67            'weekday__wednesday' => self::expression('`availability`.`Wochentag` & 8'),
68            'weekday__thursday' => self::expression('`availability`.`Wochentag` & 16'),
69            'weekday__friday' => self::expression('`availability`.`Wochentag` & 32'),
70            'weekday__saturday' => self::expression('`availability`.`Wochentag` & 64'),
71            'weekday__sunday' => self::expression('`availability`.`Wochentag` & 1'),
72            'workstationCount__intern' => 'availability.Anzahlterminarbeitsplaetze',
73            'workstationCount__public' => self::expression(
74                'GREATEST(0, `availability`.`Anzahlterminarbeitsplaetze` - `availability`.`reduktionTermineImInternet`)'
75            )
76        ];
77        if ('openinghours' == $type) {
78            // Test if following line is needed: type mapping with IF() a few lines before
79            //$mapping['type'] = self::expression('"openinghours"');
80            $mapping['startTime'] = 'availability.Anfangszeit';
81            $mapping['endTime'] = 'availability.Endzeit';
82        }
83        return $mapping;
84    }
85
86    #[\Override]
87    public function getReferenceMapping()
88    {
89        return [
90            'scope__$ref' => self::expression('CONCAT("/scope/", `availability`.`StandortID`, "/")'),
91        ];
92    }
93
94    public function addConditionAvailabilityId($availabilityId)
95    {
96        $this->query->where('availability.OeffnungszeitID', '=', $availabilityId);
97        return $this;
98    }
99
100    public function addConditionScopeId($scopeId)
101    {
102        $this->query->where('availabilityscope.StandortID', '=', $scopeId);
103        return $this;
104    }
105
106    public function addConditionAppointmentHours()
107    {
108        $this->query
109            ->where('availability.Terminanfangszeit', '!=', '00:00:00')
110            ->where('availability.Terminendzeit', '!=', '00:00:00');
111        return $this;
112    }
113
114    public function addConditionOpeningHours()
115    {
116        $this->query
117            ->where('availability.Anfangszeit', '!=', '00:00:00')
118            ->where('availability.Endzeit', '!=', '00:00:00');
119        return $this;
120    }
121
122    /**
123     * Used to identify old availabilities as appointment and openinghours
124     *
125     */
126    public function addConditionDoubleTypes()
127    {
128        $this->query
129            ->where('availability.Terminanfangszeit', '!=', '00:00:00')
130            ->where('availability.Terminendzeit', '!=', '00:00:00')
131            ->where('availability.Anfangszeit', '!=', '00:00:00')
132            ->where('availability.Endzeit', '!=', '00:00:00');
133        return $this;
134    }
135
136    public function addConditionSkipOld(\DateTimeInterface $dateTime)
137    {
138        $date = $dateTime->format('Y-m-d');
139        $this->query
140            ->where('availability.Endedatum', '>=', $date);
141        return $this;
142    }
143
144    /**
145     * Used to identify availabilities whose End Date was more than 4 weeks ago
146     *
147     */
148    public function addConditionOnlyOld(\DateTimeInterface $dateTime)
149    {
150        $date = $dateTime->format('Y-m-d');
151        $this->query
152            ->where('availability.Endedatum', '<=', $date);
153        return $this;
154    }
155
156   /**
157     * Identify availabilities between two dates
158     *
159     */
160    public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate)
161    {
162        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
163            $condition
164                ->andWith('availability.Startdatum', '<=', $endDate->format('Y-m-d'))
165                ->andWith('availability.Endedatum', '>=', $startDate->format('Y-m-d'));
166        });
167        return $this;
168    }
169
170    public function addConditionDate(\DateTimeInterface $dateTime)
171    {
172        $date = $dateTime->format('Y-m-d');
173        $this->query
174            ->where('availability.Startdatum', '<=', $date)
175            ->where('availability.Endedatum', '>=', $date);
176        //-- match weekday
177        $this->query->where(self::expression("availability.Wochentag & POW(2, DAYOFWEEK('$date') - 1)"), '>=', '1');
178        //-- match week
179        $this->query->where(self::expression("
180            (
181                (
182                    availability.allexWochen
183                    AND FLOOR(
184                        (
185                            FLOOR(UNIX_TIMESTAMP('$date'))
186                            - FLOOR(UNIX_TIMESTAMP(availability.Startdatum)))
187                            / 86400
188                            / 7
189                        )
190                        % availability.allexWochen = 0
191                )
192                OR (
193                    availability.jedexteWoche
194                    AND (
195                        CEIL(DAYOFMONTH('$date') / 7) = availability.jedexteWoche
196                        OR (
197                            availability.jedexteWoche = 5
198                            AND CEIL(LAST_DAY('$date') / 7) = CEIL(DAYOFMONTH('$date') / 7)
199                        )
200                    )
201                )
202                OR (availability.allexWochen = 0 AND availability.jedexteWoche = 0)
203            ) AND 1
204            "), '=', '1');
205        return $this;
206    }
207
208    public function addConditionAppointmentTime(\DateTimeInterface $dateTime)
209    {
210        $time = $dateTime->format('H:i:s');
211        $this->query->where("availability.Terminanfangszeit", '<=', $time);
212        $this->query->where("availability.Terminendzeit", '>', $time);
213
214        return $this;
215    }
216
217    public function reverseEntityMapping(\BO\Zmsentities\Availability $entity)
218    {
219        $data = array();
220        $data['StandortID'] = $entity->scope['id'];
221        $data['Offen_ab'] = $entity->bookable['startInDays'];
222        $data['Offen_bis'] = $entity->bookable['endInDays'];
223        $data['kommentar'] = $entity->description;
224        $data['Startdatum'] = $entity->getStartDateTime()->format('Y-m-d');
225        $data['Endedatum'] = $entity->getEndDateTime()->format('Y-m-d');
226        $data['version'] = $entity->version;
227        if ('openinghours' == $entity->type) {
228            $data['Anfangszeit'] = $entity->startTime;
229            $data['Endzeit'] = $entity->endTime;
230            $data['Terminanfangszeit'] = 0;
231            $data['Terminendzeit'] = 0;
232        } else {
233            $data['Anfangszeit'] = 0;
234            $data['Endzeit'] = 0;
235            $data['Terminanfangszeit'] = $entity->startTime;
236            $data['Terminendzeit'] = $entity->endTime;
237        }
238        $data['allexWochen'] = $entity->repeat['afterWeeks'];
239        $data['jedexteWoche'] = $entity->repeat['weekOfMonth'];
240        $data['Timeslot'] = gmdate("H:i", $entity->slotTimeInMinutes * 60);
241        $data['erlaubemehrfachslots'] = $entity->multipleSlotsAllowed ? 1 : 0;
242        $wochentagBinaryCoded = 0;
243        $binaryCodes = [
244            'sunday' => 1,
245            'monday' => 2,
246            'tuesday' => 4,
247            'wednesday' => 8,
248            'thursday' => 16,
249            'friday' => 32,
250            'saturday' => 64,
251            ];
252        foreach ($entity->weekday as $weekday => $isActive) {
253            if ($isActive) {
254                $wochentagBinaryCoded |= $binaryCodes[$weekday];
255            }
256        }
257        $data['Wochentag'] = $wochentagBinaryCoded;
258        $data['Anzahlterminarbeitsplaetze'] = $entity->workstationCount['intern'];
259        $data['reduktionTermineImInternet'] =
260            $entity->workstationCount['intern'] - $entity->workstationCount['public'];
261
262        $data = array_filter($data, function ($value) {
263            return ($value !== null && $value !== false);
264        });
265            return $data;
266    }
267
268    public static function getJoinExpression($process, $availability)
269    {
270        // UNIX_TIMESTAMP is relative here, no dependency to TIMEZONE
271        return self::expression("
272            $availability.StandortID = $process.StandortID
273            AND $availability.OeffnungszeitID IS NOT NULL
274
275            -- match weekday
276            AND $availability.Wochentag & POW(2, DAYOFWEEK($process.Datum) - 1)
277
278            -- match week
279            AND (
280                (
281                    $availability.allexWochen
282                    AND FLOOR(
283                        (
284                            FLOOR(UNIX_TIMESTAMP($process.Datum))
285                            - FLOOR(UNIX_TIMESTAMP($availability.Startdatum)))
286                            / 86400
287                            / 7
288                        )
289                        % $availability.allexWochen = 0
290                )
291                OR (
292                    $availability.jedexteWoche
293                    AND (
294                        CEIL(DAYOFMONTH($process.Datum) / 7) = $availability.jedexteWoche
295                        OR (
296                            $availability.jedexteWoche = 5
297                            AND CEIL(LAST_DAY($process.Datum) / 7) = CEIL(DAYOFMONTH($process.Datum) / 7)
298                        )
299                    )
300                )
301                OR (availability.allexWochen = 0 AND availability.jedexteWoche = 0)
302            )
303
304            -- match time and date
305            AND $process.Uhrzeit >= $availability.Terminanfangszeit
306            AND $process.Uhrzeit < $availability.Terminendzeit
307            AND $process.Datum >= $availability.Startdatum
308            AND $process.Datum <= $availability.Endedatum
309            ");
310    }
311
312    #[\Override]
313    public function postProcess($data)
314    {
315        $startDateKey = $this->getPrefixed("startDate");
316        $endDateKey = $this->getPrefixed("endDate");
317        $lastChangeKey = $this->getPrefixed("lastChange");
318        $startDate = $data[$startDateKey] ?? null;
319        $endDate = $data[$endDateKey] ?? null;
320        $lastChange = $data[$lastChangeKey] ?? null;
321        $data[$startDateKey] = $startDate !== null ? (new \DateTime($startDate))->getTimestamp() : null;
322        $data[$endDateKey] = $endDate !== null ? (new \DateTime($endDate))->getTimestamp() : null;
323        $data[$lastChangeKey] = $lastChange !== null ? (new \DateTime($lastChange . \BO\Zmsdb\Connection\Select::$connectionTimezone))->getTimestamp() : null;
324        return $data;
325    }
326}