<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;
use App\Payroll;

class PayrollDetail extends Model
{

   private $generic_condition;

   public function __construct()
    {
           
       $this->setCondition(['is_recycle' => '0']);
    }

    public function setCondition($condition = [])
    {
        $this->generic_condition = $condition;
    }

    public function getCondition()
    {
        return $this->generic_condition;
    }

    public function getAllowanceSum($user_id,$minute_sheet_id)
    {
       
        $condition = $this->getCondition();

        $condition += ['user_id' => $user_id,'minute_sheet_id' => $minute_sheet_id, 'deduction_id'=> '0','contract_id'=>'0'];

        $allowancesum = PayrollDetail::where($condition)->sum('amount');

       return $allowancesum;

    }
        public function getBasicSalary($user_id,$minute_sheet_id)
    {
       
        $condition = $this->getCondition();

        $condition += ['user_id' => $user_id,'minute_sheet_id' => $minute_sheet_id, 'deduction_id'=> '0','contract_detail_id'=> '0'];

        $basic_salary = PayrollDetail::where($condition)->sum('amount');

       return $basic_salary;

    }
    //Allowance_id refers to contract_detail_id in payroll_detail 

    public function updatePayrollDetailAmount($object,$allowance_id,$allowance_amount,$column)
    {


        return \DB::transaction(function () use ($object,$allowance_id,$allowance_amount,$column)  {

            $condition = $this->getCondition();
            $condition += ['minute_sheet_id' => $object['minute_id'],'user_id'=> $object['user_id'], $column => $allowance_id];

            $curr_payroll_detail = PayrollDetail::where($condition)->first();

            $curr_payroll_detail->amount = $allowance_amount;

            $curr_payroll_detail->update();

        });

    }

    public function getAllowanceSumByType($user_id,$minute_id,$type)
    {

 
      $condition = ['payroll_details.user_id' => $user_id,'payroll_details.minute_sheet_id'=> $minute_id,'allowances.type'=> $type,'payroll_details.is_recycle'=> 0,'contract_details.is_recycle'=> 0,'allowances.is_recycle'=> 0];

          return PayrollDetail::

            Join('contract_details','contract_details.id','=','payroll_details.contract_detail_id')

            ->Join('allowances','allowances.id','=','contract_details.allowance_id')

            ->where($condition)

           ->sum('payroll_details.amount');

        


    }

        public function getAllowanceName($id)
    {
 
      $condition = ['payroll_details.contract_detail_id' => $id];

           $payroll_detail = PayrollDetail::

            Join('contract_details','contract_details.id','=','payroll_details.contract_detail_id')

            ->Join('allowances','allowances.id','=','contract_details.allowance_id')

            ->groupBy('allowances.title')
 
            ->selectRaw('allowances.title')

            ->where($condition)

            ->first();

             return $payroll_detail->title;


    }

     public function getNonTaxableAllowanceSum($user_id,$minute_sheet_id)
    {
 
      $condition = ['payroll_details.is_recycle' => '0','contract_details.is_recycle' => '0','allowances.is_recycle'=> '0','payroll_details.minute_sheet_id' => $minute_sheet_id,'payroll_details.user_id' => $user_id ,'allowances.is_taxable' => '0'];

          return PayrollDetail::

             Join('contract_details','contract_details.id','=','payroll_details.contract_detail_id')

            ->Join('allowances','allowances.id','=','contract_details.allowance_id')

            ->where($condition)

            ->sum('payroll_details.amount');


    }


   
    public function calculateTax($taxable_salary)
    {


        $tax = new Tax;

        $yearly_salary          = $taxable_salary * 12;



        $monthly_tax_amount = 0;


        $curr_tax_slab          = $tax->getTaxSlab($yearly_salary);

        
          if(isset($curr_tax_slab->id))
          {
        $exceeding_amount       = ($yearly_salary - $curr_tax_slab->minimum);
        $yearly_tax_amount      =  ($curr_tax_slab->percentage * $exceeding_amount) + $curr_tax_slab->fix_amount ;
        $monthly_tax_amount     =   $yearly_tax_amount/12;




        return $data = ['monthly_tax_amount'=> $monthly_tax_amount,'tax_id' => $curr_tax_slab->id]; 

          }

           return 0;



    }

    public function getLoanSum($user_id,$minute_sheet_id)
    {
         $condition = ['payroll_details.is_recycle' => '0','deductions.is_recycle' => '0','loans.is_recycle'=> '0','payroll_details.minute_sheet_id' => $minute_sheet_id,'payroll_details.user_id' => $user_id];

        return PayrollDetail::

             Join('deductions','deductions.id','=','payroll_details.deduction_id')

            ->Join('loans','loans.id','=','deductions.loan_id')

            ->where($condition)

            ->sum('payroll_details.amount');
        
    }

        public function getUserLoanIdByPayrollDetail($user_id,$minute_sheet_id)
    {
         $condition = ['payroll_details.is_recycle' => '0','deductions.is_recycle' => '0','loans.is_recycle'=> '0','payroll_details.minute_sheet_id' => $minute_sheet_id,'payroll_details.user_id' => $user_id];

        return PayrollDetail::

             Join('deductions','deductions.id','=','payroll_details.deduction_id')

            ->Join('loans','loans.id','=','deductions.loan_id')

            ->where($condition)

   

            ->first();
        
    }

    

    public function getDeductionSum($user_id,$minute_sheet_id)
    {

        $condition = $this->getCondition();

        $condition += ['user_id' => $user_id,'minute_sheet_id' => $minute_sheet_id, 'contract_detail_id'=> '0','contract_id'=> '0'];

        $deductionsum = PayrollDetail::where($condition)->sum('amount');

       return $deductionsum;

    }


    public function getPayrollDetail($user_id,$minute_sheet_id)
    {

        $condition = $this->getCondition();

        $condition += ['user_id' => $user_id,'minute_sheet_id' => $minute_sheet_id];

        return PayrollDetail::where($condition)->with(['user','contract'])->get();

      
    }
     public function getDistinctContract($minute_sheet_id,$distinct_column,$column = "*")
    {
      $condition = $this->getCondition();

      $condition += ['minute_sheet_id' => $minute_sheet_id];

      return PayRollDetail::where($condition)->distinct($distinct_column)->select($column)->get();
    }


     public function getPayrollDetailByMinuteId($id,$mode = ['Cash','Cheque','Online'])
     {

     	$condition = ['payroll_details.is_recycle' => 0,'payroll_details.minute_sheet_id' => $id,'users.is_recycle'=> 0];

      return PayrollDetail::

             Join('users','users.id','=','payroll_details.user_id')

             ->Join('designations','designations.id','=','users.designation_id')

             ->Join('contracts','users.id','=','contracts.user_id')

             ->groupBy('users.id','designations.title','contracts.location','users.account_no')

            ->where($condition) 

            ->whereIn('users.salary_mode',$mode)
 
            ->selectRaw('users.name,users.id,users.salary_mode,designations.title,contracts.location,users.account_no')

            ->get();

    	
        // return PayrollDetail::where($condition)->orderBy('id','desc')->get();
     }
        public function massInsertPayrollDetail($object)
    {
        return \DB::transaction(function () use ($object)  {

            PayrollDetail::insert($object);
        });
    }

    public function user()
    {
      return $this->belongsTo(User::class,'user_id')->withDefault();
    }

    // public function contract()
    // {
    //   return $this->belongsTo(User::class,'contract_id')->withDefault();
    // }

    public function minuteSheet()
    {
        return $this->belongsTo(MinuteSheet::class,'minute_sheet_id')->withDefault();
    }

    public function calculateNetSalary($user_id,$minute_id)
    {


              $basic_salary = $this->getBasicSalary($user_id,$minute_id);

              $allowance_sum = $this->getAllowanceSum($user_id,$minute_id);

              $deduction_sum = $this->getDeductionSum($user_id,$minute_id);

              $gross_salary = ($basic_salary + $allowance_sum)-($deduction_sum);

              $non_taxable_allowances = $this->getNonTaxableAllowanceSum($user_id,$minute_id);

              $taxable_amount = $gross_salary - $non_taxable_allowances;

              $tax = $this->calculateTax($taxable_amount);

              $net_salary = $gross_salary-$tax['monthly_tax_amount'];

              return $data = ['basic_salary' => $basic_salary, 'allowance_sum' => $allowance_sum,'deduction_sum' => $deduction_sum,'gross_salary' => $gross_salary,'non_taxable_allowances'=> $non_taxable_allowances, 'taxable_amount' => $taxable_amount,'tax'=> $tax,'net_salary'=> $net_salary,'tax_id'=> $tax['tax_id']];
    }

    // public function getPayrollDetail($user_id,$minute_sheet_id)
    // {
    //     $condition  = $this->getCondition();
    //     $condition += ['user_id' => $user_id , 'minute_sheet_id' =>$minute_sheet_id];


    //     return PayrollDetail::where($condition)->get();
    // }

    public function getPayrollList()
    {
        return Payroll::where($this->getCondition())->orderBy('id','desc')->first();
    }

    public function contractDetail()
    {
         return $this->belongsTo(ContractDetail::class,'contract_detail_id')->with(['assignAllowance'])->withDefault();
    }
      public function payRoll()
    {
         return $this->belongsTo(Payroll::class)->withDefault();
    }

    public function allowance()
    {
        return $this->belongsTo(Allowance::class,'allowance_id')->withDefault();
    }

    public function deduction()
    {
        return $this->belongsTo(Deduction::class,'deduction_id')->withDefault();
    }
        public function contract()
    {
        return $this->belongsTo(Contract::class,'contract_id')->withDefault();
    }
}
