From add33027f75ec25952e8508dcaa23563d075ae38 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Mon, 31 Aug 2020 15:59:24 +0200
Subject: [PATCH] Bug 25624: Add more flexibily with --where and literal search

---
 misc/cronjobs/update_patrons_category.pl | 56 ++++++------------------
 1 file changed, 13 insertions(+), 43 deletions(-)

diff --git a/misc/cronjobs/update_patrons_category.pl b/misc/cronjobs/update_patrons_category.pl
index 7174443a36..21114cba7d 100755
--- a/misc/cronjobs/update_patrons_category.pl
+++ b/misc/cronjobs/update_patrons_category.pl
@@ -56,9 +56,7 @@ Options:
    -rb=date --regbefore     update if registration date is before given date
    -ra=date --regafter      update if registration date is after a given date
    -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>
-   -dn --notfield name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is not equal to given <value>
-   -dl --likefield name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is like the given <value>
-   -dnl --notlikefield name=value  where <name> is a column in the borrowers table, patrons will be updated if the field is not like the 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
@@ -131,33 +129,18 @@ e.g.
 --field dateexpiry=2016-01-01
 will update all patrons who expired on that date, useful for schools etc.
 
-=item B<--notfield column=value | -dn column=value>
+=item B<--where $conditions>
 
-Use this flag to specify a column in the borrowers table and update only patrons whose value in that column does not equal the value supplied (repeatable)
-A value of null will check for a field that is not set.
+Use this option to specify a condition built with columns from the borrowers table
 
 e.g.
---notfield email=NULL
+--where 'email IS NULL'
 will update all patrons with no value for email
 
-=item B<--likefield column=value | -dl column=value>
-
-Use this flag to specify a column in the borrowers table and update only patrons whose value in that column is like the value supplied (repeatable)
-use '%' as a wildcard.
-A value of null will check for a field that is not set.
-
-e.g.
---likefield categorycode=%CHILD
+--where 'categorycode LIKE "%CHILD"'
 will update all patrons with a category ending in CHILD.
 
-=item B<--notlikefield column=value | -dnl column=value>
-
-Use this flag to specify a column in the borrowers table and update only patrons whose value in that column is not like the value supplied (repeatable)
-use '%' as a wildcard.
-A value of null will check for a field that is not set.
-
-e.g.
---notlikefield categorycode=RESIDENT%
+--where 'categorycode LIKE RESIDENT%'
 will update all patrons whose category does not begin with RESIDENT.
 
 =back
@@ -196,9 +179,7 @@ my $reg_bef;
 my $reg_aft;
 my $branch_lim;
 my %fields;
-my %not_fields;
-my %like_fields;
-my %not_like_fields;
+my @where;
 
 GetOptions(
     'help|?'          => \$help,
@@ -215,9 +196,7 @@ GetOptions(
     'ra|regafter=s'   => \$reg_aft,
     'b|branch=s'      => \$branch_lim,
     'd|field=s'       => \%fields,
-    'dn|notfield=s'   => \%not_fields,
-    'dl|likefield=s'       => \%like_fields,
-    'dnl|notlikefield=s'   => \%not_like_fields
+    'where=s'         => \@where,
 );
 
 pod2usage(1) if $help;
@@ -278,20 +257,11 @@ while ( my ( $key, $value ) = each %fields ) {
     $value = undef if lc($value) eq 'null';
     $params{ "me." . $key } = $value;
 }
-while ( my ( $key, $value ) = each %not_fields ) {
-    $verbose and print "    Borrower column $key is not $value\n";
-    $value = undef if lc($value) eq 'null';
-    $params{ "me." . $key } = { '!=' => $value };
-}
-while ( my ( $key, $value ) = each %like_fields ) {
-    $verbose and print "    Borrower column $key is like $value\n";
-    $params{ "me." . $key } = { 'LIKE' => $value };
-}
-while ( my ( $key, $value ) = each %not_like_fields ) {
-    $verbose and print "    Borrower column $key is not like $value\n";
-    $params{ "me." . $key } = { 'NOT LIKE' => $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 )
+  ->search( \$where_literal )
+  ->search_patrons_to_update_category(
     {
         from          => $fromcat,
         search_params => \%params,
-- 
2.20.1