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\adminapi\service\WeCallService;
use app\common\model\works\ServiceWork;
use app\common\model\goods_time\GoodsTime;
use app\common\model\master_worker\MasterWorker;
use app\common\model\works\ServiceWorkAnomalous;
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 $customerList = [];

    //服务类目
    protected $categoryType = [
        1 => '安装', 
        2 => '维修', 
        3 => '清洗', 
    ];

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

    protected function execute(Input $input, Output $output)
    {
        //自动派单
        // $this->autoDispatch();

        // //执行外呼任务
        // $h = date('H');
        // if ($h >= 8 && $h <= 22) {
        //     $this->startTask();
        // }

        //异常工单：已过预约时间工程师未确认上门
        $this->workAnomalous();

        //异常工单：上门时间超过两小时工单未确认完成
        $this->unFinishedWorkAnomalous();
    }

    /**
     * 异常工单：已过预约时间工程师未确认上门
     */
    protected function workAnomalous() 
    {
        $size = 100;
        $startTime = strtotime(date('Y-m-d 00:00:00'));
        $endTime = time();  // 当前时间戳

        $list = ServiceWork::alias("a")
            ->leftJoin('service_work_anomalous b','a.id = b.work_id')
            ->where('a.work_status','<',3)
            ->where('a.service_status','<',2)
            ->where('a.refund_approval',0)
            ->where('a.work_pay_status',1)
            ->where('a.appointment_time','between', [$startTime, $endTime])
            ->whereNotExists(function($query) {
                $query->table('la_service_work_anomalous c')
                      ->whereRaw('a.id = c.work_id')
                      ->where('c.reason_type', 3);
            })
            ->field('a.id,a.appointment_time,b.id as anomalous_id,b.reason_type')
            ->group('a.id')
            ->order('a.create_time','asc')
            ->limit($size)
            ->select()
            ->toArray();
        if (!$list) {
            return ;
        }

        foreach($list as $item) {     
            try {
                ServiceWorkAnomalous::create([
                    'work_id' => $item['id'],
                    'reason_type' => 3,
                    'reason' => '已过预约时间工程师未确认上门',
                ]);
            } catch (\Exception $e) {
                Log::write('异常工单:'.$e->getMessage());
            }
        }
    }

    /**
     * 异常工单：上门时间超过两小时工单未确认完成
     */
    protected function unFinishedWorkAnomalous() 
    {
        $size = 100;
        $startTime = strtotime(date('Y-m-d 00:00:00'));
        $endTime = time() - 7200;  // 当前时间减去7200秒（2小时）
        $list = ServiceWork::alias("a")
            ->leftJoin('service_work_anomalous b','a.id = b.work_id')
            ->where('a.work_status', '>=', 4)
            ->where('a.service_status','<',3)
            ->where('a.refund_approval',0)
            ->where('a.work_pay_status',1)
            ->where('a.appointment_time','between', [$startTime, $endTime])
            ->whereNotExists(function($query) {
                $query->table('la_service_work_anomalous c')
                      ->whereRaw('a.id = c.work_id')
                      ->where('c.reason_type', 4);
            })
            ->field('a.id,a.appointment_time,b.id as anomalous_id')
            ->group('a.id')
            ->order('a.create_time','asc')
            ->limit($size)
            ->select()
            ->toArray();
        if (!$list) {
            return ;
        }

        foreach($list as $item) {     
            try {
                ServiceWorkAnomalous::create([
                    'work_id' => $item['id'],
                    'reason_type' => 4,
                    'reason' => '上门时间超过两小时工单未确认完成',
                ]);
            } catch (\Exception $e) {
                Log::write('异常工单:'.$e->getMessage());
            }
        }
    }

    /*
     * 自动派单总分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'));
       
        $list = ServiceWork::where('work_status',0)
            ->where('service_status',0)
            ->where('refund_approval',0)
            ->where('work_pay_status',1)
            ->where('exec_num','<', 2)
            ->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,work_sn')
            ->order('create_time','asc')
            ->limit($size)
            ->select()
            ->toArray();
        if (!$list) {
            return ;
        }

        foreach($list as $item) {     
            try {
                //优先平台工程师派单
                $res = $this->platformWorker($item);
                if ($res === false) {
                    //门店负责人派单
                    $res = $this->teamWorker($item);
                    if ($res === false) {
                        ServiceWork::where('id',$item['id'])
                            ->update([
                                'exec_num' => 2,
                            ]);
                        ServiceWorkAnomalous::create([
                            'work_id' => $item['id'],
                            'reason_type' => 2,
                            'reason' => '自动派单:找不到工程师',
                        ]);
                    }
                }

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

    /**
     * 执行外呼任务
     */
    protected function startTask() {
        if ($this->customerList) {
            $weCallService = new WeCallService();
            $res = $weCallService->importUser($this->customerList);
            if (isset($res['code']) && $res['code'] == 200) {
                $res = $weCallService->startTask();
            }
        }
        $this->customerList = [];
    }

    /**
     * 派单给平台工程师
     */
    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',
                'a.mobile',
                'a.is_wecall',
                '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 && $worker['is_wecall'] == 1) {
                    $this->customerList[] = [
                        'phone' => $worker['mobile'],
                        'properties' => [
                            '订单号' => substr($item['work_sn'], -4),
                            // '详细地址'=>$item['address'],
                            // '服务类型'=> isset($this->categoryType[$item['category_type']]) ? $this->categoryType[$item['category_type']] : '',
                            // '客户手机号'=>$item['mobile']
                        ]
                    ];
                }
                return $res;
            }
        } 
        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);
                $this->customerList[] = [
                    'phone' => MasterWorker::where('id',$worker['master_worker_id'])->value('mobile'),
                    'properties' => [
                        '订单号' => substr($item['work_sn'], -4),
                        // '详细地址'=>$item['address'],
                        // '服务类型'=> isset($this->categoryType[$item['category_type']]) ? $this->categoryType[$item['category_type']] : '',
                        // '客户手机号'=>$item['mobile']
                    ]
                ];
                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_num' => Db::raw('exec_num + 1'),
            ]);
            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;
    }

}