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

class Model_talent_referral extends CI_Model {

	function __construct()
	{
		parent::__construct();
	}

	public function getTalentReferralList($ID = null, $requester_id=null, $year = null)
	{
		$query = $this->db->select('a.*, o.name as candidate_entity, b.shortName as department_short_name, b.name as dept_name, c.name as branchName, d.name as requester_name, d.email as requester_email, d.staff_id as requester_staff_id, n.name as candidate_entity')
				->select('e.name as created_name, e.email as created_email, f.id as created_department_id, f.shortName as created_dept_short_name, f.name as created_dept_name, g.name as created_branch_name, n.name as created_entity')
				->select('h.name as processedBY_name, h.email as processedBY_email')
				->select('k.name as verifiedBY_name, k.email as verifiedBY_email')
                ->select('(select status from sv__approval where table_name="sv__talent_referral" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app')
                ->select('(select reason from sv__approval where table_name="sv__talent_referral" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
                ->join('company__department b', 'b.id = a.department_id','LEFT')
				->join('company__branch c', 'c.id = b.branch_id', 'LEFT')
				->join('user d', 'd.id = a.requester_id','LEFT')
				->join('user e', 'e.id = a.createdBY', 'LEFT')
				->join('company__department f', 'f.id = (select department_id from user__employment_detail where id = e.id)', 'LEFT')
				->join('company__branch g', 'g.id = f.branch_id', 'LEFT')
				->join('user h', 'h.id = a.processedBY', 'LEFT')
				->join('company__department i', 'i.id = (select department_id from user__employment_detail where id = h.id)', 'LEFT')
				->join('company__branch j', 'j.id = i.branch_id', 'LEFT')
				->join('user k', 'k.id = a.verifiedBY', 'LEFT')
				->join('company__department l', 'l.id = (select department_id from user__employment_detail where id = k.id)', 'LEFT')
				->join('company__branch m', 'm.id = l.branch_id', 'LEFT')
				->join('company__business_entity n', 'n.id = a.entity_id', 'LEFT')
				->join('company__business_entity o', 'o.id = a.candidate_entity_id', 'LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.ID desc');

        if($ID > 0)
        {
            $this->db->where('a.ID',$ID);
        }
                
        if(!empty($requester_id))
		{
			$this->db->where('(a.requester_id = '.$requester_id.' OR a.createdBY = '.$requester_id.')');
		}

		if($year > 0)
        {
            $this->db->where('year(a.createdTS)',$year);
        }

        $query = $this->db->get('sv__talent_referral a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }

    function getCandidateList($filters = array())
    {
    	$candidatelist = '';
    	$existingCandidate = $this->db->query('select candidate_user_id from sv__talent_referral where candidate_user_id > 0 and is_delete = 0')->result_array();

		if($existingCandidate) foreach($existingCandidate as $candidate)
		{
			$candidatelist .= $candidate['candidate_user_id'].',';
		}

		debug($candidatelist);

		$this->db->select('a.id, a.staff_id, a.name, ifnull(c.primary_email,ifnull(c.secondary_email, a.email)) as user_email, if(length(c.phone) > 0, c.phone, if(length(a.phone) > 0, a.phone, "N/A")) as user_phone, (select group_concat(candidate_user_id) from sv__talent_referral where is_delete = 0)')
			->select('c.date_join, c.action_efective_date, c.position_id, g.position, c.position_information as designation_id, rd.designation')
			->select('c.business_entity_id, f.name as entity_name, c.personnel_area_id, d.name as dept_name, e.name as branch_name')
    		->join('(select ujg.id, up.user_id, up.phone, up.primary_email, up.secondary_email, ujg.date_join, ujg.position_id, ujg.position_information, ujg.business_entity_id, ujg.personnel_area_id, ujg.action_efective_date, up.action_type_id, ujg.is_delete from view__user__joining_group ujg left join user__profiling up on up.id = ujg.profiling_id ) c','c.user_id = a.id and c.action_efective_date <= now() and c.is_delete = 0 and c.personnel_area_id > 0 and c.id = (select id from view__user__joining_group where user_id = a.id and action_efective_date <= now() and is_delete = 0 and personnel_area_id > 0 order by id desc limit 1)','left')
    		->join('company__department d','d.id = c.personnel_area_id','left')
    		->join('company__branch e','e.id = d.branch_id','left')
    		->join('company__business_entity f','f.id = c.business_entity_id','left')
    		->join('company__position g','g.id = c.position_id','left')
    		->join('_ref__designation rd','rd.id = c.position_information','left')
			->where('a.status',0)
			->where('a.is_delete',0)
			->order_by('a.name asc');
    	
		if($existingCandidate) $this->db->where('a.id not in ("'.$existingCandidate[0]['candidate_user_id'].'")');
			
    	if($filters) foreach($filters as $key => $filter)
    	{
    		if(!is_integer($key)) $this->db->where($key,$filter);
    		else $this->db->where($filter);
    	}

		$query = $this->db->get('user a');

		debug($this->db->last_query());
		debug($query->result_array());
		die();

		return ($query->num_rows() > 0) ? $query->result_array() : null;
    }
}
?>