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 with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
24 # use warnings; # FIXME: someday
27 use C4::Dates qw/format_date format_date_in_iso/;
34 # for _koha_notify_reserve
35 use C4::Members::Messaging;
37 use C4::Branch qw( GetBranchDetail );
38 use List::MoreUtils qw( firstidx any );
40 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
44 C4::Reserves - Koha functions for dealing with reservation.
52 this modules provides somes functions to deal with reservations.
54 Reserves are stored in reserves table.
55 The following columns contains important values :
56 - priority >0 : then the reserve is at 1st stage, and not yet affected to any item.
57 =0 : then the reserve is being dealed
58 - found : NULL : means the patron requested the 1st available, and we haven't choosen the item
59 W(aiting) : the reserve has an itemnumber affected, and is on the way
60 T(ransfet) : the reserve has an itemnumber affected, and is beeing transfered to pickup branch
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
96 &GetReservesFromItemnumber
97 &GetReservesFromBiblionumber
98 &GetReservesFromBorrowernumber
113 &ModReserveMinusPriority
120 &IsAvailableForItemLevelRequest
126 AddReserve($branch,$borrowernumber,$biblionumber,$constraint,$bibitems,$priority,$notes,$title,$checkitem,$found, $from)
132 $branch, $borrowernumber, $biblionumber,
133 $constraint, $bibitems, $priority, $notes,
134 $title, $checkitem, $found, $from
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,
182 # Send e-mail to librarian if syspref is active
183 if(C4::Context->preference("emailLibrarianWhenHoldIsPlaced")){
184 my $borrower = GetMemberDetails($borrowernumber);
185 my $biblio = GetBiblioData($biblionumber);
186 my $lettertype = ($from eq "intranet") ? "STAFFHOLDPLACED" : "HOLDPLACED";
187 my $letter = C4::Letters::getletter( 'reserves', $lettertype);
188 my $admin_email_address = C4::Context->preference('KohaAdminEmailAddress');
190 my %keys = (%$borrower, %$biblio);
191 foreach my $key (keys %keys) {
192 my $replacefield = "<<$key>>";
193 $letter->{content} =~ s/$replacefield/$keys{$key}/g;
194 $letter->{title} =~ s/$replacefield/$keys{$key}/g;
197 C4::Letters::EnqueueLetter(
199 borrowernumber => $borrowernumber,
200 message_transport_type => 'email',
201 from_address => $admin_email_address,
202 to_address => $admin_email_address,
211 ($const eq "o" || $const eq "e") or return; # FIXME: why not have a useful return value?
213 INSERT INTO reserveconstraints
214 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
218 $sth = $dbh->prepare($query); # keep prepare outside the loop!
219 foreach (@$bibitems) {
220 $sth->execute($borrowernumber, $biblionumber, $resdate, $_);
223 return; # FIXME: why not have a useful return value?
228 =item GetPendingReserves
232 sub GetPendingReserves {
233 my ($filters, $startindex, $results) = @_;
235 $startindex = "0" if not $startindex;
238 my $indepbranch = C4::Context->preference('IndependantBranches') ? C4::Context->userenv->{'branch'} : undef;
239 my $dbh = C4::Context->dbh;
241 my $query = "SELECT DISTINCT(biblionumber) AS biblionumber
243 LEFT JOIN biblio USING(biblionumber)
244 WHERE reserves.found IS NULL ";
247 $query .= " AND branchcode = ? ";
248 push @query_params, $indepbranch;
251 my $sth = $dbh->prepare($query);
252 $sth->execute(@query_params);
256 while ( my $reserve = $sth->fetchrow_hashref ) {
258 unless( $line = $reserves{$reserve->{biblionumber}} ){
260 my $biblio = GetBiblioData($reserve->{biblionumber});
261 my @items = GetItemsInfo($reserve->{biblionumber});
263 $line->{title} = $biblio->{title};
264 foreach my $item (@items){
265 next if ( ($indepbranch && $indepbranch ne $item->{holdingbranch})
267 or $item->{notforloan}
269 or $item->{count_reserves} eq "Waiting" or $item->{count_reserves} eq "Transit");
271 $line->{holdingbranches}->{$item->{holdingbranch}} = 1;
272 $line->{callnumbers}->{$item->{itemcallnumber}} = 1;
273 $line->{locations}->{$item->{location}} = 1;
274 $line->{itemtypes}->{$item->{itemtype}} = 1;
277 $line->{reservecount}++;
278 $reserves{$reserve->{biblionumber}} = $line if($line->{count});
282 foreach my $rkey (keys %reserves){
283 my $line = $reserves{$rkey};
284 $line->{biblionumber} = $rkey;
286 foreach my $datatype (qw/holdingbranches callnumbers locations itemtypes/){
288 foreach my $data (keys %{$line->{$datatype}}){
289 push @newdatas, { 'value' => $data}
291 $line->{$datatype} = \@newdatas;
294 foreach my $key (keys %$filters){
295 my $value = $filters->{$key};
296 $filtered = 0 if not (any { $_->{value} =~ /^$value$/ } @{$line->{$key}}) and $value;
298 push @reserves, $line if $filtered; # if (any { $_->{value} =~ /^FOSPC$/ } @{$line->{holdingbranches}});
301 my $count = scalar @reserves;
302 my $endindex = ($count > $startindex + $results) ? $startindex + $results : $count;
305 @reserves = @reserves[$startindex..$endindex];
309 return ($count, \@reserves);
312 =item GetReservesFromBiblionumber
314 ($count, $title_reserves) = &GetReserves($biblionumber);
316 This function gets the list of reservations for one C<$biblionumber>, returning a count
317 of the reserves and an arrayref pointing to the reserves for C<$biblionumber>.
321 sub GetReservesFromBiblionumber {
322 my ($biblionumber) = shift or return (0, []);
323 my $dbh = C4::Context->dbh;
325 # Find the desired items in the reserves
328 timestamp AS rtimestamp,
338 WHERE biblionumber = ?
340 my $sth = $dbh->prepare($query);
341 $sth->execute($biblionumber);
344 while ( my $data = $sth->fetchrow_hashref ) {
346 # FIXME - What is this doing? How do constraints work?
347 if ($data->{constrainttype} eq 'o') {
349 SELECT biblioitemnumber
350 FROM reserveconstraints
351 WHERE biblionumber = ?
352 AND borrowernumber = ?
355 my $csth = $dbh->prepare($query);
356 $csth->execute($data->{biblionumber}, $data->{borrowernumber}, $data->{reservedate});
358 while ( my $bibitemnos = $csth->fetchrow_array ) {
359 push( @bibitemno, $bibitemnos ); # FIXME: inefficient: use fetchall_arrayref
361 my $count = scalar @bibitemno;
363 # if we have two or more different specific itemtypes
364 # reserved by same person on same day
367 $bdata = GetBiblioItemData( $bibitemno[$i] ); # FIXME: This doesn't make sense.
368 $i++; # $i can increase each pass, but the next @bibitemno might be smaller?
371 # Look up the book we just found.
372 $bdata = GetBiblioItemData( $bibitemno[0] );
374 # Add the results of this latest search to the current
376 # FIXME - An 'each' would probably be more efficient.
377 foreach my $key ( keys %$bdata ) {
378 $data->{$key} = $bdata->{$key};
381 push @results, $data;
383 return ( $#results + 1, \@results );
386 =item GetReservesFromItemnumber
388 ( $reservedate, $borrowernumber, $branchcode ) = GetReservesFromItemnumber($itemnumber);
390 TODO :: Description here
394 sub GetReservesFromItemnumber {
395 my ( $itemnumber ) = @_;
396 my $dbh = C4::Context->dbh;
398 SELECT reservedate,borrowernumber,branchcode
402 my $sth_res = $dbh->prepare($query);
403 $sth_res->execute($itemnumber);
404 my ( $reservedate, $borrowernumber,$branchcode ) = $sth_res->fetchrow_array;
405 return ( $reservedate, $borrowernumber, $branchcode );
408 =item GetReservesFromBorrowernumber
410 $borrowerreserv = GetReservesFromBorrowernumber($borrowernumber,$tatus);
416 sub GetReservesFromBorrowernumber {
417 my ( $borrowernumber, $status ) = @_;
418 my $dbh = C4::Context->dbh;
421 $sth = $dbh->prepare("
424 WHERE borrowernumber=?
428 $sth->execute($borrowernumber,$status);
430 $sth = $dbh->prepare("
433 WHERE borrowernumber=?
436 $sth->execute($borrowernumber);
438 my $data = $sth->fetchall_arrayref({});
441 #-------------------------------------------------------------------------------------
442 =item CanBookBeReserved
444 $error = &CanBookBeReserved($borrowernumber, $biblionumber)
448 sub CanBookBeReserved{
449 my ($borrowernumber, $biblionumber) = @_;
451 my $dbh = C4::Context->dbh;
452 my $biblio = GetBiblioData($biblionumber);
453 my $borrower = C4::Members::GetMember(borrowernumber=>$borrowernumber);
454 my $controlbranch = C4::Context->preference('ReservesControlBranch');
455 my $itype = C4::Context->preference('item-level_itypes');
456 my $reservesrights= C4::Context->preference('maxreserves');
457 my $reservescount = 0;
459 # we retrieve the user rights
464 if($controlbranch eq "ItemHomeLibrary"){
466 }elsif($controlbranch eq "PatronLibrary"){
467 $branchcode = $borrower->{branchcode};
470 $reservescount = GetReserveCount($borrowernumber);
472 if($reservescount < $reservesrights){
480 =item CanItemBeReserved
482 $error = &CanItemBeReserved($borrowernumber, $itemnumber)
484 this function return 1 if an item can be issued by this borrower.
488 sub CanItemBeReserved{
489 my ($borrowernumber, $itemnumber) = @_;
491 my $dbh = C4::Context->dbh;
493 my $controlbranch = C4::Context->preference('ReservesControlBranch') || "ItemHomeLibrary";
494 my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
495 my $allowedreserves = C4::Context->preference('maxreserves');
497 # we retrieve borrowers and items informations #
498 my $item = C4::Items::GetItem($itemnumber);
499 my $borrower = C4::Members::GetMember($borrowernumber, 'borrowernumber');
501 my $branchcode = "*";
502 my $branchfield = "reserves.branchcode";
504 if( $controlbranch eq "ItemHomeLibrary" ){
505 $branchcode = $item->{homebranch};
506 }elsif( $controlbranch eq "PatronLibrary" ){
507 $branchcode = $borrower->{branchcode};
510 # we retrieve user rights on this itemtype and branchcode
511 my $issuingrule = C4::Circulation::GetIssuingRule($borrower->{categorycode}, $item->{$itype}, $branchcode);
515 my $reservecount = GetReserveCount($borrowernumber);
517 # we check if it's ok or not
518 if(( $reservecount < $allowedreserves ) and $issuingrule->{maxissueqty} ){
524 #-------------------------------------------------------------------------------------
526 =item GetReserveCount
528 $number = &GetReserveCount($borrowernumber);
530 this function returns the number of reservation for a borrower given on input arg.
534 sub GetReserveCount {
535 my ($borrowernumber) = @_;
537 my $dbh = C4::Context->dbh;
540 SELECT COUNT(*) AS counter
542 WHERE borrowernumber = ?
544 my $sth = $dbh->prepare($query);
545 $sth->execute($borrowernumber);
546 my $row = $sth->fetchrow_hashref;
547 return $row->{counter};
550 =item GetOtherReserves
552 ($messages,$nextreservinfo)=$GetOtherReserves(itemnumber);
554 Check queued list of this document and check if this document must be transfered
558 sub GetOtherReserves {
559 my ($itemnumber) = @_;
562 my ( $restype, $checkreserves ) = CheckReserves($itemnumber);
563 if ($checkreserves) {
564 my $iteminfo = GetItem($itemnumber);
565 if ( $iteminfo->{'holdingbranch'} ne $checkreserves->{'branchcode'} ) {
566 $messages->{'transfert'} = $checkreserves->{'branchcode'};
567 #minus priorities of others reservs
568 ModReserveMinusPriority(
570 $checkreserves->{'borrowernumber'},
571 $iteminfo->{'biblionumber'}
574 #launch the subroutine dotransfer
575 C4::Items::ModItemTransfer(
577 $iteminfo->{'holdingbranch'},
578 $checkreserves->{'branchcode'}
583 #step 2b : case of a reservation on the same branch, set the waiting status
585 $messages->{'waiting'} = 1;
586 ModReserveMinusPriority(
588 $checkreserves->{'borrowernumber'},
589 $iteminfo->{'biblionumber'}
591 ModReserveStatus($itemnumber,'W');
594 $nextreservinfo = $checkreserves->{'borrowernumber'};
597 return ( $messages, $nextreservinfo );
602 $fee = GetReserveFee($borrowernumber,$biblionumber,$constraint,$biblionumber);
604 Calculate the fee for a reserve
609 my ($borrowernumber, $biblionumber, $constraint, $bibitems ) = @_;
612 my $dbh = C4::Context->dbh;
613 my $const = lc substr( $constraint, 0, 1 );
615 SELECT * FROM borrowers
616 LEFT JOIN categories ON borrowers.categorycode = categories.categorycode
617 WHERE borrowernumber = ?
619 my $sth = $dbh->prepare($query);
620 $sth->execute($borrowernumber);
621 my $data = $sth->fetchrow_hashref;
623 my $fee = $data->{'reservefee'};
624 my $cntitems = @- > $bibitems;
628 # check for items on issue
629 # first find biblioitem records
631 my $sth1 = $dbh->prepare(
632 "SELECT * FROM biblio LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
633 WHERE (biblio.biblionumber = ?)"
635 $sth1->execute($biblionumber);
636 while ( my $data1 = $sth1->fetchrow_hashref ) {
637 if ( $const eq "a" ) {
638 push @biblioitems, $data1;
643 while ( $x < $cntitems ) {
644 if ( @$bibitems->{'biblioitemnumber'} ==
645 $data->{'biblioitemnumber'} )
651 if ( $const eq 'o' ) {
653 push @biblioitems, $data1;
658 push @biblioitems, $data1;
664 my $cntitemsfound = @biblioitems;
668 while ( $x < $cntitemsfound ) {
669 my $bitdata = $biblioitems[$x];
670 my $sth2 = $dbh->prepare(
672 WHERE biblioitemnumber = ?"
674 $sth2->execute( $bitdata->{'biblioitemnumber'} );
675 while ( my $itdata = $sth2->fetchrow_hashref ) {
676 my $sth3 = $dbh->prepare(
677 "SELECT * FROM issues
678 WHERE itemnumber = ?"
680 $sth3->execute( $itdata->{'itemnumber'} );
681 if ( my $isdata = $sth3->fetchrow_hashref ) {
689 if ( $allissued == 0 ) {
691 $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
692 $rsth->execute($biblionumber);
693 if ( my $rdata = $rsth->fetchrow_hashref ) {
703 =item GetReservesToBranch
705 @transreserv = GetReservesToBranch( $frombranch );
707 Get reserve list for a given branch
711 sub GetReservesToBranch {
712 my ( $frombranch ) = @_;
713 my $dbh = C4::Context->dbh;
714 my $sth = $dbh->prepare(
715 "SELECT borrowernumber,reservedate,itemnumber,timestamp
720 $sth->execute( $frombranch );
723 while ( my $data = $sth->fetchrow_hashref ) {
724 $transreserv[$i] = $data;
727 return (@transreserv);
730 =item GetReservesForBranch
732 @transreserv = GetReservesForBranch($frombranch);
736 sub GetReservesForBranch {
737 my ($frombranch) = @_;
738 my $dbh = C4::Context->dbh;
739 my $query = "SELECT borrowernumber,reservedate,itemnumber,waitingdate
744 $query .= " AND branchcode=? ";
746 $query .= "ORDER BY waitingdate" ;
747 my $sth = $dbh->prepare($query);
749 $sth->execute($frombranch);
756 while ( my $data = $sth->fetchrow_hashref ) {
757 $transreserv[$i] = $data;
760 return (@transreserv);
763 sub GetReserveStatus {
764 my ($itemnumber) = @_;
766 my $dbh = C4::Context->dbh;
768 my $itemstatus = $dbh->prepare("SELECT found FROM reserves WHERE itemnumber = ?");
770 $itemstatus->execute($itemnumber);
771 my ($found) = $itemstatus->fetchrow_array;
777 ($status, $reserve) = &CheckReserves($itemnumber);
779 Find a book in the reserves.
781 C<$itemnumber> is the book's item number.
783 As I understand it, C<&CheckReserves> looks for the given item in the
784 reserves. If it is found, that's a match, and C<$status> is set to
787 Otherwise, it finds the most important item in the reserves with the
788 same biblio number as this book (I'm not clear on this) and returns it
789 with C<$status> set to C<Reserved>.
791 C<&CheckReserves> returns a two-element list:
793 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
795 C<$reserve> is the reserve item that matched. It is a
796 reference-to-hash whose keys are mostly the fields of the reserves
797 table in the Koha database.
802 my ( $item, $barcode ) = @_;
803 my $dbh = C4::Context->dbh;
806 my $qitem = $dbh->quote($item);
807 # Look up the item by itemnumber
809 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
811 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
812 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
813 WHERE itemnumber=$qitem
815 $sth = $dbh->prepare($query);
818 my $qbc = $dbh->quote($barcode);
819 # Look up the item by barcode
821 SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan, items.notforloan AS itemnotforloan
823 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
824 LEFT JOIN itemtypes ON biblioitems.itemtype = itemtypes.itemtype
825 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
826 AND biblioitems.itemtype = itemtypes.itemtype
829 $sth = $dbh->prepare($query);
831 # FIXME - This function uses $item later on. Ought to set it here.
834 my ( $biblio, $bibitem, $notforloan_per_itemtype, $notforloan_per_item ) = $sth->fetchrow_array;
836 # if item is not for loan it cannot be reserved either.....
837 # execption to notforloan is where items.notforloan < 0 : This indicates the item is holdable.
838 return ( 0, 0 ) if ( $notforloan_per_item > 0 ) or $notforloan_per_itemtype;
840 # get the reserves...
841 # Find this item in the reserves
842 my @reserves = _Findgroupreserve( $bibitem, $biblio, $item );
843 my $count = scalar @reserves;
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.
849 my $priority = 10000000;
852 foreach my $res (@reserves) {
853 # FIXME - $item might be undefined or empty: the caller
854 # might be searching by barcode.
855 if ( $res->{'itemnumber'} == $item && $res->{'priority'} == 0) {
857 return ( "Waiting", $res );
859 elsif( $res->{'itemnumber'} == $item && $res->{'found'} eq 'T' ){
860 return ( "Transit", $res );
863 # See if this item is more important than what we've got
865 if ( $res->{'priority'} != 0 && $res->{'priority'} < $priority )
867 $priority = $res->{'priority'};
874 # If we get this far, then no exact match was found. Print the
875 # most important item on the list. I think this tells us who's
876 # next in line to get this book.
877 if ($highest) { # FIXME - $highest might be undefined
878 $highest->{'itemnumber'} = $item;
879 return ( "Reserved", $highest );
888 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
892 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
893 cancel, but not both: if both are given, C<&CancelReserve> does
896 C<$borrowernumber> is the borrower number of the patron on whose
897 behalf the book was reserved.
899 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
900 priorities of the other people who are waiting on the book.
905 my ( $biblio, $item, $borr ) = @_;
906 my $dbh = C4::Context->dbh;
907 if ( $item and $borr ) {
908 # removing a waiting reserve record....
909 # update the database...
912 SET cancellationdate = now(),
916 AND borrowernumber = ?
918 my $sth = $dbh->prepare($query);
919 $sth->execute( $item, $borr );
922 INSERT INTO old_reserves
923 SELECT * FROM reserves
925 AND borrowernumber = ?
927 $sth = $dbh->prepare($query);
928 $sth->execute( $item, $borr );
932 AND borrowernumber = ?
934 $sth = $dbh->prepare($query);
935 $sth->execute( $item, $borr );
938 # removing a reserve record....
939 # get the prioritiy on this record....
942 SELECT priority FROM reserves
943 WHERE biblionumber = ?
944 AND borrowernumber = ?
945 AND cancellationdate IS NULL
946 AND itemnumber IS NULL
948 my $sth = $dbh->prepare($query);
949 $sth->execute( $biblio, $borr );
950 ($priority) = $sth->fetchrow_array;
954 SET cancellationdate = now(),
957 WHERE biblionumber = ?
958 AND borrowernumber = ?
961 # update the database, removing the record...
962 $sth = $dbh->prepare($query);
963 $sth->execute( $biblio, $borr );
967 INSERT INTO old_reserves
968 SELECT * FROM reserves
969 WHERE biblionumber = ?
970 AND borrowernumber = ?
972 $sth = $dbh->prepare($query);
973 $sth->execute( $biblio, $borr );
977 WHERE biblionumber = ?
978 AND borrowernumber = ?
980 $sth = $dbh->prepare($query);
981 $sth->execute( $biblio, $borr );
983 # now fix the priority on the others....
984 _FixPriority( $priority, $biblio );
992 ModReserve($rank, $biblio, $borrower, $branch[, $itemnumber])
996 Change a hold request's priority or cancel it.
998 C<$rank> specifies the effect of the change. If C<$rank>
999 is 'W' or 'n', nothing happens. This corresponds to leaving a
1000 request alone when changing its priority in the holds queue
1003 If C<$rank> is 'del', the hold request is cancelled.
1005 If C<$rank> is an integer greater than zero, the priority of
1006 the request is set to that value. Since priority != 0 means
1007 that the item is not waiting on the hold shelf, setting the
1008 priority to a non-zero value also sets the request's found
1009 status and waiting date to NULL.
1011 The optional C<$itemnumber> parameter is used only when
1012 C<$rank> is a non-zero integer; if supplied, the itemnumber
1013 of the hold request is set accordingly; if omitted, the itemnumber
1016 FIXME: Note that the forgoing can have the effect of causing
1017 item-level hold requests to turn into title-level requests. This
1018 will be fixed once reserves has separate columns for requested
1019 itemnumber and supplying itemnumber.
1024 #subroutine to update a reserve
1025 my ( $rank, $biblio, $borrower, $branch , $itemnumber) = @_;
1026 return if $rank eq "W";
1027 return if $rank eq "n";
1028 my $dbh = C4::Context->dbh;
1029 if ( $rank eq "del" ) {
1032 SET cancellationdate=now()
1033 WHERE biblionumber = ?
1034 AND borrowernumber = ?
1036 my $sth = $dbh->prepare($query);
1037 $sth->execute( $biblio, $borrower );
1040 INSERT INTO old_reserves
1043 WHERE biblionumber = ?
1044 AND borrowernumber = ?
1046 $sth = $dbh->prepare($query);
1047 $sth->execute( $biblio, $borrower );
1049 DELETE FROM reserves
1050 WHERE biblionumber = ?
1051 AND borrowernumber = ?
1053 $sth = $dbh->prepare($query);
1054 $sth->execute( $biblio, $borrower );
1057 elsif ($rank =~ /^\d+/ and $rank > 0) {
1059 UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = ?, found = NULL, waitingdate = NULL
1060 WHERE biblionumber = ?
1061 AND borrowernumber = ?
1063 my $sth = $dbh->prepare($query);
1064 $sth->execute( $rank, $branch,$itemnumber, $biblio, $borrower);
1066 _FixPriority( $biblio, $borrower, $rank);
1070 =item ModReserveFill
1072 &ModReserveFill($reserve);
1074 Fill a reserve. If I understand this correctly, this means that the
1075 reserved book has been found and given to the patron who reserved it.
1077 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
1078 whose keys are fields from the reserves table in the Koha database.
1082 sub ModReserveFill {
1084 my $dbh = C4::Context->dbh;
1085 # fill in a reserve record....
1086 my $biblionumber = $res->{'biblionumber'};
1087 my $borrowernumber = $res->{'borrowernumber'};
1088 my $resdate = $res->{'reservedate'};
1090 # get the priority on this record....
1092 my $query = "SELECT priority
1094 WHERE biblionumber = ?
1095 AND borrowernumber = ?
1096 AND reservedate = ?";
1097 my $sth = $dbh->prepare($query);
1098 $sth->execute( $biblionumber, $borrowernumber, $resdate );
1099 ($priority) = $sth->fetchrow_array;
1102 # update the database...
1103 $query = "UPDATE reserves
1106 WHERE biblionumber = ?
1108 AND borrowernumber = ?
1110 $sth = $dbh->prepare($query);
1111 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1114 # move to old_reserves
1115 $query = "INSERT INTO old_reserves
1116 SELECT * FROM reserves
1117 WHERE biblionumber = ?
1119 AND borrowernumber = ?
1121 $sth = $dbh->prepare($query);
1122 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1123 $query = "DELETE FROM reserves
1124 WHERE biblionumber = ?
1126 AND borrowernumber = ?
1128 $sth = $dbh->prepare($query);
1129 $sth->execute( $biblionumber, $resdate, $borrowernumber );
1131 # now fix the priority on the others (if the priority wasn't
1132 # already sorted!)....
1133 unless ( $priority == 0 ) {
1134 _FixPriority( $priority, $biblionumber );
1138 =item ModReserveStatus
1140 &ModReserveStatus($itemnumber, $newstatus);
1142 Update the reserve status for the active (priority=0) reserve.
1144 $itemnumber is the itemnumber the reserve is on
1146 $newstatus is the new status.
1150 sub ModReserveStatus {
1152 #first : check if we have a reservation for this item .
1153 my ($itemnumber, $newstatus) = @_;
1154 my $dbh = C4::Context->dbh;
1155 my $query = " UPDATE reserves
1156 SET found=?,waitingdate = now()
1161 my $sth_set = $dbh->prepare($query);
1162 $sth_set->execute( $newstatus, $itemnumber );
1165 =item ModReserveAffect
1167 &ModReserveAffect($itemnumber,$borrowernumber,$diffBranchSend);
1169 This function affect an item and a status for a given reserve
1170 The itemnumber parameter is used to find the biblionumber.
1171 with the biblionumber & the borrowernumber, we can affect the itemnumber
1172 to the correct reserve.
1174 if $transferToDo is not set, then the status is set to "Waiting" as well.
1175 otherwise, a transfer is on the way, and the end of the transfer will
1176 take care of the waiting status
1179 sub ModReserveAffect {
1180 my ( $itemnumber, $borrowernumber,$transferToDo ) = @_;
1181 my $dbh = C4::Context->dbh;
1183 # we want to attach $itemnumber to $borrowernumber, find the biblionumber
1184 # attached to $itemnumber
1185 my $sth = $dbh->prepare("SELECT biblionumber FROM items WHERE itemnumber=?");
1186 $sth->execute($itemnumber);
1187 my ($biblionumber) = $sth->fetchrow;
1188 # If we affect a reserve that has to be transfered, don't set to Waiting
1190 if ($transferToDo) {
1196 WHERE borrowernumber = ?
1197 AND biblionumber = ?
1201 # affect the reserve to Waiting as well.
1208 WHERE borrowernumber = ?
1209 AND biblionumber = ?
1212 $sth = $dbh->prepare($query);
1213 $sth->execute( $itemnumber, $borrowernumber,$biblionumber);
1216 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber ) if ( !$transferToDo );
1221 =item ModReserveCancelAll
1223 ($messages,$nextreservinfo) = &ModReserveCancelAll($itemnumber,$borrowernumber);
1225 function to cancel reserv,check other reserves, and transfer document if it's necessary
1229 sub ModReserveCancelAll {
1232 my ( $itemnumber, $borrowernumber ) = @_;
1234 #step 1 : cancel the reservation
1235 my $CancelReserve = CancelReserve( undef, $itemnumber, $borrowernumber );
1237 #step 2 launch the subroutine of the others reserves
1238 ( $messages, $nextreservinfo ) = GetOtherReserves($itemnumber);
1240 return ( $messages, $nextreservinfo );
1243 =item ModReserveMinusPriority
1245 &ModReserveMinusPriority($itemnumber,$borrowernumber,$biblionumber)
1247 Reduce the values of queuded list
1251 sub ModReserveMinusPriority {
1252 my ( $itemnumber, $borrowernumber, $biblionumber ) = @_;
1254 #first step update the value of the first person on reserv
1255 my $dbh = C4::Context->dbh;
1258 SET priority = 0 , itemnumber = ?
1259 WHERE borrowernumber=?
1262 my $sth_upd = $dbh->prepare($query);
1263 $sth_upd->execute( $itemnumber, $borrowernumber, $biblionumber );
1264 # second step update all others reservs
1265 _FixPriority($biblionumber, $borrowernumber, '0');
1268 =item GetReserveInfo
1270 &GetReserveInfo($borrowernumber,$biblionumber);
1272 Get item and borrower details for a current hold.
1273 Current implementation this query should have a single result.
1276 sub GetReserveInfo {
1277 my ( $borrowernumber, $biblionumber ) = @_;
1278 my $dbh = C4::Context->dbh;
1279 my $strsth="SELECT reservedate, reservenotes, reserves.borrowernumber,
1280 reserves.biblionumber, reserves.branchcode,
1281 notificationdate, reminderdate, priority, found,
1282 firstname, surname, phone,
1283 email, address, address2,
1284 cardnumber, city, zipcode,
1285 biblio.title, biblio.author,
1286 items.holdingbranch, items.itemcallnumber, items.itemnumber,
1288 FROM reserves left join items
1289 ON items.itemnumber=reserves.itemnumber ,
1292 reserves.borrowernumber=? &&
1293 reserves.biblionumber=? &&
1294 reserves.borrowernumber=borrowers.borrowernumber &&
1295 reserves.biblionumber=biblio.biblionumber ";
1296 my $sth = $dbh->prepare($strsth);
1297 $sth->execute($borrowernumber,$biblionumber);
1299 my $data = $sth->fetchrow_hashref;
1304 =item IsAvailableForItemLevelRequest
1308 my $is_available = IsAvailableForItemLevelRequest($itemnumber);
1312 Checks whether a given item record is available for an
1313 item-level hold request. An item is available if
1315 * it is not lost AND
1316 * it is not damaged AND
1317 * it is not withdrawn AND
1318 * does not have a not for loan value > 0
1320 Whether or not the item is currently on loan is
1321 also checked - if the AllowOnShelfHolds system preference
1322 is ON, an item can be requested even if it is currently
1323 on loan to somebody else. If the system preference
1324 is OFF, an item that is currently checked out cannot
1325 be the target of an item-level hold request.
1327 Note that IsAvailableForItemLevelRequest() does not
1328 check if the staff operator is authorized to place
1329 a request on the item - in particular,
1330 this routine does not check IndependantBranches
1331 and canreservefromotherbranches.
1335 sub IsAvailableForItemLevelRequest {
1336 my $itemnumber = shift;
1338 my $item = GetItem($itemnumber);
1340 # must check the notforloan setting of the itemtype
1341 # FIXME - a lot of places in the code do this
1342 # or something similar - need to be
1344 my $dbh = C4::Context->dbh;
1345 my $notforloan_query;
1346 if (C4::Context->preference('item-level_itypes')) {
1347 $notforloan_query = "SELECT itemtypes.notforloan
1349 JOIN itemtypes ON (itemtypes.itemtype = items.itype)
1350 WHERE itemnumber = ?";
1352 $notforloan_query = "SELECT itemtypes.notforloan
1354 JOIN biblioitems USING (biblioitemnumber)
1355 JOIN itemtypes USING (itemtype)
1356 WHERE itemnumber = ?";
1358 my $sth = $dbh->prepare($notforloan_query);
1359 $sth->execute($itemnumber);
1360 my $notforloan_per_itemtype = 0;
1361 if (my ($notforloan) = $sth->fetchrow_array) {
1362 $notforloan_per_itemtype = 1 if $notforloan;
1365 my $available_per_item = 1;
1366 $available_per_item = 0 if $item->{itemlost} or
1367 ( $item->{notforloan} > 0 ) or
1368 ($item->{damaged} and not C4::Context->preference('AllowHoldsOnDamagedItems')) or
1369 $item->{wthdrawn} or
1370 $notforloan_per_itemtype;
1373 if (C4::Context->preference('AllowOnShelfHolds')) {
1374 return $available_per_item;
1376 return ($available_per_item and ($item->{onloan} or GetReserveStatus($itemnumber) eq "W"));
1382 &_FixPriority($biblio,$borrowernumber,$rank);
1384 Only used internally (so don't export it)
1385 Changed how this functions works #
1386 Now just gets an array of reserves in the rank order and updates them with
1387 the array index (+1 as array starts from 0)
1388 and if $rank is supplied will splice item from the array and splice it back in again
1389 in new priority rank
1394 my ( $biblio, $borrowernumber, $rank ) = @_;
1395 my $dbh = C4::Context->dbh;
1396 if ( $rank eq "del" ) {
1397 CancelReserve( $biblio, undef, $borrowernumber );
1399 if ( $rank eq "W" || $rank eq "0" ) {
1401 # make sure priority for waiting items is 0
1405 WHERE biblionumber = ?
1406 AND borrowernumber = ?
1409 my $sth = $dbh->prepare($query);
1410 $sth->execute( $biblio, $borrowernumber );
1416 # FIXME adding a new security in returned elements for changing priority,
1417 # now, we don't care anymore any reservations with itemnumber linked (suppose a waiting reserve)
1418 # This is wrong a waiting reserve has W set
1419 # The assumption that having an itemnumber set means waiting is wrong and should be corrected any place it occurs
1421 SELECT borrowernumber, reservedate, constrainttype
1423 WHERE biblionumber = ?
1424 AND ((found <> 'W') or found is NULL)
1425 ORDER BY priority ASC
1427 my $sth = $dbh->prepare($query);
1428 $sth->execute($biblio);
1429 while ( my $line = $sth->fetchrow_hashref ) {
1430 push( @reservedates, $line );
1431 push( @priority, $line );
1434 # To find the matching index
1436 my $key = -1; # to allow for 0 to be a valid result
1437 for ( $i = 0 ; $i < @priority ; $i++ ) {
1438 if ( $borrowernumber == $priority[$i]->{'borrowernumber'} ) {
1439 $key = $i; # save the index
1444 # if index exists in array then move it to new position
1445 if ( $key > -1 && $rank ne 'del' && $rank > 0 ) {
1446 my $new_rank = $rank -
1447 1; # $new_rank is what you want the new index to be in the array
1448 my $moving_item = splice( @priority, $key, 1 );
1449 splice( @priority, $new_rank, 0, $moving_item );
1452 # now fix the priority on those that are left....
1456 WHERE biblionumber = ?
1457 AND borrowernumber = ?
1461 $sth = $dbh->prepare($query);
1462 for ( my $j = 0 ; $j < @priority ; $j++ ) {
1465 $priority[$j]->{'borrowernumber'},
1466 $priority[$j]->{'reservedate'}
1472 =item _Findgroupreserve
1474 @results = &_Findgroupreserve($biblioitemnumber, $biblionumber, $itemnumber);
1476 Looks for an item-specific match first, then for a title-level match, returning the
1477 first match found. If neither, then we look for a 3rd kind of match based on
1478 reserve constraints.
1480 TODO: add more explanation about reserve constraints
1482 C<&_Findgroupreserve> returns :
1483 C<@results> is an array of references-to-hash whose keys are mostly
1484 fields from the reserves table of the Koha database, plus
1485 C<biblioitemnumber>.
1489 sub _Findgroupreserve {
1490 my ( $bibitem, $biblio, $itemnumber ) = @_;
1491 my $dbh = C4::Context->dbh;
1493 # check for exact targetted match
1494 # This select is valid for both item_level and biblio_level
1495 my $item_level_target_query = qq/
1496 SELECT reserves.biblionumber AS biblionumber,
1497 reserves.borrowernumber AS borrowernumber,
1498 reserves.reservedate AS reservedate,
1499 reserves.branchcode AS branchcode,
1500 reserves.cancellationdate AS cancellationdate,
1501 reserves.found AS found,
1502 reserves.reservenotes AS reservenotes,
1503 reserves.priority AS priority,
1504 reserves.timestamp AS timestamp,
1505 biblioitems.biblioitemnumber AS biblioitemnumber,
1506 reserves.itemnumber AS itemnumber
1508 JOIN biblioitems USING (biblionumber)
1509 JOIN hold_fill_targets USING (biblionumber, borrowernumber, itemnumber)
1512 AND hold_fill_targets.itemnumber = ?
1515 my $sth = $dbh->prepare($item_level_target_query);
1516 $sth->execute($itemnumber);
1517 my $data = $sth->fetchall_arrayref({});
1518 return @$data if (@$data);
1520 # check for title-level targetted match
1521 my $title_level_target_query = qq/
1522 SELECT reserves.biblionumber AS biblionumber,
1523 reserves.borrowernumber AS borrowernumber,
1524 reserves.reservedate AS reservedate,
1525 reserves.branchcode AS branchcode,
1526 reserves.cancellationdate AS cancellationdate,
1527 reserves.found AS found,
1528 reserves.reservenotes AS reservenotes,
1529 reserves.priority AS priority,
1530 reserves.timestamp AS timestamp,
1531 biblioitems.biblioitemnumber AS biblioitemnumber,
1532 reserves.itemnumber AS itemnumber
1534 JOIN biblioitems USING (biblionumber)
1535 JOIN hold_fill_targets USING (biblionumber, borrowernumber)
1538 AND item_level_request = 0
1539 AND hold_fill_targets.itemnumber = ?
1541 $sth = $dbh->prepare($title_level_target_query);
1542 $sth->execute($itemnumber);
1543 $data = $sth->fetchall_arrayref({});
1544 return @$data if (@$data);
1547 SELECT reserves.biblionumber AS biblionumber,
1548 reserves.borrowernumber AS borrowernumber,
1549 reserves.reservedate AS reservedate,
1550 reserves.branchcode AS branchcode,
1551 reserves.cancellationdate AS cancellationdate,
1552 reserves.found AS found,
1553 reserves.reservenotes AS reservenotes,
1554 reserves.priority AS priority,
1555 reserves.timestamp AS timestamp,
1556 reserveconstraints.biblioitemnumber AS biblioitemnumber,
1557 reserves.itemnumber AS itemnumber
1559 LEFT JOIN reserveconstraints ON reserves.biblionumber = reserveconstraints.biblionumber
1560 WHERE reserves.biblionumber = ?
1561 AND ( ( reserveconstraints.biblioitemnumber = ?
1562 AND reserves.borrowernumber = reserveconstraints.borrowernumber
1563 AND reserves.reservedate = reserveconstraints.reservedate )
1564 OR reserves.constrainttype='a' )
1565 AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1567 $sth = $dbh->prepare($query);
1568 $sth->execute( $biblio, $bibitem, $itemnumber );
1569 $data = $sth->fetchall_arrayref({});
1570 return @$data if (@$data);
1574 =item _koha_notify_reserve
1578 _koha_notify_reserve( $itemnumber, $borrowernumber, $biblionumber );
1582 Sends a notification to the patron that their hold has been filled (through
1583 ModReserveAffect, _not_ ModReserveFill)
1587 sub _koha_notify_reserve {
1588 my ($itemnumber, $borrowernumber, $biblionumber) = @_;
1590 my $dbh = C4::Context->dbh;
1591 my $messagingprefs = C4::Members::Messaging::GetMessagingPreferences( { borrowernumber => $borrowernumber, message_name => 'Hold Filled' } );
1593 return if ( !defined( $messagingprefs->{'letter_code'} ) );
1595 my $sth = $dbh->prepare("
1598 WHERE borrowernumber = ?
1599 AND biblionumber = ?
1601 $sth->execute( $borrowernumber, $biblionumber );
1602 my $reserve = $sth->fetchrow_hashref;
1603 my $branch_details = GetBranchDetail( $reserve->{'branchcode'} );
1605 my $admin_email_address = $branch_details->{'branchemail'} || C4::Context->preference('KohaAdminEmailAddress');
1607 my $letter = getletter( 'reserves', $messagingprefs->{'letter_code'} );
1609 C4::Letters::parseletter( $letter, 'branches', $reserve->{'branchcode'} );
1610 C4::Letters::parseletter( $letter, 'borrowers', $reserve->{'borrowernumber'} );
1611 C4::Letters::parseletter( $letter, 'biblio', $reserve->{'biblionumber'} );
1612 C4::Letters::parseletter( $letter, 'reserves', $reserve->{'borrowernumber'}, $reserve->{'biblionumber'} );
1614 if ( $reserve->{'itemnumber'} ) {
1615 C4::Letters::parseletter( $letter, 'items', $reserve->{'itemnumber'} );
1617 $letter->{'content'} =~ s/<<[a-z0-9_]+\.[a-z0-9]+>>//g; #remove any stragglers
1619 if ( -1 != firstidx { $_ eq 'email' } @{$messagingprefs->{transports}} ) {
1620 # aka, 'email' in ->{'transports'}
1621 C4::Letters::EnqueueLetter(
1622 { letter => $letter,
1623 borrowernumber => $borrowernumber,
1624 message_transport_type => 'email',
1625 from_address => $admin_email_address,
1630 if ( -1 != firstidx { $_ eq 'sms' } @{$messagingprefs->{transports}} ) {
1631 C4::Letters::EnqueueLetter(
1632 { letter => $letter,
1633 borrowernumber => $borrowernumber,
1634 message_transport_type => 'sms',
1644 Koha Developement team <info@koha.org>