hcsl/app/Service/ReportFormsService.php

<?php

namespace App\Service;

use App\Model\Employee;
use App\Model\OrdersProduct;
use App\Model\OrdersProductProcess;
use App\Model\Scrapp;
use App\Model\Team;
use Illuminate\Support\Facades\DB;

class ReportFormsService extends Service
{
    //生产进度
    public function productionReport($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');
        $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();
        //生产订单主表----------------

        //筛选出制单日期 分表的依据
        $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 = [];
        $process_id = $data['process_id'] ?? [];
        foreach ($out_time as $value){
            //子表搜索
            $models = new OrdersProductProcess(['channel' => $value]);
            $tmp = $models->whereIn('production_no',$production_no)
                ->where('del_time',0)
                ->whereIn('status',array(1,2))
                ->when(!empty($process_id), function ($query) use ($process_id) {
                    return $query->whereIn('process_id', $process_id);
                })
                ->select('production_no','process_id',DB::raw('SUM(CASE WHEN status >= 1 THEN 1 ELSE 0 END) as dispatch_count'),DB::raw('SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as finish_count'))
                ->groupBy('production_no','process_id')
                ->get()->toArray();//派工和完工数据

            foreach ($tmp as $t){
                $keys = $t['production_no'] . "|" .$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;
                }
            }
        }

        //返回统计数据
        date_default_timezone_set("PRC");
        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['production_no'] . "|";
            foreach ($detail as $key_son => $value_son) {
                if (strpos($key_son,$detail_key) !== false) {
                    $value_son['rate'] = number_format($value_son['finish_count'] / $value['order_quantity'], 2);
                    $list[$key]['process'][] = $value_son;
                }
            }

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

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

    private function checkSameQuarter($timestamps) {
        date_default_timezone_set("PRC");
        $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'] ?? [];
        //根据完工时间 扩大时间戳范围前后三个月
        $new_finish_time = $this->increaseTimeArea($data['finish_time']);
        //根据时间戳范围 获取分表的时间
        $return_time = $this->getTimeAreaData($new_finish_time);

        //检索分表数据
        $result = $team = [];
        foreach ($return_time as $value){
            //子表搜索
            $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= [];
        date_default_timezone_set("PRC");
        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);

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

    private function increaseTimeArea($time_area){
        // 增加三个月的时间戳
        $newStartTimestamp = strtotime('-3 months', $time_area[0]);
        $newEndTimestamp = strtotime('+3 months', $time_area[1]);

        return [$newStartTimestamp,$newEndTimestamp];
    }

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

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

        $list = OrdersProduct::whereIn('production_no',$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();

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

        return [true,$list];
    }

    //不良品
    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;
                }
            }
        }

        //返回统计数据
        date_default_timezone_set("PRC");
        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 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')
            ->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'];
                }
            }
        }

        date_default_timezone_set("PRC");

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

    //不良品原因
    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;
                }
            }
        }

        //返回统计数据
        date_default_timezone_set("PRC");
        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;
                }
            }
        }

        date_default_timezone_set("PRC");
        //次品原因 班组 人员
        $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];
    }
}