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

<?php

namespace App\Http\Controllers\Api;

use App\Model\Box;
use App\Model\BoxDetail;
use App\Model\DispatchSub;
use App\Model\OrdersProductProcess;
use App\Model\Process;
use App\Model\SaleOrdersProduct;
use App\Model\SystemL;
use App\Service\ReportFormsService;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * 大屏数据展示
 * Class ScreenController
 * @package App\Http\Controllers\Api
 */
class ScreenController extends BaseController
{
    /**
     * 产值数据全览
     * @param Request $request
     * @return array
     */
    public function output_value(Request $request){
        $currentYear = Carbon::now()->year;
        $lastYear = $currentYear - 1;
        $currentMonth = Carbon::now()->month;

        $totalValueAllTime = SaleOrdersProduct::where('del_time',0)->sum('finished_num');
        $totalValueLastYear = SaleOrdersProduct::where('del_time',0)->whereRaw("YEAR(FROM_UNIXTIME(crt_time)) = $lastYear")->sum('finished_num');
        $totalValueCurrentYearMonth = SaleOrdersProduct::where('del_time',0)->whereRaw("YEAR(FROM_UNIXTIME(crt_time)) = $currentYear AND MONTH(FROM_UNIXTIME(crt_time)) = $currentMonth")->sum('finished_num');

        return $this->json_return(200,'',['total_time'=>$totalValueAllTime, 'total_last_year'=>$totalValueLastYear, 'total_current_month'=>$totalValueCurrentYearMonth]);
    }

    /**
     * 项目进度
     * @param Request $request
     * @return array
     */
    public function order_process1(Request $request) {
        $result = SaleOrdersProduct::where('del_time',0)
            ->select(DB::raw('sum(order_quantity) as total'), DB::raw('sum(finished_num) as finished_num'),'out_order_no as Code','customer_name as CustomerName')
            ->groupBy('out_order_no')
            ->get()->toArray();

        if(! empty($result)){
            foreach ($result as $key => $value){
                $result[$key]['rate'] = number_format($value['finished_num'] / $value['total'],2) * 100;
                unset($result[$key]['total']);
                unset($result[$key]['finished_num']);
            }
            $rate = array_column($result, 'rate'); // 提取列作为排序依据
            array_multisort($rate, SORT_DESC,$result);
        }

        return $this->json_return(200,'',$result);
    }

    /**
     * 历史项目 在手项目
     * @param Request $request
     * @return array
     */
    public function project_region(Request $request){
        $all = SaleOrdersProduct::where('del_time',0)
            ->whereColumn('order_quantity','=','finished_num')
            ->select('out_order_no')
            ->groupBy('out_order_no')
            ->get()
            ->toArray();
        $all = array_column($all,'out_order_no');

        $not_all = SaleOrdersProduct::where('del_time',0)
            ->whereColumn('order_quantity','<>','finished_num')
            ->select('out_order_no')
            ->groupBy('out_order_no')
            ->get()
            ->toArray();
        $not_all = array_column($not_all,'out_order_no');
        $all = array_diff($all, $not_all);

        $arr = [
            "all_num" => count($all),
            "num" => count($not_all),
        ];
        return $this->json_return(200,'',$arr);
    }

    /**
     * 本月质量
     * @param Request $request
     * @return array
     */
    public function output_value_month1(Request $request){
        $date = date('Ymd',time());

        $startDate = strtotime(date('Y-m-01 00:00:00', time()));
        $endDate = strtotime(date('Y-m-t 23:59:59', time()));
        //工序-----------------------------
        $model = new OrdersProductProcess(['channel' => $date]);//当前季度的数据
        $data = $model->where('del_time',0)
            ->where('status',4)
            ->select('crt_time')
            ->where('crt_time','>=',$startDate)
            ->where('crt_time','<=',$endDate)
            ->get()->toArray();

        $return = [];
        if(! empty($data)){
            foreach ($data as $value){
                $crt_time = date('Y-m-d',$value['crt_time']);
                if(isset($return[$crt_time])){
                    $return[$crt_time]['num'] += 1;
                }else{
                    $return[$crt_time] = [
                        'num' => 1,
                        'value' => $crt_time
                    ];
                }
            }
        }
        $return = array_values($return);
        return $this->json_return(200,'',$return);
    }

    /**
     * 产量趋势图
     * @param Request $request
     * @return array
     */
    public function output_value_efficiency(Request $request){
        // 获取当前时间戳
        $currentTimestamp = time();
        // 输出过去两周的起止时间（包括当前日期）
        $timestamp = strtotime("-13 days", $currentTimestamp);
        $startOfDay = strtotime(date('Y-m-d 00:00:00', $timestamp));
        $endOfDay = strtotime(date('Y-m-d 23:59:59', $currentTimestamp));

        $box = Box::where('del_time',0)
            ->where('crt_time','>=',$startOfDay)
            ->where('crt_time','<=',$endOfDay)
            ->select('crt_time','top_order_no','order_no')
            ->orderBy('crt_time','desc')
            ->get()->toArray();
        $result = [];
        if(! empty($box)){
            foreach ($box as $value){
                $model = new BoxDetail(['channel' => $value['top_order_no']]);
                $map = $model->where('del_time',0)
                    ->where('order_no',$value['order_no'])
                    ->select('order_no',DB::raw('sum(num) as num'))
                    ->groupBy('order_no')
                    ->pluck('num','order_no')
                    ->toArray();
                $output = $map[$value['order_no']] ?? 0;
                $times = date('Y-m-d',$value['crt_time']);
                if(isset($result[$times])){
                    $result[$times]['output'] += $output;
                }else{
                    $result[$times] = [
                        'time' => $times,
                        'output' => $output
                    ];
                }
            }
        }
        $result = array_values($result);
        return $this->json_return(200,'',$result);
    }

    //产量趋势图（完工）
    public function output_value_efficiency1(Request $request){
        // 获取当前时间
        $date = date('Ymd',time());
        $model = new OrdersProductProcess(['channel' => $date]);//当前季度的数据

        $data = $model->where('del_time',0)
            ->where('status',2)
            ->select('finished_time')
            ->get()->toArray();
        $result = [];
        if(! empty($data)){
            foreach ($data as $value){
                $time = date('Y-m-d',$value['finished_time']);
                if(isset($result[$time])){
                    $result[$time]['output'] += 1;
                }else{
                    $result[$time] = [
                        'time' => $time,
                        'output' =>  1
                    ];
                }
            }
            ksort($result);
        }
        $result = array_values($result);
        return $this->json_return(200,'',$result);
    }

    /**
     * 工序负荷全览
     * @param Request $request
     * @return array
     */
    public function capacity(Request $request){
        $date = date('Ymd',time());

        //工序-----------------------------
        $model = new OrdersProductProcess(['channel' => $date]);//当前季度的数据
        $data = $model->where('del_time',0)
            ->where('status',2)
            ->select('finished_time')
            ->get()->toArray();

        $return = [];
        if(! empty($data)){
            foreach ($data as $value){
                $finished_time = date('Ymd',$value['finished_time']);
                if(isset($return[$finished_time])){
                    $return[$finished_time] += 1;
                }else{
                    $return[$finished_time] = 1;
                }
            }
        }
        $maxValue = empty($return) ? 0 : max($return);
        $today = $return[$date] ?? 0;
        $rate = $maxValue ? intval($today/$maxValue) : 0;
        //工序-----------------------------

        //包装-----------------------------
        $model = new BoxDetail(['channel' => $date]);//当前季度的数据
        $data = $model->where('del_time',0)
            ->select('crt_time','num')
            ->get()->toArray();

        $return = [];
        if(! empty($data)){
            foreach ($data as $value){
                $crt_time = date('Ymd',$value['crt_time']);
                if(isset($return[$crt_time])){
                    $return[$crt_time] += $value['num'];
                }else{
                    $return[$crt_time] = $value['num'];
                }
            }
        }
        $maxValue = empty($return) ? 0 : max($return);
        $today = $return[$date] ?? 0;
        $rate2 = $maxValue ? intval($today/$maxValue) : 0;
        //包装-----------------------------

        $arr = [
            [
                [
                    "title"=> "压贴",
                    "rate"=> $rate
                ],
                [
                    "title"=> "包装",
                    "rate"=> $rate2
                ]
            ],
        ];
        return $this->json_return(200,'',['data' => $arr]);

    }

    /**
     * 设备信息
     * @param Request $request
     * @return array
     */
    public function product_num(Request $request){
        //数据模型
        $models = [];
        foreach (SystemL::$device as $k => $v){
            $models[$k] = [
                "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"=> '',
                "day_num"=> 0,
                "week_num"=> 0,
                "month_num"=> 0,
                "rate"=> 0
            ];
        }

        //当天的开始与结束时间戳
        $timestamp_today_start = strtotime(date("Y-m-d 00:00:00",time()));
        $timestamp_today_end = strtotime(date("Y-m-d 23:59:59",time()));

        //查询当日
        $today = SystemL::where('time','>=',$timestamp_today_start * 1000)
            ->where('time','<=',$timestamp_today_end * 1000)
            ->select('device_name','data_point_name','time')
            ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy])
            ->get()->toArray();
        //组织当日数据
        $this->fillData($today,1,$models);

        //上周时间
        $previousWeekStartDate = Carbon::now()->subWeek()->startOfWeek();
        $previousWeekEndDate = Carbon::now()->subWeek()->endOfWeek();
        //上周开始结束日期
        $start_week = $previousWeekStartDate->toDateString();
        $end_week = $previousWeekEndDate->toDateString();
        $last_week_key = $start_week . $end_week;
        list($status, $return_last_week) = $this->getRedisData($last_week_key);
        if(! $status){
            $previousWeekStartTimeStamp = $previousWeekStartDate->timestamp * 1000;
            $previousWeekEndTimeStamp = $previousWeekEndDate->timestamp * 1000;
            //获取上周数据
            $list_week = SystemL::where('time','>=',$previousWeekStartTimeStamp)
                ->where('time','<=',$previousWeekEndTimeStamp)
                ->select('device_name','data_point_name')
                ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy])
                ->get()->toArray();
            //组织上周数据
            $this->fillData($list_week,2,$models);
            //缓存
            Cache::put($last_week_key,json_encode($list_week),86400);
        }else{
            //有缓存 填充数据 组织上周数据
            $this->fillData($return_last_week,2,$models);
        }

        //上月时间
        $previousMonthStartDate = Carbon::now()->subMonth()->startOfMonth();
        $previousMonthEndDate = Carbon::now()->subMonth()->endOfMonth();
        //上月开始结束日期
        $start_month = $previousMonthStartDate->toDateString();
        $end_month = $previousMonthEndDate->toDateString();
        $last_month_key = $start_month . $end_month;
        list($status, $return_last_month) = $this->getRedisData($last_month_key);
        if(! $status){
            $previousMonthStartTimeStamp = $previousMonthStartDate->timestamp * 1000;
            $previousMonthEndTimeStamp = $previousMonthEndDate->timestamp * 1000;
            //获取上月数据
            $list_month = SystemL::where('time','>=',$previousMonthStartTimeStamp)
                ->where('time','<=',$previousMonthEndTimeStamp)
                ->select('device_name','data_point_name')
                ->whereIn('data_point_name',[SystemL::run,SystemL::work,SystemL::stop,SystemL::standBy])
                ->get()->toArray();
            //组织上月数据
            $this->fillData($list_month,3,$models);
            //缓存
            Cache::put($last_month_key,json_encode($list_month),86400);
        }else{
            //有缓存 填充数据 组织上周数据
            $this->fillData($return_last_month,3,$models);
        }

        $return = [];
        foreach ($models as $key => $value){
            $value['device_name'] = $key;
            $return[] = $value;
        }

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

    //在制工单
    public function work_order(Request $request){
        // 获取当前时间戳
        $currentTimestamp = time();
        $timestamp = strtotime("-3 months", $currentTimestamp);
        $startOfDay = strtotime(date('Y-m-d 00:00:00', $timestamp));
        $endOfDay = strtotime(date('Y-m-d 23:59:59', $currentTimestamp));

        $result = DispatchSub::where('del_time',0)
            ->where('crt_time',">=", $startOfDay)
            ->where('crt_time',"<=", $endOfDay)
            ->whereColumn('dispatch_quantity','>','finished_num')
            ->select('dispatch_no as order_no','process_id','product_title','dispatch_quantity as product_num','finished_num as finish_num')
            ->get()->toArray();
        if(! empty($result)){
            $process_id = array_unique(array_column($result,'process_id'));
            $processMap = Process::whereIn('id',$process_id)
                ->pluck('title','id')
                ->toArray();
            foreach ($result as $key => $value){
                $result[$key]['procedure'] = $processMap[$value['process_id']] ?? '';
            }
        }

        return $this->json_return(200,'',$result);
    }

    /**
     * 待加工
     * @param Request $request
     * @return array
     */
    public function nu_work_order(Request $request){
        $date = date('Ymd',time());

        //工序-----------------------------
        $model = new OrdersProductProcess(['channel' => $date]);//当前季度的数据
        $result = $model->where('del_time',0)
            ->where('status',0)
            ->select('production_no as order_no','product_title','process_id',DB::raw('count(id) as product_num'))
            ->groupBy('order_product_id')
            ->get()->toArray();

        if(! empty($result)){
            $process_id = array_unique(array_column($result,'process_id'));
            $processMap = Process::whereIn('id',$process_id)
                ->pluck('title','id')
                ->toArray();
            foreach ($result as $key => $value){
                $result[$key]['procedure'] = $processMap[$value['process_id']] ?? '';
            }
        }

        return $this->json_return(200,'',$result);
    }

    //获取缓存
    public function getRedisData($cacheKey){
        if(Cache::has($cacheKey)){
            return [true,json_decode(Cache::get($cacheKey),true)];
        }

        return [false, []];
    }

    /**
     * 数据填充
     * @param $list
     * @param $type
     * @param $models
     */
    public function fillData($list,$type,&$models){
        if(empty($list)) return;

        $run_time = $process_time = $fault = $start_time = [];
        foreach ($list as $value){
            if($type == 1 && ! isset($start_time[$value['device_name']])){
                $start_time_tmp = date("Y-m-d H:i:s", $value['time'] / 1000);
                $start_time[$value['device_name']] = $start_time_tmp;
            }
            if($value['data_point_name'] == SystemL::run || $value['data_point_name'] == SystemL::standBy){
                //运行次数
                if(isset($run_time[$value['device_name']])){
                    $run_time[$value['device_name']] += 1;
                }else{
                    $run_time[$value['device_name']] = 1;
                }
            }
            if($value['data_point_name'] == SystemL::standBy){
                //工作次数
                if(isset($process_time[$value['device_name']])){
                    $process_time[$value['device_name']] += 1;
                }else{
                    $process_time[$value['device_name']] = 1;
                }
            }
            if($value['data_point_name'] == SystemL::stop){
                //故障次数
                if(isset($fault[$value['device_name']])){
                    $fault[$value['device_name']] += 1;
                }else{
                    $fault[$value['device_name']] = 1;
                }
            }
        }
        foreach (SystemL::$device as $key => $value){
            //运行次数
            $run_num = $run_time[$key] ?? 0;
            //工作次数
            $process_num  = $process_time[$key] ?? 0;
            //故障次数
            $fault_tmp = $fault[$key] ?? 0;

            //运行时间
            $run_time_tmp = (new ReportFormsService())->calTimeReturnMin($run_num);
            //工作时间
            $process_time_tmp = (new ReportFormsService())->calTimeReturnMin($process_num);
            //故障时间
            $fault_time_tmp = (new ReportFormsService())->calTimeReturnMin($fault_tmp);

            //计划运行时间 工作时间 - 计划停机 （没有计划停机）
            //实际运行时间  计划运行时间 -故障停机 - 设备调整（没有设备调整
            $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($type == 1){
                $models[$key]['machine_day_num'] = $run_num;
                $models[$key]['day_num'] = $process_num;
                $models[$key]['break_day_num'] = $fault_tmp;
                $models[$key]['rate'] = $oee;
                $models[$key]['start_time'] = $start_time[$key] ?? '暂未开机';
            }elseif($type == 2){
                $models[$key]['machine_week_num'] = $run_num;
                $models[$key]['week_num'] = $process_num;
                $models[$key]['break_week_num'] = $fault_tmp;
            }elseif($type == 3){
                $models[$key]['machine_month_num'] = $run_num;
                $models[$key]['month_num'] = $process_num;
                $models[$key]['break_month_num'] = $fault_tmp;
            }
        }
    }
}