|
Lines 17-24
Link Here
|
| 17 |
# You should have received a copy of the GNU General Public License |
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>. |
18 |
# along with Koha; if not, see <http://www.gnu.org/licenses>. |
| 19 |
|
19 |
|
| 20 |
use strict; |
20 |
use Modern::Perl; |
| 21 |
use warnings; |
|
|
| 22 |
|
21 |
|
| 23 |
BEGIN { |
22 |
BEGIN { |
| 24 |
# find Koha's Perl modules |
23 |
# find Koha's Perl modules |
|
Lines 27-33
BEGIN {
Link Here
|
| 27 |
eval { require "$FindBin::Bin/../kohalib.pl" }; |
26 |
eval { require "$FindBin::Bin/../kohalib.pl" }; |
| 28 |
} |
27 |
} |
| 29 |
|
28 |
|
| 30 |
|
|
|
| 31 |
use C4::Context; |
29 |
use C4::Context; |
| 32 |
use C4::Members; |
30 |
use C4::Members; |
| 33 |
use Getopt::Long; |
31 |
use Getopt::Long; |
|
Lines 43-55
juv2adult.pl - convert juvenile/child patrons from juvenile patron category and
Link Here
|
| 43 |
juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ] |
41 |
juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ] |
| 44 |
|
42 |
|
| 45 |
Options: |
43 |
Options: |
| 46 |
--help brief help message |
44 |
--help brief help message |
| 47 |
--man full documentation |
45 |
--man full documentation |
| 48 |
-v verbose mode |
46 |
-v verbose mode |
| 49 |
-n take no action, display only |
47 |
-n take no action, display only |
| 50 |
-b <branchname> only deal with patrons from this library/branch |
48 |
-b <branchname> only deal with patrons from this library/branch |
| 51 |
-f <categorycode> change patron category from this category |
49 |
-f <categorycode> change patron category from this category |
| 52 |
-t <categorycode> change patron category to this category |
50 |
-t <categorycode> change patron category to this category |
|
|
51 |
|
| 53 |
=head1 OPTIONS |
52 |
=head1 OPTIONS |
| 54 |
|
53 |
|
| 55 |
=over 8 |
54 |
=over 8 |
|
Lines 101-236
C<juv2adult.pl> -f=<categorycode> -t=<categorycode> -v -n - Processes all branch
Link Here
|
| 101 |
# These variables are set by command line options. |
100 |
# These variables are set by command line options. |
| 102 |
# They are initially set to default values. |
101 |
# They are initially set to default values. |
| 103 |
|
102 |
|
| 104 |
|
103 |
my $help = 0; |
| 105 |
my $help = 0; |
104 |
my $man = 0; |
| 106 |
my $man = 0; |
105 |
my $verbose = 0; |
| 107 |
my $verbose = 0; |
|
|
| 108 |
my $noaction = 0; |
106 |
my $noaction = 0; |
| 109 |
my $mybranch; |
107 |
my $mybranch; |
| 110 |
my $fromcat; |
108 |
my $fromcat; |
| 111 |
my $tocat; |
109 |
my $tocat; |
| 112 |
|
110 |
|
| 113 |
GetOptions( |
111 |
GetOptions( |
| 114 |
'help|?' => \$help, |
112 |
'help|?' => \$help, |
| 115 |
'man' => \$man, |
113 |
'man' => \$man, |
| 116 |
'v' => \$verbose, |
114 |
'v' => \$verbose, |
| 117 |
'n' => \$noaction, |
115 |
'n' => \$noaction, |
| 118 |
'f=s' => \$fromcat, |
116 |
'f=s' => \$fromcat, |
| 119 |
't=s' => \$tocat, |
117 |
't=s' => \$tocat, |
| 120 |
'b=s' => \$mybranch, |
118 |
'b=s' => \$mybranch, |
| 121 |
) or pod2usage(2); |
119 |
) or pod2usage(2); |
| 122 |
pod2usage(1) if $help; |
120 |
pod2usage(1) if $help; |
| 123 |
pod2usage( -verbose => 2 ) if $man; |
121 |
pod2usage( -verbose => 2 ) if $man; |
| 124 |
|
122 |
|
| 125 |
if(not $fromcat && $tocat) { #make sure we've specified the info we need. |
123 |
if ( not $fromcat && $tocat ) { #make sure we've specified the info we need. |
| 126 |
print "please specify -help for usage tips.\n"; |
124 |
print "please specify -help for usage tips.\n"; |
| 127 |
exit; |
125 |
exit; |
| 128 |
} |
126 |
} |
| 129 |
|
127 |
|
| 130 |
cronlogaction(); |
128 |
cronlogaction(); |
| 131 |
|
129 |
|
| 132 |
my $dbh=C4::Context->dbh; |
130 |
my $dbh = C4::Context->dbh; |
|
|
131 |
|
| 133 |
#get today's date, format it and subtract upperagelimit |
132 |
#get today's date, format it and subtract upperagelimit |
| 134 |
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); |
133 |
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = |
| 135 |
$year +=1900; |
134 |
localtime(time); |
| 136 |
$mon +=1; if ($mon < 10) {$mon = "0".$mon;} |
135 |
$year += 1900; |
| 137 |
if ($mday < 10) {$mday = "0".$mday;} |
136 |
$mon += 1; |
|
|
137 |
if ( $mon < 10 ) { $mon = "0" . $mon; } |
| 138 |
if ( $mday < 10 ) { $mday = "0" . $mday; } |
| 139 |
|
| 138 |
# get the upperagelimit from the category to be transitioned from |
140 |
# get the upperagelimit from the category to be transitioned from |
| 139 |
my $query=qq|SELECT upperagelimit from categories where categorycode =?|; |
141 |
my $query = qq|SELECT upperagelimit from categories where categorycode =?|; |
| 140 |
my $sth=$dbh->prepare( $query ); |
142 |
my $sth = $dbh->prepare($query); |
| 141 |
$sth->execute( $fromcat ) |
143 |
$sth->execute($fromcat) |
| 142 |
or die "Couldn't execute statement: " . $sth->errstr; |
144 |
or die "Couldn't execute statement: " . $sth->errstr; |
| 143 |
my $agelimit = $sth->fetchrow_array(); |
145 |
my $agelimit = $sth->fetchrow_array(); |
| 144 |
if ( not $agelimit ) { |
146 |
if ( not $agelimit ) { |
| 145 |
|
147 |
die "No patron category $fromcat. Please try again. \n"; |
| 146 |
die "No patron category $fromcat. Please try again. \n"; |
|
|
| 147 |
} |
148 |
} |
| 148 |
$query=qq|SELECT categorycode from categories where categorycode=?|; |
149 |
|
| 149 |
$sth=$dbh->prepare( $query ); |
150 |
$query = qq|SELECT categorycode from categories where categorycode=?|; |
| 150 |
$sth->execute( $tocat ) |
151 |
$sth = $dbh->prepare($query); |
| 151 |
or die "Couldn't execute statement: " . $sth->errstr; |
152 |
$sth->execute($tocat) |
|
|
153 |
or die "Couldn't execute statement: " . $sth->errstr; |
| 152 |
my $tocatage = $sth->fetchrow_array(); |
154 |
my $tocatage = $sth->fetchrow_array(); |
| 153 |
if ( not $tocatage ){ |
155 |
if ( not $tocatage ) { |
| 154 |
die "No patron category $tocat. Please try again. \n"; |
156 |
die "No patron category $tocat. Please try again. \n"; |
| 155 |
} |
157 |
} |
| 156 |
$sth->finish( ); |
158 |
|
| 157 |
$year -=$agelimit; |
159 |
$year -= $agelimit; |
| 158 |
|
160 |
|
| 159 |
$verbose and print "The age limit for category $fromcat is $agelimit\n"; |
161 |
$verbose and print "The age limit for category $fromcat is $agelimit\n"; |
| 160 |
|
162 |
|
| 161 |
my $itsyourbirthday = "$year-$mon-$mday"; |
163 |
my $itsyourbirthday = "$year-$mon-$mday"; |
| 162 |
|
164 |
|
| 163 |
|
165 |
if ( not $noaction ) { |
| 164 |
if ( not $noaction) { |
166 |
if ($mybranch) { #yep, we received a specific branch to work on. |
| 165 |
if ( $mybranch ) { #yep, we received a specific branch to work on. |
167 |
$verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n"; |
| 166 |
$verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n"; |
168 |
my $query = qq| |
| 167 |
my $query=qq|UPDATE borrowers |
169 |
UPDATE borrowers |
| 168 |
SET guarantorid ='0', |
170 |
SET guarantorid ='0', |
| 169 |
categorycode =? |
171 |
categorycode = ? |
| 170 |
WHERE dateofbirth<=? |
172 |
WHERE dateofbirth <= ? |
| 171 |
AND dateofbirth!='0000-00-00' |
173 |
AND dateofbirth != '0000-00-00' |
| 172 |
AND branchcode=? |
174 |
AND branchcode = ? |
| 173 |
AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|; |
175 |
AND categorycode IN ( |
| 174 |
my $sth=$dbh->prepare($query); |
176 |
SELECT categorycode |
| 175 |
my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute"; |
177 |
FROM categories |
| 176 |
if ($res eq '0E0') { print "No patrons updated\n"; |
178 |
WHERE category_type = 'C' |
| 177 |
} else { print "Updated $res patrons\n"; } |
179 |
AND categorycode = ? |
| 178 |
} else { # branch was not supplied, processing all branches |
180 |
)|; |
| 179 |
$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n"; |
181 |
my $sth = $dbh->prepare($query); |
| 180 |
my $query=qq|UPDATE borrowers |
182 |
my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) |
| 181 |
SET guarantorid ='0', |
183 |
or die "can't execute"; |
| 182 |
categorycode =? |
184 |
|
| 183 |
WHERE dateofbirth<=? |
185 |
if ( $res eq '0E0' ) { |
| 184 |
AND dateofbirth!='0000-00-00' |
186 |
print "No patrons updated\n"; |
| 185 |
AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|; |
187 |
} |
| 186 |
my $sth=$dbh->prepare($query); |
188 |
else { |
| 187 |
my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute"; |
189 |
print "Updated $res patrons\n"; |
| 188 |
if ($res eq '0E0') { print "No patrons updated\n"; |
190 |
} |
| 189 |
} else { print "Updated $res patrons\n"; } |
191 |
} |
| 190 |
} |
192 |
else { # branch was not supplied, processing all branches |
| 191 |
} else { |
193 |
$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n"; |
| 192 |
my $birthday; |
194 |
my $query = qq| |
| 193 |
if ( $mybranch ) { |
195 |
UPDATE borrowers |
| 194 |
$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n"; |
196 |
SET guarantorid = '0', |
| 195 |
my $query=qq|SELECT firstname, |
197 |
categorycode = ? |
| 196 |
surname, |
198 |
WHERE dateofbirth <= ? |
| 197 |
cardnumber, |
199 |
AND dateofbirth!='0000-00-00' |
| 198 |
dateofbirth |
200 |
AND categorycode IN ( |
| 199 |
FROM borrowers |
201 |
SELECT categorycode |
| 200 |
WHERE dateofbirth<=? |
202 |
FROM categories |
| 201 |
AND dateofbirth!='0000-00-00' |
203 |
WHERE category_type = 'C' |
| 202 |
AND branchcode=? |
204 |
AND categorycode = ? |
| 203 |
AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|; |
205 |
)|; |
| 204 |
my $sth=$dbh->prepare( $query ); |
206 |
my $sth = $dbh->prepare($query); |
| 205 |
$sth->execute( $itsyourbirthday, $mybranch, $fromcat ) |
207 |
my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) |
| 206 |
or die "Couldn't execute statement: " . $sth->errstr; |
208 |
or die "can't execute"; |
| 207 |
while ( my @res = $sth->fetchrow_array()) { |
209 |
|
| 208 |
my $firstname = $res[0]; |
210 |
if ( $res eq '0E0' ) { |
| 209 |
my $surname = $res[1]; |
211 |
print "No patrons updated\n"; |
| 210 |
my $barcode = $res[2]; |
212 |
} |
| 211 |
$birthday = $res[3]; |
213 |
else { |
| 212 |
print "$firstname $surname $barcode $birthday\n"; |
214 |
print "Updated $res patrons\n"; |
| 213 |
} |
215 |
} |
| 214 |
} else { |
216 |
} |
| 215 |
$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n"; |
217 |
} |
| 216 |
my $query=qq|SELECT firstname, |
218 |
else { |
| 217 |
surname, |
219 |
my $birthday; |
| 218 |
cardnumber, |
220 |
if ($mybranch) { |
| 219 |
dateofbirth |
221 |
$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n"; |
| 220 |
FROM borrowers |
222 |
my $query = qq| |
| 221 |
WHERE dateofbirth<=? |
223 |
SELECT firstname, |
| 222 |
AND dateofbirth!='0000-00-00' |
224 |
surname, |
| 223 |
AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|; |
225 |
cardnumber, |
| 224 |
my $sth=$dbh->prepare( $query ); |
226 |
dateofbirth |
| 225 |
$sth->execute( $itsyourbirthday, $fromcat ) |
227 |
FROM borrowers |
| 226 |
or die "Couldn't execute statement: " . $sth->errstr; |
228 |
WHERE dateofbirth <= ? |
| 227 |
while ( my @res = $sth->fetchrow_array()) { |
229 |
AND dateofbirth != '0000-00-00' |
| 228 |
my $firstname = $res[0]; |
230 |
AND branchcode = ? |
| 229 |
my $surname = $res[1]; |
231 |
AND categorycode IN ( |
| 230 |
my $barcode = $res[2]; |
232 |
SELECT categorycode |
| 231 |
$birthday = $res[3]; |
233 |
FROM categories |
| 232 |
print "$firstname $surname $barcode $birthday\n"; |
234 |
WHERE category_type = 'C' |
| 233 |
} |
235 |
AND categorycode = ? |
| 234 |
} |
236 |
) |
| 235 |
$sth->finish( ); |
237 |
|; |
|
|
238 |
my $sth = $dbh->prepare($query); |
| 239 |
$sth->execute( $itsyourbirthday, $mybranch, $fromcat ) |
| 240 |
or die "Couldn't execute statement: " . $sth->errstr; |
| 241 |
|
| 242 |
while ( my @res = $sth->fetchrow_array() ) { |
| 243 |
my $firstname = $res[0]; |
| 244 |
my $surname = $res[1]; |
| 245 |
my $barcode = $res[2]; |
| 246 |
$birthday = $res[3]; |
| 247 |
print "$firstname $surname $barcode $birthday\n"; |
| 248 |
} |
| 249 |
} |
| 250 |
else { |
| 251 |
$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n"; |
| 252 |
my $query = qq| |
| 253 |
SELECT firstname, |
| 254 |
surname, |
| 255 |
cardnumber, |
| 256 |
dateofbirth |
| 257 |
FROM borrowers |
| 258 |
WHERE dateofbirth <= ? |
| 259 |
AND dateofbirth != '0000-00-00' |
| 260 |
AND categorycode IN ( |
| 261 |
SELECT categorycode |
| 262 |
FROM categories |
| 263 |
WHERE category_type = 'C' |
| 264 |
AND categorycode = ? |
| 265 |
) |
| 266 |
|; |
| 267 |
my $sth = $dbh->prepare($query); |
| 268 |
$sth->execute( $itsyourbirthday, $fromcat ) |
| 269 |
or die "Couldn't execute statement: " . $sth->errstr; |
| 270 |
|
| 271 |
while ( my @res = $sth->fetchrow_array() ) { |
| 272 |
my $firstname = $res[0]; |
| 273 |
my $surname = $res[1]; |
| 274 |
my $barcode = $res[2]; |
| 275 |
$birthday = $res[3]; |
| 276 |
print "$firstname $surname $barcode $birthday\n"; |
| 277 |
} |
| 278 |
} |
| 236 |
} |
279 |
} |
| 237 |
- |
|
|