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

[..hidden..]

Revision: 4843
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4843&view=rev
Author:   einhverfr
Date:     2012-06-05 14:37:56 +0000 (Tue, 05 Jun 2012)
Log Message:
-----------
fixes for transaction reports

Modified Paths:
--------------
    trunk/LedgerSMB/DBObject/Report/Contact/Purchase.pm
    trunk/sql/modules/arap.sql

Modified: trunk/LedgerSMB/DBObject/Report/Contact/Purchase.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/Contact/Purchase.pm	2012-06-05 13:50:13 UTC (rev 4842)
+++ trunk/LedgerSMB/DBObject/Report/Contact/Purchase.pm	2012-06-05 14:37:56 UTC (rev 4843)
@@ -57,6 +57,9 @@
          {col_id => 'id',
             type => 'text',
             name => $locale->text('ID') },
+         {col_id => 'entity_name',
+            type => 'text',
+            name => $locale->text('Name') },
 
          {col_id => 'invnumber',
             type => 'href',
@@ -304,9 +307,11 @@
        );
     } else {
        @rows = $self->exec_method({funcname => 'ar_ap__transaction_search'});
+       my $rn = 0;
        for my $r (@rows){
+            $r->{running_number} = ++$rn;
             my $href;
-            if ($r->invoice){
+            if ($r->{invoice}){
                 if ($self->entity_class == 1) {
                     $href = 'ir.pl';
                 } else {

Modified: trunk/sql/modules/arap.sql
===================================================================
--- trunk/sql/modules/arap.sql	2012-06-05 13:50:13 UTC (rev 4842)
+++ trunk/sql/modules/arap.sql	2012-06-05 14:37:56 UTC (rev 4843)
@@ -4,6 +4,7 @@
 
 CREATE TYPE purchase_info AS (
     id int,
+    invoice bool,
     invnumber text,
     ordnumber text,
     ponumber text,
@@ -33,7 +34,8 @@
 $$
 BEGIN
 RETURN QUERY
-   SELECT gl.id, gl.invnumber, gl.ordnumber, gl.ponumber, gl.transdate, 
+   SELECT gl.id, gl.invoice, 
+          gl.invnumber, gl.ordnumber, gl.ponumber, gl.transdate, 
           e.name, eca.meta_number::text, e.id, gl.amount, 
           gl.amount - sum(CASE WHEN l.description IN ('AR', 'AP')
                                THEN ac.amount ELSE 0 
@@ -41,14 +43,16 @@
           gl.amount - gl.netamount, gl.curr, gl.datepaid, gl.duedate, 
           gl.notes, gl.shippingpoint, gl.shipvia, 
           compound_array(bua.business_units || bui.business_units)
-     FROM (select id, invnumber, ordnumber, ponumber, transdate, duedate,
+     FROM (select id, invoice, invnumber, ordnumber, ponumber, transdate, duedate,
                   description, notes, shipvia, shippingpoint, amount, 
-                  netamount, curr, datepaid, entity_credit_account, on_hold
+                  netamount, curr, datepaid, entity_credit_account, on_hold,
+                  approved
              FROM ar WHERE in_entity_class = 2
             UNION
-           select id, invnumber, ordnumber, ponumber, transdate, duedate,
+           select id, invoice, invnumber, ordnumber, ponumber, transdate, duedate,
                   description, notes, shipvia, shippingpoint, amount, 
-                  netamount, curr, datepaid, entity_credit_account, on_hold
+                  netamount, curr, datepaid, entity_credit_account, on_hold,
+                  approved
              FROM ap WHERE in_entity_class = 1) gl
      JOIN entity_credit_account eca ON gl.entity_credit_account = eca.id
      JOIN entity e ON e.id = eca.entity_id
@@ -90,11 +94,12 @@
           AND (in_to_date IS NULL OR in_to_date >= gl.transdate)
           AND (in_on_hold IS NULL OR in_on_hold = gl.on_hold)
           AND (in_as_of IS NULL OR in_as_of >= ac.transdate)
+          AND gl.approved AND ac.approved 
  GROUP BY gl.id, gl.invnumber, gl.ordnumber, gl.ponumber, gl.transdate,
           gl.duedate, e.name, eca.meta_number, gl.amount,
           gl.netamount, gl.curr, gl.datepaid, gl.duedate,
-          gl.notes, gl.shippingpoint, gl.shipvia, e.id
-   HAVING in_source = ANY(array_agg(ac.source));
+          gl.notes, gl.shippingpoint, gl.shipvia, e.id, gl.invoice
+   HAVING in_source = ANY(array_agg(ac.source)) or in_source IS NULL;
 END;
 $$ LANGUAGE PLPGSQL;
 CREATE OR REPLACE FUNCTION ar_ap__transaction_search_summary

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