public function timeCardData(Request $req)
    {
        
        $start_date = Carbon::now()->startOfMonth()->toDateString();
        $end_of_month = Carbon::now()->endOfMonth()->toDateString();
        $current_date = Carbon::now()->toDateString();    
        $user_list = $req->user_list;
        // dd($user_list);
        session(['electronic_time_card' => $req->getUri()]);
        $user_type = $req->user_type;
        $sdatefrom = ($req->sdatefrom) ? $req->sdatefrom : $start_date;
        $sdateto = ($req->sdateto) ? $req->sdateto : $current_date;
        // dd($sdatefrom);
        $schedule = $req->schedule;
        $day_type = $req->day_type;
        $category = $req->category;
        $branch = $req->branch;
        $department = $req->department;
        $emp_no = $req->emp_no;
        $in_out_filter = $req->in_out_filter;
        $filter_arr = ['no_in_out', 'no_lunch', 'over_lunch', 'late_in', 'early_out', 'overtime', 'on_leave'];
        $diffDate = date_diff(date_create($sdatefrom),date_create($sdateto));
        $totalDays = $diffDate->days;
        
        // dd(date('m', strtotime($sdatefrom)));
        $total_days = Carbon::now()->month(date('m', strtotime($sdatefrom)))->daysInMonth;
        
        $overtimes_emp = 0;
        $overtime = '';

        //
        $ActiveUserShownStatus1 = Setting::where('field', 'active_user_shown')->first();
        $ActiveUserShownStatus = $ActiveUserShownStatus1->value;
        //

        $dateRange = CarbonPeriod::create($sdatefrom, $sdateto);
        $dates = $dateRange->toArray();
        
        $qry = '';
        for ($i = 0; $i < count($dateRange); $i++) {
            $alldate = $dates[$i]->format('Y-m-d');
                $qry .= 'select cast(\'' . $alldate . '\' as date) as dte ';

                if ($i < (count($dateRange) - 1)) {
                    $qry .= 'union ';
                }
        }
        
        if($sdatefrom == $sdateto){
            $qry = $qry .'union '. $qry;
        }

        //============================
        // speed issue
        $attendanceDatadCount = DB::table(DB::raw('(' . $qry . ') dates'))
            ->select((DB::raw('DISTINCT up.username')))
            ->crossJoin(DB::raw('user_personal up'));
            $attendanceDatadCount->join('user_pay', 'user_pay.username', '=', 'up.username');
            $attendanceDatadCount->join('users', 'users.username', '=', 'up.username');
            $attendanceDatadCount->where('user_pay.date_resigned', '0000-00-00')->where('user_pay.pay_status', 1)->where('users.isDeleted', 0);
        $attendanceDatadCount = $attendanceDatadCount->whereRaw('dte >= date_format(user_pay.date_attendance_start, "%Y-%m-%d")')->whereNotNull('up.duty_roaster_id')->get();
        $attendanceDataCount = count($attendanceDatadCount);
        
        $offset = 0;
        if (isset($_GET['start'])) {
            $offset = $_GET['start'];
        }

        if (isset($_GET['length'])  && $_GET['length'] > 0) {
            $length = $_GET['length'];
        }else{
            $length = $attendanceDataCount;
        }
        //============================

        $attendanceDataUsers = DB::table(DB::raw('(' . $qry . ') dates'))
            ->select(DB::raw('DISTINCT up.username'))
            ->crossJoin(DB::raw('user_personal up'));
            $attendanceDataUsers->join('user_pay', 'user_pay.username', '=', 'up.username');
            $attendanceDataUsers->join('users', 'users.username', '=', 'up.username');
            $attendanceDataUsers->where('user_pay.date_resigned', '0000-00-00')->where('user_pay.pay_status', 1)->where('users.isDeleted', 0);
        if(isset($_GET['search'])){
            $searchVal = $_GET['search']['value'];
        }
        if(isset($searchVal) && $searchVal != ""){
            $attendanceDataUsers->where('up.name', 'like', "$searchVal"."%")->orWhere('up.employee_no', 'like', "$searchVal"."%");
        }
        if (isset($_GET['order'][1]['column'])) {
            if ($_GET['order'][1]['column'] == 1) {
                $ocolumn = "up.employee_no";
            } elseif ($_GET['order'][2]['column'] == 2) {
                $ocolumn = "up.name";
            }else{
                $ocolumn = false;
            }
            
            if($ocolumn){
                if ($_GET['order'][0]['dir'] == 'asc') {
                    $attendanceDataUsers->orderBy($ocolumn, 'ASC');
                } else {
                    $attendanceDataUsers->orderBy($ocolumn, 'DESC');
                }
            }
        } else {
            $attendanceDataUsers = $attendanceDataUsers;
        }

        $attendanceDataUsers = $attendanceDataUsers->whereRaw('dte >= date_format(user_pay.date_attendance_start, "%Y-%m-%d")')->whereNotNull('up.duty_roaster_id')->skip($offset)->take($length)->get()->pluck('username')->toArray();
      
        
        DB::statement('SET SESSION group_concat_max_len = 1000000'); //Ashutosh soni
        $attendanceDatad = DB::table(DB::raw('(' . $qry . ') dates'))
            ->select('schedule_round_time.*','additional_user_schedule.aday_type', 'dates.dte', 'leave_list.no_days', 'leave_list.type as leave_type', 'leave_list.reason','duty_roaster_dates.holiday_id','duty_roaster_dates.day_type as r_day_type', 'public_holiday.description', 'sch.*', 'up.employee_no', 'up.eattendance_type', 'up.username','up.name','clk.updated_at', 
                    DB::raw('(GROUP_CONCAT(CONCAT (clk.date," ",TIME) SEPARATOR \'==\')) AS fulltime'), 'user_pay.date_attendance_start', 'user_pay.branch_id', 'user_pay.date_resigned')
            ->crossJoin(DB::raw('user_personal up'))
            ->whereIn('up.username', $attendanceDataUsers)
            ->leftJoin(DB::raw('users'), 'up.username', '=', 'users.username');
            
            
            // roster
            $attendanceDatad->join(DB::raw('duty_roaster_dates'), function ($joins) {
                $joins->on('up.duty_roaster_id', '=', 'duty_roaster_dates.duty_roaster_id')
                    ->whereRaw('duty_roaster_dates.date = dte');
            });

            // for additional schedule
            $attendanceDatad->leftJoin(DB::raw('additional_user_schedule'), function ($joins) {
                $joins->on('additional_user_schedule.username', '=', 'up.username')
                    ->whereRaw('additional_user_schedule.working_day = dte');
            });

            $attendanceDatad->leftJoin(DB::raw('schedule_week sch'), function ($joinss) {
                $joinss->on(DB::raw('(CASE WHEN additional_user_schedule.schedule_id IS NOT NULL THEN additional_user_schedule.schedule_id ELSE duty_roaster_dates.schedule_id END)'), '=', 'sch.schedule_id')
                    ->whereRaw('sch.week_day = date_format(dte,\'%a\')');
            });

            $attendanceDatad->leftJoin(DB::raw('clockin clk'), function ($data) {
                $data->on('up.employee_no', '=', 'clk.employee_no')
                    ->whereRaw('(
                                    ( (clk.working_day = dte) 
                                        and 
                                            (
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) > UNIX_TIMESTAMP(CONCAT(dte, " ", sch.entry) - interval 360 minute) 
                                                and
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) < UNIX_TIMESTAMP(CONCAT((dte + interval 1 day), " ", sch.user_leave) + interval 360 minute)
                                            )
                                    ) or 
                                        (
                                            clk.working_day = CASE WHEN (sch.entry > sch.user_leave) THEN (dte + interval 1 day) ELSE dte END
                                            and 
                                            (
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) > UNIX_TIMESTAMP(CONCAT(dte, " ", sch.entry) - interval 360 minute) 
                                                and
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) < UNIX_TIMESTAMP(CONCAT((dte + interval 1 day), " ", sch.user_leave) + interval 360 minute)
                                            )
                                        ) 
                                    or
                                        CASE WHEN (sch.entry IS NULL and sch.break IS NULL and sch.user_resume IS NULL and sch.user_leave IS NULL) THEN 
                                            (
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) > UNIX_TIMESTAMP(CONCAT(dte, " ", "21:00") - interval 360 minute) 
                                                and
                                                UNIX_TIMESTAMP(CONCAT(clk.date, " ", clk.time)) < UNIX_TIMESTAMP(CONCAT((dte + interval 1 day), " ", "09:00") + interval 360 minute)
                                            )
                                        END
                                )');
                                                
            });

            //round system
            $attendanceDatad->leftjoin('schedule_round_time', 'schedule_round_time.schedule_id', '=', 'duty_roaster_dates.schedule_id');
            // for additional schedule

            $attendanceDatad->leftJoin(DB::raw('public_holiday'), function ($joins) {
                $joins->on('public_holiday.id', '=', 'duty_roaster_dates.holiday_id');
            });
            
            
            $attendanceDatad->leftJoin(DB::raw('leave_list'), function ($joins) use($sdatefrom, $sdateto) {
                $joins->on('up.username', '=', 'leave_list.username')
                    ->where('leave_list.start_date', '>=', $sdatefrom)->where('leave_list.end_date', '<=', $sdateto)->where('leave_list.status', 1)->where('leave_list.username', 'up.username');
            });
            
            $attendanceDatad->leftjoin('user_pay', 'user_pay.username', '=', 'up.username');

            
            $attendanceDatad->where(function($q){
                $q->where('user_pay.date_resigned', NULL)->orWhere('user_pay.date_resigned', '0000-00-00');
            })->where('user_pay.pay_status', 1)->where('users.isDeleted', 0);
            
    
            $attendanceDatad->whereRaw('dte >= date_format(user_pay.date_attendance_start, "%Y-%m-%d")');
            
            if($user_list){
                $attendanceDatad->whereIn('up.employee_no', $user_list);
            }

        // filter
        if ($sdatefrom) {
            $attendanceDatad->where('dates.dte', '>=', $sdatefrom);
        }
        
        if ($sdateto) {
            $attendanceDatad->where('dates.dte', '<=', $sdateto);
        }
        if ($schedule) {
            $attendanceDatad->where('up.schedule_id', '=', $schedule);
        }
        if ($day_type && $day_type != '') {
            $attendanceDatad->where('sch.day_type', '=', $day_type);
        }
        // for additional schedule
        if ($day_type && $day_type != '') {
            $attendanceDatad->orWhere('additional_user_schedule.aday_type', '=', $day_type);
        }
        // for additional schedule

        if ($category) {
            $attendanceDatad->where('user_pay.category_id', '=', $category);
        }
        if ($branch) {
            $attendanceDatad->where('user_pay.branch_id', '=', $branch);
        }
        if ($department) {
            $attendanceDatad->where('user_pay.department_id', '=', $department);
        }
        if ($ActiveUserShownStatus == '0') {
            $attendanceDatad->where('user_pay.pay_status', '=', 1);
        }
        // // filter


        $attendanceData = $attendanceDatad->groupBy(['up.employee_no', 'dates.dte'])->orderBy('dates.dte', 'ASC')->orderBy('up.employee_no', 'ASC')->get();
        // dd($attendanceData);


        if (in_array($in_out_filter, $filter_arr)) {
            $filtered = $attendanceData->reject(function ($value, $key) {
                $key++;
                return $value->filter_val != $key;
            });
        } else {
            $filtered = $attendanceData;
        }


        $filtered->map(function ($item, $index) {
        
            $index++;
            $item->index = $index;
            $item->date = date('d-m-Y', strtotime($item->dte));
            $item->working_day = $item->dte;
            $item->employee_no = $item->employee_no;
            $item->no_days = $item->no_days;
            $item->personname  =  $item->name;
            
            if(isset($item->aday_type) && $item->aday_type != null){
                $item->day_type = $item->aday_type;
            }

            return $item;
        });
        // dd($filtered);

        $finalDatas = json_decode(json_encode($filtered), true);

        $check = [];
        $finalReport = [];

        foreach($finalDatas as $finalData){
            if(!isset($check[$finalData['employee_no']])){
                
                // overtime present
                $overtimes_emp = 0;
                $late_emp = 0;
                $early_dep = 0;
                $mp = 0;
                $leave = 0;
                $perfect = 0;
                $leave += $finalData['no_days'];

                $workdayHours = 0;
                $restdayHours = 0;
                $eoHours = 0;
                $liHours = 0;
                
                $workdayOvertimeHours = 0;
                $restdayOvertimeHours = 0;
                $holydayHours = 0;
                $holydayOvertimeHours = 0;
                $out_time = 0;
                $hp = 0;

                    $resAt = [];
                    foreach($finalDatas as $atData){
 
                        if($atData['employee_no'] == $finalData['employee_no']){
                            // dd($finalData);
                            $user_schedule = $atData['schedule_id'];
                            $entry_time = $atData['entry'];
                            $break_time = $atData['break'];
                            $resume_time = $atData['user_resume'];
                            $leave_time = $atData['user_leave'];
                            $schedule_range = $atData['schedule_range'];
                            $flexi_lunch = $atData['flexi_lunch'];
                            $min_ot = $atData['min_ot'];
                            $min_flexi_hour = $atData['min_flexi_hour'];
        
                            $entry_range_before = date('H:i', strtotime('-' . $schedule_range . ' minutes', strtotime($entry_time)));
                            $entry_range = date('H:i', strtotime('+' . $schedule_range . ' minutes', strtotime($entry_time)));
                            $break_range_min = date('H:i', strtotime('-' . $schedule_range . ' minutes', strtotime($break_time)));
                            $resume_range_min = date('H:i', strtotime('-' . $schedule_range . ' minutes', strtotime($resume_time)));
                            $resume_range_max = date('H:i', strtotime('+' . $schedule_range . ' minutes', strtotime($resume_time)));
                            $min_ot_range =  date('H:i', strtotime('+' . $min_ot . ' minutes', strtotime($leave_time)));
                            $break_fexi_lunch = date('H:i', strtotime('+' . $flexi_lunch . ' minutes', strtotime($break_time)));
                            
                            // ======= for night schedule =======
                            // ======= for night schedule ======= 
                            $wDay = $atData['dte'];
                            if(strtotime($entry_time) > strtotime($break_time)){
                                $newDateBreak = date('Y-m-d', strtotime('+1 day', strtotime($wDay)));
                            }else{
                                $newDateBreak = $wDay;
                            }

                            if(strtotime($entry_time) > strtotime($resume_time)){
                                $newDateResume = date('Y-m-d', strtotime('+1 day', strtotime($wDay)));
                            }else{
                                $newDateResume = $wDay;
                            }

                            if(strtotime($entry_time) > strtotime($leave_time)){
                                $newDateLeave = date('Y-m-d', strtotime('+1 day', strtotime($wDay)));
                            }else{
                                $newDateLeave = $wDay;
                            }
                            $entry_range = $wDay . " ". $entry_range;
                            $break_range_min = $newDateBreak . " ". $break_range_min;
                            $resume_range_min = $newDateResume . " ". $resume_range_min;
                            $resume_range_max = $newDateResume . " ". $resume_range_max;
                            $min_ot_range = $newDateLeave . " ". $min_ot_range;
                            $leave_time = $newDateLeave . " ". $leave_time;
                            $break_range_max = date('H:i', strtotime('+' . $schedule_range . ' minutes', strtotime($break_time)));

                            // ======= for night schedule =======
                            // ======= for night schedule =======
                            
                            $intime = '';
                            $break = '';
                            $resume = '';
                            $out_time = '';
                            $otOut = '00:00';
                            $otIn = '00:00';
                                                
                            $temp = date("j", strtotime($atData['dte']));
                            $resAt[$temp] = [];
                            $clkTime = explode('==', $atData['fulltime']);
                            // dd($clkTime);
                         
                            sort($clkTime);

                            
                            $earlyOutData = 0;
                            $lateData = 0;
                            $overtimeData = '';
                            $workData = 0;
                            $out_timeEdit = '';
                            $out_timeDate = '';
                            $overtimeData1 = "";
                            $shortData1 = "";
                                
                                for ($a = 0; $a < count($clkTime); $a++) {
                                    $emp_time = $clkTime[$a];
                                    if(strtotime($emp_time) > 0){
                                        $emp_time = date("Y-m-d H:i", strtotime($emp_time));
                                    }
                                        
                                    // entry time
                                    if ($atData['day_type'] == 'Work') {
                                        if(isset($entry_time) && $entry_time != null && isset($break_time) && $break_time != null && isset($resume_time) && $resume_time != null && isset($leave_time) && $leave_time != null){
                                        
                                            if ($a == '0' && strtotime($emp_time) < strtotime($break_range_min)) {
                                                $intimeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                $intimeWorkingDayTime =  date("Y-m-d H:i", strtotime($emp_time));
                                                $intime = date("H:i", strtotime($emp_time));
                                                if($atData['in_round_min'] && isset($intime) && $intime != "" && $intime != null && $intime != "00:00"){
                                                    if($atData['in_round_type'] =="up"){
                                                        $intime = roundUpNearestMinuteInterval($intime, $atData['in_round_min']);
                                                    }elseif($atData['in_round_type'] =="down"){
                                                        $intime = roundDownNearestMinuteInterval($intime, $atData['in_round_min']);
                                                    }
                                                }
                                            }
                        
                        
                                            // break_times
                                            if ((!$break) &&  (strtotime($emp_time) >= strtotime($break_range_min)) && (strtotime($emp_time) < strtotime($resume_range_min))) {
                                                $breakWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                $break = date("H:i", strtotime($emp_time));
                                                if($atData['break_round_min'] && isset($break) && $break != "" && $break != null && $break != "00:00"){
                                                    if($atData['break_round_type'] =="up"){
                                                        $break = roundUpNearestMinuteInterval($break, $atData['break_round_min']);
                                                    }elseif($atData['break_round_type'] =="down"){
                                                        $break = roundDownNearestMinuteInterval($break, $atData['break_round_min']);
                                                    }
                                                }
                                            }
                        
                        
                                            //   resume_time
                                            if (($break) && (strtotime(date("H:i", strtotime($emp_time))) >  strtotime($break)) && (strtotime($resume_range_max) >= strtotime($emp_time))) {
                                                    $resumeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                    $resume = date("H:i", strtotime($emp_time));
                                                if($atData['resume_round_min'] && isset($resume) && $resume != "" && $resume != null && $resume != "00:00"){
                                                    if($atData['resume_round_type'] =="up"){
                                                        $resume = roundUpNearestMinuteInterval($resume, $atData['resume_round_min']);
                                                    }elseif($atData['resume_round_type'] =="down"){
                                                        $resume = roundDownNearestMinuteInterval($resume, $atData['resume_round_min']);
                                                    }
                                                }
                                            } else {
                                                if ((strtotime($emp_time) >= strtotime($resume_range_min)) && (strtotime($emp_time) <= strtotime($resume_range_max))) {
                                                    $resumeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                    $resume = date("H:i", strtotime($emp_time));
                                                    if($atData['resume_round_min'] && isset($resume) && $resume != "" && $resume != null && $resume != "00:00"){
                                                        if($atData['resume_round_type'] =="up"){
                                                            $resume = roundUpNearestMinuteInterval($resume, $atData['resume_round_min']);
                                                        }elseif($atData['resume_round_type'] =="down"){
                                                            $resume = roundDownNearestMinuteInterval($resume, $atData['resume_round_min']);
                                                        }
                                                    }
                                                }
                                            }
                        
                        
                                            //   out
                                            if ($a == (count($clkTime) - 1) &&  strtotime($emp_time) > strtotime($resume_range_max)) {
                                                $outWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                $out_time = date("H:i", strtotime($emp_time));
                                                $out_timeDate = date("Y-m-d H:i", strtotime($emp_time));
                                                if($atData['out_round_min'] && isset($out_time) && $out_time != "" && $out_time != null && $out_time != "00:00"){
                                                    if($atData['out_round_type'] =="up"){
                                                        $out_time = roundUpNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                    }elseif($atData['out_round_type'] =="down"){
                                                        $out_time = roundDownNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                    }
                                                }
                                            }
                                        }
                                    }elseif($atData['day_type']=='Flexi'){

                                            //flexi calculation
                                            $gotFlexiIn = false;
                                            $gotFlexiOut = false;
                                            if ($a == '0') {
                                                $gotFlexiIn = true;
                                                $intimeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                $intimeWorkingDayTime =  date("Y-m-d H:i", strtotime($emp_time));
                                                $intime = date("H:i", strtotime($emp_time));
                                                if($atData['in_round_min'] && isset($intime) && $intime != "" && $intime != null && $intime != "00:00"){
                                                    if($atData['in_round_type'] =="up"){
                                                        $intime = roundUpNearestMinuteInterval($intime, $atData['in_round_min']);
                                                    }elseif($atData['in_round_type'] =="down"){
                                                        $intime = roundDownNearestMinuteInterval($intime, $atData['in_round_min']);
                                                    }
                                                }
                                            }
                                            
                                            if (end($clkTime)) {
                                                $gotFlexiOut = true;
                                                $emp_time = end($clkTime);
                                                $outWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                $out_timeDate = date("Y-m-d H:i", strtotime($emp_time));
                                                $out_time = date("H:i", strtotime($emp_time));
                                                if($atData['out_round_min'] && isset($out_time) && $out_time != "" && $out_time != null && $out_time != "00:00"){
                                                    if($atData['out_round_type'] =="up"){
                                                        $out_time = roundUpNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                    }elseif($atData['out_round_type'] =="down"){
                                                        $out_time = roundDownNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                    }
                                                }
                                            }

                                           if ($gotFlexiIn && $gotFlexiOut && isset($atData['min_flexi_hour']) && is_numeric($atData['min_flexi_hour'])) {
                                            $hours = strtotime($out_time) - strtotime($intime);
                                            $diff = ($hours) - ($atData['min_flexi_hour'] * 3600);
                                            
                                            if ($diff > 0) {
                                                $overtimeData1 = second_to_hhmm($diff);
                                            } else {
                                                $shortData1 = second_to_hhmm(abs($diff));
                                            }
                                        }
                                    
                                        $gotFlexiIn = false;
                                        $gotFlexiOut = false;
                                        // $atData['day_type'] = "Work";
                                        
                                    }else{
                                        if(($atData['holiday_id'] !== null) &&( $atData !=="")){
                                            if(isset($clkTime)){

                                                if(count($clkTime) > 1){
                                                    $emp_time = $clkTime[0];
                                                    $intimeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                    $intimeWorkingDayTime =  date("Y-m-d H:i", strtotime($emp_time));
                                                    $intime = date("H:i", strtotime($emp_time));
                                                    if($atData['in_round_min'] && isset($intime) && $intime != "" && $intime != null && $intime != "00:00"){
                                                        if($atData['in_round_type'] =="up"){
                                                            $intime = roundUpNearestMinuteInterval($intime, $atData['in_round_min']);
                                                        }elseif($atData['in_round_type'] =="down"){
                                                            $intime = roundDownNearestMinuteInterval($intime, $atData['in_round_min']);
                                                        }
                                                    }
                                                    
                                                    $emp_time = end($clkTime);
                                                    $out_timeDate = date("Y-m-d H:i", strtotime($emp_time));
                                                    $outWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                    $out_time = date("H:i", strtotime($emp_time));
                                                    if($atData['out_round_min'] && isset($out_time) && $out_time != "" && $out_time != null && $out_time != "00:00"){
                                                        if($atData['out_round_type'] =="up"){
                                                            $out_time = roundUpNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                        }elseif($atData['out_round_type'] =="down"){
                                                            $out_time = roundDownNearestMinuteInterval($out_time, $atData['out_round_min']);
                                                        }
                                                    }
                                                }else{
                                                    $emp_time = $clkTime[0];
                                                    $intimeWorkingDay =  date("Y-m-d", strtotime($emp_time));
                                                    $intimeWorkingDayTime =  date("Y-m-d H:i", strtotime($emp_time));
                                                    $intime = date("H:i", strtotime($emp_time));
                                                    if($atData['in_round_min'] && isset($intime) && $intime != "" && $intime != null && $intime != "00:00"){
                                                        if($atData['in_round_type'] =="up"){
                                                            $intime = roundUpNearestMinuteInterval($intime, $atData['in_round_min']);
                                                        }elseif($atData['in_round_type'] =="down"){
                                                            $intime = roundDownNearestMinuteInterval($intime, $atData['in_round_min']);
                                                        }
                                                    }
                                                }

                                                $emp_time = $clkTime[0];
                                                $otIn = date("H:i", strtotime($emp_time));
                                                if($atData['in_round_min'] && isset($otIn) && $otIn != "" && $otIn != null && $otIn != "00:00"){
                                                    if($atData['in_round_type'] =="up"){
                                                        $otIn = roundUpNearestMinuteInterval($otIn, $atData['in_round_min']);
                                                    }elseif($atData['in_round_type'] =="down"){
                                                        $otIn = roundDownNearestMinuteInterval($otIn, $atData['in_round_min']);
                                                    }
                                                }
                                                $otOut = date("H:i", strtotime(end($clkTime)));
                                                if($atData['out_round_min'] && isset($otOut) && $otOut != "" && $otOut != null && $otOut != "00:00"){
                                                    if($atData['out_round_type'] =="up"){
                                                        $otOut = roundUpNearestMinuteInterval($otOut, $atData['out_round_min']);
                                                    }elseif($atData['out_round_type'] =="down"){
                                                        $otOut = roundDownNearestMinuteInterval($otOut, $atData['out_round_min']);
                                                    }
                                                }
                                           
                                                $restdayHours = date('H:i', strtotime($restdayHours) + (strtotime($otOut) - strtotime($otIn)));
                                                
                                                // ======================= round system ===================
                                                $glbSettingType = Setting::where('field', 'restday_round_type')->first();
                                                $glbSettingMin = Setting::where('field', 'restday_round_min')->first();
                                           
                                                if($glbSettingType && $glbSettingMin && isset($restdayHours) && $restdayHours != "" && $restdayHours != null && $restdayHours != "00:00"){
                                                    if($glbSettingType->value =="up"){
                                                        $restdayHours = roundUpNearestMinuteInterval($restdayHours, $glbSettingMin->value);
                                                    }elseif($glbSettingType->value =="down"){
                                                        $restdayHours = roundDownNearestMinuteInterval($restdayHours, $glbSettingMin->value);
                                                    }
                                                }
                                                // ======================= round system ===================
                                 
                                                if(strtotime(date('H:i', (strtotime($otOut) - strtotime($otIn)))) > strtotime(date("H:i", strtotime("08:00")))){
                                                    $restdayOvertimeHours =  date('H:i', strtotime($restdayOvertimeHours) + (strtotime($otOut) - strtotime($otIn)) - strtotime("08:00"));
                                                    $atData['day_type'] = "Flexi";
                                                }
                                            }
                                            
                                            $atData['day_type'] = "Rest";
                                        }
                                    }
                                }
                      
                                if(isset($atData['holiday_id']) && $atData['holiday_id'] != null){
                                    $atData['day_type'] = "Holiday";
                                }

                                
                                if(strtotime($intime) && strtotime($leave_time) && $leave_time != null && $leave_time != '00:00' && $intime != '00:00' && count($clkTime) > 1){
                                    if(strtotime($intime) < strtotime($entry_time)){
                                        $new_intime = $entry_time;
                                    }else{
                                        $new_intime = $intimeWorkingDayTime;
                                    }
                                    if((strtotime($out_timeDate) - strtotime($new_intime)) > (strtotime($leave_time) - strtotime($entry_time))){
                                        $workData = date("H:i", (strtotime($leave_time) - strtotime($new_intime)));
                                    }else{
                                        if(isset($out_timeDate) && $out_timeDate != "00:00" && $out_timeDate != null || isset($resume) && $resume != "00:00" && $resume != null || isset($break) && $break != "00:00" && $break != null){
                                            $workData = date('H:i', (strtotime($out_timeDate) - strtotime($new_intime)));
                                            if(strtotime($workData) > strtotime("08:00")){
                                                $workData = "08:00";
                                            }
                                        }
                                    }
                                }else{
                                    $workData = date('H:i', (strtotime($out_timeDate) - strtotime($intimeWorkingDayTime))); 
                                }
                                
                                                   

                                // ======================= round system ===================
                                if($atData['day_type'] == "Work"){
                                    $glbSettingType = Setting::where('field', 'workday_round_type')->first();
                                    $glbSettingMin = Setting::where('field', 'workday_round_min')->first();
                                }elseif($atData['day_type'] == "Holiday"){
                                    $glbSettingType = Setting::where('field', 'holiday_round_type')->first();
                                    $glbSettingMin = Setting::where('field', 'holiday_round_min')->first();
                                }else{
                                    $glbSettingType = Setting::where('field', 'restday_round_type')->first();
                                    $glbSettingMin = Setting::where('field', 'restday_round_min')->first();
                                }
 
                                if($glbSettingType && $glbSettingMin && isset($workData) && $workData != "" && $workData != null && $workData != "00:00"){
                                    if($glbSettingType->value =="up"){
                                        $workData = roundUpNearestMinuteInterval($workData, $glbSettingMin->value);
                                    }elseif($glbSettingType->value =="down"){
                                        $workData = roundDownNearestMinuteInterval($workData, $glbSettingMin->value);
                                    }
                                }
                                // ======================= round system ===================
                                if(count($clkTime) > 1 && isset($intimeWorkingDayTime) && $entry_range != "00:00" && strtotime($entry_range) > strtotime($intimeWorkingDayTime)){
                                    $lateData = "00:00";
                                }elseif(count($clkTime) > 1 && isset($intimeWorkingDayTime) && $entry_range != "00:00" && strtotime($entry_range) < strtotime($intimeWorkingDayTime)){
                                    $lateData = date("H:i", strtotime($intimeWorkingDayTime) - strtotime($entry_range));
                                }

                                // early out
                                if(isset($out_time) && $out_time != "00:00" && $out_time != ""  && $leave_time != "00:00" && strtotime($leave_time) > strtotime($out_timeDate)){
                                    $earlyOutData = date("H:i", strtotime($leave_time) - strtotime($out_timeDate));
                                }


                                $checkOTApproval = DB::table('ot_requests')->where(['username'=> $atData['username'], 'working_day'=> $atData['dte']])->where('status', '!=', '3')->first();
                                $otReqPermit = DB::table('settings')->where('field', 'ot_request')->first();
                                
                                if(isset($otReqPermit) && $otReqPermit->value == 1){
                                    if($checkOTApproval && $checkOTApproval->status != 3){
                                        if(strtotime($out_timeDate)>strtotime($min_ot_range) && $atData['day_type'] == "Work"){
                                            $overtimeData = date('H:i', strtotime($out_timeDate) - strtotime($leave_time));
                                        }else{
                                            if(strtotime(date('H:i', (strtotime($otOut) - strtotime($otIn)))) > strtotime(date("H:i", strtotime("08:00")))){
                                                $overtimeData =  date('H:i', strtotime($overtimeData) + (strtotime($otOut) - strtotime($otIn)) - strtotime("08:00"));
                                                $atData['day_type'] = "Flexi";
                                            }
                                        }
                                    }
                                }else{
                                    if(strtotime($out_timeDate)>strtotime($min_ot_range) && $atData['day_type'] == "Work"){
                                        $overtimeData = date('H:i', strtotime($out_timeDate) - strtotime($leave_time));
                                    }else{
                                        $otIn = $clkTime[0];
                                        if($atData['in_round_min'] && isset($otIn) && $otIn != "" && $otIn != null && $otIn != "00:00"){
                                            if($atData['in_round_type'] =="up"){
                                                $otIn = roundUpNearestMinuteInterval($otIn, $atData['in_round_min']);
                                            }elseif($atData['in_round_type'] =="down"){
                                                $otIn = roundDownNearestMinuteInterval($otIn, $atData['in_round_min']);
                                            }
                                        }
                                        $otOut = end($clkTime);
                                        if($atData['out_round_min'] && isset($otOut) && $otOut != "" && $otOut != null && $otOut != "00:00"){
                                            if($atData['out_round_type'] =="up"){
                                                $otOut = roundUpNearestMinuteInterval($otOut, $atData['out_round_min']);
                                            }elseif($atData['out_round_type'] =="down"){
                                                $otOut = roundDownNearestMinuteInterval($otOut, $atData['out_round_min']);
                                            }
                                        }
                                                
                                        if(strtotime(date('H:i', (strtotime($otOut) - strtotime($otIn)))) > strtotime(date("H:i", strtotime("08:00"))) && $atData['day_type'] != "Work"){
                                            $overtimeData =  date('H:i', strtotime($overtimeData) + (strtotime($otOut) - strtotime($otIn)) - strtotime("08:00"));
                                            $atData['day_type'] = "Flexi";
                                        }
                                    }
                                }
                                
                             
                                if(isset($overtimeData) && $overtimeData != "" && $overtimeData != null && $overtimeData != "00:00"){
                                    if($atData['ot_round_type'] =="up"){
                                        $overtimeData = roundUpNearestMinuteInterval($overtimeData, $atData['ot_round_min']);
                                    }elseif($atData['ot_round_type'] =="down"){
                                        $overtimeData = roundDownNearestMinuteInterval($overtimeData, $atData['ot_round_min']);
                                    }
                                }else{
                               
                                    // ======================= round system ===================
                                    $glbSettingType = Setting::where('field', 'ot_round_type')->first();
                                    $glbSettingMin = Setting::where('field', 'ot_round_min')->first();
                                    if($glbSettingType && $glbSettingMin && isset($overtimeData) && $overtimeData != "" && $overtimeData != null && $overtimeData != "00:00"){
                                        if($glbSettingType->value =="up"){
                                            $overtimeData = roundUpNearestMinuteInterval($overtimeData, $glbSettingMin->value);
                                        }elseif($glbSettingType->value =="down"){
                                            $overtimeData = roundDownNearestMinuteInterval($overtimeData, $glbSettingMin->value);
                                        }
                                    }
                                    // ======================= round system ===================
                                }


                                // flexi lunch code without break and resume lateness hrs
                                $emp_lunch = (strtotime($resume)-strtotime($break));
                                if (($emp_lunch > ($flexi_lunch * 60) && $emp_lunch > 0)) {
                                    $lunch_text_color = "red";
                                }else{
                                    $lunch_text_color = "";
                                }
                                if($flexi_lunch > 0){
                                    if((($flexi_lunch * 60) < ($emp_lunch)) && $resume != "" && $break != "" ){
                                        $lateData = second_to_hhmm(explode_time($lateData) + ($emp_lunch) - ($flexi_lunch * 60));
                                    }
                                }else{
                                    // for break
                                    if(($break_range_max != "00:00" && $break != "00:00" ) && strtotime($break) > strtotime($break_range_max) && strtotime($break) < strtotime(date("H:i", strtotime($resume_range_min)))){
                                        $lateData = second_to_hhmm(explode_time($lateData) + (strtotime($break) - strtotime($break_range_max)));
                                    }
                                }
                                
                               
                                if($atData['day_type'] == "Work"){
                                    $shortData = date('H:i', (strtotime($lateData) + strtotime($earlyOutData)));
                                }else{
                                    $shortData = null;
                                }
                                
                                // late color
                                if (strtotime($intime) > strtotime(date("H:i", strtotime($entry_time))) && $atData['day_type'] == "Work") {
                                    $resAt[$temp]['in_color'] = "red";
                                }else{
                                    $resAt[$temp]['in_color'] = null;
                                }
                                
                                if (strtotime($out_timeDate) < strtotime($leave_time) && $atData['day_type'] == "Work") {
                                    $resAt[$temp]['out_color'] = "red";
                                }else{
                                    $resAt[$temp]['out_color'] = null;
                                }
                                
                                if ($lunch_text_color) {
                                    $resAt[$temp]['lunch_text_color'] = $lunch_text_color;
                                }else{
                                    $resAt[$temp]['lunch_text_color'] = null;
                                }
                                
                                // late color
                                
                                if($intime == "00:00"){
                                    $resAt[$temp]['in_time'] = NULL;
                                }else{
                                    $resAt[$temp]['in_time'] = $intime;
                                }
                                
                                $resAt[$temp]['break_time'] = $break;
                                $resAt[$temp]['resume_time'] = $resume;
                                
                                if($out_time == "00:00"){
                                    $resAt[$temp]['out_time'] = NULL;
                                }else{
                                    $resAt[$temp]['out_time'] = $out_time;
                                }
                           

                                $resAt[$temp]['date'] = $atData['dte'];
                                $resAt[$temp]['day_type'] = $atData['day_type'];
                                
                                if(isset($atData['schedule_id']) && ($atData['schedule_id']) != null){
                                    $schedule_name = getScheduleName($atData['schedule_id']);
                                }else{
                                    $schedule_name = $atData['description'];
                                }

                                //Override
                        
                                if(isset($overtimeData1)&&$overtimeData1!=""){
                                    $overtimeData = $overtimeData1;
                                    $overtimeData1="";
                                }    
                                 if(isset($shortData1)&&$shortData1!=""){
                                    $shortData = $shortData1;
                                    $shortData1="";
                                }           

                                $resAt[$temp]['sche'] = $schedule_name;
                                
                                $resAt[$temp]['short'] = $shortData;
                                if($shortData == "00:00"){
                                    $resAt[$temp]['short'] = "";
                                }
                                

                                if($intime == "00:00"){
                                    $resAt[$temp]['work'] = NULL;
                                }else{
                                    if($workData != "00:00"){
                                        $resAt[$temp]['work'] = $workData;
                                    }else{
                                        $resAt[$temp]['work'] = null;
                                    }
                                }
                                
                                $resAt[$temp]['overtime'] = $overtimeData;

                            $leaveList = DB::table('leave_list')->where('start_date', '>=', $atData['dte'])->where('end_date', '<=', $atData['dte'])->where('status', 1)->where('username', $atData['username'])->first();
                            if($leaveList){
                                $resAt[$temp]['leave_taken'] = $leaveList->type;
                                $resAt[$temp]['remark'] = $leaveList->reason;
                            }

                        }
                    }
        

                $finalData['start_date'] = $sdatefrom;
                $finalData['end_date'] = $sdateto;
    
                $finalData['att'] = $resAt;
                $finalReport[] =$finalData;
                $check[$finalData['employee_no']]='1';
            }
        }

        //  dd($finalReport);
        $routeName = \Request::route()->getName();
        // dd($routeName);
        if($routeName == "reports.time-card-data"){
            $response = array(
                "draw" => intval($_GET['draw']),
                "iTotalRecords" => $attendanceDataCount,
                "iTotalDisplayRecords" => $attendanceDataCount,
                "data" => $finalReport
            );
            return json_encode($response);
        }else{
            return DataTables::of($finalReport)->make(true);
        }
    }