package	Opals::Transactions;


use	Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    trans_recordFine
    trans_doPayment
    trans_doRefund
    trans_doReverseLost
    trans_getPaymentList
    trans_getFineDetail
    trans_getFineListDetail
    trans_getUnpaidFineList
    trans_getStatementReport
    trans_getTaxTable
    trans_getRate
    trans_getBalance
    trans_getODL
    trans_getUnrefundLostList
    trans_getReceiptDetail
 
    trans_getTotalCharge
    trans_getTotalFgvPmt
  );
use Time::localtime;

use Opals::Date qw(
    date_parse
    date_today
    date_text
);

# Version number
$VERSION   = 0.01;


#use utf8;
use strict;
#use Constant;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);


# sha256_base64 sha384_base64 sha512_base64);
use Time::HiRes qw(
    time
);

my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
use Opals::Context;
use Opals::Constant;

my $sql_transaction =" 
insert into opl_transactions
set     uid         = ?,
        amount      = ?,
        balance     = ?,
        description = ?,
        date        =now(),
        code        =?
";

    
my $sql_transactionDetail ="
insert into opl_transactiondetail
set     tid           = ?,
        odl_id        = ?,
        responsible   = ?,
        materials     = ?,
        overdueDays   = ?,
        rate          = ?,
        fineAmount    = ?,
        forgivenAmount= ?,
        amount        = ?,
        taxes         = ?    
";
     
my $sql_taxCalcDetail = "
insert into  opl_taxcalcdetail
set     tid           = ?,
        taxName       = ?,
        taxRate       = ?,
        taxBase       = ?,
        taxableAmount = ?,
        tax           = ?    
";

my $sql_delTaxCalcDetail = "
delete  from opl_taxcalcdetail
where   tid           = ?    
";

#-------------------------------------------------------------------------------
sub trans_recordFine{
    my($dbh,$uid,$ODL_id,$rate,$material,$daysOverdue,$forgivenAmnt,$charge,$loginuid)=@_;

    my $amount =0;
    my $fineAmnt=0;
    my $totalTax=0;
    my $balance =0;
    my $note=""; 
    my $transCode;
    my $tid = isTransRecorded($dbh,$ODL_id);
    if($tid >0){
        return $tid;
    }
    my $fineType=trans_getFineType($dbh,$ODL_id);      
    $note =$fineType;
    if($fineType eq 'overdue') {
        $transCode=1;
    }
    elsif($fineType eq 'lost'){
        $transCode=2;
    }
    elsif($fineType eq 'damaged'){
        $transCode=3;
    }
    $amount = $charge;        
   
    
    $fineAmnt = $amount + $forgivenAmnt;
    my $numOD=$daysOverdue?$daysOverdue:0;

    my @taxTbl = trans_taxCalc($dbh,$amount);
    $totalTax=0;
        
    foreach my $t(@taxTbl){
        $totalTax +=$t->{'tax'};
    }
     
    $balance =trans_getBalance($dbh,$uid);       
    $balance +=$amount + $totalTax;
       
    my $sth = $dbh->prepare($sql_transaction);
    $sth->execute($uid,$amount+$totalTax,$balance,$note,$transCode);
    $tid= $dbh->{'mysql_insertid'};
    $sth->finish;
    
    $sth = $dbh->prepare($sql_transactionDetail);
    $sth->execute($tid,$ODL_id,$loginuid,$material,$numOD,$rate,$fineAmnt,$forgivenAmnt, $amount,$totalTax);
    $sth->finish;
    
    $sth = $dbh->prepare($sql_taxCalcDetail);
    for(my $t=0;$t<scalar(@taxTbl);$t++){
        $sth->execute($tid,
                        $taxTbl[$t]->{"taxName"}, 
                        $taxTbl[$t]->{"rate"}, 
                        $taxTbl[$t]->{"calFormat"}, 
                        $taxTbl[$t]->{"taxableAmount"},
                        $taxTbl[$t]->{"tax"});
    } 
    $sth->finish;
   # set as settled if charge=0 so it will not be listed as unpaid; 
   if($amount ==0){
       $dbh->do("update opl_transactions set  settleDate=now() where   tid=$tid");
       $dbh->do("update opl_odl set  settleDate=now(),days_overdue= $numOD  where   odl_id = $ODL_id");
   }
   else{
       $dbh->do("update opl_odl set days_overdue= $numOD  where odl_id = $ODL_id");
   }

 
   return  $tid;
}


#-------------------------------------------------------------------------------
sub trans_doPayment_bk{
    my($dbh,$uid,$responsible,$amount)=@_;
    #my $type = 'payment';
    my $transCode =4; #payment 
    my $description = 'payment by ' . $responsible;
    $amount *= -1;
    my $balance =trans_getBalance($dbh,$uid);
    $balance += $amount;
    my $sth = $dbh->prepare($sql_transaction);
    $sth->execute($uid,$amount,$balance,$description,$transCode);
    my $tid= $dbh->{'mysql_insertid'};
    $sth->finish;
    return $tid;
    
}
#-------------------------------------------------------------------------------
sub trans_doPayment{
    my($dbh,$uid,$responsible,$payArr,$totalUnpaid)=@_;

    my $sth_update_odl         =$dbh->prepare("update opl_odl set settleDate=now() where odl_id= ?");
    my $sth_update_trans       =$dbh->prepare("update opl_transactions set  balance=(balance -amount) + ?, amount=?, settleDate=now() where tid=?");
    my $sth_update_transDetail =$dbh->prepare("update opl_transactiondetail set forgivenAmount=  ? , amount =? , taxes=?,responsible=? where odl_id=? && tid=?");
    my ($tid,$odl_id,$fgvAmt,$chrgAmt,$payAmt)=(0,0,0,0,0);
    foreach my $trans(@$payArr){
        $tid        =$trans->{'tid'};
        $odl_id     =$trans->{'odl_id'};
        $fgvAmt     =$trans->{'forgiveAmount'};
        $chrgAmt    =$trans->{'chargeAmount'};

        my @taxTbl = trans_taxCalc($dbh,$chrgAmt);
        my $sth = $dbh->prepare($sql_delTaxCalcDetail);
        $sth->execute($tid);
        $sth = $dbh->prepare($sql_taxCalcDetail);
        my $totalTax=0;
        for(my $t=0;$t<scalar(@taxTbl);$t++){
            $sth->execute($tid,
                            $taxTbl[$t]->{"taxName"}, 
                            $taxTbl[$t]->{"rate"}, 
                            $taxTbl[$t]->{"calFormat"}, 
                            $taxTbl[$t]->{"taxableAmount"},
                            $taxTbl[$t]->{"tax"});
            $totalTax += $taxTbl[$t]->{"tax"};
        } 
        $sth_update_odl->execute($odl_id);
        $sth_update_trans->execute($chrgAmt + $totalTax,$chrgAmt +$totalTax,$tid);
        $sth_update_transDetail->execute($fgvAmt,$chrgAmt,$totalTax,$responsible,$odl_id,$tid);
        realignBalance($dbh,$uid,$tid);
        $payAmt    +=$chrgAmt + $totalTax ;

    }
    $tid=0;
    if($payAmt>0){
       my $balance =trans_getBalance($dbh,$uid);
       my $transCode =4; #payment 
       my $description = 'payment by ' . $responsible;
       $balance -= $payAmt;
       my $sth = $dbh->prepare($sql_transaction);
       if($totalUnpaid){
           $payAmt = $totalUnpaid; 
       }
        
       $sth->execute($uid,$payAmt,$balance,$description,$transCode);
       $tid= $dbh->{'mysql_insertid'};
       my $sth_transDetailList=$dbh->prepare("select odl_id,responsible,materials,rate,fineAmount,forgivenAmount,amount,taxes from opl_transactiondetail where tid=?");
       my $sth_trans = $dbh->prepare($sql_transactionDetail);
       foreach my $t(@$payArr){
            $sth_transDetailList->execute($t->{'tid'});
            if(my ($odl_id,$responsible,$materials,$rate,$fineAmount,$forgivenAmount,$amount,$taxes)=$sth_transDetailList->fetchrow_array){
                $sth_trans->execute($tid,$odl_id,$responsible,$materials,0,$rate,$fineAmount,$forgivenAmount, $amount,$taxes);
            }
       }
       $sth->finish;
       $sth_trans->finish;
       $sth_transDetailList->finish;
     }
    return $tid;
}
#-------------------------------------------------------------------------------
sub realignBalance{
    my($dbh,$uid,$tid)=@_;
    my $balance=0;
    my $sth_balance =$dbh->prepare("select balance from opl_transactions  where tid=?");
    my $sth_tidAmt =$dbh->prepare("select tid,amount from opl_transactions  where uid=? && tid > ? order by tid");
    my $sth =$dbh->prepare("update opl_transactions  set balance= ?  where tid= ?");
    $sth_balance->execute($tid);
    if(($balance) =$sth_balance->fetchrow_array){
        $sth_tidAmt->execute($uid,$tid);
        while(my ($tid,$amt)=$sth_tidAmt->fetchrow_array){
            $balance +=$amt;
            $sth->execute($balance,$tid);
        }
    }

}

#-------------------------------------------------------------------------------
sub trans_doRefund{
    my($dbh,$uid,$responsible,$amount)=@_;
    my $description = 'refund by ' . $responsible;
    my $transCode =8; #refund 
    my $balance =trans_getBalance($dbh,$uid);
    $balance += $amount;
    my $sth = $dbh->prepare($sql_transaction);
    $sth->execute($uid,$amount,$balance,$description,$transCode);
    my $tid= $dbh->{'mysql_insertid'};

    my @refundTbl = trans_getUnrefundLostList($dbh,$uid);
    my $sth_Insert_tranDetail = $dbh->prepare($sql_transactionDetail);
    foreach my $r(@refundTbl){
        $sth_Insert_tranDetail->execute($tid,$r->{"odl_id"},$r->{"responsible"},$r->{"materials"},0,$r->{"rate"},
                                 $r->{"fineAmount"},$r->{"forgivenAmount"}, $r->{"damount"},$r->{"taxes"});
    }
    $sth_Insert_tranDetail->finish;

    
    $dbh->do("update opl_transactions set  settleDate=now() where uid=$uid && code in(1,2,3,6) && settleDate is null");
    $sth->finish;
    
    return $tid;
    
}

#-------------------------------------------------------------------------------
sub trans_getReceiptDetail{
   my($dbh,$tid)=@_;
   my @rList=();

   my $sth_trans =$dbh->prepare(<<_STH_);
   select t.tid,t.uid,t.amount totalAmount,DATE_FORMAT(t.date,'%m/%d/%Y') as date ,t.code 
   from opl_transactions t 
   where t.tid=?
_STH_
  my $sth_transDetail =$dbh->prepare(<<_STH_);
   select o.type, d.odl_id,d.materials, d.amount  + d.taxes as amount
   from opl_transactiondetail d inner join opl_odl o on o.odl_id=d.odl_id
   where tid=?
_STH_

 $sth_trans->execute($tid);
my $receipt;
my $taxes=0;
if($receipt=$sth_trans->fetchrow_hashref){
    $sth_transDetail->execute($tid);
     while ( my $tDetail = $sth_transDetail->fetchrow_hashref ){   
        my $itemInfo =getItemInfo($dbh,$tDetail->{'odl_id'});
            $tDetail->{'title'}=$itemInfo->{'title'};
            $tDetail->{'barcode'}=$itemInfo->{'barcode'};
        push @{$receipt->{'transList'}},$tDetail;
     }
    
}
    
    $sth_trans->finish;
    $sth_transDetail->finish;
 
    return ($receipt); 
}



#-------------------------------------------------------------------------------
sub trans_getUnrefundLostList{
   my($dbh,$uid)=@_;
   my @rList=();

   my $query =<<_QRY_;
select   t.tid,t.uid,t.amount as tamount ,t.balance ,t.date,t.description,
         d.odl_id,d.materials,d.rate,d.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible 
from opl_transactions t left  outer join 
         opl_transactiondetail d  on t.tid=d.tid
where   t.code = 6 && 
        t.uid =?  && t.settleDate is null 
order by t.tid 
_QRY_
    my $sth = $dbh->prepare($query);
    $sth->execute($uid);

=item
   my $rootLostQuery =<<_QRY_;
select d.tid from opl_transactiondetail d inner join opl_transactions t on t.tid=d.tid  
where d.odl_id=? && code =2 order by tid desc limit 1
_QRY_
   my $sth_rootLostId = $dbh->prepare($rootLostQuery);
=cut
 
  while ( my $rec = $sth->fetchrow_hashref ){        
        my $tid = $rec->{'tid'};
        my @taxDetail =  trans_getTaxes4Transaction($dbh,$tid);
        my $m = scalar(@taxDetail);
        my $itemInfo =getItemInfo($dbh,$rec->{'odl_id'});

       # $sth_rootLostId->execute($rec->{'odl_id'});
       # my ($org_tid) = $sth_rootLostId->fetchrow_array;
        push @rList, {
            tid          => $rec->{'tid'},
            title        =>$itemInfo->{'title'},
            barcode      =>$itemInfo->{'barcode'},
            tamount      => $rec->{'tamount'},
            balance      => $rec->{'balance'},
            description  => $rec->{'description'},
            odl_id       => $rec->{'odl_id'},
            materials    => $rec->{'materials'},
            damount      => $rec->{'damount'},
            overdueDays  => $rec->{'overdueDays'},
            taxes        => $rec->{'taxes'},
            fineAmount        => $rec->{'fineAmount'},
            forgivenAmount    => $rec->{'forgivenAmount'},
            responsible       => $rec->{'responsible'},
            date         => substr ($rec->{'date'},0, 10), #date_text($rec->{'date'}, 0),
            rate         => $rec->{'rate'},
            taxDetail    => \@taxDetail,
            numOfTax     => $m,
           # org_tid     => $org_tid
            
        };
    }
    
    $sth->finish;
   # $sth_rootLostId->finish;
 
    return @rList; 
}
#-------------------------------------------------------------------------------
sub trans_getFineDetail{
    my($dbh,$tid)=@_;
    my $retTrans;

    # opl_transactions - code
    # 1:overdue; 2:lost; 3:damaged;

    my $query = <<_QRY_;
select   t.tid,t.uid,t.amount as tamount ,t.balance ,t.date,t.description,
         d.odl_id,d.materials,d.rate,d.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible 
from opl_transactions t left  outer join 
         opl_transactiondetail d  on t.tid=d.tid
where    t.tid =?  &&  code in(1,2,3)
_QRY_

    my $sth = $dbh->prepare($query);

    $sth->execute($tid);
    if($retTrans =  $sth->fetchrow_hashref ){        
        my @taxDetail =  trans_getTaxes4Transaction($dbh,$tid);
        my $m = scalar(@taxDetail);
        my $payment =0;
        my $itemInfo =getItemInfo($dbh,$retTrans->{'odl_id'});
         $retTrans->{'title'}     = $itemInfo->{'title'};
         $retTrans->{'barcode'}   = $itemInfo->{'barcode'};
         $retTrans->{'date'}      = substr($retTrans->{'date'},0, 10);
         $retTrans->{'taxDetail'} = \@taxDetail;
            
    }
    
    $sth->finish;

    return $retTrans; 
    
}

#-------------------------------------------------------------------------------
sub trans_getUnpaidFineList{
    my($dbh,$uid,$dir,$offset,$size)=@_;
    my @transTbl=();
    
    my $query = <<_QRY_;
select   t.tid,t.uid,t.amount as tamount ,t.balance ,t.date,t.description,
         d.odl_id,d.materials,d.rate,d.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible 
from opl_transactions t left  outer join 
         opl_transactiondetail d  on t.tid=d.tid
where   t.code in(1,2,3) && 
        t.uid =?  && t.settleDate is null 
order by t.tid $dir

_QRY_

         
 if($offset !=undef  && $size !=undef  &&  $size >0) {  
    $query .= <<_QRY_;
        limit $offset,$size

_QRY_
   
}
    my $sth = $dbh->prepare($query);

    $sth->execute($uid);
    while ( my $rec = $sth->fetchrow_hashref ){        
        my $tid = $rec->{'tid'};
        my @taxDetail =  trans_getTaxes4Transaction($dbh,$tid);
        my $m = scalar(@taxDetail);
        my $itemInfo =getItemInfo($dbh,$rec->{'odl_id'});
        push @transTbl, {
            tid          => $rec->{'tid'},
            title        =>$itemInfo->{'title'},
            barcode      =>$itemInfo->{'barcode'},
            tamount      => $rec->{'tamount'},
            balance      => $rec->{'balance'},
            description  => $rec->{'description'},
            odl_id       => $rec->{'odl_id'},
            materials    => $rec->{'materials'},
            damount      => $rec->{'damount'},
            overdueDays  => $rec->{'overdueDays'},
            taxes        => $rec->{'taxes'},
            fineAmount        => $rec->{'fineAmount'},
            forgivenAmount    => $rec->{'forgivenAmount'},
            responsible       => $rec->{'responsible'},
            date         => substr ($rec->{'date'},0, 10), #date_text($rec->{'date'}, 0),
            rate         => $rec->{'rate'},
            taxDetail    => \@taxDetail,
            numOfTax     => $m
            
        };
    }
    
    $sth->finish;
 
    return @transTbl; 
    
}

#-------------------------------------------------------------------------------
sub trans_getFineListDetail{
    my($dbh,$uid,$dir,$type,$offset,$size)=@_;
    my @transTbl=();
    
    my $query = <<_QRY_;
select   t.tid,t.uid,t.amount as tamount ,t.balance ,t.date,t.description,
         d.odl_id,d.materials,d.rate,d.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible 
from opl_transactions t left  outer join 
         opl_transactiondetail d  on t.tid=d.tid
where    t.description not regexp 'payment|forgive' && t.uid =?   
_QRY_

if($type ne 'all'){
   $query .= <<_QRY_;
        && t.description = '$type' 
_QRY_
}
    $query .= <<_QRY_;
        order by t.tid $dir
_QRY_
        
 if($offset !=undef  && $size !=undef  &&  $size >0) {  
    $query .= <<_QRY_;
        limit $offset,$size

_QRY_
   
}
    my $sth = $dbh->prepare($query);

    $sth->execute($uid);
    while ( my $rec = $sth->fetchrow_hashref ){        
        my $tid = $rec->{'tid'};
        my @taxDetail =  trans_getTaxes4Transaction($dbh,$tid);
        my $m = scalar(@taxDetail);
        my $payment =0;
        my $forgive = 0;
        if ($rec->{'description'} =~ /payment/){
            $payment =1;
        }
        elsif ($rec->{'description'} =~ /forgive/){
            $forgive =1;
        }
        my $itemInfo =getItemInfo($dbh,$rec->{'odl_id'});
        push @transTbl, {
            tid          => $rec->{'tid'},
            title        =>$itemInfo->{'title'},
            barcode      =>$itemInfo->{'barcode'},
            tamount      => $rec->{'tamount'},
            balance      => $rec->{'balance'},
            description  => $rec->{'description'},
            payment      => $payment,
            forgive      => $forgive,
            odl_id       => $rec->{'odl_id'},
            materials    => $rec->{'materials'},
            damount      => $rec->{'damount'},
            overdueDays  => $rec->{'overdueDays'},
            taxes        => $rec->{'taxes'},
            fineAmount        => $rec->{'fineAmount'},
            forgivenAmount    => $rec->{'forgivenAmount'},
            responsible       => $rec->{'responsible'},
            date         => substr ($rec->{'date'},0, 10), #date_text($rec->{'date'}, 0),
            rate         => $rec->{'rate'},
            taxDetail    => \@taxDetail,
            numOfTax     => $m
            
        };
    }
    
    $sth->finish;

    return @transTbl; 
    
}

sub trans_getPaymentList{
    my($dbh,$uid,$dir,$offset,$size)=@_;
    my @transTbl=();
    
    my $query = <<_QRY_;
select   t.tid,t.uid,abs(t.amount) as tamount ,t.balance ,t.date,t.description, d.odl_id
from     opl_transactions t left outer join opl_transactiondetail d on t.tid=d.tid
where    t.uid =? && (code=4  or code=6)
_QRY_


$query .=<<_QRY_;
order by t.tid $dir
_QRY_

          
 if($offset !=undef  && $size !=undef  &&  $size >0) {  
    $query .= <<_QRY_;
        limit $offset,$size

_QRY_
   
}
    my $sth = $dbh->prepare($query);

    $sth->execute($uid);
    while ( my $rec = $sth->fetchrow_hashref ){ 
         if($rec->{"odl_id"}){
             my $itemInfo =getItemInfo($dbh,$rec->{'odl_id'});
             $rec->{"barcode"} = $itemInfo->{'barcode'};
             $rec->{"title"}   = $itemInfo->{'title'};

         }
         push @transTbl,$rec;
   }
    
    $sth->finish;

    return @transTbl; 
    
}


#-------------------------------------------------------------------------------
sub trans_getODL{
    my($dbh,$uid,$dir,$type,$offset,$size)=@_;
    my @transTbl=();
    my $query = <<_QRY_;
select  f.odl_id as odlid, f.idloan, l.uid,t.tid,t.uid,t.amount as tamount ,t.balance ,t.date,t.description,t.settleDate,
         d.odl_id,d.materials,d.rate,d.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible  
from     opl_odl f   left outer join opl_loan l on f.idloan=l.idloan   
         left  outer join opl_transactiondetail d on f.odl_id=d.odl_id  
         left  outer join opl_transactions t on t.tid=d.tid
where    l.uid = ? && t.code= ?
_QRY_

        
 if($offset !=undef  && $size !=undef  &&  $size >0) {  
    $query .= <<_QRY_;
        limit $offset,$size

_QRY_
   
}
    my $sth = $dbh->prepare($query);
    $sth->execute($uid,$type);
  
         my ($tid,$title,$barcode,$tamount,$balance,$description,$materials,
            $damount,$overdueDays,$taxes,$fineAmount,$forgivenAmount,
            $responsible,$date,$rate,$payment,$forgive ,$odl_id,$settled) =
            (-1,"","",0.00,0.00,$type,"",
            0.00,0,"",0.00,0.00,
            "","",0.00,0.00,0.00,0);  
   while ( my $rec = $sth->fetchrow_hashref ){
        $tid = $rec->{'tid'};
        $settled=0;
              
        my $itemInfo =getItemInfo($dbh,$rec->{'odlid'});
            $title                = $itemInfo->{'title'};
            $barcode              = $itemInfo->{'barcode'};
  
        if ($tid >0){
            $tid                  = $rec->{'tid'};
            $tamount              = $rec->{'tamount'};
            $balance              = $rec->{'balance'};
            $description          = $rec->{'description'};
            $odl_id               = $rec->{'odl_id'};
            $materials            = $rec->{'materials'};
            $damount              = $rec->{'damount'};
            $overdueDays          = $rec->{'overdueDays'};
            $taxes                = $rec->{'taxes'};
            $fineAmount           = $rec->{'fineAmount'};
            $forgivenAmount       = $rec->{'forgivenAmount'};
            $responsible          = $rec->{'responsible'};
            $date                 = date_text($rec->{'date'});
            $rate                 = $rec->{'rate'};
            $settled              = 1 if($rec->{'settleDate'});
        }

        push @transTbl, {
            tid                  => $tid ,          
            title                => $title,         
            barcode              => $barcode,       
            type                 => $type,       
            tamount              => $tamount ,      
            balance              => $balance ,      
            description          => $description,   
            payment              => $payment,       
            forgive              => $forgive ,      
            odl_id               => $odl_id ,       
            materials            => $materials,     
            damount              => $damount ,      
            overdueDays          => $overdueDays,   
            taxes                => $taxes,         
            fineAmount           => $fineAmount,    
            forgivenAmount       => $forgivenAmount,
            responsible          => $responsible ,  
            date                 => $date,          
            rate                 => $rate,  
            settled              => $settled,          
        };

  
    }
    $sth->finish;
    return @transTbl; 
    
}

#-------------------------------------------------------------------------------

sub getItemInfo(){
    my ($dbh,$odl_id)=@_;
    my $query="select l.barcode,m.title from opl_loan l inner join opl_odl o on o.idloan=l.idloan 
inner join opl_item i on l.barcode=i.barcode inner join opl_marcRecord m on i.rid=m.rid
where o.odl_id=?";
    my $sth = $dbh->prepare($query); 
    $sth->execute($odl_id)  ;


    my $rec=$sth->fetchrow_hashref;    
    $sth->finish;
    return $rec;
}
#-------------------------------------------------------------------------------
sub trans_getTaxes4Transaction{
    my ($dbh,$tid)=@_;
    my $sth_tax = $dbh->prepare("select * from opl_taxcalcdetail where tid = ?");
    $sth_tax->execute($tid);
    
    my @taxes;
    my $t; 
    my $i=0;
    while (my $taxes = $sth_tax->fetchrow_hashref) {
        $t={};
        $t->{'taxName'}       = $taxes->{'taxName'};
        $t->{'taxRate'}       = $taxes->{'taxRate'};
        $t->{'taxBase'}       = $taxes->{'taxBase'};
        $t->{'taxableAmount'} = $taxes->{'taxableAmount'};
        $t->{'tax'}           = $taxes->{'tax'};
        $t->{'taxID'}         = $taxes->{'id'};        
        $t->{'firstR'}        = ($i == 0)? 1: 0;
        $i++;
        push @taxes,$t;
   }
   #$sth_tax->finish;
   return @taxes;

}


#-------------------------------------------------------------------------------
sub trans_getStatementReport{
}

#-------------------------------------------------------------------------------
sub trans_getFineType{
    my ($dbh,$odl_id)=@_;
    my $type="";
    my $sth = $dbh->prepare("select type from opl_odl where odl_id = ? ");
    $sth->execute($odl_id);
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $type = $rec->{'type'}; 
    }
    $sth->finish;
    return $type;
 }

#-------------------------------------------------------------------------------
sub trans_getTaxTable{
    my ($dbh)=@_;
    my @taxTbl=();
    my $sth = $dbh->prepare("select * from opl_taxes order by id");
    $sth->execute();
    my $idx=0;
    while ( my $rec = $sth->fetchrow_hashref ){
        $rec->{"idx"}=$idx++;
        push @taxTbl, $rec;
    }
    $sth->finish;

    return @taxTbl; 
    
}

#-------------------------------------------------------------------------------
sub trans_getRate{
    my ($dbh,$id)=@_;
    my $sth = $dbh->prepare("select * from opl_fineRate where id=?");
    $sth->execute($id);
     my $rec = $sth->fetchrow_hashref;
     $sth->finish;
 
    return $rec ; 
}
#-------------------------------------------------------------------------------
sub trans_getBalance{
    my ($dbh,$uid)=@_;
    my $balance=0;
    my $sth = $dbh->prepare("select balance from opl_transactions where uid = ? order by tid desc limit 1");
    $sth->execute($uid);
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $balance = $rec->{'balance'}; 
    }
    $sth->finish;
    return $balance;
 }
 
#-------------------------------------------------------------------------------
sub trans_taxCalc{ 
    my ($dbh,$amount)=@_;
    my @taxTbl = trans_getTaxTable($dbh);
    my $tax = 0;
    my $totalTax=0;
    my $base=0;
    for (my $t; $t<scalar(@taxTbl);$t++){
        if($taxTbl[$t]->{'type'} eq '%'){
            $base= $amount;
            if($taxTbl[$t]->{"calFormat"} eq 'taxontax'){
                $base +=  $totalTax;
            }                   
            $tax = ($base * $taxTbl[$t]->{"rate"})/100;
            $tax = sprintf("%.2f",$tax);
        }
        else{ #case flat rate;
            $tax =$taxTbl[$t]->{"rate"};       
        }
        $totalTax += $tax;
        $taxTbl[$t]->{"taxableAmount"}=$amount;
        $taxTbl[$t]->{"tax"}=$tax;       
    }
    return @taxTbl;
}
#-------------------------------------------------------------------------------
sub reverseTaxcalc{
    my($dbh,$amount)=@_;
    my($amtBeforeTax,$tax)=($amount,0);

    return ($amtBeforeTax,$tax);
}

#-------------------------------------------------------------------------------
sub isTransRecorded(){
    my ($dbh,$odl_id)=@_;
    my $tid=0;
    my $sth = $dbh->prepare("select tid from opl_transactiondetail where odl_id = ?");
    $sth->execute($odl_id);
    my $rec =$sth->fetchrow_hashref;
    if($rec){
        $tid =$rec->{'tid'};
    }
    $sth->finish;
    return $tid;
    
}


#-------------------------------------------------------------------------------
sub  trans_doReverseLost{
    my ($dbh,$bc,$loginuid)=@_;

#   1- get idloan
#   2- get odl and fine charged amount
#   3- if charge exists, make a refund transaction.
my $rfd_tid=0;
my $sth = $dbh->prepare("select l.uid,d.odl_id, d.tid,d.materials,d.rate,d.fineAmount,d.forgivenAmount,d.amount,d.taxes,l.dateDue
            from opl_transactiondetail d 
            inner join opl_odl o on d.odl_id=o.odl_id 
            inner join opl_loan l on l.idloan=o.idloan 
            where l.barcode=? && o.type='lost' 
            order by o.odl_id desc limit 1");

    my $transCode =6; # credit lost refund
    $sth->execute($bc);
    if(my ($uid,$odl_id,$tid,$materials,$rate,$fineAmount,$forgivenAmount,$amount,$taxes,$dateDue)=$sth->fetchrow_array){
        my $balance= trans_getBalance($dbh,$uid);
        my $sth_trans = $dbh->prepare($sql_transaction);
        my $refundCredit= -1*($amount + $taxes);
        $balance +=$refundCredit;
        $sth_trans->execute($uid,$refundCredit,$balance,'refund lost',$transCode);
        $rfd_tid= $dbh->{'mysql_insertid'};
        $sth_trans = $dbh->prepare($sql_transactionDetail);
        
            
        $sth_trans->execute($rfd_tid,$odl_id,$loginuid,$materials,0,$rate,$fineAmount,$forgivenAmount, $amount,$taxes);
        $sth_trans->finish;
        $sth=$dbh->prepare("select settleDate from opl_transactions where tid=?");
        $sth->execute($tid);
        if (my ($sDate)=$sth->fetchrow_array){
            if($sDate eq ''){
                $dbh->do("update opl_transactions set settleDate=now() where tid= $tid or tid= $rfd_tid") ;
                $dbh->do("update opl_odl set settleDate=now() where odl_id= $odl_id") ;
            }
        }
    }
    $sth->finish;
    return $rfd_tid;
}
#-------------------------------------------------------------------------------
sub trans_getTotalCharge{
    my ($dbh,$uid)=@_;
    my $totalCharge=0;

    my $query = <<_QRY_;
select   sum(amount) as totalCharge  
from opl_transactions  
where   code in(1,2,3) && 
        uid =?  && settleDate is null 
_QRY_

my $sth = $dbh->prepare($query);
$sth->execute($uid);
my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $totalCharge = $rec->{'totalCharge'}; 
    }
    $sth->finish;
    return $totalCharge;

}
#-------------------------------------------------------------------------------
sub trans_getTotalFgvPmt{
    my ($dbh,$uid)=@_;
    my $totalFgvPmt=0;
    my $query = <<_QRY_;
select   sum(amount) as   totalFgvPmt
from opl_transactions 
where   code in(4,5) && 
        uid =?  && settleDate is null 
_QRY_

my $sth = $dbh->prepare($query);
$sth->execute($uid);
my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $totalFgvPmt = $rec->{'totalFgvPmt'}; 
    }
    $sth->finish;
    return $totalFgvPmt;
}