From 02a19c9e8fbea0a93bec67047772230869866817 Mon Sep 17 00:00:00 2001
From: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Date: Wed, 16 Apr 2025 12:19:16 +0000
Subject: [PATCH] Bug 37901: Add Tests

Example reports that can be written for ILL pseudonymized data:

--Total of requests of a given request type (article)--

SELECT
  COUNT(*) AS count
FROM
  pseudonymized_transactions pt
WHERE
  EXISTS (
    SELECT 1
    FROM pseudonymized_metadata_values pmv
    WHERE pmv.transaction_id = pt.id
    AND pmv.key = 'type'
    AND pmv.value = 'article'
  );

--Add backend info to listing--

SELECT
  pt.*,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend
FROM
  pseudonymized_transactions pt;

--List all pseudonymized transactions and respective metadata values--

SELECT
  pt.*,
  table1.metadata AS borrower_attributes,
  table2.metadata AS illrequestattributes
FROM
  pseudonymized_transactions pt
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'borrower_attributes'
    GROUP BY
      transaction_id
  ) table1 ON pt.id = table1.transaction_id
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'illrequestattributes'
    GROUP BY
      transaction_id
  ) table2 ON pt.id = table2.transaction_id;

--ILL specific pseudonymized transactions report--

SELECT
  pt.hashed_borrowernumber as 'borrower',pt.transaction_type as 'Transaction type', pt.datetime as 'Datetime', pt.branchcode as 'Patron branchcode', pt.categorycode as 'Patron category',
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend
FROM
  pseudonymized_transactions pt

Sponsored-by: UKHSA - UK Health Security Agency

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
---
 t/db_dependent/Koha/Pseudonymization.t | 34 +++++++++++++++++++++++++-
 1 file changed, 33 insertions(+), 1 deletion(-)

diff --git a/t/db_dependent/Koha/Pseudonymization.t b/t/db_dependent/Koha/Pseudonymization.t
index c61d7f3e4f6..50a4b99041b 100755
--- a/t/db_dependent/Koha/Pseudonymization.t
+++ b/t/db_dependent/Koha/Pseudonymization.t
@@ -148,7 +148,7 @@ subtest 'Koha::Anonymized::Transactions tests' => sub {
 
 subtest 'PseudonymizedMetadataValues tests' => sub {
 
-    plan tests => 5;
+    plan tests => 7;
 
     $schema->storage->txn_begin;
 
@@ -275,5 +275,37 @@ subtest 'PseudonymizedMetadataValues tests' => sub {
         'The id of the 2nd pseudonymized transaction should be different'
     );
 
+    my $ill_request = $builder->build_sample_ill_request();
+    $builder->build(
+        {
+            source => 'Illrequestattribute',
+            value  => { illrequest_id => $ill_request->illrequest_id, type => 'type', value => 'book' }
+        }
+    );
+
+    my $ill_request_statistic = Koha::Statistic->new(
+        {
+            type           => 'illreq_created',
+            branch         => $library->branchcode,
+            itemnumber     => undef,
+            borrowernumber => $patron->borrowernumber,
+            itemtype       => undef,
+            location       => undef,
+            illrequest_id  => $ill_request->illrequest_id,
+            ccode          => undef,
+        }
+    );
+
+    my $p2 = Koha::PseudonymizedTransaction->create_from_statistic($ill_request_statistic)->store;
+
+    my $ill_metadata_values =
+        Koha::Database->new->schema->resultset('PseudonymizedMetadataValue')
+        ->search( { transaction_id => $p2->id, tablename => 'illrequestattributes' }, { order_by => 'value' } );
+
+    my $ill_metadata_value = $ill_metadata_values->next;
+
+    is( $ill_metadata_value->key,   'type' );
+    is( $ill_metadata_value->value, 'book' );
+
     $schema->storage->txn_rollback;
 };
-- 
2.39.5