[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4631] trunk
- Subject: SF.net SVN: ledger-smb:[4631] trunk
- From: ..hidden..
- Date: Fri, 30 Mar 2012 00:02:01 +0000
Revision: 4631
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4631&view=rev
Author: einhverfr
Date: 2012-03-30 00:02:00 +0000 (Fri, 30 Mar 2012)
Log Message:
-----------
Beginnings of Excel templates (not working yet)
Adjustments to PDF GL reports
Centralized db commits for old code.
Merging changes from branches/1.3
Modified Paths:
--------------
trunk/Changelog
trunk/LedgerSMB/DBObject/Report/GL.pm
trunk/LedgerSMB/Form.pm
trunk/LedgerSMB/Template/LaTeX.pm
trunk/LedgerSMB/Template.pm
trunk/UI/Reports/display_report.html
trunk/UI/lib/dynatable.tex
trunk/old-handler.pl
trunk/sql/modules/Payment.sql
Property Changed:
----------------
trunk/
trunk/LedgerSMB/Scripts/account.pm
trunk/LedgerSMB/Scripts/admin.pm
trunk/LedgerSMB/Scripts/customer.pm
trunk/LedgerSMB/Scripts/employee.pm
trunk/LedgerSMB/Scripts/file.pm
trunk/LedgerSMB/Scripts/journal.pm
trunk/LedgerSMB/Scripts/login.pm
trunk/LedgerSMB/Scripts/menu.pm
trunk/LedgerSMB/Scripts/payment.pm
trunk/LedgerSMB/Scripts/recon.pm
trunk/LedgerSMB/Scripts/setup.pm
trunk/LedgerSMB/Scripts/taxform.pm
trunk/LedgerSMB/Scripts/vendor.pm
trunk/sql/upgrade/1.2-1.3-manual.sql
Property changes on: trunk
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3:3711-4625
+ /branches/1.3:3711-4630
Modified: trunk/Changelog
===================================================================
--- trunk/Changelog 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/Changelog 2012-03-30 00:02:00 UTC (rev 4631)
@@ -15,21 +15,29 @@
* LedgerSMB->error and Form->error now show db version and company (Chris T)
* Simpler use of Log::Log4perl instead of LedgerSMB::Log (Chris T)
* Changing all auth calls to hit postgres db instead of template1 (Chris T)
+* Centralized database commit for new code (Chris T)
New Reporting Framework
* Easy bridge between SQL and display (Chris T)
* All reports exportable to CSV (Chris T)
+* All reports exportable to PDF (Chris T)
+* Unified UI for reports (Chris T)
Customer/Vendor Handling
* Added sales tax id and license number fields for companies (Chris T)
* Simpified database schema (Chris T)
-New CSV Import Module (Chris T)
-* Imports GL transactions
-* Imports AP batches
+New CSV Import Module
+* Imports GL transactions (Chris T)
+* Imports AP batches (Chris T)
+* Imports charts of accounts entries (Erik H)
+* Imports GIFI tables (Erik H)
+* Imports SIC tables (Erik H)
+* Imports timecards (Chris T)
* Extensible
* field maps can be overridden
+
New Business Reporting Unit System (Chris T)
* Replaces Projects and Departments
* Business reporting units may be nested
Modified: trunk/LedgerSMB/DBObject/Report/GL.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/GL.pm 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/LedgerSMB/DBObject/Report/GL.pm 2012-03-30 00:02:00 UTC (rev 4631)
@@ -86,7 +86,7 @@
{col_id => 'transdate',
name => $locale->text('Date'),
type => 'text',
- pwidth => '3', },
+ pwidth => '4', },
{col_id => 'reference',
name => $locale->text('Reference'),
@@ -97,7 +97,7 @@
{col_id => 'description',
name => $locale->text('Description'),
type => 'text',
- pwidth => '12', },
+ pwidth => '6', },
{col_id => 'entry_id',
name => $locale->text('Entry ID'),
@@ -127,7 +127,7 @@
{col_id => 'cleared',
name => $locale->text('Cleared'),
type => 'text',
- pwidth => '1', },
+ pwidth => '3', },
{col_id => 'till',
name => $locale->text('Till'),
Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/LedgerSMB/Form.pm 2012-03-30 00:02:00 UTC (rev 4631)
@@ -67,6 +67,7 @@
use LedgerSMB::Company_Config;
use LedgerSMB::PGNumber;
use Log::Log4perl;
+use LedgerSMB::App_State;
use charnames qw(:full);
use open ':utf8';
@@ -1262,6 +1263,7 @@
}
LedgerSMB::Company_Config::initialize($self);
$sth->finish();
+ LedgerSMB::App_State::DBH = $self->{dbh};
$logger->trace("end");
}
Property changes on: trunk/LedgerSMB/Scripts/account.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/account.pm:4369-4625
/branches/1.3/scripts/account.pl:3711-4368
+ /branches/1.3/LedgerSMB/Scripts/account.pm:4369-4630
/branches/1.3/scripts/account.pl:3711-4368
Property changes on: trunk/LedgerSMB/Scripts/admin.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4625
/branches/1.3/scripts/admin.pl:3711-3903,4273-4287
+ /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4630
/branches/1.3/scripts/admin.pl:3711-3903,4273-4287
Property changes on: trunk/LedgerSMB/Scripts/customer.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/customer.pm:4288-4625
/branches/1.3/scripts/customer.pl:4273-4287
+ /branches/1.3/LedgerSMB/Scripts/customer.pm:4288-4630
/branches/1.3/scripts/customer.pl:4273-4287
Property changes on: trunk/LedgerSMB/Scripts/employee.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4625
/branches/1.3/scripts/employee.pl:3842-3843,4273-4287,4289-4310
+ /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4630
/branches/1.3/scripts/employee.pl:3842-3843,4273-4287,4289-4310
Property changes on: trunk/LedgerSMB/Scripts/file.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/file.pm:3711-4625
/branches/1.3/scripts/file.pl:3711-4138
+ /branches/1.3/LedgerSMB/Scripts/file.pm:3711-4630
/branches/1.3/scripts/file.pl:3711-4138
Property changes on: trunk/LedgerSMB/Scripts/journal.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/journal.pm:4288-4625
/branches/1.3/scripts/journal.pl:3711-4328
+ /branches/1.3/LedgerSMB/Scripts/journal.pm:4288-4630
/branches/1.3/scripts/journal.pl:3711-4328
Property changes on: trunk/LedgerSMB/Scripts/login.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/login.pm:4193-4625
/branches/1.3/scripts/login.pl:3711-4192
+ /branches/1.3/LedgerSMB/Scripts/login.pm:4193-4630
/branches/1.3/scripts/login.pl:3711-4192
Property changes on: trunk/LedgerSMB/Scripts/menu.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/menu.pm:4155-4625
/branches/1.3/scripts/menu.pl:3711-4192,4273-4287
+ /branches/1.3/LedgerSMB/Scripts/menu.pm:4155-4630
/branches/1.3/scripts/menu.pl:3711-4192,4273-4287
Property changes on: trunk/LedgerSMB/Scripts/payment.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4625
/branches/1.3/scripts/payment.pl:3711-4617
+ /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4630
/branches/1.3/scripts/payment.pl:3711-4617
Property changes on: trunk/LedgerSMB/Scripts/recon.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/recon.pm:3711-4625
/branches/1.3/scripts/recon.pl:4194-4271,4273-4287,4393-4438
+ /branches/1.3/LedgerSMB/Scripts/recon.pm:3711-4630
/branches/1.3/scripts/recon.pl:4194-4271,4273-4287,4393-4438
Property changes on: trunk/LedgerSMB/Scripts/setup.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4625
/branches/1.3/scripts/setup.pl:3711-4550
+ /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4630
/branches/1.3/scripts/setup.pl:3711-4550
Property changes on: trunk/LedgerSMB/Scripts/taxform.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/taxform.pm:4193-4625
/branches/1.3/scripts/taxform.pl:3711-4192,4273-4287
+ /branches/1.3/LedgerSMB/Scripts/taxform.pm:4193-4630
/branches/1.3/scripts/taxform.pl:3711-4192,4273-4287
Property changes on: trunk/LedgerSMB/Scripts/vendor.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/vendor.pm:4288-4625
/branches/1.3/scripts/vendor.pl:4273-4287
+ /branches/1.3/LedgerSMB/Scripts/vendor.pm:4288-4630
/branches/1.3/scripts/vendor.pl:4273-4287
Modified: trunk/LedgerSMB/Template/LaTeX.pm
===================================================================
--- trunk/LedgerSMB/Template/LaTeX.pm 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/LedgerSMB/Template/LaTeX.pm 2012-03-30 00:02:00 UTC (rev 4631)
@@ -57,6 +57,7 @@
use Error qw(:try);
use Template::Latex;
use LedgerSMB::Template::TTI18N;
+use Log::Log4perl;
#my $binmode = ':utf8';
my $binmode = ':raw';
Modified: trunk/LedgerSMB/Template.pm
===================================================================
--- trunk/LedgerSMB/Template.pm 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/LedgerSMB/Template.pm 2012-03-30 00:02:00 UTC (rev 4631)
@@ -85,6 +85,26 @@
=back
+=item available_formats()
+
+Returns a list of format names, any of the following (in order) as applicable:
+
+=over
+
+=item HTML (always available)
+
+=item TXT (includes CSV, always available))
+
+=item PDF
+
+=item PS
+
+=item XLS
+
+=item ODS
+
+=back
+
=item new_UI(user => \%myconfig, locale => $locale, template => $file, ...)
Wrapper around the constructor that sets the path to 'UI', format to 'HTML',
@@ -144,6 +164,20 @@
my $logger = Log::Log4perl->get_logger('LedgerSMB::Template');
+sub available_formats {
+ my @retval = ('HTML', 'TXT');
+ if (eval {require LedgerSMB::Template::LaTeX}){
+ push @retval, 'PDF', 'PS';
+ }
+ if (eval {require LedgerSMB::Template::XLS}){
+ push @retval, 'XLS';
+ }
+ if (eval {require LedgerSMB::Template::ODS}){
+ push @retval, 'ODS';
+ }
+ return ..hidden..;
+}
+
sub new {
my $class = shift;
my $self = {};
@@ -244,6 +278,7 @@
sub render {
my $self = shift;
my $vars = shift;
+ $vars->{FORMATS} = $self->available_formats;
$vars->{ENVARS} = \%ENV;
$vars->{USER} = $LedgerSMB::App_State::User;
if ($self->{format} !~ /^\p{IsAlnum}+$/) {
Modified: trunk/UI/Reports/display_report.html
===================================================================
--- trunk/UI/Reports/display_report.html 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/UI/Reports/display_report.html 2012-03-30 00:02:00 UTC (rev 4631)
@@ -28,9 +28,19 @@
attributes = {class = 'report' } ?>
<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 ?>
Modified: trunk/UI/lib/dynatable.tex
===================================================================
--- trunk/UI/lib/dynatable.tex 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/UI/lib/dynatable.tex 2012-03-30 00:02:00 UTC (rev 4631)
@@ -1,6 +1,6 @@
<?lsmb- BLOCK dynatable;
-TOTAL_WIDTH=82; # cm. using A4 as a basis because it is slightly narrower than
+TOTAL_WIDTH=14; # cm. using A4 as a basis because it is slightly narrower than
# US Letter. This way the dynatable works for both paper sizes.
# This assumes a 1cm margin on either side. --CT
DECLARED_WIDTH=0;
@@ -22,7 +22,7 @@
'|';
END;
IF COL.pwidth;
- "p{" _ (COL_pwidth * WIDTH_PER_P) _ "cm}";
+ "p{" _ (COL.pwidth * WIDTH_PER_P) _ "cm}";
ELSIF COL.palign;
COL.palign;
ELSE;
@@ -40,9 +40,14 @@
<?lsmb
SKIP_TYPES = ['hidden', 'radio', 'checkbox'];
+ADD_SEP = 0;
FOREACH COL IN columns;
+ IF ADD_SEP;
+ ' & ';
+ END;
+ ADD_SEP = 1;
IF 0 == SKIP_TYPES.grep(COL.type).size();
- COL.name _ ' & ';
+ COL.name;
END;
END;
-?>\\
@@ -51,9 +56,14 @@
<?lsmb
SKIP_TYPES = ['hidden', 'radio', 'checkbox'];
+ADD_SEP = 0;
FOREACH COL IN columns;
+ IF ADD_SEP;
+ ' & ';
+ END;
+ ADD_SEP = 1;
IF 0 == SKIP_TYPES.grep(COL.type).size();
- COL.name _ ' & ';
+ COL.name;
END;
END;
-?>\\
@@ -62,10 +72,15 @@
<?lsmb
FOREACH ROW IN tbody.rows;
+ ADD_SEP = 0;
FOREACH COL IN columns;
+ IF ADD_SEP;
+ ' & ';
+ END;
+ ADD_SEP = 1;
COL_ID = COL.col_id;
IF 0 == SKIP_TYPES.grep(COL.type).size();
- ROW.$COL_ID _ ' & '; # $
+ ROW.$COL_ID; #$
END;
END;
?>\\
Modified: trunk/old-handler.pl
===================================================================
--- trunk/old-handler.pl 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/old-handler.pl 2012-03-30 00:02:00 UTC (rev 4631)
@@ -61,6 +61,7 @@
use LedgerSMB::Session;
use LedgerSMB::CancelFurtherProcessing;
use Data::Dumper;
+use LedgerSMB::App_State;
our $logger=Log::Log4perl->get_logger('old-handler-chain');#make logger available to other old programs
@@ -160,6 +161,7 @@
. " $form->{version} - $myconfig{name} - $myconfig{dbname}";
&{ $form->{action} };
+ LedgerSMB::App_State::cleanup();
}
else {
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2012-03-29 18:52:58 UTC (rev 4630)
+++ trunk/sql/modules/Payment.sql 2012-03-30 00:02:00 UTC (rev 4631)
@@ -69,29 +69,40 @@
-- payment_get_open_accounts and the option to get all accounts need to be
-- refactored and redesigned. -- CT
-CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
+CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
returns SETOF entity AS
$$
DECLARE out_entity entity%ROWTYPE;
BEGIN
- FOR out_entity IN
- SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
- FROM entity e
- JOIN entity_credit_account ec ON (ec.entity_id = e.id)
- JOIN company cp ON (cp.entity_id = e.id)
- WHERE ec.entity_class = in_account_class
+ FOR out_entity IN
+ SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
+ FROM entity e
+ JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+ JOIN company cp ON (cp.entity_id = e.id)
+ WHERE ec.entity_class = in_account_class
AND CASE WHEN in_account_class = 1 THEN
- ec.id IN (SELECT entity_credit_account FROM ap
- WHERE amount <> paid
- GROUP BY entity_credit_account)
- WHEN in_account_class = 2 THEN
- ec.id IN (SELECT entity_credit_account FROM ar
- WHERE amount <> paid
- GROUP BY entity_credit_account)
- END
- LOOP
- RETURN NEXT out_entity;
- END LOOP;
+ ec.id IN
+ (SELECT entity_credit_account
+ FROM acc_trans
+ JOIN chart ON (acc_trans.chart_id = chart.id)
+ JOIN ap ON (acc_trans.trans_id = ap.id)
+ WHERE link = 'AP'
+ GROUP BY chart_id,
+ trans_id, entity_credit_account
+ HAVING SUM(acc_trans.amount) <> 0)
+ WHEN in_account_class = 2 THEN
+ ec.id IN (SELECT entity_credit_account
+ FROM acc_trans
+ JOIN chart ON (acc_trans.chart_id = chart.id)
+ JOIN ar ON (acc_trans.trans_id = ar.id)
+ WHERE link = 'AR'
+ GROUP BY chart_id,
+ trans_id, entity_credit_account
+ HAVING SUM(acc_trans.amount) <> 0)
+ END
+ LOOP
+ RETURN NEXT out_entity;
+ END LOOP;
END;
$$ LANGUAGE PLPGSQL;
@@ -302,125 +313,126 @@
CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
(in_account_class int, in_business_id int, in_currency char(3),
- in_date_from date, in_date_to date, in_batch_id int,
- in_ar_ap_accno text, in_meta_number text)
+ in_date_from date, in_date_to date, in_batch_id int,
+ in_ar_ap_accno text, in_meta_number text)
RETURNS SETOF payment_contact_invoice AS
$$
DECLARE payment_item payment_contact_invoice;
BEGIN
- FOR payment_item IN
- SELECT c.id AS contact_id, e.control_code as econtrol_code,
- c.description as eca_description,
- e.name AS contact_name,
- c.meta_number AS account_number,
- sum( case when u.username IS NULL or
- u.username = SESSION_USER
- THEN
- coalesce(p.due::numeric, 0) -
- CASE WHEN c.discount_terms
- > extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (coalesce(p.due::numeric, 0)) *
- coalesce(c.discount::numeric, 0) / 100
- END
- ELSE 0::numeric
- END) AS total_due,
- compound_array(ARRAY[[
- a.id::text, a.invnumber, a.transdate::text,
- a.amount::text, (a.amount - p.due)::text,
- (CASE WHEN c.discount_terms
- < extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (coalesce(p.due, 0) * coalesce(c.discount, 0) / 100)
- END)::text,
- (coalesce(p.due, 0) -
- (CASE WHEN c.discount_terms
- < extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
- END))::text,
- case when u.username IS NOT NULL
- and u.username <> SESSION_USER
- THEN 0::text
- ELSE 1::text
- END,
- COALESCE(u.username, 0::text)
- ]]),
+ FOR payment_item IN
+ SELECT c.id AS contact_id, e.control_code as econtrol_code,
+ c.description as eca_description,
+ e.name AS contact_name,
+ c.meta_number AS account_number,
+ sum( case when u.username IS NULL or
+ u.username = SESSION_USER
+ THEN
+ coalesce(p.due::numeric, 0) -
+ CASE WHEN c.discount_terms
+ > extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(p.due::numeric, 0)) *
+ coalesce(c.discount::numeric, 0) / 100
+ END
+ ELSE 0::numeric
+ END) AS total_due,
+ compound_array(ARRAY[[
+ a.id::text, a.invnumber, a.transdate::text,
+ a.amount::text, (a.amount - p.due)::text,
+ (CASE WHEN c.discount_terms
+ < extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(p.due, 0) * coalesce(c.discount, 0) / 100)
+ END)::text,
+ (coalesce(p.due, 0) -
+ (CASE WHEN c.discount_terms
+ < extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
+ END))::text,
+ case when u.username IS NOT NULL
+ and u.username <> SESSION_USER
+ THEN 0::text
+ ELSE 1::text
+ END,
+ COALESCE(u.username, 0::text)
+ ]]),
sum(case when a.batch_id = in_batch_id then 1
- else 0 END),
- bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = (
- select id from users WHERE username =
- SESSION_USER))))
+ else 0 END),
+ bool_and(lock_record(a.id, (select max(session_id)
+ FROM "session" where users_id = (
+ select id from users WHERE username =
+ SESSION_USER))))
- FROM entity e
- JOIN entity_credit_account c ON (e.id = c.entity_id)
- JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id,
- curr, 1 as invoice_class,
- entity_credit_account, on_hold, v.batch_id,
- approved, paid
- FROM ap
- LEFT JOIN (select * from voucher where batch_class = 1) v
- ON (ap.id = v.trans_id)
- WHERE in_account_class = 1
- AND (v.batch_class = 1 or v.batch_id IS NULL)
- UNION
- SELECT ar.id, invnumber, transdate, amount, entity_id,
- curr, 2 as invoice_class,
- entity_credit_account, on_hold, v.batch_id,
- approved, paid
- FROM ar
- LEFT JOIN (select * from voucher where batch_class = 2) v
- ON (ar.id = v.trans_id)
- WHERE in_account_class = 2
- AND (v.batch_class = 2 or v.batch_id IS NULL)
- ORDER BY transdate
- ) a ON (a.entity_credit_account = c.id)
- JOIN transactions t ON (a.id = t.id)
- JOIN (SELECT acc_trans.trans_id,
- sum(CASE WHEN in_account_class = 1 THEN amount
- WHEN in_account_class = 2
- THEN amount * -1
- END) AS due
- FROM acc_trans
- JOIN account coa ON (coa.id = acc_trans.chart_id)
+ FROM entity e
+ JOIN entity_credit_account c ON (e.id = c.entity_id)
+ JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id,
+ curr, 1 as invoice_class,
+ entity_credit_account, on_hold, v.batch_id,
+ approved, paid
+ FROM ap
+ LEFT JOIN (select * from voucher where batch_class = 1) v
+ ON (ap.id = v.trans_id)
+ WHERE in_account_class = 1
+ AND (v.batch_class = 1 or v.batch_id IS NULL)
+ UNION
+ SELECT ar.id, invnumber, transdate, amount, entity_id,
+ curr, 2 as invoice_class,
+ entity_credit_account, on_hold, v.batch_id,
+ approved, paid
+ FROM ar
+ LEFT JOIN (select * from voucher where batch_class = 2) v
+ ON (ar.id = v.trans_id)
+ WHERE in_account_class = 2
+ AND (v.batch_class = 2 or v.batch_id IS NULL)
+ ORDER BY transdate
+ ) a ON (a.entity_credit_account = c.id)
+ JOIN transactions t ON (a.id = t.id)
+ JOIN (SELECT acc_trans.trans_id,
+ sum(CASE WHEN in_account_class = 1 THEN amount
+ WHEN in_account_class = 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)
- LEFT JOIN voucher v ON (acc_trans.voucher_id = v.id)
- WHERE ((al.description = 'AP' AND in_account_class = 1)
- OR (al.description = 'AR' AND in_account_class = 2))
- AND (approved IS TRUE or v.batch_class IN (3, 6))
- GROUP BY acc_trans.trans_id) p ON (a.id = p.trans_id)
- LEFT JOIN "session" s ON (s."session_id" = t.locked_by)
- LEFT JOIN users u ON (u.id = s.users_id)
- WHERE (a.batch_id = in_batch_id
- OR (a.invoice_class = in_account_class
- AND a.approved
- AND a.amount <> a.paid
- AND NOT a.on_hold
+ LEFT JOIN voucher v ON (acc_trans.voucher_id = v.id)
+ WHERE ((al.description = 'AP' AND in_account_class = 1)
+ OR (al.description = 'AR' AND in_account_class = 2))
+ AND (approved IS TRUE or v.batch_class IN (3, 6))
+ GROUP BY acc_trans.trans_id) p ON (a.id = p.trans_id)
+ LEFT JOIN "session" s ON (s."session_id" = t.locked_by)
+ LEFT JOIN users u ON (u.id = s.users_id)
+ WHERE (a.batch_id = in_batch_id
+ OR (a.invoice_class = in_account_class
+ AND a.approved
+ AND due <> 0
+ AND NOT a.on_hold
AND a.curr = in_currency
- AND EXISTS (select trans_id FROM acc_trans
- WHERE trans_id = a.id AND
- chart_id = (SELECT id from account
- WHERE accno
- = in_ar_ap_accno)
- )))
- AND (in_meta_number IS NULL OR
+ AND EXISTS (select trans_id FROM acc_trans
+ WHERE trans_id = a.id AND
+ chart_id = (SELECT id from account
+ WHERE accno
+ = in_ar_ap_accno)
+ )))
+ AND (in_meta_number IS NULL OR
in_meta_number = c.meta_number)
- GROUP BY c.id, e.name, c.meta_number, c.threshold,
- e.control_code, c.description
- HAVING (sum(p.due) >= c.threshold
- OR sum(case when a.batch_id = in_batch_id then 1
+ GROUP BY c.id, e.name, c.meta_number, c.threshold,
+ e.control_code, c.description
+ HAVING (sum(p.due) >= c.threshold
+ OR sum(case when a.batch_id = in_batch_id then 1
else 0 END) > 0)
ORDER BY c.meta_number ASC
- LOOP
- RETURN NEXT payment_item;
- END LOOP;
+ LOOP
+ RETURN NEXT payment_item;
+ END LOOP;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION payment_get_all_contact_invoices
(in_account_class int, in_business_id int, in_currency char(3),
- in_date_from date, in_date_to date, in_batch_id int,
- in_ar_ap_accno text, in_meta_number text) IS
+ in_date_from date, in_date_to date, in_batch_id int,
+ in_ar_ap_accno text, in_meta_number text) IS
$$
This function takes the following arguments (all prefaced with in_ in the db):
account_class: 1 for vendor, 2 for customer
Property changes on: trunk/sql/upgrade/1.2-1.3-manual.sql
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4625
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710
+ /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4630
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.