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)