Beispiel #1
0
  /**
   * 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);
  }
Beispiel #2
0
  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);
  }
Beispiel #4
0
  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());
    }
  }