/**
* 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);
}
public User(Integer userID) throws Exception {
this.Connect();
Connection c = this.getConn();
ResultSet resultset;
PreparedStatement preparedset = null;
String sqlOption = "SELECT * FROM users WHERE id=? LIMIT 1";
preparedset = c.prepareStatement(sqlOption);
preparedset.setInt(1, userID);
resultset = preparedset.executeQuery();
if (resultset.next()) {
this.setLogin(resultset.getString("login"));
this.setRole(resultset.getInt("role_id"));
this.setUserID(resultset.getInt("id"));
}
}
/**
* 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();
}
}
}
void profedit(String cid) throws Exception {
try {
Connection conn = DBConnection.ConnectDB();
while (true) {
// System.out.println(cid);
// list all exercises in this course
PreparedStatement stmt0 =
conn.prepareStatement("SELECT eid, hwname FROM exercise WHERE cid = ?");
stmt0.setString(1, cid);
ResultSet rs0 = stmt0.executeQuery();
int i = 1;
String[] profselect = new String[20];
int[] alleid = new int[20];
while (rs0.next()) {
String hwname = rs0.getString("hwname");
alleid[i] = rs0.getInt("eid");
profselect[i] = hwname; // professor's selection is stored
System.out.printf(" %d. %s\n", i, hwname);
i++;
}
System.out.println(" 0. Back");
Scanner scan = new Scanner(System.in);
int Choice = -1;
// get user choice of which Exercise
System.out.println("Please enter the corresponding number:");
while (true) {
Choice = scan.nextInt();
if (checkint(Choice, 0, i - 1)) break;
}
if (Choice == 0) {
System.out.print('\u000C');
break; // go back
} else {
// display chosen Exercise
System.out.print('\u000C');
String chosename = profselect[Choice];
System.out.println("You chose to update " + chosename);
int eid = alleid[Choice];
PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM Exercise WHERE eid=?");
stmt1.setInt(1, eid);
ResultSet rs1 = stmt1.executeQuery();
rs1.next();
String startdate = rs1.getString("startdate");
String enddate = rs1.getString("enddate");
int newallowattempt = rs1.getInt("allowattempt");
int newscorescheme = rs1.getInt("scorescheme");
int newnumofquestion = rs1.getInt("numofquestion");
int newcorrectpoint = rs1.getInt("correctpoint");
int newincorrectpoint = rs1.getInt("incorrectpoint");
int newbasedon = rs1.getInt("basedon");
while (true) {
// display options the professor can modify
System.out.println(
"Choose what to update:\n 1. Start date\n 2. End date\n 3. Number of attempts\n "
+ "4. Score selection\n 5. Question numbers\n 6. Correct answer points\n 7. Incorrect answer points\n 8. Based on\n 0. Back");
Choice = -1;
while (true) {
Choice = scan.nextInt();
if (checkint(Choice, 0, 8)) break;
}
/*
1. Start date
2. End date
3. Number of attempts
4. Score selection
5. Question numbers
6. Correct answer points
7. Incorrect answer points
8. Based on
*/
Scanner scan1 = new Scanner(System.in);
if (Choice == 0) {
System.out.print('\u000C');
break;
} else if (Choice == 1) {
System.out.printf(
"Origionally Start Date:%s\nPlease enter new Start Date(yyyy-mm-dd):", startdate);
while (true) {
startdate = scan1.nextLine();
if (checkdate(startdate)) break;
}
startdate = startdate + " 00:00:00.0";
} else if (Choice == 2) {
System.out.printf(
"Origionally End Date:%s\nPlease enter new End Date(yyyy-mm-dd):", enddate);
while (true) {
enddate = scan1.nextLine();
if (checkdate(enddate)) break;
}
enddate = enddate + " 00:00:00.0";
} else if (Choice == 3) {
System.out.printf(
"Origionally Number of attempts:%d\nPlease enter new Number of attempts:",
newallowattempt);
newallowattempt = scan1.nextInt();
} else if (Choice == 4) {
String[] sele = new String[5];
sele[1] = "first attempt";
sele[2] = "last attempt";
sele[3] = "average";
sele[4] = "max";
System.out.printf(
"Origionally Score selection:%s\nPlease enter new Score selection(1.first attempt 2.last attempt 3.average 4.max):",
sele[newscorescheme]);
while (true) {
newscorescheme = scan.nextInt();
if (checkint(newscorescheme, 1, 4)) {
// give professor choice whether to update all the scores of students
System.out.println(
"Do you want to update all the students' scores? 1. Yes 2. No");
int updatechoice = scan.nextInt();
if (checkint(updatechoice, 1, 2)) {
if (updatechoice == 1) {
ProfUpdateScore pupdate = new ProfUpdateScore();
pupdate.profupdate(eid, newscorescheme);
break;
}
}
break;
}
}
} else if (Choice == 5) {
System.out.printf(
"Origionally Question numbers:%d\nPlease enter new Question numbers:",
newnumofquestion);
newnumofquestion = scan1.nextInt();
} else if (Choice == 6) {
System.out.printf(
"Origionally Correct answer points:%d\nPlease enter new Correct answer points:",
newcorrectpoint);
newcorrectpoint = scan1.nextInt();
} else if (Choice == 7) {
System.out.printf(
"Origionally Incorrect answer points:%d\nPlease enter new Incorrect answer points:",
newincorrectpoint);
newincorrectpoint = scan1.nextInt();
} else if (Choice == 8) {
String[] basedt = new String[4];
if (cid.equals("CSC440")) {
basedt[1] = "Database Fundamentals";
basedt[2] = "ER Design";
basedt[3] = "Security and Authorization";
System.out.printf(
"Origionally Based on: %s\nPlease enter new Base on:\n 1. Database Fundamentals\n 2. ER Design\n 3. Security and Authorization\n",
basedt[newbasedon]);
} else {
basedt[1] = "Binary search trees and Btrees";
basedt[2] = "Hashing";
basedt[3] = "Files and indexing and other topics";
System.out.printf(
"Origionally Based on: %s\nPlease enter new Base on:\n 1. Binary search trees and Btrees\n 2.Hashing 3.Files and indexing and other topics\n",
basedt[newbasedon]);
}
while (true) {
newbasedon = scan.nextInt();
if (checkint(newbasedon, 1, 3)) break;
}
}
// System.out.println(enddate);
String query =
"UPDATE Exercise SET startdate = timestamp'"
+ startdate
+ "', enddate = timestamp'"
+ enddate
+ "',"
+ " correctpoint = "
+ newcorrectpoint
+ ",incorrectpoint = "
+ newincorrectpoint
+ ", scorescheme = "
+ newscorescheme
+ ", "
+ "allowattempt = "
+ newallowattempt
+ ", numofquestion = "
+ newnumofquestion
+ ", basedon = "
+ newbasedon
+ " WHERE eid = "
+ eid
+ " ";
// edit homework
Statement stmt3 = conn.createStatement();
// debug
// System.out.println("query:"+query);
if (stmt3.executeUpdate(query) == 1) {
// System.out.print('\u000C');
System.out.println("Update Successful!");
} else {
// System.out.print('\u000C');
System.out.println("Update Failure!Please try again.");
}
}
}
}
conn.close();
} catch (Exception e) {
System.out.println("" + e.getMessage());
}
}
