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
27 use C4::Dates qw/format_date format_date_in_iso/;
34 # for _koha_notify_reserve
35 use C4::Members::Messaging;
37 use C4::Branch qw( GetBranchDetail );
38 use List::MoreUtils qw( firstidx );
40 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
44 C4::Reserves - Koha functions for dealing with reservation.
52 this modules provides somes functions to deal with reservations.
54 Reserves are stored in reserves table.
55 The following columns contains important values :
56 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
57 =0 : then the reserve is being dealed
58 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
59 W(aiting) : the reserve has an itemnumber affected, and is on the way
60 T(ransfet) : the reserve has an itemnumber affected, and is beeing transfered to pickup branch
61 F(inished) : the reserve has been completed, and is done
62 - itemnumber : empty : the reserve is still unaffected to an item
63 filled: the reserve is attached to an item
64 The complete workflow is :
65 ==== 1st use case ====
66 patron request a document, 1st available : P >0, F=NULL, I=NULL
67 a library having it run "transfertodo", and clic on the list
68 if there is no transfer to do, the reserve waiting
69 patron can pick it up P =0, F=W, I=filled
70 if there is a transfer to do, write in branchtransfer P =0, F=NULL, I=filled
71 The pickup library recieve the book, it check in P =0, F=W, I=filled
72 The patron borrow the book P =0, F=F, I=filled
74 ==== 2nd use case ====
75 patron requests a document, a given item,
76 If pickup is holding branch P =0, F=W, I=filled
77 If transfer needed, write in branchtransfer P =0, F=NULL, I=filled
78 The pickup library recieve the book, it checks it in P =0, F=W, I=filled
79 The patron borrow the book P =0, F=F, I=filled
88 # set the version for version checking
96 &GetReservesFromItemnumber
97 &GetReservesFromBiblionumber
98 &GetReservesFromBorrowernumber
112 &ModReserveMinusPriority
117 &IsAvailableForItemLevelRequest
123 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$notes,$title,$checkitem,$found)
129 $branch, $borrowernumber, $biblionumber,
130 $constraint, $bibitems, $priority, $notes,
131 $title, $checkitem, $found
134 GetReserveFee($borrowernumber, $biblionumber, $constraint,
136 my $dbh = C4::Context->dbh;
137 my $const = lc substr( $constraint, 0, 1 );
138 my @datearr = localtime(time);
140 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
143 # If the reserv had the waiting status, we had the value of the resdate
144 if ( $found eq 'W' ) {
145 $waitingdate = $resdate;
149 # updates take place here
151 my $nextacctno = &getnextacctno( $borrowernumber );
153 INSERT INTO accountlines
154 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
156 (?,?,now(),?,?,'Res',?)
158 my $usth = $dbh->prepare($query);
159 $usth->execute( $borrowernumber, $nextacctno, $fee,
160 "Reserve Charge - $title", $fee );
166 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
167 priority,reservenotes,itemnumber,found,waitingdate)
172 my $sth = $dbh->prepare($query);
174 $borrowernumber, $biblionumber, $resdate, $branch,
175 $const, $priority, $notes, $checkitem,
179 # Send e-mail to librarian if syspref is active
180 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
181 my $borrower = GetMemberDetails($borrowernumber);
182 my $biblio = GetBiblioData($biblionumber);
183 my $letter = C4::Letters::getletter( 'reserves', 'HOLDPLACED');
184 my $admin_email_address = C4::Context->preference('KohaAdminEmailAddress');
186 my %keys = (%$borrower, %$biblio);
187 foreach my $key (keys %keys) {
188 my $replacefield = "<<$key>>";
189 $letter->{content} =~ s/$replacefield/$keys{$key}/g;
190 $letter->{title} =~ s/$replacefield/$keys{$key}/g;
193 C4::Letters::EnqueueLetter(
195 borrowernumber => $borrowernumber,
196 message_transport_type => 'email',
197 from_address => $admin_email_address,
198 to_address => $admin_email_address,
207 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
209 INSERT INTO reserveconstraints
210 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
214 $sth = $dbh->prepare($query); # keep prepare outside the loop!
215 foreach (@$bibitems) {
216 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
219 return; # FIXME: why not have a useful return value?
224 =item GetPendingReserves
228 sub GetPendingReserves {
229 my ($startdate, $enddate) = @_;
233 my $indepbranch = C4::Context->preference('IndependantBranches') ? C4::Context->userenv->{'branch'} : undef;
234 my $dbh = C4::Context->dbh;
236 my $query = "SELECT *
239 reserves.found IS NULL ";
241 if (!defined($startdate) or $startdate eq "") {
242 $startdate = format_date($startdate);
244 if (!defined($enddate) or $enddate eq "") {
245 $enddate = format_date($enddate);
249 $sqldatewhere .= " AND reservedate >= ?";
250 push @query_params, format_date_in_iso($startdate);
253 $sqldatewhere .= " AND reservedate <= ?";
254 push @query_params, format_date_in_iso($enddate);
256 $query .= $sqldatewhere;
259 $query .= " AND branchcode = ? ";
260 push @query_params, $indepbranch;
264 my $sth = $dbh->prepare($query);
265 $sth->execute(@query_params);
269 while ( my $reserve = $sth->fetchrow_hashref ) {
271 unless( $line = $reserves{$reserve->{biblionumber}} ){
273 my $biblio = GetBiblioData($reserve->{biblionumber});
274 my @items = GetItemsInfo($reserve->{biblionumber});
276 $line->{title} = $biblio->{title};
278 foreach my $item (@items){
279 next if ($indepbranch && $indepbranch ne $item->{holdingbranch});
281 $line->{holdingbranches}->{$item->{holdingbranch}} = 1;
282 $line->{callnumbers}->{$item->{itemcallnumber}} = 1;
283 $line->{locations}->{$item->{location}} = 1;
284 $line->{itemtypes}->{$item->{itemtype}} = 1;
287 $line->{reservecount}++;
288 $reserves{$reserve->{biblionumber}} = $line if($line->{count});
292 foreach my $rkey (keys %reserves){
293 my $line = $reserves{$rkey};
294 $line->{biblionumber} = $rkey;
296 foreach my $datatype (qw/holdingbranches callnumbers locations itemtypes/){
298 foreach my $data (keys %{$line->{$datatype}}){
299 @newdatas = { 'value' => $data}
301 $line->{$datatype} = \@newdatas;
304 push @reserves, $line;
310 =item GetReservesFromBiblionumber
312 ($count, $title_reserves) = &GetReserves($biblionumber);
314 This function gets the list of reservations for one C<$biblionumber>, returning a count
315 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
319 sub GetReservesFromBiblionumber {
320 my ($biblionumber) = shift or return (0, []);
321 my $dbh = C4::Context->dbh;
323 # Find the desired items in the reserves
326 timestamp AS rtimestamp,
336 WHERE biblionumber = ?
338 my $sth = $dbh->prepare($query);
339 $sth->execute($biblionumber);
342 while ( my $data = $sth->fetchrow_hashref ) {
344 # FIXME - What is this doing? How do constraints work?
345 if ($data->{constrainttype} eq 'o') {
347 SELECT biblioitemnumber
348 FROM reserveconstraints
349 WHERE biblionumber = ?
350 AND borrowernumber = ?
353 my $csth = $dbh->prepare($query);
354 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
356 while ( my $bibitemnos = $csth->fetchrow_array ) {
357 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
359 my $count = scalar @bibitemno;
361 # if we have two or more different specific itemtypes
362 # reserved by same person on same day
365 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
366 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
369 # Look up the book we just found.
370 $bdata = GetBiblioItemData( $bibitemno[0] );
372 # Add the results of this latest search to the current
374 # FIXME - An 'each' would probably be more efficient.
375 foreach my $key ( keys %$bdata ) {
376 $data->{$key} = $bdata->{$key};
379 push @results, $data;
381 return ( $#results + 1, \@results );
384 =item GetReservesFromItemnumber
386 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
388 TODO :: Description here
392 sub GetReservesFromItemnumber {
393 my ( $itemnumber ) = @_;
394 my $dbh = C4::Context->dbh;
396 SELECT reservedate,borrowernumber,branchcode
400 my $sth_res = $dbh->prepare($query);
401 $sth_res->execute($itemnumber);
402 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
403 return ( $reservedate, $borrowernumber, $branchcode );
406 =item GetReservesFromBorrowernumber
408 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
414 sub GetReservesFromBorrowernumber {
415 my ( $borrowernumber, $status ) = @_;
416 my $dbh = C4::Context->dbh;
419 $sth = $dbh->prepare("
422 WHERE borrowernumber=?
426 $sth->execute($borrowernumber,$status);
428 $sth = $dbh->prepare("
431 WHERE borrowernumber=?
434 $sth->execute($borrowernumber);
436 my $data = $sth->fetchall_arrayref({});
439 #-------------------------------------------------------------------------------------
441 =item GetReserveCount
443 $number = &GetReserveCount($borrowernumber);
445 this function returns the number of reservation for a borrower given on input arg.
449 sub GetReserveCount {
450 my ($borrowernumber) = @_;
452 my $dbh = C4::Context->dbh;
455 SELECT COUNT(*) AS counter
457 WHERE borrowernumber = ?
459 my $sth = $dbh->prepare($query);
460 $sth->execute($borrowernumber);
461 my $row = $sth->fetchrow_hashref;
462 return $row->{counter};
465 =item GetOtherReserves
467 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
469 Check queued list of this document and check if this document must be transfered
473 sub GetOtherReserves {
474 my ($itemnumber) = @_;
477 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
478 if ($checkreserves) {
479 my $iteminfo = GetItem($itemnumber);
480 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
481 $messages->{'transfert'} = $checkreserves->{'branchcode'};
482 #minus priorities of others reservs
483 ModReserveMinusPriority(
485 $checkreserves->{'borrowernumber'},
486 $iteminfo->{'biblionumber'}
489 #launch the subroutine dotransfer
490 C4::Items::ModItemTransfer(
492 $iteminfo->{'holdingbranch'},
493 $checkreserves->{'branchcode'}
498 #step 2b : case of a reservation on the same branch, set the waiting status
500 $messages->{'waiting'} = 1;
501 ModReserveMinusPriority(
503 $checkreserves->{'borrowernumber'},
504 $iteminfo->{'biblionumber'}
506 ModReserveStatus($itemnumber,'W');
509 $nextreservinfo = $checkreserves->{'borrowernumber'};
512 return ( $messages, $nextreservinfo );
517 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
519 Calculate the fee for a reserve
524 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
527 my $dbh = C4::Context->dbh;
528 my $const = lc substr( $constraint, 0, 1 );
530 SELECT * FROM borrowers
531 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
532 WHERE borrowernumber = ?
534 my $sth = $dbh->prepare($query);
535 $sth->execute($borrowernumber);
536 my $data = $sth->fetchrow_hashref;
538 my $fee = $data->{'reservefee'};
539 my $cntitems = @- > $bibitems;
543 # check for items on issue
544 # first find biblioitem records
546 my $sth1 = $dbh->prepare(
547 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
548 WHERE (biblio.biblionumber = ?)"
550 $sth1->execute($biblionumber);
551 while ( my $data1 = $sth1->fetchrow_hashref ) {
552 if ( $const eq "a" ) {
553 push @biblioitems, $data1;
558 while ( $x < $cntitems ) {
559 if ( @$bibitems->{'biblioitemnumber'} ==
560 $data->{'biblioitemnumber'} )
566 if ( $const eq 'o' ) {
568 push @biblioitems, $data1;
573 push @biblioitems, $data1;
579 my $cntitemsfound = @biblioitems;
583 while ( $x < $cntitemsfound ) {
584 my $bitdata = $biblioitems[$x];
585 my $sth2 = $dbh->prepare(
587 WHERE biblioitemnumber = ?"
589 $sth2->execute( $bitdata->{'biblioitemnumber'} );
590 while ( my $itdata = $sth2->fetchrow_hashref ) {
591 my $sth3 = $dbh->prepare(
592 "SELECT * FROM issues
593 WHERE itemnumber = ?"
595 $sth3->execute( $itdata->{'itemnumber'} );
596 if ( my $isdata = $sth3->fetchrow_hashref ) {
604 if ( $allissued == 0 ) {
606 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
607 $rsth->execute($biblionumber);
608 if ( my $rdata = $rsth->fetchrow_hashref ) {
618 =item GetReservesToBranch
620 @transreserv = GetReservesToBranch( $frombranch );
622 Get reserve list for a given branch
626 sub GetReservesToBranch {
627 my ( $frombranch ) = @_;
628 my $dbh = C4::Context->dbh;
629 my $sth = $dbh->prepare(
630 "SELECT borrowernumber,reservedate,itemnumber,timestamp
635 $sth->execute( $frombranch );
638 while ( my $data = $sth->fetchrow_hashref ) {
639 $transreserv[$i] = $data;
642 return (@transreserv);
645 =item GetReservesForBranch
647 @transreserv = GetReservesForBranch($frombranch);
651 sub GetReservesForBranch {
652 my ($frombranch) = @_;
653 my $dbh = C4::Context->dbh;
654 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
659 $query .= " AND branchcode=? ";
661 $query .= "ORDER BY waitingdate" ;
662 my $sth = $dbh->prepare($query);
664 $sth->execute($frombranch);
671 while ( my $data = $sth->fetchrow_hashref ) {
672 $transreserv[$i] = $data;
675 return (@transreserv);
680 ($status, $reserve) = &CheckReserves($itemnumber);
682 Find a book in the reserves.
684 C<$itemnumber> is the book's item number.
686 As I understand it, C<&CheckReserves> looks for the given item in the
687 reserves. If it is found, that's a match, and C<$status> is set to
690 Otherwise, it finds the most important item in the reserves with the
691 same biblio number as this book (I'm not clear on this) and returns it
692 with C<$status> set to C<Reserved>.
694 C<&CheckReserves> returns a two-element list:
696 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
698 C<$reserve> is the reserve item that matched. It is a
699 reference-to-hash whose keys are mostly the fields of the reserves
700 table in the Koha database.
705 my ( $item, $barcode ) = @_;
706 my $dbh = C4::Context->dbh;
709 my $qitem = $dbh->quote($item);
710 # Look up the item by itemnumber
712 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
714 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
715 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
716 WHERE itemnumber=$qitem
718 $sth = $dbh->prepare($query);
721 my $qbc = $dbh->quote($barcode);
722 # Look up the item by barcode
724 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
726 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
727 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
728 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
729 AND biblioitems.itemtype = itemtypes.itemtype
732 $sth = $dbh->prepare($query);
734 # FIXME - This function uses $item later on. Ought to set it here.
737 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item ) = $sth->fetchrow_array;
739 # if item is not for loan it cannot be reserved either.....
740 # execption to notforloan is where items.notforloan < 0 : This indicates the item is holdable.
741 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
743 # get the reserves...
744 # Find this item in the reserves
745 my @reserves = _Findgroupreserve( $bibitem, $biblio, $item );
746 my $count = scalar @reserves;
748 # $priority and $highest are used to find the most important item
749 # in the list returned by &_Findgroupreserve. (The lower $priority,
750 # the more important the item.)
751 # $highest is the most important item we've seen so far.
752 my $priority = 10000000;
755 foreach my $res (@reserves) {
756 # FIXME - $item might be undefined or empty: the caller
757 # might be searching by barcode.
758 if ( $res->{'itemnumber'} == $item && $res->{'priority'} == 0) {
760 return ( "Waiting", $res );
763 # See if this item is more important than what we've got
765 if ( $res->{'priority'} != 0 && $res->{'priority'} < $priority )
767 $priority = $res->{'priority'};
774 # If we get this far, then no exact match was found. Print the
775 # most important item on the list. I think this tells us who's
776 # next in line to get this book.
777 if ($highest) { # FIXME - $highest might be undefined
778 $highest->{'itemnumber'} = $item;
779 return ( "Reserved", $highest );
788 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
792 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
793 cancel, but not both: if both are given, C<&CancelReserve> does
796 C<$borrowernumber> is the borrower number of the patron on whose
797 behalf the book was reserved.
799 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
800 priorities of the other people who are waiting on the book.
805 my ( $biblio, $item, $borr ) = @_;
806 my $dbh = C4::Context->dbh;
807 if ( $item and $borr ) {
808 # removing a waiting reserve record....
809 # update the database...
812 SET cancellationdate = now(),
816 AND borrowernumber = ?
818 my $sth = $dbh->prepare($query);
819 $sth->execute( $item, $borr );
822 INSERT INTO old_reserves
823 SELECT * FROM reserves
825 AND borrowernumber = ?
827 $sth = $dbh->prepare($query);
828 $sth->execute( $item, $borr );
832 AND borrowernumber = ?
834 $sth = $dbh->prepare($query);
835 $sth->execute( $item, $borr );
838 # removing a reserve record....
839 # get the prioritiy on this record....
842 SELECT priority FROM reserves
843 WHERE biblionumber = ?
844 AND borrowernumber = ?
845 AND cancellationdate IS NULL
846 AND itemnumber IS NULL
848 my $sth = $dbh->prepare($query);
849 $sth->execute( $biblio, $borr );
850 ($priority) = $sth->fetchrow_array;
854 SET cancellationdate = now(),
857 WHERE biblionumber = ?
858 AND borrowernumber = ?
861 # update the database, removing the record...
862 $sth = $dbh->prepare($query);
863 $sth->execute( $biblio, $borr );
867 INSERT INTO old_reserves
868 SELECT * FROM reserves
869 WHERE biblionumber = ?
870 AND borrowernumber = ?
872 $sth = $dbh->prepare($query);
873 $sth->execute( $biblio, $borr );
877 WHERE biblionumber = ?
878 AND borrowernumber = ?
880 $sth = $dbh->prepare($query);
881 $sth->execute( $biblio, $borr );
883 # now fix the priority on the others....
884 _FixPriority( $priority, $biblio );
892 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
896 Change a hold request's priority or cancel it.
898 C<$rank> specifies the effect of the change. If C<$rank>
899 is 'W' or 'n', nothing happens. This corresponds to leaving a
900 request alone when changing its priority in the holds queue
903 If C<$rank> is 'del', the hold request is cancelled.
905 If C<$rank> is an integer greater than zero, the priority of
906 the request is set to that value. Since priority != 0 means
907 that the item is not waiting on the hold shelf, setting the
908 priority to a non-zero value also sets the request's found
909 status and waiting date to NULL.
911 The optional C<$itemnumber> parameter is used only when
912 C<$rank> is a non-zero integer; if supplied, the itemnumber
913 of the hold request is set accordingly; if omitted, the itemnumber
916 FIXME: Note that the forgoing can have the effect of causing
917 item-level hold requests to turn into title-level requests. This
918 will be fixed once reserves has separate columns for requested
919 itemnumber and supplying itemnumber.
924 #subroutine to update a reserve
925 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
926 return if $rank eq "W";
927 return if $rank eq "n";
928 my $dbh = C4::Context->dbh;
929 if ( $rank eq "del" ) {
932 SET cancellationdate=now()
933 WHERE biblionumber = ?
934 AND borrowernumber = ?
936 my $sth = $dbh->prepare($query);
937 $sth->execute( $biblio, $borrower );
940 INSERT INTO old_reserves
943 WHERE biblionumber = ?
944 AND borrowernumber = ?
946 $sth = $dbh->prepare($query);
947 $sth->execute( $biblio, $borrower );
950 WHERE biblionumber = ?
951 AND borrowernumber = ?
953 $sth = $dbh->prepare($query);
954 $sth->execute( $biblio, $borrower );
957 elsif ($rank =~ /^\d+/ and $rank > 0) {
959 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
960 WHERE biblionumber = ?
961 AND borrowernumber = ?
963 my $sth = $dbh->prepare($query);
964 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
966 _FixPriority( $biblio, $borrower, $rank);
972 &ModReserveFill($reserve);
974 Fill a reserve. If I understand this correctly, this means that the
975 reserved book has been found and given to the patron who reserved it.
977 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
978 whose keys are fields from the reserves table in the Koha database.
984 my $dbh = C4::Context->dbh;
985 # fill in a reserve record....
986 my $biblionumber = $res->{'biblionumber'};
987 my $borrowernumber = $res->{'borrowernumber'};
988 my $resdate = $res->{'reservedate'};
990 # get the priority on this record....
992 my $query = "SELECT priority
994 WHERE biblionumber = ?
995 AND borrowernumber = ?
996 AND reservedate = ?";
997 my $sth = $dbh->prepare($query);
998 $sth->execute( $biblionumber, $borrowernumber, $resdate );
999 ($priority) = $sth->fetchrow_array;
1002 # update the database...
1003 $query = "UPDATE reserves
1006 WHERE biblionumber = ?
1008 AND borrowernumber = ?
1010 $sth = $dbh->prepare($query);
1011 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1014 # move to old_reserves
1015 $query = "INSERT INTO old_reserves
1016 SELECT * FROM reserves
1017 WHERE biblionumber = ?
1019 AND borrowernumber = ?
1021 $sth = $dbh->prepare($query);
1022 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1023 $query = "DELETE FROM reserves
1024 WHERE biblionumber = ?
1026 AND borrowernumber = ?
1028 $sth = $dbh->prepare($query);
1029 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1031 # now fix the priority on the others (if the priority wasn't
1032 # already sorted!)....
1033 unless ( $priority == 0 ) {
1034 _FixPriority( $priority, $biblionumber );
1038 =item ModReserveStatus
1040 &ModReserveStatus($itemnumber, $newstatus);
1042 Update the reserve status for the active (priority=0) reserve.
1044 $itemnumber is the itemnumber the reserve is on
1046 $newstatus is the new status.
1050 sub ModReserveStatus {
1052 #first : check if we have a reservation for this item .
1053 my ($itemnumber, $newstatus) = @_;
1054 my $dbh = C4::Context->dbh;
1055 my $query = " UPDATE reserves
1056 SET found=?,waitingdate = now()
1061 my $sth_set = $dbh->prepare($query);
1062 $sth_set->execute( $newstatus, $itemnumber );
1065 =item ModReserveAffect
1067 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1069 This function affect an item and a status for a given reserve
1070 The itemnumber parameter is used to find the biblionumber.
1071 with the biblionumber & the borrowernumber, we can affect the itemnumber
1072 to the correct reserve.
1074 if $transferToDo is not set, then the status is set to "Waiting" as well.
1075 otherwise, a transfer is on the way, and the end of the transfer will
1076 take care of the waiting status
1079 sub ModReserveAffect {
1080 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1081 my $dbh = C4::Context->dbh;
1083 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1084 # attached to $itemnumber
1085 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1086 $sth->execute($itemnumber);
1087 my ($biblionumber) = $sth->fetchrow;
1088 # If we affect a reserve that has to be transfered, don't set to Waiting
1090 if ($transferToDo) {
1096 WHERE borrowernumber = ?
1097 AND biblionumber = ?
1101 # affect the reserve to Waiting as well.
1108 WHERE borrowernumber = ?
1109 AND biblionumber = ?
1112 $sth = $dbh->prepare($query);
1113 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1116 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo );
1121 =item ModReserveCancelAll
1123 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1125 function to cancel reserv,check other reserves, and transfer document if it's necessary
1129 sub ModReserveCancelAll {
1132 my ( $itemnumber, $borrowernumber ) = @_;
1134 #step 1 : cancel the reservation
1135 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1137 #step 2 launch the subroutine of the others reserves
1138 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1140 return ( $messages, $nextreservinfo );
1143 =item ModReserveMinusPriority
1145 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1147 Reduce the values of queuded list
1151 sub ModReserveMinusPriority {
1152 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1154 #first step update the value of the first person on reserv
1155 my $dbh = C4::Context->dbh;
1158 SET priority = 0 , itemnumber = ?
1159 WHERE borrowernumber=?
1162 my $sth_upd = $dbh->prepare($query);
1163 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1164 # second step update all others reservs
1165 _FixPriority($biblionumber, $borrowernumber, '0');
1168 =item GetReserveInfo
1170 &GetReserveInfo($borrowernumber,$biblionumber);
1172 Get item and borrower details for a current hold.
1173 Current implementation this query should have a single result.
1176 sub GetReserveInfo {
1177 my ( $borrowernumber, $biblionumber ) = @_;
1178 my $dbh = C4::Context->dbh;
1179 my $strsth="SELECT reservedate, reservenotes, reserves.borrowernumber,
1180 reserves.biblionumber, reserves.branchcode,
1181 notificationdate, reminderdate, priority, found,
1182 firstname, surname, phone,
1183 email, address, address2,
1184 cardnumber, city, zipcode,
1185 biblio.title, biblio.author,
1186 items.holdingbranch, items.itemcallnumber, items.itemnumber,
1188 FROM reserves left join items
1189 ON items.itemnumber=reserves.itemnumber ,
1192 reserves.borrowernumber=? &&
1193 reserves.biblionumber=? &&
1194 reserves.borrowernumber=borrowers.borrowernumber &&
1195 reserves.biblionumber=biblio.biblionumber ";
1196 my $sth = $dbh->prepare($strsth);
1197 $sth->execute($borrowernumber,$biblionumber);
1199 my $data = $sth->fetchrow_hashref;
1204 =item IsAvailableForItemLevelRequest
1208 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1212 Checks whether a given item record is available for an
1213 item-level hold request. An item is available if
1215 * it is not lost AND
1216 * it is not damaged AND
1217 * it is not withdrawn AND
1218 * does not have a not for loan value > 0
1220 Whether or not the item is currently on loan is
1221 also checked - if the AllowOnShelfHolds system preference
1222 is ON, an item can be requested even if it is currently
1223 on loan to somebody else. If the system preference
1224 is OFF, an item that is currently checked out cannot
1225 be the target of an item-level hold request.
1227 Note that IsAvailableForItemLevelRequest() does not
1228 check if the staff operator is authorized to place
1229 a request on the item - in particular,
1230 this routine does not check IndependantBranches
1231 and canreservefromotherbranches.
1235 sub IsAvailableForItemLevelRequest {
1236 my $itemnumber = shift;
1238 my $item = GetItem($itemnumber);
1240 # must check the notforloan setting of the itemtype
1241 # FIXME - a lot of places in the code do this
1242 # or something similar - need to be
1244 my $dbh = C4::Context->dbh;
1245 my $notforloan_query;
1246 if (C4::Context->preference('item-level_itypes')) {
1247 $notforloan_query = "SELECT itemtypes.notforloan
1249 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1250 WHERE itemnumber = ?";
1252 $notforloan_query = "SELECT itemtypes.notforloan
1254 JOIN biblioitems USING (biblioitemnumber)
1255 JOIN itemtypes USING (itemtype)
1256 WHERE itemnumber = ?";
1258 my $sth = $dbh->prepare($notforloan_query);
1259 $sth->execute($itemnumber);
1260 my $notforloan_per_itemtype = 0;
1261 if (my ($notforloan) = $sth->fetchrow_array) {
1262 $notforloan_per_itemtype = 1 if $notforloan;
1265 my $available_per_item = 1;
1266 $available_per_item = 0 if $item->{itemlost} or
1267 ( $item->{notforloan} > 0 ) or
1268 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1269 $item->{wthdrawn} or
1270 $notforloan_per_itemtype;
1272 if (C4::Context->preference('AllowOnShelfHolds')) {
1273 return $available_per_item;
1275 return ($available_per_item and $item->{onloan});
1281 &_FixPriority($biblio,$borrowernumber,$rank);
1283 Only used internally (so don't export it)
1284 Changed how this functions works #
1285 Now just gets an array of reserves in the rank order and updates them with
1286 the array index (+1 as array starts from 0)
1287 and if $rank is supplied will splice item from the array and splice it back in again
1288 in new priority rank
1293 my ( $biblio, $borrowernumber, $rank ) = @_;
1294 my $dbh = C4::Context->dbh;
1295 if ( $rank eq "del" ) {
1296 CancelReserve( $biblio, undef, $borrowernumber );
1298 if ( $rank eq "W" || $rank eq "0" ) {
1300 # make sure priority for waiting items is 0
1304 WHERE biblionumber = ?
1305 AND borrowernumber = ?
1308 my $sth = $dbh->prepare($query);
1309 $sth->execute( $biblio, $borrowernumber );
1315 # FIXME adding a new security in returned elements for changing priority,
1316 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1317 # This is wrong a waiting reserve has W set
1318 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1320 SELECT borrowernumber, reservedate, constrainttype
1322 WHERE biblionumber = ?
1323 AND ((found <> 'W') or found is NULL)
1324 ORDER BY priority ASC
1326 my $sth = $dbh->prepare($query);
1327 $sth->execute($biblio);
1328 while ( my $line = $sth->fetchrow_hashref ) {
1329 push( @reservedates, $line );
1330 push( @priority, $line );
1333 # To find the matching index
1335 my $key = -1; # to allow for 0 to be a valid result
1336 for ( $i = 0 ; $i < @priority ; $i++ ) {
1337 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1338 $key = $i; # save the index
1343 # if index exists in array then move it to new position
1344 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1345 my $new_rank = $rank -
1346 1; # $new_rank is what you want the new index to be in the array
1347 my $moving_item = splice( @priority, $key, 1 );
1348 splice( @priority, $new_rank, 0, $moving_item );
1351 # now fix the priority on those that are left....
1355 WHERE biblionumber = ?
1356 AND borrowernumber = ?
1360 $sth = $dbh->prepare($query);
1361 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1364 $priority[$j]->{'borrowernumber'},
1365 $priority[$j]->{'reservedate'}
1371 =item _Findgroupreserve
1373 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1375 Looks for an item-specific match first, then for a title-level match, returning the
1376 first match found. If neither, then we look for a 3rd kind of match based on
1377 reserve constraints.
1379 TODO: add more explanation about reserve constraints
1381 C<&_Findgroupreserve> returns :
1382 C<@results> is an array of references-to-hash whose keys are mostly
1383 fields from the reserves table of the Koha database, plus
1384 C<biblioitemnumber>.
1388 sub _Findgroupreserve {
1389 my ( $bibitem, $biblio, $itemnumber ) = @_;
1390 my $dbh = C4::Context->dbh;
1392 # check for exact targetted match
1393 # This select is valid for both item_level and biblio_level
1394 my $item_level_target_query = qq/
1395 SELECT reserves.biblionumber AS biblionumber,
1396 reserves.borrowernumber AS borrowernumber,
1397 reserves.reservedate AS reservedate,
1398 reserves.branchcode AS branchcode,
1399 reserves.cancellationdate AS cancellationdate,
1400 reserves.found AS found,
1401 reserves.reservenotes AS reservenotes,
1402 reserves.priority AS priority,
1403 reserves.timestamp AS timestamp,
1404 biblioitems.biblioitemnumber AS biblioitemnumber,
1405 reserves.itemnumber AS itemnumber
1407 JOIN biblioitems USING (biblionumber)
1408 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1411 AND hold_fill_targets.itemnumber = ?
1414 my $sth = $dbh->prepare($item_level_target_query);
1415 $sth->execute($itemnumber);
1416 my $data = $sth->fetchall_arrayref({});
1417 return @$data if (@$data);
1419 # check for title-level targetted match
1420 my $title_level_target_query = qq/
1421 SELECT reserves.biblionumber AS biblionumber,
1422 reserves.borrowernumber AS borrowernumber,
1423 reserves.reservedate AS reservedate,
1424 reserves.branchcode AS branchcode,
1425 reserves.cancellationdate AS cancellationdate,
1426 reserves.found AS found,
1427 reserves.reservenotes AS reservenotes,
1428 reserves.priority AS priority,
1429 reserves.timestamp AS timestamp,
1430 biblioitems.biblioitemnumber AS biblioitemnumber,
1431 reserves.itemnumber AS itemnumber
1433 JOIN biblioitems USING (biblionumber)
1434 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1437 AND item_level_request = 0
1438 AND hold_fill_targets.itemnumber = ?
1440 $sth = $dbh->prepare($title_level_target_query);
1441 $sth->execute($itemnumber);
1442 $data = $sth->fetchall_arrayref({});
1443 return @$data if (@$data);
1446 SELECT reserves.biblionumber AS biblionumber,
1447 reserves.borrowernumber AS borrowernumber,
1448 reserves.reservedate AS reservedate,
1449 reserves.branchcode AS branchcode,
1450 reserves.cancellationdate AS cancellationdate,
1451 reserves.found AS found,
1452 reserves.reservenotes AS reservenotes,
1453 reserves.priority AS priority,
1454 reserves.timestamp AS timestamp,
1455 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1456 reserves.itemnumber AS itemnumber
1458 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1459 WHERE reserves.biblionumber = ?
1460 AND ( ( reserveconstraints.biblioitemnumber = ?
1461 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1462 AND reserves.reservedate = reserveconstraints.reservedate )
1463 OR reserves.constrainttype='a' )
1464 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1466 $sth = $dbh->prepare($query);
1467 $sth->execute( $biblio, $bibitem, $itemnumber );
1468 $data = $sth->fetchall_arrayref({});
1469 return @$data if (@$data);
1473 =item _koha_notify_reserve
1477 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1481 Sends a notification to the patron that their hold has been filled (through
1482 ModReserveAffect, _not_ ModReserveFill)
1486 sub _koha_notify_reserve {
1487 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1489 my $dbh = C4::Context->dbh;
1490 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1492 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1494 my $sth = $dbh->prepare("
1497 WHERE borrowernumber = ?
1498 AND biblionumber = ?
1500 $sth->execute( $borrowernumber, $biblionumber );
1501 my $reserve = $sth->fetchrow_hashref;
1502 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1504 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1506 my $letter = getletter( 'reserves', $messagingprefs->{'letter_code'} );
1508 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1509 C4::Letters::parseletter( $letter, 'borrowers', $reserve->{'borrowernumber'} );
1510 C4::Letters::parseletter( $letter, 'biblio', $reserve->{'biblionumber'} );
1511 C4::Letters::parseletter( $letter, 'reserves', $reserve->{'borrowernumber'}, $reserve->{'biblionumber'} );
1513 if ( $reserve->{'itemnumber'} ) {
1514 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1516 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1518 if ( -1 != firstidx { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1519 # aka, 'email' in ->{'transports'}
1520 C4::Letters::EnqueueLetter(
1521 { letter => $letter,
1522 borrowernumber => $borrowernumber,
1523 message_transport_type => 'email',
1524 from_address => $admin_email_address,
1529 if ( -1 != firstidx { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1530 C4::Letters::EnqueueLetter(
1531 { letter => $letter,
1532 borrowernumber => $borrowernumber,
1533 message_transport_type => 'sms',
1543 Koha Developement team <info@koha.org>