From fb870fb2c83c4cdf428d1aa801db2826e7e960ac Mon Sep 17 00:00:00 2001 From: rangi Date: Mon, 2 Apr 2001 22:51:26 +0000 Subject: [PATCH] Reworking statistics for payments and writeoffs --- C4/Accounts2.pm | 3 +- C4/Stats.pm | 33 ++++++++----- pay.pl | 8 +++ sec/writeoff.pl | 3 ++ stats.pl | 126 +++++++++++++++++++++++++++++------------------- 5 files changed, 112 insertions(+), 61 deletions(-) diff --git a/C4/Accounts2.pm b/C4/Accounts2.pm index 18bad780b8..27ced45a2a 100755 --- a/C4/Accounts2.pm +++ b/C4/Accounts2.pm @@ -61,6 +61,7 @@ sub recordpayment{ my $updquery = ""; my $newamtos = 0; my $accdata = ""; + my $branch=$env->{'branchcode'}; my $amountleft = $data; # begin transaction my $nextaccntno = getnextacctno($env,$bornumber,$dbh); @@ -100,7 +101,7 @@ sub recordpayment{ my $usth = $dbh->prepare($updquery); $usth->execute; $usth->finish; - UpdateStats($env,'branch','payment',$data,'','','',$bornumber); + UpdateStats($env,$branch,'payment',$data,'','','',$bornumber); $sth->finish; $dbh->disconnect; } diff --git a/C4/Stats.pm b/C4/Stats.pm index df504e3d66..4fe513627d 100644 --- a/C4/Stats.pm +++ b/C4/Stats.pm @@ -55,7 +55,9 @@ sub UpdateStats { #module to insert stats data into stats table my ($env,$branch,$type,$amount,$other,$itemnum,$itemtype,$borrowernumber)=@_; my $dbh=C4Connect(); - my $branch=$env->{'branchcode'}; + if ($branch eq ''){ + $branch=$env->{'branchcode'}; + } my $user = $env->{'usercode'}; print $borrowernumber; my $sth=$dbh->prepare("Insert into statistics @@ -147,16 +149,24 @@ sub TotalOwing{ sub TotalPaid { my ($time)=@_; my $dbh=C4Connect; - my $query="Select * from statistics,borrowers - where statistics.borrowernumber= borrowers.borrowernumber - and (statistics.type='payment' or statistics.type='writeoff')"; + my $query="Select * from accountlines,borrowers where (accounttype = 'Pay' +or accounttype ='W') + and accountlines.borrowernumber = borrowers.borrowernumber"; if ($time eq 'today'){ - $query=$query." and datetime = now()"; + $query=$query." and date = now()"; } else { - $query.=" and datetime > '$time'"; + $query.=" and date='$time'"; } -# $query.=" order by timestamp"; - print $query; +# 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; @@ -179,7 +189,7 @@ sub getcharges{ and timestamp = '$timestamp' and accounttype <> 'Pay' and accounttype <> 'W'"; my $sth=$dbh->prepare($query); - print $query,"
"; +# print $query,"
"; $sth->execute; my $i=0; my @results; @@ -194,9 +204,10 @@ sub getcharges{ } sub Getpaidbranch{ - my($date)=@_; + my($date,$borrno)=@_; my $dbh=C4Connect; - my $query="select * from statistics where type='payment' and datetime='$date'"; + my $query="select * from statistics where type='payment' and datetime + >'$date' and borrowernumber='$borrno'"; my $sth=$dbh->prepare($query); $sth->execute; # print $query; diff --git a/pay.pl b/pay.pl index 15f6970944..d3299d24fa 100755 --- a/pay.pl +++ b/pay.pl @@ -34,6 +34,9 @@ for (my $i=0;$i<@names;$i++){ $check=1; } if ($temp eq 'yes'){ + $user=~ s/Levin/C/i; + $user=~ s/Foxton/F/i; + $user=~ s/Shannon/S/i; my $amount=$input->param($names[$i+4]); my $bornum=$input->param($names[$i+5]); my $accountno=$input->param($names[$i+6]); @@ -42,6 +45,11 @@ for (my $i=0;$i<@names;$i++){ } } my %env; + $user=~ s/Levin/C/i; + $user=~ s/Foxton/F/i; + $user=~ s/Shannon/S/i; + +$env{'branchcode'}=$user; my $total=$input->param('total'); if ($check ==0){ if ($total ne ''){ diff --git a/sec/writeoff.pl b/sec/writeoff.pl index 960849ea13..a714d853c9 100755 --- a/sec/writeoff.pl +++ b/sec/writeoff.pl @@ -45,6 +45,9 @@ print $input->redirect("/cgi-bin/koha/pay.pl?bornum=$bornum"); sub writeoff{ my ($bornum,$accountnum,$itemnum,$accounttype,$amount)=@_; my $user=$input->remote_user; + $user=~ s/Levin/C/; + $user=~ s/Foxton/F/; + $user=~ s/Shannon/S/; my $dbh=C4Connect; my $env; my $query="Update accountlines set amountoutstanding=0 where "; diff --git a/stats.pl b/stats.pl index 84c383d26a..c24cfcfe94 100755 --- a/stats.pl +++ b/stats.pl @@ -49,35 +49,29 @@ my $totalw=0; #my $totalcf=0; print mktablehdr; print mktablerow(5,'#99cc33',bold('Name'),bold('Type'),bold('Date/time'),bold('Amount'), bold('Branch'),'/images/background-mem.gif'); -for (my $i=0;$i<$count;$i++){ - 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"; - if ($payments[$i]{'accounttype'} ne 'W'){ - $payments[$i]{'amount'}*=-1; - $total+=$payments[$i]{'amount'}; - } - +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++){ - if ($charges[$i2]->{'amountoutstanding'} != $oldtime){ - print mktablerow(6,'red',$charges[$i2]->{'description'},$charges[$i2]->{'accounttype'},'', + my $temptotalw=0; + for (my $i2=0;$i2<$count;$i2++){ + $charge+=$charges[$i2]->{'amount'}; + print mktablerow(6,'red',$charges[$i2]->{'description'},$charges[$i2]->{'accounttype'},$charges[$i2]->{'timestamp'}, $charges[$i2]->{'amount'},$charges[$i2]->{'amountoutstanding'}); - if ($payments[$i]{'accounttype'} ne 'W'){ -# print $payments[$i]{'accounttype'}; + 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'){ + 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'){ @@ -86,40 +80,72 @@ for (my $i=0;$i<$count;$i++){ if ($charges[$i2]->{'accounttype'} eq 'R'){ $temptotalren+=$charges[$i2]->{'amount'}-$charges[$i2]->{'amountoutstanding'}; } - } - } - } - if ($payments[$i]{'accounttype'} eq 'W'){ - $totalw+=$payments[$i]{'amount'}; + } } - my $time2="$payments[$i]{'date'} $time"; - my $branch=Getpaidbranch($time2); - $branch=~ s/Levi/C/; + +# my $branch= + 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 ($branch eq 'C'){ - $levin{'total'}+=$payments[$i]{'amount'}; - $levin{'totalr'}+=$temptotalr; - $levin{'totalres'}+=$temptotalres; $levin{'totalf'}+=$temptotalf; + $levin{'totalres'}+=$temptotalres; $levin{'totalren'}+=$temptotalren; - } - if ($branch eq 'F'){ - $foxton{'total'}+=$payments[$i]{'amount'}; - $foxton{'totalr'}+=$temptotalr; - $foxton{'totalres'}+=$temptotalres; + $levin{'totalr'}+=$temptotalr; + } elsif ($branch eq 'F'){ $foxton{'totalf'}+=$temptotalf; + $foxton{'totalres'}+=$temptotalres; $foxton{'totalren'}+=$temptotalren; - } - if ($branch eq 'S'){ - $shannon{'total'}+=$payments[$i]{'amount'}; - $shannon{'totalr'}+=$temptotalr; - $shannon{'totalres'}+=$temptotalres; + $foxton{'totalr'}+=$temptotalr; + } elsif ($branch eq 'S'){ $shannon{'totalf'}+=$temptotalf; + $shannon{'totalres'}+=$temptotalres; $shannon{'totalren'}+=$temptotalren; + $shannon{'totalr'}+=$temptotalr; } - print mktablerow(6,'white',"$payments[$i]{'firstname'} $payments[$i]{'surname'}" - ,$payments[$i]{'accounttype'},"$payments[$i]{'date'} $time",$payments[$i]{'amount'} - ,$branch); - $oldtime=$payments[$i]{'timestamp'}; + 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'}; + if ($branch eq 'C'){ + $levin{'total'}+=$payments[$i]{'amount'}; + } + if ($branch eq 'F'){ + $foxton{'total'}+=$payments[$i]{'amount'}; + } + if ($branch eq 'S'){ + $shannon{'total'}+=$payments[$i]{'amount'}; + } + + } +# my $time2="$payments[$i]{'date'} $time"; + + + print mktablerow(6,'white',"$payments[$i]{'firstname'} $payments[$i]{'surname'}", + ,$payments[$i]{'accounttype'},"$payments[$i]{'date'} $time",$payments[$i]{'amount'} + ,$branch); + $oldtype=$payments[$i]{'accounttype'}; + $oldtime=$payments[$i]{'timestamp'}; + $bornum=$payments[$i]{'borrowernumber'}; + $i++; + + } + print mktablerow('6','white','','','','','',''); } print mktableft; print endcenter; @@ -136,13 +162,15 @@ $shannon{'returns'}=Count('return','S',$date,$date2); $levin{'renewals'}=Count('renew','C',$date,$date2); $foxton{'renewals'}=Count('renew','F',$date,$date2); $shannon{'renewals'}=Count('renew','S',$date,$date2); - -print mktablerow(9,'white',"Levin","Fines $levin{'totalf'}","Rental Charges $levin{'totalr'}", -"Reserve Charges $levin{'totalres'}","Renewal Charges $levin{'totalren'}","Total $levin{'total'}", +$levin{'unknown'}=$levin{'total'}-($levin{'totalf'}+$levin{'totalr'}+$levin{'totalres'}+$levin{'totalren'}); +$foxton{'unknown'}=$foxton{'total'}-($foxton{'totalf'}+$foxton{'totalr'}+$foxton{'totalres'}+$foxton{'totalren'}); +$foxton{'unknown'}=$foxton{'total'}-($foxton{'totalf'}+$foxton{'totalr'}+$foxton{'totalres'}+$foxton{'totalren'}); +print mktablerow(10,'white',"Levin","Fines $levin{'totalf'}","Rental Charges $levin{'totalr'}", +"Reserve Charges $levin{'totalres'}","Renewal Charges $levin{'totalren'}","Unknown $levin{'unknown'}","Total $levin{'total'}", "Issues $levin{'issues'}","Renewals $levin{'renewals'}","Returns $levin{'returns'}"); -print mktablerow(9,'white',"foxton","Fines $foxton{'totalf'}","Rental Charges $foxton{'totalr'}","Reserve Charges $foxton{'totalres'}","Renewal Charges $foxton{'totalren'}","Total $foxton{'total'}", +print mktablerow(10,'white',"foxton","Fines $foxton{'totalf'}","Rental Charges $foxton{'totalr'}","Reserve Charges $foxton{'totalres'}","Renewal Charges $foxton{'totalren'}","Unknown $foxton{'unknown'}","Total $foxton{'total'}", "Issues $foxton{'issues'}","Renewals $foxton{'renewals'}","Returns $foxton{'returns'}"); -print mktablerow(9,'white',"shannon","Fines $shannon{'totalf'}","Rental Charges $shannon{'totalr'}","Reserve Charges $shannon{'totalres'}","Renewal Charges $shannon{'totalren'}","Total $shannon{'total'}", +print mktablerow(10,'white',"shannon","Fines $shannon{'totalf'}","Rental Charges $shannon{'totalr'}","Reserve Charges $shannon{'totalres'}","Renewal Charges $shannon{'totalren'}","Unknown $shannon{'unknown'}","Total $shannon{'total'}", "Issues $shannon{'issues'}","Renewals $shannon{'renewals'}","Returns $shannon{'returns'}"); print mktableft; -- 2.39.5