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.text.SimpleDateFormat;
import java.util.Calendar;
import javax.ejb.Stateless;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

@Stateless
public class BudgetGroupAcctAmd extends ValidatorEJB implements BudgetGroupAcctAmdLocal,BudgetGroupAcctAmdRemote
{

	GenericUtility genericUtility = GenericUtility.getInstance(); 
	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------ BudgetGroupAcctAmd wfvalData method called-----------------");
		System.out.println("xmlString --->>>  [["+xmlString+"  ]]");
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : BudgetGroupAcctAmd.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	
	
	public String wfValData(Document dom, Document dom1, Document dom2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{

		NodeList parentNodeList = null,childNodeList = null;
		Node parentNode = null,childNode = null;;
		int ctr = 0,cnt = 0;
		String childNodeName = null, errString = "",errCode="",userId = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int currentFormNo=0;
		int childNodeListLength;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			GenericUtility genericUtility = GenericUtility.getInstance();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			
			
			if(objContext != null && objContext.trim().length() > 0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			switch(currentFormNo)
			{
				case 1 :
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						
						//changes by deepak
						if (childNodeName.equalsIgnoreCase("amd_no"))
						{
							String amdno="";
							amdno = genericUtility.getColumnValue("amd_no",dom);							

							if (amdno == null || amdno.trim().length() == 0 )
							{							
									errCode = "VMAMNIE";
									errString = getErrorString("amd_no",errCode,userId);
									break;	
							}
							else
							{
								if(!editFlag.equalsIgnoreCase("E"))
								{
								cnt= getDBRowCount(conn,"budgrp_acct_amd","amd_no",amdno);
							}
								if(cnt == 1)
								{
									errCode = "VMAMNOE";
									errString = getErrorString("amd_no",errCode,userId);
									break;
								}
							}
						}
						//changes by deepak
						
						if (childNodeName.equalsIgnoreCase("budget_id"))
						{
							String budgetId="";
							budgetId = genericUtility.getColumnValue("budget_id",dom);							

							if (budgetId == null || budgetId.trim().length() == 0 )
							{							
									errCode = "VMBDEMPY";
									errString = getErrorString("budget_id",errCode,userId);
									break;	
							}
							else
							{
								cnt= getDBRowCount(conn,"bud_grp_acct","budget_id",budgetId);
								if(cnt == 0)
								{
									errCode = "VMBDNTFM";
									errString = getErrorString("budget_id",errCode,userId);
									break;
								}
							}
						}
						
						else if (childNodeName.equalsIgnoreCase("remarks"))
						{
							String remarks="";
							remarks = genericUtility.getColumnValue("remarks",dom);					
							if (remarks == null || remarks.trim().length() == 0 )
							{
									errCode = "VMREMNN";
									errString = getErrorString("remarks",errCode,userId);
									break;
							}
												
						}
					}// for loop end
					break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						System.out.println("------Detail2 --- child Node : "+childNodeName);
						if (childNodeName.equalsIgnoreCase("bud_group_code"))
						{
							 String budGroupCode="";
							 budGroupCode = genericUtility.getColumnValue("bud_group_code",dom);	
							 
							 if (budGroupCode == null || budGroupCode.trim().length() == 0 )
							{							
								errCode = "VMBDGREM";
								errString = getErrorString("bud_group_code",errCode,userId);
								break;	
							}
							 else
								{
								 System.out.println("hhhhhh");
									cnt= getDBRowCount(conn,"bud_group","bud_group_code",budGroupCode);
									if(cnt == 0)
									{
										errCode = "VTBDGRNF";
										errString = getErrorString("bud_group_code",errCode,userId);
										break;
									}else
									{
										 
										String budgetId="";
										budgetId = genericUtility.getColumnValue("budget_id",dom1);	
												 System.out.println("for chaeck syXX"+budgetId);
//												String sql1="select budgetacctdet.budget_amt " +
//														" from bud_grp_acct budgetacct,bud_grp_acctdet budgetacctdet " +
//														" where budgetacct.tran_id = budgetacctdet.tran_id and budgetacct.budget_id = ? and budgetacctdet.bud_group_code = ?";
												
												String sql1="select count(1) " +
														" from bud_grp_acct budgetacct,bud_grp_acctdet budgetacctdet " +
														" where budgetacct.tran_id = budgetacctdet.tran_id and budgetacct.budget_id = ? and budgetacctdet.bud_group_code = ?";
												System.out.println("dddd");
												int cnt1 = 0;
												
												System.out.println("SQL------ : ["+sql1+"]");
												
													pstmt = conn.prepareStatement(sql1);
													pstmt.setString(1,budgetId);
													pstmt.setString(2,budGroupCode);
													rs = pstmt.executeQuery();
													if(rs.next())
													{
														//bGroupCode=rs.getString(1)==null ? "" : rs.getString(1);
														cnt1 = rs.getInt(1);
													}
													pstmt.close();
													rs.close();
													pstmt = null;
													rs = null;
													if(cnt1 == 0 || cnt1 == 0.0)
													{
														errCode = "VMIBGCMI";
														errString = getErrorString("budget_amt_old",errCode,userId);
														break;
													}
													
												//
										 
									}
								}							
						}
						
						//changes by deepak
						else if (childNodeName.equalsIgnoreCase("budget_amt"))
						{
							 String budamt="";
							 budamt = genericUtility.getColumnValue("budget_amt",dom);	
						
							 if (budamt == null || budamt.trim().length() == 0 )
							{							
								errCode = "VTBDATNUL";
								errString = getErrorString("budget_amt",errCode,userId);
								break;	
							}
							
								 
					}
						
						else if (childNodeName.equalsIgnoreCase("remarks"))
						{
							String remarks="";
							remarks = genericUtility.getColumnValue("remarks",dom);					
							if (remarks == null || remarks.trim().length() == 0 )
							{
									errCode = "VMREMNN";
									errString = getErrorString("remarks",errCode,userId);
									break;
							}
												
						}
						
						//changes by deepak
						
						
					}// for loop end
					break;					
			} //end switch
			
		}//end try
		catch(Exception e)
		{			
			e.printStackTrace();
			errString = e.getMessage();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}				
				if(pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null )
				{
					rs.close();
					rs = null;
				}
			}
			catch(Exception d)
			{
				d.printStackTrace();
			}
		}		
		return errString;
	
		
	}
	
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [BudgetGroupAcctAmd ][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
		return valueXmlString;
	}
	
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		StringBuffer valueXmlString = new StringBuffer();
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String childNodeName = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo =0;
		String columnValue = null,budget_Id="";
		int ctr=0;		
		try
		{		
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			
			System.out.println("-------- currentFormNo : "+currentFormNo);
			switch(currentFormNo)
			{
	
				case 1 :
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					ctr = 0; 
					valueXmlString.append("<Detail1>");
					//int childNodeListLength = childNodeList.getLength();				
					System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");			
				
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
					{											
						String confValue="N";	
						valueXmlString.append("<confirmed><![CDATA[").append(confValue).append("]]></confirmed>");
						
					}
					
					if((currentColumn.trim().equalsIgnoreCase("budget_id")))
					{
						String budgetId = genericUtility.getColumnValue("budget_id",dom);
						//budget_Id=budgetId;
						System.out.println("budget Id -----   :->> "+budgetId);
						if(budgetId != null && budgetId.trim().length()>0 )
						{
							String budgetIdDesc = "";						
							budgetIdDesc = getNameOrDescrForCode(conn, "budgets", "descr", "budget_id", budgetId);					
							valueXmlString.append("<budgets_descr>").append("<![CDATA[" + budgetIdDesc + "]]>").append("</budgets_descr>");
						
							System.out.println("budgets_descr ---------- >> : "+budgetIdDesc);					
						}
						else
						{
							valueXmlString.append("<budgets_descr>").append("<![CDATA[]]>").append("</budgets_descr>");
							
						}
					}			
				
					
					valueXmlString.append("</Detail1>");
					break;
		 case 2:
			 	valueXmlString.append("<Detail2>");	
				//SEARCHING THE DOM FOR THE INCOMING COLUMN VALUE START
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0;
				//childNodeListLength = childNodeList.getLength();
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				if (currentColumn.trim().equals("bud_group_code"))
				{
					String budGrpCode="", budId="",tranid="",bGroupCode="";
					budGrpCode= genericUtility.getColumnValue("bud_group_code",dom);
						
					budget_Id=genericUtility.getColumnValue("budget_id",dom1);	
					System.out.println("budGrp Code ----- ID  :->> "+budGrpCode+"  BUdget Id : "+budget_Id);
					if(budGrpCode != null && budGrpCode.trim().length()>0 )
					{
						String budgetGrpCodeDesc = "";
						double budgetAmt=0.0;						
						budgetGrpCodeDesc = getNameOrDescrForCode(conn, "bud_group", "descr", "bud_group_code", budGrpCode);
						//budgetAmt = getNameOrDescrForCode(conn, "bud_grp_acctdet", "budget_amt", "bud_group_code", budGrpCode);
						tranid = getNameOrDescrForCode(conn, "bud_grp_acct", "tran_id", "budget_id", budget_Id);		
						System.out.println("transaction id------@@ "+tranid);
//						String sql1="select budgetacct.tran_id,budgetacct.budget_id,budgetacctdet.BUD_GROUP_CODE,budgetacctdet.budget_amt " +
//								" from bud_grp_acct budgetacct,bud_grp_acctdet budgetacctdet " +
//								" where budgetacct.tran_id= ? and budgetacctdet.bud_group_code = ?";
						
						String sql1="select budgetacctdet.budget_amt " +
								" from bud_grp_acct budgetacct,bud_grp_acctdet budgetacctdet " +
								" where budgetacct.tran_id = budgetacctdet.tran_id and budgetacct.budget_id = ? and budgetacctdet.bud_group_code = ?";
						
						
						
						System.out.println("SQL------ : ["+sql1+"]");
						try
						{
							pstmt = conn.prepareStatement(sql1);
							pstmt.setString(1,budget_Id);
							pstmt.setString(2,budGrpCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								//bGroupCode=rs.getString(1)==null ? "" : rs.getString(1);
								budgetAmt=rs.getDouble("budget_amt");
							}
							System.out.println("budget amount---- >>"+budgetAmt);
						}
						
						catch(SQLException e)
						{
							System.out.println("SQL Exception  BudgetGroupAcctAmd  Class (item change) : "+e.getMessage());
							e.printStackTrace();
						}
						catch(Exception ex)
						{
							System.out.println("Exception: BudgetGroupAcctAmd Class (item change) : "+ex.getMessage());
							ex.printStackTrace();
						}
						
						valueXmlString.append("<descr>").append("<![CDATA[" + budgetGrpCodeDesc + "]]>").append("</descr>");
						valueXmlString.append("<budget_amt_old>").append("<![CDATA[" + budgetAmt + "]]>").append("</budget_amt_old>");
						System.out.println("bud_group_descr ---------- >> : "+budgetGrpCodeDesc);					
					}
					else
					{
						valueXmlString.append("<bud_group_descr>").append("<![CDATA[]]>").append("</bud_group_descr>");
						
					}
				}
				valueXmlString.append("</Detail2>");					
							 
			}// end switch
			valueXmlString.append("</Root>");		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
		
	}	
	
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of BudgetGroupAcctAmd Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of BudgetGroupAcctAmd Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to BudgetGroupAcctAmd.getDBRowCount() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return count;
	}
	
	
	private String getNameOrDescrForCode(Connection conn, String table_name, String descr_col_name,String whrCondCol, String whrCondVal)
	{
			String descr = null;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="SELECT "+descr_col_name+" FROM "+table_name+" WHERE "+whrCondCol+" = ?";
				
				System.out.println("SQL in getNameOrDescrForCode method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString(descr_col_name);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getNameOrDescrForCode method of BudgetGroupAcctAmd Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getNameOrDescrForCode method of BudgetGroupAcctAmd Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to BudgetGroupAcctAmd.getNameOrDescrForCode() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return descr;
	}
	


}
