Attachment 109427 Details for Bug 25624 – Bug 25624: Add --where option to update_patrons_category.pl

[patch] Bug 25624: Add --where option to update_patrons_category.pl

Bug-25624-Add---where-option-to-updatepatronscateg.patch (text/plain), 4.10 KB, created by Jonathan Druart on 2020-09-01 13:13:37 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2020-09-01 13:13:37 UTC

Size: 4.10 KB

patch

obsolete

>From 38ce600c91e1d1bf7d0c7b879fc2f943f0a707df Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Tue, 1 Sep 2020 15:07:49 +0200
>Subject: [PATCH] Bug 25624: Add --where option to update_patrons_category.pl
>
>The script did not allow to find empty fields or use wildcards
>
>With this new option we will now have the ability to filter patrons by
>some of their attributes.
>
>Test plan:
>1 - Run the script with no parameters and verify the help explains the parameters
>2 - Try the script with one or more --where parameters, like:
>  --where "firstname='koha'"
>3 - Test null values
>  --where "firstname IS NULL"
>4 - Test like values with wildcards
>  --where "firstname LIKE '%a%'
>5 - Test like with the word null to find fields containing the word rather than being unset
>---
> misc/cronjobs/update_patrons_category.pl | 33 ++++++++++++++++++++----
> 1 file changed, 28 insertions(+), 5 deletions(-)
>
>diff --git a/misc/cronjobs/update_patrons_category.pl b/misc/cronjobs/update_patrons_category.pl
>index 207f9b6cd9..86541a818d 100755
>--- a/misc/cronjobs/update_patrons_category.pl
>+++ b/misc/cronjobs/update_patrons_category.pl
>@@ -55,7 +55,8 @@ Options:
>    -fu=X --fineunder=X      update if fines under X amount
>    -rb=date --regbefore     update if registration date is before given date
>    -ra=date --regafter      update if registration date is after a given date
>-   -d --dbfield name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is equal to given <value>
>+   -d --field name=value    where <name> is a column in the borrowers table, patrons will be updated if the field is equal to given <value>
>+   --where <conditions>     where clause to add to the query
>    -v -verbose              verbose mode
>    -c --confirm             commit changes to db, no action will be taken unless this switch is included
>    -b --branch <branchname> only deal with patrons from this library/branch
>@@ -121,12 +122,27 @@ Enter a date in ISO format YYYY-MM-DD and only patrons registered after this dat
> 
> =item B<--field column=value | -d column=value>
> 
>-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)
>+Use this flag to specify a column in the borrowers table and update only patrons whose value in that column equals the value supplied (repeatable)
>+A value of null will check for a field that is not set.
> 
> e.g.
> --field dateexpiry=2016-01-01
> will update all patrons who expired on that date, useful for schools etc.
> 
>+=item B<--where $conditions>
>+
>+Use this option to specify a condition built with columns from the borrowers table
>+
>+e.g.
>+--where 'email IS NULL'
>+will update all patrons with no value for email
>+
>+--where 'categorycode LIKE "%CHILD"'
>+will update all patrons with a category ending in CHILD.
>+
>+--where 'categorycode LIKE RESIDENT%'
>+will update all patrons whose category does not begin with RESIDENT.
>+
> =back
> 
> =head1 DESCRIPTION
>@@ -163,6 +179,7 @@ my $reg_bef;
> my $reg_aft;
> my $branch_lim;
> my %fields;
>+my @where;
> 
> GetOptions(
>     'help|?'          => \$help,
>@@ -178,7 +195,8 @@ GetOptions(
>     'rb|regbefore=s'  => \$reg_bef,
>     'ra|regafter=s'   => \$reg_aft,
>     'b|branch=s'      => \$branch_lim,
>-    'd|field=s'       => \%fields
>+    'd|field=s'       => \%fields,
>+    'where=s'         => \@where,
> );
> 
> pod2usage(1) if $help;
>@@ -235,10 +253,15 @@ if ($verbose) {
> }
> 
> while ( my ( $key, $value ) = each %fields ) {
>-    $verbose and print "    Borrower column $key is equal to $value\n";
>+    $verbose and print "    Borrower column $key is $value\n";
>+    $value = undef if lc($value) eq 'null';
>     $params{ "me." . $key } = $value;
> }
>-my $target_patrons = Koha::Patrons->search(\%params)->search_patrons_to_update_category(
>+
>+my $where_literal = join ' AND ', @where;
>+my $target_patrons = Koha::Patrons->search( \%params );
>+$target_patrons = $target_patrons->search( \$where_literal ) if @where;
>+$target_patrons = $target_patrons->search_patrons_to_update_category(
>     {
>         from          => $fromcat,
>         search_params => \%params,
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 25624: 105439 | 105605 | 106965 | 107291 | 107292 | 109116 | 109370 | 109421 | 109427 | 109440 | 110747