[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5353] trunk/sql/modules/Report.sql
- Subject: SF.net SVN: ledger-smb:[5353] trunk/sql/modules/Report.sql
- From: ..hidden..
- Date: Sat, 15 Dec 2012 08:46:51 +0000
Revision: 5353
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5353&view=rev
Author: einhverfr
Date: 2012-12-15 08:46:51 +0000 (Sat, 15 Dec 2012)
Log Message:
-----------
Stored procedure now returns expected results
Modified Paths:
--------------
trunk/sql/modules/Report.sql
Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql 2012-12-15 08:13:51 UTC (rev 5352)
+++ trunk/sql/modules/Report.sql 2012-12-15 08:46:51 UTC (rev 5353)
@@ -397,7 +397,7 @@
FROM acc_trans ac
JOIN account_link al ON ac.chart_id = al.account_id
WHERE approved AND al.description IN ('AR', 'AP')
- AND transdate <= in_date_to
+ AND (in_date_to is null or transdate <= in_date_to)
GROUP BY trans_id) p ON p.trans_id = a.id
JOIN entity_credit_account eca ON a.entity_credit_account = eca.id
JOIN entity eeca ON eca.entity_id = eeca.id
@@ -462,7 +462,7 @@
SELECT a.id, a.invoice, eeca.id, eca.meta_number, eeca.name,
a.transdate, a.invnumber, a.amount, a.netamount,
- a.amount - a.netamount as tax, a.amount - p.due, p.last_payment,
+ a.amount - a.netamount as tax, a.amount - p.due, p.due, p.last_payment,
a.duedate, a.notes,
a.till, eee.name as employee, mee.name as manager, a.shippingpoint,
a.shipvia, '{}'
@@ -485,52 +485,52 @@
WHERE l.description IN ('AR', 'AP')
GROUP BY ac.trans_id
) p ON p.trans_id = a.id
- JOIN person per ON per.id = a.person_id
- JOIN entity_employee ee ON ee.entity_id = per.entity_id
+ JOIN entity_employee ee ON ee.entity_id = a.person_id
JOIN entity eee ON eee.id = ee.entity_id
- JOIN entity mee ON ee.manager_id = mee.id
JOIN entity_credit_account eca ON a.entity_credit_account = eca.id
JOIN entity eeca ON eca.entity_id = eeca.id
- WHERE (in_account_id IS NULL OR
- EXISTS (select * from acc_trans
- where trans_id = a.id AND chart_id = in_account_id))
- AND (in_entity_name IS NULL
- OR eeca.name ilike in_entity_name || '%')
- AND (in_meta_number IS NULL OR eca.meta_number ilike in_meta_number)
- AND (in_employee_id = ee.entity_id OR in_employee_id IS NULL)
- AND (in_manager_id = mee.id OR in_manager_id IS NULL)
- AND (a.invnumber ilike in_invnumber || '%' OR in_invnumber IS NULL)
- AND (a.ordnumber ilike in_ordnumber || '%' OR in_ordnumber IS NULL)
- AND (a.ponumber ilike in_ponumber || '%' OR in_ponumber IS NULL)
- AND (in_source IS NULL OR
- EXISTS (
- SELECT * from acc_trans where trans_id = a.id
- AND source ilike in_source || '%'
- ))
- AND (in_description IS NULL
- OR a.description @@ plainto_tsquery(in_description))
- AND (in_notes IS NULL OR a.notes @@ plainto_tsquery(in_notes))
- AND (in_shipvia IS NULL OR a.shipvia @@ plainto_tsquery(in_shipvia))
- AND (in_date_from IS NULL OR a.transdate >= in_date_from)
- AND (in_date_to IS NULL OR a.transdate <= in_date_to)
- AND (in_on_hold IS NULL OR in_on_hold = a.on_hold)
- AND (in_taxable IS NULL
- OR (in_taxable
- AND (in_tax_account_id IS NULL
- OR EXISTS (SELECT 1 FROM acc_trans
- WHERE trans_id = a.id
- AND chart_id = in_tax_account_id)
- ))
- OR (NOT in_taxable
- AND NOT EXISTS (SELECT 1
- FROM acc_trans ac
- JOIN account_link al
- ON al.account_id = ac.chart_id
- WHERE ac.trans_id = a.id
- AND al.description ilike '%tax'))
- )
+ LEFT
+ JOIN entity mee ON ee.manager_id = mee.id
+-- WHERE (in_account_id IS NULL OR
+-- EXISTS (select * from acc_trans
+-- where trans_id = a.id AND chart_id = in_account_id))
+-- AND (in_entity_name IS NULL
+-- OR eeca.name ilike in_entity_name || '%')
+-- AND (in_meta_number IS NULL OR eca.meta_number ilike in_meta_number)
+-- AND (in_employee_id = ee.entity_id OR in_employee_id IS NULL)
+-- AND (in_manager_id = mee.id OR in_manager_id IS NULL)
+-- AND (a.invnumber ilike in_invnumber || '%' OR in_invnumber IS NULL)
+-- AND (a.ordnumber ilike in_ordnumber || '%' OR in_ordnumber IS NULL)
+-- AND (a.ponumber ilike in_ponumber || '%' OR in_ponumber IS NULL)
+-- AND (in_source IS NULL OR
+-- EXISTS (
+-- SELECT * from acc_trans where trans_id = a.id
+-- AND source ilike in_source || '%'
+-- ))
+-- AND (in_description IS NULL
+-- OR a.description @@ plainto_tsquery(in_description))
+-- AND (in_notes IS NULL OR a.notes @@ plainto_tsquery(in_notes))
+-- AND (in_shipvia IS NULL OR a.shipvia @@ plainto_tsquery(in_shipvia))
+-- AND (in_date_from IS NULL OR a.transdate >= in_date_from)
+-- AND (in_date_to IS NULL OR a.transdate <= in_date_to)
+-- AND (in_on_hold IS NULL OR in_on_hold = a.on_hold)
+-- AND (in_taxable IS NULL
+-- OR (in_taxable
+-- AND (in_tax_account_id IS NULL
+-- OR EXISTS (SELECT 1 FROM acc_trans
+-- WHERE trans_id = a.id
+-- AND chart_id = in_tax_account_id)
+-- ))
+-- OR (NOT in_taxable
+-- AND NOT EXISTS (SELECT 1
+-- FROM acc_trans ac
+-- JOIN account_link al
+-- ON al.account_id = ac.chart_id
+-- WHERE ac.trans_id = a.id
+-- AND al.description ilike '%tax'))
+-- )
+--
-
LOOP
RETURN NEXT retval;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.