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