playlet/app/Console/Commands/CustomerTagF...

<?php

namespace App\Console\Commands;

use App\RedisModel;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CustomerTagFlush extends Command
{
    protected $signature = 'CustomerTagFlush';

    protected $description = '客户标签洗入客户详情表';

    private $startId = 0;
    private $pageSize = 1000;
    private $redisKey = 'Playlet::CustomerTagFlush-';

    public function handle()
    {
        \DB::connection()->disableQueryLog();
        $this->info(date('m-d H:i:s') . ' 开始整理');

        for($i=0; $i<50; $i++) {
            $start = RedisModel::get($this->redisKey.$i);
            if(empty($start)) {
                $this->startId = 0;
                RedisModel::set($this->redisKey.$i, 0);
                RedisModel::expire($this->redisKey.$i, 86400);
            } else {
                $this->startId = $start;
            }

            try{
                $this->flush($i);
                sleep(1);
            } catch (\Exception $exception) {
                $this->info('错误信息:'.$exception->getLine().', '.$exception->getMessage());
            }
        }

        $this->info(date('m-d H:i:s') . ' 整理结束');
    }

    private function flush($label)
    {
        $tagTable = 'dj_customer_tag_'.$label;
        $relationTable = 'dj_customer_relation_'.$label;
        $customerTable = 'dj_customer_'.$label;
        $customerDetail = 'dj_customer_details_'.$label;

        $this->info('当前处理的分表标识为：'. $label);
        do{
            # 获取外部联系人关系信息
            $customerRelationList = DB::table($relationTable)->select(['id', 'corpid', 'user_id', 'customer_id',
                'external_userid', 'remark', 'description', 'createtime', 'remark_corp_name', 'remark_mobiles',
                'add_way', 'wechat_channels', 'oper_userid', 'source', 'state', 'enable', 'loss_time', 'blacklist_status'])
                ->where('id', '>', $this->startId)->orderBy('id', 'asc')->limit($this->pageSize)->get();
            $count = $customerRelationList->count();
            $this->info('本次起始的ID：'.$this->startId);
            $this->info('本次共处理的消息条数'.$count);
            $customerIds = $customerRelationList->pluck('customer_id');

            # 获取外部联系人列表
            $customerList = DB::table($customerTable)->select(['id', 'corpid', 'external_userid', 'name', 'pay_num', 'gender'])
                ->whereIn('id', $customerIds)->get();

            # 获取对应外部联系人标签信息
            $customerTagList = DB::table($tagTable)->select(['tag_id', 'corpid', 'user_id', 'customer_id', 'external_userid'])
                ->where('enable', 1)->whereIn('customer_id', $customerIds)->get();

            $updateData = [];

            foreach($customerRelationList as $k => $customer) {
                $customerInfo = $customerList->where('id', $customer->customer_id)->first();

                $tagList = $customerTagList->where('customer_id', $customer->customer_id)->pluck('tag_id')->toArray();

                $newTagList = [];
                foreach($tagList as $tagId){
                    $newTagList[] = md5($tagId);
                }

                $item = json_decode(json_encode($customer), 1);
                $item['name'] = $customerInfo->name;
                $item['gender'] = $customerInfo->gender;
                $item['is_paid'] = $customerInfo->pay_num ? 1 : 0;
                $item['tag_list'] = empty($newTagList) ? '' : implode(',', array_unique($newTagList));
                $item['con_user_cus'] = $customer->user_id.$customer->external_userid;

//                $updateData[$k] = $item;

            DB::table($customerDetail)
                ->updateOrInsert([
                    'corpid' => $item['corpid'],
                    'user_id' => $item['user_id'],
                    'external_userid' => $item['external_userid'],
                ], $item);

                $this->startId = $customerRelationList->max('id');
                RedisModel::set($this->redisKey.$label, $this->startId);
            }

//            DB::table($customerDetail)
//                ->insert($updateData);
        } while ($this->pageSize == $count);
    }
}