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

(-)a/C4/Overdues.pm (-2 / +5 lines)
Lines 937-944 sub GetOverdueMessageTransportTypes { Link Here
937
    return unless $categorycode and $letternumber;
937
    return unless $categorycode and $letternumber;
938
    my $dbh = C4::Context->dbh;
938
    my $dbh = C4::Context->dbh;
939
    my $sth = $dbh->prepare("
939
    my $sth = $dbh->prepare("
940
        SELECT message_transport_type FROM overduerules_transport_types
940
        SELECT message_transport_type 
941
        WHERE branchcode = ? AND categorycode = ? AND letternumber = ?
941
        FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id) 
942
        WHERE branchcode = ? 
943
          AND categorycode = ? 
944
          AND letternumber = ?
942
    ");
945
    ");
943
    $sth->execute( $branchcode, $categorycode, $letternumber );
946
    $sth->execute( $branchcode, $categorycode, $letternumber );
944
    my @mtts;
947
    my @mtts;
(-)a/installer/data/mysql/kohastructure.sql (-4 / +4 lines)
Lines 1700-1705 CREATE TABLE `opac_news` ( -- data from the news tool Link Here
1700
1700
1701
DROP TABLE IF EXISTS `overduerules`;
1701
DROP TABLE IF EXISTS `overduerules`;
1702
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1702
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1703
  `overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1703
  `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)
1704
  `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)
1704
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1705
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1705
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
1706
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
Lines 1711-1717 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1711
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1712
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1712
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1713
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1713
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1714
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1714
  PRIMARY KEY  (`branchcode`,`categorycode`)
1715
  PRIMARY KEY  (`overduerules_id`)
1715
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1716
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1716
1717
1717
--
1718
--
Lines 2596-2607 CREATE TABLE `message_transport_types` ( Link Here
2596
DROP TABLE IF EXISTS `overduerules_transport_types`;
2597
DROP TABLE IF EXISTS `overduerules_transport_types`;
2597
CREATE TABLE overduerules_transport_types(
2598
CREATE TABLE overduerules_transport_types(
2598
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2599
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2599
    `branchcode` varchar(10) NOT NULL DEFAULT '',
2600
    `overduerules_id` mediumint NOT NULL,
2600
    `categorycode` VARCHAR(10) NOT NULL DEFAULT '',
2601
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2601
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2602
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2602
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2603
    PRIMARY KEY (id),
2603
    PRIMARY KEY (id),
2604
    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
2604
    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2605
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2605
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2606
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2606
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2607
2607
(-)a/installer/data/mysql/updatedatabase.pl (+41 lines)
Lines 9779-9784 if ( CheckVersion($DBversion) ) { Link Here
9779
    SetVersion ($DBversion);
9779
    SetVersion ($DBversion);
9780
}
9780
}
9781
9781
9782
$DBversion = "XXX";
9783
if ( CheckVersion($DBversion) ) {
9784
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
9785
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
9786
    $dbh->do("CREATE TABLE overduerules (
9787
        `overduerules_id` mediumint NOT NULL AUTO_INCREMENT,
9788
        `branchcode` varchar(10) NOT NULL DEFAULT '',
9789
        `categorycode` varchar(10) NOT NULL DEFAULT '',
9790
        `delay1` int(4) DEFAULT NULL,
9791
        `letter1` varchar(20) DEFAULT NULL,
9792
        `debarred1` varchar(1) DEFAULT '0',
9793
        `delay2` int(4) DEFAULT NULL,
9794
        `debarred2` varchar(1) DEFAULT '0',
9795
        `letter2` varchar(20) DEFAULT NULL,
9796
        `delay3` int(4) DEFAULT NULL,
9797
        `letter3` varchar(20) DEFAULT NULL,
9798
        `debarred3` int(1) DEFAULT '0',
9799
        PRIMARY KEY (`overduerules_id`)
9800
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
9801
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
9802
    $dbh->do("DROP TABLE old_overduerules");
9803
    $dbh->do("ALTER TABLE overduerules_transport_types
9804
              ADD COLUMN overduerules_id mediumint NOT NULL");
9805
    my $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?");
9806
    my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
9807
    $dbh->do("DELETE FROM overduerules_transport_types");
9808
    $dbh->do("ALTER TABLE overduerules_transport_types
9809
              DROP FOREIGN KEY overduerules_fk,
9810
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
9811
              DROP COLUMN branchcode,
9812
              DROP COLUMN categorycode");
9813
    my $s = $dbh->prepare("insert into overduerules_transport_types (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)");
9814
    foreach my $mtt(@$mtts){
9815
        $s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} );
9816
        $update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id});
9817
    }
9818
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
9819
    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
9820
    SetVersion ($DBversion);
9821
}
9822
9782
=head1 FUNCTIONS
9823
=head1 FUNCTIONS
9783
9824
9784
=head2 TableExists($table)
9825
=head2 TableExists($table)
(-)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 (-3 / +2 lines)
Lines 89-100 if ($op eq 'save') { Link Here
89
        INSERT INTO overduerules_transport_types(
89
        INSERT INTO overduerules_transport_types(
90
            branchcode, categorycode, letternumber, message_transport_type
90
            branchcode, categorycode, 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