2 # NOTE: This file uses standard 8-character tabs
8 # Copyright 2000-2002 Katipo Communications
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
30 # FIXME - C4::Reserves2 uses C4::Search, which uses C4::Reserves2.
31 # So Perl complains that all of the functions here get redefined.
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
36 # set the version for version checking
58 # FIXME Take out CalcReserveFee after it can be removed from opac-reserves.pl
74 # make all your functions, whether exported or not;
78 ($count, $results) = &FindReserves($biblionumber, $borrowernumber);
80 Looks books up in the reserves. C<$biblionumber> is the biblionumber
81 of the book to look up. C<$borrowernumber> is the borrower number of a
82 patron whose books to look up.
84 Either C<$biblionumber> or C<$borrowernumber> may be the empty string,
85 but not both. If both are specified, C<&FindReserves> looks up the
86 given book for the given patron. If only C<$biblionumber> is
87 specified, C<&FindReserves> looks up that book for all patrons. If
88 only C<$borrowernumber> is specified, C<&FindReserves> looks up all of
89 that patron's reserves. If neither is specified, C<&FindReserves>
92 For each book thus found, C<&FindReserves> checks the reserve
93 constraints and does something I don't understand.
95 C<&FindReserves> returns a two-element array:
97 C<$count> is the number of elements in C<$results>.
99 C<$results> is a reference-to-array; each element is a
100 reference-to-hash, whose keys are (I think) all of the fields of the
101 reserves, borrowers, and biblio tables of the Koha database.
107 warn "bib : $bib , bor : $bor";
108 my $dbh = C4::Context->dbh;
109 # Find the desired items in the reserves
110 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle FROM reserves,borrowers,biblio ";
111 # FIXME - These three bits of SQL seem to contain a fair amount of
112 # redundancy. Wouldn't it be better to have a @clauses array, add
113 # one or two clauses as necessary, then join(" AND ", @clauses) ?
115 $bib = $dbh->quote($bib);
117 # Both $bib and $bor specified
118 # Find a particular book for a particular patron
119 $bor = $dbh->quote($bor);
120 $query .= " where reserves.biblionumber = $bib
121 and borrowers.borrowernumber = $bor
122 and reserves.borrowernumber = borrowers.borrowernumber
123 and biblio.biblionumber = $bib
124 and cancellationdate is NULL
125 and (found <> 'F' or found is NULL)";
127 # $bib specified, but not $bor
128 # Find a particular book for all patrons
129 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
130 and biblio.biblionumber = $bib
131 and reserves.biblionumber = $bib
132 and cancellationdate is NULL
133 and (found <> 'F' or found is NULL)";
136 # FIXME - Check that $bor was given
138 # Find all books for the given patron.
139 $query .= " where borrowers.borrowernumber = $bor
140 and reserves.borrowernumber = borrowers.borrowernumber
141 and reserves.biblionumber = biblio.biblionumber
142 and cancellationdate is NULL and
143 (found <> 'F' or found is NULL)";
145 $query.=" order by priority";
146 my $sth=$dbh->prepare($query);
149 while (my $data=$sth->fetchrow_hashref){
150 # FIXME - What is this if-statement doing? How do constraints work?
151 if ($data->{'constrainttype'} eq 'o') {
152 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
153 WHERE biblionumber = ?
154 AND borrowernumber = ?
155 AND reservedate = ?";
156 my $csth=$dbh->prepare($conquery);
157 # FIXME - Why use separate variables for this?
158 my $bibn = $data->{'biblionumber'};
159 my $born = $data->{'borrowernumber'};
160 my $resd = $data->{'reservedate'};
161 $csth->execute($bibn, $born, $resd);
162 my ($bibitemno) = $csth->fetchrow_array;
164 # Look up the book we just found.
165 my $bdata = C4::Search::bibitemdata($bibitemno);
166 # Add the results of this latest search to the current
168 # FIXME - An 'each' would probably be more efficient.
169 foreach my $key (keys %$bdata) {
170 $data->{$key} = $bdata->{$key};
173 push @results, $data;
176 return($#results+1,\@results);
181 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
183 Find a book in the reserves.
185 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
186 Either one, but not both, may be false. If both are specified,
187 C<&CheckReserves> uses C<$itemnumber>.
189 $itemnubmer can be false, in which case uses the barcode. (Never uses
190 both. $itemnumber gets priority).
192 As I understand it, C<&CheckReserves> looks for the given item in the
193 reserves. If it is found, that's a match, and C<$status> is set to
196 Otherwise, it finds the most important item in the reserves with the
197 same biblio number as this book (I'm not clear on this) and returns it
198 with C<$status> set to C<Reserved>.
200 C<&CheckReserves> returns a two-element list:
202 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
204 C<$reserve> is the reserve item that matched. It is a
205 reference-to-hash whose keys are mostly the fields of the reserves
206 table in the Koha database.
211 my ($item, $barcode) = @_;
212 # warn "In CheckReserves: itemnumber = $item";
213 my $dbh = C4::Context->dbh;
216 my $qitem=$dbh->quote($item);
217 # Look up the item by itemnumber
218 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
219 FROM items, biblioitems, itemtypes
220 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
221 AND biblioitems.itemtype = itemtypes.itemtype
222 AND itemnumber=$qitem");
224 my $qbc=$dbh->quote($barcode);
225 # Look up the item by barcode
226 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
227 FROM items, biblioitems, itemtypes
228 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
229 AND biblioitems.itemtype = itemtypes.itemtype
231 # FIXME - This function uses $item later on. Ought to set it here.
234 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
236 # if item is not for loan it cannot be reserved either.....
237 return (0, 0) if ($notforloan);
238 # get the reserves...
239 # Find this item in the reserves
240 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
241 # $priority and $highest are used to find the most important item
242 # in the list returned by &Findgroupreserve. (The lower $priority,
243 # the more important the item.)
244 # $highest is the most important item we've seen so far.
245 my $priority = 10000000;
248 foreach my $res (@reserves) {
249 # FIXME - $item might be undefined or empty: the caller
250 # might be searching by barcode.
251 if ($res->{'itemnumber'} == $item) {
253 return ("Waiting", $res);
255 # See if this item is more important than what we've got
257 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
258 $priority = $res->{'priority'};
265 # If we get this far, then no exact match was found. Print the
266 # most important item on the list. I think this tells us who's
267 # next in line to get this book.
268 if ($highest) { # FIXME - $highest might be undefined
269 $highest->{'itemnumber'} = $item;
270 return ("Reserved", $highest);
278 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
282 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
283 cancel, but not both: if both are given, C<&CancelReserve> does
286 C<$borrowernumber> is the borrower number of the patron on whose
287 behalf the book was reserved.
289 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
290 priorities of the other people who are waiting on the book.
295 my ($biblio, $item, $borr) = @_;
296 my $dbh = C4::Context->dbh;
297 #warn "In CancelReserve";
298 if (($item and $borr) and (not $biblio)) {
299 # removing a waiting reserve record....
300 $item = $dbh->quote($item);
301 $borr = $dbh->quote($borr);
302 # update the database...
303 # FIXME - Use $dbh->do()
304 my $query = "update reserves set cancellationdate = now(),
307 where itemnumber = $item
308 and borrowernumber = $borr";
309 my $sth = $dbh->prepare($query);
313 if (($biblio and $borr) and (not $item)) {
315 # removing a reserve record....
316 my $q_biblio = $dbh->quote($biblio);
317 $borr = $dbh->quote($borr);
319 # get the prioritiy on this record....
322 my $query = "SELECT priority FROM reserves
323 WHERE biblionumber = $q_biblio
324 AND borrowernumber = $borr
325 AND cancellationdate is NULL
326 AND (found <> 'F' or found is NULL)";
327 my $sth=$dbh->prepare($query);
329 ($priority) = $sth->fetchrow_array;
333 # update the database, removing the record...
335 my $query = "update reserves set cancellationdate = now(),
338 where biblionumber = $q_biblio
339 and borrowernumber = $borr
340 and cancellationdate is NULL
341 and (found <> 'F' or found is NULL)";
342 my $sth = $dbh->prepare($query);
347 # now fix the priority on the others....
348 fixpriority($priority, $biblio);
354 &FillReserve($reserve);
356 Fill a reserve. If I understand this correctly, this means that the
357 reserved book has been found and given to the patron who reserved it.
359 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
360 whose keys are fields from the reserves table in the Koha database.
366 my $dbh = C4::Context->dbh;
368 # fill in a reserve record....
369 # FIXME - Remove some of the redundancy here
370 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
371 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
372 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
374 # get the priority on this record....
377 my $query = "SELECT priority FROM reserves
378 WHERE biblionumber = $qbiblio
379 AND borrowernumber = $borr
380 AND reservedate = $resdate)";
382 my $sth=$dbh->prepare($query);
384 ($priority) = $sth->fetchrow_array;
388 # update the database...
390 my $query = "UPDATE reserves SET found = 'F',
392 WHERE biblionumber = $qbiblio
393 AND reservedate = $resdate
394 AND borrowernumber = $borr";
395 my $sth = $dbh->prepare($query);
400 # now fix the priority on the others (if the priority wasn't
401 # already sorted!)....
402 unless ($priority == 0) {
403 fixpriority($priority, $biblio);
407 # Only used internally
408 # Decrements (makes more important) the reserves for all of the
409 # entries waiting on the given book, if their priority is > $priority.
411 my ($priority, $biblio) = @_;
412 my $dbh = C4::Context->dbh;
413 my ($count, $reserves) = FindReserves($biblio);
414 foreach my $rec (@$reserves) {
415 if ($rec->{'priority'} > $priority) {
416 # FIXME - Rewrite this without so much duplication and
418 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
419 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
420 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
421 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
422 my $query = "UPDATE reserves SET priority = $newpr
423 WHERE biblionumber = $nbib
424 AND borrowernumber = $nbor
425 AND reservedate = $nresd";
427 my $sth = $dbh->prepare($query);
436 my ($item, $borr) = @_;
437 my $dbh = C4::Context->dbh;
438 $item = $dbh->quote($item);
439 $borr = $dbh->quote($borr);
440 # get priority and biblionumber....
441 my $query = "SELECT reserves.priority as priority,
442 reserves.biblionumber as biblionumber,
443 reserves.branchcode as branchcode,
444 reserves.timestamp as timestamp
446 WHERE reserves.biblionumber = items.biblionumber
447 AND items.itemnumber = $item
448 AND reserves.borrowernumber = $borr
449 AND reserves.cancellationdate is NULL
450 AND (reserves.found <> 'F' or reserves.found is NULL)";
451 my $sth = $dbh->prepare($query);
453 my $data = $sth->fetchrow_hashref;
455 my $biblio = $data->{'biblionumber'};
456 my $timestamp = $data->{'timestamp'};
457 my $q_biblio = $dbh->quote($biblio);
458 my $q_timestamp = $dbh->quote($timestamp);
459 warn "Timestamp: ".$timestamp."\n";
460 # update reserves record....
461 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
462 WHERE borrowernumber = $borr
463 AND biblionumber = $q_biblio
464 AND timestamp = $q_timestamp";
465 warn "Query: ".$query."\n";
466 $sth = $dbh->prepare($query);
469 # now fix up the remaining priorities....
470 fixpriority($data->{'priority'}, $biblio);
471 my $branchcode = $data->{'branchcode'};
478 my $dbh = C4::Context->dbh;
479 $borr = $dbh->quote($borr);
481 my $query = "SELECT * FROM reserves
482 WHERE borrowernumber = $borr
483 AND reserves.found = 'W'
484 AND cancellationdate is NULL";
485 my $sth = $dbh->prepare($query);
489 if (my $data=$sth->fetchrow_hashref) {
490 $itemswaiting[$cnt] =$data;
494 return ($cnt,\@itemswaiting);
497 =item Findgroupreserve
499 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
501 I don't know what this does, because I don't understand how reserve
502 constraints work. I think the idea is that you reserve a particular
503 biblio, and the constraint allows you to restrict it to a given
504 biblioitem (e.g., if you want to borrow the audio book edition of "The
505 Prophet", rather than the first available publication).
507 C<&Findgroupreserve> returns a two-element array:
509 C<$count> is the number of elements in C<@results>.
511 C<@results> is an array of references-to-hash whose keys are mostly
512 fields from the reserves table of the Koha database, plus
517 sub Findgroupreserve {
518 my ($bibitem,$biblio)=@_;
519 my $dbh = C4::Context->dbh;
520 $bibitem=$dbh->quote($bibitem);
521 my $query = "SELECT reserves.biblionumber AS biblionumber,
522 reserves.borrowernumber AS borrowernumber,
523 reserves.reservedate AS reservedate,
524 reserves.branchcode AS branchcode,
525 reserves.cancellationdate AS cancellationdate,
526 reserves.found AS found,
527 reserves.reservenotes AS reservenotes,
528 reserves.priority AS priority,
529 reserves.timestamp AS timestamp,
530 reserveconstraints.biblioitemnumber AS biblioitemnumber,
531 reserves.itemnumber AS itemnumber
532 FROM reserves LEFT JOIN reserveconstraints
533 ON reserves.biblionumber = reserveconstraints.biblionumber
534 WHERE reserves.biblionumber = $biblio
535 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
536 AND reserves.borrowernumber = reserveconstraints.borrowernumber
537 AND reserves.reservedate =reserveconstraints.reservedate )
538 OR reserves.constrainttype='a' )
539 AND reserves.cancellationdate is NULL
540 AND (reserves.found <> 'F' or reserves.found is NULL)";
541 my $sth=$dbh->prepare($query);
543 # FIXME - $i is unnecessary and bogus
546 while (my $data=$sth->fetchrow_hashref){
547 $results[$i]=$data; # FIXME - Use push
554 # FIXME - A somewhat different version of this function appears in
555 # C4::Reserves. Pick one and stick with it.
559 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
560 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
561 my $dbh = C4::Context->dbh;
562 my $const = lc substr($constraint,0,1);
563 my @datearr = localtime(time);
564 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
566 # updates take place here
569 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
570 my $updquery = "insert into accountlines
571 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
573 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
574 my $usth = $dbh->prepare($updquery);
579 my $query="insert into reserves
580 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
582 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
583 my $sth = $dbh->prepare($query);
587 if (($const eq "o") || ($const eq "e")) {
588 my $numitems = @$bibitems;
590 while ($i < $numitems) {
591 my $biblioitem = @$bibitems[$i];
592 my $query = "insert into
594 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
596 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
597 my $sth = $dbh->prepare($query);
607 # FIXME - A functionally identical version of this function appears in
608 # C4::Reserves. Pick one and stick with it.
609 # XXX - Internal use only
610 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
612 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
614 my $dbh = C4::Context->dbh;
615 my $const = lc substr($constraint,0,1);
616 my $query = "SELECT * FROM borrowers,categories
617 WHERE (borrowernumber = ?)
618 AND (borrowers.categorycode = categories.categorycode)";
619 my $sth = $dbh->prepare($query);
620 $sth->execute($borrnum);
621 my $data = $sth->fetchrow_hashref;
623 my $fee = $data->{'reservefee'};
624 my $cntitems = @->$bibitems;
626 # check for items on issue
627 # first find biblioitem records
629 my $query1 = "SELECT * FROM biblio,biblioitems
630 WHERE (biblio.biblionumber = ?)
631 AND (biblio.biblionumber = biblioitems.biblionumber)";
632 my $sth1 = $dbh->prepare($query1);
633 $sth1->execute($biblionumber);
634 while (my $data1=$sth1->fetchrow_hashref) {
636 push @biblioitems,$data1;
640 while ($x < $cntitems) {
641 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
648 push @biblioitems,$data1;
652 push @biblioitems,$data1;
658 my $cntitemsfound = @biblioitems;
662 while ($x < $cntitemsfound) {
663 my $bitdata = $biblioitems[$x];
664 my $query2 = "SELECT * FROM items
665 WHERE biblioitemnumber = ?";
666 my $sth2 = $dbh->prepare($query2);
667 $sth2->execute($bitdata->{'biblioitemnumber'});
668 while (my $itdata=$sth2->fetchrow_hashref) {
669 my $query3 = "SELECT * FROM issues
671 AND returndate IS NULL";
673 my $sth3 = $dbh->prepare($query3);
674 $sth3->execute($itdata->{'itemnumber'});
675 if (my $isdata=$sth3->fetchrow_hashref) {
682 if ($allissued == 0) {
683 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
684 my $rsth = $dbh->prepare($rquery);
685 $rsth->execute($biblionumber);
686 if (my $rdata = $rsth->fetchrow_hashref) {
698 my ($env,$bornumber,$dbh)=@_;
700 my $query = "select * from accountlines
701 where (borrowernumber = '$bornumber')
702 order by accountno desc";
703 my $sth = $dbh->prepare($query);
705 if (my $accdata=$sth->fetchrow_hashref){
706 $nextaccntno = $accdata->{'accountno'} + 1;
709 return($nextaccntno);
714 #subroutine to update a reserve
715 my ($rank,$biblio,$borrower,$del,$branch)=@_;
716 my $dbh = C4::Context->dbh;
717 my $query="Update reserves ";
719 $query.="set priority='$rank',branchcode='$branch' where
720 biblionumber=$biblio and borrowernumber=$borrower";
722 $query="Select * from reserves where biblionumber=$biblio and
723 borrowernumber=$borrower";
724 my $sth=$dbh->prepare($query);
726 my $data=$sth->fetchrow_hashref;
728 $query="Select * from reserves where biblionumber=$biblio and
729 priority > '$data->{'priority'}' and cancellationdate is NULL
731 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
732 $sth2->execute || die $sth2->errstr;
733 while (my $data=$sth2->fetchrow_hashref){
734 $data->{'priority'}--;
735 $query="Update reserves set priority=$data->{'priority'} where
736 biblionumber=$data->{'biblionumber'} and
737 borrowernumber=$data->{'borrowernumber'}";
738 my $sth3=$dbh->prepare($query);
739 $sth3->execute || die $sth3->errstr;
743 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
744 and borrowernumber=$borrower";
746 my $sth=$dbh->prepare($query);
753 #subroutine to update a reserve
754 my ($rank,$biblio,$borrower,$branch)=@_;
755 return if $rank eq "W";
756 return if $rank eq "n";
757 my $dbh = C4::Context->dbh;
758 if ($rank eq "del") {
759 my $query = "UPDATE reserves SET cancellationdate=now()
760 WHERE biblionumber = ?
761 AND borrowernumber = ?
762 AND cancellationdate is NULL
763 AND (found <> 'F' or found is NULL)";
764 my $sth=$dbh->prepare($query);
765 $sth->execute($biblio, $borrower);
768 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
769 WHERE biblionumber = ?
770 AND borrowernumber = ?
771 AND cancellationdate is NULL
772 AND (found <> 'F' or found is NULL)";
773 my $sth=$dbh->prepare($query);
774 $sth->execute($rank, $branch, $biblio, $borrower);
780 sub getreservetitle {
781 my ($biblio,$bor,$date,$timestamp)=@_;
782 my $dbh = C4::Context->dbh;
783 my $query="Select * from reserveconstraints,biblioitems where
784 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
785 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
786 = $bor and reserveconstraints.reservedate='$date' and
787 reserveconstraints.timestamp=$timestamp";
788 my $sth=$dbh->prepare($query);
791 my $data=$sth->fetchrow_hashref;