View | Details | Raw Unified | Return to bug 12772
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/update_12772_overdue_notices.perl (+49 lines)
Line 0 Link Here
1
#! /usr/bin/perl
2
3
use strict;
4
use warnings;
5
use C4::Context;
6
my $dbh=C4::Context->dbh;
7
8
print "Will do : Bug 12772 - Rejig overdue rules to permit more than 3 notices, remove letternumber from overduerules_transport_types \n";
9
10
#if ( CheckVersion($DBversion) ) {
11
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
12
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
13
    $dbh->do("CREATE TABLE overduerules (
14
        `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
15
        `branchcode` varchar(10) NOT NULL DEFAULT '',
16
        `categorycode` varchar(10) NOT NULL DEFAULT '',
17
        `itemtype` varchar(10) default '',
18
        `onhold` tinyint(1) default 0,
19
        `delay` int(4) NOT NULL default 1,
20
        `letter` varchar(20) default NULL,
21
        `debarred` varchar(1) default 0,
22
        PRIMARY KEY (`overduerules_id`)
23
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
24
    foreach my $i (1..3){
25
         $dbh->do("INSERT INTO overduerules (delay, letter, debarred, branchcode, categorycode) ".
26
                 "SELECT delay$i AS delay, letter$i AS letter, debarred$i as debarred, branchcode, categorycode FROM old_overduerules");
27
    }
28
    $dbh->do("DROP TABLE old_overduerules");
29
    $dbh->do("ALTER TABLE overduerules_transport_types
30
              ADD COLUMN overduerules_id int(11) NOT NULL");
31
    my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} });
32
    $dbh->do("DELETE FROM overduerules_transport_types");
33
    $dbh->do("ALTER TABLE overduerules_transport_types
34
              DROP COLUMN branchcode,
35
              DROP COLUMN categorycode,
36
              DROP COLUMN letternumber");
37
    my $s = $dbh->prepare(
38
            "INSERT INTO overduerules_transport_types (overduerules_id, id, message_transport_type) ".
39
            "VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?)"
40
            );
41
    foreach my $mtt(@$mtts){
42
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{message_transport_type} );
43
    }
44
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
45
#    print "Upgrade to $DBversion done (Bug 12772 - Rejig overduerules to permit more than 3 notices, remove letternumber from overduerules_transport_types)\n";
46
#   SetVersion ($DBversion);
47
#}
48
49
print "\nDone\n";
(-)a/installer/data/mysql/kohastructure.sql (-13 / +6 lines)
Lines 1318-1334 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1318
  `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1318
  `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1319
  `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)
1319
  `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)
1320
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1320
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1321
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
1321
  `itemtype` varchar(10) default '',
1322
  `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
1322
  `onhold` tinyint(1) default 0,
1323
  `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
1323
  `delay` int(4) NOT NULL default 1,
1324
  `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
1324
  `letter` varchar(20) default NULL,
1325
  `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
1325
  `debarred` varchar(1) default 0,
1326
  `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
1326
  PRIMARY KEY  (`overduerules_id`)
1327
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1328
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1329
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1330
  PRIMARY KEY  (`overduerules_id`),
1331
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
1332
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1327
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1333
1328
1334
-- Table structure for table `pending_offline_operations`
1329
-- Table structure for table `pending_offline_operations`
Lines 2525-2531 CREATE TABLE `letter` ( -- table for all notice templates in Koha Link Here
2525
DROP TABLE IF EXISTS `overduerules_transport_types`;
2520
DROP TABLE IF EXISTS `overduerules_transport_types`;
2526
CREATE TABLE overduerules_transport_types(
2521
CREATE TABLE overduerules_transport_types(
2527
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2522
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2528
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2529
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2523
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2530
    `overduerules_id` INT(11) NOT NULL,
2524
    `overduerules_id` INT(11) NOT NULL,
2531
    PRIMARY KEY (id),
2525
    PRIMARY KEY (id),
2532
- 

Return to bug 12772