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

class Payslip_model extends CI_Model{
	
	private $hr_payslip 						= 'hr__payslip'; 
	private $hr_payslip_deduction 				= 'hr__payslip_deduction';  
	private $hr__payslip_employer_contribution	= 'hr__payslip_employer_contribution';
	private $hr_payslip_pay 					= 'hr__payslip_pay';
	private $hr_payslip_upload 					= 'hr__payslip_upload';
	private $hr_ref 							= 'hr__ref';
	
	public function company_all(){
		$this->db->select('a.*,b.address,b.postcode,b.city,c.state,d.country');
		$this->db->where('a.is_delete',0);
		$this->db->join('company__branch b','b.company_id = a.company_id');
		$this->db->join('_ref__state c','c.id = b.state_id');
		$this->db->join('_ref__country d','d.id = b.country_id');
		$this->db->group_by('a.id');
		$query = $this->db->get('company__business_entity a');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function view(){
		$query = $this->db->get('hr__ref');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getHrRef(){
		$query = $this->db->get('hr__ref');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function hr_payslip($staff_id){
		$this->db->select('hr__payslip.id, company__business_entity.roc_no, company__business_entity.name, hr__payslip.month, hr__payslip.year, hr__payslip.employeeName, hr__payslip.department'); 
		$this->db->join('company__business_entity', 'company__business_entity.id = hr__payslip.companyID');
		$this->db->where("employeeID", $staff_id);
		$query = $this->db->get('hr__payslip');
			if($query->num_rows() >= 1){
				return $query->result();
			}else{
				return FALSE;
			} 

	}

	public function print_list2($staff_id){
		$this->db->select('hr__payslip.employeeID, hr__payslip.department, hr__payslip.month, hr__payslip.year, hr__payslip.employeeIC, hr__payslip.position, hr__payslip.employeeName, hr__payslip_pay.Basic_Pay, hr__payslip_pay.Other_Allowance, hr__payslip_pay.Phone_Allowance');
		$this->db->join('hr__payslip', 'hr_payslip.id = hr__payslip_pay.paySlipID');
		$this->db->where("employeeID", $staff_id);
		$query = $this->db->get('hr__payslip');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function saveBulkPayslip($data)
	{
		$ci = get_instance();
		$ci->load->helper('stringformat_helper');
		
		$employeeId = array();
		$employeeIc = array();

		foreach ($data['tableData'] as $staff) {
			array_push($employeeId,strtoupper(str_replace(' ', '', $staff->Employee_ID)));
			array_push($employeeIc,strtoupper(rmvSpecialCharacter($staff->Employee_IC)));
		}	

		// $result = $this->getUploadPayslipByEmployeeId($employeeId,$data);
		$result = $this->getPayslipsByIC($employeeIc,$data);
		if($result != false){
			return $result;
		}
		
		//insert a new data
		$payslipID = $this->newUploadPayslip($data);
		if($payslipID){
			$this->newBatchPayslip($data['tableData'],$payslipID,$data['salaryWage'][1],$data['salaryWage'][0],$data['companyId'],$data['companyData']);
			
		}else{
			return FALSE;
		}
		
		return TRUE;	
	}

	public function newUploadPayslip($data)
	{

		$total_record = sizeof($data['tableData']);
		$totalNetPay = 0.00;
		foreach ($data['tableData'] as $staff) {
			$totalNetPay += floatval($staff->Nett_Pay);
		}

		$newRecord = array(
		        'companyId' 	=> $data['companyId'],
		        'total_record' 	=> $total_record,
		        'total_netpay' 	=> $totalNetPay,
		        'year' 			=> intval($data['salaryWage'][1]),
		        'month'			=> intval($data['salaryWage'][0]),
		        'is_delete' 	=> 0
		);
			
		$this->db->insert($this->hr_payslip_upload, $newRecord);

		return $this->db->insert_id();
	}

	public function newBatchPayslip($data,$uploadId,$year,$month,$companyId,$companyData) 
	{
		//die(debug($data));
		$ci = get_instance();
		$ci->load->helper('stringformat_helper');
		$ci->load->model('Admin/model_company');

		$business_entity = $ci->model_company->getBusinesses(null, $companyId);

		$reference = $this->getHrRef();

		//array preparation for batch saving...
		$payslipPayBatch = array();
		$payslipEmployerContBatch = array();
		$payslipDeductionBatch = array();


		foreach ($data as $staff) 
		{
			//die(debug($staff));

			$tempBatch 		= array();
			
			$paylipData = array(
			        'uploadID' 			=> $uploadId,
			        'year' 				=> intval($year),
			        'month' 			=> intval($month),
			        'companyId' 		=> intval($companyId),
			        'status' 			=> 1,
			        'employeeID' 		=> strtoupper(str_replace(' ', '', $staff->Employee_ID)),
			        'employeeIC' 		=> rmvSpecialCharacter($staff->Employee_IC),
			        'employeeName' 		=> $staff->Employee_Name,
			        'position' 			=> $staff->Position,
			        'department' 		=> $staff->Department,
			        'grossPay' 			=> $staff->Gross_Pay,
			        'totalDeduction' 	=> $staff->Total_Deduction,
			        'netPay' 			=> $staff->Nett_Pay,
			        'company_name'		=> (!empty($companyData['name']))?$companyData['name']:'',
			        'company_address'	=> '555, Jalan Samudra Utara 1, Taman Samudra Utara',
			        'company_postcode'	=> '68100',
			        'company_city'		=> 'Batu Caves',
			        'company_state'		=> 'Selangor',
			        'company_country'	=> 'Malaysia'
			);

			$this->db->insert($this->hr_payslip, $paylipData);
			$payslipID = $this->db->insert_id();

			//hr__payslip_pay 
		
			if(intval($staff->Basic_Pay) != 0){
				$tempBatch['amount'] = floatval($staff->Basic_Pay);
				foreach ($reference as $ref) {
					if($ref->colName == 'Basic_Pay'){
				$tempBatch['payID'] = $ref->id;
				$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

			array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Fixed_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Fixed_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Fixed_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			// if(intval($staff->Other_Allowance) != 0){
			// 	$tempBatch['amount'] = floatval($staff->Other_Allowance);
			// 	foreach ($reference as $ref) {
			// 		if($ref->colName == 'Other_Allowance'){
			// 			$tempBatch['payID'] = $ref->id;
			// 			$tempBatch['description'] = $ref->colName;
			// 		}
			// 	}
			// 	$tempBatch['payslipID'] = $payslipID;

			// 	array_push($payslipPayBatch, $tempBatch);
			// }

			if(intval($staff->HOD_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->HOD_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'HOD_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Branch_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Branch_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Branch_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}
			
			if(intval($staff->Phone_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Phone_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Phone_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Parking_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Parking_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Parking_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Professional_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Professional_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Professional_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Travelling_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Travelling_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Travelling_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Acting_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Acting_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Acting_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}
			
			if(intval($staff->License_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->License_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'License_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Vehicle_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Vehicle_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Vehicle_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Special_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Special_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Special_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}

			if(intval($staff->Fuel_Allowance) != 0){
				$tempBatch['amount'] = floatval($staff->Fuel_Allowance);
				foreach ($reference as $ref) {
					if($ref->colName == 'Fuel_Allowance'){
						$tempBatch['payID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipPayBatch, $tempBatch);
			}


			// hr__payslip_employer_contribution
			//reseting tempBatch
			$tempBatch 		= array();

			if(intval($staff->Employer_EPF) != 0){
				$tempBatch['amount'] = floatval($staff->Employer_EPF);
				foreach ($reference as $ref) {
					if($ref->colName == 'Employer_EPF'){
						$tempBatch['contributionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipEmployerContBatch, $tempBatch);
			}

			if(intval($staff->Employer_SOCSO) != 0){
				$tempBatch['amount'] = floatval($staff->Employer_SOCSO);
				foreach ($reference as $ref) {
					if($ref->colName == 'Employer_SOSCO'){
						$tempBatch['contributionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipEmployerContBatch, $tempBatch);
			}

			if(intval($staff->Employer_SIP) != 0){
				$tempBatch['amount'] = floatval($staff->Employer_SIP);
				foreach ($reference as $ref) {
					if($ref->colName == 'Employer_SIP'){
						$tempBatch['contributionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipEmployerContBatch, $tempBatch);
			}

			// //hr_payslip_deduction
			
			//resetting temporary array
			$tempBatch 		= array();

			if(intval($staff->EMP_EPF_Contribution) != 0){
				$tempBatch['amount'] = floatval($staff->EMP_EPF_Contribution);
				foreach ($reference as $ref) {
					if($ref->colName == 'EMP_EPF_Contribution'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->EMP_SOCSO_Contribution) != 0){
				$tempBatch['amount'] = floatval($staff->EMP_SOCSO_Contribution);
				foreach ($reference as $ref) {
					if($ref->colName == 'EMP_SOCSO_Contribution'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}


			if(intval($staff->EMP_SIP_Contribution) != 0){
				$tempBatch['amount'] = floatval($staff->EMP_SIP_Contribution);
				foreach ($reference as $ref) {
					if($ref->colName == 'EMP_SIP_Contribution'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Zakat_Deduction) != 0){
				$tempBatch['amount'] = floatval($staff->Zakat_Deduction);
				foreach ($reference as $ref) {
					if($ref->colName == 'Zakat_Deduction'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->PCB_Deduction) != 0){
				$tempBatch['amount'] = floatval($staff->PCB_Deduction);
				foreach ($reference as $ref) {
					if($ref->colName == 'PCB_Deduction'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Loan) != 0){
				$tempBatch['amount'] = floatval($staff->Loan);
				foreach ($reference as $ref) {
					if($ref->colName == 'Loan/Advance_Deduction'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Wakaf) != 0){
				$tempBatch['amount'] = floatval($staff->Wakaf);
				foreach ($reference as $ref) {
					if($ref->colName == 'Wakaf'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->YaPEIM) != 0){
				$tempBatch['amount'] = floatval($staff->YaPEIM);
				foreach ($reference as $ref) {
					if($ref->colName == 'YaPEIM'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Club_Fee) != 0){
				$tempBatch['amount'] = floatval($staff->Club_Fee);
				foreach ($reference as $ref) {
					if($ref->colName == 'Club_Fee'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Ptptn) != 0){
				$tempBatch['amount'] = floatval($staff->Ptptn);
				foreach ($reference as $ref) {
					if($ref->colName == 'Ptptn'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->Tabung_Haji) != 0){
				$tempBatch['amount'] = floatval($staff->Tabung_Haji);
				foreach ($reference as $ref) {
					if($ref->colName == 'Tabung_Haji'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}

			if(intval($staff->CP38) != 0)
			{
				$tempBatch['amount'] = floatval($staff->CP38);
				foreach ($reference as $ref) {
					if($ref->colName == 'CP38'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;
				array_push($payslipDeductionBatch, $tempBatch);
			}


			if(intval($staff->Other_Deduction) != 0){
				$tempBatch['amount'] = floatval($staff->Other_Deduction);
				foreach ($reference as $ref) {
					if($ref->colName == 'Other_Deduction'){
						$tempBatch['deductionID'] = $ref->id;
						$tempBatch['description'] = $ref->colName;
					}
				}
				$tempBatch['payslipID'] = $payslipID;

				array_push($payslipDeductionBatch, $tempBatch);
			}
			
		}
		
		// debug($payslipPayBatch);
		// debug($payslipEmployerContBatch);
		// debug($payslipDeductionBatch);
		// die();

		$this->db->insert_batch($this->hr_payslip_pay,$payslipPayBatch);
		if(!empty($payslipEmployerContBatch))
		{
			$this->db->insert_batch($this->hr__payslip_employer_contribution,$payslipEmployerContBatch);
		}

		if(!empty($payslipDeductionBatch))
		{
			$this->db->insert_batch($this->hr_payslip_deduction,$payslipDeductionBatch);
		}

	}

	public function getUploadPayslipByEmployeeId($staff,$data)
	{
		
		$where = array(
			'a.month' => intval($data['salaryWage'][0]),
			'a.year'	=> intval($data['salaryWage'][1]),
			'b.is_delete' => 0
		);

		$this->db->where($where);
		$this->db->where_in('a.employeeID',$staff);
		$this->db->from($this->hr_payslip.' a');
		$this->db->join($this->hr_payslip_upload.' b','b.id = a.uploadID');
		$query = $this->db->get();

		if($query->num_rows() >= 1){
			return $query->result();
		}

		return FALSE;

	}

	public function getPayslipsByIC($staff,$data)
	{
		$ci = get_instance();
		$ci->load->helper('stringformat_helper');

		$where = array(
			'a.month' => intval($data['salaryWage'][0]),
			'a.year'	=> intval($data['salaryWage'][1]),
			'b.is_delete' => 0
		);

		$this->db->where($where);
		$this->db->where_in('a.employeeIC',rmvSpecialCharacter($staff));
		$this->db->from($this->hr_payslip.' a');
		$this->db->join($this->hr_payslip_upload.' b','b.id = a.uploadID');
		$query = $this->db->get();

		if($query->num_rows() >= 1){
			return $query->result();
		}

		return FALSE;
	}

	public function getUploadPayslipByCompanyWage($data)
	{
		$CI =& get_instance();
		$where = array(
			'month' => intval($data['salaryWage'][0]),
			'year'	=> intval($data['salaryWage'][1])
			);
		$query = $this->db->get_where($this->hr_payslip_upload,$where);

		if($query->num_rows() >= 1){
			return $query->result();
		}
			return FALSE;
	}

	public function getUploadedPayslip($companyId = null, $month = null,$year = null)
	{
		$now = new \DateTime('now');
		if($month == null){
			$month = $now->format('m');
		}
		if($year == null){
			$year = $now->format('Y');
		}

		$this->db->select('a.id,b.roc_no,b.name,a.year,a.month,a.total_record,a.total_netpay,a.uploadedTS');
		$this->db->from($this->hr_payslip_upload.' a');
		$this->db->order_by('a.uploadedTS desc');
		$this->db->join('company__business_entity b','b.id = a.companyID');
		$this->db->where('a.is_delete',0);
		if ($month != -1) {
		$this->db->where('a.month',intval($month));
		}
		if ($year != -1) {
		$this->db->where('a.year',intval($year));
		}
		if($companyId != null && $companyId != '-1'){
			$this->db->where('b.id',$companyId);
		}
		$query = $this->db->get();

		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getPayslip($uploadId)
	{
		$where = array(
			'uploadID' => $uploadId
			);

		$this->db->select('a.id,b.roc_no, b.name,a.year, a.month, a.employeeID, a.employeeName, a.position, a.department, a.netPay, a.totalDeduction, a.grossPay');
		$this->db->from($this->hr_payslip.' a');
		$this->db->join('company__business_entity b','b.id = a.companyID');
		$this->db->where($where);
		$query = $this->db->get();

		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getPayslipDetail($payslipId,$empIc = null)
	{
		if($empIc == null){
			$where = array(
				'a.id' => $payslipId
				);
		}else{

			$where = array(
				'a.id' => $payslipId,
				'a.employeeIC' => strtoupper(rmvSpecialCharacter($empIc))
				);
		}
		

		$this->db->select(
			'b.roc_no, 
			b.name,
			a.year, 
			a.month, 
			a.employeeID,
			a.employeeIC, 
			a.employeeName, 
			a.position, 
			a.department, 
			a.netPay, 
			a.totalDeduction, 
			a.grossPay,
			a.company_name,
			a.company_address,
			a.company_postcode,
			a.company_city,
			a.company_state,
			a.company_country,
			c.address,
			c.postcode,
			c.city,
			d.state as state,
			e.country as country
			');

		// $this->db->from($this->hr_payslip.' a');
		// $this->db->join('company__business_entity b','b.id = a.companyID');
		// $this->db->join('company__branch c','b.company_id = c.company_id');

		$this->db->from('company__branch c');
		$this->db->join('company__business_entity b','b.company_id = c.company_id');
		$this->db->join($this->hr_payslip.' a','b.id = a.companyID');
		$this->db->join('_ref__state d', 'd.id = c.state_id');
		$this->db->join('_ref__country e','e.id = c.country_id');
		$this->db->where($where);
		$this->db->group_by('a.employeeIC');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getAllowance($payslipId)
	{
		$where = array(
			'a.paySlipID' => $payslipId	
		);

		$this->db->select('b.description, a.amount');
		$this->db->from($this->hr_payslip_pay.' a');
		$this->db->join($this->hr_ref.' b','b.id = a.payID');
		$this->db->where($where);
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getDeduction($payslipId)
	{
		$where = array(
			'a.paySlipID' => $payslipId	
		);

		$this->db->select('b.description, a.amount');
		$this->db->from($this->hr_payslip_deduction.' a');
		$this->db->join($this->hr_ref.' b','b.id = a.deductionID');
		$this->db->where($where);
		$this->db->where('a.deductionID NOT IN (15,16,17)');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getDeductionEPF($payslipId)
	{

		$query = $this->db->query("SELECT a.description, ifnull((SELECT amount from ".$this->hr_payslip_deduction." where paySlipID = ".$payslipId." and deductionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (15)
							UNION 
							SELECT a.description, ifnull((SELECT amount from ".$this->hr__payslip_employer_contribution." where paySlipID = ".$payslipId." and contributionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (27)");
		
		// echo $this->db->last_query();

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

	public function getDeductionSOCSO($payslipId)
	{

		$query = $this->db->query("SELECT a.description, ifnull((SELECT amount from ".$this->hr_payslip_deduction." where paySlipID = ".$payslipId." and deductionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (16)
							UNION 
							SELECT a.description, ifnull((SELECT amount from ".$this->hr__payslip_employer_contribution." where paySlipID = ".$payslipId." and contributionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (28)");
		
		// echo $this->db->last_query();

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

	public function getDeductionSIP($payslipId)
	{

		$query = $this->db->query("SELECT a.description, ifnull((SELECT amount from ".$this->hr_payslip_deduction." where paySlipID = ".$payslipId." and deductionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (17)
							UNION 
							SELECT a.description, ifnull((SELECT amount from ".$this->hr__payslip_employer_contribution." where paySlipID = ".$payslipId." and contributionID = a.id),0) as amount
							FROM ".$this->hr_ref." a 
							WHERE a.ID IN (29)");
		
		// echo $this->db->last_query();

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

	public function getEmployerContribution($payslipId)
	{
		$where = array(
			'a.paySlipID' => $payslipId	
		);

		$this->db->select('b.description, a.amount');
		$this->db->from($this->hr__payslip_employer_contribution.' a');
		$this->db->join($this->hr_ref.' b','b.id = a.contributionID');
		$this->db->where($where);
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function getEmployeePayslips($empic)
	{
		$where = array(
				'a.employeeIC' => $empic,
				'c.is_delete' => 0,
				'a.year >=' => 2020, //*for isinar 2021
		);
		$this->db->select('a.id, a.month, a.year, b.roc_no, b.name');
		$this->db->from($this->hr_payslip.' a');
		$this->db->where($where);
		$this->db->join('company__business_entity b','b.id = a.companyID');
		$this->db->join($this->hr_payslip_upload.' c','c.id = a.uploadID');
		$this->db->order_by('c.year desc, c.month desc');
		// $this->db->limit(6); *for isinar 2021
		$query = $this->db->get();
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function isDelPSlipUpload($uploadId)
	{
		$this->db->set('is_delete', 1);
		$this->db->where('id',$uploadId);
		$this->db->update($this->hr_payslip_upload); 

		if($this->db->affected_rows() >= 1){
			return TRUE;
		} 

		return FALSE;
	}

	public function countYearPayslip()
	{	
		$now = new \DateTime('now');
		$year = $now->format('Y');
		
		$where = array(
			'a.is_delete' => 0,
			'a.year'	=> $year,
		);
		$this->db->select('id');
		$this->db->from($this->hr_payslip_upload.' a');
		$this->db->where($where);
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function countMonthPayslip()
	{	
		$now = new \DateTime('now');
		$month = intval($now->format('m'));
		$year = $now->format('Y');
		
		$where = array(
			'a.is_delete' 	=> 0,
			'a.month'		=> $month,
			'a.year'		=> $year
		);
		$this->db->select('id');
		$this->db->from($this->hr_payslip_upload.' a');
		$this->db->where($where);
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function yearNetPay()
	{
		$now = new \DateTime('now');
		$year = $now->format('Y');
		
		$where = array(
			'a.is_delete' => 0,
			'a.year'	=> $year,
		);
		$this->db->select('total_netpay');
		$this->db->from($this->hr_payslip_upload.' a');
		$this->db->where($where);
		$query = $this->db->get();

		$total = 0;	
		foreach ($query->result() as $record) {
			$total += $record->total_netpay;
		}

		return $total;
	}

	public function monthNetPay()
	{	
		$now = new \DateTime('now');
		$month = intval($now->format('m'));
		$year = $now->format('Y');
		
		$where = array(
			'a.is_delete' 	=> 0,
			'a.month'		=> $month,
			'a.year'		=> $year
		);
		$this->db->select('total_netpay');
		$this->db->from($this->hr_payslip_upload.' a');
		$this->db->where($where);
		$query = $this->db->get();
		
		$total = 0;	
		foreach ($query->result() as $record) {
			$total += $record->total_netpay;
		}

		return $total;
	}

	public function getUnknownUsers($employeeIc)
	{
		$ci = get_instance();
		$ci->load->helper('stringformat_helper');

		$unknown  = array();
		foreach ($employeeIc as $ic) {
			$where = array(
				'ic_no' => rmvSpecialCharacter($ic)
			);
			$this->db->where($where);
			$query = $this->db->get('user');

			if($query->num_rows() <= 0){
				array_push($unknown, $ic);
			}
		}

		return array_filter($unknown);
	}

	public function updatePayslip($input,$id = null, $company_id = null)
	{
		$this->db->set($input);
		if($id) $this->db->where('id',$id);
		if($company_id) $this->db->where('companyID',$company_id);
		$this->db->update($this->hr_payslip);

		return $this->db->affected_rows();
	}
	
	public function getPayslipData($payslipId)
	{
		$this->db->select('a.*');
		$this->db->where(array(
			'a.id' => $payslipId
		));
		$query = $this->db->get($this->hr_payslip.' a');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	// public function getEmails($data)
	// {
	// 	$emails = array();

	// 	//loop all the content in the table
	// 	foreach ($data as $key => $staff) {
	// 		$this->db->select('email');
	// 		$this->db->where('ic_no',$staff->Employee_IC);
	// 		if ($query = $this->db->get('user')) {
	// 			array_push($emails, $query->result());
	// 		}	
	// 	}

	// 	return $emails;

	// }
}