bot-28/app/Console/Commands/Sport.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Sport as SportModel;
use App\Models\SportTeam;
use App\Models\SportLeague;
use App\Models\SportStatistics;
use App\Services\SportClientService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use App\Models\SportEvent;
use App\Models\Config;

class Sport extends Command
{
    /**
     * 命令名称和签名
     *
     * @var string
     */
    protected $signature = 'sport {is_live=0}';
    protected $is_live = 0;

    protected  $short_status = [
            'TBD' => 0,
            'NS' => 0,
            '1H' => 1,
            'HT' => 1,
            '2H' => 1,
            'ET' => 1,
            'BT' => 1,
            'P' => 1,
            'SUSP' => 1,
            'INT' => 1,
            'FT' => 2,
            'AET' => 2,
            'PEN' => 2,
            'PST' => 3,
            'CANC' => 4,
            'ABD' => 4,
            'AWD' => 4,
            'WO' => 4,
            'LIVE' => 1,
        ];
    protected $long_status = [
        'Time To Be Defined' => 0,
        'Not Started' => 0,
        'First Half' => 1,
        'First Half, Kick Off' => 1,
        'Halftime' => 1,
        'Second Half' => 1,
        'Second Half, 2nd Half Started' => 1,
        'Extra Time' => 1,
        'Break Time' => 1,
        'Penalty In Progress' => 1,
        'Match Suspended' => 1,
        'Match Interrupted' => 1,
        'Match Finished' => 2,
        'Match Finished' => 2,
        'Match Finished' => 2,
        'Match Postponed' => 3,
        'Match Cancelled' => 4,
        'Match Abandoned' => 4,
        'Technical Loss' => 4,
        'WalkOver' => 4,
        'In Progress' => 1,
    ];        
    
    protected $fixture_status = [
        'First Half' => 1,
        'First Half, Kick Off' => 1,
        'Halftime' => 1,
        'Second Half' => 1,
        'Second Half, 2nd Half Started' => 1,
        'Extra Time' => 1,
        'Break Time' => 1,
        'Penalty In Progress' => 1,
    ];     
   

    /**
     * 命令描述
     *
     * @var string
     */
    protected $description = '当天会去更新明天的赛事（23:59:00执行一次）';
    /**
     * 执行命令
     *
     * @return int
     */
    public function handle()
    {
        // $this->info('开始执行统计比赛数据任务...');
        $this->is_live = $this->argument('is_live');
        if ($this->is_live == 0) {
            //未开始的赛事拉取
            $this->fixtures();
        } elseif ($this->is_live == 1) {
            //进行中的赛事，定时更新
            $this->liveFixtures();
        } elseif ($this->is_live == 2){
            $this->checkLiveFixtures();
            //$this->updateOvertimeFixtures();
        } elseif ($this->is_live == 3) {
            $this->checkOvertimeFixtures();
        } elseif ($this->is_live == 4) {
            $this->leagueFixtures();
        } else {
            $this->initOdds();
        }
    }

    public function leagueFixtures()
    { 
        //体育赛事结束前几(分钟)锁盘,90分钟结束
        $sport_locked = Config::where('field', 'sport_locked')->first()->val ?? 1;
        //查询世界杯的赛事 
        $data = SportClientService::fixtures(['league' => 1]);
        //file_put_contents('leagueFixtures.json', json_encode($data));
        $this->updateOrCreateSport($data, $sport_locked);
        return true;
    }

    //到了比赛开始时间，但是状态还是未开始，检查比赛
    public function checkOvertimeFixtures()
    { 
        //体育赛事结束前几(分钟)锁盘,90分钟结束
        $sport_locked = Config::where('field', 'sport_locked')->first()->val ?? 1;
        
        // 比赛开始后，状态还是未开始的数据检测
        $ids = SportModel::where('state', 0)->where('game_time', '<=', time())->pluck('data_id')->toArray();
        if ($ids) {
            $chunks = array_chunk($ids, 10);
            foreach ($chunks as $chunk) {
                $ids = implode('-', $chunk);
                $data = SportClientService::fixtures(['ids' => $ids]);
                $this->updateOrCreateSport($data, $sport_locked, 1);
            }
        }
        return true;
    }

    //进行中超过3分钟没有更新数据的赛事，检查比赛是否结束
    public function checkLiveFixtures()
    { 
        //体育赛事结束前几(分钟)锁盘,90分钟结束
        $sport_locked = Config::where('field', 'sport_locked')->first()->val ?? 1;

        //1. 统一锁盘时间（比赛开始前1分钟）
        SportModel::where(['is_locked' => 0, 'is_roll' => 0])->where('game_time', '<=', time() + 90)->update(['is_locked' => 1]);

        //2. 比赛进行中，超过3分钟未更新的数据检测
        $end_time = date("Y-m-d H:i:s", time() - 180);
        $ids = SportModel::where('state', 1)->where('updated_at', '<=', $end_time)->pluck('data_id')->toArray();
        if ($ids) {
            $chunks = array_chunk($ids, 10);
            foreach ($chunks as $chunk) {
                $ids = implode('-', $chunk);
                $data = SportClientService::fixtures(['ids' => $ids]);
                $this->updateOrCreateSport($data, $sport_locked, 1);
            }
        }
        return true;
    }
    
    //更新进行中的赛事
    public function liveFixtures()
    {
        //体育赛事结束前几(分钟)锁盘,90分钟结束
        $sport_locked = Config::where('field', 'sport_locked')->first()->val ?? 1;
        $data = SportClientService::fixtures(['live' => 'all']);
        file_put_contents("fixturesLive.json", json_encode($data));
        $this->updateOrCreateSport($data, $sport_locked);
        return true;
    }

    private function updateOrCreateSport($data, $sport_locked, $is_check = 0) {
        $data = $data['response'];
        $tableData = [];
        $status = $this->short_status;
        foreach ($data as $item) {
            $home_statistics = !empty($item['statistics']) ? $item['statistics'][0]['statistics'] : '';
            $away_statistics = !empty($item['statistics']) ? $item['statistics'][1]['statistics'] : '';

            $sport_data = [
                'data_id' => $item['fixture']['id'],
                'home_team_id' => $item['teams']['home']['id'],
                'home_team_en' => $item['teams']['home']['name'],
                'home_team_logo' => $item['teams']['home']['logo'],
                'guest_team_id' => $item['teams']['away']['id'],
                'guest_team_en' => $item['teams']['away']['name'],
                'guest_team_logo' => $item['teams']['away']['logo'],
                'half_score' => "{$item['score']['halftime']['home']}-{$item['score']['halftime']['away']}",
                'rbt' => $item['fixture']['timestamp'],
                'score' => isset($item['goals']) ? "{$item['goals']['home']}-{$item['goals']['away']}":'-',
                'extra_score' => isset($item['score']['extratime']) ? "{$item['score']['extratime']['home']}-{$item['score']['extratime']['away']}" : "",//加时赛比分
                'league_id' => $item['league']['id'],
                'league_en' => $item['league']['name'],
                'league_data' => json_encode($item['league']),
                'state' => $status[$item['fixture']['status']['short']],//比赛状态：0未开始1进行中2已完场3延期4取消
                'game_time' => $item['fixture']['timestamp'],
                'updated_at' => date('Y-m-d H:i:s'),
                'home_statistics' => json_encode($home_statistics),
                'away_statistics' => json_encode($away_statistics),
                'is_send' => 0,
            ];
            
            
            $info = SportModel::where('data_id', $item['fixture']['id'])->first();
            
            $fixture_status = null;
            if (isset($item['fixture']['status']['long']) ) {
                $long = $item['fixture']['status']['long'];
                if (isset($this->fixture_status[$long])) {
                    $fixture_status = json_encode($item['fixture']['status']);
                    $sport_data['fixture_status'] = $fixture_status;
                } 
                if (isset($this->long_status[$long])) {
                    $sport_data['state'] = $this->long_status[$long];
                }
            }
            //提前锁盘(比赛进行时长，分钟)
            if (isset($item['fixture']['status']['elapsed'])) {
                $elapsed = $item['fixture']['status']['elapsed'];
                if ((int)$elapsed >= 90 - $sport_locked ) {
                   $sport_data['is_locked'] = 1;
                }
            }
            
            $sport_data['score'] = $sport_data['score'] == '-' ? '' : $sport_data['score'];
            $sport_data['half_score'] = $sport_data['half_score'] == '-' ? '' : $sport_data['half_score'];
            $sport_data['extra_score'] = $sport_data['extra_score'] == '-' ? '' : $sport_data['extra_score'];
            
            //锁盘
            if (isset($item['fixture']['status']['blocked']) && $item['fixture']['status']['blocked']) {
                $sport_data['is_locked'] = 1;
            }
            //已结束
            if (isset($item['fixture']['status']['finished']) && $item['fixture']['status']['finished']) {
                $sport_data['state'] = 2;
            }

            //如果赛事取消、延期等，标记需要退款
            if ($sport_data['state'] > 2) {
                $sport_data['refund_status'] = 1;
            }
            
            if (!$info) {
                $sport_data['created_at'] = date('Y-m-d H:i:s');
                $sport_data['status'] = 1;
                $sport_data['home_team'] = SportTeam::getTeamName($sport_data['home_team_id']);
                $sport_data['guest_team'] = SportTeam::getTeamName($sport_data['guest_team_id']);
                $sport_data['league'] = SportLeague::getLeagueName($sport_data['league_id']);
                $tableData[] = $sport_data;
            } else {
                SportModel::where('data_id', $item['fixture']['id'])->update($sport_data);
            }

            if ($sport_data['state'] == 2) {
                $data_id = $item['fixture']['id'];
                //比赛结束，插入比赛事件
                if (!empty($item['events'])) {
                    $this->insertSportEvents($data_id, $item['events']);
                }
                //比赛结束，插入比赛统计数据
                $data = SportClientService::statistics(['fixture' => $data_id,'half' => 'true']);
                // file_put_contents("fixturesStatistics.json", json_encode($data));
                $statistics_data = empty($data['response']) ? [] : $data['response'];
                foreach($statistics_data as $item) {
                    $team_id = $item['team']['id'];
                    //上半场的数据
                    if(!empty($item['statistics_1h'])) {
                        $statistics_1h = $item['statistics_1h'];
                        $this->insertSportStatistics($data_id, $team_id, $statistics_1h, 1); 
                    }
                    //下半场的数据
                    if(!empty($item['statistics_2h'])) {
                        $statistics_2h = $item['statistics_2h'];
                        $this->insertSportStatistics($data_id, $team_id, $statistics_2h, 2); 
                    }
                }
                
            }
        }
        if ($tableData) {
            SportModel::insert($tableData);
        }
        return true;
    }
    
    /**
     * 获取指定日期的所有赛事
     *
     * @return array
     */
    public function fixtures()
    {
        //根据配置拉取多少天的赛事信息
        $days = Config::where('field', 'sport_days')->first()->val ?? 1;
        for($i=0;$i<$days;$i++) {
            $date = Carbon::today()->addDay($i)->toDateString();
            $data = SportClientService::fixtures(['date' => $date]);
    
            $data = $data['response'];
            $tableData = [];
            $status = $this->short_status;
            foreach ($data as $item) {
                $home_statistics = !empty($item['statistics']) ? $item['statistics'][0]['statistics'] : '';
                $away_statistics = !empty($item['statistics']) ? $item['statistics'][1]['statistics'] : '';
    
                $sport_data = [
                    'data_id' => $item['fixture']['id'],
                    'home_team_id' => $item['teams']['home']['id'],
                    'home_team_en' => $item['teams']['home']['name'],
                    'home_team_logo' => $item['teams']['home']['logo'],
                    'guest_team_id' => $item['teams']['away']['id'],
                    'guest_team_en' => $item['teams']['away']['name'],
                    'guest_team_logo' => $item['teams']['away']['logo'],
                    'half_score' => "{$item['score']['halftime']['home']}-{$item['score']['halftime']['away']}",
                    'rbt' => $item['fixture']['timestamp'],
                    'score' => isset($item['score']['fulltime']) ? "{$item['score']['fulltime']['home']}-{$item['score']['fulltime']['away']}":'-',
                    'league_id' => $item['league']['id'],
                    'league_en' => $item['league']['name'],
                    'league_data' => json_encode($item['league']),
                    // 'state' => $status[$item['fixture']['status']['short']],//比赛状态：0未开始1进行中2已完场3延期4取消
                    'game_time' => $item['fixture']['timestamp'],
                    'updated_at' => now(),
                    'home_statistics' => $home_statistics,
                    'away_statistics' => $away_statistics,
                    'is_send' => 0,
                ];
                $status_short = strtoupper($item['fixture']['status']['short']);
                if (isset($status[$status_short])) {
                    $sport_data['state'] = $status[$status_short];
                }
                $sport_data['score'] = $sport_data['score'] == '-' ? '' : $sport_data['score'];
                $sport_data['half_score'] = $sport_data['half_score'] == '-' ? '' : $sport_data['half_score'];
    
                if (!SportModel::where('data_id', $item['fixture']['id'])->exists()) {
                    $sport_data['created_at'] = now();
                    
                    $sport_data['home_team'] = SportTeam::getTeamName($sport_data['home_team_id']);
                    $sport_data['guest_team'] = SportTeam::getTeamName($sport_data['guest_team_id']);
                    $sport_data['league'] = SportLeague::getLeagueName($sport_data['league_id']);
                    $tableData[] = $sport_data;
                } else {
                    
                    $sport_data['home_team'] = SportTeam::getTeamName($sport_data['home_team_id']);
                    $sport_data['guest_team'] = SportTeam::getTeamName($sport_data['guest_team_id']);
                    $sport_data['league'] = SportLeague::getLeagueName($sport_data['league_id']);
                    SportModel::where('data_id', $item['fixture']['id'])->update($sport_data);
                }

                //更新或创建球队和联赛信息
                SportModel::addSportTeam($sport_data);
                SportModel::addSportLeague($item['league']);

                //比赛结束，插入比赛事件
                if (isset($sport_data['state']) && $sport_data['state'] == 2 ) {
                    
                    $data_id = $item['fixture']['id'];
                    if (!empty($item['events'])) {
                        $this->insertSportEvents($data_id, $item['events']);
                    }
                    
                    //比赛结束，插入比赛事件
                    $data = SportClientService::statistics(['fixture' => $data_id,'half' => 'true']);
                    // file_put_contents("fixturesStatistics.json", json_encode($data));
                    $statistics_data = empty($data['response']) ? [] : $data['response'];
                    foreach($statistics_data as $item) {
                        $team_id = $item['team']['id'];
                        //上半场的数据
                        if(!empty($item['statistics_1h'])) {
                            $statistics_1h = $item['statistics_1h'];
                            $this->insertSportStatistics($data_id, $team_id, $statistics_1h, 1); 
                        }
                        //下半场的数据
                        if(!empty($item['statistics_2h'])) {
                            $statistics_2h = $item['statistics_2h'];
                            $this->insertSportStatistics($data_id, $team_id, $statistics_2h, 2); 
                        }
                    }
                }
            }
            if ($tableData) {
                SportModel::insert($tableData);
            }
        }
        
        return true;
    }
    public function initOdds(){
        $page = 1;
        $limit = 10;
        while (true) { 
            $list = SportModel::where('odds','<>', null)->forPage($page, $limit)->get()->toArray();
            if (empty($list)) {
                break;
            }
            echo $page.PHP_EOL;
            foreach($list as $item) {
                $odds = json_decode($item['odds'], true);
                foreach($odds as $odd) {
                    $odd_id = $odd['id'];
                    $odd_name = $odd['name'];
                    $info = DB::table('sport_odds')->where('odd_id',$odd_id)->where('odd_name_en',$odd_name)->first();
                    if ($info && (!$info->odd_name || $odd_name != $info->odd_name_en)) {
                        DB::table('sport_odds')->where('id', $info->id)->update([
                            'odd_name_en' => $odd_name,
                        ]);
                    } elseif (!$info) {
                        DB::table('sport_odds')->insert([
                            'odd_id' => $odd_id,
                            'odd_name_en' => $odd_name,
                            'created_at' => date('Y-m-d H:i:s'),
                            'updated_at' => date('Y-m-d H:i:s'),
                        ]);
                    }
                }
            }

            $page++;
        }
    }

    //比赛开始后，超过3个小时,并且更新时间超过10分钟，还是进行中的数据，更新成已完成
    public function updateOvertimeFixtures()
    { 
        // 比赛开始后，状态还是未开始的数据检测
        $end_time = date("Y-m-d H:i:s", time() - 600);
        SportModel::where('status', 1)->where('state', 1)->where('game_time', '<=', time() - 60*60*3)->where('updated_at', '<=', $end_time)->update(['state' =>2]);
        return true;
    }

    //插入比赛事件数据
    public function insertSportEvents($data_id, $events)
    { 
        foreach($events as $event) {
            SportEvent::create([
                'data_id' => $data_id,
                'type' => $event['type'],
                'time_elapsed' => $event['time']['elapsed'],
                'time' => json_encode($event['time']),
                'detail' => $event['detail'],
                'player' => $event['player'] ? json_encode($event['player']) : $event['player'],
                'team_id' => $event['team']['id'],
                'comments' => $event['comments'],
                'assist' => $event['assist'] ? json_encode($event['assist']) : $event['assist'],
            ]);
        }
    }

    //插入比赛统计数据
    public function insertSportStatistics($data_id, $team_id, $statistics, $half)
    { 
        $insertData = [];
        foreach($statistics as $item) {
            if (!empty($item['type'])) {
                switch ($item['type']) {
                    case 'Shots on Goal':
                        $insertData['shots_on_goal'] = $item['value'];
                        break;
                    case 'Shots off Goal':
                        $insertData['shots_off_goal'] = $item['value'];
                        break;
                    case 'Shots insidebox':
                        $insertData['shots_insidebox'] = $item['value'];
                        break;
                    case 'Shots outsidebox':
                        $insertData['shots_outsidebox'] = $item['value'];
                        break;
                    case 'Total Shots':
                        $insertData['total_shots'] = $item['value'];
                        break;
                    case 'Blocked Shots':
                        $insertData['blocked_shots'] = $item['value'];
                        break;
                    case 'Fouls':
                        $insertData['fouls'] = $item['value'];
                        break;
                    case 'Corner Kicks':
                        $insertData['corner_kicks'] = $item['value'];
                        break;
                    case 'Offsides':
                        $insertData['offsides'] = $item['value'];
                        break;
                    case 'Ball Possession':
                        $insertData['ball_possession'] = $item['value'];
                        break;
                    case 'Yellow Cards':
                        $insertData['yellow_cards'] = $item['value'];
                        break;
                    case 'Red Cards':
                        $insertData['red_cards'] = $item['value'];
                        break;
                    case 'Goalkeeper Saves':
                        $insertData['goalkeeper_saves'] = $item['value'];
                        break;
                    case 'Total passes':
                        $insertData['total_passes'] = $item['value'];
                        break;
                    case 'Passes accurate':
                        $insertData['passes_accurate'] = $item['value'];
                        break;
                    case 'Passes %':
                        $insertData['passes_percent'] = $item['value'];
                        break;
                    default:
                        break;
                }
            }
        }
        if ($insertData) {
            $insertData['data_id'] = $data_id;
            $insertData['team_id'] = $team_id;
            $insertData['half'] = $half;
            SportStatistics::create($insertData);
        }
    }
}