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
3namespace BO\Zmsbackend\Day\Repository;
4
5/**
6 *
7 * Calculate Slots for available booking times
8 */
9class 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            PRIMARY KEY (scopeID, year, month) 
18        );
19    ';
20
21    const string QUERY_INSERT_TEMPORARY_SCOPELIST = '
22        INSERT INTO calendarscope SET
23            scopeID = :scopeID,
24            year = :year,
25            month = :month,
26            slotsRequired = :slotsRequired;
27    ';
28
29    const string QUERY_DROP_TEMPORARY_SCOPELIST = 'DROP TEMPORARY TABLE IF EXISTS calendarscope;';
30
31    /**
32     * Shared daylist used by zmsadmin / classic /calendar/ (same semantics as next).
33     *
34     * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS
35     */
36    const string QUERY_DAYLIST_JOIN = '
37        SELECT
38            year,
39            LPAD(month, 2, "0") AS month,
40            LPAD(day, 2, "0") AS day,
41            SUM(public) AS freeAppointments__public,
42            SUM(intern) AS freeAppointments__intern,
43            SUM(publicall) AS allAppointments__public,
44            SUM(internall) AS allAppointments__intern,
45            "sum" AS freeAppointments__type,
46            "free" AS allAppointments__type,
47            "bookable" AS status,
48            IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs
49        FROM
50        (
51            SELECT
52                year,
53                month,
54                day,
55                time,
56                slotsRequired,
57                COUNT(slotID) AS ancestorCount,
58                MIN(IF(public > confirmed, public - confirmed, 0)) AS public,
59                MIN(CAST(intern AS SIGNED) - confirmed) AS intern,
60                MIN(public) AS publicall,
61                MIN(intern) AS internall,
62                scopeID
63            FROM
64            (
65                SELECT
66                    IFNULL(COUNT(p.slotID), 0) AS confirmed,
67                    IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired,
68                    s.slotID,
69                    s.year,
70                    s.month,
71                    s.day,
72                    s.time,
73                    s.public,
74                    s.intern,
75                    cc.id,
76                    s.scopeID
77                FROM
78                    calendarscope c
79                    INNER JOIN slot s
80                        ON c.scopeID = s.scopeID
81                        AND c.year = s.year
82                        AND c.month = s.month
83                        AND s.status = "free"
84                    LEFT JOIN oeffnungszeit a
85                        ON s.availabilityID = a.OeffnungszeitID
86                    LEFT JOIN slot_hiera h
87                        ON h.ancestorID = s.slotID
88                        AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots)
89                    LEFT JOIN slot_process p
90                        ON h.slotID = p.slotID
91                    LEFT JOIN closures cc
92                        ON s.scopeID = cc.StandortID
93                        AND s.year = cc.year
94                        AND s.month = cc.month
95                        AND s.day = cc.day
96                GROUP BY s.slotID, h.slotID
97                HAVING cc.id IS NULL
98            ) AS slotaggregate
99            GROUP BY slotID, scopeID
100            HAVING ancestorCount >= slotsRequired
101        ) AS dayaggregate
102        GROUP BY year, month, day
103        ORDER BY year, month, day;
104    ';
105
106    /**
107     * Citizen calendar-availability daylist only.
108     *
109     * Occupancy is pre-aggregated from slot_process and hierarchy slots must be free,
110     * so multi-service daylists do not invent bookable days.
111     *
112     * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS_AVAILABILITY
113     */
114    const string QUERY_DAYLIST_JOIN_AVAILABILITY = '
115        SELECT
116            year,
117            LPAD(month, 2, "0") AS month,
118            LPAD(day, 2, "0") AS day,
119            SUM(public) AS freeAppointments__public,
120            SUM(intern) AS freeAppointments__intern,
121            SUM(publicall) AS allAppointments__public,
122            SUM(internall) AS allAppointments__intern,
123            "sum" AS freeAppointments__type,
124            "free" AS allAppointments__type,
125            "bookable" AS status,
126            IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs
127        FROM
128        (
129            SELECT
130                year,
131                month,
132                day,
133                time,
134                slotsRequired,
135                COUNT(slotID) AS ancestorCount,
136                MIN(IF(public > confirmed, public - confirmed, 0)) AS public,
137                MIN(CAST(intern AS SIGNED) - confirmed) AS intern,
138                MIN(public) AS publicall,
139                MIN(intern) AS internall,
140                scopeID
141            FROM
142            (
143                SELECT
144                    IFNULL(occ.confirmed, 0) AS confirmed,
145                    IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired,
146                    s.slotID,
147                    s.year,
148                    s.month,
149                    s.day,
150                    s.time,
151                    s.public,
152                    s.intern,
153                    s.scopeID
154                FROM
155                    calendarscope c
156                    INNER JOIN slot s
157                        ON c.scopeID = s.scopeID
158                        AND c.year = s.year
159                        AND c.month = s.month
160                        AND s.status = "free"
161                    LEFT JOIN oeffnungszeit a
162                        ON s.availabilityID = a.OeffnungszeitID
163                    LEFT JOIN slot_hiera h
164                        ON h.ancestorID = s.slotID
165                        AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots)
166                    INNER JOIN slot s2
167                        ON h.slotID = s2.slotID
168                        AND s2.status = "free"
169                    LEFT JOIN (
170                        SELECT p.slotID, COUNT(*) AS confirmed
171                        FROM slot_process p
172                        INNER JOIN slot s_occ
173                            ON s_occ.slotID = p.slotID
174                        INNER JOIN calendarscope c_occ
175                            ON c_occ.scopeID = s_occ.scopeID
176                            AND c_occ.year = s_occ.year
177                            AND c_occ.month = s_occ.month
178                        GROUP BY p.slotID
179                    ) occ
180                        ON occ.slotID = h.slotID
181                    LEFT JOIN closures cc
182                        ON s.scopeID = cc.StandortID
183                        AND s.year = cc.year
184                        AND s.month = cc.month
185                        AND s.day = cc.day
186                WHERE cc.id IS NULL
187            ) AS slotaggregate
188            GROUP BY slotID, scopeID
189            HAVING ancestorCount >= slotsRequired
190        ) AS dayaggregate
191        GROUP BY year, month, day
192        ORDER BY year, month, day;
193    ';
194}