X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fcustalloc_db.inc;h=5b7782aecafc5477fa9b71825eb26a7055ff37b3;hb=963bc21ea092322d5750d4c0914b5e532e7d2467;hp=618c9397ca61bbdba6879d0faba20367d04727e3;hpb=576db19bd8d526336e6dcd360a6fcc875215f2a7;p=fa-stable.git

diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc
index 618c9397..5b7782ae 100644
--- a/sales/includes/db/custalloc_db.inc
+++ b/sales/includes/db/custalloc_db.inc
@@ -171,5 +171,73 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ
 	return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 }
 
+function get_sql_for_customer_allocation_inquiry()
+{
+  	$data_after = date2sql($_POST['TransAfterDate']);
+  	$date_to = date2sql($_POST['TransToDate']);
 
+  	$sql = "SELECT 
+  		trans.type,
+		trans.trans_no,
+		trans.reference,
+		trans.order_,
+		trans.tran_date,
+		trans.due_date,
+		debtor.name,
+		debtor.curr_code,
+    	(trans.ov_amount + trans.ov_gst + trans.ov_freight 
+			+ trans.ov_freight_tax + trans.ov_discount)	AS TotalAmount,
+		trans.alloc AS Allocated,
+		((trans.type = ".ST_SALESINVOICE.")
+			AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
+    	FROM "
+			.TB_PREF."debtor_trans as trans, "
+			.TB_PREF."debtors_master as debtor
+    	WHERE debtor.debtor_no = trans.debtor_no
+			AND (trans.ov_amount + trans.ov_gst + trans.ov_freight 
+				+ trans.ov_freight_tax + trans.ov_discount != 0)
+    		AND trans.tran_date >= '$data_after'
+    		AND trans.tran_date <= '$date_to'";
+
+   	if ($_POST['customer_id'] != ALL_TEXT)
+   		$sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
+
+   	if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
+   	{
+   		if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
+   		{
+   			$sql .= " AND trans.type = ".ST_SALESINVOICE." ";
+   		}
+   		elseif ($_POST['filterType'] == '3')
+   		{
+			$sql .= " AND trans.type = " . ST_CUSTPAYMENT;
+   		}
+   		elseif ($_POST['filterType'] == '4')
+   		{
+			$sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
+   		}
+
+    	if ($_POST['filterType'] == '2')
+    	{
+    		$today =  date2sql(Today());
+    		$sql .= " AND trans.due_date < '$today'
+				AND (round(abs(trans.ov_amount + "
+				."trans.ov_gst + trans.ov_freight + "
+				."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
+    	}
+   	}
+   	else
+   	{
+	    $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
+   	}
+
+
+   	if (!check_value('showSettled'))
+   	{
+   		$sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
+		."trans.ov_freight + trans.ov_freight_tax + "
+		."trans.ov_discount) - trans.alloc,6) != 0) ";
+   	}
+   	return $sql;
+}
 ?>
\ No newline at end of file