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";
103 my $qitem=$dbh->quote($item);
104 # get the biblionumber...
105 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
107 my ($biblio, $bibitem) = $sth->fetchrow_array;
110 # get the reserves...
111 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
112 my $priority = 10000000;
115 foreach my $res (@reserves) {
116 if ($res->{'itemnumber'} == $item) {
117 return ("Waiting", $res);
119 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
120 $priority = $res->{'priority'};
125 $highest->{'itemnumber'} = $item;
126 return ("Reserved", $highest);
133 my ($biblio, $item, $borr) = @_;
135 #warn "In CancelReserve";
136 if (($item and $borr) and (not $biblio)) {
137 # removing a waiting reserve record....
138 $item = $dbh->quote($item);
139 $borr = $dbh->quote($borr);
140 # update the database...
141 my $query = "update reserves set cancellationdate = now(),
144 where itemnumber = $item
145 and borrowernumber = $borr";
146 my $sth = $dbh->prepare($query);
150 if (($biblio and $borr) and (not $item)) {
151 # removing a reserve record....
152 my $q_biblio = $dbh->quote($biblio);
153 $borr = $dbh->quote($borr);
154 # fix up the priorities on the other records....
155 my $query = "SELECT priority FROM reserves
156 WHERE biblionumber = $q_biblio
157 AND borrowernumber = $borr
158 AND cancellationdate is NULL
159 AND (found <> 'F' or found is NULL)";
160 my $sth=$dbh->prepare($query);
162 my ($priority) = $sth->fetchrow_array;
164 # update the database, removing the record...
165 my $query = "update reserves set cancellationdate = now(),
168 where biblionumber = $q_biblio
169 and borrowernumber = $borr
170 and cancellationdate is NULL
171 and (found <> 'F' or found is NULL)";
172 my $sth = $dbh->prepare($query);
175 # now fix the priority on the others....
176 fixpriority($priority, $biblio);
185 # removing a waiting reserve record....
186 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
187 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
188 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
189 # update the database...
190 my $query = "UPDATE reserves SET found = 'F',
192 WHERE biblionumber = $qbiblio
193 AND reservedate = $resdate
194 AND borrowernumber = $borr";
195 my $sth = $dbh->prepare($query);
202 my ($priority, $biblio) = @_;
204 my ($count, $reserves) = FindReserves($biblio);
205 foreach my $rec (@$reserves) {
206 if ($rec->{'priority'} > $priority) {
207 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
208 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
209 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
210 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
211 my $query = "UPDATE reserves SET priority = $newpr
212 WHERE biblionumber = $nbib
213 AND borrowernumber = $nbor
214 AND reservedate = $nresd";
216 my $sth = $dbh->prepare($query);
227 my ($item, $borr) = @_;
229 $item = $dbh->quote($item);
230 $borr = $dbh->quote($borr);
231 # get priority and biblionumber....
232 my $query = "SELECT reserves.priority as priority,
233 reserves.biblionumber as biblionumber,
234 reserves.branchcode as branchcode
236 WHERE reserves.biblionumber = items.biblionumber
237 AND items.itemnumber = $item
238 AND reserves.borrowernumber = $borr
239 AND reserves.cancellationdate is NULL
240 AND (reserves.found <> 'F' or reserves.found is NULL)";
241 my $sth = $dbh->prepare($query);
243 my $data = $sth->fetchrow_hashref;
245 my $biblio = $data->{'biblionumber'};
246 my $q_biblio = $dbh->quote($biblio);
247 # update reserves record....
248 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
249 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
250 $sth = $dbh->prepare($query);
254 # now fix up the remaining priorities....
255 fixpriority($data->{'priority'}, $biblio);
256 my $branchcode = $data->{'branchcode'};
263 $borr = $dbh->quote($borr);
265 my $query = "SELECT * FROM reserves
266 WHERE borrowernumber = $borr
267 AND reserves.found = 'W'
268 AND cancellationdate is NULL";
269 my $sth = $dbh->prepare($query);
272 if (my $data=$sth->fetchrow_hashref) {
273 @itemswaiting[$cnt] =$data;
277 return ($cnt,\@itemswaiting);
280 sub Findgroupreserve {
281 my ($bibitem,$biblio)=@_;
283 $bibitem=$dbh->quote($bibitem);
284 my $query = "SELECT reserves.biblionumber AS biblionumber,
285 reserves.borrowernumber AS borrowernumber,
286 reserves.reservedate AS reservedate,
287 reserves.branchcode AS branchcode,
288 reserves.cancellationdate AS cancellationdate,
289 reserves.found AS found,
290 reserves.reservenotes AS reservenotes,
291 reserves.priority AS priority,
292 reserves.timestamp AS timestamp,
293 reserveconstraints.biblioitemnumber AS biblioitemnumber,
294 reserves.itemnumber AS itemnumber
295 FROM reserves LEFT JOIN reserveconstraints
296 ON reserves.biblionumber = reserveconstraints.biblionumber
297 WHERE reserves.biblionumber = $biblio
298 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
299 AND reserves.borrowernumber = reserveconstraints.borrowernumber
300 AND reserves.reservedate =reserveconstraints.reservedate )
301 OR reserves.constrainttype='a' )
302 AND reserves.cancellationdate is NULL
303 AND (reserves.found <> 'F' or reserves.found is NULL)";
304 my $sth=$dbh->prepare($query);
308 while (my $data=$sth->fetchrow_hashref){
319 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
320 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
321 my $dbh = &C4Connect;
322 my $const = lc substr($constraint,0,1);
323 my @datearr = localtime(time);
324 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
326 # updates take place here
329 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
330 my $updquery = "insert into accountlines
331 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
333 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
334 my $usth = $dbh->prepare($updquery);
339 my $query="insert into reserves
340 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
342 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
343 my $sth = $dbh->prepare($query);
347 if (($const eq "o") || ($const eq "e")) {
348 my $numitems = @$bibitems;
350 while ($i < $numitems) {
351 my $biblioitem = @$bibitems[$i];
352 my $query = "insert into
354 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
356 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
357 my $sth = $dbh->prepare($query);
369 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
371 my $dbh = &C4Connect;
372 my $const = lc substr($constraint,0,1);
373 my $query = "SELECT * FROM borrowers,categories
374 WHERE (borrowernumber = ?)
375 AND (borrowers.categorycode = categories.categorycode)";
376 my $sth = $dbh->prepare($query);
377 $sth->execute($borrnum);
378 my $data = $sth->fetchrow_hashref;
380 my $fee = $data->{'reservefee'};
381 my $cntitems = @->$bibitems;
383 # check for items on issue
384 # first find biblioitem records
386 my $query1 = "SELECT * FROM biblio,biblioitems
387 WHERE (biblio.biblionumber = ?)
388 AND (biblio.biblionumber = biblioitems.biblionumber)";
389 my $sth1 = $dbh->prepare($query1);
390 $sth1->execute($biblionumber);
391 while (my $data1=$sth1->fetchrow_hashref) {
393 push @biblioitems,$data1;
397 while ($x < $cntitems) {
398 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
405 push @biblioitems,$data1;
409 push @biblioitems,$data1;
415 my $cntitemsfound = @biblioitems;
419 while ($x < $cntitemsfound) {
420 my $bitdata = $biblioitems[$x];
421 my $query2 = "SELECT * FROM items
422 WHERE biblioitemnumber = ?";
423 my $sth2 = $dbh->prepare($query2);
424 $sth2->execute($bitdata->{'biblioitemnumber'});
425 while (my $itdata=$sth2->fetchrow_hashref) {
426 my $query3 = "SELECT * FROM issues
428 AND returndate IS NULL";
430 my $sth3 = $dbh->prepare($query3);
431 $sth3->execute($itdata->{'itemnumber'});
432 if (my $isdata=$sth3->fetchrow_hashref) {
439 if ($allissued == 0) {
440 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
441 my $rsth = $dbh->prepare($rquery);
442 $rsth->execute($biblionumber);
443 if (my $rdata = $rsth->fetchrow_hashref) {
455 my ($env,$bornumber,$dbh)=@_;
457 my $query = "select * from accountlines
458 where (borrowernumber = '$bornumber')
459 order by accountno desc";
460 my $sth = $dbh->prepare($query);
462 if (my $accdata=$sth->fetchrow_hashref){
463 $nextaccntno = $accdata->{'accountno'} + 1;
466 return($nextaccntno);
470 #subroutine to update a reserve
471 my ($rank,$biblio,$borrower,$del,$branch)=@_;
473 my $query="Update reserves ";
475 $query.="set priority='$rank',branchcode='$branch' where
476 biblionumber=$biblio and borrowernumber=$borrower";
478 $query="Select * from reserves where biblionumber=$biblio and
479 borrowernumber=$borrower";
480 my $sth=$dbh->prepare($query);
482 my $data=$sth->fetchrow_hashref;
484 $query="Select * from reserves where biblionumber=$biblio and
485 priority > '$data->{'priority'}' and cancellationdate is NULL
487 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
488 $sth2->execute || die $sth2->errstr;
489 while (my $data=$sth2->fetchrow_hashref){
490 $data->{'priority'}--;
491 $query="Update reserves set priority=$data->{'priority'} where
492 biblionumber=$data->{'biblionumber'} and
493 borrowernumber=$data->{'borrowernumber'}";
494 my $sth3=$dbh->prepare($query);
495 $sth3->execute || die $sth3->errstr;
499 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
500 and borrowernumber=$borrower";
502 my $sth=$dbh->prepare($query);
508 sub getreservetitle {
509 my ($biblio,$bor,$date,$timestamp)=@_;
511 my $query="Select * from reserveconstraints,biblioitems where
512 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
513 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
514 = $bor and reserveconstraints.reservedate='$date' and
515 reserveconstraints.timestamp=$timestamp";
516 my $sth=$dbh->prepare($query);
518 my $data=$sth->fetchrow_hashref;
530 END { } # module clean-up code here (global destructor)