3 # Copyright 2000-2002 Katipo Communications
4 # 2006 SAN Ouest Provence
5 # 2007 BibLibre Paul POULAIN
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along
19 # with Koha; if not, write to the Free Software Foundation, Inc.,
20 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 #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 );
39 use List::MoreUtils qw( firstidx );
41 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
45 C4::Reserves - Koha functions for dealing with reservation.
53 this modules provides somes functions to deal with reservations.
55 Reserves are stored in reserves table.
56 The following columns contains important values :
57 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
58 =0 : then the reserve is being dealed
59 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
60 W(aiting) : the reserve has an itemnumber affected, and is on the way
61 F(inished) : the reserve has been completed, and is done
62 - itemnumber : empty : the reserve is still unaffected to an item
63 filled: the reserve is attached to an item
64 The complete workflow is :
65 ==== 1st use case ====
66 patron request a document, 1st available : P >0, F=NULL, I=NULL
67 a library having it run "transfertodo", and clic on the list
68 if there is no transfer to do, the reserve waiting
69 patron can pick it up P =0, F=W, I=filled
70 if there is a transfer to do, write in branchtransfer P =0, F=NULL, I=filled
71 The pickup library recieve the book, it check in P =0, F=W, I=filled
72 The patron borrow the book P =0, F=F, I=filled
74 ==== 2nd use case ====
75 patron requests a document, a given item,
76 If pickup is holding branch P =0, F=W, I=filled
77 If transfer needed, write in branchtransfer P =0, F=NULL, I=filled
78 The pickup library recieve the book, it checks it in P =0, F=W, I=filled
79 The patron borrow the book P =0, F=F, I=filled
88 # set the version for version checking
95 &GetReservesFromItemnumber
96 &GetReservesFromBiblionumber
97 &GetReservesFromBorrowernumber
111 &ModReserveMinusPriority
117 &CancelExpiredReserves
119 &IsAvailableForItemLevelRequest
122 &ToggleLowestPriority
128 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$resdate,$expdate,$notes,$title,$checkitem,$found)
134 $branch, $borrowernumber, $biblionumber,
135 $constraint, $bibitems, $priority, $resdate, $expdate, $notes,
136 $title, $checkitem, $found
139 GetReserveFee($borrowernumber, $biblionumber, $constraint,
141 my $dbh = C4::Context->dbh;
142 my $const = lc substr( $constraint, 0, 1 );
143 $resdate = format_date_in_iso( $resdate ) if ( $resdate );
144 $resdate = C4::Dates->today( 'iso' ) unless ( $resdate );
146 $expdate = format_date_in_iso( $expdate );
148 undef $expdate; # make reserves.expirationdate default to null rather than '0000-00-00'
150 if ( C4::Context->preference( 'AllowHoldDateInFuture' ) ) {
151 # Make room in reserves for this before those of a later reserve date
152 $priority = _ShiftPriorityByDateAndPriority( $biblionumber, $resdate, $priority );
156 # If the reserv had the waiting status, we had the value of the resdate
157 if ( $found eq 'W' ) {
158 $waitingdate = $resdate;
162 # updates take place here
164 my $nextacctno = &getnextacctno( $borrowernumber );
166 INSERT INTO accountlines
167 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
169 (?,?,now(),?,?,'Res',?)
171 my $usth = $dbh->prepare($query);
172 $usth->execute( $borrowernumber, $nextacctno, $fee,
173 "Reserve Charge - $title", $fee );
179 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
180 priority,reservenotes,itemnumber,found,waitingdate,expirationdate)
185 my $sth = $dbh->prepare($query);
187 $borrowernumber, $biblionumber, $resdate, $branch,
188 $const, $priority, $notes, $checkitem,
189 $found, $waitingdate, $expdate
192 # Send e-mail to librarian if syspref is active
193 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
194 my $borrower = C4::Members::GetMember(borrowernumber => $borrowernumber);
195 my $biblio = GetBiblioData($biblionumber);
196 my $letter = C4::Letters::getletter( 'reserves', 'HOLDPLACED');
197 my $admin_email_address = C4::Context->preference('KohaAdminEmailAddress');
199 my %keys = (%$borrower, %$biblio);
200 foreach my $key (keys %keys) {
201 my $replacefield = "<<$key>>";
202 $letter->{content} =~ s/$replacefield/$keys{$key}/g;
203 $letter->{title} =~ s/$replacefield/$keys{$key}/g;
206 C4::Letters::EnqueueLetter(
208 borrowernumber => $borrowernumber,
209 message_transport_type => 'email',
210 from_address => $admin_email_address,
211 to_address => $admin_email_address,
220 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
222 INSERT INTO reserveconstraints
223 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
227 $sth = $dbh->prepare($query); # keep prepare outside the loop!
228 foreach (@$bibitems) {
229 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
232 return; # FIXME: why not have a useful return value?
235 =item GetReservesFromBiblionumber
237 ($count, $title_reserves) = &GetReserves($biblionumber);
239 This function gets the list of reservations for one C<$biblionumber>, returning a count
240 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
244 sub GetReservesFromBiblionumber {
245 my ($biblionumber) = shift or return (0, []);
246 my ($all_dates) = shift;
247 my $dbh = C4::Context->dbh;
249 # Find the desired items in the reserves
252 timestamp AS rtimestamp,
264 WHERE biblionumber = ? ";
265 unless ( $all_dates ) {
266 $query .= "AND reservedate <= CURRENT_DATE()";
268 $query .= "ORDER BY priority";
269 my $sth = $dbh->prepare($query);
270 $sth->execute($biblionumber);
273 while ( my $data = $sth->fetchrow_hashref ) {
275 # FIXME - What is this doing? How do constraints work?
276 if ($data->{constrainttype} eq 'o') {
278 SELECT biblioitemnumber
279 FROM reserveconstraints
280 WHERE biblionumber = ?
281 AND borrowernumber = ?
284 my $csth = $dbh->prepare($query);
285 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
287 while ( my $bibitemnos = $csth->fetchrow_array ) {
288 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
290 my $count = scalar @bibitemno;
292 # if we have two or more different specific itemtypes
293 # reserved by same person on same day
296 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
297 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
300 # Look up the book we just found.
301 $bdata = GetBiblioItemData( $bibitemno[0] );
303 # Add the results of this latest search to the current
305 # FIXME - An 'each' would probably be more efficient.
306 foreach my $key ( keys %$bdata ) {
307 $data->{$key} = $bdata->{$key};
310 push @results, $data;
312 return ( $#results + 1, \@results );
315 =item GetReservesFromItemnumber
317 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
319 TODO :: Description here
323 sub GetReservesFromItemnumber {
324 my ( $itemnumber, $all_dates ) = @_;
325 my $dbh = C4::Context->dbh;
327 SELECT reservedate,borrowernumber,branchcode
331 unless ( $all_dates ) {
332 $query .= " AND reservedate <= CURRENT_DATE()";
334 my $sth_res = $dbh->prepare($query);
335 $sth_res->execute($itemnumber);
336 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
337 return ( $reservedate, $borrowernumber, $branchcode );
340 =item GetReservesFromBorrowernumber
342 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
348 sub GetReservesFromBorrowernumber {
349 my ( $borrowernumber, $status ) = @_;
350 my $dbh = C4::Context->dbh;
353 $sth = $dbh->prepare("
356 WHERE borrowernumber=?
360 $sth->execute($borrowernumber,$status);
362 $sth = $dbh->prepare("
365 WHERE borrowernumber=?
368 $sth->execute($borrowernumber);
370 my $data = $sth->fetchall_arrayref({});
373 #-------------------------------------------------------------------------------------
374 =item CanBookBeReserved
376 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
380 sub CanBookBeReserved{
381 my ($borrowernumber, $biblionumber) = @_;
383 my $dbh = C4::Context->dbh;
384 my $biblio = GetBiblioData($biblionumber);
385 my $borrower = C4::Members::GetMember(borrowernumber=>$borrowernumber);
386 my $controlbranch = C4::Context->preference('ReservesControlBranch');
387 my $itype = C4::Context->preference('item-level_itypes');
388 my $reservesrights= 0;
389 my $reservescount = 0;
391 # we retrieve the user rights
393 my $rightsquery = "SELECT categorycode, itemtype, branchcode, reservesallowed
395 WHERE categorycode IN (?, '*')";
396 push @args,$borrower->{categorycode};
398 if($controlbranch eq "ItemHomeLibrary"){
399 $rightsquery .= " AND branchcode = '*'";
400 }elsif($controlbranch eq "PatronLibrary"){
401 $rightsquery .= " AND branchcode IN (?,'*')";
402 push @args, $borrower->{branchcode};
406 $rightsquery .= " AND itemtype IN (?,'*')";
407 push @args, $biblio->{itemtype};
409 $rightsquery .= " AND itemtype = '*'";
412 $rightsquery .= " ORDER BY categorycode DESC, itemtype DESC, branchcode DESC";
413 my $sthrights = $dbh->prepare($rightsquery);
414 $sthrights->execute(@args);
416 if(my $row = $sthrights->fetchrow_hashref()){
417 $reservesrights = $row->{reservesallowed};
421 # we count how many reserves the borrower have
422 my $countquery = "SELECT count(*) as count
424 LEFT JOIN items USING (itemnumber)
425 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
426 LEFT JOIN borrowers USING (borrowernumber)
427 WHERE borrowernumber = ?
429 push @args, $borrowernumber;
432 $countquery .= "AND itemtype = ?";
433 push @args, $biblio->{itemtype};
436 if($controlbranch eq "PatronLibrary"){
437 $countquery .= " AND borrowers.branchcode = ? ";
438 push @args, $borrower->{branchcode};
441 my $sthcount = $dbh->prepare($countquery);
442 $sthcount->execute(@args);
444 if(my $row = $sthcount->fetchrow_hashref()){
445 $reservescount = $row->{count};
447 if($reservescount < $reservesrights){
455 =item CanItemBeReserved
457 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
459 this function return 1 if an item can be issued by this borrower.
463 sub CanItemBeReserved{
464 my ($borrowernumber, $itemnumber) = @_;
466 my $dbh = C4::Context->dbh;
467 my $allowedreserves = 0;
469 my $controlbranch = C4::Context->preference('ReservesControlBranch');
470 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
472 # we retrieve borrowers and items informations #
473 my $item = GetItem($itemnumber);
474 my $borrower = C4::Members::GetMember('borrowernumber'=>$borrowernumber);
476 # we retrieve user rights on this itemtype and branchcode
477 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
479 WHERE (categorycode in (?,'*') )
480 AND (itemtype IN (?,'*'))
481 AND (branchcode IN (?,'*'))
488 my $querycount ="SELECT
491 LEFT JOIN items USING (itemnumber)
492 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
493 LEFT JOIN borrowers USING (borrowernumber)
494 WHERE borrowernumber = ?
498 my $itemtype = $item->{$itype};
499 my $categorycode = $borrower->{categorycode};
501 my $branchfield = "reserves.branchcode";
503 if( $controlbranch eq "ItemHomeLibrary" ){
504 $branchfield = "items.homebranch";
505 $branchcode = $item->{homebranch};
506 }elsif( $controlbranch eq "PatronLibrary" ){
507 $branchfield = "borrowers.branchcode";
508 $branchcode = $borrower->{branchcode};
512 $sth->execute($categorycode, $itemtype, $branchcode);
513 if(my $rights = $sth->fetchrow_hashref()){
514 $itemtype = $rights->{itemtype};
515 $allowedreserves = $rights->{reservesallowed};
522 $querycount .= "AND $branchfield = ?";
524 $querycount .= " AND $itype = ?" if ($itemtype ne "*");
525 my $sthcount = $dbh->prepare($querycount);
527 if($itemtype eq "*"){
528 $sthcount->execute($borrowernumber, $branchcode);
530 $sthcount->execute($borrowernumber, $branchcode, $itemtype);
533 my $reservecount = "0";
534 if(my $rowcount = $sthcount->fetchrow_hashref()){
535 $reservecount = $rowcount->{count};
538 # we check if it's ok or not
539 if( $reservecount < $allowedreserves ){
545 #--------------------------------------------------------------------------------
546 =item GetReserveCount
548 $number = &GetReserveCount($borrowernumber);
550 this function returns the number of reservation for a borrower given on input arg.
554 sub GetReserveCount {
555 my ($borrowernumber) = @_;
557 my $dbh = C4::Context->dbh;
560 SELECT COUNT(*) AS counter
562 WHERE borrowernumber = ?
564 my $sth = $dbh->prepare($query);
565 $sth->execute($borrowernumber);
566 my $row = $sth->fetchrow_hashref;
567 return $row->{counter};
570 =item GetOtherReserves
572 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
574 Check queued list of this document and check if this document must be transfered
578 sub GetOtherReserves {
579 my ($itemnumber) = @_;
582 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
583 if ($checkreserves) {
584 my $iteminfo = GetItem($itemnumber);
585 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
586 $messages->{'transfert'} = $checkreserves->{'branchcode'};
587 #minus priorities of others reservs
588 ModReserveMinusPriority(
590 $checkreserves->{'borrowernumber'},
591 $iteminfo->{'biblionumber'}
594 #launch the subroutine dotransfer
595 C4::Items::ModItemTransfer(
597 $iteminfo->{'holdingbranch'},
598 $checkreserves->{'branchcode'}
603 #step 2b : case of a reservation on the same branch, set the waiting status
605 $messages->{'waiting'} = 1;
606 ModReserveMinusPriority(
608 $checkreserves->{'borrowernumber'},
609 $iteminfo->{'biblionumber'}
611 ModReserveStatus($itemnumber,'W');
614 $nextreservinfo = $checkreserves->{'borrowernumber'};
617 return ( $messages, $nextreservinfo );
622 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
624 Calculate the fee for a reserve
629 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
632 my $dbh = C4::Context->dbh;
633 my $const = lc substr( $constraint, 0, 1 );
635 SELECT * FROM borrowers
636 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
637 WHERE borrowernumber = ?
639 my $sth = $dbh->prepare($query);
640 $sth->execute($borrowernumber);
641 my $data = $sth->fetchrow_hashref;
643 my $fee = $data->{'reservefee'};
644 my $cntitems = @- > $bibitems;
648 # check for items on issue
649 # first find biblioitem records
651 my $sth1 = $dbh->prepare(
652 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
653 WHERE (biblio.biblionumber = ?)"
655 $sth1->execute($biblionumber);
656 while ( my $data1 = $sth1->fetchrow_hashref ) {
657 if ( $const eq "a" ) {
658 push @biblioitems, $data1;
663 while ( $x < $cntitems ) {
664 if ( @$bibitems->{'biblioitemnumber'} ==
665 $data->{'biblioitemnumber'} )
671 if ( $const eq 'o' ) {
673 push @biblioitems, $data1;
678 push @biblioitems, $data1;
684 my $cntitemsfound = @biblioitems;
688 while ( $x < $cntitemsfound ) {
689 my $bitdata = $biblioitems[$x];
690 my $sth2 = $dbh->prepare(
692 WHERE biblioitemnumber = ?"
694 $sth2->execute( $bitdata->{'biblioitemnumber'} );
695 while ( my $itdata = $sth2->fetchrow_hashref ) {
696 my $sth3 = $dbh->prepare(
697 "SELECT * FROM issues
698 WHERE itemnumber = ?"
700 $sth3->execute( $itdata->{'itemnumber'} );
701 if ( my $isdata = $sth3->fetchrow_hashref ) {
709 if ( $allissued == 0 ) {
711 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
712 $rsth->execute($biblionumber);
713 if ( my $rdata = $rsth->fetchrow_hashref ) {
723 =item GetReservesToBranch
725 @transreserv = GetReservesToBranch( $frombranch );
727 Get reserve list for a given branch
731 sub GetReservesToBranch {
732 my ( $frombranch ) = @_;
733 my $dbh = C4::Context->dbh;
734 my $sth = $dbh->prepare(
735 "SELECT borrowernumber,reservedate,itemnumber,timestamp
740 $sth->execute( $frombranch );
743 while ( my $data = $sth->fetchrow_hashref ) {
744 $transreserv[$i] = $data;
747 return (@transreserv);
750 =item GetReservesForBranch
752 @transreserv = GetReservesForBranch($frombranch);
756 sub GetReservesForBranch {
757 my ($frombranch) = @_;
758 my $dbh = C4::Context->dbh;
759 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
764 $query .= " AND branchcode=? ";
766 $query .= "ORDER BY waitingdate" ;
767 my $sth = $dbh->prepare($query);
769 $sth->execute($frombranch);
776 while ( my $data = $sth->fetchrow_hashref ) {
777 $transreserv[$i] = $data;
780 return (@transreserv);
785 ($status, $reserve) = &CheckReserves($itemnumber);
786 ($status, $reserve) = &CheckReserves(undef, $barcode);
788 Find a book in the reserves.
790 C<$itemnumber> is the book's item number.
792 As I understand it, C<&CheckReserves> looks for the given item in the
793 reserves. If it is found, that's a match, and C<$status> is set to
796 Otherwise, it finds the most important item in the reserves with the
797 same biblio number as this book (I'm not clear on this) and returns it
798 with C<$status> set to C<Reserved>.
800 C<&CheckReserves> returns a two-element list:
802 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
804 C<$reserve> is the reserve item that matched. It is a
805 reference-to-hash whose keys are mostly the fields of the reserves
806 table in the Koha database.
811 my ( $item, $barcode ) = @_;
812 my $dbh = C4::Context->dbh;
815 SELECT items.biblionumber,
816 items.biblioitemnumber,
817 itemtypes.notforloan,
818 items.notforloan AS itemnotforloan,
821 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
822 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
826 $sth = $dbh->prepare("$select WHERE itemnumber = ?");
827 $sth->execute($item);
830 $sth = $dbh->prepare("$select WHERE barcode = ?");
831 $sth->execute($barcode);
833 # note: we get the itemnumber because we might have started w/ just the barcode. Now we know for sure we have it.
834 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item, $itemnumber ) = $sth->fetchrow_array;
836 return ( 0, 0 ) unless $itemnumber; # bail if we got nothing.
838 # if item is not for loan it cannot be reserved either.....
839 # execpt where items.notforloan < 0 : This indicates the item is holdable.
840 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
842 # Find this item in the reserves
843 my @reserves = _Findgroupreserve( $bibitem, $biblio, $itemnumber );
845 # $priority and $highest are used to find the most important item
846 # in the list returned by &_Findgroupreserve. (The lower $priority,
847 # the more important the item.)
848 # $highest is the most important item we've seen so far.
850 if (scalar @reserves) {
851 my $priority = 10000000;
852 foreach my $res (@reserves) {
853 if ( $res->{'itemnumber'} == $itemnumber && $res->{'priority'} == 0) {
854 return ( "Waiting", $res ); # Found it
856 # See if this item is more important than what we've got so far
857 if ( $res->{'priority'} && $res->{'priority'} < $priority ) {
858 $priority = $res->{'priority'};
865 # If we get this far, then no exact match was found.
866 # We return the most important (i.e. next) reservation.
868 $highest->{'itemnumber'} = $item;
869 return ( "Reserved", $highest );
876 =item CancelExpiredReserves
878 CancelExpiredReserves();
880 Cancels all reserves with an expiration date from before today.
884 sub CancelExpiredReserves {
886 my $dbh = C4::Context->dbh;
887 my $sth = $dbh->prepare( "
888 SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
889 AND expirationdate IS NOT NULL
893 while ( my $res = $sth->fetchrow_hashref() ) {
894 CancelReserve( $res->{'biblionumber'}, '', $res->{'borrowernumber'} );
901 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
905 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
906 cancel, but not both: if both are given, C<&CancelReserve> does
909 C<$borrowernumber> is the borrower number of the patron on whose
910 behalf the book was reserved.
912 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
913 priorities of the other people who are waiting on the book.
918 my ( $biblio, $item, $borr ) = @_;
919 my $dbh = C4::Context->dbh;
920 if ( $item and $borr ) {
921 # removing a waiting reserve record....
922 # update the database...
925 SET cancellationdate = now(),
929 AND borrowernumber = ?
931 my $sth = $dbh->prepare($query);
932 $sth->execute( $item, $borr );
935 INSERT INTO old_reserves
936 SELECT * FROM reserves
938 AND borrowernumber = ?
940 $sth = $dbh->prepare($query);
941 $sth->execute( $item, $borr );
945 AND borrowernumber = ?
947 $sth = $dbh->prepare($query);
948 $sth->execute( $item, $borr );
951 # removing a reserve record....
952 # get the prioritiy on this record....
955 SELECT priority FROM reserves
956 WHERE biblionumber = ?
957 AND borrowernumber = ?
958 AND cancellationdate IS NULL
959 AND itemnumber IS NULL
961 my $sth = $dbh->prepare($query);
962 $sth->execute( $biblio, $borr );
963 ($priority) = $sth->fetchrow_array;
967 SET cancellationdate = now(),
970 WHERE biblionumber = ?
971 AND borrowernumber = ?
974 # update the database, removing the record...
975 $sth = $dbh->prepare($query);
976 $sth->execute( $biblio, $borr );
980 INSERT INTO old_reserves
981 SELECT * FROM reserves
982 WHERE biblionumber = ?
983 AND borrowernumber = ?
985 $sth = $dbh->prepare($query);
986 $sth->execute( $biblio, $borr );
990 WHERE biblionumber = ?
991 AND borrowernumber = ?
993 $sth = $dbh->prepare($query);
994 $sth->execute( $biblio, $borr );
996 # now fix the priority on the others....
997 _FixPriority( $priority, $biblio );
1005 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
1009 Change a hold request's priority or cancel it.
1011 C<$rank> specifies the effect of the change. If C<$rank>
1012 is 'W' or 'n', nothing happens. This corresponds to leaving a
1013 request alone when changing its priority in the holds queue
1016 If C<$rank> is 'del', the hold request is cancelled.
1018 If C<$rank> is an integer greater than zero, the priority of
1019 the request is set to that value. Since priority != 0 means
1020 that the item is not waiting on the hold shelf, setting the
1021 priority to a non-zero value also sets the request's found
1022 status and waiting date to NULL.
1024 The optional C<$itemnumber> parameter is used only when
1025 C<$rank> is a non-zero integer; if supplied, the itemnumber
1026 of the hold request is set accordingly; if omitted, the itemnumber
1029 FIXME: Note that the forgoing can have the effect of causing
1030 item-level hold requests to turn into title-level requests. This
1031 will be fixed once reserves has separate columns for requested
1032 itemnumber and supplying itemnumber.
1037 #subroutine to update a reserve
1038 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1039 return if $rank eq "W";
1040 return if $rank eq "n";
1041 my $dbh = C4::Context->dbh;
1042 if ( $rank eq "del" ) {
1045 SET cancellationdate=now()
1046 WHERE biblionumber = ?
1047 AND borrowernumber = ?
1049 my $sth = $dbh->prepare($query);
1050 $sth->execute( $biblio, $borrower );
1053 INSERT INTO old_reserves
1056 WHERE biblionumber = ?
1057 AND borrowernumber = ?
1059 $sth = $dbh->prepare($query);
1060 $sth->execute( $biblio, $borrower );
1062 DELETE FROM reserves
1063 WHERE biblionumber = ?
1064 AND borrowernumber = ?
1066 $sth = $dbh->prepare($query);
1067 $sth->execute( $biblio, $borrower );
1070 elsif ($rank =~ /^\d+/ and $rank > 0) {
1072 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1073 WHERE biblionumber = ?
1074 AND borrowernumber = ?
1076 my $sth = $dbh->prepare($query);
1077 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1079 _FixPriority( $biblio, $borrower, $rank);
1083 =item ModReserveFill
1085 &ModReserveFill($reserve);
1087 Fill a reserve. If I understand this correctly, this means that the
1088 reserved book has been found and given to the patron who reserved it.
1090 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1091 whose keys are fields from the reserves table in the Koha database.
1095 sub ModReserveFill {
1097 my $dbh = C4::Context->dbh;
1098 # fill in a reserve record....
1099 my $biblionumber = $res->{'biblionumber'};
1100 my $borrowernumber = $res->{'borrowernumber'};
1101 my $resdate = $res->{'reservedate'};
1103 # get the priority on this record....
1105 my $query = "SELECT priority
1107 WHERE biblionumber = ?
1108 AND borrowernumber = ?
1109 AND reservedate = ?";
1110 my $sth = $dbh->prepare($query);
1111 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1112 ($priority) = $sth->fetchrow_array;
1115 # update the database...
1116 $query = "UPDATE reserves
1119 WHERE biblionumber = ?
1121 AND borrowernumber = ?
1123 $sth = $dbh->prepare($query);
1124 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1127 # move to old_reserves
1128 $query = "INSERT INTO old_reserves
1129 SELECT * FROM reserves
1130 WHERE biblionumber = ?
1132 AND borrowernumber = ?
1134 $sth = $dbh->prepare($query);
1135 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1136 $query = "DELETE FROM reserves
1137 WHERE biblionumber = ?
1139 AND borrowernumber = ?
1141 $sth = $dbh->prepare($query);
1142 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1144 # now fix the priority on the others (if the priority wasn't
1145 # already sorted!)....
1146 unless ( $priority == 0 ) {
1147 _FixPriority( $priority, $biblionumber );
1151 =item ModReserveStatus
1153 &ModReserveStatus($itemnumber, $newstatus);
1155 Update the reserve status for the active (priority=0) reserve.
1157 $itemnumber is the itemnumber the reserve is on
1159 $newstatus is the new status.
1163 sub ModReserveStatus {
1165 #first : check if we have a reservation for this item .
1166 my ($itemnumber, $newstatus) = @_;
1167 my $dbh = C4::Context->dbh;
1168 my $query = " UPDATE reserves
1169 SET found=?,waitingdate = now()
1174 my $sth_set = $dbh->prepare($query);
1175 $sth_set->execute( $newstatus, $itemnumber );
1177 if ( C4::Context->preference("ReturnToShelvingCart") && $newstatus ) {
1178 CartToShelf( $itemnumber );
1182 =item ModReserveAffect
1184 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1186 This function affect an item and a status for a given reserve
1187 The itemnumber parameter is used to find the biblionumber.
1188 with the biblionumber & the borrowernumber, we can affect the itemnumber
1189 to the correct reserve.
1191 if $transferToDo is not set, then the status is set to "Waiting" as well.
1192 otherwise, a transfer is on the way, and the end of the transfer will
1193 take care of the waiting status
1196 sub ModReserveAffect {
1197 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1198 my $dbh = C4::Context->dbh;
1200 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1201 # attached to $itemnumber
1202 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1203 $sth->execute($itemnumber);
1204 my ($biblionumber) = $sth->fetchrow;
1206 # get request - need to find out if item is already
1207 # waiting in order to not send duplicate hold filled notifications
1208 my $request = GetReserveInfo($borrowernumber, $biblionumber);
1209 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1211 # If we affect a reserve that has to be transfered, don't set to Waiting
1213 if ($transferToDo) {
1218 WHERE borrowernumber = ?
1219 AND biblionumber = ?
1223 # affect the reserve to Waiting as well.
1230 WHERE borrowernumber = ?
1231 AND biblionumber = ?
1234 $sth = $dbh->prepare($query);
1235 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1236 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1238 if ( C4::Context->preference("ReturnToShelvingCart") ) {
1239 CartToShelf( $itemnumber );
1245 =item ModReserveCancelAll
1247 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1249 function to cancel reserv,check other reserves, and transfer document if it's necessary
1253 sub ModReserveCancelAll {
1256 my ( $itemnumber, $borrowernumber ) = @_;
1258 #step 1 : cancel the reservation
1259 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1261 #step 2 launch the subroutine of the others reserves
1262 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1264 return ( $messages, $nextreservinfo );
1267 =item ModReserveMinusPriority
1269 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1271 Reduce the values of queuded list
1275 sub ModReserveMinusPriority {
1276 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1278 #first step update the value of the first person on reserv
1279 my $dbh = C4::Context->dbh;
1282 SET priority = 0 , itemnumber = ?
1283 WHERE borrowernumber=?
1286 my $sth_upd = $dbh->prepare($query);
1287 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1288 # second step update all others reservs
1289 _FixPriority($biblionumber, $borrowernumber, '0');
1292 =item GetReserveInfo
1294 &GetReserveInfo($borrowernumber,$biblionumber);
1296 Get item and borrower details for a current hold.
1297 Current implementation this query should have a single result.
1300 sub GetReserveInfo {
1301 my ( $borrowernumber, $biblionumber ) = @_;
1302 my $dbh = C4::Context->dbh;
1306 reserves.borrowernumber,
1307 reserves.biblionumber,
1308 reserves.branchcode,
1309 reserves.waitingdate,
1325 items.holdingbranch,
1326 items.itemcallnumber,
1332 LEFT JOIN items USING(itemnumber)
1333 LEFT JOIN borrowers USING(borrowernumber)
1334 LEFT JOIN biblio ON (reserves.biblionumber=biblio.biblionumber)
1336 reserves.borrowernumber=?
1337 AND reserves.biblionumber=?";
1338 my $sth = $dbh->prepare($strsth);
1339 $sth->execute($borrowernumber,$biblionumber);
1341 my $data = $sth->fetchrow_hashref;
1346 =item IsAvailableForItemLevelRequest
1350 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1354 Checks whether a given item record is available for an
1355 item-level hold request. An item is available if
1357 * it is not lost AND
1358 * it is not damaged AND
1359 * it is not withdrawn AND
1360 * does not have a not for loan value > 0
1362 Whether or not the item is currently on loan is
1363 also checked - if the AllowOnShelfHolds system preference
1364 is ON, an item can be requested even if it is currently
1365 on loan to somebody else. If the system preference
1366 is OFF, an item that is currently checked out cannot
1367 be the target of an item-level hold request.
1369 Note that IsAvailableForItemLevelRequest() does not
1370 check if the staff operator is authorized to place
1371 a request on the item - in particular,
1372 this routine does not check IndependantBranches
1373 and canreservefromotherbranches.
1377 sub IsAvailableForItemLevelRequest {
1378 my $itemnumber = shift;
1380 my $item = GetItem($itemnumber);
1382 # must check the notforloan setting of the itemtype
1383 # FIXME - a lot of places in the code do this
1384 # or something similar - need to be
1386 my $dbh = C4::Context->dbh;
1387 my $notforloan_query;
1388 if (C4::Context->preference('item-level_itypes')) {
1389 $notforloan_query = "SELECT itemtypes.notforloan
1391 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1392 WHERE itemnumber = ?";
1394 $notforloan_query = "SELECT itemtypes.notforloan
1396 JOIN biblioitems USING (biblioitemnumber)
1397 JOIN itemtypes USING (itemtype)
1398 WHERE itemnumber = ?";
1400 my $sth = $dbh->prepare($notforloan_query);
1401 $sth->execute($itemnumber);
1402 my $notforloan_per_itemtype = 0;
1403 if (my ($notforloan) = $sth->fetchrow_array) {
1404 $notforloan_per_itemtype = 1 if $notforloan;
1407 my $available_per_item = 1;
1408 $available_per_item = 0 if $item->{itemlost} or
1409 ( $item->{notforloan} > 0 ) or
1410 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1411 $item->{wthdrawn} or
1412 $notforloan_per_itemtype;
1415 if (C4::Context->preference('AllowOnShelfHolds')) {
1416 return $available_per_item;
1418 return ($available_per_item and $item->{onloan});
1423 AlterPriority( $where, $borrowernumber, $biblionumber, $reservedate );
1425 This function changes a reserve's priority up, down, to the top, or to the bottom.
1426 Input: $where is 'up', 'down', 'top' or 'bottom'. Biblionumber, Date reserve was placed
1430 my ( $where, $borrowernumber, $biblionumber ) = @_;
1432 my $dbh = C4::Context->dbh;
1434 ## Find this reserve
1435 my $sth = $dbh->prepare('SELECT * FROM reserves WHERE biblionumber = ? AND borrowernumber = ? AND cancellationdate IS NULL');
1436 $sth->execute( $biblionumber, $borrowernumber );
1437 my $reserve = $sth->fetchrow_hashref();
1440 if ( $where eq 'up' || $where eq 'down' ) {
1442 my $priority = $reserve->{'priority'};
1443 $priority = $where eq 'up' ? $priority - 1 : $priority + 1;
1444 _FixPriority( $biblionumber, $borrowernumber, $priority )
1446 } elsif ( $where eq 'top' ) {
1448 _FixPriority( $biblionumber, $borrowernumber, '1' )
1450 } elsif ( $where eq 'bottom' ) {
1452 _FixPriority( $biblionumber, $borrowernumber, '999999' )
1457 =item ToggleLowestPriority
1458 ToggleLowestPriority( $borrowernumber, $biblionumber );
1460 This function sets the lowestPriority field to true if is false, and false if it is true.
1463 sub ToggleLowestPriority {
1464 my ( $borrowernumber, $biblionumber ) = @_;
1466 my $dbh = C4::Context->dbh;
1468 my $sth = $dbh->prepare(
1469 "UPDATE reserves SET lowestPriority = NOT lowestPriority
1470 WHERE biblionumber = ?
1471 AND borrowernumber = ?"
1479 _FixPriority( $biblionumber, $borrowernumber, '999999' );
1484 &_FixPriority($biblio,$borrowernumber,$rank,$ignoreSetLowestRank);
1486 Only used internally (so don't export it)
1487 Changed how this functions works #
1488 Now just gets an array of reserves in the rank order and updates them with
1489 the array index (+1 as array starts from 0)
1490 and if $rank is supplied will splice item from the array and splice it back in again
1491 in new priority rank
1496 my ( $biblio, $borrowernumber, $rank, $ignoreSetLowestRank ) = @_;
1497 my $dbh = C4::Context->dbh;
1498 if ( $rank eq "del" ) {
1499 CancelReserve( $biblio, undef, $borrowernumber );
1501 if ( $rank eq "W" || $rank eq "0" ) {
1503 # make sure priority for waiting items is 0
1507 WHERE biblionumber = ?
1508 AND borrowernumber = ?
1511 my $sth = $dbh->prepare($query);
1512 $sth->execute( $biblio, $borrowernumber );
1518 # FIXME adding a new security in returned elements for changing priority,
1519 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1520 # This is wrong a waiting reserve has W set
1521 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1523 SELECT borrowernumber, reservedate, constrainttype
1525 WHERE biblionumber = ?
1526 AND ((found <> 'W') or found is NULL)
1527 ORDER BY priority ASC
1529 my $sth = $dbh->prepare($query);
1530 $sth->execute($biblio);
1531 while ( my $line = $sth->fetchrow_hashref ) {
1532 push( @reservedates, $line );
1533 push( @priority, $line );
1536 # To find the matching index
1538 my $key = -1; # to allow for 0 to be a valid result
1539 for ( $i = 0 ; $i < @priority ; $i++ ) {
1540 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1541 $key = $i; # save the index
1546 # if index exists in array then move it to new position
1547 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1548 my $new_rank = $rank -
1549 1; # $new_rank is what you want the new index to be in the array
1550 my $moving_item = splice( @priority, $key, 1 );
1551 splice( @priority, $new_rank, 0, $moving_item );
1554 # now fix the priority on those that are left....
1558 WHERE biblionumber = ?
1559 AND borrowernumber = ?
1563 $sth = $dbh->prepare($query);
1564 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1567 $priority[$j]->{'borrowernumber'},
1568 $priority[$j]->{'reservedate'}
1573 $sth = $dbh->prepare( "SELECT borrowernumber FROM reserves WHERE lowestPriority = 1 ORDER BY priority" );
1576 unless ( $ignoreSetLowestRank ) {
1577 while ( my $res = $sth->fetchrow_hashref() ) {
1578 _FixPriority( $biblio, $res->{'borrowernumber'}, '999999', 1 );
1583 =item _Findgroupreserve
1585 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1587 Looks for an item-specific match first, then for a title-level match, returning the
1588 first match found. If neither, then we look for a 3rd kind of match based on
1589 reserve constraints.
1591 TODO: add more explanation about reserve constraints
1593 C<&_Findgroupreserve> returns :
1594 C<@results> is an array of references-to-hash whose keys are mostly
1595 fields from the reserves table of the Koha database, plus
1596 C<biblioitemnumber>.
1600 sub _Findgroupreserve {
1601 my ( $bibitem, $biblio, $itemnumber ) = @_;
1602 my $dbh = C4::Context->dbh;
1604 # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var.
1605 # check for exact targetted match
1606 my $item_level_target_query = qq/
1607 SELECT reserves.biblionumber AS biblionumber,
1608 reserves.borrowernumber AS borrowernumber,
1609 reserves.reservedate AS reservedate,
1610 reserves.branchcode AS branchcode,
1611 reserves.cancellationdate AS cancellationdate,
1612 reserves.found AS found,
1613 reserves.reservenotes AS reservenotes,
1614 reserves.priority AS priority,
1615 reserves.timestamp AS timestamp,
1616 biblioitems.biblioitemnumber AS biblioitemnumber,
1617 reserves.itemnumber AS itemnumber
1619 JOIN biblioitems USING (biblionumber)
1620 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1623 AND item_level_request = 1
1625 AND reservedate <= CURRENT_DATE()
1627 my $sth = $dbh->prepare($item_level_target_query);
1628 $sth->execute($itemnumber);
1630 if ( my $data = $sth->fetchrow_hashref ) {
1631 push( @results, $data );
1633 return @results if @results;
1635 # check for title-level targetted match
1636 my $title_level_target_query = qq/
1637 SELECT reserves.biblionumber AS biblionumber,
1638 reserves.borrowernumber AS borrowernumber,
1639 reserves.reservedate AS reservedate,
1640 reserves.branchcode AS branchcode,
1641 reserves.cancellationdate AS cancellationdate,
1642 reserves.found AS found,
1643 reserves.reservenotes AS reservenotes,
1644 reserves.priority AS priority,
1645 reserves.timestamp AS timestamp,
1646 biblioitems.biblioitemnumber AS biblioitemnumber,
1647 reserves.itemnumber AS itemnumber
1649 JOIN biblioitems USING (biblionumber)
1650 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1653 AND item_level_request = 0
1654 AND hold_fill_targets.itemnumber = ?
1655 AND reservedate <= CURRENT_DATE()
1657 $sth = $dbh->prepare($title_level_target_query);
1658 $sth->execute($itemnumber);
1660 if ( my $data = $sth->fetchrow_hashref ) {
1661 push( @results, $data );
1663 return @results if @results;
1666 SELECT reserves.biblionumber AS biblionumber,
1667 reserves.borrowernumber AS borrowernumber,
1668 reserves.reservedate AS reservedate,
1669 reserves.branchcode AS branchcode,
1670 reserves.cancellationdate AS cancellationdate,
1671 reserves.found AS found,
1672 reserves.reservenotes AS reservenotes,
1673 reserves.priority AS priority,
1674 reserves.timestamp AS timestamp,
1675 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1676 reserves.itemnumber AS itemnumber
1678 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1679 WHERE reserves.biblionumber = ?
1680 AND ( ( reserveconstraints.biblioitemnumber = ?
1681 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1682 AND reserves.reservedate = reserveconstraints.reservedate )
1683 OR reserves.constrainttype='a' )
1684 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1685 AND reserves.reservedate <= CURRENT_DATE()
1687 $sth = $dbh->prepare($query);
1688 $sth->execute( $biblio, $bibitem, $itemnumber );
1690 while ( my $data = $sth->fetchrow_hashref ) {
1691 push( @results, $data );
1696 =item _koha_notify_reserve
1700 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1704 Sends a notification to the patron that their hold has been filled (through
1705 ModReserveAffect, _not_ ModReserveFill)
1709 sub _koha_notify_reserve {
1710 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1712 my $dbh = C4::Context->dbh;
1713 my $borrower = C4::Members::GetMember( $borrowernumber );
1717 if ( $borrower->{'email'} || $borrower->{'smsalertnumber'} ) {
1718 $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1720 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1721 $letter_code = $messagingprefs->{'letter_code'};
1723 $letter_code = 'HOLD_PRINT';
1727 my $sth = $dbh->prepare("
1730 WHERE borrowernumber = ?
1731 AND biblionumber = ?
1733 $sth->execute( $borrowernumber, $biblionumber );
1734 my $reserve = $sth->fetchrow_hashref;
1735 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1737 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1739 my $letter = getletter( 'reserves', $letter_code );
1740 die "Could not find a letter called '$letter_code' in the 'reserves' module" unless( $letter );
1742 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1743 C4::Letters::parseletter( $letter, 'borrowers', $borrowernumber );
1744 C4::Letters::parseletter( $letter, 'biblio', $biblionumber );
1745 C4::Letters::parseletter( $letter, 'reserves', $borrowernumber, $biblionumber );
1747 if ( $reserve->{'itemnumber'} ) {
1748 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1750 my $today = C4::Dates->new()->output();
1751 $letter->{'title'} =~ s/<<today>>/$today/g;
1752 $letter->{'content'} =~ s/<<today>>/$today/g;
1753 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1755 if ( $print_mode ) {
1756 C4::Letters::EnqueueLetter( {
1758 borrowernumber => $borrowernumber,
1759 message_transport_type => 'print',
1765 if ( grep { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1766 # aka, 'email' in ->{'transports'}
1767 C4::Letters::EnqueueLetter(
1768 { letter => $letter,
1769 borrowernumber => $borrowernumber,
1770 message_transport_type => 'email',
1771 from_address => $admin_email_address,
1776 if ( grep { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1777 C4::Letters::EnqueueLetter(
1778 { letter => $letter,
1779 borrowernumber => $borrowernumber,
1780 message_transport_type => 'sms',
1786 =item _ShiftPriorityByDateAndPriority
1790 $new_priority = _ShiftPriorityByDateAndPriority( $biblionumber, $reservedate, $priority );
1794 This increments the priority of all reserves after the one
1795 with either the lowest date after C<$reservedate>
1796 or the lowest priority after C<$priority>.
1798 It effectively makes room for a new reserve to be inserted with a certain
1799 priority, which is returned.
1801 This is most useful when the reservedate can be set by the user. It allows
1802 the new reserve to be placed before other reserves that have a later
1803 reservedate. Since priority also is set by the form in reserves/request.pl
1804 the sub accounts for that too.
1808 sub _ShiftPriorityByDateAndPriority {
1809 my ( $biblio, $resdate, $new_priority ) = @_;
1811 my $dbh = C4::Context->dbh;
1812 my $query = "SELECT priority FROM reserves WHERE biblionumber = ? AND ( reservedate > ? OR priority > ? ) ORDER BY priority ASC LIMIT 1";
1813 my $sth = $dbh->prepare( $query );
1814 $sth->execute( $biblio, $resdate, $new_priority );
1815 my $min_priority = $sth->fetchrow;
1816 # if no such matches are found, $new_priority remains as original value
1817 $new_priority = $min_priority if ( $min_priority );
1819 # Shift the priority up by one; works in conjunction with the next SQL statement
1820 $query = "UPDATE reserves
1821 SET priority = priority+1
1822 WHERE biblionumber = ?
1823 AND borrowernumber = ?
1826 my $sth_update = $dbh->prepare( $query );
1828 # Select all reserves for the biblio with priority greater than $new_priority, and order greatest to least
1829 $query = "SELECT borrowernumber, reservedate FROM reserves WHERE priority >= ? AND biblionumber = ? ORDER BY priority DESC";
1830 $sth = $dbh->prepare( $query );
1831 $sth->execute( $new_priority, $biblio );
1832 while ( my $row = $sth->fetchrow_hashref ) {
1833 $sth_update->execute( $biblio, $row->{borrowernumber}, $row->{reservedate} );
1836 return $new_priority; # so the caller knows what priority they wind up receiving
1843 Koha Developement team <info@koha.org>