private List<User> selectBySQL(String sql) {
List<User> res = new ArrayList<User>();
ResultSet rs = dbOperator.select(sql);
try {
while (rs.next()) {
res.add(setInfoToCreateUser(rs));
}
dbOperator.close();
} catch (SQLException e) {
e.printStackTrace();
;
}
return res;
}
public boolean update(User user) {
if (user.getUser_name() != null && user.getUser_name() != "") {
dbOperator.update(
String.format(UPDATE_USER, "user_name", user.getUser_name(), user.getUser_id()));
}
if (user.getDepartment_id() > 0) {
dbOperator.update(
String.format(UPDATE_USER, "department_id", user.getDepartment_id(), user.getUser_id()));
}
if (user.getStaff_position() != null && user.getStaff_position() != "") {
dbOperator.update(
String.format(
UPDATE_USER, "staff_position", user.getStaff_position(), user.getUser_id()));
}
if (user.getPwd() != null && user.getPwd() != "") {
dbOperator.update(String.format(UPDATE_USER, "pwd", user.getPwd(), user.getUser_id()));
}
return true;
}
public boolean add(User user) {
return dbOperator.add(
String.format(
ADD_USER,
user.getUser_id(),
user.getUser_name(),
user.getPwd(),
user.getDepartment_id() > 0 ? user.getDepartment_id() : null,
user.getStaff_position() != null ? "'" + user.getStaff_position() + "'" : null,
user.getTotal_annual_leave() != 0 ? user.getTotal_annual_leave() : null));
}
public User getDepartmentManager(int dep_id) {
ResultSet rs = dbOperator.select(String.format(SELECT_DEPARTMENT_MANAGER, dep_id));
// String pwd=pwdi+"";
try {
if (rs.next()) {
return setInfoToCreateUser(rs);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public String getPwd(long id) {
ResultSet rs = dbOperator.select(String.format(SELECT_PWD, id));
Random rand = new Random();
int pwdi = rand.nextInt();
String pwd = pwdi + "";
try {
if (rs.next()) {
pwd = rs.getString("pwd");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return pwd;
}
private User setInfoToCreateUser(ResultSet rs) {
User user = new User();
user.setUser_id(Long.parseLong(dbOperator.getItemResult(rs, "U.user_id")));
user.setUser_name(dbOperator.getItemResult(rs, "U.user_name"));
user.setStaff_position(dbOperator.getItemResult(rs, "U.staff_position"));
user.setDepartment_id(Integer.parseInt(dbOperator.getItemResult(rs, "U.department_id")));
user.setTotal_annual_leave(
Integer.parseInt(dbOperator.getItemResult(rs, "U.total_annual_leave")));
user.setDepartment_abbr(dbOperator.getItemResult(rs, "D.department_abbr"));
user.setDepartment_name(dbOperator.getItemResult(rs, "D.department_name"));
return user;
}
public boolean delete(long id) {
return dbOperator.delete(String.format(DELETE_USER_BY_ID, id));
}
public boolean updateByAnnualleave(User user) {
return dbOperator.update(
String.format(UPDATE_USER_BY_ANNUALLEAVE, user.getTotal_annual_leave(), user.getUser_id()));
}