misc/cronjobs/j2a.pl

   1 #!/usr/bin/perl
   2
   3 # 2011 Liz Rea - Northeast Kansas Library System <lrea@nekls.org>
   4
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 use Modern::Perl;
  21
  22 BEGIN {
  23     # find Koha's Perl modules
  24     # test carefully before changing this
  25     use FindBin;
  26     eval { require "$FindBin::Bin/../kohalib.pl" };
  27 }
  28
  29 use Koha::Script -cron;
  30 use C4::Context;
  31 use C4::Members;
  32 use Getopt::Long;
  33 use Pod::Usage;
  34 use C4::Log;
  35
  36 =head1 NAME
  37
  38 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.
  39
  40 =head1 SYNOPSIS
  41
  42 juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ]
  43
  44  Options:
  45    --help   brief help message
  46    --man    full documentation
  47    -v       verbose mode
  48    -n       take no action, display only
  49    -b       <branchname>        only deal with patrons from this library/branch
  50    -f       <categorycode>      change patron category from this category
  51    -t       <categorycode>      change patron category to this category
  52
  53 =head1 OPTIONS
  54
  55 =over 8
  56
  57 =item B<--help>
  58
  59 Print a brief help message and exits.
  60
  61 =item B<--man>
  62
  63 Prints the manual page and exits.
  64
  65 =item B<-v>
  66
  67 Verbose. Without this flag set, only fatal errors are reported.
  68
  69 =item B<-n>
  70
  71 No Action. With this flag set, script will report changes but not actually execute them on the database.
  72
  73 =item B<-b>
  74
  75 changes patrons for one specific branch. Use the value in the
  76 branches.branchcode table.
  77
  78 =item B<-f>
  79
  80 *required* defines the juvenile category to update. Expects the code from categories.categorycode.
  81
  82 =item B<-t>
  83
  84 *required* defines the category juvenile patrons will be converted to. Expects the code from categories.categorycode.
  85
  86 =back
  87
  88 =head1 DESCRIPTION
  89
  90 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.
  91
  92 =head1 USAGE EXAMPLES
  93
  94 C<juv2adult.pl> - Suggests that you read this help. :)
  95
  96 C<juv2adult.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode>  - Processes a single branch, and updates the patron categories from fromcat to tocat.
  97
  98 C<juv2adult.pl> -f=<categorycode> -t=<categorycode> -v -n - Processes all branches, shows all messages, and reports the patrons who would be affected. Takes no action on the database.
  99
 100 =cut
 101
 102 # These variables are set by command line options.
 103 # They are initially set to default values.
 104
 105 my $help     = 0;
 106 my $man      = 0;
 107 my $verbose  = 0;
 108 my $noaction = 0;
 109 my $mybranch;
 110 my $fromcat;
 111 my $tocat;
 112
 113 GetOptions(
 114     'help|?' => \$help,
 115     'man'    => \$man,
 116     'v'      => \$verbose,
 117     'n'      => \$noaction,
 118     'f=s'    => \$fromcat,
 119     't=s'    => \$tocat,
 120     'b=s'    => \$mybranch,
 121 ) or pod2usage(2);
 122 pod2usage(1) if $help;
 123 pod2usage( -verbose => 2 ) if $man;
 124
 125 if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
 126     print "please specify -help for usage tips.\n";
 127     exit;
 128 }
 129
 130 cronlogaction();
 131
 132 my $dbh      = C4::Context->dbh;
 133 my $database = Koha::Database->new();
 134 my $schema   = $database->schema;
 135
 136 #get today's date, format it and subtract upperagelimit
 137 my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
 138   localtime(time);
 139 $year += 1900;
 140 $mon  += 1;
 141 if ( $mon < 10 )  { $mon  = "0" . $mon; }
 142 if ( $mday < 10 ) { $mday = "0" . $mday; }
 143
 144 # get the upperagelimit from the category to be transitioned from
 145 my $query = qq|SELECT upperagelimit from categories where categorycode =?|;
 146 my $sth   = $dbh->prepare($query);
 147 $sth->execute($fromcat)
 148   or die "Couldn't execute statement: " . $sth->errstr;
 149 my $agelimit = $sth->fetchrow_array();
 150 if ( not $agelimit ) {
 151     die "No patron category $fromcat. Please try again. \n";
 152 }
 153
 154 $query = qq|SELECT categorycode from categories where categorycode=?|;
 155 $sth   = $dbh->prepare($query);
 156 $sth->execute($tocat)
 157   or die "Couldn't execute statement: " . $sth->errstr;
 158 my $tocatage = $sth->fetchrow_array();
 159 if ( not $tocatage ) {
 160     die "No patron category $tocat. Please try again. \n";
 161 }
 162
 163 $year -= $agelimit;
 164
 165 $verbose and print "The age limit for category $fromcat is $agelimit\n";
 166
 167 my $itsyourbirthday = "$year-$mon-$mday";
 168
 169 if ( not $noaction ) {
 170     # Start a transaction since we need to delete from relationships and update borrowers atomically
 171
 172     my $success = 1;
 173     if ($mybranch) {    #yep, we received a specific branch to work on.
 174         $verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
 175         my $where = qq|
 176             WHERE dateofbirth <= ?
 177               AND dateofbirth != '0000-00-00'
 178               AND branchcode = ?
 179               AND categorycode IN (
 180                 SELECT categorycode
 181                 FROM categories
 182                 WHERE category_type = 'C'
 183                   AND categorycode = ?
 184               )
 185         |;
 186
 187         $schema->storage->txn_begin;
 188
 189         my $query = qq|
 190             DELETE borrower_relationships FROM borrower_relationships
 191             LEFT JOIN borrowers ON ( borrowers.borrowernumber = borrower_relationships.guarantee_id )
 192             $where
 193         |;
 194         my $sth = $dbh->prepare($query);
 195         $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
 196           or $success = 0;
 197
 198         $query = qq|
 199             UPDATE borrowers
 200             SET categorycode = ?
 201             $where
 202         |;
 203         $sth = $dbh->prepare($query);
 204         my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat )
 205           or $success = 0;
 206
 207         if ( $success ) {
 208             $schema->storage->txn_commit;
 209         } else {
 210             $schema->storage->txn_rollback;
 211             die "can't execute";
 212         }
 213
 214         if ( $res eq '0E0' ) {
 215             print "No patrons updated\n";
 216         }
 217         else {
 218             print "Updated $res patrons\n";
 219         }
 220     }
 221     else {    # branch was not supplied, processing all branches
 222         $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
 223         my $where = qq|
 224             WHERE dateofbirth <= ?
 225               AND dateofbirth!='0000-00-00'
 226               AND categorycode IN (
 227                 SELECT categorycode
 228                 FROM categories
 229                 WHERE category_type = 'C'
 230                   AND categorycode = ?
 231               )
 232         |;
 233
 234         my $query = qq|
 235             DELETE borrower_relationships FROM borrower_relationships
 236             LEFT JOIN borrowers ON ( borrowers.borrowernumber = borrower_relationships.guarantee_id )
 237             $where
 238         |;
 239         my $sth = $dbh->prepare($query);
 240         $sth->execute( $itsyourbirthday, $fromcat )
 241           or $success = 0;
 242
 243         $query = qq|
 244             UPDATE borrowers
 245             SET categorycode = ?
 246             $where
 247         |;
 248         $sth = $dbh->prepare($query);
 249         my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat )
 250           or $success = 0;
 251         $dbh->commit;
 252
 253         if ( $success ) {
 254             $dbh->commit;
 255         } else {
 256             $dbh->rollback;
 257             die "can't execute";
 258         }
 259
 260         if ( $res eq '0E0' ) {
 261             print "No patrons updated\n";
 262         }
 263         else {
 264             print "Updated $res patrons\n";
 265         }
 266     }
 267 }
 268 else {
 269     my $birthday;
 270     if ($mybranch) {
 271         $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
 272         my $query = qq|
 273             SELECT firstname,
 274                    surname,
 275                    cardnumber,
 276                    dateofbirth
 277             FROM borrowers
 278             WHERE dateofbirth <= ?
 279               AND dateofbirth != '0000-00-00'
 280               AND branchcode = ?
 281               AND categorycode IN (
 282                 SELECT categorycode
 283                 FROM categories
 284                 WHERE category_type = 'C'
 285                   AND categorycode = ?
 286               )
 287         |;
 288         my $sth = $dbh->prepare($query);
 289         $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
 290           or die "Couldn't execute statement: " . $sth->errstr;
 291
 292         while ( my @res = $sth->fetchrow_array() ) {
 293             my $firstname = $res[0];
 294             my $surname   = $res[1];
 295             my $barcode   = $res[2];
 296             $birthday = $res[3];
 297             print "$firstname $surname $barcode $birthday\n";
 298         }
 299     }
 300     else {
 301         $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
 302         my $query = qq|
 303             SELECT firstname,
 304                    surname,
 305                    cardnumber,
 306                    dateofbirth
 307             FROM borrowers
 308             WHERE dateofbirth <= ?
 309               AND dateofbirth != '0000-00-00'
 310               AND categorycode IN (
 311                 SELECT categorycode
 312                 FROM categories
 313                 WHERE category_type = 'C'
 314                   AND categorycode = ?
 315               )
 316         |;
 317         my $sth = $dbh->prepare($query);
 318         $sth->execute( $itsyourbirthday, $fromcat )
 319           or die "Couldn't execute statement: " . $sth->errstr;
 320         $dbh->commit;
 321
 322         while ( my @res = $sth->fetchrow_array() ) {
 323             my $firstname = $res[0];
 324             my $surname   = $res[1];
 325             my $barcode   = $res[2];
 326             $birthday = $res[3];
 327             print "$firstname $surname $barcode $birthday\n";
 328         }
 329     }
 330 }