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