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