1 package C4::Stats; #assumes C4/Stats
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
25 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
27 # set the version for version checking
31 @EXPORT = qw(&UpdateStats &statsreport &Count &Overdues &TotalOwing
32 &TotalPaid &getcharges &Getpaidbranch &unfilledreserves);
36 #module to insert stats data into stats table
37 my ($env,$branch,$type,$amount,$other,$itemnum,$itemtype,$borrowernumber)=@_;
40 $branch=$env->{'branchcode'};
42 my $user = $env->{'usercode'};
43 print $borrowernumber;
44 my $sth=$dbh->prepare("Insert into statistics
45 (datetime,branch,type,usercode,value,
46 other,itemnumber,itemtype,borrowernumber)
47 values (now(),'$branch','$type','$user','$amount',
48 '$other','$itemnum','$itemtype','$borrowernumber')");
55 #module to return a list of stats for a given day,time,branch type
56 #or to return search stats
60 # if ($type eq 'issue'){
61 @data=circrep($time,$type);
69 my $query="Select * from statistics";
70 if ($time eq 'today'){
71 $query=$query." where type='$type' and datetime
72 >=datetime('yesterday'::date)";
74 my $sth=$dbh->prepare($query);
78 while (my $data=$sth->fetchrow_hashref){
79 $results[$i]="$data->{'datetime'}\t$data->{'branch'}";
90 my ($type,$branch,$time,$time2)=@_;
92 my $query="Select count(*) from statistics where type='$type'";
93 $query.=" and datetime >= '$time' and datetime< '$time2' and branch='$branch'";
94 my $sth=$dbh->prepare($query);
96 my $data=$sth->fetchrow_hashref;
100 return($data->{'count(*)'});
105 my $query="Select count(*) from issues where date_due >= now()";
106 my $sth=$dbh->prepare($query);
108 my $count=$sth->fetchrow_hashref;
111 return($count->{'count(*)'});
117 my $query="Select sum(amountoutstanding) from accountlines";
118 if ($type eq 'fine'){
119 $query=$query." where accounttype='F' or accounttype='FN'";
121 my $sth=$dbh->prepare($query);
124 my $total=$sth->fetchrow_hashref;
127 return($total->{'sum(amountoutstanding)'});
133 my $query="Select * from accountlines,borrowers where (accounttype = 'Pay'
135 and accountlines.borrowernumber = borrowers.borrowernumber";
136 if ($time eq 'today'){
137 $query=$query." and date = now()";
139 $query.=" and date='$time'";
141 # my $query="Select * from statistics,borrowers
142 # where statistics.borrowernumber= borrowers.borrowernumber
143 # and (statistics.type='payment' or statistics.type='writeoff') ";
144 # if ($time eq 'today'){
145 # $query=$query." and datetime = now()";
147 # $query.=" and datetime > '$time'";
149 $query.=" order by timestamp";
151 my $sth=$dbh->prepare($query);
155 while (my $data=$sth->fetchrow_hashref){
166 my($borrowerno,$timestamp)=@_;
168 my $timestamp2=$timestamp-1;
169 my $query="Select * from accountlines where borrowernumber=$borrowerno
170 and timestamp = '$timestamp' and accounttype <> 'Pay' and
172 my $sth=$dbh->prepare($query);
173 # print $query,"<br>";
177 while (my $data=$sth->fetchrow_hashref){
178 # if ($data->{'timestamp'} == $timestamp){
188 my($date,$borrno)=@_;
190 my $query="select * from statistics where type='payment' and datetime
191 >'$date' and borrowernumber='$borrno'";
192 my $sth=$dbh->prepare($query);
195 my $data=$sth->fetchrow_hashref;
198 return($data->{'branch'});
201 sub unfilledreserves {
203 my $query="select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where found <> 'F' and cancellationdate
204 is NULL and biblio.biblionumber=reserves.biblionumber and
205 reserves.constrainttype='o'
206 and (reserves.biblionumber=reserveconstraints.biblionumber
207 and reserves.borrowernumber=reserveconstraints.borrowernumber)
209 reserves.borrowernumber=borrowers.borrowernumber and
210 biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by
211 biblio.title,reserves.reservedate";
212 my $sth=$dbh->prepare($query);
216 while (my $data=$sth->fetchrow_hashref){
221 $query="select *,biblio.title from reserves,biblio,borrowers where found <> 'F' and cancellationdate
222 is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and
223 reserves.borrowernumber=borrowers.borrowernumber
225 biblio.title,reserves.reservedate";
226 $sth=$dbh->prepare($query);
228 while (my $data=$sth->fetchrow_hashref){
234 return($i,\@results);
237 END { } # module clean-up code here (global destructor)