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