X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill.cgi;h=cb358f9e99ad20601ea3485747983630bef48c34;hb=6e80f8889a13c47343d81de796f2a7ed4f036803;hp=d6492fe49e3cb2923931e3c03567267fe44bbd80;hpb=d83a273f3ac71431f5b007bb83fb55d7c83eae5c;p=freeside.git

diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi
index d6492fe49..cb358f9e9 100755
--- a/httemplate/search/cust_bill.cgi
+++ b/httemplate/search/cust_bill.cgi
@@ -16,11 +16,11 @@ my $total;
 my(@cust_bill, $sortby);
 if ( $cgi->keywords ) {
   my($query) = $cgi->keywords;
-  my $open_sql =
-    "having 0 != charged - coalesce(sum(cust_bill_pay.amount),0)
-                         - coalesce(sum(cust_credit_bill.amount),0)";
-  my $having = '';
-  my $where = '';
+  my $owed = "charged - ( select coalesce(sum(amount),0) from cust_bill_pay
+                          where cust_bill_pay.invnum = cust_bill.invnum )
+                      - ( select coalesce(sum(amount),0) from cust_credit_bill
+                          where cust_credit_bill.invnum = cust_bill.invnum )";
+  my @where;
   if ( $query eq 'invnum' ) {
     $sortby = \*invnum_sort;
     #@cust_bill = qsearch('cust_bill', {} );
@@ -33,46 +33,42 @@ if ( $cgi->keywords ) {
   } elsif ( $query eq 'OPEN_invnum' ) {
     $sortby = \*invnum_sort;
     #@cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} );
-    $having = $open_sql;
+    push @where, "0 != $owed";
   } elsif ( $query eq 'OPEN_date' ) {
     $sortby = \*date_sort;
     #@cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} );
-    $having = $open_sql;
+    push @where, "0 != $owed";
   } elsif ( $query eq 'OPEN_custnum' ) {
     $sortby = \*custnum_sort;
     #@cust_bill = grep $_->owed != 0, qsearch('cust_bill', {} );
-    $having = $open_sql;
+    push @where, "0 != $owed";
   } elsif ( $query =~ /^OPEN(\d+)_invnum$/ ) {
     my $open = $1 * 86400;
     $sortby = \*invnum_sort;
     #@cust_bill =
     #  grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} );
-    $having = $open_sql;
-    $where = "where cust_bill._date < ". (time-$open);
+    push @where, "0 != $owed".
+                 "cust_bill._date < ". (time-$open);
   } elsif ( $query =~ /^OPEN(\d+)_date$/ ) {
     my $open = $1 * 86400;
     $sortby = \*date_sort;
     #@cust_bill =
     #  grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} );
-    $having = $open_sql;
-    $where = "where cust_bill._date < ". (time-$open);
+    push @where, "0 != $owed".
+                 "cust_bill._date < ". (time-$open);
+
   } elsif ( $query =~ /^OPEN(\d+)_custnum$/ ) {
     my $open = $1 * 86400;
     $sortby = \*custnum_sort;
     #@cust_bill =
     #  grep $_->owed != 0 && $_->_date < time - $open, qsearch('cust_bill', {} );
-    $having = $open_sql;
-    $where = "where cust_bill._date < ". (time-$open);
+    push @where, "0 != $owed".
+                 "cust_bill._date < ". (time-$open);
   } else {
     die "unknown query string $query";
   }
 
-  my $extra_sql = "
-    left outer join cust_bill_pay using ( invnum )
-    left outer join cust_credit_bill using ( invnum )
-    $where
-    group by ". join(', ', map "cust_bill.$_", fields('cust_bill') ). ' '.
-    $having;
+  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
 
   my $statement = "SELECT COUNT(*) FROM cust_bill $extra_sql";
   my $sth = dbh->prepare($statement) or die dbh->errstr. " doing $statement";
@@ -83,9 +79,7 @@ if ( $cgi->keywords ) {
   @cust_bill = qsearch(
     'cust_bill',
     {},
-    'cust_bill.*,
-     charged - coalesce(sum(cust_bill_pay.amount),0)
-              - coalesce(sum(cust_credit_bill.amount),0) as owed',
+    "cust_bill.*, $owed as owed",
     "$extra_sql $orderby $limit"
   );
 } else {
@@ -97,7 +91,8 @@ if ( $cgi->keywords ) {
 }
 
 #if ( scalar(@cust_bill) == 1 ) {
-if ( $total == 1 ) {
+if ( scalar(@cust_bill) == 1 && $total == 1) {
+#if ( $total == 1 ) {
   my $invnum = $cust_bill[0]->invnum;
   print $cgi->redirect(popurl(2). "view/cust_bill.cgi?$invnum");  #redirect
 } elsif ( scalar(@cust_bill) == 0 ) {