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

(-)a/misc/cronjobs/j2a.pl (-280 lines)
Lines 1-279 Link Here
1
#!/usr/bin/perl 
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
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
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>.
19
20
use Modern::Perl;
21
22
BEGIN {
23
    # find Koha's Perl modules
24
    # test carefully before changing this
25
    use FindBin;
26
    eval { require "$FindBin::Bin/../kohalib.pl" };
27
}
28
29
use C4::Context;
30
use C4::Members;
31
use Getopt::Long;
32
use Pod::Usage;
33
use C4::Log;
34
35
=head1 NAME
36
37
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.
38
39
=head1 SYNOPSIS
40
41
juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ]
42
43
 Options:
44
   --help   brief help message
45
   --man    full documentation
46
   -v       verbose mode
47
   -n       take no action, display only
48
   -b       <branchname>	only deal with patrons from this library/branch
49
   -f       <categorycode>	change patron category from this category
50
   -t       <categorycode>	change patron category to this category
51
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
my $help     = 0;
104
my $man      = 0;
105
my $verbose  = 0;
106
my $noaction = 0;
107
my $mybranch;
108
my $fromcat;
109
my $tocat;
110
111
GetOptions(
112
    'help|?' => \$help,
113
    'man'    => \$man,
114
    'v'      => \$verbose,
115
    'n'      => \$noaction,
116
    'f=s'    => \$fromcat,
117
    't=s'    => \$tocat,
118
    'b=s'    => \$mybranch,
119
) or pod2usage(2);
120
pod2usage(1) if $help;
121
pod2usage( -verbose => 2 ) if $man;
122
123
if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
124
    print "please specify -help for usage tips.\n";
125
    exit;
126
}
127
128
cronlogaction();
129
130
my $dbh = C4::Context->dbh;
131
132
#get today's date, format it and subtract upperagelimit
133
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
134
  localtime(time);
135
$year += 1900;
136
$mon  += 1;
137
if ( $mon < 10 )  { $mon  = "0" . $mon; }
138
if ( $mday < 10 ) { $mday = "0" . $mday; }
139
140
# get the upperagelimit from the category to be transitioned from
141
my $query = qq|SELECT upperagelimit from categories where categorycode =?|;
142
my $sth   = $dbh->prepare($query);
143
$sth->execute($fromcat)
144
  or die "Couldn't execute statement: " . $sth->errstr;
145
my $agelimit = $sth->fetchrow_array();
146
if ( not $agelimit ) {
147
    die "No patron category $fromcat. Please try again. \n";
148
}
149
150
$query = qq|SELECT categorycode from categories where categorycode=?|;
151
$sth   = $dbh->prepare($query);
152
$sth->execute($tocat)
153
  or die "Couldn't execute statement: " . $sth->errstr;
154
my $tocatage = $sth->fetchrow_array();
155
if ( not $tocatage ) {
156
    die "No patron category $tocat. Please try again. \n";
157
}
158
159
$year -= $agelimit;
160
161
$verbose and print "The age limit for category $fromcat is $agelimit\n";
162
163
my $itsyourbirthday = "$year-$mon-$mday";
164
165
if ( not $noaction ) {
166
    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";
168
        my $query = qq|
169
            UPDATE borrowers
170
            SET guarantorid ='0',
171
                categorycode = ?
172
            WHERE dateofbirth <= ?
173
              AND dateofbirth != '0000-00-00'
174
              AND branchcode = ?
175
              AND categorycode IN (
176
                SELECT categorycode
177
                FROM categories
178
                WHERE category_type = 'C'
179
                  AND categorycode = ?
180
              )|;
181
        my $sth = $dbh->prepare($query);
182
        my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat )
183
          or die "can't execute";
184
185
        if ( $res eq '0E0' ) {
186
            print "No patrons updated\n";
187
        }
188
        else {
189
            print "Updated $res patrons\n";
190
        }
191
    }
192
    else {    # branch was not supplied, processing all branches
193
        $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
194
        my $query = qq|
195
            UPDATE borrowers
196
            SET guarantorid = '0',
197
                categorycode = ?
198
            WHERE dateofbirth <= ?
199
              AND dateofbirth!='0000-00-00'
200
              AND categorycode IN (
201
                SELECT categorycode
202
                FROM categories
203
                WHERE category_type = 'C'
204
                  AND categorycode = ?
205
              )|;
206
        my $sth = $dbh->prepare($query);
207
        my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat )
208
          or die "can't execute";
209
210
        if ( $res eq '0E0' ) {
211
            print "No patrons updated\n";
212
        }
213
        else {
214
            print "Updated $res patrons\n";
215
        }
216
    }
217
}
218
else {
219
    my $birthday;
220
    if ($mybranch) {
221
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
222
        my $query = qq|
223
            SELECT firstname,
224
                   surname,
225
                   cardnumber,
226
                   dateofbirth
227
            FROM borrowers
228
            WHERE dateofbirth <= ?
229
              AND dateofbirth != '0000-00-00'
230
              AND branchcode = ?
231
              AND categorycode IN (
232
                SELECT categorycode
233
                FROM categories
234
                WHERE category_type = 'C'
235
                  AND categorycode = ?
236
              )
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
    }
279
}
280
- 

Return to bug 17168