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 MiscVouchChkAmtEJB extends ActionHandlerEJB implements MiscVouchChkAmtEJBLocal, MiscVouchChkAmtEJBRemote{
//MISCVOUCHERWRKFLW
	public MiscVouchChkAmtEJB()
	{
		super();

	}

	public String confirm(String tranId, String route_code, String operation, 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, route_code , operation   , prfmrCode , roleCodeAprv , xmlDataAll , prcID );
		}
		catch (Exception e)
		{
			System.out.println("Exception :MiscVouchChkAmtEJB :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 route_code ,String operation , String prfmrCode , String rolecodeAprv , String xmlDataAll ,String prcID ) throws RemoteException, ITMException
	{
		PreparedStatement  pStmt = null,pStmt1 = null;
		ResultSet rs = null,rs1 = null;
		String  sql = "",sql1 = "";		
		String  confirmed = "";		
	    //String  roleCode = "";		
	    //java.sql.Date actionDateAprv1 = null;
		int cnt=0,i=0;
		double budgetAmtAnal = 0.0,consumedAmtAnal=0.0,amount=0.0;
		String amountFlag = "false";
		String siteCode="",acctCode="",cctrCode="",analCode="",deptCode="";
		try
		{

			System.out.println("tranId-->["+tranId+"],prfmrCode-->["+prfmrCode+"], rolecodeAprv-->["+rolecodeAprv+"],prcID-->["+prcID+"],operation-->["+operation+"]");			
			
			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,amount  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();
					while (rs.next())
					{
						acctCode = rs.getString(1);
						cctrCode = rs.getString(2);
						analCode = rs.getString(3);
						deptCode = rs.getString(4);
						amount = rs.getDouble(5);
					
					// for amt chk
						
						//CollableStatement cstmt = null; 
						
						sql1 = " select FN_GET_BUDGET_AMT('VOUCH',?,?,?,?,?,'A') BUDGET_AMT_ANAL , " +
								  " FN_GET_CONS_AMT('VOUCH',?,?,?,?,?,'A') CONSUMED_AMT_ANAL from dual";
							pStmt1 = conn.prepareStatement(sql1);
							pStmt1.setString( 1, siteCode );
							pStmt1.setString( 2, acctCode );
							pStmt1.setString( 3, cctrCode );
							pStmt1.setString( 4, analCode );
							pStmt1.setString( 5, deptCode );
							pStmt1.setString( 6, siteCode );
							pStmt1.setString( 7, acctCode );
							pStmt1.setString( 8, cctrCode );
							pStmt1.setString( 9, analCode );
							pStmt1.setString( 10, deptCode );
							
							rs1 = pStmt1.executeQuery();
							if (rs1.next())
							{
								budgetAmtAnal = rs1.getDouble(1);
								consumedAmtAnal = rs1.getDouble(2);
								
								System.out.println("i:["+i+"]:::budgetAmtAnal::["+budgetAmtAnal+"]:::::consumedAmtAnal::["+consumedAmtAnal+"]:::amount["+amount+"]");
								if(( consumedAmtAnal-budgetAmtAnal ) < amount )
								{
									amountFlag = "true";
								}
							}
							rs1.close();
							rs1 = null;
							pStmt1.close();
							pStmt1 = null;
					
					
					//
					
					}
					rs.close();
					rs = null;
					pStmt.close();
					pStmt = null;
					
					
					
					
				}
				
				

			}
	
	
			}
		catch (SQLException e)
		{
			try
			{

				conn.rollback();

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

			}

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

		}
		catch (Exception e)
		{

			System.out.println("Exception :[MiscVouchChkAmtEJB] : [confirm]==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch (Exception e)
			{
				System.out.println("Exception :[MiscVouchChkAmtEJB] : [confirm]==>\n" + e.getMessage());
				throw new ITMException(e);
			}
		}
		return amountFlag;
	}

	
}
