Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
96.47% |
164 / 170 |
|
93.75% |
15 / 16 |
CRAP | |
0.00% |
0 / 1 |
| Availability | |
96.47% |
164 / 170 |
|
93.75% |
15 / 16 |
22 | |
0.00% |
0 / 1 |
| addRequiredJoins | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getEntityMapping | |
100.00% |
46 / 46 |
|
100.00% |
1 / 1 |
2 | |||
| getReferenceMapping | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionAvailabilityId | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionScopeId | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionAppointmentHours | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionOpeningHours | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionDoubleTypes | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
2 | |||
| addConditionSkipOld | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionOnlyOld | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionTimeframe | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionDate | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
1 | |||
| addConditionAppointmentTime | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| reverseEntityMapping | |
100.00% |
43 / 43 |
|
100.00% |
1 / 1 |
6 | |||
| getJoinExpression | |
100.00% |
17 / 17 |
|
100.00% |
1 / 1 |
1 | |||
| postProcess | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace BO\Zmsdb\Query; |
| 4 | |
| 5 | /** |
| 6 | * @SuppressWarnings(Public) |
| 7 | */ |
| 8 | class Availability extends Base implements MappingInterface |
| 9 | { |
| 10 | /** |
| 11 | * @var String TABLE mysql table reference |
| 12 | */ |
| 13 | const TABLE = 'oeffnungszeit'; |
| 14 | |
| 15 | const TEMPORARY_DELETE = 'DELETE FROM oeffnungszeit WHERE kommentar = "--temporary--"'; |
| 16 | |
| 17 | const QUERY_GET_LOCK = ' |
| 18 | SELECT OeffnungszeitID FROM oeffnungszeit WHERE OeffnungszeitID = :availabilityId FOR UPDATE |
| 19 | '; |
| 20 | |
| 21 | public function addRequiredJoins() |
| 22 | { |
| 23 | $this->leftJoin( |
| 24 | new Alias(Scope::TABLE, 'availabilityscope'), |
| 25 | 'availability.StandortID', |
| 26 | '=', |
| 27 | 'availabilityscope.StandortID' |
| 28 | ); |
| 29 | } |
| 30 | |
| 31 | public function getEntityMapping($type = null) |
| 32 | { |
| 33 | $mapping = [ |
| 34 | 'id' => 'availability.OeffnungszeitID', |
| 35 | 'scope__id' => 'availability.StandortID', |
| 36 | 'bookable__startInDays' => self::expression( |
| 37 | 'CAST( |
| 38 | IF(`availability`.`Offen_ab` = "0" OR `availability`.`Offen_ab`, `availability`.`Offen_ab`, `availabilityscope`.`Termine_ab`) |
| 39 | AS SIGNED)' |
| 40 | ), |
| 41 | 'bookable__endInDays' => self::expression( |
| 42 | 'IF(`availability`.`Offen_bis`, `availability`.`Offen_bis`, `availabilityscope`.`Termine_bis`)' |
| 43 | ), |
| 44 | 'description' => 'availability.kommentar', |
| 45 | 'startDate' => 'availability.Startdatum', |
| 46 | 'startTime' => self::expression( |
| 47 | 'IF(`availability`.`Terminanfangszeit`,`availability`.`Terminanfangszeit`,`availability`.`Anfangszeit`)' |
| 48 | ), |
| 49 | 'endDate' => 'availability.Endedatum', |
| 50 | 'endTime' => self::expression( |
| 51 | 'IF(`availability`.`Terminanfangszeit`, `availability`.`Terminendzeit`, `availability`.`Endzeit`)' |
| 52 | ), |
| 53 | 'lastChange' => 'availability.updateTimestamp', |
| 54 | 'multipleSlotsAllowed' => 'availability.erlaubemehrfachslots', |
| 55 | 'repeat__afterWeeks' => 'availability.allexWochen', |
| 56 | 'repeat__weekOfMonth' => 'availability.jedexteWoche', |
| 57 | 'slotTimeInMinutes' => self::expression('FLOOR(TIME_TO_SEC(`availability`.`Timeslot`) / 60)') , |
| 58 | // dependant function on this IF(): \BO\Zmsdb\Availablity::readList() |
| 59 | 'type' => self::expression( |
| 60 | "IF(`availability`.`Terminanfangszeit`, 'appointment', 'openinghours')" |
| 61 | ), |
| 62 | 'weekday__monday' => self::expression('`availability`.`Wochentag` & 2'), |
| 63 | 'weekday__tuesday' => self::expression('`availability`.`Wochentag` & 4'), |
| 64 | 'weekday__wednesday' => self::expression('`availability`.`Wochentag` & 8'), |
| 65 | 'weekday__thursday' => self::expression('`availability`.`Wochentag` & 16'), |
| 66 | 'weekday__friday' => self::expression('`availability`.`Wochentag` & 32'), |
| 67 | 'weekday__saturday' => self::expression('`availability`.`Wochentag` & 64'), |
| 68 | 'weekday__sunday' => self::expression('`availability`.`Wochentag` & 1'), |
| 69 | 'workstationCount__callcenter' => self::expression( |
| 70 | 'GREATEST(0, `availability`.`Anzahlterminarbeitsplaetze` - `availability`.`reduktionTermineCallcenter`)' |
| 71 | ), |
| 72 | 'workstationCount__intern' => 'availability.Anzahlterminarbeitsplaetze', |
| 73 | 'workstationCount__public' => self::expression( |
| 74 | 'GREATEST(0, `availability`.`Anzahlterminarbeitsplaetze` - `availability`.`reduktionTermineImInternet`)' |
| 75 | ) |
| 76 | ]; |
| 77 | if ('openinghours' == $type) { |
| 78 | // Test if following line is needed: type mapping with IF() a few lines before |
| 79 | //$mapping['type'] = self::expression('"openinghours"'); |
| 80 | $mapping['startTime'] = 'availability.Anfangszeit'; |
| 81 | $mapping['endTime'] = 'availability.Endzeit'; |
| 82 | } |
| 83 | return $mapping; |
| 84 | } |
| 85 | |
| 86 | public function getReferenceMapping() |
| 87 | { |
| 88 | return [ |
| 89 | 'scope__$ref' => self::expression('CONCAT("/scope/", `availability`.`StandortID`, "/")'), |
| 90 | ]; |
| 91 | } |
| 92 | |
| 93 | public function addConditionAvailabilityId($availabilityId) |
| 94 | { |
| 95 | $this->query->where('availability.OeffnungszeitID', '=', $availabilityId); |
| 96 | return $this; |
| 97 | } |
| 98 | |
| 99 | public function addConditionScopeId($scopeId) |
| 100 | { |
| 101 | $this->query->where('availabilityscope.StandortID', '=', $scopeId); |
| 102 | return $this; |
| 103 | } |
| 104 | |
| 105 | public function addConditionAppointmentHours() |
| 106 | { |
| 107 | $this->query |
| 108 | ->where('availability.Terminanfangszeit', '!=', '00:00:00') |
| 109 | ->where('availability.Terminendzeit', '!=', '00:00:00'); |
| 110 | return $this; |
| 111 | } |
| 112 | |
| 113 | public function addConditionOpeningHours() |
| 114 | { |
| 115 | $this->query |
| 116 | ->where('availability.Anfangszeit', '!=', '00:00:00') |
| 117 | ->where('availability.Endzeit', '!=', '00:00:00'); |
| 118 | return $this; |
| 119 | } |
| 120 | |
| 121 | /** |
| 122 | * Used to identify old availabilities as appointment and openinghours |
| 123 | * |
| 124 | */ |
| 125 | public function addConditionDoubleTypes() |
| 126 | { |
| 127 | $this->query |
| 128 | ->where('availability.Terminanfangszeit', '!=', '00:00:00') |
| 129 | ->where('availability.Terminendzeit', '!=', '00:00:00') |
| 130 | ->where('availability.Anfangszeit', '!=', '00:00:00') |
| 131 | ->where('availability.Endzeit', '!=', '00:00:00'); |
| 132 | return $this; |
| 133 | } |
| 134 | |
| 135 | public function addConditionSkipOld(\DateTimeInterface $dateTime) |
| 136 | { |
| 137 | $date = $dateTime->format('Y-m-d'); |
| 138 | $this->query |
| 139 | ->where('availability.Endedatum', '>=', $date); |
| 140 | return $this; |
| 141 | } |
| 142 | |
| 143 | /** |
| 144 | * Used to identify availabilities whose End Date was more than 4 weeks ago |
| 145 | * |
| 146 | */ |
| 147 | public function addConditionOnlyOld(\DateTimeInterface $dateTime) |
| 148 | { |
| 149 | $date = $dateTime->format('Y-m-d'); |
| 150 | $this->query |
| 151 | ->where('availability.Endedatum', '<=', $date); |
| 152 | return $this; |
| 153 | } |
| 154 | |
| 155 | /** |
| 156 | * Identify availabilities between two dates |
| 157 | * |
| 158 | */ |
| 159 | public function addConditionTimeframe(\DateTimeInterface $startDate, \DateTimeInterface $endDate) |
| 160 | { |
| 161 | $this->query->where(function (\Solution10\SQL\ConditionBuilder $condition) use ($startDate, $endDate) { |
| 162 | $condition |
| 163 | ->andWith('availability.Startdatum', '<=', $endDate->format('Y-m-d')) |
| 164 | ->andWith('availability.Endedatum', '>=', $startDate->format('Y-m-d')); |
| 165 | }); |
| 166 | return $this; |
| 167 | } |
| 168 | |
| 169 | public function addConditionDate(\DateTimeInterface $dateTime) |
| 170 | { |
| 171 | $date = $dateTime->format('Y-m-d'); |
| 172 | $this->query |
| 173 | ->where('availability.Startdatum', '<=', $date) |
| 174 | ->where('availability.Endedatum', '>=', $date); |
| 175 | //-- match weekday |
| 176 | $this->query->where(self::expression("availability.Wochentag & POW(2, DAYOFWEEK('$date') - 1)"), '>=', '1'); |
| 177 | //-- match week |
| 178 | $this->query->where(self::expression(" |
| 179 | ( |
| 180 | ( |
| 181 | availability.allexWochen |
| 182 | AND FLOOR( |
| 183 | ( |
| 184 | FLOOR(UNIX_TIMESTAMP('$date')) |
| 185 | - FLOOR(UNIX_TIMESTAMP(availability.Startdatum))) |
| 186 | / 86400 |
| 187 | / 7 |
| 188 | ) |
| 189 | % availability.allexWochen = 0 |
| 190 | ) |
| 191 | OR ( |
| 192 | availability.jedexteWoche |
| 193 | AND ( |
| 194 | CEIL(DAYOFMONTH('$date') / 7) = availability.jedexteWoche |
| 195 | OR ( |
| 196 | availability.jedexteWoche = 5 |
| 197 | AND CEIL(LAST_DAY('$date') / 7) = CEIL(DAYOFMONTH('$date') / 7) |
| 198 | ) |
| 199 | ) |
| 200 | ) |
| 201 | OR (availability.allexWochen = 0 AND availability.jedexteWoche = 0) |
| 202 | ) AND 1 |
| 203 | "), '=', '1'); |
| 204 | return $this; |
| 205 | } |
| 206 | |
| 207 | public function addConditionAppointmentTime(\DateTimeInterface $dateTime) |
| 208 | { |
| 209 | $time = $dateTime->format('H:i:s'); |
| 210 | $this->query->where("availability.Terminanfangszeit", '<=', $time); |
| 211 | $this->query->where("availability.Terminendzeit", '>', $time); |
| 212 | |
| 213 | return $this; |
| 214 | } |
| 215 | |
| 216 | public function reverseEntityMapping(\BO\Zmsentities\Availability $entity) |
| 217 | { |
| 218 | $data = array(); |
| 219 | $data['StandortID'] = $entity->scope['id']; |
| 220 | $data['Offen_ab'] = $entity->bookable['startInDays']; |
| 221 | $data['Offen_bis'] = $entity->bookable['endInDays']; |
| 222 | $data['kommentar'] = $entity->description; |
| 223 | $data['Startdatum'] = $entity->getStartDateTime()->format('Y-m-d'); |
| 224 | $data['Endedatum'] = $entity->getEndDateTime()->format('Y-m-d'); |
| 225 | if ('openinghours' == $entity->type) { |
| 226 | $data['Anfangszeit'] = $entity->startTime; |
| 227 | $data['Endzeit'] = $entity->endTime; |
| 228 | $data['Terminanfangszeit'] = 0; |
| 229 | $data['Terminendzeit'] = 0; |
| 230 | } else { |
| 231 | $data['Anfangszeit'] = 0; |
| 232 | $data['Endzeit'] = 0; |
| 233 | $data['Terminanfangszeit'] = $entity->startTime; |
| 234 | $data['Terminendzeit'] = $entity->endTime; |
| 235 | } |
| 236 | $data['allexWochen'] = $entity->repeat['afterWeeks']; |
| 237 | $data['jedexteWoche'] = $entity->repeat['weekOfMonth']; |
| 238 | $data['Timeslot'] = gmdate("H:i", $entity->slotTimeInMinutes * 60); |
| 239 | $data['erlaubemehrfachslots'] = $entity->multipleSlotsAllowed ? 1 : 0; |
| 240 | $wochentagBinaryCoded = 0; |
| 241 | $binaryCodes = [ |
| 242 | 'sunday' => 1, |
| 243 | 'monday' => 2, |
| 244 | 'tuesday' => 4, |
| 245 | 'wednesday' => 8, |
| 246 | 'thursday' => 16, |
| 247 | 'friday' => 32, |
| 248 | 'saturday' => 64, |
| 249 | ]; |
| 250 | foreach ($entity->weekday as $weekday => $isActive) { |
| 251 | if ($isActive) { |
| 252 | $wochentagBinaryCoded |= $binaryCodes[$weekday]; |
| 253 | } |
| 254 | } |
| 255 | $data['Wochentag'] = $wochentagBinaryCoded; |
| 256 | $data['Anzahlterminarbeitsplaetze'] = $entity->workstationCount['intern']; |
| 257 | $data['reduktionTermineImInternet'] = |
| 258 | $entity->workstationCount['intern'] - $entity->workstationCount['public']; |
| 259 | $data['reduktionTermineCallcenter'] = |
| 260 | $entity->workstationCount['intern'] - $entity->workstationCount['callcenter']; |
| 261 | |
| 262 | $data = array_filter($data, function ($value) { |
| 263 | return ($value !== null && $value !== false); |
| 264 | }); |
| 265 | return $data; |
| 266 | } |
| 267 | |
| 268 | public static function getJoinExpression($process, $availability) |
| 269 | { |
| 270 | // UNIX_TIMESTAMP is relative here, no dependency to TIMEZONE |
| 271 | return self::expression(" |
| 272 | $availability.StandortID = $process.StandortID |
| 273 | AND $availability.OeffnungszeitID IS NOT NULL |
| 274 | |
| 275 | -- match weekday |
| 276 | AND $availability.Wochentag & POW(2, DAYOFWEEK($process.Datum) - 1) |
| 277 | |
| 278 | -- match week |
| 279 | AND ( |
| 280 | ( |
| 281 | $availability.allexWochen |
| 282 | AND FLOOR( |
| 283 | ( |
| 284 | FLOOR(UNIX_TIMESTAMP($process.Datum)) |
| 285 | - FLOOR(UNIX_TIMESTAMP($availability.Startdatum))) |
| 286 | / 86400 |
| 287 | / 7 |
| 288 | ) |
| 289 | % $availability.allexWochen = 0 |
| 290 | ) |
| 291 | OR ( |
| 292 | $availability.jedexteWoche |
| 293 | AND ( |
| 294 | CEIL(DAYOFMONTH($process.Datum) / 7) = $availability.jedexteWoche |
| 295 | OR ( |
| 296 | $availability.jedexteWoche = 5 |
| 297 | AND CEIL(LAST_DAY($process.Datum) / 7) = CEIL(DAYOFMONTH($process.Datum) / 7) |
| 298 | ) |
| 299 | ) |
| 300 | ) |
| 301 | OR (availability.allexWochen = 0 AND availability.jedexteWoche = 0) |
| 302 | ) |
| 303 | |
| 304 | -- match time and date |
| 305 | AND $process.Uhrzeit >= $availability.Terminanfangszeit |
| 306 | AND $process.Uhrzeit < $availability.Terminendzeit |
| 307 | AND $process.Datum >= $availability.Startdatum |
| 308 | AND $process.Datum <= $availability.Endedatum |
| 309 | "); |
| 310 | } |
| 311 | |
| 312 | public function postProcess($data) |
| 313 | { |
| 314 | $data[$this->getPrefixed("startDate")] = |
| 315 | (new \DateTime($data[$this->getPrefixed("startDate")]))->getTimestamp(); |
| 316 | $data[$this->getPrefixed("endDate")] = |
| 317 | (new \DateTime($data[$this->getPrefixed("endDate")]))->getTimestamp(); |
| 318 | $data[$this->getPrefixed("lastChange")] = |
| 319 | (new \DateTime($data[$this->getPrefixed("lastChange")] . \BO\Zmsdb\Connection\Select::$connectionTimezone)) |
| 320 | ->getTimestamp(); |
| 321 | return $data; |
| 322 | } |
| 323 | } |