public ArrayList<Object> getAppointments(AdminUser user) {
ArrayList<Object> appointments = new ArrayList<Object>();
try {
Connection conn = this.connectDB();
PreparedStatement statement;
String command =
"SELECT advisor_settings.pname,advisor_settings.email,advising_date,advising_starttime,advising_endtime,appointment_type,id FROM appointments INNER JOIN advisor_settings "
+ "WHERE advisor_settings.userid = appointments.advisor_userid";
statement = conn.prepareStatement(command);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
Appointment set = new Appointment();
set.setPname(rs.getString(1));
set.setAdvisorEmail(rs.getString(2));
set.setAdvisingDate(rs.getString(3));
set.setAdvisingStartTime(rs.getString(4));
set.setAdvisingEndTime(rs.getString(5));
set.setAppointmentType(rs.getString(6));
set.setAppointmentId(rs.getInt(7));
appointments.add(set);
}
conn.close();
} catch (Exception e) {
System.out.printf(e.toString());
}
return appointments;
}
public Boolean createAppointment(Appointment a, String email) {
Boolean result = false;
int student_id = 0;
int advisor_id = 0;
try {
Connection conn = this.connectDB();
PreparedStatement statement;
String command = "SELECT userid from user where email=?";
statement = conn.prepareStatement(command);
statement.setString(1, email);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
student_id = rs.getInt(1);
}
command = "SELECT userid FROM advisor_settings WHERE advisor_settings.pname=?";
statement = conn.prepareStatement(command);
statement.setString(1, a.getPname());
rs = statement.executeQuery();
while (rs.next()) {
advisor_id = rs.getInt(1);
}
// check for slots already taken
command =
"SELECT COUNT(*) FROM advising_schedule WHERE userid=? AND advising_date=? AND advising_starttime=? AND advising_endtime=? AND studentid is not null";
statement = conn.prepareStatement(command);
statement.setInt(1, advisor_id);
statement.setString(2, a.getAdvisingDate());
statement.setString(3, a.getAdvisingStartTime());
statement.setString(4, a.getAdvisingEndTime());
rs = statement.executeQuery();
while (rs.next()) {
if (rs.getInt(1) < 1) {
command =
"INSERT INTO appointments (id,advisor_userid,student_userid,advising_date,advising_starttime,advising_endtime,appointment_type,studentid,description,student_email)"
+ "VALUES(?,?,?,?,?,?,?,?,?,?)";
statement = conn.prepareStatement(command);
statement.setInt(1, a.getAppointmentId());
statement.setInt(2, advisor_id);
statement.setInt(3, student_id);
statement.setString(4, a.getAdvisingDate());
statement.setString(5, a.getAdvisingStartTime());
statement.setString(6, a.getAdvisingEndTime());
statement.setString(7, a.getAppointmentType());
statement.setInt(8, Integer.parseInt(a.getStudentid()));
statement.setString(9, a.getDescription());
statement.setString(10, email);
statement.executeUpdate();
command =
"UPDATE advising_schedule SET studentid=? where userid=? AND advising_date=? and advising_starttime >= ? and advising_endtime <= ?";
statement = conn.prepareStatement(command);
statement.setInt(1, Integer.parseInt(a.getStudentid()));
statement.setInt(2, advisor_id);
statement.setString(3, a.getAdvisingDate());
statement.setString(4, a.getAdvisingStartTime());
statement.setString(5, a.getAdvisingEndTime());
statement.executeUpdate();
result = true;
}
}
conn.close();
} catch (Exception e) {
System.out.printf(e.toString());
}
return result;
}
public ArrayList<Object> getAppointments(StudentUser user) {
ArrayList<Object> appointments = new ArrayList<Object>();
try {
Connection conn = this.connectDB();
PreparedStatement statement;
String command =
"SELECT advisor_settings.pname,advisor_settings.email,advising_date,advising_starttime,advising_endtime,appointment_type,id,description,user.email FROM USER,APPOINTMENTS,ADVISOR_SETTINGS "
+ "WHERE USER.email=? AND user.userid=appointments.student_userid AND advisor_settings.userid=appointments.advisor_userid";
statement = conn.prepareStatement(command);
statement.setString(1, user.getEmail());
ResultSet rs = statement.executeQuery();
while (rs.next()) {
Appointment set = new Appointment();
set.setPname(rs.getString(1));
set.setAdvisorEmail(rs.getString(2));
set.setAdvisingDate(rs.getString(3));
set.setAdvisingStartTime(rs.getString(4));
set.setAdvisingEndTime(rs.getString(5));
set.setAppointmentType(rs.getString(6));
set.setAppointmentId(rs.getInt(7));
set.setDescription(rs.getString(8));
set.setStudentid("Advisor only");
set.setStudentEmail(rs.getString(9));
appointments.add(set);
}
conn.close();
} catch (Exception e) {
System.out.printf(e.toString());
}
return appointments;
}
public UpdateAppointment(Appointment a) {
description = a.getDescription();
studentid = a.getStudentId();
id = a.getAppointmentId();
}