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;
97 &GetReservesFromItemnumber
98 &GetReservesFromBiblionumber
99 &GetReservesFromBorrowernumber
100 &GetReservesForBranch
114 &ModReserveMinusPriority
121 &CancelExpiredReserves
123 &AutoUnsuspendReserves
125 &IsAvailableForItemLevelRequest
128 &ToggleLowestPriority
134 @EXPORT_OK = qw( MergeHolds );
139 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
145 $branch, $borrowernumber, $biblionumber,
146 $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
147 $title, $checkitem, $found
150 GetReserveFee($borrowernumber, $biblionumber, $constraint,
152 my $dbh = C4::Context->dbh;
153 my $const = lc substr( $constraint, 0, 1 );
154 $resdate = format_date_in_iso( $resdate ) if ( $resdate );
155 $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
157 $expdate = format_date_in_iso( $expdate );
159 undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
161 if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
162 # Make room in reserves for this before those of a later reserve date
163 $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
167 # If the reserv had the waiting status, we had the value of the resdate
168 if ( $found eq 'W' ) {
169 $waitingdate = $resdate;
173 # updates take place here
175 my $nextacctno = &getnextacctno( $borrowernumber );
177 INSERT INTO accountlines
178 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
180 (?,?,now(),?,?,'Res',?)
182 my $usth = $dbh->prepare($query);
183 $usth->execute( $borrowernumber, $nextacctno, $fee,
184 "Reserve Charge - $title", $fee );
190 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
191 priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
196 my $sth = $dbh->prepare($query);
198 $borrowernumber, $biblionumber, $resdate, $branch,
199 $const, $priority, $notes, $checkitem,
200 $found, $waitingdate, $expdate
203 # Send e-mail to librarian if syspref is active
204 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
205 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
206 my $branch_details = C4::Branch::GetBranchDetail($borrower->{branchcode});
207 if ( my $letter = C4::Letters::GetPreparedLetter (
208 module => 'reserves',
209 letter_code => 'HOLDPLACED',
210 branchcode => $branch,
212 'branches' => $branch_details,
213 'borrowers' => $borrower,
214 'biblio' => $biblionumber,
218 my $admin_email_address =$branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
220 C4::Letters::EnqueueLetter(
222 borrowernumber => $borrowernumber,
223 message_transport_type => 'email',
224 from_address => $admin_email_address,
225 to_address => $admin_email_address,
232 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
234 INSERT INTO reserveconstraints
235 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
239 $sth = $dbh->prepare($query); # keep prepare outside the loop!
240 foreach (@$bibitems) {
241 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
244 return; # FIXME: why not have a useful return value?
247 =head2 GetReservesFromBiblionumber
249 ($count, $title_reserves) = &GetReserves($biblionumber);
251 This function gets the list of reservations for one C<$biblionumber>, returning a count
252 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
256 sub GetReservesFromBiblionumber {
257 my ($biblionumber) = shift or return (0, []);
258 my ($all_dates) = shift;
259 my $dbh = C4::Context->dbh;
261 # Find the desired items in the reserves
264 timestamp AS rtimestamp,
278 WHERE biblionumber = ? ";
279 unless ( $all_dates ) {
280 $query .= "AND reservedate <= CURRENT_DATE()";
282 $query .= "ORDER BY priority";
283 my $sth = $dbh->prepare($query);
284 $sth->execute($biblionumber);
287 while ( my $data = $sth->fetchrow_hashref ) {
289 # FIXME - What is this doing? How do constraints work?
290 if ($data->{constrainttype} eq 'o') {
292 SELECT biblioitemnumber
293 FROM reserveconstraints
294 WHERE biblionumber = ?
295 AND borrowernumber = ?
298 my $csth = $dbh->prepare($query);
299 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
301 while ( my $bibitemnos = $csth->fetchrow_array ) {
302 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
304 my $count = scalar @bibitemno;
306 # if we have two or more different specific itemtypes
307 # reserved by same person on same day
310 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
311 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
314 # Look up the book we just found.
315 $bdata = GetBiblioItemData( $bibitemno[0] );
317 # Add the results of this latest search to the current
319 # FIXME - An 'each' would probably be more efficient.
320 foreach my $key ( keys %$bdata ) {
321 $data->{$key} = $bdata->{$key};
324 push @results, $data;
326 return ( $#results + 1, \@results );
329 =head2 GetReservesFromItemnumber
331 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
333 TODO :: Description here
337 sub GetReservesFromItemnumber {
338 my ( $itemnumber, $all_dates ) = @_;
339 my $dbh = C4::Context->dbh;
341 SELECT reservedate,borrowernumber,branchcode
345 unless ( $all_dates ) {
346 $query .= " AND reservedate <= CURRENT_DATE()";
348 my $sth_res = $dbh->prepare($query);
349 $sth_res->execute($itemnumber);
350 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
351 return ( $reservedate, $borrowernumber, $branchcode );
354 =head2 GetReservesFromBorrowernumber
356 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
362 sub GetReservesFromBorrowernumber {
363 my ( $borrowernumber, $status ) = @_;
364 my $dbh = C4::Context->dbh;
367 $sth = $dbh->prepare("
370 WHERE borrowernumber=?
374 $sth->execute($borrowernumber,$status);
376 $sth = $dbh->prepare("
379 WHERE borrowernumber=?
382 $sth->execute($borrowernumber);
384 my $data = $sth->fetchall_arrayref({});
387 #-------------------------------------------------------------------------------------
388 =head2 CanBookBeReserved
390 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
394 sub CanBookBeReserved{
395 my ($borrowernumber, $biblionumber) = @_;
397 my $items = GetItemnumbersForBiblio($biblionumber);
398 #get items linked via host records
399 my @hostitems = get_hostitemnumbers_of($biblionumber);
401 push (@$items,@hostitems);
404 foreach my $item (@$items){
405 return 1 if CanItemBeReserved($borrowernumber, $item);
410 =head2 CanItemBeReserved
412 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
414 This function return 1 if an item can be issued by this borrower.
418 sub CanItemBeReserved{
419 my ($borrowernumber, $itemnumber) = @_;
421 my $dbh = C4::Context->dbh;
422 my $allowedreserves = 0;
424 my $controlbranch = C4::Context->preference('ReservesControlBranch');
425 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
427 # we retrieve borrowers and items informations #
428 my $item = GetItem($itemnumber);
429 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
431 # we retrieve user rights on this itemtype and branchcode
432 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
434 WHERE (categorycode in (?,'*') )
435 AND (itemtype IN (?,'*'))
436 AND (branchcode IN (?,'*'))
443 my $querycount ="SELECT
446 LEFT JOIN items USING (itemnumber)
447 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
448 LEFT JOIN borrowers USING (borrowernumber)
449 WHERE borrowernumber = ?
453 my $itemtype = $item->{$itype};
454 my $categorycode = $borrower->{categorycode};
456 my $branchfield = "reserves.branchcode";
458 if( $controlbranch eq "ItemHomeLibrary" ){
459 $branchfield = "items.homebranch";
460 $branchcode = $item->{homebranch};
461 }elsif( $controlbranch eq "PatronLibrary" ){
462 $branchfield = "borrowers.branchcode";
463 $branchcode = $borrower->{branchcode};
467 $sth->execute($categorycode, $itemtype, $branchcode);
468 if(my $rights = $sth->fetchrow_hashref()){
469 $itemtype = $rights->{itemtype};
470 $allowedreserves = $rights->{reservesallowed};
477 $querycount .= "AND $branchfield = ?";
479 $querycount .= " AND $itype = ?" if ($itemtype ne "*");
480 my $sthcount = $dbh->prepare($querycount);
482 if($itemtype eq "*"){
483 $sthcount->execute($borrowernumber, $branchcode);
485 $sthcount->execute($borrowernumber, $branchcode, $itemtype);
488 my $reservecount = "0";
489 if(my $rowcount = $sthcount->fetchrow_hashref()){
490 $reservecount = $rowcount->{count};
493 # we check if it's ok or not
494 if( $reservecount < $allowedreserves ){
500 #--------------------------------------------------------------------------------
501 =head2 GetReserveCount
503 $number = &GetReserveCount($borrowernumber);
505 this function returns the number of reservation for a borrower given on input arg.
509 sub GetReserveCount {
510 my ($borrowernumber) = @_;
512 my $dbh = C4::Context->dbh;
515 SELECT COUNT(*) AS counter
517 WHERE borrowernumber = ?
519 my $sth = $dbh->prepare($query);
520 $sth->execute($borrowernumber);
521 my $row = $sth->fetchrow_hashref;
522 return $row->{counter};
525 =head2 GetOtherReserves
527 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
529 Check queued list of this document and check if this document must be transfered
533 sub GetOtherReserves {
534 my ($itemnumber) = @_;
537 my ( undef, $checkreserves, undef ) = CheckReserves($itemnumber);
538 if ($checkreserves) {
539 my $iteminfo = GetItem($itemnumber);
540 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
541 $messages->{'transfert'} = $checkreserves->{'branchcode'};
542 #minus priorities of others reservs
543 ModReserveMinusPriority(
545 $checkreserves->{'borrowernumber'},
546 $iteminfo->{'biblionumber'}
549 #launch the subroutine dotransfer
550 C4::Items::ModItemTransfer(
552 $iteminfo->{'holdingbranch'},
553 $checkreserves->{'branchcode'}
558 #step 2b : case of a reservation on the same branch, set the waiting status
560 $messages->{'waiting'} = 1;
561 ModReserveMinusPriority(
563 $checkreserves->{'borrowernumber'},
564 $iteminfo->{'biblionumber'}
566 ModReserveStatus($itemnumber,'W');
569 $nextreservinfo = $checkreserves->{'borrowernumber'};
572 return ( $messages, $nextreservinfo );
577 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
579 Calculate the fee for a reserve
584 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
587 my $dbh = C4::Context->dbh;
588 my $const = lc substr( $constraint, 0, 1 );
590 SELECT * FROM borrowers
591 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
592 WHERE borrowernumber = ?
594 my $sth = $dbh->prepare($query);
595 $sth->execute($borrowernumber);
596 my $data = $sth->fetchrow_hashref;
598 my $fee = $data->{'reservefee'};
599 my $cntitems = @- > $bibitems;
603 # check for items on issue
604 # first find biblioitem records
606 my $sth1 = $dbh->prepare(
607 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
608 WHERE (biblio.biblionumber = ?)"
610 $sth1->execute($biblionumber);
611 while ( my $data1 = $sth1->fetchrow_hashref ) {
612 if ( $const eq "a" ) {
613 push @biblioitems, $data1;
618 while ( $x < $cntitems ) {
619 if ( @$bibitems->{'biblioitemnumber'} ==
620 $data->{'biblioitemnumber'} )
626 if ( $const eq 'o' ) {
628 push @biblioitems, $data1;
633 push @biblioitems, $data1;
639 my $cntitemsfound = @biblioitems;
643 while ( $x < $cntitemsfound ) {
644 my $bitdata = $biblioitems[$x];
645 my $sth2 = $dbh->prepare(
647 WHERE biblioitemnumber = ?"
649 $sth2->execute( $bitdata->{'biblioitemnumber'} );
650 while ( my $itdata = $sth2->fetchrow_hashref ) {
651 my $sth3 = $dbh->prepare(
652 "SELECT * FROM issues
653 WHERE itemnumber = ?"
655 $sth3->execute( $itdata->{'itemnumber'} );
656 if ( my $isdata = $sth3->fetchrow_hashref ) {
664 if ( $allissued == 0 ) {
666 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
667 $rsth->execute($biblionumber);
668 if ( my $rdata = $rsth->fetchrow_hashref ) {
678 =head2 GetReservesToBranch
680 @transreserv = GetReservesToBranch( $frombranch );
682 Get reserve list for a given branch
686 sub GetReservesToBranch {
687 my ( $frombranch ) = @_;
688 my $dbh = C4::Context->dbh;
689 my $sth = $dbh->prepare(
690 "SELECT borrowernumber,reservedate,itemnumber,timestamp
695 $sth->execute( $frombranch );
698 while ( my $data = $sth->fetchrow_hashref ) {
699 $transreserv[$i] = $data;
702 return (@transreserv);
705 =head2 GetReservesForBranch
707 @transreserv = GetReservesForBranch($frombranch);
711 sub GetReservesForBranch {
712 my ($frombranch) = @_;
713 my $dbh = C4::Context->dbh;
714 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
719 $query .= " AND branchcode=? ";
721 $query .= "ORDER BY waitingdate" ;
722 my $sth = $dbh->prepare($query);
724 $sth->execute($frombranch);
731 while ( my $data = $sth->fetchrow_hashref ) {
732 $transreserv[$i] = $data;
735 return (@transreserv);
738 sub GetReserveStatus {
739 my ($itemnumber, $biblionumber) = @_;
741 my $dbh = C4::Context->dbh;
743 my ($sth, $found, $priority);
745 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE itemnumber = ? order by priority LIMIT 1");
746 $sth->execute($itemnumber);
747 ($found, $priority) = $sth->fetchrow_array;
750 if ( $biblionumber and not defined $found and not defined $priority ) {
751 $sth = $dbh->prepare("SELECT found, priority FROM reserves WHERE biblionumber = ? order by priority LIMIT 1");
752 $sth->execute($biblionumber);
756 ($found, $priority) = $sth->fetchrow_array;
758 return 'Waiting' if $found eq 'W' and $priority == 0;
759 return 'Finished' if $found eq 'F';
760 return 'Reserved' if $priority > 0;
766 ($status, $reserve, $all_reserves) = &CheckReserves($itemnumber);
767 ($status, $reserve, $all_reserves) = &CheckReserves(undef, $barcode);
769 Find a book in the reserves.
771 C<$itemnumber> is the book's item number.
773 As I understand it, C<&CheckReserves> looks for the given item in the
774 reserves. If it is found, that's a match, and C<$status> is set to
777 Otherwise, it finds the most important item in the reserves with the
778 same biblio number as this book (I'm not clear on this) and returns it
779 with C<$status> set to C<Reserved>.
781 C<&CheckReserves> returns a two-element list:
783 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
785 C<$reserve> is the reserve item that matched. It is a
786 reference-to-hash whose keys are mostly the fields of the reserves
787 table in the Koha database.
792 my ( $item, $barcode ) = @_;
793 my $dbh = C4::Context->dbh;
796 if (C4::Context->preference('item-level_itypes')){
798 SELECT items.biblionumber,
799 items.biblioitemnumber,
800 itemtypes.notforloan,
801 items.notforloan AS itemnotforloan,
804 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
805 LEFT JOIN itemtypes ON items.itype = itemtypes.itemtype
810 SELECT items.biblionumber,
811 items.biblioitemnumber,
812 itemtypes.notforloan,
813 items.notforloan AS itemnotforloan,
816 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
817 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
822 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
823 $sth->execute($item);
826 $sth = $dbh->prepare("$select WHERE barcode = ?");
827 $sth->execute($barcode);
829 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
830 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
832 return ( '' ) unless $itemnumber; # bail if we got nothing.
834 # if item is not for loan it cannot be reserved either.....
835 # execpt where items.notforloan < 0 : This indicates the item is holdable.
836 return ( '' ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
838 # Find this item in the reserves
839 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
841 # $priority and $highest are used to find the most important item
842 # in the list returned by &_Findgroupreserve. (The lower $priority,
843 # the more important the item.)
844 # $highest is the most important item we've seen so far.
846 if (scalar @reserves) {
847 my $priority = 10000000;
848 foreach my $res (@reserves) {
849 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
850 return ( "Waiting", $res, \@reserves ); # Found it
852 # See if this item is more important than what we've got so far
853 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
854 my $borrowerinfo=C4::Members::GetMember(borrowernumber => $res->{'borrowernumber'});
855 my $iteminfo=C4::Items::GetItem($itemnumber);
856 my $branch=C4::Circulation::_GetCircControlBranch($iteminfo,$borrowerinfo);
857 my $branchitemrule = C4::Circulation::GetBranchItemRule($branch,$iteminfo->{'itype'});
858 next if ($branchitemrule->{'holdallowed'} == 0);
859 next if (($branchitemrule->{'holdallowed'} == 1) && ($branch ne $borrowerinfo->{'branchcode'}));
860 $priority = $res->{'priority'};
867 # If we get this far, then no exact match was found.
868 # We return the most important (i.e. next) reservation.
870 $highest->{'itemnumber'} = $item;
871 return ( "Reserved", $highest, \@reserves );
877 =head2 CancelExpiredReserves
879 CancelExpiredReserves();
881 Cancels all reserves with an expiration date from before today.
885 sub CancelExpiredReserves {
887 # Cancel reserves that have passed their expiration date.
888 my $dbh = C4::Context->dbh;
889 my $sth = $dbh->prepare( "
890 SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
891 AND expirationdate IS NOT NULL
896 while ( my $res = $sth->fetchrow_hashref() ) {
897 CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
900 # Cancel reserves that have been waiting too long
901 if ( C4::Context->preference("ExpireReservesMaxPickUpDelay") ) {
902 my $max_pickup_delay = C4::Context->preference("ReservesMaxPickUpDelay");
903 my $charge = C4::Context->preference("ExpireReservesMaxPickUpDelayCharge");
905 my $query = "SELECT * FROM reserves WHERE TO_DAYS( NOW() ) - TO_DAYS( waitingdate ) > ? AND found = 'W' AND priority = 0";
906 $sth = $dbh->prepare( $query );
907 $sth->execute( $max_pickup_delay );
909 while (my $res = $sth->fetchrow_hashref ) {
911 manualinvoice($res->{'borrowernumber'}, $res->{'itemnumber'}, 'Hold waiting too long', 'F', $charge);
914 CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
920 =head2 AutoUnsuspendReserves
922 AutoUnsuspendReserves();
924 Unsuspends all suspended reserves with a suspend_until date from before today.
928 sub AutoUnsuspendReserves {
930 my $dbh = C4::Context->dbh;
932 my $query = "UPDATE reserves SET suspend = 0, suspend_until = NULL WHERE DATE( suspend_until ) < DATE( CURDATE() )";
933 my $sth = $dbh->prepare( $query );
940 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
944 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
945 cancel, but not both: if both are given, C<&CancelReserve> uses
948 C<$borrowernumber> is the borrower number of the patron on whose
949 behalf the book was reserved.
951 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
952 priorities of the other people who are waiting on the book.
957 my ( $biblio, $item, $borr ) = @_;
958 my $dbh = C4::Context->dbh;
959 if ( $item and $borr ) {
960 # removing a waiting reserve record....
961 # update the database...
964 SET cancellationdate = now(),
968 AND borrowernumber = ?
970 my $sth = $dbh->prepare($query);
971 $sth->execute( $item, $borr );
974 INSERT INTO old_reserves
975 SELECT * FROM reserves
977 AND borrowernumber = ?
979 $sth = $dbh->prepare($query);
980 $sth->execute( $item, $borr );
984 AND borrowernumber = ?
986 $sth = $dbh->prepare($query);
987 $sth->execute( $item, $borr );
990 # removing a reserve record....
991 # get the prioritiy on this record....
994 SELECT priority FROM reserves
995 WHERE biblionumber = ?
996 AND borrowernumber = ?
997 AND cancellationdate IS NULL
998 AND itemnumber IS NULL
1000 my $sth = $dbh->prepare($query);
1001 $sth->execute( $biblio, $borr );
1002 ($priority) = $sth->fetchrow_array;
1006 SET cancellationdate = now(),
1009 WHERE biblionumber = ?
1010 AND borrowernumber = ?
1013 # update the database, removing the record...
1014 $sth = $dbh->prepare($query);
1015 $sth->execute( $biblio, $borr );
1019 INSERT INTO old_reserves
1020 SELECT * FROM reserves
1021 WHERE biblionumber = ?
1022 AND borrowernumber = ?
1024 $sth = $dbh->prepare($query);
1025 $sth->execute( $biblio, $borr );
1028 DELETE FROM reserves
1029 WHERE biblionumber = ?
1030 AND borrowernumber = ?
1032 $sth = $dbh->prepare($query);
1033 $sth->execute( $biblio, $borr );
1035 # now fix the priority on the others....
1036 _FixPriority( $biblio, $borr );
1042 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
1044 Change a hold request's priority or cancel it.
1046 C<$rank> specifies the effect of the change. If C<$rank>
1047 is 'W' or 'n', nothing happens. This corresponds to leaving a
1048 request alone when changing its priority in the holds queue
1051 If C<$rank> is 'del', the hold request is cancelled.
1053 If C<$rank> is an integer greater than zero, the priority of
1054 the request is set to that value. Since priority != 0 means
1055 that the item is not waiting on the hold shelf, setting the
1056 priority to a non-zero value also sets the request's found
1057 status and waiting date to NULL.
1059 The optional C<$itemnumber> parameter is used only when
1060 C<$rank> is a non-zero integer; if supplied, the itemnumber
1061 of the hold request is set accordingly; if omitted, the itemnumber
1064 B<FIXME:> Note that the forgoing can have the effect of causing
1065 item-level hold requests to turn into title-level requests. This
1066 will be fixed once reserves has separate columns for requested
1067 itemnumber and supplying itemnumber.
1072 #subroutine to update a reserve
1073 my ( $rank, $biblio, $borrower, $branch , $itemnumber, $suspend_until) = @_;
1074 return if $rank eq "W";
1075 return if $rank eq "n";
1076 my $dbh = C4::Context->dbh;
1077 if ( $rank eq "del" ) {
1080 SET cancellationdate=now()
1081 WHERE biblionumber = ?
1082 AND borrowernumber = ?
1084 my $sth = $dbh->prepare($query);
1085 $sth->execute( $biblio, $borrower );
1088 INSERT INTO old_reserves
1091 WHERE biblionumber = ?
1092 AND borrowernumber = ?
1094 $sth = $dbh->prepare($query);
1095 $sth->execute( $biblio, $borrower );
1097 DELETE FROM reserves
1098 WHERE biblionumber = ?
1099 AND borrowernumber = ?
1101 $sth = $dbh->prepare($query);
1102 $sth->execute( $biblio, $borrower );
1105 elsif ($rank =~ /^\d+/ and $rank > 0) {
1107 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1108 WHERE biblionumber = ?
1109 AND borrowernumber = ?
1111 my $sth = $dbh->prepare($query);
1112 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1115 if ( defined( $suspend_until ) ) {
1116 if ( $suspend_until ) {
1117 $suspend_until = C4::Dates->new( $suspend_until )->output("iso");
1118 $dbh->do("UPDATE reserves SET suspend = 1, suspend_until = ? WHERE biblionumber = ? AND borrowernumber = ?", undef, ( $suspend_until, $biblio, $borrower ) );
1120 $dbh->do("UPDATE reserves SET suspend_until = NULL WHERE biblionumber = ? AND borrowernumber = ?", undef, ( $biblio, $borrower ) );
1124 _FixPriority( $biblio, $borrower, $rank);
1128 =head2 ModReserveFill
1130 &ModReserveFill($reserve);
1132 Fill a reserve. If I understand this correctly, this means that the
1133 reserved book has been found and given to the patron who reserved it.
1135 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1136 whose keys are fields from the reserves table in the Koha database.
1140 sub ModReserveFill {
1142 my $dbh = C4::Context->dbh;
1143 # fill in a reserve record....
1144 my $biblionumber = $res->{'biblionumber'};
1145 my $borrowernumber = $res->{'borrowernumber'};
1146 my $resdate = $res->{'reservedate'};
1148 # get the priority on this record....
1150 my $query = "SELECT priority
1152 WHERE biblionumber = ?
1153 AND borrowernumber = ?
1154 AND reservedate = ?";
1155 my $sth = $dbh->prepare($query);
1156 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1157 ($priority) = $sth->fetchrow_array;
1160 # update the database...
1161 $query = "UPDATE reserves
1164 WHERE biblionumber = ?
1166 AND borrowernumber = ?
1168 $sth = $dbh->prepare($query);
1169 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1172 # move to old_reserves
1173 $query = "INSERT INTO old_reserves
1174 SELECT * FROM reserves
1175 WHERE biblionumber = ?
1177 AND borrowernumber = ?
1179 $sth = $dbh->prepare($query);
1180 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1181 $query = "DELETE FROM reserves
1182 WHERE biblionumber = ?
1184 AND borrowernumber = ?
1186 $sth = $dbh->prepare($query);
1187 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1189 # now fix the priority on the others (if the priority wasn't
1190 # already sorted!)....
1191 unless ( $priority == 0 ) {
1192 _FixPriority( $biblionumber, $borrowernumber );
1196 =head2 ModReserveStatus
1198 &ModReserveStatus($itemnumber, $newstatus);
1200 Update the reserve status for the active (priority=0) reserve.
1202 $itemnumber is the itemnumber the reserve is on
1204 $newstatus is the new status.
1208 sub ModReserveStatus {
1210 #first : check if we have a reservation for this item .
1211 my ($itemnumber, $newstatus) = @_;
1212 my $dbh = C4::Context->dbh;
1214 my $query = "UPDATE reserves SET found = ?, waitingdate = NOW() WHERE itemnumber = ? AND found IS NULL AND priority = 0";
1215 my $sth_set = $dbh->prepare($query);
1216 $sth_set->execute( $newstatus, $itemnumber );
1218 if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1219 CartToShelf( $itemnumber );
1223 =head2 ModReserveAffect
1225 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1227 This function affect an item and a status for a given reserve
1228 The itemnumber parameter is used to find the biblionumber.
1229 with the biblionumber & the borrowernumber, we can affect the itemnumber
1230 to the correct reserve.
1232 if $transferToDo is not set, then the status is set to "Waiting" as well.
1233 otherwise, a transfer is on the way, and the end of the transfer will
1234 take care of the waiting status
1238 sub ModReserveAffect {
1239 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1240 my $dbh = C4::Context->dbh;
1242 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1243 # attached to $itemnumber
1244 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1245 $sth->execute($itemnumber);
1246 my ($biblionumber) = $sth->fetchrow;
1248 # get request - need to find out if item is already
1249 # waiting in order to not send duplicate hold filled notifications
1250 my $request = GetReserveInfo($borrowernumber, $biblionumber);
1251 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1253 # If we affect a reserve that has to be transfered, don't set to Waiting
1255 if ($transferToDo) {
1261 WHERE borrowernumber = ?
1262 AND biblionumber = ?
1266 # affect the reserve to Waiting as well.
1271 waitingdate = NOW(),
1273 WHERE borrowernumber = ?
1274 AND biblionumber = ?
1277 $sth = $dbh->prepare($query);
1278 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1279 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1281 if ( C4::Context->preference("ReturnToShelvingCart") ) {
1282 CartToShelf( $itemnumber );
1288 =head2 ModReserveCancelAll
1290 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1292 function to cancel reserv,check other reserves, and transfer document if it's necessary
1296 sub ModReserveCancelAll {
1299 my ( $itemnumber, $borrowernumber ) = @_;
1301 #step 1 : cancel the reservation
1302 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1304 #step 2 launch the subroutine of the others reserves
1305 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1307 return ( $messages, $nextreservinfo );
1310 =head2 ModReserveMinusPriority
1312 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1314 Reduce the values of queuded list
1318 sub ModReserveMinusPriority {
1319 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1321 #first step update the value of the first person on reserv
1322 my $dbh = C4::Context->dbh;
1325 SET priority = 0 , itemnumber = ?
1326 WHERE borrowernumber=?
1329 my $sth_upd = $dbh->prepare($query);
1330 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1331 # second step update all others reservs
1332 _FixPriority($biblionumber, $borrowernumber, '0');
1335 =head2 GetReserveInfo
1337 &GetReserveInfo($borrowernumber,$biblionumber);
1339 Get item and borrower details for a current hold.
1340 Current implementation this query should have a single result.
1344 sub GetReserveInfo {
1345 my ( $borrowernumber, $biblionumber ) = @_;
1346 my $dbh = C4::Context->dbh;
1350 reserves.borrowernumber,
1351 reserves.biblionumber,
1352 reserves.branchcode,
1353 reserves.waitingdate,
1369 items.holdingbranch,
1370 items.itemcallnumber,
1376 LEFT JOIN items USING(itemnumber)
1377 LEFT JOIN borrowers USING(borrowernumber)
1378 LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1380 reserves.borrowernumber=?
1381 AND reserves.biblionumber=?";
1382 my $sth = $dbh->prepare($strsth);
1383 $sth->execute($borrowernumber,$biblionumber);
1385 my $data = $sth->fetchrow_hashref;
1390 =head2 IsAvailableForItemLevelRequest
1392 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1394 Checks whether a given item record is available for an
1395 item-level hold request. An item is available if
1397 * it is not lost AND
1398 * it is not damaged AND
1399 * it is not withdrawn AND
1400 * does not have a not for loan value > 0
1402 Whether or not the item is currently on loan is
1403 also checked - if the AllowOnShelfHolds system preference
1404 is ON, an item can be requested even if it is currently
1405 on loan to somebody else. If the system preference
1406 is OFF, an item that is currently checked out cannot
1407 be the target of an item-level hold request.
1409 Note that IsAvailableForItemLevelRequest() does not
1410 check if the staff operator is authorized to place
1411 a request on the item - in particular,
1412 this routine does not check IndependantBranches
1413 and canreservefromotherbranches.
1417 sub IsAvailableForItemLevelRequest {
1418 my $itemnumber = shift;
1420 my $item = GetItem($itemnumber);
1422 # must check the notforloan setting of the itemtype
1423 # FIXME - a lot of places in the code do this
1424 # or something similar - need to be
1426 my $dbh = C4::Context->dbh;
1427 my $notforloan_query;
1428 if (C4::Context->preference('item-level_itypes')) {
1429 $notforloan_query = "SELECT itemtypes.notforloan
1431 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1432 WHERE itemnumber = ?";
1434 $notforloan_query = "SELECT itemtypes.notforloan
1436 JOIN biblioitems USING (biblioitemnumber)
1437 JOIN itemtypes USING (itemtype)
1438 WHERE itemnumber = ?";
1440 my $sth = $dbh->prepare($notforloan_query);
1441 $sth->execute($itemnumber);
1442 my $notforloan_per_itemtype = 0;
1443 if (my ($notforloan) = $sth->fetchrow_array) {
1444 $notforloan_per_itemtype = 1 if $notforloan;
1447 my $available_per_item = 1;
1448 $available_per_item = 0 if $item->{itemlost} or
1449 ( $item->{notforloan} > 0 ) or
1450 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1451 $item->{wthdrawn} or
1452 $notforloan_per_itemtype;
1455 if (C4::Context->preference('AllowOnShelfHolds')) {
1456 return $available_per_item;
1458 my $status = GetReserveStatus($itemnumber);
1459 return ($available_per_item and ($item->{onloan} or $status eq "Waiting" or $status = "Reserved"));
1463 =head2 AlterPriority
1465 AlterPriority( $where, $borrowernumber, $biblionumber, $reservedate );
1467 This function changes a reserve's priority up, down, to the top, or to the bottom.
1468 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1473 my ( $where, $borrowernumber, $biblionumber ) = @_;
1475 my $dbh = C4::Context->dbh;
1477 ## Find this reserve
1478 my $sth = $dbh->prepare('SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? AND cancellationdate IS NULL');
1479 $sth->execute( $biblionumber, $borrowernumber );
1480 my $reserve = $sth->fetchrow_hashref();
1483 if ( $where eq 'up' || $where eq 'down' ) {
1485 my $priority = $reserve->{'priority'};
1486 $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1487 _FixPriority( $biblionumber, $borrowernumber, $priority )
1489 } elsif ( $where eq 'top' ) {
1491 _FixPriority( $biblionumber, $borrowernumber, '1' )
1493 } elsif ( $where eq 'bottom' ) {
1495 _FixPriority( $biblionumber, $borrowernumber, '999999' )
1500 =head2 ToggleLowestPriority
1502 ToggleLowestPriority( $borrowernumber, $biblionumber );
1504 This function sets the lowestPriority field to true if is false, and false if it is true.
1508 sub ToggleLowestPriority {
1509 my ( $borrowernumber, $biblionumber ) = @_;
1511 my $dbh = C4::Context->dbh;
1513 my $sth = $dbh->prepare(
1514 "UPDATE reserves SET lowestPriority = NOT lowestPriority
1515 WHERE biblionumber = ?
1516 AND borrowernumber = ?"
1524 _FixPriority( $biblionumber, $borrowernumber, '999999' );
1527 =head2 ToggleSuspend
1529 ToggleSuspend( $borrowernumber, $biblionumber );
1531 This function sets the suspend field to true if is false, and false if it is true.
1532 If the reserve is currently suspended with a suspend_until date, that date will
1533 be cleared when it is unsuspended.
1538 my ( $borrowernumber, $biblionumber, $suspend_until ) = @_;
1540 $suspend_until = output_pref( dt_from_string( $suspend_until ), 'iso' ) if ( $suspend_until );
1542 my $do_until = ( $suspend_until ) ? '?' : 'NULL';
1544 my $dbh = C4::Context->dbh;
1546 my $sth = $dbh->prepare(
1547 "UPDATE reserves SET suspend = NOT suspend,
1548 suspend_until = CASE WHEN suspend = 0 THEN NULL ELSE $do_until END
1549 WHERE biblionumber = ?
1550 AND borrowernumber = ?
1554 push( @params, $suspend_until ) if ( $suspend_until );
1555 push( @params, $biblionumber );
1556 push( @params, $borrowernumber );
1558 $sth->execute( @params );
1565 borrowernumber => $borrowernumber,
1566 [ biblionumber => $biblionumber, ]
1567 [ suspend_until => $suspend_until, ]
1568 [ suspend => $suspend ]
1571 This function accepts a set of hash keys as its parameters.
1572 It requires either borrowernumber or biblionumber, or both.
1574 suspend_until is wholly optional.
1581 my $borrowernumber = $params{'borrowernumber'} || undef;
1582 my $biblionumber = $params{'biblionumber'} || undef;
1583 my $suspend_until = $params{'suspend_until'} || undef;
1584 my $suspend = defined( $params{'suspend'} ) ? $params{'suspend'} : 1;
1586 $suspend_until = C4::Dates->new( $suspend_until )->output("iso") if ( defined( $suspend_until ) );
1588 return unless ( $borrowernumber || $biblionumber );
1590 my ( $query, $sth, $dbh, @query_params );
1592 $query = "UPDATE reserves SET suspend = ? ";
1593 push( @query_params, $suspend );
1595 $query .= ", suspend_until = NULL ";
1596 } elsif ( $suspend_until ) {
1597 $query .= ", suspend_until = ? ";
1598 push( @query_params, $suspend_until );
1600 $query .= " WHERE ";
1601 if ( $borrowernumber ) {
1602 $query .= " borrowernumber = ? ";
1603 push( @query_params, $borrowernumber );
1605 $query .= " AND " if ( $borrowernumber && $biblionumber );
1606 if ( $biblionumber ) {
1607 $query .= " biblionumber = ? ";
1608 push( @query_params, $biblionumber );
1610 $query .= " AND found IS NULL ";
1612 $dbh = C4::Context->dbh;
1613 $sth = $dbh->prepare( $query );
1614 $sth->execute( @query_params );
1621 &_FixPriority($biblio,$borrowernumber,$rank,$ignoreSetLowestRank);
1623 Only used internally (so don't export it)
1624 Changed how this functions works #
1625 Now just gets an array of reserves in the rank order and updates them with
1626 the array index (+1 as array starts from 0)
1627 and if $rank is supplied will splice item from the array and splice it back in again
1628 in new priority rank
1633 my ( $biblio, $borrowernumber, $rank, $ignoreSetLowestRank ) = @_;
1634 my $dbh = C4::Context->dbh;
1635 if ( $rank eq "del" ) {
1636 CancelReserve( $biblio, undef, $borrowernumber );
1638 if ( $rank eq "W" || $rank eq "0" ) {
1640 # make sure priority for waiting or in-transit items is 0
1644 WHERE biblionumber = ?
1645 AND borrowernumber = ?
1646 AND found IN ('W', 'T')
1648 my $sth = $dbh->prepare($query);
1649 $sth->execute( $biblio, $borrowernumber );
1655 # FIXME adding a new security in returned elements for changing priority,
1656 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1657 # This is wrong a waiting reserve has W set
1658 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1660 SELECT borrowernumber, reservedate, constrainttype
1662 WHERE biblionumber = ?
1663 AND ((found <> 'W' AND found <> 'T') or found is NULL)
1664 ORDER BY priority ASC
1666 my $sth = $dbh->prepare($query);
1667 $sth->execute($biblio);
1668 while ( my $line = $sth->fetchrow_hashref ) {
1669 push( @reservedates, $line );
1670 push( @priority, $line );
1673 # To find the matching index
1675 my $key = -1; # to allow for 0 to be a valid result
1676 for ( $i = 0 ; $i < @priority ; $i++ ) {
1677 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1678 $key = $i; # save the index
1683 # if index exists in array then move it to new position
1684 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1685 my $new_rank = $rank -
1686 1; # $new_rank is what you want the new index to be in the array
1687 my $moving_item = splice( @priority, $key, 1 );
1688 splice( @priority, $new_rank, 0, $moving_item );
1691 # now fix the priority on those that are left....
1695 WHERE biblionumber = ?
1696 AND borrowernumber = ?
1700 $sth = $dbh->prepare($query);
1701 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1704 $priority[$j]->{'borrowernumber'},
1705 $priority[$j]->{'reservedate'}
1710 $sth = $dbh->prepare( "SELECT borrowernumber FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1713 unless ( $ignoreSetLowestRank ) {
1714 while ( my $res = $sth->fetchrow_hashref() ) {
1715 _FixPriority( $biblio, $res->{'borrowernumber'}, '999999', 1 );
1720 =head2 _Findgroupreserve
1722 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1724 Looks for an item-specific match first, then for a title-level match, returning the
1725 first match found. If neither, then we look for a 3rd kind of match based on
1726 reserve constraints.
1728 TODO: add more explanation about reserve constraints
1730 C<&_Findgroupreserve> returns :
1731 C<@results> is an array of references-to-hash whose keys are mostly
1732 fields from the reserves table of the Koha database, plus
1733 C<biblioitemnumber>.
1737 sub _Findgroupreserve {
1738 my ( $bibitem, $biblio, $itemnumber ) = @_;
1739 my $dbh = C4::Context->dbh;
1741 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1742 # check for exact targetted match
1743 my $item_level_target_query = qq/
1744 SELECT reserves.biblionumber AS biblionumber,
1745 reserves.borrowernumber AS borrowernumber,
1746 reserves.reservedate AS reservedate,
1747 reserves.branchcode AS branchcode,
1748 reserves.cancellationdate AS cancellationdate,
1749 reserves.found AS found,
1750 reserves.reservenotes AS reservenotes,
1751 reserves.priority AS priority,
1752 reserves.timestamp AS timestamp,
1753 biblioitems.biblioitemnumber AS biblioitemnumber,
1754 reserves.itemnumber AS itemnumber
1756 JOIN biblioitems USING (biblionumber)
1757 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1760 AND item_level_request = 1
1762 AND reservedate <= CURRENT_DATE()
1765 my $sth = $dbh->prepare($item_level_target_query);
1766 $sth->execute($itemnumber);
1768 if ( my $data = $sth->fetchrow_hashref ) {
1769 push( @results, $data );
1771 return @results if @results;
1773 # check for title-level targetted match
1774 my $title_level_target_query = qq/
1775 SELECT reserves.biblionumber AS biblionumber,
1776 reserves.borrowernumber AS borrowernumber,
1777 reserves.reservedate AS reservedate,
1778 reserves.branchcode AS branchcode,
1779 reserves.cancellationdate AS cancellationdate,
1780 reserves.found AS found,
1781 reserves.reservenotes AS reservenotes,
1782 reserves.priority AS priority,
1783 reserves.timestamp AS timestamp,
1784 biblioitems.biblioitemnumber AS biblioitemnumber,
1785 reserves.itemnumber AS itemnumber
1787 JOIN biblioitems USING (biblionumber)
1788 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1791 AND item_level_request = 0
1792 AND hold_fill_targets.itemnumber = ?
1793 AND reservedate <= CURRENT_DATE()
1796 $sth = $dbh->prepare($title_level_target_query);
1797 $sth->execute($itemnumber);
1799 if ( my $data = $sth->fetchrow_hashref ) {
1800 push( @results, $data );
1802 return @results if @results;
1805 SELECT reserves.biblionumber AS biblionumber,
1806 reserves.borrowernumber AS borrowernumber,
1807 reserves.reservedate AS reservedate,
1808 reserves.waitingdate AS waitingdate,
1809 reserves.branchcode AS branchcode,
1810 reserves.cancellationdate AS cancellationdate,
1811 reserves.found AS found,
1812 reserves.reservenotes AS reservenotes,
1813 reserves.priority AS priority,
1814 reserves.timestamp AS timestamp,
1815 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1816 reserves.itemnumber AS itemnumber
1818 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1819 WHERE reserves.biblionumber = ?
1820 AND ( ( reserveconstraints.biblioitemnumber = ?
1821 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1822 AND reserves.reservedate = reserveconstraints.reservedate )
1823 OR reserves.constrainttype='a' )
1824 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1825 AND reserves.reservedate <= CURRENT_DATE()
1828 $sth = $dbh->prepare($query);
1829 $sth->execute( $biblio, $bibitem, $itemnumber );
1831 while ( my $data = $sth->fetchrow_hashref ) {
1832 push( @results, $data );
1837 =head2 _koha_notify_reserve
1839 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1841 Sends a notification to the patron that their hold has been filled (through
1842 ModReserveAffect, _not_ ModReserveFill)
1846 sub _koha_notify_reserve {
1847 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1849 my $dbh = C4::Context->dbh;
1850 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
1852 # Try to get the borrower's email address
1854 my $which_address = C4::Context->preference('AutoEmailPrimaryAddress');
1855 # If the system preference is set to 'first valid' (value == OFF), look up email address
1856 if ($which_address eq 'OFF') {
1857 $to_address = C4::Members::GetFirstValidEmailAddress( $borrowernumber );
1859 $to_address = $borrower->{$which_address};
1865 if ( $to_address || $borrower->{'smsalertnumber'} ) {
1866 $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold_Filled' } );
1871 my $sth = $dbh->prepare("
1874 WHERE borrowernumber = ?
1875 AND biblionumber = ?
1877 $sth->execute( $borrowernumber, $biblionumber );
1878 my $reserve = $sth->fetchrow_hashref;
1879 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1881 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1883 my %letter_params = (
1884 module => 'reserves',
1885 branchcode => $reserve->{branchcode},
1887 'branches' => $branch_details,
1888 'borrowers' => $borrower,
1889 'biblio' => $biblionumber,
1890 'reserves' => $reserve,
1891 'items', $reserve->{'itemnumber'},
1893 substitute => { today => C4::Dates->new()->output() },
1897 if ( $print_mode ) {
1898 $letter_params{ 'letter_code' } = 'HOLD_PRINT';
1899 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1901 C4::Letters::EnqueueLetter( {
1903 borrowernumber => $borrowernumber,
1904 message_transport_type => 'print',
1910 if ( $to_address && defined $messagingprefs->{transports}->{'email'} ) {
1911 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'email'};
1912 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1914 C4::Letters::EnqueueLetter(
1915 { letter => $letter,
1916 borrowernumber => $borrowernumber,
1917 message_transport_type => 'email',
1918 from_address => $admin_email_address,
1923 if ( $borrower->{'smsalertnumber'} && defined $messagingprefs->{transports}->{'sms'} ) {
1924 $letter_params{ 'letter_code' } = $messagingprefs->{transports}->{'sms'};
1925 my $letter = C4::Letters::GetPreparedLetter ( %letter_params ) or die "Could not find a letter called '$letter_params{'letter_code'}' in the 'reserves' module";
1927 C4::Letters::EnqueueLetter(
1928 { letter => $letter,
1929 borrowernumber => $borrowernumber,
1930 message_transport_type => 'sms',
1936 =head2 _ShiftPriorityByDateAndPriority
1938 $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1940 This increments the priority of all reserves after the one
1941 with either the lowest date after C<$reservedate>
1942 or the lowest priority after C<$priority>.
1944 It effectively makes room for a new reserve to be inserted with a certain
1945 priority, which is returned.
1947 This is most useful when the reservedate can be set by the user. It allows
1948 the new reserve to be placed before other reserves that have a later
1949 reservedate. Since priority also is set by the form in reserves/request.pl
1950 the sub accounts for that too.
1954 sub _ShiftPriorityByDateAndPriority {
1955 my ( $biblio, $resdate, $new_priority ) = @_;
1957 my $dbh = C4::Context->dbh;
1958 my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1959 my $sth = $dbh->prepare( $query );
1960 $sth->execute( $biblio, $resdate, $new_priority );
1961 my $min_priority = $sth->fetchrow;
1962 # if no such matches are found, $new_priority remains as original value
1963 $new_priority = $min_priority if ( $min_priority );
1965 # Shift the priority up by one; works in conjunction with the next SQL statement
1966 $query = "UPDATE reserves
1967 SET priority = priority+1
1968 WHERE biblionumber = ?
1969 AND borrowernumber = ?
1972 my $sth_update = $dbh->prepare( $query );
1974 # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1975 $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1976 $sth = $dbh->prepare( $query );
1977 $sth->execute( $new_priority, $biblio );
1978 while ( my $row = $sth->fetchrow_hashref ) {
1979 $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1982 return $new_priority; # so the caller knows what priority they wind up receiving
1987 MoveReserve( $itemnumber, $borrowernumber, $cancelreserve )
1989 Use when checking out an item to handle reserves
1990 If $cancelreserve boolean is set to true, it will remove existing reserve
1995 my ( $itemnumber, $borrowernumber, $cancelreserve ) = @_;
1997 my ( $restype, $res, $all_reserves ) = CheckReserves( $itemnumber );
2000 my $biblionumber = $res->{biblionumber};
2001 my $biblioitemnumber = $res->{biblioitemnumber};
2003 if ($res->{borrowernumber} == $borrowernumber) {
2004 ModReserveFill($res);
2008 # The item is reserved by someone else.
2009 # Find this item in the reserves
2012 foreach (@$all_reserves) {
2013 $_->{'borrowernumber'} == $borrowernumber or next;
2014 $_->{'biblionumber'} == $biblionumber or next;
2021 # The item is reserved by the current patron
2022 ModReserveFill($borr_res);
2025 if ( $cancelreserve eq 'revert' ) { ## Revert waiting reserve to priority 1
2026 RevertWaitingStatus({ itemnumber => $itemnumber });
2028 elsif ( $cancelreserve eq 'cancel' || $cancelreserve ) { # cancel reserves on this item
2029 CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
2030 CancelReserve($res->{'biblionumber'}, 0, $res->{'borrowernumber'});
2037 MergeHolds($dbh,$to_biblio, $from_biblio);
2039 This shifts the holds from C<$from_biblio> to C<$to_biblio> and reorders them by the date they were placed
2044 my ( $dbh, $to_biblio, $from_biblio ) = @_;
2045 my $sth = $dbh->prepare(
2046 "SELECT count(*) as reservenumber FROM reserves WHERE biblionumber = ?"
2048 $sth->execute($from_biblio);
2049 if ( my $data = $sth->fetchrow_hashref() ) {
2051 # holds exist on old record, if not we don't need to do anything
2052 $sth = $dbh->prepare(
2053 "UPDATE reserves SET biblionumber = ? WHERE biblionumber = ?");
2054 $sth->execute( $to_biblio, $from_biblio );
2057 # don't reorder those already waiting
2059 $sth = $dbh->prepare(
2060 "SELECT * FROM reserves WHERE biblionumber = ? AND (found <> ? AND found <> ? OR found is NULL) ORDER BY reservedate ASC"
2062 my $upd_sth = $dbh->prepare(
2063 "UPDATE reserves SET priority = ? WHERE biblionumber = ? AND borrowernumber = ?
2064 AND reservedate = ? AND constrainttype = ? AND (itemnumber = ? or itemnumber is NULL) "
2066 $sth->execute( $to_biblio, 'W', 'T' );
2068 while ( my $reserve = $sth->fetchrow_hashref() ) {
2070 $priority, $to_biblio,
2071 $reserve->{'borrowernumber'}, $reserve->{'reservedate'},
2072 $reserve->{'constrainttype'}, $reserve->{'itemnumber'}
2079 =head2 RevertWaitingStatus
2081 $success = RevertWaitingStatus({ itemnumber => $itemnumber });
2083 Reverts a 'waiting' hold back to a regular hold with a priority of 1.
2085 Caveat: Any waiting hold fixed with RevertWaitingStatus will be an
2086 item level hold, even if it was only a bibliolevel hold to
2087 begin with. This is because we can no longer know if a hold
2088 was item-level or bib-level after a hold has been set to
2093 sub RevertWaitingStatus {
2094 my ( $params ) = @_;
2095 my $itemnumber = $params->{'itemnumber'};
2097 return unless ( $itemnumber );
2099 my $dbh = C4::Context->dbh;
2101 ## Get the waiting reserve we want to revert
2103 SELECT * FROM reserves
2104 WHERE itemnumber = ?
2105 AND found IS NOT NULL
2107 my $sth = $dbh->prepare( $query );
2108 $sth->execute( $itemnumber );
2109 my $reserve = $sth->fetchrow_hashref();
2111 ## Increment the priority of all other non-waiting
2112 ## reserves for this bib record
2116 priority = priority + 1
2122 $sth = $dbh->prepare( $query );
2123 $sth->execute( $reserve->{'biblionumber'} );
2125 ## Fix up the currently waiting reserve
2135 $sth = $dbh->prepare( $query );
2136 return $sth->execute( $reserve->{'reserve_id'} );
2141 ReserveSlip($branchcode, $borrowernumber, $biblionumber)
2143 Returns letter hash ( see C4::Letters::GetPreparedLetter ) or undef
2148 my ($branch, $borrowernumber, $biblionumber) = @_;
2150 # return unless ( C4::Context->boolean_preference('printreserveslips') );
2152 my $reserve = GetReserveInfo($borrowernumber,$biblionumber )
2155 return C4::Letters::GetPreparedLetter (
2156 module => 'circulation',
2157 letter_code => 'RESERVESLIP',
2158 branchcode => $branch,
2160 'reserves' => $reserve,
2161 'branches' => $reserve->{branchcode},
2162 'borrowers' => $reserve->{borrowernumber},
2163 'biblio' => $reserve->{biblionumber},
2164 'items' => $reserve->{itemnumber},
2171 Koha Development Team <http://koha-community.org/>