Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
97.36%
332 / 341
86.36%
19 / 22
 CRAP
0.00%
0 / 1
ProcessSearch
97.36%
332 / 341
86.36%
19 / 22
 59
0.00%
0 / 1
 getEntityMapping
100.00%
9 / 9
100.00%
1 / 1
 1
 getHistorySelectSql
100.00%
39 / 39
100.00%
1 / 1
 2
 getHistoryBaseSelectSql
100.00%
2 / 2
100.00%
1 / 1
 1
 addHistoryScopeCondition
100.00%
13 / 13
100.00%
1 / 1
 3
 addHistoryAppointmentFromCondition
80.00%
4 / 5
0.00%
0 / 1
 2.03
 addHistorySearchQueryCondition
100.00%
8 / 8
100.00%
1 / 1
 4
 addHistoryDateCondition
100.00%
14 / 14
100.00%
1 / 1
 3
 addHistoryProviderCondition
100.00%
11 / 11
100.00%
1 / 1
 3
 addHistoryServiceCondition
100.00%
9 / 9
100.00%
1 / 1
 3
 addHistoryFilterConditions
100.00%
22 / 22
100.00%
1 / 1
 2
 addHistoryNameFilter
100.00%
14 / 14
100.00%
1 / 1
 4
 addHistoryAmendmentFilter
100.00%
9 / 9
100.00%
1 / 1
 3
 addHistoryProcessIdFilter
100.00%
5 / 5
100.00%
1 / 1
 3
 addHistoryScopeIdFilter
100.00%
5 / 5
100.00%
1 / 1
 3
 buildHistorySearchCondition
91.18%
31 / 34
0.00%
0 / 1
 7.03
 buildHistorySearchTermCondition
100.00%
39 / 39
100.00%
1 / 1
 7
 buildHistoryNameCondition
87.80%
36 / 41
0.00%
0 / 1
 3.02
 addHistorySearchParameter
100.00%
2 / 2
100.00%
1 / 1
 1
 escapeHistoryLikeValue
100.00%
5 / 5
100.00%
1 / 1
 1
 addConditionActiveSearchStatuses
100.00%
5 / 5
100.00%
1 / 1
 1
 addCombinedActiveProjection
100.00%
47 / 47
100.00%
1 / 1
 1
 getCombinedSelectSql
100.00%
3 / 3
100.00%
1 / 1
 1
1<?php
2
3namespace BO\Zmsbackend\ProcessSearch\Repository;
4
5class ProcessSearch extends \BO\Zmsbackend\Process\Repository\Process
6{
7    const string ALIAS = 'process';
8
9    private const array ACTIVE_SEARCH_STATUSES = [
10        'reserved',
11        'preconfirmed',
12        'confirmed',
13        'queued',
14        'called',
15        'processing',
16        'pending',
17        'parked',
18        'missed',
19    ];
20
21    #[\Override]
22    public function getEntityMapping()
23    {
24        $mapping = parent::getEntityMapping();
25
26        $mapping['source'] = self::expression(
27            '"active"'
28        );
29
30        $mapping['appointmentStatus'] = self::expression(
31            'CASE
32                WHEN process.status = "missed"
33                    THEN "missed"
34                ELSE "planned"
35            END'
36        );
37
38        return $mapping;
39    }
40
41    public function getHistorySelectSql(
42        ?array $scopeIds = null,
43        ?\DateTimeInterface $appointmentFrom = null,
44        ?string $searchQuery = null,
45        array &$parameters = [],
46        ?string $date = null,
47        ?string $provider = null,
48        ?string $service = null,
49        array $filters = []
50    ): string {
51        $conditions = [];
52
53        $this->addHistoryScopeCondition(
54            $conditions,
55            $scopeIds
56        );
57
58        $this->addHistoryAppointmentFromCondition(
59            $conditions,
60            $parameters,
61            $appointmentFrom
62        );
63
64        $this->addHistorySearchQueryCondition(
65            $conditions,
66            $parameters,
67            $searchQuery
68        );
69
70        $this->addHistoryDateCondition(
71            $conditions,
72            $parameters,
73            $date
74        );
75
76        $this->addHistoryProviderCondition(
77            $conditions,
78            $parameters,
79            $provider
80        );
81
82        $this->addHistoryServiceCondition(
83            $conditions,
84            $parameters,
85            $service
86        );
87
88        $this->addHistoryFilterConditions(
89            $conditions,
90            $parameters,
91            $filters
92        );
93
94        $sql = $this->getHistoryBaseSelectSql();
95
96        if ($conditions) {
97            $sql .= ' WHERE ' . implode(' AND ', $conditions);
98        }
99
100        return $sql;
101    }
102
103    private function getHistoryBaseSelectSql(): string
104    {
105        return '
106            SELECT
107                history.process_id AS process_id,
108                history.display_number AS display_number,
109                history.citizen_name AS citizen_name,
110                history.telephone AS telephone,
111                history.citizen_email AS citizen_email,
112                history.amendment AS amendment,
113                history.appointment_at AS appointment_at,
114                history.booked_at AS booked_at,
115                history.called_at AS called_at,
116                history.scope_id AS scope_id,
117                history.location_name AS location_name,
118                history.provider_name AS provider_name,
119                CASE
120                    WHEN history.status = "completed" THEN "finished"
121                    WHEN history.status = "missed" THEN "missed"
122                    WHEN history.status = "cancelled_citizen" THEN "deleted"
123                    WHEN history.status = "cancelled_staff" THEN "blocked"
124                    ELSE NULL
125                END AS technical_status,
126                CASE
127                    WHEN history.status = "completed" THEN "completed"
128                    WHEN history.status = "missed" THEN "missed"
129                    WHEN history.status = "cancelled_citizen" THEN "cancelled_citizen"
130                    WHEN history.status = "cancelled_staff" THEN "cancelled_staff"
131                    ELSE NULL
132                END AS appointment_status,
133                history.finalized_at AS finalized_at,
134                "history" AS source,
135                history.id AS source_record_id
136            FROM process_search_history history
137        ';
138    }
139
140    private function addHistoryScopeCondition(
141        array &$conditions,
142        ?array $scopeIds
143    ): void {
144        if ($scopeIds === null) {
145            return;
146        }
147
148        $scopeIds = array_values(
149            array_unique(
150                array_map('intval', $scopeIds)
151            )
152        );
153
154        if (!$scopeIds) {
155            $conditions[] = '1 = 0';
156            return;
157        }
158
159        $conditions[] = 'history.scope_id IN ('
160            . implode(',', $scopeIds)
161            . ')';
162    }
163
164    private function addHistoryAppointmentFromCondition(
165        array &$conditions,
166        array &$parameters,
167        ?\DateTimeInterface $appointmentFrom
168    ): void {
169        if ($appointmentFrom === null) {
170            return;
171        }
172
173        $conditions[] = 'history.appointment_at >= ?';
174
175        $parameters[] = $appointmentFrom
176            ->format('Y-m-d H:i:s');
177    }
178
179    private function addHistorySearchQueryCondition(
180        array &$conditions,
181        array &$parameters,
182        ?string $searchQuery
183    ): void {
184        if ($searchQuery === null || trim($searchQuery) === '') {
185            return;
186        }
187
188        $searchCondition = $this->buildHistorySearchCondition(
189            $searchQuery,
190            $parameters
191        );
192
193        if ($searchCondition !== '') {
194            $conditions[] = $searchCondition;
195        }
196    }
197
198    private function addHistoryDateCondition(
199        array &$conditions,
200        array &$parameters,
201        ?string $date
202    ): void {
203        if ($date === null || trim($date) === '') {
204            return;
205        }
206
207        $dateFrom = new \DateTimeImmutable(
208            trim($date)
209        );
210
211        $conditions[] = '
212            history.appointment_at >= ?
213            AND history.appointment_at < ?
214        ';
215
216        $parameters[] = $dateFrom
217            ->setTime(0, 0, 0)
218            ->format('Y-m-d H:i:s');
219
220        $parameters[] = $dateFrom
221            ->modify('+1 day')
222            ->setTime(0, 0, 0)
223            ->format('Y-m-d H:i:s');
224    }
225
226    private function addHistoryProviderCondition(
227        array &$conditions,
228        array &$parameters,
229        ?string $provider
230    ): void {
231        if ($provider === null || trim($provider) === '') {
232            return;
233        }
234
235        $provider = '%'
236            . $this->escapeHistoryLikeValue(
237                trim($provider)
238            )
239            . '%';
240
241        $conditions[] = '(
242            history.location_name LIKE ?
243            OR history.provider_name LIKE ?
244        )';
245
246        $parameters[] = $provider;
247        $parameters[] = $provider;
248    }
249
250    private function addHistoryServiceCondition(
251        array &$conditions,
252        array &$parameters,
253        ?string $service
254    ): void {
255        if ($service === null || trim($service) === '') {
256            return;
257        }
258
259        $conditions[] = '
260            history.services LIKE ?
261        ';
262
263        $parameters[] = '%'
264            . $this->escapeHistoryLikeValue(
265                trim($service)
266            )
267            . '%';
268    }
269
270    private function addHistoryFilterConditions(
271        array &$conditions,
272        array &$parameters,
273        array $filters
274    ): void {
275        if (!empty($filters['denyHistory'])) {
276            $conditions[] = '1 = 0';
277        }
278
279        $this->addHistoryNameFilter(
280            $conditions,
281            $parameters,
282            $filters
283        );
284
285        $this->addHistoryAmendmentFilter(
286            $conditions,
287            $parameters,
288            $filters
289        );
290
291        $this->addHistoryProcessIdFilter(
292            $conditions,
293            $parameters,
294            $filters
295        );
296
297        $this->addHistoryScopeIdFilter(
298            $conditions,
299            $parameters,
300            $filters
301        );
302    }
303
304    private function addHistoryNameFilter(
305        array &$conditions,
306        array &$parameters,
307        array $filters
308    ): void {
309        if (
310            !isset($filters['name'])
311            || trim((string) $filters['name']) === ''
312        ) {
313            return;
314        }
315
316        $name = trim(
317            (string) $filters['name']
318        );
319
320        if (!empty($filters['exact'])) {
321            $conditions[] = 'history.citizen_name = ?';
322            $parameters[] = $name;
323            return;
324        }
325
326        $conditions[] = 'history.citizen_name LIKE ?';
327
328        $parameters[] = '%'
329            . $this->escapeHistoryLikeValue($name)
330            . '%';
331    }
332
333    private function addHistoryAmendmentFilter(
334        array &$conditions,
335        array &$parameters,
336        array $filters
337    ): void {
338        if (
339            !isset($filters['amendment'])
340            || trim((string) $filters['amendment']) === ''
341        ) {
342            return;
343        }
344
345        $conditions[] = 'history.amendment LIKE ?';
346
347        $parameters[] = '%'
348            . $this->escapeHistoryLikeValue(
349                trim((string) $filters['amendment'])
350            )
351            . '%';
352    }
353
354    private function addHistoryProcessIdFilter(
355        array &$conditions,
356        array &$parameters,
357        array $filters
358    ): void {
359        if (
360            !isset($filters['processId'])
361            || !$filters['processId']
362        ) {
363            return;
364        }
365
366        $conditions[] = 'history.process_id = ?';
367        $parameters[] = (int) $filters['processId'];
368    }
369
370    private function addHistoryScopeIdFilter(
371        array &$conditions,
372        array &$parameters,
373        array $filters
374    ): void {
375        if (
376            !isset($filters['scopeId'])
377            || !$filters['scopeId']
378        ) {
379            return;
380        }
381
382        $conditions[] = 'history.scope_id = ?';
383        $parameters[] = (int) $filters['scopeId'];
384    }
385
386    private function buildHistorySearchCondition(
387        string $queryString,
388        array &$parameters
389    ): string {
390        $queryString = trim($queryString);
391
392        if ($queryString === '') {
393            return '';
394        }
395
396        preg_match_all(
397            '/"([^"]+)"|(\S+)/u',
398            $queryString,
399            $matches,
400            PREG_SET_ORDER
401        );
402
403        $terms = [];
404
405        foreach ($matches as $match) {
406            $value = trim(
407                $match[1] !== ''
408                    ? $match[1]
409                    : $match[2]
410            );
411
412            if ($value === '') {
413                continue;
414            }
415
416            $terms[] = [
417                'value' => $value,
418                'quoted' => $match[1] !== '',
419            ];
420        }
421
422        if (!$terms) {
423            return '';
424        }
425
426        $conditions = [];
427        $singleTerm = count($terms) === 1;
428
429        foreach ($terms as $term) {
430            $conditions[] = $this->buildHistorySearchTermCondition(
431                $term['value'],
432                $term['quoted'],
433                $parameters,
434                $singleTerm
435            );
436        }
437
438        return '(' . implode(' AND ', $conditions) . ')';
439    }
440
441    private function buildHistorySearchTermCondition(
442        string $term,
443        bool $quoted,
444        array &$parameters,
445        bool $singleTerm = false
446    ): string {
447        $escapedTerm = $this->escapeHistoryLikeValue($term);
448        $contains = '%' . $escapedTerm . '%';
449
450        $conditions = [];
451        $isNumeric = preg_match('#^\d+$#', $term);
452
453        if ($singleTerm && $isNumeric) {
454            $conditions[] = 'history.process_id = '
455                . $this->addHistorySearchParameter(
456                    $parameters,
457                    (int) $term
458                );
459        }
460
461        if ($quoted) {
462            $conditions[] = $this->buildHistoryNameCondition(
463                $term,
464                $parameters,
465                true
466            );
467        } elseif (!$isNumeric && mb_strlen($term) <= 3) {
468            $conditions[] = $this->buildHistoryNameCondition(
469                $term,
470                $parameters,
471                true,
472                true
473            );
474        } else {
475            $conditions[] = 'history.citizen_name LIKE '
476                . $this->addHistorySearchParameter(
477                    $parameters,
478                    $contains
479                );
480        }
481
482        foreach (
483            [
484                'history.citizen_email',
485                'history.telephone',
486                'history.display_number',
487            ] as $column
488        ) {
489            $conditions[] = $column . ' LIKE '
490                . $this->addHistorySearchParameter(
491                    $parameters,
492                    $contains
493                );
494        }
495
496        return '(' . implode(' OR ', $conditions) . ')';
497    }
498
499    private function buildHistoryNameCondition(
500        string $term,
501        array &$parameters,
502        bool $wordBoundaryOnly = false,
503        bool $includeWordPrefix = false
504    ): string {
505        $escaped = $this->escapeHistoryLikeValue($term);
506
507        $conditions = [
508            'history.citizen_name LIKE '
509                . $this->addHistorySearchParameter(
510                    $parameters,
511                    $escaped . ' %'
512                ),
513
514            'history.citizen_name LIKE '
515                . $this->addHistorySearchParameter(
516                    $parameters,
517                    '% ' . $escaped
518                ),
519
520            'history.citizen_name LIKE '
521                . $this->addHistorySearchParameter(
522                    $parameters,
523                    '% ' . $escaped . ' %'
524                ),
525
526            'history.citizen_name = '
527                . $this->addHistorySearchParameter(
528                    $parameters,
529                    $term
530                ),
531        ];
532
533        if ($wordBoundaryOnly) {
534            if ($includeWordPrefix) {
535                $conditions[] = 'history.citizen_name LIKE '
536                    . $this->addHistorySearchParameter(
537                        $parameters,
538                        $escaped . '%'
539                    );
540
541                $conditions[] = 'history.citizen_name LIKE '
542                    . $this->addHistorySearchParameter(
543                        $parameters,
544                        '% ' . $escaped . '%'
545                    );
546            }
547        } else {
548            $conditions[] = 'history.citizen_name LIKE '
549                . $this->addHistorySearchParameter(
550                    $parameters,
551                    '%' . $escaped . '%'
552                );
553        }
554
555        return '(' . implode(' OR ', $conditions) . ')';
556    }
557
558    private function addHistorySearchParameter(
559        array &$parameters,
560        mixed $value
561    ): string {
562        $parameters[] = $value;
563
564        return '?';
565    }
566
567    private function escapeHistoryLikeValue(string $value): string
568    {
569        return str_replace(
570            ['\\', '%', '_'],
571            ['\\\\', '\\%', '\\_'],
572            $value
573        );
574    }
575
576    public function addConditionActiveSearchStatuses(): self
577    {
578        $this->query->whereIn(
579            'process.status',
580            self::ACTIVE_SEARCH_STATUSES
581        );
582
583        return $this;
584    }
585
586    public function addCombinedActiveProjection(): self
587    {
588        $this->leftJoin(
589            new \BO\Zmsbackend\Query\Alias(
590                \BO\Zmsbackend\Query\Scope::TABLE,
591                'search_scope'
592            ),
593            self::expression(
594                'IF(
595                    `process`.`AbholortID`,
596                    `process`.`AbholortID`,
597                    `process`.`StandortID`
598                )'
599            ),
600            '=',
601            'search_scope.StandortID'
602        );
603
604        $this->leftJoin(
605            new \BO\Zmsbackend\Query\Alias(
606                \BO\Zmsbackend\Provider\Repository\Provider::TABLE,
607                'search_scope_provider'
608            ),
609            self::expression(
610                'search_scope.InfoDienstleisterID = search_scope_provider.id
611                AND search_scope.source = search_scope_provider.source'
612            )
613        );
614
615        $mapping = $this->getEntityMapping();
616
617        $this->query->resetSelect();
618
619        $this->query->select([
620            'process_id' => $mapping['id'],
621
622            'display_number' => $mapping['displayNumber'],
623
624            'citizen_name' => $mapping['clients__0__familyName'],
625
626            'telephone' => $mapping['clients__0__telephone'],
627
628            'citizen_email' => $mapping['clients__0__email'],
629
630            'amendment' => $mapping['amendment'],
631
632            'appointment_at' => $mapping['appointments__0__date'],
633
634            'booked_at' => 'process.IPTimeStamp',
635
636            'called_at' => self::expression(
637                'CASE
638                    WHEN process.aufrufzeit IS NULL
639                        OR process.aufrufzeit = "00:00:00"
640                        THEN NULL
641                    ELSE CONCAT(
642                        process.Datum,
643                        " ",
644                        process.aufrufzeit
645                    )
646                END'
647            ),
648
649            'scope_id' => $mapping['scope__id'],
650
651            'location_name' => 'search_scope.standortkuerzel',
652
653            'provider_name' => 'search_scope_provider.name',
654
655            'technical_status' => $mapping['status'],
656
657            'appointment_status' => $mapping['appointmentStatus'],
658
659            'finalized_at' => self::expression('NULL'),
660
661            'source' => $mapping['source'],
662
663            'source_record_id' => $mapping['id'],
664        ]);
665
666        return $this;
667    }
668
669    public function getCombinedSelectSql(
670        string $historySql
671    ): string {
672        return $this->getSql()
673            . "\nUNION ALL\n"
674            . $historySql;
675    }
676}