youfeng/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\Depart;
use App\Model\InOutRecord;
use App\Model\InvoiceOrder;
use App\Model\InvoiceOrderInfo;
use App\Model\Product;
use App\Model\ProductCategory;
use App\Model\PurchaseOrder;
use App\Model\PurchaseOrderInfo;
use App\Model\ReturnExchangeOrder;
use App\Model\ReturnExchangeOrderProductInfo;
use App\Model\SalesOrder;
use App\Model\SalesOrderProductInfo;
use Illuminate\Support\Facades\DB;

class StatisticsService extends Service
{
    //销售统计饼图 根据合同类型区分(例如线上合同、线下合同)
    public function statisticsBt($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择销售订单时间区间'];

        $model = SalesOrder::Clear($user,$data);
        $model = $model->where('del_time',0)
            ->select('model_type',DB::raw("sum(contract_fee) as total"))
            ->groupBy('model_type');

        if(! empty($data['crt_time'][0]) && ! empty($data['crt_time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
            $model->where('crt_time','>=',$return[0]);
            $model->where('crt_time','<=',$return[1]);
        }

        $list = $model->get()->toArray();
        $list = $this->fillStatisticsBt($list);

        return [true, $list];
    }

    public function fillStatisticsBt($data){
        if(empty($data)) return $data;

        foreach ($data as $key => $value){
            $data[$key]['model_type_title'] = SalesOrder::$model_type_title[$value['model_type']] ?? "";
        }

        return $data;
    }

    //大区 订货统计
    public function statisticsArea($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择时间区间'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);

        //大区
        $area = Depart::$area;
        $area_map = [];
        foreach ($area as $key => $value){
            $area_map[] = [
                'key' => $key,
                'title' => $value,
                'total' => 0
            ];
        }
        //分社所属大区
        $depart_map = Depart::where('parent_id',0)
            ->where('del_time',0)
            ->pluck('area','id')
            ->toArray();

        //向总社采购的钱
        $purchase_map = [];
        $purchase = PurchaseOrder::where('del_time',0)
            ->where('order_type',[PurchaseOrder::Order_type_three,PurchaseOrder::Order_type_four])
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->select('top_depart_id','purchase_total')
            ->get()->toArray();
        foreach ($purchase as $value){
            $area = $depart_map[$value['top_depart_id']] ?? 0;
            if(! $area) continue;
            if(isset($purchase_map[$area])){
                $total = bcadd($purchase_map[$area], $value['purchase_total'],2);
                $purchase_map[$area] = $total;
            }else{
                $purchase_map[$area] = $value['purchase_total'];
            }
        }

        //退货的差异
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->where('type',ReturnExchangeOrder::Order_type2)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->where('crt_time','<=',$return[1])
            ->select('top_depart_id','difference_amount')
            ->get()->toArray();
        foreach ($returnExchange as $value){
            $area = $depart_map[$value['top_depart_id']] ?? 0;
            if(! $area) continue;
            if(isset($returnExchange_map[$area])){
                $total = bcadd($returnExchange_map[$area], $value['difference_amount'],2);
                $returnExchange_map[$area] = $total;
            }else{
                $returnExchange_map[$area] = $value['difference_amount'];
            }
        }

        foreach ($area_map as $key => $value){
            $purchase_tmp = $purchase_map[$value['key']] ?? 0;
            $return_tmp = $returnExchange_map[$value['key']] ?? 0;
            $area_map[$key]['total'] = bcsub($purchase_tmp, $return_tmp,2);
        }

        return [true, $area_map];
    }

    // 大区下的门店  订货统计
    public function statisticsAreaDepart($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择时间区间'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
        if(empty($data['area']) || ! isset(Depart::$area[$data['area']])) return [false, '该大区不存在'];

        $depart = Depart::where('parent_id',0)
            ->where('del_time',0)
            ->where('area',$data['area'])
            ->select('id','title')
            ->get()
            ->toArray();
        if(empty($depart)) return [false, '该大区下不存在门店'];

        //向总社采购的钱
        $purchase_map = [];
        $purchase = PurchaseOrder::where('del_time',0)
            ->whereIn('top_depart_id',array_column($depart,'id'))
            ->where('order_type',[PurchaseOrder::Order_type_three,PurchaseOrder::Order_type_four])
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->select('top_depart_id','purchase_total')
            ->get()->toArray();
        foreach ($purchase as $value){
            if(isset($purchase_map[$value['top_depart_id']])){
                $total = bcadd($purchase_map[$value['top_depart_id']], $value['purchase_total'],2);
                $purchase_map[$value['top_depart_id']] = $total;
            }else{
                $purchase_map[$value['top_depart_id']] = $value['purchase_total'];
            }
        }

        //退货的差异
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->whereIn('top_depart_id',array_column($depart,'id'))
            ->where('type',ReturnExchangeOrder::Order_type2)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->where('crt_time','<=',$return[1])
            ->select('top_depart_id','difference_amount')
            ->get()->toArray();
        foreach ($returnExchange as $value){
            if(isset($returnExchange_map[$value['top_depart_id']])){
                $total = bcadd($returnExchange_map[$value['top_depart_id']], $value['difference_amount'],2);
                $returnExchange_map[$value['top_depart_id']] = $total;
            }else{
                $returnExchange_map[$value['top_depart_id']] = $value['difference_amount'];
            }
        }

        foreach ($depart as $key => $value){
            $purchase_tmp = $purchase_map[$value['id']] ?? 0;
            $return_tmp = $returnExchange_map[$value['id']] ?? 0;
            $depart[$key]['total'] = bcsub($purchase_tmp, $return_tmp,2);
        }

        return [true, $depart];
    }

    // 大区下的门店 某个门店 关于产品以及分类的统计  订货统计
    public function statisticsAreaDepartProduct($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择时间区间'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
        if(empty($data['top_depart_id'])) return [false, '请选择门店'];

        //时间 =》 钱
        $purchase_map1 = $this->getTime($return);
        //产品 =》 数量
        $purchase_category_map = $purchase_map = [];
        $purchase = PurchaseOrder::where('del_time',0)
            ->where('top_depart_id',$data['top_depart_id'])
            ->where('order_type',[PurchaseOrder::Order_type_three,PurchaseOrder::Order_type_four])
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->select('id','crt_time')
            ->get()->toArray();
        $purchase_for_time = array_column($purchase,'crt_time','id');
        $purchase_product = PurchaseOrderInfo::where("del_time",0)
            ->whereIn('purchase_order_id',array_column($purchase,'id'))
            ->select('purchase_order_id','product_id','number','price','final_amount','sports_bag_id')
            ->get()->toArray();

        //退换货
        $returnExchange_map = $this->returnExchange($data,$return);
        //产品
        $product_list = Product::whereIn('id',array_unique(array_merge_recursive(array_column($purchase_product,'product_id'),array_keys($returnExchange_map,'product_id'))))
            ->select('id','product_category','title')
            ->get()->toArray();
        $product_list_map = array_column($product_list,null,'id');

        $category_return = ProductCategory::where('del_time',0)
            ->where('parent_id',0)
            ->pluck('title','id')
            ->toArray();
        foreach ($purchase_product as $value){
            if($value['sports_bag_id'] > 0){
                $money = $value['final_amount'];
            }elseif($value['final_amount'] > 0){
                $money = $value['final_amount'];
            }else{
                $money = bcmul($value['price'],$value['number'],2);
            }
            $crt_time = $purchase_for_time[$value['purchase_order_id']];
            $crt_time = date("Y-m-d",$crt_time);

            //产品信息
            $product_tmp = $product_list_map[$value['product_id']] ?? [];

            //钱
            if(isset($purchase_map1[$crt_time])){
                $purchase_map1[$crt_time]['total'] = bcadd($purchase_map1[$crt_time]['total'],$money,2);
            }

            //-------根据产品
            //数量
            if(isset($purchase_map[$value['product_id']])){
                $tmp_number = bcadd($purchase_map[$value['product_id']]['number'], $value['number'],2);
                $purchase_map[$value['product_id']]['number'] = $tmp_number;
            }else{
                $purchase_map[$value['product_id']] = [
                    'title' => $product_tmp['title'],
                    'number' => $value['number']
                ];
            }
            //-------根据产品

            //-------根据产品大类
            //数量
            $category_tmp = json_decode($product_tmp['product_category']);
            $category_tmp = $category_tmp[0];

            if(isset($purchase_category_map[$category_tmp])){
                $tmp_number = bcadd($purchase_map[$value['product_id']]['number'], $value['number'],2);
                $purchase_category_map[$category_tmp]['number'] = $tmp_number;
            }else{
                $purchase_category_map[$category_tmp] = [
                    'number' => $value['number'],
                    'title' => $category_return[$category_tmp] ?? ""
                ];
            }
        }

        return [true, ['money' => array_values($purchase_map1), 'product_num' => array_values($purchase_map), 'category_num' => array_values($purchase_category_map)]];
    }

    public function getTime($data){
        $startTimestamp = $data[0]; // 起始时间戳
        $endTimestamp = $data[1];   // 结束时间戳

        // 创建 DateTime 对象
        $startDate = new \DateTime();
        $endDate = new \DateTime();

        $startDate->setTimestamp($startTimestamp);
        $endDate->setTimestamp($endTimestamp);

        // 创建 DatePeriod 对象
        $interval = new \DateInterval('P1D'); // 每天的间隔
        $dateRange = new \DatePeriod($startDate, $interval, $endDate);

        // 遍历日期范围并输出每个日期
        $return = [];
        foreach ($dateRange as $date) {
            $day = $date->format('Y-m-d');
            $return[$day] = [
                'day' => $day,
                'total' => 0
            ];
        }
        return $return;
    }

    public function returnExchange($data,$return){ return [];
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->where('top_depart_id',$data['top_depart_id'])
            ->where('type',ReturnExchangeOrder::Order_type2)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->select('id')
            ->get()->toArray();
        $returnExchange_product = ReturnExchangeOrderProductInfo::where("del_time",0)
            ->whereIn('return_exchange_id',array_column($returnExchange,'id'))
            ->select('product_id','number','return_exchange_price as price')
            ->get()->toArray();
        foreach ($returnExchange_product as $value){
            $money = bcmul($value['price'],$value['number'],2);

            if(isset($returnExchange_map[$value['product_id']])){
                $tmp_money = bcadd($returnExchange_map[$value['product_id']]['total'], $money,2);
                $tmp_number = bcadd($returnExchange_map[$value['product_id']]['number'], $value['number'],2);
                $returnExchange_map[$value['product_id']] = [
                    'number' => $tmp_number,
                    'total' => $tmp_money
                ];
            }else{
                $returnExchange_map[$value['product_id']] = [
                    'number' => $value['number'],
                    'total' => $money
                ];
            }
        }

        return $returnExchange_map;
    }

    public function statisticsJc($data,$user){
        if(empty($data['top_depart_id'])) return [false, '请选择门店'];
        $model = Product::ProductClear2($user,$data);
        $model = $model->where('del_time',0)
            ->select('title','id','code','depart_id','top_depart_id','product_attribute')
            ->orderby('product_attribute', 'desc')
            ->orderby('id', 'desc');

        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');
        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(isset($data['product_attribute'])) $model->where('product_attribute', $data['product_attribute']);
        if(! empty($data['product_category_id'])) $model->where('product_category_id', $data['product_category_id']);
        if(! empty($data['product_category'])) {
            $product_category = ProductCategory::where('del_time',0)
                ->where('title', 'LIKE', '%'.$data['product_category'].'%')
                ->select('id')
                ->get()->toArray();
            $model->whereIn('product_category_id',array_unique(array_column($product_category,'id')));
        }

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

        return [true, $list];
    }

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

        //产品
        $product = array_column($data['data'],'id');
        //本月入库 本月出库
        list($in, $out) = $this->getThisMonthData($product,$user,$search);
        //上月结存 汇总这个月之前的出入
        list($last_in,$last_out) = $this->getLastMonthBalance($product,$user,$search);

        foreach ($data['data'] as $key => $value){
            $last_in_tmp = $last_in[$value['id']] ?? [];
            $last_in_money = $last_in_tmp['total'] ?? 0;//本月之前入的金额
            $last_in_number = $last_in_tmp['number'] ?? 0;//本月之前入的数量
            $last_out_tmp = $last_out[$value['id']] ?? [];
            $last_out_money = $last_out_tmp['total'] ?? 0;//本月之前出的金额
            $last_out_number = $last_out_tmp['number'] ?? 0;//本月之前出的数量

            //上月结存
            $last_jc_money = bcsub($last_in_money,$last_out_money,2);//结存金额
            $last_jc_number = bcsub($last_in_number,$last_out_number,2);//结存数量
            $last_jc_price = floatval($last_jc_number) ? bcdiv($last_jc_money,$last_jc_number,2) : 0;//结存单价
            $data['data'][$key]['last_jc_money'] = $last_jc_money;
            $data['data'][$key]['last_jc_number'] = $last_jc_number;
            $data['data'][$key]['last_jc_price'] = $last_jc_price;

            //本月入库
            $this_in_tmp = $in[$value['id']] ?? [];
            $this_in_money = $this_in_tmp['total'] ?? 0;//本月入的金额
            $this_in_number = $this_in_tmp['number'] ?? 0;//本月入的数量
            $this_in_price = floatval($this_in_number) ? bcdiv($this_in_money,$this_in_number,2) : 0;//本月入单价
            $data['data'][$key]['this_in_money'] = $this_in_money;
            $data['data'][$key]['this_in_number'] = $this_in_number;
            $data['data'][$key]['this_in_price'] = $this_in_price;

            //本月出库
            $this_out_tmp = $out[$value['id']] ?? [];
            $this_out_money = $this_out_tmp['total'] ?? 0;//本月出的金额
            $this_out_number = $this_out_tmp['number'] ?? 0;//本月出的数量
            //单价= (上月结存金额+本月入库金额) /上月结存数量+本月入库数量
            $amount = bcadd($last_jc_money, $this_in_money,2);
            $number = bcadd($last_jc_number, $this_in_number,2);
            $this_out_price = floatval($number) ? bcdiv($amount,$number,2) : 0;//本月出单价
            $data['data'][$key]['this_out_money'] = $this_out_money;
            $data['data'][$key]['this_out_number'] = $this_out_number;
            $data['data'][$key]['this_out_price'] = $this_out_price;

            //本月结存
            //本月结存 数量/金额=本月入库+上月结存-本月出库
            $this_jc_money = bcsub(bcadd($this_in_money,$last_jc_money,2),$this_out_money);
            $this_jc_number = bcsub(bcadd($this_in_number,$last_jc_number,2),$this_out_number);
            $this_jc_price = floatval($this_jc_number) ? bcdiv($this_jc_money,$this_jc_number,2) : 0;//本月结存单价
            $data['data'][$key]['this_jc_money'] = $this_jc_money;
            $data['data'][$key]['this_jc_number'] = $this_jc_number;
            $data['data'][$key]['this_jc_price'] = $this_jc_price;
        }

        return $data;
    }

    //本月入库 出库(库存流水)
    public function getThisMonthData($product = [], $user = [], $search = []){
        $in = $out = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $endStamp = strtotime(date("Y-m-t 23:59:59"));

        //本月出和入的数据
        $model = InOutRecord::TopClear($user,$search);
        $list = $model->where('del_time',0)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->whereIn('product_id',$product)
            ->select('product_id','number','price')
            ->get()->toArray();

        foreach ($list as $value){
            if($value['number'] >= 0){
                $tmp_total = bcmul($value['number'], $value['price'], 2);
                if(isset($in[$value['product_id']])){
                    $number = bcadd($in[$value['product_id']]['number'], $value['number'],0);
                    $total = bcadd($in[$value['product_id']]['total'], $tmp_total,2);
                    $in[$value['product_id']]['number'] = $number;
                    $in[$value['product_id']]['total'] = $total;
                }else{
                    $in[$value['product_id']] = [
                        'number' => $value['number'],
                        'total' => $tmp_total,
                    ];
                }
            }else{
                $number = abs($value['number']);
                $tmp_total = bcmul($number, $value['price'], 2);
                if(isset($out[$value['product_id']])){
                    $number = bcadd($out[$value['product_id']]['number'], $number,0);
                    $total = bcadd($out[$value['product_id']]['total'], $tmp_total,2);
                    $out[$value['product_id']]['number'] = $number;
                    $out[$value['product_id']]['total'] = $total;
                }else{
                    $out[$value['product_id']] = [
                        'number' => $number,
                        'total' => $tmp_total,
                    ];
                }
            }
        }

        return [$in, $out];
    }

    //上月结存(汇总这个月之前的出入)(库存流水)
    public function getLastMonthBalance($product = [], $user = [], $search = []){
        $return = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $model = InOutRecord::TopClear($user,$search);
        $list = $model->where('del_time',0)
            ->where('crt_time','<',$startStamp)
            ->whereIn('product_id',$product)
            ->select('product_id','number','price')
            ->get()->toArray();

        foreach ($list as $value){
            if($value['number'] >= 0){
                $tmp_total = bcmul($value['number'], $value['price'], 2);
                if(isset($in[$value['product_id']])){
                    $number = bcadd($in[$value['product_id']]['number'], $value['number'],0);
                    $total = bcadd($in[$value['product_id']]['total'], $tmp_total,2);
                    $in[$value['product_id']]['number'] = $number;
                    $in[$value['product_id']]['total'] = $total;
                }else{
                    $in[$value['product_id']] = [
                        'number' => $value['number'],
                        'total' => $tmp_total,
                    ];
                }
            }else{
                $number = abs($value['number']);
                $tmp_total = bcmul($number, $value['price'], 2);
                if(isset($out[$value['product_id']])){
                    $number = bcadd($out[$value['product_id']]['number'], $number,0);
                    $total = bcadd($out[$value['product_id']]['total'], $tmp_total,2);
                    $out[$value['product_id']]['number'] = $number;
                    $out[$value['product_id']]['total'] = $total;
                }else{
                    $out[$value['product_id']] = [
                        'number' => $number,
                        'total' => $tmp_total,
                    ];
                }
            }
        }

        return [$in, $out];
    }

    //本月入库(单据)暂时没用
    public function getThisMonthIn1($product = [], $user = [], $search = []){
        $return = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $endStamp = strtotime(date("Y-m-t 23:59:59"));
        //本月采购单
        $model = PurchaseOrder::Clear($user,$search);
        $list = $model->where('del_time',0)
            ->where('state', PurchaseOrder::STATE_Four)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->select('id')
            ->get()->toArray();
        if(empty($list)) return $return;
        //本月采购产品
        $purchase_product_array = [];
        $purchase_product = PurchaseOrderInfo::where('del_time',0)
            ->whereIn('product_id',$product)
            ->whereIn('purchase_order_id',array_column($list,'id'))
            ->select('product_id','number','price')
            ->get()->toArray();
        foreach ($purchase_product as $value){
            $total = bcmul($value['number'],$value['price'],2);
            if(isset($purchase_product_array[$value['product_id']])){
                $purchase_product_array[$value['product_id']]['number'] += $value['number'];
                $total_tmp = bcadd($purchase_product_array[$value['product_id']]['total'], $total, 2);
                $purchase_product_array[$value['product_id']]['total'] = $total_tmp;
            }else{
                $purchase_product_array[$value['product_id']] = [
                    'number' => $value['number'],
                    'total' => $total,
                ];
            }
        }

        //本月退货(采购)
        $model2 = ReturnExchangeOrder::Clear($user, $search);
        $return_list = $model2->where('del_time',0)
            ->where('state', ReturnExchangeOrder::State_two)
            ->where('type',ReturnExchangeOrder::Order_type2)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->select('id')
            ->get()->toArray();
        //本月退货产品
        $return_product_array = [];
        if(! empty($return_list)){
            $return_product = ReturnExchangeOrderProductInfo::where('del_time',0)
                ->where('return_exchange_id', array_column($return_list, 'id'))
                ->whereIn('product_id',$product)
                ->where('return_or_exchange',ReturnExchangeOrderProductInfo::type_one)
                ->select('product_id','number','return_or_exchange')
                ->get()->toArray();
            foreach ($return_product as $value){
                $total = bcmul($value['number'],$value['return_or_exchange'],2);
                if(isset($return_product_array[$value['product_id']])){
                    $return_product_array[$value['product_id']]['number'] += $value['number'];
                    $total_tmp = bcadd($return_product_array[$value['product_id']]['total'], $total, 2);
                    $return_product_array[$value['product_id']]['total'] = $total_tmp;
                }else{
                    $return_product_array[$value['product_id']] = [
                        'number' => $value['number'],
                        'total' => $total,
                    ];
                }
            }
        }

        foreach ($return_product_array as $p => $n){
            $number_tmp = -$n['number'];
            $total_tmp = -$n['total'];
            $purchase_product_tmp = $purchase_product_array[$p] ?? [];
            if(empty($purchase_product_tmp)){
                $purchase_product_array[$p] = [
                    'number' => $number_tmp,
                    'total' => $total_tmp,
                ];
            }else{
                $purchase_product_array[$p]['number'] += $number_tmp;
                $total_tmp2 = bcadd($purchase_product_array[$p]['total'], $total_tmp, 2);
                $purchase_product_array[$p]['total'] += $total_tmp2;
            }
        }

        return $purchase_product_array;
    }


    //--------------------------脚本
    public function inoutrecord(){return;
        $in_data = InvoiceOrder::where('del_time',0)
            ->select('id','sales_order_id')
            ->get()->toArray();

        $map = [];
        $s_p = SalesOrderProductInfo::whereIn('sales_order_id',array_column($in_data,'sales_order_id'))
            ->where('del_time',0)
            ->select('sales_order_id','product_id','final_amount','price')
            ->get()->toArray();
        foreach ($s_p as $value){
            $map[$value['sales_order_id']][] = [
                'product_id' => $value['product_id'],
                'final_amount' => $value['final_amount'],
                'price' => $value['price'],
            ];
        }

        DB::beginTransaction();
        try {
            foreach ($in_data as $value){
                $tmp = $map[$value['sales_order_id']] ?? [];
                if(empty($tmp)) continue;
                foreach ($tmp as $val){
                    InvoiceOrderInfo::where('del_time',0)
                        ->where('invoice_id', $value['id'])
                        ->where('product_id', $val['product_id'])
                        ->update([
                            'final_amount' => $val['final_amount'],
                            'price' => $val['price'],
                        ]);
                }

            }
        }catch (\Throwable $exception){
            DB::rollBack();
            dd($exception->getMessage());
        }

        DB::commit();
        dd(1);

    }

    public function inoutrecord2(){return;
        DB::beginTransaction();
        try {
            DB::table('in_out_record')
                ->where('price',0)
                ->whereIn('order_type', array_values(ReturnExchangeOrder::$prefix))
                ->select('id','order_number','product_id')
                ->orderBy('id','asc')
                ->chunk(200,function ($data) {;
                    $data = Collect($data)->map(function ($object) {
                        return (array)$object;
                    })->toArray();

                    $map = ReturnExchangeOrder::where('del_time',0)
                        ->whereIn('order_number',array_column($data,'order_number'))
                        ->pluck('id','order_number')
                        ->toArray();
                    $result = ReturnExchangeOrderProductInfo::where('del_time',0)
                        ->whereIn('return_exchange_id',array_values($map))
                        ->select('return_exchange_id','return_exchange_price','product_id')
                        ->get()->toArray();
                    $result_map = [];
                    foreach ($result as $v){
                        $result_map[$v['return_exchange_id']][$v['product_id']] = [
                            'product_id' => $v['product_id'],
                            'price' => $v['return_exchange_price'],
                        ];
                    }

                    foreach ($data as $value){
                        $tmp_id = $map[$value['order_number']] ?? 0;
                        if($tmp_id < 0) continue;
                        $tmp = $result_map[$tmp_id][$value['product_id']] ?? [];
                        if(empty($tmp)) continue;

                        InOutRecord::where('id',$value['id'])->update([
                            'price' => $tmp['price']
                        ]);
                    }
                });

            DB::commit();
        }catch (\Throwable $exception){
            DB::rollBack();
            dd($exception->getMessage());
        }
        dd(1);
    }

    public function statisticsAreaDepartProduct222($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择时间区间'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
        if(empty($data['top_depart_id'])) return [false, '请选择门店'];

        //向总社采购的钱
        $purchase_map = [];
        $purchase = PurchaseOrder::where('del_time',0)
            ->where('top_depart_id',$data['top_depart_id'])
            ->where('order_type',[PurchaseOrder::Order_type_three,PurchaseOrder::Order_type_four])
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->select('id')
            ->get()->toArray();
        $purchase_product = PurchaseOrderInfo::where("del_time",0)
            ->whereIn('purchase_order_id',array_column($purchase,'id'))
            ->select('product_id','number','price','final_amount','sports_bag_id')
            ->get()->toArray();
        foreach ($purchase_product as $value){
            if($value['sports_bag_id'] > 0){
                $money = $value['final_amount'];
            }elseif($value['final_amount'] > 0){
                $money = $value['final_amount'];
            }else{
                $money = bcmul($value['price'],$value['number'],2);
            }

            if(isset($purchase_map[$value['product_id']])){
                $tmp_money = bcadd($purchase_map[$value['product_id']]['total'], $money,2);
                $tmp_number = bcadd($purchase_map[$value['product_id']]['number'], $value['number'],2);
                $purchase_map[$value['product_id']] = [
                    'number' => $tmp_number,
                    'total' => $tmp_money
                ];
            }else{
                $purchase_map[$value['product_id']] = [
                    'number' => $value['number'],
                    'total' => $money
                ];
            }
        }

        //退货的差异
        $returnExchange_map = $this->returnExchange($data,$return);

        //产品
        $product_list = Product::whereIn('id',array_unique(array_merge_recursive(array_column($purchase_product,'product_id'),array_keys($returnExchange_map,'product_id'))))
            ->select('id','product_category','title')
            ->get()->toArray();
        $product_list_map = array_column($product_list,null,'id');

        $category = $category_sum = [];
        foreach ($purchase_map as $key => $value){
//            if(isset($returnExchange_map[$key])){
//                $tmp = $returnExchange_map[$key];
//                $number = bcsub($value['number'], $tmp['number'],2);
//                $total = bcsub($value['total'], $tmp['total'],2);
//                $purchase_map[$key] = [
//                    'number' => $number,
//                    'total' => $total,
//                ];
//            }
            $product_tmp = $product_list_map[$key] ?? [];
            $purchase_map[$key]['title'] = $product_tmp['title'];
            $category_tmp = json_decode($product_tmp['product_category']);
            $category_tmp = $category_tmp[0];
            if(! in_array($category_tmp,$category)) $category[] = $category_tmp;

            if(isset($category_sum[$category_tmp])){
                $tmp_number = bcadd($category_sum[$category_tmp]['number'], $purchase_map[$key]['number'],2);
                $tmp_total = bcadd($category_sum[$category_tmp]['total'], $purchase_map[$key]['total'],2);
                $category_sum[$category_tmp] = [
                    'number' => $tmp_number,
                    'total' => $tmp_total,
                ];
            }else{
                $category_sum[$category_tmp] = [
                    'number' =>  $purchase_map[$key]['number'],
                    'total' => $purchase_map[$key]['total'],
                ];
            }
        }

        //根据产品大类 $category_sum
        $category_return = ProductCategory::whereIn('id',$category)
            ->select('id','title')
            ->get()->toArray();
        foreach ($category_return as $key => $value){
            $tmp = $category_sum[$value['id']] ?? [];
            $category_return[$key]['number'] = $tmp['number'];
            $category_return[$key]['total'] = $tmp['total'];
        }

        //根据产品 $purchase_map
        dd($purchase_map);


        return [true, ''];
    }
}