reports/stats.print.pl

   1 #!/usr/bin/perl
   2
   3 use strict;
   4 use CGI;
   5 use C4::Output;
   6
   7 use C4::Auth;
   8 use C4::Context;
   9 use Date::Manip;
  10 use C4::Stats;
  11 use Text::CSV_XS;
  12 &Date_Init("DateFormat=non-US"); # set non-USA date, eg:19/08/2005
  13
  14 my $csv = Text::CSV_XS->new(
  15     {
  16         'quote_char'  => '"',
  17         'escape_char' => '"',
  18         'sep_char'    => ',',
  19         'binary'      => 1
  20     }
  21 );
  22
  23 my $input=new CGI;
  24 my $time=$input->param('time');
  25 my $time2=$input->param('time2');
  26
  27 my @loop1;
  28 my @loop2;
  29 my $date;
  30 my $date2;
  31 if ($time eq 'yesterday'){
  32         $date=ParseDate('yesterday');
  33         $date2=ParseDate('today');
  34 }
  35 if ($time eq 'today'){
  36         $date=ParseDate('today');
  37         $date2=ParseDate('tomorrow');
  38 }
  39 if ($time eq 'daybefore'){
  40         $date=ParseDate('2 days ago');
  41         $date2=ParseDate('yesterday');
  42 }
  43 if ($time eq 'month') {
  44         $date = ParseDate('1 month ago');
  45         $date2 = ParseDate('today');
  46
  47 }
  48 if ($time=~ /\//){
  49         $date=ParseDate($time);
  50         $date2=ParseDateDelta('+ 1 day');
  51         $date2=DateCalc($date,$date2);
  52 }
  53
  54 if ($time eq ''){
  55         $date=ParseDate('today');
  56         $date2=ParseDate('tomorrow');
  57 }
  58
  59 if ($time2 ne ''){
  60             $date=ParseDate($time);
  61             $date2=ParseDate($time2);
  62 }
  63
  64 my $date=UnixDate($date,'%Y-%m-%d');
  65 my $date2=UnixDate($date2,'%Y-%m-%d');
  66
  67 #warn "MASON: DATE: $date, $date2";
  68
  69 #get a list of every payment
  70 my @payments=TotalPaid($date,$date2);
  71
  72 my $count=@payments;
  73 # print "MASON: number of payments=$count\n";
  74
  75 my $i=0;
  76 my $totalcharges=0;
  77 my $totalcredits=0;
  78 my $totalpaid=0;
  79 my $totalwritten=0;
  80
  81 # lets get a a list of all individual item charges paid for by that payment
  82 while ($i<$count ){
  83
  84        my $count;
  85        my @charges;
  86
  87        if ($payments[$i]{'type'} ne 'writeoff'){         # lets ignore writeoff payments!.
  88            @charges=getcharges($payments[$i]{'borrowernumber'}, $payments[$i]{'timestamp'}, $payments[$i]{'proccode'});
  89            $totalcharges++;
  90            $count=@charges;
  91
  92            # getting each of the charges and putting them into a array to be printed out
  93            #this loops per charge per person
  94            for (my $i2=0;$i2<$count;$i2++){
  95
  96                my $hour=substr($payments[$i]{'timestamp'},8,2);
  97                my $min=substr($payments[$i]{'timestamp'},10,2);
  98                my $sec=substr($payments[$i]{'timestamp'},12,2);
  99                my $time="$hour:$min:$sec";
 100                my $time2="$payments[$i]{'date'}";
 101 #               my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'});
 102                my $branch=$payments[$i]{'branch'};
 103
 104                my @rows1 = ($branch,          # lets build up a row
 105                             $payments[$i]->{'datetime'},
 106                             $payments[$i]->{'surname'},
 107                             $payments[$i]->{'firstname'},
 108                             $charges[$i2]->{'description'},
 109                             $charges[$i2]->{'accounttype'},
 110    # rounding amounts to 2dp and adding dollar sign to make excel read it as currency format
 111                             "\$".sprintf("%.2f", $charges[$i2]->{'amount'}),
 112                             $payments[$i]->{'type'},
 113                             "\$".$payments[$i]->{'value'});
 114
 115                push (@loop1, \@rows1);
 116                $totalpaid = $totalpaid + $payments[$i]->{'value'};
 117            }
 118        } else {
 119          ++$totalwritten;
 120        }
 121
 122        $i++; #increment the while loop
 123 }
 124
 125 #get credits and append to the bottom of payments
 126 my @credits=getcredits($date,$date2);
 127
 128 my $count=@credits;
 129 my $i=0;
 130
 131 while ($i<$count ){
 132
 133        my @rows2 = ($credits[$i]->{'branchcode'},
 134                     $credits[$i]->{'date'},
 135                     $credits[$i]->{'surname'},
 136                     $credits[$i]->{'firstname'},
 137                     $credits[$i]->{'description'},
 138                     $credits[$i]->{'accounttype'},
 139                     "\$".$credits[$i]->{'amount'});
 140
 141        push (@loop2, \@rows2);
 142        $totalcredits = $totalcredits + $credits[$i]->{'amount'};
 143        $i++;
 144 }
 145
 146 #takes off first char minus sign "-100.00"
 147 $totalcredits = substr($totalcredits, 1);
 148
 149 print $input->header(
 150     -type       => 'application/vnd.ms-excel',
 151     -attachment => "stats.csv",
 152 );
 153 print "Branch, Datetime, Surname, Firstnames, Description, Type, Invoice amount, Payment type, Payment Amount\n";
 154
 155
 156 for my $row ( @loop1 ) {
 157
 158     $csv->combine(@$row);
 159     my $string = $csv->string;
 160     print $string, "\n";
 161 }
 162
 163 print ",,,,,,,\n";
 164
 165 for my $row ( @loop2 ) {
 166
 167     $csv->combine(@$row);
 168     my $string = $csv->string;
 169     print $string, "\n";
 170 }
 171
 172 print ",,,,,,,\n";
 173 print ",,,,,,,\n";
 174 print ",,Total Amount Paid, $totalpaid\n";
 175 print ",,Total Number Written, $totalwritten\n";
 176 print ",,Total Amount Credits, $totalcredits\n";
 177