From 4ed853df6083d54b87346bf5147166020c0649cf 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
---
 misc/cronjobs/cleanup_database.pl | 32 ++++++++++++++++++++++----------
 1 file changed, 22 insertions(+), 10 deletions(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 8a116ba9a4..e13ea62160 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -90,8 +90,8 @@ Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueu
    --old-issues DAYS       Purge checkouts (old_issues) returned more than DAYS days ago.
    --old-reserves DAYS     Purge reserves (old_reserves) more than DAYS old.
    --transfers DAYS        Purge transfers completed more than DAYS day ago.
-   --labels DAYS           Purge label items added more than DAYS days ago.
-   --cards DAY             Purge card creator patrons added more than DAYS days ago.
+   --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];
@@ -490,27 +490,39 @@ if ($pTransfers) {
 }
 
 if ($labels) {
-    print "Purging label items older than $labels days from batches.\n" if $verbose;
+    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 timestamp < date_sub(curdate(), interval ? day) AND creator='Labels'
+        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 label items older than $labels days from batches.\n" if $verbose;
+    print "Done with purging item label batches last added to more than $labels days ago.\n" if $verbose;
 }
 
 if ($cards) {
-    print "Purging card creator patrons older than $cards days from batches.\n" if $verbose;
+    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 timestamp < date_sub(curdate(), interval ? day) AND creator='Patroncards'
+        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 patrons older than $cards days from batches.\n" if $verbose;
+    print "Done with purging card creator batches last added to more than $cards days ago.\n" if $verbose;
 }
 
 exit(0);
-- 
2.11.0