| 5 | | class ExchangeWaitingscope extends \BO\Zmsbackend\Query\Base |
| 6 | | { |
| 7 | | |
| 8 | | |
| 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 | | |
| 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 | | |
| 386 | | |
| 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 | | |
| 418 | | |
| 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 | | } |