From 6747ee3ee34de3eff118293d04ac3dfe78cb0e90 Mon Sep 17 00:00:00 2001 From: Matt Blenkinsop 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 < \$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)