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 countryId, 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 .
        " AND homeScore <= 0" .
        " ORDER BY gameDate ASC";
        
        
        $result = $this->getSqlAsArray($sql);

        return $this->prepareMatches($result, $leagueId, $countryId);
    }
    
    private function prepareMatches($matches, $leagueId, $countryId) {
        for ($i = 0; $i < count($matches); $i++) {

            $match = $matches[$i];
            $league = $this->getLeagueInfo($leagueId);

            $lookbackHome = $league[0]['lookbackHome'];
            $lookbackDraw = $league[0]['lookbackDraw'];
            $lookbackAway = $league[0]['lookbackAway'];
            $betMarginHome = $league[0]['betMarginHome'];
            $betMarginDraw = $league[0]['betMarginDraw'];
            $betMarginAway = $league[0]['betMarginAway'];
            
            $homeTeamResultsHome = $this->getTeamResults($match['homeTeam'], $lookbackHome, true)[0];
            $homeTeamResultsAway = $this->getTeamResults($match['awayTeam'], $lookbackHome, false)[0];
            
            $drawTeamResultsHome = $this->getTeamResults($match['homeTeam'], $lookbackDraw, true)[0];
            $drawTeamResultsAway = $this->getTeamResults($match['awayTeam'], $lookbackDraw, false)[0];
            
            $awayTeamResultsHome = $this->getTeamResults($match['homeTeam'], $lookbackAway, true)[0];
            $awayTeamResultsAway = $this->getTeamResults($match['awayTeam'], $lookbackAway, false)[0];
            
            $homeTeamCountHome = $homeTeamResultsHome['wins'] + $homeTeamResultsHome['draws'] + $homeTeamResultsHome['lost'];
            $homeTeamCountAway = $homeTeamResultsAway['wins'] + $homeTeamResultsAway['draws'] + $homeTeamResultsAway['lost'];

            $drawTeamCountHome = $drawTeamResultsHome['wins'] + $drawTeamResultsHome['draws'] + $drawTeamResultsHome['lost'];
            $drawTeamCountAway = $drawTeamResultsAway['wins'] + $drawTeamResultsAway['draws'] + $drawTeamResultsAway['lost'];
            
            $awayTeamCountHome = $awayTeamResultsHome['wins'] + $awayTeamResultsHome['draws'] + $awayTeamResultsHome['lost'];
            $awayTeamCountAway = $awayTeamResultsAway['wins'] + $awayTeamResultsAway['draws'] + $awayTeamResultsAway['lost'];
            
            
            $homeWinPercent = round((($homeTeamResultsHome['wins'] + $homeTeamResultsAway['lost']) / ($homeTeamCountHome + $homeTeamCountAway)) * 100,2);
            $drawPercent = round((($drawTeamResultsHome['draws'] + $drawTeamResultsAway['draws']) / ($drawTeamCountHome + $drawTeamCountAway)) * 100, 2);
            $awayWinPercent = round((($awayTeamResultsAway['wins'] + $awayTeamResultsHome['lost']) / ($awayTeamCountHome + $awayTeamCountAway)) * 100, 2);
            
            $match['homeWinPercent'] = $homeWinPercent;
            $match['drawPercent'] = $drawPercent;
            $match['awayWinPercent'] = $awayWinPercent;
            $matches[$i] = $match;
        }
        return $matches;
    }
    
    public 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);
    }
    
    public function addBet($matchId, $betOdds, $betAmount, $betType) {
        
        $sql = "INSERT INTO Bets (matchId, odds, amount, betOn) VALUES ($matchId, $betOdds, $betAmount, '$betType')";
        
        $this->getSqlAsArray($sql);
    }
    
    private function getGamesBySeason($leagueId, $season) {
        
    }
    
    public function getBettingStats($leagueId) {
        $sql = "SELECT * FROM Bets INNER JOIN SoccerResults sr ON matchId = sr.id WHERE leagueId = $leagueId AND Season = (SELECT MAX(season) FROM SoccerResults WHERE leagueId = $leagueId)";
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getBettingSummary() {
        $sql = "SELECT *, l.name as leagueName FROM Bets INNER JOIN SoccerResults sr ON matchId = sr.id INNER JOIN League l ON leagueId = l.id INNER JOIN Country c ON l.countryId = c.id WHERE DATE(gameDate) > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)";
        
        return $this->getSqlAsArray($sql);
    }
    
    public function getDayBettingsStats() {
        $sql = "SELECT DATE(gameDate) as date, ROUND(SUM(
    CASE
        WHEN betOn = '1' AND sr.homeScore > sr.awayScore THEN (amount * odds) -amount
        WHEN betOn = 'X' AND sr.homeScore = sr.awayScore THEN (amount * odds) -amount
        WHEN betOn = '2' AND sr.homeScore < sr.awayScore THEN (amount * odds) -amount
		ELSE -amount
	END
),2 ) as sumAmount, 
SUM(
    CASE
        WHEN betOn = '1' AND sr.homeScore > sr.awayScore AND homeScore > -1 AND awayScore > -1 THEN 1
        WHEN betOn = 'X' AND sr.homeScore = sr.awayScore AND homeScore > -1 AND awayScore > -1 THEN 1
        WHEN betOn = '2' AND sr.homeScore < sr.awayScore AND homeScore > -1 AND awayScore > -1 THEN 1
		ELSE 0
	END as wins, 
SUM(
    CASE
        WHEN homeScore <= -1 AND awayScore <= -1 THEN 1
		ELSE 0
	END as pending
SUM(amount) as betAmount, 
COUNT(gameDate) as numGames
FROM `Bets` INNER JOIN SoccerResults sr ON matchId = sr.id GROUP BY DATE(sr.gameDate) ORDER BY sr.gameDate ASC";

        return $this->getSqlAsArray($sql);
    }
}