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