youfeng/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\EmployeeIndex;
use App\Model\FreightFee;
use App\Model\GiveOut;
use App\Model\ItemReport;
use App\Model\ItemReportMan;
use App\Model\ItemReportRoad;
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']);
        if(! empty($data['id'])) $model->whereIn('id',$data['id']);

        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'].'%');
        if(! empty($data['id'])) $model->whereIn('id',$data['id']);

        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'].'%');
        if(! empty($data['employee_id_1'])) $model->where('employee_id_1', 'LIKE', $data['employee_id_1']);
        if(! empty($data['employee_id_2'])) $model->where('employee_id_2', 'LIKE', $data['employee_id_2']);
        if(! empty($data['employee_id_2_title'])) $model->where('employee_id_2_title', 'LIKE', '%'.$data['employee_id_2_title'].'%');
        if(! empty($data['channel_finance_array'])) {
            $array = explode("\n", str_replace(["\r\n", "\r"], "\n", $data['channel_finance_array']));
            $model->whereIn('channel_finance', $array);
        }
        if(! empty($data['id'])) $model->whereIn('order_id',$data['id']);

        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'],8) : 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'],
                'channel_finance' => $value['channel_finance'],
                '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']] ?? "",
            ];
        }
        $footer = $this->countTotal($result, $user['header_default']);
        $footer['profit_rate'] = $footer['profit_rate'] . "%";
        $footer = array_merge( [
            'order_number'        => '',
            'customer_code'       => '',
            'customer_title'      => '',
            'channel_finance'     => '',
            'product_code'        => '',
            'product_title'       => '',
            'product_size'        => '',
            'unit'                => '',
            'quantity'            => '',
            'payment_amount'      => '',
            'price_1'             => '',
            'price_1_total'       => '',
            'price_4'             => '',
            'price_4_total'       => '',
            'profit'              => '',
            'profit_rate'         => '',
            'customer_profit_rate'=> '',
            'voucher_type_title'  => '',
        ], $footer);
        $detail[] = $footer;

        $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)
                    ->whereNotNull('rc.employee_id_2')
                    ->where('rc.employee_id_2', '<>', '')
                    ->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'].'%');
        if(! empty($data['id'])) $model->whereIn('employee_index.id', $data['id']);

        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'].'%');
        if(! empty($data['id'])) $model->whereIn('id', $data['id']);

        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;
        }
        $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'].'%');
        if(! empty($data['id'])) $model->whereIn('order_time', $data['id']);

        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, $user,$data);

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

        return [true, $list];
    }

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

        $order_time = array_column($data['data'], 'order_time');
        $model = FreightFee::Clear($user,$ergs);
        $allFees = $model->where('del_time', 0)
            ->whereIn('order_time', $order_time)
            ->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'], 4)
                        : $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'], 4);
            }

            $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;
            $data['data'][$key]['id'] = $value['order_time'];
        }

        return $data;
    }

    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'], 4)
                    : $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'];
        $sumFields_map = ['weight' => 4,'freight_amount' => 4, 'js_amount' => 4,'customer_store_zx_fee' => 4, 'jj_fee' => 4, 'dh_fee' => 4, 'total_amount' => 4];

        // 业务类型 日期 地区 算一张表算运费
        foreach ($return as $g => $group) {
            $g_title = FreightFee::$business[$g] ?? "";
            foreach ($group as $key => $val) {
                $tmp_key = explode('|', $key);
                $title = $tmp_key[1] . "(" . $g_title . ")";
                // 计算合计
                $sumRow = [];
                foreach ($val as $key => $row) {
                    $order_time = $row['order_time'] ? date('Y-m-d',$row['order_time']) : '';
                    $val[$key]['order_time_show'] = $order_time;
                    $val[$key]['is_present_show'] = $row['is_present'] ? '是' : '否';
                    $delivery_mode_show = FreightFee::$delivery[$row['delivery_mode']] ?? "";
                    $val[$key]['delivery_mode_show'] = $delivery_mode_show;
                    $val[$key]['area_range_show'] = $row['area_range'] == 1 ? '1~5吨' : '5吨以上';
                    foreach ($sumFields as $field) {
                        $decimals = $sumFields_map[$field] ?? 2;
                        $sumRow[$field] = isset($sumRow[$field])
                            ? bcadd($sumRow[$field], $row[$field], $decimals)
                            : $row[$field];
                    }
                }

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

                $val[] = $footer;

                $result[] = [
                    'key' => $title,
                    'list' => $val
                ];
            }
        }

        return [true, $result];
    }

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

        $model = FreightFee::Clear($user, $data);
        $orderTimes = is_array($data['order_time']) ? $data['order_time'] : [$data['order_time']];

        $fee = $model->where('del_time', 0)
            ->whereIn('order_time', $orderTimes)
            ->get()
            ->toArray();

        // 表头定义（所有 sheet 通用）
        $header = [
            "仓库", "销货单备注", "业务类型", "销货单号", "销货日期", "客户",
            "客户门店装车费单价", "业务员", "送货地址", "地区华商", "存货编码", "存货名称", "箱规",
            "华商重量", "门店卸货费单价(华商)", "门店卸货费单价(华商)", "赠品", "货类(华商)", "运输方式",
            "数量", "含税单价", "含税金额", "箱数", "总重量", "运价区间", "配送费单价", "配送费金额", "结算金额",
            "门店卸货费", "上楼费", "加急费", "到货装卸费", "总金额"
        ];

        if (empty($fee)) {
            // 返回每个请求的日期都带表头（只有表头），以便导出时每个 sheet 至少有表头
            $resultEmpty = [];
            foreach ($orderTimes as $ot) {
                $dateKey = date('Y-m-d', $ot);
                $resultEmpty[$dateKey] = [
                    0 => $header
                ];
            }
            return $resultEmpty;
        }

        // 按时间+地区聚合同一天、同地区的非退货配送费（用于 calculate 逻辑）
        $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'], 4)
                    : $value['freight_amount'];
                $time_amount[$key]['is_use'] = $time_amount[$key]['is_use'] ?? 0;
            }
        }

        // 先按 order_time -> business_type_id -> (order_time|area_hs) 分组收集数据行
        $grouped = [];
        foreach ($fee as $value) {
            $value = $this->calculateFreightFeeRow($value, $time_amount);
            $key = $value['order_time'] . '|' . $value['area_hs'];
            $grouped[$value['order_time']][$value['business_type_id']][$key][] = $value;
        }

        // 构造返回：每个日期一个 sheet（键为 YYYY-MM-DD），值为数值下标数组，0 为表头
        $result = [];

        // 需要合计的字段及小数位
        $sumFields = ['xs','weight', 'freight_amount', 'js_amount', 'customer_store_zx_fee', 'jj_fee', 'dh_fee', 'total_amount'];
        $sumFields_map = ['xs' => 0,'weight' => 4, 'freight_amount' => 4, 'js_amount' => 4, 'customer_store_zx_fee' => 4, 'jj_fee' => 4, 'dh_fee' => 4, 'total_amount' => 4];

        // 初始化每个传入日期，确保即使某个日期无数据也有表头
        foreach ($orderTimes as $ot) {
            $dateKey = date('Y-m-d', $ot);
            $result[$dateKey] = [];
            $result[$dateKey][] = $header; // index 0 为表头
        }

        // 填充数据
        foreach ($grouped as $order_time => $types) {
            $dateKey = date('Y-m-d', $order_time);
            // 若该日期未初始化（理论上不会），先初始化表头
            if (!isset($result[$dateKey])) {
                $result[$dateKey] = [];
                $result[$dateKey][] = $header;
            }

            foreach ($types as $business_type_id => $group) {
                $businessTitle = FreightFee::$business[$business_type_id] ?? '';
                foreach ($group as $key => $rows) {
                    // 每个 key 对应一组明细（按 area_hs 分组）
                    $sumRow = []; // 用于该分组的合计

                    foreach ($rows as $row) {
                        $date_show = $row['order_time'] ? date('Y-m-d', $row['order_time']) : "";
                        $is_present_show = !empty($row['is_present']) ? '是' : '否';
                        $delivery_mode_show = FreightFee::$delivery[$row['delivery_mode']] ?? "";
                        $area_range_show = isset($row['area_range']) && $row['area_range'] == 1 ? '1~5吨' : '5吨以上';

                        // 单行数据（按你指定的字段顺序）
                        $line = [
                            $row['warehouse_name'] ?? '', // 仓库
                            $row['mark'] ?? '', // 销货单备注
                            $row['business_type_title'] ?? '', // 业务类型
                            $row['order_number'] ?? '', // 销货单号
                            $date_show, // 销货日期
                            $row['customer_title'] ?? '', // 客户
                            $row['customer_store_price'] ?? 0, // 客户门店装车费单价
                            $row['employee_id_1_title'] ?? '', // 业务员
                            $row['address'] ?? '', // 送货地址
                            $row['area_hs'] ?? '', // 地区华商
                            $row['product_code'] ?? '', // 存货编码
                            $row['product_title'] ?? '', // 存货名称
                            $row['product_box_size'] ?? '', // 箱规
                            $row['product_weight'] ?? 0, // 华商重量
                            $row['product_store_price'] ?? 0, // 门店卸货费单价(华商)
                            $row['product_store_price2'] ?? 0, // 到货装卸费单价(华商)
                            $is_present_show, // 赠品
                            $row['product_category'] ?? '', // 货类(华商)
                            $delivery_mode_show, // 运输方式
                            $row['quantity'] ?? 0, // 数量
                            $row['price_3'] ?? 0, // 含税单价
                            $row['price_3_total'] ?? 0, // 含税金额
                            number_format($row['xs'] ?? 0), // 箱数
                            $row['weight'] ?? 0, // 总重量
                            $area_range_show, // 运价区间
                            $row['freight_unit_price'] ?? 0, // 配送费单价
                            $row['freight_amount'] ?? 0, // 配送费金额
                            $row['js_amount'] ?? 0, // 结算金额
                            $row['customer_store_zx_fee'] ?? 0, // 门店卸货费
                            $row['sl_fee'] ?? 0, // 上楼费
                            number_format($row['jj_fee'] ?? 0, 2), // 加急费
                            $row['dh_fee'] ?? 0, // 到货装卸费
                            $row['total_amount'] ?? 0, // 总金额
                        ];

                        $result[$dateKey][] = $line;

                        // 累加合计
                        foreach ($sumFields as $field) {
                            $decimals = $sumFields_map[$field] ?? 2;
                            $sumRow[$field] = isset($sumRow[$field])
                                ? bcadd($sumRow[$field], $row[$field], $decimals)
                                : ($row[$field] ?? 0);
                        }
                    }

                    // 添加本分组合计行（合计位置与表头列对齐，其它非合计字段留空）
                    $totalLine = [
                        '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                        $sumRow['xs'] ?? 0,
                        $sumRow['weight'] ?? 0,
                        '', '',
                        $sumRow['freight_amount'] ?? 0, // 配送费金额合计
                        $sumRow['js_amount'] ?? 0, // 结算金额合计
                        $sumRow['customer_store_zx_fee'] ?? 0, // 门店卸货费合计
                        $sumRow['sl_fee'] ?? 0, // 上楼费合计
                        $sumRow['jj_fee'] ?? 0, // 加急费合计
                        $sumRow['dh_fee'] ?? 0, // 到货装卸费合计
                        $sumRow['total_amount'] ?? 0, // 总金额合计
                    ];

                    $result[$dateKey][] = $totalLine;
                }
            }
        }

        // 确保每个日期的数组是从 0 开始的连续下标（虽然我们已经按顺序 push，但为了保险）

        return $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, 4);
        }
        $value['jj_fee'] = $jj_fee;

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

        return $value;
    }

    public function statisticsItem($data, $user)
    {
        $model = $this->statisticsItemCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsItemFillData($list, $user,$data);

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

        return [true, $list];
    }

    public function statisticsItemCommon($data,$user, $field = []){
        $model = ItemReport::select('*')
            ->orderby('id', 'desc');

        if(! empty($data['order_time'])){
            $time = strtotime($data['order_time'] . "-01");
            $model->where('time', $time);
        }

        return $model;
    }

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

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

        return $data;
    }

    public function statisticsRoad($data, $user)
    {
        $model = $this->statisticsRoadCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsRoadFillData($list, $user,$data);

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

        return [true, $list];
    }

    public function statisticsRoadCommon($data,$user, $field = []){
        $model = ItemReportRoad::select('*')
            ->orderby('id', 'desc');

        if(! empty($data['order_time'])){
            $time = strtotime($data['order_time'] . "-01");
            $model->where('time', $time);
        }

        return $model;
    }

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

        foreach ($data['data'] as $key => $value) {
            $time = $value['time'] ? date('Y-m',$value['time']) : '';
            $data['data'][$key]['time'] = $time;
            $data['data'][$key]['employee_title_type_title'] = ItemReportRoad::$order_type[$value['employee_title_type']] ?? '';
        }

        return $data;
    }

    public function statisticsMan($data, $user)
    {
        $model = $this->statisticsManCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->statisticsManFillData($list, $user,$data);

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

        return [true, $list];
    }

    public function statisticsManCommon($data,$user, $field = []){
        $model = ItemReportMan::select('*')
            ->orderby('id', 'desc');

        if(! empty($data['order_time'])){
            $time = strtotime($data['order_time'] . "-01");
            $model->where('time', $time);
        }

        return $model;
    }

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

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

        return $data;
    }

    public function getChannelReport($data)
    {
        if(empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        // 1. 解析输入
        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        $yearStartTs = mktime(0, 0, 0, 1, 1, $year);

        // 2. 获取所有相关数据
        $records = DB::table('item_report')
            ->whereBetween('time', [$yearStartTs, $currentMonthTs])
            ->whereNotNull('channel_details') // 可选：排除空渠道
            ->get();

        // 3. 初始化结果容器
        $channels = [];

        foreach ($records as $record) {
            $channel = $record->channel_details;

            if (!isset($channels[$channel])) {
                // 初始化当月和累计
                $channels[$channel] = [
                    'monthly' => array_fill_keys($this->getAmountFieldNames(), 0),
                    'cumulative' => array_fill_keys($this->getAmountFieldNames(), 0),
                ];
            }

            // 判断是否是当月
            $isCurrentMonth = ($record->time == $currentMonthTs);

            // 累加所有字段（包括 NULL 处理）
            foreach ($this->getAmountFieldNames() as $field) {
                $value = $record->$field ?? 0;
                $channels[$channel]['cumulative'][$field] += $value;
                if ($isCurrentMonth) {
                    $channels[$channel]['monthly'][$field] += $value;
                }
            }
        }

        // 4. 插入 amount1 和 amount2，并格式化输出
        $finalResult = [];
        foreach ($channels as $channel => $data) {
            $finalResult[] = [
                'channel' => $channel,
                'monthly' => $this->injectAmount1And2($data['monthly']),
                'cumulative' => $this->injectAmount1And2($data['cumulative']),
            ];
        }

        return $finalResult;
    }

    private function getAmountFieldNames()
    {
        return [
            'receipt_amount',
            'cost',
            'profit',
            'settle_amount',
            'gl_amount',
            'wl_amount',
            'ht_amount',
            'zk_amount',
            'cx_amount',
            'tg_amount',
            'cl_amount',
            'kq_amount',
            'zp_amount',
            'gg_amount',
            'kd_amount',
            'xsqt_amount',
            'ry_amount',
            'sb_amount',
            'sj_amount', // 注意：包含 sj_amount
        ];
    }

    private function injectAmount1And2($data)
    {
        $result = [];
        $expenseFields = [
            'settle_amount',
            'gl_amount',
            'wl_amount',
            'ht_amount',
            'zk_amount',
            'cx_amount',
            'tg_amount',
            'cl_amount',
            'kq_amount',
            'zp_amount',
            'gg_amount',
            'kd_amount',
            'xsqt_amount',
            'ry_amount',
            'sb_amount',
            'sj_amount', // 注意：sj_amount 要包含
        ];

        // 第一步：按顺序构建带 amount1/2 的数组
        foreach ($data as $key => $value) {
            if ($key === 'sj_amount') {
                $result['amount1'] = '0.00';
                $result['sj_amount'] = number_format($value, 2, '.', '');
                $result['amount2'] = '0.00';
            } else {
                $result[$key] = number_format($value, 2, '.', '');
            }
        }

        // 第二步：计算 total（仅 expenseFields 中的字段，从 $data 原始值取，避免字符串运算）
        $total = 0;
        foreach ($expenseFields as $field) {
            $val = $data[$field] ?? 0;
            $total += is_numeric($val) ? (float)$val : 0;
        }

        $result['total'] = number_format($total, 2, '.', '');

        return $result;
    }

    public function getChannelReportRoad($data)
    {
        if(empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        // 1. 解析输入
        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        $yearStartTs = mktime(0, 0, 0, 1, 1, $year);

        // 2. 获取所有相关数据
        $records = DB::table('item_report_road')
            ->whereBetween('time', [$yearStartTs, $currentMonthTs])
            ->whereNotNull('employee_title_type') //
            ->get();

        // 3. 初始化结果容器
        $channels = [];

        foreach ($records as $record) {
            $channel = $record->employee_title_type;

            if (!isset($channels[$channel])) {
                // 初始化当月和累计
                $channels[$channel] = [
                    'monthly' => array_fill_keys($this->getAmountFieldNames(), 0),
                    'cumulative' => array_fill_keys($this->getAmountFieldNames(), 0),
                ];
            }

            // 判断是否是当月
            $isCurrentMonth = ($record->time == $currentMonthTs);

            // 累加所有字段（包括 NULL 处理）
            foreach ($this->getAmountFieldNames() as $field) {
                $value = $record->$field ?? 0;
                $channels[$channel]['cumulative'][$field] += $value;
                if ($isCurrentMonth) {
                    $channels[$channel]['monthly'][$field] += $value;
                }
            }
        }

        // 4. 插入 amount1 和 amount2，并格式化输出
        $finalResult = [];
        foreach ($channels as $channel => $data) {
            $finalResult[] = [
                'channel' => $channel,
                'monthly' => $this->injectAmount1And2($data['monthly']),
                'cumulative' => $this->injectAmount1And2($data['cumulative']),
            ];
        }

        return $finalResult;
    }

    private function getAmountFieldManNames()
    {
        return [
            'receipt_amount', 'receipt_not_amount', 'cost', 'profit', 'profit_not',
            'settle_amount', 'gl_amount', 'gl_not_amount', 'wl_amount', 'wl_not_amount',
            'ht_amount', 'zk_amount', 'cx_amount', 'tg_amount', 'cl_amount',
            'kq_amount', 'zp_amount', 'gg_amount', 'kd_amount', 'xsqt_amount',
            'ry_amount', 'sb_amount', 'sj_amount', 'sj_not_amount', 'sx_amount',
            'sx_not_amount', 'other_ck_amount', 'cg_amount'
        ];
    }

    private function injectAmountMan($data)
    {
        $result = [];
        // 参与 total 计算的费用字段 (排除收入、成本和利润)
        $expenseFields = [
            'settle_amount', 'gl_amount', 'gl_not_amount', 'wl_amount', 'wl_not_amount',
            'ht_amount', 'zk_amount', 'cx_amount', 'tg_amount', 'cl_amount',
            'kq_amount', 'zp_amount', 'gg_amount', 'kd_amount', 'xsqt_amount',
            'ry_amount', 'sb_amount', 'sj_amount', 'sj_not_amount', 'sx_amount',
            'sx_not_amount', 'other_ck_amount', 'cg_amount'
        ];

        // 第一步：格式化并插入占位符
        foreach ($this->getAmountFieldManNames() as $key) {
            $val = $data[$key] ?? 0;

            if ($key === 'sj_amount') {
                $result['amount1'] = '0.00'; // 占位
                $result['sj_amount'] = number_format((float)$val, 2, '.', '');
                $result['amount2'] = '0.00'; // 占位
            } else {
                $result[$key] = number_format((float)$val, 2, '.', '');
            }
        }

        // 第二步：计算费用总额
        $total = 0;
        foreach ($expenseFields as $field) {
            $total += (float)($data[$field] ?? 0);
        }
        $result['total'] = number_format($total, 2, '.', '');

        return $result;
    }

    public function getChannelReportMan1($data)
    {
        if(empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time']; // 格式如 2026-02

        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        // 当前选择月的第一天戳
        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        // 去年7月1日的时间戳 (例如输入2026-02，则起始为2025-07-01)
        $startTs = mktime(0, 0, 0, 7, 1, $year - 1);

        // 从业务员报表获取数据
        $records = DB::table('item_report_man')
            ->whereBetween('time', [$startTs, $currentMonthTs])
            ->get();

        $channels = [];
        $allFields = $this->getAmountFieldManNames();

        foreach ($records as $record) {
            // 以渠道财务分组
            $channel = $record->channel_finance ?: '未知渠道';

            if (!isset($channels[$channel])) {
                $channels[$channel] = [
                    'monthly'    => array_fill_keys($allFields, 0),
                    'cumulative' => array_fill_keys($allFields, 0),
                ];
            }

            $isCurrentMonth = ((int)$record->time === $currentMonthTs);

            foreach ($allFields as $field) {
                $val = (float)($record->$field ?? 0);
                // 累计去年7月至今
                $channels[$channel]['cumulative'][$field] += $val;
                // 仅当月
                if ($isCurrentMonth) {
                    $channels[$channel]['monthly'][$field] += $val;
                }
            }
        }

        $finalResult = [];
        foreach ($channels as $channelName => $values) {
            $finalResult[] = [
                'channel'    => $channelName,
                'monthly'    => $this->injectAmountMan($values['monthly']),
                'cumulative' => $this->injectAmountMan($values['cumulative']),
            ];
        }

        return $finalResult;
    }

    public function getChannelReportMan2($data)
    {
        if (empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        // 1. 计算时间范围
        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        // 财年规则：如果当前月 >= 7月，起始是当年7月；否则是去年7月
        $startYear = ($month >= 7) ? $year : $year - 1;
        $startTs = mktime(0, 0, 0, 7, 1, $startYear);

        // 2. 一次性获取所有相关记录
        $records = DB::table('item_report_man')
            ->whereBetween('time', [$startTs, $currentMonthTs])
            ->get();

        // 3. 定义金额字段（按 B5-B27 的顺序排列，方便后面直接填充 row）
        $rowFieldMapping = [
            5  => 'receipt_amount',   6  => 'cost',            7  => 'profit',
            8  => 'settle_amount',    9  => 'gl_amount',       10 => 'wl_amount',
            11 => 'ht_amount',        12 => 'zk_amount',       13 => 'cx_amount',
            14 => 'tg_amount',        15 => 'cl_amount',       16 => 'kq_amount',
            17 => 'zp_amount',        18 => 'gg_amount',       19 => 'kd_amount',
            20 => 'xsqt_amount',      21 => 'ry_amount',       22 => 'sb_amount',
            23 => '退货亏损',          24 => 'cg_amount',       25 => 'sj_amount',
            26 => 'sx_amount',        27 => 'other_ck_amount'
        ];

        // 4. 初始化列容器（C到Z）
        $columns = [];
        $columnKeys = range('C', 'Z');
        foreach ($columnKeys as $col) {
            $columns[$col] = array_fill(5, 28, 0); // 5-32行，初始化为0
        }

        // 5. 遍历分拣数据
        foreach ($records as $record) {
            $empId = (int)$record->employee_id_1;
            $finance = $record->channel_finance;
            $isCurrent = ((int)$record->time === $currentMonthTs);

            foreach ($rowFieldMapping as $rowIdx => $field) {
                $val = (float)($record->$field ?? 0);

                // 规则分拣 (C, D, G, H, K, L, O, Q, R, U, W)
                // C: 2+社区
                if ($empId === 2 && $finance === '社区') {
                    if ($isCurrent) $columns['C'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val; // 累计
                }
                // D: (2+通路) + (5+通路)
                if (($empId === 2 || $empId === 5) && $finance === '通路') {
                    if ($isCurrent) $columns['D'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val; // 累计到F
                }
                // G: 3+通路
                if ($empId === 3 && $finance === '通路') {
                    if ($isCurrent) $columns['G'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                // H: 3+联华加盟
                if ($empId === 3 && $finance === '联华加盟') {
                    if ($isCurrent) $columns['H'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                // K: 23+通路
                if ($empId === 23 && $finance === '通路') {
                    if ($isCurrent) $columns['K'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                // L: 23+智鲸
                if ($empId === 23 && $finance === '智鲸') {
                    if ($isCurrent) $columns['L'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                // O: (7,8,54) + (通路,大卖场)
                if (in_array($empId, [7, 8, 54]) && in_array($finance, ['通路', '大卖场'])) {
                    if ($isCurrent) $columns['O'][$rowIdx] += $val;
                    $columns['P'][$rowIdx] += $val;
                }
                // Q: 48+通路
                if ($empId === 48 && $finance === '通路') {
                    if ($isCurrent) $columns['Q'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                // R: 48+大卖场
                if ($empId === 48 && $finance === '大卖场') {
                    if ($isCurrent) $columns['R'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                // U: 行政
                if ($finance === '行政') {
                    if ($isCurrent) $columns['U'][$rowIdx] += $val;
                    $columns['V'][$rowIdx] += $val;
                }
                // W: 鲍总
                if ($finance === '鲍总') {
                    if ($isCurrent) $columns['W'][$rowIdx] += $val;
                    $columns['X'][$rowIdx] += $val;
                }
            }
        }

        // 6. 计算横向合计列
        for ($i = 5; $i <= 27; $i++) {
            $columns['E'][$i] = $columns['C'][$i] + $columns['D'][$i]; // 张+霍当月合计
            $columns['I'][$i] = $columns['G'][$i] + $columns['H'][$i]; // 金小勇当月合计
            $columns['M'][$i] = $columns['K'][$i] + $columns['L'][$i]; // 沈强当月合计
            $columns['S'][$i] = $columns['Q'][$i] + $columns['R'][$i]; // 叶南汝当月合计

            // Y: 当月总计 (所有当月列累加)
            $columns['Y'][$i] = $columns['C'][$i] + $columns['D'][$i] + $columns['G'][$i] +
                $columns['H'][$i] + $columns['K'][$i] + $columns['L'][$i] +
                $columns['O'][$i] + $columns['Q'][$i] + $columns['R'][$i] +
                $columns['U'][$i] + $columns['W'][$i];

            // Z: 年累计总计 (所有累计列累加)
            $columns['Z'][$i] = $columns['F'][$i] + $columns['J'][$i] + $columns['N'][$i] +
                $columns['P'][$i] + $columns['T'][$i] + $columns['V'][$i] + $columns['X'][$i];
        }

        // 7. 组装最终供 Excel 填充的数组 (5行到32行)
        $finalData = [];
        for ($row = 5; $row <= 32; $row++) {
            $rowData = [];
            // 从 C 到 Z 循环取数据
            foreach (range('C', 'Z') as $colLetter) {
                $val = $columns[$colLetter][$row] ?? 0;
                // 28-32行填空字符串，其他格式化
                if ($row > 27 && $row < 32) {
                    $rowData[] = '';
                } elseif ($row == 32) {
                    // 如果需要算小计可以在这里加，目前先填空字符串
                    $rowData[] = '';
                } else {
                    $rowData[] = $val == 0 ? '0.00' : number_format($val, 2, '.', '');
                }
            }
            $finalData[] = $rowData;
        }

        return $finalData;
    }

    public function getChannelReportMan($data)
    {
        if (empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        // 1. 计算时间范围：7月财年规则
        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        $startYear = ($month >= 7) ? $year : $year - 1;
        $startTs = mktime(0, 0, 0, 7, 1, $startYear);

        // 2. 一次性获取数据
        $records = DB::table('item_report_man')
            ->whereBetween('time', [$startTs, $currentMonthTs])
            ->get();

        // 3. 字段映射 (注意 23 行，若数据库无字段则设为 null 会自动补 0)
        $rowFieldMapping = [
            5  => 'receipt_amount',   6  => 'cost',            7  => 'profit',
            8  => 'settle_amount',    9  => 'gl_amount',       10 => 'wl_amount',
            11 => 'ht_amount',        12 => 'zk_amount',       13 => 'cx_amount',
            14 => 'tg_amount',        15 => 'cl_amount',       16 => 'kq_amount',
            17 => 'zp_amount',        18 => 'gg_amount',       19 => 'kd_amount',
            20 => 'xsqt_amount',      21 => 'ry_amount',       22 => 'sb_amount',
            23 => null,               // 退货亏损 (不良仓)，暂无字段映射，填充0
            24 => 'cg_amount',        25 => 'sj_amount',
            26 => 'sx_amount',        27 => 'other_ck_amount'
        ];

        // 4. 初始化列容器 (C 到 Z)
        $columns = [];
        $columnKeys = range('C', 'Z');
        foreach ($columnKeys as $col) {
            $columns[$col] = array_fill(5, 28, 0); // 覆盖 5-32 行
        }

        // 5. 数据分拣累加
        foreach ($records as $record) {
            $empId = (int)$record->employee_id_1;
            $finance = $record->channel_finance;
            $isCurrent = ((int)$record->time === $currentMonthTs);

            foreach ($rowFieldMapping as $rowIdx => $field) {
                $val = $field ? (float)($record->$field ?? 0) : 0;

                // 分拣规则
                // 张春勇、霍尚琳
                if ($empId === 2 && $finance === '社区') {
                    if ($isCurrent) $columns['C'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val;
                }
                if (($empId === 2 || $empId === 5) && $finance === '通路') {
                    if ($isCurrent) $columns['D'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val;
                }
                // 金小勇
                if ($empId === 3 && $finance === '通路') {
                    if ($isCurrent) $columns['G'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                if ($empId === 3 && $finance === '联华加盟') {
                    if ($isCurrent) $columns['H'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                // 沈强
                if ($empId === 23 && $finance === '通路') {
                    if ($isCurrent) $columns['K'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                if ($empId === 23 && $finance === '智鲸') {
                    if ($isCurrent) $columns['L'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                // 王利英等组合 (O/P列)
                if (in_array($empId, [7, 8, 54]) && in_array($finance, ['通路', '大卖场'])) {
                    if ($isCurrent) $columns['O'][$rowIdx] += $val;
                    $columns['P'][$rowIdx] += $val;
                }
                // 叶南汝
                if ($empId === 48 && $finance === '通路') {
                    if ($isCurrent) $columns['Q'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                if ($empId === 48 && $finance === '大卖场') {
                    if ($isCurrent) $columns['R'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                // 行政
                if ($finance === '行政') {
                    if ($isCurrent) $columns['U'][$rowIdx] += $val;
                    $columns['V'][$rowIdx] += $val;
                }
                // 鲍总
                if ($finance === '鲍总') {
                    if ($isCurrent) $columns['W'][$rowIdx] += $val;
                    $columns['X'][$rowIdx] += $val;
                }
            }
        }

        // 6. 计算合计列与纵向小计
        for ($i = 5; $i <= 27; $i++) {
            // 合计列
            $columns['E'][$i] = $columns['C'][$i] + $columns['D'][$i];
            $columns['I'][$i] = $columns['G'][$i] + $columns['H'][$i];
            $columns['M'][$i] = $columns['K'][$i] + $columns['L'][$i];
            $columns['S'][$i] = $columns['Q'][$i] + $columns['R'][$i];

            // Y/Z 总计
            $columns['Y'][$i] = $columns['C'][$i] + $columns['D'][$i] + $columns['G'][$i] + $columns['H'][$i] +
                $columns['K'][$i] + $columns['L'][$i] + $columns['O'][$i] + $columns['Q'][$i] +
                $columns['R'][$i] + $columns['U'][$i] + $columns['W'][$i];

            $columns['Z'][$i] = $columns['F'][$i] + $columns['J'][$i] + $columns['N'][$i] + $columns['P'][$i] +
                $columns['T'][$i] + $columns['V'][$i] + $columns['X'][$i];
        }

        // 纵向小计 (第 32 行 = 8 到 31 行累加)
        foreach ($columnKeys as $col) {
            $subTotal = 0;
            for ($r = 8; $r <= 31; $r++) {
                $subTotal += (float)($columns[$col][$r] ?? 0);
            }
            $columns[$col][32] = $subTotal;
        }

        // 7. 最终格式化
        $finalData = [];
        for ($row = 5; $row <= 32; $row++) {
            $rowData = [];
            foreach ($columnKeys as $colLetter) {
                $val = $columns[$colLetter][$row] ?? 0;

                if ($row >= 28 && $row <= 31) {
                    $rowData[] = ''; // 预留行填空
                } else {
                    $rowData[] = ($val == 0) ? '0.00' : number_format($val, 2, '.', '');
                }
            }
            $finalData[] = $rowData;
        }

        return $finalData;
    }

    public function getChannelReportItem1($data)
    {
        if (empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        // 1. 计算时间范围：7月财年规则
        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        $startYear = ($month >= 7) ? $year : $year - 1;
        $startTs = mktime(0, 0, 0, 7, 1, $startYear);

        // 2. 一次性获取数据
        $records = DB::table('item_report')
            ->whereBetween('time', [$startTs, $currentMonthTs])
            ->get();

        // 3. 字段映射 (注意 24 行，若数据库无字段则设为 null 会自动补 0)
        $rowFieldMapping = [
            5  => 'receipt_amount',   6  => 'pk_amount',       7  => 'cost',    8  => 'profit',
            9  => 'settle_amount',    10  => 'gl_amount',      11 => 'wl_amount',
            12 => 'ht_amount',        13 => 'zk_amount',       14 => 'cx_amount',
            15 => 'tg_amount',        16 => 'cl_amount',       17 => 'kq_amount',
            18 => 'zp_amount',        19 => 'gg_amount',       20 => 'kd_amount',
            21 => 'xsqt_amount',      22 => 'ry_amount',       23 => 'sb_amount',
            24 => null,               // 退货亏损 (不良仓)，暂无字段映射，填充0
            25 => 'cg_amount',        26 => 'sj_amount',
            27 => 'sx_amount',        28 => 'other_ck_amount'
        ];

        // 4. 初始化列容器 (C 到 Z)
        $columns = [];
        $columnKeys = range('C', 'AB');
        foreach ($columnKeys as $col) {
            $columns[$col] = array_fill(5, 33, 0); // 覆盖 5-33 行
        }

        // 5. 数据分拣累加
        foreach ($records as $record) {
            $channel_details = $record->channel_details;
            $isCurrent = ((int)$record->time === $currentMonthTs);

            foreach ($rowFieldMapping as $rowIdx => $field) {
                $val = $field ? (float)($record->$field ?? 0) : 0;

                if ($channel_details === '大卖场') {
                    if ($isCurrent) $columns['C'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val;
                }
                if (($empId === 2 || $empId === 5) && $finance === '通路') {
                    if ($isCurrent) $columns['D'][$rowIdx] += $val;
                    $columns['F'][$rowIdx] += $val;
                }
                // 金小勇
                if ($empId === 3 && $finance === '通路') {
                    if ($isCurrent) $columns['G'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                if ($empId === 3 && $finance === '联华加盟') {
                    if ($isCurrent) $columns['H'][$rowIdx] += $val;
                    $columns['J'][$rowIdx] += $val;
                }
                // 沈强
                if ($empId === 23 && $finance === '通路') {
                    if ($isCurrent) $columns['K'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                if ($empId === 23 && $finance === '智鲸') {
                    if ($isCurrent) $columns['L'][$rowIdx] += $val;
                    $columns['N'][$rowIdx] += $val;
                }
                // 王利英等组合 (O/P列)
                if (in_array($empId, [7, 8, 54]) && in_array($finance, ['通路', '大卖场'])) {
                    if ($isCurrent) $columns['O'][$rowIdx] += $val;
                    $columns['P'][$rowIdx] += $val;
                }
                // 叶南汝
                if ($empId === 48 && $finance === '通路') {
                    if ($isCurrent) $columns['Q'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                if ($empId === 48 && $finance === '大卖场') {
                    if ($isCurrent) $columns['R'][$rowIdx] += $val;
                    $columns['T'][$rowIdx] += $val;
                }
                // 行政
                if ($finance === '行政') {
                    if ($isCurrent) $columns['U'][$rowIdx] += $val;
                    $columns['V'][$rowIdx] += $val;
                }
                // 鲍总
                if ($finance === '鲍总') {
                    if ($isCurrent) $columns['W'][$rowIdx] += $val;
                    $columns['X'][$rowIdx] += $val;
                }
            }
        }

        // 6. 计算合计列与纵向小计
        for ($i = 5; $i <= 27; $i++) {
            // 合计列
            $columns['E'][$i] = $columns['C'][$i] + $columns['D'][$i];
            $columns['I'][$i] = $columns['G'][$i] + $columns['H'][$i];
            $columns['M'][$i] = $columns['K'][$i] + $columns['L'][$i];
            $columns['S'][$i] = $columns['Q'][$i] + $columns['R'][$i];

            // Y/Z 总计
            $columns['Y'][$i] = $columns['C'][$i] + $columns['D'][$i] + $columns['G'][$i] + $columns['H'][$i] +
                $columns['K'][$i] + $columns['L'][$i] + $columns['O'][$i] + $columns['Q'][$i] +
                $columns['R'][$i] + $columns['U'][$i] + $columns['W'][$i];

            $columns['Z'][$i] = $columns['F'][$i] + $columns['J'][$i] + $columns['N'][$i] + $columns['P'][$i] +
                $columns['T'][$i] + $columns['V'][$i] + $columns['X'][$i];
        }

        // 纵向小计 (第 32 行 = 8 到 31 行累加)
        foreach ($columnKeys as $col) {
            $subTotal = 0;
            for ($r = 8; $r <= 31; $r++) {
                $subTotal += (float)($columns[$col][$r] ?? 0);
            }
            $columns[$col][32] = $subTotal;
        }

        // 7. 最终格式化
        $finalData = [];
        for ($row = 5; $row <= 32; $row++) {
            $rowData = [];
            foreach ($columnKeys as $colLetter) {
                $val = $columns[$colLetter][$row] ?? 0;

                if ($row >= 28 && $row <= 31) {
                    $rowData[] = ''; // 预留行填空
                } else {
                    $rowData[] = ($val == 0) ? '0.00' : number_format($val, 2, '.', '');
                }
            }
            $finalData[] = $rowData;
        }

        return $finalData;
    }

    public function getChannelReportItem($data)
    {
        if (empty($data['order_time'])) return [false, '年月不能为空'];
        $yearMonth = $data['order_time'];

        [$year, $month] = explode('-', $yearMonth);
        $year = (int)$year;
        $month = (int)$month;

        // 1. 财年计算 (7月起算)
        $currentMonthTs = mktime(0, 0, 0, $month, 1, $year);
        $startYear = ($month >= 7) ? $year : $year - 1;
        $startTs = mktime(0, 0, 0, 7, 1, $startYear);

        // 2. 获取数据
        $records = DB::table('item_report')
            ->whereBetween('time', [$startTs, $currentMonthTs])
            ->get();

        // 3. 字段映射 (严格对应 B 列 5-28 行项目)
        $rowFieldMapping = [
            5  => 'receipt_amount',   6  => 'pk_amount',       7  => 'cost',           8  => 'profit',
            9  => 'settle_amount',    10 => 'gl_amount',       11 => 'wl_amount',      12 => 'ht_amount',
            13 => 'zk_amount',        14 => 'cx_amount',       15 => 'tg_amount',      16 => 'cl_amount',
            17 => 'kq_amount',        18 => 'zp_amount',       19 => 'gg_amount',      20 => 'kd_amount',
            21 => 'xsqt_amount',      22 => 'ry_amount',       23 => 'sb_amount',
            24 => null,               // 退货亏损 (不良仓) 预留映射位
            25 => 'cg_amount',        26 => 'sj_amount',       27 => 'sx_amount',      28 => 'other_ck_amount'
        ];

        // 4. 初始化列容器 (C 到 AB, 即第 3 列到第 28 列)
        $columnKeys = [];
        for ($i = 3; $i <= 28; $i++) {
            $columnKeys[] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
        }

        $columns = [];
        foreach ($columnKeys as $col) {
            $columns[$col] = array_fill(5, 29, 0); // 初始化 5-33 行
        }

        // 渠道名称与其起始列(当月列)的映射
        $channelMap = [
            '大卖场' => 'C',
            '杭州大红鹰超市有限公司' => 'E',
            '杭州宏祐贸易有限公司' => 'G',
            '联华加盟' => 'I',
            '社区' => 'K',
            '通路' => 'M',
            '浙江汇德隆食品有限公司' => 'O',
            '浙江灵峰教育后勤管理有限公司' => 'Q',
            '浙江物联电子商务有限公司' => 'S',
            '浙江新宇贸易有限公司' => 'U',
            '智鲸' => 'W',
            '行政' => 'Y',
        ];

        // 5. 分拣累加
        foreach ($records as $record) {
            $channel = $record->channel_details;
            $isCurrent = ((int)$record->time === $currentMonthTs);

            if (isset($channelMap[$channel])) {
                $curCol = $channelMap[$channel]; // 当月列
                // 安全获取下一列(累计列)字母，避免 chr() 处理双字母失败
                $accCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(
                    \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($curCol) + 1
                );

                foreach ($rowFieldMapping as $rowIdx => $field) {
                    $val = $field ? (float)($record->$field ?? 0) : 0;
                    if ($isCurrent) $columns[$curCol][$rowIdx] += $val;
                    $columns[$accCol][$rowIdx] += $val;
                }
            }
        }

        // 6. 计算横向合计 (AA, AB) 与 纵向小计 (第 33 行)
        $channelStartCols = array_values($channelMap);
        for ($row = 5; $row <= 28; $row++) {
            foreach ($channelStartCols as $sCol) {
                $aCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(
                    \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($sCol) + 1
                );
                $columns['AA'][$row] += $columns[$sCol][$row];
                $columns['AB'][$row] += $columns[$aCol][$row];
            }
        }

        // 计算纵向小计 (第 33 行 = 9 到 28 行的累加，跳过预留行)
        foreach ($columnKeys as $col) {
            $subTotal = 0;
            for ($r = 9; $r <= 28; $r++) {
                $subTotal += (float)($columns[$col][$r] ?? 0);
            }
            $columns[$col][33] = $subTotal;
        }

        // 7. 最终格式化输出

        $finalData = [];
        for ($row = 5; $row <= 33; $row++) {
            // 关键：29-32行我们直接不生成数据，或者生成空
            if ($row >= 29 && $row <= 32) continue;

            $rowData = [];
            foreach ($columnKeys as $colLetter) {
                $val = $columns[$colLetter][$row] ?? 0;
                $rowData[] = ($val == 0) ? '0.00' : number_format($val, 2, '.', '');
            }
            // 使用行号作为 Key
            $finalData[$row] = $rowData;
        }

        return $finalData;
    }
}