From 7186fbde4f6d1aef79177a5e91d5f2d2a4acc6b5 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] Bug 7957: Retrieve routing notes from serial table
and remove serial.routingnotes column
---
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.9.1