From 5a618706a76c255d27288805c6fdd1f113f73183 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Fri, 7 May 2021 10:02:33 +0200 Subject: [PATCH] orderitems um Attribut optional erweitert MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Optionale orderitems werden nicht in den Belegsumme aufaddiert Anpassung für Order-Controller und Druckvorlagen-System Weitere Anwender-Details s.a. Changelog --- SL/ARAP.pm | 3 +- SL/DB/Helper/FlattenToForm.pm | 2 +- SL/DB/Helper/PriceTaxCalculator.pm | 35 ++++--- SL/DB/MetaSetup/OrderItem.pm | 1 + SL/DB/Order.pm | 1 + SL/OE.pm | 21 ++-- doc/changelog | 15 +++ sql/Pg-upgrade2/orderitems_optional.sql | 5 + t/db_helper/price_tax_calculator.t | 99 +++++++++++++++++++ templates/print/RB/deutsch.tex | 1 + templates/print/RB/english.tex | 1 + templates/print/RB/sales_order.tex | 1 + templates/print/RB/sales_quotation.tex | 1 + .../webpages/order/tabs/_second_row.html | 3 + 14 files changed, 162 insertions(+), 27 deletions(-) create mode 100644 sql/Pg-upgrade2/orderitems_optional.sql diff --git a/SL/ARAP.pm b/SL/ARAP.pm index 1d044eaad..a2d786400 100644 --- a/SL/ARAP.pm +++ b/SL/ARAP.pm @@ -67,7 +67,8 @@ sub close_orders_if_billed { my $q_ordered = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit FROM orderitems oi LEFT JOIN parts p ON (oi.parts_id = p.id) - WHERE oi.trans_id = ?|; + WHERE oi.trans_id = ? + AND not oi.optional|; my $h_ordered = prepare_query($form, $dbh, $q_ordered); my @close_oe_ids; diff --git a/SL/DB/Helper/FlattenToForm.pm b/SL/DB/Helper/FlattenToForm.pm index ccd6ca045..f99993922 100644 --- a/SL/DB/Helper/FlattenToForm.pm +++ b/SL/DB/Helper/FlattenToForm.pm @@ -97,7 +97,7 @@ sub flatten_to_form { _copy($item->part, $form, '', "_${idx}", 0, qw(listprice)); _copy($item, $form, '', "_${idx}", 0, qw(description project_id ship serialnumber pricegroup_id ordnumber donumber cusordnumber unit subtotal longdescription price_factor_id marge_price_factor reqdate transdate - active_price_source active_discount_source)); + active_price_source active_discount_source optional)); _copy($item, $form, '', "_${idx}", $format_noround, qw(qty sellprice fxsellprice)); _copy($item, $form, '', "_${idx}", $format_amounts, qw(marge_total marge_percent lastcost)); _copy($item, $form, '', "_${idx}", $format_percent, qw(discount)); diff --git a/SL/DB/Helper/PriceTaxCalculator.pm b/SL/DB/Helper/PriceTaxCalculator.pm index 15850d558..bde711a79 100644 --- a/SL/DB/Helper/PriceTaxCalculator.pm +++ b/SL/DB/Helper/PriceTaxCalculator.pm @@ -44,7 +44,8 @@ sub calculate_prices_and_taxes { # set exchangerate in $data>{exchangerate} if ( ref($self) eq 'SL::DB::Order' ) { # orders store amount in the order currency - $data{exchangerate} = 1; + $data{exchangerate} = 1; + $data{allow_optional_items} = 1; } else { # invoices store amount in the default currency _get_exchangerate($self, \%data, %params); @@ -121,21 +122,21 @@ sub _calculate_item { } else { $tax_amount = $linetotal * $tax_rate; } - - if ($taxkey->tax->chart_id) { - $data->{taxes_by_chart_id}->{ $taxkey->tax->chart_id } ||= 0; - $data->{taxes_by_chart_id}->{ $taxkey->tax->chart_id } += $tax_amount; - $data->{taxes_by_tax_id}->{ $taxkey->tax_id } ||= 0; - $data->{taxes_by_tax_id}->{ $taxkey->tax_id } += $tax_amount; - } elsif ($tax_amount) { - die "tax_amount != 0 but no chart_id for taxkey " . $taxkey->id . " tax " . $taxkey->tax->id; - } - my $chart = $part->get_chart(type => $data->{is_sales} ? 'income' : 'expense', taxzone => $self->taxzone_id); - $data->{amounts}->{ $chart->id } ||= { taxkey => $taxkey->taxkey_id, tax_id => $taxkey->tax_id, amount => 0 }; - $data->{amounts}->{ $chart->id }->{amount} += $linetotal; - $data->{amounts}->{ $chart->id }->{amount} -= $tax_amount if $self->taxincluded; + unless ($data->{allow_optional_items} && $item->optional) { + if ($taxkey->tax->chart_id) { + $data->{taxes_by_chart_id}->{ $taxkey->tax->chart_id } ||= 0; + $data->{taxes_by_chart_id}->{ $taxkey->tax->chart_id } += $tax_amount; + $data->{taxes_by_tax_id}->{ $taxkey->tax_id } ||= 0; + $data->{taxes_by_tax_id}->{ $taxkey->tax_id } += $tax_amount; + } elsif ($tax_amount) { + die "tax_amount != 0 but no chart_id for taxkey " . $taxkey->id . " tax " . $taxkey->tax->id; + } + $data->{amounts}->{ $chart->id } ||= { taxkey => $taxkey->taxkey_id, tax_id => $taxkey->tax_id, amount => 0 }; + $data->{amounts}->{ $chart->id }->{amount} += $linetotal; + $data->{amounts}->{ $chart->id }->{amount} -= $tax_amount if $self->taxincluded; + } my $linetotal_cost = 0; if (!$linetotal) { @@ -150,8 +151,10 @@ sub _calculate_item { $item->marge_total( $linetotal_net - $linetotal_cost); $item->marge_percent($item->marge_total * 100 / $linetotal_net); - $self->marge_total( $self->marge_total + $item->marge_total); - $data->{lastcost_total} += $linetotal_cost; + unless ($data->{allow_optional_items} && $item->optional) { + $self->marge_total( $self->marge_total + $item->marge_total); + $data->{lastcost_total} += $linetotal_cost; + } } push @{ $data->{assembly_items} }, []; diff --git a/SL/DB/MetaSetup/OrderItem.pm b/SL/DB/MetaSetup/OrderItem.pm index 16478b149..afa64d815 100644 --- a/SL/DB/MetaSetup/OrderItem.pm +++ b/SL/DB/MetaSetup/OrderItem.pm @@ -23,6 +23,7 @@ __PACKAGE__->meta->columns( marge_price_factor => { type => 'numeric', default => 1, precision => 15, scale => 5 }, marge_total => { type => 'numeric', precision => 15, scale => 5 }, mtime => { type => 'timestamp' }, + optional => { type => 'boolean', default => 'false' }, ordnumber => { type => 'text' }, parts_id => { type => 'integer' }, position => { type => 'integer', not_null => 1 }, diff --git a/SL/DB/Order.pm b/SL/DB/Order.pm index dc4bbebd8..b3741df98 100644 --- a/SL/DB/Order.pm +++ b/SL/DB/Order.pm @@ -393,6 +393,7 @@ sub new_from { marge_percent marge_price_factor marge_total ordnumber parts_id price_factor price_factor_id pricegroup_id project_id qty reqdate sellprice serialnumber ship subtotal transdate unit + optional )), custom_variables => \@custom_variables, ); diff --git a/SL/OE.pm b/SL/OE.pm index 1b640e37b..6b4831267 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -1366,7 +1366,7 @@ sub order_details { partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription - price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt); + price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt optional); push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs }; @@ -1433,6 +1433,7 @@ sub order_details { push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor}; push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description}; push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{optional} }, $form->{"optional_$i"}; my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); my ($dec) = ($sellprice =~ /\.(\d+)/); @@ -1472,7 +1473,7 @@ sub order_details { $form->{non_separate_subtotal} += $linetotal; } - $form->{ordtotal} += $linetotal; + $form->{ordtotal} += $linetotal unless $form->{"optional_$i"}; $form->{nodiscount_total} += $nodiscount_linetotal; $form->{discount_total} += $discount; @@ -1520,14 +1521,16 @@ sub order_details { map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"}); - if ($form->{taxincluded}) { + unless ($form->{"optional_$i"}) { + if ($form->{taxincluded}) { - # calculate tax - $taxamount = $linetotal * $taxrate / (1 + $taxrate); - $taxbase = $linetotal / (1 + $taxrate); - } else { - $taxamount = $linetotal * $taxrate; - $taxbase = $linetotal; + # calculate tax + $taxamount = $linetotal * $taxrate / (1 + $taxrate); + $taxbase = $linetotal / (1 + $taxrate); + } else { + $taxamount = $linetotal * $taxrate; + $taxbase = $linetotal; + } } if ($taxamount != 0) { diff --git a/doc/changelog b/doc/changelog index ceea04d54..421d21e53 100644 --- a/doc/changelog +++ b/doc/changelog @@ -37,6 +37,21 @@ Mittelgroße neue Features: Kleinere neue Features und Detailverbesserungen: + - Angebote und Aufträge im Ein- und Verkauf können optionale Positionen enthalten. + Optionale Positionen werden in der zweiten Zeile der Position aktiviert. + Die einzelne Position wird dann berechnet und erscheint im Ausdruck mit dem + berechnetem Preis, die Position wird aber nicht in der Gesamtsumme des Belegs + aufgenommen. Dies gilt auch für die Gesamt-Marge und den Gesamt-Ertrag des Belegs. + Innerhalb der Druckvorlagen steht das Attribut mit <%optional%> als Variable zu Verfügung. + Beim Status setzen eines Auftrags (offen oder geschlossen) werden optionale Position + ignoriert. D.h. ein Auftrag gilt als geschlossen, wenn alle nicht optionalen + Positionen fakturiert worden sind. Das Attribut optional steht auch nur in + den Angeboten/Aufträgen zu Verfügung. Sobald über den Workflow ein neuer Beleg + erstellt wird, wird die vorher optionale Position zu einer normalen Position + und wird dann auch entsprechend bei dem Rechnungsbeleg mit fakturiert und im + Druckvorlagen-System entfällt das Attribut <%optional%>. + Entsprechend exemplarisch im aktuellen Druckvorlagensatz RB ergänzt. + - Lagerbestandsbericht: Die Resultate pro Seite können im Bericht eingestellt werden - Es gibt eine PDF-Druckvorschau für die Standard-Druckvorlage bei Angeboten und Aufträgen im Einkauf und Verkauf ohne ein vorheriges Dialogmenü (Druckvorlage diff --git a/sql/Pg-upgrade2/orderitems_optional.sql b/sql/Pg-upgrade2/orderitems_optional.sql new file mode 100644 index 000000000..3b4ddda5d --- /dev/null +++ b/sql/Pg-upgrade2/orderitems_optional.sql @@ -0,0 +1,5 @@ +-- @tag: orderitems_optional +-- @description: Optionale Artikel im Angebot und Auftrag +-- @depends: release_3_5_6_1 +ALTER TABLE orderitems ADD COLUMN optional BOOLEAN default FALSE; + diff --git a/t/db_helper/price_tax_calculator.t b/t/db_helper/price_tax_calculator.t index 5050ecd5f..a4f6ddcaf 100644 --- a/t/db_helper/price_tax_calculator.t +++ b/t/db_helper/price_tax_calculator.t @@ -98,6 +98,15 @@ sub new_invoice { %params, ); } +sub new_order { + my %params = @_; + + return create_sales_order( + transdate => $transdate, + taxzone_id => $taxzone->id, + %params, + ); +} sub new_item { my (%params) = @_; @@ -109,6 +118,16 @@ sub new_item { %params, ); } +sub new_order_item { + my (%params) = @_; + + my $part = delete($params{part}) || $parts[0]; + + return create_order_item( + part => $part, + %params, + ); +} sub test_default_invoice_one_item_19_tax_not_included() { reset_state(); @@ -553,6 +572,85 @@ sub test_default_invoice_one_item_19_tax_not_included_rounding_discount_big_qty_ rounding => 0, }, "${title}: calculated data"); } +sub test_default_order_two_items_19_one_optional() { + reset_state(); + + my $item = new_order_item(qty => 2.5); + my $item_optional = new_order_item(qty => 2.5, optional => 1); + + my $order = new_order( + taxincluded => 0, + orderitems => [ $item, $item_optional ], + ); + + my $taxkey = $item->part->get_taxkey(date => $transdate, is_sales => 1, taxzone => $order->taxzone_id); + + # sellprice 2.34 * qty 2.5 = 5.85 + # 19%(5.85) = 1.1115; rounded = 1.11 + # total rounded = 6.96 + + # lastcost 1.93 * qty 2.5 = 4.825; rounded 4.83 + # line marge_total = 1.02 + # line marge_percent = 17.4358974358974 + + my $title = 'default order, two item, one item optional, 19% tax not included'; + my %data = $order->calculate_prices_and_taxes; + + is($item->marge_total, 1.02, "${title}: item marge_total"); + is($item->marge_percent, 17.4358974358974, "${title}: item marge_percent"); + is($item->marge_price_factor, 1, "${title}: item marge_price_factor"); + + # optional items have a linetotal and marge, but ... + is($item_optional->marge_total, 1.02, "${title}: item optional marge_total"); + is($item_optional->marge_percent, 17.4358974358974, "${title}: item optional marge_percent"); + is($item_optional->marge_price_factor, 1, "${title}: item optional marge_price_factor"); + + # ... should not be calculated for the record sum + is($order->netamount, 5.85, "${title}: netamount"); + is($order->amount, 6.96, "${title}: amount"); + is($order->marge_total, 1.02, "${title}: marge_total"); + is($order->marge_percent, 17.4358974358974, "${title}: marge_percent"); + is($order->orderitems->[1]->optional, 1, "${title}: second order item has attribute optional"); + # diag explain $order->orderitems->[1]->optional; + # diag explain \%data; + is_deeply(\%data, { + allocated => {}, + amounts => { + $buchungsgruppe->income_accno_id($taxzone) => { + amount => 5.85, + tax_id => $tax->id, + taxkey => 3, + }, + }, + amounts_cogs => {}, + assembly_items => [ + [], + [], + ], + exchangerate => 1, + taxes_by_chart_id => { + $tax->chart_id => 1.11, + }, + taxes_by_tax_id => { + $tax->id => 1.1115, + }, + items => [ + { linetotal => 5.85, + linetotal_cost => 4.83, + sellprice => 2.34, + tax_amount => 1.1115, + taxkey_id => $taxkey->id, + }, + { linetotal => 5.85, + linetotal_cost => 4.83, + sellprice => 2.34, + tax_amount => 1.1115, + taxkey_id => $taxkey->id, + }, + ], + rounding => 0, + }, "${title}: calculated data"); +} Support::TestSetup::login(); @@ -566,6 +664,7 @@ test_default_invoice_three_items_sellprice_rounding_discount(); test_default_invoice_one_item_19_tax_not_included_rounding_discount(); test_default_invoice_one_item_19_tax_not_included_rounding_discount_huge_qty(); test_default_invoice_one_item_19_tax_not_included_rounding_discount_big_qty_low_sellprice(); +test_default_order_two_items_19_one_optional(); clear_up(); done_testing(); diff --git a/templates/print/RB/deutsch.tex b/templates/print/RB/deutsch.tex index d8384302f..e7ade80a3 100644 --- a/templates/print/RB/deutsch.tex +++ b/templates/print/RB/deutsch.tex @@ -56,6 +56,7 @@ \newcommand{\auftragerteilt}{Auftrag erteilt:} \newcommand{\angebotortdatum}{Wir nehmen das vorstehende Angebot an.} \newcommand{\abweichendeLieferadresse}{abweichende Lieferadresse} +\newcommand{\optional}{Optionale Position nach Absprache} % auftragbestätigung (sales_order) \newcommand{\auftragsbestaetigung} {Auftragsbestätigung} diff --git a/templates/print/RB/english.tex b/templates/print/RB/english.tex index 326d0411f..a3736c4bd 100644 --- a/templates/print/RB/english.tex +++ b/templates/print/RB/english.tex @@ -68,6 +68,7 @@ \newcommand{\den} {Date} \newcommand{\unterschrift} {Signature} \newcommand{\stempel} {Company stamp} +\newcommand{\optional}{Optional position by arrangement} % lieferschein (sales_delivery_order) \newcommand{\lieferschein} {Delivery order} diff --git a/templates/print/RB/sales_order.tex b/templates/print/RB/sales_order.tex index ad0de3eb8..dfec85cb1 100644 --- a/templates/print/RB/sales_order.tex +++ b/templates/print/RB/sales_order.tex @@ -151,6 +151,7 @@ <%if serialnumber%> && \scriptsize \seriennummer: <%serialnumber%>\\<%end serialnumber%> <%if ean%> && \scriptsize \ean: <%ean%>\\<%end ean%> <%if projectnumber%> && \scriptsize \projektnummer: <%projectnumber%>\\<%end projectnumber%> + <%if optional%> && \scriptsize \optional \\<%end%> <%if customer_make%> <%foreach customer_make%> \ifthenelse{\equal{<%customer_make%>}{<%name%>}}{&& \kundenartnr: <%customer_model%>\\}{} diff --git a/templates/print/RB/sales_quotation.tex b/templates/print/RB/sales_quotation.tex index 4010a7fa3..e8661cf2e 100644 --- a/templates/print/RB/sales_quotation.tex +++ b/templates/print/RB/sales_quotation.tex @@ -147,6 +147,7 @@ <%if serialnumber%> && \scriptsize \seriennummer: <%serialnumber%>\\<%end serialnumber%> <%if ean%> && \scriptsize \ean: <%ean%>\\<%end ean%> <%if projectnumber%> && \scriptsize \projektnummer: <%projectnumber%>\\<%end projectnumber%> + <%if optional%> && \scriptsize \optional \\<%end%> <%if customer_make%> <%foreach customer_make%> \ifthenelse{\equal{<%customer_make%>}{<%name%>}}{&& \kundenartnr: <%customer_model%>\\}{} diff --git a/templates/webpages/order/tabs/_second_row.html b/templates/webpages/order/tabs/_second_row.html index 4be14f7f1..8b4d83ad6 100644 --- a/templates/webpages/order/tabs/_second_row.html +++ b/templates/webpages/order/tabs/_second_row.html @@ -38,6 +38,9 @@ [%- ITEM.part.onhand_as_number -%] [%- ITEM.part.unit -%]   + [%- 'Optional' | $T8 %]  + [%- L.yes_no_tag("order.orderitems[].optional", ITEM.optional + class="recalc") %]  -- 2.20.1