[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4589] trunk
- Subject: SF.net SVN: ledger-smb:[4589] trunk
- From: ..hidden..
- Date: Mon, 26 Mar 2012 09:03:00 +0000
Revision: 4589
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4589&view=rev
Author: einhverfr
Date: 2012-03-26 09:03:00 +0000 (Mon, 26 Mar 2012)
Log Message:
-----------
Moving GL reports to new framework
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Report.pm
trunk/LedgerSMB/GL.pm
trunk/LedgerSMB/Scripts/journal.pm
trunk/UI/journal/search.html
trunk/bin/gl.pl
trunk/lsmb-request.pl
trunk/sql/modules/Report.sql
Added Paths:
-----------
trunk/LedgerSMB/DBObject/Report/
trunk/LedgerSMB/DBObject/Report/GL.pm
Added: trunk/LedgerSMB/DBObject/Report/GL.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/GL.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/Report/GL.pm 2012-03-26 09:03:00 UTC (rev 4589)
@@ -0,0 +1,326 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::GL - GL Reports for LedgerSMB
+
+=head1 SYNPOSIS
+
+ my $glreport = LedgerSMB::DBObject::Report::GL->new(%$request);
+ $glreport->run;
+ $glreport->render($request, $format);
+
+=head1 DESCRIPTION
+
+This module provides GL reports for LedgerSMB. GL reports are useful for
+searching for and reporting financial transactions.
+
+=head1 INHERITS
+
+=over
+
+=item LedgerSMB::DBObject::Report;
+
+=back
+
+=cut
+
+package LedgerSMB::DBObject::Report::GL;
+use Moose;
+extends 'LedgerSMB::DBObject::Report';
+
+use LedgerSMB::DBObject::Business_Unit_Class;
+use LedgerSMB::DBObject::Business_Unit;
+use LedgerSMB::App_State;
+
+my $locale = $LedgerSMB::App_State::Locale;
+
+=head1 PROPERTIES
+
+=over
+
+=item columns
+
+Read-only accessor, returns a list of columns.
+
+=over
+
+=item id
+
+=item reference
+
+=item description
+
+=item transdate
+
+=item source
+
+=item memo
+
+=item debits
+
+=item credits
+
+=item entry_id
+
+=item cleared
+
+=item chart_id
+
+=item accno
+
+=item gifi_accno
+
+=item running_balance
+
+=item one for each business unit class returned
+
+=back
+
+=cut
+
+our @COLUMNS = (
+ {col_id => 'id',
+ name => $locale->text('ID'),
+ type => 'text',
+ pwidth => 1, },
+
+ {col_id => 'reference',
+ name => $locale->text('Reference'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'description',
+ name => $locale->text('Description'),
+ type => 'text',
+ pwidth => '12', },
+
+ {col_id => 'entry_id',
+ name => $locale->text('Entry ID'),
+ type => 'text',
+ pwidth => '1', },
+
+ {col_id => 'debits',
+ name => $locale->text('Debits'),
+ type => 'text',
+ pwidth => '2', },
+
+ {col_id => 'credits',
+ name => $locale->text('Credits'),
+ type => 'text',
+ pwidth => '2', },
+
+ {col_id => 'source',
+ name => $locale->text('Source'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'memo',
+ name => $locale->text('Memo'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'cleared',
+ name => $locale->text('Cleared'),
+ type => 'text',
+ pwidth => '1', },
+
+ {col_id => 'transdate',
+ name => $locale->text('Date'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'till',
+ name => $locale->text('Till'),
+ type => 'text',
+ pwidth => '1', },
+
+ {col_id => 'chart_id',
+ name => $locale->text('Chart ID'),
+ type => 'text',
+ pwidth => '1', },
+
+ {col_id => 'accno',
+ name => $locale->text('Account No.'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'gifi_accno',
+ name => $locale->text('GIFI'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'accname',
+ name => $locale->text('Account Name'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'running_balance',
+ name => $locale->text('Balance'),
+ type => 'text',
+ pwidth => '3', },
+);
+
+sub columns {
+ return @COLUMNS;
+}
+
+ # TODO: business_units int[]
+
+=item filter_template
+
+Returns the template name for the filter.
+
+=cut
+
+sub filter_template {
+ return 'journal/search';
+}
+
+=head2 Criteria Properties
+
+Note that in all cases, undef matches everything.
+
+=item reference (text)
+
+Exact match on reference or invoice number.
+
+=cut
+
+has 'reference' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item accno
+
+Exact match for the account number
+
+=cut
+
+has 'accno' => (is => 'rw', isa => 'Maybe[Str]');
+
+
+=item category
+
+Is one of A (Asset), L (Liability), Q (Equity), I (Income), or E (Expense).
+
+When set only matches lines attached to transactions of specfied type.
+
+=cut
+
+has 'category' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item source
+
+Exact match of source field
+
+=cut
+
+has 'source' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item memo
+
+Full text search of memo field
+
+=cut
+
+has 'memo' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item description
+
+Full text search of description field of GL transaction
+
+=cut
+
+has 'description' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item date_from
+
+Earliest date which matches the search
+
+=cut
+
+has 'date_from' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item date_to
+
+Last date that matches the search
+
+=cut
+
+has 'date_to' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item approved
+
+Unless false, only matches approved transactions. When false, matches all
+transactions. This is the one exception to the general rule that undef matches
+all.
+
+=cut
+
+has 'approved' => (is => 'rw', isa => 'Maybe[Bool]');
+
+=item amount_from
+
+The lowest value that can match, amount-wise.
+
+=item amount_to
+
+The highest value that can match, amount-wise.
+
+=cut
+
+has 'amount_from' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGNumber]');
+has 'amount_to' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGNumber]');
+
+=item business_units
+
+Array of business unit id's
+
+=cut
+
+has 'business_units' => (is => 'rw', isa => 'Maybe[ArrayRef[Int]]');
+
+=head1 METHODS
+
+=over
+
+=item prepare_criteria($request)
+
+Instantiates the PGDate and PGNumber inputs.
+
+=cut
+
+sub prepare_criteria{
+ my ($self, $request) = @_;
+ $request->{date_from} = LedgerSMB::PGDate->from_input(
+ $request->{date_from}
+ );
+ $request->{date_to} = LedgerSMB::PGDate->from_input($request->{date_to});
+ $request->{amount_from} = LedgerSMB::PGNumber->from_input(
+ $request->{amount_from}
+ );
+ $request->{amount_to} = LedgerSMB::PGNumber->from_input(
+ $request->{amount_to}
+ );
+}
+
+=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__gl'});
+ # TODO: Convert to links and add href stuff.
+ $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
+
+return 1;
Modified: trunk/LedgerSMB/DBObject/Report.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report.pm 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/LedgerSMB/DBObject/Report.pm 2012-03-26 09:03:00 UTC (rev 4589)
@@ -121,7 +121,7 @@
=cut
sub render {
- my ($self) = @_;
+ my ($self, $request) = @_;
my $template;
if ($template = eval {$self->{template}}){
# what needs to be done here? Maybe log it?
@@ -131,13 +131,14 @@
if (!defined $self->format){
$self->format('html');
}
- my $template = LedgerSMB::Template->new(
- user => $LedgerSMB::App_State::User;
+ $template = LedgerSMB::Template->new(
+ user => $LedgerSMB::App_State::User,
locale => $LedgerSMB::App_State::Locale,
path => 'UI',
template => $template,
format => uc($self->format),
);
+ $template->render({report => $self, request => $request});
}
=back
Modified: trunk/LedgerSMB/GL.pm
===================================================================
--- trunk/LedgerSMB/GL.pm 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/LedgerSMB/GL.pm 2012-03-26 09:03:00 UTC (rev 4589)
@@ -275,344 +275,6 @@
$rc;
}
-sub all_transactions {
-
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
- my $query;
- my $sth;
- my $var;
- my $null;
- if ($form->{chart_id}){
- my $sth = $dbh->prepare("SELECT id, accno, description FROM chart WHERE id = ? AND charttype = 'A'");
- $sth->execute($form->{chart_id});
- ($form->{chart_id}, $form->{chart_accno}, $form->{chart_description}) = $sth->fetchrow_array();
- }
- if ($form->{accno} and !$form->{chart_id}){
- my $sth = $dbh->prepare('SELECT id, accno, description FROM chart WHERE accno = ?');
- $sth->execute($form->{accno});
- ($form->{chart_id}, $form->{chart_accno}, $form->{chart_description}) = $sth->fetchrow_array();
- delete $form->{accno};
- }
-
- my ( $glwhere, $arwhere, $apwhere ) = ( "1 = 1", "1 = 1", "1 = 1" );
-
- if ( $form->{reference} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{reference} ) );
- $glwhere .= " AND lower(g.reference) LIKE $var";
- $arwhere .= " AND lower(a.invnumber) LIKE $var";
- $apwhere .= " AND lower(a.invnumber) LIKE $var";
- }
-
- if ( $form->{department} ne "" ) {
- ( $null, $var ) = split /--/, $form->{department};
- $var = $dbh->quote($var);
- $glwhere .= " AND g.department_id = $var";
- $arwhere .= " AND a.department_id = $var";
- $apwhere .= " AND a.department_id = $var";
- }
-
- if ( $form->{project_id} ne "") {
- $var = $dbh->quote($form->{project_id});
- $glwhere .= " AND ac.project_id = $var";
- $arwhere .= " AND ac.project_id = $var";
- $apwhere .= " AND ac.project_id = $var";
- }
- if ( $form->{source} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{source} ) );
- $glwhere .= " AND lower(ac.source) LIKE $var";
- $arwhere .= " AND lower(ac.source) LIKE $var";
- $apwhere .= " AND lower(ac.source) LIKE $var";
- }
-
- if ( $form->{memo} ne "" ) {
- $var = $dbh->quote( $form->like( lc $form->{memo} ) );
- $glwhere .= " AND lower(ac.memo) LIKE $var";
- $arwhere .= " AND lower(ac.memo) LIKE $var";
- $apwhere .= " AND lower(ac.memo) LIKE $var";
- }
-
- if (!$form->{datefrom} && !$form->{dateto}
- && $form->{year} && $form->{month}){
- ( $form->{datefrom}, $form->{dateto} ) =
- $form->from_to( $form->{year}, $form->{month}, $form->{interval} );
- }
-
- if ( $form->{datefrom} ) {
- $glwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
- $arwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
- $apwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );
- }
-
- if ( $form->{dateto} ) {
- $glwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
- $arwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
- $apwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );
- }
-
- if ( $form->{amountfrom} ) {
- $glwhere .=
- " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
- $arwhere .=
- " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
- $apwhere .=
- " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );
- }
-
- if ( $form->{amountto} ) {
- $glwhere .=
- " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
- $arwhere .=
- " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
- $apwhere .=
- " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );
- }
-
- if ( $form->{description} ) {
-
- $var = $dbh->quote( $form->like( lc $form->{description} ) );
- $glwhere .= " AND lower(g.description) LIKE $var";
- $arwhere .= " AND (lower(e.name) LIKE $var
- OR lower(ac.memo) LIKE $var
- OR a.id IN (SELECT DISTINCT trans_id
- FROM invoice
- WHERE lower(description) LIKE $var))";
-
- $apwhere .= " AND (lower(e.name) LIKE $var
- OR lower(ac.memo) LIKE $var
- OR a.id IN (SELECT DISTINCT trans_id
- FROM invoice
- WHERE lower(description) LIKE $var))";
- }
-
- if ( $form->{notes} ) {
- $var = $dbh->quote( $form->like( lc $form->{notes} ) );
- $glwhere .= " AND lower(g.notes) LIKE $var";
- $arwhere .= " AND lower(a.notes) LIKE $var";
- $apwhere .= " AND lower(a.notes) LIKE $var";
- }
-
- if ( $form->{accno} ) {
- $var = $dbh->quote( $form->{accno} );
- $glwhere .= " AND c.accno = $var";
- $arwhere .= " AND c.accno = $var";
- $apwhere .= " AND c.accno = $var";
- }
-
- if ( $form->{gifi_accno} ) {
- $var = $dbh->quote( $form->{gifi_accno} );
- $glwhere .= " AND c.gifi_accno = $var";
- $arwhere .= " AND c.gifi_accno = $var";
- $apwhere .= " AND c.gifi_accno = $var";
- }
-
- if ( $form->{category} ne 'X' ) {
- $var = $dbh->quote( $form->{category} );
- $glwhere .= " AND c.category = $var";
- $arwhere .= " AND c.category = $var";
- $apwhere .= " AND c.category = $var";
- }
-
- if ( $form->{chart_accno} ) {
- my $accno = $dbh->quote( $form->{chart_accno} );
-
- # get category for account
- $query = qq|SELECT category, link, contra, description
- FROM chart
- WHERE accno = $accno|;
-
- (
- $form->{category}, $form->{link}, $form->{contra},
- $form->{account_description}
- ) = $dbh->selectrow_array($query);
-
- if ( $form->{datefrom} ) {
- $query = qq|
- SELECT account__obtain_balance(?, id) from chart
- WHERE accno = ? |;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{datefrom}, $form->{chart_accno});
-
- ( $form->{balance} ) = $sth->fetchrow_array;
- $sth->finish;
- }
- }
-
- if ( $form->{gifi_accno} ) {
- my $gifi = $dbh->quote( $form->{gifi_accno} );
-
- # get category for account
- $query = qq|SELECT c.category, c.link, c.contra, g.description
- FROM chart c
- LEFT JOIN gifi g ON (g.accno = c.gifi_accno)
- WHERE c.gifi_accno = $gifi|;
-
- (
- $form->{category}, $form->{link}, $form->{contra},
- $form->{gifi_account_description}
- ) = $dbh->selectrow_array($query);
-
- if ( $form->{datefrom} ) {
-
- $query = qq|
- SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id AND c.charttype = 'A')
- WHERE c.gifi_accno = $gifi
- AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );
-
- ( $form->{balance} ) = $dbh->selectrow_array($query);
- }
- }
-
- my $false = 'FALSE';
-
- my %ordinal = (
- id => 1,
- reference => 4,
- description => 5,
- transdate => 6,
- source => 7,
- accno => 9,
- department => 15,
- memo => 16,
- trans_id => 18,
- chart_id => 19,
- entry_id => 20
- );
-
- my @a = qw( entry_id trans_id chart_id id transdate reference source description accno);
- my $sortorder = $form->sort_order( ..hidden.., \%ordinal );
-
- my $chart_id;
- if ($form->{chart_id}){
- $chart_id = $dbh->quote($form->{chart_id});
- } else {
- $chart_id = 'NULL';
- }
-
- if (!defined $form->{approved}){
- $approved = 'true';
- } elsif ($form->{approved} eq 'all') {
- $approved = 'NULL';
- } else {
- $approved = $dbh->quote($form->{approved});
- }
-
- $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
- g.description, ac.transdate, ac.source,
- ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
- '' AS till, ac.cleared, d.description AS department,
- ac.memo, c.description AS accname, ac.trans_id,
- ac.chart_id, ac.entry_id
- FROM gl AS g
- JOIN acc_trans ac ON (g.id = ac.trans_id)
- JOIN chart c ON (ac.chart_id = c.id AND c.charttype = 'A')
- LEFT JOIN department d ON (d.id = g.department_id)
- WHERE $glwhere
- AND (ac.chart_id = $chart_id OR
- $chart_id IS NULL)
- AND ($approved IS NULL OR
- $approved =
- (ac.approved AND g.approved))
-
- UNION ALL
-
- SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
- e.name, ac.transdate, ac.source,
- ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
- a.till, ac.cleared, d.description AS department,
- ac.memo, c.description AS accname, ac.trans_id,
- ac.chart_id, ac.entry_id
- FROM ar a
- JOIN acc_trans ac ON (a.id = ac.trans_id)
- JOIN chart c ON (ac.chart_id = c.id AND c.charttype = 'A')
- JOIN entity_credit_account ec ON
- (a.entity_credit_account = ec.id)
- JOIN entity e ON (ec.entity_id = e.id)
- LEFT JOIN department d ON (d.id = a.department_id)
- WHERE $arwhere
- AND (ac.chart_id = $chart_id OR
- $chart_id IS NULL)
- AND ($approved IS NULL OR
- $approved =
- (ac.approved AND a.approved))
-
- UNION ALL
-
- SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
- e.name, ac.transdate, ac.source,
- ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
- a.till, ac.cleared, d.description AS department,
- ac.memo, c.description AS accname, ac.trans_id,
- ac.chart_id, ac.entry_id
- FROM ap a
- JOIN acc_trans ac ON (a.id = ac.trans_id)
- JOIN chart c ON (ac.chart_id = c.id AND c.charttype = 'A')
- JOIN entity_credit_account ec ON
- (a.entity_credit_account = ec.id)
- JOIN entity e ON (ec.entity_id = e.id)
- LEFT JOIN department d ON (d.id = a.department_id)
- WHERE $apwhere
- AND (ac.chart_id = $chart_id OR
- $chart_id IS NULL)
- AND ($approved IS NULL OR
- $approved =
- (ac.approved AND a.approved))
- ORDER BY $sortorder|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $minusOne=new Math::BigFloat(-1);#HV make sure BigFloat stays BigFloat
- my $zeroBF=new Math::BigFloat(0);#HV make sure BigFloat stays BigFloat
-
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->db_parse_numeric(sth=>$sth,hashref=>$ref);
-
- # gl
- if ( $ref->{type} eq "gl" ) {
- $ref->{module} = "gl";
- }
-
- # ap
- if ( $ref->{type} eq "ap" ) {
-
- if ( $ref->{invoice} ) {
- $ref->{module} = "ir";
- }
- else {
- $ref->{module} = "ap";
- }
- }
-
- # ar
- if ( $ref->{type} eq "ar" ) {
-
- if ( $ref->{invoice} ) {
- $ref->{module} = ( $ref->{till} ) ? "ps" : "is";
- }
- else {
- $ref->{module} = "ar";
- }
- }
-
- if ( $ref->{amount} < 0 ) {
- $ref->{debit} = $ref->{amount} * $minusOne;
- $ref->{credit} = $zeroBF;
- }
- else {
- $ref->{credit} = $ref->{amount};
- $ref->{debit} = $zeroBF;
- }
-
- push @{ $form->{GL} }, $ref;
- }
-
- $sth->finish;
-}
-
sub transaction {
my ( $self, $myconfig, $form ) = @_;
Modified: trunk/LedgerSMB/Scripts/journal.pm
===================================================================
--- trunk/LedgerSMB/Scripts/journal.pm 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/LedgerSMB/Scripts/journal.pm 2012-03-26 09:03:00 UTC (rev 4589)
@@ -19,6 +19,7 @@
use LedgerSMB;
use LedgerSMB::Template;
+use LedgerSMB::DBObject::Report::GL;
use strict;
=pod
@@ -30,8 +31,6 @@
Get the search string, query the database, return the results in a ul/li
pair easily queried by scriptaculous's autocompleter.
-=back
-
=cut
sub __default {
@@ -58,6 +57,39 @@
$template->render($request);
}
+=item start_search
+
+Displays the search screen
+
+=cut
+
+sub start_search {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/journal',
+ template => 'search',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+=item search
+
+Runs a search and displays results.
+
+=cut
+
+sub search {
+ my ($request) = @_;
+ my $report = LedgerSMB::DBObject::Report::GL->new(%$request);
+ $report->run_report;
+ $report->render;
+}
+
+=back
+
=head1 Copyright (C) 2007 The LedgerSMB Core Team
Licensed under the GNU General Public License version 2 or later (at your
Modified: trunk/UI/journal/search.html
===================================================================
--- trunk/UI/journal/search.html 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/UI/journal/search.html 2012-03-26 09:03:00 UTC (rev 4589)
@@ -329,7 +329,7 @@
class="submit"
type="submit"
name="action"
- value="generate_report"
+ value="search"
} ?>
</form>
Modified: trunk/bin/gl.pl
===================================================================
--- trunk/bin/gl.pl 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/bin/gl.pl 2012-03-26 09:03:00 UTC (rev 4589)
@@ -497,434 +497,6 @@
}
-sub generate_report {
- my $output_options = shift;
- if ($form->{account}){
- ($form->{accno}) = split /--/, $form->{account};
- }
- $form->{sort} = "transdate" unless $form->{sort};
- $form->{amountfrom} = $form->parse_amount(\%myconfig, $form->{amountfrom});
- $form->{amountto} = $form->parse_amount(\%myconfig, $form->{amountto});
- my ($totaldebit, $totalcredit)=(new Math::BigFloat(0),new Math::BigFloat(0));
-
- GL->all_transactions( \%myconfig, \%$form );
-
- $href =
-"$form->{script}?action=generate_report&direction=$form->{direction}&oldsort=$form->{oldsort}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}";
-
- $form->sort_order();
-
- $callback =
-"$form->{script}?action=generate_report&direction=$form->{direction}&oldsort=$form->{oldsort}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}";
-
- my %hiddens = (
- 'action' => 'generate_report',
- 'direction' => $form->{direction},
- 'oldsort' => $form->{oldsort},
- 'path' => $form->{path},
- 'login' => $form->{login},
- 'sessionid' => $form->{sessionid},
- );
- %acctype = (
- 'A' => $locale->text('Asset'),
- 'L' => $locale->text('Liability'),
- 'Q' => $locale->text('Equity'),
- 'I' => $locale->text('Income'),
- 'E' => $locale->text('Expense'),
- );
- my @options;
- if ($form->{chart_accno}){
- $form->{title} = $locale->text('General Ledger: [_1] [_2]', $form->{chart_accno}, $form->{chart_description});
- } else {
- $form->{title} = $locale->text('General Ledger');
- }
- $ml=new Math::BigFloat(($form->{category} =~ /(A|E)/)?-1:1);
-
- if (defined $form->{category} and $form->{category} ne 'X' ) {
- $form->{title} .=
- " : " . $locale->text( $acctype{ $form->{category} } );
- }
- if ( $form->{accno} ) {
- $href .= "&accno=" . $form->escape( $form->{accno} );
- $callback .= "&accno=" . $form->escape( $form->{accno}, 1 );
- $hiddens{accno} = $form->{accno};
- push @options, $locale->text('Account')
- . " : $form->{accno} $form->{account_description}";
- }
- if ( $form->{gifi_accno} ) {
- $href .= "&gifi_accno=" . $form->escape( $form->{gifi_accno} );
- $callback .= "&gifi_accno=" . $form->escape( $form->{gifi_accno}, 1 );
- $hiddens{gifi_accno} = $form->{gifi_accno};
- push @options, $locale->text('GIFI')
- . " : $form->{gifi_accno} $form->{gifi_account_description}";
- }
- if ( $form->{source} ) {
- $href .= "&source=" . $form->escape( $form->{source} );
- $callback .= "&source=" . $form->escape( $form->{source}, 1 );
- $hiddens{source} = $form->{source};
- push @options, $locale->text('Source') . " : $form->{source}";
- }
- if ( $form->{memo} ) {
- $href .= "&memo=" . $form->escape( $form->{memo} );
- $callback .= "&memo=" . $form->escape( $form->{memo}, 1 );
- $hiddens{memo} = $form->{memo};
- push @options, $locale->text('Memo') . " : $form->{memo}";
- }
- if ( $form->{reference} ) {
- $href .= "&reference=" . $form->escape( $form->{reference} );
- $callback .= "&reference=" . $form->escape( $form->{reference}, 1 );
- $hiddens{reference} = $form->{reference};
- push @options, $locale->text('Reference') . " : $form->{reference}";
- }
- if ( $form->{department} ) {
- $href .= "&department=" . $form->escape( $form->{department} );
- $callback .= "&department=" . $form->escape( $form->{department}, 1 );
- $hiddens{department} = $form->{department};
- ($department) = split /--/, $form->{department};
- push @options, $locale->text('Department') . " : $department";
- }
-
- if ( $form->{description} ) {
- $href .= "&description=" . $form->escape( $form->{description} );
- $callback .= "&description=" . $form->escape( $form->{description}, 1 );
- $hiddens{description} = $form->{description};
- push @options, $locale->text('Description') . " : $form->{description}";
- }
- if ( $form->{notes} ) {
- $href .= "¬es=" . $form->escape( $form->{notes} );
- $callback .= "¬es=" . $form->escape( $form->{notes}, 1 );
- $hiddens{notes} = $form->{notes};
- push @options, $locale->text('Notes') . " : $form->{notes}";
- }
-
- if ( $form->{datefrom} ) {
- $href .= "&datefrom=$form->{datefrom}";
- $callback .= "&datefrom=$form->{datefrom}";
- $hiddens{datefrom} = $form->{datefrom};
- push @options, $locale->text('From') . " "
- . $locale->date( \%myconfig, $form->{datefrom}, 1 );
- }
- if ( $form->{dateto} ) {
- $href .= "&dateto=$form->{dateto}";
- $callback .= "&dateto=$form->{dateto}";
- $hiddens{dateto} = $form->{dateto};
- my $option = $locale->text('To') . " "
- . $locale->date( \%myconfig, $form->{dateto}, 1 );
- if ( $form->{datefrom} ) {
- $options[$#options] .= " $option";
- }
- else {
- push @options, $option;
- }
- }
-
- if ( $form->{amountfrom} ) {
- $href .= "&amountfrom=$form->{amountfrom}";
- $callback .= "&amountfrom=$form->{amountfrom}";
- $hiddens{amountfrom} = $form->{amountfrom};
- push @options, $locale->text('Amount') . " >= "
- . $form->format_amount( \%myconfig, $form->{amountfrom}, 2 );
- }
- if ( $form->{amountto} ) {
- $href .= "&amountto=$form->{amountto}";
- $callback .= "&amountto=$form->{amountto}";
- $hiddens{amountto} = $form->{amountto};
- my $option .= $form->format_amount( \%myconfig, $form->{amountto}, 2 );
- if ( $form->{amountfrom} ) {
- $options[$#options] .= " <= $option";
- }
- else {
- push @options, $locale->text('Amount') . " <= $option";
- }
- }
- @columns =
- $form->sort_columns(
- qw(transdate id reference description notes source memo debit credit accno gifi_accno department)
- );
- if ($form->{bank_register_mode}){
- @columns = $form->sort_columns(
- qw(transdate id reference description notes source memo credit debit accno
- gifi_accno department)
- );
- }
- pop @columns if $form->{department};
-
- if ( $form->{link} =~ /_paid/ ) {
- @columns =
- $form->sort_columns(
- qw(transdate id reference description notes source memo cleared debit credit accno gifi_accno)
- );
- if ($form->{bank_register_mode}){
- @columns = $form->sort_columns(
- qw(transdate id reference description notes source memo cleared credit
- debit accno gifi_accno)
- );
- }
- $form->{l_cleared} = "Y";
- }
-
- if ( $form->{chart_id} || $form->{gifi_accno} ) {
- @columns = grep !/(accno|gifi_accno)/, @columns;
- push @columns, "balance";
- $form->{l_balance} = "Y";
- }
-
- foreach $item (@columns) {
- if ( $form->{"l_$item"} eq "Y" ) {
- push @column_index, $item;
-
- # add column to href and callback
- $callback .= "&l_$item=Y";
- $href .= "&l_$item=Y";
- $hiddens{"l_$item"} = 'Y';
- }
- }
-
- if ( $form->{l_subtotal} eq 'Y' ) {
- $callback .= "&l_subtotal=Y";
- $href .= "&l_subtotal=Y";
- $hiddens{l_subtotal} = 'Y';
- }
-
- $callback .= "&category=$form->{category}";
- $href .= "&category=$form->{category}";
- $hiddens{category} = $form->{category};
-
- my $column_names = {
- id => 'ID',
- transdate => 'Date',
- reference => 'Reference',
- source => 'Source',
- memo => 'Memo',
- description => 'Description',
- department => 'Department',
- notes => 'Notes',
- debit => 'Debit',
- credit => 'Credit',
- accno => 'Account',
- gifi_accno => 'GIFI',
- balance => 'Balance',
- cleared => 'R'
- };
- if ($form->{bank_register_mode}){
- $column_names->{credit} = 'Debit';
- $column_names->{debit} = 'Credit';
- }
- my $sort_href = "$href&sort";
- my @sort_columns = qw(id transdate reference source memo description department accno gifi_accno);
-
- # add sort to callback
- $form->{callback} = "$callback&sort=$form->{sort}";
- $callback = $form->escape( $form->{callback} );
- $hiddens{sort} = $form->{sort};
- $hiddens{callback} = $form->{callback};
-
- $cml=new Math::BigFloat(1);
-
- # initial item for subtotals
- if ( @{ $form->{GL} } ) {
- $sameitem = $form->{GL}->[0]->{ $form->{sort} };
- $cml=new Math::BigFloat(-1) if $form->{contra};
- }
-
- my @rows;
- if ( ( $form->{accno} || $form->{gifi_accno} ) && $form->{balance} ) {
- my %column_data;
-
- for (@column_index) { $column_data{$_} = " " }
- $column_data{balance} =
- $form->format_amount( \%myconfig, $form->{balance} * $ml * $cml,
- 2, 0 );
-
- $column_data{i} = 1;
- push @rows, \%column_data;
- }
-
- # reverse href
- # XXX: should we use the reversed href as the sort_href url above ?
- $direction = ( $form->{direction} eq 'ASC' ) ? "ASC" : "DESC";
- $form->sort_order();
- $href =~ s/direction=$form->{direction}/direction=$direction/;
-
- my $i = 0;
- foreach $ref ( @{ $form->{GL} } ) {
- my %column_data;
-
- # if item ne sort print subtotal
- if ( $form->{l_subtotal} eq 'Y' ) {
- if ( $sameitem ne $ref->{ $form->{sort} } ) {
- push @rows, &gl_subtotal_tt();
- }
- }
-
- $form->{balance} += $ref->{amount};
-
- $subtotaldebit += $ref->{debit};
- $subtotalcredit += $ref->{credit};
-
- $totaldebit += $ref->{debit};
- $totalcredit += $ref->{credit};
-
- $ref->{debit} =
- $form->format_amount( \%myconfig, $ref->{debit}, 2);
- $ref->{credit} =
- $form->format_amount( \%myconfig, $ref->{credit}, 2);
-
- for (qw(id transdate)) { $column_data{$_} = "$ref->{$_}" }
-
- $column_data{reference} =
- {href => "$ref->{module}.pl?action=edit&id=$ref->{id}&path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$callback",
- text => $ref->{reference}};
-
- #$ref->{notes} =~ s/\r?\n/<br>/g;
- for (qw(description source memo notes department)) {
- $column_data{$_} = "$ref->{$_} ";
- }
-
- $column_data{debit} = "$ref->{debit}";
- $column_data{credit} = "$ref->{credit}";
-
- $column_data{accno} =
- {href => "$href&accno=$ref->{accno}&callback=$callback",
- text => "$ref->{accno} $ref->{accname}"};
- $column_data{gifi_accno} =
- {href => "$href&gifi_accno=$ref->{gifi_accno}&callback=$callback",
- text => $ref->{gifi_accno}};
- $column_data{balance} = $form->format_amount( \%myconfig, $form->{balance} * $ml * $cml,
- 2, 0 );
- $column_data{cleared} =
- ( $ref->{cleared} ) ? "*" : " ";
-
- if ( $ref->{id} != $sameid ) {
- $i++;
- $i %= 2;
- }
- $column_data{'i'} = $i;
- push @rows, \%column_data;
-
- $sameid = $ref->{id};
- }
-
- push @rows, &gl_subtotal_tt() if ( $form->{l_subtotal} eq 'Y' );
-
- for (@column_index) { $column_data{$_} = " " }
- $column_data{debit} = $form->format_amount( \%myconfig, $totaldebit, 2, " " );
- $column_data{credit} = $form->format_amount( \%myconfig, $totalcredit, 2, " " );
- $column_data{balance} = $form->format_amount( \%myconfig, $form->{balance} * $ml * $cml, 2, 0 );
-
- $i = 1;
- my %button;
- if ( $myconfig{acs} !~ /General Ledger--General Ledger/ ) {
- $button{'General Ledger--Add Transaction'} = {
- name => 'action',
- value => 'gl_transaction',
- text => $locale->text('GL Transaction'),
- type => 'submit',
- class => 'submit',
- order => $i++};
- }
- if ( $myconfig{acs} !~ /AR--AR/ ) {
- $button{'AR--Add Transaction'} = {
- name => 'action',
- value => 'ar_transaction',
- text => $locale->text('AR Transaction'),
- type => 'submit',
- class => 'submit',
- order => $i++};
- $button{'AR--Sales Invoice'} = {
- name => 'action',
- value => 'sales_invoice_',
- text => $locale->text('Sales Invoice'),
- type => 'submit',
- class => 'submit',
- order => $i++};
- }
- if ( $myconfig{acs} !~ /AP--AP/ ) {
- $button{'AP--Add Transaction'} = {
- name => 'action',
- value => 'ap_transaction',
- text => $locale->text('AP Transaction'),
- type => 'submit',
- class => 'submit',
- order => $i++};
- $button{'AP--Vendor Invoice'} = {
- name => 'action',
- value => 'vendor_invoice_',
- text => $locale->text('Vendor Invoice'),
- type => 'submit',
- class => 'submit',
- order => $i++};
- }
-
- foreach $item ( split /;/, $myconfig{acs} ) {
- delete $button{$item};
- }
-
- my @buttons;
- foreach my $item ( sort { $a->{order} <=> $b->{order} } %button ) {
- push @buttons, $item if ref $item;
- }
- push @buttons, {
- name => 'action',
- value => 'csv_gl_report',
- text => $locale->text('CSV Report'),
- type => 'submit',
- class => 'submit',
- };
- push @buttons, {
- name => 'action',
- value => 'csv_email_gl_report',
- text => $locale->text('Email CSV Report'),
- type => 'submit',
- class => 'submit',
- };
-
-##SC: Taking this out for now...
-## if ( $form->{lynx} ) {
-## require "bin/menu.pl";
-## &menubar;
-## }
-
- my %row_alignment = (
- 'balance' => 'right',
- 'debit' => 'right',
- 'credit' => 'right'
- );
- my $template;
- my $format = uc substr($form->{action}, 0, 3);
- my $template = LedgerSMB::Template->new(
- user => \%myconfig,
- locale => $locale,
- path => 'UI',
- template => 'form-dynatable',
- format => ($format ne 'CSV')? 'HTML': 'CSV',
- output_options => $output_options,
- );
- $template->{method} = 'email' if $output_options;
-
- my $column_heading = $template->column_heading($column_names,
- {href => $sort_href, columns => ..hidden..
- );
-
- $template->render({
- form => \%$form,
- buttons => ..hidden..,
- hiddens => \%hiddens,
- options => ..hidden..,
- columns => ..hidden..,
- heading => $column_heading,
- rows => ..hidden..,
- row_alignment => \%row_alignment,
- totals => \%column_data,
- });
-
-
- $form->info($locale->text('GL report sent to [_1]', $form->{login}));
-
-}
-
-
-
sub edit {
&create_links;
@@ -999,16 +571,6 @@
}
-sub csv_gl_report { &generate_report }
-sub csv_email_gl_report {
- ##SC: XXX hardcoded test values
- &generate_report({
- to => '..hidden..',
- from => '..hidden..',
- subject => 'CSV GL report',
- });
-}
-
sub gl_subtotal_tt {
my %column_data;
Modified: trunk/lsmb-request.pl
===================================================================
--- trunk/lsmb-request.pl 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/lsmb-request.pl 2012-03-26 09:03:00 UTC (rev 4589)
@@ -99,7 +99,7 @@
eval "require $script;"
|| $request->error($locale->text('Unable to open script') .
- ": $script : $!"
+ ": $script : $!: $@"
);
$script->can($request->{action})
|| $request->error($locale->text("Action Not Defined: ") . $request->{action});
Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql 2012-03-26 06:20:43 UTC (rev 4588)
+++ trunk/sql/modules/Report.sql 2012-03-26 09:03:00 UTC (rev 4589)
@@ -183,9 +183,10 @@
);
CREATE OR REPLACE FUNCTION report__gl
-(in_reference text, in_accno text, in_source text, in_memo text,
-in_description text, in_date_from date, in_date_to date, in_approved bool,
-in_amount_from numeric, in_amount_to numeric, in_business_units int[])
+(in_reference text, in_accno text, in_category char(1),
+in_source text, in_memo text, in_description text, in_date_from date,
+in_date_to date, in_approved bool, in_amount_from numeric, in_amount_to numeric,
+in_business_units int[])
RETURNS SETOF gl_report_item AS
$$
DECLARE
@@ -204,6 +205,15 @@
END IF;
FOR retval IN
+ WITH RECURSIVE bu_tree (id, path) AS (
+ SELECT id, id::text AS path
+ FROM business_unit
+ WHERE parent is null
+ UNION
+ SELECT id, bu_tree.path || , || id
+ FROM business_unit
+ JOIN bu_tree ON bu_tree.id = business_unit.parent
+ )
SELECT g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
ac.source, ac.amount, c.accno, c.gifi_accno,
g.till, ac.cleared, ac.memo, c.description AS accname,
@@ -227,7 +237,9 @@
= eca.id
JOIN entity e ON e.id = eca.entity_id) g
JOIN acc_trans ac ON ac.trans_id = g.id
- JOIN chart c ON ac.chart_id = c.id
+ JOIN account c ON ac.chart_id = c.id
+ LEFT JOIN business_unit_ac bac ON ac.entry_id = bac.entry_id
+ LEFT JOIN bu_tree ON bac.bu_id = bu_tree.id
WHERE (g.reference ilike in_reference || '%' or in_reference is null)
AND (c.accno = in_accno OR in_accno IS NULL)
AND (ac.source ilike '%' || in_source || '%'
@@ -243,17 +255,17 @@
AND (in_approved is false OR (g.approved AND ac.approved))
AND (in_amount_from IS NULL OR ac.amount >= in_amount_from)
AND (in_amount_to IS NULL OR ac_amount <= in_amount_to)
+ AND (in_category = c.category OR in_category IS NULL)
GROUP BY g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
ac.source, ac.amount, c.accno, c.gifi_accno,
g.till, ac.cleared, ac.memo, c.description,
ac.chart_id, ac.entry_id
- HAVING in_business_units <@ as_array(bac.bu_id)
+ HAVING in_business_units
+ <@ as_array(string_to_array(bu_tree.path, ',')::int[])
ORDER BY ac.transdate, g.trans_id, c.accno
LOOP
RETURN NEXT retval;
END LOOP;
-
-
END;
$$ language plpgsql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.