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

(-)a/misc/recreateIssueStatistics.pl (-16 / +78 lines)
Lines 19-28 Link Here
19
#
19
#
20
20
21
# Re-create statistics from issues and old_issues tables
21
# Re-create statistics from issues and old_issues tables
22
# (please note that this is not an accurate process)
23
24
# If the issue is still in the issues table, we can re-create issues or renewals
25
# If the issue is already in the old_issues table, we can re-create returns
26
22
27
use strict;
23
use strict;
28
use warnings;
24
use warnings;
Lines 32-71 use Data::Dumper; Link Here
32
28
33
my $dbh = C4::Context->dbh;
29
my $dbh = C4::Context->dbh;
34
30
31
# Issues and renewals can be found in both issues and old_issues tables
35
foreach my $table ('issues', 'old_issues') {
32
foreach my $table ('issues', 'old_issues') {
33
    # Getting issues
34
    print "looking for missing issues from $table\n";
35
    my $query = "SELECT borrowernumber, branchcode, itemnumber, issuedate, renewals, lastreneweddate from $table where itemnumber is not null";
36
    my $sth = $dbh->prepare($query);
37
    $sth->execute;
38
    # Looking for missing issues
39
    while (my $hashref = $sth->fetchrow_hashref) {
40
	my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'issue'";
41
	my $substh = $dbh->prepare($ctnquery);
42
	$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'issuedate'});
43
	my $count = $substh->fetchrow_hashref->{'cnt'};
44
	if ($count == 0) {
45
	    # Inserting missing issue
46
		my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
47
				     VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
48
		$substh = $dbh->prepare($insert); 
49
		my $item = GetItem($hashref->{'itemnumber'});
50
51
		$substh->execute(
52
		    $hashref->{'issuedate'},
53
		    $hashref->{'branchcode'},
54
		    0,
55
		    'issue',
56
		    '',
57
		    $hashref->{'itemnumber'},
58
		    $item->{'itype'},
59
		    $hashref->{'borrowernumber'}
60
		);
61
		print "date: $hashref->{'issuedate'} branchcode: $hashref->{'branchcode'} type: issue itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
62
	    }
63
64
	    # Looking for missing renewals
65
	    if ($hashref->{'renewals'} && $hashref->{'renewals'} > 0 ) {
66
		# This is the not-so accurate part :
67
		# We assume that there are missing renewals, based on the last renewal date
68
		# Maybe should this be deactivated by default ?
69
		my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'renew'";
70
		my $substh = $dbh->prepare($ctnquery);
71
		$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'lastreneweddate'});
72
73
		my $missingrenewalscount = $hashref->{'renewals'} - $substh->fetchrow_hashref->{'cnt'};
74
		print "We assume $missingrenewalscount renewals are missing. Creating them\n" if ($missingrenewalscount > 0);
75
		for (my $i = 0; $i < $missingrenewalscount; $i++) {
36
76
37
    print "looking for missing statistics from the $table table\n";
77
		    # Inserting missing renewals
78
		    my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
79
				     VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
80
		    $substh = $dbh->prepare($insert); 
81
		    my $item = GetItem($hashref->{'itemnumber'});
38
82
39
    my $query = "SELECT * from $table where itemnumber is not null";
83
		    $substh->execute(
84
			$hashref->{'lastreneweddate'},
85
			$hashref->{'branchcode'},
86
			0,
87
			'renew',
88
			'',
89
			$hashref->{'itemnumber'},
90
			$item->{'itype'},
91
			$hashref->{'borrowernumber'}
92
			);
93
		    print "date: $hashref->{'lastreneweddate'} branchcode: $hashref->{'branchcode'} type: renew itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
40
94
95
		}
96
97
	    }
98
    }
99
}
100
101
# Getting returns
102
print "looking for missing returns from old_issues\n";
103
my $query = "SELECT * from old_issues where itemnumber is not null";
41
    my $sth = $dbh->prepare($query);
104
    my $sth = $dbh->prepare($query);
42
    $sth->execute;
105
    $sth->execute;
106
# Looking for missing returns
43
    while (my $hashref = $sth->fetchrow_hashref) {
107
    while (my $hashref = $sth->fetchrow_hashref) {
44
108
    my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND DATE(datetime) = ? AND type = 'return'";
45
	my $ctnquery = "SELECT count(*) as cnt FROM statistics WHERE borrowernumber = ? AND itemnumber = ? AND datetime = ?";
46
	my $substh = $dbh->prepare($ctnquery);
109
	my $substh = $dbh->prepare($ctnquery);
47
	$substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'timestamp'});
110
    $substh->execute($hashref->{'borrowernumber'}, $hashref->{'itemnumber'}, $hashref->{'returndate'});
48
	my $count = $substh->fetchrow_hashref->{'cnt'};
111
	my $count = $substh->fetchrow_hashref->{'cnt'};
49
	if ($count == 0) {
112
	if ($count == 0) {
113
	# Inserting missing issue
50
	    my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
114
	    my $insert = "INSERT INTO statistics (datetime, branch, value, type, other, itemnumber, itemtype, borrowernumber) 
51
				 VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
115
				 VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
52
	    $substh = $dbh->prepare($insert);
116
	    $substh = $dbh->prepare($insert);
53
54
	    my $type = ($table eq 'old_issues') ? 'return' : ($hashref->{'renewals'} ? 'renew' : 'issue') ;
55
	    my $item = GetItem($hashref->{'itemnumber'});
117
	    my $item = GetItem($hashref->{'itemnumber'});
56
118
57
	    $substh->execute(
119
	    $substh->execute(
58
		$hashref->{'timestamp'},
120
		$hashref->{'returndate'},
59
		$hashref->{'branchcode'},
121
		$hashref->{'branchcode'},
60
		0,
122
		0,
61
		$type,
123
		'return',
62
		'',
124
		'',
63
		$hashref->{'itemnumber'},
125
		$hashref->{'itemnumber'},
64
		$item->{'itype'},
126
		$item->{'itype'},
65
		$hashref->{'borrowernumber'}
127
		$hashref->{'borrowernumber'}
66
	    );
128
	    );
67
	    print "timestamp: $hashref->{'timestamp'} branchcode: $hashref->{'branchcode'} type: $type itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
129
	    print "date: $hashref->{'returndate'} branchcode: $hashref->{'branchcode'} type: return itemnumber: $hashref->{'itemnumber'} itype: $item->{'itype'} borrowernumber: $hashref->{'borrowernumber'}\n";
68
	}
130
	}
69
131
70
    }
132
    }
71
}
133
134
72
- 

Return to bug 6025