From 03688c1557a42e17bff85f01aa7af6cef66a70a1 Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Wed, 14 May 2008 21:16:26 +0000
Subject: ar_ap_summary_fix now handles transactions with negative ammounts
 properly.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.2@2143 4979c152-3d1c-0410-bac9-87ea11338e46
---
 sql/fixes/ar_ap_summary_fix_1.2.14.sql | 56 ++++++++++++++++++++++++++--------
 1 file changed, 43 insertions(+), 13 deletions(-)

(limited to 'sql')

diff --git a/sql/fixes/ar_ap_summary_fix_1.2.14.sql b/sql/fixes/ar_ap_summary_fix_1.2.14.sql
index f4a86bee..5f8bbdc5 100644
--- a/sql/fixes/ar_ap_summary_fix_1.2.14.sql
+++ b/sql/fixes/ar_ap_summary_fix_1.2.14.sql
@@ -2,30 +2,60 @@ BEGIN;
 
 UPDATE ap 
 SET netamount = 
-	(select sum(amount) from acc_trans 
-	where trans_id = ap.id 
-		AND ((chart_id IN (select id from chart where link = 'AP')
-			     AND amount > 0)
-			OR (chart_id IN 
-				(select id from chart where link like '%tax%')
+	CASE WHEN amount > 0 THEN -1 *
+		(select sum(amount) from acc_trans 
+		where trans_id = ap.id 
+			AND ((chart_id IN (select id from chart where link = 'AP')
+				     AND amount > 0)
+				OR (chart_id IN 
+					(select id from chart where link like '%tax%')
+				)
+			)
+		)
+	ELSE
+		-1 *
+		(select sum(amount) from acc_trans 
+		where trans_id = ap.id 
+			AND ((chart_id IN 
+				(select id from chart where link = 'AP')
+				     AND amount < 0)
+				OR (chart_id IN 
+					(select id from chart 
+					where link like '%tax%')
+				)
 			)
 		)
-	)
+	END
 WHERE netamount IS NULL;
 
 update ap set datepaid = NULL where paid = 0;
 
 UPDATE ar
-SET netamount = -1 *
-	(select sum(amount) from acc_trans 
-	where trans_id = ar.id 
-		AND ((chart_id IN (select id from chart where link = 'AR')
-			     AND amount < 0)
+SET netamount = 
+	CASE WHEN amount > 0 THEN -1 *
+		(select sum(amount) from acc_trans 
+		where trans_id = ar.id 
+			AND ((chart_id IN 
+				(select id from chart where link = 'AR')
+				     AND amount < 0)
+				OR (chart_id IN 
+					(select id from chart 
+					where link like '%tax%')
+				)
+			)
+		)
+	ELSE 
+		(select sum(amount) from acc_trans 
+		where trans_id = ar.id 
+			AND ((chart_id IN (select id from chart 
+				where link = 'AR')
+				     AND amount > 0)
 			OR (chart_id IN 
 				(select id from chart where link like '%tax%')
+				)
 			)
 		)
-	)
+	END
 WHERE netamount IS NULL;
 
 update ar set datepaid = NULL where paid = 0;
-- 
cgit v1.2.3