3 my $group_data = $m->comp( 'elements/grouped-search/core',
6 'table' => 'tablename',
7 'hashref' => { 'field' => 'value', ... }, # optional
11 #'order_by' is not allowed
13 'group_column' => 'classnum', # must be a single field
14 # 'group_table' => 'mytable', # optional; defaults to the one in the query
15 'group_label' => 'classname',
16 'subtotal' => { amount => 'sum(amount)', ... }
17 # The subtotal row will be generated as an FS::* object of the same type
18 # as all the detail rows.
19 # The only fields present will be the grouping column and any subtotals
20 # defined in 'subtotal'. Key is a field name (in the FS::* object),
21 # value is an SQL aggregate expression.
23 # How to display that object: arrayref of literal strings or coderefs
24 # (the subtotal object will be passed). These will be placed in table
25 # cells, and can contain HTML.
27 [ 'Subtotal', sub { sprintf('%.2f', $_[0]->amount) }, ],
29 # The subtotal aggregates will also be calculated for the entire data
30 # set, without grouping. How to display those (if not present, will
33 [ 'Total', sub { sprintf('%.2f', $_[0]->amount) }, ],
35 'order_by' => '_date', # SQL ordering expression for the detail rows
37 'header' => [ ... ], # per search.html
43 num => number of groups,
44 groups => [ group summary objects,
45 with group key columns, subtotals, and "num_rows" ],
46 group_labels => [ label strings ],
47 group_footers => [ formatted subtotal row arrayrefs ],
48 queries => [ FS::Query objects to evaluate to get detail rows ],
53 my $conf = FS::Conf->new;
58 my $base_query = FS::Query->new( $opt{'query'} );
59 my $main_table = $base_query->{table};
61 # $opt{subtotal} is a hashref of field names => SQL aggregate expressions.
63 my @subtotal_names = sort keys(%{ $opt{'subtotal'} });
64 my @subtotal_exprs = map { $opt{'subtotal'}->{$_} } @subtotal_names;
66 # qualify the group key to the main table unless otherwise specified
67 my $group_table = $opt{group_table} || $main_table;
68 my $group_key = $group_table . '.' . $opt{group_column};
71 $group_key, # need this to identify groups
72 'COUNT(*) as num_rows', # and this for pagination
73 map { '(' . $subtotal_exprs[$_] . ') AS "' . $subtotal_names[$_] . '"' }
74 0 .. (scalar(@subtotal_names) - 1)
77 my $group_query = $base_query->clone;
78 $group_query->{order_by} = "GROUP BY $group_key";
79 $group_query->{select} = join(',', @select);
80 # keep table, addl_from, extra_sql, and hashref as they are
82 #warn Dumper($group_query); #DEBUG
84 # run the group query and create a tab label for each group
85 my @groups = $group_query->qsearch;
87 # also run it with no grouping, and just get overall totals
88 my $total_query = $base_query->clone;
89 shift @select; # remove $group_key
90 $total_query->{select} = join(',', @select);
91 $total_query->{order_by} = '';
93 my $totals = $total_query->qsearchs;
96 if ( $cgi->param('order_by') =~ /^(\w+\.?\w*(?: DESC)?)$/ ) {
99 if ( $opt{order_by} ) {
100 $order_by .= ', ' if length($order_by); # user selection takes priority
101 $order_by .= $opt{order_by};
103 $order_by = "ORDER BY $order_by " if $order_by;
104 $base_query->{order_by} = $order_by;
106 my $group_label = $opt{group_label} || $opt{group_column};
107 my (@group_labels, @group_footers, @queries);
108 for my $i (0 .. scalar(@groups) - 1) {
109 my $label = $groups[$i]->$group_label . ' (' . $groups[$i]->num_rows . ')';
110 push @group_labels, $label;
113 if ($opt{'subtotal_row'} and @groups > 1) {
115 exists($opt{'subtotal_row'}[$col]) or exists($opt{'header'}[$col]);
118 my $value = $opt{'subtotal_row'}[$col] || '';
119 $value = &$value( $groups[$i] ) if ref($value) eq 'CODE';
120 $footer[$col] = $value;
123 push @group_footers, \@footer;
125 my $detail_query = $base_query->clone;
126 my $group_key_value = $groups[$i]->get( $opt{group_column} );
127 $group_key_value = dbh->quote($group_key_value)
128 unless looks_like_number($group_key_value);
129 $detail_query->and_where("$group_key = $group_key_value");
130 push @queries, $detail_query;
133 if ( $opt{show_combined} ) {
135 # set up group 0 as a combined view
136 unshift @groups, $totals;
137 unshift @group_labels, 'All ' . PL($opt{name_singular}) .
138 ' (' . $totals->num_rows . ')';
139 unshift @group_footers, []; # the total footer will suffice
140 unshift @queries, $base_query->clone;
145 if ($opt{'total_row'}) {
147 exists($opt{'total_row'}[$col]) or exists($opt{'header'}[$col]);
150 my $value = $opt{'total_row'}[$col] || '';
151 $value = &$value( $totals ) if ref($value) eq 'CODE';
152 $total_footer[$col] = $value;
157 num => scalar(@groups),
159 group_labels => \@group_labels,
160 group_footers => \@group_footers,
161 queries => \@queries,
162 total_footer => \@total_footer,