@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);
}
}
}
@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;
}
@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();
}