X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=28eb85436718d973686be97d99944b3b018e31ec;hb=611a3dd324e413b4653c850546e50a0a7336a8bc;hp=acbd8dab65d748d790daa74c470bc8e8f3695a48;hpb=ce2e13e64bf9de995020cf18fd7ef36f199f018d;p=kivitendo-erp.git

diff --git a/SL/DO.pm b/SL/DO.pm
index acbd8dab6..28eb85436 100644
--- a/SL/DO.pm
+++ b/SL/DO.pm
@@ -39,6 +39,7 @@ use YAML;
 use SL::AM;
 use SL::Common;
 use SL::DBUtils;
+use SL::RecordLinks;
 
 sub transactions {
   $main::lxdebug->enter_sub();
@@ -62,14 +63,12 @@ sub transactions {
          dord.transaction_description,
          pr.projectnumber AS globalprojectnumber,
          e.name AS employee,
-         sm.name AS salesman,
-         oe.id AS oe_id
+         sm.name AS salesman
        FROM delivery_orders dord
        LEFT JOIN $vc ct ON (dord.${vc}_id = ct.id)
        LEFT JOIN employee e ON (dord.employee_id = e.id)
        LEFT JOIN employee sm ON (dord.salesman_id = sm.id)
-       LEFT JOIN project pr ON (dord.globalproject_id = pr.id)
-       LEFT JOIN oe ON ((dord.ordnumber = oe.ordnumber) AND NOT COALESCE(oe.quotation, FALSE))|;
+       LEFT JOIN project pr ON (dord.globalproject_id = pr.id)|;
 
   push @where, ($form->{type} eq 'sales_delivery_order' ? '' : 'NOT ') . qq|COALESCE(dord.is_sales, FALSE)|;
 
@@ -143,16 +142,34 @@ sub transactions {
     "transaction_description" => "dord.transaction_description"
   );
 
-  my $sortoder = "dord.id";
+  my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
+  my $sortorder = "dord.id";
   if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) {
     $sortorder = $allowed_sort_columns{$form->{sort}};
   }
 
-  $query .= qq| ORDER by | . $sortorder;
+  $query .= qq| ORDER by | . $sortorder . " $sortdir";
 
   $form->{DO} = selectall_hashref_query($form, $dbh, $query, @values);
 
-  $main::lxdebug->dump(0, "DO", $form->{DO});
+  if (scalar @{ $form->{DO} }) {
+    $query =
+      qq|SELECT id
+         FROM oe
+         WHERE NOT COALESCE(quotation, FALSE)
+           AND (ordnumber = ?)
+           AND (COALESCE(${vc}_id, 0) != 0)|;
+
+    my $sth = prepare_query($form, $dbh, $query);
+
+    foreach my $dord (@{ $form->{DO} }) {
+      next unless ($dord->{ordnumber});
+      do_statement($form, $sth, $query, $dord->{ordnumber});
+      ($dord->{oe_id}) = $sth->fetchrow_array();
+    }
+
+    $sth->finish();
+  }
 
   $main::lxdebug->leave_sub();
 }
@@ -327,6 +344,20 @@ sub save {
   # save printed, emailed, queued
   $form->save_status($dbh);
 
+  # Link this delivery order to the quotations it was created from.
+  RecordLinks->create_links('dbh'        => $dbh,
+                            'mode'       => 'ids',
+                            'from_table' => 'oe',
+                            'from_ids'   => $form->{convert_from_oe_ids},
+                            'to_table'   => 'delivery_orders',
+                            'to_id'      => $form->{id},
+    );
+  delete $form->{convert_from_oe_ids};
+
+  $self->mark_orders_if_delivered('do_id' => $form->{id},
+                                  'type'  => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase',
+                                  'dbh'   => $dbh,);
+
   my $rc = $dbh->commit();
 
   $form->{saved_donumber} = $form->{donumber};
@@ -338,19 +369,96 @@ sub save {
   return $rc;
 }
 
-sub close_order {
+sub mark_orders_if_delivered {
   $main::lxdebug->enter_sub();
 
-  my ($self)   = @_;
+  my $self   = shift;
+  my %params = @_;
+
+  Common::check_params(\%params, qw(do_id type));
 
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
 
-  return $main::lxdebug->leave_sub() unless ($form->{id});
+  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 
-  my $dbh = $form->get_standard_dbh($myconfig);
-  do_query($form, $dbh, qq|UPDATE do SET closed = TRUE where id = ?|, conv_i($form->{id}));
-  $dbh->commit();
+  my @links    = RecordLinks->get_links('dbh'        => $dbh,
+                                        'from_table' => 'oe',
+                                        'to_table'   => 'delivery_orders',
+                                        'to_id'      => $params{do_id});
+
+  my ($oe_id)  = $links[0]->{from_id} if (scalar @links);
+
+  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});
+  }
+
+  $main::lxdebug->leave_sub();
+}
+
+sub close_orders {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+  my %params   = @_;
+
+  Common::check_params(\%params, qw(ids));
+
+  if (('ARRAY' ne ref $params{ids}) || !scalar @{ $params{ids} }) {
+    $main::lxdebug->leave_sub();
+    return;
+  }
+
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
+  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+  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} });
+
+  $dbh->commit() unless ($params{dbh});
 
   $main::lxdebug->leave_sub();
 }
@@ -425,7 +533,8 @@ sub delete {
 sub retrieve {
   $main::lxdebug->enter_sub();
 
-  my ($self)   = @_;
+  my $self     = shift;
+  my %params   = @_;
 
   my $myconfig = \%main::myconfig;
   my $form     = $main::form;
@@ -435,98 +544,120 @@ sub retrieve {
 
   my ($query, $query_add, @values, $sth, $ref);
 
-  if (!$form->{id}) {
+  my $vc   = $params{vc} eq 'customer' ? 'customer' : 'vendor';
+
+  my $mode = !$params{ids} ? 'default' : ref $params{ids} eq 'ARRAY' ? 'multi' : 'single';
+
+  if ($mode eq 'default') {
     $ref = selectfirst_hashref_query($form, $dbh, qq|SELECT current_date AS transdate, current_date AS reqdate|);
     map { $form->{$_} = $ref->{$_} } keys %$ref;
+
+    # get last name used
+    $form->lastname_used($dbh, $myconfig, $vc) unless $form->{"${vc}_id"};
+
+    $main::lxdebug->leave_sub();
+
+    return 1;
   }
 
-  my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
+  my @do_ids              = map { conv_i($_) } ($mode eq 'multi' ? @{ $params{ids} } : ($params{ids}));
+  my $do_ids_placeholders = join(', ', ('?') x scalar(@do_ids));
 
-  if ($form->{id}) {
+  # retrieve order for single id
+  # NOTE: this query is intended to fetch all information only ONCE.
+  # so if any of these infos is important (or even different) for any item,
+  # it will be killed out and then has to be fetched from the item scope query further down
+  $query =
+    qq|SELECT dord.cp_id, dord.donumber, dord.ordnumber, dord.transdate, dord.reqdate,
+         dord.shippingpoint, dord.shipvia, dord.notes, dord.intnotes,
+         e.name AS employee, dord.employee_id, dord.salesman_id,
+         dord.${vc}_id, cv.name AS ${vc},
+         dord.closed, dord.reqdate, dord.department_id, dord.cusordnumber,
+         d.description AS department, dord.language_id,
+         dord.shipto_id,
+         dord.globalproject_id, dord.delivered, dord.transaction_description
+       FROM delivery_orders dord
+       JOIN ${vc} cv ON (dord.${vc}_id = cv.id)
+       LEFT JOIN employee e ON (dord.employee_id = e.id)
+       LEFT JOIN department d ON (dord.department_id = d.id)
+       WHERE dord.id IN ($do_ids_placeholders)|;
+  $sth = prepare_execute_query($form, $dbh, $query, @do_ids);
 
-    # retrieve order for single id
-    # NOTE: this query is intended to fetch all information only ONCE.
-    # so if any of these infos is important (or even different) for any item,
-    # it will be killed out and then has to be fetched from the item scope query further down
-    $query =
-      qq|SELECT dord.cp_id, dord.donumber, dord.ordnumber, dord.transdate, dord.reqdate,
-           dord.shippingpoint, dord.shipvia, dord.notes, dord.intnotes,
-           e.name AS employee, dord.employee_id, dord.salesman_id,
-           dord.${vc}_id, cv.name AS ${vc},
-           dord.closed, dord.reqdate, dord.department_id, dord.cusordnumber,
-           d.description AS department, dord.language_id,
-           dord.shipto_id,
-           dord.globalproject_id, dord.delivered, dord.transaction_description
-         FROM delivery_orders dord
-         JOIN ${vc} cv ON (dord.${vc}_id = cv.id)
-         LEFT JOIN employee e ON (dord.employee_id = e.id)
-         LEFT JOIN department d ON (dord.department_id = d.id)
-         WHERE dord.id = ?|;
-    $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    $sth->finish();
+  delete $form->{"${vc}_id"};
+  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+    if ($form->{"${vc}_id"} && ($ref->{"${vc}_id"} != $form->{"${vc}_id"})) {
+      $sth->finish();
+      $main::lxdebug->leave_sub();
+
+      return 0;
+    }
 
     map { $form->{$_} = $ref->{$_} } keys %$ref if ($ref);
+  }
+  $sth->finish();
 
-    $form->{saved_donumber} = $form->{donumber};
+  $form->{saved_donumber} = $form->{donumber};
 
-    # if not given, fill transdate with current_date
-    $form->{transdate} = $form->current_date($myconfig) unless $form->{transdate};
+  # if not given, fill transdate with current_date
+  $form->{transdate} = $form->current_date($myconfig) unless $form->{transdate};
 
+  if ($mode eq 'single') {
     $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'DO'|;
-    $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
+    $sth   = prepare_execute_query($form, $dbh, $query, $form->{id});
 
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    delete($ref->{id});
+    $ref   = $sth->fetchrow_hashref(NAME_lc);
+    delete $ref->{id};
     map { $form->{$_} = $ref->{$_} } keys %$ref;
-    $sth->finish;
+    $sth->finish();
 
     # 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}));
+    $sth   = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
 
     while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
       $form->{printed} .= "$ref->{formname} " if $ref->{printed};
       $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
       $form->{queued}  .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
     }
-    $sth->finish;
+    $sth->finish();
     map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
 
-    my %oid = ('Pg'     => 'oid',
-               'Oracle' => 'rowid');
-
-    my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
+  } else {
+    delete $form->{id};
+  }
 
-    # retrieve individual items
-    # this query looks up all information about the items
-    # 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, doi.description, doi.qty,
-           doi.sellprice, doi.parts_id AS id, doi.unit, doi.discount, p.bin, p.notes AS partnotes,
-           doi.reqdate, doi.project_id, doi.serialnumber, doi.lastcost,
-           doi.ordnumber, doi.transdate, doi.cusordnumber, doi.longdescription,
-           doi.price_factor_id, doi.price_factor, doi.marge_price_factor,
-           pr.projectnumber,
-           pg.partsgroup
-         FROM delivery_order_items doi
-         JOIN parts p ON (doi.parts_id = p.id)
-         JOIN delivery_orders dord ON (doi.delivery_order_id = dord.id)
-         LEFT JOIN project pr ON (doi.project_id = pr.id)
-         LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-         WHERE doi.delivery_order_id = ?
-         ORDER BY doi.$oid{$myconfig->{dbdriver}}|;
-
-    $form->{form_details} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
+  my %oid = ('Pg'     => 'oid',
+             'Oracle' => 'rowid');
 
+  # retrieve individual items
+  # this query looks up all information about the items
+  # 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, doi.description, doi.qty,
+         doi.sellprice, doi.parts_id AS id, doi.unit, doi.discount, p.bin, p.notes AS partnotes,
+         doi.reqdate, doi.project_id, doi.serialnumber, doi.lastcost,
+         doi.ordnumber, doi.transdate, doi.cusordnumber, doi.longdescription,
+         doi.price_factor_id, doi.price_factor, doi.marge_price_factor,
+         pr.projectnumber,
+         pg.partsgroup
+       FROM delivery_order_items doi
+       JOIN parts p ON (doi.parts_id = p.id)
+       JOIN delivery_orders dord ON (doi.delivery_order_id = dord.id)
+       LEFT JOIN project pr ON (doi.project_id = pr.id)
+       LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+       WHERE doi.delivery_order_id IN ($do_ids_placeholders)
+       ORDER BY doi.$oid{$myconfig->{dbdriver}}|;
+
+  $form->{form_details} = selectall_hashref_query($form, $dbh, $query, @do_ids);
+
+  if ($mode eq 'single') {
     my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in';
 
     $query =
       qq|SELECT qty, unit, bin_id, warehouse_id, chargenumber
-           FROM delivery_order_items_stock
-           WHERE delivery_order_item_id = ?|;
+         FROM delivery_order_items_stock
+         WHERE delivery_order_item_id = ?|;
     my $sth = prepare_query($form, $dbh, $query);
 
     foreach my $doi (@{ $form->{form_details} }) {
@@ -540,16 +671,13 @@ sub retrieve {
     }
 
     $sth->finish();
-
-  } else {
-    # get last name used
-    $form->lastname_used($dbh, $myconfig, $form->{vc}) unless $form->{"$form->{vc}_id"};
-
   }
 
   Common::webdav_folder($form) if ($main::webdav);
 
   $main::lxdebug->leave_sub();
+
+  return 1;
 }
 
 sub order_details {
@@ -793,7 +921,7 @@ sub check_stock_availability {
 
   my $dbh         = $form->get_standard_dbh($myconfig);
 
-  my $units       = AM->retrieve_units($myconfig, $form, "dimension");
+  my $units       = AM->retrieve_units($myconfig, $form);
 
   my ($partunit)  = selectrow_query($form, $dbh, qq|SELECT unit FROM parts WHERE id = ?|, conv_i($params{parts_id}));
   my $unit_factor = $units->{$partunit}->{factor} || 1;
@@ -869,4 +997,75 @@ sub transfer_in_out {
   $main::lxdebug->leave_sub();
 }
 
+sub get_shipped_qty {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+  my %params   = @_;
+
+  Common::check_params(\%params, qw(type oe_id));
+
+  my $myconfig = \%main::myconfig;
+  my $form     = $main::form;
+
+  my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+  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 (!scalar @values) {
+    $main::lxdebug->leave_sub();
+    return ();
+  }
+
+  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|)|;
+
+  my %ship      = ();
+  my $entries   = selectall_hashref_query($form, $dbh, $query, @values);
+  my $all_units = AM->retrieve_all_units();
+
+  foreach my $entry (@{ $entries }) {
+    $entry->{qty} *= $all_units->{$entry->{unit}}->{factor} / $all_units->{$entry->{partunit}}->{factor};
+
+    if (!$ship{$entry->{parts_id}}) {
+      $ship{$entry->{parts_id}} = $entry;
+    } else {
+      $ship{$entry->{parts_id}}->{qty} += $entry->{qty};
+    }
+  }
+
+  $main::lxdebug->leave_sub();
+
+  return %ship;
+}
+
+sub is_marked_as_delivered {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+  my %params   = @_;
+
+  Common::check_params(\%params, qw(id));
+
+  my $myconfig    = \%main::myconfig;
+  my $form        = $main::form;
+
+  my $dbh         = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+  my ($delivered) = selectfirst_array_query($form, $dbh, qq|SELECT delivered FROM delivery_orders WHERE id = ?|, conv_i($params{id}));
+
+  $main::lxdebug->leave_sub();
+
+  return $delivered ? 1 : 0;
+}
+
+
 1;