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 any );
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
119 &IsAvailableForItemLevelRequest
125 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$notes,$title,$checkitem,$found)
131 $branch, $borrowernumber, $biblionumber,
132 $constraint, $bibitems, $priority, $notes,
133 $title, $checkitem, $found
136 GetReserveFee($borrowernumber, $biblionumber, $constraint,
138 my $dbh = C4::Context->dbh;
139 my $const = lc substr( $constraint, 0, 1 );
140 my @datearr = localtime(time);
142 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
145 # If the reserv had the waiting status, we had the value of the resdate
146 if ( $found eq 'W' ) {
147 $waitingdate = $resdate;
151 # updates take place here
153 my $nextacctno = &getnextacctno( $borrowernumber );
155 INSERT INTO accountlines
156 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
158 (?,?,now(),?,?,'Res',?)
160 my $usth = $dbh->prepare($query);
161 $usth->execute( $borrowernumber, $nextacctno, $fee,
162 "Reserve Charge - $title", $fee );
168 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
169 priority,reservenotes,itemnumber,found,waitingdate)
174 my $sth = $dbh->prepare($query);
176 $borrowernumber, $biblionumber, $resdate, $branch,
177 $const, $priority, $notes, $checkitem,
181 # Send e-mail to librarian if syspref is active
182 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
183 my $borrower = GetMemberDetails($borrowernumber);
184 my $biblio = GetBiblioData($biblionumber);
185 my $letter = C4::Letters::getletter( 'reserves', 'HOLDPLACED');
186 my $admin_email_address = C4::Context->preference('KohaAdminEmailAddress');
188 my %keys = (%$borrower, %$biblio);
189 foreach my $key (keys %keys) {
190 my $replacefield = "<<$key>>";
191 $letter->{content} =~ s/$replacefield/$keys{$key}/g;
192 $letter->{title} =~ s/$replacefield/$keys{$key}/g;
195 C4::Letters::EnqueueLetter(
197 borrowernumber => $borrowernumber,
198 message_transport_type => 'email',
199 from_address => $admin_email_address,
200 to_address => $admin_email_address,
209 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
211 INSERT INTO reserveconstraints
212 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
216 $sth = $dbh->prepare($query); # keep prepare outside the loop!
217 foreach (@$bibitems) {
218 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
221 return; # FIXME: why not have a useful return value?
226 =item GetPendingReserves
230 sub GetPendingReserves {
231 my ($filters, $startindex, $results) = @_;
233 $startindex = "0" if not $startindex;
236 my $indepbranch = C4::Context->preference('IndependantBranches') ? C4::Context->userenv->{'branch'} : undef;
237 my $dbh = C4::Context->dbh;
239 my $query = "SELECT DISTINCT(biblionumber) AS biblionumber
241 LEFT JOIN biblio USING(biblionumber)
242 WHERE reserves.found IS NULL ";
245 $query .= " AND branchcode = ? ";
246 push @query_params, $indepbranch;
249 my $sth = $dbh->prepare($query);
250 $sth->execute(@query_params);
254 while ( my $reserve = $sth->fetchrow_hashref ) {
256 unless( $line = $reserves{$reserve->{biblionumber}} ){
258 my $biblio = GetBiblioData($reserve->{biblionumber});
259 my @items = GetItemsInfo($reserve->{biblionumber});
261 $line->{title} = $biblio->{title};
262 foreach my $item (@items){
263 next if ($indepbranch && $indepbranch ne $item->{holdingbranch});
265 $line->{holdingbranches}->{$item->{holdingbranch}} = 1;
266 $line->{callnumbers}->{$item->{itemcallnumber}} = 1;
267 $line->{locations}->{$item->{location}} = 1;
268 $line->{itemtypes}->{$item->{itemtype}} = 1;
271 $line->{reservecount}++;
272 $reserves{$reserve->{biblionumber}} = $line if($line->{count});
276 foreach my $rkey (keys %reserves){
277 my $line = $reserves{$rkey};
278 $line->{biblionumber} = $rkey;
280 foreach my $datatype (qw/holdingbranches callnumbers locations itemtypes/){
282 foreach my $data (keys %{$line->{$datatype}}){
283 push @newdatas, { 'value' => $data}
285 $line->{$datatype} = \@newdatas;
288 foreach my $key (keys %$filters){
289 my $value = $filters->{$key};
290 $filtered = 0 if not (any { $_->{value} =~ /^$value$/ } @{$line->{$key}}) and $value;
292 push @reserves, $line if $filtered; # if (any { $_->{value} =~ /^FOSPC$/ } @{$line->{holdingbranches}});
295 my $count = scalar @reserves;
296 my $endindex = ($count > $startindex + $results) ? $startindex + $results : $count;
299 @reserves = @reserves[$startindex..$endindex];
303 return ($count, \@reserves);
306 =item GetReservesFromBiblionumber
308 ($count, $title_reserves) = &GetReserves($biblionumber);
310 This function gets the list of reservations for one C<$biblionumber>, returning a count
311 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
315 sub GetReservesFromBiblionumber {
316 my ($biblionumber) = shift or return (0, []);
317 my $dbh = C4::Context->dbh;
319 # Find the desired items in the reserves
322 timestamp AS rtimestamp,
332 WHERE biblionumber = ?
334 my $sth = $dbh->prepare($query);
335 $sth->execute($biblionumber);
338 while ( my $data = $sth->fetchrow_hashref ) {
340 # FIXME - What is this doing? How do constraints work?
341 if ($data->{constrainttype} eq 'o') {
343 SELECT biblioitemnumber
344 FROM reserveconstraints
345 WHERE biblionumber = ?
346 AND borrowernumber = ?
349 my $csth = $dbh->prepare($query);
350 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
352 while ( my $bibitemnos = $csth->fetchrow_array ) {
353 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
355 my $count = scalar @bibitemno;
357 # if we have two or more different specific itemtypes
358 # reserved by same person on same day
361 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
362 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
365 # Look up the book we just found.
366 $bdata = GetBiblioItemData( $bibitemno[0] );
368 # Add the results of this latest search to the current
370 # FIXME - An 'each' would probably be more efficient.
371 foreach my $key ( keys %$bdata ) {
372 $data->{$key} = $bdata->{$key};
375 push @results, $data;
377 return ( $#results + 1, \@results );
380 =item GetReservesFromItemnumber
382 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
384 TODO :: Description here
388 sub GetReservesFromItemnumber {
389 my ( $itemnumber ) = @_;
390 my $dbh = C4::Context->dbh;
392 SELECT reservedate,borrowernumber,branchcode
396 my $sth_res = $dbh->prepare($query);
397 $sth_res->execute($itemnumber);
398 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
399 return ( $reservedate, $borrowernumber, $branchcode );
402 =item GetReservesFromBorrowernumber
404 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
410 sub GetReservesFromBorrowernumber {
411 my ( $borrowernumber, $status ) = @_;
412 my $dbh = C4::Context->dbh;
415 $sth = $dbh->prepare("
418 WHERE borrowernumber=?
422 $sth->execute($borrowernumber,$status);
424 $sth = $dbh->prepare("
427 WHERE borrowernumber=?
430 $sth->execute($borrowernumber);
432 my $data = $sth->fetchall_arrayref({});
435 #-------------------------------------------------------------------------------------
436 =item CanBookBeReserved
438 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
442 sub CanBookBeReserved{
443 my ($borrowernumber, $biblionumber) = @_;
445 my $dbh = C4::Context->dbh;
446 my $biblio = GetBiblioData($biblionumber);
447 my $borrower = C4::Members::GetMember(borrowernumber=>$borrowernumber);
448 my $controlbranch = C4::Context->preference('ReservesControlBranch');
449 my $itype = C4::Context->preference('item-level_itypes');
450 my $reservesrights= C4::Context->preference('maxreserves');
451 my $reservescount = 0;
453 # we retrieve the user rights
458 if($controlbranch eq "ItemHomeLibrary"){
460 }elsif($controlbranch eq "PatronLibrary"){
461 $branchcode = $borrower->{branchcode};
464 $reservescount = GetReserveCount($borrowernumber);
466 if($reservescount < $reservesrights){
474 =item CanItemBeReserved
476 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
478 this function return 1 if an item can be issued by this borrower.
482 sub CanItemBeReserved{
483 my ($borrowernumber, $itemnumber) = @_;
485 my $dbh = C4::Context->dbh;
487 my $controlbranch = C4::Context->preference('ReservesControlBranch') || "ItemHomeLibrary";
488 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
489 my $allowedreserves = C4::Context->preference('maxreserves');
491 # we retrieve borrowers and items informations #
492 my $item = C4::Items::GetItem($itemnumber);
493 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
495 my $branchcode = "*";
496 my $branchfield = "reserves.branchcode";
498 if( $controlbranch eq "ItemHomeLibrary" ){
499 $branchcode = $item->{homebranch};
500 }elsif( $controlbranch eq "PatronLibrary" ){
501 $branchcode = $borrower->{branchcode};
504 # we retrieve user rights on this itemtype and branchcode
505 my $issuingrule = C4::Circulation::GetIssuingRule($borrower->{categorycode}, $item->{$itype}, $branchcode);
509 my $reservecount = GetReserveCount($borrowernumber);
511 # we check if it's ok or not
512 if(( $reservecount < $allowedreserves ) and $issuingrule->{maxissueqty} ){
518 #-------------------------------------------------------------------------------------
520 =item GetReserveCount
522 $number = &GetReserveCount($borrowernumber);
524 this function returns the number of reservation for a borrower given on input arg.
528 sub GetReserveCount {
529 my ($borrowernumber) = @_;
531 my $dbh = C4::Context->dbh;
534 SELECT COUNT(*) AS counter
536 WHERE borrowernumber = ?
538 my $sth = $dbh->prepare($query);
539 $sth->execute($borrowernumber);
540 my $row = $sth->fetchrow_hashref;
541 return $row->{counter};
544 =item GetOtherReserves
546 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
548 Check queued list of this document and check if this document must be transfered
552 sub GetOtherReserves {
553 my ($itemnumber) = @_;
556 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
557 if ($checkreserves) {
558 my $iteminfo = GetItem($itemnumber);
559 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
560 $messages->{'transfert'} = $checkreserves->{'branchcode'};
561 #minus priorities of others reservs
562 ModReserveMinusPriority(
564 $checkreserves->{'borrowernumber'},
565 $iteminfo->{'biblionumber'}
568 #launch the subroutine dotransfer
569 C4::Items::ModItemTransfer(
571 $iteminfo->{'holdingbranch'},
572 $checkreserves->{'branchcode'}
577 #step 2b : case of a reservation on the same branch, set the waiting status
579 $messages->{'waiting'} = 1;
580 ModReserveMinusPriority(
582 $checkreserves->{'borrowernumber'},
583 $iteminfo->{'biblionumber'}
585 ModReserveStatus($itemnumber,'W');
588 $nextreservinfo = $checkreserves->{'borrowernumber'};
591 return ( $messages, $nextreservinfo );
596 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
598 Calculate the fee for a reserve
603 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
606 my $dbh = C4::Context->dbh;
607 my $const = lc substr( $constraint, 0, 1 );
609 SELECT * FROM borrowers
610 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
611 WHERE borrowernumber = ?
613 my $sth = $dbh->prepare($query);
614 $sth->execute($borrowernumber);
615 my $data = $sth->fetchrow_hashref;
617 my $fee = $data->{'reservefee'};
618 my $cntitems = @- > $bibitems;
622 # check for items on issue
623 # first find biblioitem records
625 my $sth1 = $dbh->prepare(
626 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
627 WHERE (biblio.biblionumber = ?)"
629 $sth1->execute($biblionumber);
630 while ( my $data1 = $sth1->fetchrow_hashref ) {
631 if ( $const eq "a" ) {
632 push @biblioitems, $data1;
637 while ( $x < $cntitems ) {
638 if ( @$bibitems->{'biblioitemnumber'} ==
639 $data->{'biblioitemnumber'} )
645 if ( $const eq 'o' ) {
647 push @biblioitems, $data1;
652 push @biblioitems, $data1;
658 my $cntitemsfound = @biblioitems;
662 while ( $x < $cntitemsfound ) {
663 my $bitdata = $biblioitems[$x];
664 my $sth2 = $dbh->prepare(
666 WHERE biblioitemnumber = ?"
668 $sth2->execute( $bitdata->{'biblioitemnumber'} );
669 while ( my $itdata = $sth2->fetchrow_hashref ) {
670 my $sth3 = $dbh->prepare(
671 "SELECT * FROM issues
672 WHERE itemnumber = ?"
674 $sth3->execute( $itdata->{'itemnumber'} );
675 if ( my $isdata = $sth3->fetchrow_hashref ) {
683 if ( $allissued == 0 ) {
685 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
686 $rsth->execute($biblionumber);
687 if ( my $rdata = $rsth->fetchrow_hashref ) {
697 =item GetReservesToBranch
699 @transreserv = GetReservesToBranch( $frombranch );
701 Get reserve list for a given branch
705 sub GetReservesToBranch {
706 my ( $frombranch ) = @_;
707 my $dbh = C4::Context->dbh;
708 my $sth = $dbh->prepare(
709 "SELECT borrowernumber,reservedate,itemnumber,timestamp
714 $sth->execute( $frombranch );
717 while ( my $data = $sth->fetchrow_hashref ) {
718 $transreserv[$i] = $data;
721 return (@transreserv);
724 =item GetReservesForBranch
726 @transreserv = GetReservesForBranch($frombranch);
730 sub GetReservesForBranch {
731 my ($frombranch) = @_;
732 my $dbh = C4::Context->dbh;
733 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
738 $query .= " AND branchcode=? ";
740 $query .= "ORDER BY waitingdate" ;
741 my $sth = $dbh->prepare($query);
743 $sth->execute($frombranch);
750 while ( my $data = $sth->fetchrow_hashref ) {
751 $transreserv[$i] = $data;
754 return (@transreserv);
759 ($status, $reserve) = &CheckReserves($itemnumber);
761 Find a book in the reserves.
763 C<$itemnumber> is the book's item number.
765 As I understand it, C<&CheckReserves> looks for the given item in the
766 reserves. If it is found, that's a match, and C<$status> is set to
769 Otherwise, it finds the most important item in the reserves with the
770 same biblio number as this book (I'm not clear on this) and returns it
771 with C<$status> set to C<Reserved>.
773 C<&CheckReserves> returns a two-element list:
775 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
777 C<$reserve> is the reserve item that matched. It is a
778 reference-to-hash whose keys are mostly the fields of the reserves
779 table in the Koha database.
784 my ( $item, $barcode ) = @_;
785 my $dbh = C4::Context->dbh;
788 my $qitem = $dbh->quote($item);
789 # Look up the item by itemnumber
791 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
793 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
794 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
795 WHERE itemnumber=$qitem
797 $sth = $dbh->prepare($query);
800 my $qbc = $dbh->quote($barcode);
801 # Look up the item by barcode
803 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
805 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
806 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
807 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
808 AND biblioitems.itemtype = itemtypes.itemtype
811 $sth = $dbh->prepare($query);
813 # FIXME - This function uses $item later on. Ought to set it here.
816 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item ) = $sth->fetchrow_array;
818 # if item is not for loan it cannot be reserved either.....
819 # execption to notforloan is where items.notforloan < 0 : This indicates the item is holdable.
820 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
822 # get the reserves...
823 # Find this item in the reserves
824 my @reserves = _Findgroupreserve( $bibitem, $biblio, $item );
825 my $count = scalar @reserves;
827 # $priority and $highest are used to find the most important item
828 # in the list returned by &_Findgroupreserve. (The lower $priority,
829 # the more important the item.)
830 # $highest is the most important item we've seen so far.
831 my $priority = 10000000;
834 foreach my $res (@reserves) {
835 # FIXME - $item might be undefined or empty: the caller
836 # might be searching by barcode.
837 if ( $res->{'itemnumber'} == $item && $res->{'priority'} == 0) {
839 return ( "Waiting", $res );
842 # See if this item is more important than what we've got
844 if ( $res->{'priority'} != 0 && $res->{'priority'} < $priority )
846 $priority = $res->{'priority'};
853 # If we get this far, then no exact match was found. Print the
854 # most important item on the list. I think this tells us who's
855 # next in line to get this book.
856 if ($highest) { # FIXME - $highest might be undefined
857 $highest->{'itemnumber'} = $item;
858 return ( "Reserved", $highest );
867 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
871 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
872 cancel, but not both: if both are given, C<&CancelReserve> does
875 C<$borrowernumber> is the borrower number of the patron on whose
876 behalf the book was reserved.
878 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
879 priorities of the other people who are waiting on the book.
884 my ( $biblio, $item, $borr ) = @_;
885 my $dbh = C4::Context->dbh;
886 if ( $item and $borr ) {
887 # removing a waiting reserve record....
888 # update the database...
891 SET cancellationdate = now(),
895 AND borrowernumber = ?
897 my $sth = $dbh->prepare($query);
898 $sth->execute( $item, $borr );
901 INSERT INTO old_reserves
902 SELECT * FROM reserves
904 AND borrowernumber = ?
906 $sth = $dbh->prepare($query);
907 $sth->execute( $item, $borr );
911 AND borrowernumber = ?
913 $sth = $dbh->prepare($query);
914 $sth->execute( $item, $borr );
917 # removing a reserve record....
918 # get the prioritiy on this record....
921 SELECT priority FROM reserves
922 WHERE biblionumber = ?
923 AND borrowernumber = ?
924 AND cancellationdate IS NULL
925 AND itemnumber IS NULL
927 my $sth = $dbh->prepare($query);
928 $sth->execute( $biblio, $borr );
929 ($priority) = $sth->fetchrow_array;
933 SET cancellationdate = now(),
936 WHERE biblionumber = ?
937 AND borrowernumber = ?
940 # update the database, removing the record...
941 $sth = $dbh->prepare($query);
942 $sth->execute( $biblio, $borr );
946 INSERT INTO old_reserves
947 SELECT * FROM reserves
948 WHERE biblionumber = ?
949 AND borrowernumber = ?
951 $sth = $dbh->prepare($query);
952 $sth->execute( $biblio, $borr );
956 WHERE biblionumber = ?
957 AND borrowernumber = ?
959 $sth = $dbh->prepare($query);
960 $sth->execute( $biblio, $borr );
962 # now fix the priority on the others....
963 _FixPriority( $priority, $biblio );
971 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
975 Change a hold request's priority or cancel it.
977 C<$rank> specifies the effect of the change. If C<$rank>
978 is 'W' or 'n', nothing happens. This corresponds to leaving a
979 request alone when changing its priority in the holds queue
982 If C<$rank> is 'del', the hold request is cancelled.
984 If C<$rank> is an integer greater than zero, the priority of
985 the request is set to that value. Since priority != 0 means
986 that the item is not waiting on the hold shelf, setting the
987 priority to a non-zero value also sets the request's found
988 status and waiting date to NULL.
990 The optional C<$itemnumber> parameter is used only when
991 C<$rank> is a non-zero integer; if supplied, the itemnumber
992 of the hold request is set accordingly; if omitted, the itemnumber
995 FIXME: Note that the forgoing can have the effect of causing
996 item-level hold requests to turn into title-level requests. This
997 will be fixed once reserves has separate columns for requested
998 itemnumber and supplying itemnumber.
1003 #subroutine to update a reserve
1004 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1005 return if $rank eq "W";
1006 return if $rank eq "n";
1007 my $dbh = C4::Context->dbh;
1008 if ( $rank eq "del" ) {
1011 SET cancellationdate=now()
1012 WHERE biblionumber = ?
1013 AND borrowernumber = ?
1015 my $sth = $dbh->prepare($query);
1016 $sth->execute( $biblio, $borrower );
1019 INSERT INTO old_reserves
1022 WHERE biblionumber = ?
1023 AND borrowernumber = ?
1025 $sth = $dbh->prepare($query);
1026 $sth->execute( $biblio, $borrower );
1028 DELETE FROM reserves
1029 WHERE biblionumber = ?
1030 AND borrowernumber = ?
1032 $sth = $dbh->prepare($query);
1033 $sth->execute( $biblio, $borrower );
1036 elsif ($rank =~ /^\d+/ and $rank > 0) {
1038 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1039 WHERE biblionumber = ?
1040 AND borrowernumber = ?
1042 my $sth = $dbh->prepare($query);
1043 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1045 _FixPriority( $biblio, $borrower, $rank);
1049 =item ModReserveFill
1051 &ModReserveFill($reserve);
1053 Fill a reserve. If I understand this correctly, this means that the
1054 reserved book has been found and given to the patron who reserved it.
1056 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1057 whose keys are fields from the reserves table in the Koha database.
1061 sub ModReserveFill {
1063 my $dbh = C4::Context->dbh;
1064 # fill in a reserve record....
1065 my $biblionumber = $res->{'biblionumber'};
1066 my $borrowernumber = $res->{'borrowernumber'};
1067 my $resdate = $res->{'reservedate'};
1069 # get the priority on this record....
1071 my $query = "SELECT priority
1073 WHERE biblionumber = ?
1074 AND borrowernumber = ?
1075 AND reservedate = ?";
1076 my $sth = $dbh->prepare($query);
1077 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1078 ($priority) = $sth->fetchrow_array;
1081 # update the database...
1082 $query = "UPDATE reserves
1085 WHERE biblionumber = ?
1087 AND borrowernumber = ?
1089 $sth = $dbh->prepare($query);
1090 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1093 # move to old_reserves
1094 $query = "INSERT INTO old_reserves
1095 SELECT * FROM reserves
1096 WHERE biblionumber = ?
1098 AND borrowernumber = ?
1100 $sth = $dbh->prepare($query);
1101 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1102 $query = "DELETE FROM reserves
1103 WHERE biblionumber = ?
1105 AND borrowernumber = ?
1107 $sth = $dbh->prepare($query);
1108 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1110 # now fix the priority on the others (if the priority wasn't
1111 # already sorted!)....
1112 unless ( $priority == 0 ) {
1113 _FixPriority( $priority, $biblionumber );
1117 =item ModReserveStatus
1119 &ModReserveStatus($itemnumber, $newstatus);
1121 Update the reserve status for the active (priority=0) reserve.
1123 $itemnumber is the itemnumber the reserve is on
1125 $newstatus is the new status.
1129 sub ModReserveStatus {
1131 #first : check if we have a reservation for this item .
1132 my ($itemnumber, $newstatus) = @_;
1133 my $dbh = C4::Context->dbh;
1134 my $query = " UPDATE reserves
1135 SET found=?,waitingdate = now()
1140 my $sth_set = $dbh->prepare($query);
1141 $sth_set->execute( $newstatus, $itemnumber );
1144 =item ModReserveAffect
1146 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1148 This function affect an item and a status for a given reserve
1149 The itemnumber parameter is used to find the biblionumber.
1150 with the biblionumber & the borrowernumber, we can affect the itemnumber
1151 to the correct reserve.
1153 if $transferToDo is not set, then the status is set to "Waiting" as well.
1154 otherwise, a transfer is on the way, and the end of the transfer will
1155 take care of the waiting status
1158 sub ModReserveAffect {
1159 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1160 my $dbh = C4::Context->dbh;
1162 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1163 # attached to $itemnumber
1164 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1165 $sth->execute($itemnumber);
1166 my ($biblionumber) = $sth->fetchrow;
1167 # If we affect a reserve that has to be transfered, don't set to Waiting
1169 if ($transferToDo) {
1175 WHERE borrowernumber = ?
1176 AND biblionumber = ?
1180 # affect the reserve to Waiting as well.
1187 WHERE borrowernumber = ?
1188 AND biblionumber = ?
1191 $sth = $dbh->prepare($query);
1192 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1195 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo );
1200 =item ModReserveCancelAll
1202 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1204 function to cancel reserv,check other reserves, and transfer document if it's necessary
1208 sub ModReserveCancelAll {
1211 my ( $itemnumber, $borrowernumber ) = @_;
1213 #step 1 : cancel the reservation
1214 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1216 #step 2 launch the subroutine of the others reserves
1217 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1219 return ( $messages, $nextreservinfo );
1222 =item ModReserveMinusPriority
1224 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1226 Reduce the values of queuded list
1230 sub ModReserveMinusPriority {
1231 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1233 #first step update the value of the first person on reserv
1234 my $dbh = C4::Context->dbh;
1237 SET priority = 0 , itemnumber = ?
1238 WHERE borrowernumber=?
1241 my $sth_upd = $dbh->prepare($query);
1242 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1243 # second step update all others reservs
1244 _FixPriority($biblionumber, $borrowernumber, '0');
1247 =item GetReserveInfo
1249 &GetReserveInfo($borrowernumber,$biblionumber);
1251 Get item and borrower details for a current hold.
1252 Current implementation this query should have a single result.
1255 sub GetReserveInfo {
1256 my ( $borrowernumber, $biblionumber ) = @_;
1257 my $dbh = C4::Context->dbh;
1258 my $strsth="SELECT reservedate, reservenotes, reserves.borrowernumber,
1259 reserves.biblionumber, reserves.branchcode,
1260 notificationdate, reminderdate, priority, found,
1261 firstname, surname, phone,
1262 email, address, address2,
1263 cardnumber, city, zipcode,
1264 biblio.title, biblio.author,
1265 items.holdingbranch, items.itemcallnumber, items.itemnumber,
1267 FROM reserves left join items
1268 ON items.itemnumber=reserves.itemnumber ,
1271 reserves.borrowernumber=? &&
1272 reserves.biblionumber=? &&
1273 reserves.borrowernumber=borrowers.borrowernumber &&
1274 reserves.biblionumber=biblio.biblionumber ";
1275 my $sth = $dbh->prepare($strsth);
1276 $sth->execute($borrowernumber,$biblionumber);
1278 my $data = $sth->fetchrow_hashref;
1283 =item IsAvailableForItemLevelRequest
1287 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1291 Checks whether a given item record is available for an
1292 item-level hold request. An item is available if
1294 * it is not lost AND
1295 * it is not damaged AND
1296 * it is not withdrawn AND
1297 * does not have a not for loan value > 0
1299 Whether or not the item is currently on loan is
1300 also checked - if the AllowOnShelfHolds system preference
1301 is ON, an item can be requested even if it is currently
1302 on loan to somebody else. If the system preference
1303 is OFF, an item that is currently checked out cannot
1304 be the target of an item-level hold request.
1306 Note that IsAvailableForItemLevelRequest() does not
1307 check if the staff operator is authorized to place
1308 a request on the item - in particular,
1309 this routine does not check IndependantBranches
1310 and canreservefromotherbranches.
1314 sub IsAvailableForItemLevelRequest {
1315 my $itemnumber = shift;
1317 my $item = GetItem($itemnumber);
1319 # must check the notforloan setting of the itemtype
1320 # FIXME - a lot of places in the code do this
1321 # or something similar - need to be
1323 my $dbh = C4::Context->dbh;
1324 my $notforloan_query;
1325 if (C4::Context->preference('item-level_itypes')) {
1326 $notforloan_query = "SELECT itemtypes.notforloan
1328 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1329 WHERE itemnumber = ?";
1331 $notforloan_query = "SELECT itemtypes.notforloan
1333 JOIN biblioitems USING (biblioitemnumber)
1334 JOIN itemtypes USING (itemtype)
1335 WHERE itemnumber = ?";
1337 my $sth = $dbh->prepare($notforloan_query);
1338 $sth->execute($itemnumber);
1339 my $notforloan_per_itemtype = 0;
1340 if (my ($notforloan) = $sth->fetchrow_array) {
1341 $notforloan_per_itemtype = 1 if $notforloan;
1344 my $available_per_item = 1;
1345 $available_per_item = 0 if $item->{itemlost} or
1346 ( $item->{notforloan} > 0 ) or
1347 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1348 $item->{wthdrawn} or
1349 $notforloan_per_itemtype;
1351 if (C4::Context->preference('AllowOnShelfHolds')) {
1352 return $available_per_item;
1354 return ($available_per_item and $item->{onloan});
1360 &_FixPriority($biblio,$borrowernumber,$rank);
1362 Only used internally (so don't export it)
1363 Changed how this functions works #
1364 Now just gets an array of reserves in the rank order and updates them with
1365 the array index (+1 as array starts from 0)
1366 and if $rank is supplied will splice item from the array and splice it back in again
1367 in new priority rank
1372 my ( $biblio, $borrowernumber, $rank ) = @_;
1373 my $dbh = C4::Context->dbh;
1374 if ( $rank eq "del" ) {
1375 CancelReserve( $biblio, undef, $borrowernumber );
1377 if ( $rank eq "W" || $rank eq "0" ) {
1379 # make sure priority for waiting items is 0
1383 WHERE biblionumber = ?
1384 AND borrowernumber = ?
1387 my $sth = $dbh->prepare($query);
1388 $sth->execute( $biblio, $borrowernumber );
1394 # FIXME adding a new security in returned elements for changing priority,
1395 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1396 # This is wrong a waiting reserve has W set
1397 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1399 SELECT borrowernumber, reservedate, constrainttype
1401 WHERE biblionumber = ?
1402 AND ((found <> 'W') or found is NULL)
1403 ORDER BY priority ASC
1405 my $sth = $dbh->prepare($query);
1406 $sth->execute($biblio);
1407 while ( my $line = $sth->fetchrow_hashref ) {
1408 push( @reservedates, $line );
1409 push( @priority, $line );
1412 # To find the matching index
1414 my $key = -1; # to allow for 0 to be a valid result
1415 for ( $i = 0 ; $i < @priority ; $i++ ) {
1416 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1417 $key = $i; # save the index
1422 # if index exists in array then move it to new position
1423 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1424 my $new_rank = $rank -
1425 1; # $new_rank is what you want the new index to be in the array
1426 my $moving_item = splice( @priority, $key, 1 );
1427 splice( @priority, $new_rank, 0, $moving_item );
1430 # now fix the priority on those that are left....
1434 WHERE biblionumber = ?
1435 AND borrowernumber = ?
1439 $sth = $dbh->prepare($query);
1440 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1443 $priority[$j]->{'borrowernumber'},
1444 $priority[$j]->{'reservedate'}
1450 =item _Findgroupreserve
1452 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1454 Looks for an item-specific match first, then for a title-level match, returning the
1455 first match found. If neither, then we look for a 3rd kind of match based on
1456 reserve constraints.
1458 TODO: add more explanation about reserve constraints
1460 C<&_Findgroupreserve> returns :
1461 C<@results> is an array of references-to-hash whose keys are mostly
1462 fields from the reserves table of the Koha database, plus
1463 C<biblioitemnumber>.
1467 sub _Findgroupreserve {
1468 my ( $bibitem, $biblio, $itemnumber ) = @_;
1469 my $dbh = C4::Context->dbh;
1471 # check for exact targetted match
1472 # This select is valid for both item_level and biblio_level
1473 my $item_level_target_query = qq/
1474 SELECT reserves.biblionumber AS biblionumber,
1475 reserves.borrowernumber AS borrowernumber,
1476 reserves.reservedate AS reservedate,
1477 reserves.branchcode AS branchcode,
1478 reserves.cancellationdate AS cancellationdate,
1479 reserves.found AS found,
1480 reserves.reservenotes AS reservenotes,
1481 reserves.priority AS priority,
1482 reserves.timestamp AS timestamp,
1483 biblioitems.biblioitemnumber AS biblioitemnumber,
1484 reserves.itemnumber AS itemnumber
1486 JOIN biblioitems USING (biblionumber)
1487 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1490 AND hold_fill_targets.itemnumber = ?
1493 my $sth = $dbh->prepare($item_level_target_query);
1494 $sth->execute($itemnumber);
1495 my $data = $sth->fetchall_arrayref({});
1496 return @$data if (@$data);
1498 # check for title-level targetted match
1499 my $title_level_target_query = qq/
1500 SELECT reserves.biblionumber AS biblionumber,
1501 reserves.borrowernumber AS borrowernumber,
1502 reserves.reservedate AS reservedate,
1503 reserves.branchcode AS branchcode,
1504 reserves.cancellationdate AS cancellationdate,
1505 reserves.found AS found,
1506 reserves.reservenotes AS reservenotes,
1507 reserves.priority AS priority,
1508 reserves.timestamp AS timestamp,
1509 biblioitems.biblioitemnumber AS biblioitemnumber,
1510 reserves.itemnumber AS itemnumber
1512 JOIN biblioitems USING (biblionumber)
1513 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1516 AND item_level_request = 0
1517 AND hold_fill_targets.itemnumber = ?
1519 $sth = $dbh->prepare($title_level_target_query);
1520 $sth->execute($itemnumber);
1521 $data = $sth->fetchall_arrayref({});
1522 return @$data if (@$data);
1525 SELECT reserves.biblionumber AS biblionumber,
1526 reserves.borrowernumber AS borrowernumber,
1527 reserves.reservedate AS reservedate,
1528 reserves.branchcode AS branchcode,
1529 reserves.cancellationdate AS cancellationdate,
1530 reserves.found AS found,
1531 reserves.reservenotes AS reservenotes,
1532 reserves.priority AS priority,
1533 reserves.timestamp AS timestamp,
1534 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1535 reserves.itemnumber AS itemnumber
1537 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1538 WHERE reserves.biblionumber = ?
1539 AND ( ( reserveconstraints.biblioitemnumber = ?
1540 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1541 AND reserves.reservedate = reserveconstraints.reservedate )
1542 OR reserves.constrainttype='a' )
1543 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1545 $sth = $dbh->prepare($query);
1546 $sth->execute( $biblio, $bibitem, $itemnumber );
1547 $data = $sth->fetchall_arrayref({});
1548 return @$data if (@$data);
1552 =item _koha_notify_reserve
1556 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1560 Sends a notification to the patron that their hold has been filled (through
1561 ModReserveAffect, _not_ ModReserveFill)
1565 sub _koha_notify_reserve {
1566 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1568 my $dbh = C4::Context->dbh;
1569 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1571 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1573 my $sth = $dbh->prepare("
1576 WHERE borrowernumber = ?
1577 AND biblionumber = ?
1579 $sth->execute( $borrowernumber, $biblionumber );
1580 my $reserve = $sth->fetchrow_hashref;
1581 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1583 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1585 my $letter = getletter( 'reserves', $messagingprefs->{'letter_code'} );
1587 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1588 C4::Letters::parseletter( $letter, 'borrowers', $reserve->{'borrowernumber'} );
1589 C4::Letters::parseletter( $letter, 'biblio', $reserve->{'biblionumber'} );
1590 C4::Letters::parseletter( $letter, 'reserves', $reserve->{'borrowernumber'}, $reserve->{'biblionumber'} );
1592 if ( $reserve->{'itemnumber'} ) {
1593 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1595 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1597 if ( -1 != firstidx { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1598 # aka, 'email' in ->{'transports'}
1599 C4::Letters::EnqueueLetter(
1600 { letter => $letter,
1601 borrowernumber => $borrowernumber,
1602 message_transport_type => 'email',
1603 from_address => $admin_email_address,
1608 if ( -1 != firstidx { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1609 C4::Letters::EnqueueLetter(
1610 { letter => $letter,
1611 borrowernumber => $borrowernumber,
1612 message_transport_type => 'sms',
1622 Koha Developement team <info@koha.org>