<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class HrmEmployeeMonthlyEarningDeduction extends Model
{

    protected $table ='hrm_monthly_earning_deductions';

 
    
    protected $fillable = [
        'employ_id','year','month', 'basic_salary','title','amount','type','tax','is_sent','is_book','is_approved'	
    ];

     public function hrmEmployee() {
        
       return $this->belongsTo('App\HrmEmployee','employ_id');
    }

    public static function getData($employ_id = null , $month = null , $year = null)
    {
    	
         $employee_earning = 0;

         $employee_deductions = 0;

         $closing_bf = 0;

    	$total_calculated_earning = HrmEmployeeMonthlyEarningDeduction::where('employ_id' , $employ_id)->where('month', $month)->where('year', $year)->where('type', 1)->sum('amount');

    	$total_calculated_deduction = HrmEmployeeMonthlyEarningDeduction::where('employ_id' , $employ_id)->where('month', $month)->where('year', $year)->where('type', 0)->sum('amount');

        $tax =  HrmEmployeeMonthlyEarningDeduction::where('employ_id' , $employ_id)->where('month', $month)->where('year', $year)->sum('tax');

        $HrmEmployeeAdvance = HrmEmployeeLoanAdjusment::where('employ_id', $employ_id)->where('type' , 'Advance')->whereMonth('date' , $month)->whereYear('date', $year)->sum('adjustment_amount');

        $HrmEmployeeLoan = HrmEmployeeLoanAdjusment::where('employ_id', $employ_id)->where('type' , 'Loan')->whereMonth('date' , $month)->whereYear('date', $year)->sum('adjustment_amount');

        $total_remaning_loan = HrmEmployeeLoan::where('employ_id' , $employ_id)->where('type' , 'Loan')->whereMonth('date' , $month)->whereYear('date', $year)->sum('remaning');

        $total_loan = HrmEmployeeLoan::where('employ_id' , $employ_id)->where('type' , 'Loan')->whereMonth('date' , $month)->whereYear('date', $year)->sum('loan_amount');

        $benevolent_found = HrmEmployeeBenevolentFoundsetting::latest()->first()->bf_fund;

        $benevolent_found = ( $total_calculated_earning / 100 ) * $benevolent_found;

        $benevolent_found_opning = HrmEmployeeBenevolentFound::where('employ_id' , $employ_id)->sum('bf_fund_company_con');

        $benevolent_found_deduction = HrmEmployeeBenevolentFound::where('employ_id' , $employ_id)->where('month' , $month)->where('year', $year)->sum('bf_fund');

        $closing_bf = $benevolent_found_opning + $benevolent_found_deduction;


        $benevolent_found_comp_con = HrmEmployeeBenevolentFound::where('employ_id' , $employ_id)->where('month' , $month)->where('year', $year)->sum('bf_fund_company_con');

        $total_earnings = HrmEmployeeMonthlyEarningDeduction::where('employ_id' , $employ_id)->where('month', $month)->where('year', $year)->where('type', 1)->get(['title' , 'amount']);

        $total_leave = HrmAttendance::where('employ_id' , $employ_id)->where('present_absent' , 2)->whereYear('date', $year)->whereMonth('date', $month)->count();

        $basic_salary = 0 ; 
        $house_allowances = 0;
        $medical_allowances = 0;
        $utility_allowances = 0;
        $arears_allowances = 0;
        $other_allowances = 0;

        $newDataArray = [];

        if($total_earnings->count() > 0  )

        {
            foreach ($total_earnings as $key => $value) 
            {   
                if ($value->title == 'Monthly Salary') 
                {
                    $basic_salary = $value->amount;
                }

                if ($value->title == 'House Rent Allownace') 
                {
                    $house_allowances = $value->amount;
                }

                if ($value->title == 'Medical Allowance') 
                {
                    $medical_allowances = $value->amount;
                }

                if ($value->title == 'Utility Allowance') 
                {
                    $utility_allowances = $value->amount;
                }

                if ($value->title == 'Arears, if any.') 
                {
                    $arears_allowances = $value->amount;
                }

                if ($value->title == 'Others / Allowances') 
                {
                    $other_allowances = $value->amount;
                }

            }
        }


         $newDataArray[] = array('basic_salary' => $basic_salary ,'house_rent' => $house_allowances , 'medical' => $medical_allowances , 'utility' =>$utility_allowances , 'arears' => $arears_allowances , 'others' => $other_allowances);

         $employee_deductions = HrmEmployeeMonthlyEarningDeduction::where('employ_id' , $employ_id)->where('month', $month)->where('year', $year)->where('type', 0)->get(['title','amount']);

          $eobi_deduction = 0;
          $sessi_deduction = 0;

          if($employee_deductions->count() > 0  )

        {
            foreach ($employee_deductions as $key => $value) 
            {
                if ($value->title == 'EOBI (Employee Cont)') 
                {
                    $eobi_deduction = $value->amount;
                }

                if ($value->title == 'SESSI') 
                {
                    $sessi_deduction = $value->amount;
                }

            }
        }

          $deductionDataArray[] =  array('eobi' => $eobi_deduction , 'sessie' => $sessi_deduction );


        $already_exist  = 0;

        $find = HrmEmployeeMonthlyEarningDeduction::where('employ_id',$employ_id)
        ->where('year', $year)
        ->where('month', $month)->count();


        if ($find == 0)
        {
            $already_exist = 1;
        }

    	 return [$total_calculated_earning , $total_calculated_deduction , $already_exist , $tax , $benevolent_found , $HrmEmployeeLoan, $HrmEmployeeAdvance , $total_remaning_loan,$total_loan, $benevolent_found_comp_con , $benevolent_found_opning , $benevolent_found_deduction , $newDataArray , $deductionDataArray, $closing_bf , $total_leave];
    }
}

		
