[Koha-cvs] koha/reports stats.screen.pl stats.print.pl

[Chris Cormack]

CVSROOT:        /sources/koha
Module name:    koha
Changes by:     Chris Cormack <rangi>   06/06/07 01:40:03

Added files:
        reports        : stats.screen.pl stats.print.pl 

Log message:
        Scripts written by Bob and Mason to do daily til/cash reconciliation 
for librarybranches

CVSWeb URLs:
http://cvs.savannah.gnu.org/viewcvs/koha/reports/stats.screen.pl?cvsroot=koha&rev=1.1
http://cvs.savannah.gnu.org/viewcvs/koha/reports/stats.print.pl?cvsroot=koha&rev=1.1

Patches:
Index: stats.screen.pl
===================================================================
RCS file: stats.screen.pl
diff -N stats.screen.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ stats.screen.pl     7 Jun 2006 01:40:03 -0000       1.1
@@ -0,0 +1,204 @@
+#!/usr/bin/perl
+
+use strict;
+use CGI;
+use C4::Output;
+use HTML::Template;
+use C4::Auth;
+use C4::Interface::CGI::Output;
+use C4::Context;
+use Date::Manip;
+use C4::Stats;
+use Data::Dumper;
+
+&Date_Init("DateFormat=non-US"); # set non-USA date, eg:19/08/2005
+
+my $input=new CGI;
+my $time=$input->param('time');
+my $time2=$input->param('time2');
+
+
+if($input->param('submit') eq "To Excel" || $input->param('submit_x') eq "To 
Excel"){
+       print 
$input->redirect("/cgi-bin/koha/stats.print.pl?time=$time&time2=$time2");
+}
+
+my ($template, $loggedinuser, $cookie)
+    = get_template_and_user({template_name => "stats_screen.tmpl",
+                             query => $input,
+                             type => "intranet",
+                             authnotrequired => 1,
+                             flagsrequired => {borrowers => 1},
+                             debug => 1,
+                             });
+
+my $date;
+my $date2;
+if ($time eq 'yesterday'){
+        $date=ParseDate('yesterday');
+        $date2=ParseDate('today');
+}
+if ($time eq 'today'){
+        $date=ParseDate('today');
+        $date2=ParseDate('tomorrow');
+}
+if ($time eq 'daybefore'){
+        $date=ParseDate('2 days ago');
+        $date2=ParseDate('yesterday');
+}
+if ($time eq 'month') {
+        $date = ParseDate('1 month ago');
+        $date2 = ParseDate('today');
+
+}
+if ($time=~ /\//){
+        $date=ParseDate($time);
+        $date2=ParseDateDelta('+ 1 day');
+        $date2=DateCalc($date,$date2);
+}
+# if time is blank
+if ($time eq ''){
+        $date=ParseDate('today');
+        $date2=ParseDate('tomorrow');
+}
+
+# if script is called with a start and finsh date range...
+if ($time ne '' && $time2 ne ''){
+          $date=ParseDate($time);
+          $date2=ParseDate($time2);
+}
+
+
+my $date=UnixDate($date,'%Y-%m-%d');
+my $date2=UnixDate($date2,'%Y-%m-%d');
+# warn "MASON: TIME: $time, $time2";
+# warn "MASON: DATE: $date, $date2";
+
+# get a list of every payment
+my @payments=TotalPaid($date,$date2,0);
+
+
+my address@hidden;
+# print "MASON: number of payments=$count\n";
+
+my $i=0;
+my $totalcharges=0;
+my $totalcredits=0;
+my $totalpaid=0;
+my $totalwritten=0;
+my @loop1;
+my @loop2;
+
+
+# lets get a a list of all individual item charges paid for by that payment
+while ($i<$count ){
+
+       my $count;
+       my @charges;
+
+
+       if ($payments[$i]{'type'} ne 'writeoff'){
+
+#       warn Dumper $payments[$i];
+
+           @charges=getcharges($payments[$i]{'borrowernumber'}, 
$payments[$i]{'timestamp'}, $payments[$i]{'proccode'});
+           $totalcharges++;
+           address@hidden;
+
+           # getting each of the charges and putting them into a array to be 
printed out
+           #this loops per charge per person
+           for (my $i2=0;$i2<$count;$i2++){
+               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 $branch=$payments[$i]{'branch'};
+#             if ($payments[$i]{'borrowernumber'} == 18265){
+#               warn "$payments[$i]{'branch'} $branch 
$payments[$i]{'borrowernumber'}";#
+#                 }
+               # lets build up a row
+               my %rows1 = (branch => $branch,
+                            datetime => $payments[$i]->{'datetime'},
+                            surname => $payments[$i]->{'surname'},
+                            firstname => $payments[$i]->{'firstname'},
+                            description => $charges[$i2]->{'description'},
+                            accounttype => $charges[$i2]->{'accounttype'},
+                            amount => sprintf("%.2f", 
$charges[$i2]->{'amount'}), # rounding amounts to 2dp
+                            type => $payments[$i]->{'type'},
+                            value => sprintf("%.2f", 
$payments[$i]->{'value'})); # rounding amounts to 2dp
+
+               push (@loop1, \%rows1);
+              $totalpaid = $totalpaid + $payments[$i]->{'value'};
+           }
+       } else {
+         ++$totalwritten;
+       }
+
+       $i++; #increment the while loop 
+}
+
+#get credits and append to the bottom of payments
+my @credits=getcredits($date,$date2);
+
+my address@hidden;
+my $i=0;
+
+while ($i<$count ){
+
+       my %rows2 = (creditbranch        => $credits[$i]->{'branchcode'},
+                    creditdate          => $credits[$i]->{'date'},
+                    creditsurname       => $credits[$i]->{'surname'},
+                    creditfirstname     => $credits[$i]->{'firstname'},
+                    creditdescription   => $credits[$i]->{'description'},
+                    creditaccounttype   => $credits[$i]->{'accounttype'},
+                    creditamount        => sprintf("%.2f", 
$credits[$i]->{'amount'})
+                    );
+
+       push (@loop2, \%rows2);
+       $totalcredits = $totalcredits + $credits[$i]->{'amount'};
+       $i++; #increment the while loop
+}
+
+#takes off first char minus sign "-100.00"
+$totalcredits = substr($totalcredits, 1);
+
+my $totalrefunds=0;
+my @loop3;
+my @refunds=getrefunds($date,$date2);
address@hidden;
+$i=0;
+
+while ($i<$count ){
+
+       my %rows2 = (refundbranch        => $refunds[$i]->{'branchcode'},
+                    refunddate          => $refunds[$i]->{'date'},
+                    refundsurname       => $refunds[$i]->{'surname'},
+                    refundfirstname     => $refunds[$i]->{'firstname'},
+                    refunddescription   => $refunds[$i]->{'description'},
+                    refundaccounttype   => $refunds[$i]->{'accounttype'},
+                    refundamount        => sprintf("%.2f", 
$refunds[$i]->{'amount'})
+                    );
+
+       push (@loop3, \%rows2);
+       $totalrefunds = $totalrefunds + $refunds[$i]->{'amount'};
+       $i++; #increment the while loop
+}
+
+my $totalcash=$totalpaid-$totalrefunds;
+
+
+$template->param( date                => $time,
+                  date2               => $time2,
+                  loop1               => address@hidden,
+                  loop2               => address@hidden,
+                  loop3               => address@hidden,
+                  totalpaid           => $totalpaid,
+                  totalcredits        => $totalcredits,
+                  totalwritten        => $totalwritten,
+                  totalrefund         => $totalrefunds,
+                  totalcash           => $totalcash);
+
+output_html_with_http_headers $input, $cookie, $template->output;
+
+

Index: stats.print.pl
===================================================================
RCS file: stats.print.pl
diff -N stats.print.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ stats.print.pl      7 Jun 2006 01:40:03 -0000       1.1
@@ -0,0 +1,178 @@
+#!/usr/bin/perl
+
+use strict;
+use CGI;
+use C4::Output;
+use HTML::Template;
+use C4::Auth;
+use C4::Interface::CGI::Output;
+use C4::Context;
+use Date::Manip;
+use C4::Stats;
+use Text::CSV_XS;
+&Date_Init("DateFormat=non-US"); # set non-USA date, eg:19/08/2005
+
+my $csv = Text::CSV_XS->new(
+    {
+        'quote_char'  => '"',
+        'escape_char' => '"',
+        'sep_char'    => ',',
+        'binary'      => 1
+    }
+);
+
+my $input=new CGI;
+my $time=$input->param('time');
+my $time2=$input->param('time2');
+
+my @loop1;
+my @loop2;
+my $date;
+my $date2;
+if ($time eq 'yesterday'){
+        $date=ParseDate('yesterday');
+        $date2=ParseDate('today');
+}
+if ($time eq 'today'){
+        $date=ParseDate('today');
+        $date2=ParseDate('tomorrow');
+}
+if ($time eq 'daybefore'){
+        $date=ParseDate('2 days ago');
+        $date2=ParseDate('yesterday');
+}
+if ($time eq 'month') {
+        $date = ParseDate('1 month ago');
+        $date2 = ParseDate('today');
+
+}
+if ($time=~ /\//){
+        $date=ParseDate($time);
+        $date2=ParseDateDelta('+ 1 day');
+        $date2=DateCalc($date,$date2);
+}
+
+if ($time eq ''){
+        $date=ParseDate('today');
+        $date2=ParseDate('tomorrow');
+}
+
+if ($time2 ne ''){
+            $date=ParseDate($time);
+            $date2=ParseDate($time2);
+}
+
+my $date=UnixDate($date,'%Y-%m-%d');
+my $date2=UnixDate($date2,'%Y-%m-%d');
+
+#warn "MASON: DATE: $date, $date2";
+
+#get a list of every payment
+my @payments=TotalPaid($date,$date2);
+
+my address@hidden;
+# print "MASON: number of payments=$count\n";
+
+my $i=0;
+my $totalcharges=0;
+my $totalcredits=0;
+my $totalpaid=0;
+my $totalwritten=0;
+
+# lets get a a list of all individual item charges paid for by that payment
+while ($i<$count ){
+
+       my $count;
+       my @charges;
+
+       if ($payments[$i]{'type'} ne 'writeoff'){         # lets ignore 
writeoff payments!.
+           @charges=getcharges($payments[$i]{'borrowernumber'}, 
$payments[$i]{'timestamp'}, $payments[$i]{'proccode'});
+           $totalcharges++;
+           address@hidden;
+
+           # getting each of the charges and putting them into a array to be 
printed out
+           #this loops per charge per person
+           for (my $i2=0;$i2<$count;$i2++){
+
+               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 $branch=$payments[$i]{'branch'};
+
+               my @rows1 = ($branch,          # lets build up a row
+                            $payments[$i]->{'datetime'},
+                            $payments[$i]->{'surname'},
+                            $payments[$i]->{'firstname'},
+                            $charges[$i2]->{'description'},
+                            $charges[$i2]->{'accounttype'},
+   # rounding amounts to 2dp and adding dollar sign to make excel read it as 
currency format
+                            "\$".sprintf("%.2f", $charges[$i2]->{'amount'}), 
+                            $payments[$i]->{'type'},
+                            "\$".$payments[$i]->{'value'});
+
+               push (@loop1, address@hidden);
+              $totalpaid = $totalpaid + $payments[$i]->{'value'};
+           }
+       } else {
+         ++$totalwritten;
+       }
+
+       $i++; #increment the while loop
+}
+
+#get credits and append to the bottom of payments
+my @credits=getcredits($date,$date2);
+
+my address@hidden;
+my $i=0;
+
+while ($i<$count ){
+
+       my @rows2 = ($credits[$i]->{'branchcode'},
+                    $credits[$i]->{'date'},
+                    $credits[$i]->{'surname'},
+                    $credits[$i]->{'firstname'},
+                    $credits[$i]->{'description'},
+                    $credits[$i]->{'accounttype'},
+                    "\$".$credits[$i]->{'amount'});
+
+       push (@loop2, address@hidden);
+       $totalcredits = $totalcredits + $credits[$i]->{'amount'};
+       $i++;
+}
+
+#takes off first char minus sign "-100.00"
+$totalcredits = substr($totalcredits, 1);
+
+print $input->header(
+    -type       => 'application/vnd.ms-excel',
+    -attachment => "stats.csv",
+);
+print "Branch, Datetime, Surname, Firstnames, Description, Type, Invoice 
amount, Payment type, Payment Amount\n";
+
+
+for my $row ( @loop1 ) {
+
+    $csv->combine(@$row);
+    my $string = $csv->string;
+    print $string, "\n";
+}
+
+print ",,,,,,,\n";
+
+for my $row ( @loop2 ) {
+
+    $csv->combine(@$row);
+    my $string = $csv->string;
+    print $string, "\n";
+}
+
+print ",,,,,,,\n";
+print ",,,,,,,\n";
+print ",,Total Amount Paid, $totalpaid\n";
+print ",,Total Number Written, $totalwritten\n";
+print ",,Total Amount Credits, $totalcredits\n";
+