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;
21 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
23 # set the version for version checking
27 @EXPORT = qw(&getbranches &getprinters &getpatroninformation ¤tissues &getiteminformation &findborrower &issuebook &returnbook &returnbook2 &find_reserves &transferbook &decode);
28 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
30 # your exported package globals go here,
31 # as well as any optionally exported functions
33 @EXPORT_OK = qw($Var1 %Hashit);
36 # non-exported package globals go here
37 #use vars qw(@more $stuff);
39 # initalize package globals, first exported ones
44 # then the others (which are still accessible as $Some::Module::stuff)
48 # all file-scoped lexicals must be created before
49 # the functions below that use them.
51 # file-private lexicals go here
55 # here's a file-private function as a closure,
56 # callable as &$priv_func; it cannot be prototyped.
61 # make all your functions, whether exported or not;
65 # returns a reference to a hash of references to branches...
68 my $sth=$dbh->prepare("select * from branches");
70 while (my $branch=$sth->fetchrow_hashref) {
71 my $tmp = $branch->{'branchcode'}; my $brc = $dbh->quote($tmp);
72 my $query = "select categorycode from branchrelations where branchcode = $brc";
73 my $nsth = $dbh->prepare($query);
75 while (my ($cat) = $nsth->fetchrow_array) {
79 $branches{$branch->{'branchcode'}}=$branch;
90 my $sth=$dbh->prepare("select * from printers");
92 while (my $printer=$sth->fetchrow_hashref) {
93 $printers{$printer->{'printqueue'}}=$printer;
101 sub getpatroninformation {
103 my ($env, $borrowernumber,$cardnumber) = @_;
107 open O, ">>/root/tkcirc.out";
108 print O "Looking up patron $borrowernumber / $cardnumber\n";
109 if ($borrowernumber) {
110 $query = "select * from borrowers where borrowernumber=$borrowernumber";
111 } elsif ($cardnumber) {
112 $query = "select * from borrowers where cardnumber=$cardnumber";
114 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
117 $env->{'mess'} = $query;
118 $sth = $dbh->prepare($query);
120 my $borrower = $sth->fetchrow_hashref;
121 my $flags = patronflags($env, $borrower, $dbh);
124 print O "$borrower->{'surname'} <---\n";
126 $borrower->{'flags'}=$flags;
127 return($borrower, $flags);
132 my $seq = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-';
133 my @s = map { index($seq,$_); } split(//,$encoded);
148 my $n = (($s[0] << 6 | $s[1]) << 6 | $s[2]) << 6 | $s[3];
149 $r .=chr(($n >> 16) ^ 67) .
150 chr(($n >> 8 & 255) ^ 67) .
151 chr(($n & 255) ^ 67);
154 $r = substr($r,0,length($r)-$l);
161 sub getiteminformation {
162 # returns a hash of item information given either the itemnumber or the barcode
163 my ($env, $itemnumber, $barcode) = @_;
167 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.itemnumber=$itemnumber and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
169 my $q_barcode=$dbh->quote($barcode);
170 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.barcode=$q_barcode and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
172 $env->{'apierror'}="getiteminformation() subroutine must be called with either an itemnumber or a barcode";
177 my $iteminformation=$sth->fetchrow_hashref;
179 if ($iteminformation) {
180 $sth=$dbh->prepare("select date_due from issues where itemnumber=$iteminformation->{'itemnumber'} and isnull(returndate)");
182 my ($date_due) = $sth->fetchrow;
183 $iteminformation->{'date_due'}=$date_due;
185 #$iteminformation->{'dewey'}=~s/0*$//;
186 ($iteminformation->{'dewey'} == 0) && ($iteminformation->{'dewey'}='');
187 $sth=$dbh->prepare("select * from itemtypes where itemtype='$iteminformation->{'itemtype'}'");
189 my $itemtype=$sth->fetchrow_hashref;
190 $iteminformation->{'loanlength'}=$itemtype->{'loanlength'};
194 return($iteminformation);
198 # returns an array of borrower hash references, given a cardnumber or a partial
200 my ($env, $key) = @_;
203 my $q_key=$dbh->quote($key);
204 my $sth=$dbh->prepare("select * from borrowers where cardnumber=$q_key");
207 my ($borrower)=$sth->fetchrow_hashref;
208 push (@borrowers, $borrower);
210 $q_key=$dbh->quote("$key%");
212 $sth=$dbh->prepare("select * from borrowers where surname like $q_key");
214 while (my $borrower = $sth->fetchrow_hashref) {
215 push (@borrowers, $borrower);
225 # transfer book code....
226 my ($tbr, $barcode) = @_;
229 my $branches = getbranches();
230 my $iteminformation = getiteminformation(\%env,0, $barcode);
231 if (not $iteminformation) {
232 $message = "<font color='red' size='+2'>No item with barcode: $barcode </font>";
233 return (0, $message, 0);
235 my $fbr = $iteminformation->{'holdingbranch'};
236 if ($branches->{$fbr}->{'PE'}) {
237 $message = "<font color='red' size='+2'>You cannot transfer a book that is in a permanant branch.</font>";
238 return (0, $message, $iteminformation);
241 $message = "<font color='red' size='+2'>You can't transfer the book to the branch it is already at! </font>";
242 return (0, $message, $iteminformation);
245 my ($currentborrower) = currentborrower(\%env, $iteminformation->{'itemnumber'}, $dbh);
246 if ($currentborrower) {
247 $message = "<font color='red' size='+2'>Book cannot be transfered bracause it is currently on loan to: $currentborrower . Please return book first.</font>";
248 return (0, $message, $iteminformation);
250 my $itm = $dbh->quote($iteminformation->{'itemnumber'});
251 $fbr = $dbh->quote($fbr);
252 $tbr = $dbh->quote($tbr);
253 #new entry in branchtransfers....
254 my $query = "insert into branchtransfers (itemnumber, frombranch, datearrived, tobranch) values($itm, $fbr, now(), $tbr)";
255 my $sth = $dbh->prepare($query);
258 #update holdingbranch in items .....
259 $query = "update items set datelastseen = now(), holdingbranch=$tbr where items.itemnumber=$itm";
260 $sth = $dbh->prepare($query);
264 return (1, $message, $iteminformation);
269 my ($env, $patroninformation, $barcode, $responses, $date) = @_;
271 my $iteminformation=getiteminformation($env, 0, $barcode);
273 my ($rejected,$question,$defaultanswer,$questionnumber, $noissue);
275 if ($patroninformation->{'gonenoaddress'}) {
276 $rejected="Patron is gone, with no known address.";
279 if ($patroninformation->{'lost'}) {
280 $rejected="Patron's card has been reported lost.";
283 if ($patroninformation->{'debarred'}) {
284 $rejected="Patron is Debarred";
287 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh,$date);
288 if ($amount>5 && $patroninformation->{'categorycode'} ne 'L' &&
289 $patroninformation->{'categorycode'} ne 'W' &&
290 $patroninformation->{'categorycode'} ne 'I'
291 && $patroninformation->{'categorycode'} ne 'B' &&
292 $patroninformation->{'categorycode'} ne 'P') {
293 $rejected=sprintf "Patron owes \$%.02f.", $amount;
296 unless ($iteminformation) {
297 $rejected="$barcode is not a valid barcode.";
300 if ($iteminformation->{'notforloan'} == 1) {
301 $rejected="Item not for loan.";
304 if ($iteminformation->{'wthdrawn'} == 1) {
305 $rejected="Item withdrawn.";
308 if ($iteminformation->{'restricted'} == 1) {
309 $rejected="Restricted item.";
312 if ($iteminformation->{'itemtype'} eq 'REF') {
313 $rejected="Reference item: Not for loan.";
316 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
317 if ($currentborrower eq $patroninformation->{'borrowernumber'}) {
318 # Already issued to current borrower
319 my ($renewstatus) = renewstatus($env,$dbh,$patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
320 if ($renewstatus == 0) {
321 $rejected="No more renewals allowed for this item.";
324 if ($responses->{4} eq '') {
326 $question="Book is issued to this borrower.\nRenew?";
329 } elsif ($responses->{4} eq 'Y') {
330 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
332 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
333 $iteminformation->{'charge'}=$charge;
335 &UpdateStats($env,$env->{'branchcode'},'renew',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
336 renewbook($env,$dbh, $patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
343 } elsif ($currentborrower ne '') {
344 my ($currborrower, $cbflags)=getpatroninformation($env,$currentborrower,0);
345 if ($responses->{1} eq '') {
347 $question="Issued to $currborrower->{'firstname'} $currborrower->{'surname'} ($currborrower->{'cardnumber'}).\nMark as returned?";
350 } elsif ($responses->{1} eq 'Y') {
351 returnbook($env,$iteminformation->{'barcode'});
358 my ($resbor, $resrec) = checkreserve($env, $dbh, $iteminformation->{'itemnumber'});
360 if ($resbor eq $patroninformation->{'borrowernumber'}) {
361 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
362 my $rsth = $dbh->prepare($rquery);
365 } elsif ($resbor ne "") {
366 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
367 if ($responses->{2} eq '') {
369 $question="Reserved for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) since $resrec->{'reservedate'}\nAllow issue?";
372 } elsif ($responses->{2} eq 'N') {
373 #printreserve($env, $resrec, $resborrower, $iteminformation);
377 if ($responses->{3} eq '') {
379 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
382 } elsif ($responses->{3} eq 'Y') {
383 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
384 my $rsth = $dbh->prepare($rquery);
392 unless (($question) || ($rejected) || ($noissue)) {
394 if ($iteminformation->{'loanlength'}) {
395 $loanlength=$iteminformation->{'loanlength'};
398 my $datedue=time+($loanlength)*86400;
399 my @datearr = localtime($datedue);
400 $dateduef = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
401 if ($env->{'datedue'}) {
402 $dateduef=$env->{'datedue'};
404 $dateduef=~ s/2001\-4\-25/2001\-4\-26/;
405 my $sth=$dbh->prepare("insert into issues (borrowernumber, itemnumber, date_due, branchcode) values ($patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'}, '$dateduef', '$env->{'branchcode'}')");
408 $iteminformation->{'issues'}++;
409 $sth=$dbh->prepare("update items set issues=$iteminformation->{'issues'} where itemnumber=$iteminformation->{'itemnumber'}");
412 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
414 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
415 $iteminformation->{'charge'}=$charge;
417 &UpdateStats($env,$env->{'branchcode'},'issue',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
420 if ($iteminformation->{'charge'}) {
421 $message=sprintf "Rental charge of \$%.02f applies.", $iteminformation->{'charge'};
424 return ($iteminformation, $dateduef, $rejected, $question, $questionnumber, $defaultanswer, $message);
428 my ($dbh,$itemno)=@_;
429 my $query="update items set itemlost=0 where itemnumber=$itemno";
430 my $sth=$dbh->prepare($query);
436 my ($env, $barcode) = @_;
437 my ($messages, $overduecharge);
439 my ($iteminformation) = getiteminformation($env, 0, $barcode);
441 if ($iteminformation) {
442 my $sth=$dbh->prepare("select * from issues where (itemnumber='$iteminformation->{'itemnumber'}') and (returndate is null)");
444 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
445 updatelastseen($env,$dbh,$iteminformation->{'itemnumber'});
446 updateitemlost($dbh,$iteminformation->{'itemnumber'});
447 if ($currentborrower) {
448 ($borrower)=getpatroninformation($env,$currentborrower,0);
449 my @datearr = localtime(time);
450 my $dateret = (1900+$datearr[5])."-".$datearr[4]."-".$datearr[3];
451 my $query = "update issues set returndate = now(), branchcode ='$env->{'branchcode'}' where (borrowernumber = $borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (returndate is null)";
452 my $sth = $dbh->prepare($query);
457 # check for overdue fine
459 $sth=$dbh->prepare("select * from accountlines where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (accounttype='FU' or accounttype='O')");
461 # alter fine to show that the book has been returned
462 if (my $data = $sth->fetchrow_hashref) {
463 my $usth=$dbh->prepare("update accountlines set accounttype='F' where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber=$iteminformation->{'itemnumber'}) and (acccountno='$data->{'accountno'}')");
466 $overduecharge=$data->{'amountoutstanding'};
470 if ($iteminformation->{'itemlost'} eq '1'){
471 # check for charge made for lost book
472 my $query="select * from accountlines where (itemnumber =
473 $iteminformation->{'itemnumber'}) and (accounttype='L' or accounttype='Rep')
476 $sth=$dbh->prepare($query);
478 if (my $data = $sth->fetchrow_hashref) {
479 # writeoff this amount
481 my $amount = $data->{'amount'};
482 my $acctno = $data->{'accountno'};
485 if ($data->{'amountoutstanding'} == $amount) {
486 $offset = $data->{'amount'};
489 $offset = $amount - $data->{'amountoutstanding'};
490 $amountleft = $data->{'amountoutstanding'} - $amount;
492 my $uquery = "update accountlines
493 set accounttype = 'LR',amountoutstanding='0'
494 where (borrowernumber = $data->{'borrowernumber'})
495 and (itemnumber = $iteminformation->{'itemnumber'})
496 and (accountno = '$acctno') ";
498 my $usth = $dbh->prepare($uquery);
501 #check if any credit is left if so writeoff other accounts]
502 my $nextaccntno = getnextacctno($env,$data->{'borrowernumber'},$dbh);
503 if ($amountleft < 0){
506 if ($amountleft > 0){
508 my $query = "select * from accountlines
509 where (borrowernumber = '$data->{'borrowernumber'}') and (amountoutstanding >0)
511 my $sth = $dbh->prepare($query);
513 # offset transactions
516 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
517 if ($accdata->{'amountoutstanding'} < $amountleft) {
519 $amountleft = $amountleft - $accdata->{'amountoutstanding'};
521 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
524 my $thisacct = $accdata->{accountno};
525 my $updquery = "update accountlines set amountoutstanding= '$newamtos'
526 where (borrowernumber = '$data->{'borrowernumber'}') and (accountno='$thisacct')";
527 my $usth = $dbh->prepare($updquery);
530 $updquery = "insert into accountoffsets
531 (borrowernumber, accountno, offsetaccount, offsetamount)
533 ($data->{'borrowernumber'},$accdata->{'accountno'},$nextaccntno,$newamtos)";
534 my $usth = $dbh->prepare($updquery);
539 if ($amountleft > 0){
543 my $desc="Book Returned ".$iteminformation->{'barcode'};
544 $uquery = "insert into accountlines
545 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
546 values ($data->{'borrowernumber'},$nextaccntno,now(),0-$amount,'$desc',
548 $usth = $dbh->prepare($uquery);
552 $uquery = "insert into accountoffsets
553 (borrowernumber, accountno, offsetaccount, offsetamount)
554 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
555 $usth = $dbh->prepare($uquery);
558 $uquery="update items set paidfor='' where itemnumber=$iteminformation->{'itemnumber'}";
559 $usth = $dbh->prepare($uquery);
565 my ($resfound,$resrec) = find_reserves($env, $dbh, $iteminformation->{'itemnumber'});
566 if ($resfound eq 'y') {
567 my ($borrower) = getpatroninformation($env,$resrec->{'borrowernumber'},0);
568 #printreserve($env,$resrec,$resborrower,$itemrec);
569 my ($branches) = getbranches();
570 my $branchname=$branches->{$resrec->{'branchcode'}}->{'branchname'};
571 push (@$messages, "<b><font color=red>RESERVED</font></b> for collection by $borrower->{'firstname'} $borrower->{'surname'} ($borrower->{'cardnumber'}) at $branchname");
573 UpdateStats($env,$env->{'branchcode'},'return','0','',$iteminformation->{'itemnumber'});
576 return ($iteminformation, $borrower, $messages, $overduecharge);
582 my ($env, $barcode) = @_;
585 # get information on item
586 my ($iteminformation) = getiteminformation($env, 0, $barcode);
587 if (not $iteminformation) {
588 push(@messages, "<font color='red' size='+2'> There is no book with barcode: $barcode </font>");
589 return (0, \@messages, 0 ,0);
591 # updatelastseen($env, $dbh, $iteminformation->{'itemnumber'});
595 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
596 if (not $currentborrower) {
597 push(@messages, "<font color='red' size='+2'>Book: $barcode is not currently issued.</font>");
598 return (0, \@messages, 0,0);
600 # update issues, thereby returning book (should push this out into another subroutine
601 ($borrower) = getpatroninformation($env, $currentborrower, 0);
602 my $query = "update issues set returndate = now()
603 where (borrowernumber = '$borrower->{'borrowernumber'}')
604 and (itemnumber = '$iteminformation->{'itemnumber'}') and (returndate is null)";
605 my $sth = $dbh->prepare($query);
608 push(@messages, "Book has been returned.");
610 my $tbr = $env->{'branchcode'};
611 my ($transfered, $message, $item) = transferbook($tbr, $barcode);
613 push(@messages, "Book: as been transfered.");
616 if ($iteminformation->{'itemlost'}) {
617 updateitemlost($dbh, $iteminformation->{'itemnumber'});
618 # check for charge made for lost book
619 my $query = "select * from accountlines where (itemnumber = '$iteminformation->{'itemnumber'}')
620 and (accounttype='L' or accounttype='Rep') order by date desc";
621 my $sth = $dbh->prepare($query);
623 if (my $data = $sth->fetchrow_hashref) {
624 # writeoff this amount
626 my $amount = $data->{'amount'};
627 my $acctno = $data->{'accountno'};
629 if ($data->{'amountoutstanding'} == $amount) {
630 $offset = $data->{'amount'};
633 $offset = $amount - $data->{'amountoutstanding'};
634 $amountleft = $data->{'amountoutstanding'} - $amount;
636 my $uquery = "update accountlines
637 set accounttype = 'LR',amountoutstanding='0'
638 where (borrowernumber = '$data->{'borrowernumber'}')
639 and (itemnumber = '$iteminformation->{'itemnumber'}')
640 and (accountno = '$acctno') ";
641 my $usth = $dbh->prepare($uquery);
644 #check if any credit is left if so writeoff other accounts
645 my $nextaccntno = getnextacctno($env,$data->{'borrowernumber'},$dbh);
646 if ($amountleft < 0){
649 if ($amountleft > 0){
650 my $query = "select * from accountlines
651 where (borrowernumber = '$data->{'borrowernumber'}') and (amountoutstanding >0)
653 my $msth = $dbh->prepare($query);
655 # offset transactions
658 while (($accdata=$msth->fetchrow_hashref) and ($amountleft>0)){
659 if ($accdata->{'amountoutstanding'} < $amountleft) {
661 $amountleft = $amountleft - $accdata->{'amountoutstanding'};
663 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
666 my $thisacct = $accdata->{accountno};
667 my $updquery = "update accountlines set amountoutstanding= '$newamtos'
668 where (borrowernumber = '$data->{'borrowernumber'}') and (accountno='$thisacct')";
669 my $usth = $dbh->prepare($updquery);
672 $updquery = "insert into accountoffsets
673 (borrowernumber, accountno, offsetaccount, offsetamount)
675 ('$data->{'borrowernumber'}','$accdata->{'accountno'}','$nextaccntno','$newamtos')";
676 my $usth = $dbh->prepare($updquery);
682 if ($amountleft > 0){
685 my $desc="Book Returned ".$iteminformation->{'barcode'};
686 $uquery = "insert into accountlines
687 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
688 values ('$data->{'borrowernumber'}','$nextaccntno',now(),0-$amount,'$desc',
690 $usth = $dbh->prepare($uquery);
694 $uquery = "insert into accountoffsets
695 (borrowernumber, accountno, offsetaccount, offsetamount)
696 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
697 $usth = $dbh->prepare($uquery);
700 $uquery="update items set paidfor='' where itemnumber='$iteminformation->{'itemnumber'}'";
701 $usth = $dbh->prepare($uquery);
708 # check for overdue fine
709 my $query = "select * from accountlines where (borrowernumber='$borrower->{'borrowernumber'}')
710 and (itemnumber = '$iteminformation->{'itemnumber'}') and (accounttype='FU' or accounttype='O')";
711 $sth = $dbh->prepare($query);
713 # alter fine to show that the book has been returned
714 if (my $data = $sth->fetchrow_hashref) {
715 my $query = "update accountlines set accounttype='F'
716 where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber=$iteminformation->{'itemnumber'})
717 and (acccountno='$data->{'accountno'}')";
718 my $usth=$dbh->prepare($query);
724 my ($resfound, $resrec) = find_reserves($env, $dbh, $iteminformation->{'itemnumber'});
725 if ($resfound eq 'y') {
726 my ($borrower) = getpatroninformation($env,$resrec->{'borrowernumber'},0);
727 my ($branches) = getbranches();
728 my $branchname = $branches->{$resrec->{'branchcode'}}->{'branchname'};
729 push(@messages, "<b><font color=red>RESERVED</font></b> for collection by $borrower->{'firstname'} $borrower->{'surname'} ($borrower->{'cardnumber'}) at $branchname");
731 UpdateStats($env,$env->{'branchcode'},'return','0','',$iteminformation->{'itemnumber'});
733 return (1, \@messages, $iteminformation, $borrower);
739 # Original subroutine for Circ2.pm
741 my ($env, $patroninformation, $dbh) = @_;
742 my $amount = checkaccount($env, $patroninformation->{'borrowernumber'}, $dbh);
745 $flaginfo{'message'}= sprintf "Patron owes \$%.02f", $amount;
747 $flaginfo{'noissues'} = 1;
749 $flags{'CHARGES'} = \%flaginfo;
750 } elsif ($amount < 0){
752 $amount = $amount*-1;
753 $flaginfo{'message'} = sprintf "Patron has credit of \$%.02f", $amount;
754 $flags{'CHARGES'} = \%flaginfo;
756 if ($patroninformation->{'gonenoaddress'} == 1) {
758 $flaginfo{'message'} = 'Borrower has no valid address.';
759 $flaginfo{'noissues'} = 1;
760 $flags{'GNA'} = \%flaginfo;
762 if ($patroninformation->{'lost'} == 1) {
764 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
765 $flaginfo{'noissues'} = 1;
766 $flags{'LOST'} = \%flaginfo;
768 if ($patroninformation->{'debarred'} == 1) {
770 $flaginfo{'message'} = 'Borrower is Debarred.';
771 $flaginfo{'noissues'} = 1;
772 $flags{'DBARRED'} = \%flaginfo;
774 if ($patroninformation->{'borrowernotes'}) {
776 $flaginfo{'message'} = "$patroninformation->{'borrowernotes'}";
777 $flags{'NOTES'} = \%flaginfo;
779 my ($odues, $itemsoverdue) = checkoverdues($env, $patroninformation->{'borrowernumber'}, $dbh);
782 $flaginfo{'message'} = "Yes";
783 $flaginfo{'itemlist'} = $itemsoverdue;
784 foreach (sort {$a->{'date_due'} cmp $b->{'date_due'}} @$itemsoverdue) {
785 $flaginfo{'itemlisttext'}.="$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n";
787 $flags{'ODUES'} = \%flaginfo;
789 my ($nowaiting, $itemswaiting) = checkwaiting($env, $dbh, $patroninformation->{'borrowernumber'});
790 if ($nowaiting > 0) {
792 $flaginfo{'message'} = "Reserved items available";
793 $flaginfo{'itemlist'} = $itemswaiting;
794 $flaginfo{'itemfields'} = ['barcode', 'title', 'author', 'dewey', 'subclass', 'holdingbranch'];
795 $flags{'WAITING'} = \%flaginfo;
802 # From Main.pm, modified to return a list of overdueitems, in addition to a count
803 #checks whether a borrower has overdue items
804 my ($env,$bornum,$dbh)=@_;
805 my @datearr = localtime;
806 my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3];
809 my $query = "Select * from issues,biblio,biblioitems,items where items.biblioitemnumber=biblioitems.biblioitemnumber and items.biblionumber=biblio.biblionumber and issues.itemnumber=items.itemnumber and borrowernumber=$bornum and returndate is NULL and date_due < '$today'";
810 my $sth=$dbh->prepare($query);
812 while (my $data = $sth->fetchrow_hashref) {
813 push (@overdueitems, $data);
817 return ($count, \@overdueitems);
821 # Stolen from Returns.pm
822 my ($env, $dbh, $itemnumber) = @_;
823 my $brc = $env->{'branchcode'};
824 $brc = $dbh->quote($brc);
825 my $itm = $dbh->quote($itemnumber);
826 my $query = "update items set datelastseen = now(), holdingbranch = $brc where (itemnumber = $itm)";
827 my $sth = $dbh->prepare($query);
832 sub currentborrower {
833 # Original subroutine for Circ2.pm
834 my ($env, $itemnumber, $dbh) = @_;
835 my $q_itemnumber = $dbh->quote($itemnumber);
836 my $sth=$dbh->prepare("select borrowers.borrowernumber from
837 issues,borrowers where issues.itemnumber=$q_itemnumber and
838 issues.borrowernumber=borrowers.borrowernumber and issues.returndate is
841 my ($previousborrower) = $sth->fetchrow;
842 return($previousborrower);
846 # Stolen from Main.pm
847 # Check for reserves for biblio
848 my ($env,$dbh,$itemnum)=@_;
850 my $query = "select * from reserves,items
851 where (items.itemnumber = '$itemnum')
852 and (reserves.cancellationdate is NULL)
853 and (items.biblionumber = reserves.biblionumber)
854 and ((reserves.found = 'W')
855 or (reserves.found is null))
857 my $sth = $dbh->prepare($query);
860 my $data=$sth->fetchrow_hashref;
861 while ($data && $resbor eq '') {
863 my $const = $data->{'constrainttype'};
865 $resbor = $data->{'borrowernumber'};
868 my $cquery = "select * from reserveconstraints,items
869 where (borrowernumber='$data->{'borrowernumber'}')
870 and reservedate='$data->{'reservedate'}'
871 and reserveconstraints.biblionumber='$data->{'biblionumber'}'
872 and (items.itemnumber=$itemnum and
873 items.biblioitemnumber = reserveconstraints.biblioitemnumber)";
874 my $csth = $dbh->prepare($cquery);
876 if (my $cdata=$csth->fetchrow_hashref) {$found = 1;}
878 if ($found eq 1) {$resbor = $data->{'borrowernumber'};}
880 if ($found eq 0) {$resbor = $data->{'borrowernumber'};}
884 $data=$sth->fetchrow_hashref;
887 return ($resbor,$resrec);
891 # New subroutine for Circ2.pm
892 my ($env, $borrower) = @_;
896 my $borrowernumber=$borrower->{'borrowernumber'};
898 if ($env->{'todaysissues'}) {
899 my @datearr = localtime(time());
900 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
901 $crit=" and issues.timestamp like '$today%' ";
903 if ($env->{'nottodaysissues'}) {
904 my @datearr = localtime(time());
905 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
906 $crit=" and !(issues.timestamp like '$today%') ";
908 my $select="select * from issues,items,biblioitems,biblio where
909 borrowernumber='$borrowernumber' and issues.itemnumber=items.itemnumber and
910 items.biblionumber=biblio.biblionumber and
911 items.biblioitemnumber=biblioitems.biblioitemnumber and returndate is null
912 $crit order by issues.timestamp desc";
914 my $sth=$dbh->prepare($select);
916 while (my $data = $sth->fetchrow_hashref) {
917 $data->{'dewey'}=~s/0*$//;
918 ($data->{'dewey'} == 0) && ($data->{'dewey'}='');
919 my @datearr = localtime(time());
920 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]
921 +1)).sprintf ("%0.2d", $datearr[3]);
922 my $datedue=$data->{'date_due'};
924 if ($datedue < $todaysdate) {
925 $data->{'overdue'}=1;
927 my $itemnumber=$data->{'itemnumber'};
928 $currentissues{$counter}=$data;
933 return(\%currentissues);
938 # check for reserves waiting
939 my ($env,$dbh,$bornum)=@_;
941 my $query = "select * from reserves
942 where (borrowernumber = '$bornum')
943 and (reserves.found='W') and cancellationdate is NULL";
944 my $sth = $dbh->prepare($query);
947 if (my $data=$sth->fetchrow_hashref) {
948 @itemswaiting[$cnt] =$data;
952 return ($cnt,\@itemswaiting);
957 # Stolen from Accounts.pm
958 #take borrower number
959 #check accounts and list amounts owing
960 my ($env,$bornumber,$dbh,$date)=@_;
961 my $select="Select sum(amountoutstanding) from accountlines where
962 borrowernumber=$bornumber and amountoutstanding<>0";
964 $select.=" and date < '$date'";
967 my $sth=$dbh->prepare($select);
970 while (my $data=$sth->fetchrow_hashref){
971 $total=$total+$data->{'sum(amountoutstanding)'};
974 # output(1,2,"borrower owes $total");
976 # # output(1,2,"borrower owes $total");
978 # reconcileaccount($env,$dbh,$bornumber,$total);
986 # Stolen from Renewals.pm
987 # check renewal status
988 my ($env,$dbh,$bornum,$itemno)=@_;
991 my $q1 = "select * from issues
992 where (borrowernumber = '$bornum')
993 and (itemnumber = '$itemno')
994 and returndate is null";
995 my $sth1 = $dbh->prepare($q1);
997 if (my $data1 = $sth1->fetchrow_hashref) {
998 my $q2 = "select renewalsallowed from items,biblioitems,itemtypes
999 where (items.itemnumber = '$itemno')
1000 and (items.biblioitemnumber = biblioitems.biblioitemnumber)
1001 and (biblioitems.itemtype = itemtypes.itemtype)";
1002 my $sth2 = $dbh->prepare($q2);
1004 if (my $data2=$sth2->fetchrow_hashref) {
1005 $renews = $data2->{'renewalsallowed'};
1007 if ($renews > $data1->{'renewals'}) {
1017 # Stolen from Renewals.pm
1018 # mark book as renewed
1019 my ($env,$dbh,$bornum,$itemno,$datedue)=@_;
1020 $datedue=$env->{'datedue'};
1021 if ($datedue eq "" ) {
1023 my $query= "Select * from biblioitems,items,itemtypes
1024 where (items.itemnumber = '$itemno')
1025 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
1026 and (biblioitems.itemtype = itemtypes.itemtype)";
1027 my $sth=$dbh->prepare($query);
1029 if (my $data=$sth->fetchrow_hashref) {
1030 $loanlength = $data->{'loanlength'}
1034 my $datedu = time + ($loanlength * 86400);
1035 my @datearr = localtime($datedu);
1036 $datedue = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
1038 my @date = split("-",$datedue);
1039 my $odatedue = (@date[2]+0)."-".(@date[1]+0)."-".@date[0];
1040 my $issquery = "select * from issues where borrowernumber='$bornum' and
1041 itemnumber='$itemno' and returndate is null";
1042 my $sth=$dbh->prepare($issquery);
1044 my $issuedata=$sth->fetchrow_hashref;
1046 my $renews = $issuedata->{'renewals'} +1;
1047 my $updquery = "update issues
1048 set date_due = '$datedue', renewals = '$renews'
1049 where borrowernumber='$bornum' and
1050 itemnumber='$itemno' and returndate is null";
1051 my $sth=$dbh->prepare($updquery);
1059 # Stolen from Issues.pm
1060 # calculate charges due
1061 my ($env, $dbh, $itemno, $bornum)=@_;
1064 my $q1 = "select itemtypes.itemtype,rentalcharge from items,biblioitems,itemtypes where (items.itemnumber ='$itemno') and (biblioitems.biblioitemnumber = items.biblioitemnumber) and (biblioitems.itemtype = itemtypes.itemtype)";
1065 my $sth1= $dbh->prepare($q1);
1067 if (my $data1=$sth1->fetchrow_hashref) {
1068 $item_type = $data1->{'itemtype'};
1069 $charge = $data1->{'rentalcharge'};
1070 my $q2 = "select rentaldiscount from borrowers,categoryitem
1071 where (borrowers.borrowernumber = '$bornum')
1072 and (borrowers.categorycode = categoryitem.categorycode)
1073 and (categoryitem.itemtype = '$item_type')";
1074 my $sth2=$dbh->prepare($q2);
1076 if (my $data2=$sth2->fetchrow_hashref) {
1077 my $discount = $data2->{'rentaldiscount'};
1078 $charge = ($charge *(100 - $discount)) / 100;
1087 #Stolen from Issues.pm
1088 my ($env,$dbh,$itemno,$bornum,$charge) = @_;
1089 my $nextaccntno = getnextacctno($env,$bornum,$dbh);
1090 my $query = "insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) values ($bornum,$itemno,$nextaccntno,now(),$charge,'Rental','Rent',$charge)";
1091 my $sth = $dbh->prepare($query);
1098 # Stolen from Accounts.pm
1099 my ($env,$bornumber,$dbh)=@_;
1100 my $nextaccntno = 1;
1101 my $query = "select * from accountlines where (borrowernumber = '$bornumber') order by accountno desc";
1102 my $sth = $dbh->prepare($query);
1104 if (my $accdata=$sth->fetchrow_hashref){
1105 $nextaccntno = $accdata->{'accountno'} + 1;
1108 return($nextaccntno);
1112 # Stolen from Returns.pm
1113 my ($env,$dbh,$itemno) = @_;
1114 my ($itemdata) = getiteminformation($env,$itemno,0);
1115 my $query = "select * from reserves where
1116 ((reserves.found = 'W')
1117 or (reserves.found is null))
1118 and biblionumber = $itemdata->{'biblionumber'} and cancellationdate is NULL
1119 order by priority,reservedate ";
1120 my $sth = $dbh->prepare($query);
1126 while (($resrec=$sth->fetchrow_hashref) && ($resfound eq "n")) {
1128 if ($resrec->{'found'} eq "W") {
1129 if ($resrec->{'itemnumber'} eq $itemno) {
1133 if ($resrec->{'constrainttype'} eq "a") {
1136 my $conquery = "select * from reserveconstraints where borrowernumber
1137 = $resrec->{'borrowernumber'} and reservedate = '$resrec->{'reservedate'}' and biblionumber = $resrec->{'biblionumber'} and biblioitemnumber = $itemdata->{'biblioitemnumber'}";
1138 my $consth = $dbh->prepare($conquery);
1140 if (my $conrec=$consth->fetchrow_hashref) {
1141 if ($resrec->{'constrainttype'} eq "o") {
1145 if ($resrec->{'constrainttype'} eq "e") {
1152 if ($resfound eq "y") {
1153 my $updquery = "update reserves
1154 set found = 'W',itemnumber='$itemno'
1155 where borrowernumber = $resrec->{'borrowernumber'}
1156 and reservedate = '$resrec->{'reservedate'}'
1157 and biblionumber = $resrec->{'biblionumber'}";
1158 my $updsth = $dbh->prepare($updquery);
1161 my $itbr = $resrec->{'branchcode'};
1162 if ($resrec->{'branchcode'} ne $env->{'branchcode'}) {
1163 my $updquery = "update items
1164 set holdingbranch = 'TR'
1165 where itemnumber = $itemno";
1166 my $updsth = $dbh->prepare($updquery);
1173 return ($resfound,$lastrec);
1176 END { } # module clean-up code here (global destructor)