[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3709] addons/1.3
- Subject: SF.net SVN: ledger-smb:[3709] addons/1.3
- From: ..hidden..
- Date: Mon, 19 Sep 2011 02:56:42 +0000
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.