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

(-)a/Koha/Patrons.pm (+88 lines)
Lines 29-34 use Koha::ArticleRequests; Link Here
29
use Koha::ArticleRequest::Status;
29
use Koha::ArticleRequest::Status;
30
use Koha::Patron;
30
use Koha::Patron;
31
use Koha::Exceptions::Patron;
31
use Koha::Exceptions::Patron;
32
use Koha::Patron::Categories;
33
use Date::Calc qw( Today Add_Delta_YMD );
32
34
33
use base qw(Koha::Objects);
35
use base qw(Koha::Objects);
34
36
Lines 236-241 sub delete { Link Here
236
    return $patrons_deleted;
238
    return $patrons_deleted;
237
}
239
}
238
240
241
=head3 search_patrons_to_update
242
243
    my $patrons = Koha::Patrons->search_patrons_to_anonymise( {
244
                      from => $from_category,
245
                      fine_max => $fine_max,
246
                      fine_min  => $fin_min,
247
                      au     => $au,
248
                      ao    => $ao,
249
                  });
250
251
This method returns all patron who should be updated form one category to another meeting criteria:
252
253
from - original category
254
fine_min - with fines totaling at least this amount
255
fine_max - with fines above this amount
256
au - under the age limit for 'from'
257
ao - over the agelimit for 'from'
258
259
=cut
260
261
sub search_patrons_to_update {
262
    my ( $self, $params ) = @_;
263
    my %query;
264
    my $search_params = $params->{search_params};;
265
266
    my $cat_from = Koha::Patron::Categories->find($params->{from});
267
    $search_params->{categorycode}=$params->{from};
268
    if ($params->{ao} || $params->{au}){
269
        my ($y,$m,$d) = Today();
270
        if( $cat_from->dateofbirthrequired && $params->{au} ) {
271
            my ($dy,$dm,$dd) =Add_Delta_YMD($y,$m,$d,-$cat_from->dateofbirthrequired,0,0);
272
            $search_params->{dateofbirth}{'>'} = $dy."-".sprintf("%02d",$dm)."-".sprintf("%02d",$dd);
273
        }
274
        if( $cat_from->upperagelimit && $params->{ao} ) {
275
            my ($dy,$dm,$dd) =Add_Delta_YMD($y,$m,$d,-$cat_from->upperagelimit,0,0);
276
            $search_params->{dateofbirth}{'<'} = $dy."-".sprintf("%02d",$dm)."-".sprintf("%02d",$dd);
277
        }
278
    }
279
    if ($params->{fine_min} || $params->{fine_max}) {
280
        $query{join} = ["accountlines"];
281
        $query{select} = ["borrowernumber", { sum => 'amountoutstanding', -as => 'total_fines'} ];
282
        $query{as} = [qw/borrowernumber  total_fines/];
283
        $query{group_by} = ["borrowernumber"];
284
        $query{having}{total_fines}{'<='}=$params->{fine_max} if defined $params->{fine_max};
285
        $query{having}{total_fines}{'>='}=$params->{fine_min} if defined $params->{fine_min};
286
    }
287
    return Koha::Patrons->search($search_params,\%query);
288
}
289
290
=head3 update_category
291
292
    Koha::Patrons->search->update_category( {
293
            to   => $to,
294
        });
295
296
Update supplied patrons from one category to another and take care of guarantor info.
297
To make sure all the conditions are met, the caller has the responsibility to
298
call search_patrons_to_update to filter the Koha::Patrons set
299
300
=cut
301
302
sub update_category {
303
    my ( $self, $params ) = @_;
304
    my $to = $params->{to};
305
306
    my $to_cat = Koha::Patron::Categories->find($to);
307
    return unless $to_cat;
308
309
    my $counter = 0;
310
    my $remove_guarantor = ( $to_cat->category_type ne 'C' || $to_cat->category_type ne 'P' ) ? 1 : 0;
311
    while( my $patron = $self->next ) {
312
        $counter++;
313
        if ( $remove_guarantor && ($patron->category->category_type eq 'C' || $patron->category->category_type eq 'P') ) {
314
            $patron->guarantorid(0);
315
            $patron->contactname('');
316
            $patron->contactfirstname('');
317
            $patron->contacttitle('');
318
            $patron->relationship('');
319
        }
320
        $patron->categorycode($to);
321
        $patron->store();
322
    }
323
    return $counter;
324
>>>>>>> Bug 17168: Add a command line script for updating patron category based on status
325
}
326
239
=head3 _type
327
=head3 _type
240
328
241
=cut
329
=cut
(-)a/misc/cronjobs/update_patrons_category.pl (+259 lines)
Line 0 Link Here
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. Options are cumulative.
39
40
=head1 SYNOPSIS
41
42
update_patrons_category.pl -f=categorycode -t=categorycode
43
                          [-b=branchcode] [-au] [-ao] [-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
   -ao          update if over  maximum age for current category
53
   -au          update if under minimuum age  current category
54
   -fo=X        update if fines over  X amount
55
   -fu=X        update if fines under X amount
56
   -rb=date     update if registration date is before given date
57
   -ra=date     update if registration date is after a given date
58
   --field name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is equal to given <value>
59
   -v           verbose mode
60
   -confirm     commit changes to db, no action will be taken unless this switch is included
61
   -b           <branchname>       only deal with patrons from this library/branch
62
   -f           <categorycode>     change patron category from this category
63
   -t           <categorycode>     change patron category to this category
64
65
=head1 OPTIONS
66
67
=over 8
68
69
=item B<--help>
70
71
Print a brief help message and exits.
72
73
=item B<--man>
74
75
Prints the manual page and exits.
76
77
=item B<-v>
78
79
Verbose. Without this flag set, only fatal errors are reported.
80
81
=item B<--confirm>
82
83
Commit changes. Unless this flag set is, the script will report changes but not actually execute them on the database.
84
85
=item B<-b>
86
87
changes patrons for one specific branch. Use the value in the
88
branches.branchcode table.
89
90
=item B<-f>
91
92
*required* defines the category to update. Expects the code from categories.categorycode.
93
94
=item B<-t>
95
96
*required* defines the category patrons will be converted to. Expects the code from categories.categorycode.
97
98
=item B<-ao>
99
100
Update patron only if they are above the maximum age range specified for the 'from' category.
101
102
=item B<-au>
103
104
Update patron only if they are below the minimum age range specified for the 'from' category.
105
106
=item B<-fo=X>
107
108
Supply a number and only account with fines over this number will be updated.
109
110
=item B<-fu=X>
111
112
Supply a number and only account with fines under this number will be updated.
113
114
=item B<-rb=date>
115
116
Enter a date in ISO format YYYY-MM-DD and only patrons registered before this date wil be updated.
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
=item B<--field column=value>
123
124
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)
125
126
e.g.
127
--field dateexpiry=2016-01-01
128
will update all patrons who expired on that date, useful for schools etc.
129
130
=back
131
132
=head1 DESCRIPTION
133
134
This script is designed to update patrons from one category to another.
135
136
=head1 USAGE EXAMPLES
137
138
C<update_patron_categories.pl> - Suggests that you read this help. :)
139
140
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.
141
142
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.
143
144
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.
145
146
=cut
147
148
# These variables are set by command line options.
149
# They are initially set to default values.
150
151
my $help     = 0;
152
my $man      = 0;
153
my $verbose  = 0;
154
my $doit = 0;
155
my $au;
156
my $ao;
157
my $min_dob;
158
my $max_dob;
159
my $remove_guarantors = 0;
160
my $fine_min;
161
my $fine_max;
162
my $fromcat;
163
my $tocat;
164
my $reg_bef;
165
my $reg_aft;
166
my $branch_lim;
167
my %fields;
168
169
GetOptions(
170
    'help|?'  => \$help,
171
    'man'     => \$man,
172
    'v'       => \$verbose,
173
    'confirm' => \$doit,
174
    'f=s'     => \$fromcat,
175
    't=s'     => \$tocat,
176
    'ao'       => \$ao,
177
    'au'       => \$au,
178
    'fo=s'    => \$fine_min,
179
    'fu=s'    => \$fine_max,
180
    'rb=s'    => \$reg_bef,
181
    'ra=s'    => \$reg_aft,
182
    'b=s'     => \$branch_lim,
183
    'field=s' => \%fields
184
) or pod2usage(2);
185
pod2usage(1) if $help;
186
pod2usage( -verbose => 2 ) if $man;
187
188
if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
189
    print "Must supply category from and to (-f & -t) please specify -help for usage tips.\n";
190
    exit;
191
}
192
193
($verbose && !$doit) and print "No actions will be taken (test mode)\n";
194
195
$verbose and print "Will update patrons from $fromcat to $tocat with conditions below (if any)\n";
196
197
my %params;
198
199
if ( $reg_bef || $reg_aft ){
200
    my $date_bef;
201
    my $date_aft;
202
    if (defined $reg_bef) {eval { $date_bef = dt_from_string( $reg_bef, 'iso' ); };}
203
    die "$reg_bef is not a valid date before, aborting! Use a date in format YYYY-MM-DD.$@"
204
    if $@;
205
    if (defined $reg_aft) {eval { $date_aft = dt_from_string( $reg_aft, 'iso' ); };}
206
    die "$reg_bef is not a valid date after, aborting! Use a date in format YYYY-MM-DD.$@"
207
    if $@;
208
    $params{dateenrolled}{'<='}=$reg_bef if defined $date_bef;
209
    $params{dateenrolled}{'>='}=$reg_aft if defined $date_aft;
210
}
211
212
my $cat_from = Koha::Patron::Categories->find($fromcat);
213
my $cat_to   = Koha::Patron::Categories->find($tocat);
214
die "Categories not found" unless $cat_from && $cat_to;
215
216
$params{"me.categorycode"} = $fromcat;
217
$params{"me.branchcode"} = $branch_lim if $branch_lim;
218
219
if ($verbose) {
220
    print "Conditions:\n";
221
    print "    Registered before $reg_bef\n" if $reg_bef;
222
    print "    Registered after  $reg_aft\n" if $reg_aft;
223
    print "    Total fines more than $fine_min\n" if $fine_min;
224
    print "    Total fines less than $fine_max\n" if $fine_max;
225
    print "    Age below minimum for ".$cat_from->description."\n" if $au;
226
    print "    Age above maximum for ".$cat_from->description."\n" if $ao;
227
    if (defined $branch_lim) {
228
        print "    Branchcode of patron is $branch_lim\n";
229
    }
230
}
231
232
while (my ($key,$value) = each %fields ) {
233
    $verbose and print "    Borrower column $key is equal to $value\n";
234
    $params{"me.".$key} = $value;
235
}
236
237
my $target_patrons = Koha::Patrons->search_patrons_to_update({
238
        from => $fromcat,
239
        search_params => \%params,
240
        au => $au,
241
        ao => $ao,
242
        fine_min => $fine_min,
243
        fine_max => $fine_max,
244
    });
245
my $patrons_found = $target_patrons->count;
246
my $actually_updated = 0;
247
my $testdisplay  = $doit ? "" : "WOULD HAVE ";
248
if ( $verbose ) {
249
    while ( my $target_patron = $target_patrons->next() ){
250
    my $target = Koha::Patrons->find( $target_patron->borrowernumber );
251
    $verbose and print $testdisplay."Updated ".$target->firstname." ".$target->surname." from $fromcat to $tocat\n";
252
    }
253
    $target_patrons->reset;
254
}
255
if ( $doit ) {
256
    $actually_updated = $target_patrons->update_category({to=>$tocat});
257
}
258
259
$verbose and print "$patrons_found found, $actually_updated updated\n";
(-)a/t/db_dependent/Patrons.t (-2 / +95 lines)
Lines 17-23 Link Here
17
17
18
use Modern::Perl;
18
use Modern::Perl;
19
19
20
use Test::More tests => 17;
20
use Test::More tests => 18;
21
use Test::Warn;
21
use Test::Warn;
22
22
23
use C4::Context;
23
use C4::Context;
Lines 104-108 foreach my $b ( $patrons->as_list() ) { Link Here
104
    is( $b->categorycode(), $categorycode, "Iteration returns a patron object" );
104
    is( $b->categorycode(), $categorycode, "Iteration returns a patron object" );
105
}
105
}
106
106
107
subtest "Update patron categories" => sub {
108
    plan tests => 23;
109
    $builder->schema->resultset( 'Issue' )->delete_all;
110
    $builder->schema->resultset( 'Borrower' )->delete_all;
111
    $builder->schema->resultset( 'Category' )->delete_all;
112
    my $c_categorycode = $builder->build({ source => 'Category', value => {
113
            category_type=>'C',
114
            upperagelimit=>17,
115
            dateofbirthrequired=>5,
116
        } })->{categorycode};
117
    my $a_categorycode = $builder->build({ source => 'Category', value => {category_type=>'A'} })->{categorycode};
118
    my $p_categorycode = $builder->build({ source => 'Category', value => {category_type=>'P'} })->{categorycode};
119
    my $i_categorycode = $builder->build({ source => 'Category', value => {category_type=>'I'} })->{categorycode};
120
    my $branchcode1 = $builder->build({ source => 'Branch' })->{branchcode};
121
    my $branchcode2 = $builder->build({ source => 'Branch' })->{branchcode};
122
    my $adult1 = $builder->build({source => 'Borrower', value => {
123
            categorycode=>$a_categorycode,
124
            branchcode=>$branchcode1,
125
            dateenrolled=>'2018-01-01',
126
            sort1 =>'quack',
127
        }
128
    });
129
    my $adult2 = $builder->build({source => 'Borrower', value => {
130
            categorycode=>$a_categorycode,
131
            branchcode=>$branchcode2,
132
            dateenrolled=>'2017-01-01',
133
        }
134
    });
135
    my $inst = $builder->build({source => 'Borrower', value => {
136
            categorycode=>$i_categorycode,
137
            branchcode=>$branchcode2,
138
        }
139
    });
140
    my $prof = $builder->build({source => 'Borrower', value => {
141
            categorycode=>$p_categorycode,
142
            branchcode=>$branchcode2,
143
            guarantorid=>$inst->{borrowernumber},
144
        }
145
    });
146
    my $child1 = $builder->build({source => 'Borrower', value => {
147
            dateofbirth => dt_from_string->add(years=>-4),
148
            categorycode=>$c_categorycode,
149
            guarantorid=>$adult1->{borrowernumber},
150
            branchcode=>$branchcode1,
151
        }
152
    });
153
    my $child2 = $builder->build({source => 'Borrower', value => {
154
            dateofbirth => dt_from_string->add(years=>-8),
155
            categorycode=>$c_categorycode,
156
            guarantorid=>$adult1->{borrowernumber},
157
            branchcode=>$branchcode1,
158
        }
159
    });
160
    my $child3 = $builder->build({source => 'Borrower', value => {
161
            dateofbirth => dt_from_string->add(years=>-18),
162
            categorycode=>$c_categorycode,
163
            guarantorid=>$adult1->{borrowernumber},
164
            branchcode=>$branchcode1,
165
        }
166
    });
167
    $builder->build({source=>'Accountline',value => {amountoutstanding=>4.99,borrowernumber=>$adult1->{borrowernumber}}});
168
    $builder->build({source=>'Accountline',value => {amountoutstanding=>5.01,borrowernumber=>$adult2->{borrowernumber}}});
169
170
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode})->count,3,'Three patrons in child category');
171
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode,au=>1})->count,1,'One under age patron in child category');
172
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode,au=>1})->next->borrowernumber,$child1->{borrowernumber},'Under age patron in child category is expected one');
173
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode,ao=>1})->count,1,'One over age patron in child category');
174
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode,ao=>1})->next->borrowernumber,$child3->{borrowernumber},'Over age patron in child category is expected one');
175
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{branchcode=>$branchcode2}})->count,1,'One patron in branch 2');
176
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{branchcode=>$branchcode2}})->next->borrowernumber,$adult2->{borrowernumber},'Adult patron in branch 2 is expected one');
177
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,fine_min=>5})->count,1,'One patron with fines over $5');
178
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,fine_min=>5})->next->borrowernumber,$adult2->{borrowernumber},'One patron with fines over $5 is expected one');
179
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,fine_max=>5})->count,1,'One patron with fines under $5');
180
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,fine_max=>5})->next->borrowernumber,$adult1->{borrowernumber},'One patron with fines under $5 is expected one');
181
182
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{dateenrolled=>{'<'=>'2018-01-01'}}})->count,1,'One adult patron enrolled before date');
183
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{dateenrolled=>{'<'=>'2018-01-01'}}})->next->borrowernumber,$adult2->{borrowernumber},'One adult patron enrolled before date is expected one');
184
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{dateenrolled=>{'>'=>'2017-01-01'}}})->count,1,'One adult patron enrolled after date');
185
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{dateenrolled=>{'>'=>'2017-01-01'}}})->next->borrowernumber,$adult1->{borrowernumber},'One adult patron enrolled after date is expected one');
186
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{'sort1'=>'quack'}})->count,1,'One adult patron has a quack');
187
    is( Koha::Patrons->search_patrons_to_update({from=>$a_categorycode,search_params=>{'sort1'=>'quack'}})->next->borrowernumber,$adult1->{borrowernumber},'One adult patron with a quack is expected one');
188
189
    is( Koha::Patrons->find($adult1->{borrowernumber})->guarantees->count,3,'Guarantor has 3 guarantees');
190
    is( Koha::Patrons->search_patrons_to_update({from=>$c_categorycode,au=>1})->update_category({to=>$a_categorycode}),1,'One child patron updated to adult category');
191
    is( Koha::Patrons->find($adult1->{borrowernumber})->guarantees->count,2,'Guarantee was removed when made adult');
192
193
    is( Koha::Patrons->find($inst->{borrowernumber})->guarantees->count,1,'Guarantor has 1 guarantees');
194
    is( Koha::Patrons->search_patrons_to_update({from=>$p_categorycode})->update_category({to=>$a_categorycode}),1,'One professional patron updated to adult category');
195
    is( Koha::Patrons->find($inst->{borrowernumber})->guarantees->count,0,'Guarantee was removed when made adult');
196
197
};
198
199
200
107
$schema->storage->txn_rollback();
201
$schema->storage->txn_rollback();
108
202
109
- 

Return to bug 17168