From: Janusz Dobrowolski <janusz@frontaccounting.eu>
Date: Sat, 30 Apr 2022 11:13:49 +0000 (+0200)
Subject: Customer balances reports: additional bug fixes to balance totals calculations.
X-Git-Url: https://delta.frontaccounting.com/gitweb/?p=fa-stable.git;a=commitdiff_plain;h=4618ee761a2d5ea779858009710ed43e12325c6f

Customer balances reports: additional bug fixes to balance totals calculations.
---

diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc
index 8d423918..96e17cef 100644
--- a/sales/includes/db/customers_db.inc
+++ b/sales/includes/db/customers_db.inc
@@ -72,40 +72,42 @@ function get_customer_details($customer_id, $to=null, $all=true)
 		$todate = date2sql($to);
 	$past1 = get_company_pref('past_due_days');
 	$past2 = 2 * $past1;
-	// removed - debtor_trans.alloc from all summations
-	$sign = "IF(`type` IN(".implode(',',  array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
-	if ($all)
-    	$value = "IFNULL($sign*(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount),0)";
-    else		
-    	$value = "IFNULL($sign*(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - 
-    		trans.alloc),0)";
-	$due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
-    $sql = "SELECT debtor.name, debtor.curr_code, terms.terms, debtor.credit_limit,
-    			credit_status.dissallow_invoices, credit_status.reason_description,
-				Sum($value) AS Balance,
-				Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due,
-				Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
-				Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
-			FROM ".TB_PREF."debtors_master debtor
-				 LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY.","
-				 .TB_PREF."payment_terms terms,"
-				 .TB_PREF."credit_status credit_status
-			WHERE
-					debtor.payment_terms = terms.terms_indicator
-	 			AND debtor.credit_status = credit_status.id";
-	 if ($customer_id)
-		$sql .= " AND debtor.debtor_no = ".db_escape($customer_id);
 
+	$sign = "IF(`type` IN(".implode(',',  array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, IF(type=".ST_JOURNAL.",IF(ov_amount>=0,-1,1), 1))";
+	$value = "$sign*(IF(trans.prep_amount, trans.prep_amount, 
+		ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)) ".($all ? '' : "- trans.alloc").")";
+
+
+	$due = "IF (trans.type=10, trans.due_date, trans.tran_date)";
+    $sql = "SELECT debtor.name, debtor.curr_code, terms.terms,
+		debtor.credit_limit, credit_status.dissallow_invoices, credit_status.reason_description,
+
+		Sum(IFNULL($value,0)) AS Balance,
+		Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due,
+		Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1,
+		Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2
+
+		FROM ".TB_PREF."debtors_master debtor
+			 LEFT JOIN ".TB_PREF."debtor_trans trans ON 
+			 trans.tran_date <= '$todate' AND debtor.debtor_no = trans.debtor_no AND trans.type <> 13 AND trans.type <> 14,
+			 ".TB_PREF."payment_terms terms,
+			 ".TB_PREF."credit_status credit_status
+
+		WHERE
+			 debtor.payment_terms = terms.terms_indicator
+ 			 AND debtor.credit_status = credit_status.id
+			 AND debtor.debtor_no = ".db_escape($customer_id)." ";
 	if (!$all)
-		$sql .= " AND ABS(trans.ov_amount) + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc > ".FLOAT_COMP_DELTA;
-	$sql .= " GROUP BY
-			  	debtor.name,
-			  	terms.terms,
-			  	terms.days_before_due,
-			  	terms.day_in_following_month,
-			  	debtor.credit_limit,
-			  	credit_status.dissallow_invoices,
-			  	credit_status.reason_description";
+		$sql .= "AND ABS($value) >= 0.004 ";
+	$sql .= "GROUP BY
+			  debtor.name,
+			  terms.terms,
+			  terms.days_before_due,
+			  terms.day_in_following_month,
+			  debtor.credit_limit,
+			  credit_status.dissallow_invoices,
+			  credit_status.reason_description";
+
     $result = db_query($sql,"The customer details could not be retrieved");
 
     $customer_record = db_fetch($result);