|
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 |
|