staffbean.java

1 系统简介 1.1 系统名称：bugtrail 1.2 适用范围：大型或小型公司 1.3 用途：在项目周期内进行项目管理

/*
 * 创建日期 2006-12-16
 *
 * TODO 要更改此生成的文件的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
package bugtrail.bean;

import java.sql.ResultSet;
import java.util.Vector;

import bugtrail.basic.Staff;
import bugtrail.util.CharsetProcess;
import bugtrail.util.MD5;
import bugtrail.util.SqlHelper;
public class StaffBean {
	private SqlHelper helper;

	//得到一个连接
//	public StaffBean() throws Exception{
//		helper = new SqlHelper();
//	}

	//用户注册
	public void userRegister(Staff staff) throws Exception{
		helper = new SqlHelper();
		helper.updateHelper("insert into staff(password,name,email,pswQuestion," +
				"pswAnswer,role,department,address,phone,remark) values('"
				+MD5.MD5(staff.getPassword())+"','"
				+CharsetProcess.U2C(staff.getName())+"','"
				+staff.getEmail()+"','"
				+CharsetProcess.U2C(staff.getPswQuestion())+"','"
				+CharsetProcess.U2C(staff.getPswAnswer())+"','"
				+0+"','"
				+staff.getDepartment()+"','"
				+CharsetProcess.U2C(staff.getAddress())+"','"
				+staff.getPhone()+"','"
				+CharsetProcess.U2C(staff.getRemark())+"')");	
		helper.closeAll();
	}
	
	//得到注册用户的id
	public int getStaffID(Staff staff) throws Exception{
		int id = 0;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select staffID from staff where name='"
				+CharsetProcess.U2C(staff.getName())+"' and password='"
				+MD5.MD5(staff.getPassword())+"'");
		while(rs.next())
			id = rs.getInt(1);
		helper.closeAll();
		return id;
	}
	
	//添加员工
	public void addStaff(Staff staff) throws Exception{
		helper = new SqlHelper();
		helper.updateHelper("insert into staff(password,name,email,role,department," +
				"address,phone,remark) values('"
				+MD5.MD5(staff.getPassword())+"','"
				+CharsetProcess.U2C(staff.getName())+"','"
				+staff.getEmail()+"','"
				+staff.getRole()+"','"
				+staff.getDepartment()+"','"
				+CharsetProcess.U2C(staff.getAddress())+"','"
				+staff.getPhone()+"','"
				+CharsetProcess.U2C(staff.getRemark())+"')");	
		helper.closeAll();
	}
	
	//删除员工
	public void deleteStaff(int staffID) throws Exception{
		helper = new SqlHelper();
		helper.updateHelper("delete from staff where staffID='" +staffID+"'");	
		helper.closeAll();
	}
	
	//修改个人信息
	public void modifyMyInfo(Staff staff) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("update staff set name='" 
				+ CharsetProcess.U2C(staff.getName())
				+ "',email='" + staff.getEmail()
				+"',pswQuestion='"+ CharsetProcess.U2C(staff.getPswQuestion())
				+"',pswAnswer='"+CharsetProcess.U2C(staff.getPswAnswer())
				+"', address='"+ CharsetProcess.U2C(staff.getAddress()) 
				+ "', phone='"+ staff.getPhone()
				+ "', remark='"+ CharsetProcess.U2C(staff.getRemark())
				+ "' where staffID='"+ staff.getStaffID() + "'");
		helper.closeAll();
	}
	
	//修改用户信息
	public void modifyUser(Staff staff) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("update staff set name='" 
				+ CharsetProcess.U2C(staff.getName())
				+ "',email='" + staff.getEmail()
				+ "',role='" + staff.getRole()
				+ "',department='" + staff.getDepartment()
				+ "',address='"+ CharsetProcess.U2C(staff.getAddress()) 
				+ "',phone='"+ staff.getPhone()
				+ "',remark='"+ CharsetProcess.U2C(staff.getRemark())
				+ "'where staffID='"+ staff.getStaffID() + "'");
		helper.closeAll();
	}
	
	
	//修改用户密码
	public void modifyUserPsw(int staffID,String password) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("update staff set password='" 
				+ MD5.MD5(password)
				+ "'where staffID='"+ staffID + "'");
		helper.closeAll();
	}

	//返回staff表里某个role的总条目数
	public int getRoleCount(int roleID) throws Exception{
		int num = 0;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select count(*) from staff where role='"+roleID+"'");
		while(rs.next())
			num = rs.getInt(1);
		helper.closeAll();
		return num;
	}
	
	public void changePassword(int myID, String newPassword) throws Exception{
		helper = new SqlHelper();
		helper.updateHelper("update staff set password='"
				+MD5.MD5(newPassword)+"' where " +
				"staffID='"+myID+"'");
		helper.closeAll();
	}
	
	//返回staff表里的总条目数
	public int getAvilableCount() throws Exception{
		int num = 0;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select count(*) from staff");
		while(rs.next())
			num = rs.getInt(1);
		helper.closeAll();
		return num;
	}
	
	public Vector getAllStaff()throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff");
		Vector<Staff> staffList = new Vector<Staff>();
		while(rs.next()){
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			temp.setPassword(rs.getString("password"));
			temp.setEmail(rs.getString("email"));
			temp.setRole(rs.getInt("role"));
			temp.setDepartment(rs.getInt("department"));
			temp.setPswQuestion(rs.getString("pswQuestion"));
			temp.setPswAnswer(rs.getString("pswAnswer"));
			temp.setAddress(rs.getString("address"));
			temp.setRemark(rs.getString("remark"));
			temp.setPhone(rs.getInt("phone"));
			staffList.add(temp);
		}
		helper.closeAll();
		return staffList;
	}
	
	public Vector getPartStaff(Integer pageNum,Integer rows)throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff",pageNum,rows);
		Vector<Staff> staffList = new Vector<Staff>();
		while(rs.next()){
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			temp.setPassword(rs.getString("password"));
			temp.setEmail(rs.getString("email"));
			temp.setRole(rs.getInt("role"));
			temp.setDepartment(rs.getInt("department"));
			temp.setPswQuestion(rs.getString("pswQuestion"));
			temp.setPswAnswer(rs.getString("pswAnswer"));
			temp.setAddress(rs.getString("address"));
			temp.setRemark(rs.getString("remark"));
			temp.setPhone(rs.getInt("phone"));
			staffList.add(temp);
		}
		helper.closeAll();
		return staffList;
	}
	
	public Staff getStaff(int staffID) throws Exception{
		Staff staff = new Staff();
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where staffID='"
				+staffID+"'");
		while(rs.next()){
			staff.setStaffID(rs.getInt("staffID"));
			staff.setName(rs.getString("name"));
			staff.setPassword(rs.getString("password"));
			staff.setEmail(rs.getString("email"));
			staff.setRole(rs.getInt("role"));
			staff.setDepartment(rs.getInt("department"));
			staff.setPswQuestion(rs.getString("pswQuestion"));
			staff.setPswAnswer(rs.getString("pswAnswer"));
			staff.setAddress(rs.getString("address"));
			staff.setRemark(rs.getString("remark").toString());
			staff.setPhone(rs.getInt("phone"));
		}
		helper.closeAll();
		return staff;
	}

	public Vector getStaffByDep(int depID)throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where department='"
				+depID+"'");
		Vector<Staff> staffList = new Vector<Staff>();
		while(rs.next()){
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			temp.setPassword(rs.getString("password"));
			temp.setEmail(rs.getString("email"));
			temp.setRole(rs.getInt("role"));
			temp.setDepartment(rs.getInt("department"));
			temp.setPswQuestion(rs.getString("pswQuestion"));
			temp.setPswAnswer(rs.getString("pswAnswer"));
			temp.setAddress(rs.getString("address"));
			temp.setRemark(rs.getString("remark"));
			temp.setPhone(rs.getInt("phone"));
			staffList.add(temp);
		}
		helper.closeAll();
		return staffList;
	}
	
	public Vector getStaffByRole(int roleID)throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where role='"
				+roleID+"'");
		Vector<Staff> staffList = new Vector<Staff>();
		while(rs.next()){
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			temp.setPassword(rs.getString("password"));
			temp.setEmail(rs.getString("email"));
			temp.setRole(rs.getInt("role"));
			temp.setDepartment(rs.getInt("department"));
			temp.setPswQuestion(rs.getString("pswQuestion"));
			temp.setPswAnswer(rs.getString("pswAnswer"));
			temp.setAddress(rs.getString("address"));
			temp.setRemark(rs.getString("remark"));
			temp.setPhone(rs.getInt("phone"));
			staffList.add(temp);
		}
		helper.closeAll();
		return staffList;
	}
	
	//得到具有一定权限的用户
	public Vector getStaffByGrant(String grant)throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where role in " +
				"(select roleID from role where "+grant+ "= '1')");
		Vector<Staff> staffList = new Vector<Staff>();
		while(rs.next()){
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
//			temp.setPassword(rs.getString("password"));
//			temp.setEmail(rs.getString("email"));
//			temp.setRole(rs.getInt("role"));
//			temp.setDepartment(rs.getInt("department"));
//			temp.setPswQuestion(rs.getString("pswQuestion"));
//			temp.setPswAnswer(rs.getString("pswAnswer"));
//			temp.setAddress(rs.getString("address"));
//			temp.setRemark(rs.getString("remark"));
//			temp.setPhone(rs.getInt("phone"));
			staffList.add(temp);
		}
		helper.closeAll();
		return staffList;
	}

	public String getStaffName(int staffID) throws Exception{
		String name = "";
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select name from staff where staffID='"
				+staffID+"'");
		while(rs.next())
			name = rs.getString(1);
		helper.closeAll();
		return name;
	}
	
	public void modityUserRole(int staffID,int role) throws Exception{
		helper = new SqlHelper();
		helper.updateHelper("update staff set role='"
				+role+"' where " +"staffID='"+staffID+"'");
		helper.closeAll();
	}
	
	//得到参与项目的测试者
	public Vector getAllTesters() throws Exception {
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where staffID in " +
				"(select finder from bug)");
	    Vector<Staff> select = new Vector<Staff>();
	    while (rs.next()) {
	    	Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			select.add(temp);
	    }
	    helper.closeAll();
	    return select;
	}
	
	//得到参与项目的开发人员
	public Vector getAllDevelopers() throws Exception {
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from staff where staffID in " +
				"(select owner from bug)");
	    Vector<Staff> select = new Vector<Staff>();
	    while (rs.next()) {
	    	Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			select.add(temp);
	    }
	    helper.closeAll();
	    return select;
	}
	
}