Lines
84.67%
536 / 633
Functions and Methods
42.85%
18 / 42
Classes and Traits
0.00%
0 / 1
| Name | Lines | Functions and Methods | CRAP | Classes and Traits | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Slot | 84.67% | 536 / 633 | 42.85% | 18 / 42 | 294.29 | 0.00% | 0 / 1 | |||
| readByAppointment | 0.00% | 0 / 7 | 0.00% | 0 / 1 | 2 | |||||
| readReservationByAppointment | 100.00% | 19 / 19 | 100.00% | 1 / 1 | 4 | |||||
| readAvailabilityByAppointment | 97.05% | 33 / 34 | 0.00% | 0 / 1 | 7 | |||||
| getRequiredSlotsByAvailability | 94.11% | 16 / 17 | 0.00% | 0 / 1 | 5.01 | |||||
| getSlotCountForAvailability | 100.00% | 3 / 3 | 100.00% | 1 / 1 | 4 | |||||
| readPreferredAvailabilityId | 100.00% | 15 / 15 | 100.00% | 1 / 1 | 1 | |||||
| selectPreferredAvailabilityId | 97.61% | 41 / 42 | 0.00% | 0 / 1 | 17 | |||||
| getSecondsOfDay | 100.00% | 2 / 2 | 100.00% | 1 / 1 | 1 | |||||
| readByAvailability | 93.33% | 14 / 15 | 0.00% | 0 / 1 | 3.00 | |||||
| lockSlotsForAppointment | 91.66% | 22 / 24 | 0.00% | 0 / 1 | 4.01 | |||||
| lockCandidateSlots | 92.85% | 13 / 14 | 0.00% | 0 / 1 | 3.00 | |||||
| hasScopeRelevantChanges | 86.66% | 13 / 15 | 0.00% | 0 / 1 | 5.06 | |||||
| isAvailabilityOutdated | 84.78% | 39 / 46 | 0.00% | 0 / 1 | 19.14 | |||||
| writeByAvailability | 96.36% | 53 / 55 | 0.00% | 0 / 1 | 18 | |||||
| writeByScope | 0.00% | 0 / 19 | 0.00% | 0 / 1 | 20 | |||||
| writeSlotLists | 95.08% | 58 / 61 | 0.00% | 0 / 1 | 14 | |||||
| readSlotIdsBetween | 100.00% | 18 / 18 | 100.00% | 1 / 1 | 6 | |||||
| clocksMatchStored | 33.33% | 2 / 6 | 0.00% | 0 / 1 | 8.74 | |||||
| slotClock | 100.00% | 2 / 2 | 100.00% | 1 / 1 | 1 | |||||
| writeExistingSlotRows | 50.00% | 2 / 4 | 0.00% | 0 / 1 | 2.50 | |||||
| writeNewSlotRows | 75.00% | 3 / 4 | 0.00% | 0 / 1 | 2.06 | |||||
| writeAncestorChains | 93.75% | 15 / 16 | 0.00% | 0 / 1 | 4.00 | |||||
| writeChunks | 100.00% | 2 / 2 | 100.00% | 1 / 1 | 2 | |||||
| readLastChangedTime | 83.33% | 5 / 6 | 0.00% | 0 / 1 | 2.02 | |||||
| readLastChangedTimeByScope | 0.00% | 0 / 9 | 0.00% | 0 / 1 | 6 | |||||
| readLastChangedTimeByAvailability | 100.00% | 9 / 9 | 100.00% | 1 / 1 | 2 | |||||
| updateSlotProcessMapping | 85.71% | 12 / 14 | 0.00% | 0 / 1 | 4.05 | |||||
| deleteSlotProcessOnSlot | 0.00% | 0 / 7 | 0.00% | 0 / 1 | 6 | |||||
| deleteSlotProcessOnProcess | 0.00% | 0 / 9 | 0.00% | 0 / 1 | 6 | |||||
| writeSlotProcessMappingFor | 68.42% | 13 / 19 | 0.00% | 0 / 1 | 5.79 | |||||
| writeSlotProcessMappingForProcessGroup | 94.73% | 36 / 38 | 0.00% | 0 / 1 | 9.01 | |||||
| getSlotTypeForMapping | 100.00% | 3 / 3 | 100.00% | 1 / 1 | 3 | |||||
| writeSlotProcessMappingForAvailability | 100.00% | 5 / 5 | 100.00% | 1 / 1 | 1 | |||||
| readMappedAvailabilityId | 100.00% | 4 / 4 | 100.00% | 1 / 1 | 2 | |||||
| deleteSlotProcessMappingFor | 100.00% | 4 / 4 | 100.00% | 1 / 1 | 1 | |||||
| writeCanceledByTimeAndScope | 100.00% | 11 / 11 | 100.00% | 1 / 1 | 2 | |||||
| writeCanceledByTime | 100.00% | 9 / 9 | 100.00% | 1 / 1 | 2 | |||||
| deleteSlotsOlderThan | 100.00% | 7 / 7 | 100.00% | 1 / 1 | 2 | |||||
| readRowsByScopeAndDate | 100.00% | 7 / 7 | 100.00% | 1 / 1 | 1 | |||||
| writeOptimizedSlotTables | 62.50% | 10 / 16 | 0.00% | 0 / 1 | 4.84 | |||||
| getLastGeneratedSlotDate | 100.00% | 9 / 9 | 100.00% | 1 / 1 | 2 | |||||
| getOldestSlotVersionByAvailability | 100.00% | 7 / 7 | 100.00% | 1 / 1 | 1 | |||||
| 1 | <?php | |
| 2 | ||
| 3 | namespace BO\Zmsbackend\Slot\Service; | |
| 4 | ||
| 5 | use BO\Zmsdldb\Helper\DateTime; | |
| 6 | use BO\Zmsentities\Slot as Entity; | |
| 7 | use BO\Zmsentities\Collection\SlotList as Collection; | |
| 8 | use BO\Zmsentities\Availability as AvailabilityEntity; | |
| 9 | use BO\Zmsentities\Scope as ScopeEntity; | |
| 10 | ||
| 11 | /** | |
| 12 | * @SuppressWarnings(Public) | |
| 13 | * @SuppressWarnings(Complexity) | |
| 14 | * @SuppressWarnings(Coupling) | |
| 15 | * @SuppressWarnings(TooManyMethods) | |
| 16 | */ | |
| 17 | class Slot extends \BO\Zmsbackend\Base | |
| 18 | { | |
| 19 | const int DEFAULT_API_CLIENT_ID = 1; | |
| 20 | ||
| 21 | /** | |
| 22 | * maximum number of slots per appointment | |
| 23 | */ | |
| 24 | const int MAX_SLOTS = 96; | |
| 25 | ||
| 26 | const int MAX_DAYS_OF_SLOT_CALCULATION = 180; | |
| 27 | ||
| 28 | /** | |
| 29 | * @return \BO\Zmsentities\Collection\SlotList | |
| 30 | * | |
| 31 | */ | |
| 32 | public function readByAppointment( | |
| 33 | \BO\Zmsentities\Appointment $appointment, | |
| 34 | int|null $overwriteSlotsCount = null, | |
| 35 | bool $extendSlotList = false, | |
| 36 | bool $lockSlots = false, | |
| 37 | string $slotType = 'intern' | |
| 38 | ) { | |
| 39 | return $this->readReservationByAppointment( | |
| 40 | $appointment, | |
| 41 | $overwriteSlotsCount, | |
| 42 | $extendSlotList, | |
| 43 | $lockSlots, | |
| 44 | $slotType | |
| 45 | )['slotList']; | |
| 46 | } | |
| 47 | ||
| 48 | /** | |
| 49 | * Like readByAppointment(), but also returns the availability the slots belong to. A reservation maps the | |
| 50 | * process to exactly this availability, so the choice made under the slot locks is the one that is stored. | |
| 51 | * | |
| 52 | * @return array{availability: AvailabilityEntity, slotList: Collection} | |
| 53 | */ | |
| 54 | public function readReservationByAppointment( | |
| 55 | \BO\Zmsentities\Appointment $appointment, | |
| 56 | int|null $overwriteSlotsCount = null, | |
| 57 | bool $extendSlotList = false, | |
| 58 | bool $lockSlots = false, | |
| 59 | string $slotType = 'intern' | |
| 60 | ): array { | |
| 61 | $appointment = clone $appointment; | |
| 62 | $availability = $this->readAvailabilityByAppointment( | |
| 63 | $appointment, | |
| 64 | $overwriteSlotsCount, | |
| 65 | $extendSlotList, | |
| 66 | $lockSlots, | |
| 67 | $slotType | |
| 68 | ); | |
| 69 | $appointment->slotCount = static::getSlotCountForAvailability( | |
| 70 | $availability, | |
| 71 | $appointment, | |
| 72 | $overwriteSlotsCount | |
| 73 | ); | |
| 74 | $slotList = $availability->getSlotList()->withSlotsForAppointment($appointment, $extendSlotList); | |
| 75 | if ($lockSlots && count($slotList) > 0) { | |
| 76 | // One FOR UPDATE for all slots avoids row-by-row lock interleaving (deadlocks with calculateSlots). | |
| 77 | $this->lockSlotsForAppointment($slotList, $availability, $appointment->toDateTime()); | |
| 78 | } else { | |
| 79 | foreach ($slotList as $slot) { | |
| 80 | $this->readByAvailability($slot, $availability, $appointment->toDateTime(), false); | |
| 81 | } | |
| 82 | } | |
| 83 | return ['availability' => $availability, 'slotList' => $slotList]; | |
| 84 | } | |
| 85 | ||
| 86 | /** | |
| 87 | * Choose the availability an appointment is booked on. Overlapping availabilities offer the same time, | |
| 88 | * so the same rule as in writeSlotProcessMappingFor() decides which one gets the appointment. | |
| 89 | * | |
| 90 | * With $lockSlots the slots of all candidates are locked before their occupancy is read, so concurrent | |
| 91 | * reservations for the same time wait for each other instead of choosing on outdated occupancy. | |
| 92 | */ | |
| 93 | public function readAvailabilityByAppointment( | |
| 94 | \BO\Zmsentities\Appointment $appointment, | |
| 95 | int|null $overwriteSlotsCount = null, | |
| 96 | bool $extendSlotList = false, | |
| 97 | bool $lockSlots = false, | |
| 98 | string $slotType = 'intern' | |
| 99 | ): AvailabilityEntity { | |
| 100 | $availabilityService = new \BO\Zmsbackend\Availability\Service\Availability(); | |
| 101 | $availabilityList = $availabilityService->readListByAppointment($appointment); | |
| 102 | if (count($availabilityList) === 0) { | |
| 103 | return $availabilityService->readByAppointment($appointment); | |
| 104 | } | |
| 105 | if (count($availabilityList) === 1) { | |
| 106 | return $availabilityList->getFirst(); | |
| 107 | } | |
| 108 | $candidateTimes = []; | |
| 109 | $requiredSlots = static::getRequiredSlotsByAvailability( | |
| 110 | $availabilityList, | |
| 111 | $appointment, | |
| 112 | $overwriteSlotsCount, | |
| 113 | $extendSlotList, | |
| 114 | false, | |
| 115 | $candidateTimes | |
| 116 | ); | |
| 117 | $scopeId = (int) $appointment->toProperty()->scope->id->get(); | |
| 118 | if ($lockSlots) { | |
| 119 | $this->lockCandidateSlots( | |
| 120 | $scopeId, | |
| 121 | array_keys($requiredSlots), | |
| 122 | $candidateTimes, | |
| 123 | $appointment->toDateTime() | |
| 124 | ); | |
| 125 | } | |
| 126 | $availabilityId = $this->readPreferredAvailabilityId( | |
| 127 | $scopeId, | |
| 128 | $appointment->toDateTime(), | |
| 129 | $requiredSlots, | |
| 130 | $slotType | |
| 131 | ); | |
| 132 | foreach ($availabilityList as $candidate) { | |
| 133 | if ($availabilityId !== null && $candidate->getId() == $availabilityId) { | |
| 134 | return $candidate; | |
| 135 | } | |
| 136 | } | |
| 137 | $sorted = $availabilityList->getSortedByDuration(); | |
| 138 | return $sorted[array_key_first($sorted)]; | |
| 139 | } | |
| 140 | ||
| 141 | /** | |
| 142 | * Required slots per candidate availability, in the order of AvailabilityList::getSortedByDuration(). | |
| 143 | * This order is the only place that decides which of several overlapping availabilities is preferred. | |
| 144 | * | |
| 145 | * @param bool $keepNotFitting keep candidates the appointment does not fit in completely (an existing | |
| 146 | * booking must still be mapped somewhere), otherwise they are skipped | |
| 147 | * @param array<string> $candidateTimes filled with the slot times of all candidates | |
| 148 | * | |
| 149 | * @return array<int, int> availability id => required slots | |
| 150 | */ | |
| 151 | protected static function getRequiredSlotsByAvailability( | |
| 152 | \BO\Zmsentities\Collection\AvailabilityList $availabilityList, | |
| 153 | \BO\Zmsentities\Appointment $appointment, | |
| 154 | int|null $overwriteSlotsCount, | |
| 155 | bool $extendSlotList, | |
| 156 | bool $keepNotFitting, | |
| 157 | array &$candidateTimes = [] | |
| 158 | ): array { | |
| 159 | $requiredSlots = []; | |
| 160 | foreach ($availabilityList->getSortedByDuration() as $candidate) { | |
| 161 | $candidateAppointment = clone $appointment; | |
| 162 | $candidateAppointment->slotCount = static::getSlotCountForAvailability( | |
| 163 | $candidate, | |
| 164 | $appointment, | |
| 165 | $overwriteSlotsCount | |
| 166 | ); | |
| 167 | try { | |
| 168 | $slotList = $candidate->getSlotList()->withSlotsForAppointment($candidateAppointment, $extendSlotList); | |
| 169 | } catch (\BO\Zmsentities\Exception\AppointmentNotFitInSlotList) { | |
| 170 | if ($keepNotFitting) { | |
| 171 | $requiredSlots[(int) $candidate->getId()] = max(1, (int) $candidateAppointment->slotCount); | |
| 172 | } | |
| 173 | continue; | |
| 174 | } | |
| 175 | $requiredSlots[(int) $candidate->getId()] = count($slotList); | |
| 176 | foreach ($slotList as $slot) { | |
| 177 | $candidateTimes[] = $slot->getTimeString(); | |
| 178 | } | |
| 179 | } | |
| 180 | return $requiredSlots; | |
| 181 | } | |
| 182 | ||
| 183 | protected static function getSlotCountForAvailability( | |
| 184 | AvailabilityEntity $availability, | |
| 185 | \BO\Zmsentities\Appointment $appointment, | |
| 186 | int|null $overwriteSlotsCount = null | |
| 187 | ): mixed { | |
| 188 | // Check if availability allows multiple slots, but allow overwrite | |
| 189 | if (!$availability->multipleSlotsAllowed || $overwriteSlotsCount >= 1) { | |
| 190 | return ($overwriteSlotsCount >= 1) ? $overwriteSlotsCount : 1; | |
| 191 | } | |
| 192 | return $appointment->slotCount; | |
| 193 | } | |
| 194 | ||
| 195 | /** | |
| 196 | * @param array<int, int> $requiredSlots candidate availability id => required slots, preferred first | |
| 197 | * (see getRequiredSlotsByAvailability()) | |
| 198 | * @param string $slotType "public" bookings need public capacity, all other bookings intern capacity | |
| 199 | */ | |
| 200 | public function readPreferredAvailabilityId( | |
| 201 | int $scopeId, | |
| 202 | \DateTimeInterface $dateTime, | |
| 203 | array $requiredSlots, | |
| 204 | string $slotType = 'intern' | |
| 205 | ): ?int { | |
| 206 | $slotRows = $this->fetchAll( | |
| 207 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY, | |
| 208 | [ | |
| 209 | 'scopeID' => $scopeId, | |
| 210 | 'year' => $dateTime->format('Y'), | |
| 211 | 'month' => $dateTime->format('m'), | |
| 212 | 'day' => $dateTime->format('d'), | |
| 213 | ] | |
| 214 | ); | |
| 215 | return static::selectPreferredAvailabilityId( | |
| 216 | $slotRows, | |
| 217 | $dateTime->format('H:i:s'), | |
| 218 | $requiredSlots, | |
| 219 | $slotType | |
| 220 | ); | |
| 221 | } | |
| 222 | ||
| 223 | /** | |
| 224 | * Pick one availability for an appointment starting at $time from the free slots of that day. | |
| 225 | * | |
| 226 | * Order: all required slots in a row with free capacity, then all required slots in a row, | |
| 227 | * then only the first slot. Within each group the order of $requiredSlots decides (shorter availability | |
| 228 | * first, see AvailabilityList::getSortedByDuration()). | |
| 229 | * Free capacity is checked against the workstations of the booking type: public bookings must not | |
| 230 | * use workstations that are only open for intern bookings. | |
| 231 | * | |
| 232 | * @param array<array<string, mixed>> $slotRows rows of QUERY_SELECT_SLOT_OCCUPANCY_BY_SCOPE_AND_DAY | |
| 233 | * @param array<int, int> $requiredSlots candidate availability id => required slots, preferred first | |
| 234 | */ | |
| 235 | public static function selectPreferredAvailabilityId( | |
| 236 | array $slotRows, | |
| 237 | string $time, | |
| 238 | array $requiredSlots, | |
| 239 | string $slotType = 'intern' | |
| 240 | ): ?int { | |
| 241 | $preference = array_flip(array_map('intval', array_keys($requiredSlots))); | |
| 242 | $capacityField = ($slotType === 'public') ? 'public' : 'intern'; | |
| 243 | $slotsByAvailability = []; | |
| 244 | foreach ($slotRows as $row) { | |
| 245 | $slotsByAvailability[(int) $row['availabilityID']][] = $row; | |
| 246 | } | |
| 247 | $startSeconds = static::getSecondsOfDay($time); | |
| 248 | $candidates = []; | |
| 249 | foreach ($slotsByAvailability as $availabilityId => $slots) { | |
| 250 | if (!array_key_exists($availabilityId, $preference)) { | |
| 251 | continue; | |
| 252 | } | |
| 253 | $slotCount = max(1, (int) $requiredSlots[$availabilityId]); | |
| 254 | usort($slots, fn($left, $right) => static::getSecondsOfDay($left['time']) | |
| 255 | <=> static::getSecondsOfDay($right['time'])); | |
| 256 | $startIndex = null; | |
| 257 | foreach ($slots as $index => $slot) { | |
| 258 | $slotStart = static::getSecondsOfDay($slot['time']); | |
| 259 | if ($slotStart <= $startSeconds && $startSeconds < $slotStart + (int) $slot['slotTimeInMinutes'] * 60) { | |
| 260 | $startIndex = $index; | |
| 261 | break; | |
| 262 | } | |
| 263 | } | |
| 264 | if ($startIndex === null) { | |
| 265 | continue; | |
| 266 | } | |
| 267 | $chain = array_slice($slots, $startIndex, $slotCount); | |
| 268 | $isComplete = count($chain) === $slotCount; | |
| 269 | $hasCapacity = true; | |
| 270 | $previousEnd = null; | |
| 271 | foreach ($chain as $slot) { | |
| 272 | $slotStart = static::getSecondsOfDay($slot['time']); | |
| 273 | if ($previousEnd !== null && $previousEnd !== $slotStart) { | |
| 274 | $isComplete = false; | |
| 275 | } | |
| 276 | $previousEnd = $slotStart + (int) $slot['slotTimeInMinutes'] * 60; | |
| 277 | if ((int) $slot['confirmed'] >= (int) ($slot[$capacityField] ?? $slot['intern'])) { | |
| 278 | $hasCapacity = false; | |
| 279 | } | |
| 280 | } | |
| 281 | $candidates[] = [ | |
| 282 | 'availabilityId' => $availabilityId, | |
| 283 | 'rank' => ($isComplete && $hasCapacity) ? 0 : ($isComplete ? 1 : 2), | |
| 284 | 'preference' => $preference[$availabilityId], | |
| 285 | ]; | |
| 286 | } | |
| 287 | if ($candidates === []) { | |
| 288 | return null; | |
| 289 | } | |
| 290 | usort($candidates, fn($left, $right) => [$left['rank'], $left['preference']] | |
| 291 | <=> [$right['rank'], $right['preference']]); | |
| 292 | return $candidates[0]['availabilityId']; | |
| 293 | } | |
| 294 | ||
| 295 | protected static function getSecondsOfDay(string $time): int | |
| 296 | { | |
| 297 | $parts = array_map('intval', explode(':', $time)); | |
| 298 | return ($parts[0] ?? 0) * 3600 + ($parts[1] ?? 0) * 60 + ($parts[2] ?? 0); | |
| 299 | } | |
| 300 | ||
| 301 | public function readByAvailability( | |
| 302 | \BO\Zmsentities\Slot $slot, | |
| 303 | AvailabilityEntity $availability, | |
| 304 | \DateTimeInterface $date, | |
| 305 | bool $getLock = false | |
| 306 | ) { | |
| 307 | $data = array(); | |
| 308 | $data['scopeID'] = $availability['scope']['id']; | |
| 309 | $data['availabilityID'] = $availability['id']; | |
| 310 | $data['year'] = $date->format('Y'); | |
| 311 | $data['month'] = $date->format('m'); | |
| 312 | $data['day'] = $date->format('d'); | |
| 313 | $data['time'] = $slot->getTimeString(); | |
| 314 | $sql = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOT; | |
| 315 | if ($getLock) { | |
| 316 | $sql .= ' FOR UPDATE'; | |
| 317 | } | |
| 318 | $slotID = $this->fetchRow( | |
| 319 | $sql, | |
| 320 | $data | |
| 321 | ); | |
| 322 | return $slotID ? $slotID['slotID'] : false ; | |
| 323 | } | |
| 324 | ||
| 325 | /** | |
| 326 | * Lock all slots required for an appointment in a single SELECT ... FOR UPDATE. | |
| 327 | * Uses ORDER BY slotID ASC so lock acquisition prefers primary-key order. | |
| 328 | */ | |
| 329 | protected function lockSlotsForAppointment( | |
| 330 | Collection $slotList, | |
| 331 | AvailabilityEntity $availability, | |
| 332 | \DateTimeInterface $date | |
| 333 | ): void { | |
| 334 | $times = []; | |
| 335 | foreach ($slotList as $slot) { | |
| 336 | if (!$slot instanceof Entity) { | |
| 337 | continue; | |
| 338 | } | |
| 339 | $times[] = $slot->getTimeString(); | |
| 340 | } | |
| 341 | $times = array_values(array_unique($times)); | |
| 342 | if ($times === []) { | |
| 343 | return; | |
| 344 | } | |
| 345 | ||
| 346 | $placeholders = implode(',', array_fill(0, count($times), '?')); | |
| 347 | $sql = sprintf( | |
| 348 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOTS_FOR_UPDATE, | |
| 349 | $placeholders | |
| 350 | ); | |
| 351 | $params = array_merge( | |
| 352 | [ | |
| 353 | $availability['scope']['id'], | |
| 354 | $availability['id'], | |
| 355 | $date->format('Y'), | |
| 356 | $date->format('m'), | |
| 357 | $date->format('d'), | |
| 358 | ], | |
| 359 | $times | |
| 360 | ); | |
| 361 | $this->fetchAll($sql, $params); | |
| 362 | } | |
| 363 | ||
| 364 | /** | |
| 365 | * @param array<int|string> $availabilityIds | |
| 366 | * @param array<string> $times | |
| 367 | */ | |
| 368 | protected function lockCandidateSlots( | |
| 369 | int $scopeId, | |
| 370 | array $availabilityIds, | |
| 371 | array $times, | |
| 372 | \DateTimeInterface $date | |
| 373 | ): void { | |
| 374 | $availabilityIds = array_values(array_unique(array_map('intval', $availabilityIds))); | |
| 375 | $times = array_values(array_unique($times)); | |
| 376 | if ($availabilityIds === [] || $times === []) { | |
| 377 | return; | |
| 378 | } | |
| 379 | $sql = sprintf( | |
| 380 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_CANDIDATE_SLOTS_FOR_UPDATE, | |
| 381 | implode(',', array_fill(0, count($availabilityIds), '?')), | |
| 382 | implode(',', array_fill(0, count($times), '?')) | |
| 383 | ); | |
| 384 | $this->fetchAll($sql, array_merge( | |
| 385 | [$scopeId, $date->format('Y'), $date->format('m'), $date->format('d')], | |
| 386 | $availabilityIds, | |
| 387 | $times | |
| 388 | )); | |
| 389 | } | |
| 390 | ||
| 391 | /** | |
| 392 | * @return null|true | |
| 393 | */ | |
| 394 | public function hasScopeRelevantChanges( | |
| 395 | \BO\Zmsentities\Scope $scope, | |
| 396 | ?\DateTimeInterface $slotLastChange = null | |
| 397 | ) { | |
| 398 | $startInDaysDefault = (new \BO\Zmsbackend\Preferences\Service\Preferences()) | |
| 399 | ->readProperty('scope', $scope->id, 'appointment', 'startInDaysDefault'); | |
| 400 | $endInDaysDefault = (new \BO\Zmsbackend\Preferences\Service\Preferences()) | |
| 401 | ->readProperty('scope', $scope->id, 'appointment', 'endInDaysDefault'); | |
| 402 | if ( | |
| 403 | $scope->preferences['appointment']['startInDaysDefault'] != $startInDaysDefault | |
| 404 | || $scope->preferences['appointment']['endInDaysDefault'] != $endInDaysDefault | |
| 405 | ) { | |
| 406 | (new \BO\Zmsbackend\Scope\Service\Scope())->replacePreferences($scope); //TODO remove after ZMS1 is deactivated | |
| 407 | return true; | |
| 408 | } | |
| 409 | $startInDaysChange = (new \BO\Zmsbackend\Preferences\Service\Preferences()) | |
| 410 | ->readChangeDateTime('scope', $scope->id, 'appointment', 'startInDaysDefault'); | |
| 411 | $endInDaysChange = (new \BO\Zmsbackend\Preferences\Service\Preferences()) | |
| 412 | ->readChangeDateTime('scope', $scope->id, 'appointment', 'endInDaysDefault'); | |
| 413 | if ( | |
| 414 | $startInDaysChange->getTimestamp() > $slotLastChange->getTimestamp() | |
| 415 | || $endInDaysChange->getTimestamp() > $slotLastChange->getTimestamp() | |
| 416 | ) { | |
| 417 | return true; | |
| 418 | } | |
| 419 | } | |
| 420 | ||
| 421 | public function isAvailabilityOutdated( | |
| 422 | \BO\Zmsentities\Availability $availability, | |
| 423 | \DateTimeInterface $now, | |
| 424 | ?\DateTimeInterface $slotLastChange = null, | |
| 425 | int $oldestSlotVersion = 1 | |
| 426 | ): bool { | |
| 427 | $proposedChange = new \BO\Zmsbackend\Helper\AvailabilitySnapShot($availability, $now); | |
| 428 | $formerChange = new \BO\Zmsbackend\Helper\AvailabilitySnapShot($availability, $slotLastChange); | |
| 429 | ||
| 430 | if ($availability->version > $oldestSlotVersion) { | |
| 431 | $availability['processingNote'][] = 'outdated: availability version change'; | |
| 432 | return true; | |
| 433 | } | |
| 434 | ||
| 435 | if ($formerChange->hasOutdatedAvailability()) { | |
| 436 | $availability['processingNote'][] = 'outdated: availability change'; | |
| 437 | return true; | |
| 438 | } | |
| 439 | if ( | |
| 440 | $formerChange->hasOutdatedScope() | |
| 441 | && $this->hasScopeRelevantChanges($availability->scope, $slotLastChange) | |
| 442 | ) { | |
| 443 | $availability['processingNote'][] = 'outdated: scope change'; | |
| 444 | return true; | |
| 445 | } | |
| 446 | if ($formerChange->hasOutdatedDayoff()) { | |
| 447 | $availability['processingNote'][] = 'outdated: dayoff change'; | |
| 448 | return true; | |
| 449 | } | |
| 450 | // Be aware, that last slot change and current time might differ serval days | |
| 451 | // if the rebuild fails in some way | |
| 452 | if ( | |
| 453 | 1 | |
| 454 | // First check if the bookable end date on current time was already calculated on last slot change | |
| 455 | && !$formerChange->hasBookableDateTime($proposedChange->getLastBookableDateTime()) | |
| 456 | // Second check if between last slot change and current time could be a bookable slot | |
| 457 | && ( | |
| 458 | ( | |
| 459 | !$formerChange->isOpenedOnLastBookableDay() | |
| 460 | && $proposedChange->hasBookableDateTimeAfter($formerChange->getLastBookableDateTime()) | |
| 461 | ) | |
| 462 | // if calculation already happened the day before, check if lastChange time was before opening | |
| 463 | || ( | |
| 464 | $formerChange->isOpenedOnLastBookableDay() | |
| 465 | && ( | |
| 466 | !$formerChange->isTimeOpenedOnLastBookableDay() | |
| 467 | || $proposedChange->hasBookableDateTimeAfter( | |
| 468 | $formerChange->getLastBookableDateTime()->modify('+1day 00:00:00') | |
| 469 | ) | |
| 470 | ) | |
| 471 | ) | |
| 472 | ) | |
| 473 | // Check if daytime is after booking start time if bookable end of now is calculated | |
| 474 | && ( | |
| 475 | !$proposedChange->isOpenedOnLastBookableDay() | |
| 476 | || $proposedChange->isTimeOpenedOnLastBookableDay() | |
| 477 | ) | |
| 478 | ) { | |
| 479 | $availability['processingNote'][] = 'outdated: new slots required'; | |
| 480 | return true; | |
| 481 | } | |
| 482 | if ( | |
| 483 | $availability->getBookableStart($slotLastChange) != $availability->getBookableStart($now) | |
| 484 | // First check, if bookable start from lastChange was not included in bookable time from now | |
| 485 | && !$availability->hasDate($availability->getBookableStart($slotLastChange), $now) | |
| 486 | // Second check, if availability had a bookable time on lastChange before bookable start from now | |
| 487 | && $availability->hasDateBetween( | |
| 488 | $availability->getBookableStart($slotLastChange), | |
| 489 | $availability->getBookableStart($now), | |
| 490 | $slotLastChange | |
| 491 | ) | |
| 492 | ) { | |
| 493 | $availability['processingNote'][] = 'outdated: slots invalidated by bookable start'; | |
| 494 | return true; | |
| 495 | } | |
| 496 | $availability['processingNote'][] = 'not outdated'; | |
| 497 | return false; | |
| 498 | } | |
| 499 | ||
| 500 | /** | |
| 501 | * @return bool TRUE if there were changes on slots | |
| 502 | */ | |
| 503 | public function writeByAvailability( | |
| 504 | \BO\Zmsentities\Availability $availability, | |
| 505 | \DateTimeInterface $now, | |
| 506 | ?\DateTimeInterface $slotLastChange = null | |
| 507 | ) { | |
| 508 | $now = \BO\Zmsentities\Helper\DateTime::create($now); | |
| 509 | $calculateSlotsUntilDate = \BO\Zmsentities\Helper\DateTime::create($now)->modify('+' . self::MAX_DAYS_OF_SLOT_CALCULATION . ' days'); | |
| 510 | if (!$slotLastChange) { | |
| 511 | $slotLastChange = $this->readLastChangedTimeByAvailability($availability); | |
| 512 | } | |
| 513 | $lastGeneratedSlotDate = $this->getLastGeneratedSlotDate($availability); | |
| 514 | $oldestSlotVersion = $this->getOldestSlotVersionByAvailability($availability); | |
| 515 | $availability['processingNote'][] = 'lastchange=' . $slotLastChange->format('c'); | |
| 516 | if (!$this->isAvailabilityOutdated($availability, $now, $slotLastChange, $oldestSlotVersion)) { | |
| 517 | return false; | |
| 518 | } | |
| 519 | $startDate = $availability->getBookableStart($now)->modify('00:00:00'); | |
| 520 | $stopDate = $availability->getBookableEnd($now); | |
| 521 | $generateNew = empty($lastGeneratedSlotDate) || $availability->version > $oldestSlotVersion; | |
| 522 | (new \BO\Zmsbackend\Availability\Service\Availability())->readLock($availability->id); | |
| 523 | $cancelledSlots = 0; | |
| 524 | $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY_BEFORE_BOOKABLE, [ | |
| 525 | 'availabilityID' => $availability->id, | |
| 526 | 'providedDate' => $startDate->format('Y-m-d') | |
| 527 | ]); | |
| 528 | // Cancel slots only if previously generated beyond new bookable end | |
| 529 | if ($lastGeneratedSlotDate && $lastGeneratedSlotDate->getTimestamp() > $stopDate->getTimestamp()) { | |
| 530 | $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY_AFTER_BOOKABLE, [ | |
| 531 | 'availabilityID' => $availability->id, | |
| 532 | 'providedDate' => $stopDate->format('Y-m-d') | |
| 533 | ]); | |
| 534 | } | |
| 535 | if ($generateNew) { | |
| 536 | $cancelledSlots += $this->fetchAffected(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_AVAILABILITY, [ | |
| 537 | 'availabilityID' => $availability->id, | |
| 538 | ]); | |
| 539 | ||
| 540 | if (!$availability->withData(['bookable' => ['startInDays' => 0]])->hasBookableDates($now)) { | |
| 541 | $availability['processingNote'][] = "cancelled $cancelledSlots slots: availability not bookable "; | |
| 542 | return ($cancelledSlots > 0) ? true : false; | |
| 543 | } | |
| 544 | ||
| 545 | $scope = $availability->scope; | |
| 546 | \App::$log->info('availability: ', [ | |
| 547 | 'generate_new' => $generateNew, | |
| 548 | 'scope_id' => is_object($scope) && isset($scope->id) ? (int) $scope->id : null, | |
| 549 | 'availability_id' => (int) $availability->id, | |
| 550 | 'cancelledSlots' => $cancelledSlots | |
| 551 | ]); | |
| 552 | ||
| 553 | $availability['processingNote'][] = "cancelled $cancelledSlots slots"; | |
| 554 | } elseif ($cancelledSlots > 0) { | |
| 555 | $availability['processingNote'][] = "cancelled $cancelledSlots slots"; | |
| 556 | } | |
| 557 | ||
| 558 | $slotlist = $availability->getSlotList(); | |
| 559 | $slotlistIntern = $slotlist->withValueFor('public', 0); | |
| 560 | $time = $now->modify('00:00:00'); | |
| 561 | if (!$generateNew) { | |
| 562 | $time = $lastGeneratedSlotDate->modify('+1 day')->modify('00:00:00'); | |
| 563 | } | |
| 564 | $days = []; | |
| 565 | do { | |
| 566 | if ($availability->withData(['bookable' => ['startInDays' => 0]])->hasDate($time, $now)) { | |
| 567 | $days[] = [ | |
| 568 | 'time' => $time, | |
| 569 | 'slotlist' => ($time->getTimestamp() < $startDate->getTimestamp()) ? $slotlistIntern : $slotlist, | |
| 570 | ]; | |
| 571 | } | |
| 572 | $time = $time->modify('+1day'); | |
| 573 | } while ($time->getTimestamp() <= $stopDate->getTimestamp() && $time->getTimestamp() < $calculateSlotsUntilDate->getTimestamp()); | |
| 574 | ||
| 575 | $status = $this->writeSlotLists($days, $availability); | |
| 576 | ||
| 577 | return $status || (isset($cancelledSlots) && $cancelledSlots > 0); | |
| 578 | } | |
| 579 | ||
| 580 | public function writeByScope(\BO\Zmsentities\Scope $scope, \DateTimeInterface $now): \BO\Zmsentities\Collection\AvailabilityList | |
| 581 | { | |
| 582 | $slotLastChange = $this->readLastChangedTimeByScope($scope); | |
| 583 | $availabilityList = (new \BO\Zmsbackend\Availability\Service\Availability()) | |
| 584 | ->readAvailabilityListByScope($scope, 0, $slotLastChange->modify('-1 day')) | |
| 585 | ; | |
| 586 | $updatedList = new \BO\Zmsentities\Collection\AvailabilityList(); | |
| 587 | foreach ($availabilityList as $availability) { | |
| 588 | $availability->scope = clone $scope; //dayoff is required | |
| 589 | $availabilityStarted = microtime(true); | |
| 590 | $changed = $this->writeByAvailability($availability, $now); | |
| 591 | \App::$log->info($changed ? 'Updated availability' : 'Availability unchanged', [ | |
| 592 | 'scope_id' => (int) $scope->id, | |
| 593 | 'availability_id' => (int) $availability->id, | |
| 594 | 'duration_seconds' => round(microtime(true) - $availabilityStarted, 2), | |
| 595 | 'changed' => $changed, | |
| 596 | 'reason' => $availability['processingNote'] ?? [], | |
| 597 | ]); | |
| 598 | if ($changed) { | |
| 599 | $updatedList->addEntity($availability); | |
| 600 | } | |
| 601 | } | |
| 602 | return $updatedList; | |
| 603 | } | |
| 604 | ||
| 605 | /** | |
| 606 | * Write every open day in a few statements. | |
| 607 | * A day whose stored times already match keeps its ancestor rows. | |
| 608 | * Days that need a new chain are written together: level 1 is the slot itself, | |
| 609 | * higher levels are the earlier slots that day, at most 96. | |
| 610 | * | |
| 611 | * @param list<array{time: \DateTimeInterface, slotlist: Collection}> $days | |
| 612 | */ | |
| 613 | private function writeSlotLists(array $days, AvailabilityEntity $availability): bool | |
| 614 | { | |
| 615 | $createdAt = time(); | |
| 616 | $slotRepository = new \BO\Zmsbackend\Slot\Repository\Slot(\BO\Zmsbackend\Query\Base::INSERT); | |
| 617 | $prepared = []; | |
| 618 | foreach ($days as $day) { | |
| 619 | $rows = []; | |
| 620 | foreach ($day['slotlist'] as $slot) { | |
| 621 | $slot = clone $slot; | |
| 622 | $slot->status = 'free'; | |
| 623 | $values = $slotRepository->reverseEntityMapping($slot, $availability, $day['time']); | |
| 624 | $values['createTimestamp'] = $createdAt; | |
| 625 | $rows[] = $values; | |
| 626 | } | |
| 627 | if ($rows !== []) { | |
| 628 | $prepared[] = ['time' => $day['time'], 'rows' => $rows]; | |
| 629 | } | |
| 630 | } | |
| 631 | if ($prepared === []) { | |
| 632 | return false; | |
| 633 | } | |
| 634 | ||
| 635 | $storedByDay = $this->readSlotIdsBetween( | |
| 636 | $availability, | |
| 637 | $prepared[0]['time'], | |
| 638 | $prepared[array_key_last($prepared)]['time'] | |
| 639 | ); | |
| 640 | $updates = []; | |
| 641 | $inserts = []; | |
| 642 | $added = []; | |
| 643 | $unchanged = []; | |
| 644 | foreach ($prepared as $day) { | |
| 645 | $dateKey = $day['time']->format('Y-m-d'); | |
| 646 | $stored = $storedByDay[$dateKey] ?? []; | |
| 647 | $clocks = []; | |
| 648 | foreach ($day['rows'] as $values) { | |
| 649 | $clock = $this->slotClock((string) $values['time']); | |
| 650 | $clocks[] = $clock; | |
| 651 | if (isset($stored[$clock])) { | |
| 652 | $updates[] = \BO\Zmsbackend\Slot\Repository\Slot::packSlotRow($values, $stored[$clock]); | |
| 653 | } else { | |
| 654 | $inserts[] = \BO\Zmsbackend\Slot\Repository\Slot::packSlotRow($values); | |
| 655 | $added[$dateKey] = true; | |
| 656 | } | |
| 657 | } | |
| 658 | $unchanged[$dateKey] = $this->clocksMatchStored($stored, $clocks); | |
| 659 | } | |
| 660 | ||
| 661 | $this->writeExistingSlotRows($updates); | |
| 662 | $this->writeNewSlotRows($inserts); | |
| 663 | if ($inserts !== []) { | |
| 664 | $storedByDay = $this->readSlotIdsBetween( | |
| 665 | $availability, | |
| 666 | $prepared[0]['time'], | |
| 667 | $prepared[array_key_last($prepared)]['time'] | |
| 668 | ); | |
| 669 | foreach (array_keys($added) as $dateKey) { | |
| 670 | $availability['processingNote'][] = 'Added ' . $dateKey; | |
| 671 | } | |
| 672 | } | |
| 673 | ||
| 674 | $ancestorChains = []; | |
| 675 | foreach ($prepared as $day) { | |
| 676 | $dateKey = $day['time']->format('Y-m-d'); | |
| 677 | if ($unchanged[$dateKey]) { | |
| 678 | continue; | |
| 679 | } | |
| 680 | $stored = $storedByDay[$dateKey] ?? []; | |
| 681 | $orderedSlotIds = []; | |
| 682 | foreach ($day['rows'] as $values) { | |
| 683 | $clock = $this->slotClock((string) $values['time']); | |
| 684 | if (!isset($stored[$clock])) { | |
| 685 | throw new \RuntimeException('Slot calculation did not store every slot for ' . $dateKey); | |
| 686 | } | |
| 687 | $orderedSlotIds[] = $stored[$clock]; | |
| 688 | } | |
| 689 | $ancestorChains[] = ['date' => $dateKey, 'slotIds' => $orderedSlotIds]; | |
| 690 | } | |
| 691 | $this->writeAncestorChains($ancestorChains); | |
| 692 | ||
| 693 | return true; | |
| 694 | } | |
| 695 | ||
| 696 | /** | |
| 697 | * @return array<string, array<string, int>> date => clock time => slotID, first row wins when a time is stored twice | |
| 698 | */ | |
| 699 | private function readSlotIdsBetween( | |
| 700 | AvailabilityEntity $availability, | |
| 701 | \DateTimeInterface $from, | |
| 702 | \DateTimeInterface $to | |
| 703 | ): array { | |
| 704 | $scope = $availability->scope; | |
| 705 | $rows = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_SLOTS_IN_RANGE, [ | |
| 706 | 'scopeID' => $scope instanceof ScopeEntity ? (int) $scope->id : (int) ($scope['id'] ?? 0), | |
| 707 | 'availabilityID' => $availability->id, | |
| 708 | 'yearFrom' => $from->format('Y'), | |
| 709 | 'yearTo' => $to->format('Y'), | |
| 710 | ]); | |
| 711 | $fromKey = $from->format('Y-m-d'); | |
| 712 | $toKey = $to->format('Y-m-d'); | |
| 713 | $byDay = []; | |
| 714 | foreach ($rows as $row) { | |
| 715 | $dateKey = sprintf('%04d-%02d-%02d', $row['year'], $row['month'], $row['day']); | |
| 716 | if ($dateKey < $fromKey || $dateKey > $toKey) { | |
| 717 | continue; | |
| 718 | } | |
| 719 | $clock = $this->slotClock((string) $row['time']); | |
| 720 | if (!isset($byDay[$dateKey][$clock])) { | |
| 721 | $byDay[$dateKey][$clock] = (int) $row['slotID']; | |
| 722 | } | |
| 723 | } | |
| 724 | ||
| 725 | return $byDay; | |
| 726 | } | |
| 727 | ||
| 728 | /** | |
| 729 | * @param array<string, int> $stored | |
| 730 | * @param list<string> $clocks | |
| 731 | */ | |
| 732 | private function clocksMatchStored(array $stored, array $clocks): bool | |
| 733 | { | |
| 734 | if (count($stored) !== count($clocks)) { | |
| 735 | return false; | |
| 736 | } | |
| 737 | foreach ($clocks as $clock) { | |
| 738 | if (!isset($stored[$clock])) { | |
| 739 | return false; | |
| 740 | } | |
| 741 | } | |
| 742 | ||
| 743 | return true; | |
| 744 | } | |
| 745 | ||
| 746 | private function slotClock(string $time): string | |
| 747 | { | |
| 748 | $parts = explode(':', $time); | |
| 749 | ||
| 750 | return sprintf('%02d:%02d:%02d', (int) ($parts[0] ?? 0), (int) ($parts[1] ?? 0), (int) ($parts[2] ?? 0)); | |
| 751 | } | |
| 752 | ||
| 753 | /** | |
| 754 | * @param list<list<int|string>> $rows slotID plus SLOT_WRITE_COLUMNS | |
| 755 | */ | |
| 756 | private function writeExistingSlotRows(array $rows): void | |
| 757 | { | |
| 758 | if ($rows === []) { | |
| 759 | return; | |
| 760 | } | |
| 761 | $statement = \BO\Zmsbackend\Slot\Repository\Slot::upsertSlotRowsSql(); | |
| 762 | $this->writeChunks($statement['prefix'], $statement['suffix'], $rows); | |
| 763 | } | |
| 764 | ||
| 765 | /** | |
| 766 | * @param list<list<int|string>> $rows slot columns, without slotID | |
| 767 | */ | |
| 768 | private function writeNewSlotRows(array $rows): void | |
| 769 | { | |
| 770 | if ($rows === []) { | |
| 771 | return; | |
| 772 | } | |
| 773 | $statement = \BO\Zmsbackend\Slot\Repository\Slot::insertSlotRowsSql(); | |
| 774 | $this->writeChunks($statement['prefix'], $statement['suffix'], $rows); | |
| 775 | } | |
| 776 | ||
| 777 | /** | |
| 778 | * One chain for every day that changed. The pairs match the old per-slot loop. | |
| 779 | * | |
| 780 | * @param list<array{date: string, slotIds: list<int>}> $days | |
| 781 | */ | |
| 782 | private function writeAncestorChains(array $days): void | |
| 783 | { | |
| 784 | if ($days === []) { | |
| 785 | return; | |
| 786 | } | |
| 787 | $repository = \BO\Zmsbackend\Slot\Repository\Slot::class; | |
| 788 | $this->perform($repository::QUERY_CREATE_CALC_SLOT_ORDER); | |
| 789 | $this->perform($repository::QUERY_DELETE_CALC_SLOT_ORDER); | |
| 790 | $slotOrderRows = []; | |
| 791 | foreach ($days as $day) { | |
| 792 | $sequence = 1; | |
| 793 | foreach ($day['slotIds'] as $slotId) { | |
| 794 | $slotOrderRows[] = [$day['date'], $sequence, $slotId]; | |
| 795 | $sequence++; | |
| 796 | } | |
| 797 | } | |
| 798 | $this->writeChunks($repository::QUERY_INSERT_CALC_SLOT_ORDER, '', $slotOrderRows); | |
| 799 | $this->perform($repository::QUERY_DELETE_HIERA_FOR_CALC_ORDER); | |
| 800 | $maximumEarlierSlots = self::MAX_SLOTS - 1; | |
| 801 | $this->perform($repository::insertAncestorChainsSql($maximumEarlierSlots)); | |
| 802 | $this->perform($repository::QUERY_DELETE_CALC_SLOT_ORDER); | |
| 803 | } | |
| 804 | ||
| 805 | /** | |
| 806 | * @param list<list<int|string>> $rows | |
| 807 | */ | |
| 808 | private function writeChunks(string $sql, string $suffix, array $rows): void | |
| 809 | { | |
| 810 | foreach (\BO\Zmsbackend\Slot\Repository\Slot::chunkedStatements($sql, $suffix, $rows) as $statement) { | |
| 811 | $this->perform($statement['sql'], $statement['parameters']); | |
| 812 | } | |
| 813 | } | |
| 814 | ||
| 815 | public function readLastChangedTime(): \DateTimeImmutable | |
| 816 | { | |
| 817 | $last = $this->fetchRow( | |
| 818 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED | |
| 819 | ); | |
| 820 | if (!$last['dateString']) { | |
| 821 | $last['dateString'] = '1970-01-01 12:00'; | |
| 822 | } | |
| 823 | return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone); | |
| 824 | } | |
| 825 | ||
| 826 | public function readLastChangedTimeByScope(ScopeEntity $scope): \DateTimeImmutable | |
| 827 | { | |
| 828 | $last = $this->fetchRow( | |
| 829 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED_SCOPE, | |
| 830 | [ | |
| 831 | 'scopeID' => $scope->id, | |
| 832 | ] | |
| 833 | ); | |
| 834 | if (!$last['dateString']) { | |
| 835 | $last['dateString'] = '1970-01-01 12:00'; | |
| 836 | } | |
| 837 | return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone); | |
| 838 | } | |
| 839 | ||
| 840 | public function readLastChangedTimeByAvailability(AvailabilityEntity $availabiliy): \DateTimeImmutable | |
| 841 | { | |
| 842 | $last = $this->fetchRow( | |
| 843 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_CHANGED_AVAILABILITY, | |
| 844 | [ | |
| 845 | 'availabilityID' => $availabiliy->id, | |
| 846 | ] | |
| 847 | ); | |
| 848 | if (!$last['dateString']) { | |
| 849 | $last['dateString'] = '1970-01-01 12:00'; | |
| 850 | } | |
| 851 | return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone); | |
| 852 | } | |
| 853 | ||
| 854 | public function updateSlotProcessMapping(int|string|null $scopeID = null, ?\DateTimeInterface $now = null): int | |
| 855 | { | |
| 856 | $sql = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MISSING_PROCESS; | |
| 857 | $params = []; | |
| 858 | if ($now !== null) { | |
| 859 | $sql .= ' AND b.Datum >= :fromDate'; | |
| 860 | $params['fromDate'] = \DateTimeImmutable::createFromInterface($now)->modify('-1 day')->format('Y-m-d'); | |
| 861 | } | |
| 862 | if ($scopeID) { | |
| 863 | $sql .= \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MISSING_PROCESS_BY_SCOPE; | |
| 864 | $params['scopeID'] = $scopeID; | |
| 865 | } | |
| 866 | $processIdList = $this->fetchAll($sql, $params); | |
| 867 | // Map main processes first, so following processes can take the availability of their main process | |
| 868 | usort($processIdList, fn($left, $right) => [(int) $left['istFolgeterminvon'] > 0, (int) $left['BuergerID']] | |
| 869 | <=> [(int) $right['istFolgeterminvon'] > 0, (int) $right['BuergerID']]); | |
| 870 | foreach ($processIdList as $process) { | |
| 871 | $this->writeSlotProcessMappingFor($process['BuergerID']); | |
| 872 | } | |
| 873 | ||
| 874 | return count($processIdList); | |
| 875 | } | |
| 876 | ||
| 877 | public function deleteSlotProcessOnSlot(int|string|null $scopeID = null): void | |
| 878 | { | |
| 879 | if ($scopeID) { | |
| 880 | $this->perform( | |
| 881 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED | |
| 882 | . \BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED_BY_SCOPE, | |
| 883 | ['scopeID' => $scopeID] | |
| 884 | ); | |
| 885 | } else { | |
| 886 | $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_CANCELLED, []); | |
| 887 | } | |
| 888 | } | |
| 889 | ||
| 890 | public function deleteSlotProcessOnProcess(int|string|null $scopeID = null): int | |
| 891 | { | |
| 892 | $select = \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS; | |
| 893 | $params = []; | |
| 894 | if ($scopeID) { | |
| 895 | $select .= \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_DELETABLE_SLOT_PROCESS_BY_SCOPE; | |
| 896 | $params['scopeID'] = $scopeID; | |
| 897 | } | |
| 898 | // Delete the stale link only. A slot that still belongs to the appointment stays mapped. | |
| 899 | $sql = 'DELETE sp_del FROM slot_process sp_del INNER JOIN ( | |
| 900 | SELECT slotID, processID FROM (' . $select . ') stale_ids | |
| 901 | ) stale ON stale.processID = sp_del.processID AND stale.slotID = sp_del.slotID'; | |
| 902 | ||
| 903 | return $this->fetchAffected($sql, $params); | |
| 904 | } | |
| 905 | ||
| 906 | /** | |
| 907 | * Map a process to exactly one slot. With overlapping availabilities a process and its following | |
| 908 | * processes are mapped to slots of the same availability, otherwise the booking would be counted twice. | |
| 909 | * | |
| 910 | * @param int|null $availabilityId availability already chosen (and locked) by the reservation, otherwise the | |
| 911 | * availability is chosen like in readAvailabilityByAppointment() | |
| 912 | */ | |
| 913 | public function writeSlotProcessMappingFor(string|int $processId, ?int $availabilityId = null): static | |
| 914 | { | |
| 915 | $process = $this->fetchRow(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING, [ | |
| 916 | 'processId' => $processId, | |
| 917 | ]); | |
| 918 | if (!$process) { | |
| 919 | return $this; | |
| 920 | } | |
| 921 | $mainProcessId = (int) $process['istFolgeterminvon']; | |
| 922 | if ($mainProcessId > 0) { | |
| 923 | $mainAvailabilityId = $this->readMappedAvailabilityId($mainProcessId); | |
| 924 | if ($mainAvailabilityId) { | |
| 925 | $this->deleteSlotProcessMappingFor($process['BuergerID']); | |
| 926 | $this->writeSlotProcessMappingForAvailability($process['BuergerID'], $mainAvailabilityId); | |
| 927 | return $this; | |
| 928 | } | |
| 929 | $mainProcess = $this->fetchRow( | |
| 930 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING, | |
| 931 | ['processId' => $mainProcessId] | |
| 932 | ); | |
| 933 | $process = $mainProcess ? $mainProcess : $process; | |
| 934 | } | |
| 935 | $this->writeSlotProcessMappingForProcessGroup($process, $availabilityId); | |
| 936 | return $this; | |
| 937 | } | |
| 938 | ||
| 939 | /** | |
| 940 | * @param array<string, mixed> $mainProcess row of QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING | |
| 941 | */ | |
| 942 | protected function writeSlotProcessMappingForProcessGroup(array $mainProcess, ?int $availabilityId = null): void | |
| 943 | { | |
| 944 | $processIdList = [$mainProcess['BuergerID']]; | |
| 945 | if ((int) $mainProcess['istFolgeterminvon'] === 0) { | |
| 946 | $following = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_FOLLOWING_PROCESS_IDS, [ | |
| 947 | 'processId' => $mainProcess['BuergerID'], | |
| 948 | ]); | |
| 949 | foreach ($following as $row) { | |
| 950 | $processIdList[] = $row['BuergerID']; | |
| 951 | } | |
| 952 | } | |
| 953 | // Remove existing mappings first, so the group does not count against its own capacity | |
| 954 | foreach ($processIdList as $processId) { | |
| 955 | $this->deleteSlotProcessMappingFor($processId); | |
| 956 | } | |
| 957 | if (!$mainProcess['Datum'] || str_starts_with((string) $mainProcess['Datum'], '0000')) { | |
| 958 | return; | |
| 959 | } | |
| 960 | if ($availabilityId === null) { | |
| 961 | $slotCount = max(1 + (int) $mainProcess['hatFolgetermine'], count($processIdList)); | |
| 962 | $dateTime = new \DateTimeImmutable($mainProcess['Datum'] . ' ' . $mainProcess['Uhrzeit']); | |
| 963 | $appointment = new \BO\Zmsentities\Appointment([ | |
| 964 | 'date' => $dateTime->getTimestamp(), | |
| 965 | 'scope' => ['id' => (int) $mainProcess['StandortID']], | |
| 966 | 'slotCount' => $slotCount, | |
| 967 | ]); | |
| 968 | $availabilityList = (new \BO\Zmsbackend\Availability\Service\Availability()) | |
| 969 | ->readListByAppointment($appointment); | |
| 970 | // The booking already exists, so it is mapped even if it does not fit completely | |
| 971 | $requiredSlots = static::getRequiredSlotsByAvailability( | |
| 972 | $availabilityList, | |
| 973 | $appointment, | |
| 974 | $slotCount, | |
| 975 | false, | |
| 976 | true | |
| 977 | ); | |
| 978 | $availabilityId = $this->readPreferredAvailabilityId( | |
| 979 | (int) $mainProcess['StandortID'], | |
| 980 | $dateTime, | |
| 981 | $requiredSlots, | |
| 982 | static::getSlotTypeForMapping($mainProcess) | |
| 983 | ); | |
| 984 | } | |
| 985 | if ($availabilityId === null) { | |
| 986 | return; | |
| 987 | } | |
| 988 | foreach ($processIdList as $processId) { | |
| 989 | $this->writeSlotProcessMappingForAvailability($processId, $availabilityId); | |
| 990 | } | |
| 991 | } | |
| 992 | ||
| 993 | /** | |
| 994 | * Capacity type of an existing booking. The default client is public by column default and is shared by | |
| 995 | * admin bookings and bookings without their own client key, so its access level does not describe them. | |
| 996 | * | |
| 997 | * @param array<string, mixed> $process row of QUERY_SELECT_PROCESS_FOR_SLOT_MAPPING | |
| 998 | */ | |
| 999 | public static function getSlotTypeForMapping(array $process): string | |
| 1000 | { | |
| 1001 | $isOwnApiClient = (int) ($process['apiClientID'] ?? 0) > static::DEFAULT_API_CLIENT_ID; | |
| 1002 | $accessLevel = (string) ($process['accesslevel'] ?? ''); | |
| 1003 | return ($isOwnApiClient && $accessLevel !== '') ? $accessLevel : 'intern'; | |
| 1004 | } | |
| 1005 | ||
| 1006 | protected function writeSlotProcessMappingForAvailability(string|int $processId, int $availabilityId): void | |
| 1007 | { | |
| 1008 | $this->deleteSlotProcessMappingFor($processId); | |
| 1009 | $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_INSERT_SLOT_PROCESS_ID, [ | |
| 1010 | 'processId' => $processId, | |
| 1011 | 'availabilityId' => $availabilityId, | |
| 1012 | ]); | |
| 1013 | } | |
| 1014 | ||
| 1015 | public function readMappedAvailabilityId($processId): ?int | |
| 1016 | { | |
| 1017 | $row = $this->fetchRow(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_MAPPED_AVAILABILITY_BY_PROCESS, [ | |
| 1018 | 'processId' => $processId, | |
| 1019 | ]); | |
| 1020 | return $row ? (int) $row['availabilityID'] : null; | |
| 1021 | } | |
| 1022 | ||
| 1023 | public function deleteSlotProcessMappingFor(string|int $processId): static | |
| 1024 | { | |
| 1025 | $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_PROCESS_ID, [ | |
| 1026 | 'processId' => $processId, | |
| 1027 | ]); | |
| 1028 | return $this; | |
| 1029 | } | |
| 1030 | ||
| 1031 | public function writeCanceledByTimeAndScope(\DateTimeInterface $dateTime, \BO\Zmsentities\Scope $scope): bool | |
| 1032 | { | |
| 1033 | $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY_BY_SCOPE, [ | |
| 1034 | 'dateString' => $dateTime->format('Y-m-d'), | |
| 1035 | 'scopeID' => $scope->id, | |
| 1036 | ]); | |
| 1037 | ||
| 1038 | return $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_SLOT_OLD_BY_SCOPE, [ | |
| 1039 | 'year' => $dateTime->format('Y'), | |
| 1040 | 'month' => $dateTime->format('m'), | |
| 1041 | 'day' => $dateTime->format('d'), | |
| 1042 | 'time' => $dateTime->format('H:i:s'), | |
| 1043 | 'scopeID' => $scope->id, | |
| 1044 | ]) && $status; | |
| 1045 | } | |
| 1046 | ||
| 1047 | public function writeCanceledByTime(\DateTimeInterface $dateTime): bool | |
| 1048 | { | |
| 1049 | $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_UPDATE_SLOT_MISSING_AVAILABILITY, [ | |
| 1050 | 'dateString' => $dateTime->format('Y-m-d'), | |
| 1051 | ]); | |
| 1052 | return $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_CANCEL_SLOT_OLD, [ | |
| 1053 | 'year' => $dateTime->format('Y'), | |
| 1054 | 'month' => $dateTime->format('m'), | |
| 1055 | 'day' => $dateTime->format('d'), | |
| 1056 | 'time' => $dateTime->format('H:i:s'), | |
| 1057 | ]) && $status; | |
| 1058 | } | |
| 1059 | ||
| 1060 | public function deleteSlotsOlderThan(\DateTimeInterface $dateTime): bool | |
| 1061 | { | |
| 1062 | $status = $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_OLD, [ | |
| 1063 | 'year' => $dateTime->format('Y'), | |
| 1064 | 'month' => $dateTime->format('m'), | |
| 1065 | 'day' => $dateTime->format('d'), | |
| 1066 | ]); | |
| 1067 | $status = ($status && $this->perform(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_DELETE_SLOT_HIERA)); | |
| 1068 | return $status; | |
| 1069 | } | |
| 1070 | ||
| 1071 | /** | |
| 1072 | * This function is for debugging | |
| 1073 | */ | |
| 1074 | public function readRowsByScopeAndDate( | |
| 1075 | \BO\Zmsentities\Scope $scope, | |
| 1076 | \DateTimeInterface $dateTime | |
| 1077 | ) { | |
| 1078 | $list = $this->fetchAll(\BO\Zmsbackend\Slot\Repository\Slot::QUERY_SELECT_BY_SCOPE_AND_DAY, [ | |
| 1079 | 'year' => $dateTime->format('Y'), | |
| 1080 | 'month' => $dateTime->format('m'), | |
| 1081 | 'day' => $dateTime->format('d'), | |
| 1082 | 'scopeID' => $scope->id, | |
| 1083 | ]); | |
| 1084 | return $list; | |
| 1085 | } | |
| 1086 | ||
| 1087 | public function writeOptimizedSlotTables(): bool | |
| 1088 | { | |
| 1089 | $queries = [ | |
| 1090 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT, | |
| 1091 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT_HIERA, | |
| 1092 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_SLOT_PROCESS, | |
| 1093 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OPTIMIZE_PROCESS, | |
| 1094 | ]; | |
| 1095 | ||
| 1096 | $status = true; | |
| 1097 | foreach ($queries as $query) { | |
| 1098 | try { | |
| 1099 | $status = $status && $this->perform($query); | |
| 1100 | } catch (\PDOException $e) { | |
| 1101 | \App::$log->error("Failed to optimize table with query: $query. Error: " . $e->getMessage(), [ | |
| 1102 | 'scope_id' => null, | |
| 1103 | 'availability_id' => null, | |
| 1104 | ]); | |
| 1105 | ||
| 1106 | return false; | |
| 1107 | } | |
| 1108 | } | |
| 1109 | ||
| 1110 | return $status; | |
| 1111 | } | |
| 1112 | ||
| 1113 | private function getLastGeneratedSlotDate(AvailabilityEntity $availability): \DateTimeImmutable|null | |
| 1114 | { | |
| 1115 | $last = $this->fetchRow( | |
| 1116 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_LAST_IN_AVAILABILITY, | |
| 1117 | [ | |
| 1118 | 'availabilityID' => $availability->id, | |
| 1119 | ] | |
| 1120 | ); | |
| 1121 | ||
| 1122 | if (!isset($last['dateString'])) { | |
| 1123 | return null; | |
| 1124 | } | |
| 1125 | ||
| 1126 | return new \DateTimeImmutable($last['dateString'] . \BO\Zmsbackend\Connection\Select::$connectionTimezone); | |
| 1127 | } | |
| 1128 | ||
| 1129 | private function getOldestSlotVersionByAvailability(AvailabilityEntity $availability) | |
| 1130 | { | |
| 1131 | $last = $this->fetchRow( | |
| 1132 | \BO\Zmsbackend\Slot\Repository\Slot::QUERY_OLDEST_VERSION_IN_AVAILABILITY, | |
| 1133 | [ | |
| 1134 | 'availabilityID' => $availability->id, | |
| 1135 | ] | |
| 1136 | ); | |
| 1137 | ||
| 1138 | return $last['version'] ?? 1; | |
| 1139 | } | |
| 1140 | } |