odds/OddsJavaFx/src/tests/PrioCountriesAll.java

package tests;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

import com.google.common.collect.Lists;

import data.GuiMysql;
import objects.League;
import objects.SoccerMatch;
import objects.Team;
import objects.TeamResults;

public class PrioCountriesAll {

	Connection conn;
	private ArrayList<League> prioLeagues;

	public void runTest() {
		float bank = 1000;
		float betAmount = 1;
		final float betLevel = 0.01f;

		final ArrayList<SoccerMatch> matches = getMatches();

		for (int i = 0; i < matches.size();) {

			final SoccerMatch soccerMatch = matches.get(i);

			final List<SoccerMatch> matchesThisDate = matches.stream()
					.filter(p -> p.getGameDate().format(DateTimeFormatter.ISO_DATE)
							.equals(soccerMatch.getGameDate().format(DateTimeFormatter.ISO_DATE)))
					.collect(Collectors.toList());

			final int matchesThisDateCount = matchesThisDate.size();
			betAmount = bank * betLevel;
			int gamesBettedOn = 0;
			int winsThisDay = 0;
			final float startBank = bank;
			for (final SoccerMatch sm : matchesThisDate) {
				final int leagueId = sm.getHomeTeam().getTeamLeagueId();
				final League activeLeague = prioLeagues.stream().filter(p -> p.getLeagueId() == leagueId).findFirst().get();

				final int betOnThisMatch = checkIfBet(sm, activeLeague);
				if (betOnThisMatch > 0) {
					gamesBettedOn++;
					if (betOnThisMatch == 1) { // Spela på hemma lag
						bank = bank - betAmount;
						if (sm.getHomeScore() > sm.getAwayScore()) {
							bank += betAmount * sm.getOdds1();
							winsThisDay++;
						}
					} else if (betOnThisMatch == 2) { // Spela på borta lag
						bank = bank - betAmount;

						if (sm.getAwayScore() > sm.getHomeScore()) {
							bank += betAmount * sm.getOdds2();
							winsThisDay++;
						}
					} else if (betOnThisMatch == 3) { // Spela på bort och hemma
						bank = bank - betAmount*2;
						if (sm.getHomeScore() > sm.getAwayScore()) {
							bank += betAmount * sm.getOdds1();
							winsThisDay++;
						}
						if (sm.getAwayScore() > sm.getHomeScore()) {
							bank += betAmount * sm.getOdds2();
							winsThisDay++;
						}
					}
				}
			}

			System.out.println(matchesThisDate.get(0).getGameDate() + " result " + (bank - startBank) + " current bank: " + bank +
					" bet on " + gamesBettedOn + "/" + matchesThisDateCount + " wins " + winsThisDay);
			i += matchesThisDate.size();

		}
	}

	private int checkIfBet(SoccerMatch sm, League league) {
		int returnValue = 0;
		final float betMarginDecimal = 1 + (league.getBetMargin() / 100f);

		final TeamResults homeTeamResults = GuiMysql.getInstance().getTeamResults(sm.getHomeTeam().getTeamId(), league.getLookback(), true);
		final TeamResults awayTeamResults = GuiMysql.getInstance().getTeamResults(sm.getAwayTeam().getTeamId(), league.getLookback(), false);

		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;

		final float homeOdds = 100 / homeWinPercent;
		final float drawOdds = 100 / drawPercent;
		final float awayOdds = 100 / awayWinPercent;

		if (homeOdds * betMarginDecimal <= sm.getOdds1()) {
			returnValue += 1;
		}
		if (awayOdds * betMarginDecimal <= sm.getOdds2()) {
			returnValue += 2;
		}

		return returnValue;
	}

	private ArrayList<SoccerMatch> getMatches() {
		int i = 0;
		final ArrayList<SoccerMatch> matches = Lists.newArrayList();

		prioLeagues = getPrioLeagues();

		final ArrayList<String> leagueIds = Lists.newArrayList();
		prioLeagues.forEach(p -> leagueIds.add(String.valueOf(p.getLeagueId())));

		final String 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 " +
				"res.leagueId IN (" +
				String.join(",", leagueIds) + ") " +
				"ORDER BY gameDate ASC";

		try {
			final PreparedStatement stmt = conn.prepareStatement(sql);

			final ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				i++;
				final SoccerMatch sm = new SoccerMatch();
				final Team homeTeam = new Team();
				final Team awayTeam = new Team();

				homeTeam.setTeamId(rs.getInt("homeTeam"));
				awayTeam.setTeamId(rs.getInt("awayTeam"));
				homeTeam.setTeamName(rs.getString("homeTeamName"));
				awayTeam.setTeamName(rs.getString("awayTeamName"));
				homeTeam.setTeamLeagueId(rs.getInt("leagueId"));
				awayTeam.setTeamLeagueId(rs.getInt("leagueId"));
				homeTeam.setCountryId(rs.getInt("countryId"));
				awayTeam.setCountryId(rs.getInt("countryId"));

				sm.setAwayScore(rs.getInt("awayScore"));
				sm.setHomeScore(rs.getInt("homeScore"));
				sm.setHomeTeam(homeTeam);
				sm.setAwayTeam(awayTeam);
				sm.setMatchId(rs.getInt("id"));
				sm.setOdds1(rs.getFloat("odds1"));
				sm.setOddsX(rs.getFloat("oddsX"));
				sm.setOdds2(rs.getFloat("odds2"));
				sm.setGameDate(LocalDateTime.parse(rs.getString("gameDate")));
				sm.setSeason(rs.getString("season"));

				matches.add(sm);
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		}

		return matches;
	}

	private ArrayList<League> getPrioLeagues() {
		final String sql = "SELECT * FROM League WHERE prio = 1";
		final ArrayList<League> result = Lists.newArrayList();
		try {
			final PreparedStatement stat = getConnection().prepareStatement(sql);
			final ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				result.add(new League(rs.getInt("id"), rs.getString("name"), rs.getInt("lookback"), rs.getInt("betMargin")));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	private Connection getConnection() {
		if (conn == null) {
			conn = GuiMysql.getInstance().getDbConnection();
		}

		return conn;
	}
}