
/********************************************************
	Title : SiteIC
	Date  : 30/10/14
	Developer: Sagar Mane
    Req. Id: S14GSUN003 
 ********************************************************/

package ibase.webitm.ejb.sys;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.text.SimpleDateFormat;
import java.util.*;
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 org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; 

@Stateless 
public class SiteIC extends ValidatorEJB implements SiteICLocal, SiteICRemote 
{
	GenericUtility genericUtility = GenericUtility.getInstance();

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = "";
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			System.out.println("Val xmlString :: " + xmlString);
			System.out.println("Val xmlString1 :: " + xmlString1);
			System.out.println("Val xmlString2 :: " + xmlString2 );
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString("<Root>" + xmlString2+ "</Root>");
			}
			errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return(errString);
	}
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;	
		Node parentNode = null;
		Node childNode = null;
		String suppCode = "";
		String stanCode = "";
		String stateCode = "";
		String siteCode = "";
		String userId = "";
		String sql="";
		String errCode="";
		String errorType = "";
		String childNodeName = null;
		String errString = "";
		String regCode="",existFlag="",regDate="",validUpto="";
		String finEntity="",payTable="",bankCode="",bankCodeCash="",siteCodeSupp="",siteCodePc="",siteCodeAcct="",finEntityHdr="",siteCodeCons="";
		String qroupCode="",projCode="",siteCodeBdgt="",budgetId="",facilityCode="",itemSerAppl="",itemAppl="",itemApplVal="",itemSerVal="",siteCodeExc="",itemSerAppldb="";
		String serFlag="";
		String itemSer[] = null,itemApplArr[]=null;
		Double adhocReplPerc=0.0;
		int ctr=0,rsCnt=0;
		int childNodeListLength;
		int currentFormNo = 0;
		long count = 0;
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		Connection conn = null;
		PreparedStatement pstmt = null ;
		ResultSet rs = null;
		ConnDriver connDriver = new ConnDriver();
		
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>"); 
		try
		{
			SimpleDateFormat sdf1= new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			if (objContext != null && objContext.trim().length() > 0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			System.out.println(">>>>>>>>>>>>>>>>currentFormNo In validation:"+currentFormNo);
			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();
					if(childNodeName.equalsIgnoreCase("site_code"))
					{    
						siteCode = genericUtility.getColumnValue("site_code", dom);
						if(editFlag.equals("A"))
						{
							if(siteCode!= null && siteCode.trim().length() > 0)
							{
								existFlag = isExist("site", "site_code", siteCode, conn);
								if ("TRUE".equals(existFlag))
								{
									errCode = "VTDUPSITCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
							else
							{
								errCode = "VMSITECD";  //VESIT2 
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("state_code"))
					{    
						stateCode = genericUtility.getColumnValue("state_code", dom);
						if(stateCode!=null && stateCode.trim().length() > 0)
						{
							existFlag = isExist("state", "state_code", stateCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMSTATCD2";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else
						{
							errCode = "NLSTATCODE";      
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
						
					}
					else if(childNodeName.equalsIgnoreCase("stan_code"))
					{    
						stanCode = genericUtility.getColumnValue("stan_code", dom);
						if(stanCode!=null && stanCode.trim().length() > 0)
						{
							existFlag = isExist("station", "stan_code", stanCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VTSTAN1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else
						{
							errCode = "VMSTANCOD";      
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					else if(childNodeName.equalsIgnoreCase("fin_entity"))
					{
						finEntity = genericUtility.getColumnValue("fin_entity", dom);
						if(finEntity!=null && finEntity.trim().length() > 0)
						{
							existFlag = isExist("finent", "fin_entity", finEntity, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMFENTY1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else
						{
							errCode = "VPFINENT1";      
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}	
					}
					else if(childNodeName.equalsIgnoreCase("pay_table"))
					{
						System.out.println(">>>>>>>>>>>pay_table validation:");
						payTable = genericUtility.getColumnValue("pay_table", dom);
						if(payTable!=null && payTable.trim().length() > 0)
						{
							existFlag = isExist("paytable", "pay_table", payTable, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMPYTAB1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("bank_code"))
					{
						bankCode =genericUtility.getColumnValue("bank_code", dom);
						if(bankCode!=null && bankCode.trim().length() > 0)
						{
							existFlag = isExist("bank", "bank_code", bankCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMBANK1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("bank_code__cash"))
					{
						bankCodeCash =genericUtility.getColumnValue("bank_code__cash", dom);
						if(bankCodeCash!=null && bankCodeCash.trim().length() > 0)
						{
							existFlag = isExist("bank", "bank_code", bankCodeCash, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMBANKCASH";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						
					}
					else if(childNodeName.equalsIgnoreCase("site_code__supp"))
					{
						siteCodeSupp =genericUtility.getColumnValue("site_code__supp", dom);
						if(siteCodeSupp!=null && siteCodeSupp.trim().length() > 0)
						{
							existFlag = isExist("site", "site_code", siteCodeSupp, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMSUPSTECD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("site_code__pc"))
					{
						siteCodePc =genericUtility.getColumnValue("site_code__pc", dom);
						if(siteCodePc!= null && siteCodePc.trim().length() > 0)
						{
							existFlag = isExist("site", "site_code", siteCodePc, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMCORSITCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}                       
					else if(childNodeName.equalsIgnoreCase("site_code__acct"))
					{
						System.out.println(">>>>>>>>site_code__acct Validation:");
						siteCodeAcct = genericUtility.getColumnValue("site_code__acct", dom);
						siteCode = genericUtility.getColumnValue("site_code", dom);
						if(siteCodeAcct != null && siteCodeAcct.trim().length() > 0)
						{
							if(!siteCodeAcct.trim().equals(siteCode))
							{
								existFlag = isExist("site", "site_code", siteCodeAcct, conn);
								if ("FALSE".equals(existFlag))
								{
									errCode = "VMACTSTECD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								else
								{
									finEntity="";
									finEntity = findValue(conn, "fin_entity", "site", "site_code", siteCodeAcct);
									finEntityHdr = genericUtility.getColumnValue("fin_entity", dom);
									System.out.println(">>>>>>>>finEntity:"+finEntity);
									System.out.println(">>>>>>>>finEntityHdr:"+finEntityHdr);
									if(!finEntityHdr.trim().equals(finEntity.trim()))
									{
										System.out.println(">>>>>>>Not fin entity match");
										errCode = "VTSITE4";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("site_code__cons"))
					{
						System.out.println(">>>>>>site_code__cons validation:");
						siteCodeCons = genericUtility.getColumnValue("site_code__cons", dom);
						siteCode = genericUtility.getColumnValue("site_code",dom);
						if(!siteCodeCons.trim().equals(siteCode))
						{
							existFlag = isExist("site", "site_code", siteCodeCons, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMSITE3";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("supp_code"))
					{
						System.out.println(">>>>>>supp_code validation:");
						suppCode = genericUtility.getColumnValue("supp_code", dom);
						if(suppCode!= null && suppCode.trim().length() > 0)
						{
							existFlag = isExist("supplier", "supp_code", suppCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VTSUPP1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("adhoc_repl_perc"))
					{
						System.out.println(">>>>>>adhoc_repl_perc validation:");
						adhocReplPerc = Double.parseDouble(genericUtility.getColumnValue("adhoc_repl_perc", dom));
					    System.out.println(">>>>>>>adhocReplPerc:"+adhocReplPerc);
						if(adhocReplPerc < 0 || adhocReplPerc > 100)
						{
							errCode = "VMADHCPERC";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					else if(childNodeName.equalsIgnoreCase("grp_code"))
					{
						System.out.println(">>>>>>grp_code validation:");
						qroupCode = genericUtility.getColumnValue("grp_code", dom);
						itemAppl = checkNull(genericUtility.getColumnValue("item_ser__appl", dom));
						siteCode = genericUtility.getColumnValue("site_code", dom);
						if(qroupCode!=null && qroupCode.trim().length() > 0)
						{
							if(siteCode==null || siteCode.trim().length()==0)
							{
								siteCode=" ";
							}
							sql = "select item_ser__appl from site where grp_code = ? and site_code <> ?";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,qroupCode);
							pstmt.setString(2,siteCode);
							rs = pstmt.executeQuery();
							while(rs.next())
							{
								itemSerAppldb= rs.getString("item_ser__appl");
								System.out.println(">>>>>>>itemSerAppldb Test:"+itemSerAppldb);
								rsCnt=rsCnt+1;
								
								if(rsCnt==1)
								{
									itemSerAppl =itemSerAppldb;
								}
								else
								{
									itemSerAppl = itemSerAppl + "," + itemSerAppldb;
								}
							}
							System.out.println(">>>>>>>>>>>>itemSerAppl Test:"+itemSerAppl);
							
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							itemApplArr= itemAppl.split(",");
							itemSerAppl= checkNull(itemSerAppl);
							itemSer = itemSerAppl.split(",");
							System.out.println(">>>>>>>Array itemApplArr:" + itemApplArr.toString());
							System.out.println(">>>>>>>Array itemSer:" + itemSer.toString());
							serFlag="FALSE";
							//error raise if duplicate item ser is found in same group code
							if(itemSer!=null && itemSer.length > 0 && itemApplArr!=null && itemApplArr.length > 0)
							{
								for(int i = 0; i < itemApplArr.length; i++)
								{
									itemApplVal = itemApplArr[i];
									for(int j=0; j < itemSer.length; j++)
									{
										itemSerVal = itemSer[j];
										if(itemApplVal.trim().equals(itemSerVal.trim()))
										{
											serFlag="TRUE";
										    break;
										}
									}
									if("TRUE".equals(serFlag))
									{
										break;
									}
								}
							}
							if("TRUE".equals(serFlag))
							{
								errCode = "VMGRPSERCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("proj_code"))
					{
						System.out.println(">>>>>>proj_code validation:");
						projCode = genericUtility.getColumnValue("proj_code", dom);
						if(projCode!= null && projCode.trim().length() > 0)
						{
							existFlag = isExist("project", "proj_code", projCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMPROJ1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("site_code__budget"))
					{
						System.out.println(">>>>>>site_code__budget validation:");
						siteCodeBdgt = genericUtility.getColumnValue("site_code__budget", dom);
						if(siteCodeBdgt!= null && siteCodeBdgt.trim().length() > 0)
						{
							existFlag = isExist("site", "site_code", siteCodeBdgt, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VMBGTSITCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
         			    } 
					 }
					 else if(childNodeName.equalsIgnoreCase("budget_id"))
					 {
						 System.out.println(">>>>>>budget_id validation:");
						 budgetId = genericUtility.getColumnValue("budget_id", dom);
						 if(budgetId!= null && budgetId.trim().length() > 0)
						 {
							 sql = "select count(1) from budgets where budget_id = ? and def_budget ='Y' ";
							 pstmt =  conn.prepareStatement(sql);
							 pstmt.setString(1, budgetId);
							 rs = pstmt.executeQuery();
							 if(rs.next())
							 {
								 count = rs.getInt(1);
							 }
							 rs.close();
							 rs = null;
							 pstmt.close();
							 pstmt = null;
							 if(count == 0)
							 {
								 errCode = "VTBUD1";
								 errList.add(errCode);
								 errFields.add(childNodeName.toLowerCase());
							 }
							 count=0;
							 if("A".equals(editFlag))
							 {
								 sql = "select count(1) from site where budget_id = ?";
								 pstmt =  conn.prepareStatement(sql);
								 pstmt.setString(1, budgetId);
								 rs = pstmt.executeQuery();
								 if(rs.next())
								 {
									 count = rs.getInt(1);
								 }
								 rs.close();
								 rs = null;
								 pstmt.close();
								 pstmt = null;
							  }
							  else
							  {
								  siteCode = genericUtility.getColumnValue("site_code", dom);
								  sql = "select count(1) from site where budget_id = ? and site_code <> ? ";
								  pstmt =  conn.prepareStatement(sql);
								  pstmt.setString(1, budgetId);
								  pstmt.setString(2, siteCode);
								  rs = pstmt.executeQuery();
								  if(rs.next())
								  {
									 count = rs.getInt(1);
								  }
								  rs.close();
								  rs = null;
								  pstmt.close();
								  pstmt = null;
							   }
							   if(count > 0)
							   {
									errCode = "VTBUD5";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
							   }
							}
					 }
					 else if(childNodeName.equalsIgnoreCase("facility_code"))
					 {
						 
						 System.out.println(">>>>>>facility_code validation:");
						 facilityCode = genericUtility.getColumnValue("facility_code", dom);
						 if(facilityCode!= null && facilityCode.trim().length() > 0)
						 {
							 existFlag = isExist("facility", "facility_code", facilityCode, conn);
							 if ("FALSE".equals(existFlag))
							 {
								 errCode = "VMFACILTCD";
								 errList.add(errCode);
								 errFields.add(childNodeName.toLowerCase());
							 }
					     }
				     }
					 else if(childNodeName.equalsIgnoreCase("site_code__exc"))
					 {
						 System.out.println(">>>>>>site_code__exc validation:");
						 siteCodeExc = genericUtility.getColumnValue("site_code__exc", dom);
						 if(siteCodeExc!= null && siteCodeExc.trim().length() > 0)
						 {
								existFlag = isExist("site", "site_code", siteCodeExc, conn);
								if ("FALSE".equals(existFlag))
								{
									errCode = "VMEXCSITCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
	         			  } 
				     }
				}
				break; //end case 1 validation.
				
			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();
					if (childNodeName.equalsIgnoreCase("reg_code"))
					{
						regCode = checkNull(genericUtility.getColumnValue("reg_code", dom));
						System.out.println(">>>>>reg_code Validation" + regCode);
						if (regCode != null && regCode.trim().length() > 0)
						{
							existFlag = isExist("reg_requirements", "reg_code", regCode, conn);
							if ("FALSE".equals(existFlag))
							{
								errCode = "VTRCODEXT";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else
						{
							errCode = "VTRCODNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					if (childNodeName.equalsIgnoreCase("reg_date"))
					{
						regDate = checkNull(genericUtility.getColumnValue("reg_date", dom));
						System.out.println(">>>>>>>>>>>regDate:"+regDate);
						if(regDate==null || regDate.trim().length()==0)
						{
							errCode = "VTREGNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					if (childNodeName.equalsIgnoreCase("valid_upto"))
					{
						validUpto = checkNull(genericUtility.getColumnValue("valid_upto", dom));
						System.out.println(">>>>>>>>>>>validUpto:"+regDate);
						if(validUpto==null || validUpto.trim().length()==0)
						{
							errCode = "VTVALNULL";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
						else
						{
							regDate = checkNull(genericUtility.getColumnValue("reg_date", dom));
							if(regDate!=null && regDate.trim().length()> 0)
							{
								Timestamp validUptoDate = Timestamp.valueOf(genericUtility.getValidDateString(validUpto, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
								Timestamp regDateNew = Timestamp.valueOf(genericUtility.getValidDateString(regDate, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
								System.out.println(">>>> validUptoDate is:"+validUptoDate);
								System.out.println(">>>>>regDateNew is:" + regDateNew);
								if(validUptoDate!=null && regDateNew!=null)
								{
									if(validUptoDate.compareTo(regDateNew)<=0)
									{
										errCode = "VTVALREGDT";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							}
						}
					}
				}	
				break; // end case 2  validation.
			} 
			
			int errListSize = errList.size();
			int cnt = 0;
			String errFldName = null;
			if(errList != null && errListSize > 0)
			{
				for(cnt = 0; cnt < errListSize; cnt ++)
				{
					errCode = errList.get((int) cnt);
					errFldName = errFields.get((int) cnt);
					System.out.println("errCode .........." + errCode);
					errString = getErrorString(errFldName, errCode, userId);
					errorType =  errorType(conn , errCode);
					if(errString.length() > 0)
					{
						String bifurErrString = errString.substring(errString.indexOf("<Errors>") + 8, errString.indexOf("<trace>"));
						bifurErrString = bifurErrString + errString.substring(errString.indexOf("</trace>") + 8, errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						errString = "";
					}
					if(errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				errStringXml.append("</Errors> </Root> \r\n");
			}
			else
			{
				errStringXml = new StringBuffer("");
			}
         
		}// End of try

		catch(Exception e)
		{
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null) 
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null) 
				{
					pstmt.close();
					pstmt = null;
				}
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
				connDriver = null;
			} 
			catch(Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		errString = errStringXml.toString();
		return errString;
	}//end of validation

	// method for item change
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if(xmlString2.trim().length() > 0 )
			{
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [supplier][itemChanged( String, String )] :==>\n" + e.getMessage());
		}
		return valueXmlString;
	}

	// method for item change
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		String stanCode = "";
		String city = "";
		String pin = "";
		String stateCode = "";
		String descr = "";
		String sql = "";
		String regCode="",regDescr="";
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
        ConnDriver connDriver = new ConnDriver();
        int currentFormNo = 0;
        int ctr = 0;
        NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String empCode="",empFName="",empLName="",stateDescr="",siteCode="";
		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("**********ITEMCHANGE FOR CASE" + currentFormNo + "**************");
			switch (currentFormNo)
			{
			case 1:
				
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0;
				
				valueXmlString.append("<Detail1>");

				if(currentColumn.trim().equalsIgnoreCase("emp_code__head"))
				{
					System.out.println(">>>emp_code__head itemchange:");
					empCode = genericUtility.getColumnValue("emp_code__head", dom);
					if(empCode!=null && empCode.trim().length() > 0 )
					{
						sql = "select emp_fname, emp_lname from employee where emp_code = ? ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1,empCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							empFName = rs.getString("emp_fname");
							empLName = rs.getString("emp_lname");
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append("<emp_fname>").append("<![CDATA[" + checkNull(empFName) + "]]>").append("</emp_fname>");
						valueXmlString.append("<emp_lname>").append("<![CDATA[" + checkNull(empLName) + "]]>").append("</emp_lname>");
					}
					
				 }
				 else if(currentColumn.trim().equalsIgnoreCase("stan_code"))	
				 {
					System.out.println(">>>>>>>>>>stan_code itemchange:");
					stanCode =genericUtility.getColumnValue("stan_code", dom);
					sql = "select descr,state_code,city,pin from station where stan_code = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,stanCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString("descr");
						stateCode = rs.getString("state_code");
						city = rs.getString("city");
						pin = rs.getString("pin");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;		
					sql = "select descr from state where state_code= ?";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,stateCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						stateDescr = rs.getString("descr");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;		
					valueXmlString.append("<station_descr>").append("<![CDATA[" + checkNull(descr) + "]]>").append("</station_descr>");
					valueXmlString.append("<state_code>").append("<![CDATA[" + checkNull(stateCode) + "]]>").append("</state_code>");
					valueXmlString.append("<state_descr>").append("<![CDATA[" + checkNull(stateDescr) + "]]>").append("</state_descr>");
					valueXmlString.append("<city>").append("<![CDATA[" + checkNull(city) + "]]>").append("</city>");
					valueXmlString.append("<pin>").append("<![CDATA[" + checkNull(pin) + "]]>").append("</pin>");
				 }
				 else if(currentColumn.trim().equalsIgnoreCase("site_code"))	
				 {
					siteCode =genericUtility.getColumnValue("site_code", dom);
					valueXmlString.append("<site_code__acct>").append("<![CDATA[" + siteCode + "]]>").append("</site_code__acct>");
					if("A".equals(editFlag))
					{
						valueXmlString.append("<site_code__cons>").append("<![CDATA[" + siteCode + "]]>").append("</site_code__cons>");
					}
				 }
				 valueXmlString.append("</Detail1>");
				 break;  //end case 1 for itemchange.
				   
			case 2:
				
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0;
				valueXmlString.append("<Detail2>");
				
				System.out.println(">>>currentColumn In case 2:"+currentColumn);

				if (currentColumn.trim().equalsIgnoreCase("reg_code"))
				{
					regCode = genericUtility.getColumnValue("reg_code", dom);
					regDescr = findValue(conn, "DESCR", "REG_REQUIREMENTS", "REG_CODE", regCode);
					valueXmlString.append("<descr>").append("<![CDATA[" + regDescr + "]]>").append("</descr>");
				}
				valueXmlString.append("</Detail2>");
				break; // end case 2 of itemchange
			}
			valueXmlString.append("</Root>");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				rs = null;
				pstmt = null;
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
				connDriver = null;
			}
			catch(Exception d)
			{
				d.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}	 


	public String mid(String fname ,int i , int j)
	{
		String name = fname.substring(1, 40);
		return name; 
	}
	private String checkNull(String input)
	{
		if (input == null)
		{
			input = "";
		}
		return input;
	}
	
	private String isExist(String table, String field, String value, Connection conn) throws SQLException
	{
		String sql = "", retStr = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt = 0;

		sql = " SELECT COUNT(1) FROM " + table + " WHERE " + field + " = ? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, value);
		rs = pstmt.executeQuery();
		if (rs.next())
		{
			cnt = rs.getInt(1);
		}
		rs.close();
		rs = null;
		pstmt.close();
		pstmt = null;
		if (cnt > 0)
		{
			retStr = "TRUE";
		}
		if (cnt == 0)
		{
			retStr = "FALSE";
		}
		System.out.println("@@@@ isexist[" + value + "]:::[" + retStr + "]:::[" + cnt + "]");
		return retStr;
	}
	private String findValue(Connection conn, String columnName, String tableName, String columnName2, String value) throws ITMException, RemoteException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		String findValue = "";
		try
		{
			sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 + "= ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, value);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				findValue = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (findValue == null || findValue.trim().length()== 0)
			{
				findValue = "";
			}
		} catch (Exception e)
		{
			System.out.println("Exception in findValue ");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from findValue " + findValue);
		return findValue;
	}


	private String errorType(Connection conn , String errorCode)
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = "SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO = ?";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setString(1,errorCode);			
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}	
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
}  


