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