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