package com.isoftstone.util.uploadfile;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.junit.Test;
import jxl.Sheet;
import jxl.Workbook;
import com.isoftstone.haxdx.claim.integration.common.utils.StringUtils;
import com.isoftstone.haxdx.claim.integration.common.utils.Utils;
public class UploadFile {
private Connection conn = DBConnection.getConnection();
private List<Datadto> list = new ArrayList<Datadto>();
private File file = null;
private String filePath1 = "E:/1.xls"; //图片路径
private String filePath2 = "E:/2.xls"; //包含身份证
private int count = 0;
private String taskId; //任务号
public static void main(String[] args) throws Exception {
UploadFile uf = new UploadFile();
uf.action();
}
public void action() throws Exception{
this.file = new File(this.filePath1);
this.xlsToParse(this.file);
this.insertData();
this.file = new File(this.filePath2);
this.xlsToParse2(this.file);
this.updateData();
this.insertRelaImg();
this.updateTB_CLIENT_INDIV();
DBConnection.closeConnection(this.conn);
}
private String getTaskName(){
return file.getName();
}
// 读取还款协议合并信息.xls //合并号,路径
private void xlsToParse(File file) throws Exception {
System.out.println("--------------------xlsToParse");
String context = null;
try {
Workbook book = Workbook.getWorkbook(file);
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
int rowCount = 1;
Datadto dto = new Datadto();
while (true) {
try {
context = sheet.getCell(0, rowCount).getContents().trim();
} catch (Exception ex) {
context = null;
}
if (!StringUtils.hasText(context)) {
break;
}
// 清空计数器
this.count = 0;
dto.setUniteId(context.trim());
context = sheet.getCell(++count, rowCount).getContents();
if (StringUtils.hasText(context)) {
context = context.trim();
}
dto.setPath(context);
list.add(dto);
dto = new Datadto();
rowCount++;
}
book.close();
} catch (Exception e) {
throw e;
}
}
//补充还款协议.xls 文件名,身份证
private void xlsToParse2(File file) throws Exception {
System.out.println("--------------------xlsToParse2");
String context = null;
list = new ArrayList<Datadto>();
int count1 = 0;
try {
Workbook book = Workbook.getWorkbook(file);
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
int rowCount = 1;
Datadto dto = new Datadto();
while (true) {
try {
context = sheet.getCell(0, rowCount).getContents().trim();
} catch (Exception ex) {
context = null;
}
if (!StringUtils.hasText(context)) {
break;
}
// 清空计数器
count1 = 0;
dto.setFileName(context.trim());
context = sheet.getCell(++count1, rowCount).getContents();
if (StringUtils.hasText(context)) {
context = context.trim();
}
dto.setCertfNo(context);
list.add(dto);
dto = new Datadto();
rowCount++;
}
book.close();
} catch (Exception e) {
throw e;
}
}
//保存文件1,包含图片路径的
private void insertData() throws Exception {
System.out.println("--------------------insertData");
this.count = 0;
taskId = "TASK" + Utils.generateID();
Datadto dto = null;
PreparedStatement ps = null;
String sql = "INSERT INTO TB_HKXY_IMG( " +
" C_ID,C_UNITEID,C_TASK_ID,C_PATH, " +
" C_REMARK,C_IMG_NAME,C_IMG_TITLE ,C_ISERR) " +
" VALUES('ID'||seq_claim_import.nextval,?,?,?,?,?,?,'身份证错误')";
ps = conn.prepareStatement(sql);
Iterator<Datadto> it = list.iterator();
while (it.hasNext()) {
dto = it.next();
String uniteId = dto.getUniteId();
String temp = uniteId.substring(uniteId.lastIndexOf("/")+1);
ps.setString(1, temp);
ps.setString(2, this.taskId);
String[] arr = dto.getPath().split("\\n");
for (int i = 0; i < arr.length; i++) {
String fullname = arr[i].trim();
ps.setString(3, fullname);
String remark = fullname.substring(0, fullname.lastIndexOf("/")+1);
String imgname = fullname.substring(fullname.lastIndexOf("/")+1);
String title = imgname.substring(0,imgname.indexOf("jpg")-1);
ps.setString(4, remark);
ps.setString(5, imgname);
ps.setString(6, title);
count++;
ps.addBatch();
if(count%1000==0){
System.out.println("记录数:"+count);
ps.executeBatch();
}
}
}
ps.executeBatch();
ps.close();
}
//保存文件2:包含身份证的
private void updateData() throws Exception {
System.out.println("--------------------updateData");
Datadto dto = null;
PreparedStatement ps = null;
String sql = "UPDATE TB_HKXY_IMG SET C_CERTF_NO=? " +
" WHERE C_UNITEID=LOWER(?) AND C_TASK_ID=?";
ps = conn.prepareStatement(sql);
Iterator<Datadto> it = list.iterator();
int i=0;
while (it.hasNext()) {
dto = it.next();
i++;
ps.setString(1, dto.getCertfNo());
ps.setString(2, dto.getFileName());
ps.setString(3, this.taskId);
ps.addBatch();
if(i%1000==0){
System.out.println("记录数:"+i);
ps.executeBatch();
}
}
ps.executeBatch();
ps.close();
this.certfNoIsNotFind();
this.makeTaskRecord();
}
//更新找不到身份证的数据为错误数据
private void certfNoIsNotFind() throws Exception{
System.out.println("-----------------certfNoIsNotFind");
PreparedStatement ps = null;
String iserr = " UPDATE TB_HKXY_IMG T1 SET T1.C_ISERR='正确' " +
" WHERE T1.C_CERTF_NO IN " +
" (SELECT C_CERTF_NO FROM TB_CLIENT_BASE) " +
" AND C_TASK_ID=? AND T1.C_ISERR='身份证错误' ";
ps = conn.prepareStatement(iserr);
ps.setString(1, this.taskId);
ps.executeUpdate();
ps.close();
}
//保存到任务记录表
private void makeTaskRecord() throws Exception {
System.out.println("--------------------makeTaskRecord");
PreparedStatement ps = null;
String sql = " INSERT INTO TB_UPLOADFILE_TASK_RECORD( " +
" C_TASK_ID,C_TASK_NAME,C_DEPTNO,N_TOT_RECORD, " +
" N_ERR_RECORD,C_STATUS,T_OPER_TIME) " +
" VALUES(?,?,?,?, " +
" (SELECT COUNT(1) RN FROM TB_HKXY_IMG " +
" WHERE C_ISERR='身份证错误' " +
" AND C_TASK_ID=?),'1',sysdate)";
ps = conn.prepareStatement(sql);
ps.setString(1, this.taskId);
ps.setString(2,this.getTaskName());
ps.setString(3, "0000000000");
ps.setInt(4, this.count);
ps.setString(5,this.taskId);
ps.execute();
ps.close();
}
//插入正式表TB_RELA_IMG
private void insertRelaImg() {
System.out.println("--------------------insertRelaImg");
PreparedStatement ps = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String fileName="云南还-1";
String bz = sdf.format(new Date()) + "导入_" + fileName;
try {
String sql = "INSERT INTO TB_RELA_IMG( " +
" C_RELA_IMG_CDE, C_CLIENT_CDE,C_CERTF_NO, " +
" N_INS_NO,C_INS_CDE,C_CLIENT_TYPE,C_AGR_CDE, " +
" C_IMG_CDE, C_IMG_TITLE,C_IMG_NAME, " +
" C_SAVE_PATH,N_PAGE_NO,C_BANK_CDE, " +
" C_REMARK, C_DPT_CDE, C_CONT_CDE) " +
" (SELECT t1.C_ID , " +
" (SELECT C_CLIENT_CDE FROM TB_CLIENT_BASE " +
" WHERE C_CERTF_NO=t1.C_CERTF_NO AND ROWNUM=1) AS C_CLIENT_CDE, " +
" t1.C_CERTF_NO , " +
" '1','','0','', " +
" 'HKH',t1.C_IMG_TITLE,t1.C_IMG_NAME, " +
" t1.C_PATH, '1','', " +
" t1.C_REMARK,'' , ? " +
" FROM TB_HKXY_IMG t1 where C_ISERR='正确' AND C_TASK_ID=?)";
ps = conn.prepareStatement(sql);
ps.setString(1, bz);
ps.setString(2, this.taskId);
ps.execute();
ps.close();
} catch (SQLException e) {
System.out.println("\n\n ---------**** err ****--------TB_HKXY_IMG-------taskId=" + this.taskId);
try {
conn.rollback();
} catch (SQLException e2) {
e2.printStackTrace();
}
e.printStackTrace();
if(ps != null) {
try {
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
//更新客户表的图像表示字段,改成有图片Y
private void updateTB_CLIENT_INDIV() throws SQLException {
System.out.println("--------------------updateTB_CLIENT_BASE");
PreparedStatement ps = null;
String sql = " UPDATE TB_CLIENT_BASE T SET T.C_IMG_FLAG='Y' " +
" WHERE C_CERTF_NO IN ( " +
" SELECT C_CERTF_NO FROM TB_HKXY_IMG " +
" WHERE C_ISERR='正确' AND C_TASK_ID=?) ";
ps = conn.prepareStatement(sql);
ps.setString(1, this.taskId);
ps.execute();
ps.close();
}
/*
* 说明:因为表TB_RELA_IMG的C_SAVE_PATH字段有唯一性约束,对应表TB_HKXY_IMG的C_PATH字段也必须唯一
* 如果插入出错可以通过C_PATH是否唯一查出哪一条出错
SELECT T1.* FROM TB_HKXY_IMG T1, TB_HKXY_IMG T2
WHERE T1.C_PATH = T2.C_PATH AND T1.C_ID != T2.C_ID;
*
*
* SELECT T1.C_PATH, COUNT(T1.C_PATH)
FROM TB_HKXY_IMG T1
WHERE T1.C_TASK_ID='TASK126051494668761'
GROUP BY C_PATH
HAVING COUNT(1)>1
*
*/
public String getFilePath1() {
return filePath1;
}
public void setFilePath1(String filePath1) {
this.filePath1 = filePath1;
}
public String getFilePath2() {
return filePath2;
}
public void setFilePath2(String filePath2) {
this.filePath2 = filePath2;
}
}
<%@ page contentType="text/html;charset=GBK"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt"%>
<%@ include file="../include.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>文件上传</title>
<style type="text/css">
<!--
@import url("../css/main.css");
@import url("../css/search.css");
-->
</style>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/common.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/province_city.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/client_info.js"></script>
<script type="text/javascript">
function delData(id){
var obj = getObjects('checkbox');
var n=obj.length;
var flag=0;
for (var i = 0; i < n; i++) {
if(obj[i].checked == true){
flag=1;
break;
}
}
if(flag==0){
alert("请选择要删除的数据");
return;
}
if(confirm("确定删除?")){
alert("删除成功");
}
}
function lockTask(selectValue){
if(selectValue==true){
if(taskId == '')
{
document.getElementById("taskName").value="";
document.getElementById("taskName").readOnly= true;
}else {
document.getElementById("taskName").value=fileName;
document.getElementById("taskName").readOnly= true;
}
}
if(selectValue==false){
// alert("解除锁定!");
document.getElementById("taskName").value="";
document.getElementById("taskName").readOnly= false;
}
}
// 定义变量,标示用户选定的 任务ID
var taskId='';
function setSelectId(selectId){
taskId = selectId;
}
var fileName = '';
function showFileName(filename){
var checkValue = document.getElementById("zjChk").checked ;
fileName = filename;
if(checkValue == true){
document.getElementById("taskName").value = filename;
}
else {
document.getElementById("taskName").value = "";
}
}
// 检查是否选中了要续传的记录
function checkSelect(){
//alert(" 上传前检查");
var checkValue = document.getElementById("zjChk").checked ;
// 判断是否选择了追加;
if(checkValue==true){
if(taskId==''||taskId==null){
alert("请选择您要追加的任务清单!");
return false;
}
// 判断是否选择了导入的文件
var filePath = document.getElementById("file").value;
if(filePath == "" || filePath==null){
alert("请选择您要上传的文件!");
return false;
}
document.getElementById("addFlag").value = 'isAdd';
document.getElementById("addTaskId").value = taskId;
}
else{
var taName = document.getElementById("taskName").value;
if(taName == "" || taName==null){
alert("请输入您要上传的任务名称!");
return false;
}
var len = taName.length;
if(len>25){
alert("您输入的上传名称太长,不能超过25个字符长度!");
return false;
}
var filePath = document.getElementById("file").value;
if(filePath == "" || filePath==null){
alert("请选择您要上传的文件!");
return false;
}
}
return true;
}
// 清单内容查看前,检查是否选择了任务清单
function checkManifest(){
if(taskId==''||taskId==null){
alert("请先选择任务清单!");
return ;
}
var url = "<%=request.getContextPath()%>/claim/manifestController.do?cmd=lookManifest&taskId="+taskId+"&itemType=<c:out value='${itemType}'/>";
modalWindow(url,1000,700);
}
function checkConfirm(){
if(taskId==''||taskId==null){
alert("请先选择要确认的任务清单!");
return ;
}
window.location="<%=request.getContextPath()%>/claim/manifestController.do?cmd=confirmManifest&taskId="+taskId+"&itemType=<c:out value='${itemType}'/>";
}
// 删除任务清单前,检查是否选择了任务清单
function checkDelete(){
if(taskId==''||taskId==null){
alert("请先选择要删除的任务清单!");
return;
}
if(confirm('您确定要删除此清单吗?')){
window.location="<%=request.getContextPath()%>/claim/manifestController.do?cmd=delManifest&taskId="+taskId+"&itemType=<c:out value='${itemType}'/>";
}
}
// 双击任务,显示清单
function dblManifest(tasId)
{
var dblUrl="<%=request.getContextPath()%>/claim/manifestController.do?cmd=lookManifest&taskId="+tasId+"&itemType=<c:out value='${itemType}'/>";
modalWindow(dblUrl,1200,700);
}
function btnShow(){
var x='<c:out value='${itemType}'/>';
if(x!='TB_CLM_ZSLPSS_TMP'){
document.getElementById("btnGetData").style.display='none';
}
}
function getData(){
var dptCde="<%=depCode%>";
if(dptCde==''||dptCde==null){
alert('SESSION失效,请重新登入');
return;
}
if(dptCde=='00'){
alert('须各分公司提取逾期数据,总公司人员无此权限');
return;
}
if(confirm('您确定提取逾期数据吗')){
alert('提取数据的过程可能比较慢,请耐心等候');
window.location="<%=request.getContextPath()%>/claim/manifestController.do?cmd=insertZslpss";
}
}
</script>
</head>
<body οnlοad="btnShow()">
<div class="tabtitle1">
<div class="float_right"></div>
<h2>
<strong class="f_2">文件上传</strong>
</h2>
</div>
<div class="mainfream">
<div class="search">
<table border="0" cellpadding="4" cellspacing="0" class="align">
<form method="post" action="fileUpload.do"
enctype="multipart/form-data">
<tr>
<td width="82%">
<table border="0" cellpadding="0" cellspacing="1">
<tr>
<tr>
<td width="7%" align="right" nowrap="nowrap">
<div align="left">
<c:if test="${itemType=='TB_CLM_HDJH_TMP'}">清单类型:学生还款计划</c:if>
<c:if test="${itemType=='TB_CLM_KKQZHYE_TMP'}">清单类型:扣款前帐户余额清单</c:if>
<c:if test="${itemType=='TB_CLM_MQHK_TMP'}">清单类型:每期还款清单</c:if>
<c:if test="${itemType=='TB_CLM_ZSLPSS_TMP'}">清单类型:正式理赔损失清单</c:if>
<c:if test="${itemType=='TB_CLM_ZHPK_TMP'}">清单类型:追回赔款清单</c:if>
<c:if test="${itemType=='TB_CLM_ZJLP_SP_TMP'}">清单类型:无效验直接理赔损失清单</c:if>
</div>
</td>
<td width="93%"> </td>
</tr>
<tr>
<td align="right" nowrap="nowrap">
上传名称:
<input name="taskName" id="taskName" class="text" maxlength="50" size="50">
<input type="checkbox" name="zjChk" id="zjChk" value="checkbox"
οnclick="lockTask(this.checked);" />
追加文件
</td>
<td>
<label>
上传文件:
<input type="hidden" name="addFlag" />
<input type="hidden" name="addTaskId" />
<input type="hidden" name="itemType"
value="<c:out value='${itemType}'/>" />
<input name="file" class="upload" type="file" id="fileId"
size="50" οnchange="return checkSelect();">
</label>
<input type="Submit" value="上 传"
onClick="javascript:return checkSelect();" class="Normal"
onMouseOver="this.className='Normalover'"
οnmοuseοut="this.className='Normal'" />
</td>
</tr>
</form>
</table>
</td>
</tr>
<tr>
<td>
<tbody></tbody>
</td>
</tr>
</table>
</div>
<div class="list">
<table border="0" cellpadding="0" cellspacing="0" id="tableList">
<form name="pageform" id="pageform" method="post"
action="<%=request.getContextPath()%>/claim/uploadDataFormController.do?cmd=stuPayPlan&itemType=<c:out value='${itemType}'/>">
<tr class="lis_title">
<td width="3%" class="lis_title"> </td>
<td width="12%" class="lis_title">上传名称</td>
<td width="11%" class="lis_title">总记录数</td>
<td width="10%" class="lis_title">导入时间</td>
<td width="12%" class="lis_title">清单类型</td>
<td width="14%" class="lis_title">导入机构</td>
<td width="10%" class="lis_title">导入人</td>
<td width="10%" class="lis_title">状态</td>
</tr>
<c:forEach items="${dtoList}" var="uploadLogDto" varStatus="state">
<tr
οnclick="selectThis(this);setSelectId('<c:out value="${uploadLogDto.taskId}" />');showFileName('<c:out value="${uploadLogDto.taskName}" />')";
onDblClick="dblManifest('<c:out value="${uploadLogDto.taskId}" />');">
<td nowrap="nowrap" class="td1"><c:out value="${state.count}" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.taskName}" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.totalRecdNum}" /> </td>
<td nowrap="nowrap" class="td6 align"><fmt:formatDate value="${uploadLogDto.operTime}"pattern="yyyy-MM-dd" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.itemTypeChinese}" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.dptNme}" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.empNme}" /> </td>
<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.statusChinese}" /> </td>
</tr>
</c:forEach>
<tr>
<td colspan="8" class="td1 padding3">
<div>
<c:out value="${pageInfo.html}" escapeXml="false" />
<input type="button" name="btn1534224" value="清单查看"
onClick="javascript:checkManifest();" class="Normalover"
onMouseOver="this.className='Normal'"
οnmοuseοut="this.className='Normalover'" />
<input type="button" name="btnConfirm" value="数据确认"
onClick="javascript:checkConfirm();" class="Normalover"
onMouseOver="this.className='Normal'"
οnmοuseοut="this.className='Normalover'" />
<input type="button" name="btnGetData"
id="btnGetData" value="提取逾期数据"
onClick="javascript:getData();" class="Normalover"
onMouseOver="this.className='Normal'"
οnmοuseοut="this.className='Normalover'" />
<input type="button" name="btn1534" value=" "
onClick="checkDelete();" class="Delbuttonover"
onMouseOver="this.className='Delbutton'"
οnmοuseοut="this.className='Delbuttonover'" />
</div>
</td>
</tr>
</form>
</table>
</div>
</div>
</body>
</html>