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 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 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 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 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.erlaubemehrfachslots, 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.erlaubemehrfachslots, 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 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.erlaubemehrfachslots, 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.erlaubemehrfachslots, 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	}