summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-05-14 21:16:26 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-05-14 21:16:26 +0000
commit03688c1557a42e17bff85f01aa7af6cef66a70a1 (patch)
treeffe0e83a511382db89ad3ff0b01cb2036312b3f7 /sql
parent17f9a5b0aad5203072355f5b55957d3c12edffdb (diff)
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
Diffstat (limited to 'sql')
-rw-r--r--sql/fixes/ar_ap_summary_fix_1.2.14.sql56
1 files changed, 43 insertions, 13 deletions
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;