1 package C4::Circulation::Circ2;
3 #package to deal with Returns
4 #written 3/11/99 by olwen@katipo.co.nz
11 #use C4::InterfaceCDK;
12 #use C4::Circulation::Main;
14 #use C4::Circulation::Renewals;
20 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
22 # set the version for version checking
26 @EXPORT = qw(&getbranches &getprinters &getpatroninformation ¤tissues &getiteminformation &findborrower &issuebook &returnbook
27 &find_reserves &transferbook);
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;
68 my $sth=$dbh->prepare("select * from branches");
70 while (my $branch=$sth->fetchrow_hashref) {
71 # (next) if ($branch->{'branchcode'} eq 'TR');
72 $branches{$branch->{'branchcode'}}=$branch;
83 my $sth=$dbh->prepare("select * from printers");
85 while (my $printer=$sth->fetchrow_hashref) {
86 $printers{$printer->{'printqueue'}}=$printer;
94 sub getpatroninformation {
96 my ($env, $borrowernumber,$cardnumber) = @_;
99 open O, ">>/root/tkcirc.out";
100 print O "Looking up patron $borrowernumber / $cardnumber\n";
101 if ($borrowernumber) {
102 $sth=$dbh->prepare("select * from borrowers where borrowernumber=$borrowernumber");
103 } elsif ($cardnumber) {
104 $sth=$dbh->prepare("select * from borrowers where cardnumber=$cardnumber");
106 # error condition. This subroutine must be called with either a
107 # borrowernumber or a card number.
108 $env->{'apierror'}="invalid borrower information passed to getpatroninformation subroutine";
112 my $borrower=$sth->fetchrow_hashref;
113 my $flags=patronflags($env, $borrower, $dbh);
116 print O "$borrower->{'surname'} <---\n";
118 $borrower->{'flags'}=$flags;
119 return($borrower, $flags);
126 sub getiteminformation {
127 # returns a hash of item information given either the itemnumber or the barcode
128 my ($env, $itemnumber, $barcode) = @_;
132 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.itemnumber=$itemnumber and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
134 my $q_barcode=$dbh->quote($barcode);
135 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.barcode=$q_barcode and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
137 $env->{'apierror'}="getiteminformation() subroutine must be called with either an itemnumber or a barcode";
142 my $iteminformation=$sth->fetchrow_hashref;
144 if ($iteminformation) {
145 $sth=$dbh->prepare("select date_due from issues where itemnumber=$iteminformation->{'itemnumber'} and isnull(returndate)");
147 my ($date_due) = $sth->fetchrow;
148 $iteminformation->{'date_due'}=$date_due;
150 #$iteminformation->{'dewey'}=~s/0*$//;
151 ($iteminformation->{'dewey'} == 0) && ($iteminformation->{'dewey'}='');
152 $sth=$dbh->prepare("select * from itemtypes where itemtype='$iteminformation->{'itemtype'}'");
154 my $itemtype=$sth->fetchrow_hashref;
155 $iteminformation->{'loanlength'}=$itemtype->{'loanlength'};
159 return($iteminformation);
163 # returns an array of borrower hash references, given a cardnumber or a partial
165 my ($env, $key) = @_;
168 my $q_key=$dbh->quote($key);
169 my $sth=$dbh->prepare("select * from borrowers where cardnumber=$q_key");
172 my ($borrower)=$sth->fetchrow_hashref;
173 push (@borrowers, $borrower);
175 $q_key=$dbh->quote("$key%");
177 $sth=$dbh->prepare("select * from borrowers where surname like $q_key");
179 while (my $borrower = $sth->fetchrow_hashref) {
180 push (@borrowers, $borrower);
190 my ($env, $iteminformation, $barcode) = @_;
193 #new entry in branchtransfers....
194 my $sth = $dbh->prepare("insert into branchtransfers (itemnumber, frombranch, datearrived, tobranch) values($iteminformation->{'itemnumber'}, '$env->{'frbranchcd'}', now(), '$env->{'tobranchcd'}')");
195 $sth->execute || return (0,"database error: $sth->errstr");
197 #update holdingbranch in items .....
198 $sth = $dbh->prepare("update items set holdingbranch='$env->{'tobranchcd'}' where items.itemnumber=$iteminformation->{'itemnumber'}");
199 $sth->execute || return (0,"database error: $sth->errstr");
203 return (1, $messages);
208 my ($env, $patroninformation, $barcode, $responses, $date) = @_;
210 my $iteminformation=getiteminformation($env, 0, $barcode);
212 my ($rejected,$question,$defaultanswer,$questionnumber, $noissue);
214 if ($patroninformation->{'gonenoaddress'}) {
215 $rejected="Patron is gone, with no known address.";
218 if ($patroninformation->{'lost'}) {
219 $rejected="Patron's card has been reported lost.";
222 if ($patroninformation->{'debarred'}) {
223 $rejected="Patron is Debarred";
226 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh,$date);
227 if ($amount>5 && $patroninformation->{'categorycode'} ne 'L' &&
228 $patroninformation->{'categorycode'} ne 'W' &&
229 $patroninformation->{'categorycode'} ne 'I'
230 && $patroninformation->{'categorycode'} ne 'B' &&
231 $patroninformation->{'categorycode'} ne 'P') {
232 $rejected=sprintf "Patron owes \$%.02f.", $amount;
235 unless ($iteminformation) {
236 $rejected="$barcode is not a valid barcode.";
239 if ($iteminformation->{'notforloan'} == 1) {
240 $rejected="Item not for loan.";
243 if ($iteminformation->{'wthdrawn'} == 1) {
244 $rejected="Item withdrawn.";
247 if ($iteminformation->{'restricted'} == 1) {
248 $rejected="Restricted item.";
251 if ($iteminformation->{'itemtype'} eq 'REF') {
252 $rejected="Reference item: Not for loan.";
255 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
256 if ($currentborrower eq $patroninformation->{'borrowernumber'}) {
257 # Already issued to current borrower
258 my ($renewstatus) = renewstatus($env,$dbh,$patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
259 if ($renewstatus == 0) {
260 $rejected="No more renewals allowed for this item.";
263 if ($responses->{4} eq '') {
265 $question="Book is issued to this borrower.\nRenew?";
268 } elsif ($responses->{4} eq 'Y') {
269 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
271 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
272 $iteminformation->{'charge'}=$charge;
274 &UpdateStats($env,$env->{'branchcode'},'renew',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
275 renewbook($env,$dbh, $patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
282 } elsif ($currentborrower ne '') {
283 my ($currborrower, $cbflags)=getpatroninformation($env,$currentborrower,0);
284 if ($responses->{1} eq '') {
286 $question="Issued to $currborrower->{'firstname'} $currborrower->{'surname'} ($currborrower->{'cardnumber'}).\nMark as returned?";
289 } elsif ($responses->{1} eq 'Y') {
290 returnbook($env,$iteminformation->{'barcode'});
297 my ($resbor, $resrec) = checkreserve($env, $dbh, $iteminformation->{'itemnumber'});
299 if ($resbor eq $patroninformation->{'borrowernumber'}) {
300 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
301 my $rsth = $dbh->prepare($rquery);
304 } elsif ($resbor ne "") {
305 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
306 if ($responses->{2} eq '') {
308 $question="Reserved for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) since $resrec->{'reservedate'}\nAllow issue?";
311 } elsif ($responses->{2} eq 'N') {
312 #printreserve($env, $resrec, $resborrower, $iteminformation);
316 if ($responses->{3} eq '') {
318 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
321 } elsif ($responses->{3} eq 'Y') {
322 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
323 my $rsth = $dbh->prepare($rquery);
331 unless (($question) || ($rejected) || ($noissue)) {
333 if ($iteminformation->{'loanlength'}) {
334 $loanlength=$iteminformation->{'loanlength'};
337 my $datedue=time+($loanlength)*86400;
338 my @datearr = localtime($datedue);
339 $dateduef = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
340 if ($env->{'datedue'}) {
341 $dateduef=$env->{'datedue'};
343 $dateduef=~ s/2001\-4\-25/2001\-4\-26/;
344 my $sth=$dbh->prepare("insert into issues (borrowernumber, itemnumber, date_due, branchcode) values ($patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'}, '$dateduef', '$env->{'branchcode'}')");
347 $iteminformation->{'issues'}++;
348 $sth=$dbh->prepare("update items set issues=$iteminformation->{'issues'} where itemnumber=$iteminformation->{'itemnumber'}");
351 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
353 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
354 $iteminformation->{'charge'}=$charge;
356 &UpdateStats($env,$env->{'branchcode'},'issue',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
359 if ($iteminformation->{'charge'}) {
360 $message=sprintf "Rental charge of \$%.02f applies.", $iteminformation->{'charge'};
363 return ($iteminformation, $dateduef, $rejected, $question, $questionnumber, $defaultanswer, $message);
367 my ($dbh,$itemno)=@_;
368 my $query="update items set itemlost=0 where itemnumber=$itemno";
369 my $sth=$dbh->prepare($query);
375 my ($env, $barcode) = @_;
376 my ($messages, $overduecharge);
378 my ($iteminformation) = getiteminformation($env, 0, $barcode);
380 if ($iteminformation) {
381 my $sth=$dbh->prepare("select * from issues where (itemnumber='$iteminformation->{'itemnumber'}') and (returndate is null)");
383 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
384 updatelastseen($env,$dbh,$iteminformation->{'itemnumber'});
385 updateitemlost($dbh,$iteminformation->{'itemnumber'});
386 if ($currentborrower) {
387 ($borrower)=getpatroninformation($env,$currentborrower,0);
388 my @datearr = localtime(time);
389 my $dateret = (1900+$datearr[5])."-".$datearr[4]."-".$datearr[3];
390 my $query = "update issues set returndate = now(), branchcode ='$env->{'branchcode'}' where (borrowernumber = $borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (returndate is null)";
391 my $sth = $dbh->prepare($query);
396 # check for overdue fine
399 $sth=$dbh->prepare("select * from accountlines where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (accounttype='FU' or accounttype='O')");
401 # alter fine to show that the book has been returned
402 if (my $data = $sth->fetchrow_hashref) {
403 my $usth=$dbh->prepare("update accountlines set accounttype='F' where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber=$iteminformation->{'itemnumber'}) and (acccountno='$data->{'accountno'}')");
406 $overduecharge=$data->{'amountoutstanding'};
410 if ($iteminformation->{'itemlost'} eq '1'){
411 # check for charge made for lost book
412 my $query="select * from accountlines where (itemnumber =
413 $iteminformation->{'itemnumber'}) and (accounttype='L' or accounttype='Rep')
416 $sth=$dbh->prepare($query);
418 if (my $data = $sth->fetchrow_hashref) {
419 # writeoff this amount
421 my $amount = $data->{'amount'};
422 my $acctno = $data->{'accountno'};
425 if ($data->{'amountoutstanding'} == $amount) {
426 $offset = $data->{'amount'};
429 $offset = $amount - $data->{'amountoutstanding'};
430 $amountleft = $data->{'amountoutstanding'} - $amount;
432 my $uquery = "update accountlines
433 set accounttype = 'LR',amountoutstanding='0'
434 where (borrowernumber = $data->{'borrowernumber'})
435 and (itemnumber = $iteminformation->{'itemnumber'})
436 and (accountno = '$acctno') ";
438 my $usth = $dbh->prepare($uquery);
441 #check if any credit is left if so writeoff other accounts]
442 my $nextaccntno = getnextacctno($env,$data->{'borrowernumber'},$dbh);
443 if ($amountleft < 0){
446 if ($amountleft > 0){
448 my $query = "select * from accountlines
449 where (borrowernumber = '$data->{'borrowernumber'}') and (amountoutstanding >0)
451 my $sth = $dbh->prepare($query);
453 # offset transactions
456 while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
457 if ($accdata->{'amountoutstanding'} < $amountleft) {
459 $amountleft = $amountleft - $accdata->{'amountoutstanding'};
461 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
464 my $thisacct = $accdata->{accountno};
465 my $updquery = "update accountlines set amountoutstanding= '$newamtos'
466 where (borrowernumber = '$data->{'borrowernumber'}') and (accountno='$thisacct')";
467 my $usth = $dbh->prepare($updquery);
470 $updquery = "insert into accountoffsets
471 (borrowernumber, accountno, offsetaccount, offsetamount)
473 ($data->{'borrowernumber'},$accdata->{'accountno'},$nextaccntno,$newamtos)";
474 my $usth = $dbh->prepare($updquery);
479 if ($amountleft > 0){
483 my $desc="Book Returned ".$iteminformation->{'barcode'};
484 $uquery = "insert into accountlines
485 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
486 values ($data->{'borrowernumber'},$nextaccntno,now(),0-$amount,'$desc',
488 $usth = $dbh->prepare($uquery);
492 $uquery = "insert into accountoffsets
493 (borrowernumber, accountno, offsetaccount, offsetamount)
494 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
495 $usth = $dbh->prepare($uquery);
498 $uquery="update items set paidfor='' where itemnumber=$iteminformation->{'itemnumber'}";
499 $usth = $dbh->prepare($uquery);
505 my ($resfound,$resrec) = find_reserves($env, $dbh, $iteminformation->{'itemnumber'});
506 if ($resfound eq 'y') {
507 my ($borrower) = getpatroninformation($env,$resrec->{'borrowernumber'},0);
508 #printreserve($env,$resrec,$resborrower,$itemrec);
509 my ($branches) = getbranches();
510 my $branchname=$branches->{$resrec->{'branchcode'}}->{'branchname'};
511 push (@$messages, "<b><font color=red>RESERVED</font></b> for collection by $borrower->{'firstname'} $borrower->{'surname'} ($borrower->{'cardnumber'}) at $branchname");
513 UpdateStats($env,$env->{'branchcode'},'return','0','',$iteminformation->{'itemnumber'});
516 return ($iteminformation, $borrower, $messages, $overduecharge);
521 # Original subroutine for Circ2.pm
523 my ($env,$patroninformation,$dbh) = @_;
524 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh);
527 $flaginfo{'message'}=sprintf "Patron owes \$%.02f", $amount;
529 $flaginfo{'noissues'}=1;
531 $flags{'CHARGES'}=\%flaginfo;
532 } elsif ($amount < 0){
535 $flaginfo{'message'}=sprintf "Patron has credit of \$%.02f", $amount;
536 $flags{'CHARGES'}=\%flaginfo;
538 if ($patroninformation->{'gonenoaddress'} == 1) {
540 $flaginfo{'message'}='Borrower has no valid address.';
541 $flaginfo{'noissues'}=1;
542 $flags{'GNA'}=\%flaginfo;
544 if ($patroninformation->{'lost'} == 1) {
546 $flaginfo{'message'}='Borrower\'s card reported lost.';
547 $flaginfo{'noissues'}=1;
548 $flags{'LOST'}=\%flaginfo;
550 if ($patroninformation->{'debarred'} == 1) {
552 $flaginfo{'message'}='Borrower is Debarred.';
553 $flaginfo{'noissues'}=1;
554 $flags{'DBARRED'}=\%flaginfo;
556 if ($patroninformation->{'borrowernotes'}) {
558 $flaginfo{'message'}="$patroninformation->{'borrowernotes'}";
559 $flags{'NOTES'}=\%flaginfo;
561 my ($odues, $itemsoverdue) = checkoverdues($env,$patroninformation->{'borrowernumber'},$dbh);
564 $flaginfo{'message'}="Yes";
565 $flaginfo{'itemlist'}=$itemsoverdue;
566 foreach (sort {$a->{'date_due'} cmp $b->{'date_due'}} @$itemsoverdue) {
567 $flaginfo{'itemlisttext'}.="$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n";
569 $flags{'ODUES'}=\%flaginfo;
571 my ($nowaiting,$itemswaiting) = checkwaiting($env,$dbh,$patroninformation->{'borrowernumber'});
574 $flaginfo{'message'}="Reserved items available";
575 $flaginfo{'itemlist'}=$itemswaiting;
576 $flaginfo{'itemfields'}=['barcode', 'title', 'author', 'dewey', 'subclass', 'holdingbranch'];
577 $flags{'WAITING'}=\%flaginfo;
586 # From Main.pm, modified to return a list of overdueitems, in addition to a count
587 #checks whether a borrower has overdue items
588 my ($env,$bornum,$dbh)=@_;
589 my @datearr = localtime;
590 my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3];
593 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'";
594 my $sth=$dbh->prepare($query);
596 while (my $data = $sth->fetchrow_hashref) {
597 push (@overdueitems, $data);
601 return ($count, \@overdueitems);
605 # Stolen from Returns.pm
606 my ($env,$dbh,$itemnumber)= @_;
607 my $br = $env->{'branchcode'};
608 my $query = "update items
609 set datelastseen = now(), holdingbranch = '$br'
610 where (itemnumber = '$itemnumber')";
611 my $sth = $dbh->prepare($query);
616 sub currentborrower {
617 # Original subroutine for Circ2.pm
618 my ($env, $itemnumber, $dbh) = @_;
619 my $q_itemnumber=$dbh->quote($itemnumber);
620 my $sth=$dbh->prepare("select borrowers.borrowernumber from
621 issues,borrowers where issues.itemnumber=$q_itemnumber and
622 issues.borrowernumber=borrowers.borrowernumber and issues.returndate is
625 my ($previousborrower)=$sth->fetchrow;
626 return($previousborrower);
630 # Stolen from Main.pm
631 # Check for reserves for biblio
632 my ($env,$dbh,$itemnum)=@_;
634 my $query = "select * from reserves,items
635 where (items.itemnumber = '$itemnum')
636 and (reserves.cancellationdate is NULL)
637 and (items.biblionumber = reserves.biblionumber)
638 and ((reserves.found = 'W')
639 or (reserves.found is null))
641 my $sth = $dbh->prepare($query);
644 my $data=$sth->fetchrow_hashref;
645 while ($data && $resbor eq '') {
647 my $const = $data->{'constrainttype'};
649 $resbor = $data->{'borrowernumber'};
652 my $cquery = "select * from reserveconstraints,items
653 where (borrowernumber='$data->{'borrowernumber'}')
654 and reservedate='$data->{'reservedate'}'
655 and reserveconstraints.biblionumber='$data->{'biblionumber'}'
656 and (items.itemnumber=$itemnum and
657 items.biblioitemnumber = reserveconstraints.biblioitemnumber)";
658 my $csth = $dbh->prepare($cquery);
660 if (my $cdata=$csth->fetchrow_hashref) {$found = 1;}
662 if ($found eq 1) {$resbor = $data->{'borrowernumber'};}
664 if ($found eq 0) {$resbor = $data->{'borrowernumber'};}
668 $data=$sth->fetchrow_hashref;
671 return ($resbor,$resrec);
675 # New subroutine for Circ2.pm
676 my ($env, $borrower) = @_;
680 my $borrowernumber=$borrower->{'borrowernumber'};
682 if ($env->{'todaysissues'}) {
683 my @datearr = localtime(time());
684 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
685 $crit=" and issues.timestamp like '$today%' ";
687 if ($env->{'nottodaysissues'}) {
688 my @datearr = localtime(time());
689 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
690 $crit=" and !(issues.timestamp like '$today%') ";
692 my $select="select * from issues,items,biblioitems,biblio where
693 borrowernumber='$borrowernumber' and issues.itemnumber=items.itemnumber and
694 items.biblionumber=biblio.biblionumber and
695 items.biblioitemnumber=biblioitems.biblioitemnumber and returndate is null
696 $crit order by issues.timestamp desc";
698 my $sth=$dbh->prepare($select);
700 while (my $data = $sth->fetchrow_hashref) {
701 $data->{'dewey'}=~s/0*$//;
702 ($data->{'dewey'} == 0) && ($data->{'dewey'}='');
703 my @datearr = localtime(time());
704 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]
705 +1)).sprintf ("%0.2d", $datearr[3]);
706 my $datedue=$data->{'date_due'};
708 if ($datedue < $todaysdate) {
709 $data->{'overdue'}=1;
711 my $itemnumber=$data->{'itemnumber'};
712 $currentissues{$counter}=$data;
717 return(\%currentissues);
722 # check for reserves waiting
723 my ($env,$dbh,$bornum)=@_;
725 my $query = "select * from reserves
726 where (borrowernumber = '$bornum')
727 and (reserves.found='W') and cancellationdate is NULL";
728 my $sth = $dbh->prepare($query);
731 if (my $data=$sth->fetchrow_hashref) {
732 @itemswaiting[$cnt] =$data;
736 return ($cnt,\@itemswaiting);
741 # Stolen from Accounts.pm
742 #take borrower number
743 #check accounts and list amounts owing
744 my ($env,$bornumber,$dbh,$date)=@_;
745 my $select="Select sum(amountoutstanding) from accountlines where
746 borrowernumber=$bornumber and amountoutstanding<>0";
748 $select.=" and date < '$date'";
751 my $sth=$dbh->prepare($select);
754 while (my $data=$sth->fetchrow_hashref){
755 $total=$total+$data->{'sum(amountoutstanding)'};
758 # output(1,2,"borrower owes $total");
760 # # output(1,2,"borrower owes $total");
762 # reconcileaccount($env,$dbh,$bornumber,$total);
770 # Stolen from Renewals.pm
771 # check renewal status
772 my ($env,$dbh,$bornum,$itemno)=@_;
775 my $q1 = "select * from issues
776 where (borrowernumber = '$bornum')
777 and (itemnumber = '$itemno')
778 and returndate is null";
779 my $sth1 = $dbh->prepare($q1);
781 if (my $data1 = $sth1->fetchrow_hashref) {
782 my $q2 = "select renewalsallowed from items,biblioitems,itemtypes
783 where (items.itemnumber = '$itemno')
784 and (items.biblioitemnumber = biblioitems.biblioitemnumber)
785 and (biblioitems.itemtype = itemtypes.itemtype)";
786 my $sth2 = $dbh->prepare($q2);
788 if (my $data2=$sth2->fetchrow_hashref) {
789 $renews = $data2->{'renewalsallowed'};
791 if ($renews > $data1->{'renewals'}) {
801 # Stolen from Renewals.pm
802 # mark book as renewed
803 my ($env,$dbh,$bornum,$itemno,$datedue)=@_;
804 $datedue=$env->{'datedue'};
805 if ($datedue eq "" ) {
807 my $query= "Select * from biblioitems,items,itemtypes
808 where (items.itemnumber = '$itemno')
809 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
810 and (biblioitems.itemtype = itemtypes.itemtype)";
811 my $sth=$dbh->prepare($query);
813 if (my $data=$sth->fetchrow_hashref) {
814 $loanlength = $data->{'loanlength'}
818 my $datedu = time + ($loanlength * 86400);
819 my @datearr = localtime($datedu);
820 $datedue = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
822 my @date = split("-",$datedue);
823 my $odatedue = (@date[2]+0)."-".(@date[1]+0)."-".@date[0];
824 my $issquery = "select * from issues where borrowernumber='$bornum' and
825 itemnumber='$itemno' and returndate is null";
826 my $sth=$dbh->prepare($issquery);
828 my $issuedata=$sth->fetchrow_hashref;
830 my $renews = $issuedata->{'renewals'} +1;
831 my $updquery = "update issues
832 set date_due = '$datedue', renewals = '$renews'
833 where borrowernumber='$bornum' and
834 itemnumber='$itemno' and returndate is null";
835 my $sth=$dbh->prepare($updquery);
843 # Stolen from Issues.pm
844 # calculate charges due
845 my ($env, $dbh, $itemno, $bornum)=@_;
848 my $q1 = "select itemtypes.itemtype,rentalcharge from items,biblioitems,itemtypes where (items.itemnumber ='$itemno') and (biblioitems.biblioitemnumber = items.biblioitemnumber) and (biblioitems.itemtype = itemtypes.itemtype)";
849 my $sth1= $dbh->prepare($q1);
851 if (my $data1=$sth1->fetchrow_hashref) {
852 $item_type = $data1->{'itemtype'};
853 $charge = $data1->{'rentalcharge'};
854 my $q2 = "select rentaldiscount from borrowers,categoryitem
855 where (borrowers.borrowernumber = '$bornum')
856 and (borrowers.categorycode = categoryitem.categorycode)
857 and (categoryitem.itemtype = '$item_type')";
858 my $sth2=$dbh->prepare($q2);
860 if (my $data2=$sth2->fetchrow_hashref) {
861 my $discount = $data2->{'rentaldiscount'};
862 $charge = ($charge *(100 - $discount)) / 100;
871 #Stolen from Issues.pm
872 my ($env,$dbh,$itemno,$bornum,$charge) = @_;
873 my $nextaccntno = getnextacctno($env,$bornum,$dbh);
874 my $query = "insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) values ($bornum,$itemno,$nextaccntno,now(),$charge,'Rental','Rent',$charge)";
875 my $sth = $dbh->prepare($query);
882 # Stolen from Accounts.pm
883 my ($env,$bornumber,$dbh)=@_;
885 my $query = "select * from accountlines where (borrowernumber = '$bornumber') order by accountno desc";
886 my $sth = $dbh->prepare($query);
888 if (my $accdata=$sth->fetchrow_hashref){
889 $nextaccntno = $accdata->{'accountno'} + 1;
892 return($nextaccntno);
896 # Stolen from Returns.pm
897 my ($env,$dbh,$itemno) = @_;
898 my ($itemdata) = getiteminformation($env,$itemno,0);
899 my $query = "select * from reserves where
900 ((reserves.found = 'W')
901 or (reserves.found is null))
902 and biblionumber = $itemdata->{'biblionumber'} and cancellationdate is NULL
903 order by priority,reservedate ";
904 my $sth = $dbh->prepare($query);
910 while (($resrec=$sth->fetchrow_hashref) && ($resfound eq "n")) {
912 if ($resrec->{'found'} eq "W") {
913 if ($resrec->{'itemnumber'} eq $itemno) {
917 if ($resrec->{'constrainttype'} eq "a") {
920 my $conquery = "select * from reserveconstraints where borrowernumber
921 = $resrec->{'borrowernumber'} and reservedate = '$resrec->{'reservedate'}' and biblionumber = $resrec->{'biblionumber'} and biblioitemnumber = $itemdata->{'biblioitemnumber'}";
922 my $consth = $dbh->prepare($conquery);
924 if (my $conrec=$consth->fetchrow_hashref) {
925 if ($resrec->{'constrainttype'} eq "o") {
929 if ($resrec->{'constrainttype'} eq "e") {
936 if ($resfound eq "y") {
937 my $updquery = "update reserves
938 set found = 'W',itemnumber='$itemno'
939 where borrowernumber = $resrec->{'borrowernumber'}
940 and reservedate = '$resrec->{'reservedate'}'
941 and biblionumber = $resrec->{'biblionumber'}";
942 my $updsth = $dbh->prepare($updquery);
945 my $itbr = $resrec->{'branchcode'};
946 if ($resrec->{'branchcode'} ne $env->{'branchcode'}) {
947 my $updquery = "update items
948 set holdingbranch = 'TR'
949 where itemnumber = $itemno";
950 my $updsth = $dbh->prepare($updquery);
957 return ($resfound,$lastrec);
960 END { } # module clean-up code here (global destructor)