IDCOL/api/application/models/Lamp_model.php

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

	protected $table = 'lampinfo';

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

	public function queryData($where, $fields='*', $table='lampinfo', $order_by='updatetime desc') {
        $this->db->select($fields)->from($table)->where($where)->order_by($order_by)->limit(1);
        $query = $this->db->get();
        return $query->row_array();
    }
    public function get_list_not_in($where_in_field, $where_in,$fields='*',$filter=array()){
    	if(!empty($where_in_field) && !empty($where_in)){
            $this->db->where_not_in($where_in_field, $where_in);
        }
        return $this->get_list_in(null,null,$fields,$filter);
    }

    // 项目管理页路灯详情数据
    public function get_list_in($where_in_field, $where_in, $fields='*', $filter=array(), $lite = false) {
        $this->db->select($fields);
        if(!empty($where_in_field) && !empty($where_in)){
            if (is_array($where_in)) {
            	$this->db->where_in($where_in_field, $where_in);
            }else{
            	$this->db->where($where_in_field, $where_in);
            }
        }

		if(isset($filter['networkid']) && !empty($filter['networkid'])){
			$this->db->where('L.networkid', $filter['networkid']);
		}

		if(isset($filter['offline']) && !empty($filter['offline'])){
			$this->db->where('L.netstatus', 0);
		}
		if(isset($filter['online']) && !empty($filter['online'])){
			$this->db->where('L.netstatus', 1);
		}
		if(isset($filter['section']) && !empty($filter['section'])){
			$this->db->where('L.section', $filter['section']);
		}
		if(isset($filter['lampid']) && !empty($filter['lampid'])){
			$this->db->where('L.id', $filter['lampid']);
		}

		// if(isset($filter['status']) && is_numeric($filter['status'])){
		// 	$this->db->where('L.isfaulted', $filter['status']);
		// 	$this->db->where('AI.stralarmtype !=', '');
		// 	$this->db->where('AI.status', 0);
		// }
        if(isset($filter['lightStatus']) && is_numeric($filter['lightStatus'])){
			$this->db->where('L.status', $filter['lightStatus']);
		}

		if(isset($filter['controllerstatus']) && is_numeric($filter['controllerstatus'])){
			$this->db->group_start();
			$this->db->where('L.controllerstatus', $filter['controllerstatus']);
			$this->db->or_where('L.netstatus', 0);
			$this->db->group_end();
		}
        // 关键字搜索
		if (isset($filter['keyword']) && !empty($filter['keyword'])){
			// $this->db->like('L.number', $filter['keyword']);
			$this->db->group_start();
			$this->db->or_like('L.number', $filter['keyword']);
			$this->db->or_like('L.address', $filter['keyword']);
			$this->db->or_like('L.section', $filter['keyword']);
			$this->db->group_end();
		}
		if(isset($filter['projectid']) && !empty($filter['projectid'])){
			$this->db->where('L.projectid', $filter['projectid']);
		}
		if (isset($filter['lng_low']) && is_numeric($filter['lng_low']) && isset($filter['lng_high']) && is_numeric($filter['lng_high']) && $filter['lng_low'] > $filter['lng_high']) {

			$this->db->group_start();
			$this->db->group_start();
			$this->db->where('L.longitude >=', $filter['lng_low']);
			$this->db->where('L.longitude <=', 180);
			$this->db->group_end();
			$this->db->or_group_start();
			$this->db->where('L.longitude <=', $filter['lng_high']);
			$this->db->where('L.longitude >=', -180);
			$this->db->group_end();
			$this->db->group_end();
		}else{
			if(isset($filter['lng_low']) && is_numeric($filter['lng_low'])){
				$this->db->where('L.longitude >=', $filter['lng_low']);
			}
			if(isset($filter['lng_high']) && is_numeric($filter['lng_high'])){
				$this->db->where('L.longitude <=', $filter['lng_high']);
			}
		}
		
		if(isset($filter['lat_low']) && is_numeric($filter['lat_low'])){
			$this->db->where('L.latitude >=', $filter['lat_low']);
		}
		
		if(isset($filter['lat_high']) && is_numeric($filter['lat_high'])){
			$this->db->where('L.latitude <=', $filter['lat_high']);
		}
    
        $this->db->from($this->table.' as L');

        if ($lite != 1) {
        	// $fieldArr = explode(',',$fields);
        	// $temp = array();
        	// foreach ($fieldArr as $value) {
        	// 	$a = explode('.',$value)[0];
        	// 	if (!in_array($a, $temp)) {
        	// 		$temp[] = $a;
        	// 	}
        	// }
	        // $this->db->join('project as P', 'L.projectid = P.id', 'left');
	        // $this->db->join('network as N', 'L.networkid = N.id', 'left');
	        // $this->db->join('(select t1.* from lamp_info_log t1, (select lampid, max(updatetime) as maxtime from lamp_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as LI', 'L.id = LI.lampid', 'left');
	        // $this->db->join('(select t1.* from battery_info_log t1, (select lampid, max(updatetime) as maxtime from battery_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as BI', 'L.id = BI.lampid', 'left');
	        // $this->db->join('(select t1.* from history_info_log t1, (select lampid, max(updatetime) as maxtime from history_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as HI', 'L.id = HI.lampid', 'left');
	        // $this->db->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', 'L.id = AI.lampid', 'left');
	        // $this->db->join('(select t1.* from solar_info_log t1, (select lampid, max(updatetime) as maxtime from solar_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as SI', 'L.id = SI.lampid', 'left');
	        // $this->db->join('(select t1.* from electric_info_log t1, (select lampid, max(updatetime) as maxtime from electric_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as EI', 'L.id = EI.lampid', 'left');
	        // $this->db->join('(select t1.* from system_info_log t1, (select lampid, max(updatetime) as maxtime from system_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as SIL', 'L.id = SIL.lampid', 'left');
	        $this->db->join('project as P', 'L.projectid = P.id', 'left');
			// $this->db->join('network as N', 'L.networkid = N.id', 'left');
			// $this->db->join('msisdn as M', 'N.simid = M.imsi', 'left');
	        // $this->db->join('(select lampid,max(updatetime) as time from history_info_log group by lampid) as T', 'L.id = T.lampid', 'left');
	        // $this->db->join('lamp_info_log as LI', 'L.id = LI.lampid AND LI.lampid=T.lampid AND LI.updatetime = T.time', 'left');
	        // $this->db->join('battery_info_log as BI', 'L.id = BI.lampid AND BI.lampid=T.lampid AND BI.updatetime = T.time', 'left');
	        // $this->db->join('history_info_log as HI', 'L.id = HI.lampid AND HI.lampid=T.lampid AND HI.updatetime = T.time', 'left');
	        // $this->db->join('alarm_info_log as AI', 'L.id = AI.lampid AND AI.lampid=T.lampid AND AI.updatetime = T.time', 'left');
	        $this->db->join('(select t1.* from warning_info_log t1, (select lampid, max(updatetime) as maxtime from warning_info_log group by lampid) t2 where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI', 'L.id = AI.lampid', 'left');
	        // $this->db->join('solar_info_log as SI', 'L.id = SI.lampid AND SI.lampid=T.lampid AND SI.updatetime = T.time', 'left');
	        // $this->db->join('electric_info_log as EI', 'L.id = EI.lampid AND EI.lampid=T.lampid AND EI.updatetime = T.time', 'left');
	        // $this->db->join('system_info_log as SIL', 'L.id = SIL.lampid AND SIL.lampid=T.lampid AND SIL.updatetime = T.time', 'left');
	        $this->db->order_by("L.number ASC");
        }else{
        	$this->db->order_by("L.number ASC");
        }
        if(!empty($filter['page']) && !empty($filter['count'])){
        	$this->db->limit($filter['count'],($filter['page']-1)*$filter['count']);
		}

        $query = $this->db->get();
        // echo $this->db->last_query();die;
        return $query->result_array();
    }

    public function report_get_list_in($where_in_field, $where_in, $fields='*', $filter=array()) {
        $this->db->select($fields);
        if(!empty($where_in_field) && !empty($where_in)){
            if (is_array($where_in)) {
            	$this->db->where_in($where_in_field, $where_in);
            }else{
            	$this->db->where($where_in_field, $where_in);
            }
        }

		// if(isset($filter['networkid']) && !empty($filter['networkid'])){
		// 	$this->db->where('L.networkid', $filter['networkid']);
		// }
        
        if (isset($filter['keyword']) && !empty($filter['keyword'])){
			$this->db->like('L.number', $filter['keyword']);
		}
		if(isset($filter['projectid']) && !empty($filter['projectid'])){
			$this->db->where('L.projectid', $filter['projectid']);
		}
		if(isset($filter['section']) && !empty($filter['section'])){
			$this->db->where('L.section', $filter['section']);
		}
    
        $this->db->from($this->table.' as L');
        $this->db->join('project as P', 'L.projectid = P.id', 'left');
		// $this->db->join('network as N', 'L.networkid = N.id', 'left');
        // $this->db->join('(select lampid,max(updatetime) as updatetime from lamp_info_log group by lampid) as LI', 'L.id = LI.lampid', 'left');
        $this->db->order_by("L.number ASC");
        if(!empty($filter['page']) && !empty($filter['count'])){
        	$this->db->limit($filter['count'],($filter['page']-1)*$filter['count']);
		}
		
        $query = $this->db->get();
        return $query->result_array();
    }

    // 获取项目下路灯列表
    public function get_list_by_project($projectid,$fields = '*'){
    	if (empty($projectid)) {
    		return array();
    	}
    	$sql = "SELECT {$fields} from {$this->table} as L 
    			left join network as N on L.networkid = N.id 
    			where L.projectid = {$projectid}";

    	return $this->db->query($sql)->result_array();
    }

    // 项目管理页路灯数量
    public function get_total($where_in_field, $where_in, $fields='*', $filter=array()) {
        // $this->db->select($fields);
        $this->db->select('count(*) as total');
        if(!empty($where_in_field) && !empty($where_in)){
            if (is_array($where_in)) {
            	$this->db->where_in($where_in_field, $where_in);
            }else{
            	$this->db->where($where_in_field, $where_in);
            }
        }

		if(isset($filter['networkid']) && !empty($filter['networkid'])){
			$this->db->where('L.networkid', $filter['networkid']);
		}
		
		// if(isset($filter['alarmtype']) && !empty($filter['alarmtype'])){
		// 	$this->db->where('AI.alarmtype', $filter['alarmtype']);
		// }
		// if(isset($filter['status']) && is_numeric($filter['status'])){
		// 	$this->db->where('L.isfaulted', $filter['status']);
		// 	$this->db->where('AI.stralarmtype !=', '');
		// 	$this->db->where('AI.status', 0);
		// }
		if(isset($filter['section']) && !empty($filter['section'])){
			$this->db->where('L.section', $filter['section']);
		}
		if(isset($filter['offline']) && !empty($filter['offline'])){
			$this->db->where('L.netstatus', 0);
		}
        if(isset($filter['online']) && !empty($filter['online'])){
			$this->db->where('L.netstatus', 1);
		}
		if(isset($filter['controllerstatus']) && !empty($filter['controllerstatus'])){
			$this->db->group_start();
			$this->db->where('L.controllerstatus', 5);
			$this->db->or_where('L.netstatus', 0);
			$this->db->group_end();
		}
		if(isset($filter['lightStatus']) && is_numeric($filter['lightStatus'])){
			$this->db->group_start();
			$this->db->where('L.lighteness >', 0);
			$this->db->or_where('L.netstatus', 1);
			$this->db->group_end();
			// $this->db->where('L.status', $filter['lightStatus']);
		}
        // 关键字搜索
		if (uri_string() == 'report/home') { // 数据报表页
			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				// $this->db->group_start();
				$this->db->like('L.number', $filter['keyword']);
				// $this->db->group_end();
			}
		}else{   // 项目管理页
			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				$this->db->group_start();
				$this->db->like('L.number', $filter['keyword']);
				// $this->db->or_like('BI.updatetime', $filter['keyword']);
				$this->db->group_end();
			}
		}
		if(isset($filter['projectid']) && !empty($filter['projectid'])){
			$this->db->where('L.projectid', $filter['projectid']);
		}
		
		if (isset($filter['lng_low']) && is_numeric($filter['lng_low']) && isset($filter['lng_high']) && is_numeric($filter['lng_high']) && $filter['lng_low'] > $filter['lng_high']) {

			$this->db->group_start();
			$this->db->group_start();
			$this->db->where('L.longitude >=', $filter['lng_low']);
			$this->db->where('L.longitude <=', 180);
			$this->db->group_end();
			$this->db->or_group_start();
			$this->db->where('L.longitude <=', $filter['lng_high']);
			$this->db->where('L.longitude >=', -180);
			$this->db->group_end();
			$this->db->group_end();
		}else{
			if(isset($filter['lng_low']) && is_numeric($filter['lng_low'])){
				$this->db->where('L.longitude >=', $filter['lng_low']);
			}
			if(isset($filter['lng_high']) && is_numeric($filter['lng_high'])){
				$this->db->where('L.longitude <=', $filter['lng_high']);
			}
		}
		
		if(isset($filter['lat_low']) && is_numeric($filter['lat_low'])){
			$this->db->where('L.latitude >=', $filter['lat_low']);
		}
		
		if(isset($filter['lat_high']) && is_numeric($filter['lat_high'])){
			$this->db->where('L.latitude <=', $filter['lat_high']);
		}
    
        $this->db->from($this->table.' as L');
        // $this->db->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', 'L.id = AI.lampid', 'left');
        // $this->db->join('network as N', 'N.id = L.networkid', 'left');

        if(!empty($filter['page']) && !empty($filter['count'])){
        	$this->db->limit($filter['count'],($filter['page']-1)*$filter['count']);
		}

        // $this->db->group_by('L.id');
		// $this->db->order_by("L.number","ASC");
        $query = $this->db->get()->row_array();
        return $query['total'];
    }

    public function get_projectid_by_role($role,$userid,$companyid){
    	if ($role == SYSTEM_ADMIN) {
    		$list = $this->db->query('select id from project')->result_array();
    	}else {
    		$list = $this->db->query('select id from project where company = '.$companyid)->result_array();
    	}
    	return array_column($list, 'id');
    }

    // gis地图页路灯列表
    public function get_list_by_role($role, $companyid, $projectid = 0, $userid = 0,$fields='*',$filter=array(),$type = 0,$is_map = 0){
    	$this->db->select($fields, false);
    	$projectIds = array();
    	if ($projectid > 0) {
			$projectIds[] = $projectid;
			$this->db->where_in('L.projectid',$projectIds);
		} elseif ($role != SYSTEM_ADMIN) {
			$idStr = $this->get_projectid_by_role($role,$userid,$companyid);
			if (!empty($idStr)) {
				$projectIds = explode(',', $idStr);
			}else{
				$projectIds = array(0);
			}
			$this->db->where_in('L.projectid',$projectIds);
		}

		

		// 筛选带有监控的路灯
		// if (isset($filter['monitor']) && !empty($filter['monitor'])) {
		// 	$this->db->group_start();
		// 	$this->db->or_where('V.id >','0');
		// 	$this->db->group_end();	
		// 	unset($filter['monitor']);
		// 	$this->db->join('video_monitor as V', 'L.id = V.lampid', 'left');
		// }
		
		// 筛选带有监控的路灯
		if (isset($filter['keyword']) && !empty($filter['keyword'])) {
			$this->db->like('L.number',$filter['keyword']);
			unset($filter['keyword']);
		}

		// 筛选故障设备
		if (isset($filter['L.controllerstatus']) && !empty($filter['L.controllerstatus'])) {
			$this->db->like('L.controllerstatus',$filter['keyword']);
			$this->db->group_start();
			$this->db->where('L.controllerstatus', $filter['L.controllerstatus']);
			$this->db->or_where('L.netstatus', 0);
			$this->db->group_end();
			unset($filter['L.controllerstatus']);
		}

		// 经纬度筛选
		if (isset($filter['lng_low']) && is_numeric($filter['lng_low']) && isset($filter['lng_high']) && is_numeric($filter['lng_high']) && $filter['lng_low'] > $filter['lng_high']) {

			$this->db->group_start();
			$this->db->group_start();
			$this->db->where('L.longitude >=', $filter['lng_low']);
			$this->db->where('L.longitude <=', 180);
			$this->db->group_end();
			$this->db->or_group_start();
			$this->db->where('L.longitude <=', $filter['lng_high']);
			$this->db->where('L.longitude >=', -180);
			$this->db->group_end();
			$this->db->group_end();
			unset($filter['lng_high']);
			unset($filter['lng_low']);
		}else{
			if(isset($filter['lng_low']) && is_numeric($filter['lng_low'])){
				$this->db->where('L.longitude >=', $filter['lng_low']);
				unset($filter['lng_low']);
			}
			if(isset($filter['lng_high']) && is_numeric($filter['lng_high'])){
				$this->db->where('L.longitude <=', $filter['lng_high']);
				unset($filter['lng_high']);
			}
		}
		if(isset($filter['lat_low']) && is_numeric($filter['lat_low'])){
			$this->db->where('L.latitude >=', $filter['lat_low']);
			unset($filter['lat_low']);
		}
		if(isset($filter['lat_high']) && is_numeric($filter['lat_high'])){
			$this->db->where('L.latitude <=', $filter['lat_high']);
			unset($filter['lat_high']);
		}
		if(isset($filter['section']) && !empty($filter['section'])){
			$this->db->where('L.section', $filter['section']);
			unset($filter['section']);
		}

		if(empty($type) && !empty($filter['page']) && !empty($filter['count'])){
        	$this->db->limit($filter['count'],($filter['page']-1)*$filter['count']);
        	unset($filter['page']);
        	unset($filter['count']);
		}

		// 根据路灯状态来筛选
		foreach ($filter as $key => $value) {
			$this->db->where($key,$value);
		}
		$this->db->from($this->table.' as L');
		// $this->db->join('project as P', 'L.projectid = P.id', 'left');
        // $this->db->join('network as N', 'L.networkid = N.id', 'left');
        // $this->db->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', 'L.id = AI.lampid', 'left');
        
        $this->db->group_by('L.id');
        if ($is_map) {
        	$this->db->order_by("L.latitude DESC");
        }else{
        	$this->db->order_by("L.number ASC,L.id DESC");
        }
		
        $query = $this->db->get();
        if (empty($type)) {
        	return $query->result_array();
        }else{
        	return $query->num_rows();
        }
    }


	public function getTotalLampInfo($role, $companyid, $projectid = 0, $userid = 0,$type = 0){
		if ($projectid > 0) {
			$condition = "id = {$projectid}";
		} elseif ($role == SYSTEM_ADMIN) {
			$condition = "1=1";
		} else {
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			if (empty($projectIds)) {
				$condition = "id in (0)";
			}else{
				$condition = "id in ({$projectIds})";
			}
		}

		$toDate = date('Y-m-d H:i:s',time());
		$year = date('Y',time())-1;
		$fromDate = date("{$year}-m-d H:i:s",time());

		// $sql = "SELECT COUNT(*) AS num, SUM(boardpower) AS install, SUM(epower) AS power from
		// (SELECT 
		// L.boardpower as boardpower, 
		// L.totalgeneration AS epower 
		// FROM lampinfo AS L 
		// WHERE {$condition} GROUP BY L.id) Temp";
		// if (empty($type)) {
		// 	$sql = "SELECT SUM(lampcount) AS num, SUM(totalinstall) AS install, SUM(totalgeneration) AS power,SUM(totalconsumption) as totalconsumption,avg(online) as online,avg(light) as light
		// 	FROM project  
		// 	WHERE {$condition} ";
		// }else{
			if ($projectid > 0) {
				$condition = "projectid = {$projectid}";
			} elseif ($role == SYSTEM_ADMIN) {
				$condition = "1=1";
			} else {
				$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
				if (empty($projectIds)) {
					$condition = "projectid in (0)";
				}else{
					$condition = "projectid in ({$projectIds})";
				}
			}
			// var_dump($condition);
			$sql = "SELECT COUNT(*) AS num, SUM(boardpower) AS install, SUM(totalgeneration) AS power,SUM(totalconsumption) as totalconsumption FROM lampinfo WHERE {$condition}";
		// }
		$query = $this->db->query($sql);
		$arrRet = $query->row_array();
		// var_dump($arrRet);die;
		return $arrRet;
	}
	
	// 获取项目下面的故障数
    public function get_fault_count($filter, $role, $companyid, $userid,$projectIds=0){
        $Ids = empty($projectIds) ? 0 : $projectIds;

        $temp = array();
        $temp[] = "L.projectid in ({$Ids})";
        if (empty($filter['L.projectid'])) {
        	unset($filter['L.projectid']);
        }
        foreach ($filter as $key => $value) {
            if (is_array($value)) {
                if (!empty($value)) {
                    $temp[] = "{$key} in ({implode(',', $value)})";
                }else{
                	$temp[] = "{$key} in (0)";
                }
            }else{
                $temp[] = "{$key}={$value}";
            }
        }
        $temp[] = 'AI.status = 0';
        $temp[] = 'AI.stralarmtype != ""';
        $where = '';
        if (!empty($temp)) {
        	$where = 'where '.implode(' and ', $temp);
        }
        $sql = "select count(*) as total from (select L.id from lampinfo as L LEFT JOIN (select lampid,max(updatetime) as time from alarm_info_log group by lampid) as T on T.lampid = L.id left join alarm_info_log AS AI on AI.lampid = T.lampid and AI.updatetime = T.time {$where} group by L.id) as t";
        // $sql = "select count(*) as total from lampinfo as L {$where}";
        // echo $sql;
        $data = $this->db->query($sql)->row_array();
        return $data['total'];
    }

	public function getTotal($filter, $role, $companyid, $userid = 0){
		if(isset($filter['network'])){
			$this->db->select('count(id) as total');
			$this->db->where_in('networkid',array(intval($filter['network'])));

			if (isset($filter['keyword']) && !empty($filter['keyword'])){
				$lampIds = explode(",",$filter['keyword']);
				$arrIds     = array(); 
				foreach ($lampIds as $id){
					if(is_numeric($id)){
						$arrIds[] = intval(trim($id));
					}
				}
				if (!empty($arrIds)){
					$this->db->where_in('number', $arrIds);
				}
			}
			
			if(isset($filter['status']) && $filter['status'] == 1){
				$this->db->where('isfaulted',1);
			}

			$query = $this->db->get($this->table);
			$arr   =  $query->row_array();
			return $arr['total'];
		}

		if (isset($filter['ids']) && is_array($filter['ids'])) {
			$this->db->select('count(id) as total');
			$this->db->where_in('id', $filter['ids']);
			$query = $this->db->get($this->table);
			$arr   =  $query->row_array();
			return $arr['total'];
		}
	
		if (!empty($filter)){
			foreach ($filter as $k => $v) {
				if($k == 'status' || $k == 'keyword' || $k == 'project_name_like' || $k == 'network_name_like'){
					continue;
				}
				$this->db->where($k,$v);
			}
		}
		if (empty($filter['projectid'])) {
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
		
			$Ids = empty($projectIds) ? array(0) : explode(',', $projectIds);
	        $this->db->where_in('projectid',$Ids);
		}
		$this->db->select('count(id) as total');
		if (isset($filter['keyword']) && !empty($filter['keyword'])){
			$lampIds = explode(",",$filter['keyword']);
			$arrIds     = array(); 
			foreach ($lampIds as $id){
				if(is_numeric($id)){
					$arrIds[] = intval(trim($id));
				}
			}
			if (!empty($arrIds)){
				$this->db->where_in('number', $arrIds);
			}
		}
		if(isset($filter['status']) && $filter['status'] == 1){
			$this->db->where('isfaulted',1);
		}
		$query = $this->db->get($this->table);
		$arr   =  $query->row_array();
   
		return $arr['total'];

	}

	public function get_light_count_by_project($projectid){
		$this->db->select('count(id) as total');
		$this->db->where('projectid',$projectid);
		$this->db->where('status',1);

		$query = $this->db->get($this->table);
		$arr   =  $query->row_array();
   
		return $arr['total'];
	}
	public function get_online_count_by_project($projectid){
		$sql = "select count(*) as total from lampinfo as L left join network as N on N.id = L.networkid where N.status = 1 AND L.projectid = ".$projectid;

        $data = $this->db->query($sql)->row_array();
        return $data['total'];
	}
	public function get_lamp_count_by_project($projectid, $role, $companyid, $userid = 0){
		$this->db->select('projectid,count(*) as total');
		if (empty($projectid)) {
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			if (empty($projectIds)) {
	        	$this->db->where_in('projectid',array(0));
			}else{
				$Ids = explode(',', $projectIds);
	        	$this->db->where_in('projectid',$Ids);
			}
		}else{
			if (is_array($projectid)) {
				$this->db->where_in('projectid',$projectid);
			}else{
				$this->db->where('projectid',$projectid);
			}
		}
		return $this->db->group_by('projectid')->get($this->table)->result_array();
	}

	// 获取单个路灯数据
	public function getData($condition, $fields = '*') {
        $this->db->select($fields);
		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);
				}
			}
		}
		$query = $this->db->get($this->table);
		$data = $query->row_array();      
		return $data;
	}
	
	// 获取路灯详情
	public function getOne($id,$field="*"){
		// $sql = "SELECT {$field}, L.id as id
		// 	FROM 	  ( select * from lampinfo where id = {$id} limit 1 ) AS L
		// 	LEFT JOIN network as N on N.id = L.networkid 
		// 	LEFT JOIN ( select * from lamp_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS log ON L.id = log.lampid
		// 	LEFT JOIN ( select * from system_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS sylog ON L.id = sylog.lampid
		// 	LEFT JOIN ( select * from solar_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS solog ON L.id = solog.lampid
		// 	LEFT JOIN ( select * from battery_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS blog ON L.id = blog.lampid
		// 	LEFT JOIN ( select * from electric_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS elog ON L.id = elog.lampid
		// 	LEFT JOIN ( select * from history_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS hlog ON L.id = hlog.lampid
		// 	LEFT JOIN ( select * from alarm_info_log where lampid = {$id} order by updatetime desc limit 1 ) AS alog ON L.id = alog.lampid 
		// 	LEFT JOIN ( select * from battery_cmd where id = {$id} order by updatetime desc limit 1 ) AS BC ON L.id = BC.id
		// 	WHERE L.id = {$id} limit 1";
		$sql = "SELECT {$field}, L.id as id
			FROM 	  ( select * from lampinfo where id = {$id} limit 1 ) AS L
			-- LEFT JOIN ( select * from battery_cmd where id = {$id} order by updatetime desc limit 1 ) AS BC ON L.id = BC.id
			WHERE L.id = {$id} limit 1";		
		$query = $this->db->query($sql);
		return $query->row_array();
	}

	public function getOneLastInfo($filter){
		if (!empty($filter)){
			foreach ($filter as $k => $v) {
				$this->db->where($k,$v);
			}
		}
		$this->db->order_by("createtime","DESC");   
		$this->db->limit('1');
		$query = $this->db->get($this->table);
		return $query->row_array();
	}

	public function getOneForInfoLog($lampid, $table){
		$this->db->where('lampid',$lampid);
		$this->db->order_by("updatetime","DESC");   
		$this->db->limit('1');
		$query = $this->db->get($table);
		$ret = $query->result_array();
		$info =  ($ret)? $ret[0] : '';
		
		return $info;
	}

	// 获取历史信息的数量
	public function getCountForInfoLog($lampid, $table,$beginDate = '',$endData = ''){
		$this->db->select('count(*) as total');
		$this->db->where('lampid',$lampid);
		if (!empty($beginDate) && !empty($endData)) {
			$this->db->where('updatetime >=',$beginDate);
			$this->db->where('updatetime <=',$endData);
		}
		$query = $this->db->get($table);
		$ret = $query->row_array();    
		// var_dump($this->db->last_query());    
		return $ret['total'];
	}

	// 获取历史信息列表
	public function getListForInfoLog($lampid, $table, $offset = null, $limit = null,$beginDate = '',$endData = ''){
		if ($table == 'lamp_info_log') {
			$this->db->select('lamp_info_log.*,BI.daydischarmaxpow,BI.daychargemincurrent,L.number');
			$this->db->join('battery_info_log as BI','lamp_info_log.lampid = BI.lampid AND lamp_info_log.updatetime = BI.updatetime','left');
			$this->db->join('lampinfo as L','lamp_info_log.lampid = L.id','left');
		}elseif ($table == 'solar_info_log') {
			$this->db->select('solar_info_log.*,BI.daychargemaxpow,BI.daydischargemincurrent');
			$this->db->join('battery_info_log as BI','solar_info_log.lampid = BI.lampid AND solar_info_log.updatetime = BI.updatetime','left');
		}elseif($table == 'system_info_log'){
			$this->db->select('system_info_log.*,L.number');
			$this->db->join('lampinfo as L','system_info_log.lampid = L.id','left');
		}elseif($table == 'battery_info_log'){
			$this->db->select('battery_info_log.*,HI.overtimes,system_info_log.sysvoltage,system_info_log.syscurrent,system_info_log.temper');
			$this->db->join('history_info_log as HI','battery_info_log.lampid = HI.lampid AND battery_info_log.updatetime = HI.updatetime','left');
			$this->db->join('system_info_log','system_info_log.lampid = battery_info_log.lampid AND system_info_log.updatetime = battery_info_log.updatetime','left');
		}
		$this->db->where($table.'.lampid',$lampid);
		if (!empty($beginDate) && !empty($endData)) {
			$this->db->where($table.'.updatetime >=',$beginDate);
			$this->db->where($table.'.updatetime <=',$endData);
		}
		$this->db->order_by("updatetime","DESC"); 
		if ($limit !== null && $offset !== null) {
		  	$this->db->limit($limit, $offset);
		}
		$query = $this->db->get($table);
		$ret = $query->result_array();        
		return $ret;
	}

	// 获取最新历史信息
	public function getForInfoLog($lampid, $table){
		
		if ($table == 'lamp_info_log') {
			$this->db->where('id',$lampid);
			// $this->db->order_by("LI.updatetime","DESC");   
			$this->db->limit(1);
			$this->db->select('lighteness,status as lampstatus,lampvoltage,lampcurrent,lamppower,temper,updatetime');
			// $this->db->join('lampinfo as L','lamp_info_log.lampid = L.id','left');
			$query = $this->db->get('lampinfo');
			$ret = $query->row_array();        
			return $ret;
		}else{
			$this->db->where('lampid',$lampid);
			$this->db->order_by("updatetime","DESC");   
			$this->db->limit(1);
			$query = $this->db->get($table);
			$ret = $query->row_array();        
			return $ret;
		}

	}
	
	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 getBatch($ids){
		$this->db->where_in('id',$ids);
		$query = $this->db->get($this->table);
		$ret = $query->result_array();        
		if (empty($ret)){
			return array();
		}    
		return $ret;    
	}
	
	public function getDataCount($condition, $id = 0) {
		if (!empty($condition)){
			foreach ($condition as $k => $v) {
				$this->db->where($k,$v);
			}
		}
		if (!empty($id)) {
            $this->db->where('id !=',$id); 
        }  
		$query = $this->db->get($this->table);
		$data = $query->row_array();        
		if (empty($data)) {
			return 0;
		} else { 
			return $id == $data['id'] ? 0 : 1;
		}
	}
	
	public function getLampIds($filter){
		
		if (isset($filter['project']) && is_numeric($filter['project'])) {
			$query = "SELECT id FROM lampinfo WHERE projectid=".$filter['project'];
		}elseif (isset($filter['network']) && is_numeric($filter['network'])){
			$query = "SELECT id FROM lampinfo WHERE networkid=".$filter['network'];
		}elseif (isset($filter['lamp']) && is_numeric($filter['lamp'])){
			$query = "SELECT id FROM lampinfo WHERE id=".$filter['lamp'];
		}else {
			return array();
		}

		$query = $this->db->query($query);
		$data = $query->result_array();
		if (empty($data)) {
			return array();
		}
		
		$ids = array();
		foreach ($data as $k => $v){
			$ids[] = $v['id'];
		}
		
		return $ids;
	}
	
	public function getIdByLikeName($name){
		$sql   = "SELECT id FROM lampinfo WHERE number LIKE '%".$name."%'";
		$query = $this->db->query($sql);
		$arr   =  $query->row_array();
		if (!empty($arr)){
			return $arr['id'];
		}
		return false;
	
	}

	public function getCountByFilter($role, $companyid, $projectid = 0, $userid = 0, $filter = array()){
		if ($projectid > 0) {
			$condition = "L.projectid = {$projectid}";
		} elseif ($role == SYSTEM_ADMIN) {
			$condition = "1=1";
		} else {
			
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			if (empty($projectIds)) {
				$condition = "L.projectid in (0)";
			}else{
				$condition = "L.projectid in ({$projectIds})";
			}
			
		}

		if (!empty($filter)) {
			$andCondition = ' AND '.key($filter).' = '.current($filter);
		} else {
			$andCondition = '';
		}

		// $sql = "SELECT COUNT(L.id) as num
		// FROM lampinfo AS L 
		// LEFT JOIN (select lampid, max(updatetime) as maxtime from lamp_info_log group by lampid) t2 on L.id = t2.lampid 
		// LEFT JOIN lamp_info_log AS log ON log.lampid = t2.lampid and log.updatetime = t2.maxtime 
		// WHERE {$condition} {$andCondition}";
		$sql = "SELECT COUNT(L.id) as num
		FROM lampinfo AS L 
		WHERE {$condition} {$andCondition}";

		$query = $this->db->query($sql);
		$arrRet = $query->row_array();
		return $arrRet;
	}

	public function getOnlineCount($role, $companyid, $projectid = 0, $userid = 0, $nw_status = false){
		if ($projectid > 0) {
			$condition = "L.projectid = {$projectid}";
		} elseif ($role == SYSTEM_ADMIN) {
			$condition = "1=1";
		} else {
			$projectIds = $this->get_projectid_by_role($role,$userid,$companyid);
			if (empty($projectIds)) {
				$condition = "L.projectid in (0)";
			}else{
				$condition = "L.projectid in ({$projectIds})";
			}
		}

		if ($nw_status !== false) {
			$andCondition = 'AND L.netstatus = '.$nw_status;
		} else {
			$andCondition = '';
		}

		$sql = "SELECT COUNT(L.id) AS num FROM lampinfo as L WHERE {$condition} {$andCondition}";
		$query = $this->db->query($sql);
		$arrRet = $query->row_array();
		return $arrRet;
	}

	public function get_project_company($condition,$field){
		$this->db->select($field);
        if (!empty($condition)) {
            foreach ($condition as $key => $value) {
                if (is_array($value)) {
                    if (!empty($value)) {
                        $this->db->where_in($key,$value);
                    }else{
                        $this->db->where_in($key,array(0));
                    }
                }else{
                    $this->db->where($key,$value);
                }
                
            }
        }else{
            return array();
        }

        $this->db->join('project as P','P.id = L.projectid','left');
        // $this->db->join('network as N','N.id = L.networkid','left');

        return $this->db->get($this->table.' as L')->row_array();
    }

	public function markNormal($ids){
		$ids = implode(',', $ids);
		$sql = "UPDATE lampinfo set isfaulted = 0 WHERE id IN ($ids)";
		$query = $this->db->query($sql);
		if ($this->db->affected_rows() > 0) {
			return true;
		} else {
			return false;
		}
	}

	// 地图页获取路段均值数据
	public function get_section_avg($project,$section){
		$sql = "SELECT avg(daychargemincurrent) as daychargemincurrent,avg(daydischargemincurrent) as daydischargemincurrent,avg(totalgeneration) as totalgeneration,avg(totalconsumption) as totalconsumption,avg(electricleft) as electricleft,avg(battvoltage) as battvoltage from lampinfo WHERE projectid = {$project} AND section = '{$section}' group by section";
		return $this->db->query($sql)->row_array();
	}
}