[Koha-patches] [PATCH] Cronjob to purge issues table of "stuck" transactions.

[Joe Atzberger]

This workaround is necessary until issues table structure is fixed to ensure:
(1) always a valid borrowernumber (offline circ needs to adapt to handle errors), and
(2) itemnumber is unique, i.e. only one person can have the item checked out.
---
 misc/cronjobs/clean_issues_table.pl |   86 +++++++++++++++++++++++++++++++++++
 1 files changed, 86 insertions(+), 0 deletions(-)
 create mode 100755 misc/cronjobs/clean_issues_table.pl

diff --git a/misc/cronjobs/clean_issues_table.pl b/misc/cronjobs/clean_issues_table.pl
new file mode 100755
index 0000000..5de0222
--- /dev/null
+++ b/misc/cronjobs/clean_issues_table.pl
@@ -0,0 +1,86 @@
+#!/usr/bin/perl -w
+
+use strict;
+use warnings;
+
+BEGIN {
+    # find Koha's Perl modules
+    # test carefully before changing this
+    use FindBin;
+    eval { require "$FindBin::Bin/../kohalib.pl" };
+}
+
+use C4::Context;
+use C4::Debug;
+
+use Getopt::Long;
+use Pod::Usage;
+
+=head1 NAME
+
+clean_issues_table.pl - Display and purge rows from issues with duplicate itemnumber.
+
+=head1 SYNOPSIS
+
+clean_issues_table.pl [ -t ] [ -h ]
+
+ Options:
+   -t --test	Just display the data.  Change nothing.
+   -h --help	Display this usage advisory.
+   -v --verbose	Provide counts of itemnumber, rows affect and rows deleted.
+   -? 		Alias for --help.
+
+=head1 DESCRIPTION
+
+This hack cronjob is necessary when using offline circulation because it can force rows onto the
+issues table even when the borrowernumbers are unmatched by anything in borrowers.  That results
+in a NULL borrowernumber and the row gets "stuck" because of Koha transaction logic.  Other 
+issues can then be made on the same itemnumber and ALL will "stick" as well.  
+
+Without -t or -h, this deletes all but the most recent line from issues so there is only one
+transaction per itemnumber.  
+
+FIXME: Someday this script will be unnecessary.
+
+=cut
+
+my $test = 0;
+my $help = 0;
+my $verbose = 0;
+
+GetOptions(
+    'test'    => \$test,
+    'help|?'  => \$help,
+    'verbose' => \$verbose,
+);
+
+pod2usage(-verbose => 2) if $help;
+$verbose++ if $test;
+
+my %targets = ();
+my $total = 0;
+my $sth = C4::Context->dbh->prepare("SELECT itemnumber, count(*) as count FROM issues GROUP BY itemnumber HAVING count >1;");
+$sth->execute();
+while (my ($itemnumber, $count) = $sth->fetchrow) {
+    $targets{$itemnumber} = $count;
+    $total += $count;
+}
+
+if ($verbose) {
+    printf "Number of itemnumbers duplicated  : %3d\n", scalar(keys %targets);
+    printf "Number of duplicate rows total    : %3d\n", $total;
+}
+
+if ($test) {
+    print "\nRun in test mode.  No changes made.\n";
+    exit;
+}
+
+my $kills = 0;
+my $delete_sth = C4::Context->dbh->prepare("DELETE FROM issues where itemnumber = ? LIMIT ?");
+foreach (keys %targets) {
+   $delete_sth->execute($_, $targets{$_} - 1);
+   $kills += $delete_sth->rows();
+}
+
+($verbose) and printf "Number of rows removed from issues: %3d\n", $kills;
-- 
1.5.5.GIT