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

class Model_training_request extends CI_Model {

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

	public function getListTrainingRequest($ID = null, $user_id = null, $department_id = null, $year = null)
	{
		// $query = $this->db->select('a.*, b.name as business_entity, c.ID as photoID, c.sv_trainingRequest_id, c.photo_title, c.file_location, d.name as dept_name, e.name as branchName, f.name as requestor, f.staff_id, f.email, group_concat(h.name) as participant_name')
		// 		->select('(select status from sv__approval where table_name="sv__training_request" and id_table=a.ID order by ID desc limit 1) as status_app')
		// 		->join('company__business_entity b', 'b.id = a.business_entity_id')
  //               ->join('sv__training_request__upload c', 'c.sv_trainingRequest_id = a.ID', 'LEFT')
  //               ->join('company__department d', 'd.id = a.department_id')
  //               ->join('company__branch e', 'e.id = d.branch_id')
  //               ->join('user f', 'f.id = a.user_id', 'left')
  //               ->join('sv__training_request_participant g','g.sv_trainingRequest_id = a.ID and g.is_delete = 0','LEFT')
  //               ->join('user h', 'h.id = g.user_id', 'left')
		// 		->where('a.is_delete',0)
  //               ->group_by('a.ID')
  //               ->order_by('a.ID', 'ASC');

		$query = $this->db->select('a.*, f.staff_id, b.name as requestor, b.email, h.name as business_entity, c.name as dept_name, d.name as branchName, group_concat(e.id) as participant_id, group_concat(e.user_id) as participant_user_id, group_concat(f.name) as participant_name, group_concat(e.department_id) as participant_department_id, group_concat(e.hod_status) as participant_hod_status, group_concat(ifnull(i.name,"")) as participant_hod_name, g.ID as uploadID, g.photo_title, g.file_location')
					->select('(select status from sv__approval where table_name="sv__training_request" 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__training_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
					->join('user b','b.id = a.user_id','left')
					->join('company__department c','c.id = a.department_id','left')
					->join('company__branch d','d.id = c.branch_id','left')
					->join('sv__training_request_participant e','e.sv_trainingRequest_id = a.ID and e.is_delete = 0','left')
					->join('user f','f.id = e.user_id','left')
					->join('sv__training_request__upload g','g.sv_trainingRequest_id = a.ID and g.is_delete = 0','left')
					->join('company__business_entity h', 'h.id = a.business_entity_id','left')
					->join('user i','i.id = e.approvedBY','left')
					->where('a.is_delete',0)
	                ->group_by('a.ID')
	                ->order_by('a.ID', 'ASC');
                
        if($ID > 0)
		{
			$this->db->where('a.ID',$ID);
		}
		
		if(!empty($user_id))
		{
			$this->db->where('(a.user_id = '.$user_id.' OR e.user_id = '.$user_id.')');
			// $this->db->where('a.user_id',$user_id);
			// $this->db->or_where('e.user_id',$user_id);
        }

        if(!empty($department_id))
		{
			$this->db->where('(a.department_id = '.$department_id.' OR e.department_id = '.$department_id.')');
			// $this->db->where('a.department_id',$department_id);
			// $this->db->or_where('e.department_id',$department_id);
        }

        if($year > 0)
		{
			$this->db->where('year(a.createdTS)',$year);
		}
        
		$query = $this->db->get('sv__training_request a')
				->result_array();
        
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getRequestByBusinessEntity($year)
	{
		$query = $this->db->select('a.name as business_entity, count(b.department_id) as total_request')
							->join('sv__training_request b','b.business_entity_id = a.id and b.is_delete = 0 and year(b.createdTS) = '.$year,'LEFT')
							->where('a.is_delete',0)
							->group_by('a.id')
							->order_by('a.name asc')
							->get('company__business_entity a');

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

	public function getTrainingRequestByWeek()
	{
		$query = $this->db->select('a.*, b.name as business_entity, d.name as dept_name, e.name as branchName, f.name as requestor, f.staff_id, f.email')
				->join('company__business_entity b', 'b.id = a.business_entity_id')
                ->join('company__department d', 'd.id = a.department_id')
                ->join('company__branch e', 'e.id = d.branch_id')
                ->join('user f', 'f.id = a.user_id', 'left')
				->where('DATE(a.proposed_date) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__training_request a')
				->result_array();
		// debug($this->db->last_query());
		// die();
		return (!empty($query))?$query:null;
	}

	public function update_training_request($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__training_request');
		return $this->db->affected_rows();
	}

	public function update_training_request_upload($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__training_request__upload');
		return $this->db->affected_rows();
	}

	public function getTrainingRequestDetails($ID)
	{
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.name as business_entity, e.ID as uploadID, e.sv_trainingRequest_id, e.photo_title, e.file_location, f.name as requestor, group_concat(h.name) as participant_name')
				->join('company__department b', 'b.id = a.department_id')
				->join('company__branch c', 'c.id = b.branch_id')
				->join('company__business_entity d', 'd.id = a.business_entity_id')
				->join('sv__training_request__upload e', 'e.sv_trainingRequest_id = a.ID AND e.is_delete = 0', 'LEFT')
				->join('user f', 'f.id = a.user_id', 'left')
				->join('sv__training_request_participant g','g.sv_trainingRequest_id = a.ID and g.is_delete = 0','LEFT')
                ->join('user h', 'h.id = g.user_id', 'left')
				->where('a.ID', $ID)
				->where('a.is_delete',0)
				->order_by('a.ID', 'ASC')
				->get('sv__training_request a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getTrainingRequestFile($where = array())
	{
		$this->db->select('*');
		
		if($where)
		{
			foreach ($where as $column => $value) 
			{
				$this->db->where($column,$value);
			}
		}
		$training_request_files = $this->db->get('sv__training_request__upload')
							->result_array();   
		return $training_request_files;
	}


	public function updateOrCreateNew($training_request_id,$participant_id = array(),$participanDeptID = array())
	{
		if(empty($participant_id)) return false;
		
		$this->db->set('a.is_delete',1)
				->where_not_in('a.user_id',$participant_id)
				->where('a.sv_trainingRequest_id',$training_request_id)
				->update('sv__training_request_participant a');

		$participant_stored = $this->db->select('a.*')
						->where('a.sv_trainingRequest_id',$training_request_id)
						->where('a.is_delete',0)
						->get('sv__training_request_participant a')
						->result_array();

		if(!empty($participant_stored))
		{
			$batch_insert = array();
			foreach ($participant_id as $part_key => $partcipant) 
			{
				$bool = false;
				
				foreach ($participant_stored as $key => $partcipants) 
				{
					// die(debug($partcipant));
					if($partcipants['user_id'] == $partcipant)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'sv_trainingRequest_id'		=> $training_request_id,
						'user_id'      				=> $partcipant,
						'department_id'				=> $participanDeptID[$part_key],
						'is_delete'     			=> 0,
						'createdBY'    				=> $this->session->userdata('user')['id'],
						'createdTS'    				=> date('Y-m-d H:i:s'),
						'updatedBY'    				=> $this->session->userdata('user')['id'],
						'updatedTS'    				=> date('Y-m-d H:i:s'),
					);
					array_push($batch_insert,$temp);
				}

			}
			$this->db->insert_batch('sv__training_request_participant',$batch_insert);return true;
		} else {
			$batch_insert = array();
			foreach ($participant_id as $part_key => $partcipant) 
			{
				$temp = array
				(
					'sv_trainingRequest_id'		=> $training_request_id,
					'user_id'      				=> $partcipant,
					'department_id'				=> $participanDeptID[$part_key],
					'is_delete'     			=> 0,
					'createdBY'    				=> $this->session->userdata('user')['id'],
					'createdTS'   				=> date('Y-m-d H:i:s'),
					'updatedBY'    				=> $this->session->userdata('user')['id'],
					'updatedTS'    				=> date('Y-m-d H:i:s'),
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__training_request_participant',$batch_insert);return true;
		}
	}

	function getUserDepartment($user_id)
	{
		$query = $this->db->select('department_id')
						->where('id',$user_id)
						->get('user__employment_detail')
						->result_array();
		
		return (!empty($query))?$query[0]:null;
	}
	
}


?>