<?php

namespace App\Helpers;

use App\Model\Hr\AbsentPolicyModel;
use App\Model\Hr\AdvanceSalaryModel;
use App\Model\Hr\BlackOutModel;
use App\Model\Hr\EmpLeave;
use App\Model\Hr\HrAttendanceModel;
use App\Model\Hr\HrEmployeeBenefitModel;
use App\Model\Hr\HrExchangeRateModel;
use App\Model\Hr\OvertimePolicyModel;
use App\Model\Hr\TimeAttendanceGenModel;
use App\User;
use Illuminate\Support\Facades\DB;

class GenSalaryFp
{
    //get generate salary
    public static function getGenerateSalary($emp_id = null, $from_date = null, $to_date = null, $basic_salary = null)
    {

        $basic_salary       = $basic_salary != null ? ($basic_salary >0 ? $basic_salary : 0) : 0;
        //exchange rate
        $exchange_rate      = self::getNormalExRate();
        $khmer_rate         = isset($exchange_rate['khmer_rate'])?$exchange_rate['khmer_rate']: 4000;
        $thai_rate          = isset($exchange_rate['thai_rate'])?$exchange_rate['thai_rate']: 36;

        $absent_policy      = self::getAbsentPolicy();

        //ot rate policy
        $ot_rate_policy     = self::getOtRatePolicy();
        $rate_normal_ot     = isset($ot_rate_policy['rate_normal_ot'])?$ot_rate_policy['rate_normal_ot'] : 0;
        $rate_sunday_ot     = isset($ot_rate_policy['rate_sunday_ot'])?$ot_rate_policy['rate_sunday_ot'] : 0;
        $rate_holiday_ot    = isset($ot_rate_policy['rate_holiday_ot'])?$ot_rate_policy['rate_holiday_ot'] : 0;

        //benefit
        $benefit            = self::getBenefit($emp_id);
        $emp_benefit        = isset($benefit['emp_benefit']) ? $benefit['emp_benefit'] : 0;

        //advance salary
        $advance_sal        = self::getAdvanceSalary($emp_id, $from_date, $to_date);

        //take leave
        $take_leave         = self::getTakeLeaveAutorize($emp_id, $from_date, $to_date);

        //attend from finger print
        $attend_finger      = self::getAttendFingerPrint($emp_id, $from_date, $to_date);
        $missing            = isset($attend_finger['missing'])? $attend_finger['missing'] : 0;
        $total_holiday      = isset($attend_finger['total_holiday'])? $attend_finger['total_holiday'] : 0;
        $total_sunday       = isset($attend_finger['total_sunday']) ? $attend_finger['total_sunday'] : 0;
        $total_ot           = isset($attend_finger['total_ot'])? $attend_finger['total_ot'] : 0;

        $work_sun           = self::getWorkingSunday($emp_id, $from_date, $to_date);
        //total overtime
        $total_ots          = $total_ot > 0 ?  (int)($total_ot / 60) : 0;
        // $total_ots          = $total_ot > 0 ? (($basic_salary / 26) / 8) * ($total_ot / 60) * $rate_normal_ot : 0;
        $total_sun          = $total_holiday > 0 ? (($basic_salary / 26) / 8) * ($total_sunday / 60) * $rate_sunday_ot : 0;
        $total_hol          = $total_holiday > 0 ? (($basic_salary / 26) / 8) * ($total_holiday / 60) * $rate_holiday_ot : 0;

        //blackout
        // $blackout           = self::getBlackout($from_date, $to_date);

        //$net_pay            = $gross_pay - $income_tax;
        $net_pay            = ($basic_salary + $total_ots + $work_sun + $emp_benefit) - ($advance_sal + $take_leave);
        $net_pay            = $net_pay > 0 ? $net_pay : 0;
        $net_payment_kh     = $net_pay != null ? ($net_pay * $khmer_rate > 0 ? $net_pay * $khmer_rate : 0) : 0;
        $net_payment_th     = $net_pay != null ? ($net_pay * $thai_rate > 0 ? $net_pay * $thai_rate : 0) : 0;

        return [

            'khmer_rate'        => $khmer_rate,
            'thai_rate'         => $thai_rate,
            'absent_policy'     => $absent_policy,
            'benefit'           => $emp_benefit,
            'missing'           => $missing,
            'total_holiday'     => $total_hol,
            'total_sunday'      => $total_sun,
            'total_ot'          => $total_ots,
            'take_leave'        => $take_leave,
            'basic_salary'      => $basic_salary,
            'advance_salary'    => $advance_sal,
            'net_payment'       => $net_pay,
            'net_payment_kh'    => $net_payment_kh,
            'net_payment_th'    => $net_payment_th,
            'work_sun'          => $work_sun,

        ];
    }
    //get employee benefit
    public static function getBenefit($emp_id)
    {
        $emp_benefit = 0;
        $benefit = HrEmployeeBenefitModel::where('emp_id', $emp_id)->get();
        foreach($benefit as $ben)
        {
            $emp_benefit += $ben->amount;
            $emp_benefit = isset($emp_benefit) ? $emp_benefit: 0;
        }
        return [
            'emp_benefit' => $emp_benefit
        ];
    }

    //get Gross Pay
    public static function getGrossPay($row, $total_ot, $emp_benefit, $basic_salary)
    {

        $position_allowance         = $row->position_allowance?$row->position_allowance: 0;
        $meal_transport_allowance   = $row->meal_transport_allowance?$row->meal_transport_allowance:0;
        $other_allowance            = $row->other_allowance?$row->other_allowance:0;
        $increment                  = $row->increment?$row->increment:0;


        $gross_pay = $total_ot + $emp_benefit + $basic_salary
                    + $position_allowance + $meal_transport_allowance + $other_allowance
                    + $increment;

        $gross_pay = $gross_pay > 0 ? $gross_pay : 0;

        return ['gross_pay' => $gross_pay];
    }

    //income tax
    public static function getIncomeTax($row, $gross_pay)
    {

        $fringe_tax             = 0;
        $exchange_khmer         = self::getNormalExRate();
        $spouse_child           = self::getSpouseChild($row->id);
        $taxable_salary_khmer   = round(($gross_pay - $spouse_child) * $exchange_khmer,2);

        $taxable_salary         = $gross_pay - $spouse_child;
        $taxable_salary         = $taxable_salary>0?$taxable_salary:0;

        $income_tax_rate        = self::getIncomeTaxRate($taxable_salary);
        $income_tax_rate        = $income_tax_rate>0?$income_tax_rate:0;

        $income_tax_deduction   = self::getIncomeTaxDeduction($taxable_salary);
        $income_tax_deduction   = $income_tax_deduction>0?$income_tax_deduction:0;


        $salary_tax             = self::calculateTax($taxable_salary);
        $salary_tax             = $salary_tax>0?$salary_tax:0;

        $income_tax             = $salary_tax + $fringe_tax ;
        $income_tax             = $income_tax >0?$income_tax:0;

        return [
            'taxable_salary'        => $taxable_salary,
            'income_tax'            => $income_tax,
            'income_tax_rate'       => $income_tax_rate,
            'exchange_khmer'        => $exchange_khmer,
            'income_tax_deduction'  => $income_tax_deduction,
            'taxable_salary_khmer'  => $taxable_salary_khmer,
            'salary_tax'            => $salary_tax,
            'fringe_tax'            => $fringe_tax,
            'gross_pay'             => $gross_pay
        ];

    }

    // Deduction Tax
    public static function getIncomeTaxDeduction($salary = null, $exchange_rate=4000)
    {
        $exchange_rate = self::getNormalExRate();

        $sal_KH = $salary * $exchange_rate ;
        $re = 0;
        if($sal_KH <=1200000){
            $re = 0;
        }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
            $re = 60000 ;
        }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
            $re = 160000;
        }elseif ($sal_KH >8500000 && $sal_KH <= 12500000) {
            $re = 585000;
        }else{
            $re = 1210000;
        }
        return $re;
    }

    //Salary income tax return ៛​
    public static function calculateTax($salary = null, $exchange_rate=4000)
    {

        $exchange_rate = self::getNormalExRate();
        $sal_KH = $salary * $exchange_rate ;
        $re = 0;
        if($sal_KH <=1200000){
            $re = 0;
        }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
            $re = $sal_KH * 0.05 - 60000 ;
        }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
            $re = $sal_KH* 0.1 - 160000;
        }elseif ($sal_KH >8500000 && $sal_KH <= 12500000){
            $re = $sal_KH* 0.15 - 585000;
        }else{
            $re = $sal_KH* 0.2 - 1210000;
        }
        return number_format($re / $exchange_rate,2);
    }

    //Salary income tax reture %
    public static function getIncomeTaxRate($salary = null, $exchange_rate=4000)
    {
        $exchange_rate = self::getNormalExRate();

        $sal_KH = $salary * $exchange_rate ;
        $re = 0;
        if($sal_KH <=1200000){
            $re = 0;
        }elseif ($sal_KH >1200000 && $sal_KH <= 2000000){
            $re = 5 ;
        }elseif ($sal_KH >2000000 && $sal_KH <= 8500000){
            $re = 10;
        }elseif ($sal_KH >8500000 && $sal_KH <= 12500000) {
            $re = 15;
        }else{
            $re = 20;
        }
        return $re;
    }

    //Tax on Spouse Child
    public static function getSpouseChild($emp_id = null)
    {

        $s              = 0;
        $c              = 0;
        $total_amt      = 0;
        $total          = 0;
        $emp            = User::where('id', $emp_id)->first();
        $exchange_rate  = self::getNormalExRate();

        if ($emp != null)
        {
            $s = isset($emp->spouse) > 0 ? $emp->spouse : 0;
            $c = isset($emp->child) > 0 ? $emp->child : 0;
        }
        $total      = $c + $s;
        $total      = $total > 0 ? $total : 0;
        $total_amt  = ($total * 150000) / $exchange_rate;

        return $total_amt > 0 ? $total_amt : 0;

    }

    //Exchange Rate
    public static function getNormalExRate()
    {
        $m = HrExchangeRateModel::orderBy('date','DESC')
            ->where('general_rate','>',0)
            ->limit(1)->first();
        return [
            'khmer_rate' => $m != null ? ($m->khmer_rate>0 ? $m->khmer_rate : 4000) : 4000,
            'thai_rate'  => $m != null ? ($m->thai_rate>0 ? $m->thai_rate : 31) : 31,
        ];
    }

    //absent deductioin
    public static function getAbsentPolicy()
    {
        $absent = AbsentPolicyModel::orderBy('date', 'DESC')->limit(1)->first();
        return $absent != null ? ($absent->deduct_money ? $absent->deduct_money : 0) : 0;
    }

    //OT Policy
    public static function getOtRatePolicy()
    {
        $overtime           = OvertimePolicyModel::orderBy('effect_date', 'DESC')->limit(1)->first();
        $rate_normal_ot     = isset($overtime->normal_ot_rate)?$overtime->normal_ot_rate:0;
        $rate_sunday_ot     = isset($overtime->sunsat_ot_rate)?$overtime->sunsat_ot_rate:0;
        $rate_holiday_ot    = isset($overtime->holiday_ot_rate)?$overtime->holiday_ot_rate:0;

        return [
            'rate_normal_ot'    => $rate_normal_ot,
            'rate_sunday_ot'    => $rate_sunday_ot,
            'rate_holiday_ot'   => $rate_holiday_ot
        ];
    }

    //get advance salary
    public static function getAdvanceSalary($emp_id = null, $from_date = null, $to_date = null)
    {
        $adv_sal = AdvanceSalaryModel::select(DB::raw('SUM(advance_salary) as advance_salaries'))
                                            ->where('emp_id', $emp_id)
                                            ->whereBetween('effect_date', [$from_date, $to_date])
                                            ->limit(1)
                                            ->first();

        return $adv_sal != null ? ($adv_sal->advance_salaries ? $adv_sal->advance_salaries : 0) : 0;
    }

    //get take leave autorize
    public static function getTakeLeaveAutorize($emp_id = null, $from_date = null, $to_date = null)
    {
        $emp_leave = EmpLeave::select(DB::raw('SUM(num_take_leave * deduct) as num_leave'))
                            ->where('emp_id', $emp_id)
                            ->whereDate('from_date', '>=', $from_date)
                            ->whereDate('to_date', '<=', $to_date)
                            ->where('review', 1)
                            ->where('approve', 1)
                            ->limit(1)
                            ->first();
        return $emp_leave != null ? ($emp_leave->num_leave ? $emp_leave->num_leave : 0) : 0;
    }

    //get attendance fingerprint
    public static function getAttendFingerPrint($emp_id = null, $from_date = null, $to_date = null)
    {
        $attendance = TimeAttendanceGenModel::select(DB::raw('SUM(total) AS total'),
                                DB::raw('SUM(nso007_hr_time_attends_gen.before) AS befores'),
                                DB::raw('SUM(late) AS late'),
                                DB::raw('SUM(missing) AS missing'),
                                DB::raw('SUM(total_hol) AS total_hol'),
                                DB::raw('SUM(total_sun) AS total_sun'),
                                DB::raw('SUM(total_ot) AS total_ot'))
                            ->where('emp_id', $emp_id)
                            ->whereBetween('att_date', [$from_date, $to_date])
                            ->limit(1)
                            ->first();
        $total      = $attendance != null ? ($attendance->total ? $attendance->total : 0) : 0;
        $before     = $attendance != null ? ($attendance->befores ? $attendance->befores : 0) : 0;
        $late       = $attendance != null ? ($attendance->late ? $attendance->late : 0) : 0;
        $missing    = $attendance != null ? ($attendance->missing ? $attendance->missing : 0) : 0;

        $total_hol  = $attendance != null ? ($attendance->total_hol ? $attendance->total_hol : 0) : 0;
        $total_sun  = $attendance != null ? ($attendance->total_sun ? $attendance->total_sun : 0) : 0;
        $total_ot   = $attendance != null ? ($attendance->total_ot ? $attendance->total_ot : 0) : 0;

        return [
            'total'             => $total,
            'before'            => $before,
            'late'              => $late,
            'missing'           => $missing,
            'total_holiday'     => $total_hol,
            'total_sunday'      => $total_sun,
            'total_ot'          => $total_ot,
        ];
    }
    //get take leave missing in attendace
    public static function getTakeLeaveMissing($emp_id = null, $from_date = null, $to_date = null)
    {
        $total_leave    = 0;
        $attendance     = TimeAttendanceGenModel::select('att_date')
                                            ->where('emp_id', $emp_id)
                                            ->whereBetween('att_date', [$from_date, $to_date])
                                            ->where('missing', 1)
                                            ->get();
        foreach($attendance as $att)
        {
            $att_date   = $att->att_date;
            $s_day      = getOne("SELECT DAYNAME ('".$att_date."')");

            if($s_day == "Sunday"){
                $total_leave = 0;
            }
            else{
                $total_leave += 1;

            }
        }
        return $total_leave ? $total_leave : 0;
    }

    //get working sunday
    public static function getWorkingSunday($emp_id = null, $from_date = null, $to_date = null)
    {
        $work_sunday = TimeAttendanceGenModel::where('emp_id', $emp_id)
                        ->where('ot_type', 'otsun')
                        ->whereBetween('att_date', [$from_date, $to_date])
                        ->count();
        return $work_sunday ? $work_sunday * 15 : 0;
    }

    //get blackout
    public static function getBlackout($from_date = null, $to_date = null)
    {
        $blackout   = BlackOutModel::select(DB::raw('SUM(deduct) AS total_deduct'))
                            ->where('status', 1)
                            ->whereBetween('date', [$from_date, $to_date])
                            ->limit(1)
                            ->first();
        return  $blackout->total_deduct > 0 ? $blackout->total_deduct : 0;

    }

}
