5 # Copyright 2000-2002 Katipo Communications
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
26 use vars qw($VERSION @ISA @EXPORT);
28 # set the version for version checking
29 $VERSION = $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
30 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };;
34 C4::Stats - Update Koha statistics (log)
42 The C<&UpdateStats> function adds an entry to the statistics table in
43 the Koha database, which acts as an activity log.
52 @EXPORT = qw(&UpdateStats &statsreport &TotalOwing
53 &TotalPaid &getcharges &Getpaidbranch &unfilledreserves &getcredits);
57 &UpdateStats($env, $branch, $type, $value, $other, $itemnumber,
58 $itemtype, $borrowernumber);
60 Adds a line to the statistics table of the Koha database. In effect,
63 C<$branch>, C<$type>, C<$value>, C<$other>, C<$itemnumber>,
64 C<$itemtype>, and C<$borrowernumber> correspond to the fields of the
65 statistics table in the Koha database.
67 If C<$branch> is the empty string, the branch code will be taken from
68 C<$env-E<gt>{branchcode}>.
70 C<$env-E<gt>{usercode}> specifies the value of the C<usercode> field.
75 #module to insert stats data into stats table
76 my ($env,$branch,$type,$amount,$other,$itemnum,$itemtype,$borrowernumber,$accountno)=@_;
77 my $dbh = C4::Context->dbh;
79 $branch=$env->{'branchcode'};
81 my $user = $env->{'usercode'};
82 my $organisation = $env->{'organisation'};
83 print $borrowernumber;
84 # FIXME - Use $dbh->do() instead
85 my $sth=$dbh->prepare("Insert into statistics (datetime,branch,type,usercode,value,
86 other,itemnumber,itemtype,borrowernumber,proccode,associatedborrower) values (now(),?,?,?,?,?,?,?,?,?,?)");
87 $sth->execute($branch,$type,$user,$amount,$other,$itemnum,$itemtype,$borrowernumber,$accountno,$organisation);
91 # Otherwise, it'd need a POD.
94 $time2=$time unless $time2;
95 my $dbh = C4::Context->dbh;
98 #my $query="Select * from accountlines,borrowers where (accounttype = 'Pay' or accounttype ='W')
99 # and accountlines.borrowernumber = borrowers.borrowernumber";
101 #if ($time eq 'today'){
102 # $query .= " and date = now()";
104 # $query.=" and date>=? and date<=?";
105 # @bind = ($time,$time2);
108 my $query="Select * from statistics,borrowers
109 where statistics.borrowernumber= borrowers.borrowernumber
110 and (statistics.type='payment' or statistics.type='writeoff') ";
111 if ($time eq 'today'){
112 $query=$query." and datetime = now()";
114 $query.=" and datetime > '$time'";
118 # $query.=" order by timestamp";
122 my $sth=$dbh->prepare($query);
125 # $sth->execute(@bind);
128 while (my $data=$sth->fetchrow_hashref){
137 # Otherwise, it needs a POD.
139 my($borrowerno,$timestamp,$accountno)=@_;
140 my $dbh = C4::Context->dbh;
141 my $timestamp2=$timestamp-1;
145 # getcharges is now taking accountno. as an argument
147 $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
149 $sth->execute($borrowerno,$accountno);
151 # this bit left in for old 2 arg usage of getcharges
153 $sth=$dbh->prepare("Select * from accountlines where borrowernumber=?
154 and timestamp = ? and accounttype <> 'Pay' and
155 accounttype <> 'W'");
156 $sth->execute($borrowerno,$timestamp);
159 # print $query,"<br>";
162 while (my $data=$sth->fetchrow_hashref){
163 # if ($data->{'timestamp'} == $timestamp){
171 # Otherwise, it needs a POD.
173 my ($date,$date2)=@_;
174 my $dbh = C4::Context->dbh;
176 #takes date converts to timestamps
177 my $padding="000000";
178 (my $a, my $b, my $c) = unpack("A4 x1 A2 x1 A2", $date);
179 (my $x, my $y, my $z) = unpack("A4 x1 A2 x1 A2", $date2);
180 my $timestamp = $a.$b.$c.$padding;
181 my $timestamp2 = $x.$y.$z.$padding;
183 my $sth=$dbh->prepare("Select * from accountlines,borrowers where (((accounttype = 'LR') or (accounttype <> 'Pay'))
184 and amount < 0 and accountlines.borrowernumber = borrowers.borrowernumber
185 and timestamp >=? and timestamp <?)");
186 $sth->execute($timestamp, $timestamp2);
190 while (my $data=$sth->fetchrow_hashref){
199 # Otherwise, this needs a POD.
201 my($date,$borrno)=@_;
202 my $dbh = C4::Context->dbh;
203 my $sth=$dbh->prepare("select * from statistics where type='payment' and datetime >? and borrowernumber=?");
204 $sth->execute($date,$borrno);
206 my $data=$sth->fetchrow_hashref;
208 return($data->{'branch'});
211 # FIXME - This is only used in reservereport.pl and reservereport.xls,
212 # neither of which is used.
213 # Otherwise, it needs a POD.
214 sub unfilledreserves {
215 my $dbh = C4::Context->dbh;
216 my $sth=$dbh->prepare("select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where (found <> 'F' or
217 found is NULL) and cancellationdate
218 is NULL and biblio.biblionumber=reserves.biblionumber and
219 reserves.constrainttype='o'
220 and (reserves.biblionumber=reserveconstraints.biblionumber
221 and reserves.borrowernumber=reserveconstraints.borrowernumber)
223 reserves.borrowernumber=borrowers.borrowernumber and
224 biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by
225 biblio.title,reserves.reservedate");
229 while (my $data=$sth->fetchrow_hashref){
234 $sth=$dbh->prepare("select *,biblio.title from reserves,biblio,borrowers where (found <> 'F' or found is NULL) and cancellationdate
235 is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and
236 reserves.borrowernumber=borrowers.borrowernumber
238 biblio.title,reserves.reservedate");
240 while (my $data=$sth->fetchrow_hashref){
245 return($i,\@results);
255 Koha Developement team <info@koha.org>