hcsl/app/Service/FyyOrderService.php

<?php

namespace App\Service;

use App\Jobs\ProcessDataJob;
use App\Model\Box;
use App\Model\BoxDetail;
use App\Model\Orders;
use App\Model\SaleOrdersProduct;
use App\Model\SaleOrdersProductStockDetail;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class FyyOrderService extends Service
{
    public function salesOrderList($data,$user){
        $model = SaleOrdersProduct::where('del_time',0)
            ->select('out_order_no','customer_name','order_no')
            ->groupBy('out_order_no')
            ->orderBy('crt_time','desc')
            ->orderBy('id','asc');

        if(! empty($data['out_order_no'])) {
            if(! is_array($data['out_order_no'])) {
                $model->where('out_order_no', 'LIKE', '%'.$data['out_order_no'].'%');
            }else{
                $model->whereIn('out_order_no', $data['out_order_no']);
            }
        }

        $list = $this->limit($model,'',$data);

        return [true, $list];
    }

    public function edit($data){
        return [true,'保存成功！'];
    }

    public function setOrderNo(){
        $order_no = date('Ymd') . time() . rand(1000, 9999);
        return $order_no;
    }

    public function add($data,$user){
        //获取数据
        $sqlServerModel = new FyySqlServerService($user);
        list($status,$return,$return_stock_detail) = $sqlServerModel->getDataFromSqlServer($data);
        if(! $status) return [false, $return];

        //数据校验
//        list($status,$msg) = $this->orderRule($return);
//        if(!$status) return [$status,$msg];

        try{
            DB::beginTransaction();

            $keys = array_unique(array_column($return,'out_order_no'));
            $map = array_fill_keys($keys, 0);

            $orders = [];
            foreach ($return as $key => $value){
                if(! empty($map[$value['out_order_no']])){
                    $order_no = $map[$value['out_order_no']];
                }else{
                    $order_no = $this->setOrderNo();
                    $map[$value['out_order_no']] = $order_no;

                    $orders[] = [
                        'order_no' => $order_no,
                        'out_order_no' => $value['out_order_no'],
                        'crt_time' => time()
                    ];
                }
                $return[$key]['order_no'] = $order_no;
                $return[$key]['crt_id'] = $user['id'];
                $return[$key]['crt_time'] = time();
            }

            $args = '';
            if(! empty($return_stock_detail)){
                foreach ($return_stock_detail as $value){
                    $args .= "(product_no = '{$value['product_no']}' and technology_name = '{$value['technology_name']}') OR ";
                }
                $args = rtrim($args,'OR ');
                SaleOrdersProductStockDetail::where('del_time',0)
                    ->whereRaw("($args)")
                    ->update(['del_time' => time()]);
            }

            Orders::insert($orders);
            SaleOrdersProduct::insert($return);
            SaleOrdersProductStockDetail::insert($return_stock_detail);

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false,$e->getLine().':'.$e->getMessage()];
        }

        return [true,'保存成功！'];
    }

    public function del($data){
        if($this->isEmpty($data,'id')) return [false,'ID不能为空！'];

        Orders::whereIn('id',$data['id'])->update([
            'del_time' => time()
        ]);

        SaleOrdersProduct::where('id',$data['id'])->update([
            'del_time'=>time()
        ]);

        return [true,'删除成功'];
    }

    public function orderDetail($data){
        return [200,''];
    }

    public function orderList($data){
        $model = SaleOrdersProduct::where('del_time',0)
            ->select('id','order_no','out_order_no','out_order_no_time','customer_no','customer_name','table_header_mark','product_no','product_title','product_size','product_unit','order_quantity','technology_material','technology_name','wood_name','process_mark','table_body_mark','out_crt_man','out_checker_man','out_checker_time','production_quantity','box_num','finished_num','shipment_num','pre_shipment_time')
            ->orderBy('crt_time','desc')
            ->orderBy('id','asc');

        if(! empty($data['order_no'])) $model->where('order_no', 'LIKE', '%'.$data['order_no'].'%');
        if(! empty($data['out_order_no'])) {
            if(! is_array($data['out_order_no'])) {
                $model->where('out_order_no', 'LIKE', '%'.$data['out_order_no'].'%');
            }else{
                $model->whereIn('out_order_no', $data['out_order_no']);
            }
        }
        if(! empty($data['customer_name'])) $model->where('customer_name', 'LIKE', '%'.$data['customer_name'].'%');
        if(! empty($data['shipment_order_no'])) {
            //$data['product_no'].'_'.$data['technology_material'].'_'.$data['technology_name'].'_'.$data['wood_name'].'_'.$data['process_mark'];
            $top_order_nos = Box::where('shipment_order_no','like','%'.$data['shipment_order_no'].'%')->pluck('top_order_no')->toArray();
            $model->wherein('order_no',$top_order_nos);
        }
        if(! empty($data['product_no'])) $model->where('product_no', 'LIKE', '%'.$data['product_no'].'%');
        if(! empty($data['product_title'])) $model->where('product_title', 'LIKE', '%'.$data['product_title'].'%');
        if(! empty($data['product_size'])) $model->where('product_size', 'LIKE', '%'.$data['product_size'].'%');
        if(! empty($data['technology_material'])) $model->where('technology_material', 'LIKE', '%'.$data['technology_material'].'%');
        if(! empty($data['technology_name'])) $model->where('technology_name', 'LIKE', '%'.$data['technology_name'].'%');
        if(! empty($data['wood_name'])) $model->where('wood_name', 'LIKE', '%'.$data['wood_name'].'%');
        if(! empty($data['process_mark'])) $model->where('process_mark', 'LIKE', '%'.$data['process_mark'].'%');
        if(! empty($data['table_header_mark'])) $model->where('table_header_mark', 'LIKE', '%'.$data['table_header_mark'].'%');
        if(! empty($data['table_body_mark'])) $model->where('table_body_mark', 'LIKE', '%'.$data['table_body_mark'].'%');
        if(! empty($data['out_checker_man'])) $model->where('out_checker_man', 'LIKE', '%'.$data['out_checker_man'].'%');
        if(! empty($data['out_crt_man'])) $model->where('out_crt_man', 'LIKE', '%'.$data['out_crt_man'].'%');
        if(! empty($data['out_checker_time'][0]) && ! empty($data['out_checker_time'][1])) $model->whereBetween('out_checker_time',[$data['out_checker_time'][0],$data['out_checker_time'][1]]);
        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['pre_shipment_time'][0]) && ! empty($data['pre_shipment_time'][1])) $model->whereBetween('pre_shipment_time',[$data['pre_shipment_time'][0],$data['pre_shipment_time'][1]]);
        if(isset($data['status'])) $model->where('status',$data['status']);
        if(isset($data['is_create'])) {
            if($data['is_create']){
                $model->whereColumn('production_quantity', '>=', 'order_quantity');
            }else{
                $model->whereColumn('order_quantity', '>', 'production_quantity');
            }
        }
        $list = $this->limit($model,'',$data);
        $list = $this->fillData($list);
        if(isset($data['box_type'])&&$data['box_type'] == 1){
            $out_order_nos = [];
            foreach ($list['data'] as $v){
                $out_order_nos[] = $v['out_order_no'];
            }

            $transport_nos = Box::wherein('out_order_no',$out_order_nos)->select('*')->get()->toArray();
            $transport_key_nos = [];
            foreach ($transport_nos as $v){
                $key = $v['top_order_no'];
                if(!isset($transport_key_nos[$key])) $transport_key_nos[$key] = [];
                if(!empty($v['shipment_order_no']) && ! in_array($v['shipment_order_no'], $transport_key_nos[$key])) $transport_key_nos[$key][] = $v['shipment_order_no'];
            }

            foreach ($list['data'] as &$v){
                $key = $v['order_no'];
                if(isset($transport_key_nos[$key])) $v['shipment_order_no'] = implode(',',$transport_key_nos[$key]);

                else $v['shipment_order_no'] = '';
            }
        }


        return [true, $list];
    }

    public function fillData($data){
        if(empty($data['data'])) return $data;

        $product_no = array_filter(array_column($data['data'],'product_no'));
        $detail = SaleOrdersProductStockDetail::where('del_time',0)
            ->whereIn('product_no',$product_no)
            ->select('product_no','product_quantity_on_hand','technology_name','wood_name','warehouse_name')
            ->get()->toArray();
        $detailMap = $detail_map = [];
        foreach ($detail as $value){
            $keys = $value['product_no'] . $value['technology_name'] . $value['wood_name'];
            $detail_map[$keys][] = [
                'warehouse_name' => $value['warehouse_name'],
                'product_quantity_on_hand' => $value['product_quantity_on_hand']
            ];

            if(isset($detailMap[$keys])){
                $detailMap[$keys] += $value['product_quantity_on_hand'];
            }else{
                $detailMap[$keys] = $value['product_quantity_on_hand'];
            }
        }

        foreach ($data['data'] as $key => $value){
            $keys = $value['product_no'] . $value['technology_name'] . $value['wood_name'];
            $data['data'][$key]['sub'] = $detail_map[$keys] ?? [];
            $data['data'][$key]['out_order_no_time'] = $value['out_order_no_time'] ? date('Y-m-d',$value['out_order_no_time']) : '';
            $data['data'][$key]['out_checker_time'] = $value['out_checker_time'] ? date('Y-m-d',$value['out_checker_time']) : '';
            $data['data'][$key]['pre_shipment_time'] = $value['pre_shipment_time'] ? date('Y-m-d',$value['pre_shipment_time']) : '';
            $data['data'][$key]['product_quantity_on_hand'] = $detailMap[$keys] ?? 0;
            if($value['production_quantity'] >= $value['order_quantity']){
                $data['data'][$key]['is_create'] = 1;
            }else{
                $data['data'][$key]['is_create'] = 0;
            }
        }
        $data['order_quantity'] = $this->sumWithPrecision($data['data'],'order_quantity');
        $data['finished_num'] = $this->sumWithPrecision($data['data'],'finished_num');
        $data['product_quantity_on_hand'] = $this->sumWithPrecision($data['data'],'product_quantity_on_hand');
        return $data;
    }

    function sumWithPrecision(array $data, string $column, int $scale = 3): string
    {
        $sum = '0';
        foreach ($data as $item) {
            if (isset($item[$column])) {
                $sum = bcadd($sum, (string)$item[$column], $scale);
            }
        }
        return $sum;
    }

    public function orderRule($data){
        $result = Orders::where('del_time',0)
            ->whereIn('out_order_no',array_column($data,'out_order_no'))
            ->select('out_order_no')
            ->get()->toArray();
        if(! empty($result))  return [false,'查询区间内销售订单号已存在'];

        return [true,''];
    }

    public function fyyRefreshOnHandQuantity($data,$user){
        //获取数据
        $sqlServerModel = new FyySqlServerService($user);
        list($status,$return,$return_product) = $sqlServerModel->getDataFromSqlServerForOnHand($data);
        if(! $status) return [false, $return];

        DB::beginTransaction();
        try {
            $args = '';
            foreach ($return_product as $value){
                $args .= "(product_no = '{$value['product_no']}' and technology_name = '{$value['technology_name']}') OR ";
            }
            $args = rtrim($args,'OR ');
            SaleOrdersProductStockDetail::where('del_time',0)
                ->whereRaw("($args)")
                ->update(['del_time' => time()]);

            SaleOrdersProductStockDetail::insert($return);

            DB::commit();
        }catch (\Throwable $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true, ''];
    }

    public function fyySaveOutOrder($data,$user){
        list($status,$msg) = $this->orderOutRule($data);
        if(! $status) return [false,$msg];

        $user = [
            'id' => $user['id'],
            'operate_time' => time(),
            'zt' => Request()->header('zt'),
        ];
        $redis = [
            'result' => $msg,
            'data' => $data,
        ];
        $job = dispatch(new ProcessDataJob($redis,$user,2))->onQueue(ProcessDataJob::job_two);
        if(! $job)  return [false,'任务没有进入队列！'];

        //错误计数
        Redis::hSet('order_failures', md5(json_encode($redis)), 0);

        //标记进入队列的数据
        Box::whereIn('order_no',$data['order_no'])->update([
            'state' => 1,
        ]);

        return [true,'任务已进入队列！'];
    }

    public function addInJob($result,$data,$user){
        try{
            //获取数据库连接
            $database = (new FinishedOrderService())->getConnectionName($user['zt']);

            $sqlServerModel = new FyySqlServerService($user);
            if($sqlServerModel->error) return [false,$sqlServerModel->error];

//            list($status,$msg) = $sqlServerModel->U8Rdrecord32Save($data['post']);
//            if(! $status) return [false,$msg];

            //更新
            DB::beginTransaction();
            $model_box = new Box();
            $model_box->setConnection($database);
            $model_box->whereIn('order_no',$data['order_no'])->update([
                'state' => 2,
//                'shipment_order_no' => $data['shipment_code'],//发货单号
            ]);
//            $box = new BoxDetail(['channel'=>$data['top_order_no']]);
//            $box->setConnection($database);
//            $box->where('order_no',$data['order_no'])->update([
//                'shipment_order_no' => $data['shipment_code']//发货单号
//            ]);

//            $models = new SaleOrdersProduct();
//            $models->setConnection($database);
//            foreach ($data['box_data'] as $value){
//                $models->where('id',$value['top_id'])->update([
//                    'shipment_num' => DB::raw("shipment_num + {$value['num']}"),
//                ]);
//            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false,$e->getFile() . $e->getLine(). $e->getMessage()];
        }

        return [true,''];
    }

    public function orderOutRule($data){
        if(empty($data['order_no'])) return [false,'包装单号不能为空！'];
//        if(empty($data['order_number'])) return [false,'销售订单号不能为空！'];
        if(empty($data['post'])) return [false,'发货单数据不能为空！'];
//        if(empty($data['box_data'])) return [false,'包装单数据不能为空！'];
//        if(empty($data['top_order_no'])) return [false,'顶部订单号不能为空！'];
//        if(empty($data['shipment_code'])) return [false,'发货单订单号不能为空！'];

        $boxList = Box::whereIn('order_no',$data['order_no'])->get()->toArray();
        if(empty($boxList)) return [false,'包装单不存在！'];

        foreach ($boxList as $value){
            if($value['del_time'] > 0) return [false,'包装单:' . $value['order_no'] . '已删除'];
            if($value['state'] == 1) return [false,'包装单:' . $value['order_no'] . '已操作出库！'];
            if($value['state'] > 1) return [false,'包装单:' . $value['order_no'] . '已出库！'];
        }

        return [true, ''];
    }

    public function orderMobileList($data){
        $list = [];
        if($data['type'] == 1){
            $list = SaleOrdersProduct::where('del_time',0)
                ->select('order_no','out_order_no','customer_name')
                ->where('box_num', '>', 0)
                ->whereColumn('order_quantity', '>', 'shipment_num')
                ->groupBy('order_no')
                ->orderBy('id','desc')
                ->get()->toArray();
            $key_list = [];
            foreach ($list as $v){
                $customer_name = trim($v['customer_name']);
                if(! isset($key_list[$customer_name])) $key_list[$customer_name] = [
                    'customer_name' => $customer_name,
                    'list' => [],
                ];
                $key_list[$customer_name]['list'][] = $v;
            }
            sort($key_list);
            $list = $key_list;
        }elseif($data['type'] == 2){
            $list = SaleOrdersProduct::where('del_time',0)
                ->select('order_no','out_order_no','customer_name')
//                ->whereColumn('order_quantity', '>', 'box_num')
                ->where(function ($query){
                    $query->whereColumn('order_quantity', '>', 'box_num')->orwhereColumn('box_num','=','order_quantity');
                })
                ->groupBy('order_no')
                ->orderBy('id','desc')
                ->get()->toArray();
            $key_list = [];
            foreach ($list as $v){
                $customer_name = $v['customer_name'];
                if(!isset($key_list[$customer_name])) $key_list[$customer_name] = [
                    'customer_name' => $customer_name,
                    'list' => [],
                ];
                $key_list[$customer_name]['list'][] = $v;
            }
            sort($key_list);
            $list = $key_list;
        }

        return [true, $list];
    }

    public function getShipmentOrder($data,$user){
        if(empty($data['time'][0]) || empty($data['time'][1])) return ['false', '时间区间不能为空！'];

        $sqlServerModel = new FyySqlServerService($user);
        if($sqlServerModel->error) return [false,$sqlServerModel->error];

        $result = $sqlServerModel->getDataFromDispatchList($data);
        $return = $shipment_code = [];
        if(! empty($result)){
            foreach ($result as $value){
                $cfree1 = $value['cfree1'] ?? '';
                $cfree2 = $value['cfree2'] ?? '';
                $tmp_key = $value['cdlcode'] . $value['cinvcode'] . $cfree1 . $cfree2;
                $n = $value['iquantity'] - ($value['out_quantity'] ?? 0);
                if(isset($return[$tmp_key])){
                    $return[$tmp_key]['num'] += $n;
                }else{
                    $return[$tmp_key] = [
                        'csocode' => $value['csocode'],//销售订单号
                        'cdlcode' => $value['cdlcode'],
                        'product_no' => $value['cinvcode'],//产品编码
                        'product_title' => $value['product_title'],
                        'product_size' => $value['product_size'] ?? "",
                        'technology_name' => $cfree1 ?? '',
                        'wood_name' => $cfree2 ?? '',
                        'num' => $n,
                        'technology_material' => $value['technology_material'] ?? '',
                        'process_mark' => $value['process_mark'] ?? '',
                    ];
                }

                if(! isset($shipment_code[$value['cdlcode']])){
                    $shipment_code[$value['cdlcode']] = [
                        'shipment_code' => $value['cdlcode'],
                        'order_no' => $value['csocode'],
                        'customer_name' => $value['customer_name']
                    ];
                }
            }
        }

        return [true,['shipment_code'=>array_values($shipment_code), 'show'=>array_values($return), 'post'=>$result]];
    }

    public function getOutOrderNum($data){
        $order = Orders::where('del_time',0)
            ->select('out_order_no')
            ->get()->toArray();

        return [true, array_column($order,'out_order_no')];
    }

    public function getYyShipmentOrder($data,$user){
        if(empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间区间不能为空！'];

        $sqlServerModel = new FyySqlServerService($user);
        if($sqlServerModel->error) return [false,$sqlServerModel->error];
        $result = $sqlServerModel->getDataFromDispatchListPage($data);

        return [true,$result];
    }

    public function getStorehouseDataFromSqlServer($data,$user){
        $sqlServerModel = new FyySqlServerService($user);
        if($sqlServerModel->error) return [false,$sqlServerModel->error];

        $result = $sqlServerModel->getStorehouseDataFromSqlServer($data);

        return [true, $result];
    }

    public function getProductDataFromSqlServer($data,$user){
        $sqlServerModel = new FyySqlServerService($user);
        if($sqlServerModel->error) return [false,$sqlServerModel->error];

        $result = $sqlServerModel->getProductFromSqlServer($data);

        return [true, $result];
    }

    public function getProductBzDataFromSqlServer($data,$user){
        $sqlServerModel = new FyySqlServerService($user);
        if($sqlServerModel->error) return [false,$sqlServerModel->error];

        $result = $sqlServerModel->getProductBzFromSqlServer($data);

        return [true, $result];
    }
}