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