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	SUM(ticketprinter) as ticketprinter,
28	SUM(ticketprintermissed) as ticketprintermissed
29
30	FROM (
31	SELECT
32	StandortID as subjectid,
33	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
34	IFNULL(SUM(gesendet), 0) as notificationscount,
35	0 as notificationscost,
36	0 AS clientscount,
37	0 AS missed,
38	0 AS withappointment,
39	0 AS missedwithappointment,
40	0 AS requestcount,
41	0 AS ticketprinter,
42	0 AS ticketprintermissed
43	FROM ' . self::NOTIFICATIONSTABLE . '
44	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend
45	GROUP BY date
46
47	UNION ALL
48	SELECT
49	StandortID as subjectid,
50	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
51	0 AS notificationscount,
52	0 as notificationscost,
53	SUM(IF(`nicht_erschienen`=0,AnzahlPersonen,0)) as clientscount,
54	SUM(IF(`nicht_erschienen`=1,AnzahlPersonen,0)) as missed,
55	SUM(IF(`nicht_erschienen`=0 AND mitTermin=1,AnzahlPersonen,0)) as withappointment,
56	SUM(IF(`nicht_erschienen`=1 AND mitTermin=1,AnzahlPersonen,0)) as missedwithappointment,
57	0 AS requestcount,
58	SUM(IF(`nicht_erschienen`=0 AND mitTermin=0 AND is_ticketprinter=1,AnzahlPersonen,0)) as ticketprinter,
59	SUM(IF(`nicht_erschienen`=1 AND mitTermin=0 AND is_ticketprinter=1,AnzahlPersonen,0)) as ticketprintermissed
60	FROM ' . ProcessStatusArchived::TABLE . '
61	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend
62	GROUP BY date
63
64	UNION ALL
65	SELECT
66	StandortID as subjectid,
67	IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
68	0 AS notificationscount,
69	0 as notificationscost,
70	0 AS clientscount,
71	0 AS missed,
72	0 AS withappointment,
73	0 AS missedwithappointment,
74	COUNT(IF(ba.AnliegenID > 0, ba.AnliegenID, null)) as requestcount,
75	0 AS ticketprinter,
76	0 AS ticketprintermissed
77	FROM ' . ProcessStatusArchived::TABLE . ' a
78	LEFT JOIN ' . self::BATABLE . ' as ba ON a.BuergerarchivID = ba.BuergerarchivID
79	WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend AND nicht_erschienen=0
80	GROUP BY date
81	) as unionresult
82	GROUP BY date;
83	';
84
85	const QUERY_SUBJECTS = '
86	SELECT
87	scope.`StandortID` as subject,
88	periodstart,
89	periodend,
90	CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
91	FROM ' . Scope::TABLE . ' AS scope
92	INNER JOIN
93	(
94	SELECT
95	s.standortid as scopeid,
96	MIN(s.`datum`) AS periodstart,
97	MAX(s.`datum`) AS periodend
98	FROM ' . self::TABLE . ' s
99	group by scopeid
100	)
101	maxAndminDate ON maxAndminDate.`scopeid` = scope.`StandortID`
102	GROUP BY scope.`StandortID`
103	ORDER BY scope.`StandortID` ASC
104	';
105
106	const QUERY_PERIODLIST_MONTH = '
107	SELECT date
108	FROM ' . Scope::TABLE . ' AS scope
109	INNER JOIN (
110	SELECT
111	`StandortID`,
112	DATE_FORMAT(`Datum`,"%Y-%m") AS date
113	FROM ' . self::TABLE . '
114	) s ON scope.`StandortID` = s.`standortid`
115	WHERE scope.`StandortID` = :scopeid
116	GROUP BY date
117	ORDER BY date ASC
118	';
119	}