###
'title' => 'Page title',
-
+
'name_singular' => 'item', #singular name for the records returned
#OR# # (preferred, will be pluralized automatically)
'name' => 'items', #plural name for the records returned
'addl_from' => '', #'LEFT JOIN othertable USING ( key )',
'extra_sql' => '', #'AND otherstuff', #'WHERE onlystuff',
'order_by' => 'ORDER BY something',
-
+
},
# "select * from tablename";
-
+
#required unless 'query' is an SQL query string (shouldn't be...)
'count_query' => 'SELECT COUNT(*) FROM tablename',
'header' => [ '#',
'Item',
{ 'label' => 'Another Item',
-
+
},
],
'redirect_empty' => sub { my( $cgi ) = @_;
popurl(2).'view/item.html';
},
-
+
###
# optional
###
-
+
# some HTML callbacks...
'menubar' => '', #menubar arrayref
'html_init' => '', #after the header/menubar and before the pager
'html_foot' => '', #at the bottom
'html_posttotal' => '', #at the bottom
# (these three can be strings or coderefs)
-
+
'count_addl' => [], #additional count fields listref of sprintf strings or coderefs
# [ $money_char.'%.2f total paid', ],
-
+
#second (smaller) header line, currently only for HTML
'header2 => [ '#',
'Item',
{ 'label' => 'Another Item',
-
+
},
],
#listref of column footers
'footer' => [],
-
+
#disabling things
'disable_download' => '', # set true to hide the CSV/Excel download links
'disable_total' => '', # set true to hide the total"
'disable_nonefound' => '', # set true to disable the "No matching Xs found"
# message
'nohtmlheader' => '', # set true to remove the header and menu bar
-
+
#handling "disabled" fields in the records
'disableable' => 1, # set set to 1 (or column position for "disabled"
# status col) to enable if this table has a "disabled"
# insert an Agent column (query needs to be a
# qsearch hashref and header & fields need to
# be defined)cust_pkg_susp.html
+ 'agent_column' => 'COALESCE( cust_main.agentnum, prospect_main.agentnum )',
+ # Arbitrarily override the column used for agentvirt
# 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,
# or a listref of link and coderef to run and append
#one letter for each column, left/right/center/none
# or pass a listref with full values: [ 'left', 'right', 'center', '' ]
'align' => 'lrc.',
-
+
#listrefs of ( scalars or coderefs )
# currently only HTML, maybe eventually Excel too
'color' => [],
# Excel-specific listref of ( hashrefs or coderefs )
# each hashref: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Format_methods_and_Format_properties
'xls_format' => => [],
-
+
# miscellany
'download_label' => 'Download this report',
- # defaults to 'Download full results'
+ # defaults to 'Download full results'
'link_field' => 'pkgpart'
# will create internal links for each row,
# with the value of this field as the NAME attribute
&>
</%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 ) %>
)
%>
%
-% }
+% }
<%init>
my(%opt) = @_;
my $curuser = $FS::CurrentUser::CurrentUser;
+$m->comp('/elements/handle_uri_query');
+
my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|xml|select|html(-print)?)$/
? $1 : 'html' ;
$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;
$opt{disable_download} = 1
if $opt{really_disable_download};
-# split/map aligns here, so that agent_virt can add a column
-# (search-html.html will split aligns also if they aren't already split)
-my %align = (
- 'l' => 'left',
- 'r' => 'right',
- 'c' => 'center',
- ' ' => '',
- '.' => '',
-);
-$opt{align} = [ map $align{$_}, split(//, $opt{align}) ],
- unless !$opt{align} || ref($opt{align});
-
# get our queries ready
my $query = $opt{query} or die "query required";
my $count_query = $opt{count_query} or die "count_query required";
'null' => $opt{'agent_null'},
'null_right' => $opt{'agent_null_right'},
'table' => $query->{'table'},
+ 'column' => $opt{'agent_column'},
);
# this is ridiculous, but we do have searches where $query has constraints
$opt{$att} ||= [ map '', @{ $opt{'fields'} } ];
}
- splice @{ $opt{'header'} }, $pos, 0, 'Agent';
- splice @{ $opt{'align'} }, $pos, 0, 'c';
- splice @{ $opt{'style'} }, $pos, 0, '';
- splice @{ $opt{'size'} }, $pos, 0, '';
+ splice @{ $opt{'header'} }, $pos, 0, 'Agent';
+ splice @{ $opt{'align'} }, $pos, 0, 'c';
+ splice @{ $opt{'style'} }, $pos, 0, '';
+ splice @{ $opt{'size'} }, $pos, 0, '';
splice @{ $opt{'fields'} }, $pos, 0,
sub { $_[0]->agentnum ? $_[0]->agent->agent : '(global)'; };
splice @{ $opt{'color'} }, $pos, 0, '';
my $table = $query->{'table'};
- $count_query .=
+ $count_query .=
( $count_query =~ /\bWHERE\b/i ? ' AND ' : ' WHERE ' ).
"( $table.disabled = '' OR $table.disabled IS NULL )";
$opt{$att} ||= [ map '', @{ $opt{'fields'} } ];
}
- splice @{ $opt{'header'} }, $pos, 0, 'Status';
- splice @{ $opt{'align'} }, $pos, 0, 'c';
- splice @{ $opt{'style'} }, $pos, 0, 'b';
- splice @{ $opt{'size'} }, $pos, 0, '';
+ splice @{ $opt{'header'} }, $pos, 0, 'Status';
+ splice @{ $opt{'align'} }, $pos, 0, 'c';
+ splice @{ $opt{'style'} }, $pos, 0, 'b';
+ splice @{ $opt{'size'} }, $pos, 0, '';
splice @{ $opt{'fields'} }, $pos, 0,
sub { shift->disabled ? 'DISABLED' : 'Active'; };
splice @{ $opt{'color'} }, $pos, 0,
#setup some pagination things if we're in html mode
my $conf = new FS::Conf;
- $confmax = $conf->config('maxsearchrecordsperpage');
- if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) {
- $maxrecords = $1;
- } else {
- $maxrecords ||= $confmax;
- }
-
$opt{'disable_maxselect'} ||= $conf->exists('disable_maxselect');
+ unless ($opt{'disable_maxselect'}) {
+ $confmax = $conf->config('maxsearchrecordsperpage') || 100;
+ if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) {
+ $maxrecords = $1;
+ } else {
+ $maxrecords ||= $confmax;
+ }
+ }
$limit = $maxrecords ? "LIMIT $maxrecords" : '';
my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ];
my $rows;
+my ($order_by_key,$order_by_desc) = ($order_by =~ /^\s*(.*?)(\s+DESC)?\s*$/i);
+my $union_order_by;
+$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;
+ # Assemble peices of order_by information as SQL fragment,
+ # store as query->{order_by}
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";
}
}
-
$query->{'order_by'} .= " $limit";
} elsif (ref($query) eq 'ARRAY') {
- # do we still use this? it was for the old 477 report.
+ # Presented query is a UNION query, with multiple query references
@query = @{ $query };
+
+ # Assemble peices of order_by information as SQL fragment,
+ # store as $union_order_by. Omit order_by/limit from individual
+ # $query hashrefs, because this is a union query
+ #
+ # ! Currently, order_by data is only fetched from $cgi->param('order_by')
+ # ! for union queries. If it eventually needs to be passed within query
+ # ! hashrefs, or as mason template options, would need implemented
+ $union_order_by = " ORDER BY $order_by " if $order_by;
+ $union_order_by .= " $limit " if $limit;
+
} else {
- die "invalid query reference";
+ die "invalid query reference ($query)";
}
#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,
- )
- ];
+ if ($opt{classname_from_column}) {
+ # Perform a union of multiple queries, while using the
+ # classname_from_column qsearch union option
+
+ # Constrain hashkeys for each query from @param
+ @query = map{
+ my $query = $_;
+ my $new_query = {};
+ $new_query->{$_} = $query->{$_} for @param;
+ $new_query;
+ } @query;
+
+ $rows = [
+ qsearch(
+ \@query,
+ order_by => $union_order_by,
+ classname_from_column => 1,
+ )
+ ];
+
+ } else {
+ # default perform a query with qsearch
+ $rows = [ qsearch( [ map { my $query = $_;
+ ({ map { $_ => $query->{$_} } @param });
+ }
+ @query
+ ],
+ #'order_by' => $opt{order_by}. " ". $limit,
+ )
+ ];
+ }
} else { # not ref $query; plain SQL (still used as of 07/2015)
$query .= " $limit";