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 $bibitem=$dbh->quote($bibitem);
517 my $query = "SELECT reserves.biblionumber AS biblionumber,
518 reserves.borrowernumber AS borrowernumber,
519 reserves.reservedate AS reservedate,
520 reserves.branchcode AS branchcode,
521 reserves.cancellationdate AS cancellationdate,
522 reserves.found AS found,
523 reserves.reservenotes AS reservenotes,
524 reserves.priority AS priority,
525 reserves.timestamp AS timestamp,
526 reserveconstraints.biblioitemnumber AS biblioitemnumber,
527 reserves.itemnumber AS itemnumber
528 FROM reserves LEFT JOIN reserveconstraints
529 ON reserves.biblionumber = reserveconstraints.biblionumber
530 WHERE reserves.biblionumber = ?
531 AND ( ( reserveconstraints.biblioitemnumber = ?
532 AND reserves.borrowernumber = reserveconstraints.borrowernumber
533 AND reserves.reservedate =reserveconstraints.reservedate )
534 OR reserves.constrainttype='a' )
535 AND reserves.cancellationdate is NULL
536 AND (reserves.found <> 'F' or reserves.found is NULL)";
537 my $sth=$dbh->prepare($query);
538 $sth->execute($biblio, $bibitem);
539 # FIXME - $i is unnecessary and bogus
542 while (my $data=$sth->fetchrow_hashref){
543 $results[$i]=$data; # FIXME - Use push
550 # FIXME - A somewhat different version of this function appears in
551 # C4::Reserves. Pick one and stick with it.
555 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
556 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
557 my $dbh = C4::Context->dbh;
558 my $const = lc substr($constraint,0,1);
559 my @datearr = localtime(time);
560 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
562 # updates take place here
565 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
566 my $updquery = "insert into accountlines
567 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
569 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
570 my $usth = $dbh->prepare($updquery);
575 my $query="insert into reserves
576 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
578 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
579 my $sth = $dbh->prepare($query);
583 if (($const eq "o") || ($const eq "e")) {
584 my $numitems = @$bibitems;
586 while ($i < $numitems) {
587 my $biblioitem = @$bibitems[$i];
588 my $query = "insert into
590 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
592 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
593 my $sth = $dbh->prepare($query);
603 # FIXME - A functionally identical version of this function appears in
604 # C4::Reserves. Pick one and stick with it.
605 # XXX - Internal use only
606 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
608 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
610 my $dbh = C4::Context->dbh;
611 my $const = lc substr($constraint,0,1);
612 my $query = "SELECT * FROM borrowers,categories
613 WHERE (borrowernumber = ?)
614 AND (borrowers.categorycode = categories.categorycode)";
615 my $sth = $dbh->prepare($query);
616 $sth->execute($borrnum);
617 my $data = $sth->fetchrow_hashref;
619 my $fee = $data->{'reservefee'};
620 my $cntitems = @->$bibitems;
622 # check for items on issue
623 # first find biblioitem records
625 my $query1 = "SELECT * FROM biblio,biblioitems
626 WHERE (biblio.biblionumber = ?)
627 AND (biblio.biblionumber = biblioitems.biblionumber)";
628 my $sth1 = $dbh->prepare($query1);
629 $sth1->execute($biblionumber);
630 while (my $data1=$sth1->fetchrow_hashref) {
632 push @biblioitems,$data1;
636 while ($x < $cntitems) {
637 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
644 push @biblioitems,$data1;
648 push @biblioitems,$data1;
654 my $cntitemsfound = @biblioitems;
658 while ($x < $cntitemsfound) {
659 my $bitdata = $biblioitems[$x];
660 my $query2 = "SELECT * FROM items
661 WHERE biblioitemnumber = ?";
662 my $sth2 = $dbh->prepare($query2);
663 $sth2->execute($bitdata->{'biblioitemnumber'});
664 while (my $itdata=$sth2->fetchrow_hashref) {
665 my $query3 = "SELECT * FROM issues
667 AND returndate IS NULL";
669 my $sth3 = $dbh->prepare($query3);
670 $sth3->execute($itdata->{'itemnumber'});
671 if (my $isdata=$sth3->fetchrow_hashref) {
678 if ($allissued == 0) {
679 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
680 my $rsth = $dbh->prepare($rquery);
681 $rsth->execute($biblionumber);
682 if (my $rdata = $rsth->fetchrow_hashref) {
694 my ($env,$bornumber,$dbh)=@_;
696 my $query = "select * from accountlines
697 where (borrowernumber = '$bornumber')
698 order by accountno desc";
699 my $sth = $dbh->prepare($query);
701 if (my $accdata=$sth->fetchrow_hashref){
702 $nextaccntno = $accdata->{'accountno'} + 1;
705 return($nextaccntno);
710 #subroutine to update a reserve
711 my ($rank,$biblio,$borrower,$del,$branch)=@_;
712 my $dbh = C4::Context->dbh;
713 my $query="Update reserves ";
715 $query.="set priority='$rank',branchcode='$branch' where
716 biblionumber=$biblio and borrowernumber=$borrower";
718 $query="Select * from reserves where biblionumber=$biblio and
719 borrowernumber=$borrower";
720 my $sth=$dbh->prepare($query);
722 my $data=$sth->fetchrow_hashref;
724 $query="Select * from reserves where biblionumber=$biblio and
725 priority > '$data->{'priority'}' and cancellationdate is NULL
727 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
728 $sth2->execute || die $sth2->errstr;
729 while (my $data=$sth2->fetchrow_hashref){
730 $data->{'priority'}--;
731 $query="Update reserves set priority=$data->{'priority'} where
732 biblionumber=$data->{'biblionumber'} and
733 borrowernumber=$data->{'borrowernumber'}";
734 my $sth3=$dbh->prepare($query);
735 $sth3->execute || die $sth3->errstr;
739 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
740 and borrowernumber=$borrower";
742 my $sth=$dbh->prepare($query);
749 #subroutine to update a reserve
750 my ($rank,$biblio,$borrower,$branch)=@_;
751 return if $rank eq "W";
752 return if $rank eq "n";
753 my $dbh = C4::Context->dbh;
754 if ($rank eq "del") {
755 my $query = "UPDATE reserves SET cancellationdate=now()
756 WHERE biblionumber = ?
757 AND borrowernumber = ?
758 AND cancellationdate is NULL
759 AND (found <> 'F' or found is NULL)";
760 my $sth=$dbh->prepare($query);
761 $sth->execute($biblio, $borrower);
764 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
765 WHERE biblionumber = ?
766 AND borrowernumber = ?
767 AND cancellationdate is NULL
768 AND (found <> 'F' or found is NULL)";
769 my $sth=$dbh->prepare($query);
770 $sth->execute($rank, $branch, $biblio, $borrower);
776 sub getreservetitle {
777 my ($biblio,$bor,$date,$timestamp)=@_;
778 my $dbh = C4::Context->dbh;
779 my $query="Select * from reserveconstraints,biblioitems where
780 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
781 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
782 = $bor and reserveconstraints.reservedate='$date' and
783 reserveconstraints.timestamp=$timestamp";
784 my $sth=$dbh->prepare($query);
786 my $data=$sth->fetchrow_hashref;