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

class Overtime 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('CRM/country');
		$this->load->model('CRM/general_model');
		$this->load->helper('jwt_helper');
	}

	function getMyOtLists()
	{

		$userID 	= (!empty($this->input->post('user_id')) && $this->input->post('user_id') != -1)? $this->input->post('user_id'):null;
		$year		= (!empty($this->input->post('filter_year')))? $this->input->post('filter_year'):null;
		$month		= (!empty($this->input->post('filter_month')) && $this->input->post('filter_month') != -1)? $this->input->post('filter_month'):null;

		$filter = array(); 

		if ($year >0) {
			$this->session->set_userdata('filter_year',$year);
			$filter['a.year'] = $year; 
		}else{
			$this->session->set_userdata('filter_year','ALL');
		}

		if ($month == null || $month == 0 || $month == -1) {
			$this->session->set_userdata('filter_month','ALL');
		}else{
			$filter['a.month'] = $month; 
			$this->session->set_userdata('filter_month',$month);
		}

		$filter['a.peopleID'] = $userID; 

		$select = array('a.deptid', 'a.department', 'a.year', 'a.month', 
						'a.totalHoursApplied', 'a.totalHoursApproved', 'a.otID','a.noofclaim',
						'a.pending as pending',
						'a.rejected as Rejected',
						'a.approved as Approved',
						'b.claim_status',
						'b.paid_status'
					);
		$join = array(array('hr__ot b','b.ID = a.otID','left'));
		$orderBy = array('a.year desc','a.month desc','a.department');
		$groupBy = array('a.deptid' , 'a.peopleID','a.year', 'a.month'); 								
		$table = $this->model_general->fetchAll('view_ot a',$select, $filter, $join, $orderBy, $groupBy);		
		
		$data = array
		(
			'status' 	=> 'success',
			'message'	=> '',
			'table'		=> $table,
			'user_id' 	=> $this->input->post('user_id'),
			'filter_year'		=> $this->input->post('filter_year'),

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

	function getStaffOtModerator(){
		$this->session->unset_userdata('filter_year');
		$this->session->unset_userdata('filter_month');
		$this->session->unset_userdata('filter_staff');

		
		$year		= (!empty($this->input->post('filter_year')))? $this->input->post('filter_year'):null;
		$month		= (!empty($this->input->post('filter_month')) && $this->input->post('filter_month') != -1)? $this->input->post('filter_month'):null;
		$staff_id 	= (!empty($this->input->post('filter_staff')) && $this->input->post('filter_staff') != -1)? $this->input->post('filter_staff'):null;

		$filter = array('a.is_delete' => 0); 
		$filter = array('c.deptID' => $_SESSION['user']['department_id']);

		if ($year == null || $year == 0 || $year == -1) {
			$this->session->set_userdata('filter_year','ALL');
		}else{
			$filter['c.year'] = $year; 
			$this->session->set_userdata('filter_year',$year);
		}

		if ($month == null || $month == 0 || $month == -1) {
			$this->session->set_userdata('filter_month','All');
		}else{
			$filter['c.month'] = $month; 
			$this->session->set_userdata('filter_month',$month);
		}

		if ($staff_id == null || $staff_id == 0 || $staff_id == -1) {
			$this->session->set_userdata('filter_staff','All');
		}else{
			$filter['c.peopleID'] = $staff_id; 
			$this->session->set_userdata('filter_staff',$staff_id);
		}
		
		$join = array(array('view_ot c','a.ID = c.otID','left'),
				array('company__business_entity b','b.id = c.deptID','left'),
				array('user d','d.id = c.peopleid','left'));
		
		$table = $this->model_general->fetchAll('hr__ot a', 
				array('a.*',' b.name as companyName', 'c.otID','c.year', 'c.month', 
						'c.totalHoursApplied', 'c.totalHoursApproved', 'c.otID','c.noOfclaim',
						'c.pending as Pending',
						'c.rejected as Rejected',
						'c.approved as Approved','d.name'), 
				$filter, 
				$join, 
				array('c.year' => 'desc','c.month' => 'desc','d.name' ),
				array('c.year','c.month','d.id'));

		$data = array
		(
			'status' 	=> 'success',
			'message'	=> '',
			'table'		=> $table,
			'filter_staff' 		=> $this->input->post('filterStaff'),
			'filter_year'		=> $this->input->post('filterYear'),
			'filter_month'		=> $this->input->post('filterMonth'),
			'sql' => $this->db->last_query(),
		);
		$this->session->set_userdata('view_ot',$data['table']);
		//echo $this->db->last_query();
		echo json_encode($data);
		return true;
	}

	function getStaffOtModeratorSV(){

		$this->session->unset_userdata('filter_year');
		$this->session->unset_userdata('filter_month');
		
		$year		= (!empty($this->input->post('filter_year')))? $this->input->post('filter_year'):null;
		$month		= (!empty($this->input->post('filter_month')) && $this->input->post('filter_month') != -1)? $this->input->post('filter_month'):null;
 
		$filter['f.svId'] = $_SESSION['user']['id'];
		$filter['e.department_id !='] = $_SESSION['user']['department_id'];

		if ($month == null || $month == 0 || $month == -1) {
			$this->session->set_userdata('filter_month','All');
		}else{
			$filter['c.month'] = $month; 
			$this->session->set_userdata('filter_month',$month);
		}

		if ($year == null || $year == 0 || $year == -1) {
			$this->session->set_userdata('filter_year','ALL');
		}else{
			$filter['c.year'] = $year; 
			$this->session->set_userdata('filter_year',$year);
		}

		$filter['f.is_delete'] = 0;
		
		$join = array(array('view_ot c','a.ID = c.otID','left'),
				array('company__business_entity b','b.id = c.deptID', 'left'),
				array('user d','d.id = c.peopleid','left'),
				array('user__employment_detail e', 'e.user_id = a.peopleID','left'),
				array('hr__ot__detail f', 'f.otID = a.ID', 'left'));
		
		$table = $this->model_general->fetchAll('hr__ot a', 
				array('a.*', 'e.*', ' b.name as companyName', 'c.otID','c.year', 'c.month', 
						'c.totalHoursApplied', 'c.totalHoursApproved', 'c.otID','c.noOfclaim',
						'c.pending as Pending',
						'c.rejected as Rejected',
						'c.approved as Approved','d.name', 'f.svID'), 
				$filter, 
				$join, 
				array('c.year' => 'desc','c.month' => 'desc'),
				array('b.id','c.peopleid','c.year','c.month'));

		$data = array
		(
			'status' 	=> 'success',
			'message'	=> '',
			'table'		=> $table,
			'filter_year'		=> $this->input->post('filterYearSV'),
			'filter_month'		=> $this->input->post('filterMonthSV'),
			'sql' => $this->db->last_query(),
		);
		$this->session->set_userdata('view_ot',$data['table']);
		
		echo json_encode($data);
		return true;
	}

	function getStaffOt(){
		$this->session->unset_userdata('filter_year');
		$this->session->unset_userdata('filter_month');
		$this->session->unset_userdata('filter_staff');

		
		$year		= (!empty($this->input->post('filter_year')))? $this->input->post('filter_year'):null;
		$month		= (!empty($this->input->post('filter_month')) && $this->input->post('filter_month') != -1)? $this->input->post('filter_month'):null;
		$staff_id 	= (!empty($this->input->post('filter_staff')) && $this->input->post('filter_staff') != -1)? $this->input->post('filter_staff'):null;
		$department_id = $this->input->post('filter_department');

		$filter = array('a.is_delete' => 0); 
		$filter = array('c.deptID' => $department_id);

		if ($year == null || $year == 0 || $year == -1) {
			$this->session->set_userdata('filter_year','ALL');
		}else{
			$filter['c.year'] = $year; 
			$this->session->set_userdata('filter_year',$year);
		}

		if ($month == null || $month == 0 || $month == -1) {
			$this->session->set_userdata('filter_month','All');
		}else{
			$filter['c.month'] = $month; 
			$this->session->set_userdata('filter_month',$month);
		}

		if ($staff_id == null || $staff_id == 0 || $staff_id == -1) {
			$this->session->set_userdata('filter_staff','All');
		}else{
			$filter['c.peopleID'] = $staff_id; 
			$this->session->set_userdata('filter_staff',$staff_id);
		}
		
		$join = array(array('view_ot c','a.ID = c.otID','left'),
				array('company__business_entity b','b.id = c.deptID','left'),
				array('user d','d.id = c.peopleid','left'));
		
		$table = $this->model_general->fetchAll('hr__ot a', 
				array('a.*',' b.name as companyName', 'c.otID','c.year', 'c.month','c.department', 
						'c.totalHoursApplied', 'c.totalHoursApproved', 'c.otID','c.noOfclaim',
						'c.pending as Pending',
						'c.rejected as Rejected',
						'c.approved as Approved','d.name'), 
				$filter, 
				$join, 
				array('c.year' => 'desc','c.month' => 'desc','d.name' ),
				array('c.year','c.month','d.id'));

		$data = array
		(
			'status' 	=> 'success',
			'message'	=> '',
			'table'		=> $table,
			'filter_staff' 		=> $this->input->post('filterStaff'),
			'filter_year'		=> $this->input->post('filterYear'),
			'filter_month'		=> $this->input->post('filterMonth'),
			'sql' => $this->db->last_query(),
		);
		$this->session->set_userdata('view_ot',$data['table']);
		//echo $this->db->last_query();
		echo json_encode($data);
		return true;
	}

	function getOtSummaryByDepartment()
	{
		$this->session->unset_userdata('filter_year');
		$this->session->unset_userdata('filter_month');
		$this->session->unset_userdata('filter_company');

		$userID 	= $this->input->post('user_id');
		$year		= (!empty($this->input->post('filter_year')))? $this->input->post('filter_year'):null;
		$month		= (!empty($this->input->post('filter_month')) && $this->input->post('filter_month') > 0)? $this->input->post('filter_month'):null;
		$dept_id 	= (!empty($this->input->post('filter_company')))? $this->input->post('filter_company'):null;

		$filter = array(); 
		if ($year == null || $year == 0 || $year == -1) {
			$this->session->set_userdata('filter_year','ALL');
		}else{
			$this->session->set_userdata('filter_year',$year);
			$filter['year'] = $year;
		}

		if ($month == null || $month == 0 || $month == -1) {
			$this->session->set_userdata('filter_month','ALL');
		}else{
			$this->session->set_userdata('filter_month',$month);
			$filter['month'] = $month;
		} 
		
		if ($dept_id === null || $dept_id == 0 || $dept_id == -1) {
			$this->session->set_userdata('filter_company','ALL');
		}else{
			$this->session->set_userdata('filter_company',$dept_id);
			$filter['deptID'] = $dept_id;
		}
		
		$select = array('deptid as deptidMain', 'department', 'year as yearMain', 'month as monthMain',  'sum(totalHoursApplied)  as totalHoursApplied', 'sum(totalHoursApproved)  as totalHoursApproved', 
//						'(select sum(totalHoursApplied) from view_ot where deptid = deptidMain and year = yearMain and month = monthMain) as totalHoursApplied', 
//						'(select sum(totalHoursApproved) from view_ot where deptid = deptidMain and year = yearMain and month = monthMain) as totalHoursApproved', 
						'otID','noofclaim','branch',
						'sum(if(pending>0,1,0)) as pending',
						'sum(if(rejected=noofclaim,1,0)) as rejected',
						'sum(if(approved > 0 and approved+rejected=noofclaim,1,0)) as approved')	;
		$orderBy = array('year desc','month desc','department');
		$groupBy = array('deptid' , 'year', 'month'); 		
						
		$table = $this->model_general->fetchAll('view_ot',$select, $filter,null,$orderBy,$groupBy);				
		$data = array
		(
			'status' 	=> 'success',
			'message'	=> '',
			'table'		=> $table,
			'user_id' 	=> $this->input->post('user_id'),
			'filter_year'		=> $year, 
			'filter_month'		=> $month, 
			'filter_company'	=> $dept_id, 
			'sql' => $this->db->last_query(),
			'filter' => $filter,
		);
		
		$this->session->set_userdata('view_ot',$data['table']);
		echo json_encode($data);
		//echo $this->db->last_query();
		return true;
	}
}
?>