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};
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
268 WHERE reserves.biblionumber = items.biblionumber
269 AND items.itemnumber = $item
270 AND reserves.borrowernumber = $borr
271 AND reserves.cancellationdate is NULL
272 AND (reserves.found <> 'F' or reserves.found is NULL)";
273 my $sth = $dbh->prepare($query);
275 my $data = $sth->fetchrow_hashref;
277 my $biblio = $data->{'biblionumber'};
278 my $q_biblio = $dbh->quote($biblio);
279 # update reserves record....
280 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
281 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
282 $sth = $dbh->prepare($query);
286 # now fix up the remaining priorities....
287 fixpriority($data->{'priority'}, $biblio);
288 my $branchcode = $data->{'branchcode'};
295 $borr = $dbh->quote($borr);
297 my $query = "SELECT * FROM reserves
298 WHERE borrowernumber = $borr
299 AND reserves.found = 'W'
300 AND cancellationdate is NULL";
301 my $sth = $dbh->prepare($query);
304 if (my $data=$sth->fetchrow_hashref) {
305 @itemswaiting[$cnt] =$data;
309 return ($cnt,\@itemswaiting);
312 sub Findgroupreserve {
313 my ($bibitem,$biblio)=@_;
315 $bibitem=$dbh->quote($bibitem);
316 my $query = "SELECT reserves.biblionumber AS biblionumber,
317 reserves.borrowernumber AS borrowernumber,
318 reserves.reservedate AS reservedate,
319 reserves.branchcode AS branchcode,
320 reserves.cancellationdate AS cancellationdate,
321 reserves.found AS found,
322 reserves.reservenotes AS reservenotes,
323 reserves.priority AS priority,
324 reserves.timestamp AS timestamp,
325 reserveconstraints.biblioitemnumber AS biblioitemnumber,
326 reserves.itemnumber AS itemnumber
327 FROM reserves LEFT JOIN reserveconstraints
328 ON reserves.biblionumber = reserveconstraints.biblionumber
329 WHERE reserves.biblionumber = $biblio
330 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
331 AND reserves.borrowernumber = reserveconstraints.borrowernumber
332 AND reserves.reservedate =reserveconstraints.reservedate )
333 OR reserves.constrainttype='a' )
334 AND reserves.cancellationdate is NULL
335 AND (reserves.found <> 'F' or reserves.found is NULL)";
336 my $sth=$dbh->prepare($query);
340 while (my $data=$sth->fetchrow_hashref){
351 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
352 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
353 my $dbh = &C4Connect;
354 my $const = lc substr($constraint,0,1);
355 my @datearr = localtime(time);
356 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
358 # updates take place here
361 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
362 my $updquery = "insert into accountlines
363 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
365 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
366 my $usth = $dbh->prepare($updquery);
371 my $query="insert into reserves
372 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
374 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
375 my $sth = $dbh->prepare($query);
379 if (($const eq "o") || ($const eq "e")) {
380 my $numitems = @$bibitems;
382 while ($i < $numitems) {
383 my $biblioitem = @$bibitems[$i];
384 my $query = "insert into
386 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
388 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
389 my $sth = $dbh->prepare($query);
401 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
403 my $dbh = &C4Connect;
404 my $const = lc substr($constraint,0,1);
405 my $query = "SELECT * FROM borrowers,categories
406 WHERE (borrowernumber = ?)
407 AND (borrowers.categorycode = categories.categorycode)";
408 my $sth = $dbh->prepare($query);
409 $sth->execute($borrnum);
410 my $data = $sth->fetchrow_hashref;
412 my $fee = $data->{'reservefee'};
413 my $cntitems = @->$bibitems;
415 # check for items on issue
416 # first find biblioitem records
418 my $query1 = "SELECT * FROM biblio,biblioitems
419 WHERE (biblio.biblionumber = ?)
420 AND (biblio.biblionumber = biblioitems.biblionumber)";
421 my $sth1 = $dbh->prepare($query1);
422 $sth1->execute($biblionumber);
423 while (my $data1=$sth1->fetchrow_hashref) {
425 push @biblioitems,$data1;
429 while ($x < $cntitems) {
430 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
437 push @biblioitems,$data1;
441 push @biblioitems,$data1;
447 my $cntitemsfound = @biblioitems;
451 while ($x < $cntitemsfound) {
452 my $bitdata = $biblioitems[$x];
453 my $query2 = "SELECT * FROM items
454 WHERE biblioitemnumber = ?";
455 my $sth2 = $dbh->prepare($query2);
456 $sth2->execute($bitdata->{'biblioitemnumber'});
457 while (my $itdata=$sth2->fetchrow_hashref) {
458 my $query3 = "SELECT * FROM issues
460 AND returndate IS NULL";
462 my $sth3 = $dbh->prepare($query3);
463 $sth3->execute($itdata->{'itemnumber'});
464 if (my $isdata=$sth3->fetchrow_hashref) {
471 if ($allissued == 0) {
472 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
473 my $rsth = $dbh->prepare($rquery);
474 $rsth->execute($biblionumber);
475 if (my $rdata = $rsth->fetchrow_hashref) {
487 my ($env,$bornumber,$dbh)=@_;
489 my $query = "select * from accountlines
490 where (borrowernumber = '$bornumber')
491 order by accountno desc";
492 my $sth = $dbh->prepare($query);
494 if (my $accdata=$sth->fetchrow_hashref){
495 $nextaccntno = $accdata->{'accountno'} + 1;
498 return($nextaccntno);
502 #subroutine to update a reserve
503 my ($rank,$biblio,$borrower,$del,$branch)=@_;
505 my $query="Update reserves ";
507 $query.="set priority='$rank',branchcode='$branch' where
508 biblionumber=$biblio and borrowernumber=$borrower";
510 $query="Select * from reserves where biblionumber=$biblio and
511 borrowernumber=$borrower";
512 my $sth=$dbh->prepare($query);
514 my $data=$sth->fetchrow_hashref;
516 $query="Select * from reserves where biblionumber=$biblio and
517 priority > '$data->{'priority'}' and cancellationdate is NULL
519 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
520 $sth2->execute || die $sth2->errstr;
521 while (my $data=$sth2->fetchrow_hashref){
522 $data->{'priority'}--;
523 $query="Update reserves set priority=$data->{'priority'} where
524 biblionumber=$data->{'biblionumber'} and
525 borrowernumber=$data->{'borrowernumber'}";
526 my $sth3=$dbh->prepare($query);
527 $sth3->execute || die $sth3->errstr;
531 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
532 and borrowernumber=$borrower";
534 my $sth=$dbh->prepare($query);
540 sub getreservetitle {
541 my ($biblio,$bor,$date,$timestamp)=@_;
543 my $query="Select * from reserveconstraints,biblioitems where
544 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
545 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
546 = $bor and reserveconstraints.reservedate='$date' and
547 reserveconstraints.timestamp=$timestamp";
548 my $sth=$dbh->prepare($query);
550 my $data=$sth->fetchrow_hashref;
562 END { } # module clean-up code here (global destructor)