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\works\ServiceWorkLog;
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();
        }

    }
    /*
    * 长期合作师傅综合评分 100分 （每一项默认20%）
评分周期：每周，保修和返修统计前30天的数据

1、用户评分 
2、完单率 = 完结工单/总工单
3、客户粘性率 = 工程师第一次联系客户时间-领单时间 和定义的时间进行比较 
4、加单率 = 加单个数/派单数 
5、上门率 = 已上门/总单数
    */
    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::alias("a")
                    ->leftJoin("master_worker_score b","a.id = b.worker_id")
                    ->field('a.id,a.category_ids,b.comprehensive_score_history')
                    ->limit($offset, $size)
                    ->select()
                    ->toArray();
            if (!$list) {
                break;
            }
            foreach($list as $item) {
                $workId = $item['id'];
                $this->updateComprehensiveScore($startTime,$endTime,$workId,$item['comprehensive_score_history']);

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

    /**
     * 初始化工程师服务区域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 changeTemporaryWorderScore()
    {
        $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::alias("a")
                    ->leftJoin("master_worker_score b","a.id = b.worker_id")
                    ->field('a.id,a.category_ids,a.type,b.comprehensive_score_history')
                    ->limit($offset, $size)
                    ->select()
                    ->toArray();
            if (!$list) {
                break;
            }
            foreach($list as $item) {
                $workId = $item['id'];
                if ($item['type'] == 1) {
                    $this->updateComprehensiveScore($startTime,$endTime,$workId,$item['comprehensive_score_history']);
                } else {
                    $this->updateTemporaryComprehensiveScore($startTime,$endTime,$workId,$item['comprehensive_score_history']);
                }

                //更新工程师平均服务时长
                $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) {
                if (!$categoryId) {
                    continue;
                }
                $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
                    ]);
                }
            }
        }

    }

    /*
    * 长期合作师傅综合评分 100分 （每一项默认20%）
    评分周期：每周
    1、用户评分 
    2、完单率 = 完结工单/总工单
    3、客户粘性率 = 工程师第一次联系客户时间-领单时间 和定义的时间进行比较 
    4、加单率 = 加单个数/派单数 
    5、上门率 = 已上门/总单数
    */
    protected function updateComprehensiveScore($startTime,$endTime,$workId,$historyScore) {
        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)->where('work_pay_status','in',[1,2])->whereBetweenTime('create_time', $startTime, $endTime)->count();
            
            if ($allOrder == 0) {
                $completionRate = 0;
            } else {
                //完单率
                $completionRate = bcdiv($completeOrder, $allOrder ,2);
            }

            if ($completeOrder == 0) {
                $addRate = 0;
            } else {
                //加单率
                $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;
            
            //上门率
            $doorCount = ServiceWorkLog::where('master_worker_id',$workId)->where('opera_log','%like%','已上门')->whereBetweenTime('create_time', $startTime, $endTime)->count();
            $doorRate = bcdiv($doorCount, $allOrder,2);

            //工程师汇总评分 
            $comprehensiveScore = $commentScore + $completionRate + $viscosityRate + $addRate + $doorRate;
            $comprehensiveScore = bcdiv($comprehensiveScore, 8, 2) * 100;
            
            $this->doComprehenSivescore($workId,$comprehensiveScore,$historyScore,$doorCount);
        } catch (\Exception $e) {
            
            Log::write('更新长期合作工程师综合评分异常:'.$e->getMessage());
            return false;
        }
    }

    /**
     * 更新工程师综合评分 （短期合作工程师）
     */
    protected function updateTemporaryComprehensiveScore($startTime,$endTime,$workId,$historyScore) {
        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)->where('work_pay_status','in',[1,2])->whereBetweenTime('create_time', $startTime, $endTime)->count();
            //接单量：统计工程师接单的数量
            $acceptOrder = ServiceWorkReceiveLog::where('master_worker_id',$workId)->whereBetweenTime('create_time', $startTime, $endTime)->count();

            if ($allOrder == 0) {
                $completionRate = 0;
                $acceptRate = 0;
            } else {
                //完单率
                $completionRate = bcdiv($completeOrder, $allOrder ,2);
                //接单率
                $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;
            
            $this->doComprehenSivescore($workId,$comprehensiveScore,$historyScore,$acceptOrder);
        } catch (\Exception $e) {
            
            Log::write('更新短期合作工程师综合评分异常:'.$e->getMessage());
            return false;
        }
    }

    /**
     * 综合评分活跃度衰退机制
        1. 引入双周期加权计算机制
        - 主周期（7天）数据：用于正常活跃状态的评分计算
        - 辅助周期（30天）数据：用于低活跃状态的评分平滑
        2. 设置活跃度阈值（建议值）
        - 活跃状态：周接单量≥5单
        - 低活跃状态：周接单量1-4单
        - 停单状态：周接单量=0单
        3. 衰减系数梯度表：
        | 状态类型      | 衰减系数 | 数据来源权重         |
        |---------------|----------|----------------------|
        | 活跃状态      | 1.0      | 100%主周期数据       |
        | 低活跃状态    | 0.8      | 70%主周期+30%辅助周期|
        | 停单状态      | 0.6      | 50%主周期+50%辅助周期|
     */
    public static function doComprehenSivescore($workId,$curScore,$historyScore,$acceptOrder)
    {
        $historyScore = $historyScore ? explode(",",$historyScore) : [];
        $argScore = $historyScore ? array_sum($historyScore)/count($historyScore) : 0;
        if ($acceptOrder >= 1 && $acceptOrder < 5) {
            $curScore = bcadd($curScore * 0.7, $argScore * 0.3, 2);
        } elseif ($acceptOrder == 0) {
            $curScore = bcadd($argScore * 0.5, 0, 2);
        }
        if ($curScore > 0) {
            if (count($historyScore) >= 4) {
                //移除第一个元素
                array_shift($historyScore);
            }
            $historyScore[] = $curScore;
        }
        MasterWorkerScore::where('worker_id',$workId)->update(['comprehensive_score' => $curScore, 'comprehensive_score_history' => implode(",",$historyScore)]);
    }

    /**
     * 更新团队服务评分
     */
    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;
            }
        }
    }
}