SF.net SVN: ledger-smb:[2729] trunk

[..hidden..]

Revision: 2729
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2729&view=rev
Author:   einhverfr
Date:     2009-07-15 17:39:34 +0000 (Wed, 15 Jul 2009)

Log Message:
-----------
Minor GL report inconsistencies corrected

Modified Paths:
--------------
    trunk/LedgerSMB/GL.pm
    trunk/sql/modules/EndOfYear.sql

Modified: trunk/LedgerSMB/GL.pm
===================================================================
--- trunk/LedgerSMB/GL.pm	2009-07-15 01:10:18 UTC (rev 2728)
+++ trunk/LedgerSMB/GL.pm	2009-07-15 17:39:34 UTC (rev 2729)
@@ -371,15 +371,14 @@
         ) = $dbh->selectrow_array($query);
 
         if ( $form->{datefrom} ) {
-
             $query = qq|
-				SELECT SUM(ac.amount)
-				  FROM acc_trans ac
-				  JOIN chart c ON (ac.chart_id = c.id)
-				 WHERE c.accno = $accno
-				       AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );
+			SELECT account__obtain_balance(?, id) from chart
+			WHERE accno = ? |;
+            my $sth = $dbh->prepare($query);
+            $sth->execute($form->{datefrom}, $accno);
 
-            ( $form->{balance} ) = $dbh->selectrow_array($query);
+            ( $form->{balance} ) = $sth->fetchrow_array;
+            $sth->finish;
         }
     }
 

Modified: trunk/sql/modules/EndOfYear.sql
===================================================================
--- trunk/sql/modules/EndOfYear.sql	2009-07-15 01:10:18 UTC (rev 2728)
+++ trunk/sql/modules/EndOfYear.sql	2009-07-15 17:39:34 UTC (rev 2729)
@@ -138,11 +138,7 @@
 $$
 DECLARE balance numeric;
 BEGIN
-	SELECT amount INTO balance FROM account_checkpoint 
-	WHERE account_id = in_account_id AND end_date < in_trans_date
-	ORDER BY end_date desc LIMIT 1;
-
-	SELECT sum(ac.amount) + coalesce(cp.balance, 0)
+	SELECT coalesce(sum(ac.amount) + cp.amount, sum(ac.amount))
 	INTO balance
 	FROM acc_trans ac
 	JOIN (select id, approved from ar union
@@ -152,8 +148,10 @@
 		WHERE account_id = in_account_id AND end_date < in_transdate
 		ORDER BY end_date desc limit 1
 	) cp ON (cp.account_id = ac.chart_id)
-	WHERE ac.chart_id = in_account_id AND acc_trans > cp.end_date
-		and ac.approved and a.approved;
+	WHERE ac.chart_id = in_account_id 
+		AND ac.transdate > coalesce(cp.end_date, ac.transdate - '1 day'::interval)
+		and ac.approved and a.approved
+	GROUP BY cp.amount, ac.chart_id;
 
 	RETURN balance;
 END;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.