From 63b551cc849026a347ea923dc26ed01a6ebd7c9a Mon Sep 17 00:00:00 2001 From: Matthias Meusburger Date: Wed, 26 May 2010 11:48:36 +0200 Subject: [PATCH] Bug 6025 : MT2889, follow-up: Adds a script that re-create missing statistics from issues and old_issues tables --- 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