SF.net SVN: ledger-smb:[3570] trunk

[..hidden..]

Revision: 3570
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3570&view=rev
Author:   einhverfr
Date:     2011-07-26 21:02:23 +0000 (Tue, 26 Jul 2011)

Log Message:
-----------
Changing ar/ap transaction report to hit approved payments only

Modified Paths:
--------------
    trunk/LedgerSMB/AA.pm
    trunk/sql/modules/Payment.sql

Modified: trunk/LedgerSMB/AA.pm
===================================================================
--- trunk/LedgerSMB/AA.pm	2011-07-26 19:55:39 UTC (rev 3569)
+++ trunk/LedgerSMB/AA.pm	2011-07-26 21:02:23 UTC (rev 3570)
@@ -835,14 +835,6 @@
 
     my @paidargs = ();
     if ( $form->{outstanding} ) {
-        $paid = qq|
-			SELECT SUM(ac.amount) * -1 * $ml
-			  FROM acc_trans ac
-			  JOIN chart c ON (c.id = ac.chart_id AND charttype = 'A')
-			 WHERE ac.trans_id = a.id
-			       AND ($approved OR ac.approved)
-			       AND (c.link LIKE '%${ARAP}_paid%' 
-			       OR c.link = '')|;
         if ( $form->{transdateto} ) {
             $paid .= qq|
 			       AND ac.transdate <= ?|;
@@ -951,7 +943,8 @@
     } else {
         $query = qq|
 		   SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
-		          a.duedate, a.netamount, a.amount, ($paid) AS paid,
+		          a.duedate, a.netamount, a.amount, 
+                          (a.amount - pd.due) AS paid,
 		          a.invoice, a.datepaid, a.terms, a.notes,
 		          a.shipvia, a.shippingpoint, ee.name AS employee, 
 		          vce.name, vc.meta_number,
@@ -965,6 +958,18 @@
 		     JOIN entity_credit_account vc ON (a.entity_credit_account = vc.id)
                      JOIN acc_trans ac ON (a.id = ac.trans_id)
                      JOIN chart c ON (c.id = ac.chart_id)
+                     JOIN (SELECT acc_trans.trans_id,
+                                sum(CASE WHEN '$table' = 'ap' THEN amount
+                                         WHEN '$table' = 'ar'
+                                         THEN amount * -1
+                                    END) AS due
+                           FROM acc_trans
+                           JOIN account coa ON (coa.id = acc_trans.chart_id)
+                           JOIN account_link al ON (al.account_id = coa.id)
+                          WHERE ((al.description = 'AP' AND '$table' = 'ap')
+                                OR (al.description = 'AR' AND '$table' = 'ar'))
+                          AND (approved IS TRUE)
+                       GROUP BY acc_trans.trans_id) pd ON (a.id = pd.trans_id)
 		LEFT JOIN entity_employee e ON (a.person_id = e.entity_id)
 		LEFT JOIN entity_employee m ON (e.manager_id = m.entity_id)
 		LEFT JOIN entity ee ON (e.entity_id = ee.id)
@@ -981,7 +986,7 @@
                           a.duedate, a.netamount, a.amount,
                           a.invoice, a.datepaid, a.terms, a.notes,
                           a.shipvia, a.shippingpoint, ee.name , 
-                          vce.name, vc.meta_number, ($paid),
+                          vce.name, vc.meta_number, a.amount, pd.due,
                           vc.entity_id, a.till, me.name, a.curr,
                           ex.$buysell, a.ponumber,
                           d.description $group_by_fields|;

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2011-07-26 19:55:39 UTC (rev 3569)
+++ trunk/sql/modules/Payment.sql	2011-07-26 21:02:23 UTC (rev 3570)
@@ -410,12 +410,16 @@
 business_type: integer of business.id.
 currency: char(3) of currency (for example 'USD')
 date_from, date_to:  These dates are inclusive.
-1;3B
 batch_id:  For payment batches, where fees are concerned.
 ar_ap_accno:  The AR/AP account number.
 
 This then returns a set of contact information with a 2 dimensional array 
 cnsisting of outstanding invoices.
+
+Note that the payment selection logic is that this returns all invoices which are
+either approved or in the batch_id specified.  It also locks the invoices using 
+the LedgerSMB discretionary locking framework, and if not possible, returns the 
+username of the individual who has the lock.
 $$;
 
 CREATE OR REPLACE FUNCTION payment_bulk_queue


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