projekt kivitendo

--  WITH

--  open_qty as ( SELECT parts_id, sum(oi.qty) as sum

--  FROM orderitems oi

--  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

--  WHERE

--  oi.parts_id = ?

--  AND (NOT COALESCE(o.quotation, FALSE))

--  AND (NOT COALESCE(o.closed,    FALSE))

--  AND (NOT COALESCE(o.delivered, FALSE))

--  AND (COALESCE(o.vendor_id, 0) <> 0)

--  GROUP BY oi.parts_id),

--  open_orderitems_ids AS ( SELECT oi.id, parts_id

--  FROM orderitems oi

--  LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

--  WHERE

--  oi.parts_id = ?

--  AND (NOT COALESCE(o.quotation, FALSE))

--  AND (NOT COALESCE(o.closed,    FALSE))

--  AND (NOT COALESCE(o.delivered, FALSE))

--  AND (COALESCE(o.vendor_id, 0) <> 0)

--  ),

--  delivered_qty AS ( SELECT parts_id, sum(qty) AS sum FROM delivery_order_items

--  WHERE id IN (SELECT to_id FROM record_links WHERE from_id IN (select id FROM open_orderitems_ids) AND from_table = 'orderitems' AND to_table = 'delivery_order_items') AND parts_id = ? GROUP BY parts_id),

--  open_ordered_qty AS ( SELECT dq.parts_id, oq.sum AS ordered_sum, dq.sum, sum(oq.sum - dq.sum) AS open_qty

--  FROM open_qty oq

--  RIGHT JOIN delivered_qty dq ON dq.parts_id = oq.parts_id

--  GROUP BY dq.parts_id, oq.sum, dq.sum)

--  SELECT open_qty FROM open_ordered_qty

with

open_qty as ( SELECT parts_id, sum(oi.qty) as sum

FROM orderitems oi

LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

WHERE

oi.parts_id = ?

AND (NOT COALESCE(o.quotation, FALSE))

AND (NOT COALESCE(o.closed,    FALSE))

AND (NOT COALESCE(o.delivered, FALSE))

AND (COALESCE(o.vendor_id, 0) <> 0)

GROUP BY oi.parts_id),

open_orderitems_ids as ( SELECT oi.id, parts_id

FROM orderitems oi

LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)

WHERE

oi.parts_id = ?

AND (NOT COALESCE(o.quotation, FALSE))

AND (NOT COALESCE(o.closed,    FALSE))

AND (NOT COALESCE(o.delivered, FALSE))

AND (COALESCE(o.vendor_id, 0) <> 0)

),

delivered_qty as ( select parts_id, sum(qty) as sum from delivery_order_items

where id in (select to_id from record_links where from_id in (select id from open_orderitems_ids) and from_table = 'orderitems' and to_table = 'delivery_order_items') and parts_id = ? group by parts_id),

open_ordered_qty as ( select oq.parts_id, oq.sum as ordered_sum, iif(dq.sum is null,0.00,dq.sum) as sum, sum(iif(oq.sum is null,0.00,oq.sum) - iif(dq.sum is null,0.00,dq.sum)) as open_qty

from open_qty oq

left join delivered_qty dq on dq.parts_id = oq.parts_id

group by oq.parts_id, oq.sum, dq.sum)

select open_qty from open_ordered_qty

  my ($open_qty) = selectrow_query($::form, $class->object_class->init_db->dbh, $query, $part_id, $part_id, $part_id);

#    my $openitems = SL::DB::Manager::OrderItem->get_all(where => [

#                                                            parts_id => $part_id,

#                                                            'order.closed' => 0,

#                                                            'order.quotation' => 0,

#                                                            'order.delivered' => 0,

#                                                            #  'order.type' => { ilike => 'purchase_order' },

#                                                            'order.vendor_id' => { ne => 0 }

#                                                          ],

#                                                           with_objects => ['order'],

#                                                         );

#                                                         $main::lxdebug->dump(0, 'WH:OPEN ', $openitems);

#  return 0 unless $openitems;

#  my $open_qty        = 0;

#  my $ordered_qty     = 0;

#  my $delivered_qty   = 0;

#  for my $openitem (@{ $openitems }) {

##      if($openitem->order->type eq 'purchase_order') {

#        $ordered_qty   += $openitem->qty;

#        $delivered_qty += $openitem->delivered_qty;

##      }

#    }

##   $open_qty = map{ $_->qty - $_->delivered_qty } @{$openitems};

#      $open_qty = $ordered_qty - $delivered_qty;

#  return $open_qty;

  $main::lxdebug->dump(0, 'WH: iOPENQTY', $open_qty);

  return $open_qty;