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

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.exception.IDMFrameDBErrorSignal;
import com.cim.idm.framework.orm.SqlTemplate;
import com.cim.idm.wmspackage.inbounddeliveryorder.data.InnerRequestDetailInfo;
import com.cim.idm.wmspackage.invoice.MaterialReceiveRequestServiceProxy;
import com.cim.idm.wmspackage.invoice.management.data.MaterialReceiveRequest;
import com.cim.idm.wmspackage.invoice.management.data.MaterialReceiveRequestKey;
import com.cim.idm.wmspackage.material.MaterialServiceProxy;
import com.cim.idm.wmspackage.material.management.data.MaterialSpec;
import com.cim.idm.wmspackage.material.management.data.MaterialSpecKey;
import com.cim.idm.wmspackage.materialpacking.MaterialPackingServiceProxy;
import com.cim.idm.wmspackage.materialpacking.management.MaterialPackingService;
import com.cim.idm.wmspackage.materialpacking.management.data.MaterialPacking;
import com.cim.idm.wmspackage.materialpacking.management.data.MaterialPackingKey;
import com.cim.idm.wmspackage.materialshiprequest.MaterialShipRequestServiceProxy;
import com.cim.idm.wmspackage.materialshiprequest.management.data.MaterialShipRequest;
import com.cim.idm.wmspackage.materialundo.info.MaterialUnDoinfo;
import com.cim.idm.wmspackage.receiverequestdetail.ReceiveRequestDetailServiceProxy;
import com.cim.idm.wmspackage.receiverequestdetail.management.data.MaterialReceiveRequestDetail;
import com.cim.idm.wmspackage.receiverequestdetail.management.data.MaterialReceiveRequestDetailKey;

public class MaterialPackingStockInUtil {


	public List<MaterialPacking> GetOQAGroupList(String receiveRequestName,String receiveRequestDetailName,String receiveActNo){
			
			String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? ";
			Object[] bindSet = {receiveRequestName,receiveRequestDetailName,receiveActNo};	
			
			List<MaterialPacking> oqaList=null;
			try {
				oqaList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet, MaterialPacking.class);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			return oqaList;
	}
	
	public List<MaterialPacking> GetBoxListByReceiveActNo(String receiveActNo){
		
		String condition = "WHERE RECEIVEACTNO=? ";
		Object[] bindSet = {receiveActNo};		
		List<MaterialPacking> oqaList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet, null); 
		return oqaList;
	}
	
	public List<MaterialStockInClass> GetOQAMakedateGroupList(String receiveRequestName,String receiveRequestDetailName,String receiveActNo,String siteName) throws ParseException{
		
		String condition = "select sitename,receiverequestname,receiverequestdetailname,receiveactno,to_char(makedate,'yyyy-MM-DD'),sum(materialquantity) as receivequantity from materialpacking";
		String condition1 = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? group by(sitename,receiverequestname,receiverequestdetailname,receiveactno,makedate)";
		Object[] bindSet = new Object[] {receiveRequestName,receiveRequestDetailName,receiveActNo,siteName};		
		//Object[] bindSet = new Object[]{};
		String[][] resultList  = IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(condition+" "+condition1, bindSet);
		List<MaterialStockInClass> oqalist = new ArrayList<MaterialStockInClass>();
		for (String[] row : resultList){
			MaterialStockInClass mg = new MaterialStockInClass();
			mg.setReceiveActNo(row[3]);
			mg.setSiteName(row[0]);
			mg.setReceiveRequestName(row[1]);
			mg.setReceiveRequestDetailName(row[2]);
			String str = row[4];
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-DD");
			Date datestr = df.parse(str);
			mg.setMakedate(row[4]);//wanshan
			mg.setReceiveQuantity(row[5]);
			oqalist.add(mg);			
		}
		
		return oqalist;
	}
public List<InnerRequestDetailInfo> GetOQAMakedateGroupListByInner(String receiveRequestName,String receiveRequestDetailName,String receiveActNo,String siteName) throws ParseException{
		
		String condition = "select sitename,receiverequestname,receiverequestdetailname,receiveactno,to_char(makedate,'yyyy-MM-DD'),sum(materialquantity) as receivequantity from materialpacking";
		String condition1 = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? group by(sitename,receiverequestname,receiverequestdetailname,receiveactno,makedate)";
		Object[] bindSet = new Object[] {receiveRequestName,receiveRequestDetailName,receiveActNo,siteName};		
		//Object[] bindSet = new Object[]{};
		String[][] resultList  = IDMFrameServiceProxy.getSqlTemplate().queryForStringArray(condition+" "+condition1, bindSet);
		List<InnerRequestDetailInfo> oqalist = new ArrayList<InnerRequestDetailInfo>();
		for (String[] row : resultList){
			InnerRequestDetailInfo mg = new InnerRequestDetailInfo();
			mg.setReceiveActNo(row[3]);
			mg.setSiteName(row[0]);
			mg.setReceiveRequestName(row[1]);
			mg.setReceiveRequestDetailName(row[2]);
			String str = row[4];
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-DD");
			Date datestr = df.parse(str);
			mg.setMakeDate(row[4]);//wanshan
			mg.setReceivedQuantity(row[5]);
			oqalist.add(mg);			
		}		
		return oqalist;
	}
	
public List<MaterialPacking> GetOQADetailGroupList(MaterialStockInClass ms){
		
		String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') ORDER BY (MATERIALQUANTITY)";
		Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakedate()};		
		List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
		return resultList;
	}
public List<MaterialPacking> GetOQADetailGroupListByInner(InnerRequestDetailInfo ms){
	
	String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') ORDER BY (MATERIALQUANTITY)";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakeDate()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
public List<MaterialPacking> GetOQADetailGroupList2(MaterialStockInClass ms){
	
	String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') AND MATERIALPROCESSGROUPNAME=?";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakedate(),ms.getPreGroupName()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
public List<MaterialPacking> GetOQADetailGroupListByInner2(InnerRequestDetailInfo ms){
	
	String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') AND MATERIALPROCESSGROUPNAME=?";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakeDate(),ms.getPreGroupName()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
public List<MaterialPacking> GetOQADetailGroupList1(MaterialStockInClass ms){
	
	String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') ";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakedate()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
//20180406 10091872 香港1900平台
public List<MaterialPacking> GetOQADetailGroupList1ForHK(MaterialStockInClass ms){
	
	String condition = "WHERE RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=?  ";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
//20180406 10091872 香港1900平台
public List<MaterialPacking> GetOQADetailGroupList2ForHK(MaterialStockInClass ms){
	
	String condition = "WHERE RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=?  AND MATERIALPROCESSGROUPNAME=?";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getPreGroupName()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
public List<MaterialPacking> GetOQADetailGroupListByInner1(InnerRequestDetailInfo ms){
	
	String condition = "WHERE MATERIALPACKINGTYPE='Box' AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND SITENAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') ";
	Object[] bindSet = new Object[] {ms.getReceiveRequestName(),ms.getReceiveRequestDetailName(),ms.getReceiveActNo(),ms.getSiteName(),ms.getMakeDate()};		
	List<MaterialPacking> resultList  = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet);		
	return resultList;
}
	public boolean GetOQAInfo(String receiveRequestName,String receiveRequestDetailName,String siteName){
			
		MaterialReceiveRequestDetailKey key = new MaterialReceiveRequestDetailKey();
		key.setReceiveRequestName(receiveRequestName);
		key.setReceiveRequestDetailName(receiveRequestDetailName);
		key.setSiteName(siteName);
		MaterialReceiveRequestDetail mrq = ReceiveRequestDetailServiceProxy.getReceiveRequestDetailService().selectByKey(key);
		
		MaterialSpecKey key2 = new MaterialSpecKey();
		key2.setMaterialSpecName(mrq.getMaterialSpecName());
		key2.setSiteName(siteName);
		MaterialSpec ms = MaterialServiceProxy.getMaterialSpecService().selectByKey(key2);
		
		
		//if(ms.get)   获取表中的INSPECTV，INSPECTP，INSPECTA三个字段的值进行判断决定是否为需要之间的产品
		return false;
	}
	
	public List<MaterialPacking> GetBackStockInList(MaterialStockInClass msa){

		String condition2 = "";
		List<MaterialPacking> bsiList=new ArrayList<MaterialPacking>();
		//String condition = "WHERE SITENAME=? AND SHIPREQUESTNAME=? AND SHIPREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
		if(msa.getCharge().isEmpty()){
		 condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
		 Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};	
		 bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}
		else if(!msa.getCharge().isEmpty() &&msa.getActtype().equals("BMDTOEM")){
			//condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			// Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};	
			condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			 Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};	
			 bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}else if(!msa.getCharge().isEmpty() &&msa.getActtype().equals("ERPReserve")){
			//condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			// Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};	
			condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd') ";
			 Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};	
			 bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}else if (!msa.getCharge().isEmpty()&&msa.getActtype().equals("MES"))
		{
			condition2="WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			Object[] bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getMakedate()};
			bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}  	
		else if (!msa.getCharge().isEmpty()&&msa.getActtype().equals("Manual"))
		{
			condition2="WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND CHARGE=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			Object[] bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getCharge(),msa.getMakedate()};
			bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}else{
			condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MATERIALSPECNAME=? AND CHARGE=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
			Object[]  bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMaterialSpecName(),msa.getCharge(),msa.getMakedate()};	
			bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		}	
		return bsiList;
	}
	
	public List<MaterialPacking> GetPOOEMStockInList(MaterialStockInClass msa){

		String condition = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
		Object[] bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveRequestDetailName(),msa.getReceiveActNo(),msa.getMakedate()};		
		List<MaterialPacking> bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet); 
		return bsiList;
	}
	
	public List<MaterialPacking> GetDumpStockInList(String siteName,String ssrn,String ssrdn){

		//String condition1 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=?";
		String condition = "WHERE SITENAME=? AND SHIPREQUESTNAME=? AND SHIPREQUESTDETAILNAME=?";
		Object[] bindSet = {siteName,ssrn,ssrdn};		
		List<MaterialPacking> bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition, bindSet); 
		return bsiList;
	}
	
	public List<MaterialUnDoinfo> GetManualOQAUnDoInfoList(String undoId){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "select * from BS_MATERIALPACKINGUNDOINFO where UNDOID=:unDoID and LASTEVENTNAME='NPManualOQA' order by UNDODETAILNAME";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("unDoID", undoId);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			undo.setOqaResult(undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOIDDATE").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}
	
	public List<MaterialUnDoinfo> GetNPOQAUnDoInfoList(String undoId){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "select * from BS_MATERIALPACKINGUNDOINFO where UNDOID=:unDoID and LASTEVENTNAME='NPOQA' order by UNDODETAILNAME";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("unDoID", undoId);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			undo.setOqaResult(undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOIDDATE").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undo.setMaterialProcessGroupName(undolist.get(i).get("MATERIALPROCESSGROUPNAME")==null?"":undolist.get(i).get("MATERIALPROCESSGROUPNAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}

	public List<MaterialUnDoinfo> GetNoPOStockInUnDoInfoList(String undoId){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = " SELECT * FROM (select SITENAME,UNDOID,UNDODETAILNAME,  MATERIALSPECNAME,RECEIVEREQUESTNAME,"
				+ "RECEIVEREQUESTDETAILNAME,RECEIVEACTNO, MATERIALPROCESSGROUPNAME,ERPLOCATION, ERPFACTORY,"
				+ "MAKEDATE,CHARGE,SUM(MATERIALQUANTITY) AS MATERIALQUANTITY from BS_MATERIALPACKINGUNDOINFO"
				+ " GROUP BY (SITENAME,UNDOID,UNDODETAILNAME,MATERIALSPECNAME,MAKEDATE,CHARGE,RECEIVEREQUESTNAME,"
				+ "RECEIVEREQUESTDETAILNAME,RECEIVEACTNO, MATERIALPROCESSGROUPNAME,ERPLOCATION, ERPFACTORY) "
				+ "ORDER BY UNDOID,UNDODETAILNAME ) "
				+ "LEFT JOIN "
				+ "(select SITENAME,RECEIVEREQUESTNAME,RECEIVEREQUESTDETAILNAME,ERPPONAME,ERPPODETAILNAME "
				+ "FROM MATERIALRECEIVEREQUESTDETAIL) USING(SITENAME,RECEIVEREQUESTNAME,RECEIVEREQUESTDETAILNAME) "
				+ "WHERE UNDOID=:UNDOID";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("UNDOID", undoId);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			//undo.setOqaResult(undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOID").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			//undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undo.setMakeDate(Timestamp.valueOf(undolist.get(i).get("MAKEDATE").toString()));
			undo.setReceiveRequestName(undolist.get(i).get("ERPPONAME").toString());
			undo.setReceiveRequestDetailName(undolist.get(i).get("ERPPODETAILNAME").toString());
			undo.setMaterialProcessGroupName(undolist.get(i).get("MATERIALPROCESSGROUPNAME")==null?"":undolist.get(i).get("MATERIALPROCESSGROUPNAME").toString());
			undoList.add(undo);
			
		}
		return undoList;
	}
	
	public List<MaterialUnDoinfo> GetNoPOStockOutUnDoInfoList(String undoId){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = " SELECT * FROM (select SITENAME,UNDOID,UNDODETAILNAME,  MATERIALSPECNAME,RECEIVEREQUESTNAME,"
				+ "RECEIVEREQUESTDETAILNAME,RECEIVEACTNO, MATERIALPROCESSGROUPNAME,ERPLOCATION, ERPFACTORY,"
				+ "MAKEDATE,CHARGE,SUM(MATERIALQUANTITY) AS MATERIALQUANTITY from BS_MATERIALPACKINGUNDOINFO"
				+ " GROUP BY (SITENAME,UNDOID,UNDODETAILNAME,MATERIALSPECNAME,MAKEDATE,CHARGE,RECEIVEREQUESTNAME,"
				+ "RECEIVEREQUESTDETAILNAME,RECEIVEACTNO, MATERIALPROCESSGROUPNAME,ERPLOCATION, ERPFACTORY) "
				+ "ORDER BY UNDOID,UNDODETAILNAME ) "
				+ "LEFT JOIN "
				+ "(select SITENAME,RECEIVEREQUESTNAME,RECEIVEREQUESTDETAILNAME,ERPPONAME,ERPPODETAILNAME "
				+ "FROM MATERIALRECEIVEREQUESTDETAIL) USING(SITENAME,RECEIVEREQUESTNAME,RECEIVEREQUESTDETAILNAME) "
				+ "WHERE UNDOID=:UNDOID";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("UNDOID", undoId);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			//undo.setOqaResult(undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOID").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			//undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undo.setMakeDate(Timestamp.valueOf(undolist.get(i).get("MAKEDATE").toString()));
			undo.setReceiveRequestName(undolist.get(i).get("RECEIVEREQUESTNAME").toString());
			undo.setReceiveRequestDetailName(undolist.get(i).get("RECEIVEREQUESTDETAILNAME").toString());
			undo.setMaterialProcessGroupName(undolist.get(i).get("MATERIALPROCESSGROUPNAME")==null?"":undolist.get(i).get("MATERIALPROCESSGROUPNAME").toString());
			undoList.add(undo);
			
		}
		return undoList;
	}
	

	public List<MaterialUnDoinfo> GetNoPOUnDoInfoList(String undoId,String undodetail){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "select * from BS_MATERIALPACKINGUNDOINFO where UNDOID=:unDoID and UNDODETAILNAME=:unDoIDdetail order by UNDODETAILNAME";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("unDoID", undoId);
		 bindMap.put("unDoIDdetail", undodetail);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			undo.setOqaResult(undolist.get(i).get("OQARESULT")==null?"":undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOIDDATE").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}
	
	
	public List<MaterialUnDoinfo> GetNoPOUnDoBoxList(String undoId,String undodetail){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "SELECT SITENAME,MATERIALPACKINGNAME,RECEIVEACTNO "+
				"FROM MATERIALPACKING "+
				"WHERE (SITENAME,MATERIALPACKINGNAME) IN( "+
				"SELECT SITENAME,DESTMATERIALPACKINGNAMENAME AS MATERIALPACKINGNAME  "+
				"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 "+
				" BS.UNDODETAILNAME=:undodetailname "+
				" AND LASTEVENTNAME<>'MToM' )"+
				" union "+
				"SELECT SITENAME,MATERIALPACKINGNAME "+
				"FROM BS_MATERIALPACKINGUNDOINFO BS "+
				"WHERE BS.UNDOID=:UNDOID AND "+
				"BS.UNDODETAILNAME=:undodetailname "+
				"AND LASTEVENTNAME<>'MToM' "+
				")"+
				"and PACKINGSTATE<>'Emptied'";Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("UNDOID", undoId);
		 bindMap.put("undodetailname", undodetail);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}
	
	public List<MaterialUnDoinfo> GetNoPOOQABoxList(String undoId){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "SELECT SITENAME,MATERIALPACKINGNAME,RECEIVEACTNO "+
				"FROM MATERIALPACKING "+
				"WHERE (SITENAME,MATERIALPACKINGNAME) IN( "+
				"SELECT SITENAME,DESTMATERIALPACKINGNAMENAME AS MATERIALPACKINGNAME  "+
				"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<>'MToM' )"+
				" union "+
				"SELECT SITENAME,MATERIALPACKINGNAME "+
				"FROM BS_MATERIALPACKINGUNDOINFO BS "+
				"WHERE BS.UNDOID=:UNDOID AND "+
				"LASTEVENTNAME<>'MToM' "+
				")"+
				"and PACKINGSTATE<>'Emptied'";Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("UNDOID", undoId);
		// bindMap.put("unDoIDdetail", undodetail);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}
	
	public String GetUnDoInfoList(String undoId){
		String receiveactno="";
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "select distinct RECEIVEACTNO from BS_MATERIALPACKINGUNDOINFO where UNDOID=:unDoID";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("unDoID", undoId); 
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		if(undolist.size()>0){

			receiveactno = undolist.get(0).get("RECEIVEACTNO")==null?"":undolist.get(0).get("RECEIVEACTNO").toString();
		}
		
		return receiveactno;
	}
	
	
	public List<MaterialUnDoinfo> GetNoPOUnDoListByPOname(String oldPOname,String sitename){
		List<MaterialUnDoinfo> undoList = new ArrayList<MaterialUnDoinfo>();
		String condition = "select * from BS_MATERIALPACKINGUNDOINFO where SITENAME=:SITENAME and RECEIVEREQUESTNAME=:RECEIVEREQUESTNAME AND UNDOIDSTATE='Created' AND UNDOID LIKE 'UNDO-%'";
		Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("SITENAME", sitename);
		 bindMap.put("RECEIVEREQUESTNAME", oldPOname);
		List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		for(int i=0;i<undolist.size();i++){
			MaterialUnDoinfo undo = new MaterialUnDoinfo();
			undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
			undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
			undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
			undo.setOqaResult(undolist.get(i).get("OQARESULT")==null?"":undolist.get(i).get("OQARESULT").toString());
			undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
			undo.setUnDoID(undolist.get(i).get("UNDOIDDATE").toString());
			undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
			undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
			undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			undo.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
			undo.setSiteName(undolist.get(i).get("SITENAME").toString());
			undo.setReceiveRequestName(undolist.get(i).get("RECEIVEREQUESTNAME").toString());
			undo.setReceiveRequestDetailName(undolist.get(i).get("RECEIVEREQUESTDETAILNAME").toString());
			undoList.add(undo);
		}
		return undoList;
	}
	
	
	public List<MaterialPacking> GetNPBoxList(String receiveRequestName,String sitename)throws Exception{
		List<MaterialPacking> oqaList = new ArrayList<MaterialPacking>();
		 String condition = "SELECT DISTINCT MATERIALPACKINGNAME,SITENAME,RECEIVEREQUESTNAME,RECEIVEREQUESTDETAILNAME FROM BS_MATERIALPACKINGUNDOINFO WHERE RECEIVEREQUESTNAME=:RECEIVEREQUESTNAME AND SITENAME=:SITENAME AND NPFLAG='Y'";
		 Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("SITENAME", sitename);
		 bindMap.put("RECEIVEREQUESTNAME", receiveRequestName);
		 List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		 for(int i=0;i<undolist.size();i++){
			 MaterialPacking undo = new MaterialPacking();
			 MaterialPackingKey key = new MaterialPackingKey();
//				undo.setMaterialSpecName(undolist.get(i).get("MATERIALSPECNAME").toString());
//				undo.setErpFactory(undolist.get(i).get("ERPFACTORY").toString());
//				undo.setErpLocation(undolist.get(i).get("ERPLOCATION").toString());
//				undo.setOqaResult(undolist.get(i).get("OQARESULT")==null?"":undolist.get(i).get("OQARESULT").toString());
//				undo.setUnDoDetailName(undolist.get(i).get("UNDODETAILNAME").toString());
//				undo.setUnDoID(undolist.get(i).get("UNDOIDDATE").toString());
//				undo.setCharge(undolist.get(i).get("CHARGE")==null?"":undolist.get(i).get("CHARGE").toString());
//				undo.setMaterialQuantity(Double.parseDouble(undolist.get(i).get("MATERIALQUANTITY").toString()));
//				undo.setReceiveActNo(undolist.get(i).get("RECEIVEACTNO").toString());
			 	key.setMaterialPackingName(undolist.get(i).get("MATERIALPACKINGNAME").toString());
				key.setSiteName(undolist.get(i).get("SITENAME").toString());
				undo.setKey(key);
				if(undolist.get(i).get("RECEIVEREQUESTNAME")==null ||undolist.get(i).get("RECEIVEREQUESTDETAILNAME")==null){
					
					throw new Exception("无PO BOX没有相关PO信息！");
				}
				undo.setReceiveRequestName(undolist.get(i).get("RECEIVEREQUESTNAME").toString());
				undo.setReceiveRequestDetailName(undolist.get(i).get("RECEIVEREQUESTDETAILNAME").toString());
				oqaList.add(undo);
			}
		return oqaList;
	}
	
	public List<String> GetNewUndoIDByOldUndoID(String sitename,String undoid){
		List<String> undoID = new ArrayList<String>();
		
		String condition = "SELECT DISTINCT UNDOID FROM MATERIALPACKING WHERE (SITENAME,MATERIALPACKINGNAME) IN "
				+ "(SELECT SITENAME,MATERIALPACKINGNAME FROM BS_MATERIALPACKINGUNDOINFO WHERE UNDOID=:UNDOID AND NPFLAG='Y')";
		 Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("SITENAME", sitename);
		 bindMap.put("UNDOID", undoid);
		 List<Map<String,Object>> undolist = IDMFrameServiceProxy.getSqlTemplate().queryForList(condition, bindMap);
		 for(int i=0;i<undolist.size();i++){
			 	String undoid1= undolist.get(i).get("UNDOID")==null?"":undolist.get(i).get("UNDOID").toString();
				
			 	undoID.add(undoid1);
				
			}
		
		return undoID;
		
	}
	
	public List<MaterialPacking> GetPCBAStockInList(MaterialStockInClass msa){

		//String condition1 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=? AND RECEIVEREQUESTDETAILNAME=? AND RECEIVEACTNO=?";
		//String condition = "WHERE SITENAME=? AND SHIPREQUESTNAME=? AND SHIPREQUESTDETAILNAME=? AND RECEIVEACTNO=? AND MAKEDATE=TO_DATE(?,'yyyy-MM-dd')";
		String condition2 = "WHERE SITENAME=? AND RECEIVEREQUESTNAME=?  AND RECEIVEACTNO=? AND MATERIALSPECNAME=? ";
		Object[] bindSet = {msa.getSiteName(),msa.getReceiveRequestName(),msa.getReceiveActNo(),msa.getMaterialSpecName()};		
		List<MaterialPacking> bsiList = MaterialPackingServiceProxy.getMaterialPackingService().select(condition2, bindSet); 
		return bsiList;
	}
	
}
