/**
* Handles the HTTP <code>POST</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
RequestDispatcher disp = request.getRequestDispatcher("index.jsp?page=games");
Connection con = null;
// HTML5 datetime format is jjjj-mm-ddThh:mmZ
User user = (User) request.getSession().getAttribute("user");
DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm'Z'");
if (user != null && (user.getAccessLevel().equals("admin"))) {
try {
if (!request.getParameter("awayteam").equals(request.getParameter("hometeam"))) {
con = Connection.getConnection();
con.startConnection();
PreparedStatement pstmt =
con.prepareStatement(
"insert into game (hometeam, awayteam, date) " + " values(?, ?, ?)");
pstmt.setInt(1, Integer.valueOf(request.getParameter("hometeam")));
pstmt.setInt(2, Integer.valueOf(request.getParameter("awayteam")));
// Lots of conversions to get the time into the database
Date date = df.parse((String) request.getParameter("datetime"));
Calendar d = Calendar.getInstance();
d.setTime(date);
pstmt.setDate(3, new java.sql.Date(d.getTimeInMillis()));
pstmt.execute();
request.setAttribute("success", "The game has been added.");
} else {
request.setAttribute("error", "A team can't play against itself");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
request.setAttribute("error", "The game could not be added.");
} catch (ParseException e) {
e.printStackTrace();
request.setAttribute("error", "The date is in the wrong format.");
} finally {
try {
// Connection might not be initialized, if the user has entered the same team for home and
// away team
if (con != null) con.closeConnection();
} catch (SQLException e) {
// If it can't be closed just continue.
}
}
} else {
request.setAttribute("error", "You don't have sufficient rights to perform this operation.");
}
disp.forward(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
RequestDispatcher disp =
request.getRequestDispatcher("GameDetails?gameid=" + request.getParameter("gameid"));
Connection con = null;
HttpSession session = request.getSession();
User user = (User) request.getSession().getAttribute("user");
if (user != null
&& (user.getAccessLevel().equals("admin")
|| (user.getAccessLevel().equals("manager")
&& user.getTeamID()
== Integer.valueOf((String) session.getAttribute("hometeamid"))))) {
try {
con = Connection.getConnection();
con.startConnection();
PreparedStatement pstmt = con.prepareStatement("delete from score " + "where scoreid = ?");
pstmt.setInt(1, Integer.valueOf(request.getParameter("goalid")));
pstmt.execute();
request.setAttribute("success", "The goal has been deleted.");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
request.setAttribute("error", "Error deleting the goal.");
} finally {
try {
con.closeConnection();
} catch (SQLException e) {
// If it can't be closed just continue.
}
}
} else {
request.setAttribute("error", "You don't have sufficient rights to perform this operation.");
}
disp.forward(request, response);
}
public Standings() {
Connection con = null;
try {
con = Connection.getConnection();
con.startConnection();
HashMap<Integer, Team> teamshelper = new HashMap<Integer, Team>();
// Make sure all teams are in the list, even if no games are played
PreparedStatement pstmt = con.prepareStatement("select teamid, teamname from team");
pstmt.execute();
ResultSet rs = pstmt.getResultSet();
while (rs.next()) {
Team team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
teamshelper.put(rs.getInt("teamid"), team);
}
// Separate queries for home team wins, away team wins, home team draw and awayteamdraws
// Home team wins
pstmt =
con.prepareStatement(
"select hometeam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, h.teamname as teamname, a.teamid as awayteam, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore > atscore "
+ "group by hometeam, awayteam, teamname");
System.out.println(pstmt.toString());
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score") * 2);
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score") * 2); // A win counts 2 points
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Away team wins
pstmt =
con.prepareStatement(
"select awayteam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where atscore > htscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score") * 2);
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score") * 2); // A win counts 2 points
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Home team draws
pstmt =
con.prepareStatement(
"select hometeam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, h.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, h.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore = atscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score"));
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score")); // A draw counts 1 point
}
teamshelper.put(rs.getInt("teamid"), team);
}
// Away team draws
pstmt =
con.prepareStatement(
"select awayteam as teamid, teamname, count(*) as score "
+ "from (select h.teamid as hometeam, a.teamid as awayteam, a.teamname as teamname, g1.gameid, count(p1.teamid) as htscore, count(p2.teamid) as atscore "
+ "from game g1 join game g2 on g1.gameid = g2.gameid "
+ "join team h on g1.hometeam = h.teamid "
+ "join team a on g2.awayteam = a.teamid "
+ "full join score s1 on s1.gameid = g1.gameid "
+ "left join player p1 on (p1.playerid = s1.scorer and p1.teamid = h.teamid) "
+ "left join player p2 on (p2.playerid = s1.scorer and p2.teamid = a.teamid) "
+ "where g1.date < now() "
+ "group by h.teamid, a.teamname, a.teamid, g1.gameid, g1.date "
+ "order by g1.date desc) game "
+ "where htscore = atscore "
+ "group by hometeam, awayteam, teamname");
pstmt.execute();
rs = pstmt.getResultSet();
while (rs.next()) {
Team team = null;
if (teamshelper.containsKey(rs.getInt("teamid"))) {
team = teamshelper.get(rs.getInt("teamid"));
team.addPoints(rs.getInt("score"));
} else { // May only happen, if game is changed while the code is executing. Very unlikely.
team = new Team();
team.setTeamID(rs.getInt("teamid"));
team.setTeamName(rs.getString("teamname"));
team.addPoints(rs.getInt("score")); // A draw counts 1 point
}
teamshelper.put(rs.getInt("teamid"), team);
}
Collection<Team> c = teamshelper.values();
Iterator<Team> i = c.iterator();
teams = new ArrayList<Team>();
while (i.hasNext()) teams.add(i.next());
Collections.sort(teams);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.closeConnection();
} catch (SQLException e) {
// If it can't be closed just continue.
} catch (NullPointerException e) {
// Connection was never initialized, just do nothing.
e.printStackTrace();
}
}
}