SF.net SVN: ledger-smb:[5359] trunk/sql/modules/Report.sql

[..hidden..]

Revision: 5359
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5359&view=rev
Author:   einhverfr
Date:     2012-12-15 10:28:35 +0000 (Sat, 15 Dec 2012)
Log Message:
-----------
Numbers now work for outstanding report

Modified Paths:
--------------
    trunk/sql/modules/Report.sql

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2012-12-15 10:22:09 UTC (rev 5358)
+++ trunk/sql/modules/Report.sql	2012-12-15 10:28:35 UTC (rev 5359)
@@ -395,7 +395,9 @@
                shipvia, ordnumber, ponumber, description, on_hold
           FROM ap 
          WHERE in_entity_class = 1 and approved) a 
-  JOIN (SELECT trans_id, sum(amount) AS due, max(transdate) as last_payment
+  JOIN (SELECT trans_id, sum(amount) * 
+               CASE WHEN in_entity_class = 1 THEN 1 ELSE -1 END AS due,
+               max(transdate) as last_payment
           FROM acc_trans ac
           JOIN account_link al ON ac.chart_id = al.account_id
          WHERE approved AND al.description IN ('AR', 'AP')
@@ -420,6 +422,7 @@
        AND (in_on_hold IS NULL OR in_on_hold = a.on_hold)
        AND (in_date_from IS NULL OR a.transdate >= in_date_from)
        AND (in_date_to IS NULL OR a.transdate <= in_date_to)
+       AND p.due::numeric(100,2) <> 0
 LOOP
    RETURN NEXT retval;
 END LOOP;

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