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 &UpdateReserve &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};
100 my ($item, $barcode) = @_;
101 # warn "In CheckReserves: itemnumber = $item";
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
122 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) = @_;
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 my $query = "update reserves set cancellationdate = now(),
187 where biblionumber = $q_biblio
188 and borrowernumber = $borr
189 and cancellationdate is NULL
190 and (found <> 'F' or found is NULL)";
191 my $sth = $dbh->prepare($query);
194 # now fix the priority on the others....
195 fixpriority($priority, $biblio);
204 # fillinf a reserve record....
205 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
206 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
207 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
208 # get the prioritiy on this record....
209 my $query = "SELECT priority FROM reserves
210 WHERE biblionumber = $qbiblio
211 AND borrowernumber = $borr
212 AND reservedate = $resdate)";
213 my $sth=$dbh->prepare($query);
215 my ($priority) = $sth->fetchrow_array;
217 # update the database...
218 my $query = "UPDATE reserves SET found = 'F',
220 WHERE biblionumber = $qbiblio
221 AND reservedate = $resdate
222 AND borrowernumber = $borr";
223 my $sth = $dbh->prepare($query);
226 # now fix the priority on the others (if the priority wasnt already sorted!)....
227 unless ($priority == 0) {
228 fixpriority($priority, $biblio);
234 my ($priority, $biblio) = @_;
236 my ($count, $reserves) = FindReserves($biblio);
237 foreach my $rec (@$reserves) {
238 if ($rec->{'priority'} > $priority) {
239 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
240 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
241 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
242 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
243 my $query = "UPDATE reserves SET priority = $newpr
244 WHERE biblionumber = $nbib
245 AND borrowernumber = $nbor
246 AND reservedate = $nresd";
248 my $sth = $dbh->prepare($query);
259 my ($item, $borr) = @_;
261 $item = $dbh->quote($item);
262 $borr = $dbh->quote($borr);
263 # get priority and biblionumber....
264 my $query = "SELECT reserves.priority as priority,
265 reserves.biblionumber as biblionumber,
266 reserves.branchcode as branchcode,
267 reserves.timestamp as timestamp
269 WHERE reserves.biblionumber = items.biblionumber
270 AND items.itemnumber = $item
271 AND reserves.borrowernumber = $borr
272 AND reserves.cancellationdate is NULL
273 AND (reserves.found <> 'F' or reserves.found is NULL)";
274 my $sth = $dbh->prepare($query);
276 my $data = $sth->fetchrow_hashref;
278 my $biblio = $data->{'biblionumber'};
279 my $timestamp = $data->{'timestamp'};
280 my $q_biblio = $dbh->quote($biblio);
281 my $q_timestamp = $dbh->quote($timestamp);
282 warn "Timestamp: ".$timestamp."\n";
283 # update reserves record....
284 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
285 WHERE borrowernumber = $borr
286 AND biblionumber = $q_biblio
287 AND timestamp = $q_timestamp";
288 warn "Query: ".$query."\n";
289 $sth = $dbh->prepare($query);
293 # now fix up the remaining priorities....
294 fixpriority($data->{'priority'}, $biblio);
295 my $branchcode = $data->{'branchcode'};
302 $borr = $dbh->quote($borr);
304 my $query = "SELECT * FROM reserves
305 WHERE borrowernumber = $borr
306 AND reserves.found = 'W'
307 AND cancellationdate is NULL";
308 my $sth = $dbh->prepare($query);
311 if (my $data=$sth->fetchrow_hashref) {
312 @itemswaiting[$cnt] =$data;
316 return ($cnt,\@itemswaiting);
319 sub Findgroupreserve {
320 my ($bibitem,$biblio)=@_;
322 $bibitem=$dbh->quote($bibitem);
323 my $query = "SELECT reserves.biblionumber AS biblionumber,
324 reserves.borrowernumber AS borrowernumber,
325 reserves.reservedate AS reservedate,
326 reserves.branchcode AS branchcode,
327 reserves.cancellationdate AS cancellationdate,
328 reserves.found AS found,
329 reserves.reservenotes AS reservenotes,
330 reserves.priority AS priority,
331 reserves.timestamp AS timestamp,
332 reserveconstraints.biblioitemnumber AS biblioitemnumber,
333 reserves.itemnumber AS itemnumber
334 FROM reserves LEFT JOIN reserveconstraints
335 ON reserves.biblionumber = reserveconstraints.biblionumber
336 WHERE reserves.biblionumber = $biblio
337 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
338 AND reserves.borrowernumber = reserveconstraints.borrowernumber
339 AND reserves.reservedate =reserveconstraints.reservedate )
340 OR reserves.constrainttype='a' )
341 AND reserves.cancellationdate is NULL
342 AND (reserves.found <> 'F' or reserves.found is NULL)";
343 my $sth=$dbh->prepare($query);
347 while (my $data=$sth->fetchrow_hashref){
358 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
359 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
360 my $dbh = &C4Connect;
361 my $const = lc substr($constraint,0,1);
362 my @datearr = localtime(time);
363 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
365 # updates take place here
368 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
369 my $updquery = "insert into accountlines
370 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
372 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
373 my $usth = $dbh->prepare($updquery);
378 my $query="insert into reserves
379 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
381 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
382 my $sth = $dbh->prepare($query);
386 if (($const eq "o") || ($const eq "e")) {
387 my $numitems = @$bibitems;
389 while ($i < $numitems) {
390 my $biblioitem = @$bibitems[$i];
391 my $query = "insert into
393 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
395 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
396 my $sth = $dbh->prepare($query);
408 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
410 my $dbh = &C4Connect;
411 my $const = lc substr($constraint,0,1);
412 my $query = "SELECT * FROM borrowers,categories
413 WHERE (borrowernumber = ?)
414 AND (borrowers.categorycode = categories.categorycode)";
415 my $sth = $dbh->prepare($query);
416 $sth->execute($borrnum);
417 my $data = $sth->fetchrow_hashref;
419 my $fee = $data->{'reservefee'};
420 my $cntitems = @->$bibitems;
422 # check for items on issue
423 # first find biblioitem records
425 my $query1 = "SELECT * FROM biblio,biblioitems
426 WHERE (biblio.biblionumber = ?)
427 AND (biblio.biblionumber = biblioitems.biblionumber)";
428 my $sth1 = $dbh->prepare($query1);
429 $sth1->execute($biblionumber);
430 while (my $data1=$sth1->fetchrow_hashref) {
432 push @biblioitems,$data1;
436 while ($x < $cntitems) {
437 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
444 push @biblioitems,$data1;
448 push @biblioitems,$data1;
454 my $cntitemsfound = @biblioitems;
458 while ($x < $cntitemsfound) {
459 my $bitdata = $biblioitems[$x];
460 my $query2 = "SELECT * FROM items
461 WHERE biblioitemnumber = ?";
462 my $sth2 = $dbh->prepare($query2);
463 $sth2->execute($bitdata->{'biblioitemnumber'});
464 while (my $itdata=$sth2->fetchrow_hashref) {
465 my $query3 = "SELECT * FROM issues
467 AND returndate IS NULL";
469 my $sth3 = $dbh->prepare($query3);
470 $sth3->execute($itdata->{'itemnumber'});
471 if (my $isdata=$sth3->fetchrow_hashref) {
478 if ($allissued == 0) {
479 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
480 my $rsth = $dbh->prepare($rquery);
481 $rsth->execute($biblionumber);
482 if (my $rdata = $rsth->fetchrow_hashref) {
494 my ($env,$bornumber,$dbh)=@_;
496 my $query = "select * from accountlines
497 where (borrowernumber = '$bornumber')
498 order by accountno desc";
499 my $sth = $dbh->prepare($query);
501 if (my $accdata=$sth->fetchrow_hashref){
502 $nextaccntno = $accdata->{'accountno'} + 1;
505 return($nextaccntno);
509 #subroutine to update a reserve
510 my ($rank,$biblio,$borrower,$del,$branch)=@_;
512 my $query="Update reserves ";
514 $query.="set priority='$rank',branchcode='$branch' where
515 biblionumber=$biblio and borrowernumber=$borrower";
517 $query="Select * from reserves where biblionumber=$biblio and
518 borrowernumber=$borrower";
519 my $sth=$dbh->prepare($query);
521 my $data=$sth->fetchrow_hashref;
523 $query="Select * from reserves where biblionumber=$biblio and
524 priority > '$data->{'priority'}' and cancellationdate is NULL
526 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
527 $sth2->execute || die $sth2->errstr;
528 while (my $data=$sth2->fetchrow_hashref){
529 $data->{'priority'}--;
530 $query="Update reserves set priority=$data->{'priority'} where
531 biblionumber=$data->{'biblionumber'} and
532 borrowernumber=$data->{'borrowernumber'}";
533 my $sth3=$dbh->prepare($query);
534 $sth3->execute || die $sth3->errstr;
538 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
539 and borrowernumber=$borrower";
541 my $sth=$dbh->prepare($query);
547 #subroutine to update a reserve
548 my ($rank,$biblio,$borrower,$branch)=@_;
549 return if $rank eq "W";
551 if ($rank eq "del") {
552 my $query = "UPDATE reserves SET cancellationdate=now()
553 WHERE biblionumber = ?
554 AND borrowernumber = ?
555 AND cancellationdate is NULL
556 AND (found <> 'F' or found is NULL)";
557 my $sth=$dbh->prepare($query);
558 $sth->execute($biblio, $borrower);
561 my $query = "UPDATE reserves SET priority = ? ,branchcode = ?, itemnumber = NULL, found = NULL
562 WHERE biblionumber = ?
563 AND borrowernumber = ?
564 AND cancellationdate is NULL
565 AND (found <> 'F' or found is NULL)";
566 my $sth=$dbh->prepare($query);
567 $sth->execute($rank, $branch, $biblio, $borrower);
573 sub getreservetitle {
574 my ($biblio,$bor,$date,$timestamp)=@_;
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;
595 END { } # module clean-up code here (global destructor)