[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4737] trunk/sql/modules/Report.sql
- Subject: SF.net SVN: ledger-smb:[4737] trunk/sql/modules/Report.sql
- From: ..hidden..
- Date: Sun, 20 May 2012 13:48:53 +0000
Revision: 4737
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4737&view=rev
Author: einhverfr
Date: 2012-05-20 13:48:53 +0000 (Sun, 20 May 2012)
Log Message:
-----------
Consolidation and correction of new aging report logic
Modified Paths:
--------------
trunk/sql/modules/Report.sql
Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql 2012-05-20 11:36:31 UTC (rev 4736)
+++ trunk/sql/modules/Report.sql 2012-05-20 13:48:53 UTC (rev 4737)
@@ -15,9 +15,6 @@
mail_code text,
country text,
contact_name text,
- email text,
- phone text,
- fax text,
invnumber text,
transdate date,
till varchar(20),
@@ -36,8 +33,9 @@
);
-CREATE OR REPLACE FUNCTION
-report__invoice_aging(in_entity_id int, in_entity_class int)
+CREATE OR REPLACE FUNCTION report__invoice_aging_detail
+(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
+ in_business_units int[]))
RETURNS SETOF report_aging_item
AS
$$
@@ -45,117 +43,109 @@
item report_aging_item;
BEGIN
IF in_entity_class = 1 THEN
- FOR item IN
- SELECT c.entity_id,
- c.meta_number, e.name,
- l.line_one as address1, l.line_two as address2,
- l.line_three as address3,
- l.city_province, l.mail_code,
- country.name as country,
- '' as contact_name, '' as email,
- '' as phone, '' as fax,
- a.invnumber, a.transdate, a.till, a.ordnumber,
- a.ponumber, a.notes,
- CASE WHEN
- EXTRACT(days FROM age(a.transdate)/30)
- = 0
- THEN (a.amount - a.paid) ELSE 0 END
- as c0,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- = 1
- THEN (a.amount - a.paid) ELSE 0 END
- as c30,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- = 2
- THEN (a.amount - a.paid) ELSE 0 END
- as c60,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- > 2
- THEN (a.amount - a.paid) ELSE 0 END
- as c90,
- a.duedate, a.id, a.curr,
- COALESCE((SELECT sell FROM exchangerate ex
- WHERE a.curr = ex.curr
- AND ex.transdate = a.transdate), 1)
- AS exchangerate,
- (SELECT compound_array(ARRAY[[p.partnumber,
- i.description, i.qty::text]])
- FROM parts p
- JOIN invoice i ON (i.parts_id = p.id)
- WHERE i.trans_id = a.id) AS line_items
- FROM ap a
- JOIN entity_credit_account c USING (entity_id)
- JOIN entity e ON (e.id = c.entity_id)
- CROSS JOIN location l
- JOIN country ON (country.id = l.country_id)
- WHERE a.entity_id like coalesce(in_entity_id::text, '%')
- AND l.id = (SELECT min(location_id)
- FROM company_to_location
- WHERE company_id = (select min(id)
- FROM company
- WHERE entity_id = c.entity_id))
- ORDER BY entity_id, curr, transdate, invnumber
- LOOP
- return next item;
- END LOOP;
- ELSIF in_entity_class = 2 THEN
- FOR item IN
- SELECT c.entity_id,
- c.meta_number, e.name,
- l.line_one as address1, l.line_two as address2,
- l.line_three as address3,
- l.city_province, l.mail_code,
- country.name as country,
- '' as contact_name, '' as email,
- '' as phone, '' as fax,
- a.invnumber, a.transdate, a.till, a.ordnumber,
- a.ponumber, a.notes,
- CASE WHEN
- EXTRACT(days FROM age(a.transdate)/30)
- = 0
- THEN (a.amount - a.paid) ELSE 0 END
- as c0,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- = 1
- THEN (a.amount - a.paid) ELSE 0 END
- as c30,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- = 2
- THEN (a.amount - a.paid) ELSE 0 END
- as c60,
- CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
- > 2
- THEN (a.amount - a.paid) ELSE 0 END
- as c90,
- a.duedate, a.id, a.curr,
- (SELECT buy FROM exchangerate ex
- WHERE a.curr = ex.curr
- AND ex.transdate = a.transdate)
- AS exchangerate,
- (SELECT compound_array(ARRAY[[p.partnumber,
- i.description, i.qty::text]])
- FROM parts p
- JOIN invoice i ON (i.parts_id = p.id)
- WHERE i.trans_id = a.id) AS line_items
- FROM ar a
- JOIN entity_credit_account c USING (entity_id)
- JOIN entity e ON (e.id = c.entity_id)
- CROSS JOIN location l
- JOIN country ON (country.id = l.country_id)
- WHERE a.entity_id like coalesce(in_entity_id::text, '%')
- AND l.id = (SELECT min(location_id)
- FROM company_to_location
- WHERE company_id = (select min(id)
- FROM company
- WHERE entity_id = c.entity_id))
- ORDER BY entity_id, curr, transdate, invnumber
- LOOP
- return next item;
- END LOOP;
- ELSE
- RAISE EXCEPTION 'Entity Class % unsupported in aging report',
- in_entity_class;
- END IF;
+ FOR item IN
+ WITH RECURSIVE bu_tree (id, path) AS (
+ SELECT id, id::text AS path
+ FROM business_unit
+ WHERE id in(in_business_units)
+ UNION
+ SELECT bu.id, bu_tree.path || ',' || bu.id
+ FROM business_unit bu
+ JOIN bu_tree ON bu_tree.id = bu.parent_id
+ )
+ SELECT c.entity_id, c.meta_number, e.name,
+ l.line_one as address1, l.line_two as address2,
+ l.line_three as address3,
+ l.city_province, l.mail_code, country.name as country,
+ e.name as contact_name,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes,
+ CASE WHEN
+ EXTRACT(days FROM age(min(ac.transdate),
+ coalesce(in_to_date,
+ now())/30)
+ = 0
+ THEN (a.amount - (a.sign * sum(ac.amount))
+ ELSE 0 END
+ as c0,
+ CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
+ coalesce(in_to_date,
+ now())/30)
+ = 1
+ THEN (a.amount - (a.sign * sum(ac.amount))
+ ELSE 0 END
+ as c30,
+ CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
+ coalesce(in_to_date,
+ now())/30)
+ = 2
+ THEN (a.amount - (a.sign * sum(ac.amount),
+ coalesce(in_to_date,
+ now())
+ ELSE 0 END
+ as c60,
+ CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
+ coalesce(in_to_date,
+ now())/30)
+ > 2
+ THEN (a.amount - (a.sign * sum(ac.amount))
+ ELSE 0 END
+ as c90,
+ a.duedate, a.id, a.curr,
+ COALESCE((SELECT sell FROM exchangerate ex
+ WHERE a.curr = ex.curr
+ AND ex.transdate = a.transdate), 1)
+ AS exchangerate,
+ (SELECT compound_array(ARRAY[[p.partnumber,
+ i.description, i.qty::text]])
+ FROM parts p
+ JOIN invoice i ON (i.parts_id = p.id)
+ WHERE i.trans_id = a.id) AS line_items
+ FROM (select id, invnumber, till, ordnumber, amount, duedate,
+ curr, ponumber, notes, entity_credit_account,
+ -1 AS sign
+ FROM ar
+ WHERE in_entity_class = 2
+ UNION
+ SELECT id, invnumber, null, ordnumber, amount, duedate,
+ curr, ponumber, notes, entity_credit_account,
+ 1 as sign
+ FROM ap
+ WHERE in_entity_class = 1) a
+ JOIN acc_trans ac ON ac.trans_id = a.id
+ JOIN account acc ON ac.chart_id = acc.id
+ JOIN account_link acl ON acl.account_id = acc.id
+ AND ((in_entity_class = 1
+ AND acl.description = 'AP')
+ OR (in_entity_class = 2
+ AND acl.description = 'AR'))
+ JOIN entity_credit_account c
+ ON a.entity_credit_account = c.id
+ JOIN entity e ON (e.id = c.entity_id)
+ LEFT JOIN business_unit_ac buac ON ac.entry_id = buac.entry_id
+ LEFT JOIN bu_tree ON buac.bu_id = bu_tree.id
+ LEFT JOIN entity_to_location e2l
+ ON e.id = e2l.entity_id
+ AND e2l.location_class = 3
+ LEFT JOIN location l ON l.id = e2l.location_id
+ 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)
+ GROUP BY c.entity_id, c.meta_number, e.name,
+ l.line_one as address1, l.line_two as address2,
+ l.line_three as address3,
+ l.city_province, l.mail_code, country.name as country,
+ e.name as contact_name,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes,
+ a.duedate, a.id, a.curr
+ HAVING in_business_units is null or in_business_units
+ <@ compound_array(string_to_array(bu_tree.path,
+ ',')::int[])
+ ORDER BY entity_id, curr, transdate, invnumber
+ LOOP
+ return next item;
+ END LOOP;
END;
$$ language plpgsql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.