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

class Model_form extends CI_Model {
	function __construct() {
		parent::__construct();
    }

    function getForms($filters = array())
    {
        $this->db->select('a.*, b.category as category, c.category as main_category')
		         ->join('hr__policyprocedure_category b', 'a.category_id = b.id', 'left')
		         ->join('hr__policyprocedure_category c', 'b.parent_id = c.id', 'left');

		if(!empty($filters)){
			foreach ($filters as $filter => $value) {
				$this->db->where($filter, $value);
			}
		}

		$this->db->where('a.status', 0)
				 ->where('a.is_delete', 0);
		
		// $query = $this->db->get('hr__form a');
		$query = $this->db->get('hr__form a')->result_array();

		foreach ($query as $key => $row) 
		{
			if($row['category_id'] == 9)
			{
				$status_detail = array
				(
					'total_pending' 	=> 'ifnull((SELECT count(ID) FROM '.$row['table_name'].' WHERE is_delete = 0 AND (approval_status = 0 OR prepared_status = 0)),0)',
					'total_approved' 	=> 'ifnull((SELECT count(ID) FROM '.$row['table_name'].' WHERE is_delete = 0 AND approval_status = 1),0)',
					'total_rejected' 	=> 'ifnull((SELECT count(ID) FROM '.$row['table_name'].' WHERE is_delete = 0 AND approval_status = 2),0)',
					'total_completed' 	=> 'ifnull((SELECT count(ID) FROM '.$row['table_name'].' WHERE is_delete = 0 AND prepared_status = 1),0)',
					'total_duplicate' 	=> 'ifnull((SELECT count(ID) FROM '.$row['table_name'].' WHERE is_delete = 0 AND prepared_status = 2),0)',
				);
			}else{
				$status_detail = array
				(
					'total_pending'		=> '"%Pending%" or b.status IS NULL',
					'total_progess'		=> '"%In_progress%"',
					// 'total_approved'	=> '"%Approved%"',
					'total_approved'	=> ($row['id'] == 29 || $row['category_id'] == 7 || $row['id'] == 42)?'"%Completed%"':'"%Approved%"',
					'total_verified'	=> '"%Verified%"',
					'total_rejected'	=> '"%Rejected%"',
					'total_cancel'		=> '"%Cancel%"',
				);
			}

			foreach($status_detail as $status_key => $status)
			{
				if($row['category_id'] == 9)
				{
					$total = $this->db->select($status.' as '.$status_key)->where('YEAR(a.createdTS)',date('Y'))->where('a.is_delete',0)->limit(1)->get($row['table_name'].' a')->result_array()[0];

					$query[$key][$status_key] = $total[$status_key];
				}else{
					$this->db->select('a.title, count(b.status) as '.$status_key)
						->join('sv__approval b','b.table_name = a.table_name AND b.is_delete = 0 AND YEAR(approvedTS) = '.date('Y').' AND b.status LIKE '.$status,'LEFT')
						// ->join('sv__approval b','b.table_name = a.table_name AND b.is_delete = 0 AND YEAR(approvedTS) = '.date('Y').' AND b.status LIKE '.$status,'LEFT')
						->where('a.is_delete',0)
						->where('a.status',0)
						->group_by('a.id');
					
					$total = $this->db->get('hr__form a')->result_array();

					$query[$key][$status_key] = $total[$key][$status_key];
				}

			}
		}

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

	function getBooking($filters = array(), $group_by = array())
    {
		if(!empty($filters)){
			foreach ($filters as $filter => $value) {
				$this->db->where($filter, $value);
			}
		}

		if(!empty($group_by)){
			foreach ($group_by as $groupby) {
				$this->db->group_by($groupby);
			}
		}

		$query = $this->db->select('a.*, replace(a.status, "_", " ") as status')->get('view_booking a');

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

	function getBookingName($booking_name = '', $where_in = array())
	{
		if ($where_in) foreach($where_in as $key => $value) $this->db->where_in($key, $value);

		if($booking_name)
		{
			$query = $this->db->select('a.*')
					->group_by('a.id')
					->order_by('a.cat_id, a.id')
					->get($booking_name.' a');
		}else{
			$query = $this->db->select('a.*')
					->group_by('a.title')
					->order_by('a.title')
					->get('view_booking a');
		}
		
		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}

	public function sv__filter($table, $select = array('*'), $where = array(), $join = array(), $order = array(), $group = array(), $having = array(), $or_having = array(), $where_not_in = array(), $where_in = array(), $or_where = array(), $like = array(), $limit = 0)
	{
		if(!$table) return NULL;
		
		// if(isset($where_in['a.cat_id']) && in_array(9,$where_in['a.cat_id']))
		// {
		// 	// $select = array('a.title','a.department_id','a.department_name','a.requested_date', 'a.type','a.price','count(a.department_id) as total');
		// 	if(isset($where)) 
		// 	{
		// 		if(isset($where['a.id']) && $where['a.id'] == 37)
		// 		{	
		// 			$where_in['a.status'] = array("Approved","Reject");
		// 			$group = array('a.id_table');
		// 		}
		// 	}
		// 	// $where_in['a.prepared_status'] = array("Completed","Duplicate");
		// }

		if ($select) 		foreach($select as $key => $value) $this->db->select($value,false);
		if ($where)
		{
			foreach($where as $key => $value) 
			{
				if($key == 'not_admin' || $key == 'special_where') $this->db->where($value);
				else $this->db->where($key,$value);
			}
		}
		if ($join) 			foreach($join as $key => $value) $this->db->join($value[0],$value[1],$value[2],false);
		if ($group)			foreach($group as $key => $value) $this->db->group_by($value);
		if ($having) 		foreach($having as $key => $value) $this->db->having($key,$value);
		if ($or_having) 	foreach($or_having as $key => $value) $this->db->or_having($value);
		if ($order)			foreach($order as $key => $value) $this->db->order_by($key, $value);
		if ($where_not_in)	foreach($where_not_in as $key => $value) $this->db->where_not_in($key, $value);
		if ($where_in)		foreach($where_in as $key => $value) $this->db->where_in($key, $value);
		if ($or_where) 		foreach($or_where as $key => $value) $this->db->or_where($key,$value);
		if ($like) 			foreach($like as $key => $value) $this->db->like($key,$value);
		if ($limit > 0)		$this->db->limit($limit);


		$query = $this->db->get($table);

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

	public function countComplaint()
	{
		$sql = "SELECT  
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 0) as count_comp_new,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 1) as count_comp_in_progress,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 2) as count_comp_solved,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 3) as count_comp_approved,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 4) as count_comp_verified
				FROM sv__complaint
				LIMIT 1";

		$query = $this->db->query($sql);

		return (!empty($query))?$query->result():null;

	}

	public function countComplaint_hod($dept_id)
	{
		$sql = "SELECT  
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 0 AND dept_id = ".$dept_id.") as count_comp_new,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 1 AND dept_id = ".$dept_id.") as count_comp_in_progress,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 2 AND dept_id = ".$dept_id.") as count_comp_solved,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 3 AND dept_id = ".$dept_id.") as count_comp_approved,
				(SELECT count(id) FROM sv__complaint WHERE is_delete = 0 AND status = 4 AND dept_id = ".$dept_id.") as count_comp_verified
				FROM sv__complaint
				LIMIT 1";

		$query = $this->db->query($sql);

		return (!empty($query))?$query->result():null;

	}

	public function getHod($dept_id)
	{
		$this->db->select('*')
				->where('is_delete', 0)
				->where('id', $dept_id);

		$query = $this->db->get('company__department');

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