5 # Copyright 2000-2002 Katipo Communications
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
27 # FIXME - C4::Reserves2 uses C4::Search, which uses C4::Reserves2.
28 # So Perl complains that all of the functions here get redefined.
31 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
33 # set the version for version checking
55 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &UpdateReserve &getreservetitle &Findgroupreserve);
57 # make all your functions, whether exported or not;
61 ($count, $results) = &FindReserves($biblionumber, $borrowernumber);
63 Looks books up in the reserves. C<$biblionumber> is the biblionumber
64 of the book to look up. C<$borrowernumber> is the borrower number of a
65 patron whose books to look up.
67 Either C<$biblionumber> or C<$borrowernumber> may be the empty string,
68 but not both. If both are specified, C<&FindReserves> looks up the
69 given book for the given patron. If only C<$biblionumber> is
70 specified, C<&FindReserves> looks up that book for all patrons. If
71 only C<$borrowernumber> is specified, C<&FindReserves> looks up all of
72 that patron's reserves. If neither is specified, C<&FindReserves>
75 For each book thus found, C<&FindReserves> checks the reserve
76 constraints and does something I don't understand.
78 C<&FindReserves> returns a two-element array:
80 C<$count> is the number of elements in C<$results>.
82 C<$results> is a reference-to-array; each element is a
83 reference-to-hash, whose keys are (I think) all of the fields of the
84 reserves, borrowers, and biblio tables of the Koha database.
90 my $dbh = C4::Context->dbh;
91 # Find the desired items in the reserves
92 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
93 FROM reserves,borrowers,biblio ";
94 # FIXME - These three bits of SQL seem to contain a fair amount of
95 # redundancy. Wouldn't it be better to have a @clauses array, add
96 # one or two clauses as necessary, then join(" AND ", @clauses) ?
98 $bib = $dbh->quote($bib);
100 # Both $bib and $bor specified
101 # Find a particular book for a particular patron
102 $bor = $dbh->quote($bor);
103 $query .= " where reserves.biblionumber = $bib
104 and borrowers.borrowernumber = $bor
105 and reserves.borrowernumber = borrowers.borrowernumber
106 and biblio.biblionumber = $bib
107 and cancellationdate is NULL
108 and (found <> 'F' or found is NULL)";
110 # $bib specified, but not $bor
111 # Find a particular book for all patrons
112 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
113 and biblio.biblionumber = $bib
114 and reserves.biblionumber = $bib
115 and cancellationdate is NULL
116 and (found <> 'F' or found is NULL)";
119 # FIXME - Check that $bor was given
122 # Find all books for the given patron.
123 $query .= " where borrowers.borrowernumber = $bor
124 and reserves.borrowernumber = borrowers.borrowernumber
125 and reserves.biblionumber = biblio.biblionumber
126 and cancellationdate is NULL and
127 (found <> 'F' or found is NULL)";
129 $query.=" order by priority";
130 my $sth=$dbh->prepare($query);
132 # FIXME - $i is unnecessary and bogus
135 while (my $data=$sth->fetchrow_hashref){
136 # FIXME - What is this if-statement doing? How do constraints work?
137 if ($data->{'constrainttype'} eq 'o') {
138 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
139 WHERE biblionumber = ?
140 AND borrowernumber = ?
141 AND reservedate = ?";
142 my $csth=$dbh->prepare($conquery);
143 # FIXME - Why use separate variables for this?
144 my $bibn = $data->{'biblionumber'};
145 my $born = $data->{'borrowernumber'};
146 my $resd = $data->{'reservedate'};
147 $csth->execute($bibn, $born, $resd);
148 my ($bibitemno) = $csth->fetchrow_array;
150 # Look up the book we just found.
151 my $bdata = C4::Search::bibitemdata($bibitemno);
152 # Add the results of this latest search to the current
154 # FIXME - An 'each' would probably be more efficient.
155 foreach my $key (keys %$bdata) {
156 $data->{$key} = $bdata->{$key};
159 $results[$i]=$data; # FIXME - Use push @results
164 return($i,\@results);
169 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
171 Find a book in the reserves.
173 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
174 Either one, but not both, may be false. If both are specified,
175 C<&CheckReserves> uses C<$itemnumber>.
177 $itemnubmer can be false, in which case uses the barcode. (Never uses
178 both. $itemnumber gets priority).
180 As I understand it, C<&CheckReserves> looks for the given item in the
181 reserves. If it is found, that's a match, and C<$status> is set to
184 Otherwise, it finds the most important item in the reserves with the
185 same biblio number as this book (I'm not clear on this) and returns it
186 with C<$status> set to C<Reserved>.
188 C<&CheckReserves> returns a two-element list:
190 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
192 C<$reserve> is the reserve item that matched. It is a
193 reference-to-hash whose keys are mostly the fields of the reserves
194 table in the Koha database.
199 my ($item, $barcode) = @_;
200 # warn "In CheckReserves: itemnumber = $item";
201 my $dbh = C4::Context->dbh;
204 my $qitem=$dbh->quote($item);
205 # Look up the item by itemnumber
206 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
207 FROM items, biblioitems, itemtypes
208 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
209 AND biblioitems.itemtype = itemtypes.itemtype
210 AND itemnumber=$qitem");
212 my $qbc=$dbh->quote($barcode);
213 # Look up the item by barcode
214 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
215 FROM items, biblioitems, itemtypes
216 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
217 AND biblioitems.itemtype = itemtypes.itemtype
219 # FIXME - This function uses $item later on. Ought to set it here.
222 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
224 # if item is not for loan it cannot be reserved either.....
225 return (0, 0) if ($notforloan);
226 # get the reserves...
227 # Find this item in the reserves
228 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
229 # $priority and $highest are used to find the most important item
230 # in the list returned by &Findgroupreserve. (The lower $priority,
231 # the more important the item.)
232 # $highest is the most important item we've seen so far.
233 my $priority = 10000000;
236 foreach my $res (@reserves) {
237 # FIXME - $item might be undefined or empty: the caller
238 # might be searching by barcode.
239 if ($res->{'itemnumber'} == $item) {
241 return ("Waiting", $res);
243 # See if this item is more important than what we've got
245 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
246 $priority = $res->{'priority'};
253 # If we get this far, then no exact match was found. Print the
254 # most important item on the list. I think this tells us who's
255 # next in line to get this book.
256 if ($highest) { # FIXME - $highest might be undefined
257 $highest->{'itemnumber'} = $item;
258 return ("Reserved", $highest);
266 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
270 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
271 cancel, but not both: if both are given, C<&CancelReserve> does
274 C<$borrowernumber> is the borrower number of the patron on whose
275 behalf the book was reserved.
277 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
278 priorities of the other people who are waiting on the book.
283 my ($biblio, $item, $borr) = @_;
284 my $dbh = C4::Context->dbh;
285 #warn "In CancelReserve";
286 if (($item and $borr) and (not $biblio)) {
287 # removing a waiting reserve record....
288 $item = $dbh->quote($item);
289 $borr = $dbh->quote($borr);
290 # update the database...
291 # FIXME - Use $dbh->do()
292 my $query = "update reserves set cancellationdate = now(),
295 where itemnumber = $item
296 and borrowernumber = $borr";
297 my $sth = $dbh->prepare($query);
301 if (($biblio and $borr) and (not $item)) {
303 # removing a reserve record....
304 my $q_biblio = $dbh->quote($biblio);
305 $borr = $dbh->quote($borr);
307 # get the prioritiy on this record....
310 my $query = "SELECT priority FROM reserves
311 WHERE biblionumber = $q_biblio
312 AND borrowernumber = $borr
313 AND cancellationdate is NULL
314 AND (found <> 'F' or found is NULL)";
315 my $sth=$dbh->prepare($query);
317 ($priority) = $sth->fetchrow_array;
321 # update the database, removing the record...
323 my $query = "update reserves set cancellationdate = now(),
326 where biblionumber = $q_biblio
327 and borrowernumber = $borr
328 and cancellationdate is NULL
329 and (found <> 'F' or found is NULL)";
330 my $sth = $dbh->prepare($query);
335 # now fix the priority on the others....
336 fixpriority($priority, $biblio);
342 &FillReserve($reserve);
344 Fill a reserve. If I understand this correctly, this means that the
345 reserved book has been found and given to the patron who reserved it.
347 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
348 whose keys are fields from the reserves table in the Koha database.
354 my $dbh = C4::Context->dbh;
356 # fill in a reserve record....
357 # FIXME - Remove some of the redundancy here
358 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
359 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
360 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
362 # get the priority on this record....
365 my $query = "SELECT priority FROM reserves
366 WHERE biblionumber = $qbiblio
367 AND borrowernumber = $borr
368 AND reservedate = $resdate)";
369 my $sth=$dbh->prepare($query);
371 ($priority) = $sth->fetchrow_array;
375 # update the database...
377 my $query = "UPDATE reserves SET found = 'F',
379 WHERE biblionumber = $qbiblio
380 AND reservedate = $resdate
381 AND borrowernumber = $borr";
382 my $sth = $dbh->prepare($query);
387 # now fix the priority on the others (if the priority wasn't
388 # already sorted!)....
389 unless ($priority == 0) {
390 fixpriority($priority, $biblio);
394 # Only used internally
395 # Decrements (makes more important) the reserves for all of the
396 # entries waiting on the given book, if their priority is > $priority.
398 my ($priority, $biblio) = @_;
399 my $dbh = C4::Context->dbh;
400 my ($count, $reserves) = FindReserves($biblio);
401 foreach my $rec (@$reserves) {
402 if ($rec->{'priority'} > $priority) {
403 # FIXME - Rewrite this without so much duplication and
405 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
406 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
407 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
408 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
409 my $query = "UPDATE reserves SET priority = $newpr
410 WHERE biblionumber = $nbib
411 AND borrowernumber = $nbor
412 AND reservedate = $nresd";
414 my $sth = $dbh->prepare($query);
423 my ($item, $borr) = @_;
424 my $dbh = C4::Context->dbh;
425 $item = $dbh->quote($item);
426 $borr = $dbh->quote($borr);
427 # get priority and biblionumber....
428 my $query = "SELECT reserves.priority as priority,
429 reserves.biblionumber as biblionumber,
430 reserves.branchcode as branchcode,
431 reserves.timestamp as timestamp
433 WHERE reserves.biblionumber = items.biblionumber
434 AND items.itemnumber = $item
435 AND reserves.borrowernumber = $borr
436 AND reserves.cancellationdate is NULL
437 AND (reserves.found <> 'F' or reserves.found is NULL)";
438 my $sth = $dbh->prepare($query);
440 my $data = $sth->fetchrow_hashref;
442 my $biblio = $data->{'biblionumber'};
443 my $timestamp = $data->{'timestamp'};
444 my $q_biblio = $dbh->quote($biblio);
445 my $q_timestamp = $dbh->quote($timestamp);
446 warn "Timestamp: ".$timestamp."\n";
447 # update reserves record....
448 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
449 WHERE borrowernumber = $borr
450 AND biblionumber = $q_biblio
451 AND timestamp = $q_timestamp";
452 warn "Query: ".$query."\n";
453 $sth = $dbh->prepare($query);
456 # now fix up the remaining priorities....
457 fixpriority($data->{'priority'}, $biblio);
458 my $branchcode = $data->{'branchcode'};
465 my $dbh = C4::Context->dbh;
466 $borr = $dbh->quote($borr);
468 my $query = "SELECT * FROM reserves
469 WHERE borrowernumber = $borr
470 AND reserves.found = 'W'
471 AND cancellationdate is NULL";
472 my $sth = $dbh->prepare($query);
476 if (my $data=$sth->fetchrow_hashref) {
477 $itemswaiting[$cnt] =$data;
481 return ($cnt,\@itemswaiting);
484 =item Findgroupreserve
486 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
488 I don't know what this does, because I don't understand how reserve
489 constraints work. I think the idea is that you reserve a particular
490 biblio, and the constraint allows you to restrict it to a given
491 biblioitem (e.g., if you want to borrow the audio book edition of "The
492 Prophet", rather than the first available publication).
494 C<&Findgroupreserve> returns a two-element array:
496 C<$count> is the number of elements in C<@results>.
498 C<@results> is an array of references-to-hash whose keys are mostly
499 fields from the reserves table of the Koha database, plus
504 sub Findgroupreserve {
505 my ($bibitem,$biblio)=@_;
506 my $dbh = C4::Context->dbh;
507 $bibitem=$dbh->quote($bibitem);
508 my $query = "SELECT reserves.biblionumber AS biblionumber,
509 reserves.borrowernumber AS borrowernumber,
510 reserves.reservedate AS reservedate,
511 reserves.branchcode AS branchcode,
512 reserves.cancellationdate AS cancellationdate,
513 reserves.found AS found,
514 reserves.reservenotes AS reservenotes,
515 reserves.priority AS priority,
516 reserves.timestamp AS timestamp,
517 reserveconstraints.biblioitemnumber AS biblioitemnumber,
518 reserves.itemnumber AS itemnumber
519 FROM reserves LEFT JOIN reserveconstraints
520 ON reserves.biblionumber = reserveconstraints.biblionumber
521 WHERE reserves.biblionumber = $biblio
522 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
523 AND reserves.borrowernumber = reserveconstraints.borrowernumber
524 AND reserves.reservedate =reserveconstraints.reservedate )
525 OR reserves.constrainttype='a' )
526 AND reserves.cancellationdate is NULL
527 AND (reserves.found <> 'F' or reserves.found is NULL)";
528 my $sth=$dbh->prepare($query);
530 # FIXME - $i is unnecessary and bogus
533 while (my $data=$sth->fetchrow_hashref){
534 $results[$i]=$data; # FIXME - Use push
541 # FIXME - A somewhat different version of this function appears in
542 # C4::Reserves. Pick one and stick with it.
546 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
547 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
548 my $dbh = C4::Context->dbh;
549 my $const = lc substr($constraint,0,1);
550 my @datearr = localtime(time);
551 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
553 # updates take place here
556 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
557 my $updquery = "insert into accountlines
558 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
560 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
561 my $usth = $dbh->prepare($updquery);
566 my $query="insert into reserves
567 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
569 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
570 my $sth = $dbh->prepare($query);
574 if (($const eq "o") || ($const eq "e")) {
575 my $numitems = @$bibitems;
577 while ($i < $numitems) {
578 my $biblioitem = @$bibitems[$i];
579 my $query = "insert into
581 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
583 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
584 my $sth = $dbh->prepare($query);
594 # FIXME - A functionally identical version of this function appears in
595 # C4::Reserves. Pick one and stick with it.
596 # XXX - Internal use only
598 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
600 my $dbh = C4::Context->dbh;
601 my $const = lc substr($constraint,0,1);
602 my $query = "SELECT * FROM borrowers,categories
603 WHERE (borrowernumber = ?)
604 AND (borrowers.categorycode = categories.categorycode)";
605 my $sth = $dbh->prepare($query);
606 $sth->execute($borrnum);
607 my $data = $sth->fetchrow_hashref;
609 my $fee = $data->{'reservefee'};
610 my $cntitems = @->$bibitems;
612 # check for items on issue
613 # first find biblioitem records
615 my $query1 = "SELECT * FROM biblio,biblioitems
616 WHERE (biblio.biblionumber = ?)
617 AND (biblio.biblionumber = biblioitems.biblionumber)";
618 my $sth1 = $dbh->prepare($query1);
619 $sth1->execute($biblionumber);
620 while (my $data1=$sth1->fetchrow_hashref) {
622 push @biblioitems,$data1;
626 while ($x < $cntitems) {
627 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
634 push @biblioitems,$data1;
638 push @biblioitems,$data1;
644 my $cntitemsfound = @biblioitems;
648 while ($x < $cntitemsfound) {
649 my $bitdata = $biblioitems[$x];
650 my $query2 = "SELECT * FROM items
651 WHERE biblioitemnumber = ?";
652 my $sth2 = $dbh->prepare($query2);
653 $sth2->execute($bitdata->{'biblioitemnumber'});
654 while (my $itdata=$sth2->fetchrow_hashref) {
655 my $query3 = "SELECT * FROM issues
657 AND returndate IS NULL";
659 my $sth3 = $dbh->prepare($query3);
660 $sth3->execute($itdata->{'itemnumber'});
661 if (my $isdata=$sth3->fetchrow_hashref) {
668 if ($allissued == 0) {
669 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
670 my $rsth = $dbh->prepare($rquery);
671 $rsth->execute($biblionumber);
672 if (my $rdata = $rsth->fetchrow_hashref) {
684 my ($env,$bornumber,$dbh)=@_;
686 my $query = "select * from accountlines
687 where (borrowernumber = '$bornumber')
688 order by accountno desc";
689 my $sth = $dbh->prepare($query);
691 if (my $accdata=$sth->fetchrow_hashref){
692 $nextaccntno = $accdata->{'accountno'} + 1;
695 return($nextaccntno);
700 #subroutine to update a reserve
701 my ($rank,$biblio,$borrower,$del,$branch)=@_;
702 my $dbh = C4::Context->dbh;
703 my $query="Update reserves ";
705 $query.="set priority='$rank',branchcode='$branch' where
706 biblionumber=$biblio and borrowernumber=$borrower";
708 $query="Select * from reserves where biblionumber=$biblio and
709 borrowernumber=$borrower";
710 my $sth=$dbh->prepare($query);
712 my $data=$sth->fetchrow_hashref;
714 $query="Select * from reserves where biblionumber=$biblio and
715 priority > '$data->{'priority'}' and cancellationdate is NULL
717 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
718 $sth2->execute || die $sth2->errstr;
719 while (my $data=$sth2->fetchrow_hashref){
720 $data->{'priority'}--;
721 $query="Update reserves set priority=$data->{'priority'} where
722 biblionumber=$data->{'biblionumber'} and
723 borrowernumber=$data->{'borrowernumber'}";
724 my $sth3=$dbh->prepare($query);
725 $sth3->execute || die $sth3->errstr;
729 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
730 and borrowernumber=$borrower";
732 my $sth=$dbh->prepare($query);
739 #subroutine to update a reserve
740 my ($rank,$biblio,$borrower,$branch)=@_;
741 return if $rank eq "W";
742 return if $rank eq "n";
743 my $dbh = C4::Context->dbh;
744 if ($rank eq "del") {
745 my $query = "UPDATE reserves SET cancellationdate=now()
746 WHERE biblionumber = ?
747 AND borrowernumber = ?
748 AND cancellationdate is NULL
749 AND (found <> 'F' or found is NULL)";
750 my $sth=$dbh->prepare($query);
751 $sth->execute($biblio, $borrower);
754 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
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($rank, $branch, $biblio, $borrower);
766 sub getreservetitle {
767 my ($biblio,$bor,$date,$timestamp)=@_;
768 my $dbh = C4::Context->dbh;
769 my $query="Select * from reserveconstraints,biblioitems where
770 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
771 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
772 = $bor and reserveconstraints.reservedate='$date' and
773 reserveconstraints.timestamp=$timestamp";
774 my $sth=$dbh->prepare($query);
776 my $data=$sth->fetchrow_hashref;