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 QUERY_READ_REPORT = '
16	SELECT
17	MIN(subjectid) as subjectid,
18	date,
19	SUM(clientscount) as clientscount,
20	SUM(missed) as missed,
21	SUM(withappointment) as withappointment,
22	SUM(missedwithappointment) as missedwithappointment,
23	SUM(requestcount) as requestcount
24
25	FROM (
26	SELECT
27	o.OrganisationsID as subjectid,
28	DATE_FORMAT(a.`Datum`, :groupby) as date,
29	SUM(IF(a.`nicht_erschienen`=0,a.AnzahlPersonen,0)) as clientscount,
30	SUM(IF(a.`nicht_erschienen`=1,a.AnzahlPersonen,0)) as missed,
31	SUM(IF(a.`nicht_erschienen`=0 AND a.mitTermin=1,a.AnzahlPersonen,0)) as withappointment,
32	SUM(IF(a.`nicht_erschienen`=1 AND a.mitTermin=1,a.AnzahlPersonen,0)) as missedwithappointment,
33	0 as requestcount
34	FROM ' . Organisation::TABLE . ' o
35	LEFT JOIN ' . Department::TABLE . ' d ON d.`OrganisationsID` = o.`OrganisationsID`
36	LEFT JOIN ' . Scope::TABLE . ' scope ON scope.`BehoerdenID` = d.`BehoerdenID`
37	LEFT JOIN ' . ProcessStatusArchived::TABLE . ' a ON a.`StandortID` = scope.`StandortID`
38	WHERE o.`OrganisationsID` = :organisationid AND a.`Datum` BETWEEN :datestart AND :dateend
39	GROUP BY date
40
41	UNION ALL
42	SELECT
43	o.OrganisationsID as subjectid,
44	DATE_FORMAT(a.`Datum`, :groupby) as date,
45	0 as clientscount,
46	0 as missed,
47	0 as withappointment,
48	0 as missedwithappointment,
49	COUNT(IF(ba.AnliegenID > 0, ba.AnliegenID, null)) as requestcount
50	FROM ' . Organisation::TABLE . ' o
51	LEFT JOIN ' . Department::TABLE . ' d ON d.`OrganisationsID` = o.`OrganisationsID`
52	LEFT JOIN ' . Scope::TABLE . ' as scope ON d.`BehoerdenID` = scope.`BehoerdenID`
53	LEFT JOIN ' . ProcessStatusArchived::TABLE . ' as a ON scope.`StandortID` = a.`StandortID`
54	LEFT JOIN ' . self::BATABLE . ' as ba ON a.BuergerarchivID = ba.BuergerarchivID
55	WHERE
56	o.`OrganisationsID` = :organisationid AND
57	a.nicht_erschienen=0 AND
58	a.`Datum` BETWEEN :datestart AND :dateend
59	GROUP BY date
60	) as unionresult
61
62	GROUP BY date
63	';
64
65
66	//fast query from statistic table, but statistic is not up-to-date - 2008 - 2011 not available or complete
67	const QUERY_SUBJECTS = '
68	SELECT
69	o.`OrganisationsID` as subject,
70	periodstart,
71	periodend,
72	o.`Organisationsname` AS description
73	FROM ' . Organisation::TABLE . ' AS o
74	INNER JOIN
75	(
76	SELECT
77	s.`organisationsid` AS organisationsid,
78	MIN(s.`datum`) AS periodstart,
79	MAX(s.`datum`) AS periodend
80	FROM ' . self::TABLE . ' s
81	group by organisationsid
82	)
83	maxAndminDate ON maxAndminDate.`organisationsid` = o.`OrganisationsID`
84	GROUP BY o.`OrganisationsID`
85	ORDER BY o.`OrganisationsID` ASC
86	';
87
88	const QUERY_PERIODLIST_MONTH = '
89	SELECT date
90	FROM ' . Organisation::TABLE . ' AS o
91	INNER JOIN (
92	SELECT
93	organisationsid,
94	DATE_FORMAT(`datum`,"%Y-%m") AS date
95	FROM ' . self::TABLE . '
96	) s ON s.organisationsid = o.`OrganisationsID`
97	WHERE o.`OrganisationsID` = :organisationid
98	GROUP BY date
99	ORDER BY date ASC
100	';
101	}