package com.cim.idm.utils;

import com.cim.idm.exception.GlobalException;
import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.data.EventInfo;
import com.cim.idm.service.impl.MESServiceImpl;
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 org.apache.commons.lang3.StringUtils;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@RestController
@RequestMapping("/CommonUtils")
@EnableAutoConfiguration
public class CommonUtils {
 
	/**
	 * 查询库位所在的仓库
	 * @param loction
	 * @return
	 */
	public Boolean QueryLocation(String storage,String loction) {
		String sql="SELECT ERPLOCATION  FROM STORAGESPEC s WHERE STORAGENAME =:STORAGENAME AND ERPLOCATION =:ERPLOCATION ";
		Map<String, Object> QueryMap = new HashMap<String, Object>();
		QueryMap.put("STORAGENAME", storage);
		QueryMap.put("ERPLOCATION", loction);
		List<Map<String,Object>> list =IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, QueryMap);
		if(list.size()>0)
			return true;
		return false;
	}
	
	public String ChangeTime(String date)
	{
		DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyyMMdd");  
        DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");  
  
        // 解析为LocalDate，因为没有时间部分  
        LocalDate localDate = LocalDate.parse(date, inputFormatter);  
  
        // 创建一个LocalTime表示00:00:00  
        LocalTime localTime = LocalTime.MIDNIGHT; // 或者 LocalTime.of(0, 0, 0);  
  
        // 合并LocalDate和LocalTime为LocalDateTime  
        LocalDateTime localDateTime = LocalDateTime.of(localDate, localTime);  
  
        // 格式化LocalDateTime为字符串  
        String formattedDate = localDateTime.format(outputFormatter);  
        
        return formattedDate;
	}
	
	/**
	 * 检验是否熟化仓
	 * @param ERPLOcxation
	 * @param materialSpec
	 * @return
	 */
	public Boolean JudgeMentIsOvenSpec(String ERPLOcxation,String materialSpec)
	{
		String DetailSql = "SELECT MATURATIONFLAG FROM MATERIALSPEC m WHERE MATERIALSPECNAME =:MATERIALSPECNAME AND MATURATIONFLAG='Y' ";
		Map<String, Object> DetailMap = new HashMap<String, Object>();
		DetailMap.put("MATERIALSPECNAME",materialSpec ); 
		List<Map<String,Object>>  ShipDetaillist = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql, DetailMap);

		String DetailSql2 = "SELECT IS_OVEN FROM BS_ERPLOCATION m WHERE ERPLOCATIONNAME =:ERPLOCATIONAMEE AND IS_OVEN='Y' ";
		Map<String, Object> DetailMap2 = new HashMap<String, Object>();
		DetailMap2.put("ERPLOCATIONAMEE",ERPLOcxation ); 
		List<Map<String,Object>>  ShipDetaillist2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql2, DetailMap2);
		
		//		if(Objects.isNull(ShipDetaillist) || ShipDetaillist.isEmpty()) {
//			return true;
//		}else {
//			String DetailSql2 = "SELECT IS_OVEN FROM BS_ERPLOCATION m WHERE ERPLOCATIONNAME =:ERPLOCATIONAMEE AND IS_OVEN='Y' ";
//			Map<String, Object> DetailMap2 = new HashMap<String, Object>();
//			DetailMap2.put("ERPLOCATIONAMEE",ERPLOcxation ); 
//			List<Map<String,Object>>  ShipDetaillist2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql2, DetailMap2);
//			if(Objects.isNull(ShipDetaillist2) || ShipDetaillist2.isEmpty())
//			{
//				return false;
//			}else
//			{
//				return true;
//			}
//		} 
		if((Objects.isNull(ShipDetaillist) || ShipDetaillist.isEmpty()) && (Objects.isNull(ShipDetaillist2) || ShipDetaillist2.isEmpty())) {
			return true;
		}else {
			if(ShipDetaillist2.size() > 0 && ShipDetaillist2.size() > 0)
			{
				return true;
			}else
			{
				return false;
			}
		} 
		
	}
	
	
	/**
	 * 检验恒温管控
	 * @param ERPLOcxation
	 * @param materialSpec
	 * @return
	 */
	public Boolean JudgeMentERPLocationAndMaterialSpec(String ERPLOcxation,String materialSpec)
	{
		String DetailSql = "SELECT IS_CON_TEMP FROM MATERIALSPEC m WHERE MATERIALSPECNAME =:MATERIALSPECNAME AND IS_CON_TEMP='Y' ";
		Map<String, Object> DetailMap = new HashMap<String, Object>();
		DetailMap.put("MATERIALSPECNAME",materialSpec ); 
		List<Map<String,Object>>  ShipDetaillist = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql, DetailMap);
		
		String DetailSql2 = "SELECT CONST_TEMP FROM BS_ERPLOCATION m WHERE ERPLOCATIONNAME =:ERPLOCATIONAMEE AND CONST_TEMP='Y' ";
		Map<String, Object> DetailMap2 = new HashMap<String, Object>();
		DetailMap2.put("ERPLOCATIONAMEE",ERPLOcxation ); 
		List<Map<String,Object>>  ShipDetaillist2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql2, DetailMap2);
		if((Objects.isNull(ShipDetaillist) || ShipDetaillist.isEmpty()) && (Objects.isNull(ShipDetaillist2) || ShipDetaillist2.isEmpty())) {
			return true;
		}else {
			if(ShipDetaillist2.size() > 0 && ShipDetaillist2.size() > 0)
			{
				return true;
			}else
			{
				return false;
			}
		} 
	}
	

	/**
	 * 检验是否不良品入非不良品仓库
	 * @param ERPLOcxation
	 * @param packingGrade
	 * @return
	 */
	public Boolean JudgeMtaerialStateWithERPLocation(String ERPLOcxation,String packingGrade)
	{
		String DetailSql = "SELECT be.TYPE,be.TYPES FROM BS_ERPLOCATION be WHERE ERPLOCATIONNAME = :ERPLOCATION ";
		Map<String, Object> DetailMap = new HashMap<String, Object>();
		DetailMap.put("ERPLOCATION",ERPLOcxation ); 
		List<Map<String,Object>>  ShipDetaillist = IDMFrameServiceProxy.getSqlTemplate().queryForList(DetailSql, DetailMap);
		if(!Objects.isNull(ShipDetaillist) &&  !ShipDetaillist.isEmpty()) {
			String ckType = ShipDetaillist.get(0).get("TYPE") == null ? "" : ShipDetaillist.get(0).get("TYPE").toString();
			String ckTypes = ShipDetaillist.get(0).get("TYPES") == null ? "" : ShipDetaillist.get(0).get("TYPES").toString();
			if("Y".equals(ckTypes)) {
				return true;
			}
			if ("NG".equals(packingGrade) && !"2".equals(ckType)) {
				return false;
			} else if ("OK".equals(packingGrade) && "2".equals(ckType)) {
				return false;
			}
			return true;
		}
		return false;
	}
	
	/**
	 * 检验标签是否入库时时否在库
	 * @param erpLocation
	 * @param materialPackingName
	 * @return 
	 * @return
	 */
	public void IsAtMaterialPacking(String materialPackingName, String erpFactory, String erpLocation,String locationName, String materialSpecName)
	{
		//判断条码是否存在且状态是在库
				String sql = "SELECT M.STOCKSTATE,M.RECEIVEACTNO FROM MATERIALPACKING M WHERE M.MATERIALPACKINGNAME = :MATERIALPACKINGNAME";

				Map<String, Object> bindMap = new HashMap<String, Object>();
				bindMap.put("MATERIALPACKINGNAME",materialPackingName); 
				bindMap.put("ERPFACTORY",erpFactory); 
				bindMap.put("ERPLOCATION",erpLocation); 
				bindMap.put("LOCATIONNAME",locationName); 
				List<Map<String,Object>> list = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
				if(list.size() > 1) {
					throw new GlobalException("条码不止一个,请联系IT处理！");
				}
				if (list.size() == 1) {
					for (Map<String, Object> map : list) {
						if (!"Created".equals(map.get("STOCKSTATE").toString())) {
							throw new GlobalException("条码已存在且不为创建状态,请联系IT处理！");
						}
					}
				}
				//恒温恒湿管控
				if(!(JudgeMentERPLocationAndMaterialSpec(erpLocation,materialSpecName)))
				{
					throw new GlobalException("物料为:"+materialSpecName+" 仓库为:"+erpLocation+" ,不符合恒温恒湿管理");
				}
				String sql2 = "SELECT\r\n" + 
						"	BMS.LOTNAME MATERIALPACKINGNAME,\r\n" + 
						"	BMS.PRODUCTSPECNAME MATERIALSPECNAME,\r\n" + 
						"	BMS.PHASE ,BMS.SDK_ID,\r\n" + 
						"	SS.SPECNAME ,\r\n" + 
						"	m.DESC_CN ,\r\n" + 
						"	BMS.QTY MATERIALQUANTITY ,\r\n" + 
						"	BMS.REQUESTNAME RECEIVEREQUESTNAME ,\r\n" + 
						"	BMS.CHARGE,BMS.PACKINGGRADE, :ERPFACTORY ERPFACTORY, :ERPLOCATION ERPLOCATION, :LOCATIONNAME LOCATIONNAME\r\n" + 
						"FROM\r\n" + 
						"	BS_MES_SHIPPED BMS LEFT JOIN MATERIALSPEC m ON m.MATERIALSPECNAME = BMS.PRODUCTSPECNAME \r\n" + 
						"	LEFT JOIN SDK_SPEC SS ON SS.SDK_ID = BMS.SDK_ID \r\n" + 
						"WHERE\r\n" + 
						"	BMS.LOTNAME = :MATERIALPACKINGNAME\r\n" + 
						"	AND (BMS.RECEIVE_FLAG IS NULL\r\n" + 
						"		OR BMS.RECEIVE_FLAG <> 'Y')";
				List<Map<String,Object>> list2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql2, bindMap);
				if(list2.size() != 1) {
					throw new GlobalException("生产未完成入库,请联系IT处理！");
				}
				
				// 良品与不良品与仓校验
				String packingGrade = list2.get(0).get("PACKINGGRADE") == null ? "" : list2.get(0).get("PACKINGGRADE").toString();
				Boolean judgeMtaerialStateWithERPLocation = JudgeMtaerialStateWithERPLocation(erpLocation, packingGrade);
				if (!judgeMtaerialStateWithERPLocation) {
					throw new GlobalException("仓库类型和物料品质等级不一致");
				}
				
				// 判断是否是熟化仓
				if(!(JudgeMentIsOvenSpec(erpLocation,materialSpecName)))
				{
					throw new GlobalException("物料为:"+materialSpecName+" 仓库为:"+erpLocation+" ,熟化对应不一致");
				}
	}
	
	/**
	 * 检验是否是否有组织权限
	 * @param password
	 * @param orgNo
	 * @return
	 */
	public Boolean orgPermission(String userId, String password, String orgNo)
	{
		String sql = "SELECT ORGNO FROM USERINFO WHERE USERID =:USERID AND PASSWORD =:PASSWORD";
		Map<String, Object> bindMapOrg = new HashMap<String, Object>();
		bindMapOrg.put("USERID",userId); 
		bindMapOrg.put("PASSWORD",password); 
		// 此物料的仓库是否是调拨单的出库仓库,如果无记录，则提示，系统无此条
		List<Map<String,Object>> orgList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMapOrg);
		if(orgList.get(0).get("ORGNO") == null || "".equals(orgList.get(0).get("ORGNO"))) {
//			throw new GlobalException("组织不存在");
		}else {
			String orgs = (String)orgList.get(0).get("ORGNO");
			if(orgs.contains(orgNo)) {
				return true;
			}
			
		}
		return false;
	}
	
	/**
	 * 校验物料状态是否可以冲销
	 * @param undoId
	 * @return
	 * @throws Exception
	 */
	public Boolean CheckUndoCondition(String undoId) throws RuntimeException
	{
		String sql = " SELECT bm.UNDOID , BM .MATERIALPACKINGNAME FROM BS_MATERIALPACKINGUNDOINFO bm WHERE UNDOID = :UNDOID \r\n" + 
				"       MINUS \r\n" + 
				"       SELECT m.UNDOID , m.MATERIALPACKINGNAME FROM MATERIALPACKING m WHERE UNDOID = :UNDOID";
		Map<String, Object> bindMapOrg = new HashMap<String, Object>();
		bindMapOrg.put("UNDOID",undoId); 
		// 判断是否有其他操作影响了物料状态
		List<Map<String,Object>> undoList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMapOrg);
		if(undoList != null && undoList.size() > 0) {
			throw new RuntimeException("物料状态发生了改变，无法完成冲销");
		}else {
			
		}
		return true;
	}
	
	/**
	 * 发货写入条码拆分表
	 * @param materialPackingName
	 * @param shipRequestName
	 * @param shipRequestDetailName
	 * @param qty
	 */
	public void fahuoSplit (String materialPackingName, String shipRequestName,String shipRequestDetailName, BigDecimal qty) {
		String updateSql = "UPDATE MATERIALSHIPREQUESTDETAIL T SET T.SALEOUTQUANTITY = NVL(T.SALEOUTQUANTITY, 0) + :QTY "
				+ "WHERE T.SHIPREQUESTNAME = :SHIPREQUESTNAME AND T.SHIPREQUESTDETAILNAME  = :SHIPREQUESTDETAILNAME";
		String insertSql = "INSERT INTO MATERIALPACKINGSUB T (T.MATERIALPACKINGNAME,T.RECEIVEREQUESTNAME,T.RECEIVEREQUESTDETAILNAME,T.QTY) \r\n" + 
				"VALUES (:MATERIALPACKINGNAME,:SHIPREQUESTNAME,:SHIPREQUESTDETAILNAME,:QTY)";
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		hashMap.put("SHIPREQUESTDETAILNAME", shipRequestDetailName);
		hashMap.put("MATERIALPACKINGNAME", materialPackingName);
		hashMap.put("QTY", qty);
		IDMFrameServiceProxy.getSqlTemplate().update(updateSql, hashMap);
		IDMFrameServiceProxy.getSqlTemplate().update(insertSql, hashMap);
	}
	
	/**
	 * 更新订单状态
	 * @param shipRequestName
     */
	public void updateShipRequestState (String shipRequestName) {
		//String sql = "SELECT 1  FROM MATERIALSHIPREQUESTDETAIL M WHERE SHIPREQUESTNAME = :SHIPREQUESTNAME HAVING SUM(M.REQUESTQUANTITY - M.ASSIGNEDQUANTITY) >= 0";
		String sql="SELECT * FROM (\r\n" + 
				" SELECT m.MATERIALSPECNAME,sum(m.REQUESTQUANTITY)RQTY,sum(m.ASSIGNEDQUANTITY)AQTY  \r\n" + 
				" FROM MATERIALSHIPREQUESTDETAIL M WHERE SHIPREQUESTNAME = :SHIPREQUESTNAME\r\n" + 
				" GROUP BY m.MATERIALSPECNAME )\r\n" + 
				" WHERE AQTY-RQTY<0 ";
		Map<String, Object> hashMap = new HashMap<String,Object> ();
		hashMap.put("SHIPREQUESTNAME", shipRequestName);
		List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
		//当查不出未拣配完成的料号则将单据置为Completed状态。zd.2024.9.24
		if (queryForList == null || queryForList.size() < 1) {
			//判断是否存在未出库但是已经绑定了的box
			sql = "SELECT 1 FROM MATERIALPACKING T WHERE T.SHIPREQUESTNAME = :SHIPREQUESTNAME AND T.STOCKSTATE = 'Stocked'";
			List<Map<String,Object>> queryForList2 = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList2 == null || queryForList2.size() < 1) {
				String updateSql = "UPDATE MATERIALSHIPREQUEST T SET T.SHIPREQUESTSTATE = 'Completed' WHERE T.SHIPREQUESTNAME  = :SHIPREQUESTNAME";
				IDMFrameServiceProxy.getSqlTemplate().update(updateSql, hashMap);
			}
		}
	}
	
	//成品入库拆分批次时存储物料凭证。zd.2024.9.27
	public void SaveUnDoInfoBySplitCharge( String undoid,String commitDate ) {

		String insertUnDOMaterialPackingInfo = "insert into BS_MATERIALPACKINGUNDOINFO\r\n" + 
				"  (unDoID,\r\n" + 
				"   preundoid,\r\n" + 
				"   UNDODETAILNAME,\r\n" + 
				"   undoidstate,\r\n" + 
				"   UNDOIDDATE,\r\n" + 
				"   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" + 
				"   MATERIALQUANTITY,\r\n" + 
				"   PACKINGSTATE,\r\n" + 
				"   OLDPACKINGSTATE,\r\n" + 
				"   STOCKSTATE,\r\n" + 
				"   OLDSTOCKSTATE,\r\n" + 
				"   HOLDSTATE,\r\n" + 
				"   AREANAME,\r\n" + 
				"   LOCATIONNAME,\r\n" + 
				"   DURABLENAME,\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" + 
				"   STOCKINTYPE,\r\n" + 
				"   RECEIVETIME,\r\n" + 
				"   RECEIVEUSER,\r\n" + 
				"   SHIPREQUESTNAME,\r\n" + 
				"   SHIPREQUESTDETAILNAME,\r\n" + 
				"   SHIPTIME,\r\n" + 
				"   SHIPUSER,\r\n" + 
				"   REASONCODETYPE,\r\n" + 
				"   OQARESULTSTATE,\r\n" + 
				"   OQARESULT,\r\n" + 
				"   ERPLOCATION,\r\n" + 
				"   ERPFACTORY,\r\n" + 
				"   REASONCODE,\r\n" + 
				"   CHARGE,\r\n" + 
				"   SUPPLIERNAME,\r\n" + 
				"   ETCSTATE,\r\n" + 
				"   ERPEVENTTIME,\r\n" + 
				"   NPFLAG)\r\n" + 
				"  WITH MS AS\r\n" + 
				"   (SELECT MATERIALPACKINGNAME, MAX(RECEIVEREQUESTNAME) RECEIVEREQUESTNAME, sum(qty) QTY\r\n" + 
				"      FROM MATERIALPACKINGSUB m\r\n" + 
				"     WHERE m.MATERIALPACKINGNAME IN\r\n" + 
				"           (SELECT MATERIALPACKINGNAME\r\n" + 
				"              from MATERIALPACKING a\r\n" + 
				"             where a.unDoID = :UNDOID)\r\n" + 
				"     GROUP BY MATERIALPACKINGNAME )\r\n" + 
				"  select unDoID,\r\n" + 
				"         '' as preundoid,\r\n" + 
				"         UNDODETAILNAME,\r\n" + 
				"         'Completed' as undoidstate,\r\n" + 
				"         :commitDate AS UNDOIDDATE,\r\n" + 
				"         SITENAME,\r\n" + 
				"         m.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" + 
				"         MS.QTY,\r\n" + 
				"         PACKINGSTATE,\r\n" + 
				"         OLDPACKINGSTATE,\r\n" + 
				"         STOCKSTATE,\r\n" + 
				"         OLDSTOCKSTATE,\r\n" + 
				"         HOLDSTATE,\r\n" + 
				"         AREANAME,\r\n" + 
				"         LOCATIONNAME,\r\n" + 
				"         DURABLENAME,\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" + 
				"         MS.RECEIVEREQUESTNAME,\r\n" + 
				"         '',\r\n" + 
				"         MAKEDATE,\r\n" + 
				"         RECEIVEACTNO,\r\n" + 
				"         STOCKINTYPE,\r\n" + 
				"         RECEIVETIME,\r\n" + 
				"         RECEIVEUSER,\r\n" + 
				"         SHIPREQUESTNAME,\r\n" + 
				"         SHIPREQUESTDETAILNAME,\r\n" + 
				"         SHIPTIME,\r\n" + 
				"         SHIPUSER,\r\n" + 
				"         REASONCODETYPE,\r\n" + 
				"         OQARESULTSTATE,\r\n" + 
				"         OQARESULT,\r\n" + 
				"         ERPLOCATION,\r\n" + 
				"         ERPFACTORY,\r\n" + 
				"         REASONCODE,\r\n" + 
				"         CHARGE,\r\n" + 
				"         SUPPLIERNAME,\r\n" + 
				"         ETCSTATE,\r\n" + 
				"         ERPEVENTTIME,\r\n" + 
				"         NPFLAG\r\n" + 
				"    from MATERIALPACKING m\r\n" + 
				"    LEFT JOIN MS\r\n" + 
				"      ON m.MATERIALPACKINGNAME = MS.MATERIALPACKINGNAME\r\n" + 
				"   where m.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 list
	 * @param undoid
	 * @param makeEventInfo
	 */
	public void SaveUnDoInfo(List<String> list,  String undoid, EventInfo makeEventInfo, String erpLocation,String commitDate,String shipRequestType, 
			String eventUser,String shipRequestName) {

		String updateUndoInfo = "UPDATE MATERIALPACKING T SET T.unDoID = :unDoID WHERE T.MATERIALPACKINGNAME = :MATERIALPACKINGNAME "
				+ "AND ERPLOCATION = :ERPLOCATION AND STOCKSTATE = 'Stocked'";
		for (String s : list) {
			Map<String,Object> map = new HashMap<String,Object> ();
			map.put("MATERIALPACKINGNAME", s);
			map.put("ERPLOCATION", erpLocation);
			map.put("unDoID", undoid);
			IDMFrameServiceProxy.getSqlTemplate()
			.update(updateUndoInfo, map);
		}
		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,"
				+ "DECODE(MATERIALQUANTITY2,0,MATERIALQUANTITY,MATERIALQUANTITY2) 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("UNDOIDDATE", commitDate);
		bindMap_insert.put("UNDOID", undoid);
		bindMap_insert.put("SHIPREQUESTNAME", shipRequestName);
		bindMap_insert.put("USERID", eventUser);
		IDMFrameServiceProxy.getSqlTemplate()
				.update(insertUnDOMaterialPackingInfo, bindMap_insert);
		//更新Box状态，如果数量发货数量和条码数量相同则全部出货，小于则更新条码数量，取消单据解绑
		for (String s : list) {
			MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",s);
			MaterialPacking selectByKey = MaterialPackingServiceProxy.getMaterialPackingService().selectByKey(materialPackingKey);
			//通过sql取数量,
			String SLsQL = "SELECT m.MATERIALPACKINGNAME,m.MATERIALQUANTITY,"
					+ "m. MATERIALQUANTITY2  FROM MATERIALPACKING m"
					+ " WHERE MATERIALPACKINGNAME = :MATERIALPACKINGNAME ";
			Map<String, Object> hashMap2 = new HashMap<String,Object> ();
			hashMap2.put("MATERIALPACKINGNAME", s);
			List<Map<String, Object>> queryForList3 = IDMFrameServiceProxy.getSqlTemplate().queryForList(SLsQL, hashMap2);
			String materialQuantity = "";
			String materialQuantity2 = "";
			if (queryForList3 != null && queryForList3.size() > 0) {
				materialQuantity2 = queryForList3.get(0).get("MATERIALQUANTITY2").toString();
				materialQuantity = queryForList3.get(0).get("MATERIALQUANTITY").toString();
			} else {
//				throw new CustomException("单据" + shipRequestName + "库存"  + materialPackingName  + "不存在");
			}
			BigDecimal fmaterialQuantity = new BigDecimal(materialQuantity);
			BigDecimal fmaterialQuantity2 = new BigDecimal(materialQuantity2);
			String erpLocation2 = selectByKey.getErpLocation();
			if (erpLocation.equals(erpLocation2)) {
				if (fmaterialQuantity.compareTo(fmaterialQuantity2) > 0  && fmaterialQuantity2.compareTo(BigDecimal.ZERO) != 0) {
					Map<String, Object> hashMap = new HashMap<String,Object> ();
					hashMap.put("materialQuantity", fmaterialQuantity.subtract(fmaterialQuantity2));
//					hashMap.put("materialQuantity2", 0);
					hashMap.put("shipRequestName", "");
					hashMap.put("shipRequestDetailName", "");
					SetEventInfo setEventInfo = new SetEventInfo();
					setEventInfo.setUserColumns(hashMap);
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
					String updateSql = "UPDATE MATERIALPACKING T SET T.MATERIALQUANTITY2 = 0 ,T.MATERIALQUANTITY = :MATERIALQUANTITY  "
							+ "WHERE MATERIALPACKINGNAME = :MATERIALPACKINGNAME";
					hashMap2.put("MATERIALQUANTITY2", materialQuantity2);
					hashMap2.put("MATERIALQUANTITY", fmaterialQuantity.subtract(fmaterialQuantity2));
					IDMFrameServiceProxy.getSqlTemplate().update(updateSql, hashMap2);
				} else {
					Map<String, Object> hashMap = new HashMap<String,Object> ();
					hashMap.put("stockState", "StockOut");
//				hashMap.put("shipRequestName", "");
//				hashMap.put("shipRequestDetailName", "");
					SetEventInfo setEventInfo = new SetEventInfo();
					setEventInfo.setUserColumns(hashMap);
					MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
				}
			}
		}

	}

/**
 * 保存undo信息
 * 该方法用于更新和插入与物料包装相关的undo信息，根据提供的参数执行不同的数据库操作
 *
 * @param list 包含物料包装名称的列表
 * @param undoid undo的标识符，用于区分不同的undo操作
 * @param makeEventInfo 包含事件信息的对象，用于更新物料包装的最后事件信息
 */
public void SaveUnDoInfo_ForSap(List<String> list,  String undoid, EventInfo makeEventInfo) {

    // 更新事件名
    // 此SQL语句用于更新物料包装的最后事件名称、评论和时间
    String sqlu="UPDATE MATERIALPACKING m SET m.LASTEVENTNAME=:LASTEVENTNAME,m.LASTEVENTCOMMENT =:LASTEVENTCOMMENT,m.LASTEVENTTIME =SYSDATE \r\n" +
            " WHERE m.SITENAME =:SITENAME AND m.MATERIALPACKINGNAME in (:BOXLIST)";
    Map<String,Object> bpu = new HashMap<String,Object> ();
    bpu.put("LASTEVENTNAME", makeEventInfo.getEventName());
    bpu.put("LASTEVENTCOMMENT", makeEventInfo.getEventComment());
    bpu.put("SITENAME", "SDK");
    bpu.put("BOXLIST", list);
    IDMFrameServiceProxy.getSqlTemplate().update(sqlu, bpu);

    // 销售交货单不返回凭证
    // 如果undoid不等于"S"，则执行更新和插入操作，以记录undo信息
    if(!StringUtils.equals("S", undoid))
    {
        // 更新undo信息
        // 此SQL语句用于更新物料包装的undoid，仅当库存状态为'Stocked'时
        String updateUndoInfo = "UPDATE MATERIALPACKING T SET T.unDoID = :unDoID WHERE T.MATERIALPACKINGNAME in (:MATERIALPACKINGNAME) "
                + " AND STOCKSTATE = 'Stocked'";

        Map<String,Object> map = new HashMap<String,Object> ();
        map.put("MATERIALPACKINGNAME", list);
        map.put("unDoID", undoid);
        IDMFrameServiceProxy.getSqlTemplate().update(updateUndoInfo, map);


        // 插入undo物料包装信息
        // 此SQL语句用于插入新的记录到BS_MATERIALPACKINGUNDOINFO表中，记录详细的undo信息
        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,"
                + "DECODE(MATERIALQUANTITY2,0,MATERIALQUANTITY,MATERIALQUANTITY2) 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("UNDOIDDATE", "");
        bindMap_insert.put("UNDOID", undoid);
        IDMFrameServiceProxy.getSqlTemplate()
                .update(insertUnDOMaterialPackingInfo, bindMap_insert);
    }
    // 更新Box状态
    // 根据物料数量和包装数量，更新物料包装的状态和相关属性
    for (String s : list) {
        MaterialPackingKey materialPackingKey = new MaterialPackingKey("SDK",s);
        com.cim.idm.wmspackage.materialpacking.management.data.MaterialPacking selectByKey = MaterialPackingServiceProxy.getMaterialPackingService().selectByKey(materialPackingKey);
        double materialQuantity = selectByKey.getMaterialQuantity();
        double materialQuantity2 = selectByKey.getMaterialQuantity2();
        BigDecimal fmaterialQuantity = new BigDecimal(materialQuantity);
        BigDecimal fmaterialQuantity2 = new BigDecimal(materialQuantity2);
        if (fmaterialQuantity.compareTo(fmaterialQuantity2) > 0  && fmaterialQuantity2.compareTo(BigDecimal.ZERO) != 0) {
            Map<String, Object> hashMap = new HashMap<String,Object> ();
            hashMap.put("materialQuantity", fmaterialQuantity.subtract(fmaterialQuantity2));
            hashMap.put("materialQuantity2", 0);
            hashMap.put("shipRequestName", "");
            hashMap.put("shipRequestDetailName", "");
            SetEventInfo setEventInfo = new SetEventInfo();
            setEventInfo.setUserColumns(hashMap);
            MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
        } else {
            Map<String, Object> hashMap = new HashMap<String,Object> ();
            hashMap.put("stockState", "StockOut");
            SetEventInfo setEventInfo = new SetEventInfo();
            setEventInfo.setUserColumns(hashMap);
            MaterialPackingServiceProxy.getMaterialPackingService().setEvent(materialPackingKey, makeEventInfo, setEventInfo);
        }
    }

}


	/**
	 * 将物料信息发送到MES系统（制造执行系统）的新版本方法
	 * 该方法根据提供的发票号和收货活动号，查询数据库以获取相关的接收位置和工厂信息，
	 * 然后调用MESServiceImpl的OutMaterialInfoSend_NEW方法将这些信息连同箱号列表一起发送到MES系统
	 *
	 * @param invoiceNo 发票编号，用于标识特定的物料发货请求
	 * @param receiveActNo 收货活动编号，用于指定收货活动
	 * @param boxList 箱号列表，包含所有需要发送的物料箱号
	 */
	public void sendMaterialInfoToMES_NEW(String invoiceNo,String receiveActNo,List<String> boxList){
	    // 定义SQL查询语句，用于获取物料接收位置和接收工厂信息
	    String sql="SELECT m.ERPRECEIVELOCATION,m.ERPRECEIVEFACTORY FROM MATERIALSHIPREQUEST a,MATERIALSHIPREQUESTDETAIL  m \r\n" +
	            " WHERE m.SHIPREQUESTNAME =:SHIPREQUESTNAME and m.SHIPREQUESTNAME=a.SHIPREQUESTNAME "
	            + " AND a.SHIPREQUESTDETAILTYPE in('311','261')";
	    // 创建参数映射，用于执行SQL查询
	    Map<String, Object> bp=new HashMap<>();
	    bp.put("SHIPREQUESTNAME", invoiceNo);
	    try {
	        // 执行SQL查询，获取查询结果列表
	        List<Map<String, Object>> sr=IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bp);
	        if(sr.size()>0)
	        {

	            // 创建MESServiceImpl实例，用于调用发送物料信息到MES系统的方法
	            MESServiceImpl mesService=new MESServiceImpl() ;
	            // 调用MESServiceImpl的OutMaterialInfoSend_NEW方法，发送物料信息到MES系统
	            mesService.OutMaterialInfoSend_NEW(invoiceNo, "SDK", receiveActNo, "",
	                    sr.get(0).get("ERPRECEIVELOCATION")==null?"":sr.get(0).get("ERPRECEIVELOCATION").toString()
	                    ,sr.get(0).get("ERPRECEIVEFACTORY")==null?"":sr.get(0).get("ERPRECEIVEFACTORY").toString(),boxList );
	        }
	    } catch (CustomException e) {
	        // 异常处理：打印异常堆栈跟踪信息
	        e.printStackTrace();
	    }
	}

	public void sendMaterialInfoToMES_NEW2(String invoiceNo,String receiveActNo,List<String> boxList){
		// 定义SQL查询语句，用于获取物料接收位置和接收工厂信息
		String sql="SELECT m.ERPRECEIVELOCATION,m.ERPRECEIVEFACTORY FROM MATERIALSHIPREQUEST a,MATERIALSHIPREQUESTDETAIL  m \r\n" +
				" WHERE m.SHIPREQUESTNAME =:SHIPREQUESTNAME and m.SHIPREQUESTNAME=a.SHIPREQUESTNAME "
				+ " AND a.SHIPREQUESTDETAILTYPE in('311','261')";
		// 创建参数映射，用于执行SQL查询
		Map<String, Object> bp=new HashMap<>();
		bp.put("SHIPREQUESTNAME", invoiceNo);
		try {
			// 执行SQL查询，获取查询结果列表
			List<Map<String, Object>> sr=IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bp);
			if(sr.size()>0)
			{

				// 创建MESServiceImpl实例，用于调用发送物料信息到MES系统的方法
				MESServiceImpl mesService=new MESServiceImpl() ;
				// 调用MESServiceImpl的OutMaterialInfoSend_NEW方法，发送物料信息到MES系统
				mesService.OutMaterialInfoSend_NEW2(invoiceNo, "SDK", receiveActNo, "",
						sr.get(0).get("ERPRECEIVELOCATION")==null?"":sr.get(0).get("ERPRECEIVELOCATION").toString()
						,sr.get(0).get("ERPRECEIVEFACTORY")==null?"":sr.get(0).get("ERPRECEIVEFACTORY").toString(),boxList );
			}
		} catch (CustomException e) {
			// 异常处理：打印异常堆栈跟踪信息
			e.printStackTrace();
		}
	}


	
}
