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();
		}
		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);
		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="";
						String firstName="";
						String lastName="";
						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
						System.out.println("Sql For Resignation Date :::["+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
						}
						stmtSep.close();
						
						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;
						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()+"'" ;
							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);
								firstName=(rsSep.getString(3)==null?"":rsSep.getString(3));
								lastName=(rsSep.getString(4)==null?"":rsSep.getString(4));
								date = sdf.parse(reliveDate.toString());
								reliveDateStr=sdf1.format(date).toString();
												
							}
							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+"]");
							
							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");
						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();
		}
		return valueXmlString.toString();
	}//END OF ITEMCHNGE
}