package ibase.webitm.ejb.adm.adv;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;

import ibase.system.config.ConnDriver;
import java.sql.*;
import java.rmi.RemoteException;
import javax.ejb.Stateless; 

@Stateless 
public class BondRecoveryConfirm extends ActionHandlerEJB implements BondRecoveryConfirmLocal,BondRecoveryConfirmRemote
{
	public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		
		String retString = "";
		try
		{
			
			Connection conn=null;		
			retString = confirm( tranId, xtraParams, forcedFlag, conn);		
		
		}
		catch(Exception e)
		{
			System.out.println("Exception :BondRecoveryConfirm :confirm():" + e.getMessage() + ":");
			retString = GenericUtility.getInstance().createErrorString(e);
			e.printStackTrace();
		}
		return retString;
	}
	
	public String confirm( String tranId, String xtraParams, String forcedFlag, Connection conn)
	{
		System.out.println("--------------confirm method of BondRecoveryConfirm -------------");
		ResultSet rs=null;
		PreparedStatement pstmt = null;	
		conn=null;
		ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		String sql="",confirmed="",errString="",empcode="",retString="";
		double recAmtAct=0.0,recAmtDue=0.0;
		int updCnt=0;
		try
		{
			ConnDriver connDriver=new ConnDriver();
			conn=connDriver.getConnectDB("DriverValidator");
			conn.setAutoCommit(false);
			
			sql = "SELECT emp_code,CONFIRMED FROM BOND_RECOVERY WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed =rs.getString("CONFIRMED")==null ? "":rs.getString("CONFIRMED");
				empcode=rs.getString(1);
			}	
			System.out.println("confirm value before update :"+confirmed);
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				errString = itmdbAccess.getErrorString("", "AEBCONF", "", "", conn);
				return errString;
			}
			
			sql="select emp_code,recovery_amt__act,recovery_amt__due from Bond_recovery where emp_code = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, empcode);
			rs = pstmt.executeQuery(); 
			
			sql="update employee set BOND_AMT__RECO = ? where emp_code = ?";
			pstmt = conn.prepareStatement( sql );
			
			while(rs.next())
			{
				empcode=rs.getString(1);
				recAmtAct=recAmtAct+rs.getDouble(2);
				recAmtDue=rs.getDouble(3);
				
				
				System.out.println("empcode -->"+empcode+"recAmtAct : "+recAmtAct+" recAmtDue : "+recAmtDue);
			}
			System.out.println("Recovery amt actual------>>>>"+recAmtAct);
			pstmt.setDouble(1,recAmtAct);
			pstmt.setString(2,empcode);
			updCnt = pstmt.executeUpdate();
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}	
			System.out.println("UPDATE COUNT ---->>"+updCnt);
			if (updCnt > 0)
			{			
				retString=new ITMDBAccessEJB().getErrorString("", "VCONFSUC", "", "", conn);
				updateConfirm(conn,tranId);
				conn.commit();
				System.out.println("BOND_AMT__RECO column of Employee updated successfully.......row(s) updated :"+updCnt);
				
			}else
			{
				
				retString=new ITMDBAccessEJB().getErrorString("", "VCONFUSUC", "", "", conn);
				conn.rollback();
				System.out.println("Not Updated actual Bond amount........");
			}
			
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;					
				}	
				if (conn!=null)
				{
					conn.close();
					conn=null;
				}
							
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				e.printStackTrace();
				
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}
	public void updateConfirm(Connection conn,String tranId)
	{
		System.out.println("in updateConfirm method -------->>>");
		String sql = "UPDATE BOND_RECOVERY SET CONFIRMED = 'Y' WHERE TRAN_ID = ?";
		PreparedStatement pstmt1=null;
		try
		{
			pstmt1 = conn.prepareStatement( sql );	 
			pstmt1.setString(1, tranId); 
			int updCnt = pstmt1.executeUpdate();
			System.out.println("confirm updated -------->>>>>>>>>updCount :"+updCnt);
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();					
		}
		try
		{
			if(pstmt1 != null)
			{
				pstmt1.close();
				pstmt1 = null;					
			}
				
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
			
		}
	}
}
