youfeng/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\EmployeeIndex;
use App\Model\FreightFee;
use App\Model\GiveOut;
use App\Model\RevenueCost;
use App\Model\RevenueCostTotal;
use App\Model\SalaryEmployee;
use Illuminate\Support\Facades\DB;

class StatisticsService extends Service
{
    public function statisticsRevenueCostCommon($data,$user, $field = []){
        if(empty($field)) $field = RevenueCostTotal::$field;
        $model = RevenueCostTotal::Clear($user,$data);

        $model = $model->where('del_time',0)
            ->select($field)
            ->orderby('id', 'desc');

        if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');
        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
            list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
            if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
            $model->where('order_time', '>=', $start_time)
                ->where('order_time', '<', $end_time);
        }
        if(! empty($data['order_type'])) $model->where('order_type',$data['order_type']);

        return [true, $model];
    }

    public function statisticsRevenueCost($data,$user){
        list($status, $model) = $this->statisticsRevenueCostCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsRevenueCostFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);
        return [true, $list];
    }

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

        foreach ($data['data'] as $key => $value){
            $time = date("Y-m", $value['order_time']);
            $data['data'][$key]['order_time'] = $time;
            $data['data'][$key]['order_type_title'] = RevenueCost::$order_type[$value['order_type']] ?? "";
            $data['data'][$key]['profit_rate'] = bcmul($value['profit_rate'], 100,2);
        }

        return $data;
    }

    public function statisticsRevenueCostOneAndTwoCommon($data,$user, $field = []){
        if(empty($data['order_type']) || ! isset(RevenueCost::$order_type[$data['order_type']])) return [false, '单据类型不存在或错误'];

        if(empty($field)) {
            if($data['order_type'] == 1){
                $field = RevenueCost::$field_xhd;
            }else{
                $field = RevenueCost::$field_xsfp;
            }
        }
        $model = RevenueCost::Clear($user,$data);

        $model = $model->where('del_time',0)
            ->where('order_type',$data['order_type'])
            ->select($field)
            ->orderby('id', 'desc');
        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
            list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
            if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
            $model->where('order_time', '>=', $start_time)
                ->where('order_time', '<=', $end_time);
        }
        if(! empty($data['order_number'])) $model->where('order_number', 'LIKE', '%'.$data['order_number'].'%');
        if(! empty($data['customer_title'])) $model->where('customer_title', 'LIKE', '%'.$data['customer_title'].'%');
        if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');

        return [true, $model];
    }

    public function statisticsRevenueCostOneAndTwo($data,$user){
        list($status, $model) = $this->statisticsRevenueCostOneAndTwoCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsRevenueCostOneAndTwoFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

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

        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['profit_rate'] = bcmul($value['profit_rate'], 100,2);
            $data['data'][$key]['order_time'] = $value['order_time'] ? date("Y-m-d", $value['order_time']) : "";
        }

        return $data;
    }

    public function statisticsRevenueCostThreeCommon($data,$user, $field = []){
        if(empty($field)) {
            $field = RevenueCost::$field_hkd_salary_main;
            $field[] = DB::raw('sum(payment_amount) as payment_amount');
            $field[] = DB::raw('sum(price_4_total) as price_4_total');
            $field[] = DB::raw('sum(profit) as profit');
            $field[] = DB::raw('max(employee_id_2) as employee_id_2');
            $field[] = DB::raw("MAX(COALESCE(employee_id_2_title, '')) as employee_id_2_title");
        }
        $model = RevenueCost::Clear($user,$data);

        $model = $model->where('del_time',0)
            ->where('order_type',RevenueCost::ORDER_THREE)
            ->select($field)
            ->groupby('order_id')
            ->orderby('id', 'desc');
        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
            list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
            if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
            $model->where('order_time', '>=', $start_time)
                ->where('order_time', '<=', $end_time);
        }
        if(! empty($data['order_number'])) $model->where('order_number', 'LIKE', '%'.$data['order_number'].'%');
        if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');

        return [true, $model];
    }

    public function statisticsRevenueCostThree($data,$user){
        list($status, $model) = $this->statisticsRevenueCostThreeCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsRevenueCostThreeFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

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

        foreach ($data['data'] as $key => $value){
            $profit_rate = $value['payment_amount'] > 0 ? bcdiv($value['profit'], $value['payment_amount'],2) : 0;
            $data['data'][$key]['profit_rate'] = bcmul($profit_rate, 100,2);
            $data['data'][$key]['order_time'] = $value['order_time'] ? date("Y-m-d", $value['order_time']) : "";
        }

        return $data;
    }

    public function statisticsRevenueCostThreeDetail($data,$user){
        if(empty($data['order_number'])) return [false, '回款单号不能为空'];
        $result = RevenueCost::where('del_time',0)
            ->where('order_type',RevenueCost::ORDER_THREE)
            ->where('order_number',$data['order_number'])
            ->select(RevenueCost::$field_hkd_detail)
            ->get()->toArray();
        if(empty($result)) return [false, "回款单不存在或已被删除"];

        $detail = [];
        foreach ($result as $value){
            $detail[] = [
                'order_number' => $value['order_number_upstream'],
                'customer_code' => $value['customer_code'],
                'customer_title' => $value['customer_title'],
                'product_code' => $value['product_code'],
                'product_title' => $value['product_title'],
                'product_size' => $value['product_size'],
                'unit' => $value['unit'],
                'quantity' => $value['quantity'],
                'payment_amount' => $value['payment_amount'],
                'price_1' => $value['price_1'],
                'price_1_total' => $value['price_1_total'],
                'price_4' => $value['price_4'],
                'price_4_total' => $value['price_4_total'],
                'profit' => $value['profit'],
                'profit_rate' => bcmul($value['profit_rate'],100,2) . '%',
                'customer_profit_rate' => bcmul($value['customer_profit_rate'],100,2) . '%',
                'voucher_type_title' => RevenueCost::$voucher_type[$value['voucher_type']] ?? "",
            ];
        }

        $first = $result[0];
        $order = [
            'order_number' => $data['order_number'],
            'order_time' => date("Y-m-d",$first['order_time']),
            'employee_id_1_title' => $first['employee_id_1_title'],
            'detail' => $detail
        ];

        return [true, $order];
    }

    public function statisticsProfitCommon($data,$user, $field = []){
        if(empty($field)) {
            $field = RevenueCost::$field_hkd_profit_main;
            $field[] = DB::raw('COALESCE(SUM(rc.payment_amount), 0) AS payment_amount');
        }

        $type = RevenueCost::ORDER_THREE;
        $model = EmployeeIndex::Clear($user, $data)
            ->where('employee_index.del_time', 0)
            ->where('employee_index.type', EmployeeIndex::TYPE_THREE)
            ->leftJoin(DB::raw('revenue_cost as rc'), function ($join) use ($type) {
                $join->on('rc.employee_id_2', '=', 'employee_index.employee_id')
                    ->where('rc.del_time', 0)
                    ->where('rc.order_type', $type)
                    ->whereRaw('rc.order_time >= employee_index.start_time')
                    ->whereRaw('rc.order_time <= employee_index.end_time');
            })
            ->select($field)
            ->groupBy('employee_index.employee_id', 'employee_index.start_time', 'employee_index.end_time')
            ->orderBy('employee_index.end_time', 'desc');

//        $type = RevenueCost::ORDER_THREE;
//        $model = EmployeeIndex::where('employee_index.del_time',0)
//            ->where('employee_index.type',EmployeeIndex::TYPE_THREE)
//            ->leftJoin(DB::raw('revenue_cost as rc'), function ($join) use ($type) {
//                $join->on('rc.employee_id_2', '=', 'employee_index.employee_id')
//                    ->where('rc.del_time', 0)
//                    ->where('rc.order_type', $type)
//                    ->whereRaw('rc.order_time >= employee_index.start_time')
//                    ->whereRaw('rc.order_time <= employee_index.end_time');
//            })
//            ->select($field)
//            ->groupBy('employee_index.employee_id', 'employee_index.start_time', 'employee_index.end_time')
//            ->orderBy('employee_index.end_time','desc');

        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){;
            list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
            if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
            $model->where('employee_index.start_time', '<=', $end_time)
                ->where('employee_index.end_time', '>=', $start_time);
        }
        if(! empty($data['employee_id_2_title'])) $model->where('rc.employee_id_2_title', 'LIKE', '%'.$data['employee_id_2_title'].'%');

        return [true, $model];
    }

    public function statisticsProfit($data,$user){
        list($status, $model) = $this->statisticsProfitCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsProfitFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

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

        //获取应发放金额需要的指标
        $indexes = $this->getEmployeeIndex($data['data']);
        //已发
        $indexes_give = $this->getEmployeeGiveOut($data['data']);

        foreach ($data['data'] as $key => $value){
            $start = $value['start_time'] ? date('Y-m-d',$value['start_time']) : '';
            $end = $value['end_time'] ? date('Y-m-d',$value['end_time']) : '';
            $string = "";
            if(! empty($start) && ! empty($end)) $string = $start . "-" . $end;
            $value['check_time'] = $string;
            $value['index_' . EmployeeIndex::TYPE_ONE] = 0;
            $value['index_' . EmployeeIndex::TYPE_FIVE] = 0;
            //指标
            $this->makeIndex($indexes, $value, $data['data'][$key]);
            //应付
            $this->makeShouldPay($value,$data['data'][$key]);
            //已付
            $this->makePayed($indexes_give, $value,$data['data'][$key]);
            //未付
            $not_pay = bcsub($data['data'][$key]['should_pay'], $data['data'][$key]['payed'],2);
            $data['data'][$key]['not_pay'] = $not_pay;
        }

        return $data;
    }

    private function getEmployeeIndex($existingData)
    {
        if (empty($existingData)) {
            return collect();
        }

        // 取出所有涉及的 employee_id 和时间区间
        $employeeIds = array_column($existingData, 'employee_id_2');
        $start_time = array_column($existingData, 'start_time');
        $minStart = ! empty($start_time) ? min($start_time) : 0;
        $end_time = array_column($existingData, 'end_time');
        $maxEnd   = ! empty($end_time) ? max($end_time) : 0;

        // 一次性查出这些员工在最大区间范围内的所有指标
        $results = EmployeeIndex::where('del_time', 0)
            ->whereIn('type', [EmployeeIndex::TYPE_ONE, EmployeeIndex::TYPE_FIVE])
            ->whereIn('employee_id', $employeeIds)
            ->where('start_time', '<=', $maxEnd)
            ->where('end_time', '>=', $minStart)
            ->select('start_time','end_time','employee_id','index','type')
            ->get();

        return $results;
    }

    private function getEmployeeGiveOut($existingData)
    {
        if (empty($existingData)) {
            return collect();
        }

        // 取出所有涉及的 employee_id 和时间区间
        $employeeIds = array_column($existingData, 'employee_id_2');

        $start_time = array_column($existingData, 'start_time');
        $minStart = ! empty($start_time) ? min($start_time) : 0;
        $end_time = array_column($existingData, 'end_time');
        $maxEnd   = ! empty($end_time) ? max($end_time) : 0;

        // 一次性查出这些员工在最大区间范围内的所有指标
        $results = GiveOut::where('del_time', 0)
            ->whereIn('employee_id_1', $employeeIds)
            ->where('start_time', '<=', $maxEnd)
            ->where('end_time', '>=', $minStart)
            ->select('start_time','end_time','employee_id_1 as employee_id','give_out_amount')
            ->get();

        return $results;
    }

    private function makeIndex($indexes, $value, &$data){
        // 找到所有符合条件的 index（可能多个 type）
        $matchedIndexes = $indexes->filter(function ($item) use ($value) {
            return $item['employee_id'] == $value['employee_id_2']
                && $item['start_time'] <= $value['end_time']
                && $item['end_time'] >= $value['start_time'];
        });
        // 按 type 去重，只保留第一次出现的
        $uniqueByType = [];
        foreach ($matchedIndexes as $item) {
            $index = "index_" . $item['type'];
            if (! isset($uniqueByType[$index])) {
                $uniqueByType[$index] = $item['index'];
            }
        }
        $data = array_merge($value, $uniqueByType);
    }

    private function makeShouldPay($value, &$data){
        $index_1 = bcdiv($data['index_1'],100,2);
        $index_5 = bcdiv($data['index_5'],100,2);
        // 分红比例/2
        $a = bcdiv($index_5,2,2);
        // 季度预支分红比例/2
        $b = bcdiv($index_1,2,2);
        //（分红比例/2-季度预支分红比例/2）
        $c = bcsub($a, $b,2);
        $data['part_left'] = $c;
        if($value['payment_amount'] < $value['index']){
            //回款金额*（分红比例/2-季度预支分红比例/2）；
            $shouldPay = bcmul($value['payment_amount'], $c,2);
        }else{
            //回款金额-季度指标金额
            $d = bcsub($value['payment_amount'], $value['index'],2);
            //（回款金额-季度指标金额）*15%
            $e = bcmul($d, 0.15,2);
            //（回款金额-季度指标金额）*15%/2
            $e = bcdiv($e,2,2);
            //（分红比例/2-季度预支分红比例/2+（回款金额-季度指标金额）*15%/2）
            $rate = bcadd($c, $e,2);
//                $data['data'][$key]['rate'] = $rate;
            //回款金额*（分红比例/2-回款金额*季度预支分红比例/2+（回款金额-季度指标金额）*15%/2）
            $shouldPay = bcmul($value['payment_amount'], $rate,2);
        }
        $data['should_pay'] = $shouldPay;
    }

    private function makePayed($indexes, $value, &$data){
        $matchedIndexes_give = $indexes->filter(function ($item) use ($value) {
            return $item['employee_id'] == $value['employee_id_2']
                && $item['start_time'] <= $value['end_time']
                && $item['end_time'] >= $value['start_time'];
        });
        $give_out = 0;
        foreach ($matchedIndexes_give as $item) {
            $give_out = bcadd($item['give_out_amount'], $give_out,2);
        }
        $data['payed'] = $give_out;
    }

    public function statisticsEmployeeSalaryCommon($data,$user, $field = []){
        if(empty($field)) {
            $field = SalaryEmployee::$field;
        }
        $model = SalaryEmployee::Clear($user,$data);

        $model = $model->where('del_time',0)
            ->where('order_type',RevenueCost::ORDER_THREE)
            ->select($field)
            ->orderby('order_time', 'desc');
        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
            $start_time = strtotime($data['order_time'][0] . "-01");
            $end_time = strtotime($data['order_time'][1] . "-01");
            $model->where('order_time', '>=', $start_time)
                ->where('order_time', '<=', $end_time);
        }
        if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');

        return [true, $model];
    }

    public function statisticsEmployeeSalary($data,$user){
        list($status, $model) = $this->statisticsEmployeeSalaryCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsEmployeeSalaryFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

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

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

        return $data;
    }

    public function statisticsFreightFeeCommon($data,$user, $field = []){
        if(empty($field)) {
            $field = FreightFee::$field;
            $field[] = DB::raw('GROUP_CONCAT(id) as result');
        }
        $model = FreightFee::Clear($user,$data);

        $model = $model->where('del_time',0)
            ->select($field)
            ->groupby('order_time')
            ->orderby('order_time', 'desc');
        if(! empty($data['order_time'][0]) && ! empty($data['order_time'][1])){
            list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
            if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "单据日期的区间无效"];
            $model->where('order_time', '>=', $start_time)
                ->where('order_time', '<=', $end_time);
        }
        if(! empty($data['employee_id_1_title'])) $model->where('employee_id_1_title', 'LIKE', '%'.$data['employee_id_1_title'].'%');

        return [true, $model];
    }

    public function statisticsFreightFee($data,$user){
        list($status, $model) = $this->statisticsFreightFeeCommon($data, $user);
        if(! $status) return [false, $model];
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsFreightFeeFillData($list);

        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

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

        $id = implode(',', array_column($data['data'], 'result'));
        $idArr = explode(',', $id);
        $allFees = FreightFee::where('del_time', 0)
            ->whereIn('id', $idArr)
            ->get()
            ->toArray();
        $feesByDay = [];
        foreach ($allFees as $f) {
            $feesByDay[$f['order_time']][] = $f;
        }

        foreach ($data['data'] as $key => $value) {
            $fees = $feesByDay[$value['order_time']] ?? [];
            if (empty($fees)) {
                $data['data'][$key]['total_amount'] = 0;
                continue;
            }

            // 准备 time_amount（和详情逻辑一致）
            $time_amount = [];
            foreach ($fees as $f) {
                if ($f['business_type_id'] == FreightFee::businessTypeNormal) {
                    $k = $f['order_time'] . $f['area_hs'];
                    $time_amount[$k]['amount'] = isset($time_amount[$k]['amount'])
                        ? bcadd($f['freight_amount'], $time_amount[$k]['amount'], 2)
                        : $f['freight_amount'];
                    $time_amount[$k]['is_use'] = $time_amount[$k]['is_use'] ?? 0;
                }
            }

            // 逐条算金额
            $valueTotal = 0;
            foreach ($fees as $f) {
                $f = $this->calculateFreightFeeRow($f, $time_amount);
                $valueTotal = bcadd($valueTotal, $f['total_amount'], 2);
            }

            $data['data'][$key]['total_amount'] = $valueTotal;

            $order_time = $value['order_time'] ? date('Y-m-d',$value['order_time']) : '';
            $data['data'][$key]['order_time_show'] = $order_time;
            unset($data['data'][$key]['result']);
        }

        return $data;
    }

//    public function statisticsFreightFeeDetail($data, $user){
//        if(empty($data['order_time'])) return [false, '单据日期不能为空'];
//
//        $fee = FreightFee::where('del_time',0)
//            ->where('order_time', $data['order_time'])
//            ->get()->toArray();
//        if(empty($fee)) return [false, '单据日期下暂无数据'];
//
//        //同一天 同一个地区 非退货的配送费总金额
//        $time_amount = [];
//        foreach ($fee as $value){
//            if($value['business_type_id'] == FreightFee::businessTypeNormal){
//                $key = $value['order_time'] . $value['area_hs'];
//                if(isset($time_amount[$key])){
//                    $freight_amount = bcadd($value['freight_amount'], $time_amount[$key]['amount'],2);
//                    $time_amount[$key]['amount'] = $freight_amount;
//                }else{
//                    $time_amount[$key] = [
//                        'is_use' => 0,
//                        'amount' => $value['freight_amount'],
//                    ];
//                }
//            }
//        }
//
//        $return = [];
//        foreach ($fee as $value){
//            //结算金额
//            $js_amount = 0;
//            $key = $value['order_time'] . $value['area_hs'];
//            if($value['business_type_id'] == FreightFee::businessTypeNormal){
//                $tmp_total_arr = $time_amount[$key];
//                if($tmp_total_arr['amount'] < $value['min_freight_amount']){
//                    if(! $time_amount[$key]['is_use']){
//                        $js_amount = $value['min_freight_amount'];
//                        $time_amount[$key]['is_use'] = 1;
//                    }
//                }else{
//                    $js_amount = $value['js_single_amount'];
//                }
//                $value['js_amount'] = $js_amount;
//                //加急费
//                $jj_fee = 0;
//                if($value['delivery_mode'] == FreightFee::deliveryModeRightNow) $jj_fee = bcmul($js_amount, 0.5,2);
//                $value['jj_fee'] = $jj_fee;
//                //总金额
//                $total_amount = bcadd($js_amount,$value['customer_store_zx_fee'],2);
//                $total_amount = bcadd($total_amount,$value['sl_fee'],2);
//                $total_amount = bcadd($total_amount,$jj_fee,2);
//                $total_amount = bcadd($total_amount,$value['dh_fee'],2);
//                $value['total_amount'] = $total_amount;
//                $return[FreightFee::businessTypeNormal][$key][] = $value;
//            }else{
//                $js_amount = $value['js_single_amount'];
//                $value['js_amount'] = $js_amount;
//                $jj_fee = 0;
//                if($value['delivery_mode'] == FreightFee::deliveryModeRightNow) $jj_fee = bcmul($js_amount, 0.5,2);
//                //加急费
//                $value['jj_fee'] = $jj_fee;
//                //总金额
//                $total_amount = bcadd($js_amount,$value['customer_store_zx_fee'],2);
//                $total_amount = bcadd($total_amount,$value['sl_fee'],2);
//                $total_amount = bcadd($total_amount,$jj_fee,2);
//                $total_amount = bcadd($total_amount,$value['dh_fee'],2);
//                $value['total_amount'] = $total_amount;
//                $return[FreightFee::businessTypeReturn][$key][] = $value;
//            }
//        }
//
//        $result = [];
//        foreach ($return as $value){
//            foreach (array_values($value) as $val){
//                $result[] = $val;
//            }
//        }
//        dd($result);
//    }

    public function statisticsFreightFeeDetail($data, $user)
    {
        if (empty($data['order_time'])) {
            return [false, '单据日期不能为空'];
        }

        $model = FreightFee::Clear($user,$data);
        $fee = $model->where('del_time', 0)
            ->where('order_time', $data['order_time'])
            ->get()
            ->toArray();

        if (empty($fee)) {
            return [false, '单据日期下暂无数据'];
        }

        // 按时间+地区聚合同一天、同地区的非退货配送费
        $time_amount = [];
        foreach ($fee as $value) {
            if ($value['business_type_id'] == FreightFee::businessTypeNormal) {
                $key = $value['order_time'] . $value['area_hs'];
                $time_amount[$key]['amount'] = isset($time_amount[$key]['amount'])
                    ? bcadd($value['freight_amount'], $time_amount[$key]['amount'], 2)
                    : $value['freight_amount'];
                $time_amount[$key]['is_use'] = $time_amount[$key]['is_use'] ?? 0;
            }
        }

        $return = [];
        foreach ($fee as $value) {
            $value = $this->calculateFreightFeeRow($value, $time_amount);
            $key = $value['order_time'] . $value['area_hs'];
            $return[$value['business_type_id']][$key][] = $value;
        }

        $result = [];
        // 需要合计的字段
        $sumFields = ['xs', 'weight', 'freight_amount', 'js_amount', 'customer_store_zx_fee', 'jj_fee', 'dh_fee', 'total_amount'];

        foreach ($return as $group) {
            foreach ($group as $val) {
                // 计算合计
                $sumRow = [];
                foreach ($val as $row) {
                    foreach ($sumFields as $field) {
                        $sumRow[$field] = isset($sumRow[$field])
                            ? bcadd($sumRow[$field], $row[$field], 2)
                            : $row[$field];
                    }
                }

                // 末行合计行（非合计字段设为空字符串）
                $footer = [];
                foreach ($val[0] as $field => $v) {
                    $footer[$field] = in_array($field, $sumFields) ? $sumRow[$field] : '';
                }

                $val[] = $footer;
                $result[] = $val;
            }
        }

        return [true, $result];
    }

    protected function calculateFreightFeeRow(array $value, array &$time_amount)
    {
        $key = $value['order_time'] . $value['area_hs'];
        $js_amount = 0;

        if ($value['business_type_id'] == FreightFee::businessTypeNormal) {
            $tmp_total_arr = $time_amount[$key];

            // 判断是否使用最低运费
            if ($tmp_total_arr['amount'] < $value['min_freight_amount']) {
                if (!$time_amount[$key]['is_use']) {
                    $js_amount = $value['min_freight_amount'];
                    $time_amount[$key]['is_use'] = 1;
                }
            } else {
                $js_amount = $value['js_single_amount'];
            }
        } else {
            $js_amount = $value['js_single_amount'];
        }

        $value['js_amount'] = $js_amount;

        // 加急费
        $jj_fee = 0;
        if ($value['delivery_mode'] == FreightFee::deliveryModeRightNow) {
            $jj_fee = bcmul($js_amount, 0.5, 2);
        }
        $value['jj_fee'] = $jj_fee;

        // 总金额
        $total_amount = bcadd($js_amount, $value['customer_store_zx_fee'], 2);
        $total_amount = bcadd($total_amount, $value['sl_fee'], 2);
        $total_amount = bcadd($total_amount, $jj_fee, 2);
        $total_amount = bcadd($total_amount, $value['dh_fee'], 2);
        $value['total_amount'] = $total_amount;

        return $value;
    }
}