From 7a1ac745761c6eca721d69d30771322c711dec52 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 31 Aug 2020 15:59:24 +0200 Subject: [PATCH] Bug 25624: Add more flexibily with --where and literal search Signed-off-by: Nick Clemens --- 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 is a column in the borrowers table, patrons will be updated if the field is equal to given - -dn --notfield name=value where is a column in the borrowers table, patrons will be updated if the field is not equal to given - -dl --likefield name=value where is a column in the borrowers table, patrons will be updated if the field is like the given - -dnl --notlikefield name=value where is a column in the borrowers table, patrons will be updated if the field is not like the given + --where 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 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.11.0