From 1f1520c05bb41a864436d50281e78ba108b991c3 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 Signed-off-by: Chris Sharp --- .../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 69eab42c24..22bee1a01a 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 c18583ada1..0aec6ca7f7 100644 --- a/Open-ILS/web/reports/oils_rpt_editor.xhtml +++ b/Open-ILS/web/reports/oils_rpt_editor.xhtml @@ -93,6 +93,7 @@
  • &reports.oils_rpt_editor.excel_output;
  • &reports.oils_rpt_editor.csv_output;
    • +
  • &reports.oils_rpt_editor.do_rollup;
  • &reports.oils_rpt_editor.html_output;
      diff --git a/Open-ILS/web/reports/oils_rpt_report_editor.js b/Open-ILS/web/reports/oils_rpt_report_editor.js index ae69b467bc..a223c42df4 100644 --- a/Open-ILS/web/reports/oils_rpt_report_editor.js +++ b/Open-ILS/web/reports/oils_rpt_report_editor.js @@ -141,6 +141,7 @@ oils_rpt_editor_pivot_data setSelector(DOM.oils_rpt_editor_pivot_label, rpt_data.__pivot_label); if (rpt_data.__pivot_data) setSelector(DOM.oils_rpt_editor_pivot_data, rpt_data.__pivot_data); + DOM.oils_rpt_editor_do_rollup.checked = rpt_data.__do_rollup == '1'; } if (run = this.last_run) { @@ -353,7 +354,7 @@ oilsRptReportEditor.prototype.save = function(options) { data.__pivot_label = getSelectorVal(DOM.oils_rpt_editor_pivot_label); data.__pivot_data = getSelectorVal(DOM.oils_rpt_editor_pivot_data); } - + data.__do_rollup = DOM.oils_rpt_editor_do_rollup.checked ? '1' : '0'; data = js2JSON(data); _debug("complete report data = "+data); -- 2.43.2