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 );
35 C4::Stats - Update Koha statistics (log)
43 The C<&UpdateStats> function adds an entry to the statistics table in
44 the Koha database, which acts as an activity log.
53 @EXPORT = qw(&UpdateStats &statsreport &TotalOwing
54 &TotalPaid &getcharges &Getpaidbranch &unfilledreserves &getcredits
59 &UpdateStats($env, $branch, $type, $value, $other, $itemnumber,
60 $itemtype, $borrowernumber);
62 Adds a line to the statistics table of the Koha database. In effect,
65 C<$branch>, C<$type>, C<$value>, C<$other>, C<$itemnumber>,
66 C<$itemtype>, and C<$borrowernumber> correspond to the fields of the
67 statistics table in the Koha database.
69 If C<$branch> is the empty string, the branch code will be taken from
70 C<$env-E<gt>{branchcode}>.
72 C<$env-E<gt>{usercode}> specifies the value of the C<usercode> field.
79 #module to insert stats data into stats table
82 $amount, $other, $itemnum,
83 $itemtype, $borrowernumber, $accountno
86 my $dbh = C4::Context->dbh;
87 if ( $branch eq '' ) {
88 $branch = $env->{'branchcode'};
90 my $user = $env->{'usercode'};
91 my $organisation = $env->{'organisation'};
92 print $borrowernumber;
94 # FIXME - Use $dbh->do() instead
95 my $sth = $dbh->prepare(
96 "Insert into statistics (datetime,branch,type,usercode,value,
97 other,itemnumber,itemtype,borrowernumber,proccode,associatedborrower) values (now(),?,?,?,?,?,?,?,?,?,?)"
100 $branch, $type, $user, $amount,
101 $other, $itemnum, $itemtype, $borrowernumber,
102 $accountno, $organisation
107 # Otherwise, it'd need a POD.
109 my ( $time, $time2, $spreadsheet ) = @_;
110 $time2 = $time unless $time2;
111 my $dbh = C4::Context->dbh;
112 my $query = "SELECT * FROM statistics,borrowers
113 WHERE statistics.borrowernumber= borrowers.borrowernumber
114 AND (statistics.type='payment' OR statistics.type='writeoff') ";
115 if ( $time eq 'today' ) {
116 $query = $query . " AND datetime = now()";
119 $query .= " AND datetime > '$time'";
121 if ( $time2 ne '' ) {
122 $query .= " AND datetime < '$time2'";
125 $query .= " ORDER BY branch, type";
127 my $sth = $dbh->prepare($query);
130 while ( my $data = $sth->fetchrow_hashref ) {
131 push @results, $data;
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(
148 "Select * from accountlines where borrowernumber=?
151 $sth->execute( $borrowerno, $accountno );
153 # this bit left in for old 2 arg usage of getcharges
156 $sth = $dbh->prepare(
157 "Select * from accountlines where borrowernumber=?
158 and timestamp = ? and accounttype <> 'Pay' and
161 $sth->execute( $borrowerno, $timestamp );
164 # print $query,"<br>";
167 while ( my $data = $sth->fetchrow_hashref ) {
169 # if ($data->{'timestamp'} == $timestamp){
170 $results[$i] = $data;
178 # Otherwise, it needs a POD.
180 my ( $date, $date2 ) = @_;
181 my $dbh = C4::Context->dbh;
183 #takes date converts to timestamps
184 my $padding = "000000";
185 ( my $a, my $b, my $c ) = unpack( "A4 x1 A2 x1 A2", $date );
186 ( my $x, my $y, my $z ) = unpack( "A4 x1 A2 x1 A2", $date2 );
187 my $timestamp = $a . $b . $c . $padding;
188 my $timestamp2 = $x . $y . $z . $padding;
190 my $sth = $dbh->prepare(
191 "Select * from accountlines,borrowers where (((accounttype = 'LR') or (accounttype <> 'Pay'))
192 and amount < 0 and accountlines.borrowernumber = borrowers.borrowernumber
193 and timestamp >=? and timestamp <?)"
195 $sth->execute( $timestamp, $timestamp2 );
199 while ( my $data = $sth->fetchrow_hashref ) {
200 $results[$i] = $data;
207 my ( $date, $date2 ) = @_;
208 my $dbh = C4::Context->dbh;
210 #takes date converts to timestamps
211 my $padding = "000000";
212 ( my $a, my $b, my $c ) = unpack( "A4 x1 A2 x1 A2", $date );
213 ( my $x, my $y, my $z ) = unpack( "A4 x1 A2 x1 A2", $date2 );
214 my $timestamp = $a . $b . $c . $padding;
215 my $timestamp2 = $x . $y . $z . $padding;
217 my $sth = $dbh->prepare(
218 "Select * from accountlines,borrowers where (accounttype = 'REF'
219 and accountlines.borrowernumber = borrowers.borrowernumber
220 and timestamp >=? and timestamp <?)"
222 $sth->execute( $timestamp, $timestamp2 );
225 while ( my $data = $sth->fetchrow_hashref ) {
226 push @results, $data;
231 # Otherwise, this needs a POD.
233 my ( $date, $borrno ) = @_;
234 my $dbh = C4::Context->dbh;
237 "select * from statistics where type='payment' and datetime >? and borrowernumber=?"
239 $sth->execute( $date, $borrno );
242 my $data = $sth->fetchrow_hashref;
244 return ( $data->{'branch'} );
247 # FIXME - This is only used in reservereport.pl and reservereport.xls,
248 # neither of which is used.
249 # Otherwise, it needs a POD.
250 sub unfilledreserves {
251 my $dbh = C4::Context->dbh;
252 my $sth = $dbh->prepare(
253 "select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where (found <> 'F' or
254 found is NULL) and cancellationdate
255 is NULL and biblio.biblionumber=reserves.biblionumber and
256 reserves.constrainttype='o'
257 and (reserves.biblionumber=reserveconstraints.biblionumber
258 and reserves.borrowernumber=reserveconstraints.borrowernumber)
260 reserves.borrowernumber=borrowers.borrowernumber and
261 biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by
262 biblio.title,reserves.reservedate"
267 while ( my $data = $sth->fetchrow_hashref ) {
268 $results[$i] = $data;
272 $sth = $dbh->prepare(
273 "select *,biblio.title from reserves,biblio,borrowers where (found <> 'F' or found is NULL) and cancellationdate
274 is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and
275 reserves.borrowernumber=borrowers.borrowernumber
277 biblio.title,reserves.reservedate"
280 while ( my $data = $sth->fetchrow_hashref ) {
281 $results[$i] = $data;
285 return ( $i, \@results );
295 Koha Developement team <info@koha.org>