video_system/app/Console/Commands/UpdateHkList.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Input;
use Modules\Camera\Entities\CameraList;
use Modules\Camera\Enum\CameraEnum;
use Modules\Camera\Services\CameraServices;
use Modules\Camera\Http\Controllers\Api\TdwyController;
use Modules\Mine\Entities\MineList;
use Modules\Mine\Entities\MineListExt;
use Modules\Mine\Enum\MineEnum;
use Modules\Mine\Services\MineServices;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class UpdateHkList extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'hklist:update';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '同步海康区域及摄像头列表';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $start_time = time();
        echo '开始时间:'.date('Y-m-d H:i:s');
        $this->updateAreaList();
//        echo '区域结束:'.date('Y-m-d H:i:s');
        $this->updateCameraList();

        $this->insert_camera_id();
        echo '时长:'.number_format(((time()-$start_time)/60),2).'分钟';
    }

    //金家渠摄像头ID姓名同步
    public function insert_camera_id(){
        DB::connection('mysql_fwe10')->table('uf_sxtmcdyi')->where('mkid','2345')->delete();

        $camera_list = DB::table('camera_list as c')
            ->select('c.id', 'c.camera_name')
            ->join('mine_list as m', 'c.mine_id', '=', 'm.id')
            ->where('m.degree', 'like', '968|2345%')
            ->where('m.deleted_at', null)
            ->where('c.deleted_at', null)
            ->get()
            ->toArray();

        foreach($camera_list as $k=>$v){
            $id = mt_rand(pow(10, 18), pow(10, 18) + 999999999);
            $insert = [
                'ID' => $id,
                'FORM_DATA_ID' => $id,
                'DATA_INDEX' => 0.0,
                'CREATE_TIME' => date('Y-m-d H:i:s'),
                'TENANT_KEY' => 't1zz9w8165',
                'IS_DELETE' => 0,
                'DELETE_TYPE' => 0,
                'FT_STATUS' => 0,

                'ssmk' => '金家渠煤矿',
                'mkid' => '2345',
                'sxtmc' => $v->camera_name,
                'sxtid' => $v->id
            ];
            DB::connection('mysql_fwe10')->table('uf_sxtmcdyi')->insert($insert);
        }
    }

    //同步区域列表
    public function updateAreaList()
    {
        //查询使用海康服务器的矿区id
        $mine_id_list = MineListExt::where('is_hak', 1)->pluck('mine_id')->all();

        //海康区域同步开始时间
//        $syn['hk_area_start_time'] = date('Y-m-d H:i:s');

        foreach ($mine_id_list as $key => $val) {
//            echo '区域'.$val.'开始:'.date('Y-m-d H:i:s');
//            if($val == 968){
//                //宁煤区域同步开始时间
//                $syn['nm_area_start_time'] = date('Y-m-d H:i:s');
//
//                MineServices::getHaiKangArea($val, CameraEnum::REQUEST_TYPE_LOCAL);
//
//                //宁煤区域同步结束时间
//                $syn['nm_area_end_time'] = date('Y-m-d H:i:s');
//            }else{
                MineServices::getHaiKangArea($val, CameraEnum::REQUEST_TYPE_LOCAL);
//            }
//            echo '区域'.$val.'结束:'.date('Y-m-d H:i:s');
        }

        //海康区域同步结束时间
//        $syn['hk_area_end_time'] = date('Y-m-d H:i:s');
        //天地区域同步开始时间
//        $syn['td_area_start_time'] = date('Y-m-d H:i:s');
        //同步天地伟业矿区区域
        $mine_list_ext = DB::table('mine_list_ext')
            ->select('mine_list_ext.*','mine_list.parent_id')
            ->leftJoin('mine_list','mine_list.id','=','mine_list_ext.mine_id')
            // ->where('mine_list.parent_id',0)
            ->where('mine_list.parent_id',123456)
            ->where('mine_list_ext.is_hak',MineEnum::IS_HAK_TD)
            ->where('mine_list_ext.ip','!=','')
            ->where('mine_list_ext.port','!=','')
            ->where('mine_list_ext.key','!=','')
            ->where('mine_list_ext.secret','!=','')
            ->where('mine_list_ext.deleted_at',null)->get();

        if(sizeof($mine_list_ext)>0){
            for($i=0;$i<sizeof($mine_list_ext);$i++){//循环同步所有天地伟业矿区

                Input::replace(
                    [
                        'parentOrgIds'   => $mine_list_ext[$i]->sOrgId,
                        'url'      => $mine_list_ext[$i]->ip.":".$mine_list_ext[$i]->port,
                        'username' => $mine_list_ext[$i]->key,
                        'password' => $mine_list_ext[$i]->secret,
                        'sysId'     => 'PG',
                    ]
                );
                $tdwy = new TdwyController();
                $region = $tdwy->getRegionByOrgId();
                $mine_id = $mine_list_ext[$i]->mine_id;
                //更新区域
                $result['data']['list'] = $tdwy->saveTrees($region, $mine_id);
            }
        }

        //天地区域同步结束时间
//        $syn['td_area_end_time'] = date('Y-m-d H:i:s');
//        DB::table('syn')->insert($syn);
    }

    public function updateCameraList()
    {
        //海康
        //查询存在index_code列表
        $index_code_list = MineList::where('index_code', '!=', null)->where('deleted_at',null)->get();

        //不在线矿区
        $mine_list_ext = DB::table('mine_list_ext')->where('deleted_at',null)->where('ip','!=',null)->get();
        for($i=0;$i<count($mine_list_ext);$i++){
            $ip = explode('://',$mine_list_ext[$i]->ip)[1];
            $port = $mine_list_ext[$i]->port;
            if($fp = @fsockopen($ip,$port,$errno,$errstr,1)){
                fclose($fp);
            }else{
                $arr[] = $mine_list_ext[$i]->mine_id;
            }
        }

        //去掉不在线矿区
        foreach($index_code_list as $k=>$v){
            $degree = explode('|',$v->degree);
            if(isset($arr)){
                for($i=0;$i<count($arr);$i++){
                    if($degree[0] == $arr[$i]){
                        unset($index_code_list[$k]);
                    }
                }
            }
        }

        //去掉洗选
//        foreach($index_code_list as $k=>$v){
//            $degree = explode('|',$v->degree);
//            if($degree[0] == 66){
//                unset($index_code_list[$k]);
//            }
//        }

        //天地伟业矿区
        $mine_ext = DB::table('mine_list_ext')
            ->select('mine_list_ext.*','mine_list.parent_id')
            ->leftJoin('mine_list','mine_list.id','=','mine_list_ext.mine_id')
//            ->where('mine_list.parent_id',0)
            ->where('mine_list.parent_id',123456)
            ->where('mine_list_ext.is_hak',MineEnum::IS_HAK_TD)
            ->where('mine_list_ext.ip','!=','')
            ->where('mine_list_ext.port','!=','')
            ->where('mine_list_ext.key','!=','')
            ->where('mine_list_ext.secret','!=','')
            ->where('mine_list_ext.deleted_at',null)->get();

        $mine_id_list = [];
        foreach($mine_ext as $k=>$v){
            $mine_id_list[$k] = $v->mine_id;
        }
        //清除天地伟业的区域
        if(count($mine_id_list) > 0){
            foreach($index_code_list as $k=>$v){
                if(in_array(explode('|',$v->degree)[0],$mine_id_list)){
                    unset($index_code_list[$k]);
                }
            }
        }

        //海康摄像头同步开始时间
//        $syn_id = DB::table('syn')->orderBy('id','desc')->pluck('id')->first();
//        $syn1['hk_camera_start_time'] = date('Y-m-d H:i:s');
//        DB::table('syn')->where('id',$syn_id)->update($syn1);

//        $nm_camera_time = 0;
//        echo '海康摄像头开始:'.date('Y-m-d H:i:s');
        foreach ($index_code_list as $key => $val) {
            //宁煤同步摄像头开始时间
//            $nm_start_time = time();
//            $timetotal['mine_id'] = $val->id;
//            $timetotal['interface_start_time'] = date('Y-m-d H:i:s');

            $degree       = explode('|', $val->degree);
//            $trans_result = CameraServices::getHaiKangTransList($degree[0]);
            if($degree[0] == 6356){//洗选风险作业执法记录仪
                $degree[0] = $degree[1];
            }
            $result       = CameraServices::getHaiKangCamera($degree[0], $val->index_code, CameraEnum::CAMERA_TYPE_ALL);

//            $info = DB::table('syn')->select('interface','handle')->orderBy('id','desc')->first();
//            $syn2['interface'] = $info->interface . $val->id . ':' . (time() - $nm_start_time) . ';';
//            $timetotal['interface_end_time'] = date('Y-m-d H:i:s');
//            $timetotal['handle_start_time'] = date('Y-m-d H:i:s');
//            DB::table('syn')->where('id',$syn_id)->update($syn2);

//            $handle_start = time();

            if ($result['status']) {
                $trans_arr = [];
//                foreach ($trans_result['data'] as $tk => $tv) {
//                    $trans_arr[$tv['indexCode']]['ip'] = $tv['ip'];
//                }

                $index_code_arr = [];
//                $arr_count = count($result['data']);
                foreach ($result['data'] as $k => $v) {
                    $ip         = '';
                    $port       = '';
                    $com_number = '';
//                    if (isset($trans_arr[$v['trans_code']])) {
//                        $ip         = $trans_arr[$v['trans_code']]['ip'];
//                        $port       = CameraEnum::HAK_DEFAULT_PORT;
//                        $com_number = $v['com_number'];
//                    }

                    //当前摄像头index_code数组
                    $index_code_arr[$k] = $v['camera_id'];

                    //更新时间距当前时间天数
//                    $update_time = strtotime($v['updateTime']);
//                    $update_time = number_format((time() - $update_time)/(60*60*24),2);

                    $params = [
                        'mine_id'       => $val->id,
                        'camera_name'   => $this->transformCameraName($v['camera_name']),
                        'index_code'    => $v['camera_id'],
                        'revert_id'     => CameraEnum::CAMERA_DEFAULT_REVERT_ID,
                        'camera_source' => CameraEnum::CAMERA_SOURCE_2,
                        'ip'            => $ip,
                        'port'          => $port,
                        'com_number'    => $com_number,
                        //'sort'          => $arr_count - $k,
//                        'camera_status'        => $v['camera_status'],
                    ];

//                    if($result['is_update'] == true){
                        CameraList::updateOrCreate(['index_code' => $v['camera_id']], $params);
//                    }elseif($update_time < 10){
//                        CameraList::updateOrCreate(['index_code' => $v['camera_id']], $params);
//                    }

                }

                //删除不存在的摄像头
                CameraList::where('mine_id', $val->id)->where('index_code', '!=', NULL)->whereNotIn('index_code', $index_code_arr)->delete();
            }

//            $syn2['handle'] = $info->handle . $val->id . ':' . (time() - $handle_start) . ';';
//            $timetotal['handle_end_time'] = date('Y-m-d H:i:s');
//            DB::table('syn')->where('id',$syn_id)->update($syn2);
//            DB::table('timetotal')->insert($timetotal);

//            if($val->parent_id == 968){
//                //宁煤同步摄像头时间
//                $nm_camera_time += time() - $nm_start_time;
//            }

        }
//        echo '天地伟业摄像头开始:'.date('Y-m-d H:i:s');
//        $syn['nm_camera_time'] = $nm_camera_time;//宁煤单个区域同步时间
//        DB::table('syn')->where('id',$syn_id)->update($syn);

        //海康摄像头同步结束时间
//        $syn['hk_camera_end_time'] = date('Y-m-d H:i:s');
//        DB::table('syn')->where('id',$syn_id)->update($syn);

        //天地伟业需要同步的矿区
        $mine_list_ext = DB::table('mine_list_ext')
            ->select('mine_list_ext.*','mine_list.parent_id')
            ->leftJoin('mine_list','mine_list.id','=','mine_list_ext.mine_id')
//            ->where('mine_list.parent_id',1)//0,先不同步天地伟业摄像头
            ->where('mine_list.parent_id',123456)//0,先不同步天地伟业摄像头
            ->where('mine_list_ext.is_hak',MineEnum::IS_HAK_TD)
            ->where('mine_list_ext.ip','!=','')
            ->where('mine_list_ext.port','!=','')
            ->where('mine_list_ext.key','!=','')
            ->where('mine_list_ext.secret','!=','')
            ->where('mine_list_ext.deleted_at',null)->get();

        //天地摄像头同步开始时间
//        $syn['td_camera_start_time'] = date('Y-m-d H:i:s');
//        DB::table('syn')->where('id',$syn_id)->update($syn);

        if(sizeof($mine_list_ext)>0){
            for($i=0;$i<sizeof($mine_list_ext);$i++){//循环同步所有天地伟业矿区
                //矿区下所有区域
                $mine_list = DB::table('mine_list')
                    ->where('degree','like',$mine_list_ext[$i]->mine_id.'|%')
                    ->where('index_code','!=',null)->get();
                foreach($mine_list as $k=>$v){
                    $mine_id = $v->id;
                    Input::replace(
                        [
                            'parentOrgIds'   => $v->index_code,
                            'url'      => $mine_list_ext[$i]->ip.":".$mine_list_ext[$i]->port,
                            'username' => $mine_list_ext[$i]->key,
                            'password' => $mine_list_ext[$i]->secret,
                            'sysId'     => 'PG',
                        ]
                    );
                    //获取区域下的子区域
                    $tdwy = new TdwyController();
                    $region = $tdwy->getRegionByOrgId();

                    //获取最底层区域摄像头列表
                    if (empty($region['content'])) {
                        Input::replace(
                            [
                                'url'      => $mine_list_ext[$i]->ip.":".$mine_list_ext[$i]->port,
                                'username' => $mine_list_ext[$i]->key,
                                'password' => $mine_list_ext[$i]->secret,
                                'sysId'     => 'PG',
                                'sOrgId'   => $v->index_code,
                                'ip'       => $mine_list_ext[$i]->ip,
                                'port'     => $mine_list_ext[$i]->port,
                            ]
                        );
                        //获取设备列表
                        $tdwy = new TdwyController();
                        $device_list = $tdwy->queryDeviceList();
                        $device_index_code = [];
                        //删除区域下所有摄像头
//                        DB::table('camera_list')->where('mine_id',$v->id)->delete();
                        if(isset($device_list['content']) && sizeof($device_list['content'])>0){
                            for($j=0;$j<sizeof($device_list['content']);$j++){//循环添加区域下所有摄像头
                                if(isset($device_list['content'][$j]['sId'])){//筛选有sId的摄像头
                                    if($device_list['content'][$j]['onlineStatus'] == 0){
                                        $device_list['content'][$j]['onlineStatus'] = 2;
                                    }
                                    $params = [
                                        'mine_id'       => $v->id,
                                        'ip'            => $mine_list_ext[$i]->ip,
                                        'port'          => 1554,
                                        'revert_id'     => CameraEnum::CAMERA_DEFAULT_REVERT_ID,
                                        'sort'          => sizeof($device_list['content']) - $j,
                                        'camera_name'   => $device_list['content'][$j]['sName'],
                                        'camera_source' => CameraEnum::CAMERA_SOURCE_2,
                                        'index_code'    => $device_list['content'][$j]['sId'],
                                        'video_recorder'=> CameraEnum::VIDEO_RECORDER_TD,
                                        'camera_status' => $device_list['content'][$j]['onlineStatus'],
                                    ];
                                    CameraList::updateOrCreate(['index_code' => $device_list['content'][$j]['sId']], $params);
                                }
                                $device_index_code[$j] = $device_list['content'][$j]['sId'];
                            }
                        }
                        //删除不存在的摄像头
                        CameraList::where('mine_id', $mine_id)->where('index_code', '!=', NULL)->whereNotIn('index_code', $device_index_code)->delete();
                    }
                }
            }
        }

        //天地摄像头同步结束时间
//        $syn['td_camera_end_time'] = date('Y-m-d H:i:s');
//        DB::table('syn')->where('id',$syn_id)->update($syn);
//        echo '大华摄像头开始:'.date('Y-m-d H:i:s');
        //同步大华视频服务器摄像头
        $tdwy = new TdwyController();
        $tdwy->dahuaCamera();
    }

    //转义摄像头名称中的特殊字符
    public function transformCameraName($camera_name)
    {
        $camera_name = trim($camera_name);
        $camera_name = str_replace('#', '号', $camera_name);
        $camera_name = str_replace(' ', '-', $camera_name);
        $camera_name = str_replace('+', '', $camera_name);
        return $camera_name;
    }
}