Lines 96.58% 113 / 117
Methods 96.55% 28 / 29
Classes 0.00% 0 / 1
Name Lines Methods CRAP
 [BO\Zmsbackend\Query\Base] __construct 88.23% 30 / 34 0.00% 0 / 1 10.16
 [BO\Zmsbackend\Query\Base] __toString n/a 0 / 0 n/a 0 / 0 4
 [BO\Zmsbackend\Query\Base] getName 100.00% 1 / 1 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addSelect 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] setDistinctSelect 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] setResolveLevel 100.00% 3 / 3 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getResolveLevel 100.00% 3 / 3 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getAlias 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getTablename 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addTable 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addTableAlias 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addRequiredJoins 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addResolvedReferences 100.00% 11 / 11 100.00% 1 / 1 3
 [BO\Zmsbackend\Query\Base] setWithEntities 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addJoin 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] leftJoin 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] innerJoin 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getSql 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getParameters 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getReferenceMapping 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] expression 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addEntityMapping 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getPrefixed 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getPrefixedList 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addReferenceMapping 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addLimit 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addValues 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] postProcess 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] postProcessJoins 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] shouldLoadEntity 100.00% 3 / 3 100.00% 1 / 1 2
9class Day extends \BO\Zmsbackend\Query\Base
10{
11    const string QUERY_CREATE_TEMPORARY_SCOPELIST = '
12        CREATE TEMPORARY TABLE calendarscope (
13            scopeID INT,
14            year SMALLINT,
15            month TINYINT,
16            slotsRequired TINYINT,
17            appointmentInterval SMALLINT NOT NULL DEFAULT 5,
18            PRIMARY KEY (scopeID, year, month) 
19        );
20    ';
21
22    const string QUERY_INSERT_TEMPORARY_SCOPELIST = '
23        INSERT INTO calendarscope SET
24            scopeID = :scopeID,
25            year = :year,
26            month = :month,
27            slotsRequired = :slotsRequired,
28            appointmentInterval = :appointmentInterval;
29    ';
30
31    /**
32     * Slot count and stored slot length for each request at the given locations.
33     * Placeholders for StandortID IN (...) are injected by Day::readAppointmentIntervalByScope.
34     */
35    const string QUERY_SELECT_SCOPE_REQUEST_DURATIONS = '
36        SELECT sc.`StandortID` AS scopeId,
37               rp.`slots` AS slots,
38               CAST(JSON_UNQUOTE(JSON_EXTRACT(p.`data`, \'$.slotTimeInMinutes\')) AS UNSIGNED) AS minutes
39        FROM `standort` sc
40        INNER JOIN `provider` p
41            ON p.`id` = sc.`InfoDienstleisterID`
42            AND p.`source` = sc.`source`
43        INNER JOIN `request_provider` rp
44            ON rp.`provider__id` = p.`id`
45            AND rp.`source` = p.`source`
46            AND rp.`slots` > 0
47        WHERE sc.`StandortID` IN (%s)
48    ';
49
50    /**
51     * On a 5-minute grid, keep a free start only when it lands on the location's
52     * appointment step, measured from the opening start. Any other stored slot
53     * length already is the step, so those starts stay.
54     */
55    const string QUERY_ON_APPOINTMENT_INTERVAL = '
56        AND (
57            IFNULL(s.`slotTimeInMinutes`, 0) <> 5
58            OR MOD(
59                TIME_TO_SEC(s.`time`) - TIME_TO_SEC(
60                    IF(a.`appointment_start_time`, a.`appointment_start_time`, a.`start_time`)
61                ),
62                GREATEST(c.`appointmentInterval`, 1) * 60
63            ) = 0
64        )
65    ';
66
67    const string QUERY_DROP_TEMPORARY_SCOPELIST = 'DROP TEMPORARY TABLE IF EXISTS calendarscope;';
68
69    /**
70     * Shared daylist used by zmsadmin / classic /calendar/.
71     * Aggregate once per starting slot; count occupied hierarchy slots through the
72     * slot_process primary key instead of grouping the joined bookings separately.
73     *
74     * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS
75     */
76    const string QUERY_DAYLIST_JOIN = '
77        SELECT
78            year,
79            LPAD(month, 2, "0") AS month,
80            LPAD(day, 2, "0") AS day,
81            SUM(public) AS freeAppointments__public,
82            SUM(intern) AS freeAppointments__intern,
83            SUM(publicall) AS allAppointments__public,
84            SUM(internall) AS allAppointments__intern,
85            "sum" AS freeAppointments__type,
86            "free" AS allAppointments__type,
87            "bookable" AS status,
88            IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs
89        FROM (
90            SELECT s.year, s.month, s.day, s.time, s.scopeID,
91                IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired,
92                GREATEST(1, COUNT(DISTINCT h.slotID)) AS ancestorCount,
93                MIN(IFNULL(GREATEST(0, CAST(s.`public` AS SIGNED) -
94                    (SELECT CAST(COUNT(*) AS SIGNED) FROM slot_process p WHERE p.slotID = h.slotID)), 0)) AS public,
95                MIN(CAST(s.intern AS SIGNED) -
96                    (SELECT CAST(COUNT(*) AS SIGNED) FROM slot_process p WHERE p.slotID = h.slotID)) AS intern,
97                MIN(s.`public`) AS publicall,
98                MIN(s.intern) AS internall
99            FROM calendarscope c
100            INNER JOIN slot s ON c.scopeID = s.scopeID
101                AND c.year = s.year AND c.month = s.month AND s.status = "free"
102            LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID
103            LEFT JOIN slot_hiera h ON h.ancestorID = s.slotID
104                AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots)
105            WHERE NOT EXISTS (
106                SELECT 1 FROM closures cc
107                WHERE cc.StandortID = s.scopeID AND cc.year = s.year
108                    AND cc.month = s.month AND cc.day = s.day
109            )
110            ' . self::QUERY_ON_APPOINTMENT_INTERVAL . '
111            GROUP BY s.slotID
112            HAVING ancestorCount >= slotsRequired
113        ) AS dayaggregate
114        GROUP BY year, month, day
115        ORDER BY year, month, day;
116    ';
117
118    /**
119     * Citizen calendar-availability daylist only.
120     *
121     * Occupancy is pre-aggregated from slot_process and hierarchy slots must be free,
122     * so multi-service daylists do not invent bookable days.
123     *
124     * see also \BO\Zmsbackend\Process\Repository\ProcessStatusFree::QUERY_SELECT_PROCESSLIST_DAYS_AVAILABILITY
125     */
126    const string QUERY_DAYLIST_JOIN_AVAILABILITY = '
127        SELECT
128            year,
129            LPAD(month, 2, "0") AS month,
130            LPAD(day, 2, "0") AS day,
131            SUM(public) AS freeAppointments__public,
132            SUM(intern) AS freeAppointments__intern,
133            SUM(publicall) AS allAppointments__public,
134            SUM(internall) AS allAppointments__intern,
135            "sum" AS freeAppointments__type,
136            "free" AS allAppointments__type,
137            "bookable" AS status,
138            IFNULL(GROUP_CONCAT(DISTINCT CASE WHEN public > 0 THEN scopeID END SEPARATOR ","), "") AS scopeIDs
139        FROM
140        (
141            SELECT
142                year,
143                month,
144                day,
145                time,
146                slotsRequired,
147                COUNT(slotID) AS ancestorCount,
148                MIN(IF(public > confirmed, public - confirmed, 0)) AS public,
149                MIN(CAST(intern AS SIGNED) - confirmed) AS intern,
150                MIN(public) AS publicall,
151                MIN(intern) AS internall,
152                scopeID
153            FROM
154            (
155                SELECT
156                    IFNULL(occ.confirmed, 0) AS confirmed,
157                    IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots) AS slotsRequired,
158                    s.slotID,
159                    s.year,
160                    s.month,
161                    s.day,
162                    s.time,
163                    s.public,
164                    s.intern,
165                    s.scopeID
166                FROM
167                    calendarscope c
168                    INNER JOIN slot s
169                        ON c.scopeID = s.scopeID
170                        AND c.year = s.year
171                        AND c.month = s.month
172                        AND s.status = "free"
173                    LEFT JOIN oeffnungszeit a
174                        ON s.availabilityID = a.OeffnungszeitID
175                    LEFT JOIN slot_hiera h
176                        ON h.ancestorID = s.slotID
177                        AND h.ancestorLevel <= IF(a.multiple_slots_allowed, c.slotsRequired, :forceRequiredSlots)
178                    INNER JOIN slot s2
179                        ON h.slotID = s2.slotID
180                        AND s2.status = "free"
181                    LEFT JOIN (
182                        SELECT p.slotID, COUNT(*) AS confirmed
183                        FROM slot_process p
184                        INNER JOIN slot s_occ
185                            ON s_occ.slotID = p.slotID
186                        INNER JOIN calendarscope c_occ
187                            ON c_occ.scopeID = s_occ.scopeID
188                            AND c_occ.year = s_occ.year
189                            AND c_occ.month = s_occ.month
190                        GROUP BY p.slotID
191                    ) occ
192                        ON occ.slotID = h.slotID
193                    LEFT JOIN closures cc
194                        ON s.scopeID = cc.StandortID
195                        AND s.year = cc.year
196                        AND s.month = cc.month
197                        AND s.day = cc.day
198                WHERE cc.id IS NULL
199                ' . self::QUERY_ON_APPOINTMENT_INTERVAL . '
200            ) AS slotaggregate
201            GROUP BY slotID, scopeID
202            HAVING ancestorCount >= slotsRequired
203        ) AS dayaggregate
204        GROUP BY year, month, day
205        ORDER BY year, month, day;
206    ';
207}

Inherited from BO\Zmsbackend\Query\Base

96    public function __construct(
97        $queryType,
98        string $prefix = '',
99        string|false $name = false,
100        mixed $resolveLevel = null,
101        array $withEntities = []
102    ) {
103        $this->prefix = $prefix;
104        $this->name = $name;
105        $this->withEntities = $withEntities;
106        $this->setResolveLevel($resolveLevel);
107        $dialect = new MySQL();
108        if (self::SELECT === $queryType) {
109            $this->query = new Select($dialect);
110            $this->addSelect();
111        } elseif (self::INSERT === $queryType) {
112            $this->query = new Insert($dialect);
113            $this->addTable();
114        } elseif (self::UPDATE === $queryType) {
115            $this->query = new Update($dialect);
116            $this->addTableAlias();
117        } elseif (self::REPLACE === $queryType) {
118            $this->query = new INSERT($dialect);
119            $this->query->queryBaseStatement('REPLACE INTO');
120            $this->addTable();
121        } elseif (self::DELETE === $queryType) {
122            $this->query = new Delete($dialect);
123            $this->query->queryBaseStatement('DELETE ' . $this::getAlias() . ' FROM');
124            $this->addTableAlias();
125        } elseif ($queryType instanceof self) {
126            $this->query = $queryType->query;
127            // Share join-alias state with the parent query (intentional by-ref).
128            /** @psalm-suppress UnsupportedPropertyReferenceUsage */
129            $this->joinedAliasList =& $queryType->joinedAliasList;
130            $this->resolveLevel = $queryType->resolveLevel - 1;
131        } elseif ($queryType instanceof QueryBuilder) {
132            $this->query = $queryType;
133        } else {
134            throw new \InvalidArgumentException(
135                'Unsupported query type for ' . static::class . ': '
136                . (is_object($queryType) ? $queryType::class : gettype($queryType))
137            );
138        }
139        if ($this->query instanceof Select) {
140            $this->addRequiredJoins();
141        }
142    }
147    public function __toString()
148    {
149        if ($this->name) {
150            $name = $this->name . '_' . $this->prefix . (string) $this->resolveLevel;
151            if (!isset(static::$sqlCache[$name])) {
152                static::$sqlCache[$name] = $this->getSql();
153            }
154            return static::$sqlCache[$name];
155        }
156        if ($this->currentSqlString) {
157            $sql = $this->currentSqlString;
158        } else {
159            $sql = $this->getSql();
160        }
161        return $sql;
162    }
164    public function getName()
165    {
166        return $this->name ? $this->name : get_class($this);
167    }
174    protected function addSelect(): static
175    {
176        $table = $this::getTablename();
177        $alias = $this::getAlias();
178        $this->query->from($table, $alias);
179        return $this;
180    }
182    public function setDistinctSelect(): void
183    {
184        $this->query->queryBaseStatement('SELECT DISTINCT');
185    }
187    public function setResolveLevel(int|null $resolveLevel): static
188    {
189        if ($resolveLevel !== null) {
190            $this->resolveLevel = $resolveLevel;
191        }
192        return $this;
193    }
195    public function getResolveLevel()
196    {
197        if (null === $this->resolveLevel) {
198            throw new \Exception("Required setting for resolveReferenceLevel missing in " . get_class($this));
199        }
200        return $this->resolveLevel;
201    }
210    public static function getAlias()
211    {
212        $class = get_called_class();
213        $alias = constant($class . '::ALIAS');
214        if (null === $alias) {
215            $alias = lcfirst(preg_replace('#^.*\\\#', '', $class));
216        }
217        return $alias;
218    }
225    public static function getTablename()
226    {
227        $class = get_called_class();
228        $table = constant($class . '::TABLE');
229        return $table;
230    }
237    protected function addTable(): static
238    {
239        $table = $this::getTablename();
240        $alias = $this::getAlias();
241        $this->query->table($table, $alias);
242        return $this;
243    }
250    protected function addTableAlias(): static
251    {
252        $table = $this::getTablename();
253        $alias = $this::getAlias();
254        $this->query->table(self::expression($table . ' ' . $alias));
255        return $this;
256    }
264    protected function addRequiredJoins()
265    {
266    }
273    public function addResolvedReferences($depth): static
274    {
275        $this->setResolveLevel($depth);
276        if ($depth > 0) {
277            $queryList = $this->addJoin();
278            foreach ($queryList as $query) {
279                $query->setResolveLevel($depth);
280                $query->setWithEntities($this->withEntities);
281                $query->addResolvedReferences($depth - 1);
282                $query->addEntityMapping();
283            }
284            $this->joinedQueryList = $queryList;
285        } else {
286            $this->addReferenceMapping();
287        }
288        return $this;
289    }
294    public function setWithEntities(array $withEntities = []): void
295    {
296        $this->withEntities = $withEntities;
297    }
304    protected function addJoin()
305    {
306        return [];
307    }
309    protected function leftJoin(Alias $alias, string|Expression|null $left = null, string|null $operator = null, string|null $right = null): QueryBuilder
310    {
311        $aliasId = $alias->getAliasIdentifier();
312        if (!in_array($aliasId, $this->joinedAliasList)) {
313            $this->joinedAliasList[] = $aliasId;
314            $this->query->leftJoin($alias, $left, $operator, $right);
315        } else {
316            //throw new \Exception("Tried to add Alias ".$aliasId);
317        }
318        return $this->query;
319    }
321    protected function innerJoin(Alias $alias, string|null $left = null, string|null $operator = null, string|null $right = null): QueryBuilder
322    {
323        $aliasId = $alias->getAliasIdentifier();
324        if (!in_array($aliasId, $this->joinedAliasList)) {
325            $this->joinedAliasList[] = $aliasId;
326            $this->query->join($alias, $left, $operator, $right);
327        }
328        return $this->query;
329    }
337    public function getSql()
338    {
339        $this->currentSqlString = (string)$this->query;
340        return $this->currentSqlString;
341    }
348    public function getParameters()
349    {
350        return $this->query->params();
351    }
357    public function getReferenceMapping()
358    {
359        return [
360        ];
361    }
368    protected static function expression(string $string)
369    {
370        return new Expression($string);
371    }
379    public function addEntityMapping(mixed $type = null): static
380    {
381        // Concrete query classes provide getEntityMapping(); not all declare MappingInterface.
382        /** @psalm-suppress UndefinedMethod, TooManyArguments */
383        $entityMapping = $this->getPrefixedList($this->getEntityMapping($type));
384        $this->query->select($entityMapping);
385        return $this;
386    }
388    protected function getPrefixed(string $prefix): string
389    {
390        return $this->prefix . $prefix;
391    }
393    protected function getPrefixedList(array $unprefixedList): array
394    {
395        $prefixed = [];
396        foreach ($unprefixedList as $key => $value) {
397            $prefixed[$this->getPrefixed($key)] = $value;
398        }
399        return $prefixed;
400    }
405    protected function addReferenceMapping(): static
406    {
407        $referenceMapping = $this->getPrefixedList($this->getReferenceMapping());
408        $this->query->select($referenceMapping);
409        return $this;
410    }
412    public function addLimit(int $count, ?int $offset = null): static
413    {
414        $this->query->limit($count);
415        if ($offset) {
416            $this->query->offset($offset);
417        }
418        return $this;
419    }
424    public function addValues(array $values): static
425    {
426        $this->query->values($values);
427        return $this;
428    }
434    public function postProcess(array $data)
435    {
436        return $data;
437    }
445    public function postProcessJoins(array $data)
446    {
447        $data = $this->postProcess($data);
448        foreach ($this->joinedQueryList as $query) {
449            $data = $query->postProcess($data);
450        }
451        return $data;
452    }
454    public function shouldLoadEntity(string $name): bool
455    {
456        if (empty($this->withEntities)) {
457            return true;
458        }
459
460        return in_array($name, $this->withEntities);
461    }