예제 #1
0
  /**
   * Delete a student from the database. Also deletes the student's folders. Throws
   * InvalidDBRequestException if any error in database connection.
   *
   * @param username student's user name
   * @throws InvalidDBRequestException
   */
  private void purgeStudent(String username) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      int count;

      // delete from scores
      count = stmt.executeUpdate("delete from scores where user_login = '******'");

      // delete from student
      count = stmt.executeUpdate("delete from student where login = '******'");

      // delete student's folder
      File studentDir = new File("./StudentQuizzes/" + username);
      if (!(studentDir.delete())) {
        System.err.println("Error in deleting folder for student: " + username);
      }

      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #2
0
  /**
   * Deletes an instructor from the database. Deletes the instructor's courses by invoking the
   * deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or
   * other database connection problems.
   *
   * @see deleteCourse
   * @param instructor instructor's user name
   * @throws InvalidDBRequestException
   */
  public void deleteInstructor(String instructor) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      int count;

      // delete the instructor's courses
      ResultSet rs =
          stmt.executeQuery(
              "select course_num from course where instructor = '" + instructor + "'");
      while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor);

      // delete the instructor's record
      count = stmt.executeUpdate("delete from instructor where login ='******'");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #3
0
  /**
   * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
   * student's directory which relates to the course. Caution: vizualisation file will be deleted
   * eventhough it also relates to another course if the student is also registered to that course.
   * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
   * error occured during deletion, or other exception occured.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if student registered to the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (!rs.next())
        throw new InvalidDBRequestException("Student is not registered to the course");

      // remove student from the course
      count =
          stmt.executeUpdate(
              "delete from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");

      // delete the quiz visualization files
      rs =
          stmt.executeQuery(
              "select distinct unique_id, s.test_name from scores s, courseTest t "
                  + "where s.test_name = t.test_name "
                  + "and course_id = '"
                  + courseID
                  + "' "
                  + "and user_login = '******'");
      while (rs.next()) {
        deleteVisualization(rs.getString(1), username, rs.getString(2));
        count =
            stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #4
0
  /**
   * Deletes students who are older than a certain number of years and not registered to any course.
   *
   * @param year students older than year are candidates to be deleted
   * @throws InvalidDBRequestException
   */
  public void deleteOldStudent(int year) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();

      // query all student who have been in the database longer than a number of years and not
      // registered to any course
      ResultSet rs =
          stmt.executeQuery(
              "select login, count(course_id) "
                  + "from student s left join courseRoster r on login = user_login "
                  + "where date_entered < SUBDATE(now(), INTERVAL "
                  + new String().valueOf(year).trim()
                  + " YEAR) "
                  + "group by login, date_entered");
      // delete them
      while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #5
0
  /**
   * Check the user's name and password and verify that the user is an instructor. Throws
   * InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured
   * to the database connection.
   *
   * @param name user's user name
   * @param pass user's password
   * @throws InvalidDBRequestException
   */
  public void instructorLogin(String name, String pass) throws InvalidDBRequestException {
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // check if instructor
      rs = stmt.executeQuery("select password from instructor where login = '******'");
      if (!rs.next()) {
        if (debug) System.out.println("User not found in the instructor table");
        throw new InvalidDBRequestException("Instructor not registered");
      }

      // check password
      if (!rs.getString(1).equals(pass)) {
        if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }
  }
예제 #6
0
  /**
   * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
   * error occured to the database connection.
   *
   * @param name the instructor's user name
   * @return a vector containing the list of courses
   * @throws InvalidDBRequestException
   */
  public Vector getCourseList(String name) throws InvalidDBRequestException {
    Vector courseList = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get the course list
      rs =
          stmt.executeQuery(
              "select course_num, course_name from course where instructor = '"
                  + name
                  + "' order by course_num");
      while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }

    return courseList;
  }
예제 #7
0
  /**
   * Query a quiz list from the database. If there is no student user name specified, the list will
   * contain all quizzes that are used in the instructor's courses. Otherwise, the list will contain
   * all quizzes that the student has taken and from the instructor's courses which the student is
   * registered. Throws InvalidDBRequestException if any error occured to the database connection.
   *
   * @param instructor instructor's user name
   * @param student student's user name. Can be empty to get a list of all quizzes in the
   *     instructor's courses.
   * @return a vector containing the list of quizzes
   * @throws InvalidDBRequestException
   */
  public Vector getQuizList(String instructor, String student) throws InvalidDBRequestException {
    Vector list = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      if (!student.equals("")) {
        // get the list that contains all quizzes that the student has taken and from the
        // instructor's courses which the student is registered
        rs =
            stmt.executeQuery(
                "select courseTest.test_name, scores.start_time from scores, courseTest, course "
                    + "where courseTest.test_name = scores.test_name "
                    + "and courseTest.course_id = course.course_id "
                    + "and instructor = '"
                    + instructor
                    + "' and user_login = '******' "
                    + "order by scores.start_time");

        while (rs.next()) {
          list.add(rs.getString(1) + " <" + rs.getString(2) + ">");
        }
      } else {
        // get the list that contains all quizzes that are used in the instructor's courses
        rs =
            stmt.executeQuery(
                "select test_name from courseTest, course "
                    + "where courseTest.course_id = course.course_id "
                    + "and instructor = '"
                    + instructor
                    + "' ");

        while (rs.next()) {
          list.add(rs.getString(1));
        }
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getQuizList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }

    return list;
  }
예제 #8
0
  /**
   * Obtains information on a quiz that a student has taken, which includes the student's answer to
   * the quiz, a unique id of the instance, number of questions in the quiz, number of questions
   * answered correctly, and vizualisation type of the quiz. Throws InvalidDBRequestException if
   * cannot find the record of the stuent taking the quiz, quiz is not registered in the database,
   * or if any error occured to the database connection.
   *
   * @param student student's user name
   * @param quiz quiz name
   * @param startTime the time the student started the quiz
   * @return a string tokenizer containing: answer,uniqueID, numQuestions, numCorrect, and
   *     visualType. It uses "@" as the delimiter.
   * @throws InvalidDBRequestException
   */
  public StringTokenizer getAnswerAndVisualType(String student, String quiz, String startTime)
      throws InvalidDBRequestException {
    String answer;
    String uniqueID;
    String numQuestions;
    String numCorrect;
    String visualType;
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get student's answer, unique id, number of questions, and number of questions answered
      // correctly
      rs =
          stmt.executeQuery(
              "select transcript, unique_id, num_questions, num_correct from scores "
                  + "where test_name = '"
                  + quiz
                  + "' and user_login = '******' and start_time = '"
                  + startTime
                  + "'");
      if (!rs.next()) throw new InvalidDBRequestException("Student has not taken the quiz");
      else {
        answer = rs.getString(1).trim();
        uniqueID = rs.getString(2).trim();
        numQuestions = rs.getString(3).trim();
        numCorrect = rs.getString(4).trim();
      }

      // get quiz vizualisation type
      rs = stmt.executeQuery("select visual_type from test where name = '" + quiz + "' ");
      if (!rs.next())
        throw new InvalidDBRequestException(
            "Quiz was not found! Can't retrieve visualization type.");
      else {
        visualType = rs.getString(1);
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getAnswerAndVisualType: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
    return new StringTokenizer(
        answer + "@" + uniqueID + "@" + numQuestions + "@" + numCorrect + "@" + visualType, "@");
  }
예제 #9
0
  /**
   * Add a course into the database if not already so. Throws InvalidDBRequestException if course
   * already in the DB, error occured during insertion, or other exception occured.
   *
   * @param courseNum course number
   * @param courseName name of the course
   * @param instructor name of the instructor who owns the course
   * @throws InvalidDBRequestException
   */
  public void addCourse(String courseNum, String courseName, String instructor)
      throws InvalidDBRequestException {
    String courseId = courseNum + instructor;

    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if course already in the database
      rs = stmt.executeQuery("select course_id from course where course_id = '" + courseId + "'");
      if (rs.next()) throw new InvalidDBRequestException("Course is already in the database");

      // insert course into database
      count =
          stmt.executeUpdate(
              "insert into course values ('"
                  + courseId
                  + "', '"
                  + courseName
                  + "', '"
                  + courseNum
                  + "', '"
                  + instructor
                  + "')");
      if (count != 1) throw new InvalidDBRequestException("Something happen during insertion!");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #10
0
  public static void main(String[] args) {
    // Ensure to have all necessary drivers installed !
    try {
      Driver d = (Driver) (Class.forName("oracle.jdbc.driver.OracleDriver").newInstance());
      DriverManager.registerDriver(d);
    } catch (Exception e) {
    }

    // Set the priority which messages have to be logged
    cat.setPriority(Priority.INFO);

    // Configuration with configuration-file
    PropertyConfigurator.configure("log4jtestprops.txt");

    // These messages with Priority >= setted priority will be logged to the database.
    cat.debug("debug"); // this not, because Priority DEBUG is less than INFO
    cat.info("info");
    cat.error("error");
    cat.fatal("fatal");
  }
예제 #11
0
  /**
   * Private method to delete a visualization script file of a quiz taken by a student.
   *
   * @param uniqueId a unique number of that indicates the instance of the quiz taking in the
   *     database
   * @param studentName student's user name
   * @param testName quiz name
   */
  private void deleteVisualization(String uniqueId, String studentName, String testName) {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();

      // get quiz visulization type
      ResultSet rs =
          stmt.executeQuery("select visual_type from test where name = '" + testName + "'");
      // construct the quiz vizualisation file name
      String visFileName =
          new String("./StudentQuizzes/" + studentName + "/" + testName + uniqueId);
      if (rs.next())
        if (rs.getString(1).trim().equalsIgnoreCase("gaigs")) visFileName = visFileName + ".sho";
        else if (rs.getString(1).trim().equalsIgnoreCase("samba"))
          visFileName = visFileName + ".sam";
        else if (rs.getString(1).trim().equalsIgnoreCase("animal"))
          visFileName = visFileName + ".ani";
        else {
          System.err.println("visualization type unknown for quiz: " + rs.getString(3) + "\n");
        }
      else {
        System.err.println("Visulization type not found for quiz: " + rs.getString(3) + "\n");
      }

      // delete the file
      File visFile = new File(visFileName);
      if (debug) System.out.println("deleting " + visFileName);
      if (visFile.exists())
        if (!visFile.delete()) System.err.println("can't delete the visualization file");

      rs.close();
      stmt.close();
      db.close();
    } catch (Exception e) {
      System.err.println("Execption thrown from deleteVisualisation: " + e.getMessage());
    }
  }
예제 #12
0
파일: Fines_jsp.java 프로젝트: malus91/LMS
  public void _jspService(HttpServletRequest request, HttpServletResponse response)
      throws java.io.IOException, ServletException {

    PageContext pageContext = null;
    HttpSession session = null;
    ServletContext application = null;
    ServletConfig config = null;
    JspWriter out = null;
    Object page = this;
    JspWriter _jspx_out = null;
    PageContext _jspx_page_context = null;

    try {
      response.setContentType("text/html;charset=UTF-8");
      pageContext = _jspxFactory.getPageContext(this, request, response, null, true, 8192, true);
      _jspx_page_context = pageContext;
      application = pageContext.getServletContext();
      config = pageContext.getServletConfig();
      session = pageContext.getSession();
      out = pageContext.getOut();
      _jspx_out = out;
      _jspx_resourceInjector =
          (org.glassfish.jsp.api.ResourceInjector)
              application.getAttribute("com.sun.appserv.jsp.resource.injector");

      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("\n");
      out.write("<!DOCTYPE html>\n");
      out.write("<html>\n");
      out.write("    <head>\n");
      out.write(
          "        <meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n");
      out.write("        <title>Fine</title>\n");
      out.write("        <link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\">    \n");
      out.write("\n");
      out.write("    </head>\n");
      out.write("    <body style = \"background-image: url(lib2.jpg)\">         \n");
      out.write("    <center>\n");
      out.write("        <h1>Update Fines information</h1>\n");
      out.write("        <form name=\"Update\" action=\"Fines_upd.jsp\">\n");
      out.write("            <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
      out.write("                <thead>\n");
      out.write("                    <tr>\n");
      out.write("                        <th>Update Fines</th>\n");
      out.write("                        <th></th>\n");
      out.write("                    </tr>\n");
      out.write("                </thead>\n");
      out.write("                <tbody>\n");
      out.write("                    <tr>\n");
      out.write("                        <td>Update Fine table with todays Data</td>\n");
      out.write(
          "                        <td><input type=\"submit\" value=\"Update / View Fines\" name=\"SUBMIT\"/></td>\n");
      out.write("                    </tr>\n");
      out.write("                </tbody>\n");
      out.write("            </table>           \n");
      out.write("        </form>\n");
      out.write("        <h1>Check your Fines Here</h1>\n");
      out.write("        <form name=\"Fines\" action=\"Fines.jsp\">\n");
      out.write("            <table border=\"0\" width=\"3\" cellspacing=\"2\">\n");
      out.write("                <thead>\n");
      out.write("                    <tr>\n");
      out.write("                        <th>Get Fine Details</th>\n");
      out.write("                        <th></th>\n");
      out.write("                    </tr>\n");
      out.write("                </thead>\n");
      out.write("                <tbody>\n");
      out.write("                    <tr>\n");
      out.write("                        <td>Card No</td>\n");
      out.write(
          "                        <td><input type=\"text\" name=\"Card_no\" value=\"\"/></td>\n");
      out.write("                    </tr>\n");
      out.write("                    <tr>\n");
      out.write("                        <td></td>\n");
      out.write(
          "                        <td><input type=\"submit\" value=\"Get Fines\" name=\"SUBMIT\" /></td>\n");
      out.write("                    </tr>\n");
      out.write("                </tbody>\n");
      out.write("            </table>        \n");
      out.write("            ");

      Connection con = null;
      String[] selected_Checkboxes = request.getParameterValues("chk");
      PreparedStatement pst = null;
      ResultSet result = null;
      ResultSet resUpd = null;
      con =
          DriverManager.getConnection(
              "jdbc:mysql://localhost:3306/lbms_db?zeroDateTimeBehavior=convertToNull",
              "root",
              "admin12");
      String Card_no = request.getParameter("Card_no");
      String button = null;
      Date dt = new Date();
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
      String current_date = sdf.format(dt);
      if (Card_no != null && selected_Checkboxes == null) {
        String selSql =
            "SELECT  l.card_no, SUM(f.fine_amt) AS total_fine, f.paid  "
                + "FROM book_loans l, fines f  "
                + "WHERE l.loan_id = f.loan_id AND "
                + "l.card_no = "
                + Card_no
                + " "
                + "GROUP BY l.card_no";
        pst = con.prepareStatement(selSql);
        result = pst.executeQuery();
        String box = null;
        String paid;
        String pay;
        Boolean chk = false;
        out.println("<table>");
        pay = "<form action='Fines.jsp'>";
        out.println(pay);
        out.println("<tr>");
        out.println("<th>Card No</th>");
        out.println("<th>Fine_Amt</th>");
        out.println("<th>Paid OR Not</th>");
        out.println("</tr>");
        while (result.next()) {
          chk = true;
          paid = "No";
          if (result.getBoolean("f.paid")) {
            paid = "Yes";
          }

          out.println("<tr>");
          out.println(
              "<td>"
                  + result.getInt("l.card_no")
                  + "</td><td>"
                  + result.getString("total_fine")
                  + "</td><td>"
                  + paid
                  + "</td>");
          out.print("<td>");
          box = "<input name='chk' value=" + result.getInt("l.card_no") + " type='checkbox'>";
          out.print(box);
          out.print("</td>");
          out.print("</tr>");
        }

        if (chk == true) {
          out.println("<tr>");
          out.print("<td>");
          button = "<input type='submit' value='Pay Fine' name='Pay'>";
          out.print(button);
          out.print("</td>");
          out.println("</tr>");
        } else {

          out.write(
              "<dialog open> <font color = 'green'>No Fine information. You owe nothing! Thank You</font> </dialog>");
        }
        out.println("</form>");
        out.println("</table>");
      } else if (selected_Checkboxes != null) {
        String sqlLoan = null;
        ResultSet resultLoan = null;
        String sqlUpdFine = null;
        PreparedStatement pstUpd = null;
        String sqlBook = null;
        ResultSet rsltBook = null;
        char chkouts = 'N';

        int length_chk = selected_Checkboxes.length;
        for (int i = 0; i < length_chk; i++) {
          // Check whether the Book is returned before paying the fine.
          sqlBook =
              "SELECT COUNT(loan_id) AS no_chkouts FROM book_loans WHERE card_no = "
                  + selected_Checkboxes[i]
                  + " AND date_in = '0000-00-00' AND due_date < "
                  + current_date
                  + "";
          pst = con.prepareStatement(sqlBook);
          rsltBook = pst.executeQuery();
          while (rsltBook.next()) {
            if (rsltBook.getInt("no_chkouts") > 0) {
              chkouts = 'Y';
            }
          }
          if (chkouts == 'Y') {

            out.write(
                "<dialog open> <font color = 'red'>You have outstanding due checkouts!. Please return the books and then Pay the fine</font> </dialog>");
          }
          // Get the corresponding loan_Ids for each customer from Fines table

          sqlLoan =
              "SELECT loan_id FROM book_loans WHERE card_no = "
                  + selected_Checkboxes[i]
                  + " AND date_in IS NOT NULL AND due_date < date_in";
          pst = con.prepareStatement(sqlLoan);
          resultLoan = pst.executeQuery();
          while (resultLoan.next()) {
            sqlUpdFine =
                "UPDATE fines SET paid = true WHERE loan_id = " + resultLoan.getInt("loan_id") + "";
            pstUpd = con.prepareStatement(sqlUpdFine);
            pstUpd.executeUpdate();
            out.println("Payment Updated Successfully");
          }
        }
      }

      out.write("\n");
      out.write("        </form>        \n");
      out.write("    </center>\n");
      out.write("</body>\n");
      out.write("</html>\n");
    } catch (Throwable t) {
      if (!(t instanceof SkipPageException)) {
        out = _jspx_out;
        if (out != null && out.getBufferSize() != 0) out.clearBuffer();
        if (_jspx_page_context != null) _jspx_page_context.handlePageException(t);
        else throw new ServletException(t);
      }
    } finally {
      _jspxFactory.releasePageContext(_jspx_page_context);
    }
  }
예제 #13
0
  /**
   * Add a quiz to a course if not already so. Also write the menu file for the course. Throws
   * InvalidDBRequestException if quiz already in the course, error occured during insertion, or
   * other exception occured. Throws FileFailureException if fail to append quiz to the menu.
   *
   * @param quizname the quiz name
   * @param courseID the course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void addQuiz(String quizName, String courseID)
      throws InvalidDBRequestException, FileFailureException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      Statement cstmt = db.createStatement();
      ResultSet rs, courseRS;

      int count = 0;

      // get quiz info
      courseRS =
          cstmt.executeQuery(
              "select menu_text, script_type, visual_type from test where name = '"
                  + quizName
                  + "'");

      // check if quiz in the database
      if (!courseRS.next())
        throw new InvalidDBRequestException("Quiz is not registered in the database");
      else {
        // check if quiz already in the course
        rs =
            stmt.executeQuery(
                "select test_name from courseTest where test_name = '"
                    + quizName
                    + "' and course_id = '"
                    + courseID
                    + "'");
        if (rs.next()) throw new InvalidDBRequestException("Quiz is already added for the course");
        else {
          count =
              stmt.executeUpdate(
                  "insert into courseTest (course_id, test_name) values ('"
                      + courseID
                      + "', '"
                      + quizName
                      + "')");
          if (count != 1) throw new InvalidDBRequestException("Error occured during insertion");
          else {
            // append quiz info to the course menu
            try {
              PrintWriter fileOStream =
                  new PrintWriter(
                      new FileOutputStream("./html_root/cat/" + courseID + ".list", true));
              if (debug)
                System.out.println(
                    courseRS.getString(1).trim()
                        + "\n"
                        + quizName
                        + " "
                        + courseRS.getString(2).trim()
                        + " "
                        + courseRS.getString(3).trim().toLowerCase()
                        + "\n****\n");
              fileOStream.print(
                  courseRS.getString(1).trim()
                      + "\n"
                      + quizName
                      + " "
                      + courseRS.getString(2).trim()
                      + " "
                      + courseRS.getString(3).trim().toLowerCase()
                      + "\n****\n");
              fileOStream.flush();
              fileOStream.close();
            } catch (Exception e) {
              System.err.println("Error in creating the menu: " + e.getMessage());
              throw new FileFailureException("Error in creating the menu: " + e.getMessage());
            }
          }
        }
      }

      courseRS.close();
      rs.close();
      stmt.close();
      cstmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addQuiz: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #14
0
  /**
   * Query students grades for a course on a particular quiz Throws InvalidDBRequestException if
   * quiz is not used in the course, or if any error occured to the database connection.
   *
   * @param courseID course unique id
   * @param quizname quiz unique name
   * @return a vector that contains the students information and the grades
   * @throws InvalidDBRequestException
   */
  public Vector getQuizGrades(String courseID, String quizName) throws InvalidDBRequestException {
    Vector grades = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      rs =
          stmt.executeQuery(
              "select valid from courseTest where course_id = '"
                  + courseID
                  + "' and test_name = '"
                  + quizName
                  + "'");
      if (rs.next()) {
        rs =
            stmt.executeQuery(
                "select login, last_name, first_name, start_time, num_questions, num_correct from student, scores, courseRoster "
                    + "where student.login = scores.user_login "
                    + "and scores.user_login = courseRoster.user_login "
                    + "and scores.test_name = '"
                    + quizName
                    + "' "
                    + "and course_id = '"
                    + courseID
                    + "' "
                    + "order by last_name, first_name, login, scores.start_time");

        // use @ as delimiter since the date_time contains space
        while (rs.next())
          grades.add(
              rs.getString(1)
                  + "@"
                  + rs.getString(2)
                  + "@"
                  + rs.getString(3)
                  + "@"
                  + rs.getString(4)
                  + "@"
                  + rs.getString(5)
                  + "@"
                  + rs.getString(6));
      } else throw new InvalidDBRequestException("Quiz is not used for the course");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in instructor login: "******"???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }
    return grades;
  }
예제 #15
0
  /**
   * Changes the password of a user. Throws InvalidNameException if user's not registered in the
   * database. Throws InvalidPassException if user's password was invalid. Throws
   * InvalidDBRequestException if error occured during insertion or other database connection
   * problems.
   *
   * @param name user's user name
   * @param pass user's password
   * @param newPAss user's new password
   * @throws InvalidNameException, InvalidPassException, InvalidDBRequestException
   */
  public void changePassword(String name, String pass, String newPass)
      throws InvalidNameException, InvalidPassException, InvalidDBRequestException {
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      int count;
      Statement stmt = db.createStatement();
      ResultSet rs;
      rs = stmt.executeQuery("select password from student where login = '******'");

      // check if it's not a student in database
      if (!rs.next()) {
        rs = stmt.executeQuery("select password from instructor where login = '******'");

        // check if it's an instructor in database
        if (!rs.next()) {
          if (debug) System.out.println("User not found in the user table");
          throw new InvalidNameException("User not registered");
        }

        // check for password
        if (!rs.getString(1).equals(pass)) {
          if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"update instructor set password = '******' where login = '******' and password ='******'");
      } else {
        // check for password
        if (!rs.getString(1).equals(pass)) {
          if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"update student set password = '******' where login = '******' and password ='******'");
      }

      // check if successful
      if (count != 1) {
        System.err.println("Error in updating password");
        throw new InvalidDBRequestException("Error in updating password");
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) // catch sql exception
    {
      System.err.println("Invalid SQL in ChangePassword: "******"Server request error: " + e.getMessage());
    } catch (ClassNotFoundException e) // catch driver not found exception
    {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }
  }
예제 #16
0
  /**
   * Remove a quiz from a course. Also deletes all the quiz visualization files from the students'
   * folder who is registered in the course. Caution: vizualisation file will be deleted eventhough
   * it also relates to anther course if the student is also registered to that course. (FIX ME!)
   * Throws InvalidDBRequestException if the quiz is not registered in the course, error occured
   * during deletion, or other exception occured.
   *
   * @param quizName quiz name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteQuiz(String quizName, String courseID)
      throws InvalidDBRequestException, FileFailureException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if quiz is used in the course
      rs =
          stmt.executeQuery(
              "select test_name from courseTest where test_name = '"
                  + quizName
                  + "' and course_id = '"
                  + courseID
                  + "'");
      if (!rs.next()) throw new InvalidDBRequestException("Quiz is not registered for the course");
      else {
        // remove quiz from course
        count =
            stmt.executeUpdate(
                "delete from courseTest where course_id = '"
                    + courseID
                    + "' and test_name = '"
                    + quizName
                    + "'");
        if (count != 1) throw new InvalidDBRequestException("Error occured during deletion");
        else {
          // delete quiz visualization files
          rs =
              stmt.executeQuery(
                  "select distinct unique_id, scores.user_login from scores, courseRoster "
                      + "where courseRoster.user_login = scores.user_login "
                      + "and course_id = '"
                      + courseID
                      + "' "
                      + "and test_name = '"
                      + quizName
                      + "'");
          while (rs.next()) {
            deleteVisualization(rs.getString(1), rs.getString(2), quizName);
            count =
                stmt.executeUpdate(
                    "delete from scores where unique_id = " + rs.getString(1).trim());
          }

          // rewrite the menu for the course
          rs =
              stmt.executeQuery(
                  "select distinct menu_text, name, script_type, visual_type from test t, courseTest c "
                      + "where t.name = c.test_name "
                      + "and course_id = '"
                      + courseID
                      + "'");
          PrintWriter fileOStream =
              new PrintWriter(new FileOutputStream("./html_root/cat/" + courseID + ".list"));
          while (rs.next()) {
            if (debug)
              System.out.println(
                  rs.getString(1).trim()
                      + "\n"
                      + rs.getString(2).trim()
                      + " "
                      + rs.getString(3).trim()
                      + " "
                      + rs.getString(4).trim().toLowerCase()
                      + "\n****\n");
            fileOStream.print(
                rs.getString(1).trim()
                    + "\n"
                    + rs.getString(2).trim()
                    + " "
                    + rs.getString(3).trim()
                    + " "
                    + rs.getString(4).trim().toLowerCase()
                    + "\n****\n");
          }
          fileOStream.flush();
          fileOStream.close();
        }
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addQuiz: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    } catch (Exception e) {
      System.err.println("Error in recreating menu for course: " + courseID);
      System.err.println(e.getMessage());
      throw new FileFailureException();
    }
  }
예제 #17
0
  /**
   * Deletes a course from the database. Also deletes all the quiz visualization files from the
   * students' folder who is registered in the course for all quizzes related to the course.
   * Caution: vizualisation file will be deleted eventhough it also relates to anther course if the
   * student is also registered to that course. (FIX ME!) Throws InvalidDBRequestException if the
   * course is not in the database, error occured during deletion, or other exception occured.
   *
   * @param courseNum course number
   * @param instructor instructor's user name who owns the course
   * @throws InvalidDBRequestException
   */
  public void deleteCourse(String courseNum, String instructor) throws InvalidDBRequestException {
    String courseId = new String(courseNum + instructor);

    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if course in the database
      rs = stmt.executeQuery("select course_id from course where course_id = '" + courseId + "'");
      if (!rs.next()) throw new InvalidDBRequestException("Course is not in the database");
      else {
        // delete course from course table
        count = stmt.executeUpdate("delete from course where course_id = '" + courseId + "'");
        if (count != 1) throw new InvalidDBRequestException("Something happen during deletion!");
        else {
          // delete quiz visualization files
          rs =
              stmt.executeQuery(
                  "select distinct unique_id, s.user_login, s.test_name from scores s, courseRoster r, courseTest t "
                      + "where s.test_name = t.test_name "
                      + "and r.user_login = s.user_login "
                      + "and r.course_id = t.course_id "
                      + "and t.course_id = '"
                      + courseId
                      + "'");
          while (rs.next()) {
            deleteVisualization(
                rs.getString(1).trim(), rs.getString(2).trim(), rs.getString(3).trim());
            count =
                stmt.executeUpdate(
                    "delete from scores where unique_id = " + rs.getString(1).trim());
          }

          // delete course from other tables
          count =
              stmt.executeUpdate("delete from courseRoster where course_id = '" + courseId + "'");
          count = stmt.executeUpdate("delete from courseTest where course_id = '" + courseId + "'");

          // delete menu file
          File menuFile = new File("./html_root/cat/" + courseId + ".list");
          if (menuFile.exists())
            if (!menuFile.delete()) System.err.println("Failed to delete the menu");
        }
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #18
0
  /**
   * Query a student list from the database. If quiz name is not specified, the list will contain
   * all students who are registered in the instructor's courses. Otherwise, it will contain all
   * students who has taken the quiz and are registered to the instructor's course(s) that use the
   * quiz Throws InvalidDBRequestException if any error occured to the database connection.
   *
   * @param name instructor's user name
   * @param quiz quiz name. Can be empty String to get the list of all students who are registered
   *     in the instructor's courses.
   * @return a vector that contains the list of students
   * @throws InvalidDBRequestException
   */
  public Vector getStudentList(String name, String quiz) throws InvalidDBRequestException {
    Vector list = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      if (!quiz.equals("")) {
        // get the list that contains all students who has taken the quiz and are registered to the
        // instructor's course(s) that use the quiz
        rs =
            stmt.executeQuery(
                "select login, last_name, first_name, scores.start_time from student, scores, courseRoster, course "
                    + "where student.login = scores.user_login "
                    + "and scores.user_login = courseRoster.user_login "
                    + "and courseRoster.course_id = course.course_id "
                    + "and scores.test_name = '"
                    + quiz
                    + "' "
                    + "and instructor = '"
                    + name
                    + "' "
                    + "order by last_name, first_name, login, scores.start_time");

        while (rs.next()) {
          list.add(
              rs.getString(2)
                  + ", "
                  + rs.getString(3)
                  + " ("
                  + rs.getString(1)
                  + ") <"
                  + rs.getString(4)
                  + ">");
        }
      } else {
        // get the list that contains all students who are registered in the instructor's courses
        rs =
            stmt.executeQuery(
                "select distinct login, last_name, first_name from student, courseRoster, course "
                    + "where student.login = courseRoster.user_login "
                    + "and courseRoster.course_id = course.course_id "
                    + "and instructor = '"
                    + name
                    + "' "
                    + "order by last_name, first_name, login");

        while (rs.next()) {
          list.add(rs.getString(2) + ", " + rs.getString(3) + " (" + rs.getString(1) + ")");
        }
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getStudentList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }

    return list;
  }
예제 #19
0
  /**
   * Add a student to a course if not already so. If student not registered in the student table
   * yet, the user are required to enter the student information so that the student can be added.
   * Throws InvalidDBRequestException if user name belongs to an instructor, student already
   * registered in the course, student information differs than the one in the database, student
   * information not available when needed, error occured during insertion, or if other exception
   * occured Throws FileFailureException if fail to create folder for the student.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @param lastname student's last name
   * @param firstname student's first name
   * @param initPass initial password for the student
   * @throws InvalidDBRequestException
   */
  public void addStudent(
      String courseID, String username, String lastName, String firstName, String initPass)
      throws InvalidDBRequestException, FileFailureException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if user name belongs to an instructor
      rs = stmt.executeQuery("select login from instructor where login = '******'");
      if (rs.next()) throw new InvalidDBRequestException("User name is reserved by an instructor");

      // check if student in the database or not
      rs =
          stmt.executeQuery(
              "select last_name, first_name from student where login = '******'");
      if (rs.next()) {
        // check if user name belongs to another student
        if (!lastName.equals("")
            && (!lastName.equalsIgnoreCase(rs.getString(1).trim())
                || !firstName.equalsIgnoreCase(rs.getString(2).trim())))
          throw new InvalidDBRequestException(
              "User name is used by: " + rs.getString(2).trim() + " " + rs.getString(1).trim());
      } else {
        // check if student's information is given
        if (lastName.equals(""))
          throw new InvalidDBRequestException(
              "Student is not registered in the database. Please enter the student's name");

        // insert student into hte database
        count =
            stmt.executeUpdate(
                "insert into student values ('"
                    + username
                    + "', '"
                    + lastName
                    + "', '"
                    + firstName
                    + "', '"
                    + initPass
                    + "', now())");
        if (count != 1) throw new InvalidDBRequestException("Error occured during insertion!");

        // create student's folder
        File newStudentDir = new File("./StudentQuizzes/" + username);
        if (!(newStudentDir.mkdir())) {
          System.err.println("Error in creating new folder for student: " + username);
          throw new FileFailureException("huh");
        }
      }

      // check if student is registered in the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (rs.next())
        throw new InvalidDBRequestException("Student is already registered to the course");

      // register student to the course
      count =
          stmt.executeUpdate(
              "insert into courseRoster values ('" + courseID + "', '" + username + "')");
      if (count != 1) throw new InvalidDBRequestException("Error occured during insertion!");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
예제 #20
0
  public void start() {

    Connection connection = null; // manages connection
    Connection connection2 = null;
    Statement statement = null; // query statement
    Statement statement2 = null;
    wm = new double[20][Data.windows_size];
    String QueryKinasesName = "%" + Data.kinease + "%";
    // data.code = data.codenames[3];
    String QueryCodeName = Data.code;
    int windows_size = Data.windows_size;

    // int windows_size = 9;

    int shift = windows_size / 2;

    try {
      Class.forName(JDBC_DRIVER); // load database driver class
      for (windows_size = Data.windows_size; windows_size <= Data.windows_size; windows_size += 2) {
        shift = windows_size / 2;
        // establish connection to database
        connection = DriverManager.getConnection(DATABASE_URL, "", "");
        connection2 = DriverManager.getConnection(DATABASE_URL, "", "");
        // create Statement for querying database
        statement = connection.createStatement();
        statement2 = connection2.createStatement();
        String ACC = null;
        String SEQUENCE = null;
        String KINASES = null;
        String LIKE = "LIKE";
        // int POSITION = 0;
        // int index = 0;
        String temp = null;
        // int numtemp = 0;
        // int LENGTH = (int) 0;
        // int count = 0;
        double[] totalAAcount = new double[windows_size];

        double weightmatrix[][] = new double[windows_size][128]; // windowssize;aa;

        String statementquery1 =
            "SELECT Mid(sequence,(position-"
                + shift
                + "),"
                + windows_size
                + ") AS TARGET, index,code,length,position,sequence FROM Dataset_041106 WHERE ((position-"
                + shift
                + ")>1) AND ((position +"
                + shift
                + ")<length) AND kinases "
                + LIKE
                + " '"
                + QueryKinasesName
                + "' AND (code LIKE '"
                + QueryCodeName
                + "')";
        System.out.println("#" + statementquery1);
        /// fout.println("#"+statementquery1);
        ResultSet resultSet1 = statement.executeQuery(statementquery1);
        int seqsize = 0;
        while ((resultSet1.next())) {
          String posseq = resultSet1.getString("TARGET");
          seqsize = posseq.length();
          if (posseq.charAt(0) != 'X' && posseq.charAt(seqsize - 1) != 'X') { // �h����t

            for (int i = 0; i < seqsize; i++) {

              weightmatrix[i][posseq.charAt(i)]++;
            }
            // possequence.addElement(posseq);
          }
        } // end while
        char[] aaMap = {
          'A', 'R', 'N', 'D', 'C', 'Q', 'E', 'G', 'H', 'I', 'L', 'K', 'M', 'F', 'P', 'S', 'T', 'W',
          'Y', 'V', 'X'
        };
        double[] expmatrix = {
          0.0701313443873091,
          0.0582393695201718,
          0.0359362961736045,
          0.0520743144385134,
          0.0172010453343506,
          0.0498574962335004,
          0.0796465136978452,
          0.0624720283551962,
          0.0241405228512130,
          0.0416989778376737,
          0.0934441156861220,
          0.0632334844952389,
          0.0213293464067050,
          0.0324554733241482,
          0.0651181982370858,
          0.0881672518230193,
          0.0524630941595624,
          0.0101093184162382,
          0.0244701177088640,
          0.0578116909136386
        };
        // double[] aaMapfreq = new double[windows_size];
        double freq = 0;
        for (int j = 0; j < weightmatrix.length; j++) {
          for (int i = 0; i < aaMap.length - 1; i++) {
            totalAAcount[j] += weightmatrix[j][aaMap[i]];
          }
        }

        for (int i = 0; i < aaMap.length - 1; i++) {
          // profilefout.print(aaMap[i]);
          for (int j = 0; j < windows_size; j++) {

            freq = ((weightmatrix[j][aaMap[i]]) / (totalAAcount[j])) + 1;

            wm[i][j] = Math.log10((freq / expmatrix[i])) / Math.log10(2.0);

            //  profilefout.print(","+aaMapfreq[i]);

          }
          // profilefout.println();
        }

        // fout.close();
        // profilefout.close();

        resultSet1.close();
        connection.close();
      }

    } // end try
    catch (ClassNotFoundException classNotFound) {
      classNotFound.printStackTrace();
      System.exit(1);
    } catch (NullPointerException nullpointerException) {
      nullpointerException.printStackTrace();
      System.exit(1);

    } catch (SQLException ex) {
      /** @todo Handle this exception */
      /*        } catch (IOException ex) {
       */
      /** @todo Handle this exception */
    } catch (NoClassDefFoundError ex) {

    } finally { // ensure statement and connection are closed properly
      try {
        statement.close();
        statement2.close();

        connection.close();

        connection2.close();
      } catch (Exception exception) { // end try
        exception.printStackTrace();
        System.exit(1);
      } // end catch
    } // end finally
  } // end main
  public Design() throws Exception {
    super.setBackground(Color.BLACK);
    this.setTitle("");
    con = getContentPane();
    con.setLayout(null);
    dim = tk.getDefaultToolkit().getScreenSize();
    this.setTitle("Customer Peer Login");

    l1 = new JLabel(new ImageIcon("plain.jpg"));
    l1.setBounds(0, 0, 400, 400);
    con.add(l1);
    l1.setBorder(BorderFactory.createEtchedBorder(5, Color.black, Color.black));

    title = new JLabel("CUSTOMER PEER LOGIN ");
    title.setFont(new Font("Bookman Old Style", Font.ROMAN_BASELINE, 20));
    title.setForeground(Color.red);
    title.setBounds(80, 30, 300, 30);
    l1.add(title);

    l4 = new JLabel("CMACHINE NAME");
    l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
    l4.setForeground(Color.BLUE);
    l4.setBounds(70, 100, 160, 20);
    //	l4.setBorder(BorderFactory.createEtchedBorder(5,Color.green,Color.green));

    l1.add(l4);
    jtf2 = new JTextField();
    jtf2.setBounds(250, 100, 100, 20);
    jtf2.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));

    l1.add(jtf2);

    l2 = new JLabel("CUSER LOGIN");
    l2.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
    l2.setForeground(Color.blue);
    l2.setBounds(70, 150, 120, 20);
    l1.add(l2);

    jtf1 = new JTextField();
    jtf1.setBounds(250, 150, 100, 20);
    jtf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));

    l1.add(jtf1);

    l3 = new JLabel("CPASSWORD");
    l3.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
    l3.setForeground(Color.blue);
    l3.setBounds(70, 200, 120, 20);
    l1.add(l3);

    jptf1 = new JPasswordField();
    jptf1.setBounds(250, 200, 100, 20);
    jptf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));

    l1.add(jptf1);

    JLabel l4 = new JLabel("DAgent");
    l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
    l4.setForeground(Color.blue);
    l4.setBounds(70, 250, 120, 20);
    l1.add(l4);

    box = new JComboBox();
    box.setBounds(250, 250, 100, 20);
    box.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
    l1.add(box);

    b2 = new JButton("Register");
    b2.setBounds(50, 300, 100, 20);
    l1.add(b2);
    b2.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));

    b3 = new JButton("Login");
    b3.setBounds(150, 300, 100, 20);
    b3.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
    l1.add(b3);

    b1 = new JButton("Cancel");
    b1.setBounds(250, 300, 100, 20);
    b1.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));

    l1.add(b1);

    b1.addActionListener(
        new ActionListener() {
          public void actionPerformed(ActionEvent we) {

            dispose();
          }
        });

    try {

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      conn = DriverManager.getConnection("jdbc:odbc:agent");

    } catch (Exception exp) {

    }

    try {
      Statement satem = conn.createStatement();
      ResultSet rsatem = satem.executeQuery("select * from Dagent");
      while (rsatem.next()) {
        String namem = rsatem.getString("uname");
        box.addItem(namem);
      }

    } catch (Exception expo1) {

    }

    b2.addActionListener(
        new ActionListener() {
          public void actionPerformed(ActionEvent we) {

            String username = jtf1.getText().trim();
            String password = jptf1.getText().trim();
            String mechine = jtf2.getText().trim();
            String dname = box.getSelectedItem().toString();
            int porte = 0;

            try {
              Statement sate = conn.createStatement();
              ResultSet rsate =
                  sate.executeQuery("select * from Dagent where uname='" + dname + "'");
              if (rsate.next()) {
                servermachine = rsate.getString("umechine");
                porte = rsate.getInt("ulistport");
                System.out.println(servermachine);
              }
              System.out.println(servermachine);

            } catch (Exception exp) {
              exp.printStackTrace();
            }

            try {
              packet p = new packet();
              p.setaction("Creg");
              p.setCuser(username);
              p.setCpass(password);
              p.setCmname(mechine);
              p.setCDpeer(dname);
              Socket soc = new Socket(servermachine, porte);
              ObjectOutputStream out = new ObjectOutputStream(soc.getOutputStream());
              out.writeObject(p);
              ObjectInputStream in = new ObjectInputStream(soc.getInputStream());
              packet rpac = (packet) in.readObject();
              if (rpac.getaction().equals("ok")) {

                JOptionPane.showMessageDialog(null, "Sucessfully Registered");

                jtf2.setText("");
                jtf1.setText("");
                jptf1.setText("");

              } else {

                JOptionPane.showMessageDialog(null, "Already Registered");
                jtf2.setText("");
                jtf1.setText("");
                jptf1.setText("");
              }

            } catch (Exception exp) {
            }
          }
        });

    b3.addActionListener(
        new ActionListener() {
          public void actionPerformed(ActionEvent we) {

            String username = jtf1.getText().trim();
            String password = jptf1.getText().trim();
            String mechine = jtf2.getText().trim();
            String Dname = box.getSelectedItem().toString();

            int porte = 0;

            try {
              Statement sate = conn.createStatement();
              ResultSet rsate =
                  sate.executeQuery("select * from Dagent where uname='" + Dname + "'");
              if (rsate.next()) {
                servermachine = rsate.getString("umechine");
                porte = rsate.getInt("ulistport");
                System.out.println(servermachine);
              }
              System.out.println(servermachine);

            } catch (Exception exp) {

            }

            try {

              packet p1 = new packet();
              p1.setaction("clogin");
              p1.setCuser(username);
              p1.setCpass(password);
              p1.setCmname(mechine);
              p1.setCDpeer(Dname);
              Socket soc1 = new Socket(servermachine, porte);
              ObjectOutputStream out1 = new ObjectOutputStream(soc1.getOutputStream());
              out1.writeObject(p1);
              ObjectInputStream in1 = new ObjectInputStream(soc1.getInputStream());
              packet rpac1 = (packet) in1.readObject();
              if (rpac1.getaction().equals("ok")) {
                int port1 = 0;
                try {

                  int portm = rpac1.getCport();
                  System.out.println("XXXXXXX" + portm);
                  //	JOptionPane.showMessageDialog(null,"Sucessfully Started");

                  new Listen(portm);
                  new process(username, portm);
                  dispose();
                } catch (Exception exp) {
                }
              } else {
                JOptionPane.showMessageDialog(
                    null, "Enter valid username and password", "Server reply", 2);
                jtf1.setText("");
                jtf2.setText("");
                jptf1.setText("");
              }

            } catch (Exception exp) {
            }
          }
        });

    setSize(400, 400);
    show();
    setLocation(150, 100);
    setResizable(false);
  }