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