Attachment #37074 for bug #13624

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

Lines 2599-2607 CREATE TABLE `message_transport_types` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-2 / +1 lines)
2599	DROP TABLE IF EXISTS `overduerules_transport_types`;	2599	DROP TABLE IF EXISTS `overduerules_transport_types`;
2600	CREATE TABLE overduerules_transport_types(	2600	CREATE TABLE overduerules_transport_types(
2601	`id` INT(11) NOT NULL AUTO_INCREMENT,	2601	`id` INT(11) NOT NULL AUTO_INCREMENT,
2602	`overduerules_id` mediumint NOT NULL,
2603	`letternumber` INT(1) NOT NULL DEFAULT 1,
2604	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',	2602	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
		2603	`overduerules_id` mediumint NOT NULL,
2605	PRIMARY KEY (id),	2604	PRIMARY KEY (id),
2606	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,	2605	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2607	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE	2606	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE




    $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

              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} );
        $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";

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

(-)a/tools/overduerules.pl (-2 / +1 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	(SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?	92	(SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ?
93	)	93	)
94	-

Return to bug 13624

Lines 9890-9896 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (-4 / +3 lines)
9890	$dbh->do("DROP TABLE old_overduerules");	9890	$dbh->do("DROP TABLE old_overduerules");
9891	$dbh->do("ALTER TABLE overduerules_transport_types	9891	$dbh->do("ALTER TABLE overduerules_transport_types
9892	ADD COLUMN overduerules_id mediumint NOT NULL");	9892	ADD COLUMN overduerules_id mediumint NOT NULL");
9893	my $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?");
9894	my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });	9893	my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
9895	$dbh->do("DELETE FROM overduerules_transport_types");	9894	$dbh->do("DELETE FROM overduerules_transport_types");
9896	$dbh->do("ALTER TABLE overduerules_transport_types	9895	$dbh->do("ALTER TABLE overduerules_transport_types
Lines 9898-9907 if ( CheckVersion($DBversion) ) { Link Here
9898	ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,	9897	ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
9899	DROP COLUMN branchcode,	9898	DROP COLUMN branchcode,
9900	DROP COLUMN categorycode");	9899	DROP COLUMN categorycode");
9901	my $s = $dbh->prepare("insert into overduerules_transport_types (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)");	9900	my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
		9901	." values((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
9902	foreach my $mtt(@$mtts){	9902	foreach my $mtt(@$mtts){
9903	$s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} );	9903	$s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
9904	$update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id});
9905	}	9904	}
9906	$dbh->do("SET FOREIGN_KEY_CHECKS=1");	9905	$dbh->do("SET FOREIGN_KEY_CHECKS=1");
9907	print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";	9906	print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";