youfeng/app/Service/TPlusServerService.php

<?php

namespace App\Service;

use App\Jobs\ProcessDataJob;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\EmployeeIndex;
use App\Model\Freight;
use App\Model\FreightFee;
use App\Model\ItemReport;
use App\Model\ItemReportMan;
use App\Model\ItemReportRoad;
use App\Model\Product;
use App\Model\RevenueCost;
use App\Model\RevenueCostTotal;
use App\Model\SalaryEmployee;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class TPlusServerService extends Service
{
    /**
     * @var TPlusDatabaseServerService
     */
    protected $databaseService;

    /**
     * @var string|null
     */
    protected $error;

    /**
     * TPlusServerService constructor.
     */
    public function __construct()
    {
        $service = new TPlusDatabaseServerService();
        $this->databaseService = $service->db;
        $this->error = $service->error;
    }

    /**
     * 获取错误信息
     *
     * @return string|null
     */
    public function getError()
    {
        return $this->error;
    }

    private $table = "tmp_revenue_cost_data";
    private $table_2 = "tmp_salary_employee";
    private $table_3 = "tmp_freight_fee";
    private $table_4 = "tmp_item_road";
    private $table_5 = "tmp_item_cost";

    /**
     * 同步人员部门
     *
     * @param array $data
     * @param array $user
     * @return array
     */
    public function synPersonDepart($data, $user)
    {
        try {
            $this->databaseService->table('AA_Department')
                ->select('id','idparent as parent_id','name as title','code','disabled as is_use')
                ->chunkById(100, function ($data) {
                    DB::transaction(function () use ($data) {
                        $dataArray = Collect($data)->map(function ($object) {
                            return (array)$object;
                        })->toArray();

                        $d_id = Depart::whereIn('id', array_column($dataArray,'id'))
                            ->pluck('id')
                            ->toArray();

                        $insert = $update = [];
                        foreach ($dataArray as $value){
                            $is_use = $value['is_use'] ? 0 : 1;
                            if(in_array($value['id'], $d_id)){
                                $update[] = [
                                    'id' => $value['id'],
                                    'parent_id' => $value['parent_id'],
                                    'title' => $value['title'],
                                    'code' => $value['code'],
                                    'is_use' => $is_use
                                ];
                            }else{
                                $insert[] = [
                                    'id' => $value['id'],
                                    'parent_id' => $value['parent_id'],
                                    'title' => $value['title'],
                                    'code' => $value['code'],
                                    'is_use' => $is_use
                                ];
                            }
                        }
                        if(! empty($insert)) Depart::insert($insert);
                        if(! empty($update)) {
                            foreach ($update as $value){
                                Depart::where('id', $value['id'])
                                    ->update($value);
                            }
                        }
                    });
                });
            $this->databaseService->table('AA_Person')
                ->select('id','code as number','name as emp_name','mobilePhoneNo as mobile','iddepartment as depart_id','disabled as state')
                ->chunkById(100, function ($data) {
                    DB::transaction(function () use ($data) {
                        $dataArray = Collect($data)->map(function ($object) {
                            return (array)$object;
                        })->toArray();

                        $employee_id = Employee::whereIn('id', array_column($dataArray,'id'))
                            ->pluck('id')
                            ->toArray();

                        $insert = $update = $depart_update = [];
                        foreach ($dataArray as $value){
                            $state = $value['state'] ? Employee::NOT_USE : Employee::USE;
                            if(in_array($value['id'], $employee_id)){
                                $update[] = [
                                    'id' => $value['id'],
                                    'number' => $value['number'],
                                    'emp_name' => $value['emp_name'],
                                    'mobile' => $value['mobile'],
                                    'state' => $state
                                ];
                            }else{
                                $insert[] = [
                                    'id' => $value['id'],
                                    'number' => $value['number'],
                                    'emp_name' => $value['emp_name'],
                                    'mobile' => $value['mobile'],
                                    'state' => $state
                                ];
                            }
                            $depart_update[] = [
                                'employee_id' => $value['id'],
                                'depart_id' => $value['depart_id']
                            ];
                        }
                        if(! empty($insert)) Employee::insert($insert);
                        if(! empty($update)) {
                            foreach ($update as $value){
                                Employee::where('id', $value['id'])
                                    ->update($value);
                            }
                        }
                        if(! empty($depart_update)){
                            EmployeeDepartPermission::whereIn('employee_id',array_column($depart_update,'employee_id'))->delete();
                            EmployeeDepartPermission::insert($depart_update);
                        }
                    });
                });
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }

        return [true, ''];
    }

    /**
     * 收入成本统计同步
     *
     * @param array $data
     * @param array $user
     * @return array
     */
    public function synRevenueCost($data, $user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '同步时间不能为空'];
        list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['crt_time'],false);
        if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "同步时间：时间区间无效"];
        list($bool, $bool_msg) = $this->isWithinMonths($start_time, $end_time);
        if(! $bool) return [false, $bool_msg];
        $data['start_timeStamp'] = $start_time;
        $data['end_timeStamp'] = $end_time;
        $start = date('Y-m-d H:i:s.000', $start_time);
        $end = date('Y-m-d H:i:s.000', $end_time);
        $data['start_time'] = $start;
        $data['end_time'] = $end;
        $data['operation_time'] = time();
        if(empty($data['type'])) return [false, '同步类型不能为空'];
        if(in_array($data['type'],[1,2,3,4])){
            list($status,$msg) = $this->limitingSendRequest($this->table);
            if(! $status) return [false, '收入成本相关信息同步正在后台运行,请稍后'];

//            //同步
//            list($status, $msg) = $this->synRevenueCostFromTPlus($data, $user);
//            if(! $status) {
//                return [false, $msg];
//            }

            //队列
            ProcessDataJob::dispatch($data, $user)->onQueue(RevenueCost::job);
        }else{
            return [false, '同步类型错误'];
        }

        return [true, '收入成本相关信息同步已进入后台任务'];
    }

    public function synRevenueCostFromTPlus($data, $user){
        //创建临时表 如果不存在
        $this->createTmpTable();
        //清理临时表 如果内容不为空
        $this->clearTmpTable();
        $type = $data['type'];

        //写入临时数据
        if($type == 1){
            list($status, $msg) = $this->xhdTPlus($data, $user);
            if(! $status) return [false, $msg];
            list($status, $msg) = $this->xsfpTPlus($data, $user);
            if(! $status) return [false, $msg];
            list($status, $msg) = $this->hkdTPlus($data, $user);
            if(! $status) return [false, $msg];
        }elseif ($type == 2){
            list($status, $msg) = $this->xhdTPlus($data, $user);
            if(! $status) return [false, $msg];
        }elseif ($type == 3){
            list($status, $msg) = $this->xsfpTPlus($data, $user);
            if(! $status) return [false, $msg];
        }elseif ($type == 4){
            list($status, $msg) = $this->hkdTPlus($data, $user);
            if(! $status) return [false, $msg];
        }

        //更新数据
        list($status,$msg) = $this->updateRevenueCost($data);
        if(! $status) return [false, $msg];

//        //更新主表数据
//        list($status,$msg) = $this->updateRevenueCostTotal($data);
//        if(! $status) return [false, $msg];

        //都成功后 清理临时表
        $this->clearTmpTable();
        //释放redis
        $this->delTableKey();

        return [true, '同步成功'];
    }

    private function xhdTPlus($data, $user){
        try {
            $table = $this->table;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('SA_SaleDelivery_b as sd_b')
                    ->join('SA_SaleDelivery as sd', 'sd_b.idSaleDeliveryDTO', '=', 'sd.ID')
                    ->leftJoin('AA_Partner as pn', 'sd.idsettlecustomer', '=', 'pn.ID')
                    ->leftJoin('AA_Person as ps', 'sd.idclerk', '=', 'ps.ID')
                    ->leftJoin('AA_Person as ps2', 'pn.idsaleman', '=', 'ps2.ID')
                    ->leftJoin('AA_Inventory as it', 'sd_b.idinventory', '=', 'it.ID')
                    ->leftJoin('AA_Unit as ui', 'sd_b.idbaseunit', '=', 'ui.ID')
                    ->where('sd.voucherdate', '>=', $data['start_time'])
                    ->where('sd.voucherdate', '<=', $data['end_time'])
                    ->where('sd_b.ID', '>', $lastId) // 用真实字段
                    ->orderBy('sd_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        COALESCE(sd.ID, 0) as order_id,
                        COALESCE(sd.code, '') as order_number,
                        sd.voucherdate as order_time,
                        sd.voucherState as order_state,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(sd.idclerk, 0) as employee_id_1,
                        COALESCE(ps2.name, '') as employee_id_2_title,
                        COALESCE(pn.idsaleman, 0) as employee_id_2,
                        COALESCE(pn.code, '') as customer_code,
                        COALESCE(pn.name, '') as customer_title,
                        COALESCE(pn.priuserdefnvc14, '') as customer_profit_rate,
                        COALESCE(sd.pubuserdefnvc11, '') as channel_finance,
                        COALESCE(sd.pubuserdefnvc12, '') as channel_details,
                        COALESCE(it.code, '') as product_code,
                        COALESCE(it.name, '') as product_title,
                        COALESCE(it.specification, '') as product_size,
                        COALESCE(ui.name, '') as unit,
                        COALESCE(sd_b.quantity, 0) as quantity,
                        COALESCE(sd_b.taxPrice, 0) as price_3,
                        COALESCE(sd_b.taxAmount, 0) as price_3_total,
                        COALESCE(sd_b.ID, 0) as id_detail,
                        COALESCE(sd_b.pubuserdefdecm9, 0) as is_activity
                    ")
                    ->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                //存货档案
                $product = Product::where('del_time', 0)
                    ->whereIn('code', array_unique(array_column($dataArray, 'product_code')))
                    ->select('code', 'write_off_price', 'freight_price', 'business_cost')
                    ->get()->toArray();
                $product_map = array_column($product, null, 'code');

                //组织数据
                foreach ($dataArray as $key => $value) {
                    $customer_profit_rate = rtrim($value['customer_profit_rate'],'%');
                    if(is_numeric($customer_profit_rate)){
                        $customer_profit_rate = bcdiv($customer_profit_rate,100,3);
                    }else{
                        $customer_profit_rate = 0;
                    }
                    $dataArray[$key]['customer_profit_rate'] = $customer_profit_rate;
                    $p_tmp = $product_map[$value['product_code']] ?? [];
                    $dataArray[$key]['order_type'] = RevenueCost::ORDER_ONE;
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                    $write_off_price = $p_tmp['write_off_price'] ?? 0;
                    $dataArray[$key]['price_1'] = $write_off_price;
                    $dataArray[$key]['price_1_total'] = bcmul($write_off_price, $value['quantity'], 2);
                    $freight_price = $p_tmp['freight_price'] ?? 0;
                    $dataArray[$key]['price_2'] = $freight_price;
                    $dataArray[$key]['price_2_total'] = bcmul($freight_price, $value['quantity'], 2);
                    $business_cost = $p_tmp['business_cost'] ?? 0;
                    $dataArray[$key]['price_4'] = $business_cost;
                    $price_4_total = bcmul($business_cost, $value['quantity'], 2);
                    $dataArray[$key]['price_4_total'] = bcmul($business_cost, $value['quantity'], 2);
                    $profit = bcsub($value['price_3_total'], $price_4_total, 2);
                    $dataArray[$key]['profit'] = $profit;
                    $dataArray[$key]['profit_rate'] = $value['price_3_total'] > 0 ? bcdiv($profit, $value['price_3_total'], 2) : 0;
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);
        }catch  (\Throwable $exception){
            return [false, "销货单同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function xsfpTPlus($data, $user){
        try {
            $table = $this->table;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('SA_SaleInvoice_b as si_b')
                    ->join('SA_SaleInvoice as si', 'si_b.idSaleInvoiceDTO', '=', 'si.ID')
                    ->leftJoin('SA_SaleDelivery_b as sd_b', 'si_b.sourceVoucherDetailId', '=', 'sd_b.ID')
                    ->leftJoin('AA_Partner as pn', 'si.idsettlecustomer', '=', 'pn.ID')
                    ->leftJoin('AA_Person as ps', 'si.idclerk', '=', 'ps.ID')
                    ->leftJoin('AA_Person as ps2', 'pn.idsaleman', '=', 'ps2.ID')
                    ->leftJoin('AA_Inventory as it', 'si_b.idinventory', '=', 'it.ID')
                    ->leftJoin('AA_Unit as ui', 'si_b.idbaseunit', '=', 'ui.ID')
                    ->where('si.voucherdate','>=',$data['start_time'])
                    ->where('si.voucherdate','<=',$data['end_time'])
                    ->where('si_b.ID', '>', $lastId) // 用真实字段
                    ->orderBy('si_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        COALESCE(si.ID, 0) as order_id,
                        COALESCE(si.code, '') as order_number,
                        si.voucherdate as order_time,
                        si.voucherState as order_state,
                        COALESCE(pn.code, '') as customer_code,
                        COALESCE(pn.name, '') as customer_title,
                        COALESCE(pn.priuserdefnvc14, '') as customer_profit_rate,
                        COALESCE(si.idclerk, 0) as employee_id_1,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(pn.idsaleman, 0) as employee_id_2,
                        COALESCE(ps2.name, '') as employee_id_2_title,
                        COALESCE(it.code, '') as product_code,
                        COALESCE(it.name, '') as product_title,
                        COALESCE(it.specification, '') as product_size,
                        COALESCE(ui.name, '') as unit,
                        COALESCE(si_b.quantity, 0) as quantity,
                        COALESCE(si_b.taxPrice, 0) as price_1,
                        COALESCE(si_b.taxAmount, 0) as price_1_total,
                        COALESCE(si_b.ID, 0) as id_detail,
                        COALESCE(si_b.sourceVoucherDetailId, 0) as id_detail_upstream,
                        COALESCE(si_b.sourceVoucherCode, '') as order_number_upstream,
                        COALESCE(sd_b.pubuserdefdecm9, 0) as is_activity
                    ")
                    ->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                //存货档案
                $product = Product::where('del_time',0)
                    ->whereIn('code', array_unique(array_column($dataArray,'product_code')))
                    ->select('code','business_cost')
                    ->get()->toArray();
                $product_map = array_column($product,null,'code');

                //组织数据
                foreach ($dataArray as $key => $value){
                    $customer_profit_rate = rtrim($value['customer_profit_rate'],'%');
                    if(is_numeric($customer_profit_rate)){
                        $customer_profit_rate = bcdiv($customer_profit_rate,100,3);
                    }else{
                        $customer_profit_rate = 0;
                    }
                    $dataArray[$key]['customer_profit_rate'] = $customer_profit_rate;
                    $p_tmp = $product_map[$value['product_code']] ?? [];
                    $dataArray[$key]['order_type'] = RevenueCost::ORDER_TWO;
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                    $business_cost = $p_tmp['business_cost'] ?? 0;
                    $dataArray[$key]['price_4'] = $business_cost;
                    $price_4_total = bcmul($business_cost, $value['quantity'],2);
                    $dataArray[$key]['price_4_total'] = bcmul($business_cost, $value['quantity'],2);
                    $profit = bcsub($value['price_1_total'], $price_4_total,2);
                    $dataArray[$key]['profit'] = $profit;
                    $dataArray[$key]['profit_rate'] = $value['price_1_total'] > 0 ? bcdiv($profit, $value['price_1_total'],2) : 0;
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);
        }catch  (\Throwable $exception){
            return [false, "销售发票同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function hkdTPlus($data, $user){
        try{
            $table = $this->table;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('ARAP_ReceivePayment_b as rp_b')
                    ->join('ARAP_ReceivePayment as rp', 'rp_b.idArapReceivePaymentDTO', '=', 'rp.ID')
//                    ->leftJoin('SA_SaleInvoice_b as si_b', 'rp_b.voucherDetailID', '=', 'si_b.ID')
//                    ->leftJoin('SA_SaleInvoice as si', 'si_b.idSaleInvoiceDTO', '=', 'si.ID')
//                    ->leftJoin('SA_SaleDelivery_b as sd_b', 'si_b.sourceVoucherDetailId', '=', 'sd_b.ID')
                    // 发票子表关联（仅限 idvouchertype = 20）
                    ->leftJoin('SA_SaleInvoice_b as si_b', function ($join) {
                        $join->on('rp_b.voucherDetailID', '=', 'si_b.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('SA_SaleInvoice as si', function ($join) {
                        $join->on('si_b.idSaleInvoiceDTO', '=', 'si.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('SA_SaleDelivery_b as sd_b', function ($join) {
                        $join->on('si_b.sourceVoucherDetailId', '=', 'sd_b.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('AA_Partner as pn', 'si.idsettlecustomer', '=', 'pn.ID')
                    ->leftJoin('AA_Person as ps', 'rp.idperson', '=', 'ps.ID')
                    ->leftJoin('AA_Person as ps2', 'pn.idsaleman', '=', 'ps2.ID')
                    ->leftJoin('AA_Inventory as it', 'si_b.idinventory', '=', 'it.ID')
                    ->leftJoin('AA_Unit as ui', 'si_b.idbaseunit', '=', 'ui.ID')
                    ->where('rp.voucherdate','>=',$data['start_time'])
                    ->where('rp.voucherdate','<=',$data['end_time'])
//                    ->where('rp_b.idvouchertype','=', 20) // 销售发票
                    ->where('rp.isReceiveFlag','=', 1)
                    ->where('rp_b.ID', '>', $lastId)
                    ->orderBy('rp_b.ID')
                    ->limit($limit)
//                    ->selectRaw("
//                        COALESCE(rp.ID, 0) as order_id,
//                        COALESCE(rp.code, '') as order_number,
//                        rp.voucherdate as order_time,
//                        COALESCE(pn.code, '') as customer_code,
//                        COALESCE(pn.name, '') as customer_title,
//                        COALESCE(pn.priuserdefnvc14, '') as customer_profit_rate,
//                        COALESCE(it.code, '') as product_code,
//                        COALESCE(it.name, '') as product_title,
//                        COALESCE(rp.idperson, 0) as employee_id_1,
//                        COALESCE(ps.name, '') as employee_id_1_title,
//                        COALESCE(pn.idsaleman, 0) as employee_id_2,
//                        COALESCE(ps2.name, '') as employee_id_2_title,
//                        COALESCE(it.specification, '') as product_size,
//                        COALESCE(ui.name, '') as unit,
//                        COALESCE(si_b.quantity, 0) as quantity,
//                        COALESCE(si_b.taxPrice, 0) as price_1,
//                        COALESCE(si_b.taxAmount, 0) as price_1_total,
//                        COALESCE(rp_b.amount, 0) as payment_amount,
//                        COALESCE(rp_b.ID, 0) as id_detail,
//                        COALESCE(rp_b.voucherDetailID, 0) as id_detail_upstream,
//                        COALESCE(rp_b.voucherCode, '') as order_number_upstream,
//                        COALESCE(sd_b.pubuserdefdecm9, 0) as is_activity
//                    ")
                    ->selectRaw("
                        COALESCE(rp.ID, 0) as order_id,
                        COALESCE(rp.code, '') as order_number,
                        rp.voucherdate as order_time,
                        rp.voucherstate as order_state,
                        COALESCE(rp.pubuserdefnvc11, '') as channel_finance,
                        COALESCE(rp.pubuserdefnvc12, '') as channel_details,
                        COALESCE(pn.code, '') as customer_code,
                        COALESCE(pn.name, '') as customer_title,
                        COALESCE(pn.priuserdefnvc14, '') as customer_profit_rate,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(it.code, '') ELSE '' END as product_code,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(it.name, '') ELSE '' END as product_title,
                        COALESCE(rp.idperson, 0) as employee_id_1,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(pn.idsaleman, 0) as employee_id_2,
                        COALESCE(ps2.name, '') as employee_id_2_title,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(it.specification, '') ELSE '' END as product_size,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(ui.name, '') ELSE '' END as unit,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.quantity, 0) ELSE 0 END as quantity,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.taxPrice, 0) ELSE 0 END as price_1,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.taxAmount, 0) ELSE 0 END as price_1_total,
                        COALESCE(rp_b.origCurrentAmount, 0) as payment_amount,
                        COALESCE(rp_b.ID, 0) as id_detail,
                        COALESCE(rp_b.voucherDetailID, 0) as id_detail_upstream,
                        COALESCE(rp_b.voucherCode, '') as order_number_upstream,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(sd_b.pubuserdefdecm9, 0) ELSE 0 END as is_activity,
                        rp_b.idvouchertype as voucher_type
                    ")
                    ->get();
//                COALESCE(rp_b.amount, 0) as payment_amount,

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                //存货档案
                $product = Product::where('del_time',0)
                    ->whereIn('code', array_unique(array_column($dataArray,'product_code')))
                    ->select('code','business_cost')
                    ->get()->toArray();
                $product_map = array_column($product,null,'code');

                //组织数据
                foreach ($dataArray as $key => $value){
                    $customer_profit_rate = rtrim($value['customer_profit_rate'],'%');
                    if(is_numeric($customer_profit_rate)){
                        $customer_profit_rate = bcdiv($customer_profit_rate,100,3);
                    }else{
                        $customer_profit_rate = 0;
                    }
                    $dataArray[$key]['customer_profit_rate'] = $customer_profit_rate;
                    $p_tmp = $product_map[$value['product_code']] ?? [];
                    $dataArray[$key]['order_type'] = RevenueCost::ORDER_THREE;
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                    $business_cost = $p_tmp['business_cost'] ?? 0;
                    $dataArray[$key]['price_4'] = $business_cost;
                    $price_4_total = bcmul($business_cost, $value['quantity'],2);
                    $dataArray[$key]['price_4_total'] = $price_4_total;
                    $profit = bcsub($value['price_1_total'], $price_4_total,2);
                    $dataArray[$key]['profit'] = $profit;
                    $dataArray[$key]['profit_rate'] = $value['price_1_total'] > 0 ? bcdiv($profit, $value['price_1_total'],2) : 0;
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);

        }catch (\Throwable $exception){
            return [false, "回款单同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function updateRevenueCost($data){
        try {
            $start_timeStamp = $data['start_timeStamp'];
            $end_timeStamp = $data['end_timeStamp'];
            $tmpTable = $this->table;
            $time = time();
            $ergs = $data;

            DB::transaction(function () use ($start_timeStamp, $end_timeStamp, $tmpTable,$time, $ergs) {
                // 1. 先软删除旧数据（你已有）
                RevenueCost::where('del_time', 0)
                    ->where('order_time', '>=', $start_timeStamp)
                    ->where('order_time', '<=', $end_timeStamp)
                    ->update(['del_time' => $time]);

                // 2. 分批从临时表插入新数据
                $batchSize = 500;
                $lastId = 0;
                do {
                    $chunk = DB::table($tmpTable)
                        ->where('id', '>', $lastId)
                        ->orderBy('id')
                        ->limit($batchSize)
                        ->get();

                    if ($chunk->isEmpty()) {
                        break;
                    }

                    $data = $chunk->map(function ($item) use($time){
                        return [
                            'order_id' => $item->order_id,
                            'order_number' => $item->order_number,
                            'order_time' => $item->order_time,
                            'order_state' => $item->order_state,
                            'employee_id_1_title' => $item->employee_id_1_title,
                            'employee_id_1' => $item->employee_id_1 ?? 0,
                            'employee_id_2' => $item->employee_id_2 ?? 0,
                            'employee_id_2_title' => $item->employee_id_2_title ?? "",
                            'customer_code' => $item->customer_code,
                            'customer_title' => $item->customer_title,
                            'channel_finance' => $item->channel_finance,
                            'channel_details' => $item->channel_details,
                            'product_code' => $item->product_code,
                            'product_title' => $item->product_title,
                            'product_size' => $item->product_size,
                            'unit' => $item->unit,
                            'quantity' => $item->quantity,
                            'price_1' => $item->price_1,
                            'price_1_total' => $item->price_1_total,
                            'price_2' => $item->price_2,
                            'price_2_total' => $item->price_2_total,
                            'price_3' => $item->price_3,
                            'price_3_total' => $item->price_3_total,
                            'price_4' => $item->price_4,
                            'price_4_total' => $item->price_4_total,
                            'profit' => $item->profit,
                            'profit_rate' => $item->profit_rate,
                            'id_detail' => $item->id_detail,
                            'order_type' => $item->order_type,
                            'payment_amount' => $item->payment_amount ?? 0,
                            'id_detail_upstream' => $item->id_detail_upstream?? 0,
                            'order_number_upstream' => $item->order_number_upstream ?? "",
                            'is_activity' => $item->is_activity ?? 0,
                            'customer_profit_rate' => $item->customer_profit_rate ?? '',
                            'voucher_type' => $item->voucher_type ?? 0,
                            'crt_time' => $time,
                        ];
                    })->toArray();

                    // 每批单独插入（可选：加小事务）
                    RevenueCost::insert($data);

                    // 更新 lastId
                    $lastId = $chunk->last()->id;

                } while ($chunk->count() == $batchSize);

                // 3. 更新主表
                list($status, $msg) = $this->updateRevenueCostTotal($ergs);
                if (! $status) {
                    throw new \Exception($msg);
                }
            });
        }catch  (\Throwable $exception){
            return [false, "单据明细同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function updateRevenueCostTotal($data){
        try {
            $start_timeStamp = $data['start_timeStamp'];
            $end_timeStamp = $data['end_timeStamp'];
            $time = time();

            //组织写入数据
            $return = [];
            $update_stamp = [];
            DB::table('revenue_cost')
                ->where('del_time',0)
                ->where('order_time', '>=', $start_timeStamp)
                ->where('order_time', '<=', $end_timeStamp)
                ->select(RevenueCost::$field)
                ->chunkById(100, function ($data) use(&$return,&$update_stamp){
                    $dataArray = Collect($data)->map(function ($object){
                        return (array)$object;
                    })->toArray();
                    foreach ($dataArray as $value){
                        //变成每个月第一天的时间戳
                        $time = date("Y-m-01", $value['order_time']);
                        $stamp = strtotime($time);
                        if(! in_array($stamp, $update_stamp)) $update_stamp[] = $stamp;
                        if($value['order_type'] == RevenueCost::ORDER_ONE){
                            $income = $value['price_3_total'];
                        }elseif ($value['order_type'] == RevenueCost::ORDER_TWO){
                            $income = $value['price_1_total'];
                        }else{
                            $income = $value['payment_amount'];
                        }
                        $adjust = $income < 0 ? $income : 0;
                        $business = $value['price_4_total'];
                        if(isset($return[$stamp][$value['order_type']][$value['employee_id_1']])){
                            $income_total = bcadd($return[$stamp][$value['order_type']][$value['employee_id_1']]['income'], $income,2);
                            $adjust_total = bcadd($return[$stamp][$value['order_type']][$value['employee_id_1']]['adjust'], $adjust,2);
                            $business_total = bcadd($return[$stamp][$value['order_type']][$value['employee_id_1']]['business'], $business,2);
                            $return[$stamp][$value['order_type']][$value['employee_id_1']]['income'] = $income_total;
                            $return[$stamp][$value['order_type']][$value['employee_id_1']]['adjust'] = $adjust_total;
                            $return[$stamp][$value['order_type']][$value['employee_id_1']]['business'] = $business_total;
                        }else{
                            $return[$stamp][$value['order_type']][$value['employee_id_1']] = [
                                'income' => $income,
                                'adjust' => $adjust,
                                'business' => $business,
                                'order_time' => $stamp,
                                'employee_id_1_title' => $value['employee_id_1_title'],
                            ];
                        }
                    }
                });
            $insert = [];
            foreach ($return as $value){
                foreach ($value as $order_type => $val){
                   foreach ($val as $employee_id => $v){
                        $profit = bcsub($v['income'], $v['business'],2);
                        $profit_rate = $v['income'] > 0 ? bcdiv($profit, $v['income'],2) : 0;
                        $v['profit'] = $profit;
                        $v['profit_rate'] = $profit_rate;
                        $v['order_type'] = $order_type;
                        $v['employee_id_1'] = $employee_id;
                        $v['crt_time'] = $time;
                        $insert[] = $v;
                    }
                }
            }

            RevenueCostTotal::where('del_time', 0)
                ->whereIn('order_time', $update_stamp)
                ->update(['del_time' => $time]);

            RevenueCostTotal::insert($insert);
        }catch  (\Throwable $exception){
            return [false, "主表同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function createTmpTable(){
        $table = $this->table;
        if (! Schema::hasTable($table)) {
            // 可以通过 migration 创建，或程序启动时检查
            Schema::create($table, function (Blueprint $table) {
                $table->bigIncrements('id'); // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
                $table->integer('order_type')->default(0);
                $table->bigInteger('order_id')->default(0);
                $table->string('order_number', 50)->default('');
                $table->integer('order_time')->nullable();
                $table->integer('order_state')->default(0);
                $table->string('employee_id_1_title', 100)->default('');
                $table->bigInteger('employee_id_1')->default(0);
                $table->bigInteger('employee_id_2')->default(0);
                $table->string('employee_id_2_title', 100)->default('');
                $table->string('customer_code', 50)->default('');
                $table->string('customer_title', 100)->default('');
                $table->string('channel_finance', 50)->nullable();
                $table->string('channel_details', 50)->nullable();
                $table->string('product_code', 50)->default('');
                $table->string('product_title', 100)->default('');
                $table->string('product_size', 100)->nullable();
                $table->string('unit', 20)->nullable();
                $table->decimal('quantity', 12, 2)->default(0);
                $table->decimal('price_1', 12, 2)->default(0); // 销项成本
                $table->decimal('price_1_total', 12, 2)->default(0);
                $table->decimal('price_2', 12, 2)->default(0); // 运费
                $table->decimal('price_2_total', 12, 2)->default(0);
                $table->decimal('price_3', 12, 2)->default(0); // 含税单价
                $table->decimal('price_3_total', 12, 2)->default(0); // 含税金额
                $table->decimal('price_4', 12, 2)->default(0); // 业务成本
                $table->decimal('price_4_total', 12, 2)->default(0);
                $table->decimal('profit', 12, 2)->default(0);
                $table->decimal('payment_amount', 12, 2)->default(0);
                $table->decimal('profit_rate', 10, 3)->default(0);
                $table->bigInteger('id_detail')->default(0);
                $table->bigInteger('id_detail_upstream')->default(0);
                $table->string('order_number_upstream', 100)->nullable();
                $table->decimal('is_activity', 2, 0)->default(0);
                $table->string('customer_profit_rate', 20)->default('');
                $table->bigInteger('voucher_type')->default(0);
            });
        }
    }

    public function clearTmpTable($type = 1){
        if($type == 1){
            if (Schema::hasTable($this->table)) DB::table($this->table)->truncate();
        }elseif ($type == 2){
            if (Schema::hasTable($this->table_2)) DB::table($this->table_2)->truncate();
        }elseif ($type == 3){
            if (Schema::hasTable($this->table_3)) DB::table($this->table_3)->truncate();
        }elseif ($type == 4){
            if (Schema::hasTable($this->table_4)) DB::table($this->table_4)->truncate();
            if (Schema::hasTable($this->table_5)) DB::table($this->table_5)->truncate();
        }
//        elseif ($type == 5){
//            if (Schema::hasTable($this->table_5)) DB::table($this->table_5)->truncate();
//        }
    }

    public function delTableKey($type = 1){
        $key = $this->table;
        if($type == 2) $key = $this->table_2;
        if($type == 3) $key = $this->table_3;
        if($type == 4) $key = $this->table_4;
        $this->dellimitingSendRequest($key);
    }

    public function synSalaryEmployee($data, $user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '同步时间不能为空'];
        list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['crt_time'],false);
        if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "同步时间：时间区间无效"];
        list($bool, $bool_msg) = $this->isWithinMonths($start_time, $end_time);
        if(! $bool) return [false, $bool_msg];
        $data['start_timeStamp'] = $start_time;
        $data['end_timeStamp'] = $end_time;
        $data['start_time'] = strtotime(date("Y-m-01",$data['start_timeStamp']));
        $data['end_time'] = strtotime(date("Y-m-01"),$data['end_timeStamp']);
        $data['operation_time'] = time();
        $data['type'] = 5;

        list($status,$msg) = $this->limitingSendRequest($this->table_2);
        if(! $status) return [false, '业务员工资同步正在后台运行,请稍后'];

        //同步
//        list($status, $msg) = $this->synSalaryEmployeeFromMine($data, $user);
//        if(! $status) {
//            $this->dellimitingSendRequest($this->table_2);
//            return [false, $msg];
//        }

        //队列
        ProcessDataJob::dispatch($data, $user, 2)->onQueue(RevenueCost::job2);

        return [true, '业务员工资相关信息同步已进入后台任务'];
    }

    public function synSalaryEmployeeFromMine($data, $user){
        //创建临时表 如果不存在
        $this->createTmpTable2();
        //清理临时表 如果内容不为空
        $this->clearTmpTable(2);

        //写入临时表
        DB::table('revenue_cost')
            ->where('del_time', 0)
            ->where('order_type', RevenueCost::ORDER_THREE)
            ->where('order_time','>=',$data['start_timeStamp'])
            ->where('order_time','<=',$data['end_timeStamp'])
            ->select([
                'employee_id_1',
                'employee_id_1_title',
                'order_time as time',
                DB::raw("DATE_FORMAT(FROM_UNIXTIME(order_time), '%Y-%m-01') as order_time"),
                DB::raw("SUM(payment_amount) as payment_amount"),
                DB::raw("SUM(CASE WHEN is_activity = 0 THEN payment_amount ELSE 0 END) as payment_amount_not_include_activity"),
                DB::raw("SUM(CASE WHEN is_activity = 1 THEN payment_amount ELSE 0 END) as payment_amount_activity"),
                DB::raw("SUM(CASE WHEN profit_rate < customer_profit_rate and is_activity = 0 THEN payment_amount ELSE 0 END) as payment_amount_lower_than_rate"),
                DB::raw("SUM(CASE WHEN profit_rate >= customer_profit_rate and is_activity = 0 THEN payment_amount ELSE 0 END) as payment_amount_greater_than_rate"),
                DB::raw("SUM(CASE WHEN profit_rate >= customer_profit_rate and is_activity = 0 THEN price_4_total ELSE 0 END) as business"),
                DB::raw("ROW_NUMBER() OVER (ORDER BY MIN(id)) as fake_id")
            ])
            ->groupBy('employee_id_1', DB::raw("DATE_FORMAT(FROM_UNIXTIME(order_time), '%Y-%m-01')"))
            ->orderBy('order_time', 'desc')
            ->chunkById(500, function ($data){
                $dataArray = Collect($data)->map(function ($object){
                    return (array)$object;
                })->toArray();

                $indexes = $this->getEmployeeIndex($dataArray);
                foreach ($dataArray as $key => $value){
                    $value['index_' . EmployeeIndex::TYPE_ONE] = 0;
                    $value['index_' . EmployeeIndex::TYPE_SIX] = 0;
                    $value['index_' . EmployeeIndex::TYPE_EIGHT] = 0;
                    $this->findIndex($indexes, $value);

                    $dataArray[$key]['order_type'] = RevenueCost::ORDER_THREE;
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                    $dataArray[$key]['sale_bonus'] = $value['index_' . EmployeeIndex::TYPE_EIGHT];
                    $dataArray[$key]['index_' . EmployeeIndex::TYPE_ONE] = $value['index_' . EmployeeIndex::TYPE_ONE];
                    $rate = bcdiv($value['index_' . EmployeeIndex::TYPE_ONE],100,2);
                    $pay_in_advance = bcmul($rate, $value['payment_amount_greater_than_rate'],2);
                    $dataArray[$key]['pay_in_advance'] = $pay_in_advance;
                    $dataArray[$key]['basic_salary'] = $value['index_' . EmployeeIndex::TYPE_SIX];
                    $dataArray[$key]['should_pay'] = bcadd(bcadd($value['index_' . EmployeeIndex::TYPE_EIGHT], $pay_in_advance,2),$value['index_' . EmployeeIndex::TYPE_SIX],2);

                    unset($dataArray[$key]['fake_id']);
                    unset($dataArray[$key]['time']);
                }

                DB::table($this->table_2)->insert($dataArray);
            }, 'fake_id');

        //更新数据
        list($status,$msg) = $this->updateSalaryEmployee($data);
        if(! $status) return [false, $msg];

        //清理临时表 如果内容不为空
        $this->clearTmpTable(2);
        //释放redis
        $this->delTableKey(2);

        return [true, '同步成功'];
    }

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

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

        // 一次性查出这些员工在最大区间范围内的所有指标
        $results = EmployeeIndex::where('del_time', 0)
            ->whereIn('type', [EmployeeIndex::TYPE_ONE, EmployeeIndex::TYPE_EIGHT, EmployeeIndex::TYPE_SIX])
            ->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 findIndex($indexes, &$value){
        // 找到所有符合条件的 index（可能多个 type）
        $matchedIndexes = $indexes->filter(function ($item) use ($value) {
            return $item['employee_id'] == $value['employee_id_1']
                && $item['start_time'] <= $value['time']
                && $item['end_time'] >= $value['time'];
        });
        // 按 type 去重，只保留第一次出现的
        $uniqueByType = [];
        foreach ($matchedIndexes as $item) {
            $index = "index_" . $item['type'];
            if (! isset($uniqueByType[$index])) {
                $uniqueByType[$index] = $item['index'];
            }
        }
        $value = array_merge($value, $uniqueByType);
    }

    private function createTmpTable2(){
        $table = $this->table_2;
        if (! Schema::hasTable($table)) {
            // 可以通过 migration 创建，或程序启动时检查
            Schema::create($table, function (Blueprint $table) {
                $table->bigIncrements('id'); // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
                $table->integer('order_type')->default(0);
                $table->integer('order_time')->default(0);
                $table->string('employee_id_1_title', 100)->default('');
                $table->bigInteger('employee_id_1')->default(0);
                $table->decimal('payment_amount', 12, 2)->default(0);
                $table->decimal('payment_amount_not_include_activity', 12, 2)->default(0);
                $table->decimal('payment_amount_activity', 12, 2)->default(0);
                $table->decimal('payment_amount_lower_than_rate', 12, 2)->default(0);
                $table->decimal('payment_amount_greater_than_rate', 12, 2)->default(0);
                $table->decimal('business', 12, 2)->default(0);
                $table->decimal('sale_bonus', 12, 2)->default(0);
                $table->decimal('index_1', 10, 2)->default(0);
                $table->decimal('pay_in_advance', 12, 2)->default(0);
                $table->decimal('basic_salary', 12, 2)->default(0);
                $table->decimal('should_pay', 12, 2)->default(0);
            });
        }
    }

    private function updateSalaryEmployee($data){
        try {
            $start_timeStamp = $data['start_time'];
            $end_timeStamp = $data['end_time'];
            $tmpTable = $this->table_2;
            $time = time();
            $ergs = $data;

            DB::transaction(function () use ($start_timeStamp, $end_timeStamp, $tmpTable,$time, $ergs) {
                // 1. 先软删除旧数据（你已有）
                SalaryEmployee::where('del_time', 0)
                    ->where('order_time', '>=', $start_timeStamp)
                    ->where('order_time', '<=', $end_timeStamp)
                    ->update(['del_time' => $time]);

                // 2. 分批从临时表插入新数据
                $batchSize = 500;
                $lastId = 0;
                do {
                    $chunk = DB::table($tmpTable)
                        ->where('id', '>', $lastId)
                        ->orderBy('id')
                        ->limit($batchSize)
                        ->get();

                    if ($chunk->isEmpty()) {
                        break;
                    }

                    $data = $chunk->map(function ($item) use($time){
                        return [
                            'order_type' => $item->order_type,
                            'order_time' => $item->order_time,
                            'employee_id_1_title' => $item->employee_id_1_title,
                            'employee_id_1' => $item->employee_id_1 ?? 0,
                            'payment_amount' => $item->payment_amount,
                            'payment_amount_not_include_activity' => $item->payment_amount_not_include_activity,
                            'payment_amount_activity' => $item->payment_amount_activity,
                            'payment_amount_lower_than_rate' => $item->payment_amount_lower_than_rate,
                            'payment_amount_greater_than_rate' => $item->payment_amount_greater_than_rate,
                            'business' => $item->business,
                            'sale_bonus' => $item->sale_bonus,
                            'index_1' => $item->index_1,
                            'pay_in_advance' => $item->pay_in_advance,
                            'basic_salary' => $item->basic_salary,
                            'should_pay' => $item->should_pay,
                            'crt_time' => $time,
                        ];
                    })->toArray();

                    // 每批单独插入（可选：加小事务）
                    SalaryEmployee::insert($data);

                    // 更新 lastId
                    $lastId = $chunk->last()->id;

                } while ($chunk->count() == $batchSize);
            });
        }catch  (\Throwable $exception){
            return [false, "单据明细同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    public function synFreightFee($data, $user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '同步时间不能为空'];
        list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($data['crt_time'],false);
        if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "同步时间：时间区间无效"];
        list($bool, $bool_msg) = $this->isWithinMonths($start_time, $end_time);
        if(! $bool) return [false, $bool_msg];
        $data['start_timeStamp'] = $start_time;
        $data['end_timeStamp'] = $end_time;
        $start = date('Y-m-d H:i:s.000', $start_time);
        $end = date('Y-m-d H:i:s.000', $end_time);
        $data['start_time'] = $start;
        $data['end_time'] = $end;
        $data['operation_time'] = time();
        $data['type'] = 6;

//        list($status, $msg) = $this->synFreightFeeFromMine($data, $user);dd($status, $msg);
        list($status,$msg) = $this->limitingSendRequest($this->table_3);
        if(! $status) return [false, '运费统计同步正在后台运行,请稍后'];

        //同步
//        list($status, $msg) = $this->synFreightFeeFromMine($data, $user);
//        if(! $status) {
//            $this->dellimitingSendRequest($this->table_3);
//            return [false, $msg];
//        }

        //队列
        ProcessDataJob::dispatch($data, $user, 3)->onQueue(RevenueCost::job3);

        return [true, '运费统计同步已进入后台任务'];
    }

    public function synFreightFeeFromMine($data, $user){
        //创建临时表 如果不存在
        $this->createTmpTable3();
        //清理临时表 如果内容不为空
        $this->clearTmpTable(3);

        //生成临时数据
        list($status, $msg) = $this->getFreightData($data);
        if(! $status) return [false, $msg];

        //写入
        list($status, $msg) = $this->updateFreightFeeFromMine($data);
        if(! $status) return [false, $msg];

        //清理临时表 如果内容不为空
        $this->clearTmpTable(3);
        //释放redis
        $this->delTableKey(3);

        return [true, '同步成功'];
    }

    private function createTmpTable3(){
        $table = $this->table_3;
        if (! Schema::hasTable($table)) {
            // 可以通过 migration 创建，或程序启动时检查
            Schema::create($table, function (Blueprint $table) {
                // 主键
                $table->bigIncrements('id');
                // 原始字段
                $table->bigInteger('order_id')->default(0)->comment('订单ID');
                $table->string('order_number')->default('')->comment('订单编号');
                $table->dateTime('order_time')->nullable()->comment('订单时间');
                $table->integer('order_state')->default(0)->comment('订单状态');
                $table->string('employee_id_1_title')->default('')->comment('业务员1名称');
                $table->bigInteger('employee_id_1')->default(0)->comment('业务员1 ID');
                $table->string('employee_id_2_title')->default('')->comment('上级管理人员名称');
                $table->bigInteger('employee_id_2')->default(0)->comment('上级管理人员ID');
                $table->string('customer_code')->default('')->comment('客户编码');
                $table->string('customer_title')->default('')->comment('客户名称');
                $table->decimal('customer_store_price', 10, 2)->default(0)->comment('客户卸货费单价');
                $table->string('product_code')->default('')->comment('产品编码');
                $table->string('product_title')->default('')->comment('产品名称');
                $table->string('product_size')->default('')->comment('产品规格');
                $table->decimal('product_box_size', 10, 2)->default(0)->comment('装箱数');
                $table->decimal('product_weight', 10, 3)->default(0)->comment('单件重量(kg)');
                $table->decimal('product_store_price', 10, 2)->default(0)->comment('门店卸货费单价');
                $table->decimal('product_store_price2', 10, 2)->default(0)->comment('到货装卸费单价');
                $table->string('product_category')->default('')->comment('货类');
                $table->string('unit')->default('')->comment('单位');
                $table->decimal('quantity', 12, 3)->default(0)->comment('数量');
                $table->decimal('price_3', 12, 2)->default(0)->comment('含税单价');
                $table->decimal('price_3_total', 14, 2)->default(0)->comment('含税金额');
                $table->bigInteger('id_detail')->default(0)->comment('明细ID');
                $table->boolean('is_present')->default(false)->comment('是否赠品');
                $table->string('mark')->default('')->comment('订单备注');
                $table->string('item_mark')->default('')->comment('明细备注');
                $table->bigInteger('warehouse_id')->default(0)->comment('仓库ID');
                $table->string('warehouse_name')->default('')->comment('仓库名称');
                $table->bigInteger('business_type_id')->default(0)->comment('业务类型ID');
                $table->string('business_type_title')->default('')->comment('业务类型名称');
                $table->string('address')->default('')->comment('送货地址');
                $table->string('area_hs')->default('')->comment('区域');
                $table->string('delivery_mode')->default('')->comment('配送方式');
                $table->decimal('sl_fee', 10, 2)->default(0)->comment('上楼费');
                // 新增的计算字段
                $table->integer('xs')->default(0)->comment('箱数');
                $table->decimal('weight', 12, 4)->default(0)->comment('总重量(kg)');
                $table->tinyInteger('area_range')->default(1)->comment('运价区间 (1: <5kg, 2: >=5kg)');
                $table->decimal('freight_unit_price', 10, 2)->default(0)->comment('配送费单价');
                $table->decimal('freight_amount', 12, 4)->default(0)->comment('配送费金额');
                $table->decimal('js_single_amount', 12, 4)->default(0)->comment('结算金额');
                $table->decimal('min_freight_amount', 12, 2)->default(0)->comment('地区最低运价');
                $table->decimal('customer_store_zx_fee', 12, 4)->default(0)->comment('门店卸货费');
                $table->decimal('dh_fee', 12, 4)->default(0)->comment('到货装卸费');
            });
        }
    }

    private function getFreightData($data){
        //写入临时表
        try {
            $table = $this->table_3;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('SA_SaleDelivery_b as sd_b')
                    ->join('SA_SaleDelivery as sd', 'sd_b.idSaleDeliveryDTO', '=', 'sd.ID')
                    ->leftJoin('AA_Partner as pn', 'sd.idcustomer', '=', 'pn.ID') // 客户
                    ->leftJoin('AA_Person as ps', 'sd.idclerk', '=', 'ps.ID') // 业务员
                    ->leftJoin('AA_Person as ps2', 'pn.idsaleman', '=', 'ps2.ID')
                    ->leftJoin('AA_Inventory as it', 'sd_b.idinventory', '=', 'it.ID')
                    ->leftJoin('AA_Unit as ui', 'sd_b.idbaseunit', '=', 'ui.ID')
                    ->leftJoin('AA_Warehouse as wa', 'sd_b.idwarehouse', '=', 'wa.id')
                    ->leftJoin('AA_Busitype as bs', 'sd.idbusinesstype', '=', 'bs.id')
                    ->where('sd.voucherdate', '>=', $data['start_time'])
                    ->where('sd.voucherdate', '<=', $data['end_time'])
                    ->where('sd.voucherState', '=', 189) //181  未审核   189  已审核
                    ->where('sd_b.ID', '>', $lastId) // 用真实字段
                    ->orderBy('sd_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        COALESCE(sd.ID, 0) as order_id,
                        COALESCE(sd.code, '') as order_number,
                        sd.voucherdate as order_time,
                        sd.voucherState as order_state,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(sd.idclerk, 0) as employee_id_1,
                        COALESCE(ps2.name, '') as employee_id_2_title,
                        COALESCE(pn.idsaleman, 0) as employee_id_2,
                        COALESCE(pn.code, '') as customer_code,
                        COALESCE(pn.name, '') as customer_title,
                        COALESCE(pn.priuserdefdecm3, 0) as customer_store_price,
                        COALESCE(it.code, '') as product_code,
                        COALESCE(it.name, '') as product_title,
                        COALESCE(it.specification, '') as product_size,
                        COALESCE(it.priuserdefdecm1, 0) as product_box_size,
                        COALESCE(it.priuserdefdecm3, 0) as product_weight,
                        COALESCE(it.priuserdefdecm10, 0) as product_store_price,
                        COALESCE(it.priuserdefdecm11, 0) as product_store_price2,
                        COALESCE(it.priuserdefnvc5, '') as product_category,
                        COALESCE(ui.name, '') as unit,
                        COALESCE(sd_b.quantity, 0) as quantity,
                        COALESCE(sd_b.taxPrice, 0) as price_3,
                        COALESCE(sd_b.taxAmount, 0) as price_3_total,
                        COALESCE(sd_b.ID, 0) as id_detail,
                        COALESCE(sd_b.isPresent, 0) as is_present,
                        COALESCE(sd_b.DetailMemo, '') as item_mark,
                        COALESCE(sd.memo, '') as mark,
                        sd_b.idwarehouse as warehouse_id,
                        COALESCE(wa.name, '') as warehouse_name,
                        sd.idbusinesstype as business_type_id,
                        COALESCE(bs.name , '') as business_type_title,
                        COALESCE(sd.address , '') as address,
                        COALESCE(sd.priuserdefnvc2 , '') as area_hs,
                        COALESCE(sd.deliveryMode , 0) as delivery_mode,
                        COALESCE(sd.priuserdefdecm5 , 0) as sl_fee
                    ")
                    ->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                $freight = Freight::where('del_time',0)
                    ->whereIn('region',array_column($dataArray,'area_hs'))
                    ->select('region','one_and_five','greater_than_five','min_freight_fee')
                    ->get()->toArray();
                $freight_map = array_column($freight,null,'region');

                //组织数据
                foreach ($dataArray as $key => $value) {
                    $quantity = abs($value['quantity']);
                    //箱数
                    $xs = 0;
                    if(! empty($value['product_box_size']) && is_numeric($value['product_box_size']) && $value['product_box_size'] > 0) $xs = ceil($quantity / $value['product_box_size']);
                    $dataArray[$key]['xs'] = $xs;
                    //总重量
                    $weight = 0;
                    if(! empty($value['product_weight']) && is_numeric($value['product_weight']) && $value['product_weight'] > 0) $weight = bcdiv(bcmul($xs, $value['product_weight']),1000,4);
                    $dataArray[$key]['weight'] = $weight;
                    //运价区间
                    $area_range = 1;
                    if($weight >= 5) $area_range = 2;
                    $dataArray[$key]['area_range'] = $area_range;
                    //配送费单价
                    $freight = $min_freight = 0;
                    if(isset($freight_map[$value['area_hs']])) {
                        $tmp = $freight_map[$value['area_hs']];
                        if($area_range == 1){
                            $freight = $tmp['one_and_five'];
                        }else{
                            $freight = $tmp['greater_than_five'];
                        }
                        $min_freight = $tmp['min_freight_fee'];
                    }
                    $dataArray[$key]['freight_unit_price'] = $freight;
                    //地区最低运价
                    $dataArray[$key]['min_freight_amount'] = $min_freight;

                    //纯自提不计算-------------------------------------------------------
                    if($value['delivery_mode'] == FreightFee::deliveryModeNormal){
                        //配送费金额
                        $dataArray[$key]['freight_amount'] = 0;
                        //结算金额 记录的是每一条的
                        $dataArray[$key]['js_single_amount'] = 0;
                        //门店卸货费
                        $dataArray[$key]['customer_store_zx_fee'] = 0;
                        //到货装卸费
                        $dataArray[$key]['dh_fee'] = 0;
                    }else{
                        //配送费金额
                        $freight_amount = bcmul($weight, $freight,4);
                        $dataArray[$key]['freight_amount'] = $freight_amount;
                        //结算金额 记录的是每一条的
                        $dataArray[$key]['js_single_amount'] = $freight_amount;
                        //门店卸货费
                        $customer_store_price = 0;
                        if(! empty($value['customer_store_price']) && is_numeric($value['customer_store_price']) && $value['customer_store_price'] > 0){
                            $customer_store_price = bcmul($value['customer_store_price'], $xs,4);
                        }else{
                            if(! empty($value['product_store_price']) && is_numeric($value['product_store_price']) && $value['product_store_price'] > 0){
                                $customer_store_price = bcmul($value['product_store_price'], $xs,4);
                            }
                        }
                        $dataArray[$key]['customer_store_zx_fee'] = $customer_store_price;
                        //到货装卸费
                        $product_store_price2 = 0;
                        if(! empty($value['product_store_price2']) && is_numeric($value['product_store_price2']))  $product_store_price2 = $value['product_store_price2'];
                        if(! empty($value['product_category']) && $value['product_category'] == "礼盒"){
                            $dh_fee = bcmul($xs, $product_store_price2,4);
                        }else{
                            $dh_fee = bcmul($weight, $product_store_price2,4);
                        }
                        if($value['business_type_id'] == FreightFee::businessTypeReturn) $dh_fee = bcmul($dh_fee,2,4);
                        $dataArray[$key]['dh_fee'] = $dh_fee;
                    }
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);
        }catch  (\Throwable $exception){
            return [false, "运费获取销货单数据异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function updateFreightFeeFromMine($data){
        try {
            $start_timeStamp = $data['start_timeStamp'];
            $end_timeStamp = $data['end_timeStamp'];
            $tmpTable = $this->table_3;
            $time = time();
            $ergs = $data;

            DB::transaction(function () use ($start_timeStamp, $end_timeStamp, $tmpTable,$time, $ergs) {
                // 1. 先软删除旧数据（你已有）
                FreightFee::where('del_time', 0)
                    ->where('order_time', '>=', $start_timeStamp)
                    ->where('order_time', '<=', $end_timeStamp)
                    ->update(['del_time' => $time]);

                // 2. 分批从临时表插入新数据
                $batchSize = 500;
                $lastId = 0;
                do {
                    $chunk = DB::table($tmpTable)
                        ->where('id', '>', $lastId)
                        ->orderBy('id')
                        ->limit($batchSize)
                        ->get();

                    if ($chunk->isEmpty()) {
                        break;
                    }

                    $data = $chunk->map(function ($item) use($time){
                        return [
                            // 基础字段（按你给的顺序）
                            'order_id' => $item->order_id ?? 0,
                            'order_number' => $item->order_number ?? '',
                            'order_time' => $item->order_time ? strtotime($item->order_time) : 0,
                            'order_state' => $item->order_state ?? 0,
                            'employee_id_1_title' => $item->employee_id_1_title ?? '',
                            'employee_id_1' => $item->employee_id_1 ?? 0,
                            'employee_id_2_title' => $item->employee_id_2_title ?? '',
                            'employee_id_2' => $item->employee_id_2 ?? 0,
                            'customer_code' => $item->customer_code ?? '',
                            'customer_title' => $item->customer_title ?? '',
                            'customer_store_price' => $item->customer_store_price ?? 0,
                            'product_code' => $item->product_code ?? '',
                            'product_title' => $item->product_title ?? '',
                            'product_size' => $item->product_size ?? '',
                            'product_box_size' => $item->product_box_size ?? 0,
                            'product_weight' => $item->product_weight ?? 0,
                            'product_store_price' => $item->product_store_price ?? 0,
                            'product_store_price2' => $item->product_store_price2 ?? 0,
                            'product_category' => $item->product_category ?? '',
                            'unit' => $item->unit ?? '',
                            'quantity' => $item->quantity ?? 0,
                            'price_3' => $item->price_3 ?? 0,
                            'price_3_total' => $item->price_3_total ?? 0,
                            'id_detail' => $item->id_detail ?? 0,
                            'is_present' => $item->is_present ?? '',
                            'mark' => $item->mark ?? '',
                            'item_mark' => $item->item_mark ?? '',
                            'warehouse_id' => $item->warehouse_id ?? 0,
                            'warehouse_name' => $item->warehouse_name ?? '',
                            'business_type_id' => $item->business_type_id ?? 0,
                            'business_type_title' => $item->business_type_title ?? '',
                            'address' => $item->address ?? '',
                            'area_hs' => $item->area_hs ?? '',
                            'delivery_mode' => $item->delivery_mode ?? 0,
                            'sl_fee' => $item->sl_fee ?? 0,
                            'xs' => $item->xs,
                            'weight' => $item->weight ?? 0,
                            'area_range' => $item->area_range ?? 0,
                            'freight_unit_price' => $item->freight_unit_price ?? 0,
                            'freight_amount' => $item->freight_amount ?? 0,
                            'js_single_amount' => $item->js_single_amount ?? 0,
                            'min_freight_amount' => $item->min_freight_amount ?? 0,
                            'customer_store_zx_fee' => $item->customer_store_zx_fee ?? 0,
                            'dh_fee' => $item->dh_fee ?? 0,
                            'crt_time' => $time,
                        ];
                    })->toArray();

                    // 每批单独插入（可选：加小事务）
                    FreightFee::insert($data);

                    // 更新 lastId
                    $lastId = $chunk->last()->id;

                } while ($chunk->count() == $batchSize);
            });
        }catch  (\Throwable $exception){
            return [false, "单据明细同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    public function synItemRoad($data, $user){
        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false, '同步时间不能为空'];
        $start_time = $data['crt_time'][0] / 1000;
        $end_time = $data['crt_time'][1] / 1000;
        list($bool, $bool_msg) = $this->isWithinMonths($start_time, $end_time);
        if(! $bool) return [false, $bool_msg];

        $data['start_timeStamp'] = $start_time;
        $data['end_timeStamp'] = $end_time;
        $start = date('Y-m-d H:i:s.000', $start_time);
        $end = date('Y-m-d H:i:s.000', $end_time);
        $data['start_time'] = $start;
        $data['end_time'] = $end;
        $data['operation_time'] = time();
        $data['type'] = 7;

//        list($status, $msg) = $this->synItemRoadMy($data, $user);dd($status, $msg);

        list($status,$msg) = $this->limitingSendRequest($this->table_4);
        if(! $status) return [false, '统计同步正在后台运行,请稍后'];

        //队列
        ProcessDataJob::dispatch($data, $user, 4)->onQueue(RevenueCost::job4);

        return [true, '统计同步已进入后台任务'];
    }

    private function createTmpTable4(){
        $table = $this->table_4;
        if (! Schema::hasTable($table)) {
            Schema::create($table, function (Blueprint $table) {
                $table->bigIncrements('id');

                // 订单基础信息
                $table->bigInteger('order_id')->default(0)->comment('收款单ID(rp.ID)');
                $table->string('order_number')->default('')->comment('收款单编号(rp.code)');
                $table->integer('order_time')->default(0)->comment('单据时间(时间戳)');
                $table->integer('order_state')->default(0)->comment('订单状态');
                $table->integer('order_type')->default(0)->comment('订单分类标识');

                // 渠道/分销相关
                $table->string('channel_finance')->default('')->comment('渠道财务(rp.pubuserdefnvc11)');
                $table->string('channel_details')->default('')->comment('渠道明细(rp.pubuserdefnvc12)');
                $table->string('channel_details_fp')->default('')->comment('发票渠道明细(si.pubuserdefnvc12)');

                // 客户信息
                $table->string('customer_code')->default('')->comment('结算客户编码');
                $table->string('customer_title')->default('')->comment('结算客户名称');

                // 人员信息
                $table->bigInteger('employee_id_1')->default(0)->comment('业务员ID');
                $table->string('employee_id_1_title')->default('')->comment('业务员名称');
                $table->bigInteger('employee_id_2')->default(0)->comment('上级管理人员ID');
                $table->string('employee_id_2_title')->default('')->comment('上级管理人员名称');

                // 产品信息 (仅限 idvouchertype = 20)
                $table->string('product_code')->default('')->comment('产品编码');
                $table->string('product_title')->default('')->comment('产品名称');
                $table->decimal('quantity', 14, 3)->default(0)->comment('发票数量');

                // 收入/价格相关
                $table->decimal('price_1', 14, 4)->default(0)->comment('含税单价(taxPrice)');
                $table->decimal('price_1_total', 14, 2)->default(0)->comment('含税金额(taxAmount)');
                $table->decimal('payment_amount', 14, 2)->default(0)->comment('明细行的收款金额');

                // 成本相关 (计算得出)
                $table->decimal('price_4', 14, 4)->default(0)->comment('业务成本单价');
                $table->decimal('price_4_total', 14, 2)->default(0)->comment('业务成本总额');

                // 关联追溯字段
                $table->bigInteger('id_detail')->default(0)->comment('收款明细ID(rp_b.ID)');
                $table->bigInteger('id_detail_upstream')->default(0)->comment('上游单据明细ID(voucherDetailID)');
                $table->string('order_number_upstream')->default('')->comment('上游单据编号(voucherCode)');
                $table->integer('voucher_type')->default(0)->comment('单据类型(idvouchertype)');
                $table->string('is_kh')->default('0')->comment('是否计入考核(sd.priuserdefnvc4)');
            });
        }
    }

    private function createTmpTable5(){
        $table = $this->table_5;
        if (! Schema::hasTable($table)) {
            Schema::create($table, function (Blueprint $table) {
                $table->bigIncrements('id');

                // 基础信息
                $table->bigInteger('order_id')->default(0)->comment('费用单ID(cs.ID)');
                $table->string('order_number')->default('')->comment('费用单编号(cs.code)');
                $table->integer('order_time')->default(0)->comment('单据时间(时间戳)');
                $table->integer('order_type')->default(0)->comment('订单分类标识(1 费用单 2 其他出库单)');
                $table->string('ck_type')->default("")->comment('出库单出库类别');
                $table->integer('employee_id_1')->default(0)->comment('业务员id');
                $table->string('employee_id_1_title')->default('')->comment('业务员名称');

                // 维度信息
                $table->string('channel_finance')->default('')->comment('渠道财务(cs.pubuserdefnvc11)');
                $table->string('channel_details')->default('')->comment('渠道明细(cs.pubuserdefnvc12)');
                $table->string('fy_code')->default('')->comment('费用项目编码(ae.code)');

                // 金额信息
                $table->decimal('taxamount', 14, 2)->default(0)->comment('含税金额(cs_b.taxamount)');

                // 追溯信息
                $table->bigInteger('id_detail')->default(0)->comment('费用明细行ID(cs_b.ID)');
            });
        }
    }

    public function synItemRoadMy($data, $user){
        //收入
        list($status, $msg) = $this->synReceipt($data,$user);
        if(! $status) return [false, $msg];

        //成本
        list($status, $msg) = $this->synSaleCost($data,$user);
        if(! $status) return [false, $msg];

        //汇总项目报表
        list($status, $msg) = $this->saveResult($data,$user);
        if(! $status) return [false, $msg];

        //清理临时表 如果内容不为空
        $this->clearTmpTable(4);
        //释放redis
        $this->delTableKey(4);

        return [true, ''];
    }

    private function synReceipt($data, $user){
        //创建临时表 如果不存在
        $this->createTmpTable4();
        //清理临时表 如果内容不为空
        $this->clearTmpTable(4);

        try{
            $table = $this->table_4;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('ARAP_ReceivePayment_b as rp_b')
                    ->join('ARAP_ReceivePayment as rp', 'rp_b.idArapReceivePaymentDTO', '=', 'rp.ID')
                    // 发票子表关联（仅限 idvouchertype = 20）
                    ->leftJoin('SA_SaleInvoice_b as si_b', function ($join) {
                        $join->on('rp_b.voucherDetailID', '=', 'si_b.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('SA_SaleInvoice as si', function ($join) {
                        $join->on('si_b.idSaleInvoiceDTO', '=', 'si.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('SA_SaleDelivery_b as sd_b', function ($join) {
                        $join->on('si_b.sourceVoucherDetailId', '=', 'sd_b.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('SA_SaleDelivery as sd', function ($join) {
                        $join->on('sd_b.idSaleDeliveryDTO', '=', 'sd.ID')
                            ->where('rp_b.idvouchertype', '=', 20);
                    })
                    ->leftJoin('AA_Inventory as it', 'sd_b.idinventory', '=', 'it.ID')
                    ->leftJoin('AA_Partner as pn', 'si.idsettlecustomer', '=', 'pn.ID') //结算客户
                    ->leftJoin('AA_Person as ps', 'rp.idperson', '=', 'ps.ID')
                    ->leftJoin('AA_Person as ps2', 'pn.idsaleman', '=', 'ps2.ID') // 结算客户的上级管理人员
                    ->where('rp.voucherdate','>=',$data['start_time'])
                    ->where('rp.voucherdate','<=',$data['end_time'])
//                    ->where('priuserdefnvc4','=', 1) //.. 不计入考核 todo
                    ->where('rp.isReceiveFlag','=', 1)
                    ->where('rp_b.ID', '>', $lastId)
                    ->orderBy('rp_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        COALESCE(rp.ID, 0) as order_id,
                        COALESCE(rp.code, '') as order_number,
                        rp.voucherdate as order_time,
                        rp.voucherstate as order_state,
                        COALESCE(rp.pubuserdefnvc11, '') as channel_finance,
                        COALESCE(rp.pubuserdefnvc12, '') as channel_details,
                        COALESCE(si.pubuserdefnvc12, '') as channel_details_fp,
                        COALESCE(pn.code, '') as customer_code,
                        COALESCE(pn.name, '') as customer_title,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(it.code, '') ELSE '' END as product_code,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(it.name, '') ELSE '' END as product_title,
                        COALESCE(rp.idperson, 0) as employee_id_1,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(pn.idsaleman, 0) as employee_id_2,
                        COALESCE(ps2.name, '') as employee_id_2_title,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.quantity, 0) ELSE 0 END as quantity,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.taxPrice, 0) ELSE 0 END as price_1,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(si_b.taxAmount, 0) ELSE 0 END as price_1_total,
                        COALESCE(rp_b.origCurrentAmount, 0) as payment_amount,
                        COALESCE(rp_b.ID, 0) as id_detail,
                        COALESCE(rp_b.voucherDetailID, 0) as id_detail_upstream,
                        COALESCE(rp_b.voucherCode, '') as order_number_upstream,
                        rp_b.idvouchertype as voucher_type,
                        CASE WHEN rp_b.idvouchertype = 20 THEN COALESCE(sd.priuserdefnvc4, '') ELSE '' END as is_kh
                    ")
                    ->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                //存货档案业务成本
                $product_map = Product::where('del_time',0)
                    ->whereIn('code', array_unique(array_column($dataArray,'product_code')))
                    ->pluck('business_cost','code')
                    ->all();

                //组织数据
                foreach ($dataArray as $key => $value){
                    $p_tmp = $product_map[$value['product_code']] ?? 0;
                    $dataArray[$key]['order_type'] = RevenueCost::ORDER_THREE;
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                    //业务成本单价和金额
                    $business_cost = $p_tmp ?? 0;
                    $dataArray[$key]['price_4'] = $business_cost;
                    $price_4_total = bcmul($business_cost, $value['quantity'],2);
                    $dataArray[$key]['price_4_total'] = $price_4_total;
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);

        }catch (\Throwable $exception){
            return [false, "收入同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function synSaleCost($data, $user){
        //创建临时表 如果不存在
        $this->createTmpTable5();
        //清理临时表 如果内容不为空
//        $this->clearTmpTable(5);

        try{
            $table = $this->table_5;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('CS_ExpenseVoucher_b as cs_b')
                    ->join('CS_ExpenseVoucher as cs', 'cs_b.idExpenseVoucherDTO', '=', 'cs.ID')
                    ->leftJoin('AA_ExpenseItem as ae', 'ae.id', '=', 'cs_b.idexpenseitem')
                    ->leftJoin('AA_Person as ps', 'cs.idclerk', '=', 'ps.ID')
                    ->where('cs.voucherdate','>=',$data['start_time'])
                    ->where('cs.voucherdate','<=',$data['end_time'])
                    ->where('cs_b.ID', '>', $lastId)
                    ->orderBy('cs_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        1 as order_type,
                        COALESCE(cs.ID, 0) as order_id,
                        COALESCE(cs.code, '') as order_number,
                        cs.voucherdate as order_time,
                        cs.idclerk  as employee_id_1,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(cs.pubuserdefnvc12, '') as channel_details,
                        COALESCE(cs.pubuserdefnvc11, '') as channel_finance,
                        COALESCE(ae.code, '') as fy_code,
                        COALESCE(cs_b.taxamount, 0) as taxamount,
                        COALESCE(cs_b.ID, 0) as id_detail
                    ")
                    ->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                foreach ($dataArray as $key => $value){
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);

        }catch (\Throwable $exception){
            return [false, "费用同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        try{
            $table = $this->table_5;
            $limit = 500;
            $lastId = 0;

            do {
                $rows = $this->databaseService->table('ST_RDRecord_b as st_b')
                    ->join('ST_RDRecord as st', 'st_b.idRDRecordDTO', '=', 'st.ID')
                    ->leftJoin('AA_Person as ps', 'st.idclerk', '=', 'ps.ID')
                    ->where('st.voucherdate','>=',$data['start_time'])
                    ->where('st.voucherdate','<=',$data['end_time'])
                    ->where('st.idvouchertype', 30)
//                    ->whereIn('st.idbusitype',[20048,60,20049]) //20049 客情出库 60 赠品出库  20048 员工福利
                    ->where('st_b.ID', '>', $lastId)
                    ->orderBy('st_b.ID')
                    ->limit($limit)
                    ->selectRaw("
                        2 as order_type,
                        COALESCE(st.ID, 0) as order_id,
                        COALESCE(st.code, '') as order_number,
                        st.voucherdate as order_time,
                        st.idclerk  as employee_id_1,
                        COALESCE(ps.name, '') as employee_id_1_title,
                        COALESCE(st.pubuserdefnvc12, '') as channel_details,
                        COALESCE(st.pubuserdefnvc11, '') as channel_finance,
                        COALESCE(st_b.amount, 0) as taxamount,
                        COALESCE(st_b.ID, 0) as id_detail,
                        st.idbusitype as ck_type
                    ")->get();

                if ($rows->isEmpty()) break;

                $dataArray = Collect($rows)->map(function ($object) {
                    return (array)$object;
                })->toArray();

                foreach ($dataArray as $key => $value){
                    $dataArray[$key]['order_time'] = strtotime($value['order_time']);
                }

                DB::table($table)->insert($dataArray);

                // 更新 lastId 继续下一批
                $lastId = end($dataArray)['id_detail'] ?? $lastId;

            } while (count($rows) === $limit);

        }catch (\Throwable $exception){
            return [false, "其他出库同步异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function saveResultold($data, $user){
        // 初始化汇总数组------报表一
        $paymentSummary = [];
        $costSummary = [];
        $costSummary2 = [];

        //报表二
        $paymentSummary_road = [];
        $costSummary_road = [];
        $costSummary2_road = [];

        $map = [
            2 => [
                'type' => 1,
                'channel_details' => ['通路'],
            ],
            3 =>  [
                'type' => 2,
                'channel_details' => ['通路'],
            ],
            23 =>  [
                'type' => 3,
                'channel_details' => ['通路'],
            ],
            7 => [
                'type' => 4,
                'channel_details' => ['通路','大卖场'],
            ],
            8 => [
                'type' => 4,
                'channel_details' => ['通路','大卖场'],
            ],
            48 => [
                'type' => 4,
                'channel_details' => ['通路','大卖场'],
            ],
        ];

        try{
            $limit = 500;
            $lastId = 0;
            do {
                // 分批查询：只查 is_kh 不为空的数据
                $rows = DB::table('tmp_item_road')
                    ->where('id', '>', $lastId)
                    ->orderBy('id')
                    ->limit($limit)
                    ->select([
                        'id',
                        'order_time',
                        'channel_details',
                        'payment_amount',
                        'price_4_total',
                        'is_kh',
                        'employee_id_2'
                    ])
                    ->get();

                if ($rows->isEmpty()) {
                    break;
                }

                // 聚合到汇总数组
                foreach ($rows as $row) {
                    $order_time = $row->order_time;
                    $month = strtotime(date("Y-m-01", $order_time));

                    $channel = $row->channel_details ?? '';

                    $t = $map[$row->employee_id_2] ?? [];
                    //收款销售收入
                    if (! $row->is_kh) {
                        if (!isset($paymentSummary[$month][$channel])) {
                            $paymentSummary[$month][$channel] = $row->payment_amount;
                        }else{
                            $paymentSummary[$month][$channel] = bcadd(
                                $paymentSummary[$month][$channel],
                                $row->payment_amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($paymentSummary_road[$month][$type])) {
                                    $paymentSummary_road[$month][$type] = $row->payment_amount;
                                }else{
                                    $paymentSummary_road[$month][$type] = bcadd(
                                        $paymentSummary_road[$month][$type],
                                        $row->payment_amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    //成本
                    if (!isset($costSummary[$month][$channel])) {
                        $costSummary[$month][$channel] = $row->price_4_total;
                    }else{
                        $costSummary[$month][$channel] = bcadd(
                            $costSummary[$month][$channel],
                            $row->price_4_total,
                            3
                        );
                    }

                    if(! empty($t)){
                        $type = $t['type'];
                        $c_t = $t['channel_details'];
                        if(in_array($channel, $c_t)){
                            if (!isset($costSummary_road[$month][$type])) {
                                $costSummary_road[$month][$type] = $row->price_4_total;
                            }else{
                                $costSummary_road[$month][$type] = bcadd(
                                    $costSummary_road[$month][$type],
                                    $row->price_4_total,
                                    3
                                );
                            }
                        }
                    }
                }

                // 更新 lastId
                $lastId = $rows->last()->id;

            } while ($rows->count() === $limit);

            $limit = 500;
            $lastId = 0;
            do {
                $rows = DB::table('tmp_item_cost')
                    ->where('id', '>', $lastId)
                    ->orderBy('id')
                    ->limit($limit)
                    ->select([
                        'id',
                        'order_time',
                        'channel_details',
                        'ck_type',
                        'fy_code',
                        'taxamount',
                        'employee_id_1'
                    ])
                    ->get();

                if ($rows->isEmpty()) {
                    break;
                }

                foreach ($rows as $row) {
                    $order_time = $row->order_time;
                    $month = strtotime(date("Y-m-01", $order_time));
                    $channel = $row->channel_details ?? '';
                    $amount = $row->taxamount;

                    $t = $map[$row->employee_id_1] ?? [];

                    if($row->fy_code == 7004){
                        if (! isset($costSummary2[$month][$channel]['settle_amount'])) {
                            $costSummary2[$month][$channel]['settle_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['settle_amount'] = bcadd(
                                $costSummary2[$month][$channel]['settle_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type])) {
                                    $costSummary2_road[$month][$type]['settle_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['settle_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['settle_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    $gl_amount = bcmul($amount, 0.05,3);
                    if (! isset($costSummary2[$month][$channel]['gl_amount'])) {
                        $costSummary2[$month][$channel]['gl_amount'] = $gl_amount;
                    }else{
                        $costSummary2[$month][$channel]['gl_amount'] = bcadd(
                            $costSummary2[$month][$channel]['gl_amount'],
                            $gl_amount,
                            3
                        );
                    }

                    if(! empty($t)){
                        $type = $t['type'];
                        $c_t = $t['channel_details'];
                        if(in_array($channel, $c_t)){
                            if (!isset($costSummary2_road[$month][$type]['gl_amount'])) {
                                $costSummary2_road[$month][$type]['gl_amount'] = $amount;
                            }else{
                                $costSummary2_road[$month][$type]['gl_amount'] = bcadd(
                                    $costSummary2_road[$month][$type]['gl_amount'],
                                    $amount,
                                    3
                                );
                            }
                        }
                    }

                    $wl_amount = bcmul($amount, 2.3,3);
                    if (! isset($costSummary2[$month][$channel]['wl_amount'])) {
                        $costSummary2[$month][$channel]['wl_amount'] = $wl_amount;
                    }else{
                        $costSummary2[$month][$channel]['wl_amount'] = bcadd(
                            $costSummary2[$month][$channel]['wl_amount'],
                            $wl_amount,
                            3
                        );
                    }

                    if(! empty($t)){
                        $type = $t['type'];
                        $c_t = $t['channel_details'];
                        if(in_array($channel, $c_t)){
                            if (!isset($costSummary2_road[$month][$type]['wl_amount'])) {
                                $costSummary2_road[$month][$type]['wl_amount'] = $amount;
                            }else{
                                $costSummary2_road[$month][$type]['wl_amount'] = bcadd(
                                    $costSummary2_road[$month][$type]['wl_amount'],
                                    $amount,
                                    3
                                );
                            }
                        }
                    }

                    if($row->fy_code == 7005){
                        if (! isset($costSummary2[$month][$channel]['ht_amount'])) {
                            $costSummary2[$month][$channel]['ht_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['ht_amount'] = bcadd(
                                $costSummary2[$month][$channel]['ht_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['ht_amount'])) {
                                    $costSummary2_road[$month][$type]['ht_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['ht_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['ht_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7006){
                        if (! isset($costSummary2[$month][$channel]['zk_amount'])) {
                            $costSummary2[$month][$channel]['zk_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['zk_amount'] = bcadd(
                                $costSummary2[$month][$channel]['zk_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['zk_amount'])) {
                                    $costSummary2_road[$month][$type]['zk_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['zk_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['zk_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7007){
                        if (! isset($costSummary2[$month][$channel]['cx_amount'])) {
                            $costSummary2[$month][$channel]['cx_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['cx_amount'] = bcadd(
                                $costSummary2[$month][$channel]['cx_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['cx_amount'])) {
                                    $costSummary2_road[$month][$type]['cx_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['cx_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['cx_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7013){
                        if (! isset($costSummary2[$month][$channel]['tg_amount'])) {
                            $costSummary2[$month][$channel]['tg_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['tg_amount'] = bcadd(
                                $costSummary2[$month][$channel]['tg_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['tg_amount'])) {
                                    $costSummary2_road[$month][$type]['tg_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['tg_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['tg_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7012){
                        if (! isset($costSummary2[$month][$channel]['cl_amount'])) {
                            $costSummary2[$month][$channel]['cl_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['cl_amount'] = bcadd(
                                $costSummary2[$month][$channel]['cl_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['cl_amount'])) {
                                    $costSummary2_road[$month][$type]['cl_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['cl_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['cl_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    //20049 客情出库 60 赠品出库  20048 员工福利
                    if($row->fy_code == 7008 || $row->ck_type == 20049){
                        if (! isset($costSummary2[$month][$channel]['kq_amount'])) {
                            $costSummary2[$month][$channel]['kq_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['kq_amount'] = bcadd(
                                $costSummary2[$month][$channel]['kq_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['kq_amount'])) {
                                    $costSummary2_road[$month][$type]['kq_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['kq_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['kq_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7009 || $row->ck_type == 60){
                        if (! isset($costSummary2[$month][$channel]['zp_amount'])) {
                            $costSummary2[$month][$channel]['zp_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['zp_amount'] = bcadd(
                                $costSummary2[$month][$channel]['zp_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['zp_amount'])) {
                                    $costSummary2_road[$month][$type]['zp_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['zp_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['zp_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7010){
                        if (! isset($costSummary2[$month][$channel]['gg_amount'])) {
                            $costSummary2[$month][$channel]['gg_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['gg_amount'] = bcadd(
                                $costSummary2[$month][$channel]['gg_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['gg_amount'])) {
                                    $costSummary2_road[$month][$type]['gg_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['gg_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['gg_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7011){
                        if (! isset($costSummary2[$month][$channel]['kd_amount'])) {
                            $costSummary2[$month][$channel]['kd_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['kd_amount'] = bcadd(
                                $costSummary2[$month][$channel]['kd_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['kd_amount'])) {
                                    $costSummary2_road[$month][$type]['kd_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['kd_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['kd_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7001){
                        if (! isset($costSummary2[$month][$channel]['xsqt_amount'])) {
                            $costSummary2[$month][$channel]['xsqt_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['xsqt_amount'] = bcadd(
                                $costSummary2[$month][$channel]['xsqt_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['xsqt_amount'])) {
                                    $costSummary2_road[$month][$type]['xsqt_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['xsqt_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['xsqt_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7003){
                        if (! isset($costSummary2[$month][$channel]['ry_amount'])) {
                            $costSummary2[$month][$channel]['ry_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['ry_amount'] = bcadd(
                                $costSummary2[$month][$channel]['ry_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['ry_amount'])) {
                                    $costSummary2_road[$month][$type]['ry_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['ry_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['ry_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    if($row->fy_code == 7019 || $row->ck_type == 20048){
                        if (! isset($costSummary2[$month][$channel]['sb_amount'])) {
                            $costSummary2[$month][$channel]['sb_amount'] = $amount;
                        }else{
                            $costSummary2[$month][$channel]['sb_amount'] = bcadd(
                                $costSummary2[$month][$channel]['sb_amount'],
                                $amount,
                                3
                            );
                        }

                        if(! empty($t)){
                            $type = $t['type'];
                            $c_t = $t['channel_details'];
                            if(in_array($channel, $c_t)){
                                if (!isset($costSummary2_road[$month][$type]['sb_amount'])) {
                                    $costSummary2_road[$month][$type]['sb_amount'] = $amount;
                                }else{
                                    $costSummary2_road[$month][$type]['sb_amount'] = bcadd(
                                        $costSummary2_road[$month][$type]['sb_amount'],
                                        $amount,
                                        3
                                    );
                                }
                            }
                        }
                    }

                    $sj_amount = bcmul($amount,0.01,3);
                    if (! isset($costSummary2[$month][$channel]['sj_amount'])) {
                        $costSummary2[$month][$channel]['sj_amount'] = $sj_amount;
                    }else{
                        $costSummary2[$month][$channel]['sj_amount'] = bcadd(
                            $costSummary2[$month][$channel]['sj_amount'],
                            $sj_amount,
                            3
                        );
                    }

                    if(! empty($t)){
                        $type = $t['type'];
                        $c_t = $t['channel_details'];
                        if(in_array($channel, $c_t)){
                            if (!isset($costSummary2_road[$month][$type]['sj_amount'])) {
                                $costSummary2_road[$month][$type]['sj_amount'] = $amount;
                            }else{
                                $costSummary2_road[$month][$type]['sj_amount'] = bcadd(
                                    $costSummary2_road[$month][$type]['sj_amount'],
                                    $amount,
                                    3
                                );
                            }
                        }
                    }
                }

                // 更新 lastId
                $lastId = $rows->last()->id;

            } while ($rows->count() === $limit);
        }catch (\Throwable $exception){
            return [false, "初步汇总异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        try {
            $start_timeStamp = $data['start_timeStamp'];
            $end_timeStamp = $data['end_timeStamp'];

            $result = $this->sumRes($paymentSummary,$costSummary,$costSummary2);
            $result2 = $this->sumRes2($paymentSummary_road, $costSummary_road, $costSummary2_road);

            DB::transaction(function () use ($start_timeStamp, $end_timeStamp, $result, $result2) {
                // 1. 先删除旧数据
                ItemReport::where('time', '>=', $start_timeStamp)
                    ->where('time', '<=', $end_timeStamp)
                    ->delete();
                ItemReportRoad::where('time', '>=', $start_timeStamp)
                    ->where('time', '<=', $end_timeStamp)
                    ->delete();

                // 2. 插入新数据
                ItemReport::insert($result);
                ItemReportRoad::insert($result2);

            });
        }catch (\Throwable $exception){
            return [false, "结果异常" . $exception->getMessage() . "|" . $exception->getLine() . "|" . $exception->getFile()];
        }

        return [true, ''];
    }

    private function saveResult($data, $user){
        // 初始化汇总数组------报表一
        $paymentSummary = [];
        $costSummary = [];
        $costSummary2 = [];

        // 初始化汇总数组------报表二 (业务员报表)
        $manSummary = [];

        try{
            $limit = 500;
            $lastId = 0;
            do {
                //收入
                $rows = DB::table('tmp_item_road')
                    ->where('id', '>', $lastId)
                    ->orderBy('id')
                    ->limit($limit)
                    ->select([
                        'id',
                        'order_time',
                        'channel_details',
                        'channel_finance',
                        'payment_amount',
                        'price_4_total',
                        'is_kh',
                        'employee_id_1',
                        'employee_id_1_title',
                        'employee_id_2'
                    ])
                    ->get();

                if ($rows->isEmpty()) {
                    break;
                }

                foreach ($rows as $row) {
                    $order_time = $row->order_time;
                    $month = strtotime(date("Y-m-01", $order_time));
                    $channel = $row->channel_details ?? '';
                    $finance = $row->channel_finance ?? '';
                    $empId = $row->employee_id_1 ?? 0;
                    $empName = $row->employee_id_1_title ?? '';

                    // --- 原报表一逻辑 (不改动) ---
                    if (! $row->is_kh) {
                        $paymentSummary[$month][$channel] = bcadd($paymentSummary[$month][$channel] ?? 0, $row->payment_amount, 3);
                    }
                    $costSummary[$month][$channel] = bcadd($costSummary[$month][$channel] ?? 0, $row->price_4_total, 3);

                    // --- 新报表二逻辑 (业务员维度) ---
                    $key = "{$month}_{$finance}_{$empId}";
                    if (!isset($manSummary[$key])) {
                        $manSummary[$key] = [
                            'time'                => $month,
                            'channel_finance'     => $finance,
                            'employee_id_1'       => $empId,
                            'employee_id_1_title' => $empName,
                            'receipt_amount'      => 0,
                            'receipt_not_amount'  => 0,
                            'cost'                => 0,
                            'profit'              => 0,
                            'profit_not'          => 0,
                            'settle_amount'       => 0,
                            'gl_amount'           => 0,
                            'gl_not_amount'       => 0,
                            'wl_amount'           => 0,
                            'wl_not_amount'       => 0,
                            'ht_amount'           => 0,
                            'zk_amount'           => 0,
                            'cx_amount'           => 0,
                            'tg_amount'           => 0,
                            'cl_amount'           => 0,
                            'kq_amount'           => 0,
                            'zp_amount'           => 0,
                            'gg_amount'           => 0,
                            'kd_amount'           => 0,
                            'xsqt_amount'         => 0,
                            'ry_amount'           => 0,
                            'sb_amount'           => 0,
                            'sj_amount'           => 0,
                            'sj_not_amount'       => 0,
                            'sx_amount'           => 0,
                            'sx_not_amount'       => 0,
                            'cg_amount'           => 0,
                            'other_ck_amount'     => 0,
                        ];
                    }
                    // 收款总额 (所有)
                    $manSummary[$key]['receipt_amount'] = bcadd($manSummary[$key]['receipt_amount'], $row->payment_amount, 3);
                    // 收款 (不含不计考勤 is_kh == 0)
                    if (!$row->is_kh) {
                        $manSummary[$key]['receipt_not_amount'] = bcadd($manSummary[$key]['receipt_not_amount'], $row->payment_amount, 3);
                    }
                    // 业务成本
                    $manSummary[$key]['cost'] = bcadd($manSummary[$key]['cost'], $row->price_4_total, 3);
                }
                $lastId = $rows->last()->id;
            } while ($rows->count() === $limit);

            // 计算毛利
            foreach ($manSummary as $key => $val) {
                // 1. 计算总毛利：总收款 - 总成本
                $manSummary[$key]['profit'] = bcsub(
                    $val['receipt_amount'],
                    $val['cost'],
                    3
                );

                // 2. 计算不含不计考勤毛利：考核收款 - 总成本
                $manSummary[$key]['profit_not'] = bcsub(
                    $val['receipt_not_amount'],
                    $val['cost'],
                    3
                );
            }

            $limit = 500;
            $lastId = 0;
            do {
                //指出
                $rows = DB::table('tmp_item_cost')
                    ->where('id', '>', $lastId)
                    ->orderBy('id')
                    ->limit($limit)
                    ->select([
                        'id', 'order_time', 'channel_details', 'ck_type', 'fy_code', 'taxamount', 'employee_id_1', 'channel_finance', 'order_type' , 'employee_id_1_title'
                    ])
                    ->get();

                if ($rows->isEmpty()) {
                    break;
                }

                foreach ($rows as $row) {
                    $order_time = $row->order_time;
                    $month = strtotime(date("Y-m-01", $order_time));
                    $channel = $row->channel_details ?? '';
                    $finance = $row->channel_finance ?? '';
                    $empId = $row->employee_id_1 ?? 0;
                    $empName = $row->employee_id_1_title ?? '';
                    $amount = $row->taxamount;

                    //表一-------------------------------------------------------
                    // 结算费用
                    if($row->fy_code == 7004){
                        $costSummary2[$month][$channel]['settle_amount'] = bcadd($costSummary2[$month][$channel]['settle_amount'] ?? 0, $amount, 3);
                    }
                    // 管理费 (5%)
                    $gl_amount = bcmul($amount, 0.05, 3);
                    $costSummary2[$month][$channel]['gl_amount'] = bcadd($costSummary2[$month][$channel]['gl_amount'] ?? 0, $gl_amount, 3);
                    // 物流费 (2.3%)
                    $wl_amount = bcmul($amount, 0.023, 3);
                    $costSummary2[$month][$channel]['wl_amount'] = bcadd($costSummary2[$month][$channel]['wl_amount'] ?? 0, $wl_amount, 3);
                    if($row->fy_code == 7005) $costSummary2[$month][$channel]['ht_amount'] = bcadd($costSummary2[$month][$channel]['ht_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7006) $costSummary2[$month][$channel]['zk_amount'] = bcadd($costSummary2[$month][$channel]['zk_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7007) $costSummary2[$month][$channel]['cx_amount'] = bcadd($costSummary2[$month][$channel]['cx_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7013) $costSummary2[$month][$channel]['tg_amount'] = bcadd($costSummary2[$month][$channel]['tg_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7012) $costSummary2[$month][$channel]['cl_amount'] = bcadd($costSummary2[$month][$channel]['cl_amount'] ?? 0, $amount, 3);
                    // 客情出库
                    if($row->fy_code == 7008 || $row->ck_type == 20049) {
                        $costSummary2[$month][$channel]['kq_amount'] = bcadd($costSummary2[$month][$channel]['kq_amount'] ?? 0, $amount, 3);
                    }
                    // 赠品出库
                    if($row->fy_code == 7009 || $row->ck_type == 60) {
                        $costSummary2[$month][$channel]['zp_amount'] = bcadd($costSummary2[$month][$channel]['zp_amount'] ?? 0, $amount, 3);
                    }
                    if($row->fy_code == 7010) $costSummary2[$month][$channel]['gg_amount'] = bcadd($costSummary2[$month][$channel]['gg_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7011) $costSummary2[$month][$channel]['kd_amount'] = bcadd($costSummary2[$month][$channel]['kd_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7001) $costSummary2[$month][$channel]['xsqt_amount'] = bcadd($costSummary2[$month][$channel]['xsqt_amount'] ?? 0, $amount, 3);
                    if($row->fy_code == 7003) $costSummary2[$month][$channel]['ry_amount'] = bcadd($costSummary2[$month][$channel]['ry_amount'] ?? 0, $amount, 3);
                    // 社保/福利
                    if($row->fy_code == 7019 || $row->ck_type == 20048) {
                        $costSummary2[$month][$channel]['sb_amount'] = bcadd($costSummary2[$month][$channel]['sb_amount'] ?? 0, $amount, 3);
                    }
                    // 税金 (1%)
                    $sj_amount = bcmul($amount, 0.01, 3);
                    $costSummary2[$month][$channel]['sj_amount'] = bcadd($costSummary2[$month][$channel]['sj_amount'] ?? 0, $sj_amount, 3);


                    // 表二----------------------------------------------------------------------
                    $key = "{$month}_{$finance}_{$empId}";
                    if (!isset($manSummary[$key])) {
                        $manSummary[$key] = [
                            'time'                => $month,
                            'channel_finance'     => $finance,
                            'employee_id_1'       => $empId,
                            'employee_id_1_title' => $empName,
                            'receipt_amount'      => 0,
                            'receipt_not_amount'  => 0,
                            'cost'                => 0,
                            'profit'              => 0,
                            'profit_not'          => 0,
                            'settle_amount'       => 0,
                            'gl_amount'           => 0,
                            'gl_not_amount'       => 0,
                            'wl_amount'           => 0,
                            'wl_not_amount'       => 0,
                            'ht_amount'           => 0,
                            'zk_amount'           => 0,
                            'cx_amount'           => 0,
                            'tg_amount'           => 0,
                            'cl_amount'           => 0,
                            'kq_amount'           => 0,
                            'zp_amount'           => 0,
                            'gg_amount'           => 0,
                            'kd_amount'           => 0,
                            'xsqt_amount'         => 0,
                            'ry_amount'           => 0,
                            'sb_amount'           => 0,
                            'sj_amount'           => 0,
                            'sj_not_amount'       => 0,
                            'sx_amount'           => 0,
                            'sx_not_amount'       => 0,
                            'cg_amount'           => 0,
                            'other_ck_amount'     => 0,
                        ];
                    }
                    if ($row->order_type == 2) {
                        // 1. 判断是否为其他出库
                        $manSummary[$key]['other_ck_amount'] = bcadd($manSummary[$key]['other_ck_amount'] ?? 0, $amount, 3);
                    } else {
                        // 2. 正常费用代码归类
                        if($row->fy_code == 7004) $manSummary[$key]['settle_amount'] = bcadd($manSummary[$key]['settle_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7005) $manSummary[$key]['ht_amount'] = bcadd($manSummary[$key]['ht_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7006) $manSummary[$key]['zk_amount'] = bcadd($manSummary[$key]['zk_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7007) $manSummary[$key]['cx_amount'] = bcadd($manSummary[$key]['cx_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7013) $manSummary[$key]['tg_amount'] = bcadd($manSummary[$key]['tg_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7012) $manSummary[$key]['cl_amount'] = bcadd($manSummary[$key]['cl_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7008) $manSummary[$key]['kq_amount'] = bcadd($manSummary[$key]['kq_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7009) $manSummary[$key]['zp_amount'] = bcadd($manSummary[$key]['zp_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7010) $manSummary[$key]['gg_amount'] = bcadd($manSummary[$key]['gg_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7011) $manSummary[$key]['kd_amount'] = bcadd($manSummary[$key]['kd_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7001) $manSummary[$key]['xsqt_amount'] = bcadd($manSummary[$key]['xsqt_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7003) $manSummary[$key]['ry_amount'] = bcadd($manSummary[$key]['ry_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7019) $manSummary[$key]['sb_amount'] = bcadd($manSummary[$key]['sb_amount'] ?? 0, $amount, 3);
                        if($row->fy_code == 7023) $manSummary[$key]['cg_amount'] = bcadd($manSummary[$key]['cg_amount'] ?? 0, $amount, 3);
                    }
                }
                $lastId = $rows->last()->id;
            } while ($rows->count() === $limit);

        } catch (\Throwable $exception){
            return [false, "初步汇总异常" . $exception->getMessage() . "|" . $exception->getLine()];
        }
        foreach ($manSummary as $key => $item) {
            $r_amount = $item['receipt_amount'];
            $r_not_amount = $item['receipt_not_amount'];

            // 1. 计算毛利
            $manSummary[$key]['profit'] = bcsub($r_amount, $item['cost'], 3);
            $manSummary[$key]['profit_not'] = bcsub($r_not_amount, $item['cost'], 3);

            // 2. 统一计算比例费用（此时 $r_amount 是汇总后的总额，只算一次，绝对准确）
            $manSummary[$key]['gl_amount'] = bcmul($r_amount, 0.05, 3);
            $manSummary[$key]['gl_not_amount'] = bcmul($r_not_amount, 0.05, 3);

            $manSummary[$key]['wl_amount'] = bcmul($r_amount, 0.023, 3);
            $manSummary[$key]['wl_not_amount'] = bcmul($r_not_amount, 0.023, 3);

            $manSummary[$key]['sj_amount'] = bcmul($r_amount, 0.01, 3);
            $manSummary[$key]['sj_not_amount'] = bcmul($r_not_amount, 0.01, 3);

            // 补齐字段，防止 DB 报错
            $manSummary[$key]['sx_amount'] = bcmul($r_amount, 0.01, 3);
            $manSummary[$key]['sx_not_amount'] = bcmul($r_not_amount, 0.01, 3);
        }

        try {
            $start_timeStamp = $data['start_timeStamp'];
            $end_timeStamp = $data['end_timeStamp'];

            $result = $this->sumRes($paymentSummary, $costSummary, $costSummary2);
            $manSummary = array_values($manSummary);

            DB::transaction(function () use ($start_timeStamp, $end_timeStamp, $result, $manSummary) {
                ItemReport::where('time', '>=', $start_timeStamp)
                    ->where('time', '<=', $end_timeStamp)
                    ->delete();

                ItemReport::insert($result);

                ItemReportMan::where('time', '>=', $start_timeStamp)
                    ->where('time', '<=', $end_timeStamp)
                    ->delete();

                ItemReportMan::insert($manSummary);
            });
        } catch (\Throwable $exception){
            return [false, "结果异常" . $exception->getMessage() . "|" . $exception->getLine()];
        }

        return [true, ''];
    }

    private function sumRes($paymentSummary, $costSummary, $costSummary2){
        // 合并三个汇总数组为最终结果
        $result = [];

        // 获取所有唯一的时间+渠道组合
        $keys = [];
        foreach (array_keys($paymentSummary) as $month) {
            foreach (array_keys($paymentSummary[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }
        foreach (array_keys($costSummary) as $month) {
            foreach (array_keys($costSummary[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }
        foreach (array_keys($costSummary2) as $month) {
            foreach (array_keys($costSummary2[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }

        // 遍历所有组合，构建最终数据
        foreach ($keys as $key => $info) {
            $month = $info['month'];
            $channel = $info['channel'];

            // 收款销售收入（注意：原逻辑中 is_kh == false 才计入）
            $receipt_amount = isset($paymentSummary[$month][$channel])
                ? $paymentSummary[$month][$channel]
                : '0.000';

            // 业务成本（来自 tmp_item_road 的 price_4_total）
            $cost = isset($costSummary[$month][$channel])
                ? $costSummary[$month][$channel]
                : '0.000';

            // 利润 = 收入 - 成本
            $profit = bcsub($receipt_amount, $cost, 3);

            // 从 costSummary2 提取各项费用（默认为 0.000）
            $cs2 = $costSummary2[$month][$channel] ?? [];

            $settle_amount = $cs2['settle_amount'] ?? '0.000';
            $gl_amount     = $cs2['gl_amount']     ?? '0.000';
            $wl_amount     = $cs2['wl_amount']     ?? '0.000';
            $ht_amount     = $cs2['ht_amount']     ?? '0.000';
            $zk_amount     = $cs2['zk_amount']     ?? '0.000';
            $cx_amount     = $cs2['cx_amount']     ?? '0.000';
            $tg_amount     = $cs2['tg_amount']     ?? '0.000';
            $cl_amount     = $cs2['cl_amount']     ?? '0.000';
            $kq_amount     = $cs2['kq_amount']     ?? '0.000';
            $zp_amount     = $cs2['zp_amount']     ?? '0.000';
            $gg_amount     = $cs2['gg_amount']     ?? '0.000';
            $kd_amount     = $cs2['kd_amount']     ?? '0.000';
            $xsqt_amount   = $cs2['xsqt_amount']   ?? '0.000';
            $ry_amount     = $cs2['ry_amount']     ?? '0.000';
            $sb_amount     = $cs2['sb_amount']     ?? '0.000';
            $sj_amount     = $cs2['sj_amount']     ?? '0.000';

            $result[] = [
                'time'           => $month,
                'channel_details'=> $channel,
                'receipt_amount' => $receipt_amount,
                'cost'           => $cost,
                'profit'         => $profit,
                'settle_amount'  => $settle_amount,
                'gl_amount'      => $gl_amount,
                'wl_amount'      => $wl_amount,
                'ht_amount'      => $ht_amount,
                'zk_amount'      => $zk_amount,
                'cx_amount'      => $cx_amount,
                'tg_amount'      => $tg_amount,
                'cl_amount'      => $cl_amount,
                'kq_amount'      => $kq_amount,
                'zp_amount'      => $zp_amount,
                'gg_amount'      => $gg_amount,
                'kd_amount'      => $kd_amount,
                'xsqt_amount'    => $xsqt_amount,
                'ry_amount'      => $ry_amount,
                'sb_amount'      => $sb_amount,
                'sj_amount'      => $sj_amount,
            ];
        }

        return $result;
    }

    private function sumRes2($paymentSummary, $costSummary, $costSummary2){
        // 合并三个汇总数组为最终结果
        $result = [];

        // 获取所有唯一的时间+渠道组合
        $keys = [];
        foreach (array_keys($paymentSummary) as $month) {
            foreach (array_keys($paymentSummary[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }
        foreach (array_keys($costSummary) as $month) {
            foreach (array_keys($costSummary[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }
        foreach (array_keys($costSummary2) as $month) {
            foreach (array_keys($costSummary2[$month]) as $channel) {
                $keys["$month|$channel"] = ['month' => $month, 'channel' => $channel];
            }
        }

        // 遍历所有组合，构建最终数据
        foreach ($keys as $key => $info) {
            $month = $info['month'];
            $channel = $info['channel'];

            // 收款销售收入
            $receipt_amount = isset($paymentSummary[$month][$channel])
                ? $paymentSummary[$month][$channel]
                : '0.000';

            // 业务成本（来自 tmp_item_road 的 price_4_total）
            $cost = isset($costSummary[$month][$channel])
                ? $costSummary[$month][$channel]
                : '0.000';

            // 利润 = 收入 - 成本
            $profit = bcsub($receipt_amount, $cost, 3);

            // 从 costSummary2 提取各项费用（默认为 0.000）
            $cs2 = $costSummary2[$month][$channel] ?? [];

            $settle_amount = $cs2['settle_amount'] ?? '0.000';
            $gl_amount     = $cs2['gl_amount']     ?? '0.000';
            $wl_amount     = $cs2['wl_amount']     ?? '0.000';
            $ht_amount     = $cs2['ht_amount']     ?? '0.000';
            $zk_amount     = $cs2['zk_amount']     ?? '0.000';
            $cx_amount     = $cs2['cx_amount']     ?? '0.000';
            $tg_amount     = $cs2['tg_amount']     ?? '0.000';
            $cl_amount     = $cs2['cl_amount']     ?? '0.000';
            $kq_amount     = $cs2['kq_amount']     ?? '0.000';
            $zp_amount     = $cs2['zp_amount']     ?? '0.000';
            $gg_amount     = $cs2['gg_amount']     ?? '0.000';
            $kd_amount     = $cs2['kd_amount']     ?? '0.000';
            $xsqt_amount   = $cs2['xsqt_amount']   ?? '0.000';
            $ry_amount     = $cs2['ry_amount']     ?? '0.000';
            $sb_amount     = $cs2['sb_amount']     ?? '0.000';
            $sj_amount     = $cs2['sj_amount']     ?? '0.000';

            $result[] = [
                'time'           => $month,
                'receipt_amount' => $receipt_amount,
                'cost'           => $cost,
                'profit'         => $profit,
                'settle_amount'  => $settle_amount,
                'gl_amount'      => $gl_amount,
                'wl_amount'      => $wl_amount,
                'ht_amount'      => $ht_amount,
                'zk_amount'      => $zk_amount,
                'cx_amount'      => $cx_amount,
                'tg_amount'      => $tg_amount,
                'cl_amount'      => $cl_amount,
                'kq_amount'      => $kq_amount,
                'zp_amount'      => $zp_amount,
                'gg_amount'      => $gg_amount,
                'kd_amount'      => $kd_amount,
                'xsqt_amount'    => $xsqt_amount,
                'ry_amount'      => $ry_amount,
                'sb_amount'      => $sb_amount,
                'sj_amount'      => $sj_amount,
                'employee_title_type'  => $channel
            ];
        }

        return $result;
    }
}