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                 'chargename',
 254             ]
 255         }
 256     );
 257
 258     $itemtype = Koha::ItemTypes->find($itemtype);
 259
 260     return unless $issuing_rule; # If not rule exist, there is no fine
 261
 262     my $fine_unit = $issuing_rule->{lengthunit} || 'days';
 263
 264     my $chargeable_units = get_chargeable_units($fine_unit, $start_date, $end_date, $branchcode);
 265     my $units_minus_grace = $chargeable_units - ($issuing_rule->{firstremind} || 0);
 266     my $amount = 0;
 267     if ( $issuing_rule->{chargeperiod} && ( $units_minus_grace > 0 ) ) {
 268         my $units = C4::Context->preference('FinesIncludeGracePeriod') ? $chargeable_units : $units_minus_grace;
 269         my $charge_periods = $units / $issuing_rule->{chargeperiod};
 270         # If chargeperiod_charge_at = 1, we charge a fine at the start of each charge period
 271         # if chargeperiod_charge_at = 0, we charge at the end of each charge period
 272         $charge_periods = $issuing_rule->{chargeperiod_charge_at} == 1 ? ceil($charge_periods) : floor($charge_periods);
 273         $amount = $charge_periods * $issuing_rule->{fine};
 274     } # else { # a zero (or null) chargeperiod or negative units_minus_grace value means no charge. }
 275
 276     $amount = $issuing_rule->{overduefinescap} if $issuing_rule->{overduefinescap} && $amount > $issuing_rule->{overduefinescap};
 277
 278     # This must be moved to Koha::Item (see also similar code in C4::Accounts::chargelostitem
 279     $item->{replacementprice} ||= $itemtype->defaultreplacecost
 280       if $itemtype
 281       && ( ! defined $item->{replacementprice} || $item->{replacementprice} == 0 )
 282       && C4::Context->preference("useDefaultReplacementCost");
 283
 284     $amount = $item->{replacementprice} if ( $issuing_rule->{cap_fine_to_replacement_price} && $item->{replacementprice} && $amount > $item->{replacementprice} );
 285
 286     $debug and warn sprintf("CalcFine returning (%s, %s, %s)", $amount, $units_minus_grace, $chargeable_units);
 287     return ($amount, $units_minus_grace, $chargeable_units);
 288 }
 289
 290
 291 =head2 get_chargeable_units
 292
 293     get_chargeable_units($unit, $start_date_ $end_date, $branchcode);
 294
 295 return integer value of units between C<$start_date> and C<$end_date>, factoring in holidays for C<$branchcode>.
 296
 297 C<$unit> is 'days' or 'hours' (default is 'days').
 298
 299 C<$start_date> and C<$end_date> are the two DateTimes to get the number of units between.
 300
 301 C<$branchcode> is the branch whose calendar to use for finding holidays.
 302
 303 =cut
 304
 305 sub get_chargeable_units {
 306     my ($unit, $date_due, $date_returned, $branchcode) = @_;
 307
 308     # If the due date is later than the return date
 309     return 0 unless ( $date_returned > $date_due );
 310
 311     my $charge_units = 0;
 312     my $charge_duration;
 313     if ($unit eq 'hours') {
 314         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 315             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 316             $charge_duration = $calendar->hours_between( $date_due, $date_returned );
 317         } else {
 318             $charge_duration = $date_returned->delta_ms( $date_due );
 319         }
 320         if($charge_duration->in_units('hours') == 0 && $charge_duration->in_units('seconds') > 0){
 321             return 1;
 322         }
 323         return $charge_duration->in_units('hours');
 324     }
 325     else { # days
 326         if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 327             my $calendar = Koha::Calendar->new( branchcode => $branchcode );
 328             $charge_duration = $calendar->days_between( $date_due, $date_returned );
 329         } else {
 330             $charge_duration = $date_returned->delta_days( $date_due );
 331         }
 332         return $charge_duration->in_units('days');
 333     }
 334 }
 335
 336
 337 =head2 GetSpecialHolidays
 338
 339     &GetSpecialHolidays($date_dues,$itemnumber);
 340
 341 return number of special days  between date of the day and date due
 342
 343 C<$date_dues> is the envisaged date of book return.
 344
 345 C<$itemnumber> is the book's item number.
 346
 347 =cut
 348
 349 sub GetSpecialHolidays {
 350     my ( $date_dues, $itemnumber ) = @_;
 351
 352     # calcul the today date
 353     my $today = join "-", &Today();
 354
 355     # return the holdingbranch
 356     my $iteminfo = GetIssuesIteminfo($itemnumber);
 357
 358     # use sql request to find all date between date_due and today
 359     my $dbh = C4::Context->dbh;
 360     my $query =
 361       qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date
 362 FROM `special_holidays`
 363 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
 364 AND   DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
 365 AND branchcode=?
 366 |;
 367     my @result = GetWdayFromItemnumber($itemnumber);
 368     my @result_date;
 369     my $wday;
 370     my $dateinsec;
 371     my $sth = $dbh->prepare($query);
 372     $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} )
 373       ;    # FIXME: just use NOW() in SQL instead of passing in $today
 374
 375     while ( my $special_date = $sth->fetchrow_hashref ) {
 376         push( @result_date, $special_date );
 377     }
 378
 379     my $specialdaycount = scalar(@result_date);
 380
 381     for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) {
 382         $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" );
 383         ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) =
 384           localtime($dateinsec);
 385         for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) {
 386             if ( $wday == ( $result[$j]->{'weekday'} ) ) {
 387                 $specialdaycount--;
 388             }
 389         }
 390     }
 391
 392     return $specialdaycount;
 393 }
 394
 395 =head2 GetRepeatableHolidays
 396
 397     &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
 398
 399 return number of day closed between date of the day and date due
 400
 401 C<$date_dues> is the envisaged date of book return.
 402
 403 C<$itemnumber> is item number.
 404
 405 C<$difference> numbers of between day date of the day and date due
 406
 407 =cut
 408
 409 sub GetRepeatableHolidays {
 410     my ( $date_dues, $itemnumber, $difference ) = @_;
 411     my $dateinsec = UnixDate( $date_dues, "%o" );
 412     my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
 413       localtime($dateinsec);
 414     my @result = GetWdayFromItemnumber($itemnumber);
 415     my @dayclosedcount;
 416     my $j;
 417
 418     for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) {
 419         my $k = $wday;
 420
 421         for ( $j = 0 ; $j < $difference ; $j++ ) {
 422             if ( $result[$i]->{'weekday'} == $k ) {
 423                 push( @dayclosedcount, $k );
 424             }
 425             $k++;
 426             ( $k = 0 ) if ( $k eq 7 );
 427         }
 428     }
 429     return scalar(@dayclosedcount);
 430 }
 431
 432
 433 =head2 GetWayFromItemnumber
 434
 435     &Getwdayfromitemnumber($itemnumber);
 436
 437 return the different week day from repeatable_holidays table
 438
 439 C<$itemnumber> is  item number.
 440
 441 =cut
 442
 443 sub GetWdayFromItemnumber {
 444     my ($itemnumber) = @_;
 445     my $iteminfo = GetIssuesIteminfo($itemnumber);
 446     my @result;
 447     my $query = qq|SELECT weekday
 448     FROM repeatable_holidays
 449     WHERE branchcode=?
 450 |;
 451     my $sth = C4::Context->dbh->prepare($query);
 452
 453     $sth->execute( $iteminfo->{'branchcode'} );
 454     while ( my $weekday = $sth->fetchrow_hashref ) {
 455         push( @result, $weekday );
 456     }
 457     return @result;
 458 }
 459
 460
 461 =head2 GetIssuesIteminfo
 462
 463     &GetIssuesIteminfo($itemnumber);
 464
 465 return all data from issues about item
 466
 467 C<$itemnumber> is  item number.
 468
 469 =cut
 470
 471 sub GetIssuesIteminfo {
 472     my ($itemnumber) = @_;
 473     my $dbh          = C4::Context->dbh;
 474     my $query        = qq|SELECT *
 475     FROM issues
 476     WHERE itemnumber=?
 477     |;
 478     my $sth = $dbh->prepare($query);
 479     $sth->execute($itemnumber);
 480     my ($issuesinfo) = $sth->fetchrow_hashref;
 481     return $issuesinfo;
 482 }
 483
 484
 485 =head2 UpdateFine
 486
 487     &UpdateFine(
 488         {
 489             issue_id       => $issue_id,
 490             itemnumber     => $itemnumber,
 491             borrowernumber => $borrowernumber,
 492             amount         => $amount,
 493             due            => $date_due
 494         }
 495     );
 496
 497 (Note: the following is mostly conjecture and guesswork.)
 498
 499 Updates the fine owed on an overdue book.
 500
 501 C<$itemnumber> is the book's item number.
 502
 503 C<$borrowernumber> is the borrower number of the patron who currently
 504 has the book on loan.
 505
 506 C<$amount> is the current amount owed by the patron.
 507
 508 C<$due> is the due date formatted to the currently specified date format
 509
 510 C<&UpdateFine> looks up the amount currently owed on the given item
 511 and sets it to C<$amount>, creating, if necessary, a new entry in the
 512 accountlines table of the Koha database.
 513
 514 =cut
 515
 516 #
 517 # Question: Why should the caller have to
 518 # specify both the item number and the borrower number? A book can't
 519 # be on loan to two different people, so the item number should be
 520 # sufficient.
 521 #
 522 # Possible Answer: You might update a fine for a damaged item, *after* it is returned.
 523 #
 524 sub UpdateFine {
 525     my ($params) = @_;
 526
 527     my $issue_id       = $params->{issue_id};
 528     my $itemnum        = $params->{itemnumber};
 529     my $borrowernumber = $params->{borrowernumber};
 530     my $amount         = $params->{amount};
 531     my $due            = $params->{due};
 532
 533     $debug and warn "UpdateFine({ itemnumber => $itemnum, borrowernumber => $borrowernumber, due => $due, issue_id => $issue_id})";
 534
 535     unless ( $issue_id ) {
 536         carp("No issue_id passed in!");
 537         return;
 538     }
 539
 540     my $dbh = C4::Context->dbh;
 541     my $overdues = Koha::Account::Lines->search(
 542         {
 543             borrowernumber    => $borrowernumber,
 544             debit_type_code   => 'OVERDUE'
 545         }
 546     );
 547
 548     my $accountline;
 549     my $total_amount_other = 0.00;
 550     my $due_qr = qr/$due/;
 551     # Cycle through the fines and
 552     # - find line that relates to the requested $itemnum
 553     # - accumulate fines for other items
 554     # so we can update $itemnum fine taking in account fine caps
 555     while (my $overdue = $overdues->next) {
 556         if ( $overdue->issue_id == $issue_id && $overdue->status eq 'UNRETURNED' ) {
 557             if ($accountline) {
 558                 $debug and warn "Not a unique accountlines record for issue_id $issue_id";
 559                 #FIXME Should we still count this one in total_amount ??
 560             }
 561             else {
 562                 $accountline = $overdue;
 563                 next;
 564             }
 565         }
 566         $total_amount_other += $overdue->amountoutstanding;
 567     }
 568
 569     if ( my $maxfine = C4::Context->preference('MaxFine') ) {
 570         my $maxIncrease = $maxfine - $total_amount_other;
 571         return if $maxIncrease <= 0.00;
 572         if ($accountline) {
 573             if ( ( $amount - $accountline->amount ) > $maxIncrease ) {
 574                 my $new_amount = $accountline->amount + $maxIncrease;
 575                 $debug and warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $new_amount - MaxFine reached";
 576                 $amount = $new_amount;
 577             }
 578         }
 579         elsif ( $amount > $maxIncrease ) {
 580             $debug and warn "Reducing fine for item $itemnum borrower $borrowernumber from $amount to $maxIncrease - MaxFine reached";
 581             $amount = $maxIncrease;
 582         }
 583     }
 584
 585     if ( $accountline ) {
 586         if ( $accountline->amount != $amount ) {
 587             $accountline->adjust(
 588                 {
 589                     amount    => $amount,
 590                     type      => 'overdue_update',
 591                     interface => C4::Context->interface
 592                 }
 593             );
 594         }
 595     } else {
 596         if ( $amount ) { # Don't add new fines with an amount of 0
 597             my $sth4 = $dbh->prepare(
 598                 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
 599             );
 600             $sth4->execute($itemnum);
 601             my $title = $sth4->fetchrow;
 602             my $desc = "$title $due";
 603
 604             my $account = Koha::Account->new({ patron_id => $borrowernumber });
 605             $accountline = $account->add_debit(
 606                 {
 607                     amount      => $amount,
 608                     description => $desc,
 609                     note        => undef,
 610                     user_id     => undef,
 611                     interface   => C4::Context->interface,
 612                     library_id  => undef, #FIXME: Should we grab the checkout or circ-control branch here perhaps?
 613                     type        => 'OVERDUE',
 614                     item_id     => $itemnum,
 615                     issue_id    => $issue_id,
 616                 }
 617             );
 618         }
 619     }
 620 }
 621
 622 =head2 BorType
 623
 624     $borrower = &BorType($borrowernumber);
 625
 626 Looks up a patron by borrower number.
 627
 628 C<$borrower> is a reference-to-hash whose keys are all of the fields
 629 from the borrowers and categories tables of the Koha database. Thus,
 630 C<$borrower> contains all information about both the borrower and
 631 category they belong to.
 632
 633 =cut
 634
 635 sub BorType {
 636     my ($borrowernumber) = @_;
 637     my $dbh              = C4::Context->dbh;
 638     my $sth              = $dbh->prepare(
 639         "SELECT * from borrowers
 640       LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
 641       WHERE borrowernumber=?"
 642     );
 643     $sth->execute($borrowernumber);
 644     return $sth->fetchrow_hashref;
 645 }
 646
 647 =head2 GetFine
 648
 649     $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
 650
 651 return the total of fine
 652
 653 C<$itemnum> is item number
 654
 655 C<$borrowernumber> is the borrowernumber
 656
 657 =cut
 658
 659 sub GetFine {
 660     my ( $itemnum, $borrowernumber ) = @_;
 661     my $dbh   = C4::Context->dbh();
 662     my $query = q|SELECT sum(amountoutstanding) as fineamount FROM accountlines
 663     WHERE debit_type_code = 'OVERDUE'
 664   AND amountoutstanding > 0 AND borrowernumber=?|;
 665     my @query_param;
 666     push @query_param, $borrowernumber;
 667     if (defined $itemnum )
 668     {
 669         $query .= " AND itemnumber=?";
 670         push @query_param, $itemnum;
 671     }
 672     my $sth = $dbh->prepare($query);
 673     $sth->execute( @query_param );
 674     my $fine = $sth->fetchrow_hashref();
 675     if ($fine->{fineamount}) {
 676         return $fine->{fineamount};
 677     }
 678     return 0;
 679 }
 680
 681 =head2 GetBranchcodesWithOverdueRules
 682
 683     my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules()
 684
 685 returns a list of branch codes for branches with overdue rules defined.
 686
 687 =cut
 688
 689 sub GetBranchcodesWithOverdueRules {
 690     my $dbh               = C4::Context->dbh;
 691     my $branchcodes = $dbh->selectcol_arrayref(q|
 692         SELECT DISTINCT(branchcode)
 693         FROM overduerules
 694         WHERE delay1 IS NOT NULL
 695         ORDER BY branchcode
 696     |);
 697     if ( $branchcodes->[0] eq '' ) {
 698         # If a default rule exists, all branches should be returned
 699         return map { $_->branchcode } Koha::Libraries->search({}, { order_by => 'branchname' });
 700     }
 701     return @$branchcodes;
 702 }
 703
 704 =head2 GetOverduesForBranch
 705
 706 Sql request for display all information for branchoverdues.pl
 707 2 possibilities : with or without location .
 708 display is filtered by branch
 709
 710 FIXME: This function should be renamed.
 711
 712 =cut
 713
 714 sub GetOverduesForBranch {
 715     my ( $branch, $location) = @_;
 716         my $itype_link =  (C4::Context->preference('item-level_itypes')) ?  " items.itype " :  " biblioitems.itemtype ";
 717     my $dbh = C4::Context->dbh;
 718     my $select = "
 719     SELECT
 720             borrowers.cardnumber,
 721             borrowers.borrowernumber,
 722             borrowers.surname,
 723             borrowers.firstname,
 724             borrowers.phone,
 725             borrowers.email,
 726                biblio.title,
 727                biblio.subtitle,
 728                biblio.medium,
 729                biblio.part_number,
 730                biblio.part_name,
 731                biblio.author,
 732                biblio.biblionumber,
 733                issues.date_due,
 734                issues.returndate,
 735                issues.branchcode,
 736              branches.branchname,
 737                 items.barcode,
 738                 items.homebranch,
 739                 items.itemcallnumber,
 740                 items.location,
 741                 items.itemnumber,
 742             itemtypes.description,
 743          accountlines.amountoutstanding
 744     FROM  accountlines
 745     LEFT JOIN issues      ON    issues.itemnumber     = accountlines.itemnumber
 746                           AND   issues.borrowernumber = accountlines.borrowernumber
 747     LEFT JOIN borrowers   ON borrowers.borrowernumber = accountlines.borrowernumber
 748     LEFT JOIN items       ON     items.itemnumber     = issues.itemnumber
 749     LEFT JOIN biblio      ON      biblio.biblionumber =  items.biblionumber
 750     LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
 751     LEFT JOIN itemtypes   ON itemtypes.itemtype       = $itype_link
 752     LEFT JOIN branches    ON  branches.branchcode     = issues.branchcode
 753     WHERE (accountlines.amountoutstanding  != '0.000000')
 754       AND (accountlines.debit_type_code     = 'OVERDUE' )
 755       AND (accountlines.status              = 'UNRETURNED' )
 756       AND (issues.branchcode =  ?   )
 757       AND (issues.date_due  < NOW())
 758     ";
 759     if ($location) {
 760         my $q = "$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname";
 761         return @{ $dbh->selectall_arrayref($q, { Slice => {} }, $branch, $location ) };
 762     } else {
 763         my $q = "$select ORDER BY borrowers.surname, borrowers.firstname";
 764         return @{ $dbh->selectall_arrayref($q, { Slice => {} }, $branch ) };
 765     }
 766 }
 767
 768 =head2 GetOverdueMessageTransportTypes
 769
 770     my $message_transport_types = GetOverdueMessageTransportTypes( $branchcode, $categorycode, $letternumber);
 771
 772     return a arrayref with all message_transport_type for given branchcode, categorycode and letternumber(1,2 or 3)
 773
 774 =cut
 775
 776 sub GetOverdueMessageTransportTypes {
 777     my ( $branchcode, $categorycode, $letternumber ) = @_;
 778     return unless $categorycode and $letternumber;
 779     my $dbh = C4::Context->dbh;
 780     my $sth = $dbh->prepare("
 781         SELECT message_transport_type
 782         FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id)
 783         WHERE branchcode = ?
 784           AND categorycode = ?
 785           AND letternumber = ?
 786     ");
 787     $sth->execute( $branchcode, $categorycode, $letternumber );
 788     my @mtts;
 789     while ( my $mtt = $sth->fetchrow ) {
 790         push @mtts, $mtt;
 791     }
 792
 793     # Put 'print' in first if exists
 794     # It avoid to sent a print notice with an email or sms template is no email or sms is defined
 795     @mtts = uniq( 'print', @mtts )
 796         if grep {/^print$/} @mtts;
 797
 798     return \@mtts;
 799 }
 800
 801 =head2 parse_overdues_letter
 802
 803 parses the letter template, replacing the placeholders with data
 804 specific to this patron, biblio, or item for overdues
 805
 806 named parameters:
 807   letter - required hashref
 808   borrowernumber - required integer
 809   substitute - optional hashref of other key/value pairs that should
 810     be substituted in the letter content
 811
 812 returns the C<letter> hashref, with the content updated to reflect the
 813 substituted keys and values.
 814
 815 =cut
 816
 817 sub parse_overdues_letter {
 818     my $params = shift;
 819     foreach my $required (qw( letter_code borrowernumber )) {
 820         return unless ( exists $params->{$required} && $params->{$required} );
 821     }
 822
 823     my $patron = Koha::Patrons->find( $params->{borrowernumber} );
 824
 825     my $substitute = $params->{'substitute'} || {};
 826
 827     my %tables = ( 'borrowers' => $params->{'borrowernumber'} );
 828     if ( my $p = $params->{'branchcode'} ) {
 829         $tables{'branches'} = $p;
 830     }
 831
 832     my $active_currency = Koha::Acquisition::Currencies->get_active;
 833
 834     my $currency_format;
 835     $currency_format = $active_currency->currency if defined($active_currency);
 836
 837     my @item_tables;
 838     if ( my $i = $params->{'items'} ) {
 839         foreach my $item (@$i) {
 840             my $fine = GetFine($item->{'itemnumber'}, $params->{'borrowernumber'});
 841             $item->{'fine'} = currency_format($currency_format, "$fine", FMT_SYMBOL);
 842             # if active currency isn't correct ISO code fallback to sprintf
 843             $item->{'fine'} = sprintf('%.2f', $fine) unless $item->{'fine'};
 844
 845             push @item_tables, {
 846                 'biblio' => $item->{'biblionumber'},
 847                 'biblioitems' => $item->{'biblionumber'},
 848                 'items' => $item,
 849                 'issues' => $item->{'itemnumber'},
 850             };
 851         }
 852     }
 853
 854     return C4::Letters::GetPreparedLetter (
 855         module => 'circulation',
 856         letter_code => $params->{'letter_code'},
 857         branchcode => $params->{'branchcode'},
 858         lang => $patron->lang,
 859         tables => \%tables,
 860         loops => {
 861             overdues => [ map { $_->{items}->{itemnumber} } @item_tables ],
 862         },
 863         substitute => $substitute,
 864         repeat => { item => \@item_tables },
 865         message_transport_type => $params->{message_transport_type},
 866     );
 867 }
 868
 869 1;
 870 __END__
 871
 872 =head1 AUTHOR
 873
 874 Koha Development Team <http://koha-community.org/>
 875
 876 =cut