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