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.sql.Timestamp;
import java.text.SimpleDateFormat;
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, loginSiteCode = null, siteCode = null;
		
		PreparedStatement pstmtGrp =null, pstmt = null;
		ResultSet rsGrp =null, rs = null;
		
		String sql = null;
		String errString =  "";
		
		Connection conn = null;
		
		PreparedStatement psStmtCCode = null;
		ResultSet rsCCode = null;
		
		PreparedStatement psStatement =null;
		ResultSet rsData = 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;
			
			 userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "logincode");
			 chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_term");
			 chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
			 loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			 
			 System.out.println("\n userId "+userId+"\t chgTerm : "+chgTerm+" chgUser : "+chgUser+" loginSiteCode : "+loginSiteCode);
			 			 
			 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;

				// Getting site code against loginSiteCode from XTRA PARAMS
				 
				 sql = "select site_code__budget from site where site_code = ?";
				 
				 pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,loginSiteCode);
				 rs = pstmt.executeQuery();
				 
				 if(rs.next()){
				 
					siteCode = rs.getString(1);
				 }
				 System.out.println("\n siteCode : "+siteCode);
				 
				 this.closeResources(pstmt, rs);
				 sql = null;
				 
				 if(siteCode==null){
					 
					 errString = itmDBAccessEJB.getErrorString("","VTSITCODER",chgUser);
					 return errString;
					 
				 }
				 
				 sql = "select budget_id from budgets where site_code = ?";
				 
				 pstmt = conn.prepareStatement(sql);
				 pstmt.setString(1,siteCode);
				 rs = pstmt.executeQuery();
				 
				 String siteBudgetId = null;
				 
				 if(rs.next()){
					 siteBudgetId =  rs.getString(1);
				 }
				 
				 if(siteBudgetId!=null&&siteBudgetId.equals(budgetId)==false){

					 errString = itmDBAccessEJB.getErrorString("","VTBUDSITNF",chgUser);
					 return errString;
				 }
				 
				 this.closeResources(pstmt, rs);
				 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 sql => "+sql);
				 
				 pstmtGrp = conn.prepareStatement(sql);
				 pstmtGrp.setString(1,tranID);
				 pstmtGrp.setString(2,budgetId);
				 rsGrp = pstmtGrp.executeQuery();
				 
				 conn.setAutoCommit(false);
				 
				 while(rsGrp.next()){				// This loop will iterate through multiple budget group codes for a budget id 
				 
					 budgetGrpCode = rsGrp.getString(1);
					 budgetAmt =  rsGrp.getFloat(2);
				 
					 if(budgetGrpCode!=null)
						 budgetGrpCode = budgetGrpCode.trim();
					 
					 System.out.println("\n budgetGrpCode "+budgetGrpCode+"\t budgetAmt : "+budgetAmt);
					 
					 sql = null;
					 
					/*
					 *    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
					 */
					
					 int cntExist = 0;
					
					 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,
						 
						 System.out.println("\n  Error : Account already generated for selected budget group code, budget_id, ");
								 
						 errString = itmDBAccessEJB.getErrorString("","VTBUACEXST",chgUser);
						 return errString;
						 
					 }
					
					//---------------------------------------------------------------------------				 
					 
					 sql = "select acct_prd,prd_code,fin_entity,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;
					 Date fromDate = null ,todate = null;
					 
					 if(rs.next()){
					
						acctPrd = rs.getString(1);
						prdCode = rs.getString(2);
						finEntity = rs.getString(3);
						fromDate = rs.getDate(4);
						todate = rs.getDate(5);
					 }
					 
					 System.out.println("\n acctPrd "+acctPrd+"  prdCode : "+prdCode+"  finEntity: "+finEntity+"  fromDate: "+fromDate+"  todate: "+todate);
					 
					 if(acctPrd==null||prdCode==null||finEntity==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;
					 
						 /*Calendar currentDate = Calendar.getInstance();
						 SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
						 String systemDate = simpleDateFormatObj.format(currentDate.getTime());*/
						 
						 java.util.Date currentDate = new java.util.Date();
							SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
							//String sysDate = sdf.format(currentDate.getTime());
							Timestamp newsysDate = Timestamp.valueOf( sdf.format(currentDate)+" 00:00:00.0");
							System.out.println("Now the date is :=>  ["+newsysDate+"]");
							
						 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 = ?";
							 
							 psStatement = conn.prepareStatement(sql);
							 psStatement.setString(1,budgetGrpCode);
							 
							 rsData = psStatement.executeQuery();
							 
							 Float final_budget_amt = 0.00f;
							 
							 String sqlCurrCode = null;
							 String currCode = null;
							 
							 while(rsData.next()){  // This loop will iterate through multiple account codes for a budget group code in current iteration
							 
								deptCode = rsData.getString(1);
								analCode = rsData.getString(2);
								cctrCode = rsData.getString(3);
								allocPerc = rsData.getFloat(4);
								acctCode = rsData.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);
	
								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.setTimestamp(11,newsysDate);
								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();
												
								
								System.out.println(rowInserted + " row(s) inserted in ACCTBUDGET table for budgetId : "+budgetId+" and budgetGrpCode : "+budgetGrpCode);
								sqlInsert = null;
								
								if(pstmtInsert!=null){
									pstmtInsert.close();
									pstmtInsert=null;
								}
								
							 }			// inner while loop end
							 
						  }				// End of else
				 }	// End of outer while loop
				 
				 conn.commit();
				 System.out.println("Committed...");
				 
				 errString = itmDBAccessEJB.getErrorString("","VTBUDACSUC",chgUser);
				 return errString;
			 }
             
		}
		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(rsGrp!=null){
					rsGrp.close();
					rsGrp = null;
				}
				if (pstmtGrp != null)
				{
					pstmtGrp.close();
					pstmtGrp = null;
				}		
				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(rsData!=null){
					rsData.close();
					rsData=null;
				}
				if(psStatement!=null){
					psStatement.close();
					psStatement=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;
		}
	}
	
}
