3 # Copyright 2000-2002 Katipo Communications
4 # 2006 SAN Ouest Provence
5 # 2007 BibLibre Paul POULAIN
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
24 # use warnings; # FIXME: someday
32 # for _koha_notify_reserve
33 use C4::Members::Messaging;
34 use C4::Members qw( GetMember );
36 use C4::Branch qw( GetBranchDetail );
37 use List::MoreUtils qw( firstidx );
39 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
43 C4::Reserves - Koha functions for dealing with reservation.
51 this modules provides somes functions to deal with reservations.
53 Reserves are stored in reserves table.
54 The following columns contains important values :
55 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
56 =0 : then the reserve is being dealed
57 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
58 W(aiting) : the reserve has an itemnumber affected, and is on the way
59 F(inished) : the reserve has been completed, and is done
60 - itemnumber : empty : the reserve is still unaffected to an item
61 filled: the reserve is attached to an item
62 The complete workflow is :
63 ==== 1st use case ====
64 patron request a document, 1st available : P >0, F=NULL, I=NULL
65 a library having it run "transfertodo", and clic on the list
66 if there is no transfer to do, the reserve waiting
67 patron can pick it up P =0, F=W, I=filled
68 if there is a transfer to do, write in branchtransfer P =0, F=NULL, I=filled
69 The pickup library recieve the book, it check in P =0, F=W, I=filled
70 The patron borrow the book P =0, F=F, I=filled
72 ==== 2nd use case ====
73 patron requests a document, a given item,
74 If pickup is holding branch P =0, F=W, I=filled
75 If transfer needed, write in branchtransfer P =0, F=NULL, I=filled
76 The pickup library recieve the book, it checks it in P =0, F=W, I=filled
77 The patron borrow the book P =0, F=F, I=filled
86 # set the version for version checking
93 &GetReservesFromItemnumber
94 &GetReservesFromBiblionumber
95 &GetReservesFromBorrowernumber
109 &ModReserveMinusPriority
114 &IsAvailableForItemLevelRequest
120 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$notes,$title,$checkitem,$found)
126 $branch, $borrowernumber, $biblionumber,
127 $constraint, $bibitems, $priority, $notes,
128 $title, $checkitem, $found
131 GetReserveFee($borrowernumber, $biblionumber, $constraint,
133 my $dbh = C4::Context->dbh;
134 my $const = lc substr( $constraint, 0, 1 );
135 my @datearr = localtime(time);
137 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
140 # If the reserv had the waiting status, we had the value of the resdate
141 if ( $found eq 'W' ) {
142 $waitingdate = $resdate;
146 # updates take place here
148 my $nextacctno = &getnextacctno( $borrowernumber );
150 INSERT INTO accountlines
151 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
153 (?,?,now(),?,?,'Res',?)
155 my $usth = $dbh->prepare($query);
156 $usth->execute( $borrowernumber, $nextacctno, $fee,
157 "Reserve Charge - $title", $fee );
163 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
164 priority,reservenotes,itemnumber,found,waitingdate)
169 my $sth = $dbh->prepare($query);
171 $borrowernumber, $biblionumber, $resdate, $branch,
172 $const, $priority, $notes, $checkitem,
177 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
179 INSERT INTO reserveconstraints
180 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
184 $sth = $dbh->prepare($query); # keep prepare outside the loop!
185 foreach (@$bibitems) {
186 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
188 return; # FIXME: why not have a useful return value?
191 =item GetReservesFromBiblionumber
193 ($count, $title_reserves) = &GetReserves($biblionumber);
195 This function gets the list of reservations for one C<$biblionumber>, returning a count
196 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
200 sub GetReservesFromBiblionumber {
201 my ($biblionumber) = shift or return (0, []);
202 my $dbh = C4::Context->dbh;
204 # Find the desired items in the reserves
207 timestamp AS rtimestamp,
217 WHERE biblionumber = ?
219 my $sth = $dbh->prepare($query);
220 $sth->execute($biblionumber);
223 while ( my $data = $sth->fetchrow_hashref ) {
225 # FIXME - What is this doing? How do constraints work?
226 if ($data->{constrainttype} eq 'o') {
228 SELECT biblioitemnumber
229 FROM reserveconstraints
230 WHERE biblionumber = ?
231 AND borrowernumber = ?
234 my $csth = $dbh->prepare($query);
235 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
237 while ( my $bibitemnos = $csth->fetchrow_array ) {
238 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
240 my $count = scalar @bibitemno;
242 # if we have two or more different specific itemtypes
243 # reserved by same person on same day
246 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
247 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
250 # Look up the book we just found.
251 $bdata = GetBiblioItemData( $bibitemno[0] );
253 # Add the results of this latest search to the current
255 # FIXME - An 'each' would probably be more efficient.
256 foreach my $key ( keys %$bdata ) {
257 $data->{$key} = $bdata->{$key};
260 push @results, $data;
262 return ( $#results + 1, \@results );
265 =item GetReservesFromItemnumber
267 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
269 TODO :: Description here
273 sub GetReservesFromItemnumber {
274 my ( $itemnumber ) = @_;
275 my $dbh = C4::Context->dbh;
277 SELECT reservedate,borrowernumber,branchcode
281 my $sth_res = $dbh->prepare($query);
282 $sth_res->execute($itemnumber);
283 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
284 return ( $reservedate, $borrowernumber, $branchcode );
287 =item GetReservesFromBorrowernumber
289 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
295 sub GetReservesFromBorrowernumber {
296 my ( $borrowernumber, $status ) = @_;
297 my $dbh = C4::Context->dbh;
300 $sth = $dbh->prepare("
303 WHERE borrowernumber=?
307 $sth->execute($borrowernumber,$status);
309 $sth = $dbh->prepare("
312 WHERE borrowernumber=?
315 $sth->execute($borrowernumber);
317 my $data = $sth->fetchall_arrayref({});
320 #-------------------------------------------------------------------------------------
322 =item GetReserveCount
324 $number = &GetReserveCount($borrowernumber);
326 this function returns the number of reservation for a borrower given on input arg.
330 sub GetReserveCount {
331 my ($borrowernumber) = @_;
333 my $dbh = C4::Context->dbh;
336 SELECT COUNT(*) AS counter
338 WHERE borrowernumber = ?
340 my $sth = $dbh->prepare($query);
341 $sth->execute($borrowernumber);
342 my $row = $sth->fetchrow_hashref;
343 return $row->{counter};
346 =item GetOtherReserves
348 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
350 Check queued list of this document and check if this document must be transfered
354 sub GetOtherReserves {
355 my ($itemnumber) = @_;
358 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
359 if ($checkreserves) {
360 my $iteminfo = GetItem($itemnumber);
361 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
362 $messages->{'transfert'} = $checkreserves->{'branchcode'};
363 #minus priorities of others reservs
364 ModReserveMinusPriority(
366 $checkreserves->{'borrowernumber'},
367 $iteminfo->{'biblionumber'}
370 #launch the subroutine dotransfer
371 C4::Items::ModItemTransfer(
373 $iteminfo->{'holdingbranch'},
374 $checkreserves->{'branchcode'}
379 #step 2b : case of a reservation on the same branch, set the waiting status
381 $messages->{'waiting'} = 1;
382 ModReserveMinusPriority(
384 $checkreserves->{'borrowernumber'},
385 $iteminfo->{'biblionumber'}
387 ModReserveStatus($itemnumber,'W');
390 $nextreservinfo = $checkreserves->{'borrowernumber'};
393 return ( $messages, $nextreservinfo );
398 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
400 Calculate the fee for a reserve
405 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
408 my $dbh = C4::Context->dbh;
409 my $const = lc substr( $constraint, 0, 1 );
411 SELECT * FROM borrowers
412 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
413 WHERE borrowernumber = ?
415 my $sth = $dbh->prepare($query);
416 $sth->execute($borrowernumber);
417 my $data = $sth->fetchrow_hashref;
419 my $fee = $data->{'reservefee'};
420 my $cntitems = @- > $bibitems;
424 # check for items on issue
425 # first find biblioitem records
427 my $sth1 = $dbh->prepare(
428 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
429 WHERE (biblio.biblionumber = ?)"
431 $sth1->execute($biblionumber);
432 while ( my $data1 = $sth1->fetchrow_hashref ) {
433 if ( $const eq "a" ) {
434 push @biblioitems, $data1;
439 while ( $x < $cntitems ) {
440 if ( @$bibitems->{'biblioitemnumber'} ==
441 $data->{'biblioitemnumber'} )
447 if ( $const eq 'o' ) {
449 push @biblioitems, $data1;
454 push @biblioitems, $data1;
460 my $cntitemsfound = @biblioitems;
464 while ( $x < $cntitemsfound ) {
465 my $bitdata = $biblioitems[$x];
466 my $sth2 = $dbh->prepare(
468 WHERE biblioitemnumber = ?"
470 $sth2->execute( $bitdata->{'biblioitemnumber'} );
471 while ( my $itdata = $sth2->fetchrow_hashref ) {
472 my $sth3 = $dbh->prepare(
473 "SELECT * FROM issues
474 WHERE itemnumber = ?"
476 $sth3->execute( $itdata->{'itemnumber'} );
477 if ( my $isdata = $sth3->fetchrow_hashref ) {
485 if ( $allissued == 0 ) {
487 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
488 $rsth->execute($biblionumber);
489 if ( my $rdata = $rsth->fetchrow_hashref ) {
499 =item GetReservesToBranch
501 @transreserv = GetReservesToBranch( $frombranch );
503 Get reserve list for a given branch
507 sub GetReservesToBranch {
508 my ( $frombranch ) = @_;
509 my $dbh = C4::Context->dbh;
510 my $sth = $dbh->prepare(
511 "SELECT borrowernumber,reservedate,itemnumber,timestamp
516 $sth->execute( $frombranch );
519 while ( my $data = $sth->fetchrow_hashref ) {
520 $transreserv[$i] = $data;
523 return (@transreserv);
526 =item GetReservesForBranch
528 @transreserv = GetReservesForBranch($frombranch);
532 sub GetReservesForBranch {
533 my ($frombranch) = @_;
534 my $dbh = C4::Context->dbh;
535 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
540 $query .= " AND branchcode=? ";
542 $query .= "ORDER BY waitingdate" ;
543 my $sth = $dbh->prepare($query);
545 $sth->execute($frombranch);
552 while ( my $data = $sth->fetchrow_hashref ) {
553 $transreserv[$i] = $data;
556 return (@transreserv);
561 ($status, $reserve) = &CheckReserves($itemnumber);
562 ($status, $reserve) = &CheckReserves(undef, $barcode);
564 Find a book in the reserves.
566 C<$itemnumber> is the book's item number.
568 As I understand it, C<&CheckReserves> looks for the given item in the
569 reserves. If it is found, that's a match, and C<$status> is set to
572 Otherwise, it finds the most important item in the reserves with the
573 same biblio number as this book (I'm not clear on this) and returns it
574 with C<$status> set to C<Reserved>.
576 C<&CheckReserves> returns a two-element list:
578 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
580 C<$reserve> is the reserve item that matched. It is a
581 reference-to-hash whose keys are mostly the fields of the reserves
582 table in the Koha database.
587 my ( $item, $barcode ) = @_;
588 my $dbh = C4::Context->dbh;
591 SELECT items.biblionumber,
592 items.biblioitemnumber,
593 itemtypes.notforloan,
594 items.notforloan AS itemnotforloan,
597 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
598 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
602 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
603 $sth->execute($item);
606 $sth = $dbh->prepare("$select WHERE barcode = ?");
607 $sth->execute($barcode);
609 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
610 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
612 return ( 0, 0 ) unless $itemnumber; # bail if we got nothing.
614 # if item is not for loan it cannot be reserved either.....
615 # execpt where items.notforloan < 0 : This indicates the item is holdable.
616 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
618 # Find this item in the reserves
619 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
621 # $priority and $highest are used to find the most important item
622 # in the list returned by &_Findgroupreserve. (The lower $priority,
623 # the more important the item.)
624 # $highest is the most important item we've seen so far.
626 if (scalar @reserves) {
627 my $priority = 10000000;
628 foreach my $res (@reserves) {
629 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
630 return ( "Waiting", $res ); # Found it
632 # See if this item is more important than what we've got so far
633 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
634 $priority = $res->{'priority'};
641 # If we get this far, then no exact match was found.
642 # We return the most important (i.e. next) reservation.
644 $highest->{'itemnumber'} = $item;
645 return ( "Reserved", $highest );
654 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
658 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
659 cancel, but not both: if both are given, C<&CancelReserve> does
662 C<$borrowernumber> is the borrower number of the patron on whose
663 behalf the book was reserved.
665 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
666 priorities of the other people who are waiting on the book.
671 my ( $biblio, $item, $borr ) = @_;
672 my $dbh = C4::Context->dbh;
673 if ( $item and $borr ) {
674 # removing a waiting reserve record....
675 # update the database...
678 SET cancellationdate = now(),
682 AND borrowernumber = ?
684 my $sth = $dbh->prepare($query);
685 $sth->execute( $item, $borr );
688 INSERT INTO old_reserves
689 SELECT * FROM reserves
691 AND borrowernumber = ?
693 $sth = $dbh->prepare($query);
694 $sth->execute( $item, $borr );
698 AND borrowernumber = ?
700 $sth = $dbh->prepare($query);
701 $sth->execute( $item, $borr );
704 # removing a reserve record....
705 # get the prioritiy on this record....
708 SELECT priority FROM reserves
709 WHERE biblionumber = ?
710 AND borrowernumber = ?
711 AND cancellationdate IS NULL
712 AND itemnumber IS NULL
714 my $sth = $dbh->prepare($query);
715 $sth->execute( $biblio, $borr );
716 ($priority) = $sth->fetchrow_array;
720 SET cancellationdate = now(),
723 WHERE biblionumber = ?
724 AND borrowernumber = ?
727 # update the database, removing the record...
728 $sth = $dbh->prepare($query);
729 $sth->execute( $biblio, $borr );
733 INSERT INTO old_reserves
734 SELECT * FROM reserves
735 WHERE biblionumber = ?
736 AND borrowernumber = ?
738 $sth = $dbh->prepare($query);
739 $sth->execute( $biblio, $borr );
743 WHERE biblionumber = ?
744 AND borrowernumber = ?
746 $sth = $dbh->prepare($query);
747 $sth->execute( $biblio, $borr );
749 # now fix the priority on the others....
750 _FixPriority( $priority, $biblio );
758 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
762 Change a hold request's priority or cancel it.
764 C<$rank> specifies the effect of the change. If C<$rank>
765 is 'W' or 'n', nothing happens. This corresponds to leaving a
766 request alone when changing its priority in the holds queue
769 If C<$rank> is 'del', the hold request is cancelled.
771 If C<$rank> is an integer greater than zero, the priority of
772 the request is set to that value. Since priority != 0 means
773 that the item is not waiting on the hold shelf, setting the
774 priority to a non-zero value also sets the request's found
775 status and waiting date to NULL.
777 The optional C<$itemnumber> parameter is used only when
778 C<$rank> is a non-zero integer; if supplied, the itemnumber
779 of the hold request is set accordingly; if omitted, the itemnumber
782 FIXME: Note that the forgoing can have the effect of causing
783 item-level hold requests to turn into title-level requests. This
784 will be fixed once reserves has separate columns for requested
785 itemnumber and supplying itemnumber.
790 #subroutine to update a reserve
791 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
792 return if $rank eq "W";
793 return if $rank eq "n";
794 my $dbh = C4::Context->dbh;
795 if ( $rank eq "del" ) {
798 SET cancellationdate=now()
799 WHERE biblionumber = ?
800 AND borrowernumber = ?
802 my $sth = $dbh->prepare($query);
803 $sth->execute( $biblio, $borrower );
806 INSERT INTO old_reserves
809 WHERE biblionumber = ?
810 AND borrowernumber = ?
812 $sth = $dbh->prepare($query);
813 $sth->execute( $biblio, $borrower );
816 WHERE biblionumber = ?
817 AND borrowernumber = ?
819 $sth = $dbh->prepare($query);
820 $sth->execute( $biblio, $borrower );
823 elsif ($rank =~ /^\d+/ and $rank > 0) {
825 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
826 WHERE biblionumber = ?
827 AND borrowernumber = ?
829 my $sth = $dbh->prepare($query);
830 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
832 _FixPriority( $biblio, $borrower, $rank);
838 &ModReserveFill($reserve);
840 Fill a reserve. If I understand this correctly, this means that the
841 reserved book has been found and given to the patron who reserved it.
843 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
844 whose keys are fields from the reserves table in the Koha database.
850 my $dbh = C4::Context->dbh;
851 # fill in a reserve record....
852 my $biblionumber = $res->{'biblionumber'};
853 my $borrowernumber = $res->{'borrowernumber'};
854 my $resdate = $res->{'reservedate'};
856 # get the priority on this record....
858 my $query = "SELECT priority
860 WHERE biblionumber = ?
861 AND borrowernumber = ?
862 AND reservedate = ?";
863 my $sth = $dbh->prepare($query);
864 $sth->execute( $biblionumber, $borrowernumber, $resdate );
865 ($priority) = $sth->fetchrow_array;
868 # update the database...
869 $query = "UPDATE reserves
872 WHERE biblionumber = ?
874 AND borrowernumber = ?
876 $sth = $dbh->prepare($query);
877 $sth->execute( $biblionumber, $resdate, $borrowernumber );
880 # move to old_reserves
881 $query = "INSERT INTO old_reserves
882 SELECT * FROM reserves
883 WHERE biblionumber = ?
885 AND borrowernumber = ?
887 $sth = $dbh->prepare($query);
888 $sth->execute( $biblionumber, $resdate, $borrowernumber );
889 $query = "DELETE FROM reserves
890 WHERE biblionumber = ?
892 AND borrowernumber = ?
894 $sth = $dbh->prepare($query);
895 $sth->execute( $biblionumber, $resdate, $borrowernumber );
897 # now fix the priority on the others (if the priority wasn't
898 # already sorted!)....
899 unless ( $priority == 0 ) {
900 _FixPriority( $priority, $biblionumber );
904 =item ModReserveStatus
906 &ModReserveStatus($itemnumber, $newstatus);
908 Update the reserve status for the active (priority=0) reserve.
910 $itemnumber is the itemnumber the reserve is on
912 $newstatus is the new status.
916 sub ModReserveStatus {
918 #first : check if we have a reservation for this item .
919 my ($itemnumber, $newstatus) = @_;
920 my $dbh = C4::Context->dbh;
921 my $query = " UPDATE reserves
922 SET found=?,waitingdate = now()
927 my $sth_set = $dbh->prepare($query);
928 $sth_set->execute( $newstatus, $itemnumber );
931 =item ModReserveAffect
933 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
935 This function affect an item and a status for a given reserve
936 The itemnumber parameter is used to find the biblionumber.
937 with the biblionumber & the borrowernumber, we can affect the itemnumber
938 to the correct reserve.
940 if $transferToDo is not set, then the status is set to "Waiting" as well.
941 otherwise, a transfer is on the way, and the end of the transfer will
942 take care of the waiting status
945 sub ModReserveAffect {
946 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
947 my $dbh = C4::Context->dbh;
949 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
950 # attached to $itemnumber
951 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
952 $sth->execute($itemnumber);
953 my ($biblionumber) = $sth->fetchrow;
955 # get request - need to find out if item is already
956 # waiting in order to not send duplicate hold filled notifications
957 my $request = GetReserveInfo($borrowernumber, $biblionumber);
958 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
960 # If we affect a reserve that has to be transfered, don't set to Waiting
967 WHERE borrowernumber = ?
972 # affect the reserve to Waiting as well.
979 WHERE borrowernumber = ?
983 $sth = $dbh->prepare($query);
984 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
985 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
990 =item ModReserveCancelAll
992 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
994 function to cancel reserv,check other reserves, and transfer document if it's necessary
998 sub ModReserveCancelAll {
1001 my ( $itemnumber, $borrowernumber ) = @_;
1003 #step 1 : cancel the reservation
1004 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1006 #step 2 launch the subroutine of the others reserves
1007 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1009 return ( $messages, $nextreservinfo );
1012 =item ModReserveMinusPriority
1014 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1016 Reduce the values of queuded list
1020 sub ModReserveMinusPriority {
1021 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1023 #first step update the value of the first person on reserv
1024 my $dbh = C4::Context->dbh;
1027 SET priority = 0 , itemnumber = ?
1028 WHERE borrowernumber=?
1031 my $sth_upd = $dbh->prepare($query);
1032 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1033 # second step update all others reservs
1034 _FixPriority($biblionumber, $borrowernumber, '0');
1037 =item GetReserveInfo
1039 &GetReserveInfo($borrowernumber,$biblionumber);
1041 Get item and borrower details for a current hold.
1042 Current implementation this query should have a single result.
1045 sub GetReserveInfo {
1046 my ( $borrowernumber, $biblionumber ) = @_;
1047 my $dbh = C4::Context->dbh;
1048 my $strsth="SELECT reservedate, reservenotes, reserves.borrowernumber,
1049 reserves.biblionumber, reserves.branchcode,
1050 notificationdate, reminderdate, priority, found,
1051 firstname, surname, phone,
1052 email, address, address2,
1053 cardnumber, city, zipcode,
1054 biblio.title, biblio.author,
1055 items.holdingbranch, items.itemcallnumber, items.itemnumber,
1057 FROM reserves left join items
1058 ON items.itemnumber=reserves.itemnumber ,
1061 reserves.borrowernumber=? &&
1062 reserves.biblionumber=? &&
1063 reserves.borrowernumber=borrowers.borrowernumber &&
1064 reserves.biblionumber=biblio.biblionumber ";
1065 my $sth = $dbh->prepare($strsth);
1066 $sth->execute($borrowernumber,$biblionumber);
1068 my $data = $sth->fetchrow_hashref;
1073 =item IsAvailableForItemLevelRequest
1077 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1081 Checks whether a given item record is available for an
1082 item-level hold request. An item is available if
1084 * it is not lost AND
1085 * it is not damaged AND
1086 * it is not withdrawn AND
1087 * does not have a not for loan value > 0
1089 Whether or not the item is currently on loan is
1090 also checked - if the AllowOnShelfHolds system preference
1091 is ON, an item can be requested even if it is currently
1092 on loan to somebody else. If the system preference
1093 is OFF, an item that is currently checked out cannot
1094 be the target of an item-level hold request.
1096 Note that IsAvailableForItemLevelRequest() does not
1097 check if the staff operator is authorized to place
1098 a request on the item - in particular,
1099 this routine does not check IndependantBranches
1100 and canreservefromotherbranches.
1104 sub IsAvailableForItemLevelRequest {
1105 my $itemnumber = shift;
1107 my $item = GetItem($itemnumber);
1109 # must check the notforloan setting of the itemtype
1110 # FIXME - a lot of places in the code do this
1111 # or something similar - need to be
1113 my $dbh = C4::Context->dbh;
1114 my $notforloan_query;
1115 if (C4::Context->preference('item-level_itypes')) {
1116 $notforloan_query = "SELECT itemtypes.notforloan
1118 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1119 WHERE itemnumber = ?";
1121 $notforloan_query = "SELECT itemtypes.notforloan
1123 JOIN biblioitems USING (biblioitemnumber)
1124 JOIN itemtypes USING (itemtype)
1125 WHERE itemnumber = ?";
1127 my $sth = $dbh->prepare($notforloan_query);
1128 $sth->execute($itemnumber);
1129 my $notforloan_per_itemtype = 0;
1130 if (my ($notforloan) = $sth->fetchrow_array) {
1131 $notforloan_per_itemtype = 1 if $notforloan;
1134 my $available_per_item = 1;
1135 $available_per_item = 0 if $item->{itemlost} or
1136 ( $item->{notforloan} > 0 ) or
1137 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1138 $item->{wthdrawn} or
1139 $notforloan_per_itemtype;
1142 if (C4::Context->preference('AllowOnShelfHolds')) {
1143 return $available_per_item;
1145 return ($available_per_item and $item->{onloan});
1151 &_FixPriority($biblio,$borrowernumber,$rank);
1153 Only used internally (so don't export it)
1154 Changed how this functions works #
1155 Now just gets an array of reserves in the rank order and updates them with
1156 the array index (+1 as array starts from 0)
1157 and if $rank is supplied will splice item from the array and splice it back in again
1158 in new priority rank
1163 my ( $biblio, $borrowernumber, $rank ) = @_;
1164 my $dbh = C4::Context->dbh;
1165 if ( $rank eq "del" ) {
1166 CancelReserve( $biblio, undef, $borrowernumber );
1168 if ( $rank eq "W" || $rank eq "0" ) {
1170 # make sure priority for waiting items is 0
1174 WHERE biblionumber = ?
1175 AND borrowernumber = ?
1178 my $sth = $dbh->prepare($query);
1179 $sth->execute( $biblio, $borrowernumber );
1185 # FIXME adding a new security in returned elements for changing priority,
1186 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1187 # This is wrong a waiting reserve has W set
1188 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1190 SELECT borrowernumber, reservedate, constrainttype
1192 WHERE biblionumber = ?
1193 AND ((found <> 'W') or found is NULL)
1194 ORDER BY priority ASC
1196 my $sth = $dbh->prepare($query);
1197 $sth->execute($biblio);
1198 while ( my $line = $sth->fetchrow_hashref ) {
1199 push( @reservedates, $line );
1200 push( @priority, $line );
1203 # To find the matching index
1205 my $key = -1; # to allow for 0 to be a valid result
1206 for ( $i = 0 ; $i < @priority ; $i++ ) {
1207 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1208 $key = $i; # save the index
1213 # if index exists in array then move it to new position
1214 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1215 my $new_rank = $rank -
1216 1; # $new_rank is what you want the new index to be in the array
1217 my $moving_item = splice( @priority, $key, 1 );
1218 splice( @priority, $new_rank, 0, $moving_item );
1221 # now fix the priority on those that are left....
1225 WHERE biblionumber = ?
1226 AND borrowernumber = ?
1230 $sth = $dbh->prepare($query);
1231 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1234 $priority[$j]->{'borrowernumber'},
1235 $priority[$j]->{'reservedate'}
1241 =item _Findgroupreserve
1243 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1245 Looks for an item-specific match first, then for a title-level match, returning the
1246 first match found. If neither, then we look for a 3rd kind of match based on
1247 reserve constraints.
1249 TODO: add more explanation about reserve constraints
1251 C<&_Findgroupreserve> returns :
1252 C<@results> is an array of references-to-hash whose keys are mostly
1253 fields from the reserves table of the Koha database, plus
1254 C<biblioitemnumber>.
1258 sub _Findgroupreserve {
1259 my ( $bibitem, $biblio, $itemnumber ) = @_;
1260 my $dbh = C4::Context->dbh;
1262 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1263 # check for exact targetted match
1264 my $item_level_target_query = qq/
1265 SELECT reserves.biblionumber AS biblionumber,
1266 reserves.borrowernumber AS borrowernumber,
1267 reserves.reservedate AS reservedate,
1268 reserves.branchcode AS branchcode,
1269 reserves.cancellationdate AS cancellationdate,
1270 reserves.found AS found,
1271 reserves.reservenotes AS reservenotes,
1272 reserves.priority AS priority,
1273 reserves.timestamp AS timestamp,
1274 biblioitems.biblioitemnumber AS biblioitemnumber,
1275 reserves.itemnumber AS itemnumber
1277 JOIN biblioitems USING (biblionumber)
1278 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1281 AND item_level_request = 1
1284 my $sth = $dbh->prepare($item_level_target_query);
1285 $sth->execute($itemnumber);
1287 if ( my $data = $sth->fetchrow_hashref ) {
1288 push( @results, $data );
1290 return @results if @results;
1292 # check for title-level targetted match
1293 my $title_level_target_query = qq/
1294 SELECT reserves.biblionumber AS biblionumber,
1295 reserves.borrowernumber AS borrowernumber,
1296 reserves.reservedate AS reservedate,
1297 reserves.branchcode AS branchcode,
1298 reserves.cancellationdate AS cancellationdate,
1299 reserves.found AS found,
1300 reserves.reservenotes AS reservenotes,
1301 reserves.priority AS priority,
1302 reserves.timestamp AS timestamp,
1303 biblioitems.biblioitemnumber AS biblioitemnumber,
1304 reserves.itemnumber AS itemnumber
1306 JOIN biblioitems USING (biblionumber)
1307 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1310 AND item_level_request = 0
1311 AND hold_fill_targets.itemnumber = ?
1313 $sth = $dbh->prepare($title_level_target_query);
1314 $sth->execute($itemnumber);
1316 if ( my $data = $sth->fetchrow_hashref ) {
1317 push( @results, $data );
1319 return @results if @results;
1322 SELECT reserves.biblionumber AS biblionumber,
1323 reserves.borrowernumber AS borrowernumber,
1324 reserves.reservedate AS reservedate,
1325 reserves.branchcode AS branchcode,
1326 reserves.cancellationdate AS cancellationdate,
1327 reserves.found AS found,
1328 reserves.reservenotes AS reservenotes,
1329 reserves.priority AS priority,
1330 reserves.timestamp AS timestamp,
1331 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1332 reserves.itemnumber AS itemnumber
1334 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1335 WHERE reserves.biblionumber = ?
1336 AND ( ( reserveconstraints.biblioitemnumber = ?
1337 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1338 AND reserves.reservedate = reserveconstraints.reservedate )
1339 OR reserves.constrainttype='a' )
1340 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1342 $sth = $dbh->prepare($query);
1343 $sth->execute( $biblio, $bibitem, $itemnumber );
1345 while ( my $data = $sth->fetchrow_hashref ) {
1346 push( @results, $data );
1351 =item _koha_notify_reserve
1355 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1359 Sends a notification to the patron that their hold has been filled (through
1360 ModReserveAffect, _not_ ModReserveFill)
1364 sub _koha_notify_reserve {
1365 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1367 my $dbh = C4::Context->dbh;
1368 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1370 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1372 my $sth = $dbh->prepare("
1375 WHERE borrowernumber = ?
1376 AND biblionumber = ?
1378 $sth->execute( $borrowernumber, $biblionumber );
1379 my $reserve = $sth->fetchrow_hashref;
1380 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1382 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1384 my $letter = getletter( 'reserves', $messagingprefs->{'letter_code'} );
1386 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1387 C4::Letters::parseletter( $letter, 'borrowers', $reserve->{'borrowernumber'} );
1388 C4::Letters::parseletter( $letter, 'biblio', $reserve->{'biblionumber'} );
1389 C4::Letters::parseletter( $letter, 'reserves', $reserve->{'borrowernumber'}, $reserve->{'biblionumber'} );
1391 if ( $reserve->{'itemnumber'} ) {
1392 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1394 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1396 if ( -1 != firstidx { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1397 # aka, 'email' in ->{'transports'}
1398 C4::Letters::EnqueueLetter(
1399 { letter => $letter,
1400 borrowernumber => $borrowernumber,
1401 message_transport_type => 'email',
1402 from_address => $admin_email_address,
1407 if ( -1 != firstidx { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1408 C4::Letters::EnqueueLetter(
1409 { letter => $letter,
1410 borrowernumber => $borrowernumber,
1411 message_transport_type => 'sms',
1421 Koha Developement team <info@koha.org>