#!/usr/bin/perl 
#for i in `ls /etc/opals/conf/`; do cp -p /www/opals-20150521155601/script/update/update_20150521_preConvertTrans /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /tmp/urt; done

use lib "/www/opals-20150521155601/module";;
use Opals::Context('/etc/opals/conf/_MY_SITE_');
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 JSON;
use CGI;
use DBI;

use JSON;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

$dbh->do(<<_SQL_);
CREATE TABLE IF NOT EXISTS `opl_transaction` (
  `tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(10) unsigned NOT NULL,
  `pid` int(10) unsigned DEFAULT NULL,
  `code` smallint(6) DEFAULT NULL,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `responsible` int(10) unsigned NOT NULL,
  `creditUsed_tid` int(10) unsigned DEFAULT NULL,
  `amount` float(10,2) NOT NULL DEFAULT '0.00',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`tid`),
  UNIQUE KEY `tid` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

_SQL_

$dbh->do(<<_SQL_);
CREATE TABLE IF NOT EXISTS  `opl_charge` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `odl_id` int(10) unsigned DEFAULT NULL,
  `svc_id` int(10) unsigned DEFAULT NULL,
  `responsible` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `materials` set('book','video','DVD','reserve material') DEFAULT 'book',
  `rate` float(10,2) DEFAULT '0.00',
  `unit` int(10) unsigned DEFAULT '1',
  `waive` float(10,2) DEFAULT '0.00',
  `creditRefund` float(10,2) DEFAULT '0.00',
  `creditUsed` float(10,2) DEFAULT '0.00',
  `refund` float(10,2) DEFAULT '0.00',
  `chargeAmnt` float(10,2) DEFAULT '0.00',
  `fee` float(10,2) DEFAULT '0.00',
  `totalCharge` float(10,2) DEFAULT '0.00',
  `paid` float(10,2) DEFAULT '0.00',
  `balance` float(10,2) DEFAULT '0.00',
  `taxes` float(10,2) DEFAULT '0.00',
  `note` varchar(254) DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `NewIndex` (`cid`,`odl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

_SQL_

$dbh->do(<<_SQL_);
CREATE TABLE IF NOT EXISTS  `opl_paymentReceipt` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `recieved` float(10,2) DEFAULT '0.00',
  `withdrawn` float(10,2) DEFAULT '0.00',
  `accountBalance` float(10,2) DEFAULT '0.00',
  `payMethod` varchar(50) DEFAULT 'cash',
  `tender` float(10,2) DEFAULT '0.00',
  `changeAmnt` float(10,2) DEFAULT '0.00',
  `ondate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


_SQL_

$dbh->do(<<_SQL_);
alter table opl_transactions add converted tinyint(2) not null default '0';
_SQL_


$dbh->do("truncate table opl_charge");
$dbh->do("truncate table opl_transaction");
$dbh->do("truncate table opl_paymentReceipt");
$dbh->do("update opl_transactions set converted=0");

$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,$chargeAmount)=getCharge($dbh,$tid);

        if($code==1){
            $cid=trans_chargeOverdue($dbh,$respUid,$uid,$odl_id,$rate,$unit,$waive,$note,$chargeAmount,$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,"cash",$amount,0);
           
        }
        elsif($code==5){
             my $chargeList=getChargeList($dbh,$uid);
             my $fAmnt=$amount;
             foreach my $c(@$chargeList){
                 next if($c->{'balance'} <=0);
                 my $toWaive=0;
                 if($c->{'balance'}>$fAmnt){
                    $toWaive=$fAmnt;
                 }
                 else{
                    $toWaive=$c->{'balance'};;
                 }
                $dbh->do("update opl_charge set waive=waive+?,balance=balance-? where cid=?",undef,$toWaive,$toWaive,$c->{'cid'});
                 $fAmnt -= $toWaive;
                 last if($fAmnt<=0);

             }

        }
        elsif($code==6){
            my $cid =_getRefundLostCid($dbh,$tid);
            if($cid>0){
                trans_credit_refund($dbh,$respUid,$cid,undef,"",$date);
            }
            
        }
        $dbh->do("update opl_transactions set converted=1 where tid=?",undef,$tid);
      
    }

#============================================================================
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,$fineAmount) =(1,0,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'};
        $fineAmount=$rec->{'fineAmount'};
        $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,$fineAmount);
}

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

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");
    }
    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;
}