-
Notifications
You must be signed in to change notification settings - Fork 0
/
recreateIssueStatistics.pl
executable file
·150 lines (131 loc) · 5.5 KB
/
recreateIssueStatistics.pl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
#!/usr/bin/perl
# Re-create statistics from issues and old_issues tables
use strict;
use warnings;
use C4::Context;
use C4::Items;
use Data::Dumper;
use Getopt::Long;
my $dbh = C4::Context->dbh;
# Options
my $issues = 0;
my $returns = 0;
my $help = 0;
GetOptions ('issues' => \$issues, 'returns' => \$returns, 'help|?|h' => \$help);
# Show usage
if ($help == 1) {
print ("Usage : perl $0 [--issues] [--returns]\n\n");
print (" issues: process only issues\n");
print (" returns: process only returns\n");
exit 0;
}
if ($issues == 0 && $returns == 0) {
$issues = 1;
$returns = 1;
}
# Counters
my $count_issues = 0;
my $count_renewals = 0;
my $count_returns = 0;
# Issues and renewals can be found in both issues and old_issues tables
if ($issues == 1) {
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";
$count_issues++;
}
# 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++) {
# 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'});
$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";
$count_renewals++;
}
}
}
}
}
# Getting returns
if ($returns == 1) {
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 DATE(datetime) = ? AND type = 'return'";
my $substh = $dbh->prepare($ctnquery);
$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 $item = GetItem($hashref->{'itemnumber'});
$substh->execute(
$hashref->{'returndate'},
$hashref->{'branchcode'},
0,
'return',
'',
$hashref->{'itemnumber'},
$item->{'itype'},
$hashref->{'borrowernumber'}
);
print "date: $hashref->{'returndate'} branchcode: $hashref->{'branchcode'} type: return itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
$count_returns++;
}
}
}
print "Missing issues added: $count_issues\n" if $issues;
print "Missing renewals added: $count_renewals\n" if $issues;
print "Missing returns added: $count_returns\n" if $returns;