From 1c9fbe24507874eea9359f4914214335ed8a522b 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

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
---
 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 c7751ca..ed4ac42 100644
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -389,18 +389,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;
@@ -417,6 +426,8 @@ sub SendAlerts {
                     'biblio'      => $biblionumber,
                     'biblioitems' => $biblionumber,
                     'borrowers'   => $borinfo,
+                    'subscription' => $subscriptionid,
+                    'serial' => $externalid,
                 },
                 want_librarian => 1,
             ) or return;
@@ -758,6 +769,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 807306d..7e47d5d 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 e9f39d9..69e27e6 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');
     }
-- 
1.9.1