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

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

Return to bug 7157