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

class Model_stationery extends CI_Model {

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

	public function getStationeryList_new($ID=null, $user_id=null, $year = null)
	{        
		$this->db->select('a.*,b.name as departmentName, c.name as branchName,h.name as staffName,h.staff_id, group_concat(d.quantity) as quantity, group_concat(e.description) as description, group_concat(f.package) as package, group_concat(g.category_name) as category_name')
			->select('(select status from sv__approval where table_name="sv__stationery" 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__stationery" 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')
			->join('company__branch c','c.id = b.branch_id')
			->join('sv__stationery__detail d','d.sv_stationery_id = a.ID AND d.is_delete = 0','left')
			->join('sv__stationery__category_detail e','e.id = d.sv_stationery_category_detail_id','left')
			->join('sv__stationery__category_package f','f.id = d.category_package_id','left')
			->join('sv__stationery__category g','g.id = f.package_group_id','left')
			->join('user h','h.id = a.user_id')
			// ->join('sv__approval i','i.table_name="sv__stationery" AND i.id_table = a.ID','left')
			->where('a.is_delete',0)
			->group_by('a.ID')
			->order_by('a.ID desc');
		if(!empty($ID))
		{
			$this->db->where('a.ID',$ID);
        }
        
        if(!empty($user_id))
		{
			$this->db->where('a.user_id',$user_id);
        }

        if(!empty($year))
		{
			$this->db->where('year(a.createdTS)',$year);
        }

		$query = $this->db->get('sv__stationery a');
		// debug($this->db->last_query());
		return (!empty($query))?$query->result_array():null;
	}

	public function getStationeryRequestList()
	{
		$query = $this->db->query('
				SELECT 
				`a`.*, 
				`a`.`department_id`, 
				`b`.`name` as `dept_name`, 
				`c`.`name` as `branchName`, 
				`e`.`description`,
				`f`.`package`,
				`g`.`quantity` 

				FROM `sv__stationery` `a` 
				LEFT JOIN `company__department` `b` ON `b`.`id` = `a`.`department_id` 
				LEFT JOIN `company__branch` `c` ON `c`.`id` = `b`.`branch_id` 
				LEFT JOIN `sv__stationery__detail` `g` ON `g`.`sv_stationery_id` = `a`.`id` 
				LEFT JOIN `sv__stationery__category_detail` `e` ON `e`.`category_id` = `g`.`sv_stationery_category_detail_id` 
				LEFT JOIN `sv__stationery__category_package` `f` ON `f`.`package_group_id` = `g`.`category_package_id` 

				WHERE `a`.`is_delete` = 0 ORDER BY `a`.`ID` desc
			');
		
		return (!empty($query))?$query->result_array():null;
	}

	public function getStationeryList()
	{
        $this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, b.name as departmentName, c.name as branchName')
					->join('company__department b', 'b.id = a.department_id')
					->join('company__branch c', 'c.id = b.branch_id')
					->where('a.is_delete',0)
					->get('sv__stationery a')
                    ->result_array();
                    //die(debug($this->db->last_query()));

		return $query;
	}

	public function getStationeryBookingRequestList()
	{
		$query = $this->db->select('a.*, b.name as departmentName, c.name as branchName, d.user_id, d.department_id, d.date_taken, e.category_id, e.description, f.package_group_id, f.package, g.category_name')
					->join('sv__stationery d', 'd.ID = a.sv_stationery_id')
					->join('company__department b', 'b.id = d.department_id')
					->join('company__branch c', 'c.id = b.branch_id')
					->join('sv__stationery__category_detail e', 'e.ID = a.sv_stationery_category_detail_id')
					->join('sv__stationery__category_package f', 'f.ID = a.category_package_id')
					->join('sv__stationery__category g', 'g.ID = f.package_group_id' )
					->where('a.is_delete',0)
					->get('sv__stationery__detail a')
					->result_array();
		//die(debug($this->db->last_query()));

		return $query;
	}
	
	public function getAllStationeryItem($ID = 0)
	{ 
		$rs = $this->db->get('sv__stationery__category')->result_array();
		$i=0;
		if ($rs) foreach($rs as $row){
			$rs[$i]['item'] = $this->db->select('a.*, c.*,c.ID as stationeryDetailID, d.*, a.description, e.*')
				->join('sv__stationery__detail c','a.ID = c.sv_stationery_category_detail_id AND c.is_delete=0','LEFT OUTER')
				// ->join('sv__stationery__category_package e', 'c.category_package_id = e.ID','LEFT OUTER')
				->join('sv__stationery d','d.ID = c.sv_stationery_id and d.ID='.$ID,'LEFT OUTER')
				->join('sv__stationery__category_package e', 'e.ID = c.category_package_id')
				->where('a.category_ID',$row['ID'])
                ->where('a.is_delete',0)
                ->where('c.sv_stationery_id', $ID)
				->order_by('a.description')
				->get('sv__stationery__category_detail a')
				->result_array(); 
			$i++;	//debug($this->db->last_query());
		} //die(debug($rs));
		return $rs; 	
	}

	// public function getStationeryDetail($ID = 0)
	// {
	// 	return $this->db->where('a.sv_stationery_id',$ID)
	// 			->select('a.*')
	// 			->where('a.is_delete',0)
	// 			->get('sv__stationery__detail a')
	// 			->result_array(); 	
	// }

	public function getStationeryItemList()
	{ 
		$rs = $this->db->get('sv__stationery__category')->result_array();
		$i=0;
		if ($rs) foreach($rs as $row){
			$rs[$i]['item'] = $this->db->select('a.*, c.*, a.description, e.*')
				->join('sv__stationery__detail c','a.ID = c.sv_stationery_category_detail_id','LEFT OUTER')
				// ->join('sv__stationery__category_package e', 'c.category_package_id = e.ID','LEFT OUTER')
				// ->join('sv__stationery d','d.ID = c.sv_stationery_id and d.ID='.$ID,'LEFT OUTER')
				->join('sv__stationery__category_package e', 'e.ID = c.category_package_id', 'left')
				->where('a.category_ID',$row['ID'])
				->where('a.is_delete',0)
				->order_by('a.description')
				->get('sv__stationery__category_detail a')
				->result_array(); 
			$i++;	
		}
		return $rs; 	
	}

	public function getStationeryDetails($ID=0)
	{ 
		return $this->db->where('a.ID',$ID)
				->select('a.*, b.name as dept_name, c.name as branchName, d.name as requesterName')
				->join('company__department b', 'b.id = a.department_id')
				->join('company__branch c', 'c.id = b.branch_id')
				->join('user d','d.id = a.user_id')
				->where('a.is_delete',0)
				->get('sv__stationery a')
				->result_array(); 		
	}

	public function getStationeryCategoryDetails($category_id = 0)
	{
		if($category_id >0) $this->db->where('a.ID',$category_id);
		
		return $this->db->select('a.*, b.*, a.category_name as category_name')
				->join('sv__stationery__category_detail b', 'a.id = b.category_id')
				->where('a.is_delete',0)
				->where('b.is_delete',0)
				->order_by('b.description')
				->get('sv__stationery__category a')
				->result_array(); 
	}

	public function getStationeryPackageDetails($category_id = 0, $mode = false)
	{
		if($category_id >0) $this->db->where('a.package_group_id',$category_id);
		if($mode == true)
		{
			$this->db->select('group_concat(a.package) as packages')->group_by('a.package_group_id');
		}	
		
		$query = $this->db->select('a.*')
				//->join('sv__stationery__category b', 'a.id = b.category_id')
				//->join('sv__stationery__category_package c' 'c.id = a.package_group_id')
				->where('a.is_delete',0)
				->order_by('a.ID')
				->get('sv__stationery__category_package a')
				->result_array();
				
		return $query;
	}

	
	public function update_stationery($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__stationery');
		return $this->db->affected_rows();
	}

	public function update_stationery_details($sets = array(), $where = array())
	{
		foreach ($sets as $key => $set) 
		{
			$this->db->set($key,$set);
		}
		foreach ($where as $key => $wh) 
		{
			$this->db->where($key,$wh);
		}
		$this->db->update('sv__stationery__detail');
		return $this->db->affected_rows();
	}

	public function getMasterStationeryReq($ID = 0)
	{
		$query = $this->db->select('a.*, b.name as requestor, b.department_id, c.name as dept_name')
				->where('a.ID', $ID)
				->join('user__employment_detail b', 'b.id = a.user_id')
				->join('company__department c', 'c.id = b.department_id')
				->get('sv__stationery a')
				->result_array();
// debug($this->db->last_query());
		return $query;
	}

	public function getStationeryBookingList($ID)
	{
		$query = $this->db->select('a.*, b.category_id, b.description, c.package_group_id, c.package')
				->where('a.ID', $ID)
				->join('sv__stationery__category_detail b', 'b.category_id = a.sv_stationery_category_detail_id', 'left')
				->join('sv__stationery__category_package c', 'c.package_group_id = a.category_package_id', 'left')
				->get('sv__stationery__detail a')
				->result_array();

				// die(debug($this->db->last_query()));
		return $query;
	}

	public function getStationeryItemDetail()
	{
		$query = $this->db->select('a.*, group_concat(b.id SEPARATOR "->") as description_id, group_concat(b.description SEPARATOR "->") as description, group_concat(b.price SEPARATOR "->") as description_price, count(b.ID) as total_item, (select group_concat(package) from sv__stationery__category_package where package_group_id = a.package_group_id and is_delete = 0) as package')
						->join('sv__stationery__category_detail b','b.category_id = a.ID and b.is_delete = 0','left')
						->where('a.is_delete',0)
						->group_by('a.ID')
						->order_by('a.ID asc, b.ID asc')
						->get('sv__stationery__category a');
		
		return (!empty($query))?$query->result_array():null;
	} 
}
?>