X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Felements%2Fsearch.html;h=60548e28edffb2bfbc6287c4f66e0e01625e48ea;hp=5a16a22fead78d0a0e9cf8283b12eb739d213e5b;hb=fc263806f5e475559a0c4cfdb70a5f1cefe0ffa3;hpb=a60615bf7bde77aa2b9faf3fc268c149eecdb5ab diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 5a16a22fe..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', ### @@ -43,16 +43,16 @@ Example: #listref of column labels, #recommended unless 'query' is an SQL query string - # (if not specified the database column names will be used) + # (if not specified the database column names will be used) (XXX this is not currently working either) 'header' => [ '#', 'Item', { 'label' => 'Another Item', - + }, ], #listref - each item is a literal column name (or method) or coderef - #if not specified all columns will be shown + #if not specified all columns will be shown (XXX this is not currently working?) 'fields' => [ 'column', sub { my $row = shift; $row->column; }, @@ -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,28 +85,29 @@ 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" 'disable_maxselect' => '', # set true to disable record/page selection '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" @@ -118,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 @@ -134,14 +139,17 @@ Example: # 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 # or a coderef that returns such a listref - 'links' => [],` + 'links' => [], #listref - each item is the empty string, # or a string onClick handler for the corresponding link @@ -151,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' => [], @@ -162,14 +170,20 @@ 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 + # If this is a coderef, will evaluate it, passing the + # row as an argument, and use the result as the NAME. &> +% # if changing this, also update saved search behavior to match! % if ( $type eq 'csv' ) { % <% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %> @@ -184,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', @@ -195,11 +213,12 @@ Example: confmax => $confmax, maxrecords => $maxrecords, offset => $offset, + totals => $totals, opt => \%opt ) %> % -% } +% } <%init> my(%opt) = @_; @@ -207,14 +226,18 @@ 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', @@ -222,6 +245,7 @@ my %align = ( ' ' => '', '.' => '', ); + $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], unless !$opt{align} || ref($opt{align}); @@ -248,6 +272,12 @@ $opt{disable_download} = 0 $opt{disable_download} = 1 if $opt{really_disable_download}; +# get our queries ready +my $query = $opt{query} or die "query required"; +my $count_query = $opt{count_query} or die "count_query required"; +# there was a default count_query but it hasn't worked in about ten years + +# set up agent restriction my @link_agentnums = (); my $null_link = ''; if ( $opt{'agent_virt'} ) { @@ -259,16 +289,22 @@ if ( $opt{'agent_virt'} ) { my $agentnums_sql = $curuser->agentnums_sql( 'null' => $opt{'agent_null'}, 'null_right' => $opt{'agent_null_right'}, - 'table' => $opt{'query'}{'table'}, + 'table' => $query->{'table'}, ); - $opt{'query'}{'extra_sql'} .= - ( $opt{'query'}{'extra_sql'} =~ /WHERE/i || keys %{$opt{'query'}{'hashref'}} - ? ' AND ' - : ' WHERE ' ). $agentnums_sql; + # 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"; + } else { + $query->{'extra_sql'} .= " WHERE $agentnums_sql"; + } - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). $agentnums_sql; + if ( $count_query =~ /\bWHERE\b/i ) { + $count_query .= " AND $agentnums_sql"; + } else { + $count_query .= " WHERE $agentnums_sql"; + } if ( $opt{'agent_pos'} || $opt{'agent_pos'} eq '0' and scalar($curuser->agentnums) > 1 ) { @@ -279,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, ''; @@ -299,12 +335,14 @@ if ( $opt{'disableable'} ) { unless ( $cgi->param('showdisabled') ) { #modify searches - $opt{'query'}{'hashref'}{'disabled'} = ''; - $opt{'query'}{'extra_sql'} =~ s/^\s*WHERE/ AND/i; + $query->{'hashref'}{'disabled'} = ''; + $query->{'extra_sql'} =~ s/^\s*\bWHERE\b/ AND/i; + + my $table = $query->{'table'}; - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). - "( disabled = '' OR disabled IS NULL )"; + $count_query .= + ( $count_query =~ /\bWHERE\b/i ? ' AND ' : ' WHERE ' ). + "( $table.disabled = '' OR $table.disabled IS NULL )"; } elsif ( $opt{'disabled_statuspos'} || $opt{'disabled_statuspos'} eq '0' ) { #add status column @@ -315,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 @@ -348,32 +386,23 @@ if ( $opt{'disableable'} ) { my $limit = ''; my($confmax, $maxrecords, $offset ); -unless ( $type =~ /^(csv|\w*.xls)$/) { +unless ( $type =~ /^(csv|xml|\w*.xls)$/) { # html mode - unless (exists($opt{count_query}) && length($opt{count_query})) { - ( $opt{count_query} = $opt{query} ) =~ - s/^\s*SELECT\s*(.*?)\s+FROM\s/SELECT COUNT(*) FROM /i; #silly vim:/ - } - - if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { - $opt{count_query} .= - ( ( $opt{count_query} =~ /WHERE/i ) ? ' AND ' : ' WHERE ' ). - "( disabled = '' OR disabled IS NULL )"; - } unless ( $type eq 'html-print' ) { #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" : ''; @@ -385,67 +414,111 @@ unless ( $type =~ /^(csv|\w*.xls)$/) { } #order by override -my $order_by = ''; -#if ( $cgi->param('order_by') =~ /^([\w\, ]+)$/ ) { -# $order_by = $1; -#} +my $order_by = $opt{order_by} || ''; $order_by = $cgi->param('order_by') if $cgi->param('order_by'); # run the query my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; -if ( ref($opt{query}) ) { +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($opt{query}) eq 'HASH') { - @query = ( $opt{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 ( $opt{query}->{'order_by'} ) { - if ( $opt{query}->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) { - $opt{query}->{'order_by'} = "ORDER BY $order_by, $2"; + 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: ". $opt{query}->{'order_by'}; - die "unparsable query order_by: ". $opt{query}->{'order_by'}; + warn "unparsable query order_by: ". $query->{'order_by'}; + die "unparsable query order_by: ". $query->{'order_by'}; } } else { - $opt{query}->{'order_by'} = "ORDER BY $order_by"; + $query->{'order_by'} = "ORDER BY $order_by"; } } + $query->{'order_by'} .= " $limit"; + + } elsif (ref($query) eq 'ARRAY') { + # 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; - } elsif (ref($opt{query}) eq 'ARRAY') { - @query = @{ $opt{query} }; } else { - die "invalid query reference"; + die "invalid query reference ($query)"; } - if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { - #%search = ( 'disabled' => '' ); - $opt{'query'}->{'hashref'}->{'disabled'} = ''; - $opt{'query'}->{'extra_sql'} =~ s/^\s*WHERE/ AND/i; + #eval "use FS::$opt{'query'};"; + my @param = qw( select table addl_from hashref extra_sql order_by debug ); + 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) - #eval "use FS::$opt{'query'};"; - my @param = qw( select table addl_from hashref extra_sql order_by ); - $rows = [ qsearch( [ map { my $query = $_; - ({ map { $_ => $query->{$_} } @param }); - } - @query - ], - 'order_by' => $opt{order_by}. " ". $limit, - ) - ]; -} else { - my $sth = dbh->prepare("$opt{'query'} $limit") - or die "Error preparing $opt{'query'}: ". dbh->errstr; + $query .= " $limit"; + my $sth = dbh->prepare($query) + or die "Error preparing $query: ". dbh->errstr; $sth->execute - or die "Error executing $opt{'query'}: ". $sth->errstr; + or die "Error executing $query: ". $sth->errstr; - #can get # of rows without fetching them all? $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); +$count_sth->execute + or die "Error executing '$count_query': ".$count_sth->errstr; +my $totals = $count_sth->fetchrow_arrayref; + +push @$rows, $opt{'footer_data'} if $opt{'footer_data'}; +