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 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
29 # your exported package globals go here,
30 # as well as any optionally exported functions
32 @EXPORT_OK = qw($Var1 %Hashit);
35 # non-exported package globals go here
36 #use vars qw(@more $stuff);
38 # initalize package globals, first exported ones
43 # then the others (which are still accessible as $Some::Module::stuff)
47 # all file-scoped lexicals must be created before
48 # the functions below that use them.
50 # file-private lexicals go here
54 # here's a file-private function as a closure,
55 # callable as &$priv_func; it cannot be prototyped.
60 # make all your functions, whether exported or not;
67 my $sth=$dbh->prepare("select * from branches");
69 while (my $branch=$sth->fetchrow_hashref) {
70 $branches{$branch->{'branchcode'}}=$branch;
80 my $sth=$dbh->prepare("select * from printers");
82 while (my $printer=$sth->fetchrow_hashref) {
83 $printers{$printer->{'printqueue'}}=$printer;
90 sub getpatroninformation {
92 my ($env, $borrowernumber,$cardnumber) = @_;
95 open O, ">>/root/tkcirc.out";
96 print O "Looking up patron $borrowernumber / $cardnumber\n";
97 if ($borrowernumber) {
98 $sth=$dbh->prepare("select * from borrowers where borrowernumber=$borrowernumber");
99 } elsif ($cardnumber) {
100 $sth=$dbh->prepare("select * from borrowers where cardnumber=$cardnumber");
102 # error condition. This subroutine must be called with either a
103 # borrowernumber or a card number.
104 $env->{'apierror'}="invalid borrower information passed to getpatroninformation subroutine";
108 my $borrower=$sth->fetchrow_hashref;
109 my $flags=patronflags($env, $borrower, $dbh);
112 print O "$borrower->{'surname'} <---\n";
114 $borrower->{'flags'}=$flags;
115 return($borrower, $flags);
122 sub getiteminformation {
123 # returns a hash of item information given either the itemnumber or the barcode
124 my ($env, $itemnumber, $barcode) = @_;
128 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.itemnumber=$itemnumber and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
130 my $q_barcode=$dbh->quote($barcode);
131 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.barcode=$q_barcode and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
133 $env->{'apierror'}="getiteminformation() subroutine must be called with either an itemnumber or a barcode";
138 my $iteminformation=$sth->fetchrow_hashref;
141 $iteminformation->{'dewey'}=~s/0*$//;
142 return($iteminformation);
146 # returns an array of borrower hash references, given a cardnumber or a partial
148 my ($env, $key) = @_;
151 my $q_key=$dbh->quote($key);
152 my $sth=$dbh->prepare("select * from borrowers where cardnumber=$q_key");
155 my ($borrower)=$sth->fetchrow_hashref;
156 push (@borrowers, $borrower);
158 $q_key=$dbh->quote("$key%");
160 $sth=$dbh->prepare("select * from borrowers where surname like $q_key");
162 while (my $borrower = $sth->fetchrow_hashref) {
163 push (@borrowers, $borrower);
173 my ($env, $patroninformation, $barcode, $responses) = @_;
175 my $iteminformation=getiteminformation($env, 0, $barcode);
177 my ($rejected,$question,$defaultanswer,$questionnumber, $noissue);
179 if ($patroninformation->{'gonenoaddress'}) {
180 $rejected="Patron is gone, with no known address.";
183 if ($patroninformation->{'lost'}) {
184 $rejected="Patron's card has been reported lost.";
187 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh);
189 $rejected=sprintf "Patron owes \$%.02f.", $amount;
192 if ($iteminformation->{'notforloan'} == 1) {
193 $rejected="Item not for loan.";
196 if ($iteminformation->{'wthdrawn'} == 1) {
197 $rejected="Item withdrawn.";
200 if ($iteminformation->{'restricted'} == 1) {
201 $rejected="Restricted item.";
204 if ($iteminformation->{'itemtype'} eq 'REF') {
205 $rejected="Reference item: Not for loan.";
208 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
209 if ($currentborrower eq $patroninformation->{'borrowernumber'}) {
210 # Already issued to current borrower
211 my ($renewstatus) = renewstatus($env,$dbh,$patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
212 if ($renewstatus == 0) {
213 $rejected="No more renewals allowed for this item.";
216 if ($responses->{4} eq '') {
218 $question="Book is issued to this borrower.\nRenew?";
221 } elsif ($responses->{4} eq 'Y') {
222 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
224 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
225 $iteminformation->{'charge'}=$charge;
227 &UpdateStats($env,$env->{'branchcode'},'renew',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
228 renewbook($env,$dbh, $patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
235 } elsif ($currentborrower ne '') {
236 my ($currborrower, $cbflags)=getpatroninformation($env,$currentborrower,0);
237 if ($responses->{1} eq '') {
239 $question="Issued to $currborrower->{'firstname'} $currborrower->{'surname'} ($currborrower->{'cardnumber'}).\nMark as returned?";
242 } elsif ($responses->{1} eq 'Y') {
243 returnbook($env,$iteminformation->{'barcode'});
250 my ($resbor, $resrec) = checkreserve($env, $dbh, $iteminformation->{'itemnumber'});
252 if ($resbor eq $patroninformation->{'borrowernumber'}) {
253 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
254 my $rsth = $dbh->prepare($rquery);
257 } elsif ($resbor ne "") {
258 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
259 if ($responses->{2} eq '') {
261 $question="Reserved for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) since $resrec->{'reservedate'}\nAllow issue?";
264 } elsif ($responses->{2} eq 'N') {
265 #printreserve($env, $resrec, $resborrower, $iteminformation);
269 if ($responses->{3} eq '') {
271 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
274 } elsif ($responses->{3} eq 'Y') {
275 my $rquery = "update reserves set found = 'F' where reservedate = '$resrec->{'reservedate'}' and borrowernumber = '$resrec->{'borrowernumber'}' and biblionumber = '$resrec->{'biblionumber'}'";
276 my $rsth = $dbh->prepare($rquery);
284 unless (($question) || ($rejected) || ($noissue)) {
286 if ($iteminformation->{'loanlength'}) {
287 $loanlength=$iteminformation->{'loanlength'};
290 my $datedue=time+($loanlength)*86400;
291 my @datearr = localtime($datedue);
292 $dateduef = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
293 if ($env->{'datedue'}) {
294 $dateduef=$env->{'datedue'};
296 my $sth=$dbh->prepare("insert into issues (borrowernumber, itemnumber, date_due, branchcode) values ($patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'}, '$dateduef', '$env->{'branchcode'}')");
299 $iteminformation->{'issues'}++;
300 $sth=$dbh->prepare("update items set issues=$iteminformation->{'issues'} where itemnumber=$iteminformation->{'itemnumber'}");
303 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
305 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
306 $iteminformation->{'charge'}=$charge;
308 &UpdateStats($env,$env->{'branchcode'},'issue',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
311 if ($iteminformation->{'charge'}) {
312 $message=sprintf "Rental charge of \$%.02f applies.", $iteminformation->{'charge'};
315 return ($iteminformation, $dateduef, $rejected, $question, $questionnumber, $defaultanswer, $message);
320 my ($env, $barcode) = @_;
321 my ($messages, $overduecharge);
323 my ($iteminformation) = getiteminformation($env, 0, $barcode);
325 if ($iteminformation) {
326 my $sth=$dbh->prepare("select * from issues where (itemnumber='$iteminformation->{'itemnumber'}') and (returndate is null)");
328 my ($currentborrower) = currentborrower($env, $iteminformation->{'itemnumber'}, $dbh);
329 updatelastseen($env,$dbh,$iteminformation->{'itemnumber'});
330 if ($currentborrower) {
331 ($borrower)=getpatroninformation($env,$currentborrower,0);
332 my @datearr = localtime(time);
333 my $dateret = (1900+$datearr[5])."-".$datearr[4]."-".$datearr[3];
334 my $query = "update issues set returndate = now(), branchcode ='$env->{'branchcode'}' where (borrowernumber = $borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (returndate is null)";
335 my $sth = $dbh->prepare($query);
340 # check for overdue fine
343 $sth=$dbh->prepare("select * from accountlines where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (accounttype='FU' or accounttype='O')");
345 # alter fine to show that the book has been returned
346 if (my $data = $sth->fetchrow_hashref) {
347 my $usth=$dbh->prepare("update accountlines set accounttype='F' where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber=$iteminformation->{'itemnumber'}) and (acccountno='$data->{'accountno'}')");
350 $overduecharge=$data->{'amountoutstanding'};
353 # check for charge made for lost book
354 $sth=$dbh->prepare("select * from accountlines where (borrowernumber=$borrower->{'borrowernumber'}) and (itemnumber = $iteminformation->{'itemnumber'}) and (accounttype='L')");
356 if (my $data = $sth->fetchrow_hashref) {
357 # writeoff this amount
359 my $amount = $data->{'amount'};
360 my $acctno = $data->{'accountno'};
362 if ($data->{'amountoutstanding'} == $amount) {
363 $offset = $data->{'amount'};
366 $offset = $amount - $data->{'amountoutstanding'};
367 $amountleft = $data->{'amountoutstanding'} - $amount;
369 my $uquery = "update accountlines
370 set accounttype = 'LR',amountoutstanding='0'
371 where (borrowernumber = $borrower->{'borrowernumber'})
372 and (itemnumber = $iteminformation->{'itemnumber'})
373 and (accountno = '$acctno') ";
374 my $usth = $dbh->prepare($uquery);
377 my $nextaccntno = C4::Accounts::getnextacctno($env,$borrower->{'borrowernumber'},$dbh);
378 $uquery = "insert into accountlines
379 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
380 values ($borrower->{'borrowernumber'},$nextaccntno,now(),0-$amount,'Book Returned',
382 $usth = $dbh->prepare($uquery);
385 $uquery = "insert into accountoffsets
386 (borrowernumber, accountno, offsetaccount, offsetamount)
387 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
388 $usth = $dbh->prepare($uquery);
394 UpdateStats($env,'branch','return','0','',$iteminformation->{'itemnumber'});
397 return ($iteminformation, $borrower, $messages, $overduecharge);
402 # Original subroutine for Circ2.pm
404 my ($env,$patroninformation,$dbh) = @_;
405 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh);
408 $flaginfo{'message'}=sprintf "Patron owes \$%.02f", $amount;
410 $flaginfo{'noissues'}=1;
412 $flags{'CHARGES'}=\%flaginfo;
414 if ($patroninformation->{'gonenoaddress'} == 1) {
416 $flaginfo{'message'}='Borrower has no valid address.';
417 $flaginfo{'noissues'}=1;
418 $flags{'GNA'}=\%flaginfo;
420 if ($patroninformation->{'lost'} == 1) {
422 $flaginfo{'message'}='Borrower\'s card reported lost.';
423 $flaginfo{'noissues'}=1;
424 $flags{'LOST'}=\%flaginfo;
426 if ($patroninformation->{'borrowernotes'}) {
428 $flaginfo{'message'}="$patroninformation->{'borrowernotes'}";
429 $flags{'NOTES'}=\%flaginfo;
431 my ($odues, $itemsoverdue) = checkoverdues($env,$patroninformation->{'borrowernumber'},$dbh);
434 $flaginfo{'message'}="Overdue Items\n";
435 foreach (sort {$a->{'date_due'} cmp $b->{'date_due'}} @$itemsoverdue) {
436 $flaginfo{'message'}.="$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n";
438 $flags{'ODUES'}=\%flaginfo;
440 my ($nowaiting,$itemswaiting) = checkwaiting($env,$dbh,$patroninformation->{'borrowernumber'});
443 $flaginfo{'message'}="Reserved items available";
444 $flaginfo{'itemlist'}=$itemswaiting;
445 $flaginfo{'itemfields'}=['barcode', 'title', 'author', 'dewey', 'subclass', 'holdingbranch'];
446 $flags{'WAITING'}=\%flaginfo;
455 # From Main.pm, modified to return a list of overdueitems, in addition to a count
456 #checks whether a borrower has overdue items
457 my ($env,$bornum,$dbh)=@_;
458 my @datearr = localtime;
459 my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3];
462 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'";
463 my $sth=$dbh->prepare($query);
465 while (my $data = $sth->fetchrow_hashref) {
466 push (@overdueitems, $data);
470 return ($count, \@overdueitems);
474 # Stolen from Returns.pm
475 my ($env,$dbh,$itemnumber)= @_;
476 my $br = $env->{'branchcode'};
477 my $query = "update items
478 set datelastseen = now(), holdingbranch = '$br'
479 where (itemnumber = '$itemnumber')";
480 my $sth = $dbh->prepare($query);
485 sub currentborrower {
486 # Original subroutine for Circ2.pm
487 my ($env, $itemnumber, $dbh) = @_;
488 my $q_itemnumber=$dbh->quote($itemnumber);
489 my $sth=$dbh->prepare("select borrowers.borrowernumber from
490 issues,borrowers where issues.itemnumber=$q_itemnumber and
491 issues.borrowernumber=borrowers.borrowernumber and issues.returndate is
494 my ($previousborrower)=$sth->fetchrow;
495 return($previousborrower);
499 # Stolen from Main.pm
500 # Check for reserves for biblio
501 my ($env,$dbh,$itemnum)=@_;
503 my $query = "select * from reserves,items
504 where (items.itemnumber = '$itemnum')
505 and (reserves.cancellationdate is NULL)
506 and (items.biblionumber = reserves.biblionumber)
507 and ((reserves.found = 'W')
508 or (reserves.found is null))
510 my $sth = $dbh->prepare($query);
513 if (my $data=$sth->fetchrow_hashref) {
515 my $const = $data->{'constrainttype'};
517 $resbor = $data->{'borrowernumber'};
520 my $cquery = "select * from reserveconstraints,items
521 where (borrowernumber='$data->{'borrowernumber'}')
522 and reservedate='$data->{'reservedate'}'
523 and reserveconstraints.biblionumber='$data->{'biblionumber'}'
524 and (items.itemnumber=$itemnum and
525 items.biblioitemnumber = reserveconstraints.biblioitemnumber)";
526 my $csth = $dbh->prepare($cquery);
528 if (my $cdata=$csth->fetchrow_hashref) {$found = 1;}
530 if ($found eq 1) {$resbor = $data->{'borrowernumber'};}
532 if ($found eq 0) {$resbor = $data->{'borrowernumber'};}
538 return ($resbor,$resrec);
542 # New subroutine for Circ2.pm
543 my ($env, $borrower) = @_;
547 my $borrowernumber=$borrower->{'borrowernumber'};
549 if ($env->{'todaysissues'}) {
550 my @datearr = localtime(time());
551 my $today = (1900+$datearr[5]).sprintf "0%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
552 $crit=" and issues.timestamp like '$today%' ";
554 if ($env->{'nottodaysissues'}) {
555 my @datearr = localtime(time());
556 my $today = (1900+$datearr[5]).sprintf "0%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
557 $crit=" and !(issues.timestamp like '$today%') ";
559 my $sth=$dbh->prepare("select * from issues,items,biblioitems,biblio where borrowernumber=$borrowernumber and issues.itemnumber=items.itemnumber and items.biblionumber=biblio.biblionumber and items.biblioitemnumber=biblioitems.biblioitemnumber and returndate is null $crit order by date_due");
561 while (my $data = $sth->fetchrow_hashref) {
562 $data->{'dewey'}=~s/0*$//;
563 my $datedue=$data->{'date_due'};
564 my $itemnumber=$data->{'itemnumber'};
565 $currentissues{$counter}=$data;
570 return(\%currentissues);
575 # check for reserves waiting
576 my ($env,$dbh,$bornum)=@_;
578 my $query = "select * from reserves
579 where (borrowernumber = '$bornum')
580 and (reserves.found='W') and cancellationdate is NULL";
581 my $sth = $dbh->prepare($query);
584 if (my $data=$sth->fetchrow_hashref) {
585 @itemswaiting[$cnt] =$data;
589 return ($cnt,\@itemswaiting);
594 # Stolen from Accounts.pm
595 #take borrower number
596 #check accounts and list amounts owing
597 my ($env,$bornumber,$dbh)=@_;
598 my $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
599 borrowernumber=$bornumber and amountoutstanding<>0");
602 while (my $data=$sth->fetchrow_hashref){
603 $total=$total+$data->{'sum(amountoutstanding)'};
606 # output(1,2,"borrower owes $total");
608 # # output(1,2,"borrower owes $total");
610 # reconcileaccount($env,$dbh,$bornumber,$total);
618 # Stolen from Renewals.pm
619 # check renewal status
620 my ($env,$dbh,$bornum,$itemno)=@_;
623 my $q1 = "select * from issues
624 where (borrowernumber = '$bornum')
625 and (itemnumber = '$itemno')
626 and returndate is null";
627 my $sth1 = $dbh->prepare($q1);
629 if (my $data1 = $sth1->fetchrow_hashref) {
630 my $q2 = "select renewalsallowed from items,biblioitems,itemtypes
631 where (items.itemnumber = '$itemno')
632 and (items.biblioitemnumber = biblioitems.biblioitemnumber)
633 and (biblioitems.itemtype = itemtypes.itemtype)";
634 my $sth2 = $dbh->prepare($q2);
636 if (my $data2=$sth2->fetchrow_hashref) {
637 $renews = $data2->{'renewalsallowed'};
639 if ($renews > $data1->{'renewals'}) {
649 # Stolen from Renewals.pm
650 # mark book as renewed
651 my ($env,$dbh,$bornum,$itemno,$datedue)=@_;
652 $datedue=$env->{'datedue'};
653 if ($datedue eq "" ) {
655 my $query= "Select * from biblioitems,items,itemtypes
656 where (items.itemnumber = '$itemno')
657 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
658 and (biblioitems.itemtype = itemtypes.itemtype)";
659 my $sth=$dbh->prepare($query);
661 if (my $data=$sth->fetchrow_hashref) {
662 $loanlength = $data->{'loanlength'}
666 my $datedu = time + ($loanlength * 86400);
667 my @datearr = localtime($datedu);
668 $datedue = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
670 my @date = split("-",$datedue);
671 my $odatedue = (@date[2]+0)."-".(@date[1]+0)."-".@date[0];
672 my $issquery = "select * from issues where borrowernumber='$bornum' and
673 itemnumber='$itemno' and returndate is null";
674 my $sth=$dbh->prepare($issquery);
676 my $issuedata=$sth->fetchrow_hashref;
678 my $renews = $issuedata->{'renewals'} +1;
679 my $updquery = "update issues
680 set date_due = '$datedue', renewals = '$renews'
681 where borrowernumber='$bornum' and
682 itemnumber='$itemno' and returndate is null";
683 my $sth=$dbh->prepare($updquery);
691 # Stolen from Issues.pm
692 # calculate charges due
693 my ($env, $dbh, $itemno, $bornum)=@_;
696 my $q1 = "select itemtypes.itemtype,rentalcharge from items,biblioitems,itemtypes where (items.itemnumber ='$itemno') and (biblioitems.biblioitemnumber = items.biblioitemnumber) and (biblioitems.itemtype = itemtypes.itemtype)";
697 my $sth1= $dbh->prepare($q1);
699 if (my $data1=$sth1->fetchrow_hashref) {
700 $item_type = $data1->{'itemtype'};
701 $charge = $data1->{'rentalcharge'};
702 my $q2 = "select rentaldiscount from borrowers,categoryitem
703 where (borrowers.borrowernumber = '$bornum')
704 and (borrowers.categorycode = categoryitem.categorycode)
705 and (categoryitem.itemtype = '$item_type')";
706 my $sth2=$dbh->prepare($q2);
708 if (my $data2=$sth2->fetchrow_hashref) {
709 my $discount = $data2->{'rentaldiscount'};
710 $charge = ($charge *(100 - $discount)) / 100;
719 #Stolen from Issues.pm
720 my ($env,$dbh,$itemno,$bornum,$charge) = @_;
721 my $nextaccntno = getnextacctno($env,$bornum,$dbh);
722 my $query = "insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) values ($bornum,$itemno,$nextaccntno,now(),$charge,'Rental','Rent',$charge)";
723 my $sth = $dbh->prepare($query);
730 # Stolen from Accounts.pm
731 my ($env,$bornumber,$dbh)=@_;
733 my $query = "select * from accountlines where (borrowernumber = '$bornumber') order by accountno desc";
734 my $sth = $dbh->prepare($query);
736 if (my $accdata=$sth->fetchrow_hashref){
737 $nextaccntno = $accdata->{'accountno'} + 1;
740 return($nextaccntno);
744 END { } # module clean-up code here (global destructor)