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

class Model_maintenance extends CI_Model {

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

	public function getMaintenanceRequestList($ID = null, $user_id=null, $year = null)
	{

        if($ID > 0)
		{
			$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->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.ID as photoID, d.sv_maintenance_id, d.photo_title, d.file_location, e.name as userName, e.staff_id, e.email, f.name as approvedByName')
				->select('(select status from sv__approval where table_name="sv__maintenance" 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__maintenance" 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__maintenance__photo d', 'd.sv_maintenance_id = a.ID', 'LEFT')
				->join('user e', 'e.id = a.user_id')
				->join('user f', 'f.id = a.approvedBy', 'LEFT')
                ->where('a.is_delete',0)
                ->order_by('a.ID asc')
				->get('sv__maintenance a')
				->result_array();
		// debug($this->db->last_query());
		return (!empty($query))?$query:null;
	}

	public function getCountMaintenanceList($complain_category, $year)
	{
		$query = $this->db->select('complain_category, group_concat(ID), count(ID) as total_request')
				->where('a.complain_category',$complain_category)
				->where('a.is_delete',0)
				->where('year(a.createdTS)',$year)
				->get('sv__maintenance a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getMaintenanceByWeek()
	{
		$query = $this->db->select('a.*')
				->where('DATE(a.createdTS) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 WEEK)')
				->where('a.is_delete',0)
				->order_by('a.ID asc')
				->get('sv__maintenance a')
				->result_array();
				// echo $this->db->last_query();
		return (!empty($query))?$query:null;
	}

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

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

	public function getMaintenanceDetails($ID)
	{
		$this->db->order_by('a.ID desc');
		$query = $this->db->select('a.*, a.department_id, b.name as dept_name, c.name as branchName, d.ID as photoID, d.sv_maintenance_id, d.photo_title, d.file_location, e.name as requestor, e.staff_id, e.email, f.name as approvedByName')
                ->select('(select status from sv__approval where table_name="sv__maintenance" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as status')        
                ->select('(select reason from sv__approval where table_name="sv__maintenance" and id_table=a.ID and is_delete=0 order by ID desc limit 1) as reason')        
                ->join('company__department b', 'b.id = a.department_id')
				->join('company__branch c', 'c.id = b.branch_id')
				->join('sv__maintenance__photo d', 'd.sv_maintenance_id = a.ID','LEFT')
				->join('user e', 'e.id = a.user_id', 'left')
				->join('user f', 'f.id = a.approvedBy', 'left')
				->where('a.ID', $ID)
				->where('a.is_delete',0)
				->get('sv__maintenance a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function get_maintenance_file($where = array())
    {
		$this->db->select('*');
		
        if($where)
        {
        	foreach ($where as $column => $value) 
			{
				$this->db->where($column,$value);
			}
		}
		$maintenance_files = $this->db->get('sv__maintenance__photo')
                			->result_array();   
		return $maintenance_files;
    }

    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();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	// public function getMasterMaintenanceReq($ID)
	// {}
}
?>