Attachment #36497 for bug #13624

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

Lines 937-944 sub GetOverdueMessageTransportTypes { Link Here

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





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,
    `overduerules_id` mediumint NOT NULL,
    `categorycode` VARCHAR(10) NOT NULL DEFAULT '',
    `letternumber` INT(1) NOT NULL DEFAULT 1,
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
    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;





    SetVersion($DBversion);
}

$DBversion = "XXX";
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 $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?");
    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 (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)");
    foreach my $mtt(@$mtts){
        $s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} );
        $update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id});
    }
    $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);
}

=head1 FUNCTIONS

=head2 TableExists($table)




|);

$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;




        INSERT INTO overduerules_transport_types(
            branchcode, categorycode, 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

Lines 9793-9798 if(CheckVersion($DBversion)) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+41 lines)
9793	SetVersion($DBversion);	9793	SetVersion($DBversion);
9794	}	9794	}
9795		9795
		9796	$DBversion = "XXX";
		9797	if ( CheckVersion($DBversion) ) {
		9798	$dbh->do("SET FOREIGN_KEY_CHECKS=0");
		9799	$dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
		9800	$dbh->do("CREATE TABLE overduerules (
		9801	`overduerules_id` mediumint NOT NULL AUTO_INCREMENT,
		9802	`branchcode` varchar(10) NOT NULL DEFAULT '',
		9803	`categorycode` varchar(10) NOT NULL DEFAULT '',
		9804	`delay1` int(4) DEFAULT NULL,
		9805	`letter1` varchar(20) DEFAULT NULL,
		9806	`debarred1` varchar(1) DEFAULT '0',
		9807	`delay2` int(4) DEFAULT NULL,
		9808	`debarred2` varchar(1) DEFAULT '0',
		9809	`letter2` varchar(20) DEFAULT NULL,
		9810	`delay3` int(4) DEFAULT NULL,
		9811	`letter3` varchar(20) DEFAULT NULL,
		9812	`debarred3` int(1) DEFAULT '0',
		9813	PRIMARY KEY (`overduerules_id`)
		9814	) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		9815	$dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
		9816	$dbh->do("DROP TABLE old_overduerules");
		9817	$dbh->do("ALTER TABLE overduerules_transport_types
		9818	ADD COLUMN overduerules_id mediumint NOT NULL");
		9819	my $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?");
		9820	my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
		9821	$dbh->do("DELETE FROM overduerules_transport_types");
		9822	$dbh->do("ALTER TABLE overduerules_transport_types
		9823	DROP FOREIGN KEY overduerules_fk,
		9824	ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
		9825	DROP COLUMN branchcode,
		9826	DROP COLUMN categorycode");
		9827	my $s = $dbh->prepare("insert into overduerules_transport_types (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)");
		9828	foreach my $mtt(@$mtts){
		9829	$s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} );
		9830	$update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id});
		9831	}
		9832	$dbh->do("SET FOREIGN_KEY_CHECKS=1");
		9833	print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
		9834	SetVersion ($DBversion);
		9835	}
		9836
9796	=head1 FUNCTIONS	9837	=head1 FUNCTIONS
9797		9838
9798	=head2 TableExists($table)	9839	=head2 TableExists($table)

Lines 1700-1705 CREATE TABLE `opac_news` ( -- data from the news tool Link Here

(-)a/installer/data/mysql/kohastructure.sql (-4 / +4 lines)
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

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 89-100 if ($op eq 'save') { Link Here

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