package ibase.webitm.ejb.adm;

import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.TransIDGenerator;//TID
import ibase.utility.CommonConstants;//TID
import ibase.webitm.utility.GenericUtility; //Added by Raj (ADM5000192)- 28/04/2008

import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class SepClose extends ValidatorEJB implements SepCloseLocal,SepCloseRemote // SessionBean
{
	/*
	public void ejbCreate() throws RemoteException, CreateException 
	{
		System.out.println("SepCloseEjb  Process modified On Date 06 November..........");
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}*/
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}

	public String itemChanged() throws RemoteException,ITMException
	{
		return "";
	}

	public String wfValData(String xmlString, String xmlString1,String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String  errString = null;
		System.out.println("Validation Start..........");
		try
		{
			System.out.println("xmlString:::"+xmlString);
			System.out.println("xmlString1:::"+xmlString1);
			System.out.println("xmlString2:::"+xmlString2);
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1); 
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString( xmlString2);
			}
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : SepCloseEJB : wfValData(String xmlString) : ==>\n"+e.getMessage());
		}
		return (errString);
	}
	public String wfValData(Document dom, Document dom1,Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = "";
		String errCode = "";
		String childNodeName = ""; 
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		String sql = "";
		String sql1 = "";
		String empCode= "";
		String empSiteAprv="";
		String resiDate="";
		String effDate = "";
		String status = "";
		String tranId = "";//Added by jaimin on 02/07/2008 (ADM5000192)
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr,currentFormNo=0;
		int childNodeListLength;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB(); 
		String userId = getValueFromXTRA_PARAMS(xtraParams,"userId");
		String empCodeAprv= getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
		String loginSite = getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			//start - Added by jaimin on 02/07/2008 (ADM5000192)
			tranId = getColumnValue("tran_id",dom); 			
			if (tranId == null || tranId.trim().length() == 0)
			{
				tranId = "*";
			}
			//End - Added by jaimin on 02/07/2008 (ADM5000192)
			conn = getConnection(); 
			stmt = conn.createStatement();
			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(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						int cnt = 0;
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if (childNodeName.trim().equals("emp_code"))
						{
							// Shifted following validation bellow in sequence - Piyush - (AD89SUN025) - 21/07/2008
							// Added by Raj - Req.ID:ADM5000192 - 27/05/08
							/*
							resiDate = genericUtility.getColumnValue("resi_date", dom);
							if (resiDate == null || resiDate.trim().length() == 0 )
							{
								errCode = "VTSEPNT";
								errString = getErrorString("emp_code",errCode,userId); 
								break;
							}
							*/
							empCode = childNode.getFirstChild().getNodeValue().trim();
							//empCode = getColumnValue("emp_code",dom); 
							if (childNode.getFirstChild() == null)
							{
								errCode = "VMEMP1";
								errString = getErrorString("emp_code",errCode,userId);
								break;
							}
					 		sql = "SELECT COUNT(*) AS COUNT FROM EMPLOYEE WHERE EMP_CODE = '"+empCode.trim()+"'";
							System.out.println("sql :"+sql);
							System.out.println("emp_code :"+empCode);
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								cnt = rs.getInt("COUNT"); 
								if (cnt == 0)
								{
									errCode = "EMPCODENF";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}
							}
							rs.close();
						/*	sql = "SELECT COUNT(*) AS COUNT FROM SEPARATION_CLOSE WHERE emp_code = '"+empCode.trim()+"' AND STATUS <>'X'";
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
			 					if (cnt != 0)
								{
									errCode = "VDEMP1";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}
							}*/
							//start - Added by jaimin on 02/07/2008 (ADM5000192)
							//sql = "SELECT COUNT(*) AS COUNT FROM SEPARATION_CLOSE WHERE emp_code = '"+empCode.trim()+"' AND STATUS <>'X' AND TRAN_ID <> '"+ tranId +"' "; //Remarked by Raj (ADM5000192)- 21/01/2009 
							sql = "SELECT COUNT(*) AS COUNT FROM SEPARATION_CLOSE WHERE emp_code = '"+empCode.trim()+"' AND STATUS <>'A' AND TRAN_ID <> '"+ tranId +"' "; //Added by Raj (ADM5000192)- 21/01/2009
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								cnt = rs.getInt("COUNT");
			 					if (cnt != 0)
								{
									errCode = "VDEMP1";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}
							}
							//End - Added by jaimin on 02/07/2008 (ADM5000192)
							
							
							//********************
							String empSite="";
							sql = "SELECT EMP_SITE FROM EMPLOYEE  WHERE EMP_CODE = '"+empCode.trim()+"'" ;
							System.out.println("sql emp site validation:::"+sql);
							
							rs = stmt.executeQuery(sql);
							if (rs.next())
		 					{
								empSite = (rs.getString(1)==null?" ":rs.getString(1));
							}
							rs.close();
							System.out.println("emp_code :"+empCode);
							System.out.println("Employee Site::["+empSite+"]");
							
						/*	sql = "SELECT EMP_SITE FROM EMPLOYEE  WHERE EMP_CODE = '"+empCodeAprv.trim()+"'" ;
							System.out.println("sql Log in site validation:::"+sql);
							System.out.println("empCodeAprv :"+empCodeAprv);
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								empSiteAprv = (rs.getString(1)==null?"":rs.getString(1));
							}
							System.out.println("LoginEmployee:::["+empCodeAprv+"]");*/
							System.out.println("LoginSite:::["+loginSite+"]");
							
							if(!(empSite.trim().equalsIgnoreCase(loginSite.trim())))
							{
								errCode = "SITEMISS";
								errString = getErrorString("site_code",errCode,userId); 
								break;
							}
								
						//	************************
							//Code Added below by Raj (ADM5000192)- 24/04/2008
					 		sql = "SELECT COUNT(*) AS COUNT FROM FULLFINAL_HDR WHERE EMP_CODE = '"+empCode.trim()+"' AND CONFIRMED = 'Y'";
							rs = stmt.executeQuery(sql);
							if (rs.next())
							{
								cnt = rs.getInt("COUNT"); 
								System.out.println("Count :: "+cnt);//Added by jaimin on 17/07/2008 (AD89SUN025)
							}
							rs.close();
							if (cnt > 0)
							{
								errCode = "VTFFS1";
								errString = getErrorString("emp_code",errCode,userId); 
								break;
							}
							else
							{
								resiDate = genericUtility.getColumnValue("resi_date", dom);
								// Shifted following validation from above in sequence - Piyush - (AD89SUN025) - 21/07/2008
								// Added by Raj - Req.ID:ADM5000192 - 27/05/08
								if (resiDate == null || resiDate.trim().length() == 0 )
								{
									errCode = "VTSEPNT";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}

								java.sql.Timestamp resiDate1	= java.sql.Timestamp.valueOf(genericUtility.getValidDateTimeString(resiDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()));
								//sql1 = "SELECT STATUS FROM SEPARATION WHERE EMP_CODE = '"+empCode.trim()+"' AND RESI_DATE = ? ";
								//sql1 = "SELECT STATUS FROM SEPARATION WHERE EMP_CODE = '"+empCode.trim()+"'  "; //Commented by jaimin on 17/07/2008 (AD89SUN025)
								sql1 = "SELECT STATUS FROM SEPARATION WHERE EMP_CODE = '"+empCode.trim()+"'  AND STATUS = 'C' "; //Added by jaimin on 17/07/2008 (AD89SUN025)
								System.out.println("sql1 :: "+sql1);//Added by jaimin on 17/07/2008 (AD89SUN025)
								pstmt = conn.prepareStatement(sql1);
								//pstmt.setTimestamp(1, resiDate1);//Commented by jaimin on 30/06/2008 (ADM5000192)
								rs1 = pstmt.executeQuery();
								if (rs1.next())
								{
									status = rs1.getString("status");
									System.out.println("status :: "+status);//Added by jaimin on 17/07/2008 (AD89SUN025)
								}
								else
								{
									status = "*"; // To identify seperation is not exist
								}
								rs1.close();
								pstmt.close();
								if (status != null && status.trim().length() > 0 && status.equals("*"))
								{
									System.out.println("status is * ");//Added by jaimin on 17/07/2008 (AD89SUN025)
									errCode = "VTSEPNT";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}
								else if (status != null && status.trim().length() > 0 && !status.equals("C"))
								{
									System.out.println("status is C ");//Added by jaimin on 17/07/2008 (AD89SUN025)
									errCode = "VTSEPNTCON";
									errString = getErrorString("emp_code",errCode,userId); 
									break;
								}
							}
							//Add ended by Raj (ADM5000192)- 24/04/2008
						}
					}
						
			}//END SWITCH
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
		}
		//Added below by Raj (AD90SUN017)- 12/05/2010
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (rs1 != null)
				{
					rs1.close();
					rs1 = null;
				}
				if (stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e){}
		}
		//Addition ended by Raj (AD90SUN017)- 12/05/2010
		System.out.println("ErrString ::"+errString);
		return errString;
	}//END OF VALIDATION 
	public String itemChanged(String xmlString, String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{
			dom = parseString(xmlString); 
			System.out.println("xmlString:::" + xmlString);
			System.out.println("xmlString1:::" + xmlString1);
			System.out.println("xmlString2:::" + xmlString2);
			dom1 = parseString(xmlString1); 
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [SepCloseEJB][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
	{
		int n;
		int currentFormNo = 0;
		int ctr = 0;
		String errCode = "";
		Connection connSep = null;
		Statement stmtSep = null;
		ResultSet rsSep = null;
		String errString = "";
		String sql = "";
		String columnValue = "";
		String loginSite = "";
		String childNodeName = "";
		
		NodeList parentNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		NodeList childNodeList = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		java.util.Date DateX = new java.util.Date();
		String userId = getValueFromXTRA_PARAMS(xtraParams,"userId");
		String empCodeAprv= getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
		loginSite = getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		StringBuffer retString = new StringBuffer();
		StringBuffer valueXmlString = new StringBuffer();
		System.out.println("xtraParams::::::::::::::::"+xtraParams);
		//Added below by Raj (AD90SUN017)- 12/05/2010
		String status		= "";
		String sepMode		= ""; 
		String reason		= "";
		String vacNoCreated = "";
		String confDate	= "";
		String empCodeConf = "";
		String reliveDate	= "";
		//Addition ended by Raj (AD90SUN017)- 12/05/2010
		
		try
		{
			connSep = getConnection(); 
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println("[SepCloseEJB] [itemChanged] :currentFormNo ....." +currentFormNo);
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("FORM NO:::"+currentFormNo);							
			switch (currentFormNo)
			{
				case 1:
//	     			SEARCHING THE DOM FOR THE INCOMING COLUMN VALUE START
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0;
					int childNodeListLength = childNodeList.getLength();
					do
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if(childNodeName.equals(currentColumn))
						{
							if (childNode.getFirstChild() != null)
							{
								columnValue=childNode.getFirstChild().getNodeValue().trim();
							}
						}
						ctr++;
					}while(ctr < childNodeListLength  && !childNodeName.equals(currentColumn));
					System.out.println("current column="+currentColumn);
					if (currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						valueXmlString.append("<Detail1>");	
						String currDate = "";
						java.sql.Timestamp ts = new java.sql.Timestamp(System.currentTimeMillis());
						Object date = null;
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
						date = sdf.parse(ts.toString());
						SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
						currDate=sdf1.format(date).toString();
						String siteDescr = "";
						String fName=  "";
						String lName=  "";
								
						/*//start - Comented by jaimin on 17/07/2008 (AD89SUN025)
						//sql = "SELECT EMP_FNAME,EMP_MNAME,EMP_LNAME  FROM EMPLOYEE  WHERE EMP_CODE = '"+empCodeAprv.trim()+"'" ;
						sql = "SELECT EMP_FNAME,EMP_LNAME  FROM EMPLOYEE  WHERE EMP_CODE = '"+empCodeAprv.trim()+"'" ;
						System.out.println("Log in employee ::::::["+sql+"]");
						stmtSep = connSep.createStatement();
						rsSep = stmtSep.executeQuery(sql);
						while (rsSep.next())
						{ 
							fName = (rsSep.getString(1)==null?"":rsSep.getString(1));
							lName = (rsSep.getString(2)==null?"":rsSep.getString(2));
							
						}
						
						stmtSep.close();
						//added on 6th nov 2006
						valueXmlString.append("<emp_code__aprv>").append(empCodeAprv).append("</emp_code__aprv>\r\n");
						valueXmlString.append("<emp_fname_1>").append(fName).append("</emp_fname_1>\r\n");
						valueXmlString.append("<emp_lname_1>").append(lName).append("</emp_lname_1>\r\n");
						*///End - Commented by jaimin on 17/07/2008 (AD89SUN025)
						valueXmlString.append("<tran_date>").append(currDate).append("</tran_date>\r\n");
						
					
						System.out.println("EmpCodeaprv::::["+empCodeAprv+"]");
						System.out.println("EmpCodeaprv(FNAME)::::["+fName+"]");
						System.out.println("EmpCodeaprv(LNAME)::::["+lName+"]");
						System.out.println("Tran Date=["+currDate+"]");
						valueXmlString.append("</Detail1>\r\n");										
						
					}
					
					else if (currentColumn.trim().equalsIgnoreCase("emp_code"))
					{
						valueXmlString.append("<Detail1>");	
						String siteDescr = "";
						String dateStr = "";
						//String reliveDateStr="";//Remarked by Raj (AD90SUN017)- 13/05/2010
						String firstName="";
						String lastName="";
						String approverfirstName = ""; //Added by Raj (AD90SUN017)- 12/05/2010
						String approverlastName = ""; //Added by Raj (AD90SUN017)- 12/05/2010
						Object date = null;
						loginSite = "";//Added by jaimin on 17/07/2008 (AD89SUN025)
						String sepFound = "N";  // Added by Raj - Req.ID: ADM5000192 - 27/05/08
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
						SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
						java.sql.Date rDate = null;//new java.sql.Date();
						//sql = "SELECT RESI_DATE  FROM SEPARATION WHERE  EMP_CODE = '"+columnValue.trim()+"'"; // Remarked by Raj - Req. ID. ADM5000192 - 27/05/08
						sepFound = "N"; // Added by Raj - Req.ID: ADM5000192 - 27/05/08
						//sql = "SELECT RESI_DATE FROM SEPARATION WHERE  EMP_CODE = '"+columnValue.trim()+"' AND STATUS = 'C'";  // Added STATUS CLAUSE IN WHERE CONDITION by Raj - Req.ID: ADM5000192 - 27/05/08 ////Remarked by Raj (AD90SUN017)- 08/05/2010
						sql = "SELECT RESI_DATE, STATUS, RELIEVE_DATE, SEPARATION_MODE, REASON, VAC_NO__CREATED, CONF_DATE, EMP_CODE__CONF FROM SEPARATION WHERE  EMP_CODE = '"+columnValue.trim()+"' AND STATUS = 'C'"; //Added by Raj (AD90SUN017)- 08/05/2010
						System.out.println("Sql For Separation Details :::["+sql+"]"); 
						stmtSep = connSep.createStatement();
						rsSep = stmtSep.executeQuery(sql);
						while (rsSep.next())
						{
							rDate = rsSep.getDate(1);
							date = sdf.parse(rDate.toString());
							dateStr=sdf1.format(date).toString();
							sepFound = "Y"; // Added by Raj - Req.ID: ADM5000192 - 27/05/08
							//Added below by Raj (AD90SUN017)- 08/05/2010
							status		= rsSep.getString("STATUS");
							sepMode		= rsSep.getString("SEPARATION_MODE");
							reason		= rsSep.getString("REASON");
							vacNoCreated = rsSep.getString("VAC_NO__CREATED");
							confDate	= rsSep.getString("CONF_DATE");
							empCodeConf = rsSep.getString("EMP_CODE__CONF");
							reliveDate	= rsSep.getString("RELIEVE_DATE");
							//Addition ended by Raj (AD90SUN017)- 08/05/2010
						}
						rsSep.close();
						rsSep = null;
						stmtSep.close();
						stmtSep = null;
						//Added below by Raj (AD90SUN017)- 08/05/2010
						if ( confDate != null && confDate.trim().length() > 0 )
						{
							confDate = genericUtility.getValidDateTimeString(confDate,genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat() );
						}
						if ( reliveDate != null && reliveDate.trim().length() > 0 )
						{
							reliveDate = genericUtility.getValidDateTimeString(reliveDate,genericUtility.getDBDateFormat(),genericUtility.getApplDateFormat() );
						}
						valueXmlString.append("<sep_status>").append(status).append("</sep_status>\r\n");
						valueXmlString.append("<separation_mode>").append(sepMode).append("</separation_mode>\r\n");
						valueXmlString.append("<reason>").append(reason).append("</reason>\r\n");
						valueXmlString.append("<vac_no__created>").append(vacNoCreated).append("</vac_no__created>\r\n");
						valueXmlString.append("<conf_date_sep>").append(confDate).append("</conf_date_sep>\r\n");
						valueXmlString.append("<emp_code__conf_sep>").append(empCodeConf).append("</emp_code__conf_sep>\r\n");
						valueXmlString.append("<relieve_date_sep>").append(reliveDate).append("</relieve_date_sep>\r\n"); 
						sql = "SELECT EMP_FNAME,EMP_LNAME FROM EMPLOYEE WHERE EMP_CODE = '"+empCodeConf+"'" ;
						System.out.println("Sql For Employee Who Confirmed Separation***["+sql+"]");
						stmtSep = connSep.createStatement();
						rsSep = stmtSep.executeQuery(sql);
						while (rsSep.next())
						{
							approverfirstName=(rsSep.getString(1)==null?"":rsSep.getString(1));
							approverlastName=(rsSep.getString(2)==null?"":rsSep.getString(2));
						}
						stmtSep.close();
						valueXmlString.append("<emp_fname_sep_conf>").append(approverfirstName).append("</emp_fname_sep_conf>\r\n");
						valueXmlString.append("<emp_lname_sep_conf>").append(approverlastName).append("</emp_lname_sep_conf>\r\n");
						//Addition ended by Raj (AD90SUN017)- 08/05/2010
						
						System.out.println("EmpCode:::["+currentColumn+"]");
						System.out.println("ResiDate(Database):::["+rDate+"]");
						System.out.println("APPLICATION DATE FORMAT::["+dateStr+"]");
						System.out.println("Seperation Found::["+sepFound+"]"); // Added by Raj - Req.ID: ADM5000192 - 27/05/08
				
						//java.sql.Date reliveDate = null; //Remarked by Raj (AD90SUN017)- 12/05/2010
						if (sepFound != null && sepFound == "Y") // If condition is Added by Raj - Req.ID: ADM5000192 - 27/05/08
						{
							//sql = "SELECT EMP_SITE,RELIEVE_DATE,EMP_FNAME,EMP_LNAME  FROM EMPLOYEE  WHERE EMP_CODE = '"+columnValue.trim()+"'" ; //Remarked and added below by Raj (AD90SUN017)- 12/05/2010
							sql = "SELECT EMP_SITE,EMP_FNAME,EMP_LNAME  FROM EMPLOYEE  WHERE EMP_CODE = '"+columnValue.trim()+"'" ; //Added by Raj (AD90SUN017)- 12/05/2010
							System.out.println("Sql For Employee***["+sql+"]");
							stmtSep = connSep.createStatement();
							rsSep = stmtSep.executeQuery(sql);
							while (rsSep.next())
							{
								
								loginSite = (rsSep.getString(1)==null?"":rsSep.getString(1)); 
								//reliveDate = rsSep.getDate(2); //Remarked by Raj (AD90SUN017)- 12/05/2010
								firstName=(rsSep.getString(2)==null?"":rsSep.getString(2));
								lastName=(rsSep.getString(3)==null?"":rsSep.getString(3));
								//date = sdf.parse(reliveDate.toString()); //Remarked by Raj (AD90SUN017)- 12/05/2010
								//reliveDateStr=sdf1.format(date).toString(); //Remarked by Raj (AD90SUN017)- 12/05/2010
												
							}
							System.out.println("Employee Site code:::["+loginSite+"]");
						//	System.out.println("Relive Date  :::["+reliveDate+"]");
							System.out.println("Employee First Name:::["+firstName+"]");
							System.out.println("Employee Last Name  :::["+lastName+"]");
						
							stmtSep.close();
							//System.out.println("ReliveDateStr in Appl Format:::["+reliveDateStr+"]"); //Remarked by Raj (AD90SUN017)- 12/05/2010
							
							sql = "SELECT DESCR FROM SITE WHERE SITE_CODE ='" +loginSite+ "'";
							System.out.println("Sql for site descr "+sql);
							stmtSep = connSep.createStatement();
							rsSep = stmtSep.executeQuery(sql);
							while (rsSep.next())
							{
								siteDescr = rsSep.getString(1);
							}
							stmtSep.close();
							
							/* //Start - commented by jaimin on 17/07/2008 (AD89SUN025)
							valueXmlString.append("<emp_fname>").append(firstName).append("</emp_fname>\r\n");
							valueXmlString.append("<emp_lname>").append(lastName).append("</emp_lname>\r\n");
							valueXmlString.append("<resi_date>").append(dateStr).append("</resi_date>\r\n");
							valueXmlString.append("<site_code>").append(loginSite).append("</site_code>\r\n");
							valueXmlString.append("<relieve_date>").append(reliveDateStr).append("</relieve_date>\r\n");
							valueXmlString.append("<site_descr>").append(siteDescr).append("</site_descr>\r\n");
							*/ //End - commented by jaimin on 17/07/2008 (AD89SUN025)
						}    			
						//Start - Added by jaimin on 17/07/2008 (AD89SUN025)
						valueXmlString.append("<emp_fname>").append(firstName).append("</emp_fname>\r\n");
						valueXmlString.append("<emp_lname>").append(lastName).append("</emp_lname>\r\n");
						valueXmlString.append("<resi_date>").append(dateStr).append("</resi_date>\r\n");
						valueXmlString.append("<site_code>").append(loginSite).append("</site_code>\r\n");
						//valueXmlString.append("<relieve_date>").append(reliveDateStr).append("</relieve_date>\r\n"); //Remarked by Raj (AD90SUN017)- 11/05/2010
						valueXmlString.append("<site_descr>").append(siteDescr).append("</site_descr>\r\n");
						//End - Added by jaimin on 17/07/2008 (AD89SUN025)

    					valueXmlString.append("</Detail1>\r\n");
					}

					break;
					
					case 2:
					//valueXmlString.append("</Detail>");
					//break;
				}//END OF SWITCH
				valueXmlString.append("</Root>");	
				System.out.println("valueXmlString[sepclose]"+valueXmlString);
			}//END OF TRY
		
		catch(Exception e)
		{
			System.out.println("Exception ::"+e.getMessage());
			e.printStackTrace();
		}
		//Added below by Raj (AD90SUN017)- 12/05/2010
		finally
		{
			try
			{
				if (rsSep != null)
				{
					rsSep.close();
					rsSep = null;
				}
				if (stmtSep != null)
				{
					stmtSep.close();
					stmtSep = null;
				}
				if (connSep != null)
				{
					connSep.close();
					connSep = null;
				}
			}
			catch(Exception e) {}
		}
		//Addition ended by Raj (AD90SUN017)- 12/05/2010

		return valueXmlString.toString();
	}//END OF ITEMCHNGE
}