| 5 | | class Slot extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface |
| 6 | | { |
| 7 | | |
| 8 | | |
| 9 | | |
| 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 | | |
| 42 | | |
| 43 | | |
| 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 | | |
| 169 | | |
| 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 | | |
| 352 | | |
| 353 | | |
| 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 | | |
| 392 | | |
| 393 | | |
| 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 | | |
| 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 | | |
| 468 | | |
| 469 | | |
| 470 | | #[\Override] |
| 471 | | public function getEntityMapping() |
| 472 | | { |
| 473 | | return [ |
| 474 | | ]; |
| 475 | | } |
| 476 | | |
| 477 | | |
| 478 | | |
| 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 | | |
| 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 | | |
| 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 | | |
| 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 | | |
| 553 | | |
| 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 | | |
| 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 | | |
| 578 | | |
| 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 | | } |