[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3059]
- Subject: SF.net SVN: ledger-smb:[3059]
- From: ..hidden..
- Date: Fri, 10 Sep 2010 20:33:58 +0000
Revision: 3059
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3059&view=rev
Author: aurynn_cmd
Date: 2010-09-10 20:33:58 +0000 (Fri, 10 Sep 2010)
Log Message:
-----------
Adding the trial balance enhancements, as they do not appear to have been added properly.
Adding updates to the extended ARAP Ajax controls. The ajax controller requires parameters: query_string to function correctly. elements.html has been changed to represent this style.
Modification to the aa.html to use the new Ajax control.
Modified Paths:
--------------
addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
addons/1.3/extended_arap/trunk/UI/aa.html
trunk/UI/lib/elements.html
Added Paths:
-----------
addons/1.3/enhanced_tb/trunk/LedgerSMB/DBObject/TrialBalance.pm
addons/1.3/enhanced_tb/trunk/UI/
addons/1.3/enhanced_tb/trunk/UI/rp-search.html
addons/1.3/enhanced_tb/trunk/UI/trial_balance/
addons/1.3/enhanced_tb/trunk/UI/trial_balance/reports.html
addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html
addons/1.3/enhanced_tb/trunk/UI/trial_balance/view.html
addons/1.3/enhanced_tb/trunk/css/
addons/1.3/enhanced_tb/trunk/css/trial_balance/
addons/1.3/enhanced_tb/trunk/css/trial_balance/reports.css
addons/1.3/enhanced_tb/trunk/css/trial_balance/search.css
addons/1.3/enhanced_tb/trunk/css/trial_balance/view.css
addons/1.3/enhanced_tb/trunk/css/trial_balance.css
addons/1.3/enhanced_tb/trunk/scripts/trial_balance.pl
addons/1.3/enhanced_tb/trunk/sql/modules/EndOfYear.sql
addons/1.3/enhanced_tb/trunk/trial_balance.pl
Added: addons/1.3/enhanced_tb/trunk/LedgerSMB/DBObject/TrialBalance.pm
===================================================================
--- addons/1.3/enhanced_tb/trunk/LedgerSMB/DBObject/TrialBalance.pm (rev 0)
+++ addons/1.3/enhanced_tb/trunk/LedgerSMB/DBObject/TrialBalance.pm 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,193 @@
+package LedgerSMB::DBObject::TrialBalance;
+use Data::Dumper;
+use base LedgerSMB::DBObject;
+
+# This is wrong. This is all wrong.
+
+sub fromId {
+
+ my ($self, $report_id) = @_;
+
+ my @report = $self->exec_method(funcname=>"trial_balance__get", args=>[$report_id]);
+ $self->merge($report[0]); # There's only a single result row, but always an array.
+ return $self;
+}
+
+sub __attr {
+
+ # Returns the specified attribute
+ my ($self, $key) = @_;
+ return $self->{$key};
+}
+
+sub id {
+ return $self->__attr('id');
+}
+
+sub accounts {
+ # More complex, as this has to query the database and return
+ # a list of all accounts associated with this record_id.
+
+ if ($self->{id}) {
+ if (!defined($self->{accounts})) {
+ @{$self->{accounts}} = $self->exec_method(funcname=>"trial_balance__contents");
+ }
+ return @{ $self->{accounts} };
+ }
+ return $self->error("No ID found!");
+}
+
+sub date_from {
+ my ($self) = @_;
+ return $self->__attr('date_from');
+}
+
+sub date_to {
+ my ($self) = @_;
+ return $self->__attr('date_to');
+}
+
+sub description {
+ my ($self) = @_;
+ return $self->__attr('description');
+}
+
+sub save {
+ my ($self) = @_;
+
+ my $row = $self->exec_method(funcname=>"trial_balance__save",
+ args=> [
+ $self->{id} ? $self->{id} : undef,
+ $self->{date_from},
+ $self->{date_to},
+ $self->{description},
+ $self->{ignore_yearend},
+ $self->{heading},
+ $self->_db_array_literal( @{$self->{accounts}} ),
+ ]
+ );
+}
+
+sub contents {
+ # Returns the list of account IDs, unioned with all heading account IDs,
+ # that constitutes a given report.
+ my ($self) = @_;
+
+ my @accounts = $self->exec_method(funcname=>"trial_balance__accounts")
+}
+
+sub run {
+
+ # Runs this report.
+ my ($self, $ignore_yearend) = @_;
+
+ if (!$ignore_yearend) {
+ $ignore_yearend = $self->{ignore_yearend};
+ }
+
+ my @lines = $self->exec_method(funcname=>"trial_balance__account_data", args=>[
+ $self->{date_from},
+ $self->{date_to},
+ $self->{heading},
+ $self->_db_array_literal( @{$self->{accounts}} ), # Creates the array literal
+ $ignore_yearend,
+ $self->{department}
+ ]);
+ return ..hidden..;
+}
+
+sub delete {
+
+ my ($self) = @_;
+ my $ret = $self->exec_method(funcname=>"trial_balance__delete", args=>[$self->{id}]);
+ return $ret;
+}
+
+sub request_merge {
+ # report is self, here.
+ my ($report, $request) = @_;
+ my @accounts;
+ if ($request->{accounts}) {
+ @accounts = split /\0/, $request->{accounts};
+ }
+
+ $report->{date_from} = $request->{date_from} if $request->{date_from};
+ $report->{date_to} = $request->{date_to} if $request->{date_to};
+ if (@accounts && scalar @accounts >= 1) {
+ $report->{accounts} = ..hidden..;
+ }
+ if ($request->{heading}) {
+ $report->{heading} = $request->{heading};
+ }
+ if ($request->{ignore_yearend}) {
+ $report->{ignore_yearend} = $request->{ignore_yearend};
+ }
+}
+
+##
+# Global items, not affected by the object state.
+##
+
+sub reports {
+ # Selects all report IDs from the database.
+
+ my ($self) = @_;
+
+ my @reports = $self->exec_method(funcname=>"trial_balance__list", args=>[]);
+
+ return @reports;
+}
+
+
+sub values {
+
+ # Returns the account values for a given report, as well
+ # as the final value of the account.
+
+ my ($self, $report_id) = @_;
+
+ my @accounts = $self->contents();
+ my $final = $self->exec_method(funcname=>"trial_balance__get", args=>[$report_id]);
+
+ return @accounts, $final;
+}
+
+sub all_years {
+
+ my ($self) = @_;
+
+ my @years = $self->exec_method(funcname=>"date_get_all_years", args=>[]);
+ return ..hidden..;
+}
+
+# Requires the base addon.
+sub departments {
+
+ my ($self) = @_;
+ my @deps = $self->exec_method(funcname=>"department__list_all", args=>[]);
+ return ..hidden..;
+}
+
+# Returns all the headings and accounts, as a nested array,
+sub headings {
+
+ my ($self) = @_;
+ my @headings = $self->exec_method(funcname=>"trial_balance__list_headings", args=>[]);
+ for my $heading(@headings) {
+ $heading->{accounts} = $self->heading_accounts($heading->{accounts});
+ }
+ return ..hidden..;
+}
+
+sub heading_accounts {
+ my ($self, $heading_id) = @_;
+
+ my @accounts = $self->exec_method(
+ funcname=>"trial_balance__heading_accounts", args=>[
+ $self->_db_array_literal(@{$heading_id})
+ ]
+ );
+ return ..hidden..;
+}
+
+1;
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/UI/rp-search.html
===================================================================
--- addons/1.3/enhanced_tb/trunk/UI/rp-search.html (rev 0)
+++ addons/1.3/enhanced_tb/trunk/UI/rp-search.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,67 @@
+<?lsmb INCLUDE 'ui-header.html' ?>
+<?lsmb PROCESS elements.html ?>
+<body>
+<form method="post" action="<?lsmb form.script ?>">
+<table width="100%">
+ <tr><th class="listtop"><?lsmb form.title ?></th></tr>
+ <tr><td> </td></tr>
+ <tr>
+ <td>
+ <table>
+<?lsmb IF form.selectdepartment.defined ?>
+ <tr>
+ <th align="right"><?lsmb text('Department') ?></th>
+ <td colspan="3"><?lsmb PROCESS select element_data=form.selectdepartment ?></td>
+ </tr>
+<?lsmb END ?>
+<?lsmb INCLUDE "rp-search-generate_trial_balance.html" ?>
+<?lsmb IF gifi ?>
+ <tr>
+ <th align="right"><?lsmb text('Accounts') ?></th>
+ <td>
+ <?lsmb PROCESS input element_data={
+ type => 'radio',
+ name => 'accounttype',
+ value => 'standard',
+ label => text('Standard'),
+ checked => 'checked',
+ } -?>
+ <?lsmb PROCESS input element_data={
+ type => 'radio',
+ name => 'accounttype',
+ value => 'gifi',
+ label => text('GIFI'),
+ } -?>
+ </td>
+ </tr>
+<?lsmb END ?>
+<?lsmb IF form.report == 'trial_balance' or form.report == 'projects' ?>
+ <tr>
+ <th align="right"><?lsmb text('Ignore Year-ends'); ?></th>
+ <td>
+ <?lsmb PROCESS select element_data = {
+ options = form.yearend_options,
+ default_values = [form.ignore_yearend],
+ name = 'ignore_yearend',
+ text_attr = 'label',
+ value_attr = 'id',
+ }?>
+ </td>
+ </tr>
+<?lsmb END ?>
+ </table>
+ </td>
+ </tr>
+ <tr><td><hr size="3" noshade="noshade" /></td></tr>
+</table>
+<br />
+<?lsmb FOREACH hidden IN hiddens.keys;
+ PROCESS input element_data={
+ type => 'hidden',
+ name => hidden,
+ value => hiddens.item(hidden)
+ }; END ?>
+<?lsmb FOREACH button IN buttons; PROCESS button element_data=button; END ?>
+</form>
+</body>
+</html>
Added: addons/1.3/enhanced_tb/trunk/UI/trial_balance/reports.html
===================================================================
--- addons/1.3/enhanced_tb/trunk/UI/trial_balance/reports.html (rev 0)
+++ addons/1.3/enhanced_tb/trunk/UI/trial_balance/reports.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,55 @@
+<?lsmb include_stylesheet = ['css/trial_balance/reports.css']?>
+<?lsmb INCLUDE 'ui-header.html' ?>
+<?lsmb PROCESS elements.html?>
+
+<body>
+ <table width="100%">
+ <th class="listtop" width="100%" colspan="6">
+ Trial Balance Reports
+ </th>
+ <tr class="expander"></tr>
+
+ <tr class="title">
+ <td class="id">
+ <?lsmb text("ID")?>
+ </td>
+ <td class="description">
+ <?lsmb text("Description")?>
+ </td>
+ <td>
+ <?lsmb text("Start Date")?>
+ </td>
+
+ <td>
+ <?lsmb text("End Date")?>
+ </td>
+ <td>
+ <?lsmb text("Actions")?>
+ </td>
+ </tr>
+
+ <?lsmb FOREACH report IN reports?>
+ <tr class="list <?lsmb loop.count mod 2 ? "odd" : "even" ?>">
+ <td class="id">
+ <a href="<?lsmb uri _ "?report_id="_ report.id _ "&action=run"?>">
+ <?lsmb report.id?>
+ </a>
+ </td>
+ <td class="description">
+ <?lsmb report.description?>
+ </td>
+ <td class="date">
+ <?lsmb report.date_from?>
+ </td>
+ <td class="date">
+ <?lsmb report.date_to?>
+ </td>
+ <td class="actions">
+ <a href="<?lsmb uri _ "?report_id=" _ report.id _ "&action=run"?>">[run]</a>
+ <a href="<?lsmb uri _ "?report_id=" _ report.id _ "&action=edit"?>">[edit]</a>
+ <a href="<?lsmb uri _ "?report_id=" _ report.id _ "&action=delete"?>">[X]</a>
+ </td>
+ </tr>
+ <?lsmb END?>
+ </table>
+</body>
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html
===================================================================
--- addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html (rev 0)
+++ addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,167 @@
+<?lsmb include_stylesheet = ['css/trial_balance/search.css']?>
+<?lsmb INCLUDE 'ui-header.html' ?>
+<?lsmb PROCESS elements.html ?>
+
+<body>
+ <form method="post" action="trial_balance.pl">
+ <?lsmb IF report.id;
+ INCLUDE input element_data = {
+ type="hidden",
+ name="report_id",
+ value=report.id
+ };
+ END;?>
+ <table width="100%">
+ <tr><th class="listtop">Trial Balance <?lsmb id ? 'Edit' : 'Create'?> Report</th></tr>
+ <tr><td> </td></tr>
+ <tr>
+ <td>
+ <table>
+ <tr>
+ <th align="right"><?lsmb text('Department') ?></th>
+ <td colspan="3">
+ <?lsmb d = []; FOR i IN departments;
+ d.push({
+ text=>i,
+ value=>loop
+ });
+ END;
+ PROCESS select element_data={
+ name=department,
+ default_options=[department],
+ options=d
+ } -?>
+ </td>
+ </tr>
+ <tr>
+ <th align="right"><?lsmb text('From') ?></th>
+ <td>
+ <?lsmb PROCESS input element_data={
+ class => 'date',
+ name => 'date_from',
+ size => '11',
+ title => user.dateformat,
+ value => report.date_from,
+ } ?>
+ </td>
+ <th align="right"><?lsmb text('To') ?></th>
+ <td>
+ <?lsmb PROCESS input element_data={
+ class => 'date',
+ name => 'date_to',
+ size => '11',
+ title => user.dateformat,
+ value => report.date_to,
+ } ?>
+ </td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ <tr>
+ <td>
+ <table>
+ <tr>
+ <th align="right"><?lsmb text('Ignore Year-ends'); ?></th>
+ <td><?lsmb PROCESS select element_data = {
+ options = [
+ {
+ label="All",
+ id="all",
+ },
+ {
+ label="Last Only",
+ id="last"
+ },
+ {
+ label="None",
+ id="none",
+ }
+ ],
+ default_values = [report.ignore_yearend],
+ name = 'ignore_yearend',
+ text_attr = 'label',
+ value_attr = 'id',
+ } ?>
+ </td>
+ </tr>
+ <tr>
+ <th align="righ"><?lsmb text("Description")?></th>
+ <td colspan="2">
+ <?lsmb PROCESS input element_data={
+ name="description",
+ value=report.description,
+ size=65,
+ class= 'text'
+ }?>
+ </td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ <tr><td><hr size="3" noshade="noshade" /></td></tr>
+ </table>
+ <table width="100%">
+ <?lsmb # Add in the list of accounts here.?>
+ <?lsmb FOR heading IN headings?>
+ <tr class="heading">
+ <td class="title"><?lsmb heading.accno?> -- <?lsmb heading.description?></td>
+ <td class="check">
+ <?lsmb IF report.heading_id;
+ IF report.heading_id == heading.id;
+ checked="checked";
+ END;
+ ELSE;
+ checked=undef;
+ END;
+ PROCESS input element_data = {
+ type="radio",
+ name="heading",
+ value=heading.id,
+ checked=checked
+ }?>
+ </td>
+ </tr>
+ <?lsmb FOR account IN heading.accounts?>
+ <tr class="account">
+ <td class="title"><?lsmb account.accno?> -- <?lsmb account.description?> </td>
+ <td class="check">
+ <?lsmb id = account.id; PROCESS input element_data = {
+ type="checkbox",
+ name="accounts",
+ value=account.id
+ checked=report_accounts.$id
+ }?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ <?lsmb END?>
+ </table>
+ <br />
+
+ <?lsmb PROCESS button element_data={
+ text => 'Save',
+ attributes => {
+ name=>'action',
+ value=>'save'
+ }
+ }?>
+ <?lsmb PROCESS button element_data={
+ text => 'Run',
+ attributes => {
+ name=>'action',
+ value=>'run'
+ }
+ }?>
+
+ <?lsmb PROCESS button element_data={
+ text => 'Save & Run',
+ attributes => {
+ name=>'action',
+ value=>'save_and_run'
+ }
+ }?>
+
+ </form>
+ </body>
+</html>
Added: addons/1.3/enhanced_tb/trunk/UI/trial_balance/view.html
===================================================================
--- addons/1.3/enhanced_tb/trunk/UI/trial_balance/view.html (rev 0)
+++ addons/1.3/enhanced_tb/trunk/UI/trial_balance/view.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,121 @@
+<?lsmb include_stylesheet = ['css/trial_balance/view.css']?>
+<?lsmb INCLUDE 'ui-header.html' ?>
+<?lsmb PROCESS elements.html?>
+
+<body>
+ <table width="100%">
+ <th class="listtop" width="100%" colspan="6">
+ <?lsmb title?>
+ </th>
+ <tr class="expander"></tr>
+ <tr class="title">
+ <td class="accountname">
+ <?lsmb text("Account")?>
+ </td>
+ <td class="description">
+ <?lsmb text("Description")?>
+ </td>
+
+ <td>
+ <?lsmb text("Start Balance")?>
+ </td>
+
+ <td>
+ <?lsmb text("Debits")?>
+ </td>
+ <td>
+ <?lsmb text("Credits")?>
+ </td>
+ <td>
+ <?lsmb text("End Balance")?>
+ </td>
+ </tr>
+
+ <?lsmb FOREACH account IN accounts?>
+ <tr class="list <?lsmb loop.count mod 2 ? "odd" : "even" ?>">
+ <td class="accounttitle">
+ <a href="gl.pl?action=generate_report&accounttype=<?lsmb account.gifi_accno ? 'gifi' : 'standard'?>&datefrom=<?lsmb report.date_from ?>&dateto=<?lsmb report.date_to ?>&sort=transdate&l_subtotal=Y&l_balance=Y&department=<?lsmb report.department ?>&title=<?lsmb "Trial Balance Individual Transactions"?>&category=X&l_reference=Y&l_transdate=Y&l_description=Y&l_debit=Y&l_credit=Y<?lsmb IF account.gifi_accno; "&gifi_accno=" _ account.gifi_accno _ "&gifi_description=" _ account.account_desc; ELSE; "&accno=" _ account.account_number; END;?>"><?lsmb account.account_number?></a>
+ </td>
+ <td class="description">
+ <?lsmb account.account_desc?>
+ </td>
+ <td class="number">
+ <?lsmb account.starting_balance?>
+ </td>
+ <td class="number">
+ <?lsmb account.debits?>
+ </td>
+ <td class="number">
+ <?lsmb account.credits?>
+ </td>
+ <td class="number">
+ <?lsmb account.ending_balance ?>
+ </td>
+ </tr>
+ <?lsmb END?>
+ <tr class="expander"></tr>
+ <?lsmb # and now the total value?>
+ <tr class="list listtop">
+ <td></td>
+ <td colspan="2">Totals</td>
+ <td class="number"><?lsmb total_debits?></td>
+ <td class="number"><?lsmb total_credits?></td>
+ <td></td>
+ </tr>
+ </table>
+ <form method="post" action="trial_balance.pl">
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="report_id"
+ value=report.id
+ }?>
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="date_from"
+ value=report.date_from
+ }?>
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="date_to"
+ value=report.date_to
+ }?>
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="heading"
+ value=report.heading
+ }?>
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="ignore_yearend"
+ value=report.ignore_yearend
+ }?>
+
+ <?lsmb FOR acc IN report.accounts?>
+ <?lsmb INCLUDE input element_data = {
+ type="hidden"
+ name="account"
+ value=acc
+ }?>
+ <?lsmb END?>
+
+ <table>
+ <tr width="100%">
+ <td><?lsmb text("Save as")?></td>
+ <td align="left">
+ <?lsmb INCLUDE button element_data = {
+ name="action",
+ value="csv",
+ text="CSV"
+ }?>
+ </td>
+ <td align="left">
+ <?lsmb INCLUDE button element_data = {
+ name="action",
+ value="xls",
+ text="Excel"
+ }?>
+ </td>
+ </tr>
+ </table>
+ </form>
+</body>
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/css/trial_balance/reports.css
===================================================================
--- addons/1.3/enhanced_tb/trunk/css/trial_balance/reports.css (rev 0)
+++ addons/1.3/enhanced_tb/trunk/css/trial_balance/reports.css 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,29 @@
+tr.expander {
+ height: 1em;
+}
+tr.title td.accountname {
+ width: 10%;
+}
+tr.title td.description {
+ width: 60%;
+}
+
+tr.title td {
+ text-align: center;
+}
+
+tr.odd {
+ background-color: #cddef4;
+}
+
+tr.list td.accounttitle {
+ padding-left: 0.7em;
+}
+tr.list td.description {
+ padding-left: 0.4em;
+}
+
+tr.list td.number {
+ text-align: right;
+ padding-right: 0.3em;
+}
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/css/trial_balance/search.css
===================================================================
--- addons/1.3/enhanced_tb/trunk/css/trial_balance/search.css (rev 0)
+++ addons/1.3/enhanced_tb/trunk/css/trial_balance/search.css 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,11 @@
+tr.heading td.title {
+ text-align: center;
+ width: 100%;
+}
+tr.heading td {
+ background-color: rgb(165,195,216);
+}
+
+tr.account td.title {
+ padding-left: 3em;
+}
Added: addons/1.3/enhanced_tb/trunk/css/trial_balance/view.css
===================================================================
--- addons/1.3/enhanced_tb/trunk/css/trial_balance/view.css (rev 0)
+++ addons/1.3/enhanced_tb/trunk/css/trial_balance/view.css 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,29 @@
+tr.expander {
+ height: 1em;
+}
+tr.title td.accountname {
+ width: 10%;
+}
+tr.title td.description {
+ width: 60%;
+}
+
+tr.title td {
+ text-align: center;
+}
+
+tr.odd {
+ background-color: #cddef4;
+}
+
+tr.list td.accounttitle {
+ padding-left: 0.7em;
+}
+tr.list td.description {
+ padding-left: 0.4em;
+}
+
+tr.list td.number {
+ text-align: right;
+ padding-right: 0.3em;
+}
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/css/trial_balance.css
===================================================================
--- addons/1.3/enhanced_tb/trunk/css/trial_balance.css (rev 0)
+++ addons/1.3/enhanced_tb/trunk/css/trial_balance.css 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,11 @@
+tr.heading td.title {
+ text-align: center;
+ width: 100%;
+}
+tr.heading td {
+ background-color: rgb(165,195,216);
+}
+
+tr.account td.title {
+ padding-left: 3em;
+}
Added: addons/1.3/enhanced_tb/trunk/scripts/trial_balance.pl
===================================================================
--- addons/1.3/enhanced_tb/trunk/scripts/trial_balance.pl (rev 0)
+++ addons/1.3/enhanced_tb/trunk/scripts/trial_balance.pl 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,305 @@
+package LedgerSMB::Scripts::trial_balance;
+use LedgerSMB::DBObject::TrialBalance;
+use LedgerSMB::Template;
+use Math::BigFloat;
+use strict;
+use Data::Dumper;
+
+sub __default {
+ return reports(@_);
+}
+
+sub reports {
+ my ($request) = @_;
+
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+
+ my @reports = $report->reports();
+ my $locale = $request->{_locale};
+
+ my $template = new LedgerSMB::Template(
+ path=>'UI/trial_balance',
+ template=>'reports',
+ format=>'HTML',
+ );
+
+ return $template->render({
+ reports => ..hidden..,
+ uri=> "trial_balance.pl",
+ stylesheet=>$request->{stylesheet},
+ locale=> $locale,
+ });
+}
+
+sub run {
+ my ($request) = @_;
+
+ my $template = new LedgerSMB::Template(
+ path=>'UI/trial_balance',
+ template=>'view',
+ format=>'HTML',
+ );
+
+ my ($rep_acc, $total_debits, $total_credits, $report) = &_run($request);
+
+ return $template->render({
+ title => "Trial Balance Report",
+ accounts=>$rep_acc,
+ format_amount=>sub { $request->format_amount(@_) },
+ stylesheet=>$request->{stylesheet},
+ total_debits=>$total_debits->bstr(),
+ total_credits =>$total_credits->bstr(),
+ report=>$report,
+ });
+}
+
+sub new {
+ my ($request) = @_;
+ if ($request->{report_id}) {
+ delete ( $request->{report_id} );
+ }
+ return &edit(@_);
+}
+
+sub edit {
+
+ # Uses the rp-search page of yore.
+ # Has been refactored to make the output specific to the trial balance
+ # generation.
+
+ my ($request) = @_;
+ my $template = new LedgerSMB::Template(
+ path=>'UI/trial_balance',
+ template=>'search',
+ format=>'HTML'
+ );
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+ if ($request->{report_id}) {
+ $report->fromId($request->{report_id});
+ my $report_accounts = {};
+ for my $account (@{$report->{accounts}}) {
+ $report_accounts->{$account} = 1;
+ }
+ return $template->render(
+ {
+ form=>$request,
+ departments=>$report->departments(),
+ headings=> $report->headings(),
+ report=> $report,
+ report_accounts=>$report_accounts
+ }
+ );
+ }
+ else {
+ # We're just in New mode, which is fine.
+ return $template->render(
+ {
+ form=>$request,
+ departments=>$report->departments(),
+ headings=> $report->headings()
+ }
+ );
+ }
+}
+
+sub save {
+ my ($request) = @_;
+
+ # We need a date_start, a date_end, a description, and
+ # a whole damn lot of account IDs.
+ # I believe we can assume that a series of checkboxes
+ # on the HTML end are translated into an array on the
+ # Perl end of things, in the request object.
+
+ if ($request->{date_from} &&
+ $request->{date_to} &&
+ $request->{description} &&
+ ($request->{accounts} || $request->{heading})
+ ) {
+ print STDERR "First if block";
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+ if ($request->{report_id}) {
+ # Existing entry; we should attempt an update.
+ $report->fromId($request->{report_id});
+ if (!$report->{id}) {
+ $request->error("Trial Balance report not found!");
+ }
+ }
+
+ $report->request_merge($request);
+
+ $report->save();
+ $report->commit();
+ return &reports($request);
+ }
+}
+
+sub save_and_run {
+ my ($request) = @_;
+
+ if ($request->{date_from} &&
+ $request->{date_to} &&
+ $request->{description} &&
+ ($request->{accounts} || $request->{heading})
+ ) {
+
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+ if ($request->{report_id}) {
+ # Existing entry; we should attempt an update.
+ $report->fromId($request->{report_id});
+ if (!$report->{id}) {
+ $request->error("Trial Balance report not found!");
+ }
+ }
+ # Else, let the underlying sproc logic handle it.
+
+ $report->request_merge($request);
+
+ $report->save();
+ $report->commit();
+
+ return &run($request);
+ }
+}
+
+sub delete {
+
+ my ($request) = @_;
+ if ($request->{report_id}) {
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+ $report->fromId($request->{report_id});
+ if ($report->{id}) { # it actually exists.
+
+ $report->delete();
+ $report->commit();
+ }
+ }
+ return &__default($request);
+}
+
+sub csv {
+ my ($request) = @_;
+ return &_dynatable($request, 'CSV');
+}
+
+sub xls {
+ my ($request) = @_;
+ return &_dynatable($request, 'XLS');
+}
+
+sub _dynatable {
+ my ($request, $type) = @_;
+
+ # This requires all the report information.
+ # For these, this is expected to be entirely in hidden input fields.
+
+ my ($rep_acc, $total_debits, $total_credits )= &_run($request);
+ if (scalar @{$rep_acc} > 0) {
+
+ my $template = LedgerSMB::Template->new(
+ locale => $request->{_locale},
+ user => $request->{user},
+ template => 'form-dynatable',
+ language => $request->{user}->{language},
+ format => $type,
+ path=>"UI"
+ );
+ my $columns;
+ @$columns = qw(accno description start_balance debits credits end_balance);
+
+ my $column_names = {
+ accno => 'account_number',
+ description => 'description',
+ start_balance => 'start_balance',
+ debits => 'debits',
+ credits => 'credits',
+ end_balance => 'end_balance'
+ };
+ my $column_heading = $template->column_heading($column_names);
+
+ my $rows = [];
+
+ for my $acc (@{$rep_acc}) {
+ push @{$rows}, {
+ accno => $acc->{account_number},
+ description => $acc->{account_desc},
+ start_balance => $acc->{start_balance},
+ debits => $acc->{debits},
+ credits => $acc->{credits},
+ end_balance => $acc->{end_balance}
+ };
+ }
+
+ push @{$rows}, {
+ accno => '',
+ description => 'Total',
+ start_balance => '',
+ debits => $total_debits->bstr(),
+ credits => $total_credits->bstr(),
+ end_balance=>''
+ };
+
+ return $template->render({
+ form => $request,
+ columns => $columns,
+ heading => $column_heading,
+ rows => $rows,
+ });
+ }
+ else {
+ # It's an error.
+ $request->error("Trial Balance Not Found");
+ }
+
+}
+
+sub _run {
+
+ # Not the primary Run function. Just encapsulates all the run behavhiour into a single spot.
+ my ($request) = @_;
+ my $report = new LedgerSMB::DBObject::TrialBalance(base=>$request);
+
+ if ( $request->{report_id} ) {
+
+ $report->fromId($request->{report_id});
+ }
+
+ $report->request_merge($request);
+
+ my @rep_acc = ();
+
+ my $total_debits = new Math::BigFloat(0);
+ my $total_credits = new Math::BigFloat(0);
+ # print STDERR Dumper($report->run());
+
+ for my $acc ( @{$report->run( $report->{ignore_yearend} ) } ) {
+
+ $total_debits += $acc->{debits};
+ $total_credits += $acc->{credits};
+
+ $acc->{debits} = $request->format_amount(
+ {
+ amount=>$acc->{debits},
+ format=>'1,000.00'
+ }
+ );
+ $acc->{credits} = $request->format_amount(
+ {
+ amount=> $acc->{credits},
+ format=>'1,000.00'
+ }
+ );
+ $acc->{starting_balance} = $request->format_amount({amount=> $acc->{starting_balance}, format=>'1,000.00' });
+ $acc->{ending_balance} = $request->format_amount({amount=> $acc->{ending_balance}, format=>'1,000.00' });
+
+ push @rep_acc, $acc;
+ }
+
+ if (scalar(@rep_acc) == 0) {
+ # We need to generate output based on the accounts that we know are present.
+ }
+
+ return ..hidden.., $total_debits, $total_credits, $report;
+}
+
+1;
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/sql/modules/EndOfYear.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/EndOfYear.sql (rev 0)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/EndOfYear.sql 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,177 @@
+CREATE OR REPLACE FUNCTION eoy_create_checkpoint(in_end_date date)
+RETURNS int AS
+$$
+DECLARE ret_val int;
+ approval_check int;
+ cp_date date;
+BEGIN
+ IF in_end_date > now()::date THEN
+ RAISE EXCEPTION 'Invalid date: Must be earlier than present';
+ END IF;
+
+ SELECT count(*) into approval_check
+ FROM acc_trans ac
+ JOIN (
+ select id, approved, transdate FROM ar UNION
+ SELECT id, approved, transdate FROM gl UNION
+ SELECT id, approved, transdate FROM ap
+ ) gl ON (gl.id = ac.trans_id)
+ WHERE (ac.approved IS NOT TRUE AND ac.transdate <= in_end_date)
+ OR (gl.approved IS NOT TRUE AND gl.transdate <= in_end_date);
+
+ if approval_check > 0 THEN
+ RAISE EXCEPTION 'Unapproved transactions in closed period';
+ END IF;
+
+ SELECT max(end_date) INTO cp_date FROM account_checkpoint WHERE
+ end_date < in_end_date;
+
+ INSERT INTO
+ account_checkpoint (end_date, account_id, amount, debits, credits)
+ SELECT in_end_date, COALESCE(a.chart_id, cp.account_id),
+ COALESCE(SUM (a.amount),0) + coalesce(MAX (cp.amount), 0),
+ COALESCE(SUM (CASE WHEN (a.amount < 0) THEN a.amount ELSE 0 END), 0) +
+ COALESCE( MIN (cp.debits), 0),
+ COALESCE(SUM (CASE WHEN (a.amount > 0) THEN a.amount ELSE 0 END), 0) +
+ COALESCE( MAX (cp.credits), 0)
+ FROM
+ (SELECT * FROM acc_trans WHERE transdate <= in_end_date AND
+ transdate > COALESCE(cp_date, '1900-01-01')) a
+ FULL OUTER JOIN (
+ select account_id, end_date, amount, debits, credits
+ from account_checkpoint
+ WHERE end_date = cp_date
+ ) cp on (a.chart_id = cp.account_id)
+ group by COALESCE(a.chart_id, cp.account_id);
+
+ SELECT count(*) INTO ret_val FROM account_checkpoint
+ where end_date = in_end_date;
+
+ return ret_val;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eoy_zero_accounts
+(in_end_date date, in_reference text, in_description text,
+in_retention_acc_id int)
+RETURNS int AS
+$$
+DECLARE ret_val int;
+BEGIN
+ INSERT INTO gl (transdate, reference, description, approved)
+ VALUES (in_end_date, in_reference, in_description, true);
+
+ INSERT INTO yearend (trans_id, transdate) values (currval('id'), in_end_date);
+ INSERT INTO acc_trans (transdate, chart_id, trans_id, amount)
+ SELECT in_end_date, a.chart_id, currval('id'),
+ (sum(a.amount) + coalesce(max(cp.amount), 0)) * -1
+ FROM acc_trans a
+ LEFT JOIN (
+ select account_id, end_date, amount from account_checkpoint
+ WHERE end_date = (select max(end_date) from account_checkpoint
+ where end_date < in_end_date)
+ ) cp on (a.chart_id = cp.account_id)
+ JOIN account acc ON (acc.id = a.chart_id)
+ WHERE a.transdate <= in_end_date
+ AND a.transdate > coalesce(cp.end_date, a.transdate - 1)
+ AND acc.category IN ('I', 'E')
+ GROUP BY a.chart_id;
+
+ INSERT INTO acc_trans (transdate, trans_id, chart_id, amount)
+ SELECT in_end_date, currval('id'), in_retention_acc_id,
+ coalesce(sum(amount) * -1, 0)
+ FROM acc_trans WHERE trans_id = currval('id');
+
+
+ SELECT count(*) INTO ret_val from acc_trans
+ where trans_id = currval('id');
+
+ RETURN ret_val;
+end;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eoy_close_books
+(in_end_date date, in_reference text, in_description text,
+in_retention_acc_id int)
+RETURNS bool AS
+$$
+BEGIN
+ IF eoy_zero_accounts(in_end_date, in_reference, in_description, in_retention_acc_id) > 0 THEN
+ PERFORM eoy_create_checkpoint(in_end_date);
+ RETURN TRUE;
+ ELSE
+ RETURN FALSE;
+ END IF;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION eoy_reopen_books(in_end_date date)
+RETURNS bool AS
+$$
+BEGIN
+ PERFORM count(*) FROM account_checkpoint WHERE end_date = in_end_date;
+
+ IF NOT FOUND THEN
+ RETURN FALSE;
+ END IF;
+
+ DELETE FROM account_checkpoint WHERE end_date = in_end_date;
+
+ PERFORM count(*) FROM yearend
+ WHERE transdate = in_end_date and reversed is not true;
+
+ IF FOUND THEN
+ INSERT INTO gl (reference, description, approved)
+ SELECT 'Reversing ' || reference, 'Reversing ' || description,
+ true
+ FROM gl WHERE id = (select trans_id from yearend
+ where transdate = in_end_date and reversed is not true);
+
+ INSERT INTO acc_trans (chart_id, amount, transdate, trans_id,
+ approved)
+ SELECT chart_id, amount * -1, currval('id'), true
+ FROM acc_trans where trans_id = (select trans_id from yearend
+ where transdate = in_end_date and reversed is not true);
+
+ UPDATE yearend SET reversed = true where transdate = in_end_date
+ and reversed is not true;
+ END IF;
+
+ DELETE FROM account_checkpoint WHERE end_date = in_end_date;
+ RETURN TRUE;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION account__obtain_balance
+(in_transdate date, in_account_id int)
+RETURNS numeric AS
+$$
+DECLARE balance numeric;
+BEGIN
+ SELECT coalesce(sum(ac.amount) + cp.amount, sum(ac.amount))
+ INTO balance
+ FROM acc_trans ac
+ JOIN (select id, approved from ar union
+ select id, approved from ap union
+ select id, approved from gl) a ON (a.id = ac.trans_id)
+ LEFT JOIN (select account_id, end_date, amount from account_checkpoint
+ WHERE account_id = in_account_id AND end_date < in_transdate
+ ORDER BY end_date desc limit 1
+ ) cp ON (cp.account_id = ac.chart_id)
+ WHERE ac.chart_id = in_account_id
+ AND ac.transdate > coalesce(cp.end_date, ac.transdate - '1 day'::interval)
+ and ac.approved and a.approved
+ and ac.transdate <= in_transdate
+ GROUP BY cp.amount, ac.chart_id;
+
+ RETURN balance;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION eoy_earnings_accounts() RETURNS setof account AS
+$$
+ SELECT *
+ FROM account
+ WHERE category = 'Q'
+ ORDER BY accno;
+$$ language sql;
Modified: addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql 2010-09-03 17:01:39 UTC (rev 3058)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql 2010-09-10 20:33:58 UTC (rev 3059)
@@ -1,78 +1,476 @@
-create type tb_row AS (
- account_id int,
- account_number text,
- account_desc text,
- starting_balance numeric,
- debits numeric,
- credits numeric,
- ending_balance numeric
-);
+CREATE OR REPLACE FUNCTION unnest(anyarray)
+ RETURNS SETOF anyelement AS
+$BODY$
+SELECT $1[i] FROM
+ generate_series(array_lower($1,1),
+ array_upper($1,1)) i;
+$BODY$
+ LANGUAGE 'sql' IMMUTABLE;
+
+
+ create type tb_row AS (
+ account_id int,
+ account_number text,
+ account_desc text,
+ starting_balance numeric,
+ debits numeric,
+ credits numeric,
+ ending_balance numeric
+ );
+
CREATE OR REPLACE FUNCTION trial_balance__generate
-(in_date_from date, in_date_to date, in_heading int, in_accounts int[],
- in_ignore_yearend text)
+(i_date_from DATE, i_date_to DATE, in_heading INT, in_accounts INT[],
+ in_ignore_yearend TEXT, in_department INT)
returns setof tb_row AS
$$
DECLARE
- out_row tb_row;
- t_checkpoint_date date;
- include_trans int;
+ out_row tb_row;
+ adj_row RECORD;
+ cpa_date DATE;
+ cpb_date DATE;
+ date_to DATE;
+ date_from DATE;
+ yearend_trans INT;
+ yearends INT[];
+ include_trans INT;
BEGIN
- --in_ignore_yearend can be 'none', 'all', and 'last'
- if (in_ignore_yearend = 'last') then
- select trans_id into include_trans FROM yearend
- WHERE transdate < in_date_to and transdate > in_date_from
- order by transdate desc limit 1;
- end if;
- -- setup checkpoint stuff since can't do this in the from clause so well
- SELECT end_date INTO t_checkpoint_date
- FROM account_checkpoint
- WHERE end_date <= in_date_from
- ORDER BY end_date desc LIMIT 1;
+ -- don't bother with checking from/to dates for NULL below.
+ IF i_date_from IS NULL THEN
+ SELECT INTO date_from '1900-01-01';
+ ELSE
+ date_from = i_date_from;
+ END IF;
+ IF i_date_to IS NULL THEN
+ SELECT INTO date_to current_date + 1;
+ ELSE
+ date_to = i_date_to;
+ END IF;
+
+ -- assemble a list of transactions that shouldn't be included in a trial
+ -- balance
+ IF in_ignore_yearend = 'last' THEN
+ SELECT trans_id INTO include_trans
+ FROM yearend WHERE transdate <= date_to AND transdate >= date_from
+ ORDER by transdate DESC LIMIT 1;
+ yearends = yearends || include_trans;
+ END IF;
+
+ IF in_ignore_yearend = 'all' THEN
+ FOR yearend_trans IN SELECT trans_id FROM yearend
+ WHERE transdate <= date_to
+ LOOP
+ yearends = yearends || yearend_trans;
+ END LOOP;
+ END IF;
+
+ -- setup checkpoint stuff since can't do this in the from clause so well
+ IF i_date_from IS NOT NULL THEN
+ SELECT end_date INTO cpa_date
+ FROM account_checkpoint
+ WHERE end_date <= date_from
+ ORDER BY end_date DESC LIMIT 1;
+ ELSE
+ cpa_date = NULL;
+ END IF;
+
+ IF i_date_to IS NOT NULL THEN
+ SELECT end_date INTO cpb_date
+ FROM account_checkpoint
+ WHERE end_date > date_from AND end_date <= date_to
+ ORDER BY end_date DESC LIMIT 1;
+ ELSE
+ cpb_date = NULL;
+ END IF;
+
FOR out_row IN
-- main trial balance query
SELECT a.id, a.accno, a.description,
- (SUM(CASE WHEN ac.transdate < in_date_from
- THEN ac.amount
- ELSE 0 END) + coalesce(cp.amount, 0))
- * case when a.contra then -1 else 1 end
- * case when a.category IN ('A', 'E') THEN -1
- ELSE 1 END,
- SUM(CASE WHEN (in_date_from is null
- or in_date_from <= ac.transdate) AND
- ac.amount < 0
- THEN ac.amount ELSE 0 END),
- SUM(CASE WHEN (in_date_from is null
- or in_date_from <= ac.transdate) AND
- ac.amount > 0
- THEN ac.amount ELSE 0 END),
- (SUM(ac.amount) + coalesce(cp.amount, 0))
- * case when a.contra then -1 else 1 end
- * case when a.category IN ('A', 'E') THEN -1 ELSE 1 END
- FROM account a
- JOIN acc_trans ac ON (a.id = ac.chart_id)
- JOIN (select id, approved from ar
- UNION
- select id, approved from gl
- UNION
- select id, approved from ap) gl ON (ac.trans_id = gl.id)
- LEFT JOIN yearend y ON (gl.id = y.trans_id)
- LEFT JOIN (SELECT account_id, amount, end_date
- FROM account_checkpoint
- WHERE end_date = t_checkpoint_date) cp
- ON (cp.account_id = a.id)
- WHERE ac.approved is true and gl.approved is true and
- (in_date_to is null or ac.transdate <= in_date_to) and
- (cp.end_date is null or ac.transdate > cp.end_date) and
- (in_heading is null or a.heading = in_heading)
- (in_accounts is null or a.id = any(in_accounts) and
- (in_ignore_yearend = 'none' OR y.id is NULL
- or y.id = include_trans)
+ (COALESCE(SUM(CASE WHEN ac.transdate < date_from
+ THEN COALESCE(ac.amount, 0) ELSE 0 END), 0) +
+ COALESCE(cpa.amount, 0))
+ * CASE WHEN a.contra THEN -1 ELSE 1 END
+ * CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END,
+
+ -- debits value is end_checkpoint.debit - start_checkpoint.debit
+ -- + all transactions after end_checkpoint - all transactions
+ -- between the start checkpoint and the start_date.
+ -- Most conditions are for the case of either start or the
+ -- checkpoint missing.
+ COALESCE(cpb.debits, 0) +
+ COALESCE(SUM(CASE WHEN COALESCE(ac.amount, 1) > 0 THEN 0
+ ELSE
+ CASE WHEN (cpb.end_date IS NOT NULL
+ AND ac.transdate > cpb.end_date) OR
+ (cpb.end_date IS NULL
+ AND ac.transdate >= date_from)
+ THEN ac.amount
+ WHEN (cpb.end_date IS NOT NULL AND
+ ac.transdate < date_from)
+ THEN -ac.amount
+ ELSE 0
+ END
+ END), 0) -
+ -- we should only substract starting segment if we have added
+ -- the value of ending one first.
+ CASE WHEN cpb.end_date IS NOT NULL THEN
+ COALESCE(cpa.debits, 0)
+ ELSE 0 END,
+
+ -- see comment in debits for explanation of the case below.
+ COALESCE(cpb.credits, 0) +
+ COALESCE(SUM (CASE WHEN COALESCE(ac.amount, -1) < 0 THEN 0
+ ELSE
+ CASE WHEN (cpb.end_date IS NOT NULL
+ AND ac.transdate > cpb.end_date) OR
+ (cpb.end_date IS NULL
+ AND ac.transdate >= date_from)
+ THEN ac.amount
+ WHEN (cpb.end_date IS NOT NULL AND
+ ac.transdate < date_from)
+ THEN -ac.amount
+ ELSE 0
+ END
+ END), 0) -
+ CASE WHEN cpb.end_date IS NOT NULL THEN
+ COALESCE(cpa.credits, 0)
+ ELSE 0 END,
+
+ (COALESCE(SUM(CASE WHEN (cpb.end_date IS NULL OR
+ ac.transdate > cpb.end_date) AND
+ (ac.transdate >= date_from)
+ THEN
+ COALESCE(ac.amount, 0) ELSE 0 END), 0) +
+ COALESCE(cpb.amount, 0))
+ * CASE WHEN a.contra
+ THEN -1 ELSE 1 END
+ * CASE WHEN a.category IN ('A', 'E')
+ THEN -1 ELSE 1 END
+
+ -- acc_trans has the most rows among all tables. We try to eliminate
+ -- most before the join.
+ FROM account a
+ LEFT JOIN
+ (SELECT ac.chart_id, ac.trans_id, ac.amount, ac.transdate
+ FROM acc_trans ac
+ WHERE ac.approved IS TRUE AND transdate <= date_to AND
+ ((cpa_date IS NULL AND cpb_date IS NOT NULL AND
+ (ac.transdate < date_from OR ac.transdate > cpb_date)) OR
+ (cpa_date IS NOT NULL AND cpb_date IS NOT NULL AND
+ ((ac.transdate > cpa_date AND ac.transdate < date_from)
+ OR ac.transdate > cpb_date)) OR
+ (cpa_date IS NULL AND cpb_date IS NULL) OR
+ (cpa_date IS NOT NULL AND cpb_date IS NULL AND
+ ac.transdate > cpa_date))) ac ON (ac.chart_id = a.id)
+ LEFT JOIN
+ (SELECT id, approved FROM ar
+ UNION
+ SELECT id, approved FROM gl
+ UNION
+ SELECT id, approved FROM ap) gl
+ ON (ac.trans_id = gl.id AND gl.approved IS TRUE)
+ LEFT JOIN (SELECT account_id, amount, end_date, debits, credits
+ FROM account_checkpoint
+ WHERE end_date = cpa_date) cpa
+ ON (cpa.account_id = a.id)
+ LEFT JOIN (SELECT account_id, amount, end_date, debits, credits
+ FROM account_checkpoint
+ WHERE end_date = cpb_date) cpb
+ ON (cpb.account_id = a.id)
+ WHERE ((in_heading IS NOT NULL AND a.heading = in_heading) OR
+ (in_accounts IS NOT NULL AND a.id = any(in_accounts)) OR
+ (in_accounts IS NULL AND in_heading IS NULL))
GROUP BY a.id, a.description, a.accno, a.contra, a.category,
- cp.amount
+ cpa.end_date, cpb.end_date,
+ cpa.amount, cpb.amount, cpa.debits, cpb.debits,
+ cpa.credits, cpb.credits
LOOP
+ -- if ignore_yearends is none - we are done. Otherwise we have to
+ -- substract the value of yearend transactions for each account.
+ -- We can do it in the main query, cause some of these transactions
+ -- are never visited there, since their amounts are included in the
+ -- checkpoints.
+ IF in_ignore_yearend != 'none' THEN
+ SELECT
+ SUM(CASE WHEN ac.transdate < date_from
+ THEN ac.amount ELSE 0 END)
+ * CASE WHEN a.contra THEN -1 ELSE 1 END
+ * CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
+ AS start,
+ SUM(CASE WHEN ac.transdate >= date_from AND ac.amount < 0
+ THEN ac.amount ELSE 0 END) AS debits,
+ SUM(CASE WHEN ac.transdate >= date_from AND ac.amount > 0
+ THEN ac.amount ELSE 0 END) AS credits,
+ SUM(CASE WHEN ac.transdate >= date_from
+ THEN ac.amount ELSE 0 END)
+ * CASE WHEN a.contra THEN -1 ELSE 1 END
+ * CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
+ AS end
+
+ INTO adj_row FROM acc_trans ac JOIN account a
+ ON (a.id = ac.chart_id) WHERE
+ ac.chart_id = out_row.account_id AND
+ ac.trans_id = any(yearends) AND ac.transdate <= date_to
+ GROUP BY a.contra,a.category;
+
+ IF (ROW(adj_row.start, adj_row.debits, adj_row.credits,
+ adj_row.end) != ROW(0,0,0,0)) THEN
+ out_row.starting_balance =
+ out_row.starting_balance - adj_row.start;
+ out_row.debits =
+ out_row.debits - adj_row.debits;
+ out_row.credits =
+ out_row.credits - adj_row.credits;
+ out_row.ending_balance =
+ out_row.ending_balance - adj_row.end;
+ END IF;
+ END IF;
RETURN NEXT out_row;
- end loop;
-END;
+ END LOOP;
+ RETURN;
+END
$$ language plpgsql;
+
+
+CREATE TYPE trial_balance_all_acc_data AS (
+ account_id int,
+ account_number text,
+ account_desc text,
+ starting_balance numeric,
+ debits numeric,
+ credits numeric,
+ ending_balance numeric,
+ gifi_accno text
+);
+
+
+CREATE OR REPLACE FUNCTION trial_balance__account_data (
+ i_date_from DATE,
+ i_date_to DATE,
+ in_heading INT,
+ in_accounts INT[],
+ in_ignore_yearend TEXT,
+ in_department INT
+) RETURNS SETOF trial_balance_all_acc_data AS $body$
+
+ SELECT g.*,
+ a.gifi_accno
+ FROM trial_balance__generate($1, $2, $3, $4, $5, $6) g
+LEFT OUTER JOIN account a ON a.accno = g.account_number OR a.gifi_accno = g.account_number;
+$body$ LANGUAGE SQL;
+
+CREATE TABLE trial_balance__yearend_types (
+ type text primary key
+);
+
+INSERT INTO trial_balance__yearend_types (type) VALUES ('none');
+INSERT INTO trial_balance__yearend_types (type) VALUES ('all');
+INSERT INTO trial_balance__yearend_types (type) VALUES ('last');
+
+
+CREATE TABLE trial_balance (
+ id serial primary key,
+ date_from date not null,
+ date_to date not null,
+ description text,
+ yearend text not null references trial_balance__yearend_types(type)
+);
+
+CREATE TABLE trial_balance__account_to_report (
+ report_id int not null references trial_balance(id),
+ account_id int not null references account(id)
+);
+
+CREATE TABLE trial_balance__heading_to_report (
+ report_id int not null references trial_balance(id),
+ heading_id int not null references account_heading(id)
+);
+
+CREATE TYPE trial_balance__entry AS (
+ id int,
+ date_from date,
+ date_to date,
+ description text,
+ yearend text,
+ heading_id int,
+ accounts int[]
+);
+
+
+CREATE OR REPLACE FUNCTION trial_balance__get (
+ in_report_id int
+) RETURNS trial_balance__entry AS $body$
+ SELECT tb.id,
+ tb.date_from,
+ tb.date_to,
+ tb.description,
+ tbh.heading_id,
+ (ARRAY(SELECT account_id FROM trial_balance__account_to_report WHERE report_id = tb.id)) as accounts
+ FROM trial_balance tb
+ LEFT OUTER JOIN trial_balance__heading_to_report tbh ON tbh.report_id = tb.id
+ WHERE tb.id = $1;
+$body$ LANGUAGE SQL;
+
+
+CREATE OR REPLACE FUNCTION trial_balance__save (
+ in_id int,
+ in_date_from date,
+ in_date_to date,
+ in_desc text,
+ in_yearend text,
+ in_heading int,
+ in_accounts int[]
+) RETURNS int AS $body$
+
+ DECLARE
+ old_heading_id int;
+ new_report_id int;
+ iter int;
+ acc_id int;
+ BEGIN
+ PERFORM id
+ FROM trial_balance
+ WHERE id = in_id;
+
+ IF in_id IS NOT NULL AND FOUND THEN
+ -- This is an edit.
+ UPDATE trial_balance
+ SET date_from = in_date_from,
+ date_to = in_date_to,
+ description = in_desc,
+ yearend = in_yearend
+ WHERE id = in_id;
+
+ SELECT heading_id
+ INTO old_heading_id
+ FROM trial_balance__heading_to_report
+ WHERE heading_id = in_heading
+ AND report_id = in_id;
+
+ IF FOUND AND in_heading IS NULL THEN
+ DELETE FROM trial_balance__heading_to_report
+ WHERE report_id = in_id
+ AND heading_id = old_heading_id;
+ -- Expect to remove the heading ID.
+ ELSIF FOUND AND in_heading <> old_heading_id THEN
+
+ UPDATE trial_balance__heading_to_report
+ SET heading_id = in_heading
+ WHERE heading_id = old_heading_id
+ AND report_id = in_id;
+
+ -- Else, do nothing.
+ END IF;
+
+ IF in_accounts IS NOT NULL THEN
+ -- First, we add the new ones.
+
+ DELETE FROM trial_balance__account_to_report WHERE report_id = in_id;
+ FOR
+ iter IN array_lower(in_accounts, 1) .. array_upper(in_accounts, 1)
+ LOOP
+ INSERT INTO trial_balance__account_to_report (report_id, account_id)
+ VALUES (in_id, in_accounts[iter]);
+ END LOOP;
+
+ ELSE
+ -- It's null.
+ -- We can drop all the direct account entries.
+ DELETE
+ FROM trial_balance__account_to_report
+ WHERE report_id = in_id;
+ END IF;
+ return in_id;
+ ELSE
+ -- We don't have a trial balance setup.
+ -- We can just create a new one whole cloth. Woo!
+ new_report_id := nextval('trial_balance_id_seq');
+ INSERT INTO trial_balance (id, date_from, date_to, description, yearend)
+ VALUES (new_report_id, in_date_from, in_date_to, in_desc, in_yearend);
+
+ IF in_heading IS NOT NULL THEN
+ INSERT INTO trial_balance__heading_to_report (report_id, heading_id)
+ VALUES (new_report_id, in_heading);
+ END IF;
+
+ IF in_accounts IS NOT NULL THEN
+ -- Iterate over the length of the array, and insert each one into the
+ -- account-to-report table.
+ -- Because this targets 8.2, we can't use the 8.4 function unnest();
+ FOR
+ iter IN array_lower(in_accounts, 1) .. array_upper(in_accounts, 1)
+ LOOP
+ INSERT INTO trial_balance__account_to_report (report_id, account_id)
+ VALUES (new_report_id, in_accounts[iter]);
+ END LOOP;
+ END IF;
+ return new_report_id;
+ END IF;
+ END;
+$body$ LANGUAGE PLPGSQL;
+
+--
+
+CREATE OR REPLACE FUNCTION trial_balance__accounts (
+ in_report_id INT
+) RETURNS SETOF account AS $body$
+
+ SELECT a.*
+ FROM account a
+ JOIN trial_balance__account_to_report tbr ON a.id = tbr.account_id
+ WHERE tbr.report_id = $1
+
+ UNION
+
+ SELECT a.*
+ FROM account a
+ JOIN trial_balance__heading_to_report tbhr ON a.heading = tbhr.heading_id
+ WHERE tbhr.report_id = $1
+
+ ORDER BY accno DESC;
+$body$ LANGUAGE SQL;
+
+-- Just lists all valid report_ids
+
+CREATE OR REPLACE FUNCTION trial_balance__list (
+) RETURNS SETOF trial_balance AS $body$
+ SELECT * FROM trial_balance ORDER BY id ASC;
+$body$ LANGUAGE SQL STABLE;
+
+CREATE TYPE trial_balance__heading AS (
+ id int,
+ accno text,
+ description text,
+ accounts int[]
+);
+
+CREATE OR REPLACE FUNCTION trial_balance__list_headings (
+) RETURNS SETOF trial_balance__heading AS $body$
+ SELECT id, accno, description, ARRAY( SELECT id FROM account where heading = ah.id) FROM account_heading ah;
+$body$ LANGUAGE SQL IMMUTABLE;
+
+
+CREATE OR REPLACE FUNCTION trial_balance__heading_accounts (
+ in_accounts int[]
+) RETURNS SETOF account AS $body$
+ SELECT * FROM account WHERE id in (SELECT unnest($1));
+$body$ LANGUAGE SQL IMMUTABLE;
+
+
+CREATE OR REPLACE FUNCTION trial_balance__delete (
+ in_report_id int
+) RETURNS boolean AS $body$
+
+ BEGIN
+ PERFORM id FROM trial_balance WHERE id = in_report_id;
+
+ IF FOUND THEN
+ DELETE FROM trial_balance__heading_to_report WHERE report_id = in_report_id;
+ DELETE FROM trial_balance__account_to_report WHERE report_id = in_report_id;
+ DELETE FROM trial_balance WHERE id = in_report_id;
+ RETURN TRUE;
+ END IF;
+ RETURN FALSE;
+ END;
+$body$ LANGUAGE PLPGSQL;
\ No newline at end of file
Added: addons/1.3/enhanced_tb/trunk/trial_balance.pl
===================================================================
--- addons/1.3/enhanced_tb/trunk/trial_balance.pl (rev 0)
+++ addons/1.3/enhanced_tb/trunk/trial_balance.pl 2010-09-10 20:33:58 UTC (rev 3059)
@@ -0,0 +1,3 @@
+#!/usr/bin/perl
+
+require "lsmb-request.pl";
Modified: addons/1.3/extended_arap/trunk/UI/aa.html
===================================================================
--- addons/1.3/extended_arap/trunk/UI/aa.html 2010-09-03 17:01:39 UTC (rev 3058)
+++ addons/1.3/extended_arap/trunk/UI/aa.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -311,7 +311,7 @@
name = ARAP _ "_amount" _ row.id,
initial_value = ${"accno_$INDEX"},
ajax_target = 'journal.pl',
- params = { link_desc = '"' _ arap_amount_name _ '"' }
+ params = { 'link_desc' = arap_amount_name }
} ?>
</td>
<?lsmb #Not sure if numtextrows will show up as expected.?>
Modified: trunk/UI/lib/elements.html
===================================================================
--- trunk/UI/lib/elements.html 2010-09-03 17:01:39 UTC (rev 3058)
+++ trunk/UI/lib/elements.html 2010-09-10 20:33:58 UTC (rev 3059)
@@ -138,13 +138,22 @@
'<?lsmb element_data.ajax_target ?>',
{"afterUpdateElement":post_ajax_setter,
<?lsmb IF attribute_data.params ?>
- 'params' : {<?lsmb FOR k
+ 'parameters' : <?lsmb SET params=""; FOR k
IN attribute_data.params.keys();
+ IF params == "";
+ params = "$k=" _ attribute_data.params.$k;
+ ELSE;
+ params = params _ "&$k=" _ attribute_data.params.$k;
+ END;
+
+ END;
+ #"<?lsmb k >" : <?lsmb
+ # attribute_data.params.$k
+ #->
?>
- <?lsmb k ?> : <?lsmb
- attribute_data.params.$k
- -?>
- <?lsmb END -?> } <?lsmb END -?>
+ <?lsmb '"' _ params _ '"'?>
+ <?lsmb END -?>
+
})
//--></script>
<?lsmb END ?>
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.