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

class Model_dental extends CI_Model 
{

	function fetchDental($user_id = null,$claim_id = null,$year = null,$month = null, $mode = null)
	{
		$this->db->select('a.*,b.year,b.month');
		if($user_id)$this->db->where('b.user_id',$user_id);
		if($claim_id)$this->db->where('a.claim_id',$claim_id);
		
		if($mode == 1){
			if($year)$this->db->where('YEAR(a.date)',$year);
			if($month)$this->db->where('MONTH(a.date)',$month);
		}else{
			if($year)$this->db->where('b.year',$year);
			if($month)$this->db->where('b.month',$month);
		}

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

		if($user_id || $year || $month) $this->db->join('hr__claim b','b.id = a.claim_id AND b.is_delete = 0 AND b.is_submit = 1');

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

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

	function fetchDentalJournal($user_id = null,$claim_id = null,$year = null,$month = null)
	{
		$return = array();

		$this->db->select('a.*');
		if($user_id)$this->db->where('a.user_id',$user_id);
		if($claim_id)$this->db->where('a.claim_id',$claim_id);
		if($year)$this->db->where('a.year',$year);
		if($month)$this->db->where('a.month',$month);

		$this->db->where('a.is_delete',0);
		$query = $this->db->get('hr__claim_dental_journal a');

		if($query->result())
		{
			foreach ($query->result() as $key => $result) 
			{
				$result->payment_month = $this->fetchDentalbyJournalId($result->id);
				array_push($return,$result);
			}
		}		

		return $return;
	}

	function fetchDentalbyJournalId($journal_id)
	{
		$this->db->select('a.*');
		$this->db->where('a.journal_id',$journal_id);
		$this->db->where('a.is_delete',0);
		$this->db->join('hr__claim b','b.id = a.claim_id');
		$query = $this->db->get('hr__claim_dental a');
		return ($query->result())?$query->result():null;
	}

	function fetchSingleDental($user_id = null,$claim_id = null,$year = null,$month = null)
	{
		$dentals = $this->fetchDental($user_id,$claim_id,$year,$month);
		return ($dentals)?$dentals[0]:null;
	}

	function createDental($input)
	{
		$this->db->insert('hr__claim_dental',$input);
		return $this->db->insert_id();
	}

	function createJournal($input)
	{
		$this->db->insert('hr__claim_dental_journal',$input);
		return $this->db->insert_id();
	}

	function createAttachment($input)
	{
		return ($this->db->insert_batch('hr__claim_dental_attachment',$input))?true:false;
	}

	function updateDental($input,$where = array())
	{
		$this->db->set($input);
		foreach ($where as $key => $wh) {
			$this->db->where($key,$wh);
		}

		$this->db->update('hr__claim_dental');
	}

	function updateDentalJournal($input,$where = array())
	{
		$this->db->set($input);
		foreach ($where as $key => $wh) {
			$this->db->where($key,$wh);
		}

		$this->db->update('hr__claim_dental_journal');

	}

	function removeDental($id)
	{
		$data 	= array('is_delete' => 1);
		$where 	= array('id' => $id);
		$this->updateDental($data,$where);
	}

	function removeDentalJournal($journal_id)
	{
		//remove 
		$this->db->set(array('is_delete' => 1))
			->where('id',$journal_id)
			->update('hr__claim_dental_journal');


		$this->db->set(array('is_delete' => 1))
			->where('journal_id',$journal_id)
			->update('hr__claim_dental');
			
	}

	function getAttachment($journal_id)
	{
		$this->db->where('journal_id',$journal_id);
		$this->db->where('is_delete',0);
		$query = $this->db->get('hr__claim_dental_attachment');
		return ($query->num_rows() > 0)?$query->result():null;
	}

	function fetchAmountAdminApproved($year)
	{
		// $this->db->select('SUM(a.amount_approved)');
		$this->db->select('SUM(a.amount_approved) as approved');
		$this->db->where('a.is_approve',1);
		$this->db->where('b.year',$year);
		$this->db->join('hr__claim b','b.id = a.id','left');
		$query  = $this->db->get('hr__claim_dental a');
		return ($query->result() > 0)?$query->result()[0]:0;
	}

	function fetchAmountDraft($year)
	{
		$this->db->select('SUM(a.amount_claim) as claimed');
		$this->db->where('b.is_submit',0);
		$this->db->join('hr__claim b','b.id = a.claim_id','left');
		$query = $this->db->get('hr__claim_dental a');
		return ($query->result() > 0)?$query->result()[0]:0;
	}

	public function fetchAttachmentbyUniqueid($unique_id)
	{
		$this->db->select('a.*');
		$this->db->where('a.unique_id',$unique_id);
		$query = $this->db->get('hr__claim_dental_attachment a');
		return ($query->result())?$query->result():null;
	}

	/**
	 * version 2.0
	 * fetch approve value
	 */



	public function get_approve_amount($year,$user_id)
	{
		$this->db->select('sum(a.amount_approved) as amount');
		$this->db->where('a.date >= ',$year.'-01-01');
		$this->db->where('a.date <= ',$year.'-12-31');
		$this->db->where('a.is_approve !=',2);
		$this->db->where('a.is_delete',0);
		$this->db->where('b.is_delete',0);
		$this->db->where('b.user_id',$user_id);

		$this->db->join('hr__claim b','b.id = a.claim_id');
		$query = $this->db->get('hr__claim_dental a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;	
	}

	public function get_pending_amount($year,$user_id)
	{
		$this->db->select('sum(a.amount_claim) as amount');
		$this->db->where('a.date >= ',$year.'-01-01');
		$this->db->where('a.date <= ',$year.'-12-31');
		$this->db->where('a.is_approve !=',2);
		$this->db->where('a.is_approve !=',1);
		$this->db->where('a.is_delete',0);
		$this->db->where('b.is_delete',0); 
		$this->db->where('b.user_id',$user_id);

		$this->db->join('hr__claim b','b.id = a.claim_id');
		$query = $this->db->get('hr__claim_dental a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;
	}

	public function get_rejected_amount($year,$month,$user_id)
	{
		$this->db->select('sum(a.amount_claim) as amount');
		$this->db->where('a.date >= ',$year.'-01-01');
		$this->db->where('a.date <= ',$year.'-12-31');
		// $this->db->where('a.prorate_month <= ',$month);
		$this->db->where('a.is_approve',2);
		$this->db->where('a.is_delete',0);
		$this->db->where('b.is_delete',0);
		$this->db->where('b.user_id',$user_id); 

		$this->db->join('hr__claim b','b.id = a.claim_id');
		$query = $this->db->get('hr__claim_dental a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;
	}



	public function get_dental_amount($month,$year,$user_id)
	{
		$this->load->model('hr/model_claim_allocation','allocate');
		// $prorate_data = $this->allocate->get_allocation_for_year($year, $user_id);

		// if(!empty($prorate_data))
		// {	
		// 	$prorate_month_amount =  $prorate_data[0]->prorate[$month-1]->dental_optical;
		// }else{
		// 	$prorate_month_amount = 0;			
		// }

		$pending_amount 		= $this->get_pending_amount($year,$user_id);
		$approved_amount 		= $this->get_approve_amount($year,$user_id);
		// $rejected_amount 		= null;

		$rejected_amount 		= $this->get_rejected_amount($year,null,$user_id);
		$panel_amount 			= null;
		
		$data = array
		(
			'year' 				=> $year,
			'month'				=> $month,
			'user_id'			=> $user_id,
			// 'prorate_amount'	=> $prorate_month_amount,
			'pending_amount'	=> $pending_amount,
			'approved_amount'	=> $approved_amount,
			'rejected_amount'	=> $rejected_amount,
			'panel_amount'		=> $panel_amount,
			// 'balance'			=> $prorate_month_amount -$pending_amount - $approved_amount - $panel_amount
			'balance'			=> $pending_amount - $approved_amount - $panel_amount
		);

		return $data;
	}


	public function get_panel_amount($month,$year,$user_id)
	{
		$this->db->select('sum(a.amount) as amount');
		$this->db->where('a.is_delete',0);
		$this->db->where('a.staff_id',$user_id);

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

	/**
	 * 
	 */
	public function get_amount_claimed($year)
	{
		$amount = $this->get_approve_amount($year) + $this->get_pending_amount($year) + $this->get_panel_amount($year);
		return $amount;
	}

	public function get_view_claim_dental_optical($user_id,$year,$month)
	{
		$result = $this->db->select('a.*')
				->where('a.user_id',$user_id)
				->where('a.month',$month)
				->get('view_claim_dental_optical a')
				->result();
		return ($result)?$result:null;
	}

	
	public function get_balance_allocation_for_the_year($user_id,$year)
    {
		$this->db->select('*');
		$this->db->where('a.user_id',$user_id);
		$this->db->where('a.year',$year);
		$this->db->order_by('a.month','DESC');
		$this->db->limit(1);
		$query = $this->db->get('view_claim_dental_optical a');
		return ($query->result())?$query->result()[0]->balanced_prorate:0;


        // $claimed_amount = $this->get_outpatient_claimmed_amount($user_id,$year);
        // $allocation = $this->allocation($user_id,$year);
        // return ($allocation - $claimed_amount > 0)?$allocation - $claimed_amount:0;
    }

    //steps 1 : get claim amount 
    //         if 
    //          1. draft    : get amount claim
    //          2. reject   : amount is zero
    //          3. approve  : approve amount 
    //          4. panel clinic claim 
    public function get_outpatient_claimmed_amount($user_id,$year)
    {
        $draft_array    = $this->db->select('claim.amount_claim')
                        ->where('journal.is_delete',0)
                        ->where('claim.is_delete',0)
                        ->where('claim.is_approve',0)
                        ->join('hr__claim_dental claim','claim.journal_id = journal.id','left')
                        ->get('hr__claim_dental_journal journal');

        $approve_array  = $this->db->select('claim.amount_approved')
                        ->where('journal.is_delete',0)
                        ->where('claim.is_delete',0)
                        ->where('claim.is_approve',1)
                        ->join('hr__claim_dental claim','claim.journal_id = journal.id','left')
                        ->get('hr__claim_dental_journal journal');

		$panel_claim 	= $this->db->select('sum(detail.amount) as total')
						->where('panel.is_delete',0)
						->where('panel.invoice_year',$year)
						->where('detail.is_delete',0)
						->where('detail.staff_id',$user_id)
						->where('detail.panel_claim_id',4)
						->join('hr__claim_panel_clinic_detail detail','detail.panel_claim_id = panel.id','left')
						->get('hr__claim_panel_clinic panel');

		$draft          = 0;
        $approved       = 0;
		$rejected       = 0;
		$panel_amount 	= (!empty($panel_claim->result()))?$panel_claim->result()[0]->total:0;

        if(!empty($draft_array))
        {
            foreach ($draft_array->result() as $key => $value) 
            {
                $draft += $value->amount_claim;
            }
        }

        if(!empty($approve_array))
        {
            foreach ($approve_array->result() as $key => $value) 
            {
                $approved += $value->amount_approved;    
            }
        }

        return ($draft + $approved + $rejected + $panel_amount > 0)? $draft + $approved + $rejected + $panel_amount:0;
	}
	
    //steps 2: 
    //      1. get last allocation amount (prorate)
    public function allocation($user_id,$year)
    {
        $result = $this->db->select('max(a.dental_optical) as allocation')
                ->where('a.user_id',$user_id)
                ->where('a.year',$year)
                ->get('hr__claim_allocation_prorate a');

        if(!empty($result->result()))
        {
            return $result->result()[0]->allocation;
        }else{
            return 0;
        }
	}

	public function get_all_claim_by_prorated_year($user,$year)
	{
		$results = array();
		$journals = $this->db->select('*')
					->where('year',$year)
					->where('user_id',$user)
					->where('is_delete',0)
					->get('hr__claim_dental_journal')
					->result();

		if(!empty($journals))
		{
			foreach ($journals as $key => $journal) 
			{
				$journals[$key]->dentals = array();
				$monthly_claims = $this->db->select('*')
				->where('journal_id',$journal->id)
				->where('is_delete',0)
				->get('hr__claim_dental')
				->result();

				if(!empty($monthly_claims))
				{
					foreach ($monthly_claims as $inner_key => $month_claim) 
					{
						if($year == date('Y',strtotime($month_claim->date)))
						{
							array_push($journals[$key]->dentals,$month_claim);
						}
					}
				}
				array_push($results,$journals);
			}
		}
		return ($results)?$results:null;
	}

	/**
	 * kenapa fetch journal tapi database bukan journal
	 *  */
	public function fetchJournalById($id)
	{
		$result = $this->db->select('*')
						->where('id',$id)
						->where('is_delete',0)
						->get('hr__claim_dental_journal')
						->result();
		return ($result)?$result:null;
    }
    
    public function get_current_unsubmitted_dental()
    {
        $results = $this->db->get('view__current_month_unsubmitted_dental_or_optical_claim')->result();
        return (!empty($results))?$results:null;
    }

    public function get_panel_amount_no_prorate($year,$user_id)
	{
		$this->db->select('sum(a.amount) as amount');
		$this->db->where('a.date >= ',$year.'-01-01');
		$this->db->where('a.date <= ',$year.'-12-31');
		$this->db->where('a.is_delete',0);
		$this->db->where('a.staff_id',$user_id);
		$this->db->where('a.claim_type_id',4);

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

		return (!empty($query->result()))?$query->result()[0]->amount:0;
    }

    public function get_claimed_amount($year,$user_id)
	{
		$amount = 0;
		$dental_optical = $this->fetchDental($user_id,null,$year,null,1);
		if(!empty($dental_optical)){
			foreach ($dental_optical as $key => $row) 
			{
				/**
				 * situation :
				 * 1. admin not approve hod not approve (0 & 0) 		amount += amount_claim
				 * 2. admin reject and hod approve (2 & 0) 				amount +=  0
				 * 3. admin not approve and hod approve (0 & 1) 		amount += amount_claim
				 * 4. admin approve and hod approve (1 & 1)				amount += amount_approved
				 */

					if($row->is_approve == 2)
					{ 
						$amount += 0;
					}
					elseif ($row->is_approve == 1) 
					{
						$amount += $row->amount_approved;
					}else{
						$amount += $row->amount_claim;
					}
			}
		}
		return $amount;
	}

	public function get_amount_approved($year,$user_id)
	{
		$amount = 0;
		$dental_optical = $this->fetchDental($user_id,null,$year,null);
		if(!empty($dental_optical)){
			foreach ($dental_optical as $key => $row) 
			{
				if($row->is_approve == 1)
				{ 
					$amount += $row->amount_approved;
				}
			}
		}
		return $amount;
	}

	public function get_amount_pending($year,$user_id)
	{
		$amount = 0;
		$dental_optical = $this->fetchDental($user_id,null,$year,null);
		if(!empty($dental_optical)){
			foreach ($dental_optical as $key => $row) 
			{
				if($row->is_approve != 2 && $row->is_approve != 1)
				{ 
					$amount += $row->amount_claim;
				}
			}
		}
		return $amount;
	}
}
?>