dfxd/app/Service/ScreenService.php

<?php

namespace App\Service;

use App\Model\Device;
use App\Model\DeviceType;
use App\Model\Screen;
use App\Model\ScreenDevice;
use App\Model\ScreenGateway;
use App\Model\SystemL;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

class ScreenService extends Service
{
    public function edit($data){
        list($status,$msg) = $this->screenRule($data,false);
        if(!$status) return [$status,$msg];

        try{
            DB::beginTransaction();
            $model = Screen::where('id',$data['id'])->first();
            $model->code = $data['code'];
            $model->title = $data['title'];
            $model->area_id = $data['area_id'];
            $model->size = $data['size'];
            $model->type = $data['type'];
            $model->remark = $data['remark'];
            $model->address = $data['address'] ?? '';
            $model->coordinate = $data['coordinate'] ?? '';
            $model->img = $data['img'] ?? "";
            $model->save();

            ScreenGateway::where('screen_id',$data['id'])->update(['del_time' => time()]);
            if(isset($data['gateway_id'])){
                if(! is_array($data['gateway_id'])) $data['gateway_id'] = [$data['gateway_id']];
                $insert = [];
                foreach ($data['gateway_id'] as $val){
                    $insert[] = [
                        'screen_id' => $model->id,
                        'gateway_id' => $val,
                        'crt_time' => time()
                    ];
                }
                ScreenGateway::insert($insert);
            }
            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true,'保存成功！'];
    }

    public function add($data){
        list($status,$msg) = $this->screenRule($data);
        if(!$status) return [$status,$msg];

        try{
            DB::beginTransaction();
            $model = new Screen();
            $model->code = $data['code'];
            $model->title = $data['title'];
            $model->area_id = $data['area_id'];
            $model->size = $data['size'];
            $model->type = $data['type'];
            $model->remark = $data['remark'];
            $model->address = $data['address'] ?? '';
            $model->coordinate = $data['coordinate'] ?? '';
            $model->img = $data['img'] ?? "";
            $model->save();

            if(isset($data['gateway_id'])){
                if(! is_array($data['gateway_id'])) $data['gateway_id'] = [$data['gateway_id']];
                $insert = [];
                foreach ($data['gateway_id'] as $val){
                    $insert[] = [
                        'screen_id' => $model->id,
                        'gateway_id' => $val,
                        'crt_time' => time()
                    ];
                }
                ScreenGateway::insert($insert);
            }
            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true,'保存成功！'];
    }

    public function del($data){
        if($this->isEmpty($data,'id')) return [false,'ID必须！'];

        $bool = ScreenDevice::where('del_time',0)->where('screen_id',$data['id'])->exists();
        if($bool) return [false,'仓已绑定设备'];

        Screen::where('id',$data['id'])->update([
            'del_time' => time()
        ]);

        return [true,'删除成功'];
    }

    public function orderList($data){
        $model = Screen::where('del_time',0)
            ->select('*')
            ->orderBy('id','asc');

        if(! empty($data['type'])) $model->where('type',$data['type']);
        if(! empty($data['area_id'])) $model->where('area_id', $data['area_id']);
        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');
        if(! empty($data['device_name'])) {
            $device = Device::where('title', 'LIKE', '%'.$data['title'].'%')
                ->where('del_time',0)
                ->select('id')->get()->toArray();
            $screen = ScreenDevice::whereIn('device_id',array_column($device,'id'))
                ->where('del_time',0)
                ->select('screen_id')->get()->toArray();
            $model->whereIn('id',array_column($screen,'screen_id'));
        }

        $list = $this->limit($model,'',$data);
        $list = $this->fillOrderList($list);

        return [200,$list];
    }

    public function fillOrderList($data){
        if(empty($data['data'])) return $data;

        $screen_id = array_column($data['data'],'id');
        $gateway = ScreenGateway::whereIn('screen_id',$screen_id)
            ->where('del_time',0)
            ->select('screen_id','gateway_id')
            ->get()
            ->toArray();
        //获取仓是否在线离线状态
        $online = SystemlService::getIsOnlineStatus($gateway);
        //获取Gps信号判断状态
        $gps = SystemlService::getGpsStatus($screen_id);
        //获取是否异常状态判断
        $exception = SystemlService::getExceptionStatus($screen_id);

        $g_map = [];
        foreach ($gateway as $value){
            $g_map[$value['screen_id']] = $value['gateway_id'];
        }
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['gateway_id'] = $g_map[$value['id']] ?? 0;
            $tmp_online = $online[$value['id']] ?? 0;
            $tmp_coordinate = $gps[$value['id']] ?? 0;
            $tmp_exception = $exception[$value['id']] ?? 0;
            $data['data'][$key]['is_online'] = $tmp_online;
            $data['data'][$key]['is_coordinate'] = $tmp_coordinate;
            $data['data'][$key]['is_exception'] = $tmp_exception;
            $data['data'][$key]['is_online_name'] = Screen::$online[$tmp_online] ?? '';
            $data['data'][$key]['coordinate_name'] = Screen::$gps[$tmp_coordinate] ?? '';
            $data['data'][$key]['is_exception_name'] = Screen::$exception[$tmp_exception] ?? '';
        }

        return $data;
    }

    public function screenRule($data,$is_add = true){
        if(empty($data['title'])) return [false,'名称不能为空！'];
        if(empty($data['code'])) return [false,'编码不能为空！'];
        if(empty($data['area_id'])) return [false,'区域不能为空'];
        if(empty($data['type'])) return [false,'仓类型不能为空'];

        if($is_add){
            $bool = Screen::whereRaw("(binary code = '{$data['code']}' OR title = '{$data['title']}')")
                ->where('del_time',0)
                ->exists();
        }else{
            if(empty($data['id'])) return [false,'id不能为空！'];

            $bool = Screen::whereRaw("(binary code = '{$data['code']}' OR title = '{$data['title']}')")
                ->where('id','<>',$data['id'])
                ->where('del_time',0)
                ->exists();
        }
        if($bool) return [false,'编码或名称不能重复'];

        return [true,$data];
    }

    public function screenDeviceList($data){
        if(empty($data['screen_id'])) return [false,'仓ID不能为空'];
        $model = ScreenDevice::from('screen_device as a')
            ->leftJoin('device as b','b.id','a.device_id')
            ->leftJoin('device_type as c','c.id','b.device_type_id')
            ->where('a.screen_id',$data['screen_id'])
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->select('a.id','b.id as device_id','b.title','b.code','b.gateway_id','b.device_type_id','b.remark','c.title as device_type_name','b.size','b.unit');

        if(! empty($data['gateway_id'])) $model->where('b.gateway_id',$data['gateway_id']);
        $list = $this->limit($model,'',$data);
        $list = $this->fillData($list);

        return [true, $list];
    }

    public function fillData($data){
        if(empty($data['data'])) return $data;

        $message = SystemlService::getLastData(array_column($data['data'],'code'));
        foreach ($data['data'] as $key => $value){
            $is_online = $is_exception = 0;
            if(isset($message[$value['code']])){
                $is_online = 1;
                $tmp = $message[$value['code']];
                if(in_array($value['device_type_id'],DeviceType::$node_two)){
                    //传感器类
                    if(in_array($tmp['data_point_name'],DeviceType::$normal)) $is_exception = 1;
                }else{
                    $is_exception = 1;
                }
            }
            $data['data'][$key]['is_online'] = $is_online;
            $data['data'][$key]['is_exception'] = $is_exception;
        }

        return $data;
    }

    public function screenDevice($data){
        list($status,$msg) = $this->screenDeviceRule($data);
        if(! $status) return [false,$msg];

        $insert = [];
        foreach ($data['device_id'] as $val){
            $insert[] = [
                'screen_id' => $data['screen_id'],
                'device_id' => $val,
                'crt_time' => time()
            ];
        }
        ScreenDevice::insert($insert);

        return [true,''];
    }

    public function screenDeviceRule($data){
        if(empty($data['screen_id'])) return [false,'请选择仓'];
        if(empty($data['device_id'])) return [false,'请选择设备'];

        $bool = ScreenDevice::where('del_time',0)
            ->where('screen_id','<>',$data['screen_id'])
            ->whereIn('device_id',$data['device_id'])
            ->exists();
        if($bool) return [false, '设备已经绑定其他仓！'];

        $one = ScreenGateway::where('del_time',0)->where('screen_id',$data['screen_id'])->first();
        $two = Device::where('del_time',0)->where('id',$data['device_id'])->first();
        if(empty($one)) return [false,'仓网关不存在！'];
        if(empty($two)) return [false,'设备网关不存在！'];
        if($one->gateway_id != $two->gateway_id) return [false,'设备网关与仓网关不一致！'];

        return [true, ''];
    }

    public function screenRemoveDevice($data){
        if(empty($data['id'])) return [false,'请选择移除的数据'];

        ScreenDevice::where('id',$data['id'])->update(['del_time' => time()]);

        return [true,''];
    }

    public function screenDeviceDataRealTimeList($data){
        if(empty($data['screen_id'])) return [false,'请选择仓或台'];

        //获取仓或台的设备ID
        $device = ScreenDevice::where('del_time',0)
            ->where('screen_id',$data['screen_id'])
            ->select('device_id')
            ->get()->toArray();
        $device = array_column($device,'device_id');

        //设备的分组信息 按照设备类型
        $device = Device::whereIn('id',$device)
            ->select('code','device_type_id')
            ->get()->toArray();
        $map = $nodeOne = $nodeTwo = $nodeThree = [];
        foreach ($device as $value){
            if(in_array($value['device_type_id'],DeviceType::$node_one)){
                $nodeOne[] = $value['code'];
            }elseif(in_array($value['device_type_id'],DeviceType::$node_two)){
                $nodeTwo[] = $value['code'];
            }elseif(in_array($value['device_type_id'],DeviceType::$node_three)){
                $nodeThree[] = $value['code'];
            }
            $map[$value['code']] = $value['device_type_id'];
        }

        $posts_a = SystemL::whereIn('t.device_no', $nodeOne)
            ->select('value',DB::raw("CONCAT(device_name,'-',data_point_name) AS data_point_name"))
            ->from(DB::raw("(SELECT MAX(id) AS max_id, device_no FROM system_l GROUP BY device_no) AS subquery"))
            ->join('system_l AS t', function ($join) {
                $join->on('subquery.max_id', '=', 't.id')
                    ->on('subquery.device_no', '=', 't.device_no');
            })
            ->groupBy('t.device_no')
            ->limit(20)
            ->get()->toArray();
        foreach ($posts_a as $key => $value){
            $posts_a[$key]['value'] = $value['value'] ? '开启' : '关闭';
        }
        $posts_b = SystemL::whereIn('t.device_no', $nodeTwo)
            ->select('value',DB::raw("CONCAT(device_name,'-',data_point_name) AS data_point_name"))
            ->from(DB::raw("(SELECT MAX(id) AS max_id, device_no FROM system_l GROUP BY device_no) AS subquery"))
            ->join('system_l AS t', function ($join) {
                $join->on('subquery.max_id', '=', 't.id')
                    ->on('subquery.device_no', '=', 't.device_no');
            })
            ->groupBy('t.device_no')
            ->limit(20)
            ->get()->toArray();
        foreach ($posts_b as $key => $value){
            if(strstr($value['data_point_name'],'温度')){
                $posts_b[$key]['value'] = $value['value'] . '℃';
            }elseif (strstr($value['data_point_name'],'湿度')){
                $posts_b[$key]['value'] = $value['value'] . '％rh';
            }
        }
        $posts_c = SystemL::whereIn('t.device_no', $nodeThree)
            ->select('value',DB::raw("CONCAT(device_name,'-',data_point_name) AS data_point_name"))
            ->from(DB::raw("(SELECT MAX(id) AS max_id, device_no FROM system_l GROUP BY device_no) AS subquery"))
            ->join('system_l AS t', function ($join) {
                $join->on('subquery.max_id', '=', 't.id')
                    ->on('subquery.device_no', '=', 't.device_no');
            })
            ->groupBy('t.device_no')
            ->limit(20)
            ->get()->toArray();
        return [true,['node_one' => $posts_a,'node_two'=>$posts_b,'node_three'=>$posts_c]];
    }

    public function screenTrendChart($data){
        if(empty($data['device_no'])) return [false,'请选择设备'];
        if(count($data['device_no']) > 4) return [false,'一次最多选择4个设备'];
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false,'时间不能为空'];

        $time = $this->changeDateToTimeStampAboutRange($data['time']);
        $model = SystemL::whereIn('device_no', $data['device_no'])
            ->select('value','time','device_no')
            ->orderBy('id','asc');
        $model->where('time','>=',$time[0] * 1000);
        $model->where('time','<',$time[1] * 1000);
        $list = $model->get()->toArray();
        $list = $this->fillScreenTrendChart($list);

        return [true,$list];
    }

    public function fillScreenTrendChart($data){
        if(empty($data)) return $data;

        $return = [];
        foreach ($data as $value){
            $return[$value['device_no']][] = [
                'time' => date('Y-m-d H:i:s',$value['time'] / 1000),
                'value' => $value['value']
            ];
        }
        $final = [];
        foreach ($return as $key => $value){
            $final[] = [
                'device_no' => $key,
                'detail' => $value
            ];
        }

        return $final;
    }

    public function screenHistoryList($data){
        if(empty($data['screen_id'])) return [false,'请选择仓或台'];
        if(empty($data['device_type_id'])) return [false,'设备类型不能为空'];
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false,'时间不能为空'];

        //获取仓或台的设备ID
        $device = ScreenDevice::where('del_time',0)
            ->where('screen_id',$data['screen_id'])
            ->select('device_id')
            ->get()->toArray();
        $device = array_column($device,'device_id');

        //设备的分组信息 按照设备类型
        $device = Device::whereIn('id',$device)
            ->where('device_type_id',$data['device_type_id'])
            ->select('code','device_type_id')
            ->get()->toArray();

        $time = $this->changeDateToTimeStampAboutRange($data['time']);
        $model = SystemL::whereIn('device_no', array_column($device,'code'))
            ->select('data_point_name','value','time','device_no')
            ->orderBy('id','desc');
        $model->where('time','>=',$time[0] * 1000);
        $model->where('time','<',$time[1] * 1000);
        if(! empty($data['type'])){
            if($data['type'] == 1){
                $model->where('data_point_name','LIKE', '%'. '温度' .'%');
            }else{
                $model->where('data_point_name','LIKE', '%'. '湿度' .'%');
            }
        }

        $list = $this->limit($model,'',$data);
        $list = $this->fillHistoryData($list);

        return [true,$list];
    }

    public function fillHistoryData($data){
        if(empty($data['data'])) return $data;

        foreach ($data['data'] as $key => $value){
            $time = $value['time'] / 1000;
            $data['data'][$key]['time'] = date("Y-m-d H:i:s",$time);
        }

        return $data;
    }

    public function screenGps($data){
        if(empty($data['screen_id'])) return [false,'请选择仓'];

        //返回的参数
        $result = [
            'province' => '',
            'city' => '',
            'district' => '',
            'location' => '',
            'temperature' => [],
            'humidity' => []
        ];

        $device = ScreenDevice::from('screen_device as a')
            ->leftJoin('device as b','b.id','a.device_id')
            ->whereIn('b.device_type_id',[DeviceType::type_one,DeviceType::type_four])
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('a.screen_id',$data['screen_id'])
            ->select('b.code','b.device_type_id')
            ->get()->toArray();
        $gps = $other = [];
        foreach ($device as $value){
            if($value['device_type_id'] == DeviceType::type_four){
                $gps[] = $value['code'];
            }else{
                $other[] = $value['code'];
            }
        }

        //gps定位
        $return_gps = SystemlService::getLastData($gps);
        if(! empty($return_gps)){
            $first_position = "";
            foreach ($return_gps as $value){
                if(! empty ($first_postition)) continue;
                $first_position = $value['value'];
            }
            $url = 'https://restapi.amap.com/v3/geocode/regeo?location='.$first_position.'&key='.SystemL::GaoDeMapKey;
            try{
                $return = $this->curlOpen($url);
                $return = json_decode($return,true);
                if(! empty($return['status'])){
                    $position = $return['regeocode']['addressComponent'] ?? [];
                    $result = [
                        'province' => $position['province'] ?? '',
                        'city' => $position['city'] ?? '',
                        'district' => $position['district'] ?? '',
                        'location' => $first_position
                    ];
                }
            }catch (\Exception $exception){
//                return [false,$exception->getMessage()];
            }
        }

        //温度湿度
        $time = strtotime('-2 min',time());
        $time = $time * 1000;
        $model = SystemL::whereIn('device_no', $other)
            ->select('value','time','device_no','data_point_name')
            ->orderBy('id','asc');
        $model->where('time','>=',$time);
        $list = $model->get()->toArray();
        $chart = $this->fillScreenChart($list);
        $result['temperature'] = $chart['temperature'];
        $result['humidity'] = $chart['humidity'];

        return [true, $result];
    }

    public function fillScreenChart($data){
        if(empty($data)) return ['temperature' => [], 'humidity'=> []];

        $return1 = $return2 = [];
        foreach ($data as $value){
            if (strstr($value['data_point_name'],'温度')){
                $return1[$value['device_no']][] = [
                    'time' => date('Y-m-d H:i:s',$value['time'] / 1000),
                    'value' => $value['value']
                ];
            }elseif (strstr($value['data_point_name'],'湿度')){
                $return2[$value['device_no']][] = [
                    'time' => date('Y-m-d H:i:s',$value['time'] / 1000),
                    'value' => $value['value']
                ];
            }
        }
        $final1 = $final2 = [];
        foreach ($return1 as $key => $value){
            if(! empty($final1))  continue;
            $final1 = $value;
        }
        foreach ($return2 as $key => $value){
            if(empty($final2)) continue;
            $final2 = $value;
        }

        return ['temperature' => $final1, 'humidity'=>$final2];
    }

    public function uploadScreenFile($file){
        // 获取文件相关信息
        $ext = $file->getClientOriginalExtension();     // 扩展名
        $realPath = $file->getRealPath();   //临时文件的绝对路径

        $ext = strtolower($ext);
        if (!in_array($ext, Screen::FILE_TYPE)){
            $str = '文件格式为:';
            foreach (Screen::FILE_TYPE as $value){
                $str.= $value . ' ' ;
            }
            return [false,$str];
        }

        // 上传文件
        $file_name = time().rand(1000,9999);
        $filename =  $file_name.'.' . $ext;
        // 使用我们新建的uploads本地存储空间（目录）
        Storage::disk('public')->put('screen/'.$filename, file_get_contents($realPath));

        return [true, '/api/screen/'.$file_name];
    }

    public function screenMessage($data){
        $model = Screen::where('del_time',0)
            ->select('id','code','title','img')
            ->orderBy('id','asc');

        $list = $model->get()->toArray();
        $list = $this->fillScreenMessage($list);

        return [200,$list];
    }

    public function fillScreenMessage($data){
        if(empty($data)) return $data;

        $screen_id = array_column($data,'id');
        //设备
        $result = ScreenDevice::from('screen_device as a')
            ->leftJoin('device as b','b.id','a.device_id')
            ->whereIn('a.screen_id',$screen_id)
            ->where('a.del_time',0)
            ->select('a.screen_id','b.code','b.device_type_id','b.unit')
            ->get()
            ->toArray();
        $map = $map2 = $map3 = [];
        foreach ($result as $value){
            $map[$value['screen_id']][] = $value['code'];
            $map2[$value['code']] = $value['device_type_id'];
            $map3[$value['code']] = $value['unit'];
        }
        $return = SystemlService::getLastData(array_column($result,'code'));

        foreach ($data as $key => $value){
            if(isset($map[$value['id']])){
                foreach ($return as $code => $val){
                    if(in_array($code,$map[$value['id']]) && isset($map2[$code])){
                        if(empty($data[$key]['temperature']) && $map2[$code] == DeviceType::type_one && strstr($val['data_point_name'],'温度')){
                            $data[$key]['temperature'] = $val['value'] . ($map3[$code] ?? '');
                        }elseif(empty($data[$key]['humidity']) && $map2[$code] == DeviceType::type_one && strstr($val['data_point_name'],'湿度')){
                            $data[$key]['humidity'] = $val['value']. ($map3[$code] ?? '');
                        }elseif(empty($data[$key]['open']) && $map2[$code] == DeviceType::type_two){
                            $data[$key]['open'] = $val['value'] ? '开' : '关';
                        }elseif(empty($data[$key]['warning']) && $map2[$code] == DeviceType::type_three){
                            $data[$key]['warning'] = $val['value'] ? '是' : '否';
                        }elseif(empty($data[$key]['gps']) && $map2[$code] == DeviceType::type_four){
                            $data[$key]['gps'] = $val['value'];
                        }
                    }
                }
            }
        }

        return $data;
    }
}