projectbean.java

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

package bugtrail.bean;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import bugtrail.basic.Project;
import bugtrail.basic.Staff;
import bugtrail.util.CharsetProcess;
import bugtrail.util.SqlHelper;

/**
 * Creation date: 12-25-2006
 * 
 * @author Carol-W
 *
 */
public class ProjectBean {

	private SqlHelper helper;

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

	// add a new project
	public String addProject(Project pro) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("insert into project(name,manager,state,description,establishTime) values ('" + 
				CharsetProcess.U2C(pro.getName()) + "'," + 
				pro.getManager() + ",'" +
				pro.getState() + "','" + 
				CharsetProcess.U2C(pro.getDescription()) + "','" + 
				(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(pro.getEstablishTime()) + "');");
		ResultSet rs = helper.executeHelper("select projectID from project where establishTime = '" 
				+ (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(pro.getEstablishTime())
				+ "' and manager = " + pro.getManager() + ";");
		String ID = null;
		while(rs.next()){
			ID = rs.getString("projectID");
		}
		helper.closeAll();
		return ID;
	}
	
	public void closeProject(int projectID) throws Exception {
		helper = new SqlHelper();
		helper.updateHelper("update project set state='closed' " 
				+",closedTime='"+(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(new Date())				
				+"' where projectID='"+projectID+"'");
		System.out.println("update project set state='closed' " 
				+",closedTime='"+(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(new Date())				
				+"' where projectID='"+projectID+"'");
		helper.closeAll();
	}

	public Vector getAllProName() throws Exception {
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select name from project");
		Vector<String> proNameList = new Vector<String>();
		while(rs.next()) {
			proNameList.add(rs.getString("name"));
		}
		helper.closeAll();
		return proNameList;
	}

	//返回project表里的总条目数
	public int getAvilableCount() throws Exception{
		int num = 0;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select count(*) from project");
		while(rs.next())
			num = rs.getInt(1);
		helper.closeAll();
		return num;
	}

	//分页显示,得到project的信息
	public Vector getPartProject(Integer pageNum,Integer rows)throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from project",pageNum,rows);
		Vector<Project> projList = new Vector<Project>();
		while(rs.next()){
			Project temp = new Project();
			temp.setProjectID(rs.getInt("projectID"));
			temp.setName(rs.getString("name"));
			temp.setDescription("description");
			temp.setManager(rs.getInt("manager"));
			temp.setState(rs.getString("state"));
			temp.setEstablishTime(rs.getDate("establishTime"));
			projList.add(temp);
		}
		helper.closeAll();
		return projList;
	}

	//得到项目经理姓名
	public String getManagerName(int projectID)throws Exception{
		String name = null;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select name from staff where staffID = " +
				"(select manager from project where projectID='"+projectID+"')");
		while(rs.next())
			name = rs.getString(1);
		helper.closeAll();
		return name;
	}

	//得到项目名称
	public String getProjectName(int projectID)throws Exception{
		String name = null;
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select name from project where projectID = '" 
				+projectID+"'");
		while(rs.next())
			name = rs.getString(1);
		helper.closeAll();
		return name;
	}
	
	public Project getProject(int projectID) throws Exception{
		Project project = new Project();
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from project where projectID='"
				+projectID+"'");
		while(rs.next()){
			project.setProjectID(rs.getInt("projectID"));
			project.setName(rs.getString("name"));
			project.setManager(rs.getInt("manager"));
			project.setState(rs.getString("state"));
			project.setDescription(rs.getString("description"));
			project.setEstablishTime(rs.getDate("establishTime"));
			project.setClosedTime(rs.getDate("closedTime"));
		}
		helper.closeAll();
		return project;
	}
	
	public Vector getAllProjects() throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from project");
		Vector<Project> projList = new Vector<Project>();
		while(rs.next()){
			Project temp = new Project();
			temp.setProjectID(rs.getInt("projectID"));
			temp.setName(rs.getString("name"));
			temp.setDescription("description");
			temp.setManager(rs.getInt("manager"));
			temp.setState(rs.getString("state"));
			temp.setEstablishTime(rs.getDate("establishTime"));
			projList.add(temp);
		}
		helper.closeAll();
		return projList;
	}
	
	public Vector getMyProjects(int staffID) throws Exception{
		helper = new SqlHelper();
		ResultSet rs = helper.executeHelper("select * from project where projectID in " +
				"(select groupID from `group` where staffID='"+staffID+"')");
		Vector<Project> projList = new Vector<Project>();
		while(rs.next()){
			Project temp = new Project();
			temp.setProjectID(rs.getInt("projectID"));
			temp.setName(rs.getString("name"));
			temp.setDescription("description");
			temp.setManager(rs.getInt("manager"));
			temp.setState(rs.getString("state"));
			temp.setEstablishTime(rs.getDate("establishTime"));
			projList.add(temp);
		}
		helper.closeAll();
		return projList;
	}
}