/*
authored by Pawan 10/7/2010 to confirm the transaction
*/

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 javax.naming.InitialContext;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.GenericUtility; 

@javax.ejb.Stateless
public class SepCloseConf extends ActionHandlerEJB implements SepCloseConfLocal, SepCloseConfRemote
{
	public String confirm( String tranId, String xtraParams, String forcedFlag ) throws RemoteException,ITMException
	{	
		System.out.println("SepCloseConf Called........");
		int ctr = 0;
		String empCode = "";
		String status = "";
		String tranIdFFS = "";
		String resiDate = "";

		String separationMode = "";
		String reason = "";
		String vacNoCreated = "";
		String empCodeConf = "";
		String deleteSepOnClose = "";
		String relieveDate = "";
		String confDate = "";
		String sql = "";

		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rSet = null ;	
		Connection connectionObject = null;
		String errString = "";
		String loginEmpCode = "";
		String sepCloseStatus = "";
		boolean isError = false;

		int updCnt = 0;
		
		AdmCommon admCommon = null;
		ITMDBAccessLocal itmdbAccessLocal = null ;
		
		try
		{

			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			itmdbAccessLocal = (ITMDBAccessLocal)ctx.lookup("ibase/ITMDBAccessEJB/local");
			
			ConnDriver connDriver = new ConnDriver();
			connectionObject = connDriver.getConnectDB("DriverValidator");
			connectionObject.setAutoCommit(false);

			sql = "SELECT EMP_CODE, RESI_DATE , CASE WHEN STATUS IS NULL THEN ' ' ELSE STATUS END AS STATUS FROM SEPARATION_CLOSE WHERE TRAN_ID = '"+tranId+"'";

			stmt = connectionObject.createStatement();	
			rSet = stmt.executeQuery(sql);

			if (rSet.next())
			{
				empCode = rSet.getString("EMP_CODE");
				resiDate = rSet.getString("RESI_DATE");
				sepCloseStatus = rSet.getString("STATUS");
			}
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}
			if (sepCloseStatus.equalsIgnoreCase("X") )
			{
					isError = true;
					errString = itmdbAccessLocal.getErrorString("","VTCREDIT01","");
					return errString;		
			}
			sql = "SELECT COUNT(*)  FROM FULLFINAL_HDR WHERE EMP_CODE = '"+empCode+"' AND CONFIRMED = 'Y'";
			rSet = stmt.executeQuery(sql);
				
			if( rSet.next() )
			{
				ctr = rSet.getInt(1);
				if( ctr > 0 )
				{
					isError = true;
					errString = itmdbAccessLocal.getErrorString("","VTFFS1","");
					return errString;		
				}
			}
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}
				
			sql = "SELECT STATUS, RELIEVE_DATE, SEPARATION_MODE, REASON, VAC_NO__CREATED, CONF_DATE, EMP_CODE__CONF FROM SEPARATION WHERE EMP_CODE = ? AND RESI_DATE = ?";

			pstmt = connectionObject.prepareStatement( sql );	
			pstmt.setString(1,empCode);
			pstmt.setTimestamp( 2,Timestamp.valueOf( resiDate ) );
			rSet = pstmt.executeQuery();

			if( rSet.next() )
			{				
				status = rSet.getString("STATUS");
				relieveDate = rSet.getString("RELIEVE_DATE");
				separationMode = rSet.getString("SEPARATION_MODE");
				reason = rSet.getString("REASON");
				vacNoCreated = rSet.getString("VAC_NO__CREATED");
				confDate = rSet.getString("CONF_DATE");
				empCodeConf = rSet.getString("EMP_CODE__CONF");
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}
			if( status != null && !status.equalsIgnoreCase("C") )
			{
				isError = true;
				errString = itmdbAccessLocal.getErrorString("","VTSEPNTCON","");
				return errString;
			}
			GenericUtility genericUtility = GenericUtility.getInstance();
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");

			sql = "UPDATE SEPARATION_CLOSE SET STATUS = 'A', RELIEVE_DATE = ?, SEPARATION_MODE = ?, REASON = ?,	VAC_NO__CREATED = ?, SEP_STATUS = ?, CONF_DATE_SEP = ?, EMP_CODE__CONF_SEP = ?, EMP_CODE__APRV = ? WHERE TRAN_ID = ?";
			pstmt = connectionObject.prepareStatement( sql );	
			pstmt.setTimestamp(1, Timestamp.valueOf( relieveDate ) );
			pstmt.setString(2, separationMode); 
			pstmt.setString(3, reason); 
			pstmt.setString(4, vacNoCreated); 
			pstmt.setString(5, status); 
			pstmt.setTimestamp(6,Timestamp.valueOf( confDate ) ); 
			pstmt.setString(7, empCodeConf); 
			pstmt.setString(8, loginEmpCode); 
			pstmt.setString(9, tranId); 
			updCnt = pstmt.executeUpdate();
			if ( updCnt > 0 )
			{
				System.out.println("SEPARATION_CLOSE Updated Successfully ::"+updCnt);
			}

			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}				
			
			admCommon = new AdmCommon();
			deleteSepOnClose = admCommon.getEnv("999999", "DELSEPONCLOSE", connectionObject);

			if( deleteSepOnClose !=null && deleteSepOnClose.equalsIgnoreCase("NULLFOUND") )
			{
				isError = true;
				errString = itmdbAccessLocal.getErrorString("","VSENVAR1","");
				return errString;			
			}
			else if( deleteSepOnClose !=null && !deleteSepOnClose.equalsIgnoreCase("Y") )
			{				
				deleteSepOnClose = "N";
			}

			if( deleteSepOnClose.equalsIgnoreCase("Y") )
			{			
				sql = "DELETE FROM SEPARATION WHERE EMP_CODE = ? AND RESI_DATE = ?";
				pstmt = connectionObject.prepareStatement( sql );	
				pstmt.setString(1,empCode);
				pstmt.setTimestamp(2,Timestamp.valueOf( resiDate ) );
				pstmt.executeUpdate();
			}
			else
			{			
				sql = "UPDATE SEPARATION SET STATUS = 'X' WHERE EMP_CODE = ? AND RESI_DATE = ?";
				pstmt = connectionObject.prepareStatement( sql );	
				pstmt.setString(1,empCode);
				pstmt.setTimestamp(2,Timestamp.valueOf( resiDate ) );
				pstmt.executeUpdate();				
			}

			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}

			sql = "SELECT TRAN_ID  FROM FULLFINAL_HDR WHERE EMP_CODE = '"+empCode+"' AND CONFIRMED != 'X'";
			rSet = stmt.executeQuery(sql);
			if( rSet.next() )
			{
				tranIdFFS = rSet.getString("tran_id");

				sql = "DELETE FROM FULLFINAL_DET WHERE TRAN_ID = '"+tranIdFFS+"'";
				updCnt = stmt.executeUpdate(sql);
				if ( updCnt > 0 )
				{
					System.out.println("deleted from FULLFINAL_DET Successfully ::"+updCnt);
				}

				sql = "UPDATE FULLFINAL_HDR SET CONFIRMED = 'X', TOTAL_AMT = 0, IT_AMT = 0, AMOUNT = 0 WHERE TRAN_ID = '"+tranIdFFS+"'";
				updCnt = stmt.executeUpdate(sql);
				if ( updCnt > 0 )
				{
					System.out.println("FULLFINAL_HDR Updated Successfully ::"+updCnt);
				}
			}     
			if ( rSet != null )
			{
				rSet.close();
				rSet = null;
			}

			sql = "UPDATE EMPLOYEE SET RESI_DATE = NULL, RELIEVE_DATE = NULL WHERE EMP_CODE = '"+empCode+"'";
			updCnt = stmt.executeUpdate(sql);
			if ( updCnt > 0 )
			{
				System.out.println("EMPLOYEE Updated Successfully ::"+updCnt);
			}
		}
		catch(Exception e)
		{
			isError = true;
			try
			{
				connectionObject.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Commiting and Closing Connection..........");
				if ( !(isError) )
				{
					connectionObject.commit();
					errString = itmdbAccessLocal.getErrorString("","SEPCLCONF","");
				}
				else
				{
					connectionObject.rollback();
				}
				if( stmt != null )
				{
					stmt.close();
					stmt = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if ( rSet != null )
				{
					rSet.close();
					rSet = null;
				}
				if( connectionObject != null && ! connectionObject.isClosed() )
				{
					connectionObject.close();
					connectionObject = null;
				}
			}
			catch( Exception e)
			{
				e.printStackTrace();
			}
		}
		return errString;
	}
}
