# 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 ) %>
$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;
'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";
}
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
#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 @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);