package C4::Overdues;
-# $Id$
# Copyright 2000-2002 Katipo Communications
#
use C4::Log; # logaction
# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g;
-shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
+$VERSION = 3.00;
=head1 NAME
=item Getoverdues
- ($count, $overdues) = &Getoverdues();
+ ($overdues) = &Getoverdues();
-Returns the list of all overdue books.
-
-C<$count> is the number of elements in C<@{$overdues}>.
+Returns the list of all overdue books, with their itemtype.
C<$overdues> is a reference-to-array. Each element is a
reference-to-hash whose keys are the fields of the issues table in the
sub Getoverdues {
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
- "Select * from issues where date_due < now() and returndate is
- NULL order by borrowernumber "
+ "SELECT issues.*,biblioitems.itemtype FROM issues
+ LEFT JOIN items USING (itemnumber)
+ LEFT JOIN biblioitems USING (biblioitemnumber)
+ WHERE date_due < now()
+ AND returndate IS
+ NULL ORDER BY borrowernumber
+ "
);
$sth->execute;
- # FIXME - Use push @results
- my $i = 0;
my @results;
while ( my $data = $sth->fetchrow_hashref ) {
- $results[$i] = $data;
- $i++;
+ push @results, $data;
}
$sth->finish;
- # print @results;
- # FIXME - Bogus API.
- return ( $i, \@results );
+ return \@results;
}
=head2 checkoverdues
my @overdueitems;
my $count = 0;
my $sth = $dbh->prepare(
- "SELECT * FROM issues,biblio,biblioitems,items
- WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
- AND items.biblionumber = biblio.biblionumber
- AND issues.itemnumber = items.itemnumber
- AND issues.borrowernumber = ?
+ "SELECT * FROM issues
+ LEFT JOIN items ON issues.itemnumber = items.itemnumber
+ LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
+ LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
+ WHERE issues.borrowernumber = ?
AND issues.returndate is NULL
AND issues.date_due < ?"
);
#'
sub CalcFine {
- my ( $itemnumber, $bortype, $difference , $dues ) = @_;
+ my ( $item, $bortype, $difference , $dues ) = @_;
my $dbh = C4::Context->dbh;
- my $data = GetIssuingRules($itemnumber,$bortype);
my $amount = 0;
my $printout;
- my $countspecialday=&GetSpecialHolidays($dues,$itemnumber);
- my $countrepeatableday=&GetRepeatableHolidays($dues,$itemnumber,$difference);
+ # calculate how many days the patron is late
+ my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
+ my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
my $countalldayclosed = $countspecialday + $countrepeatableday;
- my $daycount = $difference - $countalldayclosed;
+ my $daycount = $difference - $countalldayclosed;
+ # get issuingrules (fines part will be used)
+ my $data = GetIssuingRules($item->{'itemtype'},$bortype);
my $daycounttotal = $daycount - $data->{'firstremind'};
+ if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
if ($data->{'firstremind'} < $daycount)
- {
- $amount = $daycounttotal*$data->{'fine'};
+ {
+ $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
+ }
+ } else {
+ # get fines default rules
+ my $data = GetIssuingRules($item->{'itemtype'},'*');
+ $daycounttotal = $daycount - $data->{'firstremind'};
+ if ($data->{'firstremind'} < $daycount)
+ {
+ if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
+ $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
+ }
+ }
}
+
+ warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
}
my $diff = $amount - $data->{'amount'};
my $out = $data->{'amountoutstanding'} + $diff;
my $sth2 = $dbh->prepare(
- "update accountlines set date=now(), amount=?,
- amountoutstanding=?,accounttype='FU' where
- borrowernumber=? and itemnumber=?
- and (accounttype='FU' or accounttype='O') and description like ?"
+ "UPDATE accountlines SET date=now(), amount=?,
+ amountoutstanding=?,accounttype='FU' WHERE
+ borrowernumber=? AND itemnumber=?
+ AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
);
$sth2->execute( $amount, $out, $data->{'borrowernumber'},
$data->{'itemnumber'}, "%$due%" );
# I think this else-clause deals with the case where we're adding
# a new fine.
my $sth4 = $dbh->prepare(
- "select title from biblio,items where items.itemnumber=?
- and biblio.biblionumber=items.biblionumber"
+ "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
);
$sth4->execute($itemnum);
my $title = $sth4->fetchrow_hashref;
# $sth3->finish;
# $accountno[0]++;
# begin transaction
- my $nextaccntno = getnextacctno($borrowernumber);
+ my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
my $sth2 = $dbh->prepare(
- "Insert into accountlines
+ "INSERT INTO accountlines
(borrowernumber,itemnumber,date,amount,
- description,accounttype,amountoutstanding,accountno) values
+ description,accounttype,amountoutstanding,accountno) VALUES
(?,?,now(),?,?,'FU',?,?)"
);
$sth2->execute( $borrowernumber, $itemnum, $amount,
my ($borrowernumber) = @_;
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
- "Select * from borrowers,categories where
- borrowernumber=? and
-borrowers.categorycode=categories.categorycode"
+ "SELECT * from borrowers
+ LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
+ WHERE borrowernumber=?"
);
$sth->execute($borrowernumber);
my $data = $sth->fetchrow_hashref;
=item GetIssuingRules
-$data = &GetIssuingRules($itemnumber,$categorycode);
+$data = &GetIssuingRules($itemtype,$categorycode);
Looks up for all issuingrules an item info
=cut
sub GetIssuingRules {
- my ($itemnumber,$categorycode)=@_;
+ my ($itemtype,$categorycode)=@_;
my $dbh = C4::Context->dbh();
my $query=qq|SELECT *
- FROM items,biblioitems,itemtypes,issuingrules
- WHERE items.itemnumber=?
- AND items.biblioitemnumber=biblioitems.biblioitemnumber
- AND biblioitems.itemtype=itemtypes.itemtype
- AND issuingrules.itemtype=itemtypes.itemtype
- AND issuingrules.categorycode=?
- AND (items.itemlost <> 1
- OR items.itemlost is NULL)|;
+ FROM issuingrules
+ WHERE issuingrules.itemtype=?
+ AND issuingrules.categorycode=?
+ |;
my $sth = $dbh->prepare($query);
# print $query;
- $sth->execute($itemnumber,$categorycode);
+ $sth->execute($itemtype,$categorycode);
my ($data) = $sth->fetchrow_hashref;
$sth->finish;
return ($data);
sub NumberNotifyId{
my ($borrowernumber)=@_;
my $dbh = C4::Context->dbh;
- my $env;
my $query=qq| SELECT distinct(notify_id)
FROM accountlines
WHERE borrowernumber=?|;
my @notify;
my $sth=$dbh->prepare($query);
$sth->execute($borrowernumber);
- while ( my $numberofotify=$sth->fetchrow_array){
- push (@notify,$numberofotify);
+ while ( my ($numberofnotify)=$sth->fetchrow){
+ push (@notify,$numberofnotify);
}
$sth->finish;
sub CheckExistantNotifyid {
my($borrowernumber,$date_due) = @_;
my $dbh = C4::Context->dbh;
- my $query = qq|SELECT notify_id FROM issues,accountlines
+ my $query = qq|SELECT notify_id FROM accountlines
+ LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
WHERE accountlines.borrowernumber =?
- AND issues.itemnumber= accountlines.itemnumber
AND date_due = ?|;
my $sth=$dbh->prepare($query);
$sth->execute($borrowernumber,$date_due);
accountlines.notify_level,
items.location,
accountlines.amountoutstanding
- FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
+ FROM accountlines
+ LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
+ LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
+ LEFT JOIN items ON items.itemnumber = issues.itemnumber
+ LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
+ LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
+ LEFT JOIN branches ON branches.branchcode = issues.branchcode
WHERE ( issues.returndate is null)
AND ( accountlines.amountoutstanding != '0.000000')
AND ( accountlines.accounttype = 'FU')
- AND ( issues.borrowernumber = accountlines.borrowernumber )
- AND ( issues.itemnumber = accountlines.itemnumber )
- AND ( borrowers.borrowernumber = issues.borrowernumber )
- AND ( biblio.biblionumber = biblioitems.biblionumber )
- AND ( biblioitems.biblionumber = items.biblionumber )
- AND ( itemtypes.itemtype = biblioitems.itemtype )
- AND ( items.itemnumber = issues.itemnumber )
- AND ( branches.branchcode = issues.branchcode )
AND (issues.branchcode = ?)
AND (issues.date_due <= NOW())
ORDER BY borrowers.surname
accountlines.notify_level,
items.location,
accountlines.amountoutstanding
- FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
+ FROM accountlines
+ LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
+ LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
+ LEFT JOIN items ON items.itemnumber = issues.itemnumber
+ LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
+ LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
+ LEFT JOIN branches ON branches.branchcode = issues.branchcode
WHERE ( issues.returndate is null )
AND ( accountlines.amountoutstanding != '0.000000')
AND ( accountlines.accounttype = 'FU')
- AND ( issues.borrowernumber = accountlines.borrowernumber )
- AND ( issues.itemnumber = accountlines.itemnumber )
- AND ( borrowers.borrowernumber = issues.borrowernumber )
- AND ( biblio.biblionumber = biblioitems.biblionumber )
- AND ( biblioitems.biblionumber = items.biblionumber )
- AND ( itemtypes.itemtype = biblioitems.itemtype )
- AND ( items.itemnumber = issues.itemnumber )
- AND ( branches.branchcode = issues.branchcode )
AND (issues.branchcode = ? AND items.location = ?)
AND (issues.date_due <= NOW())
ORDER BY borrowers.surname