上传文件

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>
				&nbsp;&nbsp;
				<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%">&nbsp;</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>
											&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;上传文件：
											<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="上&nbsp;&nbsp;传"
											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">&nbsp;</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}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.taskName}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.totalRecdNum}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><fmt:formatDate value="${uploadLogDto.operTime}"pattern="yyyy-MM-dd" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.itemTypeChinese}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.dptNme}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.empNme}" />&nbsp;</td>
							<td nowrap="nowrap" class="td6 align"><c:out value="${uploadLogDto.statusChinese}" />&nbsp;</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>