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' ],
pop(@quote_chars);
} elsif (length $quote_chars[-1] > 1
&& substr($_, $i, length $quote_chars[-1]) eq $quote_chars[-1]) {
- $i += length $quote_chars[-1] - 1;
+ $i += length($quote_chars[-1]) - 1;
$char = $quote_chars[-1];
pop(@quote_chars);
}
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',
GetOptions(
'login|user=s' => \ my $login,
all => \ my $all,
- sugar => \ my $sugar,
'no-commit|dry-run' => \ my $nocommit,
help => sub { pod2usage(verbose => 99, sections => 'NAME|SYNOPSIS|OPTIONS') },
verbose => \ my $verbose,
);
$options->{login} = $login if $login;
- $options->{sugar} = $sugar;
$options->{all} = $all;
$options->{nocommit} = $nocommit;
$options->{verbose} = $verbose;
sub make_tables {
my @tables;
- if ($config{all} || $config{sugar}) {
- my ($type, $prefix) = $config{sugar} ? ('SUGAR', 'sugar_') : ('LXOFFICE', '');
- my $db = SL::DB::create(undef, $type);
- @tables =
- map { $package_names{$type}->{$_} ? "$_=" . $package_names{$type}->{$_} : $prefix ? "$_=$prefix$_" : $_ }
- grep { my $table = $_; !any { $_ eq $table } @{ $blacklist{$type} } }
+ if ($config{all}) {
+ my $db = SL::DB::create(undef, 'LXOFFICE');
+ @tables =
+ map { $package_names{LXOFFICE}->{$_} ? "$_=" . $package_names{LXOFFICE}->{$_} : $_ }
+ grep { my $table = $_; !any { $_ eq $table } @{ $blacklist{LXOFFICE} } }
$db->list_tables;
} elsif (@ARGV) {
@tables = @ARGV;
} else {
- error("You specified neither --sugar nor --all nor any specific tables.");
+ error("You specified neither --all nor any specific tables.");
usage();
}
=head1 SYNOPSIS
scripts/rose_create_model.pl --login login table1[=package1] [table2[=package2] ...]
- scripts/rose_create_model.pl --login login [--all|-a] [--sugar|-s]
+ scripts/rose_create_model.pl --login login [--all|-a]
# updates all models
scripts/rose_create_model.pl --login login --all
Process all tables from the database. Only those that are blacklistes in
L<SL::DB::Helper::Mappings> are excluded.
-=item C<--sugar, -s>
-
-Process tables in sugar schema instead of standard schema. Rarely useful unless
-you debug schema awareness of the RDBO layer.
-
=item C<--no-commit, -n>
+
=item C<--dry-run>
Do not write back generated files. This will do everything as usual but not
--- /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