Jump to content
Jay-Tea

Sports pick' em website returns incorrect data

Recommended Posts

Hello,

We had a script made for us, and one of the games is simply awarding a point (1) for a team that wins its game vs an opponent.  There is an API to a stat provider that returns something like this:

scores sport="basketball"
category name="USA: NBA" id="1046" file_group="nba-scores"
  match date="29.12.2018" timezone="EST" status="Final" time="5:00 PM" timer="" formatted_date="29.12.2018" id="140386"
  hometeam name="Milwaukee Bucks" posession="False" q1="29" q2="42" q3="35" q4="23" ot="" totalscore="129" id="1184
  awayteam name="Brooklyn Nets" posession="False" q1="23" q2="29" q3="34" q4="29" ot="" totalscore="115" id="8055
 

There is more to the API, but this is the relevant part.  It appears that if a team LEADS at any point during the game, they get a point.  When a team starts losing, or if they lose outright, the point is not taken away.  For instance in the game described above, Milwaukee and Brooklyn both got a "point", but Milwaukee should have "1" and Brooklyn should have "0".

My hypothesis is that comparison between teams needs to be done only when the "STATUS = FINAL" rather than continuously updating live. Any thoughts on how to add this parameter?  Here is the snippet of code that handles how the score is calculated, and how it updates the database: 

   public function update_pickem_scores_in_lineup($sports_id)
    {
        $this->db = $this->db_game;
        $current_game = $this->get_current_season_match($sports_id);
        //echo "<pre>";print_r($current_game);die;
        if(!empty($current_game))
        {
            $all_season_game_uid = array_column($current_game, 'season_game_uid');
            $all_season_game_uid_str = implode(',', array_map( function( $n ){ return '\''.$n.'\''; } ,  $all_season_game_uid) );
            
            $this->db = $this->db_fantasy;
            $collection_master_ids = $this->get_all_table_data ("collection_master_id", COLLECTION_SEASON, "season_game_uid IN (".$all_season_game_uid_str.")",true);
            //echo "<pre>";print_r($collection_master_ids);die;
            if(!empty($collection_master_ids))
            {
                $all_coll_id = array_column($collection_master_ids, 'collection_master_id');
                $coll_ids_str = implode(',', array_map( function( $n ){ return '\''.$n.'\''; } ,  $all_coll_id) );
                $where = "collection_master_id IN (".$coll_ids_str.")";
                
                $collection_result = $this->db->distinct()
                                     ->select("season_game_uid, collection_master_id")
                                     ->from(COLLECTION_SEASON) 
                                     ->where($where)
                                     ->get()->result_array();

                $final_collection_ids = array();
                foreach ($collection_result as $key => $value) {
                    $final_collection_ids[$value['collection_master_id']][] = $value['season_game_uid'];
                }   

                //echo "<pre>";print_r($final_collection_ids);die;
                if(!empty($final_collection_ids))
                {
                    foreach ($final_collection_ids as $collection_master_id => $collection_season) 
                    {
                        $this->db = $this->db_game;
                        $season_result = $this->db->select("home_uid,home_score,away_uid,away_score,season_game_uid")
                                 ->from(SEASON) 
                                 ->where_in("season_game_uid",$collection_season)
                                 ->get()->result_array();

                        $this->db = $this->db_fantasy;
                   
    foreach($season_result as $match){
                            $winner_id = "";
                            if($match['home_score'] > $match['away_score']){
                                $winner_id = $match['home_uid'];
                            }else if($match['home_score'] < $match['away_score']){
                                $winner_id = $match['away_uid'];
                            }

                            if($winner_id != ""){
                                $player_uid = "T".$winner_id;
                                $score = 1;

                                $sql = "UPDATE  
                                            " . $this->db->dbprefix(LINEUP) . "  AS LU
                                    LEFT JOIN             
                                            " . $this->db->dbprefix(LINEUP_MASTER) . " AS LM  ON LM.lineup_master_id = LU.lineup_master_id
                                    INNER JOIN 
                                            " . $this->db->dbprefix(LINEUP_MASTER_CONTEST) . " AS LMC ON LMC.lineup_master_id = LM.lineup_master_id
                                    SET 
                                            LU.score = ( CASE  
                                                            WHEN LU.captain = '1' THEN $score * 2
                                                            WHEN LU.captain = '2' THEN $score * 1.5
                                                            ELSE $score
                                                        END ) 

                                    WHERE
                                        LM.collection_master_id = '".$collection_master_id."' 
                                        AND LU.player_unique_id = '".$player_uid."'
                                        AND LM.team_type = '3' 
                                    ";
                                $this->db->query($sql);
                            }
                        }
                    }
                    $this->db = $this->db_fantasy;
                    // Update total score in LINEUP_MASTER_CONTEST
                    $lineup_sql = " SELECT 
                                    LM.lineup_master_id 
                                    FROM 
                                        " . $this->db->dbprefix(LINEUP) . " AS L 
                                    INNER JOIN " . $this->db->dbprefix(LINEUP_MASTER) . " AS LM ON LM.lineup_master_id = L.lineup_master_id
                                    INNER JOIN " . $this->db->dbprefix(COLLECTION_SEASON) . " AS CS  ON CS.collection_master_id = LM.collection_master_id 
                                    INNER JOIN " . $this->db->dbprefix(LINEUP_MASTER_CONTEST) . " AS LMC ON LMC.lineup_master_id = LM.lineup_master_id
                                    INNER JOIN " . $this->db->dbprefix(CONTEST) . " AS C ON C.contest_id = LMC.contest_id
                                    WHERE 
                                        CS.season_game_uid IN (" . $all_season_game_uid_str . ")
                                        AND LM.team_type = '3' 
                                    GROUP BY 
                                        LM.lineup_master_id
                            ";
                    $lineup_master_ids = $this->db->query($lineup_sql)->result_array();
         
                    if (!empty($lineup_master_ids))
                    {
                        $ids = array_column($lineup_master_ids, 'lineup_master_id');

                        $update_sql = " UPDATE 
                                            " . $this->db->dbprefix(LINEUP_MASTER_CONTEST) . " AS LMC
                                                INNER JOIN ( SELECT SUM(score) AS scores, L.lineup_master_id 
                                                                                FROM " . $this->db->dbprefix(LINEUP) . " AS L 
                                                                                WHERE L.lineup_master_id IN (" . implode(',', $ids) . ")  
                                                                                GROUP BY L.lineup_master_id 
                                                                        ) AS L_PQ ON L_PQ.lineup_master_id = LMC.lineup_master_id 
                                                SET 
                                                        LMC.total_score         =   IFNULL( L_PQ.scores,'0.00')
                                        ";

                        $this->db->query($update_sql);
                        
                        echo "Update lineup score for lineup_master_id:".implode(',', $ids);
                    } 
                    else 
                    {
                        echo "No lineup score update";

 

Thanks in advance!

JT

 

Share this post


Link to post
Share on other sites

Another option I thought of would be to specifically make the team that is losing "0" .  Could this be done using an ' else "0"  'somewhere?

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.