Lines 100.00% 30 / 30
Functions and Methods 100.00% 2 / 2
Classes and Traits 100.00% 1 / 1
Name Lines Functions and Methods CRAP Classes and Traits
ExchangeWaitingscope 100.00% 30 / 30 100.00% 2 / 2 4 100.00% 1 / 1
 getQuerySelectByDateTime 100.00% 15 / 15 100.00% 1 / 1 2
 getQueryUpdateByDateTime 100.00% 15 / 15 100.00% 1 / 1 2
1<?php
2
3namespace BO\Zmsbackend\Exchange\Repository;
4
5class ExchangeWaitingscope extends \BO\Zmsbackend\Query\Base
6{
7    /**
8     * @var String TABLE mysql table reference
9     */
10    const string TABLE = 'wartenrstatistik';
11
12    const string WAITING_VALUES = "
13        AVG(hour_00_waiting_time_spontaneous) as hour_00_waiting_time_spontaneous,
14        AVG(hour_01_waiting_time_spontaneous) as hour_01_waiting_time_spontaneous,
15        AVG(hour_02_waiting_time_spontaneous) as hour_02_waiting_time_spontaneous,
16        AVG(hour_03_waiting_time_spontaneous) as hour_03_waiting_time_spontaneous,
17        AVG(hour_04_waiting_time_spontaneous) as hour_04_waiting_time_spontaneous,
18        AVG(hour_05_waiting_time_spontaneous) as hour_05_waiting_time_spontaneous,
19        AVG(hour_06_waiting_time_spontaneous) as hour_06_waiting_time_spontaneous,
20        AVG(hour_07_waiting_time_spontaneous) as hour_07_waiting_time_spontaneous,
21        AVG(hour_08_waiting_time_spontaneous) as hour_08_waiting_time_spontaneous,
22        AVG(hour_09_waiting_time_spontaneous) as hour_09_waiting_time_spontaneous,
23        AVG(hour_10_waiting_time_spontaneous) as hour_10_waiting_time_spontaneous,
24        AVG(hour_11_waiting_time_spontaneous) as hour_11_waiting_time_spontaneous,
25        AVG(hour_12_waiting_time_spontaneous) as hour_12_waiting_time_spontaneous,
26        AVG(hour_13_waiting_time_spontaneous) as hour_13_waiting_time_spontaneous,
27        AVG(hour_14_waiting_time_spontaneous) as hour_14_waiting_time_spontaneous,
28        AVG(hour_15_waiting_time_spontaneous) as hour_15_waiting_time_spontaneous,
29        AVG(hour_16_waiting_time_spontaneous) as hour_16_waiting_time_spontaneous,
30        AVG(hour_17_waiting_time_spontaneous) as hour_17_waiting_time_spontaneous,
31        AVG(hour_18_waiting_time_spontaneous) as hour_18_waiting_time_spontaneous,
32        AVG(hour_19_waiting_time_spontaneous) as hour_19_waiting_time_spontaneous,
33        AVG(hour_20_waiting_time_spontaneous) as hour_20_waiting_time_spontaneous,
34        AVG(hour_21_waiting_time_spontaneous) as hour_21_waiting_time_spontaneous,
35        AVG(hour_22_waiting_time_spontaneous) as hour_22_waiting_time_spontaneous,
36        AVG(hour_23_waiting_time_spontaneous) as hour_23_waiting_time_spontaneous,
37        AVG(hour_00_estimated_waiting_time_spontaneous) as hour_00_estimated_waiting_time_spontaneous,
38        AVG(hour_01_estimated_waiting_time_spontaneous) as hour_01_estimated_waiting_time_spontaneous,
39        AVG(hour_02_estimated_waiting_time_spontaneous) as hour_02_estimated_waiting_time_spontaneous,
40        AVG(hour_03_estimated_waiting_time_spontaneous) as hour_03_estimated_waiting_time_spontaneous,
41        AVG(hour_04_estimated_waiting_time_spontaneous) as hour_04_estimated_waiting_time_spontaneous,
42        AVG(hour_05_estimated_waiting_time_spontaneous) as hour_05_estimated_waiting_time_spontaneous,
43        AVG(hour_06_estimated_waiting_time_spontaneous) as hour_06_estimated_waiting_time_spontaneous,
44        AVG(hour_07_estimated_waiting_time_spontaneous) as hour_07_estimated_waiting_time_spontaneous,
45        AVG(hour_08_estimated_waiting_time_spontaneous) as hour_08_estimated_waiting_time_spontaneous,
46        AVG(hour_09_estimated_waiting_time_spontaneous) as hour_09_estimated_waiting_time_spontaneous,
47        AVG(hour_10_estimated_waiting_time_spontaneous) as hour_10_estimated_waiting_time_spontaneous,
48        AVG(hour_11_estimated_waiting_time_spontaneous) as hour_11_estimated_waiting_time_spontaneous,
49        AVG(hour_12_estimated_waiting_time_spontaneous) as hour_12_estimated_waiting_time_spontaneous,
50        AVG(hour_13_estimated_waiting_time_spontaneous) as hour_13_estimated_waiting_time_spontaneous,
51        AVG(hour_14_estimated_waiting_time_spontaneous) as hour_14_estimated_waiting_time_spontaneous,
52        AVG(hour_15_estimated_waiting_time_spontaneous) as hour_15_estimated_waiting_time_spontaneous,
53        AVG(hour_16_estimated_waiting_time_spontaneous) as hour_16_estimated_waiting_time_spontaneous,
54        AVG(hour_17_estimated_waiting_time_spontaneous) as hour_17_estimated_waiting_time_spontaneous,
55        AVG(hour_18_estimated_waiting_time_spontaneous) as hour_18_estimated_waiting_time_spontaneous,
56        AVG(hour_19_estimated_waiting_time_spontaneous) as hour_19_estimated_waiting_time_spontaneous,
57        AVG(hour_20_estimated_waiting_time_spontaneous) as hour_20_estimated_waiting_time_spontaneous,
58        AVG(hour_21_estimated_waiting_time_spontaneous) as hour_21_estimated_waiting_time_spontaneous,
59        AVG(hour_22_estimated_waiting_time_spontaneous) as hour_22_estimated_waiting_time_spontaneous,
60        AVG(hour_23_estimated_waiting_time_spontaneous) as hour_23_estimated_waiting_time_spontaneous,
61        AVG(hour_00_way_time_spontaneous) as hour_00_way_time_spontaneous,
62        AVG(hour_01_way_time_spontaneous) as hour_01_way_time_spontaneous,
63        AVG(hour_02_way_time_spontaneous) as hour_02_way_time_spontaneous,
64        AVG(hour_03_way_time_spontaneous) as hour_03_way_time_spontaneous,
65        AVG(hour_04_way_time_spontaneous) as hour_04_way_time_spontaneous,
66        AVG(hour_05_way_time_spontaneous) as hour_05_way_time_spontaneous,
67        AVG(hour_06_way_time_spontaneous) as hour_06_way_time_spontaneous,
68        AVG(hour_07_way_time_spontaneous) as hour_07_way_time_spontaneous,
69        AVG(hour_08_way_time_spontaneous) as hour_08_way_time_spontaneous,
70        AVG(hour_09_way_time_spontaneous) as hour_09_way_time_spontaneous,
71        AVG(hour_10_way_time_spontaneous) as hour_10_way_time_spontaneous,
72        AVG(hour_11_way_time_spontaneous) as hour_11_way_time_spontaneous,
73        AVG(hour_12_way_time_spontaneous) as hour_12_way_time_spontaneous,
74        AVG(hour_13_way_time_spontaneous) as hour_13_way_time_spontaneous,
75        AVG(hour_14_way_time_spontaneous) as hour_14_way_time_spontaneous,
76        AVG(hour_15_way_time_spontaneous) as hour_15_way_time_spontaneous,
77        AVG(hour_16_way_time_spontaneous) as hour_16_way_time_spontaneous,
78        AVG(hour_17_way_time_spontaneous) as hour_17_way_time_spontaneous,
79        AVG(hour_18_way_time_spontaneous) as hour_18_way_time_spontaneous,
80        AVG(hour_19_way_time_spontaneous) as hour_19_way_time_spontaneous,
81        AVG(hour_20_way_time_spontaneous) as hour_20_way_time_spontaneous,
82        AVG(hour_21_way_time_spontaneous) as hour_21_way_time_spontaneous,
83        AVG(hour_22_way_time_spontaneous) as hour_22_way_time_spontaneous,
84        AVG(hour_23_way_time_spontaneous) as hour_23_way_time_spontaneous,
85        SUM(hour_00_waiting_count_spontaneous) as hour_00_waiting_count_spontaneous,
86        SUM(hour_01_waiting_count_spontaneous) as hour_01_waiting_count_spontaneous,
87        SUM(hour_02_waiting_count_spontaneous) as hour_02_waiting_count_spontaneous,
88        SUM(hour_03_waiting_count_spontaneous) as hour_03_waiting_count_spontaneous,
89        SUM(hour_04_waiting_count_spontaneous) as hour_04_waiting_count_spontaneous,
90        SUM(hour_05_waiting_count_spontaneous) as hour_05_waiting_count_spontaneous,
91        SUM(hour_06_waiting_count_spontaneous) as hour_06_waiting_count_spontaneous,
92        SUM(hour_07_waiting_count_spontaneous) as hour_07_waiting_count_spontaneous,
93        SUM(hour_08_waiting_count_spontaneous) as hour_08_waiting_count_spontaneous,
94        SUM(hour_09_waiting_count_spontaneous) as hour_09_waiting_count_spontaneous,
95        SUM(hour_10_waiting_count_spontaneous) as hour_10_waiting_count_spontaneous,
96        SUM(hour_11_waiting_count_spontaneous) as hour_11_waiting_count_spontaneous,
97        SUM(hour_12_waiting_count_spontaneous) as hour_12_waiting_count_spontaneous,
98        SUM(hour_13_waiting_count_spontaneous) as hour_13_waiting_count_spontaneous,
99        SUM(hour_14_waiting_count_spontaneous) as hour_14_waiting_count_spontaneous,
100        SUM(hour_15_waiting_count_spontaneous) as hour_15_waiting_count_spontaneous,
101        SUM(hour_16_waiting_count_spontaneous) as hour_16_waiting_count_spontaneous,
102        SUM(hour_17_waiting_count_spontaneous) as hour_17_waiting_count_spontaneous,
103        SUM(hour_18_waiting_count_spontaneous) as hour_18_waiting_count_spontaneous,
104        SUM(hour_19_waiting_count_spontaneous) as hour_19_waiting_count_spontaneous,
105        SUM(hour_20_waiting_count_spontaneous) as hour_20_waiting_count_spontaneous,
106        SUM(hour_21_waiting_count_spontaneous) as hour_21_waiting_count_spontaneous,
107        SUM(hour_22_waiting_count_spontaneous) as hour_22_waiting_count_spontaneous,
108        SUM(hour_23_waiting_count_spontaneous) as hour_23_waiting_count_spontaneous,
109        AVG(hour_00_waiting_time_appointment) as hour_00_waiting_time_appointment,
110        AVG(hour_01_waiting_time_appointment) as hour_01_waiting_time_appointment,
111        AVG(hour_02_waiting_time_appointment) as hour_02_waiting_time_appointment,
112        AVG(hour_03_waiting_time_appointment) as hour_03_waiting_time_appointment,
113        AVG(hour_04_waiting_time_appointment) as hour_04_waiting_time_appointment,
114        AVG(hour_05_waiting_time_appointment) as hour_05_waiting_time_appointment,
115        AVG(hour_06_waiting_time_appointment) as hour_06_waiting_time_appointment,
116        AVG(hour_07_waiting_time_appointment) as hour_07_waiting_time_appointment,
117        AVG(hour_08_waiting_time_appointment) as hour_08_waiting_time_appointment,
118        AVG(hour_09_waiting_time_appointment) as hour_09_waiting_time_appointment,
119        AVG(hour_10_waiting_time_appointment) as hour_10_waiting_time_appointment,
120        AVG(hour_11_waiting_time_appointment) as hour_11_waiting_time_appointment,
121        AVG(hour_12_waiting_time_appointment) as hour_12_waiting_time_appointment,
122        AVG(hour_13_waiting_time_appointment) as hour_13_waiting_time_appointment,
123        AVG(hour_14_waiting_time_appointment) as hour_14_waiting_time_appointment,
124        AVG(hour_15_waiting_time_appointment) as hour_15_waiting_time_appointment,
125        AVG(hour_16_waiting_time_appointment) as hour_16_waiting_time_appointment,
126        AVG(hour_17_waiting_time_appointment) as hour_17_waiting_time_appointment,
127        AVG(hour_18_waiting_time_appointment) as hour_18_waiting_time_appointment,
128        AVG(hour_19_waiting_time_appointment) as hour_19_waiting_time_appointment,
129        AVG(hour_20_waiting_time_appointment) as hour_20_waiting_time_appointment,
130        AVG(hour_21_waiting_time_appointment) as hour_21_waiting_time_appointment,
131        AVG(hour_22_waiting_time_appointment) as hour_22_waiting_time_appointment,
132        AVG(hour_23_waiting_time_appointment) as hour_23_waiting_time_appointment,
133        AVG(hour_00_estimated_waiting_time_appointment) as hour_00_estimated_waiting_time_appointment,
134        AVG(hour_01_estimated_waiting_time_appointment) as hour_01_estimated_waiting_time_appointment,
135        AVG(hour_02_estimated_waiting_time_appointment) as hour_02_estimated_waiting_time_appointment,
136        AVG(hour_03_estimated_waiting_time_appointment) as hour_03_estimated_waiting_time_appointment,
137        AVG(hour_04_estimated_waiting_time_appointment) as hour_04_estimated_waiting_time_appointment,
138        AVG(hour_05_estimated_waiting_time_appointment) as hour_05_estimated_waiting_time_appointment,
139        AVG(hour_06_estimated_waiting_time_appointment) as hour_06_estimated_waiting_time_appointment,
140        AVG(hour_07_estimated_waiting_time_appointment) as hour_07_estimated_waiting_time_appointment,
141        AVG(hour_08_estimated_waiting_time_appointment) as hour_08_estimated_waiting_time_appointment,
142        AVG(hour_09_estimated_waiting_time_appointment) as hour_09_estimated_waiting_time_appointment,
143        AVG(hour_10_estimated_waiting_time_appointment) as hour_10_estimated_waiting_time_appointment,
144        AVG(hour_11_estimated_waiting_time_appointment) as hour_11_estimated_waiting_time_appointment,
145        AVG(hour_12_estimated_waiting_time_appointment) as hour_12_estimated_waiting_time_appointment,
146        AVG(hour_13_estimated_waiting_time_appointment) as hour_13_estimated_waiting_time_appointment,
147        AVG(hour_14_estimated_waiting_time_appointment) as hour_14_estimated_waiting_time_appointment,
148        AVG(hour_15_estimated_waiting_time_appointment) as hour_15_estimated_waiting_time_appointment,
149        AVG(hour_16_estimated_waiting_time_appointment) as hour_16_estimated_waiting_time_appointment,
150        AVG(hour_17_estimated_waiting_time_appointment) as hour_17_estimated_waiting_time_appointment,
151        AVG(hour_18_estimated_waiting_time_appointment) as hour_18_estimated_waiting_time_appointment,
152        AVG(hour_19_estimated_waiting_time_appointment) as hour_19_estimated_waiting_time_appointment,
153        AVG(hour_20_estimated_waiting_time_appointment) as hour_20_estimated_waiting_time_appointment,
154        AVG(hour_21_estimated_waiting_time_appointment) as hour_21_estimated_waiting_time_appointment,
155        AVG(hour_22_estimated_waiting_time_appointment) as hour_22_estimated_waiting_time_appointment,
156        AVG(hour_23_estimated_waiting_time_appointment) as hour_23_estimated_waiting_time_appointment,
157        SUM(hour_00_way_time_appointment * COALESCE(hour_00_way_count_appointment, hour_00_waiting_count_appointment))
158            / NULLIF(SUM(COALESCE(hour_00_way_count_appointment, hour_00_waiting_count_appointment)), 0)
159            as hour_00_way_time_appointment,
160        SUM(hour_01_way_time_appointment * COALESCE(hour_01_way_count_appointment, hour_01_waiting_count_appointment))
161            / NULLIF(SUM(COALESCE(hour_01_way_count_appointment, hour_01_waiting_count_appointment)), 0)
162            as hour_01_way_time_appointment,
163        SUM(hour_02_way_time_appointment * COALESCE(hour_02_way_count_appointment, hour_02_waiting_count_appointment))
164            / NULLIF(SUM(COALESCE(hour_02_way_count_appointment, hour_02_waiting_count_appointment)), 0)
165            as hour_02_way_time_appointment,
166        SUM(hour_03_way_time_appointment * COALESCE(hour_03_way_count_appointment, hour_03_waiting_count_appointment))
167            / NULLIF(SUM(COALESCE(hour_03_way_count_appointment, hour_03_waiting_count_appointment)), 0)
168            as hour_03_way_time_appointment,
169        SUM(hour_04_way_time_appointment * COALESCE(hour_04_way_count_appointment, hour_04_waiting_count_appointment))
170            / NULLIF(SUM(COALESCE(hour_04_way_count_appointment, hour_04_waiting_count_appointment)), 0)
171            as hour_04_way_time_appointment,
172        SUM(hour_05_way_time_appointment * COALESCE(hour_05_way_count_appointment, hour_05_waiting_count_appointment))
173            / NULLIF(SUM(COALESCE(hour_05_way_count_appointment, hour_05_waiting_count_appointment)), 0)
174            as hour_05_way_time_appointment,
175        SUM(hour_06_way_time_appointment * COALESCE(hour_06_way_count_appointment, hour_06_waiting_count_appointment))
176            / NULLIF(SUM(COALESCE(hour_06_way_count_appointment, hour_06_waiting_count_appointment)), 0)
177            as hour_06_way_time_appointment,
178        SUM(hour_07_way_time_appointment * COALESCE(hour_07_way_count_appointment, hour_07_waiting_count_appointment))
179            / NULLIF(SUM(COALESCE(hour_07_way_count_appointment, hour_07_waiting_count_appointment)), 0)
180            as hour_07_way_time_appointment,
181        SUM(hour_08_way_time_appointment * COALESCE(hour_08_way_count_appointment, hour_08_waiting_count_appointment))
182            / NULLIF(SUM(COALESCE(hour_08_way_count_appointment, hour_08_waiting_count_appointment)), 0)
183            as hour_08_way_time_appointment,
184        SUM(hour_09_way_time_appointment * COALESCE(hour_09_way_count_appointment, hour_09_waiting_count_appointment))
185            / NULLIF(SUM(COALESCE(hour_09_way_count_appointment, hour_09_waiting_count_appointment)), 0)
186            as hour_09_way_time_appointment,
187        SUM(hour_10_way_time_appointment * COALESCE(hour_10_way_count_appointment, hour_10_waiting_count_appointment))
188            / NULLIF(SUM(COALESCE(hour_10_way_count_appointment, hour_10_waiting_count_appointment)), 0)
189            as hour_10_way_time_appointment,
190        SUM(hour_11_way_time_appointment * COALESCE(hour_11_way_count_appointment, hour_11_waiting_count_appointment))
191            / NULLIF(SUM(COALESCE(hour_11_way_count_appointment, hour_11_waiting_count_appointment)), 0)
192            as hour_11_way_time_appointment,
193        SUM(hour_12_way_time_appointment * COALESCE(hour_12_way_count_appointment, hour_12_waiting_count_appointment))
194            / NULLIF(SUM(COALESCE(hour_12_way_count_appointment, hour_12_waiting_count_appointment)), 0)
195            as hour_12_way_time_appointment,
196        SUM(hour_13_way_time_appointment * COALESCE(hour_13_way_count_appointment, hour_13_waiting_count_appointment))
197            / NULLIF(SUM(COALESCE(hour_13_way_count_appointment, hour_13_waiting_count_appointment)), 0)
198            as hour_13_way_time_appointment,
199        SUM(hour_14_way_time_appointment * COALESCE(hour_14_way_count_appointment, hour_14_waiting_count_appointment))
200            / NULLIF(SUM(COALESCE(hour_14_way_count_appointment, hour_14_waiting_count_appointment)), 0)
201            as hour_14_way_time_appointment,
202        SUM(hour_15_way_time_appointment * COALESCE(hour_15_way_count_appointment, hour_15_waiting_count_appointment))
203            / NULLIF(SUM(COALESCE(hour_15_way_count_appointment, hour_15_waiting_count_appointment)), 0)
204            as hour_15_way_time_appointment,
205        SUM(hour_16_way_time_appointment * COALESCE(hour_16_way_count_appointment, hour_16_waiting_count_appointment))
206            / NULLIF(SUM(COALESCE(hour_16_way_count_appointment, hour_16_waiting_count_appointment)), 0)
207            as hour_16_way_time_appointment,
208        SUM(hour_17_way_time_appointment * COALESCE(hour_17_way_count_appointment, hour_17_waiting_count_appointment))
209            / NULLIF(SUM(COALESCE(hour_17_way_count_appointment, hour_17_waiting_count_appointment)), 0)
210            as hour_17_way_time_appointment,
211        SUM(hour_18_way_time_appointment * COALESCE(hour_18_way_count_appointment, hour_18_waiting_count_appointment))
212            / NULLIF(SUM(COALESCE(hour_18_way_count_appointment, hour_18_waiting_count_appointment)), 0)
213            as hour_18_way_time_appointment,
214        SUM(hour_19_way_time_appointment * COALESCE(hour_19_way_count_appointment, hour_19_waiting_count_appointment))
215            / NULLIF(SUM(COALESCE(hour_19_way_count_appointment, hour_19_waiting_count_appointment)), 0)
216            as hour_19_way_time_appointment,
217        SUM(hour_20_way_time_appointment * COALESCE(hour_20_way_count_appointment, hour_20_waiting_count_appointment))
218            / NULLIF(SUM(COALESCE(hour_20_way_count_appointment, hour_20_waiting_count_appointment)), 0)
219            as hour_20_way_time_appointment,
220        SUM(hour_21_way_time_appointment * COALESCE(hour_21_way_count_appointment, hour_21_waiting_count_appointment))
221            / NULLIF(SUM(COALESCE(hour_21_way_count_appointment, hour_21_waiting_count_appointment)), 0)
222            as hour_21_way_time_appointment,
223        SUM(hour_22_way_time_appointment * COALESCE(hour_22_way_count_appointment, hour_22_waiting_count_appointment))
224            / NULLIF(SUM(COALESCE(hour_22_way_count_appointment, hour_22_waiting_count_appointment)), 0)
225            as hour_22_way_time_appointment,
226        SUM(hour_23_way_time_appointment * COALESCE(hour_23_way_count_appointment, hour_23_waiting_count_appointment))
227            / NULLIF(SUM(COALESCE(hour_23_way_count_appointment, hour_23_waiting_count_appointment)), 0)
228            as hour_23_way_time_appointment,
229        SUM(COALESCE(hour_00_way_count_appointment, hour_00_waiting_count_appointment))
230            as hour_00_way_count_appointment,
231        SUM(COALESCE(hour_01_way_count_appointment, hour_01_waiting_count_appointment))
232            as hour_01_way_count_appointment,
233        SUM(COALESCE(hour_02_way_count_appointment, hour_02_waiting_count_appointment))
234            as hour_02_way_count_appointment,
235        SUM(COALESCE(hour_03_way_count_appointment, hour_03_waiting_count_appointment))
236            as hour_03_way_count_appointment,
237        SUM(COALESCE(hour_04_way_count_appointment, hour_04_waiting_count_appointment))
238            as hour_04_way_count_appointment,
239        SUM(COALESCE(hour_05_way_count_appointment, hour_05_waiting_count_appointment))
240            as hour_05_way_count_appointment,
241        SUM(COALESCE(hour_06_way_count_appointment, hour_06_waiting_count_appointment))
242            as hour_06_way_count_appointment,
243        SUM(COALESCE(hour_07_way_count_appointment, hour_07_waiting_count_appointment))
244            as hour_07_way_count_appointment,
245        SUM(COALESCE(hour_08_way_count_appointment, hour_08_waiting_count_appointment))
246            as hour_08_way_count_appointment,
247        SUM(COALESCE(hour_09_way_count_appointment, hour_09_waiting_count_appointment))
248            as hour_09_way_count_appointment,
249        SUM(COALESCE(hour_10_way_count_appointment, hour_10_waiting_count_appointment))
250            as hour_10_way_count_appointment,
251        SUM(COALESCE(hour_11_way_count_appointment, hour_11_waiting_count_appointment))
252            as hour_11_way_count_appointment,
253        SUM(COALESCE(hour_12_way_count_appointment, hour_12_waiting_count_appointment))
254            as hour_12_way_count_appointment,
255        SUM(COALESCE(hour_13_way_count_appointment, hour_13_waiting_count_appointment))
256            as hour_13_way_count_appointment,
257        SUM(COALESCE(hour_14_way_count_appointment, hour_14_waiting_count_appointment))
258            as hour_14_way_count_appointment,
259        SUM(COALESCE(hour_15_way_count_appointment, hour_15_waiting_count_appointment))
260            as hour_15_way_count_appointment,
261        SUM(COALESCE(hour_16_way_count_appointment, hour_16_waiting_count_appointment))
262            as hour_16_way_count_appointment,
263        SUM(COALESCE(hour_17_way_count_appointment, hour_17_waiting_count_appointment))
264            as hour_17_way_count_appointment,
265        SUM(COALESCE(hour_18_way_count_appointment, hour_18_waiting_count_appointment))
266            as hour_18_way_count_appointment,
267        SUM(COALESCE(hour_19_way_count_appointment, hour_19_waiting_count_appointment))
268            as hour_19_way_count_appointment,
269        SUM(COALESCE(hour_20_way_count_appointment, hour_20_waiting_count_appointment))
270            as hour_20_way_count_appointment,
271        SUM(COALESCE(hour_21_way_count_appointment, hour_21_waiting_count_appointment))
272            as hour_21_way_count_appointment,
273        SUM(COALESCE(hour_22_way_count_appointment, hour_22_waiting_count_appointment))
274            as hour_22_way_count_appointment,
275        SUM(COALESCE(hour_23_way_count_appointment, hour_23_waiting_count_appointment))
276            as hour_23_way_count_appointment,
277        SUM(hour_00_waiting_count_appointment) as hour_00_waiting_count_appointment,
278        SUM(hour_01_waiting_count_appointment) as hour_01_waiting_count_appointment,
279        SUM(hour_02_waiting_count_appointment) as hour_02_waiting_count_appointment,
280        SUM(hour_03_waiting_count_appointment) as hour_03_waiting_count_appointment,
281        SUM(hour_04_waiting_count_appointment) as hour_04_waiting_count_appointment,
282        SUM(hour_05_waiting_count_appointment) as hour_05_waiting_count_appointment,
283        SUM(hour_06_waiting_count_appointment) as hour_06_waiting_count_appointment,
284        SUM(hour_07_waiting_count_appointment) as hour_07_waiting_count_appointment,
285        SUM(hour_08_waiting_count_appointment) as hour_08_waiting_count_appointment,
286        SUM(hour_09_waiting_count_appointment) as hour_09_waiting_count_appointment,
287        SUM(hour_10_waiting_count_appointment) as hour_10_waiting_count_appointment,
288        SUM(hour_11_waiting_count_appointment) as hour_11_waiting_count_appointment,
289        SUM(hour_12_waiting_count_appointment) as hour_12_waiting_count_appointment,
290        SUM(hour_13_waiting_count_appointment) as hour_13_waiting_count_appointment,
291        SUM(hour_14_waiting_count_appointment) as hour_14_waiting_count_appointment,
292        SUM(hour_15_waiting_count_appointment) as hour_15_waiting_count_appointment,
293        SUM(hour_16_waiting_count_appointment) as hour_16_waiting_count_appointment,
294        SUM(hour_17_waiting_count_appointment) as hour_17_waiting_count_appointment,
295        SUM(hour_18_waiting_count_appointment) as hour_18_waiting_count_appointment,
296        SUM(hour_19_waiting_count_appointment) as hour_19_waiting_count_appointment,
297        SUM(hour_20_waiting_count_appointment) as hour_20_waiting_count_appointment,
298        SUM(hour_21_waiting_count_appointment) as hour_21_waiting_count_appointment,
299        SUM(hour_22_waiting_count_appointment) as hour_22_waiting_count_appointment,
300        SUM(hour_23_waiting_count_appointment) as hour_23_waiting_count_appointment
301    ";
302    const QUERY_READ_DAY = "
303        SELECT
304            standortid,
305            `datum` AS datum,
306            " . self::WAITING_VALUES . "
307        FROM " . self::TABLE . "
308        WHERE
309            `standortid` IN (:scopeid) AND
310            `datum` BETWEEN :datestart AND :dateend
311        GROUP BY `datum`
312        ORDER BY `datum` ASC
313    ";
314
315    //PLEASE REMEMBER THE REALY COOL DYNAMIC VERSION
316    const QUERY_READ_MONTH = "
317        SELECT
318            standortid,
319            DATE_FORMAT(`datum`, '%Y-%m') as datum,
320            " . self::WAITING_VALUES . "
321        FROM " . self::TABLE . "
322        WHERE
323            `standortid` = :scopeid AND
324            `datum` BETWEEN :datestart AND :dateend
325        GROUP BY DATE_FORMAT(`datum`, '%Y-%m')
326        ORDER BY DATE_FORMAT(`datum`, '%Y-%m') ASC
327    ";
328
329    const QUERY_READ_QUARTER = "
330        SELECT
331            standortid,
332            CONCAT(YEAR(w.`datum`),'-',QUARTER(w.`datum`)) as datum,
333            " . self::WAITING_VALUES . "
334        FROM " . self::TABLE . " w
335        WHERE
336            w.`standortid` = :scopeid AND
337            w.`datum` BETWEEN :datestart AND :dateend
338        GROUP BY CONCAT(YEAR(w.`datum`),'-',QUARTER(w.`datum`))
339        ORDER BY CONCAT(YEAR(w.`datum`),'-',QUARTER(w.`datum`)) ASC
340    ";
341
342    const QUERY_SUBJECTS = '
343        SELECT
344            scope.`StandortID` as subject,
345            periodstart,
346            periodend,
347            CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
348        FROM ' . \BO\Zmsbackend\Query\Scope::TABLE . ' AS scope
349            INNER JOIN
350              (
351          SELECT
352            w.standortid as scopeid,
353            MIN(w.`datum`) AS periodstart,
354            MAX(w.`datum`) AS periodend
355          FROM ' . self::TABLE . ' w
356          group by scopeid
357        )
358            maxAndminDate ON maxAndminDate.`scopeid` = scope.`StandortID`
359        GROUP BY scope.`StandortID`
360        ORDER BY scope.`StandortID` ASC, periodstart DESC
361    ';
362
363    const QUERY_PERIODLIST_DAY = '
364        SELECT
365            `datum`
366        FROM ' . self::TABLE . ' AS w
367        WHERE `standortid` = :scopeid
368        ORDER BY `datum` ASC
369    ';
370
371    const QUERY_PERIODLIST_MONTH = '
372        SELECT DISTINCT DATE_FORMAT(`datum`,"%Y-%m") AS date
373        FROM ' . self::TABLE . ' AS w
374        WHERE `standortid` = :scopeid
375        ORDER BY `datum` ASC
376    ';
377
378    const QUERY_CREATE = '
379        INSERT INTO ' . self::TABLE . ' SET
380            `standortid` = :scopeid,
381            `datum` = :date
382    ';
383
384    /**
385     * For backward compatibility on db table optimization, we have to convert the field name
386     * Drawback: No prepared statement using the date
387     */
388    public static function getQuerySelectByDateTime(\DateTimeInterface $date, bool $withAppointment = false): string
389    {
390        $hourSuffix = $withAppointment ? 'appointment' : 'spontaneous';
391
392        $query = sprintf(
393            "SELECT
394                `hour_%s_estimated_waiting_time_%s` AS waitingcalculated,
395                `hour_%s_waiting_count_%s` AS waitingcount,
396                `hour_%s_waiting_time_%s` AS waitingtime,
397                `hour_%s_way_time_%s` AS waytime
398             FROM %s
399             WHERE `standortid` = :scopeid
400                AND `datum` = :date
401                AND :hour IS NOT NULL
402            ",
403            $date->format('H'),
404            $hourSuffix,
405            $date->format('H'),
406            $hourSuffix,
407            $date->format('H'),
408            $hourSuffix,
409            $date->format('H'),
410            $hourSuffix,
411            self::TABLE
412        );
413        return $query;
414    }
415
416    /**
417     * For backward compatibility on db table optimization, we have to convert the field name
418     * Drawback: No prepared statement using the date
419     */
420    public static function getQueryUpdateByDateTime(\DateTimeInterface $date, bool $withAppointment = false): string
421    {
422        $hourSuffix = $withAppointment ? 'appointment' : 'spontaneous';
423
424        $query = sprintf(
425            "UPDATE %s
426             SET
427                `hour_%s_estimated_waiting_time_%s`= :waitingcalculated,
428                `hour_%s_waiting_count_%s` = :waitingcount,
429                `hour_%s_waiting_time_%s` = :waitingtime,
430                `hour_%s_way_time_%s` = :waytime
431             WHERE `standortid` = :scopeid
432                AND `datum` = :date
433                AND :hour IS NOT NULL
434            ",
435            self::TABLE,
436            $date->format('H'),
437            $hourSuffix,
438            $date->format('H'),
439            $hourSuffix,
440            $date->format('H'),
441            $hourSuffix,
442            $date->format('H'),
443            $hourSuffix
444        );
445        return $query;
446    }
447}