SF.net SVN: ledger-smb:[5712] addons/1.4

[..hidden..]

Revision: 5712
          http://sourceforge.net/p/ledger-smb/code/5712
Author:   einhverfr
Date:     2013-04-15 02:19:55 +0000 (Mon, 15 Apr 2013)
Log Message:
-----------
Adding product_ledger stored procedure.

Added Paths:
-----------
    addons/1.4/product_ledger/
    addons/1.4/product_ledger/sql/
    addons/1.4/product_ledger/sql/modules/
    addons/1.4/product_ledger/sql/modules/Product_Ledger.sql

Added: addons/1.4/product_ledger/sql/modules/Product_Ledger.sql
===================================================================
--- addons/1.4/product_ledger/sql/modules/Product_Ledger.sql	                        (rev 0)
+++ addons/1.4/product_ledger/sql/modules/Product_Ledger.sql	2013-04-15 02:19:55 UTC (rev 5712)
@@ -0,0 +1,33 @@
+BEGIN;
+
+DROP TYPE IF EXISTS report__product_ledger CASCADE;
+
+CREATE TYPE report__product_ledger AS (
+    invnumber text,
+    transdate date,
+    name text,
+    count_in numeric,
+    count_out numeric,
+    running_total numeric
+);
+    
+
+CREATE OR REPLACE FUNCTION report__product_ledger(in_parts_id int)
+RETURNS SETOF report__product_ledger language sql as $$
+
+SELECT a.invnumber, a.transdate, e.name, 
+       CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE null END AS count_in,  
+       CASE WHEN i.qty > 0 THEN i.qty ELSE null END AS count_out,
+       sum(qty) over (order by a.transdate, a.id, a.id) * -1 as running_total
+  from entity e
+  join entity_credit_account eca on eca.entity_id  = e.id
+  join (select id, transdate, entity_credit_account, invnumber
+          from ar
+         union 
+        select id, transdate, entity_credit_account, invnumber
+          from ap) a on a.entity_credit_account = eca.id
+  join invoice i on a.id = i.trans_id
+ where i.parts_id = $1 order by a.transdate, a.id, i.id;
+$$;
+
+COMMIT;

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