From ee42536a141bc7ed0ca752f677da31ee7bd6ac29 Mon Sep 17 00:00:00 2001
From: Blou <philippe.blouin@inlibro.com>
Date: Fri, 20 Mar 2015 15:17:48 -0400
Subject: [PATCH] Bugs 13624 - Test plan and fixes db scripts for overduerules
 migrations

TEST PLAN
1) Create or improve on overduerules data
  .1) Intranet -> tools -> overdue notices
  .2) Make sure to have data in first, second and third tabs
  .3) Make sure to have checked mixes of Email/Phone/Print/SMS (depending on availability)
  .4) Make sure to have different letters and delay on first/second/third tab for at least one category
  .5) Remember it all.
2) Apply patch
3) run installer/data/mysql/updatedatabase.pl
4) run t/db_dependant/Overdues.t
5) Validate Overdue Notice page
  .1) validate data entered previously is still there
  .2) Add some more, save changes, validate

This patch also
- simplifies (somewhat) updatedatabase.pl
- modifies kohastructure.sql to match updatedatabase.pl (because I couldn't 'insert after' with a KEY constraint)
- fixes database call
---
 installer/data/mysql/kohastructure.sql | 3 +--
 installer/data/mysql/updatedatabase.pl | 7 +++----
 tools/overduerules.pl                  | 2 +-
 3 files changed, 5 insertions(+), 7 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 863e8ab..401019f 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2599,9 +2599,8 @@ CREATE TABLE `message_transport_types` (
 DROP TABLE IF EXISTS `overduerules_transport_types`;
 CREATE TABLE overduerules_transport_types(
     `id` INT(11) NOT NULL AUTO_INCREMENT,
-    `overduerules_id` mediumint NOT NULL,
-    `letternumber` INT(1) NOT NULL DEFAULT 1,
     `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
+    `overduerules_id` mediumint NOT NULL,
     PRIMARY KEY (id),
     CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index a2a820c..2e28bac 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -9890,7 +9890,6 @@ if ( CheckVersion($DBversion) ) {
     $dbh->do("DROP TABLE old_overduerules");
     $dbh->do("ALTER TABLE overduerules_transport_types
               ADD COLUMN overduerules_id mediumint NOT NULL");
-    my $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?");
     my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
     $dbh->do("DELETE FROM overduerules_transport_types");
     $dbh->do("ALTER TABLE overduerules_transport_types
@@ -9898,10 +9897,10 @@ if ( CheckVersion($DBversion) ) {
               ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
               DROP COLUMN branchcode,
               DROP COLUMN categorycode");
-    my $s = $dbh->prepare("insert into overduerules_transport_types (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)");
+    my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) " 
+                         ." values((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
     foreach my $mtt(@$mtts){
-        $s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} );
-        $update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id});
+        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
     }
     $dbh->do("SET FOREIGN_KEY_CHECKS=1");
     print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
diff --git a/tools/overduerules.pl b/tools/overduerules.pl
index 807a2da..6d1babc 100755
--- a/tools/overduerules.pl
+++ b/tools/overduerules.pl
@@ -87,7 +87,7 @@ if ($op eq 'save') {
     my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?");
     my $sth_insert_mtt = $dbh->prepare("
         INSERT INTO overduerules_transport_types(
-            branchcode, categorycode, letternumber, message_transport_type
+            overduerules_id, letternumber, message_transport_type
         ) VALUES (
             (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?
         )
-- 
2.1.0