odds/OddsJavaFx/src/web/webDbConnection.php

<?php
mysqli_report( MYSQLI_REPORT_STRICT );

require_once __DIR__ . '/dbFunctions.php';

class WebDbConnection {
    private $dbCon;
    private $analyzerConnection;
    private $today;
    private $leagueName;
    public function __construct() {
        $this->dbCon = new dbFunctions();
    }
 
    private function getSqlAsArray($sql) {
        $mysql = $this->dbCon->getConnection();
        $result = $mysql->query( $sql );
        if ($result === false) {
            echo $mysql->error . " from sql " . $sql;
            return false;
        }
        $returnArray = array ();
        if (strpos( $sql, "UPDATE" ) === false) {
            foreach ( $result as $key => $value ) {
                $returnArray [$key] = $value;
            }
        }
        
        return $returnArray;
    }
  
    public function getCountries() {
        $mysql = $this->dbCon->getConnection();
        $sql = "SELECT id, name FROM Country WHERE id IN (SELECT countryId FROM SoccerResults WHERE DATE(gameDate) >= DATE(NOW())) GROUP BY name ORDER BY prio DESC, name ASC";

//         $result = $mysql->query( $sql );
//         if ($result === false) {
//             echo $result->error;
//         }
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getLeagues() {
        $mysql = $this->dbCon->getConnection();
        $sql = "SELECT id, name, countryId FROM League WHERE id IN (SELECT leagueId FROM SoccerResults WHERE DATE(gameDate) >= DATE(NOW())) GROUP BY name ORDER BY prio DESC, name ASC";
        
//         $result = $mysql->query( $sql );
//         if ($result === false) {
//             echo $result->error;
//         }
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getMatches($countryId, $leagueId ) {
        $mysql = $this->dbCon->getConnection();

        $sql = "SELECT res.*, " .
        "hTeam.name as homeTeamName, aTeam.name as awayTeamName " .
        "FROM SoccerResults as res " .
        "Join Team as hTeam ON res.homeTeam = hTeam.id " .
        "Join Team as aTeam ON res.awayTeam = aTeam.id " .
        "WHERE " .
        "DATE(gameDate) >= DATE(NOW()) " .
        "AND res.leagueId = " . $leagueId .
        " AND res.countryId = " . $countryId .
        " ORDER BY gameDate ASC";
        
        
        $result = $this->getSqlAsArray($sql);

        return $this->prepareMatches($result, $leagueId, $countryId);
    }
    
    private function prepareMatches($matches, $leagueId, $countryId) {
        foreach ($matches as $match) {

            var_dump($match);
            
            $league = $this->getLeagueInfo($leagueId);

            echo "</br></br> League </br>";
            var_dump($league[0]);
            
            $lookback = $league[0]['lookback'];
            $betMargin = $league[0]['betMargin'];
            
            $homeTeamResults = $this->getTeamResults($match['homeTeam'], $lookback, true)[0];
            $awayTeamResults = $this->getTeamResults($match['awayTeam'], $lookback, false)[0];
            
            echo "</br></br> HomeTeam </br>";
            var_dump($homeTeamResults);
            
            echo "</br></br> AwayTeam </br>";
            var_dump($awayTeamResults);
            $homeTeamCount = $homeTeamResults['wins'] + $homeTeamResults['draws'] + $homeTeamResults['lost'];
            $awayTeamCount = $awayTeamResults['wins'] + $awayTeamResults['draws'] + $awayTeamResults['lost'];
            
            $homeWinPercent = round((($homeTeamResults['wins'] + $awayTeamResults['lost']) / ($homeTeamCount + $awayTeamCount)) * 100,2);
            $drawPercent = round((($homeTeamResults['draws'] + $awayTeamResults['draws']) / ($homeTeamCount + $awayTeamCount)) * 100, 2);
            $awayWinPercent = round((($awayTeamResults['wins'] + $homeTeamResults['lost']) / ($homeTeamCount + $awayTeamCount)) * 100, 2);
            
            $match['homeWinPercent'] = $homeWinPercent;
            $match['drawPercent'] = $drawPercent;
            $match['awayWinPercent'] = $awayWinPercent;
            
            
            echo "</br></br> matchDoneEdit added percentages " . $homeWinPercent . ", $drawPercent, $awayWinPercent </br>";
            var_dump($match);
            die();
/*            
            
            final float homeWinPercent = (homeTeamResults.getWins() + awayTeamResults.getLosses()) / Float.valueOf(homeTeamResults.getCount() + awayTeamResults.getCount())* 100;
            final float drawPercent = (homeTeamResults.getDraws() + awayTeamResults.getDraws()) / Float.valueOf(homeTeamResults.getCount() + awayTeamResults.getCount()) * 100;
            final float awayWinPercent = (homeTeamResults.getLosses() + awayTeamResults.getWins()) / Float.valueOf(homeTeamResults.getCount() + awayTeamResults.getCount()) * 100;
            
            match.put("homeWin", String.format("%.02f", homeWinPercent) + "(" + String.format("%.02f", (100 / homeWinPercent) * betMargin) + ")");
            match.put("draw", String.format("%.02f", drawPercent) + "(" + String.format("%.02f", (100 / drawPercent ) * betMargin) + ")");
            match.put("awayWin", String.format("%.02f", awayWinPercent) + "(" + String.format("%.02f", (100 / awayWinPercent) * betMargin) + ")");
            
            matches.add(match);
        }
        
        originalMatches = matches;
        matchTable.getItems().addAll(matches);
        */
        }
        
    }
    
    private function getLeagueInfo($leagueId) {
        $sql = "SELECT * FROM League WHERE id = " . $leagueId;
        $result = $this->getSqlAsArray($sql);
        
        return $result;
    }
    
    public function getTeamResults($teamId, $numResults, $isHomeTeam) {
        $sql = "";
        if ($isHomeTeam) {
            $sql = "SELECT count(case when homeScore > awayScore then 1 end) wins, " .
            "count(case when awayScore > homeScore then 1 end) lost, " .
            "count(case when homeScore = awayScore then 1 end) draws " .
            "FROM (SELECT * FROM SoccerResults WHERE homeTeam = " . $teamId . " AND " .
            "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT " . $numResults . ") as t";
        } else {
            $sql = "SELECT count(case when homeScore < awayScore then 1 end) wins, " .
            "count(case when awayScore < homeScore then 1 end) lost, " .
            "count(case when homeScore = awayScore then 1 end) draws " .
            "FROM (SELECT * FROM SoccerResults WHERE awayTeam = " . $teamId . " AND " .
            "HomeScore >= 0 AND awayScore >= 0 AND DATE(gameDate) < DATE(NOW()) ORDER BY gameDate DESC LIMIT " . $numResults . ") as t";
        }
        
        return $this->getSqlAsArray($sql);
    }
}