package ibase.webitm.ejb.fin;

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 java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.ejb.Stateless;
@Stateless
public class BudgetGrpAcctGenPrc extends ActionHandlerEJB implements BudgetGrpAcctGenPrcLocal, BudgetGrpAcctGenPrcRemote{

	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	
	public String actionHandler(String tranID, String xtraParams, String forcedFlag) throws RemoteException, ITMException{

		String budgetId = null, confirmed = null;
		String userId = null;
		String chgTerm = null, chgUser = null;
		String excpMsg="";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = null;
		String errString =  "";
		String resultStr = null;
		
		Connection conn = null;
		
		PreparedStatement psStmtCCode = null;
		ResultSet rsCCode = null;
		 
		System.out.println("\n ************** tranID ***************\n  "+tranID);
		System.out.println("\n ************** xtraParams ***************\n  "+xtraParams);
		
		try
		{
			 ConnDriver connDriver = new ConnDriver();
			 conn = connDriver.getConnectDB( "DriverITM" );
			 connDriver = null;
			 conn.setAutoCommit( false );
			
			 userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "logincode");
			 chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_term");
			 chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
			 
			 System.out.println("\n userId "+userId+"\t chgTerm : "+chgTerm+" chgUser : "+chgUser);
			 /*tranId = genericUtility.getColumnValue( "tran_id", headerDom);
			 budgetId = genericUtility.getColumnValue( "budget_id", headerDom);
			 confirmed = genericUtility.getColumnValue( "confirmed", headerDom);
			 
			 fromDateStr = genericUtility.getValidDateString( fromDateStr, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat() );*/
			 
			 sql = "select confirmed,budget_id from bud_grp_acct where tran_id= ?";
			 
			 pstmt = conn.prepareStatement(sql);
			 pstmt.setString(1,tranID);
			 rs = pstmt.executeQuery();
			 
			 if(rs.next()){
			 
				confirmed = rs.getString(1);
				budgetId =  rs.getString(2);
			 }
			 System.out.println("\n confirmed "+confirmed+"\t budgetId : "+budgetId);
			 
			 this.closeResources(pstmt, rs);
			 sql = null;
			 
			 if(confirmed!=null&&confirmed.equalsIgnoreCase("N")){
				
				 errString = itmDBAccessEJB.getErrorString("","VTBUDACCNC",chgUser);
				 return errString;
				 
			 }else if(confirmed!=null&&confirmed.equalsIgnoreCase("Y")){
				 
				 String budgetGrpCode = null;
				 Float budgetAmt = 0.00f;
				 sql= null;
				 sql = "select bgd.bud_group_code,bgd.budget_amt "+
						 " from bud_grp_acct bga, bud_grp_acctdet bgd "+
						 "where bgd.tran_id = ? " +
						 " and bga.budget_id = ? " +
						 "and bga.tran_id = bgd.tran_id ";
				 
				 System.out.println("\n sql1 :"+sql);
				 
				 pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,tranID);
				 pstmt.setString(2,budgetId);
				 rs = pstmt.executeQuery();
				 
				 if(rs.next()){
				 
					budgetGrpCode = rs.getString(1);
					budgetAmt =  rs.getFloat(2);
				 }
				 if(budgetGrpCode!=null)
					 budgetGrpCode = budgetGrpCode.trim();
				 
				 System.out.println("\n budgetGrpCode "+budgetGrpCode+"\t budgetAmt : "+budgetAmt);
				 
				 this.closeResources(pstmt, rs);
				 sql = null;
				 
				 //sql = "select acct_prd,code from period where sysdate between fr_date and to_date";
				 sql = "select acct_prd,prd_code,fin_entity,site_code,from_date,to_date from budgets where budget_id= ?";
				 
				 pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,budgetId);
				 rs = pstmt.executeQuery();
				 
				 String acctPrd = null, prdCode = null;
				 String finEntity = null, siteCode = null;
				 Date fromDate = null ,todate = null;
				 
				 if(rs.next()){
				
					acctPrd = rs.getString(1);
					prdCode = rs.getString(2);
					finEntity = rs.getString(3);
					siteCode =  rs.getString(4);
					fromDate = rs.getDate(5);
					todate = rs.getDate(6);
				 }
				 
				 System.out.println("\n acctPrd "+acctPrd+"  prdCode : "+prdCode+"  finEntity: "+finEntity+"  siteCode : "+siteCode+"  fromDate: "+fromDate+"  todate: "+todate);
				 
				 if(finEntity!=null)
					 finEntity = finEntity.trim();
				 
				 this.closeResources(pstmt, rs);
				 sql = null;
				 
				 //--------------------------------
				 
				 sql =" select count(*) from acctbudget "+ 
						 "where bud_group_code = ? "+
						 "and budget_id= ? "+
						 "and acct_prd= ? ";
				 
				 pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,budgetGrpCode);
				 pstmt.setString(2,budgetId);
				 pstmt.setString(3,acctPrd);
				 
				 rs = pstmt.executeQuery();
				 
				 int cnt = 0;
				 
				 if(rs.next())
				 {
					cnt = rs.getInt(1);
				 }
				 System.out.println("\n cnt "+cnt);
				 
				 this.closeResources(pstmt, rs);
				 sql = null;
				 
				 // if condition to check whether budget accounting already generated for selected budget id
				 
				 if(cnt>0){
					 
					 // Error : Account already generated for selected budget ID
					 errString = itmDBAccessEJB.getErrorString("","VTBUACEXST",chgUser);
					 return errString;
					 
				 }else if(cnt==0){		// Code will insert budget accounting details in acctbudget table	
					 
					 Calendar currentDate = Calendar.getInstance();
					 SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
					 String systemDate = simpleDateFormatObj.format(currentDate.getTime());
						
					 String deptCode = null,analCode = null,cctrCode = null, acctCode = null;
					 Float allocPerc =0.00f;
					 
					 sql = "select count(*) from bud_group_det where bud_group_code = ?";
					 pstmt = conn.prepareStatement(sql);
					 pstmt.setString(1,budgetGrpCode);
					 rs = pstmt.executeQuery();
					 
					 int rowcnt = 0;
					 
					 if(rs.next()){
						 rowcnt = rs.getInt(1);
					 }
					 
					 this.closeResources(pstmt, rs);
					 
					 if(rowcnt==0){
						 
						 errString = itmDBAccessEJB.getErrorString("","VTACCODENF",chgUser);
						 return errString;
						 
					 }else{
							 sql = "select dept_code, anal_code, cctr_code, alloc_perc, acct_code from bud_group_det where bud_group_code = ?";
							 
							 pstmt = conn.prepareStatement(sql);
							 pstmt.setString(1,budgetGrpCode);
							 rs = pstmt.executeQuery();
							 
							 Float final_budget_amt = 0.00f;
							 
							 
							 String sqlCurrCode = null;
							 String currCode = null;
							 while(rs.next()){
							 
								deptCode = rs.getString(1);
								analCode = rs.getString(2);
								cctrCode = rs.getString(3);
								allocPerc = rs.getFloat(4);
								acctCode = rs.getString(5);
								
								if(acctCode!=null)
									acctCode = acctCode.trim();
								
								sqlCurrCode = "select curr_code from accounts where acct_code= ?";
								psStmtCCode = conn.prepareStatement(sqlCurrCode);
								psStmtCCode.setString(1,acctCode);
								
								rsCCode = psStmtCCode.executeQuery();
								
								if(rsCCode.next()){
									currCode = rs.getString(1);
								}
								
								System.out.println("\n deptCode: "+deptCode+"  analCode : "+analCode+"  cctrCode: "+cctrCode+"  allocPerc : "+allocPerc+"  acctCode: "+acctCode+"  currCode: "+currCode);
								
								this.closeResources(psStmtCCode, rsCCode);
								
								final_budget_amt = (budgetAmt * allocPerc)/100;
								
								System.out.println("\n final_budget_amt = (budgetAmt * allocPerc)/100");
								System.out.println("\n final_budget_amt : "+final_budget_amt);
								
								String sqlInsert = "Insert into ACCTBUDGET "+
										 "(BUDGET_ID,FIN_ENTITY,ACCT_PRD,PRD_CODE,ACCT_CODE,CCTR_CODE,BUDGET_AMT,CON_AMT,"+
										 "FROM_DATE,TO_DATE,CHG_DATE,CHG_TERM,CHG_USER,SITE_CODE,ANAL_CODE,CURR_CODE,"+
										 "DEPT_CODE,BUD_GROUP_CODE) "+
										 " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
								
								PreparedStatement pstmtInsert = conn.prepareStatement(sqlInsert);
								
								pstmtInsert.setString(1,budgetId);
								pstmtInsert.setString(2,finEntity);
								pstmtInsert.setString(3,acctPrd);
								pstmtInsert.setString(4,prdCode);
								pstmtInsert.setString(5,acctCode);
								pstmtInsert.setString(6,cctrCode);
								pstmtInsert.setFloat(7,final_budget_amt);
								pstmtInsert.setFloat(8,0);
								pstmtInsert.setDate(9,(java.sql.Date) fromDate);
								pstmtInsert.setDate(10,(java.sql.Date) todate);
								pstmtInsert.setString(11,systemDate);
								pstmtInsert.setString(12,chgTerm);
								pstmtInsert.setString(13,chgUser);
								pstmtInsert.setString(14,siteCode);
								pstmtInsert.setString(15,analCode);
								pstmtInsert.setString(16,currCode);
								/*pstmt.setFloat(17,null);
								pstmt.setString(18,null);
								pstmt.setString(19,null);
								pstmt.setString(20,null);*/
								pstmtInsert.setString(17,deptCode);
								//pstmt.setString(22,null);
								pstmtInsert.setString(18,budgetGrpCode);
								
								System.out.println("sqlInsert ------>>: "+sqlInsert);
								conn.setAutoCommit(false);
								
								int count = pstmtInsert.executeUpdate();
								if(count > 0)					
									conn.commit();						
								
								System.out.println(count + " row(s) inserted in ACCTBUDGET table.");
								sqlInsert = null;
								
								errString = itmDBAccessEJB.getErrorString("","VTBUDACSUC",chgUser);
								return errString;
								
							 }
					  }				// End of else
				 }
			 }
             
		}
		catch(ITMException itme)
		{
		   System.out.println("returning in itme ::  "+errString);
		   System.out.println("ITMException :BudgetGrpAcctGenPrc :actionHandler() method :" + itme.getMessage() + ":");
		  // errString = "ERROR";
		   //excpMsg = itme.getMessage();
		   //System.out.println("Exception in...."+e.getMessage());
		   itme.printStackTrace();
		}
	   	catch(Exception e)
		{
		   System.out.println("returning in exp ::  "+errString);
		   System.out.println("Exception :BudgetGrpAcctGenPrc :actionHandler() method : " + e.getMessage() + ":");
		   //errString = "ERROR";
		   //excpMsg = e.getMessage();
		   //System.out.println("Exception in...."+e.getMessage());
		   e.printStackTrace();
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rsCCode!=null){
					
					rsCCode.close();
					rsCCode = null;
				}
				if(psStmtCCode!=null){
					
					psStmtCCode.close();
					psStmtCCode = null;
				}
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
			   //System.out.println("Error In closing connection::==> "+e);
			   //excpMsg = e.getMessage();
		       e.printStackTrace();
			}
		}
		
		
		//System.out.println("returning from     "+retString);
	    return (errString);
	
	
	}
	
	/* @ SANDESH R SURVE				Created Date- 08-May-2013
	 * 
	 Closes passed PreparedStatement, ResultSet object
	 
	 */
	
	private void closeResources(PreparedStatement pstmt,ResultSet rs) throws SQLException{
		
		if(pstmt!=null){
			pstmt.close();
			pstmt = null;
		}
		if(rs != null)
		{
			rs.close();
			rs = null;
		}
	}
	
}
