Lines
75.00%
39 / 52
Functions and Methods
55.55%
5 / 9
Classes and Traits
0.00%
0 / 1
| Name | Lines | Functions and Methods | CRAP | Classes and Traits | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Slot | 75.00% | 39 / 52 | 55.55% | 5 / 9 | 21.52 | 0.00% | 0 / 1 | |||
| getEntityMapping | 0.00% | 0 / 2 | 0.00% | 0 / 1 | 2 | |||||
| reverseEntityMapping | 100.00% | 13 / 13 | 100.00% | 1 / 1 | 3 | |||||
| addConditionSlotId | 0.00% | 0 / 2 | 0.00% | 0 / 1 | 2 | |||||
| insertAncestorChainsSql | 100.00% | 3 / 3 | 100.00% | 1 / 1 | 1 | |||||
| upsertSlotRowsSql | 0.00% | 0 / 8 | 0.00% | 0 / 1 | 6 | |||||
| insertSlotRowsSql | 100.00% | 4 / 4 | 100.00% | 1 / 1 | 1 | |||||
| packSlotRow | 83.33% | 5 / 6 | 0.00% | 0 / 1 | 3.04 | |||||
| quotedColumns | 100.00% | 1 / 1 | 100.00% | 1 / 1 | 1 | |||||
| chunkedStatements | 100.00% | 13 / 13 | 100.00% | 1 / 1 | 4 | |||||
| 1 | <?php | |
| 2 | ||
| 3 | namespace BO\Zmsbackend\Slot\Repository; | |
| 4 | ||
| 5 | class Slot extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface | |
| 6 | { | |
| 7 | /** | |
| 8 | * | |
| 9 | * @var String TABLE mysql table reference | |
| 10 | */ | |
| 11 | const string TABLE = 'slot'; | |
| 12 | ||
| 13 | const string QUERY_OPTIMIZE_SLOT = 'OPTIMIZE TABLE slot;'; | |
| 14 | const string QUERY_OPTIMIZE_SLOT_HIERA = 'OPTIMIZE TABLE slot_hiera;'; | |
| 15 | const string QUERY_OPTIMIZE_SLOT_PROCESS = 'OPTIMIZE TABLE slot_proces;'; | |
| 16 | const string QUERY_OPTIMIZE_PROCESS = 'OPTIMIZE TABLE buerger;'; | |
| 17 | ||
| 18 | const string QUERY_LAST_CHANGED = 'SELECT MAX(updateTimestamp) AS dateString FROM slot;'; | |
| 19 | ||
| 20 | const string QUERY_LAST_CHANGED_AVAILABILITY = ' | |
| 21 | SELECT MAX(updateTimestamp) AS dateString FROM slot WHERE availabilityID = :availabilityID AND status="free";'; | |
| 22 | ||
| 23 | const string QUERY_LAST_IN_AVAILABILITY = ' | |
| 24 | SELECT CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) AS dateString | |
| 25 | FROM slot | |
| 26 | WHERE availabilityID = :availabilityID AND status="free" | |
| 27 | ORDER BY year DESC, month DESC, day DESC | |
| 28 | LIMIT 1;'; | |
| 29 | ||
| 30 | const string QUERY_OLDEST_VERSION_IN_AVAILABILITY = ' | |
| 31 | SELECT `version` | |
| 32 | FROM slot | |
| 33 | WHERE availabilityID = :availabilityID AND status="free" | |
| 34 | ORDER BY `version` ASC | |
| 35 | LIMIT 1;'; | |
| 36 | ||
| 37 | const string QUERY_LAST_CHANGED_SCOPE = ' | |
| 38 | SELECT MAX(updateTimestamp) AS dateString FROM slot WHERE scopeID = :scopeID;'; | |
| 39 | ||
| 40 | /** | |
| 41 | * Active (free or full, not cancelled) slots of one scope and day with their current occupancy, | |
| 42 | * used to decide which of several overlapping availabilities gets an appointment. | |
| 43 | * Full slots are needed, otherwise a full slot looks like a gap in its availability. | |
| 44 | */ | |
| 45 | const string QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY = ' | |
| 46 | SELECT | |
| 47 | s.slotID, | |
| 48 | s.availabilityID, | |
| 49 | s.time, | |
| 50 | s.slotTimeInMinutes, | |
| 51 | s.public, | |
| 52 | s.intern, | |
| 53 | COUNT(sp.processID) AS confirmed | |
| 54 | FROM slot s | |
| 55 | LEFT JOIN slot_process sp ON sp.slotID = s.slotID | |
| 56 | WHERE | |
| 57 | s.scopeID = :scopeID | |
| 58 | AND s.year = :year | |
| 59 | AND s.month = :month | |
| 60 | AND s.day = :day | |
| 61 | AND s.status IN ("free", "full") | |
| 62 | GROUP BY s.slotID | |
| 63 | ORDER BY s.availabilityID, s.time | |
| 64 | '; | |
| 65 | ||
| 66 | const string QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING = ' | |
| 67 | SELECT | |
| 68 | b.BuergerID, | |
| 69 | b.StandortID, | |
| 70 | b.Datum, | |
| 71 | b.Uhrzeit, | |
| 72 | b.istFolgeterminvon, | |
| 73 | b.hatFolgetermine, | |
| 74 | b.apiClientID, | |
| 75 | ac.accesslevel | |
| 76 | FROM buerger b | |
| 77 | LEFT JOIN apiclient ac ON ac.apiClientID = b.apiClientID | |
| 78 | WHERE b.BuergerID = :processId | |
| 79 | '; | |
| 80 | ||
| 81 | const string QUERY_SELECT_FOLLOWING_PROCESS_IDS = ' | |
| 82 | SELECT b.BuergerID | |
| 83 | FROM buerger b | |
| 84 | WHERE b.istFolgeterminvon = :processId | |
| 85 | ORDER BY b.Uhrzeit, b.BuergerID | |
| 86 | '; | |
| 87 | ||
| 88 | const string QUERY_SELECT_MAPPED_AVAILABILITY_BY_PROCESS = ' | |
| 89 | SELECT s.availabilityID | |
| 90 | FROM slot_process sp | |
| 91 | INNER JOIN slot s ON s.slotID = sp.slotID | |
| 92 | WHERE | |
| 93 | sp.processID = :processId | |
| 94 | AND s.status IN ("free", "full") | |
| 95 | LIMIT 1 | |
| 96 | '; | |
| 97 | ||
| 98 | const string QUERY_SELECT_BY_SCOPE_AND_DAY = ' | |
| 99 | SELECT | |
| 100 | s.* | |
| 101 | FROM slot s | |
| 102 | WHERE | |
| 103 | s.scopeID = :scopeID | |
| 104 | AND s.year = :year | |
| 105 | AND s.month = :month | |
| 106 | AND s.day = :day | |
| 107 | '; | |
| 108 | ||
| 109 | const string QUERY_SELECT_MISSING_PROCESS = ' | |
| 110 | SELECT DISTINCT | |
| 111 | b.BuergerID, | |
| 112 | b.istFolgeterminvon | |
| 113 | FROM slot s | |
| 114 | INNER JOIN buerger b ON | |
| 115 | s.scopeID = b.StandortID | |
| 116 | AND s.status = "free" | |
| 117 | AND ( | |
| 118 | ( | |
| 119 | s.slotTimeInMinutes = 5 | |
| 120 | AND (b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0) | |
| 121 | AND ( | |
| 122 | ( | |
| 123 | s.year = YEAR(b.Datum) | |
| 124 | AND s.month = MONTH(b.Datum) | |
| 125 | AND s.day = DAY(b.Datum) | |
| 126 | AND s.time < ADDTIME( | |
| 127 | b.Uhrzeit, | |
| 128 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 129 | ) | |
| 130 | AND ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) > b.Uhrzeit | |
| 131 | ) | |
| 132 | OR ( | |
| 133 | ADDTIME( | |
| 134 | b.Uhrzeit, | |
| 135 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 136 | ) >= "24:00:00" | |
| 137 | AND STR_TO_DATE(CONCAT(s.year, "-", s.month, "-", s.day), "%Y-%m-%d") | |
| 138 | = DATE_ADD(b.Datum, INTERVAL 1 DAY) | |
| 139 | AND s.time < SUBTIME( | |
| 140 | ADDTIME( | |
| 141 | b.Uhrzeit, | |
| 142 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 143 | ), | |
| 144 | "24:00:00" | |
| 145 | ) | |
| 146 | ) | |
| 147 | ) | |
| 148 | ) | |
| 149 | OR ( | |
| 150 | IFNULL(s.slotTimeInMinutes, 0) <> 5 | |
| 151 | AND s.year = YEAR(b.Datum) | |
| 152 | AND s.month = MONTH(b.Datum) | |
| 153 | AND s.day = DAY(b.Datum) | |
| 154 | AND b.Uhrzeit BETWEEN s.time | |
| 155 | AND SEC_TO_TIME(TIME_TO_SEC(s.time) + (s.slotTimeInMinutes * 60) - 1) | |
| 156 | ) | |
| 157 | ) | |
| 158 | LEFT JOIN slot_process sp ON sp.processID = b.BuergerID | |
| 159 | AND (IFNULL(s.slotTimeInMinutes, 0) <> 5 OR sp.slotID = s.slotID) | |
| 160 | WHERE | |
| 161 | sp.processID IS NULL | |
| 162 | '; | |
| 163 | const string QUERY_SELECT_MISSING_PROCESS_BY_SCOPE = ' | |
| 164 | AND s.scopeID = :scopeID | |
| 165 | '; | |
| 166 | ||
| 167 | /** | |
| 168 | * Book every step of this appointment immediately, on the given availability only. | |
| 169 | * hatFolgetermine is the extra-step count, so one process row covers the whole visit. | |
| 170 | */ | |
| 171 | const string QUERY_INSERT_SLOT_PROCESS_ID = ' | |
| 172 | INSERT INTO slot_process (slotID, processID, updateTimestamp) | |
| 173 | SELECT | |
| 174 | s.slotID, | |
| 175 | b.BuergerID, | |
| 176 | NOW() | |
| 177 | FROM buerger b | |
| 178 | INNER JOIN slot s ON s.scopeID = b.StandortID | |
| 179 | AND s.status IN ("free", "full") | |
| 180 | AND ( | |
| 181 | ( | |
| 182 | s.year = YEAR(b.Datum) | |
| 183 | AND s.month = MONTH(b.Datum) | |
| 184 | AND s.day = DAY(b.Datum) | |
| 185 | AND s.time < ADDTIME( | |
| 186 | b.Uhrzeit, | |
| 187 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 188 | ) | |
| 189 | AND ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) > b.Uhrzeit | |
| 190 | ) | |
| 191 | OR ( | |
| 192 | ADDTIME( | |
| 193 | b.Uhrzeit, | |
| 194 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 195 | ) >= "24:00:00" | |
| 196 | AND s.year = YEAR(DATE_ADD(b.Datum, INTERVAL 1 DAY)) | |
| 197 | AND s.month = MONTH(DATE_ADD(b.Datum, INTERVAL 1 DAY)) | |
| 198 | AND s.day = DAY(DATE_ADD(b.Datum, INTERVAL 1 DAY)) | |
| 199 | AND s.time < SUBTIME( | |
| 200 | ADDTIME( | |
| 201 | b.Uhrzeit, | |
| 202 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 203 | ), | |
| 204 | "24:00:00" | |
| 205 | ) | |
| 206 | ) | |
| 207 | ) | |
| 208 | WHERE | |
| 209 | b.BuergerID = :processId | |
| 210 | AND s.availabilityID = :availabilityId | |
| 211 | '; | |
| 212 | const string QUERY_DELETE_SLOT_PROCESS_CANCELLED = ' | |
| 213 | DELETE sp | |
| 214 | FROM slot_process sp LEFT JOIN slot s USING (slotID) | |
| 215 | WHERE (s.status = "cancelled" OR s.status IS NULL) | |
| 216 | '; | |
| 217 | const string QUERY_DELETE_SLOT_PROCESS_CANCELLED_BY_SCOPE = ' | |
| 218 | AND s.scopeID = :scopeID | |
| 219 | '; | |
| 220 | ||
| 221 | ||
| 222 | const string QUERY_UPDATE_SLOT_MISSING_AVAILABILITY_BY_SCOPE = ' | |
| 223 | UPDATE | |
| 224 | slot s | |
| 225 | LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID | |
| 226 | SET s.status = "cancelled" | |
| 227 | WHERE | |
| 228 | ( | |
| 229 | a.OeffnungszeitID IS NULL | |
| 230 | OR a.end_date < :dateString | |
| 231 | ) | |
| 232 | AND s.scopeID = :scopeID | |
| 233 | '; | |
| 234 | ||
| 235 | const string QUERY_UPDATE_SLOT_MISSING_AVAILABILITY = ' | |
| 236 | UPDATE | |
| 237 | slot s | |
| 238 | LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID | |
| 239 | SET s.status = "cancelled" | |
| 240 | WHERE | |
| 241 | a.OeffnungszeitID IS NULL | |
| 242 | OR a.end_date < :dateString | |
| 243 | '; | |
| 244 | ||
| 245 | const string QUERY_SELECT_DELETABLE_SLOT_PROCESS = ' | |
| 246 | SELECT sp.slotID AS slotID, sp.processID AS processID | |
| 247 | FROM slot_process sp | |
| 248 | LEFT JOIN buerger b ON sp.processID = b.BuergerID | |
| 249 | LEFT JOIN slot s ON sp.slotID = s.slotID | |
| 250 | WHERE ( | |
| 251 | b.BuergerID IS NULL | |
| 252 | OR ( | |
| 253 | s.slotTimeInMinutes = 5 | |
| 254 | AND b.istFolgeterminvon IS NOT NULL | |
| 255 | AND b.istFolgeterminvon <> 0 | |
| 256 | ) | |
| 257 | OR ( | |
| 258 | b.updateTimestamp > sp.updateTimestamp | |
| 259 | AND ( | |
| 260 | s.scopeID != b.StandortID | |
| 261 | OR ( | |
| 262 | NOT ( | |
| 263 | s.year = YEAR(b.Datum) | |
| 264 | AND s.month = MONTH(b.Datum) | |
| 265 | AND s.day = DAY(b.Datum) | |
| 266 | ) | |
| 267 | AND NOT ( | |
| 268 | s.slotTimeInMinutes = 5 | |
| 269 | AND (b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0) | |
| 270 | AND ADDTIME( | |
| 271 | b.Uhrzeit, | |
| 272 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 273 | ) >= "24:00:00" | |
| 274 | AND STR_TO_DATE(CONCAT(s.year, "-", s.month, "-", s.day), "%Y-%m-%d") | |
| 275 | = DATE_ADD(b.Datum, INTERVAL 1 DAY) | |
| 276 | AND s.time < SUBTIME( | |
| 277 | ADDTIME( | |
| 278 | b.Uhrzeit, | |
| 279 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 280 | ), | |
| 281 | "24:00:00" | |
| 282 | ) | |
| 283 | ) | |
| 284 | ) | |
| 285 | OR ( | |
| 286 | s.slotTimeInMinutes = 5 | |
| 287 | AND s.year = YEAR(b.Datum) | |
| 288 | AND s.month = MONTH(b.Datum) | |
| 289 | AND s.day = DAY(b.Datum) | |
| 290 | AND ( | |
| 291 | s.time >= ADDTIME( | |
| 292 | b.Uhrzeit, | |
| 293 | SEC_TO_TIME((IFNULL(b.hatFolgetermine, 0) + 1) * s.slotTimeInMinutes * 60) | |
| 294 | ) | |
| 295 | OR ADDTIME(s.time, SEC_TO_TIME(s.slotTimeInMinutes * 60)) <= b.Uhrzeit | |
| 296 | ) | |
| 297 | ) | |
| 298 | OR ( | |
| 299 | IFNULL(s.slotTimeInMinutes, 0) <> 5 | |
| 300 | AND b.Uhrzeit NOT BETWEEN s.time | |
| 301 | AND SEC_TO_TIME(TIME_TO_SEC(s.time) + (s.slotTimeInMinutes * 60) - 1) | |
| 302 | ) | |
| 303 | ) | |
| 304 | ) | |
| 305 | ) | |
| 306 | '; | |
| 307 | const string QUERY_SELECT_DELETABLE_SLOT_PROCESS_BY_SCOPE = ' | |
| 308 | AND b.StandortID = :scopeID | |
| 309 | '; | |
| 310 | ||
| 311 | const string QUERY_DELETE_SLOT_PROCESS_ID = ' | |
| 312 | DELETE sp | |
| 313 | FROM slot_process sp | |
| 314 | WHERE sp.processID = :processId | |
| 315 | '; | |
| 316 | ||
| 317 | const string QUERY_UPDATE_SLOT_STATUS = " | |
| 318 | UPDATE slot | |
| 319 | LEFT JOIN ( | |
| 320 | SELECT s.slotID, | |
| 321 | IF(s.status IN ('free', 'full'), IF(IFNULL(COUNT(p.slotID), 0) < intern, 'free', 'full'), s.status) newstatus | |
| 322 | FROM slot s | |
| 323 | LEFT JOIN slot_process p ON s.slotID = p.slotID | |
| 324 | GROUP BY s.slotID | |
| 325 | ) calc ON slot.slotID = calc.slotID | |
| 326 | SET | |
| 327 | slot.status = calc.newstatus | |
| 328 | WHERE slot.status != calc.newstatus | |
| 329 | "; | |
| 330 | ||
| 331 | const string QUERY_SELECT_SLOT = ' | |
| 332 | SELECT slotID FROM slot WHERE | |
| 333 | scopeID = :scopeID | |
| 334 | AND year = :year | |
| 335 | AND month = :month | |
| 336 | AND day = :day | |
| 337 | AND time = :time | |
| 338 | AND availabilityID = :availabilityID | |
| 339 | LIMIT 1 | |
| 340 | '; | |
| 341 | ||
| 342 | const string QUERY_SELECT_SLOTS_IN_RANGE = ' | |
| 343 | SELECT slotID, time, year, month, day FROM slot WHERE | |
| 344 | scopeID = :scopeID | |
| 345 | AND availabilityID = :availabilityID | |
| 346 | AND year >= :yearFrom | |
| 347 | AND year <= :yearTo | |
| 348 | '; | |
| 349 | ||
| 350 | /** | |
| 351 | * Columns written for one slot row, apart from slotID. | |
| 352 | * | |
| 353 | * @var list<string> | |
| 354 | */ | |
| 355 | const array SLOT_WRITE_COLUMNS = [ | |
| 356 | 'scopeID', | |
| 357 | 'availabilityID', | |
| 358 | 'version', | |
| 359 | 'year', | |
| 360 | 'month', | |
| 361 | 'day', | |
| 362 | 'time', | |
| 363 | 'public', | |
| 364 | 'intern', | |
| 365 | 'status', | |
| 366 | 'slotTimeInMinutes', | |
| 367 | 'createTimestamp', | |
| 368 | ]; | |
| 369 | ||
| 370 | const string QUERY_CREATE_CALC_SLOT_ORDER = ' | |
| 371 | CREATE TEMPORARY TABLE IF NOT EXISTS `zms_calc_slot_order` ( | |
| 372 | `dayKey` CHAR(10) NOT NULL, | |
| 373 | `sequence` SMALLINT UNSIGNED NOT NULL, | |
| 374 | `slotID` INT UNSIGNED NOT NULL, | |
| 375 | PRIMARY KEY (`dayKey`, `sequence`) | |
| 376 | ) ENGINE=MEMORY | |
| 377 | '; | |
| 378 | ||
| 379 | const string QUERY_DELETE_CALC_SLOT_ORDER = 'DELETE FROM `zms_calc_slot_order`'; | |
| 380 | ||
| 381 | const string QUERY_INSERT_CALC_SLOT_ORDER = ' | |
| 382 | INSERT INTO `zms_calc_slot_order` (`dayKey`, `sequence`, `slotID`) VALUES | |
| 383 | '; | |
| 384 | ||
| 385 | const string QUERY_DELETE_HIERA_FOR_CALC_ORDER = ' | |
| 386 | DELETE `hiera` FROM `slot_hiera` `hiera` | |
| 387 | INNER JOIN `zms_calc_slot_order` `orderedSlot` ON `orderedSlot`.`slotID` = `hiera`.`slotID` | |
| 388 | '; | |
| 389 | ||
| 390 | /** | |
| 391 | * Lock multiple appointment slots in one statement. | |
| 392 | * Placeholders for time IN (...) are injected by Slot::lockSlotsForAppointment. | |
| 393 | * ORDER BY slotID prefers a deterministic primary-key lock order. | |
| 394 | */ | |
| 395 | public const string QUERY_SELECT_SLOTS_FOR_UPDATE = ' | |
| 396 | SELECT slotID FROM slot WHERE | |
| 397 | scopeID = ? | |
| 398 | AND availabilityID = ? | |
| 399 | AND year = ? | |
| 400 | AND month = ? | |
| 401 | AND day = ? | |
| 402 | AND time IN (%s) | |
| 403 | ORDER BY slotID ASC | |
| 404 | FOR UPDATE | |
| 405 | '; | |
| 406 | ||
| 407 | /** | |
| 408 | * Lock the slots of several overlapping availabilities in one statement (consistent slotID order) | |
| 409 | */ | |
| 410 | public const string QUERY_SELECT_CANDIDATE_SLOTS_FOR_UPDATE = ' | |
| 411 | SELECT slotID FROM slot WHERE | |
| 412 | scopeID = ? | |
| 413 | AND year = ? | |
| 414 | AND month = ? | |
| 415 | AND day = ? | |
| 416 | AND availabilityID IN (%s) | |
| 417 | AND time IN (%s) | |
| 418 | ORDER BY slotID ASC | |
| 419 | FOR UPDATE | |
| 420 | '; | |
| 421 | ||
| 422 | const string QUERY_CANCEL_AVAILABILITY = ' | |
| 423 | UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID | |
| 424 | '; | |
| 425 | ||
| 426 | const string QUERY_CANCEL_AVAILABILITY_BEFORE_BOOKABLE = ' | |
| 427 | UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID | |
| 428 | AND CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) < :providedDate | |
| 429 | '; | |
| 430 | ||
| 431 | const string QUERY_CANCEL_AVAILABILITY_AFTER_BOOKABLE = ' | |
| 432 | UPDATE slot SET status = "cancelled" WHERE availabilityID = :availabilityID | |
| 433 | AND CONCAT(year, "-", LPAD(month, 2, "0"), "-", LPAD(day, 2, "0")) > :providedDate | |
| 434 | '; | |
| 435 | ||
| 436 | const string QUERY_CANCEL_SLOT_OLD_BY_SCOPE = ' | |
| 437 | UPDATE slot SET status = "cancelled" | |
| 438 | WHERE scopeID = :scopeID AND ( | |
| 439 | (year < :year) | |
| 440 | OR (year = :year AND month < :month) | |
| 441 | OR (year = :year AND month = :month AND day <= :day AND time < :time) | |
| 442 | ) | |
| 443 | '; | |
| 444 | ||
| 445 | const string QUERY_CANCEL_SLOT_OLD = ' | |
| 446 | UPDATE slot SET status = "cancelled" | |
| 447 | WHERE (year < :year) | |
| 448 | OR (year = :year AND month < :month) | |
| 449 | OR (year = :year AND month = :month AND day <= :day AND time < :time) | |
| 450 | '; | |
| 451 | ||
| 452 | const string QUERY_DELETE_SLOT_OLD = ' | |
| 453 | DELETE FROM slot | |
| 454 | WHERE (year < :year) | |
| 455 | OR (year = :year AND month < :month) | |
| 456 | OR (year = :year AND month = :month AND day < :day) | |
| 457 | '; | |
| 458 | ||
| 459 | const string QUERY_DELETE_SLOT_HIERA = ' | |
| 460 | DELETE sh | |
| 461 | FROM slot_hiera sh LEFT JOIN slot s USING(slotID) | |
| 462 | WHERE s.slotID IS NULL | |
| 463 | '; | |
| 464 | ||
| 465 | ||
| 466 | /** | |
| 467 | * @return array | |
| 468 | * | |
| 469 | */ | |
| 470 | #[\Override] | |
| 471 | public function getEntityMapping() | |
| 472 | { | |
| 473 | return [ | |
| 474 | ]; | |
| 475 | } | |
| 476 | ||
| 477 | /** | |
| 478 | * @return (mixed|string)[] | |
| 479 | * | |
| 480 | */ | |
| 481 | public function reverseEntityMapping( | |
| 482 | \BO\Zmsentities\Slot $slot, | |
| 483 | \BO\Zmsentities\Availability $availability, | |
| 484 | \DateTimeInterface $date | |
| 485 | ): array { | |
| 486 | $data = array(); | |
| 487 | $data['scopeID'] = $availability->scope->id; | |
| 488 | $data['availabilityID'] = $availability->id; | |
| 489 | $data['version'] = $availability->version; | |
| 490 | $data['year'] = $date->format('Y'); | |
| 491 | $data['month'] = $date->format('m'); | |
| 492 | $data['day'] = $date->format('d'); | |
| 493 | $data['time'] = $slot->getTimeString(); | |
| 494 | $data['public'] = isset($slot['public']) ? $slot['public'] : $availability->workstationCount['public']; | |
| 495 | $data['intern'] = isset($slot['intern']) ? $slot['intern'] : $availability->workstationCount['intern']; | |
| 496 | $data['status'] = $slot->status; | |
| 497 | $data['slotTimeInMinutes'] = $availability->slotTimeInMinutes; | |
| 498 | return $data; | |
| 499 | } | |
| 500 | ||
| 501 | public function addConditionSlotId(string|int $slotID): static | |
| 502 | { | |
| 503 | $this->query->where('slot.slotID', '=', $slotID); | |
| 504 | return $this; | |
| 505 | } | |
| 506 | ||
| 507 | /** | |
| 508 | * Ancestor level 1 is the slot itself. Higher levels are the earlier slots that day. | |
| 509 | */ | |
| 510 | public static function insertAncestorChainsSql(int $maximumEarlierSlots): string | |
| 511 | { | |
| 512 | return ' | |
| 513 | INSERT INTO `slot_hiera` (`slotID`, `ancestorID`, `ancestorLevel`) | |
| 514 | SELECT `slotRow`.`slotID`, `ancestorRow`.`slotID`, `slotRow`.`sequence` - `ancestorRow`.`sequence` + 1 | |
| 515 | FROM `zms_calc_slot_order` `slotRow` | |
| 516 | INNER JOIN `zms_calc_slot_order` `ancestorRow` | |
| 517 | ON `ancestorRow`.`dayKey` = `slotRow`.`dayKey` | |
| 518 | AND `ancestorRow`.`sequence` <= `slotRow`.`sequence` | |
| 519 | AND `ancestorRow`.`sequence` + ' . $maximumEarlierSlots . ' >= `slotRow`.`sequence` | |
| 520 | '; | |
| 521 | } | |
| 522 | ||
| 523 | /** | |
| 524 | * @return array{prefix: string, suffix: string} | |
| 525 | */ | |
| 526 | public static function upsertSlotRowsSql(): array | |
| 527 | { | |
| 528 | $columns = array_merge(['slotID'], self::SLOT_WRITE_COLUMNS); | |
| 529 | $assignments = []; | |
| 530 | foreach (self::SLOT_WRITE_COLUMNS as $column) { | |
| 531 | $assignments[] = sprintf('`%s` = VALUES(`%s`)', $column, $column); | |
| 532 | } | |
| 533 | ||
| 534 | return [ | |
| 535 | 'prefix' => 'INSERT INTO `slot` (' . self::quotedColumns($columns) . ') VALUES ', | |
| 536 | 'suffix' => ' ON DUPLICATE KEY UPDATE ' . implode(', ', $assignments), | |
| 537 | ]; | |
| 538 | } | |
| 539 | ||
| 540 | /** | |
| 541 | * @return array{prefix: string, suffix: string} | |
| 542 | */ | |
| 543 | public static function insertSlotRowsSql(): array | |
| 544 | { | |
| 545 | return [ | |
| 546 | 'prefix' => 'INSERT INTO `slot` (' . self::quotedColumns(self::SLOT_WRITE_COLUMNS) . ') VALUES ', | |
| 547 | 'suffix' => '', | |
| 548 | ]; | |
| 549 | } | |
| 550 | ||
| 551 | /** | |
| 552 | * @param array<string, int|string> $values | |
| 553 | * @return list<int|string> | |
| 554 | */ | |
| 555 | public static function packSlotRow(array $values, ?int $slotId = null): array | |
| 556 | { | |
| 557 | $packedRow = []; | |
| 558 | foreach (self::SLOT_WRITE_COLUMNS as $column) { | |
| 559 | $packedRow[] = $values[$column]; | |
| 560 | } | |
| 561 | if ($slotId !== null) { | |
| 562 | array_unshift($packedRow, $slotId); | |
| 563 | } | |
| 564 | ||
| 565 | return $packedRow; | |
| 566 | } | |
| 567 | ||
| 568 | /** | |
| 569 | * @param list<string> $columns | |
| 570 | */ | |
| 571 | public static function quotedColumns(array $columns): string | |
| 572 | { | |
| 573 | return implode(',', array_map(static fn (string $column): string => '`' . $column . '`', $columns)); | |
| 574 | } | |
| 575 | ||
| 576 | /** | |
| 577 | * @param list<list<int|string>> $rows | |
| 578 | * @return list<array{sql: string, parameters: list<int|string>}> | |
| 579 | */ | |
| 580 | public static function chunkedStatements(string $prefix, string $suffix, array $rows): array | |
| 581 | { | |
| 582 | $statements = []; | |
| 583 | foreach (array_chunk($rows, 1000) as $chunk) { | |
| 584 | $columnCount = count($chunk[0]); | |
| 585 | $rowPlaceholder = '(' . implode(',', array_fill(0, $columnCount, '?')) . ')'; | |
| 586 | $parameters = []; | |
| 587 | foreach ($chunk as $row) { | |
| 588 | foreach ($row as $value) { | |
| 589 | $parameters[] = $value; | |
| 590 | } | |
| 591 | } | |
| 592 | $statements[] = [ | |
| 593 | 'sql' => $prefix . implode(',', array_fill(0, count($chunk), $rowPlaceholder)) . $suffix, | |
| 594 | 'parameters' => $parameters, | |
| 595 | ]; | |
| 596 | } | |
| 597 | ||
| 598 | return $statements; | |
| 599 | } | |
| 600 | } |