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