1	<?php
2
3	namespace BO\Zmsdb\Query;
4
5	class ExchangeClientdepartment 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	d.behoerdenid as subjectid,
31	DATE_FORMAT(n.`Datum`, :groupby) as date,
32	IFNULL(SUM(n.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 ' . Department::TABLE . ' d
40	LEFT JOIN ' . Scope::TABLE . ' scope ON scope.`BehoerdenID` = d.`BehoerdenID`
41	LEFT JOIN abrechnung n ON n.`StandortID` = scope.`StandortID`
42	WHERE d.`BehoerdenID` = :departmentid AND n.`Datum` BETWEEN :datestart AND :dateend
43	GROUP BY date
44
45	UNION ALL
46	SELECT
47	d.behoerdenid as subjectid,
48	DATE_FORMAT(a.`Datum`, :groupby) as date,
49	0 as notificationscount,
50	0 as notificationscost,
51	SUM(IF(a.`nicht_erschienen`=0,a.AnzahlPersonen,0)) as clientscount,
52	SUM(IF(a.`nicht_erschienen`=1,a.AnzahlPersonen,0)) as missed,
53	SUM(IF(a.`nicht_erschienen`=0 AND a.mitTermin=1,a.AnzahlPersonen,0)) as withappointment,
54	SUM(IF(a.`nicht_erschienen`=1 AND a.mitTermin=1,a.AnzahlPersonen,0)) as missedwithappointment,
55	0 as requestcount
56	FROM ' . Department::TABLE . ' d
57	LEFT JOIN ' . Scope::TABLE . ' scope ON scope.`BehoerdenID` = d.`BehoerdenID`
58	LEFT JOIN ' . ProcessStatusArchived::TABLE . ' a ON a.`StandortID` = scope.`StandortID`
59	WHERE d.`BehoerdenID` = :departmentid AND a.`Datum` BETWEEN :datestart AND :dateend
60	GROUP BY date
61	UNION ALL
62	SELECT
63	d.behoerdenid as subjectid,
64	DATE_FORMAT(a.`Datum`, :groupby) as date,
65	0 as notificationscount,
66	0 as notificationscost,
67	0 as clientscount,
68	0 as missed,
69	0 as withappointment,
70	0 as missedwithappointment,
71	COUNT(IF(ba.AnliegenID > 0, ba.AnliegenID, null)) as requestcount
72	FROM ' . Department::TABLE . ' as d
73	LEFT JOIN ' . Scope::TABLE . ' as scope ON d.`BehoerdenID` = scope.`BehoerdenID`
74	LEFT JOIN ' . ProcessStatusArchived::TABLE . ' as a ON scope.`StandortID` = a.`StandortID`
75	LEFT JOIN ' . self::BATABLE . ' as ba ON a.BuergerarchivID = ba.BuergerarchivID
76	WHERE
77	d.`BehoerdenID` = :departmentid AND
78	a.nicht_erschienen=0 AND
79	a.`Datum` BETWEEN :datestart AND :dateend
80	GROUP BY date
81	) as unionresult
82
83	GROUP BY date
84	';
85
86
87	//fast query from statistic table, but statistic is not up-to-date - 2008 - 2011 not available or complete
88	const QUERY_SUBJECTS = '
89	SELECT
90	d.`BehoerdenID` as subject,
91	periodstart,
92	periodend,
93	o.`Organisationsname` AS organisationname,
94	d.`Name` AS description
95	FROM ' . Department::TABLE . ' AS d
96	INNER JOIN
97	(
98	SELECT
99	s.`behoerdenid` as departmentid,
100	MIN(s.`datum`) AS periodstart,
101	MAX(s.`datum`) AS periodend
102	FROM ' . self::TABLE . ' s
103	group by departmentid
104	)
105	maxAndminDate ON maxAndminDate.`departmentid` = d.`BehoerdenID`
106	LEFT JOIN ' . Organisation::TABLE . ' AS o ON d.`OrganisationsID` = o.`OrganisationsID`
107	GROUP BY d.`BehoerdenID`
108	ORDER BY d.`BehoerdenID` ASC
109	';
110
111	const QUERY_PERIODLIST_MONTH = '
112	SELECT date
113	FROM ' . Department::TABLE . ' AS d
114	INNER JOIN (
115	SELECT
116	behoerdenid,
117	DATE_FORMAT(`datum`,"%Y-%m") AS date
118	FROM ' . self::TABLE . '
119	) s ON s.behoerdenid = d.BehoerdenID
120	WHERE d.`BehoerdenID` = :departmentid
121	GROUP BY date
122	ORDER BY date ASC
123	';
124	}