Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
60.04% |
308 / 513 |
|
38.71% |
12 / 31 |
CRAP | |
0.00% |
0 / 1 |
| SignRequestMapper | |
60.04% |
308 / 513 |
|
38.71% |
12 / 31 |
724.43 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| update | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
4 | |||
| cacheEntity | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| incrementNotificationCounter | |
95.45% |
21 / 22 |
|
0.00% |
0 / 1 |
6 | |||
| deleteIfUnsigned | |
93.33% |
14 / 15 |
|
0.00% |
0 / 1 |
4.00 | |||
| getByUuid | |
84.62% |
11 / 13 |
|
0.00% |
0 / 1 |
5.09 | |||
| getByUuidUncached | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
2 | |||
| getByEmailAndFileId | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
| getByIdentifyMethodAndFileId | |
80.00% |
8 / 10 |
|
0.00% |
0 / 1 |
1.01 | |||
| getByFileIdForUpdate | |
87.50% |
7 / 8 |
|
0.00% |
0 / 1 |
2.01 | |||
| getByFileId | |
100.00% |
10 / 10 |
|
100.00% |
1 / 1 |
2 | |||
| getByIdUncached | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
1 | |||
| getById | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
2 | |||
| getByEnvelopeChildrenAndIdentifyMethod | |
100.00% |
19 / 19 |
|
100.00% |
1 / 1 |
2 | |||
| getEnvelopeChildrenForRemoval | |
92.86% |
26 / 28 |
|
0.00% |
0 / 1 |
5.01 | |||
| findRemindersCandidates | |
0.00% |
0 / 47 |
|
0.00% |
0 / 1 |
20 | |||
| getByMultipleFileId | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
2 | |||
| getByNodeId | |
90.91% |
10 / 11 |
|
0.00% |
0 / 1 |
2.00 | |||
| getByFileUuid | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
6 | |||
| getBySignerUuidAndUserId | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
2 | |||
| getByFileIdAndUserId | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
2 | |||
| getByFileIdAndEmail | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
2 | |||
| getByFileIdAndSignRequestId | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
1 | |||
| getFilesAssociatedFilesWithMe | |
86.67% |
13 / 15 |
|
0.00% |
0 / 1 |
2.01 | |||
| getFilesToSearchProvider | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
12 | |||
| getVisibleElementsFromSigners | |
93.75% |
15 / 16 |
|
0.00% |
0 / 1 |
4.00 | |||
| getIdentifyMethodsFromSigners | |
100.00% |
19 / 19 |
|
100.00% |
1 / 1 |
4 | |||
| getMyLibresignFile | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
| getFilesAssociatedFilesWithMeQueryBuilder | |
55.74% |
68 / 122 |
|
0.00% |
0 / 1 |
95.99 | |||
| getFilesAssociatedFilesWithMeStmt | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
| getTextOfSignerStatus | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| 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\Enum\SignRequestStatus; |
| 13 | use OCA\Libresign\Helper\Pagination; |
| 14 | use OCA\Libresign\Service\IdentifyMethod\IIdentifyMethod; |
| 15 | use OCA\Libresign\Service\IdentifyMethodService; |
| 16 | use OCP\AppFramework\Db\DoesNotExistException; |
| 17 | use OCP\AppFramework\Db\Entity; |
| 18 | use OCP\AppFramework\Db\TTransactional; |
| 19 | use OCP\DB\QueryBuilder\IQueryBuilder; |
| 20 | use OCP\ICacheFactory; |
| 21 | use OCP\IDBConnection; |
| 22 | use OCP\IL10N; |
| 23 | use OCP\IURLGenerator; |
| 24 | use OCP\IUser; |
| 25 | |
| 26 | /** |
| 27 | * Class SignRequestMapper |
| 28 | * |
| 29 | * @package OCA\Libresign\DB |
| 30 | * @template-extends CachedQBMapper<SignRequest> |
| 31 | */ |
| 32 | class SignRequestMapper extends CachedQBMapper { |
| 33 | use TTransactional; |
| 34 | |
| 35 | public function __construct( |
| 36 | IDBConnection $db, |
| 37 | protected IL10N $l10n, |
| 38 | protected FileMapper $fileMapper, |
| 39 | private IURLGenerator $urlGenerator, |
| 40 | ICacheFactory $cacheFactory, |
| 41 | ) { |
| 42 | parent::__construct($db, $cacheFactory, 'libresign_sign_request'); |
| 43 | } |
| 44 | |
| 45 | #[\Override] |
| 46 | public function update(Entity $entity): SignRequest { |
| 47 | $entityId = $entity->getId(); |
| 48 | if ($entityId !== null) { |
| 49 | $cached = $this->cacheGet('id:' . $entityId); |
| 50 | if ($cached instanceof SignRequest) { |
| 51 | $uuid = $cached->getUuid(); |
| 52 | if ($uuid !== '') { |
| 53 | $this->cacheRemove('uuid:' . $uuid); |
| 54 | } |
| 55 | } |
| 56 | } |
| 57 | /** @var SignRequest */ |
| 58 | return parent::update($entity); |
| 59 | } |
| 60 | |
| 61 | #[\Override] |
| 62 | protected function cacheEntity(Entity $entity): void { |
| 63 | parent::cacheEntity($entity); |
| 64 | if ($entity instanceof SignRequest) { |
| 65 | $uuid = $entity->getUuid(); |
| 66 | if ($uuid !== '') { |
| 67 | $this->cacheSet('uuid:' . $uuid, $entity->getId()); |
| 68 | } |
| 69 | } |
| 70 | } |
| 71 | |
| 72 | /** |
| 73 | * @return boolean true when is the first notification |
| 74 | */ |
| 75 | public function incrementNotificationCounter(SignRequest $signRequest, string $method): bool { |
| 76 | return $this->atomic(function () use ($signRequest, $method): bool { |
| 77 | // Bypass entity caches: mail, activity and in-app notification |
| 78 | // listeners share this JSON log. Lock the latest DB row so two |
| 79 | // writers cannot replace each other's entries. |
| 80 | $qb = $this->db->getQueryBuilder(); |
| 81 | $qb->select('*') |
| 82 | ->from($this->getTableName()) |
| 83 | ->where($qb->expr()->eq('id', $qb->createNamedParameter($signRequest->getId(), IQueryBuilder::PARAM_INT))); |
| 84 | if ($this->db->getDatabaseProvider() !== IDBConnection::PLATFORM_SQLITE) { |
| 85 | $qb->forUpdate(); |
| 86 | } |
| 87 | /** @var SignRequest $fromDatabase */ |
| 88 | $fromDatabase = $this->findEntity($qb); |
| 89 | $metadata = $fromDatabase->getMetadata() ?? []; |
| 90 | // First in THIS channel, not first across unrelated channels. |
| 91 | // The shared legacy history and its reminder dates are preserved. |
| 92 | $isFirstNotification = true; |
| 93 | foreach ($metadata['notify'] ?? [] as $notification) { |
| 94 | if (($notification['method'] ?? null) === $method) { |
| 95 | $isFirstNotification = false; |
| 96 | break; |
| 97 | } |
| 98 | } |
| 99 | |
| 100 | $entry = ['method' => $method, 'date' => time()]; |
| 101 | if ($fromDatabase->getDescription() !== null && $fromDatabase->getDescription() !== '') { |
| 102 | $entry['description'] = $fromDatabase->getDescription(); |
| 103 | } |
| 104 | $metadata['notify'][] = $entry; |
| 105 | $fromDatabase->setMetadata($metadata); |
| 106 | $this->update($fromDatabase); |
| 107 | return $isFirstNotification; |
| 108 | }, $this->db); |
| 109 | } |
| 110 | |
| 111 | /** |
| 112 | * Delete only while the signing evidence is still absent in the database. |
| 113 | * |
| 114 | * An earlier read is not sufficient: a concurrent signer may have completed |
| 115 | * the request after the service checked its status. |
| 116 | */ |
| 117 | public function deleteIfUnsigned(SignRequest $signRequest): bool { |
| 118 | $id = $signRequest->getId(); |
| 119 | if ($id === null) { |
| 120 | throw new \InvalidArgumentException('Sign request must be persisted before removal'); |
| 121 | } |
| 122 | |
| 123 | $qb = $this->db->getQueryBuilder(); |
| 124 | $deleted = $qb->delete($this->getTableName()) |
| 125 | ->where($qb->expr()->eq('id', $qb->createNamedParameter($id, IQueryBuilder::PARAM_INT))) |
| 126 | ->andWhere($qb->expr()->neq('status', $qb->createNamedParameter(SignRequestStatus::SIGNED->value, IQueryBuilder::PARAM_INT))) |
| 127 | ->andWhere($qb->expr()->isNull('signed')) |
| 128 | ->andWhere($qb->expr()->isNull('signed_hash')) |
| 129 | ->executeStatement(); |
| 130 | |
| 131 | if ($deleted === 1) { |
| 132 | $this->invalidateCachedEntity($signRequest); |
| 133 | if ($signRequest->getUuid() !== '') { |
| 134 | $this->cacheRemove('uuid:' . $signRequest->getUuid()); |
| 135 | } |
| 136 | } |
| 137 | |
| 138 | return $deleted === 1; |
| 139 | } |
| 140 | |
| 141 | /** |
| 142 | * Get sign request by UUID |
| 143 | * |
| 144 | * @throws DoesNotExistException |
| 145 | */ |
| 146 | public function getByUuid(string $uuid): SignRequest { |
| 147 | if ($uuid !== '') { |
| 148 | $cachedId = $this->cacheGet('uuid:' . $uuid); |
| 149 | if (is_int($cachedId) || (is_string($cachedId) && ctype_digit($cachedId))) { |
| 150 | return $this->getById((int)$cachedId); |
| 151 | } |
| 152 | } |
| 153 | $qb = $this->db->getQueryBuilder(); |
| 154 | |
| 155 | $qb->select('*') |
| 156 | ->from($this->getTableName()) |
| 157 | ->where( |
| 158 | $qb->expr()->eq('uuid', $qb->createNamedParameter($uuid)) |
| 159 | ); |
| 160 | /** @var SignRequest */ |
| 161 | $signRequest = $this->findEntity($qb); |
| 162 | $this->cacheEntity($signRequest); |
| 163 | return $signRequest; |
| 164 | } |
| 165 | |
| 166 | /** |
| 167 | * @throws DoesNotExistException |
| 168 | */ |
| 169 | public function getByUuidUncached(string $uuid): SignRequest { |
| 170 | $qb = $this->db->getQueryBuilder(); |
| 171 | |
| 172 | $qb->select('*') |
| 173 | ->from($this->getTableName()) |
| 174 | ->where( |
| 175 | $qb->expr()->eq('uuid', $qb->createNamedParameter($uuid)) |
| 176 | ); |
| 177 | |
| 178 | /** @var SignRequest */ |
| 179 | return $this->findEntity($qb); |
| 180 | } |
| 181 | |
| 182 | public function getByEmailAndFileId(string $email, int $fileId): SignRequest { |
| 183 | $qb = $this->db->getQueryBuilder(); |
| 184 | |
| 185 | $qb->select('*') |
| 186 | ->from($this->getTableName()) |
| 187 | ->where( |
| 188 | $qb->expr()->eq('email', $qb->createNamedParameter($email)) |
| 189 | ) |
| 190 | ->andWhere( |
| 191 | $qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)) |
| 192 | ); |
| 193 | /** @var SignRequest */ |
| 194 | $signRequest = $this->findEntity($qb); |
| 195 | $this->cacheEntity($signRequest); |
| 196 | return $signRequest; |
| 197 | } |
| 198 | |
| 199 | public function getByIdentifyMethodAndFileId(IIdentifyMethod $identifyMethod, int $fileId): SignRequest { |
| 200 | $qb = $this->db->getQueryBuilder(); |
| 201 | $qb->select('sr.*') |
| 202 | ->from($this->getTableName(), 'sr') |
| 203 | ->join('sr', 'libresign_identify_method', 'im', 'sr.id = im.sign_request_id') |
| 204 | ->where($qb->expr()->eq('im.identifier_key', $qb->createNamedParameter($identifyMethod->getEntity()->getIdentifierKey()))) |
| 205 | ->andWhere($qb->expr()->eq('im.identifier_value', $qb->createNamedParameter($identifyMethod->getEntity()->getIdentifierValue()))) |
| 206 | ->andWhere($qb->expr()->eq('sr.file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))); |
| 207 | /** @var SignRequest */ |
| 208 | $signRequest = $this->findEntity($qb); |
| 209 | $this->cacheEntity($signRequest); |
| 210 | return $signRequest; |
| 211 | } |
| 212 | |
| 213 | /** |
| 214 | * Read and lock every signing request for a file until the current database |
| 215 | * transaction ends. Do not use cached entities for integrity decisions. |
| 216 | * |
| 217 | * @return list<SignRequest> |
| 218 | */ |
| 219 | public function getByFileIdForUpdate(int $fileId): array { |
| 220 | $qb = $this->db->getQueryBuilder(); |
| 221 | $qb->select('*') |
| 222 | ->from($this->getTableName()) |
| 223 | ->where($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT))) |
| 224 | ->orderBy('id', 'ASC'); |
| 225 | // SQLite serializes writers and does not support SELECT FOR UPDATE. |
| 226 | if ($this->db->getDatabaseProvider() !== IDBConnection::PLATFORM_SQLITE) { |
| 227 | $qb->forUpdate(); |
| 228 | } |
| 229 | |
| 230 | /** @var list<SignRequest> */ |
| 231 | return $this->findEntities($qb); |
| 232 | } |
| 233 | |
| 234 | /** |
| 235 | * Get all signers by fileId |
| 236 | * |
| 237 | * @return SignRequest[] |
| 238 | */ |
| 239 | public function getByFileId(int $fileId): array { |
| 240 | $qb = $this->db->getQueryBuilder(); |
| 241 | |
| 242 | $qb->select('*') |
| 243 | ->from($this->getTableName()) |
| 244 | ->where( |
| 245 | $qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)) |
| 246 | ); |
| 247 | /** @var SignRequest[] */ |
| 248 | $signers = $this->findEntities($qb); |
| 249 | foreach ($signers as $signRequest) { |
| 250 | $this->cacheEntity($signRequest); |
| 251 | } |
| 252 | return $signers; |
| 253 | } |
| 254 | |
| 255 | /** |
| 256 | * @throws DoesNotExistException |
| 257 | */ |
| 258 | /** Read the persisted receipt, bypassing the per-request/distributed entity cache. */ |
| 259 | public function getByIdUncached(int $signRequestId): SignRequest { |
| 260 | $qb = $this->db->getQueryBuilder(); |
| 261 | $qb->select('*') |
| 262 | ->from($this->getTableName()) |
| 263 | ->where($qb->expr()->eq('id', $qb->createNamedParameter($signRequestId, IQueryBuilder::PARAM_INT))); |
| 264 | /** @var SignRequest */ |
| 265 | return $this->findEntity($qb); |
| 266 | } |
| 267 | |
| 268 | public function getById(int $signRequestId): SignRequest { |
| 269 | $cached = $this->cacheGet('id:' . $signRequestId); |
| 270 | if ($cached instanceof SignRequest) { |
| 271 | return $cached; |
| 272 | } |
| 273 | $qb = $this->db->getQueryBuilder(); |
| 274 | |
| 275 | $qb->select('*') |
| 276 | ->from($this->getTableName()) |
| 277 | ->where( |
| 278 | $qb->expr()->eq('id', $qb->createNamedParameter($signRequestId, IQueryBuilder::PARAM_INT)) |
| 279 | ); |
| 280 | |
| 281 | /** @var SignRequest */ |
| 282 | $signRequest = $this->findEntity($qb); |
| 283 | $this->cacheEntity($signRequest); |
| 284 | return $signRequest; |
| 285 | } |
| 286 | |
| 287 | /** |
| 288 | * Get sign requests of child files from an envelope for the same signer |
| 289 | * |
| 290 | * @return SignRequest[] |
| 291 | */ |
| 292 | public function getByEnvelopeChildrenAndIdentifyMethod(int $parentFileId, int $signRequestId): array { |
| 293 | $qb = $this->db->getQueryBuilder(); |
| 294 | |
| 295 | $qb->select('sr.*') |
| 296 | ->from('libresign_file', 'f') |
| 297 | ->innerJoin('f', $this->getTableName(), 'sr', $qb->expr()->eq('sr.file_id', 'f.id')) |
| 298 | ->innerJoin('sr', 'libresign_identify_method', 'im', $qb->expr()->eq('im.sign_request_id', 'sr.id')) |
| 299 | ->innerJoin('im', 'libresign_identify_method', 'im2', |
| 300 | $qb->expr()->andX( |
| 301 | $qb->expr()->eq('im2.sign_request_id', $qb->createNamedParameter($signRequestId, IQueryBuilder::PARAM_INT)), |
| 302 | $qb->expr()->eq('im2.identifier_key', 'im.identifier_key'), |
| 303 | $qb->expr()->eq('im2.identifier_value', 'im.identifier_value') |
| 304 | ) |
| 305 | ) |
| 306 | ->where( |
| 307 | $qb->expr()->eq('f.parent_file_id', $qb->createNamedParameter($parentFileId, IQueryBuilder::PARAM_INT)) |
| 308 | ); |
| 309 | |
| 310 | /** @var SignRequest[] */ |
| 311 | $signRequests = $this->findEntities($qb); |
| 312 | foreach ($signRequests as $signRequest) { |
| 313 | $this->cacheEntity($signRequest); |
| 314 | } |
| 315 | return $signRequests; |
| 316 | } |
| 317 | |
| 318 | /** |
| 319 | * Resolve only the envelope children with the same full identification as |
| 320 | * the selected parent signer. A shared email alone does not identify the |
| 321 | * same participant when their account or other factors differ. |
| 322 | * |
| 323 | * @return list<SignRequest> |
| 324 | */ |
| 325 | public function getEnvelopeChildrenForRemoval(int $parentFileId, int $signRequestId): array { |
| 326 | $candidates = $this->getByEnvelopeChildrenAndIdentifyMethod($parentFileId, $signRequestId); |
| 327 | if ($candidates === []) { |
| 328 | return []; |
| 329 | } |
| 330 | |
| 331 | $ids = array_merge([$signRequestId], array_map( |
| 332 | static fn (SignRequest $signer): int => $signer->getId(), |
| 333 | $candidates, |
| 334 | )); |
| 335 | $qb = $this->db->getQueryBuilder(); |
| 336 | $qb->select('sign_request_id', 'identifier_key', 'identifier_value') |
| 337 | ->from('libresign_identify_method') |
| 338 | ->where($qb->expr()->in('sign_request_id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY))); |
| 339 | |
| 340 | $identities = []; |
| 341 | $result = $qb->executeQuery(); |
| 342 | try { |
| 343 | while ($method = $result->fetch()) { |
| 344 | $id = (int)$method['sign_request_id']; |
| 345 | $factor = $method['identifier_key'] . chr(0) . $method['identifier_value']; |
| 346 | $identities[$id][$factor] = true; |
| 347 | } |
| 348 | } finally { |
| 349 | $result->closeCursor(); |
| 350 | } |
| 351 | |
| 352 | $parentIdentity = $identities[$signRequestId] ?? []; |
| 353 | if ($parentIdentity === []) { |
| 354 | return []; |
| 355 | } |
| 356 | |
| 357 | return array_values(array_filter($candidates, |
| 358 | static function (SignRequest $child) use ($parentIdentity, $identities): bool { |
| 359 | $childIdentity = $identities[$child->getId()] ?? []; |
| 360 | return count($parentIdentity) === count($childIdentity) |
| 361 | && array_diff_key($parentIdentity, $childIdentity) === []; |
| 362 | }, |
| 363 | )); |
| 364 | } |
| 365 | |
| 366 | /** |
| 367 | * @return \Generator<IdentifyMethod> |
| 368 | */ |
| 369 | public function findRemindersCandidates(): \Generator { |
| 370 | $qb = $this->db->getQueryBuilder(); |
| 371 | $qb->select( |
| 372 | 'sr.id AS sr_id', |
| 373 | 'sr.file_id AS sr_file_id', |
| 374 | 'sr.uuid AS sr_uuid', |
| 375 | 'sr.display_name AS sr_display_name', |
| 376 | 'sr.description AS sr_description', |
| 377 | 'sr.metadata AS sr_metadata', |
| 378 | 'sr.signed_hash AS sr_signed_hash', |
| 379 | 'sr.created_at AS sr_created_at', |
| 380 | 'sr.signed AS sr_signed', |
| 381 | |
| 382 | 'im.id AS im_id', |
| 383 | 'im.mandatory AS im_mandatory', |
| 384 | 'im.code AS im_code', |
| 385 | 'im.identifier_key AS im_identifier_key', |
| 386 | 'im.identifier_value AS im_identifier_value', |
| 387 | 'im.attempts AS im_attempts', |
| 388 | 'im.identified_at_date AS im_identified_at_date', |
| 389 | 'im.last_attempt_date AS im_last_attempt_date', |
| 390 | 'im.sign_request_id AS im_sign_request_id', |
| 391 | 'im.metadata AS im_metadata', |
| 392 | ) |
| 393 | ->from('libresign_sign_request', 'sr') |
| 394 | ->join('sr', 'libresign_identify_method', 'im', 'sr.id = im.sign_request_id') |
| 395 | ->join('sr', 'libresign_file', 'f', 'sr.file_id = f.id') |
| 396 | ->where($qb->expr()->isNull('sr.signed')) |
| 397 | ->andWhere($qb->expr()->neq('im.identifier_value', $qb->createNamedParameter('deleted_users'))) |
| 398 | ->andWhere($qb->expr()->in('f.status', $qb->createNamedParameter([ |
| 399 | FileStatus::ABLE_TO_SIGN->value, |
| 400 | FileStatus::PARTIAL_SIGNED->value |
| 401 | ], IQueryBuilder::PARAM_INT_ARRAY))) |
| 402 | ->setParameter('st', [1,2], IQueryBuilder::PARAM_INT_ARRAY) |
| 403 | ->orderBy('sr.id', 'ASC'); |
| 404 | |
| 405 | $result = $qb->executeQuery(); |
| 406 | try { |
| 407 | /** @var array<string, mixed> $row */ |
| 408 | while ($row = $result->fetch()) { |
| 409 | $signRequest = new SignRequest(); |
| 410 | $identifyMethod = new IdentifyMethod(); |
| 411 | foreach ($row as $key => $value) { |
| 412 | $prop = $identifyMethod->columnToProperty(substr($key, 3)); |
| 413 | if (str_starts_with($key, 'sr_')) { |
| 414 | $signRequest->{'set' . lcfirst($prop)}($value); |
| 415 | } else { |
| 416 | $identifyMethod->{'set' . lcfirst($prop)}($value); |
| 417 | } |
| 418 | } |
| 419 | $signRequest->resetUpdatedFields(); |
| 420 | $identifyMethod->resetUpdatedFields(); |
| 421 | $this->cacheEntity($signRequest); |
| 422 | yield $identifyMethod; |
| 423 | } |
| 424 | } finally { |
| 425 | $result->closeCursor(); |
| 426 | } |
| 427 | } |
| 428 | |
| 429 | /** |
| 430 | * Get all signers by multiple fileId |
| 431 | * Includes signers from both the files themselves and their children files (for envelopes) |
| 432 | * |
| 433 | * @return SignRequest[] |
| 434 | */ |
| 435 | public function getByMultipleFileId(array $fileId) { |
| 436 | $qb = $this->db->getQueryBuilder(); |
| 437 | |
| 438 | $qb->select('sr.*') |
| 439 | ->from($this->getTableName(), 'sr') |
| 440 | ->join('sr', 'libresign_file', 'f', $qb->expr()->eq('sr.file_id', 'f.id')) |
| 441 | ->where( |
| 442 | $qb->expr()->orX( |
| 443 | $qb->expr()->in('f.id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT_ARRAY)), |
| 444 | $qb->expr()->in('f.parent_file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT_ARRAY)) |
| 445 | ) |
| 446 | ) |
| 447 | ->orderBy('sr.id', 'ASC'); |
| 448 | |
| 449 | /** @var SignRequest[] */ |
| 450 | $signers = $this->findEntities($qb); |
| 451 | foreach ($signers as $signRequest) { |
| 452 | $this->cacheEntity($signRequest); |
| 453 | } |
| 454 | return $signers; |
| 455 | } |
| 456 | |
| 457 | /** |
| 458 | * Get all signers by fileId |
| 459 | * |
| 460 | * @return SignRequest[] |
| 461 | */ |
| 462 | public function getByNodeId(int $nodeId) { |
| 463 | $qb = $this->db->getQueryBuilder(); |
| 464 | |
| 465 | $qb->select('sr.*') |
| 466 | ->from($this->getTableName(), 'sr') |
| 467 | ->join('sr', 'libresign_file', 'f', 'sr.file_id = f.id') |
| 468 | ->where( |
| 469 | $qb->expr()->eq('f.node_id', $qb->createNamedParameter($nodeId, IQueryBuilder::PARAM_INT)) |
| 470 | ); |
| 471 | |
| 472 | /** @var SignRequest[] */ |
| 473 | $signers = $this->findEntities($qb); |
| 474 | foreach ($signers as $signRequest) { |
| 475 | $this->cacheEntity($signRequest); |
| 476 | } |
| 477 | return $signers; |
| 478 | } |
| 479 | |
| 480 | /** |
| 481 | * Get all signers by File Uuid |
| 482 | * |
| 483 | * @param string $nodeId |
| 484 | * @return SignRequest[] |
| 485 | */ |
| 486 | public function getByFileUuid(string $uuid) { |
| 487 | $qb = $this->db->getQueryBuilder(); |
| 488 | |
| 489 | $qb->select('sr.*') |
| 490 | ->from($this->getTableName(), 'sr') |
| 491 | ->join('sr', 'libresign_file', 'f', 'sr.file_id = f.id') |
| 492 | ->where( |
| 493 | $qb->expr()->eq('f.uuid', $qb->createNamedParameter($uuid)) |
| 494 | ); |
| 495 | |
| 496 | /** @var SignRequest[] */ |
| 497 | $signers = $this->findEntities($qb); |
| 498 | foreach ($signers as $signRequest) { |
| 499 | $this->cacheEntity($signRequest); |
| 500 | } |
| 501 | return $signers; |
| 502 | } |
| 503 | |
| 504 | public function getBySignerUuidAndUserId(string $uuid): SignRequest { |
| 505 | $qb = $this->db->getQueryBuilder(); |
| 506 | |
| 507 | $qb->select('sr.*') |
| 508 | ->from($this->getTableName(), 'sr') |
| 509 | ->where( |
| 510 | $qb->expr()->eq('sr.uuid', $qb->createNamedParameter($uuid)) |
| 511 | ); |
| 512 | |
| 513 | /** @var SignRequest */ |
| 514 | $signRequest = $this->findEntity($qb); |
| 515 | $this->cacheEntity($signRequest); |
| 516 | return $signRequest; |
| 517 | } |
| 518 | |
| 519 | public function getByFileIdAndUserId(int $file_id): SignRequest { |
| 520 | $qb = $this->db->getQueryBuilder(); |
| 521 | |
| 522 | $qb->select('sr.*') |
| 523 | ->from($this->getTableName(), 'sr') |
| 524 | ->join('sr', 'libresign_file', 'f', 'sr.file_id = f.id') |
| 525 | ->where( |
| 526 | $qb->expr()->eq('f.node_id', $qb->createNamedParameter($file_id, IQueryBuilder::PARAM_INT)) |
| 527 | ); |
| 528 | |
| 529 | /** @var SignRequest */ |
| 530 | $signRequest = $this->findEntity($qb); |
| 531 | $this->cacheEntity($signRequest); |
| 532 | return $signRequest; |
| 533 | } |
| 534 | |
| 535 | public function getByFileIdAndEmail(int $file_id, string $email): SignRequest { |
| 536 | $qb = $this->db->getQueryBuilder(); |
| 537 | |
| 538 | $qb->select('sr.*') |
| 539 | ->from($this->getTableName(), 'sr') |
| 540 | ->join('sr', 'libresign_file', 'f', 'sr.file_id = f.id') |
| 541 | ->where( |
| 542 | $qb->expr()->eq('f.node_id', $qb->createNamedParameter($file_id, IQueryBuilder::PARAM_INT)) |
| 543 | ) |
| 544 | ->andWhere( |
| 545 | $qb->expr()->eq('sr.email', $qb->createNamedParameter($email)) |
| 546 | ); |
| 547 | |
| 548 | /** @var SignRequest */ |
| 549 | $signRequest = $this->findEntity($qb); |
| 550 | $this->cacheEntity($signRequest); |
| 551 | return $signRequest; |
| 552 | } |
| 553 | |
| 554 | public function getByFileIdAndSignRequestId(int $fileId, int $signRequestId): SignRequest { |
| 555 | $qb = $this->db->getQueryBuilder(); |
| 556 | |
| 557 | $qb->select('sr.*') |
| 558 | ->from($this->getTableName(), 'sr') |
| 559 | ->where( |
| 560 | $qb->expr()->eq('sr.file_id', $qb->createNamedParameter($fileId)) |
| 561 | ) |
| 562 | ->andWhere( |
| 563 | $qb->expr()->eq('sr.id', $qb->createNamedParameter($signRequestId)) |
| 564 | ); |
| 565 | |
| 566 | /** @var SignRequest */ |
| 567 | $signRequest = $this->findEntity($qb); |
| 568 | $this->cacheEntity($signRequest); |
| 569 | return $signRequest; |
| 570 | } |
| 571 | |
| 572 | /** |
| 573 | * @return array{data: list<File>, pagination: Pagination} |
| 574 | */ |
| 575 | public function getFilesAssociatedFilesWithMe( |
| 576 | IUser $user, |
| 577 | array $filter, |
| 578 | ?int $page = null, |
| 579 | ?int $length = null, |
| 580 | ?array $sort = [], |
| 581 | ): array { |
| 582 | $filter['email'] = $user->getEMailAddress(); |
| 583 | $filter['length'] = $length; |
| 584 | $filter['page'] = $page; |
| 585 | $pagination = $this->getFilesAssociatedFilesWithMeStmt($user->getUID(), $filter, $sort); |
| 586 | $pagination->setMaxPerPage($length); |
| 587 | $pagination->setCurrentPage($page); |
| 588 | $currentPageResults = $pagination->getCurrentPageResults(); |
| 589 | |
| 590 | $data = []; |
| 591 | foreach ($currentPageResults as $row) { |
| 592 | $file = new File(); |
| 593 | $data[] = $file->fromRow($row); |
| 594 | } |
| 595 | /** @var array{data: list<File>, pagination: Pagination} */ |
| 596 | return [ |
| 597 | 'data' => $data, |
| 598 | 'pagination' => $pagination, |
| 599 | ]; |
| 600 | } |
| 601 | |
| 602 | public function getFilesToSearchProvider(IUser $user, string $fileName, int $limit, int $offset): array { |
| 603 | $filter = [ |
| 604 | 'page' => ($offset / $limit) + 1, |
| 605 | 'length' => $limit, |
| 606 | 'fileName' => $fileName, |
| 607 | ]; |
| 608 | |
| 609 | $sort = [ |
| 610 | 'sortBy' => 'created_at', |
| 611 | 'sortDirection' => 'desc', |
| 612 | ]; |
| 613 | |
| 614 | $qb = $this->getFilesAssociatedFilesWithMeQueryBuilder($user->getUID(), $filter, false, $sort); |
| 615 | |
| 616 | $result = $qb->executeQuery(); |
| 617 | $files = []; |
| 618 | |
| 619 | while ($row = $result->fetch()) { |
| 620 | try { |
| 621 | $file = File::fromRow($row); |
| 622 | |
| 623 | $files[] = $file; |
| 624 | } catch (\Exception) { |
| 625 | continue; |
| 626 | } |
| 627 | } |
| 628 | |
| 629 | $result->closeCursor(); |
| 630 | |
| 631 | return $files; |
| 632 | } |
| 633 | |
| 634 | /** |
| 635 | * @param array<SignRequest> $signRequests |
| 636 | * @return FileElement[][] |
| 637 | */ |
| 638 | public function getVisibleElementsFromSigners(array $signRequests): array { |
| 639 | $signRequestIds = array_map(fn (SignRequest $signRequest): int => $signRequest->getId(), $signRequests); |
| 640 | if (!$signRequestIds) { |
| 641 | return []; |
| 642 | } |
| 643 | $qb = $this->db->getQueryBuilder(); |
| 644 | |
| 645 | $qb->select('fe.*') |
| 646 | ->from('libresign_file_element', 'fe') |
| 647 | ->where( |
| 648 | $qb->expr()->in('fe.sign_request_id', $qb->createParameter('signRequestIds')) |
| 649 | ); |
| 650 | |
| 651 | $return = []; |
| 652 | foreach (array_chunk($signRequestIds, 1000) as $signRequestIdsChunk) { |
| 653 | $qb->setParameter('signRequestIds', $signRequestIdsChunk, IQueryBuilder::PARAM_INT_ARRAY); |
| 654 | $cursor = $qb->executeQuery(); |
| 655 | while ($row = $cursor->fetch()) { |
| 656 | $return[$row['sign_request_id']][] = (new FileElement())->fromRow($row); |
| 657 | } |
| 658 | } |
| 659 | return $return; |
| 660 | } |
| 661 | |
| 662 | /** |
| 663 | * @param array<SignRequest> $signRequests |
| 664 | * @return array<array-key, array<array-key, \OCP\AppFramework\Db\Entity&\OCA\Libresign\Db\IdentifyMethod>> |
| 665 | */ |
| 666 | public function getIdentifyMethodsFromSigners(array $signRequests): array { |
| 667 | $signRequestIds = array_map(fn (SignRequest $signRequest): int => $signRequest->getId(), $signRequests); |
| 668 | if (!$signRequestIds) { |
| 669 | return []; |
| 670 | } |
| 671 | $qb = $this->db->getQueryBuilder(); |
| 672 | $qb->select('im.*') |
| 673 | ->from('libresign_identify_method', 'im') |
| 674 | ->where( |
| 675 | $qb->expr()->in('im.sign_request_id', $qb->createParameter('signRequestIds')) |
| 676 | ) |
| 677 | ->orderBy('im.mandatory', 'DESC') |
| 678 | ->addOrderBy('im.identified_at_date', 'ASC'); |
| 679 | |
| 680 | $return = []; |
| 681 | foreach (array_chunk($signRequestIds, 1000) as $signRequestIdsChunk) { |
| 682 | $qb->setParameter('signRequestIds', $signRequestIdsChunk, IQueryBuilder::PARAM_INT_ARRAY); |
| 683 | $cursor = $qb->executeQuery(); |
| 684 | while ($row = $cursor->fetch()) { |
| 685 | $identifyMethod = new IdentifyMethod(); |
| 686 | $return[$row['sign_request_id']][$row['identifier_key']] = $identifyMethod->fromRow($row); |
| 687 | } |
| 688 | } |
| 689 | return $return; |
| 690 | } |
| 691 | |
| 692 | public function getMyLibresignFile(string $userId, ?array $filter = []): File { |
| 693 | $qb = $this->getFilesAssociatedFilesWithMeQueryBuilder( |
| 694 | userId: $userId, |
| 695 | filter: $filter, |
| 696 | ); |
| 697 | $cursor = $qb->executeQuery(); |
| 698 | $row = $cursor->fetch(); |
| 699 | if (!$row) { |
| 700 | throw new DoesNotExistException('LibreSign file not found'); |
| 701 | } |
| 702 | |
| 703 | $file = new File(); |
| 704 | return $file->fromRow($row); |
| 705 | } |
| 706 | |
| 707 | private function getFilesAssociatedFilesWithMeQueryBuilder( |
| 708 | string $userId, |
| 709 | array $filter = [], |
| 710 | bool $count = false, |
| 711 | array $sort = [], |
| 712 | ): IQueryBuilder { |
| 713 | $qb = $this->db->getQueryBuilder(); |
| 714 | $qb->from('libresign_file', 'f') |
| 715 | ->leftJoin('f', 'libresign_sign_request', 'sr', 'sr.file_id = f.id') |
| 716 | ->leftJoin('f', 'libresign_identify_method', 'im', $qb->expr()->eq('sr.id', 'im.sign_request_id')) |
| 717 | ->leftJoin('f', 'libresign_id_docs', 'id', 'id.file_id = f.id'); |
| 718 | if ($count) { |
| 719 | $qb->select($qb->func()->count()) |
| 720 | ->setFirstResult(0) |
| 721 | ->setMaxResults(null); |
| 722 | } else { |
| 723 | $qb->select( |
| 724 | 'f.id', |
| 725 | 'f.node_id', |
| 726 | 'f.signed_node_id', |
| 727 | 'f.user_id', |
| 728 | 'f.uuid', |
| 729 | 'f.name', |
| 730 | 'f.status', |
| 731 | 'f.metadata', |
| 732 | 'f.created_at', |
| 733 | 'f.signature_flow', |
| 734 | 'f.docmdp_level', |
| 735 | 'f.node_type', |
| 736 | 'f.parent_file_id' |
| 737 | ) |
| 738 | ->groupBy( |
| 739 | 'f.id', |
| 740 | 'f.node_id', |
| 741 | 'f.signed_node_id', |
| 742 | 'f.user_id', |
| 743 | 'f.uuid', |
| 744 | 'f.name', |
| 745 | 'f.status', |
| 746 | 'f.created_at', |
| 747 | 'f.signature_flow', |
| 748 | 'f.docmdp_level', |
| 749 | 'f.node_type', |
| 750 | 'f.parent_file_id' |
| 751 | ); |
| 752 | // metadata is a json column, the right way is to use f.metadata::text |
| 753 | // when the database is PostgreSQL. The problem is that the command |
| 754 | // addGroupBy add quotes over all text send as argument. With |
| 755 | // PostgreSQL json columns don't have problem if not added to group by. |
| 756 | if ($qb->getConnection()->getDatabaseProvider() !== IDBConnection::PLATFORM_POSTGRES) { |
| 757 | $qb->addGroupBy('f.metadata'); |
| 758 | } |
| 759 | if (isset($filter['length']) && isset($filter['page'])) { |
| 760 | $qb->setFirstResult($filter['length'] * ($filter['page'] - 1)); |
| 761 | $qb->setMaxResults($filter['length']); |
| 762 | } |
| 763 | } |
| 764 | |
| 765 | $or = [ |
| 766 | $qb->expr()->eq('f.user_id', $qb->createNamedParameter($userId)), |
| 767 | $qb->expr()->andX( |
| 768 | $qb->expr()->eq('im.identifier_key', $qb->createNamedParameter(IdentifyMethodService::IDENTIFY_ACCOUNT)), |
| 769 | $qb->expr()->eq('im.identifier_value', $qb->createNamedParameter($userId)), |
| 770 | $qb->expr()->neq('f.status', $qb->createNamedParameter(FileStatus::DRAFT->value)), |
| 771 | $qb->expr()->neq('sr.status', $qb->createNamedParameter(SignRequestStatus::DRAFT->value)), |
| 772 | ) |
| 773 | ]; |
| 774 | $qb->where($qb->expr()->orX(...$or)) |
| 775 | ->andWhere($qb->expr()->isNull('id.id')); |
| 776 | |
| 777 | if ($filter) { |
| 778 | if (isset($filter['email']) && filter_var($filter['email'], FILTER_VALIDATE_EMAIL)) { |
| 779 | $or[] = $qb->expr()->andX( |
| 780 | $qb->expr()->eq('im.identifier_key', $qb->createNamedParameter(IdentifyMethodService::IDENTIFY_EMAIL)), |
| 781 | $qb->expr()->eq('im.identifier_value', $qb->createNamedParameter($filter['email'])) |
| 782 | ); |
| 783 | } |
| 784 | if (!empty($filter['signer_uuid']) && !empty($filter['parentFileId'])) { |
| 785 | $qb->leftJoin('f', 'libresign_file', 'parent', $qb->expr()->eq('f.parent_file_id', 'parent.id')); |
| 786 | $qb->innerJoin('parent', 'libresign_sign_request', 'psr', $qb->expr()->eq('psr.file_id', 'parent.id')) |
| 787 | ->andWhere($qb->expr()->eq('psr.uuid', $qb->createNamedParameter($filter['signer_uuid']))); |
| 788 | } elseif (!empty($filter['signer_uuid'])) { |
| 789 | $qb->andWhere( |
| 790 | $qb->expr()->eq('sr.uuid', $qb->createNamedParameter($filter['signer_uuid'])) |
| 791 | ); |
| 792 | } |
| 793 | if (!empty($filter['nodeIds'])) { |
| 794 | $qb->andWhere( |
| 795 | $qb->expr()->orX( |
| 796 | $qb->expr()->in('f.node_id', $qb->createNamedParameter($filter['nodeIds'], IQueryBuilder::PARAM_INT_ARRAY)), |
| 797 | $qb->expr()->in('f.signed_node_id', $qb->createNamedParameter($filter['nodeIds'], IQueryBuilder::PARAM_INT_ARRAY)) |
| 798 | ) |
| 799 | ); |
| 800 | } |
| 801 | if (!empty($filter['fileIds'])) { |
| 802 | $qb->andWhere( |
| 803 | $qb->expr()->in('f.id', $qb->createNamedParameter($filter['fileIds'], IQueryBuilder::PARAM_INT_ARRAY)) |
| 804 | ); |
| 805 | } |
| 806 | if (!empty($filter['status'])) { |
| 807 | $qb->andWhere( |
| 808 | $qb->expr()->in('f.status', $qb->createNamedParameter($filter['status'], IQueryBuilder::PARAM_INT_ARRAY)) |
| 809 | ); |
| 810 | } |
| 811 | if (!empty($filter['start'])) { |
| 812 | $start = (new \DateTime('@' . $filter['start'], new \DateTimeZone('UTC')))->format('Y-m-d H:i:s'); |
| 813 | $qb->andWhere( |
| 814 | $qb->expr()->gte('f.created_at', $qb->createNamedParameter($start, IQueryBuilder::PARAM_STR)) |
| 815 | ); |
| 816 | } |
| 817 | if (!empty($filter['end'])) { |
| 818 | $end = (new \DateTime('@' . $filter['end'], new \DateTimeZone('UTC')))->format('Y-m-d H:i:s'); |
| 819 | $qb->andWhere( |
| 820 | $qb->expr()->lte('f.created_at', $qb->createNamedParameter($end, IQueryBuilder::PARAM_STR)) |
| 821 | ); |
| 822 | } |
| 823 | if (!empty($filter['fileName'])) { |
| 824 | $qb->andWhere( |
| 825 | $qb->expr()->like('f.name', $qb->createNamedParameter('%' . $this->db->escapeLikeParameter($filter['fileName']) . '%')) |
| 826 | ); |
| 827 | } |
| 828 | if (!empty($filter['parentFileId'])) { |
| 829 | $qb->andWhere( |
| 830 | $qb->expr()->eq('f.parent_file_id', $qb->createNamedParameter($filter['parentFileId'], IQueryBuilder::PARAM_INT)) |
| 831 | ); |
| 832 | } else { |
| 833 | $qb->andWhere($qb->expr()->isNull('f.parent_file_id')); |
| 834 | } |
| 835 | } |
| 836 | |
| 837 | if (!empty($sort['sortBy']) && !empty($sort['sortDirection'])) { |
| 838 | switch ($sort['sortBy']) { |
| 839 | case 'name': |
| 840 | $qb->orderBy( |
| 841 | $qb->func()->lower('f.' . $sort['sortBy']), |
| 842 | $sort['sortDirection'] == 'asc' ? 'asc' : 'desc' |
| 843 | ); |
| 844 | break; |
| 845 | case 'status': |
| 846 | case 'created_at': |
| 847 | $qb->orderBy( |
| 848 | 'f.' . $sort['sortBy'], |
| 849 | $sort['sortDirection'] == 'asc' ? 'asc' : 'desc' |
| 850 | ); |
| 851 | break; |
| 852 | case 'signers': |
| 853 | if (!$count) { |
| 854 | $qb->addOrderBy($qb->createFunction('COUNT(DISTINCT sr.id)'), $sort['sortDirection'] == 'asc' ? 'asc' : 'desc'); |
| 855 | } |
| 856 | break; |
| 857 | } |
| 858 | } |
| 859 | |
| 860 | return $qb; |
| 861 | } |
| 862 | |
| 863 | private function getFilesAssociatedFilesWithMeStmt( |
| 864 | string $userId, |
| 865 | ?array $filter = [], |
| 866 | ?array $sort = [], |
| 867 | ): Pagination { |
| 868 | $qb = $this->getFilesAssociatedFilesWithMeQueryBuilder($userId, $filter, false, $sort); |
| 869 | |
| 870 | $countQb = $this->getFilesAssociatedFilesWithMeQueryBuilder( |
| 871 | userId: $userId, |
| 872 | filter: $filter, |
| 873 | count: true, |
| 874 | ); |
| 875 | |
| 876 | $pagination = new Pagination($qb, $this->urlGenerator, $countQb); |
| 877 | return $pagination; |
| 878 | } |
| 879 | |
| 880 | public function getTextOfSignerStatus(int $status): string { |
| 881 | return SignRequestStatus::from($status)->getLabel($this->l10n); |
| 882 | } |
| 883 | } |