ball/app/common/command/AutomaticDispatch.php

<?php
namespace app\common\command;

use think\facade\Db;
use think\facade\Log;
use think\console\Input;
use think\console\Output;
use think\console\Command;
use app\common\model\works\ServiceWork;
use app\common\model\goods_time\GoodsTime;
use app\common\model\master_worker\MasterWorker;
use app\common\model\master_worker\MasterWorkerTeam;
use app\common\model\works\ServiceWorkAllocateWorkerLog;
use app\workerapi\logic\ServiceWorkerAllocateWorkerLogic;
use app\common\model\master_worker\MasterWorkerServiceTime;

class AutomaticDispatch extends Command
{
    //地理时效评分占比
    protected $distanceRate = 0.25;
    //工程师权重评分占比
    protected $weightRate = 0.2;
    //工程师综合评分占比
    protected $comprehensiveRate = 0.55;

    //默认服务时长 180 分钟
    protected $defaultServiceTime = 180; 

    protected function configure()
    {
        $this->setName('automatic_dispatch')
            ->setDescription('自动派单');
    }

    protected function execute(Input $input, Output $output)
    {
        $this->autoDispatch();
    }

    /*
     * 自动派单总分100分
        1、地理效率得分（distance score）25%
        2、工程师权重「自定义是否有证」（weight score）20%
        3、工程师综合服务分 55%
    */
    protected function autoDispatch()
    {
        $size = 100;
        $startTime = strtotime(date('Y-m-d 00:00:00'));
        $endTime = strtotime(date('Y-m-d 23:59:59'));
       
        // 获取当前时间的前五分钟时间戳
        $fiveMinutesAgo = time() - 120; // 300 秒 = 2 分钟
        $list = ServiceWork::where('work_status',0)
            ->where('service_status',0)
            ->where('refund_approval',0)
            ->where('work_pay_status',1)
                    ->where(function ($query) use ($fiveMinutesAgo) {
                        $query->where('exec_time', 0)->whereOr('exec_time', '<', $fiveMinutesAgo);
                    })
                    ->where('appointment_time','between', [$startTime, $endTime])
                    ->field('id,category_type,goods_category_id,service_area_id,lon,lat,province,city,title,appointment_time,address,mobile')
                    ->order('create_time','asc')
                    ->limit($size)
                    ->select()
                    ->toArray();
        if (!$list) {
            return ;
        }

        $isExec = 1;//是否派单成功
 
        foreach($list as $item) {     
            try {
                //优先平台工程师派单
                $res = $this->platformWorker($item);
                if ($res === false) {
                    //门店负责人派单
                    $res = $this->teamWorker($item);
                    if ($res === false) {
                        $isExec = 0;
                    }
                }

            } catch (\Exception $e) {
                print_r($e->getMessage());
                Log::write('自动派单异常:'.$e->getMessage());
            }

            if ($isExec == 0) {
                ServiceWork::where('id',$item['id'])->update([
                    'exec_time' => time(),
                ]);
            }
        }
    }

    /**
     * 派单给平台工程师
     */
    protected function platformWorker($item) {

        // 定义地球半径（单位：米）
        $earthRadius = 6371000;

        // 定义 Haversine 公式计算距离的 SQL 片段
        $distanceCalculation = "{$earthRadius} * 2 * ASIN(SQRT(
            POWER(SIN((RADIANS({$item['lat']}) - RADIANS(a.lat)) / 2), 2) +
            COS(RADIANS({$item['lat']})) * COS(RADIANS(a.lat)) *
            POWER(SIN((RADIANS({$item['lon']}) - RADIANS(a.lon)) / 2), 2)
        ))";
        // 计算距离的字段定义
        $real_distance = Db::raw("{$distanceCalculation} AS real_distance");

        // 获取符合条件的工程师
        $worker = MasterWorker::alias('a')
            ->leftJoin('master_worker_score b', 'a.id = b.worker_id')
            ->where([
                ['is_disable', '=', 0],
                ['work_status', '=', 0],
                ['accept_order_status', '=', 1],
                ['city', '=', $item['city']],
                ['service_area_id', '=', $item['service_area_id']],
                ['tenant_id', '=', 0]
            ])
            ->distinct('a.id')
            ->whereRaw('FIND_IN_SET(' . $item['goods_category_id'] . ', a.category_ids)')
            ->whereRaw("{$distanceCalculation} <= a.distance")
            ->field([
                'a.id',
                'a.tenant_id',
                'a.distance',
                'a.lon',
                'a.lat',
                'a.worker_number',
                'a.real_name',
                'b.comprehensive_score',
                'b.weight_score',
                $real_distance
            ])
            ->orderRaw('(b.comprehensive_score + b.weight_score) desc')
            ->limit(100)
            ->select()
            ->toArray();
            //echo MasterWorker::getLastSql();die;
        $queue = [];
        foreach($worker as $key => $value) {  
            //过滤已接过此单的师傅
            $exists = ServiceWorkAllocateWorkerLog::where('work_id', $item['id'])->where('master_worker_id',$value['id'])->count();
            if ($exists) {
                continue;
            }
        
            //计算地理效率得分
            $realDistance = bcdiv($value['real_distance'],1000,2);
            $travelTime = $realDistance * 2;//预计每公里行驶2分钟
            
            $distanceScore = 100 - ($travelTime * 1.5) - ($realDistance * 5);
            $distanceScore = bcadd($distanceScore, 0, 2);
            $tmpDistanceRate = bcmul($distanceScore, $this->distanceRate, 2);
            $tmpRate = 0;
            $value['travelTime'] = $travelTime;
            $value['comprehensive_score'] = isset($value['comprehensive_score']) ? $value['comprehensive_score'] : 0;
            $value['weight_score'] = isset($value['weight_score']) ? $value['weight_score'] : 0;
            $tmpRate = bcmul($value['comprehensive_score'], $this->comprehensiveRate, 2) + bcmul($value['weight_score'], $this->weightRate, 2);
            $tmpRate = bcadd($tmpRate, $tmpDistanceRate,2);
            $tmpKey = isset($queue[$tmpRate]) ? bcadd($tmpRate, $key / 100,2) : $tmpRate;//防止键名重复
            $queue[$tmpKey] = $value;
           
        }
        //按照工程师的总分值倒序排序
        krsort($queue);
        foreach($queue as $worker) {
            $serviceTime = MasterWorkerServiceTime::where('master_worker_id',$worker['id'])->where('goods_category_id',$item['goods_category_id'])->value('service_time');
            if (empty($serviceTime)) {
                $serviceTime = GoodsTime::whereRaw('FIND_IN_SET('.$item['goods_category_id'].', goods_category_ids)')->value('service_time');
                $serviceTime = $serviceTime ?? $this->defaultServiceTime;//默认服务时长
            }

            //预约开始时间和结束时间
            $appointment_time = is_numeric($item['appointment_time']) ? $item['appointment_time'] : strtotime($item['appointment_time']);
            $estimated_finish_time = $appointment_time + $serviceTime * 60 + $worker['travelTime'] * 60;
            //校验客户的预约时间是否在工程师的空挡期内
            $count = ServiceWork::where([
                ['master_worker_id','=',$worker['id']],
                ['work_status','>=',1],
                ['work_status','<=',5],
                ['service_status','<',4]
            ])
            ->where(function ($query) use ($appointment_time,$estimated_finish_time) {
                $query->where('appointment_time', 'between',[$appointment_time, $estimated_finish_time])
                    ->whereOr('estimated_finish_time', 'between', [$appointment_time, $estimated_finish_time]);
            })
            ->count();
            if ($count == 0) {
                $operaLog = '系统自动派单于'.date('Y-m-d H:i:s',time()).'分配了工程师'.'编号['.$worker['worker_number'].']'.$worker['real_name'];
                $res = $this->allocateWorker($item,$worker['id'],$worker['tenant_id'],$operaLog,$estimated_finish_time);
                if ($res === true) {
                    return true;
                }
            }
        } 
        return false;
    }

    /**
     * 派单给门店负责人
     */
    protected function teamWorker($item) {
       
        // 地球半径，单位：米
        $earthRadius = 6371000;

        // 定义 Haversine 公式计算距离的 SQL 片段
        $distanceCalculation = "{$earthRadius} * 2 * ASIN(SQRT(
            POWER(SIN((RADIANS({$item['lat']}) - RADIANS(lat)) / 2), 2) +
            COS(RADIANS({$item['lat']})) * COS(RADIANS(lat)) *
            POWER(SIN((RADIANS({$item['lon']}) - RADIANS(lon)) / 2), 2)
        ))";

        // 计算距离的字段定义
        $real_distance = Db::raw("{$distanceCalculation} AS real_distance");

        // 判断预约时间是上午还是下午
        $isAm = date("H", strtotime($item['appointment_time'])) < 12 ? 1 : 0;
        // 根据上午或下午构建查询条件
        $whereRaw = $isAm ? 'am_order < am_limit' : 'pm_order < pm_limit';

        // 获取符合条件的工程师团队
        $worker = MasterWorkerTeam::where([
            ['accept_order_status', '=', 1],
            ['city', '=', $item['city']],
            ['service_area_id', '=', $item['service_area_id']],
        ])
        ->whereRaw($whereRaw)
        ->whereRaw('FIND_IN_SET(' . $item['goods_category_id'] . ', goods_category_ids)')
        // 使用 Haversine 公式替换 ST_Distance_Sphere 函数进行距离筛选
        ->whereRaw("{$distanceCalculation} <= distance")
        ->field([
            'id',
            'lon',
            'lat',
            'distance',
            'tenant_id',
            'team_name',
            'master_worker_id',
            'am_order',
            'am_limit',
            'pm_order',
            'pm_limit',
            'min_order',
            'comprehensive_score',
            $real_distance
        ])
        ->order('comprehensive_score', 'desc')
        ->limit(100)
        ->select()
        ->toArray();

            //echo MasterWorkerTeam::getLastSql();die;
        $minQueue = [];
        $queue = [];
        foreach($worker as $key => $value) {  
            //过滤已接过此单的师傅
            $exists = ServiceWorkAllocateWorkerLog::where('work_id', $item['id'])->where('master_worker_id',$value['master_worker_id'])->count();
            if ($exists) {
                continue;
            }
            
            if ($value['am_order'] + $value['pm_order'] < $value['min_order']) {
                $minQueue[] = $value;
            } else {
                $queue[] = $value;
            }
        }
        $queue = array_merge($minQueue,$queue);
        //优先给接单数量不足最低接单数的团队派单，其次再给服务评分高的派单
        foreach($queue as $worker) {
            $operaLog = '系统自动派单于'.date('Y-m-d H:i:s',time()).'分配了团队ID['.$worker['id'].']'.$worker['team_name'];
            $res = $this->allocateWorker($item, $worker['master_worker_id'], $worker['tenant_id'], $operaLog);
            if ($res === true) {
                $updateData = $isAm == 1 ? ['am_order' => Db::raw('am_order + 1')] : ['pm_order' => Db::raw('pm_order + 1')];
                MasterWorkerTeam::where('id',$worker['id'])->update($updateData);
                return true;
            }
        }
        
        return false;
    }

    /**
     * 分配工程师
     */
    protected function allocateWorker($workDetail, $masterWorkerId, $tenant_id,$operaLog, $estimated_finish_time=0)
    {
        Db::startTrans();
        try {
            ServiceWork::where('id',$workDetail['id'])->update([
                'master_worker_id'=>$masterWorkerId,
                'tenant_id' => $tenant_id,
                'work_status'=>1,
                'estimated_finish_time' => $estimated_finish_time,
                'dispatch_time'=>time(),
                'exec_time' => time(),
            ]);
            MasterWorker::setWorktotal('inc',$masterWorkerId);
            $work_log = [
                'work_id'=>$workDetail['id'],
                'master_worker_id'=>$masterWorkerId,
                'type' => 0,
                'opera_log'=> $operaLog
            ];
            ServiceWorkerAllocateWorkerLogic::add($work_log);
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            Log::write('自动派单分配工程师异常:'.$e->getMessage());
            return false;
        }
        // 工程师派单通知【给工程师的通知】【公众号通知，不发短信】
        $res = event('Notice',  [
            'scene_id' => 113,
            'params' => [
                'user_id' => $masterWorkerId,
                'order_id' => $workDetail['id'],
                'thing9' => $workDetail['title'],
                'time7' => $workDetail['appointment_time'],
                'thing8' => (iconv_strlen($workDetail['address'])>15)?(mb_substr($workDetail['address'],0,15,'UTF-8').'...'):$workDetail['address'],
                'phone_number6' => asteriskString($workDetail['mobile']),
            ]
        ]);
        return true;
    }

}