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

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

	function getEntitlement($filters = array()) {
		$this->db->select('a.*, b.position');
		$this->db->join('company__position b', 'b.id = a.category_id');

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

		$this->db->where('a.is_delete', 0);
		$this->db->order_by('a.year', 'desc');
		$this->db->order_by('a.category_id', 'asc');

		$query = $this->db->get('hr__leave_entitlement a');
		//die(debug($this->db->last_query()));
		return ($query->num_rows() > 0) ? (($query->num_rows() == 1) ? $query->result_array()[0] : $query->result_array()) : null;
	}

	function getEntitlementYears() {
		$this->db->select('year');

		$this->db->where('is_delete', 0);
		$this->db->group_by('year');
		$this->db->order_by('year', 'desc');

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

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

	function getUsersLeaveAllocationDayByYears($filters = array()) {
		if(!isset($filters['year']) || !isset($filters['user_id']) || !isset($filters['leave_type_id']) ) return NULL;
		
		$query = $this->db->select('leave_entitled')
			->where('year',$filters['year'])
			->where('user_id',$filters['user_id'])
			->where('type_id',$filters['leave_type_id'])
			->where('is_delete', 0)
			->get('hr__leave_allocation');
			
		if ($query->num_rows() > 0) {
			$rs = $query->result_array();
			return $rs[0]['leave_entitled'];
		} else return 0;
	}
	function getUsersLeaveAllocation($filters = array())
	{
		$select = '
			a.*,
			group_concat(j.id) as allocation_id,
			group_concat(j.type_id) as type_id,
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name, 
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			i.parent_id as position_category_id, i.position as position_category
		';

		$this->db->select($select);
		
		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left')
				 ->join('hr__leave_allocation j', 'j.user_id = a.id AND j.year ='.$filters['j.year'], 'left outer');

		$this->db->group_by('a.id');		 

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

		$this->db->where('a.status', 0)
						 ->where('a.is_delete', 0)
						 ->order_by('a.name', 'asc')
						 ->order_by('j.type_id', 'asc');

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

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

	function getAllocation3($filters = array(), $exclude_emergency_and_unpaid=false) {
		$this->db->select('
			a.*, 
			b.code, b.name,
			b.code, b.name');
		$this->db->select('(select d.name  
from company__department d, ((`user__joining_group` `ujg` left join `user__profiling` `up` on((`up`.`id` = `ujg`.`profiling_id`))) left join .`user` `u` on((`u`.`id` = `up`.`user_id`))) where 
d.id = `ujg`.`personnel_area_id` and ((`up`.`action_efective_date` <= curdate()) and (`up`.`user_id` = a.user_id) and (`up`.`is_delete` = 0)) 
order by `up`.`action_efective_date` desc,`up`.`id` desc limit 1) AS `departmentName`');	
		$this->db->join('hr__leave_type b', 'b.id = a.type_id');
		
//these 2 join slow down the select.
// 		$this->db->join('user__employment_detail c', 'c.user_id = a.user_id');
// 		$this->db->join('company__department d', 'd.id = c.department_id');

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

		if ($exclude_emergency_and_unpaid == true) $this->db->where('a.type_id NOT IN(2, 4)');

		$this->db->where('a.is_delete', 0);
		$this->db->order_by('a.year', 'desc');
		$this->db->order_by('a.type_id', 'asc');

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

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

	function getAllocation($filters = array(), $exclude_emergency_and_unpaid=false) {
		$this->db->select('
			a.*'); 
//			b.code, b.name'); //akmal, gugurkan leave type name dlm kiraan prorate
/* akmal, gugurkan departname dlm kiraan prorate
		$this->db->select('(select d.name  
			from company__department d, ((`user__joining_group` `ujg` left join `user__profiling` `up` on((`up`.`id` = `ujg`.`profiling_id`))) left join .`user` `u` on((`u`.`id` = `up`.`user_id`))) where 
			d.id = `ujg`.`personnel_area_id` and ((`up`.`action_efective_date` <= curdate()) and (`up`.`user_id` = a.user_id) and (`up`.`is_delete` = 0)) 
			order by `up`.`action_efective_date` desc,`up`.`id` desc limit 1) AS `departmentName`,
*/			
		$this->db->select('	if(a.type_id = 1,(select sum(ifnull(leave_applied_before_april,0)) from view_leave_summary where user_id = a.user_id and year=a.year and type_id in (1,2)),0) as takenB4');	
//		$this->db->join('hr__leave_type b', 'b.id = a.type_id'); //gugurkan leave type name dlm kiraan prorate 
		
//these 2 join slow down the select.
// 		$this->db->join('user__employment_detail c', 'c.user_id = a.user_id');
// 		$this->db->join('company__department d', 'd.id = c.department_id');

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

		if ($exclude_emergency_and_unpaid == true) $this->db->where('a.type_id NOT IN(2, 4)');

		$this->db->where('a.is_delete', 0);
		$this->db->order_by('a.year', 'desc');
		$this->db->order_by('a.type_id', 'asc');

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

		// log_message('error', 'q rows: ' . $query->num_rows());

		return ($query->num_rows() > 0) ? (($query->num_rows() == 1) ? $query->result_array()[0] : $query->result_array()) : null;
	}

	function getUnallocatedLeaveTypes($filters = array()) {
		$this->db->select('a.*');
		$this->db->join('hr__leave_allocation b', 'b.type_id != a.id');

		$this->db->where('b.is_delete', 0);

		if ($filters['year']) 	 $this->db->where('b.year', $filters['year']);
		if ($filters['user_id']) $this->db->where('b.user_id', $filters['user_id']);

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

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

	function getAllocationYears() {
		$this->db->select('a.year, b.name as updated_by, a.updated_ts');
		$this->db->join('user b', 'b.id = a.updated_by', 'left');
		$this->db->where('a.is_delete', 0);
		$this->db->group_by('a.year');
		$this->db->order_by('a.year', 'desc');

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

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

	function getAllocatedUser($filters = array()) {
		// $this->db->select('b.*')
		// 				 ->join('user b', 'b.id = a.user_id')
		// 				 ->join('user__employment_detail c', 'c.user_id = b.id')
		// 				 ->join('company__department d', 'd.id = c.department_id')
		// 				 ->group_by('a.user_id')
		// 				 ->where('a.is_delete', 0);

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

		// $query = $this->db->get('hr__leave_allocation a');

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

		$sql = "SELECT DISTINCT user_id FROM hr__leave_allocation WHERE is_delete = 0 AND type_id = 1";

		if (isset($filters['year'])) $sql .= " AND `year` = ".$filters['year'];

		$this->db->select('a.*, 
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_joined, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name,
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			e.parent_id as position_category_id, i.position as position_category
		');

		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left')
				 
				 ->where('a.is_delete', 0)
				 ->where('a.id IN ('.$sql.')');

		if (isset($filters['department_id'])) $this->db->where('department_id', $filters['department_id']);

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

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

	function getUnableToAllocateUser($filters = array()) {
		$this->db->select('a.*, 
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_joined, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name,
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			e.parent_id as position_category_id, i.position as position_category
		');

		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left');
				 
		$this->db->where('a.is_delete', 0)
				 ->where('d.date_joined', null)
				 ->or_where('a.is_delete', 0)
				 ->where('d.position_id', false)
				 ->or_where('a.is_delete', 0)
				 ->where('d.department_id', false);

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

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

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

	function getUnallocatedUser($filters = array()) {
		$sql = "SELECT DISTINCT user_id FROM hr__leave_allocation WHERE is_delete = 0 AND type_id = 1";

		if (isset($filters['year'])) $sql .= " AND `year` = ".$filters['year'];

		$this->db->select('a.*, 
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_joined, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name,
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			e.parent_id as position_category_id, i.position as position_category
		');

		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left')

				 ->where('a.is_delete', 0)
				 ->where('d.date_joined !=', null)
				 ->where('d.position_id !=', false)
				 ->where('a.id NOT IN ('.$sql.')');

		if (isset($filters['department_id'])) $this->db->where('department_id', $filters['department_id']);

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

		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}
	
	function getUnallocatedUser2($filters = array()) {
		$sql = "SELECT DISTINCT user_id FROM hr__leave_allocation WHERE is_delete = 0 AND type_id = 1";

		if (isset($filters['year'])) $sql .= " AND `year` = ".$filters['year'];

		$this->db->select('a.*, 
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_joined, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name,
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			e.parent_id as position_category_id, i.position as position_category,
			al_prev.leave_entitled as prev_al,
			al_prev.leave_balance as prev_al_balance,
			al_prev2.leave_entitled as prev_al_last2year,
			co_prev.leave_balance as prev_co_balance,

			al.id as al_entitlement_id,
			al.month_of_service_add_leave as al_month_cycle, 
			al.additional_leave as al_additional_leave_per_cycle, 
			al.max_entitlement as al_max_entitlement, 
			al.bring_forward_percentage as al_bring_forward_percentage, 
			al.entitled_leave as al_entitled_leave,
			mc.id as mc_entitlement_id,
		');

		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left')
				 ->join('hr__leave_allocation al_prev', 'al_prev.type_id = 1 AND al_prev.is_delete = 0 AND a.id = al_prev.user_id AND al_prev.year = '.($filters['year'] - 1), 'left outer')
				 ->join('hr__leave_allocation al_prev2', 'al_prev2.type_id = 1 AND al_prev2.is_delete = 0 AND a.id = al_prev2.user_id AND al_prev2.year = '.($filters['year'] - 2), 'left outer')
				 ->join('hr__leave_allocation co_prev', 'co_prev.type_id = 8 AND al_prev.is_delete = 0 AND a.id = co_prev.user_id AND co_prev.year = '.($filters['year'] - 1), 'left')
				 ->join('hr__leave_entitlement al', 'al.type_id = 1 AND e.parent_id = al.category_id AND al.year = '.$filters['year'], 'left')
				 ->join('hr__leave_entitlement mc', 'mc.type_id = 3 AND e.parent_id = mc.category_id AND mc.year = '.$filters['year'], 'left') 

				 ->where('a.is_delete', 0)
				 ->where('d.date_joined !=', null)
				 ->where('d.position_id !=', false)
				 ->where('d.department_id !=', false)
				 ->where('a.id NOT IN ('.$sql.')')
				 ->order_by('a.name', 'asc');

		if (isset($filters['department_id'])) $this->db->where('department_id', $filters['department_id']);

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

	function getUserReview($filters = array()) {
		// $sql = "SELECT DISTINCT user_id FROM hr__leave_allocation WHERE is_delete = 0 AND type_id = 1";

		// if (isset($filters['year'])) $sql .= " AND `year` = ".$filters['year'];

		$this->db->select('a.*, 
			b.username,
			c.file_name, c.file_location, 
			d.id as employment_id, d.date_joined, d.date_commencement, d.date_termination,
			d.position_id, e.position as position_name,
			d.company_id, f.name as company_name, 
			g.branch_id, h.name as branch_name, 
			d.department_id, g.name as department_name,
			e.parent_id as position_category_id, i.position as position_category,

			al_prev.leave_entitled as prev_al_entitled, al_prev.leave_balance as prev_al_balance,
			co_prev.leave_entitled as prev_co_entitled, co_prev.leave_balance as prev_co_balance,
			un_prev.leave_taken as prev_un_taken,

			al.id as al_entitlement_id,
			al.month_of_service_add_leave as al_month_cycle, 
			al.additional_leave as al_additional_leave_per_cycle, 
			al.max_entitlement as al_max_entitlement, 
			al.bring_forward_percentage as al_bring_forward_percentage, 
			al.entitled_leave as al_entitled_leave,
		');

		$this->db->join('user__login b', 'b.user_id = a.id')
				 ->join('user__picture c', 'a.profile_picture_id = c.id', 'left')
				 ->join('user__employment_detail d', 'd.user_id = a.id', 'left')
				 ->join('company__position e', 'd.position_id = e.id', 'left')
				 ->join('company f', 'd.company_id = f.id', 'left')
				 ->join('company__department g', 'd.department_id = g.id', 'left')
				 ->join('company__branch h', 'g.branch_id = h.id', 'left')
				 ->join('company__position i', 'e.parent_id = i.id', 'left')
				 ->join('hr__leave_allocation al_prev', 'al_prev.type_id = 1 AND al_prev.is_delete = 0 AND a.id = al_prev.user_id AND al_prev.year = '.($filters['year'] - 1), 'left')
				 ->join('hr__leave_allocation co_prev', 'co_prev.type_id = 8 AND co_prev.is_delete = 0 AND a.id = co_prev.user_id AND co_prev.year = '.($filters['year'] - 1), 'left')
				 ->join('hr__leave_allocation un_prev', 'un_prev.type_id = 4 AND un_prev.is_delete = 0 AND a.id = un_prev.user_id AND un_prev.year = '.($filters['year'] - 1), 'left')
				 ->join('hr__leave_entitlement al', 'al.type_id = 1 AND e.parent_id = al.category_id AND al.year = '.$filters['year'], 'left')

				 ->where('a.is_delete', 0)
				//  ->where('d.date_joined !=', null)
				//  ->where('d.position_id !=', false)
				//  ->where('d.department_id !=', false)
				//  ->where('a.id NOT IN ('.$sql.')')
				 ->order_by('a.name', 'asc');

		if (isset($filters['department_id'])) $this->db->where('department_id', $filters['department_id']);

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

	function getDepartmentAllocationStatus($dept_id=null, $year=null) {
		$year = ($year && $this->validateDate($year, 'Y')) ? $year : date('Y');
		
		$sql_count_users = "SELECT COUNT(*) 
							FROM `user` `a` 
							JOIN `user__employment_detail` `b` ON `b`.`user_id` = `a`.`id` 
							JOIN `company__department` `c` ON `c`.`id` = `b`.`department_id` 
							WHERE `a`.`is_delete` = 0
							AND `b`.`status` = 0";
		if($dept_id) $sql_count_users .= " AND `b`.`department_id` = ".$dept_id;

		$sql_count_allocations = "SELECT COUNT(distinct `a`.`user_id`) 
								  FROM `hr__leave_allocation` `a` 
								  JOIN `user__employment_detail` `b` ON `b`.`user_id` = `a`.`user_id` 
								  JOIN `company__department` `c` ON `c`.`id` = `b`.`department_id` 
								  WHERE `a`.`is_delete` = 0
								  AND `a`.`year` = ".$year." 
								  AND `b`.`status` = 0";
		if($dept_id) $sql_count_allocations .= " AND `b`.`department_id` = ".$dept_id;
		// $sql_count_allocations .= " GROUP BY `a`.`user_id`";
		
		$sql = "SELECT (".$sql_count_users.") as count_users, (".$sql_count_allocations.") as count_allocations";
		$query = $this->db->query($sql);

		return ($query->num_rows() >= 1) ? $query->result_array()[0] : null; 
	}
	
	function getApplication($filters = array(), $limit=null, $mode='default') {
		log_message('error', 'getApplication');
		if ($mode == 'print') {
			$this->db->select('
				g.name as user_name,
				DATE_FORMAT(a.date_from, "%d-%m-%Y"), DATE_FORMAT(a.date_to," %d-%m-%Y"), a.days_applied,
				f.name as leave_type,
				e.status as leave_status,
				k.name as relief_person
			');
		} else {
			$this->db->select('
				a.*, 
				b.name as dept_name,
				l.name as branch_name,
				c.name as period_from,
				d.name as period_to,
				e.status as leave_status,
				f.name as leave_type, f.colour, f.code,
				g.name as user_name,
				h.file_name, h.file_location,
				k.name as relief_person
			');
		}
		
		$this->db->join('company__department b', 'b.id = a.department_id')
				 ->join('company__branch l', 'l.id = b.branch_id')
				 ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
				 ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
				 ->join('hr__ref_leave_status e', 'e.id = a.status')
				 ->join('hr__leave_type f', 'f.id = a.type_id')
				 ->join('user g', 'g.id = a.user_id')
				 ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
//akmal 20190509 these 2 lines join tables not is use in the select. should remove.  
//				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
//				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left') //farid 20190510 got error so need to re-open
				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user k', 'k.id = a.absent_relief_id');

		if(!empty($filters)){
			foreach ($filters as $filter => $value) {
				$this->db->where($filter, $value);
			}
		}
		
		$this->db->where('a.is_delete', 0);
		
		if ($mode == 'print') {
			$this->db->order_by('a.date_from', 'asc');
		} else {
			$this->db->order_by('a.status', 'asc');
			$this->db->order_by('a.created_ts', 'desc');
		}
		
		if (is_int($limit)) $this->db->limit($limit);
		$query = $this->db->get('hr__leave_application a');
		
		// return ($query->num_rows() > 0) ? (($query->num_rows() == 1) ? $query->result_array()[0] : $query->result_array()) : null;
		// echo $this->db->last_query(); 
		// die();
		// log_message('error', $this->db->last_query());
		// log_message('error', 'rows: ' . $query->num_rows());
		return ($query->num_rows() >= 1) ? $query->result_array() : null;  
	}

	function getApplication2($filters = array(), $limit=null, $mode='default') {
		if ($mode == 'print') {
			$this->db->select('
				g.name as user_name,
				DATE_FORMAT(a.date_from, "%d-%m-%Y"), DATE_FORMAT(a.date_to," %d-%m-%Y"), a.days_applied,
				f.name as leave_type,
				e.status as leave_status,
				k.name as relief_person
			');
		} else {
			$this->db->select('
				a.*, 
				b.name as dept_name,
				l.name as branch_name,
				c.name as period_from,
				d.name as period_to,
				e.status as leave_status,
				f.name as leave_type, f.colour, f.code,
				g.name as user_name,
				h.file_name, h.file_location,
				k.name as relief_person
			');
		}
		
		$this->db->join('company__department b', 'b.id = a.department_id')
				 ->join('company__branch l', 'l.id = b.branch_id')
				 ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
				 ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
				 ->join('hr__ref_leave_status e', 'e.id = a.status')
				 ->join('hr__leave_type f', 'f.id = a.type_id')
				 ->join('user g', 'g.id = a.user_id')
				 ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
//akmal 20190509 these 2 lines join tables not is use in the select. should remove.  
//				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
//				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user k', 'k.id = a.absent_relief_id', 'left');

		if(!empty($filters)){
			foreach ($filters as $filters) {
				$this->db->where($filters);
			}
		}
		
		$this->db->where('a.is_delete', 0);

		if ($mode == 'print') {
			$this->db->order_by('a.date_from', 'asc');
		} else {
			$this->db->order_by('a.status', 'asc');
			$this->db->order_by('a.created_ts', 'desc');
		}
		
		if (is_int($limit)) $this->db->limit($limit);
		$query = $this->db->get('hr__leave_application a');
//echo $this->db->last_query(); 
		// return ($query->num_rows() > 0) ? (($query->num_rows() == 1) ? $query->result_array()[0] : $query->result_array()) : null;
		return ($query->num_rows() >= 1) ? $query->result_array() : null;  
	}

	function getLeaveInfoManagers($filters = array()) {
		$sql = "SELECT COUNT(*) 
				FROM `hr__leave_application` `a` 
				LEFT JOIN `user__employment_detail` `b` ON `b`.`user_id` = `a`.`user_id` 
				LEFT JOIN `company__position` `c` ON `c`.`id` = `b`.`position_id` 
				WHERE `a`.`is_delete` = 0
				AND `c`.`parent_id` <  3";

		if(!empty($filters)) {
			foreach($filters as $filter => $value) {
				if($filter != 'status') {
					$sql .= " AND ".$filter." = ".$value;
				}
			}
		}

		$statuses = $this->getRefStatus();

		$this->db->select("(".$sql.") as leaveTotal");

		foreach($statuses as $status) {
			$this->db->select("(".$sql." AND `a`.`status` = ".$status['id'].") as leave".$status['status']);
		}

		$query = $this->db->get();
		return ($query->num_rows() >= 1 ) ? $query->result_array()[0] : false;
	}

	function getLeaveBetweenDate($user_id, $date_from, $date_to) {
		$this->db->select('a.*, b.name as leave_type');
		$this->db->join('hr__leave_type b', 'b.id = a.type_id');
		$this->db->where("('".$date_from."' BETWEEN a.date_from AND a.date_to) AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status NOT IN(3, 4)");
		$this->db->or_where("('".$date_to."' BETWEEN a.date_from and a.date_to) AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status NOT IN(3, 4)");
		$this->db->or_where("(a.date_from BETWEEN '".$date_from."' AND '".$date_to."') AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status NOT IN(3, 4)");
		$this->db->or_where("(a.date_to BETWEEN '".$date_from."' AND '".$date_to."') AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status NOT IN(3, 4)");
		// $this->db->where("('".$date_from."' BETWEEN a.date_from AND a.date_to) AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status = 2");
		// $this->db->or_where("('".$date_to."' BETWEEN a.date_from and a.date_to) AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status = 2");
		// $this->db->or_where("(a.date_from BETWEEN '".$date_from."' AND '".$date_to."') AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status = 2");
		// $this->db->or_where("(a.date_to BETWEEN '".$date_from."' AND '".$date_to."') AND a.user_id ='".$user_id."' AND a.is_delete = 0 AND a.status = 2");
		// select * from hr__leave_application as a inner join hr__leave_type as b on a.type_id = b.id where (('2022-01-24' between a.date_from and a.date_to) and a.user_id = 18)
		// or (('2022-01-25' between a.date_from and a.date_to) and a.user_id = 18)
		// or ((a.date_from between '2022-01-24' and '2022-01-25') and a.user_id = 18)
		// or ((a.date_to between '2022-01-24' and '2022-01-25') and a.user_id = 18)

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

		// log_message('error', 'leaves found: ' . $query->num_rows());
		
    	return ($query->num_rows() >= 1) ? $query->result() : null;    
	}

	function getLeaveBetweenDateWithFilters($date_from, $date_to, $filters=array()) {
		$this->db->select('
			a.*, 
			b.name as dept_name,
			c.name as period_from,
			d.name as period_to,
			e.status as leave_status,
			f.name as leave_type, f.colour, f.code,
			g.name as user_name,
			h.file_name, h.file_location,
			k.name as relief_person
		');
		
		$this->db->join('company__department b', 'b.id = a.department_id')
				 ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
				 ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
				 ->join('hr__ref_leave_status e', 'e.id = a.status')
				 ->join('hr__leave_type f', 'f.id = a.type_id')
				 ->join('user g', 'g.id = a.user_id')
				 ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
//akmal 20190509 these 2 lines join tables not is use in the select. should remove.  
//				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
//				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user k', 'k.id = a.absent_relief_id');
		
		$this->db->where('a.is_delete', 0);

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

		$this->db->group_start()
					->where("('".$date_from."' BETWEEN a.date_from AND a.date_to)")
					->or_where("('".$date_to."' BETWEEN a.date_from and a.date_to)")
					->or_where("(a.date_from BETWEEN '".$date_from."' AND '".$date_to."')")
					->or_where("(a.date_to BETWEEN '".$date_from."' AND '".$date_to."')")
				 ->group_end();

		$query = $this->db->get('hr__leave_application a');
		
    	return ($query->num_rows() >= 1) ? $query->result_array() : null;    
	}

	function getLeaveBetweenDateWithFilters2($date_from, $date_to, $filters=array()) {
		$this->db->select('
			a.*, 
			b.name as dept_name,
			c.name as period_from,
			d.name as period_to,
			e.status as leave_status,
			f.name as leave_type, f.colour, f.code,
			g.name as user_name,
			h.file_name, h.file_location,
			k.name as relief_person
		');
		
		$this->db->join('company__department b', 'b.id = a.department_id')
				 ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
				 ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
				 ->join('hr__ref_leave_status e', 'e.id = a.status')
				 ->join('hr__leave_type f', 'f.id = a.type_id')
				 ->join('user g', 'g.id = a.user_id')
				 ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
//akmal 20190509 these 2 lines join tables not is use in the select. should remove.  
//				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
//				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user k', 'k.id = a.absent_relief_id');
		
		$this->db->where('a.is_delete', 0);

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

		$this->db->group_start()
					->where("('".$date_from."' BETWEEN a.date_from AND a.date_to)")
					->or_where("('".$date_to."' BETWEEN a.date_from and a.date_to)")
					->or_where("(a.date_from BETWEEN '".$date_from."' AND '".$date_to."')")
					->or_where("(a.date_to BETWEEN '".$date_from."' AND '".$date_to."')")
				 ->group_end();

		$query = $this->db->get('hr__leave_application a');
		
    	return ($query->num_rows() >= 1) ? $query->result_array() : null;    
	}

	function getStatus($date, $filters=array()) {
		$this->db->select('
			a.*, 
			b.name as dept_name,
			c.name as period_from,
			d.name as period_to,
			e.status as leave_status,
			f.name as leave_type, f.colour, f.code,
			g.name as user_name,
			h.file_name, h.file_location,
			k.name as relief_person
		');
		
		$this->db->join('company__department b', 'b.id = a.department_id')
				 ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
				 ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
				 ->join('hr__ref_leave_status e', 'e.id = a.status')
				 ->join('hr__leave_type f', 'f.id = a.type_id')
				 ->join('user g', 'g.id = a.user_id')
				 ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
//akmal 20190509 these 2 lines join tables not is use in the select. should remove.  
//				 ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
//				 ->join('company__position j', 'i.position_id = j.id', 'left')
				 ->join('user k', 'k.id = a.absent_relief_id');

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

		$this->db->group_start()
					->where("('".$date."' BETWEEN a.date_from AND a.date_to)")
					->or_where("(a.date_from BETWEEN '".$date."' AND '".$date."')")
					->or_where("(a.date_to BETWEEN '".$date."' AND '".$date."')")
				 ->group_end();

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

		die(debug(($query->num_rows() >= 1) ? $query->result_array() : null));
		
    	return ($query->num_rows() >= 1) ? $query->result_array() : null;
	}
	
	function getLeaveTaken($user_id, $date_from, $date_to, $type_id = null) {
		// if same month
		if (date('Y-m', strtotime($date_from)) == date('Y-m', strtotime($date_to))) {
			$this->db->select('*')
					 ->where("(date_from BETWEEN '".date('Y-m-01', strtotime($date_to))."' AND LAST_DAY('".$date_to."'))")
					 ->where('user_id', $user_id)
					 ->where('is_delete = 0 AND status IN(1, 2) AND type_id IN(1, 2)');
			
			$query = $this->db->get('hr__leave_application');
			return ($query->num_rows() >= 1) ? $query->result() : null;

		} else {
			$monthFrom = $this->db->select('*')
								  ->where("(date_from BETWEEN '".date('Y-m-01', strtotime($date_from))."' AND LAST_DAY('".$date_from."'))")
								  ->where('user_id', $user_id)
								  ->where('is_delete = 0 AND status IN(1, 2) AND type_id IN(1, 2)')
								  ->get('hr__leave_application');
			
			$monthTo   = $this->db->select('*')
								  ->where("(date_from BETWEEN '".date('Y-m-01', strtotime($date_to))."' AND LAST_DAY('".$date_to."'))")
								  ->where('user_id', $user_id)
								  ->where('is_delete = 0 AND status IN(1, 2) AND type_id IN(1, 2)')
								  ->get('hr__leave_application');

			$fromLeave = ($monthFrom->num_rows() >= 1) ? $monthFrom->result() : null;
			$toLeave 	 = ($monthTo->num_rows() >= 1) ? $monthTo->result() : null;

			return array(
				'from' => $fromLeave,
				'to' 	 => $toLeave
			);
		}
	}
	
	function getLeaveUntilMonth($user_id, $date_to) {
		$year = date('Y', strtotime($date_to));

		$this->db->select('*')
						 ->where("(date_from BETWEEN '".date($year.'-01-01')."' AND LAST_DAY('".$date_to."'))")
						 ->where('user_id', $user_id)
						 ->where('is_delete = 0 AND status = 2 AND type_id IN(1, 2)');
		
		$query = $this->db->get('hr__leave_application');
		return ($query->num_rows() >= 1) ? $query->result() : null;
	}

	function getLeaveForTheYear($user_id, $year, $type_id = null) {
		$this->db->select('*')
						 ->where("(date_from BETWEEN '".date($year.'-01-01')."' AND '".date($year.'-12-31')."')")
						 ->where('user_id', $user_id)
						 ->where('is_delete = 0 AND status = 2');
		($type_id) ? $this->db->where('type_id', $type_id) : $this->db->where('type_id IN(1, 2)');
		
		$query = $this->db->get('hr__leave_application');
		return ($query->num_rows() >= 1) ? $query->result() : null;
	}

	function getApplicationTransactions($filters = array()) {
		$this->db->select('*');

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

		$this->db->where('is_delete', 0);
		$this->db->order_by('created_ts', 'desc');
		$query = $this->db->get('hr__leave_transaction');

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

	function getLeaveInfo($filters = array()) {
//		$sql = "SELECT COUNT(*) FROM `hr__leave_application` WHERE `is_delete` = 0";
//akmal 20190326 change from counting no of application to number of days
		$sql = "SELECT ifnull(sum(days_applied),0) FROM `hr__leave_application` WHERE `is_delete` = 0";
//		var_dump($filters); 
		if(!empty($filters)) {
			foreach($filters as $filter => $value) {
				if($filter != 'status') {
					$sql .= " AND ".$filter.$value;
				}
			}
		}

		if(isset($filters['status'])) {
			$sql .= " AND `status` = ".$filters['status'];

			$status = $this->getRefStatus($filters['status']);

			$this->db->select("(".$sql.") as leave".$status['leaveStatus']);
		} else {
			$statuses = $this->getRefStatus();

			$this->db->select("(".$sql.") as leaveTotal");

			foreach($statuses as $status) {
				$this->db->select("(".$sql." AND `status` = ".$status['id'].") as leave".$status['status']);
			}
		}

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

		return ($query->num_rows() >= 1 ) ? $query->result_array()[0] : false;
	}

	function getLeaveInfoByPosition($filters = array()) {
		$sql = "SELECT COUNT(*) 
						FROM `hr__leave_application` `a` 
						LEFT JOIN `user__employment_detail` `b` ON `b`.`user_id` = `a`.`user_id` 
						LEFT JOIN `company__position` `c` ON `c`.`id` = `b`.`position_id` 
						LEFT JOIN `company__position` `d` ON `d`.`id` = `c`.`parent_id` 
						WHERE `a`.`is_delete` = 0";

		if(isset($filters['position_id'])) {
			$sql .= " AND `b`.`position_id` = ".$filters['position_id'];
		}

		if(isset($filters['category_id'])) {
			$sql .= " AND `d`.`id` = ".$filters['category_id'];
		}

		if(isset($filters['status'])) {
			$sql .= " AND `a`.`status` = ".$filters['status'];

			$status = $this->getRefStatus($filters['status']);

			$this->db->select("(".$sql.") as leave".$status['leaveStatus']);
		} else {
			$statuses = $this->getRefStatus();

			$this->db->select("(".$sql.") as leaveTotal");

			foreach($statuses as $status) {
				$this->db->select("(".$sql." AND `a`.`status` = ".$status['id'].") as leave".$status['status']);
			}
		}

		$query = $this->db->get();
		return ($query->num_rows() >= 1 ) ? $query->result_array()[0] : false;
	}

	function getLeaveInfoStaffs($dept_id = null, $year=null) {
		$sql = "SELECT COUNT(*) 
				FROM `hr__leave_application` `a` 
				LEFT JOIN `user__employment_detail` `b` ON `b`.`user_id` = `a`.`user_id` 
				LEFT JOIN `company__position` `c` ON `c`.`id` = `b`.`position_id` 
				WHERE `a`.`is_delete` = 0
				AND `c`.`parent_id` >  2".(($dept_id) ? " AND `b`.`department_id` = ".$dept_id : "").(($year) ? " AND year(`a`.`date_from`) = ".$year : "");


		$statuses = $this->getRefStatus();

		$this->db->select("(".$sql.") as leaveTotal");

		foreach($statuses as $status) {
			$q = "(".$sql." AND `a`.`status` = ".$status['id'].") as leave".$status['status'];
			$this->db->select($q);
		}

		$query = $this->db->get();
		return ($query->num_rows() >= 1 ) ? $query->result_array()[0] : false;
	}
	
	function getApprovePerson($department_id) {
		$this->db->select('a.*');
		
		$this->db->join('user__employment_detail b', 'b.user_id = a.id')
						 ->join('company__position c', 'c.id = b.position_id');

		$this->db->where('a.is_delete', 0);
		$this->db->where('b.department_id', $department_id);
		$this->db->where('c.parent_id <', 3);
		$this->db->or_where('a.id', 1);

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

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

	function getLeaveType($id=null) {
		$this->db->select('*')
						 ->where('is_delete', 0);

		if($id) $this->db->where('id', $id);
		
		$query = $this->db->get('hr__leave_type');

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

	function getTypes() {
		$query = $this->db->query('SELECT * FROM hr__leave_type WHERE is_delete = 0 AND id NOT IN (2, 4)');

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

	function getRequest($filters = array()) {
		// log_message('error', 'getRequest');

		$this->db->select('a.*, b.name as type');
		$this->db->where('a.is_delete', 0);
		
		$this->db->join('hr__leave_type b', 'a.type_id = b.id');

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

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

		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}
	
	function getMajorLeaveTypes($filters=array()) {
		$this->db->select('*')
				 ->where('is_delete', 0);

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

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

	function getRefStatus($id=null) {
		$this->db->select('*');

		if($id) $this->db->where('id', $id);

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

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

	function getRefPeriod($id=null) {
		$this->db->select('*');

		if($id) $this->db->where('id', $id);

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

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

	function validateDate($date, $format = 'Y-m-d H:i:s')
	{
		$d = DateTime::createFromFormat($format, $date);
		return $d && $d->format($format) == $date;
	}


	function getEntitlementReviewReport($year=0){
		$previous_year = $year-1; 
		
		$query = $this->db->query('SELECT a.id, a.name, d.position as position_name, c.name as department_name, f.name as branch_name ,
			l1.leave_entitled as al_previous_year_entt, l1.current_balance as al_previous_year_bal,
			l2.entitled_leave as al_current_year_entt,

			l3.leave_entitled as mc_previous_year_entt, l3.leave_balance as mc_previous_year_bal,
			l4.leave_entitled as mc_current_year_entt, b.date_joined, d.parent_id as position_category_id , e.position as position_category 
			 
			FROM ((((((((`user` a 
			left outer join user__employment_detail b on b.user_id = a.id)
			left outer join company__department c on c.id = b.department_id)
			left outer join company__position d on d.id = b.position_id)
			left outer join company__branch f on f.id = c.branch_id)
			left outer join view_leave_summary l1 on a.id = l1.user_id and l1.year = '.$previous_year.' and l1.type_id = 1 )
			left outer join hr__leave_entitlement l2 on l2.category_id = b.id and l2.year = '.$year.' and l2.type_id = 1 and l2.is_delete = 0)
			left outer join hr__leave_allocation l3 on a.id = l3.user_id and l3.year = '.$previous_year.' and l3.type_id = 3 and l3.is_delete = 0)
			left outer join hr__leave_allocation l4 on a.id = l4.user_id and l4.year = '.$year.' and l4.type_id = 3 and l4.is_delete = 0),

			company__position e where e.id = d.parent_id and a.is_delete = 0

			order by a.name asc
		'); 
			
		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}

//akmal 20190524
	function getLeaveStatusByDepartment($deptID =0, $filters = array(),$date_from,$date_to){
		if($deptID == 0) return NULL; 
		if($date_to) $select = "(select status from hr__leave_application where is_delete = 0 and user_id = a.user_id and status in (1,2) and (('".$date_from."' BETWEEN date_from AND date_to ) or ('".$date_to."' BETWEEN date_from and date_to) or (date_from BETWEEN '".$date_from."' AND '".$date_to."') or (date_to BETWEEN '".$date_from."' AND '".$date_to."') ) limit 1) as status";
		else $select = "(select status from hr__leave_application where is_delete = 0 and user_id = a.user_id and status in (1,2) and (('".$date_from."' BETWEEN date_from AND date_to ) ) limit 1) as status"; 
		$this->db->select('a.user_id as id, a.user_id, a.staff_id, a.name, c.file_name, c.file_location ')
				->select($select,false)
				->where('a.department_id',$deptID)
				->where('a.is_delete',0)
				->join('user u','a.user_id = u.id','left')
				->join('user__picture c', 'u.profile_picture_id = c.id', 'left',false);
				
					
		if(!empty($filters)){
			foreach ($filters as $filter => $value) {
				$this->db->where($filter, $value);
			}
		}
		
		return $this->db->get('user__employment_detail a')->result_array();
	}
//akmal 20191226
	function getLeaveBalance($filters = array()){
		foreach($filters as $key => $val) 
			$this->db->where($key,$val);
		$leaveType	= (isset($filters['type_id']))?$filters['type_id']:0; 
		$balance = array('balance'=>0, 'mesg' => '');
		switch ($filters['type_id']) {
			case 1:
			case 2: 
				$rs = $this->db->get('view_leave_summary')->result_array()[0];
				if($rs) {
					$balance['balance'] = $rs['current_balance']; 
					$balance['method'] = 'current_balance'; 
					$balance['current_balance'] = $rs['current_balance']; 
				} else $balance['mesg'] = 'No AL Allocation:'.$this->db->last_query();
				$balance['rs'] = $rs;
				break; 
			case 8: //UPDATED farid 20211213 for conversion leave no entitlement so need to take leave balance
				$rs = $this->db->get('view_leave_balance')->result_array()[0];
				if($rs) {
					$balance['balance'] = $rs['leave_balance'] - $rs['total_applied']; 				
					$balance['method_leave_entitled'] = $rs['leave_entitled']; 
					$balance['method_total_applied'] = $rs['total_applied']; 
				}else $balance['mesg'] = 'No Allocation';
				$balance['rs'] = $rs;
				break; 
			default:
				$rs = $this->db->get('view_leave_balance')->result_array()[0];
				if($rs) {
					//$balance['balance'] = $rs['leave_balance'] - $rs['total_applied']; //akmal. isu cuti belum approve, balance x betul. 
					$balance['balance'] = $rs['leave_entitled'] - $rs['total_applied'];						
					$balance['method_leave_entitled'] = $rs['leave_entitled']; 
					$balance['method_total_applied'] = $rs['total_applied']; 
				}else $balance['mesg'] = 'No Allocation';
				$balance['rs'] = $rs;
				break; 
		}
		$balance['sql'] = $this->db->last_query();
	return $balance;		
	}
	
	/*public function akmal_get_total_leave_applied($b4april=true,
				$year=date('Y'),
				$user_id=0,
				$type_id=0){
		if($b4april == true)
			$this->db->where('month(date_from) < 4');
		else $this->db->where('month(date_from) > 3');
		if($type_id == 1 || $type_id == 2)
			$this->db->where_in('type-id',array(1,2));
		else $this->db->where('type_id',$type_id);
		$rs = $this->db->select('sum(days_applied) as total')
				->where('year',$year)
				->where('user_id',$user_id)
				->get('hr__leave_application')
				->result_array();
		if(!empty($rs)) return $rs[0]['total'];
		else return 0;		
	}
	public function akmal_get_leave_allocation($param =array('year'=>date('Y'),
				'user_id'=>0,
				'type_id'=0)){
		if($param['type_id'] >0)		//if type==0 will include all type
			$this->db->where('type_id',$param['type_id']);
		
		return $this->db->where('year',$param['year'])
				->where('user_id',$param['user_id'])
				->get('hr__leave_allocation')
				->result_array();
	}
	
	public function akmal_get_carryforward($param = array()){ 
	//carry forward is only applicapble for annual leave or leave type = 1
		if(empty($param)) return 0;
		foreach($param as $row){
			if(isset($row['type_id']) && $row['type_id'] == 1) return $row['bring_forward_entitled'];
		}
		return 0; 
	}*/

	public function get_study_leave_by_user($user_id)
	{
		$this->db->select('a.*, b.id, b.staff_id, b.programme, b.paper_code, b.title, b.exam_date, b.status as exam_status, b.result')
				->join('hr__exam b', 'b.id = a.exam_id AND b.is_delete = 0', 'left outer')
				->where('a.user_id', $user_id)
				->where('a.type_id', 7)
				->where('a.status IN (1,2)')
				->where('a.is_delete', 0);

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

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

	/**
	 * v3. old. file-based
	 * check if ph-YYYY-MM-DD.json exists, 
	 * if exists, check that json valid
     * if file does not exists, request from api, create json file from api response
	 * - pending: create a super version of a json that will be prioritized more in fetching holiday data OR supplemental json file that override whatever fetched
	 * - pending: add another day of holiday if one falls on sunday
	 */
	// public function getPublicHoliday($branchState){
	// 	// log_message('error', 'getPublicHoliday');

	// 	$gApiKey = 'AIzaSyAQU6RJSPKvQisT9Xm3FdJ2q9lLz7dSmeE';
	// 	$gCalUrl = 'https://www.googleapis.com/calendar/v3/calendars/en.malaysia%23holiday%40group.v.calendar.google.com/events?key=';

	// 	$currentYear = date('Y');
	// 	$jsonFolder = 'application/storage/json/';
	// 	$fileName = 'ph-' . $currentYear;
	// 	$filePath = $jsonFolder .$fileName . '.json';

	// 	$stateHolidays = array();

	// 	$this->load->helper('file');

	// 	// check if holiday file exists
	// 	if(!file_exists($filePath)){
	// 		// log_message('error', 'File does not exists. Fetching data from API');

	// 		// curl google calendar api
	// 		$c = curl_init();
	// 		curl_setopt( $c , CURLOPT_URL , $gCalUrl . $gApiKey);
	// 		curl_setopt( $c , CURLOPT_USERAGENT, "Mozilla/5.0 (Linux Centos 7;) Chrome/74.0.3729.169 Safari/537.36");
	// 		curl_setopt( $c , CURLOPT_RETURNTRANSFER, true);
	// 		curl_setopt( $c , CURLOPT_SSL_VERIFYPEER, false);
	// 		curl_setopt( $c , CURLOPT_SSL_VERIFYHOST, false);
	// 		curl_setopt( $c , CURLOPT_TIMEOUT, 10000); // 10 sec
	// 		$data = curl_exec($c);
	// 		curl_close($c);

	// 		// write data to file
	// 		if(! write_file($filePath, $data)){
	// 			// log_message('error', 'Unable to write file');
	// 		}else{
	// 			// log_message('error', 'File written');
	// 		}

	// 	}

	// 	// decode and process data
	// 	$decodedData = json_decode(read_file($filePath));
	// 	$holidays = $decodedData->items;
	// 	// log_message('error', 'holidays count: ' . count($holidays));
	// 	foreach($holidays as $h){
	// 		if(is_numeric(stripos($h->description, "public")) && (date('Y', strtotime($h->start->date)) == $currentYear) && 
	// 			(is_numeric(stripos($h->description, $branchState)) || (!stripos($h->description, 'in')))){
	// 			array_push($stateHolidays, $h);
	// 		}
	// 	}
	// 	// log_message('error', 'real holidays count: ' . count($stateHolidays));

	// 	// check if holiday adjustment exists

	// 	return $stateHolidays;
	// }

	public function getPublicHoliday2($cId, $sId, $y) {
        // log_message('error', 'getPublicHoliday2()');
        // log_message('error', 'getting holidays for: ' . $cId . ' ' . $sId . ' ' . $y);
        $this->db->select('b.*');
        $this->db->where('b.active', 1);
        $this->db->where('b.is_delete', 0);
        $this->db->where('b.country_id', $cId);
        $this->db->where('b.year', $y);
        $this->db->order_by('b.original_start_date', 'asc');
        $query = $this->db->get('hr__holiday b');

        // log_message('error', $this->db->last_query());
        // log_message('error', 'count2: ' . $query->num_rows() . ' ' . count($query->result_array()));
        return ($query->num_rows() > 0) ? $query->result_array() : null;
    }

    /**
     * v3. db-based
     */
    public function getPublicHoliday($branchState) {
        // log_message('error', 'getPublicHoliday()');

        $gApiKey = 'AIzaSyAQU6RJSPKvQisT9Xm3FdJ2q9lLz7dSmeE';
        $gCalUrl = 'https://www.googleapis.com/calendar/v3/calendars/en.malaysia%23holiday%40group.v.calendar.google.com/events?key=';

        $currentYear = date('Y');
        $jsonFolder = 'application/storage/json/';
        $fileName = 'ph-' . $currentYear;
        $filePath = $jsonFolder . $fileName . '.json';

        $stateHolidays = array();

        $this->load->helper('file');

        // check if holiday file exists
        if (!file_exists($filePath)) {
            // log_message('error', 'File does not exists. Fetching data from API');

            // curl google calendar api
            $c = curl_init();
            curl_setopt($c, CURLOPT_URL, $gCalUrl . $gApiKey);
            curl_setopt($c, CURLOPT_USERAGENT, "Mozilla/5.0 (Linux Centos 7;) Chrome/74.0.3729.169 Safari/537.36");
            curl_setopt($c, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($c, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($c, CURLOPT_SSL_VERIFYHOST, false);
            curl_setopt($c, CURLOPT_TIMEOUT, 10000); // 10 sec
            $data = curl_exec($c);
            curl_close($c);

            // write data to file
            if (!write_file($filePath, $data)) {
                log_message('error', 'Unable to write file');
            } else {
                log_message('error', 'File written');
            }
        }

        // decode and process data
        $decodedData = json_decode(read_file($filePath));
        $holidays = $decodedData->items;
        // log_message('error', 'holidays count: ' . count($holidays));
        foreach ($holidays as $h) {
            if (is_numeric(stripos($h->description, "public")) && (date('Y', strtotime($h->start->date)) == $currentYear) &&
                    (is_numeric(stripos($h->description, $branchState)) || (!stripos($h->description, 'in')))) {
                array_push($stateHolidays, $h);
            }
        }
        // log_message('error', 'real holidays count: ' . count($stateHolidays));

        // check if holiday adjustment exists

        return $stateHolidays;
    }

    // from v2
    function getAllocationCurrentYear($filters = array(), $exclude_emergency_and_unpaid = false) {
        // log_message('error', 'getAllocationCurrentYear');
        $this->db->select('a.*, sum(ifnull(b.days_applied,0)) as leave_taken, c.name,c.code, 
			if(c.is_prorate =1,(a.leave_entitled / 12 * month(now()) - sum(ifnull(b.days_applied,0))),a.leave_balance) as balance_entitle');

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

        $this->db->where('a.is_delete', 0)
                ->where('a.expiry_date > now()');
        $this->db->join('hr__leave_application b', 'a.user_id = b.user_id and a.type_id = b.type_id and b.is_delete =0 and a.id = b.allocation_id', 'left outer')
                ->join('hr__leave_type c', 'a.type_id = c.id');
        $this->db->group_by('a.type_id, a.year')
                ->order_by('a.year', 'desc')
                ->order_by('a.type_id', 'asc');

        $started = microtime(true);
        $query = $this->db->get('hr__leave_allocation a');
        $end = microtime(true);

        // log_message('error', $this->db->last_query());
        // log_message('error', number_format($end - $started, 10));
        return ($query->num_rows() > 0) ? (($query->num_rows() == 1) ? $query->result_array()[0] : $query->result_array()) : null;
    }

    // v3
    public function getOverlappingApplication($activeDate) {
        // log_message('error', 'getOverlappingApplication()');
        $q = $this->db->query('
			SELECT *
			FROM hr__leave_application
			WHERE is_delete=0
			AND year(date_from) = ?
			AND (month(date_from) = ?
			OR month(date_to) = ?)
			ORDER BY status ASC, created_ts DESC
		', array(date('Y', strtotime($activeDate)), date('n', strtotime($activeDate)), date('n', strtotime($activeDate))));

        // log_message('error', $this->db->last_query());
        // log_message('error', 'rows: ' . count($q->result_array()));
        return $q->result_array();
    }

    // sn. 
    function getApplication3($filters = array(), $limit = null, $mode = 'default') {
        // log_message('error', 'getApplication3');
        if ($mode == 'print') {
            $this->db->select('
				g.name as user_name,
				DATE_FORMAT(a.date_from, "%d-%m-%Y"), DATE_FORMAT(a.date_to," %d-%m-%Y"), a.days_applied,
				e.status as leave_status,
				k.name as relief_person
			');
        } else {
            $this->db->select('
				a.*, 
				b.name as dept_name,
				l.name as branch_name,
				c.name as period_from,
				d.name as period_to,
				e.status as leave_status,
				g.name as user_name,
				h.file_name, h.file_location,
				k.name as relief_person,
                y.name as updated_by_name,
                z.name as leave_type
			');
        }

        $this->db->join('company__department b', 'b.id = a.department_id')
                ->join('company__branch l', 'l.id = b.branch_id')
                ->join('hr__ref_leave_period c', 'c.id = a.period_from_id')
                ->join('hr__ref_leave_period d', 'd.id = a.period_to_id')
                ->join('hr__ref_leave_status e', 'e.id = a.status')
                ->join('user g', 'g.id = a.user_id')
                ->join('hr__leave_attachment h', 'h.application_id = a.id', 'left')
                ->join('user__employment_detail i', 'a.user_id = i.user_id', 'left')
                ->join('company__position j', 'i.position_id = j.id', 'left')
                ->join('user k', 'k.id = a.absent_relief_id')
                ->join('user y', 'y.id = a.updated_by', 'left outer')
                ->join('hr__leave_type z', 'z.id = a.type_id');

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

        $this->db->where('a.is_delete', 0);

        if ($mode == 'print') {
            $this->db->order_by('a.date_from', 'asc');
        } else {
            $this->db->order_by('a.status', 'asc');
            $this->db->order_by('a.created_ts', 'desc');
        }

        if (is_int($limit))
            $this->db->limit($limit);
        $query = $this->db->get('hr__leave_application a');

        // log_message('error', $this->db->last_query());
        // log_message('error', 'rows: ' . $query->num_rows());
        return ($query->num_rows() >= 1) ? $query->result_array() : array();
    }

    // sn@24022022. added peper id, completion flag if from, to or expiry date is null/invalid
    function getAllocation2($filters = array(), $exclude_emergency_and_unpaid = false) {
        // log_message('error', 'getAllocation2');
        $this->db->select('b.id as type_id, b.name, b.code, b.colour, b.app_require_attachment, b.require_allocation_value, b.require_exam, b.include_holiday, b.is_fixed, b.is_prorate, b.is_pre_application, b.cooling_period, b.is_bring_forward, 
			b.bring_forward_percent, b.forward_leave_type_id, b.function_to_call, b.increment_period, b.increment_value,
			a.id as allocation_id, a.user_id, a.entitlement_id, a.year, a.leave_entitled, a.leave_taken, a.leave_balance, 
			a.bring_forward_entitled, a.bring_forward, a.balance_entitled, a.date_from, a.date_to, a.expiry_date, a.paper_id,
                        if(a.date_from is null or a.date_from = "0000-00-00", 0, if(a.date_to is null or a.date_to = "0000-00-00", 0, 
                        if(a.expiry_date is null or a.expiry_date = "0000-00-00", 0, 1))) as alloc_complete');

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

        $this->db->join('hr__leave_type b', 'b.id = a.type_id');
        $this->db->where('a.is_delete', 0);
        $this->db->where('b.is_delete', 0);
        $this->db->where_not_in('b.id', array(2)); // filter emergency leave if accidentally allocated
        $this->db->order_by('b.name', 'asc');

        $result = $this->db->get('hr__leave_allocation a')->result_array();
        
        // log_message('error', $this->db->last_query());

        return (count($result) > 0) ? $result : [];
    }

	// sn. 21062022 - improved query to show correct employment data - position/grade/dept
	function getStaffAllocationSummaryV2a($year, $type, $department, $position, $status){
		$q = 'select 
			hla.year, u.id, u.staff_id, u.name, 
			(
			select max(up.gender_id) from user__profiling up where up.is_delete = 0 and up.user_id = u.id
			) as gender_id,
			(
			SELECT GROUP_CONCAT(hla2.id) FROM hr__leave_allocation hla2  where hla2.user_id = u.id and hla2.year=hla.year and is_delete=0 and type_id not in(2)
			) as allocation_id,
			(
			SELECT GROUP_CONCAT(distinct hla3.type_id) FROM hr__leave_allocation hla3  where hla3.user_id = u.id and hla3.year=hla.year and is_delete=0 and type_id not in(2)
			) as type_id,
			hla.type_id, hla.leave_entitled,
			hla.created_ts as allocation_time, hla.updated_ts as allocation_updated,
			(
			select vp.`position_group_id`  from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as position_group_id,
			(
			select vp.`position`  from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as position_name,
			(
			select vp.grade from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as grade,
			(
			select vp.company from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as company_name,
			(
			select vp.branch from view__position vp where vp.user_id = u.id
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as branch_name,
			(
			select vp.department_id from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as department_id,
			(
			select vp.department from view__position vp where vp.user_id = u.id
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as department_name,
			(
			select vp.`date_join`  from view__position vp where vp.user_id = u.id
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1) as position_start,
			hla.id as curr_allocation_id,
			hla.date_from as curr_allocation_date_from,
			hla.date_to as curr_allocation_date_to,
			hla.leave_entitled as curr_leave,
			hla.leave_taken as curr_taken,
			hle.id as entitlement_id,
			hle.month_of_service_add_leave as leave_month_cycle,
			hle.additional_leave as leave_additional_leave_per_cycle,
			hle.max_entitlement as leave_max_entitlement,
			hle.bring_forward_percentage as leave_bring_forward_percentage,
			hle.entitled_leave as entitled_leave,
			(
			select max(up3.action_efective_date) from user__profiling up3  where up3.is_delete = 0 and up3.user_id = u.id and up3.action_type_id in (12,13,14)
			) as last_resigned,
			(
			SELECT GROUP_CONCAT(ujg2.date_join) from user__joining_group ujg2  inner join user__profiling up4  on ujg2.profiling_id = up4.id  
			where up4.user_id = u.id and up4.is_delete=0 and ujg2.is_delete = 0 and up4.action_type_id in (5) order by ujg2.date_join asc
			) as possin_dates,
			(
			SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg  inner join user__profiling up2 on ujg.profiling_id = up2.id  
			where up2.user_id = u.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (0,1) order by ujg.date_join asc
			) as in_dates
			from `user` u
			left join hr__leave_allocation hla on u.id = hla.user_id and hla.is_delete = 0 and hla.type_id = ? and hla.`year` = ?
			left join hr__leave_entitlement hle on hle.type_id = hla.type_id and hle.year = hla.year 
			and hle.category_id = (
			select vp.`position_group_id`  from view__position vp where vp.user_id = u.id 
			and vp.date_join <= if(hla.updated_ts is null,
			(if(hla.created_ts is null, concat(?, "-12-31"), if(year(hla.created_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.created_ts) = hla.year, hla.created_ts, concat(hla.year,"-01-01"))))),
			(if(year(hla.updated_ts) > hla.year, concat(hla.year, "-12-31"), if(year(hla.updated_ts) = hla.year, hla.updated_ts , concat(hla.year,"-01-01"))))
			) order by vp.date_join  desc limit 1
			)
			where u.is_delete = 0
			and u.status = 0';
		$qParams = array($year,$year,$year,$year,$year,$year,$year,$year,$type, $year, $year);
		$filterC = 0;
		if($department || $position || $status){
			$q .= ' HAVING ';
		}
		if($department){
			if($filterC > 0) $q .= ' AND ';
			$q .= 'department_id = ?';
			array_push($qParams, $department);
			$filterC++;
		}
		if($position){
			if($filterC > 0) $q .= ' AND ';
			$q .= 'position_group_id = ?';
			array_push($qParams, $position);
			$filterC++;
		}
		if($status == 1){
			if($filterC > 0) $q .= ' AND ';
			$q .= 'curr_leave is not null';
			$filterC++;
		}
		if($status == 2){
			if($filterC > 0) $q .= ' AND ';
			$q .= 'curr_leave is null';
			$filterC++;
		}        
		$q .= ' ORDER BY name ASC';
		
		$result = $this->db->query($q, $qParams)->result_array();
		return $result;
	}
	
	// NEW. changes: 12/13/14 - resign, 0/1 - in, 5 - possin
	function getStaffAllocationSummary($year, $type, $department, $position, $status){
		// log_message('error', 'getStaffAllocation()');
		
		$q = 'SELECT a.id, a.staff_id, d.gender_id, a.name, 
				(SELECT GROUP_CONCAT(j.id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as allocation_id,
				(SELECT GROUP_CONCAT(distinct j.type_id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as type_id,
				-- d.date_joined, 
				-- `d`.`date_commencement`, 
				-- `d`.`date_termination`, 
				`reg`.`type` as `employee_group_name`,
				`rect`.`type` as `employee_contract_type_name`,
				`d`.`position_id`, `e`.`position` as `position_name`, `d`.`company_id`, `f`.`name` as `company_name`, 
				`g`.`branch_id`, `h`.`name` as `branch_name`, `d`.`department_id`, `g`.`name` as `department_name`, e.parent_id as position_parent_id, `i`.`parent_id` as `position_category_id`, 
				`i`.`position` as `position_category`,
				curr_alloc.id as curr_allocation_id,
                                curr_alloc.date_from as curr_allocation_date_from,
                                curr_alloc.date_to as curr_allocation_date_to,
				curr_alloc.leave_entitled as curr_leave,
				curr_alloc.leave_taken as curr_taken,
				al_prev1.leave_entitled as prev1_leave,
				al_prev1.leave_balance as prev1_leave_balance,
				al_prev2.leave_entitled as prev2_leave,
				co_prev.leave_balance as prev_co_balance,
				el.id as entitlement_id,
				el.month_of_service_add_leave as leave_month_cycle,
				el.additional_leave as leave_additional_leave_per_cycle,
				el.max_entitlement as leave_max_entitlement,
				el.bring_forward_percentage as leave_bring_forward_percentage,
				el.entitled_leave as entitled_leave,
				up.marital_status, up.married_date,
				(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation=3) as spouse_count,
				(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation=2) as children_count,
				(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation not in (2,3)) as other_family_count,
				(select count(id) from hr__exam where year(exam_date)=? and is_delete=0 and staff_id=a.id) as exam_count,
				-- (
				-- select max(ujg2.date_join) from user__joining_group ujg2 inner join user__profiling up2 on up2.id = ujg2.profiling_id
				-- where ujg2.is_delete = 0 and up2.is_delete = 0 and up2.user_id = a.id and up2.action_type_id = 12
				-- ) as last_resigned,
				(
				select max(up2.action_efective_date) from user__profiling up2 where up2.is_delete = 0 and up2.user_id = a.id and up2.action_type_id in (12,13,14)
				) as last_resigned,
				(
				SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
				where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (5) order by ujg.date_join asc
				) as possin_dates,
				(
				SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
				where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (0,1) order by ujg.date_join asc
				) as in_dates
				FROM `user` `a`
				LEFT JOIN `user__employment_detail` `d` ON `d`.`user_id` = `a`.`id`
				LEFT JOIN `company__position` `e` ON `d`.`position_id` = `e`.`id`
				LEFT JOIN `company` `f` ON `d`.`company_id` = `f`.`id`
				LEFT JOIN `company__department` `g` ON `d`.`department_id` = `g`.`id`
				LEFT JOIN `company__branch` `h` ON `g`.`branch_id` = `h`.`id`
				LEFT JOIN `company__position` `i` ON `e`.`parent_id` = `i`.`id`
				left join hr__leave_allocation curr_alloc on curr_alloc.user_id = a.id and curr_alloc.type_id=? and curr_alloc.is_delete=0 and curr_alloc.year=(?)
				left join hr__leave_allocation al_prev1 on al_prev1.user_id = a.id and al_prev1.type_id=? and al_prev1.is_delete=0 and al_prev1.year=(? -1)
				left join hr__leave_allocation al_prev2 on a.id=al_prev2.user_id and al_prev2.type_id=? and al_prev2.is_delete=0 and al_prev2.year=(? - 2)
				left join hr__leave_allocation co_prev on a.id=co_prev.user_id and co_prev.type_id=8 and co_prev.is_delete=0 and co_prev.year=(? - 1)
				left join hr__leave_entitlement el on el.type_id=? and el.year=? and el.category_id=e.parent_id
				left join user__profiling up on up.user_id = a.id and up.is_delete=0 and up.id = (select max(id) from user__profiling up2 where up2.user_id = up.user_id)
				left join `_ref__employee_group` reg on reg.id=d.contract_element_id
				left join `_ref__employee_contract_type` rect on rect.id=d.employee_group_id
				WHERE `a`.`status` =0
				AND a.is_delete =0';
				// AND `a`.`is_delete` =0 and (year(`d`.date_joined) <= ? or `d`.date_joined is null)';
				//                AND (`a`.`is_delete` =0 OR (`a`.`is_delete` =1 AND `d`.`date_termination` > ?))'
				
				//        $qParams = array($year, $year, $year, $type, $year, $type, $year, $type, $year, $year, $type, $year, date('Y-m-d'));
				// $qParams = array($year, $year, $year, $type, $year, $type, $year, $type, $year, $year, $type, $year, $year);
		$qParams = array($year, $year, $year, $type, $year, $type, $year, $type, $year, $year, $type, $year);
		if($department){
			$q .= ' AND d.department_id = ?';
			array_push($qParams, $department);
		}
		if($position){
			$q .= ' AND e.parent_id = ?';
			array_push($qParams, $position);
		}
		if($status == 1){
			$q .= ' AND curr_alloc.leave_entitled is not null';
		}
		if($status == 2){
			$q .= ' AND curr_alloc.leave_entitled is null';
		}        
		$q .= ' ORDER BY `a`.`name` ASC';
		
		$result = $this->db->query($q, $qParams)->result_array();
		// log_message('error', 'results count: ' .  count($result));
		// log_message('error', $this->db->last_query());
		return $result;
		
	}
    
    // OLD. status: 1all/2allocated/3unallocated
    function getStaffAllocationSummaryOLD($year, $type, $department, $position, $status){
        // log_message('error', 'getStaffAllocation()');
        
        $q = 'SELECT a.id, a.staff_id, d.gender_id, a.name, 
                (SELECT GROUP_CONCAT(j.id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as allocation_id,
                (SELECT GROUP_CONCAT(distinct j.type_id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as type_id,
                d.date_joined, `d`.`date_commencement`, `d`.`date_termination`, `reg`.`type` as `employee_group_name`,
                `rect`.`type` as `employee_contract_type_name`,
                `d`.`position_id`, `e`.`position` as `position_name`, `d`.`company_id`, `f`.`name` as `company_name`, 
                `g`.`branch_id`, `h`.`name` as `branch_name`, `d`.`department_id`, `g`.`name` as `department_name`, e.parent_id as position_parent_id, `i`.`parent_id` as `position_category_id`, 
                `i`.`position` as `position_category`,
                curr_alloc.id as curr_allocation_id,
                curr_alloc.leave_entitled as curr_leave,
                curr_alloc.leave_taken as curr_taken,
                al_prev1.leave_entitled as prev1_leave,
                al_prev1.leave_balance as prev1_leave_balance,
                al_prev2.leave_entitled as prev2_leave,
		co_prev.leave_balance as prev_co_balance,
		el.id as entitlement_id,
		el.month_of_service_add_leave as leave_month_cycle,
		el.additional_leave as leave_additional_leave_per_cycle,
		el.max_entitlement as leave_max_entitlement,
		el.bring_forward_percentage as leave_bring_forward_percentage,
		el.entitled_leave as entitled_leave,
                up.marital_status, up.married_date,
		(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation=3) as spouse_count,
		(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation=2) as children_count,
		(select count(id) from user__family_member ufm where ufm.user_id=a.id and ufm.is_delete=0 and ufm.relation not in (2,3)) as other_family_count,
		(select count(id) from hr__exam where year(exam_date)=? and is_delete=0 and staff_id=a.id) as exam_count
                FROM `user` `a`
                LEFT JOIN `user__employment_detail` `d` ON `d`.`user_id` = `a`.`id`
                LEFT JOIN `company__position` `e` ON `d`.`position_id` = `e`.`id`
                LEFT JOIN `company` `f` ON `d`.`company_id` = `f`.`id`
                LEFT JOIN `company__department` `g` ON `d`.`department_id` = `g`.`id`
                LEFT JOIN `company__branch` `h` ON `g`.`branch_id` = `h`.`id`
                LEFT JOIN `company__position` `i` ON `e`.`parent_id` = `i`.`id`
                left join hr__leave_allocation curr_alloc on curr_alloc.user_id = a.id and curr_alloc.type_id=? and curr_alloc.is_delete=0 and curr_alloc.year=(?)
                left join hr__leave_allocation al_prev1 on al_prev1.user_id = a.id and al_prev1.type_id=? and al_prev1.is_delete=0 and al_prev1.year=(? -1)
		left join hr__leave_allocation al_prev2 on a.id=al_prev2.user_id and al_prev2.type_id=? and al_prev2.is_delete=0 and al_prev2.year=(? - 2)
		left join hr__leave_allocation co_prev on a.id=co_prev.user_id and co_prev.type_id=8 and co_prev.is_delete=0 and co_prev.year=(? - 1)
		left join hr__leave_entitlement el on el.type_id=? and el.year=? and el.category_id=e.parent_id
                left join user__profiling up on up.user_id = a.id and up.is_delete=0 and up.id = (select max(id) from user__profiling up2 where up2.user_id = up.user_id)
                left join `_ref__employee_group` reg on reg.id=d.contract_element_id
		left join `_ref__employee_contract_type` rect on rect.id=d.employee_group_id
                WHERE `a`.`status` =0
                AND `a`.`is_delete` =0 and (year(`d`.date_joined) <= ? or `d`.date_joined is null)';
		//                AND (`a`.`is_delete` =0 OR (`a`.`is_delete` =1 AND `d`.`date_termination` > ?))'
                
		//        $qParams = array($year, $year, $year, $type, $year, $type, $year, $type, $year, $year, $type, $year, date('Y-m-d'));
        $qParams = array($year, $year, $year, $type, $year, $type, $year, $type, $year, $year, $type, $year, $year);
        if($department){
            $q .= ' AND d.department_id = ?';
            array_push($qParams, $department);
        }
        if($position){
            $q .= ' AND e.parent_id = ?';
            array_push($qParams, $position);
        }
        if($status == 1){
            $q .= ' AND curr_alloc.leave_entitled is not null';
        }
        if($status == 2){
            $q .= ' AND curr_alloc.leave_entitled is null';
        }        
        $q .= ' ORDER BY `a`.`name` ASC';
        
        $result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', 'results count: ' .  count($result));
        // log_message('error', $this->db->last_query());
        return $result;
        
    }
    
    // status: 1all/2allocated/3unallocated. bfl only lol
    function getStaffAllocationSummary2($year, $type, $refType, $department, $position, $status){
        // log_message('error', 'getStaffAllocation2()');
        
        $q = 'SELECT a.id, a.staff_id, d.gender_id, a.name, 
                (SELECT GROUP_CONCAT(j.id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as allocation_id,
                (SELECT GROUP_CONCAT(distinct j.type_id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as type_id,
                -- d.date_joined, 
		-- `d`.`date_commencement`, 
		-- `d`.`date_termination`, 
                `d`.`position_id`, `e`.`position` as `position_name`, `d`.`company_id`, `f`.`name` as `company_name`, 
                `g`.`branch_id`, `h`.`name` as `branch_name`, `d`.`department_id`, `g`.`name` as `department_name`, e.parent_id as position_parent_id, `i`.`parent_id` as `position_category_id`, 
                `i`.`position` as `position_category`,
                curr_alloc.id as curr_allocation_id,
                curr_alloc.date_from as curr_allocation_date_from,
                curr_alloc.date_to as curr_allocation_date_to,
                curr_alloc.leave_entitled as curr_leave,
                curr_alloc.leave_taken as curr_taken,
                al_prev1.leave_entitled as prev1_leave,
                al_prev1.leave_balance as prev1_leave_balance,
		al_prev1.bring_forward as prev1_bring_forward,
                al_prev1.leave_entitled as prev1_leave_entitled,
                al_prev2.leave_entitled as prev2_leave,
		co_prev.leave_balance as prev_co_balance,
		el.id as entitlement_id,
                el.entitled_leave as entitled_leave,
                el.max_entitlement as leave_max_entitlement,
		elRef.month_of_service_add_leave as ref_leave_month_cycle,
		elRef.additional_leave as ref_leave_additional_leave_per_cycle,
		elRef.max_entitlement as ref_leave_max_entitlement,
		elRef.bring_forward_percentage as ref_leave_bring_forward_percentage,
		elRef.entitled_leave as ref_entitled_leave,
		(
		select max(up2.action_efective_date) from user__profiling up2 where up2.is_delete = 0 and up2.user_id = a.id and up2.action_type_id in (12,13,14)
		) as last_resigned,
		(
		SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
		where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (5) order by ujg.date_join asc
		) as possin_dates,
		(
		SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
		where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (0,1) order by ujg.date_join asc
		) as in_dates
                FROM `user` `a`
                LEFT JOIN `user__employment_detail` `d` ON `d`.`user_id` = `a`.`id`
                LEFT JOIN `company__position` `e` ON `d`.`position_id` = `e`.`id`
                LEFT JOIN `company` `f` ON `d`.`company_id` = `f`.`id`
                LEFT JOIN `company__department` `g` ON `d`.`department_id` = `g`.`id`
                LEFT JOIN `company__branch` `h` ON `g`.`branch_id` = `h`.`id`
                LEFT JOIN `company__position` `i` ON `e`.`parent_id` = `i`.`id`
                left join hr__leave_allocation curr_alloc on curr_alloc.user_id = a.id and curr_alloc.type_id=? and curr_alloc.is_delete=0 and curr_alloc.year=(?)
                left join hr__leave_allocation al_prev1 on al_prev1.user_id = a.id and al_prev1.type_id=? and al_prev1.is_delete=0 and al_prev1.year=(? -1)
		left join hr__leave_allocation al_prev2 on a.id=al_prev2.user_id and al_prev2.type_id=? and al_prev2.is_delete=0 and al_prev2.year=(? - 2)
		left join hr__leave_allocation co_prev on a.id=co_prev.user_id and co_prev.type_id=8 and co_prev.is_delete=0 and co_prev.year=(? - 1)
		left join hr__leave_entitlement elRef on elRef.type_id=? and elRef.year=? and elRef.category_id=e.parent_id
                left join hr__leave_entitlement el on el.type_id=? and el.year=? and el.category_id=e.parent_id
                WHERE `a`.`status` =0
		AND a.is_delete =0';
                // AND `a`.`is_delete` =0 and (year(`d`.date_joined) <= ? or `d`.date_joined is null)';
				//                AND (`a`.`is_delete` =0 OR (`a`.`is_delete` =1 AND `d`.`date_termination` > ?))'; 
				//        $qParams = array($year, $year, $type, $year, $refType, $year, $refType, $year, $year, $refType, $year, $type, $year, date('Y-m-d'));
				// $qParams = array($year, $year, $type, $year, $refType, $year, $refType, $year, $year, $refType, $year, $type, $year, $year);
		$qParams = array($year, $year, $type, $year, $refType, $year, $refType, $year, $year, $refType, $year, $type, $year);
        if($department){
            $q .= ' AND d.department_id = ?';
            array_push($qParams, $department);
        }
        if($position){
            $q .= ' AND e.parent_id = ?';
            array_push($qParams, $position);
        }
        if($status == 1){
            $q .= ' AND curr_alloc.leave_entitled is not null';
        }
        if($status == 2){
            $q .= ' AND curr_alloc.leave_entitled is null';
        }        
        $q .= ' ORDER BY `a`.`name` ASC';
        
        $result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', $this->db->last_query());
        // log_message('error', 'results count: ' .  count($result));
        return $result;
        
    }
    
    // status: 1all/2allocated/3unallocated. study leave only lol
    function getStaffAllocationSummary3($year, $type, $department, $position, $status){
        // log_message('error', 'getStaffAllocation3()');
        
        $q = 'SELECT a.id, a.staff_id, d.gender_id, a.name, 
                (SELECT GROUP_CONCAT(j.id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as allocation_id,
                (SELECT GROUP_CONCAT(distinct j.type_id) FROM hr__leave_allocation j where j.user_id = a.id and year=? and is_delete=0 and type_id not in(2)) as type_id,
                -- d.date_joined, 
				-- `d`.`date_commencement`, 
				-- `d`.`date_termination`, 
                `reg`.`type` as `employee_group_name`,
                `rect`.`type` as `employee_contract_type_name`,
                `d`.`position_id`, `e`.`position` as `position_name`, `d`.`company_id`, `f`.`name` as `company_name`, 
                `g`.`branch_id`, `h`.`name` as `branch_name`, `d`.`department_id`, `g`.`name` as `department_name`, e.parent_id as position_parent_id, `i`.`parent_id` as `position_category_id`, 
                `i`.`position` as `position_category`,
				null as curr_allocation_id,
                sum(curr_alloc.leave_entitled) as curr_leave,
                curr_alloc.leave_taken as curr_taken,
                sum(al_prev1.leave_entitled) as prev1_leave,
				el.id as entitlement_id,
				el.entitled_leave as entitled_leave,
                el.max_entitlement as leave_max_entitlement,
				(select count(id) from hr__exam where year(exam_date)=2022 and is_delete=0 and staff_id=a.id) as exam_count,
				(
				select max(up2.action_efective_date) from user__profiling up2 where up2.is_delete = 0 and up2.user_id = a.id and up2.action_type_id in (12,13,14)
				) as last_resigned,
				(
				SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
				where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (5) order by ujg.date_join asc
				) as possin_dates,
				(
				SELECT GROUP_CONCAT(ujg.date_join) from user__joining_group ujg inner join user__profiling up2 on ujg.profiling_id = up2.id  
				where up2.user_id = a.id and up2.is_delete=0 and ujg.is_delete = 0 and up2.action_type_id in (0,1) order by ujg.date_join asc
				) as in_dates
                FROM `user` `a`
                LEFT JOIN `user__employment_detail` `d` ON `d`.`user_id` = `a`.`id`
                LEFT JOIN `company__position` `e` ON `d`.`position_id` = `e`.`id`
                LEFT JOIN `company` `f` ON `d`.`company_id` = `f`.`id`
                LEFT JOIN `company__department` `g` ON `d`.`department_id` = `g`.`id`
                LEFT JOIN `company__branch` `h` ON `g`.`branch_id` = `h`.`id`
                LEFT JOIN `company__position` `i` ON `e`.`parent_id` = `i`.`id`
                left join hr__leave_allocation curr_alloc on curr_alloc.user_id = a.id and curr_alloc.type_id=? and curr_alloc.is_delete=0 and curr_alloc.year=(?)
                left join hr__leave_allocation al_prev1 on al_prev1.user_id = a.id and al_prev1.type_id=? and al_prev1.is_delete=0 and al_prev1.year=(? -1)
				left join hr__leave_entitlement el on el.type_id=? and el.year=? and el.category_id=e.parent_id
                left join user__profiling up on up.user_id = a.id and up.is_delete=0 and up.id = (select max(id) from user__profiling up2 where up2.user_id = up.user_id)
                left join `_ref__employee_group` reg on reg.id=d.contract_element_id
                left join `_ref__employee_contract_type` rect on rect.id=d.employee_group_id
                WHERE `a`.`status` =0
				AND a.is_delete =0';
                // AND `a`.`is_delete` =0 and (year(`d`.date_joined) <= ? or `d`.date_joined is null)';
				//                AND (`a`.`is_delete` =0 OR (`a`.`is_delete` =1 AND `d`.`date_termination` > ?))'; 
				//        $qParams = array($year, $year, $type, $year, $type, $year, $type, $year, date('Y-m-d'));
				// $qParams = array($year, $year, $type, $year, $type, $year, $type, $year, $year);
		$qParams = array($year, $year, $type, $year, $type, $year, $type, $year);
        if($department){
            $q .= ' AND d.department_id = ?';
            array_push($qParams, $department);
        }
        if($position){
            $q .= ' AND e.parent_id = ?';
            array_push($qParams, $position);
        }
        if($status == 1){
            $q .= ' AND curr_alloc.leave_entitled is not null';
        }
        if($status == 2){
            $q .= ' AND curr_alloc.leave_entitled is null';
        }        
        $q .= ' group by staff_id ORDER BY `a`.`name` ASC';
        
        $result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', $this->db->last_query());
        // log_message('error', 'results count: ' .  count($result));
        return $result;
        
    }
    
    function getUserAllocations($userId, $typeId = null, $yearLimit = null){
        $q = 'SELECT * FROM hr__leave_allocation WHERE is_delete = 0 AND user_id = ?';
		$qParams = array($userId);
		if($typeId){
			$q .= ' AND type_id = ?';
            array_push($qParams, $typeId);
		}
		if($yearLimit){
			$q .= ' AND year < ?';
            array_push($qParams, $yearLimit);
		}
		$q .= ' ORDER BY year DESC';
		$result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', 'results count: ' .  count($result));
        return $result;
    }
    
    function getUnallocatedUsers($year){
        $q = 'SELECT 
                (SELECT GROUP_CONCAT(hla.id) FROM hr__leave_allocation hla where hla.user_id = u.id and year=? and is_delete=0) as allocation_id
                FROM `user` `u`
                LEFT JOIN `user__employment_detail` `ued` ON `ued`.`user_id` = `u`.`id`
                WHERE `u`.`status` =0
                AND `u`.is_delete =0
                AND (year(`ued`.date_joined) <= ? or `ued`.date_joined is null)
                HAVING allocation_id IS NULL';
        $qParams = array($year, $year);
        $result = $this->db->query($q, $qParams)->result_array();
        return count($result);
    }
    
    function getLeaveType2($isActive, $isDeleted, $exludeString){
        // log_message('error', 'getLeaveType2()');
        
        $es = $exludeString ? $exludeString : '';
        $a = $isActive ? 1 : 0;
        $d = $isDeleted ? 1 : 0;
        $q = 'select * from hr__leave_type where active = ? and is_delete = ? and id not in (?) order by name asc';
        $qParams = array($a, $d, $es);
        $result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', 'results count: ' .  count($result));
        return $result;
    }

    // obj or null
    function getUpcomingLeave($userId) {
        $q = 'select hla.id, hlt.name, hla.user_id, hla.type_id, hla.date_from, hla.date_to, days_applied, 
			period_from_id, period_to_id, 
			if(current_date() between date_from and date_to, ?, concat(datediff(date_from, current_date()), "", ?)) as status
			from hr__leave_application hla 
			inner join hr__leave_type hlt on hla.type_id = hlt.id
			where hla.is_delete=0
			and user_id=?
			and hla.status=2
			and (date_from >= current_date() or (current_date() between date_from and date_to)) 
			order by date_from asc
			limit 1';
        $qParams = array('now', 'd to go', $userId);
        $result = $this->db->query($q, $qParams)->result_array();
        // log_message('error', 'results count: ' . count($result));
        return (count($result) > 0 ? $result[0] : null);
    }
    
    // sn. year:int, user:array(id, date_joined)
    function getAllocationSummary($year, $user) {
        // // log_message('error', 'getAllocationSummary');
        $q = 'select b.id as type_id, b.name, b.code, b.colour, b.app_require_attachment, b.require_allocation_value, b.require_exam, b.include_holiday, b.is_fixed, b.is_prorate, b.is_pre_application, b.cooling_period, b.is_bring_forward, 
			b.bring_forward_percent, b.forward_leave_type_id, b.function_to_call, b.increment_period, b.increment_value,
			a.id as allocation_id, a.user_id, a.entitlement_id, a.year, a.leave_entitled, a.leave_taken, a.leave_balance, 
			a.bring_forward_entitled, a.bring_forward, a.balance_entitled, a.date_from, a.date_to, a.expiry_date, a.paper_id, c.paper_code,
                        if(a.date_from is null or a.date_from = "0000-00-00", 0, if(a.date_to is null or a.date_to = "0000-00-00", 0, 
                        if(a.expiry_date is null or a.expiry_date = "0000-00-00", 0, 1))) as alloc_complete,
                        ifnull((select sum(hla.days_applied) from hr__leave_application hla where hla.is_delete =0 and hla.status =1 
                        and year(hla.date_from)=? and hla.user_id=? and hla.type_id=a.type_id), 0) as pending
                        from hr__leave_allocation a
                        inner join hr__leave_type b on b.id=a.type_id 
                        left join hr__exam c on c.id = a.paper_id 
                        where a.year = ? and a.is_delete=0 and a.user_id =? order by b.name asc';
        $qParams = array($year, $user['id'], $year, $user['id']);
        $result = $this->db->query($q, $qParams)->result_array();
//        $this->db->select('b.id as type_id, b.name, b.code, b.colour, b.app_require_attachment, b.require_allocation_value, b.require_exam, b.include_holiday, b.is_fixed, b.is_prorate, b.is_pre_application, b.cooling_period, b.is_bring_forward, 
//			b.bring_forward_percent, b.forward_leave_type_id, b.function_to_call, b.increment_period, b.increment_value,
//			a.id as allocation_id, a.user_id, a.entitlement_id, a.year, a.leave_entitled, a.leave_taken, a.leave_balance, 
//			a.bring_forward_entitled, a.bring_forward, a.balance_entitled, a.date_from, a.date_to, a.expiry_date, a.paper_id, c.paper_code,
//                        if(a.date_from is null or a.date_from = "0000-00-00", 0, if(a.date_to is null or a.date_to = "0000-00-00", 0, 
//                        if(a.expiry_date is null or a.expiry_date = "0000-00-00", 0, 1))) as alloc_complete');
//
//        $this->db->join('hr__leave_type b', 'b.id = a.type_id');
//        $this->db->join('hr__exam c', 'c.id = a.paper_id', 'left');
//        $this->db->where('a.user_id', $user['id']);
//        $this->db->where('a.year', $year);
//        $this->db->where('a.is_delete', 0);
//        $this->db->where('b.is_delete', 0);
//        $this->db->order_by('b.name', 'asc');
//        $result = $this->db->get('hr__leave_allocation a')->result_array();
        // log_message('error', $this->db->last_query());

        $a = 0;
        foreach ($result as $r) {
            // default for not prorate
            $allowedEntitled = floatval($r['leave_entitled']);
            $allowedBalance = ($r['require_allocation_value'] == 1 ? floatval($r['leave_balance']) : floatval(0));
            $extraName = null;

            if ($r['is_prorate'] == 1) {
                if ((date_diff(date_create(date('Y-m-d')), date_create($user['date_joined']))->format('%a') < 365) && (date('Y', strtotime($user['date_joined'])) == date('Y'))) {
                    $allowedEntitled = floatval($r['leave_entitled']) /
                            (floatval(date('z', strtotime($year . '-12-31')) + 1) - floatval(date('z', strtotime($user['date_joined'])) + 1)) *
                            floatval(date('z') + 1);
                    $allowedEntitled = $this->floorToFraction($allowedEntitled, true, 2, true);
                    $allowedBalance = $allowedEntitled - floatval($r['leave_taken']);
                } else {
                    $allowedEntitled = floatval($r['leave_entitled']) /
                            (floatval(date('z', strtotime($year . '-12-31')) + 1)) * floatval(date('z') + 1);
                    $allowedEntitled = $this->floorToFraction($allowedEntitled, true, 2, true);
                    $allowedBalance = $allowedEntitled - floatval($r['leave_taken']);
                }
            } 
            if($r['require_exam'] == 1){
                $extraName = $r['paper_code'];
            }
            $result[$a]['extra_name'] = $extraName;
            $result[$a]['allowed_entitled'] = $allowedEntitled;
            $result[$a]['allowed_balance'] = $allowedBalance;
            
            $a ++;
        }

        return $result;
    }
    
    function floorToFraction($number, $standardRound, $denominator = 1, $useCeil = true){
        if($standardRound){
            $x = round($number);
        }else{
            $x = $number * $denominator;
            $x = ($useCeil ? (ceil($x)) : (floor($x)));
            $x = $x / $denominator;
        }
        return $x;
    }

}