sports_dapi/app/Http/Model/StWqResult.php

<?php
namespace App\Http\Model;

use Illuminate\Database\Eloquent\Model;
use App\Http\Response\Response;
//use Illuminate\Support\Facades\DB;
use Illuminate\Database\Capsule\Manager as DB;
use App\Lib\Biz\Sport\Common as commonFunction;

/**
 * Class StWqResult
 * @package App\Http\Model
 * 网球 赛事 结果
 */
class StWqResult extends Model
{
    protected $table = 'st_wq_result';
    public $timestamps = false;

    /*
     * 写赛事结果
     * 弃用
     */
    public static function WQresult__($model){
        //获取赛事表7天内所有赛事
        $matchData = $model['model_match']::select('id','home_team','guest_team','lg_id','status','tag','match_date','match_time')
            ->where([['match_date','>',date('Y-m-d',strtotime("-2 day"))],['status','=',2]])
            ->get()
            ->toArray();

        //没有数据，无需操作
        if(empty($matchData)) return Response::success();
        //获取赛事结果表 所有当月
        $matchData_r = $model['model_result']::select('match_id')
            ->where([['ctime','>',date('Y-m-d H:i:s', strtotime("-7 day"))]])
            ->get()
            ->toarray();

        //结果表无数据，直接插入
        if(empty($matchData_r)){
            foreach ($matchData as $k=>$v){
                $start_time = ($v['match_date'].' '.$v['match_time']);
                $time = time()-strtotime($v['match_time']);
                $match_time = self::secTime($time);
                $set_match_r[] = [
                    "match_id"=> $v['id'],
                    "home_player_name"=>$v['home_team'],
                    "guest_player_name"=>$v['guest_team'],
                    "lg_id"=>$v['lg_id'],
                    "status"=>$v['status'],
                    "tag"=> $v['tag'],
                    'match_time'=>$match_time,
                    "ctime"=>date('Y-m-d H:i:s'),
                    "update_time"=>date('Y-m-d H:i:s'),
                    "start_time"=>date('Y-m-d H:i:s',strtotime($start_time))
                ];
            }
            $ret = $model['model_result']::insert($set_match_r);
            if($ret != true) throw new \Exception( Response::generate('',Response::ADD_MATCH_R_ERROR));
        }else {

            //如果结果表有数据，则获取结果表没有的赛事
            foreach ($matchData as $k => $v) {
                foreach ($matchData_r as $kk => $vv) {
                    if ($v['id'] == $vv['match_id']) {
                        unset($matchData[$k]);
                    }
                }
            }
            //如果还有未写入赛事
            if (!empty($matchData)) {
                //写入结果表不存在赛事
                foreach ($matchData as $k => $v) {
                    $start_time = ($v['match_date'] . ' ' . $v['match_time']);
                    $time = time() - strtotime($v['match_time']);
                    $match_time = self::secTime($time);
                    $set_match_r[] = [
                        "match_id" => $v['id'],
                        "home_player_name" => $v['home_team'],
                        "guest_player_name" => $v['guest_team'],
                        "lg_id" => $v['lg_id'],
                        "status" => $v['status'],
                        "tag" => $v['tag'],
                        'match_time' => $match_time,
                        "ctime" => date('Y-m-d H:i:s'),
                        "update_time" => date('Y-m-d H:i:s'),
                        "start_time" => date('Y-m-d H:i:s', strtotime($start_time))
                    ];
                }
                $ret = $model['model_result']::insert($set_match_r);
                if ($ret != true) throw new \Exception(Response::generate('', Response::ADD_MATCH_R_ERROR));//Render([], '10022', lang('Tips','Sports')->get('add_match_r_error'));
            }
        }
    }
    public static function WQresult_v1($model){

        //获取赛事表7天内所有赛事
        $matchData = $model['model_match']::select('id','home_team','guest_team','lg_id','status','tag','match_date','match_time')
            ->where([['match_date','>',date('Y-m-d',strtotime("-2 day"))],['status','=',2]])
            ->get()
            ->toArray();

        //获取赛事结果表 一天内
        $matchData_r = $model['model_result']::select('match_id')
            ->where([['ctime','>',date('Y-m-d H:i:s', strtotime("-2 day"))]])
            ->get()
            ->toArray();

        if(!empty($matchData_r)){
            //如果结果表有数据，则获取结果表没有的赛事
            foreach ($matchData as $k => $v) {
                foreach ($matchData_r as $kk => $vv) {
                    if ($v['id'] == $vv['match_id']) {
                        unset($matchData[$k]);
                    }
                }
            }
        }

         //没有数据，无需操作
         if(empty($matchData)) return Response::success();
        
         //获取赛事id 用于获取赛事对应结果比分
         $match_ids = [];
         foreach($matchData as $k =>$v){
             //只获取赛事已结束的
             if($v['status'] == 2){
                 $match_ids[] = $v['id'];
             }
         }
 
         $match_ids_str = implode(",", $match_ids);

        //组装sql 
        //获取赛事最终赛果
        $sql_result = "select a.match_id,a.home_player_score,a.guest_player_score,a.match_time as a_time,a.home_player_let_plate,a.guest_player_let_plate,a.home_player_let_inning,a.guest_player_let_inning,a.all_inning,a.match_process,a.first_score_player,a.last_score_player,a.first_inning_score,a.second_inning_score,a.third_inning_score,a.match_winer_player,a.result_mark from st_wq_result_record a,
        (select match_id,max(id) id from st_wq_result_record where match_id IN ($match_ids_str) group by match_id)b
        where a.match_id = b.match_id and a.id = b.id ";

        //赛事最终结果
        $match_result = DB::select($sql_result);

        //拼装赛事结果数据 
        $set_match_r = [];
        $ss = [];

        if(!empty($match_result)){
            foreach($matchData as $k =>$v ){
                //获取开赛时间
                $start_time = ($v['match_date'].' '.$v['match_time']);
                $time = time()-strtotime($v['match_time']);
                $match_time = self::secTime($time);
                //获取赛事每节的赛果
                $resultData = commonFunction::filter_by_value($match_result,'match_id',$v['id']);
            
                //如果有获取到结果，并且进程不为空，则写入赛事结果数据
                if(!empty($resultData) and $resultData['match_process'] != ''){
                    //获取赛果json
                    $result_mark = json_decode($resultData['result_mark'],true);

                    $sss[] = $result_mark;

                    //获取进程
                    $match_process = '';
                    if($result_mark['schedule']){
                        $match_process = $result_mark['schedule'];
                    }

                    //获胜球员
                    $match_winer = '';
                    if($result_mark['game_num_H'] > $result_mark['game_num_C']){
                        $match_winer = $v['home_team'];
                    }else{
                        $match_winer = $v['guest_team'];
                    }

                    //组装输赢结果json
                    $inning = [
                        1=>[
                            "home"=>$result_mark['sc_1th_H'],
                            "guest"=>$result_mark['sc_1th_C'],
                        ],
                        2=>[
                            "home"=>$result_mark['sc_2th_H'],
                            "guest"=>$result_mark['sc_2th_C'],
                        ],
                        3=>[
                            "home"=>$result_mark['sc_3th_H'],
                            "guest"=>$result_mark['sc_3th_C'],
                        ],
                        4=>[
                            "home"=>$result_mark['sc_4th_H'],
                            "guest"=>$result_mark['sc_4th_C'],
                        ],
                        5=>[
                            "home"=>$result_mark['sc_5th_H'],
                            "guest"=>$result_mark['sc_5th_C'],
                        ],
                    
                    ];

                    //赛事比分
                    $match_score = $result_mark['game_num_H'].':'.$result_mark['game_num_C'];
                   
                    //赛事待写入赛果数据
                    /*
                    $set_match_r[] = [
                        "match_id"=> $v['id'],
                        "home_player_name"=>$v['home_team'],
                        "guest_player_name"=>$v['guest_team'],
                        "lg_id"=>$v['lg_id'],
                        "status"=>$v['status'],
                        "tag"=> $v['tag'],
                        'match_time'=>$resultData['a_time']?:0,
                        "ctime"=>date('Y-m-d H:i:s'),
                        "update_time"=>date('Y-m-d H:i:s'),
                        "start_time"=>date('Y-m-d H:i:s',strtotime($start_time)),

                        "home_player_let_plate"=> $resultData['home_player_let_plate']?:0, //主队让盘
                        "guest_player_let_plate"=> $resultData['guest_player_let_plate']?:0, //客队让盘
                        "home_player_let_inning"=> $resultData['home_player_let_inning']?:0, //主队让局
                        "guest_player_let_inning"=> $resultData['guest_player_let_inning']?:0, //客队让局
                        "home_player_score"=> $result_mark['game_num_H']?:'', //主队得分
                        "guest_player_score"=> $result_mark['game_num_C']?:'', //客队得分
                        "all_inning"=> $resultData['all_inning']?:0, //总局数
                        // "first_score_player"=> $resultData['first_score']?:'', //最先得分
                        // "last_score_player"=> $resultData['last_score']?:'', //最后得分
                        "first_inning_score"=> $resultData['first_inning_score']?:0, //第一局比分
                        "second_inning_score"=> $resultData['second_inning_score']?:0,//第二局比分
                        "third_inning_score"=> $resultData['third_inning_score']?:0,//第三局比分
                        "match_score"=> $match_score?:'',//赛事比分
                        "match_winer_player"=>  $match_winer?:'',//获胜队员
                        "match_process"=> $match_process?:'',//比赛进程
                        "inning"=> json_encode($inning)?:'',//每局输赢结果
                    ]; 
                    */
                    $set_match_r[] = [
                        "match_id"=> $v['id'],
                        "home_player_name"=>$v['home_team'],
                        "guest_player_name"=>$v['guest_team'],
                        "lg_id"=>$v['lg_id'],
                        "status"=>$v['status'],
                        "tag"=> $v['tag'],
                        'match_time'=>$resultData['a_time']?:0,
                        "ctime"=>date('Y-m-d H:i:s'),
                        "update_time"=>date('Y-m-d H:i:s'),
                        "start_time"=>date('Y-m-d H:i:s',strtotime($start_time)),

                        "home_player_let_plate"=> $resultData['home_player_let_plate']?:0, //主队让盘
                        "guest_player_let_plate"=> $resultData['guest_player_let_plate']?:0, //客队让盘
                        "home_player_let_inning"=> $resultData['home_player_let_inning']?:0, //主队让局
                        "guest_player_let_inning"=> $resultData['guest_player_let_inning']?:0, //客队让局
                        "home_player_score"=> $result_mark['game_num_H']?:0, //主队得分
                        "guest_player_score"=> $result_mark['game_num_C']?:0, //客队得分
                        "all_inning"=> $resultData['all_inning']?:0, //总局数
                        // "first_score_player"=> $resultData['first_score']?:'', //最先得分
                        // "last_score_player"=> $resultData['last_score']?:'', //最后得分
                        "first_inning_score"=> $resultData['first_inning_score']?:'', //第一局比分
                        "second_inning_score"=> $resultData['second_inning_score']?:'',//第二局比分
                        "third_inning_score"=> $resultData['third_inning_score']?:'',//第三局比分
                        "match_score"=> $match_score?:'',//赛事比分
                        "match_winer_player"=>  $match_winer?:'',//获胜队员
                        "match_process"=> $match_process?:'',//比赛进程
                        "inning"=> json_encode($inning)?:'',//每局输赢结果
                    ]; 

                }
            }
        }
        //写入赛果1
        if(!empty($set_match_r)){
            $ret = $model['model_result']::insert($set_match_r);
            if($ret != true) throw new \Exception( Response::generate('',Response::ADD_MATCH_R_ERROR));
        }

        return 1;

    }

    /**
     * 获取自动赛事结果
     * 只更新已结束+未手动修改的赛事结果
     * $match_id 本地赛事id 为0时 则用于后台手动刷新赛果 
     */
    public static function WQresult_v2($model,$match_id=0){
        //如果有赛事id 则只更新当前赛事结果
        if($match_id > 0){
            $match_ids_str = $match_id;
        }else{

            //获取两天内的结束赛事
            $matchData = $model['model_match']
                ->join('st_wq_result','st_wq_result.match_id','=','st_wq_competition.id')
                ->select('st_wq_competition.id','is_correct')
                ->where([['st_wq_competition.match_date','>',date('Y-m-d', strtotime("-2 day"))],['st_wq_competition.status','=',2],['st_wq_result.is_correct','=',-1]])
                ->get()
                ->toArray();

    
            //没有数据，无需操作
            if(empty($matchData)) return Response::success();
            //获取需更新结果的赛事ID
            $match_ids = [];
            foreach($matchData as $k =>$v){
                //未手动修改比分
                if($v['is_correct'] == 0){
                    $match_ids[] = $v['id'];
                }
            }
            if(empty($match_ids)) return Response::success();

            $match_ids_str = implode(",", $match_ids);
        }

        //组装sql 
        //获取赛事最终赛果
        $sql_result = "select a.match_id,a.home_player_name,a.guest_player_name,a.home_player_score,a.guest_player_score,a.match_time as a_time,a.home_player_let_plate,a.guest_player_let_plate,a.home_player_let_inning,a.guest_player_let_inning,a.all_inning,a.match_process,a.first_score_player,a.last_score_player,a.first_inning_score,a.second_inning_score,a.third_inning_score,a.match_winer_player,a.result_mark from st_wq_result_record a,
        (select match_id,max(id) id from st_wq_result_record where match_id IN ($match_ids_str) group by match_id)b
        where a.match_id = b.match_id and a.id = b.id ";

        //赛事最终结果
        $match_result = DB::select($sql_result);

        if(!empty($match_result)){
            foreach($match_result as $k=>$v){
                //获取赛事每节的赛果
                $resultData = commonFunction::filter_by_value($match_result,'match_id',$v->match_id);
            
                //如果有获取到结果，并且进程不为空，则写入赛事结果数据
                if(!empty($resultData) and $resultData['match_process'] != ''){
                    //获取赛果json
                    $result_mark = json_decode($resultData['result_mark'],true);

                    //获取进程
                    $match_process = '';
                    if($result_mark['schedule']){
                        $match_process = $result_mark['schedule'];
                    }

                    //获胜球员
                    $match_winer = '';
                    if($result_mark['game_num_H'] > $result_mark['game_num_C']){
                        $match_winer = $resultData['home_player_name'];
                    }else{
                        $match_winer = $resultData['guest_player_name'];
                    }

                    //组装输赢结果json
                    $inning = [
                        1=>[
                            "home"=>$result_mark['sc_1th_H'],
                            "guest"=>$result_mark['sc_1th_C'],
                        ],
                        2=>[
                            "home"=>$result_mark['sc_2th_H'],
                            "guest"=>$result_mark['sc_2th_C'],
                        ],
                        3=>[
                            "home"=>$result_mark['sc_3th_H'],
                            "guest"=>$result_mark['sc_3th_C'],
                        ],
                        4=>[
                            "home"=>$result_mark['sc_4th_H'],
                            "guest"=>$result_mark['sc_4th_C'],
                        ],
                        5=>[
                            "home"=>$result_mark['sc_5th_H'],
                            "guest"=>$result_mark['sc_5th_C'],
                        ],
                    
                    ];

                    //赛事比分
                    $match_score = $result_mark['game_num_H'].':'.$result_mark['game_num_C'];
                   
                    //赛事待写入赛果数据
                   
                    $set_match_r = [
                        "match_id"=> $v->match_id,
                        "status"=>2,
                        "update_time"=>date('Y-m-d H:i:s'),

                        "home_player_let_plate"=> $resultData['home_player_let_plate']?:0, //主队让盘
                        "guest_player_let_plate"=> $resultData['guest_player_let_plate']?:0, //客队让盘
                        "home_player_let_inning"=> $resultData['home_player_let_inning']?:0, //主队让局
                        "guest_player_let_inning"=> $resultData['guest_player_let_inning']?:0, //客队让局
                        "home_player_score"=> $result_mark['game_num_H']?:0, //主队得分
                        "guest_player_score"=> $result_mark['game_num_C']?:0, //客队得分
                        "all_inning"=> $resultData['all_inning']?:0, //总局数
                        // "first_score_player"=> $resultData['first_score']?:'', //最先得分
                        // "last_score_player"=> $resultData['last_score']?:'', //最后得分
                        "first_inning_score"=> $resultData['first_inning_score']?:'', //第一局比分
                        "second_inning_score"=> $resultData['second_inning_score']?:'',//第二局比分
                        "third_inning_score"=> $resultData['third_inning_score']?:'',//第三局比分
                        "match_score"=> $match_score?:'',//赛事比分
                        "match_winer_player"=>  $match_winer?:'',//获胜队员
                        "match_process"=> $match_process?:'',//比赛进程
                        "inning"=> json_encode($inning)?:'',//每局输赢结果
                        "is_correct"=> -1,//自动比分

                    ]; 

                    $ret = $model['model_result']::where(['match_id' => $v->match_id,'is_correct'=>-1])
                    ->update($set_match_r);
  
                    // if($ret < 1) throw new \Exception( Response::generate('',Response::ADD_MATCH_R_ERROR));  
                }
            }
        }

        return 1;
    }

    //计算滚球 赛事进行时间
    public static function secTime($sec=0){
        $min = floor($sec/60);
        $res = $min.':'.($sec-$min*60);
        return $res;
    }
}