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

[..hidden..]

Revision: 4964
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4964&view=rev
Author:   einhverfr
Date:     2012-07-10 10:14:33 +0000 (Tue, 10 Jul 2012)
Log Message:
-----------
Partial PNL reports for LedgerSMB 1.3 by customer and invoice.

Modified Paths:
--------------
    trunk/LedgerSMB/DBObject_Moose.pm
    trunk/sql/modules/LOADORDER

Added Paths:
-----------
    trunk/sql/modules/PNL.sql

Modified: trunk/LedgerSMB/DBObject_Moose.pm
===================================================================
--- trunk/LedgerSMB/DBObject_Moose.pm	2012-07-10 09:15:05 UTC (rev 4963)
+++ trunk/LedgerSMB/DBObject_Moose.pm	2012-07-10 10:14:33 UTC (rev 4964)
@@ -133,6 +133,22 @@
 
 =back
 
+=head1 COERCIONS
+
+=over
+
+=item Str -> LedgerSMB::PGNumber via from_input()
+
+=item Str -> LedgerSMB::PGDate via from_input
+
+=cut
+
+coerce 'LedgerSMB::PGNumber' from 'Str' 
+   via { LedgerSMB::PGNumber->from_input($_) };
+
+coerce 'LedgerSMB::PGDate' from 'Str' 
+   via { LedgerSMB::PGDate->from_input($_) };
+
 =head1 Copyright (C) 2007, The LedgerSMB core team.
 
 This file is licensed under the Gnu General Public License version 2, or at your

Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER	2012-07-10 09:15:05 UTC (rev 4963)
+++ trunk/sql/modules/LOADORDER	2012-07-10 10:14:33 UTC (rev 4964)
@@ -18,6 +18,7 @@
 Entity.sql
 Payment.sql
 Person.sql
+PNL.sql
 Report.sql
 Voucher.sql
 admin.sql

Added: trunk/sql/modules/PNL.sql
===================================================================
--- trunk/sql/modules/PNL.sql	                        (rev 0)
+++ trunk/sql/modules/PNL.sql	2012-07-10 10:14:33 UTC (rev 4964)
@@ -0,0 +1,56 @@
+-- Copyright 2012 The LedgerSMB Core Team.  This file may be re-used in 
+-- accordance with the GNU GPL version 2 or at your option any later version.  
+-- Please see your included LICENSE.txt for details
+
+BEGIN;
+
+-- This holds general PNL type report definitions.  The idea is to gather them
+-- here so that they share as many common types as possible.  Note that PNL 
+-- reports do not return total and summary lines.  These must be done by the 
+-- application handling this. 
+
+DROP TYPE IF EXISTS pnl_line CASCADE;
+CREATE TYPE pnl_line AS (
+    account_id int,
+    account_number text,
+    account_description text,
+    account_category text,
+    account_heading_id int,
+    account_heading_number text,
+    account_heading_description text,
+    amount numeric
+);
+
+CREATE OR REPLACE FUNCTION pnl__customer(in_id int) RETURNS SETOF pnl_line AS
+$$
+WITH gl (id) AS
+ ( SELECT id FROM ap WHERE approved is true AND entity_credit_account = $1
+UNION ALL
+   SELECT id FROM ar approved is true AND entity_credit_account = $1
+)
+SELECT a.id, a.accno, a.description, a.category, 
+       ah.id, ah.accno, ah.description,
+       CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * sum(ac.amount)
+  FROM account a
+  JOIN acc_trans ac ON a.id = ac.chart_id
+  JOIN gl ON ac.trans_id = gl.id
+ WHERE ac.approved is true
+ GROUP BY a.id, a.accno, a.description, a.category, 
+          ah.id, ah.accno, ah.description
+ ORDER BY a.category DESC, a.accno ASC;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION pnl__invoice(in_id int) RETURNS SETOF pnl_line AS
+$$
+SELECT a.id, a.accno, a.description, a.category, 
+       ah.id, ah.accno, ah.description,
+       CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * sum(ac.amount)
+  FROM account a
+  JOIN acc_trans ac ON a.id = ac.chart_id
+ WHERE ac.approved is true AND ac.trans_id = $1
+ GROUP BY a.id, a.accno, a.description, a.category, 
+          ah.id, ah.accno, ah.description
+ ORDER BY a.category DESC, a.accno ASC;
+$$ LANGUAGE SQL;
+
+COMMIT;

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