From 948648b269291eb95e85fe8c726689b5924738a0 Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Fri, 21 Dec 2007 01:56:50 +0000
Subject: Adding chart of account filter to GL reports

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1988 4979c152-3d1c-0410-bac9-87ea11338e46
---
 LedgerSMB/Form.pm | 20 ++++++++++++++++++++
 LedgerSMB/GL.pm   | 14 +++++++++++++-
 bin/gl.pl         | 12 +++++++++++-
 3 files changed, 44 insertions(+), 2 deletions(-)

diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index 496cfcbf..ff0166a1 100644
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -1816,6 +1816,26 @@ sub all_vc {
     $self->all_taxaccounts( $myconfig, $dbh, $transdate );
 }
 
+=item $form->all_accounts()
+
+Sets $form->{accounts} to all accounts.  Returns the list as well.
+Example:  my @account_list = $form->all_accounts();
+
+=cut
+
+sub all_accounts {
+    my ($self) = @_;
+    my $ref;
+    $self->{all_accounts} = [];
+    my $sth = $self->{dbh}->prepare('SELECT * FROM chart_list_all()');
+    $sth->execute || $self->dberror('SELECT * FROM chart_list_all()');
+    while ($ref = $sth->fetchrow_hashref('NAME_lc')){
+        push(@{$self->{all_accounts}}, $ref);
+    }
+    $sth->finish;
+    return @{$self->{all_accounts}};
+}
+
 =item $form->all_taxaccounts($myconfig, $dbh2[, $transdate]);
 
 Get the tax rates and numbers for all the taxes in $form->{taxaccounts}.  Does
diff --git a/LedgerSMB/GL.pm b/LedgerSMB/GL.pm
index 1c5cd97c..8979d3ca 100644
--- a/LedgerSMB/GL.pm
+++ b/LedgerSMB/GL.pm
@@ -411,6 +411,12 @@ sub all_transactions {
     my @a = ( id, transdate, reference, source, description, accno );
     my $sortorder = $form->sort_order( \@a, \%ordinal );
 
+    my $chart_id;
+    if ($form->{chart_id}){
+        $chart_id = $dbh->quote($form->{chart_id});
+    } else {
+        $chart_id = 'NULL';
+    }
     my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
 						  g.description, ac.transdate, ac.source,
 						  ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
@@ -420,7 +426,9 @@ sub all_transactions {
 					 JOIN acc_trans ac ON (g.id = ac.trans_id)
 					 JOIN chart c ON (ac.chart_id = c.id)
 				LEFT JOIN department d ON (d.id = g.department_id)
-					WHERE $glwhere
+					WHERE $glwhere 
+				              AND (ac.chart_id = $chart_id OR
+                                                   $chart_id IS NULL)
 
 					UNION ALL
 
@@ -435,6 +443,8 @@ sub all_transactions {
 					 JOIN entity e ON (a.entity_id = e.id)
 				LEFT JOIN department d ON (d.id = a.department_id)
 					WHERE $arwhere
+				              AND (ac.chart_id = $chart_id OR
+                                                   $chart_id IS NULL)
 
 				UNION ALL
 
@@ -449,6 +459,8 @@ sub all_transactions {
 					 JOIN entity e ON (a.entity_id = e.id)
 				LEFT JOIN department d ON (d.id = a.department_id)
 					WHERE $apwhere
+				              AND (ac.chart_id = $chart_id OR
+                                                   $chart_id IS NULL)
 				 ORDER BY $sortorder|;
 
     my $sth = $dbh->prepare($query);
diff --git a/bin/gl.pl b/bin/gl.pl
index 8cfe54cd..75cdec6d 100644
--- a/bin/gl.pl
+++ b/bin/gl.pl
@@ -238,6 +238,13 @@ qq|<option value="$_->{description}--$_->{id}">$_->{description}\n|;
 |;
     }
 
+    my @accounts = $form->all_accounts;
+    my $account_select = qq|<select name="chart_id" class="account>|;
+    for my $act (@accounts){
+        $account_select .= qq|<option value="$act->{id}">$act->{accno}--| .
+            qq|$act->{description}</option>|;
+    }
+
     if ( @{ $form->{all_years} } ) {
 
         # accounting years
@@ -324,7 +331,10 @@ qq|<input name="l_description" class=checkbox type=checkbox value=Y checked> |
 	<tr>
 	  <th align=right>| . $locale->text('Reference') . qq|</th>
 	  <td><input name=reference size=20></td>
-
+        </tr>
+         <tr>
+          <th align="right">| . $locale->text('Account') . qq|</th>
+          <td>$account_select</td>
 	  </tr>
 	  <tr>
 	  <th align=right>| . $locale->text('Source') . qq|</th>
-- 
cgit v1.2.3