[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4792] trunk
- Subject: SF.net SVN: ledger-smb:[4792] trunk
- From: ..hidden..
- Date: Mon, 28 May 2012 05:19:33 +0000
Revision: 4792
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4792&view=rev
Author: einhverfr
Date: 2012-05-28 05:19:33 +0000 (Mon, 28 May 2012)
Log Message:
-----------
COA report now works on new framework with role detection for which columns to display
Modified Paths:
--------------
trunk/LedgerSMB/Scripts/journal.pm
trunk/LedgerSMB/Template/HTML.pm
trunk/UI/Reports/filters/gl.html
trunk/UI/lib/dynatable.html
trunk/sql/Pg-database.sql
trunk/sql/modules/Account.sql
Added Paths:
-----------
trunk/LedgerSMB/DBObject/Report/COA.pm
Added: trunk/LedgerSMB/DBObject/Report/COA.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/COA.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Report/COA.pm 2012-05-28 05:19:33 UTC (rev 4792)
@@ -0,0 +1,208 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::COA - Chart of Accounts List for LedgerSMB
+
+=head1 SYNPOSIS
+
+ my $report = LedgerSMB::DBObject::Report::COA->new(%$request);
+ $report->run;
+ $report->render($request, $format);
+
+=head1 DESCRIPTION
+
+This module provides a Chart of Account report for LedgerSMB. This account is
+useful regarding checking on current balances and managing the accounts.
+Typically columns are displayed based on the permissions of the user.
+
+=head1 INHERITS
+
+=over
+
+=item LedgerSMB::DBObject::Report;
+
+=back
+
+=cut
+
+package LedgerSMB::DBObject::Report::COA;
+use Moose;
+extends 'LedgerSMB::DBObject::Report';
+
+use LedgerSMB::App_State;
+
+my $locale = $LedgerSMB::App_State::Locale;
+
+=head1 PROPERTIES
+
+=over
+
+=item columns
+
+Read-only accessor, returns a list of columns. Unless otherwise noted, each
+column is intended to be visible to all who have permissions to run the report.
+
+=over
+
+=item accno
+
+Displays the account number.
+
+=item description
+
+Account description.
+
+=item gifi_accno
+
+This is the GIFI account number.
+
+=item debit_balance
+
+This is the debit balance (or blank if none or balance is credit).
+
+=item credit_balance
+
+This is the credit balance (or blank if none or balance is debit)
+
+=item link
+
+This lists the link descriptions. Each represents a group of drop-downs the
+user has access to. This should be visible only to admin users.
+
+=item edit
+
+Link to edit the account. Should be visible only to admin users.
+
+=item delete
+
+Link to delete the account if it has no transactions. Should be visible only to
+admin users.
+
+=back
+
+=cut
+
+our @COLUMNS = (
+ {col_id => 'accno',
+ name => $locale->text('Account Number'),
+ type => 'href',
+ href_base => '',
+ pwidth => '2', },
+
+ {col_id => 'description',
+ name => $locale->text('Description'),
+ type => 'href',
+ href_base => '',
+ pwidth => '6', },
+
+ {col_id => 'gifi_accno',
+ name => $locale->text('GIFI'),
+ type => 'text',
+ pwidth => '1', },
+
+ {col_id => 'debit_balance',
+ name => $locale->text('Debits'),
+ type => 'text',
+ pwidth => '2', },
+
+ {col_id => 'credit_balance',
+ name => $locale->text('Credits'),
+ type => 'text',
+ pwidth => '2', },
+
+ {col_id => 'link',
+ name => $locale->text('Dropdowns'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'edit',
+ name => $locale->text('Edit'),
+ type => 'href',
+ href_base => '',
+ pwidth => '3', },
+
+ {col_id => 'delete',
+ name => $locale->text('Cleared'),
+ type => 'href',
+ href_base => '',
+ pwidth => '3', },
+
+);
+
+sub columns {
+ return ..hidden..;
+}
+
+=item name
+
+Returns the localized template name
+
+=cut
+
+sub name {
+ return $locale->text('Chart of Accounts');
+}
+
+=item header_lines
+
+Returns the inputs to display on header.
+
+=cut
+
+sub header_lines {
+ return [];
+}
+
+=item subtotal_cols
+
+Returns list of columns for subtotals
+
+=cut
+
+sub subtotal_cols {
+ return [];
+}
+
+=head2 Criteria Properties
+
+No criteria required.
+
+=head1 METHODS
+
+=over
+
+=item run_report()
+
+Runs the report, and assigns rows to $self->rows.
+
+=cut
+
+sub run_report{
+ my ($self) = @_;
+ my @rows = $self->exec_method({funcname => 'report__coa'});
+ for my $r(@rows){
+ $r->{edit} = '['.$locale->text('Edit').']';
+ $r->{delete} = '['.$locale->text('Delete').']' if $r->{rowcount};
+ $r->{edit_href_suffix} = 'account.pl?action=edit&id='.$r->{id};
+ $r->{delete_href_suffix} = 'account.pl?action=delete&id='.$r->{id};
+ $r->{accno_href_suffix} =
+ 'reports.pl?action=start_report&module_name=gl&report_name=gl' .
+ "&accno=$r->{accno}--$r->{description}"
+ unless $r->{is_heading};
+ $r->{description_href_suffix} = $r->{accno_href_suffix};
+ $r->{html_class} = 'listheading' if $r->{is_heading};
+ $r->{link} =~ s/:/\n/g;
+ }
+ $self->rows(..hidden..);
+}
+
+
+=head1 COPYRIGHT
+
+COPYRIGHT (C) 2012 The LedgerSMB Core Team. This file may be re-used following
+the terms of the GNU General Public License version 2 or at your option any
+later version. Please see included LICENSE.TXT for details.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
+return 1;
Modified: trunk/LedgerSMB/Scripts/journal.pm
===================================================================
--- trunk/LedgerSMB/Scripts/journal.pm 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/LedgerSMB/Scripts/journal.pm 2012-05-28 05:19:33 UTC (rev 4792)
@@ -21,6 +21,7 @@
use LedgerSMB::Template;
use LedgerSMB::DBObject::Business_Unit;
use LedgerSMB::DBObject::Report::GL;
+use LedgerSMB::DBObject::Report::COA;
use strict;
=pod
@@ -58,6 +59,27 @@
$template->render($request);
}
+=item chart_of_accounts
+
+Returns and displays the chart of accounts
+
+=cut
+
+sub chart_of_accounts {
+ my ($request) = @_;
+ for my $col(qw(accno description gifi_accno debit_balance credit_balance)){
+ $request->{"col_$col"} = '1';
+ }
+ if ($request->is_allowed_role({allowed_roles => ['account_edit']})){
+ for my $col(qw(link edit delete)){
+ $request->{"col_$col"} = '1';
+ }
+ }
+ my $report = LedgerSMB::DBObject::Report::COA->new(%$request);
+ $report->run_report();
+ $report->render($request);
+}
+
=item search
Runs a search and displays results.
Modified: trunk/LedgerSMB/Template/HTML.pm
===================================================================
--- trunk/LedgerSMB/Template/HTML.pm 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/LedgerSMB/Template/HTML.pm 2012-05-28 05:19:33 UTC (rev 4792)
@@ -75,9 +75,9 @@
push @{$vars}, preprocess( $_ );
}
} elsif (!$type) {
- return escapeHTML($rawvars);
+ return escape($rawvars);
} elsif ($type eq 'SCALAR' or $type eq 'Math::BigInt::GMP') {
- return escapeHTML($$rawvars);
+ return escape($$rawvars);
} elsif ($type eq 'CODE'){
return $rawvars;
} elsif ($type eq 'IO::File'){
Modified: trunk/UI/Reports/filters/gl.html
===================================================================
--- trunk/UI/Reports/filters/gl.html 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/UI/Reports/filters/gl.html 2012-05-28 05:19:33 UTC (rev 4792)
@@ -34,7 +34,7 @@
<td colspan="3">
<?lsmb PROCESS ajaxselect element_data = {
name = "accno"
- initial_value = ""
+ initial_value = accno
text_attr = 'accno'
value_attr = 'id'
ajax_target = 'journal.pl'
Modified: trunk/UI/lib/dynatable.html
===================================================================
--- trunk/UI/lib/dynatable.html 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/UI/lib/dynatable.html 2012-05-28 05:19:33 UTC (rev 4792)
@@ -25,7 +25,7 @@
PFX = attributes.input_prefix;
FOREACH ROW IN tbody.rows;
ROWCOUNT = ROWCOUNT + 1 -?>
- <tr class="<?lsmb ROW.html_class ?>">
+ <tr class="<?lsmb ROW.html_class _ ' ' _ ROW.row_id ?>">
<input type="hidden" name="row_<?lsmb ROWCOUNT ?>"
value="<?lsmb ROW.row_id ?>" />
<?lsmb- FOREACH COL IN columns;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/sql/Pg-database.sql 2012-05-28 05:19:33 UTC (rev 4792)
@@ -76,7 +76,8 @@
gifi_accno text,
heading int not null references account_heading(id),
contra bool not null default false,
- tax bool not null default false
+ tax bool not null default false,
+ obsolete bool not null default false
);
COMMENT ON TABLE account IS
Modified: trunk/sql/modules/Account.sql
===================================================================
--- trunk/sql/modules/Account.sql 2012-05-27 15:00:13 UTC (rev 4791)
+++ trunk/sql/modules/Account.sql 2012-05-28 05:19:33 UTC (rev 4792)
@@ -1,5 +1,4 @@
--- VERSION 1.3.0
-
+BEGIN;
CREATE OR REPLACE FUNCTION account__get_from_accno(in_accno text)
returns account as
$$
@@ -74,7 +73,7 @@
CREATE OR REPLACE FUNCTION account_save
(in_id int, in_accno text, in_description text, in_category char(1),
in_gifi_accno text, in_heading int, in_contra bool, in_tax bool,
-in_link text[])
+in_link text[], is_obsolete bool)
RETURNS int AS $$
DECLARE
t_heading_id int;
@@ -116,12 +115,15 @@
gifi_accno = in_gifi_accno,
heading = t_heading_id,
contra = in_contra,
+ obsolete = is_obsolete,
tax = t_tax
WHERE id = in_id;
IF FOUND THEN
t_id := in_id;
ELSE
+ -- can't obsolete on insert, but this can be changed if users
+ -- request it --CT
INSERT INTO account (accno, description, category, gifi_accno,
heading, contra, tax)
VALUES (in_accno, in_description, in_category, in_gifi_accno,
@@ -159,6 +161,20 @@
based on the in_link array.
$$;
+CREATE OR REPLACE FUNCTION account__delete(in_id int)
+RETURNS BOOL AS
+$$
+BEGIN
+DELETE FROM account WHERE id = in_id;
+RETURN FOUND;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION account__delete(int) IS
+$$ This deletes an account with the id specified. If the account has
+transactions associated with it, it will fail and raise a foreign key constraint.
+$$;
+
CREATE OR REPLACE FUNCTION account_heading_list()
RETURNS SETOF account_heading AS
$$
@@ -289,6 +305,57 @@
$$ language plpgsql;
COMMENT ON FUNCTION account__save_tax
-(in_chart_id int, in_validto date, in_rate numeric, in_minval numeric,
-in_taxnumber text, in_pass int, in_taxmodule_id int, in_old_validto date) IS
+(in_chart_id int, in_validto date, in_rate numeric, in_minvalue numeric,
+in_maxvalue numeric, in_taxnumber text,
+in_pass int, in_taxmodule_id int, in_old_validto date) IS
$$ This saves tax rates.$$;
+
+DROP TYPE IF EXISTS coa_entry CASCADE;
+
+CREATE TYPE coa_entry AS (
+ id int,
+ is_heading bool,
+ accno text,
+ description text,
+ gifi text,
+ debit_balance numeric,
+ credit_balance numeric,
+ rowcount bigint,
+ link text
+);
+
+CREATE OR REPLACE FUNCTION report__coa() RETURNS SETOF coa_entry AS
+$$
+
+WITH ac (chart_id, amount) AS (
+ SELECT chart_id, amount
+ FROM acc_trans
+ JOIN (select id, approved from ar union all
+ select id, approved from ap union all
+ select id, approved from gl) gl ON gl.id = acc_trans.trans_id
+ WHERE acc_trans.approved and gl.approved
+),
+l(account_id, link) AS (
+ SELECT account_id, array_to_string(array_agg(description), ':')
+ FROM account_link
+ GROUP BY account_id
+)
+SELECT a.id, a.is_heading, a.accno, a.description, a.gifi_accno,
+ CASE WHEN sum(ac.amount) < 0 THEN sum(amount) * -1 ELSE null::numeric
+ END,
+ CASE WHEN sum(ac.amount) > 0 THEN sum(amount) ELSE null::numeric END,
+ count(ac.*), l.link
+ FROM (SELECT id,false as is_heading, accno, description, gifi_accno
+ FROM account
+ UNION
+ SELECT id, true, accno, description, null::text
+ FROM account_heading) a
+
+ LEFT JOIN ac ON ac.chart_id = a.id AND not a.is_heading
+ LEFT JOIN l ON l.account_id = a.id AND NOT a.is_heading
+ GROUP BY a.id, a.is_heading, a.accno, a.description, a.gifi_accno, l.link
+ ORDER BY a.accno;
+
+$$ LANGUAGE SQL;
+
+COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.