private Statistic getMostPlayedAgainst(Long team_id) {
StringBuilder sqlToQuery =
new StringBuilder(
"SELECT DISTINCT Team.id, Team.team_name, Team.image, COUNT(*) AS count_matches from Team, Game ");
sqlToQuery.append(
"WHERE (end_time != 0 AND Team.id = Game.home_team_id AND Game.visitor_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" ) ");
sqlToQuery.append("OR (end_time != 0 AND Game.home_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" And Team.id = Game.visitor_team_id ) ");
sqlToQuery.append("Group BY Team.id ORDER BY count_matches DESC LIMIT 1;");
ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
Team team = new Team();
Statistic statistic = new Statistic();
try {
while (resultset.next()) {
team.id = resultset.getLong("id");
team.team_name = resultset.getString("team_name");
team.image = resultset.getString("image");
statistic.count_most_played_against = resultset.getInt("count_matches");
}
resultset.close();
if (team.id != null && team.team_name != null) {
statistic.target_team = team;
}
} catch (SQLException e) {
System.out.println(e.toString());
}
return statistic;
}
// the difference is equal to 1. Matches that was played 10-9 or 9-10
private Statistic getMostRegularAppearances(Long team_id) {
StringBuilder sqlToQuery =
new StringBuilder(
"SELECT DISTINCT Team.id, Team.team_name, Team.image, count(*) AS count_matches FROM Game, Team ");
sqlToQuery.append("WHERE ( winner_id != ");
sqlToQuery.append(team_id);
sqlToQuery.append(" ) ");
sqlToQuery.append("AND (( Team.id = Game.home_team_id AND Game.visitor_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" ) ");
sqlToQuery.append("OR ( Game.home_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" AND Team.id = Game.visitor_team_id ))");
sqlToQuery.append(
" AND (( home_score=10 AND visitor_score=9 ) OR ( home_score=9 AND visitor_score=10 ))");
sqlToQuery.append("GROUP BY Team.id ORDER BY count_matches DESC LIMIT 1;");
ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
Team team = new Team();
Statistic statistic = new Statistic();
try {
while (resultset.next()) {
team.id = resultset.getLong("id");
team.team_name = resultset.getString("team_name");
team.image = resultset.getString("image");
statistic.count_most_regular_appearances = resultset.getInt("count_matches");
}
resultset.close();
if (team.id != null && team.team_name != null) {
statistic.target_team = team;
}
} catch (SQLException e) {
System.out.println(e.toString());
}
return statistic;
}
private Statistic getMostWonAgainst(Long team_id) {
StringBuilder sqlToQuery =
new StringBuilder(
"SELECT DISTINCT Team.id, Team.team_name, Team.image, count(*) AS count_matches FROM Team, Game ");
sqlToQuery.append("WHERE ( winner_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" ) ");
sqlToQuery.append("AND (( Team.id = Game.home_team_id AND Game.visitor_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" ) ");
sqlToQuery.append("OR ( Game.home_team_id = ");
sqlToQuery.append(team_id);
sqlToQuery.append(" AND Team.id = Game.visitor_team_id ))");
sqlToQuery.append("GROUP BY Team.id ORDER BY count_matches DESC LIMIT 1;");
ResultSet resultset = OpenFoosDatabase.executeQueryToFoosBase(sqlToQuery.toString());
Team team = new Team();
Statistic statistic = new Statistic();
try {
while (resultset.next()) {
team.id = resultset.getLong("id");
team.team_name = resultset.getString("team_name");
team.image = resultset.getString("image");
statistic.count_most_won_against = resultset.getInt("count_matches");
}
resultset.close();
if (team.id != null && team.team_name != null) {
statistic.target_team = team;
}
} catch (SQLException e) {
} finally {
}
return statistic;
}