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
1038     my $dbh = C4::Context->dbh;
1039
1040     my $query = "UPDATE reserves SET suspend = 0, suspend_until = NULL WHERE DATE( suspend_until ) < DATE( CURDATE() )";
1041     my $sth = $dbh->prepare( $query );
1042     $sth->execute();
1043
1044 }
1045
1046 =head2 CancelReserve
1047
1048   CancelReserve({ reserve_id => $reserve_id, [ biblionumber => $biblionumber, borrowernumber => $borrrowernumber, itemnumber => $itemnumber, ] [ charge_cancel_fee => 1 ] });
1049
1050 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.
1051
1052 =cut
1053
1054 sub CancelReserve {
1055     my ( $params ) = @_;
1056
1057     my $reserve_id = $params->{'reserve_id'};
1058     # Filter out only the desired keys; this will insert undefined values for elements missing in
1059     # \%params, but GetReserveId filters them out anyway.
1060     $reserve_id = GetReserveId( { biblionumber => $params->{'biblionumber'}, borrowernumber => $params->{'borrowernumber'}, itemnumber => $params->{'itemnumber'} } ) unless ( $reserve_id );
1061
1062     return unless ( $reserve_id );
1063
1064     my $dbh = C4::Context->dbh;
1065
1066     my $reserve = GetReserve( $reserve_id );
1067     if ($reserve) {
1068         my $query = "
1069             UPDATE reserves
1070             SET    cancellationdate = now(),
1071                    found            = Null,
1072                    priority         = 0
1073             WHERE  reserve_id = ?
1074         ";
1075         my $sth = $dbh->prepare($query);
1076         $sth->execute( $reserve_id );
1077
1078         $query = "
1079             INSERT INTO old_reserves
1080             SELECT * FROM reserves
1081             WHERE  reserve_id = ?
1082         ";
1083         $sth = $dbh->prepare($query);
1084         $sth->execute( $reserve_id );
1085
1086         $query = "
1087             DELETE FROM reserves
1088             WHERE  reserve_id = ?
1089         ";
1090         $sth = $dbh->prepare($query);
1091         $sth->execute( $reserve_id );
1092
1093         # now fix the priority on the others....
1094         _FixPriority({ biblionumber => $reserve->{biblionumber} });
1095
1096         # and, if desired, charge a cancel fee
1097         my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
1098         if ( $charge && $params->{'charge_cancel_fee'} ) {
1099             manualinvoice($reserve->{'borrowernumber'}, $reserve->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
1100         }
1101     }
1102
1103     return $reserve;
1104 }
1105
1106 =head2 ModReserve
1107
1108   ModReserve({ rank => $rank,
1109                reserve_id => $reserve_id,
1110                branchcode => $branchcode
1111                [, itemnumber => $itemnumber ]
1112                [, biblionumber => $biblionumber, $borrowernumber => $borrowernumber ]
1113               });
1114
1115 Change a hold request's priority or cancel it.
1116
1117 C<$rank> specifies the effect of the change.  If C<$rank>
1118 is 'W' or 'n', nothing happens.  This corresponds to leaving a
1119 request alone when changing its priority in the holds queue
1120 for a bib.
1121
1122 If C<$rank> is 'del', the hold request is cancelled.
1123
1124 If C<$rank> is an integer greater than zero, the priority of
1125 the request is set to that value.  Since priority != 0 means
1126 that the item is not waiting on the hold shelf, setting the
1127 priority to a non-zero value also sets the request's found
1128 status and waiting date to NULL.
1129
1130 The optional C<$itemnumber> parameter is used only when
1131 C<$rank> is a non-zero integer; if supplied, the itemnumber
1132 of the hold request is set accordingly; if omitted, the itemnumber
1133 is cleared.
1134
1135 B<FIXME:> Note that the forgoing can have the effect of causing
1136 item-level hold requests to turn into title-level requests.  This
1137 will be fixed once reserves has separate columns for requested
1138 itemnumber and supplying itemnumber.
1139
1140 =cut
1141
1142 sub ModReserve {
1143     my ( $params ) = @_;
1144
1145     my $rank = $params->{'rank'};
1146     my $reserve_id = $params->{'reserve_id'};
1147     my $branchcode = $params->{'branchcode'};
1148     my $itemnumber = $params->{'itemnumber'};
1149     my $suspend_until = $params->{'suspend_until'};
1150     my $borrowernumber = $params->{'borrowernumber'};
1151     my $biblionumber = $params->{'biblionumber'};
1152
1153     return if $rank eq "W";
1154     return if $rank eq "n";
1155
1156     return unless ( $reserve_id || ( $borrowernumber && ( $biblionumber || $itemnumber ) ) );
1157     $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber, itemnumber => $itemnumber }) unless ( $reserve_id );
1158
1159     my $dbh = C4::Context->dbh;
1160     if ( $rank eq "del" ) {
1161         CancelReserve({ reserve_id => $reserve_id });
1162     }
1163     elsif ($rank =~ /^\d+/ and $rank > 0) {
1164         my $query = "
1165             UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1166             WHERE reserve_id = ?
1167         ";
1168         my $sth = $dbh->prepare($query);
1169         $sth->execute( $rank, $branchcode, $itemnumber, $reserve_id );
1170
1171         if ( defined( $suspend_until ) ) {
1172             if ( $suspend_until ) {
1173                 $suspend_until = eval { output_pref( { dt => dt_from_string( $suspend_until ), dateonly => 1, dateformat => 'iso' }); };
1174                 $dbh->do("UPDATE reserves SET suspend = 1, suspend_until = ? WHERE reserve_id = ?", undef, ( $suspend_until, $reserve_id ) );
1175             } else {
1176                 $dbh->do("UPDATE reserves SET suspend_until = NULL WHERE reserve_id = ?", undef, ( $reserve_id ) );
1177             }
1178         }
1179
1180         _FixPriority({ reserve_id => $reserve_id, rank =>$rank });
1181     }
1182 }
1183
1184 =head2 ModReserveFill
1185
1186   &ModReserveFill($reserve);
1187
1188 Fill a reserve. If I understand this correctly, this means that the
1189 reserved book has been found and given to the patron who reserved it.
1190
1191 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1192 whose keys are fields from the reserves table in the Koha database.
1193
1194 =cut
1195
1196 sub ModReserveFill {
1197     my ($res) = @_;
1198     my $dbh = C4::Context->dbh;
1199     # fill in a reserve record....
1200     my $reserve_id = $res->{'reserve_id'};
1201     my $biblionumber = $res->{'biblionumber'};
1202     my $borrowernumber    = $res->{'borrowernumber'};
1203     my $resdate = $res->{'reservedate'};
1204
1205     # get the priority on this record....
1206     my $priority;
1207     my $query = "SELECT priority
1208                  FROM   reserves
1209                  WHERE  biblionumber   = ?
1210                   AND   borrowernumber = ?
1211                   AND   reservedate    = ?";
1212     my $sth = $dbh->prepare($query);
1213     $sth->execute( $biblionumber, $borrowernumber, $resdate );
1214     ($priority) = $sth->fetchrow_array;
1215
1216     # update the database...
1217     $query = "UPDATE reserves
1218                   SET    found            = 'F',
1219                          priority         = 0
1220                  WHERE  biblionumber     = ?
1221                     AND reservedate      = ?
1222                     AND borrowernumber   = ?
1223                 ";
1224     $sth = $dbh->prepare($query);
1225     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1226
1227     # move to old_reserves
1228     $query = "INSERT INTO old_reserves
1229                  SELECT * FROM reserves
1230                  WHERE  biblionumber     = ?
1231                     AND reservedate      = ?
1232                     AND borrowernumber   = ?
1233                 ";
1234     $sth = $dbh->prepare($query);
1235     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1236     $query = "DELETE FROM reserves
1237                  WHERE  biblionumber     = ?
1238                     AND reservedate      = ?
1239                     AND borrowernumber   = ?
1240                 ";
1241     $sth = $dbh->prepare($query);
1242     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1243
1244     # now fix the priority on the others (if the priority wasn't
1245     # already sorted!)....
1246     unless ( $priority == 0 ) {
1247         _FixPriority({ reserve_id => $reserve_id, biblionumber => $biblionumber });
1248     }
1249 }
1250
1251 =head2 ModReserveStatus
1252
1253   &ModReserveStatus($itemnumber, $newstatus);
1254
1255 Update the reserve status for the active (priority=0) reserve.
1256
1257 $itemnumber is the itemnumber the reserve is on
1258
1259 $newstatus is the new status.
1260
1261 =cut
1262
1263 sub ModReserveStatus {
1264
1265     #first : check if we have a reservation for this item .
1266     my ($itemnumber, $newstatus) = @_;
1267     my $dbh = C4::Context->dbh;
1268
1269     my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1270     my $sth_set = $dbh->prepare($query);
1271     $sth_set->execute( $newstatus, $itemnumber );
1272
1273     if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1274       CartToShelf( $itemnumber );
1275     }
1276 }
1277
1278 =head2 ModReserveAffect
1279
1280   &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1281
1282 This function affect an item and a status for a given reserve
1283 The itemnumber parameter is used to find the biblionumber.
1284 with the biblionumber & the borrowernumber, we can affect the itemnumber
1285 to the correct reserve.
1286
1287 if $transferToDo is not set, then the status is set to "Waiting" as well.
1288 otherwise, a transfer is on the way, and the end of the transfer will
1289 take care of the waiting status
1290
1291 =cut
1292
1293 sub ModReserveAffect {
1294     my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1295     my $dbh = C4::Context->dbh;
1296
1297     # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1298     # attached to $itemnumber
1299     my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1300     $sth->execute($itemnumber);
1301     my ($biblionumber) = $sth->fetchrow;
1302
1303     # get request - need to find out if item is already
1304     # waiting in order to not send duplicate hold filled notifications
1305     my $reserve_id = GetReserveId({
1306         borrowernumber => $borrowernumber,
1307         biblionumber   => $biblionumber,
1308     });
1309     return unless defined $reserve_id;
1310     my $request = GetReserveInfo($reserve_id);
1311     my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1312
1313     # If we affect a reserve that has to be transferred, don't set to Waiting
1314     my $query;
1315     if ($transferToDo) {
1316     $query = "
1317         UPDATE reserves
1318         SET    priority = 0,
1319                itemnumber = ?,
1320                found = 'T'
1321         WHERE borrowernumber = ?
1322           AND biblionumber = ?
1323     ";
1324     }
1325     else {
1326     # affect the reserve to Waiting as well.
1327         $query = "
1328             UPDATE reserves
1329             SET     priority = 0,
1330                     found = 'W',
1331                     waitingdate = NOW(),
1332                     itemnumber = ?
1333             WHERE borrowernumber = ?
1334               AND biblionumber = ?
1335         ";
1336     }
1337     $sth = $dbh->prepare($query);
1338     $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1339     _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1340     _FixPriority( { biblionumber => $biblionumber } );
1341     if ( C4::Context->preference("ReturnToShelvingCart") ) {
1342       CartToShelf( $itemnumber );
1343     }
1344
1345     return;
1346 }
1347
1348 =head2 ModReserveCancelAll
1349
1350   ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1351
1352 function to cancel reserv,check other reserves, and transfer document if it's necessary
1353
1354 =cut
1355
1356 sub ModReserveCancelAll {
1357     my $messages;
1358     my $nextreservinfo;
1359     my ( $itemnumber, $borrowernumber ) = @_;
1360
1361     #step 1 : cancel the reservation
1362     my $CancelReserve = CancelReserve({ itemnumber => $itemnumber, borrowernumber => $borrowernumber });
1363
1364     #step 2 launch the subroutine of the others reserves
1365     ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1366
1367     return ( $messages, $nextreservinfo );
1368 }
1369
1370 =head2 ModReserveMinusPriority
1371
1372   &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1373
1374 Reduce the values of queued list
1375
1376 =cut
1377
1378 sub ModReserveMinusPriority {
1379     my ( $itemnumber, $reserve_id ) = @_;
1380
1381     #first step update the value of the first person on reserv
1382     my $dbh   = C4::Context->dbh;
1383     my $query = "
1384         UPDATE reserves
1385         SET    priority = 0 , itemnumber = ?
1386         WHERE  reserve_id = ?
1387     ";
1388     my $sth_upd = $dbh->prepare($query);
1389     $sth_upd->execute( $itemnumber, $reserve_id );
1390     # second step update all others reserves
1391     _FixPriority({ reserve_id => $reserve_id, rank => '0' });
1392 }
1393
1394 =head2 GetReserveInfo
1395
1396   &GetReserveInfo($reserve_id);
1397
1398 Get item and borrower details for a current hold.
1399 Current implementation this query should have a single result.
1400
1401 =cut
1402
1403 sub GetReserveInfo {
1404     my ( $reserve_id ) = @_;
1405     my $dbh = C4::Context->dbh;
1406     my $strsth="SELECT
1407                    reserve_id,
1408                    reservedate,
1409                    reservenotes,
1410                    reserves.borrowernumber,
1411                    reserves.biblionumber,
1412                    reserves.branchcode,
1413                    reserves.waitingdate,
1414                    notificationdate,
1415                    reminderdate,
1416                    priority,
1417                    found,
1418                    firstname,
1419                    surname,
1420                    phone,
1421                    email,
1422                    address,
1423                    address2,
1424                    cardnumber,
1425                    city,
1426                    zipcode,
1427                    biblio.title,
1428                    biblio.author,
1429                    items.holdingbranch,
1430                    items.itemcallnumber,
1431                    items.itemnumber,
1432                    items.location,
1433                    barcode,
1434                    notes
1435                 FROM reserves
1436                 LEFT JOIN items USING(itemnumber)
1437                 LEFT JOIN borrowers USING(borrowernumber)
1438                 LEFT JOIN biblio ON  (reserves.biblionumber=biblio.biblionumber)
1439                 WHERE reserves.reserve_id = ?";
1440     my $sth = $dbh->prepare($strsth);
1441     $sth->execute($reserve_id);
1442
1443     my $data = $sth->fetchrow_hashref;
1444     return $data;
1445 }
1446
1447 =head2 IsAvailableForItemLevelRequest
1448
1449   my $is_available = IsAvailableForItemLevelRequest($item_record,$borrower_record);
1450
1451 Checks whether a given item record is available for an
1452 item-level hold request.  An item is available if
1453
1454 * it is not lost AND
1455 * it is not damaged AND
1456 * it is not withdrawn AND
1457 * does not have a not for loan value > 0
1458
1459 Need to check the issuingrules onshelfholds column,
1460 if this is set items on the shelf can be placed on hold
1461
1462 Note that IsAvailableForItemLevelRequest() does not
1463 check if the staff operator is authorized to place
1464 a request on the item - in particular,
1465 this routine does not check IndependentBranches
1466 and canreservefromotherbranches.
1467
1468 =cut
1469
1470 sub IsAvailableForItemLevelRequest {
1471     my $item = shift;
1472     my $borrower = shift;
1473
1474     my $dbh = C4::Context->dbh;
1475     # must check the notforloan setting of the itemtype
1476     # FIXME - a lot of places in the code do this
1477     #         or something similar - need to be
1478     #         consolidated
1479     my $itype = _get_itype($item);
1480     my $notforloan_per_itemtype
1481       = $dbh->selectrow_array("SELECT notforloan FROM itemtypes WHERE itemtype = ?",
1482                               undef, $itype);
1483
1484     return 0 if
1485         $notforloan_per_itemtype ||
1486         $item->{itemlost}        ||
1487         $item->{notforloan} > 0  ||
1488         $item->{withdrawn}        ||
1489         ($item->{damaged} && !C4::Context->preference('AllowHoldsOnDamagedItems'));
1490
1491
1492     return 1 if _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1493
1494     return $item->{onloan} || GetReserveStatus($item->{itemnumber}) eq "Waiting";
1495 }
1496
1497 =head2 OnShelfHoldsAllowed
1498
1499   OnShelfHoldsAllowed($itemtype,$borrowercategory,$branchcode);
1500
1501 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see if onshelf
1502 holds are allowed, returns true if so.
1503
1504 =cut
1505
1506 sub OnShelfHoldsAllowed {
1507     my ($item, $borrower) = @_;
1508
1509     my $itype = _get_itype($item);
1510     return _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1511 }
1512
1513 sub _get_itype {
1514     my $item = shift;
1515
1516     my $itype;
1517     if (C4::Context->preference('item-level_itypes')) {
1518         # We can't trust GetItem to honour the syspref, so safest to do it ourselves
1519         # When GetItem is fixed, we can remove this
1520         $itype = $item->{itype};
1521     }
1522     else {
1523         # XXX This is a bit dodgy. It relies on biblio itemtype column having different name.
1524         # So if we already have a biblioitems join when calling this function,
1525         # we don't need to access the database again
1526         $itype = $item->{itemtype};
1527     }
1528     unless ($itype) {
1529         my $dbh = C4::Context->dbh;
1530         my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
1531         my $sth = $dbh->prepare($query);
1532         $sth->execute($item->{biblioitemnumber});
1533         if (my $data = $sth->fetchrow_hashref()){
1534             $itype = $data->{itemtype};
1535         }
1536     }
1537     return $itype;
1538 }
1539
1540 sub _OnShelfHoldsAllowed {
1541     my ($itype,$borrowercategory,$branchcode) = @_;
1542
1543     my $rule = C4::Circulation::GetIssuingRule($borrowercategory, $itype, $branchcode);
1544     return $rule->{onshelfholds};
1545 }
1546
1547 =head2 AlterPriority
1548
1549   AlterPriority( $where, $reserve_id );
1550
1551 This function changes a reserve's priority up, down, to the top, or to the bottom.
1552 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1553
1554 =cut
1555
1556 sub AlterPriority {
1557     my ( $where, $reserve_id ) = @_;
1558
1559     my $dbh = C4::Context->dbh;
1560
1561     my $reserve = GetReserve( $reserve_id );
1562
1563     if ( $reserve->{cancellationdate} ) {
1564         warn "I cannot alter the priority for reserve_id $reserve_id, the reserve has been cancelled (".$reserve->{cancellationdate}.')';
1565         return;
1566     }
1567
1568     if ( $where eq 'up' || $where eq 'down' ) {
1569
1570       my $priority = $reserve->{'priority'};
1571       $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1572       _FixPriority({ reserve_id => $reserve_id, rank => $priority })
1573
1574     } elsif ( $where eq 'top' ) {
1575
1576       _FixPriority({ reserve_id => $reserve_id, rank => '1' })
1577
1578     } elsif ( $where eq 'bottom' ) {
1579
1580       _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1581
1582     }
1583 }
1584
1585 =head2 ToggleLowestPriority
1586
1587   ToggleLowestPriority( $borrowernumber, $biblionumber );
1588
1589 This function sets the lowestPriority field to true if is false, and false if it is true.
1590
1591 =cut
1592
1593 sub ToggleLowestPriority {
1594     my ( $reserve_id ) = @_;
1595
1596     my $dbh = C4::Context->dbh;
1597
1598     my $sth = $dbh->prepare( "UPDATE reserves SET lowestPriority = NOT lowestPriority WHERE reserve_id = ?");
1599     $sth->execute( $reserve_id );
1600
1601     _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1602 }
1603
1604 =head2 ToggleSuspend
1605
1606   ToggleSuspend( $reserve_id );
1607
1608 This function sets the suspend field to true if is false, and false if it is true.
1609 If the reserve is currently suspended with a suspend_until date, that date will
1610 be cleared when it is unsuspended.
1611
1612 =cut
1613
1614 sub ToggleSuspend {
1615     my ( $reserve_id, $suspend_until ) = @_;
1616
1617     $suspend_until = output_pref(
1618         {
1619             dt         => dt_from_string($suspend_until),
1620             dateformat => 'iso',
1621             dateonly   => 1
1622         }
1623     ) if ($suspend_until);
1624
1625     my $do_until = ( $suspend_until ) ? '?' : 'NULL';
1626
1627     my $dbh = C4::Context->dbh;
1628
1629     my $sth = $dbh->prepare(
1630         "UPDATE reserves SET suspend = NOT suspend,
1631         suspend_until = CASE WHEN suspend = 0 THEN NULL ELSE $do_until END
1632         WHERE reserve_id = ?
1633     ");
1634
1635     my @params;
1636     push( @params, $suspend_until ) if ( $suspend_until );
1637     push( @params, $reserve_id );
1638
1639     $sth->execute( @params );
1640 }
1641
1642 =head2 SuspendAll
1643
1644   SuspendAll(
1645       borrowernumber   => $borrowernumber,
1646       [ biblionumber   => $biblionumber, ]
1647       [ suspend_until  => $suspend_until, ]
1648       [ suspend        => $suspend ]
1649   );
1650
1651   This function accepts a set of hash keys as its parameters.
1652   It requires either borrowernumber or biblionumber, or both.
1653
1654   suspend_until is wholly optional.
1655
1656 =cut
1657
1658 sub SuspendAll {
1659     my %params = @_;
1660
1661     my $borrowernumber = $params{'borrowernumber'} || undef;
1662     my $biblionumber   = $params{'biblionumber'}   || undef;
1663     my $suspend_until  = $params{'suspend_until'}  || undef;
1664     my $suspend        = defined( $params{'suspend'} ) ? $params{'suspend'} :  1;
1665
1666     $suspend_until = eval { output_pref( { dt => dt_from_string( $suspend_until), dateonly => 1, dateformat => 'iso' } ); }
1667                      if ( defined( $suspend_until ) );
1668
1669     return unless ( $borrowernumber || $biblionumber );
1670
1671     my ( $query, $sth, $dbh, @query_params );
1672
1673     $query = "UPDATE reserves SET suspend = ? ";
1674     push( @query_params, $suspend );
1675     if ( !$suspend ) {
1676         $query .= ", suspend_until = NULL ";
1677     } elsif ( $suspend_until ) {
1678         $query .= ", suspend_until = ? ";
1679         push( @query_params, $suspend_until );
1680     }
1681     $query .= " WHERE ";
1682     if ( $borrowernumber ) {
1683         $query .= " borrowernumber = ? ";
1684         push( @query_params, $borrowernumber );
1685     }
1686     $query .= " AND " if ( $borrowernumber && $biblionumber );
1687     if ( $biblionumber ) {
1688         $query .= " biblionumber = ? ";
1689         push( @query_params, $biblionumber );
1690     }
1691     $query .= " AND found IS NULL ";
1692
1693     $dbh = C4::Context->dbh;
1694     $sth = $dbh->prepare( $query );
1695     $sth->execute( @query_params );
1696 }
1697
1698
1699 =head2 _FixPriority
1700
1701   _FixPriority({
1702     reserve_id => $reserve_id,
1703     [rank => $rank,]
1704     [ignoreSetLowestRank => $ignoreSetLowestRank]
1705   });
1706
1707   or
1708
1709   _FixPriority({ biblionumber => $biblionumber});
1710
1711 This routine adjusts the priority of a hold request and holds
1712 on the same bib.
1713
1714 In the first form, where a reserve_id is passed, the priority of the
1715 hold is set to supplied rank, and other holds for that bib are adjusted
1716 accordingly.  If the rank is "del", the hold is cancelled.  If no rank
1717 is supplied, all of the holds on that bib have their priority adjusted
1718 as if the second form had been used.
1719
1720 In the second form, where a biblionumber is passed, the holds on that
1721 bib (that are not captured) are sorted in order of increasing priority,
1722 then have reserves.priority set so that the first non-captured hold
1723 has its priority set to 1, the second non-captured hold has its priority
1724 set to 2, and so forth.
1725
1726 In both cases, holds that have the lowestPriority flag on are have their
1727 priority adjusted to ensure that they remain at the end of the line.
1728
1729 Note that the ignoreSetLowestRank parameter is meant to be used only
1730 when _FixPriority calls itself.
1731
1732 =cut
1733
1734 sub _FixPriority {
1735     my ( $params ) = @_;
1736     my $reserve_id = $params->{reserve_id};
1737     my $rank = $params->{rank} // '';
1738     my $ignoreSetLowestRank = $params->{ignoreSetLowestRank};
1739     my $biblionumber = $params->{biblionumber};
1740
1741     my $dbh = C4::Context->dbh;
1742
1743     unless ( $biblionumber ) {
1744         my $res = GetReserve( $reserve_id );
1745         $biblionumber = $res->{biblionumber};
1746     }
1747
1748     if ( $rank eq "del" ) {
1749          CancelReserve({ reserve_id => $reserve_id });
1750     }
1751     elsif ( $rank eq "W" || $rank eq "0" ) {
1752
1753         # make sure priority for waiting or in-transit items is 0
1754         my $query = "
1755             UPDATE reserves
1756             SET    priority = 0
1757             WHERE reserve_id = ?
1758             AND found IN ('W', 'T')
1759         ";
1760         my $sth = $dbh->prepare($query);
1761         $sth->execute( $reserve_id );
1762     }
1763     my @priority;
1764
1765     # get whats left
1766     my $query = "
1767         SELECT reserve_id, borrowernumber, reservedate
1768         FROM   reserves
1769         WHERE  biblionumber   = ?
1770           AND  ((found <> 'W' AND found <> 'T') OR found IS NULL)
1771         ORDER BY priority ASC
1772     ";
1773     my $sth = $dbh->prepare($query);
1774     $sth->execute( $biblionumber );
1775     while ( my $line = $sth->fetchrow_hashref ) {
1776         push( @priority,     $line );
1777     }
1778
1779     # To find the matching index
1780     my $i;
1781     my $key = -1;    # to allow for 0 to be a valid result
1782     for ( $i = 0 ; $i < @priority ; $i++ ) {
1783         if ( $reserve_id == $priority[$i]->{'reserve_id'} ) {
1784             $key = $i;    # save the index
1785             last;
1786         }
1787     }
1788
1789     # if index exists in array then move it to new position
1790     if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1791         my $new_rank = $rank -
1792           1;    # $new_rank is what you want the new index to be in the array
1793         my $moving_item = splice( @priority, $key, 1 );
1794         splice( @priority, $new_rank, 0, $moving_item );
1795     }
1796
1797     # now fix the priority on those that are left....
1798     $query = "
1799         UPDATE reserves
1800         SET    priority = ?
1801         WHERE  reserve_id = ?
1802     ";
1803     $sth = $dbh->prepare($query);
1804     for ( my $j = 0 ; $j < @priority ; $j++ ) {
1805         $sth->execute(
1806             $j + 1,
1807             $priority[$j]->{'reserve_id'}
1808         );
1809     }
1810
1811     $sth = $dbh->prepare( "SELECT reserve_id FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1812     $sth->execute();
1813
1814     unless ( $ignoreSetLowestRank ) {
1815       while ( my $res = $sth->fetchrow_hashref() ) {
1816         _FixPriority({
1817             reserve_id => $res->{'reserve_id'},
1818             rank => '999999',
1819             ignoreSetLowestRank => 1
1820         });
1821       }
1822     }
1823 }
1824
1825 =head2 _Findgroupreserve
1826
1827   @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber, $lookahead, $ignore_borrowers);
1828
1829 Looks for a holds-queue based item-specific match first, then for a holds-queue title-level match, returning the
1830 first match found.  If neither, then we look for non-holds-queue based holds.
1831 Lookahead is the number of days to look in advance.
1832
1833 C<&_Findgroupreserve> returns :
1834 C<@results> is an array of references-to-hash whose keys are mostly
1835 fields from the reserves table of the Koha database, plus
1836 C<biblioitemnumber>.
1837
1838 =cut
1839
1840 sub _Findgroupreserve {
1841     my ( $bibitem, $biblio, $itemnumber, $lookahead, $ignore_borrowers) = @_;
1842     my $dbh   = C4::Context->dbh;
1843
1844     # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1845     # check for exact targeted match
1846     my $item_level_target_query = qq{
1847         SELECT reserves.biblionumber        AS biblionumber,
1848                reserves.borrowernumber      AS borrowernumber,
1849                reserves.reservedate         AS reservedate,
1850                reserves.branchcode          AS branchcode,
1851                reserves.cancellationdate    AS cancellationdate,
1852                reserves.found               AS found,
1853                reserves.reservenotes        AS reservenotes,
1854                reserves.priority            AS priority,
1855                reserves.timestamp           AS timestamp,
1856                biblioitems.biblioitemnumber AS biblioitemnumber,
1857                reserves.itemnumber          AS itemnumber,
1858                reserves.reserve_id          AS reserve_id
1859         FROM reserves
1860         JOIN biblioitems USING (biblionumber)
1861         JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1862         WHERE found IS NULL
1863         AND priority > 0
1864         AND item_level_request = 1
1865         AND itemnumber = ?
1866         AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1867         AND suspend = 0
1868         ORDER BY priority
1869     };
1870     my $sth = $dbh->prepare($item_level_target_query);
1871     $sth->execute($itemnumber, $lookahead||0);
1872     my @results;
1873     if ( my $data = $sth->fetchrow_hashref ) {
1874         push( @results, $data )
1875           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1876     }
1877     return @results if @results;
1878
1879     # check for title-level targeted match
1880     my $title_level_target_query = qq{
1881         SELECT reserves.biblionumber        AS biblionumber,
1882                reserves.borrowernumber      AS borrowernumber,
1883                reserves.reservedate         AS reservedate,
1884                reserves.branchcode          AS branchcode,
1885                reserves.cancellationdate    AS cancellationdate,
1886                reserves.found               AS found,
1887                reserves.reservenotes        AS reservenotes,
1888                reserves.priority            AS priority,
1889                reserves.timestamp           AS timestamp,
1890                biblioitems.biblioitemnumber AS biblioitemnumber,
1891                reserves.itemnumber          AS itemnumber,
1892                reserves.reserve_id          AS reserve_id
1893         FROM reserves
1894         JOIN biblioitems USING (biblionumber)
1895         JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1896         WHERE found IS NULL
1897         AND priority > 0
1898         AND item_level_request = 0
1899         AND hold_fill_targets.itemnumber = ?
1900         AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1901         AND suspend = 0
1902         ORDER BY priority
1903     };
1904     $sth = $dbh->prepare($title_level_target_query);
1905     $sth->execute($itemnumber, $lookahead||0);
1906     @results = ();
1907     if ( my $data = $sth->fetchrow_hashref ) {
1908         push( @results, $data )
1909           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1910     }
1911     return @results if @results;
1912
1913     my $query = qq{
1914         SELECT reserves.biblionumber               AS biblionumber,
1915                reserves.borrowernumber             AS borrowernumber,
1916                reserves.reservedate                AS reservedate,
1917                reserves.waitingdate                AS waitingdate,
1918                reserves.branchcode                 AS branchcode,
1919                reserves.cancellationdate           AS cancellationdate,
1920                reserves.found                      AS found,
1921                reserves.reservenotes               AS reservenotes,
1922                reserves.priority                   AS priority,
1923                reserves.timestamp                  AS timestamp,
1924                reserves.itemnumber                 AS itemnumber,
1925                reserves.reserve_id                 AS reserve_id
1926         FROM reserves
1927         WHERE reserves.biblionumber = ?
1928           AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1929           AND reserves.reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1930           AND suspend = 0
1931           ORDER BY priority
1932     };
1933     $sth = $dbh->prepare($query);
1934     $sth->execute( $biblio, $itemnumber, $lookahead||0);
1935     @results = ();
1936     while ( my $data = $sth->fetchrow_hashref ) {
1937         push( @results, $data )
1938           unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1939     }
1940     return @results;
1941 }
1942
1943 =head2 _koha_notify_reserve
1944
1945   _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1946
1947 Sends a notification to the patron that their hold has been filled (through
1948 ModReserveAffect, _not_ ModReserveFill)
1949
1950 The letter code for this notice may be found using the following query:
1951
1952     select distinct letter_code
1953     from message_transports
1954     inner join message_attributes using (message_attribute_id)
1955     where message_name = 'Hold_Filled'
1956
1957 This will probably sipmly be 'HOLD', but because it is defined in the database,
1958 it is subject to addition or change.
1959
1960 The following tables are availalbe witin the notice:
1961
1962     branches
1963     borrowers
1964     biblio
1965     biblioitems
1966     reserves
1967     items
1968
1969 =cut
1970
1971 sub _koha_notify_reserve {
1972     my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1973
1974     my $dbh = C4::Context->dbh;
1975     my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1976
1977     # Try to get the borrower's email address
1978     my $to_address = C4::Members::GetNoticeEmailAddress($borrowernumber);
1979
1980     my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( {
1981             borrowernumber => $borrowernumber,
1982             message_name => 'Hold_Filled'
1983     } );
1984
1985     my $sth = $dbh->prepare("
1986         SELECT *
1987         FROM   reserves
1988         WHERE  borrowernumber = ?
1989             AND biblionumber = ?
1990     ");
1991     $sth->execute( $borrowernumber, $biblionumber );
1992     my $reserve = $sth->fetchrow_hashref;
1993     my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1994
1995     my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1996
1997     my %letter_params = (
1998         module => 'reserves',
1999         branchcode => $reserve->{branchcode},
2000         tables => {
2001             'branches'       => $branch_details,
2002             'borrowers'      => $borrower,
2003             'biblio'         => $biblionumber,
2004             'biblioitems'    => $biblionumber,
2005             'reserves'       => $reserve,
2006             'items', $reserve->{'itemnumber'},
2007         },
2008         substitute => { today => output_pref( { dt => dt_from_string, dateonly => 1 } ) },
2009     );
2010
2011     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.
2012     my $send_notification = sub {
2013         my ( $mtt, $letter_code ) = (@_);
2014         return unless defined $letter_code;
2015         $letter_params{letter_code} = $letter_code;
2016         $letter_params{message_transport_type} = $mtt;
2017         my $letter =  C4::Letters::GetPreparedLetter ( %letter_params );
2018         unless ($letter) {
2019             warn "Could not find a letter called '$letter_params{'letter_code'}' for $mtt in the 'reserves' module";
2020             return;
2021         }
2022
2023         C4::Letters::EnqueueLetter( {
2024             letter => $letter,
2025             borrowernumber => $borrowernumber,
2026             from_address => $admin_email_address,
2027             message_transport_type => $mtt,
2028         } );
2029     };
2030
2031     while ( my ( $mtt, $letter_code ) = each %{ $messagingprefs->{transports} } ) {
2032         next if (
2033                ( $mtt eq 'email' and not $to_address ) # No email address
2034             or ( $mtt eq 'sms'   and not $borrower->{smsalertnumber} ) # No SMS number
2035             or ( $mtt eq 'phone' and C4::Context->preference('TalkingTechItivaPhoneNotification') ) # Notice is handled by TalkingTech_itiva_outbound.pl
2036         );
2037
2038         &$send_notification($mtt, $letter_code);
2039         $notification_sent++;
2040     }
2041     #Making sure that a print notification is sent if no other transport types can be utilized.
2042     if (! $notification_sent) {
2043         &$send_notification('print', 'HOLD');
2044     }
2045
2046 }
2047
2048 =head2 _ShiftPriorityByDateAndPriority
2049
2050   $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
2051
2052 This increments the priority of all reserves after the one
2053 with either the lowest date after C<$reservedate>
2054 or the lowest priority after C<$priority>.
2055
2056 It effectively makes room for a new reserve to be inserted with a certain
2057 priority, which is returned.
2058
2059 This is most useful when the reservedate can be set by the user.  It allows
2060 the new reserve to be placed before other reserves that have a later
2061 reservedate.  Since priority also is set by the form in reserves/request.pl
2062 the sub accounts for that too.
2063
2064 =cut
2065
2066 sub _ShiftPriorityByDateAndPriority {
2067     my ( $biblio, $resdate, $new_priority ) = @_;
2068
2069     my $dbh = C4::Context->dbh;
2070     my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
2071     my $sth = $dbh->prepare( $query );
2072     $sth->execute( $biblio, $resdate, $new_priority );
2073     my $min_priority = $sth->fetchrow;
2074     # if no such matches are found, $new_priority remains as original value
2075     $new_priority = $min_priority if ( $min_priority );
2076
2077     # Shift the priority up by one; works in conjunction with the next SQL statement
2078     $query = "UPDATE reserves
2079               SET priority = priority+1
2080               WHERE biblionumber = ?
2081               AND borrowernumber = ?
2082               AND reservedate = ?
2083               AND found IS NULL";
2084     my $sth_update = $dbh->prepare( $query );
2085
2086     # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
2087     $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
2088     $sth = $dbh->prepare( $query );
2089     $sth->execute( $new_priority, $biblio );
2090     while ( my $row = $sth->fetchrow_hashref ) {
2091         $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
2092     }
2093
2094     return $new_priority;  # so the caller knows what priority they wind up receiving
2095 }
2096
2097 =head2 OPACItemHoldsAllowed
2098
2099   OPACItemHoldsAllowed($item_record,$borrower_record);
2100
2101 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see
2102 if specific item holds are allowed, returns true if so.
2103
2104 =cut
2105
2106 sub OPACItemHoldsAllowed {
2107     my ($item,$borrower) = @_;
2108
2109     my $branchcode = $item->{homebranch} or die "No homebranch";
2110     my $itype;
2111     my $dbh = C4::Context->dbh;
2112     if (C4::Context->preference('item-level_itypes')) {
2113        # We can't trust GetItem to honour the syspref, so safest to do it ourselves
2114        # When GetItem is fixed, we can remove this
2115        $itype = $item->{itype};
2116     }
2117     else {
2118        my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
2119        my $sth = $dbh->prepare($query);
2120        $sth->execute($item->{biblioitemnumber});
2121        if (my $data = $sth->fetchrow_hashref()){
2122            $itype = $data->{itemtype};
2123        }
2124     }
2125
2126     my $query = "SELECT opacitemholds,categorycode,itemtype,branchcode FROM issuingrules WHERE
2127           (issuingrules.categorycode = ? OR issuingrules.categorycode = '*')
2128         AND
2129           (issuingrules.itemtype = ? OR issuingrules.itemtype = '*')
2130         AND
2131           (issuingrules.branchcode = ? OR issuingrules.branchcode = '*')
2132         ORDER BY
2133           issuingrules.categorycode desc,
2134           issuingrules.itemtype desc,
2135           issuingrules.branchcode desc
2136        LIMIT 1";
2137     my $sth = $dbh->prepare($query);
2138     $sth->execute($borrower->{categorycode},$itype,$branchcode);
2139     my $data = $sth->fetchrow_hashref;
2140     my $opacitemholds = uc substr ($data->{opacitemholds}, 0, 1);
2141     return '' if $opacitemholds eq 'N';
2142     return $opacitemholds;
2143 }
2144
2145 =head2 MoveReserve
2146
2147   MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
2148
2149 Use when checking out an item to handle reserves
2150 If $cancelreserve boolean is set to true, it will remove existing reserve
2151
2152 =cut
2153
2154 sub MoveReserve {
2155     my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
2156
2157     my $lookahead = C4::Context->preference('ConfirmFutureHolds'); #number of days to look for future holds
2158     my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber, undef, $lookahead );
2159     return unless $res;
2160
2161     my $biblionumber     =  $res->{biblionumber};
2162     my $biblioitemnumber = $res->{biblioitemnumber};
2163
2164     if ($res->{borrowernumber} == $borrowernumber) {
2165         ModReserveFill($res);
2166     }
2167     else {
2168         # warn "Reserved";
2169         # The item is reserved by someone else.
2170         # Find this item in the reserves
2171
2172         my $borr_res;
2173         foreach (@$all_reserves) {
2174             $_->{'borrowernumber'} == $borrowernumber or next;
2175             $_->{'biblionumber'}   == $biblionumber   or next;
2176
2177             $borr_res = $_;
2178             last;
2179         }
2180
2181         if ( $borr_res ) {
2182             # The item is reserved by the current patron
2183             ModReserveFill($borr_res);
2184         }
2185
2186         if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
2187             RevertWaitingStatus({ itemnumber => $itemnumber });
2188         }
2189         elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
2190             CancelReserve( { reserve_id => $res->{'reserve_id'} } );
2191         }
2192     }
2193 }
2194
2195 =head2 MergeHolds
2196
2197   MergeHolds($dbh,$to_biblio, $from_biblio);
2198
2199 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2200
2201 =cut
2202
2203 sub MergeHolds {
2204     my ( $dbh, $to_biblio, $from_biblio ) = @_;
2205     my $sth = $dbh->prepare(
2206         "SELECT count(*) as reserve_count FROM reserves WHERE biblionumber = ?"
2207     );
2208     $sth->execute($from_biblio);
2209     if ( my $data = $sth->fetchrow_hashref() ) {
2210
2211         # holds exist on old record, if not we don't need to do anything
2212         $sth = $dbh->prepare(
2213             "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2214         $sth->execute( $to_biblio, $from_biblio );
2215
2216         # Reorder by date
2217         # don't reorder those already waiting
2218
2219         $sth = $dbh->prepare(
2220 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2221         );
2222         my $upd_sth = $dbh->prepare(
2223 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2224         AND reservedate = ? AND (itemnumber = ? or itemnumber is NULL) "
2225         );
2226         $sth->execute( $to_biblio, 'W', 'T' );
2227         my $priority = 1;
2228         while ( my $reserve = $sth->fetchrow_hashref() ) {
2229             $upd_sth->execute(
2230                 $priority,                    $to_biblio,
2231                 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2232                 $reserve->{'itemnumber'}
2233             );
2234             $priority++;
2235         }
2236     }
2237 }
2238
2239 =head2 RevertWaitingStatus
2240
2241   RevertWaitingStatus({ itemnumber => $itemnumber });
2242
2243   Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2244
2245   Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2246           item level hold, even if it was only a bibliolevel hold to
2247           begin with. This is because we can no longer know if a hold
2248           was item-level or bib-level after a hold has been set to
2249           waiting status.
2250
2251 =cut
2252
2253 sub RevertWaitingStatus {
2254     my ( $params ) = @_;
2255     my $itemnumber = $params->{'itemnumber'};
2256
2257     return unless ( $itemnumber );
2258
2259     my $dbh = C4::Context->dbh;
2260
2261     ## Get the waiting reserve we want to revert
2262     my $query = "
2263         SELECT * FROM reserves
2264         WHERE itemnumber = ?
2265         AND found IS NOT NULL
2266     ";
2267     my $sth = $dbh->prepare( $query );
2268     $sth->execute( $itemnumber );
2269     my $reserve = $sth->fetchrow_hashref();
2270
2271     ## Increment the priority of all other non-waiting
2272     ## reserves for this bib record
2273     $query = "
2274         UPDATE reserves
2275         SET
2276           priority = priority + 1
2277         WHERE
2278           biblionumber =  ?
2279         AND
2280           priority > 0
2281     ";
2282     $sth = $dbh->prepare( $query );
2283     $sth->execute( $reserve->{'biblionumber'} );
2284
2285     ## Fix up the currently waiting reserve
2286     $query = "
2287     UPDATE reserves
2288     SET
2289       priority = 1,
2290       found = NULL,
2291       waitingdate = NULL
2292     WHERE
2293       reserve_id = ?
2294     ";
2295     $sth = $dbh->prepare( $query );
2296     $sth->execute( $reserve->{'reserve_id'} );
2297     _FixPriority( { biblionumber => $reserve->{biblionumber} } );
2298 }
2299
2300 =head2 GetReserveId
2301
2302   $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber [, itemnumber => $itemnumber ] });
2303
2304   Returnes the first reserve id that matches the given criteria
2305
2306 =cut
2307
2308 sub GetReserveId {
2309     my ( $params ) = @_;
2310
2311     return unless ( ( $params->{'biblionumber'} || $params->{'itemnumber'} ) && $params->{'borrowernumber'} );
2312
2313     my $dbh = C4::Context->dbh();
2314
2315     my $sql = "SELECT reserve_id FROM reserves WHERE ";
2316
2317     my @params;
2318     my @limits;
2319     foreach my $key ( keys %$params ) {
2320         if ( defined( $params->{$key} ) ) {
2321             push( @limits, "$key = ?" );
2322             push( @params, $params->{$key} );
2323         }
2324     }
2325
2326     $sql .= join( " AND ", @limits );
2327
2328     my $sth = $dbh->prepare( $sql );
2329     $sth->execute( @params );
2330     my $row = $sth->fetchrow_hashref();
2331
2332     return $row->{'reserve_id'};
2333 }
2334
2335 =head2 ReserveSlip
2336
2337   ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2338
2339 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2340
2341 The letter code will be HOLD_SLIP, and the following tables are
2342 available within the slip:
2343
2344     reserves
2345     branches
2346     borrowers
2347     biblio
2348     biblioitems
2349     items
2350
2351 =cut
2352
2353 sub ReserveSlip {
2354     my ($branch, $borrowernumber, $biblionumber) = @_;
2355
2356 #   return unless ( C4::Context->boolean_preference('printreserveslips') );
2357
2358     my $reserve_id = GetReserveId({
2359         biblionumber => $biblionumber,
2360         borrowernumber => $borrowernumber
2361     }) or return;
2362     my $reserve = GetReserveInfo($reserve_id) or return;
2363
2364     return  C4::Letters::GetPreparedLetter (
2365         module => 'circulation',
2366         letter_code => 'HOLD_SLIP',
2367         branchcode => $branch,
2368         tables => {
2369             'reserves'    => $reserve,
2370             'branches'    => $reserve->{branchcode},
2371             'borrowers'   => $reserve->{borrowernumber},
2372             'biblio'      => $reserve->{biblionumber},
2373             'biblioitems' => $reserve->{biblionumber},
2374             'items'       => $reserve->{itemnumber},
2375         },
2376     );
2377 }
2378
2379 =head2 GetReservesControlBranch
2380
2381   my $reserves_control_branch = GetReservesControlBranch($item, $borrower);
2382
2383   Return the branchcode to be used to determine which reserves
2384   policy applies to a transaction.
2385
2386   C<$item> is a hashref for an item. Only 'homebranch' is used.
2387
2388   C<$borrower> is a hashref to borrower. Only 'branchcode' is used.
2389
2390 =cut
2391
2392 sub GetReservesControlBranch {
2393     my ( $item, $borrower ) = @_;
2394
2395     my $reserves_control = C4::Context->preference('ReservesControlBranch');
2396
2397     my $branchcode =
2398         ( $reserves_control eq 'ItemHomeLibrary' ) ? $item->{'homebranch'}
2399       : ( $reserves_control eq 'PatronLibrary' )   ? $borrower->{'branchcode'}
2400       :                                              undef;
2401
2402     return $branchcode;
2403 }
2404
2405 =head2 CalculatePriority
2406
2407     my $p = CalculatePriority($biblionumber, $resdate);
2408
2409 Calculate priority for a new reserve on biblionumber, placing it at
2410 the end of the line of all holds whose start date falls before
2411 the current system time and that are neither on the hold shelf
2412 or in transit.
2413
2414 The reserve date parameter is optional; if it is supplied, the
2415 priority is based on the set of holds whose start date falls before
2416 the parameter value.
2417
2418 After calculation of this priority, it is recommended to call
2419 _ShiftPriorityByDateAndPriority. Note that this is currently done in
2420 AddReserves.
2421
2422 =cut
2423
2424 sub CalculatePriority {
2425     my ( $biblionumber, $resdate ) = @_;
2426
2427     my $sql = q{
2428         SELECT COUNT(*) FROM reserves
2429         WHERE biblionumber = ?
2430         AND   priority > 0
2431         AND   (found IS NULL OR found = '')
2432     };
2433     #skip found==W or found==T (waiting or transit holds)
2434     if( $resdate ) {
2435         $sql.= ' AND ( reservedate <= ? )';
2436     }
2437     else {
2438         $sql.= ' AND ( reservedate < NOW() )';
2439     }
2440     my $dbh = C4::Context->dbh();
2441     my @row = $dbh->selectrow_array(
2442         $sql,
2443         undef,
2444         $resdate ? ($biblionumber, $resdate) : ($biblionumber)
2445     );
2446
2447     return @row ? $row[0]+1 : 1;
2448 }
2449
2450 =head2 IsItemOnHoldAndFound
2451
2452     my $bool = IsItemFoundHold( $itemnumber );
2453
2454     Returns true if the item is currently on hold
2455     and that hold has a non-null found status ( W, T, etc. )
2456
2457 =cut
2458
2459 sub IsItemOnHoldAndFound {
2460     my ($itemnumber) = @_;
2461
2462     my $rs = Koha::Database->new()->schema()->resultset('Reserve');
2463
2464     my $found = $rs->count(
2465         {
2466             itemnumber => $itemnumber,
2467             found      => { '!=' => undef }
2468         }
2469     );
2470
2471     return $found;
2472 }
2473
2474 =head1 AUTHOR
2475
2476 Koha Development Team <http://koha-community.org/>
2477
2478 =cut
2479
2480 1;