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

class Selection_model extends CI_Model
{


    public function viewShortlist()
    {

        return $this->db->select('count(DISTINCT(stl.can_short_id)) as can_short_id,stl.*,bi.can_id,bi.firstname,bi.lastname,bi.email,po.position_name,eh.first_name,eh.last_name')
            ->from('candidate_shortlist stl')
            ->join('candidate_basic_info bi', 'stl.can_id = bi.can_id', 'left')
            ->join('position po', 'stl.job_adv_id = po.pos_id', 'left')
            ->join('employee_history eh', 'stl.shortlisted_by = eh.employee_id', 'left')
            ->where('can_type',2)
            ->group_by('stl.can_short_id')
            ->order_by('stl.can_short_id', 'desc')
            ->get()
            ->result();
    }

    public function internViewShortlist()
    {

        return $this->db->select('count(DISTINCT(stl.can_short_id)) as can_short_id,stl.*,bi.can_id,bi.firstname,bi.lastname,bi.email,po.position_name,dt.department_name,eh.first_name,eh.last_name')
            ->from('candidate_shortlist stl')
            ->join('candidate_basic_info bi', 'stl.can_id = bi.can_id', 'left')
            ->join('position po', 'stl.job_adv_id = po.pos_id', 'left')
            ->join('department dt', 'stl.dept_id = dt.dept_id', 'left')
            ->join('employee_history eh', 'stl.shortlisted_by = eh.employee_id', 'left')
            ->where('can_type',1)
            ->group_by('stl.can_short_id')
            ->order_by('stl.can_short_id', 'desc')
            ->get()
            ->result();
    }

    public function viewFinalShortlist()
    {

        return $this->db->select('count(DISTINCT(stl.can_short_id)) as can_short_id,stl.*,bi.can_id,bi.firstname,bi.lastname,po.position_name,eh.first_name,eh.last_name')
            ->from('candidate_final_shortlist stl')
            ->join('candidate_basic_info bi', 'stl.can_id = bi.can_id', 'left')
            ->join('position po', 'stl.job_adv_id = po.pos_id', 'left')
            ->join('employee_history eh', 'stl.shortlisted_by = eh.employee_id', 'left')
            ->group_by('stl.can_short_id')
            ->order_by('stl.can_short_id', 'desc')
            ->get()
            ->result();
    }

    public function dropdownfinalcanid()
    {
        $this->db->select('a.*,b.firstname,b.lastname');
        $this->db->from('candidate_interview a');
        $this->db->where('selection', 'ok');
        $this->db->join('candidate_basic_info b', 'a.can_id=b.can_id');
        $query = $this->db->get();
        $data = $query->result();
        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;
    }

    public function shortlist_create($data = array())
    {
        return $this->db->insert('candidate_shortlist', $data);
    }

    public function final_shortlist_create($data = array())
    {
        return $this->db->insert('candidate_final_shortlist', $data);
    }

    public function update_shortlist($data = array())
    {
        return $this->db->where('can_short_id', $data["can_short_id"])
            ->update("candidate_shortlist", $data);
    }

    public function update_final_shortlist($data = array())
    {
        return $this->db->where('can_short_id', $data["can_short_id"])
            ->update("candidate_final_shortlist", $data);
    }

    public function shortlist_updateForm($id)
    {
        $this->db->where('can_short_id', $id);
        $query = $this->db->get('candidate_shortlist');
        return $query->row();
    }

    public function shortlist_final_updateForm($id)
    {
        $this->db->where('can_short_id', $id);
        $query = $this->db->get('candidate_final_shortlist');
        return $query->row();
    }

    public function delete_shorlist($id = null)
    {
        $candata = $this->db->select('*')->from('candidate_shortlist')->where('can_short_id', $id)->get()->row();
        $this->db->where('can_id', $candata->can_id)
            ->delete('candidate_interview');
        $this->db->where('can_id', $candata->can_id)
            ->delete('candidate_selection');
        $this->db->where('can_short_id', $id)
            ->delete('candidate_shortlist');

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function delete_final_shortlist($id = null)
    {
        $this->db->where('can_short_id', $id)
            ->delete('candidate_final_shortlist');

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function dropdownPosition()
    {
        $this->db->select('*');
        $this->db->from('candidate_shortlist');
        $query = $this->db->get();
        $data = $query->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->job_adv_id] = $value->job_adv_id;
            }
        }
        return $list;
    }

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

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->pos_id] = $value->position_name;
            }
        }
        return $list;
    }

    public function dropdowncanid()
    {
        $this->db->select('*');
        $this->db->from('candidate_basic_info');
        $this->db->where('can_type',2);
        $query = $this->db->get();
        $data = $query->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . "(" . $value->can_id . ")";
            }
        }
        return $list;
    }

    public function interndropdowncanid()
    {
        $this->db->select('*');
        $this->db->from('candidate_basic_info');
        $this->db->where('can_type',1);
        $query = $this->db->get();
        $data = $query->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . "(" . $value->can_id . ")";
            }
        }
        return $list;
    }


    /*##########################Interview Part ################*/

    public function viewInterview()
    {
        return $this->db->select('count(DISTINCT(int.can_int_id)) as can_int_id,int.*,bi.can_id,bi.firstname,bi.lastname,po.position_name')
            ->from('candidate_interview int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->group_by('int.can_int_id')
            ->order_by('int.can_int_id', 'desc')
            ->get()
            ->result();
    }

    public function getLatestCandidate()
    {
        return $this->db->select('count(DISTINCT(int.can_int_id)) as can_int_id,int.*,bi.can_id,bi.firstname,bi.lastname,bi.email,po.position_name')
            ->from('candidate_interview int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->group_by('int.can_int_id')
            ->order_by('int.can_int_id', 'desc')
            ->limit(1)
            ->get()
            ->result();
    }

    public function getLatestCandidateFinal()
    {
        return $this->db->select('count(DISTINCT(int.can_int_id)) as can_int_id,int.*,bi.can_id,bi.firstname,bi.lastname,bi.email,po.position_name')
            ->from('final_candidate_interview int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->group_by('int.can_int_id')
            ->order_by('int.can_int_id', 'desc')
            ->limit(1)
            ->get()
            ->result();
    }

    public function viewFinalInterview()
    {
        return $this->db->select('count(DISTINCT(int.can_int_id)) as can_int_id,int.*,bi.can_id,bi.firstname,bi.lastname,po.position_name')
            ->from('final_candidate_interview int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->group_by('int.can_int_id')
            ->order_by('int.can_int_id', 'desc')
            ->get()
            ->result();
    }

    public function viewFinalManagerInterview($id)
    {
        return $this->db->select('int.*,cf.*,bi.can_id,bi.firstname,bi.lastname,po.position_name')
            ->from('candidate_final_shortlist int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->join('final_candidate_interview cf', 'int.can_id = cf.can_id', 'left')
            ->where('int.manager',$id)
            ->get()
            ->result();
    }

    public function interview_create($data = array())
    {
        return $this->db->insert('candidate_interview', $data);
    }

    public function final_interview_create($data = array())
    {
        return $this->db->insert('final_candidate_interview', $data);
    }

    public function get_role($user_id)
    {
        return $this->db->select('*')
            ->from('sec_user_access_tbl')
            ->where('fk_user_id',$user_id)
            ->get()
            ->result_array();
    }

    public function delete_interview($id = null)
    {
        $candata = $this->db->select('*')->from('candidate_interview')->where('can_int_id', $id)->get()->row();

        $this->db->where('can_id', $candata->can_id)
            ->delete('candidate_selection');
        $this->db->where('can_int_id', $id)
            ->delete('candidate_interview');

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function final_delete_interview($id = null)
    {
        $candata = $this->db->select('*')->from('final_candidate_interview')->where('can_int_id', $id)->get()->row();

        $this->db->where('can_id', $candata->can_id)
            ->delete('final_candidate_interview');
        $this->db->where('can_int_id', $id)
            ->delete('final_candidate_interview');

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function update_interview($data = array())
    {
        return $this->db->where('can_int_id', $data["can_int_id"])
            ->update("candidate_interview", $data);
    }

    public function update_final_interview($data = array())
    {
        return $this->db->where('can_int_id', $data["can_int_id"])
            ->update("final_candidate_interview", $data);
    }

    public function interview_updateForm($id)
    {
        return $caninterviewInfo = $this->db->select('a.*,b.position_name')->from('candidate_interview a')->join('position b', 'a.job_adv_id=b.pos_id')->where('a.can_int_id', $id)->get()->row();
    }

    public function interview_final_updateForm($id)
    {
        return $caninterviewInfo = $this->db->select('a.*,b.position_name')->from('final_candidate_interview a')->join('position b', 'a.job_adv_id=b.pos_id')->where('a.can_int_id', $id)->get()->row();
    }

    /* #################### Selection Part  #######################*/
    public function viewSelection()
    {

        return $this->db->select('count(DISTINCT(sel.can_sel_id)) as can_sel_id,sel.*,bi.can_id,bi.firstname,bi.lastname')
            ->from('candidate_selection sel')
            ->join('candidate_basic_info bi', 'sel.can_id = bi.can_id', 'left')
            ->group_by('sel.can_sel_id')
            ->order_by('sel.can_sel_id', 'desc')
            ->get()
            ->result();
    }

    public function selection_create($data = array())
    {
        return $this->db->insert('candidate_selection', $data);
    }

    public function selection_delete($id = null)
    {
        $this->db->where('can_sel_id', $id)
            ->delete('candidate_selection');

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function update_selection($data = array())
    {
        return $this->db->where('can_sel_id', $data["can_sel_id"])
            ->update("candidate_selection", $data);
    }

    public function selection_updateForm($id)
    {
        $this->db->where('can_sel_id', $id);
        return $query = $this->db->select('a.*,b.position_name')->from('candidate_selection a')->join('position b', 'a.pos_id=b.pos_id')->where('a.can_sel_id', $id)->get()->row();
    }

    public function dropdowninterview()
    {
        $this->db->select('a.*,b.firstname,b.lastname');
        $this->db->from('candidate_shortlist a');
        $this->db->join('candidate_basic_info b', 'a.can_id=b.can_id');
        $query = $this->db->get();
        $data = $query->result();
        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;
    }

    public function finaldropdowninterview()
    {
        $this->db->select('a.*,b.firstname,b.lastname,b.email');
        $this->db->from('candidate_final_shortlist a');
        $this->db->join('candidate_basic_info b', 'a.can_id=b.can_id');
        $query = $this->db->get();
        $data = $query->result();
        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;
    }

    public function finaldropdowninterviewmanager($id)
    {
        $this->db->select('a.*,b.firstname,b.lastname,b.email');
        $this->db->from('candidate_final_shortlist a');
        $this->db->join('candidate_basic_info b', 'a.can_id=b.can_id');
        $this->db->where('a.manager', $id);
        $query = $this->db->get();
        $data = $query->result();
        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;
    }

    public function selected_candidate($id)
    {
        return $this->db->select('count(DISTINCT(int.can_int_id)) as can_int_id,int.*,bi.can_id,bi.firstname,bi.lastname,bi.email,po.position_name')
            ->from('final_candidate_interview int')
            ->join('candidate_basic_info bi', 'int.can_id = bi.can_id', 'left')
            ->join('position po', 'int.job_adv_id = po.pos_id', 'left')
            ->where('can_int_id', $id)
            ->group_by('int.can_int_id')
            ->order_by('int.can_int_id', 'desc')
            ->get()
            ->result();
    }

    public function update_email_flag($data = array())
    {
        return $this->db->where('can_int_id', $data["can_int_id"])
            ->update("final_candidate_interview", $data);
    }

    public function dropdownselection()
    {
        $data = $this->db->select('a.*,b.firstname,b.lastname')->from('candidate_interview a')->join('candidate_basic_info b', 'a.can_id=b.can_id')->where('a.selection', 'ok')->get()->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;
    }


    /* selected dropdown position */
    public function selected()
    {
        $query = $this->db->get_where('candidate_interview', array('selection' => "ok"));

        $data = $query->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->job_adv_id] = $value->job_adv_id;
            }
        }
        return $list;
    }

    /*  ################ selected auto ######################  */

    public function insert_employee($data = array())
    {
        return $this->db->insert('employee_history', $data);
    }

    public function Canlist()
    {
        $this->db->select("can_id,CONCAT_WS(' ',firstname,lastname) AS name");
        $this->db->from('candidate_basic_info');
        $query = $this->db->get();
        $data = $query->result();

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->name;
            }
        }
        return $list;

    }

    public function FinalCanlist()
    {
        $this->db->select('a.*,b.firstname,b.lastname');
        $this->db->from('candidate_interview a');
        $this->db->where('selection', 'ok');
        $this->db->join('candidate_basic_info b', 'a.can_id=b.can_id');
        $query = $this->db->get();
        $data = $query->result();
        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->can_id] = $value->firstname . ' ' . $value->lastname;
            }
        }
        return $list;

    }

    public function headcode()
    {
        $query = $this->db->query("SELECT MAX(HeadCode) as HeadCode FROM acc_coa WHERE HeadLevel='2' And HeadCode LIKE '502020000%'");
        return $query->row();

    }

    public function create_coa($data = [])
    {
        $this->db->insert('acc_coa', $data);
        return true;
    }

    public function insert_assessment($data = [])
    {
        $this->db->insert('candidate_assessment', $data);
        return true;
    }

    public function update_assessment_email_flag($data = array())
    {
        return $this->db->where('can_id', $data["can_id"])
            ->update("candidate_shortlist", $data);
    }

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

        if (!empty($data)) {
            return $data;
        } else {
            return false;
        }
    }

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

        $list = array('' => 'Select One...');
        if (!empty($data)) {
            foreach ($data as $value) {
                $list[$value->dept_id] = $value->department_name;
            }
        }
        return $list;
    }
}
