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