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

(-)a/misc/cronjobs/j2a.pl (-281 lines)
Lines 1-280 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 Koha::Script -cron;
30
use C4::Context;
31
use C4::Members;
32
use Getopt::Long;
33
use Pod::Usage;
34
use C4::Log;
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
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
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
cronlogaction();
130
131
my $dbh = C4::Context->dbh;
132
133
#get today's date, format it and subtract upperagelimit
134
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
135
  localtime(time);
136
$year += 1900;
137
$mon  += 1;
138
if ( $mon < 10 )  { $mon  = "0" . $mon; }
139
if ( $mday < 10 ) { $mday = "0" . $mday; }
140
141
# get the upperagelimit from the category to be transitioned from
142
my $query = qq|SELECT upperagelimit from categories where categorycode =?|;
143
my $sth   = $dbh->prepare($query);
144
$sth->execute($fromcat)
145
  or die "Couldn't execute statement: " . $sth->errstr;
146
my $agelimit = $sth->fetchrow_array();
147
if ( not $agelimit ) {
148
    die "No patron category $fromcat. Please try again. \n";
149
}
150
151
$query = qq|SELECT categorycode from categories where categorycode=?|;
152
$sth   = $dbh->prepare($query);
153
$sth->execute($tocat)
154
  or die "Couldn't execute statement: " . $sth->errstr;
155
my $tocatage = $sth->fetchrow_array();
156
if ( not $tocatage ) {
157
    die "No patron category $tocat. Please try again. \n";
158
}
159
160
$year -= $agelimit;
161
162
$verbose and print "The age limit for category $fromcat is $agelimit\n";
163
164
my $itsyourbirthday = "$year-$mon-$mday";
165
166
if ( not $noaction ) {
167
    if ($mybranch) {    #yep, we received a specific branch to work on.
168
        $verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
169
        my $query = qq|
170
            UPDATE borrowers
171
            SET guarantorid ='0',
172
                categorycode = ?
173
            WHERE dateofbirth <= ?
174
              AND dateofbirth != '0000-00-00'
175
              AND branchcode = ?
176
              AND categorycode IN (
177
                SELECT categorycode
178
                FROM categories
179
                WHERE category_type = 'C'
180
                  AND categorycode = ?
181
              )|;
182
        my $sth = $dbh->prepare($query);
183
        my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat )
184
          or die "can't execute";
185
186
        if ( $res eq '0E0' ) {
187
            print "No patrons updated\n";
188
        }
189
        else {
190
            print "Updated $res patrons\n";
191
        }
192
    }
193
    else {    # branch was not supplied, processing all branches
194
        $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
195
        my $query = qq|
196
            UPDATE borrowers
197
            SET guarantorid = '0',
198
                categorycode = ?
199
            WHERE dateofbirth <= ?
200
              AND dateofbirth!='0000-00-00'
201
              AND categorycode IN (
202
                SELECT categorycode
203
                FROM categories
204
                WHERE category_type = 'C'
205
                  AND categorycode = ?
206
              )|;
207
        my $sth = $dbh->prepare($query);
208
        my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat )
209
          or die "can't execute";
210
211
        if ( $res eq '0E0' ) {
212
            print "No patrons updated\n";
213
        }
214
        else {
215
            print "Updated $res patrons\n";
216
        }
217
    }
218
}
219
else {
220
    my $birthday;
221
    if ($mybranch) {
222
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
223
        my $query = qq|
224
            SELECT firstname,
225
                   surname,
226
                   cardnumber,
227
                   dateofbirth
228
            FROM borrowers
229
            WHERE dateofbirth <= ?
230
              AND dateofbirth != '0000-00-00'
231
              AND branchcode = ?
232
              AND categorycode IN (
233
                SELECT categorycode
234
                FROM categories
235
                WHERE category_type = 'C'
236
                  AND categorycode = ?
237
              )
238
        |;
239
        my $sth = $dbh->prepare($query);
240
        $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
241
          or die "Couldn't execute statement: " . $sth->errstr;
242
243
        while ( my @res = $sth->fetchrow_array() ) {
244
            my $firstname = $res[0];
245
            my $surname   = $res[1];
246
            my $barcode   = $res[2];
247
            $birthday = $res[3];
248
            print "$firstname $surname $barcode $birthday\n";
249
        }
250
    }
251
    else {
252
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
253
        my $query = qq|
254
            SELECT firstname,
255
                   surname,
256
                   cardnumber,
257
                   dateofbirth
258
            FROM borrowers
259
            WHERE dateofbirth <= ?
260
              AND dateofbirth != '0000-00-00'
261
              AND categorycode IN (
262
                SELECT categorycode
263
                FROM categories
264
                WHERE category_type = 'C'
265
                  AND categorycode = ?
266
              )
267
        |;
268
        my $sth = $dbh->prepare($query);
269
        $sth->execute( $itsyourbirthday, $fromcat )
270
          or die "Couldn't execute statement: " . $sth->errstr;
271
272
        while ( my @res = $sth->fetchrow_array() ) {
273
            my $firstname = $res[0];
274
            my $surname   = $res[1];
275
            my $barcode   = $res[2];
276
            $birthday = $res[3];
277
            print "$firstname $surname $barcode $birthday\n";
278
        }
279
    }
280
}
281
- 

Return to bug 17168