2 #-----------------------------------
3 # Script Name: circstats.pl
6 # Author: Stephen Hedges (shedges@skemotah.com)
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.
13 # 1.0 2006/02/07: original version
14 #-----------------------------------
15 # Contributed 2003-6 by Skemotah Solutions
17 # This file is part of Koha.
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
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.
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
34 # UNCOMMENT the following lines if running from a command line
35 # 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) ";
36 # chomp($_ = <STDIN>);
39 # UNCOMMENT the following lines if getting old stats (but be aware that renewal numbers are affected by deletes)
40 # YOU WILL also need to modify the SQLs to use these dates
42 # print "Get statistics for which month (1 to 12)? ";
43 # chomp($month = <STDIN>);
44 # die if ($month < 1 || $month > 12);
45 # print "Get statistics for which year (2000 to 2050)? ";
46 # chomp($year = <STDIN>);
47 # die if ($year < 2000 || $year > 2050);
49 open OUTFILE, ">circstats.csv" or die "Cannot open file circstats.csv: $!";
50 print OUTFILE "\"ccode\",\"branch\",\"issues\",\"renewals\"\n";
54 use Mail::Sendmail; # comment out 3 lines if not doing e-mail sending of file
55 use MIME::QuotedPrint;
57 # set the e-mail server -- comment out if not doing e-mail notices
58 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost';
59 # set your own mail server name here
61 my $dbh = C4::Context->dbh;
62 #my $sth1 = $dbh->prepare ("SELECT itemtype FROM itemtypes ORDER BY itemtype");
63 my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode");
65 # number of checkouts for this library
66 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 # number of renewals for this library
69 my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems
70 WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
71 AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
72 AND statistics.itemnumber=items.itemnumber
73 AND biblioitems.ccode=?
75 AND biblioitems.biblioitemnumber=items.biblioitemnumber
76 AND statistics.type='renew'
77 GROUP BY statistics.type");
79 # find the itemnumbers
80 my ($rowt,$rowb,$rowi,$rowr,$itemtype,$branchcode,$branchname,$issues,$renews,$line);
83 my ($ccode_count,@ccode_results) = GetCcodes;
85 #for my $ccode (@ccode_results);
86 # loop through every itemtype
87 #while ($rowt = $sth1->fetchrow_arrayref) {
88 for (my $i=0;$i<scalar(@ccode_results);$i++) {
89 #for my $ccode (@ccode_results) {
90 unless (!$ccode_results[$i]) {
92 # warn Dumper($ccode_results[$i]);
93 $itemtype = $ccode_results[$i]{'authorised_value'}; #$ccode->{authorised_value}; #rowt->[0];
94 $line = "\"$itemtype\"";
99 # find the number of issues per itemtype in this branch
100 while ($rowb = $sth2->fetchrow_arrayref) {
101 $branchcode = $rowb->[0];
102 $branchname = $rowb->[1];
103 $sth3->execute($itemtype,$branchcode); # find issues by itemtype per branch
104 $rowi = $sth3->fetchrow_arrayref;
105 $issues = $rowi->[0]; # count
106 unless ($issues) {$issues=""}
109 $sth4->execute($itemtype,$branchcode); # find reserves by itemtype per branch
110 $rowr = $sth4->fetchrow_arrayref; # count
111 $renews = $rowr->[0];
112 unless ($renews) {$renews=""}
115 # put the data in this line
116 $line = $line . ",\"$branchname\",\"$issues\",\"$renews\"";
117 # warn "LINE: $branchname $issues $renews\n";
121 $line = $line . "\n";
122 print OUTFILE "$line";
129 # send the outfile as an attachment to the library e-mail
132 from => $from_address,
134 subject => 'Circulation Statistics',
138 my $boundary = "====" . time() . "====";
139 $attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\"";
141 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 my $attachfile = "circstats.csv";
145 open (F, $attachfile) or die "Cannot read $attachfile: $!";
147 $attachmail{body} = encode_base64(<F>);
150 $boundary = '--'.$boundary;
151 $attachmail{body} = <<END_OF_BODY;
153 Content-Type: text/plain; charset="iso-8859-1"
154 Content-Transfer-Encoding: quoted-printable
158 Content-Type: application/octet-stream; name="circstats.csv"
159 Content-Transfer-Encoding: base64
160 Content-Disposition: attachment; filename="circstats.csv"
166 sendmail(%attachmail) || print "Error: $Mail::Sendmail::error\n";