hcsl/app/Service/ReportFormsService.php

<?php

namespace App\Service;

use App\Model\DispatchSub;
use App\Model\Employee;
use App\Model\OrdersProduct;
use App\Model\OrdersProductProcess;
use App\Model\Process;
use App\Model\ReportWorkingDetail;
use App\Model\Scrapp;
use App\Model\SystemL;
use App\Model\Team;
use Illuminate\Support\Facades\DB;

/**
 * 设备相关设置报表
 * Class ReportFormsService
 * @package App\Service
 */
class ReportFormsService extends Service
{
    /**
     * 生产进度
     * @param $data
     * @return array
     */
    public function productionReport($data){
        if(empty($data['production_time'][0]) || empty($data['production_time'][1])) return [false, '生产订单时间必须选择！'];

        //检索条件 生产订单主表----------------
        $model = OrdersProduct::where('del_time',0)->select('id','production_time','production_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','order_quantity','production_quantity');
        $model->whereBetween('production_time',[$data['production_time'][0],$data['production_time'][1]]);
        if(! empty($data['production_no'])) $model->where('production_no', 'LIKE', '%'.$data['production_no'].'%');
        if(! empty($data['out_order_no_time'][0]) && ! empty($data['out_order_no_time'][1])) $model->whereBetween('out_order_no_time',[$data['out_order_no_time'][0],$data['out_order_no_time'][1]]);
        if(! empty($data['out_order_no'])) $model->where('out_order_no', 'LIKE', '%'.$data['out_order_no'].'%');
        if(! empty($data['customer_no'])) $model->where('customer_no', 'LIKE', '%'.$data['customer_no'].'%');
        if(! empty($data['customer_name'])) $model->where('customer_name', 'LIKE', '%'.$data['customer_name'].'%');
        $orderList = $model->get()->toArray();
        //生产订单主表----------------
        $id = array_column($orderList,'id');

        //分组以后的订单列表
        $list = [];
        foreach ($orderList as $value){
            if(! isset($list[$value['production_no']])){
                $list[$value['production_no']] = $value;
            }else{
                $list[$value['production_no']]['order_quantity'] += $value['order_quantity'];
                $list[$value['production_no']]['production_quantity'] += $value['production_quantity'];
            }
        }unset($orderList);

        $detail = [];
        $dispatchList = DispatchSub::where('del_time',0)
            ->whereIn('order_product_id',$id)
            ->select('order_product_id','process_id',DB::raw('SUM(dispatch_quantity) as dispatch_count'),DB::raw('SUM(finished_num) as finish_count'))
            ->groupBy('order_product_id','process_id')
            ->get()->toArray();//派工和完工数据
        foreach ($dispatchList as $t){
            $keys = $t['order_product_id'] . "|" .$t['process_id'];
            if(isset($detail[$keys])){
                $detail[$keys]['dispatch_count'] += $t['dispatch_count'];
                $detail[$keys]['finish_count'] += $t['finish_count'];
            }else{
                $detail[$keys] = $t;
            }
        }

        //返回统计数据
        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
            $list[$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';

            $detail_key = $value['id'] . "|";
            foreach ($detail as $key_son => $value_son) {
                if (strpos($key_son, $detail_key) !== false) {
                    $value_son['production_no'] = $value['production_no'];
                    $dispatch_count = $value_son['dispatch_count'];
                    $finish_count = $value_son['finish_count'];
                    $value_son['dispatch_count'] = $dispatch_count;
                    $value_son['finish_count'] = $finish_count;
                    $value_son['rate'] = number_format($value_son['finish_count'] / $value['order_quantity'] * 100, 2);
                    $list[$key]['process'][] = $value_son;
                }
            }

            if(empty($list[$key]['process'])) unset($list[$key]);
        }

        return [true,array_values($list)];
    }

    private function checkSameQuarter($timestamps) {

        $quarters = $out_time = [];
        foreach ($timestamps as $timestamp) {
            $date = date('Ym', $timestamp);
            $year = intval(date('Y', $timestamp));
            $quarter = ceil(intval(date('n', $timestamp)) / 3);
            if(! isset($quarters[$year]) || ! in_array($quarter,$quarters[$year])){
                $quarters[$year][] = $quarter;
                $out_time[] = $date;
            }
        }
        return $out_time;
    }

    public function teamReport($data){
        if(empty($data['finish_time'][0]) || empty($data['finish_time'][1])) return [false, '完工时间必须选择！'];

        //班组
        $team_id = $data['team_id'] ?? [];

        $result = DispatchSub::where('del_time',0)
            ->where('finished_num','>',0)
            ->whereBetween('upd_time', [$data['finish_time'][0], $data['finish_time'][1]])
            ->when(!empty($team_id), function ($query) use ($team_id) {
                return $query->whereIn('team_id', $team_id);
            })
            ->select('team_id','upd_time as finished_time','finished_num','order_product_id')
            ->get()->toArray();
        if(empty($result)) return [true , []];

        //组织数据
        $team_map = Team::whereIn('id',array_unique(array_column($result,'team_id')))
            ->pluck('title','id')
            ->toArray();
        $return_team = $return_team_time_tmp = $return_team_time= [];

        foreach ($result as $value){
            if(isset($return_team[$value['team_id']])){
                $num = bcadd($value['finished_num'], $return_team[$value['team_id']]['num'],3);
                $return_team[$value['team_id']]['num'] = $num;
                if(! in_array($value['order_product_id'], $return_team[$value['team_id']]['production_no'])) {
                    $return_team[$value['team_id']]['production_no'] = array_merge_recursive($return_team[$value['team_id']]['production_no'],[$value['order_product_id']]);
                }
            }else{
                $return_team[$value['team_id']] = [
                    'num' => $value['finished_num'],
                    'team_name' => $team_map[$value['team_id']] ?? '',
                    'production_no' => [$value['order_product_id']]
                ];
            }

            $tmp = date("Y-m-d",$value['finished_time']);
            if(isset($return_team_time_tmp[$tmp][$value['team_id']])){
                $num = bcadd($value['finished_num'],$return_team_time_tmp[$tmp][$value['team_id']]['num'],3);
                $return_team_time_tmp[$tmp][$value['team_id']]['num'] = $num;
            }else{
                $return_team_time_tmp[$tmp][$value['team_id']] = [
                    'time' => $tmp,
                    'num' => $value['finished_num'],
                    'team_name' => $team_map[$value['team_id']] ?? '',
                ];
            }
        }ksort($return_team_time_tmp);unset($result);

        $all_team_map = Team::where('del_time',0)
            ->pluck('title','id')
            ->toArray();
        foreach ($return_team_time_tmp as $key => $value){
            $t_k = array_keys($value);
            foreach ($all_team_map as $k => $v){
                if(! in_array($k,$t_k)){
                    $return_team_time_tmp[$key][$k] = [
                        'time' => $key,
                        'num' => 0,
                        'team_name' => $v,
                    ];
                }
            }
            ksort($return_team_time_tmp[$key]);
            $tmp = [];
            $tmp['time'] = $key;
            $tmp['sub'] = array_values($return_team_time_tmp[$key]);
            $return_team_time[] = $tmp;
        }unset($return_team_time_tmp);
        foreach ($return_team as $key => $value){
            $return_team[$key]['num'] = $value['num'];
        }
        foreach ($return_team_time as $key => $value){
            foreach ($value['sub'] as $k => $v){
                $return_team_time[$key]['sub'][$k]['num'] = $v['num'];
            }
        }

        //列表数据 图表数据
        return [true,['list'=>array_values($return_team),'chart'=>$return_team_time]];
    }

    /**
     * 班组
     * @param $data
     * @return array
     */
    public function teamReport1($data){
        if(empty($data['finish_time'][0]) || empty($data['finish_time'][1])) return [false, '完工时间必须选择！'];

        //班组
        $team_id = $data['team_id'] ?? [];
        //根据完工时间 扩大时间戳范围前后三个月
        $new_finish_time = $this->increaseTimeArea($data['finish_time']);
        //根据时间戳范围 获取分表的时间
        $return_time = $this->getTimeAreaData($new_finish_time);

        //检索分表数据
        $batchSize = 1000; // 每次处理的数据量
        $result = $team = [];
        foreach ($return_time as $value){
            $offset = 0;
            $hasMoreData = true;

            while ($hasMoreData) {
                // 子表搜索
                $models = new OrdersProductProcess(['channel' => $value]);
                $tmp = $models->where('del_time', 0)
                    ->whereBetween('finished_time', [$data['finish_time'][0], $data['finish_time'][1]])
                    ->where('status', 2)
                    ->when(!empty($team_id), function ($query) use ($team_id) {
                        return $query->whereIn('team_id', $team_id);
                    })
                    ->skip($offset) // 跳过前面的数据
                    ->take($batchSize) // 获取固定数量的数据
                    ->select('team_id', 'finished_time', 'production_no')
                    ->get()->toArray(); // 完工数据

                if (empty($tmp)) {
                    $hasMoreData = false;
                } else {
                    $result = array_merge_recursive($result, $tmp);
                    $team = array_merge_recursive($team, array_unique(array_column($tmp, 'team_id')));
                    $offset += $batchSize;
                }
            }
//            //子表搜索
//            $models = new OrdersProductProcess(['channel' => $value]);
//            $tmp = $models->where('del_time',0)
//                ->whereBetween('finished_time',[$data['finish_time'][0], $data['finish_time'][1]])
//                ->where('status',2)
//                ->when(!empty($team_id), function ($query) use ($team_id) {
//                    return $query->whereIn('team_id', $team_id);
//                })
//                ->select('team_id','finished_time','production_no')
//                ->get()->toArray();//完工数据
//            $result = array_merge_recursive($result,$tmp);
//            $team = array_merge_recursive($team,array_unique(array_column($tmp,'team_id')));
        }

        if(empty($result)) return [true , []];

        //组织数据
        $team_map = Team::whereIn('id',array_unique($team))
            ->pluck('title','id')
            ->toArray();
        $return_team = $return_team_time_tmp = $return_team_time= [];

        foreach ($result as $value){
            if(isset($return_team[$value['team_id']])){
                $return_team[$value['team_id']]['num'] += 1;
                if(! in_array($value['production_no'], $return_team[$value['team_id']]['production_no'])) {
                    $return_team[$value['team_id']]['production_no'] = array_merge_recursive($return_team[$value['team_id']]['production_no'],[$value['production_no']]);
                }
            }else{
                $return_team[$value['team_id']] = [
                    'num' => 1,
                    'team_name' => $team_map[$value['team_id']] ?? '',
                    'production_no' => [$value['production_no']]
                ];
            }

            $tmp = date("Y-m-d",$value['finished_time']);
            if(isset($return_team_time_tmp[$tmp][$value['team_id']])){
                $return_team_time_tmp[$tmp][$value['team_id']]['num'] += 1;
            }else{
                $return_team_time_tmp[$tmp][$value['team_id']] = [
                    'time' => $tmp,
                    'num' => 1,
                    'team_name' => $team_map[$value['team_id']] ?? '',
                ];
            }
        }ksort($return_team_time_tmp);unset($result);

        $all_team_map = Team::where('del_time',0)
            ->pluck('title','id')
            ->toArray();
        foreach ($return_team_time_tmp as $key => $value){
            $t_k = array_keys($value);
            foreach ($all_team_map as $k => $v){
                if(! in_array($k,$t_k)){
                    $return_team_time_tmp[$key][$k] = [
                        'time' => $key,
                        'num' => 0,
                        'team_name' => $v,
                    ];
                }
            }
            ksort($return_team_time_tmp[$key]);
            $tmp = [];
            $tmp['time'] = $key;
            $tmp['sub'] = array_values($return_team_time_tmp[$key]);
            $return_team_time[] = $tmp;
        }unset($return_team_time_tmp);
        foreach ($return_team as $key => $value){
            $return_team[$key]['num'] = bcdiv($value['num'],1000,3);
        }
        foreach ($return_team_time as $key => $value){
            foreach ($value['sub'] as $k => $v){
                $return_team_time[$key]['sub'][$k]['num'] = bcdiv($v['num'],1000,3);
            }
        }

        //列表数据 图表数据
        return [true,['list'=>array_values($return_team),'chart'=>$return_team_time]];
    }

    /**
     * 时间特殊处理
     * @param $time_area
     * @return array
     */
    private function increaseTimeArea($time_area){
        // 增加三个月的时间戳
        $newStartTimestamp = strtotime('-3 months', $time_area[0]);
        $newEndTimestamp = strtotime('+3 months', $time_area[1]);

        return [$time_area[0],$time_area[1]];
    }

    /**
     * 获取时间区间数据
     * @param $time_area
     * @return array
     */
    private function getTimeAreaData($time_area){
        $startYear = date('Y', $time_area[0]);
        $endYear = date('Y', $time_area[1]);

        $return = [];

        for ($year = $startYear; $year <= $endYear; $year++) {
            for ($quarter = 1; $quarter <= 4; $quarter++) {
                $quarterStart = strtotime($year . '-' . (($quarter - 1) * 3 + 1) . '-01');
                $quarterEnd = strtotime($year . '-' . ($quarter * 3) . '-01') - 1;

                if ($quarterStart <= $time_area[1] && $quarterEnd >= $time_area[0]) {
//                    $tmp = $year . sprintf('%02d', $quarter);//年季度
                    $return[] = $year .sprintf('%02d',($quarter - 1) * 3 + 1);
                }
            }
        }

        return $return;
    }

    /**
     * 班组 详情
     * @param $data
     * @return array
     */
    public function teamReportDetail($data){
        if(empty($data['production_no'])) return [false,'生产订单号不能为空！'];

        $list = OrdersProduct::whereIn('id',$data['production_no'])
            ->select('production_time','production_no','customer_no','customer_name','table_header_mark','product_no','product_title','product_size','product_unit','dispatch_complete_quantity','finished_num','technology_material','technology_name','wood_name','process_mark','table_body_mark')
            ->get()->toArray();


        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
        }

        return [true,$list];
    }

    /**
     * 不良品
     * @param $data
     * @return array
     */
    public function badGoodsReport($data){
        if(empty($data['production_time'][0]) || empty($data['production_time'][1])) return [false, '生产订单时间必须选择！'];

        //检索条件 生产订单主表----------------
        $model = OrdersProduct::where('del_time',0)->select('production_time','production_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','order_quantity','production_quantity','out_crt_man');
        $model->whereBetween('production_time',[$data['production_time'][0],$data['production_time'][1]]);
        if(! empty($data['production_no'])) $model->where('production_no', 'LIKE', '%'.$data['production_no'].'%');
        if(! empty($data['out_order_no_time'][0]) && ! empty($data['out_order_no_time'][1])) $model->whereBetween('out_order_no_time',[$data['out_order_no_time'][0],$data['out_order_no_time'][1]]);
        if(! empty($data['out_order_no'])) $model->where('out_order_no', 'LIKE', '%'.$data['out_order_no'].'%');
        if(! empty($data['customer_no'])) $model->where('customer_no', 'LIKE', '%'.$data['customer_no'].'%');
        if(! empty($data['customer_name'])) $model->where('customer_name', 'LIKE', '%'.$data['customer_name'].'%');
        if(! empty($data['out_crt_man'])) $model->where('out_crt_man', 'LIKE', '%'.$data['out_crt_man'].'%');
        $orderList = $model->get()->toArray();
        //生产订单主表----------------

        //筛选出制单日期 分表的依据
        $out_order_no_time = array_unique(array_column($orderList,'out_order_no_time'));
        //制单日期
        $out_time = $this->checkSameQuarter($out_order_no_time);

        //分组以后的订单列表
        $list = [];
        foreach ($orderList as $value){
            if(! isset($list[$value['production_no']])){
                $list[$value['production_no']] = $value;
            }else{
                $list[$value['production_no']]['order_quantity'] += $value['order_quantity'];
                $list[$value['production_no']]['production_quantity'] += $value['production_quantity'];
            }
        }unset($orderList);

        //查询分表数据
        $production_no = array_column($list,'production_no');
        $detail = [];
        foreach ($out_time as $value){
            //子表搜索
            $models = new OrdersProductProcess(['channel' => $value]);
            $tmp = $models->whereIn('production_no',$production_no)
                ->where('del_time',0)
                ->where('status',4)
                ->select('production_no',DB::raw('COUNT(id) as bad_goods_num'))
                ->groupBy('production_no')
                ->get()->toArray();//不良品数据

            foreach ($tmp as $t){
                if(isset($detail[$t['production_no']])){
                    $detail[$t['production_no']]['bad_goods_num'] += $t['bad_goods_num'];
                }else{
                    $detail[$t['production_no']] = $t;
                }
            }
        }

        //返回统计数据

        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
            $list[$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';

            $del_num = $detail[$value['production_no']] ?? 0;
            $list[$key]['bad_goods_num'] = $del_num;
            $list[$key]['rate'] = number_format($del_num / $value['production_quantity'], 2);
        }

        return [true,array_values($list)];
    }

    /**
     * 不良品 详情
     * @param $data
     * @return array
     */
    public function badGoodsReportDetail($data){
        if(empty($data['production_no'])) return [false,'生产订单号不能为空！'];

        $list = OrdersProduct::where('production_no',$data['production_no'])
            ->select('id','production_time','production_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','product_no','product_title','product_size','product_unit','dispatch_complete_quantity','finished_num','technology_material','technology_name','wood_name','process_mark','table_body_mark','out_crt_man','production_quantity','order_quantity')
            ->get()->toArray();

        //筛选出制单日期 分表的依据
        $out_order_no_time = array_unique(array_column($list,'out_order_no_time'));
        //制单日期
        $out_time = $this->checkSameQuarter($out_order_no_time);

        $detail = $detail_scrapp = [];
        foreach ($out_time as $value){
            //子表搜索
            $models = new OrdersProductProcess(['channel' => $value]);
            $tmp = $models->where('production_no',$data['production_no'])
                ->where('del_time',0)
                ->where('status',4)
                ->select('order_product_id',DB::raw('COUNT(id) as bad_goods_num'), DB::raw("GROUP_CONCAT(DISTINCT scrapp_id ORDER BY scrapp_id SEPARATOR ',') as scrapp_ids"))
                ->groupBy('order_product_id')
                ->get()
                ->toArray();//不良品数据

            foreach ($tmp as $v){
                if(isset($detail[$v['order_product_id']])){
                    $detail[$v['order_product_id']] += $v['bad_goods_num'];
                }else{
                    $detail[$v['order_product_id']] = $v['bad_goods_num'];
                }
                if(isset($detail_scrapp[$v['order_product_id']])){
                    $detail_scrapp[$v['order_product_id']] .= "," . $v['scrapp_ids'];
                }else{
                    $detail_scrapp[$v['order_product_id']] = $v['scrapp_ids'];
                }
            }
        }



        $scrapp_map = Scrapp::where('del_time',0)->pluck('title','id')->toArray();
        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
            $list[$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';
            $list[$key]['bad_goods_num'] = $detail[$value['id']] ?? 0;
            $list[$key]['rate'] =  number_format($list[$key]['bad_goods_num'] / $value['production_quantity'], 2);
            $scrapp = $detail_scrapp[$value['id']] ?? '';
            $tmp_str = '';
            if(! empty($scrapp)){
                $tmp = explode(',',$scrapp);
                foreach ($tmp as $vv){
                    $tmp_str .= ($scrapp_map[$vv] ? $scrapp_map[$vv] . ',' : '');
                }
            }
            $list[$key]['scrapp_name'] = rtrim($tmp_str,',');
        }
        return [true, $list];
    }

    /**
     * 不良品原因
     * @param $data
     * @return array
     */
    public function badGoodsReasonReport($data){
        if(empty($data['production_time'][0]) || empty($data['production_time'][1])) return [false, '生产订单时间必须选择！'];

        //检索条件 生产订单主表----------------
        $model = OrdersProduct::where('del_time',0)->select('production_time','production_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','order_quantity','production_quantity','out_crt_man');
        $model->whereBetween('production_time',[$data['production_time'][0],$data['production_time'][1]]);
        if(! empty($data['production_no'])) $model->where('production_no', 'LIKE', '%'.$data['production_no'].'%');
        if(! empty($data['out_order_no_time'][0]) && ! empty($data['out_order_no_time'][1])) $model->whereBetween('out_order_no_time',[$data['out_order_no_time'][0],$data['out_order_no_time'][1]]);
        if(! empty($data['out_order_no'])) $model->where('out_order_no', 'LIKE', '%'.$data['out_order_no'].'%');
        if(! empty($data['customer_no'])) $model->where('customer_no', 'LIKE', '%'.$data['customer_no'].'%');
        if(! empty($data['customer_name'])) $model->where('customer_name', 'LIKE', '%'.$data['customer_name'].'%');
        if(! empty($data['out_crt_man'])) $model->where('out_crt_man', 'LIKE', '%'.$data['out_crt_man'].'%');
        $orderList = $model->get()->toArray();
        //生产订单主表----------------

        //筛选出制单日期 分表的依据
        $out_order_no_time = array_unique(array_column($orderList,'out_order_no_time'));
        //制单日期
        $out_time = $this->checkSameQuarter($out_order_no_time);

        //分组以后的订单列表
        $list = [];
        foreach ($orderList as $value){
            if(! isset($list[$value['production_no']])){
                $list[$value['production_no']] = $value;
            }else{
                $list[$value['production_no']]['order_quantity'] += $value['order_quantity'];
                $list[$value['production_no']]['production_quantity'] += $value['production_quantity'];
            }
        }unset($orderList);

        //查询分表数据
        $production_no = array_column($list,'production_no');
        $detail = [];
        foreach ($out_time as $value){
            //子表搜索
            $models = new OrdersProductProcess(['channel' => $value]);
            $tmp = $models->whereIn('production_no',$production_no)
                ->where('del_time',0)
                ->where('status',4)
                ->select('production_no',DB::raw('COUNT(id) as bad_goods_num'))
                ->groupBy('production_no')
                ->get()->toArray();//不良品数据

            foreach ($tmp as $t){
                if(isset($detail[$t['production_no']])){
                    $detail[$t['production_no']]['bad_goods_num'] += $t['bad_goods_num'];
                }else{
                    $detail[$t['production_no']] = $t;
                }
            }
        }

        //返回统计数据

        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
            $list[$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';

            $del_num = $detail[$value['production_no']] ?? 0;
            $list[$key]['bad_goods_num'] = $del_num;
            $list[$key]['rate'] = number_format($del_num / $value['production_quantity'], 2);
        }

        return [true,array_values($list)];
    }

    //不良品原因 详情
    public function badGoodsReasonReportDetail($data){
        if(empty($data['production_no'])) return [false,'生产订单号不能为空！'];

        $list = OrdersProduct::where('production_no',$data['production_no'])
            ->select('id','production_time','production_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','product_no','product_title','product_size','product_unit','dispatch_complete_quantity','finished_num','technology_material','technology_name','wood_name','process_mark','table_body_mark','out_crt_man','production_quantity')
            ->get()->toArray();

        //筛选出制单日期 分表的依据
        $out_order_no_time = array_unique(array_column($list,'out_order_no_time'));
        //制单日期
        $out_time = $this->checkSameQuarter($out_order_no_time);

        $detail = $scrapp_id = $team = $man = [];
        foreach ($out_time as $value){
            //子表搜索
            $models = new OrdersProductProcess(['channel' => $value]);
            $tmp = $models->where('production_no',$data['production_no'])
                ->where('del_time',0)
                ->where('status',4)
                ->select('order_product_id','scrapp_id','team_id','finished_id',DB::raw('COUNT(id) as bad_goods_num'))
                ->groupBy('order_product_id','scrapp_id')
                ->get()->toArray();//不良品数据

            foreach ($tmp as $v){
                if(! in_array($v['scrapp_id'], $scrapp_id)) $scrapp_id[] = $v['scrapp_id'];
                if(! in_array($v['team_id'], $team)) $team[] = $v['team_id'];
                if(! in_array($v['finished_id'], $man)) $man[] = $v['finished_id'];
                if(isset($detail[$v['order_product_id']])){
                    foreach ($detail[$v['order_product_id']] as $k => $d){
                        if($d['scrapp_id'] == $v['scrapp_id']){
                            $detail[$v['order_product_id']][$k]['bad_goods_num'] += $v['bad_goods_num'];
                        }else{
                            $detail[$v['order_product_id']][] = $v;
                        }
                    }
                }else{
                    $detail[$v['order_product_id']][] = $v;
                }
            }
        }


        //次品原因 班组 人员
        $map = Scrapp::whereIn('id',$scrapp_id)
            ->pluck('title','id')
            ->toArray();
        $map1 = Team::whereIn('id',$team)
            ->pluck('title','id')
            ->toArray();
        $map2 = Employee::whereIn('id',$man)
            ->pluck('emp_name','id')
            ->toArray();
        foreach ($list as $key => $value) {
            $list[$key]['production_time'] = $value['production_time'] ? date('Y-m-d',$value['production_time']) : '';
            $list[$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';
            $list[$key]['rate'] =  number_format(($detail[$value['id']] ?? 0) / $value['production_quantity'], 2);
            $del_tmp = $detail[$value['id']] ?? [];
            foreach ($del_tmp as $dk => $dv){
                $del_tmp[$dk]['rate'] = number_format($dv['bad_goods_num'] / $value['production_quantity'], 2);
                $del_tmp[$dk]['scrapp_name'] = $map[$dv['scrapp_id']] ?? '';
                $del_tmp[$dk]['team_name'] = $map1[$dv['team_id']] ?? '';
                $del_tmp[$dk]['man_name'] = $map2[$dv['finished_id']] ?? '';
            }
            $list[$key]['bad_goods'] = $del_tmp;
        }
        return [true, $list];
    }

    //设备统计报表
    public function deviceStatisticsReport($data){
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间必须选择！'];

        $day = $this->returnDays($data['time']);
        if($day > 10) return [false, '设备数据查询时间仅支持范围区间在10天内'];

        $model = SystemL::where('time','>=',$data['time'][0])
            ->where('time','<',$data['time'][1]);
        if(! empty($data['title'])) $model->whereIn('device_name',$data['title']);
        $result = $model->select('device_name','time','value','data_point_name')
            ->get()
            ->toArray();

        if(empty($result)) return [true,[]];
        $device_name = array_values(array_unique(array_column($result,'device_name')));

        //运行时间  工作时间  故障
        $run_time = $process_time = $fault = [];
        foreach ($result as $value){
            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 ($device_name as $key => $value){
            //运行次数
            $run_num = $run_time[$value] ?? 0;
            //工作次数
            $process_num  = $process_time[$value] ?? 0;
            //故障次数
            $fault_tmp = $fault[$value] ?? 0;

            //运行时间
            $run_time_tmp = $this->calTimeReturnMin($run_num);
            //工作时间
            $process_time_tmp = $this->calTimeReturnMin($process_num);
            //故障时间
            $fault_time_tmp = $this->calTimeReturnMin($fault_tmp);
            //待机时间
            $standby_time_tmp = number_format($run_time_tmp - $process_time_tmp,2);

            //计划运行时间 工作时间 - 计划停机 （没有计划停机）
            //实际运行时间  计划运行时间 -故障停机 - 设备调整（没有设备调整
            $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);

            $device_name[$key] = [
                'device_name' => $value,
                'run_time' => $run_time_tmp,
                'process_time' => $process_time_tmp,
                'standby_time' => $standby_time_tmp,
                'process_num' => $process_num,
                'fault_num' => $fault_tmp,
                'oee' => $oee,
//                'e' => $efficient,
//                'ex' => $expressive,
//                'true_process_time' => $true_process_time,
//                'qua' => $quality_index
            ];
        }

        return [true,$device_name];
    }

    public function deviceStatisticsReportDetail($data){
        if(empty($data['device_name']) || empty($data['time'][0]) || empty($data['time'][0])) return [false,'参数不能为空！'];

        $day = $this->returnDays($data['time']);
        if($day > 10) return [false, '设备数据查询时间仅支持范围区间在10天内'];

        $result = SystemL::where('time','>=',$data['time'][0])
            ->where('time','<',$data['time'][1])
            ->where('device_name',$data['device_name'])
            ->select('device_name','time','data_point_name')
            ->get()->toArray();

        $return = [
            'run' => [],
            'work' => [],
            'stop' => [],
            'stand_by' => [],
        ];
        foreach ($result as $key => $value){
            $time = date('Y-m-d H:i:s',$value['time'] / 1000);
            $stop_time = date('Y-m-d H:i:s',$value['time'] / 1000 + rand(10,40));
            if($value['data_point_name'] == SystemL::run || $value['data_point_name'] == SystemL::standBy){
                $return['run'][] = [
                    'time' => $time,
                    'stop_time' => $stop_time
                ];
            }
            if($value['data_point_name'] == SystemL::standBy){
                $return['work'][] = [
                    'time' => $time,
                    'stop_time' => $stop_time
                ];
            }
            if($value['data_point_name'] == SystemL::stop){
                $return['stop'][] = [
                    'time' => $time,
                    'stop_time' => $stop_time
                ];
            }
        }

        return [true, $return];
    }

    /**
     * 数据分析图
     * @param $data
     * @return array
     */
    public function deviceStatisticsReportChart($data){
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间必须选择！'];

        $day = $this->returnDays($data['time'], false);
        if($day > 31) return [false, '查询时间仅支持范围区间在31天内'];

        $process_time = [];

        $result = SystemL::where('time','>=',$data['time'][0])
            ->where('time','<',$data['time'][1])
            ->where('data_point_name',SystemL::standBy)
            ->select('device_name','time','value')
            ->get()->toArray();

        //所有的时间
        $time_all = [];
        foreach ($result as $value){
            $time = date('Y-m-d',$value['time'] / 1000);
            //工作 运行次数
            if(isset($process_time[$value['device_name']][$time])){
                $process_time[$value['device_name']][$time] += 1;
            }else{
                $process_time[$value['device_name']][$time] = 1;
            }
            if(! in_array($time, $time_all)) $time_all[] = $time;
        }
        sort($time_all);

        //数据结构模型
        foreach (SystemL::$device as $k => $v){
            if(isset($process_time[$k])){
                foreach ($time_all as $t){
                    if(! isset($process_time[$k][$t])){
                        $process_time[$k][$t] = 0;
                    }
                }
            }else{
                foreach ($time_all as $t){
                    $process_time[$k][$t] = 0;
                }
            }
        }

        $return = [];
        foreach ($process_time as $key => $value){
            $tmp['title'] = $key;
            $tmp['list'] = [];
            foreach ($value as $k => $v){
                $tmp['list'][] = [
                    'time' => $k,
                    'num' => $v
                ];
            }
            $return[] = $tmp;
        }

        return [true, $return];
    }

    /**
     * 数据OEE分析图
     * @param $data
     * @return array
     */
    public function deviceStatisticsReportOEEChart($data){
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间必须选择！'];

        $day = $this->returnDays($data['time'], false);
        if($day > 31) return [false, '查询时间仅支持范围区间在31天内'];

        //获取数据
        $result = SystemL::where('time','>=',$data['time'][0])
            ->where('time','<',$data['time'][1])
            ->select('device_name','time','value','data_point_name')
            ->get()->toArray();

        if(empty($result)) return [true,[]];
        $device_name = array_values(array_unique(array_column($result,'device_name')));


        $run_time = $process_time = $fault = $time_all = [];
        foreach ($result as $value){
            $time = date("Y-m-d",$value['time'] / 1000);
            if($value['data_point_name'] == SystemL::run || $value['data_point_name'] == SystemL::standBy){
                //运行次数
                if(isset($run_time[$value['device_name']][$time])){
                    $run_time[$value['device_name']][$time] += 1;
                }else{
                    $run_time[$value['device_name']][$time] = 1;
                }
            }
            if($value['data_point_name'] == SystemL::standBy){
                //工作次数
                if(isset($process_time[$value['device_name']][$time])){
                    $process_time[$value['device_name']][$time] += 1;
                }else{
                    $process_time[$value['device_name']][$time] = 1;
                }
            }
            if($value['data_point_name'] == SystemL::stop){
                //故障次数
                if(isset($fault[$value['device_name']][$time])){
                    $fault[$value['device_name']][$time] += 1;
                }else{
                    $fault[$value['device_name']][$time] = 1;
                }
            }

            if(! in_array($time, $time_all)) $time_all[] = $time;
        }
        sort($time_all);

        //组织模型  返回大致的数据结构
        $models = [];
        foreach (SystemL::$device as $k => $v){
            foreach ($time_all as $t){
                $models[$k][$t] = 0;
            }
        }

        //填充模型里的数据
        foreach ($device_name as $value){//设备名
            foreach ($time_all as $d_val){
                //运行次数
                $run_num = $run_time[$value][$d_val] ?? 0;
                //工作次数
                $process_num  = $process_time[$value][$d_val] ?? 0;
                //故障次数
                $fault_tmp = $fault[$value][$d_val] ?? 0;

                //运行时间
                $run_time_tmp = $this->calTimeReturnMin($run_num);
                //工作时间
                $process_time_tmp = $this->calTimeReturnMin($process_num);
                //故障时间
                $fault_time_tmp = $this->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(isset($models[$value][$d_val])){
                    $models[$value][$d_val] = $oee;
                }
            }
        }

        //返回结果
        $final = [];
        foreach ($models as $key => $value){
            $tmp['title'] = $key;
            $tmp['list'] = [];
            foreach ($value as $k => $v){
                $tmp['list'][] = [
                    'time' => $k,
                    'num' => $v
                ];
            }
            $final[] = $tmp;
        }

        return [true,$final];
    }

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

    /**
     * 报工
     * @param $data
     * @return array
     */
    public function statisticsReportWorkingChart($data){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '报工单生成时间必须选择！'];

        //人员
        $finished_id = $data['finished_id'] ?? [];
        //工序
        $process_id = $data['process_id'] ?? [];

        $result = ReportWorkingDetail::where('del_time',0)
            ->where('crt_time',">=",$data['crt_time'][0])
            ->where('crt_time',"<=",$data['crt_time'][1])
            ->when(! empty($finished_id), function ($query) use ($finished_id) {
                return $query->whereIn('finished_id', $finished_id);
            })
            ->when(! empty($process_id), function ($query) use ($process_id) {
                return $query->whereIn('process_id', $process_id);
            })
            ->get()->toArray();
        if(empty($result)) return [true, []];

        $emp_map = Employee::whereIn('id',array_unique(array_column($result,'finished_id')))
            ->pluck('emp_name','id')
            ->toArray();

        $process_map = Process::whereIn('id',array_unique(array_column($result,'process_id')))
            ->pluck('title','id')
            ->toArray();

        //派工单
        $d = DispatchSub::where('del_time',0)
            ->whereIn('id', array_unique(array_column($result,'data_id')))
            ->select('id', 'dispatch_no as order_no', 'product_title','technology_name','product_no','product_unit','price')
            ->get()->toArray();
        $d_tmp = array_column($d,null,'id');

        $return = [];
        foreach ($result as $value){
            $tmp = $d_tmp[$value['data_id']] ?? [];
            if(empty($tmp)) continue;

            $string = $value['finished_id'] . $value['process_id'];
            if(isset($return[$string])){
                $quantity = bcadd($value['quantity'], $return[$string]['quantity'], 3);
                $return[$string]['quantity'] = $quantity;
                if(! in_array($value['id'], $return[$string]['report_id'])){
                    $id = array_merge_recursive($return[$string]['report_id'], [$value['id']]);
                    $return[$string]['report_id'] = $id;
                }
            }else{
                $return[$string] = [
                    'finished_id' => $value['finished_id'],
                    'finished_title' => $emp_map[$value['finished_id']] ?? "",
                    'process_id' => $value['process_id'],
                    'process_title' => $process_map[$value['process_id']] ?? "",
                    'quantity' => $value['quantity'],
                    'report_id' => [$value['id']],
                ];
            }
        }

        return [true, array_values($return)];
    }

    public function statisticsReportWorkingChartDetail($data){
        if(empty($data['report_id'])) return [false, '报工单数据必须选择！'];

        $result = ReportWorkingDetail::where('del_time',0)
            ->whereIn('id', $data['report_id'])
            ->get()->toArray();
        if(empty($result)) return [false, '报工单数据不存在或已被删除'];

        //派工单
        $d = DispatchSub::where('del_time',0)
            ->whereIn('id', array_unique(array_column($result,'data_id')))
            ->select('id', 'dispatch_no as order_no', 'product_title','technology_name','product_no','product_unit','price')
            ->get()->toArray();
        $d_tmp = array_column($d,null,'id');

        $return = [];
        foreach ($result as $value){
            $tmp = $d_tmp[$value['data_id']] ?? [];
            if(empty($tmp)) continue;

            $string = $tmp['id'] . $tmp['product_no'] . $tmp['technology_name'];
            if(isset($return[$string])){
                $return[$string]['quantity'] += $value['quantity'];
            }else{
                $return[$string] = [
                    'order_no' => $tmp['order_no'] ?? "",
                    'product_title' => $tmp["product_title"] ?? "",
                    'product_no' => $tmp["product_no"] ?? "",
                    'product_unit' => $tmp["product_unit"] ?? "",
                    'technology_name' => $tmp['technology_name'] ?? "",
                    'wood_name' => $tmp['wood_name'] ?? "",
                    'quantity' => $value['quantity'],
                ];
            }
        }

        return [true, array_values($return)];
    }
}