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 &CancelReserve &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 from items where itemnumber=$qitem");
105 my ($biblio) = $sth->fetchrow_array;
108 # get the reserves...
109 my ($count, $reserves) = FindReserves($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 if (($item and $borr) and (not $biblio)) {
134 # removing a waiting reserve record....
135 $item = $dbh->quote($item);
136 $borr = $dbh->quote($borr);
137 # update the database...
138 my $query = "update reserves set cancellationdate = now(),
141 where itemnumber = $item
142 and borrowernumber = $borr";
143 my $sth = $dbh->prepare($query);
147 if (($biblio and $borr) and (not $item)) {
148 # removing a reserve record....
149 $biblio = $dbh->quote($biblio);
150 $borr = $dbh->quote($borr);
151 # fix up the priorities on the other records....
152 my $query = "select priority from reserves
153 where biblionumber = $biblio
154 and borrowernumber = $borr
155 and cancellationdate is NULL
156 and (found <> 'F' or found is NULL)";
157 my $sth=$dbh->prepare($query);
159 my ($priority) = $sth->fetchrow_array;
161 # update the database, removing the record...
162 my $query = "update reserves set cancellationdate = now(),
165 where biblionumber = $biblio
166 and borrowernumber = $borr
167 and cancellationdate is NULL
168 and (found <> 'F' or found is NULL)";
169 my $sth = $dbh->prepare($query);
172 # now fix the priority on the others....
173 my ($count, $reserves) = FindReserves($biblio);
174 foreach my $rec (@$reserves) {
175 if ($rec->{'priority'} > $priority) {
176 my $newpr = $rec->{'priority'};
177 $newpr = $dbh->quote($newpr - 1);
178 my $query = "update reserves set priority = $newpr
179 where biblionumber = $rec->{'biblionumber'}
180 and borrowernumber = $rec->{'borrowernumber'}
181 and cancellationdate is NULL
182 and (found <> 'F' or found is NULL)";
183 my $sth = $dbh->prepare($query);
193 my ($item, $borr) = @_;
195 $item = $dbh->quote($item);
196 $borr = $dbh->quote($borr);
197 # get priority and biblionumber....
198 my $query = "SELECT reserves.priority as priority,
199 reserves.biblionumber as biblionumber,
200 reserves.branchcode as branchcode
202 WHERE reserves.biblionumber = items.biblionumber
203 AND items.itemnumber = $item
204 AND reserves.borrowernumber = $borr
205 AND reserves.cancellationdate is NULL
206 AND (reserves.found <> 'F' or reserves.found is NULL)";
207 my $sth = $dbh->prepare($query);
209 my $data = $sth->fetchrow_hashref;
211 my $biblio = $data->{'biblionumber'};
212 $biblio = $dbh->quote($biblio);
213 # update reserves record....
214 $query = "update reserves set priority = 0, found = 'W', itemnumber = $item
215 where borrowernumber = $borr and biblionumber = $biblio";
216 $sth = $dbh->prepare($query);
219 # now fix up the remaining priorities....
220 $query = "select priority, borrowernumber from reserves where biblionumber = $biblio
221 and cancellationdate is NULL
223 and priority <> 0 and priority is not NULL";
224 $sth = $dbh->prepare($query);
226 my $branchcode = $data->{'branchcode'};
227 my $priority = $data->{'priority'};
228 while (my $data = $sth->fetchrow_hashref) {
229 if ($data->{'priority'} > $priority) {
230 my $uquery = "update reserves set priority = priority - 1
231 where biblionumber = $biblio
232 and borrowernumber = $borr
233 and cancellation is NULL";
234 my $usth->$dbh->prepare($query);
244 sub Findgroupreserve {
245 my ($bibitem,$biblio)=@_;
247 $bibitem=$dbh->quote($bibitem);
248 my $query="Select * from reserves
249 left join reserveconstraints on
250 reserves.biblionumber=reserveconstraints.biblionumber
252 reserves.biblionumber=$biblio and
253 ((reserveconstraints.biblioitemnumber=$bibitem
254 and reserves.borrowernumber=reserveconstraints.borrowernumber
255 and reserves.reservedate=reserveconstraints.reservedate)
256 or reserves.constrainttype='a')
257 and reserves.cancellationdate is NULL
258 and (reserves.found <> 'F' or reserves.found is NULL)";
260 my $sth=$dbh->prepare($query);
264 while (my $data=$sth->fetchrow_hashref){
275 ($env,$branch,$borrnum,$biblionumber,$constraint,$bibitems,$priority,$notes,$title)= @_;
276 my $fee=CalcReserveFee($env,$borrnum,$biblionumber,$constraint,$bibitems);
277 my $dbh = &C4Connect;
278 my $const = lc substr($constraint,0,1);
279 my @datearr = localtime(time);
280 my $resdate =(1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
282 # updates take place here
285 my $nextacctno = &getnextacctno($env,$borrnum,$dbh);
286 my $updquery = "insert into accountlines
287 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
289 ($borrnum,$nextacctno,now(),$fee,'Reserve Charge - $title','Res',$fee)";
290 my $usth = $dbh->prepare($updquery);
295 my $query="insert into reserves
296 (borrowernumber,biblionumber,reservedate,branchcode,constrainttype,priority,reservenotes)
298 ('$borrnum','$biblionumber','$resdate','$branch','$const','$priority','$notes')";
299 my $sth = $dbh->prepare($query);
303 if (($const eq "o") || ($const eq "e")) {
304 my $numitems = @$bibitems;
306 while ($i < $numitems) {
307 my $biblioitem = @$bibitems[$i];
308 my $query = "insert into
310 (borrowernumber,biblionumber,reservedate,biblioitemnumber)
312 ('$borrnum','$biblionumber','$resdate','$biblioitem')";
313 my $sth = $dbh->prepare($query);
325 my ($env,$borrnum,$biblionumber,$constraint,$bibitems) = @_;
327 my $dbh = &C4Connect;
328 my $const = lc substr($constraint,0,1);
329 my $query = "select * from borrowers,categories
330 where (borrowernumber = '$borrnum')
331 and (borrowers.categorycode = categories.categorycode)";
332 my $sth = $dbh->prepare($query);
334 my $data = $sth->fetchrow_hashref;
336 my $fee = $data->{'reservefee'};
337 my $cntitems = @->$bibitems;
339 # check for items on issue
340 # first find biblioitem records
342 my $query1 = "select * from biblio,biblioitems
343 where (biblio.biblionumber = '$biblionumber')
344 and (biblio.biblionumber = biblioitems.biblionumber)";
345 my $sth1 = $dbh->prepare($query1);
347 while (my $data1=$sth1->fetchrow_hashref) {
349 push @biblioitems,$data1;
353 while ($x < $cntitems) {
354 if (@$bibitems->{'biblioitemnumber'} == $data->{'biblioitemnumber'}) {
361 push @biblioitems,$data1;
365 push @biblioitems,$data1;
371 my $cntitemsfound = @biblioitems;
375 while ($x < $cntitemsfound) {
376 my $bitdata = @biblioitems[$x];
377 my $query2 = "select * from items
378 where biblioitemnumber = '$bitdata->{'biblioitemnumber'}'";
379 my $sth2 = $dbh->prepare($query2);
381 while (my $itdata=$sth2->fetchrow_hashref) {
382 my $query3 = "select * from issues
383 where itemnumber = '$itdata->{'itemnumber'}' and
386 my $sth3 = $dbh->prepare($query3);
388 if (my $isdata=$sth3->fetchrow_hashref) {
395 if ($allissued == 0) {
396 my $rquery = "select * from reserves
397 where biblionumber = '$biblionumber'";
398 my $rsth = $dbh->prepare($rquery);
400 if (my $rdata = $rsth->fetchrow_hashref) {
412 my ($env,$bornumber,$dbh)=@_;
414 my $query = "select * from accountlines
415 where (borrowernumber = '$bornumber')
416 order by accountno desc";
417 my $sth = $dbh->prepare($query);
419 if (my $accdata=$sth->fetchrow_hashref){
420 $nextaccntno = $accdata->{'accountno'} + 1;
423 return($nextaccntno);
427 #subroutine to update a reserve
428 my ($rank,$biblio,$borrower,$del,$branch)=@_;
430 my $query="Update reserves ";
432 $query.="set priority='$rank',branchcode='$branch' where
433 biblionumber=$biblio and borrowernumber=$borrower";
435 $query="Select * from reserves where biblionumber=$biblio and
436 borrowernumber=$borrower";
437 my $sth=$dbh->prepare($query);
439 my $data=$sth->fetchrow_hashref;
441 $query="Select * from reserves where biblionumber=$biblio and
442 priority > '$data->{'priority'}' and cancellationdate is NULL
444 my $sth2=$dbh->prepare($query) || die $dbh->errstr;
445 $sth2->execute || die $sth2->errstr;
446 while (my $data=$sth2->fetchrow_hashref){
447 $data->{'priority'}--;
448 $query="Update reserves set priority=$data->{'priority'} where
449 biblionumber=$data->{'biblionumber'} and
450 borrowernumber=$data->{'borrowernumber'}";
451 my $sth3=$dbh->prepare($query);
452 $sth3->execute || die $sth3->errstr;
456 $query="update reserves set cancellationdate=now() where biblionumber=$biblio
457 and borrowernumber=$borrower";
459 my $sth=$dbh->prepare($query);
465 sub getreservetitle {
466 my ($biblio,$bor,$date,$timestamp)=@_;
468 my $query="Select * from reserveconstraints,biblioitems where
469 reserveconstraints.biblioitemnumber=biblioitems.biblioitemnumber
470 and reserveconstraints.biblionumber=$biblio and reserveconstraints.borrowernumber
471 = $bor and reserveconstraints.reservedate='$date' and
472 reserveconstraints.timestamp=$timestamp";
473 my $sth=$dbh->prepare($query);
475 my $data=$sth->fetchrow_hashref;
487 END { } # module clean-up code here (global destructor)