X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Forder_lines.inc;h=dc09a6bcf57d12ddd710fbadfdaa24ebf310e22e;hb=791f2f0482e01d5d152091edb5ef267300c91d26;hp=5f8d922cbe8458fc0ed226acb81861ab995751d9;hpb=3df7c3277a8c0502dca5cfeb32d63db1bef67267;p=order_line_extra.git

diff --git a/includes/order_lines.inc b/includes/order_lines.inc
index 5f8d922..dc09a6b 100644
--- a/includes/order_lines.inc
+++ b/includes/order_lines.inc
@@ -110,6 +110,15 @@ function aggregate_comment($row) {
 	return '';
 }
 
+function available_quantity($row, $available) {
+	$quantity = $row['quantity'];
+	$class = '';
+	if($available== 0) $class = 'limited';
+	else if($quantity > $available)  $class = 'partial';
+
+	return "<span class='$class'>$available<span>";
+}
+
 function input_date_details($row, $date) {
 $row_id = $row['id'];
 	$name = compute_input_name($row, 'required_date');
@@ -137,7 +146,7 @@ function get_order_details_extra($customer_id, $location) {
 	$sql = "SELECT sod.id
 	, so.order_no
 	, stk_code
-	, sod.quantity - qty_sent
+	, sod.quantity - qty_sent quantity
 	, GREATEST(0, LEAST(qoh.quantity  - quantity_before, sod.quantity - qty_sent))
 	, quantity_before
 	, required_date
@@ -149,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
@@ -171,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;