Lines
99.02%
102 / 103
Functions and Methods
92.85%
13 / 14
Classes and Traits
0.00%
0 / 1
| Name | Lines | Functions and Methods | CRAP | Classes and Traits | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Status | 99.02% | 102 / 103 | 92.85% | 13 / 14 | 26 | 0.00% | 0 / 1 | |||
| readEntity | 100.00% | 26 / 26 | 100.00% | 1 / 1 | 6 | |||||
| isSlotDeleteRunning | 85.71% | 6 / 7 | 0.00% | 0 / 1 | 4.05 | |||||
| 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% | 10 / 10 | 100.00% | 1 / 1 | 1 | |||||
| readConfirmedWithMultipleServices | 100.00% | 5 / 5 | 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 | |||||
| 1 | <?php | |
| 2 | ||
| 3 | namespace BO\Zmsbackend\Status\Service; | |
| 4 | ||
| 5 | use BO\Zmsentities\Status as Entity; | |
| 6 | ||
| 7 | class 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 && ! $this->isSlotDeleteRunning($now)) { | |
| 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 | * Set only by calculateSlots --delete. Newer than the last finished run, and less than 15 minutes old. | |
| 51 | * | |
| 52 | * @return bool | |
| 53 | */ | |
| 54 | protected function isSlotDeleteRunning(\DateTimeImmutable $now): bool | |
| 55 | { | |
| 56 | $config = new \BO\Zmsbackend\Config\Service\Config(); | |
| 57 | $started = $config->readProperty('status__calculateSlotsWithDeleteStart'); | |
| 58 | $finished = $config->readProperty('status__calculateSlotsLastRun'); | |
| 59 | $startedAt = is_string($started) ? strtotime($started) : false; | |
| 60 | if ($startedAt === false) { | |
| 61 | return false; | |
| 62 | } | |
| 63 | ||
| 64 | return $started > $finished && ($startedAt + (15 * 60)) > $now->getTimestamp(); | |
| 65 | } | |
| 66 | ||
| 67 | /** | |
| 68 | * Get total active sessions where SessionID is not null or empty and sessionExpiry is in the future | |
| 69 | * | |
| 70 | * @return int | |
| 71 | */ | |
| 72 | protected function getTotalActiveSessions() | |
| 73 | { | |
| 74 | $result = $this->getReader()->fetchOne( | |
| 75 | 'SELECT COUNT(n.SessionID) as totalActiveSessions | |
| 76 | FROM nutzer n | |
| 77 | WHERE n.SessionID IS NOT NULL | |
| 78 | AND n.SessionID != "" | |
| 79 | AND n.sessionExpiry > UNIX_TIMESTAMP()' | |
| 80 | ); | |
| 81 | ||
| 82 | return (int) $result['totalActiveSessions']; | |
| 83 | } | |
| 84 | ||
| 85 | /** | |
| 86 | * Get active sessions grouped by BehoerdenID with scopes, excluding expired sessions | |
| 87 | * | |
| 88 | * @return array | |
| 89 | */ | |
| 90 | protected function getActiveSessionsByBehoerdenWithScopes() | |
| 91 | { | |
| 92 | $result = $this->getReader()->fetchAll( | |
| 93 | 'SELECT b.BehoerdenID, b.Name as BehoerdeName, | |
| 94 | s.StandortID, s.Bezeichnung as StandortName, | |
| 95 | COALESCE(SUM(CASE | |
| 96 | WHEN n.sessionExpiry > UNIX_TIMESTAMP() | |
| 97 | THEN 1 ELSE 0 END), 0) as activeSessions | |
| 98 | FROM behoerde b | |
| 99 | LEFT JOIN standort s ON b.BehoerdenID = s.BehoerdenID | |
| 100 | LEFT JOIN nutzer n ON s.StandortID = n.StandortID | |
| 101 | GROUP BY b.BehoerdenID, b.Name, s.StandortID, s.Bezeichnung' | |
| 102 | ); | |
| 103 | ||
| 104 | $activeSessionsByBehoerden = []; | |
| 105 | ||
| 106 | foreach ($result as $row) { | |
| 107 | $behoerdenID = $row['BehoerdenID']; | |
| 108 | $standortID = $row['StandortID']; | |
| 109 | ||
| 110 | if (!isset($activeSessionsByBehoerden[$behoerdenID])) { | |
| 111 | $activeSessionsByBehoerden[$behoerdenID] = [ | |
| 112 | 'activeSessions' => 0, | |
| 113 | 'name' => $row['BehoerdeName'], | |
| 114 | 'scopes' => [] | |
| 115 | ]; | |
| 116 | } | |
| 117 | ||
| 118 | $activeSessionsByBehoerden[$behoerdenID]['scopes'][$standortID] = [ | |
| 119 | 'activeSessions' => (int) $row['activeSessions'], | |
| 120 | 'name' => $row['StandortName'] | |
| 121 | ]; | |
| 122 | ||
| 123 | $activeSessionsByBehoerden[$behoerdenID]['activeSessions'] += (int) $row['activeSessions']; | |
| 124 | } | |
| 125 | ||
| 126 | return $activeSessionsByBehoerden; | |
| 127 | } | |
| 128 | ||
| 129 | /** | |
| 130 | * Get the configuration problems | |
| 131 | * | |
| 132 | * @return string | |
| 133 | */ | |
| 134 | public function getConfigProblems(array $configVariables) | |
| 135 | { | |
| 136 | $problems = []; | |
| 137 | if ($configVariables['tmp_table_size'] < 32000000) { | |
| 138 | $problems[] = 'tmp_table_size should be at least 32MB'; | |
| 139 | } | |
| 140 | if ($configVariables['max_heap_table_size'] < 32000000) { | |
| 141 | $problems[] = 'max_heap_table_size should be at least 32MB'; | |
| 142 | } | |
| 143 | return implode('; ', $problems); | |
| 144 | } | |
| 145 | ||
| 146 | /** | |
| 147 | * Get the dldb update status | |
| 148 | * | |
| 149 | * @return array | |
| 150 | */ | |
| 151 | protected function readDdldUpdateStats() | |
| 152 | { | |
| 153 | $stats = $this->getReader()->fetchOne( | |
| 154 | 'SELECT value FROM config WHERE name = "sources_dldb_last"' | |
| 155 | ); | |
| 156 | return $stats['value']; | |
| 157 | } | |
| 158 | ||
| 159 | /** | |
| 160 | * Get outdated slots | |
| 161 | * | |
| 162 | * @return array | |
| 163 | */ | |
| 164 | protected function readOutdatedSlots() | |
| 165 | { | |
| 166 | $stats = $this->getReader()->fetchOne( | |
| 167 | 'SELECT COUNT(*) cnt, MIN(a.updated_at) oldest | |
| 168 | FROM slot s | |
| 169 | LEFT JOIN oeffnungszeit a ON s.availabilityID = a.OeffnungszeitID | |
| 170 | WHERE s.updateTimestamp < a.updated_at AND s.status = "free"' | |
| 171 | ); | |
| 172 | return $stats; | |
| 173 | } | |
| 174 | ||
| 175 | /** | |
| 176 | * Get free slots count | |
| 177 | * | |
| 178 | * @return array | |
| 179 | */ | |
| 180 | protected function readFreeSlots() | |
| 181 | { | |
| 182 | $stats = $this->getReader()->fetchOne( | |
| 183 | 'SELECT SUM(intern) cnt FROM slot s WHERE s.status = "free"' | |
| 184 | ); | |
| 185 | return $stats; | |
| 186 | } | |
| 187 | ||
| 188 | /** | |
| 189 | * Get last calculate slots information | |
| 190 | * | |
| 191 | * @return array | |
| 192 | */ | |
| 193 | protected function readLastCalculateSlots() | |
| 194 | { | |
| 195 | $stats = $this->getReader()->fetchOne( | |
| 196 | 'SELECT value FROM config WHERE name = "status__calculateSlotsLastRun"' | |
| 197 | ); | |
| 198 | return $stats['value']; | |
| 199 | } | |
| 200 | ||
| 201 | /** | |
| 202 | * Get mail stats | |
| 203 | * | |
| 204 | * @return array | |
| 205 | */ | |
| 206 | protected function readMailStats() | |
| 207 | { | |
| 208 | $stats = $this->getReader()->fetchOne( | |
| 209 | 'SELECT COUNT(id) as queueCount, UNIX_TIMESTAMP() - MIN(createTimestamp) as oldestSeconds, | |
| 210 | UNIX_TIMESTAMP() - MAX(createTimestamp) as newestSeconds | |
| 211 | FROM mailqueue' | |
| 212 | ); | |
| 213 | return $stats; | |
| 214 | } | |
| 215 | ||
| 216 | /** | |
| 217 | * Get process statistics | |
| 218 | * | |
| 219 | * @return array | |
| 220 | */ | |
| 221 | protected function readProcessStats(\DateTimeImmutable $now) | |
| 222 | { | |
| 223 | $midnight = $now->modify('00:00:00')->getTimestamp(); | |
| 224 | $last7days = $now->modify('-7 days 00:00:00')->getTimestamp(); | |
| 225 | ||
| 226 | $processStats = $this->getReader()->fetchOne( | |
| 227 | 'SELECT | |
| 228 | COALESCE(SUM(CASE WHEN b.status = "blocked" THEN 1 ELSE 0 END), 0) AS blocked, | |
| 229 | COALESCE(SUM(CASE WHEN b.status = "called" THEN 1 ELSE 0 END), 0) AS called, | |
| 230 | COALESCE(SUM(CASE WHEN b.status = "confirmed" THEN 1 ELSE 0 END), 0) AS confirmed, | |
| 231 | COALESCE(SUM(CASE WHEN b.status = "pending" THEN 1 ELSE 0 END), 0) AS pending, | |
| 232 | COALESCE(SUM(CASE WHEN b.status = "deleted" THEN 1 ELSE 0 END), 0) AS deleted, | |
| 233 | COALESCE(SUM(CASE WHEN b.status = "missed" THEN 1 ELSE 0 END), 0) AS missed, | |
| 234 | COALESCE(SUM(CASE WHEN b.status = "parked" THEN 1 ELSE 0 END), 0) AS parked, | |
| 235 | COALESCE(SUM(CASE WHEN b.status = "reserved" THEN 1 ELSE 0 END), 0) AS reserved, | |
| 236 | COALESCE(SUM( | |
| 237 | CASE | |
| 238 | WHEN b.external_user_id IS NOT NULL AND b.external_user_id != "" | |
| 239 | THEN 1 ELSE 0 | |
| 240 | END | |
| 241 | ), 0) AS withExternalUserId, | |
| 242 | COALESCE(SUM( | |
| 243 | CASE | |
| 244 | WHEN b.status = "confirmed" | |
| 245 | AND b.external_user_id IS NOT NULL | |
| 246 | AND b.external_user_id != "" | |
| 247 | THEN 1 ELSE 0 | |
| 248 | END | |
| 249 | ), 0) AS confirmedWithExternalUserId, | |
| 250 | global.sinceMidnight, | |
| 251 | global.last7days, | |
| 252 | global.lastInsert | |
| 253 | FROM buerger AS b | |
| 254 | CROSS JOIN ( | |
| 255 | SELECT | |
| 256 | COALESCE(SUM( | |
| 257 | CASE | |
| 258 | WHEN IPTimeStamp > ' . intval($midnight) . ' | |
| 259 | AND StandortID != 0 | |
| 260 | AND vorlaeufigeBuchung = 0 | |
| 261 | AND Abholer = 0 | |
| 262 | THEN 1 ELSE 0 | |
| 263 | END | |
| 264 | ), 0) AS sinceMidnight, | |
| 265 | COALESCE(SUM( | |
| 266 | CASE | |
| 267 | WHEN IPTimeStamp > ' . intval($last7days) . ' | |
| 268 | AND StandortID != 0 | |
| 269 | AND vorlaeufigeBuchung = 0 | |
| 270 | AND Abholer = 0 | |
| 271 | THEN 1 ELSE 0 | |
| 272 | END | |
| 273 | ), 0) AS last7days, | |
| 274 | FROM_UNIXTIME(MAX(IPTimeStamp)) AS lastInsert | |
| 275 | FROM buerger | |
| 276 | WHERE istFolgeterminvon IS NULL OR istFolgeterminvon = 0 | |
| 277 | ) AS global | |
| 278 | WHERE b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0' | |
| 279 | ); | |
| 280 | $processStats['confirmedWithMultipleServices'] = $this->readConfirmedWithMultipleServices(); | |
| 281 | return $processStats; | |
| 282 | } | |
| 283 | ||
| 284 | /** | |
| 285 | * Confirmed appointments whose requests list more than one distinct service. | |
| 286 | * Starts from buerger.status (indexed) and looks up buergeranliegen by BuergerID, | |
| 287 | * so the cost follows current confirmed bookings rather than the full request history. | |
| 288 | */ | |
| 289 | protected function readConfirmedWithMultipleServices(): int | |
| 290 | { | |
| 291 | $row = $this->getReader()->fetchOne( | |
| 292 | 'SELECT COUNT(*) AS confirmedWithMultipleServices | |
| 293 | FROM ( | |
| 294 | SELECT ba.BuergerID | |
| 295 | FROM buerger b | |
| 296 | INNER JOIN buergeranliegen ba ON ba.BuergerID = b.BuergerID | |
| 297 | WHERE b.status = "confirmed" | |
| 298 | AND (b.istFolgeterminvon IS NULL OR b.istFolgeterminvon = 0) | |
| 299 | AND ba.AnliegenID > 0 | |
| 300 | GROUP BY ba.BuergerID | |
| 301 | HAVING COUNT(DISTINCT ba.AnliegenID) > 1 | |
| 302 | ) AS multi' | |
| 303 | ); | |
| 304 | ||
| 305 | return (int) ($row['confirmedWithMultipleServices'] ?? 0); | |
| 306 | } | |
| 307 | ||
| 308 | /** | |
| 309 | * Fetch MySQL config variables | |
| 310 | * | |
| 311 | * @return array | |
| 312 | */ | |
| 313 | protected function readConfigVariables() | |
| 314 | { | |
| 315 | return $this->getReader()->fetchPairs('SHOW VARIABLES'); | |
| 316 | } | |
| 317 | ||
| 318 | /** | |
| 319 | * Fetch MySQL status variables | |
| 320 | * | |
| 321 | * @return array | |
| 322 | */ | |
| 323 | protected function readStatusVariables() | |
| 324 | { | |
| 325 | return $this->getReader()->fetchPairs('SHOW STATUS'); | |
| 326 | } | |
| 327 | } |