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

[..hidden..]

Revision: 5357
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5357&view=rev
Author:   einhverfr
Date:     2012-12-15 09:28:26 +0000 (Sat, 15 Dec 2012)
Log Message:
-----------
Correcting some SQL errors on outstanding report, fixing unapproved transactions showing up on transactions reports

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

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2012-12-15 09:24:11 UTC (rev 5356)
+++ trunk/sql/modules/Report.sql	2012-12-15 09:28:26 UTC (rev 5357)
@@ -384,15 +384,17 @@
        a.amount - p.due as paid, p.due, p.last_payment, a.duedate, a.notes,
        a.till, ee.name, me.name, a.shippingpoint, a.shipvia, 
        '{}' as business_units -- TODO
-  FROM (SELECT id, invoice, transdate, amount, duedate, notes, till, on_hold
-               shippingpoint, shipvia, entity_credit_account, person_id,
-               on_hold
-          FROM ar WHERE in_entity_class = 2 and approved
+  FROM (select id, transdate, invnumber, amount, netamount, duedate, notes, 
+               till, person_id, entity_credit_account, invoice, shippingpoint,
+               shipvia, ordnumber, ponumber, description, on_hold
+          FROM ar
+         WHERE in_entity_class = 2 and approved
          UNION
-        SELECT id, invoice, transdate, amount, duedate, notes, null, on_hold
-               shippingpoint, shipvia, entity_credit_account, person_id, 
-               on_hold
-          FROM ar WHERE in_entity_class = 1 and approved) a
+        SELECT id, transdate, invnumber, amount, netamount, duedate, notes,
+               null, person_id, entity_credit_account, invoice, shippingpoint,
+               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
           FROM acc_trans ac
           JOIN account_link al ON ac.chart_id = al.account_id
@@ -471,13 +473,13 @@
                till, person_id, entity_credit_account, invoice, shippingpoint,
                shipvia, ordnumber, ponumber, description, on_hold
           FROM ar
-         WHERE in_entity_class = 2
+         WHERE in_entity_class = 2 and approved
          UNION
         SELECT id, transdate, invnumber, amount, netamount, duedate, notes,
                null, person_id, entity_credit_account, invoice, shippingpoint,
                shipvia, ordnumber, ponumber, description, on_hold
           FROM ap 
-         WHERE in_entity_class = 1) a 
+         WHERE in_entity_class = 1 and approved) a 
   JOIN (select sum(amount) * case when in_entity_class = 1 THEN 1 ELSE -1 END
                as due, trans_id, max(transdate) as last_payment
           FROM acc_trans ac

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