misc/recreateIssueStatistics.pl

   1 #!/usr/bin/perl
   2
   3 # Copyright 2011 BibLibre
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it under the
   8 # terms of the GNU General Public License as published by the Free Software
   9 # Foundation; either version 2 of the License, or (at your option) any later
  10 # version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  14 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License along
  17 # with Koha; if not, write to the Free Software Foundation, Inc.,
  18 # 51 Franklin Street, Suite 500, Boston, MA 02110-1335, USA
  19
  20
  21 # Re-create statistics from issues and old_issues tables
  22
  23 use strict;
  24 use warnings;
  25
  26 use Koha::Script;
  27 use C4::Context;
  28 use C4::Items;
  29 use Data::Dumper;
  30 use Getopt::Long;
  31 use Koha::Items;
  32
  33 my $dbh = C4::Context->dbh;
  34
  35 # Options
  36 my $issues = 0;
  37 my $returns = 0;
  38 my $help = 0;
  39
  40 GetOptions ('issues' => \$issues, 'returns' => \$returns, 'help|?|h' => \$help);
  41
  42 # Show usage
  43 if ($help == 1) {
  44         print ("Usage : perl $0 [--issues] [--returns]\n\n");
  45         print (" issues: process only issues\n");
  46         print (" returns: process only returns\n");
  47         exit 0;
  48 }
  49
  50 if ($issues == 0 && $returns == 0) {
  51     $issues = 1;
  52     $returns = 1;
  53 }
  54
  55 # Counters
  56 my $count_issues = 0;
  57 my $count_renewals = 0;
  58 my $count_returns = 0;
  59
  60 # Issues and renewals can be found in both issues and old_issues tables
  61 if ($issues == 1) {
  62     foreach my $table ('issues', 'old_issues') {
  63         # Getting issues
  64         print "looking for missing issues from $table\n";
  65         my $query = "SELECT borrowernumber, branchcode, itemnumber, issuedate, renewals, lastreneweddate from $table where itemnumber is not null";
  66         my $sth = $dbh->prepare($query);
  67         $sth->execute;
  68         # Looking for missing issues
  69         while (my $hashref = $sth->fetchrow_hashref) {
  70             my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'issue'";
  71             my $substh = $dbh->prepare($ctnquery);
  72             $substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'issuedate'});
  73             my $count = $substh->fetchrow_hashref->{'cnt'};
  74             if ($count == 0) {
  75                 # Inserting missing issue
  76                     my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber)
  77                                          VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
  78                     $substh = $dbh->prepare($insert);
  79             my $item = Koha::Items->find($hashref->{'itemnumber'});
  80             my $itemtype = $item->effective_itemtype;
  81
  82                     $substh->execute(
  83                         $hashref->{'issuedate'},
  84                         $hashref->{'branchcode'},
  85                         0,
  86                         'issue',
  87                         '',
  88                         $hashref->{'itemnumber'},
  89             $itemtype,
  90                         $hashref->{'borrowernumber'}
  91                     );
  92             print "date: $hashref->{'issuedate'} branchcode: $hashref->{'branchcode'} type: issue itemnumber: $hashref->{'itemnumber'} itype: $itemtype borrowernumber: $hashref->{'borrowernumber'}\n";
  93                     $count_issues++;
  94                 }
  95
  96                 # Looking for missing renewals
  97                 if ($hashref->{'renewals'} && $hashref->{'renewals'} > 0 ) {
  98                     # This is the not-so accurate part :
  99                     # We assume that there are missing renewals, based on the last renewal date
 100                     # Maybe should this be deactivated by default ?
 101                     my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'renew'";
 102                     my $substh = $dbh->prepare($ctnquery);
 103                     $substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'lastreneweddate'});
 104
 105                     my $missingrenewalscount = $hashref->{'renewals'} - $substh->fetchrow_hashref->{'cnt'};
 106                     print "We assume $missingrenewalscount renewals are missing. Creating them\n" if ($missingrenewalscount > 0);
 107                     for (my $i = 0; $i < $missingrenewalscount; $i++) {
 108
 109                         # Inserting missing renewals
 110                         my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber)
 111                                          VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
 112                         $substh = $dbh->prepare($insert);
 113             my $item = Koha::Items->find($hashref->{'itemnumber'});
 114             my $itemtype = $item->effective_itemtype;
 115
 116                         $substh->execute(
 117                             $hashref->{'lastreneweddate'},
 118                             $hashref->{'branchcode'},
 119                             0,
 120                             'renew',
 121                             '',
 122                             $hashref->{'itemnumber'},
 123                 $itemtype,
 124                             $hashref->{'borrowernumber'}
 125                             );
 126             print "date: $hashref->{'lastreneweddate'} branchcode: $hashref->{'branchcode'} type: renew itemnumber: $hashref->{'itemnumber'} itype: $itemtype borrowernumber: $hashref->{'borrowernumber'}\n";
 127                         $count_renewals++;
 128
 129                     }
 130
 131                 }
 132         }
 133     }
 134 }
 135
 136 # Getting returns
 137 if ($returns == 1) {
 138     print "looking for missing returns from old_issues\n";
 139     my $query = "SELECT * from old_issues where itemnumber is not null";
 140     my $sth = $dbh->prepare($query);
 141     $sth->execute;
 142     # Looking for missing returns
 143     while (my $hashref = $sth->fetchrow_hashref) {
 144         my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'return'";
 145         my $substh = $dbh->prepare($ctnquery);
 146         $substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'returndate'});
 147         my $count = $substh->fetchrow_hashref->{'cnt'};
 148         if ($count == 0) {
 149             # Inserting missing issue
 150                 my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber)
 151                                      VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
 152                 $substh = $dbh->prepare($insert);
 153         my $item = Koha::Items->find($hashref->{'itemnumber'});
 154         my $itemtype = $item->effective_itemtype;
 155
 156                 $substh->execute(
 157                     $hashref->{'returndate'},
 158                     $hashref->{'branchcode'},
 159                     0,
 160                     'return',
 161                     '',
 162                     $hashref->{'itemnumber'},
 163             $itemtype,
 164                     $hashref->{'borrowernumber'}
 165                 );
 166         print "date: $hashref->{'returndate'} branchcode: $hashref->{'branchcode'} type: return itemnumber: $hashref->{'itemnumber'} itype: $itemtype borrowernumber: $hashref->{'borrowernumber'}\n";
 167                 $count_returns++;
 168             }
 169
 170     }
 171 }
 172
 173 print "Missing issues added:   $count_issues\n" if $issues;
 174 print "Missing renewals added: $count_renewals\n" if $issues;
 175 print "Missing returns added:  $count_returns\n" if $returns;