1 package C4::Reserves2; #assumes C4/Reserves2
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
26 # FIXME - C4::Reserves2 uses C4::Search, which uses C4::Reserves2.
27 # So Perl complains that all of the functions here get redefined.
30 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
32 # set the version for version checking
36 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &UpdateReserve &getreservetitle &Findgroupreserve);
38 # make all your functions, whether exported or not;
42 my $dbh = C4::Context->dbh;
43 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
44 FROM reserves,borrowers,biblio ";
46 $bib = $dbh->quote($bib);
48 $bor = $dbh->quote($bor);
49 $query .= " where reserves.biblionumber = $bib
50 and borrowers.borrowernumber = $bor
51 and reserves.borrowernumber = borrowers.borrowernumber
52 and biblio.biblionumber = $bib
53 and cancellationdate is NULL
54 and (found <> 'F' or found is NULL)";
56 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
57 and biblio.biblionumber = $bib
58 and reserves.biblionumber = $bib
59 and cancellationdate is NULL
60 and (found <> 'F' or found is NULL)";
63 $query .= " where borrowers.borrowernumber = $bor
64 and reserves.borrowernumber = borrowers.borrowernumber
65 and reserves.biblionumber = biblio.biblionumber
66 and cancellationdate is NULL and
67 (found <> 'F' or found is NULL)";
69 $query.=" order by priority";
70 my $sth=$dbh->prepare($query);
74 while (my $data=$sth->fetchrow_hashref){
75 if ($data->{'constrainttype'} eq 'o') {
76 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
77 WHERE biblionumber = ?
78 AND borrowernumber = ?
80 my $csth=$dbh->prepare($conquery);
81 my $bibn = $data->{'biblionumber'};
82 my $born = $data->{'borrowernumber'};
83 my $resd = $data->{'reservedate'};
84 $csth->execute($bibn, $born, $resd);
85 my ($bibitemno) = $csth->fetchrow_array;
87 my $bdata = C4::Search::bibitemdata($bibitemno);
88 foreach my $key (keys %$bdata) {
89 $data->{$key} = $bdata->{$key};
101 my ($item, $barcode) = @_;
102 # warn "In CheckReserves: itemnumber = $item";
103 my $dbh = C4::Context->dbh;
106 my $qitem=$dbh->quote($item);
107 # get the biblionumber...
108 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
109 FROM items, biblioitems, itemtypes
110 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
111 AND biblioitems.itemtype = itemtypes.itemtype
112 AND itemnumber=$qitem");
114 my $qbc=$dbh->quote($barcode);
115 # get the biblionumber...
116 $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
117 FROM items, biblioitems, itemtypes
118 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
119 AND biblioitems.itemtype = itemtypes.itemtype
121 # FIXME - This function uses $item later on. Ought to set it here.
124 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
126 # if item is not for loan it cannot be reserved either.....
127 return (0, 0) if ($notforloan);
128 # get the reserves...
129 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
130 my $priority = 10000000;
133 foreach my $res (@reserves) {
134 # FIXME - $item might be undefined or empty: the caller
135 # might be searching by barcode.
136 if ($res->{'itemnumber'} == $item) {
137 return ("Waiting", $res);
139 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
140 $priority = $res->{'priority'};
146 if ($highest) { # FIXME - $highest might be undefined
147 $highest->{'itemnumber'} = $item;
148 return ("Reserved", $highest);
155 my ($biblio, $item, $borr) = @_;
156 my $dbh = C4::Context->dbh;
157 #warn "In CancelReserve";
158 if (($item and $borr) and (not $biblio)) {
159 # removing a waiting reserve record....
160 $item = $dbh->quote($item);
161 $borr = $dbh->quote($borr);
162 # update the database...
163 my $query = "update reserves set cancellationdate = now(),
166 where itemnumber = $item
167 and borrowernumber = $borr";
168 my $sth = $dbh->prepare($query);
172 if (($biblio and $borr) and (not $item)) {
173 # removing a reserve record....
174 my $q_biblio = $dbh->quote($biblio);
175 $borr = $dbh->quote($borr);
176 # get the prioritiy on this record....
177 my $query = "SELECT priority FROM reserves
178 WHERE biblionumber = $q_biblio
179 AND borrowernumber = $borr
180 AND cancellationdate is NULL
181 AND (found <> 'F' or found is NULL)";
182 my $sth=$dbh->prepare($query);
184 my ($priority) = $sth->fetchrow_array;
186 # update the database, removing the record...
187 # FIXME - There's already a $query in this scope.
188 my $query = "update reserves set cancellationdate = now(),
191 where biblionumber = $q_biblio
192 and borrowernumber = $borr
193 and cancellationdate is NULL
194 and (found <> 'F' or found is NULL)";
195 # FIXME - There's already a $query in this scope.
196 # FIXME - There's already a $sth in this scope.
197 my $sth = $dbh->prepare($query);
198 # FIXME - There's already a $sth in this scope.
201 # now fix the priority on the others....
202 fixpriority($priority, $biblio);
209 my $dbh = C4::Context->dbh;
210 # fillinf a reserve record....
211 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
212 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
213 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
214 # get the prioritiy on this record....
215 my $query = "SELECT priority FROM reserves
216 WHERE biblionumber = $qbiblio
217 AND borrowernumber = $borr
218 AND reservedate = $resdate)";
219 my $sth=$dbh->prepare($query);
221 my ($priority) = $sth->fetchrow_array;
223 # update the database...
224 # FIXME - There's already a $query in this scope.
225 my $query = "UPDATE reserves SET found = 'F',
227 WHERE biblionumber = $qbiblio
228 AND reservedate = $resdate
229 AND borrowernumber = $borr";
230 # FIXME - There's already a $query in this scope.
231 # FIXME - There's already a $sth in this scope.
232 my $sth = $dbh->prepare($query);
233 # FIXME - There's already a $sth in this scope.
236 # now fix the priority on the others (if the priority wasnt already sorted!)....
237 unless ($priority == 0) {
238 fixpriority($priority, $biblio);
243 my ($priority, $biblio) = @_;
244 my $dbh = C4::Context->dbh;
245 my ($count, $reserves) = FindReserves($biblio);
246 foreach my $rec (@$reserves) {
247 if ($rec->{'priority'} > $priority) {
248 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
249 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
250 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
251 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
252 my $query = "UPDATE reserves SET priority = $newpr
253 WHERE biblionumber = $nbib
254 AND borrowernumber = $nbor
255 AND reservedate = $nresd";
257 my $sth = $dbh->prepare($query);
267 my ($item, $borr) = @_;
268 my $dbh = C4::Context->dbh;
269 $item = $dbh->quote($item);
270 $borr = $dbh->quote($borr);
271 # get priority and biblionumber....
272 my $query = "SELECT reserves.priority as priority,
273 reserves.biblionumber as biblionumber,
274 reserves.branchcode as branchcode,
275 reserves.timestamp as timestamp
277 WHERE reserves.biblionumber = items.biblionumber
278 AND items.itemnumber = $item
279 AND reserves.borrowernumber = $borr
280 AND reserves.cancellationdate is NULL
281 AND (reserves.found <> 'F' or reserves.found is NULL)";
282 my $sth = $dbh->prepare($query);
284 my $data = $sth->fetchrow_hashref;
286 my $biblio = $data->{'biblionumber'};
287 my $timestamp = $data->{'timestamp'};
288 my $q_biblio = $dbh->quote($biblio);
289 my $q_timestamp = $dbh->quote($timestamp);
290 warn "Timestamp: ".$timestamp."\n";
291 # update reserves record....
292 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
293 WHERE borrowernumber = $borr
294 AND biblionumber = $q_biblio
295 AND timestamp = $q_timestamp";
296 warn "Query: ".$query."\n";
297 $sth = $dbh->prepare($query);
300 # now fix up the remaining priorities....
301 fixpriority($data->{'priority'}, $biblio);
302 my $branchcode = $data->{'branchcode'};
308 my $dbh = C4::Context->dbh;
309 $borr = $dbh->quote($borr);
311 my $query = "SELECT * FROM reserves
312 WHERE borrowernumber = $borr
313 AND reserves.found = 'W'
314 AND cancellationdate is NULL";
315 my $sth = $dbh->prepare($query);
319 if (my $data=$sth->fetchrow_hashref) {
320 $itemswaiting[$cnt] =$data;
324 return ($cnt,\@itemswaiting);
327 sub Findgroupreserve {
328 my ($bibitem,$biblio)=@_;
329 my $dbh = C4::Context->dbh;
330 $bibitem=$dbh->quote($bibitem);
331 my $query = "SELECT reserves.biblionumber AS biblionumber,
332 reserves.borrowernumber AS borrowernumber,
333 reserves.reservedate AS reservedate,
334 reserves.branchcode AS branchcode,
335 reserves.cancellationdate AS cancellationdate,
336 reserves.found AS found,
337 reserves.reservenotes AS reservenotes,
338 reserves.priority AS priority,
339 reserves.timestamp AS timestamp,
340 reserveconstraints.biblioitemnumber AS biblioitemnumber,
341 reserves.itemnumber AS itemnumber
342 FROM reserves LEFT JOIN reserveconstraints
343 ON reserves.biblionumber = reserveconstraints.biblionumber
344 WHERE reserves.biblionumber = $biblio
345 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
346 AND reserves.borrowernumber = reserveconstraints.borrowernumber
347 AND reserves.reservedate =reserveconstraints.reservedate )
348 OR reserves.constrainttype='a' )
349 AND reserves.cancellationdate is NULL
350 AND (reserves.found <> 'F' or reserves.found is NULL)";
351 my $sth=$dbh->prepare($query);
355 while (my $data=$sth->fetchrow_hashref){
365 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
366 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
367 my $dbh = C4::Context->dbh;
368 my $const = lc substr($constraint,0,1);
369 my @datearr = localtime(time);
370 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
372 # updates take place here
375 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
376 my $updquery = "insert into accountlines
377 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
379 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
380 my $usth = $dbh->prepare($updquery);
385 my $query="insert into reserves
386 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
388 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
389 my $sth = $dbh->prepare($query);
393 if (($const eq "o") || ($const eq "e")) {
394 my $numitems = @$bibitems;
396 while ($i < $numitems) {
397 my $biblioitem = @$bibitems[$i];
398 my $query = "insert into
400 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
402 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
403 my $sth = $dbh->prepare($query);
414 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
416 my $dbh = C4::Context->dbh;
417 my $const = lc substr($constraint,0,1);
418 my $query = "SELECT * FROM borrowers,categories
419 WHERE (borrowernumber = ?)
420 AND (borrowers.categorycode = categories.categorycode)";
421 my $sth = $dbh->prepare($query);
422 $sth->execute($borrnum);
423 my $data = $sth->fetchrow_hashref;
425 my $fee = $data->{'reservefee'};
426 my $cntitems = @->$bibitems;
428 # check for items on issue
429 # first find biblioitem records
431 my $query1 = "SELECT * FROM biblio,biblioitems
432 WHERE (biblio.biblionumber = ?)
433 AND (biblio.biblionumber = biblioitems.biblionumber)";
434 my $sth1 = $dbh->prepare($query1);
435 $sth1->execute($biblionumber);
436 while (my $data1=$sth1->fetchrow_hashref) {
438 push @biblioitems,$data1;
442 while ($x < $cntitems) {
443 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
450 push @biblioitems,$data1;
454 push @biblioitems,$data1;
460 my $cntitemsfound = @biblioitems;
464 while ($x < $cntitemsfound) {
465 my $bitdata = $biblioitems[$x];
466 my $query2 = "SELECT * FROM items
467 WHERE biblioitemnumber = ?";
468 my $sth2 = $dbh->prepare($query2);
469 $sth2->execute($bitdata->{'biblioitemnumber'});
470 while (my $itdata=$sth2->fetchrow_hashref) {
471 my $query3 = "SELECT * FROM issues
473 AND returndate IS NULL";
475 my $sth3 = $dbh->prepare($query3);
476 $sth3->execute($itdata->{'itemnumber'});
477 if (my $isdata=$sth3->fetchrow_hashref) {
484 if ($allissued == 0) {
485 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
486 my $rsth = $dbh->prepare($rquery);
487 $rsth->execute($biblionumber);
488 if (my $rdata = $rsth->fetchrow_hashref) {
499 my ($env,$bornumber,$dbh)=@_;
501 my $query = "select * from accountlines
502 where (borrowernumber = '$bornumber')
503 order by accountno desc";
504 my $sth = $dbh->prepare($query);
506 if (my $accdata=$sth->fetchrow_hashref){
507 $nextaccntno = $accdata->{'accountno'} + 1;
510 return($nextaccntno);
514 #subroutine to update a reserve
515 my ($rank,$biblio,$borrower,$del,$branch)=@_;
516 my $dbh = C4::Context->dbh;
517 my $query="Update reserves ";
519 $query.="set priority='$rank',branchcode='$branch' where
520 biblionumber=$biblio and borrowernumber=$borrower";
522 $query="Select * from reserves where biblionumber=$biblio and
523 borrowernumber=$borrower";
524 my $sth=$dbh->prepare($query);
526 my $data=$sth->fetchrow_hashref;
528 $query="Select * from reserves where biblionumber=$biblio and
529 priority > '$data->{'priority'}' and cancellationdate is NULL
531 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
532 $sth2->execute || die $sth2->errstr;
533 while (my $data=$sth2->fetchrow_hashref){
534 $data->{'priority'}--;
535 $query="Update reserves set priority=$data->{'priority'} where
536 biblionumber=$data->{'biblionumber'} and
537 borrowernumber=$data->{'borrowernumber'}";
538 my $sth3=$dbh->prepare($query);
539 $sth3->execute || die $sth3->errstr;
543 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
544 and borrowernumber=$borrower";
546 my $sth=$dbh->prepare($query);
551 #subroutine to update a reserve
552 my ($rank,$biblio,$borrower,$branch)=@_;
553 return if $rank eq "W";
554 my $dbh = C4::Context->dbh;
555 if ($rank eq "del") {
556 my $query = "UPDATE reserves SET cancellationdate=now()
557 WHERE biblionumber = ?
558 AND borrowernumber = ?
559 AND cancellationdate is NULL
560 AND (found <> 'F' or found is NULL)";
561 my $sth=$dbh->prepare($query);
562 $sth->execute($biblio, $borrower);
565 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
566 WHERE biblionumber = ?
567 AND borrowernumber = ?
568 AND cancellationdate is NULL
569 AND (found <> 'F' or found is NULL)";
570 my $sth=$dbh->prepare($query);
571 $sth->execute($rank, $branch, $biblio, $borrower);
576 sub getreservetitle {
577 my ($biblio,$bor,$date,$timestamp)=@_;
578 my $dbh = C4::Context->dbh;
579 my $query="Select * from reserveconstraints,biblioitems where
580 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
581 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
582 = $bor and reserveconstraints.reservedate='$date' and
583 reserveconstraints.timestamp=$timestamp";
584 my $sth=$dbh->prepare($query);
586 my $data=$sth->fetchrow_hashref;
597 END { } # module clean-up code here (global destructor)