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