From 6a3579b2f35339a4d55cdcf5b252b12b875a2f53 Mon Sep 17 00:00:00 2001
From: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
Date: Mon, 16 Nov 2009 22:10:03 +0100
Subject: [PATCH] MT 2285 : enhancement for patronSearch

---
 C4/SQLHelper.pm                               | 43 +++++++++----------
 .../prog/en/css/staff-global.css              |  6 +++
 .../prog/en/includes/patron-search.inc        | 34 +++++++++++++--
 .../prog/en/modules/members/member.tmpl       | 23 +++++-----
 members/member.pl                             | 20 ++++++---
 t/db_dependent/SQLHelper.t                    | 12 ++++--
 6 files changed, 88 insertions(+), 50 deletions(-)

diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm
index 05e3cc66f0..1d600346ab 100644
--- a/C4/SQLHelper.pm
+++ b/C4/SQLHelper.pm
@@ -116,10 +116,13 @@ sub SearchInTable{
 	}
     if ($orderby){ 
 		#Order by desc by default
-        my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby; 
-        $sql.= do { local $"=', '; 
-                qq{ ORDER BY @orders} 
-               }; 
+		my @orders;
+		foreach my $order (@$orderby){
+			push @orders,map{ "$_".($$order{$_}? " DESC " : "") } keys %$order; 
+		}
+		$sql.= do { local $"=', '; 
+				qq{ ORDER BY @orders} 
+        }; 
     } 
 	if ($limit){
 		$sql.=qq{ LIMIT }.join(",",@$limit);
@@ -417,25 +420,21 @@ sub _Process_Operands{
 	unless ($searchtype){
 		return \@tmpkeys,\@values;
 	}
-	if ($searchtype eq "contain"){
-			my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
-			if ($field=~/(?<!zip)code|(?<!card)number/ ){
-				push @tmpkeys,(" $field= '' ","$field IS NULL");
-			} elsif ($$columns{$col_field}{Type}=~/varchar|text/i){
-				push @tmpkeys,(" $field LIKE ? ");
-				my @localvaluesextended=("\%$operand\%") ;
-				push @values,@localvaluesextended;
-			}
+	my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
+	if ($field=~/(?<!zip)code|(?<!card)number/ && $searchtype ne "exact"){
+		push @tmpkeys,(" $field= '' ","$field IS NULL");
 	}
-	if ($searchtype eq "start_with"){
-			my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
-			if ($field=~/(?<!zip)code|(?<!card)number/ ){
-				push @tmpkeys,(" $field= '' ","$field IS NULL");
-			} elsif ($$columns{$col_field}{Type}=~/varchar|text/i){
-				push @tmpkeys,(" $field LIKE ? ","$field LIKE ?");
-				my @localvaluesextended=("\% $operand\%","$operand\%") ;
-				push @values,@localvaluesextended;
-			}
+	if ($$columns{$col_field}{Type}=~/varchar|text/i){
+		my @localvaluesextended;
+		if ($searchtype eq "contain"){
+			push @tmpkeys,(" $field LIKE ? ");
+			push @localvaluesextended,("\%$operand\%") ;
+		}
+		if ($searchtype eq "start_with"){
+			push @tmpkeys,(" $field LIKE ? ","$field LIKE ?");
+			push @localvaluesextended, ("\% $operand\%","$operand\%") ;
+		}
+		push @values,@localvaluesextended;
 	}
 	push @localkeys,qq{ (}.join(" OR ",@tmpkeys).qq{) };
 	return (\@localkeys,\@values);
diff --git a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css b/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css
index 0be3df7a56..22636ab417 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css
+++ b/koha-tmpl/intranet-tmpl/prog/en/css/staff-global.css
@@ -519,6 +519,12 @@ div.yui-b fieldset.brief {
 	padding : .4em .7em;
 }
 
+label {  
+	float: left;  
+	width: 12em;  
+	margin-right: 1em;  
+	text-align: left;
+}
 div.yui-b fieldset.brief ol {
 	font-size : 85%;
 	margin : 0;
diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
index a05482916d..47063a262e 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
@@ -48,13 +48,32 @@ YAHOO.util.Event.onContentReady("header_search", function() {
 	<div id="patron_search" class="residentsearch">
 	<p class="tip">Enter patron card number or partial name:</p>
 	<form action="/cgi-bin/koha/members/member.pl" method="post">
-    <input id="searchmember" size="25" class="focus" name="member" type="text" />
+    <input id="searchmember" size="25" class="focus" name="member" type="text" value="<!--TMPL_VAR Name="member"-->"/>
+	<!--TMPL_IF Name="branchloop"-->
+	<span class="filteraction" id="filteraction_off"> <a href="#" onClick="$('#filters').toggle();$('.filteraction').toggle();">[-]</a></span>
+	<span class="filteraction" id="filteraction_on"> <a href="#" onClick="$('#filters').toggle();$('.filteraction').toggle();">[+]</a></span>
+	<!--/TMPL_IF -->
     order by:
     <select name="orderby" id="searchorderby">
-    <option value="surname,firstname">Surname</option>
-    <option value="cardnumber">Cardnumber</option>
+    <option value="surname,1">Surname</option>
+    <option value="cardnumber,1">Cardnumber</option>
     </select>
     <input value="Search" class="submit" type="submit" />
+	<!--TMPL_IF Name="branchloop"-->
+	<div id="filters"> <label for="branchcode"> Library :</label><select name="branchcode" id="branchcode">
+        <option value="">Any</option><!-- TMPL_LOOP name="branchloop" -->
+        <!-- TMPL_IF NAME="selected" -->
+        <option value="<!-- TMPL_VAR name="value" -->" selected="selected"><!-- TMPL_VAR name="branchname" --></option><!-- TMPL_ELSE -->
+        <option value="<!-- TMPL_VAR name="value" -->"><!-- TMPL_VAR name="branchname" --></option><!-- /TMPL_IF -->
+      <!-- /TMPL_LOOP --></select><br />
+                 <label for="categorycode"> categorycode :</label><select name="categorycode" id="categorycode">
+        <option value="">Any</option><!-- TMPL_LOOP name="categoryloop" -->
+        <!-- TMPL_IF NAME="selected" -->
+        <option value="<!-- TMPL_VAR name="categorycode" -->" selected="selected"><!-- TMPL_VAR name="description" --></option><!-- TMPL_ELSE -->
+        <option value="<!-- TMPL_VAR name="categorycode" -->"><!-- TMPL_VAR name="description" --></option><!-- /TMPL_IF -->
+      <!-- /TMPL_LOOP --></select>
+	</div>
+	<!--/TMPL_IF -->
 </form>
 	</div>
 	<!-- TMPL_IF NAME="CAN_user_circulate" -->
@@ -93,4 +112,11 @@ YAHOO.util.Event.onContentReady("header_search", function() {
 			<!-- TMPL_IF NAME="CAN_user_catalogue" --><li><a href="/cgi-bin/koha/catalogue/search.pl#catalog_search">Search the Catalog</a></li><!-- /TMPL_IF -->
 			</ul>	
 </div>
-<!-- End Patrons Resident Search Box -->
\ No newline at end of file
+<script type="text/javascript">//<![CDATA[
+$(document).ready(function() {
+	$("#filters").toggle(); 
+	$("#filteraction_off").toggle(); 
+}); 
+//]]>
+</script>
+<!-- End Patrons Resident Search Box -->
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tmpl
index 05b47479e2..a794b6029f 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/member.tmpl
@@ -1,16 +1,6 @@
 <!-- TMPL_INCLUDE NAME="doc-head-open.inc" -->
 <title>Koha &rsaquo; Patrons <!-- TMPL_IF NAME="searching" -->&rsaquo; Search Results<!-- /TMPL_IF --></title>
 <!-- TMPL_INCLUDE NAME="doc-head-close.inc" -->
-<script type="text/javascript" src="<!-- TMPL_VAR name="themelang" -->/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
-<script type="text/javascript">//<![CDATA[
-$(document).ready(function() {
-	$("#memberresultst").tablesorter({
-		sortList: [[1,0]],
-		widgets:	['zebra']
-	}); 
-}); 
-//]]>
-</script>
 </head>
 <body>
 <!-- TMPL_INCLUDE NAME="header.inc" -->
@@ -124,9 +114,16 @@ $(document).ready(function() {
 				</div>
 			</div> 
 		     
-   <div role="search" class="yui-b">
-				<!-- TMPL_INCLUDE NAME="search.tmpl" -->
-   </div> 
     </div> 
 </div>
+<script type="text/javascript" src="<!-- TMPL_VAR name="themelang" -->/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
+<script type="text/javascript">//<![CDATA[
+$(document).ready(function() {
+	$("#memberresultst").tablesorter({
+		sortList: [[1,0]],
+		widgets:	['zebra']
+	}); 
+}); 
+//]]>
+</script>
 <!-- TMPL_INCLUDE NAME="intranet-bottom.inc" -->
diff --git a/members/member.pl b/members/member.pl
index 51a3bf33f0..2937706b65 100755
--- a/members/member.pl
+++ b/members/member.pl
@@ -30,6 +30,7 @@ use C4::Members;
 use C4::Branch;
 use C4::Category;
 use File::Basename;
+use YAML;
 
 my $input = new CGI;
 my $quicksearch = $input->param('quicksearch');
@@ -81,23 +82,28 @@ if (C4::Context->preference("AddPatronLists")=~/code/){
 }  
 
 my $member=$input->param('member');
-my $orderby=$input->param('orderby');
-$orderby = "surname,firstname" unless $orderby;
+my $orderbyparams=$input->param('orderby');
+my @orderby;
+if ($orderbyparams){
+	my @orderbyelt=split(/,/,$orderbyparams);
+	push @orderby, {$orderbyelt[0]=>$orderbyelt[1]||0};
+}
+else {
+	@orderby = ({surname=>1},{firstname=>1});
+}
+warn Data::Dumper::Dumper(@orderby);
 $member =~ s/,//g;   #remove any commas from search string
 $member =~ s/\*/%/g;
 
 my ($count,$results);
 
-$$patron{firstname}.="\%" if ($$patron{firstname});
-$$patron{surname}.="\%" if ($$patron{surname});
-
 my @searchpatron;
 push @searchpatron, $member if ($member);
 push @searchpatron, $patron if (keys %$patron);
 my $from= ($startfrom-1)*$resultsperpage;
 my $to=$from+$resultsperpage;
  #($results)=Search(\@searchpatron,{surname=>1,firstname=>1},[$from,$to],undef,["firstname","surname","email","othernames"]  ) if (@searchpatron);
- ($results)=Search(\@searchpatron,{surname=>1,firstname=>1},undef,undef,["firstname","surname","email","othernames","cardnumber","userid"],"start_with"  ) if (@searchpatron);
+ ($results)=Search(\@searchpatron,\@orderby,undef,undef,["firstname","surname","email","othernames","cardnumber","userid"],"start_with"  ) if (@searchpatron);
 if ($results){
 	$count =scalar(@$results);
 }
@@ -134,7 +140,7 @@ if ($$patron{categorycode}){
 }
 my %parameters=
         (  %$patron
-		, 'orderby'			=> $orderby 
+		, 'orderby'			=> $orderbyparams 
 		, 'resultsperpage'	=> $resultsperpage 
         , 'type'=> 'intranet'); 
 my $base_url =
diff --git a/t/db_dependent/SQLHelper.t b/t/db_dependent/SQLHelper.t
index ffa8d43115..3c2fd48a9a 100755
--- a/t/db_dependent/SQLHelper.t
+++ b/t/db_dependent/SQLHelper.t
@@ -10,7 +10,7 @@ use YAML;
 use C4::Debug;
 use C4::SQLHelper qw(:all);
 
-use Test::More tests => 16;
+use Test::More tests => 18;
 
 #1
 BEGIN {
@@ -22,10 +22,8 @@ my @categories=C4::Category->all;
 my $branches=C4::Branch->GetBranches;
 my @branchcodes=keys %$branches;
 my ($borrid, $borrtmp);
-$borrtmp=InsertInTable("borrowers",{firstname=>"Jean",surname=>"cocteau",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]});
-#2
 ok($borrid=InsertInTable("borrowers",{firstname=>"Jean",surname=>"Valjean",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]}),"Insert In Table");
-#3
+$borrtmp=InsertInTable("borrowers",{firstname=>"Jean",surname=>"cocteau",city=>" ",zipcode=>" ",email=>"email",categorycode=>$categories[0]->{categorycode}, branchcode=>$branchcodes[0]});
 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");
 #4
@@ -36,6 +34,10 @@ ok(@$borrowers==1, "Search In Table by primary key on table");
 $borrowers=SearchInTable("borrowers",{firstname=>"Jean"});
 #6
 ok(@$borrowers>0, "Search In Table hashref");
+$borrowers=SearchInTable("borrowers",{firstname=>"Jean"},[{firstname=>1},{borrowernumber=>1}],undef, [qw(borrowernumber)]);
+ok(($$borrowers[0]{borrowernumber} + 0) > ($$borrowers[1]{borrowernumber} + 0), "Search In Table Order");
+$borrowers=SearchInTable("borrowers",{firstname=>"Jean"},[{surname=>0},{firstname=>1}], undef, [qw(firstname surname)]);
+ok(uc($$borrowers[0]{surname}) lt uc($$borrowers[1]{surname}), "Search In Table Order");
 $borrowers=SearchInTable("borrowers","Jean");
 #7
 ok(@$borrowers>0, "Search In Table string");
@@ -63,6 +65,8 @@ ok(@$borrowers==0, "Search In Table columns filter surname  Val in exact search
 $borrowers=eval{SearchInTable("borrowers",["Val",{member=>"Jean"}],undef,undef,[qw(borrowernumber)],[qw(firstname title)],"exact")};
 #15
 ok(@$borrowers==0 && !($@), "Search In Table fails gracefully when no correct field passed in hash");
+$borrowers=eval{SearchInTable("borrowers",["Jean"],undef,undef,undef,[qw(firstname surname borrowernumber)],"start_with")};
+ok(@$borrowers==0 && !($@), "Search In Table fails gracefully when no correct field passed in hash");
 
 $status=DeleteInTable("borrowers",{borrowernumber=>$borrid});
 #16
-- 
2.39.5