[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3875] addons/1.3/budgetting/trunk
- Subject: SF.net SVN: ledger-smb:[3875] addons/1.3/budgetting/trunk
- From: ..hidden..
- Date: Mon, 17 Oct 2011 09:39:20 +0000
Revision: 3875
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3875&view=rev
Author: einhverfr
Date: 2011-10-17 09:39:20 +0000 (Mon, 17 Oct 2011)
Log Message:
-----------
Budgetting module feature complete
Modified Paths:
--------------
addons/1.3/budgetting/trunk/scripts/budgets.pl
addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql
Modified: addons/1.3/budgetting/trunk/scripts/budgets.pl
===================================================================
--- addons/1.3/budgetting/trunk/scripts/budgets.pl 2011-10-16 14:24:08 UTC (rev 3874)
+++ addons/1.3/budgetting/trunk/scripts/budgets.pl 2011-10-17 09:39:20 UTC (rev 3875)
@@ -4,6 +4,7 @@
=cut
package LedgerSMB::Scripts::budgets;
+use strict;
=head1 SYNOPSYS
Budget workflow scripts.
@@ -52,11 +53,11 @@
sub _render_screen {
my ($budget) = @_;
- $additional_rows = 5;
+ my $additional_rows = 5;
$additional_rows +=20 unless $budget->{rowcount};
$additional_rows = 0 if $budget->{id};
$budget->{rowcount} ||= 0;
- for $row (@{$budget->{display_rows}}){
+ for my $row (@{$budget->{display_rows}}){
$row->{debit} = $budget->format_amount(amount => $row->{debit},
money => 1) if $row->{debit} ;
$row->{credit} = $budget->format_amount(amount => $row->{credit},
@@ -158,7 +159,7 @@
} if ($request->{"debit_$_"} or $request->{"credit_$_"});
}
- $budget->{rowcount} = scalar @{$request->{display_rows}};
+ $request->{rowcount} = scalar @{$request->{display_rows}};
new_budget(@_);
}
@@ -316,6 +317,56 @@
=cut
+sub search {
+ my ($request) = @_;
+ my $budget = LedgerSMB::DBObject::Budget->new({base => $request});
+ my @rows = $budget->search;
+ my $cols = ['start_date',
+ 'end_date',
+ 'reference',
+ 'description',
+ 'entered_by_name',
+ 'approved_by_name',
+ 'obsolete_by_name',
+ 'department_name',
+ 'project_number',
+ ];
+ my $heading = {
+ start_date => $budget->{_locale}->text('Start Date'),
+ end_date => $budget->{_locale}->text('End Date'),
+ reference => $budget->{_locale}->text('Reference'),
+ description => $budget->{_locale}->text('Description'),
+ entered_by_name => $budget->{_locale}->text('Entered by'),
+ approved_by_name => $budget->{_locale}->text('Approved By'),
+ obsolete_by_name => $budget->{_locale}->text('Obsolete By'),
+ department_name => $budget->{_locale}->text('Department'),
+ project_number => $budget->{_locale}->text('Project'),
+ };
+
+ my $base_url = 'budgets.pl';
+
+ for my $row (@rows){
+ $row->{reference} = { href => $base_url
+ . '?action=view_budget'
+ . '&id=' . $row->{id},
+ text => $row->{reference},
+ };
+ }
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => ($budget->{format}) ? $budget->{format} : 'HTML',
+ );
+ $template->render({
+ form => $budget,
+ columns => $cols,
+ rows => ..hidden..,
+ heading => $heading,
+ });
+}
+
=back
=head1 SEE ALSO
Modified: addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql
===================================================================
--- addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql 2011-10-16 14:24:08 UTC (rev 3874)
+++ addons/1.3/budgetting/trunk/sql/modules/Budgetting.sql 2011-10-17 09:39:20 UTC (rev 3875)
@@ -78,14 +78,17 @@
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 ($2 = end_date)
- OR ($3 BETWEEN start_date AND end_date)
- OR ($4 ilike reference || '%')
- OR (bi.description @@ plainto_tsquery($5))
- OR ($6 = entered_by) OR ($7 = approved_by) OR ($8 = obsolete_by)
- OR ($9 = department_id) OR ($10 = project_id)
- OR ($11 IS NULL OR ($11 = (approved_by IS NOT NULL)))
- OR ($12 IS NULL OR ($12 = (obsolete_by IS NOT NULL)))
+ 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)
+ AND (bi.description @@ plainto_tsquery($5) or $5 is null)
+ 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;
$$ language sql;
@@ -283,24 +286,26 @@
RETURNS SETOF budget_variance_report
AS
$$
- WITH agg_account (amount, id, accno, description, transdate)
+ WITH agg_account (amount, id, transdate)
AS ( SELECT ac.amount *
CASE WHEN a.contra THEN -1 ELSE 1 END *
CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
AS amount,
- a.id, a.accno, a.description, ac.transdate
+ ac.chart_id, ac.transdate
FROM acc_trans ac
JOIN account a ON ac.chart_id = a.id
- JOIN account ON ac.chart_id = account.id
)
- SELECT a.accno, a.description, a.id, b.description, b.amount,
- sum(a.amount), b.amount - sum(a.amount) AS variance
+ SELECT act.accno, act.description, act.id, b.description, b.amount,
+ coalesce(sum(a.amount), 0),
+ b.amount - coalesce(sum(a.amount), 0) AS variance
FROM budget_info bi
- JOIN agg_account a ON a.transdate BETWEEN bi.start_date and bi.end_date
JOIN budget_line b ON bi.id = b.budget_id
+ JOIN account act ON act.id = b.account_id
+LEFT JOIN agg_account a ON a.transdate BETWEEN bi.start_date and bi.end_date
+ AND a.id = b.account_id
WHERE bi.id = $1
- GROUP BY a.accno, a.description, a.id, b.description, b.amount
- ORDER BY a.accno;
+ GROUP BY act.accno, act.description, act.id, b.description, b.amount
+ ORDER BY act.accno;
$$ language sql;
COMMENT ON FUNCTION budget__variance_report(in_id int) IS
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.