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