From e53b5e10edd6be90baf56bcea796bb69593ecac3 Mon Sep 17 00:00:00 2001
From: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
Date: Tue, 15 Sep 2009 11:03:45 +0200
Subject: [PATCH] Adding some fields in GetSubscriptions

GetSubscriptions now searches string in
notes
internalnotes
callnumber
title
---
 C4/Serials.pm                                 | 90 ++++++++-----------
 .../prog/en/includes/serials-search.inc       |  2 +-
 t/db_dependent/Serials.t                      | 29 ++++++
 3 files changed, 68 insertions(+), 53 deletions(-)
 create mode 100644 t/db_dependent/Serials.t

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 5077d83be6..f7e90e0c32 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -619,66 +619,52 @@ a table of hashref. Each hash containt the subscription.
 =cut
 
 sub GetSubscriptions {
-    my ( $title, $ISSN, $biblionumber ) = @_;
+    my ( $string, $issn,$biblionumber) = @_;
     #return unless $title or $ISSN or $biblionumber;
     my $dbh = C4::Context->dbh;
     my $sth;
-    if ($biblionumber) {
-        my $query = qq(
+    my $sql = qq(
             SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
             FROM   subscription
             LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
             LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
-            WHERE biblio.biblionumber=?
-        );
-        $query.=" ORDER BY title";
-        $debug and warn "GetSubscriptions query: $query";
-        $sth = $dbh->prepare($query);
-        $sth->execute($biblionumber);
-    }
-    else {
-        if ( $ISSN and $title ) {
-            my $query = qq|
-                SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber        
-                FROM   subscription
-                LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
-                LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
-                WHERE (biblioitems.issn = ? or|. join('and ',map{"biblio.title LIKE \"%$_%\""}split (" ",$title))." )";
-            $query.=" ORDER BY title";
-        	$debug and warn "GetSubscriptions query: $query";
-            $sth = $dbh->prepare($query);
-            $sth->execute( $ISSN );
-        }
-        else {
-            if ($ISSN) {
-                my $query = qq(
-                    SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
-                    FROM   subscription
-                    LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
-                    LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
-                    WHERE biblioitems.issn LIKE ?
-                );
-                $query.=" ORDER BY title";
-        		$debug and warn "GetSubscriptions query: $query";
-                $sth = $dbh->prepare($query);
-                $sth->execute( "%" . $ISSN . "%" );
-            }
-            else {
-                my $query = qq(
-                    SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
-                    FROM   subscription
-                    LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
-                    LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
-                    WHERE 1
-                    ).($title?" and ":""). join('and ',map{"biblio.title LIKE \"%$_%\""} split (" ",$title) );
-                
-                $query.=" ORDER BY title";
-        		$debug and warn "GetSubscriptions query: $query";
-                $sth = $dbh->prepare($query);
-                $sth->execute;
-            }
-        }
+    );
+	my @bind_params;
+	my $sqlwhere;
+    if ($biblionumber) {
+        $sqlwhere="   WHERE biblio.biblionumber=?";
+		push @bind_params,$biblionumber;
     }
+    if ($string){
+		my @sqlstrings;	
+		my @strings_to_search;
+		@strings_to_search=map {"%$_%"} split (/ /,$string);
+		foreach my $index qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes){
+				push @bind_params,@strings_to_search; 
+				my $tmpstring= "AND $index LIKE ? "x scalar(@strings_to_search);
+				$debug && warn "$tmpstring";
+				$tmpstring=~s/^AND //;
+				push @sqlstrings,$tmpstring;
+		}
+		$sqlwhere.= ($sqlwhere?" AND ":" WHERE ")."(".join(") OR (",@sqlstrings).")";
+	}
+    if ($issn){
+		my @sqlstrings;	
+		my @strings_to_search;
+		@strings_to_search=map {"%$_%"} split (/ /,$issn);
+		foreach my $index qw(biblioitems.issn){
+				push @bind_params,@strings_to_search; 
+				my $tmpstring= "OR $index LIKE ? "x scalar(@strings_to_search);
+				$debug && warn "$tmpstring";
+				$tmpstring=~s/^OR //;
+				push @sqlstrings,$tmpstring;
+		}
+		$sqlwhere.= ($sqlwhere?" AND ":" WHERE ")."(".join(") OR (",@sqlstrings).")";
+	}    
+	$sql.="$sqlwhere ORDER BY title";
+    $debug and warn "GetSubscriptions query: $sql params : ", join (" ",@bind_params);
+    $sth = $dbh->prepare($sql);
+    $sth->execute(@bind_params);
     my @results;
     my $previoustitle = "";
     my $odd           = 1;
diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc
index c4ed7bf423..e0e3e150a8 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc
@@ -3,7 +3,7 @@
 <div id="header_search">
 	<div id="subscription_search" class="residentsearch">
 	<p class="tip">Search Subscriptions:</p>
- <form action="/cgi-bin/koha/serials/serials-home.pl" method="get"><!-- TMPL_IF NAME="routing" --><input type="hidden" name="routing" value="<!-- TMPL_VAR NAME="routing" -->" /><!-- /TMPL_IF --><input type="hidden" name="searched" value="1" /> <label for="ISSN_filter">ISSN:</label> <input type="text" size="10" maxlength="11" name="ISSN_filter" id="ISSN_filter" value="<!-- TMPL_VAR name="ISSN_filter" -->" /> <label for="title_filter">Title:</label> <input type="text" size="20" maxlength="40" name="title_filter" id="title_filter" value="<!-- TMPL_VAR name="title_filter" -->" /><input type="submit" value="Search" class="submit" />
+ <form action="/cgi-bin/koha/serials/serials-home.pl" method="get"><!-- TMPL_IF NAME="routing" --><input type="hidden" name="routing" value="<!-- TMPL_VAR NAME="routing" -->" /><!-- /TMPL_IF --><input type="hidden" name="searched" value="1" /> <label for="ISSN_filter">Standard Number:</label> <input type="text" size="10" maxlength="11" name="ISSN_filter" id="ISSN_filter" value="<!-- TMPL_VAR name="ISSN_filter" -->" /> <label for="title_filter">String:</label> <input type="text" size="20" maxlength="40" name="title_filter" id="title_filter" value="<!-- TMPL_VAR name="title_filter" -->" /><input type="submit" value="Search" class="submit" />
 </form>
 	</div>
     <!-- TMPL_INCLUDE NAME="patron-search-box.inc" -->
diff --git a/t/db_dependent/Serials.t b/t/db_dependent/Serials.t
new file mode 100644
index 0000000000..e1d3815e5d
--- /dev/null
+++ b/t/db_dependent/Serials.t
@@ -0,0 +1,29 @@
+#!/usr/bin/perl
+#
+# This Koha test module is a stub!
+# Add more tests here!!!
+
+use strict;
+use warnings;
+use YAML;
+
+use C4::Serials;
+use C4::Debug;
+use Test::More tests => 4;
+
+BEGIN {
+    use_ok('C4::Serials');
+}
+
+my $subscriptionid = 1;
+my $subscriptioninformation = GetSubscription( $subscriptionid );
+$debug && warn Dump($subscriptioninformation);
+my @subscriptions = GetSubscriptions( $$subscriptioninformation{bibliotitle} );
+isa_ok( \@subscriptions, 'ARRAY' );
+$debug && warn scalar(@subscriptions);
+@subscriptions = GetSubscriptions( undef, $$subscriptioninformation{issn} );
+isa_ok( \@subscriptions, 'ARRAY' );
+$debug && warn scalar(@subscriptions);
+@subscriptions = GetSubscriptions( undef,undef ,$$subscriptioninformation{bibnum} );
+isa_ok( \@subscriptions, 'ARRAY' );
+$debug && warn scalar(@subscriptions);
-- 
2.39.5