Use picked quantity in available Q&A on order summary

[order_line_extra.git] / includes / order_lines.inc

diff --git a/includes/order_lines.inc b/includes/order_lines.inc
index a3367ea340b7732b1f4d464e5e7e699eacd739e7..d961b8926fc955c4742b29b96097d336a3003d53 100644 (file)
--- a/includes/order_lines.inc
+++ b/includes/order_lines.inc
@@ -131,6 +131,11 @@ function view_link($dummy, $order_no)
        return  get_customer_trans_view_str(ST_SALESORDER, $order_no);
 }
 
+function edit_link($row, $order_no) {
+       return pager_link( _("# $order_no"),
+       "/sales/sales_order_entry.php?ModifyOrderNumber=" .$row['order_no']);
+}
+
 function item_link($dummy, $stock_id)
 {
        return pager_link( _($stock_id),
@@ -171,7 +176,7 @@ function aggregate_comment($row) {
 function available_quantity($row, $available) {
        $quantity = $row['quantity'];
        $class = '';
-       if($available== 0) $class = 'limited';
+       if($available== 0) $class = $row['qoh'] == 0 ? 'stockmankobg' : 'limited';
        else if($quantity > $available)  $class = 'partial';
        else if(OrderXtraConfig::early(sql2date($row['hold_until_date']))) $class = 'early';
 
@@ -240,8 +245,9 @@ function get_order_details_extra($customer_id, $location, $item_like, $order_id)
        , so.order_no
        , stk_code
        , sod.quantity - qty_sent quantity
-       , GREATEST(0, LEAST(qoh.quantity  - quantity_before, sod.quantity - qty_sent))
+       , GREATEST(0, LEAST(qoh.quantity  - quantity_before - COALESCE(tp.quantity,0) + COALESCE(p.quantity, 0) , sod.quantity - qty_sent)) AS available
        , quantity_before
+       , qoh.quantity AS qoh
        , p.quantity AS quantity_to_pick
        , sod.`priority`
        , hold_until_date
@@ -257,6 +263,7 @@ function get_order_details_extra($customer_id, $location, $item_like, $order_id)
        JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
        LEFT JOIN ".TB_PREF."denorm_order_details_queue  d ON (d.id = sod.id)
        LEFT JOIN (".pick_query().") p ON (detail_id = sod.id)
+       LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
        ";
        $where = " sod.quantity > qty_sent
        ";
@@ -283,28 +290,30 @@ 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
                AND so.trans_type = ".ST_SALESORDER."
        )
-       NATURAL JOIN ".TB_PREF."debtors_master
-       NATURAL JOIN ".TB_PREF."cust_branch
+       JOIN ".TB_PREF."debtors_master USING (debtor_no)
+       JOIN ".TB_PREF."cust_branch USING (debtor_no, branch_code)
        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
        ";