C4/Overdues.pm

   1 package C4::Overdues;
   2
   3
   4 # Copyright 2000-2002 Katipo Communications
   5 # copyright 2010 BibLibre
   6 #
   7 # This file is part of Koha.
   8 #
   9 # Koha is free software; you can redistribute it and/or modify it under the
  10 # terms of the GNU General Public License as published by the Free Software
  11 # Foundation; either version 2 of the License, or (at your option) any later
  12 # version.
  13 #
  14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  16 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  17 #
  18 # You should have received a copy of the GNU General Public License along
  19 # with Koha; if not, write to the Free Software Foundation, Inc.,
  20 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  21
  22 use strict;
  23 #use warnings; FIXME - Bug 2505
  24 use Date::Calc qw/Today Date_to_Days/;
  25 use Date::Manip qw/UnixDate/;
  26 use List::MoreUtils qw( uniq );
  27
  28 use C4::Circulation;
  29 use C4::Context;
  30 use C4::Accounts;
  31 use C4::Log; # logaction
  32 use C4::Debug;
  33
  34 use vars qw($VERSION @ISA @EXPORT);
  35
  36 BEGIN {
  37         # set the version for version checking
  38     $VERSION = 3.07.00.049;
  39         require Exporter;
  40         @ISA    = qw(Exporter);
  41         # subs to rename (and maybe merge some...)
  42         push @EXPORT, qw(
  43         &CalcFine
  44         &Getoverdues
  45         &checkoverdues
  46         &NumberNotifyId
  47         &AmountNotify
  48         &UpdateFine
  49         &GetFine
  50
  51         &CheckItemNotify
  52         &GetOverduesForBranch
  53         &RemoveNotifyLine
  54         &AddNotifyLine
  55         &GetOverdueMessageTransportTypes
  56         );
  57         # subs to remove
  58         push @EXPORT, qw(
  59         &BorType
  60         );
  61
  62         # check that an equivalent don't exist already before moving
  63
  64         # subs to move to Circulation.pm
  65         push @EXPORT, qw(
  66         &GetIssuesIteminfo
  67         );
  68
  69      # &GetIssuingRules - delete.
  70    # use C4::Circulation::GetIssuingRule instead.
  71
  72         # subs to move to Biblio.pm
  73         push @EXPORT, qw(
  74         &GetItems
  75         );
  76 }
  77
  78 =head1 NAME
  79
  80 C4::Circulation::Fines - Koha module dealing with fines
  81
  82 =head1 SYNOPSIS
  83
  84   use C4::Overdues;
  85
  86 =head1 DESCRIPTION
  87
  88 This module contains several functions for dealing with fines for
  89 overdue items. It is primarily used by the 'misc/fines2.pl' script.
  90
  91 =head1 FUNCTIONS
  92
  93 =head2 Getoverdues
  94
  95   $overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } );
  96
  97 Returns the list of all overdue books, with their itemtype.
  98
  99 C<$overdues> is a reference-to-array. Each element is a
 100 reference-to-hash whose keys are the fields of the issues table in the
 101 Koha database.
 102
 103 =cut
 104
 105 #'
 106 sub Getoverdues {
 107     my $params = shift;
 108     my $dbh = C4::Context->dbh;
 109     my $statement;
 110     if ( C4::Context->preference('item-level_itypes') ) {
 111         $statement = "
 112    SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode
 113      FROM issues
 114 LEFT JOIN items       USING (itemnumber)
 115     WHERE date_due < NOW()
 116 ";
 117     } else {
 118         $statement = "
 119    SELECT issues.*, biblioitems.itemtype, items.itype, items.homebranch, items.barcode
 120      FROM issues
 121 LEFT JOIN items       USING (itemnumber)
 122 LEFT JOIN biblioitems USING (biblioitemnumber)
 123     WHERE date_due < NOW()
 124 ";
 125     }
 126
 127     my @bind_parameters;
 128     if ( exists $params->{'minimumdays'} and exists $params->{'maximumdays'} ) {
 129         $statement .= ' AND TO_DAYS( NOW() )-TO_DAYS( date_due ) BETWEEN ? and ? ';
 130         push @bind_parameters, $params->{'minimumdays'}, $params->{'maximumdays'};
 131     } elsif ( exists $params->{'minimumdays'} ) {
 132         $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) > ? ';
 133         push @bind_parameters, $params->{'minimumdays'};
 134     } elsif ( exists $params->{'maximumdays'} ) {
 135         $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? ';
 136         push @bind_parameters, $params->{'maximumdays'};
 137     }
 138     $statement .= 'ORDER BY borrowernumber';
 139     my $sth = $dbh->prepare( $statement );
 140     $sth->execute( @bind_parameters );
 141     return $sth->fetchall_arrayref({});
 142 }
 143
 144
 145 =head2 checkoverdues
 146
 147     ($count, $overdueitems) = checkoverdues($borrowernumber);
 148
 149 Returns a count and a list of overdueitems for a given borrowernumber
 150
 151 =cut
 152
 153 sub checkoverdues {
 154     my $borrowernumber = shift or return;
 155     # don't select biblioitems.marc or biblioitems.marcxml... too slow on large systems
 156     my $sth = C4::Context->dbh->prepare(
 157         "SELECT biblio.*, items.*, issues.*,
 158                 biblioitems.volume,
 159                 biblioitems.number,
 160                 biblioitems.itemtype,
 161                 biblioitems.isbn,
 162                 biblioitems.issn,
 163                 biblioitems.publicationyear,
 164                 biblioitems.publishercode,
 165                 biblioitems.volumedate,
 166                 biblioitems.volumedesc,
 167                 biblioitems.collectiontitle,
 168                 biblioitems.collectionissn,
 169                 biblioitems.collectionvolume,
 170                 biblioitems.editionstatement,
 171                 biblioitems.editionresponsibility,
 172                 biblioitems.illus,
 173                 biblioitems.pages,
 174                 biblioitems.notes,
 175                 biblioitems.size,
 176                 biblioitems.place,
 177                 biblioitems.lccn,
 178                 biblioitems.url,
 179                 biblioitems.cn_source,
 180                 biblioitems.cn_class,
 181                 biblioitems.cn_item,
 182                 biblioitems.cn_suffix,
 183                 biblioitems.cn_sort,
 184                 biblioitems.totalissues
 185          FROM issues
 186          LEFT JOIN items       ON issues.itemnumber      = items.itemnumber
 187          LEFT JOIN biblio      ON items.biblionumber     = biblio.biblionumber
 188          LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 189             WHERE issues.borrowernumber  = ?
 190             AND   issues.date_due < NOW()"
 191     );
 192     # FIXME: SELECT * across 4 tables?  do we really need the marc AND marcxml blobs??
 193     $sth->execute($borrowernumber);
 194     my $results = $sth->fetchall_arrayref({});
 195     return ( scalar(@$results), $results);  # returning the count and the results is silly
 196 }
 197
 198 =head2 CalcFine
 199
 200     ($amount, $chargename,  $daycounttotal) = &CalcFine($item,
 201                                   $categorycode, $branch,
 202                                   $start_dt, $end_dt );
 203
 204 Calculates the fine for a book.
 205
 206 The issuingrules table in the Koha database is a fine matrix, listing
 207 the penalties for each type of patron for each type of item and each branch (e.g., the
 208 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
 209 members might get a longer grace period between the first and second
 210 reminders that a book is overdue).
 211
 212
 213 C<$item> is an item object (hashref).
 214
 215 C<$categorycode> is the category code (string) of the patron who currently has
 216 the book.
 217
 218 C<$branchcode> is the library (string) whose issuingrules govern this transaction.
 219
 220 C<$start_date> & C<$end_date> are DateTime objects
 221 defining the date range over which to determine the fine.
 222
 223 Fines scripts should just supply the date range over which to calculate the fine.
 224
 225 C<&CalcFine> returns four values:
 226
 227 C<$amount> is the fine owed by the patron (see above).
 228
 229 C<$chargename> is the chargename field from the applicable record in
 230 the categoryitem table, whatever that is.
 231
 232 C<$unitcount> is the number of chargeable units (days between start and end dates, Calendar adjusted where needed,
 233 minus any applicable grace period, or hours)
 234
 235 FIXME - What is chargename supposed to be ?
 236
 237 FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice",
 238 or "Final Notice".  But CalcFine never defined any value.
 239
 240 =cut
 241
 242 sub CalcFine {
 243     my ( $item, $bortype, $branchcode, $due_dt, $end_date  ) = @_;
 244     my $start_date = $due_dt->clone();
 245     # get issuingrules (fines part will be used)
 246     my $itemtype = $item->{itemtype} || $item->{itype};
 247     my $data = C4::Circulation::GetIssuingRule($bortype, $itemtype, $branchcode);
 248     my $fine_unit = $data->{lengthunit};
 249     $fine_unit ||= 'days';
 250
 251     my $chargeable_units = _get_chargeable_units($fine_unit, $start_date, $end_date, $branchcode);
 252     my $units_minus_grace = $chargeable_units - $data->{firstremind};
 253     my $amount = 0;
 254     if ($data->{'chargeperiod'}  && ($units_minus_grace > 0)  ) {
 255         if ( C4::Context->preference('FinesIncludeGracePeriod') ) {
 256             $amount = int($chargeable_units / $data->{'chargeperiod'}) * $data->{'fine'};# TODO fine calc should be in cents
 257         } else {
 258             $amount = int($units_minus_grace / $data->{'chargeperiod'}) * $data->{'fine'};
 259         }
 260     } else {
 261         # a zero (or null) chargeperiod or negative units_minus_grace value means no charge.
 262     }
 263     $amount = $data->{overduefinescap} if $data->{overduefinescap} && $amount > $data->{overduefinescap};
 264     $debug and warn sprintf("CalcFine returning (%s, %s, %s, %s)", $amount, $data->{'chargename'}, $units_minus_grace, $chargeable_units);
 265     return ($amount, $data->{'chargename'}, $units_minus_grace, $chargeable_units);
 266     # FIXME: chargename is NEVER populated anywhere.
 267 }
 268
 269
 270 =head2 _get_chargeable_units
 271
 272     _get_chargeable_units($unit, $start_date_ $end_date, $branchcode);
 273
 274 return integer value of units between C<$start_date> and C<$end_date>, factoring in holidays for C<$branchcode>.
 275
 276 C<$unit> is 'days' or 'hours' (default is 'days').
 277
 278 C<$start_date> and C<$end_date> are the two DateTimes to get the number of units between.
 279
 280 C<$branchcode> is the branch whose calendar to use for finding holidays.
 281
 282 =cut
 283
 284 sub _get_chargeable_units {
 285     my ($unit, $dt1, $dt2, $branchcode) = @_;
 286     my $charge_units = 0;
 287     my $charge_duration;
 288     if ($unit eq 'hours') {
 289         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 290             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 291             $charge_duration = $calendar->hours_between( $dt1, $dt2 );
 292         } else {
 293             $charge_duration = $dt2->delta_ms( $dt1 );
 294         }
 295         if($charge_duration->in_units('hours') == 0 && $charge_duration->in_units('seconds') > 0){
 296             return 1;
 297         }
 298         return $charge_duration->in_units('hours');
 299     }
 300     else { # days
 301         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 302             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 303             $charge_duration = $calendar->days_between( $dt1, $dt2 );
 304         } else {
 305             $charge_duration = $dt2->delta_days( $dt1 );
 306         }
 307         return $charge_duration->in_units('days');
 308     }
 309 }
 310
 311
 312 =head2 GetSpecialHolidays
 313
 314     &GetSpecialHolidays($date_dues,$itemnumber);
 315
 316 return number of special days  between date of the day and date due
 317
 318 C<$date_dues> is the envisaged date of book return.
 319
 320 C<$itemnumber> is the book's item number.
 321
 322 =cut
 323
 324 sub GetSpecialHolidays {
 325     my ( $date_dues, $itemnumber ) = @_;
 326
 327     # calcul the today date
 328     my $today = join "-", &Today();
 329
 330     # return the holdingbranch
 331     my $iteminfo = GetIssuesIteminfo($itemnumber);
 332
 333     # use sql request to find all date between date_due and today
 334     my $dbh = C4::Context->dbh;
 335     my $query =
 336       qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date
 337 FROM `special_holidays`
 338 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
 339 AND   DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
 340 AND branchcode=?
 341 |;
 342     my @result = GetWdayFromItemnumber($itemnumber);
 343     my @result_date;
 344     my $wday;
 345     my $dateinsec;
 346     my $sth = $dbh->prepare($query);
 347     $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} )
 348       ;    # FIXME: just use NOW() in SQL instead of passing in $today
 349
 350     while ( my $special_date = $sth->fetchrow_hashref ) {
 351         push( @result_date, $special_date );
 352     }
 353
 354     my $specialdaycount = scalar(@result_date);
 355
 356     for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) {
 357         $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" );
 358         ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) =
 359           localtime($dateinsec);
 360         for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) {
 361             if ( $wday == ( $result[$j]->{'weekday'} ) ) {
 362                 $specialdaycount--;
 363             }
 364         }
 365     }
 366
 367     return $specialdaycount;
 368 }
 369
 370 =head2 GetRepeatableHolidays
 371
 372     &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
 373
 374 return number of day closed between date of the day and date due
 375
 376 C<$date_dues> is the envisaged date of book return.
 377
 378 C<$itemnumber> is item number.
 379
 380 C<$difference> numbers of between day date of the day and date due
 381
 382 =cut
 383
 384 sub GetRepeatableHolidays {
 385     my ( $date_dues, $itemnumber, $difference ) = @_;
 386     my $dateinsec = UnixDate( $date_dues, "%o" );
 387     my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
 388       localtime($dateinsec);
 389     my @result = GetWdayFromItemnumber($itemnumber);
 390     my @dayclosedcount;
 391     my $j;
 392
 393     for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) {
 394         my $k = $wday;
 395
 396         for ( $j = 0 ; $j < $difference ; $j++ ) {
 397             if ( $result[$i]->{'weekday'} == $k ) {
 398                 push( @dayclosedcount, $k );
 399             }
 400             $k++;
 401             ( $k = 0 ) if ( $k eq 7 );
 402         }
 403     }
 404     return scalar(@dayclosedcount);
 405 }
 406
 407
 408 =head2 GetWayFromItemnumber
 409
 410     &Getwdayfromitemnumber($itemnumber);
 411
 412 return the different week day from repeatable_holidays table
 413
 414 C<$itemnumber> is  item number.
 415
 416 =cut
 417
 418 sub GetWdayFromItemnumber {
 419     my ($itemnumber) = @_;
 420     my $iteminfo = GetIssuesIteminfo($itemnumber);
 421     my @result;
 422     my $query = qq|SELECT weekday
 423     FROM repeatable_holidays
 424     WHERE branchcode=?
 425 |;
 426     my $sth = C4::Context->dbh->prepare($query);
 427
 428     $sth->execute( $iteminfo->{'branchcode'} );
 429     while ( my $weekday = $sth->fetchrow_hashref ) {
 430         push( @result, $weekday );
 431     }
 432     return @result;
 433 }
 434
 435
 436 =head2 GetIssuesIteminfo
 437
 438     &GetIssuesIteminfo($itemnumber);
 439
 440 return all data from issues about item
 441
 442 C<$itemnumber> is  item number.
 443
 444 =cut
 445
 446 sub GetIssuesIteminfo {
 447     my ($itemnumber) = @_;
 448     my $dbh          = C4::Context->dbh;
 449     my $query        = qq|SELECT *
 450     FROM issues
 451     WHERE itemnumber=?
 452     |;
 453     my $sth = $dbh->prepare($query);
 454     $sth->execute($itemnumber);
 455     my ($issuesinfo) = $sth->fetchrow_hashref;
 456     return $issuesinfo;
 457 }
 458
 459
 460 =head2 UpdateFine
 461
 462     &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
 463
 464 (Note: the following is mostly conjecture and guesswork.)
 465
 466 Updates the fine owed on an overdue book.
 467
 468 C<$itemnumber> is the book's item number.
 469
 470 C<$borrowernumber> is the borrower number of the patron who currently
 471 has the book on loan.
 472
 473 C<$amount> is the current amount owed by the patron.
 474
 475 C<$type> will be used in the description of the fine.
 476
 477 C<$description> is a string that must be present in the description of
 478 the fine. I think this is expected to be a date in DD/MM/YYYY format.
 479
 480 C<&UpdateFine> looks up the amount currently owed on the given item
 481 and sets it to C<$amount>, creating, if necessary, a new entry in the
 482 accountlines table of the Koha database.
 483
 484 =cut
 485
 486 #
 487 # Question: Why should the caller have to
 488 # specify both the item number and the borrower number? A book can't
 489 # be on loan to two different people, so the item number should be
 490 # sufficient.
 491 #
 492 # Possible Answer: You might update a fine for a damaged item, *after* it is returned.
 493 #
 494 sub UpdateFine {
 495     my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
 496         $debug and warn "UpdateFine($itemnum, $borrowernumber, $amount, " . ($type||'""') . ", $due) called";
 497     my $dbh = C4::Context->dbh;
 498     # FIXME - What exactly is this query supposed to do? It looks up an
 499     # entry in accountlines that matches the given item and borrower
 500     # numbers, where the description contains $due, and where the
 501     # account type has one of several values, but what does this _mean_?
 502     # Does it look up existing fines for this item?
 503     # FIXME - What are these various account types? ("FU", "O", "F", "M")
 504         #       "L"   is LOST item
 505         #   "A"   is Account Management Fee
 506         #   "N"   is New Card
 507         #   "M"   is Sundry
 508         #   "O"   is Overdue ??
 509         #   "F"   is Fine ??
 510         #   "FU"  is Fine UPDATE??
 511         #       "Pay" is Payment
 512         #   "REF" is Cash Refund
 513     my $sth = $dbh->prepare(
 514         "SELECT * FROM accountlines
 515         WHERE borrowernumber=?
 516         AND   accounttype IN ('FU','O','F','M')"
 517     );
 518     $sth->execute( $borrowernumber );
 519     my $data;
 520     my $total_amount_other = 0.00;
 521     my $due_qr = qr/$due/;
 522     # Cycle through the fines and
 523     # - find line that relates to the requested $itemnum
 524     # - accumulate fines for other items
 525     # so we can update $itemnum fine taking in account fine caps
 526     while (my $rec = $sth->fetchrow_hashref) {
 527         if ($rec->{itemnumber} == $itemnum && $rec->{description} =~ /$due_qr/) {
 528             if ($data) {
 529                 warn "Not a unique accountlines record for item $itemnum borrower $borrowernumber";
 530             } else {
 531                 $data = $rec;
 532                 next;
 533             }
 534         }
 535         $total_amount_other += $rec->{'amountoutstanding'};
 536     }
 537
 538     if (my $maxfine = C4::Context->preference('MaxFine')) {
 539         if ($total_amount_other + $amount > $maxfine) {
 540             my $new_amount = $maxfine - $total_amount_other;
 541             return if $new_amount <= 0.00;
 542             warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $new_amount - MaxFine reached";
 543             $amount = $new_amount;
 544         }
 545     }
 546
 547     if ( $data ) {
 548
 549                 # we're updating an existing fine.  Only modify if amount changed
 550         # Note that in the current implementation, you cannot pay against an accruing fine
 551         # (i.e. , of accounttype 'FU').  Doing so will break accrual.
 552         if ( $data->{'amount'} != $amount ) {
 553             my $diff = $amount - $data->{'amount'};
 554             #3341: diff could be positive or negative!
 555             my $out  = $data->{'amountoutstanding'} + $diff;
 556             my $query = "
 557                 UPDATE accountlines
 558                                 SET date=now(), amount=?, amountoutstanding=?,
 559                                         lastincrement=?, accounttype='FU'
 560                                 WHERE borrowernumber=?
 561                                 AND   itemnumber=?
 562                                 AND   accounttype IN ('FU','O')
 563                                 AND   description LIKE ?
 564                                 LIMIT 1 ";
 565             my $sth2 = $dbh->prepare($query);
 566                         # FIXME: BOGUS query cannot ensure uniqueness w/ LIKE %x% !!!
 567                         #               LIMIT 1 added to prevent multiple affected lines
 568                         # FIXME: accountlines table needs unique key!! Possibly a combo of borrowernumber and accountline.
 569                         #               But actually, we should just have a regular autoincrementing PK and forget accountline,
 570                         #               including the bogus getnextaccountno function (doesn't prevent conflict on simultaneous ops).
 571                         # FIXME: Why only 2 account types here?
 572                         $debug and print STDERR "UpdateFine query: $query\n" .
 573                                 "w/ args: $amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, \"\%$due\%\"\n";
 574             $sth2->execute($amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, "%$due%");
 575         } else {
 576             #      print "no update needed $data->{'amount'}"
 577         }
 578     } else {
 579         my $sth4 = $dbh->prepare(
 580             "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
 581         );
 582         $sth4->execute($itemnum);
 583         my $title = $sth4->fetchrow;
 584
 585 #         #   print "not in account";
 586 #         my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
 587 #         $sth3->execute;
 588 #
 589 #         # FIXME - Make $accountno a scalar.
 590 #         my @accountno = $sth3->fetchrow_array;
 591 #         $sth3->finish;
 592 #         $accountno[0]++;
 593 # begin transaction
 594                 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
 595                 my $desc = ($type ? "$type " : '') . "$title $due";     # FIXEDME, avoid whitespace prefix on empty $type
 596                 my $query = "INSERT INTO accountlines
 597                     (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno)
 598                             VALUES (?,?,now(),?,?,'FU',?,?,?)";
 599                 my $sth2 = $dbh->prepare($query);
 600                 $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno\n";
 601         $sth2->execute($borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno);
 602     }
 603     # logging action
 604     &logaction(
 605         "FINES",
 606         $type,
 607         $borrowernumber,
 608         "due=".$due."  amount=".$amount." itemnumber=".$itemnum
 609         ) if C4::Context->preference("FinesLog");
 610 }
 611
 612 =head2 BorType
 613
 614     $borrower = &BorType($borrowernumber);
 615
 616 Looks up a patron by borrower number.
 617
 618 C<$borrower> is a reference-to-hash whose keys are all of the fields
 619 from the borrowers and categories tables of the Koha database. Thus,
 620 C<$borrower> contains all information about both the borrower and
 621 category he or she belongs to.
 622
 623 =cut
 624
 625 sub BorType {
 626     my ($borrowernumber) = @_;
 627     my $dbh              = C4::Context->dbh;
 628     my $sth              = $dbh->prepare(
 629         "SELECT * from borrowers
 630       LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
 631       WHERE borrowernumber=?"
 632     );
 633     $sth->execute($borrowernumber);
 634     return $sth->fetchrow_hashref;
 635 }
 636
 637 =head2 GetFine
 638
 639     $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
 640
 641 return the total of fine
 642
 643 C<$itemnum> is item number
 644
 645 C<$borrowernumber> is the borrowernumber
 646
 647 =cut
 648
 649 sub GetFine {
 650     my ( $itemnum, $borrowernumber ) = @_;
 651     my $dbh   = C4::Context->dbh();
 652     my $query = q|SELECT sum(amountoutstanding) as fineamount FROM accountlines
 653     where accounttype like 'F%'
 654   AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?|;
 655     my $sth = $dbh->prepare($query);
 656     $sth->execute( $itemnum, $borrowernumber );
 657     my $fine = $sth->fetchrow_hashref();
 658     if ($fine->{fineamount}) {
 659         return $fine->{fineamount};
 660     }
 661     return 0;
 662 }
 663
 664 =head2 NumberNotifyId
 665
 666     (@notify) = &NumberNotifyId($borrowernumber);
 667
 668 Returns amount for all file per borrowers
 669 C<@notify> array contains all file per borrowers
 670
 671 C<$notify_id> contains the file number for the borrower number nad item number
 672
 673 =cut
 674
 675 sub NumberNotifyId{
 676     my ($borrowernumber)=@_;
 677     my $dbh = C4::Context->dbh;
 678     my $query=qq|    SELECT distinct(notify_id)
 679             FROM accountlines
 680             WHERE borrowernumber=?|;
 681     my @notify;
 682     my $sth = $dbh->prepare($query);
 683     $sth->execute($borrowernumber);
 684     while ( my ($numberofnotify) = $sth->fetchrow ) {
 685         push( @notify, $numberofnotify );
 686     }
 687     return (@notify);
 688 }
 689
 690 =head2 AmountNotify
 691
 692     ($totalnotify) = &AmountNotify($notifyid);
 693
 694 Returns amount for all file per borrowers
 695 C<$notifyid> is the file number
 696
 697 C<$totalnotify> contains amount of a file
 698
 699 C<$notify_id> contains the file number for the borrower number and item number
 700
 701 =cut
 702
 703 sub AmountNotify{
 704     my ($notifyid,$borrowernumber)=@_;
 705     my $dbh = C4::Context->dbh;
 706     my $query=qq|    SELECT sum(amountoutstanding)
 707             FROM accountlines
 708             WHERE notify_id=? AND borrowernumber = ?|;
 709     my $sth=$dbh->prepare($query);
 710         $sth->execute($notifyid,$borrowernumber);
 711         my $totalnotify=$sth->fetchrow;
 712     $sth->finish;
 713     return ($totalnotify);
 714 }
 715
 716 =head2 GetItems
 717
 718     ($items) = &GetItems($itemnumber);
 719
 720 Returns the list of all delays from overduerules.
 721
 722 C<$items> is a reference-to-hash whose keys are all of the fields
 723 from the items tables of the Koha database. Thus,
 724
 725 C<$itemnumber> contains the borrower categorycode
 726
 727 =cut
 728
 729 # FIXME: This is a bad function to have here.
 730 # Shouldn't it be in C4::Items?
 731 # Shouldn't it be called GetItem since you only get 1 row?
 732 # Shouldn't it be called GetItem since you give it only 1 itemnumber?
 733
 734 sub GetItems {
 735     my $itemnumber = shift or return;
 736     my $query = qq|SELECT *
 737              FROM items
 738               WHERE itemnumber=?|;
 739     my $sth = C4::Context->dbh->prepare($query);
 740     $sth->execute($itemnumber);
 741     my ($items) = $sth->fetchrow_hashref;
 742     return ($items);
 743 }
 744
 745 =head2 GetBranchcodesWithOverdueRules
 746
 747     my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
 748
 749 returns a list of branch codes for branches with overdue rules defined.
 750
 751 =cut
 752
 753 sub GetBranchcodesWithOverdueRules {
 754     my $dbh               = C4::Context->dbh;
 755     my $rqoverduebranches = $dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1 IS NOT NULL AND branchcode <> '' ORDER BY branchcode");
 756     $rqoverduebranches->execute;
 757     my @branches = map { shift @$_ } @{ $rqoverduebranches->fetchall_arrayref };
 758     if (!$branches[0]) {
 759        my $availbranches = C4::Branch::GetBranches();
 760        @branches = keys %$availbranches;
 761     }
 762     return @branches;
 763 }
 764
 765 =head2 CheckItemNotify
 766
 767 Sql request to check if the document has alreday been notified
 768 this function is not exported, only used with GetOverduesForBranch
 769
 770 =cut
 771
 772 sub CheckItemNotify {
 773     my ($notify_id,$notify_level,$itemnumber) = @_;
 774     my $dbh = C4::Context->dbh;
 775     my $sth = $dbh->prepare("
 776     SELECT COUNT(*)
 777      FROM notifys
 778     WHERE notify_id    = ?
 779      AND  notify_level = ?
 780      AND  itemnumber   = ? ");
 781     $sth->execute($notify_id,$notify_level,$itemnumber);
 782     my $notified = $sth->fetchrow;
 783     return ($notified);
 784 }
 785
 786 =head2 GetOverduesForBranch
 787
 788 Sql request for display all information for branchoverdues.pl
 789 2 possibilities : with or without location .
 790 display is filtered by branch
 791
 792 FIXME: This function should be renamed.
 793
 794 =cut
 795
 796 sub GetOverduesForBranch {
 797     my ( $branch, $location) = @_;
 798         my $itype_link =  (C4::Context->preference('item-level_itypes')) ?  " items.itype " :  " biblioitems.itemtype ";
 799     my $dbh = C4::Context->dbh;
 800     my $select = "
 801     SELECT
 802             borrowers.cardnumber,
 803             borrowers.borrowernumber,
 804             borrowers.surname,
 805             borrowers.firstname,
 806             borrowers.phone,
 807             borrowers.email,
 808                biblio.title,
 809                biblio.author,
 810                biblio.biblionumber,
 811                issues.date_due,
 812                issues.returndate,
 813                issues.branchcode,
 814              branches.branchname,
 815                 items.barcode,
 816                 items.homebranch,
 817                 items.itemcallnumber,
 818                 items.location,
 819                 items.itemnumber,
 820             itemtypes.description,
 821          accountlines.notify_id,
 822          accountlines.notify_level,
 823          accountlines.amountoutstanding
 824     FROM  accountlines
 825     LEFT JOIN issues      ON    issues.itemnumber     = accountlines.itemnumber
 826                           AND   issues.borrowernumber = accountlines.borrowernumber
 827     LEFT JOIN borrowers   ON borrowers.borrowernumber = accountlines.borrowernumber
 828     LEFT JOIN items       ON     items.itemnumber     = issues.itemnumber
 829     LEFT JOIN biblio      ON      biblio.biblionumber =  items.biblionumber
 830     LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
 831     LEFT JOIN itemtypes   ON itemtypes.itemtype       = $itype_link
 832     LEFT JOIN branches    ON  branches.branchcode     = issues.branchcode
 833     WHERE (accountlines.amountoutstanding  != '0.000000')
 834       AND (accountlines.accounttype         = 'FU'      )
 835       AND (issues.branchcode =  ?   )
 836       AND (issues.date_due  < NOW())
 837     ";
 838     my @getoverdues;
 839     my $i = 0;
 840     my $sth;
 841     if ($location) {
 842         $sth = $dbh->prepare("$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname");
 843         $sth->execute($branch, $location);
 844     } else {
 845         $sth = $dbh->prepare("$select ORDER BY borrowers.surname, borrowers.firstname");
 846         $sth->execute($branch);
 847     }
 848     while ( my $data = $sth->fetchrow_hashref ) {
 849     #check if the document has already been notified
 850         my $countnotify = CheckItemNotify($data->{'notify_id'}, $data->{'notify_level'}, $data->{'itemnumber'});
 851         if ($countnotify eq '0') {
 852             $getoverdues[$i] = $data;
 853             $i++;
 854         }
 855     }
 856     return (@getoverdues);
 857 }
 858
 859
 860 =head2 AddNotifyLine
 861
 862     &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
 863
 864 Create a line into notify, if the method is phone, the notification_send_date is implemented to
 865
 866 =cut
 867
 868 sub AddNotifyLine {
 869     my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
 870     my $dbh = C4::Context->dbh;
 871     if ( $method eq "phone" ) {
 872         my $sth = $dbh->prepare(
 873             "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
 874         VALUES (?,?,now(),now(),?,?,?)"
 875         );
 876         $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
 877             $notifyId );
 878     }
 879     else {
 880         my $sth = $dbh->prepare(
 881             "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
 882         VALUES (?,?,now(),?,?,?)"
 883         );
 884         $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
 885             $notifyId );
 886     }
 887     return 1;
 888 }
 889
 890 =head2 RemoveNotifyLine
 891
 892     &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
 893
 894 Cancel a notification
 895
 896 =cut
 897
 898 sub RemoveNotifyLine {
 899     my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
 900     my $dbh = C4::Context->dbh;
 901     my $sth = $dbh->prepare(
 902         "DELETE FROM notifys
 903             WHERE
 904             borrowernumber=?
 905             AND itemnumber=?
 906             AND notify_date=?"
 907     );
 908     $sth->execute( $borrowernumber, $itemnumber, $notify_date );
 909     return 1;
 910 }
 911
 912 =head2 GetOverdueMessageTransportTypes
 913
 914     my $message_transport_types = GetOverdueMessageTransportTypes( $branchcode, $categorycode, $letternumber);
 915
 916     return a arrayref with all message_transport_type for given branchcode, categorycode and letternumber(1,2 or 3)
 917
 918 =cut
 919
 920 sub GetOverdueMessageTransportTypes {
 921     my ( $branchcode, $categorycode, $letternumber ) = @_;
 922     return unless $categorycode and $letternumber;
 923     my $dbh = C4::Context->dbh;
 924     my $sth = $dbh->prepare("
 925         SELECT message_transport_type FROM overduerules_transport_types
 926         WHERE branchcode = ? AND categorycode = ? AND letternumber = ?
 927     ");
 928     $sth->execute( $branchcode, $categorycode, $letternumber );
 929     my @mtts;
 930     while ( my $mtt = $sth->fetchrow ) {
 931         push @mtts, $mtt;
 932     }
 933
 934     # Put 'print' in first if exists
 935     # It avoid to sent a print notice with an email or sms template is no email or sms is defined
 936     @mtts = uniq( 'print', @mtts )
 937         if grep {/^print$/} @mtts;
 938
 939     return \@mtts;
 940 }
 941
 942 1;
 943 __END__
 944
 945 =head1 AUTHOR
 946
 947 Koha Development Team <http://koha-community.org/>
 948
 949 =cut