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