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