playlet/app/Console/Repair/CustomerAssi...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2023/1/3
 * Time: 16:55
 */

namespace App\Console\Repair;


use App\Log;
use App\Models\BatchTransferCustomer;
use App\Models\CustomerAssignmentTotal;
use App\Models\CustomerDetails;
use App\Models\DjUser;
use App\RedisModel;
use App\Support\EmailQueue;
use Illuminate\Console\Command;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '客户迁移历史数据处理';

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

    public function handle()
    {
        # 需要处理的迁移列表
        $assignmentData = BatchTransferCustomer::query()
            ->where('create_time', '>=', '2022-12-01')
            ->where('enable', 1)
            ->orderBy('id', 'asc')
            ->get();

        $this->info(date('H:i') . ' 开始执行, 待处理的数据有'.$assignmentData->count());

        foreach($assignmentData as $data) {

            // 判断迁移类型并分别处理
            if(1 == $data['type']) {
                $this->customerAssignmentOnJob($data);
            } else {
                $this->customerAssignmentQuit($data);
            }
        }
    }

    public function customerAssignmentOnJob($data)
    {
        $params = json_decode($data['params'], 1);
        $customerName = isset($params['customer_name']) ? $params['customer_name'] : null;
        $userIdList = isset($params['user_id_list']) ? $params['user_id_list'] : null;
        $addDateStart = isset($params['add_date_start']) ? $params['add_date_start'] : null;
        $addDateEnd = isset($params['add_date_end']) ? $params['add_date_end'] : null;
        $addWay = isset($params['add_way']) ? $params['add_way'] : null;
        $gender = isset($params['gender']) ? $params['gender'] : null;
        $payStatus = isset($params['pay_status']) ? $params['pay_status'] : null;
        $payNumMin = isset($params['pay_num_min']) ? $params['pay_num_min'] : null;
        $payNumMax = isset($params['pay_num_max']) ? $params['pay_num_max'] : null;
        $tagType = isset($params['tag_type']) ? $params['tag_type'] : null;
        $tagIdList = isset($params['tag_id_list']) ? $params['tag_id_list'] : null;
        $lossStatus = 0;
        $corpid = $data['corpid'];
        $page = 1;
        $pageSize = 500;

        $filterCustomerList = json_decode($data['filter_customer_list'], 1);
        $filterData = [];
        foreach($filterCustomerList as $value) {
            $filterData[] = $value['external_userid'].'-'.$value['handover_userid'];
        }

        // 判断是否筛选了客服
        if(empty($userIdList)) {
            // 没有筛选客服时，从系统中获取所有客服
            $userIdList = DjUser::query()
                ->where('corpid', $data['corpid'])
                ->where('enable', 1)
                ->get();
            $userIdList = $userIdList->isNotEmpty() ? $userIdList->toArray() : [];
            $userIdList = array_column($userIdList, 'user_id');
        }

        foreach($userIdList as $userId) {
            $flag = true;
            $customerDataList = [];

            while($flag) {
                list($customerList, $count) = $this->customerListOnJob($customerName, $userId, $addDateStart,
                    $addDateEnd, $addWay, $gender, $payStatus, $payNumMin, $payNumMax, $tagType, $tagIdList,
                    $lossStatus, $corpid, $page, $pageSize);

                if($count > 0) {
                    foreach($customerList as $customer) {
                        $kk = $customer['external_userid'].'-'.$customer['user_id'];
                        if(!in_array($kk, $filterData)) {
                            $customerDataList[] = $customer['external_userid'];
                        }
                    }

                    $pages = ceil($count/500);
                    if($page >= $pages) {
                        $flag = false;
                    } else {
                        $page++;
                    }
                } else {
                    $flag = false;
                }
            }

            if(count($customerDataList) > 0) {

                if($userId != $data['takeover_userid']) {
                    // 将数据写入
                    $result = CustomerAssignmentTotal::query()
                        ->insert([
                            'handover_userid' => $userId,
                            'takeover_userid' => $data['takeover_userid'],
                            'transfer_success_msg' => $data['transfer_success_msg'],
                            'external_userid' => json_encode($customerDataList),
                            'type' => 1,
                            'status' => 1,
                            'portrait_inheritance' => $data['portrait_inheritance'],
                            'enable' => 1,
                            'corpid' => $data['corpid'],
                        ]);
                } else {
                    $result = true;
                }

                if(!$result) {
                    // 日志记录
                    Log::logError($this->signature, [
                        'errmsg' => '客户在职迁移-插入数据失败',
                        'params' => $data,
                        'tmp' => [
                            'filter_data' => $filterData,
                            'user_id_list' => $userIdList,
                            'user_id' => $userId,
                            'customer_data_list' => $customerDataList,
                        ],
                        'result' => $result,
                    ], 'script_error');

                    // 邮件报警
                    $this->exceptionHandler($this->signature, json_encode([
                        'errmsg' => '客户在职迁移-插入数据失败',
                        'params' => $data,
                        'tmp' => [
                            'filter_data' => $filterData,
                            'user_id_list' => $userIdList,
                            'user_id' => $userId,
                            'customer_data_list' => $customerDataList,
                        ],
                        'result' => $result,
                    ]), '客户在职迁移-'.$this->signature);
                }
            } else {
                // 日志记录
                Log::logError($this->signature, [
                    'errmsg' => '客户在职迁移-查询客户列表为空',
                    'params' => $data,
                    'tmp' => [
                        'filter_data' => $filterData,
                        'user_id_list' => $userIdList,
                        'user_id' => $userId,
                        'customer_data_list' => $customerDataList,
                    ],
                ], 'script_error');
            }
        }
    }

    public function customerAssignmentQuit($data)
    {
        $params = json_decode($data['params'], 1);
        $customerName = isset($params['customer_name']) ? $params['customer_name'] : null;
        $userIdList = isset($params['user_id_list']) ? json_decode($params['user_id_list'], 1) : null;
        $addDateStart = isset($params['add_date_start']) ? $params['add_date_start'] : null;
        $addDateEnd = isset($params['add_date_end']) ? $params['add_date_end'] : null;
        $addWay = isset($params['add_way']) ? $params['add_way'] : null;
        $payStatus = isset($params['pay_status']) ? $params['pay_status'] : null;
        $payNumMin = isset($params['pay_num_min']) ? $params['pay_num_min'] : null;
        $payNumMax = isset($params['pay_num_max']) ? $params['pay_num_max'] : null;
        $tagType = isset($params['tag_type']) ? $params['tag_type'] : null;
        $tagIdList = isset($params['tag_id_list']) ? json_decode($params['tag_id_list']) : null;
        $corpid = $data['corpid'];
        $page = 1;
        $pageSize = 500;

        $filterCustomerList = json_decode($data['filter_customer_list'], 1);
        $filterData = [];
        foreach($filterCustomerList as $value) {
            $filterData[] = $value['external_userid'].'-'.$value['handover_userid'];
        }

        // 判断是否筛选了客服
        if(empty($userIdList)) {
            // 没有筛选客服时，从系统中获取所有客服
            $userIdList = DjUser::query()
                ->where('corpid', $data['corpid'])
                ->where('enable', 1)
                ->get();
            $userIdList = $userIdList->isNotEmpty() ? $userIdList->toArray() : [];
            $userIdList = array_column($userIdList, 'user_id');
        }

        foreach($userIdList as $userId) {
            $flag = true;
            $customerDataList = [];

            while($flag) {
                list($customerList, $count) = $this->customerListQuit($customerName, $userId, $addDateStart,
                    $addDateEnd, $addWay, $payStatus, $payNumMin, $payNumMax, $tagType, $tagIdList, $corpid, $page,
                    $pageSize);

                if($count > 0) {
                    foreach($customerList as $customer) {
                        $kk = $customer['external_userid'].'-'.$customer['user_id'];
                        if(!in_array($kk, $filterData)) {
                            $customerDataList[] = [
                                'external_userid' => $customer['external_userid']
                            ];
                        }
                    }

                    $pages = ceil($count/500);
                    if($page >= $pages) {
                        $flag = false;
                    } else {
                        $page++;
                    }
                } else {
                    $flag = false;
                }
            }

            if(count($customerDataList) > 0) {
                // 将数据写入
                $result = CustomerAssignmentTotal::query()
                    ->insert([
                        'handover_userid' => $userId,
                        'takeover_userid' => $data['takeover_userid'],
                        'transfer_success_msg' => $data['transfer_success_msg'],
                        'external_userid' => json_encode($customerDataList),
                        'type' => 2,
                        'status' => 1,
                        'portrait_inheritance' => $data['portrait_inheritance'],
                        'corpid' => $data['corpid']
                    ]);

                if(!$result) {
                    // 日志记录
                    Log::logError($this->signature, [
                        'errmsg' => '客户在职迁移-插入数据失败',
                        'params' => $data,
                        'tmp' => [
                            'filter_data' => $filterData,
                            'user_id_list' => $userIdList,
                            'user_id' => $userId,
                            'customer_data_list' => $customerDataList,
                        ],
                        'result' => $result,
                    ], 'script_error');

                    // 邮件报警
                    $this->exceptionHandler($this->signature, json_encode([
                        'errmsg' => '客户在职迁移-插入数据失败',
                        'params' => $data,
                        'tmp' => [
                            'filter_data' => $filterData,
                            'user_id_list' => $userIdList,
                            'user_id' => $userId,
                            'customer_data_list' => $customerDataList,
                        ],
                        'result' => $result,
                    ]), '客户在职迁移-'.$this->signature);
                }
            } else {
                // 日志记录
                Log::logError($this->signature, [
                    'errmsg' => '客户离职迁移-查询客户列表为空',
                    'params' => $data,
                    'tmp' => [
                        'filter_data' => $filterData,
                        'user_id_list' => $userIdList,
                        'user_id' => $userId,
                        'customer_data_list' => $customerDataList,
                    ],
                ], 'script_error');
            }
        }
    }

    // 客户管理列表
    public function customerListOnJob($customerName, $userId, $addDateStart, $addDateEnd,
                                      $addWay, $gender, $payStatus, $payNumMin, $payNumMax, $tagType, $tagIdList,
                                      $lossStatus, $corpid, $page, $pageSize)
    {
        $customerQuery = CustomerDetails::suffix($corpid)
//            ->where('loss_status', 1)
            ->where('corpid', $corpid);

        if(!empty($customerName)) {
            $customerQuery = $customerQuery->where('name', 'like', '%'.$customerName.'%');
        }
        if(!empty($userId)) {
            $customerQuery = $customerQuery->where('user_id', $userId);
        }
        if(!empty($addDateStart)) {
            $customerQuery = $customerQuery->where('createtime', '>=', strtotime($addDateStart . ' 00:00:00'));
        }
        if(!empty($addDateEnd)) {
            $customerQuery = $customerQuery->where('createtime', '<=', strtotime($addDateEnd . ' 23:59:59'));
        }
        if(is_numeric($addWay)) {
            $customerQuery = $customerQuery->where('add_way', $addWay);
        }
        if(is_numeric($gender)) {
            $customerQuery = $customerQuery->where('gender', $gender);
        }

        // 付款状态，付款次数筛选
        if(is_numeric($payStatus)) {
            if(0 == $payStatus) {
                $payNumMax=0;
                $payNumMin=0;
            }else {
                if(empty($payNumMin)) {
                    $payNumMin = 1;
                }
            }

            if(is_numeric($payNumMax)) {
                $customerQuery = $customerQuery->where('pay_num', '<=', $payNumMax);
            }
            if(is_numeric($payNumMin)) {
                $customerQuery = $customerQuery->where('pay_num', '>=', $payNumMin);
            }

        }

        if(1 == $tagType) {
            $tagRaw = '';
            foreach($tagIdList as $k=>$tagId) {
                $tagRaw .= $tagId;
                if($k < count($tagIdList) -1){
                    $tagRaw = $tagRaw.' ';
                }
            }

            $customerQuery = $customerQuery->whereRaw('match(`tag_list`) against ("'.$tagRaw.'" in boolean mode)');
        }
        if(2 == $tagType) {
            $tagRaw = '';
            foreach($tagIdList as $k=>$tagId) {
                $tagRaw .= '+'.$tagId;
                if($k < count($tagIdList) -1){
                    $tagRaw = $tagRaw.' ';
                }
            }

            $customerQuery = $customerQuery->whereRaw('match(`tag_list`) against ("'.$tagRaw.'" in boolean mode)');
        }
        if(3 == $tagType) {
            $customerQuery = $customerQuery->whereNull('tag_list');
        }

        $customerQueryCount = clone $customerQuery;
        $count = $customerQueryCount->selectRaw('count(distinct(`external_userid`)) as count')->first();
        $count = $count->count;
        $offset = ($page - 1) * $pageSize;
        $data = $customerQuery->select(['customer_id', 'name', 'gender', 'external_userid', 'user_id', 'remark',
            'createtime', 'add_way', 'corpid', 'pay_num', 'enable'])
            ->groupBy('con_user_cus')
            ->offset($offset)
            ->limit($pageSize)
            ->get()
            ->toArray();

        return [$data, $count];
    }

    public function customerListQuit($customerName, $userId, $addDateStart, $addDateEnd, $addWay,
                                     $payStatus, $payNumMin, $payNumMax, $tagType, $tagIdList, $corpid,
                                     $page, $pageSize)
    {
        $customerQuery = CustomerDetails::suffix($corpid)
//            ->where('loss_status', 2)
            ->where('corpid', $corpid);

        if(!empty($customerName)) {
            $customerQuery = $customerQuery->where('name', 'like', '%'.$customerName.'%');}
        if(!empty($userId)) {
            $customerQuery = $customerQuery->where('user_id', $userId);}
        if(!empty($addDateStart)) {
            $customerQuery = $customerQuery->where('createtime', '>=', strtotime($addDateStart . ' 00:00:00'));}
        if(!empty($addDateEnd)) {
            $customerQuery = $customerQuery->where('createtime', '<=', strtotime($addDateEnd . ' 23:59:59'));}
        if(is_numeric($addWay)) {
            $customerQuery = $customerQuery->where('add_way', $addWay);}

        // 付款状态，付款次数筛选
        if(is_numeric($payStatus)) {
            if(0 == $payStatus) {
                $payNumMax=0;
                $payNumMin=0;
            } else {
                if(empty($payNumMin)) {
                    $payNumMin = 1;
                }
            }

//            $customerQuery = $customerQuery->where('pay_num', '>=', $payNumMin)
//                ->where('pay_num', '<=', $payNumMax);

            if(is_numeric($payNumMax)) {
                $customerQuery = $customerQuery->where('pay_num', '<=', $payNumMax);
            }
            if(is_numeric($payNumMin)) {
                $customerQuery = $customerQuery->where('pay_num', '>=', $payNumMin);
            }
        }

        if(1 == $tagType) {
            $tagRaw = '';
            foreach($tagIdList as $k=>$tagId) {
                $tagRaw .= $tagId;
                if($k < count($tagIdList) -1){
                    $tagRaw = $tagRaw.' ';
                }
            }

            $customerQuery = $customerQuery->whereRaw('match(`tag_list`) against ("'.$tagRaw.'" in boolean mode)');
        }
        if(2 == $tagType) {
            $tagRaw = '';
            foreach($tagIdList as $k=>$tagId) {
                $tagRaw .= '+'.$tagId;
                if($k < count($tagIdList) -1){
                    $tagRaw = $tagRaw.' ';
                }
            }

            $customerQuery = $customerQuery->whereRaw('match(`tag_list`) against ("'.$tagRaw.'" in boolean mode)');
        }
        if(3 == $tagType) {
            $customerQuery = $customerQuery->whereNull('tag_list');}

        $customerQueryCount = clone $customerQuery;
        $count = $customerQueryCount->selectRaw('count(distinct(`external_userid`)) as count')->first();
        $count = $count->count;
        $offset = ($page - 1) * $pageSize;
        $data = $customerQuery->select(['customer_id', 'name', 'gender',
            'external_userid', 'user_id', 'remark', 'createtime', 'add_way', 'corpid', 'pay_num'])
            ->groupBy('con_user_cus')
            ->offset($offset)
            ->limit($pageSize)
            ->get()
            ->toArray();

        return [$data, $count];
    }

    public function exceptionHandler($key, $message, $title)
    {
        $val = RedisModel::get($key);
        if(empty($val)) {
            EmailQueue::rPush($title, $message, ['song.shen@kuxuan-inc.com'], []);
            RedisModel::set($key, 1);
            RedisModel::expire($key, 10*60);
        }
    }
}