package	Opals::Transaction;


use	Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    trans_chargeOverdue
    trans_chargeLost
    trans_chargeDamage
    trans_chargeService
    trans_payment
    trans_credit_refund
    trans_payRefund
    trans_getCharge
    trans_adjustCharge
    trans_getChargeList
    trans_getUnpaidChargeList
    trans_getODL
    trans_getReceiptDetail
    trans_getReceiptList

    trans_doReverseLost


    trans_getBalance
    trans_getInvoice
);
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
);
use JSON;
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
use Opals::Context;
use Opals::Constant;


use constant    CHARGE_OVERDUE              => 1;
use constant    CHARGE_LOST                 => 2;
use constant    CHARGE_DAMAGE               => 3;
use constant    PAYMENT                     => 4;
use constant    FORGIVE                     => 5;
use constant    CREDIT_REFUND               => 6;
use constant    RATE_ADJUSTMENT             => 7;
use constant    PAY_REFUND                  => 8;
use constant    CHARGE_SERVICE              => 9;

use constant    CREDIT_USED                 => 10;
use constant    PAY_FROM_CREDIT             => 11 ;


#-------------------------------------------------------------------------------
sub trans_chargeOverdue{
    my($dbh,$respUid,$uid,$odl_id,$rate,$unit,$waive,$note,$chargeAmount,$date)=@_;
    my $params={};
    $params->{'code'}       =CHARGE_OVERDUE;
    $params->{'responsible'}=$respUid;
    $params->{'uid'}        =$uid;
    $params->{'rate'}       =$rate;
    $params->{'odl_id'}     =$odl_id;
    $params->{'unit'}       =$unit;
    $params->{'waive'}      =$waive;
    $params->{'note'}       =$note;
    #$params->{'chargeAmnt'} =$rate * $unit;
    $params->{'chargeAmnt'} =$chargeAmount;
    $params->{'totalCharge'}=$params->{'chargeAmnt'} - $waive;
    $params->{'balance'}    =$params->{'totalCharge'} ;
    $params->{'date'}       =$date if($date); 
    
    return _saveCharge($dbh,$params);

}

#-------------------------------------------------------------------------------
sub trans_chargeLost{
    my($dbh,$respUid,$uid,$odl_id,$rate,$waive,$svcFee,$note,$chargeAmount,$date)=@_;

    my $params={};
    $params->{'code'}       =CHARGE_LOST;
    $params->{'responsible'}=$respUid;
    $params->{'uid'}        =$uid;
    $params->{'rate'}       =$rate;
    $params->{'unit'}       =1;
    $params->{'odl_id'}     =$odl_id;
    $params->{'waive'}      =$waive;
    $params->{'note'}       =$note;
    $params->{'fee'}        =$svcFee;
    #$params->{'chargeAmnt'} =$rate  ;
    $params->{'chargeAmnt'} =$chargeAmount;
    $params->{'totalCharge'}=$params->{'chargeAmnt'} - $waive + $svcFee;
    $params->{'balance'}    =$params->{'totalCharge'} ;
    $params->{'date'}       =$date if($date  ); 
    
    return _saveCharge($dbh,$params);

}

#-------------------------------------------------------------------------------
sub trans_chargeDamage{
    my($dbh,$respUid,$uid,$odl_id,$rate,$waive,$note,$chargeAmount,$date)=@_;
    my $params={};
    $params->{'code'}       =CHARGE_DAMAGE;
    $params->{'responsible'}=$respUid;
    $params->{'uid'}        =$uid;
    $params->{'rate'}       =$rate;
    $params->{'unit'}       =1;
    $params->{'odl_id'}     =$odl_id;
    $params->{'waive'}      =$waive;
    $params->{'note'}       =$note;
    #$params->{'chargeAmnt'} =$rate;
    $params->{'chargeAmnt'} =$chargeAmount;
    $params->{'totalCharge'}=$params->{'chargeAmnt'} - $waive;
    $params->{'balance'}    =$params->{'totalCharge'} ;
    $params->{'date'}       =$date if($date ); 
    
    return _saveCharge($dbh,$params);
    
}

#-------------------------------------------------------------------------------
sub trans_chargeService{
    my($dbh,$respUid,$uid,$svc_id,$rate,$waive,$svcFee,$note,$date)=@_;
    my $params={};
    $params->{'code'}       =CHARGE_SERVICE;
    $params->{'responsible'}=$respUid;
    $params->{'uid'}        =$uid;
    $params->{'rate'}       =$rate;
    $params->{'svc_id'}     =$svc_id;
    $params->{'waive'}      =$waive;
    $params->{'note'}       =$note;
    $params->{'fee'}        =$svcFee;
    $params->{'chargeAmnt'} =$rate;
    $params->{'totalCharge'}=$params->{'chargeAmnt'} - $waive + $svcFee;
    $params->{'balance'}    =$params->{'totalCharge'} ;
    $params->{'date'}       =$date if($date ); 
    
    return _saveCharge($dbh,$params);
}
#-------------------------------------------------------------------------------
sub _saveCharge{
    my($dbh,$params)=@_;
    my $uid=$params->{'uid'};
    my @fields=qw(odl_id 
                svc_id     
                responsible
                uid
                materials  
                rate       
                unit       
                waive      
                creditRefund     
                chargeAmnt 
                fee        
                totalCharge
                balance    
                taxes      
                note
                date
                );       
    my @data=();
    my @tf=();
    foreach my $f(@fields){
        if(defined $params->{$f}){
            push @data,$params->{$f};
            push @tf,"$f = ?";
        }
    }
   
    my $sql= "insert into opl_charge set " . join(",",@tf);
    my $sth=$dbh->prepare($sql);
    $sth->execute(@data);
    my $cid= $dbh->{'mysql_insertid'};
    $sth->finish;
    if($cid>0){
        my $transParam={};
            $transParam->{'responsible'}=$params->{'responsible'};
            $transParam->{'uid'}=$params->{'uid'};
            $transParam->{'code'}=$params->{'code'};
            $transParam->{'cid'}=$cid;
            $transParam->{'amount'}=$params->{'totalCharge'};
            $transParam->{'balance'}=$params->{'balance'} + trans_getBalance($dbh,$uid);
            $transParam->{'date'}=$params->{'date'};
        
        _saveTrans($dbh,$transParam); 
    }
    return $cid;
}
#-------------------------------------------------------------------------------
sub _saveTrans{
    my($dbh,$params)=@_;
    my @fields=qw(cid 
                pid
                code     
                responsible
                uid  
                amount
                creditUsed_tid
                description
                date 
                );       
    my @data=();
    my @tf=();
    foreach my $f(@fields){
        if(defined $params->{$f}){
            push @data,$params->{$f};
            push @tf,"$f = ?";
        }
    }
    my $sql= "insert into opl_transaction set " . join(",",@tf);
    my $sth=$dbh->prepare($sql);
    $sth->execute(@data);
    my $tid= $dbh->{'mysql_insertid'};
    $sth->finish;
    return $tid;


}
#-------------------------------------------------------------------------------
sub trans_payment{
     my ($dbh,$respUid,$uid,$amtReceived,$payList,$description,$date,$payMethod,$tender,$change)=@_;
     $amtReceived=0.00 if($amtReceived<0);

     my @pays=();
     my $crCidList=[];
     my $availableCredit=0.0;

    foreach my $p(@$payList){
        if($p->{"payAmount"}>0){
            push @pays,$p
        }
        elsif($p->{"payAmount"}<0){
            push @$crCidList,$p->{"cid"};
            $availableCredit +=abs($p->{"payAmount"});
        }
    }
     my $availableFund= $amtReceived + $availableCredit;
     my @fields=("uid=?", "recieved=?");
     my @data=($uid,$amtReceived);
     if(defined $date){
          push @fields,'onDate=?';
          push @data,$date;
     }
     if(defined $payMethod){
          push @fields,'payMethod=?';
          push @data,$payMethod;
     }
     if(defined $tender){
          push @fields,'tender=?';
          push @data,$tender;
     }
     if(defined $change){
          push @fields,'changeAmnt=?';
          push @data,$change;
     }
     my $sql="insert into opl_paymentReceipt set " . join(",",@fields)  ;
     my $sth=$dbh->prepare($sql);
     $sth->execute(@data);

     my ($pid)=$dbh->{'mysql_insertid'};
     foreach my $p(@pays){
        if($availableFund <= $p->{'payAmount'}){
            $p->{'payAmount'}=$availableFund;
        }
        $availableFund -= $p->{'payAmount'};

         _payment_cid($dbh,$respUid,
                        $pid,
                        $p->{'cid'},
                        $p->{'payAmount'},
                        $crCidList,
                        $p->{'description'},
                        $p->{'date'},
                        );
         last if($availableFund<=0);
     }
     # refund credits left 
     my $refund=0.00; 
     foreach my  $cid(@$crCidList){
         my $cr =_getAvailCredit($dbh,$cid);
         if(defined$cr && $cr>0){
             trans_payRefund($dbh,$respUid,$pid,$cid,$cr,$description,$date);
             $refund +=$cr;
         }

     }
     my ($accountBalance)= $dbh->selectrow_array("select sum(balance) from opl_charge where uid=$uid");
     $dbh->do("update opl_paymentReceipt set withdrawn=$refund, accountBalance= $accountBalance  where pid=$pid");

     
     return $pid;
}
#-------------------------------------------------------------------------------
sub _payment_cid{
    my ($dbh,$respUid,$pid,$cid,$payAmount,$crCidList,$description,$date)=@_;

    my $processingAmnt=$payAmount;
    my ($uid,$balance) =$dbh->selectrow_array("select uid ,balance from opl_charge where cid= $cid");
    my ($crCid,$cr) =_getNextAvailCredit($dbh,$crCidList);
    while($cr && $cr>0 && $processingAmnt>0){
        my $amt=0;
        if($cr >= $processingAmnt){
           $amt=$processingAmnt;
        }
        else{
            $amt=$cr;
        }
        my $crUsedTid =_withdrawCredit($dbh,$respUid,$uid,$pid,$crCid,$amt,$date);
        my $tid =_payFromCredit($dbh,$respUid,$uid,$pid,$cid,$amt,$crUsedTid,$date);

        $processingAmnt -= $cr ;
        $balance -=$amt;
        if($processingAmnt>0){
            ($crCid,$cr) =_getNextAvailCredit($dbh,$crCidList);
        }

    }
    if($processingAmnt>0){
        my $newBalance   = $balance -  $processingAmnt; 
        my $sth=$dbh->prepare("update opl_charge set balance= ?,paid=paid-?  where cid= ?");
        $sth->execute($newBalance,$processingAmnt,$cid);
        my $params={};
        $params->{'code'}        = PAYMENT;
        $params->{'responsible'} = $respUid;
        $params->{'uid'}         = $uid;
        $params->{'cid'}         = $cid;
        $params->{'pid'}         = $pid;
        $params->{'amount'}      = $processingAmnt;
        $params->{'balance'}     = trans_getBalance($dbh,$uid) - $processingAmnt;
        $params->{'description'} = $description;
        $params->{'date'}       =$date if($date );
        _saveTrans($dbh,$params);

    }
}
#-------------------------------------------------------------------------------
sub _getAvailCredit{
    my($dbh,$cid)=@_;
    my ($credit)=$dbh->selectrow_array("select abs(balance) from opl_charge where balance <0 and  cid =$cid");
    return $credit;
}
##-------------------------------------------------------------------------------
sub _getNextAvailCredit{
    my($dbh,$cidList)=@_;
    return(undef,0.00) if(scalar(@$cidList)==0);
    my ($cid,$credit)=$dbh->selectrow_array("select cid, abs(balance) from opl_charge where balance <0 
                                             and  cid in (" . join(",",@$cidList) .") limit 1");
    return ($cid,$credit);
}
#-------------------------------------------------------------------------------
sub _credit_refund{
    my ($dbh,$respUid,$uid,$cid,$refundAmount,$description,$date)=@_;
    return 0 if($refundAmount<=0);
  
    my $sth=$dbh->prepare("update opl_charge set balance=balance -?,creditRefund =-1*abs(?)  where cid= ?");
    $sth->execute($refundAmount,$refundAmount,$cid);

    my $params={};
    $params->{'code'}        = CREDIT_REFUND;
    $params->{'responsible'} = $respUid;
    $params->{'uid'}         = $uid;
    $params->{'cid'}         = $cid;
    $params->{'amount'}      = $refundAmount;
    $params->{'balance'}     = trans_getBalance($dbh,$uid) - $refundAmount;
    $params->{'description'} = $description;
    $params->{'date'}       =$date if($date);
    my $tid = _saveTrans($dbh,$params);
    return $tid;

}
#-------------------------------------------------------------------------------
sub trans_credit_refund{
    my ($dbh,$respUid,$cid,$refundAmount,$description,$date)=@_;
    my ($uid,$chargeAmnt,$waive,$balance) =$dbh->selectrow_array("select uid ,chargeAmnt,waive,balance 
                                                                  from   opl_charge where cid= $cid");
    my $refundable =$chargeAmnt - $waive;
    my $tid=0;
    if(!defined $refundAmount){
        $refundAmount = $refundable ;
    }
    if($refundAmount>0 && $refundAmount <= $refundable){
         $tid =_credit_refund($dbh,$respUid,$uid,$cid,$refundAmount,$description,$date);
    }
    return $tid;
}
#-------------------------------------------------------------------------------
sub trans_credit_refund_full{
    my ($dbh,$respUid,$cid,$description,$date)=@_;
    my ($uid,$totalCharge) =$dbh->selectrow_array("select uid,totalCharge from opl_charge where cid= $cid");
    my $tid = _credit_refund($dbh,$respUid,$uid,$cid,$totalCharge,$description,$date);
    return $tid;
}
#-------------------------------------------------------------------------------

sub trans_doReverseLost{
    my($dbh,$barcode,$respUid)=@_;
    my $cid = _getLostCID($dbh,$barcode);
    if($cid>0){
        trans_credit_refund($dbh,$respUid,$cid,undef,"");
    }
}

#-------------------------------------------------------------------------------
sub _getLostCID{
    my($dbh,$barcode)=@_;
    my $cid=0;
    my $sth =$dbh->prepare(<<_SQL_);
    select o.*,c .*  
    from opl_loan l inner join opl_odl o using(idloan) 
         left outer join opl_charge c using(odl_id) 
    where barcode=? order by o.odl_id desc limit 1
_SQL_
    $sth->execute($barcode);
    if(my $rec=$sth->fetchrow_hashref){
        if($rec->{'type'} eq'lost' && defined $rec->{"cid"}){
            $cid=$rec->{"cid"};
        }
    }
    return $cid;
}

#-------------------------------------------------------------------------------
sub trans_payRefund{
    my ($dbh,$respUid,$pid,$cid,$payAmount,$description,$date)=@_;
    my ($uid,$balance) =$dbh->selectrow_array("select uid ,balance from opl_charge where cid= $cid");
    my $newBalance   = $balance +  $payAmount; 
    my $sth=$dbh->prepare("update opl_charge set balance= ? ,refund= ? where cid= ?");
    $sth->execute($newBalance,$payAmount,$cid);

    my $params={};
       $params->{'code'}        = PAY_REFUND;
       $params->{'responsible'} = $respUid;
       $params->{'uid'}         = $uid;
       $params->{'pid'}         = $pid;
       $params->{'cid'}         = $cid;
       $params->{'amount'}      = $payAmount;
       $params->{'balance'}     = trans_getBalance($dbh,$uid) + $payAmount;
       $params->{'description'} = $description;
       $params->{'date'}       =$date if($date ); 

    my  $tid =_saveTrans($dbh,$params);
   
}

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

sub _withdrawCredit{
    my($dbh,$respUid,$uid,$pid,$cid,$amt,$date)=@_;
    my $params={cid=>$cid,
                code=>CREDIT_USED,
                responsible=>$respUid,
                uid=>$uid,
                pid=>$pid,
                date=>$date,
                amount=> $amt,
                balance=>trans_getBalance($dbh,$uid) + abs($amt)
                };
    my $tid =_saveTrans($dbh,$params);
    my $sth=$dbh->prepare("update opl_charge set balance=balance + abs(?) ,creditUsed =creditUsed +abs(?) where cid= ?");
    $sth->execute($amt,$amt,$cid);

    
    return $tid;
}

#-------------------------------------------------------------------------------
sub _payFromCredit{
    my($dbh,$respUid,$uid,$pid,$cid,$amt,$fromTid,$date)=@_;
    my $params={cid=>$cid,
                code=>PAY_FROM_CREDIT,
                responsible=>$respUid,
                uid=>$uid,
                pid=>$pid,
                date=>$date,
                amount=> $amt,
                creditUsed_tid=>$fromTid,
                balance=>trans_getBalance($dbh,$uid) - abs($amt)
                };
    my $tid =_saveTrans($dbh,$params);
    my $sth=$dbh->prepare("update opl_charge set balance=balance - abs(?), paid=paid-abs(?)  where cid= ?");
    $sth->execute($amt,$amt,$cid);
    return $tid;
}
#-------------------------------------------------------------------------------
sub trans_getCharge{
    my($dbh,$cid)=@_;
    return undef if(!defined $cid);
    my $charge =$dbh->selectrow_hashref("select * from opl_charge where cid=$cid");
    return $charge;
}
#-------------------------------------------------------------------------------
sub trans_getUnpaidChargeList{
    my($dbh,$uid)=@_;
    return trans_getChargeList($dbh,$uid,1);
}
#-------------------------------------------------------------------------------
sub _getChargeAuditNote{
    my($dbh,$cid)=@_;
    my $sth=$dbh->prepare("select note,date from opl_auditTrail where cid=?");
    $sth->execute($cid);
    my $ret=[];
    while(my $r=$sth->fetchrow_hashref){
        push(@$ret,$r);
    }
    return $ret;
}
#-------------------------------------------------------------------------------
sub trans_getChargeList{
    my($dbh,$uid,$hasBlance)=@_;
    my $sql=<<_SQL_;
    select distinct c.*, if(o.hours_overdue>0,'hourly','daily') rateType ,if(t.code is null,1,0) editable
    from opl_charge c inner join opl_odl o using(odl_id)
         left outer join opl_transaction t on t.cid=c.cid and (t.code=4 || t.code=6 || t.code=8) && t.responsible<>0
    where  c.uid=?
_SQL_
                            

    if($hasBlance){
        $sql .=" && c.balance <>0";
    }
    my $sth =$dbh->prepare($sql);
    $sth->execute($uid);
    my  @chList;
    while(my $chList = $sth->fetchrow_hashref){
        $chList->{'paid'}=sprintf "%.2f", abs($chList->{'paid'});
        my $itemInfo =getItemInfo($dbh,$chList->{'cid'});
        $chList->{'title'}=$itemInfo->{'title'};
        $chList->{'barcode'}=$itemInfo->{'barcode'};
        $chList->{'chargeType'}=getChargeCodeStr($dbh,$chList->{'cid'});
        $chList->{"auditTrail"} =_getChargeAuditNote($dbh,$chList->{'cid'});
        ($chList->{"maxOverdueCharge"})=$dbh->selectrow_array("select maxOverdue from opl_item i inner join opl_fineRate f on i.typeId=f.itemType where i.barcode=?",undef,$itemInfo->{'barcode'});
        push @chList,$chList;
    }
    return \@chList;
}
#-------------------------------------------------------------------------------
sub getItemInfo{
    my ($dbh,$cid)=@_;
    my $sth = $dbh->prepare(<<__SQL__);
    select l.barcode,m.title,i.callNumber from opl_charge c inner join opl_odl o using(odl_id)
        inner join opl_loan l 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 c.cid=?    
__SQL__

    $sth->execute($cid)  ;
    my $rec=$sth->fetchrow_hashref;    
    $sth->finish;
    return $rec;

}


#-------------------------------------------------------------------------------
sub getChargeCodeStr{
    my ($dbh,$cid)=@_;
    my ($codeStr)=$dbh->selectrow_array("select code_str from opl_transaction t inner join opl_transCode using(code) where cid= $cid order by tid limit 1");
    return $codeStr;
}
#-------------------------------------------------------------------------------
sub trans_getReceiptList{
    my($dbh,$uid)=@_;
    my $sth=$dbh->prepare(<<__SQL__);
    select distinct if(p.recieved>0,'payment','refund') transType,
                    if(p.recieved>0,recieved,withdrawn) transAmount, 
                    p.pid,p.uid,p.ondate,p.accountBalance,
                    trim(concat(u.firstname,' ',u.lastname)) as responsible 
    from opl_paymentReceipt p left outer join opl_transaction t on p.pid=t.pid
         inner join opl_user u on u.uid=t.responsible 
    where p.uid=?
__SQL__
    $sth->execute($uid)  ;
    my @receiptList;
    while(my $rList = $sth->fetchrow_hashref){
        push @receiptList,$rList;
    }
    return \@receiptList;
}
#-------------------------------------------------------------------------------
sub trans_getReceiptDetail{
    my($dbh,$pid)=@_;
    my $sth=$dbh->prepare(<<__SQL__);
    select  cid, sum(if(t.code=8 || t.code=10,amount,0)) refund ,sum(if(t.code=4||t.code=11,amount,0) ) paid, date,
            trim(concat(u.firstname,' ',u.lastname)) as responsible 
    from    opl_transaction t left outer join opl_user u on u.uid=t.responsible
    where   t.pid=?
    group by cid

__SQL__
  
    my $receipt= $dbh->selectrow_hashref("select * from opl_paymentReceipt where pid=$pid");

    if($receipt){
        $receipt->{'itemList'}=[];
        $sth->execute($pid);
        while( my ($cid,$refund,$paid,$onDate,$responsible) =$sth->fetchrow_array){
            $receipt->{'responsible'}=$responsible;
            my $item=getItemInfo($dbh,$cid);
            $item->{'barcode'} =~ s/^___|_\d\d\d$//g;
            $item->{'onDate'} =$onDate;
            $item->{'paid'}=$paid if($paid>0);
            $item->{'refund'}=$refund if($refund>0);
            $item->{'chargeCode'}=getChargeCodeStr($dbh,$cid);
            $item->{'chargeCode'} .="_REFUND" if($item->{'refund'});
            push @{$receipt->{'itemList'}},$item;
        }
    }
    return $receipt;
}
#-------------------------------------------------------------------------------
sub trans_getBalance{
    my($dbh,$uid)=@_;
    my ($balance)=$dbh->selectrow_array("select sum(balance) balance from opl_charge where uid=$uid");
    
    $balance=0 if(!$balance);
    return $balance;

}
#-------------------------------------------------------------------------------
sub trans_adjustCharge{
    my($dbh,$respUid,$newCharge,$note)=@_;
    my $rs;
    my $oldCharge=trans_getCharge($dbh,$newCharge->{'cid'});
    return 1 if(!defined $oldCharge);

    my $sth_charge =$dbh->prepare(<<__SQL__);
    update opl_charge 
    set waive=?,fee=?,rate=?,unit=?,
        chargeAmnt=?,
        totalCharge=?,
        balance=?
    where cid=?
__SQL__

   my $sth_trans =$dbh->prepare("
    update opl_transaction 
        set amount=?
        where cid=? && (code=" . CHARGE_SERVICE 
                  ." || code=" . CHARGE_OVERDUE  
                  ." || code=" . CHARGE_LOST
                  ." || code=" . CHARGE_DAMAGE 
                  .")"
    );

    my $sth_auditTrail =$dbh->prepare(<<__SQL__);
    insert into opl_auditTrail 
    set gid=?, cid=?, responsible=?, type=?, oldVal=?, newVal=? ,note=?
__SQL__


    my $gid=_genGUID();
    my $hasChange=0;
    #my $chargeAmnt  = $newCharge->{'rate'} * $newCharge->{'unit'};
    my $chargeAmnt  = $newCharge->{'chargeAmnt'};
    my $totalCharge = $chargeAmnt + abs($newCharge->{'fee'}) - abs($newCharge->{'waive'});;
    my $balance=$totalCharge - abs($newCharge->{'paid'}) 
                             + abs($newCharge->{'refund'})
                             + abs($newCharge->{'creditUsed'})
                             - abs($newCharge->{'creditRefund'}) ;


    $newCharge->{'chargeAmnt'}  =$chargeAmnt;
    $newCharge->{'totalCharge'} =$totalCharge;
    $newCharge->{'balance'}     =$balance;


    foreach my $type(qw(waive fee rate unit )){
        if($newCharge->{$type} ne $oldCharge->{$type}){
            $hasChange=1;
            $rs = $sth_auditTrail->execute($gid,
                            $oldCharge->{'cid'},
                            $respUid,
                            $type,
                            $oldCharge->{$type},
                            $newCharge->{$type},
                            $note);
        }
    }
   if($hasChange && $rs ne '0E0'){

        $rs = $sth_charge->execute($newCharge->{'waive'},
                            $newCharge->{'fee'},
                            $newCharge->{'rate'},
                            $newCharge->{'unit'},
                            $newCharge->{'chargeAmnt'},
                            $newCharge->{'totalCharge'},
                            $newCharge->{'balance'},
                            $newCharge->{'cid'});

        $rs = $sth_trans->execute($totalCharge,$newCharge->{'cid'});

   }
   $newCharge->{'auditTrail'}=_getChargeAuditNote($dbh,$newCharge->{'cid'});
   return $rs eq '0E0'?undef:$newCharge;
    

}
#-------------------------------------------------------------------------------
sub _genGUID{
    my $guid=`uuidgen`;
    chomp $guid;

    $guid =~ s/-//g;
    $guid = uc($guid);
    return $guid;
}

#-------------------------------------------------------------------------------
sub trans_getInvoice{
    my ($dbh,$cid)=@_;
    return undef if(!defined $cid);
    my $sql="select  m.title,m.author ,l.barcode ,o.type,c.* 
            from     opl_loan l inner join opl_odl o on o.idloan=l.idloan 
                     inner join opl_charge c       on o.odl_id= c.odl_id 
                     inner join opl_item i        on l.barcode=i.barcode 
                     inner join  opl_marcRecord m on i.rid=m.rid where c.cid=$cid";
    my $invoice =$dbh->selectrow_hashref($sql);
    return $invoice;
}
#-------------------------------------------------------------------------------
1;