[Patches] [PATCH] Bug 6868 : Script to clean up accountno

[koha-patchbot at kohaaloha.com]

From: Chris Cormack <chrisc at catalyst.net.nz>
Date: Fri, 2 Dec 2011 19:19:14 +1300
Subject: [PATCH] Bug 6868 : Script to clean up accountno

This script cleans up damage to the accountlines table caused by the
writeoff()
subroutine in members/pay.pl (pre bug 3498 integration).

Includes two flags:

-t  Runs this in test mode.  The database is not actually updated; if
omitted, the changes run on database directly.

-v  Prints out every change made to screen; if omitted, just shows
progress counter

This script can take some time to run; 8000 updates in 5:30 minutes on a
powerful system, in my experience.
---
 misc/maintenance/cleanup_accountno.pl |   89 +++++++++++++++++++++++++++++++++
 1 files changed, 89 insertions(+), 0 deletions(-)
 create mode 100644 misc/maintenance/cleanup_accountno.pl

diff --git a/misc/maintenance/cleanup_accountno.pl b/misc/maintenance/cleanup_accountno.pl
new file mode 100644
index 0000000..bcabf3e
--- /dev/null
+++ b/misc/maintenance/cleanup_accountno.pl
@@ -0,0 +1,89 @@
+#!/usr/bin/perl
+
+# Copyright 2011 Ian Walls and ByWater Solutions
+#
+# This script is intended to help Koha libraries affected by Bug 6868 repair
+# their accountlines table; unnecessarily large accountno numbers were entered
+# due to a bug with the writeoff subroutine; this is fixed by enhancement 3498.
+# The accountno has a maximum value of 32767, and once that's reached, fines payments
+# will be applied to multiple fines (resulting in data loss).
+#
+# This 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 2 of the License, or (at your option) any later
+# version.
+#
+# This script 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, write to the Free Software Foundation, Inc.,                                              
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+
+
+
+# modules to use
+use Getopt::Long;
+use C4::Context;
+
+# Database handle
+my $dbh = C4::Context->dbh;
+my $sth_input = $dbh->prepare("SELECT borrowernumber FROM accountlines GROUP BY borrowernumber HAVING count(*) < max(accountno)"); 
+my $sth2 = $dbh->prepare("SELECT * FROM accountlines WHERE borrowernumber = ? ORDER BY accountno ASC");
+my $sth_update = $dbh->prepare("UPDATE accountlines SET accountno = ? WHERE borrowernumber = ? AND accountno = ? LIMIT 1");
+
+# Benchmarking variables
+my $startime = time();
+my $totalcount = 0;
+
+# Realtime Feedback needs this
+$|=1;
+
+# Options
+my @input;
+my $testmode;
+my $verbose;
+
+GetOptions(
+  't'	=> \$testmode,
+  'v' => \$verbose
+);
+
+$sth_input->execute();
+while ($row = $sth_input->fetchrow_hashref) {
+  main_job($row);
+  $totalcount++;
+}
+
+
+# Benchmarking
+my $endtime = time();
+my $time = $endtime-$startime;
+
+print "\n$totalcount accountlines rows updated in $time seconds\n";
+
+#
+# THE MAIN EVENT
+#
+
+sub main_job {
+  my $input = shift;
+
+  my $borrowernumber = $input->{'borrowernumber'};
+  my $newaccountno = 1;
+
+  $sth2->execute($borrowernumber);
+  while (my $accounts = $sth2->fetchrow_hashref) {
+    my $oldaccountno = $accounts->{'accountno'};
+    if (defined $verbose) {
+      print "Updating Borrower $borrowernumber, Accountno $oldaccountno to $newaccountno\n";
+    } else {
+      print ".";
+      print "\r$totalcount" unless ($totalcount % 100);
+    }
+    $sth_update->execute($newaccountno, $borrowernumber, $oldaccountno) unless (defined $testmode);  
+    $newaccountno++;
+  }
+
+}
-- 
1.7.5.4