t_nine/app/Console/Commands/CustomerFromThreePlatForm.php

<?php

namespace App\Console\Commands;

use App\Model\BasicType;
use App\Model\Customer;
use App\Model\CustomerAccessFlat;
use App\Model\CustomerInfo;
use App\Model\CustomerRemain;
use App\Model\DepartWithDHF;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CustomerFromThreePlatForm extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:customer_from_three_plat_form';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $basic = BasicType::where('del_time',0)
            ->where('type',BasicType::type_10)
            ->select('id','title')
            ->get()->toArray();
        $dhf = DepartWithDHF::from('depart_with_dhf as a')
            ->join('depart as b','b.id','a.top_depart_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->select('a.top_depart_id','a.clueSourceId','b.notify_id')
            ->get()->toArray();
        $dhf_map = array_column($dhf,null,'clueSourceId');

        \App\Model\CustomerFromThreePlatForm::where("del_time",0)
            ->where("customerName","<>","")
            ->orderBy('id')
            ->select('id','crt_time','city','actionType','acquireTime','clueType','product','wechat','clueId','transformType','clueSource','clueSourceId','customerName','platform','ipLocationCity','phone','isRepeatClue','flowType','fromUserId')
            ->chunkById(20, function ($data) use($basic,$dhf_map){
                // 开启事务
                DB::transaction(function () use ($data,$basic,$dhf_map) {
                    $dataArray = $data->toArray();
                    $nowStamp = time();
                    list($main, $main_detail, $fail, $main_detail2, $main_detail3) = $this->processingData($dataArray, $nowStamp,$basic,$dhf_map);
                    $c_third_platform_id = array_column($main,'c_third_platform_id');

                    if (! empty($main) ) {
                        Customer::insert($main);

                        $map = Customer::whereIn('c_third_platform_id',$c_third_platform_id)
                            ->pluck('id','c_third_platform_id')
                            ->toArray();
                        $insert_detail = [];
                        foreach ($main_detail as $id => $value){
                            if(isset($map[$id])){
                                foreach ($value as $v){
                                    $v['customer_id'] = $map[$id];
                                    $insert_detail[] = $v;
                                }
                            }
                        }
                        if(! empty($insert_detail)) CustomerInfo::insert($insert_detail);

                        if(! empty($main_detail2)){
                            $insert_detail_2 = [];
                            foreach ($main_detail2 as $id => $value){
                                if(isset($map[$id])){
                                    foreach ($value as $v){
                                        $v['customer_id'] = $map[$id];
                                        $insert_detail_2[] = $v;
                                    }
                                }
                            }
                            if(! empty($insert_detail_2)) {
                                CustomerInfo::insert($insert_detail_2);

                                // 新增
                                $insertArray = CustomerInfo::whereIn('customer_id', array_values($map))
                                    ->where('crt_time', $nowStamp)
                                    ->whereIn('type', [CustomerInfo::type_two])
                                    ->select('id as source_id',DB::raw(CustomerAccessFlat::source_type_one .' as source_type'), 'customer_id','data_id as target_id',DB::raw(CustomerAccessFlat::type_one .' as type'),DB::raw('2 as top_depart_id'))
                                    ->get()
                                    ->toArray();
                                if(! empty($insertArray)) CustomerAccessFlat::insert($insertArray);
                            }
                        }

                        if(! empty($main_detail3)){
                            $insert_detail_3 = [];
                            foreach ($main_detail3 as $id => $value){
                                if(isset($map[$id])){
                                    foreach ($value as $v){
                                        $v['customer_id'] = $map[$id];
                                        $insert_detail_3[] = $v;
                                    }
                                }
                            }
                            if(! empty($insert_detail_3)) CustomerRemain::insert($insert_detail_3);
                        }

                        echo '更新完成' . PHP_EOL;
                    }

                    if(! empty($fail)) {
                        foreach ($fail as $update){
                            $customer_id = $map[$update['id']] ?? 0;
                            \App\Model\CustomerFromThreePlatForm::where('id', $update['id'])
                                ->update(['del_time' => $update['result'] , 'customer_id' => $customer_id]);
                        }
                    }
                });
            });
    }

    private function processingData($data, $time,$basic,$dhf_map)
    {
        $return = []; // 客户主表
        $return_detail = []; // 客户联系方式
        $return_detail2 = []; // 客户负责人
        $return_detail3 = []; // 客户负责人提醒
        $fail   = []; // 失败数据（含 result 标记）
        $basic_map = array_column($basic,'title','id');

        $info_phone = [];
        $info_wechat = [];
        $search = [];

        // 第一步：同批次去重（保留最后一次 ID）
        foreach ($data as $value) {
            if (!empty($value['phone'])) {
                $info_phone[$value['phone']] = $value['id'];
                $search[] = $value['phone'];
            }
            if (!empty($value['wechat'])) {
                $info_wechat[$value['wechat']] = $value['id'];
                $search[] = $value['wechat'];
            }
        }

        // 第二步：数据库中已存在的联系方式
//        $contact = CustomerInfo::from('customer_info as a')
//            ->join('customer as b', 'b.id', 'a.customer_id')
//            ->where('a.del_time', 0)
//            ->where('b.del_time', 0)
//            ->where('b.top_depart_id', 2)
//            ->whereIn('a.contact_info', $search)
//            ->pluck('a.contact_info')
//            ->toArray();
//
//        $contact = array_flip($contact); // 方便 in_array 变成 isset 检查

        // 第三步：逐条判断
        foreach ($data as $value) {
            $id = $value['id'];
            $isFail = false;

            // 检查手机号
            if (!empty($value['phone'])) {
                if (isset($contact[$value['phone']])) {
                    // 数据库已存在
                    $fail[] = ['id' => $id, 'result' => 1];
                    $isFail = true;
                } elseif (($info_phone[$value['phone']] ?? 0) !== $id) {
                    // 同批重复
                    $fail[] = ['id' => $id, 'result' => 2];
                    $isFail = true;
                }
            }

            // 检查微信号（如果之前手机号已 fail，这里也可以继续检查，因为两者可能不同）
            if (!$isFail && !empty($value['wechat'])) {
                if (isset($contact[$value['wechat']])) {
                    $fail[] = ['id' => $id, 'result' => 1];
                    $isFail = true;
                } elseif (($info_wechat[$value['wechat']] ?? 0) !== $id) {
                    $fail[] = ['id' => $id, 'result' => 2];
                    $isFail = true;
                }
            }

            // 成功数据
            if (! $isFail) {
                $fail[] = ['id' => $id, 'result' => 3];

                $car_type = 0;
                if(! empty($value['product'])){
                    $t = explode('/', $value['product']);
                    foreach ($t as $t_v){
                        if($car_type) continue;
                        if(isset($basic_map[$t_v])) $car_type = $basic_map[$t_v];
                    }
                }
                $customer_from = 0;
                if($value['platform'] == 1){
                    $customer_from = 17190;//抖音DY
                }elseif($value['platform'] == 2){
                    $customer_from = 17202;//快手KS
                }elseif ($value['platform'] == 5){
                    $customer_from = 17195;//公众号GZH
                }
                $enter_time = $time;
                if(! empty($value['acquireTime'])) $enter_time = bcdiv($value['acquireTime'],1000);
                $return[] = [
                    'c_third_platform_id' => $id,
                    'title' => $value['customerName'],
                    'model_type' => Customer::Model_type_one,
                    'progress_stage' => 9315, //A后台私信
                    'car_type' => $car_type,
                    'customer_from' => $customer_from,
                    'address2' => ! empty($value['city']) ? $value['city'] : $value['ipLocationCity'],
                    'depart_id' => 2,
                    'top_depart_id' => 2,
                    'crt_id' => 1,
                    'crt_time' => $time,
                    'upd_time' => $time,
                    'enter_time' => $enter_time,
                ];

                if(! empty($value['phone'])){
                    $return_detail[$id][] = [
                        'customer_id' => 0,
                        'contact_type' => 133,
                        'contact_info' => $value['phone'],
                        'crt_time' => $time,
                        'type' => CustomerInfo::type_one
                    ];
                }
                if(! empty($value['wechat'])){
                    $return_detail[$id][] = [
                        'customer_id' => 0,
                        'contact_type' => 135,
                        'contact_info' => $value['wechat'],
                        'crt_time' => $time,
                        'type' => CustomerInfo::type_one
                    ];
                }
                $setting = $dhf_map[$value['clueSourceId']] ?? [];
                if(! empty($setting['notify_id']) && ! empty($setting['top_depart_id'])){
                    $return_detail2[$id][] = [
                        'customer_id' => 0,
                        'data_id' => $setting['notify_id'],
                        'crt_time' => $time,
                        'type' => CustomerInfo::type_two
                    ];
                    $return_detail3[$id][] = [
                        'customer_id' => 0,
                        'fp' => $setting['top_depart_id'],
                        'fz' => $setting['notify_id'],
                        'crt_time' => $time,
                    ];
                }
            }
        }

        return [$return, $return_detail, $fail, $return_detail2, $return_detail3];
    }

}