diff options
Diffstat (limited to 'sql/fixes/ar_ap_summary_fix_1.2.14.sql')
| -rw-r--r-- | sql/fixes/ar_ap_summary_fix_1.2.14.sql | 56 |
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; |