From faddf354577f312b30788c6b731c503991bc8fe4 Mon Sep 17 00:00:00 2001
From: Paul Hoffman
Date: Tue, 17 Sep 2019 13:10:01 -0400
Subject: [PATCH] Bug 23626 - Add a system preference to limit the number of
rows of data used in a chart when viewing report results
The number of rows generated by a report is potentially unlimited; this has the potential to exhaust all available memory, bringing a Koha server down to its knees.
The attached patch introduces a system preference to limit the number of rows used when charting report results; at this time, and with bug #23624 fixed, that is the only aspect of reporting that loads all results into memory at once.
---
installer/data/mysql/sysprefs.sql | 1 +
.../prog/en/modules/reports/guided_reports_start.tt | 1 +
reports/guided_reports.pl | 3 +++
3 files changed, 5 insertions(+)
diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql
index 6ce6ee15e0..78f5bef895 100644
--- a/installer/data/mysql/sysprefs.sql
+++ b/installer/data/mysql/sysprefs.sql
@@ -508,6 +508,7 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
('RentalsInNoissuesCharge','1',NULL,'Rental charges block checkouts (added to noissuescharge).','YesNo'),
('ReplyToDefault','',NULL,'Use this email address as the replyto in emails','Free'),
('ReportsLog','0',NULL,'If ON, log information about reports.','YesNo'),
+('ReportsMaxRowsInChart','10000',NULL,'If greater than 0, sets the maximum number of rows that will be used when charting results of guided reports','Integer'),
('RequestOnOpac','1',NULL,'If ON, globally enables patron holds on OPAC','YesNo'),
('RequireStrongPassword','1','','Require a strong login password for staff and patrons','YesNo'),
('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice'),
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
index de0409868a..bea40abcf6 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -719,6 +719,7 @@ canned reports and writing custom SQL reports.
[% name | html %]
[% IF ( notes ) %]Notes: [% notes | html %]
[% END %]
[% IF ( unlimited_total ) %]Total number of results: [% unlimited_total | html %][% IF unlimited_total > limit %] ([% limit | html %] shown)[% END %].
[% END %]
+[% IF ( allresults.size == max_rows_in_chart ) %]Data in chart have been limited to [% max_rows_in_chart | html %] rows.[% END %]
Report SQL:
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index bb4e87efe4..277e1e5063 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -829,6 +829,7 @@ elsif ($phase eq 'Run this report'){
$template->param(header_types => $header_types);
my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
my ($sth2, $errors2) = execute_query($sql);
+ my $max_rows = C4::Context->preference('ReportsMaxRowsInChart');
my $total = nb_rows($sql) || 0;
unless ($sth) {
die "execute_query failed to return sth for report $report_id: $sql";
@@ -842,6 +843,7 @@ elsif ($phase eq 'Run this report'){
while (my $row = $sth2->fetchrow_arrayref()) {
my @cells = map { +{ cell => $_ } } @$row;
push @allrows, { cells => \@cells };
+ last if $max_rows && @allrows >= $max_rows;
}
}
@@ -857,6 +859,7 @@ elsif ($phase eq 'Run this report'){
$template->param(
'results' => \@rows,
'allresults' => \@allrows,
+ 'max_rows_in_chart' => $max_rows,
'sql' => $sql,
original_sql => $original_sql,
'id' => $report_id,
--
2.20.1 (Apple Git-117)