public int employeeCount(Connection con, Employee employee, String bbirthday, String ebirthday)
throws Exception {
StringBuffer sb =
new StringBuffer(
"select count(*) as total from t_employee s,t_department g where s.departmentId=g.departmentId");
if (employee != null && !StringUtil.isEmpty(employee.getEmployeeNo())) {
sb.append(" and s.employeeNo like '%" + employee.getEmployeeNo() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getName())) {
sb.append(" and s.name like '%" + employee.getName() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getSex())) {
sb.append(" and s.sex = '" + employee.getSex() + "'");
}
if (employee != null && !StringUtil.isEmpty(employee.getNationality())) {
sb.append(" and s.nationality like '%" + employee.getNationality() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getEducation())) {
sb.append(" and s.education like '%" + employee.getEducation() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getPosition())) {
sb.append(" and s.position like '%" + employee.getPosition() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getEducation())) {
sb.append(" and s.education like '%" + employee.getEducation() + "%'");
}
if (employee.getDepartmentId() != -1) {
sb.append(" and s.departmentId ='" + employee.getDepartmentId() + "'");
}
if (StringUtil.isNotEmpty(bbirthday)) {
sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('" + bbirthday + "')");
}
if (StringUtil.isNotEmpty(ebirthday)) {
sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('" + ebirthday + "')");
}
PreparedStatement pstmt = con.prepareStatement(sb.toString());
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
return rs.getInt("total");
} else {
return 0;
}
}
public ResultSet employeeList(
Connection con, PageBean pageBean, Employee employee, String bbirthday, String ebirthday)
throws Exception {
StringBuffer sb =
new StringBuffer(
"select * from t_employee s,t_department g where s.departmentId=g.departmentId");
if (employee != null && !StringUtil.isEmpty(employee.getEmployeeNo())) {
sb.append(" and s.employeeNo like '%" + employee.getEmployeeNo() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getName())) {
sb.append(" and s.name like '%" + employee.getName() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getSex())) {
sb.append(" and s.sex = '" + employee.getSex() + "'");
}
if (employee != null && !StringUtil.isEmpty(employee.getNationality())) {
sb.append(" and s.nationality like '%" + employee.getNationality() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getEducation())) {
sb.append(" and s.education like '%" + employee.getEducation() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getPosition())) {
sb.append(" and s.position like '%" + employee.getPosition() + "%'");
}
if (employee != null && !StringUtil.isEmpty(employee.getEducation())) {
sb.append(" and s.education like '%" + employee.getEducation() + "%'");
}
if (employee.getDepartmentId() != -1) {
sb.append(" and s.departmentId ='" + employee.getDepartmentId() + "'");
}
if (StringUtil.isNotEmpty(bbirthday)) {
sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('" + bbirthday + "')");
}
if (StringUtil.isNotEmpty(ebirthday)) {
sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('" + ebirthday + "')");
}
if (pageBean != null) {
sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
}
PreparedStatement pstmt = con.prepareStatement(sb.toString());
return pstmt.executeQuery();
}
public int employeeAdd(Connection con, Employee employee) throws Exception {
String sql = "insert into t_employee values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, employee.getEmployeeNo());
pstmt.setString(2, employee.getName());
pstmt.setString(3, employee.getSex());
pstmt.setString(4, DateUtil.formatDate(employee.getBirthday(), "yyyy-MM-dd"));
pstmt.setString(5, employee.getNationality());
pstmt.setString(6, employee.getEducation());
pstmt.setString(7, employee.getProfession());
pstmt.setInt(8, employee.getDepartmentId());
pstmt.setString(9, employee.getPosition());
pstmt.setFloat(10, employee.getBaseMoney());
pstmt.setFloat(11, employee.getOvertime());
pstmt.setFloat(12, employee.getAge());
pstmt.setFloat(13, employee.getCheck1());
pstmt.setFloat(14, employee.getAbsent());
pstmt.setFloat(15, employee.getSafety());
return pstmt.executeUpdate();
}
public int employeeModify(Connection con, Employee employee) throws Exception {
String sql =
"update t_employee set employeeNo=?,name=?,sex=?,birthday=?,nationality=?,education=?,profession=?,departmentId=?,position=?,baseMoney=?,overtime=?,age=?,check1=?,absent=?,safety=? where employeeId=?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, employee.getEmployeeNo());
pstmt.setString(2, employee.getName());
pstmt.setString(3, employee.getSex());
pstmt.setString(4, DateUtil.formatDate(employee.getBirthday(), "yyyy-MM-dd"));
pstmt.setString(5, employee.getNationality());
pstmt.setString(6, employee.getEducation());
pstmt.setString(7, employee.getProfession());
pstmt.setInt(8, employee.getDepartmentId());
pstmt.setString(9, employee.getPosition());
pstmt.setFloat(10, employee.getBaseMoney());
pstmt.setFloat(11, employee.getOvertime());
pstmt.setFloat(12, employee.getAge());
pstmt.setFloat(13, employee.getCheck1());
pstmt.setFloat(14, employee.getAbsent());
pstmt.setFloat(15, employee.getSafety());
pstmt.setInt(16, employee.getEmployeeId());
return pstmt.executeUpdate();
}