package com.cim.idm.service.Impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cim.idm.data.ErpMessageLog;
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.sys.SystemPropHelper;
import com.cim.idm.framework.util.time.TimeStampUtil;
import com.cim.idm.mwmsextend.generic.util.CommonValidation;
import com.cim.idm.mwmsextend.materialpacking.service.MaterialPackingServiceImpl;
import com.cim.idm.mwmsextend.materialpacking.service.MaterialPackingServiceUtil;
import com.cim.idm.mwmsextend.materialshiprequest.service.MaterialShipRequestServiceImpl;
import com.cim.idm.service.InvoiceService;
import com.cim.idm.service.impl.MESServiceImpl;
import com.cim.idm.service.impl.ToSAPServiceImpl;
import com.cim.idm.util.MessageLogUtil;
import com.cim.idm.util.ToSAPMessageUtil;
import com.cim.idm.utils.CommonUtils;
import com.cim.idm.utils.EventInfoUtil;
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.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.collections4.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class InvoiceServiceImpl implements InvoiceService {

	@Autowired
	private ToSAPMessageUtil toSAPMessageUtil;

	@Autowired
	private ToSAPServiceImpl toSAPService;

	private MaterialShipRequestServiceImpl ms = new MaterialShipRequestServiceImpl();

	private MaterialPackingServiceImpl mp = new MaterialPackingServiceImpl();

	private CommonValidation comm = new CommonValidation ();
	private CommonUtils untils=new CommonUtils();
	/**
	 * 创建入库单
	 * @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  RECEIVEDQUANTITY FROM MATERIALRECEIVEREQUESTDETAIL WHERE SITENAME=:sitename AND RECEIVEREQUESTNAME=:receiveRequestName AND RECEIVEREQUESTDETAILNAME=:ReceiveRequestDetailName";
		String sqlFU="UPDATE MATERIALRECEIVEREQUESTDETAIL SET RECEIVEDQUANTITY=:receivedQuantity WHERE SITENAME=:sitename AND RECEIVEREQUESTNAME=:receiveRequestName AND RECEIVEREQUESTDETAILNAME=:ReceiveRequestDetailName";
		List<Map<String, Object>> queryResultF = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlF, bindMapF);
		if(queryResultF.size()!=0){
			receivedQuantiryF=queryResultF.get(0).get("RECEIVEDQUANTITY").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 String SendERPStockOut20240802(String shipRequestName, String siteName, String eventUser,
										  List<MaterialPacking> list , String commitDate, String opCode) throws Exception {
		//判断单据类型调用不同的NC接口
		String sql = "SELECT m.SHIPREQUESTTYPE,m.SHIPREQUESTDETAILTYPE FROM MATERIALSHIPREQUEST m WHERE m.SHIPREQUESTNAME = :SHIPREQUESTNAME";
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		List<String> materialPackingKeyList = new ArrayList<> ();
		List<MaterialPackingKey> arrayList = new ArrayList<MaterialPackingKey> ();
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
		String shipRequestType = queryForList.get(0).get("SHIPREQUESTTYPE").toString();
		String detailype = queryForList.get(0).get("SHIPREQUESTDETAILTYPE").toString();
		if ("4C".equals(shipRequestType)) {
			String sql222 = "SELECT SUM(m.REQUESTQUANTITY - m.ASSIGNEDQUANTITY)  QTY,MATERIALSPECNAME FROM MATERIALSHIPREQUESTDETAIL m WHERE m.SHIPREQUESTNAME  = :SHIPREQUESTNAME GROUP BY MATERIALSPECNAME";
			List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql222, hashMap);
			for (int i = 0; i < queryForList2.size(); i++) {
				String string = queryForList2.get(i).get("QTY").toString();
				String MATERIALSPECNAME = queryForList2.get(i).get("MATERIALSPECNAME").toString();
				BigDecimal bigDecimal = new BigDecimal(string);
				if (bigDecimal.compareTo(BigDecimal.ZERO) < 0 ) {
					throw new CustomException( MATERIALSPECNAME + "销售不允许超发");
				}
			}
		}
		//备份过账条码表的信息，并删除
		String bfSql = "INSERT\r\n" + 
				"	INTO\r\n" + 
				"	MATERIALPACKINGSUBHISTORY T (T.MATERIALPACKINGNAME,\r\n" + 
				"	T.RECEIVEREQUESTNAME,\r\n" + 
				"	T.RECEIVEREQUESTDETAILNAME,\r\n" + 
				"	T.QTY)\r\n" + 
				"SELECT\r\n" + 
				"	M.MATERIALPACKINGNAME,\r\n" + 
				"	M.RECEIVEREQUESTNAME,\r\n" + 
				"	M.RECEIVEREQUESTDETAILNAME,\r\n" + 
				"	M.QTY\r\n" + 
				"FROM\r\n" + 
				"	MATERIALPACKINGSUB M\r\n" + 
				"WHERE\r\n" + 
				"	M.RECEIVEREQUESTNAME = :SHIPREQUESTNAME";
		String scSql = "DELETE FROM MATERIALPACKINGSUB WHERE RECEIVEREQUESTNAME  = :SHIPREQUESTNAME";
		IDMFrameServiceProxy.getSqlTemplate().update(bfSql, hashMap);
		IDMFrameServiceProxy.getSqlTemplate().update(scSql, hashMap);
		EventInfoUtil eventInfoUtil = new EventInfoUtil();
		EventInfo makeEventInfo = eventInfoUtil.makeEventInfo("CompletedShipRequest", eventUser, "CompletedShipRequest");
		//循环过账批次
		for (int i = 0; i < list.size(); ++ i) {
			String materialPackingName = list.get(i).getMaterialPackingName();
			String sdk_ID = list.get(i).getSDK_ID();
			String phase = list.get(i).getPHASE();
			String materialSpecName = list.get(i).getMaterialSpecName();
			//			float materialQuantity = list.get(i).getMaterialQuantity();
			//重新获取下数量，通过sql查询取数
			String SLsQL = "SELECT m.MATERIALPACKINGNAME,m.MATERIALQUANTITY,"
					+ "DECODE(NVL(m.MATERIALQUANTITY2,0), 0 ,m.MATERIALQUANTITY,m.MATERIALQUANTITY2) MATERIALQUANTITY2  FROM MATERIALPACKING m"
					+ " WHERE m.SHIPREQUESTNAME = :SHIPRQEUESTNAME AND MATERIALPACKINGNAME = :MATERIALPACKINGNAME ";
			Map<String, Object> hashMap2 = new HashMap<String,Object> ();
			hashMap2.put("SHIPRQEUESTNAME", shipRequestName);
			hashMap2.put("MATERIALPACKINGNAME", materialPackingName);
			String materialQuantity2 = list.get(i).getMaterialQuantity2() + "";
			List<Map<String, Object>> queryForList3 = IDMFrameServiceProxy.getSqlTemplate().queryForList(SLsQL, hashMap2);
			if (queryForList3 != null && queryForList3.size() > 0) {
				materialQuantity2 = queryForList3.get(0).get("MATERIALQUANTITY2").toString();
			} else {
				throw new CustomException("单据" + shipRequestName + "库存"  + materialPackingName  + "绑定关系不存在");
			}
			//循环单据查询过帐表
			String pcSql = "SELECT SHIPREQUESTNAME, SHIPREQUESTDETAILNAME,\r\n" + 
					"	m.MATERIALSPECNAME ,\r\n" + 
					"	m.PHASE ,\r\n" + 
					"	m.SDK_ID ,\r\n" + 
					"	m.REQUESTQUANTITY ,\r\n" + 
					"	m.ASSIGNEDQUANTITY ,\r\n" + 
					"	(m.REQUESTQUANTITY - nvl(m.SALEOUTQUANTITY, 0) ) SALEOUTQUANTITY\r\n" + 
					"FROM\r\n" + 
					"	MATERIALSHIPREQUESTDETAIL m\r\n" + 
					"WHERE\r\n" + 
					"	m.MATERIALSPECNAME = :MATERIALSPECNAME\r\n" + 
					"	AND (:SDK_ID IS NULL\r\n" + 
					"		OR SDK_ID = :SDK_ID)\r\n" + 
//					"	AND (:PHASE IS NULL\r\n" +
//					"		OR PHASE = :PHASE)\r\n" +
					"	AND SHIPREQUESTNAME = :SHIPREQUESTNAME  ORDER BY TO_NUMBER(SHIPREQUESTDETAILNAME) ASC ";
			//AND (m.REQUESTQUANTITY - nvl(m.SALEOUTQUANTITY, 0)) > 0
			hashMap.put("MATERIALSPECNAME", materialSpecName);
			hashMap.put("SDK_ID", sdk_ID);
			hashMap.put("PHASE", phase);
			List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(pcSql, hashMap);
			BigDecimal pcQty = new BigDecimal(materialQuantity2);//批次数量
			BigDecimal temp;
			for (int j = 0; j < queryForList2.size(); j++) {
				String sq = queryForList2.get(j).get("SALEOUTQUANTITY").toString();//剩余发货数量
				String shipRequestDetailName = queryForList2.get(j).get("SHIPREQUESTDETAILNAME").toString();//行项
				BigDecimal saleQty = new BigDecimal(sq);//需要发货数量
				//修复Bug:发货刚好满足数量时，如有超发则不会写入SUB表.zd.2024.9.26
				//bugfix:销售出库  lxh 20240927
				if (pcQty.compareTo(BigDecimal.ZERO) == 0 
						 || (saleQty.compareTo(BigDecimal.ZERO) == 0  && (j != queryForList2.size() - 1) )
						) {
					continue;
				}
				int compareTo = saleQty.compareTo(pcQty);//大于等于0则批次全发,小于0则需要拆分
				int compareToZero=saleQty.compareTo(BigDecimal.ZERO);
				if (compareTo >= 0) {
					untils.fahuoSplit(materialPackingName, shipRequestName, shipRequestDetailName, pcQty);
					break;
				} else {//需要拆行
					if( (j != queryForList2.size() - 1) &&  compareToZero>0) {//判断是否是最后一行，不是最后一行就不允许超发，发货数量等于请求数量即拆
						untils.fahuoSplit(materialPackingName, shipRequestName, shipRequestDetailName, saleQty);
						pcQty = pcQty.subtract(saleQty);
						continue;
					} else {
						untils.fahuoSplit(materialPackingName, shipRequestName, shipRequestDetailName, pcQty);
						break;
					}
				}
			}
			materialPackingKeyList.add(materialPackingName);
//			MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",materialPackingName);
//			arrayList.add(materialPackingKey);
		}
		//判断会有多少个仓库发货，不同仓库需要分开走账
		StringBuffer cklx = new StringBuffer();
		cklx.append("SELECT DISTINCT m.ERPLOCATION FROM MATERIALPACKING m WHERE m.MATERIALPACKINGNAME IN ");
		String str = "";
		str += "(";
		for (String materialPacking : materialPackingKeyList) {
			str += "'" + materialPacking + "',";
		}
		str += "'')";
		List<Map<String, Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(cklx.toString() + str, hashMap);
		String billCode = "";
		switch (shipRequestType) {
			case "NLCC" : //STO调拨出库

				billCode=toSAPService.TransferStockOut_NLCC(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				//makeEventInfo = eventInfoUtil.makeEventInfo("CompleteShipRequest", eventUser, "CompleteShipRequest:"+shipRequestType);
				untils.SaveUnDoInfo_ForSap(materialPackingKeyList, billCode, makeEventInfo);
				break;

			case "01"://生产领料
			case "02"://生产补料
				// 判断是否是现场仓
				String IsSql = "SELECT m.ERPRECEIVELOCATION,m.ERPLOCATION,m.ERPRECEIVEFACTORY FROM MATERIALSHIPREQUESTDETAIL m WHERE  SHIPREQUESTNAME = :SHIPREQUESTNAME";
				Map<String,Object> bpc=new HashMap<>();
				bpc.put("SHIPREQUESTNAME", shipRequestName);
				List<Map<String,Object>> receiveList = IDMFrameServiceProxy.getSqlTemplate().queryForList(IsSql, bpc);
				String ERPRECEIVELOCATION = receiveList.get(0).get("ERPRECEIVELOCATION") == null ? "" : receiveList.get(0).get("ERPRECEIVELOCATION").toString();
				String ERPLOCATION = receiveList.get(0).get("ERPLOCATION") == null ? "" : receiveList.get(0).get("ERPLOCATION").toString();
				String ERPRECEIVEFACTORY = receiveList.get(0).get("ERPRECEIVEFACTORY") == null ? "" : receiveList.get(0).get("ERPRECEIVEFACTORY").toString();

				//判断是否为MES需要接收的物料
				String sqlc=" SELECT e.ENUMVALUE FROM MES_ENUMDEFVALUE e \r\n" +
						" WHERE e.ENUMNAME ='ConfirmReceiveArea'\r\n" +
						" AND e.ENUMVALUE in(\r\n" +
						" SELECT p.AREANAME FROM MES_PRODUCTREQUEST p \r\n" +
						" WHERE p.PRODUCTREQUESTNAME =(SELECT m.RECEIVEPRODUCTIONORDERNUMBER FROM MATERIALSHIPREQUESTDETAIL m \r\n" +
						" WHERE m.SHIPREQUESTNAME=:SHIPREQUESTNAME AND RECEIVEPRODUCTIONORDERNUMBER IS NOT NULL AND ROWNUM=1))";

				List<Map<String,Object>> src=IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlc, bpc);


				//MES需要接收，则只抛送数据。 或如果是现场仓,先移到现场仓
				if(src.size()>0 || !ERPRECEIVELOCATION.isEmpty()) {
					//new MESServiceImpl().NeedReceiveMaterialInfoSend_NEW(shipRequestName, "SDK", materialPackingKeyList);
					billCode="Y";
					String sqlZK=" SELECT\r\n" +
							"	t.MATERIALSPECNAME,m.MATERIALPACKINGNAME,\r\n" +
							"	t.ERPFACTORY,\r\n" +
							"	t.ERPLOCATION,\r\n" +
							"	m2.SHIPREQUESTDETAILTYPE,\r\n" +
							"	t.UNIT,\r\n" +
							"	m1.COSTCENTER,\r\n" +
							"	m1.SHIPREQUESTNAME,\r\n" +
							"	m1.SHIPREQUESTDETAILNAME,\r\n" +
							"	m1.ERPRECEIVELOCATION,\r\n" +
							"	m1.ERPRECEIVEFACTORY,\r\n" +
							"	m1.SENDSALESORDER ,m1.SENDSALESORDERDETAILNO,\r\n" +
							"	CASE WHEN m1.SENDSALESORDER IS NOT NULL THEN 'E' ELSE '' END SOBKZ,\r\n" +
							"	(\r\n" +
							"	SELECT\r\n" +
							"		RECEIVEPRODUCTIONORDERNUMBER\r\n" +
							"	FROM\r\n" +
							"		MATERIALSHIPREQUESTDETAIL m5\r\n" +
							"	WHERE\r\n" +
							"		m5.SHIPREQUESTNAME =:SHIPREQUESTNAME\r\n" +
							"		AND m5.MATERIALSPECNAME = t.MATERIALSPECNAME\r\n" +
							"	ORDER BY\r\n" +
							"		m5.USEDATE,\r\n" +
							"		m5.SHIPREQUESTDETAILNAME FETCH FIRST ROW ONLY)RECEIVEPRODUCTIONORDERNUMBER,\r\n" +
							"	SUM(m.QTY) AS MATERIALQUANTITY\r\n" +
							" FROM \r\n" +
							"		 MATERIALPACKINGSUB m\r\n" +
							" LEFT JOIN MATERIALPACKING T ON \r\n" +
							"		 m.MATERIALPACKINGNAME = T.MATERIALPACKINGNAME\r\n" +
							"	AND m.RECEIVEREQUESTNAME = T.SHIPREQUESTNAME\r\n" +
							" LEFT JOIN MATERIALSHIPREQUESTDETAIL M1  \r\n" +
							"		  ON \r\n" +
							"		 m.RECEIVEREQUESTNAME = M1.SHIPREQUESTNAME\r\n" +
							"	AND m.RECEIVEREQUESTDETAILNAME = M1.SHIPREQUESTDETAILNAME\r\n" +
							" LEFT JOIN MATERIALSHIPREQUEST M2  \r\n" +
							"		  ON \r\n" +
							"		 M1.SHIPREQUESTNAME = M2.SHIPREQUESTNAME\r\n" +
							" WHERE \r\n" +
							"		 T.SHIPREQUESTNAME = :SHIPREQUESTNAME\r\n" +
							"	AND T.SITENAME = :SITENAME\r\n" +
							"	AND T.STOCKSTATE = 'Stocked'\r\n" +
							"	AND T.MATERIALPACKINGNAME IN (:BOXLIST)\r\n" +
							" GROUP BY\r\n" +
							"	t.MATERIALSPECNAME,\r\n" +
							"	t.ERPFACTORY,\r\n" +
							"	t.ERPLOCATION,\r\n" +
							"	m2.SHIPREQUESTDETAILTYPE,\r\n" +
							"	t.UNIT,\r\n" +
							"	m1.COSTCENTER,\r\n" +
							"	m1.SHIPREQUESTNAME,m1.SENDSALESORDER ,m1.SENDSALESORDERDETAILNO,\r\n" +
							"	m1.SHIPREQUESTDETAILNAME,\r\n" +
							"	m1.ERPRECEIVELOCATION,\r\n" +
							"	m1.ERPRECEIVEFACTORY,m.MATERIALPACKINGNAME";

					// 转库到线边仓
//					String sqlZK = "SELECT b.MATERIALPACKINGNAME FROM MATERIALPACKINGSUB b WHERE b.MATERIALPACKINGNAME IN (:BOXLIST)";
					Map<String, Object> hashMapZK = new HashMap<String, Object>();
					hashMapZK.put("BOXLIST", materialPackingKeyList);
					hashMapZK.put("SHIPREQUESTNAME", shipRequestName);
					hashMapZK.put("SITENAME", siteName);
					List<Map<String, Object>> ZKqueryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlZK.toString(), hashMapZK);

					for (int z = 0; z < ZKqueryForList.size(); z++) {
						String materialPackingName = ZKqueryForList.get(z).get("MATERIALPACKINGNAME").toString();
						materialPackingKeyList.add(materialPackingName);
						MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",materialPackingName);
						arrayList.add(materialPackingKey);
					}
//					billCode = UnSourcedChangeLocation(arrayList,ERPRECEIVEFACTORY,
//							ERPRECEIVELOCATION, "", eventUser );

					List<MaterialPackingKey> mpKeyList = new ArrayList<MaterialPackingKey> ();
					SetEventInfo setEventInfo = new SetEventInfo();
					for (MaterialPackingKey materialPacking : arrayList) {
						MaterialPackingKey key = materialPacking;
						mpKeyList.add(key);
					}

					Map<String, Object> hashMapPack = new HashMap<String,Object> ();
					hashMapPack.put("erpLocation", ERPRECEIVELOCATION == null  ? "" : ERPRECEIVELOCATION);
					hashMapPack.put("erpFactory",  ERPRECEIVEFACTORY == null  ? "" : ERPRECEIVEFACTORY);
					hashMapPack.put("locationName", "");
					hashMapPack.put("lastEventUser", eventUser);
//					hashMapPack.put("unDoID", billCode);
					setEventInfo.setUserColumns(hashMapPack);
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(mpKeyList, makeEventInfo, setEventInfo);
					//抛送MES数据
					untils.sendMaterialInfoToMES_NEW2(shipRequestName, "", materialPackingKeyList);

					WlySaveUnDoInfo( billCode,commitDate);
				}
				else
				{

					billCode=ProductionStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
					if (billCode.isEmpty()) {
						throw new CustomException("SAP过账异常！");
					}
//					billCode=toSAPService.ProductionStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
					//抛送MES数据
					untils.sendMaterialInfoToMES_NEW(shipRequestName, "", materialPackingKeyList);
					//billCode = NCWServiceImpl.SaleOut2(shipRequestName, "SDK", eventUser,materialPackingKeyList,"");
					untils.SaveUnDoInfo_ForSap(materialPackingKeyList, billCode, makeEventInfo);

				}
				break;

			case "06"://成本中心领料
			case "09"://研发领料
//				billCode=toSAPService.orderStockOut(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				billCode=orderStockOut(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				//更新MES管芯数据
				new MESServiceImpl().updateDurable_ByCostcenter(shipRequestName, "SDK", eventUser, materialPackingKeyList);
				//billCode = NCWServiceImpl.SaleOut2(shipRequestName, "SDK", eventUser,materialPackingKeyList,"");
				untils.SaveUnDoInfo_ForSap(materialPackingKeyList, billCode, makeEventInfo);
				break;
			case "ZLF0"://销售出库确认 （单据类型ZLF0~ZLF8） SD023(销售回传)
			case "ZLF1":
			case "ZLF2":
			case "ZLF3":
			case "ZLF4":
			case "ZLF5":
			case "ZLF6":
			case "ZLF7":
			case "ZLF8":
//				billCode=toSAPService.saleStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				billCode=saleStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				untils.SaveUnDoInfo_ForSap(materialPackingKeyList, billCode, makeEventInfo);
				break;

			case "OEM":
//				billCode=toSAPService.oemStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				billCode=oemStockOut_NEW(shipRequestName, "SDK", eventUser,materialPackingKeyList);
				untils.SaveUnDoInfo_ForSap(materialPackingKeyList, billCode, makeEventInfo);
				break;
			default :
				break;
		}
		return billCode;
	}

	//无来源转库过账-转现场仓 （311）
	public String UnSourcedChangeLocation(List<MaterialPackingKey> all,
										 String aimFactory,
										 String aimERPlocation,
										 String aimLocationName,
										 String user) throws Exception {
		String rcode;
		String undoId = "";
		String rmsg = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append("  	SELECT   ").append(SystemPropHelper.CR)
				.append("       MP.MATERIALSPECNAME,   ").append(SystemPropHelper.CR)
				.append("       MP.ERPFACTORY ,   ").append(SystemPropHelper.CR)
				.append("       :AIMFACTORY AS UMWRK_RECIEVEFACTORY,   ").append(SystemPropHelper.CR)
				.append("       MP.ERPLOCATION,   ").append(SystemPropHelper.CR)
				.append("       :AIMERPLOCATION AS UMLGO_RECIEVEERPLOCATION,   ").append(SystemPropHelper.CR)
				.append("       MP.MATERIALQUANTITY ,   ").append(SystemPropHelper.CR)
				.append("       MP.UNIT,   ").append(SystemPropHelper.CR)
				.append("       :AIMLOCATIONNAME AS NULL_RECIVELOCAITONNAME   ").append(SystemPropHelper.CR)
				.append("   FROM   ").append(SystemPropHelper.CR)
				.append("  	    MATERIALPACKING MP   ").append(SystemPropHelper.CR)
				.append("   WHERE   ").append(SystemPropHelper.CR)
				.append("  	    MP.MATERIALPACKINGNAME IN    ").append(SystemPropHelper.CR);
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("AIMFACTORY", aimFactory);
		hashMap.put("AIMLOCATIONNAME", aimLocationName);
		hashMap.put("AIMERPLOCATION", aimERPlocation);
		String str = "";
		str += "(";
		for (MaterialPackingKey materialPackingKey : all) {
			str += "'" + materialPackingKey.getMaterialPackingName() + "',";
		}
		str += "'')";
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString() + str, hashMap);
		if (queryForList == null || queryForList.size() < 1) {
			return "";
		}
		try {

			JSONObject sendData = new JSONObject(true);
			JSONObject header = new JSONObject(true);
			sendData.put("HEAD", header);
			JSONObject body = new JSONObject(true);
			sendData.put("BODY", body);
			JSONArray itemArray = new JSONArray();

            /*
             HEAD
             */
			header.put("INTF_ID", "MM067");
			String uniqueID = UUID.randomUUID().toString();
			header.put("REQ_KEYID", uniqueID);
			header.put("SRC_MSGID", uniqueID);
			header.put("SRC_SYSTEM", "WMS");
			header.put("DEST_SYSTEM", "SAP");
			header.put("REQUSER", user);

            /*
             BODY
             */
			body.put("ITEM", itemArray);
			body.put("ITEMID", uniqueID);
			//过账日期
			body.put("BUDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证日期
			body.put("BLDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证抬头文本
			body.put("BKTXT", uniqueID);
			//用户名
			body.put("USNAM", user);

			for (Map<String, Object> mm : queryForList) {

				JSONObject item = new JSONObject(true);
				// MATNR 发货物料号
				item.put("MATNR", mm.get("MATERIALSPECNAME"));
				//UMMAT 收货物料
				item.put("UMMAT", mm.get("MATERIALSPECNAME"));
				// WERKS 发货工厂
				item.put("WERKS", mm.get("ERPFACTORY"));
				// LGORT 发货库存地点
				item.put("LGORT", mm.get("ERPLOCATION"));
				//UMWRK 收货工厂
				item.put("UMWRK", mm.get("UMWRK_RECIEVEFACTORY"));
				//UMLGO 收货库存地点
				item.put("UMLGO", mm.get("UMLGO_RECIEVEERPLOCATION"));
				//MENGE 数量
				item.put("MENGE", mm.get("MATERIALQUANTITY"));
				//MENGE 单位
				item.put("MEINS", mm.get("UNIT"));
				//BWART 移动类型
				item.put("BWART", "311");
//                //ZLLORDER 自定义领料单号
//                item.put("ZLLORDER", mm.get("SHIPREQUESTNAME"));
//                //ZLLITEM 自定义领料单行号
//                item.put("ZLLITEM", mm.get("SHIPREQUESTDETAILNAME"));

				itemArray.add(item);

			}
//			log.info("SendTOSAP >>>>" + sendData);

			String sapreturn = toSAPMessageUtil.sendHttpPost(toSAPMessageUtil.materialChangeLocationUrl, "", sendData.toJSONString());
			org.json.JSONObject receiveJsonObject = new org.json.JSONObject(sapreturn);
			org.json.JSONObject returnJsonObject = (org.json.JSONObject) receiveJsonObject.get("RETURN");
			rcode = returnJsonObject.get("STATUS").toString();
			rmsg = returnJsonObject.get("MSGTXT").toString();

			if ("S".equals(rcode)) {
				undoId = returnJsonObject.get("MBLNR").toString()+"_"+returnJsonObject.get("MJAHR").toString();//将物料凭证号与凭证年度拼在一起
			}
			//将log写到表里
			UUID uuid = UUID.randomUUID();
			ErpMessageLog erplog = new ErpMessageLog();
			erplog.setEventUser("");
			erplog.setServerName("WmsToErp");
			erplog.setEventName("无来源转库-311");
			erplog.setId(uuid.toString());
			erplog.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
			erplog.setMessageId(UUID.randomUUID().toString());
			erplog.setSendMsg(sendData.toJSONString());
			erplog.setSendMsg2(sendData.toJSONString());
			erplog.setReturnMsg2(sapreturn);
			erplog.setResultCode(rcode);
			MessageLogUtil.writeMessageLog(erplog);
		} catch (Exception e) {
//			log.info(e.getMessage(), e);
			throw new RuntimeException("SAP返回" + rmsg);
		}
		if (!"S".equals(rcode)) {
			throw new RuntimeException("SAP返回" + rmsg);
		}
		return undoId;
	}

	public void WlySaveUnDoInfo( 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);

	}


	/**
	 * 备份并清除特定发货请求的物料包装子记录
	 *
	 * 此方法首先将与特定发货请求关联的物料包装子记录备份到历史记录表中，
	 * 然后从当前表中删除这些记录，为新的过账操作做准备
	 *
	 * @param shipRequestName 发货请求的名称，用于标识需要备份和清除的记录
	 */
	private void backupMaterialPackingSub(String shipRequestName) {
	    // 备份过账条码表的信息，并删除
	    String bfSql = "INSERT\r\n" +
	            "    INTO\r\n" +
	            "    MATERIALPACKINGSUBHISTORY T (T.MATERIALPACKINGNAME,\r\n" +
	            "    T.RECEIVEREQUESTNAME,\r\n" +
	            "    T.RECEIVEREQUESTDETAILNAME,\r\n" +
	            "    T.QTY)\r\n" +
	            "SELECT\r\n" +
	            "    M.MATERIALPACKINGNAME,\r\n" +
	            "    M.RECEIVEREQUESTNAME,\r\n" +
	            "    M.RECEIVEREQUESTDETAILNAME,\r\n" +
	            "    M.QTY\r\n" +
	            "FROM\r\n" +
	            "    MATERIALPACKINGSUB M\r\n" +
	            "WHERE\r\n" +
	            "    M.RECEIVEREQUESTNAME = :SHIPREQUESTNAME";
	    String scSql = "DELETE FROM MATERIALPACKINGSUB WHERE RECEIVEREQUESTNAME  = :SHIPREQUESTNAME";
	    HashMap<String, Object> hashMap = new HashMap<>();
	    hashMap.put("SHIPREQUESTNAME", shipRequestName);
	    IDMFrameServiceProxy.getSqlTemplate().update(bfSql, hashMap);
	    IDMFrameServiceProxy.getSqlTemplate().update(scSql, hashMap);
	}

	/**
	 * 
	 * @param list
	 * @param undoid
	 * @param makeEventInfo
	 */
	public void SaveUnDoInfo(List<String> list,  String undoid, EventInfo makeEventInfo,String shipReuqestType,String erpLocations) {
		String updateUndoInfo = "UPDATE MATERIALPACKING T SET T.unDoID = :unDoID WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME AND T.ERPLOCATION = :ERPLOCATION";
		for (String s : list) {
			Map<String,Object> map = new HashMap<String,Object> ();
			map.put("MATERIALPACKINGNAME", s);
			map.put("ERPLOCATION", erpLocations);
			map.put("unDoID", undoid);
			IDMFrameServiceProxy.getSqlTemplate()
			.update(updateUndoInfo, map);
		}
		
		//更新Box状态，如果数量发货数量和条码数量相同则全部出货，小于则更新条码数量，取消单据解绑
		for (String s : list) {
			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,'Created' as undoidstate,:UNDOIDDATE AS UNDOIDDATE," //
					+ "SITENAME, MATERIALPACKINGNAME, MATERIALPACKINGTYPE, CONTENTMATERIALTYPE,"
					+ "MATERIALTYPE, MATERIALSPECNAME, SUPERMATERIALPACKINGNAME, PACKINGGRADE, SUBPACKINGQUANTITY, MATERIALCREATEQUANTITY,"
					+ "CASE WHEN MATERIALQUANTITY2 > 0 THEN  MATERIALQUANTITY2 ELSE MATERIALQUANTITY END, 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 AND MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> bindMap_insert = new HashMap<String, Object>();
			bindMap_insert.put("UNDOIDDATE", "");
			bindMap_insert.put("UNDOID", undoid);
			bindMap_insert.put("MATERIALPACKINGNAME", s);
			IDMFrameServiceProxy.getSqlTemplate()
			.update(insertUnDOMaterialPackingInfo, bindMap_insert);
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", s);
			hashMap.put("ERPLOCATION", erpLocations);
			hashMap.put("unDoID", undoid);
			if ("4Y".equals(shipReuqestType)) {
				String insertSql = "INSERT\r\n" + 
						"							INTO\r\n" + 
						"							MATERIALPACKING T (SITENAME , \r\n" + 
						"							MATERIALPACKINGNAME , \r\n" + 
						"							MATERIALPACKINGTYPE , \r\n" + 
						"							CONTENTMATERIALTYPE , \r\n" + 
						"							MATERIALTYPE , \r\n" + 
						"							MATERIALSPECNAME , \r\n" + 
						"							SUPERMATERIALPACKINGNAME, \r\n" + 
						"							PACKINGGRADE , \r\n" + 
						"							SUBPACKINGQUANTITY, \r\n" + 
						"							MATERIALCREATEQUANTITY, \r\n" + 
						"							PACKINGSTATE , \r\n" + 
						"							OLDPACKINGSTATE , \r\n" + 
						"							STOCKSTATE , \r\n" + 
						"							OLDSTOCKSTATE , \r\n" + 
						"							HOLDSTATE , \r\n" + 
						"							AREANAME , \r\n" + 
						"							LOCATIONNAME , \r\n" + 
						"							MATERIALPROCESSGROUPNAME , \r\n" + 
						"							LASTEVENTNAME , \r\n" + 
						"							LASTEVENTTIMEKEY , \r\n" + 
						"							LASTEVENTTIME, \r\n" + 
						"							LASTEVENTUSER , \r\n" + 
						"							LASTEVENTCOMMENT, \r\n" + 
						"							CREATETIME, \r\n" + 
						"							CREATEUSER , \r\n" + 
						"							RECEIVEREQUESTNAME , \r\n" + 
						"							RECEIVEREQUESTDETAILNAME , \r\n" + 
						"							MAKEDATE, \r\n" + 
						"							RECEIVEACTNO , \r\n" + 
						"							RECEIVETIME, \r\n" + 
						"							RECEIVEUSER , \r\n" + 
						"							SHIPREQUESTNAME , \r\n" + 
						"							SHIPREQUESTDETAILNAME , \r\n" + 
						"							SHIPTIME, \r\n" + 
						"							SHIPUSER , \r\n" + 
						"							REASONCODETYPE , \r\n" + 
						"							REASONCODE , \r\n" + 
						"							ERPLOCATION , \r\n" + 
						"							ETCSTATE , \r\n" + 
						"							ERPFACTORY , \r\n" + 
						"							STOCKINTYPE , \r\n" + 
						"							OQARESULTSTATE , \r\n" + 
						"							OQARESULT , \r\n" + 
						"							CHARGE , \r\n" + 
						"							SUPPLIERNAME , \r\n" + 
						"							UNDOID, \r\n" + 
						"							UNDODETAILNAME, \r\n" + 
						"							ERPEVENTTIME, \r\n" + 
						"							NPFLAG , \r\n" + 
						"							ERPSTATE, \r\n" + 
						"							MESSAGEID, \r\n" + 
						"							OQAMETHOD , \r\n" + 
						"							REMARK, \r\n" + 
						"							IMPROVMENTFLAG , \r\n" + 
						"							EXPIRINGDATE, \r\n" + 
						"							PROCESSSTATE , \r\n" + 
						"							ETCOUTQUANTITY, \r\n" + 
						"							MESRECEIVEFLAG , \r\n" + 
						"							MESRECEIVETIME, \r\n" + 
						"							RECEIPTOR , \r\n" + 
						"							DURABLENAME, \r\n" + 
						"							OEMOQAFLAG , \r\n" + 
						"							MATERIALQUANTITY, \r\n" + 
						"							FIFOFLAG , \r\n" + 
						"							SUPPLIERCHARGE, \r\n" + 
						"							SDK_ID , \r\n" + 
						"							SDKTRUEID , \r\n" + 
						"							MATURATIONINTIME, \r\n" + 
						"							MATURATIONOUTTIME, \r\n" + 
						"							PHASE, \r\n" + 
						"							ORG , \r\n" + 
						"							LKMATERIALPACKINGNAME, \r\n" + 
						"							SPECIALSTATE, \r\n" + 
						"							UNIT, \r\n" + 
						"							SHOPNAME, \r\n" + 
						"							MATURATIONTIME, \r\n" + 
						"							MATURATIONCYCLE, \r\n" + 
						"							SHELFNAME, \r\n" + 
						"							EXCEPTIONHANDLING, \r\n" + 
						"							ISCHECK, \r\n" + 
						"							PREERPLOCATION, \r\n" + 
						"							FUNIT, \r\n" + 
						"							FQTY, \r\n" + 
						"							TRUEGG, \r\n" + 
						"							BUSINESS_UNIT, \r\n" + 
						"							CAREER_ASSISTANCE, \r\n" + 
						"							SALESPERSON, \r\n" + 
						"							CUSTOMNO, \r\n" + 
						"							CUSTOMCHARGE, \r\n" + 
						"							PRELOCATIONNAME, \r\n" + 
						"							MATERIALQUANTITY2, \r\n" + 
						"							DURABLETYPE)\r\n" + 
						"						SELECT\r\n" + 
						"							m.SITENAME , \r\n" + 
						"							SYS_GUID() , \r\n" + 
						"							MATERIALPACKINGTYPE , \r\n" + 
						"							CONTENTMATERIALTYPE , \r\n" + 
						"							MATERIALTYPE , \r\n" + 
						"							m.MATERIALSPECNAME , \r\n" + 
						"							SUPERMATERIALPACKINGNAME, \r\n" + 
						"							PACKINGGRADE , \r\n" + 
						"							SUBPACKINGQUANTITY, \r\n" + 
						"							MATERIALCREATEQUANTITY, \r\n" + 
						"							PACKINGSTATE , \r\n" + 
						"							OLDPACKINGSTATE , \r\n" + 
						"							STOCKSTATE , \r\n" + 
						"							OLDSTOCKSTATE , \r\n" + 
						"							HOLDSTATE , \r\n" + 
						"							AREANAME , \r\n" + 
						"							'' , \r\n" + 
						"							MATERIALPROCESSGROUPNAME , \r\n" + 
						"							LASTEVENTNAME , \r\n" + 
						"							LASTEVENTTIMEKEY , \r\n" + 
						"							LASTEVENTTIME, \r\n" + 
						"							LASTEVENTUSER , \r\n" + 
						"							LASTEVENTCOMMENT, \r\n" + 
						"							CREATETIME, \r\n" + 
						"							CREATEUSER , \r\n" + 
						"							RECEIVEREQUESTNAME , \r\n" + 
						"							RECEIVEREQUESTDETAILNAME , \r\n" + 
						"							MAKEDATE, \r\n" + 
						"							RECEIVEACTNO , \r\n" + 
						"							RECEIVETIME, \r\n" + 
						"							RECEIVEUSER , \r\n" + 
						"							'' , \r\n" + 
						"							'' , \r\n" + 
						"							SHIPTIME, \r\n" + 
						"							SHIPUSER , \r\n" + 
						"							m.REASONCODETYPE , \r\n" + 
						"							m.REASONCODE , \r\n" + 
						"							m2.ERPRECEIVELOCATION , \r\n" + 
						"							ETCSTATE , \r\n" + 
						"							m2.ERPRECEIVEFACTORY , \r\n" + 
						"							STOCKINTYPE , \r\n" + 
						"							OQARESULTSTATE , \r\n" + 
						"							OQARESULT , \r\n" + 
						"							m.CHARGE , \r\n" + 
						"							SUPPLIERNAME , \r\n" + 
						"							UNDOID, \r\n" + 
						"							UNDODETAILNAME, \r\n" + 
						"							ERPEVENTTIME, \r\n" + 
						"							NPFLAG , \r\n" + 
						"							ERPSTATE, \r\n" + 
						"							MESSAGEID, \r\n" + 
						"							OQAMETHOD , \r\n" + 
						"							m.REMARK, \r\n" + 
						"							IMPROVMENTFLAG , \r\n" + 
						"							EXPIRINGDATE, \r\n" + 
						"							PROCESSSTATE , \r\n" + 
						"							ETCOUTQUANTITY, \r\n" + 
						"							MESRECEIVEFLAG , \r\n" + 
						"							MESRECEIVETIME, \r\n" + 
						"							RECEIPTOR , \r\n" + 
						"							DURABLENAME, \r\n" + 
						"							OEMOQAFLAG , \r\n" + 
						"							MATERIALQUANTITY2, \r\n" + 
						"							FIFOFLAG , \r\n" + 
						"							SUPPLIERCHARGE, \r\n" + 
						"							m.SDK_ID , \r\n" + 
						"							SDKTRUEID , \r\n" + 
						"							MATURATIONINTIME, \r\n" + 
						"							MATURATIONOUTTIME, \r\n" + 
						"							m.PHASE, \r\n" + 
						"							ORG , \r\n" + 
						"							LKMATERIALPACKINGNAME, \r\n" + 
						"							SPECIALSTATE, \r\n" + 
						"							m.UNIT, \r\n" + 
						"							SHOPNAME, \r\n" + 
						"							MATURATIONTIME, \r\n" + 
						"							MATURATIONCYCLE, \r\n" + 
						"							SHELFNAME, \r\n" + 
						"							EXCEPTIONHANDLING, \r\n" + 
						"							ISCHECK, \r\n" + 
						"							PREERPLOCATION, \r\n" + 
						"							FUNIT, \r\n" + 
						"							FQTY, \r\n" + 
						"							TRUEGG, \r\n" + 
						"							m.BUSINESS_UNIT, \r\n" + 
						"							m.CAREER_ASSISTANCE, \r\n" + 
						"							m.SALESPERSON, \r\n" + 
						"							CUSTOMNO, \r\n" + 
						"							CUSTOMCHARGE, \r\n" + 
						"							PRELOCATIONNAME, \r\n" + 
						"							0, \r\n" + 
						"							DURABLETYPE\r\n" + 
						"						FROM\r\n" + 
						"							MATERIALPACKING m\r\n" + 
						"						LEFT JOIN MATERIALSHIPREQUESTDETAIL m2 ON\r\n" + 
						"							m.SHIPREQUESTNAME = m2.SHIPREQUESTNAME\r\n" + 
						"							AND m.MATERIALSPECNAME = m2.MATERIALSPECNAME\r\n" + 
						"						WHERE\r\n" + 
						"							m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME AND m.ERPLOCATION = :ERPLOCATION";
				IDMFrameServiceProxy.getSqlTemplate().update(insertSql, hashMap);
			}
			
			MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",s);
			MaterialPacking selectByKey = MaterialPackingServiceProxy.getMaterialPackingService().selectByKey(materialPackingKey);
			Double materialQuantity = selectByKey.getMaterialQuantity();
			Double materialQuantity2 = selectByKey.getMaterialQuantity2();
			String erpLocation2 = selectByKey.getErpLocation();
			if (erpLocations.equals(erpLocation2)) {
				BigDecimal fmaterialQuantity = new BigDecimal(materialQuantity);
				BigDecimal fmaterialQuantity2 = new BigDecimal(materialQuantity2);
				if (fmaterialQuantity.compareTo(fmaterialQuantity2) > 0) {
					Map<String, Object> hashMap2 = new HashMap<String,Object> ();
					hashMap2.put("materialQuantity", fmaterialQuantity.subtract(fmaterialQuantity2) );
					hashMap2.put("materialQuantity2", 0);
					hashMap2.put("shipRequestName", "");
					hashMap2.put("shipRequestDetailName", "");
					SetEventInfo setEventInfo = new SetEventInfo();
					setEventInfo.setUserColumns(hashMap2);
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				} else {
					Map<String, Object> hashMap2 = new HashMap<String,Object> ();
					hashMap2.put("stockState", "StockOut");
					//				hashMap.put("shipRequestName", "");
					//				hashMap.put("shipRequestDetailName", "");
					SetEventInfo setEventInfo = new SetEventInfo();
					setEventInfo.setUserColumns(hashMap2);
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				}
			}
		}

	}
	
//	public static void main(String[] args) {
//		BigDecimal fmaterialQuantity = new BigDecimal("1063.1");
//		BigDecimal fmaterialQuantity2 = new BigDecimal("500");
//		System.out.println(fmaterialQuantity.subtract(fmaterialQuantity2) + "");
//	}

	/**
	 * 备货方法
	 * @param materialPacking
	 * @param shipReuqestName
	 * @param sdk_id
	 * @param type
	 * @param siteName
	 * @param user
	 * @throws CustomException
	 */
	@Transactional
	public void Reserve (MaterialPacking materialPacking, String shipReuqestName,
						 String materialSpecName, String sdk_id, String phase,
						 String type, String siteName, String user, String materialQuantity2, String fifoFlag) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
		Double materialQuantity = materialPacking.getMaterialQuantity();
		//			float materialQuantity2 = materialPacking.getMaterialQuantity2();
		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				} else if (!"".equals(invoice)  && invoice.equals(shipReuqestName)) {//备过货的不做处理
					return;
				}
				
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", shipReuqestName);
				hashMap2.put("fifoFlag", fifoFlag);
				setEventInfo.setUserColumns(hashMap2);
				hashMap2.put("fifoFlag", fifoFlag);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(shipReuqestName) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", "");
				hashMap2.put("fifoFlag", "");
				hashMap2.put("shipRequestDetailName", "");
				hashMap2.put("materialQuantity2", "0");
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				//更新备货数量
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity2);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}
		}
	}
	
	
	@Transactional
	public void ReserveByPallet (MaterialPacking materialPacking, String shipReuqestName,
								 String materialSpecName, String sdk_id, String phase,
								 String type, String siteName, String user, String materialQuantity2, String fifoFlag) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
		String materialQuantity = materialQuantity2;
		//			float materialQuantity2 = materialPacking.getMaterialQuantity2();
		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				} else if (!"".equals(invoice)  && invoice.equals(shipReuqestName)) {//备过货的不做处理
					return;
				}
				
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", shipReuqestName);
				hashMap2.put("fifoFlag", fifoFlag);
				hashMap2.put("materialQuantity2", materialQuantity);
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(shipReuqestName) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", "");
				hashMap2.put("fifoFlag", "");
				hashMap2.put("shipRequestDetailName", "");
				hashMap2.put("materialQuantity2", "0");
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				//更新备货数量
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity2);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}
		}
	}
	
	/**
	 * 备货方法3 MATERIALRECEIVEREQUEST表
	 * @param materialPacking
	 * @param sdk_id
	 * @param phase
	 * @param type
	 * @param siteName
	 * @param user
	 * @throws CustomException
	 */
	@Transactional
	public void Reserve3 (MaterialPacking materialPacking, String receiveRequestName, String receiveRequestDetailName,
						  String materialSpecName, String sdk_id, String phase,
						  String type, String siteName, String user, String materialQuantity2, String fifoFlag) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
		Double materialQuantity = materialPacking.getMaterialQuantity();
		//			float materialQuantity2 = materialPacking.getMaterialQuantity2();
		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(receiveRequestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", receiveRequestName);
				hashMap2.put("shipRequestDetailName", receiveRequestDetailName);
				hashMap2.put("fifoFlag", fifoFlag);
				setEventInfo.setUserColumns(hashMap2);
				hashMap2.put("fifoFlag", fifoFlag);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALRECEIVEREQUESTDETAIL m SET m.RECEIVEDQUANTITY = m.RECEIVEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.RECEIVEREQUESTNAME = :RECEIVEREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity);
				bindMap.put("SITENAME", siteName);
				bindMap.put("RECEIVEREQUESTNAME", receiveRequestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(receiveRequestName) && !invoice.equals(receiveRequestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", "");
				hashMap2.put("fifoFlag", "");
				hashMap2.put("shipRequestDetailName", "");
				hashMap2.put("materialQuantity2", "0");
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				//更新备货数量
				String updateSql= " UPDATE MATERIALRECEIVEREQUESTDETAIL m SET m.RECEIVEDQUANTITY = m.RECEIVEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.RECEIVEREQUESTNAME = :RECEIVEREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity2);
				bindMap.put("SITENAME", siteName);
				bindMap.put("RECEIVEREQUESTNAME", receiveRequestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}
		}
	}

	/**
	 * 备货方法2.为不影响其他功能，新复制原Reserve方法进行修改。zd.2024.9.10
	 * @param materialPacking
	 * @param shipReuqestName
	 * @param sdk_id
	 * @param type
	 * @param siteName
	 * @param user
	 * @throws CustomException
	 */
	@Transactional
	public void Reserve2 (MaterialPacking materialPacking, String shipReuqestName,
						  String materialSpecName, String sdk_id, String phase,
						  String type, String siteName, String user, String materialQuantity2, String fifoFlag) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
//		float materialQuantity = materialPacking.getMaterialQuantity();
//		float mqty2 = materialPacking.getMaterialQuantity2();
//		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME,MATERIALQUANTITY,SDK_ID,PHASE FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				phase = queryForList.get(0).get("PHASE") == null ? "" :queryForList.get(0).get("PHASE").toString();
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
//				SetEventInfo setEventInfo = new SetEventInfo();
//				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
//				//更新实际实际出库数量到库存表
//				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
//				hashMap2.put("shipRequestName", shipReuqestName);
//				hashMap2.put("fifoFlag", fifoFlag);
//				hashMap2.put("materialQuantity2", queryForList.get(0).get("MATERIALQUANTITY").toString());//先将Box数量记录至materialQuantity2
//				setEventInfo.setUserColumns(hashMap2);
//				hashMap2.put("fifoFlag", fifoFlag);
//				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				hashMap.put("materialQuantity2", queryForList.get(0).get("MATERIALQUANTITY").toString());
				hashMap.put("fifoFlag", fifoFlag);
				hashMap.put("shipRequestName", shipReuqestName);
				hashMap.put("SITENAME", siteName);
				
				String upMkSql = "UPDATE\r\n" + 
						"	MATERIALPACKING m\r\n" + 
						"SET\r\n" + 
						"	m.MATERIALQUANTITY2 = :materialQuantity2,\r\n" + 
						"	m.FIFOFLAG = :fifoFlag,\r\n" + 
						"	m.SHIPREQUESTNAME = :shipRequestName\r\n" + 
						"WHERE\r\n" + 
						"	m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME\r\n" + 
						"	AND m.SITENAME = :SITENAME";
				
				IDMFrameServiceProxy.getSqlTemplate().update(upMkSql, hashMap);
				
				
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) " +
//						"AND (:PHASE IS NULL OR PHASE = :PHASE) " +
						"AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", queryForList.get(0).get("MATERIALQUANTITY").toString());
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			//解绑根据查询出的materil信息拿到SDKID和阶段
//			sdk_id = materialPacking.getSDK_ID();
//			phase = materialPacking.getPHASE();
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME,CASE WHEN MATERIALQUANTITY2 > 0 THEN MATERIALQUANTITY2 ELSE MATERIALQUANTITY END MATERIALQUANTITY2,SDK_ID,PHASE FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				
				sdk_id = queryForList.get(0).get("SDK_ID") == null ? "" : queryForList.get(0).get("SDK_ID").toString();
				phase = queryForList.get(0).get("PHASE") == null ? "" : queryForList.get(0).get("PHASE") .toString();
				
				
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(shipReuqestName) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
							
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
//				SetEventInfo setEventInfo = new SetEventInfo();
//				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
//				//更新实际实际出库数量到库存表
//				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
//				hashMap2.put("shipRequestName", "");
//				hashMap2.put("fifoFlag", "");
//				hashMap2.put("shipRequestDetailName", "");
//				hashMap2.put("materialQuantity2", "0");
//				setEventInfo.setUserColumns(hashMap2);
//				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				hashMap.put("shipRequestName", "");
				hashMap.put("fifoFlag", "");
				hashMap.put("shipRequestDetailName", "");
				hashMap.put("materialQuantity2", "0");
				hashMap.put("SITENAME", siteName);
				String upMkSql = "UPDATE\r\n" + 
						"	MATERIALPACKING m\r\n" + 
						"SET\r\n" + 
						"	m.MATERIALQUANTITY2 = :materialQuantity2,\r\n" + 
						"	m.FIFOFLAG = :fifoFlag,\r\n" + 
						"	m.SHIPREQUESTNAME = :shipRequestName,\r\n" + 
						"	m.SHIPREQUESTDETAILNAME = :shipRequestDetailName\r\n" + 
						"WHERE\r\n" + 
						"	m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME\r\n" + 
						"	AND m.SITENAME = :SITENAME";
				IDMFrameServiceProxy.getSqlTemplate().update(upMkSql, hashMap);
				
				
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) " +
//						"AND (:PHASE IS NULL OR PHASE = :PHASE)" +
						"  AND ROWNUM = 1 ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				//bindMap.put("QTY", materialQuantity2);
				bindMap.put("QTY", queryForList.get(0).get("MATERIALQUANTITY2").toString());
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}
		}
	}
	
	/**
	 * 辅材备货
	 * @param materialPacking
	 * @param shipReuqestName
	 * @param shipRequestDetailName
	 * @param materialSpecName
	 * @param sdk_id
	 * @param phase
	 * @param type
	 * @param siteName
	 * @param user
	 * @param materialQuantity2
	 * @throws CustomException
	 */
	@Transactional
	public void AuxiliaryReserve (MaterialPacking materialPacking, String shipReuqestName, String shipRequestDetailName,
								  String materialSpecName, String sdk_id, String phase,
								  String type, String siteName, String user, String materialQuantity2) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
		Double materialQuantity = materialPacking.getMaterialQuantity();
		if(!"".equals(materialQuantity2)) {
			materialQuantity = Double.parseDouble(materialQuantity2);
		}
		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", shipReuqestName);
				hashMap2.put("materialQuantity2", materialQuantity);
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND m.SHIPREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME  ";
				String updateTh = "UPDATE MATERIALRECEIVEREQUESTDETAIL m SET m.RECEIVEDQUANTITY = m.RECEIVEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME \r\n" + 
						"						  AND m.RECEIVEREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME \r\n" + 
						"						  AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND m.RECEIVEREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME ";
				//增加过账条码表记录
				String insertSql = "INSERT INTO MATERIALPACKINGSUB t (t.MATERIALPACKINGNAME,t.RECEIVEREQUESTNAME,t.RECEIVEREQUESTDETAILNAME,t.QTY)\n" +
						"VALUES (:MATERIALPACKINGNAME,:RECEIVEREQUESTNAME,:RECEIVEREQUESTDETAILNAME,:QTY)";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("RECEIVEREQUESTNAME", shipReuqestName);
				bindMap.put("RECEIVEREQUESTDETAILNAME", shipRequestDetailName);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
				IDMFrameServiceProxy.getSqlTemplate().update(updateTh, bindMap);
				IDMFrameServiceProxy.getSqlTemplate().update(insertSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(shipReuqestName) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
				SetEventInfo setEventInfo = new SetEventInfo();
				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
				//更新实际实际出库数量到库存表
				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
				hashMap2.put("shipRequestName", "");
				hashMap2.put("shipRequestDetailName", "");
				hashMap2.put("materialQuantity2", "0");
				setEventInfo.setUserColumns(hashMap2);
				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				//更新备货数量
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND m.SHIPREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME  ";
				String updateTh = "UPDATE MATERIALRECEIVEREQUESTDETAIL m SET m.RECEIVEDQUANTITY = m.RECEIVEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME \r\n" + 
						"						  AND m.RECEIVEREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME \r\n" + 
						"						  AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) AND (:PHASE IS NULL OR PHASE = :PHASE) AND m.RECEIVEREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME ";
				String deleteSql = "DELETE FROM MATERIALPACKINGSUB t WHERE t.MATERIALPACKINGNAME = :MATERIALPACKINGNAME AND RECEIVEREQUESTNAME = :RECEIVEREQUESTNAME AND " +
						"RECEIVEREQUESTDETAILNAME = :RECEIVEREQUESTDETAILNAME";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", materialQuantity2);
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				bindMap.put("RECEIVEREQUESTNAME", shipReuqestName);
				bindMap.put("RECEIVEREQUESTDETAILNAME", shipRequestDetailName);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
				IDMFrameServiceProxy.getSqlTemplate().update(updateTh, bindMap);
				IDMFrameServiceProxy.getSqlTemplate().update(deleteSql, bindMap);
			}
		}

	}
	
	@Transactional
	public  List<Map<String,Object>> ZDBH (String shiprequestname,String user,String erpfactory ) {
		Map<String, Object> map = new HashMap<String, Object>();
		// 将前端传入的参数和存储过程中的参数对应起来
		map.put("V_SHIPREQUESTNAME",shiprequestname);
		map.put("V_ERPFACTORY",erpfactory);
		map.put("V_RESULT","");
		String procedureName = "CALL  GetFifoInventoryForOrder('" + shiprequestname + "', '" + erpfactory + "' )";
		try {
			// 调用存储过程返回参数
			 IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().execute(procedureName);
			//查询中间临时表的数据
			String sql = "SELECT T.MATERIALPACKINGNAME,T.MATERIALSPECNAME,T.MATERIALQUANTITY,T.MATERIALQUANTITY2,T.AREANAME ,T.SHELFNAME ,T.SDK_ID ,T.SPECNAME,T.PHASE   ,T.RECEIVETIME ,T.ERPLOCATION, \r\n" + 
					"T.LOCATIONNAME ,T.CHARGE ,T.BHFLG ,T.IS_CHECK \r\n" + 
					"FROM TEMP_FIFOTABLE T WHERE T.MATERIALPACKINGNAME NOT IN (SELECT MATERIALPACKINGNAME FROM MATERIALPACKING M WHERE M.SHIPREQUESTNAME = :SHIPREQUESTNAME)\r\n" + 
					"UNION\r\n" + 
					"(SELECT\r\n" + 
					"	A.MATERIALPACKINGNAME,\r\n" + 
					"	A.MATERIALSPECNAME,\r\n" + 
					"	A.MATERIALQUANTITY,\r\n" + 
					"	CASE\r\n" + 
					"		WHEN MATERIALQUANTITY2 > 0 THEN\r\n" + 
					"                    A.MATERIALQUANTITY2\r\n" + 
					"		ELSE\r\n" + 
					"                    A.MATERIALQUANTITY\r\n" + 
					"	END MATERIALQUANTITY2,\r\n" + 
					"	A.AREANAME,\r\n" + 
					"	A.SHELFNAME,\r\n" + 
					"	A.SDK_ID,\r\n" + 
					"	SS.SPECNAME,\r\n" + 
					"	A.PHASE,\r\n" + 
					"	TO_CHAR(A.MAKEDATE, 'YYYY-MM-DD') RECEIVETIME,\r\n" + 
					"	be.DESCRIPTION ERPLOCATION,\r\n" + 
					"	s.DESCRIPTION LOCATIONNAME,\r\n" + 
					"	A.CHARGE,\r\n" + 
					"	'Y' BHFLG,\r\n" + 
					"	'N' IS_CHECK\r\n" + 
					"FROM\r\n" + 
					"	MATERIALPACKING A\r\n" + 
					"LEFT JOIN SDK_SPEC SS ON\r\n" + 
					"	A.SDK_ID = SS.SDK_ID\r\n" + 
					"LEFT JOIN \r\n" + 
					"	MATERIALSPEC B ON\r\n" + 
					"	A.MATERIALSPECNAME = B.MATERIALSPECNAME\r\n" + 
					"LEFT JOIN BS_ERPLOCATION be ON\r\n" + 
					"	A.ERPLOCATION = be.ERPLOCATIONNAME\r\n" + 
					"LEFT JOIN STORAGESPEC s ON\r\n" + 
					"	A.LOCATIONNAME = s.STORAGENAME\r\n" + 
					"	AND A.ERPLOCATION = s.ERPLOCATION\r\n" + 
					"	AND A.ERPFACTORY = s.ERPFACTORY\r\n" + 
					"WHERE\r\n" + 
					"	1 = 1\r\n" + 
					"	AND A.MATERIALSPECNAME = B.MATERIALSPECNAME\r\n" + 
					"	AND A.SITENAME = B.SITENAME\r\n" + 
					"	AND A.SITENAME =:SITENAME\r\n" + 
					"	AND A.SHIPREQUESTNAME = :SHIPREQUESTNAME\r\n" + 
					"	AND A.STOCKSTATE = 'Stocked'\r\n" + 
					"	AND A.PACKINGSTATE = 'Released'\r\n" + 
					"	AND A.HOLDSTATE = 'N'\r\n" + 
					"	AND A.PACKINGGRADE IN ('OK', 'OKEY')\r\n" + 
					"	AND A.MATERIALQUANTITY <> '0'\r\n" + 
					"	AND (A.NPFLAG <> 'Y'\r\n" + 
					"		OR A.NPFLAG IS NULL)\r\n" + 
					"	AND (A.ETCSTATE IS NULL\r\n" + 
					"		OR A.ETCSTATE = 'EtcIn')\r\n" + 
					"	AND A.OQARESULTSTATE <> 'PREOQA'\r\n" + 
					"	AND to_char(A.EXPIRINGDATE, 'yyyymmdd') >= to_char(SYSDATE, 'yyyymmdd'))";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("SITENAME", "SDK");
			hashMap.put("SHIPREQUESTNAME", shiprequestname);
			List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			return queryForList;
		}catch(Throwable e) {
			throw e;
		}
		
	}
	public String ProductionStockOut_NEW(String shipRequestName, String siteName, String user,List<String> boxList) throws Exception {

		String rcode;
		String undoId = "";
		String rmsg = null;
		String sql=" SELECT\r\n" +
				"	t.MATERIALSPECNAME,\r\n" +
				"	t.ERPFACTORY,\r\n" +
				"	t.ERPLOCATION,\r\n" +
				"	m2.SHIPREQUESTDETAILTYPE,\r\n" +
				"	t.UNIT,\r\n" +
				"	m1.COSTCENTER,\r\n" +
				"	m1.SHIPREQUESTNAME,\r\n" +
				"	m1.SHIPREQUESTDETAILNAME,\r\n" +
				"	m1.ERPRECEIVELOCATION,\r\n" +
				"	m1.ERPRECEIVEFACTORY,\r\n" +
				"	m1.SENDSALESORDER ,m1.SENDSALESORDERDETAILNO,\r\n" +
				"	CASE WHEN m1.SENDSALESORDER IS NOT NULL THEN 'E' ELSE '' END SOBKZ,\r\n" +
				"	(\r\n" +
				"	SELECT\r\n" +
				"		RECEIVEPRODUCTIONORDERNUMBER\r\n" +
				"	FROM\r\n" +
				"		MATERIALSHIPREQUESTDETAIL m5\r\n" +
				"	WHERE\r\n" +
				"		m5.SHIPREQUESTNAME =:SHIPREQUESTNAME\r\n" +
				"		AND m5.MATERIALSPECNAME = t.MATERIALSPECNAME\r\n" +
				"	ORDER BY\r\n" +
				"		m5.USEDATE,\r\n" +
				"		m5.SHIPREQUESTDETAILNAME FETCH FIRST ROW ONLY)RECEIVEPRODUCTIONORDERNUMBER,\r\n" +
				"	SUM(m.QTY) AS MATERIALQUANTITY\r\n" +
				" FROM \r\n" +
				"		 MATERIALPACKINGSUB m\r\n" +
				" LEFT JOIN MATERIALPACKING T ON \r\n" +
				"		 m.MATERIALPACKINGNAME = T.MATERIALPACKINGNAME\r\n" +
				"	AND m.RECEIVEREQUESTNAME = T.SHIPREQUESTNAME\r\n" +
				" LEFT JOIN MATERIALSHIPREQUESTDETAIL M1  \r\n" +
				"		  ON \r\n" +
				"		 m.RECEIVEREQUESTNAME = M1.SHIPREQUESTNAME\r\n" +
				"	AND m.RECEIVEREQUESTDETAILNAME = M1.SHIPREQUESTDETAILNAME\r\n" +
				" LEFT JOIN MATERIALSHIPREQUEST M2  \r\n" +
				"		  ON \r\n" +
				"		 M1.SHIPREQUESTNAME = M2.SHIPREQUESTNAME\r\n" +
				" WHERE \r\n" +
				"		 T.SHIPREQUESTNAME = :SHIPREQUESTNAME\r\n" +
				"	AND T.SITENAME = :SITENAME\r\n" +
				"	AND T.STOCKSTATE = 'Stocked'\r\n" +
				"	AND T.MATERIALPACKINGNAME IN (:BOXLIST)\r\n" +
				" GROUP BY\r\n" +
				"	t.MATERIALSPECNAME,\r\n" +
				"	t.ERPFACTORY,\r\n" +
				"	t.ERPLOCATION,\r\n" +
				"	m2.SHIPREQUESTDETAILTYPE,\r\n" +
				"	t.UNIT,\r\n" +
				"	m1.COSTCENTER,\r\n" +
				"	m1.SHIPREQUESTNAME,m1.SENDSALESORDER ,m1.SENDSALESORDERDETAILNO,\r\n" +
				"	m1.SHIPREQUESTDETAILNAME,\r\n" +
				"	m1.ERPRECEIVELOCATION,\r\n" +
				"	m1.ERPRECEIVEFACTORY";
		Map<String, Object> hashMap = new HashMap<String, Object>();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		hashMap.put("SITENAME", siteName);
		hashMap.put("BOXLIST", boxList);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString(), hashMap);

		String sqlGetBWART = "SELECT M.SHIPREQUESTDETAILTYPE FROM MATERIALSHIPREQUEST M " +
				"WHERE M.SHIPREQUESTNAME = :SHIPREQUESTNAME AND M.SITENAME = :SITENAME";

		List<Map<String, Object>> checkResultList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sqlGetBWART, hashMap);
		String bwart = "";
		for(Map<String, Object> map : checkResultList) {
			bwart = MapUtils.getString(map, "SHIPREQUESTDETAILTYPE");
		}
		if(bwart == null || bwart == "") {
			throw new CustomException("ERROR","下发单据时，请务必填入移动类型");
		}

		try {

			JSONObject sendData = new JSONObject(true);
			JSONObject header = new JSONObject(true);
			sendData.put("HEAD", header);
			JSONObject body = new JSONObject(true);
			sendData.put("BODY", body);
			JSONArray itemArray = new JSONArray();

            /*
             HEAD
             */
			header.put("INTF_ID", "MM067");
			String uniqueID = UUID.randomUUID().toString();
			header.put("REQ_KEYID", uniqueID);
			header.put("SRC_MSGID", uniqueID);
			header.put("SRC_SYSTEM", "WMS");
			header.put("DEST_SYSTEM", "SAP");
			header.put("REQUSER", user);

            /*
             BODY
             */
			body.put("ITEM", itemArray);
			body.put("ITEMID", uniqueID);
			//过账日期
			body.put("BUDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证日期
			body.put("BLDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证抬头文本
			body.put("BKTXT", uniqueID);
			//用户名
			body.put("USNAM", user);

			for (Map<String, Object> mm : queryForList) {

				JSONObject item = new JSONObject(true);
				// MATNR 发货物料号
				item.put("MATNR", mm.get("MATERIALSPECNAME"));
				// WERKS 收货工厂
				item.put("WERKS", mm.get("ERPFACTORY"));
				// LGORT 发货库存地点
				item.put("LGORT", mm.get("ERPLOCATION"));
				//MENGE 数量
				item.put("MENGE", mm.get("MATERIALQUANTITY"));
				//MENGE 单位
				item.put("MEINS", mm.get("UNIT"));
//                //KOSTL 成本中心
//                item.put("KOSTL", mm.get("COSTCENTER"));
//                //GRUND 移动原因 (201,202回传)
//                item.put("GRUND", mm.get("SHIPREQUESTDETAILTYPE"));

				if(bwart.equals("261")) {
					//BWART 移动类型
					item.put("BWART", mm.get("SHIPREQUESTDETAILTYPE"));
					//ZLLORDER 自定义领料单号
					item.put("ZLLORDER", mm.get("SHIPREQUESTNAME"));
					//ZLLITEM 自定义领料单行号
					item.put("ZLLITEM", mm.get("SHIPREQUESTDETAILNAME"));
					//AUFNR 订单号
					item.put("AUFNR", mm.get("RECEIVEPRODUCTIONORDERNUMBER"));
				}
				if(bwart.equals("311")) {
					//BWART 移动类型
					item.put("BWART", mm.get("SHIPREQUESTDETAILTYPE"));
					//UMMAT 收货物料
					item.put("UMMAT", mm.get("MATERIALSPECNAME"));//传什么
					//UMLGO 收货库存地点
					item.put("UMLGO", mm.get("ERPRECEIVELOCATION"));
					//UMWRK 收货工厂
					item.put("UMWRK", mm.get("ERPRECEIVEFACTORY"));
				}
				item.put("SOBKZ", mm.get("SOBKZ"));//特殊库存标识
				item.put("MAT_KDAUF", mm.get("SENDSALESORDER"));//发货销售订单号
				item.put("MAT_KDPOS", mm.get("SENDSALESORDERDETAILNO"));//发货销售订单行项

				itemArray.add(item);

			}
//			log.info("SendTOSAP >>>>" + sendData);
			String dataString = sendData.toJSONString();
			String sapreturn = toSAPMessageUtil.sendHttpPost(toSAPMessageUtil.materialChangeLocationUrl, "", sendData.toJSONString());
			org.json.JSONObject receiveJsonObject = new org.json.JSONObject(sapreturn);
			org.json.JSONObject returnJsonObject = (org.json.JSONObject) receiveJsonObject.get("RETURN");
			rcode = returnJsonObject.get("STATUS").toString();
			rmsg = returnJsonObject.get("MSGTXT").toString();

			if ("S".equals(rcode)) {
				undoId = returnJsonObject.get("MBLNR").toString()+"_"+returnJsonObject.get("MJAHR").toString();//将物料凭证号与凭证年度拼在一起
			}
			//将log写到表里
			ErpMessageLog erplog = new ErpMessageLog();
			erplog.setEventUser("");
			erplog.setServerName("WmsToErp");
			erplog.setEventName("生产领料出库261/311");
			erplog.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
			erplog.setMessageId(UUID.randomUUID().toString());
			erplog.setSendMsg(sendData.toJSONString());
			erplog.setSendMsg2(sendData.toJSONString());
			erplog.setReturnMsg2(sapreturn);
			erplog.setResultCode(rcode);
			MessageLogUtil.writeMessageLog(erplog);
		} catch (Exception e) {
//			log.info(e.getMessage(), e);
			throw new GlobalException("SAP返回" + rmsg);
		}
		if (!"S".equals(rcode)) {
			throw new GlobalException("SAP返回" + rmsg);
//			throw new CustomException("SAP返回" + rmsg);
		}
		return undoId;
	}

	public String orderStockOut(String shipRequestName, String siteName, String user,List<String> boxList) throws Exception {

		String rcode;
		String undoId = "";
		String rmsg = null;
		String sql=" SELECT      MP.MATERIALSPECNAME,       \r\n" +
				"        			MP.ERPFACTORY,       \r\n" +
				"        			MP.ERPLOCATION,       \r\n" +
				"        			MS.SHIPREQUESTDETAILTYPE,       \r\n" +
				"        			MP.UNIT,       \r\n" +
				"        			MD.COSTCENTER,       \r\n" +
				"        			MD.RECEIVEPRODUCTIONORDERNUMBER,       \r\n" +
				"        			MP.SHIPREQUESTNAME,       \r\n" +
				"        			MD.SHIPREQUESTDETAILNAME,       \r\n" +
				"        			MD.ERPRECEIVELOCATION,       \r\n" +
				"        			MD.ERPRECEIVEFACTORY,       \r\n" +
				"        			MD.CAUSEOFMOVEMENT,       \r\n" +
				"        			SUM(t.QTY) AS MATERIALQUANTITY       \r\n" +
				"        		FROM       \r\n" +
				"        			MATERIALPACKING MP \r\n" +
				"        		LEFT JOIN MATERIALPACKINGSUB T\r\n" +
				"        		ON mp.MATERIALPACKINGNAME =t.MATERIALPACKINGNAME \r\n" +
				"        		AND mp.SHIPREQUESTNAME =t.RECEIVEREQUESTNAME \r\n" +
				"        		LEFT JOIN MATERIALSHIPREQUESTDETAIL MD        \r\n" +
				"        				 ON       \r\n" +
				"        			MP.SHIPREQUESTNAME = MD.SHIPREQUESTNAME       \r\n" +
				"        			AND MP.SITENAME = MD.SITENAME       \r\n" +
				"        			AND t.RECEIVEREQUESTDETAILNAME = MD.SHIPREQUESTDETAILNAME       \r\n" +
				"        		LEFT JOIN MATERIALSHIPREQUEST MS        \r\n" +
				"        				 ON       \r\n" +
				"        			MD.SHIPREQUESTNAME = MS.SHIPREQUESTNAME       \r\n" +
				"        		WHERE       \r\n" +
				"        			MP.SHIPREQUESTNAME = :SHIPREQUESTNAME       \r\n" +
				"        			AND MP.SITENAME = :SITENAME       \r\n" +
				"        			AND MP.STOCKSTATE = 'Stocked'\r\n" +
				"        			AND MP.MATERIALPACKINGNAME IN (:BOXLIST)\r\n" +
				"        		GROUP BY        \r\n" +
				"        			MP.MATERIALSPECNAME,       \r\n" +
				"        			MP.ERPFACTORY,       \r\n" +
				"        			MP.ERPLOCATION,       \r\n" +
				"        			MS.SHIPREQUESTDETAILTYPE,       \r\n" +
				"        			MP.UNIT,       \r\n" +
				"        			MD.COSTCENTER,       \r\n" +
				"        			MD.RECEIVEPRODUCTIONORDERNUMBER,       \r\n" +
				"        			MP.SHIPREQUESTNAME, \r\n" +
				"        			MD.SHIPREQUESTDETAILNAME,\r\n" +
				"        			MD.ERPRECEIVELOCATION,       \r\n" +
				"        			MD.ERPRECEIVEFACTORY,       \r\n" +
				"        			MD.CAUSEOFMOVEMENT       ";
		Map<String, Object> hashMap = new HashMap<String, Object>();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		hashMap.put("SITENAME", siteName);
		hashMap.put("BOXLIST", boxList);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString(), hashMap);


		try {

			JSONObject sendData = new JSONObject(true);
			JSONObject header = new JSONObject(true);
			sendData.put("HEAD", header);
			JSONObject body = new JSONObject(true);
			sendData.put("BODY", body);
			JSONArray itemArray = new JSONArray();

            /*
             HEAD
             */
			header.put("INTF_ID", "MM067");
			String uniqueID = UUID.randomUUID().toString();
			header.put("REQ_KEYID", uniqueID);
			header.put("SRC_MSGID", uniqueID);
			header.put("SRC_SYSTEM", "WMS");
			header.put("DEST_SYSTEM", "SAP");
			header.put("REQUSER", user);

            /*
             BODY
             */
			body.put("ITEM", itemArray);
			body.put("ITEMID", uniqueID);
			//过账日期
			body.put("BUDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证日期
			body.put("BLDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证抬头文本
			body.put("BKTXT", uniqueID);
			//用户名
			body.put("USNAM", user);

			for (Map<String, Object> mm : queryForList) {

				JSONObject item = new JSONObject(true);
				//BWART 移动类型
				item.put("BWART",  mm.get("SHIPREQUESTDETAILTYPE"));
				// MATNR 发货物料号
				item.put("MATNR", mm.get("MATERIALSPECNAME"));
				// WERKS 收货工厂
				item.put("WERKS", mm.get("ERPFACTORY"));
				// LGORT 发货库存地点
				item.put("LGORT", mm.get("ERPLOCATION"));
				//MENGE 数量
				item.put("MENGE", mm.get("MATERIALQUANTITY"));
				//MENGE 单位
				item.put("MEINS", mm.get("UNIT"));
				//KOSTL 成本中心
				item.put("KOSTL", mm.get("COSTCENTER"));
				//AUFNR 订单号
				item.put("AUFNR", mm.get("RECEIVEPRODUCTIONORDERNUMBER"));
				//GRUND 移动原因 (201,202回传)
				item.put("GRUND", mm.get("CAUSEOFMOVEMENT"));
				//ZLLORDER 自定义领料单号
				item.put("ZLLORDER", mm.get("SHIPREQUESTNAME"));
				//ZLLITEM 自定义领料单行号
				item.put("ZLLITEM", mm.get("SHIPREQUESTDETAILNAME"));

				itemArray.add(item);

			}
//			log.info("SendTOSAP >>>>" + sendData);
			String sapreturn = toSAPMessageUtil.sendHttpPost(toSAPMessageUtil.materialChangeLocationUrl, "", sendData.toJSONString());
			org.json.JSONObject receiveJsonObject = new org.json.JSONObject(sapreturn);
			org.json.JSONObject returnJsonObject = (org.json.JSONObject) receiveJsonObject.get("RETURN");
			rcode = returnJsonObject.get("STATUS").toString();
			rmsg = returnJsonObject.get("MSGTXT").toString();

			if ("S".equals(rcode)) {
				undoId = returnJsonObject.get("MBLNR").toString()+"_"+returnJsonObject.get("MJAHR").toString();//将物料凭证号与凭证年度拼在一起
			}
			//将log写到表里
			ErpMessageLog erplog = new ErpMessageLog();
			erplog.setEventUser("");
			erplog.setServerName("WmsToErp");
			erplog.setEventName("成本中心/研发领料");
			erplog.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
			erplog.setMessageId(UUID.randomUUID().toString());
			erplog.setSendMsg2(sendData.toJSONString());
			erplog.setReturnMsg2(sapreturn);
			erplog.setResultCode(rcode);
			MessageLogUtil.writeMessageLog(erplog);
		} catch (Exception e) {
//			log.info(e.getMessage(), e);
			throw new RuntimeException("SAP返回" + rmsg);
		}
		if (!"S".equals(rcode)) {
			throw new RuntimeException("SAP返回" + rmsg);
		}
		return undoId;
	}

	//重写销售交货单过账。zd.2024.8.14
	public String saleStockOut_NEW(String shipRequestName, String siteName, String user,List<String> boxList) throws Exception {
		String rcode;
		String undoId = "";
		String rmsg = null;
		String sql="    SELECT    \r\n" +
				"            count(*) AS ZROL,    \r\n" +
				"            m1.SHIPREQUESTDETAILNAME ,    \r\n" +
				"            t.ERPLOCATION,          \r\n" +
				"            sum(m.QTY)MATERIALQUANTITY          \r\n" +
				"            FROM \r\n" +
				"		 MATERIALPACKINGSUB m \r\n" +
				"		 LEFT JOIN MATERIALPACKING T ON \r\n" +
				"		 m.MATERIALPACKINGNAME = T.MATERIALPACKINGNAME AND m.RECEIVEREQUESTNAME = T.SHIPREQUESTNAME \r\n" +
				"		 LEFT JOIN MATERIALSHIPREQUESTDETAIL M1  \r\n" +
				"		  ON \r\n" +
				"		 m.RECEIVEREQUESTNAME = M1.SHIPREQUESTNAME \r\n" +
				"		 AND m.RECEIVEREQUESTDETAILNAME = M1.SHIPREQUESTDETAILNAME \r\n" +
				"		 LEFT JOIN MATERIALSHIPREQUEST M2  \r\n" +
				"		  ON \r\n" +
				"		 M1.SHIPREQUESTNAME = M2.SHIPREQUESTNAME \r\n" +
				"		 WHERE \r\n" +
				"		 T.SHIPREQUESTNAME = :SHIPREQUESTNAME \r\n" +
				"		 AND T.SITENAME = :SITENAME  \r\n" +
				"		  AND T.STOCKSTATE = 'Stocked' AND   T.MATERIALPACKINGNAME IN (:BOXLIST)\r\n" +
				"		  GROUP BY m1.SHIPREQUESTDETAILNAME ,    t.ERPLOCATION";

		Map<String, Object> hashMap = new HashMap<String, Object>();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		hashMap.put("SITENAME", siteName);
		hashMap.put("BOXLIST", boxList);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString(), hashMap);
		try {

			JSONObject sendData = new JSONObject(true);
			JSONObject header = new JSONObject(true);
			sendData.put("HEAD", header);
			JSONObject body = new JSONObject(true);
			sendData.put("BODY", body);
			JSONArray itemArray = new JSONArray();

            /*
             HEAD
             */
			header.put("DEST_SYSTEM", "SAP");
			header.put("INTF_ID", "SD023");
			header.put("SRC_SYSTEM", "WMS");
			String uniqueID = UUID.randomUUID().toString();
			header.put("SRC_MSGID", uniqueID);
			header.put("BACKUP1", uniqueID);
			header.put("BACKUP2", uniqueID);



            /*
             BODY
             */
			//交货单号
			body.put("VBELN", shipRequestName);
			//实际过账日期
			body.put("WADAT_IST", TimeStampUtil.getCurrentTime("yyyyMMdd"));//yyyyMMddhhmmss
			//发送日期
			body.put("Z_SENDDATE", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//操作类型（过账）
			body.put("Z_OPTION", "1");

			if (queryForList.size() >= 1) {
				for (Map<String, Object> mm : queryForList) {
					JSONObject item = new JSONObject(true);

					//交货单行号
					item.put("POSNR", mm.get("SHIPREQUESTDETAILNAME"));
					//交货数量
					item.put("PIKMG", mm.get("MATERIALQUANTITY"));
					//库存地点
					item.put("LGORT", mm.get("ERPLOCATION"));
					//实际发货卷数
					item.put("Z_ROL", mm.get("ZROL"));
					//实际发货平方米
					item.put("Z_SQUE", mm.get("MATERIALQUANTITY"));

					itemArray.add(item);
				}
				body.put("ITEMS", itemArray);//销售过账需放ITEMS
			}else {
				for (Map<String, Object> mm : queryForList) {

					//交货单行号
					body.put("POSNR", mm.get("SHIPREQUESTDETAILNAME"));
					//交货数量
					body.put("PIKMG", mm.get("MATERIALQUANTITY")); //SUM
					//库存地点
					body.put("LGORT", mm.get("ERPLOCATION"));
					//实际发货卷数
					body.put("Z_ROL", mm.get("ZROL"));   //COUNT
					//实际发货平方米
					body.put("Z_SQUE", mm.get("MATERIALQUANTITY"));

				}
			}

//			log.info("SendTOSAP >>>>" + sendData);
			String sapreturn = toSAPMessageUtil.sendHttpPost(toSAPMessageUtil.DNUrl, "", sendData.toJSONString());
			org.json.JSONObject receiveJsonObject = new org.json.JSONObject(sapreturn);

			//销售交货单过账不返回物料凭证,且RETURN为JsonArray
			org.json.JSONObject returnJsonObject = (org.json.JSONObject) receiveJsonObject.get("RETURN");
			rcode = returnJsonObject.get("STATUS").toString();
			rmsg = returnJsonObject.get("MSGTXT").toString();

			if ("S".equals(rcode)) {
				undoId = "S";
			}
			//将log写到表里
			ErpMessageLog erplog = new ErpMessageLog();
			erplog.setEventUser("");
			erplog.setServerName("WmsToErp");
			erplog.setEventName("调拨出库(NLCC)");
			erplog.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
			erplog.setMessageId(UUID.randomUUID().toString());
			erplog.setSendMsg2(sendData.toJSONString());
			erplog.setReturnMsg2(sapreturn);
			erplog.setResultCode(rcode);
			MessageLogUtil.writeMessageLog(erplog);
		} catch (Exception e) {
//			log.info(e.getMessage(), e);
			throw new RuntimeException("SAP返回" + rmsg);
		}
		if (!"S".equals(rcode)) {
			throw new RuntimeException("SAP返回" + rmsg);
		}
		return undoId;
	}

	//重写委外发料。zd.2024.8.17
	public String oemStockOut_NEW(String shipRequestName, String siteName, String user,List<String> boxList) throws Exception
	{

		String rcode;
		String undoId = "";
		String rmsg = null;
		String sql="SELECT       \r\n" +
				"        			MS.DELIVERYADDRESS,       \r\n" +
				"        			MP.MATERIALSPECNAME,       \r\n" +
				"        			MP.ERPFACTORY,       \r\n" +
				"        			MP.ERPLOCATION,       \r\n" +
				"        			MS.SHIPREQUESTDETAILTYPE,       \r\n" +
				"        			MP.UNIT,       \r\n" +
				"        			MD.COSTCENTER,       \r\n" +
				"        			MD.RECEIVEPRODUCTIONORDERNUMBER,       \r\n" +
				"        			MP.SHIPREQUESTNAME,       \r\n" +
				"        			MP.SHIPREQUESTDETAILNAME,       \r\n" +
				"        			MD.ERPRECEIVELOCATION,       \r\n" +
				"        			MD.ERPRECEIVEFACTORY,       \r\n" +
				"        			SUM(M.QTY) AS MATERIALQUANTITY       \r\n" +
				"        		FROM       \r\n" +
				"        			MATERIALPACKING MP  \r\n" +
				"        		LEFT JOIN MATERIALPACKINGSUB m \r\n" +
				"        		ON mp.MATERIALPACKINGNAME =m.MATERIALPACKINGNAME \r\n" +
				"        		AND mp.SHIPREQUESTNAME =m.RECEIVEREQUESTNAME \r\n" +
				"        		LEFT JOIN MATERIALSHIPREQUESTDETAIL MD        \r\n" +
				"        				 ON       \r\n" +
				"        			MP.SHIPREQUESTNAME = MD.SHIPREQUESTNAME       \r\n" +
				"        			AND MP.SITENAME = MD.SITENAME \r\n" +
				"        			AND m.RECEIVEREQUESTDETAILNAME =md.SHIPREQUESTDETAILNAME \r\n" +
				"        		LEFT JOIN MATERIALSHIPREQUEST MS        \r\n" +
				"        				 ON       \r\n" +
				"        			MD.SHIPREQUESTNAME = MS.SHIPREQUESTNAME \r\n" +
				"        		WHERE       \r\n" +
				"        			MP.SHIPREQUESTNAME = :SHIPREQUESTNAME       \r\n" +
				"        			AND MP.SITENAME = :SITENAME       \r\n" +
				"        			AND MP.STOCKSTATE = 'Stocked'\r\n" +
				"        			AND mp.MATERIALPACKINGNAME IN (:BOXLIST)\r\n" +
				"        		GROUP BY        \r\n" +
				"        			MS.DELIVERYADDRESS,       \r\n" +
				"        			MP.MATERIALSPECNAME,       \r\n" +
				"        			MP.ERPFACTORY,       \r\n" +
				"        			MP.ERPLOCATION,       \r\n" +
				"        			MS.SHIPREQUESTDETAILTYPE,       \r\n" +
				"        			MP.UNIT,       \r\n" +
				"        			MD.COSTCENTER,       \r\n" +
				"        			MD.RECEIVEPRODUCTIONORDERNUMBER,       \r\n" +
				"        			MP.SHIPREQUESTNAME,       \r\n" +
				"        			MP.SHIPREQUESTDETAILNAME,     \r\n" +
				"        			MD.ERPRECEIVELOCATION,       \r\n" +
				"        			MD.ERPRECEIVEFACTORY       ";


		Map<String, Object> hashMap = new HashMap<String, Object>();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		hashMap.put("SITENAME", siteName);
		hashMap.put("BOXLIST", boxList);
		List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql.toString(), hashMap);



		try {

			JSONObject sendData = new JSONObject(true);
			JSONObject header = new JSONObject(true);
			sendData.put("HEAD", header);
			JSONObject body = new JSONObject(true);
			sendData.put("BODY", body);
			JSONArray itemArray = new JSONArray();

            /*
             HEAD
             */
			header.put("INTF_ID", "MM067");
			String uniqueID = UUID.randomUUID().toString();
			header.put("REQ_KEYID", uniqueID);
			header.put("SRC_MSGID", uniqueID);
			header.put("SRC_SYSTEM", "WMS");
			header.put("DEST_SYSTEM", "SAP");
			header.put("REQUSER", user);

            /*
             BODY
             */
			body.put("ITEM", itemArray);
			body.put("ITEMID", uniqueID);
			//过账日期
			body.put("BUDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证日期
			body.put("BLDAT", TimeStampUtil.getCurrentTime("yyyyMMdd"));
			//凭证抬头文本
			body.put("BKTXT", uniqueID);
			//用户名
			body.put("USNAM", user);

			for (Map<String, Object> mm : queryForList) {

				JSONObject item = new JSONObject(true);

				// MATNR 发货物料号
				item.put("MATNR", mm.get("MATERIALSPECNAME"));
				// WERKS 收货工厂
				item.put("WERKS", mm.get("ERPFACTORY"));
				// LGORT 发货库存地点
				item.put("LGORT", mm.get("ERPLOCATION"));
				//BWART 移动类型
				item.put("BWART", "541O");
				//SOBKZ 特殊库存标识
//                item.put("SOBKZ", "O");
				//LIFNR 供应商编码
				item.put("LIFNR", mm.get("DELIVERYADDRESS"));
				//MENGE 数量
				item.put("MENGE", mm.get("MATERIALQUANTITY"));
				//MENGE 单位
				item.put("MEINS", mm.get("UNIT"));

				itemArray.add(item);

				updateOEMINVENTORY(mm.get("ERPFACTORY") == null ?  "" : mm.get("ERPFACTORY").toString(),mm.get("DELIVERYADDRESS") == null ?  "" : mm.get("DELIVERYADDRESS").toString(),
						mm.get("MATERIALSPECNAME") == null ?  "" : mm.get("MATERIALSPECNAME").toString(),mm.get("MATERIALQUANTITY") == null ?  "" : mm.get("MATERIALQUANTITY").toString());

			}
//			log.info("SendTOSAP >>>>" + sendData);
			String sapreturn = toSAPMessageUtil.sendHttpPost(toSAPMessageUtil.materialChangeLocationUrl, "", sendData.toJSONString());
			org.json.JSONObject receiveJsonObject = new org.json.JSONObject(sapreturn);
			org.json.JSONObject returnJsonObject = (org.json.JSONObject) receiveJsonObject.get("RETURN");
			rcode = returnJsonObject.get("STATUS").toString();
			rmsg = returnJsonObject.get("MSGTXT").toString();

			if ("S".equals(rcode)) {
				undoId = returnJsonObject.get("MBLNR").toString()+"_"+returnJsonObject.get("MJAHR").toString();//将物料凭证号与凭证年度拼在一起
			}
			//将log写到表里
			UUID uuid = UUID.randomUUID();
			ErpMessageLog erplog = new ErpMessageLog();
			erplog.setEventUser("");
			erplog.setServerName("WmsToErp");
			erplog.setEventName("委外发料541O");
			erplog.setId(uuid.toString());
			erplog.setInterfaceTime(TimeStampUtil.getCurrentTime(TimeStampUtil.FORMAT_DEFAULT));
			erplog.setMessageId(UUID.randomUUID().toString());
			erplog.setSendMsg2(sendData.toJSONString());
			erplog.setReturnMsg2(sapreturn);
			erplog.setResultCode(rcode);
			MessageLogUtil.writeMessageLog(erplog);
		} catch (Exception e) {
//			log.info(e.getMessage(), e);
			throw new RuntimeException("SAP返回" + rmsg);
		}
		if (!"S".equals(rcode)) {
			throw new RuntimeException("SAP返回" + rmsg);
		}
		return undoId;
	}

	//更新OEMINVENTORY。zd.2024.8.7
	public void updateOEMINVENTORY(String erpFactory,String supplierNo,String materialSpecName,String qty) throws Exception
	{
		String sqls="SELECT o.MATERIALSPECNAME FROM OEMINVENTORY o \r\n" +
				" WHERE o.ERPFACTORY =:ERPFACTORY\r\n" +
				" AND o.MATERIALSPECNAME =:MATERIALSPECNAME\r\n" ;
//				" AND o.SUPPLIERNO =:SUPPLIERNO";
		Map<String, Object> bp=new HashMap<>();
		bp.put("ERPFACTORY", erpFactory);
		bp.put("MATERIALSPECNAME", materialSpecName);
		bp.put("SUPPLIERNO", supplierNo);
		bp.put("QTY", qty);
		List<Map<String, Object>> sr=IDMFrameServiceProxy.getSqlTemplate().queryForList(sqls, bp);
		if(sr.size()>0)
		{
			String sqlu="UPDATE OEMINVENTORY o SET o.QTY =o.QTY + :QTY\r\n" +
					" WHERE o.ERPFACTORY =:ERPFACTORY\r\n" +
					" AND o.MATERIALSPECNAME =:MATERIALSPECNAME\r\n" ;
//					" AND o.SUPPLIERNO =:SUPPLIERNO";
			IDMFrameServiceProxy.getSqlTemplate().update(sqlu, bp);
		}
		else
		{
			String sqli="INSERT INTO OEMINVENTORY(ERPFACTORY,MATERIALSPECNAME,SUPPLIERNO,QTY)\r\n" +
					" VALUES (:ERPFACTORY,:MATERIALSPECNAME,:SUPPLIERNO,:QTY)";
			IDMFrameServiceProxy.getSqlTemplate().update(sqli, bp);
		}

	}

	@Transactional
	public void Reserve5 (MaterialPacking materialPacking, String shipReuqestName,
						  String materialSpecName, String sdk_id, String phase,
						  String type, String siteName, String user, String materialQuantity2, String fifoFlag) throws CustomException {
		String materialPackingName = materialPacking.getMaterialPackingName();//条码
//		float materialQuantity = materialPacking.getMaterialQuantity();
//		float mqty2 = materialPacking.getMaterialQuantity2();
//		String charge = materialPacking.getCharge();//批次
		EventInfo makeEventInfo;
		//1.绑定
		//校验Box有没有被其他订单绑过
		if ("Assign".equals(type)) {
			makeEventInfo = new EventInfoUtil().makeEventInfo("AssiginShipRequest", user, "AssiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME,MATERIALQUANTITY,SDK_ID,PHASE FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {
				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(invoice) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}
				phase = queryForList.get(0).get("PHASE") == null ? "" :queryForList.get(0).get("PHASE").toString();
				//更新Box绑定关系、Box发货数量、拆分到条码明细表
//				SetEventInfo setEventInfo = new SetEventInfo();
//				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
//				//更新实际实际出库数量到库存表
//				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
//				hashMap2.put("shipRequestName", shipReuqestName);
//				hashMap2.put("fifoFlag", fifoFlag);
//				hashMap2.put("materialQuantity2", queryForList.get(0).get("MATERIALQUANTITY").toString());//先将Box数量记录至materialQuantity2
//				setEventInfo.setUserColumns(hashMap2);
//				hashMap2.put("fifoFlag", fifoFlag);
//				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				hashMap.put("materialQuantity2", queryForList.get(0).get("MATERIALQUANTITY").toString());
				hashMap.put("fifoFlag", fifoFlag);
				hashMap.put("shipRequestName", shipReuqestName);
				hashMap.put("SITENAME", siteName);

				String upMkSql = "UPDATE\r\n" +
						"	MATERIALPACKING m\r\n" +
						"SET\r\n" +
						"	m.MATERIALQUANTITY2 = :materialQuantity2,\r\n" +
						"	m.FIFOFLAG = :fifoFlag,\r\n" +
						"	m.SHIPREQUESTNAME = :shipRequestName\r\n" +
						"WHERE\r\n" +
						"	m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME\r\n" +
						"	AND m.SITENAME = :SITENAME";

				IDMFrameServiceProxy.getSqlTemplate().update(upMkSql, hashMap);


				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY + :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ " AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) " +
//						"AND (:PHASE IS NULL OR PHASE = :PHASE) " +
						"AND ROWNUM = 1  ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("QTY", queryForList.get(0).get("MATERIALQUANTITY").toString());
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}

		} else 	if ("Dessign".equals(type)) {
			//解绑根据查询出的materil信息拿到SDKID和阶段
//			sdk_id = materialPacking.getSDK_ID();
//			phase = materialPacking.getPHASE();
			makeEventInfo = new EventInfoUtil().makeEventInfo("DessiginShipRequest", user, "DessiginShipRequest");
			String sql = "SELECT SHIPREQUESTNAME,CASE WHEN MATERIALQUANTITY2 > 0 THEN MATERIALQUANTITY2 ELSE MATERIALQUANTITY END MATERIALQUANTITY2,SDK_ID,PHASE FROM MATERIALPACKING T WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
			Map<String, Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("MATERIALPACKINGNAME", materialPackingName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() < 1) {
				throw new CustomException("条码在系统中不存在");
			} else {

				sdk_id = queryForList.get(0).get("SDK_ID") == null ? "" : queryForList.get(0).get("SDK_ID").toString();
				phase = queryForList.get(0).get("PHASE") == null ? "" : queryForList.get(0).get("PHASE") .toString();


				String invoice = queryForList.get(0).get("SHIPREQUESTNAME") == null ? "" : queryForList.get(0).get("SHIPREQUESTNAME").toString().toString();
				if (!"".equals(shipReuqestName) && !invoice.equals(shipReuqestName)) {
					throw new CustomException("条码已被" + invoice + "备货");
				}

				//更新Box绑定关系、Box发货数量、拆分到条码明细表
//				SetEventInfo setEventInfo = new SetEventInfo();
//				Map<String, Object> hashMap2 = new HashMap<String,Object> ();
//				//更新实际实际出库数量到库存表
//				MaterialPackingKey materialPackingKey = new MaterialPackingKey(siteName, materialPackingName);
//				hashMap2.put("shipRequestName", "");
//				hashMap2.put("fifoFlag", "");
//				hashMap2.put("shipRequestDetailName", "");
//				hashMap2.put("materialQuantity2", "0");
//				setEventInfo.setUserColumns(hashMap2);
//				MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				//更新备货数量
				//					BigDecimal QTY = new BigDecimal(materialQuantity);
				hashMap.put("shipRequestName", "");
				hashMap.put("fifoFlag", "");
				hashMap.put("shipRequestDetailName", "");
				hashMap.put("materialQuantity2", "0");
				hashMap.put("SITENAME", siteName);
				String upMkSql = "UPDATE\r\n" +
						"	MATERIALPACKING m\r\n" +
						"SET\r\n" +
						"	m.MATERIALQUANTITY2 = :materialQuantity2,\r\n" +
						"	m.FIFOFLAG = :fifoFlag,\r\n" +
						"	m.SHIPREQUESTNAME = :shipRequestName,\r\n" +
						"	m.SHIPREQUESTDETAILNAME = :shipRequestDetailName\r\n" +
						"WHERE\r\n" +
						"	m.MATERIALPACKINGNAME = :MATERIALPACKINGNAME\r\n" +
						"	AND m.SITENAME = :SITENAME";
				IDMFrameServiceProxy.getSqlTemplate().update(upMkSql, hashMap);


				String updateSql= " UPDATE MATERIALSHIPREQUESTDETAIL m SET m.ASSIGNEDQUANTITY = m.ASSIGNEDQUANTITY - :QTY WHERE m.SITENAME = :SITENAME"
						+ " AND m.SHIPREQUESTNAME = :SHIPREQUESTNAME AND m.MATERIALSPECNAME = :MATERIALSPECNAME "
						+ "AND (:SDK_ID IS NULL OR SDK_ID = :SDK_ID) " +
//						"AND (:PHASE IS NULL OR PHASE = :PHASE)" +
						"  AND ROWNUM = 1 ";
				Map<String, Object> bindMap = new HashMap<String, Object>();
				//bindMap.put("QTY", materialQuantity2);
				bindMap.put("QTY", queryForList.get(0).get("MATERIALQUANTITY2").toString());
				bindMap.put("SITENAME", siteName);
				bindMap.put("SHIPREQUESTNAME", shipReuqestName);
				bindMap.put("MATERIALPACKINGNAME", materialPackingName);
				bindMap.put("MATERIALSPECNAME", materialSpecName);
				bindMap.put("SDK_ID", sdk_id);
				bindMap.put("PHASE", phase);
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, bindMap);
			}
		}
	}

}
