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
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 if ($res->{'itemnumber'} == $item) {
134 return ("Waiting", $res);
136 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
137 $priority = $res->{'priority'};
144 $highest->{'itemnumber'} = $item;
145 return ("Reserved", $highest);
152 my ($biblio, $item, $borr) = @_;
153 my $dbh = C4::Context->dbh;
154 #warn "In CancelReserve";
155 if (($item and $borr) and (not $biblio)) {
156 # removing a waiting reserve record....
157 $item = $dbh->quote($item);
158 $borr = $dbh->quote($borr);
159 # update the database...
160 my $query = "update reserves set cancellationdate = now(),
163 where itemnumber = $item
164 and borrowernumber = $borr";
165 my $sth = $dbh->prepare($query);
169 if (($biblio and $borr) and (not $item)) {
170 # removing a reserve record....
171 my $q_biblio = $dbh->quote($biblio);
172 $borr = $dbh->quote($borr);
173 # get the prioritiy on this record....
174 my $query = "SELECT priority FROM reserves
175 WHERE biblionumber = $q_biblio
176 AND borrowernumber = $borr
177 AND cancellationdate is NULL
178 AND (found <> 'F' or found is NULL)";
179 my $sth=$dbh->prepare($query);
181 my ($priority) = $sth->fetchrow_array;
183 # update the database, removing the record...
184 # FIXME - There's already a $query in this scope.
185 my $query = "update reserves set cancellationdate = now(),
188 where biblionumber = $q_biblio
189 and borrowernumber = $borr
190 and cancellationdate is NULL
191 and (found <> 'F' or found is NULL)";
192 # FIXME - There's already a $query in this scope.
193 # FIXME - There's already a $sth in this scope.
194 my $sth = $dbh->prepare($query);
195 # FIXME - There's already a $sth in this scope.
198 # now fix the priority on the others....
199 fixpriority($priority, $biblio);
206 my $dbh = C4::Context->dbh;
207 # fillinf a reserve record....
208 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
209 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
210 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
211 # get the prioritiy on this record....
212 my $query = "SELECT priority FROM reserves
213 WHERE biblionumber = $qbiblio
214 AND borrowernumber = $borr
215 AND reservedate = $resdate)";
216 my $sth=$dbh->prepare($query);
218 my ($priority) = $sth->fetchrow_array;
220 # update the database...
221 # FIXME - There's already a $query in this scope.
222 my $query = "UPDATE reserves SET found = 'F',
224 WHERE biblionumber = $qbiblio
225 AND reservedate = $resdate
226 AND borrowernumber = $borr";
227 # FIXME - There's already a $query in this scope.
228 # FIXME - There's already a $sth in this scope.
229 my $sth = $dbh->prepare($query);
230 # FIXME - There's already a $sth in this scope.
233 # now fix the priority on the others (if the priority wasnt already sorted!)....
234 unless ($priority == 0) {
235 fixpriority($priority, $biblio);
240 my ($priority, $biblio) = @_;
241 my $dbh = C4::Context->dbh;
242 my ($count, $reserves) = FindReserves($biblio);
243 foreach my $rec (@$reserves) {
244 if ($rec->{'priority'} > $priority) {
245 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
246 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
247 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
248 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
249 my $query = "UPDATE reserves SET priority = $newpr
250 WHERE biblionumber = $nbib
251 AND borrowernumber = $nbor
252 AND reservedate = $nresd";
254 my $sth = $dbh->prepare($query);
264 my ($item, $borr) = @_;
265 my $dbh = C4::Context->dbh;
266 $item = $dbh->quote($item);
267 $borr = $dbh->quote($borr);
268 # get priority and biblionumber....
269 my $query = "SELECT reserves.priority as priority,
270 reserves.biblionumber as biblionumber,
271 reserves.branchcode as branchcode,
272 reserves.timestamp as timestamp
274 WHERE reserves.biblionumber = items.biblionumber
275 AND items.itemnumber = $item
276 AND reserves.borrowernumber = $borr
277 AND reserves.cancellationdate is NULL
278 AND (reserves.found <> 'F' or reserves.found is NULL)";
279 my $sth = $dbh->prepare($query);
281 my $data = $sth->fetchrow_hashref;
283 my $biblio = $data->{'biblionumber'};
284 my $timestamp = $data->{'timestamp'};
285 my $q_biblio = $dbh->quote($biblio);
286 my $q_timestamp = $dbh->quote($timestamp);
287 warn "Timestamp: ".$timestamp."\n";
288 # update reserves record....
289 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
290 WHERE borrowernumber = $borr
291 AND biblionumber = $q_biblio
292 AND timestamp = $q_timestamp";
293 warn "Query: ".$query."\n";
294 $sth = $dbh->prepare($query);
297 # now fix up the remaining priorities....
298 fixpriority($data->{'priority'}, $biblio);
299 my $branchcode = $data->{'branchcode'};
305 my $dbh = C4::Context->dbh;
306 $borr = $dbh->quote($borr);
308 my $query = "SELECT * FROM reserves
309 WHERE borrowernumber = $borr
310 AND reserves.found = 'W'
311 AND cancellationdate is NULL";
312 my $sth = $dbh->prepare($query);
316 if (my $data=$sth->fetchrow_hashref) {
317 $itemswaiting[$cnt] =$data;
321 return ($cnt,\@itemswaiting);
324 sub Findgroupreserve {
325 my ($bibitem,$biblio)=@_;
326 my $dbh = C4::Context->dbh;
327 $bibitem=$dbh->quote($bibitem);
328 my $query = "SELECT reserves.biblionumber AS biblionumber,
329 reserves.borrowernumber AS borrowernumber,
330 reserves.reservedate AS reservedate,
331 reserves.branchcode AS branchcode,
332 reserves.cancellationdate AS cancellationdate,
333 reserves.found AS found,
334 reserves.reservenotes AS reservenotes,
335 reserves.priority AS priority,
336 reserves.timestamp AS timestamp,
337 reserveconstraints.biblioitemnumber AS biblioitemnumber,
338 reserves.itemnumber AS itemnumber
339 FROM reserves LEFT JOIN reserveconstraints
340 ON reserves.biblionumber = reserveconstraints.biblionumber
341 WHERE reserves.biblionumber = $biblio
342 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
343 AND reserves.borrowernumber = reserveconstraints.borrowernumber
344 AND reserves.reservedate =reserveconstraints.reservedate )
345 OR reserves.constrainttype='a' )
346 AND reserves.cancellationdate is NULL
347 AND (reserves.found <> 'F' or reserves.found is NULL)";
348 my $sth=$dbh->prepare($query);
352 while (my $data=$sth->fetchrow_hashref){
362 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
363 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
364 my $dbh = C4::Context->dbh;
365 my $const = lc substr($constraint,0,1);
366 my @datearr = localtime(time);
367 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
369 # updates take place here
372 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
373 my $updquery = "insert into accountlines
374 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
376 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
377 my $usth = $dbh->prepare($updquery);
382 my $query="insert into reserves
383 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
385 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
386 my $sth = $dbh->prepare($query);
390 if (($const eq "o") || ($const eq "e")) {
391 my $numitems = @$bibitems;
393 while ($i < $numitems) {
394 my $biblioitem = @$bibitems[$i];
395 my $query = "insert into
397 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
399 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
400 my $sth = $dbh->prepare($query);
411 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
413 my $dbh = C4::Context->dbh;
414 my $const = lc substr($constraint,0,1);
415 my $query = "SELECT * FROM borrowers,categories
416 WHERE (borrowernumber = ?)
417 AND (borrowers.categorycode = categories.categorycode)";
418 my $sth = $dbh->prepare($query);
419 $sth->execute($borrnum);
420 my $data = $sth->fetchrow_hashref;
422 my $fee = $data->{'reservefee'};
423 my $cntitems = @->$bibitems;
425 # check for items on issue
426 # first find biblioitem records
428 my $query1 = "SELECT * FROM biblio,biblioitems
429 WHERE (biblio.biblionumber = ?)
430 AND (biblio.biblionumber = biblioitems.biblionumber)";
431 my $sth1 = $dbh->prepare($query1);
432 $sth1->execute($biblionumber);
433 while (my $data1=$sth1->fetchrow_hashref) {
435 push @biblioitems,$data1;
439 while ($x < $cntitems) {
440 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
447 push @biblioitems,$data1;
451 push @biblioitems,$data1;
457 my $cntitemsfound = @biblioitems;
461 while ($x < $cntitemsfound) {
462 my $bitdata = $biblioitems[$x];
463 my $query2 = "SELECT * FROM items
464 WHERE biblioitemnumber = ?";
465 my $sth2 = $dbh->prepare($query2);
466 $sth2->execute($bitdata->{'biblioitemnumber'});
467 while (my $itdata=$sth2->fetchrow_hashref) {
468 my $query3 = "SELECT * FROM issues
470 AND returndate IS NULL";
472 my $sth3 = $dbh->prepare($query3);
473 $sth3->execute($itdata->{'itemnumber'});
474 if (my $isdata=$sth3->fetchrow_hashref) {
481 if ($allissued == 0) {
482 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
483 my $rsth = $dbh->prepare($rquery);
484 $rsth->execute($biblionumber);
485 if (my $rdata = $rsth->fetchrow_hashref) {
496 my ($env,$bornumber,$dbh)=@_;
498 my $query = "select * from accountlines
499 where (borrowernumber = '$bornumber')
500 order by accountno desc";
501 my $sth = $dbh->prepare($query);
503 if (my $accdata=$sth->fetchrow_hashref){
504 $nextaccntno = $accdata->{'accountno'} + 1;
507 return($nextaccntno);
511 #subroutine to update a reserve
512 my ($rank,$biblio,$borrower,$del,$branch)=@_;
513 my $dbh = C4::Context->dbh;
514 my $query="Update reserves ";
516 $query.="set priority='$rank',branchcode='$branch' where
517 biblionumber=$biblio and borrowernumber=$borrower";
519 $query="Select * from reserves where biblionumber=$biblio and
520 borrowernumber=$borrower";
521 my $sth=$dbh->prepare($query);
523 my $data=$sth->fetchrow_hashref;
525 $query="Select * from reserves where biblionumber=$biblio and
526 priority > '$data->{'priority'}' and cancellationdate is NULL
528 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
529 $sth2->execute || die $sth2->errstr;
530 while (my $data=$sth2->fetchrow_hashref){
531 $data->{'priority'}--;
532 $query="Update reserves set priority=$data->{'priority'} where
533 biblionumber=$data->{'biblionumber'} and
534 borrowernumber=$data->{'borrowernumber'}";
535 my $sth3=$dbh->prepare($query);
536 $sth3->execute || die $sth3->errstr;
540 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
541 and borrowernumber=$borrower";
543 my $sth=$dbh->prepare($query);
548 #subroutine to update a reserve
549 my ($rank,$biblio,$borrower,$branch)=@_;
550 return if $rank eq "W";
551 my $dbh = C4::Context->dbh;
552 if ($rank eq "del") {
553 my $query = "UPDATE reserves SET cancellationdate=now()
554 WHERE biblionumber = ?
555 AND borrowernumber = ?
556 AND cancellationdate is NULL
557 AND (found <> 'F' or found is NULL)";
558 my $sth=$dbh->prepare($query);
559 $sth->execute($biblio, $borrower);
562 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
563 WHERE biblionumber = ?
564 AND borrowernumber = ?
565 AND cancellationdate is NULL
566 AND (found <> 'F' or found is NULL)";
567 my $sth=$dbh->prepare($query);
568 $sth->execute($rank, $branch, $biblio, $borrower);
573 sub getreservetitle {
574 my ($biblio,$bor,$date,$timestamp)=@_;
575 my $dbh = C4::Context->dbh;
576 my $query="Select * from reserveconstraints,biblioitems where
577 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
578 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
579 = $bor and reserveconstraints.reservedate='$date' and
580 reserveconstraints.timestamp=$timestamp";
581 my $sth=$dbh->prepare($query);
583 my $data=$sth->fetchrow_hashref;
594 END { } # module clean-up code here (global destructor)