3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
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
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.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
26 use C4::Dates qw/format_date format_date_in_iso/;
28 use Date::Calc qw/Today Add_Delta_YM/;
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;
36 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
38 template_name => "circ/billing.tmpl",
42 flagsrequired => { circulate => "circulate_remaining_permissions" },
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));
53 $startdate =~ s/^\s+//;
54 $startdate =~ 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;
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
67 $sqldatewhere .= " AND date <= ?";
68 push @query_params, format_date_in_iso($enddate);
70 push @query_params, $max_bill;
71 # the date below is to check for compliance of all fees prior
73 $presqldatewhere .= " AND date < ?";
74 push @query_params, format_date_in_iso($startdate);
76 push @query_params, $max_bill;
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 ";
91 $sqlorderby = " ORDER BY surname, firstname ";
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,
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,
110 accountlines.date as accountdate
112 borrowers, accountlines
114 accountlines.borrowernumber = borrowers.borrowernumber
115 AND accountlines.amountoutstanding <> 0
116 AND accountlines.borrowernumber
117 IN (SELECT borrowernumber FROM accountlines
118 where borrowernumber >= 0
120 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
121 AND accountlines.borrowernumber
122 NOT IN (SELECT borrowernumber FROM accountlines
123 where borrowernumber >= 0
125 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
128 if (C4::Context->preference('IndependantBranches')){
129 $strsth .= " AND borrowers.branchcode=? ";
130 push @query_params, C4::Context->userenv->{'branch'};
132 $strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? " . $sqlorderby;
133 push @query_params, $max_bill;
135 my $sth = $dbh->prepare($strsth);
136 $sth->execute(@query_params);
139 while ( my $data = $sth->fetchrow_hashref ) {
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},
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}
164 todaysdate => format_date($todaysdate),
168 billingloop => \@billingdata,
169 DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
172 output_html_with_http_headers $input, $cookie, $template->output;