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