1	<?php
2
3	namespace BO\Zmsdb\Query;
4
5	class ExchangeCapacityscope extends Base
6	{
7	/**
8	* @var String TABLE mysql table reference
9	*/
10	const TABLE = 'slot_process';
11
12	/**
13	* Time-series metrics for one scope: booked/planned capacity per day, all dates.
14	*/
15	const QUERY_CAPACITY_METRICS_BY_DAY_ALL_DATES = '
16	SELECT
17	`scopeID` as subjectid,
18	CONCAT(year, "-", LPAD(month, 2, 0), "-", LPAD(day, 2, 0)) as date,
19	SUM(slotcount),
20	SUM(intern),
21	SUM(slotbookedminutes),
22	SUM(slotplannedminutes),
23	SUM(slotcount_public),
24	SUM(public),
25	SUM(slotbookedminutes_public),
26	SUM(slotplannedminutes_public)
27	FROM (
28	SELECT s.scopeID, s.year, s.month, s.day, s.intern, s.public, s.slotTimeInMinutes,
29	COUNT(sp.slotID) as slotcount,
30	SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) as slotcount_public,
31	(COALESCE(s.intern, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes,
32	(COUNT(sp.slotID) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes,
33	(COALESCE(s.public, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes_public,
34	(SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes_public
35	FROM slot AS s
36	LEFT JOIN slot_process as sp USING(slotID)
37	LEFT JOIN buerger b ON sp.processID = b.BuergerID
38	LEFT JOIN apiclient ac ON b.apiClientID = ac.apiClientID
39	WHERE s.`scopeID` = :scopeid AND s.status = "free"
40	GROUP BY s.slotID
41	) AS innerquery
42	GROUP BY year, month, day
43	ORDER BY date ASC
44	';
45
46	/**
47	* Time-series metrics for one scope: booked/planned capacity per day, within a date range.
48	*/
49	const QUERY_CAPACITY_METRICS_BY_DAY_IN_DATE_RANGE = '
50	SELECT
51	`scopeID` as subjectid,
52	CONCAT(year, "-", LPAD(month, 2, 0), "-", LPAD(day, 2, 0)) as date,
53	SUM(slotcount),
54	SUM(intern),
55	SUM(slotbookedminutes),
56	SUM(slotplannedminutes),
57	SUM(slotcount_public),
58	SUM(public),
59	SUM(slotbookedminutes_public),
60	SUM(slotplannedminutes_public)
61	FROM (
62	SELECT s.scopeID, s.year, s.month, s.day, s.intern, s.public, s.slotTimeInMinutes,
63	COUNT(sp.slotID) as slotcount,
64	SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) as slotcount_public,
65	(COALESCE(s.intern, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes,
66	(COUNT(sp.slotID) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes,
67	(COALESCE(s.public, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes_public,
68	(SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes_public
69	FROM slot AS s
70	LEFT JOIN slot_process as sp USING(slotID)
71	LEFT JOIN buerger b ON sp.processID = b.BuergerID
72	LEFT JOIN apiclient ac ON b.apiClientID = ac.apiClientID
73	WHERE s.`scopeID` = :scopeid AND s.status = "free"
74	AND CONCAT(s.year, "-", LPAD(s.month, 2, 0), "-", LPAD(s.day, 2, 0))
75	BETWEEN :datestart AND :dateend
76	GROUP BY s.slotID
77	) AS innerquery
78	GROUP BY year, month, day
79	ORDER BY date ASC
80	';
81
82	/**
83	* Time-series metrics for one scope: booked/planned capacity per clock hour, within a date range.
84	*/
85	const QUERY_CAPACITY_METRICS_BY_HOUR_IN_DATE_RANGE = '
86	SELECT
87	`scopeID` as subjectid,
88	CONCAT(year, "-", LPAD(month, 2, 0), "-", LPAD(day, 2, 0), " ", LPAD(HOUR(`time`), 2, "0"), ":00") as date,
89	SUM(slotcount),
90	SUM(intern),
91	SUM(slotbookedminutes),
92	SUM(slotplannedminutes),
93	SUM(slotcount_public),
94	SUM(public),
95	SUM(slotbookedminutes_public),
96	SUM(slotplannedminutes_public)
97	FROM (
98	SELECT s.scopeID, s.year, s.month, s.day, s.time, s.intern, s.public, s.slotTimeInMinutes,
99	COUNT(sp.slotID) as slotcount,
100	SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) as slotcount_public,
101	(COALESCE(s.intern, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes,
102	(COUNT(sp.slotID) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes,
103	(COALESCE(s.public, 0) * COALESCE(s.slotTimeInMinutes, 0)) as slotplannedminutes_public,
104	(SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) * COALESCE(s.slotTimeInMinutes, 0)) as slotbookedminutes_public
105	FROM slot AS s
106	LEFT JOIN slot_process as sp USING(slotID)
107	LEFT JOIN buerger b ON sp.processID = b.BuergerID
108	LEFT JOIN apiclient ac ON b.apiClientID = ac.apiClientID
109	WHERE s.`scopeID` = :scopeid AND s.status = "free"
110	AND CONCAT(s.year, "-", LPAD(s.month, 2, 0), "-", LPAD(s.day, 2, 0))
111	BETWEEN :datestart AND :dateend
112	GROUP BY s.slotID
113	) AS innerquery
114	GROUP BY year, month, day, HOUR(`time`)
115	ORDER BY date ASC
116	';
117
118	/**
119	* Scope picker list: one row per scope with min/max slot dates and description (no capacity numbers).
120	*/
121	const QUERY_CAPACITY_REPORT_SCOPE_SUBJECT_LIST = '
122	SELECT
123	scope.`StandortID` as subject,
124	MIN(CONCAT(s.year, "-", LPAD(s.month, 2, 0), "-", LPAD(s.day, 2, 0))) AS periodstart,
125	MAX(CONCAT(s.year, "-", LPAD(s.month, 2, 0), "-", LPAD(s.day, 2, 0))) AS periodend,
126	CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
127	FROM ' . Scope::TABLE . ' AS scope
128	INNER JOIN slot AS s ON s.scopeID = scope.StandortID
129	GROUP BY scope.`StandortID`
130	ORDER BY description ASC
131	';
132	}