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

class Model_zoom_booking extends CI_Model {

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

	public function getZoomRequestList($ID = null, $requester_id=null, $year = null)
	{
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, h.shortName as department_short_name, c.name as branchName, d.account, d.username, d.password, d.meeting_user, d.webinar_user, d.remark, d.short_name, d.color_code')
				->select('e.name as requester_name, e.email as requester_email, e.staff_id as requester_staff_id')
				->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_branch_name')
                ->select('(select status from sv__approval where table_name="sv__zoom_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__zoom_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__ref_zoom_account d', 'd.id = a.account_id', 'LEFT')
				->join('user e', 'e.id = a.requester_id')
				->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($requester_id))
		{
			$this->db->where('(a.requester_id = '.$requester_id.' OR a.createdBY = '.$requester_id.')');
		}

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

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

    public function getZoomBookingByYear($year)
	{
		$query = $this->db->select('a.*, count(b.ID) as total_booking')
				->join('sv__zoom_booking b', 'a.id = b.account_id and b.is_delete = 0 and YEAR(b.createdTS) = '.$year, 'left outer')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->group_by('a.ID')
				->get('sv__ref_zoom_account a')
				->result_array();
				// debug($this->db->last_query());
		return (!empty($query))?$query:null;
	}

	public function getZoomBookingByDay()
	{
		$query = $this->db->select('a.*, count(a.ID) as total_booking, b.account')
				->join('sv__ref_zoom_account b', 'b.id = a.account_id and b.is_delete = 0', 'left outer')
				->where('a.is_delete',0)
				->where('DAY(a.start_date)',date('d'))
				->order_by('a.ID asc')
				->group_by('a.ID')
				->get('sv__zoom_booking a')
				->result_array();
				// debug($this->db->last_query());
		return (!empty($query))?$query:null;
	}

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

	public function getZoomAccountList($id = null)
	{
		$this->load->model('admin/model_user');
		$filter1 = array
		(
			'a.id'			=> $_SESSION['user']['id'],
			'j.module_id'	=> 1
		);

		$module_access = $this->model_user->getUserByModuleAccess($filter1)[0];

		if($module_access['access_lvl'] == 'admin' || $_SESSION['user']['division_id'] == 4 || $_SESSION['user']['department_id'] == 8) $not_include = false;
		else $not_include = true;
		
		if($id) $this->db->where('a.ID',$id);
		if($not_include) $this->db->where_not_in('a.ID',array(4));
		$query = $this->db->where('a.is_delete', 0)
				->where('a.status', 0)
				->get('sv__ref_zoom_account a')
				->result_array();

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

	public function getZoomBookingDate($account_id = null, $wheres = array())
	{
		if($account_id) $this->db->where('account_id',$account_id);
        if($wheres) foreach($wheres as $key => $where) if($where) $this->db->where($where,null,false);
		
		// $this->db->_protect_identifiers=false;
		$query = $this->db->where('is_delete',0)->get('sv__zoom_booking')->result_array();

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