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; |