From e18cfe0731941a6aeba841e49b7ad09a5fd04cb2 Mon Sep 17 00:00:00 2001 From: Kyle M Hall 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 Signed-off-by: Rebecca Coert Signed-off-by: Victor Grousset/tuxayo Signed-off-by: Marcel de Rooy Signed-off-by: Jonathan Druart --- 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 d797c074ae..5607163a6a 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -139,6 +139,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 8a3cfc4dc7..6c2a33bc6c 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 @@ -1265,3 +1265,9 @@ Circulation: - pref: ClaimReturnedWarningThreshold class: integer - items. + - + - Remove resolved return claims older than + - pref: CleanUpDatabaseReturnClaims + class: integer + - days. + - This system preference is used by the cleanup_database.pl cronjob. diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index 17b57d79fc..aefdf4cd44 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 @@ -129,6 +129,7 @@ my $pZ3950; my $pListShareInvites; my $pDebarments; my $allDebarments; +my $return_claims = C4::Context->preference('CleanUpDatabaseReturnClaims'); my $pExpSelfReg; my $pUnvSelfReg; my $fees_days; @@ -526,6 +527,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.39.5