package com.cim.idm.mwmsextend.materialpacking.service;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.Document;
import org.jdom.Element;

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.xml.JdomUtils;
import com.cim.idm.mwmsextend.erpinterface.service.ERPInterfaceServiceImpl;
import com.cim.idm.wmsextend.generic.errorHandler.CustomException;
import com.cim.idm.mwmsextend.transport.management.data.TransportCommandKey;
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.materialreceiverequestact.MaterialReceiveRequestActServiceProxy;
import com.cim.idm.wmspackage.receiverequestbatch.management.data.MaterialReceiveActKey;

public class MaterialPackingServiceUtil {

	private static Log log = LogFactory.getLog(ERPInterfaceServiceImpl.class);	

	public List<MaterialPacking> getPackingListFromInvoiceNo(String invoiceNo){
		String condition = " where  shipRequestName=? ";
		Object[] bindSet = {invoiceNo};
		List<MaterialPacking> select = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet, null);
		return select;
	}
	
	public List<MaterialPacking> getMaterialPackingListByCondition(String siteName,String conditionName,String condition)throws Exception
	{
		try{
	
		StringBuffer sql = new StringBuffer();
		Object[] bindSet = {};
		sql.append(" where ");
		sql.append("sitename ="+"'"+siteName+"'");
		sql.append(" and PACKINGSTATE='Released' and STOCKSTATE='Stocked' and ");
		sql.append(conditionName);
		if (conditionName.equalsIgnoreCase("ERPLOCATION")  || conditionName.equalsIgnoreCase("MATERIALSPECNAME")){
			
			sql.append(" in "+condition);			
		}
		else if (conditionName.equalsIgnoreCase("LOCATIONNAME")){
			sql.append(" between "+condition);
		}
		List<MaterialPacking> select = MaterialPackingServiceProxy.getMaterialPackingService().select(sql.toString(), bindSet);
		return select;
		
		}
		catch(Exception e){e.printStackTrace();
		throw e;}
	}
		
	public List<String> getKPackingListFromShipRequest(String invoiceNo){
	    String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
	        " WHERE  shipRequestName= ?   and StockState='Stocked' \n" + 
	        " and specialflag = 'K'" +
	        " ORDER BY MATERIALPACKINGNAME";
	    Object[] bindSet = new Object[] { invoiceNo};
	    String[][]  result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
	    List<String> returnValue = new ArrayList<String>();
	    for (String[] row : result)
	      returnValue.add(row[0].toString());
	    
	    log.info("-------------------------------------------");
	    log.info(invoiceNo+"下有K类零库存："+returnValue.toString());
	    log.info("-------------------------------------------");
	    return returnValue;
	 }
	
	 public void shipWithoutK(String shiprequestName){
	    log.info("-------------------------------------------");
	    log.info(shiprequestName+"下无K类库存。。。");
	    log.info("-------------------------------------------");
	 }

	public List<MaterialPacking> getMaterialPackingListByKeyList(List<MaterialPackingKey> keyList)throws Exception
	{
		try{
		StringBuffer sql = new StringBuffer();
		Object[] bindSet = {};
		sql.append("where");
		
//		for(int i=0;i<keyList.size();i++)
//		{
//			if(i!=keyList.size()-1){
//			
//			sql.append(" ( "); 
//			sql.append(" sitename = ");
//			sql.append("'" + keyList.get(i).getSiteName() + "'");
//			sql.append(" and");
//			sql.append(" materialpackingname = ");
//			sql.append("'" + keyList.get(i).getMaterialPackingName() + "'");
//			sql.append(" ) ");
//			sql.append(" or ");
//			}
//			else{
//				sql.append(" ( "); 
//				sql.append(" sitename = ");
//				sql.append("'" + keyList.get(i).getSiteName() + "'");
//				sql.append(" and");
//				sql.append(" materialpackingname = ");
//				sql.append("'" + keyList.get(i).getMaterialPackingName() + "'");
//				sql.append(" ) ");
//				//sql.append(";");				
//			}	
//		}
		
		/*使用foreach方法替代*/
		for (MaterialPackingKey key:keyList){
			sql.append(" ( "); 
			sql.append(" sitename = ");
			sql.append("'" + key.getSiteName() + "'");
			sql.append(" and");
			sql.append(" materialpackingname = ");
			sql.append("'" + key.getMaterialPackingName() + "'");
			sql.append(" ) ");
			sql.append(" or ");			
		}
		
		if (sql.length()>5)
		{
			sql.delete(sql.length()-3, sql.length());
	    }
		
		List<MaterialPacking> select = MaterialPackingServiceProxy.getMaterialPackingService().select(sql.toString(), bindSet);
		return select;
		}
		catch(Exception e){e.printStackTrace();
		throw e;}
	}

	public Document writeDocWithMaterialPackingList(Document doc,List<MaterialPacking> boxList){
		
		doc.getRootElement().removeChild("Body");
		JdomUtils.addElement(doc.getRootElement(), "Body", null);
		Field[] fields = MaterialPacking.class.getDeclaredFields();
		
		for (MaterialPacking box:boxList)
		{
			Element  materialPacking= JdomUtils.createElement("MATERIALPACKING");
			for (Field field : fields)
			{
				Method method = null;  
                Object value = null;  
                String name = field.getName();  
                String upperName = name.substring(0, 1).toUpperCase()  
                        + name.substring(1);  
                try {  
                    method = MaterialPacking.class.getMethod("get" + upperName);  
                    value = method.invoke(box);  
                    if (value != null && value.getClass().equals(MaterialPackingKey.class))
                    {
                    	Field[] keyfields = MaterialPackingKey.class.getDeclaredFields();
                    	for (Field keyField : keyfields)
            			{
            				Method keyMethod = null;  
                            Object keyValue = null;  
                            String keyName = keyField.getName();  
                            String keyUpperName = keyName.substring(0, 1).toUpperCase()  
                                    + keyName.substring(1);  
                            try {  
                            	keyMethod = MaterialPackingKey.class.getMethod("get" + keyUpperName);  
                            	keyValue = keyMethod.invoke(value); }
                            catch (Exception e) {  
                                // TODO Auto-generated catch block  
                                e.printStackTrace();  
                            }  
                            JdomUtils.addElement(materialPacking, keyName.toUpperCase(),keyValue.toString());
            			}
                    	
                    }
                    else if (value == null) {  
                        continue;  
                    }                     
                } catch (Exception e) {  
                    // TODO Auto-generated catch block  
                    e.printStackTrace();  
                }   
				if (!value.getClass().equals(MaterialPackingKey.class))
				{
				JdomUtils.addElement(materialPacking, name.toUpperCase(),value.toString());
				}
			}
			JdomUtils.appendChildElement(doc.getRootElement().getChild("Body"),materialPacking);
		}
		return doc;	
	}
	
	public List<String> getPackingListFromShipRequest(String invoiceNo,String receiveActNo){
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  shipRequestName= ? and RECEIVEACTNO = ?  and StockState='Stocked' \n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { invoiceNo,receiveActNo};
		//Object[] bindSet1 = new Object[] { receiveActNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		//List result = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().queryForList(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	//获取重发的BoxList
	public List<String> getPackingListForPMS(String invoiceNo,String receiveActNo){
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  shipRequestName= ? and RECEIVEACTNO = ?  and StockState='StockOut' \n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { invoiceNo,receiveActNo};
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	
	//获取补账的BoxList
	public List<String> getPackingListForAccount(String shipRequestName,String shipRequestDetailName,String receiveActNo){
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  shipRequestName= ? and shipRequestDetailName = ?  and RECEIVEACTNO = ?  and StockState='StockOut' \n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { shipRequestName,shipRequestDetailName,receiveActNo};
		//Object[] bindSet1 = new Object[] { receiveActNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		//List result = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().queryForList(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	
	public List<String> getPackingListFromShipRequest(String invoiceNo){
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  shipRequestName= ?  and stockstate='Stocked' and MATERIALPACKINGTYPE='Box' \n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { invoiceNo};
		//Object[] bindSet1 = new Object[] { receiveActNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		//List result = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().queryForList(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	public List<String> getStockOutBoxFromShipRequest(String shiprequest)
	{
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  shipRequestName= ?  and stockstate='StockOut'\n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { shiprequest};
		//Object[] bindSet1 = new Object[] { receiveActNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		//List result = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().queryForList(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
	}

	public List<MaterialPacking> getScrapBoxFromReceiveactNo(String receiveactno, String siteName)
	{
		List<MaterialPacking> mplist = new ArrayList<MaterialPacking>();
		String sql = "SELECT MATERIALPACKINGNAME, MATERIALSPECNAME,CHARGE FROM MATERIALPACKING\n" +
				" WHERE  receiveactno= :receiveActNo  and  sitename= :siteName and stockstate='Stocked' and materialpackingtype='Box' " + 
				" ORDER BY MATERIALSPECNAME";
		Map<String, Object> bindMap1 = new HashMap<String, Object>();
		bindMap1.put("receiveActNo", receiveactno);
		bindMap1.put("siteName", siteName);
		List<Map<String, Object>> sqlResult
			= IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap1);
		for(int i=0;i<sqlResult.size();i++)
		{
			MaterialPacking materialpacking = new MaterialPacking();
			MaterialPackingKey materialpackingKey = new MaterialPackingKey();
			materialpacking.setMaterialSpecName(sqlResult.get(i).get("MATERIALSPECNAME").toString());
			materialpacking.setCharge(sqlResult.get(i).get("CHARGE")==null?"":sqlResult.get(i).get("CHARGE").toString());
			materialpackingKey.setMaterialPackingName(sqlResult.get(i).get("MATERIALPACKINGNAME").toString());
			materialpackingKey.setSiteName(siteName);
			materialpacking.setKey(materialpackingKey);
			mplist.add(materialpacking);
		}
	
	return mplist;
	}
	public List<MaterialPackingKey> getBoxFromReceiveactNo(String receiveactno, String siteName,String PO,String PODetail)
	{
		List<MaterialPackingKey> mplist = new ArrayList<MaterialPackingKey>();
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  receiveactno= :receiveActNo  and RECEIVEREQUESTNAME=:receiveRequestName and RECEIVEREQUESTDETAILNAME=:receiveRequestDetailName AND  sitename= :siteName and PACKINGSTATE='Created'\n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Map<String, Object> bindMap1 = new HashMap<String, Object>();
		bindMap1.put("receiveActNo", receiveactno);
		bindMap1.put("siteName", siteName);
		bindMap1.put("receiveRequestName", PO);
		bindMap1.put("receiveRequestDetailName", PODetail);
		List<Map<String, Object>> sqlResult
			= IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap1);
		for(int i=0;i<sqlResult.size();i++)
		{
			MaterialPackingKey materialpackingKey = new MaterialPackingKey();
			materialpackingKey.setMaterialPackingName(sqlResult.get(i).get("MATERIALPACKINGNAME").toString());
			materialpackingKey.setSiteName(siteName);
			mplist.add(materialpackingKey);
		}
	
	return mplist;
	}
	public boolean checkUnDoCondition(String siteName,String unDoID, String materialQuantity,
			String unDoDetailName,String eventName)throws Exception  {
		try {	
			
			String sql_UndoBoxInfo=" SELECT SITENAME,UNDOID,MATERIALPACKINGNAME,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE,"+
" STOCKSTATE,ERPLOCATION,ERPFACTORY,OQARESULT,OQARESULTSTATE,MATERIALSPECNAME,MATERIALPROCESSGROUPNAME,NPFLAG"+
    " FROM BS_MATERIALPACKINGUNDOINFO BS"+
            " WHERE BS.UNDOID=:UNDOID "+
            " AND UNDOIDSTATE<>'N'"+
            " AND LASTEVENTNAME<>'MToM'"+ 
            " AND("+
            " (LASTEVENTNAME='OQAConfirm')"+ //查质检冲销的内容
            " OR"+
            " ("+
            " BS.UNDODETAILNAME=:UNDODETAILNAME"+
            " AND BS.SITENAME=:SITENAME"+
            " )"+//
            " OR"+
            " ( "+
            " BS.UNDODETAILNAME=:UNDODETAILNAME"+
            " AND BS.SITENAME=:SITENAME))"//
            ;
		String sql_1="SELECT SITENAME,MATERIALPACKINGNAME,PACKINGGRADE,OQARESULT,OQARESULTSTATE,MATERIALQUANTITY,PACKINGSTATE,"+
			   " STOCKSTATE,ERPLOCATION,ERPFACTORY,LASTEVENTNAME,MATERIALSPECNAME,MATERIALPROCESSGROUPNAME"+
			   " FROM MATERIALPACKING "+
			   " WHERE (SITENAME,MATERIALPACKINGNAME) IN( "+
			   " SELECT SITENAME,DESTMATERIALPACKINGNAMENAME AS MATERIALPACKINGNAME "+
				" FROM BS_MATERIALPACKINGSPLITMERGE "+
				" CONNECT BY  MATERIALPACKINGNAME=PRIOR DESTMATERIALPACKINGNAMENAME "+
				" START WITH SITENAME=:sitename and MATERIALPACKINGNAME=:materialPackingName "+
				" union "+
				" SELECT SITENAME,MATERIALPACKINGNAME "+
				" FROM MATERIALPACKING "+
				" WHERE SITENAME=:sitename and MATERIALPACKINGNAME=:materialPackingName ) "+
				" and packingstate<>'Emptied' and MATERIALQUANTITY !=0 " ;
		
		/*String sql_2="update BS_MATERIALPACKINGUNDOINFO set UNDOIDSTATE=:unDoIDState";
		Map<String, Object> bindMap2 = new HashMap<String, Object>();
		bindMap2.put("unDoIDState", "N");*/
		Map<String, Object> bindMap_UndoBoxInfo = new HashMap<String, Object>();
		bindMap_UndoBoxInfo.put("SITENAME", siteName);
		bindMap_UndoBoxInfo.put("UNDOID", unDoID);
		bindMap_UndoBoxInfo.put("UNDODETAILNAME",unDoDetailName);
		//bindMap_UndoBoxInfo.put("REQUESTDETAILNAME",requestDetailName);
		List<Map<String, Object>> queryResult_UndoBoxInfo = 
				IDMFrameServiceProxy.getSqlTemplate().queryForList(sql_UndoBoxInfo, bindMap_UndoBoxInfo);
		Map<String, Object> bindMap = new HashMap<String, Object>();
		double sumQuantity=0;
		List<MaterialPackingKey> materialPackingKeyList = new ArrayList<MaterialPackingKey>();
		for(int i=0;i<queryResult_UndoBoxInfo.size();i++)
		{
			bindMap.put("sitename", siteName);
			bindMap.put("materialPackingName", queryResult_UndoBoxInfo.get(i).get("MATERIALPACKINGNAME"));
			List<Map<String, Object>> queryResult = 
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql_1, bindMap);
			for(int j=0;j<queryResult.size();j++)
			{
				String PackingGrade;
				String OqaResult;
				String OqaResultState;
				String getPackingGrade=queryResult.get(j).get("PACKINGGRADE")==null?"-":queryResult.get(j).get("PACKINGGRADE").toString();
				String getPackingState=queryResult.get(j).get("PACKINGSTATE")==null?"-":queryResult.get(j).get("PACKINGSTATE").toString();
				String getStockState=queryResult.get(j).get("STOCKSTATE")==null?"-":queryResult.get(j).get("STOCKSTATE").toString();
				String getErpLocation=queryResult.get(j).get("ERPLOCATION").toString();
				String getErpFactory=queryResult.get(j).get("ERPFACTORY").toString();
				String getEventName=queryResult.get(j).get("LASTEVENTNAME").toString();
				String getOqaResult=queryResult.get(j).get("OQARESULT")==null?"-":queryResult.get(j).get("OQARESULT").toString();
				String getOQAGroupName=queryResult.get(j).get("MATERIALPROCESSGROUPNAME")==null?"-":queryResult.get(j).get("MATERIALPROCESSGROUPNAME").toString();			
				String getOqaResultState=queryResult.get(j).get("OQARESULTSTATE")==null?"-":queryResult.get(j).get("OQARESULTSTATE").toString();
				String getMaterialSpecName=queryResult.get(j).get("MATERIALSPECNAME").toString();
				String npFlag=queryResult_UndoBoxInfo.get(i).get("NPFLAG")==null?"N":queryResult_UndoBoxInfo.get(i).get("NPFLAG").toString();
				if(npFlag.equals("Y"))
				{
					PackingGrade="-";
					OqaResult="-";
					OqaResultState="PREOQA";
				}
				else{
					PackingGrade=queryResult_UndoBoxInfo.get(i).get("PACKINGGRADE")==null?"-":queryResult_UndoBoxInfo.get(i).get("PACKINGGRADE").toString();
					OqaResult=queryResult_UndoBoxInfo.get(i).get("OQARESULT")==null?"-":queryResult_UndoBoxInfo.get(i).get("OQARESULT").toString();
					OqaResultState=queryResult_UndoBoxInfo.get(i).get("OQARESULTSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("OQARESULTSTATE").toString();
				}
				String PackingState=queryResult_UndoBoxInfo.get(i).get("PACKINGSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("PACKINGSTATE").toString();
				String StockState=queryResult_UndoBoxInfo.get(i).get("STOCKSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("STOCKSTATE").toString();
				String ErpLocation=queryResult_UndoBoxInfo.get(i).get("ERPLOCATION").toString();
				String ErpFactory=queryResult_UndoBoxInfo.get(i).get("ERPFACTORY").toString();
				String OQAGroupName=queryResult_UndoBoxInfo.get(i).get("MATERIALPROCESSGROUPNAME")==null?"-":queryResult_UndoBoxInfo.get(i).get("MATERIALPROCESSGROUPNAME").toString();
				String MaterialSpecName=queryResult_UndoBoxInfo.get(i).get("MATERIALSPECNAME").toString();
				boolean state=checkState(getPackingGrade,PackingGrade,getPackingState,
						PackingState,getStockState,StockState,getErpLocation,ErpLocation,getErpFactory,
						ErpFactory,getOqaResult,OqaResult,getOqaResultState,OqaResultState,getMaterialSpecName,MaterialSpecName,
						getEventName,eventName,getOQAGroupName,OQAGroupName);
				if(state)
				{
					MaterialPackingKey 	materialPackingKey= new MaterialPackingKey();
					materialPackingKey.setMaterialPackingName(queryResult.get(j).get("MATERIALPACKINGNAME").toString());
					materialPackingKey.setSiteName(siteName);
					materialPackingKeyList.add(materialPackingKey);
					/*MaterialPacking materialPacking=new MaterialPacking();
					materialPacking.setKey(materialPackingKey);
					materialPacking.setUnDoID(queryResult_UndoBoxInfo.get(i).get("PREUNDOID").toString());*/
					sumQuantity+=Double.parseDouble(queryResult.get(j).get("MATERIALQUANTITY").toString());
					//materialPackingList.add(materialPacking);
				}
				else throw new CustomException("CHECKFAILED","","");
			}
		}

		if (sumQuantity >Double.parseDouble(materialQuantity)||sumQuantity==Double.parseDouble(materialQuantity)) //测试消除  需要确认
		{
			
			//IDMFrameServiceProxy.getSqlTemplate().update(sql_2, bindMap2);
			return true;
		}
		else 
			throw new CustomException("CHECKFAILED","","");
		} catch (Exception ex) {
			 ex.printStackTrace();
			 throw ex;
		}	
	}
	
	public boolean checkState(String getPackingGrade,String PackingGrade, String getPackingState,
			String PackingState,String getStockState,String StockState,String getErpLocation,String ErpLocation,String getErpFactory,String ErpFactory,String getOqaResult,String OqaResult,String getOqaResultState,String OqaResultState,String getMaterialSpecName,String MaterialSpecName,
			String getEventName,String eventName,String getOQAGroupName,String OQAGroupName)throws Exception 
	{
		if(getPackingGrade.equals(PackingGrade)&&
				getPackingState.equals(PackingState)&&getStockState.equals(StockState)&&
				getErpLocation.equals(ErpLocation)&&getErpFactory.equals(ErpFactory)&&getOqaResult.equals(OqaResult)&&
				getOqaResultState.equals(OqaResultState)&&getMaterialSpecName.equals(MaterialSpecName)&&getOQAGroupName.equals(OQAGroupName))
		return true;
		else
			return false;
	}
//		/**查询选择的行项目所有的非拆分的Box信息
//		 * SELECT SITENAME,MATERIALPACKINGNAME,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE,
//STOCKSTATE,ERPLOCATION,ERPFACTORY
//FROM MATERIALPACKING
//WHERE (SITENAME,MATERIALPACKINGNAME) IN(
//    SELECT SITENAME,MATERIALPACKINGNAME --1.递归循环查找所有被拆分的Box
//    FROM BS_MATERIALPACKINGSPLITMERGE 
//    CONNECT BY  MATERIALPACKINGNAME=PRIOR DESTMATERIALPACKINGNAMENAME
//    START WITH (SITENAME,MATERIALPACKINGNAME) IN(
//            SELECT SITENAME,MATERIALPACKINGNAME  
//            FROM BS_MATERIALPACKINGUNDOINFO BS
//            WHERE BS.UNDOID=:UNDOID AND(
//            (LASTEVENTNAME='NormalStockIn' AND
//            BS.RECEIVEREQUESTNAME=:REQUESTNAME
//            AND BS.RECEIVEREQUESTDETAILNAME=:REQUESTDETAILNAME)
//            OR
//            ( BS.SHIPREQUESTNAME=:REQUESTNAME
//            AND BS.SHIPREQUESTDETAILNAME=:REQUESTDETAILNAME))
//            AND LASTEVENTNAME<>'MToM' 
//        )
//    union ------2.余下未被拆分的Box，根据物料凭证号查询出来的数据
//    SELECT SITENAME,MATERIALPACKINGNAME
//    FROM BS_MATERIALPACKINGUNDOINFO BS
//            WHERE BS.UNDOID=:UNDOID AND(
//            (LASTEVENTNAME='NormalStockIn' AND
//            BS.RECEIVEREQUESTNAME=:REQUESTNAME
//            AND BS.RECEIVEREQUESTDETAILNAME=:REQUESTDETAILNAME)
//            OR
//            ( BS.SHIPREQUESTNAME=:REQUESTNAME
//            AND BS.SHIPREQUESTDETAILNAME=:REQUESTDETAILNAME))
//            AND LASTEVENTNAME<>'MToM' 
//    )
//and PACKINGSTATE<>'Emptied'
//
//2、使用替代方法，简写SQL语句
//
//		 */
//		String sql="select undoid,sitename,materialpackingname,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE, "
//				+ "STOCKSTATE,ERPLOCATION,ERPFACTORY "
//				+ "from BS_MATERIALPACKINGUNDOINFO bs where bs.undoid=:unDoID and( "
//				+ "(lasteventname='NormalStockIn' and bs.RECEIVEREQUESTNAME=:RequestName and bs.receiverequestdetailname=:RequestDetailName) "
//				+ "or ( bs.shiprequestname=:RequestName and bs.shiprequestdetailname=:RequestDetailName) )"
//				+ "and lasteventname<>'MToM'";	
//
//		Map<String, String> bindMap1 = new HashMap<String, String>();
//		bindMap1.put("UNDOID", unDoID);
//		bindMap1.put("REQUESTNAME", requestName);
//		bindMap1.put("REQUESTDETAILNAME", requestDetailName); 
//		List<Map<String, Object>> currentPackingList=
//					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap1);
//		
//		sql="";//Group by 所有的状态值，然后获得MATERIALQUANTITY的总数，只有一条数据
//		List<Map<String, Object>> unDoPackingList=
//					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap1);
//		
//		/**
//		 * 比较currentPackingList的数据与unDoPackingList的值是否一致
//		 */
//		
//		return true;
	public boolean checkCancelCondition(String siteName,String unDoID,
			String eventName)throws Exception  {
		try {	
			
			String sql_UndoBoxInfo=" SELECT SITENAME,UNDOID,MATERIALPACKINGNAME,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE,"+
" STOCKSTATE,ERPLOCATION,ERPFACTORY,OQARESULT,OQARESULTSTATE,MATERIALSPECNAME,MATERIALPROCESSGROUPNAME,NPFLAG"+
    " FROM BS_MATERIALPACKINGUNDOINFO BS"+
            " WHERE BS.UNDOID=:UNDOID "+
            " AND UNDOIDSTATE<>'N'"+
            " AND BS.SITENAME=:SITENAME"//
            ;
		String sql_1="SELECT SITENAME,MATERIALPACKINGNAME,PACKINGGRADE,OQARESULT,OQARESULTSTATE,MATERIALQUANTITY,PACKINGSTATE,"+
			   " STOCKSTATE,ERPLOCATION,ERPFACTORY,LASTEVENTNAME,MATERIALSPECNAME,MATERIALPROCESSGROUPNAME"+
			   " FROM MATERIALPACKING "+
			   " WHERE (SITENAME,MATERIALPACKINGNAME) IN( "+
			   " SELECT SITENAME,DESTMATERIALPACKINGNAMENAME AS MATERIALPACKINGNAME "+
				" FROM BS_MATERIALPACKINGSPLITMERGE "+
				" CONNECT BY  MATERIALPACKINGNAME=PRIOR DESTMATERIALPACKINGNAMENAME "+
				" START WITH SITENAME=:sitename and MATERIALPACKINGNAME=:materialPackingName "+
				" union "+
				" SELECT SITENAME,MATERIALPACKINGNAME "+
				" FROM MATERIALPACKING "+
				" WHERE SITENAME=:sitename and MATERIALPACKINGNAME=:materialPackingName ) "+
				" and packingstate<>'Emptied' and MATERIALQUANTITY !=0 " ;
		
		/*String sql_2="update BS_MATERIALPACKINGUNDOINFO set UNDOIDSTATE=:unDoIDState";
		Map<String, Object> bindMap2 = new HashMap<String, Object>();
		bindMap2.put("unDoIDState", "N");*/
		Map<String, Object> bindMap_UndoBoxInfo = new HashMap<String, Object>();
		bindMap_UndoBoxInfo.put("SITENAME", siteName);
		bindMap_UndoBoxInfo.put("UNDOID", unDoID);
		//bindMap_UndoBoxInfo.put("REQUESTDETAILNAME",requestDetailName);
		List<Map<String, Object>> queryResult_UndoBoxInfo = 
				IDMFrameServiceProxy.getSqlTemplate().queryForList(sql_UndoBoxInfo, bindMap_UndoBoxInfo);
		Map<String, Object> bindMap = new HashMap<String, Object>();
			double sumQuantity=0;
		List<MaterialPackingKey> materialPackingKeyList = new ArrayList<MaterialPackingKey>();
		for(int i=0;i<queryResult_UndoBoxInfo.size();i++)
		{
			bindMap.put("sitename", siteName);
			bindMap.put("materialPackingName", queryResult_UndoBoxInfo.get(i).get("MATERIALPACKINGNAME"));
			List<Map<String, Object>> queryResult = 
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql_1, bindMap);
			for(int j=0;j<queryResult.size();j++)
			{
				String PackingGrade;
				String OqaResult;
				String OqaResultState;
				String getPackingGrade=queryResult.get(j).get("PACKINGGRADE")==null?"-":queryResult.get(j).get("PACKINGGRADE").toString();
				String getPackingState=queryResult.get(j).get("PACKINGSTATE")==null?"-":queryResult.get(j).get("PACKINGSTATE").toString();
				String getStockState=queryResult.get(j).get("STOCKSTATE")==null?"-":queryResult.get(j).get("STOCKSTATE").toString();
				String getErpLocation=queryResult.get(j).get("ERPLOCATION").toString();
				String getErpFactory=queryResult.get(j).get("ERPFACTORY").toString();
				String getEventName=queryResult.get(j).get("LASTEVENTNAME").toString();
				String getOqaResult=queryResult.get(j).get("OQARESULT")==null?"-":queryResult.get(j).get("OQARESULT").toString();
				String getOQAGroupName=queryResult.get(j).get("MATERIALPROCESSGROUPNAME")==null?"-":queryResult.get(j).get("MATERIALPROCESSGROUPNAME").toString();			
				String getOqaResultState=queryResult.get(j).get("OQARESULTSTATE")==null?"-":queryResult.get(j).get("OQARESULTSTATE").toString();
				String getMaterialSpecName=queryResult.get(j).get("MATERIALSPECNAME").toString();
				String npFlag=queryResult_UndoBoxInfo.get(i).get("NPFLAG")==null?"N":queryResult_UndoBoxInfo.get(i).get("NPFLAG").toString();
				if(npFlag.equals("Y"))
				{
					PackingGrade="-";
					OqaResult="-";
					OqaResultState="PREOQA";
				}
				else{
					PackingGrade=queryResult_UndoBoxInfo.get(i).get("PACKINGGRADE")==null?"-":queryResult_UndoBoxInfo.get(i).get("PACKINGGRADE").toString();
					OqaResult=queryResult_UndoBoxInfo.get(i).get("OQARESULT")==null?"-":queryResult_UndoBoxInfo.get(i).get("OQARESULT").toString();
					OqaResultState=queryResult_UndoBoxInfo.get(i).get("OQARESULTSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("OQARESULTSTATE").toString();
				}
				String PackingState=queryResult_UndoBoxInfo.get(i).get("PACKINGSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("PACKINGSTATE").toString();
				String StockState=queryResult_UndoBoxInfo.get(i).get("STOCKSTATE")==null?"-":queryResult_UndoBoxInfo.get(i).get("STOCKSTATE").toString();
				String ErpLocation=queryResult_UndoBoxInfo.get(i).get("ERPLOCATION").toString();
				String ErpFactory=queryResult_UndoBoxInfo.get(i).get("ERPFACTORY").toString();
				String OQAGroupName=queryResult_UndoBoxInfo.get(i).get("MATERIALPROCESSGROUPNAME")==null?"-":queryResult_UndoBoxInfo.get(i).get("MATERIALPROCESSGROUPNAME").toString();
				String MaterialSpecName=queryResult_UndoBoxInfo.get(i).get("MATERIALSPECNAME").toString();
				boolean state=checkState(getPackingGrade,PackingGrade,getPackingState,
						PackingState,getStockState,StockState,getErpLocation,ErpLocation,getErpFactory,
						ErpFactory,getOqaResult,OqaResult,getOqaResultState,OqaResultState,getMaterialSpecName,MaterialSpecName,
						getEventName,eventName,getOQAGroupName,OQAGroupName);
				if(state)
				{
					MaterialPackingKey 	materialPackingKey= new MaterialPackingKey();
					materialPackingKey.setMaterialPackingName(queryResult.get(j).get("MATERIALPACKINGNAME").toString());
					materialPackingKey.setSiteName(siteName);
					materialPackingKeyList.add(materialPackingKey);
					/*MaterialPacking materialPacking=new MaterialPacking();
					materialPacking.setKey(materialPackingKey);
					materialPacking.setUnDoID(queryResult_UndoBoxInfo.get(i).get("PREUNDOID").toString());*/
					sumQuantity+=Double.parseDouble(queryResult.get(j).get("MATERIALQUANTITY").toString());
					//materialPackingList.add(materialPacking);
				}
				else throw new CustomException("CHECKFAILED","","");
			}
		}
		return true;

//		if (sumQuantity==Float.parseFloat(materialQuantity))
//		{
//			
//			//IDMFrameServiceProxy.getSqlTemplate().update(sql_2, bindMap2);
//			return true;
//		}
//		else 
//			throw new CustomException("CHECKFAILED","","");
		} catch (Exception ex) {
			 ex.printStackTrace();
			 throw ex;
		}	
	}
	public List<String> getLocationListFromShipRequest(String invoiceNo){
		String sql = "SELECT DISTINCT LOCATIONNAME FROM MATERIALPACKING" +
				" WHERE MATERIALPACKINGNAME IN( SELECT MATERIALPACKINGNAME FROM MATERIALPACKING WHERE SHIPREQUESTNAME=? and MATERIALPACKINGTYPE='Box')" ;
		Object[] bindSet = new Object[] { invoiceNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	public List<String> getLocationListFromActNO(String invoiceNo,String receiveActNO){
		String sql = "SELECT DISTINCT LOCATIONNAME FROM MATERIALPACKING WHERE SHIPREQUESTNAME=? AND RECEIVEACTNO= ?" ;
		Object[] bindSet = new Object[] { invoiceNo,receiveActNO};
		String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		List<String> returnValue = new ArrayList<String>();
		
		for (String[] row : result)
			returnValue.add(row[0].toString());
		
		return returnValue;
			
		}
	public String getMaterialpackingCount(String locationName)
    {
		
		String sql = 	" SELECT COUNT( MATERIALPACKINGNAME) " +
						"  FROM MATERIALPACKING " +
						"   WHERE LOCATIONNAME=? " ;
		
    	Object[] args = {locationName};

    	String result = String.valueOf(IDMFrameServiceProxy.getSqlTemplate().queryForInt(sql, args));
		
    	if ("0".equals(result)){
			return "1";
		} else {
			return "2";
		}
    }
	
	public boolean checkShiprequest(List<Element> materialPackingNameList,String siteName)throws Exception 
	{
		
		for(Element materialPackingName : materialPackingNameList){
			String sql = 	" SELECT SHIPREQUESTNAME " +
					"  FROM MATERIALPACKING " +
					"   WHERE MATERIALPACKINGNAME=? " ;
			Object[] args = {materialPackingName.getText()};
			String result = IDMFrameServiceProxy.getSqlTemplate().queryForObject(sql,String.class, args);
			
		if(result!=null && result.equals("") )
		return true;
		else
			return false;
		}
		return true;
	}

	public boolean checkUnDoCondition(String unDoID,String requestName,String requestDetailName,String eventName)
	{			
		/**查询选择的行项目所有的非拆分的Box信息
		 * SELECT SITENAME,MATERIALPACKINGNAME,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE,
STOCKSTATE,ERPLOCATION,ERPFACTORY
FROM MATERIALPACKING
WHERE (SITENAME,MATERIALPACKINGNAME) IN(
    SELECT SITENAME,MATERIALPACKINGNAME --1.递归循环查找所有被拆分的Box
    FROM BS_MATERIALPACKINGSPLITMERGE 
    CONNECT BY  MATERIALPACKINGNAME=PRIOR DESTMATERIALPACKINGNAMENAME
    START WITH (SITENAME,MATERIALPACKINGNAME) IN(
            SELECT SITENAME,MATERIALPACKINGNAME  
            FROM BS_MATERIALPACKINGUNDOINFO BS
            WHERE BS.UNDOID=:UNDOID AND(
            (LASTEVENTNAME='NormalStockIn' AND
            BS.RECEIVEREQUESTNAME=:REQUESTNAME
            AND BS.RECEIVEREQUESTDETAILNAME=:REQUESTDETAILNAME)
            OR
            ( BS.SHIPREQUESTNAME=:REQUESTNAME
            AND BS.SHIPREQUESTDETAILNAME=:REQUESTDETAILNAME))
            AND LASTEVENTNAME<>'MToM' 
        )
    union ------2.余下未被拆分的Box，根据物料凭证号查询出来的数据
    SELECT SITENAME,MATERIALPACKINGNAME
    FROM BS_MATERIALPACKINGUNDOINFO BS
            WHERE BS.UNDOID=:UNDOID AND(
            (LASTEVENTNAME='NormalStockIn' AND
            BS.RECEIVEREQUESTNAME=:REQUESTNAME
            AND BS.RECEIVEREQUESTDETAILNAME=:REQUESTDETAILNAME)
            OR
            ( BS.SHIPREQUESTNAME=:REQUESTNAME
            AND BS.SHIPREQUESTDETAILNAME=:REQUESTDETAILNAME))
            AND LASTEVENTNAME<>'MToM' 
    )
and PACKINGSTATE<>'Emptied'

2、使用替代方法，简写SQL语句

		 */
		String sql="select undoid,sitename,materialpackingname,PACKINGGRADE,MATERIALQUANTITY,PACKINGSTATE, "
				+ "STOCKSTATE,ERPLOCATION,ERPFACTORY "
				+ "from BS_MATERIALPACKINGUNDOINFO bs where bs.undoid=:unDoID and( "
				+ "(lasteventname='NormalStockIn' and bs.RECEIVEREQUESTNAME=:RequestName and bs.receiverequestdetailname=:RequestDetailName) "
				+ "or ( bs.shiprequestname=:RequestName and bs.shiprequestdetailname=:RequestDetailName) )"
				+ "and lasteventname<>'MToM'";	

		Map<String, String> bindMap = new HashMap<String, String>();
		bindMap.put("UNDOID", unDoID);
		bindMap.put("REQUESTNAME", requestName);
		bindMap.put("REQUESTDETAILNAME", requestDetailName); 
		List<Map<String, Object>> currentPackingList=
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
		
		sql="";//Group by 所有的状态值，然后获得MATERIALQUANTITY的总数，只有一条数据
		List<Map<String, Object>> unDoPackingList=
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
		
		/**
		 * 比较currentPackingList的数据与unDoPackingList的值是否一致
		 */
		
		return true;
	}
	public void MakeStockInRequestReplyComand_Started(Document message)throws Exception 
	{
		String transPortCommandId=message.getRootElement().getChild("Body").getChildText("TRANSPORTCOMMANDID");
		Map<String,Object> userColumns=new HashMap<String, Object>();
		userColumns.put("state", "Started");
		TransportCommandKey tck=new TransportCommandKey();
		tck.setTransportCommandID(transPortCommandId);
		 
		String fromDestinationName=message.getRootElement().getChild("Body").getChildText("FROMDESTINATIONNAME");
		Element STORAGERACKLIST=message.getRootElement().getChild("Body").getChild("STORAGERACKLIST");
		for (Iterator iterator = STORAGERACKLIST.getChildren() .iterator(); iterator.hasNext();) {
			Element STORAGERACK = (Element) iterator.next();
			
			
		}
		
	}
	
	// for GERP
	public List<String> getPackingListFromShipRequestToOEM(String invoiceNo,String receiveActNo){
		String sql = "SELECT MATERIALPACKINGNAME FROM MATERIALPACKING\n" +
				" WHERE  receiverequestname= ? and RECEIVEACTNO = ?  and StockState is null \n" + 
				" ORDER BY MATERIALPACKINGNAME";
		Object[] bindSet = new Object[] { invoiceNo,receiveActNo};
		//Object[] bindSet1 = new Object[] { receiveActNo };
	String[][]	result= IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(sql, bindSet);
		//List result = IDMFrameServiceProxy.getSqlTemplate().getJdbcTemplate().queryForList(sql, bindSet);

	List<String> returnValue = new ArrayList<String>();
	
	for (String[] row : result)
		returnValue.add(row[0].toString());
	
	return returnValue;
		
	}
	
	public void poReturnSEQToOEM(List<String> materialPackingKeyList, String siteName, String receiveActNo,
			String receiveRequestName, EventInfo eventInfo) throws Exception {
		try {

			String sql = "SELECT B.MATERIALSPECNAME, SUM(MATERIALQUANTITY) AS QUANTITY,B.CHARGE,B.ERPLOCATION,B.ERPFACTORY,B.RECEIVEREQUESTNAME,B.RECEIVEREQUESTDETAILNAME,B.PACKINGGRADE "
					+ "FROM MATERIALPACKING B WHERE RECEIVEACTNO = :receiveActNo "
					+ "GROUP BY B.MATERIALSPECNAME,B.CHARGE,B.ERPLOCATION,B.ERPFACTORY,B.RECEIVEREQUESTNAME,B.RECEIVEREQUESTDETAILNAME,B.PACKINGGRADE";
			String sql_box = "SELECT a.sitename,a.materialpackingname ,A.MATERIALSPECNAME, MATERIALQUANTITY,A.CHARGE,A.ERPLOCATION,A.ERPFACTORY,A.RECEIVEREQUESTNAME,A.RECEIVEREQUESTDETAILNAME,A.PACKINGGRADE,A.RECEIVEACTNO "
					+ " from materialpacking a " + " where RECEIVEACTNO = :receiveActNo ";
			Map<String, Object> bindMap1 = new HashMap<String, Object>();
			bindMap1.put("receiveActNo", receiveActNo);
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap1);
			List<Map<String, Object>> sqlResult_box = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql_box,
					bindMap1);
			List<MaterialPackingKey> packingKeyList = new ArrayList<MaterialPackingKey>();
			String CHARGE = "";
			String charge = "";
			for (int i = 0; i < materialPackingKeyList.size(); i++) {
				CHARGE = sqlResult_box.get(i).get("CHARGE") == null ? ""
						: sqlResult_box.get(i).get("CHARGE").toString();

				for (int j = 0; j < sqlResult.size(); j++) {
					charge = sqlResult.get(j).get("CHARGE") == null ? "" : sqlResult.get(j).get("CHARGE").toString();
					if (charge.equals(CHARGE)
							&& sqlResult_box.get(i).get("MATERIALSPECNAME").toString()
									.equals(sqlResult.get(j).get("MATERIALSPECNAME").toString())
							&& sqlResult_box.get(i).get("ERPLOCATION").toString()
									.equals(sqlResult.get(j).get("ERPLOCATION").toString())
							&& sqlResult_box.get(i).get("ERPFACTORY").toString()
									.equals(sqlResult.get(j).get("ERPFACTORY").toString())) {
						MaterialPackingKey keyInfo = new MaterialPackingKey();
						keyInfo.setSiteName(siteName);
						keyInfo.setMaterialPackingName(sqlResult_box.get(i).get("MATERIALPACKINGNAME").toString());
						MaterialPacking dataInfo = MaterialPackingServiceProxy.getMaterialPackingService().selectByKey(keyInfo);
						dataInfo.setUnDoDetailName(Integer.toString(j + 1));
						dataInfo.setPackingState("Completed");
						// 增加UNDOINFO表中移动类型
						dataInfo.setReasonCodeType(eventInfo.getReasonCodeType());
						dataInfo.setReasonCode(eventInfo.getReasonCode());
						// dataInfo.setShipRequestName(receiveRequestName);
						// dataInfo.setStockState("StockOut");
						packingKeyList.add(keyInfo);
						MaterialPackingServiceProxy.getMaterialPackingService().update(dataInfo);
					}
				}

			}
			MaterialReceiveActKey mrk = new MaterialReceiveActKey();
			mrk.setSiteName(siteName);
			mrk.setReceiveactNO(receiveActNo);
			mrk.setReceiveRequestName(receiveRequestName);
			com.cim.idm.wmspackage.materialreceiverequestact.management.info.SetEventInfo setevent = new com.cim.idm.wmspackage.materialreceiverequestact.management.info.SetEventInfo();
			Map<String, Object> bindMap2 = new HashMap<String, Object>();
			bindMap2.put("receiveActState", "Stocked");
			setevent.setUserColumns(bindMap2);
			MaterialReceiveRequestActServiceProxy.getReceiveRequestBatchService().setEvent(mrk, eventInfo, setevent);
			// MaterialPackingServiceProxy.getMaterialPackingService().setEvent(packingKeyList,
			// eventInfo, setEventInfo);
		} catch (IDMFrameDBErrorSignal e) {
			e.printStackTrace();
		}
	}


}
