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 | ||
| 3 | namespace BO\Zmsbackend\Exchange\Repository; | |
| 4 | ||
| 5 | class 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 | } |