IDCOL/api/application/controllers/Home.php

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

include_once(FCPATH . 'application/controllers/Base_Controller.php');
class Home extends Base_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('Lamp_model');
        $this->load->model('Project_model');
        $this->load->model('Global_location_model');
    }

    private function get_project_id(){
        $cityId = intval($this->input->post('cityId',true));
        $proId = intval($this->input->post('proId',true));
        $projectIdArr = array();
        $areaList = array();
        $cityList = array();
        $proList = array();
        if (!empty($proId)) {
            $proList[] = array('id'=>$proId,'level'=>2);
            $list = $this->Global_location_model->get_list(['pid'=>$proId],'id,level');
            foreach ($list as $key => $value) {
                if ($value['level'] == 3) {
                    $cityList[] = $value;
                }elseif ($value['level'] == 4) {
                    $areaList[] = $value;
                }
            }
        }
        if (!empty($cityId)) {
            $areaList = array();
            $cityList = array(['id'=>$cityId,'level'=>3]);
        }
        if (!empty($cityList)){
            $cityId = array_unique(array_filter(array_column($cityList, 'id')));
            $list = $this->Global_location_model->get_list(['pid'=>$cityId],'id,level');
            foreach ($list as $key => $value) {
                if ($value['level'] == 4) $areaList[] = $value;
            }
        }
        $cityArr = array_unique(array_merge(array_column($proList, 'id'),array_column($cityList, 'id'),array_column($areaList, 'id')));

        if (!empty($cityArr)) {
            $res = $this->Project_model->get_list(['cityid'=>$cityArr],'id');
            $projectIdArr = array_unique(array_column($res, 'id'));
        }

        return $projectIdArr;
    }

    // 设备统计信息
    public function data(){
        $projectIdArr = $this->get_project_id();
    	$data = array(
    		'day_new_count' => 0,
    		'day_faulty_count' => 0,
    		'total_light_count' => 0,
    		'light_up_count' => 0,
    		'online_count' => 0,
    		'faulty_count' => 0
    	);
        $createTime = $this->get_user_info('createTime');
        $data['logCount'] = empty($createTime) ? 0 : ceil((time() - strtotime($createTime))/(3600 * 24));

        $date = date('Y-m-d H:i:s',strtotime(date('Y-m-d 00:00:00',time())) - 6*3600);

        $role = $this->get_user_info('role');
        if ($role == SYSTEM_ADMIN) {
            $where = [];
            if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;
            $data['total_light_count'] = $this->Lamp_model->get_count($where);
            $where = ['createtime >='=>$date];
            if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;

            $data['day_new_count'] = $this->Lamp_model->get_count($where);

            $where = ['netstatus'=>1,'status'=>1];
            if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;
            $data['light_up_count'] = $this->Lamp_model->get_count($where);

            $where = ['netstatus'=>1];
            if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;
            $data['online_count'] = $this->Lamp_model->get_count($where);

            // $where = ['netstatus'=>1,'devstatus !='=>0];
            // if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;
            // $data['faulty_count'] = $this->Lamp_model->get_count($where);

            // $where = ['netstatus'=>1,'logtime >='=>$date,'devstatus !='=>0];
            // if (!empty($projectIdArr)) $where['projectid'] = $projectIdArr;
            // $data['day_faulty_count'] = $this->Lamp_model->get_count($where);

            $where = ['L.netstatus ='=>1];
            if (!empty($projectIdArr)) $where['L.projectid'] = $projectIdArr;
            $where1 = [];
            foreach ($where as $key => $value) {
                if (is_array($value)) {
                    $where1[] = $key.' in ('.implode(',', $value).')';
                }else{
                    $where1[] = $key.' '.$value;
                }
            }
            $where1[] = '(WI.batstatus != 0 OR WI.panelstatus != 0 OR WI.lampstatus != 0 OR WI.tempstatus != 0)';
            $where1 = implode(' AND ', $where1);
            $join = [];
            $join[] = ['table'=>'(select w1.* from warning_info_log as w1 join (select lampid,max(updatetime) as maxTime from warning_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as WI','cond'=>'L.id = WI.lampid','type'=>'inner'];
            $total = $this->Lamp_model->get_list_by_multi_join($where1, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['faulty_count'] = $total['total'];

            $where1 .= ' AND WI.updatetime >= "'.$date.'"';
            $total = $this->Lamp_model->get_list_by_multi_join($where1, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['day_faulty_count'] = $total['total'];
        }else{
            $company = $this->get_user_info('company');
            $where = ['P.company ='=>$company];
            if (!empty($projectIdArr)) $where['P.id'] = $projectIdArr;
            $join = [
                ['table'=>'project as P','cond'=>'P.id = L.projectid','type'=>'inner']
            ];
            $total = $this->Lamp_model->get_list_by_multi_join($where, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['total_light_count'] = $total['total'];

            $where['L.createtime >='] = $date;
            $total = $this->Lamp_model->get_list_by_multi_join($where, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['day_new_count'] = $total['total'];
            unset($where['L.createtime >=']);

            $where['L.netstatus ='] = 1;
            $total = $this->Lamp_model->get_list_by_multi_join($where, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['online_count'] = $total['total'];

            $where['L.status ='] = 1;
            $total = $this->Lamp_model->get_list_by_multi_join($where, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['light_up_count'] = $total['total'];
            unset($where['L.status']);

            // $where['L.devstatus !='] = 0;
            $where1 = [];
            foreach ($where as $key => $value) {
                if (is_array($value)) {
                    $where1[] = $key.' in ('.implode(',', $value).')';
                }else {
                    $where1[] = $key.' '.$value;
                }
            }
            $where1[] = '(WI.batstatus != 0 OR WI.panelstatus != 0 OR WI.lampstatus != 0 OR WI.tempstatus != 0)';
            $where1 = implode(' AND ', $where1);
            $join[] = ['table'=>'(select w1.* from warning_info_log as w1 join (select lampid,max(updatetime) as maxTime from warning_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as WI','cond'=>'L.id = WI.lampid','type'=>'inner'];
            $total = $this->Lamp_model->get_list_by_multi_join($where1, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['faulty_count'] = $total['total'];

            // $where['L.devstatus !='] = 0;
            $where1 .= ' AND WI.updatetime >= "'.$date.'"';
            $total = $this->Lamp_model->get_list_by_multi_join($where1, 'count(*) as total',NULL, NULL, $join, NULL, NUll, 'L', true);
            $data['day_faulty_count'] = $total['total'];
        }

    	exit(json_result('0000',$this->response['0000'],$data));
    }

    // 发用电量统计信息
    public function sta_info(){
    	$data = array(
    		'electricity' => 0,
    		'CO2_reduction' => 0,
    		'SO2_reduction' => 0,
    		'TCE_reduction' => 0
    	);

        $type = intval($this->input->post('type',true));

        $projectIdArr = $this->get_project_id();
        $role = $this->get_user_info('role');
        $type = $this->input->post('type',true);
        $where = array();
        if ($role != SYSTEM_ADMIN){
            $company = $this->get_user_info('company');
            $where['P.company'] = $company;
        }

        if (!empty($projectIdArr)) $where['L.projectid'] = $projectIdArr;

        if ($type == 0) {
            $join = [];
            $join[] = ['table'=>'project as P','cond'=>'L.projectid = P.id','type'=>'left'];
            $join[] = ['table'=>'(select w1.* from realtime_info_log as w1 join (select lampid,max(updatetime) as maxTime from realtime_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as RI','cond'=>'L.id = RI.lampid','type'=>'inner'];
            $res = $this->Lamp_model->get_list_by_multi_join($where, 'sum(RI.dischargeday) as discharge,sum(RI.chargeday) as charge',NULL, NULL, $join, NULL, NUll, 'L', true);
        }elseif ($type == 1) {
            $join = [];
            $join[] = ['table'=>'project as P','cond'=>'L.projectid = P.id','type'=>'left'];
            $join[] = ['table'=>'(select w1.* from history_info_log as w1 join (select lampid,max(updatetime) as maxTime from history_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as RI','cond'=>'L.id = RI.lampid','type'=>'inner'];
            $res = $this->Lamp_model->get_list_by_multi_join($where, 'sum(RI.weekdischarg) as discharge,sum(RI.weekchargeah) as charge',NULL, NULL, $join, NULL, NUll, 'L', true);
        }elseif ($type == 2) {
            $join = [];
            $join[] = ['table'=>'project as P','cond'=>'L.projectid = P.id','type'=>'left'];
            $join[] = ['table'=>'(select w1.* from history_info_log as w1 join (select lampid,max(updatetime) as maxTime from history_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as RI','cond'=>'L.id = RI.lampid','type'=>'inner'];
            $res = $this->Lamp_model->get_list_by_multi_join($where, 'sum(RI.monthdischarge) as discharge,sum(RI.monthchargeah) as charge',NULL, NULL, $join, NULL, NUll, 'L', true);
        }else{
            $join = [];
            $join[] = ['table'=>'project as P','cond'=>'L.projectid = P.id','type'=>'left'];
            $join[] = ['table'=>'(select w1.* from history_info_log as w1 join (select lampid,max(updatetime) as maxTime from history_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as RI','cond'=>'L.id = RI.lampid','type'=>'inner'];
            $res = $this->Lamp_model->get_list_by_multi_join($where, 'sum(RI.totaldischarah) as discharge,sum(RI.totalchargeah) as charge',NULL, NULL, $join, NULL, NUll, 'L', true);
        }
        $data['electricity'] = round($res['discharge']/1000,1);
        // $data['CO2_reduction'] = $res['discharge'];
        // $data['SO2_reduction'] = $res['discharge'];
        // $data['TCE_reduction'] = $res['discharge'];
        $data['CO2_reduction'] = round($res['charge']*0.977/1000, 1);
        $data['SO2_reduction'] = round($res['charge']*0.977/2620*8.5, 1);
        $data['TCE_reduction'] = round($res['charge']*0.977/2620, 1);

    	exit(json_result('0000',$this->response['0000'],$data));
    }

    // 设备统计列表
    public function dev_list(){
        $projectIdArr = $this->get_project_id();
        $role = $this->get_user_info('role');
        $type = $this->input->post('type',true);
        $where = array();
        if ($role != SYSTEM_ADMIN){
            $company = $this->get_user_info('company');
            $where[] = 'P.company = '.$company;
        }

        if ($type == 2) {
            $fields = 'L.manu as company,G.id';
            $group = 'group by L.manu';
            $where[] = 'L.manu != 0';
            $where[] = 'L.manu != ""';
        }elseif ($type == 3) {
            $fields = 'L.supplier as company,G.id';
            $group = 'group by L.supplier';
            $where[] = 'L.supplier != 0';
            $where[] = 'L.supplier != ""';
        }elseif ($type == 4) {
            $fields = 'L.po as company,G.id';
            $group = 'group by L.po';
            $where[] = 'L.po != 0';
            $where[] = 'L.po != ""';
        }else{
            $fields = 'G.english_name as company,G.id';
            $group = 'group by G.id';
            // $where[] = 'L.upazilla != 0';
            // $where[] = 'L.upazilla != ""';
        }

        // $cityId = $this->input->post('cityId',true);
        // $proId = $this->input->post('proId',true);

        if (!empty($projectIdArr)) $where[] = 'L.projectid in ('.implode(',', $projectIdArr).')';
        $where1 = empty($where) ? '' : 'where '.implode(' AND ', $where);
        $query = 'select t1.total,C.name,C.no,C.id,t1.id as cityid,t1.company from (select count(*) as total,'.$fields.' from lampinfo AS L left join project AS P on P.id = L.projectid left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 left join company as C on t1.company = C.id order by total DESC limit 10';
        // var_dump($query);
        $list = $this->db->query($query)->result_array();
        // var_dump($list);die;
        if (empty($list)) exit(json_result('0000',$this->response['0000'],array('list'=>array())));
        $temp = array();
        $allDev = array_sum(array_column($list, 'total'));
        foreach ($list as $key => $value) {
            $totalPer = empty($allDev) ? '0%' : round($value['total']/$allDev*100,2).'%';
            $value['name'] = empty($value['no']) ? $value['name'] : $value['no'];
            $temp2 = array('name'=>$value['name'],'total'=>$value['total'],'onlineCount'=>0,'offlineCount'=>0,'faultCount'=>0,'totalPer'=>$totalPer);
            if ($type == 5) {
                $temp2['name'] = $value['company'];
                $temp[$value['cityid']] = $temp2;
            }else{
                $temp[$value['id']] = $temp2;
            }
            
        }
        $companyArr = array_unique(array_column($list, 'id'));
        $cityArr = array_unique(array_column($list, 'cityid'));
        if ($type == 2) {
            $where[] = 'L.manu in ('.implode(',', $companyArr).')';
        }elseif ($type == 3) {
            $where[] = 'L.supplier in ('.implode(',', $companyArr).')';
        }elseif ($type == 4) {
            $where[] = 'L.po in ('.implode(',', $companyArr).')';
        }else{
            $where[] = 'P.cityid in ('.implode(',', $cityArr).')';
        }

        $where1 = $where;
        $where1[] = 'L.netstatus = 0';
        $where1 = 'where '.implode(' AND ', $where1);
        if ($type == 5) {
            $query = 'select t1.total,t1.id from (select count(*) as total,'.$fields.' from lampinfo AS L left join project AS P on P.id = L.projectid left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 order by total DESC limit 10';
        }else{
            $query = 'select t1.total,C.id from (select count(*) as total,'.$fields.' from lampinfo AS L left join project AS P on P.id = L.projectid left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 left join company as C on t1.company = C.id order by total DESC limit 10';
        }
        $list = $this->db->query($query)->result_array();
        foreach ($list as $key => $value) {
            if (isset($temp[$value['id']])) $temp[$value['id']]['offlineCount'] = $value['total'];
        }

        $where1 = $where;
        $where1[] = 'L.netstatus = 1';
        $where1 = 'where '.implode(' AND ', $where1);
        if ($type == 5) {
            $query = 'select t1.total,t1.id from (select count(*) as total,'.$fields.' from lampinfo AS L left join project AS P on P.id = L.projectid left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 order by total DESC limit 10';
        }else{
            $query = 'select t1.total,C.id from (select count(*) as total,'.$fields.' from lampinfo AS L left join project AS P on P.id = L.projectid left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 left join company as C on t1.company = C.id order by total DESC limit 10';
        }
        
        $list = $this->db->query($query)->result_array();
        foreach ($list as $key => $value) {
            if (isset($temp[$value['id']])) $temp[$value['id']]['onlineCount'] = $value['total'];
        }

        $where1 = $where;
        $where1[] = 'L.netstatus = 1';
        $where1[] = '(WI.batstatus != 0 OR WI.panelstatus != 0 OR WI.lampstatus != 0 OR WI.tempstatus != 0)';
        $where1 = 'where '.implode(' AND ', $where1);
        if ($type == 5) {
            $query = 'select t1.total,t1.id from (select count(*) as total,'.$fields.' from lampinfo AS L join project AS P on P.id = L.projectid join (select w1.lampid,w1.batstatus,w1.panelstatus,w1.lampstatus,w1.tempstatus from warning_info_log as w1 join (select lampid,max(updatetime) as maxTime from warning_info_log group by lampid) as w2 on w1.updatetime = w2.maxTime AND w1.lampid = w2.lampid) as WI on WI.lampid = L.id left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 order by total DESC limit 10';
        }else{
            $query = 'select t1.total,C.id from (select count(*) as total,'.$fields.' from lampinfo AS L join project AS P on P.id = L.projectid join (select w1.lampid,w1.batstatus,w1.panelstatus,w1.lampstatus,w1.tempstatus from warning_info_log as w1 join (select lampid,max(updatetime) as maxTime from warning_info_log group by lampid) as w2 on w1.updatetime = w2.maxTime AND w1.lampid = w2.lampid) as WI on WI.lampid = L.id left join global_location G on P.cityid = G.id '.$where1.' '.$group.') as t1 left join company as C on t1.company = C.id order by total DESC limit 10';
        }
        
        $list = $this->db->query($query)->result_array();
        foreach ($list as $key => $value) {
            if (isset($temp[$value['id']])) $temp[$value['id']]['faultCount'] = $value['total'];
        }

        $list = array_values($temp);

    	exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // 故障信息列表
    public function alarm_list(){
        $projectIdArr = $this->get_project_id();

        $role = $this->get_user_info('role');
        $where = array();
        if ($role != SYSTEM_ADMIN){
            $company = $this->get_user_info('company');
            $where[] = 'P.company = '.$company;
        }

        if (!empty($projectIdArr)) $where[] = 'P.id in ('.implode(',', $projectIdArr).')';

        $where[] = '(WI.batstatus != 0 OR WI.panelstatus != 0 OR WI.lampstatus != 0 OR WI.tempstatus != 0)';
        $where[] = 'L.netstatus = 1';
        $where1 = implode(' AND ', $where);
        $join = [
            ['table'=>'project as P','cond'=>'P.id = L.projectid','type'=>'inner']
        ];
        $join[] = ['table'=>'(select w1.* from warning_info_log as w1 join (select lampid,max(updatetime) as maxTime from warning_info_log group by lampid) as w2 on w1.lampid = w2.lampid AND w1.updatetime = w2.maxTime) as WI','cond'=>'L.id = WI.lampid','type'=>'inner'];
        $join[] = ['table'=>'global_location as G1','cond'=>'G1.id = P.cityid','type'=>'inner'];
        $join[] = ['table'=>'global_location as G2','cond'=>'G2.id = G1.pid','type'=>'inner'];
        $join[] = ['table'=>'global_location as G3','cond'=>'G3.id = G2.pid','type'=>'inner'];
        $list = $this->Lamp_model->get_list_by_join($where1, 'P.projectname as project,WI.batstatus,WI.id,WI.panelstatus,WI.lampstatus,WI.tempstatus,P.cityid,L.address,L.section,G1.english_name as areaName,G2.english_name as cityName,G3.english_name as proName,WI.updatetime,G1.timezone',NULL, NULL, $join, NULL, NUll, 'L');

        $batstatus = $this->config->item('batstatus');
        $panelstatus = $this->config->item('panelstatus');
        $lampstatus = $this->config->item('lampstatus');
        $tempstatus = $this->config->item('tempstatus');

        foreach ($list as $key => $value) {
            $location = $value['proName'].'/'.$value['cityName'].'/'.$value['areaName'];
            $location = empty($value['section']) ? $location : $location.'/'.$value['section'];
            $list[$key]['location'] = $location;
            $list[$key]['fault_time'] = set_timezone($value['updatetime'],$value['timezone']);

            $temp2 = array();
            if (isset($batstatus[$value['batstatus']])) $temp2[] = $batstatus[$value['batstatus']];
            if (isset($panelstatus[$value['panelstatus']])) $temp2[] = $panelstatus[$value['panelstatus']];
            if (isset($lampstatus[$value['lampstatus']])) $temp2[] = $lampstatus[$value['lampstatus']];
            if (isset($tempstatus[$value['tempstatus']])) $temp2[] = $tempstatus[$value['tempstatus']];
            $list[$key]['fault_type'] = implode(',', $temp2);

            unset($list[$key]['cityName']);
            unset($list[$key]['proName']);
            unset($list[$key]['areaName']);
            unset($list[$key]['section']);
            unset($list[$key]['timezone']);
            unset($list[$key]['updatetime']);
        }

    	exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // 字段管理
    public function fields(){
        $userid = $this->get_user_info('id');
        $data = $this->User_model->get_user_field($userid);
        if (empty($data) || empty($data['lampfield'])) {
            // $fieldstr = "number,lampstatus,updatetime,lighteness,address,lamppower,chargestage,battvoltage,overtimes,solarpower,isfaulted,status,section";
            $fieldstr = "number,lampstatus,updatetime,lighteness,address,lamppower,chargestage,battvoltage,overtimes,solarpower,isfaulted,status,section";
        }else{
            if ($data['lampfield'] == 'number,lampstatus,updatetime,lighteness,address,lamppower,chargestage,battvoltage,overtimes,solarpower,isfaulted,status') {
                $data['lampfield'] .= ',section';
            }
            $fieldstr = $data['lampfield'];
        }

        // 系统默认
        $def = array(
            array('name'=>'路灯编号','field'=>'L.number','fields1'=>'number','enname'=>'Lamp number'),
            array('name'=>'路段','field'=>'L.section','fields1'=>'section','enname'=>'Road'),
            // array('name'=>'路灯状态','field'=>'L.status as lampstatus,L.lighteness','fields1'=>'lampstatus','enname'=>'Lamp status'),
            array('name'=>'信号状态','field'=>'L.protocoltype,L.netstatus as status,L.rssi','fields1'=>'status','enname'=>'Network status'),
            // array('name'=>'信号状态','field'=>'L.protocoltype,L.netstatus as status,N.rssi,N.snr','fields1'=>'status','enname'=>'Network status'),
            array('name'=>'更新时间','field'=>'RI.updatetime','fields1'=>'updatetime','enname'=>'Update time'),
            array('name'=>'路灯亮度(%)','field'=>'L.lighteness','fields1'=>'lighteness','enname'=>'Brightness(%)'),
            array('name'=>'无线模块地址','field'=>'L.address','fields1'=>'address','enname'=>'Wireless module address'),
            array('name'=>'负载功率(W)','field'=>'RI.loadpower','fields1'=>'loadpower','enname'=>'LED power(W)'),
            array('name'=>'蓄电池电压(V)','field'=>'RI.batvoltage as battvoltage','fields1'=>'battvoltage','enname'=>'Battery voltage(V)'),
            array('name'=>'蓄电池总过放次数','field'=>'HI.overtimes','fields1'=>'overtimes','enname'=>'Over discharge times'),
            array('name'=>'太阳能板功率(W)','field'=>'RI.panelpower as solarpower','fields1'=>'solarpower','enname'=>'Solar panel power(W)'),
            array('name'=>'是否故障','field'=>'AI.batstatus,AI.panelstatus,AI.lampstatus,AI.tempstatus,AI.status as alarmStatus','fields1'=>'isfaulted','enname'=>'Fault'),
        );

        // $def = array(
        //     array('name'=>'路灯编号','field'=>'L.number','fields1'=>'number','enname'=>'Lamp number'),
        //     array('name'=>'路段','field'=>'L.section','fields1'=>'section','enname'=>'Road'),
        //     array('name'=>'路灯状态','field'=>'L.status as lampstatus,L.lighteness','fields1'=>'lampstatus','enname'=>'Lamp status'),
        //     array('name'=>'信号状态','field'=>'L.protocoltype,L.netstatus as status','fields1'=>'status','enname'=>'Network status'),
        //     // array('name'=>'信号状态','field'=>'L.protocoltype,L.netstatus as status,N.rssi,N.snr','fields1'=>'status','enname'=>'Network status'),
        //     array('name'=>'更新时间','field'=>'L.logtime as updatetime','fields1'=>'updatetime','enname'=>'Update time'),
        //     array('name'=>'路灯亮度(%)','field'=>'L.lighteness','fields1'=>'lighteness','enname'=>'Brightness(%)'),
        //     array('name'=>'无线模块地址','field'=>'L.address','fields1'=>'address','enname'=>'Wireless module address'),
        //     array('name'=>'路灯功率(W)','field'=>'L.lamppower','fields1'=>'lamppower','enname'=>'Lamp power(W)'),
        //     array('name'=>'蓄电池充电阶段','field'=>'L.chargestage','fields1'=>'chargestage','enname'=>'Charging stage'),
        //     array('name'=>'蓄电池电压(V)','field'=>'L.battvoltage','fields1'=>'battvoltage','enname'=>'Battery voltage(V)'),
        //     array('name'=>'蓄电池总过放次数','field'=>'L.overtimes','fields1'=>'overtimes','enname'=>'Over discharge times'),
        //     array('name'=>'太阳能板功率(W)','field'=>'L.solarpower','fields1'=>'solarpower','enname'=>'Solar panel power(W)'),
        //     array('name'=>'是否故障','field'=>'L.isfaulted','fields1'=>'isfaulted','enname'=>'Fault'),
        // );

        // 路灯信息
        $lampinfo = array(
            array('name'=>'当前策略','field'=>'L.policyid','fields1'=>'policyid','enname'=>'Current strategy'),
            array('name'=>'路灯id','field'=>'L.id','fields1'=>'id','enname'=>'Lamp ID'),
            // array('name'=>'网络名称','field'=>'N.networkname','fields1'=>'networkname','enname'=>'Network name'),
            array('name'=>'项目名称','field'=>'P.projectname','fields1'=>'projectname','enname'=>'Project name'),
            array('name'=>'经度','field'=>'L.longitude','fields1'=>'longitude','enname'=>'Longitude'),
            array('name'=>'纬度','field'=>'L.latitude','fields1'=>'latitude','enname'=>'Latitude'),
            // array('name'=>'sim卡号','field'=>'N.simid','fields1'=>'simid','enname'=>'SIM card number'),
            // array('name'=>'套餐剩余','field'=>'N.packageSurplus','fields1'=>'packageSurplus','enname'=>'Package surplus'),
            array('name'=>'路灯类型','field'=>'L.lamptype','fields1'=>'lamptype','enname'=>'Lamp type'),
            array('name'=>'太阳能板类型','field'=>'L.boardtype','fields1'=>'boardtype','enname'=>'Solar panel type'),
            // array('name'=>'太阳能板功率(W)','field'=>'L.boardpower','fields1'=>'boardpower','enname'=>'Solar panel power(W)'),
            // array('name'=>'蓄电池类型','field'=>'L.batterytype','fields1'=>'batterytype','enname'=>'Battery type'),
            array('name'=>'蓄电池AH数(ah)','field'=>'HI.totalchargeah as batteryah','fields1'=>'batteryah','enname'=>'Battery capacity(ah)'),
            // array('name'=>'负载功率(W)','field'=>'L.loadpower','fields1'=>'loadpower','enname'=>'Load power(W)'),
            // array('name'=>'蓄电池剩余电量','field'=>'L.electricleft','fields1'=>'electricleft','enname'=>'Remaining battery capacity(Ah)'),
        );

        // 路灯信息日志
        $lamp_info_log = array(
            array('name'=>'路灯电压(V)','field'=>'RI.loadvoltage as lampvoltage','fields1'=>'lampvoltage','enname'=>'Lamp voltage(V)'),
            array('name'=>'路灯电流(A)','field'=>'RI.loadcurrent as lampcurrent','fields1'=>'lampcurrent','enname'=>'Lamp current(A)'),
            // array('name'=>'灯头温度(℃)','field'=>'L.lamptemper as temper','fields1'=>'temper','enname'=>'Lamp temperature(℃)'),
        );

        $solar_info_log = array(
            array('name'=>'太阳能板电压(V)','field'=>'RI.panelvoltage as solarvoltage','fields1'=>'solarvoltage','enname'=>'Solar panel voltage(V)'),
            array('name'=>'太阳能板电流(A)','field'=>'RI.panelcurrent as solarcurrent','fields1'=>'solarcurrent','enname'=>'Solar panel current(A)')
        );

        // 蓄电池信息日志
        $battery_info_log = array(
            array('name'=>'蓄电池状态','field'=>'L.battstatus','fields1'=>'battstatus','enname'=>'Battery status'),
            array('name'=>'蓄电池充电电流(A)','field'=>'L.chargecurrent','fields1'=>'chargecurrent','enname'=>'Charging current(A)'),
            array('name'=>'蓄电池放电电流(A)','field'=>'L.discharcurrent','fields1'=>'discharcurrent','enname'=>'Discharging current(A)'),
            array('name'=>'蓄电池充电功率(w)','field'=>'L.chargepower','fields1'=>'chargepower','enname'=>'Charging power(w)'),
            array('name'=>'蓄电池放电功率(w)','field'=>'L.dischargepower','fields1'=>'dischargepower','enname'=>'Discharging power(w)'),
            array('name'=>'蓄电池表面温度(℃)','field'=>'L.batttemper','fields1'=>'batttemper','enname'=>'Battery surface temperature(℃)'),
            array('name'=>'蓄电池总电量','field'=>'L.electrictotal','fields1'=>'electrictotal','enname'=>'Total battery capacity(Ah)'),
            array('name'=>'蓄电池电量SOC(%)','field'=>'L.electricSOC','fields1'=>'electricSOC','enname'=>'Battery SOC(%)'),
            array('name'=>'当天最低电压(V)','field'=>'L.voltagedaymin','fields1'=>'voltagedaymin','enname'=>'Minimum voltage(V)'),
            array('name'=>'当天最高电压(V)','field'=>'L.voltagedaymax','fields1'=>'voltagedaymax','enname'=>'Highest voltage(V)'),
            // array('name'=>'当天充电安时数(ah)','field'=>'L.daychargeah','fields1'=>'daychargeah','enname'=>'Charging AH(ah)'),
            // array('name'=>'当天放电安时数(ah)','field'=>'L.daydischarah','fields1'=>'daydischarah','enname'=>'Discharging AH(ah)'),
            array('name'=>'当天充电最大功率(W)','field'=>'L.daychargemaxpow','fields1'=>'daychargemaxpow','enname'=>'Charging max-power(W)'),
            array('name'=>'当天放电最大功率(W)','field'=>'L.daydischarmaxpow','fields1'=>'daydischarmaxpow','enname'=>'Discharging max-power(W)'),
            array('name'=>'当天灯亮时间(hh:mm)','field'=>'L.daychargemincurrent','fields1'=>'daychargemincurrent','enname'=>'Turn-on duration'),
            array('name'=>'当天充电最大电流(A)','field'=>'L.daycharmaxcurrent','fields1'=>'daycharmaxcurrent','enname'=>'Highest charging current(A)'),
            array('name'=>'当天充电时间(hh:mm)','field'=>'L.daydischargemincurrent','fields1'=>'daydischargemincurrent','enname'=>'Charging duration'),
            array('name'=>'当天放电最大电流(A)','field'=>'L.daydischarmaxcurrent','fields1'=>'daydischarmaxcurrent','enname'=>'Highest discharging current(A)'),
            array('name'=>'当天蓄电池最低温度(℃)','field'=>'L.daybattmintemper','fields1'=>'daybattmintemper','enname'=>'Battery min-temperature(℃)'),
            array('name'=>'当天蓄电池最高温度(℃)','field'=>'L.daybattmaxtemper','fields1'=>'daybattmaxtemper','enname'=>'Battery max-temperature(℃)'),
        );

        $electric_info_log = array(
            array('name'=>'当天发电量(kWh)','field'=>'RI.chargeday as daygeneration','fields1'=>'daygeneration','enname'=>'Power generation(kWh)'),
            array('name'=>'当天用电量(kWh)','field'=>'RI.dischargeday as dayconsumption','fields1'=>'dayconsumption','enname'=>'Power consumption(kWh)'),
            array('name'=>'累计发电量(kWh)','field'=>'HI.totalchargeah as totalgeneration','fields1'=>'totalgeneration','enname'=>'Cumulative power generation(kWh)'),
            array('name'=>'累计用电量(kWh)','field'=>'HI.totaldischarah as totalconsumption','fields1'=>'totalconsumption','enname'=>'Cumulative power consumption(kWh)'),
        );

        $system_info_log = array(
            array('name'=>'系统电压(V)','field'=>'L.sysvoltage','fields1'=>'sysvoltage','enname'=>'System voltage(V)'),
            array('name'=>'系统电流(A)','field'=>'L.syscurrent','fields1'=>'syscurrent','enname'=>'System current(A)'),
            array('name'=>'控制器温度(℃)','field'=>'L.temper as controlTemper','fields1'=>'controlTemper','enname'=>'Controller temperature(℃)'),
        );

        // 历史信息日志
        $history_info_log = array(
            array('name'=>'运行天数','field'=>'L.rundays','fields1'=>'rundays','enname'=>'Running duration'),
            array('name'=>'蓄电池总充满次数','field'=>'L.fulltimes','fields1'=>'fulltimes','enname'=>'Full charge times'),
            // array('name'=>'蓄电池总充电安时数(ah)','field'=>'L.totalchargeah','fields1'=>'totalchargeah','enname'=>'Total charge AH(ah)'),
            // array('name'=>'蓄电池总放电安时数(ah)','field'=>'L.totaldischarah','fields1'=>'totaldischarah','enname'=>'Total discharge AH(ah)'),
            array('name'=>'版本号','field'=>'L.softwareVersion','fields1'=>'softwareVersion','enname'=>'Version information'),
        );
        $fields = array_merge($def,$lampinfo,$lamp_info_log,$solar_info_log,$battery_info_log,$electric_info_log,$system_info_log,$history_info_log);
        // $fields = array_merge($def,$lampinfo,$lamp_info_log,$solar_info_log,$electric_info_log);
        // $fields = $def;
        $def = explode(',', $fieldstr);
        foreach ($fields as &$v) {
            if (in_array($v['fields1'], $def)) {
                $v['select'] = '1';
            }else{
                $v['select'] = '0';
            }
        }
        exit(json_result('0000',$this->response['0000'],array('list'=>$fields)));
    }

    // 路灯下拉列表
    public function lamp_list(){
        $networkid = intval($this->input->post('networkid',true));
        $projectid = intval($this->input->post('projectid',true));
        if (empty($networkid) && empty($projectid)) {
            exit(json_result('0405',$this->response['0405'],array()));
        }
        if (!empty($networkid)) {
            $data = $this->Lamp_model->get_list(array('networkid'=>$networkid), 'number,id');
        }else{
            $data = $this->Lamp_model->get_list(array('projectid'=>$projectid), 'number,id');
        }
        exit(json_result('0000',$this->response['0000'],$data));
    }

    // 获取单个路灯的经纬度
    public function get_lng_lat(){
        $lampid = $this->input->post('lampid',true);
        $projectid = $this->input->post('projectid',true);
        $section = $this->input->post('section',true);
        $lampData = array();
        if (!empty($lampid)) {
            $where = array('id'=>$lampid,'longitude !='=>0,'latitude !='=>0);
            if (!empty($section)) {
                $where['section'] = $section;
            }
            $lampData = $this->Lamp_model->get_one($where,'longitude,latitude');
        }
        if (!empty($projectid)) {
            $where = array('projectid'=>$projectid,'longitude !='=>0,'latitude !='=>0);
            if (!empty($section)) {
                $where['section'] = $section;
            }
            $lampData = $this->Lamp_model->get_one($where,'longitude,latitude');
        }

        if (empty($lampData)) {
            $lampData = array('longitude'=>0,'latitude'=>0);
        }
        exit(json_result('0000',$this->response['0000'],$lampData));
    }

    public function get_province(){
        // $countryId = intval($this->input->post('countryId',true));
        $role = $this->get_user_info('role');
        $where = array();
        if ($role != SYSTEM_ADMIN) {
            $company = $this->get_user_info('company');
            $where['company'] = $company;
        }

        $pro_list = $this->Project_model->get_list($where,'cityid');
        $cityArr = array_unique(array_column($pro_list, 'cityid'));
        $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name,level,pid');
        $areaList = array();
        $cityList = array();
        $proList = array();

        foreach ($list as $key => $value) {
            if ($value['level'] == 2) {
                $proList[] = $value;
            }elseif ($value['level'] == 3) {
                $cityList[] = $value;
            }elseif ($value['level'] == 4) {
                $areaList[] = $value;
            }
        }

        if (!empty($areaList)) {
            $cityArr = array_unique(array_column($areaList, 'pid'));
            $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name,level,pid');
            foreach ($list as $key => $value) {
                if ($value['level'] == 2) {
                    $proList[] = $value;
                }elseif ($value['level'] == 3) {
                    $cityList[] = $value;
                }
            }
        }


        if (!empty($cityList)) {
            $cityArr = array_unique(array_column($cityList, 'pid'));
            $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name,level,pid');
            foreach ($list as $key => $value) {
                if ($value['level'] == 2) $proList[] = $value;
            }
        }

        if (empty($proList)) exit(json_result('0000',$this->response['0000'],array('list'=>array())));

        $cityArr = array_unique(array_column($proList, 'id'));
        $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name',null,null,'convert(english_name using gbk) ASC,id DESC');

        exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }
    public function get_city(){
        $proId = intval($this->input->post('proId',true));
        $role = $this->get_user_info('role');
        $where = array();
        if ($role != SYSTEM_ADMIN) {
            $company = $this->get_user_info('company');
            $where['company'] = $company;
        }

        $pro_list = $this->Project_model->get_list($where,'cityid');
        $cityArr = array_unique(array_column($pro_list, 'cityid'));
        $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name,level,pid');
        $areaList = array();
        $cityList = array();

        foreach ($list as $key => $value) {
            if ($value['level'] == 3) {
                $cityList[] = $value;
            }elseif ($value['level'] == 4) {
                $areaList[] = $value;
            }
        }

        if (!empty($areaList)) {
            $cityArr = array_unique(array_column($areaList, 'pid'));
            $list = $this->Global_location_model->get_list(['id'=>$cityArr],'id,english_name as name,level,pid');
            foreach ($list as $key => $value) {
                if ($value['level'] == 3) $cityList[] = $value;
            }
        }

        if (empty($cityList)) exit(json_result('0000',$this->response['0000'],array('list'=>array())));

        $cityArr = array_unique(array_column($cityList, 'id'));
        $where = ['id'=>$cityArr];
        if (!empty($proId)) $where['pid'] = $proId;
        $list = $this->Global_location_model->get_list($where,'id,english_name as name',null,null,'convert(english_name using gbk) ASC,id DESC');

        exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // 告警状态下拉列表
    public function alarm_type_list(){
        $version = $this->session->userdata('version');
        
        $batstatus = $this->config->item('batstatus');
        $panelstatus = $this->config->item('panelstatus');
        $lampstatus = $this->config->item('lampstatus');
        $tempstatus = $this->config->item('tempstatus');
        $list = array();
        foreach ($batstatus as $key => $value) {
            $list[] = ['name'=>$value,'value'=>'1'.$key];
        }

        foreach ($panelstatus as $key => $value) {
            $list[] = ['name'=>$value,'value'=>'2'.$key];
        }
        foreach ($lampstatus as $key => $value) {
            $list[] = ['name'=>$value,'value'=>'3'.$key];
        }
        foreach ($tempstatus as $key => $value) {
            $list[] = ['name'=>$value,'value'=>'4'.$key];
        }
        if ($this->os == 2) {
            exit(json_result('0000',$this->response['0000'],['list'=>$list]));
        }else{
            exit(json_result('0000',$this->response['0000'],$list));
        }
        
    }

    // 省级设备数量
    public function pro_dev_count(){
        $role = $this->get_user_info('role');
        $company = $this->get_user_info('company');
        $where = array();

        if ($role != SYSTEM_ADMIN) $where['P.company'] = $company;
        $where['G3.level'] = 2;
        $where['G3.pid'] = 92;
        $join = array();

        $join[] = ['table'=>'global_location as G2','cond'=>'G3.id = G2.pid AND G2.level = 3','type'=>'left'];
        $join[] = ['table'=>'global_location as G1','cond'=>'G2.id = G1.pid AND G1.level = 4','type'=>'left'];
        $join[] = ['table'=>'project as P','cond'=>'G1.id = P.cityid AND G1.level = 4','type'=>'left'];
        
        // $join[] = ['table'=>'global_location as G3','cond'=>'G3.id = G2.pid AND G3.level = 2','type'=>'left'];
        $list = $this->Global_location_model->get_list_by_join($where, 'G3.id,G3.english_name as name,sum(P.lampcount) as lampcount',NULL, NULL, $join, NULL, 'G3.id', 'G3');
        foreach ($list as $key => $value) {
            $list[$key]['lampcount'] = intval($value['lampcount']);
        }
        exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // 市级设备数量
    public function city_dev_count(){
        $role = $this->get_user_info('role');
        $company = $this->get_user_info('company');
        $where = array();
        $proId = intval($this->input->post('proId',true));

        if (!empty($proId)) $where['G2.pid'] = $proId;
        if ($role != SYSTEM_ADMIN) $where['P.company'] = $company;
        $where['G2.level'] = 3;
        $join = array();

        $join[] = ['table'=>'global_location as G1','cond'=>'G2.id = G1.pid','type'=>'left'];
        $join[] = ['table'=>'project as P','cond'=>'G1.id = P.cityid AND G1.level = 4','type'=>'left'];
        // $join[] = ['table'=>'global_location as G2','cond'=>'G2.id = G1.pid','type'=>'left'];
        $list = $this->Global_location_model->get_list_by_join($where, 'G2.id,G2.english_name as name,sum(P.lampcount) as lampcount',NULL, NULL, $join, NULL, 'G2.id', 'G2');
        foreach ($list as $key => $value) {
            $list[$key]['lampcount'] = intval($value['lampcount']);
        }
        exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // 区域设备数量
    public function area_dev_count(){
        $role = $this->get_user_info('role');
        $company = $this->get_user_info('company');
        $where = array();
        $cityId = intval($this->input->post('cityId',true));

        if (!empty($cityId)) $where['G1.pid'] = $cityId;
        if ($role != SYSTEM_ADMIN) $where['P.company'] = $company;
        $where['G1.level'] = 4;
        $join = array();

        $join[] = ['table'=>'project as P','cond'=>'G1.id = P.cityid AND G1.level = 4','type'=>'left'];
        // $join[] = ['table'=>'global_location as G1','cond'=>'G1.id = P.cityid','type'=>'left'];

        $list = $this->Global_location_model->get_list_by_join($where, 'G1.id,G1.english_name as name,sum(P.lampcount) as lampcount',NULL, NULL, $join, NULL, 'G1.id', 'G1');
        foreach ($list as $key => $value) {
            $list[$key]['lampcount'] = intval($value['lampcount']);
        }
        exit(json_result('0000',$this->response['0000'],array('list'=>$list)));
    }

    // app额外接口
    public function app_home_data(){
        $data = array(
            'project_count' => 0,
            'upa_count' => 0,
            'sup_count' => 0,
            'manu_count' => 0,
            'insert_power' => 0,
            'gen_power' => 0,
            'user_power' => 0,
            'light_time' => 0,
        );

        $projectIdArr = $this->get_project_id();
        if (empty($projectIdArr)) exit(json_result('0000',$this->response['0000'],$data));

        $role = $this->get_user_info('role');
        $where = array();
        
        $where[] = 'P.id in ('.implode(',', $projectIdArr).')';
        if ($role != SYSTEM_ADMIN){
            $company = $this->get_user_info('company');
            $where[] = 'P.company = '.$company;
            $data['project_count'] = $this->Project_model->get_count(['id'=>$projectIdArr,'company'=>$company]);
        }else{
            $data['project_count'] = count($projectIdArr);
        }

        $sql = 'select count(*) as total from (select L.manu from lampinfo L left join project P on L.projectid = P.id where '.implode(' AND ', $where).' AND L.manu != 0 AND L.manu != "" group by L.manu) t';
        $total = $this->db->query($sql)->row_array();
        $data['manu_count'] = $total['total'];

        $sql = 'select count(*) as total from (select L.supplier from lampinfo L left join project P on L.projectid = P.id where '.implode(' AND ', $where).' AND L.supplier != 0 AND L.supplier != "" group by L.supplier) t';
        $total = $this->db->query($sql)->row_array();
        $data['sup_count'] = $total['total'];

        $sql = 'select count(*) as total from (select G.id from lampinfo L left join project P on L.projectid = P.id left join global_location G on P.cityid = G.id where '.implode(' AND ', $where).' group by G.id) t';
        $total = $this->db->query($sql)->row_array();
        $data['upa_count'] = $total['total'];

        $sql = 'select sum(L.totalLightTime) as light_time,sum(L.boardpower) as boardpower,sum(L.totalgeneration) as totalgeneration,sum(L.totalconsumption) as totalconsumption from lampinfo L left join project P on L.projectid = P.id where '.implode(' AND ', $where);
        $total = $this->db->query($sql)->row_array();
        $data['light_time'] = ceil($total['light_time']/60);
        $data['insert_power'] = round($total['boardpower']/1000,3);
        $data['gen_power'] = round($total['totalgeneration']/1000,3);
        $data['user_power'] = round($total['totalconsumption']/1000,3);

        exit(json_result('0000',$this->response['0000'],$data));
    }

    public function weather_info(){
        $areaId = intval($this->input->post('areaId',true));
        if (empty($areaId)) exit(json_result('0007',$this->response['0007']));

        $res1 = $this->Global_location_model->get_one(['id'=>$areaId],'level,pid,chinese_name,longitude,latitude,english_name');

        $url = 'https://free-api.heweather.net/s6/weather/forecast?location='.$res1['longitude'].','.$res1['latitude'].'&key=63dbc994630941fab269ad3c3af4d5d2';
        // $url = 'https://way.jd.com/he/freeweather?city='.$res2['chinese_name'].'&appkey=a44fc7f907194098c69bb8e90003bca6';
        $resData = request_post($url);
        $arr = json_decode($resData,true);
        if ($arr['HeWeather6'][0]['status'] == 'ok') {
            $now = $arr['HeWeather6'][0]['daily_forecast'][0];
            $daily_forecast = $arr['HeWeather6'][0]['daily_forecast'];
            $data = ['weatherType'=>$now['cond_txt_d'],'code'=>$now['cond_code_d'],'tmp'=>intval(($now['tmp_min'] + $now['tmp_max'])/2)];

        }else{
            $data = ['weatherType'=>'','code'=>'','tmp'=>''];
        }

        exit(json_result('0000',$this->response['0000'],$data));
    }

    public function get_dev_number(){
        $projectId = intval($this->input->post('projectId',true));
        if (empty($projectId)) exit(json_result('0007',$this->response['0007']));

        $res = $this->Lamp_model->get_one(['projectid'=>$projectId],'max(number) as number');
        if (empty($res) || empty($res['number'])) {
            $number = 1;
        }else{
            $number = $res['number'];
        }
        $number = substr('000000'.$number,-6);
        exit(json_result('0000',$this->response['0000'],['number'=>$number]));
    }
}