Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
76.20%
493 / 647
69.49%
41 / 59
 CRAP
0.00%
0 / 1
Process
76.20%
493 / 647
69.49%
41 / 59
 605.26
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
45.45%
5 / 11
0.00%
0 / 1
 4.46
 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
33.33%
17 / 51
0.00%
0 / 1
 102.63
 addValuesQueueData
100.00%
16 / 16
100.00%
1 / 1
 10
 addValuesWaitingTimeData
84.62%
33 / 39
0.00%
0 / 1
 19.18
 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) === 0) {
500            $this->query->where(self::expression('1'), '=', 0);
501            return $this;
502        }
503
504        if (count($scopeIds) === 1) {
505            return $this->addConditionScopeId($scopeIds[0]);
506        }
507
508        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($scopeIds) {
509            $query
510                ->andWith('process.StandortID', 'IN', $scopeIds)
511                ->orWith('process.AbholortID', 'IN', $scopeIds);
512        });
513
514        return $this;
515    }
516
517    public function addConditionQueueNumber($queueNumber, $queueLimit = 10000)
518    {
519        ($queueLimit > $queueNumber)
520            ? $this->query->where('process.wartenummer', '=', $queueNumber)
521            : $this->query->where('process.BuergerID', '=', $queueNumber);
522        return $this;
523    }
524
525    public function addConditionWorkstationId($workstationId)
526    {
527        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($workstationId) {
528            $query->andWith('process.NutzerID', '=', $workstationId);
529            $query->andWith('process.StandortID', '>', 0);
530        });
531        return $this;
532    }
533
534    public function addConditionTime($dateTime)
535    {
536        $this->query->where('process.Datum', '=', $dateTime->format('Y-m-d'));
537        return $this;
538    }
539
540    /**
541     * Identify processes between two dates
542     */
543    public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate)
544    {
545        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
546            $condition
547                ->andWith('process.Datum', '<=', $endDate->format('Y-m-d'))
548                ->andWith('process.Datum', '>=', $startDate->format('Y-m-d'));
549        });
550        return $this;
551    }
552
553    public function addConditionAuthKey($authKey)
554    {
555        $authKey = urldecode($authKey);
556        $this->query->where('process.absagecode', '=', $authKey);
557        return $this;
558    }
559
560    public function addConditionAssigned()
561    {
562        $this->query->where('process.StandortID', '!=', "0");
563        return $this;
564    }
565
566    public function addConditionStatus($status)
567    {
568        $this->query->where('process.status', '=', $status);
569        return $this;
570    }
571
572    public function addConditionIsReserved()
573    {
574        $this->query->where('process.name', 'NOT IN', array(
575            'dereferenced',
576            '(abgesagt)'
577        ))
578            ->where('process.vorlaeufigeBuchung', '=', 1)
579            ->where('process.StandortID', '>', 0);
580        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) {
581            $condition
582                ->andWith('process.istFolgeterminvon', 'IS', null)
583                ->orWith('process.istFolgeterminvon', '=', 0);
584        });
585        return $this;
586    }
587
588    public function addConditionSearch($queryString, $orWhere = false)
589    {
590        $condition = function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($queryString) {
591            $queryString = trim($queryString);
592            $query->orWith('process.Name', 'LIKE', "%$queryString%");
593            $query->orWith('process.EMail', 'LIKE', "%$queryString%");
594            $query->orWith('process.Telefonnummer', 'LIKE', "%$queryString%");
595            $query->orWith('process.telefonnummer_fuer_rueckfragen', 'LIKE', "%$queryString%");
596            $query->orWith('process.displayNumber', 'LIKE', "%$queryString%");
597        };
598        if ($orWhere) {
599            $this->query->orWhere($condition);
600        } else {
601            $this->query->where($condition);
602        }
603        return $this;
604    }
605
606    public function addConditionName($name, $exactMatching = false)
607    {
608        if ($exactMatching) {
609            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($name) {
610                $query->andWith('process.Name', '=', $name);
611            });
612        } else {
613            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($name) {
614                $query->andWith('process.Name', 'LIKE', "%$name%");
615            });
616        }
617        return $this;
618    }
619
620    public function addConditionMail($mailAddress, $exactMatching = false)
621    {
622        if ($exactMatching) {
623            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
624                $query->andWith('process.Email', '=', $mailAddress);
625            });
626        } else {
627            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
628                $query->andWith('process.Email', 'LIKE', "%$mailAddress%");
629            });
630        }
631        return $this;
632    }
633
634    public function addConditionCustomTextfield($customText, $exactMatching = false)
635    {
636        if ($exactMatching) {
637            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText) {
638                $query->andWith('process.custom_text_field', '=', $customText);
639            });
640        } else {
641            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText) {
642                $query->andWith('process.custom_text_field', 'LIKE', "%$customText%");
643            });
644        }
645        return $this;
646    }
647
648    public function addConditionCustomTextfield2($customText2, $exactMatching = false)
649    {
650        if ($exactMatching) {
651            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText2) {
652                $query->andWith('process.custom_text_field2', '=', $customText2);
653            });
654        } else {
655            $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($customText2) {
656                $query->andWith('process.custom_text_field2', 'LIKE', "%$customText2%");
657            });
658        }
659        return $this;
660    }
661
662    public function addConditionAmendment($amendment)
663    {
664        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($amendment) {
665            $query->andWith('process.Anmerkung', 'LIKE', "%$amendment%");
666        });
667        return $this;
668    }
669
670    /**
671     * Add Requests Join
672     */
673    public function addConditionRequestId($requestId)
674    {
675        $this->leftJoin(
676            new Alias("buergeranliegen", 'buergeranliegen'),
677            'buergeranliegen.BuergerID',
678            '=',
679            'process.BuergerID'
680        );
681        $this->query->where('buergeranliegen.AnliegenID', '=', $requestId);
682        return $this;
683    }
684
685    /**
686     * add condition to get process if deallocation time < now
687     */
688    public function addConditionDeallocate($now)
689    {
690        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($now) {
691            $query
692                ->andWith('process.Name', '=', '(abgesagt)')
693                ->andWith('process.IPTimeStamp', '<', $now->getTimestamp());
694        });
695        $this->query->orderBy('process.IPTimeStamp', 'ASC');
696        return $this;
697    }
698
699    public function addValuesNewProcess(\BO\Zmsentities\Process $process, $parentProcess = 0, $childProcessCount = 0)
700    {
701        $values = [
702            'BuergerID' => $process->id,
703            'IPTimeStamp' => $process->createTimestamp,
704            'absagecode' => $process->authKey,
705            'hatFolgetermine' => $childProcessCount,
706            'istFolgeterminvon' => $parentProcess,
707            'displayNumber' => $parentProcess === 0 && empty($process->queue['number'])
708                ? $this->getNewDisplayNumber($process)
709                : null,
710            'wartenummer' => $process->queue['number']
711        ];
712        if ($process->toProperty()->apiclient->apiClientID->isAvailable()) {
713            $values['apiClientID'] = $process->apiclient->apiClientID;
714        }
715        if (isset($process->isTicketprinter) && $process->isTicketprinter) {
716            $values['is_ticketprinter'] = 1;
717        }
718        $this->addValues($values);
719    }
720
721    public function getNewDisplayNumber($process)
722    {
723        if (empty($process->scope->getPreference('queue', 'displayNumberPrefix'))) {
724            return $process->id;
725        }
726
727        $newDisplayNumber = $process->scope->getPreference('queue', 'displayNumberPrefix') . str_pad(
728            (new ScopeEntity())->readDisplayNumberUpdated($process->scope->id),
729            4,
730            '0',
731            STR_PAD_LEFT
732        );
733
734        if (
735            $this->checkIfDisplayNumberOnSameDateExists(
736                $process->scope->id,
737                $newDisplayNumber,
738                \DateTime::createFromFormat('U', (int) $process->getFirstAppointment()->date)
739            )
740        ) {
741            return $this->getNewDisplayNumber($process);
742        }
743
744        return $newDisplayNumber;
745    }
746
747    public function checkIfDisplayNumberOnSameDateExists($scopeId, $displayNumber, $date): bool
748    {
749        $processWithDisplayNumber = (new \BO\Zmsdb\Process())->readProcessWithSameDayAndDisplayNumber(
750            $scopeId,
751            $displayNumber,
752            $date->format('Y-m-d')
753        );
754
755        return !empty($processWithDisplayNumber->getId());
756    }
757
758    public function addValuesUpdateProcess(
759        \BO\Zmsentities\Process $process,
760        \DateTimeInterface $dateTime,
761        $parentProcess = 0,
762        $previousStatus = null
763    ) {
764        $this->addValuesIPAdress($process);
765        if (0 === $parentProcess) {
766            $this->addValuesClientData($process);
767            $this->addProcessingTimeData($process, $dateTime, $previousStatus);
768            $this->addValuesQueueData($process);
769            $this->addValuesWaitingTimeData($process, $previousStatus);
770            $this->addValuesWayTimeData($process);
771        }
772        if ($process->isWithAppointment()) {
773            $this->addValuesFollowingProcessData($process, $parentProcess);
774        }
775        $this->addValuesWasMissed($process);
776        $this->addValuesPriority($process);
777        $this->addValuesStatusData($process, $dateTime);
778        $this->addValuesExternalUserId($process);
779    }
780
781    public function addValuesIPAdress($process)
782    {
783        $data = array();
784        $data['IPAdresse'] = $process['createIP'];
785        $this->addValues($data);
786    }
787
788    public function addValuesFollowingProcessData($process, $parentProcess)
789    {
790        $data = array();
791        if (0 === $parentProcess) {
792            $data['hatFolgetermine'] = (1 <= $process->getFirstAppointment()->getSlotCount()) ?
793                $process->getFirstAppointment()->getSlotCount() - 1 :
794                0;
795        } else {
796            $data['Name'] = '(Folgetermin)';
797        }
798        $this->addValues($data);
799    }
800
801    public function addValuesAppointmentData(
802        \BO\Zmsentities\Process $process
803    ) {
804        $data = array();
805        $appointment = $process->getFirstAppointment();
806        if (null !== $appointment) {
807            $datetime = $appointment->toDateTime();
808            $data['Datum'] = $datetime->format('Y-m-d');
809            $data['Uhrzeit'] = $datetime->format('H:i:s');
810        }
811        $this->addValues($data);
812    }
813
814    public function addValuesScopeData(
815        \BO\Zmsentities\Process $process
816    ) {
817        $data = array();
818        $data['StandortID'] = $process->getScopeId();
819        $this->addValues($data);
820    }
821
822    public function addValuesStatusData($process, \DateTimeInterface $dateTime)
823    {
824        $data = array();
825        $data['vorlaeufigeBuchung'] = ($process['status'] == 'reserved') ? 1 : 0;
826        $data['aufruferfolgreich'] = ($process['status'] == 'processing') ? 1 : 0;
827        if ($process->status == 'called') {
828            $data['parked'] = 0;
829            $data['nicht_erschienen'] = 0;
830        }
831        if ($process->status == 'pending') {
832            $data['AbholortID'] = $process->scope['id'];
833            $data['Abholer'] = 1;
834            $data['nicht_erschienen'] = 0;
835            $data['parked'] = 0;
836        }
837        if ($process->status == 'queued') {
838            $data['nicht_erschienen'] = 0;
839            $data['parked'] = 0;
840            if (
841                $process->hasArrivalTime() &&
842                (isset($process->queue['withAppointment']) && $process->queue['withAppointment'])
843            ) {
844                $data['wsm_aufnahmezeit'] = $dateTime->format('H:i:s');
845            }
846        }
847        if ($process->status == 'missed') {
848            $data['nicht_erschienen'] = 1;
849        }
850        if ($process->status == 'parked') {
851            $data['parked'] = 1;
852        }
853        if ($process->status == 'confirmed') {
854            $data['bestaetigt'] = 1;
855        }
856        if ($process->status == 'preconfirmed') {
857            $data['bestaetigt'] = 0;
858        }
859        $data['status'] = $process['status'] ?? $process->status;
860        $data['parkedBy'] = $process->getParkedBy();
861
862        $this->addValues($data);
863    }
864
865    protected function addValuesClientData($process)
866    {
867        $data = array();
868        $client = $process->getFirstClient();
869        if ($client && $client->hasFamilyName()) {
870            $data['Name'] = $client->familyName;
871        }
872        if ($client && $client->hasEmail()) {
873            $data['EMail'] = $client->email;
874        }
875        if ($client && $client->offsetExists('telephone')) {
876            $data['telefonnummer_fuer_rueckfragen'] = $client->telephone;
877            $data['Telefonnummer'] = $client->telephone; // to stay compatible with ZMS1
878        }
879        if ($client && $client->offsetExists('emailSendCount')) {
880            $data['EMailverschickt'] = ('-1' == $client->emailSendCount) ? 0 : $client->emailSendCount;
881        }
882        $data['Anmerkung'] = $process->getAmendment();
883        $data['custom_text_field'] = $process->getCustomTextfield();
884        $data['custom_text_field2'] = $process->getCustomTextfield2();
885        $data['zustimmung_kundenbefragung'] = ($client->surveyAccepted) ? 1 : 0;
886        $data['Erinnerungszeitpunkt'] = $process->getReminderTimestamp();
887        $data['AnzahlPersonen'] = $process->getClients()->count();
888        $this->addValues($data);
889    }
890
891    public function addValueDisplayNumber($process)
892    {
893        $data['displayNumber'] = $process->displayNumber;
894        $this->addValues($data);
895    }
896
897    protected function addProcessingTimeData($process, \DateTimeInterface $dateTime, $previousStatus = null)
898    {
899        $data = array();
900        $timeoutTime = null;
901        $showUpTime = null;
902        $finishTime = null;
903
904        if (
905            isset($previousStatus) &&
906            (($process->status == 'called' && $previousStatus == 'called') ||
907                ($process->status == 'processing' && $previousStatus == 'processing'))
908        ) {
909            $timeoutTime = $dateTime->format('Y-m-d H:i:s');
910            $data['timeoutTime'] = $timeoutTime;
911        } elseif ($process->status == 'processing') {
912            $showUpTime = $dateTime->format('Y-m-d H:i:s');
913            $data['showUpTime'] = $showUpTime;
914        } elseif ($process->status == 'finished') {
915            $finishTime = $dateTime->format('Y-m-d H:i:s');
916            $data['finishTime'] = $finishTime;
917        } elseif (
918            $process->status == 'queued'
919            && isset($previousStatus)
920            && in_array($previousStatus, ['called', 'processing'], true)
921        ) {
922            $data['showUpTime'] = null;
923            $data['timeoutTime'] = null;
924        }
925
926
927        if (isset($finishTime) && isset($process->showUpTime)) {
928            $showUpDateTime = new \DateTime($process->showUpTime);
929            $finishTime = new \DateTime($finishTime);
930
931            $processingTimeStr = $process->getProcessingTime();
932            $previousProcessingTimeInSeconds = 0; // Default to 0 if not set
933
934            if (!empty($processingTimeStr)) {
935                // Assume the format is HH:MM:SS and parse it
936                list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
937                // Convert hours, minutes, and seconds to total seconds
938                $previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
939            }
940
941            $interval = $showUpDateTime->diff($finishTime);
942            $totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
943
944            $totalSeconds += $previousProcessingTimeInSeconds;
945
946            $hours = intdiv($totalSeconds, 3600);
947            $minutes = intdiv($totalSeconds % 3600, 60);
948            $seconds = $totalSeconds % 60;
949
950            $data['processing_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
951        } elseif (isset($timeoutTime) && isset($process->showUpTime)) {
952            $showUpDateTime = new \DateTime($process->showUpTime);
953            $timeoutDateTime = new \DateTime($timeoutTime);
954            $processingTimeStr = $process->getProcessingTime();
955
956            $previousProcessingTimeInSeconds = 0; // Default to 0 if not set
957            if (!empty($processingTimeStr)) {
958                // Assume the format is HH:MM:SS and parse it
959                list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
960                // Convert hours, minutes, and seconds to total seconds
961                $previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
962            }
963            $interval = $showUpDateTime->diff($timeoutDateTime);
964            $totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
965
966            $totalSeconds += $previousProcessingTimeInSeconds;
967
968            $hours = intdiv($totalSeconds, 3600);
969            $minutes = intdiv($totalSeconds % 3600, 60);
970            $seconds = $totalSeconds % 60;
971
972            $data['processing_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
973        }
974
975        $this->addValues($data);
976    }
977
978    protected function addValuesQueueData($process)
979    {
980        $data = array();
981        $appointmentTime = $process->getFirstAppointment()->toDateTime()->format('H:i:s');
982
983        if (isset($process->queue['callCount'])) {
984            $data['AnzahlAufrufe'] = (int) $process->queue['callCount'];
985        }
986        if (isset($process->queue['callTime']) && $process->queue['callTime']) {
987            $data['aufrufzeit'] = (new \DateTimeImmutable())
988                ->setTimestamp($process->queue['callTime'])->format('H:i:s');
989        }
990        if (isset($process->queue['lastCallTime']) && $process->queue['lastCallTime']) {
991            $data['Timestamp'] = (new \DateTimeImmutable())
992                ->setTimestamp($process->queue['lastCallTime'])->format('H:i:s');
993        }
994        if (isset($process->queue['arrivalTime']) && $process->queue['arrivalTime']) {
995            $data['wsm_aufnahmezeit'] = (new \DateTimeImmutable())
996                ->setTimestamp($process->queue['arrivalTime'])->format('H:i:s');
997        }
998        if (isset($data['wsm_aufnahmezeit']) && $data['wsm_aufnahmezeit'] == $appointmentTime) {
999            // Do not save arrivalTime if it is an appointment
1000            $data['wsm_aufnahmezeit'] = 0;
1001        }
1002        $this->addValues($data);
1003    }
1004
1005    protected function addValuesWaitingTimeData($process, $previousStatus = null)
1006    {
1007        $data = array();
1008        $hasWaitingTimeAlready = (
1009            isset($process->queue['waitingTime'])
1010            && $process->queue['waitingTime']
1011            && $process->queue['waitingTime'] !== '00:00:00'
1012        );
1013
1014        if (
1015            !$hasWaitingTimeAlready
1016            && (
1017            (
1018                // Szenario 1: Vorheriger Status ist queued, missed oder confirmed und aktueller Status ist called
1019                in_array($previousStatus, ['queued', 'missed', 'confirmed'])
1020                && $process['status'] == 'called'
1021                && (
1022                    (
1023                        isset($process->queue['callCount'])
1024                        && $process->queue['callCount'] <= 0
1025                    )
1026                    || (
1027                        ($previousStatus === 'queued' || $previousStatus === 'confirmed')
1028                        && (
1029                            !isset($process->queue['callTime'])
1030                            || (int) $process->queue['callTime'] === 0
1031                        )
1032                    )
1033                )
1034            )
1035            ||
1036            (
1037                // Szenario 2: Vorheriger Status ist missed, aktueller Status ist queued,
1038                // es gibt den Hinweis wasMissed und die Queue sowie waitingTime sind gesetzt
1039                $previousStatus == 'missed'
1040                && $process['status'] == 'queued'
1041                && !empty($process['wasMissed'])
1042                && isset($process->queue)
1043                && isset($process->queue->waitingTime)
1044            )
1045            )
1046        ) {
1047            $wartezeitInSeconds = $process->getWaitedSeconds();
1048            $wartezeitInSeconds = $wartezeitInSeconds > 0 ? $wartezeitInSeconds : 0;
1049
1050            // Convert total seconds into HH:MM:SS format
1051            $hours = intdiv($wartezeitInSeconds, 3600);
1052            $minutes = intdiv($wartezeitInSeconds % 3600, 60);
1053            $seconds = $wartezeitInSeconds % 60;
1054
1055            $data['waiting_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1056        }
1057
1058        $this->addValues($data);
1059    }
1060
1061
1062    protected function addValuesWayTimeData($process)
1063    {
1064        $data = array();
1065        if ($process['status'] == 'processing') {
1066            $wayTimeInSeconds = max(0, (int) $process->getWaySeconds());
1067            $hours = intdiv($wayTimeInSeconds, 3600);
1068            $minutes = intdiv($wayTimeInSeconds % 3600, 60);
1069            $seconds = $wayTimeInSeconds % 60;
1070            $data['way_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1071        }
1072        $this->addValues($data);
1073    }
1074
1075    protected function addValuesWasMissed($process)
1076    {
1077        $data = [
1078            'wasMissed' => $process->wasMissed ? 1 : 0,
1079        ];
1080
1081        $this->addValues($data);
1082        return $this;
1083    }
1084
1085    protected function addValuesPriority($process)
1086    {
1087        $data = [
1088            'priority' => $process->priority,
1089        ];
1090
1091        $this->addValues($data);
1092        return $this;
1093    }
1094
1095    protected function addValuesExternalUserId($process)
1096    {
1097        $data = [
1098            'external_user_id' => $process->externalUserId,
1099        ];
1100
1101        $this->addValues($data);
1102        return $this;
1103    }
1104
1105    public function postProcess($data)
1106    {
1107        $data[$this->getPrefixed("appointments__0__date")] =
1108            strtotime($data[$this->getPrefixed("appointments__0__date")]);
1109        if ('00:00:00' != $data[$this->getPrefixed("queue__callTime")]) {
1110            $time = explode(':', $data[$this->getPrefixed("queue__callTime")]);
1111            $data[$this->getPrefixed("queue__callTime")] = (new \DateTimeImmutable())
1112                ->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1113                ->setTime($time[0], $time[1], $time[2])
1114                ->getTimestamp();
1115        } else {
1116            $data[$this->getPrefixed("queue__callTime")] = 0;
1117        }
1118        if ('00:00:00' != $data[$this->getPrefixed("queue__lastCallTime")]) {
1119            $time = explode(':', $data[$this->getPrefixed("queue__lastCallTime")]);
1120            $data[$this->getPrefixed("queue__lastCallTime")] = (new \DateTimeImmutable())
1121                ->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1122                ->setTime($time[0], $time[1], $time[2])
1123                ->getTimestamp();
1124        } else {
1125            $data[$this->getPrefixed("queue__lastCallTime")] = 0;
1126        }
1127        $data[$this->getPrefixed("queue__arrivalTime")] =
1128            strtotime($data[$this->getPrefixed("queue__arrivalTime")]);
1129        if (
1130            isset($data[$this->getPrefixed('scope__provider__data')])
1131            && $data[$this->getPrefixed('scope__provider__data')]
1132        ) {
1133            $data[$this->getPrefixed('scope__provider__data')] =
1134                json_decode($data[$this->getPrefixed('scope__provider__data')], true);
1135        }
1136        if (isset($data[$this->getPrefixed('__clientsCount')])) {
1137            $clientsCount = $data[$this->getPrefixed('__clientsCount')];
1138            unset($data[$this->getPrefixed('__clientsCount')]);
1139            while (--$clientsCount > 0) {
1140                $data[$this->getPrefixed('clients__' . $clientsCount . '__familyName')] = 'Unbekannt';
1141            }
1142        }
1143        $data[$this->getPrefixed("lastChange")] =
1144            (new \DateTimeImmutable($data[$this->getPrefixed("lastChange")] .
1145                \BO\Zmsdb\Connection\Select::$connectionTimezone))->getTimestamp();
1146        return $data;
1147    }
1148
1149    public function removeDuplicates()
1150    {
1151        $this->query->groupBy('process.BuergerID');
1152        return $this;
1153    }
1154
1155    protected function addRequiredJoins()
1156    {
1157        if ($this->shouldLoadEntity('processuser')) {
1158            $this->leftJoin(
1159                new Alias(Useraccount::TABLE, 'processuser'),
1160                'process.NutzerID',
1161                '=',
1162                'processuser.NutzerID'
1163            );
1164        }
1165
1166        if ($this->shouldLoadEntity('processscope')) {
1167            $this->leftJoin(
1168                new Alias(Scope::TABLE, 'processscope'),
1169                'process.StandortID',
1170                '=',
1171                'processscope.StandortID'
1172            );
1173        }
1174    }
1175
1176    public function addConditionExternalUserId(string $externalUserId)
1177    {
1178        $this->query->where('process.external_user_id', '=', $externalUserId);
1179        return $this;
1180    }
1181}