From bdf4844a2b005e53263b50c646f45a9cd3cea4c4 Mon Sep 17 00:00:00 2001
From: Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz>
Date: Thu, 11 Jul 2024 12:05:28 +1200
Subject: [PATCH] Bug 31143: Update patrons with dates of 0000-00-00 to NULL

To test:
1) Change a patron's value of one or more of the following to
0000-00-00: dateofbirth dateenrolled dateexpiry date_renewed lastseen
updated_on debarred
2) Run the misc/maintenance/search_for_data_inconsistencies.pl script
3) Run the fix_invalid_patron_dates.pl (-c -v)
4) Notice the value has changed from 0000-00-00 to NULL

Sponsored by: Toi Ohomai Institute of Technology, New Zealand and
Catalyst IT
---
 misc/cronjobs/fix_invalid_patron_dates.pl     | 58 +++++++++++++++++++
 .../search_for_data_inconsistencies.pl        | 20 +++++++
 2 files changed, 78 insertions(+)
 create mode 100755 misc/cronjobs/fix_invalid_patron_dates.pl

diff --git a/misc/cronjobs/fix_invalid_patron_dates.pl b/misc/cronjobs/fix_invalid_patron_dates.pl
new file mode 100755
index 00000000000..01f94d0b5dd
--- /dev/null
+++ b/misc/cronjobs/fix_invalid_patron_dates.pl
@@ -0,0 +1,58 @@
+#!/usr/bin/perl
+
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it
+# under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 3 of the License, or
+# (at your option) any later version.
+#
+# Koha is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with Koha; if not, see <http://www.gnu.org/licenses>.
+
+use Modern::Perl;
+
+use C4::Context;
+use C4::Log qw(cronlogaction);
+use Getopt::Long qw( GetOptions );
+use Pod::Usage qw( pod2usage );
+use Koha::Logger;
+use Koha::Patrons;
+use Koha::DateUtils qw( dt_from_string );
+use Koha::Script -cron;
+use Data::Dumper;
+
+my $verbose = 0;
+my $doit = 0;
+
+GetOptions(
+    'v|verbose'       => \$verbose,
+    'c|confirm'       => \$doit,
+);
+
+my $count = 0;
+
+# search patrons that have date fields that are 0000-00-00
+foreach my $date_type (qw(dateofbirth dateenrolled dateexpiry date_renewed lastseen updated_on debarred)) {
+    my $borrowers = Koha::Patrons->search( { $date_type=>'0000-00-00' } );
+
+        while (my $borrower = $borrowers->next()) {
+            $count++;
+            if ($doit) { # if confirm flag was run with cronjob
+                $borrower->$date_type(undef)->store();
+            }
+        }
+
+}
+
+if ($verbose) {
+  print "$count invalid dates found\n";
+  if ($doit) {
+    print "$count invalid dates fixed\n"
+  }
+}
diff --git a/misc/maintenance/search_for_data_inconsistencies.pl b/misc/maintenance/search_for_data_inconsistencies.pl
index 6719cecedc9..d4dcd1c1f20 100755
--- a/misc/maintenance/search_for_data_inconsistencies.pl
+++ b/misc/maintenance/search_for_data_inconsistencies.pl
@@ -326,6 +326,25 @@ use C4::Biblio qw( GetMarcFromKohaField );
     }
 }
 
+{
+    my $count = 0;
+
+    # search patrons that have date fields that are 0000-00-00
+    foreach my $date_type (qw(dateofbirth dateenrolled dateexpiry date_renewed lastseen updated_on debarred)) {
+        my $borrowers = Koha::Patrons->search( { $date_type=>'0000-00-00' } );
+        while (my $borrower = $borrowers->next()) {
+            $count++;
+        }
+    }
+
+    new_section("$count invalid patron dates found");
+
+    if ($count > 0) {
+        new_hint("You may change the date with script: misc/cronjobs/fix_invalid_patron_dates.pl (-c -v)");
+    }
+
+}
+
 {
     my @loop_borrowers_relationships;
     my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
@@ -450,5 +469,6 @@ Catch data inconsistencies in Koha database
   * Item types defined in items or biblioitems must be defined in the itemtypes table
 * Invalid MARCXML in bibliographic records
 * Patrons with invalid category types due to lower and upper age limits
+* Patrons with 0000-00-00 dates in fields dateofbirth, dateenrolled, dateexpiry, date_renewed, lastseen, updated_on, debarred
 
 =cut
-- 
2.39.2