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

(-)a/misc/cronjobs/j2a.pl (-17 / +222 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 [ -b=<branchcode> -f=<categorycode> -t=<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
   -b	<branchname>	only deal with patrons from this library/branch
51
   -f	<categorycode>	change patron category from this category
52
   -t	<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<-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
=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
    '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
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
if ( not $agelimit ) {
18
144
19
$year -=18; #18 year olds here: if your J turns to A before this change here
145
	die "No patron category $fromcat. Please try again. \n";
20
my $dbh=C4::Context->dbh;
146
}
147
$query=qq|SELECT categorycode from categories where categorycode=?|;
148
$sth=$dbh->prepare( $query );
149
$sth->execute( $tocat )
150
	or die "Couldn't execute statement: " . $sth->errstr;
151
my $tocatage = $sth->fetchrow_array();
152
if ( not $tocatage ){
153
	die "No patron category $tocat. Please try again. \n";
154
}
155
$sth->finish(  );
156
$year -=$agelimit;
157
158
$verbose and print "The age limit for category $fromcat is $agelimit\n";
21
159
22
#get today's date, format it and subtract 18 yrs.
23
my $itsyourbirthday = "$year-$mon-$mday";
160
my $itsyourbirthday = "$year-$mon-$mday";
24
161
25
my $query=qq|UPDATE borrowers 
162
26
	   SET categorycode='A',
163
if ( not $noaction) {
27
	    guarantorid ='0'	 
164
	if ( $mybranch ) { #yep, we received a specific branch to work on.
28
	   WHERE dateofbirth<=? 
165
		$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' 
166
		my $query=qq|UPDATE borrowers
30
	   AND categorycode IN (select categorycode from categories where category_type='C')|;
167
		   SET categorycode='A',
31
my $sth=$dbh->prepare($query);
168
		    guarantorid ='0',
32
my $res = $sth->execute($itsyourbirthday) or die "can't execute";
169
		    categorycode =?
33
print "$res\n"; #did it work?
170
		   WHERE dateofbirth<=?
171
		   AND dateofbirth!='0000-00-00'
172
		   AND branchcode=?
173
		   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
174
		my $sth=$dbh->prepare($query);
175
		my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute";
176
		if ($res eq '0E0') { print "No patrons updated\n";
177
		} else { print "Updated $res patrons\n"; }
178
	} else { # branch was not supplied, processing all branches
179
		$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
180
		foreach my $branchcode (@branches) {
181
			my $query=qq|UPDATE borrowers
182
			   SET categorycode='A',
183
			    guarantorid ='0',
184
			    categorycode =?
185
			   WHERE dateofbirth<=?
186
			   AND dateofbirth!='0000-00-00'
187
			   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
188
			my $sth=$dbh->prepare($query);
189
			my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute";
190
			if ($res eq '0E0') { print "No patrons updated\n";
191
			} else { print "Updated $res patrons\n"; }
192
	}
193
	}
194
} else {
195
	my $birthday;
196
	if ( $mybranch ) {
197
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
198
		my $query=qq|SELECT firstname,
199
		 surname,
200
		 cardnumber,
201
		 dateofbirth
202
		FROM borrowers
203
		WHERE dateofbirth<=?
204
		AND dateofbirth!='0000-00-00'
205
		AND branchcode=?
206
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
207
		my $sth=$dbh->prepare( $query );
208
		$sth->execute( $itsyourbirthday, $mybranch, $fromcat )
209
		   or die "Couldn't execute statement: " . $sth->errstr;
210
		while ( my @res = $sth->fetchrow_array()) {
211
	            my $firstname = $res[0];
212
	            my $surname = $res[1];
213
	            my $barcode = $res[2];
214
	            $birthday = $res[3];
215
	            print "$firstname $surname $barcode $birthday\n";
216
	          }
217
	} else {
218
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
219
		my $query=qq|SELECT firstname,
220
		 surname,
221
		 cardnumber,
222
		 dateofbirth
223
		FROM borrowers
224
		WHERE dateofbirth<=?
225
		AND dateofbirth!='0000-00-00'
226
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
227
		my $sth=$dbh->prepare( $query );
228
		$sth->execute( $itsyourbirthday, $fromcat )
229
		   or die "Couldn't execute statement: " . $sth->errstr;
230
		while ( my @res = $sth->fetchrow_array()) {
231
	            my $firstname = $res[0];
232
	            my $surname = $res[1];
233
	            my $barcode = $res[2];
234
	            $birthday = $res[3];
235
	            print "$firstname $surname $barcode $birthday\n";
236
	          }
237
	}
238
	$sth->finish(  );
239
}
34
$dbh->disconnect();
240
$dbh->disconnect();
35
- 

Return to bug 7157