package com.cim.idm.service.Impl;

import com.cim.idm.exception.GlobalException;
import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.data.EventInfo;
import com.cim.idm.framework.exception.IDMFrameDBErrorSignal;
import com.cim.idm.framework.util.time.TimeStampUtil;
import com.cim.idm.service.ReturnStockInService;
import com.cim.idm.service.impl.ToSAPServiceImpl;
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.wmspackage.materialpacking.MaterialPackingServiceProxy;
import com.cim.idm.wmspackage.materialpacking.management.data.MaterialPacking;
import com.cim.idm.wmspackage.materialpacking.management.data.MaterialPackingKey;
import com.cim.idm.wmspackage.materialpacking.management.info.CreateInfo;
import com.cim.idm.wmspackage.materialpacking.management.info.SetEventInfo;
import com.cim.idm.wmspackage.materialreceiverequestact.MaterialReceiveRequestActServiceProxy;
import com.cim.idm.wmspackage.materialreceiverequestact.management.info.CreateReceiveRequestBatchInfo;
import com.cim.idm.wmspackage.materialreceiverequestactdetail.MaterialReceiveRequestActDetailServiceProxy;
import com.cim.idm.wmspackage.materialreceiverequestactdetail.management.info.ActDetailIncreaseInfo;
import com.cim.idm.wmspackage.materialreceiverequestactdetail.management.info.CreateActDetailInfo;
import com.cim.idm.wmspackage.materialreceivetactdetail.management.data.MaterialReceiveActDetailKey;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.xml.sax.SAXException;

import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ReturnStockIntoServiceImpl implements ReturnStockInService {

	@Autowired
	private ToSAPServiceImpl ToSAPServiceImpl;
	

	/**
	 * 保存入库单
	 * @param eventInfo
	 * @param siteName
	 * @param receiveRequestName
	 * @param ReceiveRequestDetailName
	 * @param makeDate
	 * @param receiveActNo
	 * @param actType
	 * @param receiveQuantity
	 * @param subMaterialSpecName
	 * @throws CustomException
	 * @throws ParseException
	 */
	public void CreateMaterialReceiveRequestAct(EventInfo eventInfo,
			String siteName, String receiveRequestName,
			String ReceiveRequestDetailName, String makeDate,
			String receiveActNo, String actType, String receiveQuantity,
			String subMaterialSpecName) throws CustomException, ParseException {
		String actType1 = "";

		float ReceiveQuantity = Float.parseFloat(receiveQuantity);

		// receiveActInfo
		MaterialReceiveActDetailKey materialReceiveActDetailKey = new MaterialReceiveActDetailKey();
		CreateActDetailInfo createActDetailInfo = new CreateActDetailInfo();
		//
		materialReceiveActDetailKey.setSiteName(siteName);
		materialReceiveActDetailKey.setReceiveRequestName(receiveRequestName);
		materialReceiveActDetailKey
		.setReceiveRequestDetailName(ReceiveRequestDetailName);

		materialReceiveActDetailKey.setReceiveActNO(receiveActNo);
//		materialReceiveActDetailKey.setMakeDate(Timestamp.valueOf(makeDate));

		String sql = "SELECT * FROM materialreceiveactdetail "
				+ "WHERE sitename = :sitename AND receiveRequestName = :receiveRequestName and ReceiveRequestDetailName=:ReceiveRequestDetailName and receiveActNo=:receiveActNo"
				+ " and makeDate=:makeDate ";

		Map<String, Object> bindMap = new HashMap<String, Object>();
		bindMap.put("sitename", siteName);
		bindMap.put("receiveRequestName", receiveRequestName);
		bindMap.put("ReceiveRequestDetailName", ReceiveRequestDetailName);
		bindMap.put("receiveActNo", receiveActNo);
		bindMap.put("makeDate", Timestamp.valueOf(makeDate));

		List<Map<String, Object>> queryResult = IDMFrameServiceProxy
				.getSqlTemplate()
				.queryForList(sql, bindMap);

		String sql1 = "SELECT * FROM materialreceiveact "
				+ "WHERE sitename = :sitename AND receiveRequestName = :receiveRequestName  and receiveActNo=:receiveActNo ";

		Map<String, Object> bindMap1 = new HashMap<String, Object>();
		bindMap1.put("sitename", siteName);
		bindMap1.put("receiveRequestName", receiveRequestName);
		bindMap1.put("receiveActNo", receiveActNo);

		List<Map<String, Object>> queryResult1 = IDMFrameServiceProxy
				.getSqlTemplate()
				.queryForList(sql1, bindMap1);
		if (queryResult1.size() != 0) {
			actType1 = queryResult1.get(0).get("ACTTYPE").toString();
		}
		if (queryResult1.size() == 0) {
			CreateReceiveRequestBatchInfo createActBatchInfo = new CreateReceiveRequestBatchInfo();
			List<CreateReceiveRequestBatchInfo> createReceiveRequestBatchInfo = new ArrayList<CreateReceiveRequestBatchInfo>();

			Map<String, Object> bindMap2 = new HashMap<String, Object>();


			createActBatchInfo.setSiteName(siteName);
			createActBatchInfo.setReceiveRequestName(receiveRequestName);
			createActBatchInfo.setReceiveactNO(receiveActNo);
			createActBatchInfo.setReceiveActState("Created");

			bindMap2.put("actType", actType);
			// bindMap2.put("receiveActState", );
			createActBatchInfo.setUserColumns(bindMap2);

			createReceiveRequestBatchInfo.add(createActBatchInfo);

			MaterialReceiveRequestActServiceProxy
					.getReceiveRequestBatchService().create(eventInfo,
							createReceiveRequestBatchInfo);
		}

		if (queryResult.size() == 0) {
			createActDetailInfo.setReceiveActNO(receiveActNo);
			createActDetailInfo.setReceiveRequestName(receiveRequestName);
			createActDetailInfo
			.setReceiveRequestDetailName(ReceiveRequestDetailName);
			createActDetailInfo.setSiteName(siteName);

			createActDetailInfo.setMakeDate(Timestamp.valueOf(makeDate));
			createActDetailInfo.setReceiveQuantity(ReceiveQuantity);

			Map<String, Object> bindMap3 = new HashMap<String, Object>();
			bindMap3.put("receiveActState", "Created");
			createActDetailInfo.setUserColumns(bindMap3);

			MaterialReceiveRequestActDetailServiceProxy
					.getMaterialReceiveRequestActDetailService().create(
							eventInfo, createActDetailInfo);
			
//			因为float精度问题，再更新一次
			try {
				String updateSql= " UPDATE MATERIALRECEIVEACTDETAIL SET RECEIVEQUANTITY = :QTY WHERE RECEIVEACTNO = :RECEIVEACTNO"
						+ " AND RECEIVEREQUESTNAME = :RECEIVEREQUESTNAME AND RECEIVEREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME";
				Map<String, Object> updateMap = new HashMap<String, Object>();
				updateMap.put("QTY", receiveQuantity);
				updateMap.put("RECEIVEACTNO", receiveActNo);
				updateMap.put("RECEIVEREQUESTNAME", receiveRequestName);
				updateMap.put("RECEIVEREQUESTDETAILNAME", createActDetailInfo.getReceiveRequestDetailName());
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, updateMap);
			} catch (IDMFrameDBErrorSignal e) {
			}
			
		} else {
			ActDetailIncreaseInfo actDetailIncreaseInfo = new ActDetailIncreaseInfo();
			actDetailIncreaseInfo.setReceiveQuantity(ReceiveQuantity);
			MaterialReceiveRequestActDetailServiceProxy
					.getMaterialReceiveRequestActDetailService()
					.increaseReceivedQuantity(materialReceiveActDetailKey,
							eventInfo, actDetailIncreaseInfo);
		}
		//float型计算精度缺失问题
		Map<String, Object> bindMapF = new HashMap<String, Object>();
		Map<String, Object> bindMapFU = new HashMap<String, Object>();
		String receivedQuantiryF="";
		bindMapF.put("receiveRequestName",receiveRequestName);
		bindMapF.put("ReceiveRequestDetailName", ReceiveRequestDetailName);
		bindMapF.put("sitename", siteName);
		String sqlF="SELECT  ASSIGNEDQUANTITY FROM MATERIALSHIPREQUESTDETAIL WHERE SITENAME=:sitename AND SHIPREQUESTNAME =:receiveRequestName AND SHIPREQUESTDETAILNAME =:ReceiveRequestDetailName";
		String sqlFU="UPDATE MATERIALSHIPREQUESTDETAIL SET ASSIGNEDQUANTITY=:receivedQuantity WHERE SITENAME=:sitename AND SHIPREQUESTNAME=:receiveRequestName AND SHIPREQUESTDETAILNAME=:ReceiveRequestDetailName";
		List<Map<String, Object>> queryResultF = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlF, bindMapF);
		if(queryResultF.size()!=0){
			 receivedQuantiryF=queryResultF.get(0).get("ASSIGNEDQUANTITY").toString();
		}
		BigDecimal receivequantity=new BigDecimal(String.valueOf(receiveQuantity));
		BigDecimal receivedQuantiry=new BigDecimal(receivedQuantiryF);
		bindMapFU.put("receivedQuantity", receivequantity.add(receivedQuantiry).doubleValue());
		bindMapFU.put("sitename", siteName);
		bindMapFU.put("receiveRequestName", receiveRequestName);
		bindMapFU.put("ReceiveRequestDetailName", ReceiveRequestDetailName);
		IDMFrameServiceProxy.getSqlTemplate().update(sqlFU, bindMapFU);
	}
	

	@Transactional
	public void returnStockInNew (List<MaterialPacking> boxList, String commitDate, String opCode,
								  String user) throws CustomException {
		String pc = "(";
		for (MaterialPacking materialPacking : boxList) {
			String materialPackingName = materialPacking.getMaterialPackingName();
			pc += "'" +  materialPackingName + "', ";
			String locationName = materialPacking.getLocationName();
			String erpFactory = materialPacking.getErpFactory();
			String erpLocation = materialPacking.getErpLocation();
			String materialspec=materialPacking.getMaterialSpecName();
			String phase = (materialPacking.getPHASE() == null || "".equals(materialPacking.getPHASE())) ? "" : materialPacking.getPHASE();
			String sdk_id = (materialPacking.getSDK_ID() == null || "".equals(materialPacking.getSDK_ID())) ? "" : materialPacking.getSDK_ID();
			String sql2 = "SELECT\r\n" + 
					"	BMS.LOTNAME ,\r\n" + 
					"	BMS.PRODUCTSPECNAME ,\r\n" + 
					"	BMS.PHASE ,\r\n" + 
					"	SS.SPECNAME ,\r\n" + 
					"	m.DESC_CN ,\r\n" + 
					"	BMS.QTY ,\r\n" + 
					"	BMS.SHIPTIME ,\r\n" + 
					"	SS.SDK_ID ,BMS.REQUESTNAME ,RETURNPC ,\r\n" +
					"	REGEXP_SUBSTR(BMS.LOTNAME , '[^|]+', 1, 5) AS CHARGE\r\n" + 
					"FROM\r\n" + 
					"	BS_MES_SHIPPED BMS LEFT JOIN MATERIALSPEC m ON m.MATERIALSPECNAME = BMS.PRODUCTSPECNAME \r\n" + 
					"	LEFT JOIN SDK_SPEC SS ON SS.SDK_ID = BMS.SDK_ID \r\n" + 
					"WHERE\r\n" + 
					"	BMS.LOTNAME = :LOTNAME\r\n" + 
					"	AND (BMS.RECEIVE_FLAG IS NULL\r\n" + 
					"		OR BMS.RECEIVE_FLAG <> 'Y')";
			Map<String, Object> bindMap = new HashMap<String, Object>();
			bindMap.put("LOTNAME",materialPackingName); 
			List<Map<String,Object>> list2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql2, bindMap);
			if(list2.size() < 1) {
				throw new GlobalException( materialPackingName + "未打印退料标签,请联系IT处理！");
			}

			//根据退库信息获取退料单
//			String sql3 = "SELECT\r\n" +
//					"	M.SHIPREQUESTNAME,\r\n" +
//					"	M.SHIPREQUESTDETAILNAME\r\n" +
//					"FROM\r\n" +
//					"	MATERIALSHIPREQUESTDETAIL M\r\n" +
//					"WHERE\r\n" +
//					"	(M.RETURNNO = :SHIPREQUESTNAME OR M.SHIPREQUESTNAME = :SHIPREQUESTNAME)\r\n" +
//					"	AND M.MATERIALSPECNAME = :MATERIALSPECNAME AND M.CHARGE = :RETURNPC ";
//
//			bindMap.put("SHIPREQUESTNAME",list2.get(0).get("REQUESTNAME"));
//			bindMap.put("MATERIALSPECNAME",list2.get(0).get("PRODUCTSPECNAME"));
//			bindMap.put("RETURNPC",list2.get(0).get("RETURNPC"));
//			List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql3, bindMap);
//			if (queryForList == null || queryForList.size() < 1) {
//				throw new GlobalException(list2.get(0).get("REQUESTNAME")+"退料单据信息不存在,请联系IT处理！");
//			}
			EventInfo makeEventInfo = new EventInfoUtil().makeEventInfo("createBoxInfoReturn", user, "createBoxInfoReturn", "", "");

			try {
//				Calendar c = Calendar.getInstance();
//				SimpleDateFormat f = new SimpleDateFormat(
//						"yyyyMMdd");
//
//				List<String> args = new ArrayList<String>();
//				String actType1 = "F";
//				String siteName = "SH";
//				args.add(siteName);
//				args.add(actType1);
//				args.add(f.format(c.getTime()));

				String ReceiveRequestActNo = opCode;

//				ReceiveRequestActNo = NameServiceProxy.getNameRuleDefService()
//						.generateName("ReceiveActNo", args, 1).get(0);
//			CreateMaterialReceiveRequestAct
//				(makeEventInfo, "SDK", queryForList.get(0).get("SHIPREQUESTNAME").toString(), 
//						queryForList.get(0).get("SHIPREQUESTDETAILNAME").toString(), list2.get(0).get("SHIPTIME").toString(), 
//						ReceiveRequestActNo, "ProduceReturn", list2.get(0).get("QTY").toString(), list2.get(0).get("PRODUCTSPECNAME").toString());

//			SHIPMesShippInfo(makeEventInfo, "SDK", materialPackingName, "", user,
//						materialPackingName, ReceiveRequestActNo, erpLocation, erpFactory, locationName,queryForList.get(0).get("SHIPREQUESTNAME").toString(), queryForList.get(0).get("SHIPREQUESTDETAILNAME").toString());

			SHIPMesShippInfo(makeEventInfo, "SDK", materialPackingName, "", user,
						materialPackingName, ReceiveRequestActNo, erpLocation, erpFactory, locationName,list2.get(0).get("REQUESTNAME").toString(), "");

			} catch (Exception e) {
				e.printStackTrace();
				throw new GlobalException(e.toString());
			}
		}
		pc += "'')";
		try {
			boolean refundStockIn = ReturnStockInNew(pc, user, commitDate,opCode);
		} catch (Exception e) {
			e.printStackTrace();
			throw new GlobalException(e.toString());
		}
		
	}


	/**
	 *
	 * @param eventInfo
	 * @param siteName
	 * @param materialPackingName
	 * @param durableName
	 * @param user
	 * @param palletName
	 * @param SHIPActNo
	 * @param erpLocation
	 * @param erpFactory
	 * @param locationName
	 * @param SHIPRequestName
	 * @param SHIPRequestDetailName
	 * @return
	 */
	public boolean SHIPMesShippInfo(EventInfo eventInfo,String siteName,String materialPackingName,
			String durableName,String user,String palletName,String SHIPActNo,
			String erpLocation,String erpFactory,String locationName,String SHIPRequestName, String SHIPRequestDetailName)  {
		StringBuffer sql = new StringBuffer();
		sql.append(" ");
		sql.append(" SELECT ");
		sql.append(" T.LOTNAME MATERIALPACKINGNAME, ");
		sql.append(" T.QTY MATERIALQUANTITY, ");
		sql.append(" T.UNIT , T.CHARGE, ");
		sql.append(" M2. SHIPREQUESTDETAILNAME,");
		sql.append(" T.REQUESTNAME SHIPREQUESTNAME,");
		sql.append(" T.PRODUCTSPECNAME MATERIALSPECNAME,  ");
		sql.append(" SS.SPECNAME , ");
		sql.append(" REPLACE(T.SDK_ID,'~','') SDK_ID ,  ");
		sql.append(" M.ERPLOCATION , ");
		sql.append(" T.AREA ,");
		sql.append(" T.SHIPUSER , NVL(M4.CAREER_ASSISTANCE ,T.CAREER_ASSISTANCE ) CAREER_ASSISTANCE,  NVL(M4.DURABLETYPE ,T.DURABLETYPE ) DURABLETYPE ,NVL(M4.BUSINESS_UNIT ,T.BUSINESS_UNIT ) BUSINESS_UNIT,NVL(M4.CUSTOMNO ,T.CUSTOMERNO ) CUSTOMNO,NVL(M4.SALESPERSON ,T.SALESPERSON ) SALESPERSON,NVL(M4.MJPC ,T.MJPC ) MJPC ,NVL(T.TRUEGG,M4.TRUEGG) TRUEGG,");
		sql.append(" T.PACKINGGRADE , ");
		sql.append(" M4.EXPIRINGDATE , ");
		sql.append(" NVL(M4.MAKEDATE ,T.MAKEDATE ) SHIPTIME , ");
		sql.append(" M3.MATERIALTYPE , ");
		sql.append(" T.REMARK, T.PHASE, T.RETURNPC,T.MJPC ");
		sql.append(" FROM ");
		sql.append(" BS_MES_SHIPPED T LEFT JOIN MATERIALPACKING M4 ON T.RETURNPC = M4.CHARGE AND T.PRODUCTSPECNAME = M4.MATERIALSPECNAME  ");
		sql.append(" LEFT JOIN MATERIALSHIPREQUEST M ON ");
		sql.append(" T.REQUESTNAME = M.SHIPREQUESTNAME ");
		sql.append(" LEFT JOIN MATERIALSHIPREQUESTDETAIL M2 ON ");
		sql.append(" T.REQUESTNAME = M2.SHIPREQUESTNAME  ");
		sql.append(" AND T.PRODUCTSPECNAME = M2.MATERIALSPECNAME  ");
		sql.append(" AND T.SDK_ID = M2.SDK_ID  ");
		sql.append(" LEFT JOIN SDK_SPEC SS ON T.SDK_ID = SS.SDK_ID ");
		sql.append(" LEFT JOIN MATERIALSPEC M3 ON T.PRODUCTSPECNAME  = M3.MATERIALSPECNAME  ");
		sql.append(" WHERE (T.RECEIVE_FLAG IS NULL OR T.RECEIVE_FLAG <> 'Y') ");
		sql.append(" AND T.LOTNAME = :MATERIALPACKINGNAME ");
		//		sql.append(" AND M.SITENAME = :SITENAME ");
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("MATERIALPACKINGNAME", materialPackingName);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString(), hashMap);
		if(queryForList == null || queryForList.size() < 1) {
			new CustomException("不存在要入库的信息");
		}
		
		
		//判断标签是否存在，存在就更新不存在就创建新的入库
		String judgeSql = "SELECT * FROM MATERIALPACKING m WHERE m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
		List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(judgeSql, hashMap);
		if  (queryForList2 == null || queryForList2.size() < 1) {
			//入库信息存入库存表
			CreateInfo createInfo = new CreateInfo();
			createInfo.setSiteName(siteName);
			createInfo.setMaterialPackingName(materialPackingName);// 新生成的box id
			createInfo.setMaterialPackingType("Box");
			createInfo.setContentMaterialType("");
			Map<String, Object> bindMap = new HashMap<String, Object>();
			String packingGrade = queryForList.get(0).get("PACKINGGRADE") == null ? "" : queryForList.get(0).get("PACKINGGRADE").toString();
			if (!"".equals(packingGrade)) {
				bindMap.put("oqaResultState", GenericServiceProxy.getConstantMap().OQA_State_End);
				bindMap.put("oqaResult",packingGrade);
				bindMap.put("packingGrade", packingGrade);
			} else {
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);
			}
			
			
			bindMap.put("lkMaterialPackingName", palletName);
			bindMap.put("MaterialType", queryForList.get(0).get("MATERIALTYPE"));
			bindMap.put("MaterialSpecName", queryForList.get(0).get("MATERIALSPECNAME"));
			bindMap.put("PackingGrade", queryForList.get(0).get("PACKINGGRADE"));
			bindMap.put("SubPackingQuantity",queryForList.get(0).get("MATERIALQUANTITY"));
			bindMap.put("MaterialCreateQuantity",
					queryForList.get(0).get("MATERIALQUANTITY"));
			bindMap.put("MaterialQuantity", queryForList.get(0).get("MATERIALQUANTITY"));// 数量
			bindMap.put("PackingState", "Released");
			bindMap.put("OldPackingState", "Released");
			bindMap.put("stockState", "Created");
			bindMap.put("OldStockState", "Created");
			bindMap.put("truegg", queryForList.get(0).get("TRUEGG"));
			bindMap.put("HoldState", "N");
			bindMap.put("AreaName", queryForList.get(0).get("AREANAME"));
			bindMap.put("LocationName", locationName);
			bindMap.put("charge",queryForList.get(0).get("CHARGE"));
			bindMap.put("DurableName", durableName);
			bindMap.put("MaterialProcessGroupName",
					"");
			bindMap.put("LastEventTimeKey",
					TimeStampUtil.getCurrentEventTimeKey());
			bindMap.put("receiveRequestName", SHIPRequestName);
			bindMap.put("productOrder", SHIPRequestName);
			bindMap.put("receiveRequestDetailName",SHIPRequestDetailName);
			//如果queryForList.get(0).get("SHIPTIME")为null获取当前时间字符串"YYYY-MM-DD"
			String makeDate = queryForList.get(0).get("SHIPTIME") == null ? TimeStampUtil.getCurrentTime("yyyy-MM-dd") : queryForList.get(0).get("SHIPTIME").toString();


			bindMap.put("MakeDate", makeDate);

			if(queryForList.get(0).get("EXPIRINGDATE") == null || queryForList.get(0).get("EXPIRINGDATE").toString().isEmpty()) {
				bindMap.put("expiringDate", getExpridate( makeDate, queryForList.get(0).get("MATERIALSPECNAME").toString()));
			}else {
				bindMap.put("expiringDate", queryForList.get(0).get("EXPIRINGDATE").toString());
			}

			bindMap.put("SHIPTime", eventInfo.getEventTime());
			bindMap.put("SHIPUser", eventInfo.getEventUser());
			bindMap.put("ReceiveTime", eventInfo.getEventTime());
			bindMap.put("ReceiveUser", eventInfo.getEventUser());
			bindMap.put("ShipTime",queryForList.get(0).get("SHIPTIME"));
			
			bindMap.put("MJPC",queryForList.get(0).get("MJPC"));
			bindMap.put("RETURNPC",queryForList.get(0).get("RETURNPC"));
			
			bindMap.put("ShipUser",queryForList.get(0).get("SHIPUSER"));
			bindMap.put("ErpLocation", erpLocation);
			bindMap.put("ErpFactory", erpFactory);
			bindMap.put("receiveActNo", SHIPActNo);
			bindMap.put("locationName", locationName);
			bindMap.put("StockInType", "Return");
			bindMap.put("SDK_ID", queryForList.get(0).get("SDK_ID"));
			bindMap.put("PHASE", queryForList.get(0).get("PHASE"));
			bindMap.put("unit", queryForList.get(0).get("UNIT"));
			bindMap.put("CAREER_ASSISTANCE", queryForList.get(0).get("CAREER_ASSISTANCE"));//业助信息
			bindMap.put("BUSINESS_UNIT", queryForList.get(0).get("BUSINESS_UNIT"));//业务
			bindMap.put("SALESPERSON", queryForList.get(0).get("SALESPERSON"));//事业部
			bindMap.put("CUSTOMNO", queryForList.get(0).get("CUSTOMNO"));//客户
			bindMap.put("remark", queryForList.get(0).get("REMARK"));//备注
			createInfo.setUserColumns(bindMap);
			MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName,materialPackingName);
			MaterialPackingServiceProxy.getMaterialPackingService().create(
					eventInfo, createInfo);
		}  else {
			SetEventInfo setEventInfo = new SetEventInfo();
			Map<String, Object> bindMap = new HashMap<String, Object>();
			String packingGrade = queryForList.get(0).get("PACKINGGRADE") == null ? "" : queryForList.get(0).get("PACKINGGRADE").toString();
			if (!"".equals(packingGrade)) {
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_End);
				bindMap.put("oqaResult",packingGrade);
				bindMap.put("packingGrade", packingGrade);
			} else {
				bindMap.put("oqaResultState",GenericServiceProxy.getConstantMap().OQA_State_Pre);
			}
			bindMap.put("ReceiveTime", eventInfo.getEventTime());
			bindMap.put("ReceiveUser", eventInfo.getEventUser());
			bindMap.put("ErpLocation", erpLocation);
			bindMap.put("ErpFactory", erpFactory);
			bindMap.put("locationName", locationName);
			bindMap.put("MaterialQuantity", queryForList.get(0).get("MATERIALQUANTITY"));// 数量
			bindMap.put("PackingState", "Released");
			bindMap.put("OldPackingState", "Released");
			bindMap.put("stockState", "Created");
			bindMap.put("SDK_ID", queryForList.get(0).get("SDK_ID"));
			bindMap.put("PHASE", queryForList.get(0).get("PHASE"));
			bindMap.put("unit", queryForList.get(0).get("UNIT"));
			bindMap.put("MJPC",queryForList.get(0).get("MJPC"));
			bindMap.put("RETURNPC",queryForList.get(0).get("RETURNPC"));
			bindMap.put("receiveActNo", SHIPActNo);
			bindMap.put("LastEventTimeKey",TimeStampUtil.getCurrentEventTimeKey());
			bindMap.put("receiveRequestName", SHIPRequestName);
			bindMap.put("receiveRequestDetailName",SHIPRequestDetailName);
			bindMap.put("CAREER_ASSISTANCE", queryForList.get(0).get("CAREER_ASSISTANCE"));//业助信息
			bindMap.put("BUSINESS_UNIT", queryForList.get(0).get("BUSINESS_UNIT"));//业务
			bindMap.put("SALESPERSON", queryForList.get(0).get("SALESPERSON"));//事业部
			bindMap.put("CUSTOMNO", queryForList.get(0).get("CUSTOMNO"));//客户
			bindMap.put("charge",palletName.split("\\|")[4]);
			setEventInfo.setUserColumns(bindMap);
			MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",materialPackingName);
			MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, eventInfo, setEventInfo);
		}
		
		return true;
	}
	
	
	
	/**
	 * 
	 * @param RECEIVEACTNO
	 * @param eventUser
	 * @return
	 * @throws IOException 
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 */
	@Transactional(rollbackFor = Exception.class)
	public String ReturnStockIn (String RECEIVEACTNO, String eventUser,String commitDate,String opCode) throws Exception {
		String billCode = ToSAPServiceImpl.ReturnStockInNew("SDK", eventUser, RECEIVEACTNO,commitDate,opCode);
		if (billCode == null) {
			throw new RuntimeException("SAP接口过账失败");
		}
		EventInfo makeEventInfo = new EventInfoUtil().makeEventInfo("ReturnStockIn", eventUser, "ReturnStockIn");
		//更新抬头文本状态和Box状态
		String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M WHERE M.RECEIVEACTNO = :RECEIVEACTNO";
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("RECEIVEACTNO", RECEIVEACTNO);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
		for (Map<String, Object> map : queryForList) {
			MaterialPackingKey materialPackingKey = new MaterialPackingKey(map.get("SITENAME").toString(),map.get("MATERIALPACKINGNAME").toString());
			SetEventInfo setEventInfo = new SetEventInfo();
			Map<String, Object> bindMap = new HashMap<String, Object>();
			bindMap.put("unDoID", billCode);
			bindMap.put("stockState", "Stocked");
			setEventInfo.setUserColumns(bindMap);
			MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
			
			String sql2 = "UPDATE BS_MES_SHIPPED T SET T.RECEIVE_FLAG = 'Y', T.RECEIVE_TIME = SYSDATE WHERE T.LOTNAME  = :LOTNAME";
			Map<String, Object> hashMap2 = new HashMap<String,Object> ();
			hashMap2.put("LOTNAME", map.get("MATERIALPACKINGNAME").toString());
			IDMFrameServiceProxy.getSqlTemplate().update(sql2, hashMap2);
			
			String sql3 = "UPDATE MATERIALRECEIVEACT T SET T.RECEIVEACTSTATE = 'Received' WHERE T.RECEIVEACTNO = :RECEIVEACTNO";
			Map<String, Object> hashMap3 = new HashMap<String,Object> ();
			hashMap2.put("RECEIVEACTNO", RECEIVEACTNO);
			IDMFrameServiceProxy.getSqlTemplate().update(sql3, hashMap2);
		}
		SaveUnDoInfo( billCode,commitDate);
		//更新MES标识
		return billCode;
		
	}
	
	//重写退料入库过账。同组织使用转库单（BLSQ4K）过账接口，跨组织使用调拨（4E）入库接口。zd.2024.9.2
	@Transactional
	public  boolean ReturnStockInNew(String pc, String eventUser, String commitDate, String opCode) throws Exception {
		
		String siteName="SDK";
		// 根据退库Box查询有多少退库单据，分开过账
		String wgs="SELECT DISTINCT m.RECEIVEREQUESTNAME,m.ERPLOCATION,(SELECT m2.SHIPREQUESTTYPE FROM MATERIALSHIPREQUEST m2 \r\n" + 
				" WHERE m2.SHIPREQUESTNAME=m.RECEIVEREQUESTNAME) AS SHIPREQUESTTYPE \r\n" + 
				" FROM MATERIALPACKING m WHERE m.SITENAME =:SITENAME AND  m.MATERIALPACKINGNAME IN " +pc;
//		String wgs = "SELECT DISTINCT m.RECEIVEREQUESTNAME,m.ERPLOCATION FROM MATERIALPACKING m WHERE m.MATERIALPACKINGNAME IN "
//				+ pc;
		Map<String, Object> hashMap3 = new HashMap<String, Object>();
		hashMap3.put("SITENAME", siteName);
		List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(wgs, hashMap3);
		
		//判断批次是否数量是否满足单据需求
		for (int i = 0; i < queryForList2.size(); i++) {
			String receiveRequestName = queryForList2.get(i).get("RECEIVEREQUESTNAME").toString();
			String erpLocation = queryForList2.get(i).get("ERPLOCATION").toString();
//			String shipRequestType=queryForList2.get(i).get("SHIPREQUESTTYPE").toString();
			String billCode="";
			ToSAPServiceImpl.ReturnStockInNew("SDK", eventUser, receiveRequestName, commitDate,opCode);
			if (billCode.length() == 0) {
				throw new GlobalException("报送ERP失败,请联系IT处理！");
			}
			EventInfo makeEventInfo = new EventInfoUtil().makeEventInfo("ReturnStockIn", eventUser, "ReturnStockIn");
			// 更新抬头文本状态和Box状态
			String sql = "SELECT M.SITENAME ,M.MATERIALPACKINGNAME FROM MATERIALPACKING M WHERE  M.RECEIVEREQUESTNAME = :RECEIVEREQUESTNAME AND ERPLOCATION = :ERPLOCATION   AND m.MATERIALPACKINGNAME IN "
					+ pc;
			Map<String, Object> hashMap = new HashMap<String, Object>();
			hashMap.put("RECEIVEREQUESTNAME", receiveRequestName);
			hashMap.put("ERPLOCATION", erpLocation);
			List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			for (Map<String, Object> map : queryForList) {
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(map.get("SITENAME").toString(),
						map.get("MATERIALPACKINGNAME").toString());
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("unDoID", billCode);
				bindMap.put("stockState", "Stocked");
				setEventInfo.setUserColumns(bindMap);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo,
						setEventInfo);
				// 更新MES标识
				String sql2 = "UPDATE BS_MES_SHIPPED T SET T.RECEIVE_FLAG = 'Y', T.RECEIVE_TIME = SYSDATE WHERE T.LOTNAME  = :LOTNAME";
				Map<String, Object> hashMap2 = new HashMap<String, Object>();
				hashMap2.put("LOTNAME", map.get("MATERIALPACKINGNAME").toString());
				IDMFrameServiceProxy.getSqlTemplate().update(sql2, hashMap2);
			}
			SaveUnDoInfo(billCode, commitDate);
		}
		
		return true;

	}
	
	/**
	 * 接收ERP返回消息更新WMS凭证
	 * @param undoid
	 */
	public void SaveUnDoInfo( String undoid,String commitDate) {

		String insertUnDOMaterialPackingInfo = "insert into BS_MATERIALPACKINGUNDOINFO (unDoID,preundoid,UNDODETAILNAME,undoidstate,UNDOIDDATE,"
				+ "SITENAME, MATERIALPACKINGNAME, MATERIALPACKINGTYPE, CONTENTMATERIALTYPE,"
				+ "MATERIALTYPE, MATERIALSPECNAME, SUPERMATERIALPACKINGNAME, PACKINGGRADE, SUBPACKINGQUANTITY, MATERIALCREATEQUANTITY,"
				+ "MATERIALQUANTITY, PACKINGSTATE, OLDPACKINGSTATE, STOCKSTATE, OLDSTOCKSTATE, HOLDSTATE, AREANAME, LOCATIONNAME,"
				+ "DURABLENAME, MATERIALPROCESSGROUPNAME, LASTEVENTNAME, LASTEVENTTIMEKEY, LASTEVENTTIME, LASTEVENTUSER,"
				+ "LASTEVENTCOMMENT, CREATETIME, CREATEUSER, RECEIVEREQUESTNAME, RECEIVEREQUESTDETAILNAME,"
				+ "MAKEDATE, RECEIVEACTNO, STOCKINTYPE, RECEIVETIME, RECEIVEUSER, SHIPREQUESTNAME, SHIPREQUESTDETAILNAME, SHIPTIME,"
				+ "SHIPUSER, REASONCODETYPE, OQARESULTSTATE, OQARESULT, ERPLOCATION, ERPFACTORY, REASONCODE, CHARGE, SUPPLIERNAME,ETCSTATE,ERPEVENTTIME,NPFLAG)"
				+ "select unDoID,'' as preundoid,UNDODETAILNAME,'Completed' as undoidstate,:commitDate AS UNDOIDDATE," //
				+ "SITENAME, MATERIALPACKINGNAME, MATERIALPACKINGTYPE, CONTENTMATERIALTYPE,"
				+ "MATERIALTYPE, MATERIALSPECNAME, SUPERMATERIALPACKINGNAME, PACKINGGRADE, SUBPACKINGQUANTITY, MATERIALCREATEQUANTITY,"
				+ "MATERIALQUANTITY, PACKINGSTATE, OLDPACKINGSTATE, STOCKSTATE, OLDSTOCKSTATE, HOLDSTATE, AREANAME, LOCATIONNAME,"
				+ "DURABLENAME, MATERIALPROCESSGROUPNAME, LASTEVENTNAME, LASTEVENTTIMEKEY, LASTEVENTTIME, LASTEVENTUSER,"
				+ "LASTEVENTCOMMENT, CREATETIME, CREATEUSER, RECEIVEREQUESTNAME, RECEIVEREQUESTDETAILNAME,"
				+ "MAKEDATE, RECEIVEACTNO, STOCKINTYPE, RECEIVETIME, RECEIVEUSER, SHIPREQUESTNAME, SHIPREQUESTDETAILNAME, SHIPTIME,"
				+ "SHIPUSER, REASONCODETYPE, OQARESULTSTATE, OQARESULT, ERPLOCATION, ERPFACTORY, REASONCODE, CHARGE, SUPPLIERNAME,ETCSTATE,ERPEVENTTIME,NPFLAG"
				+ " from MATERIALPACKING  "
				+ " where unDoID=:UNDOID";

		Map<String, Object> bindMap_insert = new HashMap<String, Object>();
		bindMap_insert.put("UNDOID", undoid);
		bindMap_insert.put("commitDate", commitDate);
		IDMFrameServiceProxy.getSqlTemplate()
				.update(insertUnDOMaterialPackingInfo, bindMap_insert);

	}

	public  EventInfo makeEventInfo( String eventName, String eventUser, String eventComment,
			String reasonCodeType, String reasonCode )
	{
		EventInfo eventInfo = new EventInfo();
		
		eventInfo.setBehaviorName( "" );
		eventInfo.setEventName( eventName );
		eventInfo.setEventUser( eventUser );
		
		if ( eventComment == null )
			eventComment = "";
		
		eventInfo.setEventComment( eventComment );
		eventInfo.setEventTime( TimeStampUtil.getCurrentTimestamp());
		// Do Not Check TimeKey Validation
		eventInfo.setCheckTimekeyValidation(false);
		eventInfo.setEventTimeKey(TimeStampUtil.getCurrentEventTimeKey());
		if ( reasonCodeType == null )
			reasonCodeType = "";
		
		eventInfo.setReasonCodeType( reasonCodeType );
		
		if ( reasonCode == null )
			reasonCode = "";
		
		eventInfo.setReasonCode( reasonCode );
		
		return eventInfo;
	}
	
	
	public static String getExpridate(String makeDate,String materialSpecName) {
		String sql = "SELECT NVL(T.EXPIRATIONDAY,180) +   TO_TIMESTAMP(SUBSTR(:MAKEDATE,0,19),'YYYY-MM-DD HH24:MI:SS') EXP FROM MATERIALSPEC T WHERE T.MATERIALSPECNAME = :MATERIALSPECNAME";
		
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("MATERIALSPECNAME", materialSpecName);
		hashMap.put("MAKEDATE", makeDate);
		List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
		
		if (queryForList != null && queryForList.size() > 0) {
			return queryForList.get(0).get("EXP").toString();
		}
		return "";
	}

}
