From d814a525b9f2fbc0bfae5591201d9185d6b1e8f4 Mon Sep 17 00:00:00 2001 From: "Martin Helmling mh@waldpark.octosoft.eu" Date: Sun, 17 Apr 2016 13:13:26 +0200 Subject: [PATCH] =?utf8?q?Gelieferte=20Mengen=20=C3=BCber=20Recordlinks=20?= =?utf8?q?ermitteln?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Die gelieferte Menge pro Position wird über die Recordlinks der Items zwischen Auftrag und Lieferschein(en) ermittelt. So werden auch gleiche Artikel auf unterschiedlichen Positionen getrennt behandelt. Ebenso ob ein Auftrag 'delivered' ist, d.h. ob alle Mengen vollständig in Lieferscheinen erfasst sind. Für nachträglich hinzugefügte Lieferscheine oder Lieferscheine ohne Item-Recodlinks wird ein Fallback-Verfahren durchgeführt, das beginnend von der ersten Auftragsposition versucht die Artikel in den Lieferscheinen zuzuordnen. --- SL/DO.pm | 221 ++++++++++++++++++++++++++++++---------------- bin/mozilla/io.pl | 32 ++----- bin/mozilla/oe.pl | 40 +++++++++ 3 files changed, 193 insertions(+), 100 deletions(-) diff --git a/SL/DO.pm b/SL/DO.pm index e3ffc2f31..a1bfd0017 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -536,56 +536,12 @@ sub mark_orders_if_delivered { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my @links = RecordLinks->get_links('dbh' => $dbh, - 'from_table' => 'oe', - 'to_table' => 'delivery_orders', - 'to_id' => $params{do_id}); + my %ship = $self->get_shipped_qty('do_id' => $form->{id}, 'delivered' => 1); - my $oe_id = @links ? $links[0]->{from_id} : undef; - - return $main::lxdebug->leave_sub() if (!$oe_id); - - my $all_units = AM->retrieve_all_units(); - - my $query = 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 = ?)|; - my $sth = prepare_execute_query($form, $dbh, $query, $oe_id); - - my %shipped = $self->get_shipped_qty('type' => $params{type}, - 'oe_id' => $oe_id,); - my %ordered = (); - - while (my $ref = $sth->fetchrow_hashref()) { - $ref->{baseqty} = $ref->{qty} * $all_units->{$ref->{unit}}->{factor} / $all_units->{$ref->{partunit}}->{factor}; - - if ($ordered{$ref->{parts_id}}) { - $ordered{$ref->{parts_id}}->{baseqty} += $ref->{baseqty}; - } else { - $ordered{$ref->{parts_id}} = $ref; - } - } - - $sth->finish(); - - map { $_->{baseqty} = $_->{qty} * $all_units->{$_->{unit}}->{factor} / $all_units->{$_->{partunit}}->{factor} } values %shipped; - - my $delivered = 1; - foreach my $part (values %ordered) { - if (!$shipped{$part->{parts_id}} || ($shipped{$part->{parts_id}}->{baseqty} < $part->{baseqty})) { - $delivered = 0; - last; - } - } - - if ($delivered) { - $query = qq|UPDATE oe - SET delivered = TRUE - WHERE id = ?|; - do_query($form, $dbh, $query, $oe_id); - $dbh->commit() if (!$params{dbh}); + foreach my $oe_id (keys %ship) { + do_query($form, $dbh,"UPDATE oe SET delivered = ".($ship{$oe_id}->{delivered}?"TRUE":"FALSE")." WHERE id = ?", $oe_id); } + $dbh->commit() if (!$params{dbh}); $main::lxdebug->leave_sub(); } @@ -1244,53 +1200,164 @@ sub transfer_in_out { $main::lxdebug->leave_sub(); } + sub get_shipped_qty { $main::lxdebug->enter_sub(); + # Drei Fälle: + # $params{oe_id} : Alle Lieferscheine zu diesem Auftrag durchsuchen und pro Auftragsposition die Mengen zurückgeben + # Wird zur Darstellung der gelieferten Mengen im Auftrag benötigt + # $params{do_id} : Alle Aufträge zu diesem Lieferschein durchsuchen und pro Lieferscheinposition die Mengen zurückgeben + # Wird für LaTeX benötigt um im Lieferschein pro Position die Mengen auszugeben + # $params{delivered}: Alle Aufträge zum Lieferschein $params{do_id} prüfen ob sie vollständiger ausgeliefert sind + # Wird für das Setzen des 'delivered' Flag in der Datenbank beim "save" des Lieferscheins benötigt + my $self = shift; my %params = @_; - Common::check_params(\%params, qw(type oe_id)); - my $myconfig = \%main::myconfig; my $form = $main::form; + my $dbh = $form->get_standard_dbh($myconfig); + my %ship = (); - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my @oe_ids; - my @links = RecordLinks->get_links('dbh' => $dbh, - 'from_table' => 'oe', - 'from_id' => $params{oe_id}, - 'to_table' => 'delivery_orders'); - my @values = map { $_->{to_id} } @links; + if ( $params{oe_id} ) { + push @oe_ids, $params{oe_id}; + } + elsif ($params{do_id}) { + my @links = RecordLinks->get_links( 'dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'to_id' => $params{do_id}); - if (!scalar @values) { - $main::lxdebug->leave_sub(); - return (); + @oe_ids = map { $_->{from_id} } @links; } - my $query = - qq|SELECT doi.parts_id, doi.qty, doi.unit, p.unit AS partunit - FROM delivery_order_items doi - LEFT JOIN delivery_orders o ON (doi.delivery_order_id = o.id) - LEFT JOIN parts p ON (doi.parts_id = p.id) - WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|; + if (scalar (@oe_ids) > 0 ) { + + #$main::lxdebug->message(LXDebug->DEBUG2(),"oeid=".$params{oe_id}." doid=".$params{do_id}); + my $all_units = AM->retrieve_all_units(); + my $query = qq|SELECT oi.id, oi.position, oi.parts_id, oi.qty, oi.unit, oi.trans_id, + p.unit AS partunit FROM orderitems oi + LEFT JOIN parts p ON (oi.parts_id = p.id) + WHERE trans_id IN (| . + join(', ', ('?') x scalar @oe_ids) . qq|) ORDER BY position ASC|; + + my $orderitems = selectall_hashref_query($form, $dbh, $query, @oe_ids); + foreach my $oe_entry (@{ $orderitems }) { + $oe_entry->{qty} *= AM->convert_unit($oe_entry->{unit}, $oe_entry->{partunit}, $all_units); + $oe_entry->{qty_notdelivered} = $oe_entry->{qty}; + + # Bei oe Modus auf jeden Fall einen Record anlegen + if ( $params{oe_id} ) { + $ship{$oe_entry->{position}} = { + 'qty_ordered' => $oe_entry->{qty} , + 'qty_notdelivered' => $oe_entry->{qty} + }; + } + } - my %ship = (); - my $entries = selectall_hashref_query($form, $dbh, $query, @values); - my $all_units = AM->retrieve_all_units(); + my @dolinks = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'from_id' => @oe_ids); - foreach my $entry (@{ $entries }) { - $entry->{qty} *= AM->convert_unit($entry->{unit}, $entry->{partunit}, $all_units); + my @do_ids = map { $_->{to_id} } @dolinks ; + if (scalar (@do_ids) == 0) { + $main::lxdebug->leave_sub(); + return %ship; + } - if (!$ship{$entry->{parts_id}}) { - $ship{$entry->{parts_id}} = $entry; - } else { - $ship{$entry->{parts_id}}->{qty} += $entry->{qty}; - } - } + my %oeitems_by_id = map { $_->{id} => $_ } @{ $orderitems }; + + + $query = qq|SELECT doi.parts_id, doi.id, doi.qty, doi.unit, doi.position, + doi.delivery_order_id, COALESCE(rlitem.from_id,0) as from_id, + p.unit AS partunit + FROM delivery_order_items doi + LEFT JOIN parts p ON (doi.parts_id = p.id) + LEFT JOIN record_links rlitem + ON (rlitem.to_id = doi.id AND rlitem.to_table='delivery_order_items') + WHERE doi.delivery_order_id IN (| . join(', ', ('?') x scalar @do_ids) . qq|)|; + + my $deliveryorderitems = selectall_hashref_query($form, $dbh, $query, @do_ids); + + # erst mal qty der links bearbeiten + foreach my $do_entry (@{ $deliveryorderitems }) { + $do_entry->{qty} *= AM->convert_unit($do_entry->{unit}, $do_entry->{partunit}, $all_units); + if ($do_entry->{from_id} > 0 ) { + # record link zwischen items vorhanden, kann auch von anderem Auftrag sein + my $oe_entry = $oeitems_by_id{$do_entry->{from_id}}; + if ( $oe_entry ) { + $oe_entry->{qty_notdelivered} -= $do_entry->{qty}; + # derzeit nur ein link pro do_item + $do_entry->{oe_entry} = $oe_entry; + } + } else { + $main::lxdebug->message(LXDebug->DEBUG2(),"no entry for=".$do_entry->{id}." part=".$do_entry->{parts_id}); + } + } + # nun den rest ohne links bearbeiten + foreach my $do_entry (@{ $deliveryorderitems }) { + next if $do_entry->{from_id} > 0; + next if $do_entry->{qty} == 0; + + foreach my $oe_entry (@{ $orderitems }) { + #$main::lxdebug->message(LXDebug->DEBUG2(),"do oe_entry ".$oe_entry." id=".$oe_entry->{id}." not del=".$oe_entry->{qty_notdelivered}); + next if $oe_entry->{qty_notdelivered} == 0; + if ( $do_entry->{parts_id} == $oe_entry->{parts_id} ) { + # zu viele geliefert auf andere position ? + if ( $oe_entry->{qty_notdelivered} < 0 ) { + $do_entry->{qty} += - $oe_entry->{qty_notdelivered}; + $oe_entry->{qty_notdelivered} = 0; + } else { + if ( $do_entry->{qty} < $oe_entry->{qty_notdelivered} ) { + $oe_entry->{qty_notdelivered} -= $do_entry->{qty}; + $do_entry->{qty} = 0; + } else { + $do_entry->{qty} -= $oe_entry->{qty_notdelivered}; + $oe_entry->{qty_notdelivered} = 0; + } + # derzeit nur ein link pro do_item + $do_entry->{oe_entry} = $oe_entry if !$do_entry->{oe_entry}; + } + } + last if $do_entry->{qty} <= 0; + } + } + if ( $params{oe_id} ) { + map { $ship{$_->{position}}->{qty_notdelivered} = $_->{qty_notdelivered}; } @{ $orderitems }; + } + elsif ($params{do_id} && $params{delivered}) { + map { + if ( !$ship{$_->{trans_id}} ) { + $ship{$_->{trans_id}} = { 'delivered' => 1 }; + } + $ship{$_->{trans_id}}->{delivered} = 0 if $_->{qty_notdelivered} > 0; + } @{ $orderitems }; + } + elsif ($params{do_id}) { + foreach my $do_entry (@{ $deliveryorderitems }) { + next if $params{do_id} != $do_entry->{delivery_order_id}; + my $position = $do_entry->{position}; + if ( $position > 0 && $do_entry->{oe_entry}) { + if ( !$ship{$position} ) { + $ship{$position} = { + 'qty_ordered' => $do_entry->{oe_entry}->{qty} , + 'qty_notdelivered' => $do_entry->{oe_entry}->{qty_notdelivered} + }; + } + else { + $ship{$position}->{qty_ordered} += $do_entry->{oe_entry}->{qty}; + $ship{$position}->{qty_notdelivered} += $do_entry->{oe_entry}->{qty_notdelivered}; + } + } + } + } + } $main::lxdebug->leave_sub(); - return %ship; } diff --git a/bin/mozilla/io.pl b/bin/mozilla/io.pl index f77a5f76e..b96f770f3 100644 --- a/bin/mozilla/io.pl +++ b/bin/mozilla/io.pl @@ -319,7 +319,8 @@ sub display_row { $ship_qty *= $all_units->{$form->{"partunit_$i"}}->{factor}; $ship_qty /= ( $all_units->{$form->{"unit_$i"}}->{factor} || 1 ); - $column_data{ship} = $form->format_amount(\%myconfig, $form->round_amount($ship_qty, 2) * 1) . ' ' . $form->{"unit_$i"}; + $column_data{ship} = $form->format_amount(\%myconfig, $form->round_amount($ship_qty, 2) * 1) . ' ' . $form->{"unit_$i"} + . $cgi->hidden(-name => "ship_$i", -value => $form->format_amount(\%myconfig, $form->{"ship_$i"}, $qty_dec)); my $ship_missing_qty = $form->{"qty_$i"} - $ship_qty; my $ship_missing_amount = $form->round_amount($ship_missing_qty * $form->{"sellprice_$i"} * (100 - $form->{"discount_$i"}) / 100 / $price_factor, 2); @@ -1770,36 +1771,21 @@ sub _update_ship { my $all_units = AM->retrieve_all_units(); - my %ship = DO->get_shipped_qty('type' => ($form->{type} eq 'purchase_order') ? 'purchase' : 'sales', - 'oe_id' => $form->{id},); + my %ship = DO->get_shipped_qty('oe_id' => $form->{id}); foreach my $i (1..$form->{rowcount}) { next unless ($form->{"id_${i}"}); $form->{"ship_$i"} = 0; - my $ship_entry = $ship{$form->{"id_$i"}}; + my $ship_entry = $ship{$i}; - next if (!$ship_entry || ($ship_entry->{qty} <= 0)); + next if (!$ship_entry || ($ship_entry->{qty_ordered} <= 0)); - my $rowqty = - ($form->{simple_save} ? $form->{"qty_$i"} : $form->parse_amount(\%myconfig, $form->{"qty_$i"})) - * $all_units->{$form->{"unit_$i"}}->{factor} - / $all_units->{$form->{"partunit_$i"}}->{factor}; - - $form->{"ship_$i"} = min($rowqty, $ship_entry->{qty}); - $ship_entry->{qty} -= $form->{"ship_$i"}; - } - - foreach my $i (1..$form->{rowcount}) { - next unless ($form->{"id_${i}"}); - - my $ship_entry = $ship{$form->{"id_$i"}}; - - next if (!$ship_entry || ($ship_entry->{qty} <= 0.01)); - - $form->{"ship_$i"} += $ship_entry->{qty}; - $ship_entry->{qty} = 0; + my $rowqty = $ship_entry->{qty_ordered} - $ship_entry->{qty_notdelivered}; + $rowqty *= $all_units->{$form->{"unit_$i"}}->{factor} / + $all_units->{$form->{"partunit_$i"}}->{factor} if !$form->{simple_save}; + $form->{"ship_$i"} = $rowqty; } $main::lxdebug->leave_sub(); diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl index 8f68f4d38..131f3f817 100644 --- a/bin/mozilla/oe.pl +++ b/bin/mozilla/oe.pl @@ -2116,6 +2116,34 @@ sub save_periodic_invoices_config { $::lxdebug->leave_sub(); } +sub _remove_full_delivered_rows { + + my @fields = map { s/_1$//; $_ } grep { m/_1$/ } keys %{ $::form }; + my @new_rows; + + my $removed_rows = 0; + my $row = 0; + while ($row < $::form->{rowcount}) { + $row++; + next unless $::form->{"id_$row"}; + my $base_factor = SL::DB::Manager::Unit->find_by(name => $::form->{"unit_$row"})->base_factor; + my $base_qty = $::form->parse_amount(\%::myconfig, $::form->{"qty_$row"}) * $base_factor; + my $ship_qty = $::form->parse_amount(\%::myconfig, $::form->{"ship_$row"}) * $base_factor; + #$main::lxdebug->message(LXDebug->DEBUG2(),"shipto=".$ship_qty." qty=".$base_qty); + + if (!$ship_qty || ($ship_qty < $base_qty)) { + $::form->{"qty_$row"} = $::form->format_amount(\%::myconfig, ($base_qty - $ship_qty) / $base_factor ); + $::form->{"ship_$row"} = 0; + push @new_rows, { map { $_ => $::form->{"${_}_${row}"} } @fields }; + + } else { + $removed_rows++; + } + } + $::form->redo_rows(\@fields, \@new_rows, scalar(@new_rows), $::form->{rowcount}); + $::form->{rowcount} -= $removed_rows; +} + sub _oe_remove_delivered_or_billed_rows { my (%params) = @_; @@ -2124,6 +2152,18 @@ sub _oe_remove_delivered_or_billed_rows { my $ord_quot = SL::DB::Order->new(id => $params{id})->load; return if !$ord_quot; + # Prüfung ob itemlinks existieren, falls ja dann neue Implementierung + + if ( $params{type} eq 'delivered' ) { + my $orderitem = SL::DB::Manager::OrderItem->get_first( where => [trans_id => $ord_quot->id]); + if ( $orderitem) { + my @links = $orderitem->linked_records(to => 'SL::DB::DeliveryOrderItem'); + if ( scalar(@links ) > 0 ) { + #$main::lxdebug->message(LXDebug->DEBUG2(),"item recordlinks vorhanden"); + return _remove_full_delivered_rows(); + } + } + } my %args = ( direction => 'to', to => $params{type} eq 'delivered' ? 'DeliveryOrder' : 'Invoice', -- 2.20.1