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