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