Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
100.00% |
70 / 70 |
|
100.00% |
19 / 19 |
CRAP | |
100.00% |
1 / 1 |
| UsageActivityReader | |
100.00% |
70 / 70 |
|
100.00% |
19 / 19 |
21 | |
100.00% |
1 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| firstActivityAt | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| firstCertificateRecordedAt | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| countStandaloneFilesCreated | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| countEnvelopesCreated | |
100.00% |
10 / 10 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningRequestsCreated | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningRequestsCompleted | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningRequestsRejected | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countObserversAdded | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningCertificatesIssued | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningCertificatesRevoked | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| countSignRequests | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| countSigningCertificates | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| childFiles | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| identificationDocuments | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| notExists | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| whereInPeriod | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| fetchCount | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| fetchDateTime | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
3 | |||
| 1 | <?php |
| 2 | |
| 3 | declare(strict_types=1); |
| 4 | /** |
| 5 | * SPDX-FileCopyrightText: 2026 LibreCode coop and contributors |
| 6 | * SPDX-License-Identifier: AGPL-3.0-or-later |
| 7 | */ |
| 8 | |
| 9 | namespace OCA\Libresign\Service\UsageStatistics\Source; |
| 10 | |
| 11 | use OCA\Libresign\Enum\CertificateType; |
| 12 | use OCA\Libresign\Enum\NodeType; |
| 13 | use OCA\Libresign\Enum\ParticipantRole; |
| 14 | use OCA\Libresign\Service\UsageStatistics\Model\ReportPeriod; |
| 15 | use OCP\DB\QueryBuilder\IQueryBuilder; |
| 16 | use OCP\IDBConnection; |
| 17 | |
| 18 | /** |
| 19 | * Aggregate counts of stored LibreSign events, by the timestamp of each event. |
| 20 | * |
| 21 | * Signing activity is counted on top-level files only: an envelope and each |
| 22 | * of its files carry their own signing request for the same signer, so |
| 23 | * counting the envelope files too would count one signer several times. |
| 24 | * Identification documents are LibreSign files too, but not signing requests. |
| 25 | */ |
| 26 | class UsageActivityReader { |
| 27 | public function __construct( |
| 28 | private IDBConnection $db, |
| 29 | ) { |
| 30 | } |
| 31 | |
| 32 | /** |
| 33 | * When the first LibreSign file was created. A signing request always |
| 34 | * belongs to a file, so no LibreSign activity can be older than this. |
| 35 | */ |
| 36 | public function firstActivityAt(): ?\DateTimeImmutable { |
| 37 | $qb = $this->db->getQueryBuilder(); |
| 38 | $qb->select($qb->func()->min('created_at')) |
| 39 | ->from('libresign_file'); |
| 40 | return $this->fetchDateTime($qb); |
| 41 | } |
| 42 | |
| 43 | /** When the first certificate of any type was recorded. */ |
| 44 | public function firstCertificateRecordedAt(): ?\DateTimeImmutable { |
| 45 | $qb = $this->db->getQueryBuilder(); |
| 46 | $qb->select($qb->func()->min('issued_at')) |
| 47 | ->from('libresign_crl'); |
| 48 | return $this->fetchDateTime($qb); |
| 49 | } |
| 50 | |
| 51 | public function countStandaloneFilesCreated(ReportPeriod $period): int { |
| 52 | $qb = $this->db->getQueryBuilder(); |
| 53 | $qb->select($qb->func()->count('*')) |
| 54 | ->from('libresign_file', 'f') |
| 55 | ->where($qb->expr()->isNull('f.parent_file_id')) |
| 56 | ->andWhere($qb->expr()->neq('f.node_type', $qb->createNamedParameter(NodeType::ENVELOPE->value))) |
| 57 | ->andWhere($this->notExists($qb, $this->childFiles())) |
| 58 | ->andWhere($this->notExists($qb, $this->identificationDocuments('f.id'))); |
| 59 | $this->whereInPeriod($qb, 'f.created_at', $period); |
| 60 | return $this->fetchCount($qb); |
| 61 | } |
| 62 | |
| 63 | public function countEnvelopesCreated(ReportPeriod $period): int { |
| 64 | $qb = $this->db->getQueryBuilder(); |
| 65 | $qb->select($qb->func()->count('*')) |
| 66 | ->from('libresign_file', 'f') |
| 67 | ->where($qb->expr()->isNull('f.parent_file_id')) |
| 68 | ->andWhere($qb->expr()->orX( |
| 69 | $qb->expr()->eq('f.node_type', $qb->createNamedParameter(NodeType::ENVELOPE->value)), |
| 70 | $qb->createFunction('EXISTS (' . $this->childFiles()->getSQL() . ')'), |
| 71 | )); |
| 72 | $this->whereInPeriod($qb, 'f.created_at', $period); |
| 73 | return $this->fetchCount($qb); |
| 74 | } |
| 75 | |
| 76 | public function countSigningRequestsCreated(ReportPeriod $period): int { |
| 77 | return $this->countSignRequests($period, 'sr.created_at', ParticipantRole::SIGNER); |
| 78 | } |
| 79 | |
| 80 | public function countSigningRequestsCompleted(ReportPeriod $period): int { |
| 81 | return $this->countSignRequests($period, 'sr.signed', ParticipantRole::SIGNER); |
| 82 | } |
| 83 | |
| 84 | public function countSigningRequestsRejected(ReportPeriod $period): int { |
| 85 | return $this->countSignRequests($period, 'sr.rejected_at', ParticipantRole::SIGNER); |
| 86 | } |
| 87 | |
| 88 | public function countObserversAdded(ReportPeriod $period): int { |
| 89 | return $this->countSignRequests($period, 'sr.created_at', ParticipantRole::OBSERVER); |
| 90 | } |
| 91 | |
| 92 | public function countSigningCertificatesIssued(ReportPeriod $period): int { |
| 93 | return $this->countSigningCertificates($period, 'issued_at'); |
| 94 | } |
| 95 | |
| 96 | public function countSigningCertificatesRevoked(ReportPeriod $period): int { |
| 97 | return $this->countSigningCertificates($period, 'revoked_at'); |
| 98 | } |
| 99 | |
| 100 | private function countSignRequests(ReportPeriod $period, string $eventColumn, ParticipantRole $role): int { |
| 101 | $qb = $this->db->getQueryBuilder(); |
| 102 | $qb->select($qb->func()->count('*')) |
| 103 | ->from('libresign_sign_request', 'sr') |
| 104 | ->innerJoin('sr', 'libresign_file', 'f', $qb->expr()->eq('f.id', 'sr.file_id')) |
| 105 | ->where($qb->expr()->isNull('f.parent_file_id')) |
| 106 | ->andWhere($qb->expr()->eq('sr.participant_role', $qb->createNamedParameter($role->value))) |
| 107 | ->andWhere($this->notExists($qb, $this->identificationDocuments('sr.file_id'))); |
| 108 | $this->whereInPeriod($qb, $eventColumn, $period); |
| 109 | return $this->fetchCount($qb); |
| 110 | } |
| 111 | |
| 112 | private function countSigningCertificates(ReportPeriod $period, string $eventColumn): int { |
| 113 | $qb = $this->db->getQueryBuilder(); |
| 114 | $qb->select($qb->func()->count('*')) |
| 115 | ->from('libresign_crl') |
| 116 | ->where($qb->expr()->eq('certificate_type', $qb->createNamedParameter(CertificateType::LEAF->value))); |
| 117 | $this->whereInPeriod($qb, $eventColumn, $period); |
| 118 | return $this->fetchCount($qb); |
| 119 | } |
| 120 | |
| 121 | private function childFiles(): IQueryBuilder { |
| 122 | $sub = $this->db->getQueryBuilder(); |
| 123 | return $sub->select($sub->expr()->literal(1)) |
| 124 | ->from('libresign_file', 'child') |
| 125 | ->where($sub->expr()->eq('child.parent_file_id', 'f.id')); |
| 126 | } |
| 127 | |
| 128 | private function identificationDocuments(string $fileIdColumn): IQueryBuilder { |
| 129 | $sub = $this->db->getQueryBuilder(); |
| 130 | return $sub->select($sub->expr()->literal(1)) |
| 131 | ->from('libresign_id_docs', 'idd') |
| 132 | ->where($sub->expr()->eq('idd.file_id', $fileIdColumn)); |
| 133 | } |
| 134 | |
| 135 | private function notExists(IQueryBuilder $outer, IQueryBuilder $subQuery): string { |
| 136 | return (string)$outer->createFunction('NOT EXISTS (' . $subQuery->getSQL() . ')'); |
| 137 | } |
| 138 | |
| 139 | private function whereInPeriod(IQueryBuilder $qb, string $column, ReportPeriod $period): void { |
| 140 | $qb->andWhere($qb->expr()->gte($column, $qb->createNamedParameter($period->start(), IQueryBuilder::PARAM_DATETIME_IMMUTABLE))) |
| 141 | ->andWhere($qb->expr()->lt($column, $qb->createNamedParameter($period->end(), IQueryBuilder::PARAM_DATETIME_IMMUTABLE))); |
| 142 | } |
| 143 | |
| 144 | private function fetchCount(IQueryBuilder $qb): int { |
| 145 | $result = $qb->executeQuery(); |
| 146 | $count = $result->fetchOne(); |
| 147 | $result->closeCursor(); |
| 148 | return (int)$count; |
| 149 | } |
| 150 | |
| 151 | private function fetchDateTime(IQueryBuilder $qb): ?\DateTimeImmutable { |
| 152 | $result = $qb->executeQuery(); |
| 153 | $value = $result->fetchOne(); |
| 154 | $result->closeCursor(); |
| 155 | if (!is_string($value) || $value === '') { |
| 156 | return null; |
| 157 | } |
| 158 | return new \DateTimeImmutable($value, new \DateTimeZone('UTC')); |
| 159 | } |
| 160 | } |