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

class Model_claim extends CI_Model 
{

	public function getUserClaims($year = null, $month = null,$user_id)
	{
		$staff_id = $user_id;

		$this->db->select('a.*,
			SUM(b.amount_claim) as dental_claim,
			SUM(c.amount_approved) as dental_appprove,
			SUM(d.amount_claim) as hospitalize_claim,
			SUM(e.amount_approved) as hospitalize_approve,
			SUM(f.amount_claim) as meals_claim,
			SUM(g.amount_approved) as meals_approved,
			SUM(h.amount_claim) as medical_screening_claim,
			SUM(i.amount_approved) as medical_screening_approved,
			SUM(j.amount_claim) as mileage_claim,
			SUM(k.amount_approve) as mileage_approved,
			SUM(l.amount_claim) as other_claim,
			SUM(m.amount_approved) as other_approved,
			SUM(n.amount_claim) as outpatient_claim,
			SUM(o.amount_approved) as outpatient_approved,
			SUM(p.amount_approve) as travelling_approved,
			');

		if($staff_id > 0) $this->db->where('user_id',$staff_id);
		if($year) $this->db->where('year',$year);
		if($month) $this->db->where('month',$month);

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

			//dental
		$this->db->join('hr__claim_dental b','b.claim_id = a.id','left');
		$this->db->join('hr__claim_dental c','c.claim_id = a.id','left');
		$this->db->group_by('b.claim_id');
		$this->db->group_by('c.claim_id');

			//hospitalization
		$this->db->join('hr__claim_hospitalization d','d.claim_id = a.id','left');
		$this->db->join('hr__claim_hospitalization e','e.claim_id = a.id','left');
		$this->db->group_by('d.claim_id');
		$this->db->group_by('e.claim_id');

			//meals
		$this->db->join('hr__claim_meals f','f.claim_id = a.id','left');
		$this->db->join('hr__claim_meals g','g.claim_id = a.id','left');
		$this->db->group_by('f.claim_id');
		$this->db->group_by('g.claim_id');

			//medical screening 
		$this->db->join('hr__claim_medical_screening h','h.claim_id = a.id','left');
		$this->db->join('hr__claim_medical_screening i','i.claim_id = a.id','left');
		$this->db->group_by('h.claim_id');
		$this->db->group_by('i.claim_id');

			//mileage 
		$this->db->join('hr__claim_mileage j','j.claim_id = a.id','left');
		$this->db->join('hr__claim_mileage k','k.claim_id = a.id','left');
		$this->db->group_by('j.claim_id');
		$this->db->group_by('k.claim_id');

			//others
		$this->db->join('hr__claim_others l','l.claim_id = a.id','left');
		$this->db->join('hr__claim_others m','m.claim_id = a.id','left');
		$this->db->group_by('l.claim_id');
		$this->db->group_by('m.claim_id');

			//outpatient
		$this->db->join('hr__claim_outpatient n','n.claim_id = a.id','left');
		$this->db->join('hr__claim_outpatient o','o.claim_id = a.id','left');
		$this->db->group_by('n.claim_id');
		$this->db->group_by('o.claim_id');

			//travelling
		$this->db->join('hr__claim_mileage p','p.claim_id = a.id','left');
		$this->db->group_by('p.claim_id');

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

	public function getClaimId($month,$year,$user_id)
	{
		$this->db->select('a.*');
		$this->db->where('a.user_id',$user_id);
		$this->db->where('a.year',$year);
		$this->db->where('a.month',$month);
		$this->db->where('a.is_delete',0);

		$query = $this->db->get('hr__claim a');
		return ($query->result())?$query->result():false;
	}

	public function getMyClaimMonth($year = null, $month = null)
	{
		$staff_id = $_SESSION['user']['id'];

		$this->db->select('a.*,
			SUM(b.amount_claim) as dental_claim,
			SUM(c.amount_approved) as dental_appprove,
			SUM(d.amount_claim) as hospitalize_claim,
			SUM(e.amount_approved) as hospitalize_approve,
			SUM(f.amount_claim) as meals_claim,
			SUM(g.amount_approved) as meals_approved,
			SUM(h.amount_claim) as medical_screening_claim,
			SUM(i.amount_approved) as medical_screening_approved,
			SUM(j.amount_claim) as mileage_claim,
			SUM(k.amount_approve) as mileage_approved,
			SUM(l.amount_claim) as other_claim,
			SUM(m.amount_approved) as other_approved,
			SUM(n.amount_claim) as outpatient_claim,
			SUM(o.amount_approved) as outpatient_approved,
			SUM(p.amount_approve) as travelling_approved,
			');

		if($staff_id > 0) $this->db->where('user_id',$staff_id);
		if($year) $this->db->where('year',$year);
		if($month) $this->db->where('month',$month);

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

			//dental
		$this->db->join('hr__claim_dental b','b.claim_id = a.id','left');
		$this->db->join('hr__claim_dental c','c.claim_id = a.id','left');
		$this->db->group_by('b.claim_id');
		$this->db->group_by('c.claim_id');

			//hospitalization
		$this->db->join('hr__claim_hospitalization d','d.claim_id = a.id','left');
		$this->db->join('hr__claim_hospitalization e','e.claim_id = a.id','left');
		$this->db->group_by('d.claim_id');
		$this->db->group_by('e.claim_id');

			//meals
		$this->db->join('hr__claim_meals f','f.claim_id = a.id','left');
		$this->db->join('hr__claim_meals g','g.claim_id = a.id','left');
		$this->db->group_by('f.claim_id');
		$this->db->group_by('g.claim_id');

			//medical screening 
		$this->db->join('hr__claim_medical_screening h','h.claim_id = a.id','left');
		$this->db->join('hr__claim_medical_screening i','i.claim_id = a.id','left');
		$this->db->group_by('h.claim_id');
		$this->db->group_by('i.claim_id');

			//mileage 
		$this->db->join('hr__claim_mileage j','j.claim_id = a.id','left');
		$this->db->join('hr__claim_mileage k','k.claim_id = a.id','left');
		$this->db->group_by('j.claim_id');
		$this->db->group_by('k.claim_id');

			//others
		$this->db->join('hr__claim_others l','l.claim_id = a.id','left');
		$this->db->join('hr__claim_others m','m.claim_id = a.id','left');
		$this->db->group_by('l.claim_id');
		$this->db->group_by('m.claim_id');

			//outpatient
		$this->db->join('hr__claim_outpatient n','n.claim_id = a.id','left');
		$this->db->join('hr__claim_outpatient o','o.claim_id = a.id','left');
		$this->db->group_by('n.claim_id');
		$this->db->group_by('o.claim_id');

			//travelling
		$this->db->join('hr__claim_mileage p','p.claim_id = a.id','left');
		$this->db->group_by('p.claim_id');

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

	public function insertNewClaimMonth($input)
	{
		$this->db->insert('hr__claim',$input);
		return $this->db->insert_id();
	}

	public function editClaimMonth($input,$id)
	{
		$this->db->set($input);
		$this->db->where('id',$id);
		$this->db->update('hr__claim');
	}

	public function getRefClaimType($type)
	{
		$this->db->select('*');
		$this->db->where('claim_type_id',$type);
		$query = $this->db->get('_ref__claim_type_detail');
		
		return ($query->num_rows() > 0)?$query->result() : null;
	}

	public function searchPatient($search_field)
	{
		//search staff name or ic
		$this->db->select('a.id as staff_id,a.name as staff_name,a.ic_no as staff_ic_no');
		$this->db->like('a.name',$search_field);
		$this->db->or_like('a.ic_no',$search_field);
		$this->db->where('is_delete','0');
		$query = $this->db->get('user a');
		$staff_record = ($query->num_rows())?$query->result():null;

		$this->db->select('a.id as family_id, a.name as family_name,a.relation as relation,a.ic_no as family_ic_no,
							b.id as staff_id,b.name as staff_name,b.ic_no as staff_ic_no');
		$this->db->like('a.name',$search_field);
		$this->db->or_like('a.ic_no',$search_field);
		$this->db->where('a.is_delete',0);
		$this->db->join('user b','b.id = a.user_id','left');

		$query = $this->db->get('user__family_member a');
		$family_record = ($query->num_rows())?$query->result():null;

		if(!empty($staff_record) && !empty($family_record)){
			return array_merge($staff_record,$family_record);
		}

		if(empty($staff_record) && empty($family_record)){
			return null;
		}

		if(empty($staff_record) && !empty($family_record)){
			return $family_record;
		}

		if(!empty($staff_record) && empty($family_record)){
			return $staff_record;
		}

		return null;
	}

	public function getClaims($user_id)
	{
		$where = array(
			'a.is_delete' 	=> 0,
			'a.user_id'		=> $user_id,
		);

		$this->db->where($where);
		$this->db->order_by('a.year','desc');
		$this->db->order_by('a.month','desc');
		$query = $this->db->get('hr__claim a');

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

	public function fetchClaimRecords($user_id,$where = array())
	{
		$where['a.is_delete'] = 0;
		$where['a.user_id'] = $user_id;

		$this->db->select('a.*');
		$this->db->where($where);
		$this->db->order_by('month','ASC');

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

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

	public function fetchClaimYear($user_id)
	{
		$where['a.is_delete'] = 0;
		$where['a.user_id'] = $user_id;

		$this->db->select('a.year');
		$this->db->where($where);
		$this->db->distinct();

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

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

	public function getDepartmentClaim($id)
	{
		$this->db->select('a.*,b.name');
		$this->db->where(
			array(
				'a.is_delete' 		=> 0,
				'a.is_submit'	 	=> 1,
				'a.status'			=> 0,
			)
		);
		$this->db->join('user__employment_detail b','b.user_id = a.user_id and b.department_id = '.$id);
		$this->db->order_by('a.year','desc');
		$this->db->order_by('a.month','desc');
		$query = $this->db->get('hr__claim a');
		return ($query->num_rows() > 0)?$query->result():null;
	}

	public function getSubmittedClaim($dept_id = null,$year = null, $month = null, $status = null)
	{
        $this->db->select('a.*,b.name');
        // if(!empty($dept_id))
        if(!empty($status))$this->db->where('a.status',$status);
        if(!empty($year))$this->db->where('a.year',$year); 
        if(!empty($month))$this->db->where('a.month',$month);
        if(!empty($dept_id))$this->db->where('c.department_id',$dept_id);

		// $this->db->where('a.status',0);
		$this->db->where('a.is_submit',1);
		$this->db->where('a.is_delete',0);
        $this->db->join('user b','b.id = a.user_id','left');
        $this->db->join('user__employment_detail c','c.user_id = a.user_id','left');
		$query = $this->db->get('view_claim a');

		return (!empty($query->result()))?$query->result():null;
	}

	public function getPaidClaim($dept_id = null)
	{
		$this->db->select('a.*,b.name');
		$this->db->where('a.status',1);
		$this->db->where('a.is_submit',1);
		$this->db->where('a.is_delete',0);
		$this->db->join('user b','b.id = a.user_id','left');
		$query = $this->db->get('view_claim a');

		return (!empty($query->result()))?$query->result():null;
	}

	/**
	 * submitted and not been reviewed
	 */
	public function count_dept_submit($dept_id = null)
	{
		$this->db->select('c.id,c.name,count(c.id) as submit');
		$this->db->where('a.status',0);
		$this->db->where('a.is_submit',1);
		$this->db->where('a.is_delete',0);
		if(!empty($dept_id))$this->db->where('c.id',$dept_id);
		$this->db->join('user__employment_detail b','b.user_id = a.user_id','left');
		$this->db->join('company__department c','c.id = b.department_id','left');
		$query = $this->db->get('view_claim a');

		return (!empty($query->result()))?$query->result():null;
	}

	public function count_dept_paid($dept_id = null)
	{
		$this->db->select('c.id,c.name,count(c.id) as submit');
		$this->db->where('a.status',1);
		$this->db->where('a.is_submit',1);
		$this->db->where('a.is_delete',0);
		if(!empty($dept_id))$this->db->where('c.id',$dept_id);
		$this->db->join('user__employment_detail b','b.user_id = a.user_id','left');
		$this->db->join('company__department c','c.id = b.department_id','left');
		$query = $this->db->get('view_claim a');

		return (!empty($query->result()))?$query->result():null;
	}

	/**
	 * submitted and been reviewed by HOD
	 */
	public function count_dept_submit_and_review()
	{
		
	}

	public function get_department($dept_id = null)
	{
		$this->db->select('a.name,b.name as branch');
		$this->db->where('a.is_delete',0);
		$this->db->where('a.id',$dept_id);
		$this->db->join('company__branch b','b.id = a.branch_id','left');

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

	public function getYears()
	{
		$this->db->select('year');
		$this->db->distinct();
		$query = $this->db->get('hr__claim');
		return ($query->result())?$query->result():null;
	}

	/*
	public function getAllDetailClaim($input = array())
	{

		// if($input['business_entity'])
		// if($input['department'])
		// if($input['staff_name'])
		// if($input['status'])
		// if($input['complete_status'])
		// if($input['year'])
		// if($input['month'])

		$this->db->select('a.id as claim_id,a.user_id,a.year,a.month,a.status,a.is_submit,a.paid_amount');
		// $this->db->select('b.amount as accomodation_claim_amount,b.amount_approved as accomodation_amount_approved,b.location as accomdodation_location,b.type as accomodation_type,b.is_admin_approve as accomodation_approve,b.is_hod_approve as accomodation_hod_approve');
		$this->db->select('round(sum(b.amount),2) as accomodation_claim_amount, round(sum(b.amount_approved),2) as accomodation_amount_approved');
		$this->db->select('round(sum(c.amount_claim),2) as dental_claim_amount, round(sum(c.amount_approved),2) as dental_amount_approved');
		$this->db->select('round(sum(d.amount_claim),2) as hospitalization_claim_amount, round(sum(d.amount_approved),2) as hospitalization_amount_approved');
		$this->db->select('round(sum(e.amount_claim),2) as outpatient_claim_amount, round(sum(e.amount_approved),2) as outpatient_amount_approve');
		$this->db->select('round(sum(f.amount),2) as parking_claim_amount, round(sum(f.amount_approved),2) as parking_amount_approved');


		$this->db->where('a.is_delete',0);
		$this->db->join('hr__claim_accommodation b','b.claim_id = a.id','left');
		$this->db->join('hr__claim_dental c','c.claim_id = a.id','left');
		$this->db->join('hr__claim_hospitalization d','d.claim_id = a.id','left');
		$this->db->join('hr__claim_outpatient e','e.claim_id = a.id','left');
		$this->db->join('hr__claim_parking f','f.claim_id = a.id','left');
		$this->db->group_by('b.id');

		$query = $this->db->get('hr__claim a');
		return ($query->result())?$query->result():null;
	}
	*/

	public function getFilterDetailClaim($input = array())
	{
		$claims = array();
		$this->db->select('a.id,a.year,a.month,a.status');
		$this->db->select('b.name as user_name');
		$this->db->select('c.name as business_entity');
		$this->db->select('d.name as department');

		$this->db->where('a.is_delete',0);
		if($input['business_entity'] != 'All') $this->db->where('b.business_entity_id',$input['business_entity']);
		if($input['department'] != 'All') $this->db->where('b.department_id',$input['department']);
		if($input['staff_name'] != 'All') $this->db->where('b.user_id',$input['staff_name']);
		if($input['complete_status'] != 'All') $this->db->where('a.status',$input['complete_status']);
		if($input['year'] != 'All') $this->db->where('a.year',$input['year']);
		if($input['month'] != 'All') $this->db->where('a.month',$input['month']);

		$this->db->join('user__employment_detail b','b.user_id = a.user_id');
		$this->db->join('company__business_entity c','c.id = b.business_entity_id');
		$this->db->join('company__department d','d.id = b.department_id');

		$claim_ids = $this->db->get('hr__claim a')->result();

		if(!empty($claim_ids))
		{
			foreach($claim_ids as $key => $claim)
			{
				//accomodation
				$accomodation_claim = $this->filterAccomodation($claim);array_push($claims,$accomodation_claim);

				//dental 
				$dental_claim = $this->filterDental($claim);array_push($claims,$dental_claim);
			
				//hospitalization
				$hospitalization_claim = $this->filterHospitalization($claim);array_push($claims,$hospitalization_claim);

				//outpatient 
				$outpatient_claim = $this->filterOutpatient($claim);array_push($claims,$outpatient_claim);

				//parking
				$parking_claim = $this->filterParking($claim);array_push($claims,$parking_claim);

				//subsistence allowance [outstation]
				$outstation_claim = $this->filterOutstation($claim);array_push($claims,$outstation_claim);

				//subsistence allowance [meals]
				$meals_claim = $this->filterMeals($claim);array_push($claims,$meals_claim);

				//toll allowance 
				$toll_claim = $this->filterToll($claim);array_push($claims,$toll_claim);

				//trainer
				$trainer_claim = $this->filterTrainer($claim);array_push($claims,$trainer_claim);

				//transportation
				$transportation_claim = $this->filterTransportation($claim);array_push($claims,$transportation_claim);

				//mileage
				$mileage_claim = $this->filterMileage($claim);array_push($claims,$mileage_claim);
			}
		}
		return $claims;
		
	}

	public function filterAccomodation($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approve as admin_status');
		$this->db->select('a.is_hod_approve as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$accomodation_result = $this->db->get('hr__claim_accommodation a')->result();
		if(!empty($accomodation_result))
		{
			$accomodation_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type' 				=> 'ACCOMODATION',
				'amount'			=> $accomodation_result[0]->amount_claim,
				'admin_status'		=> $accomodation_result[0]->admin_status,
				'hod_status'		=> $accomodation_result[0]->hod_status,
			);
			$claims = $accomodation_array;
		}
		return $claims;	
	}

	public function filterDental($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount_claim),2) as amount_claim');
		$this->db->select('a.is_approve as admin_status'); 
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$dental_result = $this->db->get('hr__claim_dental a')->result();
		if(!empty($dental_result))
		{
			$dental_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'DENTAL',
				'amount'			=> $dental_result[0]->amount_claim,
				'admin_status'		=> $dental_result[0]->admin_status,
				'hod_status'		=> $dental_result[0]->hod_status,
			);
			$claims = $dental_array;
		}
		return $claims;	
	}

	public function filterHospitalization($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount_claim),2) as amount_claim');
		$this->db->select('a.is_approve as admin_status');
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$hospitalization_result = $this->db->get('hr__claim_hospitalization a')->result();
		if(!empty($hospitalization_result))
		{
			$hospitalization_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'HOSPITALIZATION',
				'amount'			=> $hospitalization_result[0]->amount_claim,
				'admin_status'		=> $hospitalization_result[0]->admin_status,
				'hod_status'		=> $hospitalization_result[0]->hod_status
			);
			$claims = $hospitalization_array;
		}
		return $claims;
	}

	public function filterOutpatient($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount_claim),2) as amount_claim');
		$this->db->select('a.is_approve as admin_status');
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$outpatient_result = $this->db->get('hr__claim_outpatient a')->result();
		if(!empty($outpatient_result))
		{
			$outpatient_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'OUTPATIENT',
				'amount'			=> $outpatient_result[0]->amount_claim,
				'admin_status'		=> $outpatient_result[0]->admin_status,
				'hod_status'		=> $outpatient_result[0]->hod_status,
			);
			$claims = $outpatient_array;
		}
		return $claims;
	}

	public function filterParking($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approve as admin_status');
		$this->db->select('a.is_hod_approve as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$parking_result = $this->db->get('hr__claim_parking a')->result();
		if(!empty($parking_result))
		{
			$parking_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'PARKING',
				'amount'			=> $parking_result[0]->amount_claim,
				'admin_status'		=> $parking_result[0]->admin_status,
				'hod_status'		=> $parking_result[0]->hod_status,
			);
			$claims = $parking_array;
		}
		return $claims;
	}

	public function filterOutstation($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approved as admin_status');
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$this->db->where('a.allowance','OUTSTATION');
		$outstation_result = $this->db->get('hr__claim_subsistence a')->result();
		if(!empty($outstation_result))
		{
			$outstation_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'OUTSTATION',
				'amount'			=> $outstation_result[0]->amount_claim,
				'admin_status'		=> $outstation_result[0]->admin_status,
				'hod_status'		=> $outstation_result[0]->hod_status
			);
			$claims = $outstation_array;
		}
		return $claims;
	}

	public function filterMeals($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approved as admin_status');
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$this->db->where('a.allowance','MEALS');
		$meals_result = $this->db->get('hr__claim_subsistence a')->result();
		if(!empty($meals_result))
		{
			$meals_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'MEALS',
				'amount'			=> $meals_result[0]->amount_claim,
				'admin_status'		=> $meals_result[0]->admin_status,
				'hod_status'		=> $meals_result[0]->hod_status,
			);
			$claims = $meals_array;
		}
		return $claims;
	}

	public function filterToll($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approve as admin_status');
		$this->db->select('a.is_hod_approve as hod_status');
		$this->db->where('a.claim_id',$claim->id);
		$toll_result = $this->db->get('hr__claim_toll a')->result();
		if(!empty($toll_result))
		{
			$toll_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'TOLL',
				'amount'			=> $toll_result[0]->amount_claim,
				'admin_status'		=> $toll_result[0]->admin_status,
				'hod_status'		=> $toll_result[0]->hod_status
			);
			$claims = $toll_array;
		}
		return $claims;
	}

	public function filterTrainer($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_admin_approve as admin_status');
		$this->db->select('a.is_hod_approve as hod_status');
		$this->db->where('a.claim_id',$claim->id);
		$trainer_result = $this->db->get('hr__claim_trainer a')->result();
		if(!empty($trainer_result))
		{
			$trainer_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'TRAINER',
				'amount'			=> $trainer_result[0]->amount_claim,
				'admin_status'		=> $trainer_result[0]->admin_status,
				'hod_status'		=> $trainer_result[0]->hod_status
			);
			$claims = $trainer_array;
		}
		return $claims;
	}

	public function filterTransportation($claim)
	{
		$claims = array();
		$this->db->select('round(sum(a.amount),2) as amount_claim');
		$this->db->select('a.is_hod_approved as hod_status');
		$this->db->select('a.is_admin_approved as admin_status');

		$this->db->where('a.claim_id',$claim->id);
		$transportation_result = $this->db->get('hr__claim_transportation a')->result();
		if(!empty($transportation_result))
		{
			$transportation_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,						
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'TRANSPORTATION',
				'amount'			=> $transportation_result[0]->amount_claim,
				'admin_status'		=> $transportation_result[0]->admin_status,
				'hod_status'		=> $transportation_result[0]->hod_status,
			);
			$claims = $transportation_array;
		}
		return $claims;	
	}

	public function filterMileage($claim)
	{
		$claims = array();
		$this->db->select('round(sum(b.amount),2) as amount_claim');
		$this->db->select('a.is_hod_approved as hod_status');

		$this->db->where('a.claim_id',$claim->id);
		$this->db->join('hr__claim_travelling_detail b','b.claim_travelling_id = a.id','left');
		$mileage_result = $this->db->get('hr__claim_travelling a')->result();
		if(!empty($mileage_result))
		{
			$mileage_array = array
			(
				'claim_status'		=> $claim->status,
				'business_entity'	=> $claim->business_entity,
				'department'		=> $claim->department,
				'name'				=> $claim->user_name,						
				'year'				=> $claim->year,
				'month'				=> $claim->month,
				'type'				=> 'MILEAGE',
				'amount'			=> $mileage_result[0]->amount_claim,
				'admin_status'		=> 0,
				'hod_status'		=> $mileage_result[0]->hod_status,
			);
			$claims = $mileage_array;
		}
		return $claims;
	}

	public function is_claim_created($user_id,$submission_month,$submission_year)
	{
		$result = $this->db->select('*')
				->where('user_id',$user_id)
				->where('month',$submission_month)
				->where('year',$submission_year)
				->where('is_delete',0)
				->get('hr__claim')
				->result();
		return ($result)?true:false;
	}

	public function unsubmitted_claim($claim_id)
	{
		$this->db->set('is_submit',0);
		$this->db->where('id',$claim_id);
		$this->db->update('hr__claim');


		//update all claims as draft
			//-------------------- medical claim
			//outpatients 
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_outpatient_journal');
				
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_outpatient'); 


			//hospitalization
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_hospitalization');

			//dental optical 
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_dental_journal');

				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_dental');

			//panel
			//-------------------- medical claim

			//mileage
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_travelling');
			
			//petrol
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_petrol');

			//accomodation
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_accommodation');

			//transportation
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_transportation');

			//subsistence allowance
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_subsistence');

			//parking
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_parking');

			//toll
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_toll');

			//trainer
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_trainer');

			//meal allowance
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_meals');

			//entertainment
				$this->db->set('is_hod_approve',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_entertainment');

			//others
				$this->db->set('is_hod_approved',0);
				$this->db->where('claim_id',$claim_id);
				$this->db->update('hr__claim_others');

		return true; 
	}

	public function checkGadgetYear($staff_id, $year_start)
	{
		$result = $this->db->select('*')
				->where('is_delete',0)
				->where('staff_id',$staff_id)
				->where('year_start',$year_start)
				->or_where('year_end',$year_start)
				->get('hr__claim_allocation_gadget')
				->result();

		return ($result)?true:false;
	}

	public function getDressing_allocation($year)
	{
		$this->db->select('a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, b.title as designation');
		$this->db->join('user__employment_detail b','b.user_id = a.staff_id');
		$this->db->join('company__department c','c.id = b.department_id');
		$this->db->join('company__branch d','d.id = c.branch_id');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.year', $year);

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

	public function getGadget_allocation($year)
	{
		$this->db->select('a.*, b.name as staff_name, c.name as department_name, d.name as branch_name, b.title as designation');
		$this->db->join('user__employment_detail b','b.user_id = a.staff_id');
		$this->db->join('company__department c','c.id = b.department_id');
		$this->db->join('company__branch d','d.id = c.branch_id');
		$this->db->where('a.is_delete', 0);
		$this->db->where('a.year_start', $year);
		$this->db->or_where('a.year_end', $year);

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

	public function checkDressingClaim($staff_id, $year)
	{
		$result = $this->db->select('a.*')
				->join('hr__claim b','b.id = a.claim_id','left outer')
				->where('a.is_delete',0)
				->where('YEAR(a.date)',$year)
				->where('b.is_delete',0)
				->where('b.user_id',$staff_id)
				->get('hr__claim_dressing a')
				->result();

		return ($result)?true:false;
	}

	public function checkGadgetClaim($staff_id, $year_start, $year_end)
	{
		$result = $this->db->select('a.*')
				->join('hr__claim b','b.id = a.claim_id','left outer')
				->where('a.is_delete',0)
				->where('b.is_delete',0)
				->where('b.user_id',$staff_id)
				->where('YEAR(a.date) >=',$year_start)
				->where('YEAR(a.date) <=',$year_end)
				->get('hr__claim_gadget a')
				->result();
				
		return ($result)?true:false;
	}

	public function check_existing_year_allocation_dressing($staff_id, $year)
	{
		$result = $this->db->select('*')
				->where('is_delete',0)
				->where('staff_id',$staff_id)
				->where('year',$year)
				->get('hr__claim_allocation_dressing')
				->result();

		return ($result)?true:false;
	}

	public function check_existing_year_allocation_gadget($staff_id, $year_start)
	{
		$result = $this->db->select('*')
				->where('is_delete',0)
				->where('staff_id',$staff_id)
				->where('year_start',$year_start)
				->or_where('year_end',$year_start)
				->get('hr__claim_allocation_gadget')
				->result();

		return ($result)?true:false;
	}

}
?>