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 java.util.Date;

import javax.ejb.Stateless;
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;

@Stateless
public class BudgetGroupAlloAmdConfirm extends ActionHandlerEJB implements BudgetGroupAlloAmdConfirmLocal,BudgetGroupAlloAmdConfirmRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	
	String chgUser =null, chgTerm = null, userId =  null;
	String errString="";
	 
	public String actionHandler(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";		
		try
		{						
			
			 userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "logincode");
			 chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_term");
			 chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
			retString = confirmAmmend(tranId,xtraParams,forcedFlag);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception :BudgetGroupAcctAmdConfirm :actionHandler():" + e.getMessage() + ":");
			retString = GenericUtility.getInstance().createErrorString(e);
			e.printStackTrace();
		}
		
		return retString;
	}
	
	
	private String confirmAmmend(String tranIdAmd,String xtraParams,String forcedFlag) 
	{

		System.out.println("--------------confirm method of BudgetGroupAcctAmdConfirm ------------- ");
		Connection conn=null;
		ConnDriver ConnDriver = new ConnDriver();
		 ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		 ResultSet rs = null;
		PreparedStatement pstmt = null;	
		
		String sql = "",confirmed="";
				
		try
		{	
			 conn = ConnDriver.getConnectDB("DriverITM");
			 conn.setAutoCommit(false);				
			sql = "select confirmed from budgrp_allo_amd where amd_no = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranIdAmd);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed =rs.getString(1)==null ? "":rs.getString(1);
			}	
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			System.out.println("Confirmed ----- : "+confirmed);
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				errString = itmdbAccess.getErrorString("", "VTALLCONF", "", "", conn);
				return errString;
			}
			else
			{
				errString =updateBudgetAmmend(conn,tranIdAmd,xtraParams);			
				
				return errString;
				
			}
				
		}
		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 ::"+errString);
		return errString;
		
	}
	
	
	
	
	private String updateBudgetAmmend(Connection conn,String tranIdAmd,String xtraParams)
	{
		
	
		System.out.println("---------in updateConfirm method------------");
		String loginEmpCode ="",sql="",budGrpCode="",budgetId="",tran_id="";
		double budAmount=0.0;
		PreparedStatement pstmt1=null;
		ResultSet rs1=null;
		int count=0,countAcctdet=0;
		PreparedStatement pstmtBudGrp = null;
		ResultSet rsBudgrp = null;
		try
		{	
			sql="select budget_id from budgrp_allo_amd where amd_no = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, tranIdAmd);
			rs1 = pstmt1.executeQuery();
			if( rs1.next() )
			{
				budgetId =rs1.getString(1)==null ? "":rs1.getString(1);
			}	
			System.out.println("In updateBudgetAmmend : budget_id : "+budgetId);
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;
			
			/*sql="select tran_id from bud_grp_allo where budget_id = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, budgetId);
			rs1 = pstmt1.executeQuery();
			while( rs1.next() )
			{
				tran_id =rs1.getString(1)==null ? "":rs1.getString(1);
			}	
			System.out.println("In updateBudgetAmmend : tran_id : "+tran_id);
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;*/
			
			conn.setAutoCommit(false);
			
			sql="select bud_group_code,budget_amt from budgrp_allodet_amd where amd_no = ? ";
			pstmtBudGrp = conn.prepareStatement(sql);
			pstmtBudGrp.setString(1, tranIdAmd);
			rsBudgrp = pstmtBudGrp.executeQuery();
			
			while( rsBudgrp.next() )
			{
				budGrpCode =rsBudgrp.getString(1)==null ? "":rsBudgrp.getString(1);
				budAmount=rsBudgrp.getDouble(2);
			
				System.out.println("In updateBudgetAmmend : budGrpCode : "+budgetId+" budAmount : "+budAmount);
				
				System.out.println("new query addedaa");
			    sql="select acct.tran_id from  bud_grp_allo acct, bud_grp_allodet det " +
			    		" where acct.tran_id = det.tran_id and det.bud_group_code = ? " +
			    		" and  acct.budget_id = ?";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, budGrpCode);
				pstmt1.setString(2, budgetId);
				rs1 = pstmt1.executeQuery();
				if( rs1.next() )
				{
					tran_id =rs1.getString(1)==null ? "":rs1.getString(1);
				}	
				System.out.println("In updateBudgetAmmend : budget_id : "+budgetId);
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				
				
				//--------------------------------------------------------------------------------			
				
				sql=null;
				sql="update bud_grp_allodet set budget_amt = ? where tran_id = ? and bud_group_code = ?";
				
				pstmt1 = conn.prepareStatement( sql );	
				pstmt1.setDouble(1, budAmount); 
				pstmt1.setString(2, tran_id);
				pstmt1.setString(3, budGrpCode);
				
				
				
				
				countAcctdet = pstmt1.executeUpdate();
				
				
				System.out.println("----countAcctdet : "+countAcctdet);
					
				
				sql="update budgrp_allo_amd set confirmed = ?,conf_date = ?,emp_code__aprv = ? where amd_no = ?";
			
				loginEmpCode=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
				pstmt1 = conn.prepareStatement( sql );	
				pstmt1.setString(1, "Y");
				pstmt1.setTimestamp(2, this.getCurrdateAppFormat()); 
				pstmt1.setString(3, loginEmpCode);
				pstmt1.setString(4, tranIdAmd);
				
				count = pstmt1.executeUpdate();
				
				System.out.println("confirm updated -------->>>>>>>>>updCount :"+count);	
				
				//  Added by deepak
				String finEntity1 = null;
				Double final_budget_amt = 0.00;
				Float allocperc1 = 0.00f;
				Date fromDate = null ;
				sql = "select fin_entity,from_date from budgets where budget_id= ?";
				
				PreparedStatement pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,budgetId);
				ResultSet rs = pstmt.executeQuery();
				 
				finEntity1 = null;
				 fromDate = null;
				 
				 if(rs.next()){
					 
				    finEntity1 = rs.getString(1);
					fromDate = rs.getDate(2);
				 }
				
				 if(finEntity1==null||fromDate==null){
					 
					 //Error : Required data not found from budgets
					 errString = itmDBAccessEJB.getErrorString("","VTBUDINFNF",chgUser);
					 return errString;
					 
				 }
				    
					String acctcode1="",cctrcode1="",analcode1="",deptcode1="";
					
				String sqlQuery="select acct_code, cctr_code, anal_code, dept_code,alloc_perc from bud_group_det where bud_group_code = ?";
				PreparedStatement	Mysql1pstmt = conn.prepareStatement(sqlQuery);
					Mysql1pstmt.setString(1, budGrpCode);
				ResultSet Mysql1rs1 = Mysql1pstmt.executeQuery();
				
				
					while( Mysql1rs1.next() )
					{
						
						acctcode1 =Mysql1rs1.getString(1)==null ? "":Mysql1rs1.getString(1);
						cctrcode1 =Mysql1rs1.getString(2)==null ? "":Mysql1rs1.getString(2);
						analcode1 =Mysql1rs1.getString(3)==null ? "":Mysql1rs1.getString(3);
						deptcode1 =Mysql1rs1.getString(4)==null ? "":Mysql1rs1.getString(4);
						allocperc1 = Mysql1rs1.getFloat(5);
						
						if(deptcode1==null||analcode1==null||cctrcode1==null||acctcode1==null){
							 
							errString = itmDBAccessEJB.getErrorString("","VTBUDGRPNF",chgUser);
							return errString;
						
						}
						System.out.println("ghrttt");
						final_budget_amt = (budAmount * allocperc1)/100;
						
						PreparedStatement Mysqlpstmt;
						String Mysql=null;
						Mysql="update acctbudget set budget_amt = ? where fin_entity= ? and acct_code= ? and cctr_code = ? and anal_code = ? and dept_code = ? and from_date = ? and budget_id = ? and bud_group_code = ?";
						
						Mysqlpstmt = conn.prepareStatement(Mysql);	
						
						Mysqlpstmt.setDouble(1, final_budget_amt); 
						Mysqlpstmt.setString(2, finEntity1);
						Mysqlpstmt.setString(3, acctcode1);
						Mysqlpstmt.setString(4, cctrcode1);
						Mysqlpstmt.setString(5, analcode1);
						Mysqlpstmt.setString(6, deptcode1);
						Mysqlpstmt.setDate(7,(java.sql.Date)fromDate);
						Mysqlpstmt.setString(8, budgetId);
						Mysqlpstmt.setString(9, budGrpCode);
						
						
					
					int	MysqlcountAcctdet = Mysqlpstmt.executeUpdate();
						
						Mysqlpstmt.close();
						Mysqlpstmt = null;	
						
						System.out.println("----MysqlcountAcctdet : "+MysqlcountAcctdet);
						
					}	
					System.out.println(acctcode1+""+cctrcode1+""+analcode1+""+deptcode1+""+allocperc1);
					
					Mysql1rs1.close();
					Mysql1rs1=null;
					Mysql1pstmt.close();
					Mysql1pstmt=null;	
			}		// End of outer while			
				conn.commit();
				
				errString=new ITMDBAccessEJB().getErrorString("", "VTCONSUCC", "", "", conn);
				
				
				
				
				return errString;
				
		}
		
		catch(SQLException se)
		{
			se.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
		}
		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();
			
		}
		
		return errString;
	}
	
	

	public String confirmBudAmdWKFL(String tranIdAmd,String xtraParams, String forcedFlag)   // added by cpatil
	{
		
	
		System.out.println("---------in updateBudgetAmmendWKFL method------------");
		String loginEmpCode ="",sql="",budGrpCode="",budgetId="",tran_id="",confirmed="",reportTo="";
		double budAmount=0.0;
		PreparedStatement pstmt1=null;
		ResultSet rs1=null;
		int count=0,countAcctdet=0;
		PreparedStatement pstmtBudGrp = null;
		ResultSet rsBudgrp = null;
		 ITMDBAccessEJB itmdbAccess=new ITMDBAccessEJB();
		 Connection conn=null;
			ConnDriver ConnDriver = new ConnDriver();
			
		try
		{	
			conn = ConnDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);	
			sql = "select confirmed from budgrp_allo_amd where amd_no = ?";
			
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, tranIdAmd);
			rs1 = pstmt1.executeQuery();
			if( rs1.next() )
			{
				confirmed =rs1.getString(1)==null ? "":rs1.getString(1);
			}	
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;
			System.out.println("Confirmed ----- : "+confirmed);
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				errString = itmdbAccess.getErrorString("", "VTALLCONF", "", "", conn);
				return errString;
			}
			else
			{
				
			sql="select budget_id from budgrp_allo_amd where amd_no = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, tranIdAmd);
			rs1 = pstmt1.executeQuery();
			if( rs1.next() )
			{
				budgetId =rs1.getString(1)==null ? "":rs1.getString(1);
			}	
			System.out.println("In updateBudgetAmmend : budget_id : "+budgetId);
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;
			
			/*sql="select tran_id from bud_grp_allo where budget_id = ? ";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, budgetId);
			rs1 = pstmt1.executeQuery();
			while( rs1.next() )
			{
				tran_id =rs1.getString(1)==null ? "":rs1.getString(1);
			}	
			System.out.println("In updateBudgetAmmend : tran_id : "+tran_id);
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;*/
			
			conn.setAutoCommit(false);
			
			sql="select bud_group_code,budget_amt from budgrp_allodet_amd where amd_no = ? ";
			pstmtBudGrp = conn.prepareStatement(sql);
			pstmtBudGrp.setString(1, tranIdAmd);
			rsBudgrp = pstmtBudGrp.executeQuery();
			
			while( rsBudgrp.next() )
			{
				budGrpCode =rsBudgrp.getString(1)==null ? "":rsBudgrp.getString(1);
				budAmount=rsBudgrp.getDouble(2);
			
				System.out.println("In updateBudgetAmmend : budGrpCode : "+budgetId+" budAmount : "+budAmount);
				
				System.out.println("new query addedaa");
			    sql="select acct.tran_id from  bud_grp_allo acct, bud_grp_allodet det " +
			    		" where acct.tran_id = det.tran_id and det.bud_group_code = ? " +
			    		" and  acct.budget_id = ?";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, budGrpCode);
				pstmt1.setString(2, budgetId);
				rs1 = pstmt1.executeQuery();
				if( rs1.next() )
				{
					tran_id =rs1.getString(1)==null ? "":rs1.getString(1);
				}	
				System.out.println("In updateBudgetAmmend : budget_id : "+budgetId);
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				
				
				//--------------------------------------------------------------------------------			
				
				sql=null;
				sql="update bud_grp_allodet set budget_amt = ? where tran_id = ? and bud_group_code = ?";
				pstmt1 = conn.prepareStatement( sql );	
				pstmt1.setDouble(1, budAmount); 
				pstmt1.setString(2, tran_id);
				pstmt1.setString(3, budGrpCode);
				countAcctdet = pstmt1.executeUpdate();
				System.out.println("----countAcctdet : "+countAcctdet);
					
				loginEmpCode=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
				 sql="select report_to from  employee where emp_code = ? " ;
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, loginEmpCode);
					rs1 = pstmt1.executeQuery();
					if( rs1.next() )
					{
						reportTo =rs1.getString(1)==null ? "":rs1.getString(1);
					}	
					System.out.println("@@@@@@In updateBudgetAmmend loginEmpCode:["+loginEmpCode+"]::::reportTo : ["+reportTo+"]");
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;
				
				sql="update budgrp_allo_amd set confirmed = ?,conf_date = ?,emp_code__aprv = ? where amd_no = ?";
				
				pstmt1 = conn.prepareStatement( sql );	
				pstmt1.setString(1, "Y");
				pstmt1.setTimestamp(2, this.getCurrdateAppFormat()); 
				pstmt1.setString(3, reportTo);
				pstmt1.setString(4, tranIdAmd);
				
				count = pstmt1.executeUpdate();
				
				System.out.println("confirm updated -------->>>>>>>>>updCount :"+count);	
				
				//  Added by deepak
				String finEntity1 = null;
				Double final_budget_amt = 0.00;
				Float allocperc1 = 0.00f;
				Date fromDate = null ;
				sql = "select fin_entity,from_date from budgets where budget_id= ?";
				
				PreparedStatement pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,budgetId);
				ResultSet rs = pstmt.executeQuery();
				 
				finEntity1 = null;
				 fromDate = null;
				 
				 if(rs.next()){
					 
				    finEntity1 = rs.getString(1);
					fromDate = rs.getDate(2);
				 }
				
				 if(finEntity1==null||fromDate==null){
					 
					 //Error : Required data not found from budgets
					 errString = itmDBAccessEJB.getErrorString("","VTBUDINFNF",chgUser);
					 return errString;
					 
				 }
				    
					String acctcode1="",cctrcode1="",analcode1="",deptcode1="";
					
				String sqlQuery="select acct_code, cctr_code, anal_code, dept_code,alloc_perc from bud_group_det where bud_group_code = ?";
				PreparedStatement	Mysql1pstmt = conn.prepareStatement(sqlQuery);
					Mysql1pstmt.setString(1, budGrpCode);
				ResultSet Mysql1rs1 = Mysql1pstmt.executeQuery();
				
				
					while( Mysql1rs1.next() )
					{
						
						acctcode1 =Mysql1rs1.getString(1)==null ? "":Mysql1rs1.getString(1);
						cctrcode1 =Mysql1rs1.getString(2)==null ? "":Mysql1rs1.getString(2);
						analcode1 =Mysql1rs1.getString(3)==null ? "":Mysql1rs1.getString(3);
						deptcode1 =Mysql1rs1.getString(4)==null ? "":Mysql1rs1.getString(4);
						allocperc1 = Mysql1rs1.getFloat(5);
						
						if(deptcode1==null||analcode1==null||cctrcode1==null||acctcode1==null){
							 
							errString = itmDBAccessEJB.getErrorString("","VTBUDGRPNF",chgUser);
							return errString;
						
						}
						System.out.println("ghrttt");
						final_budget_amt = (budAmount * allocperc1)/100;
						
						PreparedStatement Mysqlpstmt;
						String Mysql=null;
						Mysql="update acctbudget set budget_amt = ? where fin_entity= ? and acct_code= ? and cctr_code = ? and anal_code = ? and dept_code = ? and from_date = ? and budget_id = ? and bud_group_code = ?";
						
						Mysqlpstmt = conn.prepareStatement(Mysql);	
						
						Mysqlpstmt.setDouble(1, final_budget_amt); 
						Mysqlpstmt.setString(2, finEntity1);
						Mysqlpstmt.setString(3, acctcode1);
						Mysqlpstmt.setString(4, cctrcode1);
						Mysqlpstmt.setString(5, analcode1);
						Mysqlpstmt.setString(6, deptcode1);
						Mysqlpstmt.setDate(7,(java.sql.Date)fromDate);
						Mysqlpstmt.setString(8, budgetId);
						Mysqlpstmt.setString(9, budGrpCode);
						
						
					
					int	MysqlcountAcctdet = Mysqlpstmt.executeUpdate();
						
						Mysqlpstmt.close();
						Mysqlpstmt = null;	
						
						System.out.println("----MysqlcountAcctdet : "+MysqlcountAcctdet);
						
					}	
					System.out.println(acctcode1+""+cctrcode1+""+analcode1+""+deptcode1+""+allocperc1);
					
					Mysql1rs1.close();
					Mysql1rs1=null;
					Mysql1pstmt.close();
					Mysql1pstmt=null;	
				}		// End of outer while			
				conn.commit();
				
				errString=new ITMDBAccessEJB().getErrorString("", "VTCONSUCC", "", "", conn);
				return errString;
				
		}
				
		}
		
		catch(SQLException se)
		{
			se.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
		}
		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();
			
		}
		
		return errString;
	}
	
	private java.sql.Timestamp getCurrdateAppFormat()
    {
        GenericUtility genericUtility = GenericUtility.getInstance();
		Timestamp timestamp = null;
        try
        {
            java.util.Date date = null;
            timestamp = new Timestamp(System.currentTimeMillis());            
            SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
            date = simpledateformat.parse(timestamp.toString());
            timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
        }
        catch(Exception exception)
        {
            System.out.println("Exception in [BudgetGroupAcctAmdConfirm] getCurrdateAppFormat " + exception.getMessage());
            exception.printStackTrace();
        }
        return timestamp;
    }

}
