From 5aef2f5655dc43191d6756db26094cea4d0ed032 Mon Sep 17 00:00:00 2001 From: Maxime Bourget Date: Sun, 16 Jun 2013 12:05:50 +0100 Subject: [PATCH] Use picked quantity in available Q&A on order summary --- includes/order_lines.inc | 26 ++++++++++++++------------ includes/picking.inc | 4 +++- 2 files changed, 17 insertions(+), 13 deletions(-) diff --git a/includes/order_lines.inc b/includes/order_lines.inc index d1f7cd1..d961b89 100644 --- a/includes/order_lines.inc +++ b/includes/order_lines.inc @@ -290,18 +290,19 @@ function get_order_summary($location) { $pick = pick_query(); $sub = "SELECT debtor_no, debtor_ref, branch_code, branch_ref, stk_code - , min(delivery_date) - , sum(d.quantity) as quantity - , sum((d.quantity)*unit_price*(1-discount_percent/100)) as amount - , min(required_date) - , group_concat(distinct comments separator ';') as order_comment - , group_concat(distinct comment separator ';') as detail_comment - , max(quantity_before) as quantity_before - , sum(if($held_condition, greatest(least(d.quantity, qoh.quantity - quantity_before), 0), 0)) as quantity_held - , sum(if($held_condition, 0, greatest(least(d.quantity, qoh.quantity - quantity_before), 0))) as available_quantity - , sum(if($held_condition, 0, greatest(least(d.quantity, qoh.quantity - quantity_before), 0)*unit_price*(1-discount_percent/100))) as available_amount - , sum(p.quantity) as quantity_to_pick - , sum(p.quantity*unit_price) as amount_to_pick + , MIN(delivery_date) + , SUM(d.quantity) AS quantity + , SUM((d.quantity)*unit_price*(1-discount_percent/100)) AS amount + , MIN(required_date) + , GROUP_CONCAT(distinct comments separator ';') AS order_comment + , GROUP_CONCAT(distinct comment separator ';') AS detail_comment + , MAX(quantity_before) AS quantity_before + , @available := GREATEST(LEAST(d.quantity, COALESCE(qoh.quantity,0) - COALESCE(tp.quantity,0) + COALESCE(p.quantity,0) - quantity_before), 0) + , SUM(IF($held_condition, @available , 0)) AS quantity_held + , SUM(IF($held_condition, 0, @available)) AS available_quantity + , SUM(IF($held_condition, 0, @available*unit_price*(1-discount_percent/100))) AS available_amount + , SUM(p.quantity) AS quantity_to_pick + , SUM(p.quantity*unit_price) AS amount_to_pick FROM ".TB_PREF."sales_order_details sod JOIN ".TB_PREF."sales_orders so ON (so.order_no = sod.order_no AND so.trans_type = sod.trans_type @@ -312,6 +313,7 @@ function get_order_summary($location) { LEFT JOIN ($pick) p ON (detail_id = sod.id) JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id) LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = stk_code AND loc_code = '$location') + LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code) WHERE sod.quantity > qty_sent AND ".ST_SALESORDER." GROUP BY debtor_no, branch_code, stk_code "; diff --git a/includes/picking.inc b/includes/picking.inc index 27e2ef6..2d3b5b8 100644 --- a/includes/picking.inc +++ b/includes/picking.inc @@ -48,18 +48,20 @@ class Picker { , d.quantity AS quantity ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held ,qoh.quantity AS qoh + ,tp.quantity AS total_picked ,quantity_before ,order_id FROM ".TB_PREF."sales_order_details sod NATURAL JOIN ".TB_PREF."sales_orders so JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id) JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location') + LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code) WHERE debtor_no = $debtor_no AND branch_code = $branch_code "; $result = db_query($sql, $sql); while($row=db_fetch($result)) { foreach($row as $key => $value) { $$key = $value; } - $available = max(min($quantity-$held, $qoh-$quantity_before-$held) ,0 ); + $available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 ); if($available || OrderXtraConfig::$autopick_null) insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity); -- 2.30.2