X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fcustomers_db.inc;h=637bc07a8b19dc28f331778fc3cffedc42b3e16e;hb=bc426821d8ae6319bd9394ac275bb700e6394fc3;hp=9915071af55141309745d5d30bd4930e2141dbab;hpb=da8311619dd73feae101d246a1957b972e00cbd2;p=fa-stable.git

diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc
index 9915071a..637bc07a 100644
--- a/sales/includes/db/customers_db.inc
+++ b/sales/includes/db/customers_db.inc
@@ -2,73 +2,79 @@
 
 function get_customer_details($customer_id, $to=null)
 {
-	
+
 	if ($to == null)
 		$todate = date("Y-m-d");
-	else	
+	else
 		$todate = date2sql($to);
 	$past1 = get_company_pref('past_due_days');
 	$past2 = 2 * $past1;
 	// removed - debtor_trans.alloc from all summations
-	
+
+    $value = "IF(".TB_PREF."debtor_trans.type=11 OR ".TB_PREF."debtor_trans.type=12 OR ".TB_PREF."debtor_trans.type=2,
+	-1, 1) *".
+      "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
+		.TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
+		.TB_PREF."debtor_trans.ov_discount)";
+
     $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
 		".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
 
-		Sum(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount) AS Balance,
+		Sum(".$value.") AS Balance,
 
 		Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
-			CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-			ELSE 0 END,
+			CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due
+			THEN
+			    $value
+			ELSE 0
+			END,
 
 			CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
 				INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
-				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount 
-			ELSE 
-				0 
+				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= 0
+			THEN
+			    $value
+			ELSE 0
 			END
 
 		)) AS Due,
 
 		Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
 			CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
-				AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1) 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount 
-			ELSE 
-				0 
+				AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1)
+			THEN
+				$value
+			ELSE
+				0
 			END,
 
 			CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
 				INTERVAL 1  MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
-				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past1) 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-			ELSE 
-				0 
+				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past1)
+			THEN
+				$value
+			ELSE
+				0
 			END
 
 		)) AS Overdue1,
 
 		Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
 			CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
-				AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2) 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount 
-			ELSE 
-				0 
+				AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."debtor_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2)
+			THEN
+			    $value
+			ELSE
+				0
 			END,
 
 			CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."debtor_trans.tran_date,
-				INTERVAL 1  MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month - 
-				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past2) 
-			THEN 
-				".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_discount
-			ELSE 
-				0 
+				INTERVAL 1  MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
+				DAYOFMONTH(".TB_PREF."debtor_trans.tran_date)) DAY)) >= $past2)
+			THEN
+				$value
+			ELSE
+				0
 			END
 
 		)) AS Overdue2
@@ -83,6 +89,7 @@ function get_customer_details($customer_id, $to=null)
 			 AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
 			 AND ".TB_PREF."debtors_master.debtor_no = $customer_id
 			 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
+			 AND ".TB_PREF."debtor_trans.type <> 13
 			 AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
 
 		GROUP BY
@@ -93,66 +100,66 @@ function get_customer_details($customer_id, $to=null)
 			  ".TB_PREF."debtors_master.credit_limit,
 			  ".TB_PREF."credit_status.dissallow_invoices,
 			  ".TB_PREF."credit_status.reason_description";
-    
+
     $result = db_query($sql,"The customer details could not be retrieved");
-    
+
     if (db_num_rows($result) == 0)
     {
-    
+
     	/*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
-    
+
     	$nil_balance = true;
-    
+
     	$sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.debtor_no,  ".TB_PREF."payment_terms.terms,
     		".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description
     		FROM ".TB_PREF."debtors_master,
     		     ".TB_PREF."payment_terms,
     		     ".TB_PREF."credit_status
-    
+
     		WHERE
     		     ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
     		     AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
     		     AND ".TB_PREF."debtors_master.debtor_no = '$customer_id'";
-    	     
+
     	$result = db_query($sql,"The customer details could not be retrieved");
-    
-    } 
-    else 
+
+    }
+    else
     {
     	$nil_balance = false;
     }
-    
+
     $customer_record = db_fetch($result);
-    
+
     if ($nil_balance == true)
     {
     	$customer_record["Balance"] = 0;
     	$customer_record["Due"] = 0;
     	$customer_record["Overdue1"] = 0;
     	$customer_record["Overdue2"] = 0;
-    }    
-    
+    }
+
     return $customer_record;
-	
+
 }
 
 function get_customer_name($customer_id)
 {
 	$sql = "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
-	
+
 	$result = db_query($sql, "could not get customer");
-	
+
 	$row = db_fetch_row($result);
-	
+
 	return $row[0];
 }
 
 function get_area_name($id)
 {
 	$sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=$id";
-	
+
 	$result = db_query($sql, "could not get sales type");
-	
+
 	$row = db_fetch_row($result);
 	return $row[0];
 }
@@ -160,9 +167,9 @@ function get_area_name($id)
 function get_salesman_name($id)
 {
 	$sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=$id";
-	
+
 	$result = db_query($sql, "could not get sales type");
-	
+
 	$row = db_fetch_row($result);
 	return $row[0];
 }