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