| 8 | | class Availability extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface |
| 9 | | { |
| 10 | | |
| 11 | | |
| 12 | | |
| 13 | | const string TABLE = 'oeffnungszeit'; |
| 14 | | |
| 15 | | const string TEMPORARY_DELETE = 'DELETE FROM oeffnungszeit WHERE `comment` = "--temporary--"'; |
| 16 | | |
| 17 | | const string QUERY_GET_LOCK = ' |
| 18 | | SELECT OeffnungszeitID FROM oeffnungszeit WHERE OeffnungszeitID = :availabilityId FOR UPDATE |
| 19 | | '; |
| 20 | | |
| 21 | | |
| 22 | | |
| 23 | | |
| 24 | | #[\Override] |
| 25 | | public function addRequiredJoins() |
| 26 | | { |
| 27 | | $this->leftJoin( |
| 28 | | new \BO\Zmsbackend\Query\Alias(\BO\Zmsbackend\Query\Scope::TABLE, 'availabilityscope'), |
| 29 | | 'availability.scope_id', |
| 30 | | '=', |
| 31 | | 'availabilityscope.StandortID' |
| 32 | | ); |
| 33 | | } |
| 34 | | |
| 35 | | |
| 36 | | |
| 37 | | |
| 38 | | |
| 39 | | #[\Override] |
| 40 | | public function getEntityMapping(?string $type = null) |
| 41 | | { |
| 42 | | $mapping = [ |
| 43 | | 'id' => 'availability.OeffnungszeitID', |
| 44 | | 'scope__id' => 'availability.scope_id', |
| 45 | | 'bookable__startInDays' => self::expression( |
| 46 | | 'CAST( |
| 47 | | IF(`availability`.`open_from_days` = "0" OR `availability`.`open_from_days`, `availability`.`open_from_days`, `availabilityscope`.`Termine_ab`) |
| 48 | | AS SIGNED)' |
| 49 | | ), |
| 50 | | 'bookable__endInDays' => self::expression( |
| 51 | | 'IF((`availability`.`open_from_days` = "0" AND `availability`.`open_until_days` = "0") OR `availability`.`open_until_days`, `availability`.`open_until_days`, `availabilityscope`.`Termine_bis`)' |
| 52 | | ), |
| 53 | | 'description' => 'availability.comment', |
| 54 | | 'startDate' => 'availability.start_date', |
| 55 | | 'startTime' => self::expression(static::getStartTimeExpression('`availability`')), |
| 56 | | 'endDate' => 'availability.end_date', |
| 57 | | 'endTime' => self::expression(static::getEndTimeExpression('`availability`')), |
| 58 | | 'lastChange' => 'availability.updated_at', |
| 59 | | 'version' => 'availability.version', |
| 60 | | 'multipleSlotsAllowed' => 'availability.multiple_slots_allowed', |
| 61 | | 'repeat__afterWeeks' => 'availability.every_x_weeks', |
| 62 | | 'repeat__weekOfMonth' => 'availability.every_other_week', |
| 63 | | 'slotTimeInMinutes' => self::expression('FLOOR(TIME_TO_SEC(`availability`.`time_slot`) / 60)') , |
| 64 | | |
| 65 | | 'type' => self::expression( |
| 66 | | "IF(`availability`.`appointment_start_time`, 'appointment', 'openinghours')" |
| 67 | | ), |
| 68 | | 'weekday__monday' => self::expression('`availability`.`weekday` & 2'), |
| 69 | | 'weekday__tuesday' => self::expression('`availability`.`weekday` & 4'), |
| 70 | | 'weekday__wednesday' => self::expression('`availability`.`weekday` & 8'), |
| 71 | | 'weekday__thursday' => self::expression('`availability`.`weekday` & 16'), |
| 72 | | 'weekday__friday' => self::expression('`availability`.`weekday` & 32'), |
| 73 | | 'weekday__saturday' => self::expression('`availability`.`weekday` & 64'), |
| 74 | | 'weekday__sunday' => self::expression('`availability`.`weekday` & 1'), |
| 75 | | 'workstationCount__intern' => 'availability.appointment_workstation_count', |
| 76 | | 'workstationCount__public' => self::expression( |
| 77 | | 'GREATEST(0, `availability`.`appointment_workstation_count` - `availability`.`internet_reduction`)' |
| 78 | | ) |
| 79 | | ]; |
| 80 | | if ('openinghours' == $type) { |
| 81 | | |
| 82 | | |
| 83 | | $mapping['startTime'] = 'availability.start_time'; |
| 84 | | $mapping['endTime'] = 'availability.end_time'; |
| 85 | | } |
| 86 | | return $mapping; |
| 87 | | } |
| 88 | | |
| 89 | | |
| 90 | | |
| 91 | | |
| 92 | | |
| 93 | | #[\Override] |
| 94 | | public function getReferenceMapping() |
| 95 | | { |
| 96 | | return [ |
| 97 | | 'scope__$ref' => self::expression('CONCAT("/scope/", `availability`.`scope_id`, "/")'), |
| 98 | | ]; |
| 99 | | } |
| 100 | | |
| 101 | | public function addConditionAvailabilityId(int|string $availabilityId): static |
| 102 | | { |
| 103 | | $this->query->where('availability.OeffnungszeitID', '=', $availabilityId); |
| 104 | | return $this; |
| 105 | | } |
| 106 | | |
| 107 | | public function addConditionScopeId(string|int $scopeId): static |
| 108 | | { |
| 109 | | $this->query->where('availabilityscope.StandortID', '=', $scopeId); |
| 110 | | return $this; |
| 111 | | } |
| 112 | | |
| 113 | | public function addConditionAppointmentHours(): static |
| 114 | | { |
| 115 | | $this->query |
| 116 | | ->where('availability.appointment_start_time', '!=', '00:00:00') |
| 117 | | ->where('availability.appointment_end_time', '!=', '00:00:00'); |
| 118 | | return $this; |
| 119 | | } |
| 120 | | |
| 121 | | public function addConditionOpeningHours(): static |
| 122 | | { |
| 123 | | $this->query |
| 124 | | ->where('availability.start_time', '!=', '00:00:00') |
| 125 | | ->where('availability.end_time', '!=', '00:00:00'); |
| 126 | | return $this; |
| 127 | | } |
| 128 | | |
| 129 | | |
| 130 | | |
| 131 | | |
| 132 | | |
| 133 | | |
| 134 | | public function addConditionDoubleTypes(): static |
| 135 | | { |
| 136 | | $this->query |
| 137 | | ->where('availability.appointment_start_time', '!=', '00:00:00') |
| 138 | | ->where('availability.appointment_end_time', '!=', '00:00:00') |
| 139 | | ->where('availability.start_time', '!=', '00:00:00') |
| 140 | | ->where('availability.end_time', '!=', '00:00:00'); |
| 141 | | return $this; |
| 142 | | } |
| 143 | | |
| 144 | | public function addConditionSkipOld(\DateTimeInterface $dateTime): static |
| 145 | | { |
| 146 | | $date = $dateTime->format('Y-m-d'); |
| 147 | | $this->query |
| 148 | | ->where('availability.end_date', '>=', $date); |
| 149 | | return $this; |
| 150 | | } |
| 151 | | |
| 152 | | |
| 153 | | |
| 154 | | |
| 155 | | |
| 156 | | |
| 157 | | public function addConditionOnlyOld(\DateTimeInterface $dateTime): static |
| 158 | | { |
| 159 | | $date = $dateTime->format('Y-m-d'); |
| 160 | | $this->query |
| 161 | | ->where('availability.end_date', '<=', $date); |
| 162 | | return $this; |
| 163 | | } |
| 164 | | |
| 165 | | |
| 166 | | |
| 167 | | |
| 168 | | public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate): static |
| 169 | | { |
| 170 | | $this->query->where(function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $condition) use ($startDate, $endDate) { |
| 171 | | $condition |
| 172 | | ->andWith('availability.start_date', '<=', $endDate->format('Y-m-d')) |
| 173 | | ->andWith('availability.end_date', '>=', $startDate->format('Y-m-d')); |
| 174 | | }); |
| 175 | | return $this; |
| 176 | | } |
| 177 | | |
| 178 | | public function addConditionDate(\DateTimeInterface $dateTime): static |
| 179 | | { |
| 180 | | $date = $dateTime->format('Y-m-d'); |
| 181 | | $this->query |
| 182 | | ->where('availability.start_date', '<=', $date) |
| 183 | | ->where('availability.end_date', '>=', $date); |
| 184 | | |
| 185 | | $this->query->where(self::expression("availability.weekday & POW(2, DAYOFWEEK('$date') - 1)"), '>=', '1'); |
| 186 | | |
| 187 | | $this->query->where(self::expression(" |
| 188 | | ( |
| 189 | | ( |
| 190 | | availability.every_x_weeks |
| 191 | | AND FLOOR( |
| 192 | | ( |
| 193 | | FLOOR(UNIX_TIMESTAMP('$date')) |
| 194 | | - FLOOR(UNIX_TIMESTAMP(availability.start_date))) |
| 195 | | / 86400 |
| 196 | | / 7 |
| 197 | | ) |
| 198 | | % availability.every_x_weeks = 0 |
| 199 | | ) |
| 200 | | OR ( |
| 201 | | availability.every_other_week |
| 202 | | AND ( |
| 203 | | CEIL(DAYOFMONTH('$date') / 7) = availability.every_other_week |
| 204 | | OR ( |
| 205 | | availability.every_other_week = 5 |
| 206 | | AND CEIL(LAST_DAY('$date') / 7) = CEIL(DAYOFMONTH('$date') / 7) |
| 207 | | ) |
| 208 | | ) |
| 209 | | ) |
| 210 | | OR (availability.every_x_weeks = 0 AND availability.every_other_week = 0) |
| 211 | | ) AND 1 |
| 212 | | "), '=', '1'); |
| 213 | | return $this; |
| 214 | | } |
| 215 | | |
| 216 | | public function addConditionAppointmentTime(\DateTimeInterface $dateTime): static |
| 217 | | { |
| 218 | | $time = $dateTime->format('H:i:s'); |
| 219 | | $this->query->where("availability.appointment_start_time", '<=', $time); |
| 220 | | $this->query->where("availability.appointment_end_time", '>', $time); |
| 221 | | |
| 222 | | return $this; |
| 223 | | } |
| 224 | | |
| 225 | | |
| 226 | | |
| 227 | | |
| 228 | | |
| 229 | | public function reverseEntityMapping(\BO\Zmsentities\Availability $entity): array |
| 230 | | { |
| 231 | | $data = array(); |
| 232 | | $data['scope_id'] = $entity['scope']['id']; |
| 233 | | $data['open_from_days'] = $entity['bookable']['startInDays']; |
| 234 | | $data['open_until_days'] = $entity['bookable']['endInDays']; |
| 235 | | $data['comment'] = $entity['description']; |
| 236 | | $data['start_date'] = $entity->getStartDateTime()->format('Y-m-d'); |
| 237 | | $data['end_date'] = $entity->getEndDateTime()->format('Y-m-d'); |
| 238 | | $data['version'] = $entity['version']; |
| 239 | | if ('openinghours' == $entity['type']) { |
| 240 | | $data['start_time'] = $entity['startTime']; |
| 241 | | $data['end_time'] = $entity['endTime']; |
| 242 | | $data['appointment_start_time'] = 0; |
| 243 | | $data['appointment_end_time'] = 0; |
| 244 | | } else { |
| 245 | | $data['start_time'] = 0; |
| 246 | | $data['end_time'] = 0; |
| 247 | | $data['appointment_start_time'] = $entity['startTime']; |
| 248 | | $data['appointment_end_time'] = $entity['endTime']; |
| 249 | | } |
| 250 | | $data['every_x_weeks'] = $entity['repeat']['afterWeeks']; |
| 251 | | $data['every_other_week'] = $entity['repeat']['weekOfMonth']; |
| 252 | | $data['time_slot'] = gmdate("H:i", $entity['slotTimeInMinutes'] * 60); |
| 253 | | $data['multiple_slots_allowed'] = $entity['multipleSlotsAllowed'] ? 1 : 0; |
| 254 | | $wochentagBinaryCoded = 0; |
| 255 | | $binaryCodes = [ |
| 256 | | 'sunday' => 1, |
| 257 | | 'monday' => 2, |
| 258 | | 'tuesday' => 4, |
| 259 | | 'wednesday' => 8, |
| 260 | | 'thursday' => 16, |
| 261 | | 'friday' => 32, |
| 262 | | 'saturday' => 64, |
| 263 | | ]; |
| 264 | | foreach ($entity['weekday'] as $weekday => $isActive) { |
| 265 | | if ($isActive) { |
| 266 | | $wochentagBinaryCoded |= $binaryCodes[$weekday]; |
| 267 | | } |
| 268 | | } |
| 269 | | $data['weekday'] = $wochentagBinaryCoded; |
| 270 | | $data['appointment_workstation_count'] = $entity['workstationCount']['intern']; |
| 271 | | $data['internet_reduction'] = |
| 272 | | $entity['workstationCount']['intern'] - $entity['workstationCount']['public']; |
| 273 | | |
| 274 | | $data = array_filter($data, function ($value) { |
| 275 | | return ($value !== null && $value !== false); |
| 276 | | }); |
| 277 | | return $data; |
| 278 | | } |
| 279 | | |
| 280 | | public static function getJoinExpression(string $process, string $availability): \BO\Zmsbackend\Query\Builder\Expression |
| 281 | | { |
| 282 | | $matchesProcess = static::getProcessMatchCondition($process, $availability); |
| 283 | | $fallbackMatchesProcess = static::getProcessMatchCondition($process, 'fallback_availability'); |
| 284 | | return self::expression(" |
| 285 | | $matchesProcess |
| 286 | | |
| 287 | | -- overlapping availabilities: use the availability the process is mapped to, |
| 288 | | -- unmapped processes use the shortest matching availability, then the lower id. |
| 289 | | -- SQL counterpart of AvailabilityList::getSortedByDuration(), keep both in sync |
| 290 | | AND $availability.OeffnungszeitID = IFNULL( |
| 291 | | ( |
| 292 | | SELECT mapped_slot.availabilityID |
| 293 | | FROM slot_process mapped_process |
| 294 | | INNER JOIN slot mapped_slot ON mapped_slot.slotID = mapped_process.slotID |
| 295 | | WHERE mapped_process.processID = $process.BuergerID |
| 296 | | LIMIT 1 |
| 297 | | ), |
| 298 | | ( |
| 299 | | SELECT fallback_availability.OeffnungszeitID |
| 300 | | FROM " . self::getTablename() . " fallback_availability |
| 301 | | WHERE $fallbackMatchesProcess |
| 302 | | ORDER BY |
| 303 | | TIMEDIFF( |
| 304 | | " . static::getEndTimeExpression('fallback_availability') . ", |
| 305 | | " . static::getStartTimeExpression('fallback_availability') . " |
| 306 | | ), |
| 307 | | fallback_availability.OeffnungszeitID |
| 308 | | LIMIT 1 |
| 309 | | ) |
| 310 | | ) |
| 311 | | "); |
| 312 | | } |
| 313 | | |
| 314 | | |
| 315 | | |
| 316 | | |
| 317 | | protected static function getStartTimeExpression(string $availability): string |
| 318 | | { |
| 319 | | return "IF($availability.`appointment_start_time`, $availability.`appointment_start_time`, " |
| 320 | | . "$availability.`start_time`)"; |
| 321 | | } |
| 322 | | |
| 323 | | |
| 324 | | |
| 325 | | |
| 326 | | protected static function getEndTimeExpression(string $availability): string |
| 327 | | { |
| 328 | | return "IF($availability.`appointment_start_time`, $availability.`appointment_end_time`, " |
| 329 | | . "$availability.`end_time`)"; |
| 330 | | } |
| 331 | | |
| 332 | | protected static function getProcessMatchCondition(string $process, string $availability): string |
| 333 | | { |
| 334 | | |
| 335 | | return " |
| 336 | | $availability.scope_id = $process.StandortID |
| 337 | | AND $availability.OeffnungszeitID IS NOT NULL |
| 338 | | |
| 339 | | -- match weekday |
| 340 | | AND $availability.weekday & POW(2, DAYOFWEEK($process.Datum) - 1) |
| 341 | | |
| 342 | | -- match week |
| 343 | | AND ( |
| 344 | | ( |
| 345 | | $availability.every_x_weeks |
| 346 | | AND FLOOR( |
| 347 | | ( |
| 348 | | FLOOR(UNIX_TIMESTAMP($process.Datum)) |
| 349 | | - FLOOR(UNIX_TIMESTAMP($availability.start_date))) |
| 350 | | / 86400 |
| 351 | | / 7 |
| 352 | | ) |
| 353 | | % $availability.every_x_weeks = 0 |
| 354 | | ) |
| 355 | | OR ( |
| 356 | | $availability.every_other_week |
| 357 | | AND ( |
| 358 | | CEIL(DAYOFMONTH($process.Datum) / 7) = $availability.every_other_week |
| 359 | | OR ( |
| 360 | | $availability.every_other_week = 5 |
| 361 | | AND CEIL(LAST_DAY($process.Datum) / 7) = CEIL(DAYOFMONTH($process.Datum) / 7) |
| 362 | | ) |
| 363 | | ) |
| 364 | | ) |
| 365 | | OR ($availability.every_x_weeks = 0 AND $availability.every_other_week = 0) |
| 366 | | ) |
| 367 | | |
| 368 | | -- match time and date |
| 369 | | AND $process.Uhrzeit >= $availability.appointment_start_time |
| 370 | | AND $process.Uhrzeit < $availability.appointment_end_time |
| 371 | | AND $process.Datum >= $availability.start_date |
| 372 | | AND $process.Datum <= $availability.end_date |
| 373 | | "; |
| 374 | | } |
| 375 | | |
| 376 | | #[\Override] |
| 377 | | public function postProcess(array $data) |
| 378 | | { |
| 379 | | $startDateKey = $this->getPrefixed("startDate"); |
| 380 | | $endDateKey = $this->getPrefixed("endDate"); |
| 381 | | $lastChangeKey = $this->getPrefixed("lastChange"); |
| 382 | | $startDate = $data[$startDateKey] ?? null; |
| 383 | | $endDate = $data[$endDateKey] ?? null; |
| 384 | | $lastChange = $data[$lastChangeKey] ?? null; |
| 385 | | $data[$startDateKey] = $startDate !== null ? (new \DateTime($startDate))->getTimestamp() : null; |
| 386 | | $data[$endDateKey] = $endDate !== null ? (new \DateTime($endDate))->getTimestamp() : null; |
| 387 | | $data[$lastChangeKey] = $lastChange !== null ? (new \DateTime($lastChange . \BO\Zmsbackend\Connection\Select::$connectionTimezone))->getTimestamp() : null; |
| 388 | | return $data; |
| 389 | | } |
| 390 | | } |