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\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";

    /**
     * 同步人员部门
     *
     * @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->isOverThreeMonths($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) {
//                $this->clearTmpTable();
//                $this->dellimitingSendRequest($this->table);
//                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,
                        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,
                        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,
                        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.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,
                        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();

                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,
                            '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->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(){
        if (Schema::hasTable($this->table)) DB::table($this->table)->truncate();
    }

    public function delTableKey($type = 1){
        $key = $this->table;
        if($type == 2) $key = $this->table_2;
        $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->isOverThreeMonths($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();

        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->clearTmpTable2();

        //写入临时表
        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->clearTmpTable2();
        //释放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);
            });
        }
    }

    public function clearTmpTable2(){
        if (Schema::hasTable($this->table_2)) DB::table($this->table_2)->truncate();
    }

    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, ''];
    }
}