Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
77.78%
483 / 621
70.69%
41 / 58
 CRAP
0.00%
0 / 1
Process
77.78%
483 / 621
70.69%
41 / 58
 506.25
0.00%
0 / 1
 getQueryNewProcessId
100.00%
5 / 5
100.00%
1 / 1
 1
 getLockProcessId
100.00%
1 / 1
100.00%
1 / 1
 1
 addJoin
100.00%
6 / 6
100.00%
1 / 1
 3
 addJoinAvailability
100.00%
6 / 6
100.00%
1 / 1
 1
 addJoinScope
100.00%
11 / 11
100.00%
1 / 1
 1
 addConditionDate
0.00%
0 / 2
0.00%
0 / 1
 2
 addConditionDisplayNumber
0.00%
0 / 2
0.00%
0 / 1
 2
 calculateStatus
0.00%
0 / 33
0.00%
0 / 1
 506
 getEntityMapping
100.00%
72 / 72
100.00%
1 / 1
 3
 addCountValue
100.00%
4 / 4
100.00%
1 / 1
 1
 addConditionHasTelephone
100.00%
6 / 6
100.00%
1 / 1
 1
 addConditionProcessDeleteInterval
100.00%
11 / 11
100.00%
1 / 1
 1
 addConditionProcessExpiredIPTimeStamp
100.00%
5 / 5
100.00%
1 / 1
 1
 addConditionProcessReminderInterval
100.00%
7 / 7
100.00%
1 / 1
 1
 addConditionProcessMailReminder
100.00%
44 / 44
100.00%
1 / 1
 1
 addConditionProcessId
100.00%
7 / 7
100.00%
1 / 1
 1
 addConditionProcessIdFollow
100.00%
6 / 6
100.00%
1 / 1
 1
 addConditionIgnoreSlots
100.00%
6 / 6
100.00%
1 / 1
 1
 addConditionScopeId
100.00%
6 / 6
100.00%
1 / 1
 1
 addConditionScopeIds
25.00%
2 / 8
0.00%
0 / 1
 3.69
 addConditionQueueNumber
100.00%
4 / 4
100.00%
1 / 1
 2
 addConditionWorkstationId
100.00%
5 / 5
100.00%
1 / 1
 1
 addConditionTime
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionTimeframe
0.00%
0 / 6
0.00%
0 / 1
 2
 addConditionAuthKey
100.00%
8 / 8
100.00%
1 / 1
 1
 addConditionAssigned
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionStatus
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionIsReserved
100.00%
12 / 12
100.00%
1 / 1
 1
 addConditionSearch
100.00%
12 / 12
100.00%
1 / 1
 2
 addConditionName
100.00%
8 / 8
100.00%
1 / 1
 2
 addConditionMail
62.50%
5 / 8
0.00%
0 / 1
 2.21
 addConditionCustomTextfield
0.00%
0 / 8
0.00%
0 / 1
 6
 addConditionCustomTextfield2
0.00%
0 / 8
0.00%
0 / 1
 6
 addConditionAmendment
100.00%
4 / 4
100.00%
1 / 1
 1
 addConditionRequestId
100.00%
8 / 8
100.00%
1 / 1
 1
 addConditionDeallocate
100.00%
7 / 7
100.00%
1 / 1
 1
 addValuesNewProcess
100.00%
15 / 15
100.00%
1 / 1
 6
 getNewDisplayNumber
13.33%
2 / 15
0.00%
0 / 1
 8.86
 checkIfDisplayNumberOnSameDateExists
0.00%
0 / 6
0.00%
0 / 1
 2
 addValuesUpdateProcess
100.00%
13 / 13
100.00%
1 / 1
 3
 addValuesIPAdress
100.00%
3 / 3
100.00%
1 / 1
 1
 addValuesFollowingProcessData
85.71%
6 / 7
0.00%
0 / 1
 3.03
 addValuesAppointmentData
100.00%
7 / 7
100.00%
1 / 1
 2
 addValuesScopeData
100.00%
3 / 3
100.00%
1 / 1
 1
 addValuesStatusData
78.57%
22 / 28
0.00%
0 / 1
 14.66
 addValuesClientData
91.30%
21 / 23
0.00%
0 / 1
 17.19
 addValueDisplayNumber
100.00%
2 / 2
100.00%
1 / 1
 1
 addProcessingTimeData
30.43%
14 / 46
0.00%
0 / 1
 79.98
 addValuesQueueData
100.00%
16 / 16
100.00%
1 / 1
 11
 addValuesWaitingTimeData
62.50%
10 / 16
0.00%
0 / 1
 17.38
 addValuesWayTimeData
60.00%
3 / 5
0.00%
0 / 1
 3.58
 addValuesWasMissed
100.00%
5 / 5
100.00%
1 / 1
 2
 addValuesPriority
100.00%
5 / 5
100.00%
1 / 1
 1
 addValuesExternalUserId
100.00%
5 / 5
100.00%
1 / 1
 1
 postProcess
100.00%
31 / 31
100.00%
1 / 1
 7
 removeDuplicates
100.00%
2 / 2
100.00%
1 / 1
 1
 addRequiredJoins
100.00%
14 / 14
100.00%
1 / 1
 3
 addConditionExternalUserId
0.00%
0 / 2
0.00%
0 / 1
 2
1<?php
2
3namespace BO\Zmsdb\Query;
4
5use BO\Zmsdb\Scope as ScopeEntity;
6use BO\Zmsdldb\Helper\DateTime;
7use DateTimeImmutable;
8
9/**
10 * @SuppressWarnings(Methods)
11 * @SuppressWarnings(Complexity)
12 */
13class Process extends Base implements MappingInterface
14{
15    /**
16     *     * @var String TABLE mysql table reference
17     */
18    const TABLE = 'buerger';
19
20    const QUERY_DEREFERENCED = "UPDATE `buerger` process LEFT JOIN `standort` s USING(StandortID)
21        SET
22            process.Anmerkung = ?,
23            process.custom_text_field = ?,
24            process.custom_text_field2 = ?,
25            process.StandortID = 0,
26            process.AbholortID = 0,
27            process.Abholer = 0,
28            process.Name = 'dereferenced',
29            process.IPadresse = '',
30            process.IPTimeStamp = 0,
31            process.NutzerID = 0,
32            process.vorlaeufigeBuchung = 0,
33            process.bestaetigt = 1,
34            process.absagecode = 'deref!0',
35            process.EMail = '',
36            process.NutzerID = 0,
37            process.priority = null,
38            process.status = 'blocked'
39        WHERE
40            (process.BuergerID = ? AND process.absagecode = ?)
41            OR process.istFolgeterminvon = ?
42        ";
43
44    const QUERY_CANCELED = "
45        UPDATE `buerger` process LEFT JOIN `standort` s USING(StandortID)
46            SET
47                process.Anmerkung = CONCAT(
48                    'Abgesagter Termin gebucht am: ',
49                    FROM_UNIXTIME(process.IPTimeStamp,'%d.%m.%Y, %H:%i'),' Uhr | ',
50                    IFNULL(process.Anmerkung,'')
51                ),              
52                process.Name = '(abgesagt)',
53                process.IPadresse = '',
54                process.IPTimeStamp = :canceledTimestamp + (IFNULL(s.loeschdauer, 15) * 60),
55                process.NutzerID = 0,
56                process.vorlaeufigeBuchung = 1,
57                process.status = 'deleted',
58                process.absagecode = RIGHT(MD5(CONCAT(process.absagecode, 'QUERY_CANCELED')), 4)
59            WHERE
60                (process.BuergerID = :processId AND process.absagecode = :authKey)
61                OR process.istFolgeterminvon = :processId
62        ";
63
64    const QUERY_DELETE = "DELETE FROM `buerger`
65        WHERE
66            BuergerID = ?
67            OR istFolgeterminvon = ?
68        ";
69
70    const QUERY_REASSIGN_PROCESS_CREDENTIALS = "UPDATE `buerger` process
71       SET 
72            process.BuergerID = :newProcessId, 
73            process.absagecode = :newAuthKey
74        WHERE BuergerID = :processId
75    ";
76
77    const QUERY_REASSIGN_PROCESS_REQUESTS = "UPDATE `buergeranliegen` requests
78        SET 
79            requests.BuergerID = :newProcessId
80        WHERE BuergerID = :processId
81    ";
82
83    const QUERY_REASSIGN_FOLLWING_PROCESS = "UPDATE `buerger` process
84        SET process.istFolgeterminvon = :newProcessId
85        WHERE istFolgeterminvon = :processId
86    ";
87
88    const QUERY_UPDATE_FOLLOWING_PROCESS = "UPDATE buerger 
89        SET vorlaeufigeBuchung = :reserved 
90        WHERE istFolgeterminvon = :processID
91        ";
92
93    public function getQueryNewProcessId()
94    {
95        $random = rand(20, 999);
96        return 'SELECT pseq.processId AS `nextid`
97            FROM process_sequence pseq
98            WHERE pseq.processId = (
99                SELECT ps.processID FROM `process_sequence` ps LEFT JOIN `' . self::getTablename() . '` p
100                    ON ps.processId = p.BuergerID
101                WHERE p.`BuergerID` IS NULL
102                LIMIT ' . $random . ',1)
103            FOR UPDATE';
104    }
105
106    public function getLockProcessId()
107    {
108        return 'SELECT p.`BuergerID` FROM `' . self::getTablename() . '` p WHERE p.`BuergerID` = :processId FOR UPDATE';
109    }
110
111    public function addJoin()
112    {
113        $joins = [];
114
115        if ($this->shouldLoadEntity('availability')) {
116            $joins[] = $this->addJoinAvailability();
117        }
118
119        if ($this->shouldLoadEntity('scope')) {
120            $joins[] = $this->addJoinScope();
121        }
122
123        return $joins;
124    }
125
126    /**
127     * Add Availability to the dataset
128     */
129    protected function addJoinAvailability()
130    {
131        $this->leftJoin(
132            new Alias(Availability::TABLE, 'availability'),
133            Availability::getJoinExpression('`process`', '`availability`')
134        );
135        $joinQuery = new Availability($this, $this->getPrefixed('appointments__0__availability__'));
136        return $joinQuery;
137    }
138
139    /**
140     * Add Scope to the dataset
141     */
142    protected function addJoinScope()
143    {
144        $this->leftJoin(
145            new Alias(Scope::TABLE, 'scope'),
146            self::expression(
147                'IF(`process`.`AbholortID`,
148                    `process`.`AbholortID`,
149                    `process`.`StandortID`
150                )'
151            ),
152            '=',
153            'scope.StandortID'
154        );
155        $joinQuery = new Scope($this, $this->getPrefixed('scope__'));
156        return $joinQuery;
157    }
158
159    public function addConditionDate($date)
160    {
161        $this->query->where('process.Datum', '=', $date);
162        return $this;
163    }
164
165    public function addConditionDisplayNumber($displayNumber)
166    {
167        $this->query->where('process.displayNumber', '=', $displayNumber);
168        return $this;
169    }
170
171    protected function calculateStatus()
172    {
173        if ($this->query->value('Name') === '(abgesagt)') {
174            return 'deleted';
175        }
176
177        if (
178            $this->query->value('StandortID') == 0
179            && $this->query->value('AbholortID') == 0
180        ) {
181            return 'blocked';
182        }
183
184        if (
185            $this->query->value('vorlaeufigeBuchung') == 1
186            && $this->query->value('bestaetigt') == 0
187        ) {
188            return 'reserved';
189        }
190
191        if ($this->query->value('nicht_erschienen') != 0) {
192            return 'missed';
193        }
194
195        if ($this->query->value('parked') != 0) {
196            return 'parked';
197        }
198
199        if (
200            $this->query->value('Abholer') != 0
201            && $this->query->value('AbholortID') != 0
202            && $this->query->value('NutzerID') == 0
203        ) {
204            return 'pending';
205        }
206
207        if (
208            $this->query->value('AbholortID') == 0
209            && $this->query->value('aufruferfolgreich') != 0
210            && $this->query->value('NutzerID') != 0
211        ) {
212            return 'processing';
213        }
214
215        if (
216            $this->query->value('aufrufzeit') != "00:00:00"
217            && $this->query->value('NutzerID') != 0
218            && $this->query->value('AbholortID') == 0
219        ) {
220            return 'called';
221        }
222
223        if ($this->query->value('Uhrzeit') == "00:00:00") {
224            return 'queued';
225        }
226
227        if (
228            $this->query->value('vorlaeufigeBuchung') == 0
229            && $this->query->value('bestaetigt') == 0
230        ) {
231            return 'preconfirmed';
232        }
233
234        if (
235            $this->query->value('vorlaeufigeBuchung') == 0
236            && $this->query->value('bestaetigt') == 1
237        ) {
238            return 'confirmed';
239        }
240
241        return null;
242    }
243
244    public function getEntityMapping()
245    {
246        $status_expression = self::expression(
247            'CASE
248                WHEN process.status = "called" AND process.aufrufzeit != "00:00:00" AND process.NutzerID != 0 AND process.AbholortID = 0
249                    THEN "called"
250                WHEN process.status = "called" AND process.Uhrzeit = "00:00:00"
251                    THEN "queued"
252                WHEN process.status = "called" AND process.vorlaeufigeBuchung = 0 AND process.bestaetigt = 1
253                    THEN "confirmed"
254                ELSE process.status
255            END'
256        );
257
258        return array_filter([
259            'amendment' => 'process.Anmerkung',
260            'id' => 'process.BuergerID',
261            'appointments__0__date' => self::expression(
262                'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
263            ),
264            'scope__id' => self::expression(
265                'IF(`process`.`AbholortID`,
266                    `process`.`AbholortID`,
267                    `process`.`StandortID`
268)'
269            ),
270            'appointments__0__scope__id' => 'process.StandortID',
271            // 'appointments__0__slotCount' => 'process.hatFolgetermine',
272            'appointments__0__slotCount' => self::expression('process.hatFolgetermine + 1'),
273            'authKey' => 'process.absagecode',
274            'clients__0__email' => 'process.EMail',
275            'clients__0__emailSendCount' => 'process.EMailverschickt',
276            'clients__0__familyName' => 'process.Name',
277            'clients__0__notificationsSendCount' => 'process.SMSverschickt',
278            'clients__0__surveyAccepted' => 'process.zustimmung_kundenbefragung',
279            'clients__0__telephone' => self::expression(
280                'IF(`process`.`telefonnummer_fuer_rueckfragen`!="",
281                    `process`.`telefonnummer_fuer_rueckfragen`,
282                    `process`.`Telefonnummer`
283                )'
284            ),
285            'customTextfield' => 'process.custom_text_field',
286            'customTextfield2' => 'process.custom_text_field2',
287            'createIP' => 'process.IPAdresse',
288            'priority' => 'process.priority',
289            'createTimestamp' => 'process.IPTimeStamp',
290            'lastChange' => 'process.updateTimestamp',
291            'showUpTime' => 'process.showUpTime',
292            'processingTime' => 'process.processingTime',
293            'timeoutTime' => 'process.timeoutTime',
294            'finishTime' => 'process.finishTime',
295            'status' => $status_expression,
296            'queue__status' => $status_expression,
297            'queue__arrivalTime' => self::expression(
298                'CONCAT(
299                    `process`.`Datum`,
300                    " ",
301                    IF(`process`.`wsm_aufnahmezeit`, `process`.`wsm_aufnahmezeit`, `process`.`Uhrzeit`)
302                )'
303            ),
304            'queue__callCount' => 'process.AnzahlAufrufe',
305            'queue__callTime' => 'process.aufrufzeit',
306            'queue__lastCallTime' => 'process.Timestamp',
307            'displayNumber' => self::expression(
308                'COALESCE(
309                    `process`.`displayNumber`,
310                    IF(`process`.`wartenummer`,
311                        `process`.`wartenummer`,
312                        `process`.`BuergerID`
313                    )
314                )'
315            ),
316            'queue__number' => self::expression(
317                'IF(`process`.`wartenummer`,
318                    `process`.`wartenummer`,
319                    `process`.`BuergerID`
320                )'
321            ),
322            'queue__destination' => $this->shouldLoadEntity('processuser')
323                ? 'processuser.Arbeitsplatznr'
324                : '',
325            'queue__destinationHint' => $this->shouldLoadEntity('processuser')
326                ? 'processuser.aufrufzusatz'
327                : '',
328            'queue__waitingTime' => 'process.wartezeit',
329            'queue__wayTime' => 'process.wegezeit',
330            'queue__withAppointment' => self::expression(
331                'IF(`process`.`wartenummer`,
332                    "0",
333                    "1"
334                )'
335            ),
336            'reminderTimestamp' => 'process.Erinnerungszeitpunkt',
337            '__clientsCount' => 'process.AnzahlPersonen',
338            'wasMissed' => 'process.wasMissed',
339            'externalUserId' => 'process.external_user_id',
340            'isTicketprinter' => 'process.is_ticketprinter',
341            'parkedBy' => 'process.parkedBy',
342        ], 'strlen');
343    }
344
345    public function addCountValue()
346    {
347        $this->query->select([
348            'processCount' => self::expression('COUNT(*)'),
349        ]);
350        return $this;
351    }
352
353    public function addConditionHasTelephone()
354    {
355        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) {
356            $condition
357                ->andWith('process.telefonnummer_fuer_rueckfragen', '!=', '')
358                ->orWith('process.Telefonnummer', '!=', '');
359        });
360        return $this;
361    }
362
363    public function addConditionProcessDeleteInterval(\DateTimeInterface $expirationDate)
364    {
365        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($expirationDate) {
366            $query->andWith(
367                self::expression(
368                    'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
369                ),
370                '<=',
371                $expirationDate->format('Y-m-d H:i:s')
372            );
373        });
374        $this->query->orderBy('appointments__0__date', 'ASC');
375        return $this;
376    }
377
378    public function addConditionProcessExpiredIPTimeStamp(\DateTimeInterface $expirationDate)
379    {
380        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($expirationDate) {
381            $query->andWith('process.IPTimeStamp', '<=', $expirationDate->getTimestamp());
382        });
383        $this->query->orderBy('appointments__0__date', 'ASC');
384        return $this;
385    }
386
387    public function addConditionProcessReminderInterval(\DateTimeInterface $dateTime)
388    {
389        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($dateTime) {
390            $query
391                ->andWith('process.Erinnerungszeitpunkt', '<=', $dateTime->getTimestamp())
392                ->andWith('process.Erinnerungszeitpunkt', '>=', $dateTime->modify("-5 Minutes")->getTimestamp());
393        });
394        $this->query->orderBy('reminderTimestamp', 'ASC');
395        return $this;
396    }
397
398    public function addConditionProcessMailReminder(
399        \DateTimeInterface $now,
400        \DateTimeInterface $lastRun,
401        $defaultReminderInMinutes
402    ) {
403        $this->query
404            ->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($now, $lastRun, $defaultReminderInMinutes) {
405                $query
406                    ->andWith(
407                        self::expression(
408                            'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
409                        ),
410                        '>',
411                        $lastRun->format('Y-m-d H:i:s')
412                    )
413                    ->andWith(
414                        self::expression(
415                            'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
416                        ),
417                        '>',
418                        $now->format('Y-m-d H:i:s')
419                    )
420                    ->andWith(
421                        'scopemail.send_reminder',
422                        '=',
423                        1
424                    )
425                    ->andWith(
426                        'process.EMail',
427                        '<>',
428                        ""
429                    )
430                    ->andWith(
431                        'process.EMailverschickt',
432                        '=',
433                        0
434                    )
435                    ->andWith(
436                        self::expression(
437                            'DATE_SUB(CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`), INTERVAL '
438                                . 'IFNULL(scopemail.send_reminder_minutes_before, ' . $defaultReminderInMinutes
439                                . ') MINUTE)'
440                        ),
441                        '<=',
442                        $now->format('Y-m-d H:i:s')
443                    );
444            });
445        $this->query->orderBy('appointments__0__date', 'ASC');
446        return $this;
447    }
448
449    public function addConditionProcessId($processId)
450    {
451        $this->query->where('process.BuergerID', '=', $processId);
452        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) {
453            $condition
454                ->andWith('process.istFolgeterminvon', 'IS', null)
455                ->orWith('process.istFolgeterminvon', '=', 0);
456        });
457        return $this;
458    }
459
460    public function addConditionProcessIdFollow($processId)
461    {
462        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($processId) {
463            $condition
464                ->andWith('process.BuergerID', '=', $processId)
465                ->orWith('process.istFolgeterminvon', '=', $processId);
466        });
467        return $this;
468    }
469
470    public function addConditionIgnoreSlots()
471    {
472        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) {
473            $condition
474                ->andWith('process.istFolgeterminvon', 'IS', null)
475                ->orWith('process.istFolgeterminvon', '=', 0);
476        });
477        return $this;
478    }
479
480    public function addConditionScopeId($scopeId)
481    {
482        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($scopeId) {
483            $query
484                ->andWith('process.StandortID', '=', $scopeId)
485                ->orWith('process.AbholortID', '=', $scopeId);
486        });
487        return $this;
488    }
489
490    public function addConditionScopeIds($scopeIds)
491    {
492        if (count($scopeIds) == 1) {
493            return $this->addConditionScopeId($scopeIds[0]);
494        }
495
496        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($scopeIds) {
497            $query
498                ->andWith('process.StandortID', 'IN', $scopeIds)
499                ->orWith('process.AbholortID', 'IN', $scopeIds);
500        });
501
502        return $this;
503    }
504
505    public function addConditionQueueNumber($queueNumber, $queueLimit = 10000)
506    {
507        ($queueLimit > $queueNumber)
508            ? $this->query->where('process.wartenummer', '=', $queueNumber)
509            : $this->query->where('process.BuergerID', '=', $queueNumber);
510        return $this;
511    }
512
513    public function addConditionWorkstationId($workstationId)
514    {
515        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($workstationId) {
516            $query->andWith('process.NutzerID', '=', $workstationId);
517            $query->andWith('process.StandortID', '>', 0);
518        });
519        return $this;
520    }
521
522    public function addConditionTime($dateTime)
523    {
524        $this->query->where('process.Datum', '=', $dateTime->format('Y-m-d'));
525        return $this;
526    }
527
528    /**
529     * Identify processes between two dates
530     */
531    public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate)
532    {
533        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
534            $condition
535                ->andWith('process.Datum', '<=', $endDate->format('Y-m-d'))
536                ->andWith('process.Datum', '>=', $startDate->format('Y-m-d'));
537        });
538        return $this;
539    }
540
541    public function addConditionAuthKey($authKey)
542    {
543        $authKey = urldecode($authKey);
544        $this->query
545            ->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($authKey) {
546                $condition
547                    ->andWith('process.absagecode', '=', $authKey)
548                    ->orWith('process.Name', '=', $authKey);
549            });
550        return $this;
551    }
552
553    public function addConditionAssigned()
554    {
555        $this->query->where('process.StandortID', '!=', "0");
556        return $this;
557    }
558
559    public function addConditionStatus($status)
560    {
561        $this->query->where('process.status', '=', $status);
562        return $this;
563    }
564
565    public function addConditionIsReserved()
566    {
567        $this->query->where('process.name', 'NOT IN', array(
568            'dereferenced',
569            '(abgesagt)'
570        ))
571            ->where('process.vorlaeufigeBuchung', '=', 1)
572            ->where('process.StandortID', '>', 0);
573        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) {
574            $condition
575                ->andWith('process.istFolgeterminvon', 'IS', null)
576                ->orWith('process.istFolgeterminvon', '=', 0);
577        });
578        return $this;
579    }
580
581    public function addConditionSearch($queryString, $orWhere = false)
582    {
583        $condition = function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($queryString) {
584            $queryString = trim($queryString);
585            $query->orWith('process.Name', 'LIKE', "%$queryString%");
586            $query->orWith('process.EMail', 'LIKE', "%$queryString%");
587            $query->orWith('process.Telefonnummer', 'LIKE', "%$queryString%");
588            $query->orWith('process.telefonnummer_fuer_rueckfragen', 'LIKE', "%$queryString%");
589            $query->orWith('process.displayNumber', 'LIKE', "%$queryString%");
590        };
591        if ($orWhere) {
592            $this->query->orWhere($condition);
593        } else {
594            $this->query->where($condition);
595        }
596        return $this;
597    }
598
599    public function addConditionName($name, $exactMatching = false)
600    {
601        if ($exactMatching) {
602            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($name) {
603                $query->andWith('process.Name', '=', $name);
604            });
605        } else {
606            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($name) {
607                $query->andWith('process.Name', 'LIKE', "%$name%");
608            });
609        }
610        return $this;
611    }
612
613    public function addConditionMail($mailAddress, $exactMatching = false)
614    {
615        if ($exactMatching) {
616            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
617                $query->andWith('process.Email', '=', $mailAddress);
618            });
619        } else {
620            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
621                $query->andWith('process.Email', 'LIKE', "%$mailAddress%");
622            });
623        }
624        return $this;
625    }
626
627    public function addConditionCustomTextfield($customText, $exactMatching = false)
628    {
629        if ($exactMatching) {
630            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText) {
631                $query->andWith('process.custom_text_field', '=', $customText);
632            });
633        } else {
634            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText) {
635                $query->andWith('process.custom_text_field', 'LIKE', "%$customText%");
636            });
637        }
638        return $this;
639    }
640
641    public function addConditionCustomTextfield2($customText2, $exactMatching = false)
642    {
643        if ($exactMatching) {
644            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText2) {
645                $query->andWith('process.custom_text_field2', '=', $customText2);
646            });
647        } else {
648            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText2) {
649                $query->andWith('process.custom_text_field2', 'LIKE', "%$customText2%");
650            });
651        }
652        return $this;
653    }
654
655    public function addConditionAmendment($amendment)
656    {
657        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($amendment) {
658            $query->andWith('process.Anmerkung', 'LIKE', "%$amendment%");
659        });
660        return $this;
661    }
662
663    /**
664     * Add Requests Join
665     */
666    public function addConditionRequestId($requestId)
667    {
668        $this->leftJoin(
669            new Alias("buergeranliegen", 'buergeranliegen'),
670            'buergeranliegen.BuergerID',
671            '=',
672            'process.BuergerID'
673        );
674        $this->query->where('buergeranliegen.AnliegenID', '=', $requestId);
675        return $this;
676    }
677
678    /**
679     * add condition to get process if deallocation time < now
680     */
681    public function addConditionDeallocate($now)
682    {
683        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($now) {
684            $query
685                ->andWith('process.Name', '=', '(abgesagt)')
686                ->andWith('process.IPTimeStamp', '<', $now->getTimestamp());
687        });
688        $this->query->orderBy('process.IPTimeStamp', 'ASC');
689        return $this;
690    }
691
692    public function addValuesNewProcess(\BO\Zmsentities\Process $process, $parentProcess = 0, $childProcessCount = 0)
693    {
694        $values = [
695            'BuergerID' => $process->id,
696            'IPTimeStamp' => $process->createTimestamp,
697            'absagecode' => $process->authKey,
698            'hatFolgetermine' => $childProcessCount,
699            'istFolgeterminvon' => $parentProcess,
700            'displayNumber' => $parentProcess === 0 && empty($process->queue['number'])
701                ? $this->getNewDisplayNumber($process)
702                : null,
703            'wartenummer' => $process->queue['number']
704        ];
705        if ($process->toProperty()->apiclient->apiClientID->isAvailable()) {
706            $values['apiClientID'] = $process->apiclient->apiClientID;
707        }
708        if (isset($process->isTicketprinter) && $process->isTicketprinter) {
709            $values['is_ticketprinter'] = 1;
710        }
711        $this->addValues($values);
712    }
713
714    public function getNewDisplayNumber($process)
715    {
716        if (empty($process->scope->getPreference('queue', 'displayNumberPrefix'))) {
717            return $process->id;
718        }
719
720        $newDisplayNumber = $process->scope->getPreference('queue', 'displayNumberPrefix') . str_pad(
721            (new ScopeEntity())->readDisplayNumberUpdated($process->scope->id),
722            4,
723            '0',
724            STR_PAD_LEFT
725        );
726
727        if (
728            $this->checkIfDisplayNumberOnSameDateExists(
729                $process->scope->id,
730                $newDisplayNumber,
731                \DateTime::createFromFormat('U', (int) $process->getFirstAppointment()->date)
732            )
733        ) {
734            return $this->getNewDisplayNumber($process);
735        }
736
737        return $newDisplayNumber;
738    }
739
740    public function checkIfDisplayNumberOnSameDateExists($scopeId, $displayNumber, $date): bool
741    {
742        $processWithDisplayNumber = (new \BO\Zmsdb\Process())->readProcessWithSameDayAndDisplayNumber(
743            $scopeId,
744            $displayNumber,
745            $date->format('Y-m-d')
746        );
747
748        return !empty($processWithDisplayNumber->getId());
749    }
750
751    public function addValuesUpdateProcess(
752        \BO\Zmsentities\Process $process,
753        \DateTimeInterface $dateTime,
754        $parentProcess = 0,
755        $previousStatus = null
756    ) {
757        $this->addValuesIPAdress($process);
758        if (0 === $parentProcess) {
759            $this->addValuesClientData($process);
760            $this->addProcessingTimeData($process, $dateTime, $previousStatus);
761            $this->addValuesQueueData($process);
762            $this->addValuesWaitingTimeData($process, $previousStatus);
763            $this->addValuesWayTimeData($process);
764        }
765        if ($process->isWithAppointment()) {
766            $this->addValuesFollowingProcessData($process, $parentProcess);
767        }
768        $this->addValuesWasMissed($process);
769        $this->addValuesPriority($process);
770        $this->addValuesStatusData($process, $dateTime);
771        $this->addValuesExternalUserId($process);
772    }
773
774    public function addValuesIPAdress($process)
775    {
776        $data = array();
777        $data['IPAdresse'] = $process['createIP'];
778        $this->addValues($data);
779    }
780
781    public function addValuesFollowingProcessData($process, $parentProcess)
782    {
783        $data = array();
784        if (0 === $parentProcess) {
785            $data['hatFolgetermine'] = (1 <= $process->getFirstAppointment()->getSlotCount()) ?
786                $process->getFirstAppointment()->getSlotCount() - 1 :
787                0;
788        } else {
789            $data['Name'] = '(Folgetermin)';
790        }
791        $this->addValues($data);
792    }
793
794    public function addValuesAppointmentData(
795        \BO\Zmsentities\Process $process
796    ) {
797        $data = array();
798        $appointment = $process->getFirstAppointment();
799        if (null !== $appointment) {
800            $datetime = $appointment->toDateTime();
801            $data['Datum'] = $datetime->format('Y-m-d');
802            $data['Uhrzeit'] = $datetime->format('H:i:s');
803        }
804        $this->addValues($data);
805    }
806
807    public function addValuesScopeData(
808        \BO\Zmsentities\Process $process
809    ) {
810        $data = array();
811        $data['StandortID'] = $process->getScopeId();
812        $this->addValues($data);
813    }
814
815    public function addValuesStatusData($process, \DateTimeInterface $dateTime)
816    {
817        $data = array();
818        $data['vorlaeufigeBuchung'] = ($process['status'] == 'reserved') ? 1 : 0;
819        $data['aufruferfolgreich'] = ($process['status'] == 'processing') ? 1 : 0;
820        if ($process->status == 'called') {
821            $data['parked'] = 0;
822            $data['nicht_erschienen'] = 0;
823        }
824        if ($process->status == 'pending') {
825            $data['AbholortID'] = $process->scope['id'];
826            $data['Abholer'] = 1;
827            $data['nicht_erschienen'] = 0;
828            $data['parked'] = 0;
829        }
830        if ($process->status == 'queued') {
831            $data['nicht_erschienen'] = 0;
832            $data['parked'] = 0;
833            if (
834                $process->hasArrivalTime() &&
835                (isset($process->queue['withAppointment']) && $process->queue['withAppointment'])
836            ) {
837                $data['wsm_aufnahmezeit'] = $dateTime->format('H:i:s');
838            }
839        }
840        if ($process->status == 'missed') {
841            $data['nicht_erschienen'] = 1;
842        }
843        if ($process->status == 'parked') {
844            $data['parked'] = 1;
845        }
846        if ($process->status == 'confirmed') {
847            $data['bestaetigt'] = 1;
848        }
849        if ($process->status == 'preconfirmed') {
850            $data['bestaetigt'] = 0;
851        }
852        $data['status'] = $process['status'] ?? $process->status;
853        $data['parkedBy'] = $process->getParkedBy();
854
855        $this->addValues($data);
856    }
857
858    protected function addValuesClientData($process)
859    {
860        $data = array();
861        $client = $process->getFirstClient();
862        if ($client && $client->hasFamilyName()) {
863            $data['Name'] = $client->familyName;
864        }
865        if ($client && $client->hasEmail()) {
866            $data['EMail'] = $client->email;
867        }
868        if ($client && $client->offsetExists('telephone')) {
869            $data['telefonnummer_fuer_rueckfragen'] = $client->telephone;
870            $data['Telefonnummer'] = $client->telephone; // to stay compatible with ZMS1
871        }
872        if ($client && $client->offsetExists('emailSendCount')) {
873            $data['EMailverschickt'] = ('-1' == $client->emailSendCount) ? 0 : $client->emailSendCount;
874        }
875        if ($client && $client->offsetExists('notificationsSendCount')) {
876            $data['SMSverschickt'] = ('-1' == $client->notificationsSendCount) ? 0 : $client->notificationsSendCount;
877        }
878        if ($process->getAmendment()) {
879            $data['Anmerkung'] = $process->getAmendment();
880        }
881        if ($process->getCustomTextfield()) {
882            $data['custom_text_field'] = $process->getCustomTextfield();
883        }
884        if ($process->getCustomTextfield2()) {
885            $data['custom_text_field2'] = $process->getCustomTextfield2();
886        }
887        $data['zustimmung_kundenbefragung'] = ($client->surveyAccepted) ? 1 : 0;
888        $data['Erinnerungszeitpunkt'] = $process->getReminderTimestamp();
889        $data['AnzahlPersonen'] = $process->getClients()->count();
890        $this->addValues($data);
891    }
892
893    public function addValueDisplayNumber($process)
894    {
895        $data['displayNumber'] = $process->displayNumber;
896        $this->addValues($data);
897    }
898
899    protected function addProcessingTimeData($process, \DateTimeInterface $dateTime, $previousStatus = null)
900    {
901        $data = array();
902        $timeoutTime = null;
903        $showUpTime = null;
904        $finishTime = null;
905
906        if (
907            isset($previousStatus) &&
908            (($process->status == 'called' && $previousStatus == 'called') ||
909                ($process->status == 'processing' && $previousStatus == 'processing'))
910        ) {
911            $timeoutTime = $dateTime->format('Y-m-d H:i:s');
912            $data['timeoutTime'] = $timeoutTime;
913        } elseif ($process->status == 'processing') {
914            $showUpTime = $dateTime->format('Y-m-d H:i:s');
915            $data['showUpTime'] = $showUpTime;
916        } elseif ($process->status == 'finished') {
917            $finishTime = $dateTime->format('Y-m-d H:i:s');
918            $data['finishTime'] = $finishTime;
919        }
920
921
922        if (isset($finishTime) && isset($process->showUpTime)) {
923            $showUpDateTime = new \DateTime($process->showUpTime);
924            $finishTime = new \DateTime($finishTime);
925
926            $processingTimeStr = $process->getProcessingTime();
927            $previousProcessingTimeInSeconds = 0; // Default to 0 if not set
928
929            if (!empty($processingTimeStr)) {
930                // Assume the format is HH:MM:SS and parse it
931                list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
932                // Convert hours, minutes, and seconds to total seconds
933                $previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
934            }
935
936            $interval = $showUpDateTime->diff($finishTime);
937            $totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
938
939            $totalSeconds += $previousProcessingTimeInSeconds;
940
941            $hours = intdiv($totalSeconds, 3600);
942            $minutes = intdiv($totalSeconds % 3600, 60);
943            $seconds = $totalSeconds % 60;
944
945            $data['processingTime'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
946        } elseif (isset($timeoutTime) && isset($process->showUpTime)) {
947            $showUpDateTime = new \DateTime($process->showUpTime);
948            $timeoutDateTime = new \DateTime($timeoutTime);
949            $processingTimeStr = $process->getProcessingTime();
950
951            $previousProcessingTimeInSeconds = 0; // Default to 0 if not set
952            if (!empty($processingTimeStr)) {
953                // Assume the format is HH:MM:SS and parse it
954                list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
955                // Convert hours, minutes, and seconds to total seconds
956                $previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
957            }
958            $interval = $showUpDateTime->diff($timeoutDateTime);
959            $totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
960
961            $totalSeconds += $previousProcessingTimeInSeconds;
962
963            $hours = intdiv($totalSeconds, 3600);
964            $minutes = intdiv($totalSeconds % 3600, 60);
965            $seconds = $totalSeconds % 60;
966
967            $data['processingTime'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
968        }
969
970        $this->addValues($data);
971    }
972
973    protected function addValuesQueueData($process)
974    {
975        $data = array();
976        $appointmentTime = $process->getFirstAppointment()->toDateTime()->format('H:i:s');
977
978        if (isset($process->queue['callCount']) && $process->queue['callCount']) {
979            $data['AnzahlAufrufe'] = $process->queue['callCount'];
980        }
981        if (isset($process->queue['callTime']) && $process->queue['callTime']) {
982            $data['aufrufzeit'] = (new \DateTimeImmutable())
983                ->setTimestamp($process->queue['callTime'])->format('H:i:s');
984        }
985        if (isset($process->queue['lastCallTime']) && $process->queue['lastCallTime']) {
986            $data['Timestamp'] = (new \DateTimeImmutable())
987                ->setTimestamp($process->queue['lastCallTime'])->format('H:i:s');
988        }
989        if (isset($process->queue['arrivalTime']) && $process->queue['arrivalTime']) {
990            $data['wsm_aufnahmezeit'] = (new \DateTimeImmutable())
991                ->setTimestamp($process->queue['arrivalTime'])->format('H:i:s');
992        }
993        if (isset($data['wsm_aufnahmezeit']) && $data['wsm_aufnahmezeit'] == $appointmentTime) {
994            // Do not save arrivalTime if it is an appointment
995            $data['wsm_aufnahmezeit'] = 0;
996        }
997        $this->addValues($data);
998    }
999
1000    protected function addValuesWaitingTimeData($process, $previousStatus = null)
1001    {
1002        $data = array();
1003
1004        if (
1005            (
1006                // Szenario 1: Vorheriger Status ist queued, missed oder confirmed und aktueller Status ist called
1007                in_array($previousStatus, ['queued', 'missed', 'confirmed'])
1008                && $process['status'] == 'called'
1009                && ($process->queue['callCount'] <= 0 || !empty($process['wasMissed']))
1010            )
1011            ||
1012            (
1013                // Szenario 2: Vorheriger Status ist missed, aktueller Status ist queued,
1014                // es gibt den Hinweis wasMissed und die Queue sowie waitingTime sind gesetzt
1015                $previousStatus == 'missed'
1016                && $process['status'] == 'queued'
1017                && !empty($process['wasMissed'])
1018                && isset($process->queue)
1019                && isset($process->queue->waitingTime)
1020            )
1021        ) {
1022            $wartezeitInSeconds = $process->getWaitedSeconds();
1023            $wartezeitInSeconds = $wartezeitInSeconds > 0 ? $wartezeitInSeconds : 0;
1024
1025            // Convert total seconds into HH:MM:SS format
1026            $hours = intdiv($wartezeitInSeconds, 3600);
1027            $minutes = intdiv($wartezeitInSeconds % 3600, 60);
1028            $seconds = $wartezeitInSeconds % 60;
1029
1030            $data['wartezeit'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1031        }
1032
1033        $this->addValues($data);
1034    }
1035
1036
1037    protected function addValuesWayTimeData($process)
1038    {
1039        $data = array();
1040        if ($process['status'] == 'processing') {
1041            $wegezeit = $process->getWayMinutes();
1042            $data['wegezeit'] = $wegezeit > 0 ? $wegezeit : 0;
1043        }
1044        $this->addValues($data);
1045    }
1046
1047    protected function addValuesWasMissed($process)
1048    {
1049        $data = [
1050            'wasMissed' => $process->wasMissed ? 1 : 0,
1051        ];
1052
1053        $this->addValues($data);
1054        return $this;
1055    }
1056
1057    protected function addValuesPriority($process)
1058    {
1059        $data = [
1060            'priority' => $process->priority,
1061        ];
1062
1063        $this->addValues($data);
1064        return $this;
1065    }
1066
1067    protected function addValuesExternalUserId($process)
1068    {
1069        $data = [
1070            'external_user_id' => $process->externalUserId,
1071        ];
1072
1073        $this->addValues($data);
1074        return $this;
1075    }
1076
1077    public function postProcess($data)
1078    {
1079        $data[$this->getPrefixed("appointments__0__date")] =
1080            strtotime($data[$this->getPrefixed("appointments__0__date")]);
1081        if ('00:00:00' != $data[$this->getPrefixed("queue__callTime")]) {
1082            $time = explode(':', $data[$this->getPrefixed("queue__callTime")]);
1083            $data[$this->getPrefixed("queue__callTime")] = (new \DateTimeImmutable())
1084                ->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1085                ->setTime($time[0], $time[1], $time[2])
1086                ->getTimestamp();
1087        } else {
1088            $data[$this->getPrefixed("queue__callTime")] = 0;
1089        }
1090        if ('00:00:00' != $data[$this->getPrefixed("queue__lastCallTime")]) {
1091            $time = explode(':', $data[$this->getPrefixed("queue__lastCallTime")]);
1092            $data[$this->getPrefixed("queue__lastCallTime")] = (new \DateTimeImmutable())
1093                ->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1094                ->setTime($time[0], $time[1], $time[2])
1095                ->getTimestamp();
1096        } else {
1097            $data[$this->getPrefixed("queue__lastCallTime")] = 0;
1098        }
1099        $data[$this->getPrefixed("queue__arrivalTime")] =
1100            strtotime($data[$this->getPrefixed("queue__arrivalTime")]);
1101        if (
1102            isset($data[$this->getPrefixed('scope__provider__data')])
1103            && $data[$this->getPrefixed('scope__provider__data')]
1104        ) {
1105            $data[$this->getPrefixed('scope__provider__data')] =
1106                json_decode($data[$this->getPrefixed('scope__provider__data')], true);
1107        }
1108        if (isset($data[$this->getPrefixed('__clientsCount')])) {
1109            $clientsCount = $data[$this->getPrefixed('__clientsCount')];
1110            unset($data[$this->getPrefixed('__clientsCount')]);
1111            while (--$clientsCount > 0) {
1112                $data[$this->getPrefixed('clients__' . $clientsCount . '__familyName')] = 'Unbekannt';
1113            }
1114        }
1115        $data[$this->getPrefixed("lastChange")] =
1116            (new \DateTimeImmutable($data[$this->getPrefixed("lastChange")] .
1117                \BO\Zmsdb\Connection\Select::$connectionTimezone))->getTimestamp();
1118        return $data;
1119    }
1120
1121    public function removeDuplicates()
1122    {
1123        $this->query->groupBy('process.BuergerID');
1124        return $this;
1125    }
1126
1127    protected function addRequiredJoins()
1128    {
1129        if ($this->shouldLoadEntity('processuser')) {
1130            $this->leftJoin(
1131                new Alias(Useraccount::TABLE, 'processuser'),
1132                'process.NutzerID',
1133                '=',
1134                'processuser.NutzerID'
1135            );
1136        }
1137
1138        if ($this->shouldLoadEntity('processscope')) {
1139            $this->leftJoin(
1140                new Alias(Scope::TABLE, 'processscope'),
1141                'process.StandortID',
1142                '=',
1143                'processscope.StandortID'
1144            );
1145        }
1146    }
1147
1148    public function addConditionExternalUserId(string $externalUserId)
1149    {
1150        $this->query->where('process.external_user_id', '=', $externalUserId);
1151        return $this;
1152    }
1153}