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)) {
302 # removing a reserve record....
303 my $q_biblio = $dbh->quote($biblio);
304 $borr = $dbh->quote($borr);
305 # get the prioritiy on this record....
306 my $query = "SELECT priority FROM reserves
307 WHERE biblionumber = $q_biblio
308 AND borrowernumber = $borr
309 AND cancellationdate is NULL
310 AND (found <> 'F' or found is NULL)";
311 my $sth=$dbh->prepare($query);
313 my ($priority) = $sth->fetchrow_array;
315 # update the database, removing the record...
316 # FIXME - There's already a $query in this scope.
317 my $query = "update reserves set cancellationdate = now(),
320 where biblionumber = $q_biblio
321 and borrowernumber = $borr
322 and cancellationdate is NULL
323 and (found <> 'F' or found is NULL)";
324 # FIXME - There's already a $sth in this scope.
325 my $sth = $dbh->prepare($query);
328 # now fix the priority on the others....
329 fixpriority($priority, $biblio);
335 &FillReserve($reserve);
337 Fill a reserve. If I understand this correctly, this means that the
338 reserved book has been found and given to the patron who reserved it.
340 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
341 whose keys are fields from the reserves table in the Koha database.
347 my $dbh = C4::Context->dbh;
348 # fill in a reserve record....
349 # FIXME - Remove some of the redundancy here
350 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
351 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
352 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
353 # get the priority on this record....
354 my $query = "SELECT priority FROM reserves
355 WHERE biblionumber = $qbiblio
356 AND borrowernumber = $borr
357 AND reservedate = $resdate)";
358 my $sth=$dbh->prepare($query);
360 my ($priority) = $sth->fetchrow_array;
362 # update the database...
363 # FIXME - There's already a $query in this scope.
364 my $query = "UPDATE reserves SET found = 'F',
366 WHERE biblionumber = $qbiblio
367 AND reservedate = $resdate
368 AND borrowernumber = $borr";
369 # FIXME - There's already a $sth in this scope.
370 my $sth = $dbh->prepare($query);
373 # now fix the priority on the others (if the priority wasn't
374 # already sorted!)....
375 unless ($priority == 0) {
376 fixpriority($priority, $biblio);
380 # Only used internally
381 # Decrements (makes more important) the reserves for all of the
382 # entries waiting on the given book, if their priority is > $priority.
384 my ($priority, $biblio) = @_;
385 my $dbh = C4::Context->dbh;
386 my ($count, $reserves) = FindReserves($biblio);
387 foreach my $rec (@$reserves) {
388 if ($rec->{'priority'} > $priority) {
389 # FIXME - Rewrite this without so much duplication and
391 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
392 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
393 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
394 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
395 my $query = "UPDATE reserves SET priority = $newpr
396 WHERE biblionumber = $nbib
397 AND borrowernumber = $nbor
398 AND reservedate = $nresd";
400 my $sth = $dbh->prepare($query);
409 my ($item, $borr) = @_;
410 my $dbh = C4::Context->dbh;
411 $item = $dbh->quote($item);
412 $borr = $dbh->quote($borr);
413 # get priority and biblionumber....
414 my $query = "SELECT reserves.priority as priority,
415 reserves.biblionumber as biblionumber,
416 reserves.branchcode as branchcode,
417 reserves.timestamp as timestamp
419 WHERE reserves.biblionumber = items.biblionumber
420 AND items.itemnumber = $item
421 AND reserves.borrowernumber = $borr
422 AND reserves.cancellationdate is NULL
423 AND (reserves.found <> 'F' or reserves.found is NULL)";
424 my $sth = $dbh->prepare($query);
426 my $data = $sth->fetchrow_hashref;
428 my $biblio = $data->{'biblionumber'};
429 my $timestamp = $data->{'timestamp'};
430 my $q_biblio = $dbh->quote($biblio);
431 my $q_timestamp = $dbh->quote($timestamp);
432 warn "Timestamp: ".$timestamp."\n";
433 # update reserves record....
434 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
435 WHERE borrowernumber = $borr
436 AND biblionumber = $q_biblio
437 AND timestamp = $q_timestamp";
438 warn "Query: ".$query."\n";
439 $sth = $dbh->prepare($query);
442 # now fix up the remaining priorities....
443 fixpriority($data->{'priority'}, $biblio);
444 my $branchcode = $data->{'branchcode'};
451 my $dbh = C4::Context->dbh;
452 $borr = $dbh->quote($borr);
454 my $query = "SELECT * FROM reserves
455 WHERE borrowernumber = $borr
456 AND reserves.found = 'W'
457 AND cancellationdate is NULL";
458 my $sth = $dbh->prepare($query);
462 if (my $data=$sth->fetchrow_hashref) {
463 $itemswaiting[$cnt] =$data;
467 return ($cnt,\@itemswaiting);
470 =item Findgroupreserve
472 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
474 I don't know what this does, because I don't understand how reserve
475 constraints work. I think the idea is that you reserve a particular
476 biblio, and the constraint allows you to restrict it to a given
477 biblioitem (e.g., if you want to borrow the audio book edition of "The
478 Prophet", rather than the first available publication).
480 C<&Findgroupreserve> returns a two-element array:
482 C<$count> is the number of elements in C<@results>.
484 C<@results> is an array of references-to-hash whose keys are mostly
485 fields from the reserves table of the Koha database, plus
490 sub Findgroupreserve {
491 my ($bibitem,$biblio)=@_;
492 my $dbh = C4::Context->dbh;
493 $bibitem=$dbh->quote($bibitem);
494 my $query = "SELECT reserves.biblionumber AS biblionumber,
495 reserves.borrowernumber AS borrowernumber,
496 reserves.reservedate AS reservedate,
497 reserves.branchcode AS branchcode,
498 reserves.cancellationdate AS cancellationdate,
499 reserves.found AS found,
500 reserves.reservenotes AS reservenotes,
501 reserves.priority AS priority,
502 reserves.timestamp AS timestamp,
503 reserveconstraints.biblioitemnumber AS biblioitemnumber,
504 reserves.itemnumber AS itemnumber
505 FROM reserves LEFT JOIN reserveconstraints
506 ON reserves.biblionumber = reserveconstraints.biblionumber
507 WHERE reserves.biblionumber = $biblio
508 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
509 AND reserves.borrowernumber = reserveconstraints.borrowernumber
510 AND reserves.reservedate =reserveconstraints.reservedate )
511 OR reserves.constrainttype='a' )
512 AND reserves.cancellationdate is NULL
513 AND (reserves.found <> 'F' or reserves.found is NULL)";
514 my $sth=$dbh->prepare($query);
516 # FIXME - $i is unnecessary and bogus
519 while (my $data=$sth->fetchrow_hashref){
520 $results[$i]=$data; # FIXME - Use push
527 # FIXME - A somewhat different version of this function appears in
528 # C4::Reserves. Pick one and stick with it.
532 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
533 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
534 my $dbh = C4::Context->dbh;
535 my $const = lc substr($constraint,0,1);
536 my @datearr = localtime(time);
537 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
539 # updates take place here
542 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
543 my $updquery = "insert into accountlines
544 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
546 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
547 my $usth = $dbh->prepare($updquery);
552 my $query="insert into reserves
553 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
555 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
556 my $sth = $dbh->prepare($query);
560 if (($const eq "o") || ($const eq "e")) {
561 my $numitems = @$bibitems;
563 while ($i < $numitems) {
564 my $biblioitem = @$bibitems[$i];
565 my $query = "insert into
567 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
569 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
570 my $sth = $dbh->prepare($query);
580 # FIXME - A functionally identical version of this function appears in
581 # C4::Reserves. Pick one and stick with it.
582 # XXX - Internal use only
584 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
586 my $dbh = C4::Context->dbh;
587 my $const = lc substr($constraint,0,1);
588 my $query = "SELECT * FROM borrowers,categories
589 WHERE (borrowernumber = ?)
590 AND (borrowers.categorycode = categories.categorycode)";
591 my $sth = $dbh->prepare($query);
592 $sth->execute($borrnum);
593 my $data = $sth->fetchrow_hashref;
595 my $fee = $data->{'reservefee'};
596 my $cntitems = @->$bibitems;
598 # check for items on issue
599 # first find biblioitem records
601 my $query1 = "SELECT * FROM biblio,biblioitems
602 WHERE (biblio.biblionumber = ?)
603 AND (biblio.biblionumber = biblioitems.biblionumber)";
604 my $sth1 = $dbh->prepare($query1);
605 $sth1->execute($biblionumber);
606 while (my $data1=$sth1->fetchrow_hashref) {
608 push @biblioitems,$data1;
612 while ($x < $cntitems) {
613 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
620 push @biblioitems,$data1;
624 push @biblioitems,$data1;
630 my $cntitemsfound = @biblioitems;
634 while ($x < $cntitemsfound) {
635 my $bitdata = $biblioitems[$x];
636 my $query2 = "SELECT * FROM items
637 WHERE biblioitemnumber = ?";
638 my $sth2 = $dbh->prepare($query2);
639 $sth2->execute($bitdata->{'biblioitemnumber'});
640 while (my $itdata=$sth2->fetchrow_hashref) {
641 my $query3 = "SELECT * FROM issues
643 AND returndate IS NULL";
645 my $sth3 = $dbh->prepare($query3);
646 $sth3->execute($itdata->{'itemnumber'});
647 if (my $isdata=$sth3->fetchrow_hashref) {
654 if ($allissued == 0) {
655 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
656 my $rsth = $dbh->prepare($rquery);
657 $rsth->execute($biblionumber);
658 if (my $rdata = $rsth->fetchrow_hashref) {
670 my ($env,$bornumber,$dbh)=@_;
672 my $query = "select * from accountlines
673 where (borrowernumber = '$bornumber')
674 order by accountno desc";
675 my $sth = $dbh->prepare($query);
677 if (my $accdata=$sth->fetchrow_hashref){
678 $nextaccntno = $accdata->{'accountno'} + 1;
681 return($nextaccntno);
686 #subroutine to update a reserve
687 my ($rank,$biblio,$borrower,$del,$branch)=@_;
688 my $dbh = C4::Context->dbh;
689 my $query="Update reserves ";
691 $query.="set priority='$rank',branchcode='$branch' where
692 biblionumber=$biblio and borrowernumber=$borrower";
694 $query="Select * from reserves where biblionumber=$biblio and
695 borrowernumber=$borrower";
696 my $sth=$dbh->prepare($query);
698 my $data=$sth->fetchrow_hashref;
700 $query="Select * from reserves where biblionumber=$biblio and
701 priority > '$data->{'priority'}' and cancellationdate is NULL
703 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
704 $sth2->execute || die $sth2->errstr;
705 while (my $data=$sth2->fetchrow_hashref){
706 $data->{'priority'}--;
707 $query="Update reserves set priority=$data->{'priority'} where
708 biblionumber=$data->{'biblionumber'} and
709 borrowernumber=$data->{'borrowernumber'}";
710 my $sth3=$dbh->prepare($query);
711 $sth3->execute || die $sth3->errstr;
715 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
716 and borrowernumber=$borrower";
718 my $sth=$dbh->prepare($query);
725 #subroutine to update a reserve
726 my ($rank,$biblio,$borrower,$branch)=@_;
727 return if $rank eq "W";
728 return if $rank eq "n";
729 my $dbh = C4::Context->dbh;
730 if ($rank eq "del") {
731 my $query = "UPDATE reserves SET cancellationdate=now()
732 WHERE biblionumber = ?
733 AND borrowernumber = ?
734 AND cancellationdate is NULL
735 AND (found <> 'F' or found is NULL)";
736 my $sth=$dbh->prepare($query);
737 $sth->execute($biblio, $borrower);
740 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
741 WHERE biblionumber = ?
742 AND borrowernumber = ?
743 AND cancellationdate is NULL
744 AND (found <> 'F' or found is NULL)";
745 my $sth=$dbh->prepare($query);
746 $sth->execute($rank, $branch, $biblio, $borrower);
752 sub getreservetitle {
753 my ($biblio,$bor,$date,$timestamp)=@_;
754 my $dbh = C4::Context->dbh;
755 my $query="Select * from reserveconstraints,biblioitems where
756 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
757 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
758 = $bor and reserveconstraints.reservedate='$date' and
759 reserveconstraints.timestamp=$timestamp";
760 my $sth=$dbh->prepare($query);
762 my $data=$sth->fetchrow_hashref;