1 package C4::Reserves2; #assumes C4/Reserves2
9 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
11 # set the version for version checking
15 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
17 # make all your functions, whether exported or not;
22 my $query="SELECT *,reserves.branchcode,biblio.title AS btitle
23 FROM reserves,borrowers,biblio ";
25 $bib = $dbh->quote($bib);
27 $bor = $dbh->quote($bor);
28 $query .= " where reserves.biblionumber = $bib
29 and borrowers.borrowernumber = $bor
30 and reserves.borrowernumber = borrowers.borrowernumber
31 and biblio.biblionumber = $bib
32 and cancellationdate is NULL
33 and (found <> 'F' or found is NULL)";
35 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
36 and biblio.biblionumber = $bib
37 and reserves.biblionumber = $bib
38 and cancellationdate is NULL
39 and (found <> 'F' or found is NULL)";
42 $query .= " where borrowers.borrowernumber = $bor
43 and reserves.borrowernumber = borrowers.borrowernumber
44 and reserves.biblionumber = biblio.biblionumber
45 and cancellationdate is NULL and
46 (found <> 'F' or found is NULL)";
48 $query.=" order by priority";
49 my $sth=$dbh->prepare($query);
53 while (my $data=$sth->fetchrow_hashref){
66 my $qitem=$dbh->quote($item);
67 # get the biblionumber...
68 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
70 my ($biblio, $bibitem) = $sth->fetchrow_array;
74 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
75 my $priority = 10000000;
78 foreach my $res (@reserves) {
79 if ($res->{'itemnumber'} == $item) {
80 return ("Waiting", $res);
82 if ($res->{'priority'} < $priority) {
83 $priority = $res->{'priority'};
88 $highest->{'itemnumber'} = $item;
89 return ("Reserved", $highest);
96 my ($biblio, $item, $borr) = @_;
98 warn "In CancelReserve";
99 if (($item and $borr) and (not $biblio)) {
100 # removing a waiting reserve record....
101 $item = $dbh->quote($item);
102 $borr = $dbh->quote($borr);
103 # update the database...
104 my $query = "update reserves set cancellationdate = now(),
107 where itemnumber = $item
108 and borrowernumber = $borr";
109 my $sth = $dbh->prepare($query);
113 if (($biblio and $borr) and (not $item)) {
114 # removing a reserve record....
115 my $q_biblio = $dbh->quote($biblio);
116 $borr = $dbh->quote($borr);
117 # fix up the priorities on the other records....
118 my $query = "SELECT priority FROM reserves
119 WHERE biblionumber = $q_biblio
120 AND borrowernumber = $borr
121 AND cancellationdate is NULL
122 AND (found <> 'F' or found is NULL)";
123 my $sth=$dbh->prepare($query);
125 my ($priority) = $sth->fetchrow_array;
127 # update the database, removing the record...
128 my $query = "update reserves set cancellationdate = now(),
131 where biblionumber = $q_biblio
132 and borrowernumber = $borr
133 and cancellationdate is NULL
134 and (found <> 'F' or found is NULL)";
135 my $sth = $dbh->prepare($query);
138 # now fix the priority on the others....
139 fixpriority($priority, $biblio);
148 # removing a waiting reserve record....
149 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
150 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
151 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
152 # update the database...
153 my $query = "UPDATE reserves SET found = 'F',
155 WHERE biblionumber = $qbiblio
156 AND reservedate = $resdate
157 AND borrowernumber = $borr";
158 my $sth = $dbh->prepare($query);
162 # now fix the priority on the others....
163 fixpriority($res->{'priority'}, $biblio);
167 my ($priority, $biblio) = @_;
169 my ($count, $reserves) = FindReserves($biblio);
170 foreach my $rec (@$reserves) {
171 if ($rec->{'priority'} > $priority) {
172 my $newpr = $rec->{'priority'}; $newpr = $dbh->quote($newpr - 1);
173 my $nbib = $rec->{'biblionumber'}; $nbib = $dbh->quote($nbib);
174 my $nbor = $rec->{'borrowernumber'}; $nbor = $dbh->quote($nbor);
175 my $nresd = $rec->{'reservedate'}; $nresd = $dbh->quote($nresd);
176 my $query = "UPDATE reserves SET priority = $newpr
177 WHERE biblionumber = $nbib
178 AND borrowernumber = $nbor
179 AND reservedate = $nresd";
181 my $sth = $dbh->prepare($query);
192 my ($item, $borr) = @_;
194 $item = $dbh->quote($item);
195 $borr = $dbh->quote($borr);
196 # get priority and biblionumber....
197 my $query = "SELECT reserves.priority as priority,
198 reserves.biblionumber as biblionumber,
199 reserves.branchcode as branchcode
201 WHERE reserves.biblionumber = items.biblionumber
202 AND items.itemnumber = $item
203 AND reserves.borrowernumber = $borr
204 AND reserves.cancellationdate is NULL
205 AND (reserves.found <> 'F' or reserves.found is NULL)";
206 my $sth = $dbh->prepare($query);
208 my $data = $sth->fetchrow_hashref;
210 my $biblio = $data->{'biblionumber'};
211 my $q_biblio = $dbh->quote($biblio);
212 # update reserves record....
213 $query = "UPDATE reserves SET priority = 0, found = 'W', itemnumber = $item
214 WHERE borrowernumber = $borr AND biblionumber = $q_biblio";
215 $sth = $dbh->prepare($query);
219 # now fix up the remaining priorities....
220 fixpriority($data->{'priority'}, $biblio);
221 my $branchcode = $data->{'branchcode'};
228 $borr = $dbh->quote($borr);
230 my $query = "SELECT * FROM reserves
231 WHERE borrowernumber = $borr
232 AND reserves.found = 'W'
233 AND cancellationdate is NULL";
234 my $sth = $dbh->prepare($query);
237 if (my $data=$sth->fetchrow_hashref) {
238 @itemswaiting[$cnt] =$data;
242 return ($cnt,\@itemswaiting);
245 sub Findgroupreserve {
246 my ($bibitem,$biblio)=@_;
248 $bibitem=$dbh->quote($bibitem);
249 my $query = "SELECT reserves.biblionumber AS biblionumber,
250 reserves.borrowernumber AS borrowernumber,
251 reserves.reservedate AS reservedate,
252 reserves.branchcode AS branchcode,
253 reserves.cancellationdate AS cancellationdate,
254 reserves.found AS found,
255 reserves.reservenotes AS reservenotes,
256 reserves.priority AS priority,
257 reserves.timestamp AS timestamp,
258 reserveconstraints.biblioitemnumber AS biblioitemnumber,
259 reserves.itemnumber AS itemnumber
260 FROM reserves LEFT JOIN reserveconstraints
261 ON reserves.biblionumber = reserveconstraints.biblionumber
262 WHERE reserves.biblionumber = $biblio
263 AND ( ( reserveconstraints.biblioitemnumber = $bibitem
264 AND reserves.borrowernumber = reserveconstraints.borrowernumber
265 AND reserves.reservedate =reserveconstraints.reservedate )
266 OR reserves.constrainttype='a' )
267 AND reserves.cancellationdate is NULL
268 AND (reserves.found <> 'F' or reserves.found is NULL)";
269 my $sth=$dbh->prepare($query);
273 while (my $data=$sth->fetchrow_hashref){
284 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
285 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
286 my $dbh = &C4Connect;
287 my $const = lc substr($constraint,0,1);
288 my @datearr = localtime(time);
289 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
291 # updates take place here
294 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
295 my $updquery = "insert into accountlines
296 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
298 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
299 my $usth = $dbh->prepare($updquery);
304 my $query="insert into reserves
305 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
307 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
308 my $sth = $dbh->prepare($query);
312 if (($const eq "o") || ($const eq "e")) {
313 my $numitems = @$bibitems;
315 while ($i < $numitems) {
316 my $biblioitem = @$bibitems[$i];
317 my $query = "insert into
319 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
321 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
322 my $sth = $dbh->prepare($query);
334 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
336 my $dbh = &C4Connect;
337 my $const = lc substr($constraint,0,1);
338 my $query = "select * from borrowers,categories
339 where (borrowernumber = '$borrnum')
340 and (borrowers.categorycode = categories.categorycode)";
341 my $sth = $dbh->prepare($query);
343 my $data = $sth->fetchrow_hashref;
345 my $fee = $data->{'reservefee'};
346 my $cntitems = @->$bibitems;
348 # check for items on issue
349 # first find biblioitem records
351 my $query1 = "select * from biblio,biblioitems
352 where (biblio.biblionumber = '$biblionumber')
353 and (biblio.biblionumber = biblioitems.biblionumber)";
354 my $sth1 = $dbh->prepare($query1);
356 while (my $data1=$sth1->fetchrow_hashref) {
358 push @biblioitems,$data1;
362 while ($x < $cntitems) {
363 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
370 push @biblioitems,$data1;
374 push @biblioitems,$data1;
380 my $cntitemsfound = @biblioitems;
384 while ($x < $cntitemsfound) {
385 my $bitdata = $biblioitems[$x];
386 my $query2 = "select * from items
387 where biblioitemnumber = '$bitdata->{'biblioitemnumber'}'";
388 my $sth2 = $dbh->prepare($query2);
390 while (my $itdata=$sth2->fetchrow_hashref) {
391 my $query3 = "select * from issues
392 where itemnumber = '$itdata->{'itemnumber'}' and
395 my $sth3 = $dbh->prepare($query3);
397 if (my $isdata=$sth3->fetchrow_hashref) {
404 if ($allissued == 0) {
405 my $rquery = "select * from reserves
406 where biblionumber = '$biblionumber'";
407 my $rsth = $dbh->prepare($rquery);
409 if (my $rdata = $rsth->fetchrow_hashref) {
421 my ($env,$bornumber,$dbh)=@_;
423 my $query = "select * from accountlines
424 where (borrowernumber = '$bornumber')
425 order by accountno desc";
426 my $sth = $dbh->prepare($query);
428 if (my $accdata=$sth->fetchrow_hashref){
429 $nextaccntno = $accdata->{'accountno'} + 1;
432 return($nextaccntno);
436 #subroutine to update a reserve
437 my ($rank,$biblio,$borrower,$del,$branch)=@_;
439 my $query="Update reserves ";
441 $query.="set priority='$rank',branchcode='$branch' where
442 biblionumber=$biblio and borrowernumber=$borrower";
444 $query="Select * from reserves where biblionumber=$biblio and
445 borrowernumber=$borrower";
446 my $sth=$dbh->prepare($query);
448 my $data=$sth->fetchrow_hashref;
450 $query="Select * from reserves where biblionumber=$biblio and
451 priority > '$data->{'priority'}' and cancellationdate is NULL
453 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
454 $sth2->execute || die $sth2->errstr;
455 while (my $data=$sth2->fetchrow_hashref){
456 $data->{'priority'}--;
457 $query="Update reserves set priority=$data->{'priority'} where
458 biblionumber=$data->{'biblionumber'} and
459 borrowernumber=$data->{'borrowernumber'}";
460 my $sth3=$dbh->prepare($query);
461 $sth3->execute || die $sth3->errstr;
465 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
466 and borrowernumber=$borrower";
468 my $sth=$dbh->prepare($query);
474 sub getreservetitle {
475 my ($biblio,$bor,$date,$timestamp)=@_;
477 my $query="Select * from reserveconstraints,biblioitems where
478 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
479 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
480 = $bor and reserveconstraints.reservedate='$date' and
481 reserveconstraints.timestamp=$timestamp";
482 my $sth=$dbh->prepare($query);
484 my $data=$sth->fetchrow_hashref;
496 END { } # module clean-up code here (global destructor)