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