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