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

class Model_job_request extends CI_Model {

	function __construct()
	{
		parent::__construct();
		if(ENVIRONMENT == 'production'){
			$this->erp_db = "spserp";
			$this->mgs_db = "mgsdb";
		} else if(ENVIRONMENT == 'development'){
			$this->erp_db = "spserp_dev";
			$this->mgs_db = "mgsdb_dev";
		}
	}

	public function getJobRequestList($ID = null, $requester_id=null)
	{
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, f.shortName as department_short_name, c.name as branchName')
				->select('d.name as requester_name, d.email as requester_email, d.staff_id as requester_staff_id')
				->select('e.name as created_name, e.email as created_email, f.id as created_department_id, f.name as created_dept_name, g.name as created_branch_name')
				->select('h.name as client_name')
                ->select('(select status from sv__approval where table_name="sv__job_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__job_request" 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')
				->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('customer h', 'h.id = a.client_id', 'LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.ID desc, a.client_id asc');

        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.')');
		}

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

    public function insertJobToMGS($input = array())
    {
		if (!$input) return NULL;
		
		$this->db->insert($this->mgs_db.'.job', $input);

		if($this->db->affected_rows() > 0)
		{
			return $this->db->insert_id();
		}else
		{
			return NULL;
		}
	}

	public function updateOrCreateNewClient($request_id,$client_id = array())
	{
		if(empty($client_id)) return false;
		
		$deleteNotIn = array
		(
			'a.is_delete'	=> 0,
			'a.updatedBY'	=> $this->session->userdata('user')['id'],
			'a.updatedTS'	=> date('Y-m-d H:i:s'),
		);
		$this->db->where_not_in('a.client_id',$client_id)
				->where('a.job_id',$request_id)
				->update('sv__job_request_client a',$deleteNotIn);


		$detail_stored = $this->db->select('a.*')
						->where('a.job_id',$request_id)
						->where('a.is_delete',0)
						->get('sv__job_request_client a')
						->result_array();
		

		if(!empty($detail_stored))
		{
			$batch_insert = array();
			foreach ($client_id as $client) 
			{
				$bool = false;
				
				foreach ($detail_stored as $key => $details) 
				{
					// die(debug($client));
					if($details['client_id'] == $client)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'job_id'		=> $request_id,
						'client_id'		=> $client,
						'is_delete'		=> 0,
						'createdBY'		=> $this->session->userdata('user')['id'],
						'createdTS'		=> date('Y-m-d H:i:s')
					);
					array_push($batch_insert,$temp);
				}

			}
			if($batch_insert)$this->db->insert_batch('sv__job_request_client',$batch_insert);return true;
		} else {
			$batch_insert = array();
			foreach ($client_id as $client) 
			{
				$temp = array
				(
					'job_id'		=> $request_id,
					'client_id'		=> $client,
					'is_delete'		=> 0,
					'createdBY'		=> $this->session->userdata('user')['id'],
					'createdTS'		=> date('Y-m-d H:i:s')
				);
				array_push($batch_insert,$temp);
			}
			if($batch_insert)$this->db->insert_batch('sv__job_request_client',$batch_insert);return true;
		}
	}

	public function updateOrCreateNewDepartment($request_client_id,$department_id = array())
	{
		if(empty($department_id)) return false;
		
		$deleteNotIn = array
		(
			'a.is_delete'	=> 0,
			'a.updatedBY'	=> $this->session->userdata('user')['id'],
			'a.updatedTS'	=> date('Y-m-d H:i:s'),
		);
		$this->db->where_not_in('a.department_id',$department_id)
				->where('a.request_client_id',$request_client_id)
				->update('sv__job_request_department a',$deleteNotIn);


		$detail_stored = $this->db->select('a.*')
						->where('a.request_client_id',$request_client_id)
						->where('a.is_delete',0)
						->get('sv__job_request_department a')
						->result_array();
		

		if(!empty($detail_stored))
		{
			$batch_insert = array();
			foreach ($department_id as $dept) 
			{
				$bool = false;
				
				foreach ($detail_stored as $key => $details) 
				{
					// die(debug($dept));
					if($details['department_id'] == $dept)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'request_client_id'		=> $request_client_id,
						'department_id'			=> $dept,
						'is_delete'				=> 0,
						'createdBY'				=> $this->session->userdata('user')['id'],
						'createdTS'				=> date('Y-m-d H:i:s')
					);
					array_push($batch_insert,$temp);
				}

			}
			if($batch_insert)$this->db->insert_batch('sv__job_request_department',$batch_insert);return true;
		} else {
			$batch_insert = array();
			foreach ($department_id as $dept) 
			{
				$temp = array
				(
					'request_client_id'		=> $request_client_id,
					'department_id'			=> $dept,
					'is_delete'				=> 0,
					'createdBY'				=> $this->session->userdata('user')['id'],
					'createdTS'				=> date('Y-m-d H:i:s')
				);
				array_push($batch_insert,$temp);
			}
			if($batch_insert)$this->db->insert_batch('sv__job_request_department',$batch_insert);return true;
		}
	}

	public function getClientByJob($request_id = array())
	{
		$request_client_id = array();

		$query = $this->db->select('a.*')
					->where('a.job_id',$request_id)
					->where('a.is_delete',0)
					->get('sv__job_request_client a')
					->result_array();

		if($query) foreach($query as $key => $row)
		{
			array_push($request_client_id,$row['ID']);
		}

		return $request_client_id;
	}
}
?>