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

(-)a/misc/cronjobs/j2a.pl (-17 / +219 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
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
3
use strict;
20
use strict;
4
#use warnings; FIXME - Bug 2505
21
use warnings;
22
5
BEGIN {
23
BEGIN {
6
    # find Koha's Perl modules
24
    # find Koha's Perl modules
7
    # test carefully before changing this
25
    # test carefully before changing this
Lines 9-34 BEGIN { Link Here
9
    eval { require "$FindBin::Bin/../kohalib.pl" };
27
    eval { require "$FindBin::Bin/../kohalib.pl" };
10
}
28
}
11
29
30
12
use C4::Context;
31
use C4::Context;
13
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
32
use C4::Members;
14
                                            = localtime(time);
33
use Getopt::Long;
34
use Pod::Usage;
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
=head1 OPTIONS
53
54
=over 8
55
56
=item B<--help>
57
58
Print a brief help message and exits.
59
60
=item B<--man>
61
62
Prints the manual page and exits.
63
64
=item B<-v>
65
66
Verbose. Without this flag set, only fatal errors are reported.
67
68
=item B<-n>
69
70
No Action. With this flag set, script will report changes but not actually execute them on the database.
71
72
=item B<-b>
73
74
changes patrons for one specific branch. Use the value in the
75
branches.branchcode table.
76
77
=item B<-f>
78
79
*required* defines the juvenile category to update. Expects the code from categories.categorycode.
80
81
=item B<-t>
82
83
*required* defines the category juvenile patrons will be converted to. Expects the code from categories.categorycode.
84
85
=back
86
87
=head1 DESCRIPTION
88
89
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.
90
91
=head1 USAGE EXAMPLES
92
93
C<juv2adult.pl> - Suggests that you read this help. :)
94
95
C<juv2adult.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode>  - Processes a single branch, and updates the patron categories from fromcat to tocat.
96
97
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.
98
=cut
99
100
# These variables are set by command line options.
101
# They are initially set to default values.
102
103
104
my $help    = 0;
105
my $man     = 0;
106
my $verbose = 0;
107
my $noaction = 0;
108
my $mybranch;
109
my $fromcat;
110
my $tocat;
111
112
GetOptions(
113
    'help|?'         => \$help,
114
    'man'            => \$man,
115
    'v'              => \$verbose,
116
    'n'				 => \$noaction,
117
    'f=s'	     => \$fromcat,
118
    't=s'	     => \$tocat,
119
    'b=s'      => \$mybranch,
120
) or pod2usage(2);
121
pod2usage(1) if $help;
122
pod2usage( -verbose => 2 ) if $man;
123
124
if(not $fromcat && $tocat) { #make sure we've specified the info we need.
125
	print "please specify -help for usage tips.\n";
126
		exit;
127
}
128
129
my $dbh=C4::Context->dbh;
130
my @branches = C4::Branch::GetBranches();
131
#get today's date, format it and subtract upperagelimit
132
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
15
$year +=1900;
133
$year +=1900;
16
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
134
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
17
if ($mday < 10) {$mday = "0".$mday;}
135
if ($mday < 10) {$mday = "0".$mday;}
136
# get the upperagelimit from the category to be transitioned from
137
my $query=qq|SELECT upperagelimit from categories where categorycode =?|;
138
my $sth=$dbh->prepare( $query );
139
$sth->execute( $fromcat )
140
   or die "Couldn't execute statement: " . $sth->errstr;
141
my $agelimit = $sth->fetchrow_array();
142
if ( not $agelimit ) {
18
143
19
$year -=18; #18 year olds here: if your J turns to A before this change here
144
	die "No patron category $fromcat. Please try again. \n";
20
my $dbh=C4::Context->dbh;
145
}
146
$query=qq|SELECT categorycode from categories where categorycode=?|;
147
$sth=$dbh->prepare( $query );
148
$sth->execute( $tocat )
149
	or die "Couldn't execute statement: " . $sth->errstr;
150
my $tocatage = $sth->fetchrow_array();
151
if ( not $tocatage ){
152
	die "No patron category $tocat. Please try again. \n";
153
}
154
$sth->finish(  );
155
$year -=$agelimit;
156
157
$verbose and print "The age limit for category $fromcat is $agelimit\n";
21
158
22
#get today's date, format it and subtract 18 yrs.
23
my $itsyourbirthday = "$year-$mon-$mday";
159
my $itsyourbirthday = "$year-$mon-$mday";
24
160
25
my $query=qq|UPDATE borrowers 
161
26
	   SET categorycode='A',
162
if ( not $noaction) {
27
	    guarantorid ='0'	 
163
	if ( $mybranch ) { #yep, we received a specific branch to work on.
28
	   WHERE dateofbirth<=? 
164
		$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' 
165
		my $query=qq|UPDATE borrowers
30
	   AND categorycode IN (select categorycode from categories where category_type='C')|;
166
		   SET guarantorid ='0',
31
my $sth=$dbh->prepare($query);
167
		    categorycode =?
32
my $res = $sth->execute($itsyourbirthday) or die "can't execute";
168
		   WHERE dateofbirth<=?
33
print "$res\n"; #did it work?
169
		   AND dateofbirth!='0000-00-00'
170
		   AND branchcode=?
171
		   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
172
		my $sth=$dbh->prepare($query);
173
		my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute";
174
		if ($res eq '0E0') { print "No patrons updated\n";
175
		} else { print "Updated $res patrons\n"; }
176
	} else { # branch was not supplied, processing all branches
177
		$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
178
		foreach my $branchcode (@branches) {
179
			my $query=qq|UPDATE borrowers
180
			   SET guarantorid ='0',
181
			    categorycode =?
182
			   WHERE dateofbirth<=?
183
			   AND dateofbirth!='0000-00-00'
184
			   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
185
			my $sth=$dbh->prepare($query);
186
			my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute";
187
			if ($res eq '0E0') { print "No patrons updated\n";
188
			} else { print "Updated $res patrons\n"; }
189
	}
190
	}
191
} else {
192
	my $birthday;
193
	if ( $mybranch ) {
194
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
195
		my $query=qq|SELECT firstname,
196
		 surname,
197
		 cardnumber,
198
		 dateofbirth
199
		FROM borrowers
200
		WHERE dateofbirth<=?
201
		AND dateofbirth!='0000-00-00'
202
		AND branchcode=?
203
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
204
		my $sth=$dbh->prepare( $query );
205
		$sth->execute( $itsyourbirthday, $mybranch, $fromcat )
206
		   or die "Couldn't execute statement: " . $sth->errstr;
207
		while ( my @res = $sth->fetchrow_array()) {
208
	            my $firstname = $res[0];
209
	            my $surname = $res[1];
210
	            my $barcode = $res[2];
211
	            $birthday = $res[3];
212
	            print "$firstname $surname $barcode $birthday\n";
213
	          }
214
	} else {
215
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
216
		my $query=qq|SELECT firstname,
217
		 surname,
218
		 cardnumber,
219
		 dateofbirth
220
		FROM borrowers
221
		WHERE dateofbirth<=?
222
		AND dateofbirth!='0000-00-00'
223
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
224
		my $sth=$dbh->prepare( $query );
225
		$sth->execute( $itsyourbirthday, $fromcat )
226
		   or die "Couldn't execute statement: " . $sth->errstr;
227
		while ( my @res = $sth->fetchrow_array()) {
228
	            my $firstname = $res[0];
229
	            my $surname = $res[1];
230
	            my $barcode = $res[2];
231
	            $birthday = $res[3];
232
	            print "$firstname $surname $barcode $birthday\n";
233
	          }
234
	}
235
	$sth->finish(  );
236
}
34
$dbh->disconnect();
237
$dbh->disconnect();
35
- 

Return to bug 7157