<?php

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

class Model_leave_v2 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;
    }

    // exclude EL
    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)
                ->where_not_in('j.type_id', array(2))
                ->order_by('a.name', 'asc')
                ->order_by('j.type_id', 'asc');

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

        log_message('error', $this->db->last_query());

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

    function getAllocation($filters = array()) {

        if (!empty($filters)) {
            foreach ($filters as $filter => $value) {
                $this->db->where($filter, $value);
            }
        }
        $this->db->select('a.*, b.code, b.name, b.description, b.colour')
                ->select(' b.include_holiday, b.is_fixed, b.is_prorate, b.is_pre_application, b.cooling_period, b.is_bring_forward, b.forward_leave_type_id, b.function_to_call')
                ->select('if(b.is_bring_forward=1,if( a.leave_balance > b.bring_forward_percent * a.leave_entitled / 100,b.bring_forward_percent * a.leave_entitled / 100,a.leave_balance),0) as carry_forward_allow', false)
                ->select('ifnull((select sum(ifnull(days_applied,0)) from hr__leave_application where user_id = a.user_id and year(date_from) =a.year and type_id = a.type_id and is_delete=0 and status=0),0) as pending ', false)
                ->select('ifnull((select sum(ifnull(days_applied,0)) from hr__leave_application where user_id = a.user_id and year(date_from) =a.year and type_id = a.type_id and is_delete=0 and status=2),0) as taken_calculate ', false)
                ->join('hr__leave_type b', 'b.id = a.type_id');

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

        $query = $this->db->get('hr__leave_allocation a');
//echo $this->db->last_query(); die();
        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_currentYear($filters = array(), $exclude_emergency_and_unpaid = false) {
        $this->db->select('a.*, sum(ifnull(b.days_applied,0)) as leave_taken, c.name,c.code, ifnull(if(c.is_prorate =1,(a.leave_entitled / 12 * month(now()) - sum(ifnull(b.days_applied,0))),a.leave_balance),0) 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');

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

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

    // exclude current year    
    function getAllocationHistory($filters = array()) {
        if (!empty($filters)) {
            foreach ($filters as $filter => $value) {
                $this->db->where($filter, $value);
            }
        }
        return $this->db->select('year,leave_entitled')
                        ->where('is_delete', 0)
                        ->where('year <', date('Y'))
                        ->order_by('year')
                        ->get('hr__leave_allocation')
                        ->result_array();
    }

    // exclude EL
    function getAllocation2($filters = array(), $exclude_emergency_and_unpaid = false) {
        $this->db->select('a.*, 
			vls.type_code as code, vls.leave_type as name,
			ifnull(vls.current_balance,0) as current_balance,
			if(a.type_id = 1,(select sum(ifnull(days_applied,0)) from hr__leave_application where user_id = a.user_id and year(date_from)=a.year and month(date_from) < 4 and type_id in (1,2)),0) as takenB4
		');

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

        $this->db->where_not_in('a.type_id', array(2));

        //		if ($exclude_emergency_and_unpaid == true) $this->db->where('a.type_id NOT IN(2, 4)');
        if ($exclude_emergency_and_unpaid == true) {
            $this->db->where('a.type_id NOT IN(2, 4)');
            $this->db->join('view_leave_summary vls', 'a.year = vls.year and a.user_id = vls.user_id and a.type_id = vls.type_id', 'left outer');
        } else {
            $this->db->join('view_leave__summary_all_type vls', 'a.year = vls.year and a.user_id = vls.user_id and a.type_id = vls.type_id', 'left outer');
        }

        $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');
        //		$query = $this->db->get('view_leave_summary 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() > 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 getUnallocatedUserByYear($filters = array()) {
        $rs = $this->db->select('a.year, (select count(b.id) from user__employment_detail b where b.is_delete =0 and year(b.date_joined) <= a.year and b.user_id not in (select user_id from hr__leave_allocation where year=a.year and type_id = 1 and is_delete =0 and year >= year(b.date_joined))) as unallocated')
                ->where('a.is_delete', 0)
                ->group_by('a.year')
                ->order_by('a.year desc')
                ->get('hr__leave_allocation a')
                ->result_array();
        log_message('error', $this->db->last_query());
        return $rs;
    }

    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()) {
        log_message('error', 'getUnallocatedUser2()');
        $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,
                        
                        ml.id as ml_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 ml', 'ml.type_id = 3 AND e.parent_id = ml.category_id AND ml.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');
        log_message('error', $this->db->last_query());
        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') {
        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();
        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");

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

        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;
    }

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

        if ($id) {
            $this->db->where('id', $id);
        } else {
            $this->db->where_not_in('id', array(2));
        }


        $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()) {
        $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() {
      $sql = "SELECT `a`.`name`, `f`.`position`, `d`.`name` as dept_name, `e`.`name` as branch_name, `b`.`date_joined`,
      (
      SELECT `la_prev`.`leave_entitled`
      FROM `spserp_dev`.`hr__leave_allocation` `la_prev`
      JOIN `spserp_dev`.`user` ON `la_prev`.`user_id` = `user`.`id`
      WHERE `la_prev`.`type_id` = 1
      AND `la_prev`.`year` = 2018
      ORDER BY `la_prev`.`created_ts` DESC
      LIMIT 1
      ) AS al_entitled_prev,
      (
      SELECT `la_prev`.`leave_balance`
      FROM `spserp_dev`.`hr__leave_allocation` `la_prev`
      JOIN `spserp_dev`.`user` ON `la_prev`.`user_id` = `user`.`id`
      WHERE `la_prev`.`type_id` = 1
      AND `la_prev`.`year` = 2018
      ORDER BY `la_prev`.`created_ts` DESC
      LIMIT 1
      ) AS al_balance_prev,
      (
      SELECT `la_prev`.`leave_balance`
      FROM `spserp_dev`.`hr__leave_entitlement` `la_prev`
      JOIN `spserp_dev`.`user` ON `la_prev`.`user_id` = `user`.`id`
      WHERE `la_prev`.`type_id` = 1
      AND `la_prev`.`year` = 2019
      ORDER BY `la_prev`.`created_ts` DESC
      LIMIT 1
      ) AS al_entitled_curr,
      (
      SELECT `la_prev`.`leave_balance`
      FROM `spserp_dev`.`hr__leave_entitlement` `la_prev`
      JOIN `spserp_dev`.`user` ON `la_prev`.`user_id` = `user`.`id`
      WHERE `la_prev`.`type_id` = 1
      AND `la_prev`.`year` = 2018
      ORDER BY `la_prev`.`created_ts` DESC
      LIMIT 1
      ) AS mc_entitled_prev,
      (SELECT) AS mc_entitled_curr,

      FROM `spserp_dev`.`user` `a`
      LEFT JOIN `spserp_dev`.`user__employment_detail` `b` ON `b`.`user_id` = `a`.`id`
      LEFT JOIN `spserp_dev`.`company__position` `c` ON `c`.`id` = `b`.`position_id`
      LEFT JOIN `spserp_dev`.`company__department` `d` ON `d`.`id` = `b`.`department_id`
      LEFT JOIN `spserp_dev`.`company__branch` `e` ON `e`.`id` = `d`.`branch_id`
      LEFT JOIN `spserp_dev`.`company__position` `f` ON `f`.`id` = `c`.`parent_id`

      WHERE `a`.`is_delete` = 0
      ";


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

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

    function getEntitlementReviewReport($year = 0) {
        $previous_year = $year - 1;

        /*         * * akmal 20191112 change to read leave balance from view of calculation, not from table hr__leave_allocation due to number of errors in the leave_balance
          $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.leave_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 hr__leave_allocation l1 on a.id = l1.user_id and l1.year = '.$previous_year.' and l1.type_id = 1 and l1.is_delete = 0)
          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
          '); *** */
        $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;
            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;
    }

    public function getMyLeaveAllocation($user_id = 0, $year = 0) {
        $year = ($year == 0) ? date("Y") : $year;
        $user_id = ($user_id == 0) ?: $user_id;
    }

    public function getSelfAllocationSetup($data = array()) {
        if (empty($data['user_id']) || empty($data['year']))
            return NULL;
        else
            return $this->db->select('a.*, b.leave_entitled, ,b.leave_taken, b.leave_balance')
//				->where('',$data['user_id'])
//				->where('',$data['year'])
                            ->where('a.is_delete', 0)
                            ->where('b.leave_entitled > 0')
//				->where_or('')
                            ->join('hr__leave_allocation b', 'b.type_id = a.id and b.user_id = ' . $data['user_id'] . ' and b.year =' . $data['year'] . ' and b.is_delete =0', 'left outer ')
                            ->get('hr__leave_type a')
                            ->result_array();
    }

    function getAllocation4($filters = array(), $exclude_emergency_and_unpaid = false) {
        log_message('error', 'getAllocation2');
        $this->db->select('b.id as type_id, b.name, 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.expiry_date');

        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->order_by('b.name', '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', 'rows: ' . $query->num_rows());
        log_message('error', 'exec time: ' . number_format($end - $started, 10));

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

    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
			');
        }

        $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');

        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();
    }

    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;
    }

    // sn
    function getUsersLeaveAllocation2($year, $status = null) {
        $qStr = 'SELECT a.id, a.staff_id, a.name,`d`.`id` as `employment_id`, 
                (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(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,
                (SELECT GROUP_CONCAT(if(j.date_from is null or j.date_from = "0000-00-00", 0, if(j.date_to is null or j.date_to = "0000-00-00", 0, 
                if(j.expiry_date is null or j.expiry_date = "0000-00-00", 0, 1)))) 
                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_completion,
                `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`
                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`
                WHERE `a`.`status` =0
                AND `a`.`is_delete` =0';
        
        if($status){
            if($status == 'allocated') $qStr .= ' HAVING allocation_id is not null';
            if($status == 'unallocated') $qStr .= ' HAVING allocation_id is null';
        }
        
        $qStr .= ' ORDER BY `a`.`name` ASC';
        $qParam = array($year, $year, $year);
        $result = $this->db->query($qStr, $qParam)->result_array();
        
        log_message('error', $this->db->last_query());

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

}
