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
  10 # under the terms of the GNU General Public License as published by
  11 # the Free Software Foundation; either version 3 of the License, or
  12 # (at your option) any later version.
  13 #
  14 # Koha is distributed in the hope that it will be useful, but
  15 # WITHOUT ANY WARRANTY; without even the implied warranty of
  16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17 # GNU General Public License for more details.
  18 #
  19 # You should have received a copy of the GNU General Public License
  20 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  21
  22 use Modern::Perl;
  23 use Date::Calc qw/Today Date_to_Days/;
  24 use Date::Manip qw/UnixDate/;
  25 use List::MoreUtils qw( uniq );
  26 use POSIX qw( floor ceil );
  27 use Locale::Currency::Format 1.28;
  28 use Carp;
  29
  30 use C4::Circulation;
  31 use C4::Context;
  32 use C4::Accounts;
  33 use C4::Log; # logaction
  34 use C4::Debug;
  35 use Koha::DateUtils;
  36 use Koha::Account::Lines;
  37 use Koha::Account::Offsets;
  38 use Koha::Libraries;
  39
  40 use vars qw(@ISA @EXPORT);
  41
  42 BEGIN {
  43     require Exporter;
  44     @ISA = qw(Exporter);
  45
  46     # subs to rename (and maybe merge some...)
  47     push @EXPORT, qw(
  48       &CalcFine
  49       &Getoverdues
  50       &checkoverdues
  51       &UpdateFine
  52       &GetFine
  53       &get_chargeable_units
  54       &GetOverduesForBranch
  55       &GetOverdueMessageTransportTypes
  56       &parse_overdues_letter
  57     );
  58
  59     # subs to remove
  60     push @EXPORT, qw(
  61       &BorType
  62     );
  63
  64     # check that an equivalent don't exist already before moving
  65
  66     # subs to move to Circulation.pm
  67     push @EXPORT, qw(
  68       &GetIssuesIteminfo
  69     );
  70 }
  71
  72 =head1 NAME
  73
  74 C4::Circulation::Fines - Koha module dealing with fines
  75
  76 =head1 SYNOPSIS
  77
  78   use C4::Overdues;
  79
  80 =head1 DESCRIPTION
  81
  82 This module contains several functions for dealing with fines for
  83 overdue items. It is primarily used by the 'misc/fines2.pl' script.
  84
  85 =head1 FUNCTIONS
  86
  87 =head2 Getoverdues
  88
  89   $overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } );
  90
  91 Returns the list of all overdue books, with their itemtype.
  92
  93 C<$overdues> is a reference-to-array. Each element is a
  94 reference-to-hash whose keys are the fields of the issues table in the
  95 Koha database.
  96
  97 =cut
  98
  99 #'
 100 sub Getoverdues {
 101     my $params = shift;
 102     my $dbh = C4::Context->dbh;
 103     my $statement;
 104     if ( C4::Context->preference('item-level_itypes') ) {
 105         $statement = "
 106    SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode, items.itemlost, items.replacementprice
 107      FROM issues
 108 LEFT JOIN items       USING (itemnumber)
 109     WHERE date_due < NOW()
 110 ";
 111     } else {
 112         $statement = "
 113    SELECT issues.*, biblioitems.itemtype, items.itype, items.homebranch, items.barcode, items.itemlost, replacementprice
 114      FROM issues
 115 LEFT JOIN items       USING (itemnumber)
 116 LEFT JOIN biblioitems USING (biblioitemnumber)
 117     WHERE date_due < NOW()
 118 ";
 119     }
 120
 121     my @bind_parameters;
 122     if ( exists $params->{'minimumdays'} and exists $params->{'maximumdays'} ) {
 123         $statement .= ' AND TO_DAYS( NOW() )-TO_DAYS( date_due ) BETWEEN ? and ? ';
 124         push @bind_parameters, $params->{'minimumdays'}, $params->{'maximumdays'};
 125     } elsif ( exists $params->{'minimumdays'} ) {
 126         $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) > ? ';
 127         push @bind_parameters, $params->{'minimumdays'};
 128     } elsif ( exists $params->{'maximumdays'} ) {
 129         $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? ';
 130         push @bind_parameters, $params->{'maximumdays'};
 131     }
 132     $statement .= 'ORDER BY borrowernumber';
 133     my $sth = $dbh->prepare( $statement );
 134     $sth->execute( @bind_parameters );
 135     return $sth->fetchall_arrayref({});
 136 }
 137
 138
 139 =head2 checkoverdues
 140
 141     ($count, $overdueitems) = checkoverdues($borrowernumber);
 142
 143 Returns a count and a list of overdueitems for a given borrowernumber
 144
 145 =cut
 146
 147 sub checkoverdues {
 148     my $borrowernumber = shift or return;
 149     my $sth = C4::Context->dbh->prepare(
 150         "SELECT biblio.*, items.*, issues.*,
 151                 biblioitems.volume,
 152                 biblioitems.number,
 153                 biblioitems.itemtype,
 154                 biblioitems.isbn,
 155                 biblioitems.issn,
 156                 biblioitems.publicationyear,
 157                 biblioitems.publishercode,
 158                 biblioitems.volumedate,
 159                 biblioitems.volumedesc,
 160                 biblioitems.collectiontitle,
 161                 biblioitems.collectionissn,
 162                 biblioitems.collectionvolume,
 163                 biblioitems.editionstatement,
 164                 biblioitems.editionresponsibility,
 165                 biblioitems.illus,
 166                 biblioitems.pages,
 167                 biblioitems.notes,
 168                 biblioitems.size,
 169                 biblioitems.place,
 170                 biblioitems.lccn,
 171                 biblioitems.url,
 172                 biblioitems.cn_source,
 173                 biblioitems.cn_class,
 174                 biblioitems.cn_item,
 175                 biblioitems.cn_suffix,
 176                 biblioitems.cn_sort,
 177                 biblioitems.totalissues
 178          FROM issues
 179          LEFT JOIN items       ON issues.itemnumber      = items.itemnumber
 180          LEFT JOIN biblio      ON items.biblionumber     = biblio.biblionumber
 181          LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 182             WHERE issues.borrowernumber  = ?
 183             AND   issues.date_due < NOW()"
 184     );
 185     $sth->execute($borrowernumber);
 186     my $results = $sth->fetchall_arrayref({});
 187     return ( scalar(@$results), $results);  # returning the count and the results is silly
 188 }
 189
 190 =head2 CalcFine
 191
 192     ($amount, $units_minus_grace, $chargeable_units) = &CalcFine($item,
 193                                   $categorycode, $branch,
 194                                   $start_dt, $end_dt );
 195
 196 Calculates the fine for a book.
 197
 198 The issuingrules table in the Koha database is a fine matrix, listing
 199 the penalties for each type of patron for each type of item and each branch (e.g., the
 200 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
 201 members might get a longer grace period between the first and second
 202 reminders that a book is overdue).
 203
 204
 205 C<$item> is an item object (hashref).
 206
 207 C<$categorycode> is the category code (string) of the patron who currently has
 208 the book.
 209
 210 C<$branchcode> is the library (string) whose issuingrules govern this transaction.
 211
 212 C<$start_date> & C<$end_date> are DateTime objects
 213 defining the date range over which to determine the fine.
 214
 215 Fines scripts should just supply the date range over which to calculate the fine.
 216
 217 C<&CalcFine> returns three values:
 218
 219 C<$amount> is the fine owed by the patron (see above).
 220
 221 C<$units_minus_grace> is the number of chargeable units minus the grace period
 222
 223 C<$chargeable_units> is the number of chargeable units (days between start and end dates, Calendar adjusted where needed,
 224 minus any applicable grace period, or hours)
 225
 226 FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice",
 227 or "Final Notice".  But CalcFine never defined any value.
 228
 229 =cut
 230
 231 sub CalcFine {
 232     my ( $item, $bortype, $branchcode, $due_dt, $end_date  ) = @_;
 233
 234     # Skip calculations if item is not overdue
 235     return ( 0, 0, 0 ) unless (DateTime->compare( $due_dt, $end_date ) == -1);
 236
 237     my $start_date = $due_dt->clone();
 238     # get issuingrules (fines part will be used)
 239     my $itemtype = $item->{itemtype} || $item->{itype};
 240     my $issuing_rule = Koha::CirculationRules->get_effective_rules(
 241         {
 242             categorycode => $bortype,
 243             itemtype     => $itemtype,
 244             branchcode   => $branchcode,
 245             rules => [
 246                 'lengthunit',
 247                 'firstremind',
 248                 'chargeperiod',
 249                 'chargeperiod_charge_at',
 250                 'fine',
 251                 'overduefinescap',
 252                 'cap_fine_to_replacement_price',
 253             ]
 254         }
 255     );
 256
 257     $itemtype = Koha::ItemTypes->find($itemtype);
 258
 259     return unless $issuing_rule; # If not rule exist, there is no fine
 260
 261     my $fine_unit = $issuing_rule->{lengthunit} || 'days';
 262
 263     my $chargeable_units = get_chargeable_units($fine_unit, $start_date, $end_date, $branchcode);
 264     my $units_minus_grace = $chargeable_units - ($issuing_rule->{firstremind} || 0);
 265     my $amount = 0;
 266     if ( $issuing_rule->{chargeperiod} && ( $units_minus_grace > 0 ) ) {
 267         my $units = C4::Context->preference('FinesIncludeGracePeriod') ? $chargeable_units : $units_minus_grace;
 268         my $charge_periods = $units / $issuing_rule->{chargeperiod};
 269         # If chargeperiod_charge_at = 1, we charge a fine at the start of each charge period
 270         # if chargeperiod_charge_at = 0, we charge at the end of each charge period
 271         $charge_periods = $issuing_rule->{chargeperiod_charge_at} == 1 ? ceil($charge_periods) : floor($charge_periods);
 272         $amount = $charge_periods * $issuing_rule->{fine};
 273     } # else { # a zero (or null) chargeperiod or negative units_minus_grace value means no charge. }
 274
 275     $amount = $issuing_rule->{overduefinescap} if $issuing_rule->{overduefinescap} && $amount > $issuing_rule->{overduefinescap};
 276
 277     # This must be moved to Koha::Item (see also similar code in C4::Accounts::chargelostitem
 278     $item->{replacementprice} ||= $itemtype->defaultreplacecost
 279       if $itemtype
 280       && ( ! defined $item->{replacementprice} || $item->{replacementprice} == 0 )
 281       && C4::Context->preference("useDefaultReplacementCost");
 282
 283     $amount = $item->{replacementprice} if ( $issuing_rule->{cap_fine_to_replacement_price} && $item->{replacementprice} && $amount > $item->{replacementprice} );
 284
 285     $debug and warn sprintf("CalcFine returning (%s, %s, %s)", $amount, $units_minus_grace, $chargeable_units);
 286     return ($amount, $units_minus_grace, $chargeable_units);
 287 }
 288
 289
 290 =head2 get_chargeable_units
 291
 292     get_chargeable_units($unit, $start_date_ $end_date, $branchcode);
 293
 294 return integer value of units between C<$start_date> and C<$end_date>, factoring in holidays for C<$branchcode>.
 295
 296 C<$unit> is 'days' or 'hours' (default is 'days').
 297
 298 C<$start_date> and C<$end_date> are the two DateTimes to get the number of units between.
 299
 300 C<$branchcode> is the branch whose calendar to use for finding holidays.
 301
 302 =cut
 303
 304 sub get_chargeable_units {
 305     my ($unit, $date_due, $date_returned, $branchcode) = @_;
 306
 307     # If the due date is later than the return date
 308     return 0 unless ( $date_returned > $date_due );
 309
 310     my $charge_units = 0;
 311     my $charge_duration;
 312     if ($unit eq 'hours') {
 313         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 314             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 315             $charge_duration = $calendar->hours_between( $date_due, $date_returned );
 316         } else {
 317             $charge_duration = $date_returned->delta_ms( $date_due );
 318         }
 319         if($charge_duration->in_units('hours') == 0 && $charge_duration->in_units('seconds') > 0){
 320             return 1;
 321         }
 322         return $charge_duration->in_units('hours');
 323     }
 324     else { # days
 325         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 326             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 327             $charge_duration = $calendar->days_between( $date_due, $date_returned );
 328         } else {
 329             $charge_duration = $date_returned->delta_days( $date_due );
 330         }
 331         return $charge_duration->in_units('days');
 332     }
 333 }
 334
 335
 336 =head2 GetSpecialHolidays
 337
 338     &GetSpecialHolidays($date_dues,$itemnumber);
 339
 340 return number of special days  between date of the day and date due
 341
 342 C<$date_dues> is the envisaged date of book return.
 343
 344 C<$itemnumber> is the book's item number.
 345
 346 =cut
 347
 348 sub GetSpecialHolidays {
 349     my ( $date_dues, $itemnumber ) = @_;
 350
 351     # calcul the today date
 352     my $today = join "-", &Today();
 353
 354     # return the holdingbranch
 355     my $iteminfo = GetIssuesIteminfo($itemnumber);
 356
 357     # use sql request to find all date between date_due and today
 358     my $dbh = C4::Context->dbh;
 359     my $query =
 360       qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date
 361 FROM `special_holidays`
 362 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
 363 AND   DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
 364 AND branchcode=?
 365 |;
 366     my @result = GetWdayFromItemnumber($itemnumber);
 367     my @result_date;
 368     my $wday;
 369     my $dateinsec;
 370     my $sth = $dbh->prepare($query);
 371     $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} )
 372       ;    # FIXME: just use NOW() in SQL instead of passing in $today
 373
 374     while ( my $special_date = $sth->fetchrow_hashref ) {
 375         push( @result_date, $special_date );
 376     }
 377
 378     my $specialdaycount = scalar(@result_date);
 379
 380     for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) {
 381         $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" );
 382         ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) =
 383           localtime($dateinsec);
 384         for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) {
 385             if ( $wday == ( $result[$j]->{'weekday'} ) ) {
 386                 $specialdaycount--;
 387             }
 388         }
 389     }
 390
 391     return $specialdaycount;
 392 }
 393
 394 =head2 GetRepeatableHolidays
 395
 396     &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
 397
 398 return number of day closed between date of the day and date due
 399
 400 C<$date_dues> is the envisaged date of book return.
 401
 402 C<$itemnumber> is item number.
 403
 404 C<$difference> numbers of between day date of the day and date due
 405
 406 =cut
 407
 408 sub GetRepeatableHolidays {
 409     my ( $date_dues, $itemnumber, $difference ) = @_;
 410     my $dateinsec = UnixDate( $date_dues, "%o" );
 411     my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
 412       localtime($dateinsec);
 413     my @result = GetWdayFromItemnumber($itemnumber);
 414     my @dayclosedcount;
 415     my $j;
 416
 417     for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) {
 418         my $k = $wday;
 419
 420         for ( $j = 0 ; $j < $difference ; $j++ ) {
 421             if ( $result[$i]->{'weekday'} == $k ) {
 422                 push( @dayclosedcount, $k );
 423             }
 424             $k++;
 425             ( $k = 0 ) if ( $k eq 7 );
 426         }
 427     }
 428     return scalar(@dayclosedcount);
 429 }
 430
 431
 432 =head2 GetWayFromItemnumber
 433
 434     &Getwdayfromitemnumber($itemnumber);
 435
 436 return the different week day from repeatable_holidays table
 437
 438 C<$itemnumber> is  item number.
 439
 440 =cut
 441
 442 sub GetWdayFromItemnumber {
 443     my ($itemnumber) = @_;
 444     my $iteminfo = GetIssuesIteminfo($itemnumber);
 445     my @result;
 446     my $query = qq|SELECT weekday
 447     FROM repeatable_holidays
 448     WHERE branchcode=?
 449 |;
 450     my $sth = C4::Context->dbh->prepare($query);
 451
 452     $sth->execute( $iteminfo->{'branchcode'} );
 453     while ( my $weekday = $sth->fetchrow_hashref ) {
 454         push( @result, $weekday );
 455     }
 456     return @result;
 457 }
 458
 459
 460 =head2 GetIssuesIteminfo
 461
 462     &GetIssuesIteminfo($itemnumber);
 463
 464 return all data from issues about item
 465
 466 C<$itemnumber> is  item number.
 467
 468 =cut
 469
 470 sub GetIssuesIteminfo {
 471     my ($itemnumber) = @_;
 472     my $dbh          = C4::Context->dbh;
 473     my $query        = qq|SELECT *
 474     FROM issues
 475     WHERE itemnumber=?
 476     |;
 477     my $sth = $dbh->prepare($query);
 478     $sth->execute($itemnumber);
 479     my ($issuesinfo) = $sth->fetchrow_hashref;
 480     return $issuesinfo;
 481 }
 482
 483
 484 =head2 UpdateFine
 485
 486     &UpdateFine(
 487         {
 488             issue_id       => $issue_id,
 489             itemnumber     => $itemnumber,
 490             borrowernumber => $borrowernumber,
 491             amount         => $amount,
 492             due            => $date_due
 493         }
 494     );
 495
 496 (Note: the following is mostly conjecture and guesswork.)
 497
 498 Updates the fine owed on an overdue book.
 499
 500 C<$itemnumber> is the book's item number.
 501
 502 C<$borrowernumber> is the borrower number of the patron who currently
 503 has the book on loan.
 504
 505 C<$amount> is the current amount owed by the patron.
 506
 507 C<$due> is the due date formatted to the currently specified date format
 508
 509 C<&UpdateFine> looks up the amount currently owed on the given item
 510 and sets it to C<$amount>, creating, if necessary, a new entry in the
 511 accountlines table of the Koha database.
 512
 513 =cut
 514
 515 #
 516 # Question: Why should the caller have to
 517 # specify both the item number and the borrower number? A book can't
 518 # be on loan to two different people, so the item number should be
 519 # sufficient.
 520 #
 521 # Possible Answer: You might update a fine for a damaged item, *after* it is returned.
 522 #
 523 sub UpdateFine {
 524     my ($params) = @_;
 525
 526     my $issue_id       = $params->{issue_id};
 527     my $itemnum        = $params->{itemnumber};
 528     my $borrowernumber = $params->{borrowernumber};
 529     my $amount         = $params->{amount};
 530     my $due            = $params->{due};
 531
 532     $debug and warn "UpdateFine({ itemnumber => $itemnum, borrowernumber => $borrowernumber, due => $due, issue_id => $issue_id})";
 533
 534     unless ( $issue_id ) {
 535         carp("No issue_id passed in!");
 536         return;
 537     }
 538
 539     my $dbh = C4::Context->dbh;
 540     my $overdues = Koha::Account::Lines->search(
 541         {
 542             borrowernumber    => $borrowernumber,
 543             debit_type_code   => 'OVERDUE'
 544         }
 545     );
 546
 547     my $accountline;
 548     my $total_amount_other = 0.00;
 549     my $due_qr = qr/$due/;
 550     # Cycle through the fines and
 551     # - find line that relates to the requested $itemnum
 552     # - accumulate fines for other items
 553     # so we can update $itemnum fine taking in account fine caps
 554     while (my $overdue = $overdues->next) {
 555         if ( $overdue->issue_id == $issue_id && $overdue->status eq 'UNRETURNED' ) {
 556             if ($accountline) {
 557                 $debug and warn "Not a unique accountlines record for issue_id $issue_id";
 558                 #FIXME Should we still count this one in total_amount ??
 559             }
 560             else {
 561                 $accountline = $overdue;
 562                 next;
 563             }
 564         }
 565         $total_amount_other += $overdue->amountoutstanding;
 566     }
 567
 568     if ( my $maxfine = C4::Context->preference('MaxFine') ) {
 569         my $maxIncrease = $maxfine - $total_amount_other;
 570         return if $maxIncrease <= 0.00;
 571         if ($accountline) {
 572             if ( ( $amount - $accountline->amount ) > $maxIncrease ) {
 573                 my $new_amount = $accountline->amount + $maxIncrease;
 574                 $debug and warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $new_amount - MaxFine reached";
 575                 $amount = $new_amount;
 576             }
 577         }
 578         elsif ( $amount > $maxIncrease ) {
 579             $debug and warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $maxIncrease - MaxFine reached";
 580             $amount = $maxIncrease;
 581         }
 582     }
 583
 584     if ( $accountline ) {
 585         if ( $accountline->amount != $amount ) {
 586             $accountline->adjust(
 587                 {
 588                     amount    => $amount,
 589                     type      => 'overdue_update',
 590                     interface => C4::Context->interface
 591                 }
 592             );
 593         }
 594     } else {
 595         if ( $amount ) { # Don't add new fines with an amount of 0
 596             my $sth4 = $dbh->prepare(
 597                 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
 598             );
 599             $sth4->execute($itemnum);
 600             my $title = $sth4->fetchrow;
 601             my $desc = "$title $due";
 602
 603             my $account = Koha::Account->new({ patron_id => $borrowernumber });
 604             $accountline = $account->add_debit(
 605                 {
 606                     amount      => $amount,
 607                     description => $desc,
 608                     note        => undef,
 609                     user_id     => undef,
 610                     interface   => C4::Context->interface,
 611                     library_id  => undef, #FIXME: Should we grab the checkout or circ-control branch here perhaps?
 612                     type        => 'OVERDUE',
 613                     item_id     => $itemnum,
 614                     issue_id    => $issue_id,
 615                 }
 616             );
 617         }
 618     }
 619 }
 620
 621 =head2 BorType
 622
 623     $borrower = &BorType($borrowernumber);
 624
 625 Looks up a patron by borrower number.
 626
 627 C<$borrower> is a reference-to-hash whose keys are all of the fields
 628 from the borrowers and categories tables of the Koha database. Thus,
 629 C<$borrower> contains all information about both the borrower and
 630 category they belong to.
 631
 632 =cut
 633
 634 sub BorType {
 635     my ($borrowernumber) = @_;
 636     my $dbh              = C4::Context->dbh;
 637     my $sth              = $dbh->prepare(
 638         "SELECT * from borrowers
 639       LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
 640       WHERE borrowernumber=?"
 641     );
 642     $sth->execute($borrowernumber);
 643     return $sth->fetchrow_hashref;
 644 }
 645
 646 =head2 GetFine
 647
 648     $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
 649
 650 return the total of fine
 651
 652 C<$itemnum> is item number
 653
 654 C<$borrowernumber> is the borrowernumber
 655
 656 =cut
 657
 658 sub GetFine {
 659     my ( $itemnum, $borrowernumber ) = @_;
 660     my $dbh   = C4::Context->dbh();
 661     my $query = q|SELECT sum(amountoutstanding) as fineamount FROM accountlines
 662     WHERE debit_type_code = 'OVERDUE'
 663   AND amountoutstanding > 0 AND borrowernumber=?|;
 664     my @query_param;
 665     push @query_param, $borrowernumber;
 666     if (defined $itemnum )
 667     {
 668         $query .= " AND itemnumber=?";
 669         push @query_param, $itemnum;
 670     }
 671     my $sth = $dbh->prepare($query);
 672     $sth->execute( @query_param );
 673     my $fine = $sth->fetchrow_hashref();
 674     if ($fine->{fineamount}) {
 675         return $fine->{fineamount};
 676     }
 677     return 0;
 678 }
 679
 680 =head2 GetBranchcodesWithOverdueRules
 681
 682     my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
 683
 684 returns a list of branch codes for branches with overdue rules defined.
 685
 686 =cut
 687
 688 sub GetBranchcodesWithOverdueRules {
 689     my $dbh               = C4::Context->dbh;
 690     my $branchcodes = $dbh->selectcol_arrayref(q|
 691         SELECT DISTINCT(branchcode)
 692         FROM overduerules
 693         WHERE delay1 IS NOT NULL
 694         ORDER BY branchcode
 695     |);
 696     if ( $branchcodes->[0] eq '' ) {
 697         # If a default rule exists, all branches should be returned
 698         return map { $_->branchcode } Koha::Libraries->search({}, { order_by => 'branchname' });
 699     }
 700     return @$branchcodes;
 701 }
 702
 703 =head2 GetOverduesForBranch
 704
 705 Sql request for display all information for branchoverdues.pl
 706 2 possibilities : with or without location .
 707 display is filtered by branch
 708
 709 FIXME: This function should be renamed.
 710
 711 =cut
 712
 713 sub GetOverduesForBranch {
 714     my ( $branch, $location) = @_;
 715         my $itype_link =  (C4::Context->preference('item-level_itypes')) ?  " items.itype " :  " biblioitems.itemtype ";
 716     my $dbh = C4::Context->dbh;
 717     my $select = "
 718     SELECT
 719             borrowers.cardnumber,
 720             borrowers.borrowernumber,
 721             borrowers.surname,
 722             borrowers.firstname,
 723             borrowers.phone,
 724             borrowers.email,
 725                biblio.title,
 726                biblio.subtitle,
 727                biblio.medium,
 728                biblio.part_number,
 729                biblio.part_name,
 730                biblio.author,
 731                biblio.biblionumber,
 732                issues.date_due,
 733                issues.returndate,
 734                issues.branchcode,
 735              branches.branchname,
 736                 items.barcode,
 737                 items.homebranch,
 738                 items.itemcallnumber,
 739                 items.location,
 740                 items.itemnumber,
 741             itemtypes.description,
 742          accountlines.amountoutstanding
 743     FROM  accountlines
 744     LEFT JOIN issues      ON    issues.itemnumber     = accountlines.itemnumber
 745                           AND   issues.borrowernumber = accountlines.borrowernumber
 746     LEFT JOIN borrowers   ON borrowers.borrowernumber = accountlines.borrowernumber
 747     LEFT JOIN items       ON     items.itemnumber     = issues.itemnumber
 748     LEFT JOIN biblio      ON      biblio.biblionumber =  items.biblionumber
 749     LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
 750     LEFT JOIN itemtypes   ON itemtypes.itemtype       = $itype_link
 751     LEFT JOIN branches    ON  branches.branchcode     = issues.branchcode
 752     WHERE (accountlines.amountoutstanding  != '0.000000')
 753       AND (accountlines.debit_type_code     = 'OVERDUE' )
 754       AND (accountlines.status              = 'UNRETURNED' )
 755       AND (issues.branchcode =  ?   )
 756       AND (issues.date_due  < NOW())
 757     ";
 758     if ($location) {
 759         my $q = "$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname";
 760         return @{ $dbh->selectall_arrayref($q, { Slice => {} }, $branch, $location ) };
 761     } else {
 762         my $q = "$select ORDER BY borrowers.surname, borrowers.firstname";
 763         return @{ $dbh->selectall_arrayref($q, { Slice => {} }, $branch ) };
 764     }
 765 }
 766
 767 =head2 GetOverdueMessageTransportTypes
 768
 769     my $message_transport_types = GetOverdueMessageTransportTypes( $branchcode, $categorycode, $letternumber);
 770
 771     return a arrayref with all message_transport_type for given branchcode, categorycode and letternumber(1,2 or 3)
 772
 773 =cut
 774
 775 sub GetOverdueMessageTransportTypes {
 776     my ( $branchcode, $categorycode, $letternumber ) = @_;
 777     return unless $categorycode and $letternumber;
 778     my $dbh = C4::Context->dbh;
 779     my $sth = $dbh->prepare("
 780         SELECT message_transport_type
 781         FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id)
 782         WHERE branchcode = ?
 783           AND categorycode = ?
 784           AND letternumber = ?
 785     ");
 786     $sth->execute( $branchcode, $categorycode, $letternumber );
 787     my @mtts;
 788     while ( my $mtt = $sth->fetchrow ) {
 789         push @mtts, $mtt;
 790     }
 791
 792     # Put 'print' in first if exists
 793     # It avoid to sent a print notice with an email or sms template is no email or sms is defined
 794     @mtts = uniq( 'print', @mtts )
 795         if grep {/^print$/} @mtts;
 796
 797     return \@mtts;
 798 }
 799
 800 =head2 parse_overdues_letter
 801
 802 parses the letter template, replacing the placeholders with data
 803 specific to this patron, biblio, or item for overdues
 804
 805 named parameters:
 806   letter - required hashref
 807   borrowernumber - required integer
 808   substitute - optional hashref of other key/value pairs that should
 809     be substituted in the letter content
 810
 811 returns the C<letter> hashref, with the content updated to reflect the
 812 substituted keys and values.
 813
 814 =cut
 815
 816 sub parse_overdues_letter {
 817     my $params = shift;
 818     foreach my $required (qw( letter_code borrowernumber )) {
 819         return unless ( exists $params->{$required} && $params->{$required} );
 820     }
 821
 822     my $patron = Koha::Patrons->find( $params->{borrowernumber} );
 823
 824     my $substitute = $params->{'substitute'} || {};
 825
 826     my %tables = ( 'borrowers' => $params->{'borrowernumber'} );
 827     if ( my $p = $params->{'branchcode'} ) {
 828         $tables{'branches'} = $p;
 829     }
 830
 831     my $active_currency = Koha::Acquisition::Currencies->get_active;
 832
 833     my $currency_format;
 834     $currency_format = $active_currency->currency if defined($active_currency);
 835
 836     my @item_tables;
 837     if ( my $i = $params->{'items'} ) {
 838         foreach my $item (@$i) {
 839             my $fine = GetFine($item->{'itemnumber'}, $params->{'borrowernumber'});
 840             $item->{'fine'} = currency_format($currency_format, "$fine", FMT_SYMBOL);
 841             # if active currency isn't correct ISO code fallback to sprintf
 842             $item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};
 843
 844             push @item_tables, {
 845                 'biblio' => $item->{'biblionumber'},
 846                 'biblioitems' => $item->{'biblionumber'},
 847                 'items' => $item,
 848                 'issues' => $item->{'itemnumber'},
 849             };
 850         }
 851     }
 852
 853     return C4::Letters::GetPreparedLetter (
 854         module => 'circulation',
 855         letter_code => $params->{'letter_code'},
 856         branchcode => $params->{'branchcode'},
 857         lang => $patron->lang,
 858         tables => \%tables,
 859         loops => {
 860             overdues => [ map { $_->{items}->{itemnumber} } @item_tables ],
 861         },
 862         substitute => $substitute,
 863         repeat => { item => \@item_tables },
 864         message_transport_type => $params->{message_transport_type},
 865     );
 866 }
 867
 868 1;
 869 __END__
 870
 871 =head1 AUTHOR
 872
 873 Koha Development Team <http://koha-community.org/>
 874
 875 =cut