📄 tj_type.java
字号:
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_Type extends DispatchAction {
/**
* ----生成部门列表
*/
public ActionForward zhichenglist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_List tl = new TJ_List();
ArrayList dlist = tl.list(ds);
request.setAttribute("deptlist",dlist);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_zhicheng");
}
/**
* 按职称统计查询
*/
public ActionForward zhicheng(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
ArrayList dlist1[] = new ArrayList[8];
for (int i = 0; i < 8; i++) {
dlist1[i] = new ArrayList();
}
DynaActionForm dyform = (DynaActionForm) form;
String tjdept = (String) dyform.get("tjdept");
Connection conn = null;
Statement stmt = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
//查找数据库中全部职称类型的记录
String sql = "SELECT * FROM zhichengs";
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int k = 0;
int n = 0;
int h = 0;
String n1 = "";
for(int i = 0;rs.next(); i++){
int zhicheng_id = rs.getInt("zhicheng_id");
n = 0;
h = 0;
n1 = "";
//查找数据库中匹配职称类型的记录
String sql2 = "SELECT * FROM employees e,emp_zhich ez WHERE e.emp_id=ez.emp_id AND ez.zhicheng_id=? AND e.dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,zhicheng_id);
pstmt.setString(2,tjdept);
ResultSet rs2 = pstmt.executeQuery();
while(rs2.next()){
UserForm dept1 = new UserForm();
dept1.setDeptno(rs2.getInt("emp_id")+"");
dept1.setDname(rs2.getString("emp_name"));
dlist1[i].add(dept1);
n++;
k++;
}
n1 = n + "";
ArrayList dlist2 = new ArrayList();
UserForm dept3 = new UserForm();
dept3.setDeptno(n1);
dlist2.add(dept3);
request.setAttribute("j" + i,dlist2);
String dept = "dept" + i;
request.setAttribute(dept,dlist1[i]);
//查找全厂该职称总数
String sql4 = "SELECT * FROM employees e,emp_zhich ez WHERE e.emp_id=ez.emp_id AND ez.zhicheng_id=?";
PreparedStatement pstmt4 = conn.prepareStatement(sql4);
pstmt4.setInt(1,zhicheng_id);
ResultSet rs4 = pstmt4.executeQuery();
while(rs4.next()){
h++;
}
request.setAttribute("count" + i,h + "");
}
//查找当前部门名称
String sql3 = "SELECT * FROM departments where dep_id=?";
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
pstmt3.setString(1,tjdept);
ResultSet rs3 = pstmt3.executeQuery();
rs3.next();
request.setAttribute("dept",rs3.getString("dep_name"));
request.setAttribute("dep_id",rs3.getString("dep_id"));
//某部门总人数
request.setAttribute("dept_count",k + "");
} catch(Exception e1) {
e1.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return mapping.findForward("tj_zhicheng_list");
}
/**
* 按名字查找详细信息
*/
public ActionForward emplist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String emp_id = (String)request.getParameter("emp_id");
ArrayList dlist1 = new ArrayList();
Connection conn = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
//查找数据库中全部职称类型的记录
String sql = "SELECT * FROM employees e,xuelis x,states s where e.states_id=s.states_id AND e.XUE_xue_id=x.xue_id AND emp_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,emp_id);
ResultSet rs = pstmt.executeQuery();
if(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("xue_name"));
dept1.setState(rs.getString("states_name"));
dlist1.add(dept1);
}
} catch(Exception e1) {
e1.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
request.setAttribute("emp_list",dlist1);
return mapping.findForward("tj_emp_name_list");
}
/**
* 导出数据--职称
*/
public ActionForward report_date(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String tjdepts = request.getParameter("dept");
int tjdept = Integer.parseInt(tjdepts);
Connection conn = null;
WritableWorkbook book = null;
WritableSheet sheet = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
String sql = "SELECT * FROM zhichengs";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//建立Excel表
book= Workbook.createWorkbook(new File("C:/zhicheng_" + tjdepts + ".xls"));
sheet=book.createSheet("zhicheng",0); //建立工作空间
int j = 0;
int k = 0;
while(rs.next()){
//添加表头，每一列的开始添加
Label label = new Label(j,0,rs.getString(2));
j++;
sheet.addCell(label);
int zhicheng_id = rs.getInt("zhicheng_id");
String sql2 = "SELECT * FROM employees e,emp_zhich ez WHERE e.emp_id=ez.emp_id AND ez.zhicheng_id=? AND e.dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,zhicheng_id);
pstmt.setInt(2,tjdept);
ResultSet rs2 = pstmt.executeQuery();
int i = 1;
try {
//找出存在的内容添加进Excel表工作区中
while(rs2.next()){
Label label2 = new Label(k,i,rs2.getString("emp_name"));
sheet.addCell(label2);
i++;
}
}
catch(Exception e) {
e.printStackTrace();
}
k++;
}
}
catch(Exception e) {
e.printStackTrace();
} finally {
try {
book.write();
book.close();
}
catch(Exception e) {
e.printStackTrace();
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
request.setAttribute("zhicheng_ok","report data success!");//导出成功设置属性
return mapping.findForward("tj_zhicheng_list");
}
//------------------------------------------------------------------------
/**
* 性别----生成部门列表
*/
public ActionForward xingbielist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_List tl = new TJ_List();
ArrayList dlist = tl.list(ds);
request.setAttribute("deptlist",dlist);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_xingbie");
}
/**
* 按性别统计查询
*/
public ActionForward xingbie(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
ArrayList dlist1[] = new ArrayList[2];
for (int i = 0; i < 2; i++) {
dlist1[i] = new ArrayList();
}
DynaActionForm dyform = (DynaActionForm) form;
String tjdept = (String) dyform.get("tjdept");
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_Type_query ttq = new TJ_Type_query();
dlist1 = ttq.type_query(ds,request,tjdept);
request.setAttribute("dept0",dlist1[0]);
request.setAttribute("dept1",dlist1[1]);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_xingbie_list");
}
/**
* 导出数据--性别
*/
public ActionForward report_date_sex(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String tjdepts = request.getParameter("dept");
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
String sql = "SELECT * FROM employees e WHERE e.emp_sex=? AND e.dep_dep_id=?";
Report_data_list rdl = new Report_data_list();
String succ = rdl.type_query(ds,request, sql,tjdepts);
request.setAttribute("xingbie_ok",succ);//导出成功设置属性
return mapping.findForward("tj_xingbie_list");
}
//------------------------------------------------------------------------
/**
* 年龄----生成部门列表
*/
public ActionForward nianlinglist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_List tl = new TJ_List();
ArrayList dlist = tl.list(ds);
request.setAttribute("deptlist",dlist);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_nianling");
}
/**
* 按年龄统计查询
*/
public ActionForward nianling(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
ArrayList dlist1 = new ArrayList();
String tjdepts = request.getParameter("tjdept");
int tjdept = Integer.parseInt(tjdepts);
String age2 = request.getParameter("age2");
String age3 = request.getParameter("age3");
int n = 0;
Connection conn = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
String sql = "SELECT * FROM employees WHERE to_char(emp_birth,'yyyy') >= (to_char(sysdate,'yyyy')-'"+age3+"') AND to_char(emp_birth,'yyyy') <= (to_char(sysdate,'yyyy')-'" + age2 + "') AND dep_dep_id=" + tjdept;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
UserForm dept1 = new UserForm();
dept1.setDeptno(rs.getInt("emp_id")+"");
dept1.setDname(rs.getString("emp_name"));
dlist1.add(dept1);
n++;
}
ArrayList dlist2 = new ArrayList();
UserForm dept3 = new UserForm();
dept3.setDeptno(n + "");
dlist2.add(dept3);
request.setAttribute("j0",dlist2);//该年龄段在该部门的总人数
int h = 0;
//查找全厂该职称总数
String sql4 = "SELECT * FROM employees WHERE to_char(emp_birth,'yyyy') >= (to_char(sysdate,'yyyy')-'"+age3+"') AND to_char(emp_birth,'yyyy') <= (to_char(sysdate,'yyyy')-'" + age2 + "')";
Statement pstmt4 = conn.createStatement();
ResultSet rs4 = pstmt4.executeQuery(sql4);
while(rs4.next()){
h++;
}
request.setAttribute("count0",h + "");
//查找当前部门名称
String sql3 = "SELECT * FROM departments where dep_id=?";
//stmt = conn.createStatement();
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
pstmt3.setInt(1,tjdept);
ResultSet rs3 = pstmt3.executeQuery();
rs3.next();
request.setAttribute("dept",rs3.getString("dep_name"));
request.setAttribute("dep_id",rs3.getString("dep_id"));
} catch(Exception e1) {
e1.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
HttpSession session = request.getSession();
session.setAttribute("age2",age2 + "");
session.setAttribute("age3",age3 + "");
request.setAttribute("dept1",age2 + "岁到"+ age3 +"岁");
request.setAttribute("dept0",dlist1);
return mapping.findForward("tj_nianling_list");
}
/**
* 导出数据--年龄
*/
public ActionForward report_date_nianling(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String tjdept = request.getParameter("dept");
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
Connection conn = null;
WritableWorkbook book = null;
WritableSheet sheet = null;
try{
conn = ds.getConnection();
//建立Excel表
book= Workbook.createWorkbook(new File("C:/nianling_" + tjdept + ".xls"));
sheet=book.createSheet("nianling",0); //建立工作空间
int j = 0;
int k = 0;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -