C4/Reserves.pm

   1 package C4::Reserves;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #           2006 SAN Ouest Provence
   5 #           2007 BibLibre Paul POULAIN
   6 #
   7 # This file is part of Koha.
   8 #
   9 # Koha is free software; you can redistribute it and/or modify it under the
  10 # terms of the GNU General Public License as published by the Free Software
  11 # Foundation; either version 2 of the License, or (at your option) any later
  12 # version.
  13 #
  14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  16 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  17 #
  18 # You should have received a copy of the GNU General Public License along
  19 # with Koha; if not, write to the Free Software Foundation, Inc.,
  20 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  21
  22
  23 use strict;
  24 #use warnings; FIXME - Bug 2505
  25 use C4::Context;
  26 use C4::Biblio;
  27 use C4::Members;
  28 use C4::Items;
  29 use C4::Search;
  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 use List::MoreUtils qw( firstidx );
  40
  41 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  42
  43 =head1 NAME
  44
  45 C4::Reserves - Koha functions for dealing with reservation.
  46
  47 =head1 SYNOPSIS
  48
  49   use C4::Reserves;
  50
  51 =head1 DESCRIPTION
  52
  53 This modules provides somes functions to deal with reservations.
  54
  55   Reserves are stored in reserves table.
  56   The following columns contains important values :
  57   - priority >0      : then the reserve is at 1st stage, and not yet affected to any item.
  58              =0      : then the reserve is being dealed
  59   - found : NULL       : means the patron requested the 1st available, and we haven't choosen the item
  60             T(ransit)  : the reserve is linked to an item but is in transit to the pickup branch
  61             W(aiting)  : the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf
  62             F(inished) : the reserve has been completed, and is done
  63   - itemnumber : empty : the reserve is still unaffected to an item
  64                  filled: the reserve is attached to an item
  65   The complete workflow is :
  66   ==== 1st use case ====
  67   patron request a document, 1st available :                      P >0, F=NULL, I=NULL
  68   a library having it run "transfertodo", and clic on the list
  69          if there is no transfer to do, the reserve waiting
  70          patron can pick it up                                    P =0, F=W,    I=filled
  71          if there is a transfer to do, write in branchtransfer    P =0, F=T,    I=filled
  72            The pickup library recieve the book, it check in       P =0, F=W,    I=filled
  73   The patron borrow the book                                      P =0, F=F,    I=filled
  74
  75   ==== 2nd use case ====
  76   patron requests a document, a given item,
  77     If pickup is holding branch                                   P =0, F=W,   I=filled
  78     If transfer needed, write in branchtransfer                   P =0, F=T,    I=filled
  79         The pickup library receive the book, it checks it in      P =0, F=W,    I=filled
  80   The patron borrow the book                                      P =0, F=F,    I=filled
  81
  82 =head1 FUNCTIONS
  83
  84 =cut
  85
  86 BEGIN {
  87     # set the version for version checking
  88     $VERSION = 3.01;
  89         require Exporter;
  90     @ISA = qw(Exporter);
  91     @EXPORT = qw(
  92         &AddReserve
  93
  94         &GetReservesFromItemnumber
  95         &GetReservesFromBiblionumber
  96         &GetReservesFromBorrowernumber
  97         &GetReservesForBranch
  98         &GetReservesToBranch
  99         &GetReserveCount
 100         &GetReserveFee
 101                 &GetReserveInfo
 102         &GetReserveStatus
 103
 104         &GetOtherReserves
 105
 106         &ModReserveFill
 107         &ModReserveAffect
 108         &ModReserve
 109         &ModReserveStatus
 110         &ModReserveCancelAll
 111         &ModReserveMinusPriority
 112
 113         &CheckReserves
 114         &CanBookBeReserved
 115         &CanItemBeReserved
 116         &CancelReserve
 117         &CancelExpiredReserves
 118
 119         &IsAvailableForItemLevelRequest
 120
 121         &AlterPriority
 122         &ToggleLowestPriority
 123     );
 124 }
 125
 126 =head2 AddReserve
 127
 128     AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
 129
 130 =cut
 131
 132 sub AddReserve {
 133     my (
 134         $branch,    $borrowernumber, $biblionumber,
 135         $constraint, $bibitems,  $priority, $resdate, $expdate, $notes,
 136         $title,      $checkitem, $found
 137     ) = @_;
 138     my $fee =
 139           GetReserveFee($borrowernumber, $biblionumber, $constraint,
 140             $bibitems );
 141     my $dbh     = C4::Context->dbh;
 142     my $const   = lc substr( $constraint, 0, 1 );
 143     $resdate = format_date_in_iso( $resdate ) if ( $resdate );
 144     $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
 145     if ($expdate) {
 146         $expdate = format_date_in_iso( $expdate );
 147     } else {
 148         undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
 149     }
 150     if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
 151         # Make room in reserves for this before those of a later reserve date
 152         $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
 153     }
 154     my $waitingdate;
 155
 156     # If the reserv had the waiting status, we had the value of the resdate
 157     if ( $found eq 'W' ) {
 158         $waitingdate = $resdate;
 159     }
 160
 161     #eval {
 162     # updates take place here
 163     if ( $fee > 0 ) {
 164         my $nextacctno = &getnextacctno( $borrowernumber );
 165         my $query      = qq/
 166         INSERT INTO accountlines
 167             (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
 168         VALUES
 169             (?,?,now(),?,?,'Res',?)
 170     /;
 171         my $usth = $dbh->prepare($query);
 172         $usth->execute( $borrowernumber, $nextacctno, $fee,
 173             "Reserve Charge - $title", $fee );
 174     }
 175
 176     #if ($const eq 'a'){
 177     my $query = qq/
 178         INSERT INTO reserves
 179             (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
 180             priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
 181         VALUES
 182              (?,?,?,?,?,
 183              ?,?,?,?,?,?)
 184     /;
 185     my $sth = $dbh->prepare($query);
 186     $sth->execute(
 187         $borrowernumber, $biblionumber, $resdate, $branch,
 188         $const,          $priority,     $notes,   $checkitem,
 189         $found,          $waitingdate,  $expdate
 190     );
 191
 192     # Send e-mail to librarian if syspref is active
 193     if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
 194         my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
 195         my $biblio   = GetBiblioData($biblionumber);
 196         my $letter = C4::Letters::getletter( 'reserves', 'HOLDPLACED');
 197         my $admin_email_address = C4::Context->preference('KohaAdminEmailAddress');
 198
 199         my %keys = (%$borrower, %$biblio);
 200         foreach my $key (keys %keys) {
 201             my $replacefield = "<<$key>>";
 202             $letter->{content} =~ s/$replacefield/$keys{$key}/g;
 203             $letter->{title} =~ s/$replacefield/$keys{$key}/g;
 204         }
 205
 206         C4::Letters::EnqueueLetter(
 207                             {   letter                 => $letter,
 208                                 borrowernumber         => $borrowernumber,
 209                                 message_transport_type => 'email',
 210                                 from_address           => $admin_email_address,
 211                                 to_address           => $admin_email_address,
 212                             }
 213                         );
 214
 215
 216     }
 217
 218
 219     #}
 220     ($const eq "o" || $const eq "e") or return;   # FIXME: why not have a useful return value?
 221     $query = qq/
 222         INSERT INTO reserveconstraints
 223             (borrowernumber,biblionumber,reservedate,biblioitemnumber)
 224         VALUES
 225             (?,?,?,?)
 226     /;
 227     $sth = $dbh->prepare($query);    # keep prepare outside the loop!
 228     foreach (@$bibitems) {
 229         $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
 230     }
 231
 232     return;     # FIXME: why not have a useful return value?
 233 }
 234
 235 =head2 GetReservesFromBiblionumber
 236
 237   ($count, $title_reserves) = &GetReserves($biblionumber);
 238
 239 This function gets the list of reservations for one C<$biblionumber>, returning a count
 240 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
 241
 242 =cut
 243
 244 sub GetReservesFromBiblionumber {
 245     my ($biblionumber) = shift or return (0, []);
 246     my ($all_dates) = shift;
 247     my $dbh   = C4::Context->dbh;
 248
 249     # Find the desired items in the reserves
 250     my $query = "
 251         SELECT  branchcode,
 252                 timestamp AS rtimestamp,
 253                 priority,
 254                 biblionumber,
 255                 borrowernumber,
 256                 reservedate,
 257                 constrainttype,
 258                 found,
 259                 itemnumber,
 260                 reservenotes,
 261                 expirationdate,
 262                 lowestPriority
 263         FROM     reserves
 264         WHERE biblionumber = ? ";
 265     unless ( $all_dates ) {
 266         $query .= "AND reservedate <= CURRENT_DATE()";
 267     }
 268     $query .= "ORDER BY priority";
 269     my $sth = $dbh->prepare($query);
 270     $sth->execute($biblionumber);
 271     my @results;
 272     my $i = 0;
 273     while ( my $data = $sth->fetchrow_hashref ) {
 274
 275         # FIXME - What is this doing? How do constraints work?
 276         if ($data->{constrainttype} eq 'o') {
 277             $query = '
 278                 SELECT biblioitemnumber
 279                 FROM  reserveconstraints
 280                 WHERE  biblionumber   = ?
 281                 AND   borrowernumber = ?
 282                 AND   reservedate    = ?
 283             ';
 284             my $csth = $dbh->prepare($query);
 285             $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
 286             my @bibitemno;
 287             while ( my $bibitemnos = $csth->fetchrow_array ) {
 288                 push( @bibitemno, $bibitemnos );    # FIXME: inefficient: use fetchall_arrayref
 289             }
 290             my $count = scalar @bibitemno;
 291
 292             # if we have two or more different specific itemtypes
 293             # reserved by same person on same day
 294             my $bdata;
 295             if ( $count > 1 ) {
 296                 $bdata = GetBiblioItemData( $bibitemno[$i] );   # FIXME: This doesn't make sense.
 297                 $i++; #  $i can increase each pass, but the next @bibitemno might be smaller?
 298             }
 299             else {
 300                 # Look up the book we just found.
 301                 $bdata = GetBiblioItemData( $bibitemno[0] );
 302             }
 303             # Add the results of this latest search to the current
 304             # results.
 305             # FIXME - An 'each' would probably be more efficient.
 306             foreach my $key ( keys %$bdata ) {
 307                 $data->{$key} = $bdata->{$key};
 308             }
 309         }
 310         push @results, $data;
 311     }
 312     return ( $#results + 1, \@results );
 313 }
 314
 315 =head2 GetReservesFromItemnumber
 316
 317  ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
 318
 319 TODO :: Description here
 320
 321 =cut
 322
 323 sub GetReservesFromItemnumber {
 324     my ( $itemnumber, $all_dates ) = @_;
 325     my $dbh   = C4::Context->dbh;
 326     my $query = "
 327     SELECT reservedate,borrowernumber,branchcode
 328     FROM   reserves
 329     WHERE  itemnumber=?
 330     ";
 331     unless ( $all_dates ) {
 332         $query .= " AND reservedate <= CURRENT_DATE()";
 333     }
 334     my $sth_res = $dbh->prepare($query);
 335     $sth_res->execute($itemnumber);
 336     my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
 337     return ( $reservedate, $borrowernumber, $branchcode );
 338 }
 339
 340 =head2 GetReservesFromBorrowernumber
 341
 342     $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
 343
 344 TODO :: Descritpion
 345
 346 =cut
 347
 348 sub GetReservesFromBorrowernumber {
 349     my ( $borrowernumber, $status ) = @_;
 350     my $dbh   = C4::Context->dbh;
 351     my $sth;
 352     if ($status) {
 353         $sth = $dbh->prepare("
 354             SELECT *
 355             FROM   reserves
 356             WHERE  borrowernumber=?
 357                 AND found =?
 358             ORDER BY reservedate
 359         ");
 360         $sth->execute($borrowernumber,$status);
 361     } else {
 362         $sth = $dbh->prepare("
 363             SELECT *
 364             FROM   reserves
 365             WHERE  borrowernumber=?
 366             ORDER BY reservedate
 367         ");
 368         $sth->execute($borrowernumber);
 369     }
 370     my $data = $sth->fetchall_arrayref({});
 371     return @$data;
 372 }
 373 #-------------------------------------------------------------------------------------
 374 =head2 CanBookBeReserved
 375
 376   $error = &CanBookBeReserved($borrowernumber, $biblionumber)
 377
 378 =cut
 379
 380 sub CanBookBeReserved{
 381     my ($borrowernumber, $biblionumber) = @_;
 382
 383     my $dbh           = C4::Context->dbh;
 384     my $biblio        = GetBiblioData($biblionumber);
 385     my $borrower      = C4::Members::GetMember(borrowernumber=>$borrowernumber);
 386     my $controlbranch = C4::Context->preference('ReservesControlBranch');
 387     my $itype         = C4::Context->preference('item-level_itypes');
 388     my $reservesrights= 0;
 389     my $reservescount = 0;
 390
 391     # we retrieve the user rights
 392     my @args;
 393     my $rightsquery = "SELECT categorycode, itemtype, branchcode, reservesallowed
 394                        FROM issuingrules
 395                        WHERE categorycode IN (?, '*')";
 396     push @args,$borrower->{categorycode};
 397
 398     if($controlbranch eq "ItemHomeLibrary"){
 399         $rightsquery .= " AND branchcode = '*'";
 400     }elsif($controlbranch eq "PatronLibrary"){
 401         $rightsquery .= " AND branchcode IN (?,'*')";
 402         push @args, $borrower->{branchcode};
 403     }
 404
 405     if(not $itype){
 406         $rightsquery .= " AND itemtype IN (?,'*')";
 407         push @args, $biblio->{itemtype};
 408     }else{
 409         $rightsquery .= " AND itemtype = '*'";
 410     }
 411
 412     $rightsquery .= " ORDER BY categorycode DESC, itemtype DESC, branchcode DESC";
 413     my $sthrights = $dbh->prepare($rightsquery);
 414     $sthrights->execute(@args);
 415
 416     if(my $row = $sthrights->fetchrow_hashref()){
 417        $reservesrights = $row->{reservesallowed};
 418     }
 419
 420     @args = ();
 421     # we count how many reserves the borrower have
 422     my $countquery = "SELECT count(*) as count
 423                       FROM reserves
 424                       LEFT JOIN items USING (itemnumber)
 425                       LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
 426                       LEFT JOIN borrowers USING (borrowernumber)
 427                       WHERE borrowernumber = ?
 428                     ";
 429     push @args, $borrowernumber;
 430
 431     if(not $itype){
 432            $countquery .= "AND itemtype = ?";
 433            push @args, $biblio->{itemtype};
 434     }
 435
 436     if($controlbranch eq "PatronLibrary"){
 437         $countquery .= " AND borrowers.branchcode = ? ";
 438         push @args, $borrower->{branchcode};
 439     }
 440
 441     my $sthcount = $dbh->prepare($countquery);
 442     $sthcount->execute(@args);
 443
 444     if(my $row = $sthcount->fetchrow_hashref()){
 445        $reservescount = $row->{count};
 446     }
 447     if($reservescount < $reservesrights){
 448         return 1;
 449     }else{
 450         return 0;
 451     }
 452
 453 }
 454
 455 =head2 CanItemBeReserved
 456
 457   $error = &CanItemBeReserved($borrowernumber, $itemnumber)
 458
 459 This function return 1 if an item can be issued by this borrower.
 460
 461 =cut
 462
 463 sub CanItemBeReserved{
 464     my ($borrowernumber, $itemnumber) = @_;
 465
 466     my $dbh             = C4::Context->dbh;
 467     my $allowedreserves = 0;
 468
 469     my $controlbranch = C4::Context->preference('ReservesControlBranch');
 470     my $itype         = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
 471
 472     # we retrieve borrowers and items informations #
 473     my $item     = GetItem($itemnumber);
 474     my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
 475
 476     # we retrieve user rights on this itemtype and branchcode
 477     my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
 478                              FROM issuingrules
 479                              WHERE (categorycode in (?,'*') )
 480                              AND (itemtype IN (?,'*'))
 481                              AND (branchcode IN (?,'*'))
 482                              ORDER BY
 483                                categorycode DESC,
 484                                itemtype     DESC,
 485                                branchcode   DESC;"
 486                            );
 487
 488     my $querycount ="SELECT
 489                             count(*) as count
 490                             FROM reserves
 491                                 LEFT JOIN items USING (itemnumber)
 492                                 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
 493                                 LEFT JOIN borrowers USING (borrowernumber)
 494                             WHERE borrowernumber = ?
 495                                 ";
 496
 497
 498     my $itemtype     = $item->{$itype};
 499     my $categorycode = $borrower->{categorycode};
 500     my $branchcode   = "";
 501     my $branchfield  = "reserves.branchcode";
 502
 503     if( $controlbranch eq "ItemHomeLibrary" ){
 504         $branchfield = "items.homebranch";
 505         $branchcode = $item->{homebranch};
 506     }elsif( $controlbranch eq "PatronLibrary" ){
 507         $branchfield = "borrowers.branchcode";
 508         $branchcode = $borrower->{branchcode};
 509     }
 510
 511     # we retrieve rights
 512     $sth->execute($categorycode, $itemtype, $branchcode);
 513     if(my $rights = $sth->fetchrow_hashref()){
 514         $itemtype        = $rights->{itemtype};
 515         $allowedreserves = $rights->{reservesallowed};
 516     }else{
 517         $itemtype = '*';
 518     }
 519
 520     # we retrieve count
 521
 522     $querycount .= "AND $branchfield = ?";
 523
 524     $querycount .= " AND $itype = ?" if ($itemtype ne "*");
 525     my $sthcount = $dbh->prepare($querycount);
 526
 527     if($itemtype eq "*"){
 528         $sthcount->execute($borrowernumber, $branchcode);
 529     }else{
 530         $sthcount->execute($borrowernumber, $branchcode, $itemtype);
 531     }
 532
 533     my $reservecount = "0";
 534     if(my $rowcount = $sthcount->fetchrow_hashref()){
 535         $reservecount = $rowcount->{count};
 536     }
 537
 538     # we check if it's ok or not
 539     if( $reservecount < $allowedreserves ){
 540         return 1;
 541     }else{
 542         return 0;
 543     }
 544 }
 545 #--------------------------------------------------------------------------------
 546 =head2 GetReserveCount
 547
 548   $number = &GetReserveCount($borrowernumber);
 549
 550 this function returns the number of reservation for a borrower given on input arg.
 551
 552 =cut
 553
 554 sub GetReserveCount {
 555     my ($borrowernumber) = @_;
 556
 557     my $dbh = C4::Context->dbh;
 558
 559     my $query = '
 560         SELECT COUNT(*) AS counter
 561         FROM reserves
 562           WHERE borrowernumber = ?
 563     ';
 564     my $sth = $dbh->prepare($query);
 565     $sth->execute($borrowernumber);
 566     my $row = $sth->fetchrow_hashref;
 567     return $row->{counter};
 568 }
 569
 570 =head2 GetOtherReserves
 571
 572   ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
 573
 574 Check queued list of this document and check if this document must be  transfered
 575
 576 =cut
 577
 578 sub GetOtherReserves {
 579     my ($itemnumber) = @_;
 580     my $messages;
 581     my $nextreservinfo;
 582     my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
 583     if ($checkreserves) {
 584         my $iteminfo = GetItem($itemnumber);
 585         if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
 586             $messages->{'transfert'} = $checkreserves->{'branchcode'};
 587             #minus priorities of others reservs
 588             ModReserveMinusPriority(
 589                 $itemnumber,
 590                 $checkreserves->{'borrowernumber'},
 591                 $iteminfo->{'biblionumber'}
 592             );
 593
 594             #launch the subroutine dotransfer
 595             C4::Items::ModItemTransfer(
 596                 $itemnumber,
 597                 $iteminfo->{'holdingbranch'},
 598                 $checkreserves->{'branchcode'}
 599               ),
 600               ;
 601         }
 602
 603      #step 2b : case of a reservation on the same branch, set the waiting status
 604         else {
 605             $messages->{'waiting'} = 1;
 606             ModReserveMinusPriority(
 607                 $itemnumber,
 608                 $checkreserves->{'borrowernumber'},
 609                 $iteminfo->{'biblionumber'}
 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     $sth->finish();
 643     my $fee      = $data->{'reservefee'};
 644     my $cntitems = @- > $bibitems;
 645
 646     if ( $fee > 0 ) {
 647
 648         # check for items on issue
 649         # first find biblioitem records
 650         my @biblioitems;
 651         my $sth1 = $dbh->prepare(
 652             "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
 653                    WHERE (biblio.biblionumber = ?)"
 654         );
 655         $sth1->execute($biblionumber);
 656         while ( my $data1 = $sth1->fetchrow_hashref ) {
 657             if ( $const eq "a" ) {
 658                 push @biblioitems, $data1;
 659             }
 660             else {
 661                 my $found = 0;
 662                 my $x     = 0;
 663                 while ( $x < $cntitems ) {
 664                     if ( @$bibitems->{'biblioitemnumber'} ==
 665                         $data->{'biblioitemnumber'} )
 666                     {
 667                         $found = 1;
 668                     }
 669                     $x++;
 670                 }
 671                 if ( $const eq 'o' ) {
 672                     if ( $found == 1 ) {
 673                         push @biblioitems, $data1;
 674                     }
 675                 }
 676                 else {
 677                     if ( $found == 0 ) {
 678                         push @biblioitems, $data1;
 679                     }
 680                 }
 681             }
 682         }
 683         $sth1->finish;
 684         my $cntitemsfound = @biblioitems;
 685         my $issues        = 0;
 686         my $x             = 0;
 687         my $allissued     = 1;
 688         while ( $x < $cntitemsfound ) {
 689             my $bitdata = $biblioitems[$x];
 690             my $sth2    = $dbh->prepare(
 691                 "SELECT * FROM items
 692                      WHERE biblioitemnumber = ?"
 693             );
 694             $sth2->execute( $bitdata->{'biblioitemnumber'} );
 695             while ( my $itdata = $sth2->fetchrow_hashref ) {
 696                 my $sth3 = $dbh->prepare(
 697                     "SELECT * FROM issues
 698                        WHERE itemnumber = ?"
 699                 );
 700                 $sth3->execute( $itdata->{'itemnumber'} );
 701                 if ( my $isdata = $sth3->fetchrow_hashref ) {
 702                 }
 703                 else {
 704                     $allissued = 0;
 705                 }
 706             }
 707             $x++;
 708         }
 709         if ( $allissued == 0 ) {
 710             my $rsth =
 711               $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
 712             $rsth->execute($biblionumber);
 713             if ( my $rdata = $rsth->fetchrow_hashref ) {
 714             }
 715             else {
 716                 $fee = 0;
 717             }
 718         }
 719     }
 720     return $fee;
 721 }
 722
 723 =head2 GetReservesToBranch
 724
 725   @transreserv = GetReservesToBranch( $frombranch );
 726
 727 Get reserve list for a given branch
 728
 729 =cut
 730
 731 sub GetReservesToBranch {
 732     my ( $frombranch ) = @_;
 733     my $dbh = C4::Context->dbh;
 734     my $sth = $dbh->prepare(
 735         "SELECT borrowernumber,reservedate,itemnumber,timestamp
 736          FROM reserves
 737          WHERE priority='0'
 738            AND branchcode=?"
 739     );
 740     $sth->execute( $frombranch );
 741     my @transreserv;
 742     my $i = 0;
 743     while ( my $data = $sth->fetchrow_hashref ) {
 744         $transreserv[$i] = $data;
 745         $i++;
 746     }
 747     return (@transreserv);
 748 }
 749
 750 =head2 GetReservesForBranch
 751
 752   @transreserv = GetReservesForBranch($frombranch);
 753
 754 =cut
 755
 756 sub GetReservesForBranch {
 757     my ($frombranch) = @_;
 758     my $dbh          = C4::Context->dbh;
 759         my $query        = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
 760         FROM   reserves
 761         WHERE   priority='0'
 762             AND found='W' ";
 763     if ($frombranch){
 764         $query .= " AND branchcode=? ";
 765         }
 766     $query .= "ORDER BY waitingdate" ;
 767     my $sth = $dbh->prepare($query);
 768     if ($frombranch){
 769                 $sth->execute($frombranch);
 770         }
 771     else {
 772                 $sth->execute();
 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 sub GetReserveStatus {
 784     my ($itemnumber) = @_;
 785
 786     my $dbh = C4::Context->dbh;
 787
 788     my $itemstatus = $dbh->prepare("SELECT found FROM reserves WHERE itemnumber = ?");
 789
 790     $itemstatus->execute($itemnumber);
 791     my ($found) = $itemstatus->fetchrow_array;
 792     return $found;
 793 }
 794
 795 =head2 CheckReserves
 796
 797   ($status, $reserve) = &CheckReserves($itemnumber);
 798   ($status, $reserve) = &CheckReserves(undef, $barcode);
 799
 800 Find a book in the reserves.
 801
 802 C<$itemnumber> is the book's item number.
 803
 804 As I understand it, C<&CheckReserves> looks for the given item in the
 805 reserves. If it is found, that's a match, and C<$status> is set to
 806 C<Waiting>.
 807
 808 Otherwise, it finds the most important item in the reserves with the
 809 same biblio number as this book (I'm not clear on this) and returns it
 810 with C<$status> set to C<Reserved>.
 811
 812 C<&CheckReserves> returns a two-element list:
 813
 814 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
 815
 816 C<$reserve> is the reserve item that matched. It is a
 817 reference-to-hash whose keys are mostly the fields of the reserves
 818 table in the Koha database.
 819
 820 =cut
 821
 822 sub CheckReserves {
 823     my ( $item, $barcode ) = @_;
 824     my $dbh = C4::Context->dbh;
 825     my $sth;
 826     my $select = "
 827     SELECT items.biblionumber,
 828            items.biblioitemnumber,
 829            itemtypes.notforloan,
 830            items.notforloan AS itemnotforloan,
 831            items.itemnumber
 832     FROM   items
 833     LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 834     LEFT JOIN itemtypes   ON biblioitems.itemtype   = itemtypes.itemtype
 835     ";
 836
 837     if ($item) {
 838         $sth = $dbh->prepare("$select WHERE itemnumber = ?");
 839         $sth->execute($item);
 840     }
 841     else {
 842         $sth = $dbh->prepare("$select WHERE barcode = ?");
 843         $sth->execute($barcode);
 844     }
 845     # note: we get the itemnumber because we might have started w/ just the barcode.  Now we know for sure we have it.
 846     my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
 847
 848     return ( 0, 0 ) unless $itemnumber; # bail if we got nothing.
 849
 850     # if item is not for loan it cannot be reserved either.....
 851     #    execpt where items.notforloan < 0 :  This indicates the item is holdable.
 852     return ( 0, 0 ) if  ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
 853
 854     # Find this item in the reserves
 855     my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
 856
 857     # $priority and $highest are used to find the most important item
 858     # in the list returned by &_Findgroupreserve. (The lower $priority,
 859     # the more important the item.)
 860     # $highest is the most important item we've seen so far.
 861     my $highest;
 862     if (scalar @reserves) {
 863         my $priority = 10000000;
 864         foreach my $res (@reserves) {
 865             if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
 866                 return ( "Waiting", $res ); # Found it
 867             } else {
 868                 # See if this item is more important than what we've got so far
 869                 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
 870                     $priority = $res->{'priority'};
 871                     $highest  = $res;
 872                 }
 873             }
 874         }
 875     }
 876
 877     # If we get this far, then no exact match was found.
 878     # We return the most important (i.e. next) reservation.
 879     if ($highest) {
 880         $highest->{'itemnumber'} = $item;
 881         return ( "Reserved", $highest );
 882     }
 883     else {
 884         return ( 0, 0 );
 885     }
 886 }
 887
 888 =head2 CancelExpiredReserves
 889
 890   CancelExpiredReserves();
 891
 892 Cancels all reserves with an expiration date from before today.
 893
 894 =cut
 895
 896 sub CancelExpiredReserves {
 897
 898     my $dbh = C4::Context->dbh;
 899     my $sth = $dbh->prepare( "
 900         SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
 901         AND expirationdate IS NOT NULL
 902     " );
 903     $sth->execute();
 904
 905     while ( my $res = $sth->fetchrow_hashref() ) {
 906         CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
 907     }
 908
 909 }
 910
 911 =head2 CancelReserve
 912
 913   &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
 914
 915 Cancels a reserve.
 916
 917 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
 918 cancel, but not both: if both are given, C<&CancelReserve> does
 919 nothing.
 920
 921 C<$borrowernumber> is the borrower number of the patron on whose
 922 behalf the book was reserved.
 923
 924 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
 925 priorities of the other people who are waiting on the book.
 926
 927 =cut
 928
 929 sub CancelReserve {
 930     my ( $biblio, $item, $borr ) = @_;
 931     my $dbh = C4::Context->dbh;
 932         if ( $item and $borr ) {
 933         # removing a waiting reserve record....
 934         # update the database...
 935         my $query = "
 936             UPDATE reserves
 937             SET    cancellationdate = now(),
 938                    found            = Null,
 939                    priority         = 0
 940             WHERE  itemnumber       = ?
 941              AND   borrowernumber   = ?
 942         ";
 943         my $sth = $dbh->prepare($query);
 944         $sth->execute( $item, $borr );
 945         $sth->finish;
 946         $query = "
 947             INSERT INTO old_reserves
 948             SELECT * FROM reserves
 949             WHERE  itemnumber       = ?
 950              AND   borrowernumber   = ?
 951         ";
 952         $sth = $dbh->prepare($query);
 953         $sth->execute( $item, $borr );
 954         $query = "
 955             DELETE FROM reserves
 956             WHERE  itemnumber       = ?
 957              AND   borrowernumber   = ?
 958         ";
 959         $sth = $dbh->prepare($query);
 960         $sth->execute( $item, $borr );
 961     }
 962     else {
 963         # removing a reserve record....
 964         # get the prioritiy on this record....
 965         my $priority;
 966         my $query = qq/
 967             SELECT priority FROM reserves
 968             WHERE biblionumber   = ?
 969               AND borrowernumber = ?
 970               AND cancellationdate IS NULL
 971               AND itemnumber IS NULL
 972         /;
 973         my $sth = $dbh->prepare($query);
 974         $sth->execute( $biblio, $borr );
 975         ($priority) = $sth->fetchrow_array;
 976         $sth->finish;
 977         $query = qq/
 978             UPDATE reserves
 979             SET    cancellationdate = now(),
 980                    found            = Null,
 981                    priority         = 0
 982             WHERE  biblionumber     = ?
 983               AND  borrowernumber   = ?
 984         /;
 985
 986         # update the database, removing the record...
 987         $sth = $dbh->prepare($query);
 988         $sth->execute( $biblio, $borr );
 989         $sth->finish;
 990
 991         $query = qq/
 992             INSERT INTO old_reserves
 993             SELECT * FROM reserves
 994             WHERE  biblionumber     = ?
 995               AND  borrowernumber   = ?
 996         /;
 997         $sth = $dbh->prepare($query);
 998         $sth->execute( $biblio, $borr );
 999
1000         $query = qq/
1001             DELETE FROM reserves
1002             WHERE  biblionumber     = ?
1003               AND  borrowernumber   = ?
1004         /;
1005         $sth = $dbh->prepare($query);
1006         $sth->execute( $biblio, $borr );
1007
1008         # now fix the priority on the others....
1009         _FixPriority( $biblio, $borr );
1010     }
1011 }
1012
1013 =head2 ModReserve
1014
1015   ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
1016
1017 Change a hold request's priority or cancel it.
1018
1019 C<$rank> specifies the effect of the change.  If C<$rank>
1020 is 'W' or 'n', nothing happens.  This corresponds to leaving a
1021 request alone when changing its priority in the holds queue
1022 for a bib.
1023
1024 If C<$rank> is 'del', the hold request is cancelled.
1025
1026 If C<$rank> is an integer greater than zero, the priority of
1027 the request is set to that value.  Since priority != 0 means
1028 that the item is not waiting on the hold shelf, setting the
1029 priority to a non-zero value also sets the request's found
1030 status and waiting date to NULL.
1031
1032 The optional C<$itemnumber> parameter is used only when
1033 C<$rank> is a non-zero integer; if supplied, the itemnumber
1034 of the hold request is set accordingly; if omitted, the itemnumber
1035 is cleared.
1036
1037 B<FIXME:> Note that the forgoing can have the effect of causing
1038 item-level hold requests to turn into title-level requests.  This
1039 will be fixed once reserves has separate columns for requested
1040 itemnumber and supplying itemnumber.
1041
1042 =cut
1043
1044 sub ModReserve {
1045     #subroutine to update a reserve
1046     my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1047      return if $rank eq "W";
1048      return if $rank eq "n";
1049     my $dbh = C4::Context->dbh;
1050     if ( $rank eq "del" ) {
1051         my $query = qq/
1052             UPDATE reserves
1053             SET    cancellationdate=now()
1054             WHERE  biblionumber   = ?
1055              AND   borrowernumber = ?
1056         /;
1057         my $sth = $dbh->prepare($query);
1058         $sth->execute( $biblio, $borrower );
1059         $sth->finish;
1060         $query = qq/
1061             INSERT INTO old_reserves
1062             SELECT *
1063             FROM   reserves
1064             WHERE  biblionumber   = ?
1065              AND   borrowernumber = ?
1066         /;
1067         $sth = $dbh->prepare($query);
1068         $sth->execute( $biblio, $borrower );
1069         $query = qq/
1070             DELETE FROM reserves
1071             WHERE  biblionumber   = ?
1072              AND   borrowernumber = ?
1073         /;
1074         $sth = $dbh->prepare($query);
1075         $sth->execute( $biblio, $borrower );
1076
1077     }
1078     elsif ($rank =~ /^\d+/ and $rank > 0) {
1079         my $query = qq/
1080         UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1081             WHERE biblionumber   = ?
1082              AND borrowernumber = ?
1083         /;
1084         my $sth = $dbh->prepare($query);
1085         $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1086         $sth->finish;
1087         _FixPriority( $biblio, $borrower, $rank);
1088     }
1089 }
1090
1091 =head2 ModReserveFill
1092
1093   &ModReserveFill($reserve);
1094
1095 Fill a reserve. If I understand this correctly, this means that the
1096 reserved book has been found and given to the patron who reserved it.
1097
1098 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1099 whose keys are fields from the reserves table in the Koha database.
1100
1101 =cut
1102
1103 sub ModReserveFill {
1104     my ($res) = @_;
1105     my $dbh = C4::Context->dbh;
1106     # fill in a reserve record....
1107     my $biblionumber = $res->{'biblionumber'};
1108     my $borrowernumber    = $res->{'borrowernumber'};
1109     my $resdate = $res->{'reservedate'};
1110
1111     # get the priority on this record....
1112     my $priority;
1113     my $query = "SELECT priority
1114                  FROM   reserves
1115                  WHERE  biblionumber   = ?
1116                   AND   borrowernumber = ?
1117                   AND   reservedate    = ?";
1118     my $sth = $dbh->prepare($query);
1119     $sth->execute( $biblionumber, $borrowernumber, $resdate );
1120     ($priority) = $sth->fetchrow_array;
1121     $sth->finish;
1122
1123     # update the database...
1124     $query = "UPDATE reserves
1125                   SET    found            = 'F',
1126                          priority         = 0
1127                  WHERE  biblionumber     = ?
1128                     AND reservedate      = ?
1129                     AND borrowernumber   = ?
1130                 ";
1131     $sth = $dbh->prepare($query);
1132     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1133     $sth->finish;
1134
1135     # move to old_reserves
1136     $query = "INSERT INTO old_reserves
1137                  SELECT * FROM reserves
1138                  WHERE  biblionumber     = ?
1139                     AND reservedate      = ?
1140                     AND borrowernumber   = ?
1141                 ";
1142     $sth = $dbh->prepare($query);
1143     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1144     $query = "DELETE FROM reserves
1145                  WHERE  biblionumber     = ?
1146                     AND reservedate      = ?
1147                     AND borrowernumber   = ?
1148                 ";
1149     $sth = $dbh->prepare($query);
1150     $sth->execute( $biblionumber, $resdate, $borrowernumber );
1151
1152     # now fix the priority on the others (if the priority wasn't
1153     # already sorted!)....
1154     unless ( $priority == 0 ) {
1155         _FixPriority( $biblionumber, $borrowernumber );
1156     }
1157 }
1158
1159 =head2 ModReserveStatus
1160
1161   &ModReserveStatus($itemnumber, $newstatus);
1162
1163 Update the reserve status for the active (priority=0) reserve.
1164
1165 $itemnumber is the itemnumber the reserve is on
1166
1167 $newstatus is the new status.
1168
1169 =cut
1170
1171 sub ModReserveStatus {
1172
1173     #first : check if we have a reservation for this item .
1174     my ($itemnumber, $newstatus) = @_;
1175     my $dbh          = C4::Context->dbh;
1176     my $query = " UPDATE reserves
1177     SET    found=?,waitingdate = now()
1178     WHERE itemnumber=?
1179       AND found IS NULL
1180       AND priority = 0
1181     ";
1182     my $sth_set = $dbh->prepare($query);
1183     $sth_set->execute( $newstatus, $itemnumber );
1184
1185     if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1186       CartToShelf( $itemnumber );
1187     }
1188 }
1189
1190 =head2 ModReserveAffect
1191
1192   &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1193
1194 This function affect an item and a status for a given reserve
1195 The itemnumber parameter is used to find the biblionumber.
1196 with the biblionumber & the borrowernumber, we can affect the itemnumber
1197 to the correct reserve.
1198
1199 if $transferToDo is not set, then the status is set to "Waiting" as well.
1200 otherwise, a transfer is on the way, and the end of the transfer will
1201 take care of the waiting status
1202
1203 =cut
1204
1205 sub ModReserveAffect {
1206     my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1207     my $dbh = C4::Context->dbh;
1208
1209     # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1210     # attached to $itemnumber
1211     my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1212     $sth->execute($itemnumber);
1213     my ($biblionumber) = $sth->fetchrow;
1214
1215     # get request - need to find out if item is already
1216     # waiting in order to not send duplicate hold filled notifications
1217     my $request = GetReserveInfo($borrowernumber, $biblionumber);
1218     my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1219
1220     # If we affect a reserve that has to be transfered, don't set to Waiting
1221     my $query;
1222     if ($transferToDo) {
1223     $query = "
1224         UPDATE reserves
1225         SET    priority = 0,
1226                itemnumber = ?,
1227                found = 'T'
1228         WHERE borrowernumber = ?
1229           AND biblionumber = ?
1230     ";
1231     }
1232     else {
1233     # affect the reserve to Waiting as well.
1234     $query = "
1235         UPDATE reserves
1236         SET     priority = 0,
1237                 found = 'W',
1238                 waitingdate=now(),
1239                 itemnumber = ?
1240         WHERE borrowernumber = ?
1241           AND biblionumber = ?
1242     ";
1243     }
1244     $sth = $dbh->prepare($query);
1245     $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1246     _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1247
1248     if ( C4::Context->preference("ReturnToShelvingCart") ) {
1249       CartToShelf( $itemnumber );
1250     }
1251
1252     return;
1253 }
1254
1255 =head2 ModReserveCancelAll
1256
1257   ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1258
1259 function to cancel reserv,check other reserves, and transfer document if it's necessary
1260
1261 =cut
1262
1263 sub ModReserveCancelAll {
1264     my $messages;
1265     my $nextreservinfo;
1266     my ( $itemnumber, $borrowernumber ) = @_;
1267
1268     #step 1 : cancel the reservation
1269     my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1270
1271     #step 2 launch the subroutine of the others reserves
1272     ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1273
1274     return ( $messages, $nextreservinfo );
1275 }
1276
1277 =head2 ModReserveMinusPriority
1278
1279   &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1280
1281 Reduce the values of queuded list
1282
1283 =cut
1284
1285 sub ModReserveMinusPriority {
1286     my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1287
1288     #first step update the value of the first person on reserv
1289     my $dbh   = C4::Context->dbh;
1290     my $query = "
1291         UPDATE reserves
1292         SET    priority = 0 , itemnumber = ?
1293         WHERE  borrowernumber=?
1294           AND  biblionumber=?
1295     ";
1296     my $sth_upd = $dbh->prepare($query);
1297     $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1298     # second step update all others reservs
1299     _FixPriority($biblionumber, $borrowernumber, '0');
1300 }
1301
1302 =head2 GetReserveInfo
1303
1304   &GetReserveInfo($borrowernumber,$biblionumber);
1305
1306 Get item and borrower details for a current hold.
1307 Current implementation this query should have a single result.
1308
1309 =cut
1310
1311 sub GetReserveInfo {
1312         my ( $borrowernumber, $biblionumber ) = @_;
1313     my $dbh = C4::Context->dbh;
1314         my $strsth="SELECT
1315                        reservedate,
1316                        reservenotes,
1317                        reserves.borrowernumber,
1318                                    reserves.biblionumber,
1319                                    reserves.branchcode,
1320                                    reserves.waitingdate,
1321                                    notificationdate,
1322                                    reminderdate,
1323                                    priority,
1324                                    found,
1325                                    firstname,
1326                                    surname,
1327                                    phone,
1328                                    email,
1329                                    address,
1330                                    address2,
1331                                    cardnumber,
1332                                    city,
1333                                    zipcode,
1334                                    biblio.title,
1335                                    biblio.author,
1336                                    items.holdingbranch,
1337                                    items.itemcallnumber,
1338                                    items.itemnumber,
1339                                    items.location,
1340                                    barcode,
1341                                    notes
1342                         FROM reserves
1343                          LEFT JOIN items USING(itemnumber)
1344                      LEFT JOIN borrowers USING(borrowernumber)
1345                      LEFT JOIN biblio ON  (reserves.biblionumber=biblio.biblionumber)
1346                         WHERE
1347                                 reserves.borrowernumber=?
1348                                 AND reserves.biblionumber=?";
1349         my $sth = $dbh->prepare($strsth);
1350         $sth->execute($borrowernumber,$biblionumber);
1351
1352         my $data = $sth->fetchrow_hashref;
1353         return $data;
1354
1355 }
1356
1357 =head2 IsAvailableForItemLevelRequest
1358
1359   my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1360
1361 Checks whether a given item record is available for an
1362 item-level hold request.  An item is available if
1363
1364 * it is not lost AND
1365 * it is not damaged AND
1366 * it is not withdrawn AND
1367 * does not have a not for loan value > 0
1368
1369 Whether or not the item is currently on loan is
1370 also checked - if the AllowOnShelfHolds system preference
1371 is ON, an item can be requested even if it is currently
1372 on loan to somebody else.  If the system preference
1373 is OFF, an item that is currently checked out cannot
1374 be the target of an item-level hold request.
1375
1376 Note that IsAvailableForItemLevelRequest() does not
1377 check if the staff operator is authorized to place
1378 a request on the item - in particular,
1379 this routine does not check IndependantBranches
1380 and canreservefromotherbranches.
1381
1382 =cut
1383
1384 sub IsAvailableForItemLevelRequest {
1385     my $itemnumber = shift;
1386
1387     my $item = GetItem($itemnumber);
1388
1389     # must check the notforloan setting of the itemtype
1390     # FIXME - a lot of places in the code do this
1391     #         or something similar - need to be
1392     #         consolidated
1393     my $dbh = C4::Context->dbh;
1394     my $notforloan_query;
1395     if (C4::Context->preference('item-level_itypes')) {
1396         $notforloan_query = "SELECT itemtypes.notforloan
1397                              FROM items
1398                              JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1399                              WHERE itemnumber = ?";
1400     } else {
1401         $notforloan_query = "SELECT itemtypes.notforloan
1402                              FROM items
1403                              JOIN biblioitems USING (biblioitemnumber)
1404                              JOIN itemtypes USING (itemtype)
1405                              WHERE itemnumber = ?";
1406     }
1407     my $sth = $dbh->prepare($notforloan_query);
1408     $sth->execute($itemnumber);
1409     my $notforloan_per_itemtype = 0;
1410     if (my ($notforloan) = $sth->fetchrow_array) {
1411         $notforloan_per_itemtype = 1 if $notforloan;
1412     }
1413
1414     my $available_per_item = 1;
1415     $available_per_item = 0 if $item->{itemlost} or
1416                                ( $item->{notforloan} > 0 ) or
1417                                ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1418                                $item->{wthdrawn} or
1419                                $notforloan_per_itemtype;
1420
1421
1422     if (C4::Context->preference('AllowOnShelfHolds')) {
1423         return $available_per_item;
1424     } else {
1425         return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "W"));
1426     }
1427 }
1428
1429 =head2 AlterPriority
1430
1431   AlterPriority( $where, $borrowernumber, $biblionumber, $reservedate );
1432
1433 This function changes a reserve's priority up, down, to the top, or to the bottom.
1434 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1435
1436 =cut
1437
1438 sub AlterPriority {
1439     my ( $where, $borrowernumber, $biblionumber ) = @_;
1440
1441     my $dbh = C4::Context->dbh;
1442
1443     ## Find this reserve
1444     my $sth = $dbh->prepare('SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? AND cancellationdate IS NULL');
1445     $sth->execute( $biblionumber, $borrowernumber );
1446     my $reserve = $sth->fetchrow_hashref();
1447     $sth->finish();
1448
1449     if ( $where eq 'up' || $where eq 'down' ) {
1450
1451       my $priority = $reserve->{'priority'};
1452       $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1453       _FixPriority( $biblionumber, $borrowernumber, $priority )
1454
1455     } elsif ( $where eq 'top' ) {
1456
1457       _FixPriority( $biblionumber, $borrowernumber, '1' )
1458
1459     } elsif ( $where eq 'bottom' ) {
1460
1461       _FixPriority( $biblionumber, $borrowernumber, '999999' )
1462
1463     }
1464 }
1465
1466 =head2 ToggleLowestPriority
1467
1468   ToggleLowestPriority( $borrowernumber, $biblionumber );
1469
1470 This function sets the lowestPriority field to true if is false, and false if it is true.
1471
1472 =cut
1473
1474 sub ToggleLowestPriority {
1475     my ( $borrowernumber, $biblionumber ) = @_;
1476
1477     my $dbh = C4::Context->dbh;
1478
1479     my $sth = $dbh->prepare(
1480         "UPDATE reserves SET lowestPriority = NOT lowestPriority
1481          WHERE biblionumber = ?
1482          AND borrowernumber = ?"
1483     );
1484     $sth->execute(
1485         $biblionumber,
1486         $borrowernumber,
1487     );
1488     $sth->finish;
1489
1490     _FixPriority( $biblionumber, $borrowernumber, '999999' );
1491 }
1492
1493 =head2 _FixPriority
1494
1495   &_FixPriority($biblio,$borrowernumber,$rank,$ignoreSetLowestRank);
1496
1497 Only used internally (so don't export it)
1498 Changed how this functions works #
1499 Now just gets an array of reserves in the rank order and updates them with
1500 the array index (+1 as array starts from 0)
1501 and if $rank is supplied will splice item from the array and splice it back in again
1502 in new priority rank
1503
1504 =cut
1505
1506 sub _FixPriority {
1507     my ( $biblio, $borrowernumber, $rank, $ignoreSetLowestRank ) = @_;
1508     my $dbh = C4::Context->dbh;
1509      if ( $rank eq "del" ) {
1510          CancelReserve( $biblio, undef, $borrowernumber );
1511      }
1512     if ( $rank eq "W" || $rank eq "0" ) {
1513
1514         # make sure priority for waiting or in-transit items is 0
1515         my $query = qq/
1516             UPDATE reserves
1517             SET    priority = 0
1518             WHERE biblionumber = ?
1519               AND borrowernumber = ?
1520               AND found IN ('W', 'T')
1521         /;
1522         my $sth = $dbh->prepare($query);
1523         $sth->execute( $biblio, $borrowernumber );
1524     }
1525     my @priority;
1526     my @reservedates;
1527
1528     # get whats left
1529 # FIXME adding a new security in returned elements for changing priority,
1530 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1531         # This is wrong a waiting reserve has W set
1532         # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1533     my $query = qq/
1534         SELECT borrowernumber, reservedate, constrainttype
1535         FROM   reserves
1536         WHERE  biblionumber   = ?
1537           AND  ((found <> 'W' AND found <> 'T') or found is NULL)
1538         ORDER BY priority ASC
1539     /;
1540     my $sth = $dbh->prepare($query);
1541     $sth->execute($biblio);
1542     while ( my $line = $sth->fetchrow_hashref ) {
1543         push( @reservedates, $line );
1544         push( @priority,     $line );
1545     }
1546
1547     # To find the matching index
1548     my $i;
1549     my $key = -1;    # to allow for 0 to be a valid result
1550     for ( $i = 0 ; $i < @priority ; $i++ ) {
1551         if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1552             $key = $i;    # save the index
1553             last;
1554         }
1555     }
1556
1557     # if index exists in array then move it to new position
1558     if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1559         my $new_rank = $rank -
1560           1;    # $new_rank is what you want the new index to be in the array
1561         my $moving_item = splice( @priority, $key, 1 );
1562         splice( @priority, $new_rank, 0, $moving_item );
1563     }
1564
1565     # now fix the priority on those that are left....
1566     $query = "
1567             UPDATE reserves
1568             SET    priority = ?
1569                 WHERE  biblionumber = ?
1570                  AND borrowernumber   = ?
1571                  AND reservedate = ?
1572          AND found IS NULL
1573     ";
1574     $sth = $dbh->prepare($query);
1575     for ( my $j = 0 ; $j < @priority ; $j++ ) {
1576         $sth->execute(
1577             $j + 1, $biblio,
1578             $priority[$j]->{'borrowernumber'},
1579             $priority[$j]->{'reservedate'}
1580         );
1581         $sth->finish;
1582     }
1583
1584     $sth = $dbh->prepare( "SELECT borrowernumber FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1585     $sth->execute();
1586
1587     unless ( $ignoreSetLowestRank ) {
1588       while ( my $res = $sth->fetchrow_hashref() ) {
1589         _FixPriority( $biblio, $res->{'borrowernumber'}, '999999', 1 );
1590       }
1591     }
1592 }
1593
1594 =head2 _Findgroupreserve
1595
1596   @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1597
1598 Looks for an item-specific match first, then for a title-level match, returning the
1599 first match found.  If neither, then we look for a 3rd kind of match based on
1600 reserve constraints.
1601
1602 TODO: add more explanation about reserve constraints
1603
1604 C<&_Findgroupreserve> returns :
1605 C<@results> is an array of references-to-hash whose keys are mostly
1606 fields from the reserves table of the Koha database, plus
1607 C<biblioitemnumber>.
1608
1609 =cut
1610
1611 sub _Findgroupreserve {
1612     my ( $bibitem, $biblio, $itemnumber ) = @_;
1613     my $dbh   = C4::Context->dbh;
1614
1615     # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1616     # check for exact targetted match
1617     my $item_level_target_query = qq/
1618         SELECT reserves.biblionumber        AS biblionumber,
1619                reserves.borrowernumber      AS borrowernumber,
1620                reserves.reservedate         AS reservedate,
1621                reserves.branchcode          AS branchcode,
1622                reserves.cancellationdate    AS cancellationdate,
1623                reserves.found               AS found,
1624                reserves.reservenotes        AS reservenotes,
1625                reserves.priority            AS priority,
1626                reserves.timestamp           AS timestamp,
1627                biblioitems.biblioitemnumber AS biblioitemnumber,
1628                reserves.itemnumber          AS itemnumber
1629         FROM reserves
1630         JOIN biblioitems USING (biblionumber)
1631         JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1632         WHERE found IS NULL
1633         AND priority > 0
1634         AND item_level_request = 1
1635         AND itemnumber = ?
1636         AND reservedate <= CURRENT_DATE()
1637     /;
1638     my $sth = $dbh->prepare($item_level_target_query);
1639     $sth->execute($itemnumber);
1640     my @results;
1641     if ( my $data = $sth->fetchrow_hashref ) {
1642         push( @results, $data );
1643     }
1644     return @results if @results;
1645
1646     # check for title-level targetted match
1647     my $title_level_target_query = qq/
1648         SELECT reserves.biblionumber        AS biblionumber,
1649                reserves.borrowernumber      AS borrowernumber,
1650                reserves.reservedate         AS reservedate,
1651                reserves.branchcode          AS branchcode,
1652                reserves.cancellationdate    AS cancellationdate,
1653                reserves.found               AS found,
1654                reserves.reservenotes        AS reservenotes,
1655                reserves.priority            AS priority,
1656                reserves.timestamp           AS timestamp,
1657                biblioitems.biblioitemnumber AS biblioitemnumber,
1658                reserves.itemnumber          AS itemnumber
1659         FROM reserves
1660         JOIN biblioitems USING (biblionumber)
1661         JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1662         WHERE found IS NULL
1663         AND priority > 0
1664         AND item_level_request = 0
1665         AND hold_fill_targets.itemnumber = ?
1666         AND reservedate <= CURRENT_DATE()
1667     /;
1668     $sth = $dbh->prepare($title_level_target_query);
1669     $sth->execute($itemnumber);
1670     @results = ();
1671     if ( my $data = $sth->fetchrow_hashref ) {
1672         push( @results, $data );
1673     }
1674     return @results if @results;
1675
1676     my $query = qq/
1677         SELECT reserves.biblionumber               AS biblionumber,
1678                reserves.borrowernumber             AS borrowernumber,
1679                reserves.reservedate                AS reservedate,
1680                reserves.branchcode                 AS branchcode,
1681                reserves.cancellationdate           AS cancellationdate,
1682                reserves.found                      AS found,
1683                reserves.reservenotes               AS reservenotes,
1684                reserves.priority                   AS priority,
1685                reserves.timestamp                  AS timestamp,
1686                reserveconstraints.biblioitemnumber AS biblioitemnumber,
1687                reserves.itemnumber                 AS itemnumber
1688         FROM reserves
1689           LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1690         WHERE reserves.biblionumber = ?
1691           AND ( ( reserveconstraints.biblioitemnumber = ?
1692           AND reserves.borrowernumber = reserveconstraints.borrowernumber
1693           AND reserves.reservedate    = reserveconstraints.reservedate )
1694           OR  reserves.constrainttype='a' )
1695           AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1696           AND reserves.reservedate <= CURRENT_DATE()
1697     /;
1698     $sth = $dbh->prepare($query);
1699     $sth->execute( $biblio, $bibitem, $itemnumber );
1700     @results = ();
1701     while ( my $data = $sth->fetchrow_hashref ) {
1702         push( @results, $data );
1703     }
1704     return @results;
1705 }
1706
1707 =head2 _koha_notify_reserve
1708
1709   _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1710
1711 Sends a notification to the patron that their hold has been filled (through
1712 ModReserveAffect, _not_ ModReserveFill)
1713
1714 =cut
1715
1716 sub _koha_notify_reserve {
1717     my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1718
1719     my $dbh = C4::Context->dbh;
1720     my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1721     my $letter_code;
1722     my $print_mode = 0;
1723     my $messagingprefs;
1724     if ( $borrower->{'email'} || $borrower->{'smsalertnumber'} ) {
1725         $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1726
1727         return if ( !defined( $messagingprefs->{'letter_code'} ) );
1728         $letter_code = $messagingprefs->{'letter_code'};
1729     } else {
1730         $letter_code = 'HOLD_PRINT';
1731         $print_mode = 1;
1732     }
1733
1734     my $sth = $dbh->prepare("
1735         SELECT *
1736         FROM   reserves
1737         WHERE  borrowernumber = ?
1738             AND biblionumber = ?
1739     ");
1740     $sth->execute( $borrowernumber, $biblionumber );
1741     my $reserve = $sth->fetchrow_hashref;
1742     my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1743
1744     my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1745
1746     my $letter = getletter( 'reserves', $letter_code );
1747     die "Could not find a letter called '$letter_code' in the 'reserves' module" unless( $letter );
1748
1749     C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1750     C4::Letters::parseletter( $letter, 'borrowers', $borrowernumber );
1751     C4::Letters::parseletter( $letter, 'biblio', $biblionumber );
1752     C4::Letters::parseletter( $letter, 'reserves', $borrowernumber, $biblionumber );
1753
1754     if ( $reserve->{'itemnumber'} ) {
1755         C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1756     }
1757     my $today = C4::Dates->new()->output();
1758     $letter->{'title'} =~ s/<<today>>/$today/g;
1759     $letter->{'content'} =~ s/<<today>>/$today/g;
1760     $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1761
1762     if ( $print_mode ) {
1763         C4::Letters::EnqueueLetter( {
1764             letter => $letter,
1765             borrowernumber => $borrowernumber,
1766             message_transport_type => 'print',
1767         } );
1768
1769         return;
1770     }
1771
1772     if ( grep { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1773         # aka, 'email' in ->{'transports'}
1774         C4::Letters::EnqueueLetter(
1775             {   letter                 => $letter,
1776                 borrowernumber         => $borrowernumber,
1777                 message_transport_type => 'email',
1778                 from_address           => $admin_email_address,
1779             }
1780         );
1781     }
1782
1783     if ( grep { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1784         C4::Letters::EnqueueLetter(
1785             {   letter                 => $letter,
1786                 borrowernumber         => $borrowernumber,
1787                 message_transport_type => 'sms',
1788             }
1789         );
1790     }
1791 }
1792
1793 =head2 _ShiftPriorityByDateAndPriority
1794
1795   $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1796
1797 This increments the priority of all reserves after the one
1798 with either the lowest date after C<$reservedate>
1799 or the lowest priority after C<$priority>.
1800
1801 It effectively makes room for a new reserve to be inserted with a certain
1802 priority, which is returned.
1803
1804 This is most useful when the reservedate can be set by the user.  It allows
1805 the new reserve to be placed before other reserves that have a later
1806 reservedate.  Since priority also is set by the form in reserves/request.pl
1807 the sub accounts for that too.
1808
1809 =cut
1810
1811 sub _ShiftPriorityByDateAndPriority {
1812     my ( $biblio, $resdate, $new_priority ) = @_;
1813
1814     my $dbh = C4::Context->dbh;
1815     my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1816     my $sth = $dbh->prepare( $query );
1817     $sth->execute( $biblio, $resdate, $new_priority );
1818     my $min_priority = $sth->fetchrow;
1819     # if no such matches are found, $new_priority remains as original value
1820     $new_priority = $min_priority if ( $min_priority );
1821
1822     # Shift the priority up by one; works in conjunction with the next SQL statement
1823     $query = "UPDATE reserves
1824               SET priority = priority+1
1825               WHERE biblionumber = ?
1826               AND borrowernumber = ?
1827               AND reservedate = ?
1828               AND found IS NULL";
1829     my $sth_update = $dbh->prepare( $query );
1830
1831     # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1832     $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1833     $sth = $dbh->prepare( $query );
1834     $sth->execute( $new_priority, $biblio );
1835     while ( my $row = $sth->fetchrow_hashref ) {
1836         $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1837     }
1838
1839     return $new_priority;  # so the caller knows what priority they wind up receiving
1840 }
1841
1842 =head1 AUTHOR
1843
1844 Koha Development Team <http://koha-community.org/>
1845
1846 =cut
1847
1848 1;