Lines
n/a
0 / 0
Functions and Methods
n/a
0 / 0
Classes and Traits
n/a
0 / 0
| Name | Lines | Functions and Methods | CRAP | Classes and Traits | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Day | n/a | 0 / 0 | n/a | 0 / 0 | 0 | n/a | 0 / 0 | |||
| 1 | <?php | |
| 2 | ||
| 3 | namespace BO\Zmsbackend\Day\Repository; | |
| 4 | ||
| 5 | /** | |
| 6 | * | |
| 7 | * Calculate Slots for available booking times | |
| 8 | */ | |
| 9 | class Day extends \BO\Zmsbackend\Query\Base | |
| 10 | { | |
| 11 | const string QUERY_CREATE_TEMPORARY_SCOPELIST = ' | |
| 12 | CREATE TEMPORARY TABLE calendarscope ( | |
| 13 | scopeID INT, | |
| 14 | year SMALLINT, | |
| 15 | month TINYINT, | |
| 16 | slotsRequired TINYINT, | |
| 17 | appointmentInterval SMALLINT NOT NULL DEFAULT 5, | |
| 18 | PRIMARY KEY (scopeID, year, month) | |
| 19 | ); | |
| 20 | '; | |
| 21 | ||
| 22 | const string QUERY_INSERT_TEMPORARY_SCOPELIST = ' | |
| 23 | INSERT INTO calendarscope SET | |
| 24 | scopeID = :scopeID, | |
| 25 | year = :year, | |
| 26 | month = :month, | |
| 27 | slotsRequired = :slotsRequired, | |
| 28 | appointmentInterval = :appointmentInterval; | |
| 29 | '; | |
| 30 | ||
| 31 | /** | |
| 32 | * Slot count and stored slot length for each request at the given locations. | |
| 33 | * Placeholders for StandortID IN (...) are injected by Day::readAppointmentIntervalByScope. | |
| 34 | */ | |
| 35 | const string QUERY_SELECT_SCOPE_REQUEST_DURATIONS = ' | |
| 36 | SELECT sc.`StandortID` AS scopeId, | |
| 37 | rp.`slots` AS slots, | |
| 38 | CAST(JSON_UNQUOTE(JSON_EXTRACT(p.`data`, \'$.slotTimeInMinutes\')) AS UNSIGNED) AS minutes | |
| 39 | FROM `standort` sc | |
| 40 | INNER JOIN `provider` p | |
| 41 | ON p.`id` = sc.`InfoDienstleisterID` | |
| 42 | AND p.`source` = sc.`source` | |
| 43 | INNER JOIN `request_provider` rp | |
| 44 | ON rp.`provider__id` = p.`id` | |
| 45 | AND rp.`source` = p.`source` | |
| 46 | AND rp.`slots` > 0 | |
| 47 | WHERE sc.`StandortID` IN (%s) | |
| 48 | '; | |
| 49 | ||
| 50 | /** | |
| 51 | * On a 5-minute grid, keep a free start only when it lands on the location's | |
| 52 | * appointment step, measured from the opening start. Any other stored slot | |
| 53 | * length already is the step, so those starts stay. | |
| 54 | */ | |
| 55 | const string QUERY_ON_APPOINTMENT_INTERVAL = ' | |
| 56 | AND ( | |
| 57 | IFNULL(s.`slotTimeInMinutes`, 0) <> 5 | |
| 58 | OR MOD( | |
| 59 | TIME_TO_SEC(s.`time`) - TIME_TO_SEC( | |
| 60 | IF(a.`appointment_start_time`, a.`appointment_start_time`, a.`start_time`) | |
| 61 | ), | |
| 62 | GREATEST(c.`appointmentInterval`, 1) * 60 | |
| 63 | ) = 0 | |
| 64 | ) | |
| 65 | '; | |
| 66 | ||
| 67 | const string QUERY_DROP_TEMPORARY_SCOPELIST = 'DROP TEMPORARY TABLE IF EXISTS calendarscope;'; | |
| 68 | ||
| 69 | /** | |
| 70 | * Shared daylist used by zmsadmin / classic /calendar/. | |
| 71 | * Aggregate once per starting slot; count occupied hierarchy slots through the | |
| 72 | * slot_process primary key instead of grouping the joined bookings separately. | |
| 73 | * | |
| 74 | * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS | |
| 75 | */ | |
| 76 | const string QUERY_DAYLIST_JOIN = ' | |
| 77 | SELECT | |
| 78 | year, | |
| 79 | LPAD(month, 2, "0") AS month, | |
| 80 | LPAD(day, 2, "0") AS day, | |
| 81 | SUM(public) AS freeAppointments__public, | |
| 82 | SUM(intern) AS freeAppointments__intern, | |
| 83 | SUM(publicall) AS allAppointments__public, | |
| 84 | SUM(internall) AS allAppointments__intern, | |
| 85 | "sum" AS freeAppointments__type, | |
| 86 | "free" AS allAppointments__type, | |
| 87 | "bookable" AS status, | |
| 88 | IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs | |
| 89 | FROM ( | |
| 90 | SELECT s.year, s.month, s.day, s.time, s.scopeID, | |
| 91 | IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired, | |
| 92 | GREATEST(1, COUNT(DISTINCT h.slotID)) AS ancestorCount, | |
| 93 | MIN(IFNULL(GREATEST(0, CAST(s.`public` AS SIGNED) - | |
| 94 | (SELECT CAST(COUNT(*) AS SIGNED) FROM slot_process p WHERE p.slotID = h.slotID)), 0)) AS public, | |
| 95 | MIN(CAST(s.intern AS SIGNED) - | |
| 96 | (SELECT CAST(COUNT(*) AS SIGNED) FROM slot_process p WHERE p.slotID = h.slotID)) AS intern, | |
| 97 | MIN(s.`public`) AS publicall, | |
| 98 | MIN(s.intern) AS internall | |
| 99 | FROM calendarscope c | |
| 100 | INNER JOIN slot s ON c.scopeID = s.scopeID | |
| 101 | AND c.year = s.year AND c.month = s.month AND s.status = "free" | |
| 102 | LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID | |
| 103 | LEFT JOIN slot_hiera h ON h.ancestorID = s.slotID | |
| 104 | AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) | |
| 105 | WHERE NOT EXISTS ( | |
| 106 | SELECT 1 FROM closures cc | |
| 107 | WHERE cc.StandortID = s.scopeID AND cc.year = s.year | |
| 108 | AND cc.month = s.month AND cc.day = s.day | |
| 109 | ) | |
| 110 | ' . self::QUERY_ON_APPOINTMENT_INTERVAL . ' | |
| 111 | GROUP BY s.slotID | |
| 112 | HAVING ancestorCount >= slotsRequired | |
| 113 | ) AS dayaggregate | |
| 114 | GROUP BY year, month, day | |
| 115 | ORDER BY year, month, day; | |
| 116 | '; | |
| 117 | ||
| 118 | /** | |
| 119 | * Citizen calendar-availability daylist only. | |
| 120 | * | |
| 121 | * Occupancy is pre-aggregated from slot_process and hierarchy slots must be free, | |
| 122 | * so multi-service daylists do not invent bookable days. | |
| 123 | * | |
| 124 | * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS_AVAILABILITY | |
| 125 | */ | |
| 126 | const string QUERY_DAYLIST_JOIN_AVAILABILITY = ' | |
| 127 | SELECT | |
| 128 | year, | |
| 129 | LPAD(month, 2, "0") AS month, | |
| 130 | LPAD(day, 2, "0") AS day, | |
| 131 | SUM(public) AS freeAppointments__public, | |
| 132 | SUM(intern) AS freeAppointments__intern, | |
| 133 | SUM(publicall) AS allAppointments__public, | |
| 134 | SUM(internall) AS allAppointments__intern, | |
| 135 | "sum" AS freeAppointments__type, | |
| 136 | "free" AS allAppointments__type, | |
| 137 | "bookable" AS status, | |
| 138 | IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs | |
| 139 | FROM | |
| 140 | ( | |
| 141 | SELECT | |
| 142 | year, | |
| 143 | month, | |
| 144 | day, | |
| 145 | time, | |
| 146 | slotsRequired, | |
| 147 | COUNT(slotID) AS ancestorCount, | |
| 148 | MIN(IF(public > confirmed, public - confirmed, 0)) AS public, | |
| 149 | MIN(CAST(intern AS SIGNED) - confirmed) AS intern, | |
| 150 | MIN(public) AS publicall, | |
| 151 | MIN(intern) AS internall, | |
| 152 | scopeID | |
| 153 | FROM | |
| 154 | ( | |
| 155 | SELECT | |
| 156 | IFNULL(occ.confirmed, 0) AS confirmed, | |
| 157 | IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired, | |
| 158 | s.slotID, | |
| 159 | s.year, | |
| 160 | s.month, | |
| 161 | s.day, | |
| 162 | s.time, | |
| 163 | s.public, | |
| 164 | s.intern, | |
| 165 | s.scopeID | |
| 166 | FROM | |
| 167 | calendarscope c | |
| 168 | INNER JOIN slot s | |
| 169 | ON c.scopeID = s.scopeID | |
| 170 | AND c.year = s.year | |
| 171 | AND c.month = s.month | |
| 172 | AND s.status = "free" | |
| 173 | LEFT JOIN oeffnungszeit a | |
| 174 | ON s.availabilityID = a.OeffnungszeitID | |
| 175 | LEFT JOIN slot_hiera h | |
| 176 | ON h.ancestorID = s.slotID | |
| 177 | AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) | |
| 178 | INNER JOIN slot s2 | |
| 179 | ON h.slotID = s2.slotID | |
| 180 | AND s2.status = "free" | |
| 181 | LEFT JOIN ( | |
| 182 | SELECT p.slotID, COUNT(*) AS confirmed | |
| 183 | FROM slot_process p | |
| 184 | INNER JOIN slot s_occ | |
| 185 | ON s_occ.slotID = p.slotID | |
| 186 | INNER JOIN calendarscope c_occ | |
| 187 | ON c_occ.scopeID = s_occ.scopeID | |
| 188 | AND c_occ.year = s_occ.year | |
| 189 | AND c_occ.month = s_occ.month | |
| 190 | GROUP BY p.slotID | |
| 191 | ) occ | |
| 192 | ON occ.slotID = h.slotID | |
| 193 | LEFT JOIN closures cc | |
| 194 | ON s.scopeID = cc.StandortID | |
| 195 | AND s.year = cc.year | |
| 196 | AND s.month = cc.month | |
| 197 | AND s.day = cc.day | |
| 198 | WHERE cc.id IS NULL | |
| 199 | ' . self::QUERY_ON_APPOINTMENT_INTERVAL . ' | |
| 200 | ) AS slotaggregate | |
| 201 | GROUP BY slotID, scopeID | |
| 202 | HAVING ancestorCount >= slotsRequired | |
| 203 | ) AS dayaggregate | |
| 204 | GROUP BY year, month, day | |
| 205 | ORDER BY year, month, day; | |
| 206 | '; | |
| 207 | } |