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

class Leave_report extends MY_Controller
{
	function __construct()
	{
		parent::__construct();	
		$this->load->model('Admin/model_company');
		$this->load->model('CRM/model_crm');
		$this->load->model('CRM/country');
		$this->load->model('CRM/general_model');
		$this->load->model('hr/model_leave');

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

		if($this->logged_user)
		{
			$this->login->checkModuleAccess(4); //HR
			$this->logged_module = $this->session->userdata('module_data');
		}
		else
		{
			redirect(base_url()); 
		}	 
	}

	function leaveReport($year = null)
	{
		$year = ($year && $this->validateDate($year, 'Y')) ? $year : date('Y');

		$data['role']		 = $this->logged_module['role'];
		$data['page_setup']  = $this->template->pageSetup('HR', 'home', 'Leave Report');
		$data['years']		 = $this->model_leave->getEntitlementYears();
		$data['curr_year']   = $year;
		$data['company'] 	 = $this->model_company->getCompanies(1);
		$data['month']		 = $this->model_crm->getMonth();
		$data['yearList']    = $this->db->query('select distinct year from hr__ot')->result();
		$data['department']  = $this->model_company->getDepartments();
		$data['staff']		 = $this->model_user->getUsers();
		$data['leave_types'] = $this->model_leave->getLeaveType();
		$data['applications'] = $this->model_leave->getApplication(array('year(a.date_from) = ' => $year));

		//var_dump($data['leave_types']);

		$this->template->view('hr/leave/report/leave_report', $data);
	}

	public function fnLoadLeaveDetailList()
	{
		$this->load->model('CRM/general_model');

		$department_id 	= $this->input->post('department_id');
		$staff_id 		= $this->input->post('staff_id');
		$leaveType_id	= $this->input->post('leaveType_id');
		$status 		= $this->input->post('status');
		$year 			= $this->input->post('year');
		$is_delete 		= 0;

		if($department_id!=0){
			$filter['a.department_id'] = $department_id;
		}

		if($staff_id!=0){
			$filter['a.user_id'] = $staff_id;
		}

		if($leaveType_id!=0){
			$filter['a.type_id'] = $leaveType_id;
		}

		if($status!=-1){
			$filter['a.status'] = $status;
		}

		if($year!=0){
			$filter['YEAR(a.date_from)'] = $year;
		}

		if($is_delete==0){
			$filter['a.is_delete'] = 0;
		}

		$filter2 = false;
		$filter3 = false;
		$filter4 = false;
		$filter5 = false;
		$filter6 = false;

		$select = array('a.*,
						concat(b.name,"[",l.name,"]") as deptName,
						g.name as staffName,
						f.name as leave_type,
						concat(DATE_FORMAT(a.date_from, "%d/%m/%y")," - ", DATE_FORMAT(a.date_to, "%d/%m/%y"), " [",a.days_applied,"]") as date,
						DATE_FORMAT(a.created_ts, "%d/%m/%y") as date_applied,
						e.status as leave_status');

		$leaveDetailList = $this->general_model->get_byFilter2(
			'hr__leave_application a',
			$select,
			$filter,
			$filter2,
			$filter3,
			$filter4,
			$filter5,
			$filter6,
			array(
				array('company__department b', 'b.id = a.department_id','left'),
				array('company__branch l', 'l.id = b.branch_id','left'),
				array('hr__ref_leave_period c', 'c.id = a.period_from_id','left'),
				array('hr__ref_leave_period d', 'd.id = a.period_to_id','left'),
				array('hr__ref_leave_status e', 'e.id = a.status','left'),
				array('hr__leave_type f', 'f.id = a.type_id','left'),
				array('user g', 'g.id = a.user_id','left'),
				array('hr__leave_attachment h', 'h.application_id = a.id','left'),
				array('user__employment_detail i', 'a.user_id = i.user_id','left'),
				array('company__position j', 'i.position_id = j.id','left'),
				array('user k', 'k.id = a.absent_relief_id','left')
			),
			array(
				array('a.id'),
				'ASC'
			)
		);

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

		if ($leaveDetailList) {
	    	echo json_encode(array('status' => 'success', 'message' => $leaveDetailList, 'query' => $query));
		} else {
		    echo json_encode(array('status' => 'failed', 'message' => 'No Leave Details Found.', 'query' => $query));
		}
	}

	function printLeaveReport($department_id,$staff_id,$leaveType_id,$status,$year)
	{
		$this->load->model('hr/OT/model_ot');

		$date = date("d-m-Y");
		$is_delete = 0;

		if($department_id!=0){
			$filter['a.department_id'] = $department_id;
		}

		if($staff_id!=0){
			$filter['a.user_id'] = $staff_id;
		}

		if($leaveType_id!=0){
			$filter['a.type_id'] = $leaveType_id;
		}

		if($status!=-1){
			$filter['a.status'] = $status;
		}

		if($year!=0){
			$filter['YEAR(a.date_from)'] = $year;
		}

		if($is_delete==0){
			$filter['a.is_delete'] = 0;
		}

		$filter2 = false;
		$filter3 = false;
		$filter4 = false;
		$filter5 = false;
		$filter6 = false;

		$select = array('concat(b.name,"[",l.name,"]") as deptName,
						g.name as staffName,
						f.name as leave_type,
						concat(DATE_FORMAT(a.date_from, "%d/%m/%y")," - ", DATE_FORMAT(a.date_to, "%d/%m/%y"), " [",a.days_applied,"]") as date,
						DATE_FORMAT(a.created_ts, "%d/%m/%y") as date_applied,
						e.status as leave_status');

		$leaveDetailList = $this->general_model->get_byFilter2(
			'hr__leave_application a',
			$select,
			$filter,
			$filter2,
			$filter3,
			$filter4,
			$filter5,
			$filter6,
			array(
				array('company__department b', 'b.id = a.department_id','left'),
				array('company__branch l', 'l.id = b.branch_id','left'),
				array('hr__ref_leave_period c', 'c.id = a.period_from_id','left'),
				array('hr__ref_leave_period d', 'd.id = a.period_to_id','left'),
				array('hr__ref_leave_status e', 'e.id = a.status','left'),
				array('hr__leave_type f', 'f.id = a.type_id','left'),
				array('user g', 'g.id = a.user_id','left'),
				array('hr__leave_attachment h', 'h.application_id = a.id','left'),
				array('user__employment_detail i', 'a.user_id = i.user_id','left'),
				array('company__position j', 'i.position_id = j.id','left'),
				array('user k', 'k.id = a.absent_relief_id','left')
			),
			array(
				array('a.id'),
				'ASC'
			)
		);

		if($status ==1){
			$statusWord = "Pending";
		}else if($status ==2){
			$statusWord = "Approved";
		}else if($status ==3){
			$statusWord = "Rejected";
		}else if($status ==4){
			$statusWord = "Canceled";
		}else {
			$statusWord = "ALL";
		}

		if($leaveType_id !='ALL'){
			$data['leaveType'] = $this->model_leave->getLeaveType($leaveType_id);
			$leaveType 		   = $data['leaveType']['name'];
		}else{
			$leaveType 		   = 'ALL';
		}
		

		$body = array(
				'table' => array(
					'header' => array(
						'Department',
						'Staff Name',
						'Leave Type',
						'Date [Days]',
						'Date Applied',
						'Status'
					),
					'right-column' => array(9),
					'total-column' => array(),
					'body' => $leaveDetailList,
					'footer' => array()
				),
				'filter' => array(					
					'Department' 	=> ($department_id != 0) ? $this->model_ot->getDepartments(NULL, $department_id)[0]->branch_name: "ALL",
					'Staff Name' 	=> ($staff_id != 0) ? $this->model_ot->getStaffName($staff_id)[0]->name : 'ALL',
					'Leave Type' 	=> $leaveType,
					'Status' 		=> $statusWord,
					'Year'		    => ($year != 0) ? $year : 'ALL',
				)
			);

		$header = array();
		$header['title'] = 'LEAVE DETAILS REPORT AS AT ' . $date;

		$footerData = array();

		$this->load->view('hr/overtime/report/header.php', $header);
		$this->load->view('hr/overtime/report/printList.php', $body);
		$this->load->view('hr/overtime/report/footer.php');

	}

function detail($user_id =0){
	if($user_id == 0 || ($user_id >0 && $this->logged_module['role'] != 'admin') ) 
		$user_id = $this->session->userdata('user')['id'];
	
	$data['user_id'] = $user_id;
	$data['date'] = date("d-m-Y"); 
	$data['rs_staff']		 = $this->model_user->getUsers($user_id);
//var_dump($data['rs_staff']);
	$data['rs_entitlement'] = $this->db->query('select a.*,b.name as type from hr__leave_allocation a,hr__leave_type b where a.type_id = b.id and a.user_id ='.$user_id.' and a.is_delete = 0  order by year, type_id')
				->result_array();
//var_dump($data['rs_entitlement']); 	
		$data['rs_year'] = $this->db->query('select distinct year from hr__leave_allocation where user_id ='.$user_id.' and is_delete = 0 order by year')->result_array();
//var_dump($data['rs_year']);
		$this->load->view('hr/leave/report/detail_by_user',$data);
	
	}
/* nota pasal cuti. 
table hr__leave_allocation, 
1. bring forward = leave_balance from last year 
2. bring_forward_entitled = (leave_balance from last year > leave_entitled from last year)?leave_entitled from last  year bahagi 2:leave_balance from last year
3. if(bring_forward_entitled ==0) $leave_balance = $leave_entitled - $takenb4 - $takenafter; 
			elseif($takenb4 < $bring_forward_entitled) $leave_balance = $leave_entitled - $takenafter; 
			elseif($takenb4 >= $bring_forward_entitled) $leave_balance = $leave_entitled + $bring_forward_entitled - $takenb4 - $takenafter; 
			else $bal2 = '-100000000'; //invalid calculation 
			
*/
	function test($year=0,$user_id=0){
		if($user_id > 0) $this->db->where('user_id',$user_id);
		$rsAllocation = $this->db->where('year',$year)->where('type_id',1)->get('hr__leave_allocation')->result_array(); 
		foreach($rsAllocation as $rowAllocation){
			$rsSummary = $this->db->select_sum('last_year_entitled','last_year_entitled')
					->select_sum('last_year_balance','last_year_balance')
					->select_sum('last_year_brought_forward','last_year_brought_forward')
					->select_sum('bring_forward','bring_forward_from_last_year')
					->select_sum('leave_entitled','leave_entitled')
					->select_sum('leave_applied_before_april','leave_applied_b4_april')
					->select_sum('leave_applied_after_april','leave_applied_after_april')
					->select_sum('current_balance','current_balance')
					->where('year',$year)->where('user_id',$rowAllocation['user_id'])
					->where_in('type_id',array(1,2))
					->get('view_leave_summary')->result_array();
					
			$bf = $rsSummary['0']['bring_forward_from_last_year'];
			$entt = $rsSummary['0']['leave_entitled'];
			$takenb4 = $rsSummary['0']['leave_applied_b4_april'];
			$takenafter = $rsSummary['0']['leave_applied_after_april'];
			$bal = $rsSummary['0']['current_balance'];
//			$cf = $rsSummary['0']['current_balance'];
//			$cf2 = ($bal > $entt/2)?$entt/2:$bal;
			
			$bal2=0;
			if($bf==0) $bal2 = $entt - $takenb4 - $takenafter; 
			elseif($takenb4 < $bf) $bal2 = $entt - $takenafter; 
			elseif($takenb4 >= $bf) $bal2 = $entt + $bf - $takenb4 - $takenafter; 
			else $bal2 = '-100000000';
			
			if($bal <> $bal2 ) { //|| $cf <> $cf2) {
				echo '<br>';//.$rowAllocation['user_id'].' -> '.$bal.'/'.$bal2.'  : '.$cf.'/'.$cf2; 
				var_dump($rowAllocation); 
				var_dump($rsSummary); 
//				echo '<br>'.$cf2.'/'.$bal2;
				echo '<br>'.$bal2 .'='. $entt .'+'. $bf .'-'. $takenb4 .'-'. $takenafter; 
				/* if($bal2 > -20) { 
					$this->db->where('id',$rowAllocation['id'])->update(' hr__leave_allocation',array('leave_balance'=>$bal2));
					echo $this->db->last_query(); //die();
				} */
			}
		}			
	}
	function test2($year=0){ //to test leave_bf <> previous year balance 
	$rs = $this->db->query(' select a.id, a.year, a.user_id, a.type_id, a.leave_entitled, a.bring_forward, a.bring_forward_entitled, a.leave_taken, a.leave_balance, b.year as b_year,b.leave_entitled as b_entt, b.bring_forward as b_bf, b.bring_forward_entitled as b_bf_e, b.leave_taken as b_taken, b.leave_balance as b_bal, if(b.leave_balance > b.leave_entitled/2,b.leave_entitled/2,b.leave_balance) as cf from hr__leave_allocation a, hr__leave_allocation b  where a.type_id = b.type_id and b.year = a.year - 1 and  a.user_id = b.user_id  and a.type_id = 1 and b.leave_balance <> 0 and a.year = '.$year.' and (b.leave_balance <> a.bring_forward || a.bring_forward <> a.bring_forward_entitled )')->result_array();
	var_dump($rs);
/*	foreach($rs as $row){
		$this->db->where('id',$row['id'])->update(' hr__leave_allocation',array('bring_forward'=>$row['b_bal'],'bring_forward_entitled'=>$row['cf']));
		echo $this->db->last_query(); //die();
		} */
	}

}
