<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Report_model extends CI_Model
{

    public function demographic_list($limit = null, $start = null, $id = null)
    {

        $this->db->select('p.*,d.department_name,po.position_name,dt.type_name,rt.r_type_name as rd_type,gd.gender_name,ms.marital_sta,pf.frequency_name,sp.first_name as f_name,sp.last_name as l_name');
        $this->db->from('employee_history p');
        $this->db->join('department d', 'p.dept_id = d.dept_id', 'left');
        $this->db->join('position po', 'p.pos_id = po.pos_id', 'left');
        $this->db->join('duty_type dt', 'p.duty_type = dt.id', 'left');
        $this->db->join('rate_type rt', 'p.rate_type = rt.id', 'left');
        $this->db->join('gender gd', 'p.gender = gd.id', 'left');
        $this->db->join('marital_info ms', 'p.marital_status = ms.id', 'left');
        $this->db->join('pay_frequency pf', 'p.pay_frequency = pf.id', 'left');
        $this->db->join('employee_history sp', 'p.super_visor_id = sp.emp_his_id', 'left');
        if (!empty($id)) {
            $this->db->where('p.emp_his_id', $id);
        }
        $this->db->limit($limit, $start);
        $this->db->order_by('p.emp_his_id', 'desc');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return false;
    }

    public function count_demographic($id = null)
    {
        $this->db->select('*');
        $this->db->from('employee_history');
        if (!empty($id)) {
            $this->db->where('emp_his_id', $id);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    public function dropdownemp()
    {
        $this->db->select('*');
        $this->db->from('employee_history');
        $this->db->where('employee_status', 1);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function equipment_maping_report($limit = null, $start = null, $id = null)
    {
        $this->db->select('a.*,b.*,c.type_name,em.first_name,em.last_name');
        $this->db->from('employee_equipment a');
        $this->db->join('employee_history em', 'a.employee_id = em.employee_id', 'left');
        $this->db->join('equipment b', 'a.equipment_id = b.equipment_id', 'left');
        $this->db->join('equipment_type c', 'b.type_id = c.type_id');
        if (!empty($id)) {
            $this->db->where('em.employee_id', $id);
        }
        $this->db->order_by('a.equipment_id', 'desc');
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return false;
    }

    public function employee_drop()
    {
        $data = $this->db->select("*")
            ->from('employee_history')
            ->where('employee_status', 1)
            ->get()
            ->result();

        $list[''] = display('select_type');
        if (!empty($data)) {
            foreach ($data as $value)
                $list[$value->employee_id] = $value->first_name . ' ' . $value->last_name;
            return $list;
        } else {
            return false;
        }
    }

// equipmnet count
    public function count_equipment($id = null)
    {
        $this->db->select('a.*,b.*,c.type_name,em.first_name,em.last_name');
        $this->db->from('employee_equipment a');
        $this->db->join('employee_history em', 'a.employee_id = em.employee_id', 'left');
        $this->db->join('equipment b', 'a.equipment_id = b.equipment_id', 'left');
        $this->db->join('equipment_type c', 'b.type_id = c.type_id');
        if (!empty($id)) {
            $this->db->where('em.employee_id', $id);
        }
        $this->db->order_by('a.equipment_id', 'desc');
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    // custom information
    public function custom_report($limit = null, $start = null, $id = null)
    {
        $this->db->select('a.*,b.first_name,b.last_name');
        $this->db->from('custom_table a');
        $this->db->join('employee_history b', 'a.employee_id = b.employee_id', 'left');
        if (!empty($id)) {
            $this->db->where('b.employee_id', $id);
        }
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return false;
    }

//count custom information
    public function count_custom_data($id = null)
    {
        $this->db->select('a.*,b.first_name,b.last_name');
        $this->db->from('custom_table a');
        $this->db->join('employee_history b', 'a.employee_id = b.employee_id', 'left');
        if (!empty($id)) {
            $this->db->where('b.employee_id', $id);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    //Get all departments

    public function dropdowndept()
    {
        $this->db->select('*');
        $this->db->from('department');
        $this->db->where('parent_id', 0);
        $query = $this->db->get();
        return $query->result_array();

    }

    //Emplyee search
    public function employee_search($dpt)
    {
        return $this->db->select('*')
            ->from('employee_history')
            ->where('dept_id', $dpt)
            ->order_by('emp_his_id', 'desc')
            ->get()
            ->result();
    }

    public function employee_search_by_birthday()
    {
        return $this->db->select('*')
            ->from('employee_history')
            ->order_by('emp_his_id', 'desc')
            ->get()
            ->result();
    }

    //    Inam Changing

    public function designation()
    {
        $this->db->select('*');
        $this->db->from('position');
        $query = $this->db->get();
        return $data = $query->result();
    }

    public function absent_summary($start_date, $to_date, $work_location, $department, $duty_types, $employees_status, $employees)
    {
        $Q1 = '( SELECT SUM(apply_day) FROM leave_apply AS t2
             where t2.employee_id = la.employee_id
             AND t2.status = 1
             AND t2.apply_strt_date BETWEEN "' . $start_date . '"
             AND "' . $to_date . '")';

        $Q2 = '( SELECT SUM(apply_day) FROM leave_apply AS t2 
            where t2.employee_id = la.employee_id 
            AND t2.status != 1 
            AND t2.apply_strt_date BETWEEN "' . $start_date . '" 
            AND "' . $to_date . '")';

        $this->db->select($Q1 . 'AS approved,' . $Q2 . ' AS unapproved, COUNT(aa.check_in_date) as presentstotal, la.*,eh.first_name,eh.last_name,d.department_name');
        $this->db->from('leave_apply la');
        $this->db->join('employee_history eh', 'la.employee_id = eh.employee_id', 'left');
        $this->db->join('department d', 'eh.dept_id = d.dept_id', 'left');
        $this->db->join('employee_attendance aa', 'eh.employee_id = aa.employee_id', 'left');

        if ($department != 0) {
            $this->db->where('eh.dept_id', $department);
        }
        if ($work_location != "All") {
            $this->db->where('eh.state', $work_location);
        }
        if ($duty_types != 0) {
            $this->db->where('eh.duty_type', $duty_types);
        }
        if ($employees_status != 5) {
            $this->db->where('eh.employee_status', $employees_status);
        }
        if ($employees != 0) {
            $this->db->where('eh.employee_id', $employees);
        }

        $this->db->where('la.apply_strt_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"');
        $this->db->where('aa.check_in_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"');
        $this->db->group_by('la.employee_id');

        $query = $this->db->get();
        return $query->result();
    }

    public function attendance_summary($start_date, $to_date, $work_location, $department, $duty_types, $employees_status, $employees)
    {
        if ($duty_types != 0) {
            $dyty_column_name = ', t5.type_name';
            $duty_table_name = ', `duty_type` as t5 ';
        }

        $this->db->select("t1.* ,
                (SELECT CONCAT(t2.first_name, ' ', t2.last_name)
                 FROM `employee_history` AS t2
                 WHERE t2.employee_id = t1.employee_id
                    ) AS 'employee_name' ,t3.dept_id , t4.department_name,t3.state, t3.employee_status " . $dyty_column_name . "");
        $this->db->from('`employee_attendance` AS t1, `employee_history` AS t3 , `department` AS t4 ' . $duty_table_name . '');
        $this->db->where('t1.employee_id = t3.employee_id');
        $this->db->where('t3.dept_id = t4.dept_id');
        if ($duty_types != 0) {
            $this->db->where('t3.duty_type = t5.id');
        }
        if ($department != 0) {
            $this->db->where('t3.dept_id', $department);
        }
        if ($work_location != "All") {
            $this->db->where('t3.state', $work_location);
        }
        if ($duty_types != 0) {
            $this->db->where('t3.duty_type', $duty_types);
        }
        if ($employees_status != "") {
            $this->db->where('t3.employee_status', $employees_status);
        }
        if ($employees != 0) {
            $this->db->where('t3.employee_id', $employees);
        }
        $this->db->where('t1.check_in_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"');
        $this->db->order_by('t1.employee_id', 'DESC');
        $this->db->order_by('t1.check_in_date', 'ASC');

        $query = $this->db->get();
        return $query->result();
    }

    public function leave_detail()
    {
        return $this->db->select('*')
            ->from('leave_apply')
//        ->where('t1.check_in_date BETWEEN "'.$start_date.'" AND "'.$to_date.'"')
            ->order_by('employee_id', 'ASC')
            ->order_by('apply_strt_date', 'ASC')
            ->where('status', 1)
            ->get()
            ->result();
    }

    public function remaining_emp($start_date, $to_date, $department, $work_location, $duty_types, $employees_status, $employees)
    {

        if ($duty_types != 0) {
            $column_name = ', t3.type_name';
            $table_name = ', `duty_type` as t3 ';
        }

        $this->db->select('t1.employee_id , CONCAT(t1.first_name, " ", t1.last_name) AS employee_name ,t2.department_name, t1.state' . $column_name . '');
        $this->db->from('employee_history as t1 , department as t2 ' . $table_name . '');

        $this->db->where('t1.dept_id = t2.dept_id');
        $this->db->where('t1.employee_id NOT IN (SELECT DISTINCT employee_id
            from employee_attendance
            where check_in_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"
            )');
        if ($duty_types != 0) {
            $this->db->where('t1.duty_type = t3.id');
            echo "<script> alert('Duty') </script>";
        }
        if ($department != 0) {
            echo "<script> alert('Department') </script>";
            $this->db->where('t1.dept_id', $department);
        }
        if ($work_location != "All") {
            echo "<script> alert('Location') </script>";
            $this->db->where('t1.state', $work_location);
        }
        if ($duty_types != 0) {
            echo "<script> alert('Duty 2') </script>";
            $this->db->where('t1.duty_type', $duty_types);
        }
        if ($employees_status != "") {
            echo "<script> alert('Status') </script>";
            $this->db->where('t1.employee_status', $employees_status);
        }
        if ($employees != 0) {
            echo "<script> alert('employees') </script>";
            $this->db->where('t1.employee_id', $employees);
        }
        $query = $this->db->get();
        return $query->result();
    }


    public function total_working_hr($start_date, $to_date)
    {
        return $this->db->select('t1.employee_id , SUM( TRUNCATE(t1.total_hours, 0)) +
        COALESCE((SELECT SUM(TRUNCATE(t2.total_time, 0))
                FROM employee_overtime as t2
                WHERE t2.employee_id = t1.employee_id
                AND t2.ovt_start_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"
                GROUP BY t2.employee_id ),0) as "over_time"')
            ->from('employee_attendance AS t1')
            ->where('t1.check_in_date BETWEEN "' . $start_date . '" AND "' . $to_date . '"')
            //            ->where('employee_id NOT IN (SELECT DISTINCT employee_id from employee_attendance)')
            ->group_by('t1.employee_id')
            ->get()
            ->result();
//WHERE t1.check_in_date BETWEEN "2022-01-9" AND "2022-02-27"
    }

    public function duty_type()
    {
        $this->db->select('*');
        $this->db->from('duty_type');
        $query = $this->db->get();
        return $data = $query->result_array();
    }

    public function get_atten_record($from_date, $to_date)
    {
        return $this->db->select("ea.*,eh.first_name,eh.last_name")
            ->from('employee_attendance ea')
            ->where('check_in_date BETWEEN "' . $from_date . '" AND "' . $to_date . '"')
            ->join('employee_history eh', 'ea.employee_id = eh.employee_id', 'left')
            ->order_by('ea.employee_id', 'desc')
            ->group_by('ea.employee_id')
            ->get()
            ->result();
    }

    public function get_emp_ids()
    {
        return $this->db->select("eh.employee_id,eh.first_name,eh.last_name,d.department_name")
            ->from('employee_history eh')
            ->join('department d', 'eh.dept_id = d.dept_id', 'left')
            ->order_by('eh.employee_id', 'desc')
            ->get()
            ->result();
    }

    public function get_absent_filter_emp_id($employee_id, $dept_id, $duty_type, $employee_status, $state)
    {
        $this->db->select("eh.employee_id,eh.first_name,eh.last_name,d.department_name");
        $this->db->from('employee_history eh');
        $this->db->join('department d', 'eh.dept_id = d.dept_id', 'left');
        if (!empty($employee_id)) {
            $this->db->where('eh.employee_id', $employee_id);
        }
        if (!empty($dept_id)) {
            $this->db->where('eh.dept_id', $dept_id);
        }
        if (!empty($duty_type)) {
            $this->db->where('eh.duty_type', $duty_type);
        }
        if (!empty($employee_status)) {
            $this->db->where('eh.employee_status', $employee_status);
        }
        if (!empty($state)) {
            $this->db->where('eh.state', $state);
        }
        $query = $this->db->get();
        return $data = $query->result();
    }

    public function get_filter_emp_id($employee_id, $dept_id, $duty_type, $employee_status, $state)
    {
        $this->db->select("employee_id,first_name,last_name");
        $this->db->from('employee_history');
        if (!empty($employee_id)) {
            $this->db->where('employee_id', $employee_id);
        }
        if (!empty($dept_id)) {
            $this->db->where('dept_id', $dept_id);
        }
        if (!empty($duty_type)) {
            $this->db->where('duty_type', $duty_type);
        }
        if (!empty($employee_status)) {
            $this->db->where('employee_status', $employee_status);
        }
        if (!empty($state)) {
            $this->db->where('state', $state);
        }
        $query = $this->db->get();
        return $data = $query->result();
    }

    public function check_attendance($emp_id, $date)
    {
        $this->db->select("*")
            ->from('employee_attendance')
            ->where('employee_id', $emp_id)
            ->where('check_in_date', $date);
        $query = $this->db->get();
        return $query->result();
    }

    public function check_leave($emp_id, $date)
    {
        $this->db->select("*")
            ->from('leave_apply')
            ->where('employee_id', $emp_id)
            ->where('apply_strt_date', $date)
            ->where('status', 1);
        $query = $this->db->get();
        return $query->result();
    }

    public function check_unapproved_leave($emp_id, $date)
    {
        $this->db->select("*")
            ->from('leave_apply')
            ->where('employee_id', $emp_id)
            ->where('apply_strt_date', $date)
            ->where('status', 0);
        $query = $this->db->get();
        return $query->result();
    }

    public function get_dept_name($dept_id)
    {
        $this->db->select("department_name")
            ->from('department')
            ->where('dept_id', $dept_id);
        $query = $this->db->get();
        return $query->result();
    }
}
