taskinfo.java

该项目采用Struts框架

package com.cattsoft.task.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cattsoft.DB.DBUtil;
import com.cattsoft.task.dao.TaskInfoServer;
import com.cattsoft.vo.Plan;
import com.cattsoft.vo.Task;
import com.cattsoft.vo.User;

public class TaskInfo implements TaskInfoServer {
	private static final String SQL_QUERY_BY_USERID = "select * from user where manager_id = ?"; 
	private static final String SQL_INSERT_TASK = "insert into task(t_name,t_describe,t_stime,t_etime,m_id,e_id,t_state) values(?,?,?,?,?,?,?)"; 
	private static final String SQL_QUERY_ALL_TASK = "select * from task where m_id = ?";
	private static final String SQL_QUERY_NAME_BY = "select name from user where e_id = ?";
	private static final String SQL_DELETE_TASK = "delete  from task where t_id = ?";
	private static final String SQL_QUERY_TASK_BY_TID = "select * from task where t_id = ?";
	private static final String SQL_QUERY_TASK_UNDO = "select * from task where m_id = ? and t_state = '未实施'";
	private static final String SQL_UPDATE_TASK = "update task set t_name = ?,t_describe = ?,t_stime = ?,t_etime = ? where t_id = ?";
	private static final String SQL_QUERY_EMP_BY = "select * from user where user_id = ?"; 
	private static final String SQL_UPDATE_TASK_STATE = "update task set t_state = ? where t_id = ?"; 
	private static final String SQL_QUERY_PLAN_BY = "select p_id,t_id,p_name,p_state,f_state,p_stime,p_etime from plan where t_id = ?";
	private static final String SQL_QUERY_PLAN = "select p_id,t_id,p_name,p_describe,p_stime,p_etime,p_state,f_info,t_name from plan where p_id = ?";
	private static final String SQL_QUERY_TASK_InForce = "select * from task where m_id = ? and t_state = '实施中'";
	private static final String SQL_QUERY_TASK = "select t_id,t_name,t_describe,t_stime,t_etime,e_id,t_state from task where t_id = ?";
	private static final String SQL_QUERY_PLAN_ALL = "select p_id,t_id,p_name,p_describe,p_stime,p_etime,p_state,f_state,f_info,t_name,file_name,file_type from plan where t_id = ?";
	
	DBUtil db = new DBUtil(); // 实例化一个数据库连接对象
	PreparedStatement pstmt = null; // 初始化数据库操作语句对象
	ResultSet rs = null; // 初始化结果集对象
	
	//根据主管id查询其下的员工信息
	public List<User> queryEmployeeBy(int user_id)
			throws ClassNotFoundException, SQLException {
		List<User> list = new ArrayList<User>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_BY_USERID);
		pstmt.setInt(1,user_id);
		rs = pstmt.executeQuery();		
		while(rs.next()){
			User user = new User();
			user.setUser_id(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setBirthday(rs.getString(4));
			user.setPosition(rs.getString(5));
			user.setHiredate(rs.getString(6));
			user.setExperience(rs.getString(7));
			user.setMajor(rs.getString(8));
			user.setDegree(rs.getString(9));
			user.setRole_id(rs.getString(10));
			user.setSex(rs.getString(11));
			user.setManager_name(rs.getString(12));
			user.setName(rs.getString(13));
			user.setManager_id(rs.getInt(14));
			list.add(user);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//新建任务
	public int newTask(Task task) throws ClassNotFoundException, SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_INSERT_TASK);
		pstmt.setString(1,task.getT_name());
		pstmt.setString(2,task.getT_describe());
		pstmt.setString(3,task.getT_stime());
		pstmt.setString(4,task.getT_etime());		
		pstmt.setString(5,task.getM_id());
		pstmt.setString(6,task.getE_id());
		pstmt.setString(7,task.getT_state());
		int i = pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return i;
	}

	//根据主管id查询所有任务信息
	public List<Task> queryAllTask(String m_id) throws ClassNotFoundException,
			SQLException {
		List<Task> list = new ArrayList<Task>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_ALL_TASK);
		pstmt.setString(1,m_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Task task = new Task();
			task.setT_id(rs.getInt(1));
			task.setT_name(rs.getString(2));
			task.setT_describe(rs.getString(3));
			task.setT_stime(rs.getString(4));
			task.setT_etime(rs.getString(5));			
			task.setM_id(rs.getString(6));
			task.setE_id(rs.getString(7));
			task.setT_state(rs.getString(8));
			list.add(task);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//根据员工用户名查询其姓名
	public String queryNameBy(String empId) throws ClassNotFoundException,
			SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_NAME_BY);
		pstmt.setString(1,empId);
		rs = pstmt.executeQuery();
		User user = new User();
		while(rs.next()){
			user.setName(rs.getString(1));			
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return user.getName();
	}

	//删除任务
	public void deleteTask(int t_id) throws ClassNotFoundException,
			SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_DELETE_TASK);
		pstmt.setInt(1,t_id);
		pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
	}

	//根据任务id查询对应任务的详细信息
	public List<Task> queryTaskBy(int t_id) throws ClassNotFoundException,
			SQLException {
		List<Task> list = new ArrayList<Task>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_TASK_BY_TID);
		pstmt.setInt(1,t_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Task task = new Task();
			task.setT_id(rs.getInt(1));
			task.setT_name(rs.getString(2));
			task.setT_describe(rs.getString(3));
			task.setT_stime(rs.getString(4));
			task.setT_etime(rs.getString(5));			
			task.setM_id(rs.getString(6));
			task.setE_id(rs.getString(7));
			task.setT_state(rs.getString(8));
			list.add(task);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//查询未实施状态的所有任务信息
	public List<Task> queryTaskUndoBy(String empId)
			throws ClassNotFoundException, SQLException {
		List<Task> list = new ArrayList<Task>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_TASK_UNDO);
		pstmt.setString(1,empId);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Task task = new Task();
			task.setT_id(rs.getInt(1));
			task.setT_name(rs.getString(2));
			task.setT_describe(rs.getString(3));
			task.setT_stime(rs.getString(4));
			task.setT_etime(rs.getString(5));			
			task.setM_id(rs.getString(6));
			task.setE_id(rs.getString(7));
			task.setT_state(rs.getString(8));
			list.add(task);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//更新task表
	public void updateTask(Task task) throws ClassNotFoundException,
			SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_UPDATE_TASK);
		pstmt.setString(1,task.getT_name());
		pstmt.setString(2,task.getT_describe());
		pstmt.setString(3,task.getT_stime());
		pstmt.setString(4,task.getT_etime());
		pstmt.setInt(5,task.getT_id());
		pstmt.executeUpdate();
		pstmt.close();	
		db.connectionClose(db.getConnection());
	}

	//根据员工id查询其详细信息
	public List<User> queryEmpInfoBy(int user_id)
			throws ClassNotFoundException, SQLException {
		List<User> list = new ArrayList<User>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_EMP_BY);
		pstmt.setInt(1,user_id);
		rs = pstmt.executeQuery();		
		while(rs.next()){
			User user = new User();
			user.setUser_id(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setBirthday(rs.getString(4));
			user.setPosition(rs.getString(5));
			user.setHiredate(rs.getString(6));
			user.setExperience(rs.getString(7));
			user.setMajor(rs.getString(8));
			user.setDegree(rs.getString(9));
			user.setRole_id(rs.getString(10));
			user.setSex(rs.getString(11));
			user.setManager_name(rs.getString(12));
			user.setName(rs.getString(13));
			user.setManager_id(rs.getInt(14));
			list.add(user);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//根据反馈信息更新任务状态
	public void updateTask(int t_id, String t_state)
			throws ClassNotFoundException, SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_UPDATE_TASK_STATE);
		pstmt.setString(1,t_state);
		pstmt.setInt(2,t_id);
		pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
	}

	//根据任务id查询其对应的计划信息
	public List<Plan> queryPlanBy(int t_id) throws ClassNotFoundException,
			SQLException {
		List<Plan> list = new ArrayList<Plan>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_PLAN_BY);
		pstmt.setInt(1,t_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Plan plan = new Plan();
			plan.setP_id(rs.getInt(1));
			plan.setT_id(rs.getInt(2));
			plan.setP_name(rs.getString(3));
			plan.setP_state(rs.getString(4));
			plan.setF_state(rs.getString(5));
			plan.setP_stime(rs.getString(6));
			plan.setP_etime(rs.getString(7));
			list.add(plan);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
	
	//根据计划id查询其对应的计划详细信息
	public List<Plan> queryPlan(int p_id) throws ClassNotFoundException,
			SQLException {
		List<Plan> list = new ArrayList<Plan>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_PLAN);
		pstmt.setInt(1,p_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Plan plan = new Plan();
			plan.setP_id(rs.getInt(1));
			plan.setT_id(rs.getInt(2));
			plan.setP_name(rs.getString(3));
			plan.setP_describe(rs.getString(4));
			plan.setP_stime(rs.getString(5));
			plan.setP_etime(rs.getString(6));
			plan.setP_state(rs.getString(7));
			plan.setF_info(rs.getString(8));
			plan.setT_name(rs.getString(9));
			list.add(plan);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//查询实施中状态的所有任务信息
	public List<Task> queryInForceTask(String empId)
			throws ClassNotFoundException, SQLException {
		List<Task> list = new ArrayList<Task>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_TASK_InForce);
		pstmt.setString(1,empId);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Task task = new Task();
			task.setT_id(rs.getInt(1));
			task.setT_name(rs.getString(2));
			task.setT_describe(rs.getString(3));
			task.setT_stime(rs.getString(4));
			task.setT_etime(rs.getString(5));			
			task.setM_id(rs.getString(6));
			task.setE_id(rs.getString(7));
			task.setT_state(rs.getString(8));
			list.add(task);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//根据实施中的任务id查询其详细任务信息
	public List<Task> queryTask(int t_id) throws ClassNotFoundException,
	SQLException {	
		List<Task> list = new ArrayList<Task>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_TASK);
		pstmt.setInt(1,t_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Task task = new Task();
			task.setT_id(rs.getInt(1));
			task.setT_name(rs.getString(2));
			task.setT_describe(rs.getString(3));
			task.setT_stime(rs.getString(4));
			task.setT_etime(rs.getString(5));			
			task.setE_id(rs.getString(6));
			task.setT_state(rs.getString(7));
			list.add(task);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}

	//根据实施中的任务id查询其详细计划信息
	public List<Plan> queryPlanAll(int t_id) throws ClassNotFoundException,
	SQLException {
		List<Plan> list = new ArrayList<Plan>();
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_PLAN_ALL);
		pstmt.setInt(1,t_id);
		rs = pstmt.executeQuery();
		while(rs.next()){
			Plan plan = new Plan();
			plan.setP_id(rs.getInt(1));
			plan.setT_id(rs.getInt(2));
			plan.setP_name(rs.getString(3));
			plan.setP_describe(rs.getString(4));
			plan.setP_stime(rs.getString(5));
			plan.setP_etime(rs.getString(6));
			plan.setP_state(rs.getString(7));
			plan.setF_state(rs.getString(8));
			plan.setF_info(rs.getString(9));
			plan.setT_name(rs.getString(10));
			plan.setFile_name(rs.getString(11));
			plan.setFile_type(rs.getString(12));
			list.add(plan);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
}