From a9cb3e5104fe57b0cea12a87a81495d13721f0f1 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 29 Aug 2018 11:39:30 -0400 Subject: [PATCH] LP#1789679: Provide ROLLUP support in report output In Postgres 9.5 and newer, it is simple to generate group subtotal and grand total rows for aggregate queries. The simplest thing would be to spell the grouping clause as GROUP BY ROLLUP (...) rather than GROUP BY ... when there is a mix of aggregate and non-aggregate columns in a report. This branch does just that. The report definition UI now has a checkbox labeled "Calculate grouping subtotals" which enables use of the ROLLUP functionality. Signed-off-by: Mike Rylander --- .../lib/OpenILS/Reporter/SQLBuilder.pm | 22 ++++++++++++++++++- Open-ILS/src/reporter/clark-kent.pl | 1 + Open-ILS/web/opac/locale/en-US/reports.dtd | 1 + Open-ILS/web/reports/oils_rpt_editor.xhtml | 1 + .../web/reports/oils_rpt_report_editor.js | 3 ++- 5 files changed, 26 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm index d98a04fa25..b69291bf7e 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm @@ -80,6 +80,7 @@ sub parse_report { ->set_where( $report->{where} ) ->set_having( $report->{having} ) ->set_order_by( $report->{order_by} ) + ->set_do_rollup( $report->{do_rollup} ) ->set_pivot_data( $report->{pivot_data} ) ->set_pivot_label( $report->{pivot_label} ) ->set_pivot_default( $report->{pivot_default} ); @@ -99,6 +100,11 @@ sub is_subquery { return $self->{_is_subquery}; } +sub do_rollup { + my $self = shift; + return $self->builder->{_do_rollup}; +} + sub pivot_data { my $self = shift; return $self->builder->{_pivot_data}; @@ -114,6 +120,13 @@ sub pivot_default { return $self->builder->{_pivot_default}; } +sub set_do_rollup { + my $self = shift; + my $p = shift; + $self->builder->{_do_rollup} = $p if (defined $p); + return $self; +} + sub set_pivot_default { my $self = shift; my $p = shift; @@ -254,7 +267,14 @@ sub toSQL { my @group_by = $self->group_by_list; - $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by); + # The GROUP BY clause is used to generate distinct rows even if there are no aggregates in the select list + my $rollup_start = 'ROLLUP ('; + my $rollup_end = ')'; + + $rollup_start = $rollup_end = '' + if (!$self->do_rollup or scalar(@group_by) == scalar(@{$self->{_select}})); # No ROLLUP if there are no aggregates, or not requested + + $sql .= " GROUP BY $rollup_start" . join(', ', @group_by) . "$rollup_end\n" if (@group_by); $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} }); $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} }); diff --git a/Open-ILS/src/reporter/clark-kent.pl b/Open-ILS/src/reporter/clark-kent.pl index 72edf1855e..320ee8c5c9 100755 --- a/Open-ILS/src/reporter/clark-kent.pl +++ b/Open-ILS/src/reporter/clark-kent.pl @@ -199,6 +199,7 @@ while (my $r = $sth->fetchrow_hashref) { $b->register_params( $report_data ); $r->{resultset} = $b->parse_report( OpenSRF::Utils::JSON->JSON2perl( $r->{report}->{template}->{data} ) ); + $r->{resultset}->set_do_rollup($report_data->{__do_rollup}) if $report_data->{__do_rollup}; $r->{resultset}->set_pivot_data($report_data->{__pivot_data}) if $report_data->{__pivot_data}; $r->{resultset}->set_pivot_label($report_data->{__pivot_label}) if $report_data->{__pivot_label}; $r->{resultset}->set_pivot_default($report_data->{__pivot_default}) if $report_data->{__pivot_default}; diff --git a/Open-ILS/web/opac/locale/en-US/reports.dtd b/Open-ILS/web/opac/locale/en-US/reports.dtd index 4654292f8b..1e7453abb9 100644 --- a/Open-ILS/web/opac/locale/en-US/reports.dtd +++ b/Open-ILS/web/opac/locale/en-US/reports.dtd @@ -80,6 +80,7 @@ + diff --git a/Open-ILS/web/reports/oils_rpt_editor.xhtml b/Open-ILS/web/reports/oils_rpt_editor.xhtml index c0e44497ca..1101d44dcc 100644 --- a/Open-ILS/web/reports/oils_rpt_editor.xhtml +++ b/Open-ILS/web/reports/oils_rpt_editor.xhtml @@ -93,6 +93,7 @@