[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4742] trunk
- Subject: SF.net SVN: ledger-smb:[4742] trunk
- From: ..hidden..
- Date: Mon, 21 May 2012 08:53:59 +0000
Revision: 4742
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4742&view=rev
Author: einhverfr
Date: 2012-05-21 08:53:59 +0000 (Mon, 21 May 2012)
Log Message:
-----------
Aging reports (but not statements yet) moved to 1.4 reporting framework. Statements forthcoming. A few Colombia report files added.
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Report/Aging.pm
trunk/LedgerSMB/DBObject/Report.pm
trunk/LedgerSMB/RP.pm
trunk/bin/rp.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/Budgetting.sql
trunk/sql/modules/LOADORDER
trunk/sql/modules/Report.sql
Added Paths:
-----------
trunk/LedgerSMB/Scripts/lreports_co.pm
trunk/LedgerSMB/Scripts/report_aging.pm
trunk/LedgerSMB/Scripts/reports.pm
trunk/UI/Reports/aging_report.html
trunk/UI/Reports/display_report.tex
trunk/UI/Reports/filters/aging.html
trunk/UI/lib/report_base.html
trunk/lreports_co.pl
trunk/report_aging.pl
trunk/reports.pl
Removed Paths:
-------------
trunk/UI/rp-search-aging.html
Modified: trunk/LedgerSMB/DBObject/Report/Aging.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/Aging.pm 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/LedgerSMB/DBObject/Report/Aging.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -26,7 +26,7 @@
=cut
-package LedgerSMB::DBObject::Report::GL;
+package LedgerSMB::DBObject::Report::Aging;
use Moose;
extends 'LedgerSMB::DBObject::Report';
@@ -85,11 +85,11 @@
my $credit_label;
if ($self->entity_class == 1) {
$credit_label = $LedgerSMB::App_State::Locale->text('Vendor');
- } elsif $self->entity_class == 2){
+ } elsif ($self->entity_class == 2){
$credit_label = $LedgerSMB::App_State::Locale->text('Customer');
}
push @COLUMNS,
- {col_id => 'select'
+ {col_id => 'select',
type => 'checkbox'},
{col_id => 'credit_acct',
@@ -103,7 +103,7 @@
pwidth => '0', };
if ($self->report_type eq 'detail'){
- push @columns,
+ push @COLUMNS,
{col_id => 'invnumber',
name => $locale->text('Invoice'),
type => 'href',
@@ -124,6 +124,7 @@
name => $locale->text('Due Date'),
type => 'text',
pwidth => '2', };
+ }
push @COLUMNS,
{col_id => 'c0',
@@ -149,7 +150,7 @@
{col_id => 'total',
name => $locale->text('Total'),
type => 'text',
- pwidth => '1', },
+ pwidth => '1', };
return ..hidden..;
}
@@ -186,7 +187,7 @@
if (!$self->format or (uc($self->format) eq 'HTML')
or (uc($self->format) eq 'PDF'))
{
- return 'aging_report';
+ return 'Reports/aging_report';
}
else {
return undef;
@@ -232,6 +233,14 @@
has 'date_ref' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+=item entity_class
+
+1 for vendor, 2 for customer
+
+=cut
+
+has 'entity_class' => (is => 'rw', isa => 'Maybe[Int]');
+
=head1 METHODS
=over
Modified: trunk/LedgerSMB/DBObject/Report.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report.pm 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/LedgerSMB/DBObject/Report.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -126,7 +126,8 @@
# This is a hook for other modules to use to override the default
# template --CT
- eval {$template = $self->template} || $template = 'Reports/display_report';
+ eval {$template = $self->template};
+ $template ||= 'Reports/display_report';
if (!defined $self->format){
$self->format('html');
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/LedgerSMB/RP.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -1721,185 +1721,6 @@
}
-sub aging {
- my ( $self, $myconfig, $form ) = @_;
-
- my $ref;
- my $department_id;
- my $null;
-
- my $dbh = $form->{dbh};
- my $invoice = ( $form->{arap} eq 'ar' ) ? 'is' : 'ir';
-
- my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
- ( $form->{currencies} ) = $dbh->selectrow_array($query);
-
- ( $null, $form->{todate} ) = $form->from_to( $form->{year}, $form->{month} )
- if $form->{year} && $form->{month};
-
- if ( !$form->{todate} ) {
- $query = qq|SELECT current_date|;
- ( $form->{todate} ) = $dbh->selectrow_array($query);
- }
-
- my $transdate = ( $form->{overdue} ) ? "duedate" : "transdate";
- if ( $form->{department} ) {
- ( $null, $department_id ) = split /--/, $form->{department};
- }
-
- my $buysell = ( $form->{arap} eq 'ar' ) ? 'buy' : 'sell';
-
- my $todate = $dbh->quote( $form->{todate} );
- my %interval = (
- 'c0' => "(date $todate - interval '0 days')",
- 'c30' => "(date $todate - interval '30 days')",
- 'c60' => "(date $todate - interval '60 days')",
- 'c90' => "(date $todate - interval '90 days')"
- );
-
- # for each company that has some stuff outstanding
- $form->{currencies} ||= ":";
-
- $where = qq|true|;
-
- if ($department_id) {
- $where .= qq| AND a.department_id = | . $dbh->quote($department_id);
- }
-
- if ($form->{meta_number}){
- $where .= qq| AND c.meta_number = | . $dbh->quote($form->{meta_number});
- }
-
- if ($form->{name}){
- $where .= qq| AND e.legal_name ilike | .
- $dbh->quote($form->like($form->{name}));
- }
-
-
- $query = "";
- my $union = "";
- my $aclass;
- if ($form->{arap} eq 'ar') {
- $aclass = 1;
- } else {
- $aclass = 2;
- $form->{arap} = 'ap';
- }
- $query .= qq|
- SELECT c.entity_id AS ctid,
- c.meta_number as $form->{ct}number, e.legal_name as name,
- '' as address1, '' as address2, '' as city,
- '' as state,
- '' as zipcode,
- '' as country, '' as contact, '' as email,
- '' as $form->{ct}phone,
- '' as $form->{ct}fax,
- '' as $form->{ct}taxnumber,
- a.invnumber, a.transdate, a.ordnumber,
- a.ponumber, a.notes, c.language_code,
- CASE WHEN
- EXTRACT(days FROM age(?, a.transdate)/30)
- = 0
- THEN (sum(p.due) * -1) ELSE 0 END
- as c0,
- CASE WHEN EXTRACT(days FROM age(?, a.transdate)/30)
- = 1
- THEN (sum(p.due) * -1) ELSE 0 END
- as c30,
- CASE WHEN EXTRACT(days FROM age(?, a.transdate)/30)
- = 2
- THEN (sum(p.due) * -1) ELSE 0 END
- as c60,
- CASE WHEN EXTRACT(days FROM age(?, a.transdate)/30)
- > 2
- THEN (sum(p.due) * -1) ELSE 0 END
- as c90,
- a.duedate, a.invoice, a.id, a.curr,
- (SELECT $buysell FROM exchangerate e
- WHERE a.curr = e.curr
- AND e.transdate = a.transdate)
- AS exchangerate
- FROM $form->{arap} a
- JOIN (SELECT acc_trans.trans_id,
- sum(CASE WHEN $aclass = 1 THEN amount
- WHEN $aclass = 2 THEN amount * -1
- END) AS due
- FROM acc_trans
- JOIN account coa ON (coa.id = acc_trans.chart_id)
- JOIN account_link al ON (al.account_id = coa.id)
- WHERE (al.description = |.
- $dbh->quote(uc($form->{arap})) . qq|)
- AND approved IS TRUE
- AND transdate <= ?
- GROUP BY acc_trans.trans_id) p ON (a.id = p.trans_id)
- JOIN entity_credit_account c
- ON (a.entity_credit_account = c.id)
- JOIN company e ON e.entity_id = c.entity_id
- WHERE $where
- GROUP BY c.entity_id, c.meta_number, e.legal_name, a.invnumber,
- a.transdate, a.ordnumber, a.duedate, a.invoice, a.id,
- a.curr, a.ponumber, a.notes, c.language_code
- HAVING sum(p.due) <> 0|;
-
- $query .= qq| ORDER BY curr, e.legal_name, ctid, $transdate, invnumber|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- $sth->execute($form->{todate}, $form->{todate}, $form->{todate},
- $form->{todate}, $form->{todate});
-
- while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- $ref->{module} =
- ( $ref->{invoice} )
- ? $invoice
- : $form->{arap};
- $ref->{module} = 'ps' if $ref->{till};
- $ref->{exchangerate} = 1
- unless $ref->{exchangerate};
- push @{ $form->{AG} }, $ref;
-
- }
- $sth->finish;
-
- # get language
- $query = qq|SELECT code, description FROM language ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ( $ref = $sth->fetchrow_hashref('NAME_lc') ) {
- $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
- push @{ $form->{all_language} }, $ref;
- }
- $sth->finish;
-
- $dbh->commit;
-}
-
-sub get_customer {
- my ( $self, $myconfig, $form ) = @_;
-
- my $dbh = $form->{dbh};
-
- my $query = qq|
- SELECT e.name, c.contact, cc.class
- FROM entity_credit_account eca
- JOIN entity USING (entity_id)
- JOIN eca_to_contact c ON (eca.credit_id = eca.id)
- JOIN contact_class cc ON (c.contact_class_id = cc.id)
- WHERE eca.id = ?
- AND cc.id BETWEEN 12 AND 17|;
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{"$form->{ct}_id"} );
- while (my $ref = $sth->fetchrow_hashref('NAME_lc')){
- $form->{ $form->{ct} } = $ref->{name}; # each 'name' row is the same
- $form->{ lc($ref->{class}) } .=
- ($form->{ lc($ref->{class}) } ? ", " : "") . $ref->{contact};
- }
-
- $dbh->commit;
-
-}
-
sub get_taxaccounts {
my ( $self, $myconfig, $form ) = @_;
Added: trunk/LedgerSMB/Scripts/lreports_co.pm
===================================================================
--- trunk/LedgerSMB/Scripts/lreports_co.pm (rev 0)
+++ trunk/LedgerSMB/Scripts/lreports_co.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -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;
Added: trunk/LedgerSMB/Scripts/report_aging.pm
===================================================================
--- trunk/LedgerSMB/Scripts/report_aging.pm (rev 0)
+++ trunk/LedgerSMB/Scripts/report_aging.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,52 @@
+=head1 NAME
+
+LedgerSMB::Scripts::report_aging - Aging Reports and Statements for LedgerSMB
+
+=head1 SYNOPSIS
+
+This module provides AR/AP aging reports and statements for LedgerSMB.
+
+=head1 METHODS
+
+=cut
+
+package LedgerSMB::Scripts::report_aging;
+our $VERSION = '1.0';
+
+use LedgerSMB;
+use LedgerSMB::Template;
+use LedgerSMB::DBObject::Business_Unit;
+use LedgerSMB::DBObject::Report::Aging;
+use strict;
+
+=pod
+
+=item run_report
+
+Runs the report and displays it
+
+=cut
+
+sub run_report{
+ my ($request) = @_;
+ delete $request->{category} if ($request->{category} = 'X');
+ $request->{business_units} = [];
+ for my $count (1 .. $request->{bc_count}){
+ push @{$request->{business_units}}, $request->{"business_unit_$count"}
+ if $request->{"business_unit_$count"};
+ }
+ LedgerSMB::DBObject::Report::Aging->prepare_criteria($request);
+ my $report = LedgerSMB::DBObject::Report::Aging->new(%$request);
+ $report->run_report;
+ $report->render($request);
+}
+
+=item THE FOLLOWING ARE TODO
+
+=item retrieve_statement
+
+=item print_statement
+
+=item email_screen
+
+=item email_statement
Added: trunk/LedgerSMB/Scripts/reports.pm
===================================================================
--- trunk/LedgerSMB/Scripts/reports.pm (rev 0)
+++ trunk/LedgerSMB/Scripts/reports.pm 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,86 @@
+=head1 NAME
+
+LedgerSMB::Scripts::reports - Common Report workflows
+
+=head1 SYNOPSIS
+
+This module holds common workflow routines for reports.
+
+=head1 METHODS
+
+=cut
+
+package LedgerSMB::Scripts::reports;
+our $VERSION = '1.0';
+
+use LedgerSMB;
+use LedgerSMB::Template;
+use LedgerSMB::DBObject::Business_Unit;
+use LedgerSMB::DBObject::Business_Unit_Class;
+use strict;
+
+=pod
+
+=item start_report
+
+This displays the filter screen for the report. It expects the following
+request properties to be set:
+
+=over
+
+=item report_name
+
+This is the name of the report
+
+=item module_name
+
+Module name for the report. This is used in retrieving business units. If not
+set, no business units are retrieved.
+
+=back
+
+Other variables that are set will be passed through to the underlying template.
+
+=cut
+
+sub start_report {
+ my ($request) = @_;
+ if ($request->{module_name}){
+ $request->{class_id} = 0 unless $request->{class_id};
+ $request->{control_code} = '' unless $request->{control_code};
+ my $buc = LedgerSMB::DBObject::Business_Unit_Class->new(%$request);
+ my $bu = LedgerSMB::DBObject::Business_Unit->new(%$request);
+ @{$request->{bu_classes}} = $buc->list(1, $request->{module_name});
+ for my $bc (@{$request->{bu_classes}}){
+ @{$request->{b_units}->{$bc->{id}}}
+ = $bu->list($bc->{id}, undef, 0, undef);
+ for my $bu (@{$request->{b_units}->{$bc->{id}}}){
+ $bu->{text} = $bu->control_code . ' -- '. $bu->description;
+ }
+ }
+ }
+ if (!$request->{report_name}){
+ die $request->{_locale}->text('No report specified');
+ }
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/Reports/filters',
+ template => $request->{report_name},
+ format => 'HTML'
+ );
+ $template->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 { require LedgerSMB::Scripts::custom::reports };
+1;
Added: trunk/UI/Reports/aging_report.html
===================================================================
--- trunk/UI/Reports/aging_report.html (rev 0)
+++ trunk/UI/Reports/aging_report.html 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,62 @@
+<?lsmb
+
+PROCESS "ui-header.html"
+ stylesheet = USER.stylesheet;
+
+PROCESS "elements.html";
+
+PROCESS "dynatable.html";
+
+LINK = 'http://' _ ENVARS.SERVER_NAME _ ENVARS.SCRIPT_NAME
+ _ '?' _ ENVARS.QUERY_STRING _ '&company=' _ form.company;
+
+?>
+<body>
+<div class="report_header"><label><?lsmb text('Report Name') ?>:</label>
+<span class="report_header"><?lsmb name ?></span>
+</div>
+<body>
+<div class="report_header"><label><?lsmb text('Company') ?>:</label>
+<span class="report_header"><?lsmb request.company ?></span>
+</div>
+<?lsmb FOREACH LINE IN hlines ?>
+<div class="report_header"><label><?lsmb LINE.text ?>:</label>
+<span class="report_header"><?lsmb request.${LINE.name} ?></span>
+</div>
+<?lsmb END ?>
+
+<form method="get" action="aging_report.pl">
+<?lsmb PROCESS dynatable tbody = {rows => rows }
+ attributes = {class = 'report' } ?>
+<?lsmb PROCESS button element_data = {
+ text = text('Print')
+ name = 'action'
+ value = 'print_statements'
+ type = 'submit'
+ class = 'submit'
+} ?>
+<?lsmb PROCESS button element_data = {
+ text = text('Email')
+ name = 'action'
+ value = 'email_statements'
+ type = 'submit'
+ class = 'submit'
+} ?>
+</form>
+<a href="<?lsmb LINK ?>">[<?lsmb text('permalink') ?>]</a>
+<?lsmb IF FORMATS.grep('PDF').size()
+?>
+<a href="<?lsmb LINK _ '&format=PDF' ?>">[<?lsmb text('PDF') ?>]</a>
+<?lsmb END;
+IF FORMATS.grep('TXT').size();
+?>
+<a href="<?lsmb LINK _ '&format=CSV' ?>">[<?lsmb text('CSV') ?>]</a>
+<?lsmb END;
+IF FORMATS.grep('XLS').size() ?>
+<a href="<?lsmb LINK _ '&format=XLS' ?>">[<?lsmb text('XLS') ?>]</a>
+<?lsmb END;
+IF FORMATS.grep('ODS').size() ?>
+<a href="<?lsmb LINK _ '&format=ODS' ?>">[<?lsmb text('ODS') ?>]</a>
+<?lsmb END; ?>
+</body>
+<?lsmb PROCESS end_html ?>
Added: trunk/UI/Reports/display_report.tex
===================================================================
--- trunk/UI/Reports/display_report.tex (rev 0)
+++ trunk/UI/Reports/display_report.tex 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,44 @@
+<?lsmb- PROCESS 'dynatable.tex';
+IF !papersize;
+ papersize = 'a4paper';
+END;
+
+COLNUMBER = 0;
+
+SKIP_TYPES = ['hidden', 'radio', 'checkbox'];
+
+FOREACH COL IN columns;
+ IF 0 == SKIP_TYPES.grep(COL.type).size();
+ COLNUMBER = COLNUMBER + 1;
+ END;
+END;
+
+FILTER latex;
+-?>
+\documentclass[<?lsmb papersize ?>]{article}
+\usepackage{longtable}
+\usepackage[margin=1cm]{geometry}
+\begin{document}
+<?lsmb
+
+FIRSTHEAD = '\\multicolumn{2}{r}{' _ text('Report Name') _ ':} & ' _
+ '\\multicolumn{' _ (COLNUMBER - 2) _ '}{l}{ ' _ name _ '}\\\\
+ \\multicolumn{2}{r}{' _ text('Company') _ ':} & ' _
+ '\\multicolumn{' _ (COLNUMBER - 2) _ '}{l}{ ' _ request.company
+ _ '} \\\\
+ ';
+
+FOREACH LINE IN hlines;
+ FIRSTHEAD = FIRSTHEAD _ '\\multicolumn{2}{r}{ ' _ LINE.text _ ':} & ';
+ FIRSTHEAD = FIRSTHEAD _ '\\multicolumn{' _ (COLNUMBER - 2) _ '}{l}{ ' _
+ request.${LINE.name}; #$
+ FIRSTHEAD = FIRSTHEAD _ '}\\\\
+ ';
+END;
+
+PROCESS dynatable
+ tbody = { rows = rows }
+ firsthead = FIRSTHEAD;
+?>
+\end{document}
+<?lsmb END ?>
Added: trunk/UI/Reports/filters/aging.html
===================================================================
--- trunk/UI/Reports/filters/aging.html (rev 0)
+++ trunk/UI/Reports/filters/aging.html 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,155 @@
+<?lsmb INCLUDE 'ui-header.html';
+ PROCESS elements.html;
+ PROCESS report_base.html ?>
+<body>
+<form method="get" action="report_aging.pl">
+<table width="100%">
+ <tr><th class="listtop"><?lsmb text('Aging Report') ?></th></tr>
+ <tr><td> </td></tr>
+ <tr>
+ <td>
+ <table>
+ <?lsmb INCLUDE business_classes ?>
+ <tr>
+ <th align="right"><?lsmb IF entity_class == 1; text('Vendor');
+ ELSIF entity_class == 2; text('Customer');
+ END ?></th>
+ <td><?lsmb PROCESS input element_data={
+ name = 'name'
+ type = 'text'
+ class = 'name'
+ value = name
+ } ?></td>
+ </tr>
+ <tr>
+ <th align="right"><?lsmb text('Account') ?></th>
+ <td><?lsmb PROCESS input element_data={
+ name = 'meta_number'
+ type = 'text'
+ class = 'control_code'
+ value = meta_number
+ } ?></td>
+ </tr>
+ <tr>
+ <th align="right"><?lsmb text('To') ?></th>
+ <td><?lsmb PROCESS input element_data={
+ class = 'date',
+ name = 'todate',
+ size = '11',
+ } ?></td>
+ </tr>
+ <tr>
+ <th></th>
+ <td>
+<?lsmb IF form.selectaccountingyear.defined ?>
+ <?lsmb PROCESS select element_data=form.selectaccountingmonth -?>
+ <?lsmb PROCESS select element_data=form.selectaccountingyear -?>
+<?lsmb END ?>
+ </td>
+ </tr>
+ <tr>
+ <th></th>
+ <td colspan="2">
+<?lsmb PROCESS input element_data={
+ type = 'radio',
+ name = 'report_type',
+ value = 'summary',
+ label = text('Summary'),
+ checked = 'checked',
+ } -?>
+<?lsmb PROCESS input element_data={
+ type = 'radio',
+ name = 'report_type',
+ value = 'detail',
+ label = text('Detail'),
+ } -?>
+ </td>
+ </tr>
+ <tr><td colspan="2">
+ <table>
+ <tr>
+ <th align="right"><?lsmb text('Include in Report') ?></th>
+ <td>
+ <table>
+ <tr>
+ <td>
+<?lsmb PROCESS input element_data={
+ name = 'overdue',
+ type = 'radio',
+ value = '0',
+ label = text('Aged'),
+ checked = 'checked',
+ } -?>
+ </td>
+ <td colspan="2">
+<?lsmb PROCESS input element_data={
+ name = 'overdue',
+ type = 'radio',
+ value = '1',
+ label = text('Overdue'),
+ } -?>
+ </td>
+ </tr>
+ <tr>
+ <td width="70">
+<?lsmb PROCESS input element_data={
+ name = 'c0',
+ type = 'checkbox',
+ value = '1',
+ label = text('Current'),
+ checked = 'checked',
+ } -?>
+ </td>
+ <td width="70">
+<?lsmb PROCESS input element_data={
+ name = 'c30',
+ type = 'checkbox',
+ value = '1',
+ label = '30',
+ checked = 'checked',
+ } -?>
+ </td>
+ <td width="70">
+<?lsmb PROCESS input element_data={
+ name = 'c60',
+ type = 'checkbox',
+ value = '1',
+ label = '60',
+ checked = 'checked',
+ } -?>
+ </td>
+ <td width="70">
+<?lsmb PROCESS input element_data={
+ name = 'c90',
+ type = 'checkbox',
+ value = '1',
+ label = '90',
+ checked = 'checked',
+ } -?>
+<?lsmb PROCESS input element_data = {
+ name = 'entity_class'
+ type = 'hidden'
+ value = entity_class
+} ?>
+ </td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ </table></td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ <tr><td><hr size="3" noshade="noshade" /></td></tr>
+</table>
+<?lsmb PROCESS button element_data = {
+ name = 'action'
+ text = text('Continue')
+ value = 'run_report'
+ type = 'submit'
+ class = 'submit'
+} ?>
+</form>
+</body>
+</html>
Added: trunk/UI/lib/report_base.html
===================================================================
--- trunk/UI/lib/report_base.html (rev 0)
+++ trunk/UI/lib/report_base.html 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,51 @@
+<?lsmb- BLOCK business_classes;
+ FOREACH BUC IN bu_classes ?>
+ <tr>
+ <th align="right"><?lsmb text(BUC.label) ?></th>
+ <?lsmb b_units.${BUC.id}.unshift({}) ?>
+ <td><?lsmb PROCESS select element_data = {
+ name = 'business_unit_' _ loop.count
+ options = b_units.${BUC.id}
+ text_attr = 'text'
+ value_attr = 'id'
+ class = 'business_unit'
+ } ?></td>
+ </tr>
+ <?lsmb END; # FOREACH BUC
+END # BLOCK -?>
+
+<?lsmb- BLOCK gifi_or_standard ?>
+ <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 # BLOCK -?>
+
+<?lsmb- BLOCK ignore_yearend ?>
+ <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 # BLOCK -?>
Deleted: trunk/UI/rp-search-aging.html
===================================================================
--- trunk/UI/rp-search-aging.html 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/UI/rp-search-aging.html 2012-05-21 08:53:59 UTC (rev 4742)
@@ -1,122 +0,0 @@
- <tr>
- <th align="right"><?lsmb text(form.uvc) ?></th>
- <td><?lsmb PROCESS input element_data={
- name = 'name'
- type = 'text'
- class = 'name'
- value = name
- } ?></td>
- </tr>
- <tr>
- <th align="right"><?lsmb text('Account') ?></th>
- <td><?lsmb PROCESS input element_data={
- name = 'meta_number'
- type = 'text'
- class = 'control_code'
- value = meta_number
- } ?></td>
- </tr>
- <tr>
- <th align="right"><?lsmb text('To') ?></th>
- <td><?lsmb PROCESS input element_data={
- class = 'date',
- name = 'todate',
- size = '11',
- title = user.dateformat,
- } ?></td>
- </tr>
- <tr>
- <th></th>
- <td>
-<?lsmb IF form.selectaccountingyear.defined ?>
- <?lsmb PROCESS select element_data=form.selectaccountingmonth -?>
- <?lsmb PROCESS select element_data=form.selectaccountingyear -?>
-<?lsmb END ?>
- </td>
- </tr>
- <tr>
- <th></th>
- <td>
-<?lsmb PROCESS input element_data={
- type = 'radio',
- name = 'summary',
- value = '1',
- label = text('Summary'),
- checked = 'checked',
- } -?>
-<?lsmb PROCESS input element_data={
- type = 'radio',
- name = 'summary',
- value = '0',
- label = text('Detail'),
- } -?>
- </td>
- </tr>
- <tr><td colspan="2">
- <table>
- <tr>
- <th align="right"><?lsmb text('Include in Report') ?></th>
- <td>
- <table>
- <tr>
- <td>
-<?lsmb PROCESS input element_data={
- name = 'overdue',
- type = 'radio',
- value = '0',
- label = text('Aged'),
- checked = 'checked',
- } -?>
- </td>
- <td>
-<?lsmb PROCESS input element_data={
- name = 'overdue',
- type = 'radio',
- value = '1',
- label = text('Overdue'),
- } -?>
- </td>
- </tr>
- <tr>
- <td width="70">
-<?lsmb PROCESS input element_data={
- name = 'c0',
- type = 'checkbox',
- value = '1',
- label = text('Current'),
- checked = 'checked',
- } -?>
- </td>
- <td width="70">
-<?lsmb PROCESS input element_data={
- name = 'c30',
- type = 'checkbox',
- value = '1',
- label = '30',
- checked = 'checked',
- } -?>
- </td>
- <td width="70">
-<?lsmb PROCESS input element_data={
- name = 'c60',
- type = 'checkbox',
- value = '1',
- label = '60',
- checked = 'checked',
- } -?>
- </td>
- <td width="70">
-<?lsmb PROCESS input element_data={
- name = 'c90',
- type = 'checkbox',
- value = '1',
- label = '90',
- checked = 'checked',
- } -?>
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table></td>
- </tr>
Modified: trunk/bin/rp.pl
===================================================================
--- trunk/bin/rp.pl 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/bin/rp.pl 2012-05-21 08:53:59 UTC (rev 4742)
@@ -78,8 +78,6 @@
balance_sheet => { title => 'Balance Sheet' },
income_statement => { title => 'Income Statement' },
trial_balance => { title => 'Trial Balance' },
- ar_aging => { title => 'AR Aging', vc => 'customer' },
- ap_aging => { title => 'AP Aging', vc => 'vendor' },
tax_collected => { title => 'Tax collected', vc => 'customer' },
tax_paid => { title => 'Tax paid' },
nontaxable_sales => { title => 'Non-taxable Sales', vc => 'customer' },
@@ -209,10 +207,6 @@
$hiddens{sort} = 'transdate';
$hiddens{report} = $form->{report};
$subform = 'generate_tax_report';
- } elsif ( ( $form->{report} eq "ar_aging" )
- || ( $form->{report} eq "ap_aging" ) ) {
- $gifi = 0;
- $subform = 'aging';
if ( $form->{report} eq 'ar_aging' ) {
$label = $locale->text('Customer');
@@ -925,378 +919,6 @@
}
-sub aging {
-
- my %hiddens;
- my @buttons;
- my @options;
- my %column_header;
- my @column_index;
- my %row_alignment;
-
- $column_header{statement} = ' ';
- $column_header{ct} = $locale->text( ucfirst $form->{ct} );
- $column_header{language} = $locale->text('Language');
- $column_header{invnumber} = $locale->text('Invoice');
- $column_header{ordnumber} = $locale->text('Order');
- $column_header{transdate} = $locale->text('Date');
- $column_header{duedate} = $locale->text('Due Date');
- $column_header{c0} = $locale->text('Current');
- $column_header{c30} = '30';
- $column_header{c60} = '60';
- $column_header{c90} = '90';
- $column_header{total} = $locale->text('Total');
-
- @column_index = qw(statement ct);
-
- if ( @{ $form->{all_language} } && $form->{arap} eq 'ar' ) {
- push @column_index, "language";
- $form->{language_options} = [{text => ' ', value => ''}];
-
- for ( @{ $form->{all_language} } ) {
- push @{$form->{language_options}},
- {text => $_->{description}, value => $_->{code}};
- }
- }
-
- my @c = ();
- for (qw(c0 c30 c60 c90)) {
- if ( $form->{$_} ) {
- push @c, $_;
- $form->{callback} .= "&$_=$form->{$_}";
- }
- }
-
- if ( !$form->{summary} ) {
- push @column_index, qw(invnumber ordnumber transdate duedate);
- }
- push @column_index, @c;
- push @column_index, "total";
-
- if ( $form->{overdue} ) {
- push @options, $locale->text('Aged Overdue');
- $form->{callback} .= "&overdue=$form->{overdue}";
- } else {
- push @options, $locale->text('Aged');
- }
-
- if ( $form->{department} ) {
- ($department) = split /--/, $form->{department};
- push @options, $locale->text('Department: [_1]', $department);
- $department = $form->escape( $form->{department}, 1 );
- $form->{callback} .= "&department=$department";
- }
-
- if ( $form->{arap} eq 'ar' ) {
- if ( $form->{customer} ) {
- push @options, $form->{customer};
- }
- }
- if ( $form->{arap} eq 'ap' ) {
- shift @column_index;
- if ( $form->{vendor} ) {
- push @options, $form->{vendor};
- }
- }
-
- $todate = $locale->date( \%myconfig, $form->{todate}, 1 );
- push @options, $locale->text('for Period To [_1]', $todate);
-
- $ctid = 0;
- $i = 0;
- $k = 0;
- $l = $#{ $form->{AG} };
-
- my @currencies;
- foreach my $ref ( @{ $form->{AG} } ) {
-
- if ( $curr ne $ref->{curr} ) {
- my %column_data;
- $ctid = 0;
- for (@column_index) { $column_data{$_} = ' ' }
- if ($curr) {
- $c0total = $form->format_amount(\%myconfig, $c0total, 2, ' ');
- $c30total = $form->format_amount(\%myconfig, $c30total, 2, ' ');
- $c60total = $form->format_amount(\%myconfig, $c60total, 2, ' ');
- $c90total = $form->format_amount(\%myconfig, $c90total, 2, ' ');
- $total = $form->format_amount(\%myconfig, $total, 2, ' ' );
-
- for (qw(ct statement language)) {
- $column_data{$_} = ' ';
- }
- $column_data{c0} = $c0total;
- $column_data{c30} = $c30total;
- $column_data{c60} = $c60total;
- $column_data{c90} = $c90total;
- $column_data{total} = $total;
-
- $currencies[0]{totals} = \%column_data;
-
- $c0subtotal = 0;
- $c30subtotal = 0;
- $c60subtotal = 0;
- $c90subtotal = 0;
- $subtotal = 0;
-
- $c0total = 0;
- $c30total = 0;
- $c60total = 0;
- $c90total = 0;
- $total = 0;
-
- }
-
- unshift @currencies, {};
- $curr = $ref->{curr};
- $currencies[0]{curr} = $curr;
- }
-
- $k++;
- my %column_data;
-
- if ( $ctid != $ref->{ctid} or $form->{summary}) {
- $i++;
-
- $column_data{ct} = $ref->{name};
-
- $column_data{language} = {
- name => "language_code_$i",
- options => $form->{language_options},
- default_value => $ref->{language_code},
- } if $form->{language_options};
-
- $column_data{statement} = {
- name => "statement_$i",
- type => 'checkbox',
- value => $ref->{ctid},
- };
- $column_data{statement}{checked} = 'checked' if $ref->{checked};
- $hiddens{"curr_$i"} = $ref->{curr};
- }
-
-
- $ctid = $ref->{ctid};
-
- for (qw(c0 c30 c60 c90)) {
- $ref->{$_} =
- $form->round_amount( $ref->{$_} / $ref->{exchangerate}, 2 );
- }
-
- $c0subtotal += $ref->{c0};
- $c30subtotal += $ref->{c30};
- $c60subtotal += $ref->{c60};
- $c90subtotal += $ref->{c90};
-
- $c0total += $ref->{c0};
- $c30total += $ref->{c30};
- $c60total += $ref->{c60};
- $c90total += $ref->{c90};
-
- $ref->{total} =
- ( $ref->{c0} + $ref->{c30} + $ref->{c60} + $ref->{c90} );
- $subtotal += $ref->{total};
- $total += $ref->{total};
-
- $ref->{c0} =
- $form->format_amount( \%myconfig, $ref->{c0}, 2, ' ' );
- $ref->{c30} =
- $form->format_amount( \%myconfig, $ref->{c30}, 2, ' ' );
- $ref->{c60} =
- $form->format_amount( \%myconfig, $ref->{c60}, 2, ' ' );
- $ref->{c90} =
- $form->format_amount( \%myconfig, $ref->{c90}, 2, ' ' );
- $ref->{total} =
- $form->format_amount( \%myconfig, $ref->{total}, 2, ' ' );
-
- $href =
-qq|$ref->{module}.pl?path=$form->{path}&action=edit&id=$ref->{id}&login=$form->{login}&sessionid=$form->{sessionid}&callback=|
- . $form->escape( $form->{callback} );
-
- $column_data{invnumber} = {text => $ref->{invnumber}, href => $href};
- for (qw(c0 c30 c60 c90 total ordnumber transdate duedate)) {
- $column_data{$_} = $ref->{$_};
- }
-
- if ( !$form->{summary} ) {
-
- $j++;
- $j %= 2;
- $column_data{i} = $j;
- my $rowref = {};
- $rowref->{$_} = $column_data{$_} for keys %column_data;
-
- push @{$currencies[0]{rows}}, $rowref;
- for (qw(ct statement language)) {
- $column_data{$_} = ' ';
- }
-
- }
- $column_data{ct} = $ref->{name};
-
- # prepare subtotal
- $nextid = ( $k <= $l ) ? $form->{AG}->[$k]->{ctid} : 0;
- if ( $ctid != $nextid ) {
-
- $c0subtotal =
- $form->format_amount( \%myconfig, $c0subtotal, 2, ' ' );
- $c30subtotal =
- $form->format_amount( \%myconfig, $c30subtotal, 2, ' ' );
- $c60subtotal =
- $form->format_amount( \%myconfig, $c60subtotal, 2, ' ' );
- $c90subtotal =
- $form->format_amount( \%myconfig, $c90subtotal, 2, ' ' );
- $subtotal =
- $form->format_amount( \%myconfig, $subtotal, 2, ' ' );
-
- if ( $form->{summary} ) {
- $column_data{c0} = $c0subtotal;
- $column_data{c30} = $c30subtotal;
- $column_data{c60} = $c60subtotal;
- $column_data{c90} = $c90subtotal;
- $column_data{total} = $subtotal;
-
- $j++;
- $j %= 2;
- $column_data{i} = $j;
-
- push @{$currencies[0]{rows}}, \%column_data;
-
- } else {
- for (@column_index) { $column_data{$_} = ' ' }
-
- $column_data{c0} = $c0subtotal;
- $column_data{c30} = $c30subtotal;
- $column_data{c60} = $c60subtotal;
- $column_data{c90} = $c90subtotal;
- $column_data{total} = $subtotal;
- $column_data{class} = 'subtotal';
-
- push @{$currencies[0]{rows}}, \%column_data;
- }
-
- $c0subtotal = 0;
- $c30subtotal = 0;
- $c60subtotal = 0;
- $c90subtotal = 0;
- $subtotal = 0;
-
- }
- }
-
- my %column_data;
- for (@column_index) { $column_data{$_} = ' ' }
-
- $c0total = $form->format_amount( \%myconfig, $c0total, 2, ' ' );
- $c30total = $form->format_amount( \%myconfig, $c30total, 2, ' ' );
- $c60total = $form->format_amount( \%myconfig, $c60total, 2, ' ' );
- $c90total = $form->format_amount( \%myconfig, $c90total, 2, ' ' );
- $total = $form->format_amount( \%myconfig, $total, 2, ' ' );
-
- $column_data{c0} = $c0total;
- $column_data{c30} = $c30total;
- $column_data{c60} = $c60total;
- $column_data{c90} = $c90total;
- $column_data{total} = $total;
-
- $currencies[0]{totals} = \%column_data;
-
- $row_alignment{c0} = 'right';
- $row_alignment{c30} = 'right';
- $row_alignment{c60} = 'right';
- $row_alignment{c90} = 'right';
- $row_alignment{total} = 'right';
- $hiddens{rowcount} = $i;
-
- &print_options if ( $form->{arap} eq 'ar' );
-
-
- my @buttons;
- if ( $form->{arap} eq 'ar' ) {
-
- $hiddens{$_} = $form->{$_} foreach qw(todate title summary overdue c0 c30 c60 c90 callback arap ct department path login sessionid);
- $hiddens{$form->{ct}} = $form->{$form->{ct}};
-
- # type=submit $locale->text('Select all')
- # type=submit $locale->text('Print')
- # type=submit $locale->text('E-mail')
-
- my %button = (
- 'select_all' =>
- { ndx => 1, key => 'A', value => $locale->text('Select all') },
- 'print' =>
- { ndx => 2, key => 'P', value => $locale->text('Print') },
- 'e_mail' =>
- { ndx => 5, key => 'E', value => $locale->text('E-mail') },
- );
-
- for ( sort { $button{$a}->{ndx} <=> $button{$b}->{ndx} } keys %button )
- {
- push @buttons, {
- accesskey => $button{$_}->{key},
- text => $button{$_}->{value},
- title => "$button{$_}->{value} [Alt-$button{$_}->{key}]",
- value => $_,
- name => 'action',
- };
- }
-
- }
-
-##SC: Temporary commenting
-## if ( $form->{lynx} ) {
-## require "bin/menu.pl";
-## &menubar;
-## }
-
- my %can_load;
- $can_load{CSV} = 1;
- $can_load{XLS} = ! eval { require Excel::Template::Plus };
- $can_load{ODS} = ! eval { require OpenOffice::OODoc };
-
- for my $type (qw(CSV XLS ODS)) {
- push @buttons, {
- name => 'action',
- value => lc "${type}_$form->{nextsub}",
- text => $locale->text("[_1] Report", $type),
- type => 'submit',
- class => 'submit',
- disabled => $can_load{$type} ? "" : "disabled",
- };
- }
- my $format;
- if ($form->{action} =~ /^(continue|generate_)/) {
- $format = 'HTML';
- } else {
- $format = uc substr $form->{action}, 0, 3;
- push @column_index, 'class';
- @column_index = grep {!/^(language|statement)$/} @column_index;
- $column_header{class} = 'rowtype';
- }
- my $template = LedgerSMB::Template->new(
- user => \%myconfig,
- locale => $locale,
- template => 'rp-aging',
- path => 'UI',
- format => $format,
- );
-
- $template->render({
- form => $form,
- hiddens => \%hiddens,
- buttons => ..hidden..,
- options => ..hidden..,
- columns => ..hidden..,
- heading => \%column_header,
- currencies => [reverse @currencies],
- row_alignment => {
- 'credit' => 'right',
- 'debit' => 'right',
- 'begbalance' => 'right',
- 'endbalance' => 'right'
- },
- });
-}
-
sub select_all {
RP->aging( \%myconfig, \%$form );
Added: trunk/lreports_co.pl
===================================================================
--- trunk/lreports_co.pl (rev 0)
+++ trunk/lreports_co.pl 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,8 @@
+#!/usr/bin/perl
+
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+
+require "lsmb-request.pl";
Property changes on: trunk/lreports_co.pl
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/report_aging.pl
===================================================================
--- trunk/report_aging.pl (rev 0)
+++ trunk/report_aging.pl 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,8 @@
+#!/usr/bin/perl
+
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+
+require 'lsmb-request.pl';
Property changes on: trunk/report_aging.pl
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/reports.pl
===================================================================
--- trunk/reports.pl (rev 0)
+++ trunk/reports.pl 2012-05-21 08:53:59 UTC (rev 4742)
@@ -0,0 +1,8 @@
+#!/usr/bin/perl
+
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+
+require 'lsmb-request.pl';
Property changes on: trunk/reports.pl
___________________________________________________________________
Added: svn:executable
+ *
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/sql/Pg-database.sql 2012-05-21 08:53:59 UTC (rev 4742)
@@ -1934,7 +1934,47 @@
COMMENT ON TABLE business_unit IS
$$ Tracks Projects, Departments, Funds, Etc.$$;
+CREATE TABLE budget_info (
+ id serial not null unique,
+ start_date date not null,
+ end_date date not null,
+ reference text primary key,
+ description text not null,
+ entered_by int not null references entity(id)
+ default person__get_my_entity_id(),
+ approved_by int references entity(id),
+ obsolete_by int references entity(id),
+ entered_at timestamp not null default now(),
+ approved_at timestamp,
+ obsolete_at timestamp,
+ check (start_date < end_date)
+);
+CREATE TABLE budget_to_business_unit (
+ budget_id int not null unique references budget_info(id),
+ bu_id int not null references business_unit(id),
+ bu_class int references business_unit_class(id),
+ primary key (budget_id, bu_class)
+);
+
+
+CREATE TABLE budget_line (
+ budget_id int not null references budget_info(id),
+ account_id int not null references account(id),
+ description text,
+ amount numeric not null,
+ primary key (budget_id, account_id)
+);
+
+INSERT INTO note_class (id, class) values ('6', 'Budget');
+
+CREATE TABLE budget_note (
+ primary key(id),
+ check (note_class = 6),
+ foreign key(ref_key) references budget_info(id)
+) INHERITS (note);
+ALTER TABLE budget_note ALTER COLUMN note_class SET DEFAULT 6;
+
COMMENT ON COLUMN job.parts_id IS
$$ Job costing/manufacturing here not implemented.$$;
--
Modified: trunk/sql/modules/Budgetting.sql
===================================================================
--- trunk/sql/modules/Budgetting.sql 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/sql/modules/Budgetting.sql 2012-05-21 08:53:59 UTC (rev 4742)
@@ -8,20 +8,10 @@
-- 4: Make department_id default to 0 and be not null
-- 5: Convert type definitions to views.
+BEGIN;
--- 1.4 note: Move to Util.sql
-CREATE OR REPLACE FUNCTION project_list_open(in_date date)
-RETURNS SETOF project AS
-$$ SELECT * FROM project
- WHERE $1 BETWEEN coalesce(startdate, $1) AND coalesce(enddate, $1)
-ORDER BY projectnumber;
-$$ language sql;
+DROP TYPE IF EXISTS budget_info_ext CASCADE;
--- 1.4 note: Move to Util.sql
-CREATE OR REPLACE FUNCTION department_list()
-RETURNS SETOF department AS
-$$ SELECT * FROM department ORDER BY description $$ language sql;
-
CREATE TYPE budget_info_ext AS (
id INT,
start_date date,
@@ -36,21 +26,44 @@
obsolete_at timestamp,
entered_by_name text,
approved_by_name text,
- obsolete_by_name text,
- department_id int,
- department_name text,
- project_id int,
- projectnumber text
+ obsolete_by_name text
);
COMMENT ON TYPE budget_info_ext IS
$$ This is the base budget_info type. In 1.4, it will be renamed budget and
-include an array of lines, but since we support 8.3, we can't do that.
+include an array of lines, but since we support 8.3, we cannot do that.
The id, start_date, end_date, reference, description, entered_by, approved_by,
entered_at, and approved_at fields reference the budget_info table. The other
-two fields refer to the possible joins. $$; --'
+two fields refer to the possible joins. $$;
+CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
+returns budget_info_ext AS
+$$
+select bi.id, bi.start_date, bi.end_date, bi.reference, bi.description,
+ bi.entered_by, bi.approved_by, bi.obsolete_by, bi.entered_at,
+ bi.approved_at, bi.obsolete_at,
+ ee.name, ae.name, oe.name
+ from budget_info bi
+ JOIN entity ee ON bi.entered_by = ee.id
+ LEFT JOIN entity ae ON bi.approved_by = ae.id
+ LEFT JOIN entity oe ON bi.obsolete_by = oe.id
+ where bi.id = $1;
+$$ language sql;
+
+COMMENT ON FUNCTION budget__get_info(in_id int) IS
+$$ Selects the budget info. $$;
+
+CREATE OR REPLACE FUNCTION budget__get_business_units(in_id int)
+returns setof business_unit AS
+$$ select bu.*
+ FROM business_unit bu
+ JOIN budget_to_business_unit b2bu ON b2bu.bu_id = bu.id
+ JOIN budget_info bi ON bi.id = b2bu.budget_id
+ WHERE bi.id = $1
+ ORDER BY bu.class_id;
+$$ LANGUAGE SQL;
+
CREATE OR REPLACE FUNCTION budget__search(
in_start_date date,
in_end_date date ,
@@ -60,24 +73,18 @@
in_entered_by int,
in_approved_by int,
in_obsolete_by int,
- in_department_id int,
- in_project_id int,
+ in_business_units int[],
in_is_approved bool, in_is_obsolete bool
) RETURNS SETOF budget_info_ext AS
$$
select bi.id, bi.start_date, bi.end_date, bi.reference, bi.description,
bi.entered_by, bi.approved_by, bi.obsolete_by, bi.entered_at,
bi.approved_at, bi.obsolete_at,
- ee.name, ae.name, oe.name, bd.department_id, d.description,
- bp.project_id, p.projectnumber
+ ee.name, ae.name, oe.name
from budget_info bi
JOIN entity ee ON bi.entered_by = ee.id
- LEFT JOIN budget_to_department bd ON bd.budget_id = bi.id
LEFT JOIN entity ae ON bi.approved_by = ae.id
LEFT JOIN entity oe ON bi.obsolete_by = oe.id
- LEFT JOIN budget_to_project bp ON bp.budget_id = bi.id
- LEFT JOIN department d ON d.id = bd.department_id
- LEFT JOIN project p ON bp.project_id = p.id
WHERE (start_date = $1 or $1 is null) AND ($2 = end_date or $2 is null)
AND ($3 BETWEEN start_date AND end_date or $2 is null)
AND ($4 ilike reference || '%' or $4 is null)
@@ -85,11 +92,9 @@
AND ($6 = entered_by or $6 is null)
AND ($7 = approved_by or $7 is null)
AND ($8 = obsolete_by or $8 is null)
- AND ($9 = department_id OR $9 is null)
- AND ($10 = project_id OR $10 IS NULL)
- AND ($11 IS NULL OR ($11 = (approved_by IS NOT NULL)))
- AND ($12 IS NULL OR ($12 = (obsolete_by IS NOT NULL)))
- ORDER BY department_id, project_id, reference;
+ AND ($10 IS NULL OR ($10 = (approved_by IS NOT NULL)))
+ AND ($11 IS NULL OR ($11 = (obsolete_by IS NOT NULL)))
+ ORDER BY reference;
$$ language sql;
COMMENT ON FUNCTION budget__search(
@@ -101,21 +106,26 @@
in_entered_by int,
in_approved_by int,
in_obsolete_by int,
- in_department_id int,
- in_project_id int,
+ in_business_units int[],
in_is_approved bool,
in_is_obsolete bool
) IS $$ This is a general search for budgets$$;
CREATE OR REPLACE FUNCTION budget__save_info
(in_id int, in_start_date date, in_end_date date, in_reference text,
-in_description text, in_department_id int, in_project_id int)
+in_description text, in_business_units int[])
RETURNS budget_info_ext AS
$$
DECLARE
retval budget_info_ext;
t_id int;
BEGIN
+
+ PERFORM * FROM budget_info WHERE id = in_id and approved_by is not null;
+ IF FOUND THEN
+ RAISE EXCEPTION 'report approved';
+ END IF;
+
UPDATE budget_info
SET start_date = in_start_date,
end_date = in_end_date,
@@ -125,34 +135,15 @@
IF FOUND THEN
t_id := in_id;
ELSE
- PERFORM * FROM budget_info WHERE id = in_id and approved_by is not null;
- IF FOUND THEN
- RAISE EXCEPTION 'report approved';
- END IF;
INSERT INTO budget_info (start_date, end_date, reference, description)
VALUES (in_start_date, in_end_date, in_reference, in_description);
t_id = currval('budget_info_id_seq');
- END IF;
- IF in_project_id IS NOT NULL THEN
- UPDATE budget_to_project
- SET project_id = in_project_id
- WHERE budget_id = t_id;
- IF NOT FOUND THEN
- INSERT INTO budget_to_project(budget_id, project_id)
- VALUES (t_id, in_project_id);
- END IF;
+ INSERT INTO budget_to_business_unit(budget_id, bu_id, bu_class)
+ SELECT t_id, id, class_id
+ FROM business_unit
+ WHERE id = ANY(in_business_units);
END IF;
- IF in_department_id IS NOT NULL THEN
- UPDATE budget_to_department
- SET department_id = in_department_id
- WHERE budget_id = t_id;
-
- IF NOT FOUND THEN
- INSERT INTO budget_to_department(budget_id, department_id)
- VALUES (t_id, in_department_id);
- END IF;
- END IF;
retval := budget__get_info(t_id);
return retval;
END;
@@ -160,7 +151,7 @@
COMMENT ON FUNCTION budget__save_info
(in_id int, in_start_date date, in_end_date date, in_reference text,
-in_description text, in_department_id int, in_project_id int) IS
+in_description text, in_business_units int[]) IS
$$Saves the extended budget info passed through to the function. See the
comment on type budget_info_ext for more information.$$;
@@ -204,28 +195,6 @@
where each entry is {int account_id, text description, numeric amount}. The
in_id parameter is the budget_id.$$;
-CREATE OR REPLACE FUNCTION budget__get_info(in_id int)
-returns budget_info_ext AS
-$$
-select bi.id, bi.start_date, bi.end_date, bi.reference, bi.description,
- bi.entered_by, bi.approved_by, bi.obsolete_by, bi.entered_at,
- bi.approved_at, bi.obsolete_at,
- ee.name, ae.name, oe.name, bd.department_id, d.description,
- bp.project_id, p.projectnumber
- from budget_info bi
- JOIN entity ee ON bi.entered_by = ee.id
- LEFT JOIN budget_to_department bd ON bd.budget_id = bi.id
- LEFT JOIN entity ae ON bi.approved_by = ae.id
- LEFT JOIN entity oe ON bi.obsolete_by = oe.id
- LEFT JOIN budget_to_project bp ON bp.budget_id = bi.id
- LEFT JOIN department d ON d.id = bd.department_id
- LEFT JOIN project p ON bp.project_id = p.id
- where bi.id = $1;
-$$ language sql;
-
-COMMENT ON FUNCTION budget__get_info(in_id int) IS
-$$ Selects the budget info. $$;
-
CREATE OR REPLACE FUNCTION budget__get_details(in_id int)
RETURNS SETOF budget_line AS
$$
@@ -269,6 +238,7 @@
$$ Returns all notes associated with a budget, by default in the order they
were created.$$;
+DROP TYPE IF EXISTS budget_variance_report CASCADE;
CREATE TYPE budget_variance_report AS (
accno text,
account_label text,
@@ -349,3 +319,5 @@
COMMENT ON FUNCTION budget__reject(in_id int) IS
$$ Deletes unapproved budgets only.$$;
+
+COMMIT;
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/sql/modules/LOADORDER 2012-05-21 08:53:59 UTC (rev 4742)
@@ -30,5 +30,6 @@
Files.sql
Parts.sql
COGS.sql
+Budgetting.sql
Fixes.sql
Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql 2012-05-21 07:24:06 UTC (rev 4741)
+++ trunk/sql/modules/Report.sql 2012-05-21 08:53:59 UTC (rev 4742)
@@ -36,27 +36,26 @@
CREATE OR REPLACE FUNCTION report__invoice_aging_detail
(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
- in_business_units int[]))
+ in_business_units int[])
RETURNS SETOF report_aging_item
AS
$$
DECLARE
item report_aging_item;
BEGIN
- IF in_entity_class = 1 THEN
FOR item IN
WITH RECURSIVE bu_tree (id, path) AS (
SELECT id, id::text AS path
FROM business_unit
- WHERE id in(in_business_units)
+ WHERE id = any(in_business_units)
UNION
SELECT bu.id, bu_tree.path || ',' || bu.id
FROM business_unit bu
JOIN bu_tree ON bu_tree.id = bu.parent_id
)
SELECT c.entity_id, c.meta_number, e.name,
- l.line_one as address1, l.line_two as address2,
- l.line_three as address3,
+ l.line_one, l.line_two,
+ l.line_three,
l.city, l.state, l.mail_code, country.name as country,
e.name as contact_name,
a.invnumber, a.transdate, a.till, a.ordnumber,
@@ -64,32 +63,30 @@
CASE WHEN
EXTRACT(days FROM age(min(ac.transdate),
coalesce(in_to_date,
- now())/30)
+ now()))/30)
= 0
- THEN (a.amount - (a.sign * sum(ac.amount))
+ THEN (a.amount - a.sign * sum(ac.amount))
ELSE 0 END
as c0,
CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
coalesce(in_to_date,
- now())/30)
+ now()))/30)
= 1
- THEN (a.amount - (a.sign * sum(ac.amount))
+ THEN (a.amount - a.sign * sum(ac.amount))
ELSE 0 END
as c30,
CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
coalesce(in_to_date,
- now())/30)
+ now()))/30)
= 2
- THEN (a.amount - (a.sign * sum(ac.amount),
- coalesce(in_to_date,
- now())
+ THEN (a.amount - (a.sign * sum(ac.amount)))
ELSE 0 END
as c60,
CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
coalesce(in_to_date,
- now())/30)
+ now()))/30)
> 2
- THEN (a.amount - (a.sign * sum(ac.amount))
+ THEN (a.amount - a.sign * sum(ac.amount))
ELSE 0 END
as c90,
a.duedate, a.id, a.curr,
@@ -104,13 +101,13 @@
WHERE i.trans_id = a.id) AS line_items
FROM (select id, invnumber, till, ordnumber, amount, duedate,
curr, ponumber, notes, entity_credit_account,
- -1 AS sign
+ -1 AS sign, transdate
FROM ar
WHERE in_entity_class = 2
UNION
SELECT id, invnumber, null, ordnumber, amount, duedate,
curr, ponumber, notes, entity_credit_account,
- 1 as sign
+ 1 as sign, transdate
FROM ap
WHERE in_entity_class = 1) a
JOIN acc_trans ac ON ac.trans_id = a.id
@@ -133,12 +130,10 @@
WHERE (e.id = in_entity_id OR in_entity_id IS NULL)
AND (in_accno IS NULL or acc.accno = in_accno)
GROUP BY c.entity_id, c.meta_number, e.name,
- l.line_one as address1, l.line_two as address2,
- l.line_three as address3,
- l.city_province, l.mail_code, country.name as country,
- e.name as contact_name,
+ l.line_one, l.line_two, l.line_three,
+ l.city, l.state, l.mail_code, country.name,
a.invnumber, a.transdate, a.till, a.ordnumber,
- a.ponumber, a.notes,
+ a.ponumber, a.notes, a.amount, a.sign,
a.duedate, a.id, a.curr
HAVING in_business_units is null or in_business_units
<@ compound_array(string_to_array(bu_tree.path,
@@ -152,14 +147,15 @@
CREATE OR REPLACE FUNCTION report__invoice_aging_summary
(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
- in_business_units int[]))
+ in_business_units int[])
RETURNS SETOF report_aging_item
AS $$
SELECT entity_id, account_number, name, address1, address2, address3, city,
- state, mail_code, country, contact_name, invnumber, null, null, null,
- null, null, sum(c0), sum(c30), sum(c60), sum(c90), null, null, curr,
- null, null
- FROM report__invoice_aging_summary($1, $2, $3, $4)
+ state, mail_code, country, contact_name, invnumber, null::date,
+ null::text, null::text, null::text, null::text,
+ sum(c0), sum(c30), sum(c60), sum(c90), null::date, null::int, curr,
+ null::numeric, null::text[]
+ FROM report__invoice_aging_detail($1, $2, $3, $4, $5)
GROUP BY entity_id, account_number, name, address1, address2, address3, city,
state, mail_code, country, contact_name, invnumber, curr
ORDER BY account_number
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.