package	Opals::Transactions;


use	Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    trans_recordFine
    trans_recordFine_Ext
    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
    trans_priorFgvPmt
    trans_getPriorBalance
    trans_getLastBalance
  );
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
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

# 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   = ?,
        overdueHours   = ?,
        rate          = ?,
        hourlyRate    = ?,
        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)=@_;
    trans_recordFine_Ext($dbh,$uid,$ODL_id,$rate,0,$material,$daysOverdue,0,$forgivenAmnt,$charge,$loginuid);

}
#-------------------------------------------------------------------------------
sub trans_recordFine_Ext{
    my($dbh,$uid,$ODL_id,$rate,$hrRate,$material,$daysOverdue,$hrsOverdue,$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 $numHourOD=$hrsOverdue?$hrsOverdue: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,$numHourOD,$rate,$hrRate,$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 ,hours_overdue=$numHourOD where   odl_id = $ODL_id");
   }
   else{
       $dbh->do("update opl_odl set days_overdue= $numOD,hours_overdue=$numHourOD  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,$payAmount)=@_;
    my $sth_update_trans       =$dbh->prepare("update opl_transactions set  balance=(balance - amount + ?), amount=? where tid=?");
    my $sth_update_transDetail =$dbh->prepare("update opl_transactiondetail set forgivenAmount=?,amount=?,taxes=?,responsible=? where odl_id=? && tid=?");
    my $sth_update_settleDate    = $dbh->prepare("update opl_transactions set settleDate=now() where settleDate is null && uid= ?");
    my ($tid,$odl_id,$fgvAmt,$chrgAmt)=(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);

    }
    $tid=0;
    my $balance =trans_getBalance($dbh,$uid);
    if($balance>0  && $payAmount>0){
       my $transCode =4; #payment 
       my $description = 'payment by ' . $responsible;
       my $newBalance =$balance - $payAmount;
       my $sth = $dbh->prepare($sql_transaction);
       $sth->execute($uid,$payAmount,$newBalance,$description,$transCode);
       $tid= $dbh->{'mysql_insertid'};
       # recording payment detail e.g this payment applied for which items......
       my $sth_paymentInfo = $dbh->prepare('insert into opl_payRefundInfo set tid=?,paid_tid=?,date=now()');
       foreach my $t(@$payArr){
          my $paid_tid =$t->{'tid'};
          $sth_paymentInfo->execute($tid,$paid_tid);
       }
       if($newBalance == 0){
           my $sth_update_odl   = $dbh->prepare("update opl_odl set settleDate=now() where odl_id= ?");
           $sth_update_trans    = $dbh->prepare("update opl_transactions set settleDate=now() where settleDate is null && tid =? ");
           foreach my $t(@$payArr){
              $sth_update_odl->execute($t->{'odl_id'});
              $sth_update_trans->execute( $t->{'tid'});
          }
          $sth_update_trans    = $dbh->prepare("update opl_transactions set settleDate=now() where settleDate is null && uid=? && tid <=? ");
          $sth_update_trans->execute($uid,$tid);
          $sth_update_odl->finish;
          $sth_update_trans->finish;
       }

       $sth->finish;
       $sth_paymentInfo->finish;
     }
     elsif($balance==0){
        $sth_update_settleDate->execute($uid);
     }
    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,code,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,$code,$amt)=$sth_tidAmt->fetchrow_array){
            #debit for charge case 
            #1: overdue,2: lost,3: damaged,8: refund
            if($code ==1 ||$code ==2 || $code ==3 || $code ==8  ){
                $balance +=$amt;
            }
            #credit for case 
            # 4: payment, 5:forgive, 6:credit for lost refund
            else{
                $balance -=abs($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);
    # recording refund detail e.g this refund applied for which items......
    my $sth_refundInfo = $dbh->prepare('insert into opl_payRefundInfo set tid=?,paid_tid=?,date=now()');
    foreach my $t(@refundTbl){
        my $paid_tid =$t->{'tid'};
        $sth_refundInfo->execute($tid,$paid_tid);
    }

    $sth_refundInfo->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_payRefundInfo i on i.paid_tid=d.tid  
   inner join opl_odl o on o.odl_id=d.odl_id   
   where i.tid=?
   
_STH_

 $sth_trans->execute($tid);
my $receipt;
my $taxes=0;
my $totalCharge=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'};
            $totalCharge += $tDetail->{'amount'};
            $tDetail->{'type'} = uc($tDetail->{'type'});
        push @{$receipt->{'transList'}},$tDetail;
     }
    $totalCharge = sprintf("%.2f",$totalCharge); 
    $receipt->{'totalCharge'} =$totalCharge;

    my $priorPayment =trans_priorFgvPmt($dbh,$receipt->{'uid'},$tid);
    $receipt->{'priorPayment'} =$priorPayment;
    my $balance =trans_getBalance($dbh,$receipt->{'uid'});
    $receipt->{'balance'} =$balance;
    
}
    
    $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.hourlyRate,d.amount as damount, 
         d.overdueDays, d.overdueHours,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,$sort,$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.hourlyRate,d.amount as damount, 
         d.overdueDays,d.overdueHours, 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,9) && 
        t.uid =?  && t.settleDate is null 

_QRY_

 if(defined $sort && $sort ne ""){
    $query .= <<_QRY_;
     order by $sort $dir
_QRY_
 }
 else{
    $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 $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'},
            overdueHours  => $rec->{'overdueHours'},
            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'},
            hourlyRate   => $rec->{'hourlyRate'},
            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.hourlyRate,d.amount as damount, 
         d.overdueDays,d.overdueHours, 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'},
            overdueHours  => $rec->{'overdueHours'},
            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'},
            hourlyRate   => $rec->{'hourlyRate'},
            taxDetail    => \@taxDetail,
            numOfTax     => $m
            
        };
    }
    
    $sth->finish;

    return @transTbl; 
    
}

sub trans_getPaymentList{
    my($dbh,$uid,$dir,$offset,$size)=@_;
    my @transTbl=();
   # code=4:payment; code=5:forgive; code=6:refund  
    my $query = <<_QRY_;
select   t.tid,t.uid,abs(t.amount) as tamount ,t.balance ,t.date,t.description, d.odl_id,t.code transCode
from     opl_transactions t left outer join opl_transactiondetail d on t.tid=d.tid
where    t.uid =? && (code=4  or code=6 or code=5  or code=8)
_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->{"barcode"} =~ s/^\_\_\_|\_\d\d\d$//g;
             $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 ,f.ondate as date,t.description,t.settleDate,
         d.odl_id,d.materials,d.rate,d.hourlyRate,d.amount as damount, d.overdueDays, d.overdueHours, 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 = ? &&  f.type =? && (t.code =? || t.code is null)
_QRY_

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

_QRY_
   
}
    my $sth = $dbh->prepare($query);
    my $typeStr='';
    if($type ==1){
        $typeStr ='overdue';
    }
    elsif($type ==2){
        $typeStr ='lost';
    }
    elsif($type ==3){
        $typeStr ='damaged';
    }

    $sth->execute($uid,$typeStr,$type);
  
    my ($tid,$title,$barcode,$tamount,$balance,$description,$materials,
            $damount,$overdueDays,$overdueHours,$taxes,$fineAmount,$forgivenAmount,
            $responsible,$date,$rate,$hourlyRate,$payment,$forgive ,$odl_id,$settled) ;  
   while ( my $rec = $sth->fetchrow_hashref ){
       ($tid,$title,$barcode,$tamount,$balance,$description,$materials,
            $damount,$overdueDays,$overdueHours,$taxes,$fineAmount,$forgivenAmount,
            $responsible,$date,$rate,$hourlyRate,$payment,$forgive ,$odl_id,$settled) =
            (-1,"","",0.00,0.00,$typeStr,"",
            0.00,0,0,"",0.00,0.00,
            "","",0.00,0.00,0.00,0.00,0,0);
        $tid = $rec->{'tid'};
        $date="";  
        my $itemInfo =getItemInfo($dbh,$rec->{'odlid'});
            $title                = $itemInfo->{'title'};
            $barcode              = $itemInfo->{'barcode'};
            $barcode =~ s/^\_\_\_|\_\d\d\d$//g;
  
        $date                 = date_text($rec->{'date'}) if ($rec->{'date'} != '');
        $settled              = 1 if($rec->{'settleDate'} || $rec->{'fineAmount'}==0 );
        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'};
            $overdueHours         = $rec->{'overdueHours'};
            $taxes                = $rec->{'taxes'};
            $fineAmount           = $rec->{'fineAmount'};
            $forgivenAmount       = $rec->{'forgivenAmount'};
            $responsible          = $rec->{'responsible'};
            $rate                 = $rec->{'rate'};
            $hourlyRate           = $rec->{'hourlyRate'};
        }

        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,   
            overdueHours         => $overdueHours,
            taxes                => $taxes,         
            fineAmount           => $fineAmount,    
            forgivenAmount       => $forgivenAmount,
            responsible          => $responsible ,  
            date                 => $date,          
            rate                 => $rate,  
            hourlyRate           => $hourlyRate,
            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_getPriorBalance{
    my ($dbh,$uid)=@_;
    my $balance=0;
    my $sth = $dbh->prepare("select balance from opl_transactions where uid = ? order by tid desc limit 1,1");
    $sth->execute($uid);
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $balance = $rec->{'balance'}; 
    }

    $sth->finish;
    return $balance;
 }

#-------------------------------------------------------------------------------
# Wed, Jan 19, 2011 @ 12:27:22 EST
sub trans_getLastBalance{
    my ($dbh,$uid,$tid)=@_;
    my $balance=0;
    my $sql_pmt="select balance from opl_transactions where  uid=$uid";
    my $sql_last0balanceTid ="select if(tid,tid,0) from (select max(tid) as tid from opl_transactions where uid=$uid && code in(4,5,6) && balance<=0 ";
   
    if(defined $tid && $tid>0){
        $sql_pmt .=" && tid <$tid ";
        $sql_last0balanceTid .=" && tid < $tid";
    }


    my $query = "$sql_pmt && tid = ($sql_last0balanceTid) as tmp)";
    my $sth = $dbh->prepare($query);
    $sth->execute();
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $balance = $rec->{'balance'}; 
    }

    $sth->finish;
    return $balance;
 }
 
#-------------------------------------------------------------------------------
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 $tid = trans_getUnrefundTid4LostItem($dbh,$bc);
return $rfd_tid if($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 d.tid=?");

    my $transCode =6; # credit lost refund


    $sth->execute($tid);
    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,0,$rate,0,$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") ;
            }
        }
        if($balance<=0){
            $dbh->do("update opl_transactions set settleDate=now() where uid=$uid && tid <$rfd_tid");

        }
    }
    $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;
    $totalFgvPmt =trans_priorFgvPmt($dbh,$uid);
    return $totalFgvPmt;
}
#-------------------------------------------------------------------------------
sub trans_priorFgvPmt{
    my ($dbh,$uid,$tid)=@_;
    my $totalFgvPmt=0;
    my $sql_pmt="select sum(amount) as totalFgvPmt from opl_transactions where code in(4,5) && uid=$uid";
    my $sql_last0balanceTid ="select if(tid,tid,0) from (select max(tid) as tid from opl_transactions where uid=$uid && code in(4,5,6) && balance<=0 ";
    if(defined $tid && $tid>0){
        $sql_pmt .=" && tid <$tid ";
        $sql_last0balanceTid .=" && tid < $tid";
    }


    my $query = "$sql_pmt && tid > ($sql_last0balanceTid) as tmp)";
    my $sth = $dbh->prepare($query);
    $sth->execute();
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $totalFgvPmt = $rec->{'totalFgvPmt'}; 
    }
    $sth->finish;
    return $totalFgvPmt;
}
#-------------------------------------------------------------------------------
# Tue, Jun 12, 2012 @ 13:37:40 EDT
#  code =2 :item is marked lost in table opl_transactions
#  code =6 : user was refunded.
sub trans_getUnrefundTid4LostItem{
    my ($dbh,$barcode)=@_;
    my $retTid=0;
    my $query="select t.tid ,t.code from opl_loan l inner join opl_odl o using(idloan) inner join opl_transactiondetail d using(odl_id) inner join opl_transactions t using(tid) where l.barcode=? && code in (2,6) order by tid desc limit 1";
    my $sth = $dbh->prepare($query);
    $sth->execute($barcode);
    if(my ($tid,$code) =$sth->fetchrow_array){
        if($code ==2){
            $retTid=$tid;
        }  
    }

    $sth->finish;
    return $retTid;
 }