Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
80.21% |
231 / 288 |
|
62.50% |
15 / 24 |
CRAP | |
0.00% |
0 / 1 |
| AdminDAO | |
80.21% |
231 / 288 |
|
62.50% |
15 / 24 |
71.16 | |
0.00% |
0 / 1 |
| refreshAdminToken | n/a |
0 / 0 |
n/a |
0 / 0 |
1 | |||||
| createAdminToken | |
87.50% |
7 / 8 |
|
0.00% |
0 / 1 |
4.03 | |||
| getUserByNameAndPassword | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
5 | |||
| deleteTokensByUser | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| storeToken | |
100.00% |
10 / 10 |
|
100.00% |
1 / 1 |
1 | |||
| getAdmin | |
93.75% |
15 / 16 |
|
0.00% |
0 / 1 |
2.00 | |||
| deleteResultData | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
1 | |||
| getWorkspaces | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
1 | |||
| hasAdminAccessToWorkspace | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| hasMonitorAccessToWorkspace | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
2 | |||
| getWorkspaceRole | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| getTestSessions | |
100.00% |
49 / 49 |
|
100.00% |
1 / 1 |
6 | |||
| getUnitState | |
90.91% |
10 / 11 |
|
0.00% |
0 / 1 |
2.00 | |||
| getResponseReportData | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
2 | |||
| getResponseDataParts | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
| getLogReportData | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| getReviewReportData | |
100.00% |
10 / 10 |
|
100.00% |
1 / 1 |
1 | |||
| getResultStats | |
100.00% |
19 / 19 |
|
100.00% |
1 / 1 |
1 | |||
| storeCommand | |
93.75% |
15 / 16 |
|
0.00% |
0 / 1 |
3.00 | |||
| getTest | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| getGroup | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
2 | |||
| getAttachmentById | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| getAttachments | |
0.00% |
0 / 38 |
|
0.00% |
0 / 1 |
56 | |||
| deleteAdminSession | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| doesWSwitTypeSyscheckExist | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| 1 | <?php |
| 2 | |
| 3 | /** @noinspection PhpUnhandledExceptionInspection */ |
| 4 | declare(strict_types=1); |
| 5 | |
| 6 | class AdminDAO extends DAO { |
| 7 | /** |
| 8 | * @codeCoverageIgnore |
| 9 | */ |
| 10 | public function refreshAdminToken(string $token): void { |
| 11 | $this->_( |
| 12 | 'update admin_sessions |
| 13 | set valid_until =:value |
| 14 | where token =:token', |
| 15 | [ |
| 16 | ':value' => TimeStamp::toSQLFormat(TimeStamp::expirationFromNow(0, $this->timeUserIsAllowedInMinutes)), |
| 17 | ':token' => $token |
| 18 | ] |
| 19 | ); |
| 20 | } |
| 21 | |
| 22 | public function createAdminToken(string $username, string $password, ?int $validTo = null): string | FailedLogin { |
| 23 | if ((strlen($username) == 0) or (strlen($username) > 50)) { |
| 24 | throw new Exception("Invalid Username `$username`", 400); |
| 25 | } |
| 26 | |
| 27 | $user = $this->getUserByNameAndPassword($username, $password); |
| 28 | |
| 29 | if (is_a($user, FailedLogin::class)) return $user; |
| 30 | |
| 31 | $this->deleteTokensByUser((int) $user['id']); |
| 32 | $token = Token::generate('admin', $username); |
| 33 | $this->storeToken((int) $user['id'], $token, $validTo); |
| 34 | |
| 35 | return $token; |
| 36 | } |
| 37 | |
| 38 | private function getUserByNameAndPassword(string $userName, string $password): array | FailedLogin { |
| 39 | $usersOfThisName = $this->_( |
| 40 | 'select * from users where users.name = :name', |
| 41 | [':name' => $userName], |
| 42 | true |
| 43 | ); |
| 44 | |
| 45 | $return = (!count($usersOfThisName)) ? FailedLogin::usernameNotFound : FailedLogin::wrongPassword; |
| 46 | |
| 47 | // we always check at least one user to not leak the existence of username to time-attacks |
| 48 | $usersOfThisName = (!count($usersOfThisName)) ? [['password' => 'dummy']] : $usersOfThisName; |
| 49 | |
| 50 | foreach ($usersOfThisName as $user) { |
| 51 | if (Password::verify($password, $user['password'], $this->passwordSalt)) { |
| 52 | return $user; |
| 53 | } |
| 54 | } |
| 55 | |
| 56 | // obfuscate the time taken even more |
| 57 | usleep(rand(000000, 100000)); |
| 58 | return $return; |
| 59 | } |
| 60 | |
| 61 | private function deleteTokensByUser(int $userId): void { |
| 62 | $this->_( |
| 63 | 'delete from admin_sessions where admin_sessions.user_id = :id', |
| 64 | [':id' => $userId] |
| 65 | ); |
| 66 | } |
| 67 | |
| 68 | private function storeToken(int $userId, string $token, ?int $validTo = null): void { |
| 69 | $validTo = $validTo ?? TimeStamp::expirationFromNow(0, $this->timeUserIsAllowedInMinutes); |
| 70 | |
| 71 | $this->_( |
| 72 | 'insert into admin_sessions (token, user_id, valid_until) |
| 73 | values(:token, :user_id, :valid_until)', |
| 74 | [ |
| 75 | ':token' => $token, |
| 76 | ':user_id' => $userId, |
| 77 | ':valid_until' => TimeStamp::toSQLFormat($validTo) |
| 78 | ] |
| 79 | ); |
| 80 | } |
| 81 | |
| 82 | public function getAdmin(string $token): Admin { |
| 83 | $admin = $this->_( |
| 84 | 'select |
| 85 | users.id, |
| 86 | users.name, |
| 87 | users.email, |
| 88 | users.is_superadmin, |
| 89 | users.pw_set_by_admin, |
| 90 | admin_sessions.valid_until |
| 91 | from users |
| 92 | inner join admin_sessions on users.id = admin_sessions.user_id |
| 93 | where admin_sessions.token=:token', |
| 94 | [':token' => $token] |
| 95 | ); |
| 96 | |
| 97 | if (!$admin) { |
| 98 | throw new HttpError("Token not valid! ($token)", 403); |
| 99 | } |
| 100 | |
| 101 | TimeStamp::checkExpiration(0, TimeStamp::fromSQLFormat($admin['valid_until'])); |
| 102 | |
| 103 | return new Admin( |
| 104 | $admin['id'], |
| 105 | $admin['name'], |
| 106 | $admin['email'] ?? '', |
| 107 | !!$admin['is_superadmin'], |
| 108 | $token, |
| 109 | (bool) $admin['pw_set_by_admin'] |
| 110 | ); |
| 111 | } |
| 112 | |
| 113 | public function deleteResultData(int $workspaceId, string $groupName): void { |
| 114 | $this->_( |
| 115 | "delete from login_sessions where group_name = :group_name and workspace_id = :workspace_id", |
| 116 | [ |
| 117 | ':workspace_id' => $workspaceId, |
| 118 | ':group_name' => $groupName |
| 119 | ] |
| 120 | ); |
| 121 | } |
| 122 | |
| 123 | /** @return WorkspaceData[] */ |
| 124 | public function getWorkspaces(string $token): array { |
| 125 | $workspaces = $this->_( |
| 126 | 'select |
| 127 | workspaces.id, |
| 128 | workspaces.name, |
| 129 | workspace_users.role |
| 130 | from workspaces |
| 131 | inner join workspace_users on workspaces.id = workspace_users.workspace_id |
| 132 | inner join users on workspace_users.user_id = users.id |
| 133 | inner join admin_sessions on users.id = admin_sessions.user_id |
| 134 | where |
| 135 | admin_sessions.token =:token', |
| 136 | [':token' => $token], |
| 137 | true |
| 138 | ); |
| 139 | return array_map( |
| 140 | function (array $ws): WorkspaceData { |
| 141 | return new WorkspaceData($ws['id'], $ws['name'], $ws['role']); |
| 142 | }, |
| 143 | $workspaces |
| 144 | ); |
| 145 | } |
| 146 | |
| 147 | public function hasAdminAccessToWorkspace(string $token, int $workspaceId): bool { |
| 148 | $data = $this->_( |
| 149 | 'select workspaces.id from workspaces |
| 150 | inner join workspace_users on workspaces.id = workspace_users.workspace_id |
| 151 | inner join users on workspace_users.user_id = users.id |
| 152 | inner join admin_sessions on users.id = admin_sessions.user_id |
| 153 | where admin_sessions.token =:token and workspaces.id = :wsId', |
| 154 | [ |
| 155 | ':token' => $token, |
| 156 | ':wsId' => $workspaceId |
| 157 | ] |
| 158 | ); |
| 159 | |
| 160 | return $data != false; |
| 161 | } |
| 162 | |
| 163 | public function hasMonitorAccessToWorkspace(string $token, int $workspaceId): bool { |
| 164 | $data = $this->_( |
| 165 | 'select workspaces.id from workspaces |
| 166 | inner join login_sessions on workspaces.id = login_sessions.workspace_id |
| 167 | inner join person_sessions on person_sessions.login_sessions_id = login_sessions.id |
| 168 | where person_sessions.token =:token and workspaces.id = :wsId', |
| 169 | [ |
| 170 | ':token' => $token, |
| 171 | ':wsId' => $workspaceId |
| 172 | ] |
| 173 | ); |
| 174 | |
| 175 | return $data != false; |
| 176 | } |
| 177 | |
| 178 | public function getWorkspaceRole(string $token, int $workspaceId): string { |
| 179 | $user = $this->_( |
| 180 | 'select workspace_users.role from workspaces |
| 181 | inner join workspace_users on workspaces.id = workspace_users.workspace_id |
| 182 | inner join users on workspace_users.user_id = users.id |
| 183 | inner join admin_sessions on users.id = admin_sessions.user_id |
| 184 | where admin_sessions.token =:token and workspaces.id = :wsId', |
| 185 | [ |
| 186 | ':token' => $token, |
| 187 | ':wsId' => $workspaceId |
| 188 | ] |
| 189 | ); |
| 190 | |
| 191 | return $user['role'] ?? ''; |
| 192 | } |
| 193 | |
| 194 | public function getTestSessions(int $workspaceId, array $groups): SessionChangeMessageArray { |
| 195 | $groupSelector = false; |
| 196 | if (count($groups)) { |
| 197 | $groupSelector = "'" . implode("', '", $groups) . "'"; |
| 198 | } |
| 199 | |
| 200 | $modeSelector = "'" . implode("', '", Mode::getByCapability('monitorable')) . "'"; |
| 201 | |
| 202 | $sql = 'SELECT |
| 203 | person_sessions.id as "person_id", |
| 204 | login_sessions.name as "loginName", |
| 205 | login_sessions.id as "login_sessions_id", |
| 206 | logins.name, |
| 207 | logins.mode, |
| 208 | logins.group_name, |
| 209 | logins.group_label, |
| 210 | logins.workspace_id, |
| 211 | person_sessions.code, |
| 212 | person_sessions.name_suffix, |
| 213 | tests.id as "test_id", |
| 214 | tests.name as "booklet_name", |
| 215 | tests.locked, |
| 216 | tests.running, |
| 217 | tests.laststate as "testState", |
| 218 | tests.timestamp_server as "test_timestamp_server" |
| 219 | FROM person_sessions |
| 220 | LEFT JOIN tests ON person_sessions.id = tests.person_id |
| 221 | LEFT JOIN login_sessions ON login_sessions.id = person_sessions.login_sessions_id |
| 222 | LEFt JOIN logins on logins.name = login_sessions.name |
| 223 | WHERE |
| 224 | login_sessions.workspace_id = :workspaceId |
| 225 | AND tests.id is not null' |
| 226 | . ($groupSelector ? " AND logins.group_name IN ($groupSelector)" : '') |
| 227 | . " AND logins.mode IN ($modeSelector)"; |
| 228 | |
| 229 | $testSessionsData = $this->_($sql, [':workspaceId' => $workspaceId], true); |
| 230 | |
| 231 | $sessionChangeMessages = new SessionChangeMessageArray(); |
| 232 | |
| 233 | foreach ($testSessionsData as $testSession) { |
| 234 | $testState = $this->getTestFullState($testSession); |
| 235 | |
| 236 | $sessionChangeMessage = SessionChangeMessage::session( |
| 237 | (int) $testSession['test_id'], |
| 238 | new PersonSession( |
| 239 | new LoginSession( |
| 240 | (int) $testSession['login_sessions_id'], |
| 241 | '', |
| 242 | '', |
| 243 | new Login( |
| 244 | $testSession['name'], |
| 245 | '', |
| 246 | $testSession['mode'], |
| 247 | $testSession['group_name'], |
| 248 | $testSession['group_label'], |
| 249 | [], |
| 250 | (int) ['workspace_id'] |
| 251 | ) |
| 252 | ), |
| 253 | new Person( |
| 254 | (int) $testSession['person_id'], |
| 255 | '', |
| 256 | $testSession['code'], |
| 257 | (string) $testSession['name_suffix'], |
| 258 | ) |
| 259 | ), |
| 260 | TimeStamp::fromSQLFormat($testSession['test_timestamp_server']), |
| 261 | ); |
| 262 | $sessionChangeMessage->setTestState( |
| 263 | $testState, |
| 264 | $testSession['booklet_name'] ?? "" |
| 265 | ); |
| 266 | |
| 267 | $currentUnitName = $testState['CURRENT_UNIT_ID'] ?? null; |
| 268 | |
| 269 | if ($currentUnitName) { |
| 270 | $currentUnitState = $this->getUnitState((int) $testSession['test_id'], $currentUnitName); |
| 271 | |
| 272 | if ($currentUnitState) { |
| 273 | $sessionChangeMessage->setUnitState($currentUnitName, (array) $currentUnitState); |
| 274 | } |
| 275 | } |
| 276 | |
| 277 | $sessionChangeMessages->add($sessionChangeMessage); |
| 278 | } |
| 279 | |
| 280 | return $sessionChangeMessages; |
| 281 | } |
| 282 | |
| 283 | private function getUnitState(int $testId, string $unitName): stdClass { |
| 284 | $unitData = $this->_(" |
| 285 | select |
| 286 | laststate |
| 287 | from |
| 288 | units |
| 289 | where |
| 290 | units.booklet_id = :testId |
| 291 | and units.name = :unitName", |
| 292 | [ |
| 293 | ':testId' => $testId, |
| 294 | ':unitName' => $unitName |
| 295 | ] |
| 296 | ); |
| 297 | |
| 298 | if (!$unitData) { |
| 299 | return (object) []; |
| 300 | } |
| 301 | |
| 302 | $state = JSON::decode($unitData['laststate'], true) ?? (object) []; |
| 303 | |
| 304 | return (object) $state ?? (object) []; |
| 305 | } |
| 306 | |
| 307 | public function getResponseReportData($workspaceId, $groups): ?array { |
| 308 | $groupsPlaceholders = implode(',', array_fill(0, count($groups), '?')); |
| 309 | $bindParams = array_merge([$workspaceId], $groups); |
| 310 | |
| 311 | // TODO: use data class |
| 312 | $data = $this->_(<<<EOT |
| 313 | select |
| 314 | login_sessions.group_name as groupname, |
| 315 | login_sessions.name as loginname, |
| 316 | person_sessions.name_suffix as code, |
| 317 | tests.name as bookletname, |
| 318 | units.name as unitname, |
| 319 | units.laststate, |
| 320 | units.id as unit_id, |
| 321 | units.original_unit_id as originalUnitId |
| 322 | from |
| 323 | login_sessions |
| 324 | inner join person_sessions on login_sessions.id = person_sessions.login_sessions_id |
| 325 | inner join tests on person_sessions.id = tests.person_id |
| 326 | inner join units on tests.id = units.booklet_id |
| 327 | where |
| 328 | login_sessions.workspace_id = ? |
| 329 | and login_sessions.group_name in ($groupsPlaceholders) |
| 330 | and tests.id is not null |
| 331 | EOT, |
| 332 | $bindParams, |
| 333 | true |
| 334 | ); |
| 335 | |
| 336 | foreach ($data as $index => $row) { |
| 337 | $data[$index]['responses'] = $this->getResponseDataParts((int) $row['unit_id']); |
| 338 | unset($data[$index]['unit_id']); |
| 339 | } |
| 340 | |
| 341 | return $data; |
| 342 | } |
| 343 | |
| 344 | public function getResponseDataParts(int $unitId): array { |
| 345 | $data = $this->_( |
| 346 | 'select |
| 347 | part_id as id, |
| 348 | content, |
| 349 | ts, |
| 350 | response_type as responseType |
| 351 | from |
| 352 | unit_data |
| 353 | where |
| 354 | unit_id = :unit_id', |
| 355 | [':unit_id' => $unitId], |
| 356 | true |
| 357 | ); |
| 358 | foreach ($data as $index => $row) { |
| 359 | $data[$index]['ts'] = (int) $row['ts']; |
| 360 | } |
| 361 | return $data; |
| 362 | } |
| 363 | |
| 364 | public function getLogReportData($workspaceId, $groups): ?array { |
| 365 | $groupsPlaceholders = implode(',', array_fill(0, count($groups), '?')); |
| 366 | $bindParams = array_merge([$workspaceId], $groups, [$workspaceId], $groups); |
| 367 | |
| 368 | // TODO: use data class |
| 369 | return $this->_(" |
| 370 | SELECT |
| 371 | login_sessions.group_name as groupname, |
| 372 | login_sessions.name as loginname, |
| 373 | person_sessions.name_suffix as code, |
| 374 | tests.name as bookletname, |
| 375 | units.name as unitname, |
| 376 | units.original_unit_id as originalUnitId, |
| 377 | unit_logs.timestamp, |
| 378 | unit_logs.logentry |
| 379 | FROM |
| 380 | login_sessions, |
| 381 | person_sessions, |
| 382 | tests, |
| 383 | units, |
| 384 | unit_logs |
| 385 | WHERE |
| 386 | login_sessions.workspace_id = ? AND |
| 387 | login_sessions.group_name IN ($groupsPlaceholders) AND |
| 388 | login_sessions.id = person_sessions.login_sessions_id AND |
| 389 | person_sessions.id = tests.person_id AND |
| 390 | tests.id = units.booklet_id AND |
| 391 | units.id = unit_logs.unit_id |
| 392 | |
| 393 | UNION ALL |
| 394 | |
| 395 | SELECT |
| 396 | login_sessions.group_name as groupname, |
| 397 | login_sessions.name as loginname, |
| 398 | person_sessions.name_suffix as code, |
| 399 | tests.name as bookletname, |
| 400 | '' as unitname, |
| 401 | '' as originalUnitId, |
| 402 | test_logs.timestamp, |
| 403 | test_logs.logentry |
| 404 | FROM |
| 405 | login_sessions, |
| 406 | person_sessions, |
| 407 | tests, |
| 408 | test_logs |
| 409 | WHERE |
| 410 | login_sessions.workspace_id = ? AND |
| 411 | login_sessions.group_name IN ($groupsPlaceholders) AND |
| 412 | login_sessions.id = person_sessions.login_sessions_id AND |
| 413 | person_sessions.id = tests.person_id AND |
| 414 | tests.id = test_logs.booklet_id |
| 415 | ", |
| 416 | $bindParams, |
| 417 | true |
| 418 | ); |
| 419 | } |
| 420 | |
| 421 | public function getReviewReportData($workspaceId, $groups): ?array { |
| 422 | $groupsPlaceholders = implode(',', array_fill(0, count($groups), '?')); |
| 423 | $bindParams = array_merge([$workspaceId], $groups, [$workspaceId], $groups); |
| 424 | |
| 425 | // TODO: use data class |
| 426 | return $this->_( |
| 427 | " |
| 428 | SELECT |
| 429 | login_sessions.group_name as groupname, |
| 430 | login_sessions.name as loginname, |
| 431 | person_sessions.name_suffix as code, |
| 432 | tests.name as bookletname, |
| 433 | units.name as unitname, |
| 434 | unit_reviews.priority, |
| 435 | unit_reviews.categories, |
| 436 | unit_reviews.reviewtime, |
| 437 | unit_reviews.entry, |
| 438 | unit_reviews.page, |
| 439 | unit_reviews.pagelabel, |
| 440 | units.original_unit_id as originalUnitId, |
| 441 | unit_reviews.user_agent as userAgent |
| 442 | FROM |
| 443 | login_sessions, |
| 444 | person_sessions, |
| 445 | tests, |
| 446 | units, |
| 447 | unit_reviews |
| 448 | WHERE |
| 449 | login_sessions.workspace_id = ? AND |
| 450 | login_sessions.group_name IN ($groupsPlaceholders) AND |
| 451 | login_sessions.id = person_sessions.login_sessions_id AND |
| 452 | person_sessions.id = tests.person_id AND |
| 453 | tests.id = units.booklet_id AND |
| 454 | units.id = unit_reviews.unit_id |
| 455 | |
| 456 | UNION ALL |
| 457 | |
| 458 | SELECT |
| 459 | login_sessions.group_name as groupname, |
| 460 | login_sessions.name as loginname, |
| 461 | person_sessions.name_suffix as code, |
| 462 | tests.name as bookletname, |
| 463 | '' as unitname, |
| 464 | test_reviews.priority, |
| 465 | test_reviews.categories, |
| 466 | test_reviews.reviewtime, |
| 467 | test_reviews.entry, |
| 468 | null as page, |
| 469 | null as pagelabel, |
| 470 | '' as originalUnitId, |
| 471 | test_reviews.user_agent as userAgent |
| 472 | FROM |
| 473 | login_sessions, |
| 474 | person_sessions, |
| 475 | tests, |
| 476 | test_reviews |
| 477 | WHERE |
| 478 | login_sessions.workspace_id = ? AND |
| 479 | login_sessions.group_name IN ($groupsPlaceholders) AND |
| 480 | login_sessions.id = person_sessions.login_sessions_id AND |
| 481 | person_sessions.id = tests.person_id AND |
| 482 | tests.id = test_reviews.booklet_id |
| 483 | ", |
| 484 | $bindParams, |
| 485 | true |
| 486 | ); |
| 487 | } |
| 488 | |
| 489 | public function getResultStats(int $workspaceId): array { |
| 490 | $resultStats = $this->_(' |
| 491 | select |
| 492 | group_name, |
| 493 | group_label, |
| 494 | count(*) as bookletsStarted, |
| 495 | min(num_units) as num_units_min, |
| 496 | max(num_units) as num_units_max, |
| 497 | sum(num_units) as num_units_total, |
| 498 | avg(num_units) as num_units_mean, |
| 499 | max(timestamp_server) as lastchange |
| 500 | from ( |
| 501 | select |
| 502 | login_sessions.group_name, |
| 503 | group_label, |
| 504 | count(distinct units.id) as num_units, |
| 505 | max(tests.timestamp_server) as timestamp_server |
| 506 | from |
| 507 | tests |
| 508 | left join person_sessions |
| 509 | on person_sessions.id = tests.person_id |
| 510 | inner join login_sessions |
| 511 | on login_sessions.id = person_sessions.login_sessions_id |
| 512 | left join units |
| 513 | on units.booklet_id = tests.id |
| 514 | left join unit_reviews |
| 515 | on units.id = unit_reviews.unit_id |
| 516 | left join test_reviews |
| 517 | on tests.id = test_reviews.booklet_id |
| 518 | left join login_session_groups on |
| 519 | login_sessions.group_name = login_session_groups.group_name |
| 520 | and login_sessions.workspace_id = login_session_groups.workspace_id |
| 521 | where |
| 522 | login_sessions.workspace_id = :workspaceId |
| 523 | and ( |
| 524 | tests.laststate is not null |
| 525 | or unit_reviews.entry is not null |
| 526 | or test_reviews.entry is not null |
| 527 | ) |
| 528 | and tests.running = 1 |
| 529 | group by tests.name, person_sessions.id, login_sessions.group_name, group_label |
| 530 | ) as byGroup |
| 531 | group by group_name', |
| 532 | [ |
| 533 | ':workspaceId' => $workspaceId |
| 534 | ], |
| 535 | true |
| 536 | ); |
| 537 | |
| 538 | return array_map(function ($groupStats) { |
| 539 | return [ |
| 540 | "groupName" => $groupStats["group_name"], |
| 541 | "groupLabel" => $groupStats["group_label"], |
| 542 | "bookletsStarted" => (int) $groupStats["bookletsStarted"], |
| 543 | "numUnitsMin" => (int) $groupStats["num_units_min"], |
| 544 | "numUnitsMax" => (int) $groupStats["num_units_max"], |
| 545 | "numUnitsTotal" => (int) $groupStats["num_units_total"], |
| 546 | "numUnitsAvg" => (float) $groupStats["num_units_mean"], |
| 547 | "lastChange" => TimeStamp::fromSQLFormat((string) $groupStats["lastchange"]) |
| 548 | ]; |
| 549 | }, $resultStats); |
| 550 | } |
| 551 | |
| 552 | public function storeCommand(int $commanderId, int $testId, Command $command): int { |
| 553 | if ($command->getId() === -1) { |
| 554 | $maxId = $this->_("select max(id) as max from test_commands"); |
| 555 | $commandId = isset($maxId['max']) ? (int) $maxId['max'] + 1 : 1; |
| 556 | } else { |
| 557 | $commandId = $command->getId(); |
| 558 | } |
| 559 | |
| 560 | $this->_("insert into test_commands (id, test_id, keyword, parameter, commander_id, timestamp) |
| 561 | values (:id, :test_id, :keyword, :parameter, :commander_id, :timestamp)", |
| 562 | [ |
| 563 | ':id' => $commandId, |
| 564 | ':test_id' => $testId, |
| 565 | ':keyword' => $command->getKeyword(), |
| 566 | ':parameter' => json_encode($command->getArguments()), |
| 567 | ':commander_id' => $commanderId, |
| 568 | ':timestamp' => TimeStamp::toSQLFormat($command->getTimestamp()) |
| 569 | ] |
| 570 | ); |
| 571 | |
| 572 | return $commandId; |
| 573 | } |
| 574 | |
| 575 | // TODO use typed class instead of array |
| 576 | public function getTest(int $testId): ?array { |
| 577 | return $this->_( |
| 578 | 'select tests.locked, tests.id, tests.laststate, tests.label from tests where tests.id=:id', |
| 579 | [':id' => $testId] |
| 580 | ); |
| 581 | } |
| 582 | |
| 583 | public function getGroup(string $groupName): ?Group { |
| 584 | $group = $this->_( |
| 585 | 'select group_name, group_label |
| 586 | from logins |
| 587 | where group_name=:group_name |
| 588 | group by group_name, group_label', |
| 589 | [ |
| 590 | ":group_name" => $groupName |
| 591 | ] |
| 592 | ); |
| 593 | return ($group == null) ? null : new Group($group['group_name'], $group['group_label']); |
| 594 | } |
| 595 | |
| 596 | // TODO unit-test |
| 597 | public function getAttachmentById(string $attachmentId): Attachment { |
| 598 | $attachments = $this->getAttachments(0, [], $attachmentId); |
| 599 | |
| 600 | if (!count($attachments)) { |
| 601 | throw new HttpError("Attachment not found: `$attachmentId`", 404); |
| 602 | } |
| 603 | |
| 604 | return $attachments[0]; |
| 605 | } |
| 606 | |
| 607 | // TODO unit-test |
| 608 | public function getAttachments(int $workspaceId = 0, array $groups = [], string $attachmentId = ''): array { |
| 609 | $selectors = []; |
| 610 | $replacements = []; |
| 611 | |
| 612 | if (count($groups)) { |
| 613 | $selectors[] = "logins.group_name in (" . implode(',', array_fill(0, count($groups), '? ')) . ")"; |
| 614 | $replacements = $groups; |
| 615 | } |
| 616 | |
| 617 | if ($workspaceId) { |
| 618 | $selectors[] = "logins.workspace_id = ?"; |
| 619 | $replacements[] = $workspaceId; |
| 620 | } |
| 621 | |
| 622 | if ($attachmentId) { |
| 623 | list($testId, $unitName, $variableId) = Attachment::decodeId($attachmentId); |
| 624 | $selectors[] = "tests.id = ?"; |
| 625 | $selectors[] = "unit_name = ?"; |
| 626 | $selectors[] = "variable_id = ?"; |
| 627 | $replacements[] = $testId; |
| 628 | $replacements[] = $unitName; |
| 629 | $replacements[] = $variableId; |
| 630 | } |
| 631 | |
| 632 | $sql = "select |
| 633 | group_label as groupLabel, |
| 634 | logins.group_name as groupName, |
| 635 | logins.name as loginName, |
| 636 | name_suffix as nameSuffix, |
| 637 | tests.label as testLabel, |
| 638 | tests.id as testId, |
| 639 | tests.name as bookletName, |
| 640 | unit_name as unitName, |
| 641 | unit_name as unitLabel, -- TODO get real unitLabel |
| 642 | variable_id as variableId, |
| 643 | attachment_type as attachmentType, |
| 644 | unit_data.content as dataPartContent, |
| 645 | (tests.id || ':' || unit_name || ':' || variable_id) as attachmentId, |
| 646 | unit_data.ts as lastModified |
| 647 | from |
| 648 | unit_defs_attachments |
| 649 | left join tests on booklet_name = tests.name |
| 650 | left join person_sessions on tests.person_id = person_sessions.id |
| 651 | left join login_sessions on person_sessions.login_sessions_id = login_sessions.id |
| 652 | left join logins on logins.name = login_sessions.name |
| 653 | left join unit_data on part_id = (tests.id || ':' || unit_name || ':' || variable_id) |
| 654 | where " . implode(' and ', $selectors); |
| 655 | |
| 656 | $attachments = $this->_($sql, $replacements, true); |
| 657 | |
| 658 | $attachmentData = []; |
| 659 | foreach ($attachments as $attachment) { |
| 660 | $dataPart = JSON::decode($attachment['dataPartContent'], true); |
| 661 | $attachmentFileIds = $dataPart ? $dataPart[0]['value'] : []; |
| 662 | |
| 663 | $attachmentData[] = new Attachment( |
| 664 | $attachment['attachmentId'], |
| 665 | $attachment['attachmentType'], |
| 666 | $attachment['dataPartContent'] ? explode(':', $attachmentFileIds[0])[0] : 'missing', |
| 667 | $attachmentFileIds, |
| 668 | $attachment['lastModified'], |
| 669 | $attachment['groupName'], |
| 670 | $attachment['groupLabel'], |
| 671 | $attachment['loginName'], |
| 672 | $attachment['nameSuffix'], |
| 673 | $attachment['testLabel'], |
| 674 | $attachment['bookletName'], |
| 675 | $attachment['unitLabel'] |
| 676 | ); |
| 677 | } |
| 678 | return $attachmentData; |
| 679 | } |
| 680 | |
| 681 | public function deleteAdminSession(AuthToken $authToken): void { |
| 682 | $this->_('delete from admin_sessions where token =:token', [':token' => $authToken->getToken()]); |
| 683 | } |
| 684 | |
| 685 | public function doesWSwitTypeSyscheckExist(): bool { |
| 686 | return $this->_("select count(*) as count from workspaces where content_type = 'sysCheck'")['count'] > 0; |
| 687 | } |
| 688 | } |