4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # 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');
36 my $theme = $input->param('theme'); # only used if allowthemeoverride is set
38 my ( $template, $loggedinuser, $cookie ) = get_template_and_user
(
40 template_name
=> "circ/billing.tmpl",
44 flagsrequired
=> { circulate
=> 1 },
62 my ( $year, $month, $day ) = Today
();
63 my $todaysdate = sprintf("%-04.4d-%-02.2d-%02.2d", $year, $month, $day);
64 # Find yesterday for the default shelf pull start and end dates
65 # A default of the prior years's holds is a reasonable way to pull holds
66 my $datelastyear = sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM
($year, $month, $day, -1, 0));
68 # Predefine the start and end dates if they are not already defined
69 $startdate =~ s/^\s+//;
70 $startdate =~ s/\s+$//;
73 # Check if null, should string match, if so set start and end date to yesterday
74 if (!defined($startdate) or $startdate eq "") {
75 $startdate = format_date
($datelastyear);
77 if (!defined($enddate) or $enddate eq "") {
78 $enddate = format_date
($todaysdate);
80 if (!defined($max_bill) or $max_bill eq "") {
81 $max_bill = C4
::Context
->preference('noissuescharge');
87 my $dbh = C4
::Context
->dbh;
88 my ($sqlorderby, $sqldatewhere, $presqldatewhere) = ("","","");
89 $debug and warn format_date_in_iso
($startdate) . "\n" . format_date_in_iso
($enddate);
90 my @query_params = ();
91 # the dates below is to check for compliance of the current date range
93 $sqldatewhere .= " AND date <= ?";
94 push @query_params, format_date_in_iso
($enddate);
96 push @query_params, $max_bill;
97 # the date below is to check for compliance of all fees prior
99 $presqldatewhere .= " AND date < ?";
100 push @query_params, format_date_in_iso
($startdate);
102 push @query_params, $max_bill;
104 if ($order eq "patron") {
105 $sqlorderby = " ORDER BY surname, firstname ";
106 } elsif ($order eq "fee") {
107 $sqlorderby = " ORDER BY l_amountoutstanding DESC ";
108 } elsif ($order eq "desc") {
109 $sqlorderby = " ORDER BY l_description ";
110 } elsif ($order eq "type") {
111 $sqlorderby = " ORDER BY l_accounttype ";
112 } elsif ($order eq "date") {
113 $sqlorderby = " ORDER BY l_date DESC ";
114 } elsif ($order eq "total") {
115 $sqlorderby = " ORDER BY sum_amount DESC ";
117 $sqlorderby = " ORDER BY surname, firstname ";
121 GROUP_CONCAT(accountlines.accounttype ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_accounttype,
122 GROUP_CONCAT(description ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_description,
123 GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_amountoutstanding,
124 GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_date,
125 GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date DESC SEPARATOR '<br/>') as l_itemnumber,
127 max(accountlines.date) as maxdate,
128 round(sum(amountoutstanding),2) as sum_amount,
129 borrowers.borrowernumber as borrowernumber,
130 borrowers.surname as surname,
131 borrowers.firstname as firstname,
132 borrowers.email as email,
133 borrowers.phone as phone,
134 accountlines.itemnumber,
136 accountlines.date as accountdate
138 borrowers, accountlines
140 accountlines.borrowernumber = borrowers.borrowernumber
141 AND accountlines.amountoutstanding <> 0
142 AND accountlines.borrowernumber
143 IN (SELECT borrowernumber FROM accountlines
144 where borrowernumber >= 0
146 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
147 AND accountlines.borrowernumber
148 NOT IN (SELECT borrowernumber FROM accountlines
149 where borrowernumber >= 0
151 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
155 if (C4
::Context
->preference('IndependantBranches')){
156 $strsth .= " AND borrowers.branchcode=? ";
157 push @query_params, C4
::Context
->userenv->{'branch'};
159 $strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? " . $sqlorderby;
160 push @query_params, $max_bill;
162 my $sth = $dbh->prepare($strsth);
163 $sth->execute(@query_params);
168 while ( my $data = $sth->fetchrow_hashref ) {
173 l_accountype
=> $data->{l_accounttype
},
174 l_description
=> $data->{l_description
},
175 l_amountoutstanding
=> $data->{l_amountoutstanding
},
176 l_date
=> $data->{l_date
},
177 l_itemnumber
=> $data->{l_itemnumber
},
178 l_accounttype
=> $data->{l_accounttype
},
179 l_title
=> $data->{l_title
},
181 maxdate
=> $data->{maxdate
},
182 sum_amount
=> $data->{sum_amount
},
183 borrowernumber
=> $data->{borrowernumber
},
184 surname
=> $data->{surname
},
185 firstname
=> $data->{firstname
},
186 phone
=> $data->{phone
},
187 email
=> $data->{email
},
188 patronname
=> $data->{surname
} . ", " . $data->{firstname
} ,
189 description
=> $data->{description
},
190 amountoutstanding
=> $data->{amountoutstanding
},
191 accountdata
=> $data->{accountdata
}
200 todaysdate
=> format_date
($todaysdate),
204 billingloop
=> \
@billingdata,
205 "BiblioDefaultView".C4
::Context
->preference("BiblioDefaultView") => 1,
206 DHTMLcalendar_dateformat
=> C4
::Dates
->DHTMLcalendar(),
209 output_html_with_http_headers
$input, $cookie, $template->output;