package ibase.webitm.ejb.fin;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless;
import ibase.system.config.ConnDriver;
import ibase.utility.GenericUtility;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.utility.ITMException;

@Stateless
public class ConfirmMISCVOUCHEREJB extends ActionHandlerEJB implements ConfirmMISCVOUCHEREJBLocal, ConfirmMISCVOUCHEREJBRemote{
//MISCVOUCHERWRKFLW
	public ConfirmMISCVOUCHEREJB()
	{
		super();

	}

	public String confirm(String tranId, String refSeries, String flag, String prfmrCode, String roleCodeAprv, String xmlDataAll,String prcID) throws RemoteException, ITMException
	{

		Connection                  conn = null;
		String                 retString = null;
		ConnDriver            connDriver = null,oConn=null;;
		//oracle.jdbc.OracleConnection oConn=null;
		try
		{
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			//oConn = connDriver.getConnectDB("DriverITM");
			//oConn = connDriver.getOracleConnection("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			retString  = confirm( conn ,tranId, refSeries , flag   , prfmrCode , roleCodeAprv , xmlDataAll , prcID );
		}
		catch (Exception e)
		{
			System.out.println("Exception :ConfirmMISCVOUCHEREJB :confirm():" + e.getMessage() + ":");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					conn.close();
					conn =null;
				}
				//if(oConn != null)
				//{
				//	oConn.close();
				//	oConn =null;
				//}
			}
			catch(SQLException sqle)
			{
				sqle.printStackTrace();
			}
		}

		return retString;
	}

	public String confirm( Connection conn ,  String tranId ,String refSeries ,String flag , String prfmrCode , String rolecodeAprv , String xmlDataAll ,String prcID ) throws RemoteException, ITMException
	{
		PreparedStatement  pStmt = null;
		ResultSet rs = null;
		String  sql = "";		
		String  confirmed = "";		
	    //String  roleCode = "";		
	    //java.sql.Date actionDateAprv1 = null;
		int cnt=0,i=0;
		double budgetAmtAnal = 0.0,consumedAmtAnal=0.0;
		boolean amountFlag = false;
		String siteCode="",acctCode="",cctrCode="",analCode="",deptCode="",flagStatus="",rejectionRemark="";
		try
		{

			System.out.println("tranId-->["+tranId+"],prfmrCode-->["+prfmrCode+"], rolecodeAprv-->["+rolecodeAprv+"],prcID-->["+prcID+"]:::refSeries["+refSeries+"flag-->["+flag+"]");			
			
			sql = "select confirmed from misc_voucher where tran_id = ? ";			
			pStmt = conn.prepareStatement(sql);			
			pStmt.setString(1, tranId);			
			rs = pStmt.executeQuery();			
			if (rs.next())
			{				
				confirmed = rs.getString("confirmed");			
			}			
			rs.close();
			pStmt.close();
			rs = null;
			pStmt = null;

			if(!"Y".equalsIgnoreCase(confirmed))  
			{			
			/*
				sql = "select max(line_no) from MISC_VOUCHDET WHERE tran_id = ?  ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString( 1, tranId );
				rs = pStmt.executeQuery();
				if (rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				sql = "select site_code from MISC_VOUCHER WHERE tran_id = ?  ";
				pStmt = conn.prepareStatement(sql);
				pStmt.setString( 1, tranId );
				rs = pStmt.executeQuery();
				if (rs.next())
				{
					siteCode = rs.getString(1);
				}
				rs.close();
				rs = null;
				pStmt.close();
				pStmt = null;
				
				
				for ( i=1; i<=cnt ; i++)
				{
					
					//FN_GET_BUDGET_AMT('VOUCH',VOUCHER.SITE_CODE,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,VOUCHRCP.anal_code
					//		,VOUCHRCP.DEPT_CODE,'A') BUDGET_AMT_ANAL > FN_GET_CONS_AMT('VOUCH',VOUCHER.SITE_CODE,VOUCHRCP.ACCT_CODE,VOUCHRCP.CCTR_CODE,VOUCHRCP.anal_code,VOUCHRCP.DEPT_CODE,'A')
					//		CONSUMED_AMT_ANAL
					
					
					sql = "select ACCT_CODE,CCTR_CODE,anal_code,DEPT_CODE  from MISC_VOUCHDET WHERE tran_id = ?  and line_no = ? ";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString( 1, tranId );
					pStmt.setString( 2, Integer.toString(i) );
					rs = pStmt.executeQuery();
					if (rs.next())
					{
						acctCode = rs.getString(1);
						cctrCode = rs.getString(2);
						analCode = rs.getString(3);
						deptCode = rs.getString(4);
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
					
					sql = " select FN_GET_BUDGET_AMT('VOUCH',?,?,?,?,?,'A') BUDGET_AMT_ANAL , " +
						  " FN_GET_CONS_AMT('VOUCH',?,?,?,?,?,'A') CONSUMED_AMT_ANAL from dual";
					pStmt = conn.prepareStatement(sql);
					pStmt.setString( 1, siteCode );
					pStmt.setString( 2, acctCode );
					pStmt.setString( 3, cctrCode );
					pStmt.setString( 4, analCode );
					pStmt.setString( 5, deptCode );
					pStmt.setString( 6, siteCode );
					pStmt.setString( 7, acctCode );
					pStmt.setString( 8, cctrCode );
					pStmt.setString( 9, analCode );
					pStmt.setString( 10, deptCode );
					
					rs = pStmt.executeQuery();
					if (rs.next())
					{
						budgetAmtAnal = rs.getDouble(1);
						consumedAmtAnal = rs.getDouble(2);
						
						System.out.println("i:["+i+"]:::budgetAmtAnal::["+budgetAmtAnal+"]:::::consumedAmtAnal::["+consumedAmtAnal+"]");
						if( budgetAmtAnal > consumedAmtAnal )
						{
							amountFlag = true;
						}
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
					
				}
				*/
				//if( amountFlag==true)
					{
					
						if("A".equalsIgnoreCase(flag))
						{
							flagStatus = "A";
						}
						else if("R".equalsIgnoreCase(flag))
						{
							System.out.println("@@@@@@ getRejectionRemark() Called !!! ");
							rejectionRemark = getRejectionRemark(conn,tranId,prcID, refSeries,flag);
							System.out.println("@@@@@@ rejectionRemark["+rejectionRemark+"]");
							flagStatus = "R - "+rejectionRemark;
						}	
					sql = " UPDATE misc_voucher  SET wf_status = ?  WHERE tran_id = ? ";				
					pStmt = conn.prepareStatement(sql);				
					pStmt.setString(1, flagStatus );
					pStmt.setString(2, tranId);		        
					int updateCnt = pStmt.executeUpdate();				
					if (updateCnt>0 )
					{
					conn.commit();
				
					pStmt.close();
					pStmt = null;
					System.out.println("ConfirmMISCVOUCHEREJB] : [confirm] : ==> misc_voucher is Updated. with row(s)"+updateCnt);
					}
				
				
				}

			}
	
	
			}
		catch (SQLException e)
		{
			try
			{

				conn.rollback();

			}
			catch (Exception e1)
			{
				System.out.println("Exception :[ConfirmMISCVOUCHEREJB] : [confirm]==>\n" + e1.getMessage());

			}

			System.out.println("Exception :[ConfirmMISCVOUCHEREJB] : [confirm]==>SQLException :==>\n" + e.getMessage());
			throw new ITMException(e);

		}
		catch (Exception e)
		{

			System.out.println("Exception :[ConfirmMISCVOUCHEREJB] : [confirm]==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch (Exception e)
			{
				System.out.println("Exception :[ConfirmMISCVOUCHEREJB] : [confirm]==>\n" + e.getMessage());
				throw new ITMException(e);
			}
		}
		return "";
	}
	
	
	private String getRejectionRemark(Connection conn, String refID, String processId, String refSeries,String flag) throws ITMException ,RemoteException
	{	
		PreparedStatement pstmtCheckStatus = null;
		ResultSet rsCheckStatus = null;
		String rejectionRemark = "";
		try
		{
			System.out.println("@@@@@ getRejectionRemark()===>>> refID["+refID+"]:::processId["+processId+"]:::refSeries["+refSeries+"]::::flag["+flag+"]");
			
			String getRejectionRemarkSql = "SELECT SIGN_REMARKS FROM OBJ_SIGN_TRANS " +
					  "WHERE REF_SER = ? AND REF_ID = ? AND LINE_NO = (SELECT MAX(LINE_NO) FROM OBJ_SIGN_TRANS "+
					  "WHERE REF_SER = ? AND REF_ID = ? AND SIGN_STATUS = ? ) "; 
			
			
			PreparedStatement pstmtGetRejRemark = conn.prepareStatement(getRejectionRemarkSql);
			pstmtGetRejRemark.setString(1,refSeries);
			pstmtGetRejRemark.setString(2,refID);
			pstmtGetRejRemark.setString(3,refSeries);
			pstmtGetRejRemark.setString(4,refID);
			pstmtGetRejRemark.setString(5,flag );
			ResultSet rsGetRejRemark = pstmtGetRejRemark.executeQuery();
			if(rsGetRejRemark.next())
			{
				rejectionRemark = rsGetRejRemark.getString("SIGN_REMARKS");
			}
			int updateCount = pstmtGetRejRemark.executeUpdate();
			rsGetRejRemark.close();
			rsGetRejRemark = null;
			pstmtGetRejRemark.close();
			pstmtGetRejRemark = null;
			System.out.println("@@@@ updateCount["+updateCount+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				if(rsCheckStatus != null)
				{
					rsCheckStatus.close();
					rsCheckStatus = null;
				}
				if(pstmtCheckStatus != null)
				{
					pstmtCheckStatus.close();
					pstmtCheckStatus = null;
				}
			}catch(Exception e)
			{
				System.out.println("Exception ::"+e.getMessage());
			}
		}
		System.out.println("rejectionRemark =["+rejectionRemark+"]");
		return rejectionRemark;
	}
	

	
}
