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)) {
305 # removing a reserve record....
306 # get the prioritiy on this record....
308 my $sth=$dbh->prepare("SELECT priority FROM reserves
309 WHERE biblionumber = ?
310 AND borrowernumber = ?
311 AND cancellationdate is NULL
312 AND (found <> 'F' or found is NULL)");
313 $sth->execute($biblio,$borr);
314 ($priority) = $sth->fetchrow_array;
317 # update the database, removing the record...
318 my $sth = $dbh->prepare("update reserves set cancellationdate = now(),
321 where biblionumber = ?
322 and borrowernumber = ?
323 and cancellationdate is NULL
324 and (found <> 'F' or found is NULL)");
325 $sth->execute($biblio,$borr);
327 # now fix the priority on the others....
328 fixpriority($priority, $biblio);
334 &FillReserve($reserve);
336 Fill a reserve. If I understand this correctly, this means that the
337 reserved book has been found and given to the patron who reserved it.
339 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
340 whose keys are fields from the reserves table in the Koha database.
346 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 =$biblio;
351 my $borr = $res->{'borrowernumber'};
352 my $resdate = $res->{'reservedate'};
354 # get the priority on this record....
357 my $query = "SELECT priority FROM reserves
358 WHERE biblionumber = ?
359 AND borrowernumber = ?
360 AND reservedate = ?";
361 my $sth=$dbh->prepare($query);
362 $sth->execute($qbiblio,$borr,$resdate);
363 ($priority) = $sth->fetchrow_array;
367 # update the database...
369 my $query = "UPDATE reserves SET found = 'F',
371 WHERE biblionumber = ?
373 AND borrowernumber = ?";
374 my $sth = $dbh->prepare($query);
375 $sth->execute($qbiblio,$resdate,$borr);
379 # now fix the priority on the others (if the priority wasn't
380 # already sorted!)....
381 unless ($priority == 0) {
382 fixpriority($priority, $biblio);
386 # Only used internally
387 # Decrements (makes more important) the reserves for all of the
388 # entries waiting on the given book, if their priority is > $priority.
390 my ($priority, $biblio) = @_;
391 my $dbh = C4::Context->dbh;
392 my ($count, $reserves) = FindReserves($biblio);
393 foreach my $rec (@$reserves) {
394 if ($rec->{'priority'} > $priority) {
395 my $sth = $dbh->prepare("UPDATE reserves SET priority = ?
396 WHERE biblionumber = ?
397 AND borrowernumber = ?
398 AND reservedate = ?");
399 $sth->execute($rec->{'priority'},$rec->{'biblionumber'},$rec->{'borrowernumber'},$rec->{'reservedate'});
407 my ($item, $borr) = @_;
408 my $dbh = C4::Context->dbh;
409 # get priority and biblionumber....
410 my $sth = $dbh->prepare("SELECT reserves.priority as priority,
411 reserves.biblionumber as biblionumber,
412 reserves.branchcode as branchcode,
413 reserves.timestamp as timestamp
415 WHERE reserves.biblionumber = items.biblionumber
416 AND items.itemnumber = ?
417 AND reserves.borrowernumber = ?
418 AND reserves.cancellationdate is NULL
419 AND (reserves.found <> 'F' or reserves.found is NULL)");
420 $sth->execute($item,$borr);
421 my $data = $sth->fetchrow_hashref;
423 my $biblio = $data->{'biblionumber'};
424 my $timestamp = $data->{'timestamp'};
425 # update reserves record....
426 $sth = $dbh->prepare("UPDATE reserves SET priority = 0, found = 'W', itemnumber = ?
427 WHERE borrowernumber = ?
430 $sth->execute($item,$borr,$biblio,$timestamp);
432 # now fix up the remaining priorities....
433 fixpriority($data->{'priority'}, $biblio);
434 my $branchcode = $data->{'branchcode'};
441 my $dbh = C4::Context->dbh;
443 my $sth = $dbh->prepare("SELECT * FROM reserves
444 WHERE borrowernumber = ?
445 AND reserves.found = 'W'
446 AND cancellationdate is NULL");
447 $sth->execute($borr);
448 while (my $data=$sth->fetchrow_hashref) {
449 push(@itemswaiting,$data);
452 return (scalar(@itemswaiting),\@itemswaiting);
455 =item Findgroupreserve
457 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
459 I don't know what this does, because I don't understand how reserve
460 constraints work. I think the idea is that you reserve a particular
461 biblio, and the constraint allows you to restrict it to a given
462 biblioitem (e.g., if you want to borrow the audio book edition of "The
463 Prophet", rather than the first available publication).
465 C<&Findgroupreserve> returns a two-element array:
467 C<$count> is the number of elements in C<@results>.
469 C<@results> is an array of references-to-hash whose keys are mostly
470 fields from the reserves table of the Koha database, plus
475 sub Findgroupreserve {
476 my ($bibitem,$biblio)=@_;
477 my $dbh = C4::Context->dbh;
478 my $sth=$dbh->prepare("SELECT reserves.biblionumber AS biblionumber,
479 reserves.borrowernumber AS borrowernumber,
480 reserves.reservedate AS reservedate,
481 reserves.branchcode AS branchcode,
482 reserves.cancellationdate AS cancellationdate,
483 reserves.found AS found,
484 reserves.reservenotes AS reservenotes,
485 reserves.priority AS priority,
486 reserves.timestamp AS timestamp,
487 reserveconstraints.biblioitemnumber AS biblioitemnumber,
488 reserves.itemnumber AS itemnumber
489 FROM reserves LEFT JOIN reserveconstraints
490 ON reserves.biblionumber = reserveconstraints.biblionumber
491 WHERE reserves.biblionumber = ?
492 AND ( ( reserveconstraints.biblioitemnumber = ?
493 AND reserves.borrowernumber = reserveconstraints.borrowernumber
494 AND reserves.reservedate =reserveconstraints.reservedate )
495 OR reserves.constrainttype='a' )
496 AND reserves.cancellationdate is NULL
497 AND (reserves.found <> 'F' or reserves.found is NULL)");
498 $sth->execute($biblio, $bibitem);
500 while (my $data=$sth->fetchrow_hashref){
501 push(@results,$data);
504 return(scalar(@results),@results);
507 # FIXME - A somewhat different version of this function appears in
508 # C4::Reserves. Pick one and stick with it.
512 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
513 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
514 my $dbh = C4::Context->dbh;
515 my $const = lc substr($constraint,0,1);
516 my @datearr = localtime(time);
517 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
519 # updates take place here
522 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
523 my $usth = $dbh->prepare("insert into accountlines
524 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
526 (?,?,now(),?,?,'Res',?)");
527 $usth->execute($borrnum,$nextacctno,$fee,'Reserve Charge - $title',$fee);
531 my $sth = $dbh->prepare("insert into reserves
532 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
533 values (?,?,?,?,?,?,?)");
534 $sth->execute($borrnum,$biblionumber,$resdate,$branch,$const,$priority,$notes);
537 if (($const eq "o") || ($const eq "e")) {
538 my $numitems = @$bibitems;
540 while ($i < $numitems) {
541 my $biblioitem = @$bibitems[$i];
542 my $sth = $dbh->prepare("insert into
544 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
546 $sth->execute($borrnum,$biblionumber,$resdate,$biblioitem);
555 # FIXME - A functionally identical version of this function appears in
556 # C4::Reserves. Pick one and stick with it.
557 # XXX - Internal use only
558 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
560 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
562 my $dbh = C4::Context->dbh;
563 my $const = lc substr($constraint,0,1);
564 my $sth = $dbh->prepare("SELECT * FROM borrowers,categories
565 WHERE (borrowernumber = ?)
566 AND (borrowers.categorycode = categories.categorycode)");
567 $sth->execute($borrnum);
568 my $data = $sth->fetchrow_hashref;
570 my $fee = $data->{'reservefee'};
571 my $cntitems = @->$bibitems;
573 # check for items on issue
574 # first find biblioitem records
576 my $sth1 = $dbh->prepare("SELECT * FROM biblio,biblioitems
577 WHERE (biblio.biblionumber = ?)
578 AND (biblio.biblionumber = biblioitems.biblionumber)");
579 $sth1->execute($biblionumber);
580 while (my $data1=$sth1->fetchrow_hashref) {
582 push @biblioitems,$data1;
586 while ($x < $cntitems) {
587 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
594 push @biblioitems,$data1;
598 push @biblioitems,$data1;
604 my $cntitemsfound = @biblioitems;
608 while ($x < $cntitemsfound) {
609 my $bitdata = $biblioitems[$x];
610 my $sth2 = $dbh->prepare("SELECT * FROM items
611 WHERE biblioitemnumber = ?");
612 $sth2->execute($bitdata->{'biblioitemnumber'});
613 while (my $itdata=$sth2->fetchrow_hashref) {
614 my $sth3 = $dbh->prepare("SELECT * FROM issues
616 AND returndate IS NULL");
617 $sth3->execute($itdata->{'itemnumber'});
618 if (my $isdata=$sth3->fetchrow_hashref) {
625 if ($allissued == 0) {
626 my $rsth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
627 $rsth->execute($biblionumber);
628 if (my $rdata = $rsth->fetchrow_hashref) {
640 my ($env,$bornumber,$dbh)=@_;
642 my $sth = $dbh->prepare("select * from accountlines
643 where (borrowernumber = ?)
644 order by accountno desc");
645 $sth->execute($bornumber);
646 if (my $accdata=$sth->fetchrow_hashref){
647 $nextaccntno = $accdata->{'accountno'} + 1;
650 return($nextaccntno);
655 #subroutine to update a reserve
656 my ($rank,$biblio,$borrower,$del,$branch)=@_;
657 my $dbh = C4::Context->dbh;
659 my $sth = $dbh->prepare("Update reserves set priority=?,branchcode=? where
660 biblionumber=? and borrowernumber=?");
661 $sth->execute($rank,$branch,$biblio,$borrower);
664 my $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
666 $sth->execute($biblio,$borrower);
667 my $data=$sth->fetchrow_hashref;
669 $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
670 priority > ? and cancellationdate is NULL
671 order by priority") || die $dbh->errstr;
672 $sth->execute($biblio,$data->{'priority'}) || die $sth->errstr;
673 while (my $data=$sth->fetchrow_hashref){
674 $data->{'priority'}--;
675 my $sth3=$dbh->prepare("Update reserves set priority=?
676 where biblionumber=? and borrowernumber=?");
677 $sth3->execute($data->{'priority'},$data->{'biblionumber'},$data->{'borrowernumber'}) || die $sth3->errstr;
681 $sth=$dbh->prepare("update reserves set cancellationdate=now() where biblionumber=?
682 and borrowernumber=?");
683 $sth->execute($biblio,$borrower);
690 #subroutine to update a reserve
691 my ($rank,$biblio,$borrower,$branch)=@_;
692 return if $rank eq "W";
693 return if $rank eq "n";
694 my $dbh = C4::Context->dbh;
695 if ($rank eq "del") {
696 my $sth=$dbh->prepare("UPDATE reserves SET cancellationdate=now()
697 WHERE biblionumber = ?
698 AND borrowernumber = ?
699 AND cancellationdate is NULL
700 AND (found <> 'F' or found is NULL)");
701 $sth->execute($biblio, $borrower);
704 my $sth=$dbh->prepare("UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
705 WHERE biblionumber = ?
706 AND borrowernumber = ?
707 AND cancellationdate is NULL
708 AND (found <> 'F' or found is NULL)");
709 $sth->execute($rank, $branch, $biblio, $borrower);
715 sub getreservetitle {
716 my ($biblio,$bor,$date,$timestamp)=@_;
717 my $dbh = C4::Context->dbh;
718 my $sth=$dbh->prepare("Select * from reserveconstraints,biblioitems where
719 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
720 and reserveconstraints.biblionumber=? and reserveconstraints.borrowernumber
721 = ? and reserveconstraints.reservedate=? and
722 reserveconstraints.timestamp=?");
723 $sth->execute($biblio,$bor,$date,$timestamp);
724 my $data=$sth->fetchrow_hashref;