2 # NOTE: This file uses standard 8-character tabs
6 # Copyright 2000-2002 Katipo Communications
7 # 2006 SAN Ouest Provence
8 # 2007 BibLibre Paul POULAIN
10 # This file is part of Koha.
12 # Koha is free software; you can redistribute it and/or modify it under the
13 # terms of the GNU General Public License as published by the Free Software
14 # Foundation; either version 2 of the License, or (at your option) any later
17 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
18 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
19 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
21 # You should have received a copy of the GNU General Public License along with
22 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
23 # Suite 330, Boston, MA 02111-1307 USA
34 # for _koha_notify_reserve
35 use C4::Members::Messaging;
36 use C4::Members qw( GetMember );
38 use C4::Branch qw( GetBranchDetail );
39 use List::MoreUtils qw( firstidx );
41 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
43 my $library_name = C4::Context->preference("LibraryName");
47 C4::Reserves - Koha functions for dealing with reservation.
55 this modules provides somes functions to deal with reservations.
57 Reserves are stored in reserves table.
58 The following columns contains important values :
59 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
60 =0 : then the reserve is being dealed
61 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
62 W(aiting) : the reserve has an itemnumber affected, and is on the way
63 F(inished) : the reserve has been completed, and is done
64 - itemnumber : empty : the reserve is still unaffected to an item
65 filled: the reserve is attached to an item
66 The complete workflow is :
67 ==== 1st use case ====
68 patron request a document, 1st available : P >0, F=NULL, I=NULL
69 a library having it run "transfertodo", and clic on the list
70 if there is no transfer to do, the reserve waiting
71 patron can pick it up P =0, F=W, I=filled
72 if there is a transfer to do, write in branchtransfer P =0, F=NULL, I=filled
73 The pickup library recieve the book, it check in P =0, F=W, I=filled
74 The patron borrow the book P =0, F=F, I=filled
76 ==== 2nd use case ====
77 patron requests a document, a given item,
78 If pickup is holding branch P =0, F=W, I=filled
79 If transfer needed, write in branchtransfer P =0, F=NULL, I=filled
80 The pickup library recieve the book, it checks it in P =0, F=W, I=filled
81 The patron borrow the book P =0, F=F, I=filled
90 # set the version for version checking
97 &GetReservesFromItemnumber
98 &GetReservesFromBiblionumber
99 &GetReservesFromBorrowernumber
100 &GetReservesForBranch
113 &ModReserveMinusPriority
120 &IsAvailableForItemLevelRequest
126 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$notes,$title,$checkitem,$found)
132 $branch, $borrowernumber, $biblionumber,
133 $constraint, $bibitems, $priority, $notes,
134 $title, $checkitem, $found
137 GetReserveFee($borrowernumber, $biblionumber, $constraint,
139 my $dbh = C4::Context->dbh;
140 my $const = lc substr( $constraint, 0, 1 );
141 my @datearr = localtime(time);
143 ( 1900 + $datearr[5] ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
146 # If the reserv had the waiting status, we had the value of the resdate
147 if ( $found eq 'W' ) {
148 $waitingdate = $resdate;
152 # updates take place here
154 my $nextacctno = &getnextacctno( $borrowernumber );
156 INSERT INTO accountlines
157 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
159 (?,?,now(),?,?,'Res',?)
161 my $usth = $dbh->prepare($query);
162 $usth->execute( $borrowernumber, $nextacctno, $fee,
163 "Reserve Charge - $title", $fee );
169 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,
170 priority,reservenotes,itemnumber,found,waitingdate)
175 my $sth = $dbh->prepare($query);
177 $borrowernumber, $biblionumber, $resdate, $branch,
178 $const, $priority, $notes, $checkitem,
183 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
185 INSERT INTO reserveconstraints
186 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
190 $sth = $dbh->prepare($query); # keep prepare outside the loop!
191 foreach (@$bibitems) {
192 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
194 return; # FIXME: why not have a useful return value?
197 =item GetReservesFromBiblionumber
199 @borrowerreserv=&GetReserves($biblionumber,$itemnumber,$borrowernumber);
201 this function get the list of reservation for an C<$biblionumber>, C<$itemnumber> or C<$borrowernumber>
203 Only 1 argument has to be passed.
207 sub GetReservesFromBiblionumber {
208 my ( $biblionumber, $itemnumber, $borrowernumber ) = @_;
209 my $dbh = C4::Context->dbh;
211 # Find the desired items in the reserves
214 timestamp AS rtimestamp,
224 WHERE biblionumber = ?
226 my $sth = $dbh->prepare($query);
227 $sth->execute($biblionumber);
230 while ( my $data = $sth->fetchrow_hashref ) {
232 # FIXME - What is this doing? How do constraints work?
233 if ($data->{constrainttype} eq 'o') {
235 SELECT biblioitemnumber
236 FROM reserveconstraints
237 WHERE biblionumber = ?
238 AND borrowernumber = ?
241 my $csth = $dbh->prepare($query);
242 $csth->execute( $data->{biblionumber}, $data->{borrowernumber},
243 $data->{reservedate}, );
246 while ( my $bibitemnos = $csth->fetchrow_array ) {
247 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
249 my $count = scalar @bibitemno;
251 # if we have two or more different specific itemtypes
252 # reserved by same person on same day
255 $bdata = GetBiblioItemData( $bibitemno[$i] );
259 # Look up the book we just found.
260 $bdata = GetBiblioItemData( $bibitemno[0] );
262 # Add the results of this latest search to the current
264 # FIXME - An 'each' would probably be more efficient.
265 foreach my $key ( keys %$bdata ) {
266 $data->{$key} = $bdata->{$key};
269 push @results, $data;
271 return ( $#results + 1, \@results );
274 =item GetReservesFromItemnumber
276 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
278 TODO :: Description here
282 sub GetReservesFromItemnumber {
283 my ( $itemnumber ) = @_;
284 my $dbh = C4::Context->dbh;
286 SELECT reservedate,borrowernumber,branchcode
290 my $sth_res = $dbh->prepare($query);
291 $sth_res->execute($itemnumber);
292 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
293 return ( $reservedate, $borrowernumber, $branchcode );
296 =item GetReservesFromBorrowernumber
298 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
304 sub GetReservesFromBorrowernumber {
305 my ( $borrowernumber, $status ) = @_;
306 my $dbh = C4::Context->dbh;
309 $sth = $dbh->prepare("
312 WHERE borrowernumber=?
316 $sth->execute($borrowernumber,$status);
318 $sth = $dbh->prepare("
321 WHERE borrowernumber=?
324 $sth->execute($borrowernumber);
326 my $data = $sth->fetchall_arrayref({});
329 #-------------------------------------------------------------------------------------
331 =item CanBookBeReserved
333 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
337 sub CanBookBeReserved{
338 my ($borrowernumber, $biblionumber) = @_;
340 my $dbh = C4::Context->dbh;
341 my $biblio = GetBiblioData($biblionumber);
342 my $borrower = C4::Members::GetMember($borrowernumber);
343 my $controlbranch = C4::Context->preference('ReservesControlBranch');
344 my $itype = C4::Context->preference('item-level_itypes');
345 my $reservesrights= 0;
346 my $reservescount = 0;
348 # we retrieve the user rights
350 my $rightsquery = "SELECT categorycode, itemtype, branchcode, reservesallowed
352 WHERE categorycode = ?";
353 push @args,$borrower->{categorycode};
355 if($controlbranch eq "ItemHomeLibrary"){
356 $rightsquery .= " AND branchcode = '*'";
357 }elsif($controlbranch eq "PatronLibrary"){
358 $rightsquery .= " AND branchcode IN (?,'*')";
359 push @args, $borrower->{branchcode};
363 $rightsquery .= " AND itemtype IN (?,'*')";
364 push @args, $biblio->{itemtype};
366 $rightsquery .= " AND itemtype = '*'";
369 $rightsquery .= " ORDER BY categorycode DESC, itemtype DESC, branchcode DESC";
371 my $sthrights = $dbh->prepare($rightsquery);
372 $sthrights->execute(@args);
374 if(my $row = $sthrights->fetchrow_hashref()){
375 $reservesrights = $row->{reservesallowed};
379 # we count how many reserves the borrower have
380 my $countquery = "SELECT count(*) as count
382 LEFT JOIN items USING (itemnumber)
383 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
384 LEFT JOIN borrowers USING (borrowernumber)
385 WHERE borrowernumber = ?
387 push @args, $borrowernumber;
390 $countquery .= "AND itemtype = ?";
391 push @args, $biblio->{itemtype};
394 if($controlbranch eq "PatronLibrary"){
395 $countquery .= " AND borrowers.branchcode = ? ";
396 push @args, $borrower->{branchcode};
399 my $sthcount = $dbh->prepare($countquery);
400 $sthcount->execute(@args);
402 if(my $row = $sthcount->fetchrow_hashref()){
403 $reservescount = $row->{count};
406 if($reservescount < $reservesrights){
414 =item CanItemBeReserved
416 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
418 this function return 1 if an item can be issued by this borrower.
422 sub CanItemBeReserved{
423 my ($borrowernumber, $itemnumber) = @_;
425 my $dbh = C4::Context->dbh;
426 my $allowedreserves = 0;
428 my $controlbranch = C4::Context->preference('ReservesControlBranch');
429 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
431 # we retrieve borrowers and items informations #
432 my $item = GetItem($itemnumber);
433 my $borrower = C4::Members::GetMember($borrowernumber);
435 # we retrieve user rights on this itemtype and branchcode
436 my $sth = $dbh->prepare("SELECT categorycode, itemtype, branchcode, reservesallowed
438 WHERE (categorycode in (?,'*') )
439 AND (itemtype IN (?,'*'))
440 AND (branchcode IN (?,'*'))
447 my $querycount ="SELECT
450 LEFT JOIN items USING (itemnumber)
451 LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
452 LEFT JOIN borrowers USING (borrowernumber)
453 WHERE borrowernumber = ?
457 my $itemtype = $item->{$itype};
458 my $categorycode = $borrower->{categorycode};
460 my $branchfield = "reserves.branchcode";
462 if( $controlbranch eq "ItemHomeLibrary" ){
463 $branchfield = "items.homebranch";
464 $branchcode = $item->{homebranch};
465 }elsif( $controlbranch eq "PatronLibrary" ){
466 $branchfield = "borrowers.branchcode";
467 $branchcode = $borrower->{branchcode};
471 $sth->execute($categorycode, $itemtype, $branchcode);
472 if(my $rights = $sth->fetchrow_hashref()){
473 $itemtype = $rights->{itemtype};
474 $allowedreserves = $rights->{reservesallowed};
481 $querycount .= "AND $branchfield = ?";
483 $querycount .= " AND $itype = ?" if ($itemtype ne "*");
484 my $sthcount = $dbh->prepare($querycount);
486 if($itemtype eq "*"){
487 $sthcount->execute($borrowernumber, $branchcode);
489 $sthcount->execute($borrowernumber, $branchcode, $itemtype);
492 my $reservecount = "0";
493 if(my $rowcount = $sthcount->fetchrow_hashref()){
494 $reservecount = $rowcount->{count};
497 # we check if it's ok or not
498 if( $reservecount < $allowedreserves ){
505 =item GetReserveCount
507 $number = &GetReserveCount($borrowernumber);
509 this function returns the number of reservation for a borrower given on input arg.
513 sub GetReserveCount {
514 my ($borrowernumber) = @_;
516 my $dbh = C4::Context->dbh;
519 SELECT COUNT(*) AS counter
521 WHERE borrowernumber = ?
523 my $sth = $dbh->prepare($query);
524 $sth->execute($borrowernumber);
525 my $row = $sth->fetchrow_hashref;
526 return $row->{counter};
529 =item GetOtherReserves
531 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
533 Check queued list of this document and check if this document must be transfered
537 sub GetOtherReserves {
538 my ($itemnumber) = @_;
541 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
542 if ($checkreserves) {
543 my $iteminfo = GetItem($itemnumber);
544 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
545 $messages->{'transfert'} = $checkreserves->{'branchcode'};
546 #minus priorities of others reservs
547 ModReserveMinusPriority(
549 $checkreserves->{'borrowernumber'},
550 $iteminfo->{'biblionumber'}
553 #launch the subroutine dotransfer
554 C4::Items::ModItemTransfer(
556 $iteminfo->{'holdingbranch'},
557 $checkreserves->{'branchcode'}
562 #step 2b : case of a reservation on the same branch, set the waiting status
564 $messages->{'waiting'} = 1;
565 ModReserveMinusPriority(
567 $checkreserves->{'borrowernumber'},
568 $iteminfo->{'biblionumber'}
570 ModReserveStatus($itemnumber,'W');
573 $nextreservinfo = $checkreserves->{'borrowernumber'};
576 return ( $messages, $nextreservinfo );
581 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
583 Calculate the fee for a reserve
588 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
591 my $dbh = C4::Context->dbh;
592 my $const = lc substr( $constraint, 0, 1 );
594 SELECT * FROM borrowers
595 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
596 WHERE borrowernumber = ?
598 my $sth = $dbh->prepare($query);
599 $sth->execute($borrowernumber);
600 my $data = $sth->fetchrow_hashref;
602 my $fee = $data->{'reservefee'};
603 my $cntitems = @- > $bibitems;
607 # check for items on issue
608 # first find biblioitem records
610 my $sth1 = $dbh->prepare(
611 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
612 WHERE (biblio.biblionumber = ?)"
614 $sth1->execute($biblionumber);
615 while ( my $data1 = $sth1->fetchrow_hashref ) {
616 if ( $const eq "a" ) {
617 push @biblioitems, $data1;
622 while ( $x < $cntitems ) {
623 if ( @$bibitems->{'biblioitemnumber'} ==
624 $data->{'biblioitemnumber'} )
630 if ( $const eq 'o' ) {
632 push @biblioitems, $data1;
637 push @biblioitems, $data1;
643 my $cntitemsfound = @biblioitems;
647 while ( $x < $cntitemsfound ) {
648 my $bitdata = $biblioitems[$x];
649 my $sth2 = $dbh->prepare(
651 WHERE biblioitemnumber = ?"
653 $sth2->execute( $bitdata->{'biblioitemnumber'} );
654 while ( my $itdata = $sth2->fetchrow_hashref ) {
655 my $sth3 = $dbh->prepare(
656 "SELECT * FROM issues
657 WHERE itemnumber = ?"
659 $sth3->execute( $itdata->{'itemnumber'} );
660 if ( my $isdata = $sth3->fetchrow_hashref ) {
668 if ( $allissued == 0 ) {
670 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
671 $rsth->execute($biblionumber);
672 if ( my $rdata = $rsth->fetchrow_hashref ) {
682 =item GetReservesToBranch
684 @transreserv = GetReservesToBranch( $frombranch );
686 Get reserve list for a given branch
690 sub GetReservesToBranch {
691 my ( $frombranch ) = @_;
692 my $dbh = C4::Context->dbh;
693 my $sth = $dbh->prepare(
694 "SELECT borrowernumber,reservedate,itemnumber,timestamp
699 $sth->execute( $frombranch );
702 while ( my $data = $sth->fetchrow_hashref ) {
703 $transreserv[$i] = $data;
706 return (@transreserv);
709 =item GetReservesForBranch
711 @transreserv = GetReservesForBranch($frombranch);
715 sub GetReservesForBranch {
716 my ($frombranch) = @_;
717 my $dbh = C4::Context->dbh;
718 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
723 $query .= " AND branchcode=? ";
725 $query .= "ORDER BY waitingdate" ;
726 my $sth = $dbh->prepare($query);
728 $sth->execute($frombranch);
735 while ( my $data = $sth->fetchrow_hashref ) {
736 $transreserv[$i] = $data;
739 return (@transreserv);
744 ($status, $reserve) = &CheckReserves($itemnumber);
746 Find a book in the reserves.
748 C<$itemnumber> is the book's item number.
750 As I understand it, C<&CheckReserves> looks for the given item in the
751 reserves. If it is found, that's a match, and C<$status> is set to
754 Otherwise, it finds the most important item in the reserves with the
755 same biblio number as this book (I'm not clear on this) and returns it
756 with C<$status> set to C<Reserved>.
758 C<&CheckReserves> returns a two-element list:
760 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
762 C<$reserve> is the reserve item that matched. It is a
763 reference-to-hash whose keys are mostly the fields of the reserves
764 table in the Koha database.
769 my ( $item, $barcode ) = @_;
770 my $dbh = C4::Context->dbh;
773 my $qitem = $dbh->quote($item);
774 # Look up the item by itemnumber
776 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
778 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
779 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
780 WHERE itemnumber=$qitem
782 $sth = $dbh->prepare($query);
785 my $qbc = $dbh->quote($barcode);
786 # Look up the item by barcode
788 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
790 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
791 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
792 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
793 AND biblioitems.itemtype = itemtypes.itemtype
796 $sth = $dbh->prepare($query);
798 # FIXME - This function uses $item later on. Ought to set it here.
801 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item ) = $sth->fetchrow_array;
803 # if item is not for loan it cannot be reserved either.....
804 # execption to notforloan is where items.notforloan < 0 : This indicates the item is holdable.
805 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
807 # get the reserves...
808 # Find this item in the reserves
809 my @reserves = _Findgroupreserve( $bibitem, $biblio, $item );
810 my $count = scalar @reserves;
812 # $priority and $highest are used to find the most important item
813 # in the list returned by &_Findgroupreserve. (The lower $priority,
814 # the more important the item.)
815 # $highest is the most important item we've seen so far.
816 my $priority = 10000000;
819 foreach my $res (@reserves) {
820 # FIXME - $item might be undefined or empty: the caller
821 # might be searching by barcode.
822 if ( $res->{'itemnumber'} == $item && $res->{'priority'} == 0) {
824 return ( "Waiting", $res );
827 # See if this item is more important than what we've got
829 if ( $res->{'priority'} != 0 && $res->{'priority'} < $priority )
831 $priority = $res->{'priority'};
838 # If we get this far, then no exact match was found. Print the
839 # most important item on the list. I think this tells us who's
840 # next in line to get this book.
841 if ($highest) { # FIXME - $highest might be undefined
842 $highest->{'itemnumber'} = $item;
843 return ( "Reserved", $highest );
852 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
856 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
857 cancel, but not both: if both are given, C<&CancelReserve> does
860 C<$borrowernumber> is the borrower number of the patron on whose
861 behalf the book was reserved.
863 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
864 priorities of the other people who are waiting on the book.
869 my ( $biblio, $item, $borr ) = @_;
870 my $dbh = C4::Context->dbh;
871 if ( $item and $borr ) {
872 # removing a waiting reserve record....
873 # update the database...
876 SET cancellationdate = now(),
880 AND borrowernumber = ?
882 my $sth = $dbh->prepare($query);
883 $sth->execute( $item, $borr );
886 INSERT INTO old_reserves
887 SELECT * FROM reserves
889 AND borrowernumber = ?
891 $sth = $dbh->prepare($query);
892 $sth->execute( $item, $borr );
896 AND borrowernumber = ?
898 $sth = $dbh->prepare($query);
899 $sth->execute( $item, $borr );
902 # removing a reserve record....
903 # get the prioritiy on this record....
906 SELECT priority FROM reserves
907 WHERE biblionumber = ?
908 AND borrowernumber = ?
909 AND cancellationdate IS NULL
910 AND itemnumber IS NULL
912 my $sth = $dbh->prepare($query);
913 $sth->execute( $biblio, $borr );
914 ($priority) = $sth->fetchrow_array;
918 SET cancellationdate = now(),
921 WHERE biblionumber = ?
922 AND borrowernumber = ?
925 # update the database, removing the record...
926 $sth = $dbh->prepare($query);
927 $sth->execute( $biblio, $borr );
931 INSERT INTO old_reserves
932 SELECT * FROM reserves
933 WHERE biblionumber = ?
934 AND borrowernumber = ?
936 $sth = $dbh->prepare($query);
937 $sth->execute( $biblio, $borr );
941 WHERE biblionumber = ?
942 AND borrowernumber = ?
944 $sth = $dbh->prepare($query);
945 $sth->execute( $biblio, $borr );
947 # now fix the priority on the others....
948 _FixPriority( $priority, $biblio );
956 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
960 Change a hold request's priority or cancel it.
962 C<$rank> specifies the effect of the change. If C<$rank>
963 is 'W' or 'n', nothing happens. This corresponds to leaving a
964 request alone when changing its priority in the holds queue
967 If C<$rank> is 'del', the hold request is cancelled.
969 If C<$rank> is an integer greater than zero, the priority of
970 the request is set to that value. Since priority != 0 means
971 that the item is not waiting on the hold shelf, setting the
972 priority to a non-zero value also sets the request's found
973 status and waiting date to NULL.
975 The optional C<$itemnumber> parameter is used only when
976 C<$rank> is a non-zero integer; if supplied, the itemnumber
977 of the hold request is set accordingly; if omitted, the itemnumber
980 FIXME: Note that the forgoing can have the effect of causing
981 item-level hold requests to turn into title-level requests. This
982 will be fixed once reserves has separate columns for requested
983 itemnumber and supplying itemnumber.
988 #subroutine to update a reserve
989 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
990 return if $rank eq "W";
991 return if $rank eq "n";
992 my $dbh = C4::Context->dbh;
993 if ( $rank eq "del" ) {
996 SET cancellationdate=now()
997 WHERE biblionumber = ?
998 AND borrowernumber = ?
1000 my $sth = $dbh->prepare($query);
1001 $sth->execute( $biblio, $borrower );
1004 INSERT INTO old_reserves
1007 WHERE biblionumber = ?
1008 AND borrowernumber = ?
1010 $sth = $dbh->prepare($query);
1011 $sth->execute( $biblio, $borrower );
1013 DELETE FROM reserves
1014 WHERE biblionumber = ?
1015 AND borrowernumber = ?
1017 $sth = $dbh->prepare($query);
1018 $sth->execute( $biblio, $borrower );
1021 elsif ($rank =~ /^\d+/ and $rank > 0) {
1023 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1024 WHERE biblionumber = ?
1025 AND borrowernumber = ?
1027 my $sth = $dbh->prepare($query);
1028 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1030 _FixPriority( $biblio, $borrower, $rank);
1034 =item ModReserveFill
1036 &ModReserveFill($reserve);
1038 Fill a reserve. If I understand this correctly, this means that the
1039 reserved book has been found and given to the patron who reserved it.
1041 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1042 whose keys are fields from the reserves table in the Koha database.
1046 sub ModReserveFill {
1048 my $dbh = C4::Context->dbh;
1049 # fill in a reserve record....
1050 my $biblionumber = $res->{'biblionumber'};
1051 my $borrowernumber = $res->{'borrowernumber'};
1052 my $resdate = $res->{'reservedate'};
1054 # get the priority on this record....
1056 my $query = "SELECT priority
1058 WHERE biblionumber = ?
1059 AND borrowernumber = ?
1060 AND reservedate = ?";
1061 my $sth = $dbh->prepare($query);
1062 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1063 ($priority) = $sth->fetchrow_array;
1066 # update the database...
1067 $query = "UPDATE reserves
1070 WHERE biblionumber = ?
1072 AND borrowernumber = ?
1074 $sth = $dbh->prepare($query);
1075 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1078 # move to old_reserves
1079 $query = "INSERT INTO old_reserves
1080 SELECT * FROM reserves
1081 WHERE biblionumber = ?
1083 AND borrowernumber = ?
1085 $sth = $dbh->prepare($query);
1086 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1087 $query = "DELETE FROM reserves
1088 WHERE biblionumber = ?
1090 AND borrowernumber = ?
1092 $sth = $dbh->prepare($query);
1093 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1095 # now fix the priority on the others (if the priority wasn't
1096 # already sorted!)....
1097 unless ( $priority == 0 ) {
1098 _FixPriority( $priority, $biblionumber );
1102 =item ModReserveStatus
1104 &ModReserveStatus($itemnumber, $newstatus);
1106 Update the reserve status for the active (priority=0) reserve.
1108 $itemnumber is the itemnumber the reserve is on
1110 $newstatus is the new status.
1114 sub ModReserveStatus {
1116 #first : check if we have a reservation for this item .
1117 my ($itemnumber, $newstatus) = @_;
1118 my $dbh = C4::Context->dbh;
1119 my $query = " UPDATE reserves
1120 SET found=?,waitingdate = now()
1125 my $sth_set = $dbh->prepare($query);
1126 $sth_set->execute( $newstatus, $itemnumber );
1129 =item ModReserveAffect
1131 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1133 This function affect an item and a status for a given reserve
1134 The itemnumber parameter is used to find the biblionumber.
1135 with the biblionumber & the borrowernumber, we can affect the itemnumber
1136 to the correct reserve.
1138 if $transferToDo is not set, then the status is set to "Waiting" as well.
1139 otherwise, a transfer is on the way, and the end of the transfer will
1140 take care of the waiting status
1143 sub ModReserveAffect {
1144 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1145 my $dbh = C4::Context->dbh;
1147 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1148 # attached to $itemnumber
1149 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1150 $sth->execute($itemnumber);
1151 my ($biblionumber) = $sth->fetchrow;
1153 # get request - need to find out if item is already
1154 # waiting in order to not send duplicate hold filled notifications
1155 my $request = GetReserveInfo($borrowernumber, $biblionumber);
1156 my $already_on_shelf = ($request && $request->{found} eq 'W') ? 1 : 0;
1158 # If we affect a reserve that has to be transfered, don't set to Waiting
1160 if ($transferToDo) {
1165 WHERE borrowernumber = ?
1166 AND biblionumber = ?
1170 # affect the reserve to Waiting as well.
1177 WHERE borrowernumber = ?
1178 AND biblionumber = ?
1181 $sth = $dbh->prepare($query);
1182 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1183 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo && !$already_on_shelf );
1188 =item ModReserveCancelAll
1190 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1192 function to cancel reserv,check other reserves, and transfer document if it's necessary
1196 sub ModReserveCancelAll {
1199 my ( $itemnumber, $borrowernumber ) = @_;
1201 #step 1 : cancel the reservation
1202 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1204 #step 2 launch the subroutine of the others reserves
1205 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1207 return ( $messages, $nextreservinfo );
1210 =item ModReserveMinusPriority
1212 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1214 Reduce the values of queuded list
1218 sub ModReserveMinusPriority {
1219 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1221 #first step update the value of the first person on reserv
1222 my $dbh = C4::Context->dbh;
1225 SET priority = 0 , itemnumber = ?
1226 WHERE borrowernumber=?
1229 my $sth_upd = $dbh->prepare($query);
1230 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1231 # second step update all others reservs
1232 _FixPriority($biblionumber, $borrowernumber, '0');
1235 =item GetReserveInfo
1237 &GetReserveInfo($borrowernumber,$biblionumber);
1239 Get item and borrower details for a current hold.
1240 Current implementation this query should have a single result.
1243 sub GetReserveInfo {
1244 my ( $borrowernumber, $biblionumber ) = @_;
1245 my $dbh = C4::Context->dbh;
1246 my $strsth="SELECT reservedate, reservenotes, reserves.borrowernumber,
1247 reserves.biblionumber, reserves.branchcode,
1248 notificationdate, reminderdate, priority, found,
1249 firstname, surname, phone,
1250 email, address, address2,
1251 cardnumber, city, zipcode,
1252 biblio.title, biblio.author,
1253 items.holdingbranch, items.itemcallnumber, items.itemnumber,
1255 FROM reserves left join items
1256 ON items.itemnumber=reserves.itemnumber ,
1259 reserves.borrowernumber=? &&
1260 reserves.biblionumber=? &&
1261 reserves.borrowernumber=borrowers.borrowernumber &&
1262 reserves.biblionumber=biblio.biblionumber ";
1263 my $sth = $dbh->prepare($strsth);
1264 $sth->execute($borrowernumber,$biblionumber);
1266 my $data = $sth->fetchrow_hashref;
1271 =item IsAvailableForItemLevelRequest
1275 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1279 Checks whether a given item record is available for an
1280 item-level hold request. An item is available if
1282 * it is not lost AND
1283 * it is not damaged AND
1284 * it is not withdrawn AND
1285 * does not have a not for loan value > 0
1287 Whether or not the item is currently on loan is
1288 also checked - if the AllowOnShelfHolds system preference
1289 is ON, an item can be requested even if it is currently
1290 on loan to somebody else. If the system preference
1291 is OFF, an item that is currently checked out cannot
1292 be the target of an item-level hold request.
1294 Note that IsAvailableForItemLevelRequest() does not
1295 check if the staff operator is authorized to place
1296 a request on the item - in particular,
1297 this routine does not check IndependantBranches
1298 and canreservefromotherbranches.
1302 sub IsAvailableForItemLevelRequest {
1303 my $itemnumber = shift;
1305 my $item = GetItem($itemnumber);
1307 # must check the notforloan setting of the itemtype
1308 # FIXME - a lot of places in the code do this
1309 # or something similar - need to be
1311 my $dbh = C4::Context->dbh;
1312 my $notforloan_query;
1313 if (C4::Context->preference('item-level_itypes')) {
1314 $notforloan_query = "SELECT itemtypes.notforloan
1316 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1317 WHERE itemnumber = ?";
1319 $notforloan_query = "SELECT itemtypes.notforloan
1321 JOIN biblioitems USING (biblioitemnumber)
1322 JOIN itemtypes USING (itemtype)
1323 WHERE itemnumber = ?";
1325 my $sth = $dbh->prepare($notforloan_query);
1326 $sth->execute($itemnumber);
1327 my $notforloan_per_itemtype = 0;
1328 if (my ($notforloan) = $sth->fetchrow_array) {
1329 $notforloan_per_itemtype = 1 if $notforloan;
1332 my $available_per_item = 1;
1333 $available_per_item = 0 if $item->{itemlost} or
1334 ( $item->{notforloan} > 0 ) or
1335 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1336 $item->{wthdrawn} or
1337 $notforloan_per_itemtype;
1340 if (C4::Context->preference('AllowOnShelfHolds')) {
1341 return $available_per_item;
1343 return ($available_per_item and $item->{onloan});
1349 &_FixPriority($biblio,$borrowernumber,$rank);
1351 Only used internally (so don't export it)
1352 Changed how this functions works #
1353 Now just gets an array of reserves in the rank order and updates them with
1354 the array index (+1 as array starts from 0)
1355 and if $rank is supplied will splice item from the array and splice it back in again
1356 in new priority rank
1361 my ( $biblio, $borrowernumber, $rank ) = @_;
1362 my $dbh = C4::Context->dbh;
1363 if ( $rank eq "del" ) {
1364 CancelReserve( $biblio, undef, $borrowernumber );
1366 if ( $rank eq "W" || $rank eq "0" ) {
1368 # make sure priority for waiting items is 0
1372 WHERE biblionumber = ?
1373 AND borrowernumber = ?
1376 my $sth = $dbh->prepare($query);
1377 $sth->execute( $biblio, $borrowernumber );
1383 # FIXME adding a new security in returned elements for changing priority,
1384 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1385 # This is wrong a waiting reserve has W set
1386 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1388 SELECT borrowernumber, reservedate, constrainttype
1390 WHERE biblionumber = ?
1391 AND ((found <> 'W') or found is NULL)
1392 ORDER BY priority ASC
1394 my $sth = $dbh->prepare($query);
1395 $sth->execute($biblio);
1396 while ( my $line = $sth->fetchrow_hashref ) {
1397 push( @reservedates, $line );
1398 push( @priority, $line );
1401 # To find the matching index
1403 my $key = -1; # to allow for 0 to be a valid result
1404 for ( $i = 0 ; $i < @priority ; $i++ ) {
1405 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1406 $key = $i; # save the index
1411 # if index exists in array then move it to new position
1412 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1413 my $new_rank = $rank -
1414 1; # $new_rank is what you want the new index to be in the array
1415 my $moving_item = splice( @priority, $key, 1 );
1416 splice( @priority, $new_rank, 0, $moving_item );
1419 # now fix the priority on those that are left....
1423 WHERE biblionumber = ?
1424 AND borrowernumber = ?
1428 $sth = $dbh->prepare($query);
1429 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1432 $priority[$j]->{'borrowernumber'},
1433 $priority[$j]->{'reservedate'}
1439 =item _Findgroupreserve
1441 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1444 I don't know what this does, because I don't understand how reserve
1445 constraints work. I think the idea is that you reserve a particular
1446 biblio, and the constraint allows you to restrict it to a given
1447 biblioitem (e.g., if you want to borrow the audio book edition of "The
1448 Prophet", rather than the first available publication).
1450 C<&_Findgroupreserve> returns :
1451 C<@results> is an array of references-to-hash whose keys are mostly
1452 fields from the reserves table of the Koha database, plus
1453 C<biblioitemnumber>.
1457 sub _Findgroupreserve {
1458 my ( $bibitem, $biblio, $itemnumber ) = @_;
1459 my $dbh = C4::Context->dbh;
1461 # check for exact targetted match
1462 my $item_level_target_query = qq/
1463 SELECT reserves.biblionumber AS biblionumber,
1464 reserves.borrowernumber AS borrowernumber,
1465 reserves.reservedate AS reservedate,
1466 reserves.branchcode AS branchcode,
1467 reserves.cancellationdate AS cancellationdate,
1468 reserves.found AS found,
1469 reserves.reservenotes AS reservenotes,
1470 reserves.priority AS priority,
1471 reserves.timestamp AS timestamp,
1472 biblioitems.biblioitemnumber AS biblioitemnumber,
1473 reserves.itemnumber AS itemnumber
1475 JOIN biblioitems USING (biblionumber)
1476 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1479 AND item_level_request = 1
1482 my $sth = $dbh->prepare($item_level_target_query);
1483 $sth->execute($itemnumber);
1485 if ( my $data = $sth->fetchrow_hashref ) {
1486 push( @results, $data );
1488 return @results if @results;
1490 # check for title-level targetted match
1491 my $title_level_target_query = qq/
1492 SELECT reserves.biblionumber AS biblionumber,
1493 reserves.borrowernumber AS borrowernumber,
1494 reserves.reservedate AS reservedate,
1495 reserves.branchcode AS branchcode,
1496 reserves.cancellationdate AS cancellationdate,
1497 reserves.found AS found,
1498 reserves.reservenotes AS reservenotes,
1499 reserves.priority AS priority,
1500 reserves.timestamp AS timestamp,
1501 biblioitems.biblioitemnumber AS biblioitemnumber,
1502 reserves.itemnumber AS itemnumber
1504 JOIN biblioitems USING (biblionumber)
1505 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1508 AND item_level_request = 0
1509 AND hold_fill_targets.itemnumber = ?
1511 $sth = $dbh->prepare($title_level_target_query);
1512 $sth->execute($itemnumber);
1514 if ( my $data = $sth->fetchrow_hashref ) {
1515 push( @results, $data );
1517 return @results if @results;
1520 SELECT reserves.biblionumber AS biblionumber,
1521 reserves.borrowernumber AS borrowernumber,
1522 reserves.reservedate AS reservedate,
1523 reserves.branchcode AS branchcode,
1524 reserves.cancellationdate AS cancellationdate,
1525 reserves.found AS found,
1526 reserves.reservenotes AS reservenotes,
1527 reserves.priority AS priority,
1528 reserves.timestamp AS timestamp,
1529 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1530 reserves.itemnumber AS itemnumber
1532 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1533 WHERE reserves.biblionumber = ?
1534 AND ( ( reserveconstraints.biblioitemnumber = ?
1535 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1536 AND reserves.reservedate =reserveconstraints.reservedate )
1537 OR reserves.constrainttype='a' )
1538 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1540 $sth = $dbh->prepare($query);
1541 $sth->execute( $biblio, $bibitem, $itemnumber );
1543 while ( my $data = $sth->fetchrow_hashref ) {
1544 push( @results, $data );
1549 =item _koha_notify_reserve
1553 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1557 Sends a notification to the patron that their hold has been filled (through
1558 ModReserveAffect, _not_ ModReserveFill)
1562 sub _koha_notify_reserve {
1563 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1565 my $dbh = C4::Context->dbh;
1566 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1568 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1570 my $sth = $dbh->prepare("
1573 WHERE borrowernumber = ?
1574 AND biblionumber = ?
1576 $sth->execute( $borrowernumber, $biblionumber );
1577 my $reserve = $sth->fetchrow_hashref;
1578 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1580 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1582 my $letter = getletter( 'reserves', $messagingprefs->{'letter_code'} );
1584 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1585 C4::Letters::parseletter( $letter, 'borrowers', $reserve->{'borrowernumber'} );
1586 C4::Letters::parseletter( $letter, 'biblio', $reserve->{'biblionumber'} );
1587 C4::Letters::parseletter( $letter, 'reserves', $reserve->{'borrowernumber'}, $reserve->{'biblionumber'} );
1589 if ( $reserve->{'itemnumber'} ) {
1590 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1592 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1594 if ( -1 != firstidx { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1595 # aka, 'email' in ->{'transports'}
1596 C4::Letters::EnqueueLetter(
1597 { letter => $letter,
1598 borrowernumber => $borrowernumber,
1599 message_transport_type => 'email',
1600 from_address => $admin_email_address,
1605 if ( -1 != firstidx { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1606 C4::Letters::EnqueueLetter(
1607 { letter => $letter,
1608 borrowernumber => $borrowernumber,
1609 message_transport_type => 'sms',
1619 Koha Developement team <info@koha.org>