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;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = null;
		String errString =  "";
		
		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);
			 			 
			 //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 = "N";		// Code will assume confirmed as N if confirmed value read is null
				 
			 }
			 else 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);
				 
				 /*StringBuffer prefix = new StringBuffer();
				 int zeros = 10 - budgetGrpCode.length();
				 
				 for(int j=1;j<=zeros;j++){
					 prefix.append("0");
				 }
				 
				 budgetGrpCode = prefix.toString()+budgetGrpCode;*/
				 
				 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(acctPrd==null||prdCode==null||finEntity==null||siteCode==null||fromDate==null||todate==null){
					 
					 //Error : Required data not found from budgets
					 errString = itmDBAccessEJB.getErrorString("","VTBUDINFNF",chgUser);
					 return errString;
					 
				 }
				 
				 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;
				 
					 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{
						 
						 // Code to check allocation percentage for a budget group code is 100 or not. If it is other than 100 system will give error
					 	
					 	 sql = "select sum(alloc_perc) from bud_group_det where bud_group_code = ?";
					 	 pstmt = conn.prepareStatement(sql);
						 pstmt.setString(1,budgetGrpCode);
						 rs = pstmt.executeQuery();
						
						 int sumAllocPerc = 0;
						 
						 if(rs.next()){
							 
							 sumAllocPerc = rs.getInt(1);
						 }
						 
						 if(sumAllocPerc!=100){

							//Error : Allocation percentage for budget group code is not 100
							errString = itmDBAccessEJB.getErrorString("","VTACPERERR",chgUser);
							return errString;
							 
						 }
						 this.closeResources(pstmt, rs);
						 
						 
						 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;
						 
						 conn.setAutoCommit(false);
						 
						 int cntExist = 0;
						 
						 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();
							
							if(deptCode!=null)
								deptCode = deptCode.trim();
							
							if(deptCode==null||analCode==null||cctrCode==null||acctCode==null){
								 
								//Error : Required data not found from budget group details
								errString = itmDBAccessEJB.getErrorString("","VTBUDGRPNF",chgUser);
								return errString;
								 
							 }
							
							sqlCurrCode = "select curr_code from accounts where acct_code= ?";
							psStmtCCode = conn.prepareStatement(sqlCurrCode);
							psStmtCCode.setString(1,acctCode);
							
							rsCCode = psStmtCCode.executeQuery();
							
							if(rsCCode.next()){
								currCode = rsCCode.getString(1);
							}
							if(currCode!=null)
								currCode = currCode.trim();
							
							System.out.println("\n deptCode: "+deptCode+"  analCode : "+analCode+"  cctrCode: "+cctrCode+"  allocPerc : "+allocPerc+"  acctCode: "+acctCode+"  currCode: "+currCode);
							
							this.closeResources(psStmtCCode, rsCCode);
							
							/*
							 *    Code to check budget account already generated for given budget group code, budget_id,
							 *    fin_entity,acct_code,cctr_code,anal_code,from_date,dept_code
							 */
							
							this.closeResources(pstmt, rs);
							
							 sql =" select count(*) from acctbudget "+ 
									 "where bud_group_code = ? "+
									 "and budget_id = ? "+
									 "and fin_entity = ? "+
							 		 "and acct_code = ? "+
							 		 "and cctr_code = ? "+
							 		 "and anal_code = ? "+
							 		 "and from_date= ? "+
							 		 "and dept_code = ? ";
							 
							 pstmt = conn.prepareStatement(sql);
							 pstmt.setString(1,budgetGrpCode);
							 pstmt.setString(2,budgetId);
							 pstmt.setString(3,finEntity);
							 pstmt.setString(4,acctCode);
							 pstmt.setString(5,cctrCode);
							 pstmt.setString(6,analCode);
							 pstmt.setDate(7,(java.sql.Date)fromDate);
							 pstmt.setString(8,deptCode);

							 rs = pstmt.executeQuery();
							 
							 if(rs.next())
							 {
								 cntExist = rs.getInt(1);
							 }
							 System.out.println("\n cntExist acct budget : "+cntExist);
							 
							 
							 this.closeResources(pstmt, rs);
							 sql = null;
							 
							 if(cntExist>0){
								 
								 // Error : Account already generated for selected budget group code, budget_id,
								 //         fin_entity,acct_code,cctr_code,anal_code,from_date,dept_code
								 
								 errString = itmDBAccessEJB.getErrorString("","VTBUACEXST",chgUser);
								 return errString;
								 
							 }
							
							//---------------------------------------------------------------------------
							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);
							pstmtInsert.setString(17,deptCode);
							pstmtInsert.setString(18,budgetGrpCode);
							
							System.out.println("sqlInsert ------>>: "+sqlInsert);
							
							int rowInserted = pstmtInsert.executeUpdate();
							/*if(rowInserted > 0)					
								conn.commit();	*/					
							
							System.out.println(rowInserted + " row(s) inserted in ACCTBUDGET table.");
							sqlInsert = null;
							
							if(pstmtInsert!=null){
								pstmtInsert.close();
								pstmtInsert=null;
							}
							
						 }			// while loop end
						 conn.commit();
						 System.out.println("Committed...");
						 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)
			{
		       e.printStackTrace();
			}
		}
		
	    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;
		}
	}
	
}
