summaryrefslogtreecommitdiff
path: root/httemplate/search/elements
diff options
context:
space:
mode:
Diffstat (limited to 'httemplate/search/elements')
-rw-r--r--httemplate/search/elements/cust_main_dayranges.html268
-rwxr-xr-xhttemplate/search/elements/cust_pay_or_refund.html345
-rw-r--r--httemplate/search/elements/metasearch.html71
-rw-r--r--httemplate/search/elements/search-csv.html54
-rw-r--r--httemplate/search/elements/search-html.html477
-rw-r--r--httemplate/search/elements/search-xls.html85
-rw-r--r--httemplate/search/elements/search-xml.html88
-rw-r--r--httemplate/search/elements/search.html415
8 files changed, 1803 insertions, 0 deletions
diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html
new file mode 100644
index 000000000..c53e68016
--- /dev/null
+++ b/httemplate/search/elements/cust_main_dayranges.html
@@ -0,0 +1,268 @@
+<%doc>
+
+Example:
+
+ include( 'elements/cust_main_dayranges.html',
+ 'title' => 'Accounts Receivable Aging Summary',
+ 'range_sub' => $mysub,
+ )
+
+ my $mysub = sub {
+ my( $start, $end ) = @_;
+
+ "SQL EXPRESSION BASED ON $start AND $end";
+ };
+
+</%doc>
+<% include( 'search.html',
+ 'name' => 'customers',
+ 'query' => $sql_query,
+ 'count_query' => $count_sql,
+ 'header' => [
+ FS::UI::Web::cust_header(),
+ '0-30',
+ '30-60',
+ '60-90',
+ '90+',
+ 'Total',
+ @pay_head,
+ ],
+ 'footer' => [
+ 'Total',
+ ( map '',
+ ( 1 ..
+ scalar(FS::UI::Web::cust_header()-1)
+ ),
+ ),
+
+ sprintf( $money_char.'%.2f',
+ $row->{'rangecol_0_30'} ),
+ sprintf( $money_char.'%.2f',
+ $row->{'rangecol_30_60'} ),
+ sprintf( $money_char.'%.2f',
+ $row->{'rangecol_60_90'} ),
+ sprintf( $money_char.'%.2f',
+ $row->{'rangecol_90_0'} ),
+ sprintf( '<b>'. $money_char.'%.2f'. '</b>',
+ $row->{'rangecol_0_0'} ),
+ ('') x @pay_labels,
+ ],
+ 'fields' => [
+ FS::UI::Web::cust_fields_subs(),
+ format_rangecol('0_30'),
+ format_rangecol('30_60'),
+ format_rangecol('60_90'),
+ format_rangecol('90_0'),
+ format_rangecol('0_0'),
+ @pay_labels,
+ ],
+ 'links' => [
+ ( map { $_ ne 'Cust. Status' ? $clink : '' }
+ FS::UI::Web::cust_header()
+ ),
+ '',
+ '',
+ '',
+ '',
+ '',
+ @pay_links,
+ ],
+ #'align' => 'rlccrrrrr',
+ 'align' => FS::UI::Web::cust_aligns().
+ 'rrrrr'.
+ ('c' x @pay_labels),
+ #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ],
+ #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ],
+ 'size' => [ ( map '', FS::UI::Web::cust_header() ),
+ #'-1', '', '', '', '', '', ],
+ '', '', '', '', '', '',
+ ( map '', @pay_labels ),
+ ],
+ 'style' => [ FS::UI::Web::cust_styles(),
+ #'b', '', '', '', '', 'b', ],
+ '', '', '', '', 'b',
+ ( map '', @pay_labels ),
+ ],
+ 'color' => [
+ FS::UI::Web::cust_colors(),
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ ( map '', @pay_labels ),
+ ],
+ %opt,
+ )
+%>
+<%init>
+
+my %opt = @_;
+
+#actually need to auto-generate other things too for a passed-in ranges to work
+my $ranges = $opt{'ranges'} ? delete($opt{'ranges'}) : [
+ [ 0, 30 ],
+ [ 30, 60 ],
+ [ 60, 90 ],
+ [ 90, 0 ],
+ [ 0, 0 ],
+];
+
+my $range_sub = delete($opt{'range_sub'}); #or die
+
+my $offset = 0;
+if($cgi->param('as_of')) {
+ $offset = int((time - parse_datetime($cgi->param('as_of'))) / 86400);
+ $opt{'title'} .= ' ('.$cgi->param('as_of').')' if $offset > 0;
+}
+
+#my $range_cols = join(',', map &{$range_sub}( @$_ ), @ranges );
+my $range_cols = join(',', map call_range_sub($range_sub, @$_, 'offset' => $offset ), @$ranges );
+
+my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql;
+
+my $active_sql = FS::cust_pkg->active_sql;
+my $inactive_sql = FS::cust_pkg->inactive_sql;
+my $suspended_sql = FS::cust_pkg->suspended_sql;
+my $cancelled_sql = FS::cust_pkg->cancelled_sql;
+
+my $packages_cols = <<END;
+ ( $select_count_pkgs ) AS num_pkgs_sql,
+ ( $select_count_pkgs AND $active_sql ) AS active_pkgs,
+ ( $select_count_pkgs AND $inactive_sql ) AS inactive_pkgs,
+ ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs,
+ ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs
+END
+
+my @where = ();
+
+unless ( $cgi->param('all_customers') ) {
+# Exclude entire cust_main records where the balance is >0
+ my $days = 0;
+ if ( $cgi->param('days') =~ /^\s*(\d+)\s*$/ ) {
+ $days = $1;
+ }
+
+ push @where,
+ call_range_sub($range_sub, $days + $offset, 0, 'no_as'=>1). ' > 0'; # != 0';
+}
+
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ my $agentnum = $1;
+ push @where, "agentnum = $agentnum";
+}
+
+#status (false laziness w/cust_main::search_sql
+
+#prospect active inactive suspended cancelled
+if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) {
+ my $method = $cgi->param('status'). '_sql';
+ #push @where, $class->$method();
+ push @where, FS::cust_main->$method();
+}
+
+#here is the agent virtualization
+push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+my $where = join(' AND ', @where);
+$where = "WHERE $where" if $where;
+
+my $count_sql = "select count(*) from cust_main $where";
+
+my $sql_query = {
+ 'table' => 'cust_main',
+ 'hashref' => {},
+ 'select' => join(',',
+ #'cust_main.*',
+ 'custnum',
+ $range_cols,
+ $packages_cols,
+ FS::UI::Web::cust_sql_fields(),
+ 'payby',
+ ),
+ 'extra_sql' => $where,
+ 'order_by' => "order by coalesce(lower(company), ''), lower(last)",
+};
+
+my $total_sql =
+ "SELECT ".
+ join(',', map call_range_sub( $range_sub, @$_, 'offset' => $offset, 'sum'=>1 ), @$ranges).
+ " FROM cust_main $where";
+
+my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
+$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr;
+my $row = $total_sth->fetchrow_hashref();
+
+my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
+
+my (@payby, @pay_head, @pay_labels, @pay_links);
+
+my %payby = map {$_ => 1} $conf->config('payby');
+if(%payby) {
+ push @payby, 'CARD' if ($payby{'CARD'} or $payby{'DCRD'});
+ push @payby, 'CHEK' if ($payby{'CHEK'} or $payby{'DCHK'});
+}
+else {
+ @payby = ('CARD','CHEK')
+}
+
+if($opt{'payment_links'} && $curuser->access_right('Process payment') && @payby) {
+ my %label = ( CARD => 'Card',
+ CHEK => 'E-Check' );
+ push @pay_head, ({nodownload => 1}) foreach @payby;
+ $pay_head[0] = { label => 'Process',
+ nodownload => 1,
+ colspan => scalar(@payby) };
+
+ @pay_labels = (map { my $payby = $_;
+ my $label = $label{$payby};
+ sub {($payby eq $_[0]->payby) ? "<b>$label (on file)</b>" : $label}
+ } @payby );
+
+ @pay_links = (map { [ "${p}misc/payment.cgi?payby=$_;custnum=", 'custnum' ] }
+ @payby );
+}
+
+</%init>
+<%once>
+
+my $conf = new FS::Conf;
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+my $money_char = $conf->config('money_char') || '$';
+
+#Example:
+#
+# my $balance = balance(
+# $start, $end,
+# 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause)
+# #or 0 / omit when using in a SELECT clause as a column
+# # ("AS balance_$start_$end")
+# 'sum' => 1, #set to true to get a SUM() of the values, for totals
+#
+# #obsolete? options for totals (passed to cust_main::balance_date_sql)
+# 'total' => 1, #set to true to remove all customer comparison clauses
+# 'join' => $join, #JOIN clause
+# 'where' => \@where, #WHERE clause hashref (elements "AND"ed together)
+# )
+
+sub call_range_sub {
+ my($range_sub, $start, $end, %opt) = @_;
+
+ my $as = $opt{'no_as'} ? '' : " AS rangecol_${start}_$end";
+
+ my $sql = &{$range_sub}( $start, $end, $opt{'offset'} ); #%opt?
+
+ $sql = "SUM($sql)" if $opt{'sum'};
+
+ $sql.$as;
+
+}
+
+sub format_rangecol { #closures help alot
+ my $range = shift;
+ sub { sprintf( $money_char.'%.2f', shift->get("rangecol_$range") ) };
+}
+
+</%once>
diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html
new file mode 100755
index 000000000..4f83d0ab6
--- /dev/null
+++ b/httemplate/search/elements/cust_pay_or_refund.html
@@ -0,0 +1,345 @@
+<%doc>
+
+Examples:
+
+ include( 'elements/cust_pay_or_refund.html',
+ 'thing' => 'pay',
+ 'amount_field' => 'paid',
+ 'name_singular' => 'payment',
+ 'name_verb' => 'paid',
+ )
+
+ include( 'elements/cust_pay_or_refund.html',
+ 'thing' => 'refund',
+ 'amount_field' => 'refund',
+ 'name_singular' => 'refund',
+ 'name_verb' => 'refunded',
+ )
+
+ include( 'elements/cust_pay_or_refund.html',
+ 'thing' => 'pay_pending',
+ 'amount_field' => 'paid',
+ 'name_singular' => 'pending payment',
+ 'name_verb' => 'pending',
+ 'disable_link' => 1,
+ 'disable_by' => 1,
+ 'html_init' => '',
+ 'addl_header' => [],
+ 'addl_fields' => [],
+ 'redirect_empty' => $redirect_empty,
+ )
+
+ include( 'elements/cust_pay_or_refund.html',
+ 'table' => 'h_cust_pay',
+ 'amount_field' => 'paid',
+ 'name_singular' => 'payment',
+ 'name_verb' => 'paid',
+ 'pre_header' => [ 'Transaction', 'By' ],
+ 'pre_fields' => [ 'history_action', 'history_user' ],
+ )
+
+</%doc>
+<% include( 'search.html',
+ 'title' => $title,
+ 'name_singular' => $name_singular,
+ 'query' => $sql_query,
+ 'count_query' => $count_query,
+ 'count_addl' => [ '$%.2f total '.$opt{name_verb}, ],
+ 'redirect_empty' => $opt{'redirect_empty'},
+ 'header' => \@header,
+ 'fields' => \@fields,
+ 'align' => $align,
+ 'links' => \@links,
+ 'color' => \@color,
+ 'style' => \@style,
+ )
+%>
+<%init>
+
+my %opt = @_;
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('Financial reports');
+
+my $table = $opt{'table'} || 'cust_'.$opt{'thing'};
+
+my $amount_field = $opt{'amount_field'};
+my $name_singular = $opt{'name_singular'};
+
+my $title = "\u$name_singular Search Results";
+
+my $link = '';
+if ( ( $curuser->access_right('View invoices') #XXX for now
+ || $curuser->access_right('View customer payments')
+ )
+ && ! $opt{'disable_link'}
+ )
+{
+
+ my $key;
+ my $q = '';
+ if ( $table eq 'cust_pay_void' ) {
+ $key = 'paynum';
+ $q .= 'void=1;';
+ } elsif ( $table eq /^cust_(\w+)$/ ) {
+ $key = $1.'num';
+ }
+
+ if ( $key ) {
+ $q .= "$key=";
+ $link = [ "${p}view/$table.html?$q", $key ]
+ }
+}
+
+my $cust_link = sub {
+ my $cust_thing = shift;
+ $cust_thing->cust_main_custnum
+ ? [ "${p}view/cust_main.cgi?", 'custnum' ]
+ : '';
+};
+
+my @header = ();
+my @fields = ();
+my $align = '';
+my @links = ();
+if ( $opt{'pre_header'} ) {
+ push @header, @{ $opt{'pre_header'} };
+ $align .= 'c' x scalar(@{ $opt{'pre_header'} });
+ push @links, map '', @{ $opt{'pre_header'} };
+ push @fields, @{ $opt{'pre_fields'} };
+}
+
+push @header, "\u$name_singular",
+ 'Amount',
+ 'Date',
+;
+$align .= 'rrr';
+push @links, '', '', '';
+push @fields, 'payby_payinfo_pretty',
+ sub { sprintf('$%.2f', shift->$amount_field() ) },
+ sub { time2str('%b %d %Y', shift->_date ) },
+;
+
+unless ( $opt{'disable_by'} ) {
+ push @header, 'By';
+ $align .= 'c';
+ push @links, '';
+ push @fields, sub { my $o = shift->otaker;
+ $o = 'auto billing' if $o eq 'fs_daily';
+ $o = 'customer self-service' if $o eq 'fs_selfservice';
+ $o;
+ };
+}
+
+push @header, FS::UI::Web::cust_header();
+$align .= FS::UI::Web::cust_aligns();
+push @links, map { $_ ne 'Cust. Status' ? $cust_link : '' }
+ FS::UI::Web::cust_header();
+my @color = ( ( map '', @fields ), FS::UI::Web::cust_colors() );
+my @style = ( ( map '', @fields ), FS::UI::Web::cust_styles() );
+push @fields, \&FS::UI::Web::cust_fields;
+
+push @header, @{ $opt{'addl_header'} }
+ if $opt{'addl_header'};
+push @fields, @{ $opt{'addl_fields'} }
+ if $opt{'addl_fields'};
+
+my( $count_query, $sql_query );
+if ( $cgi->param('magic') ) {
+
+ my @search = ();
+ my $orderby;
+ if ( $cgi->param('magic') eq '_date' ) {
+
+ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ push @search, "agentnum = $1"; # $search{'agentnum'} = $1;
+ my $agent = qsearchs('agent', { 'agentnum' => $1 } );
+ die "unknown agentnum $1" unless $agent;
+ $title = $agent->agent. " $title";
+ }
+
+ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+ push @search, "custnum = $1";
+ }
+
+ if ( $cgi->param('payby') ) {
+ $cgi->param('payby') =~
+ /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/
+ or die "illegal payby ". $cgi->param('payby');
+ push @search, "$table.payby = '$1'";
+ if ( $3 ) {
+
+ my $cardtype = $3;
+
+ my $search;
+ if ( $cardtype eq 'VisaMC' ) {
+ #avoid posix regexes for portability
+ $search =
+ " ( ( substring($table.payinfo from 1 for 1) = '4' ".
+ " AND substring($table.payinfo from 1 for 4) != '4936' ".
+ " AND substring($table.payinfo from 1 for 6) ".
+ " NOT SIMILAR TO '49030[2-9]' ".
+ " AND substring($table.payinfo from 1 for 6) ".
+ " NOT SIMILAR TO '49033[5-9]' ".
+ " AND substring($table.payinfo from 1 for 6) ".
+ " NOT SIMILAR TO '49110[1-2]' ".
+ " AND substring($table.payinfo from 1 for 6) ".
+ " NOT SIMILAR TO '49117[4-9]' ".
+ " AND substring($table.payinfo from 1 for 6) ".
+ " NOT SIMILAR TO '49118[1-2]' ".
+ " )".
+ " OR substring($table.payinfo from 1 for 2) = '51' ".
+ " OR substring($table.payinfo from 1 for 2) = '52' ".
+ " OR substring($table.payinfo from 1 for 2) = '53' ".
+ " OR substring($table.payinfo from 1 for 2) = '54' ".
+ " OR substring($table.payinfo from 1 for 2) = '54' ".
+ " OR substring($table.payinfo from 1 for 2) = '55' ".
+ " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US
+ " ) ";
+ } elsif ( $cardtype eq 'Amex' ) {
+ $search =
+ " ( substring($table.payinfo from 1 for 2 ) = '34' ".
+ " OR substring($table.payinfo from 1 for 2 ) = '37' ".
+ " ) ";
+ } elsif ( $cardtype eq 'Discover' ) {
+ $search =
+ " ( substring($table.payinfo from 1 for 4 ) = '6011' ".
+ " OR substring($table.payinfo from 1 for 2 ) = '65' ".
+ " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN
+ " ) ";
+ } elsif ( $cardtype eq 'Maestro' ) {
+ $search =
+ " ( substring($table.payinfo from 1 for 2 ) = '63' ".
+ " OR substring($table.payinfo from 1 for 2 ) = '67' ".
+ " OR substring($table.payinfo from 1 for 6 ) = '564182' ".
+ " OR substring($table.payinfo from 1 for 4 ) = '4936' ".
+ " OR substring($table.payinfo from 1 for 6 ) ".
+ " SIMILAR TO '49030[2-9]' ".
+ " OR substring($table.payinfo from 1 for 6 ) ".
+ " SIMILAR TO '49033[5-9]' ".
+ " OR substring($table.payinfo from 1 for 6 ) ".
+ " SIMILAR TO '49110[1-2]' ".
+ " OR substring($table.payinfo from 1 for 6 ) ".
+ " SIMILAR TO '49117[4-9]' ".
+ " OR substring($table.payinfo from 1 for 6 ) ".
+ " SIMILAR TO '49118[1-2]' ".
+ " ) ";
+ } else {
+ die "unknown card type $cardtype";
+ }
+
+ my $masksearch = $search;
+ $masksearch =~ s/$table\.payinfo/$table.paymask/gi;
+
+ push @search,
+ "( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) )";
+
+ }
+ }
+
+ if ( $cgi->param('payinfo') ) {
+ $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/
+ or die "illegal payinfo ". $cgi->param('payinfo');
+ push @search, "$table.payinfo = '$1'";
+ }
+
+ if ( $cgi->param('otaker') =~ /^(\w+)$/ ) {
+ push @search, "$table.otaker = '$1'";
+ }
+
+ #for cust_pay_pending... statusNOT=done
+ if ( $cgi->param('statusNOT') =~ /^(\w+)$/ ) {
+ push @search, "status != '$1'";
+ }
+
+ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+ push @search, "_date >= $beginning ",
+ "_date <= $ending";
+
+ if ( $table eq 'cust_pay_void' ) {
+ my($v_beginning, $v_ending) =
+ FS::UI::Web::parse_beginning_ending($cgi, 'void');
+ push @search, "void_date >= $v_beginning ",
+ "void_date <= $v_ending";
+ }
+
+ push @search, FS::UI::Web::parse_lt_gt($cgi, $amount_field );
+
+ $orderby = '_date';
+
+ } elsif ( $cgi->param('magic') eq 'paybatch' ) {
+
+ $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/
+ or die "illegal paybatch: ". $cgi->param('paybatch');
+
+ push @search, "paybatch = '$1'";
+
+ $orderby = "LOWER(company || ' ' || last || ' ' || first )";
+
+ } else {
+ die "unknown search magic: ". $cgi->param('magic');
+ }
+
+ #for the history search
+ if ( $cgi->param('history_action') =~ /^([\w,]+)$/ ) {
+ my @history_action = split(/,/, $1);
+ push @search, 'history_action IN ('.
+ join(',', map "'$_'", @history_action ). ')';
+ }
+
+ if ( $cgi->param('history_date_beginning')
+ || $cgi->param('history_date_ending') ) {
+ my($h_beginning, $h_ending) =
+ FS::UI::Web::parse_beginning_ending($cgi, 'history_date');
+ push @search, "history_date >= $h_beginning ",
+ "history_date <= $h_ending";
+ }
+
+ #here is the agent virtualization
+ push @search, $curuser->agentnums_sql;
+
+ my $search = ' WHERE '. join(' AND ', @search);
+
+ $count_query = "SELECT COUNT(*), SUM($amount_field) ".
+ "FROM $table LEFT JOIN cust_main USING ( custnum )".
+ $search;
+
+ $sql_query = {
+ 'table' => $table,
+ 'select' => join(', ',
+ "$table.*",
+ 'cust_main.custnum as cust_main_custnum',
+ FS::UI::Web::cust_sql_fields(),
+ ),
+ 'hashref' => {},
+ 'extra_sql' => "$search ORDER BY $orderby",
+ 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+ };
+
+} else {
+
+ #hmm... is this still used?
+
+ $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo";
+ my $payinfo = $1;
+
+ $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby";
+ my $payby = $1;
+
+ $count_query = "SELECT COUNT(*), SUM($amount_field) FROM $table".
+ " WHERE payinfo = '$payinfo' AND payby = '$payby'".
+ " AND ". $curuser->agentnums_sql;
+
+ $sql_query = {
+ 'table' => $table,
+ 'hashref' => { 'payinfo' => $payinfo,
+ 'payby' => $payby },
+ 'extra_sql' => $curuser->agentnums_sql.
+ " ORDER BY _date",
+ };
+
+}
+
+</%init>
diff --git a/httemplate/search/elements/metasearch.html b/httemplate/search/elements/metasearch.html
new file mode 100644
index 000000000..b9d3e3ce2
--- /dev/null
+++ b/httemplate/search/elements/metasearch.html
@@ -0,0 +1,71 @@
+<%doc>
+
+Example:
+
+ include( 'elements/metasearch.html',
+
+ ###
+ # required
+ ###
+
+ 'title' => 'Page title',
+
+ #arrayref of hashrefs suited for passing to elements/search.html
+ #see that documentation
+ 'search' => [
+ {
+ query => { 'table' => 'tablename',
+ #everything else is optional...
+ 'hashref' => { 'f1' => 'value',
+ 'f2' => { 'op' => '<',
+ 'value' => '54',
+ },
+ },
+ 'select' => '*',
+ 'order_by' => 'ORDER BY something',
+
+ },
+ count_query => 'SELECT COUNT(*) FROM tablename',
+ },
+ {
+ query => 'table' => 'anothertablename',
+ count_query => 'SELECT COUNT(*) FROM anothertablename',
+ },
+ ],
+
+ ###
+ # optional
+ ###
+
+ # some HTML callbacks...
+ 'menubar' => '', #menubar arrayref
+ 'html_init' => '', #after the header/menubar and before the pager
+ 'html_form' => '', #after the pager, right before the results
+ # (only shown if there are results)
+ # (use this for any form-opening tag rather than
+ # html_init, to avoid a nested form)
+ 'html_foot' => '', #at the bottom
+ 'html_posttotal' => '', #at the bottom
+ # (these three can be strings or coderefs)
+
+ );
+
+</%doc>
+% foreach my $search ( @{$opt{search}} ) {
+<% include('search.html',
+ %$search,
+ 'type' => $type,
+ 'nohtmlheader' => 1,
+ )
+%>
+%
+% }
+<%init>
+
+my(%opt) = @_;
+#warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n";
+
+my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|select|html(-print)?)$/
+ ? $1 : 'html' ;
+
+</%init>
diff --git a/httemplate/search/elements/search-csv.html b/httemplate/search/elements/search-csv.html
new file mode 100644
index 000000000..9eb1b66d1
--- /dev/null
+++ b/httemplate/search/elements/search-csv.html
@@ -0,0 +1,54 @@
+% $csv->combine(@$header); #or die $csv->status;
+%
+<% $opt{no_csv_header} ? '' : $csv->string %>\
+%
+% foreach my $row ( @$rows ) {
+%
+% if ( $opt{'fields'} ) {
+%
+% my @line = ();
+%
+% foreach my $field ( @{$opt{'fields'}} ) {
+% if ( ref($field) eq 'CODE' ) {
+% push @line, map {
+% ref($_) eq 'ARRAY'
+% ? '(N/A)' #unimplemented
+% : $_;
+% }
+% &{$field}($row);
+% } else {
+% push @line, $row->$field();
+% }
+% }
+%
+% $csv->combine(@line); #or die $csv->status;
+%
+% } else {
+% $csv->combine(@$row); #or die $csv->status;
+% }
+%
+%
+<% $csv->string %>\
+%
+% }
+<%init>
+
+my %args = @_;
+my $header = $args{'header'};
+my $rows = $args{'rows'};
+my %opt = %{ $args{'opt'} };
+
+#http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes
+#http_header('Content-Type' => 'text/plain' );
+http_header('Content-Type' => 'text/csv' ); # So saith RFC 4180
+http_header('Content-Disposition' =>
+ 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.csv"');
+
+my $quote_char = '"';
+$quote_char = $opt{csv_quote} if exists($opt{csv_quote});
+
+my $csv = new Text::CSV_XS { 'always_quote' => $opt{avoid_quote} ? 0 : 1,
+ 'eol' => "\n", #"\015\012", #"\012"
+ };
+
+</%init>
diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html
new file mode 100644
index 000000000..e5e6ca954
--- /dev/null
+++ b/httemplate/search/elements/search-html.html
@@ -0,0 +1,477 @@
+%
+% if ( exists($opt{'redirect'}) && $opt{'redirect'}
+% && scalar(@$rows) == 1 && $total == 1
+% && $type ne 'html-print'
+% ) {
+% my $redirect = $opt{'redirect'};
+% $redirect = &{$redirect}($rows->[0], $cgi) if ref($redirect) eq 'CODE';
+% my( $url, $method ) = @$redirect;
+% redirect( $url. $rows->[0]->$method() );
+% } elsif ( exists($opt{'redirect_empty'}) && ! scalar(@$rows) && $total == 0
+% && $type ne 'html-print'
+% && $opt{'redirect_empty'}
+% && ( ref($opt{'redirect_empty'}) ne 'CODE'
+% || &{$opt{'redirect_empty'}}($cgi) )
+% ) {
+% my $redirect = $opt{'redirect_empty'};
+% $redirect = &{$redirect}($cgi) if ref($redirect) eq 'CODE';
+% redirect( $redirect );
+% } else {
+% if ( $opt{'name_singular'} ) {
+% $opt{'name'} = PL($opt{'name_singular'});
+% }
+% ( my $xlsname = $opt{'name'} ) =~ s/\W//g;
+% if ( $total == 1 ) {
+% if ( $opt{'name_singular'} ) {
+% $opt{'name'} = $opt{'name_singular'}
+% } else {
+% #$opt{'name'} =~ s/s$// if $total == 1;
+% $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1;
+% }
+% }
+%
+% if ( $type eq 'html-print' ) {
+
+ <% $opt{nohtmlheader}
+ ? ''
+ : include( '/elements/header-popup.html', $opt{'title'} )
+ %>
+
+% } elsif ( $type eq 'select' ) {
+
+ <% $opt{nohtmlheader}
+ ? ''
+ : include( '/elements/header-popup.html', $opt{'title'} )
+ %>
+ <% defined($opt{'html_init'})
+ ? ( ref($opt{'html_init'})
+ ? &{$opt{'html_init'}}()
+ : $opt{'html_init'}
+ )
+ : ''
+ %>
+
+% } else {
+%
+% my @menubar = ();
+% if ( $opt{'menubar'} ) {
+% @menubar = @{ $opt{'menubar'} };
+% #} else {
+% # @menubar = ( 'Main menu' => $p );
+% }
+
+ <% $opt{nohtmlheader}
+ ? ''
+ : include( '/elements/header.html', $opt{'title'},
+ include( '/elements/menubar.html', @menubar )
+ )
+ %>
+
+ <% defined($opt{'html_init'})
+ ? ( ref($opt{'html_init'})
+ ? &{$opt{'html_init'}}()
+ : $opt{'html_init'}
+ )
+ : ''
+ %>
+
+% }
+
+% unless ( $total ) {
+% unless ( $opt{'disable_nonefound'} ) {
+ No matching <% $opt{'name'} %> found.<BR>
+% }
+% }
+%
+% if ( $total || $opt{'disableable'} ) { #hmm... and there *are* ones to show??
+
+ <TABLE>
+ <TR>
+
+ <TD VALIGN="bottom">
+
+ <FORM>
+
+% if (! $opt{'disable_total'}) {
+ <% $total %> total <% $opt{'name'} %>
+% }
+
+% if ( $confmax && $total > $confmax
+% && ! $opt{'disable_maxselect'}
+% && $type ne 'html-print' )
+% {
+% $cgi->delete('maxrecords');
+% $cgi->param('_dummy', 1);
+
+ ( show <SELECT NAME="maxrecords" onChange="window.location = '<% $self_url %>;maxrecords=' + this.options[this.selectedIndex].value;">
+
+% foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) {
+ <OPTION VALUE="<% $max %>" <% ( $maxrecords == $max ) ? 'SELECTED' : '' %>><% $max %></OPTION>
+% }
+
+ </SELECT> per page )
+
+% $cgi->param('maxrecords', $maxrecords);
+% }
+
+% if ( defined($opt{'html_posttotal'}) && $type ne 'html-print' ) {
+ <% ref($opt{'html_posttotal'})
+ ? &{$opt{'html_posttotal'}}()
+ : $opt{'html_posttotal'}
+ %>
+% }
+ <BR>
+
+% if ( $opt{'count_addl'} ) {
+% my $n=0;
+% foreach my $count ( @{$opt{'count_addl'}} ) {
+% my $data = $count_arrayref->[++$n];
+% if ( ref($count) ) {
+ <% &{ $count }( $data ) %>
+% } else {
+ <% sprintf( $count, $data ) %><BR>
+% }
+% }
+% }
+ </FORM>
+
+ </TD>
+
+% unless ( $opt{'disable_download'} || $type eq 'html-print' ) {
+
+ <TD ALIGN="right">
+
+ Download full results<BR>
+
+% $cgi->param('_type', "$xlsname.xls" );
+ as <A HREF="<% $self_url %>">Excel spreadsheet</A><BR>
+
+% $cgi->param('_type', 'csv');
+ as <A HREF="<% $self_url %>">CSV file</A><BR>
+
+% if ( defined($opt{xml_elements}) ) {
+% $cgi->param('_type', 'xml');
+ as <A HREF="<% $self_url %>">XML file</A><BR>
+% }
+
+% $cgi->param('_type', 'html-print');
+ as <A HREF="<% $self_url %>">printable copy</A>
+
+ </TD>
+% $cgi->param('_type', "html" );
+% }
+
+ </TR>
+ <TR>
+ <TD COLSPAN=2>
+
+% my $pager = '';
+% unless ( $type eq 'html_print' ) {
+
+ <% $pager = include( '/elements/pager.html',
+ 'offset' => $offset,
+ 'num_rows' => scalar(@$rows),
+ 'total' => $total,
+ 'maxrecords' => $maxrecords,
+ )
+ %>
+
+ <% defined($opt{'html_form'})
+ ? ( ref($opt{'html_form'})
+ ? &{$opt{'html_form'}}()
+ : $opt{'html_form'}
+ )
+ : ''
+ %>
+
+% }
+
+ <% include('/elements/table-grid.html') %>
+
+ <TR>
+% my $h2 = 0;
+% my $colspan = 0;
+% foreach my $header ( @{ $opt{header} } ) {
+% $colspan-- if $colspan > 0;
+% next if $colspan;
+%
+% my $label = ref($header) ? $header->{label} : $header;
+% $colspan = ref($header) ? $header->{colspan} : 0;
+% my $rowspan = 1;
+% my $style = '';
+% if ( $opt{header2} ) {
+% if ( !length($opt{header2}->[$h2]) ) {
+% $rowspan = 2;
+% splice @{ $opt{header2} }, $h2, 1;
+% } else {
+% $h2++;
+% $style = 'STYLE="border-bottom: none"'
+% }
+% }
+ <TH CLASS = "grid"
+ BGCOLOR = "#cccccc"
+ ROWSPAN = "<% $rowspan %>"
+ <% $colspan ? 'COLSPAN = "'.$colspan.'"' : '' %>
+ <% $style %>
+
+ >
+ <% $label %>
+ </TH>
+% }
+ </TR>
+
+% if ( $opt{header2} ) {
+ <TR>
+% foreach my $header ( @{ $opt{header2} } ) {
+% my $label = ref($header) ? $header->{label} : $header;
+ <TH CLASS="grid" BGCOLOR="#cccccc">
+ <FONT SIZE="-1"><% $label %></FONT>
+ </TH>
+% }
+ </TR>
+% }
+
+% my $bgcolor1 = '#eeeeee';
+% my $bgcolor2 = '#ffffff';
+% my $bgcolor;
+%
+% foreach my $row ( @$rows ) {
+%
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
+
+ <TR>
+
+% if ( $opt{'fields'} ) {
+%
+% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : '';
+% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : [];
+% my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : '';
+% my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : [];
+% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : [];
+% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : [];
+% my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : [];
+%
+% foreach my $field (
+%
+% map {
+% if ( ref($_) eq 'ARRAY' ) {
+%
+% my $tableref = $_;
+%
+% '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0 WIDTH="100%">'.
+%
+% join('', map {
+%
+% my $rowref = $_;
+%
+% '<tr>'.
+%
+% join('', map {
+%
+% my $e = $_;
+%
+% '<TD '.
+% join(' ', map {
+% uc($_).'="'. $e->{$_}. '"';
+% }
+% grep exists($e->{$_}),
+% qw( align bgcolor colspan rowspan
+% style valign width )
+% ).
+% '>'.
+%
+% ( $e->{'link'}
+% ? '<A HREF="'. $e->{'link'}. '">'
+% : ''
+% ).
+% ( $e->{'size'}
+% ? '<FONT SIZE="'.uc($e->{'size'}).'">'
+% : ''
+% ).
+% ( $e->{'data_style'}
+% ? '<'. uc($e->{'data_style'}). '>'
+% : ''
+% ).
+% $e->{'data'}.
+% ( $e->{'data_style'}
+% ? '</'. uc($e->{'data_style'}). '>'
+% : ''
+% ).
+% ( $e->{'size'} ? '</FONT>' : '' ).
+% ( $e->{'link'} ? '</A>' : '' ).
+% '</td>';
+%
+% } @$rowref ).
+%
+% '</tr>';
+% } @$tableref ).
+%
+% '</table>';
+%
+% } else {
+% $_;
+% }
+% }
+%
+% map {
+% if ( ref($_) eq 'CODE' ) {
+% &{$_}($row);
+% } else {
+% $row->$_();
+% }
+% }
+% @{$opt{'fields'}}
+%
+% ) {
+%
+% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid';
+%
+% my $align = $aligns ? shift @$aligns : '';
+% $align = " ALIGN=$align" if $align;
+%
+% my $a = '';
+% if ( $links ) {
+% my $link = shift @$links;
+% my $onclick = shift @$onclicks;
+%
+% if ( ! $opt{'agent_virt'}
+% || ( $null_link && ! $row->agentnum )
+% || grep { $row->agentnum == $_ }
+% @link_agentnums
+% ) {
+%
+% $link = &{$link}($row)
+% if ref($link) eq 'CODE';
+%
+% $onclick = &{$onclick}($row)
+% if ref($onclick) eq 'CODE';
+% $onclick = qq( onClick="$onclick") if $onclick;
+%
+% if ( $link ) {
+% my( $url, $method ) = @{$link};
+% if ( ref($method) eq 'CODE' ) {
+% $a = $url. &{$method}($row);
+% } else {
+% $a = $url. $row->$method();
+% }
+% $a = qq(<A HREF="$a"$onclick>);
+% }
+% elsif ( $onclick ) {
+% $a = qq(<A HREF="javascript:void(0);"$onclick>);
+% }
+% }
+%
+% }
+%
+% my $font = '';
+% my $color = shift @$colors;
+% $color = &{$color}($row) if ref($color) eq 'CODE';
+% my $size = shift @$sizes;
+% $size = &{$size}($row) if ref($size) eq 'CODE';
+% if ( $color || $size ) {
+% $font = '<FONT '.
+% ( $color ? "COLOR=#$color " : '' ).
+% ( $size ? qq(SIZE="$size" ) : '' ).
+% '>';
+% }
+%
+% my($s, $es) = ( '', '' );
+% my $style = shift @$styles;
+% $style = &{$style}($row) if ref($style) eq 'CODE';
+% if ( $style ) {
+% $s = join( '', map "<$_>", split('', $style) );
+% $es = join( '', map "</$_>", split('', $style) );
+% }
+%
+% my $cstyle = shift @$cstyles;
+% $cstyle = &{$cstyle}($row) if ref($cstyle) eq 'CODE';
+% $cstyle = qq(STYLE="$cstyle")
+% if $cstyle;
+
+ <TD CLASS="<% $class %>" BGCOLOR="<% $bgcolor %>" <% $align %> <% $cstyle %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '</A>' : '' %><% $font ? '</FONT>' : '' %></TD>
+
+% }
+%
+% } else {
+%
+% foreach ( @$row ) {
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $_ %></TD>
+% }
+%
+% }
+
+ </TR>
+
+% }
+
+% if ( $opt{'footer'} ) {
+
+ <TR>
+
+% foreach my $footer ( @{ $opt{'footer'} } ) {
+% $footer = &{$footer}() if ref($footer) eq 'CODE';
+ <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><% $footer %></i></TD>
+% }
+
+ </TR>
+% }
+
+ </TABLE>
+
+ <% $pager %>
+
+ </TD>
+ </TR>
+ </TABLE>
+% }
+
+% if ( $type eq 'html-print' ) {
+% unless ( $opt{nohtmlheader} ) {
+
+ </BODY></HTML>
+
+% }
+% } else {
+
+ <% defined($opt{'html_foot'})
+ ? ( ref($opt{'html_foot'})
+ ? &{$opt{'html_foot'}}()
+ : $opt{'html_foot'}
+ )
+ : ''
+ %>
+
+ <% $opt{nohtmlheader}
+ ? ''
+ : include( '/elements/footer.html' )
+ %>
+
+% }
+
+% }
+<%init>
+
+my %args = @_;
+my $type = $args{'type'};
+my $header = $args{'header'};
+my $rows = $args{'rows'};
+my @link_agentnums = @{ $args{'link_agentnums'} };
+my $null_link = $args{'null_link'};
+my $confmax = $args{'confmax'};
+my $maxrecords = $args{'maxrecords'};
+my $offset = $args{'offset'};
+my %opt = %{ $args{'opt'} };
+my $self_url = $opt{'url'} || $cgi->self_url;
+
+my $count_sth = dbh->prepare($opt{'count_query'})
+ or die "Error preparing $opt{'count_query'}: ". dbh->errstr;
+$count_sth->execute
+ or die "Error executing $opt{'count_query'}: ". $count_sth->errstr;
+my $count_arrayref = $count_sth->fetchrow_arrayref;
+my $total = $count_arrayref->[0];
+
+</%init>
diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html
new file mode 100644
index 000000000..8323f55de
--- /dev/null
+++ b/httemplate/search/elements/search-xls.html
@@ -0,0 +1,85 @@
+<% $data %>
+<%init>
+
+my %args = @_;
+my $type = $args{'type'};
+my $header = $args{'header'};
+my $rows = $args{'rows'};
+my %opt = %{ $args{'opt'} };
+
+#http_header('Content-Type' => 'application/excel' ); #eww
+#http_header('Content-Type' => 'application/msexcel' ); #alas
+#http_header('Content-Type' => 'application/x-msexcel' ); #?
+
+#http://support.microsoft.com/kb/199841
+http_header('Content-Type' => 'application/vnd.ms-excel' );
+http_header('Content-Disposition' =>
+ 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.xls"');
+
+#http://support.microsoft.com/kb/812935
+#http://support.microsoft.com/kb/323308
+$HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0';
+
+my $data = '';
+my $XLS = new IO::Scalar \$data;
+my $workbook = Spreadsheet::WriteExcel->new($XLS)
+ or die "Error opening .xls file: $!";
+
+my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31));
+
+$worksheet->protect();
+
+my($r,$c) = (0,0);
+
+my $header_format = $workbook->add_format(
+ bold => 1,
+ locked => 1,
+ bg_color => 55, #22,
+ bottom => 3,
+);
+
+$worksheet->write($r, $c++, $_, $header_format ) foreach @$header;
+
+foreach my $row ( @$rows ) {
+ $r++;
+ $c = 0;
+
+ if ( $opt{'fields'} ) {
+
+ #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : '';
+ #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : '';
+ #could also translate color, size, style into xls equivalents?
+ my $formats = $opt{'xls_format'} ? [ @{$opt{'xls_format'}} ] : [];
+
+ foreach my $field ( @{$opt{'fields'}} ) {
+
+ my $format = shift @$formats;
+ $format = &{$format}($row) if ref($format) eq 'CODE';
+ $format ||= {};
+ my $xls_format = $workbook->add_format(locked=>0, %$format);
+
+ if ( ref($field) eq 'CODE' ) {
+ foreach my $value ( &{$field}($row) ) {
+ if ( ref($value) eq 'ARRAY' ) {
+ $worksheet->write($r, $c++, '(N/A)' ); #unimplemented
+ } else {
+ $worksheet->write($r, $c++, $value, $xls_format );
+ }
+ }
+ } else {
+ $worksheet->write($r, $c++, $row->$field(), $xls_format );
+ }
+ }
+
+ } else {
+ my $xls_format = $workbook->add_format(locked=>0);
+ $worksheet->write($r, $c++, $_, $xls_format ) foreach @$row;
+ }
+
+}
+
+$workbook->close();# or die "Error creating .xls file: $!";
+
+http_header('Content-Length' => length($data) );
+
+</%init>
diff --git a/httemplate/search/elements/search-xml.html b/httemplate/search/elements/search-xml.html
new file mode 100644
index 000000000..9f5e9b6c1
--- /dev/null
+++ b/httemplate/search/elements/search-xml.html
@@ -0,0 +1,88 @@
+% foreach my $row ( @$rows ) {
+%
+% if (&{$beginrow}($row)){
+<% &{$beginrow}($row) %>
+% }
+%
+% foreach my $i ( 0 .. scalar( @{$opt{'fields'}} ) - 1 ) {
+% my $field = $opt{'fields'}->[$i];
+% my $value = '';
+% if ( ref($field) eq 'CODE' ) {
+% $value = &{$field}($row);
+% $value = '(N/A)' #unimplemented
+% if ref($value) eq 'ARRAY';
+% } else {
+% $value = $row->$field();
+% }
+%
+<% &{$beginfield}($row, $i) %><% $value |h %><% &{$endfield}($row, $i) %>
+%
+% }
+%
+% if (&{$endrow}($row)) {
+<% &{$endrow}($row) %>
+% }
+%
+% }
+<%init>
+
+my %args = @_;
+my $header = $args{'header'};
+my $rows = $args{'rows'};
+my %opt = %{ $args{'opt'} };
+
+http_header('Content-Type' => 'application/XML' ); # So saith RFC 4180
+http_header('Content-Disposition' =>
+ 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.xml"');
+
+unless ( $opt{'fields'} ) {
+ foreach my $i ( 0 .. ( $#{ @$rows[0] } ) ) {
+ $opt{'fields'}->[$i] = sub { my $row = shift; $row->[$i]; };
+ }
+}
+
+my $beginrow = sub { return ''; };
+my $endrow = sub { return ''; };
+if ($opt{xml_row_element}) {
+ $beginrow = sub { my ($row, $index) = @_;
+ my $value;
+ if ( ref($opt{xml_row_element}) eq 'CODE' ) {
+ $value = &{$opt{xml_row_element}}($row);
+ } else {
+ $value = $opt{xml_row_element};
+ }
+ return "<$value>";
+ };
+ $endrow = sub { my ($row, $index) = @_;
+ my $value;
+ if ( ref($opt{xml_row_element}) eq 'CODE' ) {
+ $value = &{$opt{xml_row_element}}($row);
+ } else {
+ $value = $opt{xml_row_element};
+ }
+ return "</$value>";
+ };
+}
+my $beginfield = sub { my ($row, $index) = @_;
+ my $value;
+ if ( ref($opt{xml_elements}->[$index]) eq 'CODE' ) {
+ $value = &{$opt{xml_elements}->[$index]}($row);
+ } else {
+ $value = $opt{xml_elements}->[$index];
+ }
+ return "<$value>";
+ };
+my $endfield = sub { my ($row, $index) = @_;
+ my $value;
+ if ( ref($opt{xml_elements}->[$index]) eq 'CODE' ) {
+ $value = &{$opt{xml_elements}->[$index]}($row);
+ } else {
+ $value = $opt{xml_elements}->[$index];
+ }
+ return "</$value>";
+ };
+
+$beginfield = sub { return ''; } if $opt{no_field_elements}; #hmm
+$endfield = sub { return ''; } if $opt{no_field_elements}; #hmm
+
+</%init>
diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
new file mode 100644
index 000000000..a258f1721
--- /dev/null
+++ b/httemplate/search/elements/search.html
@@ -0,0 +1,415 @@
+<%doc>
+
+Example:
+
+ include( 'elements/search.html',
+
+ ###
+ # required
+ ###
+
+ '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
+ #of qsearch hashrefs for a union of qsearches
+ 'query' => {
+ 'table' => 'tablename',
+ #everything else is optional...
+ 'hashref' => { 'field' => 'value',
+ 'field' => { 'op' => '<',
+ 'value' => '54',
+ },
+ },
+ 'select' => '*',
+ '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',
+
+ ###
+ # recommended / common
+ ###
+
+ #listref of column labels, <TH>
+ #recommended unless 'query' is an SQL query string
+ # (if not specified the database column names will be used)
+ '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
+ 'fields' => [
+ 'column',
+ sub { my $row = shift; $row->column; },
+ ],
+
+ #redirect if there's only one item...
+ # listref of URL base and column name (or method)
+ # or a coderef that returns the same
+ 'redirect' => sub { my( $record, $cgi ) = @_;
+ [ popurl(2).'view/item.html', 'primary_key' ];
+ },
+
+ #redirect if there's no items
+ # scalar URL or a coderef that returns a URL
+ '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_form' => '', #after the pager, right before the results
+ # (only shown if there are results)
+ # (use this for any form-opening tag rather than
+ # html_init, to avoid a nested form)
+ '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
+
+ #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"
+ # field, to hide disabled records & have
+ # "show disabled/hide disabled" links
+ #(can't be used with a literal query)
+ 'disabled_statuspos' => 3, #optional position (starting from 0) to insert
+ #a Status column when showing disabled records
+ #(query needs to be a qsearch hashref and
+ # header & fields need to be defined)
+
+ #handling agent virtualization
+ 'agent_virt' => 1, # set true if this search should be
+ # agent-virtualized
+ 'agent_null' => 1, # set true to view global records always
+ 'agent_null_right' => 'Access Right', # optional right to view global
+ # records
+ 'agent_null_right_link' => 'Access Right' # optional right to link to
+ # global records; defaults to
+ # same as agent_null_right
+ '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)
+
+ # link & display properties for fields
+
+ #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' => [],`
+
+ #listref - each item is the empty string,
+ # or a string onClick handler for the corresponding link
+ # or a coderef that returns string onClick handler
+ 'link_onclicks' => [],
+
+ #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' => [],
+ 'size' => [],
+ 'style' => [], #<B> or <I>, etc.
+ 'cell_style' => [], #STYLE= attribute of TR, very HTML-specific...
+
+ # 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' => => [],
+
+ )
+
+</%doc>
+% if ( $type eq 'csv' ) {
+%
+<% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %>
+%
+% #} elsif ( $type eq 'excel' ) {
+% } elsif ( $type =~ /\.xls$/ ) {
+%
+<% include('search-xls.html', header=>$header, rows=>$rows, opt=>\%opt ) %>
+%
+% } elsif ( $type eq 'xml' ) {
+%
+<% include('search-xml.html', rows=>$rows, opt=>\%opt ) %>
+%
+% } else { # regular HTML
+%
+<% include('search-html.html',
+ type => $type,
+ header => $header,
+ rows => $rows,
+ link_agentnums => \@link_agentnums,
+ null_link => $null_link,
+ confmax => $confmax,
+ maxrecords => $maxrecords,
+ offset => $offset,
+ opt => \%opt
+ )
+%>
+%
+% }
+<%init>
+
+my(%opt) = @_;
+#warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n";
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|xml|select|html(-print)?)$/
+ ? $1 : 'html' ;
+
+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;
+ my $i = 0;
+ do {
+ if( ref($h->[$i]) and exists($h->[$i]->{'nodownload'}) ) {
+ splice(@{$opt{$_}}, $i, 1) foreach
+ qw(header footer fields links link_onclicks
+ align color size style cell_style xls_format);
+ }
+ else {
+ $i++;
+ }
+ } while ( exists($h->[$i]) );
+}
+
+# wtf?
+$opt{disable_download} = 0
+ if $opt{disable_download} && $curuser->access_right('Configuration download');
+
+$opt{disable_download} = 1
+ if $opt{really_disable_download};
+
+my @link_agentnums = ();
+my $null_link = '';
+if ( $opt{'agent_virt'} ) {
+
+ @link_agentnums = $curuser->agentnums;
+ $null_link = $curuser->access_right( $opt{'agent_null_right_link'}
+ || $opt{'agent_null_right'} );
+
+ my $agentnums_sql = $curuser->agentnums_sql(
+ 'null' => $opt{'agent_null'},
+ 'null_right' => $opt{'agent_null_right'},
+ 'table' => $opt{'query'}{'table'},
+ );
+
+ $opt{'query'}{'extra_sql'} .=
+ ( $opt{'query'}{'extra_sql'} =~ /WHERE/i || keys %{$opt{'query'}{'hashref'}}
+ ? ' AND '
+ : ' WHERE ' ). $agentnums_sql;
+
+ $opt{'count_query'} .=
+ ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). $agentnums_sql;
+
+ if ( $opt{'agent_pos'} || $opt{'agent_pos'} eq '0'
+ and scalar($curuser->agentnums) > 1 ) {
+ #false laziness w/statuspos above
+ my $pos = $opt{'agent_pos'};
+
+ foreach my $att (qw( align color size style cell_style xls_format )) {
+ $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{'fields'} }, $pos, 0,
+ sub { $_[0]->agentnum ? $_[0]->agent->agent : '(global)'; };
+ splice @{ $opt{'color'} }, $pos, 0, '';
+ splice @{ $opt{'links'} }, $pos, 0, '' #[ 'agent link?', 'agentnum' ]
+ if $opt{'links'};
+ splice @{ $opt{'link_onclicks'} }, $pos, 0, ''
+ if $opt{'link_onclicks'};
+
+ }
+
+}
+
+if ( $opt{'disableable'} ) {
+
+ unless ( $cgi->param('showdisabled') ) { #modify searches
+
+ $opt{'query'}{'hashref'}{'disabled'} = '';
+ $opt{'query'}{'extra_sql'} =~ s/^\s*WHERE/ AND/i;
+
+ $opt{'count_query'} .=
+ ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
+ "( disabled = '' OR disabled IS NULL )";
+
+ } elsif ( $opt{'disabled_statuspos'}
+ || $opt{'disabled_statuspos'} eq '0' ) { #add status column
+
+ my $pos = $opt{'disabled_statuspos'};
+
+ foreach my $att (qw( align style color size )) {
+ $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{'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'};
+ }
+
+ #add show/hide disabled links
+ my $items = $opt{'name'} || PL($opt{'name_singular'});
+ if ( $cgi->param('showdisabled') ) {
+ $cgi->param('showdisabled', 0);
+ $opt{'html_posttotal'} .=
+ '( <a href="'. $cgi->self_url. qq!">hide disabled $items</a> )!; #"
+ $cgi->param('showdisabled', 1);
+ } else {
+ $cgi->param('showdisabled', 1);
+ $opt{'html_posttotal'} .=
+ '( <a href="'. $cgi->self_url. qq!">show disabled $items</a> )!; #"
+ $cgi->param('showdisabled', 0);
+ }
+
+}
+
+my $limit = '';
+my($confmax, $maxrecords, $offset );
+
+if ( !$type =~ /^(csv|\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;
+ }
+
+ $limit = $maxrecords ? "LIMIT $maxrecords" : '';
+
+ $offset = $cgi->param('offset') =~ /^(\d+)$/ ? $1 : 0;
+ $limit .= " OFFSET $offset" if $offset;
+
+ }
+
+}
+
+# run the query
+
+my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ];
+my $rows;
+if ( ref($opt{query}) ) {
+
+ my @query;
+ if (ref($opt{query}) eq 'HASH') {
+ @query = ( $opt{query} );
+ } elsif (ref($opt{query}) eq 'ARRAY') {
+ @query = @{ $opt{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 );
+ $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;
+ $sth->execute
+ or die "Error executing $opt{'query'}: ". $sth->errstr;
+
+ #can get # of rows without fetching them all?
+ $rows = $sth->fetchall_arrayref;
+
+ $header ||= $sth->{NAME};
+}
+
+</%init>
generated by cgit v1.2.1 (git 2.18.0) at 2025-08-23 08:16:40 +0000