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

class Serviceform extends MY_Controller
{
	
	function __construct() 
	{
		$this->load->module('template');
		$this->load->model('admin/model_user');
		$this->load->model('admin/model_company','company');
		$this->load->model('hr/model_form');
		$this->load->model('CRM/country');
		$this->load->model('CRM/general_model');
		$this->load->helper('jwt_helper');

		$this->logged_user = $this->session->userdata('user');
		$this->logged_module = $this->session->userdata('module_data');
	}

	function get_stationery_filter()
	{
		$department	= (!empty($this->input->post('sv_stationery_filter_dept')) && $this->input->post('sv_stationery_filter_dept') != -1)? $this->input->post('sv_stationery_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_stationery_filter_status')) && $this->input->post('sv_stationery_filter_status') != -1)? $this->input->post('sv_stationery_filter_status'):null;
		$year		= (!empty($this->input->post('sv_stationery_filter_year')))? $this->input->post('sv_stationery_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_stationery_filter_month')))? $this->input->post('sv_stationery_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_stationery_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_stationery_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_stationery_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_stationery_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_stationery_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_stationery_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin'] 	= '(a.user_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_stationery_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_stationery_filter_status','ALL');
		}

		/* filter data */

		$select = array('a.*,b.name as departmentName, c.name as branchName,h.name as staffName,h.staff_id, group_concat(d.quantity) as quantity, group_concat(e.description) as description, group_concat(f.package) as package, group_concat(g.category_name) as category_name,(select status from sv__approval where table_name="sv__stationery" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__stationery" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b','b.id = a.department_id',''),
			array('company__branch c','c.id = b.branch_id',''),
			array('sv__stationery__detail d','d.sv_stationery_id = a.ID AND d.is_delete = 0','left'),
			array('sv__stationery__category_detail e','e.id = d.sv_stationery_category_detail_id','left'),
			array('sv__stationery__category_package f','f.id = d.category_package_id','left'),
			array('sv__stationery__category g','g.id = f.package_group_id','left'),
			array('user h','h.id = a.user_id',''),
		);
		$orderBy['a.ID'] = 'desc';							
		$groupBy = array('a.ID');
		
		$table = $this->model_form->sv__filter('sv__stationery a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);

		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_stationery_filter_dept'		=> $this->input->post('sv_stationery_filter_dept'),
			'sv_stationery_filter_year'		=> $this->input->post('sv_stationery_filter_year'),
			'sv_stationery_filter_month'	=> $this->input->post('sv_stationery_filter_month'),

		);


		$this->session->set_userdata('sv__stationery',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_maintenance_filter()
	{
		$department		= (!empty($this->input->post('sv_maintenance_filter_dept')) && $this->input->post('sv_maintenance_filter_dept') != -1)? $this->input->post('sv_maintenance_filter_dept'):null;

		$status 		= (!empty($this->input->post('sv_maintenance_filter_status')) && $this->input->post('sv_maintenance_filter_status') != -1) ? $this->input->post('sv_maintenance_filter_status'):null;

		$office_unit	= (!empty($this->input->post('sv_maintenance_filter_office_unit')) && $this->input->post('sv_maintenance_filter_office_unit') != -1)? $this->input->post('sv_maintenance_filter_office_unit'):null;
		
		$floor			= (!empty($this->input->post('sv_maintenance_filter_floor')) && $this->input->post('sv_maintenance_filter_floor') != -1)? $this->input->post('sv_maintenance_filter_floor'):null;
		
		$category		= (!empty($this->input->post('sv_maintenance_filter_complaint_category')))? $this->input->post('sv_maintenance_filter_complaint_category'):null;
		$year			= (!empty($this->input->post('sv_maintenance_filter_year')))? $this->input->post('sv_maintenance_filter_year'):date('Y');
		$month			= (!empty($this->input->post('sv_maintenance_filter_month')))? $this->input->post('sv_maintenance_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_maintenance_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_maintenance_filter_dept','ALL');
		}

		if($office_unit > 0) {
			$this->session->set_userdata('sv_maintenance_filter_office_unit',$office_unit);
			$where['a.office_unit'] = $office_unit; 
		} else {
			$this->session->set_userdata('sv_maintenance_filter_office_unit','ALL');
		}

		if(!empty($floor)) {
			$this->session->set_userdata('sv_maintenance_filter_floor',$floor);
			$where['a.floor'] = $floor; 
		} else {
			$this->session->set_userdata('sv_maintenance_filter_floor','ALL');
		}

		if(!empty($category)) {
			$this->session->set_userdata('sv_maintenance_filter_complaint_category',$category);
			$where['a.complain_category'] = $category; 
		} else {
			$this->session->set_userdata('sv_maintenance_filter_complaint_category','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_maintenance_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_maintenance_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_maintenance_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_maintenance_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin'] 	= '(a.user_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_maintenance_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_maintenance_filter_status','ALL');
		}

		/* filter data */

		$select = array('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.ID as photoID, d.sv_maintenance_id, d.photo_title, d.file_location, e.name as userName, e.staff_id, e.email, f.name as approvedByName,(select status from sv__approval where table_name="sv__maintenance" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__maintenance" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id',''),
			array('company__branch c', 'c.id = b.branch_id',''),
			array('sv__maintenance__photo d', 'd.sv_maintenance_id = a.ID', 'LEFT'),
			array('user e', 'e.id = a.user_id',''),
			array('user f', 'f.id = a.approvedBy', 'LEFT')
		);
		$orderBy['a.ID'] = 'asc';							
		
		$table = $this->model_form->sv__filter('sv__maintenance a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_maintenance_filter_dept'	=> $this->input->post('sv_maintenance_filter_dept'),
			'sv_maintenance_filter_year'	=> $this->input->post('sv_maintenance_filter_year'),
			'sv_maintenance_filter_month'	=> $this->input->post('sv_maintenance_filter_month'),

		);

		$this->session->set_userdata('sv__maintenance',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_meeting_filter()
	{
		$department	= (!empty($this->input->post('sv_room_booking_filter_dept')) && $this->input->post('sv_room_booking_filter_dept') != -1)? $this->input->post('sv_room_booking_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_room_booking_filter_status')) && $this->input->post('sv_room_booking_filter_status') != -1)? $this->input->post('sv_room_booking_filter_status'):null;
		$room		= (!empty($this->input->post('sv_room_booking_filter_room')))? $this->input->post('sv_room_booking_filter_room'):null;
		$year		= (!empty($this->input->post('sv_room_booking_filter_year')))? $this->input->post('sv_room_booking_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_room_booking_filter_month')))? $this->input->post('sv_room_booking_filter_month'):null;

		$where = array(); 

		/* filter data */

		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_room_booking_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_room_booking_filter_dept','ALL');
		}

		if($room > 0) {
			$this->session->set_userdata('sv_room_booking_filter_room',$room);
			$where['a.room_id'] = $room; 
		} else {
			$this->session->set_userdata('sv_room_booking_filter_room','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_room_booking_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_room_booking_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_room_booking_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_room_booking_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin'] 	= '(a.requester_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_room_booking_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_room_booking_filter_status','ALL');
		}

		/* filter data */

		$select = array('a.*, group_concat(f.equipment_id) as equipment_id, group_concat(h.equipment) as equipment, d.name, d.staff_id, d.email, e.room, e.color_code, a.department_id, b.name as dept_name, c.name as branchName, g.package, g.price as package_price ,(select status from sv__approval where table_name="sv__booking__room" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__booking__room" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id',''),
			array('company__branch c', 'c.id = b.branch_id',''),
			array('user d', 'd.id = a.requester_id',''),
			array('sv__booking_room_type e', 'e.id = a.room_id', 'left'),
			array('sv__booking__room_equipment f', 'f.booking_room_id = a.ID and f.is_delete = 0', 'left'),
			array('sv__ref_room_package g', 'g.ID = a.package_id and g.is_delete = 0', 'left'),
			array('sv__ref_room_equipment h', 'h.ID = f.equipment_id and h.is_delete = 0', 'left'),
		);
		$orderBy['a.ID'] = 'asc';
		$groupBy = array('a.ID');

		$table = $this->model_form->sv__filter('sv__booking__room a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);

		$data = array
		(
			'status' 							=> 'success',
			'message'							=> '',
			'table'								=> ($table)?$table:'No Data',
			'user_id' 							=> $this->input->post('user_id'),
			'sv_room_booking_filter_dept'		=> $this->input->post('sv_room_booking_filter_dept'),
			'sv_room_booking_filter_room'		=> $this->input->post('sv_room_booking_filter_room'),
			'sv_room_booking_filter_year'		=> $this->input->post('sv_room_booking_filter_year'),
			'sv_room_booking_filter_month'		=> $this->input->post('sv_room_booking_filter_month'),

		);
		$this->session->set_userdata('sv__booking__room',$data['table']);
		echo json_encode($data);
		return true;
    }
	
	function get_carbooking_filter()
	{
		$department	= (!empty($this->input->post('sv_carbooking_filter_dept')) && $this->input->post('sv_carbooking_filter_dept') != -1)? $this->input->post('sv_carbooking_filter_dept'):null;
		$carname	= (!empty($this->input->post('sv_carbooking_filter_cartype')) && $this->input->post('sv_carbooking_filter_cartype') != -1)? $this->input->post('sv_carbooking_filter_cartype'):null;
		$status	= (!empty($this->input->post('sv_carbooking_filter_status')) && $this->input->post('sv_carbooking_filter_status') != -1)? $this->input->post('sv_carbooking_filter_status'):null;
		$year		= (!empty($this->input->post('sv_carbooking_filter_year')))? $this->input->post('sv_carbooking_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_carbooking_filter_month')))? $this->input->post('sv_carbooking_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_carbooking_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_carbooking_filter_dept','ALL');
		}

		if($carname > 0) {
			$this->session->set_userdata('sv_carbooking_filter_cartype',$carname);
			$where['a.car_id'] = $carname; 
		} else {
			$this->session->set_userdata('sv_carbooking_filter_cartype','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_carbooking_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_carbooking_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_carbooking_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_carbooking_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];
		
		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin'] 	= '(a.user_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_carbooking_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_carbooking_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.car_name as car_type_name, e.name as userName, e.email as userEmail, e.staff_id, e.email, f.name as approvedByName, g.name as created_name, g.email as created_email, h.id as created_department_id, h.name as created_dept_name, i.name as created_branchName, (select status from sv__approval where table_name="sv__car_booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__car_booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id','LEFT'),
			array('company__branch c', 'c.id = b.branch_id', 'LEFT'),
			array('sv__car_booking__type d', 'd.id = a.car_id', 'LEFT'),
			array('user e', 'e.id = a.user_id',''),
			array('user f', 'f.id = a.approvedBy', 'LEFT'),
			array('user g', 'g.id = a.createdBY', 'LEFT'),
			array('company__department h', 'h.id = (select department_id from user__employment_detail where id = g.id)', 'LEFT'),
			array('company__branch i', 'i.id = h.branch_id', 'LEFT'),
		);
		$orderBy['a.id'] = 'desc';

		$table = $this->model_form->sv__filter('sv__car_booking a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_carbooking_filter_dept'		=> $this->input->post('sv_carbooking_filter_dept'),
			'sv_carbooking_filter_cartype'	=> $this->input->post('sv_carbooking_filter_cartype'),
			'sv_carbooking_filter_year'		=> $this->input->post('sv_carbooking_filter_year'),
			'sv_carbooking_filter_month'	=> $this->input->post('sv_carbooking_filter_month'),

		);
		$this->session->set_userdata('sv__car_booking',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_meeting_crm_filter()
	{
		$department		= (!empty($this->input->post('sv_meetingcrm_filter_dept')) && $this->input->post('sv_meetingcrm_filter_dept') != -1)? $this->input->post('sv_meetingcrm_filter_dept'):null;
		$companyList	= (!empty($this->input->post('sv_meetingcrm_filter_company')) && $this->input->post('sv_meetingcrm_filter_company') != -1)? $this->input->post('sv_meetingcrm_filter_company'):null;
		$year			= (!empty($this->input->post('sv_meetingcrm_filter_year')))? $this->input->post('sv_meetingcrm_filter_year'):date('Y');
		$month			= (!empty($this->input->post('sv_meetingcrm_filter_month')))? $this->input->post('sv_meetingcrm_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_meetingcrm_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_meetingcrm_filter_dept','ALL');
		}

		if($companyList > 0) {
			$this->session->set_userdata('sv_meetingcrm_filter_company',$companyList);
			$where['a.company_name'] = $companyList; 
		} else {
			$this->session->set_userdata('sv_meetingcrm_filter_company','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_meetingcrm_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_meetingcrm_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_meetingcrm_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_meetingcrm_filter_month','ALL');
		}

		if($this->logged_module['role'] != 'admin')
		{
			$where['a.prepared_by'] = $this->logged_user['employment_id'];
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, h.name as company_name, b.name as requestor, c.name as dept_name, d.name as  branchName, e.name as preparedByName, f.service_name as engagementName, g.service_name as potentialServiceName');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.user_id','LEFT'),
			array('company__department c','c.id = a.department_id','LEFT'),
			array('company__branch d','d.id = c.branch_id','LEFT'),
			array('user e', 'e.id = a.prepared_by', 'left'),
			array('sv__engagement_and_potential_services f', 'f.ID = a.engagement', 'left'),
			array('sv__engagement_and_potential_services g', 'g.ID = a.potential_services', 'left'),
			array('customer h', 'h.id = a.company_name', 'left'),
		);
		// $orderBy = array('a.year desc','a.month desc','a.department');
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__meeting__crm_booking a',$select, $where, $join);
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			//'attendListName' 				=> $this->model_booking_meeting->getattendListName(),
			'sv_meetingcrm_filter_dept'		=> $this->input->post('sv_meetingcrm_filter_dept'),
			'sv_meetingcrm_filter_year'		=> $this->input->post('sv_meetingcrm_filter_year'),
			'sv_meetingcrm_filter_month'	=> $this->input->post('sv_meetingcrm_filter_month'),
			'sv_meetingcrm_filter_company'  => $this->input->post('sv_meetingcrm_filter_company'),
		);
		$this->session->set_userdata('sv__meeting__crm_booking',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_hotel_filter()
	{
		$this->load->model('hr/model_hotel');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_hotel_filter_dept')) && $this->input->post('sv_hotel_filter_dept') != -1)? $this->input->post('sv_hotel_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_hotel_filter_status')) && $this->input->post('sv_hotel_filter_status') != -1)? $this->input->post('sv_hotel_filter_status'):null;
		$year		= (!empty($this->input->post('sv_hotel_filter_year')))? $this->input->post('sv_hotel_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_hotel_filter_month')))? $this->input->post('sv_hotel_filter_month'):null;
		$check_in	= (!empty($this->input->post('sv_hotel_filter_check_in')))? $this->input->post('sv_hotel_filter_check_in'):null;
		$check_out	= (!empty($this->input->post('sv_hotel_filter_check_out')))? $this->input->post('sv_hotel_filter_check_out'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_hotel_filter_dept',$department);
			$where['b.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_hotel_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_hotel_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_hotel_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_hotel_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_hotel_filter_month','ALL');
		}

		if(!empty($check_in) && $check_in > 0) {
			$this->session->set_userdata('sv_hotel_filter_check_in',$check_in);
			$where['a.checkin_date'] = date('Y-m-d',strtotime($check_in)); 
		} else {
			$this->session->set_userdata('sv_hotel_filter_check_in','ALL');
		}

		if(!empty($check_out) && $check_out > 0) {
			$this->session->set_userdata('sv_hotel_filter_check_out',$check_out);
			$where['a.checkout_date'] = date('Y-m-d',strtotime($check_out)); 
		} else {
			$this->session->set_userdata('sv_hotel_filter_check_out','ALL');
		}

		if($this->logged_module['role'] != 'admin')
		{
			$where['not_admin'] 	= '(a.requestor_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			// $where['a.requestor_id'] = $this->logged_user['employment_id'];
			// $or_where['a.createdBY'] = $this->logged_user['employment_id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_hotel_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_hotel_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, b.department_id, c.name as dept_name, d.name as branchName, b.name as requestor, e.email as requestor_email, b.staff_id,  f.name as created_name, f.email as created_email, (select status from sv__approval where table_name="sv__hotel" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__hotel" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user__employment_detail b', 'b.id = a.requestor_id', 'LEFT'),
			array('company__department c', 'c.id = b.department_id','LEFT'),
            array('company__branch d', 'd.id = c.branch_id', 'LEFT'),
            array('user e', 'e.id = a.requestor_id','LEFT'),
            array('user f', 'f.id = a.createdBY','LEFT'),
		);
		$orderBy['a.createdTS'] = 'desc';
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__hotel a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null,null,null,null);
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_hotel_filter_dept'			=> $this->input->post('sv_hotel_filter_dept'),
			'sv_hotel_filter_year'			=> $this->input->post('sv_hotel_filter_year'),
			'sv_hotel_filter_month'			=> $this->input->post('sv_hotel_filter_month'),

		);
		$this->session->set_userdata('sv__hotel',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_flight_filter()
    {
        $this->load->model('hr/model_flight_booking');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_flight_filter_dept')) && $this->input->post('sv_flight_filter_dept') != -1)? $this->input->post('sv_flight_filter_dept'):null;
		// $guestname	= (!empty($this->input->post('sv_flight_filter_gname')) && $this->input->post('sv_flight_filter_gname') != -1)? $this->input->post('sv_flight_filter_gname'):null;
		$status	= (!empty($this->input->post('sv_flight_filter_status')) && $this->input->post('sv_flight_filter_status') != -1)? $this->input->post('sv_flight_filter_status'):null;
		$year		= (!empty($this->input->post('sv_flight_filter_year')))? $this->input->post('sv_flight_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_flight_filter_month')))? $this->input->post('sv_flight_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_flight_filter_dept',$department);
			$where['b.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_flight_filter_dept','ALL');	
		}

		// if($guestname > 0) {
		// 	$gName = $this->model_form->sv__filter('user',array('name'),array('ID' => $guestname));
		// 	$this->session->set_userdata('sv_flight_filter_gname',$gName[0]['name']);
		// 	$where['a.guest_name'] = $guestname; 
		// } else {
		// 	$this->session->set_userdata('sv_flight_filter_gname','ALL');
		// }

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_flight_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_flight_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_flight_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_flight_filter_month','ALL');
		}

		if($this->logged_module['role'] != 'admin')
		{
			$where['not_admin'] 	= '(a.requester_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			// $where['a.requester_id'] = $this->logged_user['employment_id'];
			// $or_where['a.createdBY'] = $this->logged_user['employment_id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_flight_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_flight_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, b.name as requestor, c.id as department_id, c.name as dept_name, d.name as branchName, e.email as requestor_email, e.staff_id, group_concat(f.ID) as flight_guest_id, group_concat(f.guest_id) as guest_id, group_concat(g.name) as guest_name, group_concat(if(g.phone != "",g.phone,"N/A")) as guest_phone, group_concat(if(f.passport != "",f.passport,"N/A")) as guest_passport, group_concat(if(g.ic_no != "",g.ic_no,"N/A")) as guest_ic_no, h.name as created_name, h.email as created_email, (select status from sv__approval where table_name="sv__flight__booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__flight__booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user__employment_detail b', 'b.id = a.requester_id', 'LEFT'),
            array('company__department c', 'c.id = b.department_id', 'LEFT'),
            array('company__branch d', 'd.id = c.branch_id',''),
            array('user e', 'e.id = a.requester_id','LEFT'),
            array('sv__flight__booking_guest f','f.flight_id = a.ID and f.is_delete = 0','LEFT'),
            array('user g', 'g.id = f.guest_id','LEFT'),
            array('user h', 'h.id = f.createdBY','LEFT'),
		);
		$orderBy['a.ID'] = 'desc';
		$groupBy = array('a.ID'); 								
		$table = $this->model_form->sv__filter('sv__flight__booking a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null,null,null,null);
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_flight_filter_dept'		    => $this->input->post('sv_flight_filter_dept'),
			// 'sv_flight_filter_gname'		=> $this->input->post('sv_flight_filter_gname'),
			'sv_flight_filter_year'			=> $this->input->post('sv_flight_filter_year'),
			'sv_flight_filter_month'		=> $this->input->post('sv_flight_filter_month'),

		);
		$this->session->set_userdata('sv__flight__booking',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_training_request_filter()
	{
		$this->load->model('hr/model_training_request');
		//$userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department			= (!empty($this->input->post('sv_request_dept')) && $this->input->post('sv_request_dept') != -1)? $this->input->post('sv_request_dept'):null;
		$business_entity	= (!empty($this->input->post('sv_request_business')) && $this->input->post('sv_request_business') != -1)?$this->input->post('sv_request_business'):null;
		$status				= (!empty($this->input->post('sv_request_filter_status')) && $this->input->post('sv_request_filter_status') != -1)? $this->input->post('sv_request_filter_status'):null;	
		$year				= (!empty($this->input->post('sv_request_year')))? $this->input->post('sv_request_year'):date('Y');
		$month				= (!empty($this->input->post('sv_request_month')))? $this->input->post('sv_request_month'):null;
		$hod				= (!empty($this->input->post('sv_request_hod')))? $this->input->post('sv_request_hod'):null;

		$where 		= array(); 
		// $or_where 	= array();

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_request_dept',$department);
			if(!empty($hod))
			{
				$where['a.department_id'] = $this->logged_user['department_id'];
				$or_where['e.department_id'] = $this->logged_user['department_id'];
			}
				// $where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_request_dept','ALL');
		}

		if( !empty($business_entity) && $business_entity > 0) {
			$this->session->set_userdata('sv_request_business',$business_entity);
			$where['a.business_entity_id'] = $business_entity; 
		} else {
			$this->session->set_userdata('sv_request_business','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_request_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_request_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_request_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_request_month','ALL');
		}

		$hod = $this->company->getDepartmentHOD(array('a.hod_id' => $this->logged_user['id']));
		if($this->logged_module['role'] != 'admin')
		{
			if(!empty($hod))
			{
				$where['not_admin'] 	= '(a.department_id = '.$this->logged_user['department_id'].' or e.department_id = '.$this->logged_user['department_id'].')';
				// $where['a.department_id'] = $this->logged_user['department_id'];
				// $or_where['e.department_id'] = $this->logged_user['department_id'];
			}else{
				$where['not_admin'] 	= '(a.user_id = '.$this->logged_user['id'].' or e.user_id = '.$this->logged_user['id'].')';
				// $where['a.user_id'] = $this->logged_user['employment_id'];
				// $or_where['e.user_id'] = $this->logged_user['employment_id'];
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_request_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_request_filter_status','ALL');
		}

		/* filter data */

		//$where['a.borrower_id'] = $userID;

		// $select = array('a.*, f.staff_id, b.name as requestor, b.email, h.name as business_entity, c.name as dept_name, d.name as branchName, group_concat(e.id) as participant_id, group_concat(e.user_id) as participant_user_id, group_concat(f.name) as participant_name, g.ID as uploadID, g.photo_title, g.file_location, (select status from sv__approval where table_name="sv__training_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__training_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$select = array('a.*, f.staff_id, b.name as requestor, b.email, h.name as business_entity, c.name as dept_name, d.name as branchName, group_concat(e.id) as participant_id, group_concat(e.user_id) as participant_user_id, group_concat(f.name) as participant_name, group_concat(e.department_id) as participant_department_id, group_concat(e.hod_status) as participant_hod_status, group_concat(ifnull(i.name,"")) as participant_hod_name, g.ID as uploadID, g.photo_title, g.file_location, (select status from sv__approval where table_name="sv__training_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__training_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');

		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.user_id','left'),
			array('company__department c','c.id = a.department_id','left'),
			array('company__branch d','d.id = c.branch_id','left'),
			array('sv__training_request_participant e','e.sv_trainingRequest_id = a.ID and e.is_delete = 0','left'),
			array('user f','f.id = e.user_id','left'),
			array('sv__training_request__upload g','g.sv_trainingRequest_id = a.ID and g.is_delete = 0','left'),
			array('company__business_entity h', 'h.id = a.business_entity_id','left'),
			array('user i','i.id = e.approvedBY','left')
		);
		$orderBy['a.ID'] = 'asc';
		$groupBy = array('a.ID'); 								
		$table = $this->model_form->sv__filter('sv__training_request a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null, null, null, null);
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'sv_request_dept'				=> $this->input->post('sv_request_dept'),
			'sv_request_business'			=> $this->input->post('sv_request_business'),
			'sv_request_year'				=> $this->input->post('sv_request_year'),
			'sv_request_month'				=> $this->input->post('sv_request_month'),
			'sv_request_filter_status'		=> $this->input->post('sv_request_month'),

		);
		$this->session->set_userdata('sv__training_request',$data['table']);
		echo json_encode($data);
		return true;
    }

    function get_evaluation_filter()
	{
		$this->load->model('hr/model_training_evaluation');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department		= (!empty($this->input->post('sv_evaluation_filter_dept')) && $this->input->post('sv_evaluation_filter_dept') != -1)? $this->input->post('sv_evaluation_filter_dept'):null;
		$package		= (!empty($this->input->post('sv_evaluation_filter_package')) && $this->input->post('sv_evaluation_filter_package') != -1)? $this->input->post('sv_evaluation_filter_package'):null;
		$year			= (!empty($this->input->post('sv_evaluation_filter_year')))? $this->input->post('sv_evaluation_filter_year'):date('Y');
		$month			= (!empty($this->input->post('sv_evaluation_filter_month')))? $this->input->post('sv_evaluation_filter_month'):null;
		$training_date	= (!empty($this->input->post('sv_evaluation_filter_training_date')))? $this->input->post('sv_evaluation_filter_training_date'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_evaluation_filter_dept',$department);
			$where['b.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_evaluation_filter_dept','ALL');
		}

		if(!empty($package)) {
			$this->session->set_userdata('sv_evaluation_filter_package',$package);
			$where['c.packages'] = $package; 
		} else {
			$this->session->set_userdata('sv_evaluation_filter_package','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_evaluation_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_evaluation_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_evaluation_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_evaluation_filter_month','ALL');
		}

		if(!empty($training_date) && $training_date > 0) {
			$this->session->set_userdata('sv_evaluation_filter_month',$training_date);
			$where['c.training_date'] = date('Y-m-d',strtotime($training_date)); 
		} else {
			$this->session->set_userdata('sv_evaluation_filter_month','ALL');
		}

		if($this->logged_module['role'] != 'admin')
		{
			$where['b.user_id'] = $this->logged_user['employment_id'];
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, b.*, c.*, d.name as userName, d.staff_id, e.name as dept_name, f.name as branchName');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('sv__evaluation b', 'b.ID = a.sv_evaluation_id', ''),
			array('sv__evaluation_training c', 'c.ID = a.sv_evaluation_training_id', ''),
			array('user d','d.id = b.user_id', ''),
			array('company__department e','e.id = b.department_id', ''),
			array('company__branch f','f.id = e.branch_id', ''),
		);
		$orderBy['a.ID'] = 'asc';
		$groupBy = array('a.sv_evaluation_id'); 								
		$table = $this->model_form->sv__filter('sv__evaluation__answer a',$select, $where, $join, $orderBy, $groupBy);
		 // debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_evaluation_filter_dept'		=> $this->input->post('sv_evaluation_filter_dept'),
			'sv_evaluation_filter_package'	=> $this->input->post('sv_evaluation_filter_package'),
			'sv_evaluation_filter_year'		=> $this->input->post('sv_evaluation_filter_year'),
			'sv_evaluation_filter_month'	=> $this->input->post('sv_evaluation_filter_month'),
		);
		$this->session->set_userdata('sv__evaluation__answer',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_it_equipment_filter()
	{
		$this->load->model('hr/model_av_it');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_equipment_filter_dept')) && $this->input->post('sv_equipment_filter_dept') != -1)? $this->input->post('sv_equipment_filter_dept'):null;
		$status	= (!empty($this->input->post('sv_equipment_filter_status')) && $this->input->post('sv_equipment_filter_status') != -1)? $this->input->post('sv_equipment_filter_status'):null;
		$year		= (!empty($this->input->post('sv_equipment_filter_year')))? $this->input->post('sv_equipment_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_equipment_filter_month')))? $this->input->post('sv_equipment_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_equipment_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_equipment_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_equipment_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_equipment_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_equipment_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_equipment_filter_month','ALL');
		}

		if($_SESSION['user']['division_id'] == 19 || $_SESSION['user']['division_id'] == 10)
		{
			$where['a.borrower_id'] = $this->logged_user['employment_id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_equipment_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_equipment_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		if($this->logged_user['division_id'] != 19)
		{
			$where['a.borrower_id'] = $this->logged_user['employment_id'];
			$or_where['a.createdBY'] = $this->logged_user['employment_id'];
		}else{
			$or_where = '';
		}

		$select = array('a.*, b.name as borrower_name, b.staff_id, b.email, c.name as dept_name, d.name as  branchName,(select status from sv__approval where table_name="sv__it__equipment" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__it__equipment" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.borrower_id','LEFT'),
			array('company__department c','c.id = a.department_id','LEFT'),
			array('company__branch d','d.id = c.branch_id','LEFT'),
		);
		$orderBy['a.ID'] = 'asc';
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__it__equipment a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		// debug($this->db->last_query());

		// $table = $this->model_av_it->getEquipmentList(null,$department,$year,$month);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_equipment_filter_dept'		=> $this->input->post('sv_equipment_filter_dept'),
			'sv_equipment_filter_year'		=> $this->input->post('sv_equipment_filter_year'),
			'sv_equipment_filter_month'		=> $this->input->post('sv_equipment_filter_month'),

		);
		$this->session->set_userdata('sv__it__equipment',$data['table']);
		echo json_encode($data);
		return true;
    }

    function get_it_service_filter()
	{
		$this->load->model('hr/model_it_service');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_service_filter_dept')) && $this->input->post('sv_service_filter_dept') != -1)? $this->input->post('sv_service_filter_dept'):null;
		$status	= (!empty($this->input->post('sv_service_filter_status')) && $this->input->post('sv_service_filter_status') != -1)? $this->input->post('sv_service_filter_status'):null;
		$year		= (!empty($this->input->post('sv_service_filter_year')))? $this->input->post('sv_service_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_service_filter_month')))? $this->input->post('sv_service_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_service_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_service_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_service_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_service_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_service_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_service_filter_month','ALL');
		}

		if($_SESSION['user']['division_id'] == 19 || $_SESSION['user']['division_id'] == 10)
		{
			$where['a.user_id'] = $this->logged_user['employment_id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_service_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_service_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		if($this->logged_user['division_id'] != 19)
		{
			$where['a.user_id'] = $this->logged_user['employment_id'];
			$or_where['a.createdBY'] = $this->logged_user['employment_id'];
		}else{
			$or_where = '';
		}

		$select = array('a.*, b.name as requestor, c.name as dept_name, d.name as  branchName,(select status from sv__approval where table_name="sv__it_service" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__it_service" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.user_id','LEFT'),
			array('company__department c','c.id = a.department_id','LEFT'),
			array('company__branch d','d.id = c.branch_id','LEFT'),

		);
		$orderBy['a.ID'] = 'desc';
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__it_service a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		// debug($this->db->last_query());

		// $table = $this->model_av_it->getEquipmentList(null,$department,$year,$month);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_service_filter_dept'		=> $this->input->post('sv_service_filter_dept'),
			'sv_service_filter_year'		=> $this->input->post('sv_service_filter_year'),
			'sv_service_filter_month'		=> $this->input->post('sv_service_filter_month'),

		);
		$this->session->set_userdata('sv__it_service',$data['table']);
		echo json_encode($data);
		return true;
	}
    
    function get_police_filter()
	{
		$this->load->model('hr/model_police_report');

		//$userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_police_filter_dept')) && $this->input->post('sv_police_filter_dept') != -1)? $this->input->post('sv_police_filter_dept'):null;
		$year		= (!empty($this->input->post('sv_police_filter_year')))? $this->input->post('sv_police_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_police_filter_month')))? $this->input->post('sv_police_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_police_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_police_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_police_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_police_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_police_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_police_filter_month','ALL');
		}

		if($_SESSION['user']['division_id'] == 19 || $_SESSION['user']['division_id'] == 10)
		{
			$where['a.user_id'] = $this->logged_user['employment_id'];
		}

		/* filter data */

		//$where['a.borrower_id'] = $userID;

		$select = array('a.*, b.name as requestor, b.staff_id, b.email, c.name as dept_name, d.name as  branchName, e.file_title, e.file_location');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.user_id','LEFT'),
			array('company__department c','c.id = a.department_id','LEFT'),
			array('company__branch d','d.id = c.branch_id','LEFT'),
			array('sv__police_report__upload e', 'e.sv_policeReport_id = a.ID', 'LEFT'),
		);
		$orderBy['a.ID'] = 'desc';
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__police_report a',$select, $where, $join, $orderBy);
		// debug($this->db->last_query());

		// $table = $this->model_av_it->getEquipmentList(null,$department,$year,$month);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_police_filter_dept'		    => $this->input->post('sv_police_filter_dept'),
			'sv_police_filter_year'		    => $this->input->post('sv_police_filter_year'),
			'sv_police_filter_month'		=> $this->input->post('sv_police_filter_month'),

		);
		$this->session->set_userdata('sv__police_report',$data['table']);
		echo json_encode($data);
		return true;
    }

    function get_it_system_filter()
	{
		$this->load->model('hr/model_service_form');

		// $userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$department	= (!empty($this->input->post('sv_system_filter_dept')) && $this->input->post('sv_system_filter_dept') != -1)? $this->input->post('sv_system_filter_dept'):null;
		$status	= (!empty($this->input->post('sv_system_filter_status')) && $this->input->post('sv_system_filter_status') != -1)? $this->input->post('sv_system_filter_status'):null;
		$inexternal	= (!empty($this->input->post('sv_system_filter_inexternal')) && $this->input->post('sv_system_filter_inexternal') != -1)? $this->input->post('sv_system_filter_inexternal'):null;
		$year		= (!empty($this->input->post('sv_system_filter_year')))? $this->input->post('sv_system_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_system_filter_month')))? $this->input->post('sv_system_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_system_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_system_filter_dept','ALL');
		}

		if($inexternal > 0){
			if($inexternal == 2) {
				$this->session->set_userdata('sv_system_filter_inexternal',$inexternal);
				// $where['a.cust_id'] = $inexternal; 
				$where_not_in['a.cust_id'] = 0;
			} else {
				$this->session->set_userdata('sv_system_filter_inexternal',$inexternal);
				$where['a.cust_id'] = 0;
			}
		}else{
			$this->session->set_userdata('sv_system_filter_inexternal','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_system_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_system_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_system_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_system_filter_month','ALL');
		}

		if($_SESSION['user']['division_id'] == 10)
		{
			$where['a.user_id'] = $this->logged_user['employment_id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_system_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_system_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;      

		$select = array('a.*, b.name as requestor, c.name as dept_name, d.name as  branchName, e.systemname, f.name, (select status from sv__approval where table_name="sv__system" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__system" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('user b','b.id = a.user_id','LEFT'),
			array('company__department c','c.id = a.department_id','LEFT'),
			array('company__branch d','d.id = c.branch_id','LEFT'),
			array('_ref__system_name e', 'e.ID = a.systemID', 'LEFT'),
			array('customer f', 'f.ID = a.cust_id', 'LEFT')

		);
		$orderBy['a.ID'] = 'desc';
		// $groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_form->sv__filter('sv__system a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null, (!empty($where_not_in))?$where_not_in:null);
		// debug($this->db->last_query());

		// $table = $this->model_av_it->getEquipmentList(null,$department,$year,$month);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_system_filter_dept'			=> $this->input->post('sv_system_filter_dept'),
			'sv_system_filter_inexternal'	=> $this->input->post('sv_system_filter_inexternal'),
			'sv_system_filter_year'			=> $this->input->post('sv_system_filter_year'),
			'sv_system_filter_month'		=> $this->input->post('sv_system_filter_month'),
		);
		$this->session->set_userdata('sv__system',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_delivery_filter()
	{
		$this->load->model('hr/model_delivery');

		$department	= (!empty($this->input->post('sv_delivery_filter_dept')) && $this->input->post('sv_delivery_filter_dept') != -1)? $this->input->post('sv_delivery_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_delivery_filter_status')) && $this->input->post('sv_delivery_filter_status') != -1)? $this->input->post('sv_delivery_filter_status'):null;
		$task		= (!empty($this->input->post('sv_delivery_filter_task')) && $this->input->post('sv_delivery_filter_task') != -1)? $this->input->post('sv_delivery_filter_task'):null;
		$year		= (!empty($this->input->post('sv_delivery_filter_year')))? $this->input->post('sv_delivery_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_delivery_filter_month')))? $this->input->post('sv_delivery_filter_month'):null;

		$where 		= array(); 
		$like 		= array();

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_delivery_filter_dept',$department);
			// $where['a.department_id'] = $department; 
			$where['special_where'] = '(a.department_id = '.$department.' OR f.department_id = '.$department.')'; 
		} else {
			$this->session->set_userdata('sv_delivery_filter_dept','ALL');
		}

		if($task != '') {
			$this->session->set_userdata('sv_delivery_filter_task',$task);
			$like['a.task'] = $task; 
		} else {
			$this->session->set_userdata('sv_delivery_filter_task','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_delivery_filter_year',$year);
			$where['YEAR(a.date)'] = $year;
		} else {
			$this->session->set_userdata('sv_delivery_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_delivery_filter_month',$month);
			$where['MONTH(a.date)'] = $month; 
		} else {
			$this->session->set_userdata('sv_delivery_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin'] 	= '(a.requester_id = '.$this->logged_user['id'].' or a.created_by = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_delivery_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_delivery_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, i.name as requester_dept, j.name as requester_branch, b.shortName as department_short_name, b.id as requester_department_id, b.name as dept_name, c.name as branchName, e.name as created_name, e.email as created_email, f.department_id as created_department_id, g.name as created_dept_name, h.name as created_branchName, d.detail_name, d.detail_address, d.detail_item, d.detail_remark, (select status from sv__approval where table_name="sv__delivery" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__delivery" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id', 'LEFT'),
            array('company__branch c', 'c.id = b.branch_id','LEFT'),
            array('sv__delivery_detail d', 'd.delivery_id = a.ID and d.is_delete = 0','LEFT'),
            array('user e', 'e.id = a.created_by','LEFT'),
            array('user__employment_detail f', 'f.id = a.created_by', 'LEFT'),
            array('company__department g', 'g.id = f.department_id', 'LEFT'),
            array('company__branch h', 'h.id = g.branch_id','LEFT'),
            array('company__department i', 'i.id = a.department_id', 'LEFT'),
            array('company__branch j', 'j.id = i.branch_id','LEFT'),
		);
		$groupBy = array('a.ID');
		$orderBy['a.id'] = 'desc';

		$table = $this->model_form->sv__filter('sv__delivery a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null, null, null, null, null, $like);
		// debug($this->db->last_query());
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_delivery_filter_dept'		=> $this->input->post('sv_delivery_filter_dept'),
			'sv_delivery_filter_status'		=> $this->input->post('sv_delivery_filter_status'),
			'sv_delivery_filter_task'		=> $this->input->post('sv_delivery_filter_task'),
			'sv_delivery_filter_year'		=> $this->input->post('sv_delivery_filter_year'),
			'sv_delivery_filter_month'		=> $this->input->post('sv_delivery_filter_month'),

		);
		$this->session->set_userdata('sv__delivery',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_qr_code_filter()
	{
		$this->load->model('hr/model_qr_code');

		$department	= (!empty($this->input->post('sv_qr_code_filter_dept')) && $this->input->post('sv_qr_code_filter_dept') != -1)? $this->input->post('sv_qr_code_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_qr_code_filter_status')) && $this->input->post('sv_qr_code_filter_status') != -1)? $this->input->post('sv_qr_code_filter_status'):null;
		$year		= (!empty($this->input->post('sv_qr_code_filter_year')))? $this->input->post('sv_qr_code_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_qr_code_filter_month')))? $this->input->post('sv_qr_code_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_qr_code_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_qr_code_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_qr_code_filter_year',$year);
			$where['YEAR(a.created_ts)'] = $year;
		} else {
			$this->session->set_userdata('sv_qr_code_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_qr_code_filter_month',$month);
			$where['MONTH(a.created_ts)'] = $month; 
		} else {
			$this->session->set_userdata('sv_qr_code_filter_month','ALL');
		}

		if($this->logged_module['role'] != 'admin')
		{
			$where['a.requester_id'] = $this->logged_user['id'];
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_qr_code_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_qr_code_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, e.name as requestor, c.shortName as department_short_name, c.id as department_id, c.name as dept_name, d.name as branchName, e.email as requestor_email, e.staff_id, l.organizer as cpd_organizer, group_concat(b.category_id) as cpd_category_id, group_concat(m.name SEPARATOR ", ") as cpd_category, h.name as created_name, h.email as created_email, i.department_id as created_department_id, j.name as created_dept_name, k.name as created_branchName, (select status from sv__approval where table_name="sv__qr_code" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__qr_code" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
            array('sv__qr_code_category b','b.qr_code_id = a.ID and b.is_delete = 0','LEFT'),
            array('company__department c', 'c.id = a.department_id', 'LEFT'),
            array('company__branch d', 'd.id = c.branch_id',''),
            array('user e', 'e.id = a.requester_id','LEFT'),
            array('user h', 'h.id = a.created_by','LEFT'),
            array('user__employment_detail i', 'i.id = a.created_by', 'LEFT'),
            array('company__department j', 'j.id = i.department_id', 'LEFT'),
            array('company__branch k', 'k.id = j.branch_id',''),
            array('hr__cpd_organizer l', 'l.id = a.organizer_id','LEFT'),
            array('hr__cpd_category m', 'm.id = b.category_id and m.is_delete = 0','LEFT'),
		);
		$groupBy = array('a.ID');
		$orderBy['a.ID'] = 'desc';

		$table = $this->model_form->sv__filter('sv__qr_code a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null);
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'user_id' 						=> $this->input->post('user_id'),
			'sv_qr_code_filter_dept'		=> $this->input->post('sv_qr_code_filter_dept'),
			'sv_qr_code_filter_status'		=> $this->input->post('sv_qr_code_filter_status'),
			'sv_qr_code_filter_year'		=> $this->input->post('sv_qr_code_filter_year'),
			'sv_qr_code_filter_month'		=> $this->input->post('sv_qr_code_filter_month'),

		);
		$this->session->set_userdata('sv__qr_code',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_invoice_filter()
	{
		$this->load->model('hr/model_invoice');

		$finance_staff = $this->model_user->getUsersByFilter(array('d.id'=>$this->logged_user['id'],'d.division_id'=> 8));

		$department	= (!empty($this->input->post('sv_invoice_filter_dept')) && $this->input->post('sv_invoice_filter_dept') != -1)? $this->input->post('sv_invoice_filter_dept'):null;
		$entity		= (!empty($this->input->post('sv_invoice_filter_entity')) && $this->input->post('sv_invoice_filter_entity') != -1)? $this->input->post('sv_invoice_filter_entity'):null;
		$branch		= (!empty($this->input->post('sv_invoice_filter_branch')) && $this->input->post('sv_invoice_filter_branch') != -1)? $this->input->post('sv_invoice_filter_branch'):null;
		$status		= (!empty($this->input->post('sv_invoice_filter_status')) && $this->input->post('sv_invoice_filter_status') != -1)? $this->input->post('sv_invoice_filter_status'):null;
		$requester	= (!empty($this->input->post('sv_invoice_filter_requester')) && $this->input->post('sv_invoice_filter_requester') != -1)? $this->input->post('sv_invoice_filter_requester'):null;
		$approver	= (!empty($this->input->post('sv_invoice_filter_approver')) && $this->input->post('sv_invoice_filter_approver') != -1)? $this->input->post('sv_invoice_filter_approver'):null;
		$preparer	= (!empty($this->input->post('sv_invoice_filter_preparer')) && $this->input->post('sv_invoice_filter_preparer') != -1)? $this->input->post('sv_invoice_filter_preparer'):null;
		$date_from	= (!empty($this->input->post('sv_invoice_filter_date_from')))? $this->input->post('sv_invoice_filter_date_from'):null;
		$date_to	= (!empty($this->input->post('sv_invoice_filter_date_to')))? $this->input->post('sv_invoice_filter_date_to'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_invoice_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_dept','ALL');
		}

		if($entity > 0) {
			$this->session->set_userdata('sv_invoice_filter_entity',$entity);
			$where['a.business_entity_id'] = $entity; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_entity','ALL');
		}

		if($branch > 0) {
			$this->session->set_userdata('sv_invoice_filter_branch',$branch);
			$where['a.branch_id'] = $branch; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_branch','ALL');
		}

		if($requester > 0) {
			$this->session->set_userdata('sv_invoice_filter_requester',$requester);
			$where['a.requester_id'] = $requester; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_requester','ALL');
		}

		if($approver > 0) {
			$this->session->set_userdata('sv_invoice_filter_approver',$approver);
			$where['a.approvedBy'] = $approver; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_approver','ALL');
		}

		if($preparer > 0) {
			$this->session->set_userdata('sv_invoice_filter_preparer',$preparer);
			$where['a.preparedBY'] = $preparer; 
		} else {
			$this->session->set_userdata('sv_invoice_filter_preparer','ALL');
		}

		if(!empty($date_from)) {
			$this->session->set_userdata('sv_invoice_filter_date_from',$date_from);
			$where['DATE(a.createdTS)'] = date('Y-m-d',strtotime($date_from));
		} else {
			$this->session->set_userdata('sv_invoice_filter_date_from','ALL');
		}

		if(!empty($date_to)) {
			$this->session->set_userdata('sv_invoice_filter_date_to',$date_to);
			if(!empty($date_from)) {
				unset($where['DATE(a.createdTS)']);
				$where['special_where'] = 'DATE(a.createdTS) BETWEEN "'.date('Y-m-d',strtotime($date_from)).'" AND "'.date('Y-m-d',strtotime($date_to)).'"'; 
			}else{
				$where['DATE(a.createdTS)'] = date('Y-m-d',strtotime($date_to)); 
			}
		} else {
			$this->session->set_userdata('sv_invoice_filter_date_to','ALL');
		}

		if(!$finance_staff)
		{
			$where['a.requester_id'] = $this->logged_user['id'];
		}

		if($status >= 0) 
		{
			if($status == 'Pending') 
			{
				$where['a.approval_status'] = 0;
				$where['a.prepared_status'] = 0;
			}
			if($status == 'Approved') $where['a.approval_status'] = 1;
			if($status == 'Rejected') $where['a.approval_status'] = 2;
			if($status == 'Completed') $where['a.prepared_status'] = 1;
			if($status == 'Duplicate') $where['a.prepared_status'] = 2;

			$this->session->set_userdata('sv_invoice_filter_status',$status);
		} else {
			$this->session->set_userdata('sv_invoice_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, group_concat(g.id separator "->") as description_id, group_concat(b.id separator "->") as detail_id, group_concat(g.type separator "->") as description_type, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.year_end separator "->") as description_year_end, group_concat(b.amount separator "->") as description_amount, count(g.id) as total_description,
			l.name as customerName, k.description as job_description,
			d.name as dept_name, e.name as branchName, f.name as entity_name, j.name as inv_branch,
			c.staff_id as requester_staff_id, c.name as requester_name, c.email as requester_email,
			h.id as approved_by_staff_id, h.name as approved_by_name, h.email as approved_by_email,
			i.staff_id as prepared_by_staff_id, i.name as prepared_by_name, i.email as prepared_by_email,
			(select group_concat(mgr.manager_id) from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_id,
			(select group_concat(user.name separator "->") from sv__invoice_manager_approval mgr join user on user.id = mgr.manager_id and user.is_delete = 0 where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_name,
			(select group_concat(user.email separator "->") from sv__invoice_manager_approval mgr join user on user.id = mgr.manager_id and user.is_delete = 0 where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_email,
			(select group_concat(mgr.approval_status separator "->") from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_status,
			(select group_concat(mgr.approval_remark separator "->") from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_remark');
		$where['a.is_delete'] = 0;
		$join = array
		(
            array('sv__invoice_description b','b.invoice_id = a.ID and b.is_delete = 0','left'),
			array('user c','c.id = a.requester_id and c.is_delete = 0',''),
			array('company__department d','d.id = a.department_id',''),
			array('company__branch e','e.id = d.branch_id',''),
			array('company__business_entity f','f.id = a.business_entity_id',''),
			array('sv__ref_invoice_description g','g.id = b.description_id','left'),
			array('user h','h.id = a.approvedBY and h.is_delete = 0','left'),
			array('user i','i.id = a.preparedBY and i.is_delete = 0','left'),
			array('company__branch j','j.id = a.branch_id','left'),
			array('mgsdb_dev.view__job k','k.ID = a.job_id','left'),
			array('customer l','l.id = a.client_id','left'),
		);
		$groupBy = array('a.ID');
		// $orderBy['a.ID'] = 'desc';
		$orderBy = array
		(
			'a.ID'				=> 'desc', 
			'a.job_id'			=> 'asc', 
			'a.inv_form_no'		=> 'asc'
		);

		$table = $this->model_form->sv__filter('sv__invoice a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null);

		// debug($where);
		// debug($this->input->post());
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'sv_invoice_filter_dept'		=> $this->input->post('sv_invoice_filter_dept'),
			'sv_invoice_filter_entity'		=> $this->input->post('sv_invoice_filter_entity'),
			'sv_invoice_filter_branch'		=> $this->input->post('sv_invoice_filter_branch'),
			'sv_invoice_filter_status'		=> $this->input->post('sv_invoice_filter_status'),
			'sv_invoice_filter_requester'	=> $this->input->post('sv_invoice_filter_requester'),
			'sv_invoice_filter_approver'	=> $this->input->post('sv_invoice_filter_approver'),
			'sv_invoice_filter_preparer'	=> $this->input->post('sv_invoice_filter_preparer'),
			'sv_invoice_filter_date_from'	=> $this->input->post('sv_invoice_filter_date_from'),
			'sv_invoice_filter_date_to'		=> $this->input->post('sv_invoice_filter_date_to'),

		);
		$this->session->set_userdata('sv__invoice',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_credit_note_filter()
	{
		$this->load->model('hr/model_credit_note');

		$finance_staff = $this->model_user->getUsersByFilter(array('d.id'=>$this->logged_user['id'],'d.division_id'=> 10));

		$department	= (!empty($this->input->post('sv_credit_note_filter_dept')) && $this->input->post('sv_credit_note_filter_dept') != -1)? $this->input->post('sv_credit_note_filter_dept'):null;
		$entity		= (!empty($this->input->post('sv_credit_note_filter_entity')) && $this->input->post('sv_credit_note_filter_entity') != -1)? $this->input->post('sv_credit_note_filter_entity'):null;
		$branch		= (!empty($this->input->post('sv_credit_note_filter_branch')) && $this->input->post('sv_credit_note_filter_branch') != -1)? $this->input->post('sv_credit_note_filter_branch'):null;
		$status		= (!empty($this->input->post('sv_credit_note_filter_status')) && $this->input->post('sv_credit_note_filter_status') != -1)? $this->input->post('sv_credit_note_filter_status'):null;
		$requester	= (!empty($this->input->post('sv_credit_note_filter_requester')) && $this->input->post('sv_credit_note_filter_requester') != -1)? $this->input->post('sv_credit_note_filter_requester'):null;
		$approver	= (!empty($this->input->post('sv_credit_note_filter_approver')) && $this->input->post('sv_credit_note_filter_approver') != -1)? $this->input->post('sv_credit_note_filter_approver'):null;
		$preparer	= (!empty($this->input->post('sv_credit_note_filter_preparer')) && $this->input->post('sv_credit_note_filter_preparer') != -1)? $this->input->post('sv_credit_note_filter_preparer'):null;
		$year		= (!empty($this->input->post('sv_credit_note_filter_year')))? $this->input->post('sv_credit_note_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_credit_note_filter_month')))? $this->input->post('sv_credit_note_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_credit_note_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_dept','ALL');
		}

		if($entity > 0) {
			$this->session->set_userdata('sv_credit_note_filter_entity',$entity);
			$where['a.business_entity_id'] = $entity; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_entity','ALL');
		}

		if($branch > 0) {
			$this->session->set_userdata('sv_credit_note_filter_branch',$branch);
			$where['a.branch_id'] = $branch; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_branch','ALL');
		}

		if($requester > 0) {
			$this->session->set_userdata('sv_credit_note_filter_requester',$requester);
			$where['a.requester_id'] = $requester; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_requester','ALL');
		}

		if($approver > 0) {
			$this->session->set_userdata('sv_credit_note_filter_approver',$approver);
			$where['a.approvedBy'] = $approver; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_approver','ALL');
		}

		if($preparer > 0) {
			$this->session->set_userdata('sv_credit_note_filter_preparer',$preparer);
			$where['a.preparedBY'] = $preparer; 
		} else {
			$this->session->set_userdata('sv_credit_note_filter_preparer','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_credit_note_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_credit_note_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_qr_code_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_qr_code_filter_month','ALL');
		}

		if(!$finance_staff)
		{
			$where['a.requester_id'] = $this->logged_user['id'];
		}

		if($status >= 0) 
		{
			if($status == 'Pending') 
			{
				$where['a.approval_status'] = 0;
				$where['a.prepared_status'] = 0;
			}
			if($status == 'Approved') $where['a.approval_status'] = 1;
			if($status == 'Rejected') $where['a.approval_status'] = 2;
			if($status == 'Completed') $where['a.prepared_status'] = 1;
			if($status == 'Duplicate') $where['a.prepared_status'] = 2;

			$this->session->set_userdata('sv_credit_note_filter_status',$status);
		} else {
			$this->session->set_userdata('sv_credit_note_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, format(a.total,2) as total, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.amount separator "->") as description_amount,
			l.name as customerName, k.description as job_description,
			d.name as dept_name, e.name as branchName, f.name as entity_name, j.name as inv_branch,
			c.staff_id as requester_staff_id, c.name as requester_name, c.email as requester_email,
			h.staff_id as approved_by_staff_id, h.name as approved_by_name, h.email as approved_by_email,
			i.staff_id as prepared_by_staff_id, i.name as prepared_by_name, i.email as prepared_by_email');
		$where['a.is_delete'] = 0;
		$join = array
		(
            array('sv__credit_note_description b','b.credit_note_id = a.ID and b.is_delete = 0','left'),
			array('user c','c.id = a.requester_id and c.is_delete = 0',''),
			array('company__department d','d.id = a.department_id',''),
			array('company__branch e','e.id = d.branch_id',''),
			array('company__business_entity f','f.id = a.business_entity_id',''),
			array('sv__ref_credit_note_description g','g.id = b.description_id','left'),
			array('user h','h.id = a.approvedBY and h.is_delete = 0','left'),
			array('user i','i.id = a.preparedBY and i.is_delete = 0','left'),
			array('company__branch j','j.id = a.branch_id','left'),
			array('mgsdb_dev.view__job k','k.ID = a.job_id','left'),
			array('customer l','l.id = a.client_id','left'),
		);
		$groupBy = array('a.ID');
		// $orderBy['a.ID'] = 'desc';
		$orderBy = array
		(
			'a.ID'				=> 'desc', 
			'a.job_id'			=> 'asc', 
			'a.cn_form_no'		=> 'asc'
		);

		$table = $this->model_form->sv__filter('sv__credit_note a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null);

		// debug($where);
		// debug($this->input->post());
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'sv_credit_note_filter_dept'		=> $this->input->post('sv_credit_note_filter_dept'),
			'sv_credit_note_filter_entity'		=> $this->input->post('sv_credit_note_filter_entity'),
			'sv_credit_note_filter_branch'		=> $this->input->post('sv_credit_note_filter_branch'),
			'sv_credit_note_filter_status'		=> $this->input->post('sv_credit_note_filter_status'),
			'sv_credit_note_filter_requester'	=> $this->input->post('sv_credit_note_filter_requester'),
			'sv_credit_note_filter_approver'	=> $this->input->post('sv_credit_note_filter_approver'),
			'sv_credit_note_filter_preparer'	=> $this->input->post('sv_credit_note_filter_preparer'),
			'sv_credit_note_filter_year'		=> $this->input->post('sv_credit_note_filter_year'),
			'sv_credit_note_filter_month'		=> $this->input->post('sv_credit_note_filter_month'),

		);
		$this->session->set_userdata('sv__credit_note',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_petty_cash_advance_filter()
	{
		$this->load->model('hr/model_petty_cash_advance');

		$finance_staff = $this->model_user->getUsersByFilter(array('d.id'=>$this->logged_user['id'],'d.division_id'=> 10));

		$department	= (!empty($this->input->post('sv_petty_cash_advance_filter_dept')) && $this->input->post('sv_petty_cash_advance_filter_dept') != -1)? $this->input->post('sv_petty_cash_advance_filter_dept'):null;
		$entity		= (!empty($this->input->post('sv_petty_cash_advance_filter_entity')) && $this->input->post('sv_petty_cash_advance_filter_entity') != -1)? $this->input->post('sv_petty_cash_advance_filter_entity'):null;
		$status		= (!empty($this->input->post('sv_petty_cash_advance_filter_status')) && $this->input->post('sv_petty_cash_advance_filter_status') != -1)? $this->input->post('sv_petty_cash_advance_filter_status'):null;
		$requester	= (!empty($this->input->post('sv_petty_cash_advance_filter_requester')) && $this->input->post('sv_petty_cash_advance_filter_requester') != -1)? $this->input->post('sv_petty_cash_advance_filter_requester'):null;
		$approver	= (!empty($this->input->post('sv_petty_cash_advance_filter_approver')) && $this->input->post('sv_petty_cash_advance_filter_approver') != -1)? $this->input->post('sv_petty_cash_advance_filter_approver'):null;
		$preparer	= (!empty($this->input->post('sv_petty_cash_advance_filter_preparer')) && $this->input->post('sv_petty_cash_advance_filter_preparer') != -1)? $this->input->post('sv_petty_cash_advance_filter_preparer'):null;
		$year		= (!empty($this->input->post('sv_petty_cash_advance_filter_year')))? $this->input->post('sv_petty_cash_advance_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_petty_cash_advance_filter_month')))? $this->input->post('sv_petty_cash_advance_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_dept','ALL');
		}

		if($entity > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_entity',$entity);
			$where['a.business_entity_id'] = $entity; 
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_entity','ALL');
		}

		if($requester > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_requester',$requester);
			$where['a.requester_id'] = $requester; 
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_requester','ALL');
		}

		if($approver > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_approver',$approver);
			$where['a.approvedBy'] = $approver; 
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_approver','ALL');
		}

		if($preparer > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_preparer',$preparer);
			$where['a.preparedBY'] = $preparer; 
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_preparer','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_petty_cash_advance_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_qr_code_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_qr_code_filter_month','ALL');
		}

		if(!$finance_staff)
		{
			$where['a.requester_id'] = $this->logged_user['id'];
		}

		if($status >= 0) 
		{
			if($status == 'Pending') 
			{
				$where['a.approval_status'] = 0;
				$where['a.prepared_status'] = 0;
			}
			if($status == 'Approved') $where['a.approval_status'] = 1;
			if($status == 'Rejected') $where['a.approval_status'] = 2;
			if($status == 'Completed') $where['a.prepared_status'] = 1;
			if($status == 'Duplicate') $where['a.prepared_status'] = 2;

			$this->session->set_userdata('sv_petty_cash_advance_filter_status',$status);
		} else {
			$this->session->set_userdata('sv_petty_cash_advance_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, format(a.total,2) as total, group_concat(g.id separator "->") as description_id, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.amount separator "->") as description_amount, (select group_concat(user_id separator "->") from sv__petty_cash_advance_member where petty_cash_advance_id = a.ID and is_delete = 0 group by a.ID) as member_id, (select group_concat(department_id separator "->") from sv__petty_cash_advance_member where petty_cash_advance_id = a.ID and is_delete = 0 group by a.ID) as member_department_id, l.name as customerName, k.description as job_description, d.name as dept_name, e.name as branchName, f.name as entity_name, c.staff_id as requester_staff_id, c.name as requester_name, c.email as requester_email, h.staff_id as approved_by_staff_id, h.name as approved_by_name, h.email as approved_by_email, i.staff_id as prepared_by_staff_id, i.name as prepared_by_name, i.email as prepared_by_email');
		$where['a.is_delete'] = 0;
		$join = array
		(
            array('sv__petty_cash_advance_description b','b.petty_cash_advance_id = a.ID and b.is_delete = 0','left'),
			array('user c','c.id = a.requester_id and c.is_delete = 0',''),
			array('company__department d','d.id = a.department_id',''),
			array('company__branch e','e.id = d.branch_id',''),
			array('company__business_entity f','f.id = a.business_entity_id',''),
			array('sv__ref_petty_cash_advance_description g','g.id = b.description_id','left'),
			array('user h','h.id = a.approvedBY and h.is_delete = 0','left'),
			array('user i','i.id = a.preparedBY and i.is_delete = 0','left'),
			array('mgsdb_dev.view__job k','k.ID = a.job_id','left'),
			array('customer l','l.id = a.client_id','left'),
		);
		$groupBy = array('a.ID');
		$orderBy['a.ID'] = 'desc';

		$table = $this->model_form->sv__filter('sv__petty_cash_advance a',$select, $where, $join, $orderBy, $groupBy, (!empty($having))?$having:null);

		// debug($where);
		// debug($this->input->post());
		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 									=> 'success',
			'message'									=> '',
			'table'										=> ($table)?$table:'No Data',
			'sv_petty_cash_advance_filter_dept'			=> $this->input->post('sv_petty_cash_advance_filter_dept'),
			'sv_petty_cash_advance_filter_entity'		=> $this->input->post('sv_petty_cash_advance_filter_entity'),
			'sv_petty_cash_advance_filter_status'		=> $this->input->post('sv_petty_cash_advance_filter_status'),
			'sv_petty_cash_advance_filter_requester'	=> $this->input->post('sv_petty_cash_advance_filter_requester'),
			'sv_petty_cash_advance_filter_approver'		=> $this->input->post('sv_petty_cash_advance_filter_approver'),
			'sv_petty_cash_advance_filter_preparer'		=> $this->input->post('sv_petty_cash_advance_filter_preparer'),
			'sv_petty_cash_advance_filter_year'			=> $this->input->post('sv_petty_cash_advance_filter_year'),
			'sv_petty_cash_advance_filter_month'		=> $this->input->post('sv_petty_cash_advance_filter_month'),

		);
		$this->session->set_userdata('sv__petty_cash_advance',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_zoom_booking_filter()
	{
		$this->load->model('hr/model_zoom_booking');

		$department	= (!empty($this->input->post('sv_zoom_booking_filter_dept')) && $this->input->post('sv_zoom_booking_filter_dept') != -1)? $this->input->post('sv_zoom_booking_filter_dept'):null;
		$account_id	= (!empty($this->input->post('sv_zoom_booking_filter_account')) && $this->input->post('sv_zoom_booking_filter_account') != -1)? $this->input->post('sv_zoom_booking_filter_account'):null;
		$status		= (!empty($this->input->post('sv_zoom_booking_filter_status')) && $this->input->post('sv_zoom_booking_filter_status') != -1)? $this->input->post('sv_zoom_booking_filter_status'):null;
		$year		= (!empty($this->input->post('sv_zoom_booking_filter_year')))? $this->input->post('sv_zoom_booking_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_zoom_booking_filter_month')))? $this->input->post('sv_zoom_booking_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_zoom_booking_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_zoom_booking_filter_dept','ALL');
		}

		if($account_id > 0) {
			$this->session->set_userdata('sv_zoom_booking_filter_account',$account_id);
			$where['a.account_id'] = $account_id; 
		} else {
			$this->session->set_userdata('sv_zoom_booking_filter_account','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_zoom_booking_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_zoom_booking_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_zoom_booking_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_zoom_booking_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($_SESSION['user']['division_id'] != 4)
		{
			if($module_access['access_lvl'] != 'admin')
			{
				$where['not_admin']	= '(a.requester = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_zoom_booking_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_zoom_booking_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, a.department_id, b.name as dept_name, h.shortName as department_short_name, c.name as branchName, d.account, d.username, d.password, d.meeting_user, d.webinar_user, d.remark, d.short_name, d.color_code, e.name as requester_name, e.email as requester_email, e.staff_id as requester_staff_id, g.name as created_name, g.email as created_email, h.id as created_department_id, h.name as created_dept_name, i.name as created_branch_name, (select status from sv__approval where table_name="sv__zoom_booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__zoom_booking" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id', 'LEFT'),
			array('company__branch c', 'c.id = b.branch_id', 'LEFT'),
			array('sv__ref_zoom_account d', 'd.id = a.account_id', 'LEFT'),
			array('user e', 'e.id = a.requester_id', ''),
			array('user g', 'g.id = a.createdBY', 'LEFT'),
			array('company__department h', 'h.id = (select department_id from user__employment_detail where id = g.id)', 'LEFT'),
			array('company__branch i', 'i.id = h.branch_id', 'LEFT'),
		);
		$orderBy['a.id'] = 'desc';

		$table = $this->model_form->sv__filter('sv__zoom_booking a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 							=> 'success',
			'message'							=> '',
			'table'								=> ($table)?$table:'No Data',
			'sv_zoom_booking_filter_dept'		=> $this->input->post('sv_zoom_booking_filter_dept'),
			'sv_zoom_booking_filter_account'	=> $this->input->post('sv_zoom_booking_filter_account'),
			'sv_zoom_booking_filter_status'		=> $this->input->post('sv_zoom_booking_filter_status'),
			'sv_zoom_booking_filter_year'		=> $this->input->post('sv_zoom_booking_filter_year'),
			'sv_zoom_booking_filter_month'		=> $this->input->post('sv_zoom_booking_filter_month'),

		);
		$this->session->set_userdata('sv__zoom_booking',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_talent_referral()
	{
		$this->load->model('hr/model_talent_referral');

		$department	= (!empty($this->input->post('sv_talent_referral_filter_dept')) && $this->input->post('sv_talent_referral_filter_dept') != -1)? $this->input->post('sv_talent_referral_filter_dept'):null;
		$status		= (!empty($this->input->post('sv_talent_referral_filter_status')) && $this->input->post('sv_talent_referral_filter_status') != -1)? $this->input->post('sv_talent_referral_filter_status'):null;
		$year		= (!empty($this->input->post('sv_talent_referral_filter_year')))? $this->input->post('sv_talent_referral_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_talent_referral_filter_month')))? $this->input->post('sv_talent_referral_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_talent_referral_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_talent_referral_filter_dept','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_talent_referral_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_talent_referral_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_talent_referral_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_talent_referral_filter_month','ALL');
		}

		if($_SESSION['user']['division_id'] != 5)
		{
			if($this->logged_module['role'] != 'admin')
			{
				$where['not_admin']	= '(a.requester_id = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
			}
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_talent_referral_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_talent_referral_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, b.shortName as department_short_name, b.name as dept_name, c.name as branchName, d.name as requester_name, d.email as requester_email, d.staff_id as requester_staff_id, e.name as created_name, e.email as created_email, f.id as created_department_id, f.shortName as created_dept_short_name, f.name as created_dept_name, g.name as created_branch_name, h.name as processedBY_name, h.email as processedBY_email, k.name as verifiedBY_name, k.email as verifiedBY_email, (select status from sv__approval where table_name="sv__talent_referral" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__talent_referral" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id','LEFT'),
			array('company__branch c', 'c.id = b.branch_id', 'LEFT'),
			array('user d', 'd.id = a.requester_id','LEFT'),
			array('user e', 'e.id = a.createdBY', 'LEFT'),
			array('company__department f', 'f.id = (select department_id from user__employment_detail where id = e.id)', 'LEFT'),
			array('company__branch g', 'g.id = f.branch_id', 'LEFT'),
			array('user h', 'h.id = a.processedBY', 'LEFT'),
			array('company__department i', 'i.id = (select department_id from user__employment_detail where id = h.id)', 'LEFT'),
			array('company__branch j', 'j.id = i.branch_id', 'LEFT'),
			array('user k', 'k.id = a.verifiedBY', 'LEFT'),
			array('company__department l', 'l.id = (select department_id from user__employment_detail where id = k.id)', 'LEFT'),
			array('company__branch m', 'm.id = l.branch_id', 'LEFT'),
		);
		$orderBy['a.ID'] = 'desc';

		$table = $this->model_form->sv__filter('sv__talent_referral a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 							=> 'success',
			'message'							=> '',
			'table'								=> ($table)?$table:'No Data',
			'sv_talent_referral_filter_dept'	=> $this->input->post('sv_talent_referral_filter_dept'),
			'sv_talent_referral_filter_status'	=> $this->input->post('sv_talent_referral_filter_status'),
			'sv_talent_referral_filter_year'	=> $this->input->post('sv_talent_referral_filter_year'),
			'sv_talent_referral_filter_month'	=> $this->input->post('sv_talent_referral_filter_month'),

		);
		$this->session->set_userdata('sv__talent_referral',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_job_request_filter()
	{
		$this->load->model('hr/model_job_request');

		$department	= (!empty($this->input->post('sv_job_request_filter_dept')) && $this->input->post('sv_job_request_filter_dept') != -1)? $this->input->post('sv_job_request_filter_dept'):null;
		$client_id	= (!empty($this->input->post('sv_job_request_filter_client_id')) && $this->input->post('sv_job_request_filter_client_id') != -1)? $this->input->post('sv_job_request_filter_client_id'):null;
		$status		= (!empty($this->input->post('sv_job_request_filter_status')) && $this->input->post('sv_job_request_filter_status') != -1)? $this->input->post('sv_zoom_booking_filter_status'):null;
		$year		= (!empty($this->input->post('sv_job_request_filter_year')))? $this->input->post('sv_job_request_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_job_request_filter_month')))? $this->input->post('sv_job_request_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_job_request_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_job_request_filter_dept','ALL');
		}

		if($client_id > 0) {
			$this->session->set_userdata('sv_job_request_filter_client_id',$client_id);
			$where['a.client_id'] = $client_id; 
		} else {
			$this->session->set_userdata('sv_job_request_filter_client_id','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_job_request_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_job_request_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_job_request_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_job_request_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 2
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($module_access['access_lvl'] != 'admin')
		{
			$where['not_admin']	= '(a.requester = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_job_request_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_job_request_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, a.department_id, b.name as dept_name, f.shortName as department_short_name, c.name as branchName, d.name as requester_name, d.email as requester_email, d.staff_id as requester_staff_id, e.name as created_name, e.email as created_email, f.id as created_department_id, f.name as created_dept_name, g.name as created_branch_name, h.name as client_name, (select status from sv__approval where table_name="sv__job_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__job_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id','LEFT'),
			array('company__branch c', 'c.id = b.branch_id', 'LEFT'),
			array('user d', 'd.id = a.requester_id',''),
			array('user e', 'e.id = a.createdBY', 'LEFT'),
			array('company__department f', 'f.id = (select department_id from user__employment_detail where id = e.id)', 'LEFT'),
			array('company__branch g', 'g.id = f.branch_id', 'LEFT'),
			array('customer h', 'h.id = a.client_id', 'LEFT'),
		);
		$orderBy = array('a.ID desc, a.client_id asc');

		$table = $this->model_form->sv__filter('sv__job_request a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 							=> 'success',
			'message'							=> '',
			'table'								=> ($table)?$table:'No Data',
			'sv_job_request_filter_dept'		=> $this->input->post('sv_job_request_filter_dept'),
			'sv_job_request_filter_client_id'	=> $this->input->post('sv_job_request_filter_client_id'),
			'sv_job_request_filter_status'		=> $this->input->post('sv_job_request_filter_status'),
			'sv_job_request_filter_year'		=> $this->input->post('sv_job_request_filter_year'),
			'sv_job_request_filter_month'		=> $this->input->post('sv_job_request_filter_month'),

		);
		$this->session->set_userdata('sv__job_request',$data['table']);
		echo json_encode($data);
		return true;
	}

	function get_client_new_resignation_filter()
	{
		$this->load->model('hr/model_client_new_resignation');

		$department	= (!empty($this->input->post('sv_client_new_resignation_filter_dept')) && $this->input->post('sv_client_new_resignation_filter_dept') != -1)? $this->input->post('sv_client_new_resignation_filter_dept'):null;
		$client_id	= (!empty($this->input->post('sv_client_new_resignation_filter_client_id')) && $this->input->post('sv_client_new_resignation_filter_client_id') != -1)? $this->input->post('sv_client_new_resignation_filter_client_id'):null;
		$status		= (!empty($this->input->post('sv_client_new_resignation_filter_status')) && $this->input->post('sv_client_new_resignation_filter_status') != -1)? $this->input->post('sv_zoom_booking_filter_status'):null;
		$year		= (!empty($this->input->post('sv_client_new_resignation_filter_year')))? $this->input->post('sv_client_new_resignation_filter_year'):date('Y');
		$month		= (!empty($this->input->post('sv_client_new_resignation_filter_month')))? $this->input->post('sv_client_new_resignation_filter_month'):null;

		$where = array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_client_new_resignation_filter_dept',$department);
			$where['a.department_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_client_new_resignation_filter_dept','ALL');
		}

		if($client_id > 0) {
			$this->session->set_userdata('sv_client_new_resignation_filter_client_id',$client_id);
			$where['a.client_id'] = $client_id; 
		} else {
			$this->session->set_userdata('sv_client_new_resignation_filter_client_id','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_client_new_resignation_filter_year',$year);
			$where['YEAR(a.createdTS)'] = $year;
		} else {
			$this->session->set_userdata('sv_client_new_resignation_filter_year','ALL');
		}

		if(!empty($month) && $month > 0) {
			$this->session->set_userdata('sv_client_new_resignation_filter_month',$month);
			$where['MONTH(a.createdTS)'] = $month; 
		} else {
			$this->session->set_userdata('sv_client_new_resignation_filter_month','ALL');
		}

		$filter1 = array
		(
			'a.id'			=> $this->logged_user['id'],
			'j.module_id'	=> 2
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($module_access['access_lvl'] != 'admin')
		{
			$where['not_admin']	= '(a.requester = '.$this->logged_user['id'].' or a.createdBY = '.$this->logged_user['id'].')';
		}

		if(!empty($status))
		{
			$this->session->set_userdata('sv_client_new_resignation_filter_status',$status);
			$having['status_app'] = ''.$status.'';
			if($status == "Pending")
			{
				$or_having = array('status_app IS NULL');
			}
		} else {
			$this->session->set_userdata('sv_client_new_resignation_filter_status','ALL');
		}

		/* filter data */

		// $where['a.borrower_id'] = $userID;

		$select = array('a.*, a.department_id, b.name as dept_name, f.shortName as department_short_name, c.name as branchName, d.name as requester_name, d.email as requester_email, d.staff_id as requester_staff_id, e.name as created_name, e.email as created_email, f.id as created_department_id, f.name as created_dept_name, g.name as created_branch_name, i.ID as attachment_id, i.filename, i.file_location, h.name as client_name, (select status from sv__approval where table_name="sv__client_new_resignation" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app, (select reason from sv__approval where table_name="sv__client_new_resignation" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b', 'b.id = a.department_id','LEFT'),
			array('company__branch c', 'c.id = b.branch_id', 'LEFT'),
			array('user d', 'd.id = a.requester_id',''),
			array('user e', 'e.id = a.createdBY', 'LEFT'),
			array('company__department f', 'f.id = (select department_id from user__employment_detail where id = e.id)', 'LEFT'),
			array('company__branch g', 'g.id = f.branch_id', 'LEFT'),
			array('customer h', 'h.id = a.client_id', 'LEFT'),
			array('sv__client_new_resignation_attachment i', 'i.client_new_resignation_id = a.ID and i.is_delete = 0', 'LEFT'),
		);
		$orderBy = array('a.ID desc, a.client_id asc');

		$table = $this->model_form->sv__filter('sv__client_new_resignation a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);
		
		$data = array
		(
			'status' 										=> 'success',
			'message'										=> '',
			'table'											=> ($table)?$table:'No Data',
			'sv_client_new_resignation_filter_dept'			=> $this->input->post('sv_client_new_resignation_filter_dept'),
			'sv_client_new_resignation_filter_client_id'	=> $this->input->post('sv_client_new_resignation_filter_client_id'),
			'sv_client_new_resignation_filter_status'		=> $this->input->post('sv_client_new_resignation_filter_status'),
			'sv_client_new_resignation_filter_year'			=> $this->input->post('sv_client_new_resignation_filter_year'),
			'sv_client_new_resignation_filter_month'		=> $this->input->post('sv_client_new_resignation_filter_month'),

		);
		$this->session->set_userdata('sv__client_new_resignation',$data['table']);
		echo json_encode($data);
		return true;
	}

	public function get_complaint_filter()
	{
		$this->load->model('hr/model_complaint');

		$department	= (!empty($this->input->post('sv_complaint_filter_dept')) || $this->input->post('sv_complaint_filter_dept') != -1)? $this->input->post('sv_complaint_filter_dept'):null;
		$type_id	= (!empty($this->input->post('sv_complaint_filter_type')) || $this->input->post('sv_complaint_filter_type') != -1)? $this->input->post('sv_complaint_filter_type'):null;
		$client_id	= (!empty($this->input->post('sv_complaint_filter_client')) || $this->input->post('sv_complaint_filter_client') != -1)? $this->input->post('sv_complaint_filter_client'):null;
		$status		= (!empty($this->input->post('sv_complaint_filter_status')) || $this->input->post('sv_complaint_filter_status') != -1)? $this->input->post('sv_complaint_filter_status'):null;
		$year		= (!empty($this->input->post('sv_complaint_filter_year')))? $this->input->post('sv_complaint_filter_year'):null;

		$where 	= array(); 

		/* filter data */
		if(!empty($department) && $department > 0) {
			$this->session->set_userdata('sv_complaint_filter_dept',$department);
			$where['a.dept_id'] = $department; 
		} else {
			$this->session->set_userdata('sv_complaint_filter_dept','ALL');
		}

		if($type_id > -1) {
			$this->session->set_userdata('sv_complaint_filter_type',$type_id);
			$where['a.complaint_for'] = $type_id; 
		} else {
			$this->session->set_userdata('sv_complaint_filter_type','ALL');
		}

		if($client_id > 0) {
			$this->session->set_userdata('sv_complaint_filter_client',$client_id);
			$where['a.client_id'] = $client_id; 
		} else {
			$this->session->set_userdata('sv_complaint_filter_client','ALL');
		}

		if(!empty($status) && $status > 0) {
			$this->session->set_userdata('sv_complaint_filter_status',$status);
			$where['a.status'] = $status; 
		} else {
			$this->session->set_userdata('sv_complaint_filter_status','ALL');
		}

		if (!empty($year) && $year > 0) {
			$this->session->set_userdata('sv_complaint_filter_year',$year);
			$where['YEAR(a.date)'] = $year;
		} else {
			$this->session->set_userdata('sv_complaint_filter_year','ALL');
		}

		/* filter data */

		$select = array('a.*, b.name as department_name, c.name as branch_name, if(a.complaint_for = 1, d.name, concat(e.name, " | ", f.name)) as client_name, g.service, h.job, i.name as pic_name, j.name as reg_name');
		$where['a.is_delete'] = 0;
		$join = array
		(
			array('company__department b','b.id = a.dept_id','LEFT OUTER'),
	    	array('company__branch c','c.id = b.branch_id','LEFT OUTER'),
	    	array('customer d','d.id = a.client_id','LEFT OUTER'),
	    	array('company__department e','e.id = a.client_id','LEFT OUTER'),
	    	array('company__branch f','f.id = e.branch_id','LEFT OUTER'),
	    	array('_ref__services g','g.id = a.services_id','LEFT OUTER'),
	    	array('_ref__job h','h.id = a.job_id','LEFT OUTER'),
	    	array('user__employment_detail i','i.id = a.pic_id','LEFT OUTER'),
	    	array('user__employment_detail j','j.id = a.created_by','LEFT OUTER'),
		);
		$orderBy['a.ID'] = 'desc';

		$table = $this->model_form->sv__filter('sv__complaint a',$select, $where, $join, $orderBy, null, (!empty($having))?$having:null, (!empty($or_having))?$or_having:null);

		// debug($this->db->last_query());
		
		$data = array
		(
			'status' 						=> 'success',
			'message'						=> '',
			'table'							=> ($table)?$table:'No Data',
			'sv_complaint_filter_dept'		=> $this->input->post('sv_complaint_filter_dept'),
			'sv_complaint_filter_type'		=> $this->input->post('sv_complaint_filter_type'),
			'sv_complaint_filter_client'	=> $this->input->post('sv_complaint_filter_client'),
			'sv_complaint_filter_status'	=> $this->input->post('sv_complaint_filter_status'),
			'sv_complaint_filter_year'		=> $this->input->post('sv_complaint_filter_year'),

		);
		$this->session->set_userdata('sv__crm__complaint',$data['table']);
		echo json_encode($data);
		return true;
	}
}
?>