video_system/app/Console/Commands/CameraStatusHistory

<?php

namespace App\Console\Commands;

use App\Enum\ApiEnum;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

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

    /**
     * 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()
    {
        $this->cameraStatusHistory();
    }

    //摄像头统计历史数据
    public function cameraStatusHistory()
    {
        $result['status'] = true;
        $result['msg']    = ApiEnum::RETURN_SUCCESS;

        //宁煤集团
        $ningmei = DB::table('mine_list')->where('title',config('mine_hls'))->get();

        $mine_all = DB::table('mine_list')->where('deleted_at',null)->get();//所有区域
        $mine_ningmei = [];//宁煤集团所有区域
        foreach($mine_all as $k=>$v){
            if(explode('|',$v->degree)[0] == $ningmei[0]->id){
                $mine_ningmei[] = $v->id;
            }
        }

        //宁煤在离线数量
        $data[0]['title'] = '宁煤公司';
        $data[0]['mine_code'] = $ningmei[0]->slug;
        $data[0]['total'] = DB::table('camera_list')->whereIn('mine_id',$mine_ningmei)->where('deleted_at',null)->count();
        $data[0]['total_online'] = DB::table('camera_list')->whereIn('mine_id',$mine_ningmei)->where('camera_status',1)->where('deleted_at',null)->count();
        $data[0]['total_offline'] = $data[0]['total'] - $data[0]['total_online'];
        $data[0]['rate'] = round($data[0]['total_online'] / $data[0]['total'] * 100,2);

        //宁煤下各矿在离线数量
        $mine_list = DB::table('mine_list')->where('parent_id',$ningmei[0]->id)->where('deleted_at',null)->get();
        foreach($mine_list as $k => $v){
            $mine_use = [];//宁煤下每个矿所有区域
            foreach($mine_all as $key=>$value){
                if(count(explode('|',$value->degree)) > 1){
                    if(explode('|',$value->degree)[0] == $ningmei[0]->id && explode('|',$value->degree)[1] == $v->id){
                        $mine_use[] = $value->id;
                    }
                }
            }

            $total = DB::table('camera_list')->whereIn('mine_id',$mine_use)->where('is_show',1)->where('deleted_at',null)->count();
            $total_online = DB::table('camera_list')->whereIn('mine_id',$mine_use)->where('is_show',1)->where('camera_status',1)->where('deleted_at',null)->count();

            if($total == 0){
                $rate = "0";
            }else{
                $rate = round($total_online / $total * 100,2);
            }

            if($v->slug == 'NingXiaMeiYeTeShuZuoYeJianKongShiPin'){
                $res['title'] = $v->title;
                $res['mine_code'] = $v->slug."_jituan";
                $res['total'] = $total;
                $res['total_online'] = $total_online;
                $res['total_offline'] = $total - $total_online;
                $res['rate'] = $rate;
            }else{
                array_push($data,[
                    'title'=>$v->title,
                    'mine_code'=>$v->slug."_jituan",
                    'total'=>$total,
                    'total_online'=>$total_online,
                    'total_offline'=>$total - $total_online,
                    'rate'=>$rate
                ]);
            }
        }

        if(isset($res)){
            array_push($data,[
                'title'=>$res['title'],
                'mine_code'=>$res['mine_code'],
                'total'=>$res['total'],
                'total_online'=>$res['total_online'],
                'total_offline'=>$res['total_offline'],
                'rate'=>$res['rate']
            ]);
        }

        $timestamp = strtotime(date('Y-m-d H:i:s'));
        $hour = date('G', $timestamp);
        $minute = date('i', $timestamp);
        $month = date('n', $timestamp);
        $day = date('j', $timestamp);
        if($minute == '00'){
            $minute = '';
        }
        if ($hour < 12) {
            $time = $month."月".$day."号上午".$hour."时".$minute;
        } elseif ($hour >= 12 && $hour < 18) {
            $time = $month."月".$day."号下午".$hour."时".$minute;
        } else {
            $time = $month."月".$day."号晚上".$hour."时".$minute;
        }

        $unit_max_name = '';
        $unit_max_rate = 0;
        $unit_min_name = '';
        $unit_min_rate = 100;

        $mzy_max_name = '';
        $mzy_max_rate = 0;
        $mzy_min_name = '';
        $mzy_min_rate = 100;

        $other_max_name = '';
        $other_max_rate = 0;
        $other_min_name = '';
        $other_min_rate = 100;

        if(count($data)>0){
            for($i=0;$i<count($data);$i++){
                if (strpos($data[$i]['title'], '煤矿') !== false) {

                    if($data[$i]['rate'] > $unit_max_rate){
                        $unit_max_rate = $data[$i]['rate'];
                    }
                    if($data[$i]['rate'] < $unit_min_rate){
                        $unit_min_rate = $data[$i]['rate'];
                    }

                }elseif(strpos($data[$i]['title'], '煤制油') !== false){

                    if($data[$i]['rate'] > $mzy_max_rate){
                        $mzy_max_rate = $data[$i]['rate'];
                    }
                    if($data[$i]['rate'] < $mzy_min_rate){
                        $mzy_min_rate = $data[$i]['rate'];
                    }

                }else{

                    if($data[$i]['rate'] > $other_max_rate){
                        $other_max_rate = $data[$i]['rate'];
                    }
                    if($data[$i]['rate'] < $other_min_rate){
                        $other_min_rate = $data[$i]['rate'];
                    }

                }
            }
            for($i=0;$i<count($data);$i++){
                if (strpos($data[$i]['title'], '煤矿') !== false) {

                    if($data[$i]['rate'] == $unit_max_rate){
                        $unit_max_name = $unit_max_name.'、'.$data[$i]['title'];
                    }
                    if($data[$i]['rate'] == $unit_min_rate){
                        $unit_min_name = $unit_max_name.'、'.$data[$i]['title'];
                    }

                }elseif(strpos($data[$i]['title'], '煤制油') !== false){

                    if($data[$i]['rate'] == $mzy_max_rate){
                        $mzy_max_name = $mzy_max_name.'、'.$data[$i]['title'];
                    }
                    if($data[$i]['rate'] == $mzy_min_rate){
                        $mzy_min_name = $mzy_max_name.'、'.$data[$i]['title'];
                    }

                }else{

                    if($data[$i]['rate'] == $other_max_rate){
                        $other_max_name = $other_max_name.'、'.$data[$i]['title'];
                    }
                    if($data[$i]['rate'] == $other_min_rate){
                        $other_min_name = $other_max_name.'、'.$data[$i]['title'];
                    }

                }
            }
        }

        $unit_max_name = mb_substr($unit_max_name, 1);
        $unit_min_name = mb_substr($unit_min_name, 1);
        $mzy_max_name = mb_substr($mzy_max_name, 1);
        $mzy_min_name = mb_substr($mzy_min_name, 1);
        $other_max_name = mb_substr($other_max_name, 1);
        $other_min_name = mb_substr($other_min_name, 1);

        $result['data'] = '本周公司视频摄像头在线数'.$data[0]['total_online'].'个,'.'在线率为'.$data[0]['rate'].'%('.$time
            .'统计)。其中：①煤炭生产建设单位在线率排名第一的单位是'.$unit_max_name.',在线率达到'.$unit_max_rate
            .'%;排名末位的单位是'.$unit_min_name.',在线率达到'.$unit_min_rate
            .'%。②煤制油化工单位在线率排名第一的单位是'.$mzy_max_name.',在线率达到'.$mzy_max_rate.'%;排名末位的单位是'
            .$mzy_min_name.',在线率达到'.$mzy_min_rate.'%。③其他单位在线率排名第一的单位是'.$other_max_name
            .',在线率达到'.$other_max_rate.'%;排名末位的单位是'.$other_min_name.',在线率达到'.$other_min_rate.'%。';

        DB::table('camera_status_history')->insert(['content'=>$result['data']]);
    }
}