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

class Model_job_v2 extends CI_Model {

	private $mgs_db;
  	private $erp_db; 

	public 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 get_user_mgs_job($staff_id = null)
	{
		$this->db->select('a.*, b.customerID, b.description, b.yearEndDate, if(b.is_internal = 1, d.name, c.name) as customerName');
		$this->db->where('a.staff_id', $staff_id);
		$this->db->where('a.is_delete', 0);
		$this->db->where('b.is_delete', 0);
		$this->db->join($this->mgs_db.'.job b','b.id = a.job_id','left outer');
		$this->db->join($this->mgs_db.'.customer c','c.id = b.customerID','left outer');
		$this->db->join($this->mgs_db.'.company__department d','d.id = b.customerID','left outer');
		$this->db->order_by('b.id', 'desc');

		$query = $this->db->get($this->mgs_db.'.job__pic a');

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

	public function get_mgs_job($id = null)
	{
		$this->db->select('b.id, b.customerID, b.description, b.yearEndDate, if(b.is_internal = 1, d.name, c.name) as customerName, b.departmentID, e.is_leader');
		$this->db->where('b.id', $id);
		$this->db->where('b.is_delete', 0);
		$this->db->join($this->mgs_db.'.customer c','c.id = b.customerID','left outer');
		$this->db->join($this->mgs_db.'.company__department d','d.id = b.customerID','left outer');
		$this->db->join($this->mgs_db.'.job__pic e','e.job_id = b.id','left outer');
		$this->db->order_by('b.id', 'desc');

		$query = $this->db->get($this->mgs_db.'.job b');

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

	public function is_team_leader($user_id)
	{
		$this->db->select('*');
		$this->db->where('staff_id', $user_id);
		$this->db->where('is_delete', 0);
		$this->db->where('is_leader', 1);

		$query = $this->db->get($this->mgs_db.'.job__pic');

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

	public function is_job_team_leader($job_id, $user_id)
	{
		$this->db->select('*');
		$this->db->where('staff_id', $user_id);
		$this->db->where('job_id', $job_id);
		$this->db->where('is_delete', 0);
		$this->db->where('is_leader', 1);

		$query = $this->db->get($this->mgs_db.'.job__pic');

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

	public function get_team_leader($job_id)
	{
		$this->db->select('a.staff_id, b.name as staff_name, c.name as department_name, d.name as branch_name');
		$this->db->where('a.job_id', $job_id);
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.is_leader', 1);
		$this->db->join($this->erp_db.'.user__employment_detail b','b.user_id = a.staff_id');
		$this->db->join($this->mgs_db.'.company__department c','c.id = b.department_id','left outer');
		$this->db->join($this->mgs_db.'.company__branch d','d.id = c.branch_id','left outer');

		$query = $this->db->get($this->mgs_db.'.job__pic a');

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

	public function get_lead_jobs($user_id)
	{
		$this->db->select('a.*, if(a.is_internal = 1, d.name, c.name) as customerName,');
		$this->db->where('a.is_delete', 0);
		$this->db->where('b.staff_id', $user_id);
		$this->db->where('b.is_leader', 1);
		$this->db->where('b.is_delete', 0);
		$this->db->join($this->mgs_db.'.job__pic b','b.job_id = a.id','left outer');
		$this->db->join($this->mgs_db.'.customer c','c.id = a.customerID','left outer');
		$this->db->join($this->mgs_db.'.company__department d','d.id = a.customerID','left outer');

		$query = $this->db->get($this->mgs_db.'.job a');

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

	public function get_claim_jobs($claim_id, $table, $name)
	{
		$this->db->select('a.*, "'.$name.'" as claim_name, b.customerID, b.description, b.yearEndDate, if(b.is_internal = 1, d.name, c.name) as customerName');
		$this->db->where('a.claim_id', $claim_id);
		$this->db->where('a.is_delete', 0);
		$this->db->join($this->mgs_db.'.job b','b.id = a.job_id','left outer');
		$this->db->join($this->mgs_db.'.customer c','c.id = b.customerID','left outer');
		$this->db->join($this->mgs_db.'.company__department d','d.id = b.customerID','left outer');

		$query = $this->db->get($this->erp_db.'.'.$table.' a');

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

	public function get_team_member_claim_submission($job_id)
	{
		$this->db->select('a.staff_id');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.job_id', $job_id);
		$this->db->join($this->mgs_db.'.job b','b.id = a.job_id AND b.is_delete = 0','left outer');

		$query 			= $this->db->get($this->mgs_db.'.job__pic a');
		$staff_id_arr 	= array();

		foreach ($query->result() as $key => $value) {
			array_push($staff_id_arr, $value->staff_id);
		}

		$claim_list	= array();

		$this->db->select('a.*,b.name');
		$this->db->where(
			array(
				'a.is_delete' 	=> 0,
				'a.is_submit'	=> 1,
				'a.status'		=> 0,
			)
		);
		$this->db->where_in('a.user_id', $staff_id_arr);
		$this->db->join($this->erp_db.'.user__employment_detail b','b.user_id = a.user_id');
		$this->db->order_by('a.year','desc');
		$this->db->order_by('a.month','desc');

		$query2 = $this->db->get($this->erp_db.'.hr__claim a');
		
		return $query2->result();
	}

	public function get_claim_data($job_id)
	{
		$query = $this->db->query('
			SELECT claim_id, year, month, staff_name, user_id
			FROM (
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Mileage" as claim_name 
				FROM hr__claim_travelling a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Petrol" as claim_name 
				FROM hr__claim_petrol a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Accommodation" as claim_name 
				FROM hr__claim_accommodation a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Meals Allowance" as claim_name 
				FROM hr__claim_meals a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Subsistence Allowance" as claim_name 
				FROM hr__claim_subsistence a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Transportation" as claim_name 
				FROM hr__claim_transportation a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Parking" as claim_name 
				FROM hr__claim_parking a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Toll" as claim_name 
				FROM hr__claim_toll a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Entertainment" as claim_name 
				FROM hr__claim_entertainment a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Trainer/Facilitator" as claim_name 
				FROM hr__claim_trainer a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
				UNION ALL
				SELECT a.claim_id as claim_id, a.job_id, e.user_id as user_id, e.year as year, e.month as month, f.name as staff_name, "Others" as claim_name 
				FROM hr__claim_others a
				LEFT OUTER JOIN '.$this->mgs_db.'.job b ON b.id = a.job_id 
				LEFT OUTER JOIN '.$this->mgs_db.'.customer c ON c.id = b.customerID 
				LEFT OUTER JOIN '.$this->mgs_db.'.company__department d ON d.id = b.customerID 
				LEFT OUTER JOIN hr__claim e ON e.id = a.claim_id AND e.is_delete = 0 AND e.is_submit = 1
				LEFT OUTER JOIN user__employment_detail f ON f.id = e.user_id
				WHERE a.job_id = '.$job_id.'
				AND a.is_delete = 0
			) AS F
			GROUP BY claim_id
		');

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

}
?>