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
14
14
14
114
58
444
use strict;
25#use warnings; FIXME - Bug 2505
26
14
14
14
100
104
212
use C4::Context;
27
14
14
14
1388
150
8648
use C4::Biblio;
28
14
14
14
880
104
2322
use C4::Members;
29
14
14
14
2149
155
3479
use C4::Items;
30
14
14
14
174
99
438
use C4::Circulation;
31
14
14
14
2254
87
3223
use C4::Accounts;
32
33# for _koha_notify_reserve
34
14
14
14
3322
87
601
use C4::Members::Messaging;
35
14
14
14
518
57
265
use C4::Members qw();
36
14
14
14
1865
174
3143
use C4::Letters;
37
14
14
14
232
142
1380
use C4::Branch qw( GetBranchDetail );
38
14
14
14
182
130
944
use C4::Dates qw( format_date_in_iso );
39
14
14
14
164
110
1140
use List::MoreUtils qw( firstidx );
40
41
14
14
14
171
101
2924
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
14
104
    $VERSION = 3.01;
89
14
122
    require Exporter;
90
14
251
    @ISA = qw(Exporter);
91
14
259
    @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        &ReserveSlip
126    );
127
14
117946
    @EXPORT_OK = qw( MergeHolds );
128}
129
130 - 134
=head2 AddReserve

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

=cut
135
136sub AddReserve {
137    my (
138
0
        $branch, $borrowernumber, $biblionumber,
139        $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
140        $title, $checkitem, $found
141    ) = @_;
142
0
    my $fee =
143          GetReserveFee($borrowernumber, $biblionumber, $constraint,
144            $bibitems );
145
0
    my $dbh = C4::Context->dbh;
146
0
    my $const = lc substr( $constraint, 0, 1 );
147
0
    $resdate = format_date_in_iso( $resdate ) if ( $resdate );
148
0
    $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
149
0
    if ($expdate) {
150
0
        $expdate = format_date_in_iso( $expdate );
151    } else {
152
0
        undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
153    }
154
0
    if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
155        # Make room in reserves for this before those of a later reserve date
156
0
        $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
157    }
158
0
    my $waitingdate;
159
160    # If the reserv had the waiting status, we had the value of the resdate
161
0
    if ( $found eq 'W' ) {
162
0
        $waitingdate = $resdate;
163    }
164
165    #eval {
166    # updates take place here
167
0
    if ( $fee > 0 ) {
168
0
        my $nextacctno = &getnextacctno( $borrowernumber );
169
0
        my $query = qq/
170        INSERT INTO accountlines
171            (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
172        VALUES
173            (?,?,now(),?,?,'Res',?)
174    /;
175
0
        my $usth = $dbh->prepare($query);
176
0
        $usth->execute( $borrowernumber, $nextacctno, $fee,
177            "Reserve Charge - $title", $fee );
178    }
179
180    #if ($const eq 'a'){
181
0
    my $query = qq/
182        INSERT INTO reserves
183            (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
184            priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
185        VALUES
186             (?,?,?,?,?,
187             ?,?,?,?,?,?)
188    /;
189
0
    my $sth = $dbh->prepare($query);
190
0
    $sth->execute(
191        $borrowernumber, $biblionumber, $resdate, $branch,
192        $const, $priority, $notes, $checkitem,
193        $found, $waitingdate, $expdate
194    );
195
196    # Send e-mail to librarian if syspref is active
197
0
    if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
198
0
        my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
199
0
        my $branch_details = C4::Branch::GetBranchDetail($borrower->{branchcode});
200
0
        if ( my $letter = C4::Letters::GetPreparedLetter (
201            module => 'reserves',
202            letter_code => 'HOLDPLACED',
203            branchcode => $branch,
204            tables => {
205                'branches' => $branch_details,
206                'borrowers' => $borrower,
207                'biblio' => $biblionumber,
208            },
209        ) ) {
210
211
0
            my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
212
213
0
            C4::Letters::EnqueueLetter(
214                { letter => $letter,
215                    borrowernumber => $borrowernumber,
216                    message_transport_type => 'email',
217                    from_address => $admin_email_address,
218                    to_address => $admin_email_address,
219                }
220            );
221        }
222    }
223
224    #}
225
0
    ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
226
0
    $query = qq/
227        INSERT INTO reserveconstraints
228            (borrowernumber,biblionumber,reservedate,biblioitemnumber)
229        VALUES
230            (?,?,?,?)
231    /;
232
0
    $sth = $dbh->prepare($query); # keep prepare outside the loop!
233
0
    foreach (@$bibitems) {
234
0
        $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
235    }
236
237
0
    return; # FIXME: why not have a useful return value?
238}
239
240 - 247
=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
248
249sub GetReservesFromBiblionumber {
250
0
    my ($biblionumber) = shift or return (0, []);
251
0
    my ($all_dates) = shift;
252
0
    my $dbh = C4::Context->dbh;
253
254    # Find the desired items in the reserves
255
0
    my $query = "
256        SELECT branchcode,
257                timestamp AS rtimestamp,
258                priority,
259                biblionumber,
260                borrowernumber,
261                reservedate,
262                constrainttype,
263                found,
264                itemnumber,
265                reservenotes,
266                expirationdate,
267                lowestPriority
268        FROM reserves
269        WHERE biblionumber = ? ";
270
0
    unless ( $all_dates ) {
271
0
        $query .= "AND reservedate <= CURRENT_DATE()";
272    }
273
0
    $query .= "ORDER BY priority";
274
0
    my $sth = $dbh->prepare($query);
275
0
    $sth->execute($biblionumber);
276
0
    my @results;
277
0
    my $i = 0;
278
0
    while ( my $data = $sth->fetchrow_hashref ) {
279
280        # FIXME - What is this doing? How do constraints work?
281
0
        if ($data->{constrainttype} eq 'o') {
282
0
            $query = '
283                SELECT biblioitemnumber
284                FROM reserveconstraints
285                WHERE biblionumber = ?
286                AND borrowernumber = ?
287                AND reservedate = ?
288            ';
289
0
            my $csth = $dbh->prepare($query);
290
0
            $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
291
0
            my @bibitemno;
292
0
            while ( my $bibitemnos = $csth->fetchrow_array ) {
293
0
                push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
294            }
295
0
            my $count = scalar @bibitemno;
296
297            # if we have two or more different specific itemtypes
298            # reserved by same person on same day
299
0
            my $bdata;
300
0
            if ( $count > 1 ) {
301
0
                $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
302
0
                $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
303            }
304            else {
305                # Look up the book we just found.
306
0
                $bdata = GetBiblioItemData( $bibitemno[0] );
307            }
308            # Add the results of this latest search to the current
309            # results.
310            # FIXME - An 'each' would probably be more efficient.
311
0
            foreach my $key ( keys %$bdata ) {
312
0
                $data->{$key} = $bdata->{$key};
313            }
314        }
315
0
        push @results, $data;
316    }
317
0
    return ( $#results + 1, \@results );
318}
319
320 - 326
=head2 GetReservesFromItemnumber

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

TODO :: Description here

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

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

TODO :: Descritpion

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

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

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

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

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

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

  $number = &GetReserveCount($borrowernumber);

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

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

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

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

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

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

Calculate the fee for a reserve

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

  @transreserv = GetReservesToBranch( $frombranch );

Get reserve list for a given branch

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

  @transreserv = GetReservesForBranch($frombranch);

=cut
701
702sub GetReservesForBranch {
703
0
    my ($frombranch) = @_;
704
0
    my $dbh = C4::Context->dbh;
705
0
        my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
706        FROM reserves
707        WHERE priority='0'
708            AND found='W' ";
709
0
    if ($frombranch){
710
0
        $query .= " AND branchcode=? ";
711        }
712
0
    $query .= "ORDER BY waitingdate" ;
713
0
    my $sth = $dbh->prepare($query);
714
0
    if ($frombranch){
715
0
                $sth->execute($frombranch);
716        }
717    else {
718
0
                $sth->execute();
719        }
720
0
    my @transreserv;
721
0
    my $i = 0;
722
0
    while ( my $data = $sth->fetchrow_hashref ) {
723
0
        $transreserv[$i] = $data;
724
0
        $i++;
725    }
726
0
    return (@transreserv);
727}
728
729sub GetReserveStatus {
730
0
    my ($itemnumber) = @_;
731
732
0
    my $dbh = C4::Context->dbh;
733
734
0
    my $itemstatus = $dbh->prepare("SELECT found FROM reserves WHERE itemnumber = ?");
735
736
0
    $itemstatus->execute($itemnumber);
737
0
    my ($found) = $itemstatus->fetchrow_array;
738
0
    return $found;
739}
740
741 - 766
=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
767
768sub CheckReserves {
769
0
    my ( $item, $barcode ) = @_;
770
0
    my $dbh = C4::Context->dbh;
771
0
    my $sth;
772
0
    my $select;
773
0
    if (C4::Context->preference('item-level_itypes')){
774
0
        $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 items.itype = itemtypes.itemtype
783        ";
784    }
785    else {
786
0
        $select = "
787           SELECT items.biblionumber,
788           items.biblioitemnumber,
789           itemtypes.notforloan,
790           items.notforloan AS itemnotforloan,
791           items.itemnumber
792           FROM items
793           LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
794           LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
795        ";
796    }
797
798
0
    if ($item) {
799
0
        $sth = $dbh->prepare("$select WHERE itemnumber = ?");
800
0
        $sth->execute($item);
801    }
802    else {
803
0
        $sth = $dbh->prepare("$select WHERE barcode = ?");
804
0
        $sth->execute($barcode);
805    }
806    # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
807
0
    my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
808
809
0
    return ( '' ) unless $itemnumber; # bail if we got nothing.
810
811    # if item is not for loan it cannot be reserved either.....
812    # execpt where items.notforloan < 0 : This indicates the item is holdable.
813
0
    return ( '' ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
814
815    # Find this item in the reserves
816
0
    my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
817
818    # $priority and $highest are used to find the most important item
819    # in the list returned by &_Findgroupreserve. (The lower $priority,
820    # the more important the item.)
821    # $highest is the most important item we've seen so far.
822
0
    my $highest;
823
0
    if (scalar @reserves) {
824
0
        my $priority = 10000000;
825
0
        foreach my $res (@reserves) {
826
0
            if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
827
0
                return ( "Waiting", $res, \@reserves ); # Found it
828            } else {
829                # See if this item is more important than what we've got so far
830
0
                if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
831
0
                    my $borrowerinfo=C4::Members::GetMember(borrowernumber => $res->{'borrowernumber'});
832
0
                    my $iteminfo=C4::Items::GetItem($itemnumber);
833
0
                    my $branch=C4::Circulation::_GetCircControlBranch($iteminfo,$borrowerinfo);
834
0
                    my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
835
0
                    next if ($branchitemrule->{'holdallowed'} == 0);
836
0
                    next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
837
0
                    $priority = $res->{'priority'};
838
0
                    $highest = $res;
839                }
840            }
841        }
842    }
843
844    # If we get this far, then no exact match was found.
845    # We return the most important (i.e. next) reservation.
846
0
    if ($highest) {
847
0
        $highest->{'itemnumber'} = $item;
848
0
        return ( "Reserved", $highest, \@reserves );
849    }
850
851
0
    return ( '' );
852}
853
854 - 860
=head2 CancelExpiredReserves

  CancelExpiredReserves();

Cancels all reserves with an expiration date from before today.

=cut
861
862sub CancelExpiredReserves {
863
864    # Cancel reserves that have passed their expiration date.
865
0
    my $dbh = C4::Context->dbh;
866
0
    my $sth = $dbh->prepare( "
867        SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
868        AND expirationdate IS NOT NULL
869        AND found IS NULL
870    " );
871
0
    $sth->execute();
872
873
0
    while ( my $res = $sth->fetchrow_hashref() ) {
874
0
        CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
875    }
876
877    # Cancel reserves that have been waiting too long
878
0
    if ( C4::Context->preference("ExpireReservesMaxPickUpDelay") ) {
879
0
        my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
880
0
        my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
881
882
0
        my $query = "SELECT * FROM reserves WHERE TO_DAYS( NOW() ) - TO_DAYS( waitingdate ) > ? AND found = 'W' AND priority = 0";
883
0
        $sth = $dbh->prepare( $query );
884
0
        $sth->execute( $max_pickup_delay );
885
886
0
        while (my $res = $sth->fetchrow_hashref ) {
887
0
            if ( $charge ) {
888
0
                manualinvoice($res->{'borrowernumber'}, $res->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
889            }
890
891
0
            CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
892        }
893    }
894
895}
896
897 - 913
=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> uses
C<$itemnumber>.

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
914
915sub CancelReserve {
916
0
    my ( $biblio, $item, $borr ) = @_;
917
0
    my $dbh = C4::Context->dbh;
918
0
        if ( $item and $borr ) {
919        # removing a waiting reserve record....
920        # update the database...
921
0
        my $query = "
922            UPDATE reserves
923            SET cancellationdate = now(),
924                   found = Null,
925                   priority = 0
926            WHERE itemnumber = ?
927             AND borrowernumber = ?
928        ";
929
0
        my $sth = $dbh->prepare($query);
930
0
        $sth->execute( $item, $borr );
931
0
        $sth->finish;
932
0
        $query = "
933            INSERT INTO old_reserves
934            SELECT * FROM reserves
935            WHERE itemnumber = ?
936             AND borrowernumber = ?
937        ";
938
0
        $sth = $dbh->prepare($query);
939
0
        $sth->execute( $item, $borr );
940
0
        $query = "
941            DELETE FROM reserves
942            WHERE itemnumber = ?
943             AND borrowernumber = ?
944        ";
945
0
        $sth = $dbh->prepare($query);
946
0
        $sth->execute( $item, $borr );
947    }
948    else {
949        # removing a reserve record....
950        # get the prioritiy on this record....
951
0
        my $priority;
952
0
        my $query = qq/
953            SELECT priority FROM reserves
954            WHERE biblionumber = ?
955              AND borrowernumber = ?
956              AND cancellationdate IS NULL
957              AND itemnumber IS NULL
958        /;
959
0
        my $sth = $dbh->prepare($query);
960
0
        $sth->execute( $biblio, $borr );
961
0
        ($priority) = $sth->fetchrow_array;
962
0
        $sth->finish;
963
0
        $query = qq/
964            UPDATE reserves
965            SET cancellationdate = now(),
966                   found = Null,
967                   priority = 0
968            WHERE biblionumber = ?
969              AND borrowernumber = ?
970        /;
971
972        # update the database, removing the record...
973
0
        $sth = $dbh->prepare($query);
974
0
        $sth->execute( $biblio, $borr );
975
0
        $sth->finish;
976
977
0
        $query = qq/
978            INSERT INTO old_reserves
979            SELECT * FROM reserves
980            WHERE biblionumber = ?
981              AND borrowernumber = ?
982        /;
983
0
        $sth = $dbh->prepare($query);
984
0
        $sth->execute( $biblio, $borr );
985
986
0
        $query = qq/
987            DELETE FROM reserves
988            WHERE biblionumber = ?
989              AND borrowernumber = ?
990        /;
991
0
        $sth = $dbh->prepare($query);
992
0
        $sth->execute( $biblio, $borr );
993
994        # now fix the priority on the others....
995
0
        _FixPriority( $biblio, $borr );
996    }
997}
998
999 - 1028
=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
1029
1030sub ModReserve {
1031    #subroutine to update a reserve
1032
0
    my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1033
0
     return if $rank eq "W";
1034
0
     return if $rank eq "n";
1035
0
    my $dbh = C4::Context->dbh;
1036
0
    if ( $rank eq "del" ) {
1037
0
        my $query = qq/
1038            UPDATE reserves
1039            SET cancellationdate=now()
1040            WHERE biblionumber = ?
1041             AND borrowernumber = ?
1042        /;
1043
0
        my $sth = $dbh->prepare($query);
1044
0
        $sth->execute( $biblio, $borrower );
1045
0
        $sth->finish;
1046
0
        $query = qq/
1047            INSERT INTO old_reserves
1048            SELECT *
1049            FROM reserves
1050            WHERE biblionumber = ?
1051             AND borrowernumber = ?
1052        /;
1053
0
        $sth = $dbh->prepare($query);
1054
0
        $sth->execute( $biblio, $borrower );
1055
0
        $query = qq/
1056            DELETE FROM reserves
1057            WHERE biblionumber = ?
1058             AND borrowernumber = ?
1059        /;
1060
0
        $sth = $dbh->prepare($query);
1061
0
        $sth->execute( $biblio, $borrower );
1062
1063    }
1064    elsif ($rank =~ /^\d+/ and $rank > 0) {
1065
0
        my $query = qq/
1066        UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1067            WHERE biblionumber = ?
1068             AND borrowernumber = ?
1069        /;
1070
0
        my $sth = $dbh->prepare($query);
1071
0
        $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1072
0
        $sth->finish;
1073
0
        _FixPriority( $biblio, $borrower, $rank);
1074    }
1075}
1076
1077 - 1087
=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
1088
1089sub ModReserveFill {
1090
0
    my ($res) = @_;
1091
0
    my $dbh = C4::Context->dbh;
1092    # fill in a reserve record....
1093
0
    my $biblionumber = $res->{'biblionumber'};
1094
0
    my $borrowernumber = $res->{'borrowernumber'};
1095
0
    my $resdate = $res->{'reservedate'};
1096
1097    # get the priority on this record....
1098
0
    my $priority;
1099
0
    my $query = "SELECT priority
1100                 FROM reserves
1101                 WHERE biblionumber = ?
1102                  AND borrowernumber = ?
1103                  AND reservedate = ?";
1104
0
    my $sth = $dbh->prepare($query);
1105
0
    $sth->execute( $biblionumber, $borrowernumber, $resdate );
1106
0
    ($priority) = $sth->fetchrow_array;
1107
0
    $sth->finish;
1108
1109    # update the database...
1110
0
    $query = "UPDATE reserves
1111                  SET found = 'F',
1112                         priority = 0
1113                 WHERE biblionumber = ?
1114                    AND reservedate = ?
1115                    AND borrowernumber = ?
1116                ";
1117
0
    $sth = $dbh->prepare($query);
1118
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1119
0
    $sth->finish;
1120
1121    # move to old_reserves
1122
0
    $query = "INSERT INTO old_reserves
1123                 SELECT * FROM reserves
1124                 WHERE biblionumber = ?
1125                    AND reservedate = ?
1126                    AND borrowernumber = ?
1127                ";
1128
0
    $sth = $dbh->prepare($query);
1129
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1130
0
    $query = "DELETE FROM reserves
1131                 WHERE biblionumber = ?
1132                    AND reservedate = ?
1133                    AND borrowernumber = ?
1134                ";
1135
0
    $sth = $dbh->prepare($query);
1136
0
    $sth->execute( $biblionumber, $resdate, $borrowernumber );
1137
1138    # now fix the priority on the others (if the priority wasn't
1139    # already sorted!)....
1140
0
    unless ( $priority == 0 ) {
1141
0
        _FixPriority( $biblionumber, $borrowernumber );
1142    }
1143}
1144
1145 - 1155
=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
1156
1157sub ModReserveStatus {
1158
1159    #first : check if we have a reservation for this item .
1160
0
    my ($itemnumber, $newstatus) = @_;
1161
0
    my $dbh = C4::Context->dbh;
1162
1163
0
    my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1164
0
    my $sth_set = $dbh->prepare($query);
1165
0
    $sth_set->execute( $newstatus, $itemnumber );
1166
1167
0
    if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1168
0
      CartToShelf( $itemnumber );
1169    }
1170}
1171
1172 - 1185
=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
1186
1187sub ModReserveAffect {
1188
0
    my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1189
0
    my $dbh = C4::Context->dbh;
1190
1191    # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1192    # attached to $itemnumber
1193
0
    my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1194
0
    $sth->execute($itemnumber);
1195
0
    my ($biblionumber) = $sth->fetchrow;
1196
1197    # get request - need to find out if item is already
1198    # waiting in order to not send duplicate hold filled notifications
1199
0
    my $request = GetReserveInfo($borrowernumber, $biblionumber);
1200
0
    my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1201
1202    # If we affect a reserve that has to be transfered, don't set to Waiting
1203
0
    my $query;
1204
0
    if ($transferToDo) {
1205
0
    $query = "
1206        UPDATE reserves
1207        SET priority = 0,
1208               itemnumber = ?,
1209               found = 'T'
1210        WHERE borrowernumber = ?
1211          AND biblionumber = ?
1212    ";
1213    }
1214    else {
1215    # affect the reserve to Waiting as well.
1216
0
        $query = "
1217            UPDATE reserves
1218            SET priority = 0,
1219                    found = 'W',
1220                    waitingdate = NOW(),
1221                    itemnumber = ?
1222            WHERE borrowernumber = ?
1223              AND biblionumber = ?
1224        ";
1225    }
1226
0
    $sth = $dbh->prepare($query);
1227
0
    $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1228
0
    _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1229
1230
0
    if ( C4::Context->preference("ReturnToShelvingCart") ) {
1231
0
      CartToShelf( $itemnumber );
1232    }
1233
1234
0
    return;
1235}
1236
1237 - 1243
=head2 ModReserveCancelAll

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

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

=cut
1244
1245sub ModReserveCancelAll {
1246
0
    my $messages;
1247
0
    my $nextreservinfo;
1248
0
    my ( $itemnumber, $borrowernumber ) = @_;
1249
1250    #step 1 : cancel the reservation
1251
0
    my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1252
1253    #step 2 launch the subroutine of the others reserves
1254
0
    ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1255
1256
0
    return ( $messages, $nextreservinfo );
1257}
1258
1259 - 1265
=head2 ModReserveMinusPriority

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

Reduce the values of queuded list     

=cut
1266
1267sub ModReserveMinusPriority {
1268
0
    my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1269
1270    #first step update the value of the first person on reserv
1271
0
    my $dbh = C4::Context->dbh;
1272
0
    my $query = "
1273        UPDATE reserves
1274        SET priority = 0 , itemnumber = ?
1275        WHERE borrowernumber=?
1276          AND biblionumber=?
1277    ";
1278
0
    my $sth_upd = $dbh->prepare($query);
1279
0
    $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1280    # second step update all others reservs
1281
0
    _FixPriority($biblionumber, $borrowernumber, '0');
1282}
1283
1284 - 1291
=head2 GetReserveInfo

  &GetReserveInfo($borrowernumber,$biblionumber);

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

=cut
1292
1293sub GetReserveInfo {
1294
0
        my ( $borrowernumber, $biblionumber ) = @_;
1295
0
    my $dbh = C4::Context->dbh;
1296
0
        my $strsth="SELECT
1297                       reservedate,
1298                       reservenotes,
1299                       reserves.borrowernumber,
1300                                   reserves.biblionumber,
1301                                   reserves.branchcode,
1302                                   reserves.waitingdate,
1303                                   notificationdate,
1304                                   reminderdate,
1305                                   priority,
1306                                   found,
1307                                   firstname,
1308                                   surname,
1309                                   phone,
1310                                   email,
1311                                   address,
1312                                   address2,
1313                                   cardnumber,
1314                                   city,
1315                                   zipcode,
1316                                   biblio.title,
1317                                   biblio.author,
1318                                   items.holdingbranch,
1319                                   items.itemcallnumber,
1320                                   items.itemnumber,
1321                                   items.location,
1322                                   barcode,
1323                                   notes
1324                        FROM reserves
1325                         LEFT JOIN items USING(itemnumber)
1326                     LEFT JOIN borrowers USING(borrowernumber)
1327                     LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1328                        WHERE
1329                                reserves.borrowernumber=?
1330                                AND reserves.biblionumber=?";
1331
0
        my $sth = $dbh->prepare($strsth);
1332
0
        $sth->execute($borrowernumber,$biblionumber);
1333
1334
0
        my $data = $sth->fetchrow_hashref;
1335
0
        return $data;
1336
1337}
1338
1339 - 1364
=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
1365
1366sub IsAvailableForItemLevelRequest {
1367
0
    my $itemnumber = shift;
1368
1369
0
    my $item = GetItem($itemnumber);
1370
1371    # must check the notforloan setting of the itemtype
1372    # FIXME - a lot of places in the code do this
1373    # or something similar - need to be
1374    # consolidated
1375
0
    my $dbh = C4::Context->dbh;
1376
0
    my $notforloan_query;
1377
0
    if (C4::Context->preference('item-level_itypes')) {
1378
0
        $notforloan_query = "SELECT itemtypes.notforloan
1379                             FROM items
1380                             JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1381                             WHERE itemnumber = ?";
1382    } else {
1383
0
        $notforloan_query = "SELECT itemtypes.notforloan
1384                             FROM items
1385                             JOIN biblioitems USING (biblioitemnumber)
1386                             JOIN itemtypes USING (itemtype)
1387                             WHERE itemnumber = ?";
1388    }
1389
0
    my $sth = $dbh->prepare($notforloan_query);
1390
0
    $sth->execute($itemnumber);
1391
0
    my $notforloan_per_itemtype = 0;
1392
0
    if (my ($notforloan) = $sth->fetchrow_array) {
1393
0
        $notforloan_per_itemtype = 1 if $notforloan;
1394    }
1395
1396
0
    my $available_per_item = 1;
1397
0
    $available_per_item = 0 if $item->{itemlost} or
1398                               ( $item->{notforloan} > 0 ) or
1399                               ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1400                               $item->{wthdrawn} or
1401                               $notforloan_per_itemtype;
1402
1403
1404
0
    if (C4::Context->preference('AllowOnShelfHolds')) {
1405
0
        return $available_per_item;
1406    } else {
1407
0
        return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "W"));
1408    }
1409}
1410
1411 - 1418
=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
1419
1420sub AlterPriority {
1421
0
    my ( $where, $borrowernumber, $biblionumber ) = @_;
1422
1423
0
    my $dbh = C4::Context->dbh;
1424
1425    ## Find this reserve
1426
0
    my $sth = $dbh->prepare('SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? AND cancellationdate IS NULL');
1427
0
    $sth->execute( $biblionumber, $borrowernumber );
1428
0
    my $reserve = $sth->fetchrow_hashref();
1429
0
    $sth->finish();
1430
1431
0
    if ( $where eq 'up' || $where eq 'down' ) {
1432
1433
0
      my $priority = $reserve->{'priority'};
1434
0
      $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1435
0
      _FixPriority( $biblionumber, $borrowernumber, $priority )
1436
1437    } elsif ( $where eq 'top' ) {
1438
1439
0
      _FixPriority( $biblionumber, $borrowernumber, '1' )
1440
1441    } elsif ( $where eq 'bottom' ) {
1442
1443
0
      _FixPriority( $biblionumber, $borrowernumber, '999999' )
1444
1445    }
1446}
1447
1448 - 1454
=head2 ToggleLowestPriority

  ToggleLowestPriority( $borrowernumber, $biblionumber );

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

=cut
1455
1456sub ToggleLowestPriority {
1457
0
    my ( $borrowernumber, $biblionumber ) = @_;
1458
1459
0
    my $dbh = C4::Context->dbh;
1460
1461
0
    my $sth = $dbh->prepare(
1462        "UPDATE reserves SET lowestPriority = NOT lowestPriority
1463         WHERE biblionumber = ?
1464         AND borrowernumber = ?"
1465    );
1466
0
    $sth->execute(
1467        $biblionumber,
1468        $borrowernumber,
1469    );
1470
0
    $sth->finish;
1471
1472
0
    _FixPriority( $biblionumber, $borrowernumber, '999999' );
1473}
1474
1475 - 1486
=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 
1487
1488sub _FixPriority {
1489
0
    my ( $biblio, $borrowernumber, $rank, $ignoreSetLowestRank ) = @_;
1490
0
    my $dbh = C4::Context->dbh;
1491
0
     if ( $rank eq "del" ) {
1492
0
         CancelReserve( $biblio, undef, $borrowernumber );
1493     }
1494
0
    if ( $rank eq "W" || $rank eq "0" ) {
1495
1496        # make sure priority for waiting or in-transit items is 0
1497
0
        my $query = qq/
1498            UPDATE reserves
1499            SET priority = 0
1500            WHERE biblionumber = ?
1501              AND borrowernumber = ?
1502              AND found IN ('W', 'T')
1503        /;
1504
0
        my $sth = $dbh->prepare($query);
1505
0
        $sth->execute( $biblio, $borrowernumber );
1506    }
1507
0
    my @priority;
1508
0
    my @reservedates;
1509
1510    # get whats left
1511# FIXME adding a new security in returned elements for changing priority,
1512# now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1513        # This is wrong a waiting reserve has W set
1514        # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1515
0
    my $query = qq/
1516        SELECT borrowernumber, reservedate, constrainttype
1517        FROM reserves
1518        WHERE biblionumber = ?
1519          AND ((found <> 'W' AND found <> 'T') or found is NULL)
1520        ORDER BY priority ASC
1521    /;
1522
0
    my $sth = $dbh->prepare($query);
1523
0
    $sth->execute($biblio);
1524
0
    while ( my $line = $sth->fetchrow_hashref ) {
1525
0
        push( @reservedates, $line );
1526
0
        push( @priority, $line );
1527    }
1528
1529    # To find the matching index
1530
0
    my $i;
1531
0
    my $key = -1; # to allow for 0 to be a valid result
1532    for ( $i = 0 ; $i < @priority ; $i++ ) {
1533
0
        if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1534
0
            $key = $i; # save the index
1535
0
            last;
1536        }
1537
0
    }
1538
1539    # if index exists in array then move it to new position
1540
0
    if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1541
0
        my $new_rank = $rank -
1542          1; # $new_rank is what you want the new index to be in the array
1543
0
        my $moving_item = splice( @priority, $key, 1 );
1544
0
        splice( @priority, $new_rank, 0, $moving_item );
1545    }
1546
1547    # now fix the priority on those that are left....
1548
0
    $query = "
1549            UPDATE reserves
1550            SET priority = ?
1551                WHERE biblionumber = ?
1552                 AND borrowernumber = ?
1553                 AND reservedate = ?
1554         AND found IS NULL
1555    ";
1556
0
    $sth = $dbh->prepare($query);
1557    for ( my $j = 0 ; $j < @priority ; $j++ ) {
1558
0
        $sth->execute(
1559            $j + 1, $biblio,
1560            $priority[$j]->{'borrowernumber'},
1561            $priority[$j]->{'reservedate'}
1562        );
1563
0
        $sth->finish;
1564
0
    }
1565
1566
0
    $sth = $dbh->prepare( "SELECT borrowernumber FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1567
0
    $sth->execute();
1568
1569
0
    unless ( $ignoreSetLowestRank ) {
1570
0
      while ( my $res = $sth->fetchrow_hashref() ) {
1571
0
        _FixPriority( $biblio, $res->{'borrowernumber'}, '999999', 1 );
1572      }
1573    }
1574}
1575
1576 - 1591
=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
1592
1593sub _Findgroupreserve {
1594
0
    my ( $bibitem, $biblio, $itemnumber ) = @_;
1595
0
    my $dbh = C4::Context->dbh;
1596
1597    # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1598    # check for exact targetted match
1599
0
    my $item_level_target_query = qq/
1600        SELECT reserves.biblionumber AS biblionumber,
1601               reserves.borrowernumber AS borrowernumber,
1602               reserves.reservedate AS reservedate,
1603               reserves.branchcode AS branchcode,
1604               reserves.cancellationdate AS cancellationdate,
1605               reserves.found AS found,
1606               reserves.reservenotes AS reservenotes,
1607               reserves.priority AS priority,
1608               reserves.timestamp AS timestamp,
1609               biblioitems.biblioitemnumber AS biblioitemnumber,
1610               reserves.itemnumber AS itemnumber
1611        FROM reserves
1612        JOIN biblioitems USING (biblionumber)
1613        JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1614        WHERE found IS NULL
1615        AND priority > 0
1616        AND item_level_request = 1
1617        AND itemnumber = ?
1618        AND reservedate <= CURRENT_DATE()
1619    /;
1620
0
    my $sth = $dbh->prepare($item_level_target_query);
1621
0
    $sth->execute($itemnumber);
1622
0
    my @results;
1623
0
    if ( my $data = $sth->fetchrow_hashref ) {
1624
0
        push( @results, $data );
1625    }
1626
0
    return @results if @results;
1627
1628    # check for title-level targetted match
1629
0
    my $title_level_target_query = qq/
1630        SELECT reserves.biblionumber AS biblionumber,
1631               reserves.borrowernumber AS borrowernumber,
1632               reserves.reservedate AS reservedate,
1633               reserves.branchcode AS branchcode,
1634               reserves.cancellationdate AS cancellationdate,
1635               reserves.found AS found,
1636               reserves.reservenotes AS reservenotes,
1637               reserves.priority AS priority,
1638               reserves.timestamp AS timestamp,
1639               biblioitems.biblioitemnumber AS biblioitemnumber,
1640               reserves.itemnumber AS itemnumber
1641        FROM reserves
1642        JOIN biblioitems USING (biblionumber)
1643        JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1644        WHERE found IS NULL
1645        AND priority > 0
1646        AND item_level_request = 0
1647        AND hold_fill_targets.itemnumber = ?
1648        AND reservedate <= CURRENT_DATE()
1649    /;
1650
0
    $sth = $dbh->prepare($title_level_target_query);
1651
0
    $sth->execute($itemnumber);
1652
0
    @results = ();
1653
0
    if ( my $data = $sth->fetchrow_hashref ) {
1654
0
        push( @results, $data );
1655    }
1656
0
    return @results if @results;
1657
1658
0
    my $query = qq/
1659        SELECT reserves.biblionumber AS biblionumber,
1660               reserves.borrowernumber AS borrowernumber,
1661               reserves.reservedate AS reservedate,
1662               reserves.waitingdate AS waitingdate,
1663               reserves.branchcode AS branchcode,
1664               reserves.cancellationdate AS cancellationdate,
1665               reserves.found AS found,
1666               reserves.reservenotes AS reservenotes,
1667               reserves.priority AS priority,
1668               reserves.timestamp AS timestamp,
1669               reserveconstraints.biblioitemnumber AS biblioitemnumber,
1670               reserves.itemnumber AS itemnumber
1671        FROM reserves
1672          LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1673        WHERE reserves.biblionumber = ?
1674          AND ( ( reserveconstraints.biblioitemnumber = ?
1675          AND reserves.borrowernumber = reserveconstraints.borrowernumber
1676          AND reserves.reservedate = reserveconstraints.reservedate )
1677          OR reserves.constrainttype='a' )
1678          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1679          AND reserves.reservedate <= CURRENT_DATE()
1680    /;
1681
0
    $sth = $dbh->prepare($query);
1682
0
    $sth->execute( $biblio, $bibitem, $itemnumber );
1683
0
    @results = ();
1684
0
    while ( my $data = $sth->fetchrow_hashref ) {
1685
0
        push( @results, $data );
1686    }
1687
0
    return @results;
1688}
1689
1690 - 1697
=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
1698
1699sub _koha_notify_reserve {
1700
0
    my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1701
1702
0
    my $dbh = C4::Context->dbh;
1703
0
    my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1704
1705    # Try to get the borrower's email address
1706
0
    my $to_address;
1707
0
    my $which_address = C4::Context->preference('AutoEmailPrimaryAddress');
1708    # If the system preference is set to 'first valid' (value == OFF), look up email address
1709
0
    if ($which_address eq 'OFF') {
1710
0
        $to_address = C4::Members::GetFirstValidEmailAddress( $borrowernumber );
1711    } else {
1712
0
        $to_address = $borrower->{$which_address};
1713    }
1714
1715
0
    my $letter_code;
1716
0
    my $print_mode = 0;
1717
0
    my $messagingprefs;
1718
0
    if ( $to_address || $borrower->{'smsalertnumber'} ) {
1719
0
        $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold_Filled' } );
1720
1721
0
        return if ( !defined( $messagingprefs->{'letter_code'} ) );
1722
0
        $letter_code = $messagingprefs->{'letter_code'};
1723    } else {
1724
0
        $letter_code = 'HOLD_PRINT';
1725
0
        $print_mode = 1;
1726    }
1727
1728
0
    my $sth = $dbh->prepare("
1729        SELECT *
1730        FROM reserves
1731        WHERE borrowernumber = ?
1732            AND biblionumber = ?
1733    ");
1734
0
    $sth->execute( $borrowernumber, $biblionumber );
1735
0
    my $reserve = $sth->fetchrow_hashref;
1736
0
    my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1737
1738
0
    my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1739
1740
0
    my $letter = C4::Letters::GetPreparedLetter (
1741        module => 'reserves',
1742        letter_code => $letter_code,
1743        branchcode => $reserve->{branchcode},
1744        tables => {
1745            'branches' => $branch_details,
1746            'borrowers' => $borrower,
1747            'biblio' => $biblionumber,
1748            'reserves' => $reserve,
1749            'items', $reserve->{'itemnumber'},
1750        },
1751        substitute => { today => C4::Dates->new()->output() },
1752    ) or die "Could not find a letter called '$letter_code' in the 'reserves' module";
1753
1754
1755
1756
0
    if ( $print_mode ) {
1757
0
        C4::Letters::EnqueueLetter( {
1758            letter => $letter,
1759            borrowernumber => $borrowernumber,
1760            message_transport_type => 'print',
1761        } );
1762
1763
0
        return;
1764    }
1765
1766
0
0
0
    if ( grep { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1767        # aka, 'email' in ->{'transports'}
1768
0
        C4::Letters::EnqueueLetter(
1769            { letter => $letter,
1770                borrowernumber => $borrowernumber,
1771                message_transport_type => 'email',
1772                from_address => $admin_email_address,
1773            }
1774        );
1775    }
1776
1777
0
0
0
    if ( grep { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1778
0
        C4::Letters::EnqueueLetter(
1779            { letter => $letter,
1780                borrowernumber => $borrowernumber,
1781                message_transport_type => 'sms',
1782            }
1783        );
1784    }
1785}
1786
1787 - 1803
=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
1804
1805sub _ShiftPriorityByDateAndPriority {
1806
0
    my ( $biblio, $resdate, $new_priority ) = @_;
1807
1808
0
    my $dbh = C4::Context->dbh;
1809
0
    my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1810
0
    my $sth = $dbh->prepare( $query );
1811
0
    $sth->execute( $biblio, $resdate, $new_priority );
1812
0
    my $min_priority = $sth->fetchrow;
1813    # if no such matches are found, $new_priority remains as original value
1814
0
    $new_priority = $min_priority if ( $min_priority );
1815
1816    # Shift the priority up by one; works in conjunction with the next SQL statement
1817
0
    $query = "UPDATE reserves
1818              SET priority = priority+1
1819              WHERE biblionumber = ?
1820              AND borrowernumber = ?
1821              AND reservedate = ?
1822              AND found IS NULL";
1823
0
    my $sth_update = $dbh->prepare( $query );
1824
1825    # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1826
0
    $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1827
0
    $sth = $dbh->prepare( $query );
1828
0
    $sth->execute( $new_priority, $biblio );
1829
0
    while ( my $row = $sth->fetchrow_hashref ) {
1830
0
        $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1831    }
1832
1833
0
    return $new_priority; # so the caller knows what priority they wind up receiving
1834}
1835
1836 - 1843
=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
1844
1845sub MoveReserve {
1846
0
    my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
1847
1848
0
    my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
1849
0
    return unless $res;
1850
1851
0
    my $biblionumber = $res->{biblionumber};
1852
0
    my $biblioitemnumber = $res->{biblioitemnumber};
1853
1854
0
    if ($res->{borrowernumber} == $borrowernumber) {
1855
0
        ModReserveFill($res);
1856    }
1857    else {
1858        # warn "Reserved";
1859        # The item is reserved by someone else.
1860        # Find this item in the reserves
1861
1862
0
        my $borr_res;
1863
0
        foreach (@$all_reserves) {
1864
0
            $_->{'borrowernumber'} == $borrowernumber or next;
1865
0
            $_->{'biblionumber'} == $biblionumber or next;
1866
1867
0
            $borr_res = $_;
1868
0
            last;
1869        }
1870
1871
0
        if ( $borr_res ) {
1872            # The item is reserved by the current patron
1873
0
            ModReserveFill($borr_res);
1874        }
1875
1876
0
        if ($cancelreserve) { # cancel reserves on this item
1877
0
            CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
1878
0
            CancelReserve($res->{'biblionumber'}, 0, $res->{'borrowernumber'});
1879        }
1880    }
1881}
1882
1883 - 1889
=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
1890
1891sub MergeHolds {
1892
0
    my ( $dbh, $to_biblio, $from_biblio ) = @_;
1893
0
    my $sth = $dbh->prepare(
1894        "SELECT count(*) as reservenumber FROM reserves WHERE biblionumber = ?"
1895    );
1896
0
    $sth->execute($from_biblio);
1897
0
    if ( my $data = $sth->fetchrow_hashref() ) {
1898
1899        # holds exist on old record, if not we don't need to do anything
1900
0
        $sth = $dbh->prepare(
1901            "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
1902
0
        $sth->execute( $to_biblio, $from_biblio );
1903
1904        # Reorder by date
1905        # don't reorder those already waiting
1906
1907
0
        $sth = $dbh->prepare(
1908"SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
1909        );
1910
0
        my $upd_sth = $dbh->prepare(
1911"UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
1912        AND reservedate = ? AND constrainttype = ? AND (itemnumber = ? or itemnumber is NULL) "
1913        );
1914
0
        $sth->execute( $to_biblio, 'W', 'T' );
1915
0
        my $priority = 1;
1916
0
        while ( my $reserve = $sth->fetchrow_hashref() ) {
1917
0
            $upd_sth->execute(
1918                $priority, $to_biblio,
1919                $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
1920                $reserve->{'constrainttype'}, $reserve->{'itemnumber'}
1921            );
1922
0
            $priority++;
1923        }
1924    }
1925}
1926
1927 - 1933
=head2 ReserveSlip

  ReserveSlip($branchcode, $borrowernumber, $biblionumber)

  Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef

=cut
1934
1935sub ReserveSlip {
1936
0
    my ($branch, $borrowernumber, $biblionumber) = @_;
1937
1938# return unless ( C4::Context->boolean_preference('printreserveslips') );
1939
1940
0
    my $reserve = GetReserveInfo($borrowernumber,$biblionumber )
1941      or return;
1942
1943
0
    return C4::Letters::GetPreparedLetter (
1944        module => 'circulation',
1945        letter_code => 'RESERVESLIP',
1946        branchcode => $branch,
1947        tables => {
1948            'reserves' => $reserve,
1949            'branches' => $reserve->{branchcode},
1950            'borrowers' => $reserve,
1951            'biblio' => $reserve,
1952            'items' => $reserve,
1953        },
1954    );
1955}
1956
1957 - 1961
=head1 AUTHOR

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

=cut
1962
19631;