From 0e55e9ca5685d8c4f61336efd88e32b3200eb10d 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 (user id if it has extra permissions, for example: librarian) 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 | 25 +++++++++ .../Schema/Result/PseudonymizedTransaction.pm | 6 +++ .../bug-33013_more-pseudonymization-fields.pl | 53 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 7 ++- installer/data/mysql/mandatory/sysprefs.sql | 4 +- .../en/modules/admin/preferences/patrons.pref | 3 ++ 6 files changed, 95 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 70049a48c9..49e8ab7c64 100644 --- a/Koha/PseudonymizedTransaction.pm +++ b/Koha/PseudonymizedTransaction.pm @@ -18,6 +18,8 @@ use Modern::Perl; use Crypt::Eksblowfish::Bcrypt qw( bcrypt ); +use C4::Context; + use Koha::Database; use Koha::Exceptions::Config; use Koha::Patrons; @@ -63,6 +65,20 @@ sub new_from_statistic { $values->{itemcallnumber} = $statistic->item->itemcallnumber; } + if ( grep { $_ eq 'interface' } @t_fields_to_copy ) { + $values->{interface} = C4::Context->interface; + } + + my $userenv = C4::Context->userenv; + if ( $userenv and $userenv->{number} and grep { $_ 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"; + } + } @t_fields_to_copy = grep { $_ ne 'transaction_branchcode' @@ -70,12 +86,21 @@ sub new_from_statistic { && $_ ne 'homebranch' && $_ ne 'transaction_type' && $_ ne 'itemcallnumber' + && $_ ne 'interface' + && $_ ne 'operator' } @t_fields_to_copy; $values = { %$values, map { $_ => $statistic->$_ } @t_fields_to_copy }; my $patron = Koha::Patrons->find($statistic->borrowernumber); 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 de06490fef..8284a8da3d 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 0000000000..74fb642eff --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl @@ -0,0 +1,53 @@ +use Modern::Perl; + +return { + bug_number => "BUG_NUMBER", + 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,%'} + ); + say $out "Warning: PseudonymizationPatronFields already has 'age'" + if $res == 0; + + $res = $dbh->do( + q{UPDATE systempreferences SET options=REPLACE(options, ',transaction_type,', ',transaction_type,interface,') + WHERE variable='PseudonymizationTransactionFields' AND options NOT LIKE '%,interface,%'} + ); + say $out "Warning: PseudonymizationTransactionFields already has 'interface'" + if $res == 0; + + unless ( column_exists( 'pseudonymized_transactions', 'age' ) ) { + $dbh->do("ALTER TABLE pseudonymized_transactions ADD COLUMN age TINYINT DEFAULT NULL AFTER sex"); + } else { + say $out "Warning: pseudonymized_transactions already has 'age'"; + } + + unless ( column_exists( 'pseudonymized_transactions', 'interface' ) ) { + $dbh->do( + "ALTER TABLE pseudonymized_transactions ADD COLUMN interface VARCHAR(16) DEFAULT NULL AFTER transaction_type" + ); + } else { + say $out "Warning: pseudonymized_transactions already has 'interface'"; + } + + unless ( column_exists( 'pseudonymized_transactions', 'operator' ) ) { + $dbh->do("ALTER TABLE pseudonymized_transactions ADD COLUMN operator INT(11) DEFAULT NULL AFTER interface"); + } else { + say $out "Warning: pseudonymized_transactions already has 'operator'"; + } + + $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 21f6ec015c..a0a0b3774e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4852,11 +4852,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, @@ -4867,7 +4870,9 @@ CREATE TABLE `pseudonymized_transactions` ( PRIMARY KEY (`id`), KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), - KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) + KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`), + 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 fad32d896e..5609ae399b 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -587,8 +587,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'), ('PurgeSuggestionsOlderThan', '', NULL, 'If this script is called without the days parameter', 'Integer'), ('QueryAutoTruncate','1',NULL,'If ON, query truncation is enabled by default','YesNo'), ('QueryFuzzy','1',NULL,'If ON, enables fuzzy option for searches','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 0adb1ae1d7..95748132ee 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 @@ -445,6 +445,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:" @@ -453,6 +454,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.41.0