Lines
98.24%
112 / 114
Functions and Methods
91.66%
11 / 12
Classes and Traits
0.00%
0 / 1
| Name | Lines | Functions and Methods | CRAP | Classes and Traits | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| CalculateDailyWaitingStatisticByCron | 98.24% | 112 / 114 | 91.66% | 11 / 12 | 40 | 0.00% | 0 / 1 | |||
| run | 100.00% | 5 / 5 | 100.00% | 1 / 1 | 1 | |||||
| fetchArchiveData | 100.00% | 5 / 5 | 100.00% | 1 / 1 | 1 | |||||
| processArchiveRows | 90.90% | 20 / 22 | 0.00% | 0 / 1 | 10.08 | |||||
| determineHourAndType | 100.00% | 4 / 4 | 100.00% | 1 / 1 | 4 | |||||
| initializeStatsIfNeeded | 100.00% | 9 / 9 | 100.00% | 1 / 1 | 4 | |||||
| saveStatistics | 100.00% | 3 / 3 | 100.00% | 1 / 1 | 3 | |||||
| ensureStatisticsRow | 100.00% | 18 / 18 | 100.00% | 1 / 1 | 2 | |||||
| updateStatisticsValues | 100.00% | 19 / 19 | 100.00% | 1 / 1 | 3 | |||||
| addHourUpdateColumns | 100.00% | 22 / 22 | 100.00% | 1 / 1 | 4 | |||||
| hourSuffixForStatistic | 100.00% | 1 / 1 | 100.00% | 1 / 1 | 2 | |||||
| hasPositiveWaitingTime | 100.00% | 1 / 1 | 100.00% | 1 / 1 | 2 | |||||
| countsForWayTime | 100.00% | 5 / 5 | 100.00% | 1 / 1 | 4 | |||||
| 1 | <?php | |
| 2 | ||
| 3 | namespace BO\Zmsbackend\Helper; | |
| 4 | ||
| 5 | use DateTimeImmutable; | |
| 6 | ||
| 7 | /** | |
| 8 | * Berechnung der Wartezeiten pro Standort und Stunde. | |
| 9 | * | |
| 10 | * Liest archivierte Prozesse für ein Datum und speichert die Durchschnittswerte | |
| 11 | * in 'wartenrstatistik'. Anzahl und Wartezeit zählen nur Einträge mit Wartezeit > 0. | |
| 12 | * Wegezeit zählt dieselben Einträge und zusätzlich Terminkunden, die vor dem | |
| 13 | * Terminzeitpunkt aufgerufen wurden (Wartezeit 0, erschienen) und eine Wegezeit haben. | |
| 14 | * Deren Anzahl steht in hour_*_way_count_appointment, getrennt von der Wartenden-Anzahl. | |
| 15 | * Nicht erschienene Termine mit Wartezeit 0 bleiben außen vor. | |
| 16 | * Bereits vorhandene Zeilen für (Standort, Datum) werden aktualisiert. | |
| 17 | */ | |
| 18 | class CalculateDailyWaitingStatisticByCron extends \BO\Zmsbackend\Base | |
| 19 | { | |
| 20 | public function run(DateTimeImmutable $day, bool $commit = false): void | |
| 21 | { | |
| 22 | \App::$log->info('CalculateDailyWaitingStatisticByCron started', ['date' => $day->format('Y-m-d')]); | |
| 23 | ||
| 24 | $archiveRows = $this->fetchArchiveData($day); | |
| 25 | $statsByScopeDate = $this->processArchiveRows($archiveRows); | |
| 26 | $this->saveStatistics($statsByScopeDate, $commit); | |
| 27 | ||
| 28 | \App::$log->info('CalculateDailyWaitingStatisticByCron finished', ['date' => $day->format('Y-m-d')]); | |
| 29 | } | |
| 30 | ||
| 31 | private function fetchArchiveData(DateTimeImmutable $day): array | |
| 32 | { | |
| 33 | $sql = " | |
| 34 | SELECT | |
| 35 | StandortID, | |
| 36 | Datum, | |
| 37 | `Timestamp`, | |
| 38 | mitTermin, | |
| 39 | nicht_erschienen, | |
| 40 | waiting_time, | |
| 41 | way_time | |
| 42 | FROM buergerarchiv | |
| 43 | WHERE Datum = :theDay | |
| 44 | AND StandortID > 0 | |
| 45 | "; | |
| 46 | return $this->getReader()->fetchAll($sql, [ | |
| 47 | 'theDay' => $day->format('Y-m-d'), | |
| 48 | ]); | |
| 49 | } | |
| 50 | ||
| 51 | private function processArchiveRows(array $archiveRows): array | |
| 52 | { | |
| 53 | $statsByScopeDate = []; | |
| 54 | ||
| 55 | foreach ($archiveRows as $row) { | |
| 56 | $includeInWaiting = $this->hasPositiveWaitingTime($row); | |
| 57 | $includeInWay = $this->countsForWayTime($row); | |
| 58 | if (!$includeInWaiting && !$includeInWay) { | |
| 59 | continue; | |
| 60 | } | |
| 61 | ||
| 62 | $scopeId = (int) $row['StandortID']; | |
| 63 | if ($scopeId <= 0) { | |
| 64 | continue; | |
| 65 | } | |
| 66 | ||
| 67 | [$hour, $type] = $this->determineHourAndType($row); | |
| 68 | if ($hour < 0 || $hour > 23) { | |
| 69 | continue; | |
| 70 | } | |
| 71 | ||
| 72 | $dateStr = $row['Datum']; | |
| 73 | $this->initializeStatsIfNeeded($statsByScopeDate, $scopeId, $dateStr); | |
| 74 | ||
| 75 | if ($includeInWaiting) { | |
| 76 | $statsByScopeDate[$scopeId][$dateStr][$hour][$type]['count'] += 1; | |
| 77 | $statsByScopeDate[$scopeId][$dateStr][$hour][$type]['sumWait'] += (float) $row['waiting_time']; | |
| 78 | } | |
| 79 | if ($includeInWay) { | |
| 80 | $wayMins = is_numeric($row['way_time'] ?? null) ? (float) $row['way_time'] : 0.0; | |
| 81 | $statsByScopeDate[$scopeId][$dateStr][$hour][$type]['countWay'] += 1; | |
| 82 | $statsByScopeDate[$scopeId][$dateStr][$hour][$type]['sumWay'] += $wayMins; | |
| 83 | } | |
| 84 | } | |
| 85 | ||
| 86 | return $statsByScopeDate; | |
| 87 | } | |
| 88 | ||
| 89 | private function determineHourAndType(array $archiveRecord): array | |
| 90 | { | |
| 91 | $type = ((int) $archiveRecord['mitTermin'] === 1) ? 'termin' : 'spontan'; | |
| 92 | $parts = explode(':', (string) $archiveRecord['Timestamp']); | |
| 93 | $hour = isset($parts[0]) && $parts[0] !== '' ? (int) $parts[0] : -1; | |
| 94 | ||
| 95 | return [$hour, $type]; | |
| 96 | } | |
| 97 | ||
| 98 | private function initializeStatsIfNeeded(array &$statsByScopeDate, int $scopeId, string $dateStr): void | |
| 99 | { | |
| 100 | if (!isset($statsByScopeDate[$scopeId])) { | |
| 101 | $statsByScopeDate[$scopeId] = []; | |
| 102 | } | |
| 103 | ||
| 104 | if (!isset($statsByScopeDate[$scopeId][$dateStr])) { | |
| 105 | $statsByScopeDate[$scopeId][$dateStr] = []; | |
| 106 | foreach (range(0, 23) as $h) { | |
| 107 | $statsByScopeDate[$scopeId][$dateStr][$h] = [ | |
| 108 | 'spontan' => ['count' => 0, 'sumWait' => 0.0, 'countWay' => 0, 'sumWay' => 0.0], | |
| 109 | 'termin' => ['count' => 0, 'sumWait' => 0.0, 'countWay' => 0, 'sumWay' => 0.0], | |
| 110 | ]; | |
| 111 | } | |
| 112 | } | |
| 113 | } | |
| 114 | ||
| 115 | private function saveStatistics(array $statsByScopeDate, bool $commit): void | |
| 116 | { | |
| 117 | foreach ($statsByScopeDate as $scopeId => $dateArray) { | |
| 118 | foreach ($dateArray as $dateStr => $hoursData) { | |
| 119 | $this->updateStatisticsValues((int) $scopeId, $dateStr, $hoursData, $commit); | |
| 120 | } | |
| 121 | } | |
| 122 | } | |
| 123 | ||
| 124 | private function ensureStatisticsRow(int $scopeId, string $dateStr): int | |
| 125 | { | |
| 126 | $existingId = $this->fetchValue( | |
| 127 | 'SELECT wartenrstatistikid | |
| 128 | FROM wartenrstatistik | |
| 129 | WHERE standortid = :sid | |
| 130 | AND datum = :d | |
| 131 | ORDER BY wartenrstatistikid ASC | |
| 132 | LIMIT 1', | |
| 133 | [ | |
| 134 | 'sid' => $scopeId, | |
| 135 | 'd' => $dateStr, | |
| 136 | ] | |
| 137 | ); | |
| 138 | ||
| 139 | if ($existingId) { | |
| 140 | return (int) $existingId; | |
| 141 | } | |
| 142 | ||
| 143 | $this->perform( | |
| 144 | 'INSERT INTO wartenrstatistik (standortid, datum) VALUES (:sid, :d)', | |
| 145 | [ | |
| 146 | 'sid' => $scopeId, | |
| 147 | 'd' => $dateStr, | |
| 148 | ] | |
| 149 | ); | |
| 150 | ||
| 151 | return (int) $this->getWriter()->lastInsertId(); | |
| 152 | } | |
| 153 | ||
| 154 | private function updateStatisticsValues(int $scopeId, string $dateStr, array $hoursData, bool $commit): void | |
| 155 | { | |
| 156 | $updateParams = []; | |
| 157 | $updateCols = []; | |
| 158 | ||
| 159 | foreach (range(0, 23) as $hour) { | |
| 160 | $this->addHourUpdateColumns($updateCols, $updateParams, $hour, $hoursData, 'spontan'); | |
| 161 | $this->addHourUpdateColumns($updateCols, $updateParams, $hour, $hoursData, 'termin'); | |
| 162 | } | |
| 163 | ||
| 164 | if (!$commit) { | |
| 165 | \App::$log->info('[DRY RUN] update scope statistics', [ | |
| 166 | 'scopeId' => $scopeId, | |
| 167 | 'date' => $dateStr, | |
| 168 | ]); | |
| 169 | return; | |
| 170 | } | |
| 171 | ||
| 172 | $rowId = $this->ensureStatisticsRow($scopeId, $dateStr); | |
| 173 | $updateParams['id'] = $rowId; | |
| 174 | ||
| 175 | $sqlUpdate = sprintf( | |
| 176 | 'UPDATE wartenrstatistik | |
| 177 | SET %s | |
| 178 | WHERE wartenrstatistikid = :id | |
| 179 | LIMIT 1', | |
| 180 | implode(', ', $updateCols) | |
| 181 | ); | |
| 182 | ||
| 183 | $this->perform($sqlUpdate, $updateParams); | |
| 184 | } | |
| 185 | ||
| 186 | private function addHourUpdateColumns( | |
| 187 | array &$updateCols, | |
| 188 | array &$updateParams, | |
| 189 | int $hour, | |
| 190 | array $hoursData, | |
| 191 | string $type | |
| 192 | ): void { | |
| 193 | $hourSuffix = $this->hourSuffixForStatistic($type); | |
| 194 | $colWaitCount = sprintf('hour_%02d_waiting_count_%s', $hour, $hourSuffix); | |
| 195 | $colWaitTime = sprintf('hour_%02d_waiting_time_%s', $hour, $hourSuffix); | |
| 196 | $colWayTime = sprintf('hour_%02d_way_time_%s', $hour, $hourSuffix); | |
| 197 | ||
| 198 | $count = $hoursData[$hour][$type]['count']; | |
| 199 | $countWay = $hoursData[$hour][$type]['countWay']; | |
| 200 | $avgWait = ($count > 0) | |
| 201 | ? round($hoursData[$hour][$type]['sumWait'] / $count, 2) | |
| 202 | : 0.0; | |
| 203 | $avgWay = ($countWay > 0) | |
| 204 | ? round($hoursData[$hour][$type]['sumWay'] / $countWay, 2) | |
| 205 | : 0.0; | |
| 206 | ||
| 207 | $updateCols[] = "`$colWaitCount` = :$colWaitCount"; | |
| 208 | $updateCols[] = "`$colWaitTime` = :$colWaitTime"; | |
| 209 | $updateCols[] = "`$colWayTime` = :$colWayTime"; | |
| 210 | ||
| 211 | $updateParams[$colWaitCount] = $count; | |
| 212 | $updateParams[$colWaitTime] = $avgWait; | |
| 213 | $updateParams[$colWayTime] = $avgWay; | |
| 214 | ||
| 215 | if ($type === 'termin') { | |
| 216 | $colWayCount = sprintf('hour_%02d_way_count_%s', $hour, $hourSuffix); | |
| 217 | $updateCols[] = "`$colWayCount` = :$colWayCount"; | |
| 218 | $updateParams[$colWayCount] = $countWay; | |
| 219 | } | |
| 220 | } | |
| 221 | ||
| 222 | private function hourSuffixForStatistic(string $type): string | |
| 223 | { | |
| 224 | return $type === 'termin' ? 'appointment' : 'spontaneous'; | |
| 225 | } | |
| 226 | ||
| 227 | private function hasPositiveWaitingTime(array $row): bool | |
| 228 | { | |
| 229 | return is_numeric($row['waiting_time'] ?? null) && (float) $row['waiting_time'] > 0; | |
| 230 | } | |
| 231 | ||
| 232 | private function countsForWayTime(array $row): bool | |
| 233 | { | |
| 234 | if ($this->hasPositiveWaitingTime($row)) { | |
| 235 | return true; | |
| 236 | } | |
| 237 | ||
| 238 | return (int) ($row['mitTermin'] ?? 0) === 1 | |
| 239 | && (int) ($row['nicht_erschienen'] ?? 0) === 0 | |
| 240 | && is_numeric($row['way_time'] ?? null); | |
| 241 | } | |
| 242 | } |