示例#1
0
  @Override
  public void updateCourses(Course ud, String changePres) {
    // TODO Auto-generated method stub
    try {

      stmt =
          conn.prepareStatement(
              "update Course set courseName=?,credits=?,preCourseNos=? where courseNo=?");
      stmt.setString(1, ud.getCourseName());
      stmt.setDouble(2, ud.getCredits());
      stmt.setString(3, changePres);
      stmt.setString(4, ud.getCourseNo());

      stmt.executeUpdate();
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
  }
示例#2
0
  @Override
  public void addCourse(Course add1, String addPres) {
    // TODO Auto-generated method stub

    try {
      stmt =
          conn.prepareStatement(
              "INSERT INTO Course(courseNo,courseName,credits,preCourseNos) VALUES(?,?,?,?)");
      stmt.setString(1, add1.getCourseNo());
      stmt.setString(2, add1.getCourseName());
      stmt.setDouble(3, add1.getCredits());
      stmt.setString(4, addPres);

      stmt.executeUpdate();
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
  }
  /*
  GET PASSED EXAMS
   */
  public Cursor getPassedExams() {

    database = course_dbHelper.getWritableDatabase();

    String where = DB_ABSTRACTS.DBCourse.PASSED_COLUMN + " = " + 1;

    Cursor cursor =
        database.query(
            DB_ABSTRACTS.DBCourse.DATABASE_TABLE,
            examColumns,
            where,
            null,
            null,
            null,
            DB_ABSTRACTS.DBCourse.PASS_MARK_COLUMN + " ASC");

    if (cursor.moveToFirst()) {
      do {
        Course course = new Course();
        course.setId(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.KEY_ID)));
        course.setName(cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.NAME_COLUMN)));
        course.setPassed(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PASSED_COLUMN)));
        course.setPass_mark(
            cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PASS_MARK_COLUMN)));
      } while (cursor.moveToNext());
    }
    return cursor;
  }
  @Test
  public void testApplyCourse() {
    Course course = new Course();
    User student = new User();
    student.setUserType(UserType.STUDENT);
    student.applyCourse(course);

    assertEquals(1, student.getAppliedCourses().size());
    assertEquals(1, course.getApplicationNum());
  }
 @Override
 protected Object process(EntityManager em, Object... params) throws ClassNotFoundException {
   // TODO Auto-generated method stub
   DAO<Experiment> edao = new DAO<Experiment>(Experiment.class, em);
   Experiment e = edao.singleResultQueryRead(Experiment.class, "Experiment.getById", params[0]);
   DAO<Course> cdao = new DAO<Course>(Course.class, em);
   Course course = cdao.singleResultQueryRead(Course.class, "Course.getById", params[1]);
   course.removeExperiment(e);
   cdao.update(course);
   return null;
 }
示例#6
0
  @Override
  public List<Course> getCourses() {
    // TODO Auto-generated method stub

    List<Course> courses = null;
    Course course = null;
    Course preCourse = null;
    try {

      stmt = conn.prepareStatement("SELECT courseNo,courseName,credits,preCourseNos FROM Course");
      /*stmt.setString(1, admin.getCname());
      stmt.setString(2, admin.getCplace());*/
      ResultSet rs = stmt.executeQuery();
      courses = new ArrayList<Course>();
      while (rs.next()) {
        course =
            new Course(
                rs.getString("courseNo"),
                rs.getString("courseName"),
                Double.valueOf(rs.getString("credits")));
        if (rs.getString("preCourseNos") != null) { // ÊÇ·ñÓÐÑ¡ÐÞ¿Î
          String[] pres = rs.getString("preCourseNos").split(",");
          for (int i = 0; i < pres.length; i++) {

            preCourse = new Course(pres[i], "", 0);
            course.addPrerequisite(preCourse);
          }
        }
        courses.add(course);
      }
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
    return courses;
  }
  /*
  GET A SINGLE COURSE
   */
  public Course getCourse(long course_id) {

    try {
      this.open();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    String where = DB_ABSTRACTS.DBCourse.KEY_ID + " = " + course_id;
    // SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
    Professor prof = new Professor();

    Cursor cursor =
        database.query(
            DB_ABSTRACTS.DBTasks.DATABASE_TABLE, allColumns, where, null, null, null, null);
    Course course = new Course();
    course.setId(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.KEY_ID)));
    course.setName(cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.NAME_COLUMN)));
    prof.setProfessor_name(
        cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PROFESSOR_COLUMN)));
    course.setProfessor(prof);
    course.setSemester(
        cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.SEMESTER_COLUMN)));
    course.setRoom(cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.ROOM_COLUMN)));
    course.setPassed(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PASSED_COLUMN)));

    return course;
  }
  public int updateCourse(Course course) {

    try {
      this.open();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    ContentValues values = new ContentValues();
    values.put(DB_ABSTRACTS.DBCourse.NAME_COLUMN, course.getName());
    values.put(DB_ABSTRACTS.DBCourse.ROOM_COLUMN, course.getRoom());
    values.put(DB_ABSTRACTS.DBCourse.PROFESSOR_COLUMN, course.getProfessor().getProfessor_name());
    values.put(DB_ABSTRACTS.DBCourse.SEMESTER_COLUMN, course.getSemester());
    values.put(DB_ABSTRACTS.DBCourse.PASSED_COLUMN, course.getPassed());
    values.put(DB_ABSTRACTS.DBCourse.PASS_MARK_COLUMN, course.getPass_mark());

    String where = DB_ABSTRACTS.DBCourse.KEY_ID + " = ?";

    // update row
    return database.update(
        DB_ABSTRACTS.DBCourse.DATABASE_TABLE,
        values,
        where,
        new String[] {String.valueOf(course.getId())});
  }
  /*
  GET COURSES WITH ALL COLUMNS
   */
  public Cursor getAllCourseData() {

    database = course_dbHelper.getWritableDatabase();

    Cursor cursor =
        database.query(
            DB_ABSTRACTS.DBCourse.DATABASE_TABLE,
            allColumns,
            null,
            null,
            null,
            null,
            DB_ABSTRACTS.DBCourse.NAME_COLUMN + " ASC");
    Professor prof = new Professor();

    if (cursor.moveToFirst()) {
      do {
        Course course = new Course();
        course.setId(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.KEY_ID)));
        course.setName(cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.NAME_COLUMN)));
        prof.setProfessor_name(
            cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PROFESSOR_COLUMN)));
        course.setProfessor(prof);
        course.setSemester(
            cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.SEMESTER_COLUMN)));
        course.setRoom(cursor.getString(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.ROOM_COLUMN)));
        course.setPassed(cursor.getInt(cursor.getColumnIndex(DB_ABSTRACTS.DBCourse.PASSED_COLUMN)));
      } while (cursor.moveToNext());
    }
    return cursor;
  }
  /*
  INSERT INTO THE DATABASE
   */
  public long createCourse(Course course, Professor prof) {

    try {
      this.open();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    ContentValues values = new ContentValues();
    values.put(DB_ABSTRACTS.DBCourse.NAME_COLUMN, course.getName());
    values.put(DB_ABSTRACTS.DBCourse.ROOM_COLUMN, course.getRoom());
    values.put(DB_ABSTRACTS.DBCourse.PROFESSOR_COLUMN, prof.getProfessor_name());
    values.put(DB_ABSTRACTS.DBCourse.SEMESTER_COLUMN, course.getSemester());
    values.put(DB_ABSTRACTS.DBCourse.PASSED_COLUMN, course.getPassed());
    values.put(DB_ABSTRACTS.DBCourse.PASS_MARK_COLUMN, course.getPass_mark());

    long id = database.insert(DB_ABSTRACTS.DBCourse.DATABASE_TABLE, null, values);

    return id;
  }
  /**
   * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
   *
   * @param request servlet request
   * @param response servlet response
   * @throws ServletException if a servlet-specific error occurs
   * @throws IOException if an I/O error occurs
   */
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    HttpSession ss = request.getSession();
    Account ac = (Account) ss.getAttribute("ac");
    int cId = Integer.parseInt((Long) ss.getAttribute("cId") + "");
    Course c = Course.getCourseByID(cId);

    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("scoresheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Score sheet of " + c.getName() + " course");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

    List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore");
    int rownum = 2;
    int cellcount = 1;
    Row sumRow = sheet.createRow(rownum);
    sumRow.setHeightInPoints(55);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellValue("Student name");
    cell.setCellStyle(styles.get("header"));
    int countback = listStudentScore.get(0).getListStudentScore().size();
    int maxScore = 0;
    for (int i = countback - 1; i >= 0; i--) {
      cell = sumRow.createCell(cellcount);
      UserScore u = listStudentScore.get(0).getListStudentScore().get(i);
      cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")");
      cell.setCellStyle(styles.get("header"));
      cellcount++;
      maxScore += u.getFull_mark();
    }
    cell = sumRow.createCell(cellcount);
    cell.setCellValue("Total (" + maxScore + ")");
    cell.setCellStyle(styles.get("header"));
    rownum++;

    for (Account account : listStudentScore) {
      sumRow = sheet.createRow(rownum);
      sumRow.setHeightInPoints(35);
      cell = sumRow.createCell(0);
      cell.setCellValue(account.getFirstname() + " " + account.getLastname());
      int j = 1;
      for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) {
        UserScore usc = (UserScore) account.getListStudentScore().get(i);
        cell = sumRow.createCell(j);
        Assignment a = null;
        if (usc.getAss_type().equalsIgnoreCase("web")) {
          a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id());
          String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a);
          if (status.equalsIgnoreCase("ontime")
              || status.equalsIgnoreCase("hurryup")
              || status.equalsIgnoreCase("late")) {
            cell.setCellValue(usc.getStof().getScore());
          } else {
            status = Assignment.calculateTime(a);
            if (status.equalsIgnoreCase("miss")) {
              cell.setCellValue(usc.getStof().getScore());
            } else {
              cell.setCellValue("-");
            }
          }

        } else if (usc.getAss_type().equalsIgnoreCase("file")) {
          a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id());
          String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a);
          if (status.equalsIgnoreCase("ontime")
              || status.equalsIgnoreCase("hurryup")
              || status.equalsIgnoreCase("late")) {
            cell.setCellValue(usc.getStf().getScore());
          } else {
            status = Assignment.calculateTime(a);
            if (status.equalsIgnoreCase("miss")) {
              cell.setCellValue(usc.getStf().getScore());
            } else {
              cell.setCellValue("-");
            }
          }
        }
        j++;
      }
      cell = sumRow.createCell(j);
      int lastcol = account.getListStudentScore().size();

      // calculate column
      int dv = lastcol / 26;
      String coltmp = "";
      for (int i = 0; i < dv; i++) {
        coltmp += "A";
      }
      coltmp += (char) ('A' + (lastcol - (dv * 26)));
      System.out.println(coltmp);
      //

      String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1);
      System.out.println(ref);
      cell.setCellFormula("SUM(" + ref + ")");
      rownum++;
    }

    // Write the output to a file
    String filename = "scoresheet_" + c.getName() + ".xlsx";
    String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename;
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    response.sendRedirect("file/scoresheet/" + filename);

    //
    //        Workbook wb = new XSSFWorkbook();
    //        Sheet sheet = wb.createSheet("scoresheet");
    //        PrintSetup printSetup = sheet.getPrintSetup();
    //        printSetup.setLandscape(true);
    //        sheet.setFitToPage(true);
    //        sheet.setHorizontallyCenter(true);
    //
    //        //title row
    //        Row titleRow = sheet.createRow(0);
    //        titleRow.setHeightInPoints(45);
    //        Cell titleCell = titleRow.createCell(0);
    //        titleCell.setCellValue("Score sheet of " + "...." + " course");
    //        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
    //
    //        //row with totals below
    //        int rownum = 2;
    //        Row sumRow = sheet.createRow(rownum);
    //       sumRow.setHeightInPoints(35);
    //        Cell cell;
    //        cell = sumRow.createCell(0);
    //        cell.setCellValue("Name:");
    //
    //        for (int j = 1; j < 12; j++) {
    //            cell = sumRow.createCell(j);
    //            String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
    //            cell.setCellFormula("SUM(" + ref + ")");
    //        }
    //
    //        // Write the output to a file
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    //        FileOutputStream out = new FileOutputStream(file);
    //        wb.write(out);
    //        out.close();
  }