[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[3388] trunk/sql/modules



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.