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

class model_user_professional_experiencemgs extends CI_Model {
	private $mgs_db;
	
	function __construct() {
		parent::__construct();
		
		if(ENVIRONMENT == 'production'){
		  $this->mgs_db = "mgsdb";
		} else {
		  $this->mgs_db = "mgsdb_dev";
		}
    }

    function getExpMgs($user_id, $year=null, $type=null)
	{
		//$user_id=464;
		//$year=2021;
		
		/* $q_user = $this->db->select('b.user_id,b.name,b.staff_id')
			->where('a.user_id',$user_id)
			->get('user__employment_detail a')
			->result_array(); */
		
		$job_data = $this->getUserJobIds($user_id,$year);
			
		$q_job_detail = null;	
		if ($job_data!=null && !empty($job_data['ids']))
		{
			$this->db->select('a.*,if(a.is_internal = 1, d.name, b.name) as customerName,e.name as department_name, f.name as branch_name, g.service, g.shortName as service_short, h.job as type_job');
			$this->db->where_in('a.ID',$job_data['ids']);
			$this->db->join('customer b','b.id = a.customerID','left');
			$this->db->join('company__department e','e.id = a.departmentID','LEFT',false);
			$this->db->join('company__department d','a.customerID = d.id','LEFT',false);
			$this->db->join('company__branch f', 'f.id = e.branch_id', 'LEFT', false);
			$this->db->join($this->mgs_db.'._ref__services g', 'g.id = a.serviceTypeID', 'LEFT', false);
			$this->db->join($this->mgs_db.'._ref__job h', 'h.id = a.jobTypeID', 'LEFT', false);
			
			if($type!=null) $this->db->where('a.is_internal', $type);
			
			$query = $this->db->get($this->mgs_db.'.job a');
			
			$q_job_detail = $query->result_array();
			
			if ($query->num_rows() >= 1){
				foreach ($q_job_detail as $key => $det) 
				{
					$q_job_detail[$key]['year_join'] = $job_data['years'][$det['ID']];
				}
			}
		}
		
		return $q_job_detail;
	}
	
	function getUserJobIds($user_id, $year=null){
		$this->db->select('a.job_id, a.is_leader, YEAR(a.created_ts) as year');
		$this->db->where('a.staff_id',$user_id);
		$this->db->where('a.is_delete',0);
		
		if($year) $this->db->where('YEAR(a.created_ts)', $year);
		
		$query = $this->db->get($this->mgs_db.'.job__pic a');
		$q_job = $query->result_array();
		
		if (empty($q_job))
		{
			return null;
		} else {
			$job_ids=array();
			
			foreach ($q_job as $key => $j) 
			{
				array_push($job_ids,$j['job_id']);
				$join_year[$j['job_id']] = $j['year'];
			}
			
			$data['ids'] = $job_ids;
			$data['years'] = $join_year;
			
			return $data;
		}
	}
}
?>