From c6e3ff14c30177298d10d5b70d51efcd29d8276a Mon Sep 17 00:00:00 2001
From: mxbeaulieu <maxime.beaulieu@inlibro.com>
Date: Tue, 21 Jul 2015 10:38:08 -0400
Subject: [PATCH] Bug 12772 - Update Database (Based on Bug13624)

This patch applies to Bug13624.

Removes letter number from overduerules and overdurules_transport_types

	modified:   installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
	modified:   installer/data/mysql/kohastructure.sql

Remove merge conflict markers
---
 .../update_13624_overduerules_transport_type.pl    |   33 ++++++++++----------
 installer/data/mysql/kohastructure.sql             |   19 +++++------
 2 files changed, 24 insertions(+), 28 deletions(-)

diff --git a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
index be3af3c..bcadb59 100755
--- a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
+++ b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
@@ -14,19 +14,17 @@ print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table
         `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
         `branchcode` varchar(10) NOT NULL DEFAULT '',
         `categorycode` varchar(10) NOT NULL DEFAULT '',
-        `delay1` int(4) DEFAULT NULL,
-        `letter1` varchar(20) DEFAULT NULL,
-        `debarred1` varchar(1) DEFAULT '0',
-        `delay2` int(4) DEFAULT NULL,
-        `debarred2` varchar(1) DEFAULT '0',
-        `letter2` varchar(20) DEFAULT NULL,
-        `delay3` int(4) DEFAULT NULL,
-        `letter3` varchar(20) DEFAULT NULL,
-        `debarred3` int(1) DEFAULT '0',
-        PRIMARY KEY (`overduerules_id`),
-        UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
+        `itemtype` varchar(10) default '',
+        `onhold` tinyint(1) default 0,
+        `delay` int(4) NOT NULL default 1,
+        `letter` varchar(20) default NULL,
+        `debarred` varchar(1) default 0,
+        PRIMARY KEY (`overduerules_id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
-    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
+     foreach my $i (1..3){
+        $dbh->do("INSERT INTO overduerules (delay, letter, debarred, branchcode, categorycode) ".
+                 "SELECT delay$i AS delay, letter$i AS letter, debarred$i as debarred, branchcode, categorycode FROM old_overduerules");
+     }
     $dbh->do("DROP TABLE old_overduerules");
     $dbh->do("ALTER TABLE overduerules_transport_types
               ADD COLUMN overduerules_id int(11) NOT NULL");
@@ -36,11 +34,14 @@ print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table
               DROP FOREIGN KEY overduerules_fk,
               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 (overduerules_id, id, letternumber, message_transport_type) "
-                         ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
+              DROP COLUMN categorycode,
+              DROP COLUMN letternumber");
+    my $s = $dbh->prepare(
+        "INSERT INTO overduerules_transport_types (overduerules_id, id, message_transport_type) ".
+        "VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?)"
+    );
     foreach my $mtt(@$mtts){
-        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
+        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $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/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index b1b3ecc..e4356b6 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1706,17 +1706,12 @@ CREATE TABLE `overduerules` ( -- overdue notice status and triggers
   `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
   `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
   `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
-  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
-  `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
-  `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
-  `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
-  `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
-  `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
-  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
-  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
-  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
-  PRIMARY KEY  (`overduerules_id`),
-  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
+  `itemtype` varchar(10) default '',
+  `onhold` tinyint(1) default 0,
+  `delay` int(4) NOT NULL default 1,
+  `letter` varchar(20) default NULL,
+  `debarred` varchar(1) default 0,
+   PRIMARY KEY  (`overduerules_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 --
@@ -2581,7 +2576,7 @@ CREATE TABLE overduerules_transport_types(
     `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
     `overduerules_id` INT(11) NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT overduerules_transport_types_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
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- 
1.7.10.4