View | Details | Raw Unified | Return to bug 6025
Collapse All | Expand All

(-)a/misc/recreateIssueStatistics.pl (-1 / +169 lines)
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;

Return to bug 6025