From 4f1cb2eb2121f40edf0d889e57a4aa4fead8f165 Mon Sep 17 00:00:00 2001 From: Julian Maurice 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 --- 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