Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
86.96%
480 / 552
29.79%
14 / 47
 CRAP
0.00%
0 / 1
ReportCapacityService
86.96%
480 / 552
29.79%
14 / 47
 319.54
0.00%
0 / 1
 getExchangeCapacityData
85.71%
6 / 7
0.00%
0 / 1
 4.05
 getScopeDateBoundsByScopeId
72.73%
16 / 22
0.00%
0 / 1
 10.64
 getSelectedScopeSlotTimes
80.00%
20 / 25
0.00%
0 / 1
 9.65
 formatScopeSlotTimeHint
88.89%
32 / 36
0.00%
0 / 1
 10.14
 formatGroupedScopeSlotTimeHint
88.89%
16 / 18
0.00%
0 / 1
 6.05
 resolveScopeDisplayName
87.50%
7 / 8
0.00%
0 / 1
 4.03
 resolveScopeSlotTimeMinutes
66.67%
4 / 6
0.00%
0 / 1
 7.33
 getCapacityPeriod
60.00%
3 / 5
0.00%
0 / 1
 2.26
 getExchangeCapacityForDateRange
71.43%
5 / 7
0.00%
0 / 1
 4.37
 getExchangeCapacityForPeriod
80.00%
12 / 15
0.00%
0 / 1
 6.29
 buildCapacityDisplayExchanges
100.00%
17 / 17
100.00%
1 / 1
 2
 selectDownloadExchange
100.00%
15 / 15
100.00%
1 / 1
 8
 withDailyAggregation
100.00%
4 / 4
100.00%
1 / 1
 1
 buildSparseChartExchange
100.00%
1 / 1
100.00%
1 / 1
 1
 buildChartExchange
100.00%
9 / 9
100.00%
1 / 1
 1
 applyChartVisualizationSettings
90.91%
10 / 11
0.00%
0 / 1
 4.01
 resolveChartLabelIntervalHours
91.67%
11 / 12
0.00%
0 / 1
 6.02
 shouldFetchHourlyFromApi
83.33%
5 / 6
0.00%
0 / 1
 4.07
 resolveRangeDurationHours
100.00%
6 / 6
100.00%
1 / 1
 2
 fetchAggregatedReport
76.92%
10 / 13
0.00%
0 / 1
 4.20
 buildCapacityFetchParams
100.00%
9 / 9
100.00%
1 / 1
 5
 resolveCapacityFetchUrlPeriod
100.00%
5 / 5
100.00%
1 / 1
 3
 normalizeFetchedCapacityExchange
87.50%
7 / 8
0.00%
0 / 1
 4.03
 aggregateRowsByDate
86.67%
13 / 15
0.00%
0 / 1
 6.09
 fillMissingTimeline
88.64%
39 / 44
0.00%
0 / 1
 12.21
 exchangeDataLooksHourly
71.43%
5 / 7
0.00%
0 / 1
 5.58
 rowDateValue
66.67%
2 / 3
0.00%
0 / 1
 2.15
 rowNumericValue
66.67%
2 / 3
0.00%
0 / 1
 2.15
 emptyCapacityRow
100.00%
1 / 1
100.00%
1 / 1
 1
 exchangeSupportsCapacityChannel
75.00%
3 / 4
0.00%
0 / 1
 3.14
 normalizeDataRow
100.00%
13 / 13
100.00%
1 / 1
 1
 normalizeTimelineKey
75.00%
6 / 8
0.00%
0 / 1
 4.25
 resolveTimelineBounds
90.91%
20 / 22
0.00%
0 / 1
 7.04
 filterRowsByBounds
88.89%
8 / 9
0.00%
0 / 1
 3.01
 finalizeExchange
44.44%
4 / 9
0.00%
0 / 1
 6.74
 dayFromString
100.00%
2 / 2
100.00%
1 / 1
 1
 enrichPeriodList
86.67%
26 / 30
0.00%
0 / 1
 13.40
 buildDownloadFilename
83.33%
5 / 6
0.00%
0 / 1
 3.04
 buildDownloadDateRangePart
71.43%
5 / 7
0.00%
0 / 1
 6.84
 buildDownloadExchange
93.88%
46 / 49
0.00%
0 / 1
 8.01
 exchangeSupportsMinutes
75.00%
3 / 4
0.00%
0 / 1
 3.14
 buildCapacityMetricLabel
100.00%
3 / 3
100.00%
1 / 1
 3
 resolveCapacityChannelLabel
80.00%
4 / 5
0.00%
0 / 1
 3.07
 resolveChannelMetric
91.67%
11 / 12
0.00%
0 / 1
 8.04
 formatDownloadDate
100.00%
3 / 3
100.00%
1 / 1
 3
 formatUtilizationPercent
100.00%
4 / 4
100.00%
1 / 1
 2
 prepareDownloadArgs
91.67%
22 / 24
0.00%
0 / 1
 8.04
1<?php
2
3/**
4 * @package Zmsstatistic
5 * @copyright BerlinOnline Stadtportal GmbH & Co. KG
6 **/
7
8namespace BO\Zmsstatistic\Service;
9
10use BO\Zmsentities\Day;
11use BO\Zmsentities\Exchange;
12use BO\Zmsstatistic\Helper\ReportHelper;
13use DateTimeImmutable;
14
15/**
16 * @SuppressWarnings(TooManyMethods)
17 */
18class ReportCapacityService
19{
20    /** Fetch hour-level warehouse data up to this range length (chart buckets are derived in PHP). */
21    private const int MAX_HOURLY_FETCH_HOURS = 336;
22
23    /** Above this count, slot-time hints group by duration instead of listing scope names. */
24    private const int SLOT_TIME_HINT_MAX_NAMED_SCOPES = 4;
25
26    public function getExchangeCapacityData(string $scopeId, ?array $dateRange, array $args): mixed
27    {
28        if ($scopeId === '') {
29            return null;
30        }
31
32        if (ReportHelper::hasValues($dateRange)) {
33            return $this->getExchangeCapacityForDateRange($scopeId, $dateRange);
34        }
35
36        if (isset($args['period'])) {
37            return $this->getExchangeCapacityForPeriod($scopeId, $args['period']);
38        }
39
40        return null;
41    }
42
43    /** Available date range per scope from warehouse subject list (periodstart / periodend). */
44    public function getScopeDateBoundsByScopeId(): array
45    {
46        try {
47            $result = \App::http()->readGetResult('/warehouse/capacityscope/');
48
49            $subjectList = $result->getEntity();
50
51            if (!$subjectList instanceof Exchange || empty($subjectList->data)) {
52                return [];
53            }
54
55            $dateBoundsByScopeId = [];
56            foreach ($subjectList->data as $row) {
57                $scopeId = (string) ($row[0] ?? '');
58                $periodStart = (string) ($row[1] ?? '');
59                $periodEnd = (string) ($row[2] ?? '');
60
61                if ($scopeId === '' || $periodStart === '' || $periodEnd === '') {
62                    continue;
63                }
64
65                if (!isset($dateBoundsByScopeId[$scopeId])) {
66                    $dateBoundsByScopeId[$scopeId] = [
67                        'min' => $periodStart,
68                        'max' => $periodEnd,
69                    ];
70                    continue;
71                }
72
73                $dateBoundsByScopeId[$scopeId]['min'] = min($dateBoundsByScopeId[$scopeId]['min'], $periodStart);
74                $dateBoundsByScopeId[$scopeId]['max'] = max($dateBoundsByScopeId[$scopeId]['max'], $periodEnd);
75            }
76
77            return $dateBoundsByScopeId;
78        } catch (\Throwable $exception) {
79            return [];
80        }
81    }
82
83    public function getSelectedScopeSlotTimes(array $scopeIds): array
84    {
85        if ($scopeIds === []) {
86            return [];
87        }
88
89        try {
90            $result = \App::http()->readGetResult('/scope/');
91
92            $scopeList = $result->getData();
93            if (!is_array($scopeList) && !($scopeList instanceof \Traversable)) {
94                return [];
95            }
96
97            $scopeById = [];
98            foreach ($scopeList as $scope) {
99                $id = (string) ($scope->id ?? '');
100                if ($id !== '') {
101                    $scopeById[$id] = $scope;
102                }
103            }
104
105            $scopeSlotTimeEntries = [];
106            foreach ($scopeIds as $scopeId) {
107                $id = (string) $scopeId;
108                if (!isset($scopeById[$id])) {
109                    continue;
110                }
111
112                $scope = $scopeById[$id];
113                $scopeSlotTimeEntries[] = [
114                    'id' => $id,
115                    'name' => $this->resolveScopeDisplayName($scope, $id),
116                    'slotTimeInMinutes' => $this->resolveScopeSlotTimeMinutes($scope),
117                ];
118            }
119
120            return $scopeSlotTimeEntries;
121        } catch (\Throwable $exception) {
122            return [];
123        }
124    }
125
126    public function formatScopeSlotTimeHint(array $scopeSlotTimes): ?string
127    {
128        if ($scopeSlotTimes === []) {
129            return null;
130        }
131
132        $slotTimeMinutesList = array_values(array_filter(
133            array_map(
134                static fn (array $item): ?int => $item['slotTimeInMinutes'] ?? null,
135                $scopeSlotTimes
136            ),
137            static fn (?int $minutes): bool => $minutes !== null
138        ));
139
140        if ($slotTimeMinutesList === []) {
141            return null;
142        }
143
144        if (count($scopeSlotTimes) === 1) {
145            return sprintf(
146                'Zeitschlitzdauer laut Öffnungszeit: %d Min.',
147                $slotTimeMinutesList[0]
148            );
149        }
150
151        $uniqueSlotTimeMinutes = array_values(array_unique($slotTimeMinutesList));
152        if (count($uniqueSlotTimeMinutes) === 1 && count($slotTimeMinutesList) === count($scopeSlotTimes)) {
153            return sprintf(
154                'Zeitschlitzdauer laut Öffnungszeit: %d Min. (alle ausgewählten Standorte)',
155                $uniqueSlotTimeMinutes[0]
156            );
157        }
158
159        if (count($scopeSlotTimes) > self::SLOT_TIME_HINT_MAX_NAMED_SCOPES) {
160            return $this->formatGroupedScopeSlotTimeHint($scopeSlotTimes);
161        }
162
163        $hintParts = [];
164        foreach ($scopeSlotTimes as $item) {
165            if (($item['slotTimeInMinutes'] ?? null) === null) {
166                continue;
167            }
168
169            $hintParts[] = sprintf(
170                '%s: %d Min.',
171                $item['name'],
172                $item['slotTimeInMinutes']
173            );
174        }
175
176        return $hintParts === []
177            ? null
178            : 'Zeitschlitzdauer laut Öffnungszeit: ' . implode('; ', $hintParts);
179    }
180
181    private function formatGroupedScopeSlotTimeHint(array $scopeSlotTimes): ?string
182    {
183        $scopeCountBySlotMinutes = [];
184        foreach ($scopeSlotTimes as $item) {
185            $minutes = $item['slotTimeInMinutes'] ?? null;
186            if ($minutes === null) {
187                continue;
188            }
189
190            $scopeCountBySlotMinutes[$minutes] = ($scopeCountBySlotMinutes[$minutes] ?? 0) + 1;
191        }
192
193        if ($scopeCountBySlotMinutes === []) {
194            return null;
195        }
196
197        ksort($scopeCountBySlotMinutes, SORT_NUMERIC);
198
199        $hintParts = [];
200        foreach ($scopeCountBySlotMinutes as $minutes => $scopeCount) {
201            $hintParts[] = sprintf(
202                '%d Min. (%d %s)',
203                $minutes,
204                $scopeCount,
205                $scopeCount === 1 ? 'Standort' : 'Standorte'
206            );
207        }
208
209        return 'Zeitschlitzdauer laut Öffnungszeit: ' . implode(', ', $hintParts);
210    }
211
212    private function resolveScopeDisplayName(mixed $scope, string $id): string
213    {
214        if (!is_object($scope)) {
215            return 'Standort ' . $id;
216        }
217
218        $contactName = '';
219        if (isset($scope->contact)) {
220            $contactName = (string) ($scope->contact->name ?? '');
221        }
222
223        $shortName = (string) ($scope->shortName ?? '');
224        $name = trim($contactName . ' ' . $shortName);
225
226        return $name !== '' ? $name : 'Standort ' . $id;
227    }
228
229    private function resolveScopeSlotTimeMinutes(mixed $scope): ?int
230    {
231        if (!is_object($scope) || !isset($scope->provider) || !is_object($scope->provider)) {
232            return null;
233        }
234
235        if (!method_exists($scope->provider, 'getSlotTimeInMinutes')) {
236            return null;
237        }
238
239        $slotTime = $scope->provider->getSlotTimeInMinutes();
240
241        return $slotTime !== null ? (int) $slotTime : null;
242    }
243
244    /**
245     * Period list for navigation; derived from capacityscope report dates when API only returns "_".
246     */
247    public function getCapacityPeriod(string $scopeId): mixed
248    {
249        try {
250            $result = \App::http()->readGetResult('/warehouse/capacityscope/' . $scopeId . '/');
251
252            $periodList = $result->getEntity();
253
254            return $this->enrichPeriodList($periodList, $scopeId);
255        } catch (\Throwable $exception) {
256            return null;
257        }
258    }
259
260    public function getExchangeCapacityForDateRange(string $scopeId, array $dateRange): mixed
261    {
262        if (!isset($dateRange['from'], $dateRange['to'])) {
263            return null;
264        }
265
266        $exchange = $this->fetchAggregatedReport($scopeId, $dateRange, null);
267        if (!$exchange || empty($exchange->data)) {
268            return null;
269        }
270
271        $exchange->data = $this->filterRowsByBounds($exchange->data, $dateRange);
272
273        return $this->finalizeExchange($exchange, $dateRange['from'], $dateRange['to']);
274    }
275
276    public function getExchangeCapacityForPeriod(string $scopeId, string $period): mixed
277    {
278        $exchange = $this->fetchAggregatedReport($scopeId, null, $period);
279        if (!$exchange || empty($exchange->data)) {
280            return null;
281        }
282
283        $timelineBounds = $this->resolveTimelineBounds(null, $period);
284        if (ReportHelper::hasValues($timelineBounds)) {
285            $exchange->data = $this->filterRowsByBounds($exchange->data, $timelineBounds);
286        }
287
288        if (empty($exchange->data)) {
289            return null;
290        }
291
292        if (ReportHelper::hasValues($timelineBounds)) {
293            return $this->finalizeExchange(
294                $exchange,
295                $timelineBounds['from'],
296                $timelineBounds['to']
297            );
298        }
299
300        return $this->finalizeExchange($exchange);
301    }
302
303    /**
304     * Daily totals for the default table/chart, plus hourly payloads for the optional Stundenansicht.
305     */
306    public function buildCapacityDisplayExchanges(
307        Exchange $exchange,
308        ?array $dateRange,
309        ?string $period
310    ): array {
311        $hourlyTable = null;
312        $hourlyChartSparse = null;
313        $hourlyChartFull = null;
314        $dailyTable = $exchange;
315
316        if ($exchange->period === 'hour') {
317            $hourlyTable = clone $exchange;
318            $hourlyChartSparse = $this->buildSparseChartExchange($hourlyTable, $dateRange, $period);
319            $hourlyChartFull = $this->buildChartExchange($hourlyTable, $dateRange, $period);
320            $dailyTable = $this->withDailyAggregation($exchange);
321        }
322
323        return [
324            'dailyTable' => $dailyTable,
325            'dailyChartSparse' => $this->buildSparseChartExchange($dailyTable, $dateRange, $period),
326            'dailyChartFull' => $this->buildChartExchange($dailyTable, $dateRange, $period),
327            'hourlyTable' => $hourlyTable,
328            'hourlyChartSparse' => $hourlyChartSparse,
329            'hourlyChartFull' => $hourlyChartFull,
330        ];
331    }
332
333    /**
334     * Excel rows for the view on screen: daily or hourly, sparse API rows or the full timeline.
335     */
336    public function selectDownloadExchange(
337        array $displayExchanges,
338        ?string $granularity,
339        ?string $timeline
340    ): ?Exchange {
341        $useFullTimeline = $timeline === 'full';
342
343        if ($granularity === 'hour') {
344            $hourlyFull = $displayExchanges['hourlyChartFull'] ?? null;
345            $hourlySparse = $displayExchanges['hourlyTable'] ?? null;
346            $hourly = $useFullTimeline && $hourlyFull instanceof Exchange
347                ? $hourlyFull
348                : $hourlySparse;
349            if ($hourly instanceof Exchange) {
350                return $hourly;
351            }
352        }
353
354        $dailyFull = $displayExchanges['dailyChartFull'] ?? null;
355        $dailySparse = $displayExchanges['dailyTable'] ?? null;
356        $daily = $useFullTimeline && $dailyFull instanceof Exchange
357            ? $dailyFull
358            : $dailySparse;
359
360        return $daily instanceof Exchange ? $daily : null;
361    }
362
363    public function withDailyAggregation(Exchange $exchange): Exchange
364    {
365        $daily = clone $exchange;
366        $daily->data = $this->aggregateRowsByDate($exchange->data, false);
367        $daily->period = 'day';
368
369        return $daily;
370    }
371
372    /**
373     * Sparse API rows for the chart (same as legacy warehouse reports).
374     */
375    public function buildSparseChartExchange(Exchange $exchange, ?array $dateRange, ?string $period): Exchange
376    {
377        return $this->applyChartVisualizationSettings(clone $exchange, $dateRange, $period);
378    }
379
380    /**
381     * Full timeline for the chart (zeros for closed hours/days). Table uses sparse API data.
382     */
383    public function buildChartExchange(Exchange $exchange, ?array $dateRange, ?string $period): Exchange
384    {
385        $chartExchange = clone $exchange;
386        $useHourlyTimeline = $exchange->period === 'hour';
387
388        $chartExchange->data = $this->fillMissingTimeline(
389            $chartExchange->data,
390            $dateRange,
391            $period,
392            $useHourlyTimeline
393        );
394
395        return $this->applyChartVisualizationSettings($chartExchange, $dateRange, $period);
396    }
397
398    private function applyChartVisualizationSettings(
399        Exchange $chartExchange,
400        ?array $dateRange,
401        ?string $period
402    ): Exchange {
403        $visualization = $chartExchange['visualization'] ?? [];
404        if (!is_array($visualization)) {
405            $visualization = [];
406        }
407        $visualization['labelIntervalHours'] = $chartExchange->period === 'hour'
408            ? $this->resolveChartLabelIntervalHours($dateRange, $period)
409            : null;
410        $visualization['allowSparseTimeline'] = true;
411        if (!isset($visualization['allowCapacityChannel'])) {
412            $visualization['allowCapacityChannel'] = $this->exchangeSupportsCapacityChannel($chartExchange);
413        }
414        $chartExchange['visualization'] = $visualization;
415
416        return $chartExchange;
417    }
418
419    /**
420     * X-axis tick label spacing in hours, or null for daily labels.
421     */
422    public function resolveChartLabelIntervalHours(?array $dateRange, ?string $period): ?int
423    {
424        $rangeDurationHours = $this->resolveRangeDurationHours($dateRange, $period);
425        if ($rangeDurationHours === null) {
426            return null;
427        }
428
429        if ($rangeDurationHours <= 24) {
430            return 1;
431        }
432
433        if ($rangeDurationHours <= 48) {
434            return 2;
435        }
436
437        if ($rangeDurationHours <= 168) {
438            return 6;
439        }
440
441        if ($rangeDurationHours <= self::MAX_HOURLY_FETCH_HOURS) {
442            return 12;
443        }
444
445        return null;
446    }
447
448    public function shouldFetchHourlyFromApi(?array $dateRange, ?string $period): bool
449    {
450        $rangeDurationHours = $this->resolveRangeDurationHours($dateRange, $period);
451        if ($rangeDurationHours !== null) {
452            return $rangeDurationHours <= self::MAX_HOURLY_FETCH_HOURS;
453        }
454
455        if (ReportHelper::hasNamedPeriod($period) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $period)) {
456            return true;
457        }
458
459        return false;
460    }
461
462    /** Length of selected range in hours (inclusive end day). */
463    public function resolveRangeDurationHours(?array $dateRange, ?string $period): ?float
464    {
465        $bounds = $this->resolveTimelineBounds($dateRange, $period);
466        if (!ReportHelper::hasValues($bounds)) {
467            return null;
468        }
469
470        $rangeStart = new DateTimeImmutable($bounds['from'] . ' 00:00:00');
471        $rangeEnd = new DateTimeImmutable($bounds['to'] . ' 23:59:59');
472
473        return ($rangeEnd->getTimestamp() - $rangeStart->getTimestamp()) / 3600;
474    }
475
476    private function fetchAggregatedReport(string $scopeId, ?array $dateRange, ?string $period): ?Exchange
477    {
478        try {
479            $useHourlyGrouping = $this->shouldFetchHourlyFromApi($dateRange, $period);
480            $warehouseFetchParams = $this->buildCapacityFetchParams($dateRange, $period, $useHourlyGrouping);
481            $warehouseUrlPeriodSegment = $this->resolveCapacityFetchUrlPeriod($dateRange, $period);
482
483            $result = \App::http()->readGetResult(
484                '/warehouse/capacityscope/' . $scopeId . '/' . $warehouseUrlPeriodSegment . '/',
485                $warehouseFetchParams === [] ? null : $warehouseFetchParams
486            );
487
488            $exchange = $result->getEntity();
489            if (!$exchange instanceof Exchange) {
490                return null;
491            }
492
493            return $this->normalizeFetchedCapacityExchange($exchange, $useHourlyGrouping);
494        } catch (\Throwable $exception) {
495            return null;
496        }
497    }
498
499    private function buildCapacityFetchParams(?array $dateRange, ?string $period, bool $useHourlyGrouping): array
500    {
501        $warehouseFetchParams = [];
502
503        if (ReportHelper::hasValues($dateRange)) {
504            $warehouseFetchParams['fromDate'] = $dateRange['from'];
505            $warehouseFetchParams['toDate'] = $dateRange['to'];
506        }
507
508        if ($useHourlyGrouping) {
509            $warehouseFetchParams['groupby'] = 'hour';
510        } elseif (ReportHelper::hasValues($dateRange) || ReportHelper::hasNamedPeriod($period)) {
511            $warehouseFetchParams['groupby'] = 'day';
512        }
513
514        return $warehouseFetchParams;
515    }
516
517    private function resolveCapacityFetchUrlPeriod(?array $dateRange, ?string $period): string
518    {
519        if (ReportHelper::hasNamedPeriod($period)) {
520            return $period;
521        }
522
523        if (ReportHelper::hasValues($dateRange)) {
524            return $dateRange['from'];
525        }
526
527        return '_';
528    }
529
530    private function normalizeFetchedCapacityExchange(Exchange $exchange, bool $useHourlyGrouping): Exchange
531    {
532        $exchange->data = $this->aggregateRowsByDate($exchange->data, $useHourlyGrouping);
533
534        if (!$useHourlyGrouping) {
535            if ($this->exchangeDataLooksHourly($exchange->data)) {
536                $exchange->data = $this->aggregateRowsByDate($exchange->data, false);
537            }
538            $exchange->period = 'day';
539        } elseif ($exchange->period === 'hour') {
540            $exchange->period = 'hour';
541        }
542
543        return $exchange;
544    }
545
546    /** Sum booked/planned counts per date (or hour) across multiple scopes. */
547    public function aggregateRowsByDate(array $rows, bool $useHourlyKeys): array
548    {
549        $rowsByTimelineKey = [];
550
551        foreach ($rows as $row) {
552            if (!is_array($row)) {
553                continue;
554            }
555
556            $normalizedRow = $this->normalizeDataRow($row, $useHourlyKeys);
557            $timelineKey = $normalizedRow[1];
558            if ($timelineKey === '') {
559                continue;
560            }
561
562            if (!isset($rowsByTimelineKey[$timelineKey])) {
563                $rowsByTimelineKey[$timelineKey] = $normalizedRow;
564                continue;
565            }
566
567            for ($metricColumnIndex = 2; $metricColumnIndex <= 9; $metricColumnIndex++) {
568                $rowsByTimelineKey[$timelineKey][$metricColumnIndex] += $normalizedRow[$metricColumnIndex];
569            }
570        }
571
572        ksort($rowsByTimelineKey);
573
574        return array_values($rowsByTimelineKey);
575    }
576
577    /**
578     * Insert zero rows for each hour or day in the selected range so the chart shows
579     * closed periods and gaps between days (not only timestamps with slot data).
580     */
581    private function fillMissingTimeline(
582        array $rows,
583        ?array $dateRange,
584        ?string $period,
585        bool $useHourlyTimeline
586    ): array {
587        $timelineBounds = $this->resolveTimelineBounds($dateRange, $period);
588        if (!ReportHelper::hasValues($timelineBounds)) {
589            return $rows;
590        }
591
592        $rowsByTimelineKey = [];
593        $defaultSubjectId = '';
594
595        foreach ($rows as $row) {
596            if (!is_array($row)) {
597                continue;
598            }
599
600            $normalizedRow = $this->normalizeDataRow($row, $useHourlyTimeline);
601            $timelineKey = $normalizedRow[1];
602            if ($timelineKey === '') {
603                continue;
604            }
605
606            if (!isset($rowsByTimelineKey[$timelineKey])) {
607                $rowsByTimelineKey[$timelineKey] = $normalizedRow;
608                if ($defaultSubjectId === '' && $normalizedRow[0] !== '') {
609                    $defaultSubjectId = $normalizedRow[0];
610                }
611                continue;
612            }
613
614            for ($metricColumnIndex = 2; $metricColumnIndex <= 9; $metricColumnIndex++) {
615                $rowsByTimelineKey[$timelineKey][$metricColumnIndex] += $normalizedRow[$metricColumnIndex];
616            }
617        }
618
619        $rangeStartDate = $timelineBounds['from'];
620        $rangeEndDate = $timelineBounds['to'];
621        $completeTimelineRows = [];
622
623        if ($useHourlyTimeline) {
624            $timelineStart = new DateTimeImmutable($rangeStartDate . ' 00:00:00');
625            $timelineEnd = new DateTimeImmutable($rangeEndDate . ' 23:00:00');
626            $timelineCursor = $timelineStart;
627
628            while ($timelineCursor <= $timelineEnd) {
629                $timelineKey = $this->normalizeTimelineKey(
630                    $timelineCursor->format('Y-m-d') . ' ' . $timelineCursor->format('H') . ':00',
631                    true
632                );
633                $completeTimelineRows[] = $rowsByTimelineKey[$timelineKey]
634                    ?? $this->emptyCapacityRow($defaultSubjectId, $timelineKey);
635                $timelineCursor = $timelineCursor->modify('+1 hour');
636            }
637
638            return $completeTimelineRows;
639        }
640
641        $timelineStart = new DateTimeImmutable($rangeStartDate);
642        $timelineEnd = new DateTimeImmutable($rangeEndDate);
643        $timelineCursor = $timelineStart;
644
645        while ($timelineCursor <= $timelineEnd) {
646            $timelineKey = $timelineCursor->format('Y-m-d');
647            $completeTimelineRows[] = $rowsByTimelineKey[$timelineKey]
648                ?? $this->emptyCapacityRow($defaultSubjectId, $timelineKey);
649            $timelineCursor = $timelineCursor->modify('+1 day');
650        }
651
652        return $completeTimelineRows;
653    }
654
655    private function exchangeDataLooksHourly(array $rows): bool
656    {
657        foreach ($rows as $row) {
658            if (!is_array($row)) {
659                continue;
660            }
661
662            $dateValue = $this->rowDateValue($row);
663            if ($dateValue !== '' && preg_match('/\d{2}:\d{2}/', $dateValue)) {
664                return true;
665            }
666        }
667
668        return false;
669    }
670
671    private function rowDateValue(array $row): string
672    {
673        if (isset($row['date'])) {
674            return (string) $row['date'];
675        }
676
677        return (string) ($row[1] ?? '');
678    }
679
680    private function rowNumericValue(array $row, string $variable, int $position): int
681    {
682        if (isset($row[$variable])) {
683            return (int) $row[$variable];
684        }
685
686        return (int) ($row[$position] ?? 0);
687    }
688
689    private function emptyCapacityRow(string $subjectId, string $timelineKey): array
690    {
691        return [$subjectId, $timelineKey, 0, 0, 0, 0, 0, 0, 0, 0];
692    }
693
694    public function exchangeSupportsCapacityChannel(Exchange $exchange): bool
695    {
696        foreach ($exchange->dictionary as $entry) {
697            if (($entry['variable'] ?? null) === 'bookedcount_public') {
698                return true;
699            }
700        }
701
702        return false;
703    }
704
705    private function normalizeDataRow(array $row, bool $useHourlyKeys): array
706    {
707        $rowDateValue = $this->rowDateValue($row);
708
709        return [
710            (string) ($row['subjectid'] ?? $row[0] ?? ''),
711            $this->normalizeTimelineKey($rowDateValue, $useHourlyKeys),
712            $this->rowNumericValue($row, 'bookedcount', 2),
713            $this->rowNumericValue($row, 'plannedcount', 3),
714            $this->rowNumericValue($row, 'bookedminutes', 4),
715            $this->rowNumericValue($row, 'plannedminutes', 5),
716            $this->rowNumericValue($row, 'bookedcount_public', 6),
717            $this->rowNumericValue($row, 'plannedcount_public', 7),
718            $this->rowNumericValue($row, 'bookedminutes_public', 8),
719            $this->rowNumericValue($row, 'plannedminutes_public', 9),
720        ];
721    }
722
723    private function normalizeTimelineKey(string $rowDateValue, bool $useHourlyKeys): string
724    {
725        if ($rowDateValue === '') {
726            return '';
727        }
728
729        if (!$useHourlyKeys) {
730            return substr($rowDateValue, 0, 10);
731        }
732
733        $timestamp = strtotime($rowDateValue);
734        if ($timestamp === false) {
735            return $rowDateValue;
736        }
737
738        return date('Y-m-d H', $timestamp) . ':00';
739    }
740
741    private function resolveTimelineBounds(?array $dateRange, ?string $period): ?array
742    {
743        if (ReportHelper::hasValues($dateRange) && isset($dateRange['from'], $dateRange['to'])) {
744            return [
745                'from' => $dateRange['from'],
746                'to' => $dateRange['to'],
747            ];
748        }
749
750        if (!ReportHelper::hasNamedPeriod($period)) {
751            return null;
752        }
753
754        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $period)) {
755            return ['from' => $period, 'to' => $period];
756        }
757
758        if (preg_match('/^\d{4}-\d{2}$/', $period)) {
759            $start = new DateTimeImmutable($period . '-01');
760            $end = $start->modify('last day of this month');
761
762            return [
763                'from' => $start->format('Y-m-d'),
764                'to' => $end->format('Y-m-d'),
765            ];
766        }
767
768        if (preg_match('/^\d{4}$/', $period)) {
769            return [
770                'from' => $period . '-01-01',
771                'to' => $period . '-12-31',
772            ];
773        }
774
775        return null;
776    }
777
778    private function filterRowsByBounds(array $rows, array $bounds): array
779    {
780        $rangeStart = $bounds['from'];
781        $rangeEnd = $bounds['to'];
782
783        return array_values(array_filter($rows, static function ($row) use ($rangeStart, $rangeEnd) {
784            $date = (string) ($row[1] ?? '');
785            if ($date === '') {
786                return false;
787            }
788
789            $day = substr($date, 0, 10);
790
791            return $day >= $rangeStart && $day <= $rangeEnd;
792        }));
793    }
794
795    private function finalizeExchange(Exchange $exchange, ?string $fromDate = null, ?string $toDate = null): Exchange
796    {
797        if (ReportHelper::hasText($fromDate) && ReportHelper::hasText($toDate)) {
798            $exchange->firstDay = $this->dayFromString($fromDate);
799            $exchange->lastDay = $this->dayFromString($toDate);
800        } elseif (!empty($exchange->data)) {
801            $firstRowDate = (string) $exchange->data[0][1];
802            $lastRowDate = (string) $exchange->data[count($exchange->data) - 1][1];
803            $exchange->firstDay = $this->dayFromString(substr($firstRowDate, 0, 10));
804            $exchange->lastDay = $this->dayFromString(substr($lastRowDate, 0, 10));
805        }
806
807        return $exchange;
808    }
809
810    private function dayFromString(string $date): Day
811    {
812        $dateTime = new \DateTime(substr($date, 0, 10));
813
814        return (new Day())->setDateTime($dateTime);
815    }
816
817    private function enrichPeriodList(mixed $periodList, string $scopeId): mixed
818    {
819        if (!$periodList instanceof Exchange) {
820            return $periodList;
821        }
822
823        $hasOnlyAggregatePeriod = count($periodList->data) === 1
824            && isset($periodList->data[0][0])
825            && $periodList->data[0][0] === '_';
826
827        if (!$hasOnlyAggregatePeriod) {
828            return $periodList;
829        }
830
831        $exchange = $this->fetchAggregatedReport($scopeId, null, null);
832        if (!$exchange || empty($exchange->data)) {
833            return $periodList;
834        }
835
836        $years = [];
837        $months = [];
838
839        foreach ($exchange->data as $row) {
840            $rowDateValue = (string) ($row[1] ?? '');
841            if ($rowDateValue === '') {
842                continue;
843            }
844
845            $year = substr($rowDateValue, 0, 4);
846            $month = substr($rowDateValue, 0, 7);
847
848            if (!in_array($year, $years, true)) {
849                $years[] = $year;
850            }
851
852            if (!in_array($month, $months, true)) {
853                $months[] = $month;
854            }
855        }
856
857        rsort($years);
858        rsort($months);
859
860        $periodList->data = [];
861        foreach ($years as $year) {
862            $periodList->data[] = [$year];
863        }
864        foreach ($months as $month) {
865            $periodList->data[] = [$month];
866        }
867
868        return $periodList;
869    }
870
871    public function buildDownloadFilename(?array $dateRange, ?string $period, string $valueMode = 'slots'): string
872    {
873        $valueSuffix = $valueMode === 'minutes' ? '-minuten' : '-zeitschlitze';
874        $rangePart = $this->buildDownloadDateRangePart($dateRange, $period);
875        $baseName = 'terminkapazitaet' . $valueSuffix;
876
877        if ($rangePart === '') {
878            return $baseName;
879        }
880
881        return $baseName . '_' . (string) $rangePart;
882    }
883
884    private function buildDownloadDateRangePart(?array $dateRange, ?string $period): ?string
885    {
886        if ($dateRange !== null && !empty($dateRange['from']) && !empty($dateRange['to'])) {
887            return $dateRange['from'] . '-bis-' . $dateRange['to'];
888        }
889
890        if (!ReportHelper::hasNamedPeriod($period)) {
891            return '';
892        }
893
894        if (preg_match('/^(\d{4}-\d{2}-\d{2})_(\d{4}-\d{2}-\d{2})$/', $period, $matches)) {
895            return $matches[1] . '-bis-' . $matches[2];
896        }
897
898        return preg_replace('/[^0-9-]/', '', $period);
899    }
900
901    public function buildDownloadExchange(
902        Exchange $exchange,
903        string $channelMode = 'total',
904        string $valueMode = 'slots'
905    ): Exchange {
906        $channelMode = in_array($channelMode, ['total', 'public', 'intern_only'], true)
907            ? $channelMode
908            : 'total';
909        $useMinutes = $valueMode === 'minutes' && $this->exchangeSupportsMinutes($exchange);
910        $isHourly = $exchange->period === 'hour';
911
912        $download = new Exchange();
913        $download->addDictionaryEntry(
914            'date',
915            'string',
916            $isHourly ? 'Zeitpunkt' : 'Datum'
917        );
918        $download->addDictionaryEntry(
919            'planned',
920            'number',
921            $this->buildCapacityMetricLabel('planned', $channelMode, $useMinutes)
922        );
923        $download->addDictionaryEntry(
924            'booked',
925            'number',
926            $this->buildCapacityMetricLabel('booked', $channelMode, $useMinutes)
927        );
928        $download->addDictionaryEntry('utilization', 'string', 'Auslastung');
929
930        $totalPlanned = 0;
931        $totalBooked = 0;
932
933        foreach ($exchange->data as $row) {
934            if (!is_array($row)) {
935                continue;
936            }
937
938            $normalizedRow = $this->normalizeDataRow($row, $isHourly);
939            $planned = $this->resolveChannelMetric($normalizedRow, 'planned', $channelMode, $useMinutes);
940            $booked = $this->resolveChannelMetric($normalizedRow, 'booked', $channelMode, $useMinutes);
941            $totalPlanned += $planned;
942            $totalBooked += $booked;
943            $utilization = $planned > 0 ? round(($booked / $planned) * 1000) / 10 : 0;
944
945            $download->addDataSet([
946                $this->formatDownloadDate((string) $normalizedRow[1], $isHourly),
947                (string) $planned,
948                (string) $booked,
949                $this->formatUtilizationPercent($utilization),
950            ]);
951        }
952
953        $totalUtilization = $totalPlanned > 0
954            ? round(($totalBooked / $totalPlanned) * 1000) / 10
955            : 0;
956        $download->addDataSet([
957            'Summe',
958            (string) $totalPlanned,
959            (string) $totalBooked,
960            $this->formatUtilizationPercent($totalUtilization),
961        ]);
962
963        return $download;
964    }
965
966    private function exchangeSupportsMinutes(Exchange $exchange): bool
967    {
968        foreach ($exchange->dictionary as $entry) {
969            if (($entry['variable'] ?? null) === 'bookedminutes') {
970                return true;
971            }
972        }
973
974        return false;
975    }
976
977    private function buildCapacityMetricLabel(string $kind, string $channelMode, bool $useMinutes): string
978    {
979        $unit = $useMinutes ? 'Minuten' : 'Zeitschlitze';
980        $prefix = $kind === 'planned' ? 'Geplante' : 'Gebuchte';
981
982        return $prefix . ' Kapazität ' . $this->resolveCapacityChannelLabel($channelMode) . ' (' . $unit . ')';
983    }
984
985    private function resolveCapacityChannelLabel(string $channelMode): string
986    {
987        if ($channelMode === 'public') {
988            return 'Internet';
989        }
990        if ($channelMode === 'intern_only') {
991            return 'nur intern';
992        }
993
994        return 'insgesamt';
995    }
996
997    private function resolveChannelMetric(
998        array $normalizedRow,
999        string $metric,
1000        string $channelMode,
1001        bool $useMinutes
1002    ): int {
1003        if ($useMinutes) {
1004            $totalPosition = $metric === 'planned' ? 5 : 4;
1005            $publicPosition = $metric === 'planned' ? 9 : 8;
1006        } else {
1007            $totalPosition = $metric === 'planned' ? 3 : 2;
1008            $publicPosition = $metric === 'planned' ? 7 : 6;
1009        }
1010
1011        $total = (int) ($normalizedRow[$totalPosition] ?? 0);
1012        $public = (int) ($normalizedRow[$publicPosition] ?? 0);
1013
1014        if ($channelMode === 'public') {
1015            return $public;
1016        }
1017        if ($channelMode === 'intern_only') {
1018            return max(0, $total - $public);
1019        }
1020
1021        return $total;
1022    }
1023
1024    private function formatDownloadDate(string $dateValue, bool $isHourly): string
1025    {
1026        if ($isHourly || !preg_match('/^(\d{4})-(\d{2})-(\d{2})/', $dateValue, $matches)) {
1027            return $dateValue;
1028        }
1029
1030        return $matches[3] . '.' . $matches[2] . '.' . $matches[1];
1031    }
1032
1033    private function formatUtilizationPercent(float $utilization): string
1034    {
1035        $formatted = number_format($utilization, 1, ',', '.');
1036        if (str_ends_with($formatted, ',0')) {
1037            $formatted = substr($formatted, 0, -2);
1038        }
1039
1040        return $formatted . ' %';
1041    }
1042
1043    /**
1044     * Prepare download arguments for capacity report Excel export.
1045     */
1046    public function prepareDownloadArgs(
1047        array $args,
1048        string $scopeId,
1049        mixed $exchangeCapacity,
1050        ?array $dateRange,
1051        array $selectedScopes = [],
1052        string $valueMode = 'slots',
1053        string $channelMode = 'total'
1054    ): array {
1055        $args['category'] = 'capacityscope';
1056        $args['subject'] = 'capacityscope';
1057        $args['subjectid'] = $scopeId;
1058
1059        if (ReportHelper::hasValues($dateRange)) {
1060            $args['period'] = $dateRange['from'] . '_' . $dateRange['to'];
1061        } elseif (!isset($args['period']) || $args['period'] === null || $args['period'] === '') {
1062            $args['period'] = '_';
1063        }
1064
1065        $args['downloadTitle'] = $this->buildDownloadFilename(
1066            $dateRange,
1067            $args['period'] ?? null,
1068            $valueMode
1069        );
1070
1071        if (!empty($selectedScopes)) {
1072            $args['selectedScopes'] = $selectedScopes;
1073        } elseif ($scopeId !== '') {
1074            $args['selectedScopes'] = array_values(array_filter(explode(',', $scopeId)));
1075        }
1076
1077        if ($exchangeCapacity instanceof Exchange) {
1078            $args['reports'] = [$exchangeCapacity];
1079            $args['report'] = $this->buildDownloadExchange(
1080                $exchangeCapacity,
1081                $channelMode,
1082                $valueMode
1083            );
1084        }
1085
1086        return $args;
1087    }
1088}