#!/usr/bin/perl 

#for i in `ls /etc/opals/conf/`; do cp -p /www/opals-20150521155601/script/update/update_20150521 /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(); }
update_CategoryPermission($dbh);
convert_pref_importMap($dbh);
update_userPermission($dbh);

=item$dbh->do("truncate table opl_charge");
$dbh->do("truncate table opl_transaction");
$dbh->do("truncate table opl_paymentReceipt");
=cut

$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  where converted =0  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,$chargeAmount,$date);
        }
        elsif($code==3){
            $cid=trans_chargeDamage($dbh,$respUid,$uid,$odl_id,$rate,$waive,$note,$chargeAmount,$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==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);
            }
            
        }
      
    }

#============================================================================
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;
}

#-------------------------------------------------------------------------------
sub  convert_pref_importMap{
    my($dbh,$name)=@_;
    my ($mapStr)=$dbh->selectrow_array("select val from opl_preference where var='iumap'");
    my @arr= split(",",$mapStr);
    my $mapObj={};
    my $len= scalar(@arr);
    if($len >1 && $len%2==0){
        for(my $i=0;$i<$len;$i+=2){
             $mapObj->{@arr[$i]}=@arr[$i+1];
        }
        my $mapJson=to_json($mapObj);
        $dbh->do("update opl_preference set val=? where var='iumap'",undef,$mapJson);
    }
}
#-------------------------------------------------------------------------------
sub  convert_pref_odMsg{
    my($dbh,$name)=@_;
    my ($mapStr)=$dbh->selectrow_array("select val from opl_preference where var=''");
    my @arr= split(",",$mapStr);
    my $mapObj={};
    my $len= scalar(@arr);
    if($len >1 && $len%2==0){
        for(my $i=0;$i<$len;$i+=2){
             $mapObj->{@arr[$i]}=@arr[$i+1];
        }
        my $mapJson=to_json($mapObj);
        $dbh->do("update opl_preference set val=? where var='iumap'",undef,$mapJson);
    }
}

#-------------------------------------------------------------------------------
sub update_userPermission{
    my ($dbh)=@_;
    my $map={user_add =>[qw(user_add user_permEdit)],
             marc_edit=>[qw(marc_export marc_merge marc_del marc_edit marc_add)],
             fine=>[qw(fine fine_edit fine_waive)],
             report => [qw(rpt_fine rpt_catRec rpt_circ rpt_notice rpt_user)],
             circ_rsrv=>[qw(circ_rsrv rsrv_shelf genre)]
             };
    my $sth=$dbh->prepare("select uid,permissions from opl_user where permissions is not null && permissions<>'' && permissions <>'circ_rsrv_self'");
    my $sth_update=$dbh->prepare("update opl_user set permissions=? where uid=?");
    
    $sth->execute();
    while( my ($uid,$perm)=$sth->fetchrow_array){
        my $newPermStr=getNewPermStr($perm);
        $sth_update->execute($newPermStr,$uid);
    }
}
#-------------------------------------------------------------------------------
sub update_CategoryPermission{
    my ($dbh)=@_;
    my $map={user_add =>[qw(user_add user_permEdit)],
             marc_edit=>[qw(marc_export marc_merge marc_del marc_edit marc_add)],
             fine=>[qw(fine fine_edit fine_waive)],
             report => [qw(rpt_fine rpt_catRec rpt_circ rpt_notice rpt_user)],
             circ_rsrv=>[qw(circ_rsrv rsrv_shelf genre)]
             };
    my $sth=$dbh->prepare("select catid,defaultPerm from opl_category where defaultPerm is not null && defaultPerm <>'' &&defaultPerm <>'circ_rsrv_self'");
    my $sth_update=$dbh->prepare("update opl_category set defaultPerm =? where catid=?");
    
    $sth->execute();
    while( my ($catid,$perm)=$sth->fetchrow_array){
        my $newPermStr=getNewPermStr($perm);
        $sth_update->execute($newPermStr,$catid);
    }
    
}
#-------------------------------------------------------------------------------

sub getNewPermStr{
    my ($perm)=@_;


      my $map={user_add =>[qw(user_add user_permEdit)],
             marc_edit=>[qw(marc_export marc_merge marc_del marc_edit marc_add)],
             fine=>[qw(fine fine_edit fine_waive)],
             report => [qw(rpt_fine rpt_catRec rpt_circ rpt_notice rpt_user)],
             circ_rsrv=>[qw(circ_rsrv rsrv_shelf genre)]
             };
        my @perArr= split(",",$perm);
        my @permArr_new=();

        foreach my $p(@perArr){
            next if($p eq '');
            if(defined $map->{$p}){
                foreach my $n(@{$map->{$p}}){
                    push @permArr_new,$n;
                }
            }
            else{
                push @permArr_new,$p;
            }
        }

    return join(",",@permArr_new);
}