[Koha-patches] [PATCH] Bug 6025 : MT2889, follow-up: Adds a script that re-create missing statistics from issues and old_issues tables

[Chris Cormack]

From: Matthias Meusburger <matthias.meusburger at biblibre.com>

---
 misc/recreateIssueStatistics.pl |   93 ++++++++++++++++++++++++++++++++------
 1 files changed, 78 insertions(+), 15 deletions(-)

diff --git a/misc/recreateIssueStatistics.pl b/misc/recreateIssueStatistics.pl
index a87d25b..de74265 100644
--- a/misc/recreateIssueStatistics.pl
+++ b/misc/recreateIssueStatistics.pl
@@ -19,10 +19,6 @@
 #
 
 # Re-create statistics from issues and old_issues tables
-# (please note that this is not an accurate process)
-
-# If the issue is still in the issues table, we can re-create issues or renewals
-# If the issue is already in the old_issues table, we can re-create returns
 
 use strict;
 use warnings;
@@ -32,40 +28,107 @@ use Data::Dumper;
 
 my $dbh = C4::Context->dbh;
 
+# Issues and renewals can be found in both issues and old_issues tables
 foreach my $table ('issues', 'old_issues') {
+    # Getting issues
+    print "looking for missing issues from $table\n";
+    my $query = "SELECT borrowernumber, branchcode, itemnumber, issuedate, renewals, lastreneweddate from $table where itemnumber is not null";
+    my $sth = $dbh->prepare($query);
+    $sth->execute;
+    # Looking for missing issues
+    while (my $hashref = $sth->fetchrow_hashref) {
+	my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'issue'";
+	my $substh = $dbh->prepare($ctnquery);
+	$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'issuedate'});
+	my $count = $substh->fetchrow_hashref->{'cnt'};
+	if ($count == 0) {
+	    # Inserting missing issue
+		my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
+				     VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
+		$substh = $dbh->prepare($insert); 
+		my $item = GetItem($hashref->{'itemnumber'});
+
+		$substh->execute(
+		    $hashref->{'issuedate'},
+		    $hashref->{'branchcode'},
+		    0,
+		    'issue',
+		    '',
+		    $hashref->{'itemnumber'},
+		    $item->{'itype'},
+		    $hashref->{'borrowernumber'}
+		);
+		print "date: $hashref->{'issuedate'} branchcode: $hashref->{'branchcode'} type: issue itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
+	    }
+
+	    # Looking for missing renewals
+	    if ($hashref->{'renewals'} && $hashref->{'renewals'} > 0 ) {
+		# This is the not-so accurate part :
+		# We assume that there are missing renewals, based on the last renewal date
+		# Maybe should this be deactivated by default ?
+		my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'renew'";
+		my $substh = $dbh->prepare($ctnquery);
+		$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'lastreneweddate'});
+
+		my $missingrenewalscount = $hashref->{'renewals'} - $substh->fetchrow_hashref->{'cnt'};
+		print "We assume $missingrenewalscount renewals are missing. Creating them\n" if ($missingrenewalscount > 0);
+		for (my $i = 0; $i < $missingrenewalscount; $i++) {
 
-    print "looking for missing statistics from the $table table\n";
+		    # Inserting missing renewals
+		    my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
+				     VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
+		    $substh = $dbh->prepare($insert); 
+		    my $item = GetItem($hashref->{'itemnumber'});
 
-    my $query = "SELECT * from $table where itemnumber is not null";
+		    $substh->execute(
+			$hashref->{'lastreneweddate'},
+			$hashref->{'branchcode'},
+			0,
+			'renew',
+			'',
+			$hashref->{'itemnumber'},
+			$item->{'itype'},
+			$hashref->{'borrowernumber'}
+			);
+		    print "date: $hashref->{'lastreneweddate'} branchcode: $hashref->{'branchcode'} type: renew itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
 
+		}
+
+	    }
+    }
+}
+
+# Getting returns
+print "looking for missing returns from old_issues\n";
+my $query = "SELECT * from old_issues where itemnumber is not null";
     my $sth = $dbh->prepare($query);
     $sth->execute;
+# Looking for missing returns
     while (my $hashref = $sth->fetchrow_hashref) {
-
-	my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND datetime = ?";
+    my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'return'";
 	my $substh = $dbh->prepare($ctnquery);
-	$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'timestamp'});
+    $substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'returndate'});
 	my $count = $substh->fetchrow_hashref->{'cnt'};
 	if ($count == 0) {
+	# Inserting missing issue
 	    my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
 				 VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
 	    $substh = $dbh->prepare($insert);
-
-	    my $type = ($table eq 'old_issues') ? 'return' : ($hashref->{'renewals'} ? 'renew' : 'issue') ;
 	    my $item = GetItem($hashref->{'itemnumber'});
 
 	    $substh->execute(
-		$hashref->{'timestamp'},
+		$hashref->{'returndate'},
 		$hashref->{'branchcode'},
 		0,
-		$type,
+		'return',
 		'',
 		$hashref->{'itemnumber'},
 		$item->{'itype'},
 		$hashref->{'borrowernumber'}
 	    );
-	    print "timestamp: $hashref->{'timestamp'} branchcode: $hashref->{'branchcode'} type: $type itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
+	    print "date: $hashref->{'returndate'} branchcode: $hashref->{'branchcode'} type: return itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
 	}
 
     }
-}
+
+
-- 
1.7.1