From 030c208648ddd8137d8fab33bb8b2eb87f5c080f Mon Sep 17 00:00:00 2001 From: Rolf Fluehmann Date: Fri, 13 May 2016 18:39:05 +0200 Subject: [PATCH] Belege runden, und Rundungsdifferenzen auf Extrakonten buchen. --- SL/DB/MetaSetup/Default.pm | 2 ++ SL/Form.pm | 8 +++-- SL/IS.pm | 32 ++++++++++++++++--- SL/OE.pm | 6 ++-- SL/RP.pm | 20 +++++------- bin/mozilla/am.pl | 2 +- bin/mozilla/is.pl | 7 ++++ bin/mozilla/oe.pl | 9 ++++-- locale/de/all | 3 ++ locale/en/all | 3 ++ .../defaults_add_rnd_accno_ids.sql | 6 ++++ templates/webpages/am/edit_accounts.html | 2 ++ .../client_config/_default_accounts.html | 10 ++++++ templates/webpages/is/form_footer.html | 8 ++++- templates/webpages/is/form_header.html | 2 ++ templates/webpages/oe/form_footer.html | 6 ++++ 16 files changed, 100 insertions(+), 26 deletions(-) create mode 100644 sql/Pg-upgrade2/defaults_add_rnd_accno_ids.sql diff --git a/SL/DB/MetaSetup/Default.pm b/SL/DB/MetaSetup/Default.pm index d8f350686..96c4b0f1f 100644 --- a/SL/DB/MetaSetup/Default.pm +++ b/SL/DB/MetaSetup/Default.pm @@ -91,6 +91,8 @@ __PACKAGE__->meta->columns( revtrans => { type => 'boolean', default => 'false' }, rfqnumber => { type => 'text' }, rmanumber => { type => 'text' }, + rndgain_accno_id => { type => 'integer' }, + rndloss_accno_id => { type => 'integer' }, sales_delivery_order_show_delete => { type => 'boolean', default => 'true' }, sales_order_show_delete => { type => 'boolean', default => 'true' }, sales_purchase_order_ship_missing_column => { type => 'boolean', default => 'false' }, diff --git a/SL/Form.pm b/SL/Form.pm index d712c94b1..fbcbe56f3 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -2886,7 +2886,9 @@ sub create_links { d.closedto, d.revtrans, (SELECT cu.name FROM currencies cu WHERE cu.id=d.currency_id) AS defaultcurrency, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno, + (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno FROM defaults d|; $ref = selectfirst_hashref_query($self, $dbh, $query); map { $self->{$_} = $ref->{$_} } keys %$ref; @@ -2899,7 +2901,9 @@ sub create_links { current_date AS transdate, d.closedto, d.revtrans, (SELECT cu.name FROM currencies cu WHERE cu.id=d.currency_id) AS defaultcurrency, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno, + (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno FROM defaults d|; $ref = selectfirst_hashref_query($self, $dbh, $query); map { $self->{$_} = $ref->{$_} } keys %$ref; diff --git a/SL/IS.pm b/SL/IS.pm index 10892dec1..6affa1de2 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -968,9 +968,21 @@ SQL } } - $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax; - $form->{paid} = - $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2); + # Invoice Summary includes Rounding + my $grossamount = $netamount + $tax; + my $rounding = $form->round_amount( + $form->round_amount($grossamount, 2, 1) - $form->round_amount($grossamount, 2), + 2 + ); + my $rnd_accno = $rounding == 0 ? 0 + : $rounding > 0 ? $form->{rndgain_accno} + : $form->{rndloss_accno} + ; + $form->{amount}{ $form->{id} }{ $form->{AR} } = $form->round_amount($grossamount, 2, 1); + $form->{paid} = $form->round_amount( + $form->{paid} * $form->{exchangerate} + $diff, + 2 + ); # reverse AR $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1; @@ -1073,6 +1085,14 @@ SQL do_query($form, $dbh, $query, @values); } } + if (!$payments_only && ($rnd_accno != 0)) { + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($trans_id), $rnd_accno, $rounding, conv_date($form->{invdate}), conv_i($project_id), $rnd_accno); + do_query($form, $dbh, $query, @values); + $rnd_accno = 0; + } } # deduct payment differences from diff @@ -1237,7 +1257,7 @@ SQL return; } - $amount = $netamount + $tax; + $amount = $form->round_amount( $netamount + $tax, 2, 1); # save AR record #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb @@ -1882,7 +1902,9 @@ sub retrieve_invoice { (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno, + (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno ${query_transdate} FROM defaults d|; diff --git a/SL/OE.pm b/SL/OE.pm index 7cd5de158..61af6211c 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -690,7 +690,7 @@ SQL my $tax = 0; map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts; - $amount = $form->round_amount($netamount + $tax, 2); + $amount = $form->round_amount($netamount + $tax, 2, 1); $netamount = $form->round_amount($netamount, 2); if ($form->{currency} eq $form->{defaultcurrency}) { @@ -956,7 +956,9 @@ sub retrieve { (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, - (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, + (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno, + (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno $query_add FROM defaults d|; my $ref = selectfirst_hashref_query($form, $dbh, $query); diff --git a/SL/RP.pm b/SL/RP.pm index 5554916c4..2895d534a 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -1873,12 +1873,8 @@ sub erfolgsrechnung { my ($self, $myconfig, $form) = @_; $form->{company} = $::instance_conf->get_company; $form->{address} = $::instance_conf->get_address; - #injection-filter - $form->{fromdate} =~ s/[^0-9\.]//g; - $form->{todate} =~ s/[^0-9\.]//g; - #input validation - $form->{fromdate} = "01.01.2000" if $form->{fromdate} !~ m/[0-9]*\.[0-9]*\.[0-9]*/; - $form->{todate} = $form->current_date(%{$myconfig}) if $form->{todate} !~ m/[0-9]*\.[0-9]*\.[0-9]*/; + $form->{fromdate} = DateTime->new(year => 2000, month => 1, day => 1)->to_kivitendo unless $form->{fromdate}; + $form->{todate} = $form->current_date(%{$myconfig}) unless $form->{todate}; my %categories = (I => "ERTRAG", E => "AUFWAND"); my $fromdate = conv_dateq($form->{fromdate}); @@ -1924,10 +1920,10 @@ sub get_accounts_ch { my $query = qq| SELECT id, accno, description, category FROM chart - WHERE category = '$category' $inclusion + WHERE category = ? $inclusion ORDER BY accno |; - my $accounts = _query($query); + my $accounts = _query($query, $category); $main::lxdebug->leave_sub(); return $accounts; @@ -1941,11 +1937,11 @@ sub get_total_ch { my $query = qq| SELECT SUM(amount) FROM acc_trans - WHERE chart_id = '$chart_id' - AND transdate >= $fromdate - AND transdate <= $todate + WHERE chart_id = ? + AND transdate >= ? + AND transdate <= ? |; - $total += _query($query)->[0]->{sum}; + $total += _query($query, $chart_id, $fromdate, $todate)->[0]->{sum}; $main::lxdebug->leave_sub(); return $total; diff --git a/bin/mozilla/am.pl b/bin/mozilla/am.pl index 09a671e93..62c645a6f 100644 --- a/bin/mozilla/am.pl +++ b/bin/mozilla/am.pl @@ -590,7 +590,7 @@ sub delete_account { $form->{title} = $locale->text('Delete Account'); foreach my $id ( - qw(inventory_accno_id income_accno_id expense_accno_id fxgain_accno_id fxloss_accno_id) + qw(inventory_accno_id income_accno_id expense_accno_id fxgain_accno_id fxloss_accno_id rndgain_accno_id rndloss_accno_id) ) { if ($form->{id} == $form->{$id}) { $form->error($locale->text('Cannot delete default account!')); diff --git a/bin/mozilla/is.pl b/bin/mozilla/is.pl index 3a3d433bb..631262e0d 100644 --- a/bin/mozilla/is.pl +++ b/bin/mozilla/is.pl @@ -463,6 +463,13 @@ sub form_footer { } } + my $grossamount = $form->{invtotal}; + $form->{invtotal} = $form->round_amount( $form->{invtotal}, 2, 1 ); + $form->{rounding} = $form->round_amount( + $form->{invtotal} - $form->round_amount($grossamount, 2), + 2 + ); + # follow ups if ($form->{id}) { $form->{follow_ups} = FU->follow_ups('trans_id' => $form->{id}) || []; diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl index 86f0298f7..e4e43e611 100644 --- a/bin/mozilla/oe.pl +++ b/bin/mozilla/oe.pl @@ -539,9 +539,6 @@ sub form_footer { |; } } - -# $form->{invsubtotal} = $form->format_amount(\%myconfig, $form->{invsubtotal}, 2, 0); # template does this - } else { foreach my $item (split / /, $form->{taxaccounts}) { if ($form->{"${item}_base"}) { @@ -563,6 +560,12 @@ sub form_footer { } } + my $grossamount = $form->{invtotal}; + $form->{invtotal} = $form->round_amount( $form->{invtotal}, 2, 1); + $form->{rounding} = $form->round_amount( + $form->{invtotal} - $form->round_amount($grossamount, 2), + 2 + ); $form->{oldinvtotal} = $form->{invtotal}; $TMPL_VAR{ALL_DELIVERY_TERMS} = SL::DB::Manager::DeliveryTerm->get_all_sorted(); diff --git a/locale/de/all b/locale/de/all index 796aa9ca0..393f44126 100755 --- a/locale/de/all +++ b/locale/de/all @@ -2320,6 +2320,9 @@ $self->{texts} = { 'Risk' => 'Risiko', 'Risk levels' => 'Risikograde', 'Risks' => 'Risikograde', + 'Rounding' => 'Rundung', + 'Rounding Gain' => 'Rundungserträge', + 'Rounding Loss' => 'Rundungsaufwendungen', 'Row' => 'Zeile', 'Row #1: amount has to be different from zero.' => 'Zeile #1: Der Wert darf nicht 0 sein.', 'Row number' => 'Zeilennummer', diff --git a/locale/en/all b/locale/en/all index bf275f642..e371413d7 100644 --- a/locale/en/all +++ b/locale/en/all @@ -1787,6 +1787,9 @@ $self->{texts} = { 'Revenues EU without UStId' => '', 'Review of Aging list' => '', 'Right' => '', + 'Rounding' => '', + 'Rounding Gain' => '', + 'Rounding Loss' => '', 'Row #1: amount has to be different from zero.' => '', 'Row number' => '', 'Row was created from current record' => '', diff --git a/sql/Pg-upgrade2/defaults_add_rnd_accno_ids.sql b/sql/Pg-upgrade2/defaults_add_rnd_accno_ids.sql new file mode 100644 index 000000000..deee948b1 --- /dev/null +++ b/sql/Pg-upgrade2/defaults_add_rnd_accno_ids.sql @@ -0,0 +1,6 @@ +-- @tag: defaults_add_rnd_accno_ids +-- @description: adds new columns 'rndgain_accno_id' and 'rndloss_accno_id' in table defaults, used to book roundings +-- @depends: release_3_1_0 +ALTER TABLE defaults ADD COLUMN rndgain_accno_id Integer; +ALTER TABLE defaults ADD COLUMN rndloss_accno_id Integer; + diff --git a/templates/webpages/am/edit_accounts.html b/templates/webpages/am/edit_accounts.html index 2bcaa0d30..12a284c25 100644 --- a/templates/webpages/am/edit_accounts.html +++ b/templates/webpages/am/edit_accounts.html @@ -27,6 +27,8 @@ $(function() { + +
diff --git a/templates/webpages/client_config/_default_accounts.html b/templates/webpages/client_config/_default_accounts.html index 51c9d2eed..bb92517f2 100644 --- a/templates/webpages/client_config/_default_accounts.html +++ b/templates/webpages/client_config/_default_accounts.html @@ -30,6 +30,16 @@ [% L.chart_picker('defaults.fxloss_accno_id', SELF.defaults.fxloss_accno_id, category='E,A', choose=1, style=style) %] + + [% LxERP.t8("Rounding Gain") %] + [% L.chart_picker('defaults.rndgain_accno_id', SELF.defaults.rndgain_accno_id, category='I,A', choose=1, style=style) %] + + + + [% LxERP.t8("Rounding Loss") %] + [% L.chart_picker('defaults.rndloss_accno_id', SELF.defaults.rndloss_accno_id, category='E,A', choose=1, style=style) %] + + [% LxERP.t8("Current assets account") %] [% L.chart_picker('defaults.ar_paid_accno_id', SELF.defaults.ar_paid_accno_id, type='AR_paid', choose=1, style=style) %] diff --git a/templates/webpages/is/form_footer.html b/templates/webpages/is/form_footer.html index d91527cf5..7980bcac4 100644 --- a/templates/webpages/is/form_footer.html +++ b/templates/webpages/is/form_footer.html @@ -107,6 +107,13 @@ [%- END %] +[%- IF rounding %] + + [% 'Rounding' | $T8 %] + [% LxERP.format_amount(rounding, 2) %] + +[%- END %] + [%# tax %] [% FOREACH item = taxaccounts_array %] [% SET description_ref = item _ '_description' %] @@ -124,7 +131,6 @@ [%- END %] [%- END %] - [% 'Total' | $T8 %] [% LxERP.format_amount(invtotal, 2) %] diff --git a/templates/webpages/is/form_header.html b/templates/webpages/is/form_header.html index c84b85bba..f9a8879c6 100644 --- a/templates/webpages/is/form_header.html +++ b/templates/webpages/is/form_header.html @@ -162,6 +162,8 @@ + + [%- IF show_exchangerate %] diff --git a/templates/webpages/oe/form_footer.html b/templates/webpages/oe/form_footer.html index 0956f204f..fbeb52992 100644 --- a/templates/webpages/oe/form_footer.html +++ b/templates/webpages/oe/form_footer.html @@ -107,6 +107,12 @@ [% 'Subtotal' | $T8 %] [% LxERP.format_amount(invsubtotal, 2) %] +[%- END %] +[%- IF rounding %] + + [% 'Rounding' | $T8 %] + [% LxERP.format_amount(rounding, 2) %] + [%- END %] [% tax %] -- 2.20.1