From c016531c9a3cdcaba584b7beab2c47feeb218e29 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Fri, 8 May 2020 14:02:34 -0400
Subject: [PATCH] Bug 25429: Cleanup Database - remove resolved claims returned
 from db after X days

Add option to cleanup_database script to removed 'resolved' return claims from the database after a specified number of days.

Test Plan:
1) Apply this patch
2) Set the new syspref CleanUpDatabaseReturnClaims to a number of days
3) Run cleanup_database.pl
4) Note resolved claims older than the specified number of days are removed from the database

Bug 25429: Implement system preference, remove command line switch

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>

Signed-off-by: Rebecca Coert <rcoert@arlingtonva.us>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
---
 installer/data/mysql/atomicupdate/bug_25429.perl  |  9 +++++++++
 installer/data/mysql/mandatory/sysprefs.sql       |  1 +
 .../en/modules/admin/preferences/circulation.pref |  6 ++++++
 misc/cronjobs/cleanup_database.pl                 | 15 ++++++++++++++-
 4 files changed, 30 insertions(+), 1 deletion(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_25429.perl

diff --git a/installer/data/mysql/atomicupdate/bug_25429.perl b/installer/data/mysql/atomicupdate/bug_25429.perl
new file mode 100644
index 0000000000..c9d4222f9c
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_25429.perl
@@ -0,0 +1,9 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+    $dbh->do(q{
+        INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES
+        ('CleanUpDatabaseReturnClaims', '', '', 'Sets the age of resolved return claims to delete from the database for cleanup_database.pl', 'Integer' );
+    });
+
+    NewVersion( $DBversion, 25429, "Cleanup Database - remove resolved claims returned from db after X days");
+}
diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql
index 1034313061..040b3c5745 100644
--- a/installer/data/mysql/mandatory/sysprefs.sql
+++ b/installer/data/mysql/mandatory/sysprefs.sql
@@ -134,6 +134,7 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
 ('ClaimReturnedChargeFee', 'ask', 'ask|charge|no_charge', 'Controls whether or not a lost item fee is charged for return claims', 'Choice'),
 ('ClaimReturnedLostValue', '', '', 'Sets the LOST AV value that represents "Claims returned" as a lost value', 'Free'),
 ('ClaimReturnedWarningThreshold', '', '', 'Sets the number of return claims past which the librarian will be warned the patron has many return claims', 'Integer'),
+('CleanUpDatabaseReturnClaims', '', '', 'Sets the age of resolved return claims to delete from the database for cleanup_database.pl', 'Integer' ),
 ('CoceHost', '', NULL, 'Coce server URL', 'Free'),
 ('CoceProviders', '', 'aws,gb,ol', 'Coce providers', 'multiple'),
 ('COinSinOPACResults','1','','If ON, use COinS in OPAC search results page.  NOTE: this can slow down search response time significantly','YesNo'),
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
index 9acc59f50e..d120274031 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref
@@ -1233,3 +1233,9 @@ Circulation:
             - pref: ClaimReturnedWarningThreshold
               class: integer
             - items.
+        -
+            - Remove resolved return claims older than
+            - pref: CleanUpDatabaseReturnClaims
+              class: integer
+            - days.
+            - <span class="hint">This system preference is used by the cleanup_database.pl cronjob.</span>
diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 062efe6041..d7dfc2f12c 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -88,7 +88,7 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
                          days.  Defaults to 14 days if no days specified.
    --restrictions DAYS   purge patrons restrictions expired since more than DAYS days.
                          Defaults to 30 days if no days specified.
-    --all-restrictions   purge all expired patrons restrictions.
+   --all-restrictions   purge all expired patrons restrictions.
    --del-exp-selfreg  Delete expired self registration accounts
    --del-unv-selfreg  DAYS  Delete unverified self registrations older than DAYS
    --unique-holidays DAYS  Delete all unique holidays older than DAYS
@@ -126,6 +126,7 @@ my $pZ3950;
 my $pListShareInvites;
 my $pDebarments;
 my $allDebarments;
+my $return_claims = C4::Context->preference('CleanUpDatabaseReturnClaims');
 my $pExpSelfReg;
 my $pUnvSelfReg;
 my $fees_days;
@@ -505,6 +506,18 @@ if ($pStatistics) {
     }
 }
 
+if ($return_claims) {
+    print "Purging return claims older than $return_claims days.\n" if $verbose;
+    $sth = $dbh->prepare(
+        q{
+            DELETE FROM return_claims
+            WHERE resolved_on < DATE_SUB(CURDATE(), INTERVAL ? DAY)
+        }
+    );
+    $sth->execute($return_claims);
+    print "Done with purging return claims.\n" if $verbose;
+}
+
 if ($pDeletedCatalog) {
     print "Purging deleted catalog older than $pDeletedCatalog days.\n"
       if $verbose;
-- 
2.31.0