<?php 
	/**
	* 
	*/
	class Model_profiling extends CI_Model
	{
		public function getLatestProfile($user_id)
		{
			$this->db->select('a.*,b.country as country_issue');
			$this->db->where('a.is_delete',0);
			$this->db->where('a.user_id',$user_id);

			$this->db->join('_ref__country b','b.id = a.place_issue','left');
			$this->db->order_by('a.action_efective_date ,a.id','desc');
			$query = $this->db->get('user__profiling a');

			return ($query->num_rows() > 0)?$query->result()[0]:null;
		}

		public function getPicture($id)
		{
			$this->db->select('file_location,file_name');
			$this->db->where('id',$id);
			$query = $this->db->get('user__picture');
			if ($query->num_rows() > 0) {
				$result = $query->result()[0];
				return base_url($result->file_location.'/'.$result->file_name);
			}else{
				return base_url('assets/themes/web/img/users.png');
			}
		}

		public function getConfirmDate()
		{
			$this->db->select('a.*, b.name, c.department_id, c.date_joined, d.name as department_name');
			$this->db->where('a.is_delete',0);
			$this->db->where_in('a.confirmation',['1','2']);

			$this->db->join('user b','b.id = a.user_id','left');
			$this->db->join('user__employment_detail c','c.user_id = b.id','left');
			$this->db->join('company__department d','d.id = c.department_id','left');

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

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

		public function getIncompleteDetailList($mode)
		{
			if($mode == 'incomplete')
			{
				$this->db->where('e.status',0)
						->or_where('e.status IS NULL');
			}
			if($mode == 'complete')
			{
				$this->db->where('e.status',1);
			}

			$query = $this->db->select('a.*, b.family_info, b.academic_qualification, c.previous_employment, c.contract_element, c.recognition, c.membership, c.license, d.basic_pay, d.bank_detail, d.statutory_detail, d.recurring, d.third_party_payment, d.user_pcb, e.status as profiling_status')
							->join('user__background_detail_incomplete_list b','b.user_id = a.user_id','left')
							->join('user__professional_detail_incomplete_list c','c.user_id = a.user_id','left')
							->join('user__financial_detail_incomplete_list d','d.user_id = a.user_id','left')
							->join('user__profiling_status e','e.user_id = a.user_id and e.is_delete = 0','left')
							->order_by('a.user_id ASC')
							->get('user__employment_incomplete_list a');

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

		public function countIncompleteDetailList($mode = null, $user_id = null)
		{
			if($mode)
			{	
				if($mode == 'incomplete')
				{
					$this->db->where('b.status',0)
							->or_where('b.status IS NULL');
				}
				if($mode == 'complete')
				{
					$this->db->where('b.status',1);
				}

				$query = $this->db->select('count(distinct a.user_id) as total')
								->join('user__profiling_status b','b.user_id = a.user_id and b.is_delete = 0','LEFT')
								->get('user__employment_incomplete_list a');
			}else{
				$query = $this->db->where('is_delete',0)
						->where('user_id',$user_id)
						->where('action_type_id !=',10000)
						->get('user__profiling_status');	
			}

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

		public function update_profile_list($mode = null, $date = null, $user_id = null)
		{
			if($user_id) $this->db->where('user_id',$user_id);
			if($mode)
			{	
				if($mode == 0)
				{
					$this->db->where('latest_profiling_status',0)
							->or_where('latest_profiling_status IS NULL');
				}
				if($mode == 1)
				{
					$this->db->where('latest_profiling_status',1);
				}

				$query = $this->db->where('YEAR(latest_date_submit)',date('Y',strtotime($date)))
							->where('MONTH(latest_date_submit)',date('m',strtotime($date)))
							->get('user__update_profile');
			}else{
				$query = $this->db->get('user__update_profile');
			}

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

		public function get_attachment_category($main_id)
		{
			$this->db->where('main_id', $main_id);
			$this->db->where('is_delete', 0);

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

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

		function getProfilingAttachments($staff_id, $category_id = null)
		{
			$this->db->select('a.*, b.description as category_desc');
			$this->db->join('hr__profile_category b','b.id = a.category_id','left outer');
			$this->db->where('a.is_delete', 0);
			$this->db->where('a.staff_id', $staff_id);

			if($category_id)$this->db->where('a.category_id IN (SELECT id FROM hr__profile_category WHERE main_id = '.$category_id.')');

			$query = $this->db->get('hr__profile_attachment a');
			          
			return ($query->num_rows() > 0) ? $query->result_array():null;
		}

		public function getProfilingAttachments_all($staff_id)
		{
			$query = $this->db->query("SELECT attachment_name, location, 'Academic Education' AS type, mask_id  FROM user__academic_qualification_attachment WHERE user_id = ".$staff_id." AND is_delete = 0 AND academic_qualification_id IN (SELECT id FROM user__academic_qualification WHERE profiling_id IN (SELECT id FROM user__profiling WHERE is_delete = 0) AND is_delete = 0)
			UNION
			SELECT attachment_name, location, 'Professional Membership' as type, mask_id FROM user__profesional_membership_attachment WHERE user_id = ".$staff_id." AND is_delete = 0 AND professional_membership_id IN (SELECT id FROM user__profesional_membership WHERE profiling_id IN (SELECT id FROM user__profiling WHERE is_delete = 0) AND is_delete = 0)
			UNION
			SELECT attachment_name, location, 'Professional License' as type, mask_id FROM user__license_attachment WHERE user_id = ".$staff_id." AND is_delete = 0 AND license_id IN (SELECT id FROM user__license WHERE profiling_id IN (SELECT id FROM user__profiling WHERE is_delete = 0) AND is_delete = 0)
			UNION
			SELECT a.attachment_name, a.location, if(b.type = 0, 'Professional Education', 'Professional Certificate') as type, mask_id 
			FROM user__professional_academic_attachment a 
			LEFT OUTER JOIN user__professional_academic b ON b.id = a.professional_academic_id 
			WHERE a.user_id = ".$staff_id." AND a.is_delete = 0 AND b.is_delete = 0 AND b.profiling_id IN (SELECT id FROM user__profiling WHERE is_delete = 0)
			UNION
			SELECT attachment_name, location, 'Recognition' as type, mask_id  FROM user__recognition_attachment WHERE user_id = ".$staff_id." AND is_delete = 0 AND recognition_id IN (SELECT id FROM user__recognition WHERE profiling_id IN (SELECT id FROM user__profiling WHERE is_delete = 0) AND is_delete = 0)");

// echo $this->db->last_query();
			return ($query->num_rows() > 0)?$query->result_array():null;
		}

		public function getLastWorkingDate()
		{
			$query = $this->db->query("SELECT a.*, b.name, c.department_id 
						FROM user__contract_element a
						LEFT JOIN user b ON b.id = a.user_id
						LEFT JOIN user__employment_detail c ON c.user_id = b.id AND c.is_delete = 0 AND c.status = 0
						WHERE a.is_delete = 0
						AND b.is_delete = 0
						AND c.is_delete = 0 
						AND c.status = 0
						AND a.profiling_id IN (SELECT MAX(profiling_id) FROM user__contract_element WHERE is_delete = 0 GROUP BY user_id)");

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

		public function auto_delete_staff($user_id)
		{
			$query = $this->db->query("UPDATE user SET is_delete = 1 WHERE id = '".$user_id."'");
		}
	}




?>