X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=4ef285461b645d6fe745f7b8c3502f6e978715c2;hb=2134f89abb07c18b07b3121e80d243af03ede0e0;hp=b098f71e001a224f222680e6b084e960d729cc9d;hpb=88dea78ecdb77761f71c5016ca97e53b2d55d9d3;p=kivitendo-erp.git

diff --git a/SL/DO.pm b/SL/DO.pm
index b098f71e0..4ef285461 100644
--- a/SL/DO.pm
+++ b/SL/DO.pm
@@ -33,6 +33,7 @@
 
 package DO;
 
+use Carp;
 use List::Util qw(max);
 use YAML;
 
@@ -111,12 +112,12 @@ sub transactions {
 
   } elsif ($form->{$vc}) {
     push @where,  qq|ct.name ILIKE ?|;
-    push @values, '%' . trim($form->{$vc}) . '%';
+    push @values, like($form->{$vc});
   }
 
   if ($form->{"cp_name"}) {
     push @where, "(cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
-    push @values, ('%' . trim($form->{"cp_name"}) . '%')x2;
+    push @values, (like($form->{"cp_name"}))x2;
   }
 
   foreach my $item (qw(employee_id salesman_id)) {
@@ -132,7 +133,7 @@ sub transactions {
   foreach my $item (qw(donumber ordnumber cusordnumber transaction_description)) {
     next unless ($form->{$item});
     push @where,  qq|dord.$item ILIKE ?|;
-    push @values, '%' . trim($form->{$item}) . '%';
+    push @values, like($form->{$item});
   }
 
   if (($form->{open} || $form->{closed}) &&
@@ -147,7 +148,7 @@ sub transactions {
 
   if ($form->{serialnumber}) {
     push @where, 'dord.id IN (SELECT doi.delivery_order_id FROM delivery_order_items doi WHERE doi.serialnumber LIKE ?)';
-    push @values, '%' . trim($form->{serialnumber}) . '%';
+    push @values, like($form->{serialnumber});
   }
 
   if($form->{transdatefrom}) {
@@ -180,6 +181,33 @@ sub transactions {
     push @values, conv_date($form->{insertdateto});
   }
 
+  if ($form->{parts_partnumber}) {
+    push @where, <<SQL;
+      EXISTS (
+        SELECT delivery_order_items.delivery_order_id
+        FROM delivery_order_items
+        LEFT JOIN parts ON (delivery_order_items.parts_id = parts.id)
+        WHERE (delivery_order_items.delivery_order_id = dord.id)
+          AND (parts.partnumber ILIKE ?)
+        LIMIT 1
+      )
+SQL
+    push @values, like($form->{parts_partnumber});
+  }
+
+  if ($form->{parts_description}) {
+    push @where, <<SQL;
+      EXISTS (
+        SELECT delivery_order_items.delivery_order_id
+        FROM delivery_order_items
+        WHERE (delivery_order_items.delivery_order_id = dord.id)
+          AND (delivery_order_items.description ILIKE ?)
+        LIMIT 1
+      )
+SQL
+    push @values, like($form->{parts_description});
+  }
+
   if (@where) {
     $query .= " WHERE " . join(" AND ", map { "($_)" } @where);
   }
@@ -263,6 +291,11 @@ sub save {
   if ($form->{id}) {
 
     # only delete shipto complete
+    $query = qq|DELETE FROM custom_variables
+                WHERE (config_id IN (SELECT id        FROM custom_variable_configs WHERE (module = 'ShipTo')))
+                  AND (trans_id  IN (SELECT shipto_id FROM shipto                  WHERE (module = 'DO') AND (trans_id = ?)))|;
+    do_query($form, $dbh, $query, $form->{id});
+
     $query = qq|DELETE FROM shipto WHERE trans_id = ? AND module = 'DO'|;
     do_query($form, $dbh, $query, conv_i($form->{id}));
 
@@ -279,7 +312,7 @@ sub save {
   my $items_reqdate;
 
   $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
-  my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
+  my %price_factors = map { $_->{id} => $_->{factor} *1 } @{ $form->{ALL_PRICE_FACTORS} };
   my $price_factor;
 
   my %part_id_map = map { $_ => 1 } grep { $_ } map { $form->{"id_$_"} } (1 .. $form->{rowcount});
@@ -531,56 +564,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 $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;
-    }
-  }
+  my %ship = $self->get_shipped_qty('dbh' => $dbh, 'do_id' => $form->{id}, 'delivered' => 1);
 
-  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();
 }
@@ -748,6 +737,15 @@ sub retrieve {
     map { $form->{$_} = $ref->{$_} } keys %$ref;
     $sth->finish();
 
+    if ($form->{shipto_id}) {
+      my $cvars = CVar->get_custom_variables(
+        dbh      => $dbh,
+        module   => 'ShipTo',
+        trans_id => $form->{shipto_id},
+      );
+      $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
+    }
+
     # get printed, emailed and queued
     $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
     $sth   = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
@@ -916,7 +914,7 @@ sub order_details {
   push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
 
   $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
-  my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
+  my %price_factors = map { $_->{id} => $_->{factor} *1 } @{ $form->{ALL_PRICE_FACTORS} };
 
   my $totalweight = 0;
   my $sameitem = "";
@@ -1057,9 +1055,9 @@ sub order_details {
   $h_pg->finish();
   $h_bin_wh->finish();
 
+  $form->{department}    = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
   $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
   $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
-  $form->{department}    = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
 
   $form->{username} = $myconfig->{name};
 
@@ -1230,53 +1228,163 @@ 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));
+  # 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      = $params{dbh} || $form->get_standard_dbh($myconfig);
+  my %ship = ();
 
-  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;
+  my @oe_ids;
 
-  if (!scalar @values) {
-    $main::lxdebug->leave_sub();
-    return ();
+  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});
+
+    @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 ) {
+    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 %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 };
+
+
+    $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;
+
+      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 {
+            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;
+      }
+    }
+    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;
 }