C4/Reserves.pm

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