From 2b085842554607c08d7b5672b129313008cc619b 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 Signed-off-by: Katrin Fischer Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi --- misc/cronjobs/cleanup_database.pl | 119 ++++++++++++++++++++---------- 1 file changed, 80 insertions(+), 39 deletions(-) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index 940cdf55d6..1400309d74 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 < \$help, - 'confirm' => \$confirm, - 'sessions' => \$sessions, - 'sessdays:i' => \$sess_days, - 'v|verbose' => \$verbose, - 'm|mail:i' => \$mail, - 'zebraqueue:i' => \$zebraqueue_days, - 'merged' => \$purge_merged, - 'import:i' => \$pImport, - 'z3950' => \$pZ3950, - 'logs:i' => \$pLogs, - 'log-module:s' => \@log_modules, - 'preserve-log:s' => \@preserve_logs, - 'messages:i' => \$pMessages, - 'fees:i' => \$fees_days, - 'searchhistory:i' => \$pSearchhistory, - 'list-invites:i' => \$pListShareInvites, - 'restrictions:i' => \$pDebarments, - 'all-restrictions' => \$allDebarments, - 'del-exp-selfreg' => \$pExpSelfReg, - 'del-unv-selfreg:i' => \$pUnvSelfReg, - 'unique-holidays:i' => \$special_holidays_days, - 'temp-uploads' => \$temp_uploads, - 'temp-uploads-days:i' => \$temp_uploads_days, - 'uploads-missing:i' => \$uploads_missing, - 'oauth-tokens' => \$oauth_tokens, - 'statistics:i' => \$pStatistics, - 'deleted-catalog:i' => \$pDeletedCatalog, - 'deleted-patrons:i' => \$pDeletedPatrons, - 'old-issues:i' => \$pOldIssues, - 'old-reserves:i' => \$pOldReserves, - 'transfers:i' => \$pTransfers, + 'h|help' => \$help, + 'confirm' => \$confirm, + 'sessions' => \$sessions, + 'sessdays:i' => \$sess_days, + 'v|verbose' => \$verbose, + 'm|mail:i' => \$mail, + 'zebraqueue:i' => \$zebraqueue_days, + 'merged' => \$purge_merged, + 'import:i' => \$pImport, + 'z3950' => \$pZ3950, + 'logs:i' => \$pLogs, + 'log-module:s' => \@log_modules, + 'preserve-log:s' => \@preserve_logs, + 'messages:i' => \$pMessages, + 'fees:i' => \$fees_days, + 'searchhistory:i' => \$pSearchhistory, + 'list-invites:i' => \$pListShareInvites, + 'restrictions:i' => \$pDebarments, + 'all-restrictions' => \$allDebarments, + 'del-exp-selfreg' => \$pExpSelfReg, + 'del-unv-selfreg:i' => \$pUnvSelfReg, + 'unique-holidays:i' => \$special_holidays_days, + 'temp-uploads' => \$temp_uploads, + 'temp-uploads-days:i' => \$temp_uploads_days, + 'uploads-missing:i' => \$uploads_missing, + 'oauth-tokens' => \$oauth_tokens, + 'statistics:i' => \$pStatistics, + 'deleted-catalog:i' => \$pDeletedCatalog, + 'deleted-patrons:i' => \$pDeletedPatrons, + 'old-issues:i' => \$pOldIssues, + 'old-reserves:i' => \$pOldReserves, + 'transfers:i' => \$pTransfers, 'pseudo-transactions:i' => \$pPseudoTransactions, 'pseudo-transactions-from:s' => \$pPseudoTransactionsFrom, 'pseudo-transactions-to:s' => \$pPseudoTransactionsTo, - 'labels' => \$labels, - 'cards' => \$cards, - 'return-claims' => \$return_claims, - 'jobs-type:s' => \@jobs_types, - 'jobs-days:i' => \$jobs_days, - 'reports:i' => \$reports, + 'labels' => \$labels, + 'cards' => \$cards, + 'return-claims' => \$return_claims, + 'jobs-type:s' => \@jobs_types, + 'jobs-days:i' => \$jobs_days, + 'reports:i' => \$reports, + 'edifact-messages:i' => \$edifact_msg_days, ) || usage(1); # Use default values @@ -217,6 +223,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); @@ -256,6 +263,7 @@ unless ( $sessions || $return_claims || $jobs_days || $reports + || $edifact_msg_days ) { print "You did not specify any cleanup work for the script to do.\n\n"; usage(1); @@ -725,6 +733,16 @@ if ($reports) { } } +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); @@ -872,3 +890,26 @@ sub PurgeSavedReports { }); $sth->execute( $reports ); } + +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.39.5