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
3namespace BO\Zmsdb\Query;
4
5/**
6 *
7 * Calculate Slots for available booking times
8 */
9class 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}