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

SF.net SVN: ledger-smb:[3875] addons/1.3/budgetting/trunk



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.