Scripts written by Bob and Mason to do daily til/cash reconciliation for librarybranches
[koha.git] / reports / stats.screen.pl
1 #!/usr/bin/perl
2
3 use strict;
4 use CGI;
5 use C4::Output;
6 use HTML::Template;
7 use C4::Auth;
8 use C4::Interface::CGI::Output;
9 use C4::Context;
10 use Date::Manip;
11 use C4::Stats;
12 use Data::Dumper;
13
14 &Date_Init("DateFormat=non-US"); # set non-USA date, eg:19/08/2005
15
16 my $input=new CGI;
17 my $time=$input->param('time');
18 my $time2=$input->param('time2');
19
20
21 if($input->param('submit') eq "To Excel" || $input->param('submit_x') eq "To Excel"){
22        print $input->redirect("/cgi-bin/koha/stats.print.pl?time=$time&time2=$time2");
23 }
24
25 my ($template, $loggedinuser, $cookie)
26     = get_template_and_user({template_name => "stats_screen.tmpl",
27                              query => $input,
28                              type => "intranet",
29                              authnotrequired => 1,
30                              flagsrequired => {borrowers => 1},
31                              debug => 1,
32                              });
33
34 my $date;
35 my $date2;
36 if ($time eq 'yesterday'){
37         $date=ParseDate('yesterday');
38         $date2=ParseDate('today');
39 }
40 if ($time eq 'today'){
41         $date=ParseDate('today');
42         $date2=ParseDate('tomorrow');
43 }
44 if ($time eq 'daybefore'){
45         $date=ParseDate('2 days ago');
46         $date2=ParseDate('yesterday');
47 }
48 if ($time eq 'month') {
49         $date = ParseDate('1 month ago');
50         $date2 = ParseDate('today');
51
52 }
53 if ($time=~ /\//){
54         $date=ParseDate($time);
55         $date2=ParseDateDelta('+ 1 day');
56         $date2=DateCalc($date,$date2);
57 }
58 # if time is blank
59 if ($time eq ''){
60         $date=ParseDate('today');
61         $date2=ParseDate('tomorrow');
62 }
63
64 # if script is called with a start and finsh date range...
65 if ($time ne '' && $time2 ne ''){
66           $date=ParseDate($time);
67           $date2=ParseDate($time2);
68 }
69
70
71 my $date=UnixDate($date,'%Y-%m-%d');
72 my $date2=UnixDate($date2,'%Y-%m-%d');
73 # warn "MASON: TIME: $time, $time2";
74 # warn "MASON: DATE: $date, $date2";
75
76 # get a list of every payment
77 my @payments=TotalPaid($date,$date2,0);
78
79
80 my $count=@payments;
81 # print "MASON: number of payments=$count\n";
82
83 my $i=0;
84 my $totalcharges=0;
85 my $totalcredits=0;
86 my $totalpaid=0;
87 my $totalwritten=0;
88 my @loop1;
89 my @loop2;
90
91
92 # lets get a a list of all individual item charges paid for by that payment
93 while ($i<$count ){
94
95        my $count;
96        my @charges;
97
98
99        if ($payments[$i]{'type'} ne 'writeoff'){
100
101 #       warn Dumper $payments[$i];
102
103            @charges=getcharges($payments[$i]{'borrowernumber'}, $payments[$i]{'timestamp'}, $payments[$i]{'proccode'});
104            $totalcharges++;
105            $count=@charges;
106
107            # getting each of the charges and putting them into a array to be printed out
108            #this loops per charge per person
109            for (my $i2=0;$i2<$count;$i2++){
110                my $hour=substr($payments[$i]{'timestamp'},8,2);
111                my $min=substr($payments[$i]{'timestamp'},10,2);
112                my $sec=substr($payments[$i]{'timestamp'},12,2);
113                my $time="$hour:$min:$sec";
114                my $time2="$payments[$i]{'date'}";
115 #               my $branch=Getpaidbranch($time2,$payments[$i]{'borrowernumber'});
116                my $branch=$payments[$i]{'branch'};
117 #              if ($payments[$i]{'borrowernumber'} == 18265){
118 #               warn "$payments[$i]{'branch'} $branch $payments[$i]{'borrowernumber'}";#
119 #                  }
120                # lets build up a row
121                my %rows1 = (branch => $branch,
122                             datetime => $payments[$i]->{'datetime'},
123                             surname => $payments[$i]->{'surname'},
124                             firstname => $payments[$i]->{'firstname'},
125                             description => $charges[$i2]->{'description'},
126                             accounttype => $charges[$i2]->{'accounttype'},
127                             amount => sprintf("%.2f", $charges[$i2]->{'amount'}), # rounding amounts to 2dp
128                             type => $payments[$i]->{'type'},
129                             value => sprintf("%.2f", $payments[$i]->{'value'})); # rounding amounts to 2dp
130
131                push (@loop1, \%rows1);
132                $totalpaid = $totalpaid + $payments[$i]->{'value'};
133            }
134        } else {
135          ++$totalwritten;
136        }
137
138        $i++; #increment the while loop 
139 }
140
141 #get credits and append to the bottom of payments
142 my @credits=getcredits($date,$date2);
143
144 my $count=@credits;
145 my $i=0;
146
147 while ($i<$count ){
148
149        my %rows2 = (creditbranch        => $credits[$i]->{'branchcode'},
150                     creditdate          => $credits[$i]->{'date'},
151                     creditsurname       => $credits[$i]->{'surname'},
152                     creditfirstname     => $credits[$i]->{'firstname'},
153                     creditdescription   => $credits[$i]->{'description'},
154                     creditaccounttype   => $credits[$i]->{'accounttype'},
155                     creditamount        => sprintf("%.2f", $credits[$i]->{'amount'})
156                     );
157
158        push (@loop2, \%rows2);
159        $totalcredits = $totalcredits + $credits[$i]->{'amount'};
160        $i++; #increment the while loop
161 }
162
163 #takes off first char minus sign "-100.00"
164 $totalcredits = substr($totalcredits, 1);
165
166 my $totalrefunds=0;
167 my @loop3;
168 my @refunds=getrefunds($date,$date2);
169 $count=@refunds;
170 $i=0;
171
172 while ($i<$count ){
173
174        my %rows2 = (refundbranch        => $refunds[$i]->{'branchcode'},
175                     refunddate          => $refunds[$i]->{'date'},
176                     refundsurname       => $refunds[$i]->{'surname'},
177                     refundfirstname     => $refunds[$i]->{'firstname'},
178                     refunddescription   => $refunds[$i]->{'description'},
179                     refundaccounttype   => $refunds[$i]->{'accounttype'},
180                     refundamount        => sprintf("%.2f", $refunds[$i]->{'amount'})
181                     );
182
183        push (@loop3, \%rows2);
184        $totalrefunds = $totalrefunds + $refunds[$i]->{'amount'};
185        $i++; #increment the while loop
186 }
187
188 my $totalcash=$totalpaid-$totalrefunds;
189
190
191 $template->param( date                => $time,
192                   date2               => $time2,
193                   loop1               => \@loop1,
194                   loop2               => \@loop2,
195                   loop3               => \@loop3,
196                   totalpaid           => $totalpaid,
197                   totalcredits        => $totalcredits,
198                   totalwritten        => $totalwritten,
199                   totalrefund         => $totalrefunds,
200                   totalcash           => $totalcash);
201
202 output_html_with_http_headers $input, $cookie, $template->output;
203
204