📄 pldrhandler.java
字号:
package com;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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 jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import com.jstrd.gdzc.comm.Department;
import com.jstrd.gdzc.comm.GdzcDate;
import com.jstrd.gdzc.comm.SysPath;
import com.jstrd.util.DBConnection;
import com.jstrd.web.Token;
public class PldrHandler extends HttpServlet {
private String message = ""; // 验证错误信息a
private Statement stmt = null;
// private String k_dwbh = ""; //单位编号
// private String kpbName = "gd_kpb"; //根据单位编号得到的卡片表表名
int rows = 0; // Excel的行数
// private String userName; //当前登陆用户姓名
private int startSheet = 0;
private int startRow = 2;
private int columns = 45; // 列
// private Date jkrq = null;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, java.io.IOException {
HttpSession session = request.getSession();
Token token = (Token) session.getAttribute("token");
message = "";
Date jkrq = null;
String k_dwbh = "";
String userName = "";
// String dw = "";
String kpbName = "";
String k_jkrq = request.getParameter("k_jkrq");
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
jkrq = df.parse(k_jkrq);
} catch (ParseException e2) {
System.out.println(e2.getMessage());
}
k_dwbh = (String) request.getParameter("k_dwbh").substring(0, 4);
// if (k_dwbh==null || k_dwbh="")
// {
// k_dwbh=token.get
// }
// System.out.println("k_dwbh-->" + k_dwbh);
userName = request.getParameter("UserName");
// System.out.println("userName-->" + userName);
// kpbName = (String) CodeConvert.SQLTABLE.get(k_dwbh);
kpbName = Department.GetSpecialDepartmentCardTableName(k_dwbh);
// dw = kpbName.substring(6);
String path = request.getParameter("path");
path = session.getServletContext().getRealPath("/") + "/" + path;
request.setAttribute("filepath", path);
// System.out.println("path-->" + path);
System.out.println("单位" + k_dwbh + "开始验证批量导入数据格式：");
if (message.equals("")) {
// DBBroke db = BrokeFactory.getBroke(token.getDSName());
DBConnection db = null;
String tableName = "";
try {
db = new DBConnection(token.getDSName());
long startTime = System.currentTimeMillis();
ArrayList arr = validate(path, k_dwbh, jkrq, db, token); // 批量导入验证,并将数据导入list
long endTime = System.currentTimeMillis();
System.out.println("单位" + k_dwbh + "批量导入验证格式完毕，共耗时："
+ (endTime - startTime) + "毫秒");
if (message.equals("")) {
System.out.println("单位" + k_dwbh + "批量导入开始建立临时表：");
startTime = System.currentTimeMillis();
tableName = tempTable(arr, userName, k_dwbh, kpbName, db,
token); // 将list中的数据导入到临时表temp_pldr_用户名 表中,并验证数据正确型
endTime = System.currentTimeMillis();
System.out.println("单位" + k_dwbh + "批量导入建立临时表完毕，共耗时："
+ (endTime - startTime) + "毫秒");
if (message.equals("")) {
System.out.println("单位" + k_dwbh + "开始批量导入数据：");
startTime = System.currentTimeMillis();
PLDRIMPORT pldr = new PLDRIMPORT();
pldr.importData(k_dwbh, userName, tableName, db);
endTime = System.currentTimeMillis();
System.out.println("单位" + k_dwbh + "批量导入数据完毕，共耗时："
+ (endTime - startTime) + "毫秒");
} else {
System.out.println("单位" + k_dwbh + "临时表数据验证未通过！");
request.setAttribute("ErrorMessage", message);
getServletContext().getRequestDispatcher(
"/wrzgl/pldr/pldr_error.jsp").forward(request,
response);
return;// mapping.findForward("error");
}
} else {
System.out.println("单位" + k_dwbh + "临时表数据验证未通过！");
request.setAttribute("ErrorMessage", message);
getServletContext().getRequestDispatcher(
"/wrzgl/pldr/pldr_error.jsp").forward(request,
response);
return;// mapping.findForward("error");
}
} catch (Exception e) {
System.out.println("批量导入发生错误，原因：" + e.getMessage());
String message = e.getMessage() + "\n";
request.setAttribute("ErrorMessage", message);
getServletContext().getRequestDispatcher(
"/wrzgl/pldr/pldr_error.jsp")
.forward(request, response);
return;// mapping.findForward("error");
} finally {
try {
if (!tableName.equals("")) {
delTable(tableName, k_dwbh, db);
}
db.close();
} catch (Exception e) {
}
}
getServletContext().getRequestDispatcher(
"/wrzgl/pldr/pldr_success.jsp").forward(request, response);
// mapping.findForward("success");
// return mapping.findForward("success");
} else {
System.out.println("单位" + k_dwbh + "批量导入格式验证未通过！");
request.setAttribute("ErrorMessage", message);
getServletContext().getRequestDispatcher(
"/wrzgl/pldr/pldr_error.jsp").forward(request, response);
// mapping.findForward("error");
}
}
private String tempTable(ArrayList arr, String userName, String k_dwbh,
String kpbName, DBConnection db, Token token) throws Exception {
Statement stmt = null;
ResultSet rs = null;
ResultSet rs2 = null;
PreparedStatement pstmt = null;
System.out.println("tempTable" + k_dwbh);
System.out.println("tempTable" + userName);
System.out.println("tempTable" + kpbName);
String tableName = "";
try {
stmt = db.getConnection().createStatement();
tableName = "temp_pldr_" + userName;
String sql = "select count(*) from all_tables t where t.table_name = '"
+ tableName.toUpperCase() + "'";
rs = stmt.executeQuery(sql);
rs.next();
if (rs.getInt(1) > 0) {
delTable(tableName, k_dwbh, db);
}
rs.close();
sql = " create table " + tableName + " ( " + " id numeric(10,0) ,"
+ " k_xh NUMBER(18) NOT NULL,"
+ " k_kpbh NUMBER(18) DEFAULT 0,"
+ " k_dwbh varchar2(20) NOT NULL ," // DEFAULT '" +
// k_dwbh + "',"
+ " k_zcbh varchar2(80) DEFAULT '', "
+ " k_zcml varchar2(18) NULL, "
+ " k_zcfl NUMBER(5) NULL, "
+ " k_kmdh varchar2(20) NULL, "
+ " k_zcgs NUMBER(5) NULL, "
+ " k_zygs NUMBER(5) NULL, "
+ " k_zyft varchar2(200) NULL, "
+ " k_cpft varchar2(200) DEFAULT '', "
+ " k_cpsx VARCHAR2(20) NULL, "
+ " k_kpxz NUMBER(5) DEFAULT 0, "
+ " k_zgf NUMBER(5) NULL, "
+ " k_fz NUMBER(5) NULL, "
+ " k_fkpbh numeric(18,0) DEFAULT 0, "
+ " k_fkpxh numeric(18,0) NULL, "
+ " k_glkpbh numeric(18,0) DEFAULT 0, "
+ " k_zjlx NUMBER(5) NULL, "
+ " k_abc char(1) DEFAULT '', "
+ " k_jgggcs varchar2(200) NULL, "
+ " k_sccj varchar2(200) NULL, "
+ " k_sl numeric(18,4) NULL, "
+ " k_glbm varchar2(20) NULL, "
+ " k_sybm varchar2(20) NULL, "
+ " k_sydd varchar2(200) NULL, "
+ " k_bgy varchar2(20) NULL, "
+ " k_gmrq TIMESTAMP(6) NULL, "
+ " k_jkrq TIMESTAMP(6) NULL, "
+ " k_kssyrq TIMESTAMP(6) NULL, "
+ " k_rzrq TIMESTAMP(6) NULL, "
+ " k_zczt NUMBER(5) DEFAULT 1, "
+ " k_zjnx NUMBER(5) DEFAULT 0, "
+ " k_qbnynx NUMBER(5) DEFAULT 0, "
+ " k_sksynx NUMBER(5) DEFAULT 0, "
+ " k_yz numeric(18,2) NULL, "
+ " k_jz numeric(18,2) NULL, "
+ " k_jcz numeric(18,2) NULL, "
+ " k_ljzj numeric(18,2) NULL, "
+ " k_ljjz numeric(18,2) DEFAULT 0, "
+ " k_yzjl numeric(15,12) DEFAULT 0, "
+ " k_yzje numeric(18,2) DEFAULT 0, "
+ " k_zjff NUMBER(5) DEFAULT 1, "
+ " k_zjzt NUMBER(5) DEFAULT 0, "
+ " k_sfjt NUMBER(5) DEFAULT 0, "
+ " k_gcbh varchar2(200) NULL, "
+ " k_gcmc varchar2(200) NULL, "
+ " k_swzcbh varchar2(200) NULL, "
+ " k_pzbh varchar2(40) NULL, "
+ " k_khsx NUMBER(5) NULL, "
+ " k_bz varchar2(100) NULL, "
+ " k_jslx NUMBER(5) DEFAULT 0, "
+ " k_pyf NUMBER(5) NULL, "
+ " k_syr varchar2(40) NULL, "
+ " k_czl numeric(10,2) DEFAULT 0.03, "
+ " k_zcmc varchar2(600) NULL, "
+ " k_zdybm varchar2(20) DEFAULT '', "
+ " k_jkr varchar2(20) NULL, "
+ " k_shf NUMBER(5) DEFAULT 1, "
+ " k_gly varchar2(20) NULL,"
+ " k_gys varchar2(100) NULL, "
+ " k_qybm varchar2(40) NULL,"
+ " k_sbwzh varchar2(100) NULL, "
+ " k_bklyl numeric(18,2) NULL, "
+ " k_yhszl numeric(18,2) NULL, "
/*
* + " k_yzje01 numeric(18,2) DEFAULT 0, " + " k_yzje02
* numeric(18,2) DEFAULT 0, " + " k_yzje03 numeric(18,2)
* DEFAULT 0, " + " k_yzje04 numeric(18,2) DEFAULT 0, " + "
* k_yzje05 numeric(18,2) DEFAULT 0, " + " k_yzje06
* numeric(18,2) DEFAULT 0, " + " k_yzje07 numeric(18,2)
* DEFAULT 0, " + " k_yzje08 numeric(18,2) DEFAULT 0, " + "
* k_yzje09 numeric(18,2) DEFAULT 0, " + " k_yzje10
* numeric(18,2) DEFAULT 0, " + " k_yzje11 numeric(18,2)
* DEFAULT 0, " + " k_yzje12 numeric(18,2) DEFAULT 0, "
*/
+ " k_ynsj TIMESTAMP(6) NULL , "
+ " k_zclx NUMBER(5) DEFAULT 1)";
/*
* + " CONSTRAINT k_xh " + " PRIMARY KEY NONCLUSTERED (k_xh)" + " ) ";
*/
db.executeUpdate(sql);
/*
* sql = " select * into " + tableName + " from gd_pldrb ";
* db.executeUpdate(sql); sql = " delete from " + tableName;
* db.executeUpdate(sql);
*/
sql = "insert into "
+ tableName
+ " (k_xh,k_dwbh,k_zcml, k_zcfl,k_kmdh,k_zcgs,k_zygs,k_zyft, "
+ " k_cpsx,k_zgf,k_fz,k_fkpbh,k_fkpxh,k_zjlx,"
+ " k_jgggcs,k_sccj,k_sl,k_glbm,k_sybm,k_sydd,k_bgy,k_gmrq,k_jkrq,k_kssyrq, "
+ " k_rzrq,k_yz,k_jz,k_jcz,k_ljzj,"
+ " k_gcbh,k_gcmc,k_swzcbh,k_pzbh,k_khsx,k_bz, "
+ " k_pyf,k_syr,k_zcmc,k_jkr,k_gly, k_gys, "
+ " k_qybm, k_sbwzh, k_bklyl, k_yhszl,k_zclx"
+ " ) values( " + " ?,?,?,?,?,?,?,?,?,?,?,?,"
+ " ?,?,?,?,?,?,?,?,?,?,?," + " ?,?,?,?,?,?,?,?,?,?,?,"
+ " ?,?,?,?,?,?,?,?,?,?,?,?)";
// Connection conn = db.getConnection();
// conn.setAutoCommit(false);
pstmt = db.getConnection().prepareStatement(sql);
/*
* PreparedStatement pstmt =
* ((SybConnection)conn).prepareStatement(sql,false);
*/
// long start = System.currentTimeMillis();
for (int i = 0; i < arr.size(); i++) {
PLDRKP rowkp = (PLDRKP) arr.get(i);
pstmt.setLong(1, rowkp.getXh());
pstmt.setString(2, rowkp.getDwbh());
pstmt.setString(3, rowkp.getZcml());
pstmt.setInt(4, rowkp.getZcfl());
pstmt.setString(5, rowkp.getKmdh());
pstmt.setInt(6, rowkp.getZcgs());
pstmt.setInt(7, rowkp.getZygs());
pstmt.setString(8, rowkp.getZyft());
pstmt.setString(9, rowkp.getCpsx());
pstmt.setInt(10, rowkp.getZgf());
pstmt.setInt(11, rowkp.getFz());
pstmt.setLong(12, rowkp.getFkpbh());
pstmt.setLong(13, rowkp.getFkpxh());
pstmt.setInt(14, rowkp.getZjlx());
pstmt.setString(15, (rowkp.getJgggcs()));
pstmt.setString(16, (rowkp.getSccj()));
pstmt.setDouble(17, rowkp.getSl());
pstmt.setString(18, rowkp.getGlbm());
pstmt.setString(19, rowkp.getSybm());
pstmt.setString(20, (rowkp.getSydd()));
pstmt.setString(21, (rowkp.getbgy()));
pstmt.setDate(22, new java.sql.Date(rowkp.getGmrq().getTime()));
pstmt.setDate(23, new java.sql.Date(rowkp.getJkrq().getTime()));
pstmt.setDate(24,
new java.sql.Date(rowkp.getKssyrq().getTime()));
pstmt.setDate(25, new java.sql.Date(rowkp.getRzrq().getTime()));
pstmt.setDouble(26, rowkp.getYz());
pstmt.setDouble(27, rowkp.getJz());
pstmt.setDouble(28, rowkp.getJcz());
pstmt.setDouble(29, rowkp.getLjzj());
pstmt.setString(30, rowkp.getGcbh());
pstmt.setString(31, (rowkp.getGcmc()));
pstmt.setString(32, (rowkp.getSwzcbh()));
pstmt.setString(33, rowkp.getPzhm());
pstmt.setInt(34, rowkp.getKhsx());
pstmt.setString(35, (rowkp.getBz()));
pstmt.setInt(36, rowkp.getPyf());
pstmt.setString(37, (rowkp.getSyr()));
pstmt.setString(38, (rowkp.getZcmc()));
pstmt.setString(39, (rowkp.getJkr()));
pstmt.setString(40, (rowkp.getGly()));
pstmt.setString(41, (rowkp.getGys()));
pstmt.setString(42, rowkp.getQybm());
pstmt.setString(43, (rowkp.getSbwzh()));
pstmt.setDouble(44, rowkp.getBklyl());
pstmt.setDouble(45, rowkp.getYhszl());
pstmt.setInt(46, rowkp.getK_zclx());
// pstmt.addBatch();
pstmt.executeUpdate();
}
// 资产目录
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_zcmlb b where a.k_zcml=b.k_zcml) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片资产目录不存在\n";
}
rs.close();
sql = " select k_xh from "
+ tableName
+ " where k_fz=1 and k_xh in ("
+ " select k_xh from "
+ tableName
+ " a,gd_zcmlb b where a.k_zcml=b.k_zcml and ((LENGTH(a.k_zcml)>9 and LENGTH(a.k_zcml)<=11) or LENGTH(a.k_zcml)<=7) and a.k_fz=1) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片的资产目录不允许建父卡片\n";
}
rs.close();
// 会计科目
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_kmdhb b where a.k_kmdh=b.k_kmdh) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片会计目录不存在\n";
}
rs.close();
// 资产分类
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_zcflb b where a.k_zcfl=b.k_zcfl) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片资产分类不存在\n";
}
rs.close();
// 资产归属
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_zcgsb b where a.k_zcgs=b.k_zcgs) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片资产归属不存在\n";
}
rs.close();
// 专业归属
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_zygsb b where a.k_zygs=b.k_zygs) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片专业归属不存在\n";
}
rs.close();
// 使用部门
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " a,gd_dsbmb b where b.k_dwbh=a.k_dwbh and a.k_sybm=b.k_bmbh ) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片使用部门编码不存在或非使用部门\n";
}
rs.close();
// 管理部门
// System.out.println(token.getDeptFlg());
if (Integer.parseInt(token.getDeptFlg()) == 0) {
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " a,gd_dsbmb b where b.k_dwbh=a.k_dwbh and a.k_glbm=b.k_bmbh and b.k_bmxz=1) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片管理部门编码不存在或非管理部门\n";
}
rs.close();
} else {
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " a,gd_dsbmb b where a.k_dwbh=b.k_dwbh and length(a.k_dwbh)<=4 and a.k_glbm=b.k_bmbh and b.k_bmxz=1) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片管理部门编码不存在或非管理部门\n";
}
rs.close();
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " where k_glbm in(select k_bmbh from gd_dsbmb where k_dwbh like '"
+ k_dwbh + "%' "
+ " and k_bmxz=1 ) and length(k_dwbh)>4";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片管理部门编码不存在或非管理部门\n";
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -