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 Date::Calc qw/Today Add_Delta_YM/;
  28
  29 use vars qw($debug);
  30
  31 BEGIN {
  32     $debug = $ENV{DEBUG} || 0;
  33 }
  34
  35 my $input = new CGI;
  36 my $order = $input->param('order');
  37 my $startdate=$input->param('from');
  38 my $enddate=$input->param('to');
  39 my $max_bill=$input->param('ratio');
  40
  41 my $theme = $input->param('theme');    # only used if allowthemeoverride is set
  42
  43 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
  44     {
  45         template_name   => "circ/billing.tmpl",
  46         query           => $input,
  47         type            => "intranet",
  48         authnotrequired => 0,
  49         flagsrequired   => { circulate => 1 },
  50         debug           => 1,
  51     }
  52 );
  53
  54 my $duedate;
  55 my $borrowernumber;
  56 my $itemnum;
  57 my $data1;
  58 my $data2;
  59 my $data3;
  60 my $name;
  61 my $phone;
  62 my $email;
  63 my $biblionumber;
  64 my $title;
  65 my $author;
  66
  67 my ( $year, $month, $day ) = Today();
  68 my $todaysdate   = sprintf("%-04.4d-%-02.2d-%02.2d", $year, $month, $day);
  69 # Find yesterday for the default shelf pull start and end dates
  70 #    A default of the prior years's holds is a reasonable way to pull holds
  71 my $datelastyear = sprintf("%-04.4d-%-02.2d-%02.2d", Add_Delta_YM($year, $month, $day, -1, 0));
  72
  73 #               Predefine the start and end dates if they are not already defined
  74 $startdate =~ s/^\s+//;
  75 $startdate =~ s/\s+$//;
  76 $enddate =~ s/^\s+//;
  77 $enddate =~ s/\s+$//;
  78 #               Check if null, should string match, if so set start and end date to yesterday
  79 if (!defined($startdate) or $startdate eq "") {
  80         $startdate = format_date($datelastyear);
  81 }
  82 if (!defined($enddate) or $enddate eq "") {
  83         $enddate = format_date($todaysdate);
  84 }
  85 if (!defined($max_bill) or $max_bill eq "") {
  86         $max_bill = C4::Context->preference('noissuescharge');
  87         if ($max_bill <= 0) {
  88                 $max_bill = 20.00;
  89         }
  90 }
  91
  92 my $dbh    = C4::Context->dbh;
  93 my ($sqlorderby, $sqldatewhere, $presqldatewhere) = ("","","");
  94 $debug and warn format_date_in_iso($startdate) . "\n" . format_date_in_iso($enddate);
  95 # the dates below is to check for compliance of the current date range
  96 #$sqldatewhere .= " AND date >= " . $dbh->quote(format_date_in_iso($startdate))  if ($startdate) ;
  97 $sqldatewhere .= " AND date <= " . $dbh->quote(format_date_in_iso($enddate))  if ($enddate) ;
  98 # the date below is to check for compliance of all fees prior
  99 $presqldatewhere .= " AND date < " . $dbh->quote(format_date_in_iso($startdate))  if ($startdate) ;
 100
 101 if ($order eq "patron") {
 102         $sqlorderby = " order by surname, firstname ";
 103 } elsif ($order eq "fee") {
 104     $sqlorderby = " order by l_amountoutstanding DESC ";
 105 } elsif ($order eq "desc") {
 106     $sqlorderby = " order by l_description ";
 107 } elsif ($order eq "type") {
 108     $sqlorderby = " order by l_accounttype ";
 109 } elsif ($order eq "date") {
 110     $sqlorderby = " order by l_date DESC ";
 111 } elsif ($order eq "total") {
 112     $sqlorderby = " order by sum_amount DESC ";
 113 } else {
 114         $sqlorderby = " order by surname, firstname ";
 115 }
 116 my $strsth =
 117         "SELECT
 118                 GROUP_CONCAT(accountlines.accounttype ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_accounttype,
 119                 GROUP_CONCAT(description ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_description,
 120                 GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_amountoutstanding,
 121                 GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_date,
 122                 GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_itemnumber,
 123                 count(*) as cnt,
 124                 max(accountlines.date) as maxdate,
 125                 round(sum(amountoutstanding),2) as sum_amount,
 126                 borrowers.borrowernumber as borrowernumber,
 127                 borrowers.surname as surname,
 128                 borrowers.firstname as firstname,
 129                 borrowers.email as email,
 130                 borrowers.phone as phone,
 131                 accountlines.itemnumber,
 132                 description,
 133                 accountlines.date as accountdate
 134                 FROM
 135                         borrowers, accountlines
 136                 WHERE
 137                         accountlines.borrowernumber = borrowers.borrowernumber
 138                 AND accountlines.amountoutstanding <> 0
 139                 AND accountlines.borrowernumber
 140                         IN (SELECT borrowernumber FROM accountlines
 141                                 where borrowernumber >= 0
 142                                 $sqldatewhere
 143                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill )
 144                 AND accountlines.borrowernumber
 145                         NOT IN (SELECT borrowernumber FROM accountlines
 146                                 where borrowernumber >= 0
 147                                 $presqldatewhere
 148                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill )
 149 ";
 150
 151
 152 if (C4::Context->preference('IndependantBranches')){
 153         $strsth .= " AND borrowers.branchcode=? ";
 154 }
 155 $strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill " . $sqlorderby;
 156 my $sth = $dbh->prepare($strsth);
 157
 158 if (C4::Context->preference('IndependantBranches')){
 159         $sth->execute(C4::Context->userenv->{'branch'});
 160 }
 161 else {
 162         $sth->execute();
 163 }
 164 my @billingdata;
 165 my $previous;
 166 my $this;
 167 while ( my $data = $sth->fetchrow_hashref ) {
 168     my @itemlist;
 169     push(
 170         @billingdata,
 171         {
 172                                 l_accountype                    =>              $data->{l_accounttype},
 173                                 l_description                   =>              $data->{l_description},
 174                                 l_amountoutstanding     =>              $data->{l_amountoutstanding},
 175                                 l_date                                  =>              $data->{l_date},
 176                                 l_itemnumber                    =>              $data->{l_itemnumber},
 177                                 l_accounttype                   =>              $data->{l_accounttype},
 178                                 l_title                                 =>              $data->{l_title},
 179                                 cnt                                             =>              $data->{cnt},
 180                                 maxdate                                 =>              $data->{maxdate},
 181                                 sum_amount                              =>              $data->{sum_amount},
 182                                 borrowernumber                  =>              $data->{borrowernumber},
 183                                 surname                                 =>              $data->{surname},
 184                                 firstname                               =>              $data->{firstname},
 185                                 phone                                           =>              $data->{phone},
 186                                 email                                           =>              $data->{email},
 187                                 patronname                              =>              $data->{surname} . ", " . $data->{firstname} ,
 188                                 description                             =>              $data->{description},
 189                                 amountoutstanding               =>              $data->{amountoutstanding},
 190                                 accountdata                             =>              $data->{accountdata}
 191         }
 192     );
 193 }
 194
 195
 196 $sth->finish;
 197
 198 $template->param(
 199     todaysdate      => format_date($todaysdate),
 200     from            => $startdate,
 201     to              => $enddate,
 202     ratio           => $max_bill,
 203     billingloop     => \@billingdata,
 204     "BiblioDefaultView".C4::Context->preference("BiblioDefaultView") => 1,
 205     DHTMLcalendar_dateformat =>  C4::Dates->DHTMLcalendar(),
 206 );
 207
 208 output_html_with_http_headers $input, $cookie, $template->output;