From 4f1cb2eb2121f40edf0d889e57a4aa4fead8f165 Mon Sep 17 00:00:00 2001
From: Julian Maurice <julian.maurice@biblibre.com>
Date: Wed, 11 Feb 2015 16:18:11 +0100
Subject: [PATCH 3/4] Bug 7957: Retrieve routing notes from serial table

and remove serial.routingnotes column

Signed-off-by: Paola Rossi <paola.rossi@cineca.it>
---
 C4/Serials.pm                          |    6 +++---
 installer/data/mysql/updatedatabase.pl |   11 +++++++----
 2 files changed, 10 insertions(+), 7 deletions(-)

diff --git a/C4/Serials.pm b/C4/Serials.pm
index e46a549..d062167 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -767,7 +767,7 @@ sub GetSerials {
     my $counter = 0;
     $count = 5 unless ($count);
     my @serials;
-    my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
+    my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes
                         FROM   serial
                         WHERE  subscriptionid = ? AND status NOT IN (2, 4, 41, 42, 43, 44, 5)
                         ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
@@ -787,7 +787,7 @@ sub GetSerials {
     }
 
     # OK, now add the last 5 issues arrives/missing
-    $query = "SELECT   serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
+    $query = "SELECT   serialid,serialseq, status, planneddate, publisheddate,notes
        FROM     serial
        WHERE    subscriptionid = ?
        AND      (status in (2, 4, 41, 42, 43, 44, 5))
@@ -832,7 +832,7 @@ sub GetSerials2 {
 
     my $dbh   = C4::Context->dbh;
     my $query = qq|
-                 SELECT   serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
+                 SELECT   serialid,serialseq, status, planneddate, publisheddate,notes
                  FROM     serial 
                  WHERE    subscriptionid=$subscription AND status IN ($status)
                  ORDER BY publisheddate,serialid DESC
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 84aba5c..d48d670 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -9788,15 +9788,17 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 
     # Migrate data from old subscriptionroutinglist table
     my $query = qq{
-        SELECT DISTINCT subscriptionid
+        SELECT serial.subscriptionid, serial.routingnotes
         FROM tmp_subscriptionroutinglist
+        LEFT JOIN serial ON (tmp_subscriptionroutinglist.subscriptionid = serial.subscriptionid)
+        GROUP BY serial.subscriptionid
     };
     my $sth = $dbh->prepare($query);
     $sth->execute();
     my $results = $sth->fetchall_arrayref( {} );
     $query = qq{
-        INSERT INTO subscriptionroutinglist (subscriptionid, title)
-        VALUES (?, ?)
+        INSERT INTO subscriptionroutinglist (subscriptionid, title, notes)
+        VALUES (?, ?, ?)
     };
     $sth = $dbh->prepare($query);
     $query = qq{
@@ -9811,7 +9813,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     };
     my $insert_sth = $dbh->prepare($query);
     foreach ( @$results ) {
-        $sth->execute($_->{subscriptionid}, 'import');
+        $sth->execute($_->{subscriptionid}, 'import', $_->{routingnotes});
         my $routinglistid = $dbh->last_insert_id(undef, undef, 'subscriptionroutinglist', undef);
         $select_sth->execute($_->{subscriptionid});
         my $routings = $select_sth->fetchall_arrayref( {} );
@@ -9821,6 +9823,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     }
 
     $dbh->do("DROP TABLE tmp_subscriptionroutinglist");
+    $dbh->do("ALTER TABLE serial DROP COLUMN routingnotes");
     print "Upgrade to $DBversion done (New subscription routing list system).\n";
     SetVersion($DBversion);
 }
-- 
1.7.10.4