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

(-)a/misc/cronjobs/j2a.pl (-331 lines)
Lines 1-330 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
100
=cut
101
102
# These variables are set by command line options.
103
# They are initially set to default values.
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
    'f=s'    => \$fromcat,
119
    't=s'    => \$tocat,
120
    'b=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
cronlogaction();
131
132
my $dbh      = C4::Context->dbh;
133
my $database = Koha::Database->new();
134
my $schema   = $database->schema;
135
136
#get today's date, format it and subtract upperagelimit
137
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
138
  localtime(time);
139
$year += 1900;
140
$mon  += 1;
141
if ( $mon < 10 )  { $mon  = "0" . $mon; }
142
if ( $mday < 10 ) { $mday = "0" . $mday; }
143
144
# get the upperagelimit from the category to be transitioned from
145
my $query = qq|SELECT upperagelimit from categories where categorycode =?|;
146
my $sth   = $dbh->prepare($query);
147
$sth->execute($fromcat)
148
  or die "Couldn't execute statement: " . $sth->errstr;
149
my $agelimit = $sth->fetchrow_array();
150
if ( not $agelimit ) {
151
    die "No patron category $fromcat. Please try again. \n";
152
}
153
154
$query = qq|SELECT categorycode from categories where categorycode=?|;
155
$sth   = $dbh->prepare($query);
156
$sth->execute($tocat)
157
  or die "Couldn't execute statement: " . $sth->errstr;
158
my $tocatage = $sth->fetchrow_array();
159
if ( not $tocatage ) {
160
    die "No patron category $tocat. Please try again. \n";
161
}
162
163
$year -= $agelimit;
164
165
$verbose and print "The age limit for category $fromcat is $agelimit\n";
166
167
my $itsyourbirthday = "$year-$mon-$mday";
168
169
if ( not $noaction ) {
170
    # Start a transaction since we need to delete from relationships and update borrowers atomically
171
172
    my $success = 1;
173
    if ($mybranch) {    #yep, we received a specific branch to work on.
174
        $verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
175
        my $where = qq|
176
            WHERE dateofbirth <= ?
177
              AND dateofbirth != '0000-00-00'
178
              AND branchcode = ?
179
              AND categorycode IN (
180
                SELECT categorycode
181
                FROM categories
182
                WHERE category_type = 'C'
183
                  AND categorycode = ?
184
              )
185
        |;
186
187
        $schema->storage->txn_begin;
188
189
        my $query = qq|
190
            DELETE borrower_relationships FROM borrower_relationships
191
            LEFT JOIN borrowers ON ( borrowers.borrowernumber = borrower_relationships.guarantee_id )
192
            $where
193
        |;
194
        my $sth = $dbh->prepare($query);
195
        $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
196
          or $success = 0;
197
198
        $query = qq|
199
            UPDATE borrowers
200
            SET categorycode = ?
201
            $where
202
        |;
203
        $sth = $dbh->prepare($query);
204
        my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat )
205
          or $success = 0;
206
207
        if ( $success ) {
208
            $schema->storage->txn_commit;
209
        } else {
210
            $schema->storage->txn_rollback;
211
            die "can't execute";
212
        }
213
214
        if ( $res eq '0E0' ) {
215
            print "No patrons updated\n";
216
        }
217
        else {
218
            print "Updated $res patrons\n";
219
        }
220
    }
221
    else {    # branch was not supplied, processing all branches
222
        $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
223
        my $where = qq|
224
            WHERE dateofbirth <= ?
225
              AND dateofbirth!='0000-00-00'
226
              AND categorycode IN (
227
                SELECT categorycode
228
                FROM categories
229
                WHERE category_type = 'C'
230
                  AND categorycode = ?
231
              )
232
        |;
233
234
        my $query = qq|
235
            DELETE borrower_relationships FROM borrower_relationships
236
            LEFT JOIN borrowers ON ( borrowers.borrowernumber = borrower_relationships.guarantee_id )
237
            $where
238
        |;
239
        my $sth = $dbh->prepare($query);
240
        $sth->execute( $itsyourbirthday, $fromcat )
241
          or $success = 0;
242
243
        $query = qq|
244
            UPDATE borrowers
245
            SET categorycode = ?
246
            $where
247
        |;
248
        $sth = $dbh->prepare($query);
249
        my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat )
250
          or $success = 0;
251
        $dbh->commit;
252
253
        if ( $success ) {
254
            $dbh->commit;
255
        } else {
256
            $dbh->rollback;
257
            die "can't execute";
258
        }
259
260
        if ( $res eq '0E0' ) {
261
            print "No patrons updated\n";
262
        }
263
        else {
264
            print "Updated $res patrons\n";
265
        }
266
    }
267
}
268
else {
269
    my $birthday;
270
    if ($mybranch) {
271
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
272
        my $query = qq|
273
            SELECT firstname,
274
                   surname,
275
                   cardnumber,
276
                   dateofbirth
277
            FROM borrowers
278
            WHERE dateofbirth <= ?
279
              AND dateofbirth != '0000-00-00'
280
              AND branchcode = ?
281
              AND categorycode IN (
282
                SELECT categorycode
283
                FROM categories
284
                WHERE category_type = 'C'
285
                  AND categorycode = ?
286
              )
287
        |;
288
        my $sth = $dbh->prepare($query);
289
        $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
290
          or die "Couldn't execute statement: " . $sth->errstr;
291
292
        while ( my @res = $sth->fetchrow_array() ) {
293
            my $firstname = $res[0];
294
            my $surname   = $res[1];
295
            my $barcode   = $res[2];
296
            $birthday = $res[3];
297
            print "$firstname $surname $barcode $birthday\n";
298
        }
299
    }
300
    else {
301
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
302
        my $query = qq|
303
            SELECT firstname,
304
                   surname,
305
                   cardnumber,
306
                   dateofbirth
307
            FROM borrowers
308
            WHERE dateofbirth <= ?
309
              AND dateofbirth != '0000-00-00'
310
              AND categorycode IN (
311
                SELECT categorycode
312
                FROM categories
313
                WHERE category_type = 'C'
314
                  AND categorycode = ?
315
              )
316
        |;
317
        my $sth = $dbh->prepare($query);
318
        $sth->execute( $itsyourbirthday, $fromcat )
319
          or die "Couldn't execute statement: " . $sth->errstr;
320
        $dbh->commit;
321
322
        while ( my @res = $sth->fetchrow_array() ) {
323
            my $firstname = $res[0];
324
            my $surname   = $res[1];
325
            my $barcode   = $res[2];
326
            $birthday = $res[3];
327
            print "$firstname $surname $barcode $birthday\n";
328
        }
329
    }
330
}
331
- 

Return to bug 17168