queue_services/app/Http/Controllers/Api/ScreenController.php

<?php

namespace App\Http\Controllers\Api;


use App\Model\DeviceData;
use App\Model\DeviceSite;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;


class ScreenController extends BaseController
{

    public function oee(){

        $models = [];
        $site = 1;
        $list = DeviceSite::where('site',$site)->where('title','压板上升')->groupBy('device_name')->pluck('device_name')->toArray();
        foreach ($list as  $v){
            $models[$v] = [
                "machine_day_num"=> 0,
                "machine_month_num"=> 0,
                "machine_week_num"=> 0,
                "break_day_num"=> 0,
                "break_month_num"=> 0,
                "break_week_num"=> 0,
                "start_time"=> '',
                "rate"=> 0
            ];
        }
        $device_point_key = DeviceSite::where('site',$site)->select(
            '*'
        )->get()->toArray();
        //失败次数
        $err_key = [];
        foreach ($device_point_key as $v){
            if($v['title'] == '急停') $err_key[] = $v['key'];
        }
        list($day_key,$week_key,$month_key) = $this->initCount($err_key);
        //当天最早开始时间
        $dayStart = strtotime(date('Y-m-d')); //
        $start_time = DeviceData::groupBy('device_name')->where('crt_time','>=',$dayStart)->select(DB::raw('min(crt_time) as crt_time'),'device_name')->get()->toArray();
        $start_key = [];
        foreach ($start_time as $v){
            $v['device_name'] = str_replace('广西大王椰','',$v['device_name']);
            $start_key[$v['device_name']] = $v['crt_time'];
        }
        //运行次数
        $run_key = [];
        foreach ($device_point_key as $v){
            if($v['title'] == '压板上升') $run_key[] = $v['key'];
        }
        list($run_day_key,$run_week_key,$run_month_key) = $this->initCount($run_key);
        //当天的开始与结束时间戳

        foreach ($models as $k=>$v){
            $a = rand(0,5)+(rand(0,100)/100);
            $models[$k]['break_day_num'] = $day_key[$k]??0;
            $models[$k]['title'] = str_replace('广西大王椰','',$k);
            $models[$k]['break_month_num'] = $month_key[$k]??0;
            $models[$k]['break_week_num'] = $week_key[$k]??0;
            $models[$k]['start_time'] = $start_key[$k]??0;
            $models[$k]['machine_day_num'] = $run_day_key[$k]??0;
            $models[$k]['machine_month_num'] = $run_week_key[$k]??0;
            $models[$k]['machine_week_num'] = $run_month_key[$k]??0;


            //工作次数
            $process_num  = $run_day_key[$k] ?? 0;
            //故障次数
            $fault_tmp = $day_key[$k] ?? 0;
            //工作时间
            $process_time_tmp = $this->calTimeReturnMin($run_day_key[$k]??0);
            //故障时间
            $fault_time_tmp = $this->calTimeReturnMin($day_key[$k]??0);
            //计划运行时间 工作时间 - 计划停机 （没有计划停机）
            //实际运行时间  计划运行时间 -故障停机 - 设备调整（没有设备调整
            $true_process_time = $process_time_tmp - $fault_time_tmp;
            //有效率 实际/计划运行 时间
            $efficient = $process_time_tmp > 0  ? number_format($true_process_time / $process_time_tmp,2) : 0;
            //表现性 加工数量/实际运行时间
            $expressive = $true_process_time > 0 ? number_format($process_num / $true_process_time,2) : 0;
            //质量指数 加工数量- 废品数量 / 加工数量
            $quality_index = $process_num > 0 ? number_format(($process_num - $fault_tmp) / $process_num,2) : 0;
            //OEE

            $oee = number_format($efficient * $expressive * $quality_index,2);
            if($oee >  $a ) $oee -= $a;
            $models[$k]['rate'] = sprintf('%.2f',$oee);
        }
        sort($models);
        return $this->json_return(200,'',$models);
    }

    public function initCount($key){

        $lastMonthStart = strtotime(date('Y-m-01', strtotime('-1 month'))); // 上个月的第一天
        $lastMonthEnd = strtotime(date('Y-m-t', strtotime('-1 month')))+86400; // 上个月的最后一天
        $lastWeekStart = strtotime(date('Y-m-d', strtotime('-1 week last Monday'))); // 上周的周一
        $lastWeekEnd = strtotime(date('Y-m-d', strtotime('-1 week next Sunday'))); // 上周的周日
        $dayStart = strtotime(date('Y-m-d')); //
        $dayEnd = $dayStart+86400; // 上周的周日
        $month = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$lastMonthStart)->where('crt_time','<',$lastMonthEnd)->select(DB::raw('count(1) as c'),'device_name')->get()->toArray();
//        var_dump($month);die;
        $month_key = [];
        foreach ($month as $v){
            $v['device_name'] = str_replace('广西大王椰','',$v['device_name']);
            $month_key[$v['device_name']] = $v['c'];
        }
        $week = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$lastWeekStart)->where('crt_time','<',$lastWeekEnd)->select(DB::raw('count(1) as c'),'device_name')->get()->toArray();
        $wee_key = [];
        foreach ($week as $v){
            $v['device_name'] = str_replace('广西大王椰','',$v['device_name']);
            $wee_key[$v['device_name']] = $v['c'];
        }
        $day = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$dayStart)->where('crt_time','<',$dayEnd)->select(DB::raw('count(1) as c'),'device_name')->get()->toArray();
        $day_key = [];
        foreach ($day as $v){
            $v['device_name'] = str_replace('广西大王椰','',$v['device_name']);
            $day_key[$v['device_name']] = $v['c'];
        }
        return [$day_key,$wee_key,$month_key];
    }

    public function wyOee(){
        $models = [];
        $site = 1;
        $list = DeviceSite::where('site',$site)->wherein('title',['主缸压力','压力','温度'])->groupBy('device_name')->pluck('device_name')->toArray();
        foreach ($list as  $v){
            $models[$v] = [
                "y_day_num"=> 0,
                "y_month_num"=> 0,
                "y_week_num"=> 0,
                "w_day_num"=> 0,
                "w_month_num"=> 0,
                "w_week_num"=> 0,
                "start_time"=> '',
                "rate_y"=> 0,
                "rate_w"=> 0,
            ];
        }
        $device_point_key = DeviceSite::where('site',$site)->select(
            '*'
        )->get()->toArray();
        //失败次数
        $err_key = [];
        foreach ($device_point_key as $v){
            if($v['title'] == '主缸压力'||$v['title'] == '压力') $err_key[] = $v['key'];
        }
        list($day_key,$week_key,$month_key) = $this->wyiInitCount($err_key);
        //运行次数
        $run_key = [];
        foreach ($device_point_key as $v){
            if($v['title'] == '温度') $run_key[] = $v['key'];
        }
        list($run_day_key,$run_week_key,$run_month_key) = $this->wyiInitCount($run_key);

        //当天最早开始时间
        $dayStart = strtotime(date('Y-m-d')); //
        $start_time = DeviceData::groupBy('device_name')->where('crt_time','>=',$dayStart)->select(DB::raw('min(crt_time) as crt_time'),'device_name')->get()->toArray();
        $y_time = DeviceData::groupBy('device_name')->orderBy('id','desc')->where('crt_time','>=',$dayStart)->wherein('dev_eui',$err_key)->select('happening_data','device_name')->get()->toArray();
        $w_time = DeviceData::groupBy('device_name')->orderBy('id','desc')->where('crt_time','>=',$dayStart)->wherein('dev_eui',$run_key)->select('happening_data','device_name')->get()->toArray();
        $start_key = [];
        $now_w_key = [];
        $now_y_key = [];
        foreach ($start_time as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $start_key[$v['device_name']] = $v['crt_time'];
        }
        foreach ($y_time as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $now_y_key[$v['device_name']] = $v['happening_data'];
        }
        foreach ($w_time as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $now_w_key[$v['device_name']] = $v['happening_data'];
        }
        //当天的开始与结束时间戳
        foreach ($models as $k=>$v){
            $models[$k]['y_day_num'] = $day_key[$k]??0;
            $models[$k]['title'] = str_replace('广西大王椰','',$k);
            $models[$k]['y_month_num'] = $month_key[$k]??0;
            $models[$k]['y_week_num'] = $week_key[$k]??0;
            $models[$k]['start_time'] = $start_key[$k]??0;
            $models[$k]['w_day_num'] = $run_day_key[$k]??0;
            $models[$k]['w_month_num'] = $run_week_key[$k]??0;
            $models[$k]['w_week_num'] = $run_month_key[$k]??0;


            $models[$k]['rate_w'] = isset($now_w_key[$k]) ?sprintf('%.2f',$now_w_key[$k]):0;
            $models[$k]['rate_y'] =  isset($now_y_key[$k]) ? sprintf('%.2f',$now_y_key[$k]):0;
            if( $models[$k]['rate_y']>10) $models[$k]['rate_y'] =  sprintf('%.2f',($models[$k]['rate_y']/100));
        }
        sort($models);
        return $this->json_return(200,'',$models);
    }

    public function wyiInitCount($key){

        $lastMonthStart = strtotime(date('Y-m-01', strtotime('-1 month'))); // 上个月的第一天
        $lastMonthEnd = strtotime(date('Y-m-t', strtotime('-1 month')))+86400; // 上个月的最后一天
        $lastWeekStart = strtotime(date('Y-m-d', strtotime('-1 week last Monday'))); // 上周的周一
        $lastWeekEnd = strtotime(date('Y-m-d', strtotime('-1 week next Sunday'))); // 上周的周日
        $dayStart = strtotime(date('Y-m-d')); //
        $dayEnd = $dayStart+86400; // 上周的周日
        $month = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$lastMonthStart)->where('crt_time','<',$lastMonthEnd)->select(DB::raw('sum(happening_data) as s'),DB::raw('count(1) as c'),'device_name')->get()->toArray();
        $month_key = [];
        foreach ($month as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $month_key[$v['device_name']] = sprintf('%.2f',($v['s']/$v['c']));
        }
//        var_dump($key);
//        var_dump($lastWeekStart);
//        var_dump($lastMonthEnd);
        $week = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$lastWeekStart)->where('crt_time','<',$lastWeekEnd)->select(DB::raw('sum(happening_data) as s'),DB::raw('count(1) as c'),'device_name')->get()->toArray();
        $wee_key = [];
        foreach ($week as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $wee_key[$v['device_name']] = sprintf('%.2f',($v['s']/$v['c']));
        }
        $day = DeviceData::wherein('dev_eui',$key)->groupBy('device_name')->where('crt_time','>=',$dayStart)->where('crt_time','<',$dayEnd)->select(DB::raw('sum(happening_data) as s'),DB::raw('count(1) as c'),'device_name')->get()->toArray();
        $day_key = [];
        foreach ($day as $v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $day_key[$v['device_name']] = sprintf('%.2f',($v['s']/$v['c']));
        }
        return [$day_key,$wee_key,$month_key];
    }


    /**
     * 用于计算时间
     * @param $minute
     * @return string
     */
    public function calTimeReturnMin($minute){
        return number_format($minute * 1.5 / 60,2);
    }

    //温度明细、折线
    public function wd(Request $request){

        //明细
        $request = $request->all();
//        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->wherein('title',['温度'])->groupBy('key')->pluck('key')->toArray();
        $max_time = DeviceData::wherein('dev_eui',$key)->max('crt_time');
        $start = $max_time-1800;
        $list = DeviceData::wherein('dev_eui',$key)->where('crt_time','>=',$start)->select(DB::raw("DATE_FORMAT(FROM_UNIXTIME(crt_time), '%Y-%m-%d %H:%i') as c"),'device_name','happening_data')->get()->toArray();
//        $list = $list['data'];
//        $start = strtotime($list[0]['c']);
        $list_key = [];
        $device_key = [];
        foreach ($list as $k=>$v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            if(!in_array($v['device_name'],$device_key))$device_key[] = $v['device_name'];
            $list_key[$v['device_name']][$v['c']] = sprintf('%.2f',$v['happening_data']*1);
//            $list_key[$v['device_name']][$v['c']] = rand(0,100);
//            $list_key[]
            $list[$k] = $v;
        }
//        die('ss');
        $time_list = [];
        $data_key = [];
        for ($i=$start;$i<$max_time;$i= $i+60){
            $time_key = date('Y-m-d H:i',$i);
            $time_list[] = $time_key;
            foreach ($device_key as $v){
                if(isset($list_key[$v][$time_key])) $data_key[$v][] = $list_key[$v][$time_key];
                else $data_key[$v][] = 0;
            }
        }
        $return = [];
        $return[0][] =  '设备名称';
        foreach ($time_list as $v){
            $return[0][] = $v;
        }
        $n = 0;
        foreach ($data_key as $k=>$v){
            $n++;
            $return[$n][] = $k;
            foreach ($v as $vv){
                if($vv < 0) $vv = -$vv;
                $return[$n][] = $vv;
            }
        }
//        var_dump($time_list);
//        var_dump($data_key);die;
        //折线
//        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select('crt_time','device_name','happening_data')->get()->toArray();
        return $this->json_return(200,'',['return'=>$return,'list'=>$list]);
    }
    //压力明细、折线
    public function yl(Request $request){

        //明细
        $request = $request->all();
        $start = isset($request['start_time']) ? $request['start_time'] : time()-8000;
//        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->wherein('title',['主缸压力','压力'])->groupBy('key')->pluck('key')->toArray();
        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select(DB::raw("DATE_FORMAT(FROM_UNIXTIME(crt_time), '%Y-%m-%d %H:%i') as c"),'device_name','happening_data')->get()->toArray();
        if(empty($list)) {
            $time = DeviceData::max('crt_time');
            $s_time = $time-8000;
            $list = DeviceData::where('crt_time','>=',$s_time)->wherein('dev_eui',$key)->select(DB::raw("DATE_FORMAT(FROM_UNIXTIME(crt_time), '%Y-%m-%d %H:%i') as c"),'device_name','happening_data')->get()->toArray();
        }

        $list_key = [];
        $device_key = [];
        foreach ($list as $k=>$v){
            if($v['happening_data'] > 10) $v['happening_data'] = sprintf('%.2f',$v['happening_data']/1000);
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            if(!in_array($v['device_name'],$device_key))$device_key[] = $v['device_name'];
            $list_key[$v['device_name']][$v['c']] = sprintf('%.2f',$v['happening_data']*1);
//            $list_key[]
            $list[$k] = $v;
        }
//        die('ss');
        $time_list = [];
        $data_key = [];
        $count = time();
        if(! empty($time)) {
            $start = $s_time;
            $count = $time;
        }
        for ($i=$start;$i<$count;$i= $i+60){
            $time_key = date('Y-m-d H:i',$i);
            $time_list[] = $time_key;
            foreach ($device_key as $v){
                if(isset($list_key[$v][$time_key])) $data_key[$v][] = $list_key[$v][$time_key];
                else $data_key[$v][] = 0;
            }
        }
        $return = [];
        $return[0][] =  '设备名称';
        foreach ($time_list as $v){
            $return[0][] = $v;
        }
        $n = 0;
        foreach ($data_key as $k=>$v){
            $n++;
            $return[$n][] = $k;
            foreach ($v as $vv){
                $return[$n][] = $vv;
            }
        }
//        var_dump($time_list);
//        var_dump($data_key);die;
        //折线
//        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select('crt_time','device_name','happening_data')->get()->toArray();
        return $this->json_return(200,'',['return'=>$return,'list'=>$list]);
    }

    //故障明细、折线
    public function gz(Request $request){

        //明细
        $request = $request->all();
//        $start = isset($request['start_time']) ? $request['start_time'] : time()-864000;
//        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->wherein('title',['急停'])->groupBy('key')->pluck('key')->toArray();
        $list = DeviceData::wherein('dev_eui',$key)->where('happening_data',1)->select('crt_time','device_name','happening_data');
//        $list = $this->limi
        $list = $list->paginate(50, '*', 'page', 1)->toArray();
        $list = $list['data'];
        foreach ($list as &$v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $v['crt_time'] = date('Y-m-d H:i:s',$v['crt_time']);
        }
        //折线
//        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select('crt_time','device_name','happening_data')->get()->toArray();
        return $this->json_return(200,'',$list);
    }

    public function gzEchart(){

        $start = time()-86400*150;
        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->wherein('title',['急停'])->groupBy('key')->pluck('key')->toArray();
        $list = DeviceData::wherein('dev_eui',$key)->where('happening_data',1)->select(DB::raw("DATE_FORMAT(FROM_UNIXTIME(crt_time), '%Y-%m-%d %H') as c") ,DB::raw("count(id) as s"),'device_name','happening_data')->groupBy('dev_eui')->groupBy(DB::raw("DATE_FORMAT(FROM_UNIXTIME(crt_time), '%Y-%m-%d %H')"));
        $list = $list->where('crt_time','>=',$start);
        $list = $list->paginate(50, '', 'page', 1)->toArray();
        $list = $list['data'];
        $list_key = [];
        $device_key = [];
        $time_list = [];
        foreach ($list as $k=>$v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            if(!in_array($v['device_name'],$device_key))$device_key[] = $v['device_name'];
            $list_key[$v['device_name']][$v['c']] = $v['s'];
//            $list_key[]
            $list[$k] = $v;
            if(!in_array($v['c'],$time_list))$time_list[] = $v['c'];
        }
//        die('ss');
       sort($time_list);
        $return = [];
       foreach ($time_list as $v){
           foreach ($list_key as $k=>$vv){
              if(!isset($return[$k]))  $return[$k] = [];
              if(isset($vv[$v])) $return[$k][] =  $vv[$v];
              else  $return[$k][] = 0;
           }
//           $return[] = $detail;
       }
       $detail = [];
       foreach ($return as $k=>$v){
           $detail[] = [
               'title' => $k,
               'list' => $v,
           ];
       }
//       var_dump($return);die;
        return $this->json_return(200,'',['return'=>$detail,'x'=>$time_list]);
    }

    //机器云明细，机器云折线
    public function deviceAll(Request $request){

        //明细
        $request = $request->all();
//        $start = isset($request['start_time']) ? $request['start_time'] : time()-200;
//        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->groupBy('key')->pluck('title','key')->toArray();
        $key_list = [];
        foreach ($key as $k=>$v){
            $key_list[] = $k;
        }
        $id = DeviceData::wherein('dev_eui',$key_list)->max('id');
        $list = DeviceData::where('id','>=',($id-100))->wherein('dev_eui',$key_list)->select('crt_time','device_name','happening_data','dev_eui')->paginate(100, '', 'page', 1)->toArray();
        $list = $list['data'];
        foreach ($list as &$v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
            $v['crt_time'] = date('Y-m-d H:i:s',$v['crt_time']);
            $v['title'] = $key[$v['dev_eui']];
        }
        //折线
//        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select('crt_time','device_name','happening_data')->get()->toArray();
        return $this->json_return(200,'',$list);
    }

    //机器云明细，机器云折线
    public function deviceChart(Request $request){

        //明细
        $request = $request->all();
        $start = isset($request['start_time']) ? $request['start_time'] : time()-100000;
//        $end = isset($request['start_time']) ? $request['start_time'] : time();
        $key = DeviceSite::where('site',1)->wherein('title',['温度'])->groupBy('key')->pluck('key')->toArray();
        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select(DB::raw('count(1) as count'),'device_name')->groupBy('dev_eui')->get()->toArray();
        if(empty($list)){
            $time = DeviceData::max('crt_time');
            $s_time = $time-100000;
            $list = DeviceData::where('crt_time','>=',$s_time)->wherein('dev_eui',$key)->select(DB::raw('count(1) as count'),'device_name')->groupBy('dev_eui')->get()->toArray();
        }
        foreach ($list as &$v){
            $v['device_name'] = substr(str_replace('广西大王椰','',$v['device_name']),0,19);
        }
        //折线
//        $list = DeviceData::where('crt_time','>=',$start)->wherein('dev_eui',$key)->select('crt_time','device_name','happening_data')->get()->toArray();
        return $this->json_return(200,'',$list);
    }

    public function inout(){
        $i = 'inout_key_state';
        $s = Redis::get($i);
        if($s){
            $key = 'inout_key';
            $a = Redis::get($key);
            if(empty($a)){
                $a = 1;
            } else{
                if($a == 1) $a = 2;
                else $a = 1;
            }
            Redis::set($key,$a);

            if($a == 1) $type = '出库';
            else $type = '入库';

            $detail = [
                'project'=>'南京福猴建材有限公司',
                'type'=>$type,
                'num'=>'2',
                'order_no'=>'PG2023101900818',
                '时间'=>date('Y-m-d H:i:s'),
            ];
            Redis::del($i);
            return $this->json_return(200,'',$detail);
        }

        return $this->json_return(200,'',[]);

    }

    public function upInout(){
        $i = 'inout_key_state';
        $s = Redis::set($i,1);
    }

    public function t(){
        $a = bin2hex('PG2023103000182');
        echo             '00'.$a;
    }
//
//    public function endDb($db,$start_time){
//
//        $end_time = $start_time-86400;
//        $start_time =
//        $db = $db->where('crt_time','>=',$start_time)->
//        return $db;
//    }

}