#!/usr/bin/perl 

use lib "/www/opals-20150521155601/module";;
use Opals::Context('/etc/opals/conf/jac_jac');
use strict;
use Opals::Constant; 
use Date::Calc::Object qw(
    :all
);
use Digest::SHA qw(
    sha1_hex
    sha512_hex
);

use POSIX qw(
    ceil
    floor
);
use Time::localtime;

use Opals::Transaction qw(
   trans_chargeOverdue
    trans_chargeLost
    trans_chargeDamage
    trans_chargeService
    trans_payment
    trans_credit_refund
    trans_payRefund
    

    
);


use CGI;
use DBI;

use JSON;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }
$dbh->do("truncate table opl_charge");
$dbh->do("truncate table opl_transaction");
$dbh->do("truncate table opl_paymentReceipt");
$dbh->do("update opl_transactiondetail set forgivenAmount=fineAmount where amount=0.00 && fineAmount<>0");
my $sth_trans_insert=$dbh->prepare(<<__SQL__);
    insert into opl_transaction(cid,code,uid,responsible,creditUsed_tid,amount,balance,date,ondate,description) 
    values(?,?,?,?,?,?,?,?,?,?);
__SQL__


my $sth_charge_insert=$dbh->prepare(<<__SQL__);
    insert into opl_charge(odl_id,svc_id,responsible,uid,materials,rate,unit,waive,creditRefund,creditUsed,chargeAmnt,fee,totalCharge,paid,balance,taxes,note,date,onDate) 
    values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);

__SQL__

my $sth_trans=$dbh->prepare("select tid,uid,amount,balance,date,description,code,settleDate from opl_transactions  order by tid");
#my $sth_trans=$dbh->prepare("select tid,uid,amount,balance,date,description,code,settleDate from opl_transactions where uid=1203 && tid<5084 order by tid");
 $sth_trans->execute();

    my ($tid,$cid,$code,$uid,$responsible,$creditUsed_tid,$amount,$balance,$date,$ondate,$description);
    $responsible=1;
    while(my($tid,$uid,$amount,$balance,$date,$description,$code,$settleDate) =$sth_trans->fetchrow_array){
        my ($respUid,$odl_id,$rate,$unit,$waive,$note)=getCharge($dbh,$tid);

        if($code==1){
            $cid=trans_chargeOverdue($dbh,$respUid,$uid,$odl_id,$rate,$unit,$waive,$note,$date);
        }
        elsif($code==2){
            my $svcFee=0.00;
            $cid=trans_chargeLost($dbh,$respUid,$uid,$odl_id,$rate,$waive,$svcFee,$note,$date);
        }
        elsif($code==3){
            $cid=trans_chargeDamage($dbh,$respUid,$uid,$odl_id,$rate,$waive,$note,$date);
        }
        elsif($code==4 || $code==8){

            my $chargeList=getChargeList($dbh,$uid);
            my $name=$description;
            $name =~ s/payment by //g;
            $respUid =_lookupUid($dbh,$name);
            $amount=0.00 if($code==8);
            trans_payment($dbh,$respUid,$uid,$amount,$chargeList,"",$date);
           
        }
        elsif($code==6){
            my $cid =_getRefundLostCid($dbh,$tid);
            if($cid>0){
                trans_credit_refund($dbh,$respUid,$cid,undef,"",$date);
            }
            
        }
      
    }

#============================================================================
sub getCharge{
    my ($dbh,$tid)=@_;
    my $sth=$dbh->prepare("select t.*,o.type  from opl_transactiondetail t inner join opl_odl o using(odl_id) where tid=?");
    my( $respUid,$odl_id,$rate,$unit,$waive,$note) =(1,0,0,0,0,0,"");
    $sth->execute($tid);
    if( my $rec = $sth->fetchrow_hashref){
        $odl_id=$rec->{'odl_id'};
        $waive=$rec->{'forgivenAmount'};
        $note=$rec->{'note'};
        $respUid =_lookupUid($dbh,$rec->{'responsible'});
        if($rec->{'type'} eq 'overdue' ){
            $rate=$rec->{'rate'}>0?$rec->{'rate'}:$rec->{'hourlyRate'};
            $unit=$rec->{'rate'}>0?$rec->{'overdueDays'}:$rec->{'overdueHours'};
        }
        else{
           $rate=$rec->{'rate'};
           $unit=1;
        }
    }
    return ($respUid,$odl_id,$rate,$unit,$waive,$note);
}

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

sub getChargeList{
    my($dbh,$uid)=@_;
    my $chargeList=[];
    my $sth=$dbh->prepare("select * from opl_charge where uid= ? && balance <>0 order by cid");
    $sth->execute($uid);
    while(my $c=$sth->fetchrow_hashref){
        $c->{'payAmount'}=$c->{'balance'};
        push @$chargeList,$c;
    }
    return $chargeList;
}

#-------------------------------------------------------------------------------
sub _getAvailCredit{
    my($dbh,$uid)=@_;
    my ($credit)=$dbh->selectrow_array("select sum(abs(balance)) from opl_charge where balance <0 and  uid=$uid ");
    return $credit;
}
sub _getNextRefund{
    my($dbh,$uid)=@_;
    my ($cid,$credit)=$dbh->selectrow_array("select cid,abs(balance) from opl_charge where balance <0 and  uid=$uid order by cid limit 1");
    return ($cid,abs($credit));
}

#-------------------------------------------------------------------------------
sub _getRefundLostCid{
    my ($dbh,$tid)=@_;
    my $cid=0;
    my ($odl_id)=$dbh->selectrow_array("select odl_id from opl_transactiondetail where tid= $tid");
    if($odl_id){
        ($cid)=$dbh->selectrow_array("select cid from opl_charge where odl_id= $odl_id");
    }
    print "refund lost cid:$cid\n" ;
    return $cid;
}
#-------------------------------------------------------------------------------
sub _lookupUid{
    my($dbh,$name)=@_;
    my $uid=1;
    my ($firstname,$lastname)=("","");
    my @a =split " ",$name;
    if(scalar(@a)==1){
        $lastname=$name;
    }
    elsif(scalar(@a)==2){
        $firstname=@a[0];
        $lastname=@a[1];
    }

    my $sth =$dbh->prepare("select uid from opl_user where firstname=? && lastname=?");
    $sth->execute($firstname,$lastname);
    if(my $rec = $sth->fetchrow_hashref){
        $uid=$rec->{'uid'}
    }
    return $uid;
}

#$odl_id,$responsible,$materials,$rate,$hourlyRate,$overdueDays,$overdueHours,$fineAmount,$forgivenAmount,$amount,$taxes,$note
#$odl_id,$svc_id,$responsible,$uid,$materials,$rate,$unit,$waive,$refund,$creditUsed,$chargeAmnt,$fee,$totalCharge,$paid,$balance,$taxes,$note,$date,$onDate