From bf94bf99b337e9330175a5192402f71ba47be541 Mon Sep 17 00:00:00 2001 From: Petro Vashchuk Date: Thu, 1 Sep 2022 11:25:23 +0300 Subject: [PATCH] Bug 33013: add age, interface and operator to pseudonymized statistics This patch adds age (patron's age), interface (sip/api/opac/intranet/cron/etc) and operator (numerical user id who performed pseudonymized action) to the pseudonymized statistics table. To reproduce: 1. With Pseudonymization enabled, perform a check-out, check-in or renew to add an entry to the pseudonymized_transactions table. 2. Apply the patch and perform the atomic update. 3. Go to sysprefs and enable "Interface" and "Operator ID" for PseudonymizationTransactionFields and "Patron's age" for PseudonymizationPatronFields. 4. Perform the same actions as you did earlier (check-out, check-in, or renew) to add another entry to the pseudonymized_transactions table. Compare those two entries, the one that was added should have new stats. --- Koha/PseudonymizedTransaction.pm | 26 +++++++++++ .../Schema/Result/PseudonymizedTransaction.pm | 6 +++ .../bug-33013_more-pseudonymization-fields.pl | 43 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 7 ++- installer/data/mysql/mandatory/sysprefs.sql | 4 +- .../en/modules/admin/preferences/patrons.pref | 3 ++ 6 files changed, 86 insertions(+), 3 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl diff --git a/Koha/PseudonymizedTransaction.pm b/Koha/PseudonymizedTransaction.pm index ee243d76e67..a1c168f4644 100644 --- a/Koha/PseudonymizedTransaction.pm +++ b/Koha/PseudonymizedTransaction.pm @@ -19,6 +19,8 @@ use Modern::Perl; use Crypt::Eksblowfish::Bcrypt qw( bcrypt ); use List::MoreUtils qw(any); +use C4::Context; + use Koha::Database; use Koha::Exceptions::Config; use Koha::Patrons; @@ -71,6 +73,21 @@ sub new_from_statistic { } } + if ( any { $_ eq 'interface' } @t_fields_to_copy ) { + $values->{interface} = C4::Context->interface; + } + + my $userenv = C4::Context->userenv; + if ( $userenv and $userenv->{number} and any { $_ eq 'operator' } @t_fields_to_copy ) { + if ( defined $userenv->{flags} ) { + $values->{operator} = $userenv->{number}; + } elsif ( $statistic->borrowernumber != $userenv->{number} ) { + warn "Pseudonymization / new record: unflagged user tries to change another user: " + . $statistic->borrowernumber . " != " + . $userenv->{number} . "\n"; + } + } + # Remove fields we have already handled from the list @t_fields_to_copy = grep { $_ ne 'transaction_branchcode' @@ -78,6 +95,8 @@ sub new_from_statistic { && $_ ne 'homebranch' && $_ ne 'transaction_type' && $_ ne 'itemcallnumber' + && $_ ne 'interface' + && $_ ne 'operator' } @t_fields_to_copy; # Populate the remaining columns @@ -86,6 +105,13 @@ sub new_from_statistic { my $patron = Koha::Patrons->find( $statistic->borrowernumber ); if ($patron) { my @p_fields_to_copy = split ',', C4::Context->preference('PseudonymizationPatronFields') || ''; + + if ( grep { $_ eq 'age' } @p_fields_to_copy ) { + $values->{age} = $patron->get_age; + } + + @p_fields_to_copy = grep { $_ ne 'age' } @p_fields_to_copy; + $values = { %$values, map { $_ => $patron->$_ } @p_fields_to_copy }; $values->{branchcode} = $patron->branchcode; # FIXME Must be removed from the pref options, or FK removed (?) diff --git a/Koha/Schema/Result/PseudonymizedTransaction.pm b/Koha/Schema/Result/PseudonymizedTransaction.pm index de06490fefe..8284a8da3d2 100644 --- a/Koha/Schema/Result/PseudonymizedTransaction.pm +++ b/Koha/Schema/Result/PseudonymizedTransaction.pm @@ -191,6 +191,8 @@ __PACKAGE__->add_columns( { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, "sex", { data_type => "varchar", is_nullable => 1, size => 1 }, + "age", + { data_type => "tinyint", is_nullable => 1, size => 4 }, "sort1", { data_type => "varchar", is_nullable => 1, size => 80 }, "sort2", @@ -205,6 +207,10 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 10 }, "transaction_type", { data_type => "varchar", is_nullable => 1, size => 16 }, + "interface", + { data_type => "varchar", is_nullable => 1, size => 16 }, + "operator", + { data_type => "integer", is_nullable => 1 }, "itemnumber", { data_type => "integer", is_nullable => 1 }, "itemtype", diff --git a/installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl b/installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl new file mode 100755 index 00000000000..00f9401ca30 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl @@ -0,0 +1,43 @@ +use Modern::Perl; + +return { + bug_number => "33013", + description => "Update systemprefs Pseudonymization: add age and interface", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + my $res; + + $res = $dbh->do( + q{UPDATE systempreferences SET options=REPLACE(options, ',sex,', ',sex,age,') + WHERE variable='PseudonymizationPatronFields' AND options NOT LIKE '%,age,%'} + ); + + $res = $dbh->do( + q{UPDATE systempreferences SET options=REPLACE(options, ',transaction_type,', ',transaction_type,interface,') + WHERE variable='PseudonymizationTransactionFields' AND options NOT LIKE '%,interface,%'} + ); + + unless ( column_exists( 'pseudonymized_transactions', 'age' ) ) { + $dbh->do("ALTER TABLE pseudonymized_transactions ADD COLUMN age TINYINT DEFAULT NULL AFTER sex"); + } + + unless ( column_exists( 'pseudonymized_transactions', 'interface' ) ) { + $dbh->do( + "ALTER TABLE pseudonymized_transactions ADD COLUMN interface VARCHAR(16) DEFAULT NULL AFTER transaction_type" + ); + } + + unless ( column_exists( 'pseudonymized_transactions', 'operator' ) ) { + $dbh->do("ALTER TABLE pseudonymized_transactions ADD COLUMN operator INT(11) DEFAULT NULL AFTER interface"); + } + + $dbh->do( + "ALTER TABLE pseudonymized_transactions ADD INDEX IF NOT EXISTS `pseudonymized_transactions_reporting_1` (`interface`)" + ); + $dbh->do( + "ALTER TABLE pseudonymized_transactions ADD INDEX IF NOT EXISTS `pseudonymized_transactions_reporting_2` (`operator`)" + ); + }, + } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3bf15c1007d..54d24cda24f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5437,11 +5437,14 @@ CREATE TABLE `pseudonymized_transactions` ( `categorycode` varchar(10) NOT NULL DEFAULT '', `dateenrolled` date DEFAULT NULL, `sex` varchar(1) DEFAULT NULL, + `age` varchar(16) DEFAULT NULL, `sort1` varchar(80) DEFAULT NULL, `sort2` varchar(80) DEFAULT NULL, `datetime` datetime DEFAULT NULL, `transaction_branchcode` varchar(10) DEFAULT NULL, `transaction_type` varchar(16) DEFAULT NULL, + `interface` varchar(16) DEFAULT NULL COMMENT 'Interface (sip/api/opac/intranet/cron/etc)', + `operator` int(11) DEFAULT NULL COMMENT 'User id (if user has extra permissions, operator, api-keys user, sip-user, librarian)', `itemnumber` int(11) DEFAULT NULL, `itemtype` varchar(10) DEFAULT NULL, `holdingbranch` varchar(10) DEFAULT NULL, @@ -5455,7 +5458,9 @@ CREATE TABLE `pseudonymized_transactions` ( KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`), KEY `pseudonymized_transactions_items_ibfk_4` (`itemnumber`), KEY `pseudonymized_transactions_ibfk_5` (`transaction_type`), - KEY `pseudonymized_transactions_ibfk_6` (`datetime`) + KEY `pseudonymized_transactions_ibfk_6` (`datetime`), + KEY `pseudonymized_transactions_reporting_1` (`interface`), + KEY `pseudonymized_transactions_reporting_2` (`operator`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql index 682b9fb950b..4f3793370ed 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -638,8 +638,8 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` ('ProcessingFeeNote', '', NULL, 'Set the text to be recorded in the column note, table accountlines when the processing fee (defined in item type) is applied', 'textarea'), ('ProtectSuperlibrarianPrivileges','1',NULL,'If enabled, non-superlibrarians cannot set superlibrarian privileges','YesNo'), ('Pseudonymization','0',NULL,'If enabled patrons and transactions will be copied in a separate table for statistics purpose','YesNo'), -('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple'), -('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple'), +('PseudonymizationPatronFields','','title,city,state,zipcode,country,branchcode,categorycode,dateenrolled,sex,age,sort1,sort2','Patron fields to copy to the pseudonymized_transactions table','multiple'), +('PseudonymizationTransactionFields','','datetime,branchcode,transaction_type,interface,operator,itemnumber,itemtype,holdingbranch,location,itemcallnumber,ccode','Transaction fields to copy to the pseudonymized_transactions table','multiple'), ('PurgeListShareInvitesOlderThan', '14', NULL, 'If not empty, number of days used when deleting unaccepted list share invites', 'Integer'), ('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'), ('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'), diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref index 671b4f4d416..f9083479be9 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/patrons.pref @@ -509,6 +509,7 @@ Patrons: categorycode: "Patron category" dateenrolled: "Date the patron was added to Koha" sex: "Patron's gender" + age: "Patron's age" sort1: "Sort 1" sort2: "Sort 2" - "
And the following fields for the transactions:" @@ -517,6 +518,8 @@ Patrons: datetime: "Date and time of the transaction" transaction_branchcode: "Library where the transaction occurred" transaction_type: "Transaction type" + interface: "Interface (sip/api/opac/intranet/cron/etc)" + operator: "Operator ID (user id if it has extra permissions, i.e. librarian)" itemnumber: "Itemnumber" itemtype: "Item type" holdingbranch: "Holding library" -- 2.48.1