From a3514b5d40bd16e08a02e33c691d7b21fd3f49a5 Mon Sep 17 00:00:00 2001
From: Hammat Wele <hammat.wele@inlibro.com>
Date: Thu, 23 Mar 2023 21:13:48 +0000
Subject: [PATCH] Bug 21214: (fix) Change letter_code with letter_id in the sql
 query and code modification

---
 misc/cronjobs/cleanup_database.pl | 48 +++++++++++++++----------------
 1 file changed, 24 insertions(+), 24 deletions(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index a4cf1ec2b1..e9b48aed19 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -73,7 +73,7 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
                       Defaults to 30 days if no days specified.
    --keep-lc LC[,LC]* only used in conjunction with -m/--mail.
                       Keeps certain emails in the message_queue based on a
-                      comma-separated list of LETTER_CODES
+                      comma-separated list of letter_id
    --merged           purged completed entries from need_merge_authorities.
    --messages DAYS    purge entries from messages table older than DAYS days.
                       Defaults to 365 days if no days specified.
@@ -135,7 +135,7 @@ my $sess_days;
 my $verbose;
 my $zebraqueue_days;
 my $mail;
-my $letter_codes;
+my $letter_id;
 my $purge_merged;
 my $pImport;
 my $pLogs;
@@ -180,7 +180,7 @@ GetOptions(
     'sessdays:i'                 => \$sess_days,
     'v|verbose'                  => \$verbose,
     'm|mail:i'                   => \$mail,
-    'keep-lc:s'                  => \$letter_codes,
+    'keep-lc:s'                  => \$letter_id,
     'zebraqueue:i'               => \$zebraqueue_days,
     'merged'                     => \$purge_merged,
     'import:i'                   => \$pImport,
@@ -286,11 +286,6 @@ say "Confirm flag not passed, running in dry-run mode..." unless $confirm;
 
 cronlogaction({ info => $command_line_options });
 
-if (!$mail && $letter_codes) {
-    print "You should not specify --keep-lc if you're not cleaning emails.\n\n";
-    usage(1);
-}
-
 my $dbh = C4::Context->dbh();
 my $sth;
 my $sth2;
@@ -344,22 +339,27 @@ if ($zebraqueue_days) {
 if ($mail) {
     my $count = 0;
     print "Mail queue purge triggered for $mail days.\n" if $verbose;
-    print "Not deleting emails of type $letter_codes.\n" if ($verbose && $letter_codes);
-    my (@codes, $quoted_codes);
-    if ($letter_codes) {
-        $letter_codes =~ s/ //g;
-        @codes = map { "$_" } split(',', $letter_codes);
-        $quoted_codes = join ",", ("?") x @codes;
-    }
-
-    $sth = $dbh->prepare("
-        DELETE FROM message_queue
-        WHERE time_queued < date_sub(curdate(), INTERVAL ? DAY)
-          AND letter_code NOT IN ( $quoted_codes )");
-    $sth->execute($mail, @codes) or die $dbh->errstr;
-    $count = $sth->rows;
-    $sth->finish;
-    print "$count messages were deleted from the mail queue.\nDone with message_queue purge.\n" if $verbose;
+    print "Not deleting emails of type $letter_id.\n" if ($verbose && $letter_id);
+    my $query = q{
+                    DELETE FROM message_queue
+                    WHERE time_queued < date_sub(curdate(), INTERVAL ? DAY)
+                };
+    my @bind_params = ($mail);
+    if ($letter_id) {
+        $letter_id =~ s/ //g;
+        my @codes = map { "$_" } split(',', $letter_id);
+        $query .= " AND letter_id NOT IN ( ".(join ",", ("?") x @codes)." )";
+        push @bind_params, @codes;
+    }
+    $sth = $dbh->prepare($query);
+    if ( $confirm ) {
+        $sth->execute(@bind_params) or die $dbh->errstr;
+        $count = $sth->rows;
+    }
+    if ( $verbose ) {
+        say $confirm ? "$count messages were deleted from the mail queue." : "Message from message_queue would have been deleted";
+        say "Done with message_queue purge.";
+    }
 }
 
 if ($purge_merged) {
-- 
2.34.1