1 package C4::Reserves2; #asummes C4/Reserves2
3 #requires DBI.pm to be installed
12 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
14 # set the version for version checking
18 @EXPORT = qw(&FindReserves &CheckReserves &CheckWaiting &CancelReserve &FillReserve &ReserveWaiting &CreateReserve &updatereserves &getreservetitle &Findgroupreserve);
19 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
21 # your exported package globals go here,
22 # as well as any optionally exported functions
24 @EXPORT_OK = qw($Var1 %Hashit);
27 # non-exported package globals go here
28 use vars qw(@more $stuff);
30 # initalize package globals, first exported ones
35 # then the others (which are still accessible as $Some::Module::stuff)
39 # all file-scoped lexicals must be created before
40 # the functions below that use them.
42 # file-private lexicals go here
46 # here's a file-private function as a closure,
47 # callable as &$priv_func; it cannot be prototyped.
52 # make all your functions, whether exported or not;
57 my $query = "SELECT *,reserves.branchcode,biblio.title AS btitle
58 FROM reserves,borrowers,biblio ";
60 $bib = $dbh->quote($bib);
62 $bor = $dbh->quote($bor);
63 $query .= " where reserves.biblionumber = $bib
64 and borrowers.borrowernumber = $bor
65 and reserves.borrowernumber = borrowers.borrowernumber
66 and biblio.biblionumber = $bib
67 and cancellationdate is NULL
68 and (found <> 'F' or found is NULL)";
70 $query .= " where reserves.borrowernumber = borrowers.borrowernumber
71 and biblio.biblionumber = $bib
72 and reserves.biblionumber = $bib
73 and cancellationdate is NULL
74 and (found <> 'F' or found is NULL)";
77 $query .= " where borrowers.borrowernumber = $bor
78 and reserves.borrowernumber = borrowers.borrowernumber
79 and reserves.biblionumber = biblio.biblionumber
80 and cancellationdate is NULL and
81 (found <> 'F' or found is NULL)";
83 $query.=" order by priority";
84 my $sth=$dbh->prepare($query);
88 while (my $data=$sth->fetchrow_hashref){
101 my $qitem=$dbh->quote($item);
102 # get the biblionumber...
103 my $sth=$dbh->prepare("select biblionumber, biblioitemnumber from items where itemnumber=$qitem");
105 my ($biblio, $bibitem) = $sth->fetchrow_array;
108 # get the reserves...
109 my ($count, @reserves) = Findgroupreserve($bibitem, $biblio);
110 my $priority = 10000000;
113 foreach my $res (@reserves) {
114 if ($res->{'itemnumber'} == $item) {
115 return ("Waiting", $res);
117 if ($res->{'priority'} < $priority) {
118 $priority = $res->{'priority'};
123 $highest->{'itemnumber'} = $item;
124 return ("Reserved", $highest);
131 my ($biblio, $item, $borr) = @_;
133 warn "In CancelReserve";
134 if (($item and $borr) and (not $biblio)) {
135 # removing a waiting reserve record....
136 $item = $dbh->quote($item);
137 $borr = $dbh->quote($borr);
138 # update the database...
139 my $query = "update reserves set cancellationdate = now(),
142 where itemnumber = $item
143 and borrowernumber = $borr";
144 my $sth = $dbh->prepare($query);
148 if (($biblio and $borr) and (not $item)) {
149 # removing a reserve record....
150 my $q_biblio = $dbh->quote($biblio);
151 $borr = $dbh->quote($borr);
152 # fix up the priorities on the other records....
153 my $query = "SELECT priority FROM reserves
154 WHERE biblionumber = $q_biblio
155 AND borrowernumber = $borr
156 AND cancellationdate is NULL
157 AND (found <> 'F' or found is NULL)";
158 my $sth=$dbh->prepare($query);
160 my ($priority) = $sth->fetchrow_array;
162 # update the database, removing the record...
163 my $query = "update reserves set cancellationdate = now(),
166 where biblionumber = $q_biblio
167 and borrowernumber = $borr
168 and cancellationdate is NULL
169 and (found <> 'F' or found is NULL)";
170 my $sth = $dbh->prepare($query);
173 # now fix the priority on the others....
174 fixpriority($priority, $biblio);
183 # removing a waiting reserve record....
184 my $biblio = $res->{'biblionumber'}; my $qbiblio = $dbh->quote($biblio);
185 my $borr = $res->{'borrowernumber'}; $borr = $dbh->quote($borr);
186 my $resdate = $res->{'reservedate'}; $resdate = $dbh->quote($resdate);
187 # update the database...
188 my $query = "UPDATE reserves SET found = 'F',
190 WHERE biblionumber = $qbiblio
191 AND reservedate = $resdate
192 AND borrowernumber = $borr";
193 my $sth = $dbh->prepare($query);
197 # now fix the priority on the others....
198 fixpriority($res->{'priority'}, $biblio);
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 = '$borrnum')
375 and (borrowers.categorycode = categories.categorycode)";
376 my $sth = $dbh->prepare($query);
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 = '$biblionumber')
388 and (biblio.biblionumber = biblioitems.biblionumber)";
389 my $sth1 = $dbh->prepare($query1);
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 = '$bitdata->{'biblioitemnumber'}'";
423 my $sth2 = $dbh->prepare($query2);
425 while (my $itdata=$sth2->fetchrow_hashref) {
426 my $query3 = "select * from issues
427 where itemnumber = '$itdata->{'itemnumber'}' and
430 my $sth3 = $dbh->prepare($query3);
432 if (my $isdata=$sth3->fetchrow_hashref) {
439 if ($allissued == 0) {
440 my $rquery = "select * from reserves
441 where biblionumber = '$biblionumber'";
442 my $rsth = $dbh->prepare($rquery);
444 if (my $rdata = $rsth->fetchrow_hashref) {
456 my ($env,$bornumber,$dbh)=@_;
458 my $query = "select * from accountlines
459 where (borrowernumber = '$bornumber')
460 order by accountno desc";
461 my $sth = $dbh->prepare($query);
463 if (my $accdata=$sth->fetchrow_hashref){
464 $nextaccntno = $accdata->{'accountno'} + 1;
467 return($nextaccntno);
471 #subroutine to update a reserve
472 my ($rank,$biblio,$borrower,$del,$branch)=@_;
474 my $query="Update reserves ";
476 $query.="set priority='$rank',branchcode='$branch' where
477 biblionumber=$biblio and borrowernumber=$borrower";
479 $query="Select * from reserves where biblionumber=$biblio and
480 borrowernumber=$borrower";
481 my $sth=$dbh->prepare($query);
483 my $data=$sth->fetchrow_hashref;
485 $query="Select * from reserves where biblionumber=$biblio and
486 priority > '$data->{'priority'}' and cancellationdate is NULL
488 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
489 $sth2->execute || die $sth2->errstr;
490 while (my $data=$sth2->fetchrow_hashref){
491 $data->{'priority'}--;
492 $query="Update reserves set priority=$data->{'priority'} where
493 biblionumber=$data->{'biblionumber'} and
494 borrowernumber=$data->{'borrowernumber'}";
495 my $sth3=$dbh->prepare($query);
496 $sth3->execute || die $sth3->errstr;
500 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
501 and borrowernumber=$borrower";
503 my $sth=$dbh->prepare($query);
509 sub getreservetitle {
510 my ($biblio,$bor,$date,$timestamp)=@_;
512 my $query="Select * from reserveconstraints,biblioitems where
513 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
514 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
515 = $bor and reserveconstraints.reservedate='$date' and
516 reserveconstraints.timestamp=$timestamp";
517 my $sth=$dbh->prepare($query);
519 my $data=$sth->fetchrow_hashref;
531 END { } # module clean-up code here (global destructor)