getcategoriesservlet.java

本人课程设计时做的一个用struts框架实现的基于cmmi2的项目管理系统的原型。还有部分功能尚未实现

package com.cmmi2pms.common.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.cmmi2pms.common.comdb.*;
import com.cmmi2pms.sa.department.*;
import com.cmmi2pms.sa.user.*;
import com.cmmi2pms.sa.project.Project;
import	com.cmmi2pms.common.Constants; 
import com.cmmi2pms.pmc.report.*;
import com.cmmi2pms.sa.user.Employee;

import org.apache.log4j.Logger;

public class GetCategoriesServlet extends HttpServlet {
	
	private SqlDB dbf;
	private ResultSet rs;

	private static Logger logger = Logger.getLogger ("GetCategoriesServlet") ;
	
	private void close()
	{
		try
		{
			if(rs!=null) rs.close();
			logger.debug("close the resultset");
			if(dbf!=null) dbf.close();
			logger.debug("close the sqldb");
		}
		catch(SQLException sqle)
		{
			logger.error("Close SqlDB error: "+sqle.getMessage());
		}
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
		/*
		PrintWriter out=response.getWriter();
		out.println("<h1>This is a servlet test.</h1>");
		out.flush();
		*/
		
		try{
			
			//PooledConnectionMgr.getInstance().initialize(30,"192.168.15.150",3306,"CMMI2PM");
			//Get an instance reference to the DbFacade object
			SqlDB dbf = null;
			dbf = new SqlDB();
			ResultSet rs = null,rs1 = null,rs_pmc=null;
			String sql = null;
			
			String type = request.getParameter("type");
			
			System.out.println("type:" + type);
			
			ArrayList theArrayList = new ArrayList();
    			
			if (type.equals("depart")){
				sql = "select departID,departName from department order by departID";
				
				rs=dbf.executeQuery(sql);
    			logger.info("get department list executeQuery:" + sql);	
    			
    			//int rowNum = 0;
    			
    			while ((rs.next())){	
    				Department aDepart = new Department();
    							
    				aDepart.setDepartID(rs.getString("departID"));
    				aDepart.setDepartName(rs.getString("departName"));
    				theArrayList.add(aDepart);				
    			}
    			dbf.close();	
    			
    			Department[] departments = new Department[theArrayList.size()];
    			theArrayList.toArray(departments);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("departments",departments );	
				
						
			} else if (type.equals("employee")){
				
				sql = "select userID,Username,Password,Permission,Name,DepartName,Position,Email,";
				sql += "Phone,Mobile from employee,department where employee.departID=department.departID";
				sql += " and IsAvailable > 0 order by userID";
				
				rs=dbf.executeQuery(sql);
    			logger.info("get employee list executeQuery:" + sql);	
    			
    			while ((rs.next())){	
    				Employee aEmployee = new Employee();
    							
    				aEmployee.setUserID(rs.getString("userID"));
    				aEmployee.setName(rs.getString("Name"));
    				theArrayList.add(aEmployee);				
    			}
    			dbf.close();	
    			
    			Employee[] employees = new Employee[theArrayList.size()];
    			theArrayList.toArray(employees);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("employees",employees );				
						
			} 	else if (type.equals("project")){
				
				sql = "select projectID,projectCode,projectName ";
				sql += " from project ";
				sql += " order by projectID";
				
				rs=dbf.executeQuery(sql);
    			logger.info("get project list executeQuery:" + sql);	
    			
    			
    			while ((rs.next())){	
    				Project aProject = new Project();
    							
    				aProject.setProjectID(rs.getString("projectID"));
    				aProject.setProjectCode(rs.getString("projectCode"));
    				aProject.setProjectName(rs.getString("projectName"));
    				theArrayList.add(aProject);				
    			}
    			dbf.close();	
    			
    			Project[] projects = new Project[theArrayList.size()];
    			theArrayList.toArray(projects);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("projects",projects );				
						
			} 	else if (type.equals("myProject")){
				
				Employee user = new Employee();
				user.setUserID("guest");
				user.setName("未登录");
				if (request.getSession().getAttribute(Constants.USER_KEY)!=null)
					user = (Employee) request.getSession().getAttribute(Constants.USER_KEY);
				
				sql = "select projectID,projectCode,projectName ";
				sql += " from project p, projectMember pm  ";
				sql += " where p.projectID=pm.project and pm.user=" + user.getUserID();
				sql += " order by projectID";
				
				rs=dbf.executeQuery(sql);
    			logger.info("get myproject list executeQuery:" + sql);	
    			
    			
    			while ((rs.next())){	
    				Project aProject = new Project();
    							
    				aProject.setProjectID(rs.getString("projectID"));
    				aProject.setProjectCode(rs.getString("projectCode"));
    				aProject.setProjectName(rs.getString("projectName"));
    				theArrayList.add(aProject);				
    			}
    			dbf.close();	
    			
    			Project[] projects = new Project[theArrayList.size()];
    			theArrayList.toArray(projects);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("myProjects",projects );				
						
			} 	
			//add by liuyang		
			else if (type.equals("notprojectMember"))
			{
				
            		    int projectid =100;
	                	try{
			             Project theProject = new Project();
			             theProject = (Project) request.getSession().getAttribute(Constants.PROJECT_KEY);
			             projectid = Integer.parseInt( theProject.getProjectID() );
		                 }
		                 catch (Exception e){
		                	System.out.println("Exception is throwed from getProjectID:"+e.getMessage());
		                 }
 				sql = "select user from projectMember where project= " + projectid + " and isavailable>0";
				rs1=dbf.executeQuery(sql);
				logger.info("executeQuery:" + sql);	
				String notin=" (0";
				while (rs1.next()){
					notin += "," ;
					notin += rs1.getString("user");
					System.out.println(rs1.getInt("user"));
				}
				notin += ") " ;
				
				sql = "select name,userID from employee";
				sql += " where isavailable>0 and userID NOT IN ";
				sql += notin + " order by name";
				
				rs=dbf.executeQuery(sql);
    			logger.info("get employee list executeQuery:" + sql);	
    			
    			
    			while ((rs.next())){	
    				Employee aEmployee = new Employee();
    							
    				aEmployee.setUserID(rs.getString("userID"));
    				aEmployee.setName(rs.getString("Name"));
    				theArrayList.add(aEmployee);				
    			}
    			dbf.close();	
    			
    			Employee[] employees = new Employee[theArrayList.size()];
    			theArrayList.toArray(employees);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("employees",employees );		
		
			} if (type.equals("projectMember"))
			{
				logger.info("projectMember");
            		    int projectid =100;
	                	try{
			             Project theProject = new Project();
			             theProject = (Project) request.getSession().getAttribute(Constants.PROJECT_KEY);
			             projectid = Integer.parseInt( theProject.getProjectID() );
		                 }
		                 catch (Exception e){
		                	System.out.println("Exception is throwed from getProjectID:"+e.getMessage());
		                 }
 				sql = "select name,userID from projectMember,employee where projectMember.user=employee.userID and project= " + projectid + " and projectMember.isavailable>0";
				rs=dbf.executeQuery(sql);
				logger.info("get employee list executeQuery:" + sql);	
    			    			
    			while ((rs.next())){	
    				Employee aEmployee = new Employee();
    							
    				aEmployee.setUserID(rs.getString("userID"));
    				aEmployee.setName(rs.getString("Name"));
    				theArrayList.add(aEmployee);				
    			}
    			
    			dbf.close();
    			
    			Employee[] employees = new Employee[theArrayList.size()];
    			theArrayList.toArray(employees);	
    			
    			HttpSession session = request.getSession();
				session.setAttribute("employees",employees );		
		
			}
			else if(type.equals("task")){
				logger.info("11111111111111111111");
				Employee theUser = new Employee();
				theUser.setUserID("guest");
				if (request.getSession().getAttribute(Constants.USER_KEY)!=null)
				theUser = (Employee) request.getSession().getAttribute(Constants.USER_KEY);

				Project theProject = new Project();
				theProject.setProjectID("Unknown!");
				if (request.getSession().getAttribute(Constants.PROJECT_KEY)!=null)
				theProject = (Project) request.getSession().getAttribute(Constants.PROJECT_KEY);
				
				sql= "select taskid,taskname ";
				sql+= "from pp_workloadestimate ";
				sql+= "where projectid="+theProject.getProjectID()+" and manager="+theUser.getUserID();
				sql+= " and Isleave=1 and trackid=0";
				
				System.out.println(sql);
				rs=dbf.executeQuery(sql);
				
				while ((rs.next())){	
					SimpleTask simpleTask = new SimpleTask();
    				simpleTask.setTaskid(rs.getString("taskid"));
					simpleTask.setTaskname(rs.getString("taskname"));
					
					//如果该任务没有完成，则加入到链表中
					sql="select taskname from pp_workloadestimate";
					sql+=" where trackid=2 and projectid="+theProject.getProjectID();
					sql+=" and manager="+theUser.getUserID();
					sql+=" and taskid="+simpleTask.getTaskid();
					
					System.out.println(sql);
					rs_pmc=dbf.executeQuery(sql);
					
					int finished=0;
					while(rs_pmc.next()){
						finished=1;
					}
					if(finished==0)	theArrayList.add(simpleTask);					
								
				}
				dbf.close();	
				
				SimpleTask[] tasks = new SimpleTask[theArrayList.size()];
				theArrayList.toArray(tasks);	
				
				HttpSession session = request.getSession();
				session.setAttribute("tasks",tasks);	
				
				
			}//if(type.equals("task"))
		
		}
		catch (java.sql.SQLException ex)   	
		{
			logger.error("SQLException in GetCategoriesServlet:" + ex.getMessage());
		}
		catch (Exception e)   	
		{
			logger.error("Exception in GetCategoriesServlet:" + e.getMessage());
		}
		finally
		{
			close();
		}
		
		
		
		
	}
}