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