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 "

League
"; 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 "

HomeTeam
"; var_dump($homeTeamResults); echo "

AwayTeam
"; 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 "

matchDoneEdit added percentages " . $homeWinPercent . ", $drawPercent, $awayWinPercent
"; 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); } }