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

[..hidden..]

Revision: 6487
          http://sourceforge.net/p/ledger-smb/code/6487
Author:   einhverfr
Date:     2014-01-17 03:16:07 +0000 (Fri, 17 Jan 2014)
Log Message:
-----------
Fixing handling of money places in rounded tax module.
Added force_closed handling to ar/ap transaction, outstanding, and aging reports.
force_closed is not yet extended to the user interface.  We should discuss on the list the relationship between onhold and force_closed.

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

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2014-01-17 03:01:54 UTC (rev 6486)
+++ trunk/sql/modules/Report.sql	2014-01-17 03:16:07 UTC (rev 6487)
@@ -80,7 +80,7 @@
                    (coalesce(in_to_date, now())::date - a.transdate) as age
 		  FROM (select id, invnumber, till, ordnumber, amount, duedate,
                                curr, ponumber, notes, entity_credit_account,
-                               -1 AS sign, transdate,
+                               -1 AS sign, transdate, force_closed,
                                CASE WHEN in_use_duedate 
                                     THEN coalesce(in_to_date, now())::date
                                          - duedate
@@ -92,7 +92,7 @@
                          UNION 
                         SELECT id, invnumber, null, ordnumber, amount, duedate,
                                curr, ponumber, notes, entity_credit_account,
-                               1 as sign, transdate,
+                               1 as sign, transdate, force_closed,
                                CASE WHEN in_use_duedate 
                                     THEN coalesce(in_to_date, now())::date
                                          - duedate
@@ -120,6 +120,7 @@
 	     LEFT JOIN country ON (country.id = l.country_id)
                  WHERE (e.id = in_entity_id OR in_entity_id IS NULL)
                        AND (in_accno IS NULL or acc.accno = in_accno)
+                       AND NOT a.force_closed
               GROUP BY c.entity_id, c.meta_number, e.name,
                        l.line_one, l.line_two, l.line_three,
                        l.city, l.state, l.mail_code, country.name,
@@ -386,13 +387,13 @@
        '{}' as business_units -- TODO
   FROM (select id, transdate, invnumber, amount, netamount, duedate, notes, 
                till, person_id, entity_credit_account, invoice, shippingpoint,
-               shipvia, ordnumber, ponumber, description, on_hold
+               shipvia, ordnumber, ponumber, description, on_hold, force_closed
           FROM ar
          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
+               shipvia, ordnumber, ponumber, description, on_hold, force_closed
           FROM ap 
          WHERE in_entity_class = 1 and approved) a 
   LEFT
@@ -423,7 +424,8 @@
        AND (in_on_hold IS NULL OR in_on_hold = a.on_hold)
        AND (in_from_date IS NULL OR a.transdate >= in_from_date)
        AND (in_to_date IS NULL OR a.transdate <= in_to_date)
-       AND p.due::numeric(100,2) <> 0
+       AND p.due::numeric(100,2) <> 0 
+       AND NOT a.force_closed
        AND (in_partnumber IS NULL 
           OR EXISTS(SELECT 1 FROM invoice inv 
                       JOIN parts ON inv.parts_id = parts.id
@@ -483,13 +485,13 @@
        
   FROM (select id, transdate, invnumber, amount, netamount, duedate, notes, 
                till, person_id, entity_credit_account, invoice, shippingpoint,
-               shipvia, ordnumber, ponumber, description, on_hold
+               shipvia, ordnumber, ponumber, description, on_hold, force_closed
           FROM ar
          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
+               shipvia, ordnumber, ponumber, description, on_hold, force_closed
           FROM ap 
          WHERE in_entity_class = 1 and approved) a 
   LEFT
@@ -546,10 +548,11 @@
                                          AND al.description ilike '%tax'))
             )
             AND ( -- open/closed handling
-              (in_open IS TRUE AND abs(p.due) > 0.005) -- threshold due to 
+              (in_open IS TRUE AND ( a.force_closed IS NOT TRUE AND
+                 abs(p.due) > 0.005))                  -- threshold due to 
                                                        -- impossibility to 
                                                        -- collect below -CT
-              OR (in_closed IS TRUE AND abs(p.due) < 0.005 )
+              OR (in_closed IS TRUE AND (a.force_closed OR abs(p.due) < 0.005 ))
             )
 
 LOOP

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


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits