From a8ab093107924691935126205db483112aa7f50e Mon Sep 17 00:00:00 2001 From: "Martin Helmling mh@waldpark.octosoft.eu" Date: Mon, 18 Apr 2016 19:00:46 +0200 Subject: [PATCH] =?utf8?q?Gelieferte=20Mengen=20=C3=BCber=20Recordlinks=20?= =?utf8?q?ermitteln(2)?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Für den Verschönerungsverein einige Stilanpassungen --- SL/DO.pm | 227 +++++++++++++++++++++++++++---------------------------- 1 file changed, 113 insertions(+), 114 deletions(-) diff --git a/SL/DO.pm b/SL/DO.pm index ee62af223..acc2999f3 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -536,7 +536,7 @@ sub mark_orders_if_delivered { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my %ship = $self->get_shipped_qty('do_id' => $form->{id}, 'delivered' => 1); + my %ship = $self->get_shipped_qty('dbh' => $dbh, 'do_id' => $form->{id}, 'delivered' => 1); foreach my $oe_id (keys %ship) { do_query($form, $dbh,"UPDATE oe SET delivered = ".($ship{$oe_id}->{delivered}?"TRUE":"FALSE")." WHERE id = ?", $oe_id); @@ -1215,17 +1215,19 @@ sub get_shipped_qty { my $self = shift; my %params = @_; + # Eigentlich unkritisch: wenn keine der Parameter gesetzt ist kommt ein leerer Hash zurück + croak ("Need at least one parameter oe_id or do_id") unless $params{oe_id} || $params{do_id}; + my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); my %ship = (); my @oe_ids; - if ( $params{oe_id} ) { + if ($params{oe_id} ) { push @oe_ids, $params{oe_id}; - } - elsif ($params{do_id}) { + } elsif ($params{do_id}) { my @links = RecordLinks->get_links( 'dbh' => $dbh, 'from_table' => 'oe', 'to_table' => 'delivery_orders', @@ -1235,127 +1237,124 @@ sub get_shipped_qty { } 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 @dolinks = RecordLinks->get_links('dbh' => $dbh, - 'from_table' => 'oe', - 'to_table' => 'delivery_orders', - 'from_id' => @oe_ids); - - my @do_ids = map { $_->{to_id} } @dolinks ; - if (scalar (@do_ids) == 0) { - $main::lxdebug->leave_sub(); - return %ship; + my $all_units = AM->retrieve_all_units(); + my $placeholders = join(', ', ('?') x scalar @oe_ids); + 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 (${placeholders}) + 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 %oeitems_by_id = map { $_->{id} => $_ } @{ $orderitems }; - + my @dolinks = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'from_id' => @oe_ids); - $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 @do_ids = map { $_->{to_id} } @dolinks ; + if (scalar (@do_ids) == 0) { + $main::lxdebug->leave_sub(); + return %ship; + } - my $deliveryorderitems = selectall_hashref_query($form, $dbh, $query, @do_ids); + my %oeitems_by_id = map { $_->{id} => $_ } @{ $orderitems }; + + + $placeholders = join(', ', ('?') x scalar @do_ids); + $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 (${placeholders})|; + + 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; - # 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; - } + foreach my $oe_entry (@{ $orderitems }) { + 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 { - $main::lxdebug->message(LXDebug->DEBUG2(),"no entry for=".$do_entry->{id}." part=".$do_entry->{parts_id}); + 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} ) { + $ship{$_->{position}}->{qty_notdelivered} = $_->{qty_notdelivered} for @{ $orderitems }; + } + elsif ($params{do_id} && $params{delivered}) { + foreach my $oe_entry (@{ $orderitems }) { + if ( !$ship{$oe_entry->{trans_id}} ) { + $ship{$oe_entry->{trans_id}} = { 'delivered' => 1 }; + } + $ship{$oe_entry->{trans_id}}->{delivered} = 0 if $oe_entry->{qty_notdelivered} > 0; } - # nun den rest ohne links bearbeiten + } + elsif ($params{do_id}) { 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; + 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}; } - - } - 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; -- 2.20.1