ball/app/common/command/UpdateWorkerScore.php

<?php
namespace app\common\command;

use think\facade\Log;
use think\console\Input;
use think\console\Output;
use think\console\Command;
use think\console\input\Argument;
use app\common\model\works\IssueWork;
use app\common\model\works\ReturnWork;
use app\common\model\works\ServiceWork;
use app\common\model\goods_time\GoodsTime;
use app\common\model\reviews\GoodsReviews;
use app\common\model\master_worker\MasterWorker;
use app\common\model\effective\OrderEffectiveLog;
use app\common\model\works\ServiceWorkReceiveLog;
use app\common\model\master_worker\MasterWorkerTeam;
use app\common\model\master_worker\MasterWorkerScore;
use app\common\model\works\ServiceWorkAllocateWorkerLog;
use app\common\model\master_worker\MasterWorkerServiceTime;
use app\common\model\service_area\ServiceArea;

class UpdateWorkerScore extends Command
{
    protected $defaultServiceTime = 300; //默认服务时长 300 分钟
    protected function configure()
    {
        $this->setName('update_worker_score')
            ->setDescription('更新工程师综合评分和服务时长')
            ->addArgument('type', Argument::OPTIONAL, '类型可选');
    }

    protected function execute(Input $input, Output $output)
    {
        // 获取传递的参数
        $type = $input->getArgument('type');
        
        if ($type == 'init') {
            $this->initMasterAreaId();
        } else {
            //更新工程师评分
            $this->changeWorderScore();

            //更新团队服务评分
            $this->changeWorkTeamScore();
        }

    }

    /**
     * 初始化工程师服务区域ID
     */
    protected function initMasterAreaId()
    {
        $last_id = 0;
        while($last_id >= 0) {
            $list = MasterWorker::where('lon','>',0)
                        ->where('lat','>',0)
                        ->where('id','>',$last_id)
                        ->field('id,lon,lat')
                        ->order('id','asc')
                        ->limit(50)
                        ->select()->toArray();
            if (!$list) {
                $last_id = -1;
                break;
            }
            foreach($list as $item) {
                $last_id = $item['id'];
                $service_area_id = ServiceArea::serviceAreaId(['lon' => $item['lon'], 'lat' => $item['lat']]);
                if ($service_area_id) {
                    MasterWorker::where('id',$item['id'])->update(['service_area_id' => $service_area_id]);
                }
            }
        }
    }

    /**
     * 初始化工程师汇总评分数据，只执行一次即可
     */
    protected function initMasterWorkerScore()
    {
        $masterWorker = MasterWorker::field('id')->order('id','asc')->select()->toArray();
        foreach($masterWorker as $item) {
            //添加工程师汇总评分数据
            MasterWorkerScore::create([
                'worker_id' => $item['id']
            ]);
        }
    }

    /*
     * 每周统计并更新一次工程师的综合评分
    1、用户评分 
    2、完单率 = 完结工单/总工单
    3、接单率 = 已领的工单/总工单
    4、工单投诉率 = 投诉的工单（判定工程师原因） /已完成工单
    5、客户粘性率 = 工程师第一次联系客户时间-领单时间 和定义的时间(10分钟)进行比较 
    6、保修率 = 保修工单/已完成工单（去除保修单）
    7、返修率 = 返修工单/已完成工单
    8、加单率 = 加单个数/派单数
    */
    protected function changeWorderScore()
    {
        $startTime = date('Y-m-d 00:00:00', strtotime('-7 days'));
        $endTime = date('Y-m-d 23:59:59', strtotime('-1 days'));
        $page = 0;
        $size = 50;
        while(true) {
            $page++;
            $offset = ($page - 1) * $size;
            $list = MasterWorker::field('id,category_ids')
                    ->limit($offset, $size)
                    ->select()
                    ->toArray();
            if (!$list) {
                break;
            }
            foreach($list as $item) {
                $workId = $item['id'];
                $this->updateComprehensiveScore($startTime,$endTime,$workId);

                //更新工程师平均服务时长
                $this->updateServiceTime($startTime,$endTime,$item);
            }
        }
    }

    /**
     * 更新工程师服务类目的平均服务时长
     */
    protected function updateServiceTime($startTime,$endTime,$worker) {
        if ($worker['category_ids']) {
            $category_ids = explode(",",$worker['category_ids']);
            foreach($category_ids as $categoryId) {
                $avgTime = ServiceWork::where('master_worker_id',$worker['id'])->where('service_status',3)->whereIn('work_type',[0,1])->whereBetweenTime('create_time', $startTime, $endTime)->field('AVG(finished_time - appointment_time) as avg_time')->find();
                if (isset($avgTime['avg_time']) && $avgTime['avg_time'] > 0) {
                    $avgTime = $avgTime['avg_time'] / 60 ;
                } else {
                    $avgTime = GoodsTime::whereRaw('FIND_IN_SET('.$categoryId.', goods_category_ids)')->value('service_time');
                    $avgTime = $avgTime ?? $this->defaultServiceTime;//默认服务时长
                }
                $exists = MasterWorkerServiceTime::where('master_worker_id',$worker['id'])->where('goods_category_id',$categoryId)->value('id');
                if ($exists) {
                    MasterWorkerServiceTime::where('master_worker_id',$worker['id'])->where('goods_category_id',$categoryId)->update([
                        'service_time' => $avgTime
                    ]);
                } else {
                    MasterWorkerServiceTime::create([
                        'master_worker_id' => $worker['id'],
                        'goods_category_id' => $categoryId,
                        'service_time' => $avgTime
                    ]);
                }
            }
        }

    }

    /**
     * 更新工程师综合评分
     */
    protected function updateComprehensiveScore($startTime,$endTime,$workId) {
        try {
            //查询本周平均评分值
            $goodsReviewsAvg = GoodsReviews::alias('a')->leftJoin("service_work b","a.work_id = b.id")->whereBetweenTime('a.create_time', $startTime, $endTime)->avg('rating');
            $commentScore = $goodsReviewsAvg > 0 ? bcdiv($goodsReviewsAvg, 5, 2) : 0;
            
            //总工单：统计派单日志记录
            $allOrder = ServiceWorkAllocateWorkerLog::where('master_worker_id',$workId)->whereBetweenTime('create_time', $startTime, $endTime)->count();
            //完结工单：统计工单表已完成的工单
            $completeOrder = ServiceWork::where('master_worker_id',$workId)->where('service_status',3)->whereBetweenTime('create_time', $startTime, $endTime)->count();
            
            if ($allOrder == 0) {
                $completionRate = 0;
                $acceptRate = 0;
            } else {
                //完单率
                $completionRate = bcdiv($completeOrder, $allOrder ,2);
                //接单率
                $acceptOrder = ServiceWorkReceiveLog::where('master_worker_id',$workId)->whereBetweenTime('create_time', $startTime, $endTime)->count();
                $acceptRate = bcdiv($acceptOrder, $allOrder ,2);
            }

            if ($completeOrder == 0) {
                $issueRate = 0;
                $returnRate = 0;
                $addRate = 0;
            } else {
                //工单投诉率
                $issueWork = IssueWork::where('master_worker_id',$workId)->where('responsible',2)->whereBetweenTime('create_time', $startTime, $endTime)->count();
                $issueRate = bcdiv($issueWork, $completeOrder ,2);
                $issueRate = 1 - ($issueRate > 1 ? 1 : $issueRate);
                //返修率
                $returnWord = ReturnWork::where('master_worker_id',$workId)->whereBetweenTime('create_time', date('Y-m-d 00:00:00', strtotime('-30 days')), $endTime)->count();
                $returnRate = bcdiv($returnWord, $completeOrder ,2);
                $returnRate =  1 - ($returnRate > 1 ? 1 : $returnRate);
                //加单率
                $addWord = ServiceWork::where('master_worker_id',$workId)->where('work_type',2)->whereBetweenTime('create_time', $startTime, $endTime)->count();
                $addRate = bcdiv($addWord, $completeOrder ,2);
                $addRate = $addRate > 1 ? 1 : $addRate;
            }
         
            //客户粘性率(10分钟内)
            $avgTime = ServiceWork::where('master_worker_id',$workId)->where('work_status','>',1)->where('first_contact_time','>',0)->whereBetweenTime('create_time', $startTime, $endTime)->field('AVG(first_contact_time - receive_time) as avg_time')->find();
            $avgTime = $avgTime['avg_time'] ? $avgTime['avg_time'] / 60 : 0;
            $viscosityRate = $avgTime <= 10  && $avgTime > 0 ? 1 : 0;

            $partOrder = ServiceWork::where('master_worker_id',$workId)->where('work_status','>',1)->where('order_effective_id',0)->whereBetweenTime('create_time', $startTime, $endTime)->count();
            if ($partOrder == 0) {
                $warrantyRate = 0;
            } else {
                //保修率
                $effectiveOder = OrderEffectiveLog::alias('a')->leftJoin('service_work b', 'a.work_id = b.id')->where('b.id',$workId)->whereBetweenTime('a.create_time', date('Y-m-d 00:00:00', strtotime('-30 days')), $endTime)->count();
                $warrantyRate = bcdiv($effectiveOder, $partOrder ,2);
                $warrantyRate = 1 - ($warrantyRate > 1 ? 1 : $warrantyRate);
            }
            //工程师汇总评分 
            $comprehensiveScore = $commentScore + $completionRate + $acceptRate + $issueRate + $viscosityRate + $warrantyRate + $returnRate + $addRate;
            $comprehensiveScore = bcdiv($comprehensiveScore, 8, 2) * 100;
            
            MasterWorkerScore::where('worker_id',$workId)->update(['comprehensive_score' => $comprehensiveScore]);
            
        } catch (\Exception $e) {
            
            Log::write('更新工程师综合评分异常:'.$e->getMessage());
            return false;
        }
    }

    /**
     * 更新团队服务评分
     */
    protected function changeWorkTeamScore() {
        $page = 0;
        $size = 50;
        while(true) {
            $page++;
            $offset = ($page - 1) * $size;
            $list = MasterWorkerTeam::alias('a')->leftJoin('master_worker b','a.master_worker_id=b.id')->field('a.id,b.tenant_id')
                    ->limit($offset, $size)
                    ->select()
                    ->toArray();
            if (!$list) {
                break;
            }
            try {
                foreach($list as $item) {
                    $comprehensiveScore = 3.5;
                    if ($item['tenant_id'] > 0) {
                        $avg = MasterWorker::alias('a')->leftJoin('master_worker_score b','a.id=b.worker_id')
                        ->where('a.tenant_id',$item['tenant_id'])
                        ->field('AVG(b.comprehensive_score) as avg')
                        ->find();
                        if (isset($avg['avg']) && $avg['avg'] > 0) {
                            $comprehensiveScore = bcdiv($avg['avg'], 20 , 1);
                            $comprehensiveScore = $comprehensiveScore < 3.5 ? 3.5 : $comprehensiveScore;
                            $comprehensiveScore = $comprehensiveScore > 5 ? 5 : $comprehensiveScore;
                        } 
                    }
                    
                    MasterWorkerTeam::where('id',$item['id'])->update(['comprehensive_score' => $comprehensiveScore]);
                }
            } catch (\Exception $e) {
                Log::write('更新团队综合评分异常:'.$e->getMessage());
                return false;
            }
        }
    }
}