From 7ea32650019b2611e03f83ccaa904a03d6a6b3d1 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 8 Nov 2012 12:07:09 +0100 Subject: [PATCH] =?utf8?q?W=C3=A4hrungen=20mit=20mehr=20als=20drei=20Zeich?= =?utf8?q?en=20erlauben=20&=20Spaltentyp=20'text'=20anstelle=20von=20'char?= =?utf8?q?(3)'?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Alle Währungsspalten sind nun vom Typ 'text' anstatt 'char(3)'. Das hat zwei Effekte: 1. Es ist möglich, Währungen einzugeben, deren Abkürzung nicht exakt drei Zeichen lang ist. 2. Fixt #1934. Durch Umstellung von "keine Währung" auf "Währungen" wurden Inhalte von 'char(3)' als drei Leerzeichen von PostgreSQL zurückgegeben (das ist SQL-Standardkonform), was von kivitendo als "oh das ist nicht die Standardwährung" interpretiert wurde. --- SL/DB/MetaSetup/Customer.pm | 2 +- SL/DB/MetaSetup/DeliveryOrder.pm | 2 +- SL/DB/MetaSetup/Exchangerate.pm | 2 +- SL/DB/MetaSetup/Invoice.pm | 2 +- SL/DB/MetaSetup/PurchaseInvoice.pm | 2 +- SL/DB/MetaSetup/RMA.pm | 2 +- SL/DB/MetaSetup/Vendor.pm | 2 +- SL/Form.pm | 2 +- locale/de/all | 2 +- sql/Pg-upgrade2/convert_curr_to_text.sql | 108 +++++++++++++++++++++++ templates/webpages/am/edit_defaults.html | 3 +- 11 files changed, 118 insertions(+), 11 deletions(-) create mode 100644 sql/Pg-upgrade2/convert_curr_to_text.sql diff --git a/SL/DB/MetaSetup/Customer.pm b/SL/DB/MetaSetup/Customer.pm index 51779f377..82d808cf9 100644 --- a/SL/DB/MetaSetup/Customer.pm +++ b/SL/DB/MetaSetup/Customer.pm @@ -53,7 +53,7 @@ __PACKAGE__->meta->setup( iban => { type => 'varchar', length => 100 }, bic => { type => 'varchar', length => 100 }, direct_debit => { type => 'boolean', default => 'false' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, taxincluded_checked => { type => 'boolean' }, ], diff --git a/SL/DB/MetaSetup/DeliveryOrder.pm b/SL/DB/MetaSetup/DeliveryOrder.pm index 66596543d..fb4b82818 100644 --- a/SL/DB/MetaSetup/DeliveryOrder.pm +++ b/SL/DB/MetaSetup/DeliveryOrder.pm @@ -39,7 +39,7 @@ __PACKAGE__->meta->setup( taxzone_id => { type => 'integer' }, taxincluded => { type => 'boolean' }, terms => { type => 'integer' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, ], primary_key_columns => [ 'id' ], diff --git a/SL/DB/MetaSetup/Exchangerate.pm b/SL/DB/MetaSetup/Exchangerate.pm index 95ea23c17..44bce842b 100644 --- a/SL/DB/MetaSetup/Exchangerate.pm +++ b/SL/DB/MetaSetup/Exchangerate.pm @@ -10,7 +10,7 @@ __PACKAGE__->meta->setup( table => 'exchangerate', columns => [ - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, transdate => { type => 'date' }, buy => { type => 'numeric', precision => 5, scale => 15 }, sell => { type => 'numeric', precision => 5, scale => 15 }, diff --git a/SL/DB/MetaSetup/Invoice.pm b/SL/DB/MetaSetup/Invoice.pm index d300a7e6a..854b9c7e3 100644 --- a/SL/DB/MetaSetup/Invoice.pm +++ b/SL/DB/MetaSetup/Invoice.pm @@ -26,7 +26,7 @@ __PACKAGE__->meta->setup( shippingpoint => { type => 'text' }, terms => { type => 'integer', default => '0' }, notes => { type => 'text' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, ordnumber => { type => 'text' }, employee_id => { type => 'integer' }, quonumber => { type => 'text' }, diff --git a/SL/DB/MetaSetup/PurchaseInvoice.pm b/SL/DB/MetaSetup/PurchaseInvoice.pm index 448b2a6e0..eb18f7058 100644 --- a/SL/DB/MetaSetup/PurchaseInvoice.pm +++ b/SL/DB/MetaSetup/PurchaseInvoice.pm @@ -23,7 +23,7 @@ __PACKAGE__->meta->setup( duedate => { type => 'date' }, invoice => { type => 'boolean', default => 'false' }, ordnumber => { type => 'text' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, notes => { type => 'text' }, employee_id => { type => 'integer' }, quonumber => { type => 'text' }, diff --git a/SL/DB/MetaSetup/RMA.pm b/SL/DB/MetaSetup/RMA.pm index 20790e360..beae23228 100644 --- a/SL/DB/MetaSetup/RMA.pm +++ b/SL/DB/MetaSetup/RMA.pm @@ -21,7 +21,7 @@ __PACKAGE__->meta->setup( taxincluded => { type => 'boolean' }, shippingpoint => { type => 'text' }, notes => { type => 'text' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, employee_id => { type => 'integer' }, closed => { type => 'boolean', default => 'false' }, quotation => { type => 'boolean', default => 'false' }, diff --git a/SL/DB/MetaSetup/Vendor.pm b/SL/DB/MetaSetup/Vendor.pm index 387b2d857..cc9366339 100644 --- a/SL/DB/MetaSetup/Vendor.pm +++ b/SL/DB/MetaSetup/Vendor.pm @@ -53,7 +53,7 @@ __PACKAGE__->meta->setup( iban => { type => 'varchar', length => 100 }, bic => { type => 'varchar', length => 100 }, direct_debit => { type => 'boolean', default => 'false' }, - curr => { type => 'character', length => 3 }, + curr => { type => 'text' }, ], primary_key_columns => [ 'id' ], diff --git a/SL/Form.pm b/SL/Form.pm index ce887c347..3e9704fd2 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -1607,7 +1607,7 @@ sub get_exchangerate { my ($self, $dbh, $curr, $transdate, $fld) = @_; my ($query); - unless ($transdate) { + unless ($transdate && $curr) { $main::lxdebug->leave_sub(); return 1; } diff --git a/locale/de/all b/locale/de/all index 604ae2eb8..f5a79c387 100644 --- a/locale/de/all +++ b/locale/de/all @@ -794,8 +794,8 @@ $self->{texts} = { 'End date' => 'Enddatum', 'Enter a description for this new draft.' => 'Geben Sie eine Beschreibung für diesen Entwurf ein.', 'Enter longdescription' => 'Langtext eingeben', + 'Enter the abbreviations separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' => 'Geben Sie Ihre und weitere Währungen als Abkürzungen durch Doppelpunkte getrennt ein (z.B. EUR:USD:CAD)', 'Enter the requested execution date or leave empty for the quickest possible execution:' => 'Geben Sie das jeweils gewünschte Ausführungsdatum an, oder lassen Sie das Feld leer für die schnellstmögliche Ausführung:', - 'Enter up to 3 letters separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' => 'Geben Sie Ihre und weitere Währungen mit bis zu drei Buchstaben pro Währung und Währungen durch Doppelpunkte getrennt ein (z.B. EUR:USD:CAD)', 'Equity' => 'Passiva', 'Error' => 'Fehler', 'Error in database control file \'%s\': %s' => 'Fehler in Datenbankupgradekontrolldatei \'%s\': %s', diff --git a/sql/Pg-upgrade2/convert_curr_to_text.sql b/sql/Pg-upgrade2/convert_curr_to_text.sql new file mode 100644 index 000000000..90cc0349a --- /dev/null +++ b/sql/Pg-upgrade2/convert_curr_to_text.sql @@ -0,0 +1,108 @@ +-- @tag: convert_curr_to_text +-- @description: Spalte 'curr' von 'char(3)' nach 'text' konvertieren +-- @depends: release_2_7_0 +-- @charset: utf-8 + +-- Zuerst alle Spaltentypen konvertieren. +ALTER TABLE ap ALTER COLUMN curr TYPE text; +ALTER TABLE ar ALTER COLUMN curr TYPE text; +ALTER TABLE customer ALTER COLUMN curr TYPE text; +ALTER TABLE delivery_orders ALTER COLUMN curr TYPE text; +ALTER TABLE exchangerate ALTER COLUMN curr TYPE text; +ALTER TABLE rma ALTER COLUMN curr TYPE text; +ALTER TABLE vendor ALTER COLUMN curr TYPE text; + +-- Eventuell falsche Inhalte (Leerzeichenpadding) auf leere Strings setzen. +UPDATE ap SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE ar SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE customer SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE delivery_orders SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE exchangerate SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE oe SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE rma SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); +UPDATE vendor SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL); + +-- Nun noch die stored procedures anpassen. +CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS trigger + LANGUAGE plpgsql + AS $$ + DECLARE + t_transdate date; + t_curr text; + t_id int; + d_curr text; + BEGIN + SELECT INTO d_curr substring(curr FROM '[^:]*') FROM DEFAULTS; + + IF TG_RELNAME = 'ar' THEN + SELECT INTO t_curr, t_transdate curr, transdate FROM ar WHERE id = old.id; + END IF; + + IF TG_RELNAME = 'ap' THEN + SELECT INTO t_curr, t_transdate curr, transdate FROM ap WHERE id = old.id; + END IF; + + IF TG_RELNAME = 'oe' THEN + SELECT INTO t_curr, t_transdate curr, transdate FROM oe WHERE id = old.id; + END IF; + + IF TG_RELNAME = 'delivery_orders' THEN + SELECT INTO t_curr, t_transdate curr, transdate FROM delivery_orders WHERE id = old.id; + END IF; + + IF d_curr != t_curr THEN + SELECT INTO t_id a.id FROM acc_trans ac + JOIN ar a ON (a.id = ac.trans_id) + WHERE (a.curr = t_curr) + AND (ac.transdate = t_transdate) + EXCEPT SELECT a.id + FROM ar a + WHERE (a.id = old.id) + + UNION + + SELECT a.id + FROM acc_trans ac + JOIN ap a ON (a.id = ac.trans_id) + WHERE (a.curr = t_curr) + AND (ac.transdate = t_transdate) + EXCEPT SELECT a.id + FROM ap a + WHERE (a.id = old.id) + + UNION + + SELECT o.id + FROM oe o + WHERE (o.curr = t_curr) + AND (o.transdate = t_transdate) + EXCEPT SELECT o.id + FROM oe o + WHERE (o.id = old.id) + + UNION + + SELECT dord.id + FROM delivery_orders dord + WHERE (dord.curr = t_curr) + AND (dord.transdate = t_transdate) + EXCEPT SELECT dord.id + FROM delivery_orders dord + WHERE (dord.id = old.id); + + IF NOT FOUND THEN + DELETE FROM exchangerate + WHERE (curr = t_curr) + AND (transdate = t_transdate); + END IF; + END IF; + + RETURN old; + END; +$$; + +-- Und die stored procedure auch auf delivery_orders anwenden +CREATE TRIGGER del_exchangerate + BEFORE DELETE ON delivery_orders + FOR EACH ROW + EXECUTE PROCEDURE del_exchangerate(); diff --git a/templates/webpages/am/edit_defaults.html b/templates/webpages/am/edit_defaults.html index 05351ff29..36033dbdf 100644 --- a/templates/webpages/am/edit_defaults.html +++ b/templates/webpages/am/edit_defaults.html @@ -206,8 +206,7 @@

- (1) [% 'Enter up to 3 letters separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' | $T8 %] + (1) [% 'Enter the abbreviations separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' | $T8 %] [% 'IMPORTANT NOTE: You cannot safely change currencies, IF you have already booking entries!' | $T8 %]

- -- 2.20.1