AND so.debtor_no = $customer_id
)
JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
- JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id)
+ LEFT JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id)
WHERE sod.quantity > qty_sent
";
return $sql;
}
-function get_order_summary() {
- $sql = "SELECT debtor_no, debtor_ref, branch_ref
+function get_order_summary($location) {
+/*
+ $sub = "SELECT debtor_no, debtor_ref, branch_ref, stk_code
, min(delivery_date)
- , sum(quantity - qty_sent) as quantity
- , sum((quantity - qty_sent)*unit_price*(1-discount_percent/100)) as amount
+ , sum(sod.quantity - qty_sent) as quantity
+ , sum((sod.quantity - qty_sent)*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
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
)
NATURAL JOIN ".TB_PREF."debtors_master
NATURAL JOIN ".TB_PREF."cust_branch
- WHERE quantity > qty_sent AND ".ST_SALESORDER."
- GROUP BY debtor_no, branch_code
+ JOIN ".TB_PREF."denorm_order_details_queue d ON (d.id = sod.id)
+ WHERE sod.quantity > qty_sent AND ".ST_SALESORDER."
+ GROUP BY debtor_no, branch_code, stk_code
+ ";
+*/
+
+ $sub = TB_PREF."order_summary_view";
+
+ $sql = "SELECT debtor_no, debtor_ref, branch_ref
+ , `min(delivery_date)`
+ , sum(sub.quantity) as quantity
+ , sum(sub.amount) as amount
+ , sum(greatest(least(sub.quantity, qoh.quantity - quantity_before), 0))
+ , sum(sub.amount*greatest(least(sub.quantity, qoh.quantity - quantity_before), 0)/sub.quantity)
+ , min(`min(required_date)`)
+ , group_concat(distinct order_comment separator ';') as order_comment
+ , group_concat(distinct detail_comment separator ';') as detail_comment
+ FROM $sub sub
+ LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
+ GROUP BY debtor_no, debtor_ref
";
return $sql;