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

class Model_gadget extends CI_Model 
{
	function __construct()
	{
		parent::__construct();
		// if(ENVIRONMENT == 'production'){
		// 	$this->erp_db = "spserp_master";
		// 	$this->mgs_db = "mgsdb_master";
		// } else if(ENVIRONMENT == 'development'){
		// 	$this->erp_db = "spserp_dev";
		// 	$this->mgs_db = "mgsdb_dev";
		// }
	}

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

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

	public function get_balance_allocation_for_the_year($user_id = null, $year_start = null)
    {
		$query = $this->db->query("SELECT a.staff_id as staff_id, 
					concat(a.year_start,'-',a.year_end) as year,
					a.amount as allocation,
					ifnull(
					    (select sum(b.amount_claim) 
						from hr__claim_gadget b  
					    left outer join hr__claim c on c.id = b.claim_id 
						where b.is_delete = 0 and b.is_approve = 0 and (year(b.date) BETWEEN a.year_start and a.year_end) and c.user_id = a.staff_id),0) as pending,
					ifnull(
					    (select sum(b.amount_claim) 
						from hr__claim_gadget b
					    left outer join hr__claim c on c.id = b.claim_id
						where b.is_delete = 0 and b.is_approve = 1 and (year(b.date) BETWEEN a.year_start and a.year_end) and c.user_id = a.staff_id),0) as approved,
					(select (a.amount-pending-approved)) as balance
					FROM hr__claim_allocation_gadget a
					WHERE a.is_delete = 0
					AND staff_id = ".$user_id."
					AND (year_start = ".$year_start." 
					OR year_end = ".$year_start.")");

		return ($query->result())?$query->result()[0]->balance:0;
    }

    public function get_balance_allocation_for_the_year_all($user_id = null, $year_start = null)
    {
		$query = $this->db->query("SELECT a.staff_id as staff_id, 
					concat(a.year_start,'-',a.year_end) as year,
					a.amount as allocation,
					ifnull(
					    (select sum(b.amount_claim) 
						from hr__claim_gadget b  
					    left outer join hr__claim c on c.id = b.claim_id 
						where b.is_delete = 0 and b.is_approve = 0 and (year(b.date) BETWEEN a.year_start and a.year_end) and c.user_id = a.staff_id),0) as pending,
					ifnull(
					    (select sum(b.amount_claim) 
						from hr__claim_gadget b
					    left outer join hr__claim c on c.id = b.claim_id
						where b.is_delete = 0 and b.is_approve = 1 and (year(b.date) BETWEEN a.year_start and a.year_end) and c.user_id = a.staff_id),0) as approved,
					(select (a.amount-pending-approved)) as balance
					FROM hr__claim_allocation_gadget a
					WHERE a.is_delete = 0
					AND staff_id = ".$user_id."
					AND (year_start = ".$year_start." 
					OR year_end = ".$year_start.")");

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

    function fetchGadget($claim_id = null)
	{
		$this->db->select('a.*,b.year,b.month');
		$this->db->join('hr__claim b','b.id = a.claim_id');
		if($claim_id)$this->db->where('a.claim_id',$claim_id);
		$this->db->where('a.is_delete',0);

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

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

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

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

	public function updateClaim($input,$where)
    {
		$this->db->set($input);
		foreach ($where as $key => $wh) {
			$this->db->where($key,$wh);
		}
        $this->db->update('hr__claim_gadget');
    }
}

?>