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