Fixing trial balance to filter all areas of unapproved transactions

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2035 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 82 insertions, 2 deletions

diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm
index 389550ae..fa10a985 100644
--- a/LedgerSMB/CA.pm
+++ b/LedgerSMB/CA.pm
@@ -43,10 +43,22 @@ sub all_accounts {
     # connect to database
     my $dbh = $form->{dbh};
 
+    my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
+
     my $query = qq|
 		   SELECT accno, SUM(acc_trans.amount) AS amount
-		     FROM chart, acc_trans
-		    WHERE chart.id = acc_trans.chart_id
+		     FROM chart
+		     JOIN acc_trans ON (chart.id = acc_trans.chart_id)
+		     JOIN transactions ON (acc_trans.trans_id = transactions.id)
+                     JOIN (SELECT id, approved, 'ap' AS tablename FROM ap 
+		          UNION
+                         SELECT id, approved, 'ar' as tablename FROM ar
+			 UNION 
+                         SELECT id, approved, 'gl' as tablename FROM gl
+		    ) g ON (g.id = acc_trans.trans_id 
+				AND transactions.table_name = g.tablename)
+		    WHERE ($approved OR acc_trans.approved)
+			  AND (g.approved OR $approved)
 		 GROUP BY accno|;
 
     my $sth = $dbh->prepare($query);
@@ -108,6 +120,7 @@ sub all_transactions {
 
     # connect to database
     my $dbh = $form->{dbh};
+    my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
 
     # get chart_id
     my $query = qq|
@@ -246,6 +259,9 @@ sub all_transactions {
 							       AND 
 							       a.department_id 
 							       = ?
+							       AND ($approved OR
+							           (a.approved
+							       AND ac.approved))
 								 $project |;
 
                         push @queryargs, $form->{gifi_accno}, $form->{fromdate},
@@ -272,6 +288,9 @@ sub all_transactions {
 							       AND 
 							       a.department_id 
 							       = ?
+							       AND ($approved OR
+							           (a.approved
+							       AND ac.approved))
 							       $project |;
                         push @queryargs, $form->{accno}, $form->{fromdate},
                           $department_id;
@@ -292,8 +311,27 @@ sub all_transactions {
 						  FROM acc_trans ac
 						  JOIN chart c ON 
 						       (ac.chart_id = c.id)
+						  JOIN transactions t ON 
+						       (t.id = ac.trans_id)
+						  JOIN (SELECT id, approved
+						               'ar' AS tablename
+						          FROM ar
+						        UNION 
+						        SELECT id, approved, 
+							       'ap' AS tablename
+						          FROM ap
+						        UNION 
+						        SELECT id, approved, 
+							       'gl' AS tablename
+						          FROM gl
+						        ) a ON 
+						       (a.tablename = t.table_name
+						       AND a.id = t.id)
 						 WHERE c.gifi_accno = ?
 						       AND ac.transdate < ?
+						       AND ($approved OR
+							   (a.approved
+							       AND ac.approved))
 						$project |;
                     @queryargs = ( $form->{gifi_accno}, $form->{fromdate} );
                     if ($p_id) {
@@ -306,8 +344,27 @@ sub all_transactions {
 						  FROM acc_trans ac
 						  JOIN chart c 
 						       ON (ac.chart_id = c.id)
+						  JOIN transactions t ON 
+						       (t.id = ac.trans_id)
+						  JOIN (SELECT id, approved, 
+						               'ar' AS tablename
+						          FROM ar
+						        UNION 
+						        SELECT id, approved, 
+							       'ap' AS tablename
+						          FROM ap
+						        UNION 
+						        SELECT id, approved, 
+							       'gl' AS tablename
+						          FROM gl
+						        ) a ON 
+						       (a.tablename = t.table_name
+						       AND a.id = t.id)
 						 WHERE c.accno = ?
 						       AND ac.transdate < ?
+						       AND ($approved OR
+							   (a.approved
+							       AND ac.approved))
 						$project |;
                     @queryargs = ( $form->{accno}, $form->{fromdate} );
                     if ($p_id) {
@@ -344,6 +401,7 @@ sub all_transactions {
 			$fromdate_where
 			$todate_where
 			$dpt_where
+			       AND ($approved OR (a.approved AND ac.approved))
 			$project|;
         if ($d_id) {
             push @queryargs, $d_id;
@@ -373,6 +431,7 @@ sub all_transactions {
 			  JOIN entity e ON (a.entity_id = e.id)
 			$dpt_join
 			 WHERE ac.chart_id = ?
+			       AND ($approved OR (a.approved AND ac.approved))
 			$fromdate_where
 			$todate_where
 			$dpt_where
@@ -406,6 +465,7 @@ sub all_transactions {
 			  JOIN entity e ON (a.entity_id = e.id)
 			$dpt_join
 			 WHERE ac.chart_id = ?
+			       AND ($approved OR (a.approved AND ac.approved))
 			$fromdate_where
 			$todate_where
 			$dpt_where
@@ -440,6 +500,7 @@ sub all_transactions {
 				  JOIN acc_trans ac ON (ac.chart_id = c.id)
 				 WHERE ac.amount >= 0
 				   AND (c.link = 'AR' OR c.link = 'AP')
+				   AND ac.approved OR $approved
 				   AND ac.trans_id = ?|;
 
     my $dr = $dbh->prepare($query) || $form->dberror($query);
@@ -449,6 +510,7 @@ sub all_transactions {
 				  JOIN acc_trans ac ON (ac.chart_id = c.id)
 				 WHERE ac.amount < 0
 				   AND (c.link = 'AR' OR c.link = 'AP')
+				   AND ac.approved OR $approved
 				   AND ac.trans_id = ?|;
 
     my $cr = $dbh->prepare($query) || $form->dberror($query);
diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm
index b51d7290..ebca11c3 100644
--- a/LedgerSMB/RP.pm
+++ b/LedgerSMB/RP.pm
@@ -1213,10 +1213,19 @@ sub get_accounts {
 				  SELECT c.accno, sum(ac.amount) AS amount,
 				         c.description, c.category
 				    FROM acc_trans ac
+				    JOIN (SELECT id, approved FROM ar
+				          UNION
+                                          SELECT id, approved FROM ap
+                                          UNION
+                                          SELECT id, approved FROM gl
+                                          ) g ON (ac.trans_id = g.id)
 				    JOIN chart c ON (c.id = ac.chart_id)
 				         $dpt_join
 				   WHERE $where $ywhere $dpt_where $category
 				         $project
+					  AND ($approved IS NULL OR
+						$approved = 
+					        (ac.approved AND g.approved))
 				GROUP BY c.accno, c.description, c.category|;
 
             if ($excludeyearend) {
@@ -1304,6 +1313,7 @@ sub trial_balance {
     my ( $self, $myconfig, $form ) = @_;
 
     my $dbh = $form->{dbh};
+    my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
 
     my ( $query, $sth, $ref );
     my %balance = ();
@@ -1353,6 +1363,7 @@ sub trial_balance {
 				         $dpt_join
 				   WHERE ac.transdate < '$form->{fromdate}'
 				         $dpt_where $project
+				         AND ($approved OR ac.approved)
 				GROUP BY g.accno, c.category, g.description, 
 				         c.contra|;
 
@@ -1368,6 +1379,7 @@ sub trial_balance {
 				         $dpt_join
 				   WHERE ac.transdate < '$form->{fromdate}'
 				         $dpt_where $project
+				         AND ($approved OR ac.approved)
 				GROUP BY c.accno, c.category, c.description, 
 				         c.contra|;
 
@@ -1447,6 +1459,7 @@ sub trial_balance {
 			    JOIN gifi g ON (c.gifi_accno = g.accno)
 			         $dpt_join
 			   WHERE $where $dpt_where $project
+				         AND ($approved OR ac.approved)
 			GROUP BY g.accno, g.description, c.category, c.contra
 			ORDER BY accno|;
 
@@ -1460,6 +1473,7 @@ sub trial_balance {
 			    JOIN chart c ON (c.id = ac.chart_id)
 			         $dpt_join
 			   WHERE $where $dpt_where $project
+				         AND $approved OR (ac.approved)
 			GROUP BY c.accno, c.description, c.category, c.contra
 			ORDER BY accno|;
 
@@ -1474,11 +1488,13 @@ sub trial_balance {
 		          JOIN chart c ON (c.id = ac.chart_id)
 		               $dpt_join
 		          WHERE $where $dpt_where $project AND ac.amount < 0
+				         AND ($approved OR ac.approved)
 		                 AND c.accno = ?) AS debit,
 		       (SELECT SUM(ac.amount) FROM acc_trans ac
 		          JOIN chart c ON (c.id = ac.chart_id)
 		               $dpt_join
 		         WHERE $where $dpt_where $project AND ac.amount > 0
+				         AND ($approved OR ac.approved)
 		               AND c.accno = ?) AS credit |;
 
     if ( $form->{accounttype} eq 'gifi' ) {
@@ -1489,6 +1505,7 @@ sub trial_balance {
 		          JOIN chart c ON (c.id = ac.chart_id)
 		               $dpt_join
 		         WHERE $where $dpt_where $project AND ac.amount < 0
+				         AND ($approved OR ac.approved)
 		               AND c.gifi_accno = ?) AS debit,
 		
 		       (SELECT SUM(ac.amount)
@@ -1496,6 +1513,7 @@ sub trial_balance {
 		          JOIN chart c ON (c.id = ac.chart_id)
 		               $dpt_join
 		         WHERE $where $dpt_where $project AND ac.amount > 0
+				         AND ($approved OR ac.approved)
 		               AND c.gifi_accno = ?) AS credit|;
 
     }