Compute available summary (and view)

[order_line_extra.git] / includes / order_lines.inc

diff --git a/includes/order_lines.inc b/includes/order_lines.inc
index 92391f6034458ef3efb3c9447e1562e7dca6a111..dc09a6bcf57d12ddd710fbadfdaa24ebf310e22e 100644 (file)
--- a/includes/order_lines.inc
+++ b/includes/order_lines.inc
@@ -158,21 +158,23 @@ function get_order_details_extra($customer_id, $location) {
                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
@@ -180,8 +182,26 @@ function get_order_summary() {
        )
        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(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;