From dbdd62422eb4c0691b3d16fbe776918c794253b8 Mon Sep 17 00:00:00 2001 From: rangi Date: Wed, 7 Jun 2006 03:58:37 +0000 Subject: [PATCH] Merging in Katipo changes plus perltidy code format --- C4/Stats.pm | 314 +++++++++++++++++++++++++++++----------------------- 1 file changed, 177 insertions(+), 137 deletions(-) diff --git a/C4/Stats.pm b/C4/Stats.pm index 86ee905cc5..838dae0479 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -27,7 +27,8 @@ use vars qw($VERSION @ISA @EXPORT); # set the version for version checking $VERSION = $VERSION = do { my @v = '$Revision$' =~ /\d+/g; - shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };; + shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); +}; =head1 NAME @@ -48,9 +49,10 @@ the Koha database, which acts as an activity log. =cut -@ISA = qw(Exporter); +@ISA = qw(Exporter); @EXPORT = qw(&UpdateStats &statsreport &TotalOwing -&TotalPaid &getcharges &Getpaidbranch &unfilledreserves &getcredits); + &TotalPaid &getcharges &Getpaidbranch &unfilledreserves &getcredits + getrefunds); =item UpdateStats @@ -70,150 +72,185 @@ C<$env-E{branchcode}>. C<$env-E{usercode}> specifies the value of the C field. =cut + #' sub UpdateStats { - #module to insert stats data into stats table - my ($env,$branch,$type,$amount,$other,$itemnum,$itemtype,$borrowernumber,$accountno)=@_; - my $dbh = C4::Context->dbh; - if ($branch eq ''){ - $branch=$env->{'branchcode'}; - } - my $user = $env->{'usercode'}; - my $organisation = $env->{'organisation'}; - print $borrowernumber; - # FIXME - Use $dbh->do() instead - my $sth=$dbh->prepare("Insert into statistics (datetime,branch,type,usercode,value, - other,itemnumber,itemtype,borrowernumber,proccode,associatedborrower) values (now(),?,?,?,?,?,?,?,?,?,?)"); - $sth->execute($branch,$type,$user,$amount,$other,$itemnum,$itemtype,$borrowernumber,$accountno,$organisation); - $sth->finish; + + #module to insert stats data into stats table + my ( + $env, $branch, $type, + $amount, $other, $itemnum, + $itemtype, $borrowernumber, $accountno + ) + = @_; + my $dbh = C4::Context->dbh; + if ( $branch eq '' ) { + $branch = $env->{'branchcode'}; + } + my $user = $env->{'usercode'}; + my $organisation = $env->{'organisation'}; + print $borrowernumber; + + # FIXME - Use $dbh->do() instead + my $sth = $dbh->prepare( + "Insert into statistics (datetime,branch,type,usercode,value, + other,itemnumber,itemtype,borrowernumber,proccode,associatedborrower) values (now(),?,?,?,?,?,?,?,?,?,?)" + ); + $sth->execute( + $branch, $type, $user, $amount, + $other, $itemnum, $itemtype, $borrowernumber, + $accountno, $organisation + ); + $sth->finish; } # Otherwise, it'd need a POD. sub TotalPaid { - my ($time,$time2)=@_; - $time2=$time unless $time2; - my $dbh = C4::Context->dbh; - - - #my $query="Select * from accountlines,borrowers where (accounttype = 'Pay' or accounttype ='W') - # and accountlines.borrowernumber = borrowers.borrowernumber"; - #my @bind = (); - #if ($time eq 'today'){ - # $query .= " and date = now()"; - #} else { - # $query.=" and date>=? and date<=?"; - # @bind = ($time,$time2); - #} - - my $query="Select * from statistics,borrowers - where statistics.borrowernumber= borrowers.borrowernumber - and (statistics.type='payment' or statistics.type='writeoff') "; - if ($time eq 'today'){ - $query=$query." and datetime = now()"; - } else { - $query.=" and datetime > '$time'"; - } - - - # $query.=" order by timestamp"; - - # print $query; - - my $sth=$dbh->prepare($query); - - $sth->execute(); - # $sth->execute(@bind); - my @results; - my $i=0; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - # print $query; - return(@results); + my ( $time, $time2, $spreadsheet ) = @_; + $time2 = $time unless $time2; + my $dbh = C4::Context->dbh; + my $query = "SELECT * FROM statistics,borrowers + WHERE statistics.borrowernumber= borrowers.borrowernumber + AND (statistics.type='payment' OR statistics.type='writeoff') "; + if ( $time eq 'today' ) { + $query = $query . " AND datetime = now()"; + } + else { + $query .= " AND datetime > '$time'"; + } + if ( $time2 ne '' ) { + $query .= " AND datetime < '$time2'"; + } + if ($spreadsheet) { + $query .= " ORDER BY branch, type"; + } + my $sth = $dbh->prepare($query); + $sth->execute(); + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; + } + $sth->finish; + return (@results); } # Otherwise, it needs a POD. -sub getcharges{ - my($borrowerno,$timestamp,$accountno)=@_; - my $dbh = C4::Context->dbh; - my $timestamp2=$timestamp-1; - my $query=""; - my $sth; - - # getcharges is now taking accountno. as an argument - if ($accountno){ - $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? - and accountno = ?"); - $sth->execute($borrowerno,$accountno); +sub getcharges { + my ( $borrowerno, $timestamp, $accountno ) = @_; + my $dbh = C4::Context->dbh; + my $timestamp2 = $timestamp - 1; + my $query = ""; + my $sth; + + # getcharges is now taking accountno. as an argument + if ($accountno) { + $sth = $dbh->prepare( + "Select * from accountlines where borrowernumber=? + and accountno = ?" + ); + $sth->execute( $borrowerno, $accountno ); # this bit left in for old 2 arg usage of getcharges - } else { - $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? + } + else { + $sth = $dbh->prepare( + "Select * from accountlines where borrowernumber=? and timestamp = ? and accounttype <> 'Pay' and - accounttype <> 'W'"); - $sth->execute($borrowerno,$timestamp); - } - - # print $query,"
"; - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ + accounttype <> 'W'" + ); + $sth->execute( $borrowerno, $timestamp ); + } + + # print $query,"
"; + my $i = 0; + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + # if ($data->{'timestamp'} == $timestamp){ - $results[$i]=$data; - $i++; + $results[$i] = $data; + $i++; + # } - } - return(@results); + } + return (@results); } # Otherwise, it needs a POD. -sub getcredits{ - my ($date,$date2)=@_; - my $dbh = C4::Context->dbh; - - #takes date converts to timestamps - my $padding="000000"; - (my $a, my $b, my $c) = unpack("A4 x1 A2 x1 A2", $date); - (my $x, my $y, my $z) = unpack("A4 x1 A2 x1 A2", $date2); - my $timestamp = $a.$b.$c.$padding; - my $timestamp2 = $x.$y.$z.$padding; - - my $sth=$dbh->prepare("Select * from accountlines,borrowers where (((accounttype = 'LR') or (accounttype <> 'Pay')) +sub getcredits { + my ( $date, $date2 ) = @_; + my $dbh = C4::Context->dbh; + + #takes date converts to timestamps + my $padding = "000000"; + ( my $a, my $b, my $c ) = unpack( "A4 x1 A2 x1 A2", $date ); + ( my $x, my $y, my $z ) = unpack( "A4 x1 A2 x1 A2", $date2 ); + my $timestamp = $a . $b . $c . $padding; + my $timestamp2 = $x . $y . $z . $padding; + + my $sth = $dbh->prepare( +"Select * from accountlines,borrowers where (((accounttype = 'LR') or (accounttype <> 'Pay')) and amount < 0 and accountlines.borrowernumber = borrowers.borrowernumber - and timestamp >=? and timestamp execute($timestamp, $timestamp2); - - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - return(@results); + and timestamp >=? and timestamp execute( $timestamp, $timestamp2 ); + + my $i = 0; + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + $results[$i] = $data; + $i++; + } + return (@results); } - +sub getrefunds { + my ( $date, $date2 ) = @_; + my $dbh = C4::Context->dbh; + + #takes date converts to timestamps + my $padding = "000000"; + ( my $a, my $b, my $c ) = unpack( "A4 x1 A2 x1 A2", $date ); + ( my $x, my $y, my $z ) = unpack( "A4 x1 A2 x1 A2", $date2 ); + my $timestamp = $a . $b . $c . $padding; + my $timestamp2 = $x . $y . $z . $padding; + + my $sth = $dbh->prepare( +"Select * from accountlines,borrowers where (accounttype = 'REF' + and accountlines.borrowernumber = borrowers.borrowernumber + and timestamp >=? and timestamp execute( $timestamp, $timestamp2 ); + + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; + } + return (@results); +} # Otherwise, this needs a POD. -sub Getpaidbranch{ - my($date,$borrno)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select * from statistics where type='payment' and datetime >? and borrowernumber=?"); - $sth->execute($date,$borrno); - # print $query; - my $data=$sth->fetchrow_hashref; - $sth->finish; - return($data->{'branch'}); +sub Getpaidbranch { + my ( $date, $borrno ) = @_; + my $dbh = C4::Context->dbh; + my $sth = + $dbh->prepare( +"select * from statistics where type='payment' and datetime >? and borrowernumber=?" + ); + $sth->execute( $date, $borrno ); + + # print $query; + my $data = $sth->fetchrow_hashref; + $sth->finish; + return ( $data->{'branch'} ); } # FIXME - This is only used in reservereport.pl and reservereport.xls, # neither of which is used. # Otherwise, it needs a POD. sub unfilledreserves { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where (found <> 'F' or + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( +"select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where (found <> 'F' or found is NULL) and cancellationdate is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='o' @@ -222,27 +259,30 @@ sub unfilledreserves { and reserves.borrowernumber=borrowers.borrowernumber and biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by - biblio.title,reserves.reservedate"); - $sth->execute; - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - $sth=$dbh->prepare("select *,biblio.title from reserves,biblio,borrowers where (found <> 'F' or found is NULL) and cancellationdate + biblio.title,reserves.reservedate" + ); + $sth->execute; + my $i = 0; + my @results; + while ( my $data = $sth->fetchrow_hashref ) { + $results[$i] = $data; + $i++; + } + $sth->finish; + $sth = $dbh->prepare( +"select *,biblio.title from reserves,biblio,borrowers where (found <> 'F' or found is NULL) and cancellationdate is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and reserves.borrowernumber=borrowers.borrowernumber order by - biblio.title,reserves.reservedate"); - $sth->execute; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - return($i,\@results); + biblio.title,reserves.reservedate" + ); + $sth->execute; + while ( my $data = $sth->fetchrow_hashref ) { + $results[$i] = $data; + $i++; + } + $sth->finish; + return ( $i, \@results ); } 1; -- 2.39.5