[Koha-patches] [PATCH] bug 2582 [2/2]: fix NULL issues.issuedate

[Galen Charlton]

Corrects loans whose issuedate was set to NULL
during a renewal as follows:

* First, tries to extract the loan date from
  the statistics table.
* For any remaining rows,  sets the issue date
  to the date of the last renewal.
---
 installer/data/mysql/updatedatabase.pl |   36 ++++++++++++++++++++++++++++++++
 kohaversion.pl                         |    2 +-
 2 files changed, 37 insertions(+), 1 deletions(-)

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 1dd86a1..a8468c9 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -1997,6 +1997,42 @@ if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = '3.01.00.002';
+if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
+    # use statistics where available
+    $dbh->do("
+        ALTER TABLE statistics ADD KEY  tmp_stats (type, itemnumber, borrowernumber)
+    ");
+    $dbh->do("
+        UPDATE issues iss
+        SET issuedate = (
+            SELECT max(datetime)
+            FROM statistics 
+            WHERE type = 'issue'
+            AND itemnumber = iss.itemnumber
+            AND borrowernumber = iss.borrowernumber
+        )
+        WHERE issuedate IS NULL;
+    ");  
+    $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
+
+    # default to last renewal date
+    $dbh->do("
+        UPDATE issues
+        SET issuedate = lastreneweddate
+        WHERE issuedate IS NULL
+        and lastreneweddate IS NOT NULL
+    ");
+
+    my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
+    if ($num_bad_issuedates > 0) {
+        print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
+                     "Please check the issues table in your database.";
+    }
+    print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)";
+    SetVersion($DBversion);
+}
+
 =item DropAllForeignKeys($table)
 
   Drop all foreign keys of the table $table
diff --git a/kohaversion.pl b/kohaversion.pl
index 9a9221f..6449588 100644
--- a/kohaversion.pl
+++ b/kohaversion.pl
@@ -10,7 +10,7 @@
 use strict;
 
 sub kohaversion {
-    our $VERSION = '3.01.00.001';
+    our $VERSION = '3.01.00.002';
     # version needs to be set this way
     # so that it can be picked up by Makefile.PL
     # during install
-- 
1.5.5.GIT