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