From 926ebbd74f26a6d1b6248ef13146f8ea8c84c3d9 Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Wed, 15 Feb 2017 16:34:04 +0000 Subject: [PATCH] Bug 17168 - Add a command line script for updating patron category based on status This patch adds a new script update_patrons_category.pl which allows for updating patron categories in a batch upon meeting provided criteria. This script additionally can replace j2a.pl. To test: 1 - perl update_patrons_category.pl -h 2 - Ensure help text makes sense and covers all options 3 - Test converting patrons supplying only fromcat and tocat perl update_patrons_category.pl -f PT -t J -v perl update_patrons_category.pl -f PT -t J -v 4 - All patrons should have been switched to and from Juveniles 5 - Try with -n switch perl update_patrons_category.pl -f PT -t J -v -n 6 - Should list all patrons but not update 7 - Set the age for juvenile patrons to be outside the range provided in categories (or set the upper age limit for juveniles to '2') 8 - Test with verbosity and with without -n perl update_patrons_category.pl -f J -a -t PT -v -n perl update_patrons_category.pl -f J -a -t PT -v 9 - Repeat above and verify linked/unlinked guarantors are removed in above scenario 10 - Test various fine and registration limits 11 - Test matching on specific fields i.e. --field surname=acosta 12 - Sign off --- misc/cronjobs/update_patrons_category.pl | 276 +++++++++++++++++++++++++++++++ 1 file changed, 276 insertions(+) create mode 100644 misc/cronjobs/update_patrons_category.pl diff --git a/misc/cronjobs/update_patrons_category.pl b/misc/cronjobs/update_patrons_category.pl new file mode 100644 index 0000000..27c41cd --- /dev/null +++ b/misc/cronjobs/update_patrons_category.pl @@ -0,0 +1,276 @@ +#!/usr/bin/perl + +# 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, see . + +use Modern::Perl; + +BEGIN { + # find Koha's Perl modules + # test carefully before changing this + use FindBin; + eval { require "$FindBin::Bin/../kohalib.pl" }; +} + +use C4::Context; +use Getopt::Long; +use Pod::Usage; +use Koha::Logger; +use Koha::Patrons; +use Koha::Patron::Categories; +use Koha::DateUtils; +use Data::Dumper; + +=head1 NAME + +update_patrons_category.pl - Given a set of parameters update selected patrons from one catgeory to another + +=head1 SYNOPSIS + +update_patrons_category.pl [ -b= -f= -t= ] + + Options: + --help brief help message + --man full documentation + -a update based on age restriction of current category + -fo=X update if fines over X amount + -fu=X update if fines under X amount + -rb=date update if registration date is before given date + -ra=date update if registration date is after a given date + --field name=value where is a column in the borrowers table, patrons will be updated if the field is equal to given + -v verbose mode + -n take no action, display only + -b only deal with patrons from this library/branch + -f change patron category from this category + -t change patron category to this category + +=head1 OPTIONS + +=over 8 + +=item B<--help> + +Print a brief help message and exits. + +=item B<--man> + +Prints the manual page and exits. + +=item B<-v> + +Verbose. Without this flag set, only fatal errors are reported. + +=item B<-n> + +No Action. With this flag set, script will report changes but not actually execute them on the database. + +=item B<-b> + +changes patrons for one specific branch. Use the value in the +branches.branchcode table. + +=item B<-f> + +*required* defines the category to update. Expects the code from categories.categorycode. + +=item B<-t> + +*required* defines the category patrons will be converted to. Expects the code from categories.categorycode. + +=back + +=item B<-a> + +Update patron only if they are outside the age range specified for the 'from' category. + +=back + +=item B<-fo=X> + +Supply a number and only account with fines over this number will be updated. + +=back + +=item B<-fu=X> + +Supply a number and only account with fines under this number will be updated. + +=back + +=item B<-rb=date> + +Enter a date in ISO format YYYY-MM-DD and only patrons registered before this date wil be updated. + +=back + +=item B<-ra=date> + +Enter a date in ISO format YYYY-MM-DD and only patrons registered after this date wil be updated. + +=back + +=item B<--field column=value> + +Use this flag to specify a column in the borrowers table and update only patrons whose value in that column matches the value supplied (repeatable) + +e.g. +--field dateexpiry=2016-01-01 +will update all patrons who expired on that date, useful for schools etc. + +=back + +=head1 DESCRIPTION + +This script is designed to update patrons from one category to another. + +=head1 USAGE EXAMPLES + +C - Suggests that you read this help. :) + +C -b= -f= -t= - Processes a single branch, and updates the patron categories from fromcat to tocat. + +C -b= -f= -t= -a - Processes a single branch, and updates the patron categories from fromcat to tocat for patrons outside the age range of fromcat. + +C -f= -t= -v -n - Processes all branches, shows all messages, and reports the patrons who would be affected. Takes no action on the database. +=cut + +# These variables are set by command line options. +# They are initially set to default values. + +my $help = 0; +my $man = 0; +my $verbose = 0; +my $noaction = 0; +my $age = 0; +my $fine_min; +my $fine_max; +my $fromcat; +my $tocat; +my $reg_bef; +my $reg_aft; +my $branch_lim; +my %fields; + +GetOptions( + 'help|?' => \$help, + 'man' => \$man, + 'v' => \$verbose, + 'n' => \$noaction, + 'f=s' => \$fromcat, + 't=s' => \$tocat, + 'a' => \$age, + 'fo=s' => \$fine_min, + 'fu=s' => \$fine_max, + 'rb=s' => \$reg_bef, + 'ra=s' => \$reg_aft, + 'b=s' => \$branch_lim, + 'field=s' => \%fields +) or pod2usage(2); +pod2usage(1) if $help; +pod2usage( -verbose => 2 ) if $man; + +if ( not $fromcat && $tocat ) { #make sure we've specified the info we need. + print "Must supply category from and to (-f & -t) please specify -help for usage tips.\n"; + exit; +} + +($verbose && $noaction) and print "No actions will be taken (test mode)\n"; + +$verbose and print "Will update patrons from $fromcat to $tocat with conditions below (if any)\n"; + +my %params; +my %query; + +if ( $reg_bef || $reg_aft ){ + my $date_bef; + my $date_aft; + if (defined $reg_bef) {eval { $date_bef = dt_from_string( $reg_bef, 'iso' ); };} + die "$reg_bef is not a valid date before, aborting! Use a date in format YYYY-MM-DD.$@" + if $@; + if (defined $reg_aft) {eval { $date_aft = dt_from_string( $reg_aft, 'iso' ); };} + die "$reg_bef is not a valid date after, aborting! Use a date in format YYYY-MM-DD.$@" + if $@; + $params{dateenrolled}{'<='}=$reg_bef if defined $date_bef; + $params{dateenrolled}{'>='}=$reg_aft if defined $date_aft; +} + +if ($age && ($fine_min || $fine_max) ){ + %query = ( + join => ["accountlines" => "categorycode"], + select => ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ], + as => [qw/borrowernumber total_fines/], + where => \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN IFNULL(dateofbirthrequired,0) AND IFNULL( upperagelimit, 999 ) '], + group_by => ["borrowernumber"], + having => [{"total_fines" => { '>=', $fine_min, '<=', $fine_max }}], + ); + $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max; + $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min; +} +else{ + if ($age) { + $query{join} = ["categorycode"]; + $query{where} = \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN IFNULL(dateofbirthrequired,0) AND IFNULL( upperagelimit, 999 ) ']; + } + if ($fine_min || $fine_max) { + $query{join} = ["accountlines"]; + $query{select} = ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ]; + $query{as} = [qw/borrowernumber total_fines/]; + $query{group_by} = ["borrowernumber"]; + $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max; + $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min; + } +} + +$params{"me.categorycode"} = $fromcat if defined $fromcat; +$params{"me.branchcode"} = $branch_lim if defined $branch_lim; + +if ($verbose) { + if (defined $reg_bef) {print "Condition: Registered before $reg_bef\n";} + if (defined $reg_aft) {print "Condition: Registered before $reg_aft\n";} + if (defined $fine_min) {print "Condition: Total fines more than $fine_min\n";} + if (defined $fine_max) {print "Condition: Total fines less than $fine_max\n";} + if (defined $age) {print "Condition: Age is outside range defined for $fromcat\n";} + if (defined $branch_lim) {print "Condition: Branchcode of patron is $branch_lim\n";} +} + +while (my ($key,$value) = each %fields ) { + $verbose and print "Condition: borrower column $key is equal to $value\n"; + $params{"me.".$key} = $value; +} + +my $target_patrons = Koha::Patrons->search(\%params,\%query); + +my $counter = 0; + +while ( my $target_patron = $target_patrons->next() ){ + $counter++; + my $target = Koha::Patrons->find( $target_patron->borrowernumber ); + $verbose and print "Updating ".$target->firstname." ".$target->surname." from $fromcat to $tocat\n"; + if ( Koha::Patron::Categories->find($fromcat)->category_type eq 'C' && Koha::Patron::Categories->find($tocat)->category_type eq 'A' ){ + $verbose and print "Removing guarantor info\n"; + $target->guarantorid(0); + $target->contactname(''); + $target->contactfirstname(''); + $target->contacttitle(''); + $target->relationship(''); + } + unless ($noaction) { + $target->categorycode($tocat); + $target->store(); + } +} + +$verbose and print "$counter patrons updated\n"; + -- 2.1.4