circ/billing.pl

   1 #!/usr/bin/perl
   2
   3
   4 # Copyright 2000-2002 Katipo Communications
   5 #
   6 # This file is part of Koha.
   7 #
   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
  11 # version.
  12 #
  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.
  16 #
  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
  20
  21 use strict;
  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');
  35
  36 my $theme = $input->param('theme');    # only used if allowthemeoverride is set
  37
  38 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
  39     {
  40         template_name   => "circ/billing.tmpl",
  41         query           => $input,
  42         type            => "intranet",
  43         authnotrequired => 0,
  44         flagsrequired   => { circulate => 1 },
  45         debug           => 1,
  46     }
  47 );
  48
  49 my $duedate;
  50 my $borrowernumber;
  51 my $itemnum;
  52 my $data1;
  53 my $data2;
  54 my $data3;
  55 my $name;
  56 my $phone;
  57 my $email;
  58 my $biblionumber;
  59 my $title;
  60 my $author;
  61
  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));
  67
  68 #               Predefine the start and end dates if they are not already defined
  69 $startdate =~ s/^\s+//;
  70 $startdate =~ s/\s+$//;
  71 $enddate =~ s/^\s+//;
  72 $enddate =~ 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);
  76 }
  77 if (!defined($enddate) or $enddate eq "") {
  78         $enddate = format_date($todaysdate);
  79 }
  80 if (!defined($max_bill) or $max_bill eq "") {
  81         $max_bill = C4::Context->preference('noissuescharge');
  82         if ($max_bill <= 0) {
  83                 $max_bill = 20.00;
  84         }
  85 }
  86
  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
  92 if ($enddate) {
  93     $sqldatewhere .= " AND date <= ?";
  94     push @query_params, format_date_in_iso($enddate);
  95 }
  96 push @query_params, $max_bill;
  97 # the date below is to check for compliance of all fees prior
  98 if ($startdate) {
  99     $presqldatewhere .= " AND date < ?";
 100     push @query_params, format_date_in_iso($startdate);
 101 }
 102 push @query_params, $max_bill;
 103
 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 ";
 116 } else {
 117         $sqlorderby = " ORDER BY surname, firstname ";
 118 }
 119 my $strsth =
 120         "SELECT
 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,
 126                 count(*) as cnt,
 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,
 135                 description,
 136                 accountlines.date as accountdate
 137                 FROM
 138                         borrowers, accountlines
 139                 WHERE
 140                         accountlines.borrowernumber = borrowers.borrowernumber
 141                 AND accountlines.amountoutstanding <> 0
 142                 AND accountlines.borrowernumber
 143                         IN (SELECT borrowernumber FROM accountlines
 144                                 where borrowernumber >= 0
 145                                 $sqldatewhere
 146                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
 147                 AND accountlines.borrowernumber
 148                         NOT IN (SELECT borrowernumber FROM accountlines
 149                                 where borrowernumber >= 0
 150                                 $presqldatewhere
 151                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? )
 152 ";
 153
 154
 155 if (C4::Context->preference('IndependantBranches')){
 156         $strsth .= " AND borrowers.branchcode=? ";
 157     push @query_params, C4::Context->userenv->{'branch'};
 158 }
 159 $strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= ? " . $sqlorderby;
 160 push @query_params, $max_bill;
 161
 162 my $sth = $dbh->prepare($strsth);
 163 $sth->execute(@query_params);
 164
 165 my @billingdata;
 166 my $previous;
 167 my $this;
 168 while ( my $data = $sth->fetchrow_hashref ) {
 169     my @itemlist;
 170     push(
 171         @billingdata,
 172         {
 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},
 180                                 cnt                                             =>              $data->{cnt},
 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}
 192         }
 193     );
 194 }
 195
 196
 197 $sth->finish;
 198
 199 $template->param(
 200     todaysdate      => format_date($todaysdate),
 201     from            => $startdate,
 202     to              => $enddate,
 203     ratio           => $max_bill,
 204     billingloop     => \@billingdata,
 205     "BiblioDefaultView".C4::Context->preference("BiblioDefaultView") => 1,
 206     DHTMLcalendar_dateformat =>  C4::Dates->DHTMLcalendar(),
 207 );
 208
 209 output_html_with_http_headers $input, $cookie, $template->output;