/********************************************************
	Title     : GenWOTrackStatusPrc(M14GSUN005)
	Date      : 22-09-2014
	Developer : mahendra Jadhav
	Purpose   : Create process window for work order track status to retrive data from work order and activity 
	            master.
	
********************************************************/

package ibase.webitm.ejb.sys;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import org.apache.axis.client.Service;
import org.apache.axis.client.Call;
import org.apache.axis.encoding.XMLType;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.rpc.ParameterMode;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.GenerateSaleOrderPrc;
import ibase.webitm.ejb.fin.FinCommon;

@Stateless
public class GenWOTrackStatusPrc extends ProcessEJB implements GenWOTrackStatusPrcLocal,GenWOTrackStatusPrcRemote 
{
 
	static
	{
		System.out.println("-- GenerateworkOrderTrackPrc called -- ");
		
	}
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{			
			System.out.println("Exception :SoHoldRefPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	
	} //end of confirm method
	public String process(Document dom, Document dom2, String windowName,String xtraParams) throws RemoteException, ITMException 
	{
		String errString="";
		String sql = "";
		String siteCode = "";
		String  tranId = "",conf="";
	    String workOrder ="", activitySet="",eventNo="";		
		
		int woCnt=0,activityCnt=0;
		
		Connection conn = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		
		
		try
		{
			System.out.println("process  starts!!!!!!!");
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			
			
			
			
			workOrder=genericUtility.getColumnValue("work_order", dom);
			eventNo=checkNull(genericUtility.getColumnValue("event_no", dom));
			activitySet=genericUtility.getColumnValue("activity_set", dom);
			System.out.println("workOrder!!! :"+workOrder);
			System.out.println("eventNo :"+eventNo);
			System.out.println("activitySet :"+activitySet);
			
			if(workOrder == null)
			{
				errString = itmDBAccessEJB.getErrorString("","VMEMTWO    ","","",conn);
				return errString;
			}
			else
			{
				
				sql = "select count(*) from workorder where work_order = ?";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,workOrder);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					woCnt = rs.getInt(1);
				} 
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				if(woCnt == 0)
				{
					System.out.println("work order not exist in work order master!!");
					errString = itmDBAccessEJB.getErrorString("","VMINVWO    ","","",conn);
					return errString;
				}
				
			  }
			
			
			if(activitySet == null)
			{
				errString = itmDBAccessEJB.getErrorString("","VMEMTACTST    ","","",conn);
				return errString;
			}
			else
			{
				
				sql = "select count(*) from tran_track_set where track_set = ?";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,activitySet);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					activityCnt = rs.getInt(1);
				} 
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				System.out.println("activityCnt :"+activityCnt);
				if(activityCnt == 0)
				{
					System.out.println("activity set not exist in activity(tran_track_set) master!!");
					errString = itmDBAccessEJB.getErrorString("","VMINVACTST    ","","",conn);
					return errString;
				}
				
			  }
			
			
			
			
			errString=insertWorderTrack(workOrder,eventNo,activitySet,xtraParams);
			errString = errString.toLowerCase();               
			System.out.println("err String from GenWOTrackStatusPrc ="+errString);
			if((errString != null ) && (errString.indexOf("success") > -1))
			{
				System.out.println("--process successfully--");
				errString = itmDBAccessEJB.getErrorString("","VTPRCSUCC","","",conn);
				return errString;
			}
			else 
			{
				conn.rollback();
				System.out.println("--process failed---");
				errString = itmDBAccessEJB.getErrorString("","VTPRCNTSUC ","","",conn);
				return errString;
			}
			
			
		}
		catch(Exception e)
		{
			try 
			{
				conn.rollback();
			} 
			catch (SQLException ex) 
			{
				Logger.getLogger(GenerateSaleOrderPrc.class.getName()).log(Level.SEVERE, null, ex);
			}
			e.printStackTrace();
		}
		finally
		{		
				try
				{
					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;					
					}		
					if( conn != null && ! conn.isClosed() )
					{
						conn.close();
						conn = null;
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			}
				
		return errString;
	}
	
	
	
	
	
	public String insertWorderTrack(String workOrder ,String eventNo,String activitySet,String xtraParams) throws RemoteException,ITMException
	{
		
		System.out.println( " Scheduler activity starts ......");
		String retString = "",sql = "",sqlInsert = "";
		String actCode = "",roleCode = "",trackBasis = "", trackSet = ""  ;
		String refSer = "" ,trackReq = "" ,trackLevel = "",tranId = "",prvWorkorder = "";
	    String xmlString="",userId="",termId="",siteCode="",itemDescr="";
		String lotNo="",itemCode="";
		Date dueDate=null; 
		int expCompDays = 0 ,lineNo = 0;
		int seqNo = 0;
		java.sql.Timestamp currDate = null;
		java.util.Date planDate = null ;
		Connection conn = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		PreparedStatement pstmt = null,pstmt1 = null;
		PreparedStatement pstmtInst = null;
		ResultSet rs = null,rs1 = null;
		ArrayList  trackList = new ArrayList();
		StringBuffer trackSql = new StringBuffer("");
		boolean isError=false;
		StringBuffer xmlBuff = null;
		
		try
		{
			Date currentDate = new Date();
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			//Date date = sdf.parse(currDate.toString());
			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String currDateStr = sdfAppl.format(currDate);
			java.sql.Timestamp sysdate =java.sql.Timestamp.valueOf(sdf.format(new Date()).toString() + " 00:00:00.0");
			//tranId = generateTranId( "w_tran_trk_status", siteCode, currDateStr, conn );
			System.out.println("tranId =="+tranId);
			
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			System.out.println("--login code--"+userId);
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); 
			System.out.println("--term id--"+termId);
			refSer="W-ORD";
			
		
			sql="select wo.work_order,wo.item_code ,wo.site_code,wo.order_type,wo.route_code ,wo.DUE_DATE,wo.LOT_NO,act_code,seq_no,EXP_COMP_DAYS, " +
					" track_basis_value as BASIS , track_req,role_code,track_level from (select tsd.act_code,tsd.seq_no,tsd.EXP_COMP_DAYS,reqd.track_req, " +
					" reqd.track_basis_value,act.role_code,act.track_level from tran_track_set ts , tran_track_set_det tsd ,obj_track_req_det reqd , " +
					" tran_track_acts act where tsd.track_set = ts.track_set and reqd.track_set = tsd.track_set and " +
					" tsd.act_code = act.act_code and tsd.track_set=? and  reqd.ref_ser= 'W-ORD' ) ,workorder wo " +
					" where wo.work_order  = ? ";
			
			
			System.out.println("sql  @@@@@ :"+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, activitySet);
			pstmt.setString(2, workOrder);
			
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				lineNo++;
				
				//refSer = rs.getString("REF_SER");
				System.out.println("lineNo : "+lineNo);
				lotNo=rs.getString("LOT_NO");
				System.out.println("lotNo : "+lotNo);
				dueDate=rs.getDate("DUE_DATE");
				System.out.println("dueDate : "+dueDate);
				itemCode=rs.getString("item_code");
				System.out.println("itemCode : "+itemCode);
				seqNo = rs.getInt("SEQ_NO");
				System.out.println("seqNo : "+seqNo);
				actCode = rs.getString("ACT_CODE");
				System.out.println("actCode : "+actCode);
				trackReq = rs.getString("TRACK_REQ");
				System.out.println("trackReq : "+trackReq);
				trackBasis = rs.getString("BASIS");
				System.out.println("trackBasis : "+trackBasis);
				roleCode = rs.getString("role_code");
				System.out.println("roleCode : "+roleCode);
				trackLevel = rs.getString("track_level");
				System.out.println("trackLevel : "+trackLevel);
				expCompDays=rs.getInt("EXP_COMP_DAYS");
				System.out.println("expCompDays :"+expCompDays);
				planDate = getDates(new Date(),expCompDays);
				System.out.println("planDate  :: "+planDate);
				
				System.out.println("dueDate formated ::: "+sdfAppl.format(dueDate));
				System.out.println("planDate formated ::: "+sdfAppl.format(planDate));
				
				System.out.println("@@@@@@");
				sql = "select descr from item where item_code=?";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, itemCode);
				rs1 = pstmt1.executeQuery();
				if (rs1.next()) 
				{
					itemDescr=rs1.getString("descr");
					
				}
				rs1.close();
				rs1 = null;
				pstmt1.close();
				pstmt1 = null;
				System.out.println("itemDescr :"+itemDescr);
				
				
				xmlBuff = new StringBuffer();
			    xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
				xmlBuff.append("<DocumentRoot>");
				xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
				xmlBuff.append("<group0>");
				xmlBuff.append("<description>").append("Group0 description").append("</description>");
				xmlBuff.append("<Header0>");
				xmlBuff.append("<objName><![CDATA[").append("wo_track_status").append("]]></objName>");  
				xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
				xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
				xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
				xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
				xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
				xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
				xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
				xmlBuff.append("<taxKeyValue><![CDATA[").append("").append("]]></taxKeyValue>");
				xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
				xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
				xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
				xmlBuff.append("<description>").append("Header0 members").append("</description>");	
				xmlBuff.append("<Detail1 dbID=\"\" domID=\"1\" objName=\"sorder\" objContext=\"1\">");  
				xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
				xmlBuff.append("<tran_id/>");
				xmlBuff.append("<line_no><![CDATA["+lineNo+"]]></line_no>");
				xmlBuff.append("<ref_ser><![CDATA["+refSer+"]]></ref_ser>");
				xmlBuff.append("<ref_id><![CDATA["+workOrder+"]]></ref_id>");
				xmlBuff.append("<ref_line_no><![CDATA[1]]></ref_line_no>");
				xmlBuff.append("<track_req><![CDATA["+trackReq+"]]></track_req>");
				xmlBuff.append("<context_value><![CDATA["+trackBasis+"]]></context_value>");
				xmlBuff.append("<track_set><![CDATA["+trackSet+"]]></track_set >");
				xmlBuff.append("<context_1><![CDATA["+itemCode+"-"+itemDescr+"]]></context_1>");
				xmlBuff.append("<context_2><![CDATA["+lotNo+"]]></context_2>");
				xmlBuff.append("<context_3><![CDATA["+sdfAppl.format(dueDate)+"]]></context_3>");
				xmlBuff.append("<context_4><![CDATA[]]></context_4>");
				xmlBuff.append("<context_5><![CDATA[]]></context_5>");
				xmlBuff.append("<seq_no><![CDATA["+seqNo+"]]></seq_no>");
				xmlBuff.append("<act_code><![CDATA["+actCode+"]]></act_code>");
				xmlBuff.append("<role_code><![CDATA["+roleCode+"]]></role_code>");
				xmlBuff.append("<track_level><![CDATA["+trackLevel+"]]></track_level>");
				xmlBuff.append("<days_required><![CDATA["+expCompDays+"]]></days_required>");
				xmlBuff.append("<plan_end_date><![CDATA["+sdfAppl.format(planDate)+"]]></plan_end_date>");
				xmlBuff.append("<event_no><![CDATA["+eventNo+"]]></event_no>");
				xmlBuff.append("<remarks><![CDATA[]]></remarks>");
				xmlBuff.append("<chg_user><![CDATA["+userId+"]]></chg_user>");
				xmlBuff.append("<chg_date><![CDATA["+sdfAppl.format(currentDate)+"]]></chg_date>");
				xmlBuff.append("<chg_term><![CDATA["+termId+"]]></chg_term>");
				xmlBuff.append("</Detail1>");
				xmlBuff.append("</Header0>");
				xmlBuff.append("</group0>");
				xmlBuff.append("</DocumentRoot>");
				xmlString = xmlBuff.toString();
				System.out.println("@@@@@2: xmlString:"+xmlBuff.toString());
				
				
				
				System.out.println("......just before savdata()");
				
				siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
				System.out.println("== site code =="+siteCode);
				retString = saveData(siteCode,xmlString,conn);
			   	System.out.println("retString!!!!!!! :: "+retString);	
			    
			    
			    
			    
			}//end of while code
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			System.out.println("retString @@@@@ :"+retString);
			if (retString.indexOf("Success") > -1)
			{
					System.out.println("@@@@@@3: retString from wo_track_status !:"+retString);
					conn.commit();
					return retString;  
			}
			
			System.out.println("sql insert "+pstmtInst.executeBatch().length);

			}
		catch(SQLException se)
		{
			isError = true;
			System.out.println("SQLException : WorderTrackSchedule : " +se.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception se1){}
			throw new ITMException(se);
		}
		catch(Exception e)
		{
			isError = true;
			System.out.println("Exception :WorderTrackSchedule :"+e.getMessage());
			try
			{
				conn.rollback();
			}
			catch(Exception e1){}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				
				if ( conn != null && !conn.isClosed() )
				{
					conn.close();
					conn = null;
				}
			}catch(Exception se){}
		}
		System.out.println("retString........:: " + retString);
		return retString;  	
	}
	
	
	
	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	private String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
	{
		System.out.println("saving data...........");
		InitialContext ctx = null;
		String retString = null;
		MasterStatefulLocal masterStateful = null; // for ejb3
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			ctx = new InitialContext(appConnect.getProperty());
			masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
			System.out.println("-----------masterStateful------- " + masterStateful);
			String [] authencate = new String[2];
			authencate[0] = "";
			authencate[1] = "";
			System.out.println("xmlString to masterstateful [" + xmlString + "]");
			retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
			System.out.println("--retString - -"+retString);
		}
		catch(ITMException itme)
		{
			System.out.println("ITMException :CreateDistOrder :saveData :==>");
			throw itme;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :CreateDistOrder :saveData :==>");
			throw new ITMException(e);
		}
		return retString;
	}
	public String confirmSaleOrder(String businessObj, String tranIdFr,String xtraParams, Connection conn) throws ITMException
	{
		String methodName = "";
		String compName = "";
		String retString = "";
		String serviceCode = "";
		String serviceURI = "";
		String actionURI = "";
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println("confirmSaleOrder(String businessObj, String tranIdFr,String xtraParams, String forcedFlag, Connection conn) called >>><!@#>");

		try
		{
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");

			methodName = "gbf_post";
			actionURI = "http://NvoServiceurl.org/" + methodName;

			sql = "SELECT SERVICE_CODE,COMP_NAME FROM SYSTEM_EVENTS WHERE OBJ_NAME = ? AND EVENT_CODE = 'pre_confirm' ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,businessObj);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceCode = rs.getString("SERVICE_CODE");
				compName = rs.getString("COMP_NAME");
			}
			System.out.println("serviceCode = "+serviceCode+" compName "+compName);
			sql = "SELECT SERVICE_URI FROM SYSTEM_EVENT_SERVICES WHERE SERVICE_CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,serviceCode);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				serviceURI = rs.getString("SERVICE_URI");
			}
			System.out.println("serviceURI = "+serviceURI+" compName = "+compName);
			Service service = new Service();
			Call call = (Call)service.createCall();
			call.setTargetEndpointAddress(new java.net.URL(serviceURI));
			call.setOperationName( new javax.xml.namespace.QName("http://NvoServiceurl.org", methodName ) );
			call.setUseSOAPAction(true);
			call.setSOAPActionURI(actionURI);
			Object[] aobj = new Object[4];

			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "component_name"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "tran_id"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "xtra_params"), XMLType.XSD_STRING, ParameterMode.IN);
			call.addParameter( new javax.xml.namespace.QName("http://NvoServiceurl.org", "forced_flag"), XMLType.XSD_STRING, ParameterMode.IN);

			aobj[0] = new String(compName);
			aobj[1] = new String(tranIdFr);
			aobj[2] = new String(xtraParams);
			//aobj[3] = new String(forcedFlag);
			//System.out.println("@@@@@@@@@@loginEmpCode:" +genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode")+":");
			System.out.println("@@@@@@ call.setReturnType(XMLType.XSD_STRING) executed........");
			call.setReturnType(XMLType.XSD_STRING);
			retString = (String)call.invoke(aobj);
			System.out.println("Confirm Complete @@@@@@@@@@@ Return string from NVO is:==>["+retString+"]");

		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{		
			try{
				
			
				if (pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				/*if( conn != null ){
					conn.close();
					conn = null;
				}*/
			}
			catch(Exception e)
			{
				System.out.println("Exception inCalling confirmed");
				e.printStackTrace();
				try{
					conn.rollback();

				}catch (Exception s)
				{
					System.out.println("Unable to rollback");
					s.printStackTrace();
				}
				throw new ITMException(e);
			}
		}
		return retString;
	}
	
	
	
	private String getContextValue( String Workorder,String contextCol, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String contextValue = "";
		try
		{
			if(contextCol == null || contextCol.trim().length() == 0)
			{
				return "";
			}
			else
			{
				sql = " select "+contextCol+" from workorder where work_order = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, Workorder );
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					contextValue = rs.getString(1);
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				
			}
		
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +sql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return contextValue;
	}
	
	
	
	
	public int getAcitivityLeadTime( String trackSet, String actCode, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		int leadTime = 0;

		try
		{
			selSql = "  SELECT EXP_COMP_DAYS FROM TRAN_TRACK_SET_DET WHERE TRACK_SET = ? AND ACT_CODE = ? ";
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, trackSet );
			pstmt.setString( 2, actCode );
			rs = pstmt.executeQuery();
			
			if (rs.next())
			{
				leadTime = rs.getInt("EXP_COMP_DAYS");
			}
			
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			System.out.println("getAcitivityLeadTime().leadTime value :"+leadTime);
		
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				
			}
		}
		return leadTime;
	}//getAcitivityLeadTime()
	
	public static java.util.Date getDates( java.util.Date dt, int addDay)
	{
		 Calendar cal = Calendar.getInstance();
	        cal.setTime(dt);
	        cal.add(Calendar.DATE, addDay);
	        dt = cal.getTime();
	        
	        return dt;
	}
	
	public Document parseString(String xmlString) throws RemoteException,ITMException
	{
		Document dom1 = null;
		try
		{
			//Changed by Monif on 2/23/2009 [For special char parsing issue].Start
			ibase.webitm.utility.GenericUtility genericUtility = new ibase.webitm.utility.GenericUtility();
			xmlString = genericUtility.setXmlDec( xmlString );			
			//Changed by Monif on 2/23/2009 [For special char parsing issue].End
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			dbf.setNamespaceAware(true);
			dbf.setIgnoringComments(true);
			DocumentBuilder db = dbf.newDocumentBuilder();
			OutputStreamWriter errorWriter =   new OutputStreamWriter(System.err, CommonConstants.ENCODING); //$NON-NLS-1$
			//Changed by Monif on 6/3/2008 [TO support Character encoding].
			//ByteArrayInputStream baos = new ByteArrayInputStream(xmlString.getBytes());
			ByteArrayInputStream baos = new ByteArrayInputStream(xmlString.getBytes( CommonConstants.ENCODING ));
		
			dom1 = db.parse(baos);
		}
		catch(Exception e)
		{
			dom1 = null;
			System.out.println("Exception : [ValidatorEJB][parseString] :==>\n"+e.getMessage()); //$NON-NLS-1$
			e.printStackTrace();
			throw new ITMException(e);
		}
		return dom1;
	}
	

	
	
	
	
	
	
	
	
	
	
	
}