Lines 96.96% 224 / 231
Methods 93.54% 29 / 31
Classes 0.00% 0 / 1
Name Lines Methods CRAP
 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
 [BO\Zmsbackend\Base] __construct 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] init 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] getWriter 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] getReader 100.00% 5 / 5 100.00% 1 / 1 3
 [BO\Zmsbackend\Base] fetchPreparedStatement 100.00% 9 / 9 100.00% 1 / 1 3
 [BO\Zmsbackend\Base] startExecute 100.00% 5 / 5 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] pdoExceptionHandler 100.00% 13 / 13 100.00% 1 / 1 4
 [BO\Zmsbackend\Base] fetchStatement 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchOne 100.00% 7 / 7 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] fetchRow 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchValue 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchAll 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchHandle 100.00% 5 / 5 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchList 100.00% 8 / 8 100.00% 1 / 1 2
 [BO\Zmsbackend\Base] writeItem 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] deleteItem 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] perform 100.00% 7 / 7 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] fetchAffected 100.00% 8 / 8 100.00% 1 / 1 1
 [BO\Zmsbackend\Base] readResolvedReferences n/a 0 / 0 n/a 0 / 0 1
 [BO\Zmsbackend\Base] hashStringValue 50.00% 5 / 10 0.00% 0 / 1 8.12
18class 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}

Inherited from BO\Zmsbackend\Base

38    public function __construct(?Connection\Pdo $writeConnection = null, ?Connection\Pdo $readConnection = null)
39    {
40        $this->writeDb = $writeConnection;
41        $this->readDb = $readConnection;
42    }
44    public static function init(?Connection\Pdo $writeConnection = null, ?Connection\Pdo $readConnection = null): static
45    {
46        $instance = new static($writeConnection, $readConnection);
47        return $instance;
48    }
50    public function getWriter(): Connection\Pdo
51    {
52        if (null === $this->writeDb) {
53            $this->writeDb = Connection\Select::getWriteConnection();
54            $this->readDb = $this->writeDb;
55        }
56        return $this->writeDb;
57    }
59    public function getReader(): Connection\Pdo
60    {
61        if (null !== $this->writeDb) {
62            // if readDB gets a reset, still use writeDB
63            return $this->writeDb;
64        }
65        if (null === $this->readDb) {
66            $this->readDb = Connection\Select::getReadConnection();
67        }
68        return $this->readDb;
69    }
71    public function fetchPreparedStatement(Query\Base|string $query)
72    {
73        $sql = "$query";
74        $reader = $this->getReader();
75        if (spl_object_hash($reader) != static::$preparedConnectionId) {
76            // do not use prepared statements on a different connection
77            static::$preparedCache = [];
78            static::$preparedConnectionId = spl_object_hash($reader);
79        }
80        if (!isset(static::$preparedCache[$sql])) {
81            $prepared = $this->getReader()->prepare($sql);
82            static::$preparedCache[$sql] = $prepared;
83        }
84        return static::$preparedCache[$sql];
85    }
87    public function startExecute(\PDOStatement $statement, array $parameters)
88    {
89        $statement = static::pdoExceptionHandler(function () use ($statement, $parameters) {
90            $statement->execute($parameters);
91            return $statement;
92        });
93        return $statement;
94    }
96    protected static function pdoExceptionHandler(\Closure $pdoFunction, array $parameters = [])
97    {
98        try {
99            $statement = $pdoFunction($parameters);
100        } catch (\PDOException $pdoException) {
101            if (stripos($pdoException->getMessage(), 'Lock wait timeout') !== false) {
102                throw new \BO\Zmsbackend\Exception\Pdo\LockTimeout();
103            }
104            //@codeCoverageIgnoreStart
105            if (stripos($pdoException->getMessage(), 'Deadlock found') !== false) {
106                throw new \BO\Zmsbackend\Exception\Pdo\DeadLockFound();
107            }
108            //@codeCoverageIgnoreEnd
109            $message = "SQL: "
110                . " Err: "
111                . $pdoException->getMessage()
112                //. " || Statement: "
113                //.$statement->queryString
114                //." || Parameters=". var_export($parameters, true)
115                ;
116            throw new \BO\Zmsbackend\Exception\Pdo\PDOFailed($message, 0, $pdoException);
117        }
118        return $statement;
119    }
121    public function fetchStatement(Query\Base $query)
122    {
123        $parameters = $query->getParameters();
124        $statement = $this->startExecute($this->fetchPreparedStatement($query), $parameters);
125        return $statement;
126    }
133    public function fetchOne(Query\Base $query, \BO\Zmsentities\Schema\Entity $entity)
134    {
135        $statement = $this->fetchStatement($query);
136        $data = $statement->fetch(\PDO::FETCH_ASSOC);
137        if ($data) {
138            $entity->exchangeArray($query->postProcessJoins($data));
139            $entity->setResolveLevel($query->getResolveLevel());
140        }
141        $statement->closeCursor();
142        return $entity;
143    }
145    public function fetchRow(Query\Base|string $query, array|null $parameters = null)
146    {
147        return static::pdoExceptionHandler(function () use ($query, $parameters) {
148            $prepared = $this->fetchPreparedStatement($query);
149            $prepared->execute($parameters);
150            $row = $prepared->fetch(\PDO::FETCH_ASSOC);
151            $prepared->closeCursor();
152            return $row;
153        });
154    }
156    public function fetchValue(Query\Base|string $query, array|null $parameters = null)
157    {
158        return static::pdoExceptionHandler(function () use ($query, $parameters) {
159            $prepared = $this->fetchPreparedStatement($query);
160            $prepared->execute($parameters);
161            $value = $prepared->fetchColumn();
162            $prepared->closeCursor();
163            return $value;
164        });
165    }
167    public function fetchAll(Query\Base|string $query, array|null $parameters = null)
168    {
169        return static::pdoExceptionHandler(function () use ($query, $parameters) {
170            $prepared = $this->fetchPreparedStatement($query);
171            $prepared->execute($parameters);
172            $list = $prepared->fetchAll(\PDO::FETCH_ASSOC);
173            $prepared->closeCursor();
174            return $list;
175        });
176    }
178    public function fetchHandle(Query\Base|string $query, array|null $parameters = null)
179    {
180        return static::pdoExceptionHandler(function () use ($query, $parameters) {
181            $prepared = $this->fetchPreparedStatement($query);
182            $prepared->execute($parameters);
183            return $prepared;
184        });
185    }
187    public function fetchList(Query\Base $query, \BO\Zmsentities\Schema\Entity $entity, array|\ArrayAccess $resultList = [])
188    {
189        $statement = $this->fetchStatement($query);
190        while ($data = $statement->fetch(\PDO::FETCH_ASSOC)) {
191            $dataEntity = clone $entity;
192            $dataEntity->exchangeArray($query->postProcessJoins($data));
193            $dataEntity->setResolveLevel($query->getResolveLevel());
194            $resultList[] = $dataEntity;
195        }
196        $statement->closeCursor();
197        return $resultList;
198    }
206    public function writeItem(Query\Base $query)
207    {
208        return static::pdoExceptionHandler(function () use ($query) {
209            $this->getWriter(); //Switch to writer for write/delete
210            $statement = $this->fetchPreparedStatement($query);
211            $status = $statement->execute($query->getParameters());
212            $statement->closeCursor();
213            return $status;
214        });
215    }
221    public function deleteItem(Query\Base $query)
222    {
223        return $this->writeItem($query);
224    }
226    public function perform(string $sql, array|null $parameters = null)
227    {
228        return static::pdoExceptionHandler(function () use ($sql, $parameters) {
229            $this->getWriter(); //Switch to writer for perform
230            $prepared = $this->fetchPreparedStatement($sql);
231            $status = $prepared->execute($parameters);
232            $prepared->closeCursor();
233            return $status;
234        });
235    }
237    public function fetchAffected(string $sql, array|null $parameters = null)
238    {
239        return static::pdoExceptionHandler(function () use ($sql, $parameters) {
240            $this->getWriter(); //Switch to writer for perform
241            $prepared = $this->fetchPreparedStatement($sql);
242            $prepared->execute($parameters);
243            $count = $prepared->rowCount();
244            $prepared->closeCursor();
245            return $count;
246        });
247    }
256    public function readResolvedReferences(\BO\Zmsentities\Schema\Entity $entity, int $resolveReferences)
257    {
258        return $entity;
259    }
268    public function hashStringValue(string $value, ?callable $callable = null): string
269    {
270        if ($callable === null) {
271            $callable = 'sha1';
272        }
273
274        if (is_callable($callable)) {
275            $hash = $callable($value);
276
277            if (is_string($callable)) {
278                return $callable . ':' . $hash;
279            }
280            if ($callable instanceof \Closure) {
281                return 'closure:' . $hash;
282            }
283            // else
284            return 'custom:' . $hash;
285        }
286
287        throw new \InvalidArgumentException('hashStringValue() should be called with a callable as second parameter.');
288    }