[Astpp-commit] SF.net SVN: astpp:[2277] trunk/modules/ASTPP/lib/ASTPP.pm

[<dar...@us...>]

Revision: 2277
          http://astpp.svn.sourceforge.net/astpp/?rev=2277&view=rev
Author:   darrenkw
Date:     2009-10-06 03:40:09 +0000 (Tue, 06 Oct 2009)

Log Message:
-----------
Speed up the balance calculating SQL.  This should cut the time taken to about 1/3rd.

Modified Paths:
--------------
    trunk/modules/ASTPP/lib/ASTPP.pm

Modified: trunk/modules/ASTPP/lib/ASTPP.pm
===================================================================
--- trunk/modules/ASTPP/lib/ASTPP.pm	2009-10-06 03:39:24 UTC (rev 2276)
+++ trunk/modules/ASTPP/lib/ASTPP.pm	2009-10-06 03:40:09 UTC (rev 2277)
@@ -1429,35 +1429,21 @@
 sub accountbalance() {
 	my ($self, %arg) = @_;
     my ( $tmp, $sql, $row, $debit, $credit, $balance, $posted_balance );
-    $tmp =
-	"SELECT SUM(debit) FROM cdrs WHERE cardnum= "
-      . $self->{_astpp_db}->quote($arg{account})
-      . " AND status NOT IN (1, 2)";
-    $sql = $self->{_astpp_db}->prepare($tmp);
+    $tmp = "SELECT SUM(cdrs.debit),SUM(cdrs.credit),accounts.balance "
+	. " from cdrs,accounts WHERE cardnum = "
+        . $self->{_astpp_db}->quote($arg{account})
+	. " AND cdrs.status NOT IN(1,2)";
     $sql->execute;
-    $row   = $sql->fetchrow_hashref;
-    $debit = $row->{"SUM(debit)"};
+    $row = $sql->fetchrow_hashref;
+    $credit = $row->{"SUM(cdrs.credit)"};
+    $debit = $row->{"SUM(cdrs.debit)"};
+    $posted_balance = $row->{"balance"};
     $sql->finish;
-    $tmp =
-	"SELECT SUM(credit) FROM cdrs WHERE cardnum= "
-      . $self->{_astpp_db}->quote($arg{account})
-      . " AND status NOT IN (1, 2)";
-    $sql = $self->{_astpp_db}->prepare($tmp);
-    $sql->execute;
-    $row    = $sql->fetchrow_hashref;
-    $credit = $row->{"SUM(credit)"};
-    $sql->finish;
-    $tmp =
-      "SELECT * FROM accounts WHERE number = " . $self->{_astpp_db}->quote($arg{account});
-    $sql = $self->{_astpp_db}->prepare($tmp);
-    $sql->execute;
-    $row            = $sql->fetchrow_hashref;
-    $posted_balance = $row->{balance};
-    $sql->finish;
     if ( !$credit )         { $credit         = 0; }
     if ( !$debit )          { $debit          = 0; }
-    if ( !$posted_balance ) { $posted_balance = 0; }
+    if ( !$posted_balance )          { $posted_balance         = 0; }
     $balance = ( $debit - $credit + $posted_balance );
+    $sql->finish;
     return $balance;
 }
 
@@ -1567,7 +1553,7 @@
 	  . $self->{_astpp_db}->quote($arg{destination})
 	  . " RLIKE pattern AND pricelist = "
 	  . $self->{_astpp_db}->quote($arg{pricelist})
-	  . " ORDER BY LENGTH(pattern) DESC";
+	  . " ORDER BY LENGTH(pattern) DESC LIMIT 1";
     print STDERR "$tmp\n";
     $sql =
       $self->{_astpp_db}->prepare($tmp);


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