From 1e6c13df39547f3f4421bf6308a0b35cb7a811d8 Mon Sep 17 00:00:00 2001
From: Paul POULAIN <paul@koha-fr.org>
Date: Thu, 16 Aug 2007 12:28:46 +0200
Subject: [PATCH] show callnumber in result list and update SQL by using LEFT
 JOIN

Signed-off-by: Chris Cormack <crc@liblime.com>
---
 C4/Serials.pm                                 | 39 ++++++++++---------
 .../prog/en/serials/serials-home.tmpl         |  3 +-
 2 files changed, 22 insertions(+), 20 deletions(-)

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 1a805851e3..82e1094d10 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -623,11 +623,11 @@ sub GetSubscriptions {
     my $sth;
     if ($biblionumber) {
         my $query = qq(
-            SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
-            FROM   subscription,biblio,biblioitems
-            WHERE   biblio.biblionumber = biblioitems.biblionumber
-                AND biblio.biblionumber = subscription.biblionumber
-                AND biblio.biblionumber=?
+            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=?
         );
         if (C4::Context->preference('IndependantBranches') && 
             C4::Context->userenv && 
@@ -642,11 +642,11 @@ sub GetSubscriptions {
     else {
         if ( $ISSN and $title ) {
             my $query = qq|
-                SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
-                FROM   subscription,biblio,biblioitems
-                WHERE  biblio.biblionumber = biblioitems.biblionumber
-                    AND biblio.biblionumber= subscription.biblionumber
-                    AND (biblioitems.issn = ? or|. join('and ',map{"biblio.title LIKE \"%$_%\""}split (" ",$title))." )";
+                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))." )";
             
             if (C4::Context->preference('IndependantBranches') && 
                 C4::Context->userenv && 
@@ -660,11 +660,11 @@ sub GetSubscriptions {
         else {
             if ($ISSN) {
                 my $query = qq(
-                    SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
-                    FROM   subscription,biblio,biblioitems
-                    WHERE  biblio.biblionumber = biblioitems.biblionumber
-                        AND biblio.biblionumber=subscription.biblionumber
-                        AND biblioitems.issn LIKE ?
+                    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 ?
                 );
                 if (C4::Context->preference('IndependantBranches') && 
                     C4::Context->userenv && 
@@ -678,10 +678,11 @@ sub GetSubscriptions {
             }
             else {
                 my $query = qq(
-                    SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
-                    FROM   subscription,biblio,biblioitems
-                    WHERE  biblio.biblionumber = biblioitems.biblionumber
-                        AND biblio.biblionumber=subscription.biblionumber
+                    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) );
                 
                 warn $query;       
diff --git a/koha-tmpl/intranet-tmpl/prog/en/serials/serials-home.tmpl b/koha-tmpl/intranet-tmpl/prog/en/serials/serials-home.tmpl
index 43409be593..cbab91d929 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/serials/serials-home.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/serials/serials-home.tmpl
@@ -119,7 +119,8 @@ Serials updated :
                 <!-- TMPL_ELSE -->
                     <td>
                 <!-- /TMPL_IF -->
-                <!-- TMPL_IF name="notes" -->(<!-- TMPL_VAR name="notes" -->)<!-- /TMPL_IF -->
+                <!-- TMPL_IF name="notes" --><p><!-- TMPL_VAR name="notes" --></p><!-- /TMPL_IF -->
+                <!-- TMPL_IF name="callnumber" --><p><!-- TMPL_VAR name="callnumber" --></p><!-- /TMPL_IF -->
                 </td>
                 <!-- TMPL_IF name="toggle" -->
                     <td class="hilighted">
-- 
2.39.5