1 package C4::Circulation::Circ2;
3 #package to deal with Returns
4 #written 3/11/99 by olwen@katipo.co.nz
7 # Copyright 2000-2002 Katipo Communications
9 # This file is part of Koha.
11 # Koha is free software; you can redistribute it and/or modify it under the
12 # terms of the GNU General Public License as published by the Free Software
13 # Foundation; either version 2 of the License, or (at your option) any later
16 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
17 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
18 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License along with
21 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
22 # Suite 330, Boston, MA 02111-1307 USA
30 #use C4::InterfaceCDK;
31 #use C4::Circulation::Main;
33 #use C4::Circulation::Renewals;
40 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
42 # set the version for version checking
46 @EXPORT = qw(&getbranches &getprinters &getpatroninformation ¤tissues &getissues &getiteminformation &findborrower &issuebook &returnbook &find_reserves &transferbook &decode
48 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
50 # your exported package globals go here,
51 # as well as any optionally exported functions
53 @EXPORT_OK = qw($Var1 %Hashit);
56 # non-exported package globals go here
57 #use vars qw(@more $stuff);
59 # initalize package globals, first exported ones
64 # then the others (which are still accessible as $Some::Module::stuff)
68 # all file-scoped lexicals must be created before
69 # the functions below that use them.
71 # file-private lexicals go here
75 # here's a file-private function as a closure,
76 # callable as &$priv_func; it cannot be prototyped.
81 # make all your functions, whether exported or not;
85 # returns a reference to a hash of references to branches...
87 my $dbh = C4::Context->dbh;
88 my $sth=$dbh->prepare("select * from branches");
90 while (my $branch=$sth->fetchrow_hashref) {
91 my $tmp = $branch->{'branchcode'}; my $brc = $dbh->quote($tmp);
92 my $query = "select categorycode from branchrelations where branchcode = $brc";
93 my $nsth = $dbh->prepare($query);
95 while (my ($cat) = $nsth->fetchrow_array) {
99 $branches{$branch->{'branchcode'}}=$branch;
108 my $dbh = C4::Context->dbh;
109 my $sth=$dbh->prepare("select * from printers");
111 while (my $printer=$sth->fetchrow_hashref) {
112 $printers{$printer->{'printqueue'}}=$printer;
119 sub getpatroninformation {
121 my ($env, $borrowernumber,$cardnumber) = @_;
122 my $dbh = C4::Context->dbh;
125 if ($borrowernumber) {
126 $query = "select * from borrowers where borrowernumber=$borrowernumber";
127 } elsif ($cardnumber) {
128 $query = "select * from borrowers where cardnumber=$cardnumber";
130 $env->{'apierror'} = "invalid borrower information passed to getpatroninformation subroutine";
133 $env->{'mess'} = $query;
134 $sth = $dbh->prepare($query);
136 my $borrower = $sth->fetchrow_hashref;
137 my $flags = patronflags($env, $borrower, $dbh);
139 $borrower->{'flags'}=$flags;
140 return($borrower, $flags);
145 my $seq = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-';
146 my @s = map { index($seq,$_); } split(//,$encoded);
161 my $n = (($s[0] << 6 | $s[1]) << 6 | $s[2]) << 6 | $s[3];
162 $r .=chr(($n >> 16) ^ 67) .
163 chr(($n >> 8 & 255) ^ 67) .
164 chr(($n & 255) ^ 67);
167 $r = substr($r,0,length($r)-$l);
174 sub getiteminformation {
175 # returns a hash of item information given either the itemnumber or the barcode
176 my ($env, $itemnumber, $barcode) = @_;
177 my $dbh = C4::Context->dbh;
180 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.itemnumber=$itemnumber and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
182 my $q_barcode=$dbh->quote($barcode);
183 $sth=$dbh->prepare("select * from biblio,items,biblioitems where items.barcode=$q_barcode and biblio.biblionumber=items.biblionumber and biblioitems.biblioitemnumber = items.biblioitemnumber");
185 $env->{'apierror'}="getiteminformation() subroutine must be called with either an itemnumber or a barcode";
190 my $iteminformation=$sth->fetchrow_hashref;
192 if ($iteminformation) {
193 $sth=$dbh->prepare("select date_due from issues where itemnumber=$iteminformation->{'itemnumber'} and isnull(returndate)");
195 my ($date_due) = $sth->fetchrow;
196 $iteminformation->{'date_due'}=$date_due;
198 #$iteminformation->{'dewey'}=~s/0*$//;
199 ($iteminformation->{'dewey'} == 0) && ($iteminformation->{'dewey'}='');
200 $sth=$dbh->prepare("select * from itemtypes where itemtype='$iteminformation->{'itemtype'}'");
202 my $itemtype=$sth->fetchrow_hashref;
203 $iteminformation->{'loanlength'}=$itemtype->{'loanlength'};
204 $iteminformation->{'notforloan'}=$itemtype->{'notforloan'};
207 return($iteminformation);
211 # returns an array of borrower hash references, given a cardnumber or a partial
213 my ($env, $key) = @_;
214 my $dbh = C4::Context->dbh;
216 my $q_key=$dbh->quote($key);
217 my $sth=$dbh->prepare("select * from borrowers where cardnumber=$q_key");
220 my ($borrower)=$sth->fetchrow_hashref;
221 push (@borrowers, $borrower);
223 $q_key=$dbh->quote("$key%");
225 $sth=$dbh->prepare("select * from borrowers where surname like $q_key");
227 while (my $borrower = $sth->fetchrow_hashref) {
228 push (@borrowers, $borrower);
237 # transfer book code....
238 my ($tbr, $barcode, $ignoreRs) = @_;
242 my $branches = getbranches();
243 my $iteminformation = getiteminformation(\%env, 0, $barcode);
245 if (not $iteminformation) {
246 $messages->{'BadBarcode'} = $barcode;
249 # get branches of book...
250 my $hbr = $iteminformation->{'homebranch'};
251 my $fbr = $iteminformation->{'holdingbranch'};
253 if ($branches->{$hbr}->{'PE'}) {
254 $messages->{'IsPermanent'} = $hbr;
256 # cant transfer book if is already there....
258 $messages->{'DestinationEqualsHolding'} = 1;
261 # check if it is still issued to someone, return it...
262 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
263 if ($currentborrower) {
264 returnbook($barcode, $fbr);
265 $messages->{'WasReturned'} = $currentborrower;
268 my ($resfound, $resrec) = CheckReserves($iteminformation->{'itemnumber'});
269 if ($resfound and not $ignoreRs) {
270 $resrec->{'ResFound'} = $resfound;
271 $messages->{'ResFound'} = $resrec;
274 #actually do the transfer....
276 dotransfer($iteminformation->{'itemnumber'}, $fbr, $tbr);
277 $messages->{'WasTransfered'} = 1;
279 return ($dotransfer, $messages, $iteminformation);
283 my ($itm, $fbr, $tbr) = @_;
284 my $dbh = C4::Context->dbh;
285 $itm = $dbh->quote($itm);
286 $fbr = $dbh->quote($fbr);
287 $tbr = $dbh->quote($tbr);
288 #new entry in branchtransfers....
289 my $query = "insert into branchtransfers (itemnumber, frombranch, datearrived, tobranch)
290 values($itm, $fbr, now(), $tbr)";
291 my $sth = $dbh->prepare($query);
294 #update holdingbranch in items .....
295 # FIXME - Use $dbh->do()
296 $query = "update items set datelastseen = now(), holdingbranch=$tbr where items.itemnumber=$itm";
297 $sth = $dbh->prepare($query);
305 my ($env, $patroninformation, $barcode, $responses, $date) = @_;
306 my $dbh = C4::Context->dbh;
307 my $iteminformation = getiteminformation($env, 0, $barcode);
309 my ($rejected,$question,$defaultanswer,$questionnumber, $noissue);
312 if ($patroninformation->{'gonenoaddress'}) {
313 $rejected="Patron is gone, with no known address.";
316 if ($patroninformation->{'lost'}) {
317 $rejected="Patron's card has been reported lost.";
320 if ($patroninformation->{'debarred'}) {
321 $rejected="Patron is Debarred";
324 my $amount = checkaccount($env,$patroninformation->{'borrowernumber'}, $dbh,$date);
325 if ($amount > 5 && $patroninformation->{'categorycode'} ne 'L' &&
326 $patroninformation->{'categorycode'} ne 'W' &&
327 $patroninformation->{'categorycode'} ne 'I' &&
328 $patroninformation->{'categorycode'} ne 'B' &&
329 $patroninformation->{'categorycode'} ne 'P') {
330 $rejected = sprintf "Patron owes \$%.02f.", $amount;
333 unless ($iteminformation) {
334 $rejected = "$barcode is not a valid barcode.";
337 if ($iteminformation->{'notforloan'} == 1) {
338 $rejected="Reference item: not for loan.";
341 if ($iteminformation->{'wthdrawn'} == 1) {
342 $rejected="Item withdrawn.";
345 if ($iteminformation->{'restricted'} == 1) {
346 $rejected="Restricted item.";
349 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
350 if ($currentborrower eq $patroninformation->{'borrowernumber'}) {
351 # Already issued to current borrower
352 my ($renewstatus) = renewstatus($env,$dbh,$patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
353 if ($renewstatus == 0) {
354 $rejected="No more renewals allowed for this item.";
357 if ($responses->{4} eq '') {
359 $question = "Book is issued to this borrower.\nRenew?";
360 $defaultanswer = 'Y';
362 } elsif ($responses->{4} eq 'Y') {
363 my $charge = calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
365 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
366 $iteminformation->{'charge'} = $charge;
368 &UpdateStats($env,$env->{'branchcode'},'renew',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
369 renewbook($env,$dbh, $patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'});
376 } elsif ($currentborrower ne '') {
377 my ($currborrower, $cbflags) = getpatroninformation($env,$currentborrower,0);
378 if ($responses->{1} eq '') {
380 $question = "Issued to $currborrower->{'firstname'} $currborrower->{'surname'} ($currborrower->{'cardnumber'}).\nMark as returned?";
383 } elsif ($responses->{1} eq 'Y') {
384 returnbook($iteminformation->{'barcode'}, $env->{'branch'});
391 my ($restype, $res) = CheckReserves($iteminformation->{'itemnumber'});
393 my $resbor = $res->{'borrowernumber'};
394 if ($resbor eq $patroninformation->{'borrowernumber'}) {
396 } elsif ($restype eq "Waiting") {
397 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
398 my $branches = getbranches();
399 my $branchname = $branches->{$res->{'branchcode'}}->{'branchname'};
400 if ($responses->{2} eq '') {
402 $question="<font color=red>Waiting</font> for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) at $branchname \nAllow issue?";
405 } elsif ($responses->{2} eq 'N') {
409 if ($responses->{3} eq '') {
411 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
414 } elsif ($responses->{3} eq 'Y') {
415 CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
418 } elsif ($restype eq "Reserved") {
419 my ($resborrower, $flags)=getpatroninformation($env, $resbor,0);
420 my $branches = getbranches();
421 my $branchname = $branches->{$res->{'branchcode'}}->{'branchname'};
422 if ($responses->{5} eq '') {
424 $question="Reserved for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) since $res->{'reservedate'} \nAllow issue?";
427 } elsif ($responses->{5} eq 'N') {
428 if ($responses->{6} eq '') {
430 $question="Set reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'}) to waiting and transfer to $branchname?";
432 } elsif ($responses->{6} eq 'Y') {
433 my $tobrcd = ReserveWaiting($res->{'itemnumber'}, $res->{'borrowernumber'});
434 transferbook($tobrcd, $barcode, 1);
435 $message = "Item should now be waiting at $branchname";
440 if ($responses->{7} eq '') {
442 $question="Cancel reserve for $resborrower->{'firstname'} $resborrower->{'surname'} ($resborrower->{'cardnumber'})?";
445 } elsif ($responses->{7} eq 'Y') {
446 CancelReserve(0, $res->{'itemnumber'}, $res->{'borrowernumber'});
453 unless (($question) || ($rejected) || ($noissue)) {
455 if ($iteminformation->{'loanlength'}) {
456 $loanlength=$iteminformation->{'loanlength'};
459 my $datedue=time+($loanlength)*86400;
460 my @datearr = localtime($datedue);
461 $dateduef = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
462 if ($env->{'datedue'}) {
463 $dateduef=$env->{'datedue'};
465 $dateduef=~ s/2001\-4\-25/2001\-4\-26/;
466 my $sth=$dbh->prepare("insert into issues (borrowernumber, itemnumber, date_due, branchcode) values ($patroninformation->{'borrowernumber'}, $iteminformation->{'itemnumber'}, '$dateduef', '$env->{'branchcode'}')");
469 $iteminformation->{'issues'}++;
470 $sth=$dbh->prepare("update items set issues=$iteminformation->{'issues'},datelastseen=now() where itemnumber=$iteminformation->{'itemnumber'}");
473 my $charge=calc_charges($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'});
475 createcharge($env, $dbh, $iteminformation->{'itemnumber'}, $patroninformation->{'borrowernumber'}, $charge);
476 $iteminformation->{'charge'}=$charge;
478 &UpdateStats($env,$env->{'branchcode'},'issue',$charge,'',$iteminformation->{'itemnumber'},$iteminformation->{'itemtype'});
480 if ($iteminformation->{'charge'}) {
481 $message=sprintf "Rental charge of \$%.02f applies.", $iteminformation->{'charge'};
483 return ($iteminformation, $dateduef, $rejected, $question, $questionnumber, $defaultanswer, $message);
489 my ($barcode, $branch) = @_;
493 # get information on item
494 my ($iteminformation) = getiteminformation(\%env, 0, $barcode);
495 if (not $iteminformation) {
496 $messages->{'BadBarcode'} = $barcode;
500 my ($currentborrower) = currentborrower($iteminformation->{'itemnumber'});
501 if ((not $currentborrower) && $doreturn) {
502 $messages->{'NotIssued'} = $barcode;
505 # check if the book is in a permanent collection....
506 my $hbr = $iteminformation->{'homebranch'};
507 my $branches = getbranches();
508 if ($branches->{$hbr}->{'PE'}) {
509 $messages->{'IsPermanent'} = $hbr;
511 # check that the book has been cancelled
512 if ($iteminformation->{'wthdrawn'}) {
513 $messages->{'wthdrawn'} = 1;
516 # update issues, thereby returning book (should push this out into another subroutine
517 my ($borrower) = getpatroninformation(\%env, $currentborrower, 0);
518 if ($doreturn) { # FIXME - perl -wc complains about this line.
519 doreturn($borrower->{'borrowernumber'}, $iteminformation->{'itemnumber'});
520 $messages->{'WasReturned'};
522 ($borrower) = getpatroninformation(\%env, $currentborrower, 0);
524 my ($transfered, $mess, $item) = transferbook($branch, $barcode, 1);
525 if ($transfered) { # FIXME - perl -wc complains about this line.
526 $messages->{'WasTransfered'};
528 # fix up the accounts.....
529 if ($iteminformation->{'itemlost'}) { # FIXME - perl -wc complains about this line.
530 updateitemlost($iteminformation->{'itemnumber'});
531 fixaccountforlostandreturned($iteminformation, $borrower);
532 $messages->{'WasLost'};
534 # fix up the overdues in accounts...
535 fixoverduesonreturn($borrower->{'borrowernumber'}, $iteminformation->{'itemnumber'});
537 my ($resfound, $resrec) = CheckReserves($iteminformation->{'itemnumber'});
539 $resrec->{'ResFound'} = $resfound;
540 $messages->{'ResFound'} = $resrec;
543 UpdateStats(\%env, $branch ,'return','0','',$iteminformation->{'itemnumber'});
544 return ($doreturn, $messages, $iteminformation, $borrower);
549 my ($brn, $itm) = @_;
550 my $dbh = C4::Context->dbh;
551 $brn = $dbh->quote($brn);
552 $itm = $dbh->quote($itm);
553 my $query = "update issues set returndate = now() where (borrowernumber = $brn)
554 and (itemnumber = $itm) and (returndate is null)";
555 my $sth = $dbh->prepare($query);
558 $query="update items set datelastseen=now() where itemnumber=$itm";
559 $sth=$dbh->prepare($query);
567 my $dbh = C4::Context->dbh;
568 # FIXME - Use $dbh->do();
569 my $query="update items set itemlost=0 where itemnumber=$itemno";
570 my $sth=$dbh->prepare($query);
575 sub fixaccountforlostandreturned {
576 my ($iteminfo, $borrower) = @_;
578 my $dbh = C4::Context->dbh;
579 my $itm = $dbh->quote($iteminfo->{'itemnumber'});
580 # check for charge made for lost book
581 my $query = "select * from accountlines where (itemnumber = $itm)
582 and (accounttype='L' or accounttype='Rep') order by date desc";
583 my $sth = $dbh->prepare($query);
585 if (my $data = $sth->fetchrow_hashref) {
586 # writeoff this amount
588 my $amount = $data->{'amount'};
589 my $acctno = $data->{'accountno'};
591 if ($data->{'amountoutstanding'} == $amount) {
592 $offset = $data->{'amount'};
595 $offset = $amount - $data->{'amountoutstanding'};
596 $amountleft = $data->{'amountoutstanding'} - $amount;
598 my $uquery = "update accountlines set accounttype = 'LR',amountoutstanding='0'
599 where (borrowernumber = '$data->{'borrowernumber'}')
600 and (itemnumber = $itm) and (accountno = '$acctno') ";
601 my $usth = $dbh->prepare($uquery);
604 #check if any credit is left if so writeoff other accounts
605 my $nextaccntno = getnextacctno(\%env,$data->{'borrowernumber'},$dbh);
606 if ($amountleft < 0){
609 if ($amountleft > 0){
610 my $query = "select * from accountlines where (borrowernumber = '$data->{'borrowernumber'}')
611 and (amountoutstanding >0) order by date";
612 my $msth = $dbh->prepare($query);
614 # offset transactions
617 while (($accdata=$msth->fetchrow_hashref) and ($amountleft>0)){
618 if ($accdata->{'amountoutstanding'} < $amountleft) {
620 $amountleft = $amountleft - $accdata->{'amountoutstanding'};
622 $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
625 my $thisacct = $accdata->{'accountno'};
626 my $updquery = "update accountlines set amountoutstanding= '$newamtos'
627 where (borrowernumber = '$data->{'borrowernumber'}')
628 and (accountno='$thisacct')";
629 my $usth = $dbh->prepare($updquery);
632 $updquery = "insert into accountoffsets
633 (borrowernumber, accountno, offsetaccount, offsetamount)
635 ('$data->{'borrowernumber'}','$accdata->{'accountno'}','$nextaccntno','$newamtos')";
636 $usth = $dbh->prepare($updquery);
642 if ($amountleft > 0){
645 my $desc="Book Returned ".$iteminfo->{'barcode'};
646 $uquery = "insert into accountlines
647 (borrowernumber,accountno,date,amount,description,accounttype,amountoutstanding)
648 values ('$data->{'borrowernumber'}','$nextaccntno',now(),0-$amount,'$desc',
650 $usth = $dbh->prepare($uquery);
653 $uquery = "insert into accountoffsets
654 (borrowernumber, accountno, offsetaccount, offsetamount)
655 values ($borrower->{'borrowernumber'},$data->{'accountno'},$nextaccntno,$offset)";
656 $usth = $dbh->prepare($uquery);
659 $uquery = "update items set paidfor='' where itemnumber=$itm";
660 $usth = $dbh->prepare($uquery);
668 sub fixoverduesonreturn {
669 my ($brn, $itm) = @_;
670 my $dbh = C4::Context->dbh;
671 $itm = $dbh->quote($itm);
672 $brn = $dbh->quote($brn);
673 # check for overdue fine
674 my $query = "select * from accountlines where (borrowernumber=$brn)
675 and (itemnumber = $itm) and (accounttype='FU' or accounttype='O')";
676 my $sth = $dbh->prepare($query);
678 # alter fine to show that the book has been returned
679 if (my $data = $sth->fetchrow_hashref) {
680 my $query = "update accountlines set accounttype='F' where (borrowernumber = $brn)
681 and (itemnumber = $itm) and (acccountno='$data->{'accountno'}')";
682 my $usth=$dbh->prepare($query);
691 # Original subroutine for Circ2.pm
693 my ($env, $patroninformation, $dbh) = @_;
694 my $amount = checkaccount($env, $patroninformation->{'borrowernumber'}, $dbh);
697 $flaginfo{'message'}= sprintf "Patron owes \$%.02f", $amount;
699 $flaginfo{'noissues'} = 1;
701 $flags{'CHARGES'} = \%flaginfo;
702 } elsif ($amount < 0){
704 $amount = $amount*-1;
705 $flaginfo{'message'} = sprintf "Patron has credit of \$%.02f", $amount;
706 $flags{'CHARGES'} = \%flaginfo;
708 if ($patroninformation->{'gonenoaddress'} == 1) {
710 $flaginfo{'message'} = 'Borrower has no valid address.';
711 $flaginfo{'noissues'} = 1;
712 $flags{'GNA'} = \%flaginfo;
714 if ($patroninformation->{'lost'} == 1) {
716 $flaginfo{'message'} = 'Borrower\'s card reported lost.';
717 $flaginfo{'noissues'} = 1;
718 $flags{'LOST'} = \%flaginfo;
720 if ($patroninformation->{'debarred'} == 1) {
722 $flaginfo{'message'} = 'Borrower is Debarred.';
723 $flaginfo{'noissues'} = 1;
724 $flags{'DBARRED'} = \%flaginfo;
726 if ($patroninformation->{'borrowernotes'}) {
728 $flaginfo{'message'} = "$patroninformation->{'borrowernotes'}";
729 $flags{'NOTES'} = \%flaginfo;
731 my ($odues, $itemsoverdue)
732 = checkoverdues($env, $patroninformation->{'borrowernumber'}, $dbh);
735 $flaginfo{'message'} = "Yes";
736 $flaginfo{'itemlist'} = $itemsoverdue;
737 foreach (sort {$a->{'date_due'} cmp $b->{'date_due'}} @$itemsoverdue) {
738 $flaginfo{'itemlisttext'}.="$_->{'date_due'} $_->{'barcode'} $_->{'title'} \n";
740 $flags{'ODUES'} = \%flaginfo;
742 my ($nowaiting, $itemswaiting)
743 = CheckWaiting($patroninformation->{'borrowernumber'});
744 if ($nowaiting > 0) {
746 $flaginfo{'message'} = "Reserved items available";
747 $flaginfo{'itemlist'} = $itemswaiting;
748 $flags{'WAITING'} = \%flaginfo;
755 # From Main.pm, modified to return a list of overdueitems, in addition to a count
756 #checks whether a borrower has overdue items
757 my ($env, $bornum, $dbh)=@_;
758 my @datearr = localtime;
759 my $today = ($datearr[5] + 1900)."-".($datearr[4]+1)."-".$datearr[3];
762 my $query = "SELECT * FROM issues,biblio,biblioitems,items
763 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
764 AND items.biblionumber = biblio.biblionumber
765 AND issues.itemnumber = items.itemnumber
766 AND issues.borrowernumber = $bornum
767 AND issues.returndate is NULL
768 AND issues.date_due < '$today'";
769 my $sth = $dbh->prepare($query);
771 while (my $data = $sth->fetchrow_hashref) {
772 push (@overdueitems, $data);
776 return ($count, \@overdueitems);
779 sub currentborrower {
780 # Original subroutine for Circ2.pm
781 my ($itemnumber) = @_;
782 my $dbh = C4::Context->dbh;
783 my $q_itemnumber = $dbh->quote($itemnumber);
784 my $sth=$dbh->prepare("select borrowers.borrowernumber from
785 issues,borrowers where issues.itemnumber=$q_itemnumber and
786 issues.borrowernumber=borrowers.borrowernumber and issues.returndate is
789 my ($borrower) = $sth->fetchrow;
794 # Stolen from Main.pm
795 # Check for reserves for biblio
796 my ($env,$dbh,$itemnum)=@_;
798 my $query = "select * from reserves,items
799 where (items.itemnumber = '$itemnum')
800 and (reserves.cancellationdate is NULL)
801 and (items.biblionumber = reserves.biblionumber)
802 and ((reserves.found = 'W')
803 or (reserves.found is null))
805 my $sth = $dbh->prepare($query);
808 my $data=$sth->fetchrow_hashref;
809 while ($data && $resbor eq '') {
811 my $const = $data->{'constrainttype'};
813 $resbor = $data->{'borrowernumber'};
816 my $cquery = "select * from reserveconstraints,items
817 where (borrowernumber='$data->{'borrowernumber'}')
818 and reservedate='$data->{'reservedate'}'
819 and reserveconstraints.biblionumber='$data->{'biblionumber'}'
820 and (items.itemnumber=$itemnum and
821 items.biblioitemnumber = reserveconstraints.biblioitemnumber)";
822 my $csth = $dbh->prepare($cquery);
824 if (my $cdata=$csth->fetchrow_hashref) {$found = 1;}
826 if ($found eq 1) {$resbor = $data->{'borrowernumber'};}
828 if ($found eq 0) {$resbor = $data->{'borrowernumber'};}
832 $data=$sth->fetchrow_hashref;
835 return ($resbor,$resrec);
839 # New subroutine for Circ2.pm
840 my ($env, $borrower) = @_;
841 my $dbh = C4::Context->dbh;
844 my $borrowernumber = $borrower->{'borrowernumber'};
846 if ($env->{'todaysissues'}) {
847 my @datearr = localtime(time());
848 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
849 $crit=" and issues.timestamp like '$today%' ";
851 if ($env->{'nottodaysissues'}) {
852 my @datearr = localtime(time());
853 my $today = (1900+$datearr[5]).sprintf "%02d", ($datearr[4]+1).sprintf "%02d", $datearr[3];
854 $crit=" and !(issues.timestamp like '$today%') ";
856 my $select="select * from issues,items,biblioitems,biblio where
857 borrowernumber='$borrowernumber' and issues.itemnumber=items.itemnumber and
858 items.biblionumber=biblio.biblionumber and
859 items.biblioitemnumber=biblioitems.biblioitemnumber and returndate is null
860 $crit order by issues.date_due";
862 my $sth=$dbh->prepare($select);
864 while (my $data = $sth->fetchrow_hashref) {
865 $data->{'dewey'}=~s/0*$//;
866 ($data->{'dewey'} == 0) && ($data->{'dewey'}='');
867 my @datearr = localtime(time());
868 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]
869 +1)).sprintf ("%0.2d", $datearr[3]);
870 my $datedue=$data->{'date_due'};
872 if ($datedue < $todaysdate) {
873 $data->{'overdue'}=1;
875 my $itemnumber=$data->{'itemnumber'};
876 $currentissues{$counter}=$data;
880 return(\%currentissues);
884 # New subroutine for Circ2.pm
886 my $dbh = C4::Context->dbh;
887 my $borrowernumber = $borrower->{'borrowernumber'};
888 my $brn =$dbh->quote($borrowernumber);
890 my $select = "select issues.timestamp, issues.date_due, items.biblionumber,
891 items.barcode, biblio.title, biblio.author, biblioitems.dewey,
893 from issues,items,biblioitems,biblio
894 where issues.borrowernumber = $brn
895 and issues.itemnumber = items.itemnumber
896 and items.biblionumber = biblio.biblionumber
897 and items.biblioitemnumber = biblioitems.biblioitemnumber
898 and issues.returndate is null
899 order by issues.date_due";
901 my $sth=$dbh->prepare($select);
904 while (my $data = $sth->fetchrow_hashref) {
905 $data->{'dewey'} =~ s/0*$//;
906 ($data->{'dewey'} == 0) && ($data->{'dewey'} = '');
907 my @datearr = localtime(time());
908 my $todaysdate = (1900+$datearr[5]).sprintf ("%0.2d", ($datearr[4]+1)).sprintf ("%0.2d", $datearr[3]);
909 my $datedue = $data->{'date_due'};
911 if ($datedue < $todaysdate) {
912 $data->{'overdue'} = 1;
914 $currentissues{$counter} = $data;
918 return(\%currentissues);
923 # check for reserves waiting
924 my ($env,$dbh,$bornum)=@_;
926 my $query = "select * from reserves
927 where (borrowernumber = '$bornum')
928 and (reserves.found='W') and cancellationdate is NULL";
929 my $sth = $dbh->prepare($query);
932 if (my $data=$sth->fetchrow_hashref) {
933 $itemswaiting[$cnt] =$data;
937 return ($cnt,\@itemswaiting);
940 # FIXME - This is nearly-identical to &C4::Accounts::checkaccount
942 # Stolen from Accounts.pm
943 #take borrower number
944 #check accounts and list amounts owing
945 my ($env,$bornumber,$dbh,$date)=@_;
946 my $select="Select sum(amountoutstanding) from accountlines where
947 borrowernumber=$bornumber and amountoutstanding<>0";
949 $select.=" and date < '$date'";
952 my $sth=$dbh->prepare($select);
955 while (my $data=$sth->fetchrow_hashref){
956 $total=$total+$data->{'sum(amountoutstanding)'};
959 # output(1,2,"borrower owes $total");
961 # # output(1,2,"borrower owes $total");
963 # reconcileaccount($env,$dbh,$bornumber,$total);
971 # Stolen from Renewals.pm
972 # check renewal status
973 my ($env,$dbh,$bornum,$itemno)=@_;
976 my $q1 = "select * from issues
977 where (borrowernumber = '$bornum')
978 and (itemnumber = '$itemno')
979 and returndate is null";
980 my $sth1 = $dbh->prepare($q1);
982 if (my $data1 = $sth1->fetchrow_hashref) {
983 my $q2 = "select renewalsallowed from items,biblioitems,itemtypes
984 where (items.itemnumber = '$itemno')
985 and (items.biblioitemnumber = biblioitems.biblioitemnumber)
986 and (biblioitems.itemtype = itemtypes.itemtype)";
987 my $sth2 = $dbh->prepare($q2);
989 if (my $data2=$sth2->fetchrow_hashref) {
990 $renews = $data2->{'renewalsallowed'};
992 if ($renews > $data1->{'renewals'}) {
1002 # Stolen from Renewals.pm
1003 # mark book as renewed
1004 my ($env,$dbh,$bornum,$itemno,$datedue)=@_;
1005 $datedue=$env->{'datedue'};
1006 if ($datedue eq "" ) {
1008 my $query= "Select * from biblioitems,items,itemtypes
1009 where (items.itemnumber = '$itemno')
1010 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
1011 and (biblioitems.itemtype = itemtypes.itemtype)";
1012 my $sth=$dbh->prepare($query);
1014 if (my $data=$sth->fetchrow_hashref) {
1015 $loanlength = $data->{'loanlength'}
1019 my $datedu = time + ($loanlength * 86400);
1020 my @datearr = localtime($datedu);
1021 $datedue = (1900+$datearr[5])."-".($datearr[4]+1)."-".$datearr[3];
1023 my @date = split("-",$datedue);
1024 my $odatedue = ($date[2]+0)."-".($date[1]+0)."-".$date[0];
1025 my $issquery = "select * from issues where borrowernumber='$bornum' and
1026 itemnumber='$itemno' and returndate is null";
1027 my $sth=$dbh->prepare($issquery);
1029 my $issuedata=$sth->fetchrow_hashref;
1031 my $renews = $issuedata->{'renewals'} +1;
1032 my $updquery = "update issues
1033 set date_due = '$datedue', renewals = '$renews'
1034 where borrowernumber='$bornum' and
1035 itemnumber='$itemno' and returndate is null";
1036 $sth=$dbh->prepare($updquery);
1043 # FIXME - This is almost, but not quite, identical to
1044 # &C4::Circulation::Issues::calc_charges and
1045 # &C4::Circulation::Renewals2::calc_charges.
1046 # Pick one and stick with it.
1048 # Stolen from Issues.pm
1049 # calculate charges due
1050 my ($env, $dbh, $itemno, $bornum)=@_;
1055 # open (FILE,">>/tmp/charges");
1057 my $q1 = "select itemtypes.itemtype,rentalcharge from items,biblioitems,itemtypes
1058 where (items.itemnumber ='$itemno')
1059 and (biblioitems.biblioitemnumber = items.biblioitemnumber)
1060 and (biblioitems.itemtype = itemtypes.itemtype)";
1061 my $sth1= $dbh->prepare($q1);
1062 # print FILE "$q1\n";
1064 if (my $data1=$sth1->fetchrow_hashref) {
1065 $item_type = $data1->{'itemtype'};
1066 $charge = $data1->{'rentalcharge'};
1067 # print FILE "charge is $charge\n";
1068 my $q2 = "select rentaldiscount from borrowers,categoryitem
1069 where (borrowers.borrowernumber = '$bornum')
1070 and (borrowers.categorycode = categoryitem.categorycode)
1071 and (categoryitem.itemtype = '$item_type')";
1072 my $sth2=$dbh->prepare($q2);
1075 if (my $data2=$sth2->fetchrow_hashref) {
1076 my $discount = $data2->{'rentaldiscount'};
1077 # print FILE "discount is $discount";
1078 if ($discount eq 'NULL') {
1081 $charge = ($charge *(100 - $discount)) / 100;
1091 #Stolen from Issues.pm
1092 my ($env,$dbh,$itemno,$bornum,$charge) = @_;
1093 my $nextaccntno = getnextacctno($env,$bornum,$dbh);
1094 my $query = "insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) values ($bornum,$itemno,$nextaccntno,now(),$charge,'Rental','Rent',$charge)";
1095 my $sth = $dbh->prepare($query);
1102 # Stolen from Accounts.pm
1103 my ($env,$bornumber,$dbh)=@_;
1104 my $nextaccntno = 1;
1105 my $query = "select * from accountlines where (borrowernumber = '$bornumber') order by accountno desc";
1106 my $sth = $dbh->prepare($query);
1108 if (my $accdata=$sth->fetchrow_hashref){
1109 $nextaccntno = $accdata->{'accountno'} + 1;
1112 return($nextaccntno);
1116 # Stolen from Returns.pm
1119 my $dbh = C4::Context->dbh;
1120 my ($itemdata) = getiteminformation(\%env, $itemno,0);
1121 my $bibno = $dbh->quote($itemdata->{'biblionumber'});
1122 my $bibitm = $dbh->quote($itemdata->{'biblioitemnumber'});
1123 my $query = "select * from reserves where ((found = 'W') or (found is null))
1124 and biblionumber = $bibno and cancellationdate is NULL
1125 order by priority, reservedate ";
1126 my $sth = $dbh->prepare($query);
1132 while (($resrec = $sth->fetchrow_hashref) && (not $resfound)) {
1134 my $brn = $dbh->quote($resrec->{'borrowernumber'});
1135 my $rdate = $dbh->quote($resrec->{'reservedate'});
1136 my $bibno = $dbh->quote($resrec->{'biblionumber'});
1137 if ($resrec->{'found'} eq "W") {
1138 if ($resrec->{'itemnumber'} eq $itemno) {
1142 if ($resrec->{'constrainttype'} eq "a") {
1145 my $conquery = "select * from reserveconstraints where borrowernumber = $brn
1146 and reservedate = $rdate and biblionumber = $bibno and biblioitemnumber = $bibitm";
1147 my $consth = $dbh->prepare($conquery);
1149 if (my $conrec = $consth->fetchrow_hashref) {
1150 if ($resrec->{'constrainttype'} eq "o") {
1158 my $updquery = "update reserves set found = 'W', itemnumber = '$itemno'
1159 where borrowernumber = $brn and reservedate = $rdate and biblionumber = $bibno";
1160 my $updsth = $dbh->prepare($updquery);
1166 return ($resfound,$lastrec);
1169 END { } # module clean-up code here (global destructor)