Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
65.00% |
169 / 260 |
|
38.89% |
7 / 18 |
CRAP | |
0.00% |
0 / 1 |
| IdDocsMapper | |
65.00% |
169 / 260 |
|
38.89% |
7 / 18 |
146.78 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| save | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getByUserIdAndFileId | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
2 | |||
| getByUserId | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
2 | |||
| getByUserIdAndNodeId | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
2 | |||
| getByNodeId | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
2 | |||
| getBySignRequestIdAndNodeId | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
2 | |||
| getByFileId | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
1 | |||
| deleteByFileId | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
2 | |||
| getByUserAndType | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
2 | |||
| list | |
100.00% |
18 / 18 |
|
100.00% |
1 / 1 |
2 | |||
| getQueryBuilder | |
76.19% |
64 / 84 |
|
0.00% |
0 / 1 |
30.14 | |||
| getDocs | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| formatListRow | |
100.00% |
34 / 34 |
|
100.00% |
1 / 1 |
1 | |||
| assocFileToSignRequestAndFormat | |
84.62% |
22 / 26 |
|
0.00% |
0 / 1 |
5.09 | |||
| getFilesOfAccount | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
6 | |||
| getFilesOfSignRequest | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
6 | |||
| getIdDocStatusText | |
66.67% |
2 / 3 |
|
0.00% |
0 / 1 |
1.04 | |||
| 1 | <?php |
| 2 | |
| 3 | declare(strict_types=1); |
| 4 | /** |
| 5 | * SPDX-FileCopyrightText: 2020-2024 LibreCode coop and contributors |
| 6 | * SPDX-License-Identifier: AGPL-3.0-or-later |
| 7 | */ |
| 8 | |
| 9 | namespace OCA\Libresign\Db; |
| 10 | |
| 11 | use OCA\Libresign\Enum\FileStatus; |
| 12 | use OCA\Libresign\Helper\Pagination; |
| 13 | use OCP\AppFramework\Db\QBMapper; |
| 14 | use OCP\DB\QueryBuilder\IQueryBuilder; |
| 15 | use OCP\IDBConnection; |
| 16 | use OCP\IL10N; |
| 17 | use OCP\IURLGenerator; |
| 18 | |
| 19 | /** |
| 20 | * Class FileMapper |
| 21 | * |
| 22 | * @package OCA\Libresign\DB |
| 23 | * @template-extends QBMapper<IdDocs> |
| 24 | */ |
| 25 | class IdDocsMapper extends QBMapper { |
| 26 | public function __construct( |
| 27 | IDBConnection $db, |
| 28 | private IURLGenerator $urlGenerator, |
| 29 | private FileMapper $fileMapper, |
| 30 | private SignRequestMapper $signRequestMapper, |
| 31 | private FileTypeMapper $fileTypeMapper, |
| 32 | private IL10N $l10n, |
| 33 | ) { |
| 34 | parent::__construct($db, 'libresign_id_docs'); |
| 35 | } |
| 36 | |
| 37 | public function save(int $fileId, ?int $signRequestId, ?string $userId, string $fileType): IdDocs { |
| 38 | $idDocs = new IdDocs(); |
| 39 | $idDocs->setFileId($fileId); |
| 40 | $idDocs->setSignRequestId($signRequestId); |
| 41 | $idDocs->setUserId($userId); |
| 42 | $idDocs->setFileType($fileType); |
| 43 | return $this->insert($idDocs); |
| 44 | } |
| 45 | |
| 46 | public function getByUserIdAndFileId(string $userId, int $fileId): IdDocs { |
| 47 | $qb = $this->db->getQueryBuilder(); |
| 48 | $qb->select('*') |
| 49 | ->from($this->getTableName()) |
| 50 | ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId))) |
| 51 | ->andWhere($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))); |
| 52 | return $this->findEntity($qb); |
| 53 | } |
| 54 | |
| 55 | /** |
| 56 | * @return IdDocs[] |
| 57 | */ |
| 58 | public function getByUserId(string $userId): array { |
| 59 | $qb = $this->db->getQueryBuilder(); |
| 60 | $qb->select('*') |
| 61 | ->from($this->getTableName()) |
| 62 | ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId))); |
| 63 | return $this->findEntities($qb); |
| 64 | } |
| 65 | |
| 66 | public function getByUserIdAndNodeId(string $userId, int $nodeId): IdDocs { |
| 67 | $qb = $this->db->getQueryBuilder(); |
| 68 | $qb->select('id.*') |
| 69 | ->from($this->getTableName(), 'id') |
| 70 | ->join('id', 'libresign_file', 'f', 'f.id = id.file_id') |
| 71 | ->where($qb->expr()->eq('id.user_id', $qb->createNamedParameter($userId))) |
| 72 | ->andWhere($qb->expr()->eq('f.node_id', $qb->createNamedParameter($nodeId, IQueryBuilder::PARAM_INT))); |
| 73 | return $this->findEntity($qb); |
| 74 | } |
| 75 | |
| 76 | public function getByNodeId(int $nodeId): IdDocs { |
| 77 | $qb = $this->db->getQueryBuilder(); |
| 78 | $qb->select('id.*') |
| 79 | ->from($this->getTableName(), 'id') |
| 80 | ->join('id', 'libresign_file', 'f', 'f.id = id.file_id') |
| 81 | ->where( |
| 82 | $qb->expr()->orX( |
| 83 | $qb->expr()->eq('f.node_id', $qb->createNamedParameter($nodeId, IQueryBuilder::PARAM_INT)), |
| 84 | $qb->expr()->eq('f.signed_node_id', $qb->createNamedParameter($nodeId, IQueryBuilder::PARAM_INT)) |
| 85 | ) |
| 86 | ); |
| 87 | return $this->findEntity($qb); |
| 88 | } |
| 89 | |
| 90 | public function getBySignRequestIdAndNodeId(int $signRequestId, int $nodeId): IdDocs { |
| 91 | $qb = $this->db->getQueryBuilder(); |
| 92 | $qb->select('id.*') |
| 93 | ->from($this->getTableName(), 'id') |
| 94 | ->join('id', 'libresign_file', 'f', 'f.id = id.file_id') |
| 95 | ->where($qb->expr()->eq('id.sign_request_id', $qb->createNamedParameter($signRequestId, IQueryBuilder::PARAM_INT))) |
| 96 | ->andWhere($qb->expr()->eq('f.node_id', $qb->createNamedParameter($nodeId, IQueryBuilder::PARAM_INT))); |
| 97 | return $this->findEntity($qb); |
| 98 | } |
| 99 | |
| 100 | public function getByFileId(int $fileId): IdDocs { |
| 101 | $qb = $this->db->getQueryBuilder(); |
| 102 | $qb->select('*') |
| 103 | ->from($this->getTableName()) |
| 104 | ->where($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))); |
| 105 | return $this->findEntity($qb); |
| 106 | } |
| 107 | |
| 108 | public function deleteByFileId(int $fileId): void { |
| 109 | $qb = $this->db->getQueryBuilder(); |
| 110 | $qb->delete($this->getTableName()) |
| 111 | ->where($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))); |
| 112 | $qb->executeStatement(); |
| 113 | } |
| 114 | |
| 115 | public function getByUserAndType(string $userId, string $fileType): ?IdDocs { |
| 116 | $qb = $this->db->getQueryBuilder(); |
| 117 | $qb->select('*') |
| 118 | ->from($this->getTableName()) |
| 119 | ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId))) |
| 120 | ->andWhere($qb->expr()->eq('file_type', $qb->createNamedParameter($fileType))); |
| 121 | try { |
| 122 | return $this->findEntity($qb); |
| 123 | } catch (\Throwable) { |
| 124 | return null; |
| 125 | } |
| 126 | } |
| 127 | |
| 128 | public function list(array $filter, ?int $page = null, ?int $length = null, array $sort = []): array { |
| 129 | $filter['length'] = $length; |
| 130 | $filter['page'] = $page; |
| 131 | $filter['sort'] = $sort; |
| 132 | $pagination = $this->getDocs($filter); |
| 133 | $pagination->setMaxPerPage($length); |
| 134 | $pagination->setCurrentPage($page); |
| 135 | $currentPageResults = $pagination->getCurrentPageResults(); |
| 136 | |
| 137 | $url = $this->urlGenerator->linkToRoute('libresign.page.getPdf', ['uuid' => '_replace_']); |
| 138 | $url = str_replace('_replace_', '', $url); |
| 139 | |
| 140 | $data = []; |
| 141 | $fileIds = []; |
| 142 | |
| 143 | foreach ($currentPageResults as $row) { |
| 144 | $fileIds[] = $row['id']; |
| 145 | $data[] = $this->formatListRow($row, $url); |
| 146 | } |
| 147 | $signers = $this->signRequestMapper->getByMultipleFileId($fileIds); |
| 148 | $return['data'] = $this->assocFileToSignRequestAndFormat($data, $signers); |
| 149 | $return['pagination'] = $pagination; |
| 150 | return $return; |
| 151 | } |
| 152 | |
| 153 | private function getQueryBuilder(array $filter = [], bool $count = false): IQueryBuilder { |
| 154 | $qb = $this->db->getQueryBuilder(); |
| 155 | |
| 156 | $needsUserJoin = !$count || !empty($filter['userId']) || (!empty($filter['sort']) && isset($filter['sort']['owner'])); |
| 157 | |
| 158 | if ($count) { |
| 159 | $qb->select($qb->func()->count()); |
| 160 | } else { |
| 161 | $qb->select( |
| 162 | 'f.id', |
| 163 | 'f.uuid', |
| 164 | 'f.name', |
| 165 | 'f.callback', |
| 166 | 'f.status', |
| 167 | 'f.node_id', |
| 168 | 'f.user_id', |
| 169 | 'id.file_type', |
| 170 | 'id.sign_request_id', |
| 171 | 'f.created_at', |
| 172 | ); |
| 173 | |
| 174 | $qb->selectAlias('sr.display_name', 'sign_request_display_name'); |
| 175 | |
| 176 | if ($needsUserJoin) { |
| 177 | $qb->selectAlias('u.uid_lower', 'account_uid') |
| 178 | ->selectAlias('u.displayname', 'account_displayname'); |
| 179 | } |
| 180 | |
| 181 | $qb->selectAlias( |
| 182 | $qb->createFunction( |
| 183 | 'CASE WHEN u.displayname IS NOT NULL THEN u.displayname ELSE sr.display_name END' |
| 184 | ), |
| 185 | 'owner_display_name' |
| 186 | ); |
| 187 | } |
| 188 | |
| 189 | $qb->from($this->getTableName(), 'id') |
| 190 | ->join('id', 'libresign_file', 'f', 'f.id = id.file_id') |
| 191 | ->leftJoin('id', 'libresign_sign_request', 'sr', 'id.sign_request_id = sr.id'); |
| 192 | |
| 193 | if ($needsUserJoin) { |
| 194 | $qb->leftJoin('id', 'users', 'u', 'id.user_id = u.uid'); |
| 195 | } |
| 196 | |
| 197 | if (!empty($filter['userId'])) { |
| 198 | $qb->where( |
| 199 | $qb->expr()->eq('id.user_id', $qb->createNamedParameter($filter['userId'])) |
| 200 | ); |
| 201 | } |
| 202 | |
| 203 | if (!empty($filter['signRequestId'])) { |
| 204 | $qb->andWhere( |
| 205 | $qb->expr()->eq('id.sign_request_id', $qb->createNamedParameter($filter['signRequestId'], IQueryBuilder::PARAM_INT)) |
| 206 | ); |
| 207 | } |
| 208 | |
| 209 | if (!empty($filter['approved']) && $filter['approved'] === 'yes') { |
| 210 | $qb->andWhere( |
| 211 | $qb->expr()->eq('f.status', $qb->createNamedParameter(FileStatus::SIGNED->value, IQueryBuilder::PARAM_INT)) |
| 212 | ); |
| 213 | } |
| 214 | |
| 215 | if (!$count) { |
| 216 | $qb->groupBy( |
| 217 | 'f.id', |
| 218 | 'f.uuid', |
| 219 | 'f.name', |
| 220 | 'f.callback', |
| 221 | 'f.status', |
| 222 | 'f.node_id', |
| 223 | 'f.user_id', |
| 224 | 'id.sign_request_id', |
| 225 | 'sr.display_name', |
| 226 | 'f.created_at', |
| 227 | 'id.file_type', |
| 228 | ); |
| 229 | |
| 230 | if ($needsUserJoin) { |
| 231 | $qb->addGroupBy('u.uid_lower') |
| 232 | ->addGroupBy('u.displayname'); |
| 233 | } |
| 234 | |
| 235 | $qb->addGroupBy('owner_display_name'); |
| 236 | } |
| 237 | |
| 238 | if (!$count) { |
| 239 | if (!empty($filter['sort'])) { |
| 240 | foreach ($filter['sort'] as $field => $direction) { |
| 241 | $direction = strtoupper((string)$direction) === 'ASC' ? 'ASC' : 'DESC'; |
| 242 | |
| 243 | switch ($field) { |
| 244 | case 'owner': |
| 245 | $qb->addOrderBy('owner_display_name', $direction); |
| 246 | break; |
| 247 | case 'file_type': |
| 248 | $qb->addOrderBy('id.file_type', $direction); |
| 249 | break; |
| 250 | case 'status': |
| 251 | $qb->addOrderBy('f.status', $direction); |
| 252 | break; |
| 253 | case 'created_at': |
| 254 | $qb->addOrderBy('f.created_at', $direction); |
| 255 | break; |
| 256 | } |
| 257 | } |
| 258 | } else { |
| 259 | $qb->orderBy('f.created_at', 'DESC'); |
| 260 | } |
| 261 | } |
| 262 | |
| 263 | if ($count) { |
| 264 | $qb->setFirstResult(0)->setMaxResults(null); |
| 265 | } elseif (isset($filter['length'], $filter['page'])) { |
| 266 | $qb->setFirstResult($filter['length'] * ($filter['page'] - 1)) |
| 267 | ->setMaxResults($filter['length']); |
| 268 | } |
| 269 | |
| 270 | return $qb; |
| 271 | } |
| 272 | |
| 273 | private function getDocs(array $filter = []): Pagination { |
| 274 | $qb = $this->getQueryBuilder( |
| 275 | filter: $filter, |
| 276 | ); |
| 277 | $countQb = $this->getQueryBuilder( |
| 278 | filter: $filter, |
| 279 | count: true, |
| 280 | ); |
| 281 | |
| 282 | $pagination = new Pagination($qb, $this->urlGenerator, $countQb); |
| 283 | return $pagination; |
| 284 | } |
| 285 | |
| 286 | private function formatListRow(array $row, string $url): array { |
| 287 | $createdAt = (new \DateTime()) |
| 288 | ->setTimestamp((int)$row['created_at']) |
| 289 | ->format('Y-m-d H:i:s'); |
| 290 | |
| 291 | $userId = $row['user_id'] ?? null; |
| 292 | $displayName = $row['account_displayname'] ?? $row['sign_request_display_name'] ?? $userId ?? ''; |
| 293 | |
| 294 | return [ |
| 295 | 'account' => [ |
| 296 | 'userId' => $userId, |
| 297 | 'displayName' => $displayName, |
| 298 | ], |
| 299 | 'file_type' => [ |
| 300 | 'type' => $row['file_type'], |
| 301 | 'name' => $this->fileTypeMapper->getNameOfType($row['file_type']), |
| 302 | 'description' => $this->fileTypeMapper->getDescriptionOfType($row['file_type']), |
| 303 | ], |
| 304 | 'created_at' => $createdAt, |
| 305 | 'file' => [ |
| 306 | 'name' => $row['name'], |
| 307 | 'status' => (int)$row['status'], |
| 308 | 'statusText' => $this->getIdDocStatusText((int)$row['status']), |
| 309 | 'created_at' => $createdAt, |
| 310 | 'user_id' => $row['user_id'], |
| 311 | 'file' => [ |
| 312 | 'type' => 'pdf', |
| 313 | 'nodeId' => (int)$row['node_id'], |
| 314 | 'signedNodeId' => (int)$row['node_id'], |
| 315 | 'url' => $url . $row['uuid'], |
| 316 | ], |
| 317 | 'callback' => $row['callback'], |
| 318 | 'uuid' => $row['uuid'], |
| 319 | 'signers' => [], |
| 320 | ], |
| 321 | 'id' => (int)$row['id'], |
| 322 | ]; |
| 323 | } |
| 324 | |
| 325 | /** |
| 326 | * @param array $files |
| 327 | * @param SignRequest[] $signers |
| 328 | */ |
| 329 | private function assocFileToSignRequestAndFormat(array $files, array $signers): array { |
| 330 | foreach ($files as $key => $file) { |
| 331 | $totalSigned = 0; |
| 332 | $files[$key]['file']['signers'] = []; |
| 333 | foreach ($signers as $signerKey => $signer) { |
| 334 | if ($signer->getFileId() === $file['id']) { |
| 335 | $data = [ |
| 336 | 'description' => $signer->getDescription(), |
| 337 | 'displayName' => $signer->getDisplayName(), |
| 338 | 'uid' => $file['file']['user_id'] ?? null, |
| 339 | 'request_sign_date' => (new \DateTime()) |
| 340 | ->setTimestamp($signer->getCreatedAt()->getTimestamp()) |
| 341 | ->format('Y-m-d H:i:s'), |
| 342 | 'sign_date' => null, |
| 343 | 'signRequestId' => $signer->getId(), |
| 344 | 'status' => $signer->getStatus(), |
| 345 | 'statusText' => $this->signRequestMapper->getTextOfSignerStatus($signer->getStatus()), |
| 346 | ]; |
| 347 | if ($signer->getSigned()) { |
| 348 | $data['sign_date'] = (new \DateTime()) |
| 349 | ->setTimestamp($signer->getSigned()->getTimestamp()) |
| 350 | ->format('Y-m-d H:i:s'); |
| 351 | $totalSigned++; |
| 352 | } |
| 353 | $files[$key]['file']['signers'][] = $data; |
| 354 | unset($signers[$signerKey]); |
| 355 | } |
| 356 | } |
| 357 | unset($files[$key]['id']); |
| 358 | } |
| 359 | return $files; |
| 360 | } |
| 361 | |
| 362 | /** |
| 363 | * Get all identification document files for a specific user account |
| 364 | * |
| 365 | * @param string $userId The user identifier |
| 366 | * @return File[] Array of File entities associated with the user's identification documents |
| 367 | */ |
| 368 | public function getFilesOfAccount(string $userId): array { |
| 369 | $qb = $this->db->getQueryBuilder(); |
| 370 | |
| 371 | $qb->select('lf.*') |
| 372 | ->from('libresign_file', 'lf') |
| 373 | ->join('lf', $this->getTableName(), 'lid', 'lid.file_id = lf.id') |
| 374 | ->where( |
| 375 | $qb->expr()->eq('lid.user_id', $qb->createNamedParameter($userId)) |
| 376 | ); |
| 377 | |
| 378 | $cursor = $qb->executeQuery(); |
| 379 | $return = []; |
| 380 | while ($row = $cursor->fetch()) { |
| 381 | /** @var File */ |
| 382 | $file = $this->fileMapper->mapRowToEntity($row); |
| 383 | $return[] = $file; |
| 384 | } |
| 385 | return $return; |
| 386 | } |
| 387 | |
| 388 | /** |
| 389 | * Get all identification document files for a specific sign request |
| 390 | * |
| 391 | * @param int $signRequestId The sign request identifier |
| 392 | * @return File[] Array of File entities associated with the sign request's identification documents |
| 393 | */ |
| 394 | public function getFilesOfSignRequest(int $signRequestId): array { |
| 395 | $qb = $this->db->getQueryBuilder(); |
| 396 | |
| 397 | $qb->select('lf.*') |
| 398 | ->from('libresign_file', 'lf') |
| 399 | ->join('lf', $this->getTableName(), 'lid', 'lid.file_id = lf.id') |
| 400 | ->where( |
| 401 | $qb->expr()->eq('lid.sign_request_id', $qb->createNamedParameter($signRequestId, IQueryBuilder::PARAM_INT)) |
| 402 | ); |
| 403 | |
| 404 | $cursor = $qb->executeQuery(); |
| 405 | $return = []; |
| 406 | while ($row = $cursor->fetch()) { |
| 407 | /** @var File */ |
| 408 | $file = $this->fileMapper->mapRowToEntity($row); |
| 409 | $return[] = $file; |
| 410 | } |
| 411 | return $return; |
| 412 | } |
| 413 | |
| 414 | private function getIdDocStatusText(int $status): string { |
| 415 | return match ($status) { |
| 416 | // TRANSLATORS Status label for an identity document that a signer uploaded and that is waiting for an authorized reviewer to approve. |
| 417 | FileStatus::ABLE_TO_SIGN->value => $this->l10n->t('waiting for approval'), |
| 418 | // TRANSLATORS Status label for an identity document that was approved and can unlock signing when identity documents are required. |
| 419 | FileStatus::SIGNED->value => $this->l10n->t('approved'), |
| 420 | default => $this->fileMapper->getTextOfStatus($status), |
| 421 | }; |
| 422 | } |
| 423 | } |