1	<?php
2
3	namespace BO\Zmsdb\Query;
4
5	class ExchangeRequestdepartment extends Base
6	{
7	/**
8	* @var String TABLE mysql table reference
9	*/
10	const TABLE = 'statistik';
11
12	const REQUESTTABLE = 'request';
13
14	const QUERY_READ_REPORT = '
15	SELECT
16	d.BehoerdenID as departmentid,
17	statistikJoin.`organisationsid` as organisationid,
18	DATE_FORMAT(statistikJoin.`datum`, :groupby) as date,
19	(
20	CASE
21	WHEN statistikJoin.anliegenid = -1 THEN "Dienstleistung wurde nicht erfasst"
22	WHEN statistikJoin.anliegenid = 0 THEN "Dienstleistung konnte nicht erbracht werden"
23	ELSE r.name
24	END
25	) as name,
26	SUM(statistikJoin.requestscount) as requestscount,
27	AVG(statistikJoin.processingtime) as processingtime
28	FROM ' . Department::TABLE . ' d
29	INNER JOIN (
30	SELECT
31	s.anliegenid,
32	s.behoerdenid,
33	s.organisationsid,
34	COUNT(s.anliegenid) as requestscount,
35	AVG(s.bearbeitungszeit) as processingtime,
36	s.`datum`
37	FROM ' . self::TABLE . ' s
38	WHERE s.behoerdenid = :departmentid AND s.`datum` BETWEEN :datestart AND :dateend
39	GROUP BY s.`datum`, s.anliegenid
40	) as statistikJoin ON statistikJoin.`behoerdenid` = d.BehoerdenID
41	LEFT JOIN ' . self::REQUESTTABLE . ' r ON r.id = statistikJoin.anliegenid
42	WHERE d.`behoerdenid` = :departmentid AND statistikJoin.`datum` BETWEEN :datestart AND :dateend
43	GROUP BY DATE_FORMAT(statistikJoin.`datum`, :groupby), name, statistikJoin.anliegenid
44	ORDER BY r.name, statistikJoin.anliegenid
45	';
46
47	const QUERY_SUBJECTS = '
48	SELECT
49	d.`BehoerdenID` as subject,
50	periodstart,
51	periodend,
52	o.`Organisationsname` AS organisationname,
53	d.`Name` AS description
54	FROM ' . Department::TABLE . ' AS d
55	INNER JOIN
56	(
57	SELECT
58	s.`behoerdenid` as departmentid,
59	MIN(s.`datum`) AS periodstart,
60	MAX(s.`datum`) AS periodend
61	FROM ' . self::TABLE . ' s
62	group by departmentid
63	)
64	maxAndminDate ON maxAndminDate.`departmentid` = d.`BehoerdenID`
65	LEFT JOIN ' . Organisation::TABLE . ' AS o ON d.`OrganisationsID` = o.`OrganisationsID`
66	GROUP BY d.`BehoerdenID`
67	ORDER BY d.`BehoerdenID` ASC
68	';
69
70	const QUERY_PERIODLIST_MONTH = '
71	SELECT date
72	FROM ' . Department::TABLE . ' AS d
73	INNER JOIN (
74	SELECT
75	behoerdenid,
76	DATE_FORMAT(`datum`,"%Y-%m") AS date
77	FROM ' . self::TABLE . '
78	) s ON s.behoerdenid = d.BehoerdenID
79	WHERE d.`BehoerdenID` = :departmentid
80	GROUP BY date
81	ORDER BY date ASC
82	';
83	}