From ef4f62e71484c2ad94740c1950c08f6a0004d35c Mon Sep 17 00:00:00 2001 From: Joshua Ferraro Date: Sun, 10 Aug 2008 15:38:42 -0500 Subject: [PATCH] Adding three statistics files that NPL uses, they don't work 100% with stock 3.0 yet but are good examples of custom scripts that libraries might want to write and schedule periodically --- .../stats/monthly_circulation_statistics.pl | 167 ++++++++++++++++++ .../stats/monthly_new_items_statistics.pl | 114 ++++++++++++ .../stats/monthly_new_patron_statistics.pl | 127 +++++++++++++ 3 files changed, 408 insertions(+) create mode 100755 misc/cronjobs/stats/monthly_circulation_statistics.pl create mode 100755 misc/cronjobs/stats/monthly_new_items_statistics.pl create mode 100755 misc/cronjobs/stats/monthly_new_patron_statistics.pl diff --git a/misc/cronjobs/stats/monthly_circulation_statistics.pl b/misc/cronjobs/stats/monthly_circulation_statistics.pl new file mode 100755 index 0000000000..961d7e4914 --- /dev/null +++ b/misc/cronjobs/stats/monthly_circulation_statistics.pl @@ -0,0 +1,167 @@ +#!/usr/bin/perl -w +#----------------------------------- +# Script Name: circstats.pl +# Script Version: 1.0 +# Date: 2006/02/07 +# Author: Stephen Hedges (shedges@skemotah.com) +# Description: +# This script creates a comma-separated value file of +# circulation statistics for any given month and year. +# The statistics are grouped by itemtype, then by branch, +# then by issues and renewals. +# Revision History: +# 1.0 2006/02/07: original version +#----------------------------------- +# Contributed 2003-6 by Skemotah Solutions +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along with +# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA + +# use strict; + +# UNCOMMENT the following lines if running from a command line +# 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) "; +# chomp($_ = ); +# die unless (/^y/i); + +# UNCOMMENT the following lines if getting old stats (but be aware that renewal numbers are affected by deletes) +# YOU WILL also need to modify the SQLs to use these dates +# my ($month,$year); +# print "Get statistics for which month (1 to 12)? "; +# chomp($month = ); +# die if ($month < 1 || $month > 12); +# print "Get statistics for which year (2000 to 2050)? "; +# chomp($year = ); +# die if ($year < 2000 || $year > 2050); + +open OUTFILE, ">circstats.csv" or die "Cannot open file circstats.csv: $!"; +print OUTFILE "\"ccode\",\"branch\",\"issues\",\"renewals\"\n"; + +use C4::Context; +use C4::Koha; +use Mail::Sendmail; # comment out 3 lines if not doing e-mail sending of file +use MIME::QuotedPrint; +use MIME::Base64; +# set the e-mail server -- comment out if not doing e-mail notices +unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; +# set your own mail server name here + +my $dbh = C4::Context->dbh; +#my $sth1 = $dbh->prepare ("SELECT itemtype FROM itemtypes ORDER BY itemtype"); +my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode"); + +# number of checkouts for this library +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"); + +# number of renewals for this library +my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems + WHERE YEAR(statistics.datetime)=YEAR(SUBDATE('2007-01-01',INTERVAL 1 MONTH)) + AND MONTH(statistics.datetime)=MONTH(SUBDATE('2007-01-01',INTERVAL 1 MONTH)) + AND statistics.itemnumber=items.itemnumber + AND biblioitems.ccode=? + AND homebranch=? + AND biblioitems.biblioitemnumber=items.biblioitemnumber + AND statistics.type='renew' + GROUP BY statistics.type"); + +# find the itemnumbers +my ($rowt,$rowb,$rowi,$rowr,$itemtype,$branchcode,$branchname,$issues,$renews,$line); + +#$sth1->execute(); +my ($ccode_count,@ccode_results) = GetCcodes; + +#for my $ccode (@ccode_results); +# loop through every itemtype +#while ($rowt = $sth1->fetchrow_arrayref) { +for (my $i=0;$i{authorised_value}; #rowt->[0]; + $line = "\"$itemtype\""; +# warn "$itemtype\n"; + # find branchnames + $sth2->execute(); + + # find the number of issues per itemtype in this branch + while ($rowb = $sth2->fetchrow_arrayref) { + $branchcode = $rowb->[0]; + $branchname = $rowb->[1]; + $sth3->execute($itemtype,$branchcode); # find issues by itemtype per branch + $rowi = $sth3->fetchrow_arrayref; + $issues = $rowi->[0]; # count + unless ($issues) {$issues=""} + $sth3->finish; + + $sth4->execute($itemtype,$branchcode); # find reserves by itemtype per branch + $rowr = $sth4->fetchrow_arrayref; # count + $renews = $rowr->[0]; + unless ($renews) {$renews=""} + $sth4->finish; + + # put the data in this line + $line = $line . ",\"$branchname\",\"$issues\",\"$renews\""; +# warn "LINE: $branchname $issues $renews\n"; + } + $sth2->finish; + + $line = $line . "\n"; + print OUTFILE "$line"; + } +} +#$sth1->finish; +close OUTFILE; +$dbh->disconnect; + +# send the outfile as an attachment to the library e-mail + +my %attachmail = ( + from => $from_address, + to => $to_addresses, + subject => 'Circulation Statistics', + ); + + +my $boundary = "====" . time() . "===="; +$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\""; + +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"; + +my $attachfile = "circstats.csv"; + +open (F, $attachfile) or die "Cannot read $attachfile: $!"; +binmode F; undef $/; +$attachmail{body} = encode_base64(); +close F; + +$boundary = '--'.$boundary; +$attachmail{body} = <); +# die unless (/^y/i); + +# UNCOMMENT the following lines if getting old stats (but be aware that renewal numbers are affected by deletes) +# YOU WILL also need to modify the SQLs to use these dates +# my ($month,$year); +# print "Get statistics for which month (1 to 12)? "; +# chomp($month = ); +# die if ($month < 1 || $month > 12); +# print "Get statistics for which year (2000 to 2050)? "; +# chomp($year = ); +# die if ($year < 2000 || $year > 2050); + +open OUTFILE, ">addstats.csv" or die "Cannot open file addstats.csv: $!"; +print OUTFILE "\"type\",\"count\"\n"; + +use C4::Context; +use Mail::Sendmail; # comment out 3 lines if not doing e-mail sending of file +use MIME::QuotedPrint; +use MIME::Base64; +# set the e-mail server -- comment out if not doing e-mail notices +unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; +# set your own mail server name here + +my $dbh = C4::Context->dbh; + +my $sth = $dbh->prepare ("SELECT biblioitems.ccode,COUNT(biblioitems.ccode) FROM items,biblioitems WHERE YEAR(items.dateaccessioned)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(items.dateaccessioned)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY biblioitems.ccode"); + +my ($row,$itemtype,$count); + +$sth->execute(); + +while ($row = $sth->fetchrow_arrayref) { + $itemtype = $row->[0]; + $count = $row->[1]; + + print OUTFILE "$itemtype,$count\n"; +} +$sth->finish; +close OUTFILE; +$dbh->disconnect; + +# send the outfile as an attachment to the library e-mail + +my %attachmail = ( + from => $from_address, + to => $to_addresses, + subject => 'New Items Statistics', + ); + + +my $boundary = "====" . time() . "===="; +$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\""; + +my $attachmessage = "Attached is the file of new materials statistics for the previous month. Please use this file to calculate the values for the adds spreadsheet.\n"; + +my $attachfile = "addstats.csv"; + +open (F, $attachfile) or die "Cannot read $attachfile: $!"; +binmode F; undef $/; +$attachmail{body} = encode_base64(); +close F; + +$boundary = '--'.$boundary; +$attachmail{body} = <); +# die unless (/^y/i); + +# UNCOMMENT the following lines if getting old stats (but be aware that renewal numbers are affected by deletes) +# YOU WILL also need to modify the SQLs to use these dates +# my ($month,$year); +# print "Get statistics for which month (1 to 12)? "; +# chomp($month = ); +# die if ($month < 1 || $month > 12); +# print "Get statistics for which year (2000 to 2050)? "; +# chomp($year = ); +# die if ($year < 2000 || $year > 2050); + +open OUTFILE, ">borrstats.csv" or die "Cannot open file borrstats.csv: $!"; +print OUTFILE "\"type\",\"branch\",\"count\"\n"; + +use C4::Context; +use Mail::Sendmail; # comment out 3 lines if not doing e-mail sending of file +use MIME::QuotedPrint; +use MIME::Base64; +# set the e-mail server -- comment out if not doing e-mail notices +unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; +# set your own mail server name here + +my $dbh = C4::Context->dbh; +my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode"); +my $sth2 = $dbh->prepare ("SELECT branchcode,COUNT(branchcode) FROM borrowers WHERE categorycode=? AND YEAR(dateenrolled)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(dateenrolled)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) GROUP BY branchcode"); + +my ($rowc,$rowb,$categorycode,$branchcode,$count,$line); + +$sth1->execute(); + +while ($rowc = $sth1->fetchrow_arrayref) { + $categorycode = $rowc->[0]; + $line = "\"$categorycode\""; + + $sth2->execute($categorycode); + + while ($rowb = $sth2->fetchrow_arrayref) { + $branchcode = $rowb->[0]; + $count = $rowb->[1]; + + $line = $line . ",\"$branchcode\",\"$count\""; + } + $sth2->finish; + + $line = $line . "\n"; + print OUTFILE "$line"; +} +$sth1->finish; +close OUTFILE; +$dbh->disconnect; + +# send the outfile as an attachment to the library e-mail + +my %attachmail = ( + from => $from_address, + to => $to_addresses, + subject => 'New Patrons Statistics', + ); + + +my $boundary = "====" . time() . "===="; +$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\""; + +my $attachmessage = "Attached is the file of new borrower statistics for the previous month. Please open the statistics spreadsheet for Page 2, open this file in a new spreadsheet, and paste the numbers from this file into the statistics spreadsheet.\n"; + +my $attachfile = "borrstats.csv"; + +open (F, $attachfile) or die "Cannot read $attachfile: $!"; +binmode F; undef $/; +$attachmail{body} = encode_base64(); +close F; + +$boundary = '--'.$boundary; +$attachmail{body} = <