warehouse-edi/src/main/java/com/uas/main/Download.java

package com.uas.main;

import com.jcraft.jsch.JSchException;
import com.uas.util.BaseUtil;
import com.uas.util.FtpUtil;
import com.uas.util.JdbcUtil;
import com.uas.util.SFTPUtil;
import org.apache.commons.net.ftp.FTPClient;
import org.codehaus.jackson.map.ObjectMapper;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class Download{

	public static Boolean checkXmlFileToSqlToEnterprise(File file,String enterprise){
		String ent = null;
		Element root = getElementRoot(file);  //获取根节点
		Map<String, String> mainData = getMainData(root); //获取主表数据 {tag:data}
		ent = mainData.get("ENTERPRISE");
		if (ent==null) {
			return true;
		}else {
			return ent.equals(enterprise);
		}
	}
	
	/*
	 * 通过配置解析xml为sql语句
	 */
	public static List<String> parseXmlFileToSqls(File file,Statement statement){
		List<String> sqls = new ArrayList<String>();
		Statement stm=statement;
		String mainTableUpdateSql = null;
		String updateDetailConditon = null;
		List<Map<String,String>> detailXmlConfig = null;
		List<Map<String,String>> detailData = null;
		
		String fileName = file.getName().substring(0, file.getName().indexOf(".")) + ".xml";
		String filePrefix = fileName.substring(0,fileName.indexOf("_")+1); //获取文件名前缀
		String docType = null;
		List<String> logSql = new ArrayList<String>();
		
		Element root = getElementRoot(file);  //获取根节点
		Map<String, String> mainData = getMainData(root); //获取主表数据 {tag:data}
		Element detail = root.element("Detail");  //获取明细节点
		if(detail!=null){
			detailData = getDetailData(root);  //获取明细数据
		}
		
		docType = mainData.get("DOC_TYPE"); //文件类型
		Map<String,String> xmlConfig = getXmlConfig(statement,docType,filePrefix); //通过文件名前缀查找相应配置
		if(xmlConfig.keySet().size()==0){
			return null;
		}
		String caller=xmlConfig.get("xd_caller");
		List<Map<String,String>> mainXmlConfig = getXmlConfigDetail(statement,xmlConfig.get("xd_id"),"0",xmlConfig.get("xd_main_conditiontag")); //获取主表的配置
		if(xmlConfig.get("xd_detail_conditiontag")!=null&&detailData!=null){
			if("Packing".equals(caller)||"Invoice".equals(caller)){
			detailXmlConfig = getXmlConfigDetail(statement,xmlConfig.get("xd_id"),"1"); //获取明细表的配置	
			}else {
                detailXmlConfig = getXmlConfigDetail(statement, xmlConfig.get("xd_id"), "1", xmlConfig.get("xd_detail_conditiontag")); //获取明细表的配置
            }
			}
		
		String updateMainConditon = getUpdateMainCondtion(mainData,xmlConfig); //主表更新条件
		updateMainConditon = updateMainConditon.replace(" pi_inoutno='' and ","");//主表更新条件
		String maincode = mainData.get(xmlConfig.get("xd_main_conditiontag"));
		if(mainXmlConfig.size()>0){
			mainTableUpdateSql = getUpdateMainFields(mainData,xmlConfig,mainXmlConfig); //主表更新字段
			mainTableUpdateSql += " where " + updateMainConditon;
			sqls.add(mainTableUpdateSql);
		}
		if(detailXmlConfig!=null&&detailXmlConfig.size()>0){
			updateDetailConditon = getUpdateDetailConditionByMainTable(mainData,xmlConfig); //明细表跟主表关联更新条件
			for(Map<String,String> data:detailData){
				String sql = null;
				if("Packing".equals(caller)||"Invoice".equals(caller)){
					sql = getInsertDetailSql(maincode,data,xmlConfig,detailXmlConfig);
				}else{
					sql = getUpdateDetailSql(data,xmlConfig,detailXmlConfig) + " and " + updateDetailConditon;
				}
				sqls.add(sql);
			}
		}
		
		//记录json数据
		logSql = getLogXmlDataAndInterceptorSql(mainData,detailData,xmlConfig,statement,fileName);
		sqls.addAll(logSql);
		try {
			stm.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		return sqls;
	}

	@SuppressWarnings("finally")
	private static List<String> getLogXmlDataAndInterceptorSql(Map<String, String> mainData,List<Map<String,String>> detailData,Map<String,String> xmlConfig,Statement statement,String fileName){
		ObjectMapper mapper = new ObjectMapper();
		String jsonData = null;
		String log = null;
		String caller = null;
		int id = 0;
		ResultSet rs = null;
		
		List<String> sqls = new ArrayList<String>();
		
		try {
			jsonData = mapper.writeValueAsString(mainData);
			
			if(detailData!=null){
				String detailJsonData = mapper.writeValueAsString(detailData);
				jsonData = jsonData.substring(0,jsonData.length()-1) + ",DETAIL:" + detailJsonData + "}";
			}

			String code = null;
			code = mainData.get(xmlConfig.get("xd_main_codexmltag"));
			caller = xmlConfig.get("xd_caller");
			
			
			id = JdbcUtil.getIdBySeq("xmldatalog_seq");
			if(!("Packing".equals(caller)||"Invoice".equals(caller))){
				log = "insert into xmldatalog(xl_id,xl_data,xl_date,xl_caller,xl_code,xl_from,xl_filename) values ("
						+ id + ",'"+jsonData.replaceAll("'", "''")+"',sysdate,'"+caller
						+ "','"+code+"','download','"+fileName+"')";
				sqls.add(log);				
			}else{
				//由于packing和invoice回复文件可能字段串过长，暂不存入xl_data
				log = "insert into xmldatalog(xl_id,xl_data,xl_date,xl_caller,xl_code,xl_from,xl_filename) values ("
						+ id + ",null,sysdate,'"+caller
						+ "','"+code+"','download','"+fileName+"')";
				sqls.add(log);					
			}

			if(!("Packing".equals(caller)||"Invoice".equals(caller))){
				//common interceptors 主要用于自动过账
				rs = statement.executeQuery("select id from interceptors where caller='"+caller+"' "
						+ "and class_='com.uas.erp.service.common.impl.CommonHandler' "
						+ "and method='exec_handler' and type='xmltrans' and enable=1");
				while(rs.next()){
					sqls.add("declare out varchar2(500);begin SP_COMMONHANDLER("+id+"," + rs.getString("id") + ",out);end;");
				}	
			}else {
				sqls.add("declare out varchar2(500);begin SP_XMLTEMPPARSE("+id+",'" + caller + "',out);end;");
			}
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			return sqls;
		}
	}
	
	/*
	 * 获取明细表更新sql
	 */
	public static String getUpdateDetailSql(Map<String, String> data,Map<String,String> xmlConfig,List<Map<String,String>> detailXmlConfig){
		StringBuffer sb = new StringBuffer();
		String sql = null;
		String condition = null;
		String resSql = null;
		sql = "update " + xmlConfig.get("xd_detailtable") + " set ";
		for(Map<String,String> map:detailXmlConfig){
			if ("D".equals(map.get("fieldtype"))) {
				sb.append("," + map.get("uasfield") + "=to_date('" + data.get(map.get("xmltag"))+ "'," + "'YYYY-MM-DD HH24:mi:ss'" + ")");
			} else{
				sb.append("," + map.get("uasfield") + "='" + data.get(map.get("xmltag")) + "'");
			}
		}
		condition = getUpdateDetailCondition(data,xmlConfig);
		resSql = sql + sb.substring(1,sb.length()) + " where " + condition;
		return resSql;
	}
	
	/*
	 * 获取添加临时表数据sql
	 */
	public static String getInsertDetailSql(String maincode,Map<String, String> data,Map<String,String> xmlConfig,List<Map<String,String>> detailXmlConfig){
		StringBuffer sb = new StringBuffer();
		StringBuffer sb2 =new StringBuffer();
		String sql = null;
		String resSql = null;
		String caller=xmlConfig.get("xd_caller");
			sql = "insert into "+xmlConfig.get("xd_detailtable")+" (";
			for(Map<String,String> map:detailXmlConfig){
				sb.append("," + map.get("uasfield"));
			}
			if("Packing".equals(caller)){
				sb.append(",pdt_piid,pdt_id,pdt_status,pdt_indate) values (");
			}else if ("Invoice".equals(caller)){
				sb.append(",idt_inid,idt_id,idt_status,idt_indate) values (");
			}
			sql=sql+sb.substring(1,sb.length());
			for(Map<String,String> map:detailXmlConfig){
				if ("D".equals(map.get("fieldtype"))) {
					sb2.append("," + "=to_date('" + data.get(map.get("xmltag"))+ "'," + "'YYYY-MM-DD HH24:mi:ss'" + ")");
				} else if("N".equals(map.get("fieldtype"))){
					sb2.append("," + data.get(map.get("xmltag")));
				}	else{
					if (data.get(map.get("xmltag"))==null){
						data.put(map.get("xmltag")," ");
					}
					sb2.append(",'" + data.get(map.get("xmltag")) + "'");
				}
			}
			sb2.append(",(select "+xmlConfig.get("xd_keyfield")+" from "+xmlConfig.get("xd_table")
					+" where "+xmlConfig.get("xd_main_conditionfield")+"='"+maincode+"')");
			sb2.append(","+xmlConfig.get("xd_detailtable")+"_seq.nextval,'UNPARSE',sysdate)");
			resSql=sql+sb2.substring(1,sb2.length());
		return resSql;
	}
	
	/*
	 * 获取明细表和主表关联的更新条件
	 */
	public static String getUpdateDetailConditionByMainTable(Map<String, String> mainData,Map<String,String> xmlConfig){
		String mainConditionField = xmlConfig.get("xd_main_conditionfield");
		String mainConditionTag = xmlConfig.get("xd_main_conditiontag");
		String[] fields = mainConditionField.split(",");
		String[] tags = mainConditionTag.split(",");
		StringBuffer sql = new StringBuffer();
		StringBuffer sb = new StringBuffer();
		sql.append(xmlConfig.get("xd_detail_foreignkey") + "=(select "+xmlConfig.get("xd_keyfield")+" from "+xmlConfig.get("xd_table")+" where ");
		for(int i=0;i<fields.length;i++){
			if(mainData.get(tags[i])!=null){
				if(sb.length()>0){
					sb.append(" and " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}else{
					sb.append(" " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}				
			}
		}
		sql.append(sb + ")");
		return sql.toString();
	}
	
	/*
	 * 获取更新主表字段的sql
	 */
	public static String getUpdateMainFields(Map<String, String> mainData,Map<String,String> xmlConfig,List<Map<String,String>> mainXmlConfig){
		StringBuffer sb = new StringBuffer();
		String update = "update " + xmlConfig.get("xd_table") + " set ";
		for(Map<String,String> map:mainXmlConfig){
			if ("D".equals(map.get("fieldtype"))) {
				sb.append("," + map.get("uasfield") + "=to_date('" + mainData.get(map.get("xmltag"))+ "'," + "'YYYY-MM-DD HH24:mi:ss'" + ")");
			} else{
				sb.append("," + map.get("uasfield") + "='" + mainData.get(map.get("xmltag")) + "'");
			}
		}
		return update + sb.substring(1);
	}	
	
	/*
	 * 获取主表更新条件
	 */
	public static String getUpdateMainCondtion(Map<String, String> mainData,Map<String,String> xmlConfig){
		String mainConditionField = xmlConfig.get("xd_main_conditionfield");
		String mainConditionTag = xmlConfig.get("xd_main_conditiontag");
		String[] fields = mainConditionField.split(",");
		String[] tags = mainConditionTag.split(",");
		StringBuffer sb = new StringBuffer();
		for(int i=0;i<fields.length;i++){
			if(mainData.get(tags[i])!=null){
				if(sb.length()>0){
					sb.append(" and " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}else{
					sb.append(" " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}				
			}
		}
		return sb.toString();
	}
	
	/*
	 * 获取更新明细表的条件
	 */
	public static String getUpdateDetailCondition(Map<String, String> mainData,Map<String,String> xmlConfig){
		String mainConditionField = xmlConfig.get("xd_detail_conditionfield");
		String mainConditionTag = xmlConfig.get("xd_detail_conditiontag");
		String[] fields = mainConditionField.split(",");
		String[] tags = mainConditionTag.split(",");
		StringBuffer sb = new StringBuffer();
		for(int i=0;i<fields.length;i++){
			if(mainData.get(tags[i])!=null){
				if(sb.length()>0){
					sb.append(" and " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}else{
					sb.append(" " + fields[i] + "='" + mainData.get(tags[i]) + "'");
				}				
			}
		}
		return sb.toString();
	}
	
	/*
	 * 获取xmldataconfigdetail配置
	 */
	public static List<Map<String,String>> getXmlConfigDetail(Statement statement,String id,String type,String conditiontag){
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		
		ResultSet rs = null;
		try{
			rs = statement.executeQuery("select * from xmldataconfigdetail where nvl(uasfield,' ')<>' ' and isdetail="+type+" and xdid=" + id + " and xmltag not in ('" + conditiontag.replaceAll(",", "','") + "')");
			while(rs.next()){
				Map<String,String> map = new HashMap<String,String>();
				map.put("xmltag", rs.getString("xmltag"));
				map.put("uasfield", rs.getString("uasfield"));
				map.put("fieldtype", rs.getString("fieldtype"));
				list.add(map);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;		
	}
	
	/*
	 * 获取xmldataconfigdetail配置
	 */
	public static List<Map<String,String>> getXmlConfigDetail(Statement statement,String id,String type){
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		
		ResultSet rs = null;
		try{
			rs = statement.executeQuery("select * from xmldataconfigdetail where nvl(uasfield,' ')<>' ' and isdetail="+type+" and xdid=" + id);
			while(rs.next()){
				Map<String,String> map = new HashMap<String,String>();
				map.put("xmltag", rs.getString("xmltag"));
				map.put("uasfield", rs.getString("uasfield"));
				map.put("fieldtype", rs.getString("fieldtype"));
				list.add(map);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;		
	}
	
	/*
	 * 获取xmldataconfig配置
	 */
	public static Map<String,String> getXmlConfig(Statement statement,String docType,String filePrefix){
		Map<String,String> map = new HashMap<String,String>();
		ResultSet rs = null;
		try{
			if(docType==null){
				rs = statement.executeQuery("select * from xmldataconfig  where xd_fileprefix='"+ filePrefix + "' and xd_type='DOWNLOAD'");
			}else{
				rs = statement.executeQuery("select * from xmldataconfig  where xd_doctype='"+ docType + "' and xd_type='DOWNLOAD' and xd_fileprefix='"+ filePrefix + "'");
			}
			if(rs.next()){
				map.put("xd_id", rs.getString("xd_id"));
				map.put("xd_table", rs.getString("xd_table"));
				map.put("xd_detailtable", rs.getString("xd_detailtable"));
				map.put("xd_caller", rs.getString("xd_caller"));
				map.put("xd_keyfield", rs.getString("xd_keyfield"));
				map.put("xd_main_conditiontag", rs.getString("xd_main_conditiontag"));
				map.put("xd_main_conditionfield", rs.getString("xd_main_conditionfield"));
				map.put("xd_detail_conditiontag", rs.getString("xd_detail_conditiontag"));
				map.put("xd_detail_conditionfield", rs.getString("xd_detail_conditionfield"));
				map.put("xd_detail_foreignkey", rs.getString("xd_detail_foreignkey"));
				map.put("xd_main_codexmltag",rs.getString("xd_main_codexmltag"));
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return map;
	}
	
	/*
	 * 获取根节点
	 */
	public static Element getElementRoot(File file){
        Element element = null;
		try {
			InputStream in = new FileInputStream(file);
            SAXReader reader = new SAXReader();
            Document doc = reader.read(in);
            in.close();
            element = doc.getRootElement();
        } catch (Exception e) {
			e.printStackTrace();
		}
        return element;
	}
	

	/*
	 * 获取主表数据
	 */
	@SuppressWarnings("unchecked")
	private static Map<String, String> getMainData(Element root) {
		Map<String, String> data=new HashMap<String,String>();
	    List<Element> e = root.element("Header").elements();
		for(Element el:e){
			data.put(el.getName(),el.getData()==null?"":el.getData().toString());
    	}
		return data;
	}
	
	/*
	 * 获取明细表数据
	 */
	@SuppressWarnings("unchecked")
	private static List<Map<String, String>> getDetailData(Element root) {
		List<Map<String, String>> data=new ArrayList<Map<String,String>>();
	    List<Element> items  = root.element("Detail").elements("Item");
		for(Element el:items){
			Map<String, String> detail = new HashMap<String,String>();
			List<Element> e= el.elements();
			for(Element i:e){
				detail.put(i.getName(),i.getData()==null?"":i.getData().toString());
			}
			data.add(detail);
    	}
		return data;
	}			
    public static void downloadRun(){
		//联合创泰-富森
		downloadByDepotAndSob("N_YITOA_LHCT-FS","N_YITOA_LHCT","聯合創泰科技有限公司");
		//联合创泰-顺丰
		downloadByDepotAndSob("N_YITOA_LHCT-SF","N_YITOA_LHCT","聯合創泰科技有限公司");
		//联合创泰-准时达
		downloadByDepotAndSob("N_YITOA_LHCT-ZSD","N_YITOA_LHCT","聯合創泰科技有限公司");
		//联合创泰-SLC上海保税仓
        downloadByDepotAndSob("N_YITOA_LHCT-SLC","N_YITOA_LHCT","聯合創泰科技有限公司");
		//联合创泰-中芯
		//downloadByDepotAndSob("N_YITOA_LHCT-ZX","N_YITOA_LHCT","聯合創泰科技有限公司");
		//联合创泰-泓明
		downloadByDepotAndSob("N_YITOA_LHCT-HM","N_YITOA_LHCT","聯合創泰科技有限公司");
		//英唐创泰香港-富森
		downloadByDepotAndSob("N_YITOA_LHCT_HK-FS","N_YITOA_LHCT_HK","英唐創泰香港科技有限公司");


	}

	@SuppressWarnings("unchecked")
	public static void downloadByDepotAndSob(String depot,String sob,String enterprise){
		BaseUtil.getLogger().info("download xml" + " from "+depot);
		FTPClient client = null;
		SFTPUtil sftp = null;
		List<String> sqls = new ArrayList<String>();
		boolean executeRes = false;
		Connection connect = null;

		try {
			Map<String,Object> servMap = JdbcUtil.getFtpConfigs();
			Map<String,Object> ftpMap = (Map<String,Object>)servMap.get(depot);
			if ("N_YITOA_LHCT-SF".equals(depot)||"N_YITOA_LHCT-ZX".equals(depot)){
				sftp = new SFTPUtil(ftpMap.get("user").toString(),ftpMap.get("password").toString(),
						ftpMap.get("ip").toString(),
						Integer.parseInt(ftpMap.get("port").toString()));
				try {
					sftp.login();
				} catch (JSchException e) {
					e.printStackTrace();
				}
			}else {
				client = FtpUtil.connect(ftpMap);
			}
			if(client!=null||sftp!=null) {
				List<File> files = null;
				if ("N_YITOA_LHCT-SF".equals(depot)||"N_YITOA_LHCT-ZX".equals(depot)) {
					files = sftp.download(ftpMap.get("downloadpath").toString());

				}else if("N_YITOA_LHCT-SLC".equals(depot)){
					files = FtpUtil.downloadDirFileByType(client, "XML",
							ftpMap.get("downloadpath").toString());
				}else{
					files = FtpUtil.downloadAllFileByType(client, "XML");
				}
				String fileName = "";
				if (files != null && files.size() > 0) {
					connect = JdbcUtil.getConnectBySob(sob);
					Statement statement = connect.createStatement();
					if (connect != null) {
						for (File file : files) {
							try {
								fileName += "," + file.getName();
								//判断当前帐套和文件是否一致
								if (!checkXmlFileToSqlToEnterprise(file, enterprise)) {
									continue;
								}
								sqls = parseXmlFileToSqls(file, statement);
								if (sqls == null) {
									continue;
								}

								executeRes = JdbcUtil.executeSqls(connect, sqls);

								if (executeRes) {
									if ("N_YITOA_LHCT-SF".equals(depot)) {
										String s = file.getName().substring(0, file.getName().indexOf(".")) + ".xml";
										Boolean rename = sftp.rename(ftpMap.get("downloadpath").toString() + "/" + s, "goodsreceipt/backup/" + s);
									}else if("N_YITOA_LHCT-ZX".equals(depot)){
										String s = file.getName().substring(0, file.getName().indexOf(".")) + ".xml";
										Boolean rename = sftp.rename(ftpMap.get("downloadpath").toString() + "/" + s,ftpMap.get("downloadpath").toString() + "/bak/" + s);
									} else {
										//移动处理完的文件到bak文件夹下
										client.rename(file.getName(), "bak/" + file.getName());
									}
								}
								statement.close();
							} catch (Exception e) {
								e.printStackTrace();
								BaseUtil.getLogger().error(e.toString());
								continue;
							}
						}

					}
					if (!"".equals(fileName)) {
						BaseUtil.logDownload(fileName.substring(1), ftpMap.get("ip").toString(), ftpMap.get("downloadpath").toString());
					}
				}
			}
		} catch (Exception e) {
			BaseUtil.getLogger().error(e.toString());
			e.printStackTrace();
		}finally{
			if(client!=null){
				FtpUtil.closeFtpClient(client);
				client = null;
			}
			if (sftp!=null){
				sftp.logout();
			}
			//把当前的连接关闭
			try {
				if(connect!=null){
					connect.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				connect = null;
			}
		}
	}
 }