groupbean.java

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

package bugtrail.bean;

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

import bugtrail.basic.BugCount;
import bugtrail.basic.Role;
import bugtrail.basic.Staff;
import bugtrail.util.SqlHelper;

/**
 * Creation date: 12-25-2006
 * 
 * @author Carol-W
 *
 */
public class GroupBean {
	
	private SqlHelper helper;

	// 得到一个连接
//	public GroupBean() throws Exception {
//		helper = new SqlHelper();
//	}
	
	// 添加一个staff到工作组中。
	public void addGroup(int projectID, int staffID) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("insert into `group` values (" + projectID + "," + staffID + ");");
	    helper.closeAll();
	}
	
	// 删除工作组中的一个staff。
	public void deleteGroup(int projectID, int staffID) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("delete from `group` where groupID = " 
				+ projectID + " and staffID = " + staffID + ";");
		helper.closeAll();
	}
	
	// 利用session中更新后的Vector<Staff>:updateStaffList更新工作组。
	public void updateGroup(int projectID, Vector update) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("delete from `group` where groupID = " + projectID + ";");
		Iterator i = update.iterator();
		while (i.hasNext()) {
			Staff temp = (Staff)i.next();
			this.addGroup(projectID, temp.getStaffID());
		}
		helper.closeAll();
	}
	
	// 得到符合的所有角色。（即除项目经理外，有操作bug权限的角色）
	public Vector getProRole() throws Exception {
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select roleID,name from role " +
				"where addBug='1' or editBug='1' or validateBug='1' or decideBug='1'" +
				"or assign_wait_deal_Bug='1' or assign_wait_validate_Bug='1';");
		Vector<Role> roles = new Vector<Role>();
		while(rs.next()) {
			Role temp = new Role();
			temp.setRoleID(rs.getInt("roleID"));
			temp.setName(rs.getString("name"));
			if (temp.getRoleID() != 3) {
				roles.add(temp);
			}
		}
		helper.closeAll();
		return roles;		
	}

	// 得到所有当前项目中为当前角色的staff。
	public Vector getGroupMembers(int roleID, int projectID) throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select staffID,name from staff where role = " 
				+ roleID + " and staffID in (select staffID from `group` where groupID = "
				+ projectID +")");
		Vector<Staff> members = new Vector<Staff>();
		while (rs.next()) {
			Staff temp = new Staff();
			temp.setStaffID(rs.getInt("staffID"));
			temp.setName(rs.getString("name"));
			members.add(temp);
		}
		helper.closeAll();
		return members;
	}
	
	// 得到所有不在当前项目且角色符合的staff。
	public Vector getSelectMembers(int roleID, int projectID) throws Exception {
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select staffID,name from staff " +
				"where role = " + roleID + " and staffID not in ( select staffID from `group` where groupID = " + projectID + ");");
	    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 getAllTesters(int projectID, int total) throws Exception {
		helper = new SqlHelper();
		String s;
		if (projectID == 0) {
			s = "";
		} else {
			s = "and b.project=" + projectID;
		}
		ResultSet rs = helper.executeHelper("select finder,name,count(bugID)as countBug from bug b,staff s " +
				"where b.finder=s.staffID " + s + " group by finder;");
	    Vector<BugCount> select = new Vector<BugCount>();
	    while (rs.next()) {
	    	BugCount temp = new BugCount();
			temp.setStaffID(rs.getInt("finder"));
			temp.setStaffName(rs.getString("name"));
			int i = rs.getInt("countBug");
			temp.setBugCount(i);
			temp.setPercent(new java.text.DecimalFormat("0.00").format(1000*i/total*0.1));		
			select.add(temp);
	    }
	    helper.closeAll();
	    return select;
	}
	
	//得到项目组的开发人员
	public Vector getAllDevelopers(int projectID, int total) throws Exception {
		helper = new SqlHelper();
		String s;
		if (projectID == 0) {
			s = "";
		} else {
			s = "and b.project=" + projectID;
		}
		ResultSet rs = helper.executeHelper("select owner,name,count(bugID)as countBug from bug b,staff s " +
				"where b.owner=s.staffID " + s + " group by owner;");
	    Vector<BugCount> select = new Vector<BugCount>();
	    while (rs.next()) {
	    	BugCount temp = new BugCount();
			temp.setStaffID(rs.getInt("owner"));
			temp.setStaffName(rs.getString("name"));
			int i = rs.getInt("countBug");
			temp.setBugCount(i);
			temp.setPercent(new java.text.DecimalFormat("0.00").format(1000*i/total*0.1));		
			select.add(temp);
	    }
	    helper.closeAll();
	    return select;
	}
}