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

class Model_complaint extends CI_Model {

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

		if(ENVIRONMENT == 'production'){
			$this->erp_db = "spserp_master";
			$this->mgs_db = "mgsdb_master";
		} else if(ENVIRONMENT == 'development'){
			$this->erp_db = "spserp_dev";
			$this->mgs_db = "mgsdb_dev";
		}
	}

	public function getComplaintListAdmin(){
		$this->db->select('a.*, b.name as department_name, c.name as branch_name, if(a.complaint_for = 1, d.name, concat(e.name, " | ", f.name)) as client_name, g.service, h.job, i.name as pic_name, j.name as reg_name');
		$this->db->join('company__department b','b.id = a.dept_id','LEFT OUTER');
    	$this->db->join('company__branch c','c.id = b.branch_id','LEFT OUTER');
    	$this->db->join('customer d','d.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__department e','e.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__branch f','f.id = e.branch_id','LEFT OUTER');
    	$this->db->join('_ref__services g','g.id = a.services_id','LEFT OUTER');
    	$this->db->join('_ref__job h','h.id = a.job_id','LEFT OUTER');
    	$this->db->join('user__employment_detail i','i.id = a.pic_id','LEFT OUTER');
    	$this->db->join('user__employment_detail j','j.id = a.created_by','LEFT OUTER');
		$this->db->where('a.is_delete', 0);
		$this->db->order_by('a.id DESC');

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

	public function getComplaintListHOD($dept_id, $pic_id){
		$this->db->select('a.*, b.name as department_name, c.name as branch_name, if(a.complaint_for = 1, d.name, concat(e.name, " | ", f.name)) as client_name, g.service, h.job, i.name as pic_name, j.name as reg_name');
		$this->db->join('company__department b','b.id = a.dept_id','LEFT OUTER');
    	$this->db->join('company__branch c','c.id = b.branch_id','LEFT OUTER');
    	$this->db->join('customer d','d.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__department e','e.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__branch f','f.id = e.branch_id','LEFT OUTER');
    	$this->db->join('_ref__services g','g.id = a.services_id','LEFT OUTER');
    	$this->db->join('_ref__job h','h.id = a.job_id','LEFT OUTER');
    	$this->db->join('user__employment_detail i','i.id = a.pic_id','LEFT OUTER');
    	$this->db->join('user__employment_detail j','j.id = a.created_by','LEFT OUTER');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.dept_id', $dept_id);
		$this->db->or_where('a.pic_id', $pic_id);
		$this->db->or_where('a.created_by', $pic_id);
		$this->db->order_by('a.id DESC');

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

	public function getComplaintListUser($pic_id){
		$this->db->select('a.*, b.name as department_name, c.name as branch_name, if(a.complaint_for = 1, d.name, concat(e.name, " | ", f.name)) as client_name, g.service, h.job, i.name as pic_name, j.name as reg_name');
		$this->db->join('company__department b','b.id = a.dept_id','LEFT OUTER');
    	$this->db->join('company__branch c','c.id = b.branch_id','LEFT OUTER');
    	$this->db->join('customer d','d.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__department e','e.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__branch f','f.id = e.branch_id','LEFT OUTER');
    	$this->db->join('_ref__services g','g.id = a.services_id','LEFT OUTER');
    	$this->db->join('_ref__job h','h.id = a.job_id','LEFT OUTER');
    	$this->db->join('user__employment_detail i','i.id = a.pic_id','LEFT OUTER');
    	$this->db->join('user__employment_detail j','j.id = a.created_by','LEFT OUTER');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.pic_id', $pic_id);
		$this->db->or_where('a.created_by', $pic_id);
		$this->db->order_by('a.id DESC');

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

	public function getComplaintListByID($id){
		$this->db->select('a.*, b.name as department_name, c.name as branch_name, if(a.complaint_for = 1, d.name, concat(e.name, " | ", f.name)) as client_name, g.service, h.job, i.name as pic_name, j.name as reg_name, 

			(CASE 
				WHEN a.complaint_for = 0 THEN l.name
				WHEN (a.complaint_for = 1 AND a.contact_id = 9999) THEN a.contact_name
				ELSE k.name
				END 
			) AS contact_name');
		
		$this->db->join('company__department b','b.id = a.dept_id','LEFT OUTER');
    	$this->db->join('company__branch c','c.id = b.branch_id','LEFT OUTER');
    	$this->db->join('customer d','d.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__department e','e.id = a.client_id','LEFT OUTER');
    	$this->db->join('company__branch f','f.id = e.branch_id','LEFT OUTER');
    	$this->db->join('_ref__services g','g.id = a.services_id','LEFT OUTER');
    	$this->db->join('_ref__job h','h.id = a.job_id','LEFT OUTER');
    	$this->db->join('user__employment_detail i','i.id = a.pic_id','LEFT OUTER');
    	$this->db->join('user__employment_detail j','j.id = a.created_by','LEFT OUTER');
    	$this->db->join('customer__contact k','k.id = a.contact_id','LEFT OUTER');
    	$this->db->join('user__employment_detail l','l.id = a.contact_id','LEFT OUTER');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.id', $id);
		$this->db->order_by('a.id DESC');

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

	public function update_complaint($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__complaint');

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

	public function getRequestByCompany($year = null){
		$query = $this->db->select('a.name as dept_name, a.shortName, b.name as branchName, count(d.id) as total_request')
							->join('company__branch b','b.id = a.branch_id','LEFT')
							->join('sv__complaint d','d.dept_id = a.id AND d.is_delete = 0 and YEAR(d.created_ts) = '.$year.'','LEFT')
							->where('a.is_delete',0)
							->group_by('a.id')
							->order_by('a.name', "ASC")
							->get('company__department a');

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

	public function getComplaintAttachment_main($id)
	{
		$this->db->select('*');
		$this->db->where('is_delete', 0);
		$this->db->where('complaint_id', $id);
		$this->db->where('class_id', 1);

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

	public function getComplaintAttachment_reso($id)
	{
		$this->db->select('*');
		$this->db->where('is_delete', 0);
		$this->db->where('complaint_id', $id);
		$this->db->where('class_id', 2);

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

	function createAttachment($input)
	{
		return ($this->db->insert_batch('sv__complaint_attachment',$input))?true:false;
	}
}
?>