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
31 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
33 # set the version for version checking
55 # FIXME Take out CalcReserveFee after it can be removed from opac-reserves.pl
71 # make all your functions, whether exported or not;
75 ($count, $results) = &FindReserves($biblionumber, $borrowernumber);
77 Looks books up in the reserves. C<$biblionumber> is the biblionumber
78 of the book to look up. C<$borrowernumber> is the borrower number of a
79 patron whose books to look up.
81 Either C<$biblionumber> or C<$borrowernumber> may be the empty string,
82 but not both. If both are specified, C<&FindReserves> looks up the
83 given book for the given patron. If only C<$biblionumber> is
84 specified, C<&FindReserves> looks up that book for all patrons. If
85 only C<$borrowernumber> is specified, C<&FindReserves> looks up all of
86 that patron's reserves. If neither is specified, C<&FindReserves>
89 For each book thus found, C<&FindReserves> checks the reserve
90 constraints and does something I don't understand.
92 C<&FindReserves> returns a two-element array:
94 C<$count> is the number of elements in C<$results>.
96 C<$results> is a reference-to-array; each element is a
97 reference-to-hash, whose keys are (I think) all of the fields of the
98 reserves, borrowers, and biblio tables of the Koha database.
104 my $dbh = C4::Context->dbh;
105 # Find the desired items in the reserves
106 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle, reserves.timestamp as rtimestamp FROM reserves,borrowers,biblio ";
107 # FIXME - These three bits of SQL seem to contain a fair amount of
108 # redundancy. Wouldn't it be better to have a @clauses array, add
109 # one or two clauses as necessary, then join(" AND ", @clauses) ?
110 # FIXME: not keen on quote() and interpolation either, but it looks safe
112 $bib = $dbh->quote($bib);
114 # Both $bib and $bor specified
115 # Find a particular book for a particular patron
116 $bor = $dbh->quote($bor);
117 $query .= " where reserves.biblionumber = $bib
118 and borrowers.borrowernumber = $bor
119 and reserves.borrowernumber = borrowers.borrowernumber
120 and biblio.biblionumber = $bib
121 and cancellationdate is NULL
122 and (found <> 'F' or found is NULL)";
124 # $bib specified, but not $bor
125 # Find a particular book for all patrons
126 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
127 and biblio.biblionumber = $bib
128 and reserves.biblionumber = $bib
129 and cancellationdate is NULL
130 and (found <> 'F' or found is NULL)";
133 # FIXME - Check that $bor was given
135 # Find all books for the given patron.
136 $query .= " where borrowers.borrowernumber = $bor
137 and reserves.borrowernumber = borrowers.borrowernumber
138 and reserves.biblionumber = biblio.biblionumber
139 and cancellationdate is NULL and
140 (found <> 'F' or found is NULL)";
142 $query.=" order by priority";
143 my $sth=$dbh->prepare($query);
146 while (my $data=$sth->fetchrow_hashref){
147 # FIXME - What is this if-statement doing? How do constraints work?
148 if ($data->{'constrainttype'} eq 'o') {
149 my $csth=$dbh->prepare("SELECT biblioitemnumber FROM reserveconstraints
150 WHERE biblionumber = ?
151 AND borrowernumber = ?
152 AND reservedate = ?");
153 $csth->execute($data->{'biblionumber'}, $data->{'borrowernumber'}, $data->{'reservedate'});
154 my ($bibitemno) = $csth->fetchrow_array;
156 # Look up the book we just found.
157 my $bdata = bibitemdata($bibitemno);
158 # Add the results of this latest search to the current
160 # FIXME - An 'each' would probably be more efficient.
161 foreach my $key (keys %$bdata) {
162 $data->{$key} = $bdata->{$key};
165 push @results, $data;
168 return($#results+1,\@results);
173 ($status, $reserve) = &CheckReserves($itemnumber, $barcode);
175 Find a book in the reserves.
177 C<$itemnumber> is the book's item number. C<$barcode> is its barcode.
178 Either one, but not both, may be false. If both are specified,
179 C<&CheckReserves> uses C<$itemnumber>.
181 $itemnubmer can be false, in which case uses the barcode. (Never uses
182 both. $itemnumber gets priority).
184 As I understand it, C<&CheckReserves> looks for the given item in the
185 reserves. If it is found, that's a match, and C<$status> is set to
188 Otherwise, it finds the most important item in the reserves with the
189 same biblio number as this book (I'm not clear on this) and returns it
190 with C<$status> set to C<Reserved>.
192 C<&CheckReserves> returns a two-element list:
194 C<$status> is either C<Waiting>, C<Reserved> (see above), or 0.
196 C<$reserve> is the reserve item that matched. It is a
197 reference-to-hash whose keys are mostly the fields of the reserves
198 table in the Koha database.
203 my ($item, $barcode) = @_;
204 # warn "In CheckReserves: itemnumber = $item";
205 my $dbh = C4::Context->dbh;
208 my $qitem=$dbh->quote($item);
209 # Look up the item by itemnumber
210 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
211 FROM items, biblioitems, itemtypes
212 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
213 AND biblioitems.itemtype = itemtypes.itemtype
214 AND itemnumber=$qitem");
216 my $qbc=$dbh->quote($barcode);
217 # Look up the item by barcode
218 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
219 FROM items, biblioitems, itemtypes
220 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
221 AND biblioitems.itemtype = itemtypes.itemtype
223 # FIXME - This function uses $item later on. Ought to set it here.
226 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
228 # if item is not for loan it cannot be reserved either.....
229 return (0, 0) if ($notforloan);
230 # get the reserves...
231 # Find this item in the reserves
232 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
233 # $priority and $highest are used to find the most important item
234 # in the list returned by &Findgroupreserve. (The lower $priority,
235 # the more important the item.)
236 # $highest is the most important item we've seen so far.
237 my $priority = 10000000;
240 foreach my $res (@reserves) {
241 # FIXME - $item might be undefined or empty: the caller
242 # might be searching by barcode.
243 if ($res->{'itemnumber'} == $item) {
245 return ("Waiting", $res);
247 # See if this item is more important than what we've got
249 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
250 $priority = $res->{'priority'};
257 # If we get this far, then no exact match was found. Print the
258 # most important item on the list. I think this tells us who's
259 # next in line to get this book.
260 if ($highest) { # FIXME - $highest might be undefined
261 $highest->{'itemnumber'} = $item;
262 return ("Reserved", $highest);
270 &CancelReserve($biblionumber, $itemnumber, $borrowernumber);
274 Use either C<$biblionumber> or C<$itemnumber> to specify the item to
275 cancel, but not both: if both are given, C<&CancelReserve> does
278 C<$borrowernumber> is the borrower number of the patron on whose
279 behalf the book was reserved.
281 If C<$biblionumber> was given, C<&CancelReserve> also adjusts the
282 priorities of the other people who are waiting on the book.
287 my ($biblio, $item, $borr) = @_;
288 my $dbh = C4::Context->dbh;
289 #warn "In CancelReserve";
290 if (($item and $borr) and (not $biblio)) {
291 # removing a waiting reserve record....
292 # update the database...
293 my $sth = $dbh->prepare("update reserves set cancellationdate = now(),
297 and borrowernumber = ?");
298 $sth->execute($item,$borr);
301 if (($biblio and $borr) and (not $item)) {
302 # removing a reserve record....
303 # get the prioritiy on this record....
305 my $sth=$dbh->prepare("SELECT priority FROM reserves
306 WHERE biblionumber = ?
307 AND borrowernumber = ?
308 AND cancellationdate is NULL
309 AND (found <> 'F' or found is NULL)");
310 $sth->execute($biblio,$borr);
311 ($priority) = $sth->fetchrow_array;
314 # update the database, removing the record...
315 $sth = $dbh->prepare("update reserves set cancellationdate = now(),
318 where biblionumber = ?
319 and borrowernumber = ?
320 and cancellationdate is NULL
321 and (found <> 'F' or found is NULL)");
322 $sth->execute($biblio,$borr);
324 # now fix the priority on the others....
325 fixpriority($priority, $biblio);
331 &FillReserve($reserve);
333 Fill a reserve. If I understand this correctly, this means that the
334 reserved book has been found and given to the patron who reserved it.
336 C<$reserve> specifies the reserve to fill. It is a reference-to-hash
337 whose keys are fields from the reserves table in the Koha database.
343 my $dbh = C4::Context->dbh;
345 # fill in a reserve record....
346 # FIXME - Remove some of the redundancy here
347 my $biblio = $res->{'biblionumber'}; my $qbiblio =$biblio;
348 my $borr = $res->{'borrowernumber'};
349 my $resdate = $res->{'reservedate'};
351 # get the priority on this record....
354 my $query = "SELECT priority FROM reserves
355 WHERE biblionumber = ?
356 AND borrowernumber = ?
357 AND reservedate = ?";
358 my $sth=$dbh->prepare($query);
359 $sth->execute($qbiblio,$borr,$resdate);
360 ($priority) = $sth->fetchrow_array;
364 # update the database...
366 my $query = "UPDATE reserves SET found = 'F',
368 WHERE biblionumber = ?
370 AND borrowernumber = ?";
371 my $sth = $dbh->prepare($query);
372 $sth->execute($qbiblio,$resdate,$borr);
376 # now fix the priority on the others (if the priority wasn't
377 # already sorted!)....
378 unless ($priority == 0) {
379 fixpriority($priority, $biblio);
383 # Only used internally
384 # Decrements (makes more important) the reserves for all of the
385 # entries waiting on the given book, if their priority is > $priority.
387 my ($priority, $biblio) = @_;
388 my $dbh = C4::Context->dbh;
389 my ($count, $reserves) = FindReserves($biblio);
390 foreach my $rec (@$reserves) {
391 if ($rec->{'priority'} > $priority) {
392 my $sth = $dbh->prepare("UPDATE reserves SET priority = ?
393 WHERE biblionumber = ?
394 AND borrowernumber = ?
395 AND reservedate = ?");
396 $sth->execute($rec->{'priority'},$rec->{'biblionumber'},$rec->{'borrowernumber'},$rec->{'reservedate'});
404 my ($item, $borr) = @_;
405 my $dbh = C4::Context->dbh;
406 # get priority and biblionumber....
407 my $sth = $dbh->prepare("SELECT reserves.priority as priority,
408 reserves.biblionumber as biblionumber,
409 reserves.branchcode as branchcode,
410 reserves.timestamp as timestamp
412 WHERE reserves.biblionumber = items.biblionumber
413 AND items.itemnumber = ?
414 AND reserves.borrowernumber = ?
415 AND reserves.cancellationdate is NULL
416 AND (reserves.found <> 'F' or reserves.found is NULL)");
417 $sth->execute($item,$borr);
418 my $data = $sth->fetchrow_hashref;
420 my $biblio = $data->{'biblionumber'};
421 my $timestamp = $data->{'timestamp'};
422 # update reserves record....
423 $sth = $dbh->prepare("UPDATE reserves SET priority = 0, found = 'W', itemnumber = ?
424 WHERE borrowernumber = ?
427 $sth->execute($item,$borr,$biblio,$timestamp);
429 # now fix up the remaining priorities....
430 fixpriority($data->{'priority'}, $biblio);
431 my $branchcode = $data->{'branchcode'};
438 my $dbh = C4::Context->dbh;
440 my $sth = $dbh->prepare("SELECT * FROM reserves
441 WHERE borrowernumber = ?
442 AND reserves.found = 'W'
443 AND cancellationdate is NULL");
444 $sth->execute($borr);
445 while (my $data=$sth->fetchrow_hashref) {
446 push(@itemswaiting,$data);
449 return (scalar(@itemswaiting),\@itemswaiting);
452 =item Findgroupreserve
454 ($count, @results) = &Findgroupreserve($biblioitemnumber, $biblionumber);
456 I don't know what this does, because I don't understand how reserve
457 constraints work. I think the idea is that you reserve a particular
458 biblio, and the constraint allows you to restrict it to a given
459 biblioitem (e.g., if you want to borrow the audio book edition of "The
460 Prophet", rather than the first available publication).
462 C<&Findgroupreserve> returns a two-element array:
464 C<$count> is the number of elements in C<@results>.
466 C<@results> is an array of references-to-hash whose keys are mostly
467 fields from the reserves table of the Koha database, plus
472 sub Findgroupreserve {
473 my ($bibitem,$biblio)=@_;
474 my $dbh = C4::Context->dbh;
475 my $sth=$dbh->prepare("SELECT reserves.biblionumber AS biblionumber,
476 reserves.borrowernumber AS borrowernumber,
477 reserves.reservedate AS reservedate,
478 reserves.branchcode AS branchcode,
479 reserves.cancellationdate AS cancellationdate,
480 reserves.found AS found,
481 reserves.reservenotes AS reservenotes,
482 reserves.priority AS priority,
483 reserves.timestamp AS timestamp,
484 reserveconstraints.biblioitemnumber AS biblioitemnumber,
485 reserves.itemnumber AS itemnumber
486 FROM reserves LEFT JOIN reserveconstraints
487 ON reserves.biblionumber = reserveconstraints.biblionumber
488 WHERE reserves.biblionumber = ?
489 AND ( ( reserveconstraints.biblioitemnumber = ?
490 AND reserves.borrowernumber = reserveconstraints.borrowernumber
491 AND reserves.reservedate =reserveconstraints.reservedate )
492 OR reserves.constrainttype='a' )
493 AND reserves.cancellationdate is NULL
494 AND (reserves.found <> 'F' or reserves.found is NULL)");
495 $sth->execute($biblio, $bibitem);
497 while (my $data=$sth->fetchrow_hashref){
498 push(@results,$data);
501 return(scalar(@results),@results);
504 # FIXME - A somewhat different version of this function appears in
505 # C4::Reserves. Pick one and stick with it.
509 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
510 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
511 my $dbh = C4::Context->dbh;
512 my $const = lc substr($constraint,0,1);
513 my @datearr = localtime(time);
514 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
516 # updates take place here
519 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
520 my $usth = $dbh->prepare("insert into accountlines
521 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
523 (?,?,now(),?,?,'Res',?)");
524 $usth->execute($borrnum,$nextacctno,$fee,"Reserve Charge - $title",$fee);
528 my $sth = $dbh->prepare("insert into reserves
529 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
530 values (?,?,?,?,?,?,?)");
531 $sth->execute($borrnum,$biblionumber,$resdate,$branch,$const,$priority,$notes);
534 if (($const eq "o") || ($const eq "e")) {
535 my $numitems = @$bibitems;
537 while ($i < $numitems) {
538 my $biblioitem = @$bibitems[$i];
539 my $sth = $dbh->prepare("insert into
541 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
543 $sth->execute($borrnum,$biblionumber,$resdate,$biblioitem);
552 # FIXME - A functionally identical version of this function appears in
553 # C4::Reserves. Pick one and stick with it.
554 # XXX - Internal use only
555 # FIXME - opac-reserves.pl need to use it, temporarily put into @EXPORT
557 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
559 my $dbh = C4::Context->dbh;
560 my $const = lc substr($constraint,0,1);
561 my $sth = $dbh->prepare("SELECT * FROM borrowers,categories
562 WHERE (borrowernumber = ?)
563 AND (borrowers.categorycode = categories.categorycode)");
564 $sth->execute($borrnum);
565 my $data = $sth->fetchrow_hashref;
567 my $fee = $data->{'reservefee'};
568 my $cntitems = @->$bibitems;
570 # check for items on issue
571 # first find biblioitem records
573 my $sth1 = $dbh->prepare("SELECT * FROM biblio,biblioitems
574 WHERE (biblio.biblionumber = ?)
575 AND (biblio.biblionumber = biblioitems.biblionumber)");
576 $sth1->execute($biblionumber);
577 while (my $data1=$sth1->fetchrow_hashref) {
579 push @biblioitems,$data1;
583 while ($x < $cntitems) {
584 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
591 push @biblioitems,$data1;
595 push @biblioitems,$data1;
601 my $cntitemsfound = @biblioitems;
605 while ($x < $cntitemsfound) {
606 my $bitdata = $biblioitems[$x];
607 my $sth2 = $dbh->prepare("SELECT * FROM items
608 WHERE biblioitemnumber = ?");
609 $sth2->execute($bitdata->{'biblioitemnumber'});
610 while (my $itdata=$sth2->fetchrow_hashref) {
611 my $sth3 = $dbh->prepare("SELECT * FROM issues
613 AND returndate IS NULL");
614 $sth3->execute($itdata->{'itemnumber'});
615 if (my $isdata=$sth3->fetchrow_hashref) {
622 if ($allissued == 0) {
623 my $rsth = $dbh->prepare("SELECT * FROM reserves WHERE biblionumber = ?");
624 $rsth->execute($biblionumber);
625 if (my $rdata = $rsth->fetchrow_hashref) {
637 my ($env,$bornumber,$dbh)=@_;
639 my $sth = $dbh->prepare("select * from accountlines
640 where (borrowernumber = ?)
641 order by accountno desc");
642 $sth->execute($bornumber);
643 if (my $accdata=$sth->fetchrow_hashref){
644 $nextaccntno = $accdata->{'accountno'} + 1;
647 return($nextaccntno);
652 #subroutine to update a reserve
653 my ($rank,$biblio,$borrower,$del,$branch)=@_;
654 my $dbh = C4::Context->dbh;
656 my $sth = $dbh->prepare("Update reserves set priority=?,branchcode=? where
657 biblionumber=? and borrowernumber=?");
658 $sth->execute($rank,$branch,$biblio,$borrower);
661 my $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
663 $sth->execute($biblio,$borrower);
664 my $data=$sth->fetchrow_hashref;
666 $sth=$dbh->prepare("Select * from reserves where biblionumber=? and
667 priority > ? and cancellationdate is NULL
668 order by priority") || die $dbh->errstr;
669 $sth->execute($biblio,$data->{'priority'}) || die $sth->errstr;
670 while (my $data=$sth->fetchrow_hashref){
671 $data->{'priority'}--;
672 my $sth3=$dbh->prepare("Update reserves set priority=?
673 where biblionumber=? and borrowernumber=?");
674 $sth3->execute($data->{'priority'},$data->{'biblionumber'},$data->{'borrowernumber'}) || die $sth3->errstr;
678 $sth=$dbh->prepare("update reserves set cancellationdate=now() where biblionumber=?
679 and borrowernumber=?");
680 $sth->execute($biblio,$borrower);
687 #subroutine to update a reserve
688 my ($rank,$biblio,$borrower,$branch)=@_;
689 return if $rank eq "W";
690 return if $rank eq "n";
691 my $dbh = C4::Context->dbh;
692 if ($rank eq "del") {
693 my $sth=$dbh->prepare("UPDATE reserves SET cancellationdate=now()
694 WHERE biblionumber = ?
695 AND borrowernumber = ?
696 AND cancellationdate is NULL
697 AND (found <> 'F' or found is NULL)");
698 $sth->execute($biblio, $borrower);
701 my $sth=$dbh->prepare("UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
702 WHERE biblionumber = ?
703 AND borrowernumber = ?
704 AND cancellationdate is NULL
705 AND (found <> 'F' or found is NULL)");
706 $sth->execute($rank, $branch, $biblio, $borrower);
712 sub getreservetitle {
713 my ($biblio,$bor,$date,$timestamp)=@_;
714 my $dbh = C4::Context->dbh;
715 my $sth=$dbh->prepare("Select * from reserveconstraints,biblioitems where
716 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
717 and reserveconstraints.biblionumber=? and reserveconstraints.borrowernumber
718 = ? and reserveconstraints.reservedate=? and
719 reserveconstraints.timestamp=?");
720 $sth->execute($biblio,$bor,$date,$timestamp);
721 my $data=$sth->fetchrow_hashref;