sports_dapi/app/Http/Model/StZqResult.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;


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

    /*
     * 写赛事结果
     * 弃用
     */
    public static function ZQresult__($model){
        //获取赛事表7天内所有赛事
        $matchData = $model['model_match']::select('id','home_team','guest_team','lg_id','status','tag','match_date','match_time')
            ->where([['ctime','>',date('Y-m-d H:i:s', strtotime("-1 day"))]])
            ->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("-1 day"))]])
            ->get()
            ->toarray();

        //获取赛事对应结果比分
        //获取赛事id
        $match_ids = [];
        foreach($matchData as $k =>$v){
            //只获取赛事已结束的
            if($v['status'] == 2){
                $match_ids[] = $v['id'];
            }
        }
        $match_ids_str = implode(",", $match_ids);
        
        //上半场
        $sql_h = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_result_record where match_process = '上半场' and match_id IN ($match_ids_str) group by match_id)b
        where a.match_id = b.match_id and a.id = b.id ";

        //全场
        $sql_f = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer,a.home_rate,a.guest_rate from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_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_h = DB::select($sql_h);

        //全场最终结果
        $match_result_f = DB::select($sql_f);
        //拼装赛事结果数据
        $match_result_record = [];
        if(!empty($match_result_h) and !empty($match_result_f)){
            foreach($match_result_h as $k=>$v){
                foreach($match_result_f as $kk=>$vv){
                    if($v->match_id == $vv->match_id){
                        $match_result_record[$k] = [
                            "match_id"=>$v->match_id,
                            "home_score"=> $vv->home_score,
                            "guest_score"=> $vv->guest_score,
                            "a_time"=> $vv->a_time,
                            "match_process"=> $vv->match_process,
                            "all_goal"=> $vv->all_goal,
                            "first_score"=> $vv->first_score,
                            "last_score"=> $vv->last_score,
                            "match_score"=> $vv->match_score,
                            "match_winer"=> $vv->match_winer,
                            "u_home_score"=> $v->home_score,
                            "u_guest_score"=> $v->guest_score,
                            "home_rate"=> $vv->home_rate,
                            "guest_rate"=> $vv->guest_rate,
                        ];
                    }
                }
            }
        }
        

        //结果表无数据，直接插入
        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);
                foreach($match_result_record as $kk =>$vv){        
                    if($v['id'] == $vv['match_id']){
                        $set_match_r[] = [
                            "match_id"=> $v['id'],
                            "home_team"=>$v['home_team'],
                            "guest_team"=>$v['guest_team'],
                            "lg_id"=>$v['lg_id'],
                            "status"=>$v['status'],
                            "tag"=> $v['tag'],
                            'match_time'=>$vv['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_rate"=> $vv['home_rate']?:0, //主队让球
                            "guest_rate"=> $vv['guest_rate']?:0, //客队让球
                            "home_score"=> $vv['home_score']?:0, //主队进球数
                            "guest_score"=> $vv['guest_score']?:0, //客队进球数
                            "all_goal"=> $vv['all_goal']?:0, //总进球数
                            "first_score"=> $vv['first_score']?:'', //最先进球球队
                            "last_score"=> $vv['last_score']?:'', //最后进球球队
                            "match_score"=> $vv['match_score']?:0, //赛事比分
                            "match_winer"=>  $vv['match_winer']?:'',//获胜球队
                            "match_process"=> $vv['match_process']?:'',//比赛进程
                            "u_home_score"=> $vv['u_home_score']?:0,//上半场主队进球数
                            "u_guest_score"=> $vv['u_guest_score']?:0,//上半场客队进球数
                        ];                        
                    }                   
                }
            }
            /*
            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));
            }
            */
            
        }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);
                    foreach($match_result_record as $kk =>$vv){        
                        if($v['id'] == $vv['match_id']){
                            $set_match_r[] = [
                                "match_id"=> $v['id'],
                                "home_team"=>$v['home_team'],
                                "guest_team"=>$v['guest_team'],
                                "lg_id"=>$v['lg_id'],
                                "status"=>$v['status'],
                                "tag"=> $v['tag'],
                                'match_time'=>$vv['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_rate"=> $vv['home_rate']?:0, //主队让球
                                "guest_rate"=> $vv['guest_rate']?:0, //客队让球
                                "home_score"=> $vv['home_score']?:0, //主队进球数
                                "guest_score"=> $vv['guest_score']?:0, //客队进球数
                                "all_goal"=> $vv['all_goal']?:0, //总进球数
                                "first_score"=> $vv['first_score']?:'', //最先进球球队
                                "last_score"=> $vv['last_score']?:'', //最后进球球队
                                "match_score"=> $vv['match_score']?:0, //赛事比分
                                "match_winer"=>  $vv['match_winer']?:'',//获胜球队
                                "match_process"=> $vv['match_process']?:'',//比赛进程
                                "u_home_score"=> $vv['u_home_score']?:0,//上半场主队进球数
                                "u_guest_score"=> $vv['u_guest_score']?:0,//上半场客队进球数
                            ];                      
                        }                   
                    }
                }
            }
        }

        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;
    }

    /*
     * 写赛事结果
     */
    public static function ZQresult_v1($model){
        //获取当天开始并且已结束的所有赛事
        $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_h = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_result_record where match_process = '半场' and match_id IN ($match_ids_str) group by match_id)b
        where a.match_id = b.match_id and a.id = b.id ";

        //全场
        $sql_f = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer,a.home_rate,a.guest_rate from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_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_h = DB::select($sql_h);

        //全场最终结果
        $match_result_f = DB::select($sql_f);

        //拼装赛事结果数据
        $match_result_record = [];
        if(!empty($match_result_h) and !empty($match_result_f)){
            foreach($match_result_h as $k=>$v){
                foreach($match_result_f as $kk=>$vv){
                    if($v->match_id == $vv->match_id){
                        $match_result_record[$k] = [
                            "match_id"=>$v->match_id,
                            "home_score"=> $vv->home_score,
                            "guest_score"=> $vv->guest_score,
                            "a_time"=> $vv->a_time,
                            "match_process"=> $vv->match_process,
                            "all_goal"=> $vv->all_goal,
                            "first_score"=> $vv->first_score,
                            "last_score"=> $vv->last_score,
                            "match_score"=> $vv->match_score,
                            "match_winer"=> $vv->match_winer,
                            "u_home_score"=> $v->home_score,
                            "u_guest_score"=> $v->guest_score,
                            "home_rate"=> $vv->home_rate,
                            "guest_rate"=> $vv->guest_rate,
                        ];
                    }
                }
            }
        }
        

        //组装赛果数据
        if(!empty($match_result_record)){
            foreach ($matchData as $k=>$v){
                $start_time = ($v['match_date'].' '.$v['match_time']);
                $time = time()-strtotime($v['match_time']);
                $match_time = self::secTime($time);
                foreach($match_result_record as $kk =>$vv){        
                    if($v['id'] == $vv['match_id']){
                        //获取获胜球队
                        if(($vv['home_score']) > ($vv['guest_score'])){
                            $match_winer = $v['home_team'];
                        }else{
                            $match_winer = $v['guest_team'];
                        }
                        $set_match_r[] = [
                            "match_id"=> $v['id'],
                            "home_team"=>$v['home_team'],
                            "guest_team"=>$v['guest_team'],
                            "lg_id"=>$v['lg_id'],
                            "status"=>$v['status'],
                            "tag"=> $v['tag'],
                            'match_time'=>$vv['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_rate"=> $vv['home_rate']?:0, //主队让球
                            "guest_rate"=> $vv['guest_rate']?:0, //客队让球
                            "home_score"=> $vv['home_score']?:0, //主队进球数
                            "guest_score"=> $vv['guest_score']?:0, //客队进球数
                            "all_goal"=> $vv['all_goal']?:0, //总进球数
                            // "first_score"=> $vv['first_score']?:'', //最先进球球队
                            // "last_score"=> $vv['last_score']?:'', //最后进球球队
                            "match_score"=> $vv['match_score']?:0, //赛事比分
                            "match_winer"=>  $match_winer?:'',//获胜球队
                            "match_process"=> $vv['match_process']?:'',//比赛进程
                            "u_home_score"=> $vv['u_home_score']?:0,//上半场主队进球数
                            "u_guest_score"=> $vv['u_guest_score']?:0,//上半场客队进球数
                        ];                        
                    }                   
                }
            }
        }

        //写入赛果
        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 ZQresult_v2($model,$match_id = 0){
        //如果有赛事id 则只更新当前赛事结果
        if($match_id > 0){
            $match_ids_str = $match_id;
        }else{
            //获取两天内的结束赛事
            $matchData = $model['model_match']
                ->join('st_zq_result','st_zq_result.match_id','=','st_zq_competition.id')
                ->select('st_zq_competition.id','is_correct')
                ->where([['st_zq_competition.match_date','>',date('Y-m-d', strtotime("-2 day"))],['st_zq_competition.status','=',2],['st_zq_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_h = "select a.match_id,a.home_team,a.guest_team,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_result_record where match_process = '半场' and match_id IN ($match_ids_str) group by match_id)b
        where a.match_id = b.match_id and a.id = b.id ";

        //全场
        $sql_f = "select a.match_id,a.home_team,a.guest_team,a.home_score,a.guest_score,a.match_time as a_time,a.match_process,a.all_goal,a.first_score,a.last_score,a.match_score,a.match_winer,a.home_rate,a.guest_rate from st_zq_result_record a,
        (select match_id,max(id) id from st_zq_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_h = DB::select($sql_h);

        //全场最终结果
        $match_result_f = DB::select($sql_f);

        //拼装赛事结果数据
        $match_result_record = [];
        if(!empty($match_result_h) and !empty($match_result_f)){
            foreach($match_result_h as $k=>$v){
                foreach($match_result_f as $kk=>$vv){
                    if($v->match_id == $vv->match_id){
                        $match_result_record[$k] = [
                            "match_id"=>$v->match_id,
                            "home_team"=>$v->home_team,
                            "guest_team"=>$v->guest_team,
                            "home_score"=> $vv->home_score,
                            "guest_score"=> $vv->guest_score,
                            "a_time"=> $vv->a_time,
                            "match_process"=> $vv->match_process,
                            "all_goal"=> $vv->all_goal,
                            "first_score"=> $vv->first_score,
                            "last_score"=> $vv->last_score,
                            "match_score"=> $vv->match_score,
                            "match_winer"=> $vv->match_winer,
                            "u_home_score"=> $v->home_score,
                            "u_guest_score"=> $v->guest_score,
                            "home_rate"=> $vv->home_rate,
                            "guest_rate"=> $vv->guest_rate,
                        ];
                    }
                }
            }
        }

        if(!empty($match_result_record)){
            foreach($match_result_record as $k=>$v){
                //获取获胜球队
                if(($v['home_score']) > ($v['guest_score'])){
                    $match_winer = $v['home_team'];
                }else{
                    $match_winer = $v['guest_team'];
                
                }

                $set_match_r = [
                    "match_id"=> $v['match_id'],
                    "update_time"=>date('Y-m-d H:i:s'),
                    "status"=>2,//已结束
                    "home_rate"=> $v['home_rate']?:0, //主队让球
                    "guest_rate"=> $v['guest_rate']?:0, //客队让球
                    "home_score"=> $v['home_score']?:0, //主队进球数
                    "guest_score"=> $v['guest_score']?:0, //客队进球数
                    "all_goal"=> $v['all_goal']?:0, //总进球数
                    // "first_score"=> $vv['first_score']?:'', //最先进球球队
                    // "last_score"=> $vv['last_score']?:'', //最后进球球队
                    "match_score"=> $v['match_score']?:0, //赛事比分
                    "match_winer"=>  $match_winer?:'',//获胜球队
                    "match_process"=> $v['match_process']?:'',//比赛进程
                    "u_home_score"=> $v['u_home_score']?:0,//上半场主队进球数
                    "u_guest_score"=> $v['u_guest_score']?:0,//上半场客队进球数
                    "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;
    }

    /**
     * 写入预植赛果
     */
    public static function set_result($opt = []){
        if(!empty($opt)){

            //赛事数据
            $data = $opt['data'];
            //获取开始时间
            $start_time = ($data['match_date'].' '.$data['match_time']);

            $match_result = [
                "match_id"=> $opt['match_id'],
                "lg_id"=>$opt['lg_id'],
                "status"=>$data['status'],
                "tag"=> 0,
                'match_time'=>0,//比赛进行时间
                "ctime"=>date('Y-m-d H:i:s'),
                "update_time"=>date('Y-m-d H:i:s'),
                "is_correct" => -1,
                "start_time"=>date('Y-m-d H:i:s',strtotime($start_time)),
            ];

            //根据球类赋值
            if($opt['game_code'] == 'zq'){//足球
                $match_result['home_team'] = $data['home_team'];
                $match_result['guest_team'] = $data['guest_team'];
            }
            if($opt['game_code'] == 'lq'){//篮球
                $match_result['home_team'] = $data['home_team'];
                $match_result['guest_team'] = $data['guest_team'];
            }
            if($opt['game_code'] == 'wq'){//网球
                $match_result['home_player_name'] = $data['home_team'];
                $match_result['guest_player_name'] = $data['guest_team'];
            }
            if($opt['game_code'] == 'bq'){//棒球
                $match_result['home_team'] = $data['home_team'];
                $match_result['guest_team'] = $data['guest_team'];
            }

            //获取model
            $models = $opt['models'];

            //执行写入
            $ret_id = $models['model_result']::insertGetId($match_result);

            //如果失败，返回异常
            if ($ret_id < 1) throw new \Exception(Response::generate($opt['gameName'] . '赛事-match_id:' . $opt['match_id'] . ';', Response::SET_MATCH_ERR));
        }
    }
}