X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Felements%2Fsearch.html;h=b6ee7b3735e69716e194fe0647b5db925e3f17d1;hp=5a16a22fead78d0a0e9cf8283b12eb739d213e5b;hb=d406868577ff4df701bc3fe6ef9c0ab6f0e72999;hpb=431c9ca4fab151862bd24322bf8a1f9252fb38fc diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 5a16a22fe..b6ee7b373 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -43,7 +43,7 @@ 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', @@ -52,7 +52,7 @@ Example: ], #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; }, @@ -106,7 +106,8 @@ Example: '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" @@ -130,18 +131,21 @@ 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) + # be defined)cust_pkg_susp.html # 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 @@ -167,6 +171,11 @@ Example: # miscellany 'download_label' => 'Download this report', # 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. &> @@ -195,6 +204,7 @@ Example: confmax => $confmax, maxrecords => $maxrecords, offset => $offset, + totals => $totals, opt => \%opt ) %> @@ -215,6 +225,7 @@ if ( !$curuser->access_right('Download report data') ) { $type = 'html'; } +# split/map aligns here before doing anything else my %align = ( 'l' => 'left', 'r' => 'right', @@ -222,6 +233,7 @@ my %align = ( ' ' => '', '.' => '', ); + $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], unless !$opt{align} || ref($opt{align}); @@ -248,6 +260,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 +277,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 ) { @@ -299,12 +323,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; - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). - "( disabled = '' OR disabled IS NULL )"; + my $table = $query->{'table'}; + + $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 @@ -325,8 +351,8 @@ if ( $opt{'disableable'} ) { 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,18 +374,8 @@ 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' ) { @@ -385,67 +401,76 @@ 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); +$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; 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"; } } - } elsif (ref($opt{query}) eq 'ARRAY') { - @query = @{ $opt{query} }; + $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"; } - 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 ); + 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, + #'order_by' => $opt{order_by}. " ". $limit, ) ]; -} else { - my $sth = dbh->prepare("$opt{'query'} $limit") - or die "Error preparing $opt{'query'}: ". dbh->errstr; + +} else { # not ref $query; plain SQL (still used as of 07/2015) + + $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'}; +