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 * 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; } }