package	Opals::Tb_Transactions;


use	Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    trans_recordFine
    trans_doPayment
    trans_doRefund
    trans_doReverseLost
    trans_doReverseDamaged
    trans_getPaymentList
    trans_getFineDetail
    trans_getFineListDetail
    trans_getUnpaidFineList
    trans_getStatementReport
    trans_getTaxTable
    trans_getRate
    trans_getBalance
    trans_tb_getBalance
    trans_getODL
    trans_getUnrefundLostList
    trans_getReceiptDetail
   
    trans_getInfoByUid
 
    trans_getDetailBalance
    
    trans_getTotalCharge
    trans_getTotalForgiven
    trans_getTotalPaid
    trans_getTotalRefundLost
    trans_getTotalRefund
    trans_getPreviousPaid
    trans_getPreviousBalance
    
  );
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;
=item
my $sql_transaction =" 
insert into tb_transactions
set     uid         = ?,
        amount      = ?,
        balance     = ?,
        description = ?,
        date        =now(),
        code        =?
";
=cut

my $sql_transaction =" 
insert into tb_transactions
set     ptid        = ?,
        uid         = ?,
        amount      = ?,
        balance     = ?,
        description = ?,
        date        =now(),
        code        =?
";
  
my $sql_transactionDetail ="
insert into tb_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 $ptid = 0;
    my $sth = $dbh->prepare($sql_transaction);
    $sth->execute($ptid, $uid,$amount+$totalTax,$balance,$note,$transCode);
    my $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;
=item    
   # set as settled if charge=0 so it will not be listed as unpaid; 
   if($amount ==0){
       $dbh->do("update tb_transactions set  settleDate=now() where   tid=$tid");
       $dbh->do("update tb_odl set  settleDate=now(),days_overdue= $numOD  where   odl_id = $ODL_id");
   }
   else{
       $dbh->do("update tb_odl set days_overdue= $numOD  where odl_id = $ODL_id");
   }
=cut
   return  $tid;
}

sub trans_doPayment{
   
   my($dbh,$uid,$responsible,$payArr,$payAmount)=@_;

   my $sth_update_odl         =$dbh->prepare("update opl_odl set settleDate=now() where odl_id= ?");
   my $sth_update_trans_settleDate= $dbh->prepare("update opl_transactions set settleDate=now() where settleDate is null && tid =? ");
   my $sth_update_trans       =$dbh->prepare("update tb_transactions set  balance=(balance -amount) + ?, amount=? where tid=?");
   my $sth_update_transDetail =$dbh->prepare("update tb_transactiondetail set forgivenAmount=  ? , amount =? , taxes=?,responsible=? where odl_id=? && tid=?");

   my $transCode = {
        4 =>'payment',
        5=> 'forgive',
        8=>'refund'
    };
   my $sth_insert_trans = $dbh->prepare($sql_transaction);

   my ($tid,$odl_id,$fgvAmt,$chrgAmt,$payAmt,$totalTax)=(0,0,0,0,0,0.00);
   foreach my $trans(@$payArr){
        $tid        =$trans->{'tid'};
        $odl_id     =$trans->{'odl_id'};
        $fgvAmt     =$trans->{'forgiveAmount'};
        $chrgAmt    =$trans->{'chargeAmount'};
        $sth_update_trans->execute($chrgAmt,$chrgAmt, $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;
        $sth_insert_trans->execute(0,$uid,$payAmount,$newBalance,$description,$transCode);
        $tid= $dbh->{'mysql_insertid'};
        if ($newBalance == 0){
            foreach my $t(@$payArr){
                my $paid_tid =$t->{'tid'};
                $sth_update_odl->execute($t->{'odl_id'});
                $sth_update_trans_settleDate->execute($t->{'tid'});    
            }
            $sth_update_trans    = $dbh->prepare("update tb_transactions set settleDate=now() where settleDate is null && uid=? && tid <=?");
            $sth_update_trans->execute($uid,$tid);
            $sth_update_odl->finish;
            $sth_update_trans->finish;
        }
    }
    elsif($balance ==0){
        $sth_update_trans    = $dbh->prepare("update tb_transactions set settleDate=now() where settleDate is null && uid=? ");
        $sth_update_trans->execute($uid);
    }
    $sth_insert_trans->finish;
    return $tid;
}
#-------------------------------------------------------------------------------
sub realignBalance{
    my($dbh,$uid,$tid)=@_;
    my $balance=0;
    my $sth_balance =$dbh->prepare("select balance from tb_transactions  where tid=?");
    my $sth_tidAmt =$dbh->prepare("select tid,code,amount from tb_transactions  where uid=? && tid > ? order by tid");
    my $sth =$dbh->prepare("update tb_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 -=$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);

    my $sth = $dbh->prepare($sql_transaction);
    $sth->execute(0, $uid, abs($amount), $balance +  abs($amount),$description,$transCode);
    my $tid= $dbh->{'mysql_insertid'};

    $dbh->do("update tb_transactions set settleDate=now() where uid=$uid && code in(1,2,3,6) && settleDate is null");

    $sth->finish;

    return $tid;
}
#use left outer join ..... for trans_getInfoByUid
#-------------------------------------------------------------------------------
sub trans_getInfoByUid {

    my ($dbh,$uid) = @_;

    my $query = <<_SQL_;
select t.*,d.* from tb_transactions t 
        inner join tb_transactiondetail d using(tid) 
        where t.uid =  ? 
_SQL_

    my @transList;
    my $sth = $dbh->prepare($query);
    $sth->execute($uid);
    while ( my $trans = $sth->fetchrow_hashref){
        my $paid = getPaidAmountUId($dbh, $uid)|| 0;
        my $forgive = getForgiveAmountByTransID($dbh, $trans->{'tid'}, $uid)|| 0;
        my $refundLost = getRefundLostAmountByTransID($dbh, $trans->{'tid'}, $uid)|| 0;
        my $refund =  getRefundAmountByTransID($dbh, $trans->{'tid'}, $uid)|| 0;
        $trans->{'paid'} = $paid;
        $trans->{'forgive'} = $forgive;
        $trans->{'refundLost'} = $refundLost;
        $trans->{'refund'} = $refund;
        $trans->{'tbalance'} = (($trans->{'fineAmount'} + $refund) - ($paid + $forgive + $refundLost));
        push @transList, $trans;
    }
    $sth->finish;
    return \@transList;
}

sub trans_getDetailBalance {
    my ($dbh,$uid,$tid) = @_;
    my ($totCharge,$totPaid,$totForgive,$totCreditLost, $totRefund, $totCredit,$totDebit) = (0,0,0,0,0,0,0,0,0,0);    

    my $sql_last0balanceTid ="select if(tid,tid,0)  as tid from (select max(tid) as tid from tb_transactions where uid=$uid && code in(4,5,6) && balance<=0 ";
    if (defined $tid && $tid > 0){
        $sql_last0balanceTid .= " && tid < $tid ";
    }
    $sql_last0balanceTid .= " ) as tmp";
    #select if (tid,tid,0) as tid from (select max(tid) as tid from tb_transactions where uid=2382 && code in(4,5,6) && balance<=0) as tmp;
    my $last0balanceTid = 0;
    my $sth= $dbh->prepare($sql_last0balanceTid);
    $sth->execute();
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $last0balanceTid = $rec->{'tid'};
    }

    $totCharge =  trans_getTotalCharge($dbh,$uid,$last0balanceTid,0 );
    $totPaid = trans_getTotalPaid($dbh,$uid,$last0balanceTid,0 );
    $totForgive = trans_getTotalForgiven($dbh,$uid,$last0balanceTid,0 );
    $totCreditLost =  trans_getTotalRefundLost($dbh,$uid,$last0balanceTid,0 );
    $totRefund = trans_getTotalRefund($dbh,$uid,$last0balanceTid );
    $totCredit = $totPaid;
    return ($totCharge,$totPaid,$totForgive,$totCreditLost, $totRefund,$totCredit,$totDebit);
}

sub trans_getReceiptDetail{

    my($dbh,$tid)=@_;

    my $sth_trans= $dbh->prepare(<<_STH_);
select tid, code, UPPER(description) as description, DATE_FORMAT(date,'%m/%d/%Y') as date, amount as totalAmount, uid 
from tb_transactions 
where tid= ? 
_STH_

$sth_trans->execute($tid);
my $receipt;
my $totalCharge=0;

my $sth_transDetail =$dbh->prepare(<<_STH_);
   select o.type, d.odl_id,d.materials, d.amount+ d.taxes as amount, DATE_FORMAT(t.date,'%m/%d/%Y') as date 
   from tb_transactiondetail d inner join tb_transactions t on t.tid=d.tid && d.tid < ? 
   inner join tb_odl o on o.odl_id=d.odl_id   
   where t.tid < ? && uid=? 
_STH_
#  where t.tid < ? && uid=? && t.tid > ?
    if($receipt=$sth_trans->fetchrow_hashref){
        my $sql_last0balanceTid ="select if(tid,tid,0)  as tid from (select max(tid) as tid from tb_transactions where uid= ? && code in(4,5,6) && balance<=0 ";
        if (defined $tid && $tid > 0){
            #$sql_last0balanceTid .= " && tid < $tid ";
        }
        $sql_last0balanceTid .= " ) as tmp";
        #select if (tid,tid,0) as tid from (select max(tid) as tid from tb_transactions where uid=2382 && code in(4,5,6) && balance<=0) as tmp;
        my $last0balanceTid = 0;
        my $sth= $dbh->prepare($sql_last0balanceTid);
        $sth->execute($receipt->{'uid'});
        my $rec = $sth->fetchrow_hashref;
        if ($rec){
            $last0balanceTid = $rec->{'tid'};
        }
        #$sth_transDetail->execute($tid,$tid,$receipt->{'uid'},$last0balanceTid);
        $sth_transDetail->execute($tid,$tid,$receipt->{'uid'});
        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); 
        my $previousPayment =trans_getPreviousPaid($dbh,$receipt->{'uid'},$tid,$last0balanceTid);
        my $totalRefundLost = trans_getTotalRefundLost($dbh,$receipt->{'uid'},$tid,$last0balanceTid );

        $receipt->{'previousPayment'} =$previousPayment;
        $receipt->{'totalCharge'} =$totalCharge;
        $receipt->{'refundLost'} = $totalRefundLost;
        my $balance =trans_getBalance($dbh,$receipt->{'uid'});
        $receipt->{'balance'} =sprintf("%.2f",$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 tb_transactions t left  outer join tb_transactiondetail d  on t.ptid=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);

    while ( my $t = $sth->fetchrow_hashref ){ 
        my $tid = $t->{'tid'};
        my $itemInfo =getItemInfo($dbh,$t->{'odl_id'});
        my @taxDetail =  trans_getTaxes4Transaction($dbh,$t->{'tid'});
        my $m = scalar(@taxDetail);
        push @rList, {
            tid             => $t->{'tid'},
            title           => $itemInfo->{'title'},
            barcode         => $itemInfo->{'barcode'},
            tamount         => $t->{'tamount'},
            balance         => $t->{'balance'},
            description     => $t->{'description'},
            odl_id          => $t->{'odl_id'},
            materials       => $t->{'materials'},
            overdueDays     => $t->{'overdueDays'},
            taxes           => $t->{'taxes'},
            fineAmount      => $t->{'fineAmount'},
            forgivenAmount  => $t->{'forgive'},
            responsible     => $t->{'responsible'},
            date         => substr ($t->{'date'},0, 10), #date_text($rec->{'date'}, 0),
            rate         => $t->{'rate'},
            taxDetail    => \@taxDetail,
            numOfTax     => $m,
        }
    }
 $sth->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 tb_transactions t left  outer join 
         tb_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 $balance =trans_getBalance($dbh,$uid);
    if ($balance <= 0){
        return @transTbl;
    }

    my $query = <<_QRY_;
select   t.tid,t.uid,t.amount as tamount,
         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 tb_transactions t left outer join 
         tb_transactiondetail d on t.tid=d.tid
where   t.uid =? && t.code in(1,2,3)  
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'});
        my $paidAmount  = getPaidAmountByTransID($dbh,$tid, $uid);
        my $forgivenAmount = getForgiveAmountByTransID($dbh,$tid,$uid );
        my $refundLostAmount =  getRefundLostAmountByTransID($dbh,$tid,$uid );
        my $refundAmount =  getRefundAmountByTransID($dbh,$tid,$uid );

        my $dueAmount = $rec->{'fineAmount'} - ($paidAmount + $forgivenAmount) - $refundLostAmount + $refundAmount;
        next if $dueAmount <= 0;    
        $forgivenAmount = sprintf("%.2f", $forgivenAmount);
        $paidAmount = sprintf("%.2f", $paidAmount);
        $dueAmount = sprintf("%.2f", $dueAmount);
        push @transTbl, {
            tid          => $rec->{'tid'},
            title        =>$itemInfo->{'title'},
            barcode      =>$itemInfo->{'barcode'},
            tamount      => $rec->{'tamount'},
            balance      => $rec->{'balance'},
            paidAmount   =>  $paidAmount,
            forgivenAmount=> $forgivenAmount,
            fgvPaidAmount=> ($paidAmount + $forgivenAmount) ,
            dueAmount    => $dueAmount,
            description  => $rec->{'description'},
            odl_id       => $rec->{'odl_id'},
            materials    => $rec->{'materials'},
            damount      => $rec->{'damount'},
            overdueDays  => $rec->{'overdueDays'},
            taxes        => $rec->{'taxes'},
            fineAmount   => $rec->{'fineAmount'},
            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 tb_transactions t left  outer join 
         tb_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 $paymentList;
   # code=4:payment; code=5:forgive; code=6:refund  
    my $query = <<_QRY_;
select   t.ptid, t.tid,t.uid,abs(t.amount) as tamount ,t.balance ,t.date,t.description, d.odl_id
from     tb_transactions t left outer join tb_transactiondetail d on t.ptid=d.tid
where    t.uid =? && (code=4  or code=6 or code=5 or code = 8) 

_QRY_


$query .=<<_QRY_;
order by t.ptid $dir , t.date 
_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"} ){
             if (!$paymentList->{$rec->{'ptid'}}){
               my $itemInfo =getItemInfo($dbh,$rec->{'odl_id'});
               $paymentList->{$rec->{'ptid'}}->{'title'} = $itemInfo->{'title'};
               $paymentList->{$rec->{'ptid'}}->{'barcode'} = $itemInfo->{'barcode'};
               $paymentList->{$rec->{'ptid'}}->{'barcode'} =~ s/^\_\_\_|\_\d\d\d$//g;
               }
             push @{$paymentList->{$rec->{'ptid'}}->{'transList'}}, $rec;      
         }
   }
   $sth->finish;
   foreach my $ptid(keys %{$paymentList}){
      $paymentList->{$ptid}->{'rows'} = scalar(@{$paymentList->{$ptid}->{'transList'}});
      push @transTbl, $paymentList->{$ptid};
   }
   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.amount as damount, d.overdueDays, d.taxes,
         d.fineAmount,d.forgivenAmount,d.responsible  
from     tb_odl f left outer join tb_loan l on f.idloan=l.id   
         left  outer join tb_transactiondetail d on f.odl_id=d.odl_id  
         left  outer join tb_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,$taxes,$fineAmount,$forgivenAmount,
            $responsible,$date,$rate,$payment,$forgive ,$odl_id,$settled) ;  
    my ($paidAmount ,$forgivenAmount,$refundLostAmount,$refundAmount,$dueAmount);
    while ( my $rec = $sth->fetchrow_hashref ){
       ($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,$typeStr,"",
            0.00,0,"",0.00,0.00,
            "","",0.00,0.00,0.00,0);
         ($paidAmount ,$forgivenAmount,$refundLostAmount,$refundAmount,$dueAmount) = (0,0,0,0,0);
        $tid = $rec->{'tid'};
        $date="";  
        $paidAmount = getPaidAmountByTransID($dbh,$tid, $uid);
        $forgivenAmount = getForgiveAmountByTransID($dbh,$tid,$uid );
        $refundLostAmount =  getRefundLostAmountByTransID($dbh,$tid,$uid );
        $refundAmount =  getRefundAmountByTransID($dbh,$tid,$uid );

        my $itemInfo =getItemInfo($dbh,$rec->{'odlid'});
            $title                = $itemInfo->{'title'};
            $barcode              = $itemInfo->{'barcode'};
            $barcode =~ s/^\_\_\_|\_\d\d\d$//g;
        $dueAmount = $rec->{'fineAmount'} - ($paidAmount + $forgivenAmount) - $refundLostAmount + $refundAmount;
        $date                 = date_text($rec->{'date'}) if ($rec->{'date'} != '');
        $settled              = 1 if($dueAmount ==0 );
        if ($tid >0){
            $tid                  = $rec->{'tid'};
            $tamount              = $rec->{'tamount'};
            $balance              = $rec->{'balance'};
            $description          = $rec->{'description'};
            $odl_id               = $rec->{'odl_id'};
            $materials            = $rec->{'materials'};
            $dueAmount            = $rec->{'damount'};
            $overdueDays          = $rec->{'overdueDays'};
            $taxes                = $rec->{'taxes'};
            $fineAmount           = $rec->{'fineAmount'};
            $forgivenAmount       = $rec->{'forgivenAmount'};
            $responsible          = $rec->{'responsible'};
            $rate                 = $rec->{'rate'};
        }

        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,r.fVal as title from tb_loan l inner join tb_odl o on o.idloan=l.id 
inner join tb_items i on l.barcode=i.barcode inner join tb_records r on i.rid=r.rid && r.fId = '245_a'
where o.odl_id=?";
    my $sth = $dbh->prepare($query); 
    $sth->execute($odl_id)  ;


    my $rec=$sth->fetchrow_hashref;    
    $sth->finish;
    return $rec;
}

#lost =  (opl_transCode)
sub getPaidAmountUId {

    my ($dbh,$uid ) = @_;
    my $ret = "0.00";
    my $query = "select SUM(amount) as amount from tb_transactions where uid = ? && code = 4 ";
    my $sth = $dbh->prepare($query); 
    $sth->execute($uid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $ret = $rec->{'amount'};
    }
    $sth->finish;
    return $ret;
}

sub getPaidAmountByTransID {

    my ($dbh,$tid, $uid ) = @_;
    my $ret = "0.00";
    my $query = "select SUM(amount) as amount from tb_transactions where uid = ? && code = 4 ";
    if (defined $tid &&  $tid > 0) {
        $query .= " && ptid = $tid ";
    }
    my $sth = $dbh->prepare($query); 
    $sth->execute($uid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $ret = $rec->{'amount'};
    }
    $sth->finish;
    return $ret;

}

#forgive = 5 (opl_transCode)
sub getForgiveAmountByTransID {
    my ($dbh,$tid,$uid) = @_;
    my ($ret,$amount) = ("0.00","0.00");
    my $query = "select SUM(amount) as amount from tb_transactions where uid = ? && code = 5 && ptid = ?";
    my $sth = $dbh->prepare($query); 
    $sth->execute($uid, $tid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $amount = $rec->{'amount'};
    }
    $query = "select forgivenAmount as amount from tb_transactiondetail where tid=?";
    $sth = $dbh->prepare($query);
    $sth->execute($tid);
    $rec = $sth->fetchrow_hashref;
    if ($rec){
        $amount += $rec->{'amount'};
    }
    $sth->finish;

    return $amount;
}
#refundLost = 6 (opl_transCode)
sub getRefundLostAmountByTransID {
    my ($dbh,$tid,$uid) = @_;
    my $ret = "0.00";
    my $query = "select SUM(amount) as amount from tb_transactions where uid = ? && code = 6 && ptid = ?";
    my $sth = $dbh->prepare($query); 
    $sth->execute($uid, $tid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $ret = $rec->{'amount'};
    }
    $sth->finish;

    return $ret;
}
#refund = 8 (opl_transCode)
sub getRefundAmountByTransID {
    my ($dbh,$tid,$uid) = @_;
    my $ret = "0.00";
    my $query = "select SUM(amount) as amount from tb_transactions where uid = ? && code = 8 && ptid = ?";
    my $sth = $dbh->prepare($query); 
    $sth->execute($uid, $tid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec){
        $ret = $rec->{'amount'};
    }
    $sth->finish;

    return $ret;
}

#-------------------------------------------------------------------------------
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 tb_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, $tid)=@_;
    my ($balance,$debit,$credit)=(0,0,0);
    
    my $queryDebit  =" select SUM(amount) as debit from tb_transactions where uid =? && code in (1,2,3,8)";
    my $queryCredit =" select SUM(amount) as credit from tb_transactions where uid =? && code in (4,5,6)";
=item    
    if ($tid && $tid > 0){
        $queryDebit .= " && tid = $tid ";
        $queryCredit .= " && tid = $tid ";
    }
=cut
    my $sth = $dbh->prepare($queryDebit);
    $sth->execute($uid);
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $debit = $rec->{'debit'} || 0; 
    }
    
    $sth = $dbh->prepare($queryCredit);
    $sth->execute($uid);
    $rec =$sth->fetchrow_hashref; 
    if($rec){
        $credit = $rec->{'credit'} || 0; 
    }
    $sth->finish; 
    $balance = $debit - $credit;
    return $balance;
 }
#--------------------------------------------------------------------------------#
sub trans_tb_getBalance {
    my ($dbh,$uid, $tid)=@_;

    return  trans_getBalance($dbh,$uid,$tid);
}

 
#-------------------------------------------------------------------------------
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 tb_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 tb_transactiondetail d 
            inner join tb_odl o on d.odl_id=o.odl_id 
            inner join tb_loan l on l.id=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 $forgive = getForgiveAmountByTransID($dbh, $tid, $uid)|| 0;
        my $refundCredit= $amount;  #($amount + $taxes) - $forgivenAmount - $forgive ;
        $balance -=$refundCredit;
        $sth_trans->execute($tid,$uid,$refundCredit,$balance,'refund lost',$transCode);
        $sth_trans->finish;
        $sth=$dbh->prepare("select settleDate from tb_transactions where tid=?");
        $sth->execute($tid);
        my $rfd_tid= $dbh->{'mysql_insertid'};
        if (my ($sDate)=$sth->fetchrow_array){
            if($sDate eq ''){
                $dbh->do("update tb_transactions set settleDate=now() where tid= $tid ") ;
                $dbh->do("update tb_odl set settleDate=now() where odl_id= $odl_id") ;
            }
        }
        if($balance==0){
            $dbh->do("update tb_transactions set settleDate=now() where uid=$uid && tid <$rfd_tid");
        }
    }
    $sth->finish;
    return 1;
}
sub trans_doReverseDamaged{


}

sub trans_getTotalAmountByCode {

    my ($dbh,$uid,$codeArr,$tid,$last0balanceTid)=@_;
    return 0 if $codeArr eq '';
    my $codes = join(",",@{$codeArr});
    my $total = 0;
    my $query = <<_QRY_;
select   sum(amount) as total
from tb_transactions  
where   code in ( $codes ) && uid = $uid && settleDate is null
_QRY_

    if (defined $tid && $tid > 0){
        $query .= " && tid > $tid ";
    }
    if (defined $last0balanceTid && $last0balanceTid > 0){
        $query .= " && tid < $last0balanceTid ";
    }
    my $sth = $dbh->prepare($query);
    $sth->execute();

    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $total = $rec->{'total'}; 
    }
    $sth->finish;
    return $total;
}

#-------------------------------------------------------------------------------
sub trans_getTotalCharge{
    my ($dbh,$uid,$tid)=@_;
    my @codes = (1,2,3);
    my $totalCharge = trans_getTotalAmountByCode($dbh,$uid,\@codes,$tid);
}
#-------------------------------------------------------------------------------
sub trans_getTotalPaid{
    my ($dbh,$uid,$tid )=@_;
    my $totalPaid=0;
    my @codes = (4);
    $totalPaid = trans_getTotalAmountByCode($dbh,$uid,\@codes,$tid);
    return $totalPaid;
}

sub trans_getTotalForgiven{
    my ($dbh,$uid,$tid)=@_;
    my $totalFgv=0;
    my @codes = (5);
    $totalFgv = trans_getTotalAmountByCode($dbh,$uid,\@codes,$tid);
    return $totalFgv;
}


sub trans_getTotalRefundLost{
    my ($dbh,$uid,$tid,$last0balanceTid)=@_;
    my @codes = (6);
    my $total = 0;
    my $query = <<_QRY_;
select   sum(amount) as total
from tb_transactions  
where   code in ( 6 ) && uid = $uid 
_QRY_

    if (defined $tid && $tid > 0){
        $query .= " && tid < $tid ";
    }
    if (defined $last0balanceTid && $last0balanceTid > 0){
        $query .= " && tid > $last0balanceTid ";
    }
    my $sth = $dbh->prepare($query);
    $sth->execute();

    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $total = $rec->{'total'}; 
    }
    $sth->finish;

    return $total;
}

sub trans_getTotalRefund{
    my ($dbh,$uid,$tid,$last0balanceTid)=@_;
    my $totalRefund=0;
    my @codes = (8);
    $totalRefund = trans_getTotalAmountByCode($dbh,$uid,\@codes,$tid,$last0balanceTid);
    return $totalRefund;
}

sub trans_getPreviousPaid{
    my ($dbh,$uid,$tid,$last0balanceTid)=@_;
    my $total = 0;
    my $query="select sum(amount) as total from tb_transactions where code in(4) && uid=$uid && tid < $tid && tid > $last0balanceTid ";
    my $sth = $dbh->prepare($query);
    $sth->execute();
    my $rec =$sth->fetchrow_hashref; 
    if($rec){
        $total = $rec->{'total'}; 
    }
    $sth->finish;
    return $total;
}

sub trans_getPreviousBalance {

    my ($dbh,$uid)=@_;
    my $balance=0;
    my $sth = $dbh->prepare("select balance from tb_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;
}