<?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\EmployeeStatus;
use Modules\Hr\Entities\EmployeeSubsection;
use Modules\Hr\Entities\Team;

class EmployeeDetailReportController 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_status" => $this->employee_status(),
                "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 employee_status()
    {

        try {
            $status = EmployeeStatus::all();
            $collection = [];
            for ($i = 0; $i < count($status); $i++) {
                array_push($collection, ["id" => $status[$i]['employeestatus_id'], "value" => $status[$i]['employeestatus']]);
            }
            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 detailReport($setting)
    {
        try {
            $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;
            $status = $setting_aaray->status;
            $employee = $setting_aaray->employee;


            $query = "SELECT DISTINCT
            hr_employees.`employee_id` AS employee_id,
            hr_employees.`name_withinitial` AS employee_name,
            hr_employees.`address` AS address,
            hr_employees.`epf_no` AS epf_no,
            hr_employees.`birthday` AS birthday,
            IF(hr_employees.`gender_id` < 1, 'Female', 'Male') AS gender,
            hr_employee_designations.`designation` AS designation,
            hr_employees.`nic` AS nic,
            hr_employees.`date_join` AS date_join,
            hr_employee_civilstatuses.`civilstatus` AS civilstatus,
            hr_employees.`fixed` AS fixed_mobile,
            hr_employees.`attendance_id` AS attendance_id,
            hr_employee_statuses.`employeestatus` AS employeestatus,
            hr_employee_categories.`category` AS category,
            hr_employee_sites.`site` AS site,
            hr_employee_departments.`department` AS department,
            (SELECT hr_teams.team_name FROM hr_teams INNER JOIN hr_employee_teams ON hr_teams.team_id = hr_employee_teams.team_id WHERE hr_employee_teams.employee_id = hr_employees.employee_id) AS team_name,
            hr_employees.`supervisor_id` AS supervisor,
            hr_gs_division.`gs_division` AS division,
            hr_police.`police_station` AS police,
            hr_moh_divisions.`moh_division` AS moh
       FROM
            `hr_employee_designations` hr_employee_designations
            LEFT JOIN `hr_employees` hr_employees ON hr_employee_designations.`designation_id` = hr_employees.`designation_id`
            LEFT JOIN `hr_employee_civilstatuses` hr_employee_civilstatuses ON hr_employees.`civilstatus_id` = hr_employee_civilstatuses.`civilstatus_id`
            LEFT JOIN `hr_employee_statuses` hr_employee_statuses ON hr_employees.`employeestatus_id` = hr_employee_statuses.`employeestatus_id`
            LEFT JOIN `hr_employee_categories` hr_employee_categories ON hr_employees.`category_id` = hr_employee_categories.`category_id`
            LEFT JOIN `hr_employee_sites` hr_employee_sites ON hr_employees.`site_id` = hr_employee_sites.`site_id`
            LEFT JOIN `hr_employee_departments` hr_employee_departments ON hr_employees.`department_id` = hr_employee_departments.`department_id`
            LEFT JOIN `hr_teams` hr_teams ON hr_employees.`employee_id` = hr_teams.`leader_id`
            LEFT JOIN `hr_gs_divisions` hr_gs_division ON hr_employees.`gs_division_id` = hr_gs_division.`gs_division_id`
            LEFT JOIN `hr_police` hr_police ON hr_employees.`police_station_id` = hr_police.`police_station_id`
            LEFT JOIN `hr_moh_divisions` hr_moh_divisions ON hr_employees.`moh_division_id` = hr_moh_divisions.`moh_division_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 ";
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
            }

            if ($status != '') {
                $query .= "hr_employees.employeestatus_id = '" . $status . "' AND ";
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
            }


            if ($status != 4) {
                $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            } else {
                $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
                $query .= " ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            }

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


    public function basicSalaryReport($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_employee_designations.designation,
            IF(hr_employee_salary.salary,hr_employee_salary.salary,0) AS salary,
            hr_employee_categories.budget_allowance1 AS br1,
            hr_employee_categories.budget_allowance2 AS br2,
            IF(TRUE,0,0) AS travalling_allowance,
            IF(TRUE,0,0) AS production_incentive,
            (
            IF(salary,salary,0)+
            IF(hr_employee_categories.budget_allowance1,hr_employee_categories.budget_allowance1,0)+
            IF(hr_employee_categories.budget_allowance2,hr_employee_categories.budget_allowance2,0)
            ) AS total_day_salary
            FROM hr_employees 
            LEFT JOIN hr_employee_designations ON hr_employees.designation_id = hr_employee_designations.designation_id
            LEFT JOIN hr_employee_salary ON hr_employees.employee_id = hr_employee_salary.employee_id
            LEFT JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_id 
            INNER 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);
            $query .= " 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 resignEmployeeDetailsReport($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_employees.name_withinitial AS employee_name,
            hr_employees.epf_no,
            hr_employees.address,
            hr_employees.mobile,
            hr_employee_designations.designation,
            hr_employee_departments.department,
            hr_employee_sections.section,
            hr_employees.birthday AS dob,
            IF(hr_employees.gender_id = 1,'Male','Female') AS gender,
            hr_employees.nic,
            hr_employees.date_join,
            hr_employees.resign_date,
            IF(TIMESTAMPDIFF(YEAR, hr_employees.date_join, CURDATE()) = 0,CONCAT(TIMESTAMPDIFF(MONTH, hr_employees.date_join, CURDATE()),' Months'),CONCAT(TIMESTAMPDIFF(YEAR, hr_employees.date_join, CURDATE()),' Years')) AS service,
            hr_employee_salary.salary
            FROM hr_employees
            LEFT JOIN hr_employee_designations ON hr_employees.designation_id = hr_employee_designations.designation_id
            LEFT JOIN hr_employee_departments ON hr_employees.department_id = hr_employee_departments.department_id
            LEFT JOIN hr_employee_sections ON hr_employees.section_id = hr_employee_sections.section_id
            LEFT JOIN hr_employee_salary ON hr_employees.employee_id = hr_employee_salary.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 .= " (hr_employees.employeestatus_id = '4' || hr_employees.employeestatus_id = '5') AND hr_employees.resign_date BETWEEN  '" . $date_from . "' AND '" . $date_to . "'  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_from . " to " . $date_to
            ]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $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 dailyOTReport($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,
            hr_att_time.N_overtime AS n_ot,
            hr_att_time.D_overtime AS d_ot,
            ((hr_att_time.N_overtime * hr_salary_data.ot_rate1_amount)+(hr_att_time.D_overtime * hr_salary_data.ot_rate2_amount)) AS ot_amount 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
            INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.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 .= " hr_att_schedules.schedule_date_from = '" . $date . "' AND (hr_att_time.InTime AND hr_att_time.OutTime) 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 highOTReopt($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_teams.team_name,
            hr_employee_designations.designation,
            hr_att_shifts.shift_name AS shift,
            SUM(hr_att_time.N_overtime) AS n_ot,
            (SUM(hr_att_time.N_overtime) * hr_salary_data.ot_rate1_amount) AS n_ot_cost,
            SUM(hr_att_time.D_overtime) AS d_ot,
            (SUM(hr_att_time.D_overtime) * hr_salary_data.ot_rate2_amount) AS d_ot_cost 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
            INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id
            INNER JOIN hr_employee_designations ON hr_employees.designation_id = hr_employee_designations.designation_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 BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND (hr_att_time.InTime AND hr_att_time.OutTime) GROUP BY hr_employees.employee_id 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_from . " To " . $date_to]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }*/


    /*public function monthlyOTSummaryReport($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_teams.team_name,
            hr_employee_designations.designation,
            hr_att_shifts.shift_name AS shift,
            SUM(hr_att_time.N_overtime) AS n_ot,
            (SUM(hr_att_time.N_overtime) * hr_salary_data.ot_rate1_amount) AS n_ot_cost,
            SUM(hr_att_time.D_overtime) AS d_ot,
            (SUM(hr_att_time.D_overtime) * hr_salary_data.ot_rate2_amount) AS d_ot_cost,
            0 AS p_ot,
            0 AS p_ot_cost,
            (SUM(hr_att_time.N_overtime) + SUM(hr_att_time.D_overtime)) AS total_ot_hours,
            ((SUM(hr_att_time.N_overtime) * hr_salary_data.ot_rate1_amount) + (SUM(hr_att_time.D_overtime) * hr_salary_data.ot_rate2_amount)) AS total_ot_amount 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
            INNER JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id
            INNER JOIN hr_employee_designations ON hr_employees.designation_id = hr_employee_designations.designation_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 BETWEEN '" . $date_from . "' AND '" . $date_to . "' AND (hr_att_time.InTime AND hr_att_time.OutTime) GROUP BY hr_employees.employee_id 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_from . " To " . $date_to]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }*/


    /*public function epf_etfReports($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.epf_no,
            hr_employees.nic,
            @employer_epf :=hr_salary_data.employer_epf As employer_contribution,
            @employee_epf :=hr_salary_data.employee_epf As employee_contribution,
			(@employer_epf + @employee_epf) As total_contribution,
            hr_salary_data.employee_etf,
            (SUM(hr_salary_data.earn_01)+
            SUM(hr_salary_data.earn_02)+
            SUM(hr_salary_data.earn_03)+
            SUM(hr_salary_data.earn_04)+
            SUM(hr_salary_data.earn_05)+
            SUM(hr_salary_data.earn_06)+
            SUM(hr_salary_data.earn_07)+
            SUM(hr_salary_data.earn_08)+
            SUM(hr_salary_data.earn_09)+
            SUM(hr_salary_data.earn_10)) AS total_earnings,
            SUM(hr_salary_data.worked_days) AS work_day
            FROM hr_employees 
            LEFT JOIN hr_salary_data ON hr_employees.employee_id = hr_salary_data.employee_id
            LEFT JOIN hr_employee_categories ON hr_employees.category_id = hr_employee_categories.category_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 ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= " hr_salary_data.salary_year = YEAR('" . $date . "') AND hr_salary_data.salary_month = MONTH('" . $date . "') GROUP BY employee_id 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 allDeductionsReport($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_salary_data.employee_id,hr_employees.name_withinitial AS employee_name,@advance := hr_salary_data.advance AS advance,@loan := hr_salary_data.loan AS loan,
            @deduct1 := IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Welfare Fund',
               hr_salary_data.deduct_01,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Welfare Fund',
               hr_salary_data.deduct_02,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Welfare Fund',
               hr_salary_data.deduct_03,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Welfare Fund',
               hr_salary_data.deduct_04,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Welfare Fund',
               hr_salary_data.deduct_05,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Welfare Fund',
               hr_salary_data.deduct_06,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Welfare Fund',
               hr_salary_data.deduct_07,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Welfare Fund',
               hr_salary_data.deduct_08,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Welfare Fund',
               hr_salary_data.deduct_09,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Welfare Fund',
               hr_salary_data.deduct_10,
            0)))))))))) AS welfare_fund,
            @deduct2 :=  IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_01,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_02,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_03,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_04,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_05,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_06,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_07,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_08,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_09,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Welfare Shop Deduction',
               hr_salary_data.deduct_10,
            0)))))))))) AS welfare_shop_deduction,
            @deduct3 := IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_01) = 'Other Deductions',
               hr_salary_data.deduct_01,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_02) = 'Other Deductions',
               hr_salary_data.deduct_02,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_03) = 'Other Deductions',
               hr_salary_data.deduct_03,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_04) = 'Other Deductions',
               hr_salary_data.deduct_04,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_05) = 'Other Deductions',
               hr_salary_data.deduct_05,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_06) = 'Other Deductions',
               hr_salary_data.deduct_06,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_07) = 'Other Deductions',
               hr_salary_data.deduct_07,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_08) = 'Other Deductions',
               hr_salary_data.deduct_08,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_09) = 'Other Deductions',
               hr_salary_data.deduct_09,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.deduct_trans_id_10) = 'Other Deductions',
               hr_salary_data.deduct_10,
            0)))))))))) AS other_deduction,
            (@advance + @deduct1 + @deduct2 + @deduct3) AS total_deductions FROM hr_salary_data
            INNER JOIN hr_employees ON hr_salary_data.employee_id = hr_employees.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 ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
                $title = 'Employee : ' . Employee::find($employee)->name_withinitial;
            }


            $query .= " hr_salary_data.salary_year = '" . explode("-", $date_from)[0] . "' and hr_salary_data.salary_month = '" . explode("-", $date_from)[1] . "' 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_from . " To " . $date_to]);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        return response()->json(["data" => $responseBody]);
    }*/


    /*public function nightAllowanceReport($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 AS employee_id,
            hr_employees.name_withinitial AS employee_name,
						@shift := hr_att_shifts.shift_name,
            SUM(IF(DAY(hr_att_time.Date) = 1 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day1,
            SUM(IF(DAY(hr_att_time.Date) = 2 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day2,
            SUM(IF(DAY(hr_att_time.Date) = 3 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day3,
            SUM(IF(DAY(hr_att_time.Date) = 4 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day4,
            SUM(IF(DAY(hr_att_time.Date) = 5 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day5,
            SUM(IF(DAY(hr_att_time.Date) = 6 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day6,
            SUM(IF(DAY(hr_att_time.Date) = 7 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day7,
            SUM(IF(DAY(hr_att_time.Date) = 8 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day8,
            SUM(IF(DAY(hr_att_time.Date) = 9 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day9,
            SUM(IF(DAY(hr_att_time.Date) = 10 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day10,
            SUM(IF(DAY(hr_att_time.Date) = 11 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day11,
            SUM(IF(DAY(hr_att_time.Date) = 12 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day12,
            SUM(IF(DAY(hr_att_time.Date) = 13 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day13,
            SUM(IF(DAY(hr_att_time.Date) = 14 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day14,
            SUM(IF(DAY(hr_att_time.Date) = 15 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day15,
            SUM(IF(DAY(hr_att_time.Date) = 16 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day16,
            SUM(IF(DAY(hr_att_time.Date) = 17 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day17,
            SUM(IF(DAY(hr_att_time.Date) = 18 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day18,
            SUM(IF(DAY(hr_att_time.Date) = 19 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day19,
            SUM(IF(DAY(hr_att_time.Date) = 20 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day20,
            SUM(IF(DAY(hr_att_time.Date) = 21 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day21,
            SUM(IF(DAY(hr_att_time.Date) = 22 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day22,
            SUM(IF(DAY(hr_att_time.Date) = 23 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day23,
            SUM(IF(DAY(hr_att_time.Date) = 24 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day24,
            SUM(IF(DAY(hr_att_time.Date) = 25 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day25,
            SUM(IF(DAY(hr_att_time.Date) = 26 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day26,
            SUM(IF(DAY(hr_att_time.Date) = 27 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day27,
            SUM(IF(DAY(hr_att_time.Date) = 28 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day28,
            SUM(IF(DAY(hr_att_time.Date) = 29 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day29,
            SUM(IF(DAY(hr_att_time.Date) = 30 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day30,
            SUM(IF(DAY(hr_att_time.Date) = 31 && hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) AS day31,
            @total_days := SUM(IF(hr_att_time.In_Status = 1 && hr_att_time.Out_Status = 1 && @shift = 'E',1,0)) 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
		    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 ($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(LENGTH(MONTH('" . $date . "')) =1,CONCAT('-0',MONTH('" . $date . "')),CONCAT('-',MONTH('" . $date . "'))),'%') ";
            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= " GROUP BY hr_att_time.EmployeeID 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 attendanceAllowanceReport($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 DISTINCT hr_salary_data.employee_id,hr_employees.name_withinitial AS employee_name,hr_employee_categories.category,hr_salary_data.worked_days,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_01) = 'Attendance Allowance',
               hr_salary_data.earn_01,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_02) = 'Attendance Allowance',
               hr_salary_data.earn_02,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_03) = 'Attendance Allowance',
               hr_salary_data.earn_03,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_04) = 'Attendance Allowance',
               hr_salary_data.earn_04,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_05) = 'Attendance Allowance',
               hr_salary_data.earn_05,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_06) = 'Attendance Allowance',
               hr_salary_data.earn_06,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_07) = 'Attendance Allowance',
               hr_salary_data.earn_07,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_08) = 'Attendance Allowance',
               hr_salary_data.earn_08,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_09) = 'Attendance Allowance',
               hr_salary_data.earn_09,
            IF((SELECT hr_transaction_item_setups.narration FROM hr_transaction_item_setups WHERE hr_transaction_item_setups.item_id = hr_salary_data.earn_trans_id_10) = 'Attendance Allowance',
               hr_salary_data.earn_10,
            0)))))))))) AS attendance_allowance FROM hr_att_time
	          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_salary_data ON hr_att_time.EmployeeID = hr_salary_data.employee_id
            INNER JOIN hr_employees ON hr_salary_data.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_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_time.Date LIKE CONCAT(YEAR('" . $date . "'),IF(LENGTH(MONTH('" . $date . "')) =1,CONCAT('-0',MONTH('" . $date . "')),CONCAT('-',MONTH('" . $date . "'))),'%') ";
            //$query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
            $query .= " GROUP BY hr_employees.employee_id";

            $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 attendanceReport($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_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,
            (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 ";

            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 BETWEEN '" . $date_from . "' AND '" . $date_to . "'";
            //$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 leaveStatusReport($setting)
    {


        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_att_leave_types.leave_type,
            IF(YEAR(CURDATE()) = hr_att_employee_leaves.`year`, ROUND(((hr_att_leave_types.days_per_year / 12) * (IF(hr_att_employee_leaves.`month` > 1,(12 - hr_att_employee_leaves.`month`),12))),0),hr_att_leave_types.days_per_year) AS allocated,
            SUM(IF((DATEDIFF(hr_leaves.`to`,hr_leaves.`from`)+1),(DATEDIFF(hr_leaves.`to`,hr_leaves.`from`)+1),0)) AS used,
            IF(hr_att_leave_types.days_per_year = 0,'Yes','No') AS unlimited,
            (IF(YEAR(CURDATE()) = hr_att_employee_leaves.`year`, ROUND(((hr_att_leave_types.days_per_year / 12) * (IF(hr_att_employee_leaves.`month` > 1,(12 - hr_att_employee_leaves.`month`),12))),0),hr_att_leave_types.days_per_year) - SUM(IF((DATEDIFF(hr_leaves.`to`,hr_leaves.`from`)+1),(DATEDIFF(hr_leaves.`to`,hr_leaves.`from`)+1),0))) AS balance 
            FROM hr_att_leave_types
            LEFT JOIN hr_leaves ON hr_att_leave_types.leave_type_id = hr_leaves.leave_type_id
            INNER JOIN hr_att_employee_leaves ON hr_att_leave_types.leave_type_id = hr_att_employee_leaves.leave_type_id
            INNER JOIN hr_employees ON hr_att_employee_leaves.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 .= " GROUP BY hr_leaves.leave_type_id";

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


    public function employeeUnder18Report($setting, $age_from, $age_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 name,
            hr_employees.birthday,
            TIMESTAMPDIFF(YEAR, hr_employees.birthday, CURDATE()) AS age
            FROM hr_employees
            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 .= " TIMESTAMPDIFF(YEAR, hr_employees.birthday, CURDATE()) BETWEEN '" . $age_from . "' AND '" . $age_to . "' ";
            $query .= " AND hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";

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


    public function allEmployeeGratuityDetailsReport($setting)
    {
        try {
            $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;
            $status = $setting_aaray->status;
            $employee = $setting_aaray->employee;


            $query = "SELECT DISTINCT
            hr_employees.`employee_id` AS employee_id,
            hr_employees.`name_withinitial` AS employee_name,
            hr_employees.`address` AS address,
            hr_employees.`epf_no` AS epf_no,
            hr_employees.`birthday` AS birthday,
            IF(hr_employees.`gender_id` < 1, 'Female', 'Male') AS gender,
            hr_employee_designations.`designation` AS designation,
            hr_employees.`nic` AS nic,
            hr_employees.`date_join` AS date_join,
            hr_employee_civilstatuses.`civilstatus` AS civilstatus,
            hr_employees.`fixed` AS fixed_mobile,
            hr_employees.`attendance_id` AS attendance_id,
            hr_employee_statuses.`employeestatus` AS employeestatus,
            hr_employee_categories.`category` AS category,
            hr_employee_sites.`site` AS site,
            hr_employee_departments.`department` AS department,
            (SELECT hr_teams.team_name FROM hr_teams INNER JOIN hr_employee_teams ON hr_teams.team_id = hr_employee_teams.team_id WHERE hr_employee_teams.employee_id = hr_employees.employee_id) AS team_name,
            hr_employees.`supervisor_id` AS supervisor,
            hr_gs_division.`gs_division` AS division,
            hr_police.`police_station` AS police,
            hr_moh_divisions.`moh_division` AS moh,
            TIMESTAMPDIFF(YEAR, hr_employees.date_join, CURDATE()) AS period
       FROM
            `hr_employee_designations` hr_employee_designations
            LEFT JOIN `hr_employees` hr_employees ON hr_employee_designations.`designation_id` = hr_employees.`designation_id`
            LEFT JOIN `hr_employee_civilstatuses` hr_employee_civilstatuses ON hr_employees.`civilstatus_id` = hr_employee_civilstatuses.`civilstatus_id`
            LEFT JOIN `hr_employee_statuses` hr_employee_statuses ON hr_employees.`employeestatus_id` = hr_employee_statuses.`employeestatus_id`
            LEFT JOIN `hr_employee_categories` hr_employee_categories ON hr_employees.`category_id` = hr_employee_categories.`category_id`
            LEFT JOIN `hr_employee_sites` hr_employee_sites ON hr_employees.`site_id` = hr_employee_sites.`site_id`
            LEFT JOIN `hr_employee_departments` hr_employee_departments ON hr_employees.`department_id` = hr_employee_departments.`department_id`
            LEFT JOIN `hr_teams` hr_teams ON hr_employees.`employee_id` = hr_teams.`leader_id`
            LEFT JOIN `hr_gs_divisions` hr_gs_division ON hr_employees.`gs_division_id` = hr_gs_division.`gs_division_id`
            LEFT JOIN `hr_police` hr_police ON hr_employees.`police_station_id` = hr_police.`police_station_id`
            LEFT JOIN `hr_moh_divisions` hr_moh_divisions ON hr_employees.`moh_division_id` = hr_moh_divisions.`moh_division_id`
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id WHERE  TIMESTAMPDIFF(YEAR, hr_employees.date_join, CURDATE()) > '5' AND ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
            }

            if ($status != '') {
                $query .= "hr_employees.employeestatus_id = '" . $status . "' AND ";
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
            }


           /*if ($status != 4) {
                $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            } else {
                $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
                $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            }*/
            $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";

            //dd($query);

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


    public function allEmployeeRetirementCaderReport($setting)
    {
        try {
            $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;
            $status = $setting_aaray->status;
            $employee = $setting_aaray->employee;


            $query = "SELECT DISTINCT
            hr_employees.`employee_id` AS employee_id,
            hr_employees.`name_withinitial` AS employee_name,
            hr_employees.`address` AS address,
            hr_employees.`epf_no` AS epf_no,
            hr_employees.`birthday` AS birthday,
            IF(hr_employees.`gender_id` < 1, 'Female', 'Male') AS gender,
            hr_employee_designations.`designation` AS designation,
            hr_employees.`nic` AS nic,
            hr_employees.`date_join` AS date_join,
            hr_employee_civilstatuses.`civilstatus` AS civilstatus,
            hr_employees.`fixed` AS fixed_mobile,
            hr_employees.`attendance_id` AS attendance_id,
            hr_employee_statuses.`employeestatus` AS employeestatus,
            hr_employee_categories.`category` AS category,
            hr_employee_sites.`site` AS site,
            hr_employee_departments.`department` AS department,
            (SELECT hr_teams.team_name FROM hr_teams INNER JOIN hr_employee_teams ON hr_teams.team_id = hr_employee_teams.team_id WHERE hr_employee_teams.employee_id = hr_employees.employee_id) AS team_name,
            hr_employees.`supervisor_id` AS supervisor,
            hr_gs_division.`gs_division` AS division,
            hr_police.`police_station` AS police,
            hr_moh_divisions.`moh_division` AS moh
       FROM
            `hr_employee_designations` hr_employee_designations
            LEFT JOIN `hr_employees` hr_employees ON hr_employee_designations.`designation_id` = hr_employees.`designation_id`
            LEFT JOIN `hr_employee_civilstatuses` hr_employee_civilstatuses ON hr_employees.`civilstatus_id` = hr_employee_civilstatuses.`civilstatus_id`
            LEFT JOIN `hr_employee_statuses` hr_employee_statuses ON hr_employees.`employeestatus_id` = hr_employee_statuses.`employeestatus_id`
            LEFT JOIN `hr_employee_categories` hr_employee_categories ON hr_employees.`category_id` = hr_employee_categories.`category_id`
            LEFT JOIN `hr_employee_sites` hr_employee_sites ON hr_employees.`site_id` = hr_employee_sites.`site_id`
            LEFT JOIN `hr_employee_departments` hr_employee_departments ON hr_employees.`department_id` = hr_employee_departments.`department_id`
            LEFT JOIN `hr_teams` hr_teams ON hr_employees.`employee_id` = hr_teams.`leader_id`
            LEFT JOIN `hr_gs_divisions` hr_gs_division ON hr_employees.`gs_division_id` = hr_gs_division.`gs_division_id`
            LEFT JOIN `hr_police` hr_police ON hr_employees.`police_station_id` = hr_police.`police_station_id`
            LEFT JOIN `hr_moh_divisions` hr_moh_divisions ON hr_employees.`moh_division_id` = hr_moh_divisions.`moh_division_id`
            LEFT JOIN hr_employee_teams ON hr_employees.employee_id = hr_employee_teams.employee_id WHERE  TIMESTAMPDIFF(YEAR, hr_employees.birthday, CURDATE()) >= '60' AND ";

            if ($category != '') {
                $query .= "hr_employees.category_id = '" . $category . "' AND ";
            }

            if ($site != '') {
                $query .= "hr_employees.site_id = '" . $site . "' AND ";
            }

            if ($department != '') {
                $query .= "hr_employees.department_id = '" . $department . "' AND ";
            }

            if ($section != '') {
                $query .= "hr_employees.section_id = '" . $section . "' AND ";
            }

            if ($subsection != '') {
                $query .= "hr_employees.subsection_id = '" . $subsection . "' AND ";
            }

            if ($company != '') {
                $query .= "hr_employees.company_id = '" . $company . "' AND ";
            }

            if ($team != '') {
                $query .= "hr_employee_teams.team_id = '" . $team . "' AND ";
            }

            if ($status != '') {
                $query .= "hr_employees.employeestatus_id = '" . $status . "' AND ";
            }

            if ($employee != '') {
                $query .= "hr_employees.employee_id = '" . $employee . "' AND ";
            }


            /*if ($status != 4) {
                $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            } else {
                $query = preg_replace('/\W\w+\s*(\W*)$/', '$1', $query);
                $query .= " ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            }*/
            $query .= " hr_employees.employeestatus_id != '4'  ORDER BY convert(hr_employees.employee_id, decimal) ASC";
            //dd($query);

            $employees = DB::select($query);
            $responseBody = $this->responseBody(true, "EmployeeDetailReportController", "Report", $employees);
        } catch (Exception $exception) {
            $responseBody = $this->responseBody(false, "EmployeeDetailReportController", "Report", $exception);
        }
        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;
    }
}