diff options
Diffstat (limited to 'httemplate/search/elements/grouped-search')
| -rw-r--r-- | httemplate/search/elements/grouped-search/core | 162 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/html | 149 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/html-print | 81 | ||||
| -rw-r--r-- | httemplate/search/elements/grouped-search/xls | 61 |
4 files changed, 453 insertions, 0 deletions
diff --git a/httemplate/search/elements/grouped-search/core b/httemplate/search/elements/grouped-search/core new file mode 100644 index 000000000..ffa8cee39 --- /dev/null +++ b/httemplate/search/elements/grouped-search/core @@ -0,0 +1,162 @@ +<%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; +} + +if ( $opt{show_combined} ) { + # set up group 0 as a combined view + unshift @groups, $totals; + unshift @group_labels, 'All ' . PL($opt{name_singular}) . + ' (' . $totals->num_rows . ')'; + unshift @group_footers, []; # the total footer will suffice + unshift @queries, $base_query->clone; +} + +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..df1471a52 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html @@ -0,0 +1,149 @@ +<%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"; +} + +# not enabled yet; if we need this at some point, enable it on a per-report +# basis and then disable it for search/cust_pay.html, because it's redundant +# to see "Check Check #130108", "Credit card Card #401...", etc. + +## if this is the combined view, add a column for the group key +#if ( $curr_group == 0 and $opt{'show_combined'} ) { +# unshift @{$opt{'header'}}, ''; +# unshift @{$opt{'fields'}}, $opt{group_label}; +# unshift @{$opt{'sort_fields'}}, $opt{group_column} if $opt{'sort_fields'}; +# $opt{'align'} = 'c'.$opt{'align'}; +# foreach (qw(header2 links link_onclicks color size style cell_style xls_format)) { +# if ( $opt{$_} ) { +# unshift @{$opt{$_}}, ''; +# } +# } +#} + +</%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> +% $cgi->delete('type'); +</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..6d9521ba3 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html-print @@ -0,0 +1,81 @@ +<%doc> + +The "printable" view (all groups on one page). +</%doc> +<%init> +my %opt = @_; + +my $group_info = $m->comp('core', %opt, + 'show_combined' => 0 +); +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> |