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

SF.net SVN: ledger-smb:[4742] trunk



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>&nbsp;
+<?lsmb IF FORMATS.grep('PDF').size()
+?>
+<a href="<?lsmb LINK _ '&format=PDF' ?>">[<?lsmb text('PDF') ?>]</a>&nbsp;
+<?lsmb END;
+IF FORMATS.grep('TXT').size();
+?>
+<a href="<?lsmb LINK _ '&format=CSV' ?>">[<?lsmb text('CSV') ?>]</a>&nbsp;
+<?lsmb END;
+IF FORMATS.grep('XLS').size() ?>
+<a href="<?lsmb LINK _ '&format=XLS' ?>">[<?lsmb text('XLS') ?>]</a>&nbsp;
+<?lsmb END;
+IF FORMATS.grep('ODS').size() ?>
+<a href="<?lsmb LINK _ '&format=ODS' ?>">[<?lsmb text('ODS') ?>]</a>&nbsp;
+<?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.