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

class Model_academic_qualification extends CI_Model {

	public function fetchQualification($staff_id = null, $latest = false,$profiling_id = null, $qualification_id = null)
	{	
		//inner query
		$this->db->select('MAX(a.profiling_id)');
		$this->db->from('user__academic_qualification a');
		if($staff_id) $this->db->where('a.user_id',$staff_id);
		$this->db->where('a.is_delete',0);
		$max = $this->db->get_compiled_select();

		//outer query
		$this->db->select('a.*,
							b.level as academic_level,
							group_concat(c.id separator "->") as attachment_id, group_concat(c.attachment_name separator "->") as attachment_name, group_concat(c.location separator "->") as location, group_concat(c.file_type separator "->") as file_type, group_concat(c.file_ext separator "->") as file_ext, group_concat(c.mask_id separator "->") as mask_id,
							d.action_efective_date,
							e.type as action_type');

		if($staff_id) $this->db->where('a.user_id',$staff_id);
		$this->db->where('a.is_delete',0);
		if($latest) $this->db->where("a.profiling_id IN ($max)",'',false);
		if($profiling_id) $this->db->where('a.profiling_id',$profiling_id);
		if($qualification_id) $this->db->where('a.id',$qualification_id);
		$this->db->join('_ref__academic b','b.id = a.level_id');
		$this->db->join('user__academic_qualification_attachment c','c.academic_qualification_id = a.id and c.is_delete = 0','left outer');
		$this->db->join('user__profiling d','d.id = a.profiling_id','left');
		$this->db->join('_ref__action_type e','e.id = d.action_type_id','left');
		$this->db->group_by('a.id');

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

	public function createQualification($input)
	{
		$data['profiling_id'] 		= $input['profiling_id'];
		$data['user_id'] 			= $input['user_id'];
		$data['level_id'] 			= $input['level_id'];
		$data['program'] 			= $input['program'];
		$data['institution'] 		= $input['institution'];
		$data['year'] 				= $input['year'];
		$data['cgpa']				= $input['cgpa'];
		$data['is_delete'] 			= 0;

		$this->db->insert('user__academic_qualification',$data);

		return $this->db->insert_id();
	}

	public function editQualification($input, $qualifactionId = null)
	{
		$this->db->set($input);
		$this->db->where('id',$qualifactionId);
		$this->db->update('user__academic_qualification');

		return true;
	}
}
?>