From c1d9a2c7709be5eefb6e4bb9a6fd280e8ce430b5 Mon Sep 17 00:00:00 2001
From: Katrin Fischer <Katrin.Fischer.83@web.de>
Date: Mon, 25 May 2015 14:53:29 +0200
Subject: [PATCH] Bug 13972: Include fields from subscription and serial table
 in serial notification email
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Currently it's not possible to include information about which
issue has arrived in the serial notification notice the patron
can subscribe to from the OPAC.

The patch makes the fields from the subscription and serial
table available to the notice template.

In order to be able to print information about the correct
issue, the GetAlert has been modified to expext the serialid
as externalid when the module is issue.

git grep SendAlerts (only call with 'issue' is in Serial.pm)

To test:
- Add a subscription, select a patron notification template
- Search for the record in the OPAC
- Go to the subscription tab - More details
- Subscribe to the notification
- Edit the notice template you selected for the subscription
  - add fields from subscription
  - add fields from serial (serial.serialseq has the issue
    information)
- Receive an issue for the subscription
- Check that you have received the notification and that
  all information has been printed correctly
  NOTE: notice is sent directly, not through the message_queue

Followed test plan, works as expected.
Signed-off-by: Marc Véron <veron@veron.ch>

Signed-off-by: Jonathan Druart <jonathan.druart@koha-community.org>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 C4/Letters.pm   | 21 +++++++++++++++++----
 C4/Serials.pm   |  2 +-
 tools/letter.pl |  3 +++
 3 files changed, 21 insertions(+), 5 deletions(-)

diff --git a/C4/Letters.pm b/C4/Letters.pm
index db9f987e29..2e6c4cecd2 100644
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -390,18 +390,27 @@ sub SendAlerts {
     if ( $type eq 'issue' ) {
 
         # prepare the letter...
-        # search the biblionumber
+        # search the subscriptionid
         my $sth =
           $dbh->prepare(
-            "SELECT biblionumber FROM subscription WHERE subscriptionid=?");
+            "SELECT subscriptionid FROM serial WHERE serialid=?");
         $sth->execute($externalid);
-        my ($biblionumber) = $sth->fetchrow
+        my ($subscriptionid) = $sth->fetchrow
           or warn( "No subscription for '$externalid'" ),
              return;
 
+        # search the biblionumber
+        $sth =
+          $dbh->prepare(
+            "SELECT biblionumber FROM subscription WHERE subscriptionid=?");
+        $sth->execute($subscriptionid);
+        my ($biblionumber) = $sth->fetchrow
+          or warn( "No biblionumber for '$subscriptionid'" ),
+             return;
+
         my %letter;
         # find the list of borrowers to alert
-        my $alerts = getalert( '', 'issue', $externalid );
+        my $alerts = getalert( '', 'issue', $subscriptionid );
         foreach (@$alerts) {
             my $borinfo = C4::Members::GetMember('borrowernumber' => $_->{'borrowernumber'});
             my $email = $borinfo->{email} or next;
@@ -418,6 +427,8 @@ sub SendAlerts {
                     'biblio'      => $biblionumber,
                     'biblioitems' => $biblionumber,
                     'borrowers'   => $borinfo,
+                    'subscription' => $subscriptionid,
+                    'serial' => $externalid,
                 },
                 want_librarian => 1,
             ) or return;
@@ -759,6 +770,8 @@ sub _parseletter_sth {
     ($table eq 'aqorders'     ) ? "SELECT * FROM $table WHERE    ordernumber = ?"                                  :
     ($table eq 'opac_news'    ) ? "SELECT * FROM $table WHERE          idnew = ?"                                  :
     ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE verification_token = ?" :
+    ($table eq 'subscription') ? "SELECT * FROM $table WHERE subscriptionid = ?" :
+    ($table eq 'serial') ? "SELECT * FROM $table WHERE serialid = ?" :
     undef ;
     unless ($query) {
         warn "ERROR: No _parseletter_sth query for table '$table'";
diff --git a/C4/Serials.pm b/C4/Serials.pm
index 3fcc4a3290..5e52bdb63d 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -1149,7 +1149,7 @@ sub ModSerialStatus {
         # check if an alert must be sent... (= a letter is defined & status became "arrived"
         if ( $subscription->{letter} && $status == ARRIVED && $oldstatus != ARRIVED ) {
             require C4::Letters;
-            C4::Letters::SendAlerts( 'issue', $subscription->{subscriptionid}, $subscription->{letter} );
+            C4::Letters::SendAlerts( 'issue', $serialid, $subscription->{letter} );
         }
     }
 
diff --git a/tools/letter.pl b/tools/letter.pl
index bb54d7e719..8bc0c8ea6c 100755
--- a/tools/letter.pl
+++ b/tools/letter.pl
@@ -213,6 +213,9 @@ sub add_form {
             push @{$field_selection}, {value => "biblio.$_", text => ucfirst $_ };
         }
     }
+    elsif ($module eq 'serial') {
+        push @{$field_selection}, add_fields('branches', 'biblio', 'biblioitems', 'borrowers', 'subscription', 'serial');
+    }
     elsif ($module eq 'suggestions') {
         push @{$field_selection}, add_fields('suggestions', 'borrowers', 'biblio');
     }
-- 
2.39.5