tj_query.java

1.员工添加 2.人事调动 3.在职人员管理 4.部门（分厂）管理

package pmsys.wyj;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;
import org.apache.struts.actions.DispatchAction;

import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import java.io.*;
import jxl.*; 
import jxl.write.*; 

import pmsys.*;
import pmsys.wyj.*;

public class TJ_Query extends DispatchAction {
	/**
	 * ----生成部门列表
	 */
	public ActionForward querylist(ActionMapping mapping,
									ActionForm form,
									HttpServletRequest request,
									HttpServletResponse response) {
		try{
			DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
			String sql1 = "SELECT * FROM zhichengs";
			TJ_Lists tl1 = new TJ_Lists();
			ArrayList dlist1 = tl1.lists(ds,sql1);
			request.setAttribute("zc_list",dlist1);
			
			String sql2 = "SELECT * FROM xuelis";
			TJ_Lists tl2 = new TJ_Lists();
			ArrayList dlist2 = tl2.lists(ds,sql2);
			request.setAttribute("xl_list",dlist2);
			
			String sql3 = "SELECT * FROM zhiwus";
			TJ_Lists tl3 = new TJ_Lists();
			ArrayList dlist3 = tl3.lists(ds,sql3);
			request.setAttribute("zw_list",dlist3);
			
			String sql4 = "SELECT * FROM states";
			TJ_Lists tl4 = new TJ_Lists();
			ArrayList dlist4 = tl4.lists(ds,sql4);
			request.setAttribute("st_list",dlist4);
			
			String sql5 = "SELECT * FROM nations";
			TJ_Lists tl5 = new TJ_Lists();
			ArrayList dlist5 = tl5.lists(ds,sql5);
			request.setAttribute("na_list",dlist5);
			
			String sql6 = "SELECT * FROM departments";
			TJ_Lists tl6 = new TJ_Lists();
			ArrayList dlist6 = tl6.lists(ds,sql6);
			request.setAttribute("dept_list",dlist6);
		} catch(Exception e1) {
			e1.printStackTrace();
		}	
		return mapping.findForward("tj_query");
	}
	/**
	 * ----组合查找
	 */
	public ActionForward lookup(ActionMapping mapping,
									ActionForm form,
									HttpServletRequest request,
									HttpServletResponse response) {
		ArrayList dlist1 = new ArrayList();
		DynaActionForm dyform = (DynaActionForm) form;
		String zhichengs = (String) dyform.get("zhicheng");
		//String sex = (String) dyform.get("sex");
		String xuelis = (String) dyform.get("xueli");
		String zhiwus = (String) dyform.get("zhiwu");
		String states = (String) dyform.get("state");
		String nations = (String) dyform.get("nation");
		String depts = (String) dyform.get("dept");
		String age1 = (String) dyform.get("age1");
		String age2 = (String) dyform.get("age2");
		Connection conn = null; 
		Statement stmt = null;
		String sql = "";	
		try{
			sql = "SELECT * FROM employees e,emp_zhich ez,emp_zhiwu zw WHERE e.emp_id=zw.emp_id AND e.emp_id=ez.emp_id ";
			if(zhichengs.equals("#") && xuelis.equals("#") && zhiwus.equals("#") && states.equals("#") && nations.equals("#") && depts.equals("#") && age1.trim().equals("") && age2.trim().equals("")){
				sql = "SELECT * FROM employees";
			}else {
				if(!zhichengs.equals("#")){
					int zhicheng = Integer.parseInt(zhichengs);
					sql = sql + " AND ez.zhicheng_id=" + zhicheng + "";
				}
				//if(!sex.equals("#")){       && sex.equals("#")
				//	sql = sql + " AND e.emp_sex='" + sex + "' ";
				//}
				if(!xuelis.equals("#")){
					int xueli = Integer.parseInt(xuelis);
					sql = sql + " AND e.xue_xue_id=" + xueli + "";
				}
				if(!zhiwus.equals("#")){
					int zhiwu = Integer.parseInt(zhiwus);
					sql = sql + " AND e.emp_id=zw.emp_id AND zw.zhiwu_id=" + zhiwu + "";
				}
				if(!states.equals("#")){
					int state = Integer.parseInt(states);
					sql = sql + " AND e.states_id=" + state + "";
				}
				if(!nations.equals("#")){
					int nation = Integer.parseInt(nations);
					sql = sql + " AND e.NAT_nation_id=" + nation + "";
				}
				if(!depts.equals("#")){
					int dept = Integer.parseInt(depts);
					sql = sql + " AND e.dep_dep_id=" + dept + "";
				}
				String age11 = "1";
				if(age1.trim().equals("")){
					age11 = "1";
				}else{
					age11 = age1;
				}
				String age12 = "100";
				if(age2.trim().equals("")){
					age12 = "100";
				}else{
					age12 = age2;
				}
				sql = sql + " AND to_char(emp_birth,'yyyy') >= (to_char(sysdate,'yyyy')-'"+age12+"') AND to_char(emp_birth,'yyyy') <= (to_char(sysdate,'yyyy')-'" + age11 + "')";
			}
			
			DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
			conn = ds.getConnection();
			Statement stmts = conn.createStatement();
			ResultSet rs = stmts.executeQuery(sql);
			while(rs.next()){
				UserForm dept1 = new UserForm();
				//dept1.setDeptno(rs.getInt("emp_id")+"");
				dept1.setDname(rs.getString("emp_name"));
				dept1.setSex(rs.getString("emp_sex"));
				dept1.setDate(rs.getString("emp_birth"));
				dept1.setXueli(rs.getString("emp_shenfen"));
				dept1.setState(rs.getString("emp_tuizi"));
				dlist1.add(dept1);
			}	
			request.setAttribute("emp_list1",dlist1);
		} catch(Exception e1) {
			e1.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}	
		return mapping.findForward("tj_query_list");
	}
}