Bugzilla – Attachment 56063 Details for
Bug 12772
Add item type and on-hold status to overdue notice/status triggers tool
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 12772 : Update kohastructure.sql
Bug-12772--Update-kohastructuresql.patch (text/plain), 5.88 KB, created by
Chris Cormack
on 2016-10-06 02:49:20 UTC
(
hide
)
Description:
Bug 12772 : Update kohastructure.sql
Filename:
MIME Type:
Creator:
Chris Cormack
Created:
2016-10-06 02:49:20 UTC
Size:
5.88 KB
patch
obsolete
>From 49bdad98fa55eea898d7d84bf02dee0e3b9294cd Mon Sep 17 00:00:00 2001 >From: Chris Cormack <chrisc@catalyst.net.nz> >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 12772
:
41477
|
41478
|
41479
|
41480
|
41855
|
42126
|
45184
|
45185
|
45186
|
45187
|
45188
|
45189
|
45190
|
45191
|
45192
|
45193
|
45194
|
45195
|
45196
|
45197
|
45198
|
45199
|
45200
|
45201
|
45202
|
45203
|
45204
|
45544
|
45545
|
45546
|
45547
|
45548
|
45549
|
45550
|
45551
|
45552
| 56063 |
56077