From e31b55de3119c35de64c5428412fe5f6d29f796a Mon Sep 17 00:00:00 2001
From: David Cook <dcook@prosentient.com.au>
Date: Mon, 2 Jul 2012 10:16:46 +1000
Subject: [PATCH] Bug 7839 [ENH] : Add tab in patron record to show patron's
 routing lists
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

dcook: fixed SQL error and removed blank targets

Signed-off-by: Frédéric Demians <f.demians@tamil.fr>

Thanks. Works as described. Valuable addition on patron information
page.

Ideas of enhancement:

- For some library, and some patron, the routing lists table could be
  very long, and a paged table would be a plus.
- In the table, a new Vendor column.
- The possibility to sort the table entries by Title and Vendor column.

Signed-off-by: Paul Poulain <paul.poulain@biblibre.com>
---
 C4/Serials.pm                                 |  35 +++++
 .../prog/en/includes/circ-menu.inc            |   1 +
 .../prog/en/modules/members/routing-lists.tt  |  80 +++++++++++
 members/routing-lists.pl                      | 133 ++++++++++++++++++
 4 files changed, 249 insertions(+)
 create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt
 create mode 100755 members/routing-lists.pl

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 58b6078c28..90335d03fc 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -53,6 +53,7 @@ BEGIN {
       &check_routing &updateClaim &removeMissingIssue
       &CountIssues
       HasItems
+      &GetSubscriptionsFromBorrower
 
     );
 }
@@ -2175,6 +2176,40 @@ sub in_array {    # used in next sub down
     return 0;
 }
 
+=head2 GetSubscriptionsFromBorrower
+
+($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
+
+this gets the info from subscriptionroutinglist for each $subscriptionid
+
+return :
+a count of the serial subscription routing lists to which a patron belongs,
+with the titles of those serial subscriptions as an array. Each element of the array
+contains a hash_ref with subscriptionID and title of subscription.
+
+=cut
+
+sub GetSubscriptionsFromBorrower {
+    my ($borrowernumber) = @_;
+    my $dbh              = C4::Context->dbh;
+    my $sth              = $dbh->prepare(
+        "SELECT subscription.subscriptionid, biblio.title
+            FROM subscription
+            JOIN biblio ON biblio.biblionumber = subscription.biblionumber
+            JOIN subscriptionroutinglist USING (subscriptionid)
+            WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
+                               "
+    );
+    $sth->execute($borrowernumber);
+    my @routinglist;
+    my $count = 0;
+    while ( my $line = $sth->fetchrow_hashref ) {
+        $count++;
+        push( @routinglist, $line );
+    }
+    return ( $count, @routinglist );
+}
+
 =head2 GetNextDate
 
 $resultdate = GetNextDate($planneddate,$subscription)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
index 091f7febc5..9f1c887127 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc
@@ -69,6 +69,7 @@
     [% IF ( CAN_user_updatecharges ) %]
         [% IF ( finesview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/boraccount.pl?borrowernumber=[% borrowernumber %]">Fines</a></li>
     [% END %]
+    [% IF ( routinglistview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/routing-lists.pl?borrowernumber=[% borrowernumber %]">Routing Lists</a></li>
     [% IF ( intranetreadinghistory ) %]
         [% IF ( readingrecordview ) %]<li class="active">[% ELSE %]<li>[% END %]<a href="/cgi-bin/koha/members/readingrec.pl?borrowernumber=[% borrowernumber %]">Circulation history</a></li>
     [% END %]
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt
new file mode 100644
index 0000000000..d505a3389e
--- /dev/null
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt
@@ -0,0 +1,80 @@
+[% INCLUDE 'doc-head-open.inc' %]
+<title>Subscription Routing Lists for [% INCLUDE 'patron-title.inc' %]</title>
+[% INCLUDE 'doc-head-close.inc' %]
+</head>
+<body>
+[% INCLUDE 'header.inc' %]
+[% INCLUDE 'patron-search.inc' %]
+
+<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/members/members-home.pl">Patrons</a>  &rsaquo; Subscription Routing Lists for [% INCLUDE 'patron-title.inc' %]</div>
+
+<div id="doc3" class="yui-t2">
+    <div id="bd">
+    <div id="yui-main">
+    <div class="yui-b">
+[% INCLUDE 'circ-toolbar.inc' %]
+
+<!-- Search Bar -->
+<p class="tip">Search Subscriptions:</p>
+<form action="/cgi-bin/koha/serials/serials-home.pl" method="get">[% IF ( routing ) %]<input type="hidden" name="routing" value="[% routing %]" />[% END %]<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="[% ISSN_filter %]" /> <label for="title_filter">Title:</label> <input type="text" size="20" maxlength="40" name="title_filter" id="title_filter" value="[% title_filter %]" /><input type="submit" value="Search" class="submit" />
+</form>
+<!-- Search Bar End -->
+
+<h1>
+[% IF ( countSubscrip ) %]
+[% countSubscrip %] Subscription Routing List(s)
+[% ELSE %]
+0 Subscription Routing Lists
+[% END %]
+</h1>
+
+<div id="subscriptions">
+[% IF ( subscripLoop ) %]
+<table id="subscriptiont">
+              <thead>
+                <tr>
+                  <th>Subscription Title</th>
+                  <th>Routing List</th>
+                </tr>
+              </thead>
+              <tbody>
+[% FOREACH subscripLoop IN subscripLoop %]
+<tr>
+    <td>
+    <a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% subscripLoop.subscriptionid %]"><strong>
+    [% subscripLoop.title %]
+    </strong>
+                    </a>
+                  </td>
+                  <td>
+                    <a href="/cgi-bin/koha/serials/routing.pl?subscriptionid=[% subscripLoop.subscriptionid %]"><strong>
+                    Edit Routing List
+                      </strong>
+                    </a>
+                    <input type="hidden" name="biblionumber" value="[% biblionumber %]" />
+                    <input type="hidden" name="borrowernumber" value="[% borrowernumber %]" />
+                  </td>
+                </tr>
+                [% END %]
+                </tbody>
+            </table>
+            </form>
+          [% ELSE %]
+          <p>Patron does not belong to any subscription routing lists.</p>
+          <input type="hidden" name="biblionumber" value="[% biblionumber %]" />
+                    <input type="hidden" name="borrowernumber" value="[% borrowernumber %]" />
+                    [% END %]
+
+</div>
+
+
+
+
+</div>
+</div>
+
+<div class="yui-b">
+[% INCLUDE 'circ-menu.inc' %]
+</div>
+</div>
+[% INCLUDE 'intranet-bottom.inc' %]
diff --git a/members/routing-lists.pl b/members/routing-lists.pl
new file mode 100755
index 0000000000..25b0f85d4f
--- /dev/null
+++ b/members/routing-lists.pl
@@ -0,0 +1,133 @@
+#!/usr/bin/perl
+
+# Copyright 2012 Prosentient Systems
+#
+# 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 2 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, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+
+use strict;
+#use warnings; FIXME - Bug 2505
+use CGI;
+use C4::Output;
+use C4::Auth qw/:DEFAULT get_session/;
+use C4::Branch; # GetBranches
+use C4::Members;
+use C4::Context;
+use C4::Serials;
+use CGI::Session;
+
+my $query = new CGI;
+
+my $sessionID = $query->cookie("CGISESSID") ;
+my $session = get_session($sessionID);
+
+# branch are now defined by the userenv
+# but first we have to check if someone has tried to change them
+
+my $branch = $query->param('branch');
+if ($branch){
+    # update our session so the userenv is updated
+    $session->param('branch', $branch);
+    $session->param('branchname', GetBranchName($branch));
+}
+
+my ( $template, $loggedinuser, $cookie ) = get_template_and_user (
+    {
+        template_name   => 'members/routing-lists.tt',
+        query           => $query,
+        type            => "intranet",
+        authnotrequired => 0,
+        flagsrequired   => { circulate => 'circulate_remaining_permissions' },
+    }
+);
+
+my $branches = GetBranches();
+
+my $findborrower = $query->param('findborrower');
+$findborrower =~ s|,| |g;
+
+my $borrowernumber = $query->param('borrowernumber');
+
+$branch  = C4::Context->userenv->{'branch'};
+
+# get the borrower information.....
+my $borrower;
+if ($borrowernumber) {
+    $borrower = GetMemberDetails( $borrowernumber, 0 );
+}
+
+
+##################################################################################
+# BUILD HTML
+# I'm trying to show the title of subscriptions where the borrowernumber is attached via a routing list
+
+if ($borrowernumber) {
+# new op dev
+  my $count;
+  my @borrowerSubscriptions;
+  ($count, @borrowerSubscriptions) = GetSubscriptionsFromBorrower($borrowernumber );
+  my @subscripLoop;
+
+    foreach my $num_res (@borrowerSubscriptions) {
+        my %getSubscrip;
+        $getSubscrip{subscriptionid}	= $num_res->{'subscriptionid'};
+        $getSubscrip{title}			= $num_res->{'title'};
+        $getSubscrip{borrowernumber}		= $num_res->{'borrowernumber'};
+        push( @subscripLoop, \%getSubscrip );
+    }
+
+    $template->param(
+        countSubscrip => scalar @subscripLoop,
+        subscripLoop  => \@subscripLoop,
+        routinglistview => 1
+    );
+
+    $template->param( adultborrower => 1 ) if ( $borrower->{'category_type'} eq 'A' );
+}
+
+##################################################################################
+
+
+# Computes full borrower address
+my (undef, $roadttype_hashref) = &GetRoadTypes();
+my $address = $borrower->{'streetnumber'}.' '.$roadttype_hashref->{$borrower->{'streettype'}}.' '.$borrower->{'address'};
+
+$template->param(
+
+    findborrower      => $findborrower,
+    borrower          => $borrower,
+    borrowernumber    => $borrowernumber,
+    branch            => $branch,
+    branchname        => GetBranchName($borrower->{'branchcode'}),
+    firstname         => $borrower->{'firstname'},
+    surname           => $borrower->{'surname'},
+    categorycode      => $borrower->{'categorycode'},
+    categoryname      => $borrower->{description},
+    address           => $address,
+    address2          => $borrower->{'address2'},
+    email             => $borrower->{'email'},
+    emailpro          => $borrower->{'emailpro'},
+    borrowernotes     => $borrower->{'borrowernotes'},
+    city              => $borrower->{'city'},
+    zipcode           => $borrower->{'zipcode'},
+    country           => $borrower->{'country'},
+    phone             => $borrower->{'phone'} || $borrower->{'mobile'},
+    cardnumber        => $borrower->{'cardnumber'},
+);
+
+my ($picture, $dberror) = GetPatronImage($borrower->{'cardnumber'});
+$template->param( picture => 1 ) if $picture;
+
+output_html_with_http_headers $query, $cookie, $template->output;
-- 
2.39.5