RT#42724: Data Usage Report - System Error on Column Sort

[freeside.git] / httemplate / search / elements / search.html

diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
index 80bc89d..b6ee7b3 100644 (file)
--- a/httemplate/search/elements/search.html
+++ b/httemplate/search/elements/search.html
@@ -135,8 +135,11 @@ Example:
 
     # sort, link & display properties for fields
 
-    'sort_fields' => [], #optional list of field names or SQL expressions for
-                         # sorts
+    'sort_fields' => [], #optional list of field names or SQL expressions for sorts
+
+    'order_by_sql' => {              #to keep complex SQL expressions out of cgi order_by value,
+      'fieldname' => 'sql snippet',  #  maps fields/sort_fields values to sql snippets
+    }
    
     #listref - each item is the empty string,
     #          or a listref of link and method name to append,
@@ -222,6 +225,18 @@ if ( !$curuser->access_right('Download report data') ) {
   $type = 'html';
 }
 
+# split/map aligns here before doing anything else
+my %align = (
+  'l' => 'left',
+  'r' => 'right',
+  'c' => 'center',
+  ' ' => '',
+  '.' => '',
+);
+
+$opt{align} = [ map $align{$_}, split(//, $opt{align}) ],
+  unless !$opt{align} || ref($opt{align});
+
 if($type =~ /csv|xls/) {
   my $h = $opt{'header'};
   my @del;
@@ -265,11 +280,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";
   }
 
@@ -330,8 +351,8 @@ if ( $opt{'disableable'} ) {
       sub { shift->disabled ? 'FF0000'   : '00CC00'; };
     splice @{ $opt{'links'}  }, $pos, 0, ''
       if $opt{'links'};
-    splice @{ $opt{'link_onlicks'}  }, $pos, 0, ''
-      if $opt{'link_onlicks'};
+    splice @{ $opt{'link_onclicks'}  }, $pos, 0, ''
+      if $opt{'link_onclicks'};
   }
 
   #add show/hide disabled links
@@ -388,48 +409,61 @@ $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";
+my ($order_by_key,$order_by_desc) = ($order_by =~ /^\s*(.*?)(\s+DESC)?\s*$/i);
+$opt{'order_by_sql'} ||= {};
+$order_by_desc ||= '';
+$order_by = $opt{'order_by_sql'}{$order_by_key} . $order_by_desc
+  if $opt{'order_by_sql'}{$order_by_key};
+
+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);