Einkaufs- und Verkaufsrechnungen: Filtermöglichkeiten nach Projektnummer sowie Anzeig...

[kivitendo-erp.git] / SL / OE.pm

diff --git a/SL/OE.pm b/SL/OE.pm
index b950983..d471a81 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -52,6 +52,15 @@ sub transactions {
   my ($null, $department_id) = split /--/, $form->{department};
 
   my $department = " AND o.department_id = $department_id" if $department_id;
+  my @values;
+
+  if ($form->{"project_id"}) {
+    $department .=
+      qq|AND ((globalproject_id = ?) OR EXISTS | .
+      qq|  (SELECT * FROM orderitems oi | .
+      qq|   WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
+    push(@values, $form->{"project_id"}, $form->{"project_id"});
+  }
 
   my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
 
@@ -65,6 +74,7 @@ sub transactions {
 
   my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
                  o.amount, ct.name, o.netamount, o.$form->{vc}_id,
+                 o.globalproject_id, pr.projectnumber AS globalprojectnumber,
                 ex.$rate AS exchangerate,
                 o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia,
                 e.name AS employee
@@ -73,39 +83,10 @@ sub transactions {
                 LEFT JOIN employee e ON (o.employee_id = e.id)
                 LEFT JOIN exchangerate ex ON (ex.curr = o.curr
                                               AND ex.transdate = o.transdate)
+                 LEFT JOIN project pr ON o.globalproject_id = pr.id
                 WHERE o.quotation = '$quotation'
                 $department|;
 
-  # build query if type eq (ship|receive)_order
-  if ($form->{type} =~ /(ship|receive)_order/) {
-    my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse};
-
-    $query = qq|SELECT DISTINCT ON (o.id) o.id, o.ordnumber, o.transdate,
-                 o.reqdate, o.amount, ct.name, o.netamount, o.$form->{vc}_id,
-                ex.$rate AS exchangerate,
-                o.closed, o.quonumber, o.shippingpoint, o.shipvia,
-                e.name AS employee
-                FROM oe o
-                JOIN $form->{vc} ct ON (o.$form->{vc}_id = ct.id)
-                JOIN orderitems oi ON (oi.trans_id = o.id)
-                JOIN parts p ON (p.id = oi.parts_id)
-                LEFT JOIN employee e ON (o.employee_id = e.id)
-                LEFT JOIN exchangerate ex ON (ex.curr = o.curr
-                                              AND ex.transdate = o.transdate)
-                WHERE o.quotation = '0'
-                AND (p.inventory_accno_id > 0 OR p.assembly = '1')
-                AND oi.qty <> oi.ship
-                $department|;
-
-    if ($warehouse_id && $form->{type} eq 'ship_order') {
-      $query .= qq|
-                 AND i.warehouse_id = $warehouse_id
-                AND i.qty >= (oi.qty - oi.ship)
-                |;
-    }
-
-  }
-
   if ($form->{"$form->{vc}_id"}) {
     $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
   } else {
@@ -137,7 +118,10 @@ sub transactions {
   $query .= " ORDER by $sortorder";
 
   my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $sth->execute(@values) ||
+    $form->dberror($query . " (" . join(", ", @values) . ")");
+
+  dump_query(0, "laqje", $query, @values);
 
   my %id = ();
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
@@ -341,10 +325,6 @@ sub save {
 
       $netamount += $form->{"sellprice_$i"} * $form->{"qty_$i"};
 
-      $project_id = 'NULL';
-      if ($form->{"projectnumber_$i"}) {
-        $project_id = $form->{"projectnumber_$i"};
-      }
       $reqdate =
         ($form->{"reqdate_$i"}) ? qq|'$form->{"reqdate_$i"}'| : "NULL";
 
@@ -365,7 +345,7 @@ sub save {
       $query .= qq|$form->{id}, $form->{"id_$i"},
                   '$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"}, $baseqty,
                   $fxsellprice, $form->{"discount_$i"},
-                  '$form->{"unit_$i"}', $reqdate, (SELECT id from project where projectnumber = '$project_id'),
+                  '$form->{"unit_$i"}', $reqdate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
                   '$form->{"serialnumber_$i"}', $form->{"ship_$i"}, '$pricegroup_id',
                   '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', '$subtotal')|;
       $dbh->do($query) || $form->dberror($query);
@@ -467,6 +447,7 @@ Message: $form->{message}\r| if $form->{message};
              payment_id = $form->{payment_id},
              delivery_vendor_id = $form->{delivery_vendor_id},
              delivery_customer_id = $form->{delivery_customer_id},
+              globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
              employee_id = $form->{employee_id},
               cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
               WHERE id = $form->{id}|;
@@ -700,6 +681,7 @@ sub retrieve {
                o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
                d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
                 o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
+                o.globalproject_id,
                 o.delivered
                FROM oe o
                JOIN $form->{vc} cv ON (o.$form->{vc}_id = cv.id)
@@ -962,30 +944,6 @@ sub order_details {
     push @partsgroup, [$i, $partsgroup];
   }
 
-  # if there is a warehouse limit picking
-  if ($form->{warehouse_id} && $form->{formname} =~ /(pick|packing)_list/) {
-
-    # run query to check for inventory
-    $query = qq|SELECT sum(i.qty) AS qty
-                FROM inventory i
-               WHERE i.parts_id = ?
-               AND i.warehouse_id = ?|;
-    $sth = $dbh->prepare($query) || $form->dberror($query);
-
-    for $i (1 .. $form->{rowcount}) {
-      $sth->execute($form->{"id_$i"}, $form->{warehouse_id}) || $form->dberror;
-
-      ($qty) = $sth->fetchrow_array;
-      $sth->finish;
-
-      $form->{"qty_$i"} = 0 if $qty == 0;
-
-      if ($form->parse_amount($myconfig, $form->{"ship_$i"}) > $qty) {
-        $form->{"ship_$i"} = $form->format_amount($myconfig, $qty);
-      }
-    }
-  }
-
   my $sameitem = "";
   foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
     $i = $item->[0];