2 # NOTE: This file uses standard 8-character tabs
8 # Copyright 2000-2002 Katipo Communications
10 # This file is hard coded with koha-reserves table to be used only by the OPAC -TG.
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
31 # FIXME - C4::Reserves2 uses C4::Search, which uses C4::Reserves2.
32 # So Perl complains that all of the functions here get redefined.
35 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
37 # set the version for version checking
59 # FIXME Take out CalcReserveFee after it can be removed from opac-reserves.pl
60 @EXPORT = qw(&FindReserves
77 # make all your functions, whether exported or not;
81 ($count, $results) = &FindReserves($biblionumber, $borrowernumber);
83 Looks books up in the reserves. C<$biblionumber> is the biblionumber
84 of the book to look up. C<$borrowernumber> is the borrower number of a
85 patron whose books to look up.
87 Either C<$biblionumber> or C<$borrowernumber> may be the empty string,
88 but not both. If both are specified, C<&FindReserves> looks up the
89 given book for the given patron. If only C<$biblionumber> is
90 specified, C<&FindReserves> looks up that book for all patrons. If
91 only C<$borrowernumber> is specified, C<&FindReserves> looks up all of
92 that patron's reserves. If neither is specified, C<&FindReserves>
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.
106 my ($bib, $bor) = @_;
109 my $dbh = C4::Context->dbh;
110 # Find the desired items in the reserves
111 my $query="SELECT *, reserves.branchcode, reserves.timestamp as rtimestamp, DATE_FORMAT(reserves.timestamp, '%T') AS time
112 FROM reserves,borrowers,items ";
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 = ?) and
120 (borrowers.borrowernumber = ?) and
121 (reserves.borrowernumber = borrowers.borrowernumber) and
122 (reserves.itemnumber=items.itemnumber) and
123 (cancellationdate IS NULL) and
126 push @params, $bib, $bor;
128 # $bib specified, but not $bor
129 # Find a particular book for all patrons
130 $query .= "WHERE (reserves.borrowernumber = borrowers.borrowernumber) and
131 (reserves.biblionumber = ?) and
132 (reserves.itemnumber=items.itemnumber) and
133 (cancellationdate IS NULL) and
139 $query .= "WHERE (reserves.biblionumber = items.biblionumber) and
140 (borrowers.borrowernumber = ?) and
141 (reserves.borrowernumber = borrowers.borrowernumber) and
142 (reserves.itemnumber=items.itemnumber) and
143 (cancellationdate IS NULL) and
148 $query.=" order by reserves.timestamp";
149 my $sth = $dbh->prepare($query);
150 $sth->execute(@params);
154 while (my $data = $sth->fetchrow_hashref){
155 my ($bibdatarecord) =XMLgetbiblio($dbh,$data->{'biblionumber'});
157 my $bibdata=XML_xml2hash_onerecord($bibdatarecord);
158 $data->{'author'} =XML_readline_onerecord($bibdata,"author","biblios");
159 $data->{'publishercode'} = XML_readline_onerecord($bibdata,"publishercode","biblios");
160 $data->{'publicationyear'} = XML_readline_onerecord($bibdata,"publicationyear","biblios");
161 $data->{'title'} = XML_readline_onerecord($bibdata,"title","biblios");
162 push @results, $data;
167 return($i,\@results);
170 =item FindAllReserves
172 ($count, $results) = &FindAllReserves($biblionumber, $borrowernumber);
174 Looks books up in the reserves. C<$biblionumber> is the biblionumber
175 of the book to look up. C<$borrowernumber> is the borrower number of a
176 patron whose books to look up.
178 Either C<$biblionumber> or C<$borrowernumber> may be the empty string,
179 but not both. If both are specified, C<&FindReserves> looks up the
180 given book for the given patron. If only C<$biblionumber> is
181 specified, C<&FindReserves> looks up that book for all patrons. If
182 only C<$borrowernumber> is specified, C<&FindReserves> looks up all of
183 that patron's reserves. If neither is specified, C<&FindReserves>
186 C<&FindAllReserves> returns a two-element array:
188 C<$count> is the number of elements in C<$results>.
190 C<$results> is a reference-to-array; each element is a
191 reference-to-hash, whose keys are (I think) all of the fields of the
192 reserves, borrowers, and biblio tables of the Koha database.
196 sub FindAllReserves {
197 my ($bib, $bor) = @_;
202 $dbh = C4::Context->dbh;
204 # Find the desired items in the reserves
207 biblio.title AS btitle,
208 reserves.timestamp as rtimestamp,
209 DATE_FORMAT(reserves.timestamp, '%T') AS time
214 #$bib = $dbh->quote($bib);
216 # Both $bib and $bor specified
217 # Find a particular book for a particular patron
218 #$bor = $dbh->quote($bor);
219 $query .= "WHERE (reserves.biblionumber = ?) and
220 (borrowers.borrowernumber = ?) and
221 (reserves.borrowernumber = borrowers.borrowernumber) and
222 (biblio.biblionumber = ?) and
223 (cancellationdate IS NULL) and
225 (reservefrom > NOW())";
226 push @params, $bib, $bor, $bib;
228 # $bib specified, but not $bor
229 # Find a particular book for all patrons
230 $query .= "WHERE (reserves.borrowernumber = borrowers.borrowernumber) and
231 (biblio.biblionumber = ?) and
232 (reserves.biblionumber = ?) and
233 (cancellationdate IS NULL) and
235 (reservefrom > NOW())";
236 push @params, $bib, $bib;
239 $query .= "WHERE (reserves.biblionumber = biblio.biblionumber) and
240 (borrowers.borrowernumber = ?) and
241 (reserves.borrowernumber = borrowers.borrowernumber) and
242 (reserves.biblionumber = biblio.biblionumber) and
243 (cancellationdate IS NULL) and
245 (reservefrom > NOW())";
248 $query.=" order by reserves.timestamp";
249 my $sth = $dbh->prepare($query);
250 $sth->execute(@params);
254 while (my $data = $sth->fetchrow_hashref){
255 my $bibdata = C4::Search::bibdata($data->{'biblionumber'});
256 $data->{'author'} = $bibdata->{'author'};
257 $data->{'publishercode'} = $bibdata->{'publishercode'};
258 $data->{'publicationyear'} = $bibdata->{'publicationyear'};
259 $data->{'title'} = $bibdata->{'title'};
260 push @results, $data;
265 return($i,\@results);
270 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
272 Find a book in the reserves.
274 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
275 Either one, but not both, may be false. If both are specified,
276 C<&CheckReserves> uses C<$itemnumber>.
278 $itemnubmer can be false, in which case uses the barcode. (Never uses
279 both. $itemnumber gets priority).
281 As I understand it, C<&CheckReserves> looks for the given item in the
282 reserves. If it is found, that's a match, and C<$status> is set to
285 Otherwise, it finds the most important item in the reserves with the
286 same biblio number as this book (I'm not clear on this) and returns it
287 with C<$status> set to C<Reserved>.
289 C<&CheckReserves> returns a two-element list:
291 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
293 C<$reserve> is the reserve item that matched. It is a
294 reference-to-hash whose keys are mostly the fields of the reserves
295 table in the Koha database.
300 my ($item, $barcode) = @_;
301 # warn "In CheckReserves: itemnumber = $item";
302 my $dbh = C4::Context->dbh;
307 my $qbc=$dbh->quote($barcode);
308 # Look up the item by barcode
309 $sth=$dbh->prepare("SELECT items.itemnumber
311 WHERE barcode=$qbc");
313 ($item) = $sth->fetchrow;
318 # if item is not for loan it cannot be reserved either.....
319 # return (0, 0) if ($notforloan);
320 # get the reserves...
321 # Find this item in the reserves
322 my ($count, @reserves) = Findgroupreserve($item);
323 # $priority and $highest are used to find the most important item
324 # in the list returned by &Findgroupreserve. (The lower $priority,
325 # the more important the item.)
326 # $highest is the most important item we've seen so far.
327 my $priority = 10000000;
330 foreach my $res (@reserves) {
331 if ($res->{found} eq "W"){
332 return ("Waiting", $res);
334 # See if this item is more important than what we've got
336 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
337 $priority = $res->{'priority'};
344 # If we get this far, then no exact match was found. Print the
345 # most important item on the list. I think this tells us who's
346 # next in line to get this book.
347 if ($highest) { # FIXME - $highest might be undefined
348 $highest->{'itemnumber'} = $item;
349 return ("Reserved", $highest);
357 &CancelReserve($reserveid);
361 Use reserveid to cancel the reservation.
363 C<$reserveid> is the reserve ID to cancel.
368 my ($biblio, $item, $borr) = @_;
372 $dbh = C4::Context->dbh;
374 #warn "In CancelReserve";
375 if (($item and $borr) and (not $biblio)) {
376 # removing a waiting reserve record....
377 # update the database...
378 my $sth = $dbh->prepare("update reserves set cancellationdate = now(),
382 and borrowernumber = ?");
383 $sth->execute($item,$borr);
386 if (($biblio and $borr) and (not $item)) {
387 # removing a reserve record....
388 # get the prioritiy on this record....
390 my $sth=$dbh->prepare("SELECT priority FROM reserves
391 WHERE biblionumber = ?
392 AND borrowernumber = ?
393 AND cancellationdate is NULL
395 $sth->execute($biblio,$borr);
396 ($priority) = $sth->fetchrow_array;
399 # update the database, removing the record...
400 $sth = $dbh->prepare("update reserves set cancellationdate = now(),
403 where biblionumber = ?
404 and borrowernumber = ?
405 and cancellationdate is NULL
407 $sth->execute($biblio,$borr);
409 # now fix the priority on the others....
410 fixpriority($priority, $biblio);
415 &FillReserve($reserveid, $itemnumber);
417 Fill a reserve. If I understand this correctly, this means that the
418 reserved book has been found and given to the patron who reserved it.
420 C<$reserve> specifies the reserve id to fill.
422 C<$itemnumber> specifies the borrowed itemnumber for the reserve.
429 $dbh = C4::Context->dbh;
430 # fill in a reserve record....
431 # FIXME - Remove some of the redundancy here
432 my $biblio = $res->{'biblionumber'}; my $qbiblio =$biblio;
433 my $borr = $res->{'borrowernumber'};
434 my $resdate = $res->{'reservedate'};
436 # get the priority on this record....
439 my $query = "SELECT priority FROM reserves
440 WHERE biblionumber = ?
441 AND borrowernumber = ?
442 AND reservedate = ?";
443 my $sth=$dbh->prepare($query);
444 $sth->execute($qbiblio,$borr,$resdate);
445 ($priority) = $sth->fetchrow_array;
449 # update the database...
451 my $query = "UPDATE reserves SET found = 1,
453 WHERE biblionumber = ?
455 AND borrowernumber = ?";
456 my $sth = $dbh->prepare($query);
457 $sth->execute($qbiblio,$resdate,$borr);
461 # now fix the priority on the others (if the priority wasn't
462 # already sorted!)....
463 unless ($priority == 0) {
464 fixpriority($priority, $biblio);
468 # Only used internally
469 # Decrements (makes more important) the reserves for all of the
470 # entries waiting on the given book, if their priority is > $priority.
472 my ($priority, $biblio) = @_;
474 $dbh = C4::Context->dbh;
476 my ($count, $reserves) = FindReserves($biblio);
477 foreach my $rec (@$reserves) {
478 if ($rec->{'priority'} > $priority) {
479 my $sth = $dbh->prepare("UPDATE reserves SET priority = ?
480 WHERE biblionumber = ?
481 AND borrowernumber = ?
482 AND reservedate = ?");
483 $sth->execute($rec->{'priority'},$rec->{'biblionumber'},$rec->{'borrowernumber'},$rec->{'reservedate'});
491 my ($item, $borr) = @_;
495 $dbh = C4::Context->dbh;
497 # get priority and biblionumber....
498 my $sth = $dbh->prepare("SELECT reserves.priority as priority,
499 reserves.biblionumber as biblionumber,
500 reserves.branchcode as branchcode,
501 reserves.timestamp as timestamp
503 WHERE reserves.itemnumber = ?
504 AND reserves.borrowernumber = ?
505 AND reserves.cancellationdate is NULL
506 AND (reserves.found <> '1' or reserves.found is NULL)");
507 $sth->execute($item,$borr);
508 my $data = $sth->fetchrow_hashref;
510 my $biblio = $data->{'biblionumber'};
511 my $timestamp = $data->{'timestamp'};
512 # update reserves record....
513 $sth = $dbh->prepare("UPDATE reserves SET priority = 0, found = 'W'
514 WHERE borrowernumber = ?
517 $sth->execute($borr,$item,$timestamp);
519 # now fix up the remaining priorities....
520 fixpriority($data->{'priority'}, $biblio);
521 my $branchcode = $data->{'branchcode'};
530 $dbh = C4::Context->dbh;
532 my $sth = $dbh->prepare("SELECT * FROM reserves
533 WHERE borrowernumber = ?
534 AND reserves.found = 'W'
535 AND cancellationdate is NULL");
536 $sth->execute($borr);
537 while (my $data=$sth->fetchrow_hashref) {
538 push(@itemswaiting,$data);
541 return (scalar(@itemswaiting),\@itemswaiting);
544 =item Findgroupreserve
546 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
548 I don't know what this does, because I don't understand how reserve
549 constraints work. I think the idea is that you reserve a particular
550 biblio, and the constraint allows you to restrict it to a given
551 biblioitem (e.g., if you want to borrow the audio book edition of "The
552 Prophet", rather than the first available publication).
554 C<&Findgroupreserve> returns a two-element array:
556 C<$count> is the number of elements in C<@results>.
558 C<@results> is an array of references-to-hash whose keys are mostly
559 fields from the reserves table of the Koha database, plus
564 sub Findgroupreserve {
567 my $dbh = C4::Context->dbh;
569 my $sth = $dbh->prepare("SELECT *
571 WHERE (itemnumber = ?) AND
572 (cancellationdate IS NULL) AND
574 ORDER BY timestamp");
575 $sth->execute($itemnumber);
577 while (my $data = $sth->fetchrow_hashref) {
578 push(@results,$data);
581 return(scalar(@results),@results);
584 # FIXME - A somewhat different version of this function appears in
585 # C4::Reserves. Pick one and stick with it.
588 my ($env, $borrnum,$registeredby ,$biblionumber,$reservefrom, $reserveto, $branch,
589 $constraint, $priority, $notes, $title,$bibitems,$itemnumber) = @_;
591 my $dbh = C4::Context->dbh;
592 my $sth = $dbh->prepare("INSERT INTO reserves
593 (borrowernumber, registeredby, reservedate, biblionumber, reservefrom,
594 reserveto, branchcode, constrainttype, priority, found, reservenotes,itemnumber)
595 VALUES (?, ?, NOW(),?,?,?,?,?,?,0,?,?)");
596 $sth->execute($borrnum, $registeredby, $biblionumber, $reservefrom, $reserveto, $branch, $constraint, $priority, $notes,$itemnumber);
597 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
600 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
601 my $usth = $dbh->prepare("insert into accountlines
602 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
604 (?,?,now(),?,?,'Res',?)");
605 $usth->execute($borrnum,$nextacctno,$fee,'Reserve Charge -'. $title,$fee);
611 # FIXME - A functionally identical version of this function appears in
612 # C4::Reserves. Pick one and stick with it.
613 # XXX - Internal use only
614 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
617 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
619 my $dbh = C4::Context->dbh;
622 my $const = lc substr($constraint,0,1);
623 my $sth = $dbh->prepare("SELECT * FROM borrowers,categories
624 WHERE (borrowernumber = ?)
625 AND (borrowers.categorycode = categories.categorycode)");
626 $sth->execute($borrnum);
627 my $data = $sth->fetchrow_hashref;
629 my $fee = $data->{'reservefee'};
632 # check for items on issue
639 my $sth2 = $dbh->prepare("SELECT * FROM items
640 WHERE biblionumber = ?");
641 $sth2->execute($biblionumber);
642 while (my $itdata=$sth2->fetchrow_hashref) {
643 my $sth3 = $dbh->prepare("SELECT * FROM issues
645 AND returndate IS NULL");
646 $sth3->execute($itdata->{'itemnumber'});
647 if (my $isdata=$sth3->fetchrow_hashref) {
654 if ($allissued == 0) {
655 my $rsth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
656 $rsth->execute($biblionumber);
657 if (my $rdata = $rsth->fetchrow_hashref) {
670 my ($env,$bornumber,$dbh)=@_;
672 my $sth = $dbh->prepare("select * from accountlines
673 where (borrowernumber = ?)
674 order by accountno desc");
675 $sth->execute($bornumber);
676 if (my $accdata=$sth->fetchrow_hashref){
677 $nextaccntno = $accdata->{'accountno'} + 1;
680 return($nextaccntno);
685 #subroutine to update a reserve
686 my ($rank,$biblio,$borrower,$branch,$cataloger)=@_;
687 return if $rank eq "W";
688 return if $rank eq "n";
690 $dbh = C4::Context->dbh;
692 if ($rank eq "del") {
693 my $sth=$dbh->prepare("UPDATE reserves SET cancellationdate=now(),registeredby=?
694 WHERE biblionumber = ?
695 AND borrowernumber = ?
696 AND cancellationdate is NULL
698 $sth->execute($cataloger,$biblio, $borrower);
701 my $sth=$dbh->prepare("UPDATE reserves SET priority = ? ,branchcode = ?, found = 0
702 WHERE biblionumber = ?
703 AND borrowernumber = ?
704 AND cancellationdate is NULL
706 $sth->execute($rank, $branch, $biblio, $borrower);
713 #subroutine to update a reserve
714 my ($reserveid, $timestamp) = @_;
717 $dbh = C4::Context->dbh;
720 my $sth=$dbh->prepare("UPDATE reserves
721 SET timestamp = $timestamp,
722 reservedate = DATE_FORMAT($timestamp, '%Y-%m-%d')
723 WHERE (reserveid = $reserveid)");
729 sub getreservetitle {
730 my ($biblio,$bor,$date,$timestamp)=@_;
731 my $dbh = C4::Context->dbh;
734 my $sth=$dbh->prepare("Select * from reserveconstraints where
735 reserveconstraints.biblionumber=? and reserveconstraints.borrowernumber
736 = ? and reserveconstraints.reservedate=? and
737 reserveconstraints.timestamp=?");
738 $sth->execute($biblio,$bor,$date,$timestamp);
739 my $data=$sth->fetchrow_hashref;
744 sub findActiveReserve {
745 my ($borrowernumber, $biblionumber, $from, $days) = @_;
746 my $dbh = C4::Context->dbh;
748 my $sth = $dbh->prepare("SELECT *
753 AND (cancellationdate IS NULL)
755 AND ((? BETWEEN reservefrom AND reserveto)
756 OR (ADDDATE(?, INTERVAL ? DAY) BETWEEN reservefrom AND reserveto))
758 $sth->execute($borrowernumber, $biblionumber, $from, $from, $days);