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

class Model_training_evaluation extends CI_Model {

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

	public function getListTrainingEvaluation($user_id = null)
	{
		$query = $this->db->select('a.*, b.*, c.*, d.name as userName, d.staff_id, e.name as dept_name, f.name as branchName')
				->join('sv__evaluation b',' b.ID = a.sv_evaluation_id')
				->join('sv__evaluation_training c',' c.ID = a.sv_evaluation_training_id')
				->join('user d',' d.id = b.user_id')
				->join('company__department e',' e.id = b.department_id')
				->join('company__branch f',' f.id = e.branch_id')
				->where('a.is_delete',0)
				->where('b.is_delete',0)
				->group_by('a.sv_evaluation_id')
                ->order_by('a.ID', 'ASC');

        if(!empty($user_id))
		{
			$this->db->where('b.user_id',$user_id);
        }

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

	public function name_with_branch()
	{
		// $this->db->cache_on();
		$record_set = $this->db->select('a.id, concat(a.name," / ", , b.name) as department')
				->where('a.is_delete',0)
				->join('company__branch b','b.id = a.branch_id')
				->order_by('a.name, b.name')
				->get('company__department a')
				->result_array();
		// $this->db->cache_off();
		return $record_set;
	}

	public function getEvaluationQuestionDetails($ID = 0)
	{ 
		$rs = $this->db->get('sv__evaluation__category_question')->result_array();
		$i=0;
		if ($rs) foreach($rs as $row){
			$rs[$i]['item'] = $this->db->select('a.*, a.ID as questionID')
				//->join('sv__evaluation__answer c','a.ID = c.sv_evaluation_question_id AND c.is_delete=0','LEFT OUTER')
				//->join('sv__evaluation d','d.ID = c.sv_evaluation_id and d.ID='.$ID,'LEFT OUTER')
				->where('a.category_ID',$row['ID'])
				->where('a.is_delete',0)
				->order_by('a.ID')
				->get('sv__evaluation__question a')
				->result_array(); 
			$i++;	
		} 
		// die(debug($this->db->last_query()));
		return $rs; 	
	}

	public function getAllEvaluationDetails_new($ID = 0)
	{
		if(!empty($ID))
		{
			$this->db->where('a.sv_evaluation_id',$ID);
		}

		$query = $this->db->select('a.ID as asnwer_id, a.sv_evaluation_id')
					->select('b.user_id, f.name as userName, b.department_id, g.name as dept_name, h.name as branchName')
					->select('b.approvedBy, i.name as approvedByName')
					->select('a.sv_evaluation_training_id, c.training_title, c.packages, c.venue, c.training_date, c.time, c.respective_dept, c.cpd_hours, c.trainer_name')
					->select("a.sv_evaluation_question_id, e.ID as category_id, e.category_question, d.category_id as question_category_id, GROUP_CONCAT(d.ID SEPARATOR '==>') AS questionID, GROUP_CONCAT(d.question SEPARATOR '==>') AS question, d.question_type, GROUP_CONCAT(a.answer_radio SEPARATOR '==>') AS answer_radio, (IF(a.answer_text!='',GROUP_CONCAT(a.answer_text SEPARATOR '==>'),'')) AS answer_text")
					->select('b.is_delete, b.createdBy, b.createdTS')
					->join('sv__evaluation b','b.ID = a.sv_evaluation_id','LEFT')
					->join('sv__evaluation_training c','c.ID = a.sv_evaluation_training_id','LEFT')
					->join('sv__evaluation__question d','d.ID = a.sv_evaluation_question_id','LEFT')
					->join('sv__evaluation__category_question e','e.ID = d.category_id','LEFT')
					->join('user f','f.id = b.user_id','LEFT')
					->join('company__department g','g.id = b.department_id','LEFT')
					->join('company__branch h','h.id = g.branch_id','LEFT')
					->join('user i','i.id = b.approvedBy','LEFT')
					->where('a.is_delete',0)
					->group_by('d.category_id')
					->order_by('a.sv_evaluation_question_id','asc')
					->get('sv__evaluation__answer a')
					->result_array();

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

	public function getUserEvaluationDetails($ID = 0)
	{
		$query = $this->db->select('a.*, f.name as departmentName, b.name as requestor')
				->join('company__department f', 'f.id = a.department_id')
				// ->join('company__branch g', 'g.id = f.branch_id')
				->join('user b', 'b.id = a.user_id', 'left')
				->where('a.is_delete',0)
				->where('a.id', $ID)
				->get('sv__evaluation a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getAllEvaluationDetails($ID = 0)
	{
		$query = $this->db->select('a.*, f.name as departmentName, g.name as branchName')
				->join('company__department f', 'f.id = a.respective_dept')
				->join('company__branch g', 'g.id = f.branch_id')
				->where('a.is_delete',0)
				->where('a.id', $ID)
				->get('sv__evaluation_training a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getEvaluationAnswerDetails($ID = 0)
	{
		$query = $this->db->select('a.* ')
				// ->join('sv__evaluation e', 'e.ID = a.sv_evaluation_id')
				// ->join('user f', 'f.id = e.user_id', 'left')
				->where('a.sv_evaluation_training_id', $ID)
				->where('a.is_delete',0)
				->order_by('a.ID')
				->get('sv__evaluation__answer a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function update_training_evaluation($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__evaluation');
		return $this->db->affected_rows();
	}


	
}


?>