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