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