mingde-bd/application/api/controller/energy/Energy.php

<?php
namespace app\api\controller\energy;

use app\hander\HelpHander;
use think\Controller;
use think\Db;
class Energy extends Base
{

    public function energyCount(){

        $addr = input('addr','');
        $ssmap = [];
        if($addr !=''){
            $ids = $this->addrSubIds($addr);
            $map[] = ['ed.room_id','in',$ids];
            $ssmap[] = ['room_id','in',$ids];
        }
        $map[] = ['ed.del','=',1];
        $map[] = ['ed.cate','=',1];
        $map[] = ['ed.org_id','=',$this->orgId];
        $map1[] = ['ep.report_time','>=',date('Y-m-d').' 00:00:00'];
        $map1[] = ['ep.report_time','<=',date('Y-m-d').' 23:59:59'];

        $map2[] = ['ep.report_time','>=',date('Y-m-d',strtotime('-1 day')).' 00:00:00'];
        $map2[] = ['ep.report_time','<=',date('Y-m-d',strtotime('-1 day')).' 23:59:59'];

        $lists = Db::name('energy_param')
            ->alias('ep')
            ->join('energy_device ed','ep.device_sn = ed.sn')
            ->where($map)
            ->where($map1)
            ->field('ep.device_sn,max(ep.degree) as degree')
            ->group('ep.device_sn')
            ->order('ep.report_time desc')
            ->select();
        $count = 0;
        foreach ($lists as $k=>$v){
            $count += $v['degree'];
        }

        $lists2 = Db::name('energy_param')
            ->alias('ep')
            ->join('energy_device ed','ep.device_sn = ed.sn')
            ->where($map)
            ->where($map2)
            ->field('ep.device_sn,max(ep.degree) as degree')
            ->group('ep.device_sn')
            ->order('ep.report_time desc')
            ->select();
        $count1 = 0;
        foreach ($lists2 as $k=>$v){
            $count1 += $v['degree'];
        }


        $power = 0;
        $devices = Db::name('energy_device')
            ->where('org_id',$this->orgId)
            ->where('cate',5)
            ->where($ssmap)
            ->where('del',0)
            ->select();
        foreach ($devices as $k=>$v){
            $info = Db::name('energy_param')->where('device_sn',$v['sn'])->where('org_id',$this->orgId)->order('report_time desc')->find();
            if($info){
                $content = json_decode($info['content'],true);
                if(isset($content['P'])){
                    $power += $content['P'];
                }
            }
        }


        $zongList = Db::name('energy_param')
            ->alias('ep')
            ->join('energy_device ed','ep.device_sn = ed.sn')
            ->where($map)
            ->field('ep.device_sn,max(ep.degree) as degree')
            ->group('ep.device_sn')
            ->order('ep.report_time desc')
            ->select();
        $zongNum = 0;
        foreach ($zongList as $k=>$v){
            $zongNum += $v['degree'];
        }
        $pfm = 85000; //面积(m2)
        $bzm = round($zongNum/8137*1000/$pfm,2);
        //用电量折算标准煤：1.229（t/万KWh） 1万千瓦时=1.229吨标煤；8137千瓦时=1吨标煤

        //1千瓦时的二氧化碳排放量约为960克。
        $co2 = round($zongNum*0.96/85000,2);

        $data = [
            'count'=>round($count,2),
            'count1'=>round($count1,2),
            'num'=>round($power,2),
            'num1'=>$bzm,
            'num2'=>$co2,
        ];

        HelpHander::success($data);

    }


    public function addrList(){

        $lists = Db::name('energy_address')
            ->where('pid',0)
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        HelpHander::success($lists);

    }


    public function addrSubIds($id){

        $lists = Db::name('energy_address')
            ->where('pid',$id)
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->select();
        $ids = [];
        foreach ($lists as $k=>$v){
            $ids[] = Db::name('energy_address')
                ->where('pid',$v['id'])
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('enable',1)
                ->column('id');
        }
        $aids = [];
        foreach ($ids as $k=>$v){
            foreach ($v as $kk=>$vv){
                $aids[] = $vv;
            }
        }
        return $aids;
    }

    public function addrElectricityList(){
        $lists = Db::name('energy_address')
            ->where('pid',0)
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('enable',1)
            ->order('id desc')
            ->limit(8)
            ->select();

        $map[] = ['ed.del','=',1];
        $map[] = ['ed.org_id','=',$this->orgId];
        $map1[] = ['ep.report_time','>=',date('Y-m-d').' 00:00:00'];
        $map1[] = ['ep.report_time','<=',date('Y-m-d').' 23:59:59'];
        $data = [];
        foreach ($lists as $k=>$v){
            $ids = $this->addrSubIds($v['id']);
            $value =  Db::name('energy_device')
                ->alias('ed')
                ->join('energy_param ep','ep.device_sn=ed.sn')
                ->where('ed.room_id','in',$ids)
                ->where($map)
                ->where($map1)
                ->sum('degree');
            $data[$k]['name'] = $v['title'];
            $data[$k]['value'] = $value;
        }
        HelpHander::success($data);
    }

    public function timesList(){
        $x = $y = $y1 = [];
        $list = [];
        for ($i=1;$i<=24;$i++){
            $x[] = $i;
            $t= $i-1;
            if($i < 10){
              $i = '0'.$i;
            }
            if($t < 10){
                $t = '0'.$i;
            }
            $list[$i-1]['st'] = date('Y-m-d').' '.$t.'00:00';
            $list[$i-1]['et'] = date('Y-m-d').' '.$i.'00:00';
        }

        foreach ($list as $k=>$v){
            $jin = Db::name('energy_param')
                ->where('org_id',$this->orgId)
                ->where('report_time','>=',$v['st'])
                ->where('report_time','<=',$v['et'])
                ->sum('degree');
            $y[] = round($jin,1);

            $zuo = Db::name('energy_param')
                ->where('org_id',$this->orgId)
                ->where('report_time','>=',date('Y-m-d H:i:s',strtotime($v['st'])-86400))
                ->where('report_time','<=',date('Y-m-d H:i:s',strtotime($v['et'])-86400))
                ->sum('degree');
            $y1[] = round($zuo,1);
        }
        $data =[
            'x'=>$x,
            'y'=>$y,
            'y1'=>$y1
        ];

        HelpHander::success($data);

    }

    public function deviceList(){

        $lists = Db::name('energy_device')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->limit(6)
            ->select();
        $x = $y = [];
        foreach ($lists as $k=>$v){
            $x[] = $v['title'];
            $degree = Db::name('energy_param')
                ->where('device_sn',$v['sn'])
                ->where('org_id',$this->orgId)
                ->where('report_time','>=',date('Y-m-d').' 00:00:00')
                ->where('report_time','<=',date('Y-m-d').' 23:59:59')
                ->sum('degree');

            $y[] = round($degree,1);
        }
        $data = [
            'x'=>$x,
            'y'=>$y
        ];
        HelpHander::success($data);
    }

    //电耗报表
    public function meterList(){
        $date = input('date','');
        $deviceName = input('deviceName');

        if($deviceName !=''){
            $map[] = ['ed.title','like','%'.$deviceName.'%'];
        }
        $where = [];
        if($date !=''){
            $st = $date[0];
            $et = $date[1];
            $where[] = ['report_time','>=',date('Y-m-d H:i:s',strtotime($st))];
            $where[] = ['report_time','<=',date('Y-m-d H:i:s',strtotime($et))];
        }
        $map[] = ['ed.org_id','=',$this->orgId];
        $map[] = ['ed.del','=',0];
        $map[] = ['ed.protocol','=',1];
        $map[] = ['ed.cate','in',[1,2,5]];

//        $list =  Db::name('energy_device')
//            ->alias('ed')
//            ->field('ed.*,er.title as room')
//            ->join('energy_room er','er.id=ed.room_id')
//            ->where($map)
//            ->order('ed.id desc')
//            ->limit(50)
//            ->select();

        $list =  Db::name('energy_device')
            ->alias('ed')
            ->field('ed.*,ea.title as address')
            ->join('energy_address ea','ea.id=ed.address_id')
            ->where($map)
            ->order('ed.id desc')
            ->limit(30)
            ->select();

        $data = [];
        foreach ($list as $k=>$v){
//            $address = Db::name('energy_address')->where('id',$v['address_id'])->value('title');
            $paramList = Db::name('energy_param')
                ->field('content')
                ->where('device_sn',$v['sn'])
                ->where('org_id',$this->orgId)
                ->where($where)
                ->select();
            $EPIF = $EPIJ = $EPIP = $EPIG = 0;
            foreach ($paramList as $kk=>$vv){
                $content = json_decode($vv['content'],true);
                if(isset($content['EPIF'])){
                    $EPIF += round($content['EPIF'],1);
                    $EPIJ += round($content['EPIJ'],1);
                    $EPIP += round($content['EPIP'],1);
                    $EPIG += round($content['EPIG'],1);
                }
            }

            $zong = $EPIF+$EPIJ+$EPIP+$EPIG;

            $data[] =[
//                'room'=>$v['room'],
//                'address'=>$address,
                'address'=>$v['address'],
                'device'=>$v['title'],
                'jian'=>round($EPIF,1),
                'feng'=> round($EPIJ,1),
                'ping'=>round($EPIP,1),
                'gu'=>round($EPIG,1),
                'count'=>round($zong,1),
                'total_price'=>round($zong*0.5,1),
            ];

        }
        $data = list_sort_by($data,'ping','desc');


        $columns = [
//            ["title" => "房间","key" => "room"],
            ["title" => "地点","key" => "address"],
            ["title" => "设备","key" => "device"],
            ["title" => "尖用量(kWh)","key" => "jian"],
            ["title" => "峰用量(kWh)","key" => "feng"],
            ["title" => "平用量(kWh)","key" => "ping"],
            ["title" => "谷用量(kWh)","key" => "gu"],
            ["title" => "总用量(kWh)","key" => "count"],
            ["title" => "总费用(元)","key" => "totalPrice"],
        ];
        $dt = [
            'columns'=>$columns,
            'list'=>$data
        ];

        HelpHander::success($dt);
    }
    //抄表明细表
    public function meterReadingList(){
        $page = input('page',1);
        $size = input('size',6);
        $date = input('date','');
        $deviceName = input('deviceName');

        if($deviceName !=''){
            $map[] = ['title','like','%'.$deviceName.'%'];
        }
        $where = [];
        if($date !=''){
            $st = $date[0];
            $et = $date[1];
            $where[] = ['report_time','>=',date('Y-m-d H:i:s',strtotime($st))];
            $where[] = ['report_time','<=',date('Y-m-d H:i:s',strtotime($et))];
        }
        $map[] = ['org_id','=',$this->orgId];
        $map[] = ['del','=',0];
        $map[] = ['cate','=',1];
        $list =  Db::name('energy_device')
            ->field('id,title,sn,address_id')
            ->where($map)
            ->order('id desc')
            ->page($page,$size)
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['address'] = Db::name('energy_address')->where('id',$v['address_id'])->value('title');

            $sinfo = Db::name('energy_param')
                ->where('device_sn',$v['sn'])
                ->where($where)
                ->where('org_id',$this->orgId)
                ->where('report_time','<>','')
                ->where('report_time','<>',null)
                ->order('report_time asc')
                ->find();

            $einfo = Db::name('energy_param')
                ->where('device_sn',$v['sn'])
                ->where($where)
                ->where('org_id',$this->orgId)
                ->where('report_time','<>','')
                ->where('report_time','<>',null)
                ->order('report_time desc')
                ->find();
            $list[$k]['st'] = '';
            $list[$k]['et'] = '';
            $list[$k]['snum'] = 0;
            $list[$k]['enum'] = 0;
            $list[$k]['yl'] = 0;
            $snum = $enum = 0;
            if($sinfo){
                $list[$k]['st'] = $sinfo['report_time']?date('Y-m-d H:i',strtotime($sinfo['report_time'])):'';
                $list[$k]['snum'] = $snum = round($sinfo['degree'],1);
            }

            if($einfo){
                $list[$k]['et'] = $einfo['report_time']?date('Y-m-d H:i',strtotime($einfo['report_time'])):'';
                $list[$k]['enum']  = $enum = round($einfo['degree'],1);
            }
            $list[$k]['yl'] = round($enum-$snum,1);
        }
        $total = Db::name('energy_device')
            ->where($map)
            ->count();

        $data = [
            'total'=>$total,
            'list'=>$list
        ];

        HelpHander::success($data);
    }

    public function meterReadingListExport(){
        $ids = input('ids','');
        $date = input('date','');
        $deviceName = input('deviceName');

        if($deviceName !=''){
            $map[] = ['title','like','%'.$deviceName.'%'];
        }
        $where = [];
        if($date !=''){
            $st = $date[0];
            $et = $date[1];
            $where[] = ['report_time','>=',date('Y-m-d H:i:s',strtotime($st))];
            $where[] = ['report_time','<=',date('Y-m-d H:i:s',strtotime($et))];
        }
        if($ids){
            $map[] = ['id','in',explode(',',$ids)];
        }
        $map[] = ['org_id','=',$this->orgId];
        $map[] = ['del','=',0];
        $map[] = ['cate','=',1];
        $list =  Db::name('energy_device')
            ->field('id,title,sn,address_id')
            ->where($map)
            ->order('id desc')
            ->select();

        foreach ($list as $k=>$v){
            $list[$k]['address'] = Db::name('energy_address')->where('id',$v['address_id'])->value('title');
            $sinfo = Db::name('energy_param')
                ->where('device_sn',$v['sn'])
                ->where($where)
                ->where('org_id',$this->orgId)
                ->where('report_time','<>','')
                ->where('report_time','<>',null)
                ->order('report_time asc')
                ->find();

            $einfo = Db::name('energy_param')
                ->where('device_sn',$v['sn'])
                ->where($where)
                ->where('org_id',$this->orgId)
                ->where('report_time','<>','')
                ->where('report_time','<>',null)
                ->order('report_time desc')
                ->find();
            $list[$k]['st'] = '';
            $list[$k]['et'] = '';
            $list[$k]['snum'] = 0;
            $list[$k]['enum'] = 0;
            $list[$k]['yl'] = 0;
            $snum = $enum = 0;
            if($sinfo){
                $list[$k]['st'] = $sinfo['report_time']?date('Y-m-d H:i',strtotime($sinfo['report_time'])):'';
                $list[$k]['snum'] = $snum = round($sinfo['degree'],1);
            }

            if($einfo){
                $list[$k]['et'] = $einfo['report_time']? date('Y-m-d H:i',strtotime($einfo['report_time'])):'';
                $list[$k]['enum']  = $enum = round($einfo['degree'],1);
            }
            $list[$k]['yl'] = round($enum-$snum,1);
        }

        $columns = [
            ["title" => "设备id","key" => "id"],
            ["title" => "地点","key" => "address"],
            ["title" => "设备名称","key" => "title"],
            ["title" => "起始时间","key" => "st"],
            ["title" => "终止时间","key" => "snum"],
            ["title" => "起始读数","key" => "et"],
            ["title" => "终止读数","key" => "enum"],
            ["title" => "用量","key" => "yl"],
        ];
        $dt = [
            'columns'=>$columns,
            'list'=>$list
        ];

        HelpHander::success($dt);
    }

}