From 6747ee3ee34de3eff118293d04ac3dfe78cb0e90 Mon Sep 17 00:00:00 2001
From: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>
Date: Wed, 15 Feb 2023 14:20:54 +0000
Subject: [PATCH] Bug 30069: Add edifact-messages to cleanup_database.pl

This patch allows users to clear out old edifact_messages using the cleanup_database script. The number of days can either be set in the CLI or the default value of 365 can be used.

Test plan:
1) Ensure you have some EDI orders or even just some dummy messages in the edifact_messages table
2) Run perl misc/cronjobs/cleanup_database.pl --edifact-messages 100 --verbose (Change the number of days according to the data in your table)
3) The response should show a number of messages that would have been deleted
4) Run perl misc/cronjobs/cleanup_database.pl --edifact-messages 100 --verbose --confirm
5) The response should now show the same number of messages have been deleted
6) Check your edifact_messages table to confirm that the data has been deleted

Sponsored-by: PTFS Europe
---
 misc/cronjobs/cleanup_database.pl | 42 ++++++++++++++++++++++++++++++-
 1 file changed, 41 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 0c32bc02ca2..a83227f2a2e 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -29,6 +29,7 @@ use constant DEFAULT_SHARE_INVITATION_EXPIRY_DAYS => 14;
 use constant DEFAULT_DEBARMENTS_PURGEDAYS         => 30;
 use constant DEFAULT_JOBS_PURGEDAYS               => 1;
 use constant DEFAULT_JOBS_PURGETYPES              => qw{ update_elastic_index };
+use constant DEFAULT_EDIFACT_MSG_PURGEDAYS        => 365;
 
 use Koha::Script -cron;
 use C4::Context;
@@ -49,10 +50,11 @@ use Koha::Item::Transfers;
 use Koha::PseudonymizedTransactions;
 use Koha::Patron::Messages;
 use Koha::Patron::Debarments qw( DelDebarment );
+use Koha::Database;
 
 sub usage {
     print STDERR <<USAGE;
-Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] [--searchhistory DAYS] [--restrictions DAYS] [--all-restrictions] [--fees DAYS] [--temp-uploads] [--temp-uploads-days DAYS] [--uploads-missing 0|1 ] [--statistics DAYS] [--deleted-catalog DAYS] [--deleted-patrons DAYS] [--old-issues DAYS] [--old-reserves DAYS] [--transfers DAYS] [--labels DAYS] [--cards DAYS] [--bg-days DAYS [--bg-type TYPE] ]
+Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] [--searchhistory DAYS] [--restrictions DAYS] [--all-restrictions] [--fees DAYS] [--temp-uploads] [--temp-uploads-days DAYS] [--uploads-missing 0|1 ] [--statistics DAYS] [--deleted-catalog DAYS] [--deleted-patrons DAYS] [--old-issues DAYS] [--old-reserves DAYS] [--transfers DAYS] [--labels DAYS] [--cards DAYS] [--bg-days DAYS [--bg-type TYPE] ] [--edifact-messages DAYS]
 
    -h --help          prints this help message, and exits, ignoring all
                       other options
@@ -113,6 +115,8 @@ 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.
+   --edifact-messages DAYS   Purge entries from edifact_messages table older than DAYS days.
+                             Defaults to 365 days if no days specified.
 USAGE
     exit $_[0];
 }
@@ -156,6 +160,7 @@ my @log_modules;
 my @preserve_logs;
 my $jobs_days;
 my @jobs_types;
+my $edifact_msg_days;
 
 my $command_line_options = join(" ",@ARGV);
 
@@ -200,6 +205,7 @@ GetOptions(
     'return-claims'     => \$return_claims,
     'jobs-type:s'       => \@jobs_types,
     'jobs-days:i'       => \$jobs_days,
+    'edifact-messages:i' => \$edifact_msg_days,
 ) || usage(1);
 
 # Use default values
@@ -214,6 +220,7 @@ $pDebarments       = DEFAULT_DEBARMENTS_PURGEDAYS         if defined($pDebarment
 $pMessages         = DEFAULT_MESSAGES_PURGEDAYS           if defined($pMessages)         && $pMessages == 0;
 $jobs_days         = DEFAULT_JOBS_PURGEDAYS               if defined($jobs_days)         && $jobs_days == 0;
 @jobs_types        = (DEFAULT_JOBS_PURGETYPES)            if $jobs_days                  && @jobs_types == 0;
+$edifact_msg_days  = DEFAULT_EDIFACT_MSG_PURGEDAYS        if defined($edifact_msg_days)  && $edifact_msg_days == 0;
 
 if ($help) {
     usage(0);
@@ -252,6 +259,7 @@ unless ( $sessions
     || $cards
     || $return_claims
     || $jobs_days
+    || $edifact_msg_days
 ) {
     print "You did not specify any cleanup work for the script to do.\n\n";
     usage(1);
@@ -713,6 +721,16 @@ if ($jobs_days) {
     }
 }
 
+if($edifact_msg_days) {
+    print "Purging edifact messages older than $edifact_msg_days days.\n" if $verbose;
+    my $count = PurgeEdifactMessages($edifact_msg_days, $confirm);
+    if ( $verbose ) {
+        say $confirm
+          ? sprintf( "Done with purging %d edifact messages", $count )
+          : sprintf( "%d edifact messages would have been removed", $count );
+    }
+}
+
 cronlogaction({ action => 'End', info => "COMPLETED" });
 
 exit(0);
@@ -851,3 +869,25 @@ sub DeleteSpecialHolidays {
     print "Removed $count unique holidays\n" if $verbose;
 }
 
+sub PurgeEdifactMessages {
+    my ( $days, $doit ) = @_;
+
+    my $count = 0;
+    my $schema = Koha::Database->new()->schema();
+
+    $sth = $dbh->prepare(
+        q{
+            SELECT id
+            FROM edifact_messages
+            WHERE transfer_date < date_sub(curdate(), INTERVAL ? DAY)
+        }
+    );
+    $sth->execute($days) or die $dbh->errstr;
+
+    while ( my ($msg_id) = $sth->fetchrow_array) {
+        my $msg = $schema->resultset('EdifactMessage')->find($msg_id);
+        $msg->delete if $doit;
+        $count++;
+    }
+    return $count;
+}
-- 
2.37.1 (Apple Git-137.1)