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

(-)a/circ/billing.pl (-172 lines)
Lines 1-172 Link Here
1
#!/usr/bin/perl
2
3
# Copyright 2000-2002 Katipo Communications
4
#
5
# This file is part of Koha.
6
#
7
# Koha is free software; you can redistribute it and/or modify it under the
8
# terms of the GNU General Public License as published by the Free Software
9
# Foundation; either version 2 of the License, or (at your option) any later
10
# version.
11
#
12
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14
# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
15
#
16
# You should have received a copy of the GNU General Public License along
17
# with Koha; if not, write to the Free Software Foundation, Inc.,
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
20
use strict;
21
use warnings;
22
use C4::Context;
23
use C4::Output;
24
use CGI;
25
use C4::Auth;
26
use C4::Dates qw/format_date format_date_in_iso/;
27
use C4::Debug;
28
use Date::Calc qw/Today Add_Delta_YM/;
29
30
my $input = new CGI;
31
my $order     = $input->param('order') || '';
32
my $startdate = $input->param('from')  || '';
33
my $enddate   = $input->param('to')    || '';
34
my $max_bill  = $input->param('ratio') || C4::Context->preference('noissuescharge') || 20.00;
35
36
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
37
    {
38
        template_name   => "circ/billing.tmpl",
39
        query           => $input,
40
        type            => "intranet",
41
        authnotrequired => 0,
42
        flagsrequired   => { circulate => "circulate_remaining_permissions" },
43
        debug           => 1,
44
    }
45
);
46
47
my ( $year, $month, $day ) = Today();
48
my $todaysdate   = sprintf("%-04.4d-%-02.2d-%02.2d", $year, $month, $day);
49
# Find yesterday for the default shelf pull start and end dates
50
#    A default of the prior years's holds is a reasonable way to pull holds 
51
my $datelastyear = sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, -1, 0));
52
53
$startdate =~ s/^\s+//;
54
$startdate =~ s/\s+$//;
55
$enddate   =~ s/^\s+//;
56
$enddate   =~ s/\s+$//;
57
# Predefine the start and end dates if they are not already defined
58
$startdate = format_date($datelastyear) unless $startdate;
59
$enddate   = format_date($todaysdate  ) unless   $enddate;
60
61
my $dbh = C4::Context->dbh;
62
my ($sqlorderby, $sqldatewhere, $presqldatewhere) = ("","","");
63
$debug and warn "start: " . format_date_in_iso($startdate) . "\nend: " . format_date_in_iso($enddate);
64
my @query_params = ();
65
# the dates below is to check for compliance of the current date range
66
if ($enddate) {
67
    $sqldatewhere .= " AND date <= ?";
68
    push @query_params, format_date_in_iso($enddate);
69
}
70
push @query_params, $max_bill;
71
# the date below is to check for compliance of all fees prior
72
if ($startdate) {
73
    $presqldatewhere .= " AND date < ?";
74
    push @query_params, format_date_in_iso($startdate);
75
}
76
push @query_params, $max_bill;
77
78
if ($order eq "patron") {
79
	$sqlorderby = " ORDER BY surname, firstname ";
80
} elsif ($order eq "fee") {
81
    $sqlorderby = " ORDER BY l_amountoutstanding DESC ";
82
} elsif ($order eq "desc") {
83
    $sqlorderby = " ORDER BY l_description ";
84
} elsif ($order eq "type") {
85
    $sqlorderby = " ORDER BY l_accounttype ";
86
} elsif ($order eq "date") {
87
    $sqlorderby = " ORDER BY l_date DESC ";
88
} elsif ($order eq "total") {
89
    $sqlorderby = " ORDER BY sum_amount DESC ";
90
} else {
91
	$sqlorderby = " ORDER BY surname, firstname ";
92
}
93
my $strsth =
94
	"SELECT 
95
		GROUP_CONCAT(accountlines.accounttype   ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_accounttype,
96
		GROUP_CONCAT(description                ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_description,
97
		GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_amountoutstanding, 
98
		GROUP_CONCAT(accountlines.date          ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_date,
99
		GROUP_CONCAT(accountlines.itemnumber    ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_itemnumber,
100
		count(*)                        as cnt,
101
		max(accountlines.date)          as maxdate,
102
		round(sum(amountoutstanding),2) as sum_amount, 
103
		borrowers.borrowernumber        as borrowernumber,
104
		borrowers.surname               as surname,
105
		borrowers.firstname             as firstname,
106
		borrowers.email                 as email,
107
		borrowers.phone                 as phone,
108
		accountlines.itemnumber,
109
		description, 
110
		accountlines.date               as accountdate
111
		FROM 
112
			borrowers, accountlines
113
		WHERE 
114
			accountlines.borrowernumber = borrowers.borrowernumber
115
		AND accountlines.amountoutstanding <> 0 
116
		AND accountlines.borrowernumber 
117
			IN (SELECT borrowernumber FROM accountlines 
118
				where borrowernumber >= 0
119
				$sqldatewhere 
120
				GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? ) 
121
		AND accountlines.borrowernumber 
122
			NOT IN (SELECT borrowernumber FROM accountlines 
123
				where borrowernumber >= 0
124
				$presqldatewhere 
125
				GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? ) 
126
";
127
128
if (C4::Context->preference('IndependantBranches')){
129
	$strsth .= " AND borrowers.branchcode=? ";
130
    push @query_params, C4::Context->userenv->{'branch'};
131
}
132
$strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? " . $sqlorderby;
133
push @query_params, $max_bill;
134
135
my $sth = $dbh->prepare($strsth);
136
$sth->execute(@query_params);
137
138
my @billingdata;
139
while ( my $data = $sth->fetchrow_hashref ) {   
140
    push @billingdata, {
141
        l_accountype        => $data->{l_accounttype},
142
        l_description       => $data->{l_description},
143
        l_amountoutstanding => $data->{l_amountoutstanding},
144
        l_date              => $data->{l_date},
145
        l_itemnumber        => $data->{l_itemnumber},
146
        l_accounttype       => $data->{l_accounttype},
147
        l_title             => $data->{l_title},
148
        cnt                 => $data->{cnt},
149
        maxdate             => $data->{maxdate},
150
        sum_amount          => $data->{sum_amount},
151
        borrowernumber      => $data->{borrowernumber},
152
        surname             => $data->{surname},
153
        firstname           => $data->{firstname},
154
        phone               => $data->{phone},
155
        email               => $data->{email},
156
        patronname          => $data->{surname} . ", " . $data->{firstname},
157
        description         => $data->{description},
158
        amountoutstanding   => $data->{amountoutstanding},
159
        accountdata         => $data->{accountdata}
160
    };
161
}
162
163
$template->param(
164
    todaysdate      => format_date($todaysdate),
165
    from            => $startdate,
166
    to              => $enddate,
167
    ratio           => $max_bill,
168
    billingloop     => \@billingdata,
169
    DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
170
);
171
172
output_html_with_http_headers $input, $cookie, $template->output;
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/billing.tt (-162 lines)
Lines 1-162 Link Here
1
[% INCLUDE 'doc-head-open.inc' %]
2
<title>Koha &rsaquo; Circulation &rsaquo; Billing</title>
3
[% INCLUDE 'doc-head-close.inc' %]
4
<!-- Additions to enable Calendar system -->
5
<link rel="stylesheet" type="text/css" href="[% themelang %]/lib/calendar/calendar-system.css" />
6
<script type="text/javascript" src="[% themelang %]/lib/calendar/calendar.js"></script>
7
<script type="text/javascript" src="[% themelang %]/lib/calendar/calendar-en.js"></script>
8
<script type="text/javascript" src="[% themelang %]/lib/calendar/calendar-setup.js"></script>
9
<!-- End of additions -->
10
</head>
11
<body id="circ_billing" class="circ">
12
[% INCLUDE 'header.inc' %]
13
[% INCLUDE 'circ-search.inc' %]
14
15
16
<div id="breadcrumbs"><a href="/cgi-bin/koha/mainpage.pl">Home</a> &rsaquo; <a href="/cgi-bin/koha/circ/circulation-home.pl">Circulation</a> &rsaquo; Billing</div>
17
18
<div id="doc3" class="yui-t2">
19
   
20
   <div id="bd">
21
	<div id="yui-main">
22
	<div class="yui-b">
23
24
<h2>Billing from [% from %] to [% to %]</h2>
25
<h3>Reported  on [% todaysdate %]</h3>
26
27
28
29
30
[% IF ( billingloop ) %]
31
<h4>The following patrons have bills.</h4>
32
    <table>
33
    <tr>
34
        <th>Patron
35
        <a href="/cgi-bin/koha/circ/billing.pl?order=patron&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
36
        </th>
37
        <th>Fee Item
38
        <a href="/cgi-bin/koha/circ/billing.pl?order=fee&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
39
        </th>
40
        <th>Description
41
        <a href="/cgi-bin/koha/circ/billing.pl?order=desc&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
42
        </th>
43
        <th>Type
44
        <a href="/cgi-bin/koha/circ/billing.pl?order=type&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
45
        </th>
46
        <th>Date
47
        <a href="/cgi-bin/koha/circ/billing.pl?order=date&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
48
        </th>
49
        <th>Total Amount
50
        <a href="/cgi-bin/koha/circ/billing.pl?order=total&amp;from=[% from %]&amp;to=[% to %]">Sort</a>
51
        </th>
52
    </tr>
53
    
54
    [% FOREACH billingloo IN billingloop %]
55
        <tr>
56
            	 <td>
57
					 <p><a href="/cgi-bin/koha/members/boraccount.pl?borrowernumber=[% billingloo.borrowernumber %]">[% billingloo.patronname %]</a><br />[% billingloo.phone %]<br />
58
                [% IF ( billingloo.email ) %]<a href="mailto:[% billingloo.email %]?subject=Account: [% billingloo.title %]">
59
    				 [% billingloo.email %]</a>[% END %]
60
                </p>
61
            	 </td>
62
                <td align="right">
63
                    <p>[% billingloo.l_amountoutstanding %]</p>
64
                </td>
65
                <td>
66
                    <p>[% billingloo.l_description %]</p>
67
                </td>
68
                <td>
69
                    <p>[% billingloo.l_accounttype %]</p>
70
                </td>
71
                <td>
72
                    <p>[% billingloo.l_date %]</p>
73
                </td>                
74
                <td align="right">
75
                    <p>[% billingloo.sum_amount %]</p>
76
                </td>
77
        </tr>
78
    [% END %]
79
    </table>
80
    [% ELSE %]
81
        <b>No items found.</b>
82
    [% END %]
83
84
</div>
85
</div>
86
<div class="yui-b">
87
<form action="/cgi-bin/koha/circ/billing.pl" method="post" >
88
<fieldset class="brief">
89
<ol><li><label for="ratio">
90
    Currency Cutoff:
91
</label>
92
<input type="text" size="5" id="ratio" name="ratio" value="[% ratio %]" /></li>
93
<li><label for="from">
94
    Start date:
95
</label>
96
<input type="text" size="10" id="from" name="from" value="[% from %]" />
97
<img src="[% themelang %]/lib/calendar/cal.gif"  border="0" id="openCalendarFrom" style="cursor: pointer;" alt="" />
98
<script language="JavaScript" type="text/javascript">
99
function validate1(date) {
100
    var day = date.getDate();
101
    var month = date.getMonth() + 1;
102
    var year = date.getFullYear();
103
    var weekDay = date.getDay();
104
    var dayMonth = month + '-' + day;
105
    var dateString = year + '-' + month + '-' + day;
106
    var dateTo = document.getElementById('to').value.split("-");
107
    var limitDate = new Date(dateTo[0], (dateTo[1] - 1), dateTo[2]);
108
    if (date > limitDate) {
109
            return true;
110
    } else {
111
            return false;
112
    }
113
}
114
Calendar.setup(
115
        {
116
        inputField : "from",
117
        ifFormat : "[% DHTMLcalendar_dateformat %]",
118
        button : "openCalendarFrom",
119
        disableFunc : validate1,
120
        dateStatusFunc : validate1
121
        }
122
);
123
</script></li>
124
<li><label for="to" >
125
    End date:
126
</label>
127
<input size="10" id="to" name="to" value="[% to %]" type="text" />
128
<img src="[% themelang %]/lib/calendar/cal.gif" alt="" id="openCalendarTo" style="cursor: pointer;" border="0" />
129
<script type="text/javascript">
130
        function validate2(date) {
131
            var day = date.getDate();
132
            var month = date.getMonth() + 1;
133
            var year = date.getFullYear();
134
            var weekDay = date.getDay();
135
            var dayMonth = month + '-' + day;
136
            var dateString = year + '-' + month + '-' + day;
137
            var dateFrom = document.getElementById('from').value.split("-");
138
            var limitDate = new Date(dateFrom[0], (dateFrom[1] - 1), dateFrom[2]);
139
            if (limitDate > date) {
140
                    return true;
141
            } else {
142
                    return false;
143
            }
144
        }
145
146
        Calendar.setup(
147
                {
148
                    inputField : "to",
149
                    ifFormat : "[% DHTMLcalendar_dateformat %]",
150
                    button : "openCalendarTo",
151
                    disableFunc : validate2,
152
                    dateStatusFunc : validate2
153
                }
154
        );
155
</script>
156
(inclusive)</li></ol>
157
158
<fieldset class="action"><input type="submit" value="Go" class="submit"/></fieldset></fieldset>
159
</form>
160
</div>
161
</div>
162
[% INCLUDE 'intranet-bottom.inc' %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation-home.tt (-2 lines)
Lines 42-48 Link Here
42
	- <b>Warning:</b> This report is very resource intensive on
42
	- <b>Warning:</b> This report is very resource intensive on
43
	systems with large numbers of overdue items.</li>[% END %]
43
	systems with large numbers of overdue items.</li>[% END %]
44
	<li>    <a href="/cgi-bin/koha/circ/branchoverdues.pl">Overdues with fines</a> - Limited to your library.  See report help for other details.</li>
44
	<li>    <a href="/cgi-bin/koha/circ/branchoverdues.pl">Overdues with fines</a> - Limited to your library.  See report help for other details.</li>
45
<!--	<li>    <a href="/cgi-bin/koha/circ/billing.pl">Billing</a></li> -->
46
<!--	<li>    <a href="/cgi-bin/koha/circ/stats.pl?time=yesterday">Daily reconciliation</a></li> -->
45
<!--	<li>    <a href="/cgi-bin/koha/circ/stats.pl?time=yesterday">Daily reconciliation</a></li> -->
47
</ul>
46
</ul>
48
	
47
	
49
- 

Return to bug 2034