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