Sports pick' em website returns incorrect data

[Jay-Tea]

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

 

[Jay-Tea]

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?