From daf2669405a282a14d35525678d4b8c9d6077002 Mon Sep 17 00:00:00 2001
From: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
Date: Mon, 20 Jul 2020 15:09:30 +0000
Subject: [PATCH] Bug 15563: add label and card creator batch deletion to
 cleanup_database

To test:
For item label batches:
1- Create 3 item label batches of at least 2 items each
2- Perform the following updates via the database
3- In Batch 1, set all items' timestamps to 10+ days prior to today
4- In Batch 2, set 1 item's timestamp to 10+ days prior to today
5- In Batch 3, do not update any timestamps
6- Run cron with --labels 9
7- Confirm batch 1 is deleted, batches 2 and 3 are not

Repeat with card creator batches
8- Create 3 card creator batches of at least 2 items each
9- Perform the following updates via the database
10- In Batch 1, set all cards' timestamps to 10+ days prior to today
11- In Batch 2, set 1 card's timestamp to 10+ days prior to today
12- In Batch 3, do not update any timestamps
13- Run cron with --cards 9
14- Confirm batch 1 is deleted, batches 2 and 3 are noti

Signed-off-by: Kelly McElligott <kelly@bywatersolutions.com>
Signed-off-by: Deb Stephenson <dstephen@dubuque.lib.ia.us>
Signed-off-by: Abbey Holt <aholt@dubuque.lib.ia.us>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
---
 misc/cronjobs/cleanup_database.pl | 47 ++++++++++++++++++++++++++++++-
 1 file changed, 46 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 4f56e9b4d3..85967e7807 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -55,7 +55,7 @@ use Koha::Patron::Messages;
 
 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]
+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]
 
    -h --help          prints this help message, and exits, ignoring all
                       other options
@@ -107,6 +107,9 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
    --pseudo-transactions DAYS   Purge the pseudonymized transactions that have been originally created more than DAYS days ago
                                 DAYS is optional and can be replaced by:
                                     --pseudo-transactions-from YYYY-MM-DD and/or --pseudo-transactions-to YYYY-MM-DD
+   --labels DAYS           Purge item label batches last added to more than DAYS days ago.
+   --cards DAY             Purge card creator batches last added to more than DAYS days ago.
+
 USAGE
     exit $_[0];
 }
@@ -143,6 +146,8 @@ my $pTransfers;
 my ( $pPseudoTransactions, $pPseudoTransactionsFrom, $pPseudoTransactionsTo );
 my $pMessages;
 my $lock_days = C4::Context->preference('LockExpiredDelay');
+my $labels;
+my $cards;
 
 GetOptions(
     'h|help'            => \$help,
@@ -178,6 +183,8 @@ GetOptions(
     'pseudo-transactions:i'      => \$pPseudoTransactions,
     'pseudo-transactions-from:s' => \$pPseudoTransactionsFrom,
     'pseudo-transactions-to:s'   => \$pPseudoTransactionsTo,
+    'labels'            => \$labels,
+    'cards'             => \$cards,
 ) || usage(1);
 
 # Use default values
@@ -224,6 +231,8 @@ unless ( $sessions
     || $pPseudoTransactionsTo
     || $pMessages
     || defined $lock_days && $lock_days ne q{}
+    || $labels
+    || $cards
 ) {
     print "You did not specify any cleanup work for the script to do.\n\n";
     usage(1);
@@ -614,6 +623,42 @@ if (defined $pPseudoTransactions or $pPseudoTransactionsFrom or $pPseudoTransact
     }
 }
 
+if ($labels) {
+    print "Purging item label batches last added to more than $labels days ago.\n" if $verbose;
+    $sth = $dbh->prepare(
+        q{
+        DELETE from creator_batches
+        WHERE batch_id in
+            (SELECT batch_id
+            FROM (SELECT batch_id
+                    FROM creator_batches
+                    WHERE creator='labels'
+                    GROUP BY batch_id
+                    HAVING max(timestamp) < date_sub(curdate(),interval ? day)) a)
+        }
+    );
+    $sth->execute($labels) or die $dbh->errstr;
+    print "Done with purging item label batches last added to more than $labels days ago.\n" if $verbose;
+}
+
+if ($cards) {
+    print "Purging card creator batches last added to more than $cards days ago.\n" if $verbose;
+    $sth = $dbh->prepare(
+        q{
+        DELETE from creator_batches
+        WHERE batch_id in
+            (SELECT batch_id
+            FROM (SELECT batch_id
+                    FROM creator_batches
+                    WHERE creator='patroncards'
+                    GROUP BY batch_id
+                    HAVING max(timestamp) < date_sub(curdate(),interval ? day)) a);
+        }
+    );
+    $sth->execute($cards) or die $dbh->errstr;
+    print "Done with purging card creator batches last added to more than $cards days ago.\n" if $verbose;
+}
+
 exit(0);
 
 sub RemoveOldSessions {
-- 
2.39.5