X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=b51fc0f1bf15ee199d7003cdf54f53d902b3c7db;hb=eae5f8bdbca22f1f8c7ba4439dafbe3b89b6a143;hp=ee62af2235ba570226f7151cfa1cb95c7b4a2a1c;hpb=bc40bcabc425b99f3b79a5544684a6fe8674adfe;p=kivitendo-erp.git

diff --git a/SL/DO.pm b/SL/DO.pm
index ee62af223..b51fc0f1b 100644
--- a/SL/DO.pm
+++ b/SL/DO.pm
@@ -25,7 +25,8 @@
 # GNU General Public License for more details.
 # You should have received a copy of the GNU General Public License
 # along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
 #======================================================================
 #
 # Delivery Order entry module
@@ -33,6 +34,7 @@
 
 package DO;
 
+use Carp;
 use List::Util qw(max);
 use YAML;
 
@@ -46,6 +48,7 @@ use SL::HTML::Restrict;
 use SL::RecordLinks;
 use SL::IC;
 use SL::TransNumber;
+use SL::DB;
 use SL::Util qw(trim);
 
 use strict;
@@ -180,6 +183,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);
   }
@@ -232,6 +262,16 @@ sub transactions {
 }
 
 sub save {
+  my ($self) = @_;
+  $main::lxdebug->enter_sub();
+
+  my $rc = SL::DB->client->with_transaction(\&_save, $self);
+
+  $main::lxdebug->leave_sub();
+  return $rc;
+}
+
+sub _save {
   $main::lxdebug->enter_sub();
 
   my ($self)   = @_;
@@ -239,8 +279,7 @@ sub save {
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
-  # connect to database, turn off autocommit
-  my $dbh = $form->get_standard_dbh($myconfig);
+  my $dbh = SL::DB->client->dbh;
   my $restricter = SL::HTML::Restrict->create;
 
   my ($query, @values, $sth, $null);
@@ -284,7 +323,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});
@@ -510,8 +549,6 @@ SQL
                                   'type'  => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase',
                                   'dbh'   => $dbh,);
 
-  my $rc = $dbh->commit();
-
   $form->{saved_donumber} = $form->{donumber};
   $form->{saved_ordnumber} = $form->{ordnumber};
   $form->{saved_cusordnumber} = $form->{cusordnumber};
@@ -520,7 +557,7 @@ SQL
 
   $main::lxdebug->leave_sub();
 
-  return $rc;
+  return 1;
 }
 
 sub mark_orders_if_delivered {
@@ -534,14 +571,16 @@ sub mark_orders_if_delivered {
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
-  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+  SL::DB->client->with_transaction(sub {
+    my $dbh      = $params{dbh} || SL::DB->client->dbh;
 
-  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);
-  }
-  $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);
+    }
+    1;
+  }) or do { die SL::DB->client->error };
 
   $main::lxdebug->leave_sub();
 }
@@ -562,13 +601,15 @@ sub close_orders {
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
-  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+  SL::DB->client->with_transaction(sub {
+    my $dbh      = $params{dbh} || SL::DB->client->dbh;
 
-  my $query    = qq|UPDATE delivery_orders SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar(@{ $params{ids} })) . qq|)|;
+    my $query    = qq|UPDATE delivery_orders SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar(@{ $params{ids} })) . qq|)|;
 
-  do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} });
+    do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} });
+    1;
+  }) or die { SL::DB->client->error };
 
-  $dbh->commit() unless ($params{dbh});
   $form->new_lastmtime('delivery_orders');
 
   $main::lxdebug->leave_sub();
@@ -705,15 +746,14 @@ sub retrieve {
     $sth   = prepare_execute_query($form, $dbh, $query, $form->{id});
 
     $ref   = $sth->fetchrow_hashref("NAME_lc");
-    delete $ref->{id};
-    map { $form->{$_} = $ref->{$_} } keys %$ref;
+    $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
     $sth->finish();
 
-    if ($form->{shipto_id}) {
+    if ($ref->{shipto_id}) {
       my $cvars = CVar->get_custom_variables(
         dbh      => $dbh,
         module   => 'ShipTo',
-        trans_id => $form->{shipto_id},
+        trans_id => $ref->{shipto_id},
       );
       $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
     }
@@ -739,7 +779,7 @@ sub retrieve {
   # stuff different from the whole will not be overwritten, but saved with a suffix.
   $query =
     qq|SELECT doi.id AS delivery_order_items_id,
-         p.partnumber, p.assembly, p.listprice, doi.description, doi.qty,
+         p.partnumber, p.part_type, p.listprice, doi.description, doi.qty,
          doi.sellprice, doi.parts_id AS id, doi.unit, doi.discount, p.notes AS partnotes,
          doi.reqdate, doi.project_id, doi.serialnumber, doi.lastcost,
          doi.ordnumber, doi.transdate, doi.cusordnumber, doi.longdescription,
@@ -886,7 +926,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 = "";
@@ -1027,9 +1067,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};
 
@@ -1211,21 +1251,23 @@ sub get_shipped_qty {
   #                  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   = @_;
 
+  # 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 +1277,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;