[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3388] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[3388] trunk/sql/modules
- From: ..hidden..
- Date: Thu, 30 Jun 2011 15:32:02 +0000
Revision: 3388
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3388&view=rev
Author: einhverfr
Date: 2011-06-30 15:32:02 +0000 (Thu, 30 Jun 2011)
Log Message:
-----------
1099 reporting fixes, 4 tests
still failing, probable issues with test cases, bugs causing failures fixed in reporting module
Modified Paths:
--------------
trunk/sql/modules/1099_reports.sql
trunk/sql/modules/Roles.sql
trunk/sql/modules/test/Taxform.sql
Modified: trunk/sql/modules/1099_reports.sql
===================================================================
--- trunk/sql/modules/1099_reports.sql 2011-06-30 10:32:28 UTC (rev 3387)
+++ trunk/sql/modules/1099_reports.sql 2011-06-30 15:32:02 UTC (rev 3388)
@@ -30,27 +30,35 @@
SELECT company.legal_name, company.entity_id,
entity_credit_account.entity_class, entity.control_code,
entity_credit_account.meta_number,
- sum(CASE WHEN relation = 'acc_trans'
+ sum(CASE WHEN gl.amount = 0 THEN 0
+ WHEN relation = 'acc_trans'
THEN ac.reportable_amount * pmt.amount
- / ac.amount
+ / gl.amount
ELSE 0
- END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end),
- sum(CASE WHEN relation = 'invoice'
+ END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end),
+ sum(CASE WHEN gl.amount = 0 THEN 0
+ WHEN relation = 'invoice'
THEN ac.reportable_amount * pmt.amount
- / ac.amount
+ / gl.amount
ELSE 0
- END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end)
+ END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end),
+ sum(CASE WHEN gl.amount = 0 THEN 0
+ ELSE ac.reportable_amount * pmt.amount
+ / gl.amount
+ END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end)
- FROM (select id, transdate, entity_credit_account, 'ar' as class FROM ar
+ FROM (select id, transdate, entity_credit_account, invoice,
+ amount, 'ar' as class FROM ar
UNION
- select id, transdate, entity_credit_account, 'ap' as class from ap
+ select id, transdate, entity_credit_account, invoice,
+ amount, 'ap' as class from ap
) gl
JOIN (select trans_id, 'acc_trans' as relation,
sum(amount) as amount,
sum(case when atf.reportable then amount else 0
end) as reportable_amount
FROM acc_trans
- JOIN ac_tax_form atf
+ LEFT JOIN ac_tax_form atf
ON (acc_trans.entry_id = atf.entry_id)
GROUP BY trans_id
UNION
@@ -61,10 +69,13 @@
else 0
end) as reportable_amount
FROM invoice
- JOIN invoice_tax_form itf
+ LEFT JOIN invoice_tax_form itf
ON (invoice.id = itf.invoice_id)
GROUP BY trans_id
- ) ac ON (ac.trans_id = gl.id)
+ ) ac ON (ac.trans_id = gl.id
+ AND ((gl.invoice is true and ac.relation='invoice')
+ OR (gl.invoice is false
+ and ac.relation='acc_trans')))
JOIN (SELECT ac.trans_id, sum(ac.amount) as amount,
as_array(entry_id) as entry_ids,
as_array(chart_id) as chart_ids,
@@ -98,23 +109,30 @@
SELECT company.legal_name, company.entity_id,
entity_credit_account.entity_class, entity.control_code,
entity_credit_account.meta_number,
- sum(CASE WHEN relation = 'acc_trans'
+ sum(CASE WHEN gl.amount = 0 then 0
+ when relation = 'acc_trans'
THEN ac.reportable_amount * pmt.amount
- / ac.amount
+ / gl.amount
ELSE 0
- END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end),
- sum(CASE WHEN relation = 'invoice'
+ END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end),
+ sum(CASE WHEN gl.amount = 0 then 0
+ WHEN relation = 'invoice'
THEN ac.reportable_amount * pmt.amount
- / ac.amount
+ / gl.amount
ELSE 0
- END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end),
- SUM(ac.reportable_amount * pmt.amount
- / ac.amount),
+ END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end),
+ SUM(CASE WHEN gl.amount = 0 THEN 0
+ ELSE ac.reportable_amount * pmt.amount
+ / gl.amount
+ END
+ * CASE WHEN gl.class = 'ap' THEN -1 else 1 end),
gl.invnumber, gl.duedate::text, gl.id
- FROM (select id, entity_credit_account, invnumber, duedate, transdate, 'ar' as class
+ FROM (select id, entity_credit_account, invnumber, duedate,
+ amount, transdate, 'ar' as class
FROM ar
UNION
- select id, entity_credit_account, invnumber, duedate, transdate, 'ap' as class
+ select id, entity_credit_account, invnumber, duedate,
+ amount, transdate, 'ap' as class
FROM ap
) gl
JOIN (select trans_id, 'acc_trans' as relation,
@@ -122,7 +140,7 @@
sum(case when atf.reportable then amount else 0
end) as reportable_amount
FROM acc_trans
- JOIN ac_tax_form atf
+ LEFT JOIN ac_tax_form atf
ON (acc_trans.entry_id = atf.entry_id)
GROUP BY trans_id
UNION
@@ -133,7 +151,7 @@
else 0
end) as reportable_amount
FROM invoice
- JOIN invoice_tax_form itf
+ LEFT JOIN invoice_tax_form itf
ON (invoice.id = itf.invoice_id)
GROUP BY trans_id
) ac ON (ac.trans_id = gl.id)
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2011-06-30 10:32:28 UTC (rev 3387)
+++ trunk/sql/modules/Roles.sql 2011-06-30 15:32:02 UTC (rev 3388)
@@ -367,7 +367,7 @@
IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
"lsmb_<?lsmb dbname ?>__batch_create";
-GRANT INSERT ON ar TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
+GRANT SELECT,INSERT, UPDATE ON ap TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
GRANT ALL ON id TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
Modified: trunk/sql/modules/test/Taxform.sql
===================================================================
--- trunk/sql/modules/test/Taxform.sql 2011-06-30 10:32:28 UTC (rev 3387)
+++ trunk/sql/modules/test/Taxform.sql 2011-06-30 15:32:02 UTC (rev 3388)
@@ -345,10 +345,10 @@
where meta_number = 'Test account 1';
INSERT INTO test_result(test_name, success)
-SELECT 'ac_sum for test vendor 1, current report is $1000', acc_sum = 1000
+SELECT 'ac_sum for test vendor 2, current report is $1000', acc_sum = 1000
FROM tax_form_summary_report(-511, (date1() - '1 day'::interval)::date,
(date1() + '1 day'::interval)::date)
- where meta_number = 'Test account 1';
+ where meta_number = 'Test account 2';
INSERT INTO test_result(test_name, success)
SELECT '6 in detail report for current report, vendor 1', count(*) = 6
@@ -602,4 +602,13 @@
|| (select count(*) from test_result where success is not true)
|| ' failed' as message;
+
+SELECT *
+ FROM tax_form_details_report(-511, (date1() - '1 day'::interval)::date,
+ (date1() + '1 day'::interval)::date,
+ 'Test account 2')
+ WHERE invoice_id = -1034;
+SELECT *
+ FROM tax_form_summary_report(-511, (date1() - '1 day'::interval)::date,
+ (date1() + '1 day'::interval)::date);
ROLLBACK;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.