playlet/app/Console/Repair/CustomerConv...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2022/11/22
 * Time: 16:12
 */

namespace App\Console\Repair;


use App\Models\CustomerDetails;
use App\Models\Report\DjCustomerConversationReport;
use Illuminate\Console\Command;

class CustomerConversationReportDataRepair extends Command
{
    protected $signature    = 'CustomerConversationReportDataRepair';
    protected $description  = '员工联系客户数据统计历史数据修复';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $this->info(date('H:i') . ' 开始执行');

        $corpList = DjCustomerConversationReport::query()
            ->select(['corpid', 'date'])
            ->where('enable', 1)
            ->where('new_contact_cnt', 0)
            ->where('date', '<=', '2021-12-31')
            ->where('date', '>=', '2021-01-01')
            ->groupBy(['corpid', 'date'])
            ->orderBy('date', 'desc')
            ->get();

        foreach($corpList as $info) {
            $this->info(date('H:i') . ' 开始处理企微 ' . $info->corpid . ' 在 ' . $info->date . ' 日的数据');

            $this->dealCorpData($info->corpid, $info->date);
        }

        $this->info(date('H:i') . ' 执行完成');
    }

    public function dealCorpData($corpid, $date)
    {
        # 提取该企微在当日所有的客服
        $userList = DjCustomerConversationReport::query()
            ->where('date', $date)
            ->where('enable', 1)
            ->where('corpid', $corpid)
            ->pluck('user_id')
            ->toArray();

        # 批量查询当日所有客服的流失客户数
//        $lossCustomerData = CustomerDetails::suffix($corpid)
//            ->where('corpid', $corpid)
//            ->whereIn('user_id', $userList)
//            ->where('loss_status', 0)
//            ->whereBetween('loss_time', [$date . ' 00:00:00', $date . ' 23:59:59'])
//            ->selectRaw('user_id, sum(1) as loss_contact_cnt')
//            ->groupBy('user_id')
//            ->get();

        $addCustomerData = CustomerDetails::suffix($corpid)
            ->where('corpid', $corpid)
            ->whereIn('user_id', $userList)
            ->whereIn('loss_status', [0, 1])
            ->whereBetween('createtime', [strtotime($date . ' 00:00:00'), strtotime($date . ' 23:59:59')])
            ->selectRaw('user_id, sum(1) as new_contact_cnt')
            ->groupBy('user_id')
            ->get();

        foreach($userList as $userId) {
//            $lossData = $lossCustomerData->where('user_id', $userId)->first();
            $addData = $addCustomerData->where('user_id', $userId)->first();
            $newContactCnt = $addData->new_contact_cnt ?? 0;
            if(!empty($newContactCnt)) {
                $this->info(date('H:i').' corpid:'.$corpid." user_id:".$userId." date:".$date);
                DjCustomerConversationReport::query()
                    ->where('corpid', $corpid)
                    ->where('date', $date)
                    ->where('user_id', $userId)
                    ->update([
//                    'loss_contact_cnt' => $lossData->loss_contact_cnt ?? 0,
                        'new_contact_cnt'  => $newContactCnt,
                    ]);
            }
        }
    }
}