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

class Model_police_report extends CI_Model {

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

	public function getListPoliceReport($ID = null, $user_id = null)
	{
        $query = $this->db->select('a.*, b.name as dept_name, c.name as  branchName, d.ID as fileID, d.sv_policeReport_id, d.file_title, d.file_location, e.name as requestor, e.staff_id')
                ->join('company__department b', 'b.id = a.department_id')
				->join('company__branch c', 'c.id = b.branch_id')
                ->join('sv__police_report__upload d', 'd.sv_policeReport_id = a.ID', 'LEFT')
                ->join('user e', 'e.id = a.user_id', 'left')
                ->where('a.is_delete',0)
                //->order_by('a.ID', 'ASC');
                ->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);
        }
        
        $query = $this->db->get('sv__police_report a')
				->result_array();
		//die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

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

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

	public function getPoliceReportDetails($ID)
	{
		$query = $this->db->select('a.*, e.*, a.department_id, b.name as dept_name, e.ID as uploadID, e.sv_policeReport_id, e.file_title, e.file_location, f.name as requestor')
				->join('company__department b', 'b.id = a.department_id')
				//->join('company__branch c', 'c.id = b.branch_id')
				->join('sv__police_report__upload e', 'e.sv_policeReport_id = a.ID AND e.is_delete = 0', 'LEFT')
				->join('user f', 'f.id = a.user_id', 'left')
				->where('a.ID', $ID)
				->where('a.is_delete',0)
                //->order_by('a.ID', 'ASC')
                ->order_by('a.ID desc')
				->get('sv__police_report a')
				->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

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