View | Details | Raw Unified | Return to bug 18643
Collapse All | Expand All

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/reports-home.tt (-1 lines)
Lines 68-74 Link Here
68
        <li><a href="/cgi-bin/koha/reports/issues_avg_stats.pl">Average loan time</a></li>
68
        <li><a href="/cgi-bin/koha/reports/issues_avg_stats.pl">Average loan time</a></li>
69
                <li><a href="http://schema.koha-community.org/" target="blank">Koha database schema</a></li>
69
                <li><a href="http://schema.koha-community.org/" target="blank">Koha database schema</a></li>
70
                <li><a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library" target="blank">Koha reports library</a></li>
70
                <li><a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library" target="blank">Koha reports library</a></li>
71
                <!--<li><a href="/cgi-bin/koha/reports/stats.screen.pl">Till reconciliation</a></li> -->
72
</ul></div>
71
</ul></div>
73
</div>
72
</div>
74
73
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/stats_screen.tt (-131 lines)
Lines 1-131 Link Here
1
[% INCLUDE 'doc-head-open.inc' %] 
2
<title>Koha &rsaquo; Reports &rsaquo; Till reconciliation</title>
3
[% INCLUDE 'doc-head-close.inc' %]
4
[% INCLUDE 'calendar.inc' %]
5
</head>
6
<body id="rep_stats_screen" class="rep">
7
[% INCLUDE 'header.inc' %]
8
[% INCLUDE 'circ-search.inc' %]
9
<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/reports/reports-home.pl">Reports</a> &rsaquo; Till reconciliation
10
</div>
11
12
<div id="doc3" class="yui-t2">
13
   
14
   <div id="bd">
15
	<div id="yui-main">
16
	<div class="yui-b">
17
18
<h1>Till reconciliation</h1>
19
20
<fieldset><legend>Search between two dates</legend>
21
<form action="stats.screen.pl" method="post">
22
  <label for="from">Start Date: </label>
23
  <input type="text" name="time" size="10" value="[% IF ( date ) %][% date %][% ELSE %]today[% END %]" id="from" class="datepickerfrom" />
24
  <label for="to">End Date: </label>
25
  <input type="text" name="time2" size="10" value="[% IF ( date2 ) %][% date2 %][% ELSE %]tomorrow[% END %]" class="datepickerto" id="to" />
26
  <input type="submit" value="To screen" name="submit" class="submit" />
27
<!--  <input type="submit" value="To Excel" name="submit" class="button"> --></fieldset>
28
</form>
29
30
<h2>Payments</h2>
31
32
        <table>
33
                <tr>
34
                        <th>Library</th>
35
                        <th>Date/time</th>
36
                        <th>Surname</th>
37
                        <th>First name</th>
38
                        <th>Description</th>
39
                        <th>Charge type</th>
40
                        <th>Invoice amount</th>
41
                        <th>Payment type</th>
42
                        <th>Payment amount</th>
43
                </tr>
44
45
                [% FOREACH loop IN loop1 %]
46
                <tr>
47
                     <td>[% loop.branch %]</td>
48
                        <td>[% loop.datetime %]</td>
49
                        <td>[% loop.surname %]</td>
50
                        <td>[% loop.firstname %]</td>
51
                        <td>[% loop.description %]</td>
52
                        <td>[% loop.accounttype %]</td>
53
                        <td>[% loop.amount %]</td>
54
                        <td>[% loop.type %]</td>
55
                        <td>[% loop.value %]</td>
56
                </tr>
57
                [% END %]
58
        </table>
59
60
<p>
61
        <b>Total amount paid: [% totalpaid %]</b>
62
</p>
63
64
65
<h2>Credits</h2>
66
67
        <table>
68
                <tr>
69
                        <th>Library</th>
70
                        <th>Date/time</th>
71
                        <th>Surname</th>
72
                        <th>First name</th>
73
                        <th>Description</th>
74
                        <th>Charge type</th>
75
                        <th>Invoice amount</th>
76
                </tr>
77
78
                [% FOREACH loop IN loop2 %]
79
                <tr>
80
                     <td>[% loop.creditbranch %]</td>
81
                        <td>[% loop.creditdate %]</td>
82
                        <td>[% loop.creditsurname %]</td>
83
                        <td>[% loop.creditfirstname %]</td>
84
                        <td>[% loop.creditdescription %]</td>
85
                        <td>[% loop.creditaccounttype %]</td>
86
                        <td>[% loop.creditamount %]</td>
87
                </tr>
88
                [% END %]
89
        </table>
90
<p>
91
       <ul><li> <b>Total amount credits: [% totalcredits %]</b></li>
92
        <li><b>Total number written off: [% totalwritten %] charges</b></li></ul>
93
</p>
94
95
96
<h2>Refunds</h2>
97
98
        <table>
99
                <tr>
100
                        <th>Library</th>
101
                        <th>Date/time</th>
102
                        <th>Surname</th>
103
                        <th>First name</th>
104
                        <th>Description</th>
105
                        <th>Charge type</th>
106
                        <th>Invoice amount</th>
107
                </tr>
108
109
                [% FOREACH loop IN loop3 %]
110
                <tr>
111
                     <td>[% loop.refundbranch %]</td>
112
                        <td>[% loop.refunddate %]</td>
113
                        <td>[% loop.refundsurname %]</td>
114
                        <td>[% loop.refundfirstname %]</td>
115
                        <td>[% loop.refunddescription %]</td>
116
                        <td>[% loop.refundaccounttype %]</td>
117
                        <td>[% loop.refundamount %]</td>
118
                </tr>
119
                [% END %]
120
        </table>
121
<p>
122
        <ul><li><b>Total amount refunds: [% totalrefund %]</b></li>
123
        <li><b>Total amount of cash collected: [% totalcash %] </b></li></ul>
124
</p>
125
</div>
126
</div>
127
<div class="yui-b">
128
[% INCLUDE 'reports-menu.inc' %]
129
</div>
130
</div>
131
[% INCLUDE 'intranet-bottom.inc' %]
(-)a/reports/stats.screen.pl (-266 lines)
Lines 1-265 Link Here
1
#!/usr/bin/perl
2
3
# Copyright Katipo Communications 2006
4
#
5
# This file is part of Koha.
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
21
use strict;
22
#use warnings; FIXME - Bug 2505
23
use CGI qw ( -utf8 );
24
use C4::Output;
25
use C4::Auth;
26
use C4::Context;
27
use C4::Stats;
28
use C4::Accounts;
29
use C4::Debug;
30
use Date::Manip;
31
32
my $input = new CGI;
33
my $time  = $input->param('time');
34
my $time2 = $input->param('time2');
35
my $op    = $input->param('submit');
36
37
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
38
    {
39
        template_name   => "reports/stats_screen.tt",
40
        query           => $input,
41
        type            => "intranet",
42
        flagsrequired   => { reports => '*' },
43
    }
44
);
45
46
( $time  = "today" )    if !$time;
47
( $time2 = "tomorrow" ) if !$time2;
48
49
my $date  = ParseDate($time);
50
my $date2 = ParseDate($time2);
51
$date  = UnixDate( $date,  '%Y-%m-%d' );
52
$date2 = UnixDate( $date2, '%Y-%m-%d' );
53
$debug and warn "MASON: TIME: $time, $time2";
54
$debug and warn "MASON: DATE: $date, $date2";
55
56
# get a list of every payment
57
my @payments = TotalPaid( $date, $date2 );
58
59
my $count = @payments;
60
61
$debug and warn "MASON: number of payments=$count\n";
62
63
my $i            = 0;
64
my $totalcharges = 0;
65
my $totalcredits = 0;
66
my $totalpaid    = 0;
67
my $totalwritten = 0;
68
my @loop1;
69
my @loop2;
70
71
# lets get a a list of all individual item charges paid for by that payment
72
73
foreach my $payment (@payments) {
74
75
    my @charges;
76
    if ( $payment->{'type'} ne 'writeoff' ) {
77
78
        @charges = getcharges(
79
            $payment->{'borrowernumber'},
80
            $payment->{'timestamp'},
81
            $payment->{'proccode'}
82
        );
83
        $totalcharges++;
84
        my $count = @charges;
85
86
   # getting each of the charges and putting them into a array to be printed out
87
   #this loops per charge per person
88
        for ( my $i2 = 0 ; $i2 < $count ; $i2++ ) {
89
            my $hour = substr( $payment->{'timestamp'}, 8,  2 );
90
            my $min  = substr( $payment->{'timestamp'}, 10, 2 );
91
            my $sec  = substr( $payment->{'timestamp'}, 12, 2 );
92
            my $time = "$hour:$min:$sec";
93
            my $time2 = "$payment->{'date'}";
94
95
  #               my $branch=Getpaidbranch($time2,$payment->{'borrowernumber'});
96
            my $branch = $payment->{'branch'};
97
98
            # lets build up a row
99
            my %rows1 = (
100
                branch      => $branch,
101
                datetime    => $payment->{'datetime'},
102
                surname     => $payment->{'surname'},
103
                firstname   => $payment->{'firstname'},
104
                description => $charges[$i2]->{'description'},
105
                accounttype => $charges[$i2]->{'accounttype'},
106
                amount      => sprintf( "%.2f", $charges[$i2]->{'amount'} )
107
                ,    # rounding amounts to 2dp
108
                type  => $payment->{'type'},
109
                value => sprintf( "%.2f", $payment->{'value'} )
110
            );       # rounding amounts to 2dp
111
112
            push( @loop1, \%rows1 );
113
114
        }
115
            $totalpaid = $totalpaid + $payment->{'value'};
116
			$debug and warn "totalpaid = $totalpaid";		
117
    }
118
    else {
119
        ++$totalwritten;
120
    }
121
122
}
123
124
#get credits and append to the bottom of payments
125
my @credits = getcredits( $date, $date2 );
126
127
my $count = @credits;
128
my $i     = 0;
129
130
while ( $i < $count ) {
131
132
    my %rows2 = (
133
        creditbranch      => $credits[$i]->{'branchcode'},
134
        creditdate        => $credits[$i]->{'date'},
135
        creditsurname     => $credits[$i]->{'surname'},
136
        creditfirstname   => $credits[$i]->{'firstname'},
137
        creditdescription => $credits[$i]->{'description'},
138
        creditaccounttype => $credits[$i]->{'accounttype'},
139
        creditamount      => sprintf( "%.2f", $credits[$i]->{'amount'} )
140
    );
141
142
    push( @loop2, \%rows2 );
143
    $totalcredits = $totalcredits + $credits[$i]->{'amount'};
144
    $i++;    #increment the while loop
145
}
146
147
#takes off first char minus sign "-100.00"
148
$totalcredits = substr( $totalcredits, 1 );
149
150
my $totalrefunds = 0;
151
my @loop3;
152
my @refunds = getrefunds( $date, $date2 );
153
$count = @refunds;
154
$i     = 0;
155
156
while ( $i < $count ) {
157
158
    my %rows3 = (
159
        refundbranch      => $refunds[$i]->{'branchcode'},
160
        refunddate        => $refunds[$i]->{'datetime'},
161
        refundsurname     => $refunds[$i]->{'surname'},
162
        refundfirstname   => $refunds[$i]->{'firstname'},
163
        refunddescription => $refunds[$i]->{'description'},
164
        refundaccounttype => $refunds[$i]->{'accounttype'},
165
        refundamount      => sprintf( "%.2f", $refunds[$i]->{'amount'} )
166
    );
167
168
    push( @loop3, \%rows3 );
169
    $totalrefunds = $totalrefunds + $refunds[$i]->{'amount'};
170
    $i++;    #increment the while loop
171
}
172
173
my $totalcash = $totalpaid - $totalrefunds;
174
175
if ( $op eq 'To Excel' ) {
176
177
    my $csv = Text::CSV_XS->new(
178
        {
179
            'quote_char'  => '"',
180
            'escape_char' => '"',
181
            'sep_char'    => ',',
182
            'binary'      => 1
183
        }
184
    );
185
186
    print $input->header(
187
        -type       => 'application/vnd.ms-excel',
188
        -attachment => "stats.csv",
189
    );
190
    print
191
"Branch, Datetime, Surname, Firstnames, Description, Type, Invoice amount, Payment type, Payment Amount\n";
192
193
    $DB::single = 1;
194
195
    for my $row (@loop1) {
196
        my @array = (
197
            $row->{'branch'},      $row->{'datetime'},
198
            $row->{'surname'},     $row->{'firstname'},
199
            $row->{'description'}, $row->{'accounttype'},
200
            $row->{'amount'},      $row->{'type'},
201
            $row->{'value'}
202
        );
203
204
        $csv->combine(@array);
205
        my $string = $csv->string(@array);
206
        print $string, "\n";
207
    }
208
    print ",,,,,,,\n";
209
    print
210
"Branch, Date/time, Surname, Firstname, Description, Charge Type, Invoice Amount\n";
211
212
    for my $row (@loop2) {
213
214
        my @array = (
215
            $row->{'creditbranch'},      $row->{'creditdate'},
216
            $row->{'creditsurname'},     $row->{'creditfirstname'},
217
            $row->{'creditdescription'}, $row->{'creditaccounttype'},
218
            $row->{'creditamount'}
219
        );
220
221
        $csv->combine(@array);
222
        my $string = $csv->string(@array);
223
        print $string, "\n";
224
    }
225
    print ",,,,,,,\n";
226
    print
227
"Branch, Date/time, Surname, Firstname, Description, Charge Type, Invoice Amount\n";
228
229
    for my $row (@loop3) {
230
        my @array = (
231
            $row->{'refundbranch'},      $row->{'refunddate'},
232
            $row->{'refundsurname'},     $row->{'refundfirstname'},
233
            $row->{'refunddescription'}, $row->{'refundaccounttype'},
234
            $row->{'refundamount'}
235
        );
236
237
        $csv->combine(@array);
238
        my $string = $csv->string(@array);
239
        print $string, "\n";
240
241
    }
242
243
    print ",,,,,,,\n";
244
    print ",,,,,,,\n";
245
    print ",,Total Amount Paid, $totalpaid\n";
246
    print ",,Total Number Written, $totalwritten\n";
247
    print ",,Total Amount Credits, $totalcredits\n";
248
    print ",,Total Amount Refunds, $totalrefunds\n";
249
}
250
else {
251
    $template->param(
252
        date         => $time,
253
        date2        => $time2,
254
        loop1        => \@loop1,
255
        loop2        => \@loop2,
256
        loop3        => \@loop3,
257
        totalpaid    => $totalpaid,
258
        totalcredits => $totalcredits,
259
        totalwritten => $totalwritten,
260
        totalrefund  => $totalrefunds,
261
        totalcash    => $totalcash,
262
    );
263
    output_html_with_http_headers $input, $cookie, $template->output;
264
}
265
266
- 

Return to bug 18643