C4/Reserves.pm

   1 package C4::Reserves;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #           2006 SAN Ouest Provence
   5 #           2007-2010 BibLibre Paul POULAIN
   6 #           2011 Catalyst IT
   7 #
   8 # This file is part of Koha.
   9 #
  10 # Koha is free software; you can redistribute it and/or modify it
  11 # under the terms of the GNU General Public License as published by
  12 # the Free Software Foundation; either version 3 of the License, or
  13 # (at your option) any later version.
  14 #
  15 # Koha is distributed in the hope that it will be useful, but
  16 # WITHOUT ANY WARRANTY; without even the implied warranty of
  17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  18 # GNU General Public License for more details.
  19 #
  20 # You should have received a copy of the GNU General Public License
  21 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  22
  23
  24 use strict;
  25 #use warnings; FIXME - Bug 2505
  26 use C4::Context;
  27 use C4::Biblio;
  28 use C4::Members;
  29 use C4::Items;
  30 use C4::Circulation;
  31 use C4::Accounts;
  32
  33 # for _koha_notify_reserve
  34 use C4::Members::Messaging;
  35 use C4::Members qw();
  36 use C4::Letters;
  37 use C4::Log;
  38
  39 use Koha::DateUtils;
  40 use Koha::Calendar;
  41 use Koha::Database;
  42 use Koha::Hold;
  43 use Koha::Old::Hold;
  44 use Koha::Holds;
  45 use Koha::Libraries;
  46 use Koha::IssuingRules;
  47 use Koha::Items;
  48 use Koha::ItemTypes;
  49 use Koha::Patrons;
  50
  51 use List::MoreUtils qw( firstidx any );
  52 use Carp;
  53 use Data::Dumper;
  54
  55 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  56
  57 =head1 NAME
  58
  59 C4::Reserves - Koha functions for dealing with reservation.
  60
  61 =head1 SYNOPSIS
  62
  63   use C4::Reserves;
  64
  65 =head1 DESCRIPTION
  66
  67 This modules provides somes functions to deal with reservations.
  68
  69   Reserves are stored in reserves table.
  70   The following columns contains important values :
  71   - priority >0      : then the reserve is at 1st stage, and not yet affected to any item.
  72              =0      : then the reserve is being dealed
  73   - found : NULL       : means the patron requested the 1st available, and we haven't chosen the item
  74             T(ransit)  : the reserve is linked to an item but is in transit to the pickup branch
  75             W(aiting)  : the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf
  76             F(inished) : the reserve has been completed, and is done
  77   - itemnumber : empty : the reserve is still unaffected to an item
  78                  filled: the reserve is attached to an item
  79   The complete workflow is :
  80   ==== 1st use case ====
  81   patron request a document, 1st available :                      P >0, F=NULL, I=NULL
  82   a library having it run "transfertodo", and clic on the list
  83          if there is no transfer to do, the reserve waiting
  84          patron can pick it up                                    P =0, F=W,    I=filled
  85          if there is a transfer to do, write in branchtransfer    P =0, F=T,    I=filled
  86            The pickup library receive the book, it check in       P =0, F=W,    I=filled
  87   The patron borrow the book                                      P =0, F=F,    I=filled
  88
  89   ==== 2nd use case ====
  90   patron requests a document, a given item,
  91     If pickup is holding branch                                   P =0, F=W,   I=filled
  92     If transfer needed, write in branchtransfer                   P =0, F=T,    I=filled
  93         The pickup library receive the book, it checks it in      P =0, F=W,    I=filled
  94   The patron borrow the book                                      P =0, F=F,    I=filled
  95
  96 =head1 FUNCTIONS
  97
  98 =cut
  99
 100 BEGIN {
 101     require Exporter;
 102     @ISA = qw(Exporter);
 103     @EXPORT = qw(
 104         &AddReserve
 105
 106         &GetReserve
 107         &GetReservesFromBorrowernumber
 108         &GetReservesForBranch
 109         &GetReserveCount
 110         &GetReserveInfo
 111         &GetReserveStatus
 112
 113         &GetOtherReserves
 114
 115         &ModReserveFill
 116         &ModReserveAffect
 117         &ModReserve
 118         &ModReserveStatus
 119         &ModReserveCancelAll
 120         &ModReserveMinusPriority
 121         &MoveReserve
 122
 123         &CheckReserves
 124         &CanBookBeReserved
 125         &CanItemBeReserved
 126         &CanReserveBeCanceledFromOpac
 127         &CancelReserve
 128         &CancelExpiredReserves
 129
 130         &AutoUnsuspendReserves
 131
 132         &IsAvailableForItemLevelRequest
 133
 134         &OPACItemHoldsAllowed
 135
 136         &AlterPriority
 137         &ToggleLowestPriority
 138
 139         &ReserveSlip
 140         &ToggleSuspend
 141         &SuspendAll
 142
 143         &GetReservesControlBranch
 144
 145         IsItemOnHoldAndFound
 146
 147         GetMaxPatronHoldsForRecord
 148     );
 149     @EXPORT_OK = qw( MergeHolds );
 150 }
 151
 152 =head2 AddReserve
 153
 154     AddReserve($branch,$borrowernumber,$biblionumber,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
 155
 156 Adds reserve and generates HOLDPLACED message.
 157
 158 The following tables are available witin the HOLDPLACED message:
 159
 160     branches
 161     borrowers
 162     biblio
 163     biblioitems
 164     items
 165     reserves
 166
 167 =cut
 168
 169 sub AddReserve {
 170     my (
 171         $branch,   $borrowernumber, $biblionumber, $bibitems,
 172         $priority, $resdate,        $expdate,      $notes,
 173         $title,    $checkitem,      $found,        $itemtype
 174     ) = @_;
 175
 176     $resdate = output_pref( { str => dt_from_string( $resdate ), dateonly => 1, dateformat => 'iso' })
 177         or output_pref({ dt => dt_from_string, dateonly => 1, dateformat => 'iso' });
 178
 179     $expdate = output_pref({ str => $expdate, dateonly => 1, dateformat => 'iso' });
 180
 181     if ( C4::Context->preference('AllowHoldDateInFuture') ) {
 182
 183         # Make room in reserves for this before those of a later reserve date
 184         $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
 185     }
 186
 187     my $waitingdate;
 188
 189     # If the reserv had the waiting status, we had the value of the resdate
 190     if ( $found eq 'W' ) {
 191         $waitingdate = $resdate;
 192     }
 193
 194     # Don't add itemtype limit if specific item is selected
 195     $itemtype = undef if $checkitem;
 196
 197     # updates take place here
 198     my $hold = Koha::Hold->new(
 199         {
 200             borrowernumber => $borrowernumber,
 201             biblionumber   => $biblionumber,
 202             reservedate    => $resdate,
 203             branchcode     => $branch,
 204             priority       => $priority,
 205             reservenotes   => $notes,
 206             itemnumber     => $checkitem,
 207             found          => $found,
 208             waitingdate    => $waitingdate,
 209             expirationdate => $expdate,
 210             itemtype       => $itemtype,
 211         }
 212     )->store();
 213
 214     logaction( 'HOLDS', 'CREATE', $hold->id, Dumper($hold->unblessed) )
 215         if C4::Context->preference('HoldsLog');
 216
 217     my $reserve_id = $hold->id();
 218
 219     # add a reserve fee if needed
 220     if ( C4::Context->preference('HoldFeeMode') ne 'any_time_is_collected' ) {
 221         my $reserve_fee = GetReserveFee( $borrowernumber, $biblionumber );
 222         ChargeReserveFee( $borrowernumber, $reserve_fee, $title );
 223     }
 224
 225     _FixPriority({ biblionumber => $biblionumber});
 226
 227     # Send e-mail to librarian if syspref is active
 228     if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
 229         my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
 230         my $library = Koha::Libraries->find($borrower->{branchcode})->unblessed;
 231         if ( my $letter =  C4::Letters::GetPreparedLetter (
 232             module => 'reserves',
 233             letter_code => 'HOLDPLACED',
 234             branchcode => $branch,
 235             lang => $borrower->{lang},
 236             tables => {
 237                 'branches'    => $library,
 238                 'borrowers'   => $borrower,
 239                 'biblio'      => $biblionumber,
 240                 'biblioitems' => $biblionumber,
 241                 'items'       => $checkitem,
 242                 'reserves'    => $hold->unblessed,
 243             },
 244         ) ) {
 245
 246             my $admin_email_address = $library->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
 247
 248             C4::Letters::EnqueueLetter(
 249                 {   letter                 => $letter,
 250                     borrowernumber         => $borrowernumber,
 251                     message_transport_type => 'email',
 252                     from_address           => $admin_email_address,
 253                     to_address           => $admin_email_address,
 254                 }
 255             );
 256         }
 257     }
 258
 259     return $reserve_id;
 260 }
 261
 262 =head2 GetReserve
 263
 264     $res = GetReserve( $reserve_id );
 265
 266     Return the current reserve.
 267
 268 =cut
 269
 270 sub GetReserve {
 271     my ($reserve_id) = @_;
 272
 273     my $dbh = C4::Context->dbh;
 274
 275     my $query = "SELECT * FROM reserves WHERE reserve_id = ?";
 276     my $sth = $dbh->prepare( $query );
 277     $sth->execute( $reserve_id );
 278     return $sth->fetchrow_hashref();
 279 }
 280
 281 =head2 GetReservesFromBorrowernumber
 282
 283     $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
 284
 285 TODO :: Descritpion
 286
 287 =cut
 288
 289 sub GetReservesFromBorrowernumber {
 290     my ( $borrowernumber, $status ) = @_;
 291     my $dbh   = C4::Context->dbh;
 292     my $sth;
 293     if ($status) {
 294         $sth = $dbh->prepare("
 295             SELECT *
 296             FROM   reserves
 297             WHERE  borrowernumber=?
 298                 AND found =?
 299             ORDER BY reservedate
 300         ");
 301         $sth->execute($borrowernumber,$status);
 302     } else {
 303         $sth = $dbh->prepare("
 304             SELECT *
 305             FROM   reserves
 306             WHERE  borrowernumber=?
 307             ORDER BY reservedate
 308         ");
 309         $sth->execute($borrowernumber);
 310     }
 311     my $data = $sth->fetchall_arrayref({});
 312     return @$data;
 313 }
 314
 315 =head2 CanBookBeReserved
 316
 317   $canReserve = &CanBookBeReserved($borrowernumber, $biblionumber)
 318   if ($canReserve eq 'OK') { #We can reserve this Item! }
 319
 320 See CanItemBeReserved() for possible return values.
 321
 322 =cut
 323
 324 sub CanBookBeReserved{
 325     my ($borrowernumber, $biblionumber) = @_;
 326
 327     my $items = GetItemnumbersForBiblio($biblionumber);
 328     #get items linked via host records
 329     my @hostitems = get_hostitemnumbers_of($biblionumber);
 330     if (@hostitems){
 331     push (@$items,@hostitems);
 332     }
 333
 334     my $canReserve;
 335     foreach my $item (@$items) {
 336         $canReserve = CanItemBeReserved( $borrowernumber, $item );
 337         return 'OK' if $canReserve eq 'OK';
 338     }
 339     return $canReserve;
 340 }
 341
 342 =head2 CanItemBeReserved
 343
 344   $canReserve = &CanItemBeReserved($borrowernumber, $itemnumber)
 345   if ($canReserve eq 'OK') { #We can reserve this Item! }
 346
 347 @RETURNS OK,              if the Item can be reserved.
 348          ageRestricted,   if the Item is age restricted for this borrower.
 349          damaged,         if the Item is damaged.
 350          cannotReserveFromOtherBranches, if syspref 'canreservefromotherbranches' is OK.
 351          tooManyReserves, if the borrower has exceeded his maximum reserve amount.
 352          notReservable,   if holds on this item are not allowed
 353
 354 =cut
 355
 356 sub CanItemBeReserved {
 357     my ( $borrowernumber, $itemnumber ) = @_;
 358
 359     my $dbh = C4::Context->dbh;
 360     my $ruleitemtype;    # itemtype of the matching issuing rule
 361     my $allowedreserves  = 0; # Total number of holds allowed across all records
 362     my $holds_per_record = 1; # Total number of holds allowed for this one given record
 363
 364     # we retrieve borrowers and items informations #
 365     # item->{itype} will come for biblioitems if necessery
 366     my $item       = GetItem($itemnumber);
 367     my $biblioData = C4::Biblio::GetBiblioData( $item->{biblionumber} );
 368     my $borrower   = C4::Members::GetMember( 'borrowernumber' => $borrowernumber );
 369
 370     # If an item is damaged and we don't allow holds on damaged items, we can stop right here
 371     return 'damaged'
 372       if ( $item->{damaged}
 373         && !C4::Context->preference('AllowHoldsOnDamagedItems') );
 374
 375     # Check for the age restriction
 376     my ( $ageRestriction, $daysToAgeRestriction ) =
 377       C4::Circulation::GetAgeRestriction( $biblioData->{agerestriction}, $borrower );
 378     return 'ageRestricted' if $daysToAgeRestriction && $daysToAgeRestriction > 0;
 379
 380     # Check that the patron doesn't have an item level hold on this item already
 381     return 'itemAlreadyOnHold'
 382       if Koha::Holds->search( { borrowernumber => $borrowernumber, itemnumber => $itemnumber } )->count();
 383
 384     my $controlbranch = C4::Context->preference('ReservesControlBranch');
 385
 386     my $querycount = q{
 387         SELECT count(*) AS count
 388           FROM reserves
 389      LEFT JOIN items USING (itemnumber)
 390      LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
 391      LEFT JOIN borrowers USING (borrowernumber)
 392          WHERE borrowernumber = ?
 393     };
 394
 395     my $branchcode  = "";
 396     my $branchfield = "reserves.branchcode";
 397
 398     if ( $controlbranch eq "ItemHomeLibrary" ) {
 399         $branchfield = "items.homebranch";
 400         $branchcode  = $item->{homebranch};
 401     }
 402     elsif ( $controlbranch eq "PatronLibrary" ) {
 403         $branchfield = "borrowers.branchcode";
 404         $branchcode  = $borrower->{branchcode};
 405     }
 406
 407     # we retrieve rights
 408     if ( my $rights = GetHoldRule( $borrower->{'categorycode'}, $item->{'itype'}, $branchcode ) ) {
 409         $ruleitemtype     = $rights->{itemtype};
 410         $allowedreserves  = $rights->{reservesallowed};
 411         $holds_per_record = $rights->{holds_per_record};
 412     }
 413     else {
 414         $ruleitemtype = '*';
 415     }
 416
 417     $item = Koha::Items->find( $itemnumber );
 418     my $holds = Koha::Holds->search(
 419         {
 420             borrowernumber => $borrowernumber,
 421             biblionumber   => $item->biblionumber,
 422             found          => undef, # Found holds don't count against a patron's holds limit
 423         }
 424     );
 425     if ( $holds->count() >= $holds_per_record ) {
 426         return "tooManyHoldsForThisRecord";
 427     }
 428
 429     # we retrieve count
 430
 431     $querycount .= "AND $branchfield = ?";
 432
 433     # If using item-level itypes, fall back to the record
 434     # level itemtype if the hold has no associated item
 435     $querycount .=
 436       C4::Context->preference('item-level_itypes')
 437       ? " AND COALESCE( items.itype, biblioitems.itemtype ) = ?"
 438       : " AND biblioitems.itemtype = ?"
 439       if ( $ruleitemtype ne "*" );
 440
 441     my $sthcount = $dbh->prepare($querycount);
 442
 443     if ( $ruleitemtype eq "*" ) {
 444         $sthcount->execute( $borrowernumber, $branchcode );
 445     }
 446     else {
 447         $sthcount->execute( $borrowernumber, $branchcode, $ruleitemtype );
 448     }
 449
 450     my $reservecount = "0";
 451     if ( my $rowcount = $sthcount->fetchrow_hashref() ) {
 452         $reservecount = $rowcount->{count};
 453     }
 454
 455     # we check if it's ok or not
 456     if ( $reservecount >= $allowedreserves ) {
 457         return 'tooManyReserves';
 458     }
 459
 460     my $circ_control_branch =
 461       C4::Circulation::_GetCircControlBranch( $item->unblessed(), $borrower );
 462     my $branchitemrule =
 463       C4::Circulation::GetBranchItemRule( $circ_control_branch, $item->itype );
 464
 465     if ( $branchitemrule->{holdallowed} == 0 ) {
 466         return 'notReservable';
 467     }
 468
 469     if (   $branchitemrule->{holdallowed} == 1
 470         && $borrower->{branchcode} ne $item->homebranch )
 471     {
 472         return 'cannotReserveFromOtherBranches';
 473     }
 474
 475     # If reservecount is ok, we check item branch if IndependentBranches is ON
 476     # and canreservefromotherbranches is OFF
 477     if ( C4::Context->preference('IndependentBranches')
 478         and !C4::Context->preference('canreservefromotherbranches') )
 479     {
 480         my $itembranch = $item->homebranch;
 481         if ( $itembranch ne $borrower->{branchcode} ) {
 482             return 'cannotReserveFromOtherBranches';
 483         }
 484     }
 485
 486     return 'OK';
 487 }
 488
 489 =head2 CanReserveBeCanceledFromOpac
 490
 491     $number = CanReserveBeCanceledFromOpac($reserve_id, $borrowernumber);
 492
 493     returns 1 if reserve can be cancelled by user from OPAC.
 494     First check if reserve belongs to user, next checks if reserve is not in
 495     transfer or waiting status
 496
 497 =cut
 498
 499 sub CanReserveBeCanceledFromOpac {
 500     my ($reserve_id, $borrowernumber) = @_;
 501
 502     return unless $reserve_id and $borrowernumber;
 503     my $reserve = GetReserve($reserve_id);
 504
 505     return 0 unless $reserve->{borrowernumber} == $borrowernumber;
 506     return 0 if ( $reserve->{found} eq 'W' ) or ( $reserve->{found} eq 'T' );
 507
 508     return 1;
 509
 510 }
 511
 512 =head2 GetReserveCount
 513
 514   $number = &GetReserveCount($borrowernumber);
 515
 516 this function returns the number of reservation for a borrower given on input arg.
 517
 518 =cut
 519
 520 sub GetReserveCount {
 521     my ($borrowernumber) = @_;
 522
 523     my $dbh = C4::Context->dbh;
 524
 525     my $query = "
 526         SELECT COUNT(*) AS counter
 527         FROM reserves
 528         WHERE borrowernumber = ?
 529     ";
 530     my $sth = $dbh->prepare($query);
 531     $sth->execute($borrowernumber);
 532     my $row = $sth->fetchrow_hashref;
 533     return $row->{counter};
 534 }
 535
 536 =head2 GetOtherReserves
 537
 538   ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
 539
 540 Check queued list of this document and check if this document must be transferred
 541
 542 =cut
 543
 544 sub GetOtherReserves {
 545     my ($itemnumber) = @_;
 546     my $messages;
 547     my $nextreservinfo;
 548     my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
 549     if ($checkreserves) {
 550         my $iteminfo = GetItem($itemnumber);
 551         if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
 552             $messages->{'transfert'} = $checkreserves->{'branchcode'};
 553             #minus priorities of others reservs
 554             ModReserveMinusPriority(
 555                 $itemnumber,
 556                 $checkreserves->{'reserve_id'},
 557             );
 558
 559             #launch the subroutine dotransfer
 560             C4::Items::ModItemTransfer(
 561                 $itemnumber,
 562                 $iteminfo->{'holdingbranch'},
 563                 $checkreserves->{'branchcode'}
 564               ),
 565               ;
 566         }
 567
 568      #step 2b : case of a reservation on the same branch, set the waiting status
 569         else {
 570             $messages->{'waiting'} = 1;
 571             ModReserveMinusPriority(
 572                 $itemnumber,
 573                 $checkreserves->{'reserve_id'},
 574             );
 575             ModReserveStatus($itemnumber,'W');
 576         }
 577
 578         $nextreservinfo = $checkreserves->{'borrowernumber'};
 579     }
 580
 581     return ( $messages, $nextreservinfo );
 582 }
 583
 584 =head2 ChargeReserveFee
 585
 586     $fee = ChargeReserveFee( $borrowernumber, $fee, $title );
 587
 588     Charge the fee for a reserve (if $fee > 0)
 589
 590 =cut
 591
 592 sub ChargeReserveFee {
 593     my ( $borrowernumber, $fee, $title ) = @_;
 594     return if !$fee || $fee==0; # the last test is needed to include 0.00
 595     my $accquery = qq{
 596 INSERT INTO accountlines ( borrowernumber, accountno, date, amount, description, accounttype, amountoutstanding ) VALUES (?, ?, NOW(), ?, ?, 'Res', ?)
 597     };
 598     my $dbh = C4::Context->dbh;
 599     my $nextacctno = &getnextacctno( $borrowernumber );
 600     $dbh->do( $accquery, undef, ( $borrowernumber, $nextacctno, $fee, "Reserve Charge - $title", $fee ) );
 601 }
 602
 603 =head2 GetReserveFee
 604
 605     $fee = GetReserveFee( $borrowernumber, $biblionumber );
 606
 607     Calculate the fee for a reserve (if applicable).
 608
 609 =cut
 610
 611 sub GetReserveFee {
 612     my ( $borrowernumber, $biblionumber ) = @_;
 613     my $borquery = qq{
 614 SELECT reservefee FROM borrowers LEFT JOIN categories ON borrowers.categorycode = categories.categorycode WHERE borrowernumber = ?
 615     };
 616     my $issue_qry = qq{
 617 SELECT COUNT(*) FROM items
 618 LEFT JOIN issues USING (itemnumber)
 619 WHERE items.biblionumber=? AND issues.issue_id IS NULL
 620     };
 621     my $holds_qry = qq{
 622 SELECT COUNT(*) FROM reserves WHERE biblionumber=? AND borrowernumber<>?
 623     };
 624
 625     my $dbh = C4::Context->dbh;
 626     my ( $fee ) = $dbh->selectrow_array( $borquery, undef, ($borrowernumber) );
 627     my $hold_fee_mode = C4::Context->preference('HoldFeeMode') || 'not_always';
 628     if( $fee and $fee > 0 and $hold_fee_mode eq 'not_always' ) {
 629         # This is a reconstruction of the old code:
 630         # Compare number of items with items issued, and optionally check holds
 631         # If not all items are issued and there are no holds: charge no fee
 632         # NOTE: Lost, damaged, not-for-loan, etc. are just ignored here
 633         my ( $notissued, $reserved );
 634         ( $notissued ) = $dbh->selectrow_array( $issue_qry, undef,
 635             ( $biblionumber ) );
 636         if( $notissued ) {
 637             ( $reserved ) = $dbh->selectrow_array( $holds_qry, undef,
 638                 ( $biblionumber, $borrowernumber ) );
 639             $fee = 0 if $reserved == 0;
 640         }
 641     }
 642     return $fee;
 643 }
 644
 645 =head2 GetReservesForBranch
 646
 647   @transreserv = GetReservesForBranch($frombranch);
 648
 649 =cut
 650
 651 sub GetReservesForBranch {
 652     my ($frombranch) = @_;
 653     my $dbh = C4::Context->dbh;
 654
 655     my $query = "
 656         SELECT reserve_id,borrowernumber,reservedate,itemnumber,waitingdate, expirationdate
 657         FROM   reserves
 658         WHERE   priority='0'
 659         AND found='W'
 660     ";
 661     $query .= " AND branchcode=? " if ( $frombranch );
 662     $query .= "ORDER BY waitingdate" ;
 663
 664     my $sth = $dbh->prepare($query);
 665     if ($frombranch){
 666      $sth->execute($frombranch);
 667     } else {
 668         $sth->execute();
 669     }
 670
 671     my @transreserv;
 672     my $i = 0;
 673     while ( my $data = $sth->fetchrow_hashref ) {
 674         $transreserv[$i] = $data;
 675         $i++;
 676     }
 677     return (@transreserv);
 678 }
 679
 680 =head2 GetReserveStatus
 681
 682   $reservestatus = GetReserveStatus($itemnumber);
 683
 684 Takes an itemnumber and returns the status of the reserve placed on it.
 685 If several reserves exist, the reserve with the lower priority is given.
 686
 687 =cut
 688
 689 ## FIXME: I don't think this does what it thinks it does.
 690 ## It only ever checks the first reserve result, even though
 691 ## multiple reserves for that bib can have the itemnumber set
 692 ## the sub is only used once in the codebase.
 693 sub GetReserveStatus {
 694     my ($itemnumber) = @_;
 695
 696     my $dbh = C4::Context->dbh;
 697
 698     my ($sth, $found, $priority);
 699     if ( $itemnumber ) {
 700         $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE itemnumber = ? order by priority LIMIT 1");
 701         $sth->execute($itemnumber);
 702         ($found, $priority) = $sth->fetchrow_array;
 703     }
 704
 705     if(defined $found) {
 706         return 'Waiting'  if $found eq 'W' and $priority == 0;
 707         return 'Finished' if $found eq 'F';
 708     }
 709
 710     return 'Reserved' if $priority > 0;
 711
 712     return ''; # empty string here will remove need for checking undef, or less log lines
 713 }
 714
 715 =head2 CheckReserves
 716
 717   ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
 718   ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);
 719   ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber,undef,$lookahead);
 720
 721 Find a book in the reserves.
 722
 723 C<$itemnumber> is the book's item number.
 724 C<$lookahead> is the number of days to look in advance for future reserves.
 725
 726 As I understand it, C<&CheckReserves> looks for the given item in the
 727 reserves. If it is found, that's a match, and C<$status> is set to
 728 C<Waiting>.
 729
 730 Otherwise, it finds the most important item in the reserves with the
 731 same biblio number as this book (I'm not clear on this) and returns it
 732 with C<$status> set to C<Reserved>.
 733
 734 C<&CheckReserves> returns a two-element list:
 735
 736 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
 737
 738 C<$reserve> is the reserve item that matched. It is a
 739 reference-to-hash whose keys are mostly the fields of the reserves
 740 table in the Koha database.
 741
 742 =cut
 743
 744 sub CheckReserves {
 745     my ( $item, $barcode, $lookahead_days, $ignore_borrowers) = @_;
 746     my $dbh = C4::Context->dbh;
 747     my $sth;
 748     my $select;
 749     if (C4::Context->preference('item-level_itypes')){
 750         $select = "
 751            SELECT items.biblionumber,
 752            items.biblioitemnumber,
 753            itemtypes.notforloan,
 754            items.notforloan AS itemnotforloan,
 755            items.itemnumber,
 756            items.damaged,
 757            items.homebranch,
 758            items.holdingbranch
 759            FROM   items
 760            LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 761            LEFT JOIN itemtypes   ON items.itype   = itemtypes.itemtype
 762         ";
 763     }
 764     else {
 765         $select = "
 766            SELECT items.biblionumber,
 767            items.biblioitemnumber,
 768            itemtypes.notforloan,
 769            items.notforloan AS itemnotforloan,
 770            items.itemnumber,
 771            items.damaged,
 772            items.homebranch,
 773            items.holdingbranch
 774            FROM   items
 775            LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 776            LEFT JOIN itemtypes   ON biblioitems.itemtype   = itemtypes.itemtype
 777         ";
 778     }
 779
 780     if ($item) {
 781         $sth = $dbh->prepare("$select WHERE itemnumber = ?");
 782         $sth->execute($item);
 783     }
 784     else {
 785         $sth = $dbh->prepare("$select WHERE barcode = ?");
 786         $sth->execute($barcode);
 787     }
 788     # note: we get the itemnumber because we might have started w/ just the barcode.  Now we know for sure we have it.
 789     my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber, $damaged, $item_homebranch, $item_holdingbranch ) = $sth->fetchrow_array;
 790
 791     return if ( $damaged && !C4::Context->preference('AllowHoldsOnDamagedItems') );
 792
 793     return unless $itemnumber; # bail if we got nothing.
 794
 795     # if item is not for loan it cannot be reserved either.....
 796     # except where items.notforloan < 0 :  This indicates the item is holdable.
 797     return if  ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
 798
 799     # Find this item in the reserves
 800     my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber, $lookahead_days, $ignore_borrowers);
 801
 802     # $priority and $highest are used to find the most important item
 803     # in the list returned by &_Findgroupreserve. (The lower $priority,
 804     # the more important the item.)
 805     # $highest is the most important item we've seen so far.
 806     my $highest;
 807     if (scalar @reserves) {
 808         my $LocalHoldsPriority = C4::Context->preference('LocalHoldsPriority');
 809         my $LocalHoldsPriorityPatronControl = C4::Context->preference('LocalHoldsPriorityPatronControl');
 810         my $LocalHoldsPriorityItemControl = C4::Context->preference('LocalHoldsPriorityItemControl');
 811
 812         my $priority = 10000000;
 813         foreach my $res (@reserves) {
 814             if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
 815                 if ($res->{'found'} eq 'W') {
 816                     return ( "Waiting", $res, \@reserves ); # Found it, it is waiting
 817                 } else {
 818                     return ( "Reserved", $res, \@reserves ); # Found determinated hold, e. g. the tranferred one
 819                 }
 820             } else {
 821                 my $borrowerinfo;
 822                 my $iteminfo;
 823                 my $local_hold_match;
 824
 825                 if ($LocalHoldsPriority) {
 826                     $borrowerinfo = C4::Members::GetMember( borrowernumber => $res->{'borrowernumber'} );
 827                     $iteminfo = C4::Items::GetItem($itemnumber);
 828
 829                     my $local_holds_priority_item_branchcode =
 830                       $iteminfo->{$LocalHoldsPriorityItemControl};
 831                     my $local_holds_priority_patron_branchcode =
 832                       ( $LocalHoldsPriorityPatronControl eq 'PickupLibrary' )
 833                       ? $res->{branchcode}
 834                       : ( $LocalHoldsPriorityPatronControl eq 'HomeLibrary' )
 835                       ? $borrowerinfo->{branchcode}
 836                       : undef;
 837                     $local_hold_match =
 838                       $local_holds_priority_item_branchcode eq
 839                       $local_holds_priority_patron_branchcode;
 840                 }
 841
 842                 # See if this item is more important than what we've got so far
 843                 if ( ( $res->{'priority'} && $res->{'priority'} < $priority ) || $local_hold_match ) {
 844                     $iteminfo ||= C4::Items::GetItem($itemnumber);
 845                     next if $res->{itemtype} && $res->{itemtype} ne _get_itype( $iteminfo );
 846                     $borrowerinfo ||= C4::Members::GetMember( borrowernumber => $res->{'borrowernumber'} );
 847                     my $branch = GetReservesControlBranch( $iteminfo, $borrowerinfo );
 848                     my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
 849                     next if ($branchitemrule->{'holdallowed'} == 0);
 850                     next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
 851                     next if ( ($branchitemrule->{hold_fulfillment_policy} ne 'any') && ($res->{branchcode} ne $iteminfo->{ $branchitemrule->{hold_fulfillment_policy} }) );
 852                     $priority = $res->{'priority'};
 853                     $highest  = $res;
 854                     last if $local_hold_match;
 855                 }
 856             }
 857         }
 858     }
 859
 860     # If we get this far, then no exact match was found.
 861     # We return the most important (i.e. next) reservation.
 862     if ($highest) {
 863         $highest->{'itemnumber'} = $item;
 864         return ( "Reserved", $highest, \@reserves );
 865     }
 866
 867     return ( '' );
 868 }
 869
 870 =head2 CancelExpiredReserves
 871
 872   CancelExpiredReserves();
 873
 874 Cancels all reserves with an expiration date from before today.
 875
 876 =cut
 877
 878 sub CancelExpiredReserves {
 879     my $today = dt_from_string();
 880     my $cancel_on_holidays = C4::Context->preference('ExpireReservesOnHolidays');
 881     my $expireWaiting = C4::Context->preference('ExpireReservesMaxPickUpDelay');
 882
 883     my $dtf = Koha::Database->new->schema->storage->datetime_parser;
 884     my $params = { expirationdate => { '<', $dtf->format_date($today) } };
 885     $params->{found} = undef unless $expireWaiting;
 886
 887     # FIXME To move to Koha::Holds->search_expired (?)
 888     my $holds = Koha::Holds->search( $params );
 889
 890     while ( my $hold = $holds->next ) {
 891         my $calendar = Koha::Calendar->new( branchcode => $hold->branchcode );
 892         next if !$cancel_on_holidays && $calendar->is_holiday( $today );
 893
 894         my $cancel_params = { reserve_id => $hold->reserve_id };
 895         if ( $hold->found eq 'W' ) {
 896             $cancel_params->{charge_cancel_fee} = 1;
 897         }
 898
 899         CancelReserve($cancel_params);
 900     }
 901 }
 902
 903 =head2 AutoUnsuspendReserves
 904
 905   AutoUnsuspendReserves();
 906
 907 Unsuspends all suspended reserves with a suspend_until date from before today.
 908
 909 =cut
 910
 911 sub AutoUnsuspendReserves {
 912     my $today = dt_from_string();
 913
 914     my @holds = Koha::Holds->search( { suspend_until => { '<' => $today->ymd() } } );
 915
 916     map { $_->suspend(0)->suspend_until(undef)->store() } @holds;
 917 }
 918
 919 =head2 CancelReserve
 920
 921   CancelReserve({ reserve_id => $reserve_id, [ biblionumber => $biblionumber, borrowernumber => $borrrowernumber, itemnumber => $itemnumber, ] [ charge_cancel_fee => 1 ] });
 922
 923 Cancels a reserve. If C<charge_cancel_fee> is passed and the C<ExpireReservesMaxPickUpDelayCharge> syspref is set, charge that fee to the patron's account.
 924
 925 =cut
 926
 927 sub CancelReserve {
 928     my ( $params ) = @_;
 929
 930     my $reserve_id = $params->{'reserve_id'};
 931     # Filter out only the desired keys; this will insert undefined values for elements missing in
 932     # \%params, but GetReserveId filters them out anyway.
 933     $reserve_id = GetReserveId( { biblionumber => $params->{'biblionumber'}, borrowernumber => $params->{'borrowernumber'}, itemnumber => $params->{'itemnumber'} } ) unless ( $reserve_id );
 934
 935     return unless ( $reserve_id );
 936
 937     my $dbh = C4::Context->dbh;
 938
 939     my $reserve = GetReserve( $reserve_id );
 940     if ($reserve) {
 941
 942         my $hold = Koha::Holds->find( $reserve_id );
 943         logaction( 'HOLDS', 'CANCEL', $hold->reserve_id, Dumper($hold->unblessed) )
 944             if C4::Context->preference('HoldsLog');
 945
 946         my $query = "
 947             UPDATE reserves
 948             SET    cancellationdate = now(),
 949                    priority         = 0
 950             WHERE  reserve_id = ?
 951         ";
 952         my $sth = $dbh->prepare($query);
 953         $sth->execute( $reserve_id );
 954
 955         $query = "
 956             INSERT INTO old_reserves
 957             SELECT * FROM reserves
 958             WHERE  reserve_id = ?
 959         ";
 960         $sth = $dbh->prepare($query);
 961         $sth->execute( $reserve_id );
 962
 963         $query = "
 964             DELETE FROM reserves
 965             WHERE  reserve_id = ?
 966         ";
 967         $sth = $dbh->prepare($query);
 968         $sth->execute( $reserve_id );
 969
 970         # now fix the priority on the others....
 971         _FixPriority({ biblionumber => $reserve->{biblionumber} });
 972
 973         # and, if desired, charge a cancel fee
 974         my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
 975         if ( $charge && $params->{'charge_cancel_fee'} ) {
 976             manualinvoice($reserve->{'borrowernumber'}, $reserve->{'itemnumber'}, '', 'HE', $charge);
 977         }
 978     }
 979
 980     return $reserve;
 981 }
 982
 983 =head2 ModReserve
 984
 985   ModReserve({ rank => $rank,
 986                reserve_id => $reserve_id,
 987                branchcode => $branchcode
 988                [, itemnumber => $itemnumber ]
 989                [, biblionumber => $biblionumber, $borrowernumber => $borrowernumber ]
 990               });
 991
 992 Change a hold request's priority or cancel it.
 993
 994 C<$rank> specifies the effect of the change.  If C<$rank>
 995 is 'W' or 'n', nothing happens.  This corresponds to leaving a
 996 request alone when changing its priority in the holds queue
 997 for a bib.
 998
 999 If C<$rank> is 'del', the hold request is cancelled.
1000
1001 If C<$rank> is an integer greater than zero, the priority of
1002 the request is set to that value.  Since priority != 0 means
1003 that the item is not waiting on the hold shelf, setting the
1004 priority to a non-zero value also sets the request's found
1005 status and waiting date to NULL.
1006
1007 The optional C<$itemnumber> parameter is used only when
1008 C<$rank> is a non-zero integer; if supplied, the itemnumber
1009 of the hold request is set accordingly; if omitted, the itemnumber
1010 is cleared.
1011
1012 B<FIXME:> Note that the forgoing can have the effect of causing
1013 item-level hold requests to turn into title-level requests.  This
1014 will be fixed once reserves has separate columns for requested
1015 itemnumber and supplying itemnumber.
1016
1017 =cut
1018
1019 sub ModReserve {
1020     my ( $params ) = @_;
1021
1022     my $rank = $params->{'rank'};
1023     my $reserve_id = $params->{'reserve_id'};
1024     my $branchcode = $params->{'branchcode'};
1025     my $itemnumber = $params->{'itemnumber'};
1026     my $suspend_until = $params->{'suspend_until'};
1027     my $borrowernumber = $params->{'borrowernumber'};
1028     my $biblionumber = $params->{'biblionumber'};
1029
1030     return if $rank eq "W";
1031     return if $rank eq "n";
1032
1033     return unless ( $reserve_id || ( $borrowernumber && ( $biblionumber || $itemnumber ) ) );
1034     $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber, itemnumber => $itemnumber }) unless ( $reserve_id );
1035
1036     if ( $rank eq "del" ) {
1037         CancelReserve({ reserve_id => $reserve_id });
1038     }
1039     elsif ($rank =~ /^\d+/ and $rank > 0) {
1040         my $hold = Koha::Holds->find($reserve_id);
1041         logaction( 'HOLDS', 'MODIFY', $hold->reserve_id, Dumper($hold->unblessed) )
1042             if C4::Context->preference('HoldsLog');
1043
1044         $hold->set(
1045             {
1046                 priority    => $rank,
1047                 branchcode  => $branchcode,
1048                 itemnumber  => $itemnumber,
1049                 found       => undef,
1050                 waitingdate => undef
1051             }
1052         )->store();
1053
1054         if ( defined( $suspend_until ) ) {
1055             if ( $suspend_until ) {
1056                 $suspend_until = eval { dt_from_string( $suspend_until ) };
1057                 $hold->suspend_hold( $suspend_until );
1058             } else {
1059                 # If the hold is suspended leave the hold suspended, but convert it to an indefinite hold.
1060                 # If the hold is not suspended, this does nothing.
1061                 $hold->set( { suspend_until => undef } )->store();
1062             }
1063         }
1064
1065         _FixPriority({ reserve_id => $reserve_id, rank =>$rank });
1066     }
1067 }
1068
1069 =head2 ModReserveFill
1070
1071   &ModReserveFill($reserve);
1072
1073 Fill a reserve. If I understand this correctly, this means that the
1074 reserved book has been found and given to the patron who reserved it.
1075
1076 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1077 whose keys are fields from the reserves table in the Koha database.
1078
1079 =cut
1080
1081 sub ModReserveFill {
1082     my ($res) = @_;
1083     my $reserve_id = $res->{'reserve_id'};
1084
1085     my $hold = Koha::Holds->find($reserve_id);
1086
1087     # get the priority on this record....
1088     my $priority = $hold->priority;
1089
1090     # update the hold statuses, no need to store it though, we will be deleting it anyway
1091     $hold->set(
1092         {
1093             found    => 'F',
1094             priority => 0,
1095         }
1096     );
1097
1098     Koha::Old::Hold->new( $hold->unblessed() )->store();
1099
1100     $hold->delete();
1101
1102     if ( C4::Context->preference('HoldFeeMode') eq 'any_time_is_collected' ) {
1103         my $reserve_fee = GetReserveFee( $hold->borrowernumber, $hold->biblionumber );
1104         ChargeReserveFee( $hold->borrowernumber, $reserve_fee, $hold->biblio->title );
1105     }
1106
1107     # now fix the priority on the others (if the priority wasn't
1108     # already sorted!)....
1109     unless ( $priority == 0 ) {
1110         _FixPriority( { reserve_id => $reserve_id, biblionumber => $hold->biblionumber } );
1111     }
1112 }
1113
1114 =head2 ModReserveStatus
1115
1116   &ModReserveStatus($itemnumber, $newstatus);
1117
1118 Update the reserve status for the active (priority=0) reserve.
1119
1120 $itemnumber is the itemnumber the reserve is on
1121
1122 $newstatus is the new status.
1123
1124 =cut
1125
1126 sub ModReserveStatus {
1127
1128     #first : check if we have a reservation for this item .
1129     my ($itemnumber, $newstatus) = @_;
1130     my $dbh = C4::Context->dbh;
1131
1132     my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1133     my $sth_set = $dbh->prepare($query);
1134     $sth_set->execute( $newstatus, $itemnumber );
1135
1136     if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1137       CartToShelf( $itemnumber );
1138     }
1139 }
1140
1141 =head2 ModReserveAffect
1142
1143   &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend,$reserve_id);
1144
1145 This function affect an item and a status for a given reserve, either fetched directly
1146 by record_id, or by borrowernumber and itemnumber or biblionumber. If only biblionumber
1147 is given, only first reserve returned is affected, which is ok for anything but
1148 multi-item holds.
1149
1150 if $transferToDo is not set, then the status is set to "Waiting" as well.
1151 otherwise, a transfer is on the way, and the end of the transfer will
1152 take care of the waiting status
1153
1154 =cut
1155
1156 sub ModReserveAffect {
1157     my ( $itemnumber, $borrowernumber, $transferToDo, $reserve_id ) = @_;
1158     my $dbh = C4::Context->dbh;
1159
1160     # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1161     # attached to $itemnumber
1162     my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1163     $sth->execute($itemnumber);
1164     my ($biblionumber) = $sth->fetchrow;
1165
1166     # get request - need to find out if item is already
1167     # waiting in order to not send duplicate hold filled notifications
1168
1169     my $hold;
1170     # Find hold by id if we have it
1171     $hold = Koha::Holds->find( $reserve_id ) if $reserve_id;
1172     # Find item level hold for this item if there is one
1173     $hold ||= Koha::Holds->search( { borrowernumber => $borrowernumber, itemnumber => $itemnumber } )->next();
1174     # Find record level hold if there is no item level hold
1175     $hold ||= Koha::Holds->search( { borrowernumber => $borrowernumber, biblionumber => $biblionumber } )->next();
1176
1177     return unless $hold;
1178
1179     my $already_on_shelf = $hold->found && $hold->found eq 'W';
1180
1181     $hold->itemnumber($itemnumber);
1182     $hold->set_waiting($transferToDo);
1183
1184     _koha_notify_reserve( $hold->reserve_id )
1185       if ( !$transferToDo && !$already_on_shelf );
1186
1187     _FixPriority( { biblionumber => $biblionumber } );
1188
1189     if ( C4::Context->preference("ReturnToShelvingCart") ) {
1190         CartToShelf($itemnumber);
1191     }
1192
1193     return;
1194 }
1195
1196 =head2 ModReserveCancelAll
1197
1198   ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1199
1200 function to cancel reserv,check other reserves, and transfer document if it's necessary
1201
1202 =cut
1203
1204 sub ModReserveCancelAll {
1205     my $messages;
1206     my $nextreservinfo;
1207     my ( $itemnumber, $borrowernumber ) = @_;
1208
1209     #step 1 : cancel the reservation
1210     my $CancelReserve = CancelReserve({ itemnumber => $itemnumber, borrowernumber => $borrowernumber });
1211
1212     #step 2 launch the subroutine of the others reserves
1213     ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1214
1215     return ( $messages, $nextreservinfo );
1216 }
1217
1218 =head2 ModReserveMinusPriority
1219
1220   &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1221
1222 Reduce the values of queued list
1223
1224 =cut
1225
1226 sub ModReserveMinusPriority {
1227     my ( $itemnumber, $reserve_id ) = @_;
1228
1229     #first step update the value of the first person on reserv
1230     my $dbh   = C4::Context->dbh;
1231     my $query = "
1232         UPDATE reserves
1233         SET    priority = 0 , itemnumber = ?
1234         WHERE  reserve_id = ?
1235     ";
1236     my $sth_upd = $dbh->prepare($query);
1237     $sth_upd->execute( $itemnumber, $reserve_id );
1238     # second step update all others reserves
1239     _FixPriority({ reserve_id => $reserve_id, rank => '0' });
1240 }
1241
1242 =head2 GetReserveInfo
1243
1244   &GetReserveInfo($reserve_id);
1245
1246 Get item and borrower details for a current hold.
1247 Current implementation this query should have a single result.
1248
1249 =cut
1250
1251 sub GetReserveInfo {
1252     my ( $reserve_id ) = @_;
1253     my $dbh = C4::Context->dbh;
1254     my $strsth="SELECT
1255                    reserve_id,
1256                    reservedate,
1257                    reservenotes,
1258                    reserves.borrowernumber,
1259                    reserves.biblionumber,
1260                    reserves.branchcode,
1261                    reserves.waitingdate,
1262                    notificationdate,
1263                    reminderdate,
1264                    priority,
1265                    found,
1266                    firstname,
1267                    surname,
1268                    phone,
1269                    email,
1270                    address,
1271                    address2,
1272                    cardnumber,
1273                    city,
1274                    zipcode,
1275                    biblio.title,
1276                    biblio.author,
1277                    items.holdingbranch,
1278                    items.itemcallnumber,
1279                    items.itemnumber,
1280                    items.location,
1281                    barcode,
1282                    notes
1283                 FROM reserves
1284                 LEFT JOIN items USING(itemnumber)
1285                 LEFT JOIN borrowers USING(borrowernumber)
1286                 LEFT JOIN biblio ON  (reserves.biblionumber=biblio.biblionumber)
1287                 WHERE reserves.reserve_id = ?";
1288     my $sth = $dbh->prepare($strsth);
1289     $sth->execute($reserve_id);
1290
1291     my $data = $sth->fetchrow_hashref;
1292     return $data;
1293 }
1294
1295 =head2 IsAvailableForItemLevelRequest
1296
1297   my $is_available = IsAvailableForItemLevelRequest($item_record,$borrower_record);
1298
1299 Checks whether a given item record is available for an
1300 item-level hold request.  An item is available if
1301
1302 * it is not lost AND
1303 * it is not damaged AND
1304 * it is not withdrawn AND
1305 * does not have a not for loan value > 0
1306
1307 Need to check the issuingrules onshelfholds column,
1308 if this is set items on the shelf can be placed on hold
1309
1310 Note that IsAvailableForItemLevelRequest() does not
1311 check if the staff operator is authorized to place
1312 a request on the item - in particular,
1313 this routine does not check IndependentBranches
1314 and canreservefromotherbranches.
1315
1316 =cut
1317
1318 sub IsAvailableForItemLevelRequest {
1319     my $item = shift;
1320     my $borrower = shift;
1321
1322     my $dbh = C4::Context->dbh;
1323     # must check the notforloan setting of the itemtype
1324     # FIXME - a lot of places in the code do this
1325     #         or something similar - need to be
1326     #         consolidated
1327     my $itype = _get_itype($item);
1328     my $notforloan_per_itemtype
1329       = $dbh->selectrow_array("SELECT notforloan FROM itemtypes WHERE itemtype = ?",
1330                               undef, $itype);
1331
1332     return 0 if
1333         $notforloan_per_itemtype ||
1334         $item->{itemlost}        ||
1335         $item->{notforloan} > 0  ||
1336         $item->{withdrawn}        ||
1337         ($item->{damaged} && !C4::Context->preference('AllowHoldsOnDamagedItems'));
1338
1339     my $on_shelf_holds = _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1340
1341     if ( $on_shelf_holds == 1 ) {
1342         return 1;
1343     } elsif ( $on_shelf_holds == 2 ) {
1344         my @items =
1345           Koha::Items->search( { biblionumber => $item->{biblionumber} } );
1346
1347         my $any_available = 0;
1348
1349         foreach my $i (@items) {
1350             $any_available = 1
1351               unless $i->itemlost
1352               || $i->notforloan > 0
1353               || $i->withdrawn
1354               || $i->onloan
1355               || IsItemOnHoldAndFound( $i->id )
1356               || ( $i->damaged
1357                 && !C4::Context->preference('AllowHoldsOnDamagedItems') )
1358               || Koha::ItemTypes->find( $i->effective_itemtype() )->notforloan;
1359         }
1360
1361         return $any_available ? 0 : 1;
1362     }
1363
1364     return $item->{onloan} || GetReserveStatus($item->{itemnumber}) eq "Waiting";
1365 }
1366
1367 =head2 OnShelfHoldsAllowed
1368
1369   OnShelfHoldsAllowed($itemtype,$borrowercategory,$branchcode);
1370
1371 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see if onshelf
1372 holds are allowed, returns true if so.
1373
1374 =cut
1375
1376 sub OnShelfHoldsAllowed {
1377     my ($item, $borrower) = @_;
1378
1379     my $itype = _get_itype($item);
1380     return _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1381 }
1382
1383 sub _get_itype {
1384     my $item = shift;
1385
1386     my $itype;
1387     if (C4::Context->preference('item-level_itypes')) {
1388         # We can't trust GetItem to honour the syspref, so safest to do it ourselves
1389         # When GetItem is fixed, we can remove this
1390         $itype = $item->{itype};
1391     }
1392     else {
1393         # XXX This is a bit dodgy. It relies on biblio itemtype column having different name.
1394         # So if we already have a biblioitems join when calling this function,
1395         # we don't need to access the database again
1396         $itype = $item->{itemtype};
1397     }
1398     unless ($itype) {
1399         my $dbh = C4::Context->dbh;
1400         my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
1401         my $sth = $dbh->prepare($query);
1402         $sth->execute($item->{biblioitemnumber});
1403         if (my $data = $sth->fetchrow_hashref()){
1404             $itype = $data->{itemtype};
1405         }
1406     }
1407     return $itype;
1408 }
1409
1410 sub _OnShelfHoldsAllowed {
1411     my ($itype,$borrowercategory,$branchcode) = @_;
1412
1413     my $issuing_rule = Koha::IssuingRules->get_effective_issuing_rule({ categorycode => $borrowercategory, itemtype => $itype, branchcode => $branchcode });
1414     return $issuing_rule ? $issuing_rule->onshelfholds : undef;
1415 }
1416
1417 =head2 AlterPriority
1418
1419   AlterPriority( $where, $reserve_id );
1420
1421 This function changes a reserve's priority up, down, to the top, or to the bottom.
1422 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1423
1424 =cut
1425
1426 sub AlterPriority {
1427     my ( $where, $reserve_id ) = @_;
1428
1429     my $reserve = GetReserve( $reserve_id );
1430
1431     if ( $reserve->{cancellationdate} ) {
1432         warn "I cannot alter the priority for reserve_id $reserve_id, the reserve has been cancelled (".$reserve->{cancellationdate}.')';
1433         return;
1434     }
1435
1436     if ( $where eq 'up' || $where eq 'down' ) {
1437
1438       my $priority = $reserve->{'priority'};
1439       $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1440       _FixPriority({ reserve_id => $reserve_id, rank => $priority })
1441
1442     } elsif ( $where eq 'top' ) {
1443
1444       _FixPriority({ reserve_id => $reserve_id, rank => '1' })
1445
1446     } elsif ( $where eq 'bottom' ) {
1447
1448       _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1449
1450     }
1451 }
1452
1453 =head2 ToggleLowestPriority
1454
1455   ToggleLowestPriority( $borrowernumber, $biblionumber );
1456
1457 This function sets the lowestPriority field to true if is false, and false if it is true.
1458
1459 =cut
1460
1461 sub ToggleLowestPriority {
1462     my ( $reserve_id ) = @_;
1463
1464     my $dbh = C4::Context->dbh;
1465
1466     my $sth = $dbh->prepare( "UPDATE reserves SET lowestPriority = NOT lowestPriority WHERE reserve_id = ?");
1467     $sth->execute( $reserve_id );
1468
1469     _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1470 }
1471
1472 =head2 ToggleSuspend
1473
1474   ToggleSuspend( $reserve_id );
1475
1476 This function sets the suspend field to true if is false, and false if it is true.
1477 If the reserve is currently suspended with a suspend_until date, that date will
1478 be cleared when it is unsuspended.
1479
1480 =cut
1481
1482 sub ToggleSuspend {
1483     my ( $reserve_id, $suspend_until ) = @_;
1484
1485     $suspend_until = dt_from_string($suspend_until) if ($suspend_until);
1486
1487     my $hold = Koha::Holds->find( $reserve_id );
1488
1489     if ( $hold->is_suspended ) {
1490         $hold->resume()
1491     } else {
1492         $hold->suspend_hold( $suspend_until );
1493     }
1494 }
1495
1496 =head2 SuspendAll
1497
1498   SuspendAll(
1499       borrowernumber   => $borrowernumber,
1500       [ biblionumber   => $biblionumber, ]
1501       [ suspend_until  => $suspend_until, ]
1502       [ suspend        => $suspend ]
1503   );
1504
1505   This function accepts a set of hash keys as its parameters.
1506   It requires either borrowernumber or biblionumber, or both.
1507
1508   suspend_until is wholly optional.
1509
1510 =cut
1511
1512 sub SuspendAll {
1513     my %params = @_;
1514
1515     my $borrowernumber = $params{'borrowernumber'} || undef;
1516     my $biblionumber   = $params{'biblionumber'}   || undef;
1517     my $suspend_until  = $params{'suspend_until'}  || undef;
1518     my $suspend = defined( $params{'suspend'} ) ? $params{'suspend'} : 1;
1519
1520     $suspend_until = eval { dt_from_string($suspend_until) }
1521       if ( defined($suspend_until) );
1522
1523     return unless ( $borrowernumber || $biblionumber );
1524
1525     my $params;
1526     $params->{found}          = undef;
1527     $params->{borrowernumber} = $borrowernumber if $borrowernumber;
1528     $params->{biblionumber}   = $biblionumber if $biblionumber;
1529
1530     my @holds = Koha::Holds->search($params);
1531
1532     if ($suspend) {
1533         map { $_->suspend_hold($suspend_until) } @holds;
1534     }
1535     else {
1536         map { $_->resume() } @holds;
1537     }
1538 }
1539
1540
1541 =head2 _FixPriority
1542
1543   _FixPriority({
1544     reserve_id => $reserve_id,
1545     [rank => $rank,]
1546     [ignoreSetLowestRank => $ignoreSetLowestRank]
1547   });
1548
1549   or
1550
1551   _FixPriority({ biblionumber => $biblionumber});
1552
1553 This routine adjusts the priority of a hold request and holds
1554 on the same bib.
1555
1556 In the first form, where a reserve_id is passed, the priority of the
1557 hold is set to supplied rank, and other holds for that bib are adjusted
1558 accordingly.  If the rank is "del", the hold is cancelled.  If no rank
1559 is supplied, all of the holds on that bib have their priority adjusted
1560 as if the second form had been used.
1561
1562 In the second form, where a biblionumber is passed, the holds on that
1563 bib (that are not captured) are sorted in order of increasing priority,
1564 then have reserves.priority set so that the first non-captured hold
1565 has its priority set to 1, the second non-captured hold has its priority
1566 set to 2, and so forth.
1567
1568 In both cases, holds that have the lowestPriority flag on are have their
1569 priority adjusted to ensure that they remain at the end of the line.
1570
1571 Note that the ignoreSetLowestRank parameter is meant to be used only
1572 when _FixPriority calls itself.
1573
1574 =cut
1575
1576 sub _FixPriority {
1577     my ( $params ) = @_;
1578     my $reserve_id = $params->{reserve_id};
1579     my $rank = $params->{rank} // '';
1580     my $ignoreSetLowestRank = $params->{ignoreSetLowestRank};
1581     my $biblionumber = $params->{biblionumber};
1582
1583     my $dbh = C4::Context->dbh;
1584
1585     unless ( $biblionumber ) {
1586         my $res = GetReserve( $reserve_id );
1587         $biblionumber = $res->{biblionumber};
1588     }
1589
1590     if ( $rank eq "del" ) {
1591          CancelReserve({ reserve_id => $reserve_id });
1592     }
1593     elsif ( $rank eq "W" || $rank eq "0" ) {
1594
1595         # make sure priority for waiting or in-transit items is 0
1596         my $query = "
1597             UPDATE reserves
1598             SET    priority = 0
1599             WHERE reserve_id = ?
1600             AND found IN ('W', 'T')
1601         ";
1602         my $sth = $dbh->prepare($query);
1603         $sth->execute( $reserve_id );
1604     }
1605     my @priority;
1606
1607     # get whats left
1608     my $query = "
1609         SELECT reserve_id, borrowernumber, reservedate
1610         FROM   reserves
1611         WHERE  biblionumber   = ?
1612           AND  ((found <> 'W' AND found <> 'T') OR found IS NULL)
1613         ORDER BY priority ASC
1614     ";
1615     my $sth = $dbh->prepare($query);
1616     $sth->execute( $biblionumber );
1617     while ( my $line = $sth->fetchrow_hashref ) {
1618         push( @priority,     $line );
1619     }
1620
1621     # To find the matching index
1622     my $i;
1623     my $key = -1;    # to allow for 0 to be a valid result
1624     for ( $i = 0 ; $i < @priority ; $i++ ) {
1625         if ( $reserve_id == $priority[$i]->{'reserve_id'} ) {
1626             $key = $i;    # save the index
1627             last;
1628         }
1629     }
1630
1631     # if index exists in array then move it to new position
1632     if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1633         my $new_rank = $rank -
1634           1;    # $new_rank is what you want the new index to be in the array
1635         my $moving_item = splice( @priority, $key, 1 );
1636         splice( @priority, $new_rank, 0, $moving_item );
1637     }
1638
1639     # now fix the priority on those that are left....
1640     $query = "
1641         UPDATE reserves
1642         SET    priority = ?
1643         WHERE  reserve_id = ?
1644     ";
1645     $sth = $dbh->prepare($query);
1646     for ( my $j = 0 ; $j < @priority ; $j++ ) {
1647         $sth->execute(
1648             $j + 1,
1649             $priority[$j]->{'reserve_id'}
1650         );
1651     }
1652
1653     $sth = $dbh->prepare( "SELECT reserve_id FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1654     $sth->execute();
1655
1656     unless ( $ignoreSetLowestRank ) {
1657       while ( my $res = $sth->fetchrow_hashref() ) {
1658         _FixPriority({
1659             reserve_id => $res->{'reserve_id'},
1660             rank => '999999',
1661             ignoreSetLowestRank => 1
1662         });
1663       }
1664     }
1665 }
1666
1667 =head2 _Findgroupreserve
1668
1669   @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber, $lookahead, $ignore_borrowers);
1670
1671 Looks for a holds-queue based item-specific match first, then for a holds-queue title-level match, returning the
1672 first match found.  If neither, then we look for non-holds-queue based holds.
1673 Lookahead is the number of days to look in advance.
1674
1675 C<&_Findgroupreserve> returns :
1676 C<@results> is an array of references-to-hash whose keys are mostly
1677 fields from the reserves table of the Koha database, plus
1678 C<biblioitemnumber>.
1679
1680 =cut
1681
1682 sub _Findgroupreserve {
1683     my ( $bibitem, $biblio, $itemnumber, $lookahead, $ignore_borrowers) = @_;
1684     my $dbh   = C4::Context->dbh;
1685
1686     # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1687     # check for exact targeted match
1688     my $item_level_target_query = qq{
1689         SELECT reserves.biblionumber        AS biblionumber,
1690                reserves.borrowernumber      AS borrowernumber,
1691                reserves.reservedate         AS reservedate,
1692                reserves.branchcode          AS branchcode,
1693                reserves.cancellationdate    AS cancellationdate,
1694                reserves.found               AS found,
1695                reserves.reservenotes        AS reservenotes,
1696                reserves.priority            AS priority,
1697                reserves.timestamp           AS timestamp,
1698                biblioitems.biblioitemnumber AS biblioitemnumber,
1699                reserves.itemnumber          AS itemnumber,
1700                reserves.reserve_id          AS reserve_id,
1701                reserves.itemtype            AS itemtype
1702         FROM reserves
1703         JOIN biblioitems USING (biblionumber)
1704         JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1705         WHERE found IS NULL
1706         AND priority > 0
1707         AND item_level_request = 1
1708         AND itemnumber = ?
1709         AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1710         AND suspend = 0
1711         ORDER BY priority
1712     };
1713     my $sth = $dbh->prepare($item_level_target_query);
1714     $sth->execute($itemnumber, $lookahead||0);
1715     my @results;
1716     if ( my $data = $sth->fetchrow_hashref ) {
1717         push( @results, $data )
1718           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1719     }
1720     return @results if @results;
1721
1722     # check for title-level targeted match
1723     my $title_level_target_query = qq{
1724         SELECT reserves.biblionumber        AS biblionumber,
1725                reserves.borrowernumber      AS borrowernumber,
1726                reserves.reservedate         AS reservedate,
1727                reserves.branchcode          AS branchcode,
1728                reserves.cancellationdate    AS cancellationdate,
1729                reserves.found               AS found,
1730                reserves.reservenotes        AS reservenotes,
1731                reserves.priority            AS priority,
1732                reserves.timestamp           AS timestamp,
1733                biblioitems.biblioitemnumber AS biblioitemnumber,
1734                reserves.itemnumber          AS itemnumber,
1735                reserves.reserve_id          AS reserve_id,
1736                reserves.itemtype            AS itemtype
1737         FROM reserves
1738         JOIN biblioitems USING (biblionumber)
1739         JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1740         WHERE found IS NULL
1741         AND priority > 0
1742         AND item_level_request = 0
1743         AND hold_fill_targets.itemnumber = ?
1744         AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1745         AND suspend = 0
1746         ORDER BY priority
1747     };
1748     $sth = $dbh->prepare($title_level_target_query);
1749     $sth->execute($itemnumber, $lookahead||0);
1750     @results = ();
1751     if ( my $data = $sth->fetchrow_hashref ) {
1752         push( @results, $data )
1753           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1754     }
1755     return @results if @results;
1756
1757     my $query = qq{
1758         SELECT reserves.biblionumber               AS biblionumber,
1759                reserves.borrowernumber             AS borrowernumber,
1760                reserves.reservedate                AS reservedate,
1761                reserves.waitingdate                AS waitingdate,
1762                reserves.branchcode                 AS branchcode,
1763                reserves.cancellationdate           AS cancellationdate,
1764                reserves.found                      AS found,
1765                reserves.reservenotes               AS reservenotes,
1766                reserves.priority                   AS priority,
1767                reserves.timestamp                  AS timestamp,
1768                reserves.itemnumber                 AS itemnumber,
1769                reserves.reserve_id                 AS reserve_id,
1770                reserves.itemtype                   AS itemtype
1771         FROM reserves
1772         WHERE reserves.biblionumber = ?
1773           AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1774           AND reserves.reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1775           AND suspend = 0
1776           ORDER BY priority
1777     };
1778     $sth = $dbh->prepare($query);
1779     $sth->execute( $biblio, $itemnumber, $lookahead||0);
1780     @results = ();
1781     while ( my $data = $sth->fetchrow_hashref ) {
1782         push( @results, $data )
1783           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1784     }
1785     return @results;
1786 }
1787
1788 =head2 _koha_notify_reserve
1789
1790   _koha_notify_reserve( $hold->reserve_id );
1791
1792 Sends a notification to the patron that their hold has been filled (through
1793 ModReserveAffect, _not_ ModReserveFill)
1794
1795 The letter code for this notice may be found using the following query:
1796
1797     select distinct letter_code
1798     from message_transports
1799     inner join message_attributes using (message_attribute_id)
1800     where message_name = 'Hold_Filled'
1801
1802 This will probably sipmly be 'HOLD', but because it is defined in the database,
1803 it is subject to addition or change.
1804
1805 The following tables are availalbe witin the notice:
1806
1807     branches
1808     borrowers
1809     biblio
1810     biblioitems
1811     reserves
1812     items
1813
1814 =cut
1815
1816 sub _koha_notify_reserve {
1817     my $reserve_id = shift;
1818     my $hold = Koha::Holds->find($reserve_id);
1819     my $borrowernumber = $hold->borrowernumber;
1820
1821     my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1822
1823     # Try to get the borrower's email address
1824     my $to_address = C4::Members::GetNoticeEmailAddress($borrowernumber);
1825
1826     my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( {
1827             borrowernumber => $borrowernumber,
1828             message_name => 'Hold_Filled'
1829     } );
1830
1831     my $library = Koha::Libraries->find( $hold->branchcode )->unblessed;
1832
1833     my $admin_email_address = $library->{branchemail} || C4::Context->preference('KohaAdminEmailAddress');
1834
1835     my %letter_params = (
1836         module => 'reserves',
1837         branchcode => $hold->branchcode,
1838         lang => $borrower->{lang},
1839         tables => {
1840             'branches'       => $library,
1841             'borrowers'      => $borrower,
1842             'biblio'         => $hold->biblionumber,
1843             'biblioitems'    => $hold->biblionumber,
1844             'reserves'       => $hold->unblessed,
1845             'items'          => $hold->itemnumber,
1846         },
1847         substitute => { today => output_pref( { dt => dt_from_string, dateonly => 1 } ) },
1848     );
1849
1850     my $notification_sent = 0; #Keeping track if a Hold_filled message is sent. If no message can be sent, then default to a print message.
1851     my $send_notification = sub {
1852         my ( $mtt, $letter_code ) = (@_);
1853         return unless defined $letter_code;
1854         $letter_params{letter_code} = $letter_code;
1855         $letter_params{message_transport_type} = $mtt;
1856         my $letter =  C4::Letters::GetPreparedLetter ( %letter_params );
1857         unless ($letter) {
1858             warn "Could not find a letter called '$letter_params{'letter_code'}' for $mtt in the 'reserves' module";
1859             return;
1860         }
1861
1862         C4::Letters::EnqueueLetter( {
1863             letter => $letter,
1864             borrowernumber => $borrowernumber,
1865             from_address => $admin_email_address,
1866             message_transport_type => $mtt,
1867         } );
1868     };
1869
1870     while ( my ( $mtt, $letter_code ) = each %{ $messagingprefs->{transports} } ) {
1871         next if (
1872                ( $mtt eq 'email' and not $to_address ) # No email address
1873             or ( $mtt eq 'sms'   and not $borrower->{smsalertnumber} ) # No SMS number
1874             or ( $mtt eq 'phone' and C4::Context->preference('TalkingTechItivaPhoneNotification') ) # Notice is handled by TalkingTech_itiva_outbound.pl
1875         );
1876
1877         &$send_notification($mtt, $letter_code);
1878         $notification_sent++;
1879     }
1880     #Making sure that a print notification is sent if no other transport types can be utilized.
1881     if (! $notification_sent) {
1882         &$send_notification('print', 'HOLD');
1883     }
1884
1885 }
1886
1887 =head2 _ShiftPriorityByDateAndPriority
1888
1889   $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1890
1891 This increments the priority of all reserves after the one
1892 with either the lowest date after C<$reservedate>
1893 or the lowest priority after C<$priority>.
1894
1895 It effectively makes room for a new reserve to be inserted with a certain
1896 priority, which is returned.
1897
1898 This is most useful when the reservedate can be set by the user.  It allows
1899 the new reserve to be placed before other reserves that have a later
1900 reservedate.  Since priority also is set by the form in reserves/request.pl
1901 the sub accounts for that too.
1902
1903 =cut
1904
1905 sub _ShiftPriorityByDateAndPriority {
1906     my ( $biblio, $resdate, $new_priority ) = @_;
1907
1908     my $dbh = C4::Context->dbh;
1909     my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1910     my $sth = $dbh->prepare( $query );
1911     $sth->execute( $biblio, $resdate, $new_priority );
1912     my $min_priority = $sth->fetchrow;
1913     # if no such matches are found, $new_priority remains as original value
1914     $new_priority = $min_priority if ( $min_priority );
1915
1916     # Shift the priority up by one; works in conjunction with the next SQL statement
1917     $query = "UPDATE reserves
1918               SET priority = priority+1
1919               WHERE biblionumber = ?
1920               AND borrowernumber = ?
1921               AND reservedate = ?
1922               AND found IS NULL";
1923     my $sth_update = $dbh->prepare( $query );
1924
1925     # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1926     $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1927     $sth = $dbh->prepare( $query );
1928     $sth->execute( $new_priority, $biblio );
1929     while ( my $row = $sth->fetchrow_hashref ) {
1930         $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1931     }
1932
1933     return $new_priority;  # so the caller knows what priority they wind up receiving
1934 }
1935
1936 =head2 OPACItemHoldsAllowed
1937
1938   OPACItemHoldsAllowed($item_record,$borrower_record);
1939
1940 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see
1941 if specific item holds are allowed, returns true if so.
1942
1943 =cut
1944
1945 sub OPACItemHoldsAllowed {
1946     my ($item,$borrower) = @_;
1947
1948     my $branchcode = $item->{homebranch} or die "No homebranch";
1949     my $itype;
1950     my $dbh = C4::Context->dbh;
1951     if (C4::Context->preference('item-level_itypes')) {
1952        # We can't trust GetItem to honour the syspref, so safest to do it ourselves
1953        # When GetItem is fixed, we can remove this
1954        $itype = $item->{itype};
1955     }
1956     else {
1957        my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
1958        my $sth = $dbh->prepare($query);
1959        $sth->execute($item->{biblioitemnumber});
1960        if (my $data = $sth->fetchrow_hashref()){
1961            $itype = $data->{itemtype};
1962        }
1963     }
1964
1965     my $query = "SELECT opacitemholds,categorycode,itemtype,branchcode FROM issuingrules WHERE
1966           (issuingrules.categorycode = ? OR issuingrules.categorycode = '*')
1967         AND
1968           (issuingrules.itemtype = ? OR issuingrules.itemtype = '*')
1969         AND
1970           (issuingrules.branchcode = ? OR issuingrules.branchcode = '*')
1971         ORDER BY
1972           issuingrules.categorycode desc,
1973           issuingrules.itemtype desc,
1974           issuingrules.branchcode desc
1975        LIMIT 1";
1976     my $sth = $dbh->prepare($query);
1977     $sth->execute($borrower->{categorycode},$itype,$branchcode);
1978     my $data = $sth->fetchrow_hashref;
1979     my $opacitemholds = uc substr ($data->{opacitemholds}, 0, 1);
1980     return '' if $opacitemholds eq 'N';
1981     return $opacitemholds;
1982 }
1983
1984 =head2 MoveReserve
1985
1986   MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
1987
1988 Use when checking out an item to handle reserves
1989 If $cancelreserve boolean is set to true, it will remove existing reserve
1990
1991 =cut
1992
1993 sub MoveReserve {
1994     my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
1995
1996     my $lookahead = C4::Context->preference('ConfirmFutureHolds'); #number of days to look for future holds
1997     my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber, undef, $lookahead );
1998     return unless $res;
1999
2000     my $biblionumber     =  $res->{biblionumber};
2001
2002     if ($res->{borrowernumber} == $borrowernumber) {
2003         ModReserveFill($res);
2004     }
2005     else {
2006         # warn "Reserved";
2007         # The item is reserved by someone else.
2008         # Find this item in the reserves
2009
2010         my $borr_res;
2011         foreach (@$all_reserves) {
2012             $_->{'borrowernumber'} == $borrowernumber or next;
2013             $_->{'biblionumber'}   == $biblionumber   or next;
2014
2015             $borr_res = $_;
2016             last;
2017         }
2018
2019         if ( $borr_res ) {
2020             # The item is reserved by the current patron
2021             ModReserveFill($borr_res);
2022         }
2023
2024         if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
2025             RevertWaitingStatus({ itemnumber => $itemnumber });
2026         }
2027         elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
2028             CancelReserve( { reserve_id => $res->{'reserve_id'} } );
2029         }
2030     }
2031 }
2032
2033 =head2 MergeHolds
2034
2035   MergeHolds($dbh,$to_biblio, $from_biblio);
2036
2037 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2038
2039 =cut
2040
2041 sub MergeHolds {
2042     my ( $dbh, $to_biblio, $from_biblio ) = @_;
2043     my $sth = $dbh->prepare(
2044         "SELECT count(*) as reserve_count FROM reserves WHERE biblionumber = ?"
2045     );
2046     $sth->execute($from_biblio);
2047     if ( my $data = $sth->fetchrow_hashref() ) {
2048
2049         # holds exist on old record, if not we don't need to do anything
2050         $sth = $dbh->prepare(
2051             "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2052         $sth->execute( $to_biblio, $from_biblio );
2053
2054         # Reorder by date
2055         # don't reorder those already waiting
2056
2057         $sth = $dbh->prepare(
2058 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2059         );
2060         my $upd_sth = $dbh->prepare(
2061 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2062         AND reservedate = ? AND (itemnumber = ? or itemnumber is NULL) "
2063         );
2064         $sth->execute( $to_biblio, 'W', 'T' );
2065         my $priority = 1;
2066         while ( my $reserve = $sth->fetchrow_hashref() ) {
2067             $upd_sth->execute(
2068                 $priority,                    $to_biblio,
2069                 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2070                 $reserve->{'itemnumber'}
2071             );
2072             $priority++;
2073         }
2074     }
2075 }
2076
2077 =head2 RevertWaitingStatus
2078
2079   RevertWaitingStatus({ itemnumber => $itemnumber });
2080
2081   Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2082
2083   Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2084           item level hold, even if it was only a bibliolevel hold to
2085           begin with. This is because we can no longer know if a hold
2086           was item-level or bib-level after a hold has been set to
2087           waiting status.
2088
2089 =cut
2090
2091 sub RevertWaitingStatus {
2092     my ( $params ) = @_;
2093     my $itemnumber = $params->{'itemnumber'};
2094
2095     return unless ( $itemnumber );
2096
2097     my $dbh = C4::Context->dbh;
2098
2099     ## Get the waiting reserve we want to revert
2100     my $query = "
2101         SELECT * FROM reserves
2102         WHERE itemnumber = ?
2103         AND found IS NOT NULL
2104     ";
2105     my $sth = $dbh->prepare( $query );
2106     $sth->execute( $itemnumber );
2107     my $reserve = $sth->fetchrow_hashref();
2108
2109     ## Increment the priority of all other non-waiting
2110     ## reserves for this bib record
2111     $query = "
2112         UPDATE reserves
2113         SET
2114           priority = priority + 1
2115         WHERE
2116           biblionumber =  ?
2117         AND
2118           priority > 0
2119     ";
2120     $sth = $dbh->prepare( $query );
2121     $sth->execute( $reserve->{'biblionumber'} );
2122
2123     ## Fix up the currently waiting reserve
2124     $query = "
2125     UPDATE reserves
2126     SET
2127       priority = 1,
2128       found = NULL,
2129       waitingdate = NULL
2130     WHERE
2131       reserve_id = ?
2132     ";
2133     $sth = $dbh->prepare( $query );
2134     $sth->execute( $reserve->{'reserve_id'} );
2135     _FixPriority( { biblionumber => $reserve->{biblionumber} } );
2136 }
2137
2138 =head2 GetReserveId
2139
2140   $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber [, itemnumber => $itemnumber ] });
2141
2142   Returnes the first reserve id that matches the given criteria
2143
2144 =cut
2145
2146 sub GetReserveId {
2147     my ( $params ) = @_;
2148
2149     return unless ( ( $params->{'biblionumber'} || $params->{'itemnumber'} ) && $params->{'borrowernumber'} );
2150
2151     foreach my $key ( keys %$params ) {
2152         delete $params->{$key} unless defined( $params->{$key} );
2153     }
2154
2155     my $hold = Koha::Holds->search( $params )->next();
2156
2157     return unless $hold;
2158
2159     return $hold->id();
2160 }
2161
2162 =head2 ReserveSlip
2163
2164   ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2165
2166 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2167
2168 The letter code will be HOLD_SLIP, and the following tables are
2169 available within the slip:
2170
2171     reserves
2172     branches
2173     borrowers
2174     biblio
2175     biblioitems
2176     items
2177
2178 =cut
2179
2180 sub ReserveSlip {
2181     my ($branch, $borrowernumber, $biblionumber) = @_;
2182
2183 #   return unless ( C4::Context->boolean_preference('printreserveslips') );
2184     my $patron = Koha::Patrons->find( $borrowernumber );
2185
2186     my $reserve_id = GetReserveId({
2187         biblionumber => $biblionumber,
2188         borrowernumber => $borrowernumber
2189     }) or return;
2190     my $reserve = GetReserveInfo($reserve_id) or return;
2191
2192     return  C4::Letters::GetPreparedLetter (
2193         module => 'circulation',
2194         letter_code => 'HOLD_SLIP',
2195         branchcode => $branch,
2196         lang => $patron->lang,
2197         tables => {
2198             'reserves'    => $reserve,
2199             'branches'    => $reserve->{branchcode},
2200             'borrowers'   => $reserve->{borrowernumber},
2201             'biblio'      => $reserve->{biblionumber},
2202             'biblioitems' => $reserve->{biblionumber},
2203             'items'       => $reserve->{itemnumber},
2204         },
2205     );
2206 }
2207
2208 =head2 GetReservesControlBranch
2209
2210   my $reserves_control_branch = GetReservesControlBranch($item, $borrower);
2211
2212   Return the branchcode to be used to determine which reserves
2213   policy applies to a transaction.
2214
2215   C<$item> is a hashref for an item. Only 'homebranch' is used.
2216
2217   C<$borrower> is a hashref to borrower. Only 'branchcode' is used.
2218
2219 =cut
2220
2221 sub GetReservesControlBranch {
2222     my ( $item, $borrower ) = @_;
2223
2224     my $reserves_control = C4::Context->preference('ReservesControlBranch');
2225
2226     my $branchcode =
2227         ( $reserves_control eq 'ItemHomeLibrary' ) ? $item->{'homebranch'}
2228       : ( $reserves_control eq 'PatronLibrary' )   ? $borrower->{'branchcode'}
2229       :                                              undef;
2230
2231     return $branchcode;
2232 }
2233
2234 =head2 CalculatePriority
2235
2236     my $p = CalculatePriority($biblionumber, $resdate);
2237
2238 Calculate priority for a new reserve on biblionumber, placing it at
2239 the end of the line of all holds whose start date falls before
2240 the current system time and that are neither on the hold shelf
2241 or in transit.
2242
2243 The reserve date parameter is optional; if it is supplied, the
2244 priority is based on the set of holds whose start date falls before
2245 the parameter value.
2246
2247 After calculation of this priority, it is recommended to call
2248 _ShiftPriorityByDateAndPriority. Note that this is currently done in
2249 AddReserves.
2250
2251 =cut
2252
2253 sub CalculatePriority {
2254     my ( $biblionumber, $resdate ) = @_;
2255
2256     my $sql = q{
2257         SELECT COUNT(*) FROM reserves
2258         WHERE biblionumber = ?
2259         AND   priority > 0
2260         AND   (found IS NULL OR found = '')
2261     };
2262     #skip found==W or found==T (waiting or transit holds)
2263     if( $resdate ) {
2264         $sql.= ' AND ( reservedate <= ? )';
2265     }
2266     else {
2267         $sql.= ' AND ( reservedate < NOW() )';
2268     }
2269     my $dbh = C4::Context->dbh();
2270     my @row = $dbh->selectrow_array(
2271         $sql,
2272         undef,
2273         $resdate ? ($biblionumber, $resdate) : ($biblionumber)
2274     );
2275
2276     return @row ? $row[0]+1 : 1;
2277 }
2278
2279 =head2 IsItemOnHoldAndFound
2280
2281     my $bool = IsItemFoundHold( $itemnumber );
2282
2283     Returns true if the item is currently on hold
2284     and that hold has a non-null found status ( W, T, etc. )
2285
2286 =cut
2287
2288 sub IsItemOnHoldAndFound {
2289     my ($itemnumber) = @_;
2290
2291     my $rs = Koha::Database->new()->schema()->resultset('Reserve');
2292
2293     my $found = $rs->count(
2294         {
2295             itemnumber => $itemnumber,
2296             found      => { '!=' => undef }
2297         }
2298     );
2299
2300     return $found;
2301 }
2302
2303 =head2 GetMaxPatronHoldsForRecord
2304
2305 my $holds_per_record = ReservesControlBranch( $borrowernumber, $biblionumber );
2306
2307 For multiple holds on a given record for a given patron, the max
2308 number of record level holds that a patron can be placed is the highest
2309 value of the holds_per_record rule for each item if the record for that
2310 patron. This subroutine finds and returns the highest holds_per_record
2311 rule value for a given patron id and record id.
2312
2313 =cut
2314
2315 sub GetMaxPatronHoldsForRecord {
2316     my ( $borrowernumber, $biblionumber ) = @_;
2317
2318     my $patron = Koha::Patrons->find($borrowernumber);
2319     my @items = Koha::Items->search( { biblionumber => $biblionumber } );
2320
2321     my $controlbranch = C4::Context->preference('ReservesControlBranch');
2322
2323     my $categorycode = $patron->categorycode;
2324     my $branchcode;
2325     $branchcode = $patron->branchcode if ( $controlbranch eq "PatronLibrary" );
2326
2327     my $max = 0;
2328     foreach my $item (@items) {
2329         my $itemtype = $item->effective_itemtype();
2330
2331         $branchcode = $item->homebranch if ( $controlbranch eq "ItemHomeLibrary" );
2332
2333         my $rule = GetHoldRule( $categorycode, $itemtype, $branchcode );
2334         my $holds_per_record = $rule ? $rule->{holds_per_record} : 0;
2335         $max = $holds_per_record if $holds_per_record > $max;
2336     }
2337
2338     return $max;
2339 }
2340
2341 =head2 GetHoldRule
2342
2343 my $rule = GetHoldRule( $categorycode, $itemtype, $branchcode );
2344
2345 Returns the matching hold related issuingrule fields for a given
2346 patron category, itemtype, and library.
2347
2348 =cut
2349
2350 sub GetHoldRule {
2351     my ( $categorycode, $itemtype, $branchcode ) = @_;
2352
2353     my $dbh = C4::Context->dbh;
2354
2355     my $sth = $dbh->prepare(
2356         q{
2357          SELECT categorycode, itemtype, branchcode, reservesallowed, holds_per_record
2358            FROM issuingrules
2359           WHERE (categorycode in (?,'*') )
2360             AND (itemtype IN (?,'*'))
2361             AND (branchcode IN (?,'*'))
2362        ORDER BY categorycode DESC,
2363                 itemtype     DESC,
2364                 branchcode   DESC
2365         }
2366     );
2367
2368     $sth->execute( $categorycode, $itemtype, $branchcode );
2369
2370     return $sth->fetchrow_hashref();
2371 }
2372
2373 =head1 AUTHOR
2374
2375 Koha Development Team <http://koha-community.org/>
2376
2377 =cut
2378
2379 1;