OddsWebspace/SoccerMatchAnalysis.php

<?php

include_once 'Database.php';
include_once 'SoccerMatch.php';

class SoccerMatchAnalysis
{
    private SoccerMatch $match;
    private Database $database;
    private $leagueTable;
    private $leagueTableUpdated;

    public function __construct(SoccerMatch $match)
    {
        $this->match = $match;
        $this->database = new Database();
    }

    /**
     * Antalet mål som hemmalaget gjort de senaste "gamesLookback" matcherna -
     * antalet mål för bortalaget under samma period
     *
     * @param int $gamesLookback - hur många matcher bakåt i tiden som ska kontrolleras
     * @return float med skillnaden i mål mellan lagen hemma/borta
     * @throws \Exception
     */
    public function getScoringDiffLastGames(int $gamesLookback): float
    {
        $homeRes = 0;
        $awayRes = 0;

        $sql =
            'SELECT * FROM ' .
            '(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) ht ' .
            'UNION DISTINCT ' .
            '(SELECT * FROM SoccerResults WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?)';

        $stmt = $this->database->getConnection()->prepare($sql);

        $homeTeamId = $this->match->getHomeTeam()->getTeamId();
        $leagueId = $this->match->getHomeTeam()->getTeamLeagueId();
        $gameDate = $this->match->getGameDate();
        $awayTeamId = $this->match->getAwayTeam()->getTeamId();

        $stmt->bindParam(1, $homeTeamId, PDO::PARAM_INT);
        $stmt->bindParam(2, $leagueId, PDO::PARAM_INT);
        $stmt->bindParam(3, $gameDate);
        $stmt->bindParam(4, $gamesLookback, PDO::PARAM_INT);

        $stmt->bindParam(5, $awayTeamId, PDO::PARAM_INT);
        $stmt->bindParam(6, $leagueId, PDO::PARAM_INT);
        $stmt->bindParam(7, $gameDate);
        $stmt->bindParam(8, $gamesLookback, PDO::PARAM_INT);

        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $row) {
            if (
                $this->match->getHomeTeam()->getTeamId() ===
                (int) $row['homeTeamId']
            ) {
                $homeRes += (int) $row['homeScore'];
            }
            if (
                $this->match->getAwayTeam()->getTeamId() ===
                (int) $row['awayTeamId']
            ) {
                $awayRes += (int) $row['awayScore'];
            }
        }

        return $homeRes - $awayRes;
    }

    /**
     *
     * Plocka fram antalet mål som ett specifikt lag gjort under de senaste
     * <gamesLookback> matcherna
     *
     * @param int $gamesLookback - hur många matcher bakåt i tiden som ska kontrolleras
     * @param bool $homeTeam      - är det hemma laget som ska kontrolleras i matchen
     * @return int antalet mål som är gjorda av bestämt lag.
     */
    public function scoringTotal(int $gamesLookback, bool $homeTeam): int
    {
        $result = 0;
        $sql =
            'SELECT * FROM ' .
            '(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) ht ' .
            'UNION DISTINCT ' .
            '(SELECT * FROM SoccerResults WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?)';

        try {
            $stat = $this->database->getConnection()->prepare($sql);
            $team = $homeTeam
                ? $this->match->getHomeTeam()
                : $this->match->getAwayTeam();

            $stat->bindValue(1, $team->getTeamId(), PDO::PARAM_INT);
            $stat->bindValue(2, $team->getTeamLeagueId(), PDO::PARAM_INT);
            $stat->bindValue(3, $this->match->getGameDate());
            $stat->bindValue(4, $gamesLookback, PDO::PARAM_INT);
            $stat->bindValue(5, $team->getTeamId(), PDO::PARAM_INT);
            $stat->bindValue(6, $team->getTeamLeagueId(), PDO::PARAM_INT);
            $stat->bindValue(7, $this->match->getGameDate());
            $stat->bindValue(8, $gamesLookback, PDO::PARAM_INT);

            $stat->execute();
            $rs = $stat->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rs as $row) {
                if ($row['homeTeamId'] == $team->getTeamId()) {
                    $result += $row['homeScore'];
                } elseif ($row['awayTeamId'] == $team->getTeamId()) {
                    $result += $row['awayScore'];
                }
            }
        } catch (PDOException $e) {
            echo 'Error: ' . $e->getMessage();
        }
        return $result;
    }

    /**
     * Hämta tabell positionen för hemma eller borta laget
     *
     * @param bool $homeTeam - är det hemma laget som ska kontrolleras för matchen?
     * @return int position för specifierat lag
     */
    public function getTablePosition(bool $homeTeam): int
    {
        $result = 0;
        $this->updateLeagueTable();
        $teamName = $homeTeam
            ? $this->match->getHomeTeam()->getTeamName()
            : $this->match->getAwayTeam()->getTeamName();
        $standingOptional = array_filter($this->leagueTable, function ($p) use (
            $teamName
        ) {
            return $p->getTeamName() == $teamName;
        });

        if (!empty($standingOptional)) {
            $standing = reset($standingOptional);
            $result = array_search($standing, $this->leagueTable);
        }
        return $result;
    }

    private function updateLeagueTable()
    {
        global $leagueTableUpdated, $leagueTable, $database, $match;
        if ($leagueTableUpdated != $match->getGameDate()->format('Y-m-d')) {
            $leagueTable = $database->getLeagueTable(
                $match->getHomeTeam()->getTeamLeagueId(),
                $match->getSeason(),
                $match->getHomeTeam()->getCountryId(),
                $match->getGameDate()->format('Y-m-d')
            );

            $leagueTableUpdated = $match->getGameDate()->format('Y-m-d');
        }
    }

    /**
     *
     * @return Integer - hur många platser det är mellan hemma och borta laget
     */
    public function diffInStanding()
    {
        global $leagueTable, $match;
        $result = 0;
        $this->updateLeagueTable();

        $homeTeamStandingOptional = array_values(
            array_filter($leagueTable, function ($p) use ($match) {
                return $p->getTeamName() ==
                    $match->getHomeTeam()->getTeamName();
            })
        );

        $awayTeamStandingOptional = array_values(
            array_filter($leagueTable, function ($p) use ($match) {
                return $p->getTeamName() ==
                    $match->getAwayTeam()->getTeamName();
            })
        );

        if (
            !empty($homeTeamStandingOptional) &&
            !empty($awayTeamStandingOptional)
        ) {
            $homeStanding = $homeTeamStandingOptional[0];
            $awayStanding = $awayTeamStandingOptional[0];

            $result =
                array_search($homeStanding, $leagueTable) -
                array_search($awayStanding, $leagueTable);
        }
        return $result;
    }

    /**
     *
     * Vinst förlust ratio för om man är enbart hemma eller bortalag.
     *
     * @param gamesLookback
     * @param homeTeam
     * @return int där vinst ger +1 lika ger 0 och förlust get -1
     */
    public function winLossRatio($gamesLookback, $homeTeam)
    {
        global $match, $database;
        $result = 0;

        $team = $homeTeam
            ? $this->match->getHomeTeam()
            : $this->match->getAwayTeam();

        $teamSql = $homeTeam ? 'homeTeamId = ? ' : 'awayTeamId = ? ';
        $sql =
            'SELECT * FROM SoccerResults WHERE ' .
            $teamSql .
            'AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?';

        $stmt = $database->getConnection()->prepare($sql);
        $stmt->bindValue(1, $team->getTeamId(), PDO::PARAM_INT);
        $stmt->bindValue(2, $team->getTeamLeagueId(), PDO::PARAM_INT);
        $stmt->bindValue(3, $this->match->getGameDate(), PDO::PARAM_STR);
        $stmt->bindValue(4, $gamesLookback, PDO::PARAM_INT);
        $stmt->execute();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $homeScore = $row['homeScore'];
            $awayScore = $row['awayScore'];
            if ($homeTeam) {
                if ($homeScore > $awayScore) {
                    $result++;
                } elseif ($homeScore < $awayScore) {
                    $result--;
                }
            } else {
                if ($homeScore > $awayScore) {
                    $result--;
                } elseif ($homeScore < $awayScore) {
                    $result++;
                }
            }
        }

        return $result;
    }

    public function winLossRatioHomeAndAway($homeTeam, $gamesLookback)
    {
        $result = 0;

        $sql = 'SELECT gameDate, homeTeamId, awayTeamId, homeScore, awayScore, leagueId
        FROM
        (SELECT gameDate, homeTeamId, awayTeamId, homeScore, awayScore, leagueId
        FROM SoccerResults
        WHERE homeTeamId = ? AND leagueId = ? AND gameDate < ?
        ORDER BY gameDate DESC
        LIMIT ?) a
        UNION ALL
        SELECT gameDate, homeTeamId, awayTeamId, homeScore, awayScore, leagueId
        FROM SoccerResults
        WHERE awayTeamId = ? AND leagueId = ? AND gameDate < ?
        ORDER BY gameDate DESC
        LIMIT ?';

        $team = $homeTeam
            ? $this->match->getHomeTeam()
            : $this->match->getAwayTeam();
        try {
            $teamId = $team->getTeamId();
            $leagueId = $team->getTeamLeagueId();
            $gameDate = $this->match->getGameDate();

            $stat = $this->database->getConnection()->prepare($sql);
            $stat->bindParam(1, $teamId, PDO::PARAM_INT);
            $stat->bindParam(2, $leagueId, PDO::PARAM_INT);
            $stat->bindParam(3, $gameDate, PDO::PARAM_STR);
            $stat->bindParam(4, $gamesLookback, PDO::PARAM_INT);
            $stat->bindParam(5, $teamId, PDO::PARAM_INT);
            $stat->bindParam(6, $leagueId, PDO::PARAM_INT);
            $stat->bindParam(7, $gameDate, PDO::PARAM_STR);
            $stat->bindParam(8, $gamesLookback, PDO::PARAM_INT);
            $stat->execute();
            $rs = $stat->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rs as $row) {
                if (
                    $row['homeTeamId'] == $team->getTeamId() &&
                    $row['homeScore'] > $row['awayScore']
                ) {
                    $result++;
                } elseif (
                    $row['awayTeamId'] == $team->getTeamId() &&
                    $row['awayScore'] > $row['homeScore']
                ) {
                    $result++;
                } else {
                    $result--;
                }
            }
        } catch (PDOException $e) {
            echo $e->getMessage();
        }

        return $result;
    }

    public function goalsScoredHomeAndAway($homeTeam, $gameLookback)
    {
        $result = 0;
        $team = $homeTeam
            ? $this->match->getHomeTeam()
            : $this->match->getAwayTeam();

        $sql =
            'SELECT * FROM ' .
            '(SELECT * FROM SoccerResults WHERE homeTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) a ' .
            'UNION ALL ' .
            '(SELECT * FROM SoccerResults WHERE awayTeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ?) ' .
            'ORDER BY gameDate DESC ' .
            'LIMIT ?';

        try {
            $stat = $this->database->getConnection()->prepare($sql);
            $stat->bindParam(1, $team->getTeamId());
            $stat->bindParam(2, $team->getTeamLeagueId());
            $stat->bindParam(3, $this->match->getGameDate());
            $stat->bindParam(4, $gameLookback, PDO::PARAM_INT);
            $stat->bindParam(5, $team->getTeamId());
            $stat->bindParam(6, $team->getTeamLeagueId());
            $stat->bindParam(7, $this->match->getGameDate());
            $stat->bindParam(8, $gameLookback, PDO::PARAM_INT);
            $stat->bindParam(9, $gameLookback, PDO::PARAM_INT);

            $stat->execute();

            while ($row = $stat->fetch(PDO::FETCH_ASSOC)) {
                if ($row['homeTeamId'] == $team->getTeamId()) {
                    $result += $row['homeScore'];
                }
                if ($row['awayTeamId'] == $team->getTeamId()) {
                    $result += $row['awayScore'];
                }
            }
        } catch (PDOException $e) {
            $e->getMessage();
        }
        return $result;
    }

    public function goalsScoredHomeOrAway($homeTeam, $gameLookback)
    {
        $result = 0;
        $team = $homeTeam
            ? $this->match->getHomeTeam()
            : $this->match->getAwayTeam();
        $homeOrAway = $homeTeam ? 'home' : 'away';
        $sql =
            'SELECT SUM(' .
            $homeOrAway .
            'Score) FROM SoccerResults WHERE ' .
            $homeOrAway .
            'TeamId = ? AND leagueId = ? AND DATE(gameDate) < ? ORDER BY gameDate DESC LIMIT ? ' .
            'ORDER BY gameDate DESC';

        try {
            $stat = $this->database->getConnection()->prepare($sql);
            $stat->bindParam(1, $team->getTeamId());
            $stat->bindParam(2, $team->getTeamLeagueId());
            $stat->bindParam(3, $this->match->getGameDate());
            $stat->bindParam(4, $gameLookback, PDO::PARAM_INT);

            $stat->execute();

            while ($row = $stat->fetch(PDO::FETCH_ASSOC)) {
                $result += $row[$homeOrAway . 'Score'];
            }
        } catch (PDOException $e) {
            $e->getMessage();
        }
        return $result;
    }
}