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