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 @EXPORT_OK = qw( MergeHolds );
140 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
146 $branch, $borrowernumber, $biblionumber,
147 $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
148 $title, $checkitem, $found
151 GetReserveFee($borrowernumber, $biblionumber, $constraint,
153 my $dbh = C4::Context->dbh;
154 my $const = lc substr( $constraint, 0, 1 );
155 $resdate = format_date_in_iso( $resdate ) if ( $resdate );
156 $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
158 $expdate = format_date_in_iso( $expdate );
160 undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
162 if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
163 # Make room in reserves for this before those of a later reserve date
164 $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
168 # If the reserv had the waiting status, we had the value of the resdate
169 if ( $found eq 'W' ) {
170 $waitingdate = $resdate;
174 # updates take place here
176 my $nextacctno = &getnextacctno( $borrowernumber );
178 INSERT INTO accountlines
179 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
181 (?,?,now(),?,?,'Res',?)
183 my $usth = $dbh->prepare($query);
184 $usth->execute( $borrowernumber, $nextacctno, $fee,
185 "Reserve Charge - $title", $fee );
191 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
192 priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
197 my $sth = $dbh->prepare($query);
199 $borrowernumber, $biblionumber, $resdate, $branch,
200 $const, $priority, $notes, $checkitem,
201 $found, $waitingdate, $expdate
204 # Send e-mail to librarian if syspref is active
205 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
206 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
207 my $branch_details = C4::Branch::GetBranchDetail($borrower->{branchcode});
208 if ( my $letter = C4::Letters::GetPreparedLetter (
209 module => 'reserves',
210 letter_code => 'HOLDPLACED',
211 branchcode => $branch,
213 'branches' => $branch_details,
214 'borrowers' => $borrower,
215 'biblio' => $biblionumber,
219 my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
221 C4::Letters::EnqueueLetter(
223 borrowernumber => $borrowernumber,
224 message_transport_type => 'email',
225 from_address => $admin_email_address,
226 to_address => $admin_email_address,
233 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
235 INSERT INTO reserveconstraints
236 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
240 $sth = $dbh->prepare($query); # keep prepare outside the loop!
241 foreach (@$bibitems) {
242 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
245 return; # FIXME: why not have a useful return value?
250 $res = GetReserve( $reserve_id );
255 my ($reserve_id) = @_;
256 #warn "C4::Reserves::GetReserve( $reserve_id )";
258 my $dbh = C4::Context->dbh;
259 my $query = "SELECT * FROM reserves WHERE reserve_id = ?";
260 my $sth = $dbh->prepare( $query );
261 $sth->execute( $reserve_id );
262 my $res = $sth->fetchrow_hashref();
266 =head2 GetReservesFromBiblionumber
268 ($count, $title_reserves) = GetReservesFromBiblionumber($biblionumber);
270 This function gets the list of reservations for one C<$biblionumber>, returning a count
271 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
275 sub GetReservesFromBiblionumber {
276 my ($biblionumber) = shift or return (0, []);
277 my ($all_dates) = shift;
278 my $dbh = C4::Context->dbh;
280 # Find the desired items in the reserves
284 timestamp AS rtimestamp,
298 WHERE biblionumber = ? ";
299 unless ( $all_dates ) {
300 $query .= "AND reservedate <= CURRENT_DATE()";
302 $query .= "ORDER BY priority";
303 my $sth = $dbh->prepare($query);
304 $sth->execute($biblionumber);
307 while ( my $data = $sth->fetchrow_hashref ) {
309 # FIXME - What is this doing? How do constraints work?
310 if ($data->{constrainttype} eq 'o') {
312 SELECT biblioitemnumber
313 FROM reserveconstraints
314 WHERE biblionumber = ?
315 AND borrowernumber = ?
318 my $csth = $dbh->prepare($query);
319 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
321 while ( my $bibitemnos = $csth->fetchrow_array ) {
322 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
324 my $count = scalar @bibitemno;
326 # if we have two or more different specific itemtypes
327 # reserved by same person on same day
330 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
331 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
334 # Look up the book we just found.
335 $bdata = GetBiblioItemData( $bibitemno[0] );
337 # Add the results of this latest search to the current
339 # FIXME - An 'each' would probably be more efficient.
340 foreach my $key ( keys %$bdata ) {
341 $data->{$key} = $bdata->{$key};
344 push @results, $data;
346 return ( $#results + 1, \@results );
349 =head2 GetReservesFromItemnumber
351 ( $reservedate, $borrowernumber, $branchcode, $reserve_id ) = GetReservesFromItemnumber($itemnumber);
353 TODO :: Description here
357 sub GetReservesFromItemnumber {
358 my ( $itemnumber, $all_dates ) = @_;
359 my $dbh = C4::Context->dbh;
361 SELECT reservedate,borrowernumber,branchcode,reserve_id
365 unless ( $all_dates ) {
366 $query .= " AND reservedate <= CURRENT_DATE()";
368 my $sth_res = $dbh->prepare($query);
369 $sth_res->execute($itemnumber);
370 my ( $reservedate, $borrowernumber, $branchcode, $reserve_id ) = $sth_res->fetchrow_array;
371 return ( $reservedate, $borrowernumber, $branchcode, $reserve_id );
374 =head2 GetReservesFromBorrowernumber
376 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
382 sub GetReservesFromBorrowernumber {
383 my ( $borrowernumber, $status ) = @_;
384 my $dbh = C4::Context->dbh;
387 $sth = $dbh->prepare("
390 WHERE borrowernumber=?
394 $sth->execute($borrowernumber,$status);
396 $sth = $dbh->prepare("
399 WHERE borrowernumber=?
402 $sth->execute($borrowernumber);
404 my $data = $sth->fetchall_arrayref({});
407 #-------------------------------------------------------------------------------------
408 =head2 CanBookBeReserved
410 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
414 sub CanBookBeReserved{
415 my ($borrowernumber, $biblionumber) = @_;
417 my $items = GetItemnumbersForBiblio($biblionumber);
418 #get items linked via host records
419 my @hostitems = get_hostitemnumbers_of($biblionumber);
421 push (@$items,@hostitems);
424 foreach my $item (@$items){
425 return 1 if CanItemBeReserved($borrowernumber, $item);
430 =head2 CanItemBeReserved
432 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
434 This function return 1 if an item can be issued by this borrower.
438 sub CanItemBeReserved{
439 my ($borrowernumber, $itemnumber) = @_;
441 my $dbh = C4::Context->dbh;
442 my $allowedreserves = 0;
444 my $controlbranch = C4::Context->preference('ReservesControlBranch');
445 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
447 # we retrieve borrowers and items informations #
448 my $item = GetItem($itemnumber);
449 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
451 # we retrieve user rights on this itemtype and branchcode
452 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
454 WHERE (categorycode in (?,'*') )
455 AND (itemtype IN (?,'*'))
456 AND (branchcode IN (?,'*'))
463 my $querycount ="SELECT
466 LEFT JOIN items USING (itemnumber)
467 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
468 LEFT JOIN borrowers USING (borrowernumber)
469 WHERE borrowernumber = ?
473 my $itemtype = $item->{$itype};
474 my $categorycode = $borrower->{categorycode};
476 my $branchfield = "reserves.branchcode";
478 if( $controlbranch eq "ItemHomeLibrary" ){
479 $branchfield = "items.homebranch";
480 $branchcode = $item->{homebranch};
481 }elsif( $controlbranch eq "PatronLibrary" ){
482 $branchfield = "borrowers.branchcode";
483 $branchcode = $borrower->{branchcode};
487 $sth->execute($categorycode, $itemtype, $branchcode);
488 if(my $rights = $sth->fetchrow_hashref()){
489 $itemtype = $rights->{itemtype};
490 $allowedreserves = $rights->{reservesallowed};
497 $querycount .= "AND $branchfield = ?";
499 $querycount .= " AND $itype = ?" if ($itemtype ne "*");
500 my $sthcount = $dbh->prepare($querycount);
502 if($itemtype eq "*"){
503 $sthcount->execute($borrowernumber, $branchcode);
505 $sthcount->execute($borrowernumber, $branchcode, $itemtype);
508 my $reservecount = "0";
509 if(my $rowcount = $sthcount->fetchrow_hashref()){
510 $reservecount = $rowcount->{count};
513 # we check if it's ok or not
514 if( $reservecount < $allowedreserves ){
520 #--------------------------------------------------------------------------------
521 =head2 GetReserveCount
523 $number = &GetReserveCount($borrowernumber);
525 this function returns the number of reservation for a borrower given on input arg.
529 sub GetReserveCount {
530 my ($borrowernumber) = @_;
532 my $dbh = C4::Context->dbh;
535 SELECT COUNT(*) AS counter
537 WHERE borrowernumber = ?
539 my $sth = $dbh->prepare($query);
540 $sth->execute($borrowernumber);
541 my $row = $sth->fetchrow_hashref;
542 return $row->{counter};
545 =head2 GetOtherReserves
547 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
549 Check queued list of this document and check if this document must be transfered
553 sub GetOtherReserves {
554 my ($itemnumber) = @_;
557 my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
558 if ($checkreserves) {
559 my $iteminfo = GetItem($itemnumber);
560 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
561 $messages->{'transfert'} = $checkreserves->{'branchcode'};
562 #minus priorities of others reservs
563 ModReserveMinusPriority(
565 $checkreserves->{'reserve_id'},
568 #launch the subroutine dotransfer
569 C4::Items::ModItemTransfer(
571 $iteminfo->{'holdingbranch'},
572 $checkreserves->{'branchcode'}
577 #step 2b : case of a reservation on the same branch, set the waiting status
579 $messages->{'waiting'} = 1;
580 ModReserveMinusPriority(
582 $checkreserves->{'reserve_id'},
584 ModReserveStatus($itemnumber,'W');
587 $nextreservinfo = $checkreserves->{'borrowernumber'};
590 return ( $messages, $nextreservinfo );
595 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
597 Calculate the fee for a reserve
602 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
605 my $dbh = C4::Context->dbh;
606 my $const = lc substr( $constraint, 0, 1 );
608 SELECT * FROM borrowers
609 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
610 WHERE borrowernumber = ?
612 my $sth = $dbh->prepare($query);
613 $sth->execute($borrowernumber);
614 my $data = $sth->fetchrow_hashref;
616 my $fee = $data->{'reservefee'};
617 my $cntitems = @- > $bibitems;
621 # check for items on issue
622 # first find biblioitem records
624 my $sth1 = $dbh->prepare(
625 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
626 WHERE (biblio.biblionumber = ?)"
628 $sth1->execute($biblionumber);
629 while ( my $data1 = $sth1->fetchrow_hashref ) {
630 if ( $const eq "a" ) {
631 push @biblioitems, $data1;
636 while ( $x < $cntitems ) {
637 if ( @$bibitems->{'biblioitemnumber'} ==
638 $data->{'biblioitemnumber'} )
644 if ( $const eq 'o' ) {
646 push @biblioitems, $data1;
651 push @biblioitems, $data1;
657 my $cntitemsfound = @biblioitems;
661 while ( $x < $cntitemsfound ) {
662 my $bitdata = $biblioitems[$x];
663 my $sth2 = $dbh->prepare(
665 WHERE biblioitemnumber = ?"
667 $sth2->execute( $bitdata->{'biblioitemnumber'} );
668 while ( my $itdata = $sth2->fetchrow_hashref ) {
669 my $sth3 = $dbh->prepare(
670 "SELECT * FROM issues
671 WHERE itemnumber = ?"
673 $sth3->execute( $itdata->{'itemnumber'} );
674 if ( my $isdata = $sth3->fetchrow_hashref ) {
682 if ( $allissued == 0 ) {
684 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
685 $rsth->execute($biblionumber);
686 if ( my $rdata = $rsth->fetchrow_hashref ) {
696 =head2 GetReservesToBranch
698 @transreserv = GetReservesToBranch( $frombranch );
700 Get reserve list for a given branch
704 sub GetReservesToBranch {
705 my ( $frombranch ) = @_;
706 my $dbh = C4::Context->dbh;
707 my $sth = $dbh->prepare(
708 "SELECT reserve_id,borrowernumber,reservedate,itemnumber,timestamp
713 $sth->execute( $frombranch );
716 while ( my $data = $sth->fetchrow_hashref ) {
717 $transreserv[$i] = $data;
720 return (@transreserv);
723 =head2 GetReservesForBranch
725 @transreserv = GetReservesForBranch($frombranch);
729 sub GetReservesForBranch {
730 my ($frombranch) = @_;
731 my $dbh = C4::Context->dbh;
734 SELECT reserve_id,borrowernumber,reservedate,itemnumber,waitingdate
739 $query .= " AND branchcode=? " if ( $frombranch );
740 $query .= "ORDER BY waitingdate" ;
742 my $sth = $dbh->prepare($query);
744 $sth->execute($frombranch);
751 while ( my $data = $sth->fetchrow_hashref ) {
752 $transreserv[$i] = $data;
755 return (@transreserv);
758 =head2 GetReserveStatus
760 $reservestatus = GetReserveStatus($itemnumber, $biblionumber);
762 Take an itemnumber or a biblionumber and return the status of the reserve places on it.
763 If several reserves exist, the reserve with the lower priority is given.
767 ## FIXME: I don't think this does what it thinks it does.
768 ## It only ever checks the first reserve result, even though
769 ## multiple reserves for that bib can have the itemnumber set
770 ## the sub is only used once in the codebase.
771 sub GetReserveStatus {
772 my ($itemnumber, $biblionumber) = @_;
774 my $dbh = C4::Context->dbh;
776 my ($sth, $found, $priority);
778 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE itemnumber = ? order by priority LIMIT 1");
779 $sth->execute($itemnumber);
780 ($found, $priority) = $sth->fetchrow_array;
783 if ( $biblionumber and not defined $found and not defined $priority ) {
784 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE biblionumber = ? order by priority LIMIT 1");
785 $sth->execute($biblionumber);
786 ($found, $priority) = $sth->fetchrow_array;
790 return 'Waiting' if $found eq 'W' and $priority == 0;
791 return 'Finished' if $found eq 'F';
792 return 'Reserved' if $priority > 0;
795 #empty string here will remove need for checking undef, or less log lines
800 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
801 ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);
803 Find a book in the reserves.
805 C<$itemnumber> is the book's item number.
807 As I understand it, C<&CheckReserves> looks for the given item in the
808 reserves. If it is found, that's a match, and C<$status> is set to
811 Otherwise, it finds the most important item in the reserves with the
812 same biblio number as this book (I'm not clear on this) and returns it
813 with C<$status> set to C<Reserved>.
815 C<&CheckReserves> returns a two-element list:
817 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
819 C<$reserve> is the reserve item that matched. It is a
820 reference-to-hash whose keys are mostly the fields of the reserves
821 table in the Koha database.
826 my ( $item, $barcode ) = @_;
827 my $dbh = C4::Context->dbh;
830 if (C4::Context->preference('item-level_itypes')){
832 SELECT items.biblionumber,
833 items.biblioitemnumber,
834 itemtypes.notforloan,
835 items.notforloan AS itemnotforloan,
838 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
839 LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype
844 SELECT items.biblionumber,
845 items.biblioitemnumber,
846 itemtypes.notforloan,
847 items.notforloan AS itemnotforloan,
850 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
851 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
856 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
857 $sth->execute($item);
860 $sth = $dbh->prepare("$select WHERE barcode = ?");
861 $sth->execute($barcode);
863 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
864 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
866 return ( '' ) unless $itemnumber; # bail if we got nothing.
868 # if item is not for loan it cannot be reserved either.....
869 # execpt where items.notforloan < 0 : This indicates the item is holdable.
870 return ( '' ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
872 # Find this item in the reserves
873 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
875 # $priority and $highest are used to find the most important item
876 # in the list returned by &_Findgroupreserve. (The lower $priority,
877 # the more important the item.)
878 # $highest is the most important item we've seen so far.
880 if (scalar @reserves) {
881 my $priority = 10000000;
882 foreach my $res (@reserves) {
883 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
884 return ( "Waiting", $res, \@reserves ); # Found it
886 # See if this item is more important than what we've got so far
887 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
888 my $borrowerinfo=C4::Members::GetMember(borrowernumber => $res->{'borrowernumber'});
889 my $iteminfo=C4::Items::GetItem($itemnumber);
890 my $branch=C4::Circulation::_GetCircControlBranch($iteminfo,$borrowerinfo);
891 my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
892 next if ($branchitemrule->{'holdallowed'} == 0);
893 next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
894 $priority = $res->{'priority'};
901 # If we get this far, then no exact match was found.
902 # We return the most important (i.e. next) reservation.
904 $highest->{'itemnumber'} = $item;
905 return ( "Reserved", $highest, \@reserves );
911 =head2 CancelExpiredReserves
913 CancelExpiredReserves();
915 Cancels all reserves with an expiration date from before today.
919 sub CancelExpiredReserves {
921 # Cancel reserves that have passed their expiration date.
922 my $dbh = C4::Context->dbh;
923 my $sth = $dbh->prepare( "
924 SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
925 AND expirationdate IS NOT NULL
930 while ( my $res = $sth->fetchrow_hashref() ) {
931 CancelReserve({ reserve_id => $res->{'reserve_id'} });
934 # Cancel reserves that have been waiting too long
935 if ( C4::Context->preference("ExpireReservesMaxPickUpDelay") ) {
936 my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
937 my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
939 my $query = "SELECT * FROM reserves WHERE TO_DAYS( NOW() ) - TO_DAYS( waitingdate ) > ? AND found = 'W' AND priority = 0";
940 $sth = $dbh->prepare( $query );
941 $sth->execute( $max_pickup_delay );
943 while (my $res = $sth->fetchrow_hashref ) {
945 manualinvoice($res->{'borrowernumber'}, $res->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
948 CancelReserve({ reserve_id => $res->{'reserve_id'} });
954 =head2 AutoUnsuspendReserves
956 AutoUnsuspendReserves();
958 Unsuspends all suspended reserves with a suspend_until date from before today.
962 sub AutoUnsuspendReserves {
964 my $dbh = C4::Context->dbh;
966 my $query = "UPDATE reserves SET suspend = 0, suspend_until = NULL WHERE DATE( suspend_until ) < DATE( CURDATE() )";
967 my $sth = $dbh->prepare( $query );
974 CancelReserve({ reserve_id => $reserve_id, [ biblionumber => $biblionumber, borrowernumber => $borrrowernumber, itemnumber => $itemnumber ] });
983 my $reserve_id = $params->{'reserve_id'};
984 $reserve_id = GetReserveId( $params ) unless ( $reserve_id );
986 return unless ( $reserve_id );
988 my $dbh = C4::Context->dbh;
992 SET cancellationdate = now(),
997 my $sth = $dbh->prepare($query);
998 $sth->execute( $reserve_id );
1002 INSERT INTO old_reserves
1003 SELECT * FROM reserves
1004 WHERE reserve_id = ?
1006 $sth = $dbh->prepare($query);
1007 $sth->execute( $reserve_id );
1010 DELETE FROM reserves
1011 WHERE reserve_id = ?
1013 $sth = $dbh->prepare($query);
1014 $sth->execute( $reserve_id );
1016 # now fix the priority on the others....
1017 _FixPriority( $reserve_id );
1022 ModReserve({ rank => $rank,
1023 reserve_id => $reserve_id,
1024 branchcode => $branchcode
1025 [, itemnumber => $itemnumber ]
1026 [, biblionumber => $biblionumber, $borrowernumber => $borrowernumber ]
1029 Change a hold request's priority or cancel it.
1031 C<$rank> specifies the effect of the change. If C<$rank>
1032 is 'W' or 'n', nothing happens. This corresponds to leaving a
1033 request alone when changing its priority in the holds queue
1036 If C<$rank> is 'del', the hold request is cancelled.
1038 If C<$rank> is an integer greater than zero, the priority of
1039 the request is set to that value. Since priority != 0 means
1040 that the item is not waiting on the hold shelf, setting the
1041 priority to a non-zero value also sets the request's found
1042 status and waiting date to NULL.
1044 The optional C<$itemnumber> parameter is used only when
1045 C<$rank> is a non-zero integer; if supplied, the itemnumber
1046 of the hold request is set accordingly; if omitted, the itemnumber
1049 B<FIXME:> Note that the forgoing can have the effect of causing
1050 item-level hold requests to turn into title-level requests. This
1051 will be fixed once reserves has separate columns for requested
1052 itemnumber and supplying itemnumber.
1057 my ( $params ) = @_;
1059 my $rank = $params->{'rank'};
1060 my $reserve_id = $params->{'reserve_id'};
1061 my $branchcode = $params->{'branchcode'};
1062 my $itemnumber = $params->{'itemnumber'};
1063 my $suspend_until = $params->{'suspend_until'};
1064 my $borrowernumber = $params->{'borrowernumber'};
1065 my $biblionumber = $params->{'biblionumber'};
1067 return if $rank eq "W";
1068 return if $rank eq "n";
1070 return unless ( $reserve_id || ( $borrowernumber && ( $biblionumber || $itemnumber ) ) );
1071 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber, itemnumber => $itemnumber }) unless ( $reserve_id );
1073 my $dbh = C4::Context->dbh;
1074 if ( $rank eq "del" ) {
1077 SET cancellationdate=now()
1078 WHERE reserve_id = ?
1080 my $sth = $dbh->prepare($query);
1081 $sth->execute( $reserve_id );
1084 INSERT INTO old_reserves
1087 WHERE reserve_id = ?
1089 $sth = $dbh->prepare($query);
1090 $sth->execute( $reserve_id );
1092 DELETE FROM reserves
1093 WHERE reserve_id = ?
1095 $sth = $dbh->prepare($query);
1096 $sth->execute( $reserve_id );
1099 elsif ($rank =~ /^\d+/ and $rank > 0) {
1101 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1102 WHERE reserve_id = ?
1104 my $sth = $dbh->prepare($query);
1105 $sth->execute( $rank, $branchcode, $itemnumber, $reserve_id );
1108 if ( defined( $suspend_until ) ) {
1109 if ( $suspend_until ) {
1110 $suspend_until = C4::Dates->new( $suspend_until )->output("iso");
1111 $dbh->do("UPDATE reserves SET suspend = 1, suspend_until = ? WHERE reserve_id = ?", undef, ( $suspend_until, $reserve_id ) );
1113 $dbh->do("UPDATE reserves SET suspend_until = NULL WHERE reserve_id = ?", undef, ( $reserve_id ) );
1117 _FixPriority( $reserve_id, $rank );
1121 =head2 ModReserveFill
1123 &ModReserveFill($reserve);
1125 Fill a reserve. If I understand this correctly, this means that the
1126 reserved book has been found and given to the patron who reserved it.
1128 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1129 whose keys are fields from the reserves table in the Koha database.
1133 sub ModReserveFill {
1135 my $dbh = C4::Context->dbh;
1136 # fill in a reserve record....
1137 my $reserve_id = $res->{'reserve_id'};
1138 my $biblionumber = $res->{'biblionumber'};
1139 my $borrowernumber = $res->{'borrowernumber'};
1140 my $resdate = $res->{'reservedate'};
1142 # get the priority on this record....
1144 my $query = "SELECT priority
1146 WHERE biblionumber = ?
1147 AND borrowernumber = ?
1148 AND reservedate = ?";
1149 my $sth = $dbh->prepare($query);
1150 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1151 ($priority) = $sth->fetchrow_array;
1154 # update the database...
1155 $query = "UPDATE reserves
1158 WHERE biblionumber = ?
1160 AND borrowernumber = ?
1162 $sth = $dbh->prepare($query);
1163 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1166 # move to old_reserves
1167 $query = "INSERT INTO old_reserves
1168 SELECT * FROM reserves
1169 WHERE biblionumber = ?
1171 AND borrowernumber = ?
1173 $sth = $dbh->prepare($query);
1174 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1175 $query = "DELETE FROM reserves
1176 WHERE biblionumber = ?
1178 AND borrowernumber = ?
1180 $sth = $dbh->prepare($query);
1181 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1183 # now fix the priority on the others (if the priority wasn't
1184 # already sorted!)....
1185 unless ( $priority == 0 ) {
1186 _FixPriority( $reserve_id );
1190 =head2 ModReserveStatus
1192 &ModReserveStatus($itemnumber, $newstatus);
1194 Update the reserve status for the active (priority=0) reserve.
1196 $itemnumber is the itemnumber the reserve is on
1198 $newstatus is the new status.
1202 sub ModReserveStatus {
1204 #first : check if we have a reservation for this item .
1205 my ($itemnumber, $newstatus) = @_;
1206 my $dbh = C4::Context->dbh;
1208 my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1209 my $sth_set = $dbh->prepare($query);
1210 $sth_set->execute( $newstatus, $itemnumber );
1212 if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1213 CartToShelf( $itemnumber );
1217 =head2 ModReserveAffect
1219 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1221 This function affect an item and a status for a given reserve
1222 The itemnumber parameter is used to find the biblionumber.
1223 with the biblionumber & the borrowernumber, we can affect the itemnumber
1224 to the correct reserve.
1226 if $transferToDo is not set, then the status is set to "Waiting" as well.
1227 otherwise, a transfer is on the way, and the end of the transfer will
1228 take care of the waiting status
1232 sub ModReserveAffect {
1233 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1234 my $dbh = C4::Context->dbh;
1236 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1237 # attached to $itemnumber
1238 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1239 $sth->execute($itemnumber);
1240 my ($biblionumber) = $sth->fetchrow;
1242 # get request - need to find out if item is already
1243 # waiting in order to not send duplicate hold filled notifications
1244 my $request = GetReserveInfo($borrowernumber, $biblionumber);
1245 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1247 # If we affect a reserve that has to be transfered, don't set to Waiting
1249 if ($transferToDo) {
1255 WHERE borrowernumber = ?
1256 AND biblionumber = ?
1260 # affect the reserve to Waiting as well.
1265 waitingdate = NOW(),
1267 WHERE borrowernumber = ?
1268 AND biblionumber = ?
1271 $sth = $dbh->prepare($query);
1272 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1273 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1275 if ( C4::Context->preference("ReturnToShelvingCart") ) {
1276 CartToShelf( $itemnumber );
1282 =head2 ModReserveCancelAll
1284 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1286 function to cancel reserv,check other reserves, and transfer document if it's necessary
1290 sub ModReserveCancelAll {
1293 my ( $itemnumber, $borrowernumber ) = @_;
1295 #step 1 : cancel the reservation
1296 my $CancelReserve = CancelReserve({ itemnumber => $itemnumber, borrowernumber => $borrowernumber });
1298 #step 2 launch the subroutine of the others reserves
1299 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1301 return ( $messages, $nextreservinfo );
1304 =head2 ModReserveMinusPriority
1306 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1308 Reduce the values of queued list
1312 sub ModReserveMinusPriority {
1313 my ( $itemnumber, $reserve_id ) = @_;
1315 #first step update the value of the first person on reserv
1316 my $dbh = C4::Context->dbh;
1319 SET priority = 0 , itemnumber = ?
1320 WHERE reserve_id = ?
1322 my $sth_upd = $dbh->prepare($query);
1323 $sth_upd->execute( $itemnumber, $reserve_id );
1324 # second step update all others reservs
1325 _FixPriority( $reserve_id, '0');
1328 =head2 GetReserveInfo
1330 &GetReserveInfo($reserve_id);
1332 Get item and borrower details for a current hold.
1333 Current implementation this query should have a single result.
1337 sub GetReserveInfo {
1338 my ( $reserve_id ) = @_;
1339 my $dbh = C4::Context->dbh;
1344 reserves.borrowernumber,
1345 reserves.biblionumber,
1346 reserves.branchcode,
1347 reserves.waitingdate,
1363 items.holdingbranch,
1364 items.itemcallnumber,
1370 LEFT JOIN items USING(itemnumber)
1371 LEFT JOIN borrowers USING(borrowernumber)
1372 LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1373 WHERE reserves.reserve_id = ?";
1374 my $sth = $dbh->prepare($strsth);
1375 $sth->execute($reserve_id);
1377 my $data = $sth->fetchrow_hashref;
1381 =head2 IsAvailableForItemLevelRequest
1383 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1385 Checks whether a given item record is available for an
1386 item-level hold request. An item is available if
1388 * it is not lost AND
1389 * it is not damaged AND
1390 * it is not withdrawn AND
1391 * does not have a not for loan value > 0
1393 Whether or not the item is currently on loan is
1394 also checked - if the AllowOnShelfHolds system preference
1395 is ON, an item can be requested even if it is currently
1396 on loan to somebody else. If the system preference
1397 is OFF, an item that is currently checked out cannot
1398 be the target of an item-level hold request.
1400 Note that IsAvailableForItemLevelRequest() does not
1401 check if the staff operator is authorized to place
1402 a request on the item - in particular,
1403 this routine does not check IndependentBranches
1404 and canreservefromotherbranches.
1408 sub IsAvailableForItemLevelRequest {
1409 my $itemnumber = shift;
1411 my $item = GetItem($itemnumber);
1413 # must check the notforloan setting of the itemtype
1414 # FIXME - a lot of places in the code do this
1415 # or something similar - need to be
1417 my $dbh = C4::Context->dbh;
1418 my $notforloan_query;
1419 if (C4::Context->preference('item-level_itypes')) {
1420 $notforloan_query = "SELECT itemtypes.notforloan
1422 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1423 WHERE itemnumber = ?";
1425 $notforloan_query = "SELECT itemtypes.notforloan
1427 JOIN biblioitems USING (biblioitemnumber)
1428 JOIN itemtypes USING (itemtype)
1429 WHERE itemnumber = ?";
1431 my $sth = $dbh->prepare($notforloan_query);
1432 $sth->execute($itemnumber);
1433 my $notforloan_per_itemtype = 0;
1434 if (my ($notforloan) = $sth->fetchrow_array) {
1435 $notforloan_per_itemtype = 1 if $notforloan;
1438 my $available_per_item = 1;
1439 $available_per_item = 0 if $item->{itemlost} or
1440 ( $item->{notforloan} > 0 ) or
1441 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1442 $item->{wthdrawn} or
1443 $notforloan_per_itemtype;
1446 if (C4::Context->preference('AllowOnShelfHolds')) {
1447 return $available_per_item;
1449 return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "Waiting"));
1453 =head2 AlterPriority
1455 AlterPriority( $where, $reserve_id );
1457 This function changes a reserve's priority up, down, to the top, or to the bottom.
1458 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1463 my ( $where, $reserve_id ) = @_;
1465 my $dbh = C4::Context->dbh;
1467 my $reserve = GetReserve( $reserve_id );
1469 if ( $where eq 'up' || $where eq 'down' ) {
1471 my $priority = $reserve->{'priority'};
1472 $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1473 _FixPriority( $reserve_id, $priority )
1475 } elsif ( $where eq 'top' ) {
1477 _FixPriority( $reserve_id, '1' )
1479 } elsif ( $where eq 'bottom' ) {
1481 _FixPriority( $reserve_id, '999999' )
1486 =head2 ToggleLowestPriority
1488 ToggleLowestPriority( $borrowernumber, $biblionumber );
1490 This function sets the lowestPriority field to true if is false, and false if it is true.
1494 sub ToggleLowestPriority {
1495 my ( $reserve_id ) = @_;
1497 my $dbh = C4::Context->dbh;
1499 my $sth = $dbh->prepare( "UPDATE reserves SET lowestPriority = NOT lowestPriority WHERE reserve_id = ?");
1500 $sth->execute( $reserve_id );
1503 _FixPriority( $reserve_id, '999999' );
1506 =head2 ToggleSuspend
1508 ToggleSuspend( $reserve_id );
1510 This function sets the suspend field to true if is false, and false if it is true.
1511 If the reserve is currently suspended with a suspend_until date, that date will
1512 be cleared when it is unsuspended.
1517 my ( $reserve_id, $suspend_until ) = @_;
1519 $suspend_until = output_pref( dt_from_string( $suspend_until ), 'iso' ) if ( $suspend_until );
1521 my $do_until = ( $suspend_until ) ? '?' : 'NULL';
1523 my $dbh = C4::Context->dbh;
1525 my $sth = $dbh->prepare(
1526 "UPDATE reserves SET suspend = NOT suspend,
1527 suspend_until = CASE WHEN suspend = 0 THEN NULL ELSE $do_until END
1528 WHERE reserve_id = ?
1532 push( @params, $suspend_until ) if ( $suspend_until );
1533 push( @params, $reserve_id );
1535 $sth->execute( @params );
1542 borrowernumber => $borrowernumber,
1543 [ biblionumber => $biblionumber, ]
1544 [ suspend_until => $suspend_until, ]
1545 [ suspend => $suspend ]
1548 This function accepts a set of hash keys as its parameters.
1549 It requires either borrowernumber or biblionumber, or both.
1551 suspend_until is wholly optional.
1558 my $borrowernumber = $params{'borrowernumber'} || undef;
1559 my $biblionumber = $params{'biblionumber'} || undef;
1560 my $suspend_until = $params{'suspend_until'} || undef;
1561 my $suspend = defined( $params{'suspend'} ) ? $params{'suspend'} : 1;
1563 $suspend_until = C4::Dates->new( $suspend_until )->output("iso") if ( defined( $suspend_until ) );
1565 return unless ( $borrowernumber || $biblionumber );
1567 my ( $query, $sth, $dbh, @query_params );
1569 $query = "UPDATE reserves SET suspend = ? ";
1570 push( @query_params, $suspend );
1572 $query .= ", suspend_until = NULL ";
1573 } elsif ( $suspend_until ) {
1574 $query .= ", suspend_until = ? ";
1575 push( @query_params, $suspend_until );
1577 $query .= " WHERE ";
1578 if ( $borrowernumber ) {
1579 $query .= " borrowernumber = ? ";
1580 push( @query_params, $borrowernumber );
1582 $query .= " AND " if ( $borrowernumber && $biblionumber );
1583 if ( $biblionumber ) {
1584 $query .= " biblionumber = ? ";
1585 push( @query_params, $biblionumber );
1587 $query .= " AND found IS NULL ";
1589 $dbh = C4::Context->dbh;
1590 $sth = $dbh->prepare( $query );
1591 $sth->execute( @query_params );
1598 &_FixPriority( $reserve_id, $rank, $ignoreSetLowestRank);
1600 Only used internally (so don't export it)
1601 Changed how this functions works #
1602 Now just gets an array of reserves in the rank order and updates them with
1603 the array index (+1 as array starts from 0)
1604 and if $rank is supplied will splice item from the array and splice it back in again
1605 in new priority rank
1610 my ( $reserve_id, $rank, $ignoreSetLowestRank ) = @_;
1611 my $dbh = C4::Context->dbh;
1613 my $res = GetReserve( $reserve_id );
1615 if ( $rank eq "del" ) {
1616 CancelReserve({ reserve_id => $reserve_id });
1618 elsif ( $rank eq "W" || $rank eq "0" ) {
1620 # make sure priority for waiting or in-transit items is 0
1624 WHERE reserve_id = ?
1625 AND found IN ('W', 'T')
1627 my $sth = $dbh->prepare($query);
1628 $sth->execute( $reserve_id );
1635 SELECT reserve_id, borrowernumber, reservedate, constrainttype
1637 WHERE biblionumber = ?
1638 AND ((found <> 'W' AND found <> 'T') OR found IS NULL)
1639 ORDER BY priority ASC
1641 my $sth = $dbh->prepare($query);
1642 $sth->execute( $res->{'biblionumber'} );
1643 while ( my $line = $sth->fetchrow_hashref ) {
1644 push( @reservedates, $line );
1645 push( @priority, $line );
1648 # To find the matching index
1650 my $key = -1; # to allow for 0 to be a valid result
1651 for ( $i = 0 ; $i < @priority ; $i++ ) {
1652 if ( $reserve_id == $priority[$i]->{'reserve_id'} ) {
1653 $key = $i; # save the index
1658 # if index exists in array then move it to new position
1659 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1660 my $new_rank = $rank -
1661 1; # $new_rank is what you want the new index to be in the array
1662 my $moving_item = splice( @priority, $key, 1 );
1663 splice( @priority, $new_rank, 0, $moving_item );
1666 # now fix the priority on those that are left....
1670 WHERE reserve_id = ?
1672 $sth = $dbh->prepare($query);
1673 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1676 $priority[$j]->{'reserve_id'}
1681 $sth = $dbh->prepare( "SELECT reserve_id FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1684 unless ( $ignoreSetLowestRank ) {
1685 while ( my $res = $sth->fetchrow_hashref() ) {
1686 _FixPriority( $res->{'reserve_id'}, '999999', 1 );
1691 =head2 _Findgroupreserve
1693 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1695 Looks for an item-specific match first, then for a title-level match, returning the
1696 first match found. If neither, then we look for a 3rd kind of match based on
1697 reserve constraints.
1699 TODO: add more explanation about reserve constraints
1701 C<&_Findgroupreserve> returns :
1702 C<@results> is an array of references-to-hash whose keys are mostly
1703 fields from the reserves table of the Koha database, plus
1704 C<biblioitemnumber>.
1708 sub _Findgroupreserve {
1709 my ( $bibitem, $biblio, $itemnumber ) = @_;
1710 my $dbh = C4::Context->dbh;
1712 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1713 # check for exact targetted match
1714 my $item_level_target_query = qq/
1715 SELECT reserves.biblionumber AS biblionumber,
1716 reserves.borrowernumber AS borrowernumber,
1717 reserves.reservedate AS reservedate,
1718 reserves.branchcode AS branchcode,
1719 reserves.cancellationdate AS cancellationdate,
1720 reserves.found AS found,
1721 reserves.reservenotes AS reservenotes,
1722 reserves.priority AS priority,
1723 reserves.timestamp AS timestamp,
1724 biblioitems.biblioitemnumber AS biblioitemnumber,
1725 reserves.itemnumber AS itemnumber,
1726 reserves.reserve_id AS reserve_id
1728 JOIN biblioitems USING (biblionumber)
1729 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1732 AND item_level_request = 1
1734 AND reservedate <= CURRENT_DATE()
1737 my $sth = $dbh->prepare($item_level_target_query);
1738 $sth->execute($itemnumber);
1740 if ( my $data = $sth->fetchrow_hashref ) {
1741 push( @results, $data );
1743 return @results if @results;
1745 # check for title-level targetted match
1746 my $title_level_target_query = qq/
1747 SELECT reserves.biblionumber AS biblionumber,
1748 reserves.borrowernumber AS borrowernumber,
1749 reserves.reservedate AS reservedate,
1750 reserves.branchcode AS branchcode,
1751 reserves.cancellationdate AS cancellationdate,
1752 reserves.found AS found,
1753 reserves.reservenotes AS reservenotes,
1754 reserves.priority AS priority,
1755 reserves.timestamp AS timestamp,
1756 biblioitems.biblioitemnumber AS biblioitemnumber,
1757 reserves.itemnumber AS itemnumber
1759 JOIN biblioitems USING (biblionumber)
1760 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1763 AND item_level_request = 0
1764 AND hold_fill_targets.itemnumber = ?
1765 AND reservedate <= CURRENT_DATE()
1768 $sth = $dbh->prepare($title_level_target_query);
1769 $sth->execute($itemnumber);
1771 if ( my $data = $sth->fetchrow_hashref ) {
1772 push( @results, $data );
1774 return @results if @results;
1777 SELECT reserves.biblionumber AS biblionumber,
1778 reserves.borrowernumber AS borrowernumber,
1779 reserves.reservedate AS reservedate,
1780 reserves.waitingdate AS waitingdate,
1781 reserves.branchcode AS branchcode,
1782 reserves.cancellationdate AS cancellationdate,
1783 reserves.found AS found,
1784 reserves.reservenotes AS reservenotes,
1785 reserves.priority AS priority,
1786 reserves.timestamp AS timestamp,
1787 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1788 reserves.itemnumber AS itemnumber
1790 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1791 WHERE reserves.biblionumber = ?
1792 AND ( ( reserveconstraints.biblioitemnumber = ?
1793 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1794 AND reserves.reservedate = reserveconstraints.reservedate )
1795 OR reserves.constrainttype='a' )
1796 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1797 AND reserves.reservedate <= CURRENT_DATE()
1800 $sth = $dbh->prepare($query);
1801 $sth->execute( $biblio, $bibitem, $itemnumber );
1803 while ( my $data = $sth->fetchrow_hashref ) {
1804 push( @results, $data );
1809 =head2 _koha_notify_reserve
1811 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1813 Sends a notification to the patron that their hold has been filled (through
1814 ModReserveAffect, _not_ ModReserveFill)
1818 sub _koha_notify_reserve {
1819 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1821 my $dbh = C4::Context->dbh;
1822 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1824 # Try to get the borrower's email address
1825 my $to_address = C4::Members::GetNoticeEmailAddress($borrowernumber);
1830 if ( $to_address || $borrower->{'smsalertnumber'} ) {
1831 $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold_Filled' } );
1836 my $sth = $dbh->prepare("
1839 WHERE borrowernumber = ?
1840 AND biblionumber = ?
1842 $sth->execute( $borrowernumber, $biblionumber );
1843 my $reserve = $sth->fetchrow_hashref;
1844 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1846 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1848 my %letter_params = (
1849 module => 'reserves',
1850 branchcode => $reserve->{branchcode},
1852 'branches' => $branch_details,
1853 'borrowers' => $borrower,
1854 'biblio' => $biblionumber,
1855 'reserves' => $reserve,
1856 'items', $reserve->{'itemnumber'},
1858 substitute => { today => C4::Dates->new()->output() },
1862 if ( $print_mode ) {
1863 $letter_params{ 'letter_code' } = 'HOLD_PRINT';
1864 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1866 C4::Letters::EnqueueLetter( {
1868 borrowernumber => $borrowernumber,
1869 message_transport_type => 'print',
1875 if ( $to_address && defined $messagingprefs->{transports}->{'email'} ) {
1876 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'email'};
1877 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1879 C4::Letters::EnqueueLetter(
1880 { letter => $letter,
1881 borrowernumber => $borrowernumber,
1882 message_transport_type => 'email',
1883 from_address => $admin_email_address,
1888 if ( $borrower->{'smsalertnumber'} && defined $messagingprefs->{transports}->{'sms'} ) {
1889 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'sms'};
1890 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1892 C4::Letters::EnqueueLetter(
1893 { letter => $letter,
1894 borrowernumber => $borrowernumber,
1895 message_transport_type => 'sms',
1901 =head2 _ShiftPriorityByDateAndPriority
1903 $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1905 This increments the priority of all reserves after the one
1906 with either the lowest date after C<$reservedate>
1907 or the lowest priority after C<$priority>.
1909 It effectively makes room for a new reserve to be inserted with a certain
1910 priority, which is returned.
1912 This is most useful when the reservedate can be set by the user. It allows
1913 the new reserve to be placed before other reserves that have a later
1914 reservedate. Since priority also is set by the form in reserves/request.pl
1915 the sub accounts for that too.
1919 sub _ShiftPriorityByDateAndPriority {
1920 my ( $biblio, $resdate, $new_priority ) = @_;
1922 my $dbh = C4::Context->dbh;
1923 my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1924 my $sth = $dbh->prepare( $query );
1925 $sth->execute( $biblio, $resdate, $new_priority );
1926 my $min_priority = $sth->fetchrow;
1927 # if no such matches are found, $new_priority remains as original value
1928 $new_priority = $min_priority if ( $min_priority );
1930 # Shift the priority up by one; works in conjunction with the next SQL statement
1931 $query = "UPDATE reserves
1932 SET priority = priority+1
1933 WHERE biblionumber = ?
1934 AND borrowernumber = ?
1937 my $sth_update = $dbh->prepare( $query );
1939 # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1940 $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1941 $sth = $dbh->prepare( $query );
1942 $sth->execute( $new_priority, $biblio );
1943 while ( my $row = $sth->fetchrow_hashref ) {
1944 $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1947 return $new_priority; # so the caller knows what priority they wind up receiving
1952 MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
1954 Use when checking out an item to handle reserves
1955 If $cancelreserve boolean is set to true, it will remove existing reserve
1960 my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
1962 my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
1965 my $biblionumber = $res->{biblionumber};
1966 my $biblioitemnumber = $res->{biblioitemnumber};
1968 if ($res->{borrowernumber} == $borrowernumber) {
1969 ModReserveFill($res);
1973 # The item is reserved by someone else.
1974 # Find this item in the reserves
1977 foreach (@$all_reserves) {
1978 $_->{'borrowernumber'} == $borrowernumber or next;
1979 $_->{'biblionumber'} == $biblionumber or next;
1986 # The item is reserved by the current patron
1987 ModReserveFill($borr_res);
1990 if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
1991 RevertWaitingStatus({ itemnumber => $itemnumber });
1993 elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
1995 biblionumber => $res->{'biblionumber'},
1996 itemnumber => $res->{'itemnumber'},
1997 borrowernumber => $res->{'borrowernumber'}
2005 MergeHolds($dbh,$to_biblio, $from_biblio);
2007 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2012 my ( $dbh, $to_biblio, $from_biblio ) = @_;
2013 my $sth = $dbh->prepare(
2014 "SELECT count(*) as reserve_id FROM reserves WHERE biblionumber = ?"
2016 $sth->execute($from_biblio);
2017 if ( my $data = $sth->fetchrow_hashref() ) {
2019 # holds exist on old record, if not we don't need to do anything
2020 $sth = $dbh->prepare(
2021 "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2022 $sth->execute( $to_biblio, $from_biblio );
2025 # don't reorder those already waiting
2027 $sth = $dbh->prepare(
2028 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2030 my $upd_sth = $dbh->prepare(
2031 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2032 AND reservedate = ? AND constrainttype = ? AND (itemnumber = ? or itemnumber is NULL) "
2034 $sth->execute( $to_biblio, 'W', 'T' );
2036 while ( my $reserve = $sth->fetchrow_hashref() ) {
2038 $priority, $to_biblio,
2039 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2040 $reserve->{'constrainttype'}, $reserve->{'itemnumber'}
2047 =head2 RevertWaitingStatus
2049 $success = RevertWaitingStatus({ itemnumber => $itemnumber });
2051 Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2053 Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2054 item level hold, even if it was only a bibliolevel hold to
2055 begin with. This is because we can no longer know if a hold
2056 was item-level or bib-level after a hold has been set to
2061 sub RevertWaitingStatus {
2062 my ( $params ) = @_;
2063 my $itemnumber = $params->{'itemnumber'};
2065 return unless ( $itemnumber );
2067 my $dbh = C4::Context->dbh;
2069 ## Get the waiting reserve we want to revert
2071 SELECT * FROM reserves
2072 WHERE itemnumber = ?
2073 AND found IS NOT NULL
2075 my $sth = $dbh->prepare( $query );
2076 $sth->execute( $itemnumber );
2077 my $reserve = $sth->fetchrow_hashref();
2079 ## Increment the priority of all other non-waiting
2080 ## reserves for this bib record
2084 priority = priority + 1
2090 $sth = $dbh->prepare( $query );
2091 $sth->execute( $reserve->{'biblionumber'} );
2093 ## Fix up the currently waiting reserve
2103 $sth = $dbh->prepare( $query );
2104 return $sth->execute( $reserve->{'reserve_id'} );
2109 $reserve_id = GetReserveId({ biblionumber => $biblionumber, borrowernumber => $borrowernumber [, itemnumber => $itemnumber ] });
2111 Returnes the first reserve id that matches the given criteria
2116 my ( $params ) = @_;
2118 return unless ( ( $params->{'biblionumber'} || $params->{'itemnumber'} ) && $params->{'borrowernumber'} );
2120 my $dbh = C4::Context->dbh();
2122 my $sql = "SELECT reserve_id FROM reserves WHERE ";
2126 foreach my $key ( keys %$params ) {
2127 if ( defined( $params->{$key} ) ) {
2128 push( @limits, "$key = ?" );
2129 push( @params, $params->{$key} );
2133 $sql .= join( " AND ", @limits );
2135 my $sth = $dbh->prepare( $sql );
2136 $sth->execute( @params );
2137 my $row = $sth->fetchrow_hashref();
2139 return $row->{'reserve_id'};
2144 ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2146 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2151 my ($branch, $borrowernumber, $biblionumber) = @_;
2153 # return unless ( C4::Context->boolean_preference('printreserveslips') );
2155 my $reserve = GetReserveInfo($borrowernumber,$biblionumber )
2158 return C4::Letters::GetPreparedLetter (
2159 module => 'circulation',
2160 letter_code => 'RESERVESLIP',
2161 branchcode => $branch,
2163 'reserves' => $reserve,
2164 'branches' => $reserve->{branchcode},
2165 'borrowers' => $reserve->{borrowernumber},
2166 'biblio' => $reserve->{biblionumber},
2167 'items' => $reserve->{itemnumber},
2174 Koha Development Team <http://koha-community.org/>