tytplus/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\Depart;
use App\Model\DepartIndex;
use App\Model\InOutRecord;
use App\Model\InvoiceOrder;
use App\Model\InvoiceOrderInfo;
use App\Model\LastJc;
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 statisticsProvince($data,$user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '请选择时间区间'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);

        //销售订单类型
        $model_type = 0;
        if(! empty($data['model_type'])) $model_type = $data['model_type'];

        //门店设置的指标 按照区域汇总
        $index_map = DepartIndex::where('del_time',0)
            ->pluck('param_one','top_depart_id')
            ->toArray();
        //分社所属省
        $depart_map = $province_map = [];
        $depart = Depart::where('parent_id',0)
            ->where('province','<>','')
            ->where('del_time',0)
            ->select('province','id')
            ->get()->toArray();
        foreach ($depart as $value){
            $depart_map[$value['id']] = $value['province'];
            $province_map[$value['province']][] = $value['id'];
        }

        $address_map = config('address');
        $address_map = array_column($address_map,'label','value');

        //省
        $final_address_map = [];
        foreach ($address_map as $key => $value){
            $tmp = [
                'key' => $key,
                'title' => $value,
                'total' => 0,
                'index' => 0
            ];
            $top_depart_id = $province_map[$key] ?? [];
            if(! empty($top_depart_id)){
                foreach ($top_depart_id as $depart_id){
                    $index = $index_map[$depart_id] ?? 0;
                    $tmp['index'] = bcadd($tmp['index'], $index,2);
                }
            }
            $final_address_map[] = $tmp;
        }

        $model = SalesOrder::Clear($user,$data);
        $sale_order = $model->where("del_time",0)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->when(! empty($model_type), function ($query) use ($model_type) {
                return $query->where('model_type',$model_type);
            })
            ->select('id','top_depart_id','contract_fee')
            ->get()->toArray();

        //合同
        $purchase_map = [];
        foreach ($sale_order as $value){
            $area = $depart_map[$value['top_depart_id']] ?? 0;
            if(! $area) continue;
            if(isset($purchase_map[$area])){
                $total = bcadd($purchase_map[$area], $value['contract_fee'],2);
                $purchase_map[$area] = $total;
            }else{
                $purchase_map[$area] = $value['contract_fee'];
            }
        }

        //退货的差异
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->where('type',ReturnExchangeOrder::Order_type)
            ->whereIn('data_id',array_column($sale_order,'id'))
            ->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 ($final_address_map as $key => $value){
            $purchase_tmp = $purchase_map[$value['key']] ?? 0;
            $return_tmp = $returnExchange_map[$value['key']] ?? 0;
            $final_address_map[$key]['total'] = bcsub($purchase_tmp, $return_tmp,2);
        }

        usort($final_address_map, function($a, $b) {
            return $b['total'] - $a['total'];
        });

        return [true, $final_address_map];
    }

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

        //销售订单类型
        $model_type = 0;
        if(! empty($data['model_type'])) $model_type = $data['model_type'];

        //门店设置的指标 按照区域汇总
        $index_map = DepartIndex::where('del_time',0)
            ->pluck('param_one','top_depart_id')
            ->toArray();
        //分社所属大区
        $depart_map = $area_map = [];
        $depart = Depart::where('parent_id',0)
            ->where('area','>',0)
            ->where('del_time',0)
            ->select('area','id')
            ->get()->toArray();
        foreach ($depart as $value){
            $depart_map[$value['id']] = $value['area'];
            $area_map[$value['area']][] = $value['id'];
        }

        //大区
        $area = Depart::$area;
        $area_map = [];
        foreach ($area as $key => $value){
            $tmp = [
                'key' => $key,
                'title' => $value,
                'total' => 0,
                'index' => 0
            ];
            $top_depart_id = $area_map[$key] ?? [];
            if(! empty($top_depart_id)){
                foreach ($top_depart_id as $depart_id){
                    $index = $index_map[$depart_id] ?? 0;
                    $tmp['index'] = bcadd($tmp['index'], $index,2);
                }
            }
            $area_map[] = $tmp;
        }

        //合同
        $model = SalesOrder::Clear($user,$data);
        $sale_order = $model->where("del_time",0)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->when(! empty($model_type), function ($query) use ($model_type) {
                return $query->where('model_type',$model_type);
            })
            ->select('top_depart_id','contract_fee')
            ->get()->toArray();

        $purchase_map = [];
        foreach ($sale_order as $value){
            $area = $depart_map[$value['top_depart_id']] ?? 0;
            if(! $area) continue;
            if(isset($purchase_map[$area])){
                $total = bcadd($purchase_map[$area], $value['contract_fee'],2);
                $purchase_map[$area] = $total;
            }else{
                $purchase_map[$area] = $value['contract_fee'];
            }
        }

        //退货的差异
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->where('type',ReturnExchangeOrder::Order_type)
            ->whereIn('data_id',array_column($sale_order,'id'))
            ->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);
        }

        usort($area_map, function($a, $b) {
            return $b['total'] - $a['total'];
        });

        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']) && empty($data['province'])) return [false, '大区或省份必须选择一个'];
        if(! empty($data['area']) && ! empty($data['province']))return [false, '大区或省份有且只能有一个'];
        if(! empty($data['area']) && ! isset(Depart::$area[$data['area']])) return [false, '该大区不存在'];
        $address_map = config('address');
        $address_map = array_column($address_map,'label','value');
        if(! empty($data['province']) && ! isset($address_map[$data['province']])) return [false, '该省不存在'];

        //销售订单类型
        $model_type = 0;
        if(! empty($data['model_type'])) $model_type = $data['model_type'];

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

        //门店设置的指标
        $index_map = DepartIndex::where('del_time',0)
            ->whereIn('top_depart_id',array_column($depart,'id'))
            ->pluck('param_one','top_depart_id')
            ->toArray();

        //合同
        $sale_order = SalesOrder::where("del_time",0)
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->when(! empty($model_type), function ($query) use ($model_type) {
                return $query->where('model_type',$model_type);
            })
            ->select('top_depart_id','contract_fee')
            ->get()->toArray();
        //向总社采购的钱
        $purchase_map = [];
        foreach ($sale_order as $value){
            if(isset($purchase_map[$value['top_depart_id']])){
                $total = bcadd($purchase_map[$value['top_depart_id']], $value['contract_fee'],2);
                $purchase_map[$value['top_depart_id']] = $total;
            }else{
                $purchase_map[$value['top_depart_id']] = $value['contract_fee'];
            }
        }

        //退货的差异
        $returnExchange_map = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->whereIn('top_depart_id',array_column($depart,'id'))
            ->where('type',ReturnExchangeOrder::Order_type)
            ->whereIn('data_id',array_column($sale_order,'id'))
            ->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);
            $depart[$key]['index'] = $index_map[$value['id']] ?? 0;
        }

        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, '请选择门店'];

        //销售订单类型
        $model_type = 0;
        if(! empty($data['model_type'])) $model_type = $data['model_type'];

        //时间 =》 钱
        $purchase_map1 = $this->getTime($return);
        //产品 =》 数量
        $purchase_category_map = $purchase_map = [];

        $sale_order = SalesOrder::where("del_time",0)
            ->where('top_depart_id',$data['top_depart_id'])
            ->where('crt_time','>=',$return[0])
            ->where('crt_time','<=',$return[1])
            ->when(! empty($model_type), function ($query) use ($model_type) {
                return $query->where('model_type',$model_type);
            })
            ->select('id','crt_time')
            ->get()->toArray();
        $purchase_for_time = array_column($sale_order,'crt_time','id');
        $sale_order_id = array_column($sale_order,'id');
        $purchase_product = SalesOrderProductInfo::where("del_time",0)
            ->whereIn('sales_order_id',$sale_order_id)
            ->select('sales_order_id','product_id','number','price','final_amount','sports_bag_id')
            ->get()->toArray();

        //退换货
        list($returnExchange_map,$returnExchange_map_2) = $this->returnExchange($data,$sale_order_id);
        //产品
        $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','code')
            ->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['sales_order_id']];
            $crt_time = date("Y-m-d",$crt_time);

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

            $time_money = 0;
            if(isset($returnExchange_map_2[$crt_time])) {
                $time_money = $returnExchange_map_2[$crt_time] ?? 0;
                unset($returnExchange_map_2[$crt_time]);
            }
            //钱
            if(isset($purchase_map1[$crt_time])){
                $time_total = bcadd($purchase_map1[$crt_time]['total'],$money,2);
                $time_total = bcsub($time_total,$time_money,2);
                $purchase_map1[$crt_time]['total'] = $time_total;
            }

            $return_number = $return_total = 0;
            $return_category_number = $return_category_total = 0;
            if(isset($returnExchange_map[$value['product_id']])){
                $tmp = $returnExchange_map[$value['product_id']];
                $return_number = bcsub($value['number'], $tmp['number'],2);
                $return_total = bcsub($money, $tmp['total'],2);
                $return_category_number = $return_number;
                $return_category_total = $return_total;
            }

            //-------根据产品
            //数量
            if(isset($purchase_map[$value['product_id']])){
                $tmp_number = bcadd($purchase_map[$value['product_id']]['number'], $value['number'],2);
                $tmp_money = bcadd($purchase_map[$value['product_id']]['total'], $money,2);
                $purchase_map[$value['product_id']]['number'] = $tmp_number;
                $purchase_map[$value['product_id']]['total'] = $tmp_money;
            }else{
                //减去退换货
                $number = bcsub($value['number'], $return_number,2);
                $total = bcsub($money, $return_total,2);
                $purchase_map[$value['product_id']] = [
                    'title' => $product_tmp['title'] . "(" . $product_tmp['code'] .")",
                    'number' => $number,
                    'total' => $total
                ];
            }
            //-------根据产品

            //-------根据产品大类
            //数量
            $category_tmp = json_decode($product_tmp['product_category']);
            $category_tmp = min($category_tmp);
            //退换货
            $number_2 = bcsub($value['number'], $return_category_number,2);
            $total_2 = bcsub($money, $return_category_total,2);
            if(isset($purchase_category_map[$category_tmp])){
                $tmp_number = bcadd($purchase_map[$value['product_id']]['number'], $value['number'],2);
                $tmp_number = bcsub($tmp_number,$number_2,2);
                $tmp_money = bcadd($purchase_map[$value['product_id']]['total'], $money,2);
                $tmp_money = bcsub($tmp_money,$total_2,2);
                $purchase_category_map[$category_tmp]['number'] = $tmp_number;
                $purchase_category_map[$category_tmp]['total'] = $tmp_money;
            }else{
                $num = bcsub($value['number'],$number_2,2);
                $tol = bcsub($money,$total_2,2);
                $purchase_category_map[$category_tmp] = [
                    'number' => $num,
                    'title' => $category_return[$category_tmp] ?? "",
                    'total' => $tol
                ];
            }
        }

        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,$sale_order_id){
        $returnExchange_map = $returnExchange_map_2 = [];
        $returnExchange = ReturnExchangeOrder::where('del_time',0)
            ->where('type',ReturnExchangeOrder::Order_type)
            ->whereIn('data_id',$sale_order_id)
            ->select('id')
            ->get()->toArray();
        $returnExchange_product = ReturnExchangeOrderProductInfo::where("del_time",0)
            ->whereIn('return_exchange_id',array_column($returnExchange,'id'))
            ->select('return_exchange_id','product_id','number','return_exchange_price as price','crt_time')
            ->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
                ];
            }

            $crt_time = date("Y-m-d",$value['crt_time']);
            if(isset($returnExchange_map_2[$crt_time])){
                $tmp_money_2 = bcadd($returnExchange_map_2[$crt_time], $money,2);
                $returnExchange_map_2[$crt_time] = $tmp_money_2;
            }else{
                $returnExchange_map_2[$crt_time] = $money;
            }
        }

        return [$returnExchange_map,$returnExchange_map_2];
    }

    public function statisticsJc($data,$user){
        $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 = []){
        //用户提交的上月结存
        $lastData = $this->getLastMonthJc($product,$user,$search);

        $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','top_depart_id')
            ->get()->toArray();
        $map = [];
        foreach ($list as $val){
            $map[$val['product_id'] . $val['top_depart_id']] = $val;
        }

        //先结存表
        $in = $out = [];
        foreach ($lastData as $value){
            $key = $value['product_id'] . '|' . $value['top_depart_id'];
            if($value['number'] >= 0){
                if(isset($in[$key])){
                    $number = bcadd($in[$key]['number'], $value['number'],0);
                    $total = bcadd($in[$key]['total'], $value['total'],2);
                    $in[$key]['number'] = $number;
                    $in[$key]['total'] = $total;
                }else{
                    $in[$key] = [
                        'number' => $value['number'],
                        'total' => $value['total'],
                    ];
                }
            }else{
                $number = abs($value['number']);
                $total = abs($value['total']);
                if(isset($out[$key])){
                    $number = bcadd($out[$key]['number'], $number,0);
                    $total = bcadd($out[$key]['total'], $total,2);
                    $out[$key]['number'] = $number;
                    $out[$key]['total'] = $total;
                }else{
                    $out[$key] = [
                        'number' => $number,
                        'total' => $total,
                    ];
                }
            }
        }
        //后库存流水
        foreach ($list as $value){
           $key = $value['product_id'] . '|' . $value['top_depart_id'];

            if($value['number'] >= 0){
                if(isset($in[$key])) continue;
                $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{
                if(isset($out[$key])) continue;
                $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,
                    ];
                }
            }
        }
        //两个数组组合过滤
        $new_in = $new_out = [];
        foreach ($in as $key => $value){
            $tmp = explode('|', $key);
            $product_id = $tmp[0];
            if(isset($new_in[$product_id])){
                $number = bcadd($new_in[$product_id]['number'], $value['number'],0);
                $total = bcadd($new_in[$product_id]['total'], $value['total'],2);
                $new_in[$product_id] = [
                    'number' => $number,
                    'total' => $total,
                ];
            }else{
                $new_in[$product_id] = [
                    'number' => $value['number'],
                    'total' => $value['total'],
                ];
            }
        }
        foreach ($out as $key => $value){
            $tmp = explode('|', $key);
            $product_id = $tmp[0];
            if(isset($new_out[$product_id])){
                $number = bcadd($new_out[$product_id]['number'], $value['number'],0);
                $total = bcadd($new_out[$product_id]['total'], $value['total'],2);
                $new_out[$product_id] = [
                    'number' => $number,
                    'total' => $total,
                ];
            }else{
                $new_out[$product_id] = [
                    'number' => $value['number'],
                    'total' => $value['total'],
                ];
            }
        }

        return [$new_in, $new_out];
    }

    public function getLastMonthJc($product = [], $user = [], $search = []){
        // 如果存在上月结存数据则使用这个
        $top_depart_id = 0;
        if(empty($search['top_depart_id'])) $top_depart_id = $search['top_depart_id'];

        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $result = LastJc::where('del_time',0)
            ->whereIn('product_id',$product)
            ->where('time','<',$startStamp)
            ->when(! empty($top_depart_id), function ($query) use ($top_depart_id) {
                return $query->where('top_depart_id',$top_depart_id);
            })
            ->select('product_id','top_depart_id','total','number','price')
            ->orderBy('time','desc')
            ->get()->toArray();
        $return = [];
        $tmp = [];
        foreach ($result as $value){
            $key = $value['product_id'] . $value['top_depart_id'];
            if(in_array($key, $tmp)) continue;

            $return[] = $value;
            $tmp[] = $key;
        }

        return $return;
    }

    //本月入库(单据)暂时没用
    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([],'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, ''];
    }

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

        //门店设置的指标 按照区域汇总
        $index_map = DepartIndex::where('del_time',0)
            ->pluck('param_one','top_depart_id')
            ->toArray();
        //分社所属省
        $depart_map = $province_map = [];
        $depart = Depart::where('parent_id',0)
            ->where('province','<>','')
            ->where('del_time',0)
            ->select('province','id')
            ->get()->toArray();
        foreach ($depart as $value){
            $depart_map[$value['id']] = $value['province'];
            $province_map[$value['province']][] = $value['id'];
        }

        $address_map = config('address');
        $address_map = array_column($address_map,'label','value');

        //大区
        $final_address_map = [];
        foreach ($address_map as $key => $value){
            $tmp = [
                'key' => $key,
                'title' => $value,
                'total' => 0,
                'index' => 0
            ];
            $top_depart_id = $province_map[$key] ?? [];
            if(! empty($top_depart_id)){
                foreach ($top_depart_id as $depart_id){
                    $index = $index_map[$depart_id] ?? 0;
                    $tmp['index'] = bcadd($tmp['index'], $index,2);
                }
            }
            $final_address_map[] = $tmp;
        }

        //向总社采购的钱
        $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 ($final_address_map as $key => $value){
            $purchase_tmp = $purchase_map[$value['key']] ?? 0;
            $return_tmp = $returnExchange_map[$value['key']] ?? 0;
            $final_address_map[$key]['total'] = bcsub($purchase_tmp, $return_tmp,2);
        }

        return [true, $final_address_map];
    }

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

        $total = floatval(array_sum(array_column($data,'total')));

        // 设置一个最小显示阈值，比如0.5%
        $threshold = 0.5;

        // 用于存储调整后的数据
        $adjustedData = [];
        $otherTotal = 0;
        $other = [];

        foreach ($data as $value) {
            $model_type_title = SalesOrder::$model_type_title[$value['model_type']] ?? "";
            $rate = $total ? $value['total'] / $total : 0;
            // 检查是否低于阈值
            if ($rate * 100 < $threshold) {
                // 小于阈值的部分加到"其他"中
                $otherTotal += $value['total'];
                $other[] = $model_type_title;
            } else {
                // 保留更多小数位数
                $adjustedData[] = [
                    'model_type' => $value['model_type'],
                    'total' => $value['total'],
                    'model_type_title' => $model_type_title,
                    'rate' => round($rate * 100, 2)
                ];
            }
        }

        // 如果有"其他"值，则添加到数据集中
        if ($otherTotal > 0) {
            $other_title = implode(',',$other);
            $adjustedData[] = [
                'model_type' => -1, // 自定义一个类型标识
                'total' => $otherTotal,
                'model_type_title' => '其他(' . $other_title . ')',
                'rate' => round($otherTotal / $total * 100, 2)
            ];
        }


        return $adjustedData;
    }
}