X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Felements%2Fsearch.html;h=1e9ce5cdeb6c35ef356f2d42ef7ca6690559c07b;hp=5058a1ac7f8c0975453db5b2b17b839a80e22a45;hb=d2e3299a0af2b5122d73b9f3ca0814a36919b1da;hpb=f2dca165c20e75f7a6048d048f353b56d9f2d621 diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 5058a1ac7..1e9ce5cde 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -9,7 +9,7 @@ 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 @@ -30,11 +30,16 @@ Example: 'addl_from' => '', #'LEFT JOIN othertable USING ( key )', 'extra_sql' => '', #'AND otherstuff', #'WHERE onlystuff', 'order_by' => 'ORDER BY something', - + }, # "select * from tablename"; +<<<<<<< HEAD #required (now even if 'query' is an SQL query string) +======= + + #required unless 'query' is an SQL query string (shouldn't be...) +>>>>>>> 95144265eeb3ecd13b16708dbdd75dd3701f92ad 'count_query' => 'SELECT COUNT(*) FROM tablename', ### @@ -47,7 +52,7 @@ Example: 'header' => [ '#', 'Item', { 'label' => 'Another Item', - + }, ], @@ -70,11 +75,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 +90,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 +112,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" @@ -140,7 +145,7 @@ Example: '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 @@ -155,7 +160,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' => [], @@ -166,11 +171,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 @@ -210,7 +215,7 @@ Example: ) %> % -% } +% } <%init> my(%opt) = @_; @@ -304,10 +309,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, ''; @@ -329,7 +334,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 )"; @@ -342,10 +347,10 @@ 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, @@ -411,6 +416,7 @@ 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 @@ -421,6 +427,8 @@ if ( ref $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 ) { @@ -433,27 +441,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";