planinfo.java

该项目采用Struts框架

package com.cattsoft.plan.daoImpl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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.plan.dao.PlanInfoServer;
import com.cattsoft.vo.Plan;
import com.cattsoft.vo.Task;

public class PlanInfo implements PlanInfoServer{
	private static final String SQL_QUERY_TASK_BY_EID = "select * from task where e_id = ?"; 
	private static final String SQL_QUERY_TASK_BY_TID = "select * from task where t_id = ?";
	private static final String SQL_UPDATE_TASK_BY_PID = "update task set t_state = '实施中' where t_id = ?";	
	private static final String SQL_ADD_PLAN = "insert into plan(t_id,e_id,p_name,p_describe,p_stime,p_etime,p_state,f_state,t_name) values(?,?,?,?,?,?,?,?,?)";
	private static final String SQL_QUERY_PlAN_BY_TASK_ID = "select p_id,t_id,e_id,p_name,p_describe,p_stime,p_etime,p_state,f_state,f_info,t_name from plan where t_id = ?";
	private static final String SQL_DEL_PLAN = "delete from plan where p_id = ?";
	private static final String SQL_UPDATE_TASK = "update task set t_state = '未实施' where t_id = ?";
	private static final String SQL_QUERY_PLAN_BY_PLAN_ID = "select p_id,t_id,e_id,p_name,p_describe,p_stime,p_etime,p_state,f_state,f_info,t_name from plan where p_id = ?";
	private static final String SQL_UPDATE_PLAN = "update plan set p_state = ?,f_state = ?,f_info = ?,file_name = ?,file_type = ? where p_id = ?";
	private static final String SQL_QUERY_TASK_NAME = "select t_name from task where t_id = ?";
	
	DBUtil db = new DBUtil();	  //实例化一个数据库连接对象
	PreparedStatement pstmt=null; //初始化数据库操作语句对象
	ResultSet rs=null;			  //初始化结果集对象	
	
	//根据员工id查询任务信息
	public List<Task> queryTask(String emId) throws SQLException{
		List<Task> list=null;
		pstmt=db.getConnection().prepareStatement(SQL_QUERY_TASK_BY_EID);
		pstmt.setString(1,emId);
		rs=pstmt.executeQuery();
		list=new ArrayList<Task>();
		while(rs.next()){
			Task mt = new Task();
			mt.setT_id(rs.getInt(1));
			mt.setT_name(rs.getString(2));
			mt.setT_describe(rs.getString(3));
			mt.setT_stime(rs.getString(4));
			mt.setT_etime(rs.getString(5));
			mt.setM_id(rs.getString(6));
			mt.setE_id(rs.getString(7));
			mt.setT_state(rs.getString(8));
			list.add(mt);
		}
		
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
	
	//根据任务id查询任务详细信息
	public List<Task> queryTask(int tId) throws SQLException{
		List<Task> list=null;
		pstmt=db.getConnection().prepareStatement(SQL_QUERY_TASK_BY_TID);
		pstmt.setInt(1,tId);
		rs=pstmt.executeQuery();
		list=new ArrayList<Task>();
		while(rs.next()){
			Task mt = new Task();
			mt.setT_id(rs.getInt(1));
			mt.setT_name(rs.getString(2));
			mt.setT_describe(rs.getString(3));
			mt.setT_stime(rs.getString(4));
			mt.setT_etime(rs.getString(5));
			mt.setM_id(rs.getString(6));
			mt.setE_id(rs.getString(7));
			mt.setT_state(rs.getString(8));
			list.add(mt);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
	//根据任务id修改任务状态未'实施中'
	public void update(int tId) throws SQLException{
		pstmt=db.getConnection().prepareStatement(SQL_UPDATE_TASK_BY_PID);
		pstmt.setInt(1,tId);
		pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
	}
	
	//根据任务id查询其名字
	public String selectBy(int tId) throws ClassNotFoundException, SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_TASK_NAME);
		pstmt.setInt(1,tId);
		rs = pstmt.executeQuery();
		rs.next();
		Task mt = new Task();
		mt.setT_name(rs.getString(1));
		return mt.getT_name();
	}
	
	//新增计划
	public int addPlan(Plan mp) throws ClassNotFoundException, SQLException {
		pstmt=db.getConnection().prepareStatement(SQL_ADD_PLAN);
		pstmt.setInt(1,mp.getT_id());		
		pstmt.setString(2,mp.getE_id());
		pstmt.setString(3,mp.getP_name());
		pstmt.setString(4,mp.getP_describe());
		pstmt.setString(5,mp.getP_stime());
		pstmt.setString(6,mp.getP_etime());
		pstmt.setString(7,mp.getP_state());
		pstmt.setString(8,mp.getF_state());
		pstmt.setString(9,mp.getT_name());
		int result = pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return result;
	}
	
	//根据任务id查询计划
	public List<Plan> query(int tId) throws ClassNotFoundException,SQLException {
		List<Plan> list = null;
		pstmt=db.getConnection().prepareStatement(SQL_QUERY_PlAN_BY_TASK_ID);
		pstmt.setInt(1,tId);
		rs=pstmt.executeQuery();
		list = new ArrayList<Plan>();
		while(rs.next()){
			Plan mp = new Plan();
			mp.setP_id(rs.getInt(1));
			mp.setT_id(rs.getInt(2));
			mp.setE_id(rs.getString(3));
			mp.setP_name(rs.getString(4));
			mp.setP_describe(rs.getString(5));
			mp.setP_stime(rs.getString(6));
			mp.setP_etime(rs.getString(7));
			mp.setP_state(rs.getString(8));
			mp.setF_state(rs.getString(9));
			mp.setF_info(rs.getString(10));
			mp.setT_name(rs.getString(11));
		    list.add(mp);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
	
	//根据计划id去删除对应的计划
	public void delPlan(int pId) throws ClassNotFoundException, SQLException {
		pstmt = db.getConnection().prepareStatement(SQL_DEL_PLAN);
		pstmt.setInt(1, pId);
		pstmt.executeUpdate();
		pstmt.close();
		db.connectionClose(db.getConnection());
	}
	
	//根据计划id去查询对应计划的详细信息
	public List<Plan> queryBy(int pId) throws ClassNotFoundException, SQLException {
		List<Plan> list = null;
		pstmt = db.getConnection().prepareStatement(SQL_QUERY_PLAN_BY_PLAN_ID);
		pstmt.setInt(1,pId);
		rs=pstmt.executeQuery();
		list = new ArrayList<Plan>();
		while(rs.next()){
			Plan mp = new Plan();
			mp.setP_id(rs.getInt(1));
			mp.setT_id(rs.getInt(2));
			mp.setE_id(rs.getString(3));
			mp.setP_name(rs.getString(4));
			mp.setP_describe(rs.getString(5));
			mp.setP_stime(rs.getString(6));
			mp.setP_etime(rs.getString(7));
			mp.setP_state(rs.getString(8));
			mp.setF_state(rs.getString(9));
			mp.setF_info(rs.getString(10));
			mp.setT_name(rs.getString(11));
			list.add(mp);
		}
		rs.close();
		pstmt.close();
		db.connectionClose(db.getConnection());
		return list;
	}
	
	//更新任务状态为'未实施'
	public void updateTask(int tId) throws ClassNotFoundException, SQLException {
			pstmt=db.getConnection().prepareStatement(SQL_UPDATE_TASK);
			pstmt.setInt(1,tId);
			pstmt.executeUpdate();
			pstmt.close();
			db.connectionClose(db.getConnection());
		}
	
	//根据计划id和计划反馈信息更新计划表数据
	public void updatePlan(int pId, String pState,String fState,String fInfo,String fileName,String fileType) 
			throws ClassNotFoundException,SQLException {
				pstmt = db.getConnection().prepareStatement(SQL_UPDATE_PLAN);
				pstmt.setString(1,pState);
				pstmt.setString(2,fState);
				pstmt.setString(3,fInfo);
				pstmt.setString(4, fileName);
				pstmt.setString(5,fileType);
				pstmt.setInt(6, pId);
				pstmt.executeUpdate();
			pstmt.close();
			db.connectionClose(db.getConnection());
	}

	//模糊查询计划表	
	public List<Plan> queryLike(String e_id,String pName,int tId,String pStime,String pStime2,String pEtime,String pEtime2,String fState)
			throws ClassNotFoundException, SQLException {
		String sql = "select * from plan where e_id = ?";
		if(!pName.equals("")){
			sql+=" and p_name like '%" + pName + "%'";
		}
		if(tId!=0){
			sql+=" and t_id = '" + tId + "'";		
		}
		if(!pStime.equals("")){
			sql+=" and p_stime >='" + pStime + "'";		
		}
		if(!pStime2.equals("")){			
			sql+=" and p_stime <='" + pStime2 +"'";			
		}
		if(!pEtime.equals("")){			
			sql+=" and p_etime >='" + pEtime + "'";		
		}
		if(!pEtime2.equals("")){			
			sql+=" and p_etime <='" + pEtime2 +"'";		
		}
		if(!fState.equals("")){			
			sql+=" and f_state like '%" + fState + "%'";		
		}	
		List<Plan> list = null;
			pstmt = db.getConnection().prepareStatement(sql);
			pstmt.setString(1,e_id);
			rs = pstmt.executeQuery();
			list = new ArrayList<Plan>();
			while(rs.next()){
				Plan mp = new Plan();
				mp.setP_id(rs.getInt(1));
				mp.setT_id(rs.getInt(2));
				mp.setE_id(rs.getString(3));
				mp.setP_name(rs.getString(4));
				mp.setP_describe(rs.getString(5));
				mp.setP_stime(rs.getString(6));
				mp.setP_etime(rs.getString(7));
				mp.setP_state(rs.getString(8));
				mp.setF_state(rs.getString(9));
				mp.setF_info(rs.getString(10));
				mp.setT_name(rs.getString(11));
				list.add(mp);
				 
			}
			rs.close();
			pstmt.close();
			db.connectionClose(db.getConnection());
		return list;
	}
}