Lines 97.58% 202 / 207
Methods 96.77% 30 / 31
Classes 0.00% 0 / 1
Name Lines Methods CRAP
 readEntity 100.00% 26 / 26 100.00% 1 / 1 5
 getTotalActiveSessions 100.00% 5 / 5 100.00% 1 / 1 1
 getActiveSessionsByBehoerdenWithScopes 100.00% 20 / 20 100.00% 1 / 1 3
 getConfigProblems 100.00% 6 / 6 100.00% 1 / 1 3
 readDdldUpdateStats 100.00% 4 / 4 100.00% 1 / 1 1
 readOutdatedSlots 100.00% 5 / 5 100.00% 1 / 1 1
 readFreeSlots 100.00% 4 / 4 100.00% 1 / 1 1
 readLastCalculateSlots 100.00% 4 / 4 100.00% 1 / 1 1
 readMailStats 100.00% 5 / 5 100.00% 1 / 1 1
 readProcessStats 100.00% 9 / 9 100.00% 1 / 1 1
 readConfigVariables 100.00% 1 / 1 100.00% 1 / 1 1
 readStatusVariables 100.00% 1 / 1 100.00% 1 / 1 1
 [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
7class Status extends \BO\Zmsbackend\Base
8{
9    /**
10     * Fetch status from db
11     *
12     * @return \BO\Zmsentities\Status
13     */
14    public function readEntity(\DateTimeImmutable $now, bool $includeProcessStats = true)
15    {
16        $entity = new Entity();
17        $configVariables = $this->readConfigVariables();
18        $statusVariables = $this->readStatusVariables();
19        $nodeConnections = round($statusVariables['Threads_connected'] / $configVariables['max_connections'], 2);
20
21        $entity['database']['problems'] = $this->getConfigProblems($configVariables);
22        $entity['database']['locks'] = $statusVariables['Innodb_row_lock_current_waits'];
23        $entity['database']['threads'] = $statusVariables['Threads_connected'];
24        $entity['database']['nodeConnections'] = $nodeConnections;
25        $entity['database']['clusterStatus'] =
26            array_key_exists('wsrep_ready', $statusVariables) ? $statusVariables['wsrep_ready'] : 'OFF';
27        $entity['database']['logbin'] =
28            array_key_exists('log_bin', $configVariables) ? $configVariables['log_bin'] : 'OFF';
29        $entity['processes'] = $includeProcessStats ? $this->readProcessStats($now) : [];
30
31        if ($includeProcessStats) {
32            $entity['processes'] = $this->readProcessStats($now);
33            $outdated = $this->readOutdatedSlots();
34            $entity['processes']['outdated'] = $outdated['cnt'];
35            $entity['processes']['outdatedOldest'] = $outdated['oldest'];
36            $freeSlots = $this->readFreeSlots();
37            $entity['processes']['freeSlots'] = $freeSlots['cnt'];
38        }
39
40        $entity['mail'] = $this->readMailStats();
41        $entity['sources']['dldb']['last'] = $this->readDdldUpdateStats();
42        $entity['processes']['lastCalculate'] = $this->readLastCalculateSlots();
43        $entity['useraccounts']['activeSessions'] = $this->getTotalActiveSessions();
44        $entity['useraccounts']['departments'] = $this->getActiveSessionsByBehoerdenWithScopes();
45
46        return $entity;
47    }
48
49    /**
50     * Get total active sessions where SessionID is not null or empty and sessionExpiry is in the future
51     *
52     * @return int
53     */
54    protected function getTotalActiveSessions()
55    {
56        $result = $this->getReader()->fetchOne(
57            'SELECT COUNT(n.SessionID) as totalActiveSessions
58             FROM nutzer n
59             WHERE n.SessionID IS NOT NULL
60             AND n.SessionID != ""
61             AND n.sessionExpiry > UNIX_TIMESTAMP()'
62        );
63
64        return (int) $result['totalActiveSessions'];
65    }
66
67    /**
68     * Get active sessions grouped by BehoerdenID with scopes, excluding expired sessions
69     *
70     * @return array
71     */
72    protected function getActiveSessionsByBehoerdenWithScopes()
73    {
74        $result = $this->getReader()->fetchAll(
75            'SELECT b.BehoerdenID, b.Name as BehoerdeName, 
76                    s.StandortID, s.Bezeichnung as StandortName,
77                    COALESCE(SUM(CASE 
78                        WHEN n.sessionExpiry > UNIX_TIMESTAMP()
79                        THEN 1 ELSE 0 END), 0) as activeSessions
80             FROM behoerde b
81             LEFT JOIN standort s ON b.BehoerdenID = s.BehoerdenID
82             LEFT JOIN nutzer n ON s.StandortID = n.StandortID
83             GROUP BY b.BehoerdenID, b.Name, s.StandortID, s.Bezeichnung'
84        );
85
86        $activeSessionsByBehoerden = [];
87
88        foreach ($result as $row) {
89            $behoerdenID = $row['BehoerdenID'];
90            $standortID = $row['StandortID'];
91
92            if (!isset($activeSessionsByBehoerden[$behoerdenID])) {
93                $activeSessionsByBehoerden[$behoerdenID] = [
94                    'activeSessions' => 0,
95                    'name' => $row['BehoerdeName'],
96                    'scopes' => []
97                ];
98            }
99
100            $activeSessionsByBehoerden[$behoerdenID]['scopes'][$standortID] = [
101                'activeSessions' => (int) $row['activeSessions'],
102                'name' => $row['StandortName']
103            ];
104
105            $activeSessionsByBehoerden[$behoerdenID]['activeSessions'] += (int) $row['activeSessions'];
106        }
107
108        return $activeSessionsByBehoerden;
109    }
110
111    /**
112     * Get the configuration problems
113     *
114     * @return string
115     */
116    public function getConfigProblems(array $configVariables)
117    {
118        $problems = [];
119        if ($configVariables['tmp_table_size'] < 32000000) {
120            $problems[] = 'tmp_table_size should be at least 32MB';
121        }
122        if ($configVariables['max_heap_table_size'] < 32000000) {
123            $problems[] = 'max_heap_table_size should be at least 32MB';
124        }
125        return implode('; ', $problems);
126    }
127
128    /**
129     * Get the dldb update status
130     *
131     * @return array
132     */
133    protected function readDdldUpdateStats()
134    {
135        $stats = $this->getReader()->fetchOne(
136            'SELECT value FROM config WHERE name = "sources_dldb_last"'
137        );
138        return $stats['value'];
139    }
140
141    /**
142     * Get outdated slots
143     *
144     * @return array
145     */
146    protected function readOutdatedSlots()
147    {
148        $stats = $this->getReader()->fetchOne(
149            'SELECT COUNT(*) cnt, MIN(a.updated_at) oldest
150             FROM slot s
151             LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID
152             WHERE s.updateTimestamp < a.updated_at AND s.status = "free"'
153        );
154        return $stats;
155    }
156
157    /**
158     * Get free slots count
159     *
160     * @return array
161     */
162    protected function readFreeSlots()
163    {
164        $stats = $this->getReader()->fetchOne(
165            'SELECT SUM(intern) cnt FROM slot s WHERE s.status = "free"'
166        );
167        return $stats;
168    }
169
170    /**
171     * Get last calculate slots information
172     *
173     * @return array
174     */
175    protected function readLastCalculateSlots()
176    {
177        $stats = $this->getReader()->fetchOne(
178            'SELECT value FROM config WHERE name = "status__calculateSlotsLastRun"'
179        );
180        return $stats['value'];
181    }
182
183    /**
184     * Get mail stats
185     *
186     * @return array
187     */
188    protected function readMailStats()
189    {
190        $stats = $this->getReader()->fetchOne(
191            'SELECT COUNT(id) as queueCount, UNIX_TIMESTAMP() - MIN(createTimestamp) as oldestSeconds, 
192                UNIX_TIMESTAMP() - MAX(createTimestamp) as newestSeconds
193             FROM mailqueue'
194        );
195        return $stats;
196    }
197
198    /**
199     * Get process statistics
200     *
201     * @return array
202     */
203    protected function readProcessStats(\DateTimeImmutable $now)
204    {
205        $midnight = $now->modify('00:00:00')->getTimestamp();
206        $last7days = $now->modify('-7 days 00:00:00')->getTimestamp();
207
208        $processStats = $this->getReader()->fetchOne(
209            'SELECT
210                COALESCE(SUM(CASE WHEN b.status = "blocked" THEN 1 ELSE 0 END), 0) AS blocked,
211                COALESCE(SUM(CASE WHEN b.status = "called" THEN 1 ELSE 0 END), 0) AS called,
212                COALESCE(SUM(CASE WHEN b.status = "confirmed" THEN 1 ELSE 0 END), 0) AS confirmed,
213                COALESCE(SUM(CASE WHEN b.status = "pending" THEN 1 ELSE 0 END), 0) AS pending,
214                COALESCE(SUM(CASE WHEN b.status = "deleted" THEN 1 ELSE 0 END), 0) AS deleted,
215                COALESCE(SUM(CASE WHEN b.status = "missed" THEN 1 ELSE 0 END), 0) AS missed,
216                COALESCE(SUM(CASE WHEN b.status = "parked" THEN 1 ELSE 0 END), 0) AS parked,
217                COALESCE(SUM(CASE WHEN b.status = "reserved" THEN 1 ELSE 0 END), 0) AS reserved,
218                COALESCE(SUM(
219                    CASE
220                        WHEN b.external_user_id IS NOT NULL AND b.external_user_id != ""
221                        THEN 1 ELSE 0
222                    END
223                ), 0) AS withExternalUserId,
224                COALESCE(SUM(
225                    CASE
226                        WHEN b.status = "confirmed"
227                            AND b.external_user_id IS NOT NULL
228                            AND b.external_user_id != ""
229                        THEN 1 ELSE 0
230                    END
231                ), 0) AS confirmedWithExternalUserId,
232                global.sinceMidnight,
233                global.last7days,
234                global.lastInsert
235             FROM buerger AS b
236             CROSS JOIN (
237                SELECT
238                    COALESCE(SUM(
239                        CASE
240                            WHEN IPTimeStamp > ' . intval($midnight) . ' 
241                                 AND StandortID != 0
242                                 AND vorlaeufigeBuchung = 0
243                                 AND Abholer = 0
244                            THEN 1 ELSE 0
245                        END
246                    ), 0) AS sinceMidnight,
247                    COALESCE(SUM(
248                        CASE
249                            WHEN IPTimeStamp > ' . intval($last7days) . ' 
250                                 AND StandortID != 0
251                                 AND vorlaeufigeBuchung = 0
252                                 AND Abholer = 0
253                            THEN 1 ELSE 0
254                        END
255                    ), 0) AS last7days,
256                    FROM_UNIXTIME(MAX(IPTimeStamp)) AS lastInsert
257                FROM buerger
258                WHERE istFolgeterminvon IS NULL OR istFolgeterminvon = 0
259             ) AS global
260             WHERE b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0'
261        );
262        return $processStats;
263    }
264
265    /**
266     * Fetch MySQL config variables
267     *
268     * @return array
269     */
270    protected function readConfigVariables()
271    {
272        return $this->getReader()->fetchPairs('SHOW VARIABLES');
273    }
274
275    /**
276     * Fetch MySQL status variables
277     *
278     * @return array
279     */
280    protected function readStatusVariables()
281    {
282        return $this->getReader()->fetchPairs('SHOW STATUS');
283    }
284}

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($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, $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    }