package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class WorkOrderRouteAlt extends ValidatorEJB implements WorkOrderRouteAltLocal,WorkOrderRouteAltRemote
{
	public static WorkOrderRouteAlt getInstance(){
		return new WorkOrderRouteAlt();
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------ wfvalData method called-----------------");
		System.out.println("xtraParams --->>>  [["+xtraParams+"  ]]");		
		System.out.println("editFlag --->>>  [["+editFlag+"  ]]");
		
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : WorkOrderRouteAlt.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	public String wfValData(Document dom, Document dom1, Document dom2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = null;
		Connection conn = null;
		PreparedStatement pstmt;
		ResultSet rs=null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();		
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		ArrayList<String> condParam=new ArrayList();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String userId="",sql="",errCode="",errString="",errorType="";
		int cnt=0;
		Timestamp loaddate=null,loadedupto=null;
		boolean isRecordFound=false;
		try
		{
			conn = connDriver.getConnectDB("DriverITM");			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId +"Edit Flag -->>: "+editFlag);
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			switch(currentFormNo)
			{

			case 1 :
			parentNodeList = dom.getElementsByTagName("Detail1");
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			childNodeListLength = childNodeList.getLength();
			
			for(int ctr = 0; ctr < childNodeListLength; ctr++)
			{
				childNode = childNodeList.item(ctr);
				childNodeName = childNode.getNodeName();
				System.out.println("Child name --->> "+childNodeName);				
				
				if (childNodeName.equalsIgnoreCase("mc_code")){
					String mcCode="",mcCodeO="",workctrO="",workctr="",workorder="",operation="",mcCodeOld="";
					int count=0;
					
					mcCode =  checkNull(genericUtility.getColumnValue("mc_code",dom));
					mcCodeOld = checkNull(genericUtility.getColumnValue("mc_code__o",dom));					
					workctr = checkNull(genericUtility.getColumnValue("work_ctr",dom));
					workctrO = checkNull(genericUtility.getColumnValue("work_ctr__o",dom));					
					workorder = checkNull(genericUtility.getColumnValue("work_order",dom));
					operation = checkNull(genericUtility.getColumnValue("operation",dom));
					
					//--------------------						
					
					
					System.out.println("mc_code wfval----->>["+mcCode+"]");
					System.out.println("mcCodeOld wfval----->>["+mcCodeOld+"]");
					if(mcCode.length() == 0 && mcCodeOld.length() ==0){
						 errCode = "VTMCNN"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
					}else{
						sql="select count(*) from machines where mc_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, mcCode); 					
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						if(count == 0){
					     errCode = "VTMCCNE"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}
					//--------------------
					
					System.out.println("work_ctr---->>["+workctr+"] mc_code---->>["+mcCode+"]");
					//check machine is in  work center or not
					if(workctr.length() > 0 && mcCode.length() > 0){
					//cnt=getDBRowCount(conn, "workcntr",condParam);
					sql="select count(*) from workcntr where work_ctr = ? and mc_code = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, workctr);
					pstmt.setString(2, mcCode);
					rs=pstmt.executeQuery();
					if(rs.next()){
						cnt=rs.getInt(1);
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					System.out.println("return cnt from getDBRowCount---->>["+cnt+"]");
					
					if(cnt==0){
						errCode = "VTMCHN";//machine is not in workcenter
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
					}
					}
				
					cnt=0;
					condParam.clear();
					condParam.add(0, "operation");
					condParam.add(1, "work_order");  
					condParam.add(2, operation);					
					condParam.add(3, workorder);					
					condParam.add(4, "mc_code");
					condParam.add(5, workctrO);					
					cnt=getDBRowCount(conn, "worder_route",condParam);
					System.out.println("return cnt after getDBRowCount111--->>["+cnt+"]");
					condParam.clear();
					if(cnt > 0){
						sql="select min(a.load_date) , max(a.load_date) from machine_load a,machine_load_det b "
								+ "where a.site_code = b.site_code and a.load_date 	= b.load_date "
								+ "and a.work_ctr = b.work_ctr and a.mc_code = b.mc_code "
								+ "and a.day_no = b.day_no  and a.shift = b.shift "
								+ "and a.work_ctr =  ?  and a.mc_code = ?"
								+ "and b.work_order =  ?  and b.operation =  ?";
						
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workctrO);
						pstmt.setString(2, mcCodeO);
						pstmt.setString(3, workorder);
						pstmt.setString(4, operation);
						rs=pstmt.executeQuery();
						if(rs.next()){
							isRecordFound=true;
							loaddate=rs.getTimestamp(1);
							loadedupto=rs.getTimestamp(2);
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						System.out.println("isRecordFound --->>["+isRecordFound+"]");
						if(isRecordFound){
							errCode = "VTMAVA";
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						sql="";
						/* Assumed that atleast the same amount of time will be required by the
						   new machine to complete the work same work
					       NOTE : if this machine is of less capacity then the old one
						   and this machine is already allocated from the 
						   next day ( loadedupto + 1) then there will be a overlap?????? */
						sql="select count(1) from machine_load a,machine_load_det b "
								+ "where a.site_code = b.site_code and a.load_date = b.load_date "
								+ "and a.work_ctr = b.work_ctr and a.mc_code  = b.mc_code "
								+ "and a.day_no = b.day_no and a.shift = b.shift "
								+ "and a.work_ctr = ? and a.mc_code = ? "
								+ "and a.load_date >= ? and a.load_date < ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workctr);
						pstmt.setString(2, mcCode);
						pstmt.setTimestamp(3, loaddate);
						pstmt.setTimestamp(4, loadedupto);
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						System.out.println("count------>>["+count+"]"); 
						if(count > 0){
							errCode = "VTMCNFREE"; //The machine is already allocated
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}
						count=0;
						condParam.clear();
						condParam.add("work_ctr");
						condParam.add("mc_code");
						condParam.add(workctr);
						condParam.add(mcCode);
						count=getDBRowCount(conn, "workcntr", condParam);
						System.out.println("count workcntr------>>["+count+"]");
						if(count==0){
							errCode = "VTWCTR2"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}					
						
					} //end cnt
									
				 }	
				else if (childNodeName.equalsIgnoreCase("item_code")){
					System.out.println("in item_code--------------------");
					String itemCode="";
					int count=0;
					itemCode =  checkNull(genericUtility.getColumnValue("item_code",dom));
					System.out.println("item code wfval----->>["+itemCode+"]");
					if(itemCode.length()==0){
					 errCode = "VTITMCNN"; 
					 errList.add( errCode );
					 errFields.add( childNodeName.toLowerCase() );
					}else{
						sql="select count(*) from item where item_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, itemCode);						
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						if(count == 0){
					     errCode = "VTITMCNM"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}
					
										
				}
				else if (childNodeName.equalsIgnoreCase("proc_code")){
					System.out.println("in proc_code--------------------");
					String procCode="";
					int count=0;
					procCode =  checkNull(genericUtility.getColumnValue("proc_code",dom));
					System.out.println("proc_code wfval----->>["+procCode+"]");
					if(procCode.length()==0){
					 errCode = "VTPRCCNN"; 
					 errList.add( errCode );
					 errFields.add( childNodeName.toLowerCase() );
					}else{
						sql="select count(*) from process where proc_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, procCode);						
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						if(count == 0){
					     errCode = "VTPRCCNM"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}				
										
				}
				else if (childNodeName.equalsIgnoreCase("work_ctr")){ 
					String workCtr="",workCtrOld="";
					int count=0;
					workCtr = checkNull(genericUtility.getColumnValue("work_ctr",dom));
					workCtrOld = checkNull(genericUtility.getColumnValue("work_ctr",dom));
					System.out.println("workCtr wfval----->>["+workCtr+"]");
					System.out.println("workCtrOld wfval----->>["+workCtrOld+"]");
					//when user enter new operation against work order that time below condition check.					
					if(workCtr.length() == 0 && workCtrOld.length() == 0){
						 errCode = "VTWRKCNN"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
					}else{
						sql="select count(*) from workcntr where work_ctr = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workCtr); 					
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						if(count == 0){
					     errCode = "VTWRKCNTN"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}
				}
				else if (childNodeName.equalsIgnoreCase("mc_code")){ 
					String mcCode="",mcCodeOld="";
					int count=0;
					mcCode = checkNull(genericUtility.getColumnValue("mc_code",dom));
					mcCodeOld = checkNull(genericUtility.getColumnValue("mc_code__o",dom));
					System.out.println("mc_code wfval----->>["+mcCode+"]");
					System.out.println("mcCodeOld wfval----->>["+mcCodeOld+"]");
					if(mcCode.length() == 0 && mcCodeOld.length() ==0){
						 errCode = "VTMCNN"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
					}else{
						sql="select count(*) from machines where mc_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, mcCode); 					
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						if(count == 0){
					     errCode = "VTMCCNE"; 
						 errList.add( errCode );
						 errFields.add( childNodeName.toLowerCase() );
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}
				}
				else if (childNodeName.equalsIgnoreCase("work_order")){ 
					String workorder="",status="";
					workorder = checkNull(genericUtility.getColumnValue("work_order",dom));
					if(workorder.length() > 0){
						sql="select status from workorder where work_order = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workorder);						
						rs=pstmt.executeQuery();
						if(rs.next()){
							status=checkNull(rs.getString(1));
						}
						System.out.println("Status------->>["+status+"]");
						if(!("R".equalsIgnoreCase(status))){
							errCode = "VTWOSRAM"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );							
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
					}
					
				}
				else if (childNodeName.equalsIgnoreCase("operation_parent")){
					String parentOpertn="",workorder="",confirmedOpr="";
					parentOpertn = checkNull(genericUtility.getColumnValue("operation_parent",dom));
					workorder = checkNull(genericUtility.getColumnValue("work_order",dom));
					System.out.println("Parent operation---->>["+parentOpertn+"] workorder-->>"+workorder);
					if(parentOpertn.length() > 0 && workorder.length() > 0){
						sql="select confirmed from workorder_feedback where work_order = ? and operation = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workorder);
						pstmt.setInt(2, Integer.parseInt(parentOpertn));
						rs=pstmt.executeQuery();
						if(rs.next()){
							confirmedOpr=checkNull(rs.getString(1));
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						confirmedOpr= confirmedOpr.length() == 0 ? "N" :confirmedOpr;
						System.out.println("confirmedOpr--->>["+confirmedOpr+"]");
						
						if("N".equalsIgnoreCase(confirmedOpr)){
							errCode = "VTOPRFDB"; 
							//errString = getErrorString("site_code",errCode,userId);
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );							
						}
					}
					
				}
				// Added by mahesh on 14-07-2014
				else if (childNodeName.equalsIgnoreCase("site_code")){ 
					String siteCode = "";
					int count = 0;
					siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
					if(siteCode.length() > 0){
						sql="select count(1) from site where site_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, siteCode);						
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						
						if(count == 0){
							errCode = "VTSHSITE"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
					}else{
						errCode = "VMSITECD1"; 
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
					}
					
				}
				// End of code added by mahesh
				
				
			 } //end for loop 
			break;			
			} //end switch
			int errListSize = errList.size();
			System.out.println("errListSize ---->>"+errListSize);
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (int cnt1 = 0; cnt1 < errListSize; cnt1++ )
				{
					System.out.println("IN ERROR cnt-------["+cnt1+"]");
					errCode = (String)errList.get(cnt1);
					errFldName = (String)errFields.get(cnt1);
					System.out.println("errCode12.........."+errCode);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn, errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}	
			
			
			
		} //end try
		catch(Exception e)
		{
			System.out.println("Exception : WorkOrderRouteAlt class--->["+e.getMessage()+"]");
			e.printStackTrace();
			return "Error"; // return error message----------------------------------------
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(connDriver!=null){
					connDriver = null;
				}	
				if(rs !=null)
				{
					rs.close();
					rs=null;
				}
				
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();


		
	}
	
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{  
			System.out.println("xmlString---->>["+xmlString+"]");
			System.out.println("xmlString2---->>["+xmlString2+"]");
			System.out.println("xmlString1---->>["+xmlString1+"]");
			xmlString=xmlString==null ? "" :xmlString.trim();
			xmlString2=xmlString2==null ? "" :xmlString2.trim();
			xmlString1=xmlString==null ? "" :xmlString1.trim();
			if(xmlString.length() > 0 )
			dom = parseString(xmlString);
		
			if(xmlString1.length() > 0 )
			dom1 = parseString(xmlString1);
			
			if(xmlString2.length() > 0 )
			dom2 = parseString(xmlString2);
			
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [WorkOrderRouteAlt ][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
		return valueXmlString;
	}
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		boolean isError=false;
		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo =0;
		String columnValue = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0;
		String sql="",workorder="",mcCodeDesc="",procCodeDesc="",itemCodeDesc="", loginSiteCode = "", sitedescr = "";
	
		try
		{
			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{	
				case 1 :
				valueXmlString.append("<Detail1>");				
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				System.out.println("editFlag122 =>" +editFlag);
				
			
				if( currentColumn.trim().equalsIgnoreCase( "itm_defaultedit" ) )
				{	
					String parentOpr="",itemCodeL="",procCodeL="",procCodeLDescL="";
					System.out.println("------------in itm_default Edit--------->");		
					
				}
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{					
					System.out.println("------------in itm_default--------->");					
					workorder = checkNull(genericUtility.getColumnValue("work_order",dom));
					System.out.println("Dom in item default---->>[["+genericUtility.serializeDom(dom)+"]]");
					System.out.println("Dom in item default---->>[["+genericUtility.serializeDom(dom1)+"]]");
					System.out.println("Dom in item default---->>[["+genericUtility.serializeDom(dom2)+"]]");
					String confirm="N";
					 Calendar currentDate = Calendar.getInstance();
					 SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
					 String tranDate = simpleDateFormatObj.format(currentDate.getTime());						 
					 valueXmlString.append("<amd_date>").append("<![CDATA[" + tranDate + "]]>").append("</amd_date>");
					 System.out.println("workorder default------>>"+workorder);
					String routecodeDF=getColumnDescr(conn,"route_code","workorder","work_order",workorder);
					
					 valueXmlString.append("<work_order>").append("<![CDATA[" + workorder + "]]>").append("</work_order>");
					 valueXmlString.append("<confirmed>").append("<![CDATA[" + confirm + "]]>").append("</confirmed>");			 
					 valueXmlString.append("<route_code>").append("<![CDATA[" + routecodeDF + "]]>").append("</route_code>");
					 
					 // Added by mahesh on 14-07-2014
					 loginSiteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"));
						if ( loginSiteCode!= null && loginSiteCode.trim().length() > 0  )
						{
							sql = "select descr from site where site_code = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, loginSiteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								sitedescr = checkNull( rs.getString(1));
								System.out.println("Default loginSiteCode descr["+sitedescr+"]");
								
							}
							rs.close();
							rs = null;
							pstmt.close(); 
							pstmt = null;
							
						}
						valueXmlString.append("<site_code>").append("<![CDATA["+checkNull(loginSiteCode)+"]]>").append("</site_code>");
						valueXmlString.append("<site_descr>").append("<![CDATA["+checkNull(sitedescr)+"]]>").append("</site_descr>");
					 // End of code added by mahesh
				}				
				
				if (currentColumn.trim().equalsIgnoreCase("operation"))
				{
					String workOrder="",operation="",oprtn="",procCode="", routecode="";
					String itemCode = "",oprParent="",procCodeLDesc="",workctr = "",mcCode= "",mcCodeDescL;
					int seqno = 0;
					double wtime=0,stime = 0,retime= 0,rutime=0;
					operation = checkNull(genericUtility.getColumnValue("operation",dom));
					workOrder = checkNull(genericUtility.getColumnValue("work_order",dom));
					oprParent = checkNull(genericUtility.getColumnValue("operation_parent",dom));
					/*sql="select operation, proc_code, seq_no,item_code,"
							+ "wait_time, set_time, reset_time,run_time from worder_route "
							+ "where work_order = ? and operation = ?";*/
					sql="select operation, proc_code, seq_no,item_code, work_ctr,"
							+ "mc_code,wait_time, set_time, reset_time,run_time from worder_route "
							+ "where work_order = ? and operation = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, workOrder);
					pstmt.setString(2, operation);
					rs=pstmt.executeQuery();
					if(rs.next()){
						oprtn=checkNull(rs.getString(1));
						procCode=checkNull(rs.getString(2));
						seqno=rs.getInt(3);						
						itemCode=checkNull(rs.getString(4));						
						workctr=checkNull(rs.getString(5));							
						mcCode=checkNull(rs.getString(6));
						wtime=rs.getDouble(7);
						stime=rs.getDouble(8);
						retime=rs.getDouble(9);
						rutime=rs.getDouble(10);						
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;				
					
					 valueXmlString.append("<seq_no>").append("<![CDATA[" +seqno+ "]]>").append("</seq_no>");					 
					
					
					 valueXmlString.append("<wait_time>").append("<![CDATA[" +wtime+ "]]>").append("</wait_time>");
					 valueXmlString.append("<set_time>").append("<![CDATA[" +stime+ "]]>").append("</set_time>");
					 valueXmlString.append("<reset_time>").append("<![CDATA[" +retime+ "]]>").append("</reset_time>");
					 valueXmlString.append("<run_time>").append("<![CDATA[" +rutime+ "]]>").append("</run_time>");
					 
					 
					 
					 System.out.println("oprParent1---->>["+oprParent+"]");
					 if(oprParent.length()==0){
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						itemCodeDesc=getColumnDescr(conn,"descr","item","item_code",itemCode);
						valueXmlString.append("<item_descr>").append("<![CDATA[" +itemCodeDesc+ "]]>").append("</item_descr>");
							
						valueXmlString.append("<work_ctr__o>").append("<![CDATA[" + workctr + "]]>").append("</work_ctr__o>");
						valueXmlString.append("<work_ctr>").append("<![CDATA[" + workctr + "]]>").append("</work_ctr>");
						valueXmlString.append("<mc_code__o>").append("<![CDATA[" + mcCode + "]]>").append("</mc_code__o>");
						valueXmlString.append("<mc_code>").append("<![CDATA[" + mcCode + "]]>").append("</mc_code>");
						mcCodeDescL=getColumnDescr(conn,"descr","machines","mc_code",mcCode);
						valueXmlString.append("<machines_descr>").append("<![CDATA[" +mcCodeDescL+ "]]>").append("</machines_descr>");	 
					   valueXmlString.append("<proc_code protect = \"0\">").append("<![CDATA[" + procCode + "]]>").append("</proc_code>");
					   procCodeLDesc=getColumnDescr(conn,"descr","process","proc_code",procCode);
					   valueXmlString.append("<process_descr>").append("<![CDATA[" +procCodeLDesc+ "]]>").append("</process_descr>");					
					 }
					 
					
				}
				else if(currentColumn.trim().equalsIgnoreCase("work_order"))
				{
					String routecodeDF="",workOrderL="";
					workOrderL = checkNull(genericUtility.getColumnValue("work_order",dom));
					
					routecodeDF=getColumnDescr(conn,"route_code","workorder","work_order",workOrderL);				 					 			 
					valueXmlString.append("<route_code>").append("<![CDATA[" + routecodeDF + "]]>").append("</route_code>");
				}
				else if (currentColumn.trim().equalsIgnoreCase("mc_code"))
				{
					String mcCode="",mcCodeDescL="",operationL="",workOrderL="";
					operationL = checkNull(genericUtility.getColumnValue("operation",dom));
					workOrderL = checkNull(genericUtility.getColumnValue("work_order",dom));
					
					mcCode=checkNull(genericUtility.getColumnValue("mc_code",dom));
					mcCodeDescL=getColumnDescr(conn,"descr","machines","mc_code",mcCode);
					 if(mcCodeDesc.length() > 0)
						 valueXmlString.append("<machines_descr>").append("<![CDATA[" +mcCodeDescL+ "]]>").append("</machines_descr>");
					 else
						 valueXmlString.append("<machines_descr>").append("<![CDATA[" +mcCodeDescL+ "]]>").append("</machines_descr>");
					 
					 if(isOperationNew(conn,workOrderL,operationL)){
							valueXmlString.append("<mc_code__o>").append("<![CDATA[" +mcCode+ "]]>").append("</mc_code__o>");
						}	
						 
					
				}
				else if(currentColumn.trim().equalsIgnoreCase("item_code"))
				{
					String itemCodeL="",itemCodeLDesc="";
					itemCodeL = checkNull(genericUtility.getColumnValue("item_code",dom));
					System.out.println("item code dom---->>["+itemCodeL+"]");
					itemCodeLDesc=getColumnDescr(conn,"descr","item","item_code",itemCodeL);
					 valueXmlString.append("<item_descr>").append("<![CDATA[" +itemCodeLDesc+ "]]>").append("</item_descr>");
						
				} 
				else if(currentColumn.trim().equalsIgnoreCase("proc_code"))
				{
					String procCodeL="",procCodeLDesc="";
					procCodeL = checkNull(genericUtility.getColumnValue("proc_code",dom));
					System.out.println("Process code dom---->>["+procCodeL+"]");
					procCodeLDesc=getColumnDescr(conn,"descr","process","proc_code",procCodeL);
					valueXmlString.append("<process_descr>").append("<![CDATA[" +procCodeLDesc+ "]]>").append("</process_descr>");
						
				}
				else if(currentColumn.trim().equalsIgnoreCase("work_ctr"))
				{
					String workCtr="",workCtrOld="",operationL="",workOrderL="";
					operationL = checkNull(genericUtility.getColumnValue("operation",dom));
					workOrderL = checkNull(genericUtility.getColumnValue("work_order",dom));
					workCtr= checkNull(genericUtility.getColumnValue("work_ctr",dom));
					workCtrOld= checkNull(genericUtility.getColumnValue("work_ctr__o",dom));
					System.out.println("work_ctr dom---->>["+workCtr+"]");
					System.out.println("workOrderL dom---->>["+workOrderL+"] operation-->"+operationL);
					System.out.println("work_ctrold dom---->>["+workCtrOld+"]");
					if(isOperationNew(conn,workOrderL,operationL)){
						valueXmlString.append("<work_ctr__o>").append("<![CDATA[" +workCtr+ "]]>").append("</work_ctr__o>");
					}			
					
				}
				else if(currentColumn.trim().equalsIgnoreCase("operation_parent")) 
				{
					boolean isRecordFound=false;
					String operationParent="",procCode="",procCodeLDesc="",workOrd="",workCtrF="",mcCodeF="",
							itemCode="",itemCodeDescL="";
					operationParent = checkNull(genericUtility.getColumnValue("operation_parent",dom));
					workOrd= checkNull(genericUtility.getColumnValue("work_order",dom));
					System.out.println("operationParent dom---->>["+operationParent+"]");
					System.out.println("work_order dom---->>["+workOrd+"]");
					if(operationParent.length() > 0){
						sql="select proc_code from worder_route where work_order= ? and operation = ? ";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workOrd);
						pstmt.setInt(2, Integer.parseInt(operationParent));
						rs=pstmt.executeQuery();
						if(rs.next()){
							isRecordFound=true;
							procCode=checkNull(rs.getString(1));
							valueXmlString.append("<proc_code protect = \"1\">").append("<![CDATA[" + procCode + "]]>").append("</proc_code>");
							procCodeLDesc=getColumnDescr(conn,"descr","process","proc_code",procCode);
							valueXmlString.append("<process_descr>").append("<![CDATA[" +procCodeLDesc+ "]]>").append("</process_descr>");					
							
						}					
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						
						sql="select work_ctr,mc_code,item_code from workorder_feedback where work_order = ? and operation = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, workOrd);
						pstmt.setInt(2, Integer.parseInt(operationParent));
						rs=pstmt.executeQuery();
						if(rs.next()){
							workCtrF=checkNull(rs.getString(1));  
							mcCodeF=checkNull(rs.getString(2)); 
							itemCode=checkNull(rs.getString(3));							
						}
					
					}
					valueXmlString.append("<work_ctr__o>").append("<![CDATA[" + workCtrF + "]]>").append("</work_ctr__o>");
					 valueXmlString.append("<work_ctr>").append("<![CDATA[" + workCtrF + "]]>").append("</work_ctr>");
					 valueXmlString.append("<mc_code__o>").append("<![CDATA[" + mcCodeF + "]]>").append("</mc_code__o>");
					 valueXmlString.append("<mc_code>").append("<![CDATA[" + mcCodeF + "]]>").append("</mc_code>");
					 valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
					 
					 itemCodeDescL=getColumnDescr(conn,"descr","item","item_code",itemCode);
					 valueXmlString.append("<item_descr>").append("<![CDATA[" +itemCodeDescL+ "]]>").append("</item_descr>");
					 mcCodeDesc=getColumnDescr(conn,"descr","machines","mc_code",mcCodeF);
					 valueXmlString.append("<machines_descr>").append("<![CDATA[" +mcCodeDesc+ "]]>").append("</machines_descr>");					
					if(!isRecordFound){
						valueXmlString.append("<proc_code protect = \"0\">").append("<![CDATA[" + procCode + "]]>").append("</proc_code>");
						procCodeLDesc=getColumnDescr(conn,"descr","process","proc_code",procCode);
						valueXmlString.append("<process_descr>").append("<![CDATA[" +procCodeLDesc+ "]]>").append("</process_descr>");
					}
					
					 
						
				}
				// Code added by mahesh on 14-07-2014
				else if(currentColumn.trim().equalsIgnoreCase("site_code")){
					loginSiteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
					if ( loginSiteCode!= null && loginSiteCode.trim().length() > 0  )
					{
						sql = "select descr from site where site_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, loginSiteCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							sitedescr = checkNull( rs.getString(1));
							System.out.println("Default loginSiteCode descr["+sitedescr+"]");
							
						}else{
							sitedescr = "";
						}
						rs.close();
						rs = null;
						pstmt.close(); 
						pstmt = null;
						
					}
					valueXmlString.append("<site_descr>").append("<![CDATA["+checkNull(sitedescr)+"]]>").append("</site_descr>");
				}
				// End of code added by mahesh

				 
				
				    valueXmlString.append("</Detail1>");
				  break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					int childListLength = childNodeList.getLength();					
					
					valueXmlString.append("</Detail2>");
				}// end switch 
			valueXmlString.append("</Root>");			
			
			
		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			isError=true;
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
	}
	
	/* Method returns no. of row count. In Array list first 2 objects are condition parameter
	and  3 and 4 are condition parameter values */  
	public int getDBRowCount(Connection conn,String tableName,ArrayList<String> condParamL)
	{
		System.out.println("---------in getDBRowCount----------------");
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int cnt=0;
		String sql="";
		
		StringBuffer sql1=new StringBuffer();
		try{
			if(condParamL.size() > 0){
				System.out.println("---------arrayList Size---->>["+condParamL+"]");
				System.out.println("---------arrayList values 1---->>["+condParamL.get(0)+"]");//"operation"
				System.out.println("---------arrayList values 2---->>["+condParamL.get(1)+"]");//"work_order"
				System.out.println("---------arrayList values 3---->>["+condParamL.get(2)+"]");//operation
				System.out.println("---------arrayList values 4---->>["+condParamL.get(3)+"]");//work_order
			sql1.append("select count(*) from "+tableName+" where "+condParamL.get(0)+" = ? and "+condParamL.get(1)+" = ? ");
			if(condParamL.size() > 4)
			{
				sql1.append(" and "+condParamL.get(4)+" = ?");
			}
			System.out.println("sql final 13132---->>["+sql1.toString()+"]");
			pstmt=conn.prepareStatement(sql1.toString());
			pstmt.setInt(1, Integer.parseInt(condParamL.get(2).toString()));
			pstmt.setString(2, condParamL.get(3).toString());
			if(condParamL.size() > 4)
			{
				pstmt.setString(3, condParamL.get(5).toString());
			}
			rs=pstmt.executeQuery();
			if(rs.next()){
				cnt=rs.getInt(1);
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			}
			System.out.println("cnt in getDBRowCount--->>["+cnt+"]");
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in getDBRowCount method-----------");
			e.printStackTrace();
			return -1;
		}
		return cnt;
	}
	private boolean isOperationNew(Connection conn,String workOrder,String operation)
	{
		PreparedStatement pstmt = null ;
		ResultSet rs = null ; 
		String sql = "";
		int count=0;
		try{
			sql="select count(*) from worder_route where work_order = ? and operation  = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, workOrder);
			pstmt.setInt(2, Integer.parseInt(operation));
			rs=pstmt.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
			System.out.println("count in isOperationNew123--->>["+count+"]");
			if(rs!=null){
			rs.close();
			rs=null;
			}
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
			
			if(count == 0){
				System.out.println("Returning true...........");
				return true;
			}

		}
				catch(Exception e){
			e.printStackTrace();
		}
		return false;
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			if(findValue==null)
                findValue="";
			
			return findValue;
		 
	}
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";
			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, checkNull(errorCode));			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	private String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input.trim();
	}
	
	

	
}
