From 947c3f9226b99723d088af2404e18767743b9e82 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 16 Jan 2023 21:09:05 +0100 Subject: [PATCH] Bug 25655: DB changes Sponsored-by: The Research University in the Helmholtz Association (KIT) --- .../data/mysql/atomicupdate/bug_25655.pl | 26 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 +++ 2 files changed, 29 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_25655.pl diff --git a/installer/data/mysql/atomicupdate/bug_25655.pl b/installer/data/mysql/atomicupdate/bug_25655.pl new file mode 100755 index 00000000000..01be1a1ee21 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_25655.pl @@ -0,0 +1,26 @@ +use Modern::Perl; + +return { + bug_number => "25655", + description => "Store actual cost in foreign currency and currency from the invoices", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + unless ( column_exists('aqorders', 'invoice_unitprice') ) { + $dbh->do(q{ + ALTER TABLE aqorders + ADD COLUMN invoice_unitprice decimal(28,6) DEFAULT NULL COMMENT 'the unit price in foreign currency' AFTER estimated_delivery_date + }); + } + say $out "Added column 'aqorders.invoice_unitprice'"; + + unless ( column_exists('aqorders', 'invoice_currency') ) { + $dbh->do(q{ + ALTER TABLE aqorders + ADD COLUMN invoice_currency varchar(10) DEFAULT NULL COMMENT 'the currency of the invoice_unitprice' AFTER invoice_unitprice, + ADD CONSTRAINT `aqorders_invoice_currency` FOREIGN KEY (`invoice_currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL + }); + } + say $out "Added column 'aqorders.invoice_currency'"; + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 59607073cac..aa109506d01 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -690,6 +690,8 @@ CREATE TABLE `aqorders` ( `suppliers_reference_qualifier` varchar(3) DEFAULT NULL COMMENT 'Type of number above usually ''QLI''', `suppliers_report` mediumtext DEFAULT NULL COMMENT 'reports received from suppliers', `estimated_delivery_date` date DEFAULT NULL COMMENT 'Estimated delivery date', + `invoice_unitprice` decimal(28,6) DEFAULT NULL COMMENT 'the unit price in foreign currency', + `invoice_currency` varchar(10) DEFAULT NULL COMMENT 'the currency of the invoice_unitprice', PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), @@ -703,6 +705,7 @@ CREATE TABLE `aqorders` ( CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `aqorders_created_by` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `aqorders_currency` FOREIGN KEY (`currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `aqorders_invoice_currency` FOREIGN KEY (`invoice_currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `aqorders_ibfk_3` FOREIGN KEY (`invoiceid`) REFERENCES `aqinvoices` (`invoiceid`) ON DELETE SET NULL ON UPDATE CASCADE, -- 2.25.1