public static List<Account> getWaitForApproveMemberInCourse(int course_id) {
List<Account> listAccount = new ArrayList<>();
Connection conn = ConnectionBuilder.getConnection();
String sql =
"select * from account where acc_id in (select acc_id from account_course where course_id=? AND status = \"waiting\")";
PreparedStatement pstm;
Account acc = null;
try {
pstm = conn.prepareStatement(sql);
pstm.setInt(1, course_id);
ResultSet rs = pstm.executeQuery();
while (rs.next()) {
// acc = Account.getAccountByID(rs.getInt("acc_id"));
acc = new Account();
acc.setAcc_id(rs.getInt("acc_id"));
acc.setFirstname(rs.getString("firstname"));
acc.setLastname(rs.getString("lastname"));
acc.setEmail(rs.getString("email"));
acc.setAccount_type(rs.getString("account_type"));
acc.setProfile_pic(rs.getString("profile_pic"));
acc.setRegister_date(rs.getTimestamp("register_date"));
listAccount.add(acc);
}
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Account.class.getName()).log(Level.SEVERE, null, ex);
}
return listAccount;
}
public static Map<AccountCourse, Account> getMemberInCourseWithRole(int course_id) {
Map<AccountCourse, Account> listAccount = new HashMap<>();
Connection conn = ConnectionBuilder.getConnection();
String sql =
"select * from account a join account_course m on a.acc_id = m.acc_id where course_id=? and a.acc_id in (select acc_id from account_course where course_id=? AND status = \"approved\")";
PreparedStatement pstm;
Account acc = null;
try {
pstm = conn.prepareStatement(sql);
pstm.setInt(1, course_id);
pstm.setInt(2, course_id);
ResultSet rs = pstm.executeQuery();
while (rs.next()) {
// Account acc = Account.getAccountByID(rs.getInt("acc_id"));
acc = new Account();
acc.setAcc_id(rs.getInt("acc_id"));
acc.setFirstname(rs.getString("firstname"));
acc.setLastname(rs.getString("lastname"));
acc.setEmail(rs.getString("email"));
acc.setAccount_type(rs.getString("account_type"));
acc.setProfile_pic(rs.getString("profile_pic"));
acc.setRegister_date(rs.getTimestamp("register_date"));
AccountCourse acCourse = new AccountCourse();
acCourse.setRole(rs.getString("role"));
listAccount.put(acCourse, acc);
}
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Account.class.getName()).log(Level.SEVERE, null, ex);
}
return listAccount;
}
public static List<Account> whoNotJoin(int course_id) {
List<Account> listAccount = new ArrayList<>();
Connection conn = ConnectionBuilder.getConnection();
Account acc = null;
String sql =
"select a.* "
+ "from import_student_list imst "
+ "join account a on imst.email = a.email "
+ "where imst.course_id = ? "
+ "and imst.email not in ( select a.email "
+ " from account_course ac "
+ " join account a "
+ " on ac.acc_id = a.acc_id "
+ " where ac.course_id=? and ac.status = 'approved') ";
PreparedStatement pstm;
try {
pstm = conn.prepareStatement(sql);
pstm.setInt(1, course_id);
pstm.setInt(2, course_id);
ResultSet rs = pstm.executeQuery();
while (rs.next()) {
acc = new Account();
acc.setAcc_id(rs.getInt("acc_id"));
acc.setFirstname(rs.getString("firstname"));
acc.setLastname(rs.getString("lastname"));
acc.setEmail(rs.getString("email"));
// acc.setPassword(rs.getString("password"));
acc.setAccount_type(rs.getString("account_type"));
acc.setProfile_pic(rs.getString("profile_pic"));
acc.setRegister_date(rs.getTimestamp("register_date"));
listAccount.add(acc);
}
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Account.class.getName()).log(Level.SEVERE, null, ex);
}
return listAccount;
}
