package	Opals::Tb_Fines;


use	Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    fine_getFineList
    fine_getFineRate
    fine_getUserByOdl_id
);


# Version number
$VERSION   = 0.01;


#use utf8;
use strict;

use Opals::Context;
use Opals::Transactions qw(
    trans_getTaxTable
);

#######################################
sub fine_getFineList{
    my ($dbh,$lidList)=@_;
    my @idArr = split /,/,$lidList;
    my @fineList;
    sort {$a <=> $b} (@idArr);
    my $curLid="";
    my $indexBase=0;
    my $p;
    foreach my $id(@idArr){
        if($curLid ne $id){
            $curLid=$id;
           my @fineInfo   = fineInfo($dbh,$id,$indexBase);
           if(scalar(@fineInfo)>0){
              my $loanItemInfo = getLoanItemInfo($dbh,$id);
              $p = (!$loanItemInfo->{'price'}) ? '0.00':  $loanItemInfo->{'price'};
              $p =~ s/^\D+|$\D+//gi;
              push @fineList, {idloan=>$id,
                               l_uid  =>$loanItemInfo->{'uid'},
                               l_firstName  =>$loanItemInfo->{'firstname'},
                               l_lastName  =>$loanItemInfo->{'lastname'},
                               barcode=>$loanItemInfo->{'barcode'},
                               itemType=>$loanItemInfo->{'itemType'},
                               title=> $loanItemInfo->{'title'},
                               dateLoan=>$loanItemInfo->{'dateLoan'},
                               dateDue=>$loanItemInfo->{'dateDue'},
                               dateReturn=>$loanItemInfo->{'dateReturn'},
                               price=>$p,
                               numOfFines=>scalar(@fineInfo),
                               fineInfo=>\@fineInfo};
              $indexBase += scalar(@fineInfo);
           }
        }
   } 
   return  @fineList;      
}
#######################################
sub fine_getFineRate{
    my ($dbh)=@_;
    my $sth = $dbh->prepare(<<_STH_);
    select      i.id as itemType ,i.description, 
                if (r.overdue,r.overdue,0) as overdue,
                if (r.maxOverdue , r.maxOverdue,0) as maxOverdue,
                if (r.damage , r.damage, 0) as damage
    from        opl_itemType i left outer join opl_fineRate r on r.itemType=i.id
    where       i.id != '' &&  i.itemCategory = 2
    order by    i.defaultType desc,i.id 
_STH_
    $sth->execute();
 
        my @finerateTbl;
        while (my $rec = $sth->fetchrow_hashref) {
            if($rec->{'overdue'} ne '' || $rec->{'maxOverdue'} ne ''  || $rec->{'damage'} ne'' ){
                $rec->{'settled'}=1;
            }
             push @finerateTbl, $rec;
        }
        $sth->finish;
        return \@finerateTbl;

}
#######################################
sub fine_getFineRate_bk{
    my ($dbh)=@_;
    #my $sth = $dbh->prepare("select * from opl_fineRate order by material, fineType, id");

    my $sth = $dbh->prepare("select * from opl_fineRate r inner join opl_fineMaterials m on m.id=r.mid order by m.def desc,m.material,fineType");
    $sth->execute();
    my $curMaterialType="";
    my $rec;
    my @rate;
    my @rateTbl;
    my $i=0;
    while($rec = $sth->fetchrow_hashref){

        if($curMaterialType ne $rec->{'material'}){
           if($curMaterialType  ne ''){
                my @tmp=@rate;
                push @rateTbl,{index=>$i,material=>$curMaterialType,rate=>\@tmp}; 
                @rate=();
                $i++
           }
           $curMaterialType=$rec->{'material'};
       }
       if($rec->{'fineType'} eq 'overdue'){
            push @rate,{fineType=>'overdue',
                   rate=>$rec->{'rate'},
                   max=>$rec->{'max'}} ;               
       }
       else{
         push @rate,{fineType=>$rec->{'fineType'},
                   rate=>$rec->{'rate'}};
       }                  
        
    }
    
    if($curMaterialType ne ''){
        push @rateTbl,{index=>$i,material=>$curMaterialType,rate=>\@rate};         
    }
    if(scalar(@rateTbl) ==0){
       push @rate,{fineType=>'overdue',rate=>'0.00', max=>'0.00'};
       push @rate,{fineType=>'damaged',rate=>'0.00'};
       push @rateTbl,{index=>0,material=>'book',rate=>\@rate};
    }
 
    
    return @rateTbl ; 
}
#######################################
sub fineInfo{
    my ($dbh,$idloan,$indexBase) = @_;
    my $sth = $dbh->prepare(<<_STH_);
    select * from  tb_odl where idloan =?
_STH_
    $sth->execute($idloan);

    
    my @fineInfo; 
    my $fine;
    my $i=$indexBase;
    while (my $rec = $sth->fetchrow_hashref) {
        $fine={};
        if($rec->{'type'} eq 'overdue'){
           $fine->{'daysOverdue'}=$rec->{'days_overdue'};
        }
        $fine->{'type'}=$rec->{'type'};
        $fine->{$rec->{'type'}} =1;
        $fine->{'idloan'}=$rec->{'idloan'};
        $fine->{'odl_id'} =$rec->{'odl_id'};
        $fine->{'ondate'}=substr $rec->{'ondate'},0,10;
        $fine->{'idx'} =$i;
        $i++;
        push @fineInfo,$fine;
    }
    $sth->finish;
    return @fineInfo;
}
#######################################

sub getLoanItemInfo{
    my ($dbh,$idloan) = @_;
    my $sth = $dbh->prepare(<<_STH_);
    select i.price,i.typeId as itemType, r.fVal as title, l.*, u.firstname,u.lastname 
    from  tb_loan l inner join opl_user u on u.uid=l.uid inner join tb_items i on i.barcode=l.barcode
    inner join tb_records r on i.rid=r.rid && r.fId = "245_a"
    where l.id=? 
_STH_
    $sth->execute($idloan);
    
    my $rec = $sth->fetchrow_hashref;
    if($rec){
        
        $rec->{'dateLoan'} = substr $rec->{'dateLoan'},0,10;
        $rec->{'dateDue'}  = substr $rec->{'dateDue'},0,10;
        $rec->{'dateReturn'}= substr $rec->{'dateReturn'},0,10;
        $rec->{'price'} =~ s/^[\D]+|[\D]+$//g;
        if (!$rec->{'price'}){  $rec->{'price'}= '0.00'};
     }   
    $sth->finish;
    return $rec;
     
}

sub fine_getUserByOdl_id{
    my($dbh,$odl_id)=@_;
    my $sth = $dbh->prepare(<<_STH_);
    select u.* 
    from opl_user u inner join tb_loan l on l.uid=u.uid 
    inner join tb_odl o on o.idloan=l.id
    where o.odl_id=?
_STH_

    $sth->execute($odl_id);
    my $user = $sth->fetchrow_hashref;
    $sth->finish;

    return $user;
}
#######################################
sub fine_updateFineRate{
}
#######################################

sub fine_addNewFineRate{
}