From 9fda7972d8dd37f1a760a7b430b638c202aac2b7 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Wed, 17 Jun 2020 11:40:03 +0200 Subject: [PATCH] Wechselkurs pro Angebot/Auftrag: DB-Upgrade-Skript + Rose MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit exchangerate direkt in Tabelle oe ablegen. Die Implementierung, um bei Angeboten/Aufträgen den Wechselkurs pro Beleg zu speichern folgt in weiteren commits und wird erstmal nur für den neuen Auftrags-Controller umgesetzt. Bezieht sich auch auf #135 Refs #135 --- SL/DB/MetaSetup/Order.pm | 1 + sql/Pg-upgrade2/exchangerate_in_oe.sql | 12 ++++++++++++ 2 files changed, 13 insertions(+) create mode 100644 sql/Pg-upgrade2/exchangerate_in_oe.sql diff --git a/SL/DB/MetaSetup/Order.pm b/SL/DB/MetaSetup/Order.pm index b359c07b2..d6922bd53 100644 --- a/SL/DB/MetaSetup/Order.pm +++ b/SL/DB/MetaSetup/Order.pm @@ -21,6 +21,7 @@ __PACKAGE__->meta->columns( delivery_vendor_id => { type => 'integer' }, department_id => { type => 'integer' }, employee_id => { type => 'integer' }, + exchangerate => { type => 'numeric', precision => 15, scale => 5 }, expected_billing_date => { type => 'date' }, globalproject_id => { type => 'integer' }, id => { type => 'integer', not_null => 1, sequence => 'id' }, diff --git a/sql/Pg-upgrade2/exchangerate_in_oe.sql b/sql/Pg-upgrade2/exchangerate_in_oe.sql new file mode 100644 index 000000000..ca3defd0d --- /dev/null +++ b/sql/Pg-upgrade2/exchangerate_in_oe.sql @@ -0,0 +1,12 @@ +-- @tag: exchangerate_in_oe +-- @description: Wechselkurs pro Angebot/Auftrag in Belegtabelle speichern +-- @depends: release_3_5_5 + +ALTER TABLE oe ADD COLUMN exchangerate NUMERIC(15,5); + +WITH table_ex AS + (SELECT oe.id, COALESCE(CASE WHEN customer_id IS NOT NULL THEN buy ELSE sell END, 1.0) AS exchangerate FROM oe + LEFT JOIN exchangerate ON (oe.transdate = exchangerate.transdate AND oe.currency_id = exchangerate.currency_id) + WHERE oe.currency_id != (SELECT currency_id FROM defaults)) + UPDATE oe SET exchangerate = (SELECT exchangerate FROM table_ex WHERE table_ex.id = oe.id) + WHERE EXISTS (SELECT table_ex.exchangerate FROM table_ex WHERE table_ex.id = oe.id); -- 2.20.1