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

SF.net SVN: ledger-smb:[4732] addons/1.3/colombia_general/trunk



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" } ?>&nbsp;<?lsmb text('Current') ?>
+	<?lsmb PROCESS input element_data = { 
+               name="interval" 
+               class="radio" 
+               type="radio" 
+               value="1" } ?>&nbsp;<?lsmb text('Month') ?>
+	<?lsmb PROCESS input element_data = {
+               name="interval"
+               class="radio"
+               type="radio"
+               value="3" } ?>&nbsp;<?lsmb text('Quarter') ?>
+	<input name=interval class=radio type=radio value=12>&nbsp;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.