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

class Model_client_new_resignation extends CI_Model {

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

	public function getClientNewResignationList($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('i.ID as attachment_id, i.filename, i.file_location')
				->select('ifnull(h.name, a.client_name) as client_name')
                ->select('(select status from sv__approval where table_name="sv__client_new_resignation" 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__client_new_resignation" 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')
				->join('sv__client_new_resignation_attachment i', 'i.client_new_resignation_id = a.ID and i.is_delete = 0', '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__client_new_resignation a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }

    function insertCustomerCRM($table_name, $input)
    {
    	if(!$table_name) return false;
    	$query = $this->db->insert($table_name,$input);
    	return $this->db->insert_id();
    }

    function updateServiceStatus($input, $department_id, $client_id)
    {
    	if(!$input) return false;

    	$where = array
    	(
    		'departmentID'	=> $department_id,
    		'customerID'	=> $client_id,
    	);
    	
    	$query = $this->db->update('customer__services_status',$input,$where);
    	return $query;
    }
}
?>