From 52a7692187e30acda4aef92f5d7f04fd8366acd7 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 22 Nov 2019 17:37:42 +0100 Subject: [PATCH] Bug 24151: Copy info to the pseudonymized table when a transaction is done This is the commit where you will find useful information about this development. The goal of this new feature is to add a way to pseudonymize patron's data, in a way they could not be personally identifiable. https://en.wikipedia.org/wiki/Pseudonymization There are different existing way to anonymize patron's information in Koha, but we loose the ability to make useful report. This development proposes to have 2 different tables: * 1 for transactions and patrons data (pseudonymized_transactions) * 1 for patrons' attributes (pseudonymized_borrower_attributes) Entries to pseudonymized_transactions are added when a new transaction (checkout, checkin, renew, on-site checkout) is done. Also, anonymized_borrower_attributes is populated if patron's attributes are marked as "keep for pseudonymization". To make those informations not identifiable to a patron, we are having a hashed_borrowernumber column in pseudonymized_transactions. This hash will be generated (Blowfish-based crypt) using a key stored in the Koha configuration. To make things configurable, we are adding 3 sysprefs and 1 new DB column: * syspref Pseudonymization to turn on/off the whole feature * syspref PseudonymizationPatronFields to list the informations of the patrons to sync * syspref PseudonymizationTransactionFields to list the informations of the transactions to copy * DB column borrower_attribute_types.keep_for_pseudonymization that is a boolean to enable/disable the copy of a given patron's attribute type. Test plan: 1/ Turn on Pseudonymization 2/ Define in PseudonymizationPatronFields and PseudonymizationTransactionFields the different fields you want to copy 3/ Go to the about page => You will see a warning about a missing config entry 4/ You need to generate a key and put it in the koha-conf.xml file. The following command will generate one: % htpasswd -bnBC 10 "" password | tr -d ':\n' | sed 's/$2y/$2a/' Then edit $KOHA_CONF and add it before of the end of the config section ($2a$10$PfdrEBdRcL2MZlEtKueyLegxI6zg735jD07GRnc1bt.N/ZYMvBAB2 5/ Restart memcached then plack (alias restart_all) => Everything is setup! 6/ Create a new transaction (checkin for instance) => Confirm that a new entry has been added to pseudonymized_transaction with the data you expect to be copied 7/ Edit some patron attribute types and tick "Keep for pseudonymization" 8/ Create a new transaction => Confirm that new entries have been added to pseudonymized_borrower_attributes 11/ Delete the patrons => Confirm that the entries still exist in the pseudonymized_* tables 12/ Purge the patrons (ie. use cleanup_database.pl to remove them from the deleted_borrowers table) => Confirm that the entries still exist in the pseudonymized_* tables See bug 24152 to remove data from the anonymized_* tables Sponsored-by: Association KohaLa - https://koha-fr.org/ Signed-off-by: Signed-off-by: Sonia Bouis --- C4/Stats.pm | 43 +++++++----- Koha/PseudonymizedTransaction.pm | 105 ++++++++++++++++++++++++++++++ Koha/PseudonymizedTransactions.pm | 51 +++++++++++++++ 3 files changed, 183 insertions(+), 16 deletions(-) create mode 100644 Koha/PseudonymizedTransaction.pm create mode 100644 Koha/PseudonymizedTransactions.pm diff --git a/C4/Stats.pm b/C4/Stats.pm index effcc0376c..b3e7f98f7d 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -18,12 +18,16 @@ package C4::Stats; # You should have received a copy of the GNU General Public License # along with Koha; if not, see . -use strict; -use warnings; +use Modern::Perl; require Exporter; use Carp; use C4::Context; use C4::Debug; + +use Koha::DateUtils qw( dt_from_string ); +use Koha::Statistics; +use Koha::PseudonymizedTransactions; + use vars qw(@ISA @EXPORT); our $debug; @@ -124,20 +128,27 @@ sub UpdateStats { my $location = exists $params->{location} ? $params->{location} : undef; my $ccode = exists $params->{ccode} ? $params->{ccode} : ''; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( - "INSERT INTO statistics - (datetime, - branch, type, value, - other, itemnumber, itemtype, location, - borrowernumber, ccode) - VALUES (now(),?,?,?,?,?,?,?,?,?)" - ); - $sth->execute( - $branch, $type, $amount, $other, - $itemnumber, $itemtype, $location, $borrowernumber, - $ccode - ); + my $dtf = Koha::Database->new->schema->storage->datetime_parser; + my $statistic = Koha::Statistic->new( + { + datetime => $dtf->format_datetime( dt_from_string ), + branch => $branch, + type => $type, + value => $amount, + other => $other, + itemnumber => $itemnumber, + itemtype => $itemtype, + location => $location, + borrowernumber => $borrowernumber, + ccode => $ccode, + } + )->store; + + Koha::PseudonymizedTransaction->new_from_statistic($statistic)->store + if C4::Context->preference('Pseudonymization') + && $borrowernumber # Not a real transaction if the patron does not exist + # For instance can be a transfer, or hold trigger + && grep { $_ eq $params->{type} } qw(renew issue return onsite_checkout); } 1; diff --git a/Koha/PseudonymizedTransaction.pm b/Koha/PseudonymizedTransaction.pm new file mode 100644 index 0000000000..04aab176df --- /dev/null +++ b/Koha/PseudonymizedTransaction.pm @@ -0,0 +1,105 @@ +package Koha::PseudonymizedTransaction; + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; +use Crypt::Eksblowfish::Bcrypt qw(bcrypt en_base64); + +use Koha::Database; +use Koha::Exceptions::Config; +use Koha::Patrons; + +use base qw(Koha::Object); + +=head1 NAME + +Koha::PseudonymizedTransaction - Koha Koha::PseudonymizedTransaction Object class + +=head1 API + +=head2 Class methods + +=head3 new + +=cut + +sub new_from_statistic { + my ( $class, $statistic ) = @_; + + my $values = { + hashed_borrowernumber => $class->get_hash($statistic->borrowernumber), + }; + + my @t_fields_to_copy = split ',', C4::Context->preference('PseudonymizationTransactionFields') || ''; + + if ( grep { $_ eq 'transaction_branchcode' } @t_fields_to_copy ) { + $values->{transaction_branchcode} = $statistic->branch; + } + if ( grep { $_ eq 'holdingbranch' } @t_fields_to_copy ) { + $values->{holdingbranch} = $statistic->item->holdingbranch; + } + if ( grep { $_ eq 'transaction_type' } @t_fields_to_copy ) { + $values->{transaction_type} = $statistic->type; + } + if ( grep { $_ eq 'itemcallnumber' } @t_fields_to_copy ) { + $values->{itemcallnumber} = $statistic->item->itemcallnumber; + } + + + @t_fields_to_copy = grep { + $_ ne 'transaction_branchcode' + && $_ ne 'holdingbranch' + && $_ ne 'transaction_type' + && $_ ne 'itemcallnumber' + } @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') || ''; + $values = { %$values, map { $_ => $patron->$_ } @p_fields_to_copy }; + + $values->{branchcode} = $patron->branchcode; # FIXME Must be removed from the pref options, or FK removed (?) + $values->{categorycode} = $patron->categorycode; + + $values->{has_cardnumber} = $patron->cardnumber ? 1 : 0; + + return $class->SUPER::new($values); +} + +sub get_hash { + my ( $class, $s ) = @_; + my $key = C4::Context->config('key'); + + Koha::Exceptions::Config::MissingEntry->throw( + "Missing 'key' entry in config file") unless $key; + + return bcrypt($s, $key); +} + +=head2 Internal methods + +=head3 _type + +=cut + +sub _type { + return 'PseudonymizedTransaction'; +} + +1; diff --git a/Koha/PseudonymizedTransactions.pm b/Koha/PseudonymizedTransactions.pm new file mode 100644 index 0000000000..524b3b1a4b --- /dev/null +++ b/Koha/PseudonymizedTransactions.pm @@ -0,0 +1,51 @@ +package Koha::PseudonymizedTransactions; + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; + +use Koha::Database; +use Koha::PseudonymizedTransaction; + +use base qw(Koha::Objects); + +=head1 NAME + +Koha::PseudonymizedTransactions - Koha PseudonymizedTransaction Object set class + +=head1 API + +=cut + +=head2 Class Methods + +=cut + +=head3 type + +=cut + +sub _type { + return 'PseudonymizedTransaction'; +} + +sub object_class { + return 'Koha::PseudonymizedTransaction'; +} + +1; -- 2.20.1