sports_dapi/app/Http/Model/StLqResult.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 StLqResult
 * @package App\Http\Model
 * 篮球 赛事 结果
 */
class StLqResult extends Model
{
    protected $table = 'st_lq_result';
    public $timestamps = false;

    /*
     * 写赛事结果
     * 弃用
     */
    public static function LQresult___($model){
        dd(123);
        //获取赛事表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();
        //获取赛事结果表 15天内
        $matchData_r = $model['model_result']::select('match_id')
            ->where([['ctime','>',date('Y-m-d H:i:s', strtotime("-1 day"))]])
            ->get()
            ->toarray();
        //结果表无数据，直接插入
        if(empty($matchData_r)){
            foreach ($matchData as $k=>$v){
                $process = 0;
                if((int)$v['status'] == 2){
                    $process = 4;
                }
                if((int)$v['status'] == 1){
                    $process = 1;
                }
                $start_time = ($v['match_date'].' '.$v['match_time']);

                $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_process"=>$process,
                    'match_time'=>12,
                    "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){
                    $process = 0;
                    if((int)$v['status'] == 2){
                        $process = 4;
                    }
                    if((int)$v['status'] == 1){
                        $process = 1;
                    }
                    $start_time = ($v['match_date'].' '.$v['match_time']);

                    $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_process"=>$process,
                        'match_time'=>12,
                        "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 LQresult_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_1 = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.home_rate,a.guest_rate,a.match_process,a.first_score,a.last_score,a.match_score,a.match_winer from st_lq_result_record a,
        (select match_id,max(id) id from st_lq_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_2 = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.home_rate,a.guest_rate,a.match_process,a.first_score,a.last_score,a.match_score,a.match_winer from st_lq_result_record a,
        (select match_id,max(id) id from st_lq_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_3 = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.home_rate,a.guest_rate,a.match_process,a.first_score,a.last_score,a.match_score,a.match_winer from st_lq_result_record a,
        (select match_id,max(id) id from st_lq_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_4 = "select a.match_id,a.home_score,a.guest_score,a.match_time as a_time,a.home_rate,a.guest_rate,a.match_process,a.first_score,a.last_score,a.match_score,a.match_winer from st_lq_result_record a,
        (select match_id,max(id) id from st_lq_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 ";


        //第一节最终结果
        $match_result_1 = DB::select($sql_1);
        //第二节最终结果
        $match_result_2 = DB::select($sql_2);
        //第三节最终结果
        $match_result_3 = DB::select($sql_3);
        //第四节最终结果
        $match_result_4 = DB::select($sql_4);

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

        if(!empty($match_result_1) and !empty($match_result_2) and !empty($match_result_3) and !empty($match_result_4)){

            foreach($matchData as $k =>$v ){
                //获取开赛时间
                $start_time = ($v['match_date'].' '.$v['match_time']);
                $time = time()-strtotime($v['match_time']);
                $match_time = self::secTime($time);
                //获取赛事每节的赛果
                $result_1 = commonFunction::filter_by_value($match_result_1,'match_id',$v['id']);
                $result_2 = commonFunction::filter_by_value($match_result_2,'match_id',$v['id']);
                $result_3 = commonFunction::filter_by_value($match_result_3,'match_id',$v['id']);
                $result_4 = commonFunction::filter_by_value($match_result_4,'match_id',$v['id']);


                if(!empty($result_1) and !empty($result_2) and !empty($result_3) and !empty($result_4)){

                    //拼接赛事主队比分  {"1":0,"2":0,"3":0,"4":0}
                    $home_score = [
                        '1'=>$result_1['home_score'],
                        '2'=>$result_2['home_score'] - $result_1['home_score'],
                        '3'=>$result_3['home_score'] - $result_2['home_score'],
                        '4'=>$result_4['home_score'] - $result_3['home_score'],
                    ];
                    //拼接赛事客队比分
                    $guest_score = [
                        '1'=>$result_1['guest_score'],
                        '2'=>$result_2['guest_score'] - $result_1['guest_score'],
                        '3'=>$result_3['guest_score'] - $result_2['guest_score'],
                        '4'=>$result_4['guest_score'] - $result_3['guest_score'],
                    ];

                    //获取获胜球队
                    if(($result_4['home_score']) > ($result_4['guest_score'])){
                        $match_winer = $v['home_team'];
                    }else{
                        $match_winer = $v['guest_team'];
                    }

                    //获取总进球数
                    $all_goal = ($result_4['home_score'])+($result_4['guest_score']);

                    //赛事待写入赛果数据
                    $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'=>$result_4['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"=> $result_4['home_rate']?:0, //主队让球
                        "guest_rate"=> $result_4['guest_rate']?:0, //客队让球
                        "home_score"=> json_encode($home_score)?:'', //主队进球数
                        "guest_score"=> json_encode($guest_score)?:'', //客队进球数
                        "all_goal"=> $all_goal?:0, //总进球数
                        // "first_score"=> $result_4['first_score']?:'', //最先进球球队
                        // "last_score"=> $result_4['last_score']?:'', //最后进球球队
                        "match_score"=> $result_4['match_score']?:0, //赛事比分
                        "match_winer"=>  $match_winer?:'',//获胜球队
                        "match_process"=> $result_4['match_process']?:'',//比赛进程
                        "u_home_score"=> $result_2['home_score']?:0,//上半场主队进球数
                        "u_guest_score"=> $result_2['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 LQresult_v2($model,$match_id=0){
        //如果有赛事id 则只更新当前赛事结果
        if($match_id > 0){
            $match_ids_str = $match_id;
            $match_ids = [$match_id];
        }else{
            //获取两天内的结束赛事
            $matchData = $model['model_match']
                ->join('st_lq_result','st_lq_result.match_id','=','st_lq_competition.id')
                ->select('st_lq_competition.id','is_correct')
                ->where([['st_lq_competition.match_date','>',date('Y-m-d', strtotime("-2 day"))],['st_lq_competition.status','=',2],['st_lq_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);
        }

        //如果有赛事id 获取结果中的手动数据
        if(!empty($match_ids)){
            $manual_result_data = self::select('match_id','manual_result')
                ->whereIn('match_id',$match_ids)
                ->get()
                ->toArray();
        }


        //拼接sql
        $sql_result = "select a.match_id,a.home_team,a.guest_team,a.home_score,a.guest_score,a.match_time as a_time,a.home_rate,a.guest_rate,a.match_process,a.first_score,a.last_score,a.match_score,a.match_winer,a.result_mark from st_lq_result_record a,
        (select match_id,max(id) id from st_lq_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){
                //获取结果json
                $mark_result = json_decode($v->result_mark,true);
                //如果为空，设默认值
                if($mark_result == []){
                    $mark_result_json = '{"schedule":"ot","r_time":"00:00","sc_1th_H":0,"sc_1th_C":0,"sc_2th_H":0,"sc_2th_C":0,"sc_3th_H":0,"sc_3th_C":0,"sc_4th_H":0,"sc_4th_C":0,"overtime_H":0,"overtime_C":0,"half_H":0,"half_C":0,"full_H":0,"full_C":0,"whole_H":0,"whole_C":0}';
                    $mark_result = json_decode($mark_result_json,true);
                }
                //获取总进球数
                $all_goal = $mark_result['whole_H']+$mark_result['whole_C'];
                //获取比分
                $match_score = $mark_result['whole_H'].':'.$mark_result['whole_C'];
                //获取获胜球队
                if(($mark_result['whole_H']) > ($mark_result['whole_C'])){
                    $match_winer = $v->home_team;
                }else{
                    $match_winer = $v->guest_team;
                }
                //拼接赛事主队比分  {"1":0,"2":0,"3":0,"4":0}
                $home_score = [
                    '1'=>$mark_result['sc_1th_H'],
                    '2'=>$mark_result['sc_2th_H'],
                    '3'=>$mark_result['sc_3th_H'],
                    '4'=>$mark_result['sc_4th_H'],
                ];
                //拼接赛事客队比分
                $guest_score = [
                    '1'=>$mark_result['sc_1th_C'],
                    '2'=>$mark_result['sc_2th_C'],
                    '3'=>$mark_result['sc_3th_C'],
                    '4'=>$mark_result['sc_4th_C'],
                ];
                //拼接赛事主客队比分
                $inning = [
                    1=>[
                        "home"=>$mark_result['sc_1th_H'],
                        "guest"=>$mark_result['sc_1th_C'],
                    ],
                    2=>[
                        "home"=>$mark_result['sc_2th_H'],
                        "guest"=>$mark_result['sc_2th_C'],
                    ],
                    3=>[
                        "home"=>$mark_result['sc_3th_H'],
                        "guest"=>$mark_result['sc_3th_C'],
                    ],
                    4=>[
                        "home"=>$mark_result['sc_4th_H'],
                        "guest"=>$mark_result['sc_4th_C'],
                    ],
                    5=>[//上半场
                        "home"=>$mark_result['sc_1th_H']+$mark_result['sc_2th_H'],
                        "guest"=>$mark_result['sc_1th_C']+$mark_result['sc_2th_C'],
                    ],
                    6=>[//下半场
                        "home"=>$mark_result['sc_3th_H']+$mark_result['sc_4th_H'],
                        "guest"=>$mark_result['sc_3th_C']+$mark_result['sc_4th_C'],
                    ],
                    'all'=>[
                        "home"=>$mark_result['sc_1th_H']+$mark_result['sc_2th_H']+$mark_result['sc_3th_H']+$mark_result['sc_4th_H'],
                        "guest"=>$mark_result['sc_1th_C']+$mark_result['sc_2th_C']+$mark_result['sc_3th_C']+$mark_result['sc_4th_C'],
                    ],

                ];

                //===追加获取手动结果数据===
                $manual_result_json = commonFunction::getManual_result($manual_result_data,$inning,$v->match_id);

                //赛事待写入赛果数据
                $set_match_r = [
                    "match_id"=> $v->match_id,
                    "status"=>2,
                    "update_time"=>date('Y-m-d H:i:s'),

                    "home_rate"=> $v->home_rate?:0, //主队让球
                    "guest_rate"=> $v->guest_rate?:0, //客队让球
                    "home_score"=> json_encode($home_score)?:'', //主队进球数
                    "guest_score"=> json_encode($guest_score)?:'', //客队进球数
                    "inning"=> json_encode($inning)?:'', //主客队进球数
                    "all_goal"=> $all_goal?:0, //总进球数
                    // "first_score"=> $result_4['first_score']?:'', //最先进球球队
                    // "last_score"=> $result_4['last_score']?:'', //最后进球球队
                    "match_score"=> $match_score?:0, //赛事比分
                    "match_winer"=>  $match_winer?:'',//获胜球队
                    "match_process"=> $mark_result['schedule']?:'',//比赛进程
                    "u_home_score"=> $mark_result['half_H']?:0,//上半场主队进球数
                    "u_guest_score"=> $mark_result['half_C']?:0,//上半场客队进球数
                    "is_correct"=> -1,//自动比分
                    "manual_result"=> $manual_result_json,//手动结果

                ];

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