Code Coverage for BO\Zmsbackend\ProcessSearch\Repository\ProcessSearch

Lines 83.79% 1024 / 1222

Methods 79.16% 95 / 120

Classes 0.00% 0 / 1

Name					CRAP
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.17%	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
[BO\Zmsbackend\Process\Repository\Process] getQueryNewProcessId	100.00%	5 / 5	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] getLockProcessId	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] getLockAssignedWorkstationId	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addJoin	100.00%	6 / 6	100.00%	1 / 1	3
[BO\Zmsbackend\Process\Repository\Process] addJoinAvailability	100.00%	6 / 6	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addJoinScope	100.00%	11 / 11	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionDate	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionDisplayNumber	0.00%	0 / 2	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] calculateStatus	0.00%	0 / 33	0.00%	0 / 1	506
[BO\Zmsbackend\Process\Repository\Process] addCountValue	100.00%	4 / 4	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionHasTelephone	0.00%	0 / 6	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessDeleteInterval	100.00%	11 / 11	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessDeleteBeforeDate	100.00%	11 / 11	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessExpiredIPTimeStamp	100.00%	5 / 5	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessReminderInterval	0.00%	0 / 7	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessMailReminder	100.00%	44 / 44	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessId	100.00%	7 / 7	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessIdFollow	100.00%	6 / 6	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionIgnoreSlots	100.00%	6 / 6	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionScopeId	100.00%	6 / 6	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionScopeIds	45.45%	5 / 11	0.00%	0 / 1	4.46
[BO\Zmsbackend\Process\Repository\Process] addConditionQueueNumber	100.00%	4 / 4	100.00%	1 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionWorkstationId	100.00%	5 / 5	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionTime	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionTimeframe	0.00%	0 / 6	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionAuthKey	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionAssigned	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionStatus	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionIsReserved	100.00%	12 / 12	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionSearch	91.66%	22 / 24	0.00%	0 / 1	4.01
[BO\Zmsbackend\Process\Repository\Process] addConditionProcessIdOrSearch	96.66%	29 / 30	0.00%	0 / 1	3
[BO\Zmsbackend\Process\Repository\Process] appendGeneralSearchTermGroup	100.00%	18 / 18	100.00%	1 / 1	5
[BO\Zmsbackend\Process\Repository\Process] addConditionUpcomingOnly	100.00%	12 / 12	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addOrderByAppointmentDate	0.00%	0 / 4	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addOrderBySearchRelevance	0.00%	0 / 38	0.00%	0 / 1	20
[BO\Zmsbackend\Process\Repository\Process] parseSearchTerms	78.57%	11 / 14	0.00%	0 / 1	6.35
[BO\Zmsbackend\Process\Repository\Process] appendNamePartLikeGroup	90.00%	9 / 10	0.00%	0 / 1	3.01
[BO\Zmsbackend\Process\Repository\Process] isNumericSearchQuery	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] escapeLikeValue	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] escapeLikePatternForSqlLiteral	0.00%	0 / 1	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] escapeSqlStringLiteral	0.00%	0 / 1	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionName	100.00%	8 / 8	100.00%	1 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addConditionMail	62.50%	5 / 8	0.00%	0 / 1	2.21
[BO\Zmsbackend\Process\Repository\Process] addConditionCustomTextfield	0.00%	0 / 8	0.00%	0 / 1	6
[BO\Zmsbackend\Process\Repository\Process] addConditionCustomTextfield2	0.00%	0 / 8	0.00%	0 / 1	6
[BO\Zmsbackend\Process\Repository\Process] addConditionAmendment	100.00%	4 / 4	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionRequestId	100.00%	8 / 8	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionScopeNameSearch	100.00%	23 / 23	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionServiceNameSearch	100.00%	16 / 16	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addConditionDeallocate	100.00%	7 / 7	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addValuesNewProcess	100.00%	16 / 16	100.00%	1 / 1	6
[BO\Zmsbackend\Process\Repository\Process] getNewDisplayNumber	13.33%	2 / 15	0.00%	0 / 1	8.86
[BO\Zmsbackend\Process\Repository\Process] checkIfDisplayNumberOnSameDateExists	0.00%	0 / 6	0.00%	0 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addValuesUpdateProcess	100.00%	13 / 13	100.00%	1 / 1	3
[BO\Zmsbackend\Process\Repository\Process] addValuesIPAdress	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addValuesFollowingProcessData	100.00%	7 / 7	100.00%	1 / 1	3
[BO\Zmsbackend\Process\Repository\Process] addValuesAppointmentData	100.00%	6 / 6	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addValuesScopeData	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addValuesStatusData	100.00%	28 / 28	100.00%	1 / 1	13
[BO\Zmsbackend\Process\Repository\Process] addValuesClientData	100.00%	18 / 18	100.00%	1 / 1	11
[BO\Zmsbackend\Process\Repository\Process] addValueDisplayNumber	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addProcessingTimeData	40.00%	20 / 50	0.00%	0 / 1	96.98
[BO\Zmsbackend\Process\Repository\Process] addValuesQueueData	100.00%	16 / 16	100.00%	1 / 1	10
[BO\Zmsbackend\Process\Repository\Process] addValuesWaitingTimeData	84.61%	33 / 39	0.00%	0 / 1	19.18
[BO\Zmsbackend\Process\Repository\Process] addValuesWayTimeData	100.00%	8 / 8	100.00%	1 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addValuesWasMissed	100.00%	5 / 5	100.00%	1 / 1	2
[BO\Zmsbackend\Process\Repository\Process] addValuesPriority	100.00%	5 / 5	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addValuesExternalUserId	100.00%	5 / 5	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] postProcess	100.00%	31 / 31	100.00%	1 / 1	7
[BO\Zmsbackend\Process\Repository\Process] removeDuplicates	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Process\Repository\Process] addRequiredJoins	100.00%	14 / 14	100.00%	1 / 1	3
[BO\Zmsbackend\Process\Repository\Process] addConditionExternalUserId	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] __construct	88.23%	30 / 34	0.00%	0 / 1	10.16
[BO\Zmsbackend\Query\Base] __toString	n/a	0 / 0	n/a	0 / 0	4
[BO\Zmsbackend\Query\Base] getName	100.00%	1 / 1	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] addSelect	100.00%	4 / 4	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] setDistinctSelect	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] setResolveLevel	100.00%	3 / 3	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] getResolveLevel	100.00%	3 / 3	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] getAlias	100.00%	5 / 5	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] getTablename	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] addTable	100.00%	4 / 4	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] addTableAlias	100.00%	4 / 4	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] addResolvedReferences	100.00%	11 / 11	100.00%	1 / 1	3
[BO\Zmsbackend\Query\Base] setWithEntities	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] leftJoin	100.00%	5 / 5	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] innerJoin	100.00%	5 / 5	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] getSql	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] getParameters	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] getReferenceMapping	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] expression	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] addEntityMapping	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] getPrefixed	100.00%	1 / 1	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] getPrefixedList	100.00%	4 / 4	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] addReferenceMapping	100.00%	3 / 3	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] addLimit	100.00%	4 / 4	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] addValues	100.00%	2 / 2	100.00%	1 / 1	1
[BO\Zmsbackend\Query\Base] postProcessJoins	100.00%	4 / 4	100.00%	1 / 1	2
[BO\Zmsbackend\Query\Base] shouldLoadEntity	100.00%	3 / 3	100.00%	1 / 1	2

5		class 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		}

Inherited from BO\Zmsbackend\Process\Repository\Process

94		public function getQueryNewProcessId(): string
95		{
96		$random = rand(20, 999);
97		return 'SELECT pseq.processId AS `nextid`
98		FROM process_sequence pseq
99		WHERE pseq.processId = (
100		SELECT ps.processID FROM `process_sequence` ps LEFT JOIN `' . self::getTablename() . '` p
101		ON ps.processId = p.BuergerID
102		WHERE p.`BuergerID` IS NULL
103		LIMIT ' . $random . ',1)
104		FOR UPDATE';
105		}

107		public function getLockProcessId(): string
108		{
109		return 'SELECT p.`BuergerID` FROM `' . self::getTablename() . '` p WHERE p.`BuergerID` = :processId FOR UPDATE';
110		}

112		public function getLockAssignedWorkstationId(): string
113		{
114		return 'SELECT p.`NutzerID`
115		FROM `' . self::getTablename() . '` p
116		WHERE p.`BuergerID` = :processId
117		FOR UPDATE';
118		}

121		public function addJoin()
122		{
123		$joins = [];
124
125		if ($this->shouldLoadEntity('availability')) {
126		$joins[] = $this->addJoinAvailability();
127		}
128
129		if ($this->shouldLoadEntity('scope')) {
130		$joins[] = $this->addJoinScope();
131		}
132
133		return $joins;
134		}

139		protected function addJoinAvailability(): \BO\Zmsbackend\Availability\Repository\Availability
140		{
141		$this->leftJoin(
142		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Availability\Repository\Availability::TABLE, 'availability'),
143		\BO\Zmsbackend\Availability\Repository\Availability::getJoinExpression('`process`', '`availability`')
144		);
145		$joinQuery = new \BO\Zmsbackend\Availability\Repository\Availability($this, $this->getPrefixed('appointments__0__availability__'));
146		return $joinQuery;
147		}

152		protected function addJoinScope(): \BO\Zmsbackend\Query\Scope
153		{
154		$this->leftJoin(
155		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Query\Scope::TABLE, 'scope'),
156		self::expression(
157		'IF(`process`.`AbholortID`,
158		`process`.`AbholortID`,
159		`process`.`StandortID`
160		)'
161		),
162		'=',
163		'scope.StandortID'
164		);
165		$joinQuery = new \BO\Zmsbackend\Query\Scope($this, $this->getPrefixed('scope__'));
166		return $joinQuery;
167		}

169		public function addConditionDate($date): static
170		{
171		$this->query->where('process.Datum', '=', $date);
172		return $this;
173		}

178		public function addConditionDisplayNumber($displayNumber): static
179		{
180		$this->query->where('process.displayNumber', '=', $displayNumber);
181		return $this;
182		}

189		protected function calculateStatus(): string\|null
190		{
191		if ($this->query->value('Name') === '(abgesagt)') {
192		return 'deleted';
193		}
194
195		if (
196		$this->query->value('StandortID') == 0
197		&& $this->query->value('AbholortID') == 0
198		) {
199		return 'blocked';
200		}
201
202		if (
203		$this->query->value('vorlaeufigeBuchung') == 1
204		&& $this->query->value('bestaetigt') == 0
205		) {
206		return 'reserved';
207		}
208
209		if ($this->query->value('nicht_erschienen') != 0) {
210		return 'missed';
211		}
212
213		if ($this->query->value('parked') != 0) {
214		return 'parked';
215		}
216
217		if (
218		$this->query->value('Abholer') != 0
219		&& $this->query->value('AbholortID') != 0
220		&& $this->query->value('NutzerID') == 0
221		) {
222		return 'pending';
223		}
224
225		if (
226		$this->query->value('AbholortID') == 0
227		&& $this->query->value('aufruferfolgreich') != 0
228		&& $this->query->value('NutzerID') != 0
229		) {
230		return 'processing';
231		}
232
233		if (
234		$this->query->value('aufrufzeit') != "00:00:00"
235		&& $this->query->value('NutzerID') != 0
236		&& $this->query->value('AbholortID') == 0
237		) {
238		return 'called';
239		}
240
241		if ($this->query->value('Uhrzeit') == "00:00:00") {
242		return 'queued';
243		}
244
245		if (
246		$this->query->value('vorlaeufigeBuchung') == 0
247		&& $this->query->value('bestaetigt') == 0
248		) {
249		return 'preconfirmed';
250		}
251
252		if (
253		$this->query->value('vorlaeufigeBuchung') == 0
254		&& $this->query->value('bestaetigt') == 1
255		) {
256		return 'confirmed';
257		}
258
259		return null;
260		}

367		public function addCountValue(): static
368		{
369		$this->query->select([
370		'processCount' => self::expression('COUNT(*)'),
371		]);
372		return $this;
373		}

378		public function addConditionHasTelephone(): static
379		{
380		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) {
381		$condition
382		->andWith('process.telefonnummer_fuer_rueckfragen', '!=', '')
383		->orWith('process.Telefonnummer', '!=', '');
384		});
385		return $this;
386		}

388		public function addConditionProcessDeleteInterval(\DateTimeInterface $expirationDate): static
389		{
390		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($expirationDate) {
391		$query->andWith(
392		self::expression(
393		'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
394		),
395		'<=',
396		$expirationDate->format('Y-m-d H:i:s')
397		);
398		});
399		$this->query->orderBy('appointments__0__date', 'ASC');
400		return $this;
401		}

403		public function addConditionProcessDeleteBeforeDate(\DateTimeInterface $date): static
404		{
405		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($date) {
406		$query->andWith(
407		self::expression(
408		'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
409		),
410		'<',
411		$date->format('Y-m-d') . ' 00:00:00'
412		);
413		});
414		$this->query->orderBy('appointments__0__date', 'ASC');
415		return $this;
416		}

421		public function addConditionProcessExpiredIPTimeStamp(\DateTimeInterface $expirationDate): static
422		{
423		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($expirationDate) {
424		$query->andWith('process.IPTimeStamp', '<=', $expirationDate->getTimestamp());
425		});
426		$this->query->orderBy('appointments__0__date', 'ASC');
427		return $this;
428		}

433		public function addConditionProcessReminderInterval(\DateTimeInterface $dateTime): static
434		{
435		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($dateTime) {
436		$query
437		->andWith('process.Erinnerungszeitpunkt', '<=', $dateTime->getTimestamp())
438		->andWith('process.Erinnerungszeitpunkt', '>=', $dateTime->modify("-5 Minutes")->getTimestamp());
439		});
440		$this->query->orderBy('reminderTimestamp', 'ASC');
441		return $this;
442		}

444		public function addConditionProcessMailReminder(
445		\DateTimeInterface $now,
446		\DateTimeInterface $lastRun,
447		int $defaultReminderInMinutes
448		): static {
449		$this->query
450		->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($now, $lastRun, $defaultReminderInMinutes) {
451		$query
452		->andWith(
453		self::expression(
454		'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
455		),
456		'>',
457		$lastRun->format('Y-m-d H:i:s')
458		)
459		->andWith(
460		self::expression(
461		'CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`)'
462		),
463		'>',
464		$now->format('Y-m-d H:i:s')
465		)
466		->andWith(
467		'scopemail.send_reminder',
468		'=',
469		1
470		)
471		->andWith(
472		'process.EMail',
473		'<>',
474		""
475		)
476		->andWith(
477		'process.EMailverschickt',
478		'=',
479		0
480		)
481		->andWith(
482		self::expression(
483		'DATE_SUB(CONCAT(`process`.`Datum`, " ", `process`.`Uhrzeit`), INTERVAL '
484		. 'IFNULL(scopemail.send_reminder_minutes_before, ' . $defaultReminderInMinutes
485		. ') MINUTE)'
486		),
487		'<=',
488		$now->format('Y-m-d H:i:s')
489		);
490		});
491		$this->query->orderBy('appointments__0__date', 'ASC');
492		return $this;
493		}

495		public function addConditionProcessId(int $processId): static
496		{
497		$this->query->where('process.BuergerID', '=', $processId);
498		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) {
499		$condition
500		->andWith('process.istFolgeterminvon', 'IS', null)
501		->orWith('process.istFolgeterminvon', '=', 0);
502		});
503		return $this;
504		}

506		public function addConditionProcessIdFollow($processId): static
507		{
508		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($processId) {
509		$condition
510		->andWith('process.BuergerID', '=', $processId)
511		->orWith('process.istFolgeterminvon', '=', $processId);
512		});
513		return $this;
514		}

519		public function addConditionIgnoreSlots(): static
520		{
521		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) {
522		$condition
523		->andWith('process.istFolgeterminvon', 'IS', null)
524		->orWith('process.istFolgeterminvon', '=', 0);
525		});
526		return $this;
527		}

529		public function addConditionScopeId(int $scopeId): static
530		{
531		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($scopeId) {
532		$query
533		->andWith('process.StandortID', '=', $scopeId)
534		->orWith('process.AbholortID', '=', $scopeId);
535		});
536		return $this;
537		}

539		public function addConditionScopeIds(array\|int $scopeIds)
540		{
541		if (count($scopeIds) === 0) {
542		$this->query->where(self::expression('1'), '=', 0);
543		return $this;
544		}
545
546		if (count($scopeIds) === 1) {
547		return $this->addConditionScopeId($scopeIds[0]);
548		}
549
550		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($scopeIds) {
551		$query
552		->andWith('process.StandortID', 'IN', $scopeIds)
553		->orWith('process.AbholortID', 'IN', $scopeIds);
554		});
555
556		return $this;
557		}

562		public function addConditionQueueNumber(int $queueNumber, int $queueLimit = 10000): static
563		{
564		($queueLimit > $queueNumber)
565		? $this->query->where('process.wartenummer', '=', $queueNumber)
566		: $this->query->where('process.BuergerID', '=', $queueNumber);
567		return $this;
568		}

570		public function addConditionWorkstationId($workstationId): static
571		{
572		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($workstationId) {
573		$query->andWith('process.NutzerID', '=', $workstationId);
574		$query->andWith('process.StandortID', '>', 0);
575		});
576		return $this;
577		}

579		public function addConditionTime(\DateTimeInterface\|null $dateTime): static
580		{
581		$this->query->where('process.Datum', '=', $dateTime->format('Y-m-d'));
582		return $this;
583		}

590		public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate): static
591		{
592		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) {
593		$condition
594		->andWith('process.Datum', '<=', $endDate->format('Y-m-d'))
595		->andWith('process.Datum', '>=', $startDate->format('Y-m-d'));
596		});
597		return $this;
598		}

600		public function addConditionAuthKey($authKey): static
601		{
602		$authKey = urldecode($authKey);
603		$this->query->where('process.absagecode', '=', $authKey);
604		return $this;
605		}

610		public function addConditionAssigned()
611		{
612		$this->query->where('process.StandortID', '!=', "0");
613		return $this;
614		}

616		public function addConditionStatus(string $status): static
617		{
618		$this->query->where('process.status', '=', $status);
619		return $this;
620		}

625		public function addConditionIsReserved(): static
626		{
627		$this->query->where('process.name', 'NOT IN', array(
628		'dereferenced',
629		'(abgesagt)'
630		))
631		->where('process.vorlaeufigeBuchung', '=', 1)
632		->where('process.StandortID', '>', 0);
633		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) {
634		$condition
635		->andWith('process.istFolgeterminvon', 'IS', null)
636		->orWith('process.istFolgeterminvon', '=', 0);
637		});
638		return $this;
639		}

641		public function addConditionSearch($queryString, bool $orWhere = false): static
642		{
643		$queryString = trim((string) $queryString);
644		$terms = $this->parseSearchTerms($queryString);
645
646		if ($terms === []) {
647		return $this;
648		}
649
650		$condition = function (
651		\BO\Zmsbackend\Query\Builder\ConditionBuilder $query
652		) use ($terms) {
653		foreach ($terms as $term) {
654		$query->andWith(
655		function (
656		\BO\Zmsbackend\Query\Builder\ConditionBuilder $inner
657		) use ($term) {
658		$this->appendGeneralSearchTermGroup(
659		$inner,
660		$term['value'],
661		$term['quoted']
662		);
663		}
664		);
665		}
666		};
667
668		if ($orWhere) {
669		$this->query->orWhere($condition);
670		} else {
671		$this->query->where($condition);
672		}
673
674		return $this;
675		}

680		public function addConditionProcessIdOrSearch($queryString): static
681		{
682		$queryString = trim((string) $queryString);
683		$processId = (int) $queryString;
684		$terms = $this->parseSearchTerms($queryString);
685
686		$this->query->where(function (
687		\BO\Zmsbackend\Query\Builder\ConditionBuilder $group
688		) use (
689		$processId,
690		$terms
691		) {
692		$group->andWith('process.BuergerID', '=', $processId);
693
694		if ($terms === []) {
695		return;
696		}
697
698		$group->orWith(function (
699		\BO\Zmsbackend\Query\Builder\ConditionBuilder $search
700		) use ($terms) {
701		foreach ($terms as $term) {
702		$search->andWith(
703		function (
704		\BO\Zmsbackend\Query\Builder\ConditionBuilder $inner
705		) use ($term) {
706		$this->appendGeneralSearchTermGroup(
707		$inner,
708		$term['value'],
709		$term['quoted']
710		);
711		}
712		);
713		}
714		});
715		});
716
717		return $this;
718		}

720		private function appendGeneralSearchTermGroup(\BO\Zmsbackend\Query\Builder\ConditionBuilder $query, string $term, bool $quoted = false): void
721		{
722		$likeContains = '%' . $this->escapeLikeValue($term) . '%';
723
724		$useNameWordBoundary = !$quoted
725		&& !$this->isNumericSearchQuery($term) && mb_strlen($term) <= 3;
726
727		if ($quoted) {
728		$this->appendNamePartLikeGroup(
729		$query,
730		$term,
731		true
732		);
733		} elseif ($useNameWordBoundary) {
734		$this->appendNamePartLikeGroup($query, $term, true, true);
735		} else {
736		$query->orWith('process.Name', 'LIKE', $likeContains);
737		}
738
739		$query->orWith('process.EMail', 'LIKE', $likeContains);
740		$query->orWith('process.Telefonnummer', 'LIKE', $likeContains);
741		$query->orWith('process.telefonnummer_fuer_rueckfragen', 'LIKE', $likeContains);
742		$query->orWith('process.displayNumber', 'LIKE', $likeContains);
743		$query->orWith('process.custom_text_field', 'LIKE', $likeContains);
744		$query->orWith('process.custom_text_field2', 'LIKE', $likeContains);
745		}

747		public function addConditionUpcomingOnly(\DateTimeInterface $now): static
748		{
749		$today = $now->format('Y-m-d');
750		$time = $now->format('H:i:s');
751		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($today, $time) {
752		$condition
753		->andWith('process.Datum', '>', $today)
754		->orWith(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $inner) use ($today, $time) {
755		$inner
756		->andWith('process.Datum', '=', $today)
757		->andWith('process.Uhrzeit', '>=', $time);
758		});
759		});
760		return $this;
761		}

764		public function addOrderByAppointmentDate(): static
765		{
766		$this->query->orderBy('process.Datum', 'ASC');
767		$this->query->orderBy('process.Uhrzeit', 'ASC');
768		$this->query->orderBy('process.BuergerID', 'ASC');
769		return $this;
770		}

773		public function addOrderBySearchRelevance(string $queryString): static
774		{
775		$queryString = trim($queryString);
776		if ($queryString === '') {
777		return $this;
778		}
779
780		$terms = $this->parseSearchTerms($queryString);
781		if ($terms === []) {
782		return $this;
783		}
784		$fullQuery = $this->escapeLikePatternForSqlLiteral($queryString);
785		$fullQueryExact = $this->escapeSqlStringLiteral($queryString);
786
787		$allTermsAsWordsConditions = [];
788		$allTermsInNameConditions = [];
789		$anyTermInNameConditions = [];
790
791		foreach ($terms as $term) {
792		$escapedTerm = $this->escapeLikePatternForSqlLiteral($term['value']);
793		$exactTerm = $this->escapeSqlStringLiteral($term['value']);
794		$allTermsAsWordsConditions[] = "("
795		. "process.Name = '{$exactTerm}'"
796		. " OR process.Name LIKE '{$escapedTerm} %'"
797		. " OR process.Name LIKE '% {$escapedTerm}'"
798		. " OR process.Name LIKE '% {$escapedTerm} %'"
799		. ")";
800		$allTermsInNameConditions[] = "process.Name LIKE '%{$escapedTerm}%'";
801		$anyTermInNameConditions[] = "process.Name LIKE '%{$escapedTerm}%'";
802		}
803		$allTermsAsWords = implode(' AND ', $allTermsAsWordsConditions);
804		$allTermsInName = implode(' AND ', $allTermsInNameConditions);
805		$anyTermInName = implode(' OR ', $anyTermInNameConditions);
806
807		$this->query->orderBy(self::expression(
808		"CASE
809		WHEN process.Name LIKE '{$fullQueryExact}' THEN 0
810		WHEN process.Name LIKE '{$fullQuery} %' THEN 1
811		WHEN {$allTermsAsWords} THEN 2
812		WHEN {$allTermsInName} THEN 3
813		WHEN {$anyTermInName} THEN 4
814		ELSE 5
815		END"
816		));
817
818		$this->query->orderBy('process.Datum', 'ASC');
819		$this->query->orderBy('process.Uhrzeit', 'ASC');
820		$this->query->orderBy('process.BuergerID', 'ASC');
821
822		return $this;
823		}

828		private function parseSearchTerms(string $queryString): array
829		{
830		if ($queryString === '') {
831		return [];
832		}
833
834		if (!preg_match_all('/"([^"]+)"\|(\S+)/u', $queryString, $matches, PREG_SET_ORDER)) {
835		return [['value' => $queryString, 'quoted' => false]];
836		}
837
838		$terms = [];
839		foreach ($matches as $match) {
840		$value = trim($match[1] !== '' ? $match[1] : $match[2]);
841		if ($value === '') {
842		continue;
843		}
844		$terms[] = [
845		'value' => $value,
846		'quoted' => $match[1] !== '',
847		];
848		}
849
850		return $terms;
851		}

853		private function appendNamePartLikeGroup(
854		\BO\Zmsbackend\Query\Builder\ConditionBuilder $query,
855		string $term,
856		bool $wordBoundaryOnly = false,
857		bool $includeWordPrefix = false
858		): void {
859		$escaped = $this->escapeLikeValue($term);
860		$query->orWith('process.Name', 'LIKE', $escaped . ' %');
861		$query->orWith('process.Name', 'LIKE', '% ' . $escaped);
862		$query->orWith('process.Name', 'LIKE', '% ' . $escaped . ' %');
863		$query->orWith('process.Name', '=', $term);
864		if ($wordBoundaryOnly) {
865		if ($includeWordPrefix) {
866		$query->orWith('process.Name', 'LIKE', $escaped . '%');
867		$query->orWith('process.Name', 'LIKE', '% ' . $escaped . '%');
868		}
869		} else {
870		$query->orWith('process.Name', 'LIKE', '%' . $escaped . '%');
871		}
872		}

874		private function isNumericSearchQuery(string $queryString): bool
875		{
876		return (bool) preg_match('#^\d+$#', $queryString);
877		}

879		private function escapeLikeValue(string $value): string
880		{
881		return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
882		}

884		private function escapeLikePatternForSqlLiteral(string $value): string
885		{
886		return $this->escapeSqlStringLiteral($this->escapeLikeValue($value));
887		}

889		private function escapeSqlStringLiteral(string $value): string
890		{
891		return str_replace("'", "''", $value);
892		}

894		public function addConditionName($name, bool $exactMatching = false): static
895		{
896		if ($exactMatching) {
897		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($name) {
898		$query->andWith('process.Name', '=', $name);
899		});
900		} else {
901		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($name) {
902		$query->andWith('process.Name', 'LIKE', "%$name%");
903		});
904		}
905		return $this;
906		}

908		public function addConditionMail(string $mailAddress, bool $exactMatching = false): static
909		{
910		if ($exactMatching) {
911		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
912		$query->andWith('process.Email', '=', $mailAddress);
913		});
914		} else {
915		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($mailAddress) {
916		$query->andWith('process.Email', 'LIKE', "%$mailAddress%");
917		});
918		}
919		return $this;
920		}

925		public function addConditionCustomTextfield($customText, bool $exactMatching = false): static
926		{
927		if ($exactMatching) {
928		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($customText) {
929		$query->andWith('process.custom_text_field', '=', $customText);
930		});
931		} else {
932		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($customText) {
933		$query->andWith('process.custom_text_field', 'LIKE', "%$customText%");
934		});
935		}
936		return $this;
937		}

942		public function addConditionCustomTextfield2($customText2, bool $exactMatching = false): static
943		{
944		if ($exactMatching) {
945		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($customText2) {
946		$query->andWith('process.custom_text_field2', '=', $customText2);
947		});
948		} else {
949		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($customText2) {
950		$query->andWith('process.custom_text_field2', 'LIKE', "%$customText2%");
951		});
952		}
953		return $this;
954		}

956		public function addConditionAmendment($amendment): static
957		{
958		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($amendment) {
959		$query->andWith('process.Anmerkung', 'LIKE', "%$amendment%");
960		});
961		return $this;
962		}

967		public function addConditionRequestId($requestId): static
968		{
969		$this->leftJoin(
970		new \BO\Zmsbackend\Query\Alias("buergeranliegen", 'buergeranliegen'),
971		'buergeranliegen.BuergerID',
972		'=',
973		'process.BuergerID'
974		);
975		$this->query->where('buergeranliegen.AnliegenID', '=', $requestId);
976		return $this;
977		}

979		public function addConditionScopeNameSearch(string $scopeName): static
980		{
981		$likeValue = '%' . $this->escapeLikeValue($scopeName) . '%';
982		$this->leftJoin(
983		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Query\Scope::TABLE, 'search_scope'),
984		self::expression(
985		'IF(`process`.`AbholortID`, `process`.`AbholortID`, `process`.`StandortID`)'
986		),
987		'=',
988		'search_scope.StandortID'
989		);
990		$this->leftJoin(
991		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Provider\Repository\Provider::TABLE, 'search_scope_provider'),
992		self::expression(
993		'search_scope.InfoDienstleisterID = search_scope_provider.id
994		AND search_scope.source = search_scope_provider.source'
995		)
996		);
997		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($likeValue) {
998		$query->orWith('search_scope.Bezeichnung', 'LIKE', $likeValue);
999		$query->orWith('search_scope.standortinfozeile', 'LIKE', $likeValue);
1000		$query->orWith('search_scope.standortkuerzel', 'LIKE', $likeValue);
1001		$query->orWith('search_scope_provider.name', 'LIKE', $likeValue);
1002		});
1003
1004		return $this;
1005		}

1007		public function addConditionServiceNameSearch(string $serviceName): static
1008		{
1009		$likeValue = '%' . $this->escapeLikeValue($serviceName) . '%';
1010		$this->leftJoin(
1011		new \BO\Zmsbackend\Query\Alias('buergeranliegen', 'search_request_link'),
1012		'search_request_link.BuergerID',
1013		'=',
1014		'process.BuergerID'
1015		);
1016		$this->leftJoin(
1017		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Request\Repository\Request::TABLE, 'search_request'),
1018		self::expression(
1019		'search_request_link.AnliegenID = search_request.id
1020		AND search_request_link.source = search_request.source'
1021		)
1022		);
1023		$this->query->where('search_request.name', 'LIKE', $likeValue);
1024
1025		return $this;
1026		}

1031		public function addConditionDeallocate(\DateTimeInterface $now): static
1032		{
1033		$this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($now) {
1034		$query
1035		->andWith('process.Name', '=', '(abgesagt)')
1036		->andWith('process.IPTimeStamp', '<', $now->getTimestamp());
1037		});
1038		$this->query->orderBy('process.IPTimeStamp', 'ASC');
1039		return $this;
1040		}

1042		public function addValuesNewProcess(ProcessEntity $process, $parentProcess = 0, int $childProcessCount = 0): void
1043		{
1044		$values = [
1045		'BuergerID' => $process->id,
1046		'IPTimeStamp' => $process->createTimestamp,
1047		'absagecode' => $process->authKey,
1048		'hatFolgetermine' => $childProcessCount,
1049		'istFolgeterminvon' => $parentProcess,
1050		'displayNumber' => $parentProcess === 0 && empty($process->queue['number'])
1051		? $this->getNewDisplayNumber($process)
1052		: null,
1053		'wartenummer' => $process->queue['number']
1054		];
1055		if ($process->toProperty()->apiclient->apiClientID->isAvailable()) {
1056		$values['apiClientID'] = $process->apiclient->apiClientID;
1057		}
1058		if (isset($process->isTicketprinter) && $process->isTicketprinter) {
1059		$values['is_ticketprinter'] = 1;
1060		}
1061		$this->addValues($values);
1062		}

1064		public function getNewDisplayNumber(ProcessEntity $process)
1065		{
1066		if (empty($process->scope->getPreference('queue', 'displayNumberPrefix'))) {
1067		return $process->id;
1068		}
1069
1070		$newDisplayNumber = $process->scope->getPreference('queue', 'displayNumberPrefix') . str_pad(
1071		(new ScopeEntity())->readDisplayNumberUpdated($process->scope->id),
1072		4,
1073		'0',
1074		STR_PAD_LEFT
1075		);
1076
1077		if (
1078		$this->checkIfDisplayNumberOnSameDateExists(
1079		$process->scope->id,
1080		$newDisplayNumber,
1081		\DateTime::createFromFormat('U', (int) $process->getFirstAppointment()->date)
1082		)
1083		) {
1084		return $this->getNewDisplayNumber($process);
1085		}
1086
1087		return $newDisplayNumber;
1088		}

1090		public function checkIfDisplayNumberOnSameDateExists($scopeId, string $displayNumber, \DateTime\|false $date): bool
1091		{
1092		$processWithDisplayNumber = (new \BO\Zmsbackend\Process\Service\Process())->readProcessWithSameDayAndDisplayNumber(
1093		$scopeId,
1094		$displayNumber,
1095		$date->format('Y-m-d')
1096		);
1097
1098		return !empty($processWithDisplayNumber->getId());
1099		}

1101		public function addValuesUpdateProcess(
1102		ProcessEntity $process,
1103		\DateTimeInterface $dateTime,
1104		int $parentProcess = 0,
1105		$previousStatus = null
1106		): void {
1107		$this->addValuesIPAdress($process);
1108		if (0 === $parentProcess) {
1109		$this->addValuesClientData($process);
1110		$this->addProcessingTimeData($process, $dateTime, $previousStatus);
1111		$this->addValuesQueueData($process);
1112		$this->addValuesWaitingTimeData($process, $previousStatus);
1113		$this->addValuesWayTimeData($process);
1114		}
1115		if ($process->isWithAppointment()) {
1116		$this->addValuesFollowingProcessData($process, $parentProcess);
1117		}
1118		$this->addValuesWasMissed($process);
1119		$this->addValuesPriority($process);
1120		$this->addValuesStatusData($process, $dateTime);
1121		$this->addValuesExternalUserId($process);
1122		}

1124		public function addValuesIPAdress(ProcessEntity $process): void
1125		{
1126		$data = array();
1127		$data['IPAdresse'] = $process->createIP;
1128		$this->addValues($data);
1129		}

1131		public function addValuesFollowingProcessData(ProcessEntity $process, $parentProcess): void
1132		{
1133		$data = array();
1134		if (0 === $parentProcess) {
1135		$data['hatFolgetermine'] = (1 <= $process->getFirstAppointment()->getSlotCount()) ?
1136		$process->getFirstAppointment()->getSlotCount() - 1 :
1137		0;
1138		} else {
1139		$data['Name'] = '(Folgetermin)';
1140		}
1141		$this->addValues($data);
1142		}

1144		public function addValuesAppointmentData(
1145		ProcessEntity $process
1146		): void {
1147		$data = array();
1148		$appointment = $process->getFirstAppointment();
1149		$datetime = $appointment->toDateTime();
1150		$data['Datum'] = $datetime->format('Y-m-d');
1151		$data['Uhrzeit'] = $datetime->format('H:i:s');
1152		$this->addValues($data);
1153		}

1155		public function addValuesScopeData(
1156		ProcessEntity $process
1157		): void {
1158		$data = array();
1159		$data['StandortID'] = $process->getScopeId();
1160		$this->addValues($data);
1161		}

1163		public function addValuesStatusData(ProcessEntity $process, \DateTimeInterface $dateTime): void
1164		{
1165		$data = array();
1166		$data['vorlaeufigeBuchung'] = ($process->status == 'reserved') ? 1 : 0;
1167		$data['aufruferfolgreich'] = ($process->status == 'processing') ? 1 : 0;
1168		if ($process->status == 'called') {
1169		$data['parked'] = 0;
1170		$data['nicht_erschienen'] = 0;
1171		}
1172		if ($process->status == 'pending') {
1173		$data['AbholortID'] = $process->scope->id;
1174		$data['Abholer'] = 1;
1175		$data['nicht_erschienen'] = 0;
1176		$data['parked'] = 0;
1177		}
1178		if ($process->status == 'queued') {
1179		$data['nicht_erschienen'] = 0;
1180		$data['parked'] = 0;
1181		if (
1182		$process->hasArrivalTime() &&
1183		(isset($process->queue->withAppointment) && $process->queue->withAppointment)
1184		) {
1185		$data['wsm_aufnahmezeit'] = $dateTime->format('H:i:s');
1186		}
1187		}
1188		if ($process->status == 'missed') {
1189		$data['nicht_erschienen'] = 1;
1190		}
1191		if ($process->status == 'parked') {
1192		$data['parked'] = 1;
1193		}
1194		if ($process->status == 'confirmed') {
1195		$data['bestaetigt'] = 1;
1196		}
1197		if ($process->status == 'preconfirmed') {
1198		$data['bestaetigt'] = 0;
1199		}
1200		$data['status'] = $process->status;
1201		$data['parkedBy'] = $process->getParkedBy();
1202
1203		$this->addValues($data);
1204		}

1206		protected function addValuesClientData(ProcessEntity $process): void
1207		{
1208		$data = array();
1209		$client = $process->getFirstClient();
1210		if ($client && $client->hasFamilyName()) {
1211		$data['Name'] = $client->familyName;
1212		}
1213		if ($client && $client->hasEmail()) {
1214		$data['EMail'] = $client->email;
1215		}
1216		if ($client && $client->offsetExists('telephone')) {
1217		$data['telefonnummer_fuer_rueckfragen'] = $client->telephone;
1218		$data['Telefonnummer'] = $client->telephone; // to stay compatible with ZMS1
1219		}
1220		if ($client && $client->offsetExists('emailSendCount')) {
1221		$data['EMailverschickt'] = ('-1' == $client->emailSendCount) ? 0 : $client->emailSendCount;
1222		}
1223		$data['Anmerkung'] = $process->getAmendment();
1224		$data['custom_text_field'] = $process->getCustomTextfield();
1225		$data['custom_text_field2'] = $process->getCustomTextfield2();
1226		$data['zustimmung_kundenbefragung'] = ($client->surveyAccepted) ? 1 : 0;
1227		$data['Erinnerungszeitpunkt'] = $process->getReminderTimestamp();
1228		$data['AnzahlPersonen'] = $process->getClients()->count();
1229		$this->addValues($data);
1230		}

1232		public function addValueDisplayNumber(ProcessEntity $process): void
1233		{
1234		$data['displayNumber'] = $process->displayNumber;
1235		$this->addValues($data);
1236		}

1238		protected function addProcessingTimeData(ProcessEntity $process, \DateTimeInterface $dateTime, $previousStatus = null): void
1239		{
1240		$data = array();
1241		$timeoutTime = null;
1242		$finishTime = null;
1243
1244		if (
1245		isset($previousStatus) &&
1246		(($process->status == 'called' && $previousStatus == 'called') \|\|
1247		($process->status == 'processing' && $previousStatus == 'processing'))
1248		) {
1249		$timeoutTime = $dateTime->format('Y-m-d H:i:s');
1250		$data['timeoutTime'] = $timeoutTime;
1251		} elseif ($process->status == 'processing' && ($process->showUpTime === null \|\| $process->showUpTime === '')) {
1252		// Do not reset showUpTime on re-save (preserves Bearbeitungszeit)
1253		$showUpTime = $dateTime->format('Y-m-d H:i:s');
1254		$data['showUpTime'] = $showUpTime;
1255		} elseif ($process->status == 'finished') {
1256		$finishTime = $dateTime->format('Y-m-d H:i:s');
1257		$data['finishTime'] = $finishTime;
1258		} elseif (
1259		isset($previousStatus)
1260		&& in_array($previousStatus, ['called', 'processing'], true)
1261		&& in_array($process->status, ['queued', 'parked'], true)
1262		) {
1263		// Drop open processing segment when leaving to queue/park so a later
1264		// resume starts a fresh showUpTime (park-and-resume ATAF expectation).
1265		$data['showUpTime'] = null;
1266		$data['timeoutTime'] = null;
1267		}
1268
1269
1270		if (isset($finishTime) && isset($process->showUpTime)) {
1271		$showUpDateTime = new \DateTime($process->showUpTime);
1272		$finishTime = new \DateTime($finishTime);
1273
1274		$processingTimeStr = $process->getProcessingTime();
1275		$previousProcessingTimeInSeconds = 0; // Default to 0 if not set
1276
1277		if (!empty($processingTimeStr)) {
1278		// Assume the format is HH:MM:SS and parse it
1279		list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
1280		// Convert hours, minutes, and seconds to total seconds
1281		$previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
1282		}
1283
1284		$interval = $showUpDateTime->diff($finishTime);
1285		$totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
1286
1287		$totalSeconds += $previousProcessingTimeInSeconds;
1288
1289		$hours = intdiv($totalSeconds, 3600);
1290		$minutes = intdiv($totalSeconds % 3600, 60);
1291		$seconds = $totalSeconds % 60;
1292
1293		$data['processing_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1294		} elseif (isset($timeoutTime) && isset($process->showUpTime)) {
1295		$showUpDateTime = new \DateTime($process->showUpTime);
1296		$timeoutDateTime = new \DateTime($timeoutTime);
1297		$processingTimeStr = $process->getProcessingTime();
1298
1299		$previousProcessingTimeInSeconds = 0; // Default to 0 if not set
1300		if (!empty($processingTimeStr)) {
1301		// Assume the format is HH:MM:SS and parse it
1302		list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
1303		// Convert hours, minutes, and seconds to total seconds
1304		$previousProcessingTimeInSeconds = (int)$hours * 3600 + (int)$minutes * 60 + (int)$seconds;
1305		}
1306		$interval = $showUpDateTime->diff($timeoutDateTime);
1307		$totalSeconds = ($interval->days * 24 * 60 * 60) + ($interval->h * 60 * 60) + ($interval->i * 60) + $interval->s;
1308
1309		$totalSeconds += $previousProcessingTimeInSeconds;
1310
1311		$hours = intdiv($totalSeconds, 3600);
1312		$minutes = intdiv($totalSeconds % 3600, 60);
1313		$seconds = $totalSeconds % 60;
1314
1315		$data['processing_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1316		}
1317
1318		$this->addValues($data);
1319		}

1321		protected function addValuesQueueData(ProcessEntity $process): void
1322		{
1323		$data = array();
1324		$appointmentTime = $process->getFirstAppointment()->toDateTime()->format('H:i:s');
1325
1326		if (isset($process->queue['callCount'])) {
1327		$data['AnzahlAufrufe'] = (int) $process->queue['callCount'];
1328		}
1329		if (isset($process->queue['callTime']) && $process->queue['callTime']) {
1330		$data['aufrufzeit'] = (new \DateTimeImmutable())
1331		->setTimestamp($process->queue['callTime'])->format('H:i:s');
1332		}
1333		if (isset($process->queue['lastCallTime']) && $process->queue['lastCallTime']) {
1334		$data['Timestamp'] = (new \DateTimeImmutable())
1335		->setTimestamp($process->queue['lastCallTime'])->format('H:i:s');
1336		}
1337		if (isset($process->queue['arrivalTime']) && $process->queue['arrivalTime']) {
1338		$data['wsm_aufnahmezeit'] = (new \DateTimeImmutable())
1339		->setTimestamp($process->queue['arrivalTime'])->format('H:i:s');
1340		}
1341		if (isset($data['wsm_aufnahmezeit']) && $data['wsm_aufnahmezeit'] == $appointmentTime) {
1342		// Do not save arrivalTime if it is an appointment
1343		$data['wsm_aufnahmezeit'] = 0;
1344		}
1345		$this->addValues($data);
1346		}

1348		protected function addValuesWaitingTimeData(ProcessEntity $process, $previousStatus = null): void
1349		{
1350		$data = array();
1351		$hasWaitingTimeAlready = (
1352		isset($process->queue->waitingTime)
1353		&& $process->queue->waitingTime
1354		&& $process->queue->waitingTime !== '00:00:00'
1355		);
1356
1357		if (
1358		!$hasWaitingTimeAlready
1359		&& (
1360		(
1361		// Szenario 1: Vorheriger Status ist queued, missed oder confirmed und aktueller Status ist called
1362		in_array($previousStatus, ['queued', 'missed', 'confirmed'])
1363		&& $process->status == 'called'
1364		&& (
1365		(
1366		isset($process->queue->callCount)
1367		&& $process->queue->callCount <= 0
1368		)
1369		\|\| (
1370		($previousStatus === 'queued' \|\| $previousStatus === 'confirmed')
1371		&& (
1372		!isset($process->queue->callTime)
1373		\|\| (int) $process->queue->callTime === 0
1374		)
1375		)
1376		)
1377		)
1378		\|\|
1379		(
1380		// Szenario 2: Vorheriger Status ist missed, aktueller Status ist queued,
1381		// es gibt den Hinweis wasMissed und die Queue sowie waitingTime sind gesetzt
1382		$previousStatus == 'missed'
1383		&& $process->status == 'queued'
1384		&& !empty($process->wasMissed)
1385		&& isset($process->queue)
1386		&& isset($process->queue->waitingTime)
1387		)
1388		)
1389		) {
1390		$wartezeitInSeconds = $process->getWaitedSeconds();
1391		$wartezeitInSeconds = $wartezeitInSeconds > 0 ? $wartezeitInSeconds : 0;
1392
1393		// Convert total seconds into HH:MM:SS format
1394		$hours = intdiv($wartezeitInSeconds, 3600);
1395		$minutes = intdiv($wartezeitInSeconds % 3600, 60);
1396		$seconds = $wartezeitInSeconds % 60;
1397
1398		$data['waiting_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1399		}
1400
1401		$this->addValues($data);
1402		}

1405		protected function addValuesWayTimeData(ProcessEntity $process): void
1406		{
1407		$data = array();
1408		if ($process->status == 'processing') {
1409		$wayTimeInSeconds = max(0, (int) $process->getWaySeconds());
1410		$hours = intdiv($wayTimeInSeconds, 3600);
1411		$minutes = intdiv($wayTimeInSeconds % 3600, 60);
1412		$seconds = $wayTimeInSeconds % 60;
1413		$data['way_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
1414		}
1415		$this->addValues($data);
1416		}

1418		protected function addValuesWasMissed(ProcessEntity $process): static
1419		{
1420		$data = [
1421		'wasMissed' => $process->wasMissed ? 1 : 0,
1422		];
1423
1424		$this->addValues($data);
1425		return $this;
1426		}

1428		protected function addValuesPriority(ProcessEntity $process): static
1429		{
1430		$data = [
1431		'priority' => $process->priority,
1432		];
1433
1434		$this->addValues($data);
1435		return $this;
1436		}

1438		protected function addValuesExternalUserId(ProcessEntity $process): static
1439		{
1440		$data = [
1441		'external_user_id' => $process->externalUserId,
1442		];
1443
1444		$this->addValues($data);
1445		return $this;
1446		}

1449		public function postProcess($data)
1450		{
1451		$data[$this->getPrefixed("appointments__0__date")] =
1452		strtotime($data[$this->getPrefixed("appointments__0__date")]);
1453		if ('00:00:00' != $data[$this->getPrefixed("queue__callTime")]) {
1454		$time = explode(':', $data[$this->getPrefixed("queue__callTime")]);
1455		$data[$this->getPrefixed("queue__callTime")] = (new \DateTimeImmutable())
1456		->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1457		->setTime($time[0], $time[1], $time[2])
1458		->getTimestamp();
1459		} else {
1460		$data[$this->getPrefixed("queue__callTime")] = 0;
1461		}
1462		if ('00:00:00' != $data[$this->getPrefixed("queue__lastCallTime")]) {
1463		$time = explode(':', $data[$this->getPrefixed("queue__lastCallTime")]);
1464		$data[$this->getPrefixed("queue__lastCallTime")] = (new \DateTimeImmutable())
1465		->setTimestamp($data[$this->getPrefixed("appointments__0__date")])
1466		->setTime($time[0], $time[1], $time[2])
1467		->getTimestamp();
1468		} else {
1469		$data[$this->getPrefixed("queue__lastCallTime")] = 0;
1470		}
1471		$data[$this->getPrefixed("queue__arrivalTime")] =
1472		strtotime($data[$this->getPrefixed("queue__arrivalTime")]);
1473		if (
1474		isset($data[$this->getPrefixed('scope__provider__data')])
1475		&& $data[$this->getPrefixed('scope__provider__data')]
1476		) {
1477		$data[$this->getPrefixed('scope__provider__data')] =
1478		json_decode($data[$this->getPrefixed('scope__provider__data')], true);
1479		}
1480		if (isset($data[$this->getPrefixed('__clientsCount')])) {
1481		$clientsCount = $data[$this->getPrefixed('__clientsCount')];
1482		unset($data[$this->getPrefixed('__clientsCount')]);
1483		while (--$clientsCount > 0) {
1484		$data[$this->getPrefixed('clients__' . $clientsCount . '__familyName')] = 'Unbekannt';
1485		}
1486		}
1487		$data[$this->getPrefixed("lastChange")] =
1488		(new \DateTimeImmutable($data[$this->getPrefixed("lastChange")] .
1489		\BO\Zmsbackend\Connection\Select::$connectionTimezone))->getTimestamp();
1490		return $data;
1491		}

1496		public function removeDuplicates(): static
1497		{
1498		$this->query->groupBy('process.BuergerID');
1499		return $this;
1500		}

1506		protected function addRequiredJoins()
1507		{
1508		if ($this->shouldLoadEntity('processuser')) {
1509		$this->leftJoin(
1510		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Useraccount\Repository\Useraccount::TABLE, 'processuser'),
1511		'process.NutzerID',
1512		'=',
1513		'processuser.NutzerID'
1514		);
1515		}
1516
1517		if ($this->shouldLoadEntity('processscope')) {
1518		$this->leftJoin(
1519		new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Query\Scope::TABLE, 'processscope'),
1520		'process.StandortID',
1521		'=',
1522		'processscope.StandortID'
1523		);
1524		}
1525		}

1527		public function addConditionExternalUserId(string $externalUserId): static
1528		{
1529		$this->query->where('process.external_user_id', '=', $externalUserId);
1530		return $this;
1531		}

Inherited from BO\Zmsbackend\Query\Base

96		public function __construct(
97		$queryType,
98		$prefix = '',
99		string\|false $name = false,
100		mixed $resolveLevel = null,
101		array $withEntities = []
102		) {
103		$this->prefix = $prefix;
104		$this->name = $name;
105		$this->withEntities = $withEntities;
106		$this->setResolveLevel($resolveLevel);
107		$dialect = new MySQL();
108		if (self::SELECT === $queryType) {
109		$this->query = new Select($dialect);
110		$this->addSelect();
111		} elseif (self::INSERT === $queryType) {
112		$this->query = new Insert($dialect);
113		$this->addTable();
114		} elseif (self::UPDATE === $queryType) {
115		$this->query = new Update($dialect);
116		$this->addTableAlias();
117		} elseif (self::REPLACE === $queryType) {
118		$this->query = new INSERT($dialect);
119		$this->query->queryBaseStatement('REPLACE INTO');
120		$this->addTable();
121		} elseif (self::DELETE === $queryType) {
122		$this->query = new Delete($dialect);
123		$this->query->queryBaseStatement('DELETE ' . $this::getAlias() . ' FROM');
124		$this->addTableAlias();
125		} elseif ($queryType instanceof self) {
126		$this->query = $queryType->query;
127		// Share join-alias state with the parent query (intentional by-ref).
128		/** @psalm-suppress UnsupportedPropertyReferenceUsage */
129		$this->joinedAliasList =& $queryType->joinedAliasList;
130		$this->resolveLevel = $queryType->resolveLevel - 1;
131		} elseif ($queryType instanceof QueryBuilder) {
132		$this->query = $queryType;
133		} else {
134		throw new \InvalidArgumentException(
135		'Unsupported query type for ' . static::class . ': '
136		. (is_object($queryType) ? $queryType::class : gettype($queryType))
137		);
138		}
139		if ($this->query instanceof Select) {
140		$this->addRequiredJoins();
141		}
142		}

147		public function __toString()
148		{
149		if ($this->name) {
150		$name = $this->name . '_' . $this->prefix . (string) $this->resolveLevel;
151		if (!isset(static::$sqlCache[$name])) {
152		static::$sqlCache[$name] = $this->getSql();
153		}
154		return static::$sqlCache[$name];
155		}
156		if ($this->currentSqlString) {
157		$sql = $this->currentSqlString;
158		} else {
159		$sql = $this->getSql();
160		}
161		return $sql;
162		}

164		public function getName()
165		{
166		return $this->name ? $this->name : get_class($this);
167		}

174		protected function addSelect(): static
175		{
176		$table = $this::getTablename();
177		$alias = $this::getAlias();
178		$this->query->from($table, $alias);
179		return $this;
180		}

182		public function setDistinctSelect(): void
183		{
184		$this->query->queryBaseStatement('SELECT DISTINCT');
185		}

187		public function setResolveLevel(int\|null $resolveLevel): static
188		{
189		if ($resolveLevel !== null) {
190		$this->resolveLevel = $resolveLevel;
191		}
192		return $this;
193		}

195		public function getResolveLevel()
196		{
197		if (null === $this->resolveLevel) {
198		throw new \Exception("Required setting for resolveReferenceLevel missing in " . get_class($this));
199		}
200		return $this->resolveLevel;
201		}

210		public static function getAlias()
211		{
212		$class = get_called_class();
213		$alias = constant($class . '::ALIAS');
214		if (null === $alias) {
215		$alias = lcfirst(preg_replace('#^.*\\\#', '', $class));
216		}
217		return $alias;
218		}

225		public static function getTablename()
226		{
227		$class = get_called_class();
228		$table = constant($class . '::TABLE');
229		return $table;
230		}

237		protected function addTable(): static
238		{
239		$table = $this::getTablename();
240		$alias = $this::getAlias();
241		$this->query->table($table, $alias);
242		return $this;
243		}

250		protected function addTableAlias(): static
251		{
252		$table = $this::getTablename();
253		$alias = $this::getAlias();
254		$this->query->table(self::expression($table . ' ' . $alias));
255		return $this;
256		}

273		public function addResolvedReferences($depth): static
274		{
275		$this->setResolveLevel($depth);
276		if ($depth > 0) {
277		$queryList = $this->addJoin();
278		foreach ($queryList as $query) {
279		$query->setResolveLevel($depth);
280		$query->setWithEntities($this->withEntities);
281		$query->addResolvedReferences($depth - 1);
282		$query->addEntityMapping();
283		}
284		$this->joinedQueryList = $queryList;
285		} else {
286		$this->addReferenceMapping();
287		}
288		return $this;
289		}

294		public function setWithEntities($withEntities = []): void
295		{
296		$this->withEntities = $withEntities;
297		}

309		protected function leftJoin(Alias $alias, string\|Expression\|null $left = null, string\|null $operator = null, string\|null $right = null): QueryBuilder
310		{
311		$aliasId = $alias->getAliasIdentifier();
312		if (!in_array($aliasId, $this->joinedAliasList)) {
313		$this->joinedAliasList[] = $aliasId;
314		$this->query->leftJoin($alias, $left, $operator, $right);
315		} else {
316		//throw new \Exception("Tried to add Alias ".$aliasId);
317		}
318		return $this->query;
319		}

321		protected function innerJoin(Alias $alias, string\|null $left = null, string\|null $operator = null, string\|null $right = null): QueryBuilder
322		{
323		$aliasId = $alias->getAliasIdentifier();
324		if (!in_array($aliasId, $this->joinedAliasList)) {
325		$this->joinedAliasList[] = $aliasId;
326		$this->query->join($alias, $left, $operator, $right);
327		}
328		return $this->query;
329		}

337		public function getSql()
338		{
339		$this->currentSqlString = (string)$this->query;
340		return $this->currentSqlString;
341		}

348		public function getParameters()
349		{
350		return $this->query->params();
351		}

357		public function getReferenceMapping()
358		{
359		return [
360		];
361		}

368		protected static function expression(string $string)
369		{
370		return new Expression($string);
371		}

379		public function addEntityMapping(mixed $type = null): static
380		{
381		// Concrete query classes provide getEntityMapping(); not all declare MappingInterface.
382		/** @psalm-suppress UndefinedMethod, TooManyArguments */
383		$entityMapping = $this->getPrefixedList($this->getEntityMapping($type));
384		$this->query->select($entityMapping);
385		return $this;
386		}

388		protected function getPrefixed($prefix): string
389		{
390		return $this->prefix . $prefix;
391		}

393		protected function getPrefixedList(array $unprefixedList): array
394		{
395		$prefixed = [];
396		foreach ($unprefixedList as $key => $value) {
397		$prefixed[$this->getPrefixed($key)] = $value;
398		}
399		return $prefixed;
400		}

405		protected function addReferenceMapping(): static
406		{
407		$referenceMapping = $this->getPrefixedList($this->getReferenceMapping());
408		$this->query->select($referenceMapping);
409		return $this;
410		}

412		public function addLimit(int $count, ?int $offset = null): static
413		{
414		$this->query->limit($count);
415		if ($offset) {
416		$this->query->offset($offset);
417		}
418		return $this;
419		}

424		public function addValues(array $values): static
425		{
426		$this->query->values($values);
427		return $this;
428		}

445		public function postProcessJoins(array $data)
446		{
447		$data = $this->postProcess($data);
448		foreach ($this->joinedQueryList as $query) {
449		$data = $query->postProcess($data);
450		}
451		return $data;
452		}

454		public function shouldLoadEntity(string $name): bool
455		{
456		if (empty($this->withEntities)) {
457		return true;
458		}
459
460		return in_array($name, $this->withEntities);
461		}