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