/**
 DEVELOPED BY RITESH TIWARI ON 10/04/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.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.SQLException;

import javax.ejb.Stateless;

@Stateless 
public class EmpInfoSubmit extends ActionHandlerEJB implements EmpInfoSubmitRemote, EmpInfoSubmitLocal
{
	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;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccess = null;
		try
		{
			genericUtility = GenericUtility.getInstance();
			itmDBAccess = new ITMDBAccessEJB();
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			connStatus = true;
			
			
			sql = " select count(*)  from doc_contents where doc_id in (select doc_id from doc_transaction_link where ref_id  =  ? ) ";
			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("No attachment found !");
				retString = itmDBAccess.getErrorString("","VTNOATCH1","");
				return retString;
			}
			
			sql = " select count(*) from emp_travel_info where tran_id = ?  and (case when status is null then 'N' else status 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 submited");
				retString= "VTCONF8";
			}
			else
			{
				sql = " update emp_travel_info set status = 'Y'  where tran_id  =  ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				upd =  pstmt.executeUpdate();
				pstmt.close();pstmt = null;
				System.out.println(" status updated:: "+upd);
				if(upd > 0)
				{
					retString = "submited";
					System.out.println("  information submited::");
				}
			}
		}
		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("submited") > -1)
			{
				try {
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				retString = itmDBAccess.getErrorString("","VTSUB1","");
			}
			else if(retString != null && retString.indexOf("VTCONF8") > -1)
			{
				retString = itmDBAccess.getErrorString("","VTALSUB1","");
			}
			else
			{
				retString = itmDBAccess.getErrorString("","VTERRSUB","");
			}
			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;
	}
}
