diff options
Diffstat (limited to 'httemplate')
| -rw-r--r-- | httemplate/elements/freeside.css | 18 | ||||
| -rw-r--r-- | httemplate/elements/select.html | 3 | ||||
| -rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 32 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search.html | 49 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/core | 153 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/html | 131 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/html-print | 79 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/xls | 61 | ||||
| -rw-r--r-- | httemplate/search/elements/match-classnum | 12 | ||||
| -rw-r--r-- | httemplate/search/elements/report_cust_pay_or_refund.html | 63 | ||||
| -rw-r--r-- | httemplate/search/elements/search-html.html | 659 | ||||
| -rw-r--r-- | httemplate/search/elements/search-xls.html | 73 | ||||
| -rw-r--r-- | httemplate/search/elements/search.html | 135 |
13 files changed, 1040 insertions, 428 deletions
diff --git a/httemplate/elements/freeside.css b/httemplate/elements/freeside.css index 90b2d51b1..e292172ff 100644 --- a/httemplate/elements/freeside.css +++ b/httemplate/elements/freeside.css @@ -256,6 +256,24 @@ table.grid { border-radius: 2px; } +tr.row0 { + background-color: #eeeeee; +} + +tr.row1 { + background-color: #ffffff; +} + +.grid tfoot tr { + background-color: #dddddd; + font-style: italic; +} + +/* border at the top of the footer, but not between footer rows */ +.grid tfoot tr:first-child td { + border-top: 1px dashed black; +} + th, td { vertical-align: top; } diff --git a/httemplate/elements/select.html b/httemplate/elements/select.html index 67ef51418..4492681de 100644 --- a/httemplate/elements/select.html +++ b/httemplate/elements/select.html @@ -4,6 +4,7 @@ ID = "<% $opt{id} %>" previousValue = "<% $curr_value %>" previousText = "<% $labels->{$curr_value} || $curr_value %>" + <% $multiple %> <% $size %> <% $style %> <% $opt{disabled} %> @@ -74,4 +75,6 @@ my $style = scalar(@style) ? 'STYLE="'. join(';', @style). '"' : ''; my $size = $opt{'size'} ? 'SIZE='.$opt{'size'} : ''; +my $multiple = $opt{'multiple'} ? 'MULTIPLE' : ''; + </%init> diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 0e3fce303..ce513ab15 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -39,7 +39,7 @@ Examples: ) </%doc> -<& search.html, +<& grouped-search.html, 'title' => $title, # XXX: translate 'name_singular' => $name_singular, 'query' => $sql_query, @@ -54,6 +54,16 @@ Examples: 'link_onclicks' => \@link_onclicks, 'color' => \@color, 'style' => \@style, + + 'group_column' => 'payby', + 'group_label' => 'payby_name', + 'subtotal' => { 'paid' => 'sum(paid)' }, + 'subtotal_row' => [ 'Subtotal', + sub { sprintf($money, $_[0]->paid) }, + ], + 'total_row' => [ '<B>Total</B>', + sub { sprintf("<B>$money</B>", $_[0]->paid) }, + ], &> <%init> @@ -61,6 +71,9 @@ my %opt = @_; my $curuser = $FS::CurrentUser::CurrentUser; +my $conf = FS::Conf->new; +my $money = ($conf->config('money_char') || '$') . '%.2f'; + die "access denied" unless $curuser->access_right('Financial reports'); @@ -165,7 +178,7 @@ push @header, "\u$name_singular", $align .= 'rr'; push @links, '', ''; push @fields, 'payby_payinfo_pretty', - sub { sprintf('$%.2f', shift->$amount_field() ) }, + sub { sprintf($money, shift->$amount_field() ) }, ; push @link_onclicks, $sub_receipt, ''; push @sort_fields, '', $amount_field; @@ -174,7 +187,7 @@ if ( $unapplied ) { push @header, emt('Unapplied'); $align .= 'r'; push @links, ''; - push @fields, sub { sprintf('$%.2f', shift->unapplied_amount) }; + push @fields, sub { sprintf($money, shift->unapplied_amount) }; push @sort_fields, ''; } @@ -252,14 +265,10 @@ if ( $cgi->param('magic') ) { $title = $part_referral->referral. " $title"; } - # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) - if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { - my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); - push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. - join(',', map { $_ || '0' } @classnums ). - ' )' - if @classnums; - } + # cust_classnum - standard matching + push @search, $m->comp('match-classnum', + param => 'cust_classnum', field => 'cust_main.classnum' + ); if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { push @search, "$table.custnum = $1"; @@ -321,7 +330,6 @@ if ( $cgi->param('magic') ) { } elsif ( $cardtype eq 'Discover' ) { - my $conf = new FS::Conf; my $country = $conf->config('countrydefault') || 'US'; $search = diff --git a/httemplate/search/elements/grouped-search.html b/httemplate/search/elements/grouped-search.html new file mode 100644 index 000000000..56fc88d38 --- /dev/null +++ b/httemplate/search/elements/grouped-search.html @@ -0,0 +1,49 @@ +<%doc> + +<& elements/grouped-search/html, + + # required + 'title' => 'Page title', + + 'name_singular' => 'item', + + 'query' => { + 'table' => 'tablename', + 'hashref' => { 'field' => 'value', ... }, # optional + 'select' => '*', + 'addl_from' => '', + 'extra_sql' => '', + #'order_by' is not allowed + }, + 'group_column' => 'classnum', # must be a single field + # 'group_table' => 'mytable', # optional; defaults to the one in the query + 'group_label' => 'classname', + 'subtotal' => { amount => 'sum(amount)', ... } + # The subtotal row will be generated as an FS::* object of the same type + # as all the detail rows. + # The only fields present will be the grouping column and any subtotals + # defined in 'subtotal'. Key is a field name (in the FS::* object), + # value is an SQL aggregate expression. + + # How to display that object: arrayref of literal strings or coderefs + # (the subtotal object will be passed). These will be placed in table + # cells, and can contain HTML. + 'subtotal_row' => + [ 'Total' + sub { sprintf('%.2f', $_[0]->amount) }, + ], + + 'order_by' => '_date', # SQL ordering expression for the detail rows + + 'header' => [ ... ], # per search.html + 'fields' => [ ... ], + +&> +</%doc> +<%init> +my $type = 'html'; +if ($cgi->param('type') =~ /^(html|html-print|xls)$/) { + $type = $1; +} +</%init> +<& "grouped-search/$type", @_ &> diff --git a/httemplate/search/elements/grouped-search/core b/httemplate/search/elements/grouped-search/core new file mode 100644 index 000000000..fe47fb7d1 --- /dev/null +++ b/httemplate/search/elements/grouped-search/core @@ -0,0 +1,153 @@ +<%doc> + +my $group_data = $m->comp( 'elements/grouped-search/core', + + 'query' => { + 'table' => 'tablename', + 'hashref' => { 'field' => 'value', ... }, # optional + 'select' => '*', + 'addl_from' => '', + 'extra_sql' => '', + #'order_by' is not allowed + }, + 'group_column' => 'classnum', # must be a single field + # 'group_table' => 'mytable', # optional; defaults to the one in the query + 'group_label' => 'classname', + 'subtotal' => { amount => 'sum(amount)', ... } + # The subtotal row will be generated as an FS::* object of the same type + # as all the detail rows. + # The only fields present will be the grouping column and any subtotals + # defined in 'subtotal'. Key is a field name (in the FS::* object), + # value is an SQL aggregate expression. + + # How to display that object: arrayref of literal strings or coderefs + # (the subtotal object will be passed). These will be placed in table + # cells, and can contain HTML. + 'subtotal_row' => + [ 'Subtotal', sub { sprintf('%.2f', $_[0]->amount) }, ], + + # The subtotal aggregates will also be calculated for the entire data + # set, without grouping. How to display those (if not present, will + # not be displayed); + 'total_row' => + [ 'Total', sub { sprintf('%.2f', $_[0]->amount) }, ], + + 'order_by' => '_date', # SQL ordering expression for the detail rows + + 'header' => [ ... ], # per search.html + 'fields' => [ ... ], +); + +returns: +{ + num => number of groups, + groups => [ group summary objects, + with group key columns, subtotals, and "num_rows" ], + group_labels => [ label strings ], + group_footers => [ formatted subtotal row arrayrefs ], + queries => [ FS::Query objects to evaluate to get detail rows ], +} + +</%doc> +<%shared> +my $conf = FS::Conf->new; +</%shared> +<%init> +my %opt = @_; + +my $base_query = FS::Query->new( $opt{'query'} ); +my $main_table = $base_query->{table}; + +# $opt{subtotal} is a hashref of field names => SQL aggregate expressions. + +my @subtotal_names = sort keys(%{ $opt{'subtotal'} }); +my @subtotal_exprs = map { $opt{'subtotal'}->{$_} } @subtotal_names; + +# qualify the group key to the main table unless otherwise specified +my $group_table = $opt{group_table} || $main_table; +my $group_key = $group_table . '.' . $opt{group_column}; + +my @select = ( + $group_key, # need this to identify groups + 'COUNT(*) as num_rows', # and this for pagination + map { '(' . $subtotal_exprs[$_] . ') AS "' . $subtotal_names[$_] . '"' } + 0 .. (scalar(@subtotal_names) - 1) +); + +my $group_query = $base_query->clone; +$group_query->{order_by} = "GROUP BY $group_key"; +$group_query->{select} = join(',', @select); +# keep table, addl_from, extra_sql, and hashref as they are + +#warn Dumper($group_query); #DEBUG + +# run the group query and create a tab label for each group +my @groups = $group_query->qsearch; + +# also run it with no grouping, and just get overall totals +my $total_query = $base_query->clone; +shift @select; # remove $group_key +$total_query->{select} = join(',', @select); +$total_query->{order_by} = ''; + +my $totals = $total_query->qsearchs; + +my $order_by = ''; +if ( $cgi->param('order_by') =~ /^(\w+\.?\w*(?: DESC)?)$/ ) { + $order_by = $1; +} +if ( $opt{order_by} ) { + $order_by .= ', ' if length($order_by); # user selection takes priority + $order_by .= $opt{order_by}; +} +$order_by = "ORDER BY $order_by " if $order_by; +$base_query->{order_by} = $order_by; + +my $group_label = $opt{group_label} || $opt{group_column}; +my (@group_labels, @group_footers, @queries); +for my $i (0 .. scalar(@groups) - 1) { + my $label = $groups[$i]->$group_label . ' (' . $groups[$i]->num_rows . ')'; + push @group_labels, $label; + + my @footer; + if ($opt{'subtotal_row'}) { + for( my $col = 0; + exists($opt{'subtotal_row'}[$col]) or exists($opt{'header'}[$col]); + $col++ + ) { + my $value = $opt{'subtotal_row'}[$col] || ''; + $value = &$value( $groups[$i] ) if ref($value) eq 'CODE'; + $footer[$col] = $value; + } + } + push @group_footers, \@footer; + + my $detail_query = $base_query->clone; + my $group_key_value = $groups[$i]->get( $opt{group_column} ); + $group_key_value = dbh->quote($group_key_value) + unless looks_like_number($group_key_value); + $detail_query->and_where("$group_key = $group_key_value"); + push @queries, $detail_query; +} + +my @total_footer; +if ($opt{'total_row'}) { + for( my $col = 0; + exists($opt{'total_row'}[$col]) or exists($opt{'header'}[$col]); + $col++ + ) { + my $value = $opt{'total_row'}[$col] || ''; + $value = &$value( $totals ) if ref($value) eq 'CODE'; + $total_footer[$col] = $value; + } +} + +return { + num => scalar(@groups), + groups => \@groups, + group_labels => \@group_labels, + group_footers => \@group_footers, + queries => \@queries, + total_footer => \@total_footer, +}; +</%init> diff --git a/httemplate/search/elements/grouped-search/html b/httemplate/search/elements/grouped-search/html new file mode 100644 index 000000000..aff17cea0 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html @@ -0,0 +1,131 @@ +<%shared> +my $conf = FS::Conf->new; +</%shared> +<%init> +my %opt = @_; +$opt{'name'} ||= PL($opt{'name_singular'}); + +my $group_info = $m->comp('core', %opt); + +my $redirect; + +if ( $group_info->{num} == 0 ) { + $redirect = $opt{'redirect_empty'}; + if ($redirect) { + $redirect = &$redirect($cgi) if ref($redirect) eq 'CODE'; + redirect( $redirect ); + } else { # just print this stuff and exit + $m->comp('/elements/header.html', $opt{'title'}); + $m->print('<BR><BR>No matching ' . $opt{'name'} . ' found.<BR>'); + $m->comp('/elements/footer.html'); + $m->abort; + } +} + +# this mode has a concept of "current group" +my $curr_group = 0; +if ($cgi->param('group') =~ /^(\d+)$/) { + $curr_group = $1; +} + +my $group = $group_info->{groups}[$curr_group]; +my $query = $group_info->{queries}[$curr_group]; +my $footer = $group_info->{group_footers}[$curr_group]; +my $total_footer = $group_info->{total_footer} || []; +# pagination +my ($limit, $offset); +my $maxrecords = $conf->config('maxsearchrecordsperpage') || 50; +if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) { + $maxrecords = $1; +} +if ( $maxrecords ) { + $limit = "LIMIT $maxrecords"; + if ( $cgi->param('offset') =~ /^(\d+)$/ ) { + $offset = $1; + $limit .= " OFFSET $offset"; + } +} +$query->{order_by} .= $limit if $limit; + +#warn Dumper($query); #DEBUG + +# run the query +my @rows = $query->qsearch; + +#warn Dumper(\@rows); #DEBUG + +my $pager = ''; +# show pager if needed +if ( $group->num_rows > scalar(@rows) ) { + $pager = include( '/elements/pager.html', + 'offset' => $offset, + 'num_rows' => scalar(@rows), + 'total' => $group->num_rows, + 'maxrecords' => $maxrecords, + ); +} + +# set up tab bar +my @menubar; +for (my $i = 0; $i < $group_info->{num}; $i++) { + push @menubar, $group_info->{group_labels}[$i], ";group=$i"; +} + +</%init> + +<& /elements/header.html, $opt{title} &> + +%# tab bar +% $cgi->delete('group'); +% $cgi->delete('offset'); +% $cgi->delete('type'); +<& /elements/menubar.html, + { newstyle => 1, + url_base => $cgi->self_url, + selected => $group_info->{group_labels}[$curr_group] }, + @menubar +&> + +<DIV CLASS="fstabcontainer"> +%# download links +<P><% emt('Download full results') %><BR> +% $cgi->param('type', 'xls'); +<A HREF="<% $cgi->self_url %>"><% emt('as Excel spreadsheet') %></A><BR> +% $cgi->param('type', 'html-print'); +<A HREF="<% $cgi->self_url %>"><% emt('as printable copy') %></A><BR> +</P> + +<% $pager %> + +<STYLE> + table.grid { + border-spacing: 0; + } +</STYLE> +<table class="grid"> + <thead> + <& /search/elements/search-html.html:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), + &> + </thead> + <tbody> + <& /search/elements/search-html.html:data_rows, + 'rows' => \@rows, + 'opt' => \%opt, + &> + </tbody> + <tfoot> + <& /search/elements/search-html.html:footer_row, row => $footer, opt => \%opt &> +% if ( scalar @$total_footer ) { + <& /search/elements/search-html.html:footer_row, row => $total_footer, opt => \%opt &> +% } + </tfoot> +</table> + +<% $pager %> +</DIV> + +<& /elements/footer.html &> + diff --git a/httemplate/search/elements/grouped-search/html-print b/httemplate/search/elements/grouped-search/html-print new file mode 100644 index 000000000..c4c3b1ef5 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html-print @@ -0,0 +1,79 @@ +<%doc> + +The "printable" view (all groups on one page). +</%doc> +<%init> +my %opt = @_; + +my $group_info = $m->comp('core', %opt); +my $ncols = scalar(@{ $opt{header} }); + +my $total_footer = $group_info->{total_footer} || []; +</%init> +<& /elements/header-popup.html, $opt{title} &> + +<STYLE> +.grouphead { + text-align: left; + font-size: 120%; + padding: 1ex 0 0.5ex 0.1ex; + border-top: 1px solid black; +} +.subtotal td { + background-color: #dddddd; + font-style: italic; + border-top: 1px dashed black; +} +.total td { + background-color: #dddddd; + font-style: italic; + font-weight: bold; + border-top: 2px solid black !important; +} +</STYLE> +<& /elements/table-grid.html &> +<THEAD> +<& /search/elements/search-html.html:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), +&> +</THEAD> + +% for (my $curr_group = 0; $curr_group < $group_info->{num}; $curr_group++) { +% my $group = $group_info->{groups}[$curr_group]; +% my $query = $group_info->{queries}[$curr_group]; +% my $footer = $group_info->{group_footers}[$curr_group]; +% my $label = $group_info->{group_labels}[$curr_group]; +% # run the query +% my @rows = $query->qsearch; +% #warn Dumper(\@rows); #DEBUG + +<TBODY> +<TR><TH CLASS="grouphead" COLSPAN="<% $ncols %>"> + <% $label %> +</TH></TR> + +<& /search/elements/search-html.html:data_rows, + rows => \@rows, + opt => \%opt, +&> +</TBODY> +<TBODY CLASS="subtotal"> +<& /search/elements/search-html.html:footer_row, + row => $footer, + opt => \%opt, +&> +</TBODY> +% } # for $curr_group + +% if ( scalar @$total_footer ) { +<TFOOT CLASS="total"> +<& /search/elements/search-html.html:footer_row, + row => $total_footer, + opt => \%opt, +&> +</TFOOT> +% } +</TABLE> +<& /elements/footer.html &> diff --git a/httemplate/search/elements/grouped-search/xls b/httemplate/search/elements/grouped-search/xls new file mode 100644 index 000000000..8540dcf06 --- /dev/null +++ b/httemplate/search/elements/grouped-search/xls @@ -0,0 +1,61 @@ +<%doc> + +Excel spreadsheet view. + +</%doc> +<%init> +my %opt = @_; + +my $group_info = $m->comp('core', %opt); + +# minor false laziness with search-xls.html +my $override = ''; +$override = 'XLSX' if grep { $_->num_rows >= 65536 } + @{ $group_info->{groups} }; + +my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); + +my $filename = $opt{'name'} || PL($opt{'name_singular'}); +$filename .= $format->{extension}; + +http_header('Content-Type' => $format->{mime_type} ); +http_header('Content-Disposition' => qq!attachment;filename="$filename"! ); +$HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; + +my $data = ''; +my $XLS = new IO::Scalar \$data; +my $workbook = $format->{class}->new($XLS) + or die "Error opening Excel file: $!"; + +my $title = $opt{'title'}; +$title =~ s/[\[\]\:\*\?\/\/]//g; +$title = substr($title, 0, 31); + +for (my $curr_group = 0; $curr_group < $group_info->{num}; $curr_group++) { + my $group = $group_info->{groups}[$curr_group]; + my $query = $group_info->{queries}[$curr_group]; + my $footer = $group_info->{group_footers}[$curr_group]; + my $label = $group_info->{group_labels}[$curr_group]; + # run the query + my @rows = $query->qsearch; + #warn Dumper(\@rows); #DEBUG + + # pass arrayrefs to write_row to write multiple rows + $opt{footer} = [ List::MoreUtils::pairwise { [ $a, $b ] } + @$footer, + @{$group_info->{total_footer}} + ]; + $m->comp('/search/elements/search-xls.html:worksheet', + workbook => $workbook, + title => $label, + header => $opt{header}, + opt => \%opt, + rows => \@rows, + ); +} + +$workbook->close(); + +$m->clear_buffer(); +$m->print($data); +</%init> diff --git a/httemplate/search/elements/match-classnum b/httemplate/search/elements/match-classnum new file mode 100644 index 000000000..ed1efd53c --- /dev/null +++ b/httemplate/search/elements/match-classnum @@ -0,0 +1,12 @@ +<%args> +$param # name of the form field containing the classnum +$field => $param # SQL field that must match that form field +</%args> +<%init> +my @values = grep /^(\d+)$/, $cgi->param( $param ); +if (@values) { + return "COALESCE($field, 0) IN (" . join(',', @values) . ')'; +} else { + return; +} +</%init> diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html index 9c4ca2761..70727c007 100644 --- a/httemplate/search/elements/report_cust_pay_or_refund.html +++ b/httemplate/search/elements/report_cust_pay_or_refund.html @@ -27,36 +27,14 @@ Examples: </TH> </TR> - <TR> - <TD ALIGN="right"><% ucfirst(PL($name_singular)) %> of type: </TD> - <TD> - <SELECT NAME="payby" SIZE=16 MULTIPLE> - -%# <OPTION VALUE=""><% mt('all') |h %></OPTION> -%# <OPTION VALUE="CARD"><% mt('credit card (all)') |h %></OPTION> - - <OPTION VALUE="CARD-VisaMC" SELECTED><% mt('credit card (Visa/MasterCard)') |h %></OPTION> - <OPTION VALUE="CARD-Amex" SELECTED><% mt('credit card (American Express)') |h %></OPTION> - <OPTION VALUE="CARD-Discover" SELECTED><% mt('credit card (Discover)') |h %></OPTION> - <OPTION VALUE="CARD-Maestro" SELECTED><% mt('credit card (Maestro/Switch/Solo)') |h %></OPTION> - <OPTION VALUE="CARD-Tokenized" SELECTED><% mt('credit card (Tokenized)') |h %></OPTION> - - <OPTION VALUE="CHEK" SELECTED><% mt('electronic check / ACH') |h %></OPTION> - <OPTION VALUE="BILL" SELECTED><% mt('check') |h %></OPTION> - <OPTION VALUE="CASH" SELECTED><% mt('cash') |h %></OPTION> - <OPTION VALUE="PPAL" SELECTED><% mt('Paypal') |h %></OPTION> - <OPTION VALUE="APPL" SELECTED><% mt('Apple Store') |h %></OPTION> - <OPTION VALUE="ANRD" SELECTED><% mt('Android Market') |h %></OPTION> - - <OPTION VALUE="PREP" SELECTED><% mt('prepaid card') |h %></OPTION> - <OPTION VALUE="WIRE" SELECTED><% mt('wire transfer') |h %></OPTION> - <OPTION VALUE="WEST" SELECTED><% mt('Western Union') |h %></OPTION> - <OPTION VALUE="EDI" SELECTED><% mt('Electronic Debit (EDI)') |h %></OPTION> - <OPTION VALUE="MCRD" SELECTED><% mt('manual credit card') |h %></OPTION> - <OPTION VALUE="MCHK" SELECTED><% mt('manual electronic check') |h %></OPTION> - </SELECT> - </TD> - </TR> + <& /elements/tr-select.html, + label => ucfirst(PL($name_singular)) . ' of type:', + field => 'payby', + options => [ keys(\%payby) ], + labels => \%payby, + multiple => 1, + size => 16 + &> <TR> <TD ALIGN="right"><% mt('Check #:') |h %> </TD> @@ -137,8 +115,7 @@ Examples: 'label' => emt('Customer class'), 'field' => 'cust_classnum', 'multiple' => 1, - 'pre_options' => [ '' => emt('(none)') ], - 'all_selected' => 1, + 'pre_options' => [ 0 => emt('(none)') ], &> </TABLE> @@ -189,4 +166,26 @@ my $title = $void ? "Voided $name_singular report" : "\u$name_singular report" ; $table .= '_void' if $void; +tie (my %payby, 'Tie::IxHash', + 'CARD-VisaMC' => 'credit card (Visa/MasterCard)', + 'CARD-Amex' => 'credit card (American Express)', + 'CARD-Discover' => 'credit card (Discover)', + 'CARD-Maestro' => 'credit card (Maestro/Switch/Solo)', + 'CARD-Tokenized' => 'credit card (Tokenized)', + + 'CHEK' => 'electronic check / ACH', + 'BILL' => 'check', + 'CASH' => 'cash', + 'PPAL' => 'Paypal', + 'APPL' => 'Apple Store', + 'ANRD' => 'Android Market', + + 'PREP' => 'prepaid card', + 'WIRE' => 'wire transfer', + 'WEST' => 'Western Union', + 'EDI' => 'Electronic Debit (EDI)', + 'MCRD' => 'manual credit card', + 'MCHK' => 'manual electronic check', +); + </%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index 10cc95539..d8ab30a32 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -181,300 +181,13 @@ % } - <% include('/elements/table-grid.html') %> - - <TR> -% my $h2 = 0; -% my $colspan = 0; -% my @fields = @{ $opt{'sort_fields'} || $opt{'fields'} || [] }; -% my $order_by = $cgi->param('order_by'); -% foreach my $header ( @{ $opt{header} } ) { -% -% my $field = shift @fields; -% -% $colspan-- if $colspan > 0; -% next if $colspan; -% -% my $label = ref($header) ? $header->{label} : $header; -% unless ( ref($field) || !$field ) { -% if ( $order_by eq $field ) { -% $cgi->param('order_by', "$field DESC"); -% } else { -% $cgi->param('order_by', $field); -% } -% $label = qq(<A HREF="$self_url?). $cgi->query_string. -% qq(">$label</A>); -% } -% -% $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; -% } - -% my $rowstyle = ''; -% if ( $row eq $opt{'footer_data'} ) { -% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic"'; -% $bgcolor = '#dddddd'; -% } - -% my $trid = ''; -% if ( $opt{'link_field' } ) { -% my $link_field = $opt{'link_field'}; -% if ( ref($link_field) eq 'CODE' ) { -% $trid = &{$link_field}($row); -% } else { -% $trid = $row->$link_field(); -% } -% } - <TR ID="<%$trid |h%>"<%$rowstyle%>> - - -% if ( $opt{'fields'} ) { -% -% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; -% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; -% my $tooltips = $opt{'tooltips'} ? [ @{$opt{'tooltips'}} ] : []; -% 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->{'onclick'} # don't use with 'link' -% ? '<A HREF="#" onclick="' . -% $e->{'onclick'}.'">' -% : '' -% ). -% ( $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'} || $e->{'onclick'} -% ? '</A>' -% : '' ). -% '</td>'; -% -% } @$rowref ). -% -% '</tr>'; -% } @$tableref ). -% -% '</table>'; -% -% } else { -% $_; -% } -% } -% -% map { -% if ( ref($_) eq 'CODE' ) { -% &{$_}($row); -% } elsif ( ref($row) eq 'ARRAY' and -% $_ =~ /^\d+$/ ) { -% # for the 'straight SQL' case: specify fields -% # by position -% encode_entities($row->[$_]); -% } else { -% encode_entities($row->$_()); -% } -% } -% @{$opt{'fields'}} -% -% ) { -% -%# my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; -% my $class = 'grid'; -% -% my $align = $aligns ? shift @$aligns : ''; -% $align = " ALIGN=$align" if $align; -% -% my $a = ''; -% if ( $links ) { -% my $link = shift @$links; -% my $onclick = shift @$onclicks; -% my $tooltip = shift @$tooltips; -% -% 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; -% -% $tooltip = &{$tooltip}($row) -% if ref($tooltip) eq 'CODE'; -% $tooltip = qq! id="a$id" !. -% qq! onmouseover="return overlib(!. -% $m->interp->apply_escapes($tooltip, 'h', 'js_string'). -% qq!, FGCLASS, 'tooltip', REF, 'a$id', !. -% qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; -% -% 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$tooltip>); -% } -% elsif ( $onclick ) { -% $a = qq(<A HREF="javascript:void(0);"$onclick>); -% } -% elsif ( $tooltip ) { -% $a = qq(<A $tooltip>); -% } -% $id++; - -% } -% -% } -% -% 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 %>><% $a %><% $font %><% $s %><% $field %><% $es %><% $font ? '</FONT>' : '' %><% $a ? '</A>' : '' %></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> + <& SELF:data_table, + rows => $rows, + null_link => $null_link, + link_agentnums => \@link_agentnums, + self_url => $self_url, + %opt + &> <% $pager %> @@ -521,14 +234,358 @@ my $confmax = $args{'confmax'}; my $maxrecords = $args{'maxrecords'}; my $offset = $args{'offset'}; my %opt = %{ $args{'opt'} }; -my $self_url = $opt{'url'} || $cgi->url('-path_info' => 1, '-full' =>1); -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; +# must be an arrayref of the row count, followed by any other totals +my $count_arrayref = $args{'totals'}; my $total = $count_arrayref->[0]; -my $id = 0; +# there used to be an option to override this, for highly dubious reasons +my $self_url = $cgi->url('-path_info' => 1, '-full' =>1); + </%init> +<%method data_table> +% my %opt = @_; +% my $rows = delete $opt{rows}; +% my $self_url = delete $opt{self_url}; +<& /elements/table-grid.html &> + +<THEAD> +<& SELF:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), +&> +</THEAD> + +<TBODY> +<& SELF:data_rows, rows => $rows, opt => \%opt &> +</TBODY> + +% if ( $opt{'footer'} ) { +<TFOOT> +<& SELF:footer_row, row => $opt{'footer'}, opt => \%opt &> +</TFOOT> +% } +</TABLE> +</%method> +<%method header_row> +<%args> +@sort_fields +@header +@header2 => () +</%args> + <TR> +% my $h2 = 0; +% my $colspan = 0; +% my $order_by = $cgi->param('order_by'); +% my $self_url = $cgi->url('-path_info' => 1, '-full' =>1); +% foreach my $header ( @header ) { +% +% my $field = shift @sort_fields; +% +% $colspan-- if $colspan > 0; +% next if $colspan; +% +% my $label = ref($header) ? $header->{label} : $header; +% unless ( ref($field) || !$field ) { +% if ( $order_by eq $field ) { +% $cgi->param('order_by', "$field DESC"); +% } else { +% $cgi->param('order_by', $field); +% } +% $label = qq(<A HREF="$self_url?). $cgi->query_string. +% qq(">$label</A>); +% } +% +% $colspan = ref($header) ? $header->{colspan} : 0; +% my $rowspan = 1; +% my $style = ''; +% if ( @header2 ) { +% if ( !length($header2[$h2]) ) { +% $rowspan = 2; +% splice @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 ( @header2 ) { + <TR> +% foreach my $header ( @header2 ) { +% my $label = ref($header) ? $header->{label} : $header; + <TH CLASS="grid" BGCOLOR="#cccccc"> + <FONT SIZE="-1"><% $label %></FONT> + </TH> +% } + </TR> +% } +</%method> +<%method data_rows> +<%args> +$rows => [] +%opt +</%args> +% my %align = ( +% 'l' => 'left', +% 'r' => 'right', +% 'c' => 'center', +% ' ' => '', +% '.' => '', +% ); +% if ( $opt{align} and !ref($opt{align}) ) { +% $opt{align} = [ map $align{$_}, split(//, $opt{align}) ]; +% } + +% my $i = 0; # for row striping # XXX CSS - nth-child +% foreach my $row ( @$rows ) { +% +% my $rowstyle = ''; +% if ( $row eq $opt{'footer_data'} ) { # XXX CSS - tfoot +% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic background-color=#dddddd"'; +% } +% +% my $trid = ''; +% if ( $opt{'link_field' } ) { +% my $link_field = $opt{'link_field'}; +% if ( ref($link_field) eq 'CODE' ) { +% $trid = &{$link_field}($row); +% } else { +% $trid = $row->$link_field(); +% } +% } + <TR ID="<%$trid |h%>" CLASS="row<% $i % 2 %>"<%$rowstyle%>> + +% if ( $opt{'fields'} ) { +% +% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; +% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; +% my $tooltips = $opt{'tooltips'} ? [ @{$opt{'tooltips'}} ] : []; +% 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->{'onclick'} # don't use with 'link' +% ? '<A HREF="#" onclick="' . +% $e->{'onclick'}.'">' +% : '' +% ). +% ( $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'} || $e->{'onclick'} +% ? '</A>' +% : '' ). +% '</td>'; +% +% } @$rowref ). +% +% '</tr>'; +% } @$tableref ). +% +% '</table>'; +% +% } else { +% $_; +% } +% } +% +% map { +% if ( ref($_) eq 'CODE' ) { +% &{$_}($row); +% } elsif ( ref($row) eq 'ARRAY' and +% $_ =~ /^\d+$/ ) { +% # for the 'straight SQL' case: specify fields +% # by position +% encode_entities($row->[$_]); +% } else { +% encode_entities($row->$_()); +% } +% } +% @{$opt{'fields'}} +% +% ) { +% +% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +% my $class = 'grid'; +% +% my $align = $aligns ? shift @$aligns : ''; +% $align = " ALIGN=$align" if $align; +% +% my $a = ''; +% if ( $links ) { +% my $link = shift @$links; +% my $onclick = shift @$onclicks; +% my $tooltip = shift @$tooltips; +% +% if ( ! $opt{'agent_virt'} +% || ( $opt{'null_link'} && ! $row->agentnum ) +% || grep { $row->agentnum == $_ } +% @{ $opt{link_agentnums} } +% ) { +% +% $link = &{$link}($row) +% if ref($link) eq 'CODE'; +% +% $onclick = &{$onclick}($row) +% if ref($onclick) eq 'CODE'; +% $onclick = qq( onClick="$onclick") if $onclick; +% +% $tooltip = &{$tooltip}($row) +% if ref($tooltip) eq 'CODE'; +% $tooltip = qq! title="<% $tooltip |h %>"!; +%# qq! onmouseover="return overlib(!. +%# $m->interp->apply_escapes($tooltip, 'h', 'js_string'). +%# qq!, FGCLASS, 'tooltip', REF, 'a$id', !. +%# qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; +% +% 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$tooltip>); +% } +% elsif ( $onclick ) { +% $a = qq(<A HREF="javascript:void(0);"$onclick>); +% } +% elsif ( $tooltip ) { +% $a = qq(<A $tooltip>); +% } + +% } +% +% } +% +% 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 %>" <% $align %> <% $cstyle %>><% $a %><% $font %><% $s %><% $field %><% $es %><% $font ? '</FONT>' : '' %><% $a ? '</A>' : '' %></TD> + +% } +% +% } else { # not $opt{'fields'} +% +% foreach ( @$row ) { + <TD CLASS="grid"><% $_ %></TD> +% } +% +% } + + </TR> + +% $i++; +% +% } # foreach $row +</%method> +<%method footer_row> +<%args> +$row +%opt +</%args> +%# don't try to respect all the styling options, just the ones that are +%# hard to replicate with CSS +% my %align = ( +% 'l' => 'left', +% 'r' => 'right', +% 'c' => 'center', +% ' ' => '', +% '.' => '', +% ); +% if ( $opt{align} and !ref($opt{align}) ) { +% $opt{align} = [ map $align{$_}, split(//, $opt{align}) ]; +% } +% my @aligns = @{ $opt{align} }; + +<TR> +% foreach my $footer ( @$row ) { +% $footer = &{$footer}() if ref($footer) eq 'CODE'; +% my $align = shift @aligns; +% my $style = ''; +% $style .= "text-align: $align;" if $align; + <TD CLASS="grid" STYLE="<% $style %>"><% $footer %></TD> +% } +</TR> +</%method> + diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index 8334497d2..c4265e8c8 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -1,13 +1,10 @@ <%init> my %args = @_; -my $type = $args{'type'}; my $header = $args{'header'}; my $rows = $args{'rows'}; my %opt = %{ $args{'opt'} }; -my $style = $opt{'style'}; - my $override = scalar(@$rows) >= 65536 ? 'XLSX' : ''; my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); @@ -35,9 +32,38 @@ my $workbook = $format->{class}->new($XLS) my $title = $opt{'title'}; $title =~ s/[\[\]\:\*\?\/\/]//g; $title = substr($title, 0, 31); + +# append a single worksheet +$m->comp( 'SELF:worksheet', + workbook => $workbook, + title => $title, + opt => \%opt, + header => $header, + rows => $rows +); + +$workbook->close();# or die "Error creating .xls file: $!"; + +http_header('Content-Length' => length($data) ); +$m->clear_buffer(); +$m->print($data); + +</%init> +<%method worksheet> +<%args> +$workbook +$title +%opt +$header +$rows +</%args> +<%perl> + my $worksheet = $workbook->add_worksheet($title); -$worksheet->protect(); +#$worksheet->protect(); + +my $style = $opt{style}; my($r,$c) = (0,0); @@ -63,12 +89,29 @@ xl_parse_date_init(); my %bold_format; -my $writer = sub { +my @widths; + +my $writer; +$writer = sub { # Wrapper for $worksheet->write. # Do any massaging of the value/format here. my ($r, $c, $value, $format) = @_; #warn "writer called with format $format\n"; + if ( ref $value eq 'ARRAY' ) { + # imitate the write_row() method: write the array into a column starting + # with $r. + # (currently only used in the footer; to use it anywhere else we'd need + # some way to return the number of rows written) + foreach my $v (@$value) { + $writer->($r, $c, $v, $format); + $r++; + } + return; + } + + my $bold = 0; + my $date = 0; if ( $style->[$c] eq 'b' or $value =~ /<b>/i ) { # the only one in common use $value =~ s[</?b>][]ig; if ( !exists($bold_format{$format}) ) { @@ -77,6 +120,7 @@ my $writer = sub { $bold_format{$format}->set_bold(); } $format = $bold_format{$format}; + $bold = 1; } # convert HTML entities @@ -106,6 +150,7 @@ my $writer = sub { $date_format{$format}->set_num_format('mmm dd yyyy'); } $format = $date_format{$format}; + $date = 1; } else { # String: replace line breaks with newlines @@ -113,6 +158,14 @@ my $writer = sub { } #warn "writing with format $format\n"; $worksheet->write($r, $c, $value, $format); + + # estimate width + # use Font::TTFMetrics; # would work, but we can't redistribute the font... + my $width = length($value); + $width = 11 if $date; + $width *= 1.1 if $bold; + $width += 1; # pad it a little + $widths[$c] = $width if $width > ($widths[$c] || 0); }; $writer->( $r, $c++, $_, $header_format ) foreach @$header; @@ -170,9 +223,9 @@ if ( $opt{'footer'} ) { } } -$workbook->close();# or die "Error creating .xls file: $!"; - -http_header('Content-Length' => length($data) ); -$m->print($data); +for ( my $x = 0; $x < scalar @widths; $x++ ) { + $worksheet->set_column($x, $x, $widths[$x]); +} -</%init> +</%perl> +</%method> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 64f3a665f..80bc89d3e 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -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" @@ -200,6 +201,7 @@ Example: confmax => $confmax, maxrecords => $maxrecords, offset => $offset, + totals => $totals, opt => \%opt ) %> @@ -220,16 +222,6 @@ if ( !$curuser->access_right('Download report data') ) { $type = '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; @@ -253,6 +245,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'} ) { @@ -264,16 +262,16 @@ 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; - - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). $agentnums_sql; + if ( $query->{'extra_sql'} =~ /\bWHERE\b/i or keys %{$query->{hashref}} ) { + $query->{'extra_sql'} .= " AND $agentnums_sql"; + $count_query .= " AND $agentnums_sql"; + } else { + $query->{'extra_sql'} .= " WHERE $agentnums_sql"; + $count_query .= " WHERE $agentnums_sql"; + } if ( $opt{'agent_pos'} || $opt{'agent_pos'} eq '0' and scalar($curuser->agentnums) > 1 ) { @@ -304,13 +302,13 @@ 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 = $opt{'query'}{'table'}; + my $table = $query->{'table'}; - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). + $count_query .= + ( $count_query =~ /\bWHERE\b/i ? ' AND ' : ' WHERE ' ). "( $table.disabled = '' OR $table.disabled IS NULL )"; } elsif ( $opt{'disabled_statuspos'} @@ -357,10 +355,6 @@ my($confmax, $maxrecords, $offset ); 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:/ - } unless ( $type eq 'html-print' ) { @@ -393,61 +387,56 @@ $order_by = $cgi->param('order_by') if $cgi->param('order_by'); my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; -if ( ref($opt{query}) ) { - - my @query; - if (ref($opt{query}) eq 'HASH') { - @query = ( $opt{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"; - } else { - warn "unparsable query order_by: ". $opt{query}->{'order_by'}; - die "unparsable query order_by: ". $opt{query}->{'order_by'}; - } + +my @query; +if (ref($query) eq 'HASH') { + @query = $query; + + if ( $order_by ) { + if ( $query->{'order_by'} ) { + if ( $query->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) { + $query->{'order_by'} = "ORDER BY $order_by, $2"; } else { - $opt{query}->{'order_by'} = "ORDER BY $order_by"; + warn "unparsable query order_by: ". $query->{'order_by'}; + die "unparsable query order_by: ". $query->{'order_by'}; } + } else { + $query->{'order_by'} = "ORDER BY $order_by"; } - - $opt{query}->{'order_by'} .= " $limit"; - - } 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; - } + $query->{'order_by'} .= " $limit"; - #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, - ) - ]; +} elsif (ref($query) eq 'ARRAY') { + # do we still use this? it was for the old 477 report. + @query = @{ $query }; } 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; + die "invalid query reference"; +} - $header ||= $sth->{NAME}; +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 ); +$rows = [ qsearch( [ map { my $query = $_; + ({ map { $_ => $query->{$_} } @param }); + } + @query + ], + #'order_by' => $opt{order_by}. " ". $limit, + ) + ]; + +# 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'}; </%init> |