From cc87243050bcec588ba8a5995ba3f9eca761ad2b Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Tue, 26 Aug 2014 10:53:15 +0200 Subject: [PATCH] Bearb. Buchungsgruppen: Spalte taxzone_id in oe/delivery_orders konvertieren MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 1. Werte 0 und NULL durch neue ID für »Inland« ersetzen (oe, delivery_orders, ar, ap) 2. Spalte taxzone_id NOT NULL setzen (oe, delivery_orders, ar, ap) 3. Fremdschlüssel auf Tabelle tax_zones setzen (oe, delivery_orders) --- SL/DB/MetaSetup/DeliveryOrder.pm | 7 ++++++- SL/DB/MetaSetup/Invoice.pm | 2 +- SL/DB/MetaSetup/Order.pm | 7 ++++++- SL/DB/MetaSetup/PurchaseInvoice.pm | 2 +- .../taxzone_id_in_oe_delivery_orders.sql | 16 ++++++++++++++++ 5 files changed, 30 insertions(+), 4 deletions(-) create mode 100644 sql/Pg-upgrade2/taxzone_id_in_oe_delivery_orders.sql diff --git a/SL/DB/MetaSetup/DeliveryOrder.pm b/SL/DB/MetaSetup/DeliveryOrder.pm index a1efe9a84..c4ee93083 100644 --- a/SL/DB/MetaSetup/DeliveryOrder.pm +++ b/SL/DB/MetaSetup/DeliveryOrder.pm @@ -35,7 +35,7 @@ __PACKAGE__->meta->columns( shipto_id => { type => 'integer' }, shipvia => { type => 'text' }, taxincluded => { type => 'boolean' }, - taxzone_id => { type => 'integer' }, + taxzone_id => { type => 'integer', not_null => 1 }, terms => { type => 'integer' }, transaction_description => { type => 'text' }, transdate => { type => 'date', default => 'now()' }, @@ -97,6 +97,11 @@ __PACKAGE__->meta->foreign_keys( key_columns => { shipto_id => 'shipto_id' }, }, + taxzone => { + class => 'SL::DB::TaxZone', + key_columns => { taxzone_id => 'id' }, + }, + vendor => { class => 'SL::DB::Vendor', key_columns => { vendor_id => 'id' }, diff --git a/SL/DB/MetaSetup/Invoice.pm b/SL/DB/MetaSetup/Invoice.pm index b80cee70a..72aaa4505 100644 --- a/SL/DB/MetaSetup/Invoice.pm +++ b/SL/DB/MetaSetup/Invoice.pm @@ -52,7 +52,7 @@ __PACKAGE__->meta->columns( storno => { type => 'boolean', default => 'false' }, storno_id => { type => 'integer' }, taxincluded => { type => 'boolean' }, - taxzone_id => { type => 'integer' }, + taxzone_id => { type => 'integer', not_null => 1 }, terms => { type => 'integer', default => '0' }, transaction_description => { type => 'text' }, transdate => { type => 'date', default => 'now' }, diff --git a/SL/DB/MetaSetup/Order.pm b/SL/DB/MetaSetup/Order.pm index 92bb92ffd..5e67aac75 100644 --- a/SL/DB/MetaSetup/Order.pm +++ b/SL/DB/MetaSetup/Order.pm @@ -44,7 +44,7 @@ __PACKAGE__->meta->columns( shipto_id => { type => 'integer' }, shipvia => { type => 'text' }, taxincluded => { type => 'boolean' }, - taxzone_id => { type => 'integer' }, + taxzone_id => { type => 'integer', not_null => 1 }, transaction_description => { type => 'text' }, transdate => { type => 'date', default => 'now' }, vendor_id => { type => 'integer' }, @@ -120,6 +120,11 @@ __PACKAGE__->meta->foreign_keys( key_columns => { shipto_id => 'shipto_id' }, }, + taxzone => { + class => 'SL::DB::TaxZone', + key_columns => { taxzone_id => 'id' }, + }, + vendor => { class => 'SL::DB::Vendor', key_columns => { vendor_id => 'id' }, diff --git a/SL/DB/MetaSetup/PurchaseInvoice.pm b/SL/DB/MetaSetup/PurchaseInvoice.pm index 6de5abd2a..b3cb02970 100644 --- a/SL/DB/MetaSetup/PurchaseInvoice.pm +++ b/SL/DB/MetaSetup/PurchaseInvoice.pm @@ -40,7 +40,7 @@ __PACKAGE__->meta->columns( storno => { type => 'boolean', default => 'false' }, storno_id => { type => 'integer' }, taxincluded => { type => 'boolean', default => 'false' }, - taxzone_id => { type => 'integer' }, + taxzone_id => { type => 'integer', not_null => 1 }, transaction_description => { type => 'text' }, transdate => { type => 'date', default => 'now' }, type => { type => 'text' }, diff --git a/sql/Pg-upgrade2/taxzone_id_in_oe_delivery_orders.sql b/sql/Pg-upgrade2/taxzone_id_in_oe_delivery_orders.sql new file mode 100644 index 000000000..00a654e5a --- /dev/null +++ b/sql/Pg-upgrade2/taxzone_id_in_oe_delivery_orders.sql @@ -0,0 +1,16 @@ +-- @tag: taxzone_id_in_oe_delivery_orders +-- @description: Werte für Inland in Spalte taxzone_id in Tabellen oe und delivery_orders in Foreign Key zu tax_zones konvertieren; NULL-Werte in ap/ar verhindern; Spalten NOT NULL setzen +-- @depends: change_taxzone_id_0 + +UPDATE oe SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); +UPDATE delivery_orders SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); +UPDATE ar SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); +UPDATE ap SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Inland') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL); + +ALTER TABLE oe ALTER COLUMN taxzone_id SET NOT NULL; +ALTER TABLE delivery_orders ALTER COLUMN taxzone_id SET NOT NULL; +ALTER TABLE ar ALTER COLUMN taxzone_id SET NOT NULL; +ALTER TABLE ap ALTER COLUMN taxzone_id SET NOT NULL; + +ALTER TABLE oe ADD CONSTRAINT oe_taxzone_id_fkey FOREIGN KEY (taxzone_id) REFERENCES tax_zones (id); +ALTER TABLE delivery_orders ADD CONSTRAINT delivery_orders_taxzone_id_fkey FOREIGN KEY (taxzone_id) REFERENCES tax_zones (id); -- 2.20.1