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

(-)a/misc/cronjobs/update_patrons_category.pl (-1 / +278 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl 
2
3
# This file is part of Koha.
4
#
5
# Koha is free software; you can redistribute it and/or modify it
6
# under the terms of the GNU General Public License as published by
7
# the Free Software Foundation; either version 3 of the License, or
8
# (at your option) any later version.
9
#
10
# Koha is distributed in the hope that it will be useful, but
11
# WITHOUT ANY WARRANTY; without even the implied warranty of
12
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13
# GNU General Public License for more details.
14
#
15
# You should have received a copy of the GNU General Public License
16
# along with Koha; if not, see <http://www.gnu.org/licenses>.
17
18
use Modern::Perl;
19
20
BEGIN {
21
    # find Koha's Perl modules
22
    # test carefully before changing this
23
    use FindBin;
24
    eval { require "$FindBin::Bin/../kohalib.pl" };
25
}
26
27
use C4::Context;
28
use Getopt::Long;
29
use Pod::Usage;
30
use Koha::Logger;
31
use Koha::Patrons;
32
use Koha::Patron::Categories;
33
use Koha::DateUtils;
34
use Data::Dumper;
35
36
=head1 NAME
37
38
update_patrons_category.pl - Given a set of parameters update selected patrons from one catgeory to another
39
40
=head1 SYNOPSIS
41
42
update_patrons_category.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ]
43
44
 Options:
45
   --help       brief help message
46
   --man        full documentation
47
   -a           update based on age restriction of current category
48
   -fo=X        update if fines over X amount
49
   -fu=X        update if fines under X amount
50
   -rb=date     update if registration date is before given date
51
   -ra=date     update if registration date is after a given date
52
   --field name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is equal to given <value>
53
   -v           verbose mode
54
   -n           take no action, display only
55
   -b           <branchname>       only deal with patrons from this library/branch
56
   -f           <categorycode>     change patron category from this category
57
   -t           <categorycode>     change patron category to this category
58
59
=head1 OPTIONS
60
61
=over 8
62
63
=item B<--help>
64
65
Print a brief help message and exits.
66
67
=item B<--man>
68
69
Prints the manual page and exits.
70
71
=item B<-v>
72
73
Verbose. Without this flag set, only fatal errors are reported.
74
75
=item B<-n>
76
77
No Action. With this flag set, script will report changes but not actually execute them on the database.
78
79
=item B<-b>
80
81
changes patrons for one specific branch. Use the value in the
82
branches.branchcode table.
83
84
=item B<-f>
85
86
*required* defines the category to update. Expects the code from categories.categorycode.
87
88
=item B<-t>
89
90
*required* defines the category patrons will be converted to. Expects the code from categories.categorycode.
91
92
=back
93
94
=item B<-a>
95
96
Update patron only if they are outside the age range specified for the 'from' category.
97
98
=back
99
100
=item B<-fo=X>
101
102
Supply a number and only account with fines over this number will be updated.
103
104
=back
105
106
=item B<-fu=X>
107
108
Supply a number and only account with fines under this number will be updated.
109
110
=back
111
112
=item B<-rb=date>
113
114
Enter a date in ISO format YYYY-MM-DD and only patrons registered before this date wil be updated.
115
116
=back
117
118
=item B<-ra=date>
119
120
Enter a date in ISO format YYYY-MM-DD and only patrons registered after this date wil be updated.
121
122
=back
123
124
=item B<--field column=value>
125
126
Use this flag to specify a column in the borrowers table and update only patrons whose value in that column matches the value supplied (repeatable)
127
128
e.g. 
129
--field dateexpiry=2016-01-01 
130
will update all patrons who expired on that date, useful for schools etc.
131
132
=back
133
134
=head1 DESCRIPTION
135
136
This script is designed to update patrons from one category to another.
137
138
=head1 USAGE EXAMPLES
139
140
C<update_patron_categories.pl> - Suggests that you read this help. :)
141
142
C<update_patron_categories.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode>  - Processes a single branch, and updates the patron categories from fromcat to tocat.
143
144
C<update_patron_categories.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode>  -a - Processes a single branch, and updates the patron categories from fromcat to tocat for patrons outside the age range of fromcat.
145
146
C<update_patron_categories.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.
147
=cut
148
149
# These variables are set by command line options.
150
# They are initially set to default values.
151
152
my $help     = 0;
153
my $man      = 0;
154
my $verbose  = 0;
155
my $noaction = 0;
156
my $age      = 0;
157
my $remove_guarantors = 0;
158
my $fine_min;
159
my $fine_max;
160
my $fromcat;
161
my $tocat;
162
my $reg_bef;
163
my $reg_aft;
164
my $branch_lim;
165
my %fields;
166
167
GetOptions(
168
    'help|?'  => \$help,
169
    'man'     => \$man,
170
    'v'       => \$verbose,
171
    'n'       => \$noaction,
172
    'f=s'     => \$fromcat,
173
    't=s'     => \$tocat,
174
    'a'       => \$age,
175
    'fo=s'    => \$fine_min,
176
    'fu=s'    => \$fine_max,
177
    'rb=s'    => \$reg_bef,
178
    'ra=s'    => \$reg_aft,
179
    'b=s'     => \$branch_lim,
180
    'field=s' => \%fields
181
) or pod2usage(2);
182
pod2usage(1) if $help;
183
pod2usage( -verbose => 2 ) if $man;
184
185
if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
186
    print "Must supply category from and to (-f & -t) please specify -help for usage tips.\n";
187
    exit;
188
}
189
190
($verbose && $noaction) and print "No actions will be taken (test mode)\n";
191
192
$verbose and print "Will update patrons from $fromcat to $tocat with conditions below (if any)\n";
193
194
my %params;
195
my %query;
196
197
if ( $reg_bef || $reg_aft ){
198
    my $date_bef;
199
    my $date_aft;
200
    if (defined $reg_bef) {eval { $date_bef = dt_from_string( $reg_bef, 'iso' ); };}
201
    die "$reg_bef is not a valid date before, aborting! Use a date in format YYYY-MM-DD.$@"
202
    if $@;
203
    if (defined $reg_aft) {eval { $date_aft = dt_from_string( $reg_aft, 'iso' ); };}
204
    die "$reg_bef is not a valid date after, aborting! Use a date in format YYYY-MM-DD.$@"
205
    if $@;
206
    $params{dateenrolled}{'<='}=$reg_bef if defined $date_bef;
207
    $params{dateenrolled}{'>='}=$reg_aft if defined $date_aft;
208
}
209
210
if ($age && ($fine_min || $fine_max) ){
211
    %query = (
212
        join     => ["accountlines" => "categorycode"],
213
        select   => ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ],
214
        as       => [qw/borrowernumber  total_fines/],
215
        where  => \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN IFNULL(dateofbirthrequired,0) AND IFNULL( upperagelimit, 999 ) '],
216
        group_by => ["borrowernumber"],
217
        having   => [{"total_fines" => { '>=', $fine_min, '<=', $fine_max }}],
218
        );
219
        $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max;
220
        $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min;
221
}
222
else{
223
    if ($age) {
224
        if ( Koha::Patron::Categories->find($fromcat)->category_type eq 'C' && Koha::Patron::Categories->find($tocat)->category_type eq 'A' ) { $remove_guarantors = 1; }
225
        $query{join} = ["categorycode"];
226
        $query{where} = \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN IFNULL(dateofbirthrequired,0) AND IFNULL( upperagelimit, 999 ) '];
227
    }
228
    if ($fine_min || $fine_max) {
229
        $query{join} = ["accountlines"];
230
        $query{select} = ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ];
231
        $query{as} = [qw/borrowernumber  total_fines/];
232
        $query{group_by} = ["borrowernumber"];
233
        $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max;
234
        $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min;
235
    }
236
}
237
238
$params{"me.categorycode"} = $fromcat if defined $fromcat;
239
$params{"me.branchcode"} = $branch_lim if defined $branch_lim;
240
241
if ($verbose) {
242
    if (defined $reg_bef)    {print "Condition: Registered before $reg_bef\n";}
243
    if (defined $reg_aft)    {print "Condition: Registered before $reg_aft\n";}
244
    if (defined $fine_min)   {print "Condition: Total fines more than $fine_min\n";}
245
    if (defined $fine_max)   {print "Condition: Total fines less than $fine_max\n";}
246
    if (defined $age)        {print "Condition: Age is outside range defined for $fromcat\n";}
247
    if (defined $branch_lim) {print "Condition: Branchcode of patron is $branch_lim\n";}
248
}
249
250
while (my ($key,$value) = each %fields ) {
251
    $verbose and print "Condition: borrower column $key is equal to $value\n";
252
    $params{"me.".$key} = $value;
253
}
254
255
my $target_patrons = Koha::Patrons->search(\%params,\%query);
256
257
my $counter = 0;
258
259
while ( my $target_patron = $target_patrons->next() ){
260
    $counter++;
261
    my $target = Koha::Patrons->find( $target_patron->borrowernumber );
262
    $verbose and print "Updating ".$target->firstname." ".$target->surname." from $fromcat to $tocat\n";
263
    if ( $remove_guarantors ){
264
        $verbose and print "Removing guarantor info\n";
265
        $target->guarantorid(0);
266
        $target->contactname('');
267
        $target->contactfirstname('');
268
        $target->contacttitle('');
269
        $target->relationship('');
270
    }
271
    unless ($noaction) {
272
        $target->categorycode($tocat);
273
        $target->store();
274
    }
275
}
276
277
$verbose and print "$counter patrons updated\n";
278

Return to bug 17168