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

(-)a/C4/Overdues.pm (-2 / +5 lines)
Lines 932-939 sub GetOverdueMessageTransportTypes { Link Here
932
    return unless $categorycode and $letternumber;
932
    return unless $categorycode and $letternumber;
933
    my $dbh = C4::Context->dbh;
933
    my $dbh = C4::Context->dbh;
934
    my $sth = $dbh->prepare("
934
    my $sth = $dbh->prepare("
935
        SELECT message_transport_type FROM overduerules_transport_types
935
        SELECT message_transport_type
936
        WHERE branchcode = ? AND categorycode = ? AND letternumber = ?
936
        FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id)
937
        WHERE branchcode = ?
938
          AND categorycode = ?
939
          AND letternumber = ?
937
    ");
940
    ");
938
    $sth->execute( $branchcode, $categorycode, $letternumber );
941
    $sth->execute( $branchcode, $categorycode, $letternumber );
939
    my @mtts;
942
    my @mtts;
(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (+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 : Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table 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` mediumint NOT NULL AUTO_INCREMENT,
15
        `branchcode` varchar(10) NOT NULL DEFAULT '',
16
        `categorycode` varchar(10) NOT NULL DEFAULT '',
17
        `delay1` int(4) DEFAULT NULL,
18
        `letter1` varchar(20) DEFAULT NULL,
19
        `debarred1` varchar(1) DEFAULT '0',
20
        `delay2` int(4) DEFAULT NULL,
21
        `debarred2` varchar(1) DEFAULT '0',
22
        `letter2` varchar(20) DEFAULT NULL,
23
        `delay3` int(4) DEFAULT NULL,
24
        `letter3` varchar(20) DEFAULT NULL,
25
        `debarred3` int(1) DEFAULT '0',
26
        PRIMARY KEY (`overduerules_id`)
27
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
28
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
29
    $dbh->do("DROP TABLE old_overduerules");
30
    $dbh->do("ALTER TABLE overduerules_transport_types
31
              ADD COLUMN overduerules_id mediumint NOT NULL");
32
    my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
33
    $dbh->do("DELETE FROM overduerules_transport_types");
34
    $dbh->do("ALTER TABLE overduerules_transport_types
35
              DROP FOREIGN KEY overduerules_fk,
36
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
37
              DROP COLUMN branchcode,
38
              DROP COLUMN categorycode");
39
    my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
40
                         ." values((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
41
    foreach my $mtt(@$mtts){
42
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
43
    }
44
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
45
#    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
46
#   SetVersion ($DBversion);
47
#}
48
49
print "\nDone\n";
(-)a/installer/data/mysql/kohastructure.sql (-5 / +4 lines)
Lines 1696-1701 CREATE TABLE `opac_news` ( -- data from the news tool Link Here
1696
1696
1697
DROP TABLE IF EXISTS `overduerules`;
1697
DROP TABLE IF EXISTS `overduerules`;
1698
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1698
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1699
  `overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1699
  `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)
1700
  `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)
1700
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1701
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1701
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
1702
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
Lines 1707-1713 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1707
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1708
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1708
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1709
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1709
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1710
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1710
  PRIMARY KEY  (`branchcode`,`categorycode`)
1711
  PRIMARY KEY  (`overduerules_id`)
1711
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1712
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1712
1713
1713
--
1714
--
Lines 2568-2579 CREATE TABLE `message_transport_types` ( Link Here
2568
DROP TABLE IF EXISTS `overduerules_transport_types`;
2569
DROP TABLE IF EXISTS `overduerules_transport_types`;
2569
CREATE TABLE overduerules_transport_types(
2570
CREATE TABLE overduerules_transport_types(
2570
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2571
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2571
    `branchcode` varchar(10) NOT NULL DEFAULT '',
2572
    `categorycode` VARCHAR(10) NOT NULL DEFAULT '',
2573
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2574
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2572
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2573
    `overduerules_id` mediumint NOT NULL,
2575
    PRIMARY KEY (id),
2574
    PRIMARY KEY (id),
2576
    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
2575
    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2577
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2576
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2578
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2577
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2579
2578
(-)a/t/db_dependent/Overdues.t (-18 / +17 lines)
Lines 24-49 $dbh->do(q| Link Here
24
|);
24
|);
25
25
26
$dbh->do(q|
26
$dbh->do(q|
27
    INSERT INTO overduerules ( branchcode, categorycode ) VALUES
27
    INSERT INTO overduerules ( overduerules_id, branchcode, categorycode ) VALUES
28
    ('CPL', 'PT'),
28
    (1, 'CPL', 'PT'),
29
    ('CPL', 'YA'),
29
    (2, 'CPL', 'YA'),
30
    ('', 'PT'),
30
    (3, '', 'PT'),
31
    ('', 'YA')
31
    (4, '', 'YA')
32
|);
32
|);
33
33
34
$dbh->do(q|
34
$dbh->do(q|INSERT INTO overduerules_transport_types (overduerules_id, letternumber, message_transport_type) VALUES
35
    INSERT INTO overduerules_transport_types( branchcode, categorycode, letternumber, message_transport_type ) VALUES
35
    (1, 1, 'email'),
36
    ('CPL', 'PT', 1, 'email'),
36
    (1, 2, 'sms'),
37
    ('CPL', 'PT', 2, 'sms'),
37
    (1, 3, 'email'),
38
    ('CPL', 'PT', 3, 'email'),
38
    (2, 3, 'print'),
39
    ('CPL', 'YA', 3, 'print'),
39
    (3, 1, 'email'),
40
    ('', 'PT', 1, 'email'),
40
    (3, 2, 'email'),
41
    ('', 'PT', 2, 'email'),
41
    (3, 2, 'sms'),
42
    ('', 'PT', 2, 'sms'),
42
    (3, 3, 'sms'),
43
    ('', 'PT', 3, 'sms'),
43
    (3, 3, 'email'),
44
    ('', 'PT', 3, 'email'),
44
    (3, 3, 'print'),
45
    ('', 'PT', 3, 'print'),
45
    (4, 2, 'sms')
46
    ('', 'YA', 2, 'sms')
47
|);
46
|);
48
47
49
my $mtts;
48
my $mtts;
(-)a/tools/overduerules.pl (-4 / +3 lines)
Lines 87-100 if ($op eq 'save') { Link Here
87
    my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?");
87
    my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?");
88
    my $sth_insert_mtt = $dbh->prepare("
88
    my $sth_insert_mtt = $dbh->prepare("
89
        INSERT INTO overduerules_transport_types(
89
        INSERT INTO overduerules_transport_types(
90
            branchcode, categorycode, letternumber, message_transport_type
90
            overduerules_id, letternumber, message_transport_type
91
        ) VALUES (
91
        ) VALUES (
92
            ?, ?, ?, ?
92
            (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?
93
        )
93
        )
94
    ");
94
    ");
95
    my $sth_delete_mtt = $dbh->prepare("
95
    my $sth_delete_mtt = $dbh->prepare("
96
        DELETE FROM overduerules_transport_types
96
        DELETE FROM overduerules_transport_types
97
        WHERE branchcode = ? AND categorycode = ?
97
        WHERE overduerules_id = (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?)
98
    ");
98
    ");
99
99
100
    foreach my $key (@names){
100
    foreach my $key (@names){
101
- 

Return to bug 13624