1 package C4::Circulation::Circ2;
3 #package to deal with Returns
4 #written 3/11/99 by olwen@katipo.co.nz
12 #use C4::InterfaceCDK;
13 #use C4::Circulation::Main;
15 #use C4::Circulation::Renewals;
22 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
24 # set the version for version checking
28 @EXPORT = qw(&getbranches &getprinters &getpatroninformation ¤tissues &getissues &getiteminformation &findborrower &issuebook &returnbook &find_reserves &transferbook &decode);
29 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
31 # your exported package globals go here,
32 # as well as any optionally exported functions
34 @EXPORT_OK = qw($Var1 %Hashit);
37 # non-exported package globals go here
38 #use vars qw(@more $stuff);
40 # initalize package globals, first exported ones
45 # then the others (which are still accessible as $Some::Module::stuff)
49 # all file-scoped lexicals must be created before
50 # the functions below that use them.
52 # file-private lexicals go here
56 # here's a file-private function as a closure,
57 # callable as &$priv_func; it cannot be prototyped.
62 # make all your functions, whether exported or not;
66 # returns a reference to a hash of references to branches...
69 my $sth=$dbh->prepare("select * from branches");
71 while (my $branch=$sth->fetchrow_hashref) {
72 my $tmp = $branch->{'branchcode'}; my $brc = $dbh->quote($tmp);
73 my $query = "select categorycode from branchrelations where branchcode = $brc";
74 my $nsth = $dbh->prepare($query);
76 while (my ($cat) = $nsth->fetchrow_array) {
80 $branches{$branch->{'branchcode'}}=$branch;
91 my $sth=$dbh->prepare("select * from printers");
93 while (my $printer=$sth->fetchrow_hashref) {
94 $printers{$printer->{'printqueue'}}=$printer;
102 sub getpatroninformation {
104 my ($env, $borrowernumber,$cardnumber) = @_;
108 if ($borrowernumber) {
109 $query = "select * from borrowers where borrowernumber=$borrowernumber";
110 } elsif ($cardnumber) {
111 $query = "select * from borrowers where cardnumber=$cardnumber";
113 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
116 $env->{'mess'} = $query;
117 $sth = $dbh->prepare($query);
119 my $borrower = $sth->fetchrow_hashref;
120 my $flags = patronflags($env, $borrower, $dbh);
123 $borrower->{'flags'}=$flags;
124 return($borrower, $flags);
129 my $seq = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-';
130 my @s = map { index($seq,$_); } split(//,$encoded);
145 my $n = (($s[0] << 6 | $s[1]) << 6 | $s[2]) << 6 | $s[3];
146 $r .=chr(($n >> 16) ^ 67) .
147 chr(($n >> 8 & 255) ^ 67) .
148 chr(($n & 255) ^ 67);
151 $r = substr($r,0,length($r)-$l);
158 sub getiteminformation {
159 # returns a hash of item information given either the itemnumber or the barcode
160 my ($env, $itemnumber, $barcode) = @_;
164 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.itemnumber=$itemnumber and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
166 my $q_barcode=$dbh->quote($barcode);
167 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.barcode=$q_barcode and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
169 $env->{'apierror'}="getiteminformation() subroutine must be called with either an itemnumber or a barcode";
174 my $iteminformation=$sth->fetchrow_hashref;
176 if ($iteminformation) {
177 $sth=$dbh->prepare("select date_due from issues where itemnumber=$iteminformation->{'itemnumber'} and isnull(returndate)");
179 my ($date_due) = $sth->fetchrow;
180 $iteminformation->{'date_due'}=$date_due;
182 #$iteminformation->{'dewey'}=~s/0*$//;
183 ($iteminformation->{'dewey'} == 0) && ($iteminformation->{'dewey'}='');
184 $sth=$dbh->prepare("select * from itemtypes where itemtype='$iteminformation->{'itemtype'}'");
186 my $itemtype=$sth->fetchrow_hashref;
187 $iteminformation->{'loanlength'}=$itemtype->{'loanlength'};
191 return($iteminformation);
195 # returns an array of borrower hash references, given a cardnumber or a partial
197 my ($env, $key) = @_;
200 my $q_key=$dbh->quote($key);
201 my $sth=$dbh->prepare("select * from borrowers where cardnumber=$q_key");
204 my ($borrower)=$sth->fetchrow_hashref;
205 push (@borrowers, $borrower);
207 $q_key=$dbh->quote("$key%");
209 $sth=$dbh->prepare("select * from borrowers where surname like $q_key");
211 while (my $borrower = $sth->fetchrow_hashref) {
212 push (@borrowers, $borrower);
222 # transfer book code....
223 my ($tbr, $barcode, $ignoreRs) = @_;
227 my $branches = getbranches();
228 my $iteminformation = getiteminformation(\%env, 0, $barcode);
230 if (not $iteminformation) {
231 $messages->{'BadBarcode'} = $barcode;
234 # get branches of book...
235 my $hbr = $iteminformation->{'homebranch'};
236 my $fbr = $iteminformation->{'holdingbranch'};
238 if ($branches->{$hbr}->{'PE'}) {
239 $messages->{'IsPermanent'} = $hbr;
241 # cant transfer book if is already there....
243 $messages->{'DestinationEqualsHolding'} = 1;
246 # check if it is still issued to someone, return it...
247 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
248 if ($currentborrower) {
249 returnbook($barcode, $fbr);
250 $messages->{'WasReturned'} = $currentborrower;
253 my ($resfound, $resrec) = CheckReserves($iteminformation->{'itemnumber'});
254 if ($resfound and not $ignoreRs) {
255 $resrec->{'ResFound'} = $resfound;
256 $messages->{'ResFound'} = $resrec;
259 #actually do the transfer....
261 dotransfer($iteminformation->{'itemnumber'}, $fbr, $tbr);
262 $messages->{'WasTransfered'} = 1;
264 return ($dotransfer, $messages, $iteminformation);
268 my ($itm, $fbr, $tbr) = @_;
269 my $dbh = &C4Connect;
270 $itm = $dbh->quote($itm);
271 $fbr = $dbh->quote($fbr);
272 $tbr = $dbh->quote($tbr);
273 #new entry in branchtransfers....
274 my $query = "insert into branchtransfers (itemnumber, frombranch, datearrived, tobranch)
275 values($itm, $fbr, now(), $tbr)";
276 my $sth = $dbh->prepare($query);
279 #update holdingbranch in items .....
280 $query = "update items set datelastseen = now(), holdingbranch=$tbr where items.itemnumber=$itm";
281 $sth = $dbh->prepare($query);
290 my ($env, $patroninformation, $barcode, $responses, $date) = @_;
292 my $iteminformation = getiteminformation($env, 0, $barcode);
294 my ($rejected,$question,$defaultanswer,$questionnumber, $noissue);
297 if ($patroninformation->{'gonenoaddress'}) {
298 $rejected="Patron is gone, with no known address.";
301 if ($patroninformation->{'lost'}) {
302 $rejected="Patron's card has been reported lost.";
305 if ($patroninformation->{'debarred'}) {
306 $rejected="Patron is Debarred";
309 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh,$date);
310 if ($amount > 5 && $patroninformation->{'categorycode'} ne 'L' &&
311 $patroninformation->{'categorycode'} ne 'W' &&
312 $patroninformation->{'categorycode'} ne 'I' &&
313 $patroninformation->{'categorycode'} ne 'B' &&
314 $patroninformation->{'categorycode'} ne 'P') {
315 $rejected = sprintf "Patron owes \$%.02f.", $amount;
318 unless ($iteminformation) {
319 $rejected = "$barcode is not a valid barcode.";
322 if ($iteminformation->{'notforloan'} == 1) {
323 $rejected="Item not for loan.";
326 if ($iteminformation->{'wthdrawn'} == 1) {
327 $rejected="Item withdrawn.";
330 if ($iteminformation->{'restricted'} == 1) {
331 $rejected="Restricted item.";
334 if ($iteminformation->{'itemtype'} eq 'REF') {
335 $rejected="Reference item: Not for loan.";
338 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
339 if ($currentborrower eq $patroninformation->{'borrowernumber'}) {
340 # Already issued to current borrower
341 my ($renewstatus) = renewstatus($env,$dbh,$patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
342 if ($renewstatus == 0) {
343 $rejected="No more renewals allowed for this item.";
346 if ($responses->{4} eq '') {
348 $question = "Book is issued to this borrower.\nRenew?";
349 $defaultanswer = 'Y';
351 } elsif ($responses->{4} eq 'Y') {
352 my $charge = calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
354 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
355 $iteminformation->{'charge'} = $charge;
357 &UpdateStats($env,$env->{'branchcode'},'renew',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
358 renewbook($env,$dbh, $patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
365 } elsif ($currentborrower ne '') {
366 my ($currborrower, $cbflags) = getpatroninformation($env,$currentborrower,0);
367 if ($responses->{1} eq '') {
369 $question = "Issued to $currborrower->{'firstname'} $currborrower->{'surname'} ($currborrower->{'cardnumber'}).\nMark as returned?";
372 } elsif ($responses->{1} eq 'Y') {
373 returnbook($iteminformation->{'barcode'}, $env->{'branch'});
380 my ($restype, $res) = CheckReserves($iteminformation->{'itemnumber'});
382 my $resbor = $res->{'borrowernumber'};
383 if ($resbor eq $patroninformation->{'borrowernumber'}) {
385 } elsif ($restype eq "Waiting") {
386 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
387 my $branches = getbranches();
388 my $branchname = $branches->{$res->{'branchcode'}}->{'branchname'};
389 if ($responses->{2} eq '') {
391 $question="Waiting for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) at $branchname \nAllow issue?";
394 } elsif ($responses->{2} eq 'N') {
398 if ($responses->{3} eq '') {
400 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
403 } elsif ($responses->{3} eq 'Y') {
404 CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
407 } elsif ($restype eq "Reserved") {
408 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
409 my $branches = getbranches();
410 my $branchname = $branches->{$res->{'branchcode'}}->{'branchname'};
411 if ($responses->{5} eq '') {
413 $question="Reserved for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) since $res->{'reservedate'} \nAllow issue?";
416 } elsif ($responses->{5} eq 'N') {
417 if ($responses->{6} eq '') {
419 $question="Set reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) to waiting and transfer to $branchname?";
421 } elsif ($responses->{6} eq 'Y') {
422 my $tobrcd = ReserveWaiting($res->{'itemnumber'}, $res->{'borrowernumber'});
423 transferbook($tobrcd, $barcode, 1);
424 $message = "Item should now be waiting at $branchname";
429 if ($responses->{7} eq '') {
431 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
434 } elsif ($responses->{7} eq 'Y') {
435 CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
442 unless (($question) || ($rejected) || ($noissue)) {
444 if ($iteminformation->{'loanlength'}) {
445 $loanlength=$iteminformation->{'loanlength'};
448 my $datedue=time+($loanlength)*86400;
449 my @datearr = localtime($datedue);
450 $dateduef = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
451 if ($env->{'datedue'}) {
452 $dateduef=$env->{'datedue'};
454 $dateduef=~ s/2001\-4\-25/2001\-4\-26/;
455 my $sth=$dbh->prepare("insert into issues (borrowernumber, itemnumber, date_due, branchcode) values ($patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'}, '$dateduef', '$env->{'branchcode'}')");
458 $iteminformation->{'issues'}++;
459 $sth=$dbh->prepare("update items set issues=$iteminformation->{'issues'} where itemnumber=$iteminformation->{'itemnumber'}");
462 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
464 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
465 $iteminformation->{'charge'}=$charge;
467 &UpdateStats($env,$env->{'branchcode'},'issue',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
469 if ($iteminformation->{'charge'}) {
470 $message=sprintf "Rental charge of \$%.02f applies.", $iteminformation->{'charge'};
473 return ($iteminformation, $dateduef, $rejected, $question, $questionnumber, $defaultanswer, $message);
479 my ($barcode, $branch) = @_;
483 # get information on item
484 my ($iteminformation) = getiteminformation(\%env, 0, $barcode);
485 if (not $iteminformation) {
486 $messages->{'BadBarcode'} = $barcode;
490 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
491 if ((not $currentborrower) && $doreturn) {
492 $messages->{'NotIssued'} = $barcode;
495 # check if the book is in a permanent collection....
496 my $hbr = $iteminformation->{'homebranch'};
497 my $branches = getbranches();
498 if ($branches->{$hbr}->{'PE'}) {
499 $messages->{'IsPermanent'} = $hbr;
501 # update issues, thereby returning book (should push this out into another subroutine
502 my ($borrower) = getpatroninformation(\%env, $currentborrower, 0);
504 doreturn($borrower->{'borrowernumber'}, $iteminformation->{'itemnumber'});
505 $messages->{'WasReturned'};
507 ($borrower) = getpatroninformation(\%env, $currentborrower, 0);
509 my ($transfered, $mess, $item) = transferbook($branch, $barcode);
511 $messages->{'WasTransfered'};
513 # fix up the accounts.....
514 if ($iteminformation->{'itemlost'}) {
515 updateitemlost($iteminformation->{'itemnumber'});
516 fixaccountforlostandreturned($iteminformation, $borrower);
517 $messages->{'WasLost'};
519 # fix up the overdues in accounts...
520 fixoverduesonreturn($borrower->{'borrowernumber'}, $iteminformation->{'itemnumber'});
522 my ($resfound, $resrec) = CheckReserves($iteminformation->{'itemnumber'});
524 $resrec->{'ResFound'} = $resfound;
525 $messages->{'ResFound'} = $resrec;
528 UpdateStats(\%env, $branch ,'return','0','',$iteminformation->{'itemnumber'});
529 return ($doreturn, $messages, $iteminformation, $borrower);
534 my ($brn, $itm) = @_;
536 $brn = $dbh->quote($brn);
537 $itm = $dbh->quote($itm);
538 my $query = "update issues set returndate = now() where (borrowernumber = $brn)
539 and (itemnumber = $itm) and (returndate is null)";
540 my $sth = $dbh->prepare($query);
549 my $query="update items set itemlost=0 where itemnumber=$itemno";
550 my $sth=$dbh->prepare($query);
555 sub fixaccountforlostandreturned {
556 my ($iteminfo, $borrower) = @_;
559 my $itm = $dbh->quote($iteminfo->{'itemnumber'});
560 # check for charge made for lost book
561 my $query = "select * from accountlines where (itemnumber = $itm)
562 and (accounttype='L' or accounttype='Rep') order by date desc";
563 my $sth = $dbh->prepare($query);
565 if (my $data = $sth->fetchrow_hashref) {
566 # writeoff this amount
568 my $amount = $data->{'amount'};
569 my $acctno = $data->{'accountno'};
571 if ($data->{'amountoutstanding'} == $amount) {
572 $offset = $data->{'amount'};
575 $offset = $amount - $data->{'amountoutstanding'};
576 $amountleft = $data->{'amountoutstanding'} - $amount;
578 my $uquery = "update accountlines set accounttype = 'LR',amountoutstanding='0'
579 where (borrowernumber = '$data->{'borrowernumber'}')
580 and (itemnumber = $itm) and (accountno = '$acctno') ";
581 my $usth = $dbh->prepare($uquery);
584 #check if any credit is left if so writeoff other accounts
585 my $nextaccntno = getnextacctno(\%env,$data->{'borrowernumber'},$dbh);
586 if ($amountleft < 0){
589 if ($amountleft > 0){
590 my $query = "select * from accountlines where (borrowernumber = '$data->{'borrowernumber'}')
591 and (amountoutstanding >0) order by date";
592 my $msth = $dbh->prepare($query);
594 # offset transactions
597 while (($accdata=$msth->fetchrow_hashref) and ($amountleft>0)){
598 if ($accdata->{'amountoutstanding'} < $amountleft) {
600 $amountleft = $amountleft - $accdata->{'amountoutstanding'};
602 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
605 my $thisacct = $accdata->{'accountno'};
606 my $updquery = "update accountlines set amountoutstanding= '$newamtos'
607 where (borrowernumber = '$data->{'borrowernumber'}')
608 and (accountno='$thisacct')";
609 my $usth = $dbh->prepare($updquery);
612 $updquery = "insert into accountoffsets
613 (borrowernumber, accountno, offsetaccount, offsetamount)
615 ('$data->{'borrowernumber'}','$accdata->{'accountno'}','$nextaccntno','$newamtos')";
616 my $usth = $dbh->prepare($updquery);
622 if ($amountleft > 0){
625 my $desc="Book Returned ".$iteminfo->{'barcode'};
626 $uquery = "insert into accountlines
627 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
628 values ('$data->{'borrowernumber'}','$nextaccntno',now(),0-$amount,'$desc',
630 $usth = $dbh->prepare($uquery);
633 $uquery = "insert into accountoffsets
634 (borrowernumber, accountno, offsetaccount, offsetamount)
635 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
636 $usth = $dbh->prepare($uquery);
639 $uquery = "update items set paidfor='' where itemnumber=$itm";
640 $usth = $dbh->prepare($uquery);
648 sub fixoverduesonreturn {
649 my ($brn, $itm) = @_;
651 $itm = $dbh->quote($itm);
652 $brn = $dbh->quote($brn);
653 # check for overdue fine
654 my $query = "select * from accountlines where (borrowernumber=$brn)
655 and (itemnumber = $itm) and (accounttype='FU' or accounttype='O')";
656 my $sth = $dbh->prepare($query);
658 # alter fine to show that the book has been returned
659 if (my $data = $sth->fetchrow_hashref) {
660 my $query = "update accountlines set accounttype='F' where (borrowernumber = $brn)
661 and (itemnumber = $itm) and (acccountno='$data->{'accountno'}')";
662 my $usth=$dbh->prepare($query);
671 # Original subroutine for Circ2.pm
673 my ($env, $patroninformation, $dbh) = @_;
674 my $amount = checkaccount($env, $patroninformation->{'borrowernumber'}, $dbh);
677 $flaginfo{'message'}= sprintf "Patron owes \$%.02f", $amount;
679 $flaginfo{'noissues'} = 1;
681 $flags{'CHARGES'} = \%flaginfo;
682 } elsif ($amount < 0){
684 $amount = $amount*-1;
685 $flaginfo{'message'} = sprintf "Patron has credit of \$%.02f", $amount;
686 $flags{'CHARGES'} = \%flaginfo;
688 if ($patroninformation->{'gonenoaddress'} == 1) {
690 $flaginfo{'message'} = 'Borrower has no valid address.';
691 $flaginfo{'noissues'} = 1;
692 $flags{'GNA'} = \%flaginfo;
694 if ($patroninformation->{'lost'} == 1) {
696 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
697 $flaginfo{'noissues'} = 1;
698 $flags{'LOST'} = \%flaginfo;
700 if ($patroninformation->{'debarred'} == 1) {
702 $flaginfo{'message'} = 'Borrower is Debarred.';
703 $flaginfo{'noissues'} = 1;
704 $flags{'DBARRED'} = \%flaginfo;
706 if ($patroninformation->{'borrowernotes'}) {
708 $flaginfo{'message'} = "$patroninformation->{'borrowernotes'}";
709 $flags{'NOTES'} = \%flaginfo;
711 my ($odues, $itemsoverdue)
712 = checkoverdues($env, $patroninformation->{'borrowernumber'}, $dbh);
715 $flaginfo{'message'} = "Yes";
716 $flaginfo{'itemlist'} = $itemsoverdue;
717 foreach (sort {$a->{'date_due'} cmp $b->{'date_due'}} @$itemsoverdue) {
718 $flaginfo{'itemlisttext'}.="$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n";
720 $flags{'ODUES'} = \%flaginfo;
722 my ($nowaiting, $itemswaiting)
723 = CheckWaiting($patroninformation->{'borrowernumber'});
724 if ($nowaiting > 0) {
726 $flaginfo{'message'} = "Reserved items available";
727 $flaginfo{'itemlist'} = $itemswaiting;
728 $flags{'WAITING'} = \%flaginfo;
735 # From Main.pm, modified to return a list of overdueitems, in addition to a count
736 #checks whether a borrower has overdue items
737 my ($env, $bornum, $dbh)=@_;
738 my @datearr = localtime;
739 my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3];
742 my $query = "SELECT * FROM issues,biblio,biblioitems,items
743 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
744 AND items.biblionumber = biblio.biblionumber
745 AND issues.itemnumber = items.itemnumber
746 AND issues.borrowernumber = $bornum
747 AND issues.returndate is NULL
748 AND issues.date_due < '$today'";
749 my $sth = $dbh->prepare($query);
751 while (my $data = $sth->fetchrow_hashref) {
752 push (@overdueitems, $data);
756 return ($count, \@overdueitems);
759 sub currentborrower {
760 # Original subroutine for Circ2.pm
761 my ($itemnumber) = @_;
762 my $dbh = &C4Connect;
763 my $q_itemnumber = $dbh->quote($itemnumber);
764 my $sth=$dbh->prepare("select borrowers.borrowernumber from
765 issues,borrowers where issues.itemnumber=$q_itemnumber and
766 issues.borrowernumber=borrowers.borrowernumber and issues.returndate is
769 my ($borrower) = $sth->fetchrow;
774 # Stolen from Main.pm
775 # Check for reserves for biblio
776 my ($env,$dbh,$itemnum)=@_;
778 my $query = "select * from reserves,items
779 where (items.itemnumber = '$itemnum')
780 and (reserves.cancellationdate is NULL)
781 and (items.biblionumber = reserves.biblionumber)
782 and ((reserves.found = 'W')
783 or (reserves.found is null))
785 my $sth = $dbh->prepare($query);
788 my $data=$sth->fetchrow_hashref;
789 while ($data && $resbor eq '') {
791 my $const = $data->{'constrainttype'};
793 $resbor = $data->{'borrowernumber'};
796 my $cquery = "select * from reserveconstraints,items
797 where (borrowernumber='$data->{'borrowernumber'}')
798 and reservedate='$data->{'reservedate'}'
799 and reserveconstraints.biblionumber='$data->{'biblionumber'}'
800 and (items.itemnumber=$itemnum and
801 items.biblioitemnumber = reserveconstraints.biblioitemnumber)";
802 my $csth = $dbh->prepare($cquery);
804 if (my $cdata=$csth->fetchrow_hashref) {$found = 1;}
806 if ($found eq 1) {$resbor = $data->{'borrowernumber'};}
808 if ($found eq 0) {$resbor = $data->{'borrowernumber'};}
812 $data=$sth->fetchrow_hashref;
815 return ($resbor,$resrec);
819 # New subroutine for Circ2.pm
820 my ($env, $borrower) = @_;
824 my $borrowernumber = $borrower->{'borrowernumber'};
826 if ($env->{'todaysissues'}) {
827 my @datearr = localtime(time());
828 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
829 $crit=" and issues.timestamp like '$today%' ";
831 if ($env->{'nottodaysissues'}) {
832 my @datearr = localtime(time());
833 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
834 $crit=" and !(issues.timestamp like '$today%') ";
836 my $select="select * from issues,items,biblioitems,biblio where
837 borrowernumber='$borrowernumber' and issues.itemnumber=items.itemnumber and
838 items.biblionumber=biblio.biblionumber and
839 items.biblioitemnumber=biblioitems.biblioitemnumber and returndate is null
840 $crit order by issues.timestamp desc";
842 my $sth=$dbh->prepare($select);
844 while (my $data = $sth->fetchrow_hashref) {
845 $data->{'dewey'}=~s/0*$//;
846 ($data->{'dewey'} == 0) && ($data->{'dewey'}='');
847 my @datearr = localtime(time());
848 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]
849 +1)).sprintf ("%0.2d", $datearr[3]);
850 my $datedue=$data->{'date_due'};
852 if ($datedue < $todaysdate) {
853 $data->{'overdue'}=1;
855 my $itemnumber=$data->{'itemnumber'};
856 $currentissues{$counter}=$data;
861 return(\%currentissues);
864 # New subroutine for Circ2.pm
867 my $borrowernumber = $borrower->{'borrowernumber'};
868 my $brn =$dbh->quote($borrowernumber);
870 my $select = "select issues.timestamp, issues.date_due, items.biblionumber,
871 items.barcode, biblio.title, biblio.author, biblioitems.dewey,
873 from issues,items,biblioitems,biblio
874 where issues.borrowernumber = $brn
875 and issues.itemnumber = items.itemnumber
876 and items.biblionumber = biblio.biblionumber
877 and items.biblioitemnumber = biblioitems.biblioitemnumber
878 and issues.returndate is null
879 order by issues.timestamp desc";
881 my $sth=$dbh->prepare($select);
884 while (my $data = $sth->fetchrow_hashref) {
885 $data->{'dewey'} =~ s/0*$//;
886 ($data->{'dewey'} == 0) && ($data->{'dewey'} = '');
887 my @datearr = localtime(time());
888 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]+1)).sprintf ("%0.2d", $datearr[3]);
889 my $datedue = $data->{'date_due'};
891 if ($datedue < $todaysdate) {
892 $data->{'overdue'} = 1;
894 $currentissues{$counter} = $data;
899 return(\%currentissues);
904 # check for reserves waiting
905 my ($env,$dbh,$bornum)=@_;
907 my $query = "select * from reserves
908 where (borrowernumber = '$bornum')
909 and (reserves.found='W') and cancellationdate is NULL";
910 my $sth = $dbh->prepare($query);
913 if (my $data=$sth->fetchrow_hashref) {
914 @itemswaiting[$cnt] =$data;
918 return ($cnt,\@itemswaiting);
923 # Stolen from Accounts.pm
924 #take borrower number
925 #check accounts and list amounts owing
926 my ($env,$bornumber,$dbh,$date)=@_;
927 my $select="Select sum(amountoutstanding) from accountlines where
928 borrowernumber=$bornumber and amountoutstanding<>0";
930 $select.=" and date < '$date'";
933 my $sth=$dbh->prepare($select);
936 while (my $data=$sth->fetchrow_hashref){
937 $total=$total+$data->{'sum(amountoutstanding)'};
940 # output(1,2,"borrower owes $total");
942 # # output(1,2,"borrower owes $total");
944 # reconcileaccount($env,$dbh,$bornumber,$total);
952 # Stolen from Renewals.pm
953 # check renewal status
954 my ($env,$dbh,$bornum,$itemno)=@_;
957 my $q1 = "select * from issues
958 where (borrowernumber = '$bornum')
959 and (itemnumber = '$itemno')
960 and returndate is null";
961 my $sth1 = $dbh->prepare($q1);
963 if (my $data1 = $sth1->fetchrow_hashref) {
964 my $q2 = "select renewalsallowed from items,biblioitems,itemtypes
965 where (items.itemnumber = '$itemno')
966 and (items.biblioitemnumber = biblioitems.biblioitemnumber)
967 and (biblioitems.itemtype = itemtypes.itemtype)";
968 my $sth2 = $dbh->prepare($q2);
970 if (my $data2=$sth2->fetchrow_hashref) {
971 $renews = $data2->{'renewalsallowed'};
973 if ($renews > $data1->{'renewals'}) {
983 # Stolen from Renewals.pm
984 # mark book as renewed
985 my ($env,$dbh,$bornum,$itemno,$datedue)=@_;
986 $datedue=$env->{'datedue'};
987 if ($datedue eq "" ) {
989 my $query= "Select * from biblioitems,items,itemtypes
990 where (items.itemnumber = '$itemno')
991 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
992 and (biblioitems.itemtype = itemtypes.itemtype)";
993 my $sth=$dbh->prepare($query);
995 if (my $data=$sth->fetchrow_hashref) {
996 $loanlength = $data->{'loanlength'}
1000 my $datedu = time + ($loanlength * 86400);
1001 my @datearr = localtime($datedu);
1002 $datedue = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
1004 my @date = split("-",$datedue);
1005 my $odatedue = (@date[2]+0)."-".(@date[1]+0)."-".@date[0];
1006 my $issquery = "select * from issues where borrowernumber='$bornum' and
1007 itemnumber='$itemno' and returndate is null";
1008 my $sth=$dbh->prepare($issquery);
1010 my $issuedata=$sth->fetchrow_hashref;
1012 my $renews = $issuedata->{'renewals'} +1;
1013 my $updquery = "update issues
1014 set date_due = '$datedue', renewals = '$renews'
1015 where borrowernumber='$bornum' and
1016 itemnumber='$itemno' and returndate is null";
1017 my $sth=$dbh->prepare($updquery);
1025 # Stolen from Issues.pm
1026 # calculate charges due
1027 my ($env, $dbh, $itemno, $bornum)=@_;
1030 my $q1 = "select itemtypes.itemtype,rentalcharge from items,biblioitems,itemtypes where (items.itemnumber ='$itemno') and (biblioitems.biblioitemnumber = items.biblioitemnumber) and (biblioitems.itemtype = itemtypes.itemtype)";
1031 my $sth1= $dbh->prepare($q1);
1033 if (my $data1=$sth1->fetchrow_hashref) {
1034 $item_type = $data1->{'itemtype'};
1035 $charge = $data1->{'rentalcharge'};
1036 my $q2 = "select rentaldiscount from borrowers,categoryitem
1037 where (borrowers.borrowernumber = '$bornum')
1038 and (borrowers.categorycode = categoryitem.categorycode)
1039 and (categoryitem.itemtype = '$item_type')";
1040 my $sth2=$dbh->prepare($q2);
1042 if (my $data2=$sth2->fetchrow_hashref) {
1043 my $discount = $data2->{'rentaldiscount'};
1044 $charge = ($charge *(100 - $discount)) / 100;
1053 #Stolen from Issues.pm
1054 my ($env,$dbh,$itemno,$bornum,$charge) = @_;
1055 my $nextaccntno = getnextacctno($env,$bornum,$dbh);
1056 my $query = "insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) values ($bornum,$itemno,$nextaccntno,now(),$charge,'Rental','Rent',$charge)";
1057 my $sth = $dbh->prepare($query);
1064 # Stolen from Accounts.pm
1065 my ($env,$bornumber,$dbh)=@_;
1066 my $nextaccntno = 1;
1067 my $query = "select * from accountlines where (borrowernumber = '$bornumber') order by accountno desc";
1068 my $sth = $dbh->prepare($query);
1070 if (my $accdata=$sth->fetchrow_hashref){
1071 $nextaccntno = $accdata->{'accountno'} + 1;
1074 return($nextaccntno);
1078 # Stolen from Returns.pm
1082 my ($itemdata) = getiteminformation(\%env, $itemno,0);
1083 my $bibno = $dbh->quote($itemdata->{'biblionumber'});
1084 my $bibitm = $dbh->quote($itemdata->{'biblioitemnumber'});
1085 my $query = "select * from reserves where ((found = 'W') or (found is null))
1086 and biblionumber = $bibno and cancellationdate is NULL
1087 order by priority, reservedate ";
1088 my $sth = $dbh->prepare($query);
1094 while (($resrec = $sth->fetchrow_hashref) && (not $resfound)) {
1096 my $brn = $dbh->quote($resrec->{'borrowernumber'});
1097 my $rdate = $dbh->quote($resrec->{'reservedate'});
1098 my $bibno = $dbh->quote($resrec->{'biblionumber'});
1099 if ($resrec->{'found'} eq "W") {
1100 if ($resrec->{'itemnumber'} eq $itemno) {
1104 if ($resrec->{'constrainttype'} eq "a") {
1107 my $conquery = "select * from reserveconstraints where borrowernumber = $brn
1108 and reservedate = $rdate and biblionumber = $bibno and biblioitemnumber = $bibitm";
1109 my $consth = $dbh->prepare($conquery);
1111 if (my $conrec = $consth->fetchrow_hashref) {
1112 if ($resrec->{'constrainttype'} eq "o") {
1120 my $updquery = "update reserves set found = 'W', itemnumber = '$itemno'
1121 where borrowernumber = $brn and reservedate = $rdate and biblionumber = $bibno";
1122 my $updsth = $dbh->prepare($updquery);
1128 return ($resfound,$lastrec);
1131 END { } # module clean-up code here (global destructor)