From fb56ea04d1da7ece8c4152521f1f1f6778fc3278 Mon Sep 17 00:00:00 2001 From: David Cook Date: Thu, 15 Sep 2022 04:36:32 +0000 Subject: [PATCH] Bug 23817: Normalize phone number when searching This patch rewrites the phone number DBIC query when sent from DataTables like in the Patron module. Test plan: 0. Apply patch and koha-plack --restart kohadev 1. Go to http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=modify&destination=circ&borrowernumber=51 2. Input '1-(234)-567-8901' into the 'Primary phone' field 3. Note on the next screen the phone number is shown as '1-(234)-567-8901' 4. Go to http://localhost:8081/cgi-bin/koha/members/members-home.pl 5. Choose "Search field" of "All phones" 6. Search for '12345678901' 7. Note you are taken to http://localhost:8081/cgi-bin/koha/members/moremember.pl?borrowernumber=51 8. Try out different permutations like '234 567 8901' or '5678901' 9. Note that every permutation works to find the borrower 10. prove t/Koha/Patrons/DataTables.t Signed-off-by: David Nind --- Koha/Patrons/DataTables.pm | 85 +++++++++ Koha/REST/Plugin/Objects.pm | 7 + Koha/REST/V1/Patrons.pm | 14 ++ .../intranet-tmpl/prog/js/staff-global.js | 3 + t/Koha/Patrons/DataTables.t | 180 ++++++++++++++++++ 5 files changed, 289 insertions(+) create mode 100644 Koha/Patrons/DataTables.pm create mode 100755 t/Koha/Patrons/DataTables.t diff --git a/Koha/Patrons/DataTables.pm b/Koha/Patrons/DataTables.pm new file mode 100644 index 0000000000..3b57add836 --- /dev/null +++ b/Koha/Patrons/DataTables.pm @@ -0,0 +1,85 @@ +package Koha::Patrons::DataTables; + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +=head1 NAME + +Koha::Patrons::DataTables + +=head1 API + +=head2 Methods + +=cut + +sub _parse_dbic_query_for_dt { + my ( $class, $q_params, $collector ) = @_; + my %allowed_keys = ( + 'me.phone' => 1, + 'me.phonepro' => 1, + 'me.B_phone' => 1, + 'me.altcontactphone' => 1, + 'me.mobile' => 1, + ); + if ( ref($q_params) && ref($q_params) eq 'HASH' ) { + foreach my $key ( keys %{$q_params} ) { + if ( $allowed_keys{$key} ) { + my $query = $q_params->{$key}; + if ( $query->{'like'} ) { + my $like = $query->{'like'}; + + #NOTE: Remove the hashref, and add a data structure to add to the WHERE clause via the attributes... + delete $q_params->{$key}; + my $collection = \[ "regexp_replace($key,?,?) LIKE ?", "[^0-9]", "", $like ]; + push( @$collector, $collection ); + } + } else { + $q_params->{$key} = $class->_parse_dbic_query_for_dt( $q_params->{$key}, $collector ); + } + } + return $q_params; + } elsif ( ref($q_params) && ref($q_params) eq 'ARRAY' ) { + foreach my $param (@$q_params) { + $param = $class->_parse_dbic_query_for_dt( $param, $collector ); + } + return $q_params; + } else { + return $q_params; + } +} + +=head3 preprocess_dbic_for_datatables + +Method for preprocessing DBIC queries sent from DataTables, so a WHERE +clause can be injected into the query if one doesn't already exist. + +=cut + +sub preprocess_dbic_for_datatables { + my ( $class, $q_params, $attributes ) = @_; + my @collector = (); + my $rewritten_q_params = $class->_parse_dbic_query_for_dt( $q_params, \@collector ); + unless ( $attributes->{where} ) { + foreach my $collection (@collector) { + push( @{ $attributes->{where} }, $collection ); + } + } + return 1; +} + +1; diff --git a/Koha/REST/Plugin/Objects.pm b/Koha/REST/Plugin/Objects.pm index c51f899f40..9f38abc352 100644 --- a/Koha/REST/Plugin/Objects.pm +++ b/Koha/REST/Plugin/Objects.pm @@ -277,6 +277,13 @@ controller, and thus shouldn't be called twice in it. $c->dbic_validate_operators( { filtered_params => $filtered_params } ); + # Pre-process DBIC queries if controller supports it + if ( $c->stash('koha.request_id') ) { + if ( $c->can('preprocess_dbic_for_datatables') ) { + $c->preprocess_dbic_for_datatables( $filtered_params, $attributes ); + } + } + # Generate the resultset my $objects_rs = $result_set->search( $filtered_params, $attributes ); diff --git a/Koha/REST/V1/Patrons.pm b/Koha/REST/V1/Patrons.pm index 05ae08688e..57e976bda7 100644 --- a/Koha/REST/V1/Patrons.pm +++ b/Koha/REST/V1/Patrons.pm @@ -23,6 +23,7 @@ use Koha::Database; use Koha::Exceptions; use Koha::Patrons; use C4::Letters qw( GetPreparedLetter EnqueueLetter SendQueuedMessages ); +use Koha::Patrons::DataTables; use List::MoreUtils qw(any); use Scalar::Util qw( blessed ); @@ -496,4 +497,17 @@ sub guarantors_can_see_checkouts { }; } +=head3 preprocess_dbic_for_datatables + +Method for preprocessing DBIC queries sent from DataTables, so a WHERE +clause can be injected into the query if one doesn't already exist. + +=cut + +sub preprocess_dbic_for_datatables { + my ( $c, $q_params, $attributes ) = @_; + Koha::Patrons::DataTables->preprocess_dbic_for_datatables( $q_params, $attributes ); + return 1; +} + 1; diff --git a/koha-tmpl/intranet-tmpl/prog/js/staff-global.js b/koha-tmpl/intranet-tmpl/prog/js/staff-global.js index 264ad54f71..4020b25212 100644 --- a/koha-tmpl/intranet-tmpl/prog/js/staff-global.js +++ b/koha-tmpl/intranet-tmpl/prog/js/staff-global.js @@ -916,6 +916,9 @@ function buildPatronSearchQuery(term, options) { let searched_attribute_fields = []; // Search fields: If search_fields option exists, we use that if (typeof options !== "undefined" && options.search_fields) { + if (options.search_fields == "all_phones") { + patterns = [term.replace(/[^\+0-9]/g, "")]; + } expand_fields = expandPatronSearchFields(options.search_fields); expand_fields.split("|").forEach(function (field, i) { if (field.startsWith("_ATTR_")) { diff --git a/t/Koha/Patrons/DataTables.t b/t/Koha/Patrons/DataTables.t new file mode 100755 index 0000000000..f9c2eebd69 --- /dev/null +++ b/t/Koha/Patrons/DataTables.t @@ -0,0 +1,180 @@ +#!/usr/bin/perl + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use Test::NoWarnings; +use Test::More tests => 3; + +use_ok("Koha::Patrons::DataTables"); + +subtest 'Preprocessor tests' => sub { + plan tests => 2; + my $q_params = [ + { + '-and' => [ + [ + { 'me.phone' => { 'like' => '%2345678901%' } }, + { 'me.phonepro' => { 'like' => '%2345678901%' } }, + { 'me.B_phone' => { 'like' => '%2345678901%' } }, + { 'me.altcontactphone' => { 'like' => '%2345678901%' } }, + { 'me.mobile' => { 'like' => '%2345678901%' } } + ] + ] + }, + { + '-or' => [ + { 'me.phone' => { 'like' => '%234 567 8901%' } }, + { 'me.phonepro' => { 'like' => '%234 567 8901%' } }, + { 'me.B_phone' => { 'like' => '%234 567 8901%' } }, + { 'me.altcontactphone' => { 'like' => '%234 567 8901%' } }, + { 'me.mobile' => { 'like' => '%234 567 8901%' } } + ] + } + ]; + my $attributes = { + 'prefetch' => [ + 'extended_attributes', + 'library' + ], + 'order_by' => [ + { '-asc' => 'me.surname' }, + { '-asc' => 'me.preferred_name' }, + { '-asc' => 'me.firstname' }, + { '-asc' => 'me.middle_name' }, + { '-asc' => 'me.othernames' }, + { '-asc' => 'me.streetnumber' }, + { '-asc' => 'me.address' }, + { '-asc' => 'me.address2' }, + { '-asc' => 'me.city' }, + { '-asc' => 'me.state' }, + { '-asc' => 'me.zipcode' }, + { '-asc' => 'me.country' } + ], + 'page' => 1, + 'rows' => 20 + }; + my $expected_params = [ + { + '-and' => [ + [ + {}, + {}, + {}, + {}, + {} + ] + ] + }, + { + '-or' => [ + {}, + {}, + {}, + {}, + {} + ] + } + ]; + my $expected_attributes = { + 'order_by' => [ + { '-asc' => 'me.surname' }, + { '-asc' => 'me.preferred_name' }, + { '-asc' => 'me.firstname' }, + { '-asc' => 'me.middle_name' }, + { '-asc' => 'me.othernames' }, + { '-asc' => 'me.streetnumber' }, + { '-asc' => 'me.address' }, + { '-asc' => 'me.address2' }, + { '-asc' => 'me.city' }, + { '-asc' => 'me.state' }, + { '-asc' => 'me.zipcode' }, + { '-asc' => 'me.country' } + ], + 'page' => 1, + 'rows' => 20, + 'prefetch' => [ + 'extended_attributes', + 'library' + ], + 'where' => [ + \[ + 'regexp_replace(me.phone,?,?) LIKE ?', + '[^0-9]', + '', + '%2345678901%' + ], + \[ + 'regexp_replace(me.phonepro,?,?) LIKE ?', + '[^0-9]', + '', + '%2345678901%' + ], + \[ + 'regexp_replace(me.B_phone,?,?) LIKE ?', + '[^0-9]', + '', + '%2345678901%' + ], + \[ + 'regexp_replace(me.altcontactphone,?,?) LIKE ?', + '[^0-9]', + '', + '%2345678901%' + ], + \[ + 'regexp_replace(me.mobile,?,?) LIKE ?', + '[^0-9]', + '', + '%2345678901%' + ], + \[ + 'regexp_replace(me.phone,?,?) LIKE ?', + '[^0-9]', + '', + '%234 567 8901%' + ], + \[ + 'regexp_replace(me.phonepro,?,?) LIKE ?', + '[^0-9]', + '', + '%234 567 8901%' + ], + \[ + 'regexp_replace(me.B_phone,?,?) LIKE ?', + '[^0-9]', + '', + '%234 567 8901%' + ], + \[ + 'regexp_replace(me.altcontactphone,?,?) LIKE ?', + '[^0-9]', + '', + '%234 567 8901%' + ], + \[ + 'regexp_replace(me.mobile,?,?) LIKE ?', + '[^0-9]', + '', + '%234 567 8901%' + ] + ] + }; + Koha::Patrons::DataTables->preprocess_dbic_for_datatables( $q_params, $attributes ); + is_deeply( $q_params, $expected_params, "Params preprocessed as expected" ); + is_deeply( $attributes, $expected_attributes, "Attributes preprocessed as expected" ); +}; -- 2.39.5