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

<?php

namespace App\Http\Controllers\Api;

use App\Model\ApplyOrder;
use App\Model\ApplyOrderDetail;
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\SalesFrom;
use App\Model\ScrappCount;
use App\Model\SystemL;
use App\Service\FyySqlServerService;
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');
        $totalValueAllTime = $totalValueAllTime . "(吨)";

        $totalValueLastYear = SaleOrdersProduct::where('del_time',0)
            ->whereRaw("YEAR(FROM_UNIXTIME(crt_time)) = $lastYear")
            ->sum('finished_num');
        $totalValueLastYear = $totalValueLastYear . "(吨)";

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

        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','pre_shipment_time')
            ->groupBy('out_order_no')
            ->orderBy('id', 'desc') // 添加这一行以按创建时间降序排序
            ->take(200)                    // 添加这一行以限制结果集大小为100
            ->get()->toArray();

        if(! empty($result)){
            foreach ($result as $key => $value){
                $rate = bcmul(bcdiv($value['finished_num'], $value['total'],2),100,2);
                if($rate > 100) $rate = "100.00";
                $result[$key]['rate'] = $rate;
                if($result[$key]['rate'] >= 100){
                    $result[$key]['is_over'] = '否';
                }else{
                    $t = $value['pre_shipment_time'];
                    if(time() > $t) $result[$key]['is_over'] = '是';
                    else $result[$key]['is_over'] = '否';
                }
                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) + 8000,
            "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())) * 1000;
        $endDate = strtotime(date('Y-m-t 23:59:59', time())) * 1000;
        $return = $this->getDayInMonth();

        //获取数据
        $data = ScrappCount::where('crt_time','>=',$startDate)
            ->where('crt_time','<',$endDate)
            ->where('del_time',0)
            ->select('crt_time as time','scrapp_num as value')
            ->get()->toArray();

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

    function getDayInMonth(){
        $startDate = strtotime(date('Y-m-01 00:00:00', time()));

        // 获取当前日期
        $currentDate = time();

        $dates = array();
        while ($currentDate >= $startDate) {
            $t = date('Y-m-d', $currentDate);
            $dates[$t] = [
                'value' => $t,
                'num' =>  0
            ];
            $currentDate = strtotime('-1 day', $currentDate);
        }

        return $dates;
    }

    /**
     * 本月质量
     * @param Request $request
     * @return array
     */
    public function output_value_month11(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_efficiency1(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_efficiency11(Request $request){
        $list = DispatchSub::where('del_time',0)
            ->where('finished_num','>=',0)
            ->select('crt_time','finished_num')
            ->orderBy('id','desc')
            ->limit(20)
            ->get()->toArray();

        $return = [];
        foreach ($list as $value){
            $date = date("Y-m-d",$value['crt_time']);
            if(isset($return[$date])){
                $num = bcadd($value['finished_num'], $return[$date]['output'],3);
                $return[$date]['output'] = $num;
            }else{
                $return[$date] = [
                    'output' => $value['finished_num'],
                    'time' => $date,
                ];
            }
        }

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

    //产量趋势图
    public function output_value_efficiency(Request $request){
        $time = strtotime(date("Y-06-01 00:00:00"));

        $return = [];
        $data = ApplyOrderDetail::where('del_time',0)
            ->where('type',ApplyOrder::type_two)
            ->where('crt_time','>=',$time)
            ->select('quantity','crt_time','data_id')
            ->get()->toArray();
        $dispatch = DispatchSub::whereIn('id',array_column($data,'data_id'))
            ->pluck("production_quantity",'id')
            ->toArray();

        $plan = [];
        foreach ($data as $value) {
            $date = date("Y-m-d", $value['crt_time']);
            if (isset($return[$date])) {
                $quantity = bcadd($return[$date], $value['quantity'], 3);
                $return[$date] = $quantity;
            } else {
                $return[$date] = $value['quantity'];
            }

            $n = $dispatch[$value['data_id']] ?? 0;
            if (isset($plan[$date])) {
                $quantity = bcadd($plan[$date], $n, 3);
                $plan[$date] = $quantity;
            } else {
                $plan[$date] = $n;
            }
        }

        ksort($return);

        $result = [];
        foreach ($return as $key => $value){
            $tmp = [
                'time' => $key,
                'output' => $value,
                'plan' => $plan[$key] ?? 0,
            ];
            $result[] = $tmp;
        }

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

    function getDay(){
        // 获取当前季度的开始日期
        $month = date('m');
        if ($month >= 1 && $month <= 3) {
            $quarter = 1;
        } elseif ($month >= 4 && $month <= 6) {
            $quarter = 2;
        } elseif ($month >= 7 && $month <= 9) {
            $quarter = 3;
        } else {
            $quarter = 4;
        }
        $year = date('Y'); // 获取当前年份

        if ($quarter == 1) {
            $startDate = strtotime("$year-01-01"); // 第一季度的开始日期
        } elseif ($quarter == 2) {
            $startDate = strtotime("$year-04-01"); // 第二季度的开始日期
        } elseif ($quarter == 3) {
            $startDate = strtotime("$year-07-01"); // 第三季度的开始日期
        } else {
            $startDate = strtotime("$year-10-01"); // 第四季度的开始日期
        }

// 获取当前日期
        $currentDate = time();

// 生成当前季度到今天为止的所有日期
        $dates = array();
        while ($currentDate >= $startDate) {
            $t = date('Y-m-d', $currentDate);
            $dates[$t] = [
                'time' => $t,
                'output' =>  0
            ];
            $currentDate = strtotime('-1 day', $currentDate);
        }

        return $dates;
    }

    /**
     * 工序负荷全览
     * @param Request $request
     * @return array
     */
    public function capacity(Request $request){
        $process = Process::where('del_time',0)->get()->toArray();

        $list = DispatchSub::where('del_time',0)
            ->where('dispatch_quantity','>=',0)
            ->select('crt_time','dispatch_quantity')
            ->orderBy('id','desc')
            ->limit(20)
            ->get()->toArray();

        $return = [];
        foreach ($list as $value){
            $date = date("Ymd",$value['crt_time']);
            if(isset($return[$date])){
                $num = bcadd($value['dispatch_quantity'], $return[$date],3);
                $return[$date] = $num;
            }else{
                $return[$date] = $value['dispatch_quantity'];
            }
        }

        $maxValue = empty($return) ? 0 : max($return);
        $today = $return[date("Ymd")] ?? 0;
        $rate = $maxValue ? intval($today/$maxValue * 100) : 0;
        $array = [];
        foreach ($process as $value){
            $array[] = [
                [
                    'title' => $value['title'],
                    'rate' => $rate
                ]
            ];
        }
        //工序-----------------------------

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

    }

    /**
     * 设备信息
     * @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','technology_name','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']] ?? '';
                $result[$key]['product_title'] = $value['product_title'] . "(". $value['technology_name'] .")";
            }
        }

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

    /**
     * 待加工
     * @param Request $request
     * @return array
     */
    public function nu_work_order(Request $request){
        $startOfDay = strtotime(date('Y-m-d 00:00:00', strtotime("-20days")));

        $result = DispatchSub::where('del_time',0)
//            ->where('dispatch_time_start',">=", $startOfDay)
            ->where('finished_num',0)
            ->select('dispatch_no as order_no','product_title','technology_name','dispatch_quantity as product_num','process_id')
            ->orderBy('id','desc')
            ->limit(20)
            ->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']] ?? '';
                $result[$key]['product_title'] = $value['product_title'] . "(". $value['technology_name'] .")";
            }
        }

        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;
            }
        }
    }

    /**
     * 项目分布的地区
     * @param Request $request
     * @return array
     */
    public function saleOrdersFromAddress(Request $request){
        $return = [];
        $address_map = config('address');
        foreach ($address_map as $value){
            $return[] = [
                'code' => $value['value'],
                'label' => $value['label'],
                'num' => ""
            ];
        }

        $sqlServerModel = new FyySqlServerService(['id' => 1]);
        list($status,$msg) = $sqlServerModel->getCustomerFromSqlServer();
        if($status){
            foreach ($return as $key => $value){
                if(isset($msg[$value['code']])) $return[$key]['num'] = $msg[$value['code']];
            }
        }

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

    /**
     * 车间生产状态
     * @param Request $request
     * @return array
     */
    public function production_status(Request $request){
        $return = [
            'dispatch' => 0,
            'finished' => 0,
            'box' => 0,
            'send' => 0,
        ];
        $time = strtotime(date("Y-m-d 00:00:00"));

        //派工
        $dispatch = ApplyOrderDetail::where('del_time',0)
            ->where('type', ApplyOrder::type_one)
            ->where('crt_time','>=',$time)
            ->select('quantity')
            ->get()->toArray();
        $dispatch_num = 0;
        foreach (array_column($dispatch, 'dispatch_quantity') as $quantity) {
            $dispatch_num = bcadd($dispatch_num, $quantity, 3);
        }
        $return['dispatch'] = $dispatch_num;

        //完工
        $finished = ApplyOrderDetail::where('del_time',0)
            ->where('type', ApplyOrder::type_two)
            ->where('crt_time','>=',$time)
            ->select('quantity')
            ->get()->toArray();
        $finished_num = 0;
        foreach (array_column($finished, 'quantity') as $quantity) {
            $finished_num = bcadd($finished_num, $quantity, 3);
        }
        $return['finished'] = $finished_num;

        //包装 发货
        $box_quantity = ApplyOrderDetail::where('del_time',0)
            ->where('type', ApplyOrder::type_three)
            ->where('crt_time','>=',$time)
            ->select('quantity')
            ->get()->toArray();
        $num = 0;
        foreach (array_column($box_quantity, 'quantity') as $quantity) {
            $num = bcadd($num, $quantity, 3);
        }
        $return['box'] = $return['send'] = $num;

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