<?php

namespace Modules\Hr\Http\Controllers;

use DateTime;
use Exception;
use Illuminate\Contracts\Support\Renderable;
use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use Illuminate\Support\Facades\DB;
use Modules\Hr\Entities\AttLeaveType;
use Modules\Hr\Entities\AttShift;
use Modules\Hr\Entities\Company;
use Modules\Hr\Entities\Employee;
use Modules\Hr\Entities\EmployeeCategory;
use Modules\Hr\Entities\EmployeeDepartment;
use Modules\Hr\Entities\EmployeeSection;
use Modules\Hr\Entities\EmployeeSite;
use Modules\Hr\Entities\EmployeeSubsection;
use Modules\Hr\Entities\Team;

class EmployeeAttendanceReportController extends Controller
{
    /**
     * Display a listing of the resource.
     * @return Renderable
     */
    public function index()
    {
        return view('hr::index');
    }

    /**
     * Show the form for creating a new resource.
     * @return Renderable
     */
    public function create()
    {
        return view('hr::create');
    }

    /**
     * Store a newly created resource in storage.
     * @param Request $request
     * @return Renderable
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Show the specified resource.
     * @param int $id
     * @return Renderable
     */
    public function show($id)
    {
        return view('hr::show');
    }

    /**
     * Show the form for editing the specified resource.
     * @param int $id
     * @return Renderable
     */
    public function edit($id)
    {
        return view('hr::edit');
    }

    /**
     * Update the specified resource in storage.
     * @param Request $request
     * @param int $id
     * @return Renderable
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     * @param int $id
     * @return Renderable
     */
    public function destroy($id)
    {
        //
    }


    public function allSettings()
    {

        try {
            $settings = [
                "shift" => $this->shift(),
                "category" => $this->category(),
                "site" => $this->site(),
                "section" => $this->section(),
                "department" => $this->department(),
                "company" => $this->company(),
                "team" => $this->team(),
                "employee" => $this->allEmployee(),
            ];
            $responseBody = $this->responseBody(true, "AllSettings", "all", ["settings" => $settings]);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "AllSettings", "error", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function shift()
    {

        try {
            $shift = AttShift::all();
            $collection = [];
            for ($i = 0; $i < count($shift); $i++) {
                array_push($collection, ["id" => $shift[$i]['shift_id'], "value" => $shift[$i]['shift_name']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function category()
    {

        try {
            $category = EmployeeCategory::all();
            $collection = [];
            for ($i = 0; $i < count($category); $i++) {
                array_push($collection, ["id" => $category[$i]['category_id'], "value" => $category[$i]['category']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function site()
    {

        try {
            $site = EmployeeSite::all();
            $collection = [];
            for ($i = 0; $i < count($site); $i++) {
                array_push($collection, ["id" => $site[$i]['site_id'], "value" => $site[$i]['site']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function department()
    {

        try {
            $department = EmployeeDepartment::all();
            $collection = [];
            for ($i = 0; $i < count($department); $i++) {
                array_push($collection, ["id" => $department[$i]['department_id'], "value" => $department[$i]['department']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function section()
    {

        try {
            $section = EmployeeSection::all();
            $collection = [];
            for ($i = 0; $i < count($section); $i++) {
                array_push($collection, ["id" => $section[$i]['section_id'], "value" => $section[$i]['section']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    public function subSection($section_id)
    {
        try {
            $subsection = EmployeeSubsection::where('section_id', '=', $section_id)->get();
            $collection = [];
            for ($i = 0; $i < count($subsection); $i++) {
                array_push($collection, ["id" => $subsection[$i]['subsection_id'], "value" => $subsection[$i]['subsection']]);
            }
            $responseBody = $this->responseBody(true, "Subsection", "all", $collection);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "Subsection", "error", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    private function company()
    {

        try {
            $company = Company::all();
            $collection = [];
            for ($i = 0; $i < count($company); $i++) {
                array_push($collection, ["id" => $company[$i]['company_id'], "value" => $company[$i]['company']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function team()
    {

        try {
            $team = Team::all();
            $collection = [];
            for ($i = 0; $i < count($team); $i++) {
                array_push($collection, ["id" => $team[$i]['team_id'], "value" => $team[$i]['team_name']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    private function allEmployee()
    {
        try {
            $employees = Employee::all();

            $collection = [];
            for ($i = 0; $i < count($employees); $i++) {
                array_push($collection, ["img" => $employees[$i]['photo_parth'], "id" => $employees[$i]['employee_id'], "value" => $employees[$i]['employee_id']]);
            }
            return $collection;
        } catch (\Exception $exception) {
            return $exception;
        }
    }


    public function dailyAbsentReport($setting, $date)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;


            $query = "SELECT hr_employees.employee_id,
            hr_employees.name_withinitial AS employee_name,
            hr_employees.date_join,
            hr_att_time.Date,
            hr_employee_categories.category,
            hr_employee_departments.department,
            hr_teams.team_name,
            @ab := hr_att_time.NoPay,
            IF(TIMESTAMPDIFF(YEAR, date_join, CURDATE()) = 0,CONCAT(TIMESTAMPDIFF(MONTH, date_join, CURDATE()),' Months'),CONCAT(TIMESTAMPDIFF(YEAR, date_join, CURDATE()),' Years')) AS service,
            (SELECT MAX(hr_att_time.Date) FROM hr_att_time WHERE hr_att_time.NoPay = @ab AND hr_att_time.Date < '" . $date . "') AS last_present_date,
            hr_att_time.NoPay AS absent FROM hr_att_schedules
            INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date
            INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id
            INNER JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id
            INNER JOIN hr_employee_departments ON hr_employees.department_id = hr_employee_departments.department_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            INNER JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= "  hr_att_schedules.schedule_date_from = '" . $date . "' AND hr_att_time.In_Status = '0'  AND hr_employees.employeestatus_id != '4' AND hr_employees.employeestatus_id != '5'  GROUP BY hr_att_schedules.employee_id ORDER BY convert(hr_employees.employee_id, decimal) ASC";


            $employees = DB::select($query);
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function dailyPresentReport($setting, $date)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT hr_employees.employee_id,
            hr_employees.name_withinitial AS employee_name,
            hr_teams.team_name,
            CONCAT(TIME(hr_att_schedules.in_time),'-',TIME(hr_att_schedules.out_time)) AS shift,
            TIME(hr_att_time.InTime) AS in_time,
            TIME(hr_att_time.OutTime) AS out_time,
            DATE(hr_att_time.OutTime) AS out_date,
            hr_att_time.Late_Arriver AS late_arriver,
            hr_att_time.N_overtime AS n_ot,  
            hr_att_time.D_overtime AS d_ot FROM hr_att_schedules
            INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date
            INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE  ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
             $query .= "  hr_att_schedules.schedule_date_from = '" . $date . "' AND hr_att_time.In_Status = '1'  AND hr_employees.employeestatus_id != '4' AND hr_employees.employeestatus_id != '5'  GROUP BY hr_att_schedules.employee_id ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            $employees = DB::select($query);
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function AttendanceRegisterReport($setting, $year, $month)
    {
        try {
            $date = $year . "-" . $month . "-01";
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT hr_employees.employee_id,
            hr_employees.name_withinitial AS employee_name,
            SUM(IF(DAY(hr_att_time.Date) = 1 && hr_att_time.In_Status = 1,1,0)) AS day1,
            SUM(IF(DAY(hr_att_time.Date) = 2 && hr_att_time.In_Status = 1,1,0)) AS day2,
            SUM(IF(DAY(hr_att_time.Date) = 3 && hr_att_time.In_Status = 1,1,0)) AS day3,
            SUM(IF(DAY(hr_att_time.Date) = 4 && hr_att_time.In_Status = 1,1,0)) AS day4,
            SUM(IF(DAY(hr_att_time.Date) = 5 && hr_att_time.In_Status = 1,1,0)) AS day5,
            SUM(IF(DAY(hr_att_time.Date) = 6 && hr_att_time.In_Status = 1,1,0)) AS day6,
            SUM(IF(DAY(hr_att_time.Date) = 7 && hr_att_time.In_Status = 1,1,0)) AS day7,
            SUM(IF(DAY(hr_att_time.Date) = 8 && hr_att_time.In_Status = 1,1,0)) AS day8,
            SUM(IF(DAY(hr_att_time.Date) = 9 && hr_att_time.In_Status = 1,1,0)) AS day9,
            SUM(IF(DAY(hr_att_time.Date) = 10 && hr_att_time.In_Status = 1,1,0)) AS day10,
            SUM(IF(DAY(hr_att_time.Date) = 11 && hr_att_time.In_Status = 1,1,0)) AS day11,
            SUM(IF(DAY(hr_att_time.Date) = 12 && hr_att_time.In_Status = 1,1,0)) AS day12,
            SUM(IF(DAY(hr_att_time.Date) = 13 && hr_att_time.In_Status = 1,1,0)) AS day13,
            SUM(IF(DAY(hr_att_time.Date) = 14 && hr_att_time.In_Status = 1,1,0)) AS day14,
            SUM(IF(DAY(hr_att_time.Date) = 15 && hr_att_time.In_Status = 1,1,0)) AS day15,
            SUM(IF(DAY(hr_att_time.Date) = 16 && hr_att_time.In_Status = 1,1,0)) AS day16,
            SUM(IF(DAY(hr_att_time.Date) = 17 && hr_att_time.In_Status = 1,1,0)) AS day17,
            SUM(IF(DAY(hr_att_time.Date) = 18 && hr_att_time.In_Status = 1,1,0)) AS day18,
            SUM(IF(DAY(hr_att_time.Date) = 19 && hr_att_time.In_Status = 1,1,0)) AS day19,
            SUM(IF(DAY(hr_att_time.Date) = 20 && hr_att_time.In_Status = 1,1,0)) AS day20,
            SUM(IF(DAY(hr_att_time.Date) = 21 && hr_att_time.In_Status = 1,1,0)) AS day21,
            SUM(IF(DAY(hr_att_time.Date) = 22 && hr_att_time.In_Status = 1,1,0)) AS day22,
            SUM(IF(DAY(hr_att_time.Date) = 23 && hr_att_time.In_Status = 1,1,0)) AS day23,
            SUM(IF(DAY(hr_att_time.Date) = 24 && hr_att_time.In_Status = 1,1,0)) AS day24,
            SUM(IF(DAY(hr_att_time.Date) = 25 && hr_att_time.In_Status = 1,1,0)) AS day25,
            SUM(IF(DAY(hr_att_time.Date) = 26 && hr_att_time.In_Status = 1,1,0)) AS day26,
            SUM(IF(DAY(hr_att_time.Date) = 27 && hr_att_time.In_Status = 1,1,0)) AS day27,
            SUM(IF(DAY(hr_att_time.Date) = 28 && hr_att_time.In_Status = 1,1,0)) AS day28,
            SUM(IF(DAY(hr_att_time.Date) = 29 && hr_att_time.In_Status = 1,1,0)) AS day29,
            SUM(IF(DAY(hr_att_time.Date) = 30 && hr_att_time.In_Status = 1,1,0)) AS day30,
            SUM(IF(DAY(hr_att_time.Date) = 31 && hr_att_time.In_Status = 1,1,0)) AS day31,
            SUM(IF(hr_att_time.In_Status = 1,1,0)) AS present_day,
            (hr_employee_categories.work_days_month - SUM(hr_att_time.Worked_Day)) AS absent_day,
            0 AS leave_day,
            (SUM(hr_att_time.Worked_Day)+(hr_employee_categories.work_days_month - SUM(hr_att_time.Worked_Day)) ) AS total_day
            FROM hr_att_time INNER JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id
            INNER JOIN hr_att_schedules ON hr_att_time.EmployeeID = hr_att_schedules.employee_id AND hr_att_time.Date = hr_att_schedules.schedule_date_from
            INNER JOIN hr_employee_categories ON  hr_employees.category_id = hr_employee_categories.category_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE  ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= " hr_att_time.Date LIKE CONCAT(YEAR('" . $date . "'),IF(CHAR_LENGTH(MONTH('" . $date . "')) = 1,CONCAT('-0',MONTH('" . $date . "')),CONCAT('-',MONTH('" . $date . "'))),'%')  AND hr_employees.employeestatus_id != '4' GROUP BY hr_att_time.EmployeeID ORDER BY convert(hr_employees.employee_id, decimal) ASC";


            $employees = DB::select($query);
            $date = DB::select("SELECT YEAR('" . $date . "') AS year, MONTHNAME('" . $date . "') AS month");
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report",  ['body' => $employees, 'title' => $title, "report_date" => $date]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function dailyMarkingSheetReport($setting, $date)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT DISTINCT hr_employees.employee_id,
            hr_employees.name_withinitial AS employee_name,
            CONCAT(TIME(hr_att_schedules.in_time),'-',TIME(hr_att_schedules.out_time)) AS shift,
            TIME(hr_att_time.InTime) AS in_time,
            TIME(hr_att_time.OutTime) AS out_time,
            hr_att_time.Late_Arriver AS late_arriver,
            hr_att_time.N_overtime AS n_ot,  
            hr_att_time.D_overtime AS d_ot FROM hr_att_schedules
            INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date
            INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id 
            INNER JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id WHERE ";

            $query2 = "SELECT DISTINCT 
            COUNT(hr_employees.employee_id) AS total_carder,
            SUM(hr_att_time.In_Status) AS present_carder,
            (COUNT(hr_employees.employee_id) - SUM(hr_att_time.In_Status)) AS absent_carder,
            (((COUNT(hr_employees.employee_id) - SUM(hr_att_time.In_Status)) / COUNT(hr_employees.employee_id)) * 100) AS presentage FROM hr_employees
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            LEFT JOIN hr_att_time ON hr_employees.employee_id = hr_att_time.EmployeeID AND hr_att_time.Date = '" . $date . "' WHERE ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $query2 .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $query2 .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $query2 .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $query2 .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $query2 .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $query2 .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $query2 .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $query2 .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= " hr_att_time.Date = '" . $date . "'  AND hr_employees.employeestatus_id != '4' ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            $query2 .= " employeestatus_id != '4' AND employeestatus_id != '5'";
            $employees = DB::select($query);
            $summary = DB::select($query2);


            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, "report_date" => $date, "summary" => $summary]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


   public function dailyHeadCountReport($setting, $date)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT HR_EMP.designation_id,
            hr_employee_departments.department,
            hr_employee_designations.designation,
            (SELECT COUNT(hr_employees.employee_id) as total_carder FROM hr_employees WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id) AS total_carder,
            (SELECT SUM(hr_att_time.In_Status) FROM hr_att_time LEFT JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id AND hr_att_time.Date ='".$date."' WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id) AS present_carder,
            ((SELECT COUNT(hr_employees.employee_id) as total_carder FROM hr_employees WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id) - (SELECT SUM(hr_att_time.In_Status) FROM hr_att_time LEFT JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id AND hr_att_time.Date ='".$date."' WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id)) AS absent_carder,
            (((SELECT COUNT(hr_employees.employee_id) as total_carder FROM hr_employees WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id) - (SELECT SUM(hr_att_time.In_Status) FROM hr_att_time LEFT JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id AND hr_att_time.Date ='".$date."' WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id)) /(SELECT COUNT(hr_employees.employee_id) as total_carder FROM hr_employees WHERE hr_employees.date_join <='".$date."' AND hr_employees.designation_id = HR_EMP.designation_id AND hr_employees.employeestatus_id !='4' AND hr_employees.employeestatus_id !='5'  GROUP BY hr_employees.designation_id) * 100) AS presentage
            FROM hr_employees AS HR_EMP
            INNER JOIN hr_employee_departments ON HR_EMP.department_id = hr_employee_departments.department_id
            INNER JOIN hr_employee_designations ON HR_EMP.designation_id = hr_employee_designations.designation_id 
            LEFT JOIN hr_employee_teams ON HR_EMP.employee_id = hr_employee_teams.employee_id WHERE ";

            if ($category != '') {
                $query .= "HR_EMP.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "HR_EMP.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "HR_EMP.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "HR_EMP.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "HR_EMP.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "HR_EMP.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "HR_EMP.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= " HR_EMP.employeestatus_id != '4' AND HR_EMP.employeestatus_id != '5' GROUP BY HR_EMP.designation_id  ORDER BY HR_EMP.department_id ASC";
            //$query = Auth::user()->categoryRestrictionQuery($query);
            $employees = DB::select($query);
            $grand_total_carder = 0;
            $grand_present_carder = 0;
            $grand_absent_carder = 0;
            $grand_presentage = 0;
            foreach ($employees as $emp) {
                $grand_total_carder += $emp->total_carder;
                $grand_present_carder += $emp->present_carder;
                $grand_absent_carder += $emp->absent_carder;
                $grand_presentage = ($grand_absent_carder /  $grand_total_carder) * 100;
            }
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", [
                'body' => $employees,
                'title' => $title,
                "report_date" => $date,
                'grand_total_carder' => $grand_total_carder,
                'grand_present_carder' => $grand_present_carder,
                'grand_absent_carder' => $grand_absent_carder,
                'grand_presentage' => $grand_presentage,
            ]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


       public function attendanceSummryReport($setting, $date_form, $date_to)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT 
    hr_employees.employee_id,
    SUM((hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1)) AS worked_day,
    (SUM((hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1)) - (SUM((hr_att_time.In_Status = 1 AND hr_att_time.Out_Status = 1)) - SUM(hr_att_time.Worked_Day))) AS without_holyday,
    hr_employee_categories.category,
    hr_employees.name_withinitial AS employee_name,
    SUM(hr_att_time.N_overtime) AS n_ot,
    SUM(hr_att_time.D_overtime) AS d_ot,
    0 AS poya_ot,
    ROUND(SUM(hr_att_time.Late_Arriver), 2) AS late,
    ROUND(SUM(hr_att_time.Early_Departure), 2) AS early,
    
    -- Calculate total hours worked for 12-hour shifts
    SUM(CASE 
            WHEN hr_att_shifts.duration = 12 
            THEN TIMESTAMPDIFF(HOUR, hr_att_time.InTime, hr_att_time.OutTime) 
            ELSE 0 
        END) AS hours_12,

    -- Calculate total hours worked for 8-hour shifts
    SUM(CASE 
            WHEN hr_att_shifts.duration = 8 
            THEN TIMESTAMPDIFF(HOUR, hr_att_time.InTime, hr_att_time.OutTime) 
            ELSE 0 
        END) AS hours_8
    
FROM hr_att_time
            INNER JOIN hr_employees 
    ON hr_att_time.EmployeeID = hr_employees.employee_id
INNER JOIN hr_employee_categories 
    ON hr_employees.category_id = hr_employee_categories.category_id
LEFT JOIN hr_atd_holidays 
    ON hr_att_time.Date = hr_atd_holidays.holyday_date
LEFT JOIN hr_att_shifts 
    ON hr_att_time.ShiftNo = hr_att_shifts.shift_id WHERE  ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= " hr_att_time.Date BETWEEN '" . $date_form . "' AND '" . $date_to . "'  AND hr_employees.employeestatus_id != '4' GROUP BY 
    hr_att_time.EmployeeID, hr_employees.employee_id, hr_employee_categories.category, hr_employees.name_withinitial

ORDER BY 
    CAST(hr_employees.employee_id AS DECIMAL) ASC";

            //dd($query);
            $employees = DB::select($query);
            foreach ($employees as $employee) {
                if (!$employee->n_ot) {
                    $employee->n_ot = 0;
                }
                if (!$employee->d_ot) {
                    $employee->d_ot = 0;
                }
            }
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", [
                'body' => $employees,
                'title' => $title,
                "report_date" => "(" . $date_form . " - " . $date_to . ")",
            ]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function timeCardsReport($setting, $date_from, $date_to)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT DISTINCT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name
            FROM hr_employees
            INNER JOIN hr_att_time ON hr_employees.employee_id = hr_att_time.EmployeeID
            INNER JOIN hr_att_schedules ON hr_att_time.Date = hr_att_schedules.schedule_date_from AND hr_att_time.EmployeeID = hr_att_schedules.employee_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id  WHERE ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $result = DB::select($query);

            foreach ($result as $employee) {
                $query = "SELECT DISTINCT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name,
                hr_att_time.Date,TIME(hr_att_time.InTime) AS InTime,TIME(hr_att_time.OutTime) AS OutTime,hr_att_shifts.shift_name,
                hr_att_time.NoPay,hr_att_time.Late_Arriver AS late_arriver,hr_att_time.N_overtime AS n_ot,hr_att_time.D_overtime AS d_ot,
                IF(hr_att_time.Early_Departure,hr_att_time.Early_Departure,0) AS early_departure,(SELECT hr_att_leave_types.leave_type FROM hr_att_leave_types WHERE leave_type_id = hr_leaves.leave_type_id) AS leave_type
                FROM hr_att_time
                INNER JOIN hr_att_schedules ON hr_att_time.Date = hr_att_schedules.schedule_date_from AND hr_att_time.EmployeeID = hr_att_schedules.employee_id
                LEFT JOIN hr_employee_teams ON hr_att_time.EmployeeID = hr_employee_teams.employee_id
                LEFT JOIN hr_employees ON hr_att_time.EmployeeID = hr_employees.employee_id
                LEFT JOIN hr_att_shifts ON hr_att_time.ShiftNo = hr_att_shifts.shift_id
                LEFT JOIN hr_leaves ON hr_employees.employee_id = hr_leaves.employee_id AND hr_leaves.from = hr_att_time.Date  WHERE hr_att_time.EmployeeID = '" . $employee->employee_id . "' AND hr_att_time.Date BETWEEN '" . $date_from . "' AND '" . $date_to . "'  AND hr_employees.employeestatus_id != '4' ORDER BY hr_att_schedules.schedule_date_from";
                $employee->result = DB::select($query);
            }
            $responseBody = $this->responseBody(true, "Attendance", "all", ['body' => $result, 'title' => $title]);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "Attendance", "error", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function getEmployeeOt_N($ot)
    {

        $overtime = 0;
        $hour = (float)$ot;
        if ($hour >= 3) {
            $overtime += 3;
        } else if ($hour <= 3) {
            $overtime += $hour;
        }
        return $overtime;
    }


    public function getEmployeeOt_D($ot)
    {

        $overtime = 0;
        $hour = (float)$ot;
        if ($hour >= 6) {
            $overtime += 3;
        } else if ($hour < 6 && $hour > 3) {
            $overtime += ($hour - 3);
        }
        return $overtime;
    }


    public function dailyInvalidReport($setting, $date)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;


            $query = "SELECT 
            hr_employees.employee_id,
            hr_employees.name_withinitial AS employee_name,
            hr_teams.team_name,
            hr_att_shifts.shift_name AS shift,
            hr_att_time.InTime AS in_time,
            hr_att_time.OutTime AS out_time FROM hr_att_schedules
            INNER JOIN hr_att_time ON hr_att_schedules.employee_id = hr_att_time.EmployeeID AND hr_att_schedules.schedule_date_from = hr_att_time.Date
            INNER JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id
            INNER JOIN hr_att_shifts ON hr_att_schedules.shift_id = hr_att_shifts.shift_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id   WHERE ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= " hr_att_schedules.schedule_date_from = '" . $date . "' AND hr_employees.employeestatus_id != '4' AND (hr_att_time.InTime IS NULL || hr_att_time.OutTime IS NULL) ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);

            $employees = DB::select($query);
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function leaveReport($setting, $date_from, $date_to)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;


            $query = "SELECT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name,hr_att_leave_types.leave_type,hr_leaves.`from`,hr_leaves.`to`,hr_leaves.date AS request_date,(SELECT hr_employees.name_withinitial FROM hr_employees WHERE hr_employees.employee_id = hr_leaves.approved_by) AS approved_by,(DATEDIFF(hr_leaves.`to`,hr_leaves.`from`)+1) AS days,hr_employee_sections.section FROM hr_leaves
            INNER JOIN hr_employees ON hr_leaves.employee_id = hr_employees.employee_id
            INNER JOIN hr_att_leave_types ON hr_leaves.leave_type_id = hr_att_leave_types.leave_type_id
            INNER JOIN hr_employee_sections ON hr_employees.section_id = hr_employee_sections.section_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
            LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id   WHERE ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= " DATE(hr_leaves.date) BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND hr_leaves.status = '1'";
            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);

            $employees = DB::select($query);
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", ['body' => $employees, 'title' => $title, 'report_date' => $date_from . " To " . $date_to]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function attendanceReport2($setting, $date_from, $date_to)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;

            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT DISTINCT hr_employees.employee_id,hr_employees.name_withinitial AS employee_name
            FROM hr_employees
            INNER JOIN hr_att_time ON hr_employees.employee_id = hr_att_time.EmployeeID
            INNER JOIN hr_att_schedules ON hr_att_time.Date = hr_att_schedules.schedule_date_from AND hr_att_time.EmployeeID = hr_att_schedules.employee_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id  WHERE hr_employees.employeestatus_id != '4' AND ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= ' ORDER BY convert(hr_employees.employee_id, decimal) ASC';
            $result = DB::select($query);

            foreach ($result as $employee) {
                $query = "SELECT DISTINCT hr_employees.employee_id,
                hr_employees.name_withinitial AS employee_name, 
                hr_att_time.Date,
                hr_att_schedules.in_time AS schedule_in,
                hr_att_schedules.out_time AS schedule_out,
                hr_att_time.InTime AS actual_in,
                hr_att_time.OutTime AS actual_out,
                hr_att_time.remark AS remark,
                (SELECT hr_att_shifts.shift_name FROM hr_att_shifts WHERE hr_att_shifts.shift_id = hr_att_time.ShiftNo) AS shift
                FROM hr_att_time
                LEFT JOIN hr_att_schedules ON hr_att_time.EmployeeID = hr_att_schedules.employee_id AND hr_att_time.Date = hr_att_schedules.schedule_date_from
                LEFT JOIN hr_employees ON hr_att_schedules.employee_id = hr_employees.employee_id
                LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id
                LEFT JOIN hr_teams ON hr_employee_teams.team_id = hr_teams.team_id WHERE hr_att_time.EmployeeID = '" . $employee->employee_id . "' AND hr_att_time.Date BETWEEN '" . $date_from . "' AND '" . $date_to . "'  AND hr_employees.employeestatus_id != '4' ";
                $employee->result = DB::select($query);
            }
            $responseBody = $this->responseBody(true, "Attendance", "all", ['body' => $result, 'title' => $title]);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "Attendance", "error", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function supervisorApprovalReport($id, $date_from)
    {

        try {
            $query = "SELECT
                hr_employees.`name_withinitial` AS employee_name,
                hr_employees.`employee_id` AS employee_id,
                hr_att_time.`InTime` AS InTime,
                hr_att_time.`OutTime` AS OutTime,
                hr_att_time.`ID` AS att_id,
                hr_att_time.`supervisor_approval` AS approval,
                hr_att_time.`remark` AS remark
           FROM
                `hr_employees` hr_employees INNER JOIN `hr_att_time` hr_att_time ON hr_employees.`attendance_id` = hr_att_time.`EmployeeID` WHERE hr_employees.`supervisor_id` = '" . $id . "' AND hr_att_time.`Date`  = '" . $date_from . "'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";

            $result = DB::select($query);
            $responseBody = $this->responseBody(true, "Attendance", "all", ['body' => $result]);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "Attendance", "error", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }


    public function rosterMissingReport($setting, $date_from, $date_to)
    {
        try {
            $title = '';
            $setting_aaray =  json_decode($setting);
            $category = $setting_aaray->category;
            $site = $setting_aaray->site;
            $department = $setting_aaray->department;
            $section = $setting_aaray->section;
            $subsection = $setting_aaray->subsection;
            $company = $setting_aaray->company;
            $team = $setting_aaray->team;
            $employee = $setting_aaray->employee;


            ini_set('max_execution_time', '0'); // for infinite time of execution

            $query = "SELECT hr_employees.employee_id,hr_employee_sites.site,hr_employees.name_withinitial AS employee_name, date(hr_att_raw_data.ChecKINOUT) AS date FROM hr_att_raw_data 
            INNER JOIN hr_employees ON hr_att_raw_data.ID = hr_employees.attendance_id
            INNER JOIN hr_employee_sites ON hr_employees.site_id = hr_employee_sites.site_id
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id 
            WHERE hr_att_raw_data.ID NOT IN(SELECT hr_att_schedules.employee_id FROM hr_att_schedules WHERE employee_id = hr_employees.employee_id) AND DATE(ChecKINOUT) BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
                $title = 'Category : ' . EmployeeCategory::find($category)->category;
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
                $title = 'Site : ' . EmployeeSite::find($site)->site;
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
                $title = 'Department : ' . EmployeeDepartment::find($department)->department;
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
                $title = 'Section : ' . EmployeeSection::find($section)->section;
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
                $title = 'Subsection : ' . EmployeeSubsection::find($subsection)->subsection;
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
                $title = 'Company : ' . Company::find($company)->company;
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
                $title = 'Team : ' . Team::find($team)->team_name;
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }

            $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query.= " GROUP BY DATE(ChecKINOUT)";
            $rosterMissing = DB::select($query);
            $responseBody = $this->responseBody(true, "Attendance", "all", ['body' => $rosterMissing,'report_date' => $date_from . " To " . $date_to]);
        } catch (\Exception $exception) {
            $responseBody = $this->responseBody(false, "Attendance", "error", $query);
        }
        return response()->json(["data" => $responseBody]);
    }


    /**
     * responseBody
     * This is used to create response.
     * @param success This is the paramter require boolean
     * @param name This is the paramter require ui table name
     * @param message This is the paramter require message content
     * @param result This is the paramter require result as some of data to return client
     * @return Json This returns as response.
     */
    private function responseBody($success, $name, $message, $result)
    {
        $body = [
            "success" => $success,
            "message" => $message,
            "name" => $name,
            "result" => $result
        ];
        return $body;
    }
}