3 # Copyright 2000-2002 Katipo Communications
4 # 2006 SAN Ouest Provence
5 # 2007-2010 BibLibre Paul POULAIN
8 # This file is part of Koha.
10 # Koha is free software; you can redistribute it and/or modify it
11 # under the terms of the GNU General Public License as published by
12 # the Free Software Foundation; either version 3 of the License, or
13 # (at your option) any later version.
15 # Koha is distributed in the hope that it will be useful, but
16 # WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with Koha; if not, see <http://www.gnu.org/licenses>.
25 #use warnings; FIXME - Bug 2505
33 # for _koha_notify_reserve
34 use C4::Members::Messaging;
37 use C4::Branch qw( GetBranchDetail );
38 use C4::Dates qw( format_date_in_iso );
44 use List::MoreUtils qw( firstidx any );
46 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
50 C4::Reserves - Koha functions for dealing with reservation.
58 This modules provides somes functions to deal with reservations.
60 Reserves are stored in reserves table.
61 The following columns contains important values :
62 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
63 =0 : then the reserve is being dealed
64 - found : NULL : means the patron requested the 1st available, and we haven't chosen the item
65 T(ransit) : the reserve is linked to an item but is in transit to the pickup branch
66 W(aiting) : the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf
67 F(inished) : the reserve has been completed, and is done
68 - itemnumber : empty : the reserve is still unaffected to an item
69 filled: the reserve is attached to an item
70 The complete workflow is :
71 ==== 1st use case ====
72 patron request a document, 1st available : P >0, F=NULL, I=NULL
73 a library having it run "transfertodo", and clic on the list
74 if there is no transfer to do, the reserve waiting
75 patron can pick it up P =0, F=W, I=filled
76 if there is a transfer to do, write in branchtransfer P =0, F=T, I=filled
77 The pickup library receive the book, it check in P =0, F=W, I=filled
78 The patron borrow the book P =0, F=F, I=filled
80 ==== 2nd use case ====
81 patron requests a document, a given item,
82 If pickup is holding branch P =0, F=W, I=filled
83 If transfer needed, write in branchtransfer P =0, F=T, I=filled
84 The pickup library receive the book, it checks it in P =0, F=W, I=filled
85 The patron borrow the book P =0, F=F, I=filled
92 # set the version for version checking
93 $VERSION = 3.07.00.049;
100 &GetReservesFromItemnumber
101 &GetReservesFromBiblionumber
102 &GetReservesFromBorrowernumber
103 &GetReservesForBranch
117 &ModReserveMinusPriority
123 &CanReserveBeCanceledFromOpac
125 &CancelExpiredReserves
127 &AutoUnsuspendReserves
129 &IsAvailableForItemLevelRequest
131 &OPACItemHoldsAllowed
134 &ToggleLowestPriority
140 &GetReservesControlBranch
144 @EXPORT_OK = qw( MergeHolds );
149 AddReserve($branch,$borrowernumber,$biblionumber,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
155 $branch, $borrowernumber, $biblionumber,
156 $bibitems, $priority, $resdate, $expdate, $notes,
157 $title, $checkitem, $found
160 GetReserveFee($borrowernumber, $biblionumber,
162 my $dbh = C4::Context->dbh;
163 $resdate = format_date_in_iso( $resdate ) if ( $resdate );
164 $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
166 $expdate = format_date_in_iso( $expdate );
168 undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
170 if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
171 # Make room in reserves for this before those of a later reserve date
172 $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
176 # If the reserv had the waiting status, we had the value of the resdate
177 if ( $found eq 'W' ) {
178 $waitingdate = $resdate;
182 # updates take place here
184 my $nextacctno = &getnextacctno( $borrowernumber );
186 INSERT INTO accountlines
187 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
189 (?,?,now(),?,?,'Res',?)
191 my $usth = $dbh->prepare($query);
192 $usth->execute( $borrowernumber, $nextacctno, $fee,
193 "Reserve Charge - $title", $fee );
198 (borrowernumber,biblionumber,reservedate,branchcode,
199 priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
204 my $sth = $dbh->prepare($query);
206 $borrowernumber, $biblionumber, $resdate, $branch, $priority,
207 $notes, $checkitem, $found, $waitingdate, $expdate
209 my $reserve_id = $sth->{mysql_insertid};
211 # Send e-mail to librarian if syspref is active
212 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
213 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
214 my $branch_details = C4::Branch::GetBranchDetail($borrower->{branchcode});
215 if ( my $letter = C4::Letters::GetPreparedLetter (
216 module => 'reserves',
217 letter_code => 'HOLDPLACED',
218 branchcode => $branch,
220 'branches' => $branch_details,
221 'borrowers' => $borrower,
222 'biblio' => $biblionumber,
223 'items' => $checkitem,
227 my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
229 C4::Letters::EnqueueLetter(
231 borrowernumber => $borrowernumber,
232 message_transport_type => 'email',
233 from_address => $admin_email_address,
234 to_address => $admin_email_address,
245 $res = GetReserve( $reserve_id );
247 Return the current reserve.
252 my ($reserve_id) = @_;
254 my $dbh = C4::Context->dbh;
255 my $query = "SELECT * FROM reserves WHERE reserve_id = ?";
256 my $sth = $dbh->prepare( $query );
257 $sth->execute( $reserve_id );
258 return $sth->fetchrow_hashref();
261 =head2 GetReservesFromBiblionumber
263 my $reserves = GetReservesFromBiblionumber({
264 biblionumber => $biblionumber,
265 [ itemnumber => $itemnumber, ]
269 This function gets the list of reservations for one C<$biblionumber>,
270 returning an arrayref pointing to the reserves for C<$biblionumber>.
272 By default, only reserves whose start date falls before the current
273 time are returned. To return all reserves, including future ones,
274 the C<all_dates> parameter can be included and set to a true value.
276 If the C<itemnumber> parameter is supplied, reserves must be targeted
277 to that item or not targeted to any item at all; otherwise, they
278 are excluded from the list.
282 sub GetReservesFromBiblionumber {
284 my $biblionumber = $params->{biblionumber} or return [];
285 my $itemnumber = $params->{itemnumber};
286 my $all_dates = $params->{all_dates} // 0;
287 my $dbh = C4::Context->dbh;
289 # Find the desired items in the reserves
294 timestamp AS rtimestamp,
307 WHERE biblionumber = ? ";
308 push( @params, $biblionumber );
309 unless ( $all_dates ) {
310 $query .= " AND reservedate <= CAST(NOW() AS DATE) ";
313 $query .= " AND ( itemnumber IS NULL OR itemnumber = ? )";
314 push( @params, $itemnumber );
316 $query .= "ORDER BY priority";
317 my $sth = $dbh->prepare($query);
318 $sth->execute( @params );
320 while ( my $data = $sth->fetchrow_hashref ) {
321 push @results, $data;
326 =head2 GetReservesFromItemnumber
328 ( $reservedate, $borrowernumber, $branchcode, $reserve_id, $waitingdate ) = GetReservesFromItemnumber($itemnumber);
330 Get the first reserve for a specific item number (based on priority). Returns the abovementioned values for that reserve.
332 The routine does not look at future reserves (read: item level holds), but DOES include future waits (a confirmed future hold).
336 sub GetReservesFromItemnumber {
337 my ($itemnumber) = @_;
339 my $schema = Koha::Database->new()->schema();
341 my $r = $schema->resultset('Reserve')->search(
343 itemnumber => $itemnumber,
346 reservedate => \'<= CAST( NOW() AS DATE )',
347 waitingdate => { '!=', undef }
351 order_by => 'priority',
359 $r->get_column('borrowernumber'),
360 $r->get_column('branchcode'),
366 =head2 GetReservesFromBorrowernumber
368 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
374 sub GetReservesFromBorrowernumber {
375 my ( $borrowernumber, $status ) = @_;
376 my $dbh = C4::Context->dbh;
379 $sth = $dbh->prepare("
382 WHERE borrowernumber=?
386 $sth->execute($borrowernumber,$status);
388 $sth = $dbh->prepare("
391 WHERE borrowernumber=?
394 $sth->execute($borrowernumber);
396 my $data = $sth->fetchall_arrayref({});
399 #-------------------------------------------------------------------------------------
400 =head2 CanBookBeReserved
402 $canReserve = &CanBookBeReserved($borrowernumber, $biblionumber)
403 if ($canReserve eq 'OK') { #We can reserve this Item! }
405 See CanItemBeReserved() for possible return values.
409 sub CanBookBeReserved{
410 my ($borrowernumber, $biblionumber) = @_;
412 my $items = GetItemnumbersForBiblio($biblionumber);
413 #get items linked via host records
414 my @hostitems = get_hostitemnumbers_of($biblionumber);
416 push (@$items,@hostitems);
420 foreach my $item (@$items) {
421 $canReserve = CanItemBeReserved( $borrowernumber, $item );
422 return 'OK' if $canReserve eq 'OK';
427 =head2 CanItemBeReserved
429 $canReserve = &CanItemBeReserved($borrowernumber, $itemnumber)
430 if ($canReserve eq 'OK') { #We can reserve this Item! }
432 @RETURNS OK, if the Item can be reserved.
433 ageRestricted, if the Item is age restricted for this borrower.
434 damaged, if the Item is damaged.
435 cannotReserveFromOtherBranches, if syspref 'canreservefromotherbranches' is OK.
436 tooManyReserves, if the borrower has exceeded his maximum reserve amount.
437 notReservable, if holds on this item are not allowed
441 sub CanItemBeReserved{
442 my ($borrowernumber, $itemnumber) = @_;
444 my $dbh = C4::Context->dbh;
445 my $ruleitemtype; # itemtype of the matching issuing rule
446 my $allowedreserves = 0;
448 # we retrieve borrowers and items informations #
449 # item->{itype} will come for biblioitems if necessery
450 my $item = GetItem($itemnumber);
451 my $biblioData = C4::Biblio::GetBiblioData( $item->{biblionumber} );
452 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
454 # If an item is damaged and we don't allow holds on damaged items, we can stop right here
455 return 'damaged' if ( $item->{damaged} && !C4::Context->preference('AllowHoldsOnDamagedItems') );
457 #Check for the age restriction
458 my ($ageRestriction, $daysToAgeRestriction) = C4::Circulation::GetAgeRestriction( $biblioData->{agerestriction}, $borrower );
459 return 'ageRestricted' if $daysToAgeRestriction && $daysToAgeRestriction > 0;
461 my $controlbranch = C4::Context->preference('ReservesControlBranch');
462 my $itemtypefield = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
464 # we retrieve user rights on this itemtype and branchcode
465 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
467 WHERE (categorycode in (?,'*') )
468 AND (itemtype IN (?,'*'))
469 AND (branchcode IN (?,'*'))
476 my $querycount ="SELECT
479 LEFT JOIN items USING (itemnumber)
480 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
481 LEFT JOIN borrowers USING (borrowernumber)
482 WHERE borrowernumber = ?
487 my $branchfield = "reserves.branchcode";
489 if( $controlbranch eq "ItemHomeLibrary" ){
490 $branchfield = "items.homebranch";
491 $branchcode = $item->{homebranch};
492 }elsif( $controlbranch eq "PatronLibrary" ){
493 $branchfield = "borrowers.branchcode";
494 $branchcode = $borrower->{branchcode};
498 $sth->execute($borrower->{'categorycode'}, $item->{'itype'}, $branchcode);
499 if(my $rights = $sth->fetchrow_hashref()){
500 $ruleitemtype = $rights->{itemtype};
501 $allowedreserves = $rights->{reservesallowed};
508 $querycount .= "AND $branchfield = ?";
510 $querycount .= " AND $itemtypefield = ?" if ($ruleitemtype ne "*");
511 my $sthcount = $dbh->prepare($querycount);
513 if($ruleitemtype eq "*"){
514 $sthcount->execute($borrowernumber, $branchcode);
516 $sthcount->execute($borrowernumber, $branchcode, $ruleitemtype);
519 my $reservecount = "0";
520 if(my $rowcount = $sthcount->fetchrow_hashref()){
521 $reservecount = $rowcount->{count};
523 # we check if it's ok or not
524 if( $reservecount >= $allowedreserves ){
525 return 'tooManyReserves';
528 my $circ_control_branch = C4::Circulation::_GetCircControlBranch($item,
530 my $branchitemrule = C4::Circulation::GetBranchItemRule($circ_control_branch,
533 if ( $branchitemrule->{holdallowed} == 0 ) {
534 return 'notReservable';
537 if ( $branchitemrule->{holdallowed} == 1
538 && $borrower->{branchcode} ne $item->{homebranch} )
540 return 'cannotReserveFromOtherBranches';
543 # If reservecount is ok, we check item branch if IndependentBranches is ON
544 # and canreservefromotherbranches is OFF
545 if ( C4::Context->preference('IndependentBranches')
546 and !C4::Context->preference('canreservefromotherbranches') )
548 my $itembranch = $item->{homebranch};
549 if ($itembranch ne $borrower->{branchcode}) {
550 return 'cannotReserveFromOtherBranches';
557 =head2 CanReserveBeCanceledFromOpac
559 $number = CanReserveBeCanceledFromOpac($reserve_id, $borrowernumber);
561 returns 1 if reserve can be cancelled by user from OPAC.
562 First check if reserve belongs to user, next checks if reserve is not in
563 transfer or waiting status
567 sub CanReserveBeCanceledFromOpac {
568 my ($reserve_id, $borrowernumber) = @_;
570 return unless $reserve_id and $borrowernumber;
571 my $reserve = GetReserve($reserve_id);
573 return 0 unless $reserve->{borrowernumber} == $borrowernumber;
574 return 0 if ( $reserve->{found} eq 'W' ) or ( $reserve->{found} eq 'T' );
580 #--------------------------------------------------------------------------------
581 =head2 GetReserveCount
583 $number = &GetReserveCount($borrowernumber);
585 this function returns the number of reservation for a borrower given on input arg.
589 sub GetReserveCount {
590 my ($borrowernumber) = @_;
592 my $dbh = C4::Context->dbh;
595 SELECT COUNT(*) AS counter
597 WHERE borrowernumber = ?
599 my $sth = $dbh->prepare($query);
600 $sth->execute($borrowernumber);
601 my $row = $sth->fetchrow_hashref;
602 return $row->{counter};
605 =head2 GetOtherReserves
607 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
609 Check queued list of this document and check if this document must be transferred
613 sub GetOtherReserves {
614 my ($itemnumber) = @_;
617 my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
618 if ($checkreserves) {
619 my $iteminfo = GetItem($itemnumber);
620 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
621 $messages->{'transfert'} = $checkreserves->{'branchcode'};
622 #minus priorities of others reservs
623 ModReserveMinusPriority(
625 $checkreserves->{'reserve_id'},
628 #launch the subroutine dotransfer
629 C4::Items::ModItemTransfer(
631 $iteminfo->{'holdingbranch'},
632 $checkreserves->{'branchcode'}
637 #step 2b : case of a reservation on the same branch, set the waiting status
639 $messages->{'waiting'} = 1;
640 ModReserveMinusPriority(
642 $checkreserves->{'reserve_id'},
644 ModReserveStatus($itemnumber,'W');
647 $nextreservinfo = $checkreserves->{'borrowernumber'};
650 return ( $messages, $nextreservinfo );
655 $fee = GetReserveFee($borrowernumber,$biblionumber,$biblionumber);
657 Calculate the fee for a reserve
662 my ($borrowernumber, $biblionumber, $bibitems ) = @_;
665 my $dbh = C4::Context->dbh;
667 SELECT * FROM borrowers
668 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
669 WHERE borrowernumber = ?
671 my $sth = $dbh->prepare($query);
672 $sth->execute($borrowernumber);
673 my $data = $sth->fetchrow_hashref;
674 my $fee = $data->{'reservefee'};
675 my $cntitems = @- > $bibitems;
679 # check for items on issue
680 # first find biblioitem records
682 my $sth1 = $dbh->prepare(
683 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
684 WHERE (biblio.biblionumber = ?)"
686 $sth1->execute($biblionumber);
687 while ( my $data1 = $sth1->fetchrow_hashref ) {
690 while ( $x < $cntitems ) {
691 if ( @$bibitems->{'biblioitemnumber'} ==
692 $data->{'biblioitemnumber'} )
700 push @biblioitems, $data1;
703 my $cntitemsfound = @biblioitems;
707 while ( $x < $cntitemsfound ) {
708 my $bitdata = $biblioitems[$x];
709 my $sth2 = $dbh->prepare(
711 WHERE biblioitemnumber = ?"
713 $sth2->execute( $bitdata->{'biblioitemnumber'} );
714 while ( my $itdata = $sth2->fetchrow_hashref ) {
715 my $sth3 = $dbh->prepare(
716 "SELECT * FROM issues
717 WHERE itemnumber = ?"
719 $sth3->execute( $itdata->{'itemnumber'} );
720 if ( my $isdata = $sth3->fetchrow_hashref ) {
728 if ( $allissued == 0 ) {
730 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
731 $rsth->execute($biblionumber);
732 if ( my $rdata = $rsth->fetchrow_hashref ) {
742 =head2 GetReservesToBranch
744 @transreserv = GetReservesToBranch( $frombranch );
746 Get reserve list for a given branch
750 sub GetReservesToBranch {
751 my ( $frombranch ) = @_;
752 my $dbh = C4::Context->dbh;
753 my $sth = $dbh->prepare(
754 "SELECT reserve_id,borrowernumber,reservedate,itemnumber,timestamp
759 $sth->execute( $frombranch );
762 while ( my $data = $sth->fetchrow_hashref ) {
763 $transreserv[$i] = $data;
766 return (@transreserv);
769 =head2 GetReservesForBranch
771 @transreserv = GetReservesForBranch($frombranch);
775 sub GetReservesForBranch {
776 my ($frombranch) = @_;
777 my $dbh = C4::Context->dbh;
780 SELECT reserve_id,borrowernumber,reservedate,itemnumber,waitingdate
785 $query .= " AND branchcode=? " if ( $frombranch );
786 $query .= "ORDER BY waitingdate" ;
788 my $sth = $dbh->prepare($query);
790 $sth->execute($frombranch);
797 while ( my $data = $sth->fetchrow_hashref ) {
798 $transreserv[$i] = $data;
801 return (@transreserv);
804 =head2 GetReserveStatus
806 $reservestatus = GetReserveStatus($itemnumber);
808 Takes an itemnumber and returns the status of the reserve placed on it.
809 If several reserves exist, the reserve with the lower priority is given.
813 ## FIXME: I don't think this does what it thinks it does.
814 ## It only ever checks the first reserve result, even though
815 ## multiple reserves for that bib can have the itemnumber set
816 ## the sub is only used once in the codebase.
817 sub GetReserveStatus {
818 my ($itemnumber) = @_;
820 my $dbh = C4::Context->dbh;
822 my ($sth, $found, $priority);
824 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE itemnumber = ? order by priority LIMIT 1");
825 $sth->execute($itemnumber);
826 ($found, $priority) = $sth->fetchrow_array;
830 return 'Waiting' if $found eq 'W' and $priority == 0;
831 return 'Finished' if $found eq 'F';
834 return 'Reserved' if $priority > 0;
836 return ''; # empty string here will remove need for checking undef, or less log lines
841 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
842 ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);
843 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber,undef,$lookahead);
845 Find a book in the reserves.
847 C<$itemnumber> is the book's item number.
848 C<$lookahead> is the number of days to look in advance for future reserves.
850 As I understand it, C<&CheckReserves> looks for the given item in the
851 reserves. If it is found, that's a match, and C<$status> is set to
854 Otherwise, it finds the most important item in the reserves with the
855 same biblio number as this book (I'm not clear on this) and returns it
856 with C<$status> set to C<Reserved>.
858 C<&CheckReserves> returns a two-element list:
860 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
862 C<$reserve> is the reserve item that matched. It is a
863 reference-to-hash whose keys are mostly the fields of the reserves
864 table in the Koha database.
869 my ( $item, $barcode, $lookahead_days, $ignore_borrowers) = @_;
870 my $dbh = C4::Context->dbh;
873 if (C4::Context->preference('item-level_itypes')){
875 SELECT items.biblionumber,
876 items.biblioitemnumber,
877 itemtypes.notforloan,
878 items.notforloan AS itemnotforloan,
884 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
885 LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype
890 SELECT items.biblionumber,
891 items.biblioitemnumber,
892 itemtypes.notforloan,
893 items.notforloan AS itemnotforloan,
899 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
900 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
905 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
906 $sth->execute($item);
909 $sth = $dbh->prepare("$select WHERE barcode = ?");
910 $sth->execute($barcode);
912 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
913 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber, $damaged, $item_homebranch, $item_holdingbranch ) = $sth->fetchrow_array;
915 return if ( $damaged && !C4::Context->preference('AllowHoldsOnDamagedItems') );
917 return unless $itemnumber; # bail if we got nothing.
919 # if item is not for loan it cannot be reserved either.....
920 # except where items.notforloan < 0 : This indicates the item is holdable.
921 return if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
923 # Find this item in the reserves
924 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber, $lookahead_days, $ignore_borrowers);
926 # $priority and $highest are used to find the most important item
927 # in the list returned by &_Findgroupreserve. (The lower $priority,
928 # the more important the item.)
929 # $highest is the most important item we've seen so far.
931 if (scalar @reserves) {
932 my $LocalHoldsPriority = C4::Context->preference('LocalHoldsPriority');
933 my $LocalHoldsPriorityPatronControl = C4::Context->preference('LocalHoldsPriorityPatronControl');
934 my $LocalHoldsPriorityItemControl = C4::Context->preference('LocalHoldsPriorityItemControl');
936 my $priority = 10000000;
937 foreach my $res (@reserves) {
938 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
939 return ( "Waiting", $res, \@reserves ); # Found it
943 my $local_hold_match;
945 if ($LocalHoldsPriority) {
946 $borrowerinfo = C4::Members::GetMember( borrowernumber => $res->{'borrowernumber'} );
947 $iteminfo = C4::Items::GetItem($itemnumber);
949 my $local_holds_priority_item_branchcode =
950 $iteminfo->{$LocalHoldsPriorityItemControl};
951 my $local_holds_priority_patron_branchcode =
952 ( $LocalHoldsPriorityPatronControl eq 'PickupLibrary' )
954 : ( $LocalHoldsPriorityPatronControl eq 'HomeLibrary' )
955 ? $borrowerinfo->{branchcode}
958 $local_holds_priority_item_branchcode eq
959 $local_holds_priority_patron_branchcode;
962 # See if this item is more important than what we've got so far
963 if ( ( $res->{'priority'} && $res->{'priority'} < $priority ) || $local_hold_match ) {
964 $borrowerinfo ||= C4::Members::GetMember( borrowernumber => $res->{'borrowernumber'} );
965 $iteminfo ||= C4::Items::GetItem($itemnumber);
966 my $branch = GetReservesControlBranch( $iteminfo, $borrowerinfo );
967 my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
968 next if ($branchitemrule->{'holdallowed'} == 0);
969 next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
970 $priority = $res->{'priority'};
972 last if $local_hold_match;
978 # If we get this far, then no exact match was found.
979 # We return the most important (i.e. next) reservation.
981 $highest->{'itemnumber'} = $item;
982 return ( "Reserved", $highest, \@reserves );
988 =head2 CancelExpiredReserves
990 CancelExpiredReserves();
992 Cancels all reserves with an expiration date from before today.
996 sub CancelExpiredReserves {
998 # Cancel reserves that have passed their expiration date.
999 my $dbh = C4::Context->dbh;
1000 my $sth = $dbh->prepare( "
1001 SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
1002 AND expirationdate IS NOT NULL
1007 while ( my $res = $sth->fetchrow_hashref() ) {
1008 CancelReserve({ reserve_id => $res->{'reserve_id'} });
1011 # Cancel reserves that have been waiting too long
1012 if ( C4::Context->preference("ExpireReservesMaxPickUpDelay") ) {
1013 my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
1014 my $cancel_on_holidays = C4::Context->preference('ExpireReservesOnHolidays');
1016 my $today = dt_from_string();
1018 my $query = "SELECT * FROM reserves WHERE TO_DAYS( NOW() ) - TO_DAYS( waitingdate ) > ? AND found = 'W' AND priority = 0";
1019 $sth = $dbh->prepare( $query );
1020 $sth->execute( $max_pickup_delay );
1022 while ( my $res = $sth->fetchrow_hashref ) {
1024 unless ( $cancel_on_holidays ) {
1025 my $calendar = Koha::Calendar->new( branchcode => $res->{'branchcode'} );
1026 my $is_holiday = $calendar->is_holiday( $today );
1028 if ( $is_holiday ) {
1034 CancelReserve({ reserve_id => $res->{'reserve_id'}, charge_cancel_fee => 1 });
1041 =head2 AutoUnsuspendReserves
1043 AutoUnsuspendReserves();
1045 Unsuspends all suspended reserves with a suspend_until date from before today.
1049 sub AutoUnsuspendReserves {
1051 my $dbh = C4::Context->dbh;
1053 my $query = "UPDATE reserves SET suspend = 0, suspend_until = NULL WHERE DATE( suspend_until ) < DATE( CURDATE() )";
1054 my $sth = $dbh->prepare( $query );
1059 =head2 CancelReserve
1061 CancelReserve({ reserve_id => $reserve_id, [ biblionumber => $biblionumber, borrowernumber => $borrrowernumber, itemnumber => $itemnumber, ] [ charge_cancel_fee => 1 ] });
1063 Cancels a reserve. If C<charge_cancel_fee> is passed and the C<ExpireReservesMaxPickUpDelayCharge> syspref is set, charge that fee to the patron's account.
1068 my ( $params ) = @_;
1070 my $reserve_id = $params->{'reserve_id'};
1071 # Filter out only the desired keys; this will insert undefined values for elements missing in
1072 # \%params, but GetReserveId filters them out anyway.
1073 $reserve_id = GetReserveId( { biblionumber => $params->{'biblionumber'}, borrowernumber => $params->{'borrowernumber'}, itemnumber => $params->{'itemnumber'} } ) unless ( $reserve_id );
1075 return unless ( $reserve_id );
1077 my $dbh = C4::Context->dbh;
1079 my $reserve = GetReserve( $reserve_id );
1083 SET cancellationdate = now(),
1086 WHERE reserve_id = ?
1088 my $sth = $dbh->prepare($query);
1089 $sth->execute( $reserve_id );
1092 INSERT INTO old_reserves
1093 SELECT * FROM reserves
1094 WHERE reserve_id = ?
1096 $sth = $dbh->prepare($query);
1097 $sth->execute( $reserve_id );
1100 DELETE FROM reserves
1101 WHERE reserve_id = ?
1103 $sth = $dbh->prepare($query);
1104 $sth->execute( $reserve_id );
1106 # now fix the priority on the others....
1107 _FixPriority({ biblionumber => $reserve->{biblionumber} });
1109 # and, if desired, charge a cancel fee
1110 my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
1111 if ( $charge && $params->{'charge_cancel_fee'} ) {
1112 manualinvoice($reserve->{'borrowernumber'}, $reserve->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
1121 ModReserve({ rank => $rank,
1122 reserve_id => $reserve_id,
1123 branchcode => $branchcode
1124 [, itemnumber => $itemnumber ]
1125 [, biblionumber => $biblionumber, $borrowernumber => $borrowernumber ]
1128 Change a hold request's priority or cancel it.
1130 C<$rank> specifies the effect of the change. If C<$rank>
1131 is 'W' or 'n', nothing happens. This corresponds to leaving a
1132 request alone when changing its priority in the holds queue
1135 If C<$rank> is 'del', the hold request is cancelled.
1137 If C<$rank> is an integer greater than zero, the priority of
1138 the request is set to that value. Since priority != 0 means
1139 that the item is not waiting on the hold shelf, setting the
1140 priority to a non-zero value also sets the request's found
1141 status and waiting date to NULL.
1143 The optional C<$itemnumber> parameter is used only when
1144 C<$rank> is a non-zero integer; if supplied, the itemnumber
1145 of the hold request is set accordingly; if omitted, the itemnumber
1148 B<FIXME:> Note that the forgoing can have the effect of causing
1149 item-level hold requests to turn into title-level requests. This
1150 will be fixed once reserves has separate columns for requested
1151 itemnumber and supplying itemnumber.
1156 my ( $params ) = @_;
1158 my $rank = $params->{'rank'};
1159 my $reserve_id = $params->{'reserve_id'};
1160 my $branchcode = $params->{'branchcode'};
1161 my $itemnumber = $params->{'itemnumber'};
1162 my $suspend_until = $params->{'suspend_until'};
1163 my $borrowernumber = $params->{'borrowernumber'};
1164 my $biblionumber = $params->{'biblionumber'};
1166 return if $rank eq "W";
1167 return if $rank eq "n";
1169 return unless ( $reserve_id || ( $borrowernumber && ( $biblionumber || $itemnumber ) ) );
1170 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber, itemnumber => $itemnumber }) unless ( $reserve_id );
1172 my $dbh = C4::Context->dbh;
1173 if ( $rank eq "del" ) {
1174 CancelReserve({ reserve_id => $reserve_id });
1176 elsif ($rank =~ /^\d+/ and $rank > 0) {
1178 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1179 WHERE reserve_id = ?
1181 my $sth = $dbh->prepare($query);
1182 $sth->execute( $rank, $branchcode, $itemnumber, $reserve_id );
1184 if ( defined( $suspend_until ) ) {
1185 if ( $suspend_until ) {
1186 $suspend_until = C4::Dates->new( $suspend_until )->output("iso");
1187 $dbh->do("UPDATE reserves SET suspend = 1, suspend_until = ? WHERE reserve_id = ?", undef, ( $suspend_until, $reserve_id ) );
1189 $dbh->do("UPDATE reserves SET suspend_until = NULL WHERE reserve_id = ?", undef, ( $reserve_id ) );
1193 _FixPriority({ reserve_id => $reserve_id, rank =>$rank });
1197 =head2 ModReserveFill
1199 &ModReserveFill($reserve);
1201 Fill a reserve. If I understand this correctly, this means that the
1202 reserved book has been found and given to the patron who reserved it.
1204 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1205 whose keys are fields from the reserves table in the Koha database.
1209 sub ModReserveFill {
1211 my $dbh = C4::Context->dbh;
1212 # fill in a reserve record....
1213 my $reserve_id = $res->{'reserve_id'};
1214 my $biblionumber = $res->{'biblionumber'};
1215 my $borrowernumber = $res->{'borrowernumber'};
1216 my $resdate = $res->{'reservedate'};
1218 # get the priority on this record....
1220 my $query = "SELECT priority
1222 WHERE biblionumber = ?
1223 AND borrowernumber = ?
1224 AND reservedate = ?";
1225 my $sth = $dbh->prepare($query);
1226 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1227 ($priority) = $sth->fetchrow_array;
1229 # update the database...
1230 $query = "UPDATE reserves
1233 WHERE biblionumber = ?
1235 AND borrowernumber = ?
1237 $sth = $dbh->prepare($query);
1238 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1240 # move to old_reserves
1241 $query = "INSERT INTO old_reserves
1242 SELECT * FROM reserves
1243 WHERE biblionumber = ?
1245 AND borrowernumber = ?
1247 $sth = $dbh->prepare($query);
1248 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1249 $query = "DELETE FROM reserves
1250 WHERE biblionumber = ?
1252 AND borrowernumber = ?
1254 $sth = $dbh->prepare($query);
1255 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1257 # now fix the priority on the others (if the priority wasn't
1258 # already sorted!)....
1259 unless ( $priority == 0 ) {
1260 _FixPriority({ reserve_id => $reserve_id, biblionumber => $biblionumber });
1264 =head2 ModReserveStatus
1266 &ModReserveStatus($itemnumber, $newstatus);
1268 Update the reserve status for the active (priority=0) reserve.
1270 $itemnumber is the itemnumber the reserve is on
1272 $newstatus is the new status.
1276 sub ModReserveStatus {
1278 #first : check if we have a reservation for this item .
1279 my ($itemnumber, $newstatus) = @_;
1280 my $dbh = C4::Context->dbh;
1282 my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1283 my $sth_set = $dbh->prepare($query);
1284 $sth_set->execute( $newstatus, $itemnumber );
1286 if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1287 CartToShelf( $itemnumber );
1291 =head2 ModReserveAffect
1293 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1295 This function affect an item and a status for a given reserve
1296 The itemnumber parameter is used to find the biblionumber.
1297 with the biblionumber & the borrowernumber, we can affect the itemnumber
1298 to the correct reserve.
1300 if $transferToDo is not set, then the status is set to "Waiting" as well.
1301 otherwise, a transfer is on the way, and the end of the transfer will
1302 take care of the waiting status
1306 sub ModReserveAffect {
1307 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1308 my $dbh = C4::Context->dbh;
1310 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1311 # attached to $itemnumber
1312 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1313 $sth->execute($itemnumber);
1314 my ($biblionumber) = $sth->fetchrow;
1316 # get request - need to find out if item is already
1317 # waiting in order to not send duplicate hold filled notifications
1318 my $reserve_id = GetReserveId({
1319 borrowernumber => $borrowernumber,
1320 biblionumber => $biblionumber,
1322 return unless defined $reserve_id;
1323 my $request = GetReserveInfo($reserve_id);
1324 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1326 # If we affect a reserve that has to be transferred, don't set to Waiting
1328 if ($transferToDo) {
1334 WHERE borrowernumber = ?
1335 AND biblionumber = ?
1339 # affect the reserve to Waiting as well.
1344 waitingdate = NOW(),
1346 WHERE borrowernumber = ?
1347 AND biblionumber = ?
1350 $sth = $dbh->prepare($query);
1351 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1352 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1353 _FixPriority( { biblionumber => $biblionumber } );
1354 if ( C4::Context->preference("ReturnToShelvingCart") ) {
1355 CartToShelf( $itemnumber );
1361 =head2 ModReserveCancelAll
1363 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1365 function to cancel reserv,check other reserves, and transfer document if it's necessary
1369 sub ModReserveCancelAll {
1372 my ( $itemnumber, $borrowernumber ) = @_;
1374 #step 1 : cancel the reservation
1375 my $CancelReserve = CancelReserve({ itemnumber => $itemnumber, borrowernumber => $borrowernumber });
1377 #step 2 launch the subroutine of the others reserves
1378 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1380 return ( $messages, $nextreservinfo );
1383 =head2 ModReserveMinusPriority
1385 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1387 Reduce the values of queued list
1391 sub ModReserveMinusPriority {
1392 my ( $itemnumber, $reserve_id ) = @_;
1394 #first step update the value of the first person on reserv
1395 my $dbh = C4::Context->dbh;
1398 SET priority = 0 , itemnumber = ?
1399 WHERE reserve_id = ?
1401 my $sth_upd = $dbh->prepare($query);
1402 $sth_upd->execute( $itemnumber, $reserve_id );
1403 # second step update all others reserves
1404 _FixPriority({ reserve_id => $reserve_id, rank => '0' });
1407 =head2 GetReserveInfo
1409 &GetReserveInfo($reserve_id);
1411 Get item and borrower details for a current hold.
1412 Current implementation this query should have a single result.
1416 sub GetReserveInfo {
1417 my ( $reserve_id ) = @_;
1418 my $dbh = C4::Context->dbh;
1423 reserves.borrowernumber,
1424 reserves.biblionumber,
1425 reserves.branchcode,
1426 reserves.waitingdate,
1442 items.holdingbranch,
1443 items.itemcallnumber,
1449 LEFT JOIN items USING(itemnumber)
1450 LEFT JOIN borrowers USING(borrowernumber)
1451 LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1452 WHERE reserves.reserve_id = ?";
1453 my $sth = $dbh->prepare($strsth);
1454 $sth->execute($reserve_id);
1456 my $data = $sth->fetchrow_hashref;
1460 =head2 IsAvailableForItemLevelRequest
1462 my $is_available = IsAvailableForItemLevelRequest($item_record,$borrower_record);
1464 Checks whether a given item record is available for an
1465 item-level hold request. An item is available if
1467 * it is not lost AND
1468 * it is not damaged AND
1469 * it is not withdrawn AND
1470 * does not have a not for loan value > 0
1472 Need to check the issuingrules onshelfholds column,
1473 if this is set items on the shelf can be placed on hold
1475 Note that IsAvailableForItemLevelRequest() does not
1476 check if the staff operator is authorized to place
1477 a request on the item - in particular,
1478 this routine does not check IndependentBranches
1479 and canreservefromotherbranches.
1483 sub IsAvailableForItemLevelRequest {
1485 my $borrower = shift;
1487 my $dbh = C4::Context->dbh;
1488 # must check the notforloan setting of the itemtype
1489 # FIXME - a lot of places in the code do this
1490 # or something similar - need to be
1492 my $itype = _get_itype($item);
1493 my $notforloan_per_itemtype
1494 = $dbh->selectrow_array("SELECT notforloan FROM itemtypes WHERE itemtype = ?",
1498 $notforloan_per_itemtype ||
1499 $item->{itemlost} ||
1500 $item->{notforloan} > 0 ||
1501 $item->{withdrawn} ||
1502 ($item->{damaged} && !C4::Context->preference('AllowHoldsOnDamagedItems'));
1505 return 1 if _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1507 return $item->{onloan} || GetReserveStatus($item->{itemnumber}) eq "Waiting";
1510 =head2 OnShelfHoldsAllowed
1512 OnShelfHoldsAllowed($itemtype,$borrowercategory,$branchcode);
1514 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see if onshelf
1515 holds are allowed, returns true if so.
1519 sub OnShelfHoldsAllowed {
1520 my ($item, $borrower) = @_;
1522 my $itype = _get_itype($item);
1523 return _OnShelfHoldsAllowed($itype,$borrower->{categorycode},$item->{holdingbranch});
1530 if (C4::Context->preference('item-level_itypes')) {
1531 # We can't trust GetItem to honour the syspref, so safest to do it ourselves
1532 # When GetItem is fixed, we can remove this
1533 $itype = $item->{itype};
1536 # XXX This is a bit dodgy. It relies on biblio itemtype column having different name.
1537 # So if we already have a biblioitems join when calling this function,
1538 # we don't need to access the database again
1539 $itype = $item->{itemtype};
1542 my $dbh = C4::Context->dbh;
1543 my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
1544 my $sth = $dbh->prepare($query);
1545 $sth->execute($item->{biblioitemnumber});
1546 if (my $data = $sth->fetchrow_hashref()){
1547 $itype = $data->{itemtype};
1553 sub _OnShelfHoldsAllowed {
1554 my ($itype,$borrowercategory,$branchcode) = @_;
1556 my $rule = C4::Circulation::GetIssuingRule($borrowercategory, $itype, $branchcode);
1557 return $rule->{onshelfholds};
1560 =head2 AlterPriority
1562 AlterPriority( $where, $reserve_id );
1564 This function changes a reserve's priority up, down, to the top, or to the bottom.
1565 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1570 my ( $where, $reserve_id ) = @_;
1572 my $dbh = C4::Context->dbh;
1574 my $reserve = GetReserve( $reserve_id );
1576 if ( $reserve->{cancellationdate} ) {
1577 warn "I cannot alter the priority for reserve_id $reserve_id, the reserve has been cancelled (".$reserve->{cancellationdate}.')';
1581 if ( $where eq 'up' || $where eq 'down' ) {
1583 my $priority = $reserve->{'priority'};
1584 $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1585 _FixPriority({ reserve_id => $reserve_id, rank => $priority })
1587 } elsif ( $where eq 'top' ) {
1589 _FixPriority({ reserve_id => $reserve_id, rank => '1' })
1591 } elsif ( $where eq 'bottom' ) {
1593 _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1598 =head2 ToggleLowestPriority
1600 ToggleLowestPriority( $borrowernumber, $biblionumber );
1602 This function sets the lowestPriority field to true if is false, and false if it is true.
1606 sub ToggleLowestPriority {
1607 my ( $reserve_id ) = @_;
1609 my $dbh = C4::Context->dbh;
1611 my $sth = $dbh->prepare( "UPDATE reserves SET lowestPriority = NOT lowestPriority WHERE reserve_id = ?");
1612 $sth->execute( $reserve_id );
1614 _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1617 =head2 ToggleSuspend
1619 ToggleSuspend( $reserve_id );
1621 This function sets the suspend field to true if is false, and false if it is true.
1622 If the reserve is currently suspended with a suspend_until date, that date will
1623 be cleared when it is unsuspended.
1628 my ( $reserve_id, $suspend_until ) = @_;
1630 $suspend_until = output_pref(
1632 dt => dt_from_string($suspend_until),
1633 dateformat => 'iso',
1636 ) if ($suspend_until);
1638 my $do_until = ( $suspend_until ) ? '?' : 'NULL';
1640 my $dbh = C4::Context->dbh;
1642 my $sth = $dbh->prepare(
1643 "UPDATE reserves SET suspend = NOT suspend,
1644 suspend_until = CASE WHEN suspend = 0 THEN NULL ELSE $do_until END
1645 WHERE reserve_id = ?
1649 push( @params, $suspend_until ) if ( $suspend_until );
1650 push( @params, $reserve_id );
1652 $sth->execute( @params );
1658 borrowernumber => $borrowernumber,
1659 [ biblionumber => $biblionumber, ]
1660 [ suspend_until => $suspend_until, ]
1661 [ suspend => $suspend ]
1664 This function accepts a set of hash keys as its parameters.
1665 It requires either borrowernumber or biblionumber, or both.
1667 suspend_until is wholly optional.
1674 my $borrowernumber = $params{'borrowernumber'} || undef;
1675 my $biblionumber = $params{'biblionumber'} || undef;
1676 my $suspend_until = $params{'suspend_until'} || undef;
1677 my $suspend = defined( $params{'suspend'} ) ? $params{'suspend'} : 1;
1679 $suspend_until = C4::Dates->new( $suspend_until )->output("iso") if ( defined( $suspend_until ) );
1681 return unless ( $borrowernumber || $biblionumber );
1683 my ( $query, $sth, $dbh, @query_params );
1685 $query = "UPDATE reserves SET suspend = ? ";
1686 push( @query_params, $suspend );
1688 $query .= ", suspend_until = NULL ";
1689 } elsif ( $suspend_until ) {
1690 $query .= ", suspend_until = ? ";
1691 push( @query_params, $suspend_until );
1693 $query .= " WHERE ";
1694 if ( $borrowernumber ) {
1695 $query .= " borrowernumber = ? ";
1696 push( @query_params, $borrowernumber );
1698 $query .= " AND " if ( $borrowernumber && $biblionumber );
1699 if ( $biblionumber ) {
1700 $query .= " biblionumber = ? ";
1701 push( @query_params, $biblionumber );
1703 $query .= " AND found IS NULL ";
1705 $dbh = C4::Context->dbh;
1706 $sth = $dbh->prepare( $query );
1707 $sth->execute( @query_params );
1714 reserve_id => $reserve_id,
1716 [ignoreSetLowestRank => $ignoreSetLowestRank]
1721 _FixPriority({ biblionumber => $biblionumber});
1723 This routine adjusts the priority of a hold request and holds
1726 In the first form, where a reserve_id is passed, the priority of the
1727 hold is set to supplied rank, and other holds for that bib are adjusted
1728 accordingly. If the rank is "del", the hold is cancelled. If no rank
1729 is supplied, all of the holds on that bib have their priority adjusted
1730 as if the second form had been used.
1732 In the second form, where a biblionumber is passed, the holds on that
1733 bib (that are not captured) are sorted in order of increasing priority,
1734 then have reserves.priority set so that the first non-captured hold
1735 has its priority set to 1, the second non-captured hold has its priority
1736 set to 2, and so forth.
1738 In both cases, holds that have the lowestPriority flag on are have their
1739 priority adjusted to ensure that they remain at the end of the line.
1741 Note that the ignoreSetLowestRank parameter is meant to be used only
1742 when _FixPriority calls itself.
1747 my ( $params ) = @_;
1748 my $reserve_id = $params->{reserve_id};
1749 my $rank = $params->{rank} // '';
1750 my $ignoreSetLowestRank = $params->{ignoreSetLowestRank};
1751 my $biblionumber = $params->{biblionumber};
1753 my $dbh = C4::Context->dbh;
1755 unless ( $biblionumber ) {
1756 my $res = GetReserve( $reserve_id );
1757 $biblionumber = $res->{biblionumber};
1760 if ( $rank eq "del" ) {
1761 CancelReserve({ reserve_id => $reserve_id });
1763 elsif ( $rank eq "W" || $rank eq "0" ) {
1765 # make sure priority for waiting or in-transit items is 0
1769 WHERE reserve_id = ?
1770 AND found IN ('W', 'T')
1772 my $sth = $dbh->prepare($query);
1773 $sth->execute( $reserve_id );
1779 SELECT reserve_id, borrowernumber, reservedate
1781 WHERE biblionumber = ?
1782 AND ((found <> 'W' AND found <> 'T') OR found IS NULL)
1783 ORDER BY priority ASC
1785 my $sth = $dbh->prepare($query);
1786 $sth->execute( $biblionumber );
1787 while ( my $line = $sth->fetchrow_hashref ) {
1788 push( @priority, $line );
1791 # To find the matching index
1793 my $key = -1; # to allow for 0 to be a valid result
1794 for ( $i = 0 ; $i < @priority ; $i++ ) {
1795 if ( $reserve_id == $priority[$i]->{'reserve_id'} ) {
1796 $key = $i; # save the index
1801 # if index exists in array then move it to new position
1802 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1803 my $new_rank = $rank -
1804 1; # $new_rank is what you want the new index to be in the array
1805 my $moving_item = splice( @priority, $key, 1 );
1806 splice( @priority, $new_rank, 0, $moving_item );
1809 # now fix the priority on those that are left....
1813 WHERE reserve_id = ?
1815 $sth = $dbh->prepare($query);
1816 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1819 $priority[$j]->{'reserve_id'}
1823 $sth = $dbh->prepare( "SELECT reserve_id FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1826 unless ( $ignoreSetLowestRank ) {
1827 while ( my $res = $sth->fetchrow_hashref() ) {
1829 reserve_id => $res->{'reserve_id'},
1831 ignoreSetLowestRank => 1
1837 =head2 _Findgroupreserve
1839 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber, $lookahead, $ignore_borrowers);
1841 Looks for a holds-queue based item-specific match first, then for a holds-queue title-level match, returning the
1842 first match found. If neither, then we look for non-holds-queue based holds.
1843 Lookahead is the number of days to look in advance.
1845 C<&_Findgroupreserve> returns :
1846 C<@results> is an array of references-to-hash whose keys are mostly
1847 fields from the reserves table of the Koha database, plus
1848 C<biblioitemnumber>.
1852 sub _Findgroupreserve {
1853 my ( $bibitem, $biblio, $itemnumber, $lookahead, $ignore_borrowers) = @_;
1854 my $dbh = C4::Context->dbh;
1856 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1857 # check for exact targeted match
1858 my $item_level_target_query = qq{
1859 SELECT reserves.biblionumber AS biblionumber,
1860 reserves.borrowernumber AS borrowernumber,
1861 reserves.reservedate AS reservedate,
1862 reserves.branchcode AS branchcode,
1863 reserves.cancellationdate AS cancellationdate,
1864 reserves.found AS found,
1865 reserves.reservenotes AS reservenotes,
1866 reserves.priority AS priority,
1867 reserves.timestamp AS timestamp,
1868 biblioitems.biblioitemnumber AS biblioitemnumber,
1869 reserves.itemnumber AS itemnumber,
1870 reserves.reserve_id AS reserve_id
1872 JOIN biblioitems USING (biblionumber)
1873 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1876 AND item_level_request = 1
1878 AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1882 my $sth = $dbh->prepare($item_level_target_query);
1883 $sth->execute($itemnumber, $lookahead||0);
1885 if ( my $data = $sth->fetchrow_hashref ) {
1886 push( @results, $data )
1887 unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1889 return @results if @results;
1891 # check for title-level targeted match
1892 my $title_level_target_query = qq{
1893 SELECT reserves.biblionumber AS biblionumber,
1894 reserves.borrowernumber AS borrowernumber,
1895 reserves.reservedate AS reservedate,
1896 reserves.branchcode AS branchcode,
1897 reserves.cancellationdate AS cancellationdate,
1898 reserves.found AS found,
1899 reserves.reservenotes AS reservenotes,
1900 reserves.priority AS priority,
1901 reserves.timestamp AS timestamp,
1902 biblioitems.biblioitemnumber AS biblioitemnumber,
1903 reserves.itemnumber AS itemnumber,
1904 reserves.reserve_id AS reserve_id
1906 JOIN biblioitems USING (biblionumber)
1907 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1910 AND item_level_request = 0
1911 AND hold_fill_targets.itemnumber = ?
1912 AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1916 $sth = $dbh->prepare($title_level_target_query);
1917 $sth->execute($itemnumber, $lookahead||0);
1919 if ( my $data = $sth->fetchrow_hashref ) {
1920 push( @results, $data )
1921 unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1923 return @results if @results;
1926 SELECT reserves.biblionumber AS biblionumber,
1927 reserves.borrowernumber AS borrowernumber,
1928 reserves.reservedate AS reservedate,
1929 reserves.waitingdate AS waitingdate,
1930 reserves.branchcode AS branchcode,
1931 reserves.cancellationdate AS cancellationdate,
1932 reserves.found AS found,
1933 reserves.reservenotes AS reservenotes,
1934 reserves.priority AS priority,
1935 reserves.timestamp AS timestamp,
1936 reserves.itemnumber AS itemnumber,
1937 reserves.reserve_id AS reserve_id
1939 WHERE reserves.biblionumber = ?
1940 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1941 AND reserves.reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1945 $sth = $dbh->prepare($query);
1946 $sth->execute( $biblio, $itemnumber, $lookahead||0);
1948 while ( my $data = $sth->fetchrow_hashref ) {
1949 push( @results, $data )
1950 unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ;
1955 =head2 _koha_notify_reserve
1957 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1959 Sends a notification to the patron that their hold has been filled (through
1960 ModReserveAffect, _not_ ModReserveFill)
1964 sub _koha_notify_reserve {
1965 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1967 my $dbh = C4::Context->dbh;
1968 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1970 # Try to get the borrower's email address
1971 my $to_address = C4::Members::GetNoticeEmailAddress($borrowernumber);
1973 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( {
1974 borrowernumber => $borrowernumber,
1975 message_name => 'Hold_Filled'
1978 my $sth = $dbh->prepare("
1981 WHERE borrowernumber = ?
1982 AND biblionumber = ?
1984 $sth->execute( $borrowernumber, $biblionumber );
1985 my $reserve = $sth->fetchrow_hashref;
1986 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1988 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1990 my %letter_params = (
1991 module => 'reserves',
1992 branchcode => $reserve->{branchcode},
1994 'branches' => $branch_details,
1995 'borrowers' => $borrower,
1996 'biblio' => $biblionumber,
1997 'reserves' => $reserve,
1998 'items', $reserve->{'itemnumber'},
2000 substitute => { today => C4::Dates->new()->output() },
2003 my $notification_sent = 0; #Keeping track if a Hold_filled message is sent. If no message can be sent, then default to a print message.
2004 my $send_notification = sub {
2005 my ( $mtt, $letter_code ) = (@_);
2006 return unless defined $letter_code;
2007 $letter_params{letter_code} = $letter_code;
2008 $letter_params{message_transport_type} = $mtt;
2009 my $letter = C4::Letters::GetPreparedLetter ( %letter_params );
2011 warn "Could not find a letter called '$letter_params{'letter_code'}' for $mtt in the 'reserves' module";
2015 C4::Letters::EnqueueLetter( {
2017 borrowernumber => $borrowernumber,
2018 from_address => $admin_email_address,
2019 message_transport_type => $mtt,
2023 while ( my ( $mtt, $letter_code ) = each %{ $messagingprefs->{transports} } ) {
2025 ( $mtt eq 'email' and not $to_address ) # No email address
2026 or ( $mtt eq 'sms' and not $borrower->{smsalertnumber} ) # No SMS number
2027 or ( $mtt eq 'phone' and C4::Context->preference('TalkingTechItivaPhoneNotification') ) # Notice is handled by TalkingTech_itiva_outbound.pl
2030 &$send_notification($mtt, $letter_code);
2031 $notification_sent++;
2033 #Making sure that a print notification is sent if no other transport types can be utilized.
2034 if (! $notification_sent) {
2035 &$send_notification('print', 'HOLD');
2040 =head2 _ShiftPriorityByDateAndPriority
2042 $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
2044 This increments the priority of all reserves after the one
2045 with either the lowest date after C<$reservedate>
2046 or the lowest priority after C<$priority>.
2048 It effectively makes room for a new reserve to be inserted with a certain
2049 priority, which is returned.
2051 This is most useful when the reservedate can be set by the user. It allows
2052 the new reserve to be placed before other reserves that have a later
2053 reservedate. Since priority also is set by the form in reserves/request.pl
2054 the sub accounts for that too.
2058 sub _ShiftPriorityByDateAndPriority {
2059 my ( $biblio, $resdate, $new_priority ) = @_;
2061 my $dbh = C4::Context->dbh;
2062 my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
2063 my $sth = $dbh->prepare( $query );
2064 $sth->execute( $biblio, $resdate, $new_priority );
2065 my $min_priority = $sth->fetchrow;
2066 # if no such matches are found, $new_priority remains as original value
2067 $new_priority = $min_priority if ( $min_priority );
2069 # Shift the priority up by one; works in conjunction with the next SQL statement
2070 $query = "UPDATE reserves
2071 SET priority = priority+1
2072 WHERE biblionumber = ?
2073 AND borrowernumber = ?
2076 my $sth_update = $dbh->prepare( $query );
2078 # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
2079 $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
2080 $sth = $dbh->prepare( $query );
2081 $sth->execute( $new_priority, $biblio );
2082 while ( my $row = $sth->fetchrow_hashref ) {
2083 $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
2086 return $new_priority; # so the caller knows what priority they wind up receiving
2089 =head2 OPACItemHoldsAllowed
2091 OPACItemHoldsAllowed($item_record,$borrower_record);
2093 Checks issuingrules, using the borrowers categorycode, the itemtype, and branchcode to see
2094 if specific item holds are allowed, returns true if so.
2098 sub OPACItemHoldsAllowed {
2099 my ($item,$borrower) = @_;
2101 my $branchcode = $item->{homebranch} or die "No homebranch";
2103 my $dbh = C4::Context->dbh;
2104 if (C4::Context->preference('item-level_itypes')) {
2105 # We can't trust GetItem to honour the syspref, so safest to do it ourselves
2106 # When GetItem is fixed, we can remove this
2107 $itype = $item->{itype};
2110 my $query = "SELECT itemtype FROM biblioitems WHERE biblioitemnumber = ? ";
2111 my $sth = $dbh->prepare($query);
2112 $sth->execute($item->{biblioitemnumber});
2113 if (my $data = $sth->fetchrow_hashref()){
2114 $itype = $data->{itemtype};
2118 my $query = "SELECT opacitemholds,categorycode,itemtype,branchcode FROM issuingrules WHERE
2119 (issuingrules.categorycode = ? OR issuingrules.categorycode = '*')
2121 (issuingrules.itemtype = ? OR issuingrules.itemtype = '*')
2123 (issuingrules.branchcode = ? OR issuingrules.branchcode = '*')
2125 issuingrules.categorycode desc,
2126 issuingrules.itemtype desc,
2127 issuingrules.branchcode desc
2129 my $sth = $dbh->prepare($query);
2130 $sth->execute($borrower->{categorycode},$itype,$branchcode);
2131 my $data = $sth->fetchrow_hashref;
2132 my $opacitemholds = uc substr ($data->{opacitemholds}, 0, 1);
2133 return '' if $opacitemholds eq 'N';
2134 return $opacitemholds;
2139 MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
2141 Use when checking out an item to handle reserves
2142 If $cancelreserve boolean is set to true, it will remove existing reserve
2147 my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
2149 my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
2152 my $biblionumber = $res->{biblionumber};
2153 my $biblioitemnumber = $res->{biblioitemnumber};
2155 if ($res->{borrowernumber} == $borrowernumber) {
2156 ModReserveFill($res);
2160 # The item is reserved by someone else.
2161 # Find this item in the reserves
2164 foreach (@$all_reserves) {
2165 $_->{'borrowernumber'} == $borrowernumber or next;
2166 $_->{'biblionumber'} == $biblionumber or next;
2173 # The item is reserved by the current patron
2174 ModReserveFill($borr_res);
2177 if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
2178 RevertWaitingStatus({ itemnumber => $itemnumber });
2180 elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
2182 biblionumber => $res->{'biblionumber'},
2183 itemnumber => $res->{'itemnumber'},
2184 borrowernumber => $res->{'borrowernumber'}
2192 MergeHolds($dbh,$to_biblio, $from_biblio);
2194 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2199 my ( $dbh, $to_biblio, $from_biblio ) = @_;
2200 my $sth = $dbh->prepare(
2201 "SELECT count(*) as reserve_count FROM reserves WHERE biblionumber = ?"
2203 $sth->execute($from_biblio);
2204 if ( my $data = $sth->fetchrow_hashref() ) {
2206 # holds exist on old record, if not we don't need to do anything
2207 $sth = $dbh->prepare(
2208 "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2209 $sth->execute( $to_biblio, $from_biblio );
2212 # don't reorder those already waiting
2214 $sth = $dbh->prepare(
2215 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2217 my $upd_sth = $dbh->prepare(
2218 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2219 AND reservedate = ? AND (itemnumber = ? or itemnumber is NULL) "
2221 $sth->execute( $to_biblio, 'W', 'T' );
2223 while ( my $reserve = $sth->fetchrow_hashref() ) {
2225 $priority, $to_biblio,
2226 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2227 $reserve->{'itemnumber'}
2234 =head2 RevertWaitingStatus
2236 RevertWaitingStatus({ itemnumber => $itemnumber });
2238 Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2240 Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2241 item level hold, even if it was only a bibliolevel hold to
2242 begin with. This is because we can no longer know if a hold
2243 was item-level or bib-level after a hold has been set to
2248 sub RevertWaitingStatus {
2249 my ( $params ) = @_;
2250 my $itemnumber = $params->{'itemnumber'};
2252 return unless ( $itemnumber );
2254 my $dbh = C4::Context->dbh;
2256 ## Get the waiting reserve we want to revert
2258 SELECT * FROM reserves
2259 WHERE itemnumber = ?
2260 AND found IS NOT NULL
2262 my $sth = $dbh->prepare( $query );
2263 $sth->execute( $itemnumber );
2264 my $reserve = $sth->fetchrow_hashref();
2266 ## Increment the priority of all other non-waiting
2267 ## reserves for this bib record
2271 priority = priority + 1
2277 $sth = $dbh->prepare( $query );
2278 $sth->execute( $reserve->{'biblionumber'} );
2280 ## Fix up the currently waiting reserve
2290 $sth = $dbh->prepare( $query );
2291 $sth->execute( $reserve->{'reserve_id'} );
2292 _FixPriority( { biblionumber => $reserve->{biblionumber} } );
2297 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber [, itemnumber => $itemnumber ] });
2299 Returnes the first reserve id that matches the given criteria
2304 my ( $params ) = @_;
2306 return unless ( ( $params->{'biblionumber'} || $params->{'itemnumber'} ) && $params->{'borrowernumber'} );
2308 my $dbh = C4::Context->dbh();
2310 my $sql = "SELECT reserve_id FROM reserves WHERE ";
2314 foreach my $key ( keys %$params ) {
2315 if ( defined( $params->{$key} ) ) {
2316 push( @limits, "$key = ?" );
2317 push( @params, $params->{$key} );
2321 $sql .= join( " AND ", @limits );
2323 my $sth = $dbh->prepare( $sql );
2324 $sth->execute( @params );
2325 my $row = $sth->fetchrow_hashref();
2327 return $row->{'reserve_id'};
2332 ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2334 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2339 my ($branch, $borrowernumber, $biblionumber) = @_;
2341 # return unless ( C4::Context->boolean_preference('printreserveslips') );
2343 my $reserve_id = GetReserveId({
2344 biblionumber => $biblionumber,
2345 borrowernumber => $borrowernumber
2347 my $reserve = GetReserveInfo($reserve_id) or return;
2349 return C4::Letters::GetPreparedLetter (
2350 module => 'circulation',
2351 letter_code => 'RESERVESLIP',
2352 branchcode => $branch,
2354 'reserves' => $reserve,
2355 'branches' => $reserve->{branchcode},
2356 'borrowers' => $reserve->{borrowernumber},
2357 'biblio' => $reserve->{biblionumber},
2358 'items' => $reserve->{itemnumber},
2363 =head2 GetReservesControlBranch
2365 my $reserves_control_branch = GetReservesControlBranch($item, $borrower);
2367 Return the branchcode to be used to determine which reserves
2368 policy applies to a transaction.
2370 C<$item> is a hashref for an item. Only 'homebranch' is used.
2372 C<$borrower> is a hashref to borrower. Only 'branchcode' is used.
2376 sub GetReservesControlBranch {
2377 my ( $item, $borrower ) = @_;
2379 my $reserves_control = C4::Context->preference('ReservesControlBranch');
2382 ( $reserves_control eq 'ItemHomeLibrary' ) ? $item->{'homebranch'}
2383 : ( $reserves_control eq 'PatronLibrary' ) ? $borrower->{'branchcode'}
2389 =head2 CalculatePriority
2391 my $p = CalculatePriority($biblionumber, $resdate);
2393 Calculate priority for a new reserve on biblionumber, placing it at
2394 the end of the line of all holds whose start date falls before
2395 the current system time and that are neither on the hold shelf
2398 The reserve date parameter is optional; if it is supplied, the
2399 priority is based on the set of holds whose start date falls before
2400 the parameter value.
2402 After calculation of this priority, it is recommended to call
2403 _ShiftPriorityByDateAndPriority. Note that this is currently done in
2408 sub CalculatePriority {
2409 my ( $biblionumber, $resdate ) = @_;
2412 SELECT COUNT(*) FROM reserves
2413 WHERE biblionumber = ?
2415 AND (found IS NULL OR found = '')
2417 #skip found==W or found==T (waiting or transit holds)
2419 $sql.= ' AND ( reservedate <= ? )';
2422 $sql.= ' AND ( reservedate < NOW() )';
2424 my $dbh = C4::Context->dbh();
2425 my @row = $dbh->selectrow_array(
2428 $resdate ? ($biblionumber, $resdate) : ($biblionumber)
2431 return @row ? $row[0]+1 : 1;
2434 =head2 IsItemOnHoldAndFound
2436 my $bool = IsItemFoundHold( $itemnumber );
2438 Returns true if the item is currently on hold
2439 and that hold has a non-null found status ( W, T, etc. )
2443 sub IsItemOnHoldAndFound {
2444 my ($itemnumber) = @_;
2446 my $rs = Koha::Database->new()->schema()->resultset('Reserve');
2448 my $found = $rs->count(
2450 itemnumber => $itemnumber,
2451 found => { '!=' => undef }
2460 Koha Development Team <http://koha-community.org/>