[Patches] [PATCH] Bug 7157 - Improve the j2a.pl cronjob
koha-patchbot at kohaaloha.com
koha-patchbot at kohaaloha.com
Tue Dec 13 22:05:49 NZDT 2011
From: Liz Rea <lrea at nekls.org>
Date: Fri, 4 Nov 2011 13:20:23 -0500
Subject: [PATCH] Bug 7157 - Improve the j2a.pl cronjob
Content-Type: text/plain; charset="utf-8"
- Calculates updates date based on the upper age limit defined in the patron categories.
- Allows libraries to work on all branches or only one.
- Allows libraries to specify which Adult patron category to update child categories to.
- Allows libraries to specify a single Child patron category to update to an adult category.
- Has a test mode to display what transforms would be done on the database without executing the changes.
Includes improved help, copyright statement, and uses warnings. Also incorporates Paul's suggestions regarding --help and --man.
To test:
Create two patron categories, a child and an adult category. Make sure they
have an upper age limit.
Create or modify some patrons in multiple branches that fall into the category
of "my birthdate is less than or equal to today's date minus the upper age
limit"
1. Run the script with no flags - nothing should happen, it will suggest you try the -help flag.
2. Run the script with the -help flag - you should see the help
3. Run the script with the -fromcat=<child category> -tocat=<adult category> -v -n - should show you results from all branches but take no action and tell you what its computations are.
4. Run the script with the -fromcat=<child category> -tocat=<adult category> -branch=<branchcode> -v -n - should show you results from your specified branch, but take no action and tell you what it's computations are.
5. Run the script with the -fromcat=<child category> -tocat=<adult category> -v -branch=<branchcode> - should show you the computations and tell you how many patrons were modified in your single branch. It will not show you the information on which patrons were updated.
6. Run the script with the -fromcat=<child category> -tocat=<adult category> -v - should show you the computations and tell you how many patrons were modified across all branches.
7. Run the script without the -v flag, if you care what the non-verbose output looks like.
Known limitation - if you give it an unknown tocat, it will fail with a rather ugly error. I'm going to fix that but in another patch.
There is more this script could do, please feel free to take it and run.
---
misc/cronjobs/j2a.pl | 230 ++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 214 insertions(+), 16 deletions(-)
mode change 100755 => 100644 misc/cronjobs/j2a.pl
diff --git a/misc/cronjobs/j2a.pl b/misc/cronjobs/j2a.pl
old mode 100755
new mode 100644
index ec5d207..b445b10
--- a/misc/cronjobs/j2a.pl
+++ b/misc/cronjobs/j2a.pl
@@ -1,7 +1,26 @@
#!/usr/bin/perl
-#run nightly -- changes J to A on someone's 18th birthday
+
+# 2011 Liz Rea - Northeast Kansas Library System <lrea at nekls.org>
+
+# 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 2 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., 59 Temple Place,
+# Suite 330, Boston, MA 02111-1307 USA
+#
+
use strict;
-#use warnings; FIXME - Bug 2505
+use warnings;
+
BEGIN {
# find Koha's Perl modules
# test carefully before changing this
@@ -9,26 +28,205 @@ BEGIN {
eval { require "$FindBin::Bin/../kohalib.pl" };
}
+
use C4::Context;
-my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
- = localtime(time);
+use C4::Members;
+use Getopt::Long;
+use Pod::Usage;
+
+=head1 NAME
+
+juv2adult.pl - convert juvenile/child patrons from juvenile patron category and category code to corresponding adult patron category and category code when they reach the upper age limit defined in the Patron Categories.
+
+=head1 SYNOPSIS
+
+juv2adult.pl [ -branch=<branchcode> -fromcat=<categorycode> -tocat=<categorycode> ]
+
+ Options:
+ --help brief help message
+ --man full documentation
+ -v verbose mode
+ -n take no action, display only
+ -branch <branchname> only deal with patrons from this library/branch
+ -fromcat <categorycode> change patron category from this category
+ -tocat <categorycode> 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<-branch>
+
+changes patrons for one specific branch. Use the value in the
+branches.branchcode table.
+
+=item B<-fromcat>
+
+*required* defines the juvenile category to update. Expects the code from categories.categorycode.
+
+=item B<-tocat>
+
+*required* defines the category juvenile patrons will be converted to. Expects the code from categories.categorycode.
+
+=back
+
+=head1 DESCRIPTION
+
+This script is designed to update patrons from juvenile to adult patron types, remove the guarantor, and update their category codes appropriately when they reach the upper age limit defined in the Patron Categories.
+
+=head1 USAGE EXAMPLES
+
+C<juv2adult.pl> - Suggests that you read this help. :)
+
+C<juv2adult.pl> -branch=<branchcode> -fromcat=<categorycode> -tocat=<categorycode> - Processes a single branch, and updates the patron categories from fromcat to tocat.
+
+C<juv2adult.pl> -fromcat=<categorycode> -tocat=<categorycode> -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 $mybranch;
+my $fromcat;
+my $tocat;
+
+GetOptions(
+ 'help|?' => \$help,
+ 'man' => \$man,
+ 'v' => \$verbose,
+ 'n' => \$noaction,
+ 'fromcat=s' => \$fromcat,
+ 'tocat=s' => \$tocat,
+ 'branch=s' => \$mybranch,
+) 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 "please specify -help for usage tips.\n";
+ exit;
+}
+
+my $dbh=C4::Context->dbh;
+my @branches = C4::Branch::GetBranches();
+#get today's date, format it and subtract upperagelimit
+my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year +=1900;
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
if ($mday < 10) {$mday = "0".$mday;}
+# get the upperagelimit from the category to be transitioned from
+my $query=qq|SELECT upperagelimit from categories where categorycode =?|;
+my $sth=$dbh->prepare( $query );
+$sth->execute( $fromcat )
+ or die "Couldn't execute statement: " . $sth->errstr;
+my $agelimit = $sth->fetchrow_array();
+if ( not $agelimit ) {
-$year -=18; #18 year olds here: if your J turns to A before this change here
-my $dbh=C4::Context->dbh;
+ die "No patron category with that category code. Please try again. \n";
+}
+$sth->finish( );
+$year -=$agelimit;
+
+$verbose and print "The age limit for category $fromcat is $agelimit\n";
-#get today's date, format it and subtract 18 yrs.
my $itsyourbirthday = "$year-$mon-$mday";
-my $query=qq|UPDATE borrowers
- SET categorycode='A',
- guarantorid ='0'
- WHERE dateofbirth<=?
- AND dateofbirth!='0000-00-00'
- AND categorycode IN (select categorycode from categories where category_type='C')|;
-my $sth=$dbh->prepare($query);
-my $res = $sth->execute($itsyourbirthday) or die "can't execute";
-print "$res\n"; #did it work?
+
+if ( not $noaction) {
+ if ( $mybranch ) { #yep, we received a specific branch to work on.
+ $verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
+ my $query=qq|UPDATE borrowers
+ SET categorycode='A',
+ guarantorid ='0',
+ categorycode =?
+ WHERE dateofbirth<=?
+ AND dateofbirth!='0000-00-00'
+ AND branchcode=?
+ AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
+ my $sth=$dbh->prepare($query);
+ my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute";
+ if ($res eq '0E0') { print "No patrons updated\n";
+ } else { print "Updated $res patrons\n"; }
+ } else { # branch was not supplied, processing all branches
+ $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
+ foreach my $branchcode (@branches) {
+ my $query=qq|UPDATE borrowers
+ SET categorycode='A',
+ guarantorid ='0',
+ categorycode =?
+ WHERE dateofbirth<=?
+ AND dateofbirth!='0000-00-00'
+ AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
+ my $sth=$dbh->prepare($query);
+ my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute";
+ if ($res eq '0E0') { print "No patrons updated\n";
+ } else { print "Updated $res patrons\n"; }
+ }
+ }
+} else {
+ my $birthday;
+ if ( $mybranch ) {
+ $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
+ my $query=qq|SELECT firstname,
+ surname,
+ cardnumber,
+ dateofbirth
+ FROM borrowers
+ WHERE dateofbirth<=?
+ AND dateofbirth!='0000-00-00'
+ AND branchcode=?
+ AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
+ my $sth=$dbh->prepare( $query );
+ $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
+ or die "Couldn't execute statement: " . $sth->errstr;
+ while ( my @res = $sth->fetchrow_array()) {
+ my $firstname = $res[0];
+ my $surname = $res[1];
+ my $barcode = $res[2];
+ $birthday = $res[3];
+ print "$firstname $surname $barcode $birthday\n";
+ }
+ } else {
+ $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
+ my $query=qq|SELECT firstname,
+ surname,
+ cardnumber,
+ dateofbirth
+ FROM borrowers
+ WHERE dateofbirth<=?
+ AND dateofbirth!='0000-00-00'
+ AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
+ my $sth=$dbh->prepare( $query );
+ $sth->execute( $itsyourbirthday, $fromcat )
+ or die "Couldn't execute statement: " . $sth->errstr;
+ while ( my @res = $sth->fetchrow_array()) {
+ my $firstname = $res[0];
+ my $surname = $res[1];
+ my $barcode = $res[2];
+ $birthday = $res[3];
+ print "$firstname $surname $barcode $birthday\n";
+ }
+ }
+ $sth->finish( );
+}
$dbh->disconnect();
--
1.7.2.5
More information about the Patches
mailing list