Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
94.59%
70 / 74
40.00%
2 / 5
 CRAP
0.00%
0 / 1
ExchangeCapacityscope
94.59%
70 / 74
40.00%
2 / 5
 14.03
0.00%
0 / 1
 buildCapacityMetricsQuery
94.44%
17 / 18
0.00%
0 / 1
 3.00
 buildScopeSlotTimeQuery
100.00%
7 / 7
100.00%
1 / 1
 1
 slotFilterSql
90.91%
10 / 11
0.00%
0 / 1
 3.01
 scopeInClause
100.00%
6 / 6
100.00%
1 / 1
 2
 monthRangeSql
93.75%
30 / 32
0.00%
0 / 1
 5.01
1<?php
2
3namespace BO\Zmsbackend\Exchange\Repository;
4
5class ExchangeCapacityscope extends \BO\Zmsbackend\Query\Base
6{
7    /**
8     * @var String TABLE mysql table reference
9     */
10    const string TABLE = 'slot_process';
11
12    /**
13     * Scope picker list. Min/max dates are index lookups per scope, not a scan of every slot.
14     */
15    const string QUERY_CAPACITY_REPORT_SCOPE_SUBJECT_LIST = '
16      SELECT
17          scope.`StandortID` as subject,
18          (
19              SELECT CONCAT(s.year, "-", LPAD(s.month, 2, "0"), "-", LPAD(s.day, 2, "0"))
20              FROM slot AS s
21              WHERE s.scopeID = scope.`StandortID`
22              ORDER BY s.year, s.month, s.day
23              LIMIT 1
24          ) AS periodstart,
25          (
26              SELECT CONCAT(s.year, "-", LPAD(s.month, 2, "0"), "-", LPAD(s.day, 2, "0"))
27              FROM slot AS s
28              WHERE s.scopeID = scope.`StandortID`
29              ORDER BY s.year DESC, s.month DESC, s.day DESC
30              LIMIT 1
31          ) AS periodend,
32          CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
33      FROM ' . \BO\Zmsbackend\Query\Scope::TABLE . ' AS scope
34      HAVING periodstart IS NOT NULL
35      ORDER BY description ASC
36    ';
37
38    /**
39     * Planned totals from slot, booked totals from appointments on those slots.
40     * All scopes are one statement. Bookings are reached from those slots, not from every row in slot_process.
41     *
42     * @param array<int, int> $scopeIds
43     * @param array<string, int> $parameters
44     */
45    public static function buildCapacityMetricsQuery(
46        array $scopeIds,
47        ?\DateTimeInterface $dateStart,
48        ?\DateTimeInterface $dateEnd,
49        string $period,
50        array &$parameters
51    ): string {
52        $parameters = [];
53        $plannedFilter = self::slotFilterSql('s', 'o', $scopeIds, $dateStart, $dateEnd, $parameters);
54        $bookedFilter = self::slotFilterSql('s', 'i', $scopeIds, $dateStart, $dateEnd, $parameters);
55        $hourly = $period === 'hour';
56        $dateExpression = $hourly
57            ? 'CONCAT(s.year, "-", LPAD(s.month, 2, "0"), "-", LPAD(s.day, 2, "0"), " ", '
58                . 'LPAD(HOUR(s.`time`), 2, "0"), ":00")'
59            : 'CONCAT(s.year, "-", LPAD(s.month, 2, "0"), "-", LPAD(s.day, 2, "0"))';
60        $groupBy = $hourly
61            ? 's.scopeID, s.year, s.month, s.day, HOUR(s.`time`)'
62            : 's.scopeID, s.year, s.month, s.day';
63
64        return '
65        SELECT
66            planned.subjectid,
67            planned.date,
68            COALESCE(booked.slotcount, 0),
69            planned.plannedcount,
70            COALESCE(booked.bookedminutes, 0),
71            planned.plannedminutes,
72            COALESCE(booked.slotcount_public, 0),
73            planned.plannedpublic,
74            COALESCE(booked.bookedminutes_public, 0),
75            planned.plannedminutes_public
76        FROM (
77            SELECT
78                s.scopeID as subjectid,
79                ' . $dateExpression . ' as date,
80                SUM(s.intern) as plannedcount,
81                SUM(COALESCE(s.intern, 0) * COALESCE(s.slotTimeInMinutes, 0)) as plannedminutes,
82                SUM(s.`public`) as plannedpublic,
83                SUM(COALESCE(s.`public`, 0) * COALESCE(s.slotTimeInMinutes, 0)) as plannedminutes_public
84            FROM slot AS s
85            WHERE ' . $plannedFilter . '
86            GROUP BY ' . $groupBy . '
87        ) AS planned
88        LEFT JOIN (
89            SELECT
90                s.scopeID as subjectid,
91                ' . $dateExpression . ' as date,
92                COUNT(*) as slotcount,
93                SUM(COALESCE(s.slotTimeInMinutes, 0)) as bookedminutes,
94                SUM(CASE WHEN ac.accesslevel = "public" THEN 1 ELSE 0 END) as slotcount_public,
95                SUM(CASE WHEN ac.accesslevel = "public" THEN COALESCE(s.slotTimeInMinutes, 0) ELSE 0 END)
96                    as bookedminutes_public
97            FROM slot AS s
98            STRAIGHT_JOIN slot_process AS sp ON sp.slotID = s.slotID
99            LEFT JOIN buerger b ON sp.processID = b.BuergerID
100            LEFT JOIN apiclient ac ON b.apiClientID = ac.apiClientID
101            WHERE ' . $bookedFilter . '
102            GROUP BY ' . $groupBy . '
103        ) AS booked ON booked.subjectid = planned.subjectid AND booked.date = planned.date
104        ORDER BY planned.date ASC, FIELD(planned.subjectid, ' . implode(', ', array_map('intval', $scopeIds)) . ')
105        ';
106    }
107
108    /**
109     * Distinct slot lengths in the same range, for the duration hint.
110     *
111     * @param array<int, int> $scopeIds
112     * @param array<string, int> $parameters
113     */
114    public static function buildScopeSlotTimeQuery(
115        array $scopeIds,
116        ?\DateTimeInterface $dateStart,
117        ?\DateTimeInterface $dateEnd,
118        array &$parameters
119    ): string {
120        $parameters = [];
121        $filter = self::slotFilterSql('s', 't', $scopeIds, $dateStart, $dateEnd, $parameters);
122
123        return '
124            SELECT
125                s.scopeID as subjectid,
126                s.slotTimeInMinutes as slotminutes,
127                TRIM(CONCAT(IFNULL(scopeprovider.name, ""), " ", IFNULL(scope.standortkuerzel, ""))) as scopename
128            FROM slot AS s
129            INNER JOIN ' . \BO\Zmsbackend\Query\Scope::TABLE . ' AS scope
130                ON scope.StandortID = s.scopeID
131            LEFT JOIN ' . \BO\Zmsbackend\Provider\Repository\Provider::TABLE . ' AS scopeprovider
132                ON scope.InfoDienstleisterID = scopeprovider.id
133                AND scope.source = scopeprovider.source
134            WHERE ' . $filter . '
135            GROUP BY s.scopeID, s.slotTimeInMinutes, scopename
136        ';
137    }
138
139    /**
140     * @param array<int, int> $scopeIds
141     * @param array<string, int> $parameters
142     */
143    private static function slotFilterSql(
144        string $alias,
145        string $parameterPrefix,
146        array $scopeIds,
147        ?\DateTimeInterface $dateStart,
148        ?\DateTimeInterface $dateEnd,
149        array &$parameters
150    ): string {
151        $filter = self::scopeInClause($alias, $parameterPrefix, $scopeIds, $parameters)
152            . ' AND ' . $alias . '.status = "free"';
153
154        if ($dateStart === null || $dateEnd === null) {
155            return $filter;
156        }
157
158        return $filter . ' AND (' . self::monthRangeSql(
159            $alias,
160            $parameterPrefix,
161            $dateStart,
162            $dateEnd,
163            $parameters
164        ) . ')';
165    }
166
167    /**
168     * @param array<int, int> $scopeIds
169     * @param array<string, int> $parameters
170     */
171    private static function scopeInClause(
172        string $alias,
173        string $parameterPrefix,
174        array $scopeIds,
175        array &$parameters
176    ): string {
177        $placeholders = [];
178        foreach (array_values($scopeIds) as $index => $scopeId) {
179            $name = $parameterPrefix . 'scope' . $index;
180            $parameters[$name] = $scopeId;
181            $placeholders[] = ':' . $name;
182        }
183
184        return $alias . '.scopeID IN (' . implode(', ', $placeholders) . ')';
185    }
186
187    /**
188     * @param array<string, int> $parameters
189     */
190    private static function monthRangeSql(
191        string $alias,
192        string $parameterPrefix,
193        \DateTimeInterface $dateStart,
194        \DateTimeInterface $dateEnd,
195        array &$parameters
196    ): string {
197        $start = \DateTimeImmutable::createFromInterface($dateStart)->setTime(0, 0);
198        $end = \DateTimeImmutable::createFromInterface($dateEnd)->setTime(0, 0);
199        if ($end < $start) {
200            return '0';
201        }
202
203        $clauses = [];
204        $cursor = $start->modify('first day of this month');
205        $lastMonth = $end->modify('first day of this month');
206        $index = 0;
207        while ($cursor <= $lastMonth) {
208            $dayStart = $cursor->format('Y-m') === $start->format('Y-m') ? (int) $start->format('j') : 1;
209            $dayEnd = $cursor->format('Y-m') === $end->format('Y-m')
210                ? (int) $end->format('j')
211                : (int) $cursor->format('t');
212            $yearKey = $parameterPrefix . 'y' . $index;
213            $monthKey = $parameterPrefix . 'm' . $index;
214            $dayStartKey = $parameterPrefix . 'ds' . $index;
215            $dayEndKey = $parameterPrefix . 'de' . $index;
216            $parameters[$yearKey] = (int) $cursor->format('Y');
217            $parameters[$monthKey] = (int) $cursor->format('n');
218            $parameters[$dayStartKey] = $dayStart;
219            $parameters[$dayEndKey] = $dayEnd;
220            $clauses[] = sprintf(
221                '(%1$s.year = :%2$s AND %1$s.month = :%3$s AND %1$s.day BETWEEN :%4$s AND :%5$s)',
222                $alias,
223                $yearKey,
224                $monthKey,
225                $dayStartKey,
226                $dayEndKey
227            );
228            $cursor = $cursor->modify('+1 month');
229            $index++;
230        }
231
232        return implode(' OR ', $clauses);
233    }
234}