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

class Model_capex_request extends CI_Model {

	function __construct()
	{
		parent::__construct();
	}

	public function getCapexRequestList($ID = null, $requester_id=null)
	{
		$this->db->select('a.*')
			->select('b.shortName as department_short_name, b.name as dept_name, c.name as branchName, g.name as requester_name, g.email as requester_email, g.staff_id as requester_staff_id')
			->select('h.name as created_name, h.email as created_email, i.id as created_department_id, i.name as created_dept_name, j.name as created_branch_name')
			->select('group_concat(f.category) as category, group_concat(d.item_id) as item_id, group_concat(e.item) as item, group_concat(d.quantity) as quantity')
			->select('(select status from sv__approval where table_name="sv__capex_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_app')
			->select('(select reason from sv__approval where table_name="sv__capex_request" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
			->join('company__department b','b.id = a.department_id','left')
			->join('company__branch c','c.id = b.branch_id','left')
			->join('sv__capex_item_detail d','d.capex_id = a.ID and d.is_delete = 0','left')
			->join('sv__ref_capex_item e','e.id = d.item_id','left')
			->join('sv__ref_capex_category f','f.id = e.category_id','left')
			->join('user g','g.id = a.requester_id','left')
			->join('user h','h.id = a.createdBY','left')
			->join('company__department i', 'i.id = (select department_id from user__employment_detail where id = g.id)', 'LEFT')
			->join('company__branch j', 'j.id = i.branch_id', 'LEFT')
			->where('a.is_delete',0)
			->group_by('a.ID')
			->order_by('a.ID desc');

        if($ID > 0)
        {
            $this->db->where('a.ID',$ID);
        }
                
        if(!empty($requester_id))
		{
			$this->db->where('a.requester_id',$requester_id);
		}

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

		// debug($query);
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
    }

    public function getCapexItemDetail($capex_id = null)
	{
		$query = $this->db->get('sv__ref_capex_category')->result_array();
		$i=0;
		if ($query) foreach($query as $row)
		{
			$query[$i]['item'] = $this->db->select('a.*, b.*, b.ID as capex_detail_id, c.*, a.item')
				->join('sv__capex_item_detail b','b.item_id = a.ID AND b.is_delete=0','left outer')
				->join('sv__capex_request c','b.capex_id = c.ID','left outer')
				->where('a.category_id',$row['ID'])
                ->where('a.is_delete',0)
                ->where('c.ID', $capex_id)
				->order_by('a.category_id asc, a.ID')
				->get('sv__ref_capex_item a')
				->result_array(); 
			$i++;
		}
		return $query; 	
				// debug($this->db->last_query());
		return (!empty($query))?$query:null;
	}

	public function getCapexCategoryItem($category_id = 0)
	{
		if($category_id >0) $this->db->where('a.ID',$category_id);
		
		$query =  $this->db->select('a.*, b.*')
						->join('sv__ref_capex_item b', 'b.category_id = a.id and b.is_delete = 0')
						->where('a.is_delete',0)
						->order_by('b.item')
						->get('sv__ref_capex_category a')
						->result_array(); 

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