Attachment #38290 for bug #13624

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

Lines 932-939 sub GetOverdueMessageTransportTypes { Link Here

(-)a/C4/Overdues.pm (-2 / +5 lines)
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;




#! /usr/bin/perl

use strict;
use warnings;
use C4::Context;
my $dbh=C4::Context->dbh;

print "Will do : Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table 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` mediumint NOT NULL AUTO_INCREMENT,
        `branchcode` varchar(10) NOT NULL DEFAULT '',
        `categorycode` varchar(10) NOT NULL DEFAULT '',
        `delay1` int(4) DEFAULT NULL,
        `letter1` varchar(20) DEFAULT NULL,
        `debarred1` varchar(1) DEFAULT '0',
        `delay2` int(4) DEFAULT NULL,
        `debarred2` varchar(1) DEFAULT '0',
        `letter2` varchar(20) DEFAULT NULL,
        `delay3` int(4) DEFAULT NULL,
        `letter3` varchar(20) DEFAULT NULL,
        `debarred3` int(1) DEFAULT '0',
        PRIMARY KEY (`overduerules_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
    $dbh->do("DROP TABLE old_overduerules");
    $dbh->do("ALTER TABLE overduerules_transport_types
              ADD COLUMN overduerules_id mediumint 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 FOREIGN KEY overduerules_fk,
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
              DROP COLUMN branchcode,
              DROP COLUMN categorycode");
    my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, 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->{letternumber}, $mtt->{message_transport_type} );    
    }
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
#    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
#   SetVersion ($DBversion);
#}

print "\nDone\n";





DROP TABLE IF EXISTS `overduerules`;
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
  `overduerules_id` mediumint 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

  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

DROP TABLE IF EXISTS `overduerules_transport_types`;
CREATE TABLE overduerules_transport_types(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `branchcode` varchar(10) NOT NULL DEFAULT '',
    `categorycode` VARCHAR(10) NOT NULL DEFAULT '',
    `letternumber` INT(1) NOT NULL DEFAULT 1,
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
    `overduerules_id` mediumint NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





|);

$dbh->do(q|
    INSERT INTO overduerules ( overduerules_id, branchcode, categorycode ) VALUES
    (1, 'CPL', 'PT'),
    (2, 'CPL', 'YA'),
    (3, '', 'PT'),
    (4, '', 'YA')
|);

$dbh->do(q|INSERT INTO overduerules_transport_types (overduerules_id, letternumber, message_transport_type) VALUES
    (1, 1, 'email'),
    (1, 2, 'sms'),
    (1, 3, 'email'),
    (2, 3, 'print'),
    (3, 1, 'email'),
    (3, 2, 'email'),
    (3, 2, 'sms'),
    (3, 3, 'sms'),
    (3, 3, 'email'),
    (3, 3, 'print'),
    (4, 2, 'sms')
    ('', 'YA', 2, 'sms')
|);

my $mtts;




    my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?");
    my $sth_insert_mtt = $dbh->prepare("
        INSERT INTO overduerules_transport_types(
            overduerules_id, letternumber, message_transport_type
        ) VALUES (
            (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?
        )
    ");
    my $sth_delete_mtt = $dbh->prepare("
        DELETE FROM overduerules_transport_types
        WHERE overduerules_id = (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?)
    ");

    foreach my $key (@names){
- 

Return to bug 13624

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (+49 lines)
	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";

Lines 1702-1707 CREATE TABLE `opac_news` ( -- data from the news tool Link Here

(-)a/installer/data/mysql/kohastructure.sql (-5 / +4 lines)
1702		1702
1703	DROP TABLE IF EXISTS `overduerules`;	1703	DROP TABLE IF EXISTS `overduerules`;
1704	CREATE TABLE `overduerules` ( -- overdue notice status and triggers	1704	CREATE TABLE `overduerules` ( -- overdue notice status and triggers
		1705	`overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1705	`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)	1706	`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)
1706	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for	1707	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1707	`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent	1708	`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
Lines 1713-1719 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1713	`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent	1714	`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1714	`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice	1715	`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1715	`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)	1716	`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1716	PRIMARY KEY (`branchcode`,`categorycode`)	1717	PRIMARY KEY (`overduerules_id`)
1717	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	1718	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1718		1719
1719	--	1720	--
Lines 2600-2611 CREATE TABLE `message_transport_types` ( Link Here
2600	DROP TABLE IF EXISTS `overduerules_transport_types`;	2601	DROP TABLE IF EXISTS `overduerules_transport_types`;
2601	CREATE TABLE overduerules_transport_types(	2602	CREATE TABLE overduerules_transport_types(
2602	`id` INT(11) NOT NULL AUTO_INCREMENT,	2603	`id` INT(11) NOT NULL AUTO_INCREMENT,
2603	`branchcode` varchar(10) NOT NULL DEFAULT '',
2604	`categorycode` VARCHAR(10) NOT NULL DEFAULT '',
2605	`letternumber` INT(1) NOT NULL DEFAULT 1,
2606	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',	2604	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
		2605	`overduerules_id` mediumint NOT NULL,
2607	PRIMARY KEY (id),	2606	PRIMARY KEY (id),
2608	CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,	2607	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2609	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE	2608	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2610	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	2609	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2611		2610

Lines 24-49 $dbh->do(q\| Link Here

(-)a/t/db_dependent/Overdues.t (-18 / +17 lines)
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;

Lines 87-100 if ($op eq 'save') { Link Here

(-)a/tools/overduerules.pl (-4 / +3 lines)
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	-