Lines 1-168
Link Here
|
1 |
#!/usr/bin/perl |
|
|
2 |
#----------------------------------- |
3 |
# Script Name: circstats.pl |
4 |
# Script Version: 1.0 |
5 |
# Date: 2006/02/07 |
6 |
# Author: Stephen Hedges (shedges@skemotah.com) |
7 |
# Description: |
8 |
# This script creates a comma-separated value file of |
9 |
# circulation statistics for any given month and year. |
10 |
# The statistics are grouped by itemtype, then by branch, |
11 |
# then by issues and renewals. |
12 |
# Revision History: |
13 |
# 1.0 2006/02/07: original version |
14 |
#----------------------------------- |
15 |
# Contributed 2003-6 by Skemotah Solutions |
16 |
# |
17 |
# This file is part of Koha. |
18 |
# |
19 |
# Koha is free software; you can redistribute it and/or modify it under the |
20 |
# terms of the GNU General Public License as published by the Free Software |
21 |
# Foundation; either version 2 of the License, or (at your option) any later |
22 |
# version. |
23 |
# |
24 |
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY |
25 |
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR |
26 |
# A PARTICULAR PURPOSE. See the GNU General Public License for more details. |
27 |
# |
28 |
# You should have received a copy of the GNU General Public License along with |
29 |
# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, |
30 |
# Suite 330, Boston, MA 02111-1307 USA |
31 |
|
32 |
# use strict; |
33 |
use warnings; |
34 |
|
35 |
# UNCOMMENT the following lines if running from a command line |
36 |
# print "THIS SCRIPT produces a comma-separated values file of circulation statistics for a given month and year.\n\nDo you wish to continue? (y/n) "; |
37 |
# chomp($_ = <STDIN>); |
38 |
# die unless (/^y/i); |
39 |
|
40 |
# UNCOMMENT the following lines if getting old stats (but be aware that renewal numbers are affected by deletes) |
41 |
# YOU WILL also need to modify the SQLs to use these dates |
42 |
# my ($month,$year); |
43 |
# print "Get statistics for which month (1 to 12)? "; |
44 |
# chomp($month = <STDIN>); |
45 |
# die if ($month < 1 || $month > 12); |
46 |
# print "Get statistics for which year (2000 to 2050)? "; |
47 |
# chomp($year = <STDIN>); |
48 |
# die if ($year < 2000 || $year > 2050); |
49 |
|
50 |
open OUTFILE, ">circstats.csv" or die "Cannot open file circstats.csv: $!"; |
51 |
print OUTFILE "\"ccode\",\"branch\",\"issues\",\"renewals\"\n"; |
52 |
|
53 |
use C4::Context; |
54 |
use C4::Koha; |
55 |
use Mail::Sendmail; # comment out 3 lines if not doing e-mail sending of file |
56 |
use MIME::QuotedPrint; |
57 |
use MIME::Base64; |
58 |
# set the e-mail server -- comment out if not doing e-mail notices |
59 |
unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; |
60 |
# set your own mail server name here |
61 |
|
62 |
my $dbh = C4::Context->dbh; |
63 |
#my $sth1 = $dbh->prepare ("SELECT itemtype FROM itemtypes ORDER BY itemtype"); |
64 |
my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode"); |
65 |
|
66 |
# number of checkouts for this library |
67 |
my $sth3 = $dbh->prepare ("SELECT COUNT(*) FROM biblioitems,items,statistics WHERE biblioitems.biblioitemnumber=items.biblioitemnumber AND statistics.itemnumber=items.itemnumber AND items.ccode=? AND YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND statistics.branch=? AND statistics.type='issue' GROUP BY ccode"); |
68 |
|
69 |
# number of renewals for this library |
70 |
my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems |
71 |
WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) |
72 |
AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) |
73 |
AND statistics.itemnumber=items.itemnumber |
74 |
AND biblioitems.ccode=? |
75 |
AND homebranch=? |
76 |
AND biblioitems.biblioitemnumber=items.biblioitemnumber |
77 |
AND statistics.type='renew' |
78 |
GROUP BY statistics.type"); |
79 |
|
80 |
# find the itemnumbers |
81 |
my ($rowt,$rowb,$rowi,$rowr,$itemtype,$branchcode,$branchname,$issues,$renews,$line); |
82 |
|
83 |
#$sth1->execute(); |
84 |
my ($ccode_count,@ccode_results) = GetCcodes; |
85 |
|
86 |
#for my $ccode (@ccode_results); |
87 |
# loop through every itemtype |
88 |
#while ($rowt = $sth1->fetchrow_arrayref) { |
89 |
for (my $i=0;$i<scalar(@ccode_results);$i++) { |
90 |
#for my $ccode (@ccode_results) { |
91 |
unless (!$ccode_results[$i]) { |
92 |
# use Data::Dumper; |
93 |
# warn Dumper($ccode_results[$i]); |
94 |
$itemtype = $ccode_results[$i]{'authorised_value'}; #$ccode->{authorised_value}; #rowt->[0]; |
95 |
$line = "\"$itemtype\""; |
96 |
# warn "$itemtype\n"; |
97 |
# find branchnames |
98 |
$sth2->execute(); |
99 |
|
100 |
# find the number of issues per itemtype in this branch |
101 |
while ($rowb = $sth2->fetchrow_arrayref) { |
102 |
$branchcode = $rowb->[0]; |
103 |
$branchname = $rowb->[1]; |
104 |
$sth3->execute($itemtype,$branchcode); # find issues by itemtype per branch |
105 |
$rowi = $sth3->fetchrow_arrayref; |
106 |
$issues = $rowi->[0]; # count |
107 |
unless ($issues) {$issues=""} |
108 |
$sth3->finish; |
109 |
|
110 |
$sth4->execute($itemtype,$branchcode); # find reserves by itemtype per branch |
111 |
$rowr = $sth4->fetchrow_arrayref; # count |
112 |
$renews = $rowr->[0]; |
113 |
unless ($renews) {$renews=""} |
114 |
$sth4->finish; |
115 |
|
116 |
# put the data in this line |
117 |
$line = $line . ",\"$branchname\",\"$issues\",\"$renews\""; |
118 |
# warn "LINE: $branchname $issues $renews\n"; |
119 |
} |
120 |
$sth2->finish; |
121 |
|
122 |
$line = $line . "\n"; |
123 |
print OUTFILE "$line"; |
124 |
} |
125 |
} |
126 |
#$sth1->finish; |
127 |
close OUTFILE; |
128 |
$dbh->disconnect; |
129 |
|
130 |
# send the outfile as an attachment to the library e-mail |
131 |
|
132 |
my %attachmail = ( |
133 |
from => $from_address, |
134 |
to => $to_addresses, |
135 |
subject => 'Circulation Statistics', |
136 |
); |
137 |
|
138 |
|
139 |
my $boundary = "====" . time() . "===="; |
140 |
$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\""; |
141 |
|
142 |
my $attachmessage = "Attached is the file of circulation statistics for the previous month. Please open the statistics spreadsheet template for Page 1, open this file in a new spreadsheet, and paste the numbers from this file into the template.\n"; |
143 |
|
144 |
my $attachfile = "circstats.csv"; |
145 |
|
146 |
open (F, $attachfile) or die "Cannot read $attachfile: $!"; |
147 |
binmode F; undef $/; |
148 |
$attachmail{body} = encode_base64(<F>); |
149 |
close F; |
150 |
|
151 |
$boundary = '--'.$boundary; |
152 |
$attachmail{body} = <<END_OF_BODY; |
153 |
$boundary |
154 |
Content-Type: text/plain; charset="iso-8859-1" |
155 |
Content-Transfer-Encoding: quoted-printable |
156 |
|
157 |
$attachmessage |
158 |
$boundary |
159 |
Content-Type: application/octet-stream; name="circstats.csv" |
160 |
Content-Transfer-Encoding: base64 |
161 |
Content-Disposition: attachment; filename="circstats.csv" |
162 |
|
163 |
$attachmail{body} |
164 |
$boundary-- |
165 |
END_OF_BODY |
166 |
|
167 |
sendmail(%attachmail) || print "Error: $Mail::Sendmail::error\n"; |
168 |
|