File Coverage

File:C4/Reserves.pm
Coverage:6.7%

linestmtbrancondsubtimecode
1package C4::Reserves;
2
3# Copyright 2000-2002 Katipo Communications
4# 2006 SAN Ouest Provence
5# 2007-2010 BibLibre Paul POULAIN
6# 2011 Catalyst IT
7#
8# This file is part of Koha.
9#
10# Koha is free software; you can redistribute it and/or modify it under the
11# terms of the GNU General Public License as published by the Free Software
12# Foundation; either version 2 of the License, or (at your option) any later
13# version.
14#
15# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
16# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
17# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18#
19# You should have received a copy of the GNU General Public License along
20# with Koha; if not, write to the Free Software Foundation, Inc.,
21# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
22
23
24
24
24
24
127
62
866
use strict;
25#use warnings; FIXME - Bug 2505
26
24
24
24
156
78
793
use C4::Context;
27
24
24
24
4017
226
13069
use C4::Biblio;
28
24
24
24
2009
355
4794
use C4::Members;
29
24
24
24
5410
517
7182
use C4::Items;
30
24
24
24
479
282
985
use C4::Circulation;
31
24
24
24
3762
233
5102
use C4::Accounts;
32
33# for _koha_notify_reserve
34
24
24
24
5190
206
970
use C4::Members::Messaging;
35
24
24
24
222
114
479
use C4::Members qw();
36
24
24
24
4322
944
4926
use C4::Letters;
37
24
24
24
1745
457
2822
use C4::Branch qw( GetBranchDetail );
38
24
24
24
479
375
1588
use C4::Dates qw( format_date_in_iso );
39
24
24
24
397
299
1632
use List::MoreUtils qw( firstidx );
40
41
24
24
24
349
247
4932
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
42
43 - 84
=head1 NAME

C4::Reserves - Koha functions for dealing with reservation.

=head1 SYNOPSIS

  use C4::Reserves;

=head1 DESCRIPTION

This modules provides somes functions to deal with reservations.

  Reserves are stored in reserves table.
  The following columns contains important values :
  - priority >0      : then the reserve is at 1st stage, and not yet affected to any item.
             =0      : then the reserve is being dealed
  - found : NULL       : means the patron requested the 1st available, and we haven't choosen the item
            T(ransit)  : the reserve is linked to an item but is in transit to the pickup branch
            W(aiting)  : the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf
            F(inished) : the reserve has been completed, and is done
  - itemnumber : empty : the reserve is still unaffected to an item
                 filled: the reserve is attached to an item
  The complete workflow is :
  ==== 1st use case ====
  patron request a document, 1st available :                      P >0, F=NULL, I=NULL
  a library having it run "transfertodo", and clic on the list    
         if there is no transfer to do, the reserve waiting
         patron can pick it up                                    P =0, F=W,    I=filled 
         if there is a transfer to do, write in branchtransfer    P =0, F=T,    I=filled
           The pickup library recieve the book, it check in       P =0, F=W,    I=filled
  The patron borrow the book                                      P =0, F=F,    I=filled
  
  ==== 2nd use case ====
  patron requests a document, a given item,
    If pickup is holding branch                                   P =0, F=W,   I=filled
    If transfer needed, write in branchtransfer                   P =0, F=T,    I=filled
        The pickup library receive the book, it checks it in      P =0, F=W,    I=filled
  The patron borrow the book                                      P =0, F=F,    I=filled

=head1 FUNCTIONS

=cut
85
86BEGIN {
87    # set the version for version checking
88
24
253
    $VERSION = 3.01;
89
24
1133
    require Exporter;
90
24
475
    @ISA = qw(Exporter);
91
24
566
    @EXPORT = qw(
92        &AddReserve
93
94        &GetReservesFromItemnumber
95        &GetReservesFromBiblionumber
96        &GetReservesFromBorrowernumber
97        &GetReservesForBranch
98        &GetReservesToBranch
99        &GetReserveCount
100        &GetReserveFee
101                &GetReserveInfo
102        &GetReserveStatus
103
104        &GetOtherReserves
105
106        &ModReserveFill
107        &ModReserveAffect
108        &ModReserve
109        &ModReserveStatus
110        &ModReserveCancelAll
111        &ModReserveMinusPriority
112        &MoveReserve
113
114        &CheckReserves
115        &CanBookBeReserved
116        &CanItemBeReserved
117        &CancelReserve
118        &CancelExpiredReserves
119
120        &IsAvailableForItemLevelRequest
121
122        &AlterPriority
123        &ToggleLowestPriority
124    );
125
24
188942
    @EXPORT_OK = qw( MergeHolds );
126}
127
128 - 132
=head2 AddReserve

    AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)

=cut
133
134sub AddReserve {
135    my (
136
0
        $branch, $borrowernumber, $biblionumber,
137        $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
138        $title, $checkitem, $found
139    ) = @_;
140
0
    my $fee =
141          GetReserveFee($borrowernumber, $biblionumber, $constraint,
142            $bibitems );
143
0
    my $dbh = C4::Context->dbh;
144
0
    my $const = lc substr( $constraint, 0, 1 );
145
0
    $resdate = format_date_in_iso( $resdate ) if ( $resdate );
146
0
    $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
147
0
    if ($expdate) {
148
0
        $expdate = format_date_in_iso( $expdate );
149    } else {
150
0
        undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
151    }
152
0
    if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
153        # Make room in reserves for this before those of a later reserve date
154
0
        $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
155    }
156
0
    my $waitingdate;
157
158    # If the reserv had the waiting status, we had the value of the resdate
159
0
    if ( $found eq 'W' ) {
160
0
        $waitingdate = $resdate;
161    }
162
163    #eval {
164    # updates take place here
165
0
    if ( $fee > 0 ) {
166
0
        my $nextacctno = &getnextacctno( $borrowernumber );
167
0
        my $query = qq/
168        INSERT INTO accountlines
169            (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
170        VALUES
171            (?,?,now(),?,?,'Res',?)
172    /;
173
0
        my $usth = $dbh->prepare($query);
174
0
        $usth->execute( $borrowernumber, $nextacctno, $fee,
175            "Reserve Charge - $title", $fee );
176    }
177
178    #if ($const eq 'a'){
179
0
    my $query = qq/
180        INSERT INTO reserves
181            (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
182            priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
183        VALUES
184             (?,?,?,?,?,
185             ?,?,?,?,?,?)
186    /;
187
0
    my $sth = $dbh->prepare($query);
188
0
    $sth->execute(
189        $borrowernumber, $biblionumber, $resdate, $branch,
190        $const, $priority, $notes, $checkitem,
191        $found, $waitingdate, $expdate
192    );
193
194    # Send e-mail to librarian if syspref is active
195
0
    if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
196
0
        my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
197
0
        my $biblio = GetBiblioData($biblionumber);
198
0
        my $letter = C4::Letters::getletter( 'reserves', 'HOLDPLACED');
199
0
        my $branchcode = $borrower->{branchcode};
200
0
        my $branch_details = C4::Branch::GetBranchDetail($branchcode);
201
0
        my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
202
203
0
        my %keys = (%$borrower, %$biblio);
204
0
        foreach my $key (keys %keys) {
205
0
            my $replacefield = "<<$key>>";
206
0
            $letter->{content} =~ s/$replacefield/$keys{$key}/g;
207
0
            $letter->{title} =~ s/$replacefield/$keys{$key}/g;
208        }
209
210        C4::Letters::EnqueueLetter(
211
0
                            { letter => $letter,
212                                borrowernumber => $borrowernumber,
213                                message_transport_type => 'email',
214                                from_address => $admin_email_address,
215                                to_address => $admin_email_address,
216                            }
217                        );
218
219
220    }
221
222
223    #}
224
0
    ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
225
0
    $query = qq/
226        INSERT INTO reserveconstraints
227            (borrowernumber,biblionumber,reservedate,biblioitemnumber)
228        VALUES
229            (?,?,?,?)
230    /;
231
0
    $sth = $dbh->prepare($query); # keep prepare outside the loop!
232
0
    foreach (@$bibitems) {
233
0
        $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
234    }
235
236
0
    return; # FIXME: why not have a useful return value?
237}
238
239 - 246
=head2 GetReservesFromBiblionumber

  ($count, $title_reserves) = &GetReserves($biblionumber);

This function gets the list of reservations for one C<$biblionumber>, returning a count
of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.

=cut
247
248sub GetReservesFromBiblionumber {
249
0
    my ($biblionumber) = shift or return (0, []);
250
0
    my ($all_dates) = shift;
251
0
    my $dbh = C4::Context->dbh;
252
253    # Find the desired items in the reserves
254
0
    my $query = "
255        SELECT branchcode,
256                timestamp AS rtimestamp,
257                priority,
258                biblionumber,
259                borrowernumber,
260                reservedate,
261                constrainttype,
262                found,
263                itemnumber,
264                reservenotes,
265                expirationdate,
266                lowestPriority
267        FROM reserves
268        WHERE biblionumber = ? ";
269
0
    unless ( $all_dates ) {
270
0
        $query .= "AND reservedate <= CURRENT_DATE()";
271    }
272
0
    $query .= "ORDER BY priority";
273
0
    my $sth = $dbh->prepare($query);
274
0
    $sth->execute($biblionumber);
275
0
    my @results;
276
0
    my $i = 0;
277
0
    while ( my $data = $sth->fetchrow_hashref ) {
278
279        # FIXME - What is this doing? How do constraints work?
280
0
        if ($data->{constrainttype} eq 'o') {
281
0
            $query = '
282                SELECT biblioitemnumber
283                FROM reserveconstraints
284                WHERE biblionumber = ?
285                AND borrowernumber = ?
286                AND reservedate = ?
287            ';
288
0
            my $csth = $dbh->prepare($query);
289
0
            $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
290
0
            my @bibitemno;
291
0
            while ( my $bibitemnos = $csth->fetchrow_array ) {
292
0
                push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
293            }
294
0
            my $count = scalar @bibitemno;
295
296            # if we have two or more different specific itemtypes
297            # reserved by same person on same day
298
0
            my $bdata;
299
0
            if ( $count > 1 ) {
300
0
                $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
301
0
                $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
302            }
303            else {
304                # Look up the book we just found.
305
0
                $bdata = GetBiblioItemData( $bibitemno[0] );
306            }
307            # Add the results of this latest search to the current
308            # results.
309            # FIXME - An 'each' would probably be more efficient.
310
0
            foreach my $key ( keys %$bdata ) {
311
0
                $data->{$key} = $bdata->{$key};
312            }
313        }
314
0
        push @results, $data;
315    }
316
0
    return ( $#results + 1, \@results );
317}
318
319 - 325
=head2 GetReservesFromItemnumber

 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);

TODO :: Description here

=cut
326
327sub GetReservesFromItemnumber {
328
0
    my ( $itemnumber, $all_dates ) = @_;
329
0
    my $dbh = C4::Context->dbh;
330
0
    my $query = "
331    SELECT reservedate,borrowernumber,branchcode
332    FROM reserves
333    WHERE itemnumber=?
334    ";
335
0
    unless ( $all_dates ) {
336
0
        $query .= " AND reservedate <= CURRENT_DATE()";
337    }
338
0
    my $sth_res = $dbh->prepare($query);
339
0
    $sth_res->execute($itemnumber);
340
0
    my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
341
0
    return ( $reservedate, $borrowernumber, $branchcode );
342}
343
344 - 350
=head2 GetReservesFromBorrowernumber

    $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);

TODO :: Descritpion

=cut
351
352sub GetReservesFromBorrowernumber {
353
0
    my ( $borrowernumber, $status ) = @_;
354
0
    my $dbh = C4::Context->dbh;
355
0
    my $sth;
356
0
    if ($status) {
357
0
        $sth = $dbh->prepare("
358            SELECT *
359            FROM reserves
360            WHERE borrowernumber=?
361                AND found =?
362            ORDER BY reservedate
363        ");
364
0
        $sth->execute($borrowernumber,$status);
365    } else {
366
0
        $sth = $dbh->prepare("
367            SELECT *
368            FROM reserves
369            WHERE borrowernumber=?
370            ORDER BY reservedate
371        ");
372
0
        $sth->execute($borrowernumber);
373    }
374
0
    my $data = $sth->fetchall_arrayref({});
375
0
    return @$data;
376}
377#-------------------------------------------------------------------------------------
378 - 382
=head2 CanBookBeReserved

  $error = &CanBookBeReserved($borrowernumber, $biblionumber)

=cut
383
384sub CanBookBeReserved{
385
0
    my ($borrowernumber, $biblionumber) = @_;
386
387
0
    my @items = get_itemnumbers_of($biblionumber);
388    #get items linked via host records
389
0
    my @hostitems = get_hostitemnumbers_of($biblionumber);
390
0
    if (@hostitems){
391
0
        push (@items,@hostitems);
392    }
393
394
0
    foreach my $item (@items){
395
0
        return 1 if CanItemBeReserved($borrowernumber, $item);
396    }
397
0
    return 0;
398}
399
400 - 406
=head2 CanItemBeReserved

  $error = &CanItemBeReserved($borrowernumber, $itemnumber)

This function return 1 if an item can be issued by this borrower.

=cut
407
408sub CanItemBeReserved{
409
0
    my ($borrowernumber, $itemnumber) = @_;
410
411
0
    my $dbh = C4::Context->dbh;
412
0
    my $allowedreserves = 0;
413
414
0
    my $controlbranch = C4::Context->preference('ReservesControlBranch');
415
0
    my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
416
417    # we retrieve borrowers and items informations #
418
0
    my $item = GetItem($itemnumber);
419
0
    my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
420
421    # we retrieve user rights on this itemtype and branchcode
422
0
    my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
423                             FROM issuingrules
424                             WHERE (categorycode in (?,'*') )
425                             AND (itemtype IN (?,'*'))
426                             AND (branchcode IN (?,'*'))
427                             ORDER BY
428                               categorycode DESC,
429                               itemtype DESC,
430                               branchcode DESC;"
431                           );
432
433
0
    my $querycount ="SELECT
434                            count(*) as count
435                            FROM reserves
436                                LEFT JOIN items USING (itemnumber)
437                                LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
438                                LEFT JOIN borrowers USING (borrowernumber)
439                            WHERE borrowernumber = ?
440                                ";
441
442
443
0
    my $itemtype = $item->{$itype};
444
0
    my $categorycode = $borrower->{categorycode};
445
0
    my $branchcode = "";
446
0
    my $branchfield = "reserves.branchcode";
447
448
0
    if( $controlbranch eq "ItemHomeLibrary" ){
449
0
        $branchfield = "items.homebranch";
450
0
        $branchcode = $item->{homebranch};
451    }elsif( $controlbranch eq "PatronLibrary" ){
452
0
        $branchfield = "borrowers.branchcode";
453
0
        $branchcode = $borrower->{branchcode};
454    }
455
456    # we retrieve rights
457
0
    $sth->execute($categorycode, $itemtype, $branchcode);
458
0
    if(my $rights = $sth->fetchrow_hashref()){
459
0
        $itemtype = $rights->{itemtype};
460
0
        $allowedreserves = $rights->{reservesallowed};
461    }else{
462
0
        $itemtype = '*';
463    }
464
465    # we retrieve count
466
467
0
    $querycount .= "AND $branchfield = ?";
468
469
0
    $querycount .= " AND $itype = ?" if ($itemtype ne "*");
470
0
    my $sthcount = $dbh->prepare($querycount);
471
472
0
    if($itemtype eq "*"){
473
0
        $sthcount->execute($borrowernumber, $branchcode);
474    }else{
475
0
        $sthcount->execute($borrowernumber, $branchcode, $itemtype);
476    }
477
478
0
    my $reservecount = "0";
479
0
    if(my $rowcount = $sthcount->fetchrow_hashref()){
480
0
        $reservecount = $rowcount->{count};
481    }
482
483    # we check if it's ok or not
484
0
    if( $reservecount < $allowedreserves ){
485
0
        return 1;
486    }else{
487
0
        return 0;
488    }
489}
490#--------------------------------------------------------------------------------
491 - 497
=head2 GetReserveCount

  $number = &GetReserveCount($borrowernumber);

this function returns the number of reservation for a borrower given on input arg.

=cut
498
499sub GetReserveCount {
500
0
    my ($borrowernumber) = @_;
501
502
0
    my $dbh = C4::Context->dbh;
503
504
0
    my $query = '
505        SELECT COUNT(*) AS counter
506        FROM reserves
507          WHERE borrowernumber = ?
508    ';
509
0
    my $sth = $dbh->prepare($query);
510
0
    $sth->execute($borrowernumber);
511
0
    my $row = $sth->fetchrow_hashref;
512
0
    return $row->{counter};
513}
514
515 - 521
=head2 GetOtherReserves

  ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);

Check queued list of this document and check if this document must be  transfered

=cut
522
523sub GetOtherReserves {
524
0
    my ($itemnumber) = @_;
525
0
    my $messages;
526
0
    my $nextreservinfo;
527
0
    my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
528
0
    if ($checkreserves) {
529
0
        my $iteminfo = GetItem($itemnumber);
530
0
        if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
531
0
            $messages->{'transfert'} = $checkreserves->{'branchcode'};
532            #minus priorities of others reservs
533
0
            ModReserveMinusPriority(
534                $itemnumber,
535                $checkreserves->{'borrowernumber'},
536                $iteminfo->{'biblionumber'}
537            );
538
539            #launch the subroutine dotransfer
540
0
            C4::Items::ModItemTransfer(
541                $itemnumber,
542                $iteminfo->{'holdingbranch'},
543                $checkreserves->{'branchcode'}
544              ),
545              ;
546        }
547
548     #step 2b : case of a reservation on the same branch, set the waiting status
549        else {
550
0
            $messages->{'waiting'} = 1;
551
0
            ModReserveMinusPriority(
552                $itemnumber,
553                $checkreserves->{'borrowernumber'},
554                $iteminfo->{'biblionumber'}
555            );
556
0
            ModReserveStatus($itemnumber,'W');
557        }
558
559
0
        $nextreservinfo = $checkreserves->{'borrowernumber'};
560    }
561
562
0
    return ( $messages, $nextreservinfo );
563}
564
565 - 571
=head2 GetReserveFee

  $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);

Calculate the fee for a reserve

=cut
572
573sub GetReserveFee {
574
0
    my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
575
576    #check for issues;
577
0
    my $dbh = C4::Context->dbh;
578
0
    my $const = lc substr( $constraint, 0, 1 );
579
0
    my $query = qq/
580      SELECT * FROM borrowers
581    LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
582    WHERE borrowernumber = ?
583    /;
584
0
    my $sth = $dbh->prepare($query);
585
0
    $sth->execute($borrowernumber);
586
0
    my $data = $sth->fetchrow_hashref;
587
0
    $sth->finish();
588
0
    my $fee = $data->{'reservefee'};
589
0
    my $cntitems = @- > $bibitems;
590
591
0
    if ( $fee > 0 ) {
592
593        # check for items on issue
594        # first find biblioitem records
595
0
        my @biblioitems;
596
0
        my $sth1 = $dbh->prepare(
597            "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
598                   WHERE (biblio.biblionumber = ?)"
599        );
600
0
        $sth1->execute($biblionumber);
601
0
        while ( my $data1 = $sth1->fetchrow_hashref ) {
602
0
            if ( $const eq "a" ) {
603
0
                push @biblioitems, $data1;
604            }
605            else {
606
0
                my $found = 0;
607
0
                my $x = 0;
608
0
                while ( $x < $cntitems ) {
609
0
                    if ( @$bibitems->{'biblioitemnumber'} ==
610                        $data->{'biblioitemnumber'} )
611                    {
612
0
                        $found = 1;
613                    }
614
0
                    $x++;
615                }
616
0
                if ( $const eq 'o' ) {
617
0
                    if ( $found == 1 ) {
618
0
                        push @biblioitems, $data1;
619                    }
620                }
621                else {
622
0
                    if ( $found == 0 ) {
623
0
                        push @biblioitems, $data1;
624                    }
625                }
626            }
627        }
628
0
        $sth1->finish;
629
0
        my $cntitemsfound = @biblioitems;
630
0
        my $issues = 0;
631
0
        my $x = 0;
632
0
        my $allissued = 1;
633
0
        while ( $x < $cntitemsfound ) {
634
0
            my $bitdata = $biblioitems[$x];
635
0
            my $sth2 = $dbh->prepare(
636                "SELECT * FROM items
637                     WHERE biblioitemnumber = ?"
638            );
639
0
            $sth2->execute( $bitdata->{'biblioitemnumber'} );
640
0
            while ( my $itdata = $sth2->fetchrow_hashref ) {
641
0
                my $sth3 = $dbh->prepare(
642                    "SELECT * FROM issues
643                       WHERE itemnumber = ?"
644                );
645
0
                $sth3->execute( $itdata->{'itemnumber'} );
646
0
                if ( my $isdata = $sth3->fetchrow_hashref ) {
647                }
648                else {
649
0
                    $allissued = 0;
650                }
651            }
652
0
            $x++;
653        }
654
0
        if ( $allissued == 0 ) {
655
0
            my $rsth =
656              $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
657
0
            $rsth->execute($biblionumber);
658
0
            if ( my $rdata = $rsth->fetchrow_hashref ) {
659            }
660            else {
661
0
                $fee = 0;
662            }
663        }
664    }
665
0
    return $fee;
666}
667
668 - 674
=head2 GetReservesToBranch

  @transreserv = GetReservesToBranch( $frombranch );

Get reserve list for a given branch

=cut
675
676sub GetReservesToBranch {
677
0
    my ( $frombranch ) = @_;
678
0
    my $dbh = C4::Context->dbh;
679
0
    my $sth = $dbh->prepare(
680        "SELECT borrowernumber,reservedate,itemnumber,timestamp
681         FROM reserves
682         WHERE priority='0'
683           AND branchcode=?"
684    );
685
0
    $sth->execute( $frombranch );
686
0
    my @transreserv;
687
0
    my $i = 0;
688
0
    while ( my $data = $sth->fetchrow_hashref ) {
689
0
        $transreserv[$i] = $data;
690
0
        $i++;
691    }
692
0
    return (@transreserv);
693}
694
695 - 699
=head2 GetReservesForBranch

  @transreserv = GetReservesForBranch($frombranch);

=cut
700
701sub GetReservesForBranch {
702
0
    my ($frombranch) = @_;
703
0
    my $dbh = C4::Context->dbh;
704
0
        my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
705        FROM reserves
706        WHERE priority='0'
707            AND found='W' ";
708
0
    if ($frombranch){
709
0
        $query .= " AND branchcode=? ";
710        }
711
0
    $query .= "ORDER BY waitingdate" ;
712
0
    my $sth = $dbh->prepare($query);
713
0
    if ($frombranch){
714
0
                $sth->execute($frombranch);
715        }
716    else {
717
0
                $sth->execute();
718        }
719
0
    my @transreserv;
720
0
    my $i = 0;
721
0
    while ( my $data = $sth->fetchrow_hashref ) {
722
0
        $transreserv[$i] = $data;
723
0
        $i++;
724    }
725
0
    return (@transreserv);
726}
727
728sub GetReserveStatus {
729
0
    my ($itemnumber) = @_;
730
731
0
    my $dbh = C4::Context->dbh;
732
733
0
    my $itemstatus = $dbh->prepare("SELECT found FROM reserves WHERE itemnumber = ?");
734
735
0
    $itemstatus->execute($itemnumber);
736
0
    my ($found) = $itemstatus->fetchrow_array;
737
0
    return $found;
738}
739
740 - 765
=head2 CheckReserves

  ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
  ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);

Find a book in the reserves.

C<$itemnumber> is the book's item number.

As I understand it, C<&CheckReserves> looks for the given item in the
reserves. If it is found, that's a match, and C<$status> is set to
C<Waiting>.

Otherwise, it finds the most important item in the reserves with the
same biblio number as this book (I'm not clear on this) and returns it
with C<$status> set to C<Reserved>.

C<&CheckReserves> returns a two-element list:

C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.

C<$reserve> is the reserve item that matched. It is a
reference-to-hash whose keys are mostly the fields of the reserves
table in the Koha database.

=cut
766
767sub CheckReserves {
768
0
    my ( $item, $barcode ) = @_;
769
0
    my $dbh = C4::Context->dbh;
770
0
    my $sth;
771
0
    my $select;
772
0
    if (C4::Context->preference('item-level_itypes')){
773
0
        $select = "
774           SELECT items.biblionumber,
775           items.biblioitemnumber,
776           itemtypes.notforloan,
777           items.notforloan AS itemnotforloan,
778           items.itemnumber
779           FROM items
780           LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
781           LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype
782        ";
783    }
784    else {
785
0
        $select = "
786           SELECT items.biblionumber,
787           items.biblioitemnumber,
788           itemtypes.notforloan,
789           items.notforloan AS itemnotforloan,
790           items.itemnumber
791           FROM items
792           LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
793           LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
794        ";
795    }
796
797
0
    if ($item) {
798
0
        $sth = $dbh->prepare("$select WHERE itemnumber = ?");
799
0
        $sth->execute($item);
800    }
801    else {
802
0
        $sth = $dbh->prepare("$select WHERE barcode = ?");
803
0
        $sth->execute($barcode);
804    }
805    # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
806
0
    my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
807
808
0
    return ( '' ) unless $itemnumber; # bail if we got nothing.
809
810    # if item is not for loan it cannot be reserved either.....
811    # execpt where items.notforloan < 0 : This indicates the item is holdable.
812
0
    return ( '' ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
813
814    # Find this item in the reserves
815
0
    my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
816
817    # $priority and $highest are used to find the most important item
818    # in the list returned by &_Findgroupreserve. (The lower $priority,
819    # the more important the item.)
820    # $highest is the most important item we've seen so far.
821
0
    my $highest;
822
0
    if (scalar @reserves) {
823
0
        my $priority = 10000000;
824
0
        foreach my $res (@reserves) {
825
0
            if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
826
0
                return ( "Waiting", $res, \@reserves ); # Found it
827            } else {
828                # See if this item is more important than what we've got so far
829
0
                if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
830
0
                    my $borrowerinfo=C4::Members::GetMember(borrowernumber => $res->{'borrowernumber'});
831
0
                    my $iteminfo=C4::Items::GetItem($itemnumber);
832
0
                    my $branch=C4::Circulation::_GetCircControlBranch($iteminfo,$borrowerinfo);
833
0
                    my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
834
0
                    next if ($branchitemrule->{'holdallowed'} == 0);
835
0
                    next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
836
0
                    $priority = $res->{'priority'};
837
0
                    $highest = $res;
838                }
839            }
840        }
841    }
842
843    # If we get this far, then no exact match was found.
844    # We return the most important (i.e. next) reservation.
845
0
    if ($highest) {
846
0
        $highest->{'itemnumber'} = $item;
847
0
        return ( "Reserved", $highest, \@reserves );
848    }
849
850
0
    return ( '' );
851}
852
853 - 859
=head2 CancelExpiredReserves

  CancelExpiredReserves();

Cancels all reserves with an expiration date from before today.

=cut
860
861sub CancelExpiredReserves {
862
863
0
    my $dbh = C4::Context->dbh;
864
0
    my $sth = $dbh->prepare( "
865        SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
866        AND expirationdate IS NOT NULL
867    " );
868
0
    $sth->execute();
869
870
0
    while ( my $res = $sth->fetchrow_hashref() ) {
871
0
        CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
872    }
873
874}
875
876 - 892
=head2 CancelReserve

  &CancelReserve($biblionumber, $itemnumber, $borrowernumber);

Cancels a reserve.

Use either C<$biblionumber> or C<$itemnumber> to specify the item to
cancel, but not both: if both are given, C<&CancelReserve> does
nothing.

C<$borrowernumber> is the borrower number of the patron on whose
behalf the book was reserved.

If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
priorities of the other people who are waiting on the book.

=cut
893
894sub CancelReserve {
895
0
    my ( $biblio, $item, $borr ) = @_;
896
0
    my $dbh = C4::Context->dbh;
897
0
        if ( $item and $borr ) {
898        # removing a waiting reserve record....
899        # update the database...
900
0
        my $query = "
901            UPDATE reserves
902            SET cancellationdate = now(),
903                   found = Null,
904                   priority = 0
905            WHERE itemnumber = ?
906             AND borrowernumber = ?
907        ";
908
0
        my $sth = $dbh->prepare($query);
909
0
        $sth->execute( $item, $borr );
910
0
        $sth->finish;
911
0
        $query = "
912            INSERT INTO old_reserves
913            SELECT * FROM reserves
914            WHERE itemnumber = ?
915             AND borrowernumber = ?
916        ";
917
0
        $sth = $dbh->prepare($query);
918
0
        $sth->execute( $item, $borr );
919
0
        $query = "
920            DELETE FROM reserves
921            WHERE itemnumber = ?
922             AND borrowernumber = ?
923        ";
924
0
        $sth = $dbh->prepare($query);
925
0
        $sth->execute( $item, $borr );
926    }
927    else {
928        # removing a reserve record....
929        # get the prioritiy on this record....
930
0
        my $priority;
931
0
        my $query = qq/
932            SELECT priority FROM reserves
933            WHERE biblionumber = ?
934              AND borrowernumber = ?
935              AND cancellationdate IS NULL
936              AND itemnumber IS NULL
937        /;
938
0
        my $sth = $dbh->prepare($query);
939
0
        $sth->execute( $biblio, $borr );
940
0
        ($priority) = $sth->fetchrow_array;
941
0
        $sth->finish;
942
0
        $query = qq/
943            UPDATE reserves
944            SET cancellationdate = now(),
945                   found = Null,
946                   priority = 0
947            WHERE biblionumber = ?
948              AND borrowernumber = ?
949        /;
950
951        # update the database, removing the record...
952
0
        $sth = $dbh->prepare($query);
953
0
        $sth->execute( $biblio, $borr );
954
0
        $sth->finish;
955
956
0
        $query = qq/
957            INSERT INTO old_reserves
958            SELECT * FROM reserves
959            WHERE biblionumber = ?
960              AND borrowernumber = ?
961        /;
962
0
        $sth = $dbh->prepare($query);
963
0
        $sth->execute( $biblio, $borr );
964
965
0
        $query = qq/
966            DELETE FROM reserves
967            WHERE biblionumber = ?
968              AND borrowernumber = ?
969        /;
970
0
        $sth = $dbh->prepare($query);
971
0
        $sth->execute( $biblio, $borr );
972
973        # now fix the priority on the others....
974
0
        _FixPriority( $biblio, $borr );
975    }
976}
977
978 - 1007
=head2 ModReserve

  ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])

Change a hold request's priority or cancel it.

C<$rank> specifies the effect of the change.  If C<$rank>
is 'W' or 'n', nothing happens.  This corresponds to leaving a
request alone when changing its priority in the holds queue
for a bib.

If C<$rank> is 'del', the hold request is cancelled.

If C<$rank> is an integer greater than zero, the priority of
the request is set to that value.  Since priority != 0 means
that the item is not waiting on the hold shelf, setting the 
priority to a non-zero value also sets the request's found
status and waiting date to NULL. 

The optional C<$itemnumber> parameter is used only when
C<$rank> is a non-zero integer; if supplied, the itemnumber 
of the hold request is set accordingly; if omitted, the itemnumber
is cleared.

B<FIXME:> Note that the forgoing can have the effect of causing
item-level hold requests to turn into title-level requests.  This
will be fixed once reserves has separate columns for requested
itemnumber and supplying itemnumber.

=cut
1008
1009sub ModReserve {
1010    #subroutine to update a reserve
1011
0
    my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1012
0
     return if $rank eq "W";
1013
0
     return if $rank eq "n";
1014
0
    my $dbh = C4::Context->dbh;
1015
0
    if ( $rank eq "del" ) {
1016
0
        my $query = qq/
1017            UPDATE reserves
1018            SET cancellationdate=now()
1019            WHERE biblionumber = ?
1020             AND borrowernumber = ?
1021        /;
1022
0
        my $sth = $dbh->prepare($query);
1023
0
        $sth->execute( $biblio, $borrower );
1024
0
        $sth->finish;
1025
0
        $query = qq/
1026            INSERT INTO old_reserves
1027            SELECT *
1028            FROM reserves
1029            WHERE biblionumber = ?
1030             AND borrowernumber = ?
1031        /;
1032
0
        $sth = $dbh->prepare($query);
1033
0
        $sth->execute( $biblio, $borrower );
1034
0
        $query = qq/
1035            DELETE FROM reserves
1036            WHERE biblionumber = ?
1037             AND borrowernumber = ?
1038        /;
1039
0
        $sth = $dbh->prepare($query);
1040
0
        $sth->execute( $biblio, $borrower );
1041
1042    }
1043    elsif ($rank =~ /^\d+/ and $rank > 0) {
1044
0
        my $query = qq/
1045        UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1046            WHERE biblionumber = ?
1047             AND borrowernumber = ?
1048        /;
1049
0
        my $sth = $dbh->prepare($query);
1050
0
        $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1051
0
        $sth->finish;
1052
0
        _FixPriority( $biblio, $borrower, $rank);
1053    }
1054}
1055
1056 - 1066
=head2 ModReserveFill

  &ModReserveFill($reserve);

Fill a reserve. If I understand this correctly, this means that the
reserved book has been found and given to the patron who reserved it.

C<$reserve> specifies the reserve to fill. It is a reference-to-hash
whose keys are fields from the reserves table in the Koha database.

=cut
1067
1068sub ModReserveFill {
1069
0
    my ($res) = @_;
1070
0
    my $dbh = C4::Context->dbh;
1071    # fill in a reserve record....
1072
0
    my $biblionumber = $res->{'biblionumber'};
1073
0
    my $borrowernumber = $res->{'borrowernumber'};
1074
0
    my $resdate = $res->{'reservedate'};
1075
1076    # get the priority on this record....
1077
0
    my $priority;
1078
0
    my $query = "SELECT priority
1079                 FROM reserves
1080                 WHERE biblionumber = ?
1081                  AND borrowernumber = ?
1082                  AND reservedate = ?";
1083
0
    my $sth = $dbh->prepare($query);
1084
0
    $sth->execute( $biblionumber, $borrowernumber, $resdate );
1085
0
    ($priority) = $sth->fetchrow_array;
1086
0
    $sth->finish;
1087
1088    # update the database...
1089
0
    $query = "UPDATE reserves
1090                  SET found = 'F',
1091                         priority = 0
1092                 WHERE biblionumber = ?
1093                    AND reservedate = ?
1094                    AND borrowernumber = ?
1095                ";
1096
0
    $sth = $dbh->prepare($query);
1097
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1098
0
    $sth->finish;
1099
1100    # move to old_reserves
1101
0
    $query = "INSERT INTO old_reserves
1102                 SELECT * FROM reserves
1103                 WHERE biblionumber = ?
1104                    AND reservedate = ?
1105                    AND borrowernumber = ?
1106                ";
1107
0
    $sth = $dbh->prepare($query);
1108
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1109
0
    $query = "DELETE FROM reserves
1110                 WHERE biblionumber = ?
1111                    AND reservedate = ?
1112                    AND borrowernumber = ?
1113                ";
1114
0
    $sth = $dbh->prepare($query);
1115
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1116
1117    # now fix the priority on the others (if the priority wasn't
1118    # already sorted!)....
1119
0
    unless ( $priority == 0 ) {
1120
0
        _FixPriority( $biblionumber, $borrowernumber );
1121    }
1122}
1123
1124 - 1134
=head2 ModReserveStatus

  &ModReserveStatus($itemnumber, $newstatus);

Update the reserve status for the active (priority=0) reserve.

$itemnumber is the itemnumber the reserve is on

$newstatus is the new status.

=cut
1135
1136sub ModReserveStatus {
1137
1138    #first : check if we have a reservation for this item .
1139
0
    my ($itemnumber, $newstatus) = @_;
1140
0
    my $dbh = C4::Context->dbh;
1141
0
    my $query = " UPDATE reserves
1142    SET found=?,waitingdate = now()
1143    WHERE itemnumber=?
1144      AND found IS NULL
1145      AND priority = 0
1146    ";
1147
0
    my $sth_set = $dbh->prepare($query);
1148
0
    $sth_set->execute( $newstatus, $itemnumber );
1149
1150
0
    if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1151
0
      CartToShelf( $itemnumber );
1152    }
1153}
1154
1155 - 1168
=head2 ModReserveAffect

  &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);

This function affect an item and a status for a given reserve
The itemnumber parameter is used to find the biblionumber.
with the biblionumber & the borrowernumber, we can affect the itemnumber
to the correct reserve.

if $transferToDo is not set, then the status is set to "Waiting" as well.
otherwise, a transfer is on the way, and the end of the transfer will 
take care of the waiting status

=cut
1169
1170sub ModReserveAffect {
1171
0
    my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1172
0
    my $dbh = C4::Context->dbh;
1173
1174    # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1175    # attached to $itemnumber
1176
0
    my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1177
0
    $sth->execute($itemnumber);
1178
0
    my ($biblionumber) = $sth->fetchrow;
1179
1180    # get request - need to find out if item is already
1181    # waiting in order to not send duplicate hold filled notifications
1182
0
    my $request = GetReserveInfo($borrowernumber, $biblionumber);
1183
0
    my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1184
1185    # If we affect a reserve that has to be transfered, don't set to Waiting
1186
0
    my $query;
1187
0
    if ($transferToDo) {
1188
0
    $query = "
1189        UPDATE reserves
1190        SET priority = 0,
1191               itemnumber = ?,
1192               found = 'T'
1193        WHERE borrowernumber = ?
1194          AND biblionumber = ?
1195    ";
1196    }
1197    else {
1198    # affect the reserve to Waiting as well.
1199
0
    $query = "
1200        UPDATE reserves
1201        SET priority = 0,
1202                found = 'W',
1203                waitingdate=now(),
1204                itemnumber = ?
1205        WHERE borrowernumber = ?
1206          AND biblionumber = ?
1207    ";
1208    }
1209
0
    $sth = $dbh->prepare($query);
1210
0
    $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1211
0
    _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1212
1213
0
    if ( C4::Context->preference("ReturnToShelvingCart") ) {
1214
0
      CartToShelf( $itemnumber );
1215    }
1216
1217
0
    return;
1218}
1219
1220 - 1226
=head2 ModReserveCancelAll

  ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);

function to cancel reserv,check other reserves, and transfer document if it's necessary

=cut
1227
1228sub ModReserveCancelAll {
1229
0
    my $messages;
1230
0
    my $nextreservinfo;
1231
0
    my ( $itemnumber, $borrowernumber ) = @_;
1232
1233    #step 1 : cancel the reservation
1234
0
    my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1235
1236    #step 2 launch the subroutine of the others reserves
1237
0
    ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1238
1239
0
    return ( $messages, $nextreservinfo );
1240}
1241
1242 - 1248
=head2 ModReserveMinusPriority

  &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)

Reduce the values of queuded list     

=cut
1249
1250sub ModReserveMinusPriority {
1251
0
    my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1252
1253    #first step update the value of the first person on reserv
1254
0
    my $dbh = C4::Context->dbh;
1255
0
    my $query = "
1256        UPDATE reserves
1257        SET priority = 0 , itemnumber = ?
1258        WHERE borrowernumber=?
1259          AND biblionumber=?
1260    ";
1261
0
    my $sth_upd = $dbh->prepare($query);
1262
0
    $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1263    # second step update all others reservs
1264
0
    _FixPriority($biblionumber, $borrowernumber, '0');
1265}
1266
1267 - 1274
=head2 GetReserveInfo

  &GetReserveInfo($borrowernumber,$biblionumber);

Get item and borrower details for a current hold.
Current implementation this query should have a single result.

=cut
1275
1276sub GetReserveInfo {
1277
0
        my ( $borrowernumber, $biblionumber ) = @_;
1278
0
    my $dbh = C4::Context->dbh;
1279
0
        my $strsth="SELECT
1280                       reservedate,
1281                       reservenotes,
1282                       reserves.borrowernumber,
1283                                   reserves.biblionumber,
1284                                   reserves.branchcode,
1285                                   reserves.waitingdate,
1286                                   notificationdate,
1287                                   reminderdate,
1288                                   priority,
1289                                   found,
1290                                   firstname,
1291                                   surname,
1292                                   phone,
1293                                   email,
1294                                   address,
1295                                   address2,
1296                                   cardnumber,
1297                                   city,
1298                                   zipcode,
1299                                   biblio.title,
1300                                   biblio.author,
1301                                   items.holdingbranch,
1302                                   items.itemcallnumber,
1303                                   items.itemnumber,
1304                                   items.location,
1305                                   barcode,
1306                                   notes
1307                        FROM reserves
1308                         LEFT JOIN items USING(itemnumber)
1309                     LEFT JOIN borrowers USING(borrowernumber)
1310                     LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1311                        WHERE
1312                                reserves.borrowernumber=?
1313                                AND reserves.biblionumber=?";
1314
0
        my $sth = $dbh->prepare($strsth);
1315
0
        $sth->execute($borrowernumber,$biblionumber);
1316
1317
0
        my $data = $sth->fetchrow_hashref;
1318
0
        return $data;
1319
1320}
1321
1322 - 1347
=head2 IsAvailableForItemLevelRequest

  my $is_available = IsAvailableForItemLevelRequest($itemnumber);

Checks whether a given item record is available for an
item-level hold request.  An item is available if

* it is not lost AND 
* it is not damaged AND 
* it is not withdrawn AND 
* does not have a not for loan value > 0

Whether or not the item is currently on loan is 
also checked - if the AllowOnShelfHolds system preference
is ON, an item can be requested even if it is currently
on loan to somebody else.  If the system preference
is OFF, an item that is currently checked out cannot
be the target of an item-level hold request.

Note that IsAvailableForItemLevelRequest() does not
check if the staff operator is authorized to place
a request on the item - in particular,
this routine does not check IndependantBranches
and canreservefromotherbranches.

=cut
1348
1349sub IsAvailableForItemLevelRequest {
1350
0
    my $itemnumber = shift;
1351
1352
0
    my $item = GetItem($itemnumber);
1353
1354    # must check the notforloan setting of the itemtype
1355    # FIXME - a lot of places in the code do this
1356    # or something similar - need to be
1357    # consolidated
1358
0
    my $dbh = C4::Context->dbh;
1359
0
    my $notforloan_query;
1360
0
    if (C4::Context->preference('item-level_itypes')) {
1361
0
        $notforloan_query = "SELECT itemtypes.notforloan
1362                             FROM items
1363                             JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1364                             WHERE itemnumber = ?";
1365    } else {
1366
0
        $notforloan_query = "SELECT itemtypes.notforloan
1367                             FROM items
1368                             JOIN biblioitems USING (biblioitemnumber)
1369                             JOIN itemtypes USING (itemtype)
1370                             WHERE itemnumber = ?";
1371    }
1372
0
    my $sth = $dbh->prepare($notforloan_query);
1373
0
    $sth->execute($itemnumber);
1374
0
    my $notforloan_per_itemtype = 0;
1375
0
    if (my ($notforloan) = $sth->fetchrow_array) {
1376
0
        $notforloan_per_itemtype = 1 if $notforloan;
1377    }
1378
1379
0
    my $available_per_item = 1;
1380
0
    $available_per_item = 0 if $item->{itemlost} or
1381                               ( $item->{notforloan} > 0 ) or
1382                               ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1383                               $item->{wthdrawn} or
1384                               $notforloan_per_itemtype;
1385
1386
1387
0
    if (C4::Context->preference('AllowOnShelfHolds')) {
1388
0
        return $available_per_item;
1389    } else {
1390
0
        return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "W"));
1391    }
1392}
1393
1394 - 1401
=head2 AlterPriority

  AlterPriority( $where, $borrowernumber, $biblionumber, $reservedate );

This function changes a reserve's priority up, down, to the top, or to the bottom.
Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed

=cut
1402
1403sub AlterPriority {
1404
0
    my ( $where, $borrowernumber, $biblionumber ) = @_;
1405
1406
0
    my $dbh = C4::Context->dbh;
1407
1408    ## Find this reserve
1409
0
    my $sth = $dbh->prepare('SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? AND cancellationdate IS NULL');
1410
0
    $sth->execute( $biblionumber, $borrowernumber );
1411
0
    my $reserve = $sth->fetchrow_hashref();
1412
0
    $sth->finish();
1413
1414
0
    if ( $where eq 'up' || $where eq 'down' ) {
1415
1416
0
      my $priority = $reserve->{'priority'};
1417
0
      $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1418
0
      _FixPriority( $biblionumber, $borrowernumber, $priority )
1419
1420    } elsif ( $where eq 'top' ) {
1421
1422
0
      _FixPriority( $biblionumber, $borrowernumber, '1' )
1423
1424    } elsif ( $where eq 'bottom' ) {
1425
1426
0
      _FixPriority( $biblionumber, $borrowernumber, '999999' )
1427
1428    }
1429}
1430
1431 - 1437
=head2 ToggleLowestPriority

  ToggleLowestPriority( $borrowernumber, $biblionumber );

This function sets the lowestPriority field to true if is false, and false if it is true.

=cut
1438
1439sub ToggleLowestPriority {
1440
0
    my ( $borrowernumber, $biblionumber ) = @_;
1441
1442
0
    my $dbh = C4::Context->dbh;
1443
1444
0
    my $sth = $dbh->prepare(
1445        "UPDATE reserves SET lowestPriority = NOT lowestPriority
1446         WHERE biblionumber = ?
1447         AND borrowernumber = ?"
1448    );
1449
0
    $sth->execute(
1450        $biblionumber,
1451        $borrowernumber,
1452    );
1453
0
    $sth->finish;
1454
1455
0
    _FixPriority( $biblionumber, $borrowernumber, '999999' );
1456}
1457
1458 - 1469
=head2 _FixPriority

  &_FixPriority($biblio,$borrowernumber,$rank,$ignoreSetLowestRank);

Only used internally (so don't export it)
Changed how this functions works #
Now just gets an array of reserves in the rank order and updates them with
the array index (+1 as array starts from 0)
and if $rank is supplied will splice item from the array and splice it back in again
in new priority rank

=cut 
1470
1471sub _FixPriority {
1472
0
    my ( $biblio, $borrowernumber, $rank, $ignoreSetLowestRank ) = @_;
1473
0
    my $dbh = C4::Context->dbh;
1474
0
     if ( $rank eq "del" ) {
1475
0
         CancelReserve( $biblio, undef, $borrowernumber );
1476     }
1477
0
    if ( $rank eq "W" || $rank eq "0" ) {
1478
1479        # make sure priority for waiting or in-transit items is 0
1480
0
        my $query = qq/
1481            UPDATE reserves
1482            SET priority = 0
1483            WHERE biblionumber = ?
1484              AND borrowernumber = ?
1485              AND found IN ('W', 'T')
1486        /;
1487
0
        my $sth = $dbh->prepare($query);
1488
0
        $sth->execute( $biblio, $borrowernumber );
1489    }
1490
0
    my @priority;
1491
0
    my @reservedates;
1492
1493    # get whats left
1494# FIXME adding a new security in returned elements for changing priority,
1495# now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1496        # This is wrong a waiting reserve has W set
1497        # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1498
0
    my $query = qq/
1499        SELECT borrowernumber, reservedate, constrainttype
1500        FROM reserves
1501        WHERE biblionumber = ?
1502          AND ((found <> 'W' AND found <> 'T') or found is NULL)
1503        ORDER BY priority ASC
1504    /;
1505
0
    my $sth = $dbh->prepare($query);
1506
0
    $sth->execute($biblio);
1507
0
    while ( my $line = $sth->fetchrow_hashref ) {
1508
0
        push( @reservedates, $line );
1509
0
        push( @priority, $line );
1510    }
1511
1512    # To find the matching index
1513
0
    my $i;
1514
0
    my $key = -1; # to allow for 0 to be a valid result
1515    for ( $i = 0 ; $i < @priority ; $i++ ) {
1516
0
        if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1517
0
            $key = $i; # save the index
1518
0
            last;
1519        }
1520
0
    }
1521
1522    # if index exists in array then move it to new position
1523
0
    if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1524
0
        my $new_rank = $rank -
1525          1; # $new_rank is what you want the new index to be in the array
1526
0
        my $moving_item = splice( @priority, $key, 1 );
1527
0
        splice( @priority, $new_rank, 0, $moving_item );
1528    }
1529
1530    # now fix the priority on those that are left....
1531
0
    $query = "
1532            UPDATE reserves
1533            SET priority = ?
1534                WHERE biblionumber = ?
1535                 AND borrowernumber = ?
1536                 AND reservedate = ?
1537         AND found IS NULL
1538    ";
1539
0
    $sth = $dbh->prepare($query);
1540    for ( my $j = 0 ; $j < @priority ; $j++ ) {
1541
0
        $sth->execute(
1542            $j + 1, $biblio,
1543            $priority[$j]->{'borrowernumber'},
1544            $priority[$j]->{'reservedate'}
1545        );
1546
0
        $sth->finish;
1547
0
    }
1548
1549
0
    $sth = $dbh->prepare( "SELECT borrowernumber FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1550
0
    $sth->execute();
1551
1552
0
    unless ( $ignoreSetLowestRank ) {
1553
0
      while ( my $res = $sth->fetchrow_hashref() ) {
1554
0
        _FixPriority( $biblio, $res->{'borrowernumber'}, '999999', 1 );
1555      }
1556    }
1557}
1558
1559 - 1574
=head2 _Findgroupreserve

  @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);

Looks for an item-specific match first, then for a title-level match, returning the
first match found.  If neither, then we look for a 3rd kind of match based on
reserve constraints.

TODO: add more explanation about reserve constraints

C<&_Findgroupreserve> returns :
C<@results> is an array of references-to-hash whose keys are mostly
fields from the reserves table of the Koha database, plus
C<biblioitemnumber>.

=cut
1575
1576sub _Findgroupreserve {
1577
0
    my ( $bibitem, $biblio, $itemnumber ) = @_;
1578
0
    my $dbh = C4::Context->dbh;
1579
1580    # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1581    # check for exact targetted match
1582
0
    my $item_level_target_query = qq/
1583        SELECT reserves.biblionumber AS biblionumber,
1584               reserves.borrowernumber AS borrowernumber,
1585               reserves.reservedate AS reservedate,
1586               reserves.branchcode AS branchcode,
1587               reserves.cancellationdate AS cancellationdate,
1588               reserves.found AS found,
1589               reserves.reservenotes AS reservenotes,
1590               reserves.priority AS priority,
1591               reserves.timestamp AS timestamp,
1592               biblioitems.biblioitemnumber AS biblioitemnumber,
1593               reserves.itemnumber AS itemnumber
1594        FROM reserves
1595        JOIN biblioitems USING (biblionumber)
1596        JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1597        WHERE found IS NULL
1598        AND priority > 0
1599        AND item_level_request = 1
1600        AND itemnumber = ?
1601        AND reservedate <= CURRENT_DATE()
1602    /;
1603
0
    my $sth = $dbh->prepare($item_level_target_query);
1604
0
    $sth->execute($itemnumber);
1605
0
    my @results;
1606
0
    if ( my $data = $sth->fetchrow_hashref ) {
1607
0
        push( @results, $data );
1608    }
1609
0
    return @results if @results;
1610
1611    # check for title-level targetted match
1612
0
    my $title_level_target_query = qq/
1613        SELECT reserves.biblionumber AS biblionumber,
1614               reserves.borrowernumber AS borrowernumber,
1615               reserves.reservedate AS reservedate,
1616               reserves.branchcode AS branchcode,
1617               reserves.cancellationdate AS cancellationdate,
1618               reserves.found AS found,
1619               reserves.reservenotes AS reservenotes,
1620               reserves.priority AS priority,
1621               reserves.timestamp AS timestamp,
1622               biblioitems.biblioitemnumber AS biblioitemnumber,
1623               reserves.itemnumber AS itemnumber
1624        FROM reserves
1625        JOIN biblioitems USING (biblionumber)
1626        JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1627        WHERE found IS NULL
1628        AND priority > 0
1629        AND item_level_request = 0
1630        AND hold_fill_targets.itemnumber = ?
1631        AND reservedate <= CURRENT_DATE()
1632    /;
1633
0
    $sth = $dbh->prepare($title_level_target_query);
1634
0
    $sth->execute($itemnumber);
1635
0
    @results = ();
1636
0
    if ( my $data = $sth->fetchrow_hashref ) {
1637
0
        push( @results, $data );
1638    }
1639
0
    return @results if @results;
1640
1641
0
    my $query = qq/
1642        SELECT reserves.biblionumber AS biblionumber,
1643               reserves.borrowernumber AS borrowernumber,
1644               reserves.reservedate AS reservedate,
1645               reserves.waitingdate AS waitingdate,
1646               reserves.branchcode AS branchcode,
1647               reserves.cancellationdate AS cancellationdate,
1648               reserves.found AS found,
1649               reserves.reservenotes AS reservenotes,
1650               reserves.priority AS priority,
1651               reserves.timestamp AS timestamp,
1652               reserveconstraints.biblioitemnumber AS biblioitemnumber,
1653               reserves.itemnumber AS itemnumber
1654        FROM reserves
1655          LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1656        WHERE reserves.biblionumber = ?
1657          AND ( ( reserveconstraints.biblioitemnumber = ?
1658          AND reserves.borrowernumber = reserveconstraints.borrowernumber
1659          AND reserves.reservedate = reserveconstraints.reservedate )
1660          OR reserves.constrainttype='a' )
1661          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1662          AND reserves.reservedate <= CURRENT_DATE()
1663    /;
1664
0
    $sth = $dbh->prepare($query);
1665
0
    $sth->execute( $biblio, $bibitem, $itemnumber );
1666
0
    @results = ();
1667
0
    while ( my $data = $sth->fetchrow_hashref ) {
1668
0
        push( @results, $data );
1669    }
1670
0
    return @results;
1671}
1672
1673 - 1680
=head2 _koha_notify_reserve

  _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );

Sends a notification to the patron that their hold has been filled (through
ModReserveAffect, _not_ ModReserveFill)

=cut
1681
1682sub _koha_notify_reserve {
1683
0
    my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1684
1685
0
    my $dbh = C4::Context->dbh;
1686
0
    my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1687
1688    # Try to get the borrower's email address
1689
0
    my $to_address;
1690
0
    my $which_address = C4::Context->preference('AutoEmailPrimaryAddress');
1691    # If the system preference is set to 'first valid' (value == OFF), look up email address
1692
0
    if ($which_address eq 'OFF') {
1693
0
        $to_address = C4::Members::GetFirstValidEmailAddress( $borrowernumber );
1694    } else {
1695
0
        $to_address = $borrower->{$which_address};
1696    }
1697
1698
0
    my $letter_code;
1699
0
    my $print_mode = 0;
1700
0
    my $messagingprefs;
1701
0
    if ( $to_address || $borrower->{'smsalertnumber'} ) {
1702
0
        $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold_Filled' } );
1703
1704
0
        return if ( !defined( $messagingprefs->{'letter_code'} ) );
1705
0
        $letter_code = $messagingprefs->{'letter_code'};
1706    } else {
1707
0
        $letter_code = 'HOLD_PRINT';
1708
0
        $print_mode = 1;
1709    }
1710
1711
0
    my $sth = $dbh->prepare("
1712        SELECT *
1713        FROM reserves
1714        WHERE borrowernumber = ?
1715            AND biblionumber = ?
1716    ");
1717
0
    $sth->execute( $borrowernumber, $biblionumber );
1718
0
    my $reserve = $sth->fetchrow_hashref;
1719
0
    my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1720
1721
0
    my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1722
1723
0
    my $letter = getletter( 'reserves', $letter_code );
1724
0
    die "Could not find a letter called '$letter_code' in the 'reserves' module" unless( $letter );
1725
1726
0
    C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1727
0
    C4::Letters::parseletter( $letter, 'borrowers', $borrowernumber );
1728
0
    C4::Letters::parseletter( $letter, 'biblio', $biblionumber );
1729
0
    C4::Letters::parseletter( $letter, 'reserves', $borrowernumber, $biblionumber );
1730
1731
0
    if ( $reserve->{'itemnumber'} ) {
1732
0
        C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1733    }
1734
0
    my $today = C4::Dates->new()->output();
1735
0
    $letter->{'title'} =~ s/<<today>>/$today/g;
1736
0
    $letter->{'content'} =~ s/<<today>>/$today/g;
1737
0
    $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1738
1739
0
    if ( $print_mode ) {
1740
0
        C4::Letters::EnqueueLetter( {
1741            letter => $letter,
1742            borrowernumber => $borrowernumber,
1743            message_transport_type => 'print',
1744        } );
1745
1746
0
        return;
1747    }
1748
1749
0
0
0
    if ( grep { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1750        # aka, 'email' in ->{'transports'}
1751
0
        C4::Letters::EnqueueLetter(
1752            { letter => $letter,
1753                borrowernumber => $borrowernumber,
1754                message_transport_type => 'email',
1755                from_address => $admin_email_address,
1756            }
1757        );
1758    }
1759
1760
0
0
0
    if ( grep { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1761
0
        C4::Letters::EnqueueLetter(
1762            { letter => $letter,
1763                borrowernumber => $borrowernumber,
1764                message_transport_type => 'sms',
1765            }
1766        );
1767    }
1768}
1769
1770 - 1786
=head2 _ShiftPriorityByDateAndPriority

  $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );

This increments the priority of all reserves after the one
with either the lowest date after C<$reservedate>
or the lowest priority after C<$priority>.

It effectively makes room for a new reserve to be inserted with a certain
priority, which is returned.

This is most useful when the reservedate can be set by the user.  It allows
the new reserve to be placed before other reserves that have a later
reservedate.  Since priority also is set by the form in reserves/request.pl
the sub accounts for that too.

=cut
1787
1788sub _ShiftPriorityByDateAndPriority {
1789
0
    my ( $biblio, $resdate, $new_priority ) = @_;
1790
1791
0
    my $dbh = C4::Context->dbh;
1792
0
    my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1793
0
    my $sth = $dbh->prepare( $query );
1794
0
    $sth->execute( $biblio, $resdate, $new_priority );
1795
0
    my $min_priority = $sth->fetchrow;
1796    # if no such matches are found, $new_priority remains as original value
1797
0
    $new_priority = $min_priority if ( $min_priority );
1798
1799    # Shift the priority up by one; works in conjunction with the next SQL statement
1800
0
    $query = "UPDATE reserves
1801              SET priority = priority+1
1802              WHERE biblionumber = ?
1803              AND borrowernumber = ?
1804              AND reservedate = ?
1805              AND found IS NULL";
1806
0
    my $sth_update = $dbh->prepare( $query );
1807
1808    # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1809
0
    $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1810
0
    $sth = $dbh->prepare( $query );
1811
0
    $sth->execute( $new_priority, $biblio );
1812
0
    while ( my $row = $sth->fetchrow_hashref ) {
1813
0
        $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1814    }
1815
1816
0
    return $new_priority; # so the caller knows what priority they wind up receiving
1817}
1818
1819 - 1826
=head2 MoveReserve

  MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )

Use when checking out an item to handle reserves
If $cancelreserve boolean is set to true, it will remove existing reserve

=cut
1827
1828sub MoveReserve {
1829
0
    my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
1830
1831
0
    my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
1832
0
    return unless $res;
1833
1834
0
    my $biblionumber = $res->{biblionumber};
1835
0
    my $biblioitemnumber = $res->{biblioitemnumber};
1836
1837
0
    if ($res->{borrowernumber} == $borrowernumber) {
1838
0
        ModReserveFill($res);
1839    }
1840    else {
1841        # warn "Reserved";
1842        # The item is reserved by someone else.
1843        # Find this item in the reserves
1844
1845
0
        my $borr_res;
1846
0
        foreach (@$all_reserves) {
1847
0
            $_->{'borrowernumber'} == $borrowernumber or next;
1848
0
            $_->{'biblionumber'} == $biblionumber or next;
1849
1850
0
            $borr_res = $_;
1851
0
            last;
1852        }
1853
1854
0
        if ( $borr_res ) {
1855            # The item is reserved by the current patron
1856
0
            ModReserveFill($borr_res);
1857        }
1858
1859
0
        if ($cancelreserve) { # cancel reserves on this item
1860
0
            CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
1861
0
            CancelReserve($res->{'biblionumber'}, 0, $res->{'borrowernumber'});
1862        }
1863    }
1864}
1865
1866 - 1872
=head2 MergeHolds

  MergeHolds($dbh,$to_biblio, $from_biblio);

This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed

=cut
1873
1874sub MergeHolds {
1875
0
    my ( $dbh, $to_biblio, $from_biblio ) = @_;
1876
0
    my $sth = $dbh->prepare(
1877        "SELECT count(*) as reservenumber FROM reserves WHERE biblionumber = ?"
1878    );
1879
0
    $sth->execute($from_biblio);
1880
0
    if ( my $data = $sth->fetchrow_hashref() ) {
1881
1882        # holds exist on old record, if not we don't need to do anything
1883
0
        $sth = $dbh->prepare(
1884            "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
1885
0
        $sth->execute( $to_biblio, $from_biblio );
1886
1887        # Reorder by date
1888        # don't reorder those already waiting
1889
1890
0
        $sth = $dbh->prepare(
1891"SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
1892        );
1893
0
        my $upd_sth = $dbh->prepare(
1894"UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
1895        AND reservedate = ? AND constrainttype = ? AND (itemnumber = ? or itemnumber is NULL) "
1896        );
1897
0
        $sth->execute( $to_biblio, 'W', 'T' );
1898
0
        my $priority = 1;
1899
0
        while ( my $reserve = $sth->fetchrow_hashref() ) {
1900
0
            $upd_sth->execute(
1901                $priority, $to_biblio,
1902                $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
1903                $reserve->{'constrainttype'}, $reserve->{'itemnumber'}
1904            );
1905
0
            $priority++;
1906        }
1907    }
1908}
1909
1910
1911 - 1915
=head1 AUTHOR

Koha Development Team <http://koha-community.org/>

=cut
1916
19171;