From 621e60b86ec3a53965649047c7bd00607f06c870 Mon Sep 17 00:00:00 2001 From: szrj1m Date: Fri, 19 Aug 2005 00:38:33 +0000 Subject: [PATCH] Added &getcredits routine. --- C4/Stats.pm | 255 +++++++++++++++++++++++++++++++--------------------- 1 file changed, 153 insertions(+), 102 deletions(-) diff --git a/C4/Stats.pm b/C4/Stats.pm index 43275b674e..185428db43 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -49,7 +49,7 @@ the Koha database, which acts as an activity log. @ISA = qw(Exporter); @EXPORT = qw(&UpdateStats &statsreport &TotalOwing -&TotalPaid &getcharges &Getpaidbranch &unfilledreserves); +&TotalPaid &getcharges &Getpaidbranch &unfilledreserves &getcredits); =item UpdateStats @@ -71,125 +71,175 @@ 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)=@_; - my $dbh = C4::Context->dbh; - if ($branch eq ''){ - $branch=$env->{'branchcode'}; - } - my $user = $env->{'usercode'}; - # FIXME - Use $dbh->do() instead - my $sth=$dbh->prepare("Insert into statistics (datetime,branch,type,usercode,value, - other,itemnumber,itemtype,borrowernumber) values (now(),?,?,?,?,?,?,?,?)"); - $sth->execute($branch,$type,$user,$amount,$other,$itemnum,$itemtype,$borrowernumber); - $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'}; + print $borrowernumber; + # FIXME - Use $dbh->do() instead + my $sth=$dbh->prepare("Insert into statistics (datetime,branch,type,usercode,value, + other,itemnumber,itemtype,borrowernumber,proccode) values (now(),?,?,?,?,?,?,?,?,?)"); + $sth->execute($branch,$type,$user,$amount,$other,$itemnum,$itemtype,$borrowernumber,$accountno); + $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(@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)=@_; + $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); } # Otherwise, it needs a POD. sub getcharges{ - my($borrowerno,$timestamp)=@_; - my $dbh = C4::Context->dbh; - my $timestamp2=$timestamp-1; - my $query=""; - my $sth=$dbh->prepare("Select * from accountlines where borrowernumber=? - and timestamp = ? and accounttype <> 'Pay' and - accounttype <> 'W'"); - # print $query,"
"; - $sth->execute($borrowerno,$timestamp); - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ - # if ($data->{'timestamp'} == $timestamp){ - $results[$i]=$data; - $i++; - # } - } - return(@results); + 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=? + 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){ + # if ($data->{'timestamp'} == $timestamp){ + $results[$i]=$data; + $i++; + # } + } + 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; + + $sth=$dbh->prepare("Select * from accountlines where (accounttype = 'CR' or accounttype = 'LR') and timestamp >=? and timestamp execute($timestamp,$timestamp2); + + my $i=0; + my @results; + while (my $data=$sth->fetchrow_hashref){ + $results[$i]=$data; + $i++; + } + 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'}); + 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' and cancellationdate - is NULL and biblio.biblionumber=reserves.biblionumber and - reserves.constrainttype='o' - and (reserves.biblionumber=reserveconstraints.biblionumber - and reserves.borrowernumber=reserveconstraints.borrowernumber) - 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' 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); + my $dbh = C4::Context->dbh; + my $sth=$dbh->prepare("select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where found <> 'F' and cancellationdate + is NULL and biblio.biblionumber=reserves.biblionumber and + reserves.constrainttype='o' + and (reserves.biblionumber=reserveconstraints.biblionumber + and reserves.borrowernumber=reserveconstraints.borrowernumber) + 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' 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); } 1; @@ -202,3 +252,4 @@ __END__ Koha Developement team =cut + -- 2.39.2