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) ?
113 # FIXME: not keen on quote() and interpolation either, but it looks safe
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
138 # Find all books for the given patron.
139 $query .= " where borrowers.borrowernumber = $bor
140 and reserves.borrowernumber = borrowers.borrowernumber
141 and reserves.biblionumber = biblio.biblionumber
142 and cancellationdate is NULL and
143 (found <> 'F' or found is NULL)";
145 $query.=" order by priority";
146 my $sth=$dbh->prepare($query);
149 while (my $data=$sth->fetchrow_hashref){
150 # FIXME - What is this if-statement doing? How do constraints work?
151 if ($data->{'constrainttype'} eq 'o') {
152 my $csth=$dbh->prepare("SELECT biblioitemnumber FROM reserveconstraints
153 WHERE biblionumber = ?
154 AND borrowernumber = ?
155 AND reservedate = ?");
156 $csth->execute($data->{'biblionumber'}, $data->{'borrowernumber'}, $data->{'reservedate'});
157 my ($bibitemno) = $csth->fetchrow_array;
159 # Look up the book we just found.
160 my $bdata = C4::Search::bibitemdata($bibitemno);
161 # Add the results of this latest search to the current
163 # FIXME - An 'each' would probably be more efficient.
164 foreach my $key (keys %$bdata) {
165 $data->{$key} = $bdata->{$key};
168 push @results, $data;
171 return($#results+1,\@results);
176 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
178 Find a book in the reserves.
180 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
181 Either one, but not both, may be false. If both are specified,
182 C<&CheckReserves> uses C<$itemnumber>.
184 $itemnubmer can be false, in which case uses the barcode. (Never uses
185 both. $itemnumber gets priority).
187 As I understand it, C<&CheckReserves> looks for the given item in the
188 reserves. If it is found, that's a match, and C<$status> is set to
191 Otherwise, it finds the most important item in the reserves with the
192 same biblio number as this book (I'm not clear on this) and returns it
193 with C<$status> set to C<Reserved>.
195 C<&CheckReserves> returns a two-element list:
197 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
199 C<$reserve> is the reserve item that matched. It is a
200 reference-to-hash whose keys are mostly the fields of the reserves
201 table in the Koha database.
206 my ($item, $barcode) = @_;
207 # warn "In CheckReserves: itemnumber = $item";
208 my $dbh = C4::Context->dbh;
211 my $qitem=$dbh->quote($item);
212 # Look up the item by itemnumber
213 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
214 FROM items, biblioitems, itemtypes
215 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
216 AND biblioitems.itemtype = itemtypes.itemtype
217 AND itemnumber=$qitem");
219 my $qbc=$dbh->quote($barcode);
220 # Look up the item by barcode
221 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
222 FROM items, biblioitems, itemtypes
223 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
224 AND biblioitems.itemtype = itemtypes.itemtype
226 # FIXME - This function uses $item later on. Ought to set it here.
229 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
231 # if item is not for loan it cannot be reserved either.....
232 return (0, 0) if ($notforloan);
233 # get the reserves...
234 # Find this item in the reserves
235 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
236 # $priority and $highest are used to find the most important item
237 # in the list returned by &Findgroupreserve. (The lower $priority,
238 # the more important the item.)
239 # $highest is the most important item we've seen so far.
240 my $priority = 10000000;
243 foreach my $res (@reserves) {
244 # FIXME - $item might be undefined or empty: the caller
245 # might be searching by barcode.
246 if ($res->{'itemnumber'} == $item) {
248 return ("Waiting", $res);
250 # See if this item is more important than what we've got
252 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
253 $priority = $res->{'priority'};
260 # If we get this far, then no exact match was found. Print the
261 # most important item on the list. I think this tells us who's
262 # next in line to get this book.
263 if ($highest) { # FIXME - $highest might be undefined
264 $highest->{'itemnumber'} = $item;
265 return ("Reserved", $highest);
273 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
277 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
278 cancel, but not both: if both are given, C<&CancelReserve> does
281 C<$borrowernumber> is the borrower number of the patron on whose
282 behalf the book was reserved.
284 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
285 priorities of the other people who are waiting on the book.
290 my ($biblio, $item, $borr) = @_;
291 my $dbh = C4::Context->dbh;
292 #warn "In CancelReserve";
293 if (($item and $borr) and (not $biblio)) {
294 # removing a waiting reserve record....
295 # update the database...
296 my $sth = $dbh->prepare("update reserves set cancellationdate = now(),
300 and borrowernumber = ?");
301 $sth->execute($item,$borr);
304 if (($biblio and $borr) and (not $item)) {
306 # removing a reserve record....
308 # get the prioritiy on this record....
311 my $sth=$dbh->prepare("SELECT priority FROM reserves
312 WHERE biblionumber = ?
313 AND borrowernumber = ?
314 AND cancellationdate is NULL
315 AND (found <> 'F' or found is NULL)");
316 $sth->execute($biblio,$borr);
317 ($priority) = $sth->fetchrow_array;
321 # update the database, removing the record...
323 my $sth = $dbh->prepare("update reserves set cancellationdate = now(),
326 where biblionumber = ?
327 and borrowernumber = ?
328 and cancellationdate is NULL
329 and (found <> 'F' or found is NULL)");
330 $sth->execute($biblio,$borr);
334 # now fix the priority on the others....
335 fixpriority($priority, $biblio);
341 &FillReserve($reserve);
343 Fill a reserve. If I understand this correctly, this means that the
344 reserved book has been found and given to the patron who reserved it.
346 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
347 whose keys are fields from the reserves table in the Koha database.
353 my $dbh = C4::Context->dbh;
355 # fill in a reserve record....
356 # FIXME - Remove some of the redundancy here
357 my $biblio = $res->{'biblionumber'}; my $qbiblio =$biblio;
358 my $borr = $res->{'borrowernumber'};
359 my $resdate = $res->{'reservedate'};
361 # get the priority on this record....
364 my $query = "SELECT priority FROM reserves
365 WHERE biblionumber = ?
366 AND borrowernumber = ?
367 AND reservedate = ?";
368 my $sth=$dbh->prepare($query);
369 $sth->execute($qbiblio,$borr,$resdate);
370 ($priority) = $sth->fetchrow_array;
374 # update the database...
376 my $query = "UPDATE reserves SET found = 'F',
378 WHERE biblionumber = ?
380 AND borrowernumber = ?";
381 my $sth = $dbh->prepare($query);
382 $sth->execute($qbiblio,$resdate,$borr);
386 # now fix the priority on the others (if the priority wasn't
387 # already sorted!)....
388 unless ($priority == 0) {
389 fixpriority($priority, $biblio);
393 # Only used internally
394 # Decrements (makes more important) the reserves for all of the
395 # entries waiting on the given book, if their priority is > $priority.
397 my ($priority, $biblio) = @_;
398 my $dbh = C4::Context->dbh;
399 my ($count, $reserves) = FindReserves($biblio);
400 foreach my $rec (@$reserves) {
401 if ($rec->{'priority'} > $priority) {
402 my $sth = $dbh->prepare("UPDATE reserves SET priority = ?
403 WHERE biblionumber = ?
404 AND borrowernumber = ?
405 AND reservedate = ?");
406 $sth->execute($rec->{'priority'},$rec->{'biblionumber'},$rec->{'borrowernumber'},$rec->{'reservedate'});
414 my ($item, $borr) = @_;
415 my $dbh = C4::Context->dbh;
416 # get priority and biblionumber....
417 my $sth = $dbh->prepare("SELECT reserves.priority as priority,
418 reserves.biblionumber as biblionumber,
419 reserves.branchcode as branchcode,
420 reserves.timestamp as timestamp
422 WHERE reserves.biblionumber = items.biblionumber
423 AND items.itemnumber = ?
424 AND reserves.borrowernumber = ?
425 AND reserves.cancellationdate is NULL
426 AND (reserves.found <> 'F' or reserves.found is NULL)");
427 $sth->execute($item,$borr);
428 my $data = $sth->fetchrow_hashref;
430 my $biblio = $data->{'biblionumber'};
431 my $timestamp = $data->{'timestamp'};
432 # update reserves record....
433 $sth = $dbh->prepare("UPDATE reserves SET priority = 0, found = 'W', itemnumber = ?
434 WHERE borrowernumber = ?
437 $sth->execute($item,$borr,$biblio,$timestamp);
439 # now fix up the remaining priorities....
440 fixpriority($data->{'priority'}, $biblio);
441 my $branchcode = $data->{'branchcode'};
448 my $dbh = C4::Context->dbh;
450 my $sth = $dbh->prepare("SELECT * FROM reserves
451 WHERE borrowernumber = ?
452 AND reserves.found = 'W'
453 AND cancellationdate is NULL");
454 $sth->execute($borr);
455 while (my $data=$sth->fetchrow_hashref) {
456 push(@itemswaiting,$data);
459 return (scalar(@itemswaiting),\@itemswaiting);
462 =item Findgroupreserve
464 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
466 I don't know what this does, because I don't understand how reserve
467 constraints work. I think the idea is that you reserve a particular
468 biblio, and the constraint allows you to restrict it to a given
469 biblioitem (e.g., if you want to borrow the audio book edition of "The
470 Prophet", rather than the first available publication).
472 C<&Findgroupreserve> returns a two-element array:
474 C<$count> is the number of elements in C<@results>.
476 C<@results> is an array of references-to-hash whose keys are mostly
477 fields from the reserves table of the Koha database, plus
482 sub Findgroupreserve {
483 my ($bibitem,$biblio)=@_;
484 my $dbh = C4::Context->dbh;
485 my $sth=$dbh->prepare("SELECT reserves.biblionumber AS biblionumber,
486 reserves.borrowernumber AS borrowernumber,
487 reserves.reservedate AS reservedate,
488 reserves.branchcode AS branchcode,
489 reserves.cancellationdate AS cancellationdate,
490 reserves.found AS found,
491 reserves.reservenotes AS reservenotes,
492 reserves.priority AS priority,
493 reserves.timestamp AS timestamp,
494 reserveconstraints.biblioitemnumber AS biblioitemnumber,
495 reserves.itemnumber AS itemnumber
496 FROM reserves LEFT JOIN reserveconstraints
497 ON reserves.biblionumber = reserveconstraints.biblionumber
498 WHERE reserves.biblionumber = ?
499 AND ( ( reserveconstraints.biblioitemnumber = ?
500 AND reserves.borrowernumber = reserveconstraints.borrowernumber
501 AND reserves.reservedate =reserveconstraints.reservedate )
502 OR reserves.constrainttype='a' )
503 AND reserves.cancellationdate is NULL
504 AND (reserves.found <> 'F' or reserves.found is NULL)");
505 $sth->execute($biblio, $bibitem);
507 while (my $data=$sth->fetchrow_hashref){
508 push(@results,$data);
511 return(scalar(@results),@results);
514 # FIXME - A somewhat different version of this function appears in
515 # C4::Reserves. Pick one and stick with it.
519 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
520 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
521 my $dbh = C4::Context->dbh;
522 my $const = lc substr($constraint,0,1);
523 my @datearr = localtime(time);
524 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
526 # updates take place here
529 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
530 my $usth = $dbh->prepare("insert into accountlines
531 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
533 (?,?,now(),?,?,'Res',?)");
534 $usth->execute($borrnum,$nextacctno,$fee,'Reserve Charge - $title',$fee);
538 my $sth = $dbh->prepare("insert into reserves
539 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
540 values (?,?,?,?,?,?,?)");
541 $sth->execute($borrnum,$biblionumber,$resdate,$branch,$const,$priority,$notes);
544 if (($const eq "o") || ($const eq "e")) {
545 my $numitems = @$bibitems;
547 while ($i < $numitems) {
548 my $biblioitem = @$bibitems[$i];
549 my $sth = $dbh->prepare("insert into
551 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
553 $sth->execute($borrnum,$biblionumber,$resdate,$biblioitem);
562 # FIXME - A functionally identical version of this function appears in
563 # C4::Reserves. Pick one and stick with it.
564 # XXX - Internal use only
565 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
567 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
569 my $dbh = C4::Context->dbh;
570 my $const = lc substr($constraint,0,1);
571 my $sth = $dbh->prepare("SELECT * FROM borrowers,categories
572 WHERE (borrowernumber = ?)
573 AND (borrowers.categorycode = categories.categorycode)");
574 $sth->execute($borrnum);
575 my $data = $sth->fetchrow_hashref;
577 my $fee = $data->{'reservefee'};
578 my $cntitems = @->$bibitems;
580 # check for items on issue
581 # first find biblioitem records
583 my $sth1 = $dbh->prepare("SELECT * FROM biblio,biblioitems
584 WHERE (biblio.biblionumber = ?)
585 AND (biblio.biblionumber = biblioitems.biblionumber)");
586 $sth1->execute($biblionumber);
587 while (my $data1=$sth1->fetchrow_hashref) {
589 push @biblioitems,$data1;
593 while ($x < $cntitems) {
594 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
601 push @biblioitems,$data1;
605 push @biblioitems,$data1;
611 my $cntitemsfound = @biblioitems;
615 while ($x < $cntitemsfound) {
616 my $bitdata = $biblioitems[$x];
617 my $sth2 = $dbh->prepare("SELECT * FROM items
618 WHERE biblioitemnumber = ?");
619 $sth2->execute($bitdata->{'biblioitemnumber'});
620 while (my $itdata=$sth2->fetchrow_hashref) {
621 my $sth3 = $dbh->prepare("SELECT * FROM issues
623 AND returndate IS NULL");
624 $sth3->execute($itdata->{'itemnumber'});
625 if (my $isdata=$sth3->fetchrow_hashref) {
632 if ($allissued == 0) {
633 my $rsth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
634 $rsth->execute($biblionumber);
635 if (my $rdata = $rsth->fetchrow_hashref) {
647 my ($env,$bornumber,$dbh)=@_;
649 my $sth = $dbh->prepare("select * from accountlines
650 where (borrowernumber = ?)
651 order by accountno desc");
652 $sth->execute($bornumber);
653 if (my $accdata=$sth->fetchrow_hashref){
654 $nextaccntno = $accdata->{'accountno'} + 1;
657 return($nextaccntno);
662 #subroutine to update a reserve
663 my ($rank,$biblio,$borrower,$del,$branch)=@_;
664 my $dbh = C4::Context->dbh;
666 my $sth = $dbh->prepare("Update reserves set priority=?,branchcode=? where
667 biblionumber=? and borrowernumber=?");
668 $sth->execute($rank,$branch,$biblio,$borrower);
671 my $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
673 $sth->execute($biblio,$borrower);
674 my $data=$sth->fetchrow_hashref;
676 $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
677 priority > ? and cancellationdate is NULL
678 order by priority") || die $dbh->errstr;
679 $sth->execute($biblio,$data->{'priority'}) || die $sth->errstr;
680 while (my $data=$sth->fetchrow_hashref){
681 $data->{'priority'}--;
682 my $sth3=$dbh->prepare("Update reserves set priority=?
683 where biblionumber=? and borrowernumber=?");
684 $sth3->execute($data->{'priority'},$data->{'biblionumber'},$data->{'borrowernumber'}) || die $sth3->errstr;
688 $sth=$dbh->prepare("update reserves set cancellationdate=now() where biblionumber=?
689 and borrowernumber=?");
690 $sth->execute($biblio,$borrower);
697 #subroutine to update a reserve
698 my ($rank,$biblio,$borrower,$branch)=@_;
699 return if $rank eq "W";
700 return if $rank eq "n";
701 my $dbh = C4::Context->dbh;
702 if ($rank eq "del") {
703 my $sth=$dbh->prepare("UPDATE reserves SET cancellationdate=now()
704 WHERE biblionumber = ?
705 AND borrowernumber = ?
706 AND cancellationdate is NULL
707 AND (found <> 'F' or found is NULL)");
708 $sth->execute($biblio, $borrower);
711 my $sth=$dbh->prepare("UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
712 WHERE biblionumber = ?
713 AND borrowernumber = ?
714 AND cancellationdate is NULL
715 AND (found <> 'F' or found is NULL)");
716 $sth->execute($rank, $branch, $biblio, $borrower);
722 sub getreservetitle {
723 my ($biblio,$bor,$date,$timestamp)=@_;
724 my $dbh = C4::Context->dbh;
725 my $sth=$dbh->prepare("Select * from reserveconstraints,biblioitems where
726 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
727 and reserveconstraints.biblionumber=? and reserveconstraints.borrowernumber
728 = ? and reserveconstraints.reservedate=? and
729 reserveconstraints.timestamp=?");
730 $sth->execute($biblio,$bor,$date,$timestamp);
731 my $data=$sth->fetchrow_hashref;