Line 0
Link Here
|
0 |
- |
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 |
use C4::Context; |
26 |
use C4::Items; |
27 |
use Data::Dumper; |
28 |
use Getopt::Long; |
29 |
|
30 |
my $dbh = C4::Context->dbh; |
31 |
|
32 |
# Options |
33 |
my $issues = 0; |
34 |
my $returns = 0; |
35 |
my $help = 0; |
36 |
|
37 |
GetOptions ('issues' => \$issues, 'returns' => \$returns, 'help|?|h' => \$help); |
38 |
|
39 |
# Show usage |
40 |
if ($help == 1) { |
41 |
print ("Usage : perl $0 [--issues] [--returns]\n\n"); |
42 |
print (" issues: process only issues\n"); |
43 |
print (" returns: process only returns\n"); |
44 |
exit 0; |
45 |
} |
46 |
|
47 |
if ($issues == 0 && $returns == 0) { |
48 |
$issues = 1; |
49 |
$returns = 1; |
50 |
} |
51 |
|
52 |
# Counters |
53 |
my $count_issues = 0; |
54 |
my $count_renewals = 0; |
55 |
my $count_returns = 0; |
56 |
|
57 |
# Issues and renewals can be found in both issues and old_issues tables |
58 |
if ($issues == 1) { |
59 |
foreach my $table ('issues', 'old_issues') { |
60 |
# Getting issues |
61 |
print "looking for missing issues from $table\n"; |
62 |
my $query = "SELECT borrowernumber, branchcode, itemnumber, issuedate, renewals, lastreneweddate from $table where itemnumber is not null"; |
63 |
my $sth = $dbh->prepare($query); |
64 |
$sth->execute; |
65 |
# Looking for missing issues |
66 |
while (my $hashref = $sth->fetchrow_hashref) { |
67 |
my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'issue'"; |
68 |
my $substh = $dbh->prepare($ctnquery); |
69 |
$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'issuedate'}); |
70 |
my $count = $substh->fetchrow_hashref->{'cnt'}; |
71 |
if ($count == 0) { |
72 |
# Inserting missing issue |
73 |
my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) |
74 |
VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; |
75 |
$substh = $dbh->prepare($insert); |
76 |
my $item = GetItem($hashref->{'itemnumber'}); |
77 |
|
78 |
$substh->execute( |
79 |
$hashref->{'issuedate'}, |
80 |
$hashref->{'branchcode'}, |
81 |
0, |
82 |
'issue', |
83 |
'', |
84 |
$hashref->{'itemnumber'}, |
85 |
$item->{'itype'}, |
86 |
$hashref->{'borrowernumber'} |
87 |
); |
88 |
print "date: $hashref->{'issuedate'} branchcode: $hashref->{'branchcode'} type: issue itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n"; |
89 |
$count_issues++; |
90 |
} |
91 |
|
92 |
# Looking for missing renewals |
93 |
if ($hashref->{'renewals'} && $hashref->{'renewals'} > 0 ) { |
94 |
# This is the not-so accurate part : |
95 |
# We assume that there are missing renewals, based on the last renewal date |
96 |
# Maybe should this be deactivated by default ? |
97 |
my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'renew'"; |
98 |
my $substh = $dbh->prepare($ctnquery); |
99 |
$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'lastreneweddate'}); |
100 |
|
101 |
my $missingrenewalscount = $hashref->{'renewals'} - $substh->fetchrow_hashref->{'cnt'}; |
102 |
print "We assume $missingrenewalscount renewals are missing. Creating them\n" if ($missingrenewalscount > 0); |
103 |
for (my $i = 0; $i < $missingrenewalscount; $i++) { |
104 |
|
105 |
# Inserting missing renewals |
106 |
my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) |
107 |
VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; |
108 |
$substh = $dbh->prepare($insert); |
109 |
my $item = GetItem($hashref->{'itemnumber'}); |
110 |
|
111 |
$substh->execute( |
112 |
$hashref->{'lastreneweddate'}, |
113 |
$hashref->{'branchcode'}, |
114 |
0, |
115 |
'renew', |
116 |
'', |
117 |
$hashref->{'itemnumber'}, |
118 |
$item->{'itype'}, |
119 |
$hashref->{'borrowernumber'} |
120 |
); |
121 |
print "date: $hashref->{'lastreneweddate'} branchcode: $hashref->{'branchcode'} type: renew itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n"; |
122 |
$count_renewals++; |
123 |
|
124 |
} |
125 |
|
126 |
} |
127 |
} |
128 |
} |
129 |
} |
130 |
|
131 |
# Getting returns |
132 |
if ($returns == 1) { |
133 |
print "looking for missing returns from old_issues\n"; |
134 |
my $query = "SELECT * from old_issues where itemnumber is not null"; |
135 |
my $sth = $dbh->prepare($query); |
136 |
$sth->execute; |
137 |
# Looking for missing returns |
138 |
while (my $hashref = $sth->fetchrow_hashref) { |
139 |
my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'return'"; |
140 |
my $substh = $dbh->prepare($ctnquery); |
141 |
$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'returndate'}); |
142 |
my $count = $substh->fetchrow_hashref->{'cnt'}; |
143 |
if ($count == 0) { |
144 |
# Inserting missing issue |
145 |
my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) |
146 |
VALUES(?, ?, ?, ?, ?, ?, ?, ?)"; |
147 |
$substh = $dbh->prepare($insert); |
148 |
my $item = GetItem($hashref->{'itemnumber'}); |
149 |
|
150 |
$substh->execute( |
151 |
$hashref->{'returndate'}, |
152 |
$hashref->{'branchcode'}, |
153 |
0, |
154 |
'return', |
155 |
'', |
156 |
$hashref->{'itemnumber'}, |
157 |
$item->{'itype'}, |
158 |
$hashref->{'borrowernumber'} |
159 |
); |
160 |
print "date: $hashref->{'returndate'} branchcode: $hashref->{'branchcode'} type: return itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n"; |
161 |
$count_returns++; |
162 |
} |
163 |
|
164 |
} |
165 |
} |
166 |
|
167 |
print "Missing issues added: $count_issues\n" if $issues; |
168 |
print "Missing renewals added: $count_renewals\n" if $issues; |
169 |
print "Missing returns added: $count_returns\n" if $returns; |