# (deprecated, will be singularlized
# simplisticly)
- #literal SQL query string (deprecated?) or qsearch hashref or arrayref
+ #literal SQL query string (corner cases only) or qsearch hashref or arrayref
#of qsearch hashrefs for a union of qsearches
'query' => {
'table' => 'tablename',
},
# "select * from tablename";
- #required unless 'query' is an SQL query string (shouldn't be...)
+ #required (now even if 'query' is an SQL query string)
'count_query' => 'SELECT COUNT(*) FROM tablename',
###
'agent_pos' => 3, # optional position (starting from 0) to
# insert an Agent column (query needs to be a
# qsearch hashref and header & fields need to
- # be defined)cust_pkg_susp.html
+ # be defined)
# 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,
&>
</%doc>
+% # if changing this, also update saved search behavior to match!
% if ( $type eq 'csv' ) {
%
<% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %>
#setup some pagination things if we're in html mode
my $conf = new FS::Conf;
- $confmax = $conf->config('maxsearchrecordsperpage');
+ $confmax = $conf->config('maxsearchrecordsperpage') || 100;
if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) {
$maxrecords = $1;
} else {
my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ];
my $rows;
+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') {