View | Details | Raw Unified | Return to bug 7157
Collapse All | Expand All

(-)a/misc/cronjobs/j2a.pl (-17 / +211 lines)
Lines 1-7 Link Here
1
#!/usr/bin/perl 
1
#!/usr/bin/perl 
2
#run nightly -- changes J to A on someone's 18th birthday
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 under the
8
# terms of the GNU General Public License as published by the Free Software
9
# Foundation; either version 2 of the License, or (at your option) any later
10
# version.
11
#
12
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14
# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
15
#
16
# You should have received a copy of the GNU General Public License along with
17
# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18
# Suite 330, Boston, MA  02111-1307 USA
19
#
20
3
use strict;
21
use strict;
4
#use warnings; FIXME - Bug 2505
22
use warnings;
23
5
BEGIN {
24
BEGIN {
6
    # find Koha's Perl modules
25
    # find Koha's Perl modules
7
    # test carefully before changing this
26
    # test carefully before changing this
Lines 9-34 BEGIN { Link Here
9
    eval { require "$FindBin::Bin/../kohalib.pl" };
28
    eval { require "$FindBin::Bin/../kohalib.pl" };
10
}
29
}
11
30
31
12
use C4::Context;
32
use C4::Context;
13
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
33
use C4::Members;
14
                                            = localtime(time);
34
use Getopt::Long;
35
use Pod::Usage;
36
37
=head1 NAME
38
39
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.
40
41
=head1 SYNOPSIS
42
43
juv2adult.pl [ -branch=<branchcode> -fromcat=<categorycode> -tocat=<categorycode> ]
44
45
 Options:
46
   -help					brief help message
47
   -man						full documentation
48
   -v						verbose mode
49
   -n						take no action, display only
50
   -branch	<branchname>	only deal with patrons from this library/branch
51
   -fromcat	<categorycode>	change patron category from this category
52
   -tocat	<categorycode>	change patron category to this category
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<-branch>
74
75
changes patrons for one specific branch. Use the value in the
76
branches.branchcode table.
77
78
=item B<-fromcat>
79
80
*required* defines the juvenile category to update. Expects the code from categories.categorycode.
81
82
=item B<-tocat>
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> -branch=<branchcode> -fromcat=<categorycode> -tocat=<categorycode>  - Processes a single branch, and updates the patron categories from fromcat to tocat.
97
98
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.
99
=cut
100
101
# These variables are set by command line options.
102
# They are initially set to default values.
103
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
    'fromcat=s'	     => \$fromcat,
119
    'tocat=s'	     => \$tocat,
120
    'branch=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
my $dbh=C4::Context->dbh;
131
my @branches = C4::Branch::GetBranches();
132
#get today's date, format it and subtract upperagelimit
133
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
15
$year +=1900;
134
$year +=1900;
16
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
135
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
17
if ($mday < 10) {$mday = "0".$mday;}
136
if ($mday < 10) {$mday = "0".$mday;}
137
# get the upperagelimit from the category to be transitioned from
138
my $query=qq|SELECT upperagelimit from categories where categorycode =?|;
139
my $sth=$dbh->prepare( $query );
140
$sth->execute( $fromcat ) 
141
   or die "Couldn't execute statement: " . $sth->errstr;
142
my $agelimit = $sth->fetchrow_array();
143
$sth->finish(  );
144
$year -=$agelimit;
18
145
19
$year -=18; #18 year olds here: if your J turns to A before this change here
146
$verbose and print "The age limit for category $fromcat is $agelimit\n";
20
my $dbh=C4::Context->dbh;
21
147
22
#get today's date, format it and subtract 18 yrs.
23
my $itsyourbirthday = "$year-$mon-$mday";
148
my $itsyourbirthday = "$year-$mon-$mday";
24
149
25
my $query=qq|UPDATE borrowers 
150
26
	   SET categorycode='A',
151
if ( not $noaction) {
27
	    guarantorid ='0'	 
152
	if ( $mybranch ) { #yep, we received a specific branch to work on.
28
	   WHERE dateofbirth<=? 
153
		$verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
29
	   AND dateofbirth!='0000-00-00' 
154
		my $query=qq|UPDATE borrowers 
30
	   AND categorycode IN (select categorycode from categories where category_type='C')|;
155
		   SET categorycode='A',
31
my $sth=$dbh->prepare($query);
156
		    guarantorid ='0',	 
32
my $res = $sth->execute($itsyourbirthday) or die "can't execute";
157
		    categorycode =?
33
print "$res\n"; #did it work?
158
		   WHERE dateofbirth<=? 
159
		   AND dateofbirth!='0000-00-00' 
160
		   AND branchcode=?
161
		   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
162
		my $sth=$dbh->prepare($query);
163
		my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute";
164
		if ($res eq '0E0') { print "No patrons updated\n"; 
165
		} else { print "Updated $res patrons\n"; }
166
	} else { # branch was not supplied, processing all branches
167
		$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
168
		foreach my $branchcode (@branches) {
169
			my $query=qq|UPDATE borrowers 
170
			   SET categorycode='A',
171
			    guarantorid ='0',	 
172
			    categorycode =?
173
			   WHERE dateofbirth<=? 
174
			   AND dateofbirth!='0000-00-00' 
175
			   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
176
			my $sth=$dbh->prepare($query);
177
			my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute";
178
			if ($res eq '0E0') { print "No patrons updated\n"; 
179
			} else { print "Updated $res patrons\n"; }
180
	}
181
	}	
182
} else {
183
	my $birthday;
184
	if ( $mybranch ) {
185
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
186
		my $query=qq|SELECT firstname, 
187
		 surname, 
188
		 cardnumber, 
189
		 dateofbirth 
190
		FROM borrowers
191
		WHERE dateofbirth<=? 
192
		AND dateofbirth!='0000-00-00' 
193
		AND branchcode=?
194
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
195
		my $sth=$dbh->prepare( $query );
196
		$sth->execute( $itsyourbirthday, $mybranch, $fromcat ) 
197
		   or die "Couldn't execute statement: " . $sth->errstr;
198
		while ( my @res = $sth->fetchrow_array()) {
199
	            my $firstname = $res[0];
200
	            my $surname = $res[1];
201
	            my $barcode = $res[2];
202
	            $birthday = $res[3];
203
	            print "$firstname $surname $barcode $birthday\n";
204
	          }
205
	} else { 
206
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
207
		my $query=qq|SELECT firstname, 
208
		 surname, 
209
		 cardnumber, 
210
		 dateofbirth 
211
		FROM borrowers
212
		WHERE dateofbirth<=? 
213
		AND dateofbirth!='0000-00-00' 
214
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
215
		my $sth=$dbh->prepare( $query );
216
		$sth->execute( $itsyourbirthday, $fromcat ) 
217
		   or die "Couldn't execute statement: " . $sth->errstr;
218
		while ( my @res = $sth->fetchrow_array()) {
219
	            my $firstname = $res[0];
220
	            my $surname = $res[1];
221
	            my $barcode = $res[2];
222
	            $birthday = $res[3];
223
	            print "$firstname $surname $barcode $birthday\n";
224
	          }
225
	}  
226
	$sth->finish(  );
227
}
228
	
34
$dbh->disconnect();
229
$dbh->disconnect();
35
- 

Return to bug 7157