X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fcustalloc_db.inc;h=5b7782aecafc5477fa9b71825eb26a7055ff37b3;hb=3cf9ab70d79ebd01b13b6cc0c9366ccb5b8c496a;hp=9152a3f41142dbba608c1a3117168bb525c57673;hpb=ddadb47f2620ce6902ad4694ce6512568862ba05;p=fa-stable.git

diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc
index 9152a3f4..5b7782ae 100644
--- a/sales/includes/db/custalloc_db.inc
+++ b/sales/includes/db/custalloc_db.inc
@@ -17,7 +17,8 @@ function add_cust_allocation($amount, $trans_type_from, $trans_no_from,
 	$sql = "INSERT INTO ".TB_PREF."cust_allocations (
 		amt, date_alloc,
 		trans_type_from, trans_no_from, trans_no_to, trans_type_to)
-		VALUES ($amount, Now(), $trans_type_from, $trans_no_from, $trans_no_to, $trans_type_to)";
+		VALUES ($amount, Now(), ".db_escape($trans_type_from).", ".db_escape($trans_no_from).", ".db_escape($trans_no_to)
+		.", ".db_escape($trans_type_to).")";
 
 	db_query($sql, "A customer allocation could not be added to the database");
 }
@@ -27,7 +28,7 @@ function add_cust_allocation($amount, $trans_type_from, $trans_no_from,
 
 function delete_cust_allocation($trans_id)
 {
-	$sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE id = " . $trans_id;
+	$sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE id = ".db_escape($trans_id);
 	return db_query($sql, "The existing allocation $trans_id could not be deleted");
 }
 
@@ -37,7 +38,7 @@ function get_DebtorTrans_allocation_balance($trans_type, $trans_no)
 {
 
 	$sql = "SELECT (ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc) AS BalToAllocate
-		FROM ".TB_PREF."debtor_trans WHERE trans_no=$trans_no AND type=$trans_type";
+		FROM ".TB_PREF."debtor_trans WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type);
 	$result = db_query($sql,"calculate the allocation");
 	$myrow = db_fetch_row($result);
 
@@ -49,7 +50,7 @@ function get_DebtorTrans_allocation_balance($trans_type, $trans_no)
 function update_debtor_trans_allocation($trans_type, $trans_no, $alloc)
 {
 	$sql = "UPDATE ".TB_PREF."debtor_trans SET alloc = alloc + $alloc
-		WHERE type=$trans_type AND trans_no = $trans_no";
+		WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no);
 	db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
 }
 
@@ -66,8 +67,8 @@ function clear_cust_alloctions($type, $type_no, $date="")
 {
 	// clear any allocations for this transaction
 	$sql = "SELECT * FROM ".TB_PREF."cust_allocations
-		WHERE (trans_type_from=$type AND trans_no_from=$type_no)
-		OR (trans_type_to=$type AND trans_no_to=$type_no)";
+		WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
+		OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
 	$result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
 
 	while ($row = db_fetch($result))
@@ -86,8 +87,8 @@ function clear_cust_alloctions($type, $type_no, $date="")
 
 	// remove any allocations for this transaction
 	$sql = "DELETE FROM ".TB_PREF."cust_allocations
-		WHERE (trans_type_from=$type AND trans_no_from=$type_no)
-		OR (trans_type_to=$type AND trans_no_to=$type_no)";
+		WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
+		OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
 
 	db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
 }
@@ -136,7 +137,7 @@ function get_allocatable_from_cust_sql($customer_id, $settled)
 	}
 	$cust_sql = "";
 	if ($customer_id != null)
-		$cust_sql = " AND trans.debtor_no = $customer_id";
+		$cust_sql = " AND trans.debtor_no = ".db_escape($customer_id);
 
 	$sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
 		"(type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
@@ -154,7 +155,7 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ
 			AND trans.type = alloc.trans_type_to
 			AND alloc.trans_no_from=$trans_no
 			AND alloc.trans_type_from=$type
-			AND trans.debtor_no=$customer_id",
+			AND trans.debtor_no=".db_escape($customer_id),
 			"".TB_PREF."cust_allocations as alloc");
 	}
 	else
@@ -164,11 +165,79 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ
 			AND trans.type <> " . ST_BANKDEPOSIT . "
 			AND trans.type <> " . ST_CUSTCREDIT . "
 			AND trans.type <> " . ST_CUSTDELIVERY . "
-			AND trans.debtor_no=$customer_id");
+			AND trans.debtor_no=".db_escape($customer_id));
 	}
 
 	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