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, reserves.timestamp as rtimestamp FROM reserves,borrowers,biblio ";
110 # FIXME - These three bits of SQL seem to contain a fair amount of
111 # redundancy. Wouldn't it be better to have a @clauses array, add
112 # one or two clauses as necessary, then join(" AND ", @clauses) ?
114 $bib = $dbh->quote($bib);
116 # Both $bib and $bor specified
117 # Find a particular book for a particular patron
118 $bor = $dbh->quote($bor);
119 $query .= " where reserves.biblionumber = $bib
120 and borrowers.borrowernumber = $bor
121 and reserves.borrowernumber = borrowers.borrowernumber
122 and biblio.biblionumber = $bib
123 and cancellationdate is NULL
124 and (found <> 'F' or found is NULL)";
126 # $bib specified, but not $bor
127 # Find a particular book for all patrons
128 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
129 and biblio.biblionumber = $bib
130 and reserves.biblionumber = $bib
131 and cancellationdate is NULL
132 and (found <> 'F' or found is NULL)";
135 # FIXME - Check that $bor was given
137 # Find all books for the given patron.
138 $query .= " where borrowers.borrowernumber = $bor
139 and reserves.borrowernumber = borrowers.borrowernumber
140 and reserves.biblionumber = biblio.biblionumber
141 and cancellationdate is NULL and
142 (found <> 'F' or found is NULL)";
144 $query.=" order by priority";
145 my $sth=$dbh->prepare($query);
148 while (my $data=$sth->fetchrow_hashref){
149 # FIXME - What is this if-statement doing? How do constraints work?
150 if ($data->{'constrainttype'} eq 'o') {
151 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
152 WHERE biblionumber = ?
153 AND borrowernumber = ?
154 AND reservedate = ?";
155 my $csth=$dbh->prepare($conquery);
156 # FIXME - Why use separate variables for this?
157 my $bibn = $data->{'biblionumber'};
158 my $born = $data->{'borrowernumber'};
159 my $resd = $data->{'reservedate'};
160 $csth->execute($bibn, $born, $resd);
161 my ($bibitemno) = $csth->fetchrow_array;
163 # Look up the book we just found.
164 my $bdata = C4::Search::bibitemdata($bibitemno);
165 # Add the results of this latest search to the current
167 # FIXME - An 'each' would probably be more efficient.
168 foreach my $key (keys %$bdata) {
169 $data->{$key} = $bdata->{$key};
172 push @results, $data;
175 return($#results+1,\@results);
180 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
182 Find a book in the reserves.
184 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
185 Either one, but not both, may be false. If both are specified,
186 C<&CheckReserves> uses C<$itemnumber>.
188 $itemnubmer can be false, in which case uses the barcode. (Never uses
189 both. $itemnumber gets priority).
191 As I understand it, C<&CheckReserves> looks for the given item in the
192 reserves. If it is found, that's a match, and C<$status> is set to
195 Otherwise, it finds the most important item in the reserves with the
196 same biblio number as this book (I'm not clear on this) and returns it
197 with C<$status> set to C<Reserved>.
199 C<&CheckReserves> returns a two-element list:
201 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
203 C<$reserve> is the reserve item that matched. It is a
204 reference-to-hash whose keys are mostly the fields of the reserves
205 table in the Koha database.
210 my ($item, $barcode) = @_;
211 # warn "In CheckReserves: itemnumber = $item";
212 my $dbh = C4::Context->dbh;
215 my $qitem=$dbh->quote($item);
216 # Look up the item by itemnumber
217 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
218 FROM items, biblioitems, itemtypes
219 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
220 AND biblioitems.itemtype = itemtypes.itemtype
221 AND itemnumber=$qitem");
223 my $qbc=$dbh->quote($barcode);
224 # Look up the item by barcode
225 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
226 FROM items, biblioitems, itemtypes
227 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
228 AND biblioitems.itemtype = itemtypes.itemtype
230 # FIXME - This function uses $item later on. Ought to set it here.
233 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
235 # if item is not for loan it cannot be reserved either.....
236 return (0, 0) if ($notforloan);
237 # get the reserves...
238 # Find this item in the reserves
239 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
240 # $priority and $highest are used to find the most important item
241 # in the list returned by &Findgroupreserve. (The lower $priority,
242 # the more important the item.)
243 # $highest is the most important item we've seen so far.
244 my $priority = 10000000;
247 foreach my $res (@reserves) {
248 # FIXME - $item might be undefined or empty: the caller
249 # might be searching by barcode.
250 if ($res->{'itemnumber'} == $item) {
252 return ("Waiting", $res);
254 # See if this item is more important than what we've got
256 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
257 $priority = $res->{'priority'};
264 # If we get this far, then no exact match was found. Print the
265 # most important item on the list. I think this tells us who's
266 # next in line to get this book.
267 if ($highest) { # FIXME - $highest might be undefined
268 $highest->{'itemnumber'} = $item;
269 return ("Reserved", $highest);
277 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
281 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
282 cancel, but not both: if both are given, C<&CancelReserve> does
285 C<$borrowernumber> is the borrower number of the patron on whose
286 behalf the book was reserved.
288 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
289 priorities of the other people who are waiting on the book.
294 my ($biblio, $item, $borr) = @_;
295 my $dbh = C4::Context->dbh;
296 #warn "In CancelReserve";
297 if (($item and $borr) and (not $biblio)) {
298 # removing a waiting reserve record....
299 $item = $dbh->quote($item);
300 $borr = $dbh->quote($borr);
301 # update the database...
302 # FIXME - Use $dbh->do()
303 my $query = "update reserves set cancellationdate = now(),
306 where itemnumber = $item
307 and borrowernumber = $borr";
308 my $sth = $dbh->prepare($query);
312 if (($biblio and $borr) and (not $item)) {
314 # removing a reserve record....
315 my $q_biblio = $dbh->quote($biblio);
316 $borr = $dbh->quote($borr);
318 # get the prioritiy on this record....
321 my $query = "SELECT priority FROM reserves
322 WHERE biblionumber = $q_biblio
323 AND borrowernumber = $borr
324 AND cancellationdate is NULL
325 AND (found <> 'F' or found is NULL)";
326 my $sth=$dbh->prepare($query);
328 ($priority) = $sth->fetchrow_array;
332 # update the database, removing the record...
334 my $query = "update reserves set cancellationdate = now(),
337 where biblionumber = $q_biblio
338 and borrowernumber = $borr
339 and cancellationdate is NULL
340 and (found <> 'F' or found is NULL)";
341 my $sth = $dbh->prepare($query);
346 # now fix the priority on the others....
347 fixpriority($priority, $biblio);
353 &FillReserve($reserve);
355 Fill a reserve. If I understand this correctly, this means that the
356 reserved book has been found and given to the patron who reserved it.
358 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
359 whose keys are fields from the reserves table in the Koha database.
365 my $dbh = C4::Context->dbh;
367 # fill in a reserve record....
368 # FIXME - Remove some of the redundancy here
369 my $biblio = $res->{'biblionumber'}; my $qbiblio =$biblio;
370 my $borr = $res->{'borrowernumber'};
371 my $resdate = $res->{'reservedate'};
373 # get the priority on this record....
376 my $query = "SELECT priority FROM reserves
377 WHERE biblionumber = ?
378 AND borrowernumber = ?
379 AND reservedate = ?";
380 my $sth=$dbh->prepare($query);
381 $sth->execute($qbiblio,$borr,$resdate);
382 ($priority) = $sth->fetchrow_array;
386 # update the database...
388 my $query = "UPDATE reserves SET found = 'F',
390 WHERE biblionumber = ?
392 AND borrowernumber = ?";
393 my $sth = $dbh->prepare($query);
394 $sth->execute($qbiblio,$resdate,$borr);
398 # now fix the priority on the others (if the priority wasn't
399 # already sorted!)....
400 unless ($priority == 0) {
401 fixpriority($priority, $biblio);
405 # Only used internally
406 # Decrements (makes more important) the reserves for all of the
407 # entries waiting on the given book, if their priority is > $priority.
409 my ($priority, $biblio) = @_;
410 my $dbh = C4::Context->dbh;
411 my ($count, $reserves) = FindReserves($biblio);
412 foreach my $rec (@$reserves) {
413 if ($rec->{'priority'} > $priority) {
414 # FIXME - Rewrite this without so much duplication and
416 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
417 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
418 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
419 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
420 my $query = "UPDATE reserves SET priority = $newpr
421 WHERE biblionumber = $nbib
422 AND borrowernumber = $nbor
423 AND reservedate = $nresd";
425 my $sth = $dbh->prepare($query);
434 my ($item, $borr) = @_;
435 my $dbh = C4::Context->dbh;
436 $item = $dbh->quote($item);
437 $borr = $dbh->quote($borr);
438 # get priority and biblionumber....
439 my $query = "SELECT reserves.priority as priority,
440 reserves.biblionumber as biblionumber,
441 reserves.branchcode as branchcode,
442 reserves.timestamp as timestamp
444 WHERE reserves.biblionumber = items.biblionumber
445 AND items.itemnumber = $item
446 AND reserves.borrowernumber = $borr
447 AND reserves.cancellationdate is NULL
448 AND (reserves.found <> 'F' or reserves.found is NULL)";
449 my $sth = $dbh->prepare($query);
451 my $data = $sth->fetchrow_hashref;
453 my $biblio = $data->{'biblionumber'};
454 my $timestamp = $data->{'timestamp'};
455 my $q_biblio = $dbh->quote($biblio);
456 my $q_timestamp = $dbh->quote($timestamp);
457 # update reserves record....
458 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
459 WHERE borrowernumber = $borr
460 AND biblionumber = $q_biblio
461 AND timestamp = $q_timestamp";
462 $sth = $dbh->prepare($query);
465 # now fix up the remaining priorities....
466 fixpriority($data->{'priority'}, $biblio);
467 my $branchcode = $data->{'branchcode'};
474 my $dbh = C4::Context->dbh;
475 $borr = $dbh->quote($borr);
477 my $query = "SELECT * FROM reserves
478 WHERE borrowernumber = $borr
479 AND reserves.found = 'W'
480 AND cancellationdate is NULL";
481 my $sth = $dbh->prepare($query);
485 if (my $data=$sth->fetchrow_hashref) {
486 $itemswaiting[$cnt] =$data;
490 return ($cnt,\@itemswaiting);
493 =item Findgroupreserve
495 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
497 I don't know what this does, because I don't understand how reserve
498 constraints work. I think the idea is that you reserve a particular
499 biblio, and the constraint allows you to restrict it to a given
500 biblioitem (e.g., if you want to borrow the audio book edition of "The
501 Prophet", rather than the first available publication).
503 C<&Findgroupreserve> returns a two-element array:
505 C<$count> is the number of elements in C<@results>.
507 C<@results> is an array of references-to-hash whose keys are mostly
508 fields from the reserves table of the Koha database, plus
513 sub Findgroupreserve {
514 my ($bibitem,$biblio)=@_;
515 my $dbh = C4::Context->dbh;
516 my $query = "SELECT reserves.biblionumber AS biblionumber,
517 reserves.borrowernumber AS borrowernumber,
518 reserves.reservedate AS reservedate,
519 reserves.branchcode AS branchcode,
520 reserves.cancellationdate AS cancellationdate,
521 reserves.found AS found,
522 reserves.reservenotes AS reservenotes,
523 reserves.priority AS priority,
524 reserves.timestamp AS timestamp,
525 reserveconstraints.biblioitemnumber AS biblioitemnumber,
526 reserves.itemnumber AS itemnumber
527 FROM reserves LEFT JOIN reserveconstraints
528 ON reserves.biblionumber = reserveconstraints.biblionumber
529 WHERE reserves.biblionumber = ?
530 AND ( ( reserveconstraints.biblioitemnumber = ?
531 AND reserves.borrowernumber = reserveconstraints.borrowernumber
532 AND reserves.reservedate =reserveconstraints.reservedate )
533 OR reserves.constrainttype='a' )
534 AND reserves.cancellationdate is NULL
535 AND (reserves.found <> 'F' or reserves.found is NULL)";
536 my $sth=$dbh->prepare($query);
537 $sth->execute($biblio, $bibitem);
538 # FIXME - $i is unnecessary and bogus
541 while (my $data=$sth->fetchrow_hashref){
542 $results[$i]=$data; # FIXME - Use push
549 # FIXME - A somewhat different version of this function appears in
550 # C4::Reserves. Pick one and stick with it.
554 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
555 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
556 my $dbh = C4::Context->dbh;
557 my $const = lc substr($constraint,0,1);
558 my @datearr = localtime(time);
559 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
561 # updates take place here
564 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
565 my $updquery = "insert into accountlines
566 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
568 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
569 my $usth = $dbh->prepare($updquery);
574 my $query="insert into reserves
575 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
577 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
578 my $sth = $dbh->prepare($query);
582 if (($const eq "o") || ($const eq "e")) {
583 my $numitems = @$bibitems;
585 while ($i < $numitems) {
586 my $biblioitem = @$bibitems[$i];
587 my $query = "insert into
589 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
591 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
592 my $sth = $dbh->prepare($query);
602 # FIXME - A functionally identical version of this function appears in
603 # C4::Reserves. Pick one and stick with it.
604 # XXX - Internal use only
605 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
607 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
609 my $dbh = C4::Context->dbh;
610 my $const = lc substr($constraint,0,1);
611 my $query = "SELECT * FROM borrowers,categories
612 WHERE (borrowernumber = ?)
613 AND (borrowers.categorycode = categories.categorycode)";
614 my $sth = $dbh->prepare($query);
615 $sth->execute($borrnum);
616 my $data = $sth->fetchrow_hashref;
618 my $fee = $data->{'reservefee'};
619 my $cntitems = @->$bibitems;
621 # check for items on issue
622 # first find biblioitem records
624 my $query1 = "SELECT * FROM biblio,biblioitems
625 WHERE (biblio.biblionumber = ?)
626 AND (biblio.biblionumber = biblioitems.biblionumber)";
627 my $sth1 = $dbh->prepare($query1);
628 $sth1->execute($biblionumber);
629 while (my $data1=$sth1->fetchrow_hashref) {
631 push @biblioitems,$data1;
635 while ($x < $cntitems) {
636 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
643 push @biblioitems,$data1;
647 push @biblioitems,$data1;
653 my $cntitemsfound = @biblioitems;
657 while ($x < $cntitemsfound) {
658 my $bitdata = $biblioitems[$x];
659 my $query2 = "SELECT * FROM items
660 WHERE biblioitemnumber = ?";
661 my $sth2 = $dbh->prepare($query2);
662 $sth2->execute($bitdata->{'biblioitemnumber'});
663 while (my $itdata=$sth2->fetchrow_hashref) {
664 my $query3 = "SELECT * FROM issues
666 AND returndate IS NULL";
668 my $sth3 = $dbh->prepare($query3);
669 $sth3->execute($itdata->{'itemnumber'});
670 if (my $isdata=$sth3->fetchrow_hashref) {
677 if ($allissued == 0) {
678 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
679 my $rsth = $dbh->prepare($rquery);
680 $rsth->execute($biblionumber);
681 if (my $rdata = $rsth->fetchrow_hashref) {
693 my ($env,$bornumber,$dbh)=@_;
695 my $query = "select * from accountlines
696 where (borrowernumber = '$bornumber')
697 order by accountno desc";
698 my $sth = $dbh->prepare($query);
700 if (my $accdata=$sth->fetchrow_hashref){
701 $nextaccntno = $accdata->{'accountno'} + 1;
704 return($nextaccntno);
709 #subroutine to update a reserve
710 my ($rank,$biblio,$borrower,$del,$branch)=@_;
711 my $dbh = C4::Context->dbh;
712 my $query="Update reserves ";
714 $query.="set priority='$rank',branchcode='$branch' where
715 biblionumber=$biblio and borrowernumber=$borrower";
717 $query="Select * from reserves where biblionumber=$biblio and
718 borrowernumber=$borrower";
719 my $sth=$dbh->prepare($query);
721 my $data=$sth->fetchrow_hashref;
723 $query="Select * from reserves where biblionumber=$biblio and
724 priority > '$data->{'priority'}' and cancellationdate is NULL
726 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
727 $sth2->execute || die $sth2->errstr;
728 while (my $data=$sth2->fetchrow_hashref){
729 $data->{'priority'}--;
730 $query="Update reserves set priority=$data->{'priority'} where
731 biblionumber=$data->{'biblionumber'} and
732 borrowernumber=$data->{'borrowernumber'}";
733 my $sth3=$dbh->prepare($query);
734 $sth3->execute || die $sth3->errstr;
738 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
739 and borrowernumber=$borrower";
741 my $sth=$dbh->prepare($query);
748 #subroutine to update a reserve
749 my ($rank,$biblio,$borrower,$branch)=@_;
750 return if $rank eq "W";
751 return if $rank eq "n";
752 my $dbh = C4::Context->dbh;
753 if ($rank eq "del") {
754 my $query = "UPDATE reserves SET cancellationdate=now()
755 WHERE biblionumber = ?
756 AND borrowernumber = ?
757 AND cancellationdate is NULL
758 AND (found <> 'F' or found is NULL)";
759 my $sth=$dbh->prepare($query);
760 $sth->execute($biblio, $borrower);
763 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
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($rank, $branch, $biblio, $borrower);
775 sub getreservetitle {
776 my ($biblio,$bor,$date,$timestamp)=@_;
777 my $dbh = C4::Context->dbh;
778 my $query="Select * from reserveconstraints,biblioitems where
779 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
780 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
781 = $bor and reserveconstraints.reservedate='$date' and
782 reserveconstraints.timestamp=$timestamp";
783 my $sth=$dbh->prepare($query);
785 my $data=$sth->fetchrow_hashref;