Lines 95.66% 243 / 254
Functions and Methods 80.00% 12 / 15
Classes and Traits 0.00% 0 / 1
Name Lines Functions and Methods CRAP Classes and Traits
ProcessSearch 95.66% 243 / 254 80.00% 12 / 15 62 0.00% 0 / 1
 mapSearchRowToProcess 100.00% 29 / 29 100.00% 1 / 1 6
 readSearch 100.00% 19 / 19 100.00% 1 / 1 2
 readSearchCount 100.00% 8 / 8 100.00% 1 / 1 1
 buildSearchQuery 89.79% 44 / 49 0.00% 0 / 1 10.11
 extractSearchQuery 100.00% 4 / 4 100.00% 1 / 1 3
 addSearchConditions 100.00% 9 / 9 100.00% 1 / 1 1
 addBasicSearchConditions 100.00% 20 / 20 100.00% 1 / 1 10
 addAdditionalSearchConditions 100.00% 20 / 20 100.00% 1 / 1 11
 resolveSearchReferences 100.00% 8 / 8 100.00% 1 / 1 2
 buildCombinedSearchSql 100.00% 31 / 31 100.00% 1 / 1 1
 shouldDenyHistory 100.00% 4 / 4 100.00% 1 / 1 4
 readScopeIdsParameter 90.00% 9 / 10 0.00% 0 / 1 3.01
 wrapCombinedSearchSql 100.00% 9 / 9 100.00% 1 / 1 3
 buildCombinedSearchCountSql 100.00% 11 / 11 100.00% 1 / 1 1
 getHistoryAppointmentFrom 78.26% 18 / 23 0.00% 0 / 1 4.16
1<?php
2
3namespace BO\Zmsbackend\ProcessSearch\Service;
4
5use BO\Zmsbackend\ProcessSearch\Repository\ProcessSearch as ProcessSearchRepository;
6use BO\Zmsbackend\Query\Base as QueryBase;
7use BO\Zmsentities\Collection\ProcessList as Collection;
8use BO\Zmsentities\Process as Entity;
9use BO\Zmsbackend\Config\Service\Config as ConfigService;
10
11class ProcessSearch extends \BO\Zmsbackend\Base
12{
13    private const int DEFAULT_HISTORY_DAYS = 90;
14
15    public function mapSearchRowToProcess(array $row): Entity
16    {
17        return new Entity([
18            'id' => (int) $row['process_id'],
19            'displayNumber' => (string) ($row['display_number'] ?? ''),
20
21            'clients__0__familyName' => (string) ($row['citizen_name'] ?? ''),
22            'clients__0__telephone' => (string) ($row['telephone'] ?? ''),
23            'clients__0__email' => (string) ($row['citizen_email'] ?? ''),
24
25            'amendment' => (string) ($row['amendment'] ?? ''),
26
27            'appointments__0__date' => !empty($row['appointment_at'])
28                ? strtotime($row['appointment_at'])
29                : 0,
30
31            'appointments__0__scope__id' => (int) $row['scope_id'],
32
33            'createTimestamp' => ($row['source'] ?? '') === 'active'
34                ? (int) ($row['booked_at'] ?? 0)
35                : (!empty($row['booked_at'])
36                    ? strtotime($row['booked_at'])
37                    : 0),
38
39            'queue__callTime' => !empty($row['called_at'])
40                ? strtotime($row['called_at'])
41                : 0,
42
43            'scope__id' => (int) $row['scope_id'],
44            'scope__shortName' => (string) ($row['location_name'] ?? ''),
45            'scope__contact__name' => (string) ($row['provider_name'] ?? ''),
46
47            'status' => (string) $row['technical_status'],
48            'source' => (string) $row['source'],
49            'appointmentStatus' => (string) $row['appointment_status'],
50            'finalizedAt' => !empty($row['finalized_at'])
51                ? strtotime((string) $row['finalized_at'])
52                : 0,
53        ]);
54    }
55
56    public function readSearch(
57        array $parameter,
58        int $resolveReferences = 0,
59        int $limit = 100,
60        int $offset = 0
61    ): Collection {
62        $combined = $this->buildCombinedSearchSql(
63            $parameter,
64            $this->getHistoryAppointmentFrom(),
65            (int) $limit,
66            (int) $offset
67        );
68
69        $rows = $this->fetchAll(
70            $combined['sql'],
71            $combined['parameters']
72        );
73
74        $processList = new Collection();
75
76        foreach ($rows as $row) {
77            $entity = $this->mapSearchRowToProcess($row);
78
79            $entity = $this->resolveSearchReferences(
80                $entity,
81                $resolveReferences
82            );
83
84            $processList->addEntity($entity);
85        }
86
87        return $processList;
88    }
89
90    public function readSearchCount(array $parameter): int
91    {
92        $combined = $this->buildCombinedSearchCountSql(
93            $parameter,
94            $this->getHistoryAppointmentFrom()
95        );
96
97        return (int) $this->fetchValue(
98            $combined['sql'],
99            $combined['parameters']
100        );
101    }
102
103    protected function buildSearchQuery(
104        array $parameter
105    ): ProcessSearchRepository {
106        $query = new ProcessSearchRepository(QueryBase::SELECT);
107
108        $query
109            ->addConditionAssigned()
110            ->addConditionIgnoreSlots()
111            ->addConditionActiveSearchStatuses();
112
113        if (!empty($parameter['upcomingOnly'])) {
114            $now = class_exists('\App') && isset(\App::$now)
115                ? \App::$now
116                : new \DateTimeImmutable(
117                    'now',
118                    new \DateTimeZone('Europe/Berlin')
119                );
120
121            $query->addConditionUpcomingOnly($now);
122        }
123
124        if (array_key_exists('scopeIds', $parameter)) {
125            $scopeIds = is_array($parameter['scopeIds'])
126                ? $parameter['scopeIds']
127                : array_map(
128                    'intval',
129                    explode(',', (string) $parameter['scopeIds'])
130                );
131
132            $scopeIds = array_values(
133                array_filter($scopeIds)
134            );
135
136            $query->addConditionScopeIds($scopeIds);
137        }
138
139        if (isset($parameter['query'])) {
140            if (preg_match('#^\d+$#', $parameter['query'])) {
141                $query->addConditionProcessIdOrSearch(
142                    $parameter['query']
143                );
144            } else {
145                $query->addConditionSearch(
146                    $parameter['query']
147                );
148            }
149
150            unset($parameter['query']);
151        }
152
153        if (count($parameter)) {
154            foreach (
155                [
156                    'upcomingOnly',
157                    'scopeIds',
158                    'page',
159                    'limit',
160                    'offset',
161                    'includePast',
162                    'denyHistory',
163                ] as $reservedKey
164            ) {
165                unset($parameter[$reservedKey]);
166            }
167
168            $query = $this->addSearchConditions(
169                $query,
170                $parameter
171            );
172        }
173
174        return $query;
175    }
176
177    protected function extractSearchQuery(array $parameter): ?string
178    {
179        if (!isset($parameter['query'])) {
180            return null;
181        }
182
183        $queryString = trim((string) $parameter['query']);
184
185        return $queryString !== '' ? $queryString : null;
186    }
187
188    protected function addSearchConditions(
189        ProcessSearchRepository $query,
190        array $parameter
191    ): ProcessSearchRepository {
192        $this->addBasicSearchConditions(
193            $query,
194            $parameter
195        );
196
197        $this->addAdditionalSearchConditions(
198            $query,
199            $parameter
200        );
201
202        return $query;
203    }
204
205    private function addBasicSearchConditions(
206        ProcessSearchRepository $query,
207        array $parameter
208    ): void {
209        if (isset($parameter['processId']) && $parameter['processId']) {
210            $query->addConditionProcessId(
211                $parameter['processId']
212            );
213        }
214
215        if (isset($parameter['name']) && $parameter['name']) {
216            $exact = isset($parameter['exact'])
217                ? $parameter['exact']
218                : false;
219
220            $query->addConditionName(
221                $parameter['name'],
222                $exact
223            );
224        }
225
226        if (isset($parameter['amendment']) && $parameter['amendment']) {
227            $query->addConditionAmendment(
228                $parameter['amendment']
229            );
230        }
231
232        if (isset($parameter['scopeId']) && $parameter['scopeId']) {
233            $query->addConditionScopeId(
234                $parameter['scopeId']
235            );
236        }
237    }
238
239    private function addAdditionalSearchConditions(
240        ProcessSearchRepository $query,
241        array $parameter
242    ): void {
243        if (isset($parameter['authKey']) && $parameter['authKey']) {
244            $query->addConditionAuthKey(
245                $parameter['authKey']
246            );
247        }
248
249        if (isset($parameter['requestId']) && $parameter['requestId']) {
250            $query->addConditionRequestId(
251                $parameter['requestId']
252            );
253        }
254
255        if (isset($parameter['provider']) && $parameter['provider']) {
256            $query->addConditionScopeNameSearch(
257                $parameter['provider']
258            );
259        }
260
261        if (isset($parameter['service']) && $parameter['service']) {
262            $query->addConditionServiceNameSearch(
263                $parameter['service']
264            );
265        }
266
267        if (isset($parameter['date']) && $parameter['date']) {
268            $query->addConditionDate(
269                $parameter['date']
270            );
271        }
272    }
273
274    protected function resolveSearchReferences(
275        Entity $entity,
276        int $resolveReferences
277    ): Entity {
278        if (($entity->source ?? 'active') === 'history') {
279            return $entity;
280        }
281
282        (new \BO\Zmsbackend\Process\Service\Process())
283            ->readResolvedReferences(
284                $entity,
285                $resolveReferences
286            );
287
288        return $entity;
289    }
290
291    protected function buildCombinedSearchSql(
292        array $parameter,
293        ?\DateTimeInterface $appointmentFrom = null,
294        ?int $limit = null,
295        int $offset = 0
296    ): array {
297        $searchRepository = $this->buildSearchQuery($parameter);
298        $searchRepository->addCombinedActiveProjection();
299
300        $historyParameters = [];
301        $historySql = $searchRepository->getHistorySelectSql(
302            $this->readScopeIdsParameter($parameter),
303            $appointmentFrom,
304            $this->extractSearchQuery($parameter),
305            $historyParameters,
306            $parameter['date'] ?? null,
307            $parameter['provider'] ?? null,
308            $parameter['service'] ?? null,
309            [
310                'name' => $parameter['name'] ?? null,
311                'exact' => !empty($parameter['exact']),
312                'amendment' => $parameter['amendment'] ?? null,
313                'processId' => $parameter['processId'] ?? null,
314                'scopeId' => $parameter['scopeId'] ?? null,
315                'denyHistory' => $this->shouldDenyHistory($parameter),
316            ]
317        );
318
319        return [
320            'sql' => $this->wrapCombinedSearchSql(
321                $searchRepository->getCombinedSelectSql($historySql),
322                $limit,
323                $offset
324            ),
325            'parameters' => array_merge(
326                $searchRepository->getParameters(),
327                $historyParameters
328            ),
329        ];
330    }
331
332    private function shouldDenyHistory(array $parameter): bool
333    {
334        return !empty($parameter['denyHistory'])
335            || !empty($parameter['authKey'])
336            || !empty($parameter['requestId'])
337            || !empty($parameter['upcomingOnly']);
338    }
339
340    private function readScopeIdsParameter(array $parameter): ?array
341    {
342        if (!array_key_exists('scopeIds', $parameter)) {
343            return null;
344        }
345
346        $scopeIds = is_array($parameter['scopeIds'])
347            ? $parameter['scopeIds']
348            : explode(',', (string) $parameter['scopeIds']);
349
350        return array_values(
351            array_filter(
352                array_map('intval', $scopeIds)
353            )
354        );
355    }
356
357    private function wrapCombinedSearchSql(
358        string $combinedSql,
359        ?int $limit,
360        int $offset
361    ): string {
362        $sql = '
363            SELECT *
364            FROM (
365                ' . $combinedSql . '
366            ) combined
367            ORDER BY
368                combined.appointment_at DESC,
369                combined.source_record_id DESC,
370                combined.source ASC
371        ';
372
373        if ($limit === null) {
374            return $sql;
375        }
376
377        $sql .= ' LIMIT ' . max(0, $limit);
378
379        if ($offset > 0) {
380            $sql .= ' OFFSET ' . max(0, $offset);
381        }
382
383        return $sql;
384    }
385
386    protected function buildCombinedSearchCountSql(
387        array $parameter,
388        ?\DateTimeInterface $appointmentFrom = null
389    ): array {
390        $combined = $this->buildCombinedSearchSql(
391            $parameter,
392            $appointmentFrom
393        );
394
395        $sql = '
396            SELECT COUNT(*) AS total_count
397            FROM (
398                ' . $combined['sql'] . '
399            ) combined_count
400        ';
401
402        return [
403            'sql' => $sql,
404            'parameters' => $combined['parameters'],
405        ];
406    }
407
408    protected function getHistoryAppointmentFrom(): \DateTimeImmutable
409    {
410        $now = class_exists('\App') && isset(\App::$now)
411            ? \App::$now
412            : new \DateTimeImmutable(
413                'now',
414                new \DateTimeZone('Europe/Berlin')
415            );
416
417        $config = (new ConfigService())->readEntity();
418
419        $retentionDays = filter_var(
420            $config->getPreference(
421                'processSearchHistory',
422                'deleteOlderThanDays'
423            ),
424            FILTER_VALIDATE_INT,
425            [
426                'options' => [
427                    'min_range' => 1,
428                ],
429            ]
430        );
431
432        if ($retentionDays === false) {
433            $retentionDays = self::DEFAULT_HISTORY_DAYS;
434        }
435
436        return \DateTimeImmutable::createFromInterface($now)
437            ->modify('-' . $retentionDays . ' days');
438    }
439}