package ibase.webitm.ejb.fin;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
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.text.SimpleDateFormat;
import java.util.Calendar;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;

import javax.ejb.Stateless;

@Stateless
public class StrgCustBank extends ValidatorEJB implements StrgCustBankLocal,
		StrgCustBankRemote {
	public StrgCustBank() {
		System.out.println("^^^^^^^ inside Stratergic customer bank ^^^^^^^");
	}

	 public String wfValData(String xmlString, String xmlString1,
			String xmlString2, String objContext, String editFlag,
			String xtraParams) throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";
		System.out.println("deepakkkkk");
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";
		System.out.println("DDEEPPAAKK");
		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			if (objContext != null && Integer.parseInt(objContext) == 1) {
				parentNodeList = dom2.getElementsByTagName("Header0");
				parentNode = parentNodeList.item(1);
				childNodeList = parentNode.getChildNodes();
				for (int x = 0; x < childNodeList.getLength(); x++) {
					childNode = childNodeList.item(x);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("Detail1")) {
						errString = wfValData(dom, dom1, dom2, "1", editFlag,
								xtraParams);
						if (errString != null && errString.trim().length() > 0)
							break;
					} else if (childNodeName.equalsIgnoreCase("Detail2")) {
						errString = wfValData(dom, dom1, dom2, "2", editFlag,
								xtraParams);
						break;
					}
				}
			}

			else {
				errString = wfValData(dom, dom1, dom2, objContext, editFlag,
						xtraParams);
			}
		} catch (Exception e) {
			System.out
					.println("Exception : Inside Stratergic customer bank wfValData Method ..> "
							+ e.getMessage());
			throw new ITMException(e);
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2,
			String objContext, String editFlag, String xtraParams)
			throws RemoteException, ITMException {
		GenericUtility genericUtility;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0, currentFormNo = 0, childNodeListLength = 0, cnt = 0;
		String childNodeName = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = "";
		String userId = "";
		String ScCode = "";

		// SimpleDateFormat simpleDateFormat;

		try {

			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");

			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			genericUtility = GenericUtility.getInstance();
			// simpleDateFormat = new
			// SimpleDateFormat(genericUtility.getApplDateFormat());
			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();
					if (childNodeName.equalsIgnoreCase("sc_code")) {
						ScCode = genericUtility.getColumnValue("sc_code", dom);
						ScCode = ScCode == null ? "" : ScCode.trim();
						if (ScCode.length() == 0) {
							errCode = "VTSTRGCUST";
							errString = getErrorString("sc_code", errCode,
									userId);
							break;
						} else {
							sql = "select count(1) from strg_customer where sc_code = ?";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, ScCode);
							rs = pStmt.executeQuery();

							if (rs.next()) {
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if (cnt == 0) {
								errCode = "VTSTRCUST1";
								errString = getErrorString("sc_code", errCode,
										userId);
								break;
							}
						}
					}
					// else if
					// (childNodeName.equalsIgnoreCase("bank_code__ben")) {
					// String bankCodeBen =
					// genericUtility.getColumnValue("bank_code__ben", dom);
					// bankCodeBen = bankCodeBen == null ? "" :
					// bankCodeBen.trim();
					// if (bankCodeBen.length() == 0) {
					// errCode = "VTBCBNNL";
					// errString = getErrorString("bank_code__ben", errCode,
					// userId);
					// break;
					// }
					// else
					// {
					// sql = "select count(1) from strg_cust_bank where "
					// +" sc_code = ? and bank_code__ben= ? ";
					// pStmt = conn.prepareStatement(sql);
					// pStmt.setString(1, ScCode);
					// pStmt.setString(2, bankCodeBen);
					// rs = pStmt.executeQuery();
					//
					// if (rs.next()) {
					// cnt = rs.getInt(1);
					// }
					// rs.close();
					// rs = null;
					// pStmt.close();
					// pStmt = null;
					// if (cnt > 0) {
					// errCode = "VMCABCE";
					// errString = getErrorString("sc_code",errCode, userId);
					// break;
					// }
					// }
					// }

					else if (childNodeName.equalsIgnoreCase("bank_code__ben")) {
						String bankCodeBen = genericUtility.getColumnValue(
								"bank_code__ben", dom);
						ScCode = genericUtility.getColumnValue("sc_code", dom);
						bankCodeBen = bankCodeBen == null ? "" : bankCodeBen
								.trim();
						if (bankCodeBen.length() == 0) {
							errCode = "VTBCBNNL";
							errString = getErrorString("bank_code__ben",
									errCode, userId);
							break;
						} else {
							if (!editFlag.equals("E")) {
								sql = "select count(1) from strg_cust_bank where "
										+ " sc_code = ? and bank_code__ben= ? ";
								pStmt = conn.prepareStatement(sql);
								pStmt.setString(1, ScCode);
								pStmt.setString(2, bankCodeBen);
								rs = pStmt.executeQuery();

								if (rs.next()) {
									cnt = rs.getInt(1);
								}
								rs.close();
								rs = null;
								pStmt.close();
								pStmt = null;
								if (cnt > 0) {

									errCode = "VMCABCE";
									errString = getErrorString("sc_code",
											errCode, userId);
									break;
								}
							}
						}
					}

					else if (childNodeName.equalsIgnoreCase("bank_name__ben")) {
						String bankNameBen = genericUtility.getColumnValue("bank_name__ben", dom);
						bankNameBen = bankNameBen == null ? "" : bankNameBen.trim();
						if (bankNameBen.length() == 0) {
							errCode = "VTBNBNNL";
							errString = getErrorString("bank_name__ben",
									errCode, userId);
							break;
						}
					} 
					
					else if(childNodeName.equalsIgnoreCase("city__ben"))
					{
						String city_ben = genericUtility.getColumnValue("city__ben",dom);
						

						if(city_ben != null && city_ben.length() > 0)
						{
						sql = "select count(1) from station where city = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,city_ben);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "VMCINTP";
							errString = getErrorString("city__ben",errCode,userId);
							break;
						}
						}
					
					}
					
					else if(childNodeName.equalsIgnoreCase("state_code__ben"))
					{
						String state_code__ben = genericUtility.getColumnValue("state_code__ben",dom);
						
						if(state_code__ben != null && state_code__ben.length() > 0)
						{
						sql = "select count(1) from state where state_code = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,state_code__ben);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "VMSCNPSM";
							errString = getErrorString("state_code__ben",errCode,userId);
							break;
						}
						}
					
					}
					
					else if(childNodeName.equalsIgnoreCase("count_code__ben"))
					{
						String count_code__ben = genericUtility.getColumnValue("count_code__ben",dom);
						
						if(count_code__ben != null && count_code__ben.length() > 0)
						{
						sql = "select count(1) from country where count_code = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,count_code__ben);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "WROCOUNTCD";
							errString = getErrorString("count_code__ben",errCode,userId);
							break;
						}
					
						}
					}
					
					else if (childNodeName.equalsIgnoreCase("bank_code__inter")) {
						String bankCodeBen = genericUtility.getColumnValue(
								"bank_code__inter", dom);
						ScCode = genericUtility.getColumnValue("sc_code", dom);
						bankCodeBen = bankCodeBen == null ? "" : bankCodeBen
								.trim();
						if (bankCodeBen.length() == 0) {
							errCode = "VTBBCIC";
							errString = getErrorString("bank_code__inter",
									errCode, userId);
							break;
						}
						}
					else if (childNodeName.equalsIgnoreCase("bank_name__inter")) {
						String bankNameBen = genericUtility.getColumnValue(
								"bank_name__inter", dom);
						bankNameBen = bankNameBen == null ? "" : bankNameBen
								.trim();
						if (bankNameBen.length() == 0) {
							errCode = "VTBNEBNI";
							errString = getErrorString("bank_name__inter",
									errCode, userId);
							break;
						}
					}
					
					else if(childNodeName.equalsIgnoreCase("city__inter"))
					{
						System.out.println("DDEEPPAAKK");
						String city_inter = genericUtility.getColumnValue("city__inter",dom);
						
						if(city_inter != null && city_inter.length() > 0)
						{
						sql = "select count(1) from station where city = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,city_inter);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "VMCINTP";
							errString = getErrorString("city__inter",errCode,userId);
							break;
						}
						}
					
					}
					
					else if(childNodeName.equalsIgnoreCase("state_code__inter"))
					{
						String state_code__inter = genericUtility.getColumnValue("state_code__inter",dom);
						
						if(state_code__inter != null && state_code__inter.length() > 0)
						{
						sql = "select count(1) from state where state_code = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,state_code__inter);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "VMSCNPSM";
							errString = getErrorString("state_code__inter",errCode,userId);
							break;
						}
						}
					
					}
					
					
					else if(childNodeName.equalsIgnoreCase("count_code__inter"))
					{
						String count_code__inter = genericUtility.getColumnValue("count_code__inter",dom);
						
						if(count_code__inter != null && count_code__inter.length() > 0)
						{
						sql = "select count(1) from country where count_code = ? ";
						pStmt=conn.prepareStatement(sql);
						pStmt.setString(1,count_code__inter);
						rs = pStmt.executeQuery();
						if(rs.next())
						{
							cnt = rs.getInt(1);
						}
						pStmt.close();
						rs.close();
						pStmt = null;
						rs = null;
						if(cnt == 0)
						{
							errCode = "WROCOUNTCD";
							errString = getErrorString("count_code__inter",errCode,userId);
							break;
						}
					
						}
					}
//					else if (childNodeName
//							.equalsIgnoreCase("bank_acct_no__ben")) {
//						String bankActNo = genericUtility.getColumnValue(
//								"bank_acct_no__ben", dom);
//						bankActNo = bankActNo == null ? "" : bankActNo.trim();
//						if (bankActNo.length() == 0) {
//							errCode = "VTBANNL";
//							errString = getErrorString("bank_acct_no__ben",
//									errCode, userId);
//							break;
//						}
//					}
					else if (childNodeName.equalsIgnoreCase("active_yn")) {
						String active = genericUtility.getColumnValue(
								"active_yn", dom);
						active = active == null ? "" : active.trim();
						if (active.length() == 0) {
							errCode = "VTACTNL";
							errString = getErrorString("active_yn", errCode,
									userId);
							break;
						}
					}

				}// end of for
			}// end of switch
		} catch (Exception e) {
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				conn = null;
			} catch (Exception d) {
				d.printStackTrace();
			}
		}
		return errString;
	}

	// /CHANGES FOR ITMCHANGE

	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 : [Stratergic customer bank ][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 {

		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		String childNodeName = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		String sql = "", sql1 = "";
		int currentFormNo = 0;
		String columnValue = "", siteCode = "", salesPerson = "", chgTerm = "", chgUser = "", siteCodePay = "", firstname = "", lastname = "";
		String banknameben = "", add1 = "", add2 = "", add3 = "", city = "", pin = "", statecode = "", acctcode = "", swiftcode = "", countcode = "";
		String banknameinter = "", add1int = "", add2int = "", add3int = "", cityint = "", pinint = "", statecodeint = "", countcodeint = "", swiftcodeint = "";
		String siteCodePayDescr = "", salesPersonName = "", PartyCode = "", PartyName = "", currcode = "";
		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>");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,
					"chg_term");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,
					"chg_user");
			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")) {
					System.out
							.println("------------@@@@@@@@in itm_default@@@@@@@@@---------");
					Calendar currentDate = Calendar.getInstance();
					SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(
							genericUtility.getApplDateFormat());
					String systemDate = simpleDateFormatObj.format(currentDate
							.getTime());
					System.out.println("Now the date is :=>  " + systemDate);

					valueXmlString.append("<site_code>").append("")
							.append("</site_code>");
					valueXmlString.append("<chg_date><![CDATA[")
							.append(systemDate).append("]]></chg_date>\r\n");
					valueXmlString.append("<chg_term><![CDATA[")
							.append(chgTerm).append("]]></chg_term>");
					valueXmlString.append("<chg_user><![CDATA[")
							.append(chgUser).append("]]></chg_user>");

				}

				if ((currentColumn.trim().equalsIgnoreCase("sc_code"))) {
					String ScCode = genericUtility.getColumnValue("sc_code",
							dom);
					System.out.println("sc_code -----   :->> " + ScCode);
					if (ScCode != null && ScCode.trim().length() > 0) {
						sql = "SELECT FIRST_NAME,LAST_NAME FROM STRG_CUSTOMER WHERE SC_CODE = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ScCode);
						rs = pstmt.executeQuery();
						while (rs.next()) {
							firstname = rs.getString(1) == null ? "" : rs
									.getString(1);
							lastname = rs.getString(2) == null ? "" : rs
									.getString(2);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						valueXmlString
								.append("<first_name>")
								.append("<![CDATA[" + firstname + " "
										+ lastname + "]]>")
								.append("</first_name>");
						// valueXmlString.append("<last_name>").append("<![CDATA["
						// + lastname + "]]>").append("</last_name>");

						System.out.println("firstname---------- >> : "
								+ firstname);
						System.out.println("firstname---------- >> : "
								+ lastname);

					} else {
						valueXmlString.append("<first_name>")
								.append("<![CDATA[]]>").append("</first_name>");
						// valueXmlString.append("<last_name>").append("<![CDATA[]]>").append("</last_name>");

					}
				}
				
//added on 06.08.2013
				
				if((currentColumn.trim().equalsIgnoreCase("bank_code__ben")))
				{
					String bankcodeben = genericUtility.getColumnValue("bank_code__ben",dom);
					System.out.println("bank_code__ben -----   :->> "+bankcodeben);
					if(bankcodeben != null && bankcodeben.trim().length()>0 )
					{
						sql = "Select Bank_Name, Addr1, Addr2, Addr3, City,pin,State_Code, Count_Code From Bank Where Bank_Code = ?";
						pstmt= conn.prepareStatement(sql);
						pstmt.setString(1,bankcodeben);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							banknameben = rs.getString(1) == null ? "" : rs.getString(1);
							add1 = rs.getString(2) == null ? "" : rs.getString(2);
							add2 = rs.getString(3) == null ? "" : rs.getString(3);
							add3 = rs.getString(4) == null ? "" : rs.getString(4);
							city = rs.getString(5) == null ? "" : rs.getString(5);
							pin = rs.getString(6) == null ? "" : rs.getString(6);
							statecode = rs.getString(7) == null ? "" : rs.getString(7);
							countcode = rs.getString(8) == null ? "" : rs.getString(8);
							
							
							
						}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					valueXmlString.append("<bank_name__ben>").append("<![CDATA[" + banknameben + "]]>").append("</bank_name__ben>");
					valueXmlString.append("<addr1__ben>").append("<![CDATA[" + add1 + "]]>").append("</addr1__ben>");
					valueXmlString.append("<addr2__ben>").append("<![CDATA[" + add2 + "]]>").append("</addr2__ben>");
					valueXmlString.append("<addr3__ben>").append("<![CDATA[" + add3 + "]]>").append("</addr3__ben>");
					
					valueXmlString.append("<city__ben>").append("<![CDATA[" + city + "]]>").append("</city__ben>");
					valueXmlString.append("<pin__ben>").append("<![CDATA[" + pin + "]]>").append("</pin__ben>");
					valueXmlString.append("<state_code__ben>").append("<![CDATA[" + statecode + "]]>").append("</state_code__ben>");
					valueXmlString.append("<count_code__ben>").append("<![CDATA[" + countcode + "]]>").append("</count_code__ben>");
					
					}
					else
					{
						valueXmlString.append("<bank_name__ben>").append("<![CDATA[]]>").append("</bank_name__ben>");
						valueXmlString.append("<addr1__ben>").append("<![CDATA[]]>").append("</addr1__ben>");
						valueXmlString.append("<addr2__ben>").append("<![CDATA[]]>").append("</addr2__ben>");
						valueXmlString.append("<addr3__ben>").append("<![CDATA[]]>").append("</addr3__ben>");
						valueXmlString.append("<city__ben>").append("<![CDATA[]]>").append("</city__ben>");
						valueXmlString.append("<pin__ben>").append("<![CDATA[]]>").append("</pin__ben>");
						valueXmlString.append("<state_code__ben>").append("<![CDATA[]]>").append("</state_code__ben>");
						valueXmlString.append("<count_code__ben>").append("<![CDATA[]]>").append("</count_code__ben>");
						
						
						}
				}
				
				
				if((currentColumn.trim().equalsIgnoreCase("bank_code__inter")))
				{
					String bankcodeint = genericUtility.getColumnValue("bank_code__inter",dom);
					System.out.println("bank_code__inter -----   :->> "+bankcodeint);
					if(bankcodeint != null && bankcodeint.trim().length()>0 )
					{
						sql = "Select Bank_Name, Addr1, Addr2, Addr3, City,pin,State_Code, Count_Code From Bank Where Bank_Code = ?";
						pstmt= conn.prepareStatement(sql);
						pstmt.setString(1,bankcodeint);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							banknameinter = rs.getString(1) == null ? "" : rs.getString(1);
							add1int = rs.getString(2) == null ? "" : rs.getString(2);
							add2int = rs.getString(3) == null ? "" : rs.getString(3);
							add3int = rs.getString(4) == null ? "" : rs.getString(4);
							cityint = rs.getString(5) == null ? "" : rs.getString(5);
							pinint = rs.getString(6) == null ? "" : rs.getString(6);
							statecodeint = rs.getString(7) == null ? "" : rs.getString(7);
							countcodeint = rs.getString(8) == null ? "" : rs.getString(8);
							
							
							
						}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					valueXmlString.append("<bank_name__inter>").append("<![CDATA[" + banknameinter + "]]>").append("</bank_name__inter>");
					valueXmlString.append("<addr1__inter>").append("<![CDATA[" + add1int + "]]>").append("</addr1__inter>");
					valueXmlString.append("<addr2__inter>").append("<![CDATA[" + add2int + "]]>").append("</addr2__inter>");
					valueXmlString.append("<addr3__inter>").append("<![CDATA[" + add3int + "]]>").append("</addr3__inter>");
					
					valueXmlString.append("<city__inter>").append("<![CDATA[" + cityint + "]]>").append("</city__inter>");
					valueXmlString.append("<pin__inter>").append("<![CDATA[" + pinint + "]]>").append("</pin__inter>");
					valueXmlString.append("<state_code__inter>").append("<![CDATA[" + statecodeint + "]]>").append("</state_code__inter>");
					valueXmlString.append("<count_code__inter>").append("<![CDATA[" + countcodeint + "]]>").append("</count_code__inter>");
					
					}
					else
					{
						valueXmlString.append("<bank_name__inter>").append("<![CDATA[]]>").append("</bank_name__inter>");
						valueXmlString.append("<addr1__inter>").append("<![CDATA[]]>").append("</addr1__inter>");
						valueXmlString.append("<addr2__inter>").append("<![CDATA[]]>").append("</addr2__inter>");
						valueXmlString.append("<addr3__inter>").append("<![CDATA[]]>").append("</addr3__inter>");
						valueXmlString.append("<city__inter>").append("<![CDATA[]]>").append("</city__inter>");
						valueXmlString.append("<pin__inter>").append("<![CDATA[]]>").append("</pin__inter>");
						valueXmlString.append("<state_code__inter>").append("<![CDATA[]]>").append("</state_code__inter>");
						valueXmlString.append("<count_code__inter>").append("<![CDATA[]]>").append("</count_code__inter>");
						
						
						}
				}
				
//added on 06.08.2013

				valueXmlString.append("</Detail1>");
			}// 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) {
					if (rs != null) {
						rs.close();
						rs = null;
					}
					if (pstmt != null) {
						pstmt.close();
						pstmt = null;
					}
					conn.close();
					conn = null;
				}
			} catch (Exception d) {
				d.printStackTrace();
			}
		}

		return valueXmlString.toString();
	}

}
