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

diff --git a/SL/DO.pm b/SL/DO.pm
index 236a173c2..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;
 
@@ -46,6 +47,7 @@ use SL::HTML::Restrict;
 use SL::RecordLinks;
 use SL::IC;
 use SL::TransNumber;
+use SL::Util qw(trim);
 
 use strict;
 
@@ -110,12 +112,12 @@ sub transactions {
 
   } elsif ($form->{$vc}) {
     push @where,  qq|ct.name ILIKE ?|;
-    push @values, '%' . $form->{$vc} . '%';
+    push @values, like($form->{$vc});
   }
 
   if ($form->{"cp_name"}) {
     push @where, "(cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
-    push @values, ('%' . $form->{"cp_name"} . '%')x2;
+    push @values, (like($form->{"cp_name"}))x2;
   }
 
   foreach my $item (qw(employee_id salesman_id)) {
@@ -131,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, '%' . $form->{$item} . '%';
+    push @values, like($form->{$item});
   }
 
   if (($form->{open} || $form->{closed}) &&
@@ -146,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, '%' . $form->{serialnumber} . '%';
+    push @values, like($form->{serialnumber});
   }
 
   if($form->{transdatefrom}) {
@@ -179,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);
   }
@@ -262,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}));
 
@@ -278,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});
@@ -293,7 +327,6 @@ sub save {
     UPDATE delivery_order_items SET
        delivery_order_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
        sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?,
-       ordnumber = ?, transdate = ?, cusordnumber = ?,
        lastcost = ? , price_factor_id = ?, price_factor = (SELECT factor FROM price_factors where id = ?),
        marge_price_factor = ?, pricegroup_id = ?, active_price_source = ?, active_discount_source = ?
     WHERE id = ?
@@ -368,8 +401,6 @@ SQL
                $form->{"sellprice_$i"}, $form->{"discount_$i"} / 100,
                $form->{"unit_$i"}, conv_date($items_reqdate), conv_i($form->{"project_id_$i"}),
                $form->{"serialnumber_$i"},
-               $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
-               $form->{"cusordnumber_$i"},
                $form->{"lastcost_$i"},
                conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
                conv_i($form->{"marge_price_factor_$i"}),
@@ -393,9 +424,9 @@ SQL
         do_query($form, $dbh, $query, conv_i($sinfo->{"delivery_order_items_stock_id"}),
                   conv_i($form->{"delivery_order_items_id_$i"}), $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}),
                   conv_i($sinfo->{bin_id}));
-       $h_item_stock_id->finish();
-      # write back the id to the form (important if only transfer was clicked (id fk for invoice)
-      $form->{"stock_${in_out}_$i"} = YAML::Dump($stock_info);
+        $h_item_stock_id->finish();
+        # write back the id to the form (important if only transfer was clicked (id fk for invoice)
+        $form->{"stock_${in_out}_$i"} = YAML::Dump($stock_info);
       }
       @values = ($form->{"delivery_order_items_id_$i"}, $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}),
                  conv_i($sinfo->{bin_id}), $sinfo->{chargenumber}, conv_date($sinfo->{bestbefore}),
@@ -480,6 +511,8 @@ SQL
              conv_i($form->{id}));
   do_query($form, $dbh, $query, @values);
 
+  $form->new_lastmtime('delivery_orders');
+
   $form->{name} = $form->{ $form->{vc} };
   $form->{name} =~ s/--$form->{"$form->{vc}_id"}//;
 
@@ -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};
+  my %ship = $self->get_shipped_qty('dbh' => $dbh, 'do_id' => $form->{id}, 'delivered' => 1);
 
-    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();
 }
@@ -608,6 +597,7 @@ sub close_orders {
   do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} });
 
   $dbh->commit() unless ($params{dbh});
+  $form->new_lastmtime('delivery_orders');
 
   $main::lxdebug->leave_sub();
 }
@@ -688,6 +678,7 @@ sub retrieve {
          dord.closed, dord.reqdate, dord.department_id, dord.cusordnumber,
          d.description AS department, dord.language_id,
          dord.shipto_id,
+         dord.itime, dord.mtime,
          dord.globalproject_id, dord.delivered, dord.transaction_description,
          dord.taxzone_id, dord.taxincluded, dord.payment_id, (SELECT cu.name FROM currencies cu WHERE cu.id=dord.currency_id) AS currency,
          dord.delivery_term_id, dord.itime::DATE AS insertdate
@@ -722,7 +713,8 @@ sub retrieve {
     }
   }
   $sth->finish();
-
+  $form->{mtime}   ||= $form->{itime};
+  $form->{lastmtime} = $form->{mtime};
   $form->{donumber_array} =~ s/\s*$//g;
   $form->{ordnumber_array} =~ s/ //;
   $form->{ordnumber_array} =~ s/\s*$//g;
@@ -745,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}));
@@ -913,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 = "";
@@ -1054,6 +1055,7 @@ 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};
 
@@ -1226,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;
 }