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

class Model_ot extends CI_Model {

	public function getTotalHourByOTId($otIDMain)
	{
		$this->db->select('SUM(totalHoursApplied) as totalHoursApplied');
	    $this->db->where('otID', $otIDMain);
	    $this->db->where('is_delete', 0);

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

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

	public function getMainOTID($otID)
	{
		$this->db->select('otID');
	    $this->db->where('ID', $otID);

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

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

	public function getOtDate($peopleID, $otDate)
	{
		$this->db->select('otDate, startTime, endTime');
	    $this->db->where('peopleID', $peopleID);
	    $this->db->where('otDate', $otDate);
	    $this->db->where('is_delete', 0);
	    $this->db->where('status !=', 2);

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

	    return ($query->num_rows() > 0)?$query->result():null;
	}
	
	public function getStaffListByDept($department_id)
	{
		$this->db->select('a.*, b.name');
		$this->db->join('user b','b.id = a.user_id','left outer');
	    $this->db->where('a.department_id', $department_id);
	    $this->db->where('a.is_delete', 0);

	    $query = $this->db->get('user__employment_detail a');   
	    if($query->num_rows() >= 1){
	      return $query->result();
	    }else{
	      return false;//$this->db->last_query();
	    }
	}

	function getOTSubList($user_id){

		$this->db->select('a.*,b.totalHoursApproved as totalHoursApprovedFinal,b.Pending, b.Approved, b.Rejected');
		$this->db->join('view_ot b','b.otID = a.ID','left outer');
		$this->db->where('a.peopleID',$user_id);
		$this->db->where('a.is_delete',0);

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

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

	}

	function getOTSubListByMonth($user_id,$month,$year){

		$this->db->select('*');
		$this->db->where('peopleID',$user_id);
		$this->db->where('month',$month);
		$this->db->where('year',$year);
		$this->db->where('is_delete',0);

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

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

	}

	function getBasicSalary($user_id){
/* //akmal 20190509. these select run slow due to user_employment_detail view. change to select direct from the respective table. 
		$this->db->select('basic_pay');
		$this->db->where('user_id',$user_id);
		$this->db->where('is_delete',0);
		$query = $this->db->get('user__employment_detail');

//select `ubp`.`amount` from (`user__basic_pay` `ubp` join `user__profiling` `up` on((`ubp`.`profiling_id` = `up`.`id`))) where ((`up`.`action_efective_date` <= curdate()) and (`up`.`user_id` = `a`.`id`) and (`up`.`is_delete` = 0)) order by `up`.`action_efective_date` desc,`up`.`id` desc limit 1 */
		$this->db->select('basic_pay');
		// 		->join('user__profiling up','ubp.profiling_id = up.id','LEFT JOIN',false)
		// 		->where('up.action_efective_date <=','curdate()',false);
		$this->db->where('user_id',$user_id);
		//$this->db->where('up.is_delete',0);
		$query = $this->db->get('user__employment_detail');
		
		return ($query->num_rows() > 0)? $query->result()[0] : null;
	}

	function getCOAllocation($user_id,$year){
		$this->db->select('*');
		$this->db->where('user_id',$user_id);
		$this->db->where('year',$year);
		$this->db->where('type_id',8); //conversion leave
		$this->db->where('is_delete',0);

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

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

	function updateCOAllocation($dataCO, $user_id,$year){

		$this->db->set('balance_entitled', 'balance_entitled +'.$dataCO, FALSE);
		$this->db->set('leave_balance', 'leave_balance +'.$dataCO, FALSE);
		$this->db->where('user_id',$user_id);
		$this->db->where('year',$year);
		$this->db->where('type_id',8); //conversion leave
		$query = $this->db->update('hr__leave_allocation');

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

	function getUsers($user_id)
	{
		$this->db->select('a.*,b.ic_no, g.name as department_name, e.position as position_name');
		$this->db->join('user b','b.id = a.user_id','left outer');
		$this->db->join('company__department g', 'a.department_id = g.id', 'left outer');
		$this->db->join('company__position e', 'a.position_id = e.id', 'left');
		$this->db->where('a.user_id',$user_id);

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

		if($query->num_rows() > 0) return ($user_id) ? $this->ObjToArray($query->result())[0] : $this->ObjToArray($query->result());
	}

	public function staff_list($dept_id)
	{
		$this->db->order_by('user_id');
		$query = $this->db->get_where('user__employment_detail', array('department_id'=>$dept_id));   
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;//$this->db->last_query();
		}
	}

	public function supervisor_department($supervisor_id)
	{
		$this->db->select('a.id, b.id as dept_id, b.name, c.id as branch_id, c.name as branch_name');
		$this->db->join('company__department b', 'a.department_id = b.id', 'left outer');
		$this->db->join('company__branch c', 'c.id = b.branch_id','left outer');
		$query = $this->db->get_where('user__employment_detail a', array('a.id'=>$supervisor_id));   
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;//$this->db->last_query();
		}
	}

	public function getOTDetailAll()
	{
		$this->db->select('a.*, b.name as staffName, concat(c.name,"[",d.name,"]") as deptName, e.name as clientName, i.name as status_by_name, j.name as svName, k.name as svDeptName');
		$this->db->join('user__employment_detail b', 'b.user_id = a.peopleID','left');
		$this->db->join('company__department c', 'c.id = b.department_id','left');
		$this->db->join('company__branch d', 'd.id = c.branch_id','left');
		$this->db->join('customer e', 'e.id = a.clientID','left');
		$this->db->join('user i','i.id = a.status_by','left');
		$this->db->join('user j','j.id = a.svID','left');
		$this->db->join('company__department k','k.id = a.svDeptID','left');
	    $this->db->where('a.is_delete', 0);

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

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

	public function getStatusByDetailID($otDetail_id)
	{
		$this->db->select('id, status, remarks');
	    $this->db->where('id', $otDetail_id);
	    $this->db->where('is_delete', 0);

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

	    if($query->num_rows() > 0) return ($otDetail_id) ? $this->ObjToArray($query->result())[0] : $this->ObjToArray($query->result());

	}

	function getDepartments($branch_id=null, $department_id=null)
	{
		$this->db->select('a.*, concat(a.name,"[",b.name,"]") as branch_name, c.name as hod_name');
		$this->db->join('company__branch b', 'b.id = a.branch_id');
		$this->db->join('user c', 'c.id = a.hod_id', 'left');

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

		if($department_id)$this->db->where('a.id', $department_id);
		if($branch_id)$this->db->where('a.branch_id', $branch_id);

		$this->db->order_by('name', 'asc');

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

	function getStaffName($staff_id)
	{
		$this->db->select('name');

		$this->db->where('user_id', $staff_id);

		$query = $this->db->get('user__employment_detail');
						  
		return ($query->num_rows() > 0)?$query->result():null;
	}

	function getClaimDetailAll()
	{
		$this->db->select('a.*, b.name as staffName, concat(c.name,"[",d.name,"]") as deptName, e.name as be_name');
		$this->db->join('user__employment_detail b', 'b.user_id = a.peopleID','left');
		$this->db->join('company__department c', 'c.id = b.department_id','left');
		$this->db->join('company__branch d', 'd.id = c.branch_id','left');
		$this->db->join('company__business_entity e', 'e.id = b.business_entity_id','left');
	    $this->db->where('a.is_delete', 0);

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

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

	function getBusinesses($company_id=null, $business_id=null)
	{
		$this->db->select('a.*, b.name as company_name');
		$this->db->join('company b', 'b.id = a.company_id');

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

		if($business_id)$this->db->where('a.id', $business_id);
		if($company_id)$this->db->where('a.company_id', $company_id);

		$this->db->order_by('name', 'asc');
		
		$query = $this->db->get('company__business_entity a');
		
		return ($query->num_rows() > 0)?$query->result():null;
	}

	function getclaimNotApproved($deptID,$year,$month)
	{
		$this->db->select('a.*, b.claim_status, b.paid_status');
		$this->db->join('hr__ot b', 'a.otID = b.ID');
		$this->db->where('a.deptID', $deptID);
		$this->db->where('a.year', $year);
		$this->db->where('a.month', $month);
		$this->db->where('b.is_delete', 0);
		$this->db->where('b.claim_status !=', 2);

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

	function getOTSupervised($user_id, $deptID)
	{
		$this->db->select('a.*,e.*, b.name as companyName, 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');
		$this->db->join('view_ot c','a.ID = c.otID','left');
		$this->db->join('company__business_entity b','b.id = c.deptID', 'left');
		$this->db->join('user d','d.id = c.peopleid','left');
		$this->db->join('user__employment_detail e', 'e.user_id = a.peopleID','left');
		$this->db->join('hr__ot__detail f', 'f.otID = a.ID', 'left');
		$this->db->where('f.svId', $user_id);
		$this->db->where('e.department_id !=', $deptID);
		$this->db->where('c.month', date('n'));
		$this->db->where('c.year', date('Y'));
		$this->db->where('f.is_delete', 0);
		$this->db->order_by('c.year', 'DESC');
		$this->db->order_by('c.month', 'DESC');
		$this->db->group_by('b.id');
		$this->db->group_by('c.peopleid');
		$this->db->group_by('c.year');
		$this->db->group_by('c.month'); 

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

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

	function getOTSummaryByDept($otID){
		$this->db->select('a.svDeptID, b.name as dept, c.name as branch, sum(a.totalHoursApplied) as totalHoursApplied,
							(select sum(totalHoursApplied) from hr__ot__detail where otID = a.otID and status = 1 and is_delete = 0 and svDeptID = a.svDeptID) as totalHoursApproved');
		$this->db->select('(select sum(totalHoursApplied) from hr__ot__detail where otID = a.otID and status = 1 and is_delete = 0 and svDeptID = a.svDeptID) as totalHoursApproved');
		$this->db->select('(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 1) and (otID = a.otID and svDeptID = a.svDeptID))) AS Normal');
		$this->db->select('(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 2) and (otID = a.otID and svDeptID = a.svDeptID))) AS Rest');
		$this->db->select('(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 3) and (otID = a.otID and svDeptID = a.svDeptID))) AS Public');
		$this->db->select('(select sum(if(((dayType = 1) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)),1,0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS NormalMeal');
		$this->db->select('(select sum(if(((dayType = 2) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)),1,0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS RestMeal');
		$this->db->select('(select sum(if(((dayType = 3) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)),1,0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS PublicMeal');
		$this->db->join('company__department b','b.ID = a.svDeptID','left outer');
		$this->db->join('company__branch c','c.ID = b.branch_id','left outer');
		$this->db->where('a.otID',$otID);
		$this->db->where('a.is_delete',0);
		$this->db->group_by('a.svDeptID');

		$query = $this->db->get('hr__ot__detail a');
//echo $this->db->last_query();
		return ($query->num_rows() > 0)?$this->ObjToArray($query->result()):null;
	}

	function getCountOTListSV($otID)
	{
		$this->db->select('*');
		$this->db->where('otID',$otID);
		$this->db->where('svDeptID !=', 0);

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

	function getPendingOTDetails($otID)
	{
		$this->db->select('*');
		$this->db->where('otID',$otID);
		$this->db->where('status', 0);
		$this->db->where('is_delete', 0);

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

	function getCrossJobDept($deptID,$month_submission,$year_submission)
	{
		if(!empty($deptID)){
			$filter_dept = 'AND a.svDeptID = "'.$deptID.'"';
		} else{
			$filter_dept = NULL;
		}

		if(!empty($month_submission)){
			$filter_month = 'AND d.month = "'.$month_submission.'"';
		} else{
			$filter_month = NULL;
		}

		if(!empty($year_submission)){
			$filter_year = 'AND d.year = "'.$year_submission.'"';
		} else{
			$filter_year = NULL;
		}
		
		$sql = "SELECT DISTINCT(a.svDeptID), f.name as job_deptName, g.name as job_branchName, d.year, d.month
				FROM hr__ot__detail a
				LEFT JOIN user b ON b.id = a.peopleID
				LEFT JOIN user__employment_detail c ON c.user_id = a.peopleID
				LEFT JOIN hr__ot d ON d.id = a.otID
				LEFT JOIN company__position e ON e.id = c.position_id
				LEFT JOIN company__department f ON f.id = a.svDeptID
				LEFT JOIN company__branch g ON g.id = f.branch_id
				LEFT JOIN company__department h ON h.id = c.department_id
				LEFT JOIN company__branch i ON i.id = h.branch_id
				WHERE a.crossDept = 1 
				AND a.is_delete = 0
				AND a.status = 1
				AND a.svDeptID != c.department_id
				".$filter_month." 
				".$filter_year."
				".$filter_dept."
				ORDER BY a.svDeptID, d.month, d.year ASC";

		$query = $this->db->query($sql);
		return (!empty($query))?$query->result():null;
	}

	function getSummaryCrossJOb($deptID,$month_submission,$year_submission)
	{
		if(!empty($deptID)){
			$filter_dept = 'AND a.svDeptID = "'.$deptID.'"';
		} else{
			$filter_dept = NULL;
		}

		if(!empty($month_submission)){
			$filter_month = 'AND d.month = "'.$month_submission.'"';
		} else{
			$filter_month = NULL;
		}

		if(!empty($year_submission)){
			$filter_year = 'AND d.year = "'.$year_submission.'"';
		} else{
			$filter_year = NULL;
		}

		$sql = "SELECT `a`.`svDeptID`, `f`.`name` as `job_deptName`, `g`.`name` as `job_branchName`, b. name as staffName, e.position, c.department_id, c.basic_pay, h.name as staff_deptName, i.name as staff_branchName, a.otID, a.peopleID, d.month, d.year, d.totalConvertToLeave,
				IF(d.totalConvertToLeave > 0, 'Yes','No') as convertAL,
				(select sum(totalHoursApplied) from hr__ot__detail where otID = a.otID and is_delete = 0 and svDeptID = a.svDeptID) as totalHoursApplied,
				(select sum(totalHoursApplied) from hr__ot__detail where otID = a.otID and status = 1 and is_delete = 0 and svDeptID = a.svDeptID) as totalHoursApproved, 
				(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 1) and (otID = a.otID and svDeptID = a.svDeptID))) AS Normal, 
				(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 2) and (otID = a.otID and svDeptID = a.svDeptID))) AS Rest, 
				(select sum(totalHoursApplied) from hr__ot__detail where ((status = 1) and (is_delete = 0) and (dayType = 3) and (otID = a.otID and svDeptID = a.svDeptID))) AS Public, 
				(select sum(if(((dayType = 1) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)), 1, 0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS NormalMeal, 
				(select sum(if(((dayType = 2) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)), 1, 0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS RestMeal, 
				(select sum(if(((dayType = 3) and (mealAllowance = 2) and (status = 1) and (is_delete = 0)), 1, 0)) from hr__ot__detail where (otID = a.otID and svDeptID = a.svDeptID)) AS PublicMeal
				FROM `hr__ot__detail` `a`
				LEFT JOIN user b ON b.id = a.peopleID
				LEFT JOIN user__employment_detail c ON c.user_id = a.peopleID 
				LEFT JOIN hr__ot d ON d.id = a.otID
				LEFT JOIN company__position e ON e.id = c.position_id 
				LEFT OUTER JOIN `company__department` `f` ON `f`.`ID` = `a`.`svDeptID`
				LEFT OUTER JOIN `company__branch` `g` ON `g`.`ID` = `f`.`branch_id`
				LEFT JOIN company__department h ON h.id = c.department_id 
				LEFT JOIN company__branch i ON i.id = h.branch_id 
				WHERE a.is_delete = 0
				AND a.crossDept = 1
				AND a.status = 1
				AND a.svDeptID != c.department_id
				".$filter_month." 
				".$filter_year."
				".$filter_dept."
				GROUP BY b.id
				ORDER BY a.svDeptID, d.month, d.year ASC";

		$query = $this->db->query($sql);
		return (!empty($query))?$query->result_array():null;
	}

	function get_prev_balanceCarryForward($user_id){

		$this->db->select('*');
		$this->db->where('peopleID',$user_id);
		// $this->db->where('claim_status !=', 0);
		$this->db->where('is_delete', 0);
		$this->db->order_by('ID DESC');
		$this->db->limit(1);
		// $this->db->limit(1,1);

		$query = $this->db->get('hr__ot');
		
		return ($query->num_rows() > 0)?$query->result():null;
	}

	function ObjToArray($item) { return json_decode(json_encode($item),true); }

}
?>