
package com.cim.idm.mwmsextend.generic.util;

import java.net.InetAddress;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.cim.idm.wmsextend.generic.util.ConvertUtil;
import com.cim.idm.wmsextend.generic.util.SMessageUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.Document;
import org.jdom.Element;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

import com.cim.idm.wmsextend.generic.errorHandler.CustomException;
import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.util.xml.JdomUtils;
import com.cim.idm.wmspackage.name.NameServiceProxy;
import com.cim.idm.wmspackage.productpacking.ProductPackingServiceProxy;
import com.cim.idm.wmspackage.productpacking.management.data.ProductPacking;

public class CommonUtil implements ApplicationContextAware
{
	private static Log log = LogFactory.getLog(CommonUtil.class);
	private ApplicationContext	applicationContext;

	/*
	* Name : setApplicationContext
	* Desc : This function is setApplicationContext
	* Author : ZXY IDM Team
	* Date : 2011.02.11
	*/
	public void setApplicationContext( ApplicationContext arg0 ) throws BeansException
	{
		// TODO Auto-generated method stub
		applicationContext = arg0;
	}
	

	/*
	* Name : getXmlDataValue
	* Desc : This function is getXmlDataValue
	* Author : ZXY IDM Team
	* Date : 2011.02.15
	*/
	public static String getXmlDataValue(Document doc, String category, String item)	
	{
		String dataValue = "";
		try
		{
			dataValue = doc.getDocument().getRootElement().getChild(category).getChildText(item);
		}
		catch(Exception e)
		{
			log.info("Cannot Find Item Name: " + item);
		}
		return dataValue;
	}
	
	/*
	* Name : getIp
	* Desc : This function is getIp
	* Author : ZXY IDM Team
	* Date : 2011.01.15
	*/
	public static String getIp()
	{
		String ip = "";
		try 
		{
			ip = InetAddress.getLocalHost().getHostAddress();
		} catch (Exception e) {}
		return ip;
	}
	
	/*
	* Name : getValue
	* Desc : This function is getValue
	* Author : ZXY IDM Team
	* Date : 2011.01.15
	*/
	public static String getValue(Document doc, String itemName)	
	{
		String value = "";
		Element root = doc.getDocument().getRootElement();
		try
		{
			value  = root.getChild("Body").getChild(itemName).getText();
		}
		catch(Exception e1)
		{
			log.info("Cannot Find Item Name: " + itemName);
		}
		return value;
	}
	
	/*
	* Name : getReturnMessage
	* Desc : This function is getReturnMessage
	* Author : ZXY IDM Team
	* Date : 2011.03.05
	*/
	public static String getReturnMessage(Document doc)
	{
		String returnMessage = "";
		Element root = doc.getDocument().getRootElement();
		try
		{
			returnMessage = root.getChild("Return").getChildText("RETURNMESSAGE");
		} catch(Exception e){}
		return returnMessage;
	}
	
	public static String getBodyReturnMessage(Document doc)
	{
		String returnMessage = "";
		Element root = doc.getDocument().getRootElement();
		try
		{
			returnMessage = root.getChild("Body").getChildText("RETURNMESSAGE");
		} catch(Exception e){}
		return returnMessage;
	}
	
	/*
	* Name : insertErrorMessageLog
	* Desc : This function is insertErrorMessageLog
	* Author : ZXY IDM Team
	* Date : 2011.03.05
	*/
	public static void insertErrorMessageLog(org.jdom.Document doc)
	{
		try{
			// Set Variable
			String enumName = "ErrorMessageLog";
			String enumValues = "WriteFlag";
			String fullMessage = JdomUtils.toString(doc);
			
			String eventTimeKey = ConvertUtil.getCurrTimeKey();
			String eventName = CommonUtil.getXmlDataValue(doc,"Header","MESSAGENAME");
			String eventUser = CommonUtil.getXmlDataValue(doc,"Header","EVENTUSER");
			String originalSourceSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "ORIGINALSOURCESUBJECTNAME");
			String sourceSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "SOURCESUBJECTNAME");
			String targetSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "TARGETSUBJECTNAME");
			String transactionId     = CommonUtil.getXmlDataValue(doc, "Header", "TRANSACTIONID");
			String ip = CommonUtil.getIp();
//			String factoryName = CommonUtil.getXmlDataValue(doc,"Header","SHOPNAME");
//			String machineName = CommonUtil.getValue(doc, "MACHINENAME");
//			String unitName = CommonUtil.getValue(doc, "UNITNAME");
//			String subUnitName = CommonUtil.getValue(doc, "SUBUNITNAME");
//			String lotName = CommonUtil.getValue(doc, "LOTNAME");
//			String durableName = CommonUtil.getValue(doc, "CARRIERNAME");
//			String processOperationName = CommonUtil.getValue(doc, "PROCESSOPERATIONNAME");
//			String productName = CommonUtil.getValue(doc, "PRODUCTNAME");
			String errorMessage = CommonUtil.getReturnMessage(doc);

			if(StringUtils.equals(eventName, "UserLogin"))
			{
				log.info("Do not Insert BS_ErrorMessageLog for EventName: " + eventName + ", ErrorMSG: " + errorMessage);
			}
			else
			{
				// Get DEFAULTFLAG
				String sql = "SELECT DEFAULTFLAG FROM ENUMDEFVALUE WHERE ENUMNAME = :enumName AND ENUMVALUE = :enumValues ";
				
				Map<String, String> bindMap = new HashMap<String, String>();
				bindMap.put("enumName", enumName);
				bindMap.put("enumValues", enumValues);
				
				List<Map<String, Object>> queryResult = 
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
				
				String logFlag = queryResult.get(0).get("DEFAULTFLAG").toString();
				
				// If logFlag = 'Y' insert To MessageLog
				if(StringUtils.equals(logFlag, "Y"))
				{
					sql = "INSERT INTO BS_ERRORMESSAGELOG " +
						  "   (EVENTTIMEKEY, EVENTNAME, EVENTUSER, TRANSACTIONID, IP,  " +
						  "   ERRORMESSAGE, MESSAGE,  ORIGINALSOURCESUBJECTNAME, SOURCESUBJECTNAME, TARGETSUBJECTNAME) " +
						  " VALUES" +
						  "   (:eventTimeKey, :eventName, :eventUser,  :transactionId, :ip,  " +
						  "   :errorMessage, :message, :originalSourceSubjectName, :sourceSubjectName, :targetSubjectName)";
					
					bindMap.clear();
					bindMap.put("eventTimeKey", eventTimeKey);
					bindMap.put("eventName", eventName);
					bindMap.put("eventUser", eventUser);
					bindMap.put("transactionId", transactionId);
					bindMap.put("ip", ip);
//					bindMap.put("factoryName", factoryName);
//					bindMap.put("machineName", machineName);
//					bindMap.put("unitName", unitName);
//					bindMap.put("subUnitName", subUnitName);
//					bindMap.put("lotName", lotName);
//					bindMap.put("durableName", durableName);
//					bindMap.put("processOperationName", processOperationName);
//					bindMap.put("productName", productName);
					bindMap.put("errorMessage", errorMessage);
					bindMap.put("message", fullMessage);
					bindMap.put("originalSourceSubjectName", originalSourceSubjectName);
					bindMap.put("sourceSubjectName", sourceSubjectName);
					bindMap.put("targetSubjectName", targetSubjectName);
					
					IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
				}
			}
		}catch(Exception e) {
			// Pass this Action if any error occurs
		}
	}	
	
	public static void insertWMSErrorMessageLog(org.jdom.Document doc)
	{
		try{
			// Set Variable
			String enumName = "ErrorMessageLog";
			String enumValues = "WriteFlag";
			String fullMessage = JdomUtils.toString(doc);
			
			String eventTimeKey = ConvertUtil.getCurrTimeKey();
			String eventName = CommonUtil.getXmlDataValue(doc,"Header","MESSAGENAME");
			String eventUser = CommonUtil.getXmlDataValue(doc,"Header","EVENTUSER");
			String originalSourceSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "ORIGINALSOURCESUBJECTNAME");
			String sourceSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "SOURCESUBJECTNAME");
			String targetSubjectName = CommonUtil.getXmlDataValue(doc, "Header", "TARGETSUBJECTNAME");
			String transactionId     = CommonUtil.getXmlDataValue(doc, "Header", "TRANSACTIONID");
			String ip = CommonUtil.getIp();
			String errorMessage = CommonUtil.getReturnMessage(doc);

			{
				// Get DEFAULTFLAG
				String sql = "SELECT DEFAULTFLAG FROM ENUMDEFVALUE WHERE ENUMNAME = :enumName AND ENUMVALUE = :enumValues ";
				
				Map<String, String> bindMap = new HashMap<String, String>();
				bindMap.put("enumName", enumName);
				bindMap.put("enumValues", enumValues);
				
				List<Map<String, Object>> queryResult = 
					IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
				
				String logFlag = queryResult.get(0).get("DEFAULTFLAG").toString();
				
				// If logFlag = 'Y' insert To MessageLog
				if(StringUtils.equals(logFlag, "Y"))
				{
					sql = "INSERT INTO BS_ERRORMESSAGELOG " +
						  "   (EVENTTIMEKEY, EVENTNAME, EVENTUSER, TRANSACTIONID, IP,  " +
						  "   ERRORMESSAGE, MESSAGE,  ORIGINALSOURCESUBJECTNAME, SOURCESUBJECTNAME, TARGETSUBJECTNAME) " +
						  " VALUES" +
						  "   (:eventTimeKey, :eventName, :eventUser,  :transactionId, :ip,  " +
						  "   :errorMessage, :message, :originalSourceSubjectName, :sourceSubjectName, :targetSubjectName)";
					
					bindMap.clear();
					bindMap.put("eventTimeKey", eventTimeKey);
					bindMap.put("eventName", eventName);
					bindMap.put("eventUser", eventUser);
					bindMap.put("transactionId", transactionId);
					bindMap.put("ip", ip);
					bindMap.put("errorMessage", errorMessage);
					bindMap.put("message", fullMessage);
					bindMap.put("originalSourceSubjectName", originalSourceSubjectName);
					bindMap.put("sourceSubjectName", sourceSubjectName);
					bindMap.put("targetSubjectName", targetSubjectName);
					
					IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
				}
			}
		}catch(Exception e) {
			// Pass this Action if any error occurs
		}
	}	
	
	/*
	* Name : setEventComment
	* Desc : This function is setEventComment
	* Author : ZXY IDM Team
	* Date : 2011.03.28
	*/
	public static String setEventComment(Document doc)
	{
		Element root = doc.getDocument().getRootElement();
		String messageName  = root.getChild("Header").getChild("MESSAGENAME").getText();
		String eventComment = root.getChild("Header").getChild("EVENTCOMMENT").getText();
		if(eventComment == null || eventComment.isEmpty() )
		{
			eventComment = messageName;
		}
		return eventComment;
	}
	
	/*
	* Name : getProductSpecByProductSpec
	* Desc : This function is getProductSpecByProductSpec
	* Author : ZXY IDM Team
	* Date : 2011.08.19
	*/
	/*public static ProductSpec getProductSpecByProductSpec (String productSpecName) throws CustomException{
		try{
			ProductSpecKey productSpecKey = new ProductSpecKey();
			productSpecKey.setSiteName(com.cim.idm.mwmsextend.generic.GenericServiceProxy.getConstantMap().SiteName_B8);
			productSpecKey.setProductSpecName(productSpecName);
			
			ProductSpec productSpecData = null;
			productSpecData = ProductServiceProxy.getProductSpecService().selectByKey(productSpecKey);
			
			return productSpecData;
		}catch(Exception e){
			throw new CustomException("PRODUCTSPEC-9000", productSpecName);
		}
	}
	*/
	/*
	* Name : getBatchArgsBySqlResult
	* Desc : This function is getBatchArgsBySqlResult
	* Author : ZXY IDM Team
	* Date : 2012.01.19
	*/
	public static List<Object[]> getBatchArgsBySqlResult( List<Map<String, Object>> sqlResult, String item )
	{
		String value = "";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		
		if(sqlResult.size() > 0) {
			for(int i=0; sqlResult.size() > i; i++) {
				value = sqlResult.get(i).get(item).toString();
				batchArgs.add(new Object[] {value});
			}
		}
		
		return batchArgs;
	}

	/*
	* Name : makeListForQuery
	* Desc : This function is makeListForQuery
	* Author : ZXY IDM Team
	* Date : 2011.03.07
	*/
	public static String makeListForQuery(Element element, String listName, String Name)
	{
		String list= "'";

		Element ListElement = element.getChild(listName);
		if( ListElement != null ){
			for( Iterator iterator = ListElement.getChildren().iterator(); iterator.hasNext(); ){
				Element product = (Element) iterator.next();
				String productName = product.getChild(Name).getText();
				list =  list + productName + "', '";
			}
			list = list + "'";
		}
		
		return list;
	}

	/*
	* Name : makeListForQueryByList
	* Desc : This function is makeListForQueryByList
	* Author : ZXY IDM Team
	* Date : 2011.03.07
	*/
	public static String makeListForQueryByList(List<String> lists)
	{
		String list= "'";
		String value = "";

		if( lists.size() > 0){
			for(int i=0; lists.size() > i; i++){
				value = lists.get(i);
				list =  list + value + "', '";
			}
			list = list + "'";
		}
		
		return list;
	}
	
	/*
	* Name : getEnumDefValueStringByEnumName
	* Desc : This function is getEnumDefValueStringByEnumName
	* Author : ZXY IDM Team
	* Date : 2011.05.20
	*/
	public static String getEnumDefValueStringByEnumName( String enumName ){
		String enumValue = "";
		String sql = "SELECT ENUMVALUE FROM ENUMDEFVALUE WHERE ENUMNAME = :enumName ";
		
		Map<String, String> bindMap = new HashMap<String, String>();
		bindMap.put("enumName", enumName);
		
		List<Map<String, Object>> sqlResult = 
			IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
		
		if(sqlResult.size() > 0){
			enumValue = sqlResult.get(0).get("ENUMVALUE").toString();
		}
		
		return enumValue;
	}

	/*
	* Name : makeList
	* Desc : This function is makeList
	* Author : ZXY IDM Team
	* Date : 2011.03.21
	*/
	public static List<String> makeList(Element element, String listName, String name)
	{
		// Caution. 
		// This list size should not exceed 1000 when it uses for SQL BindSet without SQL Exception.
		String childText     = "";
		List<String> list    = new ArrayList<String>();
		Element childElement = null;
		Element listElement  = element.getChild(listName);
		
		if( listElement != null ){
			for( Iterator iterator = listElement.getChildren().iterator(); iterator.hasNext(); ){
				childElement = (Element) iterator.next();
				childText = childElement.getChild(name).getText();
				list.add(childText);
			}
		}
		
		return list;
	}

	/*
	* Name : getProcessGroupHistoryColumns
	* Desc : This function is getProcessGroupHistoryColumns
	* Author : ZXY IDM Team
	* Date : 2011.03.21
	*/
	public static String getProcessGroupHistoryColumns()
	{
		String objAttSql = "";
		String field 	 = "";

		Map<String, String> bindMap 			= new HashMap<String, String>();
		List<Map<String, Object>> objAttResult 	= new ArrayList<Map<String, Object>>();
		
		
		// Get the Same Column Names by ProcessGroup & ProcessGroupHistory 
		objAttSql = "SELECT UPPER(PH.ATTRIBUTENAME) AS ATTRIBUTENAME FROM "
				  + " (SELECT ATTRIBUTENAME, POSITION FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'ProcessGroup' ORDER BY POSITION) P, "
				  + " (SELECT ATTRIBUTENAME, POSITION FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'ProcessGroupHistory' ORDER BY POSITION) PH "
				  + " WHERE P.ATTRIBUTENAME = PH.ATTRIBUTENAME ";
		objAttResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(objAttSql, bindMap);
		
		for(int i=0; i<objAttResult.size(); i++){
			field += objAttResult.get(i).get("ATTRIBUTENAME").toString() + ",";
		}

		
		// Get the Different ProcessGroupHistory Column Names from ProcessGroup 
		objAttSql = "SELECT   UPPER (ATTRIBUTENAME) AS ATTRIBUTENAME FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'ProcessGroupHistory' "
				  + "MINUS "
				  + "SELECT   UPPER (ATTRIBUTENAME) AS ATTRIBUTENAME FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'ProcessGroup' ";
		objAttResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(objAttSql, bindMap);
		
		for(int i=0; i<objAttResult.size(); i++){
			field += objAttResult.get(i).get("ATTRIBUTENAME").toString() + ",";
		}

		try{
			if( field.length() > 1){
				field = field.substring(0, field.length() - 1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return field;
	}
	
	/*
	* Name : getLotHistoryColumns
	* Desc : This function is getLotHistoryColumns
	* Author : ZXY IDM Team
	* Date : 2011.03.21
	*/
	public static String getLotHistoryColumns()
	{
		String objAttSql = "";
		String field 	 = "";

		Map<String, String> bindMap 			= new HashMap<String, String>();
		List<Map<String, Object>> objAttResult 	= new ArrayList<Map<String, Object>>();
		
		
		// Get the Same Column Names by Lot & LotHistory 
		objAttSql = "SELECT UPPER(LH.ATTRIBUTENAME) AS ATTRIBUTENAME FROM "
				  + " (SELECT ATTRIBUTENAME, POSITION FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'Lot' ORDER BY POSITION) L, "
				  + " (SELECT ATTRIBUTENAME, POSITION FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'LotHistory' ORDER BY POSITION) LH "
				  + " WHERE L.ATTRIBUTENAME = LH.ATTRIBUTENAME ";
		objAttResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(objAttSql, bindMap);
		
		for(int i=0; i<objAttResult.size(); i++){
			field += objAttResult.get(i).get("ATTRIBUTENAME").toString() + ",";
		}

		
		// Get the Different LotHistory Column Names from Lot 
		objAttSql = "SELECT   UPPER (ATTRIBUTENAME) AS ATTRIBUTENAME FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'LotHistory' "
				  + "MINUS "
				  + "SELECT   UPPER (ATTRIBUTENAME) AS ATTRIBUTENAME FROM OBJECTATTRIBUTEDEF WHERE TYPENAME = 'Lot' ";
		objAttResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(objAttSql, bindMap);
		
		for(int i=0; i<objAttResult.size(); i++){
			field += objAttResult.get(i).get("ATTRIBUTENAME").toString() + ",";
		}

		try{
			if( field.length() > 1){
				field = field.substring(0, field.length() - 1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return field;
	}
	
	/*
	 *   Name : insertProcessGroupHistoryByPalletList
	 *   Desc : This function is insertProcessGroupHistoryByPalletList
	 * Author : ZXY IDM Team Date : 2011.03.22
	 */
	public static void insertProcessGroupHistoryByPalletList(List<String> palletList)
	{
		String name					= "";
		String field 	 			= "";
		String insertSql			= "";
		Object[] args				= null;
		List<Object[]> batchArgs	= new ArrayList<Object[]>();
		
		// Set the Insert Statement & Bind List
		{
			// Set Bind List
			for(int i=0; palletList.size() > i; i++){
				name = palletList.get(i);
				args = new Object[]{name, name, name};
				batchArgs.add(args);
			}
			
			// Get Column Names of ProcessGroupHistory 
			field = getProcessGroupHistoryColumns();
			
			// Insert into ProcessGroupHistory for Pallet
			insertSql = "INSERT INTO PROCESSGROUPHISTORY ( " + field + ") " +
						"   (SELECT " + field  +
						"      FROM (SELECT P.*, LASTEVENTNAME AS EVENTNAME, LASTEVENTCOMMENT AS EVENTCOMMENT, " +
						"                   LASTEVENTFLAG AS EVENTFLAG, " +
						"					LASTEVENTTIME AS EVENTTIME, " +
						"                   LASTEVENTTIMEKEY AS TIMEKEY, " +
						"                   LASTEVENTUSER AS EVENTUSER " +
						"              FROM PROCESSGROUP P " +
						"             WHERE PROCESSGROUPNAME = :name) A, " +
						"           (SELECT 'A' AS CANCELFLAG, " +
						"                   NULL AS CANCELTIMEKEY, " +
						"                   MATERIALQUANTITY AS OLDMATERIALQUANTITY, " +
						"					SYSDATE AS SYSTEMTIME, " +
						"					PROCESSGROUPNAME AS PROCESSGROUPNAMEH " +
						"              FROM PROCESSGROUPHISTORY " +
						"             WHERE TIMEKEY = (SELECT MAX (TIMEKEY) FROM PROCESSGROUPHISTORY WHERE PROCESSGROUPNAME = :name) " +
						"				AND PROCESSGROUPNAME = :name) B " +
						"     WHERE A.PROCESSGROUPNAME = B.PROCESSGROUPNAMEH) ";
			try{
				if(batchArgs.size() > 0){
					IDMFrameServiceProxy.getSqlTemplate().updateBatch(insertSql, batchArgs);
				}
			}catch(Exception e){
				e.printStackTrace();
			}

			// Insert into ProcessGroupHistory for Box
			insertSql = "INSERT INTO PROCESSGROUPHISTORY ( " + field + ") " +
						"   (SELECT " + field  +
						"      FROM (SELECT P.*, LASTEVENTNAME AS EVENTNAME, LASTEVENTCOMMENT AS EVENTCOMMENT, " +
						"                   LASTEVENTFLAG AS EVENTFLAG, " +
						"					LASTEVENTTIME AS EVENTTIME, " +
						"                   LASTEVENTTIMEKEY AS TIMEKEY, " +
						"                   LASTEVENTUSER AS EVENTUSER " +
						"              FROM PROCESSGROUP P " +
						"             WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME = :name)) A, " +
						"           (SELECT 'A' AS CANCELFLAG, " +
						"                   NULL AS CANCELTIMEKEY, " +
						"                   MATERIALQUANTITY AS OLDMATERIALQUANTITY, " +
						"					SYSDATE AS SYSTEMTIME, " +
						"					PROCESSGROUPNAME AS PROCESSGROUPNAMEH " +
						"              FROM PROCESSGROUPHISTORY " +
						"             WHERE TIMEKEY = (SELECT MAX (TIMEKEY) FROM PROCESSGROUPHISTORY " +
						"								WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP " +
						"															WHERE SUPERPROCESSGROUPNAME = :name)) " +
						"				AND PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME = :name)) B " +
						"     WHERE A.PROCESSGROUPNAME = B.PROCESSGROUPNAMEH) ";
			try{
				if(batchArgs.size() > 0){
					IDMFrameServiceProxy.getSqlTemplate().updateBatch(insertSql, batchArgs);
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	
	/*
	 *   Name : insertLotHistoryByPalletList
	 *   Desc : This function is insertLotHistoryByPalletList
	 * Author : ZXY IDM Team Date : 2011.03.22
	 */
	public static void insertLotHistoryByPalletList(List<String> cellLotList)
	{
		String name					= "";
		String field 	 			= "";
		String insertSql			= "";
		Object[] args				= null;
		List<Object[]> batchArgs	= new ArrayList<Object[]>();
		
		// Set the Insert Statement & Bind List
		{
			// Set Bind List
			for(int i=0; cellLotList.size() > i; i++){
				name = cellLotList.get(i);
				args = new Object[]{name, name, name};
				batchArgs.add(args);
			}
			
			// Get Column Names of LotHistory 
			field = getLotHistoryColumns();
			
			// Insert into LotHistory by Lot
			insertSql = "INSERT INTO LOTHISTORY ( " + field + ") " +
						"   (SELECT " + field  +
						"      FROM (SELECT L.*, LASTEVENTNAME AS EVENTNAME, LASTEVENTCOMMENT AS EVENTCOMMENT, " +
						"                   LASTEVENTFLAG AS EVENTFLAG, " +
						"					LASTEVENTTIME AS EVENTTIME, " +
						"                   LASTEVENTTIMEKEY AS TIMEKEY, " +
						"                   LASTEVENTUSER AS EVENTUSER " +
						"              FROM LOT L " +
						"             WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP WHERE SUPERPROCESSGROUPNAME = :name)) A, " +
						"           (SELECT PRODUCTIONTYPE AS OLDPRODUCTIONTYPE, " +
						"                   PRODUCTSPECNAME AS OLDPRODUCTSPECNAME, " +
						"                   PRODUCTSPECVERSION AS OLDPRODUCTSPECVERSION, " +
						"                   PRODUCTSPEC2NAME AS OLDPRODUCTSPEC2NAME, " +
						"                   PRODUCTSPEC2VERSION AS OLDPRODUCTSPEC2VERSION, " +
						"                   PRODUCTTYPE AS OLDPRODUCTTYPE, " +
						"                   SUBPRODUCTTYPE AS OLDSUBPRODUCTTYPE, " +
						"                   PRODUCTQUANTITY AS OLDPRODUCTQUANTITY, " +
						"                   SUBPRODUCTQUANTITY AS OLDSUBPRODUCTQUANTITY, " +
						"                   SUBPRODUCTQUANTITY1 AS OLDSUBPRODUCTQUANTITY1, " +
						"                   SUBPRODUCTQUANTITY2 AS OLDSUBPRODUCTQUANTITY2, " +
						"                   FACTORYNAME AS OLDFACTORYNAME, " +
						"                   DESTINATIONFACTORYNAME AS OLDDESTINATIONFACTORYNAME, " +
						"                   AREANAME AS OLDAREANAME, " +
						"                   PROCESSFLOWNAME AS OLDPROCESSFLOWNAME, " +
						"                   PROCESSFLOWVERSION AS OLDPROCESSFLOWVERSION, " +
						"                   PROCESSOPERATIONNAME AS OLDPROCESSOPERATIONNAME, " +
						"                   PROCESSOPERATIONVERSION AS OLDPROCESSOPERATIONVERSION, " +
						"                   'A' AS CANCELFLAG, SYSDATE AS SYSTEMTIME, " +
						"                   NULL AS CONSUMERLOTNAME, NULL AS CONSUMERTIMEKEY, " +
						"                   NULL AS CONSUMEDLOTNAME, NULL AS CONSUMEDDURABLENAME, " +
						"                   NULL AS CONSUMEDCONSUMABLENAME, NULL AS CANCELTIMEKEY, " +
						"					LOTNAME AS LOTNAMEH " +
						"              FROM LOTHISTORY " +
						"             WHERE TIMEKEY = (SELECT MAX (TIMEKEY) FROM LOTHISTORY " +
						"								WHERE LOTNAME IN (SELECT LOTNAME FROM LOT " +
						"												   WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP " +
						"																			   WHERE SUPERPROCESSGROUPNAME = :name))) " +
						"				AND LOTNAME IN (SELECT LOTNAME FROM LOT " +
						" 								 WHERE PROCESSGROUPNAME IN (SELECT PROCESSGROUPNAME FROM PROCESSGROUP " +
						"															 WHERE SUPERPROCESSGROUPNAME = :name))) B " +
						"     WHERE A.LOTNAME = B.LOTNAMEH) ";
		}
		
		try{
			if(batchArgs.size() > 0){
				IDMFrameServiceProxy.getSqlTemplate().updateBatch(insertSql, batchArgs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/*
	* Name : getTimeKey
	* Desc : This function is getTimeKey from DB
	* Author : ZXY IDM Team
	* Date : 2011.03.21
	*/
	public static String getTimeKey()
	{
		String timeKey = "";
		String sql = "SELECT TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6') AS TIMEKEY FROM DUAL ";
		Map<String, String> bindMap = new HashMap<String, String>();
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, bindMap);
		if(sqlResult.size() > 0) {
			timeKey = sqlResult.get(0).get("TIMEKEY").toString();
		}
		return timeKey;
	}
	
	/*
	 * Name : insertMessageLog
	 * Desc : This function is insertMessageLog
	 * Author : hanb
	 * Date : 2016.01.29
	 */
	public static void insertMessageLog(Document doc, String serverName)
	{
		try{
			Element root = doc.getDocument().getRootElement();
			
			// Set Variable
			String enumName = "MessageLog";
			String enumValues = "WriteFlag";
			String fullMessage = JdomUtils.toString(doc);
			
			String eventName = root.getChild(SMessageUtil.Header_Tag).getChildText("MESSAGENAME");
			//String eventUser = root.getChild(SMessageUtil.Header_Tag).getChildText("EVENTUSER");
			String eventUser = "ERP";

			//String timeKey = root.getChild(SMessageUtil.Header_Tag).getChildText("TRANSACTIONID");
			String timeKey = root.getChild("Header").getChildText("TRANSACTIONID");
			if(!(timeKey != null && !"".equals(timeKey))){
				timeKey = ConvertUtil.getCurrTimeKey();
			}
			
			if (StringUtils.equals(eventName, "RequestTransportJobReply"))
			{
				timeKey = ConvertUtil.getCurrTimeKey();
			}
			
			if (StringUtils.equals(eventName, "UserLogin") || StringUtils.equals(eventName, "UserLogout"))
			{
				log.info("Do not Insert BS_ERPMESSAGELOG for EventName: " + eventName);
			}
			else{
				// Get DEFAULTFLAG
				String sql = "SELECT DEFAULTFLAG FROM ENUMDEFVALUE "
						+ "WHERE ENUMNAME = :enumName AND ENUMVALUE = :enumValues ";

				Map<String, String> bindMap = new HashMap<String, String>();
				bindMap.put("enumName", enumName);
				bindMap.put("enumValues", enumValues);

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

				String logFlag = queryResult.get(0).get("DEFAULTFLAG").toString();

				// If logFlag = 'Y' insert To MessageLog
				if(StringUtils.equals(logFlag, "Y"))
				{
					try{
						sql = "INSERT INTO BS_ERPMESSAGELOG(SERVERNAME,EVENTTIMEKEY,EVENTNAME,EVENTUSER,MESSAGELOG,IP)  "
								+ "VALUES (:serverName, :eventTimeKey, "
								+ ":eventName, :eventUser, "
								+ ":messageLog, :ip)";
						//+ "sys.XMLType.createXML(:messageLog))";

						bindMap.clear();

						bindMap.put("serverName", serverName);
						bindMap.put("eventTimeKey", timeKey);
						bindMap.put("eventName", eventName);
						bindMap.put("eventUser", eventUser);
						bindMap.put("messageLog", fullMessage);
						bindMap.put("ip", CommonUtil.getIp());

						IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
					}catch(Exception e){
						sql = "INSERT INTO BS_ERPMESSAGELOG(SERVERNAME,EVENTTIMEKEY,EVENTNAME,EVENTUSER,MESSAGELOG)  "
								+ "VALUES(:serverName, :eventTimeKey, "
								+ ":eventName, :eventUser, "
								+ ":messageLog)";
						//+ "sys.XMLType.createXML(:messageLog))";

						bindMap.clear();

						bindMap.put("serverName", serverName);
						bindMap.put("eventTimeKey", timeKey);
						bindMap.put("eventName", eventName);
						bindMap.put("eventUser", eventUser);
						bindMap.put("messageLog", fullMessage);

						IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
					}
				}
			}
		}catch(Exception e) {
			// Pass this Action if any error occurs
		}
	}

	public static void setReturnDoc(String nodeValue,String nodeName,Document doc)
	{
		SMessageUtil.setBodyItemValue(doc, nodeName, nodeValue);
	}	
	/*
	* Name : getPackingGroupInfoByInoviceNo
	* Desc : This function is getProcessGroupData By InoviceNo
	* Author : BOE CIM 
	* Date : 2015.01.12
	*/
	public static List<ProductPacking> getPackingGroupInfoByInoviceNo ( String invoiceNo,String siteName) throws CustomException{
		
		String condition = "shiprequestname = ? AND sitename=? ";
		Object[] bindSet = new Object[]{invoiceNo,siteName};
		List<ProductPacking> packingGroupdataList = ProductPackingServiceProxy.getProductPackingService().select(condition, bindSet);
		return packingGroupdataList;
		
	}
}