<?php

namespace App\Http\Controllers\Backend\Hr;

use App\Http\Controllers\Controller;
use App\Models\Organization;
use App\Models\User;
use App\Models\Employee;
// use App\Models\Hr\Report;
use Carbon\Carbon;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Contracts\View\Factory;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class HrReportController extends Controller
{
    public function attendFilterMulti() {
        // $allData = $this->getFarmsAllData($request);
        // $query = DB::table('alerts');
        // if ($request->start_date !== null && $request->end_date !== null) {
        //     $start_date = Carbon::createFromFormat('d-m-Y', $request->start_date)->format('Y-m-d');
        //     $end_date = Carbon::createFromFormat('d-m-Y', $request->end_date)->format('Y-m-d');
        //     $query->whereDate('alerts.date', '>=', Carbon::createFromFormat('d-m-Y', $request->start_date)->format('Y-m-d'))
        //         ->whereDate('alerts.date', '<=', Carbon::createFromFormat('d-m-Y', $request->end_date)->format('Y-m-d'));
        // }
        // $query->Join('cattles', 'cattles.id', '=', 'alerts.cattle_id');
        // $query->leftJoin('tags', 'tags.id', '=', 'cattles.tag_id');
        // $query->whereIn('cattles.farm_id', $farmId)->whereIn('cattles.herd_id', $herdId)->whereIn('cattles.lot_id', $lotId)->where('alerts.status', 0)->where('alerts.type', 'amr_check')->select('cattles.farm_id', 'cattles.herd_id', 'cattles.lot_id', 'cattles.status', 'tags.uid', 'alerts.date', 'cattles.birth_date')->orderBy('alerts.id', 'desc');
        // $cattles = $query->get();
        $organizations = DB::table('organizations')->where('is_main', '=', 0)->get();
        $departments = DB::table('departments')->get();
        $designations = DB::table('designations')->get();
        $sessions = DB::table('organization_session')->get();
        $employees = Employee::orderBy('id', 'desc')->get();
        return view('backend.hr.report.attendFilterMulti', compact('organizations', 'sessions', 'departments', 'designations', 'employees'));
    }

    public function attendReportMulti(Request $request) {
        $attendanceData = $this->getStaffListWithAttend($request->org_id, $request->department_id, $request->designation_id,
            $request->employee_id, $request->session_id, $request->emp_type, $request->duty_shift,
            $request->sdate, $request->edate, $request->abPrStatus, $request->record_status, $request->start, $request->per_page);

        $stitle = "Employee Attendance Report";
        $session_id = $request->session_id;
        $duty_shift = $request->duty_shift;
        $abPrStatus = $request->abPrStatus;
        $record_status = $request->record_status;
        $sdate = $request->sdate;
        $edate = $request->edate;
        $branch_name = $department_name = $designation_name = $session_name = '';
        if($request->org_id) {
            $branch = DB::table('organizations')->where('id', $request->org_id)->first();
            $branch_name = $branch ? $branch->name : '';
        }
        if($request->department_id) {
            $department = DB::table('departments')->where('department_id', $request->department_id)->first();
            $department_name = $department ? $department->department_name : '';
        }
        if($request->designation_id) {
            $designation = DB::table('designations')->where('id', $request->designation_id)->first();
            $designation_name = $designation ? $designation->name : '';
        }

        $aInfo = DB::table('organizations as org')->where('org.is_main', 1)->first();

        return view('backend.hr.report.attendReportMulti', compact('attendanceData', 'aInfo', 'stitle', 'session_id', 'duty_shift',
        'abPrStatus', 'record_status', 'sdate', 'edate', 'branch_name', 'department_name', 'designation_name'));
    }

    public function attendReportDetail(Request $request) {
        $attendReportSingle = $this->attendReportSingle($request->session_id, $request->reg_id, $request->sdate, $request->edate);

        $stitle = "Employee Attendance History";
        $session_id = $request->session_id;
        $reg_id = $request->reg_id;
        $sdate = $request->sdate;
        $edate = $request->edate;

        $sInfo = $recData = DB::table('employees as ei')->leftJoin('employees_records as er', 'er.employee_id', '=', 'ei.id')
        ->leftJoin('designations as dsg', 'dsg.id', '=', 'ei.designation_id')
        ->orderBy('er.rec_id', 'DESC')->where('ei.employee_no', $reg_id)->first();

        $org_id = $recData->organization_id;

        $data['rec_id'] = $rec_id = $recData->rec_id;

        $leave_datesData = DB::select("SELECT GROUP_CONCAT(leave_dates) AS leave_dates FROM `emp_leave_apps` WHERE rec_id = $rec_id AND lStatus = 2");

        $leave_dates = $leave_datesData ? $leave_datesData[0]->leave_dates : array();
        $aInfo = DB::table('organizations as org')->leftJoin('organization_session as orgSess', 'orgSess.org_id', '=', 'org.id')
        ->orderBy('org.is_main', 'DESC')->where('org.id', $org_id)->first();
        //    echo '<pre>'; echo $this->db->last_query(); echo '<pre>'; print_r($data['attendReportSingle']); exit();
        return view('backend.hr.report.attendReportDetail', compact('attendReportSingle', 'aInfo', 'sInfo', 'stitle',
        'session_id', 'reg_id', 'sdate', 'edate', 'leave_dates'));
    }

    public function getStaffListWithAttend($branch_id, $department_id, $designation_id,
        $employee_id, $session_id, $emp_type = '', $duty_shift = '', $sdate = '', $edate = '',
        $abPrStatus = '', $record_status = '', $start = 0, $per_page = 0) {

        $limit = ($per_page) ? (" LIMIT $start, $per_page") : "";
		if($sdate==0){
			$sdate='2019-01-01';
		}
		if($edate==0){
			$edate=date('Y-m-d');
		}
        //$org_id = $this->db->query("SELECT org_id FROM `organization_session` ases WHERE ases.`session_id` = $session_id")->row('org_id');
        $sql = $sql2 = $sqlActiveDays = $sqlHoliDays = $sqlExt = $sqlAbPr = '';
        // $sql .= ($emp_type) ? " AND ei.emp_type = '$emp_type'" : "";
        // $sql .= " AND ei.emp_type IN ('T', 'S')";
        $sql .= ($branch_id) ? " AND ei.branch_id = '$branch_id'" : "";
        $sql .= ($department_id) ? " AND ei.department_id = '$department_id'" : "";
        $sql .= ($designation_id) ? " AND ei.designation_id = '$designation_id'" : "";
        $sql .= ($employee_id) ? " AND ei.id = '$employee_id'" : "";

        $sql .= ($duty_shift) ? " AND ei.duty_shift = '$duty_shift'" : "";
        $sql .= ($record_status) ? ($record_status ? " AND ei.status = 1" : " AND ei.status = 2") : "";
        $sql2 .= ($session_id) ? " AND session_id = $session_id " : '';
        $sql2 .= ($sdate and $edate) ? " AND (sa.att_date >= '$sdate' AND sa.att_date <= '$edate 23:59:59' )" : '';

        if($sdate and $edate) {
            $sqlActiveDays .= " AND v.selected_date BETWEEN '$sdate' AND '$edate'";
            $sqlHoliDays .= " AND holiday BETWEEN '$sdate' AND '$edate'";
        } else {
            $sqlExt .= ($session_id) ? " AND sat.session_id = $session_id " : '';
            $getAttDta = DB::select("SELECT MAX(sat.att_date) AS maxDate, MIN(sat.att_date) AS minDate
            FROM staff_attendance sat WHERE 1=1 $sqlExt");

            if($getAttDta) {
                $minDate = $getAttDta[0]->minDate; $maxDate = $getAttDta[0]->maxDate;
                $sqlActiveDays .= " AND v.selected_date BETWEEN '$minDate' AND '$maxDate'";
                $sqlHoliDays .= " AND holiday BETWEEN '$minDate' AND '$maxDate'";
            }
        }
        if($session_id) {
            $sqlHoliDays .= " AND ac.session_id = $session_id";
        }

        $total_holidaysData = DB::select("SELECT IFNULL(COUNT(holiday), 0) AS total_holidays
        FROM holiday_calendar_details acd
          JOIN holiday_calendar ac ON ac.`hd_id` = acd.`hd_id`
        WHERE 1=1 $sqlHoliDays AND ac.`approval_status` = 2");

        if ($total_holidaysData) {
            $total_holidays = $total_holidaysData[0]->total_holidays;
        } else {
            $total_holidays = 0;
        }
        // main query
        $queryDetails
        =
        "SELECT
            ei.employee_no, ei.employee_name, ei.id AS emp_id, DATE_FORMAT(ei.joining_date, '%d-%b-%Y') AS joining_date, ei.released_on,
            designations.name AS post_name, mobile_no, ei.duty_shift, ei.joining_date AS joined_date,

            (SELECT COUNT(leavedate) AS total_leaves
            FROM emp_leave_apps_details elad
                JOIN emp_leave_apps ela ON ela.ela_id = elad.ela_id JOIN employees_records er ON er.`rec_id` = ela.rec_id
            WHERE 1 AND ela.lStatus = 2 AND er.employee_id = ei.`id`
            ) AS total_leaves,

            '$total_holidays' AS total_holidays,

            (SELECT COUNT(DISTINCT(DATE_FORMAT(att_date,'%Y-%m-%d')))
            FROM staff_attendance sa
            WHERE reg_id = ei.employee_no $sql2 AND att_status = 1
            ) AS present,

            /*get_total_late_att(ei.`id`,'$sdate','$edate') AS total_late*/
            (SELECT COUNT(sa.is_delay) FROM staff_attendance sa WHERE sa.reg_id = ei.employee_no
            AND (sa.att_date >= '$sdate' AND sa.att_date <= '$edate 23:59:59') AND sa.is_delay = 1
            ) AS total_late
        FROM employees ei
            JOIN designations ON designations.`id` = ei.`designation_id`
        WHERE 1=1 $sql
        ORDER BY ei.employee_no ASC $limit";

        if($abPrStatus) {
            $sqlAbPr .= ($abPrStatus == 'pr') ? " AND a.present > 0" : ' AND a.present = 0';
            $dataaaa = DB::select("SELECT a.* FROM (
                $queryDetails
            ) a WHERE 1=1 $sqlAbPr ORDER BY a.employee_no ASC");
        } else {
            $dataaaa = DB::select("$queryDetails");
        }
        // echo 'gggggggggggggg'; dd($dataaaa);
        return $dataaaa;
    }

    public function attendReportSingle($session_id, $reg_id, $sdate, $edate) {

        $sql = $sqlActiveDays = $sqlHoliDays = $sqlExt = '';
        $sql .= ($session_id) ? " AND sa.session_id = $session_id" : "";
        $sql .= ($sdate and $edate) ? " AND (sa.att_date >= '$sdate' AND sa.att_date <= '$edate 23:59:59' )" : '';

        if($sdate and $edate) {
            $sqlActiveDays .= " AND v.selected_date BETWEEN '$sdate' AND '$edate 23:59:59'";
            $sqlHoliDays .= " AND holiday BETWEEN '$sdate' AND '$edate 23:59:59'";
        } else {

            $sqlExt .= ($session_id) ? " AND sat.session_id = $session_id " : '';
            $getAttDta = DB::select("SELECT MAX(sat.att_date) AS maxDate, MIN(sat.att_date) AS minDate
            FROM staff_attendance sat WHERE 1=1 $sqlExt");

            if($getAttDta) {
                $minDate = $getAttDta[0]->minDate; $maxDate = $getAttDta[0]->maxDate;
                $sqlActiveDays .= " AND v.selected_date BETWEEN '$minDate' AND '$maxDate'";
                $sqlHoliDays .= " AND holiday BETWEEN '$minDate' AND '$maxDate'";
            }
        }
        // echo '<pre>'; echo $this->db->last_query();exit;
        if($session_id) {
            $sqlHoliDays .= " AND ac.session_id = $session_id";
        }

        $eiDataaa = DB::select("SELECT * FROM employees ei WHERE 1 AND employee_no = $reg_id");
        $eiData = $eiDataaa[0];

        $released_on = ($eiData->released_on) ? " COALESCE('$eiData->released_on', NOW())" : " NOW()";

        $query = DB::select("SELECT c.*, CASE WHEN in_time=out_time THEN '' ELSE out_time END out_time,
            (SELECT atext FROM organization_special_days asd
            WHERE asd.adate = c.att_date AND asd.approval_status = 2 LIMIT 1) AS special_occasion
        FROM (
                SELECT a.*,
                -- DATE_FORMAT(a.att_date2, '%H:%i') AS att_time,
                CASE WHEN a.att_status = 1 THEN 'Present' ELSE 'Absent' END attendance,
                (SELECT DATE_FORMAT(MIN(sa1.att_date), '%H:%i') AS att_time FROM staff_attendance sa1
                WHERE sa1.reg_id = a.reg_id AND (sa1.att_date
                BETWEEN a.att_date AND CONCAT(a.att_date, ' 23:59:59'))
                ) AS in_time,

                (SELECT DATE_FORMAT(MAX(sa1.att_date), '%H:%i') AS att_time FROM staff_attendance sa1
                WHERE sa1.reg_id = a.reg_id AND (sa1.att_date
                BETWEEN a.att_date AND CONCAT(a.att_date, ' 23:59:59'))
                ) AS out_time,

                (SELECT DATE_FORMAT(MIN(sa1.att_date), '%H:%i:%s') AS att_time FROM staff_attendance sa1
                WHERE sa1.reg_id = a.reg_id AND (sa1.att_date
                BETWEEN a.att_date AND CONCAT(a.att_date, ' 23:59:59'))
                ) AS in_time2,

                (SELECT IFNULL(MAX(is_delay), 0) AS is_delay FROM staff_attendance sa1
                WHERE is_delay = '1' AND sa1.reg_id = a.reg_id AND (sa1.att_date
                BETWEEN a.att_date AND CONCAT(a.att_date, ' 23:59:59'))
                ) AS is_delay
            FROM
              (
                (
                    SELECT DISTINCT(DATE_FORMAT(att_date,'%Y-%m-%d')) AS att_date, sa.att_status, sa.reg_id
                    FROM staff_attendance sa
                    WHERE 1=1 $sql AND sa.reg_id = $reg_id AND sa.att_status = 1
                )

                UNION ALL

                (
                    SELECT (v.selected_date) att_date, '0' att_status, '$reg_id' reg_id
                    FROM
                        (SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
                            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
                            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
                            (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
                        ) v
                    WHERE 1=1 AND (v.selected_date BETWEEN '$eiData->joining_date' AND $released_on)
                    $sqlActiveDays AND v.selected_date NOT IN

                    (SELECT holiday
                    FROM holiday_calendar_details acd
                    JOIN holiday_calendar ac ON ac.`hd_id` = acd.`hd_id`
                    WHERE 1=1 $sqlHoliDays
                    AND ac.`approval_status` = 2) AND v.selected_date NOT IN

                    (SELECT DISTINCT(DATE_FORMAT(att_date,'%Y-%m-%d')) AS att_date
                    FROM staff_attendance sa
                    WHERE 1=1 $sql AND sa.reg_id = $reg_id AND sa.att_status = 1
                    )

                )
            ) a
        ) c ORDER BY c.att_date ASC");

        return $query;
    }

    public function attendStatusFilter() {
        $organizations = DB::table('organizations')->where('is_main', '=', 0)->get();
        $departments = DB::table('departments')->get();
        $designations = DB::table('designations')->get();
        $sessions = DB::table('organization_session')->get();
        $employees = Employee::orderBy('id', 'desc')->get();
        return view('backend.hr.report.attendStatusFilter', compact('organizations', 'sessions', 'departments', 'designations', 'employees'));
    }

    public function attendStatusReport(Request $request) {

        $allStaffList = $this->getStaffList($request->att_date, $request->org_id, $request->department_id, $request->designation_id,
            $request->employee_id, $request->emp_type, $request->duty_shift,
            $request->abPrStatus, $request->record_status);// THIS IS WITH IN OUT TIME

        $allAttendList = $this->getAttendList($request->att_date, $request->org_id, $request->department_id, $request->designation_id,
            $request->employee_id, $request->emp_type, $request->duty_shift,
            $request->abPrStatus, $request->record_status);// THIS IS WITH PRESENT RECORD (ATT_STATUS)

        $i = 1;
        $stu = array();
        if ($allAttendList) {
            foreach ($allAttendList as $v) {
               $stu[$i] = $v->emp_id;
               $i++;
            }
        }
        $d = $employeeAttendanceStatusList = array();
        if($allStaffList)
        foreach ($allStaffList as $key => $val) {
            $employeeAttendanceStatusList[$key] = $val;
            if (array_search($val->emp_id,$stu)) {
                $employeeAttendanceStatusList[$key]->attStatusTxt = 'Present';
            } else {
                $employeeAttendanceStatusList[$key]->attStatusTxt = '<font style="color:red !important">Absent</font>';
            }
        }
        $data['allStaffList'] = $employeeAttendanceStatusList;
        $data['att_date'] = $att_date = $request->att_date;
        // $special_occasionDta = $this->db->query("SELECT atext as special_occasion FROM academic_special_days asd
        //     WHERE asd.adate = '$att_date' AND asd.approval_status = 2 LIMIT 1")->row();
        $data['special_occasion'] = $special_occasion = '';//($special_occasionDta) ? $special_occasionDta->special_occasion : '';


        ////////////// START FROM HERE

        $stitle = "Day's Attendance Status";
        $duty_shift = $request->duty_shift;
        $abPrStatus = $request->abPrStatus;
        $record_status = $request->record_status;
        $att_date = $request->att_date;
        $branch_name = $department_name = $designation_name = $session_name = '';
        if($request->org_id) {
            $branch = DB::table('organizations')->where('id', $request->org_id)->first();
            $branch_name = $branch ? $branch->name : '';
        }
        if($request->department_id) {
            $department = DB::table('departments')->where('department_id', $request->department_id)->first();
            $department_name = $department ? $department->department_name : '';
        }
        if($request->designation_id) {
            $designation = DB::table('designations')->where('id', $request->designation_id)->first();
            $designation_name = $designation ? $designation->name : '';
        }

        $aInfo = DB::table('organizations as org')->where('org.is_main', 1)->first();

        return view('backend.hr.report.attendStatusReport', compact('employeeAttendanceStatusList', 'aInfo', 'stitle', 'duty_shift',
        'abPrStatus', 'record_status', 'att_date', 'branch_name', 'department_name', 'designation_name', 'special_occasion'));
    }

    //($emp_type = 0, $att_date = '', $status = 'all')
    function getStaffList($att_date, $branch_id, $department_id, $designation_id, $employee_id, $emp_type, $duty_shift,
        $abPrStatus, $record_status) {

        // $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
        // $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
        // $sort = isset($_POST['sort']) ? strval($_POST['sort']) : '';
        // $order = isset($_POST['order']) ? strval($_POST['order']) : 'desc';
        // $offset = ($page - 1) * $rows;

        // if (!empty($sort)) {
        //     $orderVy = " ORDER BY ";
        //     $sortArr = explode(',', $sort);
        //     $orderArr = explode(',', $order);
        //     $j = 1;
        //     foreach ($sortArr as $i => $v) {
        //         if ($j == 1) {
        //             $orderVy .= " $sortArr[$i] $orderArr[$i]";
        //         } else {
        //             $orderVy .= ", $sortArr[$i] $orderArr[$i]";
        //         }
        //         $j++;
        //     }
        // } else {
            $orderVy = " ORDER BY a.employee_no ASC ";//a.post_level ASC,
        // }
        $sql = '';
        $sql .= ($branch_id) ? " AND ei.branch_id = '$branch_id'" : "";
        $sql .= ($department_id) ? " AND ei.department_id = '$department_id'" : "";
        $sql .= ($designation_id) ? " AND ei.designation_id = '$designation_id'" : "";
        $sql .= ($employee_id) ? " AND ei.id = '$employee_id'" : "";

        $sql .= ($duty_shift) ? " AND ei.duty_shift = '$duty_shift'" : "";
        $sql .= $record_status ? " AND ei.status = $record_status" : "";

        $joinSql = ($att_date) ? " AND att_date LIKE '$att_date%'" : " AND att_date LIKE '2019-01-01%'";

        $attDataaa = DB::select("SELECT a.*,
            CASE WHEN is_delay = '1' THEN 'Yes' ELSE ' ' END is_late
        FROM (
            SELECT
                ei.*, ei.id AS emp_id, dsg.name AS designation_name, sa.att_status,
                DATE_FORMAT(MIN(sa.att_date), '%H:%i') AS att_date,
                DATE_FORMAT(MAX(sa.att_date), '%H:%i') AS out_time,
                DATE_FORMAT(MIN(sa.att_date), '%H:%i') AS in_time,

                (SELECT IFNULL(MAX(is_delay), 0) AS is_delay FROM staff_attendance sa1
                WHERE is_delay = '1' AND sa1.reg_id = sa.reg_id AND SUBSTR(sa1.att_date, 1, 10) = SUBSTR(sa.att_date, 1, 10)
                ) AS is_delay

            FROM employees ei
                INNER JOIN designations dsg ON(dsg.id = ei.designation_id)
                INNER JOIN organizations o ON(o.id = ei.branch_id)
                LEFT JOIN staff_attendance sa ON sa.reg_id = ei.employee_no $joinSql
            WHERE 1 $sql GROUP BY ei.id
        ) a
        $orderVy");//LIMIT $offset, $rows

        return $attDataaa;
    }

    function getAttendList($att_date, $branch_id, $department_id, $designation_id, $employee_id, $emp_type, $duty_shift,
        $abPrStatus, $record_status) {

        $sql = '';
        $sql .= ($branch_id) ? " AND ei.branch_id = '$branch_id'" : "";
        $sql .= ($department_id) ? " AND ei.department_id = '$department_id'" : "";
        $sql .= ($designation_id) ? " AND ei.designation_id = '$designation_id'" : "";
        $sql .= ($employee_id) ? " AND ei.id = '$employee_id'" : "";

        $sql .= ($duty_shift) ? " AND ei.duty_shift = '$duty_shift'" : "";
        $sql .= $record_status ? " AND ei.status = $record_status" : "";

        $attDataaa = DB::select("SELECT ei.id AS emp_id
            FROM employees ei
                JOIN staff_attendance sa ON sa.`reg_id` = ei.`employee_no`
                JOIN designations dsg ON dsg.id = ei.designation_id
            WHERE sa.att_status = 1
                $sql AND sa.att_date LIKE '$att_date%'
            ORDER BY ei.employee_no ASC");

        return $attDataaa;
    }
}
