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

class Model_claim_outpatient extends CI_Model 
{

	public function fetchOutpatient($user_id = null,$claim_id = null,$year = null, $month = null,$admin_status = null,$hod_status = null)
	{
		$this->db->select
		('
			a.*,
			b.ID as claim_id, b.user_id,b.year,b.month,b.status,
			a.clinic_id as clinic_name
		');

		if($user_id) $this->db->where('b.user_id',$user_id);
		if($claim_id)$this->db->where('a.claim_id',$claim_id);
		// if($year) $this->db->where('b.year',$year);
		// if($month) $this->db->where('b.month',$month);
		if($admin_status) $this->db->where('is_approve',$admin_status);
		if($hod_status) $this->db->where('is_hod_approved',$hod_status);

		$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');
		// $this->db->join('customer c','c.id = a.clinic_id','left');

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

		// echo $this->db->last_query();
		return ($query->result())?$query->result():null;
	}
	
	public function fetchOutpatientByStatus($year = null, $month = null, $claim_status = array(), $outpatient_status = array())
	{
		$this->db->select('a.*');
		if($year) $this->db->where('b.year',$year);
		if($month) $this->db->where('b.month',$month);
		if($claim_status)
		{
			foreach ($claim_status as $key => $value) {
				$this->db->where('b.'.$key,$value);
			}
		}
		
		if($outpatient_status) 
		{
			foreach ($outpatient_status as $key => $value) {
				$this->db->where('a.'.$key,$value);
			}
		}

		$this->db->join('hr__claim b','b.id = a.claim_id','left');
		$query = $this->db->get('hr__claim_outpatient a');
		return ($query->num_rows() > 0)?$query->result():null;
	}

	public function fetchSingleOutpatient($user_id = null,$claim_id = null,$year = null, $month = null)
	{
		$allocation =  $this->fetchOutpatient($user_id,$claim_id,$year,$month);
		return $allocation[0];
	}

	public function fetchOutpatientJournal($user_id = null,$claim_id = null,$year = null, $month = null)
	{
		$temp = 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('is_delete',0);
		$query = $this->db->get('hr__claim_outpatient_journal a');
		if($query->result())foreach ($query->result() as $key => $result) 
		{
			$result->payment_month = $this->fetchOutpatientByJournalId($result->id);
			array_push($temp,$result);
		}

		return $temp;
	}

	/**
	 * kenapa fetch journal sedangkan database bukan..
	 */
	public function fetchJournalById($id)
	{
		$result = $this->db->select('*')
						->where('id',$id)
						->where('is_delete',0)
						->get('hr__claim_outpatient_journal')
						->result();
		return ($result)?$result:null;
	}

	public function fetchOutpatientByJournalId($journal_id)
	{
		$this->db->select('a.*');
		$this->db->where('a.journal_id',$journal_id);
		$this->db->where('a.is_delete',0);
		$query = $this->db->get('hr__claim_outpatient a');
		return ($query->result())?$query->result():null;
	}

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

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

	//batch insert 
	public function createAttachment($input)
	{
		return ($this->db->insert_batch('hr__claim_outpatient_attachment',$input))?true:false;
	}

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

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

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

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

	public function removeOutpatientJournal($id)
	{
		$data 	= array('is_delete' => 1);
		$where 	= array('id' => $id);
		$out_where = array('journal_id' => $id);
		$this->db->set($data)->where($where)->update('hr__claim_outpatient_journal');
		$this->db->set($data)->where($out_where)->update('hr__claim_outpatient');
	}

	public function removeOutpatient($id)
	{
		$data 	= array('is_delete' => 1);
		$where 	= array('id' => $id);
		$this->updateOutpatient($data,$where);
	}

	public function fetchAttachment($outpatient_id)
	{	
		$this->db->where('journal_id',$outpatient_id);
		$query = $this->db->get('hr__claim_outpatient_attachment');
		return ($query->num_rows() > 0)?$query->result():null;
	}

	public function fetchAttachmentByUniqueId($unique_id)
	{
		$this->db->where('unique_id',$unique_id);
		$query = $this->db->get('hr__claim_outpatient_attachment');
		return ($query->num_rows() > 0)?$query->result():null;	
	}

	public function fetchDraft($user_id,$year)
	{
		$this->db->select
		('
			a.*,
			b.date,
			b.invoice_chit,
			b.clinic_id as clinic_name,
			b.patient_name,
			b.treatment,
			b.amount_claim,
		');

		$this->db->where('a.year',$year);
		$this->db->where('a.user_id',$user_id);
		$this->db->where('a.status',0);
		$this->db->where('b.is_approve',0);
		$this->db->where('b.is_hod_approved',0);
		$this->db->join('hr__claim_outpatient b','b.claim_id = a.id','left');
		$query = $this->db->get('hr__claim a');
		return ($query->result() > 0)?$query->result():null;
	}

	/**
	 * version 2.0  
	 * 
	 */
	public function get_claim_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]->outpatient;
		// }else{
		// 	$prorate_month_amount = 0;			
		// }


		$pending_amount 	= (($this->get_pending_amount($year,$user_id)) != null)?$this->get_pending_amount($year,$user_id):0;
		$approved_amount 	= (($this->get_approved_amount($year,$user_id)) != null)?$this->get_approved_amount($year,$user_id):0;
		$rejected_amount 	= (($this->get_rejected_amount($year,$user_id)) != null)?$this->get_approved_amount($year,$user_id):0;
		$panel_amount 		= $this->get_panel_amount_no_prorate($year,$user_id);

		$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_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_outpatient a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;
	}

	public function get_approved_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_outpatient a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;		
	}

	public function get_rejected_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.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_outpatient a');
		return (!empty($query->result()))?$query->result()[0]->amount:0;	
	}

	public function get_panel_amount($year,$user_id)
	{
		return 0;
    }
    
    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',1);

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

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

    /**
     * if claim is already approved get the value of the approved claim
     * if the claim havent been approved get the value of the claim amount
     * 
     */
    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_outpatient a');
		return ($query->result())?$query->result()[0]->balanced_prorate:0;

		// die(debug($query->result()));

        // $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_outpatient claim','claim.journal_id = journal.id','left')
                        ->get('hr__claim_outpatient_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_outpatient claim','claim.journal_id = journal.id','left')
                        ->get('hr__claim_outpatient_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',1)
						->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;

		// die(debug($panel_amount));
        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.outpatient) 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_view_outpatient($month,$year,$user_id)
	{
		$result = $this->db->select('*')->where('user_id',$user_id)->where('month',$month)->where('year',$year)->get('view_claim_outpatient')->result();
		return ($result > 0)?$result:null;
	}


	/**
	 * current year and next year for prorated month 11 and 12
	 * kene check user_id
	 *  */
	public function get_all_claim_by_prorated_year($user_id,$year)
	{
		$results  = array();
		$journals = $this->db->select('*')
					->where('year',$year)
					->where('user_id',$user_id)
					->where('is_delete',0)
					->get('hr__claim_outpatient_journal')
					->result();

		if(!empty($journals))
		{
			foreach ($journals as $key => $journal) 
			{
				$journals[$key]->outpatients = array();
				$monthly_claims = $this->db->select('*')
				->where('journal_id',$journal->id)
				->where('is_delete',0)
				->get('hr__claim_outpatient')
				->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]->outpatients,$month_claim);
						}
					}
				}
			}
		}
		array_push($results,$journals);


		//bulan satu tahun depan
		$journals = $this->db->select('*')
						->where('year',$year+1)
						->where('month',1)
						->where('is_delete',0)
						->get('hr__claim_outpatient_journal')
						->result();
		if(!empty($journals))
		{
			foreach ($journals as $key => $journal) 
			{
				$journals[$key]->outpatients = array();
				$monthly_claims = $this->db->select('*')
							->where('journal_id',$journal->id)
							->where('is_delete',0)
							->get('hr__claim_outpatient')
							->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]->outpatients,$month_claim);
						}
					}
				}
			}
		}
		array_push($results,$journals);


		$have_year = false;
		$temps = array();
		//bulan dua tahun depan
		$journals = $this->db->select('*')
						->where('year',$year+1)
						->where('month',2)
						->where('is_delete',0)
						->get('hr__claim_outpatient_journal')
						->result();
		
		if(!empty($journals))
		{
			foreach ($journals as $key => $journal) 
			{
				$journals[$key]->outpatients = array();
				$monthly_claims = $this->db->select('*')
								->where('journal_id',$journal->id)
								->where('is_delete',0)
								->get('hr__claim_outpatient')
								->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]->outpatients,$month_claim);
							array_push($temps,$journals);
						}
					}
				}
			}
		}
		array_push($results,$temps);

		return ($results)?$results:null;
	}

	public function fetch_view_claim_outpatient($user_id,$month,$year)
	{
		$result = $this->db->select('*')->where('user_id',$user_id)->where('month',$month)->where('year',$year)->get('view_claim_outpatient')->result();
		return ($result)?$result[0]:null;
    }
    
    public function get_current_unsubmitted_outpatient()
    {
        $results = $this->db->get('view__current_month_unsubmitted_outpatient_claim')->result();
        return (!empty($results))?$results:null;
    }
}
?>