koha-dev.rot13.org Git - srvgit/blob - 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 # the dates below is to check for compliance of the current date range
  91 #$sqldatewhere .= " AND date >= " . $dbh->quote(format_date_in_iso($startdate))  if ($startdate) ;
  92 $sqldatewhere .= " AND date <= " . $dbh->quote(format_date_in_iso($enddate))  if ($enddate) ;
  93 # the date below is to check for compliance of all fees prior
  94 $presqldatewhere .= " AND date < " . $dbh->quote(format_date_in_iso($startdate))  if ($startdate) ;
  95
  96 if ($order eq "patron") {
  97         $sqlorderby = " order by surname, firstname ";
  98 } elsif ($order eq "fee") {
  99     $sqlorderby = " order by l_amountoutstanding DESC ";
 100 } elsif ($order eq "desc") {
 101     $sqlorderby = " order by l_description ";
 102 } elsif ($order eq "type") {
 103     $sqlorderby = " order by l_accounttype ";
 104 } elsif ($order eq "date") {
 105     $sqlorderby = " order by l_date DESC ";
 106 } elsif ($order eq "total") {
 107     $sqlorderby = " order by sum_amount DESC ";
 108 } else {
 109         $sqlorderby = " order by surname, firstname ";
 110 }
 111 my $strsth =
 112         "SELECT
 113                 GROUP_CONCAT(accountlines.accounttype ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_accounttype,
 114                 GROUP_CONCAT(description ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_description,
 115                 GROUP_CONCAT(round(amountoutstanding,2) ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_amountoutstanding,
 116                 GROUP_CONCAT(accountlines.date ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_date,
 117                 GROUP_CONCAT(accountlines.itemnumber ORDER BY accountlines.date DESC SEPARATOR '<br>') as l_itemnumber,
 118                 count(*) as cnt,
 119                 max(accountlines.date) as maxdate,
 120                 round(sum(amountoutstanding),2) as sum_amount,
 121                 borrowers.borrowernumber as borrowernumber,
 122                 borrowers.surname as surname,
 123                 borrowers.firstname as firstname,
 124                 borrowers.email as email,
 125                 borrowers.phone as phone,
 126                 accountlines.itemnumber,
 127                 description,
 128                 accountlines.date as accountdate
 129                 FROM
 130                         borrowers, accountlines
 131                 WHERE
 132                         accountlines.borrowernumber = borrowers.borrowernumber
 133                 AND accountlines.amountoutstanding <> 0
 134                 AND accountlines.borrowernumber
 135                         IN (SELECT borrowernumber FROM accountlines
 136                                 where borrowernumber >= 0
 137                                 $sqldatewhere
 138                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill )
 139                 AND accountlines.borrowernumber
 140                         NOT IN (SELECT borrowernumber FROM accountlines
 141                                 where borrowernumber >= 0
 142                                 $presqldatewhere
 143                                 GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill )
 144 ";
 145
 146
 147 if (C4::Context->preference('IndependantBranches')){
 148         $strsth .= " AND borrowers.branchcode=? ";
 149 }
 150 $strsth .= " GROUP BY accountlines.borrowernumber HAVING sum(amountoutstanding) >= $max_bill " . $sqlorderby;
 151 my $sth = $dbh->prepare($strsth);
 152
 153 if (C4::Context->preference('IndependantBranches')){
 154         $sth->execute(C4::Context->userenv->{'branch'});
 155 }
 156 else {
 157         $sth->execute();
 158 }
 159 my @billingdata;
 160 my $previous;
 161 my $this;
 162 while ( my $data = $sth->fetchrow_hashref ) {
 163     my @itemlist;
 164     push(
 165         @billingdata,
 166         {
 167                                 l_accountype                    =>              $data->{l_accounttype},
 168                                 l_description                   =>              $data->{l_description},
 169                                 l_amountoutstanding     =>              $data->{l_amountoutstanding},
 170                                 l_date                                  =>              $data->{l_date},
 171                                 l_itemnumber                    =>              $data->{l_itemnumber},
 172                                 l_accounttype                   =>              $data->{l_accounttype},
 173                                 l_title                                 =>              $data->{l_title},
 174                                 cnt                                             =>              $data->{cnt},
 175                                 maxdate                                 =>              $data->{maxdate},
 176                                 sum_amount                              =>              $data->{sum_amount},
 177                                 borrowernumber                  =>              $data->{borrowernumber},
 178                                 surname                                 =>              $data->{surname},
 179                                 firstname                               =>              $data->{firstname},
 180                                 phone                                           =>              $data->{phone},
 181                                 email                                           =>              $data->{email},
 182                                 patronname                              =>              $data->{surname} . ", " . $data->{firstname} ,
 183                                 description                             =>              $data->{description},
 184                                 amountoutstanding               =>              $data->{amountoutstanding},
 185                                 accountdata                             =>              $data->{accountdata}
 186         }
 187     );
 188 }
 189
 190
 191 $sth->finish;
 192
 193 $template->param(
 194     todaysdate      => format_date($todaysdate),
 195     from            => $startdate,
 196     to              => $enddate,
 197     ratio           => $max_bill,
 198     billingloop     => \@billingdata,
 199     "BiblioDefaultView".C4::Context->preference("BiblioDefaultView") => 1,
 200     DHTMLcalendar_dateformat =>  C4::Dates->DHTMLcalendar(),
 201 );
 202
 203 output_html_with_http_headers $input, $cookie, $template->output;