From: Mark Wells <mark@freeside.biz>
Date: Wed, 1 Jul 2015 23:54:00 +0000 (-0700)
Subject: ensure all reports work correctly after #25994 changes
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=5fa112d129fa6866703c52c4fdbe0076d80efdf0

ensure all reports work correctly after #25994 changes
---

diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
index c58e63698..f136832a2 100644
--- a/httemplate/search/elements/search.html
+++ b/httemplate/search/elements/search.html
@@ -277,11 +277,17 @@ if ( $opt{'agent_virt'} ) {
                         'table'      => $query->{'table'},
                       );
 
+  # this is ridiculous, but we do have searches where $query has constraints
+  # and $count_query doesn't, or vice versa.
   if ( $query->{'extra_sql'} =~ /\bWHERE\b/i or keys %{$query->{hashref}} ) {
     $query->{'extra_sql'} .= " AND $agentnums_sql";
-    $count_query .= " AND $agentnums_sql";
   } else {
     $query->{'extra_sql'} .= " WHERE $agentnums_sql";
+  }
+
+  if ( $count_query =~ /\bWHERE\b/i ) {
+    $count_query .= " AND $agentnums_sql";
+  } else {
     $count_query .= " WHERE $agentnums_sql";
   }
 
@@ -400,48 +406,55 @@ $order_by = $cgi->param('order_by') if $cgi->param('order_by');
 my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ];
 my $rows;
 
-my @query;
-if (ref($query) eq 'HASH') {
-  @query = $query;
-
-  if ( $order_by ) {
-    if ( $query->{'order_by'} ) {
-      if ( $query->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) {
-        $query->{'order_by'} = "ORDER BY $order_by, $2";
+if ( ref $query ) {
+  my @query;
+  if (ref($query) eq 'HASH') {
+    @query = $query;
+
+    if ( $order_by ) {
+      if ( $query->{'order_by'} ) {
+        if ( $query->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) {
+          $query->{'order_by'} = "ORDER BY $order_by, $2";
+        } else {
+          warn "unparsable query order_by: ". $query->{'order_by'};
+          die "unparsable query order_by: ". $query->{'order_by'};
+        }
       } else {
-        warn "unparsable query order_by: ". $query->{'order_by'};
-        die "unparsable query order_by: ". $query->{'order_by'};
+        $query->{'order_by'} = "ORDER BY $order_by";
       }
-    } else {
-      $query->{'order_by'} = "ORDER BY $order_by";
     }
+
+    $query->{'order_by'} .= " $limit";
+
+  } elsif (ref($query) eq 'ARRAY') {
+    # do we still use this? it was for the old 477 report.
+    @query = @{ $query };
+  } else {
+    die "invalid query reference";
   }
 
-  $query->{'order_by'} .= " $limit";
+  #eval "use FS::$opt{'query'};";
+  my @param = qw( select table addl_from hashref extra_sql order_by debug );
+  $rows = [ qsearch( [ map { my $query = $_;
+                             ({ map { $_ => $query->{$_} } @param });
+                           }
+                       @query
+                     ],
+                     #'order_by' => $opt{order_by}. " ". $limit,
+                   )
+          ]; 
 
-} elsif (ref($query) eq 'ARRAY') {
-  # do we still use this? it was for the old 477 report.
-  @query = @{ $query };
-} else {
-  die "invalid query reference";
-}
+} else { # not ref $query; plain SQL (still used as of 07/2015)
 
-if ( $opt{disableable} && ! $cgi->param('showdisabled') ) {
-  #%search = ( 'disabled' => '' );
-  $opt{'query'}->{'hashref'}->{'disabled'} = '';
-  $opt{'query'}->{'extra_sql'} =~ s/^\s*WHERE/ AND/i;
-}
+  $query .= " $limit";
+  my $sth = dbh->prepare($query)
+    or die "Error preparing $query: ". dbh->errstr;
+  $sth->execute
+    or die "Error executing $query: ". $sth->errstr;
 
-#eval "use FS::$opt{'query'};";
-my @param = qw( select table addl_from hashref extra_sql order_by debug );
-$rows = [ qsearch( [ map { my $query = $_;
-                           ({ map { $_ => $query->{$_} } @param });
-                         }
-                     @query
-                   ],
-                   #'order_by' => $opt{order_by}. " ". $limit,
-                 )
-        ]; 
+  $rows = $sth->fetchall_arrayref;
+  $header ||= $sth->{NAME};
+}
 
 # run the count query to get number of rows and other totals
 my $count_sth = dbh->prepare($count_query);
diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html
index 71aa00671..54d6c2d79 100644
--- a/httemplate/search/sql.html
+++ b/httemplate/search/sql.html
@@ -2,7 +2,7 @@
                'title' => 'Query Results',
                'name'  => 'rows',
                'query' => "SELECT $sql",
-          
+               'count_query' => $count,          
 &>
 <%init>
 
@@ -12,4 +12,7 @@ die "access denied"
 my $sql = $cgi->param('sql') or errorpage('Empty query');
 $sql =~ s/;+\s*$//; #remove trailing ;
 
+my $count = $sql;
+$count =~ s/.* FROM /SELECT COUNT(*) FROM /i;
+
 </%init>