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
28 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
30 # set the version for version checking
34 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
36 # make all your functions, whether exported or not;
41 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
42 FROM reserves,borrowers,biblio ";
44 $bib = $dbh->quote($bib);
46 $bor = $dbh->quote($bor);
47 $query .= " where reserves.biblionumber = $bib
48 and borrowers.borrowernumber = $bor
49 and reserves.borrowernumber = borrowers.borrowernumber
50 and biblio.biblionumber = $bib
51 and cancellationdate is NULL
52 and (found <> 'F' or found is NULL)";
54 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
55 and biblio.biblionumber = $bib
56 and reserves.biblionumber = $bib
57 and cancellationdate is NULL
58 and (found <> 'F' or found is NULL)";
61 $query .= " where borrowers.borrowernumber = $bor
62 and reserves.borrowernumber = borrowers.borrowernumber
63 and reserves.biblionumber = biblio.biblionumber
64 and cancellationdate is NULL and
65 (found <> 'F' or found is NULL)";
67 $query.=" order by priority";
68 my $sth=$dbh->prepare($query);
72 while (my $data=$sth->fetchrow_hashref){
73 if ($data->{'constrainttype'} eq 'o') {
74 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
75 WHERE biblionumber = ?
76 AND borrowernumber = ?
78 my $csth=$dbh->prepare($conquery);
79 my $bibn = $data->{'biblionumber'};
80 my $born = $data->{'borrowernumber'};
81 my $resd = $data->{'reservedate'};
82 $csth->execute($bibn, $born, $resd);
83 my ($bibitemno) = $csth->fetchrow_array;
85 my $bdata = C4::Search::bibitemdata($bibitemno);
86 foreach my $key (keys %$bdata) {
87 $data->{$key} = $bdata->{$key};
101 warn "In CheckReserves: itemnumber = $item";
103 my $qitem=$dbh->quote($item);
104 # get the biblionumber...
105 my $sth=$dbh->prepare("SELECT items.biblionumber, items.biblioitemnumber, itemtypes.notforloan
106 FROM items, biblioitems, itemtypes
107 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
108 AND biblioitems.itemtype = itemtypes.itemtype
109 AND itemnumber=$qitem");
111 my ($biblio, $bibitem, $notforloan) = $sth->fetchrow_array;
114 # if item is not for loan it cannot be reserved either.....
115 return (0, 0) if ($notforloan);
116 # get the reserves...
117 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
118 my $priority = 10000000;
121 foreach my $res (@reserves) {
122 if ($res->{'itemnumber'} == $item) {
123 return ("Waiting", $res);
125 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
126 $priority = $res->{'priority'};
133 $highest->{'itemnumber'} = $item;
134 return ("Reserved", $highest);
141 my ($biblio, $item, $borr) = @_;
143 #warn "In CancelReserve";
144 if (($item and $borr) and (not $biblio)) {
145 # removing a waiting reserve record....
146 $item = $dbh->quote($item);
147 $borr = $dbh->quote($borr);
148 # update the database...
149 my $query = "update reserves set cancellationdate = now(),
152 where itemnumber = $item
153 and borrowernumber = $borr";
154 my $sth = $dbh->prepare($query);
158 if (($biblio and $borr) and (not $item)) {
159 # removing a reserve record....
160 my $q_biblio = $dbh->quote($biblio);
161 $borr = $dbh->quote($borr);
162 # get the prioritiy on this record....
163 my $query = "SELECT priority FROM reserves
164 WHERE biblionumber = $q_biblio
165 AND borrowernumber = $borr
166 AND cancellationdate is NULL
167 AND (found <> 'F' or found is NULL)";
168 my $sth=$dbh->prepare($query);
170 my ($priority) = $sth->fetchrow_array;
172 # update the database, removing the record...
173 my $query = "update reserves set cancellationdate = now(),
176 where biblionumber = $q_biblio
177 and borrowernumber = $borr
178 and cancellationdate is NULL
179 and (found <> 'F' or found is NULL)";
180 my $sth = $dbh->prepare($query);
183 # now fix the priority on the others....
184 fixpriority($priority, $biblio);
193 # fillinf a reserve record....
194 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
195 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
196 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
197 # get the prioritiy on this record....
198 my $query = "SELECT priority FROM reserves
199 WHERE biblionumber = $qbiblio
200 AND borrowernumber = $borr
201 AND reservedate = $resdate)";
202 my $sth=$dbh->prepare($query);
204 my ($priority) = $sth->fetchrow_array;
206 # update the database...
207 my $query = "UPDATE reserves SET found = 'F',
209 WHERE biblionumber = $qbiblio
210 AND reservedate = $resdate
211 AND borrowernumber = $borr";
212 my $sth = $dbh->prepare($query);
215 # now fix the priority on the others (if the priority wasnt already sorted!)....
216 unless ($priority == 0) {
217 fixpriority($priority, $biblio);
223 my ($priority, $biblio) = @_;
225 my ($count, $reserves) = FindReserves($biblio);
226 foreach my $rec (@$reserves) {
227 if ($rec->{'priority'} > $priority) {
228 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
229 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
230 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
231 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
232 my $query = "UPDATE reserves SET priority = $newpr
233 WHERE biblionumber = $nbib
234 AND borrowernumber = $nbor
235 AND reservedate = $nresd";
237 my $sth = $dbh->prepare($query);
248 my ($item, $borr) = @_;
250 $item = $dbh->quote($item);
251 $borr = $dbh->quote($borr);
252 # get priority and biblionumber....
253 my $query = "SELECT reserves.priority as priority,
254 reserves.biblionumber as biblionumber,
255 reserves.branchcode as branchcode
257 WHERE reserves.biblionumber = items.biblionumber
258 AND items.itemnumber = $item
259 AND reserves.borrowernumber = $borr
260 AND reserves.cancellationdate is NULL
261 AND (reserves.found <> 'F' or reserves.found is NULL)";
262 my $sth = $dbh->prepare($query);
264 my $data = $sth->fetchrow_hashref;
266 my $biblio = $data->{'biblionumber'};
267 my $q_biblio = $dbh->quote($biblio);
268 # update reserves record....
269 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
270 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
271 $sth = $dbh->prepare($query);
275 # now fix up the remaining priorities....
276 fixpriority($data->{'priority'}, $biblio);
277 my $branchcode = $data->{'branchcode'};
284 $borr = $dbh->quote($borr);
286 my $query = "SELECT * FROM reserves
287 WHERE borrowernumber = $borr
288 AND reserves.found = 'W'
289 AND cancellationdate is NULL";
290 my $sth = $dbh->prepare($query);
293 if (my $data=$sth->fetchrow_hashref) {
294 @itemswaiting[$cnt] =$data;
298 return ($cnt,\@itemswaiting);
301 sub Findgroupreserve {
302 my ($bibitem,$biblio)=@_;
304 $bibitem=$dbh->quote($bibitem);
305 my $query = "SELECT reserves.biblionumber AS biblionumber,
306 reserves.borrowernumber AS borrowernumber,
307 reserves.reservedate AS reservedate,
308 reserves.branchcode AS branchcode,
309 reserves.cancellationdate AS cancellationdate,
310 reserves.found AS found,
311 reserves.reservenotes AS reservenotes,
312 reserves.priority AS priority,
313 reserves.timestamp AS timestamp,
314 reserveconstraints.biblioitemnumber AS biblioitemnumber,
315 reserves.itemnumber AS itemnumber
316 FROM reserves LEFT JOIN reserveconstraints
317 ON reserves.biblionumber = reserveconstraints.biblionumber
318 WHERE reserves.biblionumber = $biblio
319 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
320 AND reserves.borrowernumber = reserveconstraints.borrowernumber
321 AND reserves.reservedate =reserveconstraints.reservedate )
322 OR reserves.constrainttype='a' )
323 AND reserves.cancellationdate is NULL
324 AND (reserves.found <> 'F' or reserves.found is NULL)";
325 my $sth=$dbh->prepare($query);
329 while (my $data=$sth->fetchrow_hashref){
340 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
341 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
342 my $dbh = &C4Connect;
343 my $const = lc substr($constraint,0,1);
344 my @datearr = localtime(time);
345 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
347 # updates take place here
350 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
351 my $updquery = "insert into accountlines
352 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
354 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
355 my $usth = $dbh->prepare($updquery);
360 my $query="insert into reserves
361 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
363 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
364 my $sth = $dbh->prepare($query);
368 if (($const eq "o") || ($const eq "e")) {
369 my $numitems = @$bibitems;
371 while ($i < $numitems) {
372 my $biblioitem = @$bibitems[$i];
373 my $query = "insert into
375 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
377 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
378 my $sth = $dbh->prepare($query);
390 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
392 my $dbh = &C4Connect;
393 my $const = lc substr($constraint,0,1);
394 my $query = "SELECT * FROM borrowers,categories
395 WHERE (borrowernumber = ?)
396 AND (borrowers.categorycode = categories.categorycode)";
397 my $sth = $dbh->prepare($query);
398 $sth->execute($borrnum);
399 my $data = $sth->fetchrow_hashref;
401 my $fee = $data->{'reservefee'};
402 my $cntitems = @->$bibitems;
404 # check for items on issue
405 # first find biblioitem records
407 my $query1 = "SELECT * FROM biblio,biblioitems
408 WHERE (biblio.biblionumber = ?)
409 AND (biblio.biblionumber = biblioitems.biblionumber)";
410 my $sth1 = $dbh->prepare($query1);
411 $sth1->execute($biblionumber);
412 while (my $data1=$sth1->fetchrow_hashref) {
414 push @biblioitems,$data1;
418 while ($x < $cntitems) {
419 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
426 push @biblioitems,$data1;
430 push @biblioitems,$data1;
436 my $cntitemsfound = @biblioitems;
440 while ($x < $cntitemsfound) {
441 my $bitdata = $biblioitems[$x];
442 my $query2 = "SELECT * FROM items
443 WHERE biblioitemnumber = ?";
444 my $sth2 = $dbh->prepare($query2);
445 $sth2->execute($bitdata->{'biblioitemnumber'});
446 while (my $itdata=$sth2->fetchrow_hashref) {
447 my $query3 = "SELECT * FROM issues
449 AND returndate IS NULL";
451 my $sth3 = $dbh->prepare($query3);
452 $sth3->execute($itdata->{'itemnumber'});
453 if (my $isdata=$sth3->fetchrow_hashref) {
460 if ($allissued == 0) {
461 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
462 my $rsth = $dbh->prepare($rquery);
463 $rsth->execute($biblionumber);
464 if (my $rdata = $rsth->fetchrow_hashref) {
476 my ($env,$bornumber,$dbh)=@_;
478 my $query = "select * from accountlines
479 where (borrowernumber = '$bornumber')
480 order by accountno desc";
481 my $sth = $dbh->prepare($query);
483 if (my $accdata=$sth->fetchrow_hashref){
484 $nextaccntno = $accdata->{'accountno'} + 1;
487 return($nextaccntno);
491 #subroutine to update a reserve
492 my ($rank,$biblio,$borrower,$del,$branch)=@_;
494 my $query="Update reserves ";
496 $query.="set priority='$rank',branchcode='$branch' where
497 biblionumber=$biblio and borrowernumber=$borrower";
499 $query="Select * from reserves where biblionumber=$biblio and
500 borrowernumber=$borrower";
501 my $sth=$dbh->prepare($query);
503 my $data=$sth->fetchrow_hashref;
505 $query="Select * from reserves where biblionumber=$biblio and
506 priority > '$data->{'priority'}' and cancellationdate is NULL
508 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
509 $sth2->execute || die $sth2->errstr;
510 while (my $data=$sth2->fetchrow_hashref){
511 $data->{'priority'}--;
512 $query="Update reserves set priority=$data->{'priority'} where
513 biblionumber=$data->{'biblionumber'} and
514 borrowernumber=$data->{'borrowernumber'}";
515 my $sth3=$dbh->prepare($query);
516 $sth3->execute || die $sth3->errstr;
520 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
521 and borrowernumber=$borrower";
523 my $sth=$dbh->prepare($query);
529 sub getreservetitle {
530 my ($biblio,$bor,$date,$timestamp)=@_;
532 my $query="Select * from reserveconstraints,biblioitems where
533 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
534 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
535 = $bor and reserveconstraints.reservedate='$date' and
536 reserveconstraints.timestamp=$timestamp";
537 my $sth=$dbh->prepare($query);
539 my $data=$sth->fetchrow_hashref;
551 END { } # module clean-up code here (global destructor)