dfxd/app/Service/ScreenService.php

<?php

namespace App\Service;

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

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->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->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','desc');

        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_id = ScreenGateway::whereIn('screen_id',$screen_id)
            ->where('del_time',0)
            ->pluck('screen_id','gateway_id')
            ->toArray();
        //获取仓是否在线离线状态
        $online = SystemlService::getIsOnlineStatus($gateway_id);
        //获取Gps信号判断状态
        $gps = SystemlService::getGpsStatus($screen_id);
        //获取是否异常状态判断
        $exception = SystemlService::getExceptionStatus($screen_id);

        $g_map = array_flip($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)
            ->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');

        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();
        $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();
        $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 screenHistoryList($data){
        if(empty($data['screen_id'])) return [false,'请选择仓或台'];
        if(empty($data['device_type_id'])) return [false,'设备类型不能为空'];
        if($data['device_type_id'] == DeviceType::type_one && empty($data['type']))  return [false,'传感器类型时需传递TYPE'];
        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;
    }
}