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

(-)a/misc/cronjobs/update_patrons_category.pl (-1 / +273 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 -f=categorycode -t=categorycode
43
                          [-b=branchcode] [-n] [-a] [-fo=X] [-fu=X]
44
                          [-rb=date] [-ra=date] [-v]
45
                          [--field column=value ...]
46
47
update_patrons_category.pl --help | --man
48
49
Options:
50
   --help       brief help message
51
   --man        full documentation
52
   -a           update based on age restriction of current category
53
   -fo=X        update if fines over X amount
54
   -fu=X        update if fines under X amount
55
   -rb=date     update if registration date is before given date
56
   -ra=date     update if registration date is after a given date
57
   --field name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is equal to given <value>
58
   -v           verbose mode
59
   -confirm     commit changes to db, no action will be taken unless this switch is included
60
   -b           <branchname>       only deal with patrons from this library/branch
61
   -f           <categorycode>     change patron category from this category
62
   -t           <categorycode>     change patron category to this category
63
64
=head1 OPTIONS
65
66
=over 8
67
68
=item B<--help>
69
70
Print a brief help message and exits.
71
72
=item B<--man>
73
74
Prints the manual page and exits.
75
76
=item B<-v>
77
78
Verbose. Without this flag set, only fatal errors are reported.
79
80
=item B<--confirm>
81
82
Commit changes. Unless this flag set is, the script will report changes but not actually execute them on the database.
83
84
=item B<-b>
85
86
changes patrons for one specific branch. Use the value in the
87
branches.branchcode table.
88
89
=item B<-f>
90
91
*required* defines the category to update. Expects the code from categories.categorycode.
92
93
=item B<-t>
94
95
*required* defines the category patrons will be converted to. Expects the code from categories.categorycode.
96
97
=item B<-a>
98
99
Update patron only if they are outside the age range specified for the 'from' category.
100
101
=item B<-fo=X>
102
103
Supply a number and only account with fines over this number will be updated.
104
105
=item B<-fu=X>
106
107
Supply a number and only account with fines under this number will be updated.
108
109
=item B<-rb=date>
110
111
Enter a date in ISO format YYYY-MM-DD and only patrons registered before this date wil be updated.
112
113
=item B<-ra=date>
114
115
Enter a date in ISO format YYYY-MM-DD and only patrons registered after this date wil be updated.
116
117
=item B<--field column=value>
118
119
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)
120
121
e.g.
122
--field dateexpiry=2016-01-01
123
will update all patrons who expired on that date, useful for schools etc.
124
125
=back
126
127
=head1 DESCRIPTION
128
129
This script is designed to update patrons from one category to another.
130
131
=head1 USAGE EXAMPLES
132
133
C<update_patron_categories.pl> - Suggests that you read this help. :)
134
135
C<update_patron_categories.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode> --confirm  - Processes a single branch, and updates the patron categories from fromcat to tocat.
136
137
C<update_patron_categories.pl> -b=<branchcode> -f=<categorycode> -t=<categorycode>  -a --confirm  - Processes a single branch, and updates the patron categories from fromcat to tocat for patrons outside the age range of fromcat.
138
139
C<update_patron_categories.pl> -f=<categorycode> -t=<categorycode> -v  - Processes all branches, shows all messages, and reports the patrons who would be affected. Takes no action on the database.
140
141
=cut
142
143
# These variables are set by command line options.
144
# They are initially set to default values.
145
146
my $help     = 0;
147
my $man      = 0;
148
my $verbose  = 0;
149
my $doit = 0;
150
my $age      = 0;
151
my $remove_guarantors = 0;
152
my $fine_min;
153
my $fine_max;
154
my $fromcat;
155
my $tocat;
156
my $reg_bef;
157
my $reg_aft;
158
my $branch_lim;
159
my %fields;
160
161
GetOptions(
162
    'help|?'  => \$help,
163
    'man'     => \$man,
164
    'v'       => \$verbose,
165
    'confirm' => \$doit,
166
    'f=s'     => \$fromcat,
167
    't=s'     => \$tocat,
168
    'a'       => \$age,
169
    'fo=s'    => \$fine_min,
170
    'fu=s'    => \$fine_max,
171
    'rb=s'    => \$reg_bef,
172
    'ra=s'    => \$reg_aft,
173
    'b=s'     => \$branch_lim,
174
    'field=s' => \%fields
175
) or pod2usage(2);
176
pod2usage(1) if $help;
177
pod2usage( -verbose => 2 ) if $man;
178
179
if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
180
    print "Must supply category from and to (-f & -t) please specify -help for usage tips.\n";
181
    exit;
182
}
183
184
($verbose && !$doit) and print "No actions will be taken (test mode)\n";
185
186
$verbose and print "Will update patrons from $fromcat to $tocat with conditions below (if any)\n";
187
188
my %params;
189
my %query;
190
191
if ( $reg_bef || $reg_aft ){
192
    my $date_bef;
193
    my $date_aft;
194
    if (defined $reg_bef) {eval { $date_bef = dt_from_string( $reg_bef, 'iso' ); };}
195
    die "$reg_bef is not a valid date before, aborting! Use a date in format YYYY-MM-DD.$@"
196
    if $@;
197
    if (defined $reg_aft) {eval { $date_aft = dt_from_string( $reg_aft, 'iso' ); };}
198
    die "$reg_bef is not a valid date after, aborting! Use a date in format YYYY-MM-DD.$@"
199
    if $@;
200
    $params{dateenrolled}{'<='}=$reg_bef if defined $date_bef;
201
    $params{dateenrolled}{'>='}=$reg_aft if defined $date_aft;
202
}
203
204
if ($age && ($fine_min || $fine_max) ){
205
    %query = (
206
        join     => ["accountlines" => "categorycode"],
207
        select   => ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ],
208
        as       => [qw/borrowernumber  total_fines/],
209
        where  => \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN COALESCE(dateofbirthrequired,0) AND COALESCE( upperagelimit, 999 ) '],
210
        group_by => ["borrowernumber"],
211
        having   => [{"total_fines" => { '>=', $fine_min, '<=', $fine_max }}],
212
        );
213
        $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max;
214
        $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min;
215
}
216
else{
217
    if ($age) {
218
        if ( Koha::Patron::Categories->find($fromcat)->category_type eq 'C' && Koha::Patron::Categories->find($tocat)->category_type eq 'A' ) { $remove_guarantors = 1; }
219
        $query{join} = ["categorycode"];
220
        $query{where} = \[' FLOOR(DATEDIFF (NOW(), dateofbirth)/365) NOT BETWEEN COALESCE(dateofbirthrequired,0) AND COALESCE( upperagelimit, 999 ) '];
221
    }
222
    if ($fine_min || $fine_max) {
223
        $query{join} = ["accountlines"];
224
        $query{select} = ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ];
225
        $query{as} = [qw/borrowernumber  total_fines/];
226
        $query{group_by} = ["borrowernumber"];
227
        $query{having}{total_fines}{'<='}=$fine_max if defined $fine_max;
228
        $query{having}{total_fines}{'>='}=$fine_min if defined $fine_min;
229
    }
230
}
231
232
$params{"me.categorycode"} = $fromcat if defined $fromcat;
233
$params{"me.branchcode"} = $branch_lim if defined $branch_lim;
234
235
if ($verbose) {
236
    if (defined $reg_bef)    {print "Condition: Registered before $reg_bef\n";}
237
    if (defined $reg_aft)    {print "Condition: Registered before $reg_aft\n";}
238
    if (defined $fine_min)   {print "Condition: Total fines more than $fine_min\n";}
239
    if (defined $fine_max)   {print "Condition: Total fines less than $fine_max\n";}
240
    if ($age)        {print "Condition: Age is outside range defined for $fromcat\n";}
241
    if (defined $branch_lim) {print "Condition: Branchcode of patron is $branch_lim\n";}
242
}
243
244
while (my ($key,$value) = each %fields ) {
245
    $verbose and print "Condition: borrower column $key is equal to $value\n";
246
    $params{"me.".$key} = $value;
247
}
248
249
my $target_patrons = Koha::Patrons->search(\%params,\%query);
250
251
my $counter = 0;
252
253
my $testdisplay  = $doit ? "" : "WOULD HAVE ";
254
255
while ( my $target_patron = $target_patrons->next() ){
256
    $counter++;
257
    my $target = Koha::Patrons->find( $target_patron->borrowernumber );
258
    $verbose and print $testdisplay."Updated ".$target->firstname." ".$target->surname." from $fromcat to $tocat\n";
259
    if ( $remove_guarantors ){
260
        $verbose and print $testdisplay."Removing guarantor info\n";
261
        $target->guarantorid(0);
262
        $target->contactname('');
263
        $target->contactfirstname('');
264
        $target->contacttitle('');
265
        $target->relationship('');
266
    }
267
    if ($doit) {
268
        $target->categorycode($tocat);
269
        $target->store();
270
    }
271
}
272
273
$verbose and print "$counter patrons $testdisplay updated\n";

Return to bug 17168