public ArrayList<Account> getAccountList() {
Connection connect = null;
try {
ArrayList<Account> accounts = new ArrayList<>();
connect = DBConnection.getConnection();
String sql = "SELECT * FROM account" + "ORDER BY name desc";
ResultSet result = Utility.queryOperation(connect, sql);
Account temp = new Account();
while (result.next()) {
temp.setName(result.getString("name"));
temp.setEmail(result.getString("email"));
temp.setSecondaryEmail(result.getString("secondaryEmail"));
temp.setTypeAccount(result.getString("typeAccount"));
temp.setPassword(result.getString("password"));
temp.setAdmin(result.getBoolean("isAdministrator"));
accounts.add(temp);
}
return accounts;
} catch (SQLException ex) {
Logger.getLogger(AccountManager.class.getName()).log(Level.SEVERE, null, ex);
} finally {
DBConnection.releaseConnection(connect);
}
return null;
}
public ArrayList<Account> searchUser(String search, String type) throws SQLException {
Connection connect = null;
ArrayList<Account> accounts;
String sql =
"SELECT * from account WHERE "
+ "name LIKE '%"
+ search
+ "%'"
+ "AND typeAccount = '"
+ type
+ "'";
String sql2 = "SELECT * from account WHERE " + "typeAccount ='" + type + "'";
try {
connect = DBConnection.getConnection();
accounts = new ArrayList<>();
if (search.isEmpty()) {
ResultSet result = Utility.queryOperation(connect, sql2);
Account temp = new Account();
while (result.next()) {
temp.setName(result.getString("name"));
temp.setEmail(result.getString("email"));
temp.setSecondaryEmail(result.getString("secondaryEmail"));
temp.setTypeAccount(result.getString("typeAccount"));
temp.setPassword(result.getString("password"));
temp.setAdmin(result.getBoolean("isAdministrator"));
accounts.add(temp);
}
} else {
ResultSet result = Utility.queryOperation(connect, sql);
Account temp = new Account();
while (result.next()) {
temp.setName(result.getString("name"));
temp.setEmail(result.getString("email"));
temp.setSecondaryEmail(result.getString("secondaryEmail"));
temp.setTypeAccount(result.getString("typeAccount"));
temp.setPassword(result.getString("password"));
temp.setAdmin(result.getBoolean("isAdministrator"));
accounts.add(temp);
}
}
} finally {
DBConnection.releaseConnection(connect);
}
return accounts;
}
public void changeType(Account pAccount, String newType)
throws SQLException, ConnectionException, NullAccountException, EmailException {
String demotionSql =
"DELETE FROM " // cancella vecchie info
+ pAccount.getTypeAccount()
+ "WHERE fkAccount = '"
+ testEmail(pAccount.getSecondaryEmail())
+ "'";
String toProfessorSql =
"INSERT INTO professor " // se nuovo professor
+ "(fkAccount,link,department)"
+ "VALUES ('"
+ testEmail(pAccount.getSecondaryEmail())
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "'";
String toPhdSql =
"INSERT INTO phdstudent "
+ "(fkAccount,telephone,link,deparment,researchInterest,fkCycle"
+ "fkCurriculum, fkProfessor )" // nuovo dottorando
+ "VALUES ('"
+ testEmail(pAccount.getSecondaryEmail())
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "',"
+ "'"
+ "null"
+ "'";
String changeTypeSql =
"UPDATE account" // aggiorna il tipo
+ "set typeAccount = '"
+ newType
+ "' WHERE email = '"
+ pAccount.getEmail();
Connection connect = null;
try {
connect = DBConnection.getConnection();
pAccount = testAccount(pAccount);
if (newType.equals("phdstudent") && pAccount.getTypeAccount().equals("basic")) {
Utility.executeOperation(connect, toPhdSql); // diventa un dottorando
Utility.executeOperation(connect, changeTypeSql); // cambia tipo in account
} else if (newType.equals("phdstudent") && pAccount.getTypeAccount().equals("professor")) {
Utility.executeOperation(connect, demotionSql); // perde info phd
Utility.executeOperation(connect, toPhdSql); // nuove info prof
Utility.executeOperation(connect, changeTypeSql);
} else if (newType.equals("professor") && pAccount.getTypeAccount().equals("basic")) {
Utility.executeOperation(connect, toProfessorSql);
Utility.executeOperation(connect, changeTypeSql);
} else if (newType.equals("professor") && pAccount.getTypeAccount().equals("phdstudent")) {
Utility.executeOperation(connect, demotionSql);
Utility.executeOperation(connect, toProfessorSql);
Utility.executeOperation(connect, changeTypeSql);
} else if (newType.equals("basic")) {
Utility.executeOperation(connect, demotionSql);
Utility.executeOperation(connect, changeTypeSql);
}
} finally {
DBConnection.releaseConnection(connect);
}
}