Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | n/a |
0 / 0 |
n/a |
0 / 0 |
CRAP | n/a |
0 / 0 |
|||
Day | n/a |
0 / 0 |
n/a |
0 / 0 |
0 | n/a |
0 / 0 |
1 | <?php |
2 | |
3 | namespace BO\Zmsdb\Query; |
4 | |
5 | /** |
6 | * |
7 | * Calculate Slots for available booking times |
8 | */ |
9 | class Day extends Base |
10 | { |
11 | const QUERY_CREATE_TEMPORARY_SCOPELIST = ' |
12 | CREATE TEMPORARY TABLE calendarscope ( |
13 | scopeID INT, |
14 | year SMALLINT, |
15 | month TINYINT, |
16 | slotsRequired TINYINT, |
17 | PRIMARY KEY (scopeID, year, month) |
18 | ); |
19 | '; |
20 | |
21 | const QUERY_INSERT_TEMPORARY_SCOPELIST = ' |
22 | INSERT INTO calendarscope SET |
23 | scopeID = :scopeID, |
24 | year = :year, |
25 | month = :month, |
26 | slotsRequired = :slotsRequired; |
27 | '; |
28 | |
29 | const QUERY_DROP_TEMPORARY_SCOPELIST = 'DROP TEMPORARY TABLE IF EXISTS calendarscope;'; |
30 | |
31 | /** |
32 | * see also ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAY |
33 | */ |
34 | const QUERY_DAYLIST_JOIN = ' |
35 | SELECT |
36 | year, |
37 | LPAD(month, 2, "0") AS month, |
38 | LPAD(day, 2, "0") AS day, |
39 | SUM(public) AS freeAppointments__public, |
40 | SUM(callcenter) AS freeAppointments__callcenter, |
41 | SUM(intern) AS freeAppointments__intern, |
42 | SUM(publicall) AS allAppointments__public, |
43 | SUM(callcenterall) AS allAppointments__callcenter, |
44 | SUM(internall) AS allAppointments__intern, |
45 | "sum" AS freeAppointments__type, |
46 | "free" AS allAppointments__type, |
47 | "bookable" AS status, |
48 | GROUP_CONCAT(DISTINCT scopeID SEPARATOR ",") AS scopeIDs |
49 | FROM |
50 | ( |
51 | SELECT |
52 | year, |
53 | month, |
54 | day, |
55 | time, |
56 | slotsRequired, |
57 | COUNT(slotID) AS ancestorCount, |
58 | MIN(IF(public > confirmed, public - confirmed, 0)) AS public, |
59 | MIN(IF(callcenter > confirmed, callcenter - confirmed, 0)) AS callcenter, |
60 | MIN(CAST(intern AS SIGNED) - confirmed) AS intern, |
61 | MIN(public) AS publicall, |
62 | MIN(callcenter) AS callcenterall, |
63 | MIN(intern) AS internall, |
64 | scopeID |
65 | FROM |
66 | ( |
67 | SELECT |
68 | IFNULL(COUNT(p.slotID), 0) AS confirmed, |
69 | IF(a.erlaubemehrfachslots, c.slotsRequired, :forceRequiredSlots) AS slotsRequired, |
70 | s.slotID, |
71 | s.year, |
72 | s.month, |
73 | s.day, |
74 | s.time, |
75 | s.public, |
76 | s.callcenter, |
77 | s.intern, |
78 | cc.id, |
79 | s.scopeID |
80 | FROM |
81 | calendarscope c |
82 | INNER JOIN slot s |
83 | ON c.scopeID = s.scopeID |
84 | AND c.year = s.year |
85 | AND c.month = s.month |
86 | AND s.status = "free" |
87 | LEFT JOIN oeffnungszeit a |
88 | ON s.availabilityID = a.OeffnungszeitID |
89 | LEFT JOIN slot_hiera h |
90 | ON h.ancestorID = s.slotID |
91 | AND h.ancestorLevel <= IF(a.erlaubemehrfachslots, c.slotsRequired, :forceRequiredSlots) |
92 | LEFT JOIN slot_process p |
93 | ON h.slotID = p.slotID |
94 | LEFT JOIN closures cc |
95 | ON s.scopeID = cc.StandortID |
96 | AND s.year = cc.year |
97 | AND s.month = cc.month |
98 | AND s.day = cc.day |
99 | GROUP BY s.slotID, h.slotID |
100 | HAVING cc.id IS NULL |
101 | ) AS slotaggregate |
102 | GROUP BY slotID, scopeID |
103 | HAVING ancestorCount >= slotsRequired |
104 | ) AS dayaggregate |
105 | GROUP BY year, month, day |
106 | ORDER BY year, month, day; |
107 | '; |
108 | } |