public HashMap SearchEmployees(
String key, int index, int size, String sortField, String sortOrder) throws Exception {
// System.Threading.Thread.Sleep(300);
if (key == null) key = "";
String sql =
"select a.*, b.name dept_name, c.name position_name, d.name educational_name \n"
+ "from t_employee a \n"
+ "left join t_department b \n"
+ "on a.dept_id = b.id \n"
+ "left join t_position c \n"
+ "on a.position = c.id \n"
+ "left join t_educational d \n"
+ "on a.educational = d.id \n"
+ "where a.name like '%"
+ key
+ "%' \n";
if (StringUtil.isNullOrEmpty(sortField) == false) {
if ("desc".equals(sortOrder) == false) sortOrder = "asc";
sql += " order by " + sortField + " " + sortOrder;
} else {
sql += " order by createtime desc";
}
ArrayList dataAll = DBSelect(sql);
// ʵ��һ���ڴ��ҳ(ʵ��Ӧ��ʹ��SQL��ҳ)
ArrayList data = new ArrayList();
int start = index * size, end = start + size;
for (int i = 0, l = dataAll.size(); i < l; i++) {
HashMap record = (HashMap) dataAll.get(i);
if (record == null) continue;
if (start <= i && i < end) {
data.add(record);
}
}
HashMap result = new HashMap();
result.put("data", data);
result.put("total", dataAll.size());
// ���һЩ������Ϣ
// 1)���䣺minAge, maxAge, avgAge
ArrayList ages =
DBSelect(
"select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_employee");
HashMap ageInfo = (HashMap) ages.get(0);
result.put("minAge", ageInfo.get("minAge"));
result.put("maxAge", ageInfo.get("maxAge"));
result.put("avgAge", ageInfo.get("avgAge"));
// 2)��Ա���� total
return result;
}
public String InsertEmployee(HashMap user) throws Exception {
String id =
(user.get("id") == null || user.get("id").toString().equals(""))
? UUID.randomUUID().toString()
: user.get("id").toString();
user.put("id", id);
if (user.get("name") == null) user.put("name", "");
if (StringUtil.isNullOrEmpty(user.get("gender").toString())) user.put("gender", 0);
Connection conn = getConn();
String sql =
"insert into t_employee (id, loginname, name, age, married, gender, birthday, country, city, dept_id, position, createtime, salary, educational, school, email, remarks)"
+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, ToString(user.get("id")));
stmt.setString(2, ToString(user.get("loginname")));
stmt.setString(3, ToString(user.get("name")));
stmt.setInt(4, ToInt(user.get("age")));
stmt.setInt(5, ToInt(user.get("married")));
stmt.setInt(6, ToInt(user.get("gender")));
stmt.setTimestamp(7, ToDate(user.get("birthday")));
stmt.setString(8, ToString(user.get("country")));
stmt.setString(9, ToString(user.get("city")));
stmt.setString(10, ToString(user.get("dept_id")));
stmt.setString(11, ToString(user.get("position")));
stmt.setTimestamp(12, ToDate(user.get("createtime")));
stmt.setString(13, ToString(user.get("salary")));
stmt.setString(14, ToString(user.get("educational")));
stmt.setString(15, ToString(user.get("school")));
stmt.setString(16, ToString(user.get("email")));
stmt.setString(17, ToString(user.get("remarks")));
stmt.executeUpdate();
stmt.close();
conn.close();
return id;
}