From f53b82430bc08942ea41cfb41513039a897131c2 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Fri, 9 Mar 2012 12:09:24 +0100
Subject: [PATCH 1/2] Bug 5342: Adds a claims_count field in the serial table

This patch adds 1 column in the serial table :
    - claims_count : number of claims for an orders

You can add a <order> tag around your item fields, like this :

    -- Begin example
    <<LibrarianFirstname>>
    <<LibrarianSurname>>
    The following issues are in late:
    <order><<biblio.title>>, <<biblio.author>> (<<biblio.serial>>)</order>
    -- End example

For more explication, see the discussion on the Bug 5347
---
 C4/Serials.pm                                      |   26 ++++--
 installer/data/mysql/kohastructure.sql             |    1 +
 installer/data/mysql/updatedatabase.pl             |   10 ++
 .../prog/en/modules/serials/claims.tt              |   96 +++++++++++---------
 serials/claims.pl                                  |   46 ++++++----
 5 files changed, 107 insertions(+), 72 deletions(-)

diff --git a/C4/Serials.pm b/C4/Serials.pm
index af5c833..412abdc 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -88,10 +88,12 @@ sub GetSuppliersWithLateIssues {
     my $dbh   = C4::Context->dbh;
     my $query = qq|
         SELECT DISTINCT id, name
-    FROM            subscription
-    LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
-    LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
-    WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4) ORDER BY name|;
+        FROM            subscription
+        LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
+        LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
+        WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4 OR serial.STATUS = 7)
+        ORDER BY name
+    |;
     return $dbh->selectall_arrayref($query, { Slice => {} });
 }
 
@@ -284,8 +286,12 @@ sub UpdateClaimdateIssues {
     my $dbh = C4::Context->dbh;
     $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
     my $query = "
-        UPDATE serial SET claimdate = ?, status = 7
-        WHERE  serialid in (" . join( ",", map { '?' } @$serialids ) . ")";
+        UPDATE serial
+        SET claimdate = ?,
+            status = 7,
+            claims_count = claims_count + 1
+        WHERE  serialid in (" . join( ",", map { '?' } @$serialids ) . ")
+    ";
     my $rq = $dbh->prepare($query);
     $rq->execute($date, @$serialids);
     return $rq->rows;
@@ -1756,7 +1762,7 @@ sub GetLateOrMissingIssues {
             "SELECT
                 serialid,      aqbooksellerid,        name,
                 biblio.title,  planneddate,           serialseq,
-                serial.status, serial.subscriptionid, claimdate,
+                serial.status, serial.subscriptionid, claimdate, claims_count,
                 subscription.branchcode
             FROM      serial 
                 LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid 
@@ -1773,7 +1779,7 @@ sub GetLateOrMissingIssues {
             "SELECT 
             serialid,      aqbooksellerid,         name,
             biblio.title,  planneddate,           serialseq,
-                serial.status, serial.subscriptionid, claimdate,
+                serial.status, serial.subscriptionid, claimdate, claims_count,
                 subscription.branchcode
             FROM serial 
                 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid 
@@ -1853,7 +1859,9 @@ sub updateClaim {
     my ($serialid) = @_;
     my $dbh        = C4::Context->dbh;
     my $sth        = $dbh->prepare(
-        "UPDATE serial SET claimdate = now()
+        "UPDATE serial
+         SET claimdate = now(),
+             claims_count = claims_count + 1
                 WHERE serialid = ?
         "
     );
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 3137f38..fabf96a 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1768,6 +1768,7 @@ CREATE TABLE `serial` (
   `publisheddate` date default NULL,
   `itemnumber` text default NULL,
   `claimdate` date default NULL,
+  claims_count int(11) default 0,
   `routingnotes` text,
   PRIMARY KEY  (`serialid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index dbbdee3..a89573d 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -5308,6 +5308,16 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+
+
+
+$DBversion = "3.07.00.XXX";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+    $dbh->do("ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0");
+    print "Upgrade to $DBversion done (Add claims_count field in serial table)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt
index 399c933..520f031 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt
@@ -164,6 +164,17 @@
 
     <h1>Claims</h1>
 
+    [% IF error_claim %]
+        [% IF ( error_claim == "no_email" ) %]
+            <div class="error">This vendor has no email</div>
+        [% ELSE %]
+            <div class="error">[% error_claim %]</div>
+        [% END %]
+    [% END %]
+    [% IF info_claim %]
+        <div class="dialog message">Email has been sent.</div>
+    [% END %]
+
 [% IF ( letter ) %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]
 	
 	     [% IF ( SHOWCONFIRMATION ) %]
@@ -214,6 +225,8 @@
 	<li>
 	    <label for="titlefilter">Title : </label>
 	    <input id="titlefilter" type="text" />
+    </li>
+    <li>
 	    <label for="branchfilter">Library: </label>
 	    <select id="branchfilter" onchange="filterByBranch();">
             [% FOREACH branchloo IN branchloop %]
@@ -263,41 +276,36 @@
     </form>
 
     <fieldset>
-	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
-        <input type="hidden" name="order" value="[% order %]" />
-         <table id="claimst">
-                <thead><tr>
-                    [% IF ( letter ) %]
-                    <th><input type="checkbox" id="CheckAll"></th>
-                    [% END %]
-                    <th>Vendor</th>
-                    <th>Library</th>
-                    <th>Title</th>
-                    <th>Issue number</th>
-                    <th>Status</th>
-                    <th>Since</th>
-                    <th>Claim date</th>
-                <th>Begin claim</th>
-                </tr></thead>
+        <form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
+            <table id="claimst">
+                <thead>
+                    <tr>
+                        [% IF ( letter ) %]
+                            <th><input type="checkbox" id="CheckAll"></th>
+                        [% END %]
+                        <th>Vendor</th>
+                        <th>Library</th>
+                        <th>Title</th>
+                        <th>Issue number</th>
+                        <th>Status</th>
+                        <th>Since</th>
+                        <th>Claims count</th>
+                        <th>Claim date</th>
+                    </tr>
+                </thead>
                 <tbody>[% FOREACH missingissue IN missingissues %]
                     <tr>
                         [% IF ( letter ) %]
                             <td>
-                    <input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
+                                <input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
                             </td>
                         [% END %]
-                        <td>
-                        [% missingissue.name %]
-                        </td>
-                        <td>
-                        [% missingissue.branchcode %]
-                        </td>
+                        <td>[% missingissue.name %]</td>
+                        <td>[% missingissue.branchcode %]</td>
                         <td>
                         <a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title |html %]</a>
                         </td>
-                        <td>
-                        [% missingissue.serialseq %]
-                        </td>
+                        <td>[% missingissue.serialseq %]</td>
                         <td>
                             [% IF ( missingissue.status1 ) %]Expected[% END %]
                             [% IF ( missingissue.status2 ) %]Arrived[% END %]
@@ -305,28 +313,26 @@
                             [% IF ( missingissue.status4 ) %]Missing[% END %]
                             [% IF ( missingissue.status7 ) %]Claimed[% END %]
                         </td>
-                        <td class="planneddate">
-                        [% missingissue.planneddate %]
-                        </td>
-                        <td>
-                        [% missingissue.claimdate %]
-                        </td>
-                        <td>
-                            <a href="/cgi-bin/koha/serials/lateissues-excel.pl?supplierid=[% missingissue.supplieri %]&amp;serialid=[% missingissue.serialid %]&amp;op=claims">Export item data</a>
-                        </td>
+                        <td class="planneddate">[% missingissue.planneddate %]</td>
+                        <td>[% missingissue.claims_count %]</td>
+                        <td>[% missingissue.claimdate %]</td>
                     </tr>
                 [% END %]</tbody>
             </table>
-	     <p><span class="exportSelected"></span></p>
-
-[% IF ( letter ) %]
-        <fieldset class="action"> <label for="letter_code">Select notice:</label>
-            <select name="letter_code" id="letter_code">
-                [% FOREACH letter IN letters %]
-                    <option value="[% letter.code %]">[% letter.name %]</option>
-                [% END %]
-			</select>
-	    <input type="hidden" name="op" value="send_alert" /><input type="submit" name="submit" class="button" value="Send notification" /></fieldset>
+            <p><span class="exportSelected"></span></p>
+
+            [% IF ( letter ) %]
+                <fieldset class="action">
+                    <label for="letter_code">Select notice:</label>
+                    <select name="letter_code" id="letter_code">
+                        [% FOREACH letter IN letters %]
+                            <option value="[% letter.code %]">[% letter.name %]</option>
+                        [% END %]
+                    </select>
+                    <input type="hidden" name="op" value="send_alert" />
+                    <input type="hidden" name="supplierid" value="[% supplierid %]" />
+                    <input type="submit" name="submit" class="button" value="Send notification" />
+                </fieldset>
             [% END %]
         </form>
     </fieldset>
diff --git a/serials/claims.pl b/serials/claims.pl
index e51148c..70fc5c2 100755
--- a/serials/claims.pl
+++ b/serials/claims.pl
@@ -36,7 +36,6 @@ my $op = $input->param('op');
 my $claimletter = $input->param('claimletter');
 my $supplierid = $input->param('supplierid');
 my $suppliername = $input->param('suppliername');
-my $order = $input->param('order');
 
 # open template first (security & userenv set here)
 my ($template, $loggedinuser, $cookie)
@@ -51,12 +50,38 @@ my ($template, $loggedinuser, $cookie)
 # supplierlist is returned in name order
 my $supplierlist = GetSuppliersWithLateIssues();
 for my $s (@{$supplierlist} ) {
-    $s->{count} = scalar  GetLateOrMissingIssues($s->{id}, q{}, $order);
+    $s->{count} = scalar  GetLateOrMissingIssues($s->{id}, q{});
     if ($supplierid && $s->{id} == $supplierid) {
         $s->{selected} = 1;
     }
 }
 
+my $branchloop = GetBranchesLoop();
+unshift @$branchloop, {value=> 'all',name=>''};
+
+my $preview=0;
+if($op && $op eq 'preview'){
+    $preview = 1;
+} else {
+    my @serialnums=$input->param('serialid');
+    if (@serialnums) { # i.e. they have been flagged to generate claims
+        my $err;
+        eval {
+            $err = SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
+            if ( not ref $err or not exists $err->{error} ) {
+               UpdateClaimdateIssues(\@serialnums);
+            }
+        };
+        if ( $@ ) {
+            $template->param(error_claim => $@);
+        } elsif ( ref $err and exists $err->{error} and $err->{error} eq "no_email" ) {
+            $template->{VARS}->{'error_claim'} = "no_email";
+        } else {
+            $template->{VARS}->{'info_claim'} = 1;
+        }
+    }
+}
+
 my $letters = GetLetters('claimissues');
 my @letters;
 foreach (keys %{$letters}){
@@ -67,27 +92,12 @@ my $letter=((scalar(@letters)>1) || ($letters[0]->{name}||$letters[0]->{code}));
 my  @missingissues;
 my @supplierinfo;
 if ($supplierid) {
-    @missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order);
+    @missingissues = GetLateOrMissingIssues($supplierid);
     @supplierinfo=GetBookSeller($supplierid);
 }
 
-my $branchloop = GetBranchesLoop();
-unshift @$branchloop, {value=> 'all',name=>''};
-
-my $preview=0;
-if($op && $op eq 'preview'){
-    $preview = 1;
-} else {
-    my @serialnums=$input->param('serialid');
-    if (@serialnums) { # i.e. they have been flagged to generate claims
-        SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
-        my $cntupdate=UpdateClaimdateIssues(\@serialnums);
-        ### $cntupdate SHOULD be equal to scalar(@$serialnums)
-    }
-}
 $template->param('letters'=>\@letters,'letter'=>$letter);
 $template->param(
-        order =>$order,
         suploop => $supplierlist,
         phone => $supplierinfo[0]->{phone},
         booksellerfax => $supplierinfo[0]->{booksellerfax},
-- 
1.7.7.3