Пример #1
0
  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;
  }
Пример #2
0
 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;
 }
Пример #3
0
  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();
 }