X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Felements%2Fsearch.html;h=60548e28edffb2bfbc6287c4f66e0e01625e48ea;hp=f136832a24b4c8c2df244076ccc99e98c5f69cc1;hb=fc263806f5e475559a0c4cfdb70a5f1cefe0ffa3;hpb=379c06ccd4b984bfa8130d98f862799239a8d00c diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index f136832a2..60548e28e 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -9,14 +9,14 @@ Example: ### '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 # (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', @@ -30,11 +30,11 @@ Example: '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...) + #required (now even if 'query' is an SQL query string) 'count_query' => 'SELECT COUNT(*) FROM tablename', ### @@ -47,7 +47,7 @@ Example: 'header' => [ '#', 'Item', { 'label' => 'Another Item', - + }, ], @@ -70,11 +70,11 @@ Example: '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 @@ -85,21 +85,21 @@ Example: '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" @@ -107,7 +107,7 @@ Example: '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" @@ -119,7 +119,11 @@ Example: #(query needs to be a qsearch hashref and # header & fields need to be defined) - #handling agent virtualization + # Agent Virtualization parameters: + # In this context, only available if your selected table has agentnum. + # You must also include agentnum as a SELECT column in your SQL query, + # or experience non-obvious problems + # 'agent_virt' => 1, # set true if this search should be # agent-virtualized 'agent_null' => 1, # set true to view global records always @@ -131,13 +135,16 @@ Example: '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, # or a listref of link and coderef to run and append @@ -152,7 +159,7 @@ Example: #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' => [], @@ -163,11 +170,11 @@ Example: # 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 @@ -176,6 +183,7 @@ Example: &> +% # if changing this, also update saved search behavior to match! % if ( $type eq 'csv' ) { % <% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %> @@ -190,6 +198,10 @@ Example: % <% include('search-xml.html', rows=>$rows, opt=>\%opt ) %> % +% } elsif ( $type eq 'deposit_slip' ) { +% +<% include('search-deposit_slip.html', rows=>$rows, opt=>\%opt ) %> +% % } else { % <% include('search-html.html', @@ -206,7 +218,7 @@ Example: ) %> % -% } +% } <%init> my(%opt) = @_; @@ -214,14 +226,29 @@ my(%opt) = @_; my $curuser = $FS::CurrentUser::CurrentUser; -my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|xml|select|html(-print)?)$/ - ? $1 : 'html' ; +$m->comp('/elements/handle_uri_query'); + +my $type = $cgi->param('_type') + =~ /^(csv|\w*\.xls|xml|select|html(-print)?|deposit_slip)$/ + ? $1 : 'html' ; if ( !$curuser->access_right('Download report data') ) { $opt{'disable_download'} = 1; $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; @@ -245,18 +272,6 @@ $opt{disable_download} = 0 $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"; @@ -300,10 +315,10 @@ if ( $opt{'agent_virt'} ) { $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, ''; @@ -325,7 +340,7 @@ if ( $opt{'disableable'} ) { my $table = $query->{'table'}; - $count_query .= + $count_query .= ( $count_query =~ /\bWHERE\b/i ? ' AND ' : ' WHERE ' ). "( $table.disabled = '' OR $table.disabled IS NULL )"; @@ -338,18 +353,18 @@ if ( $opt{'disableable'} ) { $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, 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 @@ -379,14 +394,15 @@ unless ( $type =~ /^(csv|xml|\w*.xls)$/) { #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" : ''; @@ -406,11 +422,20 @@ $order_by = $cgi->param('order_by') if $cgi->param('order_by'); 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 ) { @@ -423,27 +448,59 @@ if ( ref $query ) { $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";