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

class Model_qr_code extends CI_Model {

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

	public function getQRCodeList($ID = null, $requester_id=null, $year = null)
	{
		$query = $this->db->select('a.*, e.name as requestor, c.shortName as department_short_name, c.id as department_id, c.name as dept_name, d.name as branchName, e.email as requestor_email, e.staff_id')
				->select('l.organizer as cpd_organizer, group_concat(b.category_id) as cpd_category_id, group_concat(m.name SEPARATOR ", ") as cpd_category')
				->select('h.name as created_name, h.email as created_email, i.department_id as created_department_id, j.name as created_dept_name, k.name as created_branchName')
				->select('(select status from sv__approval where table_name="sv__qr_code" 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__qr_code" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status_reason')
                ->join('sv__qr_code_category b','b.qr_code_id = a.ID and b.is_delete = 0','LEFT')
                ->join('company__department c', 'c.id = a.department_id', 'LEFT')
                ->join('company__branch d', 'd.id = c.branch_id')
                ->join('user e', 'e.id = a.requester_id','LEFT')
                ->join('user h', 'h.id = a.created_by','LEFT')
                ->join('user__employment_detail i', 'i.id = a.created_by', 'LEFT')
                ->join('company__department j', 'j.id = i.department_id', 'LEFT')
                ->join('company__branch k', 'k.id = j.branch_id')
                ->join('hr__cpd_organizer l', 'l.id = a.organizer_id','LEFT')
                ->join('hr__cpd_category m', 'm.id = b.category_id and m.is_delete = 0','LEFT')
				->where('a.is_delete',0)
				->group_by('a.ID')
				->order_by('a.ID desc');
                
        if($ID)
		{
			$this->db->where('a.ID',$ID);
        }
        
        if(!empty($requester_id))
		{
			$this->db->where('(a.requester_id = '.$requester_id.' OR a.created_by = '.$requester_id.')');
			// $this->db->where('a.requester_id',$requester_id);
   //          $this->db->or_where('a.created_by',$requester_id);
		}

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

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

	public function getQRCodeByWeek()
	{
		$query = $this->db->select('a.*, d.name, b.shortName, b.name as dept_name, c.name as branchName')
				->join('user d', 'd.id = a.requester_id', 'LEFT')
				->join('company__department b', 'b.id = a.department_id', 'left')
				->join('company__branch c', 'c.id = b.branch_id','left')
				->where('DATE(a.date) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__qr_code a')
				->result_array();
				
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

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

	public function updateOrCreateNew($qr_code_id,$category_id = array())
	{
		if(empty($category_id)) return false;
		
		$this->db->set('a.is_delete',1)
				->set('updated_by',$this->session->userdata('user')['id'])
				->set('updated_ts',date('Y-m-d H:i:s'))
				->where_not_in('a.category_id',$category_id)
				->where('a.qr_code_id',$qr_code_id)
				->update('sv__qr_code_category a');

		$organizer_stored = $this->db->select('a.*')
						->where('a.qr_code_id',$qr_code_id)
						->where('a.is_delete',0)
						->get('sv__qr_code_category a')
						->result_array();

		if(!empty($organizer_stored))
		{
			$batch_insert = array();
			foreach ($category_id as $org) 
			{
				$bool = false;
				
				foreach ($organizer_stored as $key => $orgs) 
				{
					if($orgs['category_id'] == $org)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'qr_code_id'   		=> $qr_code_id,
						'category_id'      	=> $org,
						'is_delete'     	=> 0,
						'created_by'   		=> $this->session->userdata('user')['id']
					);
					array_push($batch_insert,$temp);
				}

			}
			if($batch_insert)
			{
				$this->db->insert_batch('sv__qr_code_category',$batch_insert);return true;
			}else{
				return true;
			}				

		} else {
			$batch_insert = array();
			foreach ($category_id as $org) 
			{
				$temp = array
				(
					'qr_code_id'		=> $qr_code_id,
					'category_id'      	=> $org,
					'is_delete'     	=> 0,
					'created_by'   		=> $this->session->userdata('user')['id']
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__qr_code_category',$batch_insert);return true;
		}
	}

	public function getRequestByDepartment($year = null)
	{
		$query = $this->db->select('a.name as dept_name, a.shortName, b.name as branchName, count(d.id) as total_request')
							->join('company__branch b','b.id = a.branch_id','LEFT')
							->join('user__employment_detail c','c.department_id = a.id','LEFT')
							->join('sv__qr_code d','d.requester_id = c.id AND d.is_delete = 0 and YEAR(d.created_ts) = '.$year.'','LEFT')
							->where('a.is_delete',0)
							->group_by('a.id')
							->get('company__department a');

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


?>