From fe6a03c43a61100f5df1b273ff671eee4326809c Mon Sep 17 00:00:00 2001
From: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Date: Mon, 21 Nov 2022 20:00:57 +0000
Subject: [PATCH] Bug 17350: Purge old saved reports via cleanup_database

To test:
apply patch
1 - have or create a report
2 - run your report via the command line with --store-results. Do this twice.
3 - update the saved_reports table to set date_run for one of your two saved results set to a datetime more than 5 days ago
4 - perl misc/cronjobs/cleanup_database.pl --reports 5 --verbose --confirm
5 - Koha tells you its deleting saved reports data from more than 5 days ago
6 - confirm in the database and the staff interface that it's done so

Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 misc/cronjobs/cleanup_database.pl | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 0c32bc02ca..6760b32d86 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -113,6 +113,7 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
    --jobs-days DAYS        Purge all finished background jobs this many days old. Defaults to 1 if no DAYS provided.
    --jobs-type TYPES       What type of background job to purge. Defaults to "update_elastic_index" if omitted
                            Specifying "all" will purge all types. Repeatable.
+   --reports DAYS          Purge reports data saved more than DAYS days ago.
 USAGE
     exit $_[0];
 }
@@ -156,6 +157,7 @@ my @log_modules;
 my @preserve_logs;
 my $jobs_days;
 my @jobs_types;
+my $reports;
 
 my $command_line_options = join(" ",@ARGV);
 
@@ -200,6 +202,7 @@ GetOptions(
     'return-claims'     => \$return_claims,
     'jobs-type:s'       => \@jobs_types,
     'jobs-days:i'       => \$jobs_days,
+    'reports:i'           => \$reports,
 ) || usage(1);
 
 # Use default values
@@ -252,6 +255,7 @@ unless ( $sessions
     || $cards
     || $return_claims
     || $jobs_days
+    || $reports
 ) {
     print "You did not specify any cleanup work for the script to do.\n\n";
     usage(1);
@@ -713,6 +717,14 @@ if ($jobs_days) {
     }
 }
 
+if ($reports) {
+    if ( $confirm ) {
+        PurgeSavedReports($reports);
+    } if ( $verbose ) {
+        say "Purging reports data saved more than $reports days ago.\n";
+    }
+}
+
 cronlogaction({ action => 'End', info => "COMPLETED" });
 
 exit(0);
@@ -851,3 +863,12 @@ sub DeleteSpecialHolidays {
     print "Removed $count unique holidays\n" if $verbose;
 }
 
+sub PurgeSavedReports {
+    my ( $reports ) = @_;
+
+    my $sth = $dbh->prepare(q{
+            DELETE FROM saved_reports
+            WHERE date(date_run) < DATE_SUB(CURDATE(),INTERVAL ? DAY );
+        });
+    $sth->execute( $reports );
+}
-- 
2.39.5