1 package C4::Reserves2; #assumes C4/Reserves2
10 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
12 # set the version for version checking
16 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
18 # make all your functions, whether exported or not;
23 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
24 FROM reserves,borrowers,biblio ";
26 $bib = $dbh->quote($bib);
28 $bor = $dbh->quote($bor);
29 $query .= " where reserves.biblionumber = $bib
30 and borrowers.borrowernumber = $bor
31 and reserves.borrowernumber = borrowers.borrowernumber
32 and biblio.biblionumber = $bib
33 and cancellationdate is NULL
34 and (found <> 'F' or found is NULL)";
36 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
37 and biblio.biblionumber = $bib
38 and reserves.biblionumber = $bib
39 and cancellationdate is NULL
40 and (found <> 'F' or found is NULL)";
43 $query .= " where borrowers.borrowernumber = $bor
44 and reserves.borrowernumber = borrowers.borrowernumber
45 and reserves.biblionumber = biblio.biblionumber
46 and cancellationdate is NULL and
47 (found <> 'F' or found is NULL)";
49 $query.=" order by priority";
51 my $sth=$dbh->prepare($query);
55 while (my $data=$sth->fetchrow_hashref){
56 if ($data->{'constrainttype'} eq 'o') {
57 my $conquery = "SELECT biblioitemnumber FROM reserveconstraints
58 WHERE biblionumber = ?
59 AND borrowernumber = ?
61 my $csth=$dbh->prepare($conquery);
62 my $bibn = $data->{'biblionumber'};
63 my $born = $data->{'borrowernumber'};
64 my $resd = $data->{'reservedate'};
65 $csth->execute($bibn, $born, $resd);
66 my ($bibitemno) = $csth->fetchrow_array;
68 my $bdata = C4::Search::bibitemdata($bibitemno);
69 foreach my $key (keys %$bdata) {
70 $data->{$key} = $bdata->{$key};
84 warn "In CheckReserves";
86 my $qitem=$dbh->quote($item);
87 # get the biblionumber...
88 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
90 my ($biblio, $bibitem) = $sth->fetchrow_array;
94 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
95 my $priority = 10000000;
98 foreach my $res (@reserves) {
99 if ($res->{'itemnumber'} == $item) {
100 return ("Waiting", $res);
102 if ($res->{'priority'} != 0 && $res->{'priority'} < $priority) {
103 $priority = $res->{'priority'};
108 warn "highest = $highest";
109 $highest->{'itemnumber'} = $item;
110 foreach my $key (keys %$highest) {
111 warn "$key : $highest->{$key}\n";
114 return ("Reserved", $highest);
121 my ($biblio, $item, $borr) = @_;
123 #warn "In CancelReserve";
124 if (($item and $borr) and (not $biblio)) {
125 # removing a waiting reserve record....
126 $item = $dbh->quote($item);
127 $borr = $dbh->quote($borr);
128 # update the database...
129 my $query = "update reserves set cancellationdate = now(),
132 where itemnumber = $item
133 and borrowernumber = $borr";
134 my $sth = $dbh->prepare($query);
138 if (($biblio and $borr) and (not $item)) {
139 # removing a reserve record....
140 my $q_biblio = $dbh->quote($biblio);
141 $borr = $dbh->quote($borr);
142 # fix up the priorities on the other records....
143 my $query = "SELECT priority FROM reserves
144 WHERE biblionumber = $q_biblio
145 AND borrowernumber = $borr
146 AND cancellationdate is NULL
147 AND (found <> 'F' or found is NULL)";
148 my $sth=$dbh->prepare($query);
150 my ($priority) = $sth->fetchrow_array;
152 # update the database, removing the record...
153 my $query = "update reserves set cancellationdate = now(),
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);
163 # now fix the priority on the others....
164 fixpriority($priority, $biblio);
173 # removing a waiting reserve record....
174 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
175 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
176 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
177 # update the database...
178 my $query = "UPDATE reserves SET found = 'F',
180 WHERE biblionumber = $qbiblio
181 AND reservedate = $resdate
182 AND borrowernumber = $borr";
183 my $sth = $dbh->prepare($query);
187 # now fix the priority on the others....
188 fixpriority($res->{'priority'}, $biblio);
192 my ($priority, $biblio) = @_;
194 my ($count, $reserves) = FindReserves($biblio);
195 foreach my $rec (@$reserves) {
196 if ($rec->{'priority'} > $priority) {
197 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
198 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
199 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
200 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
201 my $query = "UPDATE reserves SET priority = $newpr
202 WHERE biblionumber = $nbib
203 AND borrowernumber = $nbor
204 AND reservedate = $nresd";
206 my $sth = $dbh->prepare($query);
217 my ($item, $borr) = @_;
219 $item = $dbh->quote($item);
220 $borr = $dbh->quote($borr);
221 # get priority and biblionumber....
222 my $query = "SELECT reserves.priority as priority,
223 reserves.biblionumber as biblionumber,
224 reserves.branchcode as branchcode
226 WHERE reserves.biblionumber = items.biblionumber
227 AND items.itemnumber = $item
228 AND reserves.borrowernumber = $borr
229 AND reserves.cancellationdate is NULL
230 AND (reserves.found <> 'F' or reserves.found is NULL)";
231 my $sth = $dbh->prepare($query);
233 my $data = $sth->fetchrow_hashref;
235 my $biblio = $data->{'biblionumber'};
236 my $q_biblio = $dbh->quote($biblio);
237 # update reserves record....
238 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
239 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
240 $sth = $dbh->prepare($query);
244 # now fix up the remaining priorities....
245 fixpriority($data->{'priority'}, $biblio);
246 my $branchcode = $data->{'branchcode'};
253 $borr = $dbh->quote($borr);
255 my $query = "SELECT * FROM reserves
256 WHERE borrowernumber = $borr
257 AND reserves.found = 'W'
258 AND cancellationdate is NULL";
259 my $sth = $dbh->prepare($query);
262 if (my $data=$sth->fetchrow_hashref) {
263 @itemswaiting[$cnt] =$data;
267 return ($cnt,\@itemswaiting);
270 sub Findgroupreserve {
271 my ($bibitem,$biblio)=@_;
273 $bibitem=$dbh->quote($bibitem);
274 my $query = "SELECT reserves.biblionumber AS biblionumber,
275 reserves.borrowernumber AS borrowernumber,
276 reserves.reservedate AS reservedate,
277 reserves.branchcode AS branchcode,
278 reserves.cancellationdate AS cancellationdate,
279 reserves.found AS found,
280 reserves.reservenotes AS reservenotes,
281 reserves.priority AS priority,
282 reserves.timestamp AS timestamp,
283 reserveconstraints.biblioitemnumber AS biblioitemnumber,
284 reserves.itemnumber AS itemnumber
285 FROM reserves LEFT JOIN reserveconstraints
286 ON reserves.biblionumber = reserveconstraints.biblionumber
287 WHERE reserves.biblionumber = $biblio
288 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
289 AND reserves.borrowernumber = reserveconstraints.borrowernumber
290 AND reserves.reservedate =reserveconstraints.reservedate )
291 OR reserves.constrainttype='a' )
292 AND reserves.cancellationdate is NULL
293 AND (reserves.found <> 'F' or reserves.found is NULL)";
294 my $sth=$dbh->prepare($query);
298 while (my $data=$sth->fetchrow_hashref){
309 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
310 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
311 my $dbh = &C4Connect;
312 my $const = lc substr($constraint,0,1);
313 my @datearr = localtime(time);
314 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
316 # updates take place here
319 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
320 my $updquery = "insert into accountlines
321 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
323 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
324 my $usth = $dbh->prepare($updquery);
329 my $query="insert into reserves
330 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
332 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
333 my $sth = $dbh->prepare($query);
337 if (($const eq "o") || ($const eq "e")) {
338 my $numitems = @$bibitems;
340 while ($i < $numitems) {
341 my $biblioitem = @$bibitems[$i];
342 my $query = "insert into
344 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
346 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
347 my $sth = $dbh->prepare($query);
359 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
361 my $dbh = &C4Connect;
362 my $const = lc substr($constraint,0,1);
363 my $query = "SELECT * FROM borrowers,categories
364 WHERE (borrowernumber = ?)
365 AND (borrowers.categorycode = categories.categorycode)";
366 my $sth = $dbh->prepare($query);
367 $sth->execute($borrnum);
368 my $data = $sth->fetchrow_hashref;
370 my $fee = $data->{'reservefee'};
371 my $cntitems = @->$bibitems;
373 # check for items on issue
374 # first find biblioitem records
376 my $query1 = "SELECT * FROM biblio,biblioitems
377 WHERE (biblio.biblionumber = ?)
378 AND (biblio.biblionumber = biblioitems.biblionumber)";
379 my $sth1 = $dbh->prepare($query1);
380 $sth1->execute($biblionumber);
381 while (my $data1=$sth1->fetchrow_hashref) {
383 push @biblioitems,$data1;
387 while ($x < $cntitems) {
388 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
395 push @biblioitems,$data1;
399 push @biblioitems,$data1;
405 my $cntitemsfound = @biblioitems;
409 while ($x < $cntitemsfound) {
410 my $bitdata = $biblioitems[$x];
411 my $query2 = "SELECT * FROM items
412 WHERE biblioitemnumber = ?";
413 my $sth2 = $dbh->prepare($query2);
414 $sth2->execute($bitdata->{'biblioitemnumber'});
415 while (my $itdata=$sth2->fetchrow_hashref) {
416 my $query3 = "SELECT * FROM issues
418 AND returndate IS NULL";
420 my $sth3 = $dbh->prepare($query3);
421 $sth3->execute($itdata->{'itemnumber'});
422 if (my $isdata=$sth3->fetchrow_hashref) {
429 if ($allissued == 0) {
430 my $rquery = "SELECT * FROM reserves WHERE biblionumber = ?";
431 my $rsth = $dbh->prepare($rquery);
432 $rsth->execute($biblionumber);
433 if (my $rdata = $rsth->fetchrow_hashref) {
445 my ($env,$bornumber,$dbh)=@_;
447 my $query = "select * from accountlines
448 where (borrowernumber = '$bornumber')
449 order by accountno desc";
450 my $sth = $dbh->prepare($query);
452 if (my $accdata=$sth->fetchrow_hashref){
453 $nextaccntno = $accdata->{'accountno'} + 1;
456 return($nextaccntno);
460 #subroutine to update a reserve
461 my ($rank,$biblio,$borrower,$del,$branch)=@_;
463 my $query="Update reserves ";
465 $query.="set priority='$rank',branchcode='$branch' where
466 biblionumber=$biblio and borrowernumber=$borrower";
468 $query="Select * from reserves where biblionumber=$biblio and
469 borrowernumber=$borrower";
470 my $sth=$dbh->prepare($query);
472 my $data=$sth->fetchrow_hashref;
474 $query="Select * from reserves where biblionumber=$biblio and
475 priority > '$data->{'priority'}' and cancellationdate is NULL
477 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
478 $sth2->execute || die $sth2->errstr;
479 while (my $data=$sth2->fetchrow_hashref){
480 $data->{'priority'}--;
481 $query="Update reserves set priority=$data->{'priority'} where
482 biblionumber=$data->{'biblionumber'} and
483 borrowernumber=$data->{'borrowernumber'}";
484 my $sth3=$dbh->prepare($query);
485 $sth3->execute || die $sth3->errstr;
489 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
490 and borrowernumber=$borrower";
492 my $sth=$dbh->prepare($query);
498 sub getreservetitle {
499 my ($biblio,$bor,$date,$timestamp)=@_;
501 my $query="Select * from reserveconstraints,biblioitems where
502 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
503 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
504 = $bor and reserveconstraints.reservedate='$date' and
505 reserveconstraints.timestamp=$timestamp";
506 my $sth=$dbh->prepare($query);
508 my $data=$sth->fetchrow_hashref;
520 END { } # module clean-up code here (global destructor)