[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4732] addons/1.3/colombia_general/trunk
- Subject: SF.net SVN: ledger-smb:[4732] addons/1.3/colombia_general/trunk
- From: ..hidden..
- Date: Sat, 19 May 2012 15:26:06 +0000
Revision: 4732
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4732&view=rev
Author: einhverfr
Date: 2012-05-19 15:26:06 +0000 (Sat, 19 May 2012)
Log Message:
-----------
Merging in some previously uncommitted Colombia reporting work
Modified Paths:
--------------
addons/1.3/colombia_general/trunk/sql/modules/colombia-general.sql
Added Paths:
-----------
addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/
addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/
addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/
addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Balance_y_Mayor.pm
addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Caja_Diaria.pm
addons/1.3/colombia_general/trunk/UI/Report/
addons/1.3/colombia_general/trunk/UI/Report/co/
addons/1.3/colombia_general/trunk/UI/Report/co/filter_bm.html
addons/1.3/colombia_general/trunk/UI/Report/co/filter_cd.html
addons/1.3/colombia_general/trunk/scripts/
addons/1.3/colombia_general/trunk/scripts/lreport_co.pl
Added: addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Balance_y_Mayor.pm
===================================================================
--- addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Balance_y_Mayor.pm (rev 0)
+++ addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Balance_y_Mayor.pm 2012-05-19 15:26:06 UTC (rev 4732)
@@ -0,0 +1,194 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::co::Balance_y_Mayor - Colombian Balance/Ledger Rpt
+
+=head1 SYNPOSIS
+
+ my $bmreport = LedgerSMB::DBObject::Report::co::Balance_y_Mayor->new(%$request);
+ $bmreport->run;
+ $bmreport->render($request, $format);
+
+=head1 DESCRIPTION
+
+This module provides Balance y Mayor reports for LedgerSMB to Colombian
+standards. This report shows total activity over a time period.
+
+=head1 INHERITS
+
+=over
+
+=item LedgerSMB::DBObject::Report;
+
+=back
+
+=cut
+
+package LedgerSMB::DBObject::Report::co::Balance_y_Mayor;
+use Moose;
+extends 'LedgerSMB::DBObject::Report';
+
+use LedgerSMB::App_State;
+
+my $locale = $LedgerSMB::App_State::Locale;
+my $doctypes = {};
+
+=head1 PROPERTIES
+
+=over
+
+=item columns
+
+Read-only accessor, returns a list of columns.
+
+=over
+
+=item accno
+
+Account Number
+
+=item description
+
+Account name
+
+=item document_type
+
+=item debits
+
+=item credits
+
+=back
+
+=cut
+
+our @COLUMNS = (
+ {col_id => 'accno',
+ name => $locale->text('Account'),
+ type => 'href',
+ pwidth => 3,
+ href_base => '', },
+
+ {col_id => 'description',
+ name => $locale->text('Description'),
+ type => 'text',
+ pwidth => '12', },
+
+ {col_id => 'starting_balance',
+ name => $locale->text('Starting Balance'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'debits',
+ name => $locale->text('Debit'),
+ type => 'text',
+ pwidth => '4', },
+
+ {col_id => 'credits',
+ name => $locale->text('Credit'),
+ type => 'text',
+ pwidth => '4', },
+ {col_id => 'ending_balance',
+ name => $locale->text('Balance'),
+ type => 'text',
+ pwidth => '3', },
+
+);
+
+sub columns {
+ return ..hidden..;
+}
+
+
+=item filter_template
+
+Returns the template name for the filter.
+
+=cut
+
+sub filter_template {
+ return 'Reports/co/bm_filter';
+}
+
+=item name
+
+Returns the localized template name
+
+=cut
+
+sub name {
+ return $locale->text('Balance y Mayor');
+}
+
+=item header_lines
+
+Returns the inputs to display on header.
+
+=cut
+
+sub header_lines {
+ return [{name => 'date_from',
+ text => $locale->text('Start Date')},
+ {name => 'date_to',
+ text => $locale->text('End Date')},]
+}
+
+=head2 Criteria Properties
+
+Note that in all cases, undef matches everything.
+
+=item date_from (text)
+
+start date for the report
+
+=cut
+
+has 'date_from' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item date_to
+
+End date for the report
+
+=cut
+
+has 'date_to' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+
+=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});
+}
+
+=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__general_balance'});
+ $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;
Added: addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Caja_Diaria.pm
===================================================================
--- addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Caja_Diaria.pm (rev 0)
+++ addons/1.3/colombia_general/trunk/LedgerSMB/DBObject/Report/co/Caja_Diaria.pm 2012-05-19 15:26:06 UTC (rev 4732)
@@ -0,0 +1,212 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Report::co::Caja_Diaria - Caja Diaria Reports (Colombia)
+
+=head1 SYNPOSIS
+
+ my $cdreport = LedgerSMB::DBObject::Report::co::Caja_Diaria->new(%$request);
+ $cdreport->run;
+ $cdreport->render($request, $format);
+
+=head1 DESCRIPTION
+
+This module provides Caja Diaria eports for LedgerSMB to Colombian standards.
+These reports provide an overview of cash activity to a set of accounts for a
+specific period.
+
+=head1 INHERITS
+
+=over
+
+=item LedgerSMB::DBObject::Report;
+
+=back
+
+=cut
+
+package LedgerSMB::DBObject::Report::co::Caja_Diaria;
+use Moose;
+extends 'LedgerSMB::DBObject::Report';
+
+use LedgerSMB::App_State;
+
+my $locale = $LedgerSMB::App_State::Locale;
+my $doctypes = {};
+
+=head1 PROPERTIES
+
+=over
+
+=item columns
+
+Read-only accessor, returns a list of columns.
+
+=over
+
+=item accno
+
+Account Number
+
+=item description
+
+Account name
+
+=item document_type
+
+=item debits
+
+=item credits
+
+=back
+
+=cut
+
+our @COLUMNS = (
+ {col_id => 'accno',
+ name => $locale->text('Account'),
+ type => 'href',
+ pwidth => 3,
+ href_base => '', },
+
+ {col_id => 'description',
+ name => $locale->text('Description'),
+ type => 'text',
+ pwidth => '12', },
+
+ {col_id => 'document_type',
+ name => $locale->text('Document'),
+ type => 'text',
+ pwidth => '3', },
+
+ {col_id => 'debits',
+ name => $locale->text('Debit'),
+ type => 'text',
+ pwidth => '4', },
+
+ {col_id => 'credits',
+ name => $locale->text('Credit'),
+ type => 'text',
+ pwidth => '4', },
+
+);
+
+sub columns {
+ return ..hidden..;
+}
+
+
+=item filter_template
+
+Returns the template name for the filter.
+
+=cut
+
+sub filter_template {
+ return 'Reports/co/cj_filter';
+}
+
+=item name
+
+Returns the localized template name
+
+=cut
+
+sub name {
+ return $locale->text('Caja Diaria');
+}
+
+=item header_lines
+
+Returns the inputs to display on header.
+
+=cut
+
+sub header_lines {
+ return [{name => 'date_from',
+ text => $locale->text('Start Date')},
+ {name => 'date_to',
+ text => $locale->text('End Date')},
+ {name => 'accno',
+ text => $locale->text('Account Number Start')},
+ {name => 'reference',
+ text => $locale->text('Account Number End')},]
+}
+
+=head2 Criteria Properties
+
+Note that in all cases, undef matches everything.
+
+=item date_from (text)
+
+start date for the report
+
+=cut
+
+has 'date_from' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item date_to
+
+End date for the report
+
+=cut
+
+has 'date_to' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+
+=item from_accno
+
+=cut
+
+has 'from_accno' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item to_accno
+
+
+=cut
+
+has 'to_accno' => (is => 'rw', isa => 'Maybe[Str]');
+
+=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});
+}
+
+=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__cash_summary'});
+ for my $ref(@rows){
+ $ref->{document_type} = $doctypes->{$ref->{document_type}}
+ if $doctypes->{$ref->{document_type}};
+ }
+ $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;
Added: addons/1.3/colombia_general/trunk/UI/Report/co/filter_bm.html
===================================================================
--- addons/1.3/colombia_general/trunk/UI/Report/co/filter_bm.html (rev 0)
+++ addons/1.3/colombia_general/trunk/UI/Report/co/filter_bm.html 2012-05-19 15:26:06 UTC (rev 4732)
@@ -0,0 +1,153 @@
+<?lsmb INCLUDE 'ui-header.html'
+ include_script = ["UI/ajax/scriptaculous/lib/prototype.js","UI/ajax/scriptaculous/src/scriptaculous.js?load=builder,effects,dragdrop,controls","UI/ajax/helpers.js"]
+?>
+<?lsmb PROCESS 'elements.html' ?>
+<body>
+
+<form method="get" action="<?lsmb script ?>">
+<?lsmb PROCESS input element_data = {
+ type = "hidden"
+ name = "sort"
+ value = "transdate"
+} ?>
+
+<table width="100%">
+ <tr>
+ <th class="listtop"><?lsmb text('Balance y Mayor') ?></th>
+
+ </tr>
+ <tr height="5"></tr>
+ <tr>
+ <td>
+ <table>
+ <th align="right"><?lsmb text('From') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ class="date"
+ name="date_from"
+ size="11"
+ title=datestyle } ?></td>
+ <th align="right"><?lsmb text('To') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ class="date"
+ name="date_to"
+ size="11"
+ title=datestyle } ?></td>
+ </tr>
+ <td colspan="5">
+ <?lsmb PROCESS select element_data = {
+ name = "month"
+ options = accountingmonths
+ text_attr = "month"
+ value_attr = "id"
+ } ?>
+
+ <?lsmb PROCESS select element_data = {
+ name="year"
+ options = all_years
+ text_attr = "year"
+ value_attr = "year"
+ } ?>
+ <?lsmb PROCESS input element_data = {
+ name="interval"
+ class="radio"
+ type="radio"
+ value="0"
+ checked="checked" } ?> <?lsmb text('Current') ?>
+ <?lsmb PROCESS input element_data = {
+ name="interval"
+ class="radio"
+ type="radio"
+ value="1" } ?> <?lsmb text('Month') ?>
+ <?lsmb PROCESS input element_data = {
+ name="interval"
+ class="radio"
+ type="radio"
+ value="3" } ?> <?lsmb text('Quarter') ?>
+ <input name=interval class=radio type=radio value=12> Year
+ </td>
+ </tr>
+ <th align="right"><?lsmb text('Include in Report') ?></th>
+
+ <td colspan="5">
+ <table>
+ <tr>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_accno"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked = 'checked'
+ } ?> <?lsmb text('Account Number') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_description"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Account Name') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_starting_balance"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('starting_balance') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_debits"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('debits') ?></td>
+
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_credits"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Credit') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_balance"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked = 'checked'
+ } ?> <?lsmb text('balance') ?></td>
+ </tr>
+ </table>
+ </tr>
+
+ </table>
+ </td>
+ </tr>
+ <tr>
+ <td><hr size=3 noshade></td>
+ </tr>
+</table>
+
+<?lsmb PROCESS input element_data = {
+ type="hidden"
+ name="path"
+ value=path
+} ?>
+<?lsmb PROCESS input element_data = {
+ type="hidden"
+ name="login"
+ value=login
+} ?>
+
+<br>
+<?lsmb PROCESS button element_data = {
+ text = text('Continue')
+ class="submit"
+ type="submit"
+ name="action"
+ value="run_bm"
+} ?>
+</form>
+
+
+</body>
+</html>
+
Added: addons/1.3/colombia_general/trunk/UI/Report/co/filter_cd.html
===================================================================
--- addons/1.3/colombia_general/trunk/UI/Report/co/filter_cd.html (rev 0)
+++ addons/1.3/colombia_general/trunk/UI/Report/co/filter_cd.html 2012-05-19 15:26:06 UTC (rev 4732)
@@ -0,0 +1,128 @@
+<?lsmb INCLUDE 'ui-header.html'
+ include_script = ["UI/ajax/scriptaculous/lib/prototype.js","UI/ajax/scriptaculous/src/scriptaculous.js?load=builder,effects,dragdrop,controls","UI/ajax/helpers.js"]
+?>
+<?lsmb PROCESS 'elements.html' ?>
+<body>
+
+<form method="get" action="<?lsmb script ?>">
+<?lsmb PROCESS input element_data = {
+ type = "hidden"
+ name = "sort"
+ value = "transdate"
+} ?>
+
+<table width="100%">
+ <tr>
+ <th class="listtop"><?lsmb text('Caja Diaria') _ ' --- '
+ _ text('All fields are required') ?>
+ </th>
+
+ </tr>
+ <tr height="5"></tr>
+ <tr>
+ <td>
+ <table>
+ <tr>
+ <th align="right"><?lsmb text('Accounts From') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name = 'from_accno'
+ type = 'text'
+ size = 20 } ?></td>
+ <th align="right"><?lsmb text('Accounts To') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ name="to_accno"
+ size="20"
+ type="text"
+ } ?></td>
+ </tr>
+
+ <tr>
+
+ <th align="right"><?lsmb text('From') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ class="date"
+ name="date_from"
+ size="11"
+ title=datestyle } ?></td>
+ <th align="right"><?lsmb text('To') ?></th>
+ <td><?lsmb PROCESS input element_data = {
+ class="date"
+ name="date_to"
+ size="11"
+ title=datestyle } ?></td>
+ </tr>
+
+ <tr>
+ <th align="right"><?lsmb text('Include in Report') ?></th>
+
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_accno"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked = 'checked'
+ } ?> <?lsmb text('Account Number') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_description"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Account Name') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_document_type"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Document Type') ?></td>
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_debits"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Debits') ?></td>
+
+ <td nowrap><?lsmb PROCESS input element_data = {
+ name="col_credits"
+ class="checkbox"
+ type="checkbox"
+ value="Y"
+ checked="checked"
+ } ?> <?lsmb text('Credits') ?></td>
+ </tr>
+
+ </table>
+ </td>
+ </tr>
+ <tr>
+ <td><hr size=3 noshade></td>
+ </tr>
+</table>
+
+<?lsmb PROCESS input element_data = {
+ type="hidden"
+ name="path"
+ value=path
+} ?>
+<?lsmb PROCESS input element_data = {
+ type="hidden"
+ name="login"
+ value=login
+} ?>
+
+<br>
+<?lsmb PROCESS button element_data = {
+ text = text('Continue')
+ class="submit"
+ type="submit"
+ name="action"
+ value="run_caja_diaria"
+} ?>
+</form>
+
+
+</body>
+</html>
+
Added: addons/1.3/colombia_general/trunk/scripts/lreport_co.pl
===================================================================
--- addons/1.3/colombia_general/trunk/scripts/lreport_co.pl (rev 0)
+++ addons/1.3/colombia_general/trunk/scripts/lreport_co.pl 2012-05-19 15:26:06 UTC (rev 4732)
@@ -0,0 +1,101 @@
+=head1 NAME
+
+LedgerSMB::Scripts::lreports_co - Colombian local reports
+
+=head1 SYNOPSIS
+
+This module holds Colombia-specific reports.
+
+=head1 METHODS
+
+=cut
+
+package LedgerSMB::Scripts::lreports_co;
+our $VERSION = '1.0';
+
+use LedgerSMB;
+use LedgerSMB::Template;
+use LedgerSMB::DBObject::Report::co::Caja_Diaria;
+use LedgerSMB::DBObject::Report::co::Balance_y_Mayor;
+use strict;
+
+=pod
+
+=over
+
+=item begin_caja_diaria
+
+Displays the filter screen for Caja Diaria
+
+=cut
+
+sub start_caja_diaria {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/Reports/co',
+ template => 'filter_cd',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+=item begin_bm
+
+Displays the filter screen for Balance y Mayor
+
+=cut
+
+sub start_bm {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/Reports/co',
+ template => 'filter_bm',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+=item run_caja_diaria
+
+Runs a Caja Diaria and displays results.
+
+=cut
+
+sub run_caja_diaria {
+ my ($request) = @_;
+ LedgerSMB::DBObject::Report::co::Caja_Diaria->prepare_criteria($request);
+ my $report = LedgerSMB::DBObject::Report::co::Caja_Diaria->new(%$request);
+ $report->run_report;
+ $report->render($request);
+}
+
+=item run_bm
+
+Runs Balance y Mayor and displays results.
+
+=cut
+
+sub run_bm {
+ my ($request) = @_;
+ LedgerSMB::DBObject::Report::co::Balance_y_Mayor->prepare_criteria($request);
+ my $report = LedgerSMB::DBObject::Report::co::Balance_y_Mayor->new(%$request);
+ $report->run_report;
+ $report->render($request);
+}
+
+=back
+
+=head1 Copyright (C) 2007 The LedgerSMB Core Team
+
+Licensed under the GNU General Public License version 2 or later (at your
+option). For more information please see the included LICENSE and COPYRIGHT
+files.
+
+=cut
+
+eval { do "scripts/custom/lreports_co.pl"};
+1;
Modified: addons/1.3/colombia_general/trunk/sql/modules/colombia-general.sql
===================================================================
--- addons/1.3/colombia_general/trunk/sql/modules/colombia-general.sql 2012-05-19 10:05:32 UTC (rev 4731)
+++ addons/1.3/colombia_general/trunk/sql/modules/colombia-general.sql 2012-05-19 15:26:06 UTC (rev 4732)
@@ -21,4 +21,80 @@
$$ LANGUAGE PLPGSQL;
+DROP TYPE IF EXISTS cash_summary_item CASCADE;
+
+CREATE TYPE cash_summary_item AS (
+ account_id int,
+ accno text,
+ is_heading bool,
+ description text,
+ document_type text,
+ debits numeric,
+ credits numeric
+);
+
+CREATE OR REPLACE FUNCTION report__cash_summary
+(in_date_from date, in_date_to date, in_from_accno text, in_to_accno text)
+RETURNS SETOF cash_summary_item AS
+$$
+SELECT a.id, a.accno, a.is_heading, a.description, t.label,
+ sum(CASE WHEN ac.amount < 0 THEN ac.amount * -1 ELSE NULL END),
+ sum(CASE WHEN ac.amount > 0 THEN ac.amount ELSE NULL END)
+ FROM (select id, accno, false as is_heading, description FROM account
+ UNION
+ SELECT id, accno, true, description FROM account_heading) a
+ LEFT
+ JOIN acc_trans ac ON ac.chart_id = a.id
+ LEFT
+ JOIN (select id, case when table_name ilike 'ar' THEN 'rcpt'
+ when table_name ilike 'ap' THEN 'pmt'
+ when table_name ilike 'gl' THEN 'xfer'
+ END AS label
+ FROM transactions) t ON t.id = ac.trans_id
+ WHERE accno BETWEEN $3 AND $4
+ and ac.transdate BETWEEN $1 AND $2
+GROUP BY a.id, a.accno, a.is_heading, a.description, t.label
+ORDER BY accno;
+
+$$ LANGUAGE SQL;
+
+DROP TYPE IF EXISTS general_balance_line CASCADE;
+
+CREATE TYPE general_balance_line AS (
+ account_id int,
+ account_accno text,
+ account_description text,
+ starting_balance numeric,
+ debits numeric,
+ credits numeric,
+ final_balance numeric
+);
+
+CREATE OR REPLACE FUNCTION report__general_balance
+(in_date_from date, in_date_to date)
+RETURNS SETOF general_balance_line AS
+$$
+
+SELECT a.id, a.accno, a.description,
+ sum(CASE WHEN ac.transdate < $1 THEN abs(amount) ELSE null END),
+ sum(CASE WHEN ac.transdate >= $1 AND ac.amount < 0
+ THEN ac.amount * -1 ELSE null END),
+ SUM(CASE WHEN ac.transdate >= $1 AND ac.amount > 0
+ THEN ac.amount ELSE null END),
+ SUM(ABS(ac.amount))
+ FROM account a
+ LEFT
+ JOIN acc_trans ac ON ac.chart_id = a.id
+ LEFT
+ JOIN (select id, approved from ar UNION
+ SELECT id, approved from ap UNION
+ SELECT id, approved FROM gl) gl ON ac.trans_id = gl.id
+WHERE gl.approved and ac.approved
+ and ac.transdate <= $2
+GROUP BY a.id, a.accno, a.description
+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.