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 under the
11 # terms of the GNU General Public License as published by the Free Software
12 # Foundation; either version 2 of the License, or (at your option) any later
15 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
16 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
17 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License along
20 # with Koha; if not, write to the Free Software Foundation, Inc.,
21 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
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 );
42 use List::MoreUtils qw( firstidx );
44 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
48 C4::Reserves - Koha functions for dealing with reservation.
56 This modules provides somes functions to deal with reservations.
58 Reserves are stored in reserves table.
59 The following columns contains important values :
60 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
61 =0 : then the reserve is being dealed
62 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
63 T(ransit) : the reserve is linked to an item but is in transit to the pickup branch
64 W(aiting) : the reserve is linked to an item, is at the pickup branch, and is waiting on the hold shelf
65 F(inished) : the reserve has been completed, and is done
66 - itemnumber : empty : the reserve is still unaffected to an item
67 filled: the reserve is attached to an item
68 The complete workflow is :
69 ==== 1st use case ====
70 patron request a document, 1st available : P >0, F=NULL, I=NULL
71 a library having it run "transfertodo", and clic on the list
72 if there is no transfer to do, the reserve waiting
73 patron can pick it up P =0, F=W, I=filled
74 if there is a transfer to do, write in branchtransfer P =0, F=T, I=filled
75 The pickup library recieve the book, it check in P =0, F=W, I=filled
76 The patron borrow the book P =0, F=F, I=filled
78 ==== 2nd use case ====
79 patron requests a document, a given item,
80 If pickup is holding branch P =0, F=W, I=filled
81 If transfer needed, write in branchtransfer P =0, F=T, I=filled
82 The pickup library receive the book, it checks it in P =0, F=W, I=filled
83 The patron borrow the book P =0, F=F, I=filled
90 # set the version for version checking
91 $VERSION = 3.07.00.049;
98 &GetReservesFromItemnumber
99 &GetReservesFromBiblionumber
100 &GetReservesFromBorrowernumber
101 &GetReservesForBranch
115 &ModReserveMinusPriority
122 &CancelExpiredReserves
124 &AutoUnsuspendReserves
126 &IsAvailableForItemLevelRequest
129 &ToggleLowestPriority
135 &GetReservesControlBranch
137 @EXPORT_OK = qw( MergeHolds );
142 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
148 $branch, $borrowernumber, $biblionumber,
149 $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
150 $title, $checkitem, $found
153 GetReserveFee($borrowernumber, $biblionumber, $constraint,
155 my $dbh = C4::Context->dbh;
156 my $const = lc substr( $constraint, 0, 1 );
157 $resdate = format_date_in_iso( $resdate ) if ( $resdate );
158 $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
160 $expdate = format_date_in_iso( $expdate );
162 undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
164 if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
165 # Make room in reserves for this before those of a later reserve date
166 $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
170 # If the reserv had the waiting status, we had the value of the resdate
171 if ( $found eq 'W' ) {
172 $waitingdate = $resdate;
176 # updates take place here
178 my $nextacctno = &getnextacctno( $borrowernumber );
180 INSERT INTO accountlines
181 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
183 (?,?,now(),?,?,'Res',?)
185 my $usth = $dbh->prepare($query);
186 $usth->execute( $borrowernumber, $nextacctno, $fee,
187 "Reserve Charge - $title", $fee );
193 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
194 priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
199 my $sth = $dbh->prepare($query);
201 $borrowernumber, $biblionumber, $resdate, $branch,
202 $const, $priority, $notes, $checkitem,
203 $found, $waitingdate, $expdate
206 # Send e-mail to librarian if syspref is active
207 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
208 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
209 my $branch_details = C4::Branch::GetBranchDetail($borrower->{branchcode});
210 if ( my $letter = C4::Letters::GetPreparedLetter (
211 module => 'reserves',
212 letter_code => 'HOLDPLACED',
213 branchcode => $branch,
215 'branches' => $branch_details,
216 'borrowers' => $borrower,
217 'biblio' => $biblionumber,
218 'items' => $checkitem,
222 my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
224 C4::Letters::EnqueueLetter(
226 borrowernumber => $borrowernumber,
227 message_transport_type => 'email',
228 from_address => $admin_email_address,
229 to_address => $admin_email_address,
236 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
238 INSERT INTO reserveconstraints
239 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
243 $sth = $dbh->prepare($query); # keep prepare outside the loop!
244 foreach (@$bibitems) {
245 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
248 return; # FIXME: why not have a useful return value?
253 $res = GetReserve( $reserve_id );
255 Return the current reserve.
260 my ($reserve_id) = @_;
262 my $dbh = C4::Context->dbh;
263 my $query = "SELECT * FROM reserves WHERE reserve_id = ?";
264 my $sth = $dbh->prepare( $query );
265 $sth->execute( $reserve_id );
266 return $sth->fetchrow_hashref();
269 =head2 GetReservesFromBiblionumber
271 my $reserves = GetReservesFromBiblionumber({
272 biblionumber => $biblionumber,
273 [ itemnumber => $itemnumber, ]
277 This function gets the list of reservations for one C<$biblionumber>,
278 returning an arrayref pointing to the reserves for C<$biblionumber>.
280 By default, only reserves whose start date falls before the current
281 time are returned. To return all reserves, including future ones,
282 the C<all_dates> parameter can be included and set to a true value.
284 If the C<itemnumber> parameter is supplied, reserves must be targeted
285 to that item or not targeted to any item at all; otherwise, they
286 are excluded from the list.
290 sub GetReservesFromBiblionumber {
292 my $biblionumber = $params->{biblionumber} or return [];
293 my $itemnumber = $params->{itemnumber};
294 my $all_dates = $params->{all_dates} // 0;
295 my $dbh = C4::Context->dbh;
297 # Find the desired items in the reserves
302 timestamp AS rtimestamp,
316 WHERE biblionumber = ? ";
317 push( @params, $biblionumber );
318 unless ( $all_dates ) {
319 $query .= " AND reservedate <= CAST(NOW() AS DATE) ";
322 $query .= " AND ( itemnumber IS NULL OR itemnumber = ? )";
323 push( @params, $itemnumber );
325 $query .= "ORDER BY priority";
326 my $sth = $dbh->prepare($query);
327 $sth->execute( @params );
330 while ( my $data = $sth->fetchrow_hashref ) {
332 # FIXME - What is this doing? How do constraints work?
333 if ($data->{constrainttype} eq 'o') {
335 SELECT biblioitemnumber
336 FROM reserveconstraints
337 WHERE biblionumber = ?
338 AND borrowernumber = ?
341 my $csth = $dbh->prepare($query);
342 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
344 while ( my $bibitemnos = $csth->fetchrow_array ) {
345 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
347 my $count = scalar @bibitemno;
349 # if we have two or more different specific itemtypes
350 # reserved by same person on same day
353 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
354 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
357 # Look up the book we just found.
358 $bdata = GetBiblioItemData( $bibitemno[0] );
360 # Add the results of this latest search to the current
362 # FIXME - An 'each' would probably be more efficient.
363 foreach my $key ( keys %$bdata ) {
364 $data->{$key} = $bdata->{$key};
367 push @results, $data;
372 =head2 GetReservesFromItemnumber
374 ( $reservedate, $borrowernumber, $branchcode, $reserve_id, $waitingdate ) = GetReservesFromItemnumber($itemnumber);
376 Get the first reserve for a specific item number (based on priority). Returns the abovementioned values for that reserve.
378 The routine does not look at future reserves (read: item level holds), but DOES include future waits (a confirmed future hold).
382 sub GetReservesFromItemnumber {
383 my ( $itemnumber ) = @_;
384 my $dbh = C4::Context->dbh;
386 SELECT reservedate,borrowernumber,branchcode,reserve_id,waitingdate
388 WHERE itemnumber=? AND ( reservedate <= CAST(now() AS date) OR
389 waitingdate IS NOT NULL )
392 my $sth_res = $dbh->prepare($query);
393 $sth_res->execute($itemnumber);
394 my ( $reservedate, $borrowernumber,$branchcode, $reserve_id, $wait ) = $sth_res->fetchrow_array;
395 return ( $reservedate, $borrowernumber, $branchcode, $reserve_id, $wait );
398 =head2 GetReservesFromBorrowernumber
400 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
406 sub GetReservesFromBorrowernumber {
407 my ( $borrowernumber, $status ) = @_;
408 my $dbh = C4::Context->dbh;
411 $sth = $dbh->prepare("
414 WHERE borrowernumber=?
418 $sth->execute($borrowernumber,$status);
420 $sth = $dbh->prepare("
423 WHERE borrowernumber=?
426 $sth->execute($borrowernumber);
428 my $data = $sth->fetchall_arrayref({});
431 #-------------------------------------------------------------------------------------
432 =head2 CanBookBeReserved
434 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
438 sub CanBookBeReserved{
439 my ($borrowernumber, $biblionumber) = @_;
441 my $items = GetItemnumbersForBiblio($biblionumber);
442 #get items linked via host records
443 my @hostitems = get_hostitemnumbers_of($biblionumber);
445 push (@$items,@hostitems);
448 foreach my $item (@$items){
449 return 1 if CanItemBeReserved($borrowernumber, $item);
454 =head2 CanItemBeReserved
456 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
458 This function return 1 if an item can be issued by this borrower.
462 sub CanItemBeReserved{
463 my ($borrowernumber, $itemnumber) = @_;
465 my $dbh = C4::Context->dbh;
466 my $allowedreserves = 0;
468 # we retrieve borrowers and items informations #
469 my $item = GetItem($itemnumber);
471 # If an item is damaged and we don't allow holds on damaged items, we can stop right here
472 return 0 if ( $item->{damaged} && !C4::Context->preference('AllowHoldsOnDamagedItems') );
474 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
476 my $controlbranch = C4::Context->preference('ReservesControlBranch');
477 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
479 # we retrieve user rights on this itemtype and branchcode
480 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
482 WHERE (categorycode in (?,'*') )
483 AND (itemtype IN (?,'*'))
484 AND (branchcode IN (?,'*'))
491 my $querycount ="SELECT
494 LEFT JOIN items USING (itemnumber)
495 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
496 LEFT JOIN borrowers USING (borrowernumber)
497 WHERE borrowernumber = ?
501 my $itemtype = $item->{$itype};
502 my $categorycode = $borrower->{categorycode};
504 my $branchfield = "reserves.branchcode";
506 if( $controlbranch eq "ItemHomeLibrary" ){
507 $branchfield = "items.homebranch";
508 $branchcode = $item->{homebranch};
509 }elsif( $controlbranch eq "PatronLibrary" ){
510 $branchfield = "borrowers.branchcode";
511 $branchcode = $borrower->{branchcode};
515 $sth->execute($categorycode, $itemtype, $branchcode);
516 if(my $rights = $sth->fetchrow_hashref()){
517 $itemtype = $rights->{itemtype};
518 $allowedreserves = $rights->{reservesallowed};
525 $querycount .= "AND $branchfield = ?";
527 $querycount .= " AND $itype = ?" if ($itemtype ne "*");
528 my $sthcount = $dbh->prepare($querycount);
530 if($itemtype eq "*"){
531 $sthcount->execute($borrowernumber, $branchcode);
533 $sthcount->execute($borrowernumber, $branchcode, $itemtype);
536 my $reservecount = "0";
537 if(my $rowcount = $sthcount->fetchrow_hashref()){
538 $reservecount = $rowcount->{count};
541 # we check if it's ok or not
542 if( $reservecount >= $allowedreserves ){
546 # If reservecount is ok, we check item branch if IndependentBranches is ON
547 # and canreservefromotherbranches is OFF
548 if ( C4::Context->preference('IndependentBranches')
549 and !C4::Context->preference('canreservefromotherbranches') )
551 my $itembranch = $item->{homebranch};
552 if ($itembranch ne $borrower->{branchcode}) {
559 #--------------------------------------------------------------------------------
560 =head2 GetReserveCount
562 $number = &GetReserveCount($borrowernumber);
564 this function returns the number of reservation for a borrower given on input arg.
568 sub GetReserveCount {
569 my ($borrowernumber) = @_;
571 my $dbh = C4::Context->dbh;
574 SELECT COUNT(*) AS counter
576 WHERE borrowernumber = ?
578 my $sth = $dbh->prepare($query);
579 $sth->execute($borrowernumber);
580 my $row = $sth->fetchrow_hashref;
581 return $row->{counter};
584 =head2 GetOtherReserves
586 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
588 Check queued list of this document and check if this document must be transfered
592 sub GetOtherReserves {
593 my ($itemnumber) = @_;
596 my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
597 if ($checkreserves) {
598 my $iteminfo = GetItem($itemnumber);
599 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
600 $messages->{'transfert'} = $checkreserves->{'branchcode'};
601 #minus priorities of others reservs
602 ModReserveMinusPriority(
604 $checkreserves->{'reserve_id'},
607 #launch the subroutine dotransfer
608 C4::Items::ModItemTransfer(
610 $iteminfo->{'holdingbranch'},
611 $checkreserves->{'branchcode'}
616 #step 2b : case of a reservation on the same branch, set the waiting status
618 $messages->{'waiting'} = 1;
619 ModReserveMinusPriority(
621 $checkreserves->{'reserve_id'},
623 ModReserveStatus($itemnumber,'W');
626 $nextreservinfo = $checkreserves->{'borrowernumber'};
629 return ( $messages, $nextreservinfo );
634 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
636 Calculate the fee for a reserve
641 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
644 my $dbh = C4::Context->dbh;
645 my $const = lc substr( $constraint, 0, 1 );
647 SELECT * FROM borrowers
648 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
649 WHERE borrowernumber = ?
651 my $sth = $dbh->prepare($query);
652 $sth->execute($borrowernumber);
653 my $data = $sth->fetchrow_hashref;
654 my $fee = $data->{'reservefee'};
655 my $cntitems = @- > $bibitems;
659 # check for items on issue
660 # first find biblioitem records
662 my $sth1 = $dbh->prepare(
663 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
664 WHERE (biblio.biblionumber = ?)"
666 $sth1->execute($biblionumber);
667 while ( my $data1 = $sth1->fetchrow_hashref ) {
668 if ( $const eq "a" ) {
669 push @biblioitems, $data1;
674 while ( $x < $cntitems ) {
675 if ( @$bibitems->{'biblioitemnumber'} ==
676 $data->{'biblioitemnumber'} )
682 if ( $const eq 'o' ) {
684 push @biblioitems, $data1;
689 push @biblioitems, $data1;
694 my $cntitemsfound = @biblioitems;
698 while ( $x < $cntitemsfound ) {
699 my $bitdata = $biblioitems[$x];
700 my $sth2 = $dbh->prepare(
702 WHERE biblioitemnumber = ?"
704 $sth2->execute( $bitdata->{'biblioitemnumber'} );
705 while ( my $itdata = $sth2->fetchrow_hashref ) {
706 my $sth3 = $dbh->prepare(
707 "SELECT * FROM issues
708 WHERE itemnumber = ?"
710 $sth3->execute( $itdata->{'itemnumber'} );
711 if ( my $isdata = $sth3->fetchrow_hashref ) {
719 if ( $allissued == 0 ) {
721 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
722 $rsth->execute($biblionumber);
723 if ( my $rdata = $rsth->fetchrow_hashref ) {
733 =head2 GetReservesToBranch
735 @transreserv = GetReservesToBranch( $frombranch );
737 Get reserve list for a given branch
741 sub GetReservesToBranch {
742 my ( $frombranch ) = @_;
743 my $dbh = C4::Context->dbh;
744 my $sth = $dbh->prepare(
745 "SELECT reserve_id,borrowernumber,reservedate,itemnumber,timestamp
750 $sth->execute( $frombranch );
753 while ( my $data = $sth->fetchrow_hashref ) {
754 $transreserv[$i] = $data;
757 return (@transreserv);
760 =head2 GetReservesForBranch
762 @transreserv = GetReservesForBranch($frombranch);
766 sub GetReservesForBranch {
767 my ($frombranch) = @_;
768 my $dbh = C4::Context->dbh;
771 SELECT reserve_id,borrowernumber,reservedate,itemnumber,waitingdate
776 $query .= " AND branchcode=? " if ( $frombranch );
777 $query .= "ORDER BY waitingdate" ;
779 my $sth = $dbh->prepare($query);
781 $sth->execute($frombranch);
788 while ( my $data = $sth->fetchrow_hashref ) {
789 $transreserv[$i] = $data;
792 return (@transreserv);
795 =head2 GetReserveStatus
797 $reservestatus = GetReserveStatus($itemnumber, $biblionumber);
799 Take an itemnumber or a biblionumber and return the status of the reserve places on it.
800 If several reserves exist, the reserve with the lower priority is given.
804 ## FIXME: I don't think this does what it thinks it does.
805 ## It only ever checks the first reserve result, even though
806 ## multiple reserves for that bib can have the itemnumber set
807 ## the sub is only used once in the codebase.
808 sub GetReserveStatus {
809 my ($itemnumber, $biblionumber) = @_;
811 my $dbh = C4::Context->dbh;
813 my ($sth, $found, $priority);
815 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE itemnumber = ? order by priority LIMIT 1");
816 $sth->execute($itemnumber);
817 ($found, $priority) = $sth->fetchrow_array;
820 if ( $biblionumber and not defined $found and not defined $priority ) {
821 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE biblionumber = ? order by priority LIMIT 1");
822 $sth->execute($biblionumber);
823 ($found, $priority) = $sth->fetchrow_array;
827 return 'Waiting' if $found eq 'W' and $priority == 0;
828 return 'Finished' if $found eq 'F';
831 return 'Reserved' if $priority > 0;
833 return ''; # empty string here will remove need for checking undef, or less log lines
838 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
839 ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);
840 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber,undef,$lookahead);
842 Find a book in the reserves.
844 C<$itemnumber> is the book's item number.
845 C<$lookahead> is the number of days to look in advance for future reserves.
847 As I understand it, C<&CheckReserves> looks for the given item in the
848 reserves. If it is found, that's a match, and C<$status> is set to
851 Otherwise, it finds the most important item in the reserves with the
852 same biblio number as this book (I'm not clear on this) and returns it
853 with C<$status> set to C<Reserved>.
855 C<&CheckReserves> returns a two-element list:
857 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
859 C<$reserve> is the reserve item that matched. It is a
860 reference-to-hash whose keys are mostly the fields of the reserves
861 table in the Koha database.
866 my ( $item, $barcode, $lookahead_days) = @_;
867 my $dbh = C4::Context->dbh;
870 if (C4::Context->preference('item-level_itypes')){
872 SELECT items.biblionumber,
873 items.biblioitemnumber,
874 itemtypes.notforloan,
875 items.notforloan AS itemnotforloan,
879 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
880 LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype
885 SELECT items.biblionumber,
886 items.biblioitemnumber,
887 itemtypes.notforloan,
888 items.notforloan AS itemnotforloan,
892 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
893 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
898 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
899 $sth->execute($item);
902 $sth = $dbh->prepare("$select WHERE barcode = ?");
903 $sth->execute($barcode);
905 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
906 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber, $damaged ) = $sth->fetchrow_array;
908 return if ( $damaged && !C4::Context->preference('AllowHoldsOnDamagedItems') );
910 return unless $itemnumber; # bail if we got nothing.
912 # if item is not for loan it cannot be reserved either.....
913 # execpt where items.notforloan < 0 : This indicates the item is holdable.
914 return if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
916 # Find this item in the reserves
917 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber, $lookahead_days);
919 # $priority and $highest are used to find the most important item
920 # in the list returned by &_Findgroupreserve. (The lower $priority,
921 # the more important the item.)
922 # $highest is the most important item we've seen so far.
924 if (scalar @reserves) {
925 my $priority = 10000000;
926 foreach my $res (@reserves) {
927 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
928 return ( "Waiting", $res, \@reserves ); # Found it
930 # See if this item is more important than what we've got so far
931 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
932 my $borrowerinfo=C4::Members::GetMember(borrowernumber => $res->{'borrowernumber'});
933 my $iteminfo=C4::Items::GetItem($itemnumber);
934 my $branch = GetReservesControlBranch( $iteminfo, $borrowerinfo );
935 my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
936 next if ($branchitemrule->{'holdallowed'} == 0);
937 next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
938 $priority = $res->{'priority'};
945 # If we get this far, then no exact match was found.
946 # We return the most important (i.e. next) reservation.
948 $highest->{'itemnumber'} = $item;
949 return ( "Reserved", $highest, \@reserves );
955 =head2 CancelExpiredReserves
957 CancelExpiredReserves();
959 Cancels all reserves with an expiration date from before today.
963 sub CancelExpiredReserves {
965 # Cancel reserves that have passed their expiration date.
966 my $dbh = C4::Context->dbh;
967 my $sth = $dbh->prepare( "
968 SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
969 AND expirationdate IS NOT NULL
974 while ( my $res = $sth->fetchrow_hashref() ) {
975 CancelReserve({ reserve_id => $res->{'reserve_id'} });
978 # Cancel reserves that have been waiting too long
979 if ( C4::Context->preference("ExpireReservesMaxPickUpDelay") ) {
980 my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
981 my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
983 my $query = "SELECT * FROM reserves WHERE TO_DAYS( NOW() ) - TO_DAYS( waitingdate ) > ? AND found = 'W' AND priority = 0";
984 $sth = $dbh->prepare( $query );
985 $sth->execute( $max_pickup_delay );
987 while (my $res = $sth->fetchrow_hashref ) {
989 manualinvoice($res->{'borrowernumber'}, $res->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
992 CancelReserve({ reserve_id => $res->{'reserve_id'} });
998 =head2 AutoUnsuspendReserves
1000 AutoUnsuspendReserves();
1002 Unsuspends all suspended reserves with a suspend_until date from before today.
1006 sub AutoUnsuspendReserves {
1008 my $dbh = C4::Context->dbh;
1010 my $query = "UPDATE reserves SET suspend = 0, suspend_until = NULL WHERE DATE( suspend_until ) < DATE( CURDATE() )";
1011 my $sth = $dbh->prepare( $query );
1016 =head2 CancelReserve
1018 CancelReserve({ reserve_id => $reserve_id, [ biblionumber => $biblionumber, borrowernumber => $borrrowernumber, itemnumber => $itemnumber ] });
1025 my ( $params ) = @_;
1027 my $reserve_id = $params->{'reserve_id'};
1028 $reserve_id = GetReserveId( $params ) unless ( $reserve_id );
1030 return unless ( $reserve_id );
1032 my $dbh = C4::Context->dbh;
1034 my $reserve = GetReserve( $reserve_id );
1038 SET cancellationdate = now(),
1041 WHERE reserve_id = ?
1043 my $sth = $dbh->prepare($query);
1044 $sth->execute( $reserve_id );
1047 INSERT INTO old_reserves
1048 SELECT * FROM reserves
1049 WHERE reserve_id = ?
1051 $sth = $dbh->prepare($query);
1052 $sth->execute( $reserve_id );
1055 DELETE FROM reserves
1056 WHERE reserve_id = ?
1058 $sth = $dbh->prepare($query);
1059 $sth->execute( $reserve_id );
1061 # now fix the priority on the others....
1062 _FixPriority({ biblionumber => $reserve->{biblionumber} });
1067 ModReserve({ rank => $rank,
1068 reserve_id => $reserve_id,
1069 branchcode => $branchcode
1070 [, itemnumber => $itemnumber ]
1071 [, biblionumber => $biblionumber, $borrowernumber => $borrowernumber ]
1074 Change a hold request's priority or cancel it.
1076 C<$rank> specifies the effect of the change. If C<$rank>
1077 is 'W' or 'n', nothing happens. This corresponds to leaving a
1078 request alone when changing its priority in the holds queue
1081 If C<$rank> is 'del', the hold request is cancelled.
1083 If C<$rank> is an integer greater than zero, the priority of
1084 the request is set to that value. Since priority != 0 means
1085 that the item is not waiting on the hold shelf, setting the
1086 priority to a non-zero value also sets the request's found
1087 status and waiting date to NULL.
1089 The optional C<$itemnumber> parameter is used only when
1090 C<$rank> is a non-zero integer; if supplied, the itemnumber
1091 of the hold request is set accordingly; if omitted, the itemnumber
1094 B<FIXME:> Note that the forgoing can have the effect of causing
1095 item-level hold requests to turn into title-level requests. This
1096 will be fixed once reserves has separate columns for requested
1097 itemnumber and supplying itemnumber.
1102 my ( $params ) = @_;
1104 my $rank = $params->{'rank'};
1105 my $reserve_id = $params->{'reserve_id'};
1106 my $branchcode = $params->{'branchcode'};
1107 my $itemnumber = $params->{'itemnumber'};
1108 my $suspend_until = $params->{'suspend_until'};
1109 my $borrowernumber = $params->{'borrowernumber'};
1110 my $biblionumber = $params->{'biblionumber'};
1112 return if $rank eq "W";
1113 return if $rank eq "n";
1115 return unless ( $reserve_id || ( $borrowernumber && ( $biblionumber || $itemnumber ) ) );
1116 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber, itemnumber => $itemnumber }) unless ( $reserve_id );
1118 my $dbh = C4::Context->dbh;
1119 if ( $rank eq "del" ) {
1120 CancelReserve({ reserve_id => $reserve_id });
1122 elsif ($rank =~ /^\d+/ and $rank > 0) {
1124 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1125 WHERE reserve_id = ?
1127 my $sth = $dbh->prepare($query);
1128 $sth->execute( $rank, $branchcode, $itemnumber, $reserve_id );
1130 if ( defined( $suspend_until ) ) {
1131 if ( $suspend_until ) {
1132 $suspend_until = C4::Dates->new( $suspend_until )->output("iso");
1133 $dbh->do("UPDATE reserves SET suspend = 1, suspend_until = ? WHERE reserve_id = ?", undef, ( $suspend_until, $reserve_id ) );
1135 $dbh->do("UPDATE reserves SET suspend_until = NULL WHERE reserve_id = ?", undef, ( $reserve_id ) );
1139 _FixPriority({ reserve_id => $reserve_id, rank =>$rank });
1143 =head2 ModReserveFill
1145 &ModReserveFill($reserve);
1147 Fill a reserve. If I understand this correctly, this means that the
1148 reserved book has been found and given to the patron who reserved it.
1150 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1151 whose keys are fields from the reserves table in the Koha database.
1155 sub ModReserveFill {
1157 my $dbh = C4::Context->dbh;
1158 # fill in a reserve record....
1159 my $reserve_id = $res->{'reserve_id'};
1160 my $biblionumber = $res->{'biblionumber'};
1161 my $borrowernumber = $res->{'borrowernumber'};
1162 my $resdate = $res->{'reservedate'};
1164 # get the priority on this record....
1166 my $query = "SELECT priority
1168 WHERE biblionumber = ?
1169 AND borrowernumber = ?
1170 AND reservedate = ?";
1171 my $sth = $dbh->prepare($query);
1172 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1173 ($priority) = $sth->fetchrow_array;
1175 # update the database...
1176 $query = "UPDATE reserves
1179 WHERE biblionumber = ?
1181 AND borrowernumber = ?
1183 $sth = $dbh->prepare($query);
1184 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1186 # move to old_reserves
1187 $query = "INSERT INTO old_reserves
1188 SELECT * FROM reserves
1189 WHERE biblionumber = ?
1191 AND borrowernumber = ?
1193 $sth = $dbh->prepare($query);
1194 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1195 $query = "DELETE FROM reserves
1196 WHERE biblionumber = ?
1198 AND borrowernumber = ?
1200 $sth = $dbh->prepare($query);
1201 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1203 # now fix the priority on the others (if the priority wasn't
1204 # already sorted!)....
1205 unless ( $priority == 0 ) {
1206 _FixPriority({ reserve_id => $reserve_id });
1210 =head2 ModReserveStatus
1212 &ModReserveStatus($itemnumber, $newstatus);
1214 Update the reserve status for the active (priority=0) reserve.
1216 $itemnumber is the itemnumber the reserve is on
1218 $newstatus is the new status.
1222 sub ModReserveStatus {
1224 #first : check if we have a reservation for this item .
1225 my ($itemnumber, $newstatus) = @_;
1226 my $dbh = C4::Context->dbh;
1228 my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1229 my $sth_set = $dbh->prepare($query);
1230 $sth_set->execute( $newstatus, $itemnumber );
1232 if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1233 CartToShelf( $itemnumber );
1237 =head2 ModReserveAffect
1239 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1241 This function affect an item and a status for a given reserve
1242 The itemnumber parameter is used to find the biblionumber.
1243 with the biblionumber & the borrowernumber, we can affect the itemnumber
1244 to the correct reserve.
1246 if $transferToDo is not set, then the status is set to "Waiting" as well.
1247 otherwise, a transfer is on the way, and the end of the transfer will
1248 take care of the waiting status
1252 sub ModReserveAffect {
1253 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1254 my $dbh = C4::Context->dbh;
1256 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1257 # attached to $itemnumber
1258 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1259 $sth->execute($itemnumber);
1260 my ($biblionumber) = $sth->fetchrow;
1262 # get request - need to find out if item is already
1263 # waiting in order to not send duplicate hold filled notifications
1264 my $reserve_id = GetReserveId({
1265 borrowernumber => $borrowernumber,
1266 biblionumber => $biblionumber,
1268 return unless defined $reserve_id;
1269 my $request = GetReserveInfo($reserve_id);
1270 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1272 # If we affect a reserve that has to be transfered, don't set to Waiting
1274 if ($transferToDo) {
1280 WHERE borrowernumber = ?
1281 AND biblionumber = ?
1285 # affect the reserve to Waiting as well.
1290 waitingdate = NOW(),
1292 WHERE borrowernumber = ?
1293 AND biblionumber = ?
1296 $sth = $dbh->prepare($query);
1297 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1298 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1300 if ( C4::Context->preference("ReturnToShelvingCart") ) {
1301 CartToShelf( $itemnumber );
1307 =head2 ModReserveCancelAll
1309 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1311 function to cancel reserv,check other reserves, and transfer document if it's necessary
1315 sub ModReserveCancelAll {
1318 my ( $itemnumber, $borrowernumber ) = @_;
1320 #step 1 : cancel the reservation
1321 my $CancelReserve = CancelReserve({ itemnumber => $itemnumber, borrowernumber => $borrowernumber });
1323 #step 2 launch the subroutine of the others reserves
1324 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1326 return ( $messages, $nextreservinfo );
1329 =head2 ModReserveMinusPriority
1331 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1333 Reduce the values of queued list
1337 sub ModReserveMinusPriority {
1338 my ( $itemnumber, $reserve_id ) = @_;
1340 #first step update the value of the first person on reserv
1341 my $dbh = C4::Context->dbh;
1344 SET priority = 0 , itemnumber = ?
1345 WHERE reserve_id = ?
1347 my $sth_upd = $dbh->prepare($query);
1348 $sth_upd->execute( $itemnumber, $reserve_id );
1349 # second step update all others reserves
1350 _FixPriority({ reserve_id => $reserve_id, rank => '0' });
1353 =head2 GetReserveInfo
1355 &GetReserveInfo($reserve_id);
1357 Get item and borrower details for a current hold.
1358 Current implementation this query should have a single result.
1362 sub GetReserveInfo {
1363 my ( $reserve_id ) = @_;
1364 my $dbh = C4::Context->dbh;
1369 reserves.borrowernumber,
1370 reserves.biblionumber,
1371 reserves.branchcode,
1372 reserves.waitingdate,
1388 items.holdingbranch,
1389 items.itemcallnumber,
1395 LEFT JOIN items USING(itemnumber)
1396 LEFT JOIN borrowers USING(borrowernumber)
1397 LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1398 WHERE reserves.reserve_id = ?";
1399 my $sth = $dbh->prepare($strsth);
1400 $sth->execute($reserve_id);
1402 my $data = $sth->fetchrow_hashref;
1406 =head2 IsAvailableForItemLevelRequest
1408 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1410 Checks whether a given item record is available for an
1411 item-level hold request. An item is available if
1413 * it is not lost AND
1414 * it is not damaged AND
1415 * it is not withdrawn AND
1416 * does not have a not for loan value > 0
1418 Whether or not the item is currently on loan is
1419 also checked - if the AllowOnShelfHolds system preference
1420 is ON, an item can be requested even if it is currently
1421 on loan to somebody else. If the system preference
1422 is OFF, an item that is currently checked out cannot
1423 be the target of an item-level hold request.
1425 Note that IsAvailableForItemLevelRequest() does not
1426 check if the staff operator is authorized to place
1427 a request on the item - in particular,
1428 this routine does not check IndependentBranches
1429 and canreservefromotherbranches.
1433 sub IsAvailableForItemLevelRequest {
1434 my $itemnumber = shift;
1436 my $item = GetItem($itemnumber);
1438 # must check the notforloan setting of the itemtype
1439 # FIXME - a lot of places in the code do this
1440 # or something similar - need to be
1442 my $dbh = C4::Context->dbh;
1443 my $notforloan_query;
1444 if (C4::Context->preference('item-level_itypes')) {
1445 $notforloan_query = "SELECT itemtypes.notforloan
1447 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1448 WHERE itemnumber = ?";
1450 $notforloan_query = "SELECT itemtypes.notforloan
1452 JOIN biblioitems USING (biblioitemnumber)
1453 JOIN itemtypes USING (itemtype)
1454 WHERE itemnumber = ?";
1456 my $sth = $dbh->prepare($notforloan_query);
1457 $sth->execute($itemnumber);
1458 my $notforloan_per_itemtype = 0;
1459 if (my ($notforloan) = $sth->fetchrow_array) {
1460 $notforloan_per_itemtype = 1 if $notforloan;
1463 my $available_per_item = 1;
1464 $available_per_item = 0 if $item->{itemlost} or
1465 ( $item->{notforloan} > 0 ) or
1466 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1467 $item->{withdrawn} or
1468 $notforloan_per_itemtype;
1471 if (C4::Context->preference('AllowOnShelfHolds')) {
1472 return $available_per_item;
1474 return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "Waiting"));
1478 =head2 AlterPriority
1480 AlterPriority( $where, $reserve_id );
1482 This function changes a reserve's priority up, down, to the top, or to the bottom.
1483 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1488 my ( $where, $reserve_id ) = @_;
1490 my $dbh = C4::Context->dbh;
1492 my $reserve = GetReserve( $reserve_id );
1494 if ( $reserve->{cancellationdate} ) {
1495 warn "I cannot alter the priority for reserve_id $reserve_id, the reserve has been cancelled (".$reserve->{cancellationdate}.')';
1499 if ( $where eq 'up' || $where eq 'down' ) {
1501 my $priority = $reserve->{'priority'};
1502 $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1503 _FixPriority({ reserve_id => $reserve_id, rank => $priority })
1505 } elsif ( $where eq 'top' ) {
1507 _FixPriority({ reserve_id => $reserve_id, rank => '1' })
1509 } elsif ( $where eq 'bottom' ) {
1511 _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1516 =head2 ToggleLowestPriority
1518 ToggleLowestPriority( $borrowernumber, $biblionumber );
1520 This function sets the lowestPriority field to true if is false, and false if it is true.
1524 sub ToggleLowestPriority {
1525 my ( $reserve_id ) = @_;
1527 my $dbh = C4::Context->dbh;
1529 my $sth = $dbh->prepare( "UPDATE reserves SET lowestPriority = NOT lowestPriority WHERE reserve_id = ?");
1530 $sth->execute( $reserve_id );
1532 _FixPriority({ reserve_id => $reserve_id, rank => '999999' });
1535 =head2 ToggleSuspend
1537 ToggleSuspend( $reserve_id );
1539 This function sets the suspend field to true if is false, and false if it is true.
1540 If the reserve is currently suspended with a suspend_until date, that date will
1541 be cleared when it is unsuspended.
1546 my ( $reserve_id, $suspend_until ) = @_;
1548 $suspend_until = output_pref(
1550 dt => dt_from_string($suspend_until),
1551 dateformat => 'iso',
1554 ) if ($suspend_until);
1556 my $do_until = ( $suspend_until ) ? '?' : 'NULL';
1558 my $dbh = C4::Context->dbh;
1560 my $sth = $dbh->prepare(
1561 "UPDATE reserves SET suspend = NOT suspend,
1562 suspend_until = CASE WHEN suspend = 0 THEN NULL ELSE $do_until END
1563 WHERE reserve_id = ?
1567 push( @params, $suspend_until ) if ( $suspend_until );
1568 push( @params, $reserve_id );
1570 $sth->execute( @params );
1576 borrowernumber => $borrowernumber,
1577 [ biblionumber => $biblionumber, ]
1578 [ suspend_until => $suspend_until, ]
1579 [ suspend => $suspend ]
1582 This function accepts a set of hash keys as its parameters.
1583 It requires either borrowernumber or biblionumber, or both.
1585 suspend_until is wholly optional.
1592 my $borrowernumber = $params{'borrowernumber'} || undef;
1593 my $biblionumber = $params{'biblionumber'} || undef;
1594 my $suspend_until = $params{'suspend_until'} || undef;
1595 my $suspend = defined( $params{'suspend'} ) ? $params{'suspend'} : 1;
1597 $suspend_until = C4::Dates->new( $suspend_until )->output("iso") if ( defined( $suspend_until ) );
1599 return unless ( $borrowernumber || $biblionumber );
1601 my ( $query, $sth, $dbh, @query_params );
1603 $query = "UPDATE reserves SET suspend = ? ";
1604 push( @query_params, $suspend );
1606 $query .= ", suspend_until = NULL ";
1607 } elsif ( $suspend_until ) {
1608 $query .= ", suspend_until = ? ";
1609 push( @query_params, $suspend_until );
1611 $query .= " WHERE ";
1612 if ( $borrowernumber ) {
1613 $query .= " borrowernumber = ? ";
1614 push( @query_params, $borrowernumber );
1616 $query .= " AND " if ( $borrowernumber && $biblionumber );
1617 if ( $biblionumber ) {
1618 $query .= " biblionumber = ? ";
1619 push( @query_params, $biblionumber );
1621 $query .= " AND found IS NULL ";
1623 $dbh = C4::Context->dbh;
1624 $sth = $dbh->prepare( $query );
1625 $sth->execute( @query_params );
1632 reserve_id => $reserve_id,
1634 [ignoreSetLowestRank => $ignoreSetLowestRank]
1639 _FixPriority({ biblionumber => $biblionumber});
1641 This routine adjusts the priority of a hold request and holds
1644 In the first form, where a reserve_id is passed, the priority of the
1645 hold is set to supplied rank, and other holds for that bib are adjusted
1646 accordingly. If the rank is "del", the hold is cancelled. If no rank
1647 is supplied, all of the holds on that bib have their priority adjusted
1648 as if the second form had been used.
1650 In the second form, where a biblionumber is passed, the holds on that
1651 bib (that are not captured) are sorted in order of increasing priority,
1652 then have reserves.priority set so that the first non-captured hold
1653 has its priority set to 1, the second non-captured hold has its priority
1654 set to 2, and so forth.
1656 In both cases, holds that have the lowestPriority flag on are have their
1657 priority adjusted to ensure that they remain at the end of the line.
1659 Note that the ignoreSetLowestRank parameter is meant to be used only
1660 when _FixPriority calls itself.
1665 my ( $params ) = @_;
1666 my $reserve_id = $params->{reserve_id};
1667 my $rank = $params->{rank} // '';
1668 my $ignoreSetLowestRank = $params->{ignoreSetLowestRank};
1669 my $biblionumber = $params->{biblionumber};
1671 my $dbh = C4::Context->dbh;
1673 unless ( $biblionumber ) {
1674 my $res = GetReserve( $reserve_id );
1675 $biblionumber = $res->{biblionumber};
1678 if ( $rank eq "del" ) {
1679 CancelReserve({ reserve_id => $reserve_id });
1681 elsif ( $rank eq "W" || $rank eq "0" ) {
1683 # make sure priority for waiting or in-transit items is 0
1687 WHERE reserve_id = ?
1688 AND found IN ('W', 'T')
1690 my $sth = $dbh->prepare($query);
1691 $sth->execute( $reserve_id );
1697 SELECT reserve_id, borrowernumber, reservedate, constrainttype
1699 WHERE biblionumber = ?
1700 AND ((found <> 'W' AND found <> 'T') OR found IS NULL)
1701 ORDER BY priority ASC
1703 my $sth = $dbh->prepare($query);
1704 $sth->execute( $biblionumber );
1705 while ( my $line = $sth->fetchrow_hashref ) {
1706 push( @priority, $line );
1709 # To find the matching index
1711 my $key = -1; # to allow for 0 to be a valid result
1712 for ( $i = 0 ; $i < @priority ; $i++ ) {
1713 if ( $reserve_id == $priority[$i]->{'reserve_id'} ) {
1714 $key = $i; # save the index
1719 # if index exists in array then move it to new position
1720 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1721 my $new_rank = $rank -
1722 1; # $new_rank is what you want the new index to be in the array
1723 my $moving_item = splice( @priority, $key, 1 );
1724 splice( @priority, $new_rank, 0, $moving_item );
1727 # now fix the priority on those that are left....
1731 WHERE reserve_id = ?
1733 $sth = $dbh->prepare($query);
1734 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1737 $priority[$j]->{'reserve_id'}
1741 $sth = $dbh->prepare( "SELECT reserve_id FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1744 unless ( $ignoreSetLowestRank ) {
1745 while ( my $res = $sth->fetchrow_hashref() ) {
1747 reserve_id => $res->{'reserve_id'},
1749 ignoreSetLowestRank => 1
1755 =head2 _Findgroupreserve
1757 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber, $lookahead);
1759 Looks for an item-specific match first, then for a title-level match, returning the
1760 first match found. If neither, then we look for a 3rd kind of match based on
1761 reserve constraints.
1762 Lookahead is the number of days to look in advance.
1764 TODO: add more explanation about reserve constraints
1766 C<&_Findgroupreserve> returns :
1767 C<@results> is an array of references-to-hash whose keys are mostly
1768 fields from the reserves table of the Koha database, plus
1769 C<biblioitemnumber>.
1773 sub _Findgroupreserve {
1774 my ( $bibitem, $biblio, $itemnumber, $lookahead) = @_;
1775 my $dbh = C4::Context->dbh;
1777 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1778 # check for exact targetted match
1779 my $item_level_target_query = qq/
1780 SELECT reserves.biblionumber AS biblionumber,
1781 reserves.borrowernumber AS borrowernumber,
1782 reserves.reservedate AS reservedate,
1783 reserves.branchcode AS branchcode,
1784 reserves.cancellationdate AS cancellationdate,
1785 reserves.found AS found,
1786 reserves.reservenotes AS reservenotes,
1787 reserves.priority AS priority,
1788 reserves.timestamp AS timestamp,
1789 biblioitems.biblioitemnumber AS biblioitemnumber,
1790 reserves.itemnumber AS itemnumber,
1791 reserves.reserve_id AS reserve_id
1793 JOIN biblioitems USING (biblionumber)
1794 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1797 AND item_level_request = 1
1799 AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1802 my $sth = $dbh->prepare($item_level_target_query);
1803 $sth->execute($itemnumber, $lookahead||0);
1805 if ( my $data = $sth->fetchrow_hashref ) {
1806 push( @results, $data );
1808 return @results if @results;
1810 # check for title-level targetted match
1811 my $title_level_target_query = qq/
1812 SELECT reserves.biblionumber AS biblionumber,
1813 reserves.borrowernumber AS borrowernumber,
1814 reserves.reservedate AS reservedate,
1815 reserves.branchcode AS branchcode,
1816 reserves.cancellationdate AS cancellationdate,
1817 reserves.found AS found,
1818 reserves.reservenotes AS reservenotes,
1819 reserves.priority AS priority,
1820 reserves.timestamp AS timestamp,
1821 biblioitems.biblioitemnumber AS biblioitemnumber,
1822 reserves.itemnumber AS itemnumber
1824 JOIN biblioitems USING (biblionumber)
1825 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1828 AND item_level_request = 0
1829 AND hold_fill_targets.itemnumber = ?
1830 AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1833 $sth = $dbh->prepare($title_level_target_query);
1834 $sth->execute($itemnumber, $lookahead||0);
1836 if ( my $data = $sth->fetchrow_hashref ) {
1837 push( @results, $data );
1839 return @results if @results;
1842 SELECT reserves.biblionumber AS biblionumber,
1843 reserves.borrowernumber AS borrowernumber,
1844 reserves.reservedate AS reservedate,
1845 reserves.waitingdate AS waitingdate,
1846 reserves.branchcode AS branchcode,
1847 reserves.cancellationdate AS cancellationdate,
1848 reserves.found AS found,
1849 reserves.reservenotes AS reservenotes,
1850 reserves.priority AS priority,
1851 reserves.timestamp AS timestamp,
1852 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1853 reserves.itemnumber AS itemnumber
1855 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1856 WHERE reserves.biblionumber = ?
1857 AND ( ( reserveconstraints.biblioitemnumber = ?
1858 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1859 AND reserves.reservedate = reserveconstraints.reservedate )
1860 OR reserves.constrainttype='a' )
1861 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1862 AND reserves.reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY)
1865 $sth = $dbh->prepare($query);
1866 $sth->execute( $biblio, $bibitem, $itemnumber, $lookahead||0);
1868 while ( my $data = $sth->fetchrow_hashref ) {
1869 push( @results, $data );
1874 =head2 _koha_notify_reserve
1876 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1878 Sends a notification to the patron that their hold has been filled (through
1879 ModReserveAffect, _not_ ModReserveFill)
1883 sub _koha_notify_reserve {
1884 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1886 my $dbh = C4::Context->dbh;
1887 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1889 # Try to get the borrower's email address
1890 my $to_address = C4::Members::GetNoticeEmailAddress($borrowernumber);
1895 if ( $to_address || $borrower->{'smsalertnumber'} ) {
1896 $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold_Filled' } );
1901 my $sth = $dbh->prepare("
1904 WHERE borrowernumber = ?
1905 AND biblionumber = ?
1907 $sth->execute( $borrowernumber, $biblionumber );
1908 my $reserve = $sth->fetchrow_hashref;
1909 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1911 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1913 my %letter_params = (
1914 module => 'reserves',
1915 branchcode => $reserve->{branchcode},
1917 'branches' => $branch_details,
1918 'borrowers' => $borrower,
1919 'biblio' => $biblionumber,
1920 'reserves' => $reserve,
1921 'items', $reserve->{'itemnumber'},
1923 substitute => { today => C4::Dates->new()->output() },
1927 if ( $print_mode ) {
1928 $letter_params{ 'letter_code' } = 'HOLD_PRINT';
1929 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1931 C4::Letters::EnqueueLetter( {
1933 borrowernumber => $borrowernumber,
1934 message_transport_type => 'print',
1940 if ( $to_address && defined $messagingprefs->{transports}->{'email'} ) {
1941 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'email'};
1942 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1944 C4::Letters::EnqueueLetter(
1945 { letter => $letter,
1946 borrowernumber => $borrowernumber,
1947 message_transport_type => 'email',
1948 from_address => $admin_email_address,
1953 if ( $borrower->{'smsalertnumber'} && defined $messagingprefs->{transports}->{'sms'} ) {
1954 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'sms'};
1955 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1957 C4::Letters::EnqueueLetter(
1958 { letter => $letter,
1959 borrowernumber => $borrowernumber,
1960 message_transport_type => 'sms',
1966 =head2 _ShiftPriorityByDateAndPriority
1968 $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1970 This increments the priority of all reserves after the one
1971 with either the lowest date after C<$reservedate>
1972 or the lowest priority after C<$priority>.
1974 It effectively makes room for a new reserve to be inserted with a certain
1975 priority, which is returned.
1977 This is most useful when the reservedate can be set by the user. It allows
1978 the new reserve to be placed before other reserves that have a later
1979 reservedate. Since priority also is set by the form in reserves/request.pl
1980 the sub accounts for that too.
1984 sub _ShiftPriorityByDateAndPriority {
1985 my ( $biblio, $resdate, $new_priority ) = @_;
1987 my $dbh = C4::Context->dbh;
1988 my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1989 my $sth = $dbh->prepare( $query );
1990 $sth->execute( $biblio, $resdate, $new_priority );
1991 my $min_priority = $sth->fetchrow;
1992 # if no such matches are found, $new_priority remains as original value
1993 $new_priority = $min_priority if ( $min_priority );
1995 # Shift the priority up by one; works in conjunction with the next SQL statement
1996 $query = "UPDATE reserves
1997 SET priority = priority+1
1998 WHERE biblionumber = ?
1999 AND borrowernumber = ?
2002 my $sth_update = $dbh->prepare( $query );
2004 # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
2005 $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
2006 $sth = $dbh->prepare( $query );
2007 $sth->execute( $new_priority, $biblio );
2008 while ( my $row = $sth->fetchrow_hashref ) {
2009 $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
2012 return $new_priority; # so the caller knows what priority they wind up receiving
2017 MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
2019 Use when checking out an item to handle reserves
2020 If $cancelreserve boolean is set to true, it will remove existing reserve
2025 my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
2027 my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
2030 my $biblionumber = $res->{biblionumber};
2031 my $biblioitemnumber = $res->{biblioitemnumber};
2033 if ($res->{borrowernumber} == $borrowernumber) {
2034 ModReserveFill($res);
2038 # The item is reserved by someone else.
2039 # Find this item in the reserves
2042 foreach (@$all_reserves) {
2043 $_->{'borrowernumber'} == $borrowernumber or next;
2044 $_->{'biblionumber'} == $biblionumber or next;
2051 # The item is reserved by the current patron
2052 ModReserveFill($borr_res);
2055 if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
2056 RevertWaitingStatus({ itemnumber => $itemnumber });
2058 elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
2060 biblionumber => $res->{'biblionumber'},
2061 itemnumber => $res->{'itemnumber'},
2062 borrowernumber => $res->{'borrowernumber'}
2070 MergeHolds($dbh,$to_biblio, $from_biblio);
2072 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2077 my ( $dbh, $to_biblio, $from_biblio ) = @_;
2078 my $sth = $dbh->prepare(
2079 "SELECT count(*) as reserve_count FROM reserves WHERE biblionumber = ?"
2081 $sth->execute($from_biblio);
2082 if ( my $data = $sth->fetchrow_hashref() ) {
2084 # holds exist on old record, if not we don't need to do anything
2085 $sth = $dbh->prepare(
2086 "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2087 $sth->execute( $to_biblio, $from_biblio );
2090 # don't reorder those already waiting
2092 $sth = $dbh->prepare(
2093 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2095 my $upd_sth = $dbh->prepare(
2096 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2097 AND reservedate = ? AND constrainttype = ? AND (itemnumber = ? or itemnumber is NULL) "
2099 $sth->execute( $to_biblio, 'W', 'T' );
2101 while ( my $reserve = $sth->fetchrow_hashref() ) {
2103 $priority, $to_biblio,
2104 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2105 $reserve->{'constrainttype'}, $reserve->{'itemnumber'}
2112 =head2 RevertWaitingStatus
2114 $success = RevertWaitingStatus({ itemnumber => $itemnumber });
2116 Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2118 Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2119 item level hold, even if it was only a bibliolevel hold to
2120 begin with. This is because we can no longer know if a hold
2121 was item-level or bib-level after a hold has been set to
2126 sub RevertWaitingStatus {
2127 my ( $params ) = @_;
2128 my $itemnumber = $params->{'itemnumber'};
2130 return unless ( $itemnumber );
2132 my $dbh = C4::Context->dbh;
2134 ## Get the waiting reserve we want to revert
2136 SELECT * FROM reserves
2137 WHERE itemnumber = ?
2138 AND found IS NOT NULL
2140 my $sth = $dbh->prepare( $query );
2141 $sth->execute( $itemnumber );
2142 my $reserve = $sth->fetchrow_hashref();
2144 ## Increment the priority of all other non-waiting
2145 ## reserves for this bib record
2149 priority = priority + 1
2155 $sth = $dbh->prepare( $query );
2156 $sth->execute( $reserve->{'biblionumber'} );
2158 ## Fix up the currently waiting reserve
2168 $sth = $dbh->prepare( $query );
2169 return $sth->execute( $reserve->{'reserve_id'} );
2174 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber [, itemnumber => $itemnumber ] });
2176 Returnes the first reserve id that matches the given criteria
2181 my ( $params ) = @_;
2183 return unless ( ( $params->{'biblionumber'} || $params->{'itemnumber'} ) && $params->{'borrowernumber'} );
2185 my $dbh = C4::Context->dbh();
2187 my $sql = "SELECT reserve_id FROM reserves WHERE ";
2191 foreach my $key ( keys %$params ) {
2192 if ( defined( $params->{$key} ) ) {
2193 push( @limits, "$key = ?" );
2194 push( @params, $params->{$key} );
2198 $sql .= join( " AND ", @limits );
2200 my $sth = $dbh->prepare( $sql );
2201 $sth->execute( @params );
2202 my $row = $sth->fetchrow_hashref();
2204 return $row->{'reserve_id'};
2209 ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2211 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2216 my ($branch, $borrowernumber, $biblionumber) = @_;
2218 # return unless ( C4::Context->boolean_preference('printreserveslips') );
2220 my $reserve_id = GetReserveId({
2221 biblionumber => $biblionumber,
2222 borrowernumber => $borrowernumber
2224 my $reserve = GetReserveInfo($reserve_id) or return;
2226 return C4::Letters::GetPreparedLetter (
2227 module => 'circulation',
2228 letter_code => 'RESERVESLIP',
2229 branchcode => $branch,
2231 'reserves' => $reserve,
2232 'branches' => $reserve->{branchcode},
2233 'borrowers' => $reserve->{borrowernumber},
2234 'biblio' => $reserve->{biblionumber},
2235 'items' => $reserve->{itemnumber},
2240 =head2 GetReservesControlBranch
2242 my $reserves_control_branch = GetReservesControlBranch($item, $borrower);
2244 Return the branchcode to be used to determine which reserves
2245 policy applies to a transaction.
2247 C<$item> is a hashref for an item. Only 'homebranch' is used.
2249 C<$borrower> is a hashref to borrower. Only 'branchcode' is used.
2253 sub GetReservesControlBranch {
2254 my ( $item, $borrower ) = @_;
2256 my $reserves_control = C4::Context->preference('ReservesControlBranch');
2259 ( $reserves_control eq 'ItemHomeLibrary' ) ? $item->{'homebranch'}
2260 : ( $reserves_control eq 'PatronLibrary' ) ? $borrower->{'branchcode'}
2266 =head2 CalculatePriority
2268 my $p = CalculatePriority($biblionumber, $resdate);
2270 Calculate priority for a new reserve on biblionumber, placing it at
2271 the end of the line of all holds whose start date falls before
2272 the current system time and that are neither on the hold shelf
2275 The reserve date parameter is optional; if it is supplied, the
2276 priority is based on the set of holds whose start date falls before
2277 the parameter value.
2279 After calculation of this priority, it is recommended to call
2280 _ShiftPriorityByDateAndPriority. Note that this is currently done in
2285 sub CalculatePriority {
2286 my ( $biblionumber, $resdate ) = @_;
2289 SELECT COUNT(*) FROM reserves
2290 WHERE biblionumber = ?
2292 AND (found IS NULL OR found = '')
2294 #skip found==W or found==T (waiting or transit holds)
2296 $sql.= ' AND ( reservedate <= ? )';
2299 $sql.= ' AND ( reservedate < NOW() )';
2301 my $dbh = C4::Context->dbh();
2302 my @row = $dbh->selectrow_array(
2305 $resdate ? ($biblionumber, $resdate) : ($biblionumber)
2308 return @row ? $row[0]+1 : 1;
2313 Koha Development Team <http://koha-community.org/>