<?php

namespace App\Http\Controllers;

use DateTime;
use DatePeriod;
use DateInterval;
use Carbon\Carbon; 
use App\Models\TimeSheet;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;


class TimesheetController extends Controller
{
    public function index()
    {  
        $company_id = Auth::user()->company_id;   
        $data['pay_rates']  = TimeSheet::employee_payrates_all();

        return view('timesheet.dashboard',$data);
    }

    public function store(Request $request)
    {        
        echo 'Danda Satish';
           
        return redirect()->back()->withSuccess('Saved Successfully');
    }

    public function show($id)
    {
        $company_id = Auth::user()->company_id;                 
        $data['pay_rates']  = TimeSheet::employee_payrates_single($id);
        $data['pay_rates_yearly']    = TimeSheet::yearly_sheets_single($id);

        return view('timesheet.showtimesheet',$data);
    }

  
    public function student_worked_hours(Request $request)
    {
        $id = Auth::user()->id;
        $data['pay_rates']  = TimeSheet::employee_payrates_single($id);


        $data['timesheet_weekly_total'] = DB::table('timesheet_week_total')
                                            ->where('employee_id',Auth::user()->id)
                                            ->orderBy('month_value', 'asc')
                                            ->orderBy('year_value', 'dsc')
                                            ->get();

        $data['time_sheet_yearly']    = TimeSheet::yearly_sheets_single($id);
       
        return view('timesheet.worked_hours',$data);
    }
  
    public function getClientdata($id)
    {        
        $data = DB::table('clients')
                ->leftjoin('countries','countries.id','=','clients.country')
                ->select('countries.name as c_name','clients.*')
                ->where('clients.id',$id)
                ->first();

        echo json_encode($data);
    } 
   

    public function weeks_wise_working_hours(Request $request)
    {
        $rules = [
            'employee_id' => 'required',
            'actual_week' => 'required'
        ];

        $value = explode('-',$request->employee_id);
        if(count($value) > 1){
            $request['employee_id'] = $value[0];
            $request['person_type'] = $value[1];
        }else{
            $request['employee_id'] = $request->employee_id; 
            $request['person_type'] = 'consultant';
        }
            
        $this->validate($request,$rules);
                  
       // WORKED HOURS
       $monday          = isset($request->monday)?$request->monday:0;
       $tuesday         = isset($request->tuesday)?$request->tuesday:0;
       $wednesday       = isset($request->wednesday)?$request->wednesday:0;
       $thursday        = isset($request->thursday)?$request->thursday:0;
       $friday          = isset($request->friday)?$request->friday:0;
       $saturday        = isset($request->saturday)?$request->saturday:0;
       $sunday          = isset($request->sunday)?$request->sunday:0;

       // OT Hours
       $monday_ot       = isset($request->monday_ot)?$request->monday_ot:0;
       $tuesday_ot      = isset($request->tuesday_ot)?$request->tuesday_ot:0;
       $wednesday_ot    = isset($request->wednesday_ot)?$request->wednesday_ot:0;
       $thursday_ot     = isset($request->thursday_ot)?$request->thursday_ot:0;
       $friday_ot       = isset($request->friday_ot)?$request->friday_ot:0;
       $saturday_ot     = isset($request->saturday_ot)?$request->saturday_ot:0;
       $sunday_ot       = isset($request->sunday_ot)?$request->sunday_ot:0;
       $total_hours     = isset($request->total_hours)?$request->total_hours:0;
       $work_report     = isset($request->work_report)?$request->work_report:'';
       $employee_id     = $request->employee_id;
       $total_work_hours= $request->total_work_hours;
       $total_ot_hours  = $request->total_ot_hours;
       $employee_payrate_id  = $request->employee_payrate_id;

        
        $attachment_name = '';
        if($request->file('client_docs')){
        $extension = $request->file('client_docs')->getClientOriginalExtension();
        $time = rand(1,100000) . time();
        $file = $request->file('client_docs')->move(storage_path('/clients_docs/'), $time .".".$extension);
        $attachment_name = $time .".".$extension;
        }
        
       $check_week = DB::table('timesheet_weekly')
                    ->where('emp_id', $request->employee_id)
                    ->where('week_value',$request->actual_week)
                    ->where('year_value',$request->actual_year)
                    ->where('person_type',$request->person_type)
                    ->first();

        // Total Hours and Work Report Weekly Wise
        if($check_week){
            //Convert to Json data and Insert And Update Weekly Wise.
            DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['total_hours'=>$total_hours,'work_report'=>$work_report,'client_attachmnet'=>$attachment_name,'total_work_hours'=>$total_work_hours,'total_ot_hours'=>$total_ot_hours]);

            if($request->monday != 0){
                $monday_array = array( 'WH' => "$monday", 'OT' => "$monday_ot" );
                $monday_json  = json_encode($monday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['monday'=>$monday_json]);
            }if($request->tuesday != 0){
                $tuesday_array = array( 'WH' => "$tuesday", 'OT' => "$tuesday_ot" );
                $tuesday_json  = json_encode($tuesday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['tuesday'=>$tuesday_json]);
            }if($request->wednesday != 0){
                $wednesday_array = array( 'WH' => "$wednesday", 'OT' => "$wednesday_ot" );
                $wednesday_json  = json_encode($wednesday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['wednesday'=>$wednesday_json]);
            }if($request->thursday != 0){
                $thursday_array = array( 'WH' => "$thursday", 'OT' => "$thursday_ot" );
                $thursday_json  = json_encode($thursday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['thursday'=>$thursday_json]);
            }if($request->friday != 0){
                $friday_array = array( 'WH' => "$friday", 'OT' => "$friday_ot" );
                $friday_json  = json_encode($friday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['friday'=>$friday_json]);
            }if($request->saturday != 0){
                $saturday_array = array( 'WH' => "$saturday", 'OT' => "$saturday_ot" );
                $saturday_json  = json_encode($saturday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['saturday'=>$saturday_json]);
            }if($request->sunday != 0){
                $sunday_array = array( 'WH' => "$sunday", 'OT' => "$sunday_ot" );
                $sunday_json  = json_encode($sunday_array);
                DB::table('timesheet_weekly')->where('emp_id',$request->employee_id)->where('week_value',$request->actual_week)->update(['sunday'=>$sunday_json]);
            } 
        }else{
            //'month_value' =>$request->month_value,
            $insert_week = DB::table('timesheet_weekly')->insertGetId(['emp_id'=>$request->employee_id,'week_value'=>$request->actual_week,'year_value'=>$request->actual_year,'employeepayrate_id'=>$employee_payrate_id,'person_type'=>$request->person_type]);
            //Convert to Json data and Insert And Update Weekly Wise.
            DB::table('timesheet_weekly')->where('id',$insert_week)->update(['total_hours'=>$total_hours,'work_report'=>$work_report,'client_attachmnet'=>$attachment_name,'total_work_hours'=>$total_work_hours,'total_ot_hours'=>$total_ot_hours]);
            if($request->monday != 0){
                $monday_array = array( 'WH' => "$monday", 'OT' => "$monday_ot" );
                $monday_json  = json_encode($monday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['monday'=>$monday_json]);
            }if($request->tuesday != 0){
                $tuesday_array = array( 'WH' => "$tuesday", 'OT' => "$tuesday_ot" );
                $tuesday_json  = json_encode($tuesday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['tuesday'=>$tuesday_json]);
            }if($request->wednesday != 0){
                $wednesday_array = array( 'WH' => "$wednesday", 'OT' => "$wednesday_ot" );
                $wednesday_json  = json_encode($wednesday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['wednesday'=>$wednesday_json]);
            }if($request->thursday != 0){
                $thursday_array = array( 'WH' => "$thursday", 'OT' => "$thursday_ot" );
                $thursday_json  = json_encode($thursday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['thursday'=>$thursday_json]);
            }if($request->friday != 0){
                $friday_array = array( 'WH' => "$friday", 'OT' => "$friday_ot" );
                $friday_json  = json_encode($friday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['friday'=>$friday_json]);
            }if($request->saturday != 0){
                $saturday_array = array( 'WH' => "$saturday", 'OT' => "$saturday_ot" );
                $saturday_json  = json_encode($saturday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['saturday'=>$saturday_json]);
            }if($request->sunday != 0){
                $sunday_array = array( 'WH' => "$sunday", 'OT' => "$sunday_ot" );
                $sunday_json  = json_encode($sunday_array);
                DB::table('timesheet_weekly')->where('id',$insert_week)->update(['sunday'=>$sunday_json]);
            } 
        }

        // Total Hours and Work Report Yearly Wise

        if($request->monday != 0){

            $monday_array = array( 'WH' => "$monday", 'OT' => "$monday_ot" );
            $monday_json  = json_encode($monday_array);

            $date_D = date('d',strtotime($request->day1));
            $date_M = date('m',strtotime($request->day1));
            $date_Y = date('Y',strtotime($request->day1));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$monday_json];
            if($check_year){
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{
                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->tuesday != 0){

            $tuesday_array = array( 'WH' => "$tuesday", 'OT' => "$tuesday_ot" );
            $tuesday_json  = json_encode($tuesday_array);

            $date_D = date('d',strtotime($request->day2));
            $date_M = date('m',strtotime($request->day2));
            $date_Y = date('Y',strtotime($request->day2));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$tuesday_json];
            if($check_year){     
            $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{
            $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->wednesday != 0){

            $wednesday_array = array( 'WH' => "$wednesday", 'OT' => "$wednesday_ot" );
            $wednesday_json  = json_encode($wednesday_array);

            $date_D = date('d',strtotime($request->day3));
            $date_M = date('m',strtotime($request->day3));
            $date_Y = date('Y',strtotime($request->day3));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$wednesday_json];
            if($check_year){
                
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{
                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->thursday != 0){

            $thursday_array = array( 'WH' => "$thursday", 'OT' => "$thursday_ot" );
            $thursday_json  = json_encode($thursday_array);

            $date_D = date('d',strtotime($request->day4));
            $date_M = date('m',strtotime($request->day4));
            $date_Y = date('Y',strtotime($request->day4));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$thursday_json];
            if($check_year){
                
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
               
            }else{
                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->friday != 0){

            $friday_array = array( 'WH' => "$friday", 'OT' => "$friday_ot" );
            $friday_json  = json_encode($friday_array);

            $date_D = date('d',strtotime($request->day5));
            $date_M = date('m',strtotime($request->day5));
            $date_Y = date('Y',strtotime($request->day5));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$friday_json];
            if($check_year){
                
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{
                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->saturday != 0){

            $saturday_array = array( 'WH' => "$saturday", 'OT' => "$saturday_ot" );
            $saturday_json  = json_encode($saturday_array);

            $date_D = date('d',strtotime($request->day6));
            $date_M = date('m',strtotime($request->day6));
            $date_Y = date('Y',strtotime($request->day6));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
            $value = ["$date_D"=>$saturday_json];
            if($check_year){
                
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{

                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }if($request->sunday != 0){

            $sunday_array = array( 'WH' => "$sunday", 'OT' => "$sunday_ot" );
            $sunday_json  = json_encode($sunday_array);

            $date_D = date('d',strtotime($request->day7));
            $date_M = date('m',strtotime($request->day7));
            $date_Y = date('Y',strtotime($request->day7));

            $check_year = $this->checkYear($date_M,$date_Y,$request->employee_id,$request->person_type);
             $value = ["$date_D"=>$sunday_json];
            if($check_year){
               
                $this->updateYearData($date_M,$date_Y,$request->employee_id,$value);
            }else{
                $this->insertYearData($date_M,$date_Y,$request->employee_id,$employee_payrate_id,$value,$request->person_type);
            } 
        }

        //ISSUE RAISE BY DANDA SATISH
        // Total Hours Weekly Wise Sum
        if($request->monday != 0){
            $d = date('d',strtotime($request->day1));
            $m = date('m',strtotime($request->day1));
            $y = date('Y',strtotime($request->day1)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total = $request->monday + $request->monday_ot;
                DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" => $total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->monday,'total_ot_hours'=>$request->monday_ot,'person_type'=>$request->person_type]);
            }else{
                $total = $weeks_get->weekCount + $request->monday + $request->monday_ot;
                $sum = $total - ($request->get_mon_value_reg + $request->get_mon_value_ot);

                $total_reg_monday = $weeks_get->RegHours + $request->monday;
                $total_reg_hours_monday = $total_reg_monday - $request->get_mon_value_reg;
                $total_ot_monday =  $weeks_get->OtHours + $request->monday_ot;
                $total_ot_hours_monday = $total_ot_monday - $request->get_mon_value_ot;
                
                DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_monday,'total_ot_hours'=>$total_ot_hours_monday]);
            }
        }if($request->tuesday != 0){
            $d = date('d',strtotime($request->day2));
            $m = date('m',strtotime($request->day2));
            $y = date('Y',strtotime($request->day2)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();


            if($weeks_get==null){
                $total = $request->tuesday + $request->tuesday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->tuesday,'total_ot_hours'=>$request->tuesday_ot,'person_type'=>$request->person_type]);
            }else{
                $total = $weeks_get->weekCount + $request->tuesday + $request->tuesday_ot;
                $sum = $total - ($request->get_tue_value_reg + $request->get_tue_value_ot);

                $total_reg_tuesday = $weeks_get->RegHours + $request->tuesday;
                $total_reg_hours_tuesday = $total_reg_tuesday - $request->get_tue_value_reg;
                $total_ot_tuesday =  $weeks_get->OtHours + $request->tuesday_ot;
                $total_ot_hours_tuesday = $total_ot_tuesday - $request->get_tue_value_ot;
                

            DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_tuesday,'total_ot_hours'=>$total_ot_hours_tuesday]);
            }
        }if($request->wednesday != 0){
            $d = date('d',strtotime($request->day3));
            $m = date('m',strtotime($request->day3));
            $y = date('Y',strtotime($request->day3)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total = $request->wednesday + $request->wednesday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->wednesday,'total_ot_hours'=>$request->wednesday_ot,'person_type'=>$request->person_type]);
            }else{
                   $total = $weeks_get->weekCount + $request->wednesday + $request->wednesday_ot;
                   $sum = $total - ($request->get_wed_value_reg + $request->get_wed_value_ot);

                    $total_reg_wednesday = $weeks_get->RegHours + $request->wednesday;
                    $total_reg_hours_wednesday = $total_reg_wednesday - $request->get_wed_value_reg;
                    $total_ot_wednesday =  $weeks_get->OtHours + $request->wednesday_ot;
                    $total_ot_hours_wednesday = $total_ot_wednesday - $request->get_wed_value_ot;
                

             DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_wednesday,'total_ot_hours'=>$total_ot_hours_wednesday]);
            }
        }if($request->thursday != 0){
            $d = date('d',strtotime($request->day4));
            $m = date('m',strtotime($request->day4));
            $y = date('Y',strtotime($request->day4)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total = $request->thursday + $request->thursday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->thursday,'total_ot_hours'=>$request->thursday_ot,'person_type'=>$request->person_type]);
            }else{
                    $total = $weeks_get->weekCount + $request->thursday + $request->thursday_ot;
                    $sum  = $total - ($request->get_thu_value_reg + $request->get_thu_value_ot);
                    $total_reg_thursday = $weeks_get->RegHours + $request->thursday;
                    $total_reg_hours_thursday = $total_reg_thursday - $request->get_thu_value_reg;
                    $total_ot_thursday =  $weeks_get->OtHours + $request->thursday_ot;
                    $total_ot_hours_thursday = $total_ot_thursday - $request->get_thu_value_ot;
                

                DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_thursday,'total_ot_hours'=>$total_ot_hours_thursday]);
            }
        }if($request->friday != 0){
            $d = date('d',strtotime($request->day5));
            $m = date('m',strtotime($request->day5));
            $y = date('Y',strtotime($request->day5)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total = $request->friday + $request->friday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->friday,'total_ot_hours'=>$request->friday_ot,'person_type'=>$request->person_type]);
            }else{
                   $total = $weeks_get->weekCount + $request->friday + $request->friday_ot;
                   $sum = $total - ($request->get_fri_value_reg + $request->get_fri_value_ot);
                    
                    $total_reg_friday = $weeks_get->RegHours + $request->friday;
                    $total_reg_hours_friday = $total_reg_friday - $request->get_fri_value_reg;
                    $total_ot_friday =  $weeks_get->OtHours + $request->friday_ot;
                    $total_ot_hours_friday = $total_ot_friday - $request->get_fri_value_ot;
                

                DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_friday,'total_ot_hours'=>$total_ot_hours_friday]);
            }
        }if($request->saturday != 0){
            $d = date('d',strtotime($request->day6));
            $m = date('m',strtotime($request->day6));
            $y = date('Y',strtotime($request->day6)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total =  $request->saturday + $request->saturday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->saturday,'total_ot_hours'=>$request->saturday_ot,'person_type'=>$request->person_type]);
            }else{
                   $total = $weeks_get->weekCount + $request->saturday + $request->saturday_ot;
                   $sum = $total - ($request->get_sat_value_reg + $request->get_sat_value_ot);

                    $total_reg_saturday = $weeks_get->RegHours + $request->saturday;
                    $total_reg_hours_saturday = $total_reg_saturday - $request->get_sat_value_reg;
                    $total_ot_saturday =  $weeks_get->OtHours + $request->friday_ot;
                    $total_ot_hours_saturday = $total_ot_saturday - $request->get_sat_value_ot;
                

                DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_saturday,'total_ot_hours'=>$total_ot_hours_saturday]);
            }
        }if($request->sunday != 0){
            $d = date('d',strtotime($request->day7));
            $m = date('m',strtotime($request->day7));
            $y = date('Y',strtotime($request->day7)); 

            $weekNumber = TimeSheet::weekOfMonth($y,$m,$d);

            $weeks_get =  DB::table('timesheet_week_total')
                            ->where('employee_id',$employee_id)
                            ->where('month_value',$m)
                            ->where('year_value',$y)
                            ->where('person_type',$request->person_type)
                            ->select('timesheet_week_total.week'."$weekNumber". ' as weekCount','total_reg_hours as RegHours','total_ot_hours as OtHours')
                            ->first();

            if($weeks_get==null){
                $total = $request->sunday + $request->sunday_ot;
            DB::table('timesheet_week_total')->insert(['employee_id'=>$employee_id,'month_value'=>$m,'year_value'=>$y,'week'."$weekNumber" =>$total,'employeepayrate_id'=>$employee_payrate_id,'total_reg_hours'=>$request->sunday,'total_ot_hours'=>$request->sunday_ot,'person_type'=>$request->person_type]);
            }else{
                   $total = $weeks_get->weekCount + $request->sunday + $request->sunday_ot;
                   $sum = $total - ($request->get_sun_value_reg + $request->get_sun_value_ot);
                
                    $total_reg_sunday = $weeks_get->RegHours + $request->sunday;
                    $total_reg_hours_sunday = $total_reg_sunday - $request->get_sun_value_reg;
                    $total_ot_sunday =  $weeks_get->OtHours + $request->friday_ot;
                    $total_ot_hours_sunday = $total_ot_sunday - $request->get_sun_value_ot;
                

                DB::table('timesheet_week_total')->where('month_value',$m)->where('year_value',$y)->where('employee_id',$employee_id)->where('person_type',$request->person_type)->update(['week'."$weekNumber"=>$sum,'total_reg_hours'=>$total_reg_hours_sunday,'total_ot_hours'=>$total_ot_hours_sunday]);
            }
        }
    }

    public function timeheet_weekly_data(Request $request)
    {
        $week_value = $request->id;
        $year_value = $request->year; 
        $value = explode('-',$request->employee_id);

        if(count($value) > 1){
            $employee_id = $value[0];
            $person_type = $value[1];
        }else{
            $employee_id = $request->employee_id; 
            $person_type = 'consultant';
        }

        $timesheet_weekly_data = DB::table('timesheet_weekly')
                                    ->where('week_value',$week_value)
                                    ->where('year_value',$year_value)
                                    ->where('emp_id',$employee_id)
                                    ->where('person_type',$person_type)
                                    ->first();


        echo json_encode($timesheet_weekly_data);
    }
    
    public function downloadClientSheet($filename)
    {
        $path= storage_path(). "/clients_docs/".$filename; 

        return response()->file($path);

    }

    public function approvalsTimesheet()
    {
        return view('timesheet.approvaltimesheet');
    }

    public function approve_status_id(Request $request,$id)
    {
        $week = $request->presentWeekNumber;
        $year = $request->presentYear;
        $emp_id = $request->emp_id;
        $id   = $id;
       DB::table('timesheet_weekly')->where('timesheet_weekly.year_value',$year)
                                    ->where('timesheet_weekly.week_value',$week)
                                    ->where('timesheet_weekly.emp_id',$emp_id)
                                    ->update(['approved_status'=>$id]);
    }

    public function approve_status_all(Request $request,$id)
    {        
        $week = $request->presentWeekNumber;
        $year = $request->presentYear;
        $emp_id = json_decode($request->emp_id);
        $id   = $id;

        foreach ($emp_id as $key => $value) {
            DB::table('timesheet_weekly')->where('timesheet_weekly.year_value',$year)
                                    ->where('timesheet_weekly.week_value',$week)
                                    ->where('timesheet_weekly.emp_id',$value)
                                    ->update(['approved_status'=>$id]);
        }
      
    }

    public function all_employees_weekly_data(Request $request)
    {
        $week = $request->week;

        $year = $request->year;

        $results = TimeSheet::employees_weekly_data($year,$week,2);
        
        $results_approve = TimeSheet::employees_weekly_data($year,$week,1);

        $results_unapprove = TimeSheet::employees_weekly_data($year,$week,0);       
       
        return view('timesheet.employeeHours',compact('results','results_approve','results_unapprove'));
    }

    public function getDataWeekAjaxinMonth(Request $request)
    {
        $week_number    = $request->week;
        $month = $request->month;
        $year = $request->year;
        $lastDayOfWeek = '6'; //1 (for monday) to 7 (for sunday)
        $weeks = $this->getWeeksInMonth($year, $month, $lastDayOfWeek);
            foreach($weeks as $weekNumber => $week){
                if($weekNumber==$week_number ){
                    if($week[0]==$week[1]){
                        $dates_between = $week[0];
                        $start = $week[0];    
                        $end_date = $week[1];    
                        $day = date('d',strtotime($dates_between));
                        $month = date('m',strtotime($dates_between));
                        $year = date('Y',strtotime($dates_between)); 
                        $arrayName = array();
                        $dayName = array();
                        $data =  DB::table('timesheet_yearly')
                                ->select('timesheet_yearly.'."$day")
                                ->where('year_value',$year)
                                ->where('month_value',$month)
                                ->where('emp_id',Auth::user()->id)
                                ->first();
                        if($data->$day){
                            $variable = $data->$day; 
                            $arrayName[$day] = $variable;
                        }else{
                            $variable = '{"WH":0,"OT":0}';  
                            $arrayName[$day] = $variable;
                        }
                    }else{
                        $begin = new DateTime($week[0]);
                        $end = new DateTime($week[1]);
                        $end = $end->modify('+1 day');
                        $start = $week[0];    
                        $end_date = $week[1];
                        $daterange = new DatePeriod($begin, new DateInterval('P1D'), $end);
                        $arrayName = array();
                        $dayName = array();
                        foreach($daterange as $key=>$date){
                            $dates_between = $date->format("Y-m-d");    
                            $day = date('d',strtotime($dates_between));
                            $month = date('m',strtotime($dates_between));
                            $year = date('Y',strtotime($dates_between)); 
                    
                            $data =  DB::table('timesheet_yearly')
                                ->select('timesheet_yearly.'."$day")
                                ->where('year_value',$year)
                                ->where('month_value',$month)
                                ->where('emp_id',Auth::user()->id)
                                ->first();
                            if($data->$day){
                                $variable = $data->$day; 
                                $arrayName[$day] = $variable;
                                
                            }else{
                                $variable = '{"WH":0,"OT":0}';  
                                $arrayName[$day] = $variable;
                                
                            }
                        } 
                    }
                }
            } 
        return response()->json(['arrayName'=>$arrayName,'month'=>$month,'year'=>$year,'start'=>$start,'last'=>$end_date]);
    }

    public function getWeeksInMonth($year, $month, $lastDayOfWeek)
    {
        $aWeeksOfMonth = [];
        $date = new DateTime("{$year}-{$month}-01");
        $iDaysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $aOneWeek = [$date->format('Y-m-d')];
        $weekNumber = 1;
        for ($i = 1; $i <= $iDaysInMonth; $i++)
        {
            if ($lastDayOfWeek == $date->format('N') || $i == $iDaysInMonth)
            {
                $aOneWeek[]      = $date->format('Y-m-d');
                $aWeeksOfMonth[$weekNumber++] = $aOneWeek;
                $date->add(new DateInterval('P1D'));
                $aOneWeek = [$date->format('Y-m-d')];
                $i++;

            }
            $date->add(new DateInterval('P1D'));
        }
        return $aWeeksOfMonth;
    }

    public function getDataMonthAjaxinMonth(Request $request)
    {
        $month     = $request->month;
        $year     = $request->year;
        $emp_id     = Auth::user()->id;
        $month_details = TimeSheet::month_sheet_single($month,$year,$emp_id);
      

        $datesByWeek = $this->getWeekDays($month, $year);
        $o = '<table class="agenda">';
        $o.= '<tr><th>Week</th><th>Monday </th><th>Tuesday</th><th>Wednesday</th><th>Thursday</th><th>Friday</th><th>Saturday</th><th>Sunday</th></tr>';
        foreach ($datesByWeek as $week => $dates) {

            $firstD = $dates[0];
            $lastD = $dates[count($dates)-1];

            $o.= "<tr>";
            $o.= "<td>" . $firstD->format('M d') . ' - ' . $lastD->format('M d') . "</td>";
            $N = $firstD->format('N');
            for ($i = 1; $i < $N; $i++) {
                $o.= "<td>-</td>";
            }

            foreach ($dates as $d) {

                $dates_name = $d->format('d');
                $date_value  = 'day'.$dates_name;
                $values = $month_details->$date_value;
                if($values){
                $values = $month_details->$date_value;
                }else{
                    $values = '{"WH":"0","OT":"0"}';
                } 
            
                $o.= "<td>" . "REG-". json_decode($values)->WH ." "."OT-".  json_decode($values)->OT."</td>";
  
            }
            $N = $lastD->format('N');
            for ($i = $N; $i < 7; $i++) {
                $o.= "<td>-</td>";
            }
            $o.= "</tr>";
        }
        $o.= '</table>';
        echo $o;


    }

    public function getWeekDays($month,$year)
    {        
        $p = new DatePeriod(
        DateTime::createFromFormat('!Y-n-d', "$year-$month-01"),
        new DateInterval('P1D'),
        DateTime::createFromFormat('!Y-n-d', "$year-$month-01")->add(new DateInterval('P1M'))
        );

        $datesByWeek = array();
        foreach ($p as $d) {
        $dateByWeek[ $d->format('W') ][] = $d;
        }
       return $dateByWeek;
    }

    public function checkYear($date_M,$date_Y,$employee_id,$person_type)
    {     
        return  DB::table('timesheet_yearly')
                        ->where('month_value', $date_M)
                        ->where('year_value',$date_Y)
                        ->where('emp_id',$employee_id)
                        ->where('person_type',$person_type)
                        ->first();
    }

    public function updateYearData($date_M,$date_Y,$employee_id,$value)
    {
        return  DB::table('timesheet_yearly')
                    ->where('month_value', $date_M)
                    ->where('year_value',$date_Y)
                    ->where('emp_id',$employee_id)
                    ->update($value);
    }

    public function insertYearData($date_M,$date_Y,$employee_id,$employee_payrate_id,$value,$person_type)
    {
        $insert_year = DB::table('timesheet_yearly')
                        ->insertGetId(['month_value'=>$date_M,'year_value'=>$date_Y,'emp_id'=>$employee_id,'employeepayrate_id'=>$employee_payrate_id,'person_type'=>$person_type]);

        return  DB::table('timesheet_yearly')->where('id',$insert_year)->update($value);
    }
                        
    
}
