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 my $dbh = C4::Context->dbh;
108 # Find the desired items in the reserves
109 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
110 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
139 # Find all books for the given patron.
140 $query .= " where borrowers.borrowernumber = $bor
141 and reserves.borrowernumber = borrowers.borrowernumber
142 and reserves.biblionumber = biblio.biblionumber
143 and cancellationdate is NULL and
144 (found <> 'F' or found is NULL)";
146 $query.=" order by priority";
147 my $sth=$dbh->prepare($query);
149 # FIXME - $i is unnecessary and bogus
152 while (my $data=$sth->fetchrow_hashref){
153 # FIXME - What is this if-statement doing? How do constraints work?
154 if ($data->{'constrainttype'} eq 'o') {
155 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
156 WHERE biblionumber = ?
157 AND borrowernumber = ?
158 AND reservedate = ?";
159 my $csth=$dbh->prepare($conquery);
160 # FIXME - Why use separate variables for this?
161 my $bibn = $data->{'biblionumber'};
162 my $born = $data->{'borrowernumber'};
163 my $resd = $data->{'reservedate'};
164 $csth->execute($bibn, $born, $resd);
165 my ($bibitemno) = $csth->fetchrow_array;
167 # Look up the book we just found.
168 my $bdata = C4::Search::bibitemdata($bibitemno);
169 # Add the results of this latest search to the current
171 # FIXME - An 'each' would probably be more efficient.
172 foreach my $key (keys %$bdata) {
173 $data->{$key} = $bdata->{$key};
176 $results[$i]=$data; # FIXME - Use push @results
181 return($i,\@results);
186 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
188 Find a book in the reserves.
190 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
191 Either one, but not both, may be false. If both are specified,
192 C<&CheckReserves> uses C<$itemnumber>.
194 $itemnubmer can be false, in which case uses the barcode. (Never uses
195 both. $itemnumber gets priority).
197 As I understand it, C<&CheckReserves> looks for the given item in the
198 reserves. If it is found, that's a match, and C<$status> is set to
201 Otherwise, it finds the most important item in the reserves with the
202 same biblio number as this book (I'm not clear on this) and returns it
203 with C<$status> set to C<Reserved>.
205 C<&CheckReserves> returns a two-element list:
207 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
209 C<$reserve> is the reserve item that matched. It is a
210 reference-to-hash whose keys are mostly the fields of the reserves
211 table in the Koha database.
216 my ($item, $barcode) = @_;
217 # warn "In CheckReserves: itemnumber = $item";
218 my $dbh = C4::Context->dbh;
221 my $qitem=$dbh->quote($item);
222 # Look up the item by itemnumber
223 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
224 FROM items, biblioitems, itemtypes
225 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
226 AND biblioitems.itemtype = itemtypes.itemtype
227 AND itemnumber=$qitem");
229 my $qbc=$dbh->quote($barcode);
230 # Look up the item by barcode
231 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
232 FROM items, biblioitems, itemtypes
233 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
234 AND biblioitems.itemtype = itemtypes.itemtype
236 # FIXME - This function uses $item later on. Ought to set it here.
239 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
241 # if item is not for loan it cannot be reserved either.....
242 return (0, 0) if ($notforloan);
243 # get the reserves...
244 # Find this item in the reserves
245 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
246 # $priority and $highest are used to find the most important item
247 # in the list returned by &Findgroupreserve. (The lower $priority,
248 # the more important the item.)
249 # $highest is the most important item we've seen so far.
250 my $priority = 10000000;
253 foreach my $res (@reserves) {
254 # FIXME - $item might be undefined or empty: the caller
255 # might be searching by barcode.
256 if ($res->{'itemnumber'} == $item) {
258 return ("Waiting", $res);
260 # See if this item is more important than what we've got
262 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
263 $priority = $res->{'priority'};
270 # If we get this far, then no exact match was found. Print the
271 # most important item on the list. I think this tells us who's
272 # next in line to get this book.
273 if ($highest) { # FIXME - $highest might be undefined
274 $highest->{'itemnumber'} = $item;
275 return ("Reserved", $highest);
283 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
287 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
288 cancel, but not both: if both are given, C<&CancelReserve> does
291 C<$borrowernumber> is the borrower number of the patron on whose
292 behalf the book was reserved.
294 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
295 priorities of the other people who are waiting on the book.
300 my ($biblio, $item, $borr) = @_;
301 my $dbh = C4::Context->dbh;
302 #warn "In CancelReserve";
303 if (($item and $borr) and (not $biblio)) {
304 # removing a waiting reserve record....
305 $item = $dbh->quote($item);
306 $borr = $dbh->quote($borr);
307 # update the database...
308 # FIXME - Use $dbh->do()
309 my $query = "update reserves set cancellationdate = now(),
312 where itemnumber = $item
313 and borrowernumber = $borr";
314 my $sth = $dbh->prepare($query);
318 if (($biblio and $borr) and (not $item)) {
320 # removing a reserve record....
321 my $q_biblio = $dbh->quote($biblio);
322 $borr = $dbh->quote($borr);
324 # get the prioritiy on this record....
327 my $query = "SELECT priority FROM reserves
328 WHERE biblionumber = $q_biblio
329 AND borrowernumber = $borr
330 AND cancellationdate is NULL
331 AND (found <> 'F' or found is NULL)";
332 my $sth=$dbh->prepare($query);
334 ($priority) = $sth->fetchrow_array;
338 # update the database, removing the record...
340 my $query = "update reserves set cancellationdate = now(),
343 where biblionumber = $q_biblio
344 and borrowernumber = $borr
345 and cancellationdate is NULL
346 and (found <> 'F' or found is NULL)";
347 my $sth = $dbh->prepare($query);
352 # now fix the priority on the others....
353 fixpriority($priority, $biblio);
359 &FillReserve($reserve);
361 Fill a reserve. If I understand this correctly, this means that the
362 reserved book has been found and given to the patron who reserved it.
364 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
365 whose keys are fields from the reserves table in the Koha database.
371 my $dbh = C4::Context->dbh;
373 # fill in a reserve record....
374 # FIXME - Remove some of the redundancy here
375 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
376 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
377 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
379 # get the priority on this record....
382 my $query = "SELECT priority FROM reserves
383 WHERE biblionumber = $qbiblio
384 AND borrowernumber = $borr
385 AND reservedate = $resdate)";
386 my $sth=$dbh->prepare($query);
388 ($priority) = $sth->fetchrow_array;
392 # update the database...
394 my $query = "UPDATE reserves SET found = 'F',
396 WHERE biblionumber = $qbiblio
397 AND reservedate = $resdate
398 AND borrowernumber = $borr";
399 my $sth = $dbh->prepare($query);
404 # now fix the priority on the others (if the priority wasn't
405 # already sorted!)....
406 unless ($priority == 0) {
407 fixpriority($priority, $biblio);
411 # Only used internally
412 # Decrements (makes more important) the reserves for all of the
413 # entries waiting on the given book, if their priority is > $priority.
415 my ($priority, $biblio) = @_;
416 my $dbh = C4::Context->dbh;
417 my ($count, $reserves) = FindReserves($biblio);
418 foreach my $rec (@$reserves) {
419 if ($rec->{'priority'} > $priority) {
420 # FIXME - Rewrite this without so much duplication and
422 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
423 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
424 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
425 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
426 my $query = "UPDATE reserves SET priority = $newpr
427 WHERE biblionumber = $nbib
428 AND borrowernumber = $nbor
429 AND reservedate = $nresd";
431 my $sth = $dbh->prepare($query);
440 my ($item, $borr) = @_;
441 my $dbh = C4::Context->dbh;
442 $item = $dbh->quote($item);
443 $borr = $dbh->quote($borr);
444 # get priority and biblionumber....
445 my $query = "SELECT reserves.priority as priority,
446 reserves.biblionumber as biblionumber,
447 reserves.branchcode as branchcode,
448 reserves.timestamp as timestamp
450 WHERE reserves.biblionumber = items.biblionumber
451 AND items.itemnumber = $item
452 AND reserves.borrowernumber = $borr
453 AND reserves.cancellationdate is NULL
454 AND (reserves.found <> 'F' or reserves.found is NULL)";
455 my $sth = $dbh->prepare($query);
457 my $data = $sth->fetchrow_hashref;
459 my $biblio = $data->{'biblionumber'};
460 my $timestamp = $data->{'timestamp'};
461 my $q_biblio = $dbh->quote($biblio);
462 my $q_timestamp = $dbh->quote($timestamp);
463 warn "Timestamp: ".$timestamp."\n";
464 # update reserves record....
465 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
466 WHERE borrowernumber = $borr
467 AND biblionumber = $q_biblio
468 AND timestamp = $q_timestamp";
469 warn "Query: ".$query."\n";
470 $sth = $dbh->prepare($query);
473 # now fix up the remaining priorities....
474 fixpriority($data->{'priority'}, $biblio);
475 my $branchcode = $data->{'branchcode'};
482 my $dbh = C4::Context->dbh;
483 $borr = $dbh->quote($borr);
485 my $query = "SELECT * FROM reserves
486 WHERE borrowernumber = $borr
487 AND reserves.found = 'W'
488 AND cancellationdate is NULL";
489 my $sth = $dbh->prepare($query);
493 if (my $data=$sth->fetchrow_hashref) {
494 $itemswaiting[$cnt] =$data;
498 return ($cnt,\@itemswaiting);
501 =item Findgroupreserve
503 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
505 I don't know what this does, because I don't understand how reserve
506 constraints work. I think the idea is that you reserve a particular
507 biblio, and the constraint allows you to restrict it to a given
508 biblioitem (e.g., if you want to borrow the audio book edition of "The
509 Prophet", rather than the first available publication).
511 C<&Findgroupreserve> returns a two-element array:
513 C<$count> is the number of elements in C<@results>.
515 C<@results> is an array of references-to-hash whose keys are mostly
516 fields from the reserves table of the Koha database, plus
521 sub Findgroupreserve {
522 my ($bibitem,$biblio)=@_;
523 my $dbh = C4::Context->dbh;
524 $bibitem=$dbh->quote($bibitem);
525 my $query = "SELECT reserves.biblionumber AS biblionumber,
526 reserves.borrowernumber AS borrowernumber,
527 reserves.reservedate AS reservedate,
528 reserves.branchcode AS branchcode,
529 reserves.cancellationdate AS cancellationdate,
530 reserves.found AS found,
531 reserves.reservenotes AS reservenotes,
532 reserves.priority AS priority,
533 reserves.timestamp AS timestamp,
534 reserveconstraints.biblioitemnumber AS biblioitemnumber,
535 reserves.itemnumber AS itemnumber
536 FROM reserves LEFT JOIN reserveconstraints
537 ON reserves.biblionumber = reserveconstraints.biblionumber
538 WHERE reserves.biblionumber = $biblio
539 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
540 AND reserves.borrowernumber = reserveconstraints.borrowernumber
541 AND reserves.reservedate =reserveconstraints.reservedate )
542 OR reserves.constrainttype='a' )
543 AND reserves.cancellationdate is NULL
544 AND (reserves.found <> 'F' or reserves.found is NULL)";
545 my $sth=$dbh->prepare($query);
547 # FIXME - $i is unnecessary and bogus
550 while (my $data=$sth->fetchrow_hashref){
551 $results[$i]=$data; # FIXME - Use push
558 # FIXME - A somewhat different version of this function appears in
559 # C4::Reserves. Pick one and stick with it.
563 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
564 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
565 my $dbh = C4::Context->dbh;
566 my $const = lc substr($constraint,0,1);
567 my @datearr = localtime(time);
568 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
570 # updates take place here
573 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
574 my $updquery = "insert into accountlines
575 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
577 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
578 my $usth = $dbh->prepare($updquery);
583 my $query="insert into reserves
584 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
586 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
587 my $sth = $dbh->prepare($query);
591 if (($const eq "o") || ($const eq "e")) {
592 my $numitems = @$bibitems;
594 while ($i < $numitems) {
595 my $biblioitem = @$bibitems[$i];
596 my $query = "insert into
598 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
600 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
601 my $sth = $dbh->prepare($query);
611 # FIXME - A functionally identical version of this function appears in
612 # C4::Reserves. Pick one and stick with it.
613 # XXX - Internal use only
614 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
616 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
618 my $dbh = C4::Context->dbh;
619 my $const = lc substr($constraint,0,1);
620 my $query = "SELECT * FROM borrowers,categories
621 WHERE (borrowernumber = ?)
622 AND (borrowers.categorycode = categories.categorycode)";
623 my $sth = $dbh->prepare($query);
624 $sth->execute($borrnum);
625 my $data = $sth->fetchrow_hashref;
627 my $fee = $data->{'reservefee'};
628 my $cntitems = @->$bibitems;
630 # check for items on issue
631 # first find biblioitem records
633 my $query1 = "SELECT * FROM biblio,biblioitems
634 WHERE (biblio.biblionumber = ?)
635 AND (biblio.biblionumber = biblioitems.biblionumber)";
636 my $sth1 = $dbh->prepare($query1);
637 $sth1->execute($biblionumber);
638 while (my $data1=$sth1->fetchrow_hashref) {
640 push @biblioitems,$data1;
644 while ($x < $cntitems) {
645 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
652 push @biblioitems,$data1;
656 push @biblioitems,$data1;
662 my $cntitemsfound = @biblioitems;
666 while ($x < $cntitemsfound) {
667 my $bitdata = $biblioitems[$x];
668 my $query2 = "SELECT * FROM items
669 WHERE biblioitemnumber = ?";
670 my $sth2 = $dbh->prepare($query2);
671 $sth2->execute($bitdata->{'biblioitemnumber'});
672 while (my $itdata=$sth2->fetchrow_hashref) {
673 my $query3 = "SELECT * FROM issues
675 AND returndate IS NULL";
677 my $sth3 = $dbh->prepare($query3);
678 $sth3->execute($itdata->{'itemnumber'});
679 if (my $isdata=$sth3->fetchrow_hashref) {
686 if ($allissued == 0) {
687 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
688 my $rsth = $dbh->prepare($rquery);
689 $rsth->execute($biblionumber);
690 if (my $rdata = $rsth->fetchrow_hashref) {
702 my ($env,$bornumber,$dbh)=@_;
704 my $query = "select * from accountlines
705 where (borrowernumber = '$bornumber')
706 order by accountno desc";
707 my $sth = $dbh->prepare($query);
709 if (my $accdata=$sth->fetchrow_hashref){
710 $nextaccntno = $accdata->{'accountno'} + 1;
713 return($nextaccntno);
718 #subroutine to update a reserve
719 my ($rank,$biblio,$borrower,$del,$branch)=@_;
720 my $dbh = C4::Context->dbh;
721 my $query="Update reserves ";
723 $query.="set priority='$rank',branchcode='$branch' where
724 biblionumber=$biblio and borrowernumber=$borrower";
726 $query="Select * from reserves where biblionumber=$biblio and
727 borrowernumber=$borrower";
728 my $sth=$dbh->prepare($query);
730 my $data=$sth->fetchrow_hashref;
732 $query="Select * from reserves where biblionumber=$biblio and
733 priority > '$data->{'priority'}' and cancellationdate is NULL
735 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
736 $sth2->execute || die $sth2->errstr;
737 while (my $data=$sth2->fetchrow_hashref){
738 $data->{'priority'}--;
739 $query="Update reserves set priority=$data->{'priority'} where
740 biblionumber=$data->{'biblionumber'} and
741 borrowernumber=$data->{'borrowernumber'}";
742 my $sth3=$dbh->prepare($query);
743 $sth3->execute || die $sth3->errstr;
747 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
748 and borrowernumber=$borrower";
750 my $sth=$dbh->prepare($query);
757 #subroutine to update a reserve
758 my ($rank,$biblio,$borrower,$branch)=@_;
759 return if $rank eq "W";
760 return if $rank eq "n";
761 my $dbh = C4::Context->dbh;
762 if ($rank eq "del") {
763 my $query = "UPDATE reserves SET cancellationdate=now()
764 WHERE biblionumber = ?
765 AND borrowernumber = ?
766 AND cancellationdate is NULL
767 AND (found <> 'F' or found is NULL)";
768 my $sth=$dbh->prepare($query);
769 $sth->execute($biblio, $borrower);
772 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
773 WHERE biblionumber = ?
774 AND borrowernumber = ?
775 AND cancellationdate is NULL
776 AND (found <> 'F' or found is NULL)";
777 my $sth=$dbh->prepare($query);
778 $sth->execute($rank, $branch, $biblio, $borrower);
784 sub getreservetitle {
785 my ($biblio,$bor,$date,$timestamp)=@_;
786 my $dbh = C4::Context->dbh;
787 my $query="Select * from reserveconstraints,biblioitems where
788 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
789 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
790 = $bor and reserveconstraints.reservedate='$date' and
791 reserveconstraints.timestamp=$timestamp";
792 my $sth=$dbh->prepare($query);
794 my $data=$sth->fetchrow_hashref;