<%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 '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, #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' => ### # 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_right' => 'Access Right', #opt. right to view global records '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' => [], # or , etc. 'cell_style' => [], #STYLE= attribute of TR, very HTML-specific... ); % if ( $type eq 'csv' ) { % % #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes % http_header('Content-Type' => 'text/plain' ); % % my $csv = new Text::CSV_XS { 'always_quote' => 1, % 'eol' => "\n", #"\015\012", #"\012" % }; % % $csv->combine(@$header); #or die $csv->status; % <% $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 %> % % % } % % #} elsif ( $type eq 'excel' ) { % } elsif ( $type =~ /\.xls$/ ) { % % #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://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)); % % my($r,$c) = (0,0); % % $worksheet->write($r, $c++, $_) foreach @$header; % % foreach my $row ( @$rows ) { % $r++; % $c = 0; % % if ( $opt{'fields'} ) { % % #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; % #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; % % foreach my $field ( @{$opt{'fields'}} ) { % #my $align = $aligns ? shift @$aligns : ''; % #$align = " ALIGN=$align" if $align; % #my $a = ''; % #if ( $links ) { % # my $link = shift @$links; % # $link = &{$link}($row) if ref($link) eq 'CODE'; % # if ( $link ) { % # my( $url, $method ) = @{$link}; % # if ( ref($method) eq 'CODE' ) { % # $a = $url. &{$method}($row); % # } else { % # $a = $url. $row->$method(); % # } % # $a = qq(); % # } % #} % 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 ); % } % } % } else { % $worksheet->write($r, $c++, $row->$field() ); % } % } % % } else { % $worksheet->write($r, $c++, $_) foreach @$row; % } % % } % % $workbook->close();# or die "Error creating .xls file: $!"; % % http_header('Content-Length' => length($data) ); % <% $data %> % % % } else { # regular HTML % % if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 % && $type ne 'html-print' % ) { % my $redirect = $opt{'redirect'}; % $redirect = &{$redirect}($rows->[0]) if ref($redirect) eq 'CODE'; % my( $url, $method ) = @$redirect; % redirect( $url. $rows->[0]->$method() ); % } 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' ) { <% include( '/elements/header-popup.html', $opt{'title'} ) %> % } elsif ( $type eq 'select' ) { <% 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 ); % } <% 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.
% } % } % % if ( $total || $opt{'disableable'} ) { #hmm... and there *are* ones to show?? % unless ( $opt{'disable_download'} || $type eq 'html-print' ) { % $cgi->param('_type', "html" ); % } % my $h2 = 0; % foreach my $header ( @{ $opt{header} } ) { % my $label = ref($header) ? $header->{label} : $header; % 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"' % } % } % } % if ( $opt{header2} ) { % foreach my $header ( @{ $opt{header2} } ) { % my $label = ref($header) ? $header->{label} : $header; % } % } % my $bgcolor1 = '#eeeeee'; % my $bgcolor2 = '#ffffff'; % my $bgcolor; % % foreach my $row ( @$rows ) { % % if ( $bgcolor eq $bgcolor1 ) { % $bgcolor = $bgcolor2; % } else { % $bgcolor = $bgcolor1; % } % 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 = $_; % % '

% 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 % foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) { ><% $max %> % } 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'} %> % } % 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 ) %> % } % } % }	Download full results % $cgi->param('_type', "$xlsname.xls" ); as Excel spreadsheet % $cgi->param('_type', 'csv'); as CSV file % $cgi->param('_type', 'html-print'); as printable copy <% $opt{'extra_choices_callback'} ? &{$opt{'extra_choices_callback'}}($cgi->query_string) : '' %>
% 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') %>
> <% $label %>
<% $label %>

'. % % join('', map { % % my $rowref = $_; % % ''. % % join('', map { % % my $e = $_; % % ''; % % } @$rowref ). % % ''; % } @$tableref ). % % '

{$_}), % qw( align bgcolor colspan rowspan % style valign width ) % ). % '>'. % % ( $e->{'link'} % ? '' % : '' % ). % ( $e->{'size'} % ? '' % : '' % ). % ( $e->{'data_style'} % ? '<'. uc($e->{'data_style'}). '>' % : '' % ). % $e->{'data'}. % ( $e->{'data_style'} % ? '{'data_style'}). '>' % : '' % ). % ( $e->{'size'} ? '' : '' ). % ( $e->{'link'} ? '' : '' ). % '

'; % % } else { % $_; % } % } % % map { % if ( ref($_) eq 'CODE' ) { % &{$_}($row); % } else { % $row->$_(); % } % } % @{$opt{'fields'}} % % ) { % % my $class = ( $field =~ /^$method(); % } % $a = qq(); % } % } % % 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 = ''; % } % % 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; % } % % } else { % % foreach ( @$row ) { % } % % } % } % if ( $opt{'footer'} ) { % foreach my $footer ( @{ $opt{'footer'} } ) { % } % }

<% $cstyle %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '' : '' %><% $font ? '' : '' %>	<% $_ %>
<% $footer %>

<% $pager %> % } % if ( $type eq 'html-print' ) { % } else { <% defined($opt{'html_foot'}) ? ( ref($opt{'html_foot'}) ? &{$opt{'html_foot'}}() : $opt{'html_foot'} ) : '' %> <% include( '/elements/footer.html' ) %> % } % } % % } <%init> my(%opt) = @_; #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; my $curuser = $FS::CurrentUser::CurrentUser; my %align = ( 'l' => 'left', 'r' => 'right', 'c' => 'center', ' ' => '', '.' => '', ); $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], unless !$opt{align} || ref($opt{align}); if ( $opt{'agent_virt'} ) { my $agentnums_sql = $curuser->agentnums_sql( 'null_right' => $opt{'agent_null_right'} ); $opt{'query'}{'extra_sql'} .= ( $opt{'query'} =~ /WHERE/i ? ' 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 style color size )) { $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'} .= '( hide disabled $items )!; $cgi->param('showdisabled', 1); } else { $cgi->param('showdisabled', 1); $opt{'html_posttotal'} .= '( show disabled $items )!; $cgi->param('showdisabled', 0); } } my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|select|html(-print)?)$/ ? $1 : 'html'; my $limit = ''; my($confmax, $maxrecords, $total, $offset, $count_arrayref); unless ( $type =~ /^(csv|\w*\.xls)$/ ) { 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; } 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; $count_arrayref = $count_sth->fetchrow_arrayref; $total = $count_arrayref->[0]; } # run the query my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; if ( ref($opt{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'};"; $rows = [ qsearch({ 'select' => $opt{'query'}->{'select'}, 'table' => $opt{'query'}->{'table'}, 'addl_from' => (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : ''), 'hashref' => $opt{'query'}->{'hashref'} || {}, 'extra_sql' => $opt{'query'}->{'extra_sql'}, 'order_by' => $opt{'query'}->{'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}; }