From d8551b19c242888b3d98cdf2f1506fa0794792ea Mon Sep 17 00:00:00 2001 From: tipaul Date: Mon, 19 May 2003 16:20:51 +0000 Subject: [PATCH] * PARTIAL fix for #322 => gives strange results imho * removing unused subs * indenting with tabs * adding monthly report (stats.pl?time=month) --- C4/Stats.pm | 303 ++++++------------ koha-tmpl/intranet-tmpl/default/en/stats.tmpl | 7 - stats.pl | 170 +++++----- 3 files changed, 189 insertions(+), 291 deletions(-) diff --git a/C4/Stats.pm b/C4/Stats.pm index accae1cfff..3159ac3af4 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -48,7 +48,7 @@ the Koha database, which acts as an activity log. =cut @ISA = qw(Exporter); -@EXPORT = qw(&UpdateStats &statsreport &Count &Overdues &TotalOwing +@EXPORT = qw(&UpdateStats &statsreport &TotalOwing &TotalPaid &getcharges &Getpaidbranch &unfilledreserves); =item UpdateStats @@ -71,222 +71,127 @@ 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'}; - print $borrowernumber; - # FIXME - Use $dbh->do() instead - my $sth=$dbh->prepare("Insert into statistics - (datetime,branch,type,usercode,value, - other,itemnumber,itemtype,borrowernumber) - values (now(),'$branch','$type','$user','$amount', - '$other','$itemnum','$itemtype','$borrowernumber')"); - $sth->execute; - $sth->finish; + #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'}; + print $borrowernumber; + # 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; } -# FIXME - Why does this function exist? Why not just rename &circrep -# to &statsreport? -# Then again, it only appears to be used in reports.pl which, in turn, -# doesn't appear to be used. So presumably this function is obsolete. -# If not, it needs a POD. -sub statsreport { - #module to return a list of stats for a given day,time,branch type - #or to return search stats - my ($type,$time)=@_; - my @data; -# print "here"; -# if ($type eq 'issue'){ - @data=circrep($time,$type); -# } - return(@data); -} - -# Only used internally. Probably useless: see comment for -# &statsreport. -sub circrep { - my ($time,$type)=@_; - my $dbh = C4::Context->dbh; - my $query="Select * from statistics"; - if ($time eq 'today'){ - # FIXME - What is this supposed to do? MySQL 3.23.42 barfs on it. - $query=$query." where type='$type' and datetime - >=datetime('yesterday'::date)"; - # FIXME - .= <prepare($query); - $sth->execute; - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ - my %tempvalue; - $tempvalue{'value'}="$data->{'datetime'}\t$data->{'branch'}"; - push(@results, \%tempvalue); - } - $sth->finish; -# print $query; - return(@results); -} - -# FIXME - This is only used in stats.pl, which in turn is never used. -# Otherwise, this needs a POD. -sub Count { - my ($type,$branch,$time,$time2)=@_; - my $dbh = C4::Context->dbh; - my $query="Select count(*) from statistics where type='$type'"; - $query.=" and datetime >= '$time' and datetime< '$time2' and branch='$branch'"; - my $sth=$dbh->prepare($query); - $sth->execute; - my $data=$sth->fetchrow_hashref; - $sth->finish; -# print $query; - return($data->{'count(*)'}); -} - -# FIXME - This function doesn't appear to be used. -# If it is, it needs a POD. -sub Overdues{ - my $dbh = C4::Context->dbh; - my $query="Select count(*) from issues where date_due >= now()"; - my $sth=$dbh->prepare($query); - $sth->execute; - my $count=$sth->fetchrow_hashref; - $sth->finish; - return($count->{'count(*)'}); -} - -# FIXME - Never used. -# Otherwise, it'd need a POD. -sub TotalOwing{ - my ($type)=@_; - my $dbh = C4::Context->dbh; - my $query="Select sum(amountoutstanding) from accountlines"; - if ($type eq 'fine'){ - $query .= " where accounttype='F' or accounttype='FN'"; - } - my $sth=$dbh->prepare($query); -# print $query; - $sth->execute; - my $total=$sth->fetchrow_hashref; - $sth->finish; - return($total->{'sum(amountoutstanding)'}); -} - -# FIXME - Never used. # Otherwise, it'd need a POD. sub TotalPaid { - my ($time)=@_; - my $dbh = C4::Context->dbh; - my $query="Select * from accountlines,borrowers where (accounttype = 'Pay' -or accounttype ='W') - and accountlines.borrowernumber = borrowers.borrowernumber"; - if ($time eq 'today'){ - $query .= " and date = now()"; - } else { - $query.=" and date='$time'"; - } -# 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; - 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"; + if ($time eq 'today'){ + $query .= " and date = now()"; + } else { + $query.=" and date>='$time' and date<='$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; + my @results; + my $i=0; + while (my $data=$sth->fetchrow_hashref){ + $results[$i]=$data; + $i++; + } + $sth->finish; + # print $query; + return(@results); } -# FIXME - Only used in stats.pl, which in turn is never used. # Otherwise, it needs a POD. sub getcharges{ - my($borrowerno,$timestamp)=@_; - my $dbh = C4::Context->dbh; - my $timestamp2=$timestamp-1; - my $query="Select * from accountlines where borrowernumber=$borrowerno - and timestamp = '$timestamp' and accounttype <> 'Pay' and - accounttype <> 'W'"; - my $sth=$dbh->prepare($query); -# print $query,"
"; - $sth->execute; - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ -# if ($data->{'timestamp'} == $timestamp){ - $results[$i]=$data; - $i++; -# } - } - return(@results); + my($borrowerno,$timestamp)=@_; + my $dbh = C4::Context->dbh; + my $timestamp2=$timestamp-1; + my $query="Select * from accountlines where borrowernumber=$borrowerno + and timestamp = '$timestamp' and accounttype <> 'Pay' and + accounttype <> 'W'"; + my $sth=$dbh->prepare($query); + # print $query,"
"; + $sth->execute; + my $i=0; + my @results; + while (my $data=$sth->fetchrow_hashref){ + # if ($data->{'timestamp'} == $timestamp){ + $results[$i]=$data; + $i++; + # } + } + return(@results); } -# This is only used in stats.pl and stats2.pl, neither of which is -# used. # Otherwise, this needs a POD. sub Getpaidbranch{ - my($date,$borrno)=@_; - my $dbh = C4::Context->dbh; - my $query="select * from statistics where type='payment' and datetime - >'$date' and borrowernumber='$borrno'"; - my $sth=$dbh->prepare($query); - $sth->execute; -# print $query; - my $data=$sth->fetchrow_hashref; - $sth->finish; - return($data->{'branch'}); + my($date,$borrno)=@_; + my $dbh = C4::Context->dbh; + my $query="select * from statistics where type='payment' and datetime >'$date' and borrowernumber='$borrno'"; + my $sth=$dbh->prepare($query); + $sth->execute; + # 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 $query="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"; - my $sth=$dbh->prepare($query); - $sth->execute; - my $i=0; - my @results; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - $query="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=$dbh->prepare($query); - $sth->execute; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - return($i,\@results); + my $dbh = C4::Context->dbh; + my $query="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"; + my $sth=$dbh->prepare($query); + $sth->execute; + my $i=0; + my @results; + while (my $data=$sth->fetchrow_hashref){ + $results[$i]=$data; + $i++; + } + $sth->finish; + $query="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=$dbh->prepare($query); + $sth->execute; + while (my $data=$sth->fetchrow_hashref){ + $results[$i]=$data; + $i++; + } + $sth->finish; + return($i,\@results); } 1; diff --git a/koha-tmpl/intranet-tmpl/default/en/stats.tmpl b/koha-tmpl/intranet-tmpl/default/en/stats.tmpl index b72fbcad18..88b52ed975 100644 --- a/koha-tmpl/intranet-tmpl/default/en/stats.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/stats.tmpl @@ -18,13 +18,6 @@ - - - - - - - diff --git a/stats.pl b/stats.pl index 5ba3630946..a11be08023 100755 --- a/stats.pl +++ b/stats.pl @@ -48,25 +48,31 @@ my ($template, $loggedinuser, $cookie) my $date; my $date2; if ($time eq 'yesterday'){ - $date=ParseDate('yesterday'); - $date2=ParseDate('today'); + $date=ParseDate('yesterday'); + $date2=ParseDate('today'); } if ($time eq 'today'){ - $date=ParseDate('today'); - $date2=ParseDate('tomorrow'); + $date=ParseDate('today'); + $date2=ParseDate('tomorrow'); } if ($time eq 'daybefore'){ - $date=ParseDate('2 days ago'); - $date2=ParseDate('yesterday'); + $date=ParseDate('2 days ago'); + $date2=ParseDate('yesterday'); +} +if ($time eq 'month') { + $date = ParseDate('1 month ago'); + $date2 = ParseDate('today'); + warn "d : $date // d2 : $date2"; } if ($time=~ /\//){ - $date=ParseDate($time); - $date2=ParseDateDelta('+ 1 day'); - $date2=DateCalc($date,$date2); + $date=ParseDate($time); + $date2=ParseDateDelta('+ 1 day'); + $date2=DateCalc($date,$date2); } $date=UnixDate($date,'%Y-%m-%d'); $date2=UnixDate($date2,'%Y-%m-%d'); -my @payments=TotalPaid($date); + warn "d : $date // d2 : $date2"; +my @payments=TotalPaid($date,$date2); my $count=@payments; my $total=0; my $oldtime; @@ -75,83 +81,77 @@ my @loop; my %row; my $i=0; while ($i<$count){ - my $time=$payments[$i]{'datetime'}; - my $payments=$payments[$i]{'value'}; - my $charge=0; - my @temp=split(/ /,$payments[$i]{'datetime'}); - my $date=$temp[0]; - my @charges=getcharges($payments[$i]{'borrowernumber'},$payments[$i]{'timestamp'}); - my $count=@charges; - my $temptotalf=0; - my $temptotalr=0; - my $temptotalres=0; - my $temptotalren=0; - my $temptotalw=0; - for (my $i2=0;$i2<$count;$i2++){ - $charge+=$charges[$i2]->{'amount'}; - %row = ( name => $charges[$i2]->{'description'}, - type => $charges[$i2]->{'accounttype'}, - time => $charges[$i2]->{'timestamp'}, - amount => $charges[$i2]->{'amount'}, - branch => $charges[$i2]->{'amountoutstanding'} ); - push(@loop, \%row); - if ($payments[$i]{'accountytpe'} ne 'W'){ - if ($charges[$i2]->{'accounttype'} eq 'Rent'){ - $temptotalr+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; - } - if ($charges[$i2]->{'accounttype'} eq 'F' || $charges[$i2]->{'accounttype'} eq 'FU' || $charges[$i2]->{'accounttype'} eq 'FN' ){ - $temptotalf+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; - } - if ($charges[$i2]->{'accounttype'} eq 'Res'){ - $temptotalres+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; - } - if ($charges[$i2]->{'accounttype'} eq 'R'){ - $temptotalren+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; - } - } - } - - my $hour=substr($payments[$i]{'timestamp'},8,2); - my $min=substr($payments[$i]{'timestamp'},10,2); - my $sec=substr($payments[$i]{'timestamp'},12,2); - my $time="$hour:$min:$sec"; - my $time2="$payments[$i]{'date'}"; - my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'}); - my $bornum=$payments[$i]{'borrowernumber'}; - my $oldtime=$payments[$i]{'timestamp'}; - my $oldtype=$payments[$i]{'accounttype'}; - while ($bornum eq $payments[$i]{'borrowernumber'} && $oldtype == $payments[$i]{'accounttype'} && $oldtime eq $payments[$i]{'timestamp'}){ - my $hour=substr($payments[$i]{'timestamp'},8,2); - my $min=substr($payments[$i]{'timestamp'},10,2); - my $sec=substr($payments[$i]{'timestamp'},12,2); - my $time="$hour:$min:$sec"; - my $time2="$payments[$i]{'date'}"; - my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'}); - - if ($payments[$i]{'accounttype'} eq 'W'){ - $totalw+=$payments[$i]{'amount'}; - } else { - $payments[$i]{'amount'}=$payments[$i]{'amount'}*-1; - $total+=$payments[$i]{'amount'}; - } - - %row = ( name => $payments[$i]{'firstname'} . " " . $payments[$i]{'surname'} . "", - type => $payments[$i]{'accounttype'}, - time => $payments[$i]{'date'}, - amount => $payments[$i]{'amount'}, - branch => $branch ); - - push(@loop, \%row); - - $oldtype=$payments[$i]{'accounttype'}; - $oldtime=$payments[$i]{'timestamp'}; - $bornum=$payments[$i]{'borrowernumber'}; - $i++; - - } + warn " pay : ".$payments[$i]{'timestamp'}; + my $time=$payments[$i]{'datetime'}; + my $payments=$payments[$i]{'value'}; + my $charge=0; + my @temp=split(/ /,$payments[$i]{'datetime'}); + my $date=$temp[0]; + my @charges=getcharges($payments[$i]{'borrowernumber'},$payments[$i]{'timestamp'}); + my $count=@charges; + my $temptotalf=0; + my $temptotalr=0; + my $temptotalres=0; + my $temptotalren=0; + my $temptotalw=0; + for (my $i2=0;$i2<$count;$i2++){ + $charge+=$charges[$i2]->{'amount'}; + %row = ( name => $charges[$i2]->{'description'}, + type => $charges[$i2]->{'accounttype'}, + time => $charges[$i2]->{'timestamp'}, + amount => $charges[$i2]->{'amount'}, + branch => $charges[$i2]->{'amountoutstanding'} ); + push(@loop, \%row); + if ($payments[$i]{'accountytpe'} ne 'W'){ + if ($charges[$i2]->{'accounttype'} eq 'Rent'){ + $temptotalr+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; + } + if ($charges[$i2]->{'accounttype'} eq 'F' || $charges[$i2]->{'accounttype'} eq 'FU' || $charges[$i2]->{'accounttype'} eq 'FN' ){ + $temptotalf+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; + } + if ($charges[$i2]->{'accounttype'} eq 'Res'){ + $temptotalres+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; + } + if ($charges[$i2]->{'accounttype'} eq 'R'){ + $temptotalren+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; + } + } + } + my $hour=substr($payments[$i]{'timestamp'},8,2); + my $min=substr($payments[$i]{'timestamp'},10,2); + my $sec=substr($payments[$i]{'timestamp'},12,2); + my $time="$hour:$min:$sec"; + my $time2="$payments[$i]{'date'}"; + my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'}); + my $bornum=$payments[$i]{'borrowernumber'}; + my $oldtime=$payments[$i]{'timestamp'}; + my $oldtype=$payments[$i]{'accounttype'}; + while ($bornum eq $payments[$i]{'borrowernumber'} && $oldtype == $payments[$i]{'accounttype'} && $oldtime eq $payments[$i]{'timestamp'}){ + my $hour=substr($payments[$i]{'timestamp'},8,2); + my $min=substr($payments[$i]{'timestamp'},10,2); + my $sec=substr($payments[$i]{'timestamp'},12,2); + my $time="$hour:$min:$sec"; + my $time2="$payments[$i]{'date'}"; + my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'}); + if ($payments[$i]{'accounttype'} eq 'W'){ + $totalw+=$payments[$i]{'amount'}; + } else { + $payments[$i]{'amount'}=$payments[$i]{'amount'}*-1; + $total+=$payments[$i]{'amount'}; + } + + %row = ( name => "".$payments[$i]{'firstname'}.$payments[$i]{'surname'} . "", + type => $payments[$i]{'accounttype'}, time => $payments[$i]{'date'}, + amount => $payments[$i]{'amount'}, branch => $branch ); + push(@loop, \%row); + $oldtype=$payments[$i]{'accounttype'}; + $oldtime=$payments[$i]{'timestamp'}; + $bornum=$payments[$i]{'borrowernumber'}; + $i++; + } } -$template->param( loop => \@loop, +$template->param( loop1 => \@loop, totalw => $totalw, total => $total ); -- 2.20.1