From 2690aceca59fabbd8241163b53de1707cf9f7152 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 | 30 +++++++++++++ .../Schema/Result/PseudonymizedTransaction.pm | 6 +++ .../bug-33013_more-pseudonymization-fields.pl | 44 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 7 ++- installer/data/mysql/mandatory/sysprefs.sql | 4 +- .../en/modules/admin/preferences/patrons.pref | 3 ++ 6 files changed, 91 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 63521d7e61..524e1a2274 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,23 @@ 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 +89,23 @@ 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->{has_cardnumber} = $patron->cardnumber ? 1 : 0; 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..a2c313c77f --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-33013_more-pseudonymization-fields.pl @@ -0,0 +1,44 @@ +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 59607073ca..d228fd1de1 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4774,11 +4774,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, @@ -4789,7 +4792,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 beebd27a6f..9d0f8694c1 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -576,8 +576,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 25f6c27a12..08cb6b4fa9 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 @@ -417,6 +417,7 @@ Patrons: categorycode: "Patron's category" dateenrolled: "Date the patron was added to Koha" sex: "Patron's gender" + age: "Patron's age" sort1: "Sort1" sort2: "Sort2" - "
And the following fields for the transactions:" @@ -425,6 +426,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.38.1