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' },
],
taxzone_id => { type => 'integer' },
taxincluded => { type => 'boolean' },
terms => { type => 'integer' },
- curr => { type => 'character', length => 3 },
+ curr => { type => 'text' },
],
primary_key_columns => [ 'id' ],
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 },
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' },
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' },
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' },
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' ],
my ($self, $dbh, $curr, $transdate, $fld) = @_;
my ($query);
- unless ($transdate) {
+ unless ($transdate && $curr) {
$main::lxdebug->leave_sub();
return 1;
}
'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',
--- /dev/null
+-- @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();
<hr height="3" noshade>
<p>
- (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 %]
</p>
</form>
-
projects / mfinanz.git / commitdiff