/**
 DEVELOPED BY RITESH TIWARI ON 27/03/14 
 PURPOSE: WS3LSUN003 (StarClub Employee details.)
 */
package ibase.webitm.ejb.dis.adv;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.utility.E12GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.ejb.Stateless;

@Stateless 
public class EmpTravelInfoConf extends ActionHandlerEJB implements EmpTravelInfoConfRemote, EmpTravelInfoConfLocal
{
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{

		String retString = "";
		Connection conn = null;
		boolean isConn= false;
		try
		{
			retString = this.confirm(tranID, xtraParams, forcedFlag, conn, isConn);
			System.out.println("retString:::::"+retString);
		}
		catch(Exception e)
		{
			System.out.println("Exception in [StockTransferConf] confirm " + e.getMessage());
			throw new ITMException(e);
		}
		return retString;
	}
	public String confirm( String tranId, String xtraParams, String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{
		String retString = "";
		String sql = "";
		int count=0,upd=0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ibase.utility.E12GenericUtility genericUtility= null;
		ITMDBAccessEJB itmDBAccess = null;
		try
		{
			genericUtility = new  ibase.utility.E12GenericUtility();
			itmDBAccess = new ITMDBAccessEJB();
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			connStatus = true;
			String userId="",empCode="",status="";
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
						
			sql = " select count(*) from emp_travel_info where tran_id = ?  and emp_code in (SELECT EMP_CODE FROM USERS WHERE CODE = ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			pstmt.setString(2, userId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			if(count > 0 )
			{
				System.out.println("same employee not eligiable to confirme");
				retString = itmDBAccess.getErrorString("","VTNTCNF1","");
				return retString;
			}
			//changes done by Chandrashekar on 15-may-2014
			sql = " select status from emp_travel_info where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				status = rs.getString("status");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			if("X".equalsIgnoreCase(status))
			{	System.out.println("Travel information Cancelled");
				retString = itmDBAccess.getErrorString("","VTNOTCONF3","");
				return retString;
			}
			else
			{
				sql = " select count(*) from emp_travel_info where tran_id = ?  and (case when confirmed is null then 'N' else confirmed end = 'Y') ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				if(count > 0 )
				{
					System.out.println("The transaction already confirmed");
					retString= "VTCONF8";
				}
				else
				{
					sql = " update emp_travel_info set confirmed = 'Y' , conf_date = ? where tran_id  =  ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1, new java.sql.Timestamp( System.currentTimeMillis() ) );
					pstmt.setString(2, tranId);
					upd =  pstmt.executeUpdate();
					pstmt.close();pstmt = null;
					System.out.println(" confirmed transaction:: "+upd);
					if(upd > 0)
					{
						retString = "confirmed";
						System.out.println("  transaction confirmed::");
					}
				}
				
			}
			//End Changes done by Chandrashekar on 15-may-2014		
			
		}
		catch( Exception e )
		{
			try
			{
				conn.rollback();
				retString = e.getMessage();
				e.printStackTrace();
			}
			catch (Exception e1)
			{
				System.out.println("Exception : "+e);e.printStackTrace();
			}
			throw new ITMException(e);
		}
		finally
		{
			if(retString != null && retString.indexOf("confirmed") > -1)
			{
				
				try {
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				retString = itmDBAccess.getErrorString("","VCNFSUC1","");
			}
			else if(retString != null && retString.indexOf("VTCONF8") > -1)
			{
				retString = itmDBAccess.getErrorString("","VTCNF","");
			}
			else
			{
				retString = itmDBAccess.getErrorString("","VTERRCON","");
			}
			try{
				
			if(rs != null)
			{
				rs.close();rs = null;
			}
			if(pstmt != null)
			{
				pstmt.close();pstmt = null;
			}
			}
			catch(Exception e)
			{System.out.println("Exception : "+e);e.printStackTrace();}
			
		}

		
		return retString ;
	}
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
}
