1	<?php
2
3	namespace BO\Zmsdb\Query;
4
5	class ExchangeClientscope extends Base
6	{
7	/**
8	* @var String TABLE mysql table reference
9	*/
10	const TABLE = 'statistik';
11
12	const BATABLE = 'buergeranliegen';
13
14	const NOTIFICATIONSTABLE = 'abrechnung';
15
16	const QUERY_READ_REPORT = '
17	SELECT
18	MIN(subjectid) as subjectid,
19	date,
20	notificationscount,
21	0 as notificationscost,
22	SUM(clientscount) as clientscount,
23	SUM(missed) as missed,
24	SUM(withappointment) as withappointment,
25	SUM(missedwithappointment) as missedwithappointment,
26	SUM(requestcount) as requestcount
27
28	FROM (
29	SELECT
30	StandortID as subjectid,
31	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
32	IFNULL(SUM(gesendet), 0) as notificationscount,
33	0 as notificationscost,
34	0 AS clientscount,
35	0 AS missed,
36	0 AS withappointment,
37	0 AS missedwithappointment,
38	0 AS requestcount
39	FROM ' . self::NOTIFICATIONSTABLE . '
40	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend
41	GROUP BY date
42
43	UNION ALL
44	SELECT
45	StandortID as subjectid,
46	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
47	0 AS notificationscount,
48	0 as notificationscost,
49	SUM(IF(`nicht_erschienen`=0,AnzahlPersonen,0)) as clientscount,
50	SUM(IF(`nicht_erschienen`=1,AnzahlPersonen,0)) as missed,
51	SUM(IF(`nicht_erschienen`=0 AND mitTermin=1,AnzahlPersonen,0)) as withappointment,
52	SUM(IF(`nicht_erschienen`=1 AND mitTermin=1,AnzahlPersonen,0)) as missedwithappointment,
53	0 AS requestcount
54	FROM ' . ProcessStatusArchived::TABLE . '
55	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend
56	GROUP BY date
57
58	UNION ALL
59	SELECT
60	StandortID as subjectid,
61	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
62	0 AS notificationscount,
63	0 as notificationscost,
64	0 AS clientscount,
65	0 AS missed,
66	0 AS withappointment,
67	0 AS missedwithappointment,
68	COUNT(IF(ba.AnliegenID > 0, ba.AnliegenID, null)) as requestcount
69	FROM ' . ProcessStatusArchived::TABLE . ' a
70	LEFT JOIN ' . self::BATABLE . ' as ba ON a.BuergerarchivID = ba.BuergerarchivID
71	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend AND nicht_erschienen=0
72	GROUP BY date
73	) as unionresult
74	GROUP BY date;
75	';
76
77	const QUERY_SUBJECTS = '
78	SELECT
79	scope.`StandortID` as subject,
80	periodstart,
81	periodend,
82	CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
83	FROM ' . Scope::TABLE . ' AS scope
84	INNER JOIN
85	(
86	SELECT
87	s.standortid as scopeid,
88	MIN(s.`datum`) AS periodstart,
89	MAX(s.`datum`) AS periodend
90	FROM ' . self::TABLE . ' s
91	group by scopeid
92	)
93	maxAndminDate ON maxAndminDate.`scopeid` = scope.`StandortID`
94	GROUP BY scope.`StandortID`
95	ORDER BY scope.`StandortID` ASC
96	';
97
98	const QUERY_PERIODLIST_MONTH = '
99	SELECT date
100	FROM ' . Scope::TABLE . ' AS scope
101	INNER JOIN (
102	SELECT
103	`StandortID`,
104	DATE_FORMAT(`Datum`,"%Y-%m") AS date
105	FROM ' . self::TABLE . '
106	) s ON scope.`StandortID` = s.`standortid`
107	WHERE scope.`StandortID` = :scopeid
108	GROUP BY date
109	ORDER BY date ASC
110	';
111	}