IDCOL/api/application/models/Alarm_model.php

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

    protected $table = 'alarm_info_log';

    public function __construct() {
        parent::__construct();
    }
    // 告警信息列表
    public function getList($filter,$role,$user_id,$page = null,$limit = null,$type = 0){
    	
    	$sql	    = '';
    	$sqlSel     = "";
    	$implode = array();
    	
    	if (isset($filter['id']) && !empty($filter['id'])) {
    		$implode[] = "A.id='" . intval($filter['id']) . "'";
    	}
    	
        // 告警状态筛选
        if (isset($filter['status']) && is_numeric($filter['status'])) {
            $implode[] = "A.status='" . $filter['status'] . "'";
            if ($filter['status'] == 0) {
                $implode[] = "L.isfaulted=1";
            }else{
                $implode[] = "L.isfaulted=0";
            }
        }

    	if (isset($filter['lampid']) && is_numeric($filter['lampid'])) {
    		$implode[] = "A.lampid='" . $filter['lampid'] . "'";
    	}
    	
        // 告警类型筛选
    	if (isset($filter['alarmtype']) && !empty($filter['alarmtype'])) {
    		$implode[] = "A.stralarmtype like '%" . $filter['alarmtype'] . "%'";
    	}
    	
    	if (isset($filter['projectid']) && is_numeric($filter['projectid'])) {
    		$implode[] = "L.projectid='" . $filter['projectid'] . "'";
    	}
        if (isset($filter['section']) && !empty($filter['section'])) {
            $implode[] = "L.section='" . $filter['section'] . "'";
        }
        // 关键字搜索
        if (isset($filter['keyword']) && !empty($filter['keyword'])) {
            $implode[] = "(L.number LIKE '%{$filter['keyword']}%' or P.projectname LIKE '%{$filter['keyword']}%')";
        }

        // 时间筛选
        if (!empty($filter['startDate']) && !empty($filter['endDate'])) {
            $implode[] = "A.updatetime >= '{$filter['startDate']}'";
            $implode[] = "A.updatetime <= '{$filter['endDate']}'";
        }
	    
    	$this->load->model('User_model');
        $user_info = $this->User_model->get_user_by_id($user_id);
        $companyid = $user_info['companyid'];       
        $projectIds = $this->get_projectid_by_role($role,$user_id,$companyid);

        if(empty($projectIds)){
            $implode[] = "L.projectid in (0)";
        }else{
            $implode[] = "L.projectid in ({$projectIds})";
        }
    	
        
        $field = 'A.id as id,
                 A.lampid AS lampid,
                 A.alarmtype AS alarmtype,
                 A.stralarmtype AS stralarmtype,
                 A.status AS status,
                 L.number AS number,
                 P.projectname AS project,
                 N.networkname AS network,
                 L.address,
                 L.isfaulted,
                 L.protocoltype as lampprotocoltype';

        if (isset($filter['type']) && $filter['type'] == 0){
            $field .= ',max(A.updatetime) AS updatetime';
            $implode[] = "A.updatetime = T.time";
            $where = $implode ? " WHERE " . implode(" AND ", $implode) : '';
            $query = "SELECT {$field} 
                FROM alarm_info_log AS A 
                LEFT JOIN lampinfo AS L ON A.lampid = L.id 
                LEFT JOIN network AS N ON N.id = L.networkid 
                LEFT JOIN project AS P ON P.id = L.projectid 
                LEFT JOIN (select max(updatetime) as time,lampid FROM alarm_info_log group by lampid) as T on A.lampid = T.lampid 
                ".$where;
            $query .= " group by A.lampid";
        }else{
            $field .= ',A.updatetime AS updatetime';
            $where = $implode ? " WHERE " . implode(" AND ", $implode) : '';
            $query = "SELECT {$field}
                FROM alarm_info_log AS A 
                LEFT JOIN lampinfo AS L ON A.lampid = L.id 
                LEFT JOIN network AS N ON N.id = L.networkid 
                LEFT JOIN project AS P ON P.id = L.projectid ".$where;
            
        }

        $query .= " ORDER BY updatetime desc";
    	$limit_sql = "";
        if (empty($type) && $limit < 1000) {
            if ($page != null && $limit != null){
                $limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
            }    
        }else{
            if (isset($page) && isset($limit)){
                $limit_sql = " LIMIT ".$page.",".$limit;
            }
        }
    	
    	
    	$query .= $limit_sql;
        // echo $query;;die;
    	$query  = $this->db->query($query);

    	return  $query->result_array();

    }

    // 获取故障信息
    public function get_fault_info($lampid){
        $field = "P.projectname,L.number,AI.stralarmtype,AI.id as alarmid,AI.updatetime,AI.status,RU.id as userid";
        $sql = "select {$field} FROM 
                lampinfo as L LEFT join {$this->table} as AI on L.id=AI.lampid 
                LEFT join project as P on P.id=L.projectid 
                LEFT join repair_dispatch as RD on RD.lampid=L.id  
                LEFT join repair_user as RU on RU.id=RD.repair_userid 
                where L.id={$lampid} order by AI.updatetime desc limit 1";
        return $this->db->query($sql)->row_array();
    }

    public function get_list_by_filter($filter,$page = null,$limit = null) {
    	
    	if (isset($filter['keyword']) || isset($filter['s_type'])){
    	
	    		$project_ids_where_in = "";
	    		if ($this->session->userdata('role') == COMPANY_CUSTOMER) {
	    			$this->load->model('User_model');
	    			$user_project = $this->User_model->get_user_zone();
	    			$project_ids_where_in = " AND L.projectid IN({$user_project}) ";
	    		} else if ($this->session->userdata('role') == COMPANY_ADMIN) {
                    $id = $this->session->userdata('id');
                    $this->load->model('User_model');
                    $user_info = $this->User_model->get_user_by_id($id);
                    $companyid = $user_info['companyid'];
                    if ($companyid) {
                        $this->load->model('Project_model');
                        $project_ids = $this->Project_model->get_project_ids(array('company'=>$companyid));
                        $user_project = implode(',', $project_ids);
                        if ($user_project) {
                            $project_ids_where_in = " AND L.projectid IN({$user_project}) ";
                        } else {
                            $project_ids_where_in = " AND L.projectid in (0)";
                        }
                    } else {
                        $project_ids_where_in = " AND L.projectid in (0)";
                    }
                }
	    	
	    		if (in_array($filter['s_type'], array('project'))){
	    			$keywork_name_like    = " P.projectname LIKE '%{$filter['keyword']}%' ";
	    		}elseif (in_array($filter['s_type'], array('network'))){
	    			$keywork_name_like    = " N.networkname LIKE '%{$filter['keyword']}%' ";
	    		}elseif (in_array($filter['s_type'], array('lamp'))){
	    			$keywork_name_like    = " L.number LIKE '%{$filter['keyword']}%' ";
	    		}
	    		 
	    		$sql = "SELECT A.id as id,
	    		A.lampid AS lampid,
	    		A.alarmtype AS alarmtype,
	    		A.stralarmtype AS stralarmtype,
	    		A.status AS status,
	    		A.updatetime AS updatetime,
	    		L.number AS number,
	    		P.projectname AS project,
	    		N.networkname AS network
	    		FROM alarm_info_log AS A
	    		LEFT JOIN lampinfo AS L ON A.lampid = L.id
	    		LEFT JOIN network AS N ON N.id = L.networkid
	    		LEFT JOIN project AS P ON P.id = L.projectid
	    		WHERE {$keywork_name_like}  {$project_ids_where_in}  ORDER BY A.updatetime desc";
	    	
	    		$limit_sql = "";
	    		if ($page != null && $limit != null){
	    		$limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
	    	
	    		$sql .= $limit_sql;
	    		
	    
	    		$query  = $this->db->query($sql);
	    		return  $query->result_array();
    		}
    	}
        if (isset($filter['network'])){

           $status_where = "";
           if (isset($filter['status']) && in_array($filter['status'], array(0,1))){
           	  $status_where = " AND A.status=".$filter['status']." ";
           }
           $sql = "SELECT A.id as id,
                         A.lampid AS lampid,
                         A.alarmtype AS alarmtype,
                         A.stralarmtype AS stralarmtype,
                         A.status AS status,
                         A.updatetime AS updatetime,
                         L.number AS number,
                         P.projectname AS project,
                         N.networkname AS network
                FROM alarm_info_log AS A 
                LEFT JOIN lampinfo AS L ON A.lampid = L.id 
                LEFT JOIN network AS N ON N.id = L.networkid 
                LEFT JOIN project AS P ON P.id = L.projectid 
                WHERE L.networkid = {$filter['network']} ".$status_where."ORDER BY A.updatetime desc";
         
            $limit_sql = "";
            if ($page != null && $limit != null){
                $limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
            }
             
            $sql .= $limit_sql;
            $query = $this->db->query($sql);
            return $query->result_array();
        }

        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));
            }
        } else if ($this->session->userdata('role') == COMPANY_ADMIN) {
            $id = $this->session->userdata('id');
            $this->load->model('User_model');
            $user_info = $this->User_model->get_user_by_id($id);
            $companyid = $user_info['companyid'];
            if ($companyid) {
                $this->load->model('Project_model');
                $project_ids = $this->Project_model->get_project_ids(array('company'=>$companyid));
                $project_ids = empty($project_ids) ? array(0) : $project_ids;
                $this->db->where_in('id', $project_ids);
            } else {
                $this->db->where_in('id', array(0));
            }
        }


        $status_where = "";
        if (!empty($filter)){
            
            if (isset($filter['status']) && in_array($filter['status'], array(0,1))){
                $status_where = " AND A.status=".$filter['status']." ";
                unset($filter['status']);
            }
            
            foreach ($filter as $k => $v) {
                $this->db->where($k,$v);
            }
        }

    	$this->db->select('*');
    	$this->db->order_by("id","DESC");
    	
    	$query = $this->db->get('project');
    	$project = $query->result_array();
    	if (empty($project)){
    		return array();
    	}
    	 
    	$Ids = array();
    	$projectIds = array();
    	foreach ($project as $v){
    		$Ids[] = $v['id'];
    		$projectIds[$v['id']] = $v['projectid'];
    	}
    	
        $condition = "L.projectid in (".implode(',', $Ids).")";

    	
    	$sql = "SELECT A.id as id,
    					 A.lampid AS lampid,
    					 A.alarmtype AS alarmtype,
    					 A.stralarmtype AS stralarmtype,
    					 A.status AS status,
    					 A.updatetime AS updatetime,
    					 L.number AS number,
                         P.projectname AS project,
                         N.networkname AS network
                FROM alarm_info_log AS A 
                LEFT JOIN lampinfo AS L ON A.lampid = L.id 
                LEFT JOIN network AS N ON N.id = L.networkid 
                LEFT JOIN project AS P ON P.id = L.projectid 
                WHERE {$condition} {$status_where} ORDER BY A.updatetime desc";
    	 
    	$limit_sql = "";
    	if ($page != null && $limit != null){
    		$limit_sql = " LIMIT ".($page-1)*$limit.",".$limit;
    	}
    	 
    	$sql .= $limit_sql;
    	$query  = $this->db->query($sql);
    	return  $query->result_array();
    }
    
    public function get_list_by_projectid($projectid,$filter=array(),$field='*'){
        $this->db->select($field);
        if (!empty($filter['group'])) {
            $this->db->group_by($filter['group']);
            unset($filter['group']);
        }
        if (!empty($filter)) {
            foreach ($filter as $key => $value) {
                $this->db->where($key,$value);
            }
        }
        if (!empty($projectid)) {
            $this->db->where('L.projectid',$projectid);
        }
        $this->db->join('lampinfo as L','on L.id = AI.lampid','LEFT');
        $this->db->order_by('AI.updatetime','desc');
        return $this->db->get($this->table.' as AI')->result_array();
    }

    public function getTotal($filter){
    	
    	if (isset($filter['keyword']) || isset($filter['s_type'])){
    		 
    		$project_ids_where_in = "";
    		if ($this->session->userdata('role') == COMPANY_CUSTOMER) {
    			$this->load->model('User_model');
    			$user_project = $this->User_model->get_user_zone();
    			$project_ids_where_in = " AND L.projectid IN({$user_project}) ";
    		} else if ($this->session->userdata('role') == COMPANY_ADMIN) {
                    $id = $this->session->userdata('id');
                    $this->load->model('User_model');
                    $user_info = $this->User_model->get_user_by_id($id);
                    $companyid = $user_info['companyid'];
                    if ($companyid) {
                        $this->load->model('Project_model');
                        $project_ids = $this->Project_model->get_project_ids(array('company'=>$companyid));
                        $user_project = implode(',', $project_ids);
                        if ($user_project) {
                            $project_ids_where_in = " AND L.projectid IN({$user_project}) ";
                        } else {
                            $project_ids_where_in = " AND L.projectid in (0)";
                        }
                    } else {
                        $project_ids_where_in = " AND L.projectid in (0)";
                    }
                }
    	
    		if (in_array($filter['s_type'], array('project'))){
    			$keywork_name_like    = " P.projectname LIKE '%{$filter['keyword']}%' ";
    		}elseif (in_array($filter['s_type'], array('network'))){
    			$keywork_name_like    = " N.networkname LIKE '%{$filter['keyword']}%' ";
    		}elseif (in_array($filter['s_type'], array('lamp'))){
    			$keywork_name_like    = " L.number LIKE '%{$filter['keyword']}%' ";
    		}
    	
    		$sql = "SELECT count(A.id) as total 
    		FROM alarm_info_log AS A
    		LEFT JOIN lampinfo AS L ON A.lampid = L.id
    		LEFT JOIN network AS N ON N.id = L.networkid
    		LEFT JOIN project AS P ON P.id = L.projectid
    		WHERE {$keywork_name_like}  {$project_ids_where_in}  ORDER BY A.updatetime desc";

    		$query  = $this->db->query($sql);
    		$arr   = $query->row_array();
            return $arr['total'];
    	}
	
        if (isset($filter['network'])){
        	$status_where = "";
        	if (isset($filter['status']) && in_array($filter['status'], array(0,1))){
        		$status_where = " AND A.status=".$filter['status']." ";
        		unset($filter['status']);
        	}
        	
            $sql = "SELECT count(A.id) as total 
                FROM alarm_info_log AS A LEFT JOIN lampinfo AS L
                ON A.lampid = L.id WHERE L.networkid = {$filter['network']}".$status_where;
            $query = $this->db->query($sql);
            $arr   = $query->row_array();
            return $arr['total'];
        }

        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));
            }
        } else if ($this->session->userdata('role') == COMPANY_ADMIN) {
            $id = $this->session->userdata('id');
            $this->load->model('User_model');
            $user_info = $this->User_model->get_user_by_id($id);
            $companyid = $user_info['companyid'];
            if ($companyid) {
                $this->load->model('Project_model');
                $project_ids = $this->Project_model->get_project_ids(array('company'=>$companyid));
                $project_ids = empty($project_ids) ? array(0) : $project_ids;                
                $this->db->where_in('id', $project_ids);
            } else {
                $this->db->where_in('id', array(0));
            }
        }

        $status_where = "";
        if (!empty($filter)){
            if (isset($filter['status']) && in_array($filter['status'], array(0,1))){
                $status_where = " AND A.status=".$filter['status']." ";
                unset($filter['status']);
            }
            foreach ($filter as $k => $v) {
                $this->db->where($k,$v);
            }
        }

    	$this->db->select('*');
    	$this->db->order_by("id","DESC");
    	
    	$query = $this->db->get('project');
    	$project = $query->result_array();
    	if (empty($project)){
    		return 0;
    	}
    	 
    	$Ids = array();
    	$projectIds = array();
    	foreach ($project as $v){
    		$Ids[] = $v['id'];
    		$projectIds[$v['id']] = $v['projectid'];
    	}
    	
        $condition = "L.projectid in (".implode(',', $Ids).")";

    	
    	$query = "SELECT count(A.id) as total 
    			FROM alarm_info_log AS A LEFT JOIN lampinfo AS L
    			ON A.lampid = L.id WHERE {$condition} {$status_where}";

    	$query  = $this->db->query($query);

    	$arr   =  $query->row_array();
    	return $arr['total'];
    }
    
    public function get_one_by_filter($filter=array(),$field="*"){
        $this->db->select($field);
        foreach ($filter as $key => $value) {
            $this->db->where($key,$value);
        }
        $this->db->order_by('updatetime','desc');
        $this->db->limit(1);
        $data = $this->db->get($this->table)->row_array();
        return $data;
    }

    public function getOne($id,$field=null){
        if (empty($field)) {
            $field = "P.projectname AS project_name,
                       N.networkid AS network_no,
                       L.number AS lamp_no,
                       L.section AS location,
                       A.stralarmtype AS alarm_event,
                       A.updatetime AS alarm_time,
                       NOW() AS network_time,
                       A.lampid AS lampid,
                       A.status AS status";
        }
        $sql = "SELECT {$field}
                FROM alarm_info_log AS A 
                LEFT JOIN lampinfo AS L ON A.lampid = L.id 
                LEFT JOIN network AS N ON N.id = L.networkid 
                LEFT JOIN project AS P ON P.id = L.projectid WHERE A.id = {$id} order by A.updatetime";
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    
    public function delData($condition) {
        if (!empty($condition)){
            foreach ($condition as $k => $v) {
                if (is_array($v)) {
                    if (!empty($v)) {
                        $this->db->where_in($k,$v);
                    }else{
                        $this->db->where_in($k,array(0));
                    }
                }else{
                    $this->db->where($k,$v);
                }
            }
        }else{
            return false;
        }
        $this->db->delete($this->table);
        if ($this->db->affected_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }
    
    public function delBatch($data, $field = 'id'){
        $this->db->where_in($field, $data);
        $this->db->delete($this->table);
        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function delDataByCondition($condition){
        $query = "DELETE FROM alarm_info_log WHERE lampid in (SELECT id FROM lampinfo WHERE ".key($condition)." = ".current($condition).")";
        $this->db->query($query);
        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

    public function delBatchByCondition($data, $field = 'id'){
        if (empty($data)) {
            return false;
        }

        $data = trim(implode(',', $data), ',');
        $query = "DELETE FROM alarm_info_log WHERE lampid in ( SELECT DISTINCT(id) FROM lampinfo WHERE {$field} in ({$data}) )";
        $this->db->query($query);
        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }

}