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

SF.net SVN: ledger-smb:[3709] addons/1.3



Revision: 3709
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3709&view=rev
Author:   einhverfr
Date:     2011-09-19 02:56:41 +0000 (Mon, 19 Sep 2011)
Log Message:
-----------
Group manager and eu vat addon modules

Added Paths:
-----------
    addons/1.3/eu_vat/
    addons/1.3/eu_vat/trunk/
    addons/1.3/eu_vat/trunk/sql/
    addons/1.3/eu_vat/trunk/sql/modules/
    addons/1.3/eu_vat/trunk/sql/modules/tax_form_accrual.sql
    addons/1.3/group_mgr/trunk/scripts/
    addons/1.3/group_mgr/trunk/scripts/group_mgr.pl

Added: addons/1.3/eu_vat/trunk/sql/modules/tax_form_accrual.sql
===================================================================
--- addons/1.3/eu_vat/trunk/sql/modules/tax_form_accrual.sql	                        (rev 0)
+++ addons/1.3/eu_vat/trunk/sql/modules/tax_form_accrual.sql	2011-09-19 02:56:41 UTC (rev 3709)
@@ -0,0 +1,175 @@
+-- First a couple of notes about this file and what will probably change in the
+-- future.
+--
+-- The current generation here of reports assumes that we are providing 
+-- reporting of purchases or sales to one country only.  If this ever changes
+-- we will need an abstraction layer like we use with fixed assets.  I suspect
+-- we will go that way anyway since it will make some things easier. 
+--
+-- This file provides accrual-based tax form reports, for example for EU VAT 
+-- reporting.  --CT
+
+
+
+
+CREATE TYPE tax_form_report_item AS (
+    legal_name text, 
+    entity_id integer, 
+    entity_class integer, 
+    control_code text, 
+    meta_number character varying(32), 
+    acc_sum numeric, 
+    invoice_sum numeric, 
+    total_sum numeric);
+
+CREATE TYPE tax_form_report_detail_item AS (
+    legal_name text, 
+    entity_id integer, 
+    entity_class integer, 
+    control_code text, 
+    meta_number character varying(32), 
+    acc_sum numeric, 
+    invoice_sum numeric, 
+    total_sum numeric, 
+    invnumber text, 
+    duedate text,
+    invoice_id int);
+
+CREATE OR REPLACE FUNCTION tax_form_summary_report(in_tax_form_id int, in_begin date, in_end date) 
+RETURNS SETOF tax_form_report_item AS $BODY$
+DECLARE
+	out_row tax_form_report_item;
+BEGIN
+	FOR out_row IN 
+              SELECT company.legal_name, company.entity_id, 
+                     entity_credit_account.entity_class, entity.control_code, 
+                     entity_credit_account.meta_number, 
+                     sum(CASE WHEN gl.amount = 0 THEN 0
+                              WHEN relation = 'acc_trans' 
+                          THEN ac.reportable_amount 
+                          ELSE 0
+                      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 
+                          ELSE 0
+                      END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end),
+                     sum(CASE WHEN gl.amount = 0 THEN 0
+                          ELSE ac.reportable_amount 
+                      END * CASE WHEN gl.class = 'ap' THEN -1 else 1 end
+                      * CASE WHEN ac.relation = 'invoice' then -1 else 1 end)
+                         
+		FROM (select id, transdate, entity_credit_account, invoice, 
+                             amount, 'ar' as class FROM ar 
+                       UNION 
+                      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
+                    LEFT JOIN ac_tax_form atf
+                          ON (acc_trans.entry_id = atf.entry_id)
+                       GROUP BY trans_id
+                       UNION
+                      select trans_id, 'invoice' as relation, 
+                             sum(sellprice * qty) as amount,
+                             sum(case when itf.reportable 
+                                      then sellprice * qty
+                                      else 0
+                                 end) as reportable_amount
+                        FROM invoice
+                    LEFT JOIN invoice_tax_form itf
+                          ON (invoice.id = itf.invoice_id)
+                       GROUP BY trans_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 entity_credit_account 
+                  ON (gl.entity_credit_account = entity_credit_account.id) 
+		JOIN entity ON (entity.id = entity_credit_account.entity_id) 
+		JOIN company ON (entity.id = company.entity_id)
+		JOIN country_tax_form ON (entity_credit_account.taxform_id = country_tax_form.id)
+               WHERE country_tax_form.id = in_tax_form_id
+             GROUP BY legal_name, meta_number, company.entity_id, entity_credit_account.entity_class, entity.control_code 
+    LOOP
+		RETURN NEXT out_row;
+	END LOOP;
+END;
+$BODY$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION tax_form_summary_report
+(in_tax_form_id int, in_begin date, in_end date) IS
+$$This provides the total reportable value per vendor, accrual-basis.$$;
+
+CREATE OR REPLACE FUNCTION tax_form_details_report(in_tax_form_id int, in_begin date, in_end date, in_meta_number text) 
+RETURNS SETOF tax_form_report_detail_item AS $BODY$
+DECLARE
+	out_row tax_form_report_detail_item;
+BEGIN
+	FOR out_row IN 
+              SELECT company.legal_name, company.entity_id, 
+                     entity_credit_account.entity_class, entity.control_code, 
+                     entity_credit_account.meta_number, 
+                     sum(CASE WHEN gl.amount = 0 then 0 
+                              when relation = 'acc_trans'
+                          THEN ac.reportable_amount 
+                          ELSE 0
+                      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 
+                          ELSE 0
+                      END * CASE WHEN gl.class = 'ar' THEN -1 else 1 end),
+                     SUM(CASE WHEN gl.amount = 0 THEN 0 
+                              ELSE ac.reportable_amount 
+                              END
+                         * CASE WHEN gl.class = 'ap' THEN -1 else 1 end 
+                         * CASE WHEN relation = 'invoice' THEN -1 ELSE 1 END),
+                     gl.invnumber, gl.duedate::text, gl.id
+                FROM (select id, entity_credit_account, invnumber, duedate, 
+                             amount, transdate, 'ar' as class
+                        FROM ar 
+                       UNION 
+                      select id, entity_credit_account, invnumber, duedate, 
+                             amount, transdate, '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
+                   LEFT JOIN ac_tax_form atf
+                          ON (acc_trans.entry_id = atf.entry_id)
+                       GROUP BY trans_id
+                       UNION
+                      select trans_id, 'invoice' as relation, 
+                             sum(sellprice * qty) as amount,
+                             sum(case when itf.reportable 
+                                      then sellprice * qty
+                                      else 0
+                                 end) as reportable_amount
+                        FROM invoice
+                   LEFT JOIN invoice_tax_form itf
+                          ON (invoice.id = itf.invoice_id)
+                       GROUP BY trans_id
+                     ) ac ON (ac.trans_id = gl.id)
+		JOIN entity_credit_account ON (gl.entity_credit_account = entity_credit_account.id) 
+		JOIN entity ON (entity.id = entity_credit_account.entity_id) 
+		JOIN company ON (entity.id = company.entity_id)
+		JOIN country_tax_form ON (entity_credit_account.taxform_id = country_tax_form.id)
+		WHERE country_tax_form.id = in_tax_form_id AND meta_number = in_meta_number
+		GROUP BY legal_name, meta_number, company.entity_id, entity_credit_account.entity_class, entity.control_code, gl.invnumber, gl.duedate, gl.id
+	LOOP
+		RETURN NEXT out_row;
+	END LOOP;
+END;
+$BODY$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION tax_form_details_report
+(in_tax_form_id int, in_begin date, in_end date, in_meta_number text) IS
+$$ This provides a list of invoices and transactions that a report hits.  This report is accrual-based.$$;

Added: addons/1.3/group_mgr/trunk/scripts/group_mgr.pl
===================================================================
--- addons/1.3/group_mgr/trunk/scripts/group_mgr.pl	                        (rev 0)
+++ addons/1.3/group_mgr/trunk/scripts/group_mgr.pl	2011-09-19 02:56:41 UTC (rev 3709)
@@ -0,0 +1,100 @@
+package LedgerSMB::Scripts::group_mgr;
+
+sub new_group {
+    
+    my ($request) = @_;
+    my $user = $request->{_user};
+    
+    my $template = LedgerSMB::Template->new( user=>$user, 
+        template=>'Admin/new_group', language=>$user->{language},
+        format=>'HTML', path=>'UI');
+    
+    $template->render();
+}
+
+sub edit_group {
+    
+    my ($request) = @_;
+    my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
+    
+    my $all_roles = $admin->role_list();
+    my $user = $request->{_user};
+    
+    my $template = LedgerSMB::Template->new( 
+        user => $user, 
+        template => 'Admin/edit_group', 
+        language => $user->{language}, 
+        format => 'HTML', 
+        path=>'UI'
+    );
+        
+    if ($request->type() eq "POST") {
+
+        my $role = $admin->save_role();
+        return $template->render(
+            {
+                user=> $request->{role}, 
+                roles=>$all_roles,
+                user_roles=>$admin->get_user_roles($request->{role})
+            }
+        );
+    }
+    else {
+        return $template->render(
+            {
+            roles=>$all_roles
+            }
+        );
+    }    
+}
+
+sub create_group {
+    
+    my ($request) = @_;
+    my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
+    my $user = $request->{_user};
+    
+    my $all_roles = $admin->get_roles();
+    my $template = LedgerSMB::Template->new( 
+        user => $user, 
+        template => 'Admin/edit_group', 
+        language => $user->{language}, 
+        format => 'HTML', 
+        path=>'UI'
+    );
+    if ($request->type() eq "POST") {
+        
+        my $role = $admin->save_role();
+        return $template->render(
+            {
+                user=> $role, roles=>$all_roles
+            }
+        );
+    }
+    else {
+        return $template->render({roles=>$all_roles});
+    }
+}
+
+sub delete_group {
+    
+    my ($request) = @_;
+    my $user = $request->{_user};
+    
+    my $admin = LedgerSMB::DBObject::Admin->new(base=>$request, copy=>'all');
+    
+    # requires the field modifying_user to be set.
+    
+    my $status = $admin->delete_group($request->{modifying_user});
+    
+    # status can either be 1, or an error.
+    # if there's an error, $status->throw() is called by admin.pm. Or possibly
+    # in the template itself.
+    
+    my $template = LedgerSMB::Template->new ( user=>$user, 
+        template=>'Admin/delete_group', language=>$user->{language}, 
+        format=>'HTML', path=>'UI');    
+        
+    $template->render($status);    
+}
+

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