From 49bdad98fa55eea898d7d84bf02dee0e3b9294cd Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Wed, 5 Oct 2016 23:44:16 +0000 Subject: [PATCH] Bug 12772 : Update kohastructure.sql And add atomic update To rejig the overduerules table so more than 3 types of notices can be sent Also removes the letternumber column from overduerules_transport_types This patch will break the system until the others are applied --- .../atomicupdate/update_12772_overdue_notices.perl | 49 ++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 18 +++----- 2 files changed, 55 insertions(+), 12 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/update_12772_overdue_notices.perl diff --git a/installer/data/mysql/atomicupdate/update_12772_overdue_notices.perl b/installer/data/mysql/atomicupdate/update_12772_overdue_notices.perl new file mode 100755 index 0000000..5e56fc6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/update_12772_overdue_notices.perl @@ -0,0 +1,49 @@ +#! /usr/bin/perl + +use strict; +use warnings; +use C4::Context; +my $dbh=C4::Context->dbh; + +print "Will do : Bug 12772 - Rejig overdue rules to permit more than 3 notices, remove letternumber from overduerules_transport_types \n"; + +#if ( CheckVersion($DBversion) ) { + $dbh->do("SET FOREIGN_KEY_CHECKS=0"); + $dbh->do("ALTER TABLE overduerules RENAME old_overduerules"); + $dbh->do("CREATE TABLE overduerules ( + `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, + `branchcode` varchar(10) NOT NULL DEFAULT '', + `categorycode` varchar(10) NOT NULL DEFAULT '', + `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;"); + 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"); + 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 + DROP COLUMN branchcode, + 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->{message_transport_type} ); + } + $dbh->do("SET FOREIGN_KEY_CHECKS=1"); +# print "Upgrade to $DBversion done (Bug 12772 - Rejig overduerules to permit more than 3 notices, remove letternumber from overduerules_transport_types)\n"; +# SetVersion ($DBversion); +#} + +print "\nDone\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e38efc5..0ea7d8e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1318,17 +1318,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; -- Table structure for table `pending_offline_operations` @@ -2525,7 +2520,6 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha DROP TABLE IF EXISTS `overduerules_transport_types`; CREATE TABLE overduerules_transport_types( `id` INT(11) NOT NULL AUTO_INCREMENT, - `letternumber` INT(1) NOT NULL DEFAULT 1, `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email', `overduerules_id` INT(11) NOT NULL, PRIMARY KEY (id), -- 2.1.4