3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
25 # FIXME - C4::Reserves2 uses C4::Search, which uses C4::Reserves2.
26 # So Perl complains that all of the functions here get redefined.
29 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
31 # set the version for version checking
35 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &UpdateReserve &getreservetitle &Findgroupreserve);
37 # make all your functions, whether exported or not;
41 my $dbh = C4::Context->dbh;
42 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
43 FROM reserves,borrowers,biblio ";
45 $bib = $dbh->quote($bib);
47 $bor = $dbh->quote($bor);
48 $query .= " where reserves.biblionumber = $bib
49 and borrowers.borrowernumber = $bor
50 and reserves.borrowernumber = borrowers.borrowernumber
51 and biblio.biblionumber = $bib
52 and cancellationdate is NULL
53 and (found <> 'F' or found is NULL)";
55 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
56 and biblio.biblionumber = $bib
57 and reserves.biblionumber = $bib
58 and cancellationdate is NULL
59 and (found <> 'F' or found is NULL)";
62 $query .= " where borrowers.borrowernumber = $bor
63 and reserves.borrowernumber = borrowers.borrowernumber
64 and reserves.biblionumber = biblio.biblionumber
65 and cancellationdate is NULL and
66 (found <> 'F' or found is NULL)";
68 $query.=" order by priority";
69 my $sth=$dbh->prepare($query);
73 while (my $data=$sth->fetchrow_hashref){
74 if ($data->{'constrainttype'} eq 'o') {
75 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
76 WHERE biblionumber = ?
77 AND borrowernumber = ?
79 my $csth=$dbh->prepare($conquery);
80 my $bibn = $data->{'biblionumber'};
81 my $born = $data->{'borrowernumber'};
82 my $resd = $data->{'reservedate'};
83 $csth->execute($bibn, $born, $resd);
84 my ($bibitemno) = $csth->fetchrow_array;
86 my $bdata = C4::Search::bibitemdata($bibitemno);
87 foreach my $key (keys %$bdata) {
88 $data->{$key} = $bdata->{$key};
100 my ($item, $barcode) = @_;
101 # warn "In CheckReserves: itemnumber = $item";
102 my $dbh = C4::Context->dbh;
105 my $qitem=$dbh->quote($item);
106 # get the biblionumber...
107 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
108 FROM items, biblioitems, itemtypes
109 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
110 AND biblioitems.itemtype = itemtypes.itemtype
111 AND itemnumber=$qitem");
113 my $qbc=$dbh->quote($barcode);
114 # get the biblionumber...
115 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
116 FROM items, biblioitems, itemtypes
117 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
118 AND biblioitems.itemtype = itemtypes.itemtype
120 # FIXME - This function uses $item later on. Ought to set it here.
123 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
125 # if item is not for loan it cannot be reserved either.....
126 return (0, 0) if ($notforloan);
127 # get the reserves...
128 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
129 my $priority = 10000000;
132 foreach my $res (@reserves) {
133 # FIXME - $item might be undefined or empty: the caller
134 # might be searching by barcode.
135 if ($res->{'itemnumber'} == $item) {
136 return ("Waiting", $res);
138 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
139 $priority = $res->{'priority'};
145 if ($highest) { # FIXME - $highest might be undefined
146 $highest->{'itemnumber'} = $item;
147 return ("Reserved", $highest);
154 my ($biblio, $item, $borr) = @_;
155 my $dbh = C4::Context->dbh;
156 #warn "In CancelReserve";
157 if (($item and $borr) and (not $biblio)) {
158 # removing a waiting reserve record....
159 $item = $dbh->quote($item);
160 $borr = $dbh->quote($borr);
161 # update the database...
162 my $query = "update reserves set cancellationdate = now(),
165 where itemnumber = $item
166 and borrowernumber = $borr";
167 my $sth = $dbh->prepare($query);
171 if (($biblio and $borr) and (not $item)) {
172 # removing a reserve record....
173 my $q_biblio = $dbh->quote($biblio);
174 $borr = $dbh->quote($borr);
175 # get the prioritiy on this record....
176 my $query = "SELECT priority FROM reserves
177 WHERE biblionumber = $q_biblio
178 AND borrowernumber = $borr
179 AND cancellationdate is NULL
180 AND (found <> 'F' or found is NULL)";
181 my $sth=$dbh->prepare($query);
183 my ($priority) = $sth->fetchrow_array;
185 # update the database, removing the record...
186 # FIXME - There's already a $query in this scope.
187 my $query = "update reserves set cancellationdate = now(),
190 where biblionumber = $q_biblio
191 and borrowernumber = $borr
192 and cancellationdate is NULL
193 and (found <> 'F' or found is NULL)";
194 # FIXME - There's already a $query in this scope.
195 # FIXME - There's already a $sth in this scope.
196 my $sth = $dbh->prepare($query);
197 # FIXME - There's already a $sth in this scope.
200 # now fix the priority on the others....
201 fixpriority($priority, $biblio);
208 my $dbh = C4::Context->dbh;
209 # fillinf a reserve record....
210 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
211 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
212 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
213 # get the prioritiy on this record....
214 my $query = "SELECT priority FROM reserves
215 WHERE biblionumber = $qbiblio
216 AND borrowernumber = $borr
217 AND reservedate = $resdate)";
218 my $sth=$dbh->prepare($query);
220 my ($priority) = $sth->fetchrow_array;
222 # update the database...
223 # FIXME - There's already a $query in this scope.
224 my $query = "UPDATE reserves SET found = 'F',
226 WHERE biblionumber = $qbiblio
227 AND reservedate = $resdate
228 AND borrowernumber = $borr";
229 # FIXME - There's already a $query in this scope.
230 # FIXME - There's already a $sth in this scope.
231 my $sth = $dbh->prepare($query);
232 # FIXME - There's already a $sth in this scope.
235 # now fix the priority on the others (if the priority wasnt already sorted!)....
236 unless ($priority == 0) {
237 fixpriority($priority, $biblio);
242 my ($priority, $biblio) = @_;
243 my $dbh = C4::Context->dbh;
244 my ($count, $reserves) = FindReserves($biblio);
245 foreach my $rec (@$reserves) {
246 if ($rec->{'priority'} > $priority) {
247 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
248 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
249 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
250 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
251 my $query = "UPDATE reserves SET priority = $newpr
252 WHERE biblionumber = $nbib
253 AND borrowernumber = $nbor
254 AND reservedate = $nresd";
256 my $sth = $dbh->prepare($query);
266 my ($item, $borr) = @_;
267 my $dbh = C4::Context->dbh;
268 $item = $dbh->quote($item);
269 $borr = $dbh->quote($borr);
270 # get priority and biblionumber....
271 my $query = "SELECT reserves.priority as priority,
272 reserves.biblionumber as biblionumber,
273 reserves.branchcode as branchcode,
274 reserves.timestamp as timestamp
276 WHERE reserves.biblionumber = items.biblionumber
277 AND items.itemnumber = $item
278 AND reserves.borrowernumber = $borr
279 AND reserves.cancellationdate is NULL
280 AND (reserves.found <> 'F' or reserves.found is NULL)";
281 my $sth = $dbh->prepare($query);
283 my $data = $sth->fetchrow_hashref;
285 my $biblio = $data->{'biblionumber'};
286 my $timestamp = $data->{'timestamp'};
287 my $q_biblio = $dbh->quote($biblio);
288 my $q_timestamp = $dbh->quote($timestamp);
289 warn "Timestamp: ".$timestamp."\n";
290 # update reserves record....
291 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
292 WHERE borrowernumber = $borr
293 AND biblionumber = $q_biblio
294 AND timestamp = $q_timestamp";
295 warn "Query: ".$query."\n";
296 $sth = $dbh->prepare($query);
299 # now fix up the remaining priorities....
300 fixpriority($data->{'priority'}, $biblio);
301 my $branchcode = $data->{'branchcode'};
307 my $dbh = C4::Context->dbh;
308 $borr = $dbh->quote($borr);
310 my $query = "SELECT * FROM reserves
311 WHERE borrowernumber = $borr
312 AND reserves.found = 'W'
313 AND cancellationdate is NULL";
314 my $sth = $dbh->prepare($query);
318 if (my $data=$sth->fetchrow_hashref) {
319 $itemswaiting[$cnt] =$data;
323 return ($cnt,\@itemswaiting);
326 sub Findgroupreserve {
327 my ($bibitem,$biblio)=@_;
328 my $dbh = C4::Context->dbh;
329 $bibitem=$dbh->quote($bibitem);
330 my $query = "SELECT reserves.biblionumber AS biblionumber,
331 reserves.borrowernumber AS borrowernumber,
332 reserves.reservedate AS reservedate,
333 reserves.branchcode AS branchcode,
334 reserves.cancellationdate AS cancellationdate,
335 reserves.found AS found,
336 reserves.reservenotes AS reservenotes,
337 reserves.priority AS priority,
338 reserves.timestamp AS timestamp,
339 reserveconstraints.biblioitemnumber AS biblioitemnumber,
340 reserves.itemnumber AS itemnumber
341 FROM reserves LEFT JOIN reserveconstraints
342 ON reserves.biblionumber = reserveconstraints.biblionumber
343 WHERE reserves.biblionumber = $biblio
344 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
345 AND reserves.borrowernumber = reserveconstraints.borrowernumber
346 AND reserves.reservedate =reserveconstraints.reservedate )
347 OR reserves.constrainttype='a' )
348 AND reserves.cancellationdate is NULL
349 AND (reserves.found <> 'F' or reserves.found is NULL)";
350 my $sth=$dbh->prepare($query);
354 while (my $data=$sth->fetchrow_hashref){
362 # FIXME - A somewhat different version of this function appears in
363 # C4::Reserves. Pick one and stick with it.
366 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
367 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
368 my $dbh = C4::Context->dbh;
369 my $const = lc substr($constraint,0,1);
370 my @datearr = localtime(time);
371 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
373 # updates take place here
376 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
377 my $updquery = "insert into accountlines
378 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
380 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
381 my $usth = $dbh->prepare($updquery);
386 my $query="insert into reserves
387 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
389 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
390 my $sth = $dbh->prepare($query);
394 if (($const eq "o") || ($const eq "e")) {
395 my $numitems = @$bibitems;
397 while ($i < $numitems) {
398 my $biblioitem = @$bibitems[$i];
399 my $query = "insert into
401 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
403 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
404 my $sth = $dbh->prepare($query);
414 # FIXME - A functionally identical version of this function appears in
415 # C4::Reserves. Pick one and stick with it.
417 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
419 my $dbh = C4::Context->dbh;
420 my $const = lc substr($constraint,0,1);
421 my $query = "SELECT * FROM borrowers,categories
422 WHERE (borrowernumber = ?)
423 AND (borrowers.categorycode = categories.categorycode)";
424 my $sth = $dbh->prepare($query);
425 $sth->execute($borrnum);
426 my $data = $sth->fetchrow_hashref;
428 my $fee = $data->{'reservefee'};
429 my $cntitems = @->$bibitems;
431 # check for items on issue
432 # first find biblioitem records
434 my $query1 = "SELECT * FROM biblio,biblioitems
435 WHERE (biblio.biblionumber = ?)
436 AND (biblio.biblionumber = biblioitems.biblionumber)";
437 my $sth1 = $dbh->prepare($query1);
438 $sth1->execute($biblionumber);
439 while (my $data1=$sth1->fetchrow_hashref) {
441 push @biblioitems,$data1;
445 while ($x < $cntitems) {
446 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
453 push @biblioitems,$data1;
457 push @biblioitems,$data1;
463 my $cntitemsfound = @biblioitems;
467 while ($x < $cntitemsfound) {
468 my $bitdata = $biblioitems[$x];
469 my $query2 = "SELECT * FROM items
470 WHERE biblioitemnumber = ?";
471 my $sth2 = $dbh->prepare($query2);
472 $sth2->execute($bitdata->{'biblioitemnumber'});
473 while (my $itdata=$sth2->fetchrow_hashref) {
474 my $query3 = "SELECT * FROM issues
476 AND returndate IS NULL";
478 my $sth3 = $dbh->prepare($query3);
479 $sth3->execute($itdata->{'itemnumber'});
480 if (my $isdata=$sth3->fetchrow_hashref) {
487 if ($allissued == 0) {
488 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
489 my $rsth = $dbh->prepare($rquery);
490 $rsth->execute($biblionumber);
491 if (my $rdata = $rsth->fetchrow_hashref) {
502 my ($env,$bornumber,$dbh)=@_;
504 my $query = "select * from accountlines
505 where (borrowernumber = '$bornumber')
506 order by accountno desc";
507 my $sth = $dbh->prepare($query);
509 if (my $accdata=$sth->fetchrow_hashref){
510 $nextaccntno = $accdata->{'accountno'} + 1;
513 return($nextaccntno);
517 #subroutine to update a reserve
518 my ($rank,$biblio,$borrower,$del,$branch)=@_;
519 my $dbh = C4::Context->dbh;
520 my $query="Update reserves ";
522 $query.="set priority='$rank',branchcode='$branch' where
523 biblionumber=$biblio and borrowernumber=$borrower";
525 $query="Select * from reserves where biblionumber=$biblio and
526 borrowernumber=$borrower";
527 my $sth=$dbh->prepare($query);
529 my $data=$sth->fetchrow_hashref;
531 $query="Select * from reserves where biblionumber=$biblio and
532 priority > '$data->{'priority'}' and cancellationdate is NULL
534 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
535 $sth2->execute || die $sth2->errstr;
536 while (my $data=$sth2->fetchrow_hashref){
537 $data->{'priority'}--;
538 $query="Update reserves set priority=$data->{'priority'} where
539 biblionumber=$data->{'biblionumber'} and
540 borrowernumber=$data->{'borrowernumber'}";
541 my $sth3=$dbh->prepare($query);
542 $sth3->execute || die $sth3->errstr;
546 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
547 and borrowernumber=$borrower";
549 my $sth=$dbh->prepare($query);
554 #subroutine to update a reserve
555 my ($rank,$biblio,$borrower,$branch)=@_;
556 return if $rank eq "W";
557 my $dbh = C4::Context->dbh;
558 if ($rank eq "del") {
559 my $query = "UPDATE reserves SET cancellationdate=now()
560 WHERE biblionumber = ?
561 AND borrowernumber = ?
562 AND cancellationdate is NULL
563 AND (found <> 'F' or found is NULL)";
564 my $sth=$dbh->prepare($query);
565 $sth->execute($biblio, $borrower);
568 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
569 WHERE biblionumber = ?
570 AND borrowernumber = ?
571 AND cancellationdate is NULL
572 AND (found <> 'F' or found is NULL)";
573 my $sth=$dbh->prepare($query);
574 $sth->execute($rank, $branch, $biblio, $borrower);
579 sub getreservetitle {
580 my ($biblio,$bor,$date,$timestamp)=@_;
581 my $dbh = C4::Context->dbh;
582 my $query="Select * from reserveconstraints,biblioitems where
583 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
584 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
585 = $bor and reserveconstraints.reservedate='$date' and
586 reserveconstraints.timestamp=$timestamp";
587 my $sth=$dbh->prepare($query);
589 my $data=$sth->fetchrow_hashref;
600 END { } # module clean-up code here (global destructor)