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