package com.cim.idm.controller;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.cim.idm.framework.template.workflow.BpelExecuter;
import com.cim.idm.model.ErpMessageLog;
import com.cim.idm.service.impl.ToSAPServiceImpl;
import com.cim.idm.utils.AjaxResult;
import com.cim.idm.utils.EventInfoUtil;
import com.cim.idm.wmsextend.generic.GenericServiceProxy;
import com.cim.idm.wmsextend.generic.errorHandler.CustomException;
import com.cim.idm.wmsextend.generic.util.ConvertUtil;
import org.apache.commons.lang3.StringUtils;
import org.jdom.Document;
import org.jdom.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.cim.idm.wmspackage.materialpacking.MaterialPackingServiceProxy;
import com.cim.idm.wmspackage.materialpacking.management.data.MaterialPackingKey;
import com.cim.idm.wmspackage.materialpacking.management.info.SetEventInfo;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import cn.hutool.core.util.XmlUtil;

import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.data.EventInfo;
import com.cim.idm.framework.util.time.TimeStampUtil;

@RestController
@RequestMapping("/api/wms")
@EnableAutoConfiguration
public class QMSController {

	public static final String 							BPELName_Tag = "bpelname";
	public static final String 							Message_Tag = "Message";
	public static final String 							MessageName_Tag = "MESSAGENAME";
	public static final String 							Header_Tag = "Header";
	public static final String 							Body_Tag = "Body";
	public static final String 							Result_Name_Tag				= "Return";	        
	public static final String							Result_ReturnCode			= "RETURNCODE";
	public static final String							Result_ErrorMessage			= "RETURNMESSAGE";
	public static final String							dataField_TAG = "Message";

	private static final String 						ReplySubjectName_Tag = "ORIGINALSOURCESUBJECTNAME";

	@Autowired(required = false)
	private BpelExecuter bpelExecuter;

	@Autowired
	private ToSAPServiceImpl toSAPService;

	private static final Logger log = LoggerFactory.getLogger(QMSController.class);
	
//	private NCWServiceImpl NCWServiceImpl = new NCWServiceImpl ();

	@RequestMapping(value = "/iqcSyncdata", method = RequestMethod.POST)
	public AjaxResult SyncIQCData(@RequestBody JSONObject in) {
		log.debug("***********************************");
		log.debug("***********************************");
		log.debug(in.toString());
		log.debug("***********************************");
		log.debug("***********************************");
		AjaxResult result = new AjaxResult();
		ErpMessageLog loginfo = new ErpMessageLog();
		loginfo.setServerName("QMSToWms");
		loginfo.setMessageId("");

		loginfo.setEventUser("IQC");
		loginfo.setId(UUID.randomUUID().toString());
		loginfo.setInterfaceTime(ConvertUtil.getCurrTime("yyyy-MM-dd HH:mm:ss"));
		loginfo.setSendMsg(in.toJSONString());
		loginfo.setEventName("iqcSyncdata");


		SetEventInfo setEventInfo = new SetEventInfo();
		EventInfo eventInfo = new EventInfo();
		eventInfo.setEventName("iqcSyncdata");
		eventInfo.setEventUser("IQC");


		//0待检验、1合格、2不合格、3检验中
		String deliveryNumber = in.get("deliveryNumber").toString();
		String materialCode = in.get("materialCode").toString();
		String iqcResult = in.get("iqcResult").toString();
		String iqcResultDate = in.get("iqcResultDate").toString();
		Map<String, Object> bindMap = new HashMap<String, Object>();
		if("1".equals(iqcResult))
		{
			iqcResult="OK";
			bindMap.put("oqaResultState", GenericServiceProxy.getConstantMap().OQA_State_End);
		} else if ("0".equals(iqcResult)) {
			iqcResult = "";
			bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);	
		} else if ("2".equals(iqcResult)) {
			iqcResult = "NG";
			bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);	
		} else if ("3".equals(iqcResult)) {
			iqcResult = "";
			bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);	
		}
		bindMap.put("oqaResult",iqcResult);
		bindMap.put("packingGrade", iqcResult);
		setEventInfo.setUserColumns(bindMap);
		eventInfo.setEventTime(Timestamp.valueOf(iqcResultDate));
		String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
				+ "WHERE M.RECEIVEACTNO = :RECEIVEACTNO AND M.MATERIALSPECNAME  = :MATERIALSPECNAME";
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("RECEIVEACTNO", deliveryNumber);
		hashMap.put("MATERIALSPECNAME", materialCode);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
		if (queryForList == null || queryForList.size() < 1) {
			result = AjaxResult.me().setSuccess(false).setMessage("未找到" + deliveryNumber + "的待检信息");
			return result;
		}
		for (int i = 0; i < queryForList.size(); i++) {
			MaterialPackingKey materialPackingKey = new MaterialPackingKey(queryForList.get(i).get("SITENAME").toString(),
					queryForList.get(i).get("MATERIALPACKINGNAME").toString());
			MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, eventInfo, setEventInfo);
		}


		result = AjaxResult.me().setSuccess(true).setMessage("执行成功");
		loginfo.setResultCode("success");
		loginfo.setReturnMsg(JSONObject.toJSONString(result));
		// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
		saveMessageLog(loginfo);
		return result;
	}



	@RequestMapping(value = "/pqcSyncdata", method = RequestMethod.POST)
	public AjaxResult pqcSyncdata(@RequestBody JSONArray in) {
		
		log.debug("***********************************");
		log.debug("***********************************");
		log.debug(in.toString());
		log.debug("***********************************");
		log.debug("***********************************");
		AjaxResult result = new AjaxResult();
		ErpMessageLog loginfo = new ErpMessageLog();
		loginfo.setServerName("QMSToWms");
		loginfo.setMessageId("");

		loginfo.setEventUser("IQC");
		loginfo.setId(UUID.randomUUID().toString());
		loginfo.setInterfaceTime(ConvertUtil.getCurrTime("yyyy-MM-dd HH:mm:ss"));
		loginfo.setSendMsg(in.toJSONString());
		loginfo.setEventName("pqcSyncdata");


		SetEventInfo setEventInfo = new SetEventInfo();
		EventInfo eventInfo = new EventInfo();
		eventInfo.setEventName("iqcSyncdata");
		eventInfo.setEventUser("pQC");
		//获取到货单和物料编码
		//		    {
		//		        "deliveryNumber": "SDK20240511003",
		//		        "materialCode": "2F08S4J4",
		//		        "iqcResult": "0", //  0 : 不合格  ;  1:合格  ;  2：待检
		//		        "iqcResultDate": "2024-1-1 12:2:2" // YYYY-MM-DD HH24:MI:SS
		//		    }

		//	    {
		//        "deliveryNumber": "SDK20240511003",  
		//        "materialCode": "2F08S4J4",
		//        "iqcResult": "0", //  0 : 不合格  ；  1:合格  ;  2：待检
		//        "iqcResultDate": "2024-1-1 12:2:2" // YYYY-MM-DD HH24:MI:SS
		//    }
		if (1 == 1) {
			result = AjaxResult.me().setSuccess(true).setMessage("执行成功");
			loginfo.setResultCode("success");
			loginfo.setReturnMsg(JSONObject.toJSONString(result));
			// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
			saveMessageLog(loginfo);
			return result;
		}
		Map<String, Object> bindMap = new HashMap<String, Object>();
		List<MaterialPackingKey> mpList = new ArrayList<> ();
		int success = 0;
		String errorCode = "";
		for (int i = 0; i < in.size(); i++) {

			JSONObject jb = in.getJSONObject(i);
			String batchCode = jb.get("batchCode").toString();
			String materialCode = jb.get("materialCode").toString();
			String pqcResult = jb.get("pqcResult").toString();
			String pqcResultDate = jb.get("pqcResultDate").toString();
			String user = jb.get("user").toString();
			//0待检验、1合格、2不合格、3检验中
			if("1".equals(pqcResult))
			{
				pqcResult="OK";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);	
			} else if ("0".equals(pqcResult)) {
				pqcResult = "";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);	
			} else if ("2".equals(pqcResult)) {
				pqcResult = "NG";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);	
			} else if ("3".equals(pqcResult)) {
				pqcResult = "";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);	
			}
			bindMap.put("oqaResult",pqcResult);
			bindMap.put("packingGrade", pqcResult);
			setEventInfo.setUserColumns(bindMap);
			eventInfo.setEventTime(Timestamp.valueOf(pqcResultDate));
			eventInfo.setEventUser(user);
			String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
					+ "WHERE M.CHARGE = :BATCH AND M.MATERIALSPECNAME  = :MATERIALSPECNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("BATCH", batchCode);
			hashMap.put("MATERIALSPECNAME", materialCode);
			List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
//				result = AjaxResult.me().setResultObj(batchCode + "," + materialCode).setSuccess(false).setCode(500).setMessage("未找到待检信息");
//				return result;
				errorCode += batchCode + "," + materialCode + ";";
				
			} else {
				mpList.add(new MaterialPackingKey(queryForList.get(i).get("SITENAME").toString(),
						queryForList.get(i).get("MATERIALPACKINGNAME").toString()));
				success ++;
			}
		}
		for (MaterialPackingKey mp :mpList) {
			MaterialPackingKey materialPackingKey = mp;
			MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, eventInfo, setEventInfo);
		}
		if (success < in.size()) {
			int error = in.size() - success;
			result = AjaxResult.me().setResultObj(errorCode).setSuccess(false).setMessage("未找到待检信息" + error + "条");
			return result;
		}
		result = AjaxResult.me().setSuccess(true).setMessage("执行成功");
		loginfo.setResultCode("success");
		loginfo.setReturnMsg(JSONObject.toJSONString(result));
		// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
		saveMessageLog(loginfo);
		return result;
	}


	
//	@Transactional(isolation= Isolation.READ_COMMITTED)
	@Transactional
	@RequestMapping(value = "/pqcSyncdata2", method = RequestMethod.POST)
	public AjaxResult pqcSyncdata2(@RequestBody JSONArray in) throws CustomException {
		
		AjaxResult result = new AjaxResult();
		ErpMessageLog loginfo = new ErpMessageLog();
		loginfo.setServerName("QMSToWms");
		loginfo.setMessageId("");

		loginfo.setEventUser("IQC");
		loginfo.setId(UUID.randomUUID().toString());
		loginfo.setInterfaceTime(ConvertUtil.getCurrTime("yyyy-MM-dd HH:mm:ss"));
		loginfo.setSendMsg(in.toJSONString());
		loginfo.setEventName("pqcSyncdata2");
		String siteName = "SDK";

		SetEventInfo setEventInfo = new SetEventInfo();
		EventInfo makeEventInfo = new EventInfoUtil().makeEventInfo("iqcSyncdata", "", "iqcSyncdata");
		EventInfo eventInfo = new EventInfo();
		eventInfo.setEventName("iqcSyncdata");
		eventInfo.setEventUser("pQC");
		//获取到货单和物料编码
//		{
//	        "commonNumber": "123456",
//	        "materialCode": null,
//	        "judgementResult": "1",
//	        "resultTime": "2024-06-11 11:24:41",
//	        "inspector": "管理员",
//	        "exceptionHandling": null,
//	        "specialState": null,
//	        "transferStatus": null,
//	        "tpType": "0"
//	    }
		Map<String, Object> bindMap = new HashMap<String, Object>();
		List<MaterialPackingKey> mpList = new ArrayList<> ();
		int success = 0;
		String errorCode = "";
		boolean ngFlag=false;
		for (int i = 0; i < in.size(); i++) {

			JSONObject jb = in.getJSONObject(i);
//			String commonNumber = jb.get("commonNumber").toString();//通用单号
			String commonNumber = jb.get("commonNumber")== null  ? "" : jb.get("commonNumber").toString();//通用单号
			String materialCode = jb.get("materialCode")== null  ? "" : jb.get("materialCode").toString();//料号
			String pqcResult = jb.get("judgementResult").toString();//检验结果
			String pqcResultDate = jb.get("resultTime").toString();//检验时间
			String user = jb.get("inspector").toString();//检验员
			makeEventInfo = new EventInfoUtil().makeEventInfo("iqcSyncdata", user, "iqcSyncdata");
			String exceptionHandling = jb.get("exceptionHandling") == null  ? "" : jb.get("exceptionHandling").toString();//异常处理 （0复卷，1降级，2报废）
			String specialState = jb.get("specialState") == null  ? "" : jb.get("specialState").toString();//特殊状态 （0特采、1紧急放行、2退供应商）
			String transferStatus = jb.get("transferStatus") == null  ? "" : jb.get("transferStatus").toString();//异常处理
//			0：来料
//			1：5,7,9,10#涂布成品
//			2: 3,4#涂布成品
//			3: 销售退货
//			4：品质放行/特采
//			5：过期检验
//			6：胶水出货"

//			到货单
//			批次号+料号
//			批次号+料号
//			批次号+料号
//			批次号+料号
//			投放计划单

			String tpType = jb.get("tpType").toString();//检验类型
			//0不合格、1合格
			if("1".equals(pqcResult))
			{
				pqcResult="OK";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);	
			}else if ("0".equals(pqcResult)) {
				pqcResult = "NG";
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);	
				ngFlag=true;
			} 
			bindMap.put("oqaResult",pqcResult);
			bindMap.put("packingGrade", pqcResult);
			bindMap.put("packingState", "Released");
			bindMap.put("exceptionHandling",exceptionHandling);
			bindMap.put("specialState", specialState);
			setEventInfo.setUserColumns(bindMap);
			//eventInfo.setEventTime(Timestamp.valueOf(pqcResultDate));
			eventInfo.setEventUser(user);
			List<String> sapBoxList = new ArrayList<> ();
			if ("0".equals(tpType)) {//采购到货单, 领料退库，产成品入库
				
				// 获取JSON数组
				JSONArray jsonArray = jb.getJSONArray("lotList"); // arrayName为JSON数组的键名
				if (!java.util.Objects.isNull(jsonArray)) {				
					for (int j = 0; j < jsonArray.size(); j++) {
					    String charge = jsonArray.getString(j);				
						String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
								+ "WHERE M.CHARGE  = :CHARGE  AND  M.MATERIALSPECNAME =:MATERIALSPECNAME AND M.STOCKSTATE = 'Stocked'";
						Map<String, Object> hashMap = new HashMap<String,Object> ();
						hashMap.put("CHARGE", charge);
						hashMap.put("MATERIALSPECNAME", materialCode);
						List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
						if (queryForList == null || queryForList.size() < 1) {
							errorCode += charge + "," + materialCode + ";";
						} else {							
							mpList.add(new MaterialPackingKey(queryForList.get(0).get("SITENAME").toString(),
									queryForList.get(0).get("MATERIALPACKINGNAME").toString()));
//							MaterialPackingServiceProxy.getMaterialPackingService().setEvent(new MaterialPackingKey(queryForList.get(j).get("SITENAME").toString(),
//									queryForList.get(j).get("MATERIALPACKINGNAME").toString()), makeEventInfo, setEventInfo);
							sapBoxList.add( queryForList.get(0).get("MATERIALPACKINGNAME").toString());
							success ++;
						}					    
					}
											
				} else if (!StringUtils.isEmpty(commonNumber)) {
					String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
							+ "WHERE M.MESSAGEID  = :RECEIVEREQUESTNAME";
					Map<String, Object> hashMap = new HashMap<String,Object> ();
					hashMap.put("RECEIVEREQUESTNAME", commonNumber);
					List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
					if (queryForList == null || queryForList.size() < 1) {
						errorCode += commonNumber + "," + materialCode +";";
					} else {
						for (int j = 0; j < queryForList.size(); j++) {
							mpList.add(new MaterialPackingKey(queryForList.get(j).get("SITENAME").toString(),
									queryForList.get(j).get("MATERIALPACKINGNAME").toString()));
//							MaterialPackingServiceProxy.getMaterialPackingService().setEvent(new MaterialPackingKey(queryForList.get(j).get("SITENAME").toString(),
//									queryForList.get(j).get("MATERIALPACKINGNAME").toString()), makeEventInfo, setEventInfo);
							sapBoxList.add(queryForList.get(j).get("MATERIALPACKINGNAME").toString());
							success ++;
						}
					}
				} else {	
					throw new CustomException("Common","无单据信息和批次信息");
				}

				// 将订单的质检结果传给SAP
				String undoId = toSAPService.iqcResultFeekback(sapBoxList,siteName,commonNumber,materialCode,pqcResult,pqcResultDate,user,
						exceptionHandling,specialState);

				if(mpList.size()>0)
				{
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(mpList, eventInfo, setEventInfo);
				}
			}
			
			else if ("6".equals(tpType)) {//投放计划单
				String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
						+ "WHERE M.MESSAGEID  = :RECEIVEREQUESTNAME";
				Map<String, Object> hashMap = new HashMap<String,Object> ();
				hashMap.put("RECEIVEREQUESTNAME", commonNumber);
				List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
				if (queryForList == null || queryForList.size() < 1) {
					//查询中间表是否有数据 
					String sqlmes = "SELECT bms.LOTNAME FROM BS_MES_SHIPPED bms  WHERE bms.REQUESTNAME = :RECEIVEREQUESTNAME";
					List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlmes, hashMap);
					if  (queryForList2 == null || queryForList2.size() < 1) {
						errorCode += commonNumber + ";";
					} else {
						for (int j = 0; j < queryForList2.size(); j++) {
							String lotName = queryForList2.get(j).get("LOTNAME").toString();
							String update = "UPDATE BS_MES_SHIPPED SET PACKINGGRADE = :PACKINGGRADE,IQCFLAG = 'Y'  WHERE LOTNAME = :LOTNAME";
							hashMap.put("PACKINGGRADE", pqcResult);
							hashMap.put("LOTNAME", lotName);
							IDMFrameServiceProxy.getSqlTemplate().update(update, hashMap);
							success ++;
						}
					}
					
					
				} else {
					for (int j = 0; j < queryForList.size(); j++) {
						mpList.add(new MaterialPackingKey(queryForList.get(j).get("SITENAME").toString(),
								queryForList.get(j).get("MATERIALPACKINGNAME").toString()));
						success ++;
					}

					//查询中间表是否有数据
					String sqlmes = "SELECT bms.LOTNAME FROM BS_MES_SHIPPED bms  WHERE bms.REQUESTNAME = :RECEIVEREQUESTNAME";
					List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlmes, hashMap);
					if  (queryForList2 == null || queryForList2.size() < 1) {
						errorCode += commonNumber + ";";
					} else {
						for (int j = 0; j < queryForList2.size(); j++) {
							String lotName = queryForList2.get(j).get("LOTNAME").toString();
							String update = "UPDATE BS_MES_SHIPPED SET PACKINGGRADE = :PACKINGGRADE,IQCFLAG = 'Y'  WHERE LOTNAME = :LOTNAME";
							hashMap.put("PACKINGGRADE", pqcResult);
							hashMap.put("LOTNAME", lotName);
							IDMFrameServiceProxy.getSqlTemplate().update(update, hashMap);
							success ++;
						}
					}
				}
			} else if ("1".equals(tpType) || "2".equals(tpType) || "3".equals(tpType) || "4".equals(tpType) || "5".equals(tpType)) {//料号+批次
				String kcsql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M "
						+ "WHERE M.CHARGE = :BATCH AND M.MATERIALSPECNAME  = :MATERIALSPECNAME";
				Map<String, Object> hashMap = new HashMap<String,Object> ();
				hashMap.put("BATCH", commonNumber);
				hashMap.put("MATERIALSPECNAME", materialCode);
				List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(kcsql, hashMap);
				
				
				String drkSql = "SELECT * FROM BS_MES_SHIPPED BMS WHERE BMS.CHARGE = :BATCH AND BMS.PRODCUTSPECNAME = :MATERIALSPECNAME";
				
				List<Map<String, Object>> drkSqlList = IDMFrameServiceProxy.getSqlTemplate().queryForList(drkSql, hashMap);
				
				if (queryForList != null && queryForList.size() > 0) {
					for (int j = 0; j < queryForList.size(); j++) {
						mpList.add(new MaterialPackingKey(queryForList.get(j).get("SITENAME").toString(),
								queryForList.get(j).get("MATERIALPACKINGNAME").toString()));
						success ++;
					}
				} else if (drkSqlList != null && drkSqlList.size() > 0) {
					String update = "UPDATE BS_MES_SHIPPED SET PACKINGGRADE = :PACKINGGRADE,IQCFLAG = 'Y' WHERE CHARGE = :BATCH  AND PRODCUTSPECNAME = :MATERIALSPECNAME";
					hashMap.put("PACKINGGRADE", pqcResult);
					IDMFrameServiceProxy.getSqlTemplate().update(update, hashMap);
					success ++;
				} else {
					errorCode += commonNumber + "," + materialCode + ";";
				}
			} else {
				result = AjaxResult.me().setResultObj(errorCode).setSuccess(false).setMessage("检验类型不存在" + tpType);
				loginfo.setResultCode("E");
				loginfo.setReturnMsg(JSONObject.toJSONString(result));
				// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
				saveMessageLog(loginfo);
				return result;
			}
		}
		//获取转库对应关系
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		String chargeString="";
		for (MaterialPackingKey mp :mpList) {
			MaterialPackingKey materialPackingKey = mp;
			chargeString+=(mp.getMaterialPackingName()+",");
			MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
		}
		if(ngFlag) 
		{
			//QMS判NG消息推送钉钉
			String message = "【通知预警】 "+"批次："+chargeString+"在QMS已判NG，请确认" ;
			String webhookUrl="https://oapi.dingtalk.com/robot/send?access_token=5bb36ab9bec4a92ba4e4ab11a21918346b65c7368ee7777e4dd407d4b169a2c2";
			try 
			{			
//				DingTalkWebhookNotifier.sendTextMessage(webhookUrl, message);
			}
			catch (Exception e) {
	              e.printStackTrace();
	      }
		}
		if (success < in.size()) {
			int error = in.size() - success;
			result = AjaxResult.me().setResultObj(errorCode).setSuccess(false).setMessage("未找到待检信息" + error + "条");
			loginfo.setResultCode("E");
			loginfo.setReturnMsg(JSONObject.toJSONString(result));
			// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
			saveMessageLog(loginfo);
			return result;
		}
		result = AjaxResult.me().setSuccess(true).setMessage("执行成功");
		loginfo.setResultCode("success");
		loginfo.setReturnMsg(JSONObject.toJSONString(result));
		// 消息保存日志，erp推送wms的数据写入表BS_ERPMESSAGELOG中
		saveMessageLog(loginfo);
		
		//根据品质状态自动转库
		try {
			autoChangeERPlocation(in, makeEventInfo,mpList);
		} catch (Exception e) {
			e.printStackTrace();
			throw new CustomException(e.toString());
		}
		return result;
	}

	@RequestMapping(value = "/QMSSyncData", method = RequestMethod.POST)
	public AjaxResult QMSSyncData(@RequestBody JSONArray in) throws CustomException {
		//重写QMS回传通用处理方法。
		AjaxResult result = new AjaxResult();
		ErpMessageLog loginfo = new ErpMessageLog();
		loginfo.setServerName("QMSToWms");
		loginfo.setMessageId("");

		loginfo.setEventUser("QMS");
		loginfo.setId(TimeStampUtil.getCurrentEventTimeKey());
		loginfo.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
		loginfo.setSendMsg(in.toJSONString());
		loginfo.setEventName("QMSSyncData");
		//String siteName = System.getProperty("company","SDK");
		String siteName = "SDK";
		saveMessageLog(loginfo);

		SetEventInfo setEventInfo = new SetEventInfo();
		EventInfo makeEventInfo = EventInfoUtil.makeEventInfo("QMSSyncData", "", "QMSSyncData");
		EventInfo eventInfo = new EventInfo();
		eventInfo.setEventName("QMSSyncData");
		//eventInfo.setEventUser("pQC");
		//QMS回传json
//		[{
//	        "materialName": "品名",
//	        "materialCode": "123",
//	        "judgementResult": "1",
//	        "resultTime": "2024-06-11 11:24:41",
//	        "inspector": "管理员",
//	        "specialTreatment": "0",
//	        "lot": "lot1,lot2,lot3",
//	        "inspectionScenario": "0"
//	    },
//		{
//	        "materialName": "品名",
//	        "materialCode": "123",
//	        "judgementResult": "1",
//	        "resultTime": "2024-06-11 11:24:41",
//	        "inspector": "管理员",
//	        "specialTreatment": "0",
//	        "lot": "lot1,lot2,lot3",
//			"inspectionScenario": "0"
//	    }]
		Map<String, Object> bindMap = new HashMap<String, Object>();
		List<MaterialPackingKey> mpList = new ArrayList<> ();
		int success = 0;
		String errorCode = "";
		List<String> boxList=new ArrayList<>();
		List<String> expBoxList=new ArrayList<>(); // 如果存在延期时处理
		try {
			IDMFrameServiceProxy.getTransactionManager().beginTransaction();
			String inspectionScenario="";
			String user = "";
			String expiringDateType="";
			for (int i = 0; i < in.size(); i++) {

				JSONObject jb = in.getJSONObject(i);
				//String commonNumber = jb.get("commonNumber").toString();//通用单号
				inspectionScenario=jb.get("inspectionScenario").toString();//0：原材  1：产成品 2：PET成品以及分切 3：销售退货检结果 4：过期复判检验结果 5. 胶水出库检结果

				String materialCode = jb.get("materialCode")== null  ? "" : jb.get("materialCode").toString();//料号
				String pqcResult = jb.get("judgementResult")==null?"":jb.get("judgementResult").toString();//检验结果。0不合格 1合格
				if(StringUtils.equals("0", pqcResult))
				{
					pqcResult="NG";
				}
				else if(StringUtils.equals("1", pqcResult))
				{
					pqcResult="OK";
				}
				String pqcResultDate = jb.get("resultTime").toString();//检验时间
				user = jb.get("inspector").toString();//检验员
				String[] lots=jb.get("lot").toString().split(",");//批号List
				String expiringDate = jb.get("delayTo") == null  ? "" : jb.get("delayTo").toString();//延期时间
				// 如果存在延期时间
//				if(!expiringDate.isEmpty()) {
//					expiringDateType = "exp";
//				}
				makeEventInfo = EventInfoUtil.makeEventInfo("QMSSyncData", user, "QMSSyncData:"+inspectionScenario);
				String specialTreatment = jb.get("specialTreatment") == null  ? "" : jb.get("specialTreatment").toString();//异常处理 （0复卷、1降级、2报废、3特采、4紧急放行、5退货）
				for(int j=0;j<lots.length;j++)
				{
					boxList.add(lots[j]);

					// 如果存在延期时间
					if(!expiringDate.isEmpty()) {
						expBoxList.add(lots[j]);
					}

					//先将数据都存储至临时表
					insertQMSTemp(lots[j], pqcResult, specialTreatment, materialCode, inspectionScenario,pqcResultDate,expiringDate);
				}
			}
			//判断数据是否需要发送SAP
			String undoId = toSAPService.qmsResultFeekback(siteName,user,boxList);
			//更新Box状态
			List<MaterialPackingKey> mpkList=updateOqaResult(boxList,siteName,inspectionScenario,expiringDateType, expBoxList);
			//记录履历
			if(mpkList.size()>0)
			{
				setEventOqaResult(mpkList,"",siteName,makeEventInfo);
			}
			//删除临时表数据
			deleteQMSTemp(boxList);

			IDMFrameServiceProxy.getTransactionManager().commitTransaction();
			result = AjaxResult.me().setSuccess(true).setMessage("执行成功");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			IDMFrameServiceProxy.getTransactionManager().rollbackTransaction();
			result = AjaxResult.me().setSuccess(false).setMessage("处理异常:" + e.getMessage());
			return result;
		}


		return result;
	}

		//插入temp表
		public static void insertQMSTemp(String materialPackingName,String packingGrade,String specialState,String materialSpecName,String qmsType,String resultDate, String expiringDate) 
		{
			log.info("======insert MATERIALPACKINGQMSTEMP");
			String sql="INSERT INTO MATERIALPACKINGQMSTEMP(MATERIALPACKINGNAME, PACKINGGRADE, SPECIALSTATE, MATERIALSPECNAME,QMSTYPE,RESULTDATE,EXPIRINGDATE)\r\n" + 
					" VALUES (:MATERIALPACKINGNAME, :PACKINGGRADE, :SPECIALSTATE, :MATERIALSPECNAME,:QMSTYPE,:RESULTDATE,:EXPIRINGDATE)";
			Map<String, Object> bp=new HashMap<>();
			bp.put("MATERIALPACKINGNAME", materialPackingName);
			bp.put("PACKINGGRADE", packingGrade);
			bp.put("SPECIALSTATE", specialState);
			bp.put("MATERIALSPECNAME", materialSpecName);
			bp.put("QMSTYPE", qmsType);
			bp.put("RESULTDATE", resultDate);
			bp.put("EXPIRINGDATE", expiringDate);
			IDMFrameServiceProxy.getSqlTemplate().update(sql, bp);
		}
		//删除Temp表
		public static void deleteQMSTemp(List<String> mpList) 
		{
			log.info("======delete MATERIALPACKINGQMSTEMP");
			String sql="DELETE FROM MATERIALPACKINGQMSTEMP WHERE MATERIALPACKINGNAME in (:MPLIST)" ;
			Map<String, Object> bp=new HashMap<>();
			bp.put("MPLIST", mpList);
			IDMFrameServiceProxy.getSqlTemplate().update(sql, bp);
		}
		
		//更新Box结果
		public static List<MaterialPackingKey> updateOqaResult(List<String> mpList,String siteName,String qmsType, String expiringDateType,List<String> expBoxList)
		{
			List<MaterialPackingKey> mpkList=new ArrayList<>();

			String sqls=" SELECT m.MATERIALPACKINGNAME,m.CHARGE FROM MATERIALPACKING m,MATERIALPACKINGQMSTEMP t\r\n" +
					" WHERE m.CHARGE in(:BOXLIST) AND m.STOCKSTATE ='Stocked'\r\n" + 
					" AND m.CHARGE =t.MATERIALPACKINGNAME  AND m.MATERIALSPECNAME = t.MATERIALSPECNAME AND   m.MATERIALQUANTITY <> 0 \r\n" ;
					//" AND NVL(m.PACKINGGRADE,'N') <>nvl(t.PACKINGGRADE,'N') ";
			Map<String, Object> bps=new HashMap<>();
			bps.put("BOXLIST", mpList);
			List<Map<String, Object>> sr=IDMFrameServiceProxy.getSqlTemplate().queryForList(sqls, bps);
			mpList = new ArrayList<String> ();
			mpList.add("empty");

			List<String> expDateBoxList = new ArrayList<String> ();
			expDateBoxList.add("empty");

 			if(sr.size()>0)
			{
				for(int i=0;i<sr.size();i++) 
				{
					MaterialPackingKey mpk=new MaterialPackingKey();
					mpk.setSiteName(siteName);
					mpk.setMaterialPackingName(sr.get(i).get("MATERIALPACKINGNAME").toString());
					mpkList.add(mpk);
					mpList.add(sr.get(i).get("MATERIALPACKINGNAME").toString());

					// 如果存在延期时间的批次，就添加到延期列表中
					String charge = sr.get(i).get("CHARGE").toString();
					if(expBoxList.contains(charge)) {
						expDateBoxList.add(sr.get(i).get("MATERIALPACKINGNAME").toString());
					}
				}
							
			}


			log.info("======UPDATE MATERIALPACKING OQAResult");
				
			String sql=" UPDATE MATERIALPACKING m SET m.PACKINGGRADE =(SELECT t.PACKINGGRADE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE),\r\n" + 
					" m.SPECIALSTATE =(SELECT t.SPECIALSTATE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME),\r\n" + 
					" m.OQARESULT =(SELECT t.PACKINGGRADE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME),\r\n" +  
					" m.OQARESULTSTATE =(SELECT CASE WHEN t.PACKINGGRADE IS NULL THEN 'PREOQA' ELSE 'END' end \r\n" + 
					" from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME)\r\n" + 
					" WHERE m.materialpackingname  in(:BOXLIST)\r\n" + 
					" AND m.STOCKSTATE ='Stocked' ";
			
			//qms传过来存在过期时间情况
//			if(!expiringDateType.isEmpty() && !StringUtils.equals("4", qmsType)) {
//				String sqlEp=" UPDATE MATERIALPACKING m SET m.EXPIRINGDATE =(SELECT TO_DATE(t.EXPIRINGDATE,'YYYY-MM-DD HH24:MI:SS') EXPIRINGDATE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME) \r\n" +
//						" WHERE m.materialpackingname  in(:BOXLIST)\r\n" +
//						" AND m.STOCKSTATE ='Stocked' ";
//
//				Map<String, Object> exhp=new HashMap<>();
//				exhp.put("BOXLIST", mpList);
//				int exUpdate = IDMFrameServiceProxy.getSqlTemplate().update(sqlEp, exhp);
//			}
			if(!StringUtils.equals("4", qmsType)) {
				String sqlEp=" UPDATE MATERIALPACKING m SET m.EXPIRINGDATE =(SELECT TO_DATE(t.EXPIRINGDATE,'YYYY-MM-DD HH24:MI:SS') EXPIRINGDATE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME AND t.EXPIRINGDATE IS NOT NULL) \r\n" +
						" WHERE m.materialpackingname  in(:BOXLIST)\r\n" +
						" AND m.STOCKSTATE ='Stocked' ";

				Map<String, Object> exhp=new HashMap<>();
				exhp.put("BOXLIST", expDateBoxList);
				int exUpdate = IDMFrameServiceProxy.getSqlTemplate().update(sqlEp, exhp);
			}

			//临期复检需更新到期日
			if(StringUtils.equals("4", qmsType)) 
			{
				sql=" UPDATE MATERIALPACKING m SET m.PACKINGGRADE =(SELECT t.PACKINGGRADE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME), \r\n" + 
						"					 m.SPECIALSTATE =(SELECT t.SPECIALSTATE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME), \r\n" + 
						"					 m.OQARESULT =(SELECT t.PACKINGGRADE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME), \r\n" + 
						"					 m.OQARESULTSTATE =(SELECT CASE WHEN t.PACKINGGRADE IS NULL THEN 'PREOQA' ELSE 'END' end  \r\n" + 
						"					 from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME), \r\n" + 
						"					 m.EXPIRINGDATE = (SELECT CASE WHEN t.PACKINGGRADE='OK' THEN SYSDATE + (SELECT m2.EXPIRATIONDAY/2 FROM MATERIALSPEC m2 \r\n" + 
						"					 WHERE m2.MATERIALSPECNAME=t.MATERIALSPECNAME) ELSE m.EXPIRINGDATE end  \r\n" + 
						"					 from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME) ,\r\n" + 
						"					 m.REINSPEFLAG = (SELECT CASE WHEN t.PACKINGGRADE='OK' THEN to_number(nvl(m.REINSPEFLAG,'0'))+1  ELSE to_number(nvl(m.REINSPEFLAG,'0')) end  \r\n" + 
						"					 from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE AND m.MATERIALSPECNAME = t.MATERIALSPECNAME)\r\n" + 
						"					 WHERE m.materialpackingname  in(:BOXLIST)  \r\n" + 
						"					 AND m.STOCKSTATE ='Stocked' ";
			}
			Map<String, Object> bp=new HashMap<>();
			bp.put("BOXLIST", mpList);
			int update = IDMFrameServiceProxy.getSqlTemplate().update(sql, bp);
			
			log.info("======UPDATE BS_MES_SHIPPED OQAResult");
			String sql2="UPDATE BS_MES_SHIPPED m SET m.PACKINGGRADE =(SELECT t.PACKINGGRADE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE),\r\n" + 
					"m.PACKINGGRADEDETAIL =(SELECT t.SPECIALSTATE from MATERIALPACKINGQMSTEMP t WHERE t.materialpackingname=m.CHARGE)\r\n" + 
					"WHERE m.LOTNAME  in(:BOXLIST)";
			IDMFrameServiceProxy.getSqlTemplate().update(sql2, bp);
			
			return mpkList;
		}
		//更新履历
		public static void setEventOqaResult(List<MaterialPackingKey> mpList,String undoId,String siteName,EventInfo eventInfo) 
		{
								
			if(mpList.size()>0)
			{
				
				
				SetEventInfo setEventInfo=new SetEventInfo();
				Map<String, Object> userc=new HashMap<>();
				//userc.put("unDoID", undoId);
				setEventInfo.setUserColumns(userc);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(mpList, eventInfo, setEventInfo);
				//存储物料凭证
				//SDKMaterialPackingServiceImpl.SaveUnDoInfo( undoId);
			}
			
		}
	
	@Transactional
	public void autoChangeERPlocation (JSONArray in,EventInfo eventInfo,List<MaterialPackingKey> mpkList) throws Exception {
		String sqlDic = "SELECT s.OKERPLOCATION,s.BEPENDINGERPLOCATION,\r\n" + 
				" s2.STORAGENAME OKLOCATION,s3.STORAGENAME BEPENDINGLOCATION\r\n" + 
				" FROM BS_ERPLOCATIONSHIP S \r\n" + 
				" LEFT JOIN STORAGESPEC s2 \r\n" + 
				" ON s.OKERPLOCATION =s2.ERPLOCATION \r\n" + 
				" AND s2.STORAGEUSETYPE='virtual'\r\n" + 
				" LEFT JOIN STORAGESPEC s3 \r\n" + 
				" ON s.BEPENDINGERPLOCATION =s3.ERPLOCATION \r\n" + 
				" AND s3.STORAGEUSETYPE='virtual'\r\n" + 
				" WHERE S.DJERPLOCATION = :ERPLOCATION ";
		Map<String, Object> hashMap2 = new HashMap<String,Object> ();
		for (int i = 0; i < in.size(); i++) {
			JSONObject jb = in.getJSONObject(i);
			String commonNumber = jb.get("commonNumber").toString();//通用单号
			String materialCode = jb.get("materialCode")== null  ? "" : jb.get("materialCode").toString();//料号
			String pqcResult = jb.get("judgementResult").toString();//检验结果
			String pqcResultDate = jb.get("resultTime").toString();//检验时间
			String user = jb.get("inspector").toString();//检验员
			String exceptionHandling = jb.get("exceptionHandling") == null  ? "" : jb.get("exceptionHandling").toString();//异常处理 （0复卷，1降级，2报废）
			String specialState = jb.get("specialState") == null  ? "" : jb.get("specialState").toString();//特殊状态 （0特采、1紧急放行）
			String transferStatus = jb.get("transferStatus") == null  ? "" : jb.get("transferStatus").toString();//异常处理

			String tpType = jb.get("tpType").toString();//检验类型
			//0不合格、1合格
			if("1".equals(pqcResult))
			{
				pqcResult="OK";
			}else if ("0".equals(pqcResult)) {
				pqcResult = "NG";
				continue;//NG直接跳过
			} 
			List<MaterialPackingKey> mpList = new ArrayList<> ();
			List<String> boxList = new ArrayList<> ();
			if ("0".equals(tpType)) {//到货单
				String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME,M.ERPLOCATION,M.LOCATIONNAME FROM MATERIALPACKING M "
						+ "WHERE M.MESSAGEID  = :RECEIVEREQUESTNAME ";
				Map<String, Object> hashMap = new HashMap<String,Object> ();
				hashMap.put("RECEIVEREQUESTNAME", commonNumber);
				List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
				SetEventInfo setEventInfo = new SetEventInfo();
				if (queryForList == null || queryForList.size() < 1) {
				} else {
					for (Map<String, Object> map : queryForList) {
						hashMap2.put("ERPLOCATION", map.get("ERPLOCATION"));
						Map<String, Object> hashMap3 = new HashMap<String,Object> ();
						hashMap3.put("preErpLocation", map.get("ERPLOCATION"));
						hashMap3.put("PRELOCATIONNAME", map.get("LOCATIONNAME"));
						List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlDic, hashMap2);
						if (queryForList2 == null || queryForList2.size() < 1) {
							log.info("未找到待检仓");
							continue;
						}
						String OKERPLOCATION=queryForList2.get(0).get("OKERPLOCATION")==null?"":queryForList2.get(0).get("OKERPLOCATION").toString();
						String OKLOCATION=queryForList2.get(0).get("OKLOCATION")==null?"":queryForList2.get(0).get("OKLOCATION").toString();
						String BEPENDINGERPLOCATION=queryForList2.get(0).get("BEPENDINGERPLOCATION")==null?"":queryForList2.get(0).get("BEPENDINGERPLOCATION").toString();
						String BEPENDINGLOCATION=queryForList2.get(0).get("BEPENDINGLOCATION")==null?"":queryForList2.get(0).get("BEPENDINGLOCATION").toString();
						
						if ("OK".equals(pqcResult)) {
							if(OKERPLOCATION.length()==0 || OKLOCATION.length()==0) 
							{
								log.info("未维护转库对应关系");
								continue;
								//throw new CustomException("未维护转库对应关系");
							}
							hashMap3.put("erpLocation", queryForList2.get(0).get("OKERPLOCATION"));
							hashMap3.put("locationName", queryForList2.get(0).get("OKLOCATION"));
							
						} else if ("NG".equals(pqcResult)) {
							if(BEPENDINGERPLOCATION.length()==0 || BEPENDINGLOCATION.length()==0) 
							{
								log.info("未维护转库对应关系");
								continue;
								//throw new CustomException("未维护转库对应关系");
							}
							hashMap3.put("erpLocation", queryForList2.get(0).get("BEPENDINGERPLOCATION"));
							hashMap3.put("locationName", queryForList2.get(0).get("BEPENDINGLOCATION"));
						}
						setEventInfo.setUserColumns(hashMap3);
						mpList.add(new MaterialPackingKey(map.get("SITENAME").toString(),
								map.get("MATERIALPACKINGNAME").toString()));
						boxList.add(map.get("MATERIALPACKINGNAME").toString());
					}
					if(mpList.size()>0)
					{
						MaterialPackingServiceProxy.getMaterialPackingService().setEvent(mpList, eventInfo, setEventInfo);
					}
				}
				if(boxList.size()>0)
				{
					String sendUser="101867";
//					NCWServiceImpl.OQZkNoInvoice(boxList, sendUser);
				}
			}
			else 
			{
				//非到货单
//				String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME,M.ERPLOCATION,M.LOCATIONNAME FROM MATERIALPACKING M "
//						+ "WHERE M.MESSAGEID  = :RECEIVEREQUESTNAME ";
//				Map<String, Object> hashMap = new HashMap<String,Object> ();
//				hashMap.put("RECEIVEREQUESTNAME", commonNumber);
//				List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
				SetEventInfo setEventInfo = new SetEventInfo();
				if (mpkList == null || mpkList.size() < 1) {
				} else {
					for (int j=0;j<mpkList.size();j++) {
						String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME,M.ERPLOCATION,M.LOCATIONNAME FROM MATERIALPACKING M "
						+ "WHERE M.MATERIALPACKINGNAME  = :MATERIALPACKINGNAME  ";
						Map<String, Object> hashMap = new HashMap<String,Object> ();
						hashMap.put("MATERIALPACKINGNAME", mpkList.get(j).getMaterialPackingName());
						List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
						hashMap2.put("ERPLOCATION", queryForList.get(0).get("ERPLOCATION"));
						Map<String, Object> hashMap3 = new HashMap<String,Object> ();
						hashMap3.put("preErpLocation",  queryForList.get(0).get("ERPLOCATION"));
						hashMap3.put("PRELOCATIONNAME",  queryForList.get(0).get("LOCATIONNAME"));
						List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlDic, hashMap2);
						if (queryForList2 == null || queryForList2.size() < 1) {
							log.info("未找到待检仓");
							continue;
						}
						String OKERPLOCATION=queryForList2.get(0).get("OKERPLOCATION")==null?"":queryForList2.get(0).get("OKERPLOCATION").toString();
						String OKLOCATION=queryForList2.get(0).get("OKLOCATION")==null?"":queryForList2.get(0).get("OKLOCATION").toString();
						String BEPENDINGERPLOCATION=queryForList2.get(0).get("BEPENDINGERPLOCATION")==null?"":queryForList2.get(0).get("BEPENDINGERPLOCATION").toString();
						String BEPENDINGLOCATION=queryForList2.get(0).get("BEPENDINGLOCATION")==null?"":queryForList2.get(0).get("BEPENDINGLOCATION").toString();
						
						if ("OK".equals(pqcResult)) {
							if(OKERPLOCATION.length()==0 || OKLOCATION.length()==0) 
							{
								log.info("未维护转库对应关系");
								continue;
								//throw new CustomException("未维护转库对应关系");
							}
							hashMap3.put("erpLocation", queryForList2.get(0).get("OKERPLOCATION"));
							hashMap3.put("locationName", queryForList2.get(0).get("OKLOCATION"));
							
						} else if ("NG".equals(pqcResult)) {
							if(BEPENDINGERPLOCATION.length()==0 || BEPENDINGLOCATION.length()==0) 
							{
								log.info("未维护转库对应关系");
								continue;
								//throw new CustomException("未维护转库对应关系");
							}
							hashMap3.put("erpLocation", queryForList2.get(0).get("BEPENDINGERPLOCATION"));
							hashMap3.put("locationName", queryForList2.get(0).get("BEPENDINGLOCATION"));
						}
						setEventInfo.setUserColumns(hashMap3);
						mpList.add(new MaterialPackingKey( queryForList.get(0).get("SITENAME").toString(),
								 queryForList.get(0).get("MATERIALPACKINGNAME").toString()));
						boxList.add( queryForList.get(0).get("MATERIALPACKINGNAME").toString());
					}
					if(mpList.size()>0)
					{
						MaterialPackingServiceProxy.getMaterialPackingService().setEvent(mpList, eventInfo, setEventInfo);
					}
				}
				if(boxList.size()>0)
				{
					String sendUser="101867";
//					NCWServiceImpl.OQZkNoInvoice(boxList, sendUser);
				}
			
			}
		}
	}


	public static Document createXmlDocumentByString(String sourcesubjectname, String targetsubjectname, String messageName, JSONObject d) throws Exception
	{
		Element message = new Element( Message_Tag );
		Document document = new Document( message );


		Element header = new Element( Header_Tag );

		Element subElement = new Element( MessageName_Tag );
		subElement.setText(messageName);
		header.addContent(subElement);

		subElement = new Element( "EVENTCOMMENT" );
		subElement.setText(messageName);
		header.addContent(subElement);

		subElement = new Element( "SOURCESUBJECTNAME" );
		subElement.setText(sourcesubjectname);
		header.addContent(subElement);

		subElement = new Element( "TARGETSUBJECTNAME" );
		subElement.setText(targetsubjectname);
		header.addContent(subElement);


		subElement = new Element( "SITENAME" );
		subElement.setText("");
		header.addContent(subElement);

		subElement = new Element( "EVENTUSER" );
		subElement.setText("");
		header.addContent(subElement);

		subElement = new Element( "LANGUAGE" );
		subElement.setText("");
		header.addContent(subElement);

		message.addContent(header);

		//		Element body = new Element( Body_Tag );
		//		body.setText(d);

		//		com.alibaba.fastjson.JSONObject jsonObject = xmlToJson(d);
		//		Object object = jsonObject.get("detail");
		Element body = new Element( Body_Tag );
		Element ele = null;
		//调用从Json转化成XML格式的方法

		jsonToElement2(body, d);
		//Iterator keys = jsonObject.keys();
		message.addContent(body);
		return document;
	}



	public static JSONObject xmlToJson(String xml) {
		//去除xml开头和结尾的双引号
		StringBuilder sb = new StringBuilder();
		sb.append(xml);
		char charAt = sb.charAt(0);
		if (sb.charAt(0) == '\"') {
			sb.deleteCharAt(0);
		}
		if (sb.charAt(sb.length() - 1) == '\"') {
			sb.deleteCharAt(sb.length() - 1);
		}
		Map<String, Object> stringObjectMap = XmlUtil.xmlToMap(sb.toString());
		String json = JSONObject.toJSONString(stringObjectMap);
		return JSONObject.parseObject(json, JSONObject.class);
	}

	public static void jsonToElement(Element body, JSONObject jsonObject) {
		Iterator<String> iterator = jsonObject.keySet().iterator();
		while(iterator.hasNext()) {
			String key = iterator.next();
			Object jsonChild = jsonObject.get(key);
			if(jsonChild.getClass().equals(JSONObject.class)) {
				Element child = new Element(key.toUpperCase());
				body.addContent(child);
				jsonToElement(child,(JSONObject)jsonChild);
			}else if(JSONArray.class.equals(jsonChild.getClass())) {
				Element child = new Element(key.toUpperCase());
				body.addContent(child);
				for(Object o : (JSONArray)jsonChild) {
					if(o instanceof JSONObject) {
						Element data = new Element("DATA");
						child.addContent(data);
						jsonToElement(data,(JSONObject)o);
					}else {
						Element child2 = new Element((key+"child").toUpperCase());
						child2.setText(o.toString());
						child.addContent(child2);
					}
				}

			}else {
				Element child = new Element(key.toUpperCase());
				body.addContent(child);
				child.setText(jsonChild.toString());
			}
		}
	}

	public static void jsonToElement2(Element body, JSONObject jsonObject) {
		Iterator<String> iterator = jsonObject.keySet().iterator();
		while(iterator.hasNext()) {
			String key = iterator.next();
			Object jsonChild = jsonObject.get(key);
			if(jsonChild.getClass().equals(JSONObject.class)) {
				if ("detail".equals(key)) {
					Element child = new Element(key.toUpperCase());
					Element data = new Element("DATA");
					child.addContent(data);
					body.addContent(child);
					jsonToElement2(data,(JSONObject)jsonChild);
				} else {
					Element child = new Element(key.toUpperCase());
					body.addContent(child);
					jsonToElement2(child,(JSONObject)jsonChild);
				}
				//				Element child = new Element(key.toUpperCase());
				//				body.addContent(child);
				//				jsonToElement2(child,(com.alibaba.fastjson.JSONObject)jsonChild);
			}else if(JSONArray.class.equals(jsonChild.getClass())) {
				Element child = new Element(key.toUpperCase());
				body.addContent(child);
				for(Object o : (JSONArray)jsonChild) {
					if(o instanceof JSONObject && !"detail".equals(key)) {
						Element data = new Element("DATAS");
						child.addContent(data);
						jsonToElement2(data,(JSONObject)o);
					} else if (o instanceof JSONObject && "detail".equals(key)) {
						Element data = new Element("DATA");
						child.addContent(data);
						jsonToElement2(data,(JSONObject)o);
					}else {
						Element child2 = new Element((key+"child").toUpperCase());
						child2.setText(o.toString());
						child.addContent(child2);
					}
				}

			}else {
				Element child = new Element(key.toUpperCase());
				body.addContent(child);
				child.setText(jsonChild.toString());
			}
		}
	}


	public static void saveMessageLog(ErpMessageLog log) {

		String sql = "INSERT INTO BS_ERPMESSAGELOG(INTERFACETIME,SERVERNAME, EVENTNAME, EVENTTIMEKEY, EVENTUSER, MESSAGELOG,RESULTCODE,MESSAGEID,RESULTMESSAGE,MESSAGELOG2,RESULTMESSAGE2)\r\n"
				+ "VALUES(TO_DATE(:INTERFACETIME,'yyyy-mm-dd hh24:mi:ss'),:SERVERNAME, :EVENTNAME, :EVENTTIMEKEY, :EVENTUSER, :MESSAGELOG,:RESULTCODE,:MESSAGEID,:RESULTMESSAGE,:MESSAGELOG2,:RESULTMESSAGE2)";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		bindMap.put("SERVERNAME", log.getServerName());
		bindMap.put("EVENTNAME", log.getEventName());
		bindMap.put("EVENTTIMEKEY", log.getId());
		bindMap.put("EVENTUSER", log.getEventUser());
		bindMap.put("MESSAGEID", log.getMessageId());
		bindMap.put("INTERFACETIME", log.getInterfaceTime());
		bindMap.put("RESULTCODE", log.getResultCode());
		bindMap.put("MESSAGELOG", log.getSendMsg());// 推送ERP的json
		bindMap.put("RESULTMESSAGE", log.getReturnMsg());// 返回结果的json
		bindMap.put("MESSAGELOG2", log.getSendMsg2());// 推送ERP的json
		bindMap.put("RESULTMESSAGE2", log.getReturnMsg2());// 返回结果的json
		IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
	}
}


