SF.net SVN: ledger-smb:[5282] trunk/sql

[..hidden..]

Revision: 5282
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5282&view=rev
Author:   einhverfr
Date:     2012-11-28 07:02:24 +0000 (Wed, 28 Nov 2012)
Log Message:
-----------
Fixing cash impact view used for cash basis reporting

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Fixes.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-11-28 04:17:52 UTC (rev 5281)
+++ trunk/sql/Pg-database.sql	2012-11-28 07:02:24 UTC (rev 5282)
@@ -4772,23 +4772,29 @@
 $$ This view provides join and approval information for transactions.$$;
 
 CREATE VIEW cash_impact AS
-SELECT id, '1'::numeric AS portion, 'gl' as rel FROM gl
+SELECT id, '1'::numeric AS portion, 'gl' as rel, gl.transdate FROM gl
 UNION ALL
-SELECT id, CASE WHEN (gl.amount - sum(ac.amount)) = 0 THEN 0 
-                ELSE gl.amount / (gl.amount - sum(ac.amount))
-                END , 'ar' as rel
+SELECT id, CASE WHEN gl.amount = 0 THEN 0 -- avoid div by 0
+                WHEN gl.transdate = ac.transdate
+                     THEN 1 + sum(ac.amount) / gl.amount
+                ELSE 
+                     1 - (gl.amount - sum(ac.amount)) / gl.amount
+                END , 'ar' as rel, ac.transdate
   FROM ar gl
   JOIN acc_trans ac ON ac.trans_id = gl.id
   JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AR'
- GROUP BY gl.id, gl.amount
+ GROUP BY gl.id, gl.amount, ac.transdate
 UNION ALL
-SELECT id, CASE WHEN (gl.amount - sum(ac.amount)) = 0 THEN 0 
-                ELSE gl.amount / (gl.amount - sum(ac.amount))
-            END, 'ap' as rel
+SELECT id, CASE WHEN gl.amount = 0 THEN 0
+                WHEN gl.transdate = ac.transdate
+                     THEN 1 - sum(ac.amount) / gl.amount
+                ELSE 
+                     1 - (gl.amount + sum(ac.amount)) / gl.amount
+            END, 'ap' as rel, ac.transdate
   FROM ap gl
   JOIN acc_trans ac ON ac.trans_id = gl.id
   JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AP'
- GROUP BY gl.id, gl.amount;
+ GROUP BY gl.id, gl.amount, ac.transdate;
 
 COMMENT ON VIEW cash_impact IS
 $$ This view is used by cash basis reports to determine the fraction of a

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2012-11-28 04:17:52 UTC (rev 5281)
+++ trunk/sql/modules/Fixes.sql	2012-11-28 07:02:24 UTC (rev 5282)
@@ -75,3 +75,35 @@
 ALTER TABLE country_tax_form ADD is_accrual bool not null default false;
 
 COMMIT;
+
+BEGIN;
+
+CREATE VIEW cash_impact AS
+SELECT id, '1'::numeric AS portion, 'gl' as rel, gl.transdate FROM gl
+UNION ALL
+SELECT id, CASE WHEN gl.amount = 0 THEN 0 -- avoid div by 0
+                WHEN gl.transdate = ac.transdate
+                     THEN 1 + sum(ac.amount) / gl.amount
+                ELSE 
+                     1 - (gl.amount - sum(ac.amount)) / gl.amount
+                END , 'ar' as rel, ac.transdate
+  FROM ar gl
+  JOIN acc_trans ac ON ac.trans_id = gl.id
+  JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AR'
+ GROUP BY gl.id, gl.amount, ac.transdate
+UNION ALL
+SELECT id, CASE WHEN gl.amount = 0 THEN 0
+                WHEN gl.transdate = ac.transdate
+                     THEN 1 - sum(ac.amount) / gl.amount
+                ELSE 
+                     1 - (gl.amount + sum(ac.amount)) / gl.amount
+            END, 'ap' as rel, ac.transdate
+  FROM ap gl
+  JOIN acc_trans ac ON ac.trans_id = gl.id
+  JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AP'
+ GROUP BY gl.id, gl.amount, ac.transdate;
+
+COMMENT ON VIEW cash_impact IS
+$$ This view is used by cash basis reports to determine the fraction of a
+transaction to be counted.$$;
+COMMIT;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.