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

(-)a/misc/cronjobs/stats/monthly_circulation_statistics.pl (-168 lines)
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
(-)a/misc/cronjobs/stats/monthly_new_items_statistics.pl (-116 lines)
Lines 1-116 Link Here
1
#!/usr/bin/perl
2
#-----------------------------------
3
# Script Name: addstats.pl
4
# Script Version: 1.0
5
# Date:  2006/02/24
6
# Author:  Stephen Hedges (shedges@skemotah.com)
7
# Description: 
8
#	This script creates a comma-separated value file of
9
#	new materials statistics for any given month and year.
10
#       The statistics are grouped by itemtype.
11
# 
12
# Revision History:
13
#    1.0  2006/02/24: 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 warnings;
33
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>);
37
# die unless (/^y/i);
38
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
41
# my ($month,$year);
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);
48
49
open OUTFILE, ">addstats.csv" or die "Cannot open file addstats.csv: $!";
50
print OUTFILE "\"type\",\"count\"\n";
51
52
use C4::Context;
53
use Mail::Sendmail;  # comment out 3 lines if not doing e-mail sending of file
54
use MIME::QuotedPrint;
55
use MIME::Base64;
56
# set the e-mail server -- comment out if not doing e-mail notices
57
unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost';
58
#                                         set your own mail server name here
59
60
my $dbh = C4::Context->dbh;
61
62
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");
63
64
my ($row,$itemtype,$count);
65
66
$sth->execute();
67
68
while ($row = $sth->fetchrow_arrayref) {
69
    $itemtype = $row->[0];
70
    $count = $row->[1];
71
72
    print OUTFILE "$itemtype,$count\n";
73
}
74
$sth->finish;
75
close OUTFILE;
76
$dbh->disconnect;
77
78
# send the outfile as an attachment to the library e-mail
79
80
my %attachmail = (
81
         from => $from_address,
82
         to => $to_addresses,
83
         subject => 'New Items Statistics',
84
        );
85
86
87
my $boundary = "====" . time() . "====";
88
$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\"";
89
90
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";
91
92
my $attachfile = "addstats.csv"; 
93
94
open (F, $attachfile) or die "Cannot read $attachfile: $!";
95
binmode F; undef $/;
96
$attachmail{body} = encode_base64(<F>);
97
close F;
98
99
$boundary = '--'.$boundary;
100
$attachmail{body} = <<END_OF_BODY;
101
$boundary
102
Content-Type: text/plain; charset="iso-8859-1"
103
Content-Transfer-Encoding: quoted-printable
104
105
$attachmessage
106
$boundary
107
Content-Type: application/octet-stream; name="addstats.csv"
108
Content-Transfer-Encoding: base64
109
Content-Disposition: attachment; filename="addstats.csv"
110
111
$attachmail{body}
112
$boundary--
113
END_OF_BODY
114
115
sendmail(%attachmail) || print "Error: $Mail::Sendmail::error\n";
116
(-)a/misc/cronjobs/stats/monthly_new_patron_statistics.pl (-129 lines)
Lines 1-128 Link Here
1
#!/usr/bin/perl
2
#-----------------------------------
3
# Script Name: borrstats.pl
4
# Script Version: 1.0
5
# Date:  2006/02/24
6
# Author:  Stephen Hedges (shedges@skemotah.com)
7
# Description: 
8
#	This script creates a comma-separated value file of
9
#	new borrower statistics for any given month and year.
10
#       The statistics are grouped by borrower type, then by
11
#	branch.
12
# Revision History:
13
#    1.0  2006/02/24: 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, ">borrstats.csv" or die "Cannot open file borrstats.csv: $!";
51
print OUTFILE "\"type\",\"branch\",\"count\"\n";
52
53
use C4::Context;
54
use Mail::Sendmail;  # comment out 3 lines if not doing e-mail sending of file
55
use MIME::QuotedPrint;
56
use MIME::Base64;
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
60
61
my $dbh = C4::Context->dbh;
62
my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode");
63
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");
64
65
my ($rowc,$rowb,$categorycode,$branchcode,$count,$line);
66
67
$sth1->execute();
68
69
while ($rowc = $sth1->fetchrow_arrayref) {
70
    $categorycode = $rowc->[0];
71
    $line = "\"$categorycode\"";
72
    
73
    $sth2->execute($categorycode);
74
75
    while ($rowb = $sth2->fetchrow_arrayref) {
76
	$branchcode = $rowb->[0];
77
	$count = $rowb->[1];
78
79
	$line = $line . ",\"$branchcode\",\"$count\"";
80
    }
81
    $sth2->finish;
82
83
    $line = $line . "\n";
84
    print OUTFILE "$line";
85
}
86
$sth1->finish;
87
close OUTFILE;
88
$dbh->disconnect;
89
90
# send the outfile as an attachment to the library e-mail
91
92
my %attachmail = (
93
         from => $from_address,
94
         to => $to_addresses,
95
         subject => 'New Patrons Statistics',
96
        );
97
98
99
my $boundary = "====" . time() . "====";
100
$attachmail{'content-type'} = "multipart/mixed; boundary=\"$boundary\"";
101
102
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";
103
104
my $attachfile = "borrstats.csv"; 
105
106
open (F, $attachfile) or die "Cannot read $attachfile: $!";
107
binmode F; undef $/;
108
$attachmail{body} = encode_base64(<F>);
109
close F;
110
111
$boundary = '--'.$boundary;
112
$attachmail{body} = <<END_OF_BODY;
113
$boundary
114
Content-Type: text/plain; charset="iso-8859-1"
115
Content-Transfer-Encoding: quoted-printable
116
117
$attachmessage
118
$boundary
119
Content-Type: application/octet-stream; name="borrstats.csv"
120
Content-Transfer-Encoding: base64
121
Content-Disposition: attachment; filename="borrstats.csv"
122
123
$attachmail{body}
124
$boundary--
125
END_OF_BODY
126
127
sendmail(%attachmail) || print "Error: $Mail::Sendmail::error\n";
128
129
- 

Return to bug 10771