From e3a8f1e20d871fca0b50c57146d2194ffdda00c2 Mon Sep 17 00:00:00 2001
From: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
Date: Fri, 23 Oct 2009 16:05:33 +0200
Subject: [PATCH] MT 2168 : Logical AND between multiple words strings

C4::SQLHelper was doing an implicit OR of multiple words strings
It is now fixed and does an AND
---
 C4/SQLHelper.pm            | 15 +++++++++++----
 t/db_dependent/SQLHelper.t | 16 +++++++++++-----
 2 files changed, 22 insertions(+), 9 deletions(-)

diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm
index 7ee3c70951..08cfdf3956 100644
--- a/C4/SQLHelper.pm
+++ b/C4/SQLHelper.pm
@@ -334,7 +334,14 @@ sub _filter_fields{
 		}
 	} 
 	else{
-		return _filter_string($tablename,$filter_input,$searchtype,$filtercolumns);
+		my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns);
+		if ($keys){
+		my $stringkey="(".join (") AND (",@$keys).")";
+		return [$stringkey],$values;
+		}
+		else {
+		return ();
+		}
 	}
 
 	return (\@keys,\@values);
@@ -372,8 +379,8 @@ sub _filter_string{
 	my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
 	my $columns= _get_columns($tablename);
 	my (@values,@keys);
-	my @localkeys;
 	foreach my $operand (@operands){
+		my @localkeys;
 		foreach my $field (@columns_filtered){
 			my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
 			if ($tmpkeys){
@@ -381,9 +388,9 @@ sub _filter_string{
 				push @localkeys,@$tmpkeys;
 			}
 		}
+		my $sql= join (' OR ', @localkeys);
+		push @keys, $sql;
 	}
-	my $sql= join (' OR ', @localkeys);
-	push @keys, $sql;
 
 	if (@keys){
 		return (\@keys,\@values);
diff --git a/t/db_dependent/SQLHelper.t b/t/db_dependent/SQLHelper.t
index d3be1f3883..4932a05d5d 100755
--- a/t/db_dependent/SQLHelper.t
+++ b/t/db_dependent/SQLHelper.t
@@ -5,11 +5,12 @@
 
 use strict;
 use warnings;
-use Data::Dumper;
+use YAML;
 
+use C4::Debug;
 use C4::SQLHelper qw(:all);
 
-use Test::More tests => 14;
+use Test::More tests => 15;
 
 BEGIN {
     use_ok('C4::SQLHelper');
@@ -19,21 +20,24 @@ use C4::Branch;
 my @categories=C4::Category->all;
 my $branches=C4::Branch->GetBranches;
 my @branchcodes=keys %$branches;
-my $borrid;
+my ($borrid, $borrtmp);
+$borrtmp=InsertInTable("borrowers",{firstname=>"Jean",surname=>"cocteau",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]});
 ok($borrid=InsertInTable("borrowers",{firstname=>"Jean",surname=>"Valjean",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]}),"Insert In Table");
-ok(my $status=UpdateInTable("borrowers",{borrowernumber=>$borrid,firstname=>"Jean",surname=>"Valjean",city=>"ma6tVaCracker ",zipcode=>" ",email=>"email", branchcode=>$branchcodes[1]}),"Update In Table");
+ok(my $status=UpdateInTable("borrowers",{borrowernumber=>$borrid,firstname=>"Jean",surname=>"Valjean",city=>"Dampierre",zipcode=>" ",email=>"email", branchcode=>$branchcodes[1]}),"Update In Table");
 my $borrowers=SearchInTable("borrowers");
 ok(@$borrowers>0, "Search In Table All values");
 $borrowers=SearchInTable("borrowers",{firstname=>"Jean"});
 ok(@$borrowers>0, "Search In Table hashref");
 $borrowers=SearchInTable("borrowers","Jean");
 ok(@$borrowers>0, "Search In Table string");
+eval{$borrowers=SearchInTable("borrowers","Jean Valjean")};
+ok(scalar(@$borrowers)==1 && !($@), "Search In Table does an implicit AND of all the words in strings");
 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}]);
 ok(@$borrowers>0, "Search In Table arrayref");
 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)]);
 ok(keys %{$$borrowers[0]} ==1, "Search In Table columns out limit");
 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname surname title)]);
-ok(@$borrowers>0, "Search In Table columns out limit");
+ok(@$borrowers>0, "Search In Table columns out limit to borrowernumber AND filter firstname surname title");
 $borrowers=SearchInTable("borrowers",["Valjean",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)]);
 ok(@$borrowers==0, "Search In Table columns filter firstname title limit Valjean not in other fields than surname ");
 $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(surname)],"start_with");
@@ -42,5 +46,7 @@ $borrowers=SearchInTable("borrowers",["Val",{firstname=>"Jean"}],undef,undef,[qw
 ok(@$borrowers==0, "Search In Table columns filter surname  Val in exact search not found ");
 $borrowers=eval{SearchInTable("borrowers",["Val",{member=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)],"exact")};
 ok(@$borrowers==0 && !($@), "Search In Table fails gracefully when no correct field passed in hash");
+
 $status=DeleteInTable("borrowers",{borrowernumber=>$borrid});
 ok($status>0 && !($@), "DeleteInTable OK");
+$status=DeleteInTable("borrowers",{borrowernumber=>$borrtmp});
-- 
2.39.5