IDCOL/api/application/models/Patrol_model.php

<?php
if (!defined('BASEPATH'))exit('No direct script access allowed');
include_once(FCPATH . 'application/models/Base_model.php');
class Patrol_model extends Base_model {

	protected $table = 'patrol_cmd';

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

    public function get_list_in($field, $array, $fields='*') {

    	// $sql = 
        
        if(!empty($field) && !empty($array)){
            // $this->db->where_in($field, $array);
            $s = implode(',', $array);
            $where = "{$field} in ({$s})";
        }else{
        	return array();
        }
    	// $this->db->select($fields);
    	$query = "select {$fields} FROM {$this->table} where {$where}";
        $data = $this->db->query($query);

        return $data->result_array();
    }
	
	public function queryData($start,$offset){
		$this->db->order_by("id","DESC");
		$query = $this->db->get($this->table);
		return $query->result_array();
	}
	
	public function getList($role,$companyid,$projectid,$userid,$filter,$field = '*',$page = null,$limit = null,$type = 0){
   
		$sql_in = array();
		// if (isset($filter['keyword']) && !empty($filter['keyword'])){               
		// 	$lampIds = explode(",",$filter['keyword']);
		// 	$arrIds     = array();
		// 	foreach ($lampIds as $id){
		// 		if(is_numeric($id) && !empty($id)){
		// 			$arrIds[] = trim($id);
		// 		}
		// 	}

		// 	if(!empty($arrIds)){
		// 		$sql_in_ids = implode(',',$arrIds);
		// 		$sql_in =" AND L.number IN (".$sql_in_ids.")";
		// 	}
		// }
    	if ($projectid > 0) {
    		$sql_in[] = "L.projectid = {$projectid}";
		} elseif ($role != SYSTEM_ADMIN) {
			$idStr = $this->get_projectid_by_role($role,$userid,$companyid);
			if (!empty($idStr)) {
				$sql_in[] = "L.projectid in ({$idStr})";
			}else{
				$sql_in[] = "L.projectid in (0)";
			}
		}

		if (isset($filter['keyword']) && !empty($filter['keyword'])){
			$sql_in[] ="(L.number like '%".$filter['keyword']."%' OR N.networkname like '%".$filter['keyword']."%' OR P.projectname like '%".$filter['keyword']."%')";
		}
		// var_dump($filter['status']);die;
		if(isset($filter['status']) && $filter['status'] == 1){
			$sql_in[] = "AI.status = 0 ";
		}
		if(isset($filter['section']) && !empty($filter['section'])){
			$sql_in[] = "L.section = '".$filter['section']."' ";
		}
		
		$sql_limit = "";
		if (empty($type) && $limit < 1000) {
			if(is_numeric($page) && is_numeric($limit)){
				$sql_limit = " LIMIT ".($page-1)*$limit.",".$limit;
			}
		}else{
			if(is_numeric($page) && is_numeric($limit)){
				$sql_limit = " LIMIT ".$page.",".$limit;
			}
		}
		
		$where = '';
		if (!empty($sql_in)) {
			$where = 'WHERE '.implode(' AND ', $sql_in);
		}
		$sql = "SELECT 
		{$field} FROM lampinfo AS L
		LEFT JOIN project AS P ON P.id = L.projectid 
		LEFT JOIN network AS N ON N.id = L.networkid
		LEFT JOIN (SELECT * FROM ".$this->table." ORDER BY updatetime DESC) AS cmd ON L.id = cmd.id 
		LEFT JOIN (select t1.* from alarm_info_log t1, (select lampid, max(updatetime) as maxtime from alarm_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI on AI.lampid = L.id   
		 {$where} order by L.number asc, L.id asc {$sql_limit}";
		
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	public function getTotal($role,$companyid,$projectid,$userid,$filter){
		$sql_in = array();
		// if (isset($filter['keyword']) && !empty($filter['keyword'])){               
		// 	$lampIds = explode(",",$filter['keyword']);
		// 	$arrIds     = array();
		// 	foreach ($lampIds as $id){
		// 		if(is_numeric($id) && !empty($id)){
		// 			$arrIds[] = trim($id);
		// 		}
		// 	}

		// 	if(!empty($arrIds)){
		// 		$sql_in_ids = implode(',',$arrIds);
		// 		$sql_in =" AND L.number IN (".$sql_in_ids.")";
		// 	}
		// }
    	if ($projectid > 0) {
    		$sql_in[] = "L.projectid = {$projectid}";
		} elseif ($role != SYSTEM_ADMIN) {
			$idStr = $this->get_projectid_by_role($role,$userid,$companyid);
			if (!empty($idStr)) {
				$sql_in[] = "L.projectid in ({$idStr})";
			}else{
				$sql_in[] = "L.projectid in (0)";
			}
		}

		if (isset($filter['keyword']) && !empty($filter['keyword'])){
			$sql_in[] ="L.number like '%".$filter['keyword']."%'";
			$sql_in[] ="N.networkname like '%".$filter['keyword']."%'";
			$sql_in[] ="P.projectname like '%".$filter['keyword']."%'";
		}
		if(isset($filter['status']) && $filter['status'] == 1){
			$sql_in[] = "AI.status = 0 ";
		}
		if(isset($filter['section']) && !empty($filter['section'])){
			$sql_in[] = "L.section = '".$filter['section']."' ";
		}
		// $sql_limit = "";
		// if(is_numeric($page) && is_numeric($limit)){
		// 	$sql_limit = " LIMIT ".($page-1)*$limit.",".$limit;
		// }
		
		$where = '';
		if (!empty($sql_in)) {
			$where = 'WHERE '.implode(' AND ', $sql_in);
		}
		$sql = "SELECT 
		count(L.id) as total FROM lampinfo AS L
		LEFT JOIN project AS P ON P.id = L.projectid 
		LEFT JOIN network AS N ON N.id = L.networkid
		LEFT JOIN (SELECT * FROM ".$this->table." ORDER BY updatetime DESC) AS cmd ON L.id = cmd.id 
		LEFT JOIN (select t1.* from alarm_info_log t1, (select lampid, max(updatetime) as maxtime from alarm_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI on AI.lampid = L.id   
		 {$where} order by L.number asc, L.id asc";
		// (select t1.* from alarm_info_log t1, (select lampid, max(updatetime) as maxtime from alarm_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI on AI.lampid = L.id 
		// (SELECT * FROM alarm_info_log as t1 JOIN (SELECT max(updatetime) as time,lampid FROM alarm_info_log GROUP BY lampid) as t2 on t1.lampid = t2.lampid AND t1.updatetime = t2.time) as AI on AI.lampid = L.id 
		$data = $this->db->query($sql)->row_array();
		return $data['total'];
	}
	
	// 通过角色获取巡检信息列表
	public function getPatrolInfoList($filter,$role,$userid,$companyid,$field = '*',$page = null,$limit = null,$projectid=0){
		if ($projectid > 0 ) {
			$projectIds = $projectid;
		}else{
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			$projectIds = empty($projectIds) ? '0' : $projectIds;
		}

		$sql = "SELECT {$field} 
			    FROM patrol_info_log AS PIL
				LEFT JOIN  network AS N ON PIL.networkid=N.id
				LEFT JOIN  project AS P ON N.projectid=P.id
				WHERE P.id IN(".$projectIds.")" ;
		
		if (isset($filter['keyword'])) {
			$sql .= " AND (N.networkname like '%{$filter['keyword']}%'";
			$sql .= " or PIL.patroltime like '%{$filter['keyword']}%'";
			$sql .= " or N.section like '%{$filter['keyword']}%')";
			unset($filter['keyword']);
		}

		$limit_sql = "";
		if ($page != null && $limit != null){
			$limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
		}
		
		$query  = $sql."  ORDER BY patroltime DESC  ".$limit_sql;
		$query  = $this->db->query($query);
		return  $query->result_array();
	}
	
	// 通过角色获取巡检信息数目
	public function getPatrolInfoTotal($filter,$role,$userid,$companyid,$projectid=0){
		if ($projectid > 0 ) {
			$projectIds = $projectid;
		}else{
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			$projectIds = empty($projectIds) ? '0' : $projectIds;
		}
		
		$sql = "SELECT COUNT(*) AS total
		FROM patrol_info_log as PIL 
		left join network as N on PIL.networkid = N.id 
		left join project as P on P.id = N.projectid 
		WHERE P.id IN('".$projectIds."')" ;
		
		if (isset($filter['keyword'])) {
			$sql .= " AND (N.networkname like '%{$filter['keyword']}%'";
			$sql .= " or PIL.patroltime like '%{$filter['keyword']}%'";
			$sql .= " or N.section like '%{$filter['keyword']}%')";
			unset($filter['keyword']);
		}
		$query  = $this->db->query($sql);
		$row    = $query->row_array();
		return $row['total'];
	}
	
	public function getNetworkIds($filter){
		
		foreach ($filter as $k => $v) {
			if(in_array($k, array('company','zone','province'))){
				$this->db->where($k,$v);
			}elseif ($k == 'projectid'){
				$this->db->where('id',$v);
			}elseif ($k == 'projectname'){
				$this->db->like('projectname', $v);
			}
		}
		
		if ($this->session->userdata('role') == COMPANY_ADMIN) {
			$companyid = $this->session->userdata('company_id');
			$this->db->where('company', $companyid);
		}
		
		if ($this->session->userdata('role') == COMPANY_CUSTOMER) {
			$this->load->model('User_model');
			$user_project = $this->User_model->get_user_zone();
			if ($user_project) {
				$projects = explode(',', $user_project);
				$this->db->where_in('id',$projects);
			} else {
				$this->db->where_in('id', array(0));
			}
		}
		
		$projectIds = array(0);
		$this->db->select('id');
		$query = $this->db->get('project');
		$project = $query->result_array();
		foreach ($project as $v){
			$projectIds[] = $v['id'];
		}
		
		// 查询networkIds
		$networkIds = array(0);
		
		if (isset($filter['networkid'])){
			$this->db->where('id',$filter['networkid']);
		}elseif (isset($filter['networkname'])){
			$this->db->like('networkname', $filter['networkname']);
		}
		
        $this->db->where_in('projectid', $projectIds);

		$this->db->select('id');
		$query = $this->db->get('network');
		$networks = $query->result_array();
		foreach ($networks as $v){
			$networkIds[] = $v['id'];
		}
		
		return $networkIds;
	}
}

?>