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

class Model_car_booking extends CI_Model {

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

	public function getCarRequestList($ID = null, $user_id=null, $car_id = null, $year = null)
	{
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.car_name as car_type_name, d.color_code, e.name as userName, e.email as userEmail, e.staff_id, e.email, f.name as approvedByName')
				->select('g.name as created_name, g.email as created_email, h.id as created_department_id, h.name as created_dept_name, i.name as created_branchName')
                ->select('(select status from sv__approval where table_name="sv__car_booking" 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__car_booking" 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__car_booking__type d', 'd.id = a.car_id', 'LEFT')
				->join('user e', 'e.id = a.user_id')
				->join('user f', 'f.id = a.approvedBy', 'LEFT')
				->join('user g', 'g.id = a.createdBY', 'LEFT')
				->join('company__department h', 'h.id = (select department_id from user__employment_detail where id = g.id)', 'LEFT')
				->join('company__branch i', 'i.id = h.branch_id', 'LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.ID desc');

        if($ID > 0)
        {
            $this->db->where('a.ID',$ID);
        }
                
        if(!empty($user_id))
		{
			$this->db->where('(a.user_id = '.$user_id.' OR a.createdBY = '.$user_id.')');
			// $this->db->where('a.user_id',$user_id);
			// $this->db->or_where('a.createdBY',$user_id);
		}

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

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

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

    public function getBookingCarByWeek()
	{
		$query = $this->db->select('a.*, b.*')
				->join('sv__car_booking__type b', 'b.id = a.car_id', 'left')
				->where('DATE(a.start_date) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__car_booking a')
				->result_array();
				// debug($this->db->last_query());
		return (!empty($query))?$query:null;
	}
    
    public function getCarBookingDate($carType=null,$dateApplied=null,$timeApplied=null, $wheres = array(), $wheres_or = array())
	{
		if($carType)
		{
			$this->db->where('a.car_id',$carType);
		}
		if($dateApplied)
		{
			$this->db->where('"'.$dateApplied.'" BETWEEN a.start_date AND a.end_date');
        }
        if($timeApplied)
		{
			$this->db->where('"'.$timeApplied.'" BETWEEN a.start_time AND a.end_time');
        }

        if($wheres) foreach($wheres as $key => $where) if($where) $this->db->where($where, null, false);
		if($wheres_or) foreach($wheres_or as $key => $where_or) if($where_or) $this->db->or_where($where_or);

		$query = $this->db->select('a.*')
			->where('(a.user_id != '.$_SESSION['user']['id'].' AND a.createdBY != '.$_SESSION['user']['id'].')')
			->where('a.is_delete',0)
			->order_by('a.ID desc')
			->get('sv__car_booking a')
			->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getCarBookingType2($year)
    {
        $query = $this->db->select('a.*, count(b.car_id) as total_booking')
        				->where('a.is_delete',0)
        				->join('sv__car_booking b','b.car_id = a.ID AND b.is_delete = 0 AND YEAR(b.createdTS) = '.$year.'','LEFT')
        				->group_by('a.ID')
        				->get('sv__car_booking__type a');

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

	public function getCarListForm($ID = null)
	{
		if($ID) $this->db->where('a.ID', $ID);


		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, b.shortName as department_short_name, d.car_name as car_type_name, e.name as requestor, e.staff_id, f.name as approvedByName')
				->select('g.name as created_name, g.email as created_email, h.id as created_department_id, h.name as created_dept_name, i.name as created_branchName')
				->join('company__department b', 'b.id = a.department_id', 'LEFT')
				->join('company__branch c', 'c.id = b.branch_id', 'LEFT')
				->join('sv__car_booking__type d', 'd.id = a.car_id', 'LEFT')
				->join('user e', 'e.id = a.user_id', 'LEFT')
				->join('user f', 'f.id = a.approvedBy', 'LEFT')
				->join('user g', 'g.id = a.createdBY', 'LEFT')
				->join('company__department h', 'h.id = (select department_id from user__employment_detail where id = g.id)', 'LEFT')
				->join('company__branch i', 'i.id = h.branch_id', 'LEFT')
				->where('a.is_delete',0)
				->get('sv__car_booking a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function update_carBooking($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__car_booking');
		return $this->db->affected_rows();
	}

	public function getAdminList()
	{
		$query = $this->db->select('a.access_lvl, a.module_id, b.id as userID, b.name')
				->join('user__employment_detail_new b','b.id = a.user_id', 'LEFT')
				->join('module c', 'c.id = a.module_id', 'LEFT')
				->where('a.access_lvl', 'admin')
				->where('a.module_id', 4)
				->where('a.is_delete', 0)
				->get('user__module_access a')
				->result_array();

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

	public function getCarList($id = null)
	{
		if($id) $this->db->where('a.ID',$id);
		$query = $this->db->where('a.is_delete', 0)
				->get('sv__car_booking__type a')
				->result_array();

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