/********************************************************
	Title : InterCompReconcile EJB
	Date  : 18 - JAN - 2014
	Author: Sandeep B. Kumbhar

 ********************************************************/
package ibase.webitm.ejb.fin;

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

import java.io.File;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;


import javax.ejb.Stateless;
import javax.naming.InitialContext;

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

import java.sql.Timestamp;

@Stateless
public class InterCompReconcile extends ValidatorEJB implements InterCompReconcileProcessLocal, InterCompReconcileProcessRemote {

	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = null;
	String chgUser = null;
	String chgTerm = null;
	NumberFormat nf = null;
	boolean isError=false;
	InterCompReconcileProcessLocal interRecProcessObj=null;
	
	public InterCompReconcile() {
		System.out.println("^^^^^^^ inside Inter Compompany Reconciliation ^^^^^^^");
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ inside Inter Compompany Reconciliation 12^^^^^^^");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";

		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 DocumentMaster 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 {
		System.out.println("^^^^^^^ inside Reconciliation wfvaldata---------------");
		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;
		SimpleDateFormat simpleDateFormat = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		String userId = "",firstValue = "",secondValue = "";
		Date tranDate1,tranDate2;

		try {
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			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 ("site_code_fr".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("site_code_fr", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						if (firstValue.length() == 0) {
						    errCode = "NULLSITECD";
						    errString = getErrorString("site_code_fr", errCode, userId);
						    break;
						} else {
							cnt = getDBRowCount(conn, "site", "site_code", firstValue);
							if (cnt == 0) {
								errCode = "INVDSITECD";
								errString = getErrorString("site_code_fr", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("site_code_to".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("site_code_to", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						if (firstValue.length() == 0) {
						    errCode = "NULLSITECD";
						    errString = getErrorString("site_code_to", errCode, userId);
						    break;
						} else {
							cnt = getDBRowCount(conn, "site", "site_code", firstValue);
							if (cnt == 0) {
								errCode = "INVDSITECD";
								errString = getErrorString("site_code_to", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("sundry_code_fr".equalsIgnoreCase(childNodeName)) {
						String sundryCodeFr = genericUtility.getColumnValue("sundry_code_fr", dom);
						String sundryTypeFr = genericUtility.getColumnValue("sundry_type_fr", dom);
						sundryCodeFr=sundryCodeFr==null ? "" :sundryCodeFr.trim();
						sundryTypeFr=sundryTypeFr==null ? "" :sundryTypeFr.trim();	
						System.out.println("sundryCodeFr-->["+sundryCodeFr+"]");
						System.out.println("sundryTypeFr-->["+sundryTypeFr+"]");
						
						if(sundryCodeFr.length() == 0){
							errCode = "VTSUNTYFE"; //Sundry Code from  empty
						    errString = getErrorString("sundry_code_fr", errCode, userId);
						    break;
						}else{
							String tableName="",columnName="";
							if("customer".equalsIgnoreCase(sundryTypeFr)){
								tableName="customer";
								columnName="cust_code";
							}
							else if("supplier".equalsIgnoreCase(sundryTypeFr)){
								tableName="supplier";
								columnName="supp_code";
							}
							cnt = getDBRowCount(conn, tableName, columnName, sundryCodeFr);
							if (cnt == 0) {
								errCode = "VTSUNTYFX"; //sundry code from does not exist in master
								errString = getErrorString("sundry_code_fr", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("sundry_code_to".equalsIgnoreCase(childNodeName)) {
						String sundryCodeTo = genericUtility.getColumnValue("sundry_code_to", dom);
						String sundryTypeTo = genericUtility.getColumnValue("sundry_type_to", dom);
						sundryCodeTo=sundryCodeTo==null ? "" :sundryCodeTo.trim();
						sundryTypeTo=sundryTypeTo==null ? "" :sundryTypeTo.trim();	
						System.out.println("sundryTypeTo-->["+sundryTypeTo+"]");
						System.out.println("sundryCodeTo-->["+sundryCodeTo+"]");
						
						if(sundryCodeTo.length() == 0){
							errCode = "VTSUNTYFE"; //Sundry Code To from  empty
						    errString = getErrorString("sundry_code_fr", errCode, userId);
						    break;
						}else{
							String tableName="",columnName="";
							if("customer".equalsIgnoreCase(sundryTypeTo)){
								tableName="customer";
								columnName="cust_code";
							}
							else if("supplier".equalsIgnoreCase(sundryTypeTo)){
								tableName="supplier";
								columnName="supp_code";
							}
							cnt = getDBRowCount(conn, tableName, columnName, sundryCodeTo);
							if (cnt == 0) {
								errCode = "VTSUNTYTX"; //sundry code To does not existin master
								errString = getErrorString("sundry_code_to", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("from_tran_date".equalsIgnoreCase(childNodeName) || "to_tran_date".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("from_tran_date", dom);
						secondValue = genericUtility.getColumnValue("to_tran_date", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						secondValue = secondValue == null ? "" : secondValue.trim();
					    
						if (firstValue.length() == 0) {
							errCode = "VMDTNAF";
							errString = getErrorString("from_tran_date", errCode, userId);
							break;
					    } else if (secondValue.length() == 0) {
							errCode = "VMDTNAT";
							errString = getErrorString("to_tran_date", errCode, userId);
							break;
					    } else {
					    	tranDate1 = simpleDateFormat.parse(firstValue);
					    	tranDate2 = simpleDateFormat.parse(secondValue);
					    	if (tranDate1.after(tranDate2)) {
							    errCode = "VMDTGTR";
							    errString = getErrorString("from_tran_date", errCode, userId);
							    break;
							}
					    }
						firstValue = null;
						secondValue = null;
						tranDate1 = null;
						tranDate2 = null;
					}
				}

				break;
			}
		} catch (Exception e) {
			e.printStackTrace();			
			errString = e.getMessage();
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				conn = 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 {
		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 : [itemChanged(String,String)] :==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		return valueXmlString;
	}

	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException {

		String siteCodeTo = "", siteCodeFr = "", fromTranDate = "", toTranDate = "", insertQuery = "", path = "", objName = "intercomp_reconcile";
		String sundryTypeFr = "", sundryTypeTo = "", sundryCodeFr = "", sundryCodeTo = "",siteCodeFrDescr="",siteCodeToDescr="";
		StringBuffer valueXmlString = new StringBuffer();
		int currentFormNo = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			switch (currentFormNo) {
			case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");
				//int childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equals("itm_default")) {
					System.out.println("in itm_default,,,,,,,,,,,,,,11111111");
					Calendar cal = Calendar.getInstance();
					Date d = new Date();
					cal.setTime(d);
					cal.set(Calendar.DATE,1);
					d = cal.getTime();
					String fromDate = simpleDateFormat.format(d);
					cal.setTime(d);
					cal.add(Calendar.MONTH,1);
					cal.set(Calendar.DATE,0);
					d = cal.getTime();
					String toDate = simpleDateFormat.format(d);
					System.out.println("From Date :" + fromDate + " To Date :" + toDate);
					valueXmlString.append("<from_tran_date><![CDATA[").append(fromDate).append("]]></from_tran_date>");
					valueXmlString.append("<to_tran_date><![CDATA[").append(toDate).append("]]></to_tran_date>");
					valueXmlString.append("<site_code_fr><![CDATA[").append("").append("]]></site_code_fr>");
					valueXmlString.append("<site_code_to><![CDATA[").append("").append("]]></site_code_to>");
					valueXmlString.append("<sundry_type_fr><![CDATA[").append("Customer").append("]]></sundry_type_fr>");
					valueXmlString.append("<sundry_type_to><![CDATA[").append("Customer").append("]]></sundry_type_to>");
					valueXmlString.append("<sundry_code_fr><![CDATA[").append("").append("]]></sundry_code_fr>");
					valueXmlString.append("<sundry_code_to><![CDATA[").append("").append("]]></sundry_code_to>");
				}
				valueXmlString.append("</Detail1>");
				break;
			case 2 : 
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");
				//childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					System.out.println("calling process ejb12121..............");
					InitialContext ctx = null;
					AppConnectParm appConnect = new AppConnectParm();
					ctx = new InitialContext(appConnect.getProperty());
					interRecProcessObj = (InterCompReconcileProcessLocal)ctx.lookup("ibase/InterCompReconcileProcess/local");
					interRecProcessObj.process(dom1, dom2, "", xtraParams);
					System.out.println("calling process ejb called..............");
					siteCodeTo = genericUtility.getColumnValue("site_code_to",dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code_fr",dom1);
					fromTranDate = genericUtility.getColumnValue("from_tran_date", dom1);
			    	toTranDate = genericUtility.getColumnValue("to_tran_date", dom1);
			    	sundryTypeFr = genericUtility.getColumnValue("sundry_type_fr", dom1);
			    	sundryTypeTo = genericUtility.getColumnValue("sundry_type_to", dom1);
			    	sundryCodeFr = genericUtility.getColumnValue("sundry_code_fr", dom1);
			    	sundryCodeTo = genericUtility.getColumnValue("sundry_code_to", dom1);
			    	
			    	siteCodeToDescr=getColumnDescr(conn,"descr","site","site_code",siteCodeTo);
			    	siteCodeFrDescr=getColumnDescr(conn,"descr","site","site_code",siteCodeFr);
			    	
			    	System.out.println("siteCodeTo="+siteCodeTo+  "  siteCodeFr ="+siteCodeFr);

					path = CommonConstants.JBOSSHOME + File.separator + "sql" + File.separator + "oracle/relatePartyConsoleSupply.sql";
					String adpQuery = genericUtility.readFile(path);
					
					//insertQuery = "Insert Into Intercomp_Reco_Trace ( Tran_Id,Tran_Date,Ref_Ser,Ref_Id,Ref_Ser__To,Ref_Id__To,Site_Code,Site_Code__To,Amount,Chg_Date,Chg_User,Chg_Term) values (?,?,?,?, ?,?,?,?, ?,?,?,? )";
					valueXmlString.append("<Detail1>");
					valueXmlString.append("<from_tran_date><![CDATA[").append(fromTranDate).append("]]></from_tran_date>");
					valueXmlString.append("<to_tran_date><![CDATA[").append(toTranDate).append("]]></to_tran_date>");
					valueXmlString.append("<site_code_fr><![CDATA[").append(siteCodeFr).append("]]></site_code_fr>");
					
					valueXmlString.append("<site_code_fr_descr><![CDATA[").append(siteCodeFrDescr).append("]]></site_code_fr_descr>");
					valueXmlString.append("<site_code_to_descr><![CDATA[").append(siteCodeToDescr).append("]]></site_code_to_descr>");
					
					valueXmlString.append("<site_code_to><![CDATA[").append(siteCodeTo).append("]]></site_code_to>");
					valueXmlString.append("<chg_user><![CDATA[").append(chgUser).append("]]></chg_user>");
					valueXmlString.append("<chg_term><![CDATA[").append(chgTerm).append("]]></chg_term>");
					valueXmlString.append("<sundry_type_fr><![CDATA[").append(sundryTypeFr).append("]]></sundry_type_fr>");
					valueXmlString.append("<sundry_type_to><![CDATA[").append(sundryTypeTo).append("]]></sundry_type_to>");
					valueXmlString.append("<sundry_code_fr><![CDATA[").append(sundryCodeFr).append("]]></sundry_code_fr>");
					valueXmlString.append("<sundry_code_to><![CDATA[").append(sundryCodeTo).append("]]></sundry_code_to>");
					valueXmlString.append("</Detail1>");
					valueXmlString.append("<Detail2>");
					valueXmlString.append(getXMLTransData(conn,adpQuery,"",objName,new StringBuffer(""),"CallByItemChange","","",""));
					valueXmlString.append("</Detail2>");
				
				}
				break;
			case 3 : 
				parentNodeList = dom.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");
				//childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					/*siteCodeTo = genericUtility.getColumnValue("site_code_to",dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code_fr",dom1);
					fromTranDate = genericUtility.getColumnValue("from_tran_date", dom1);
			    	toTranDate = genericUtility.getColumnValue("to_tran_date", dom1);
			    	System.out.println("siteCodeTo="+siteCodeTo+  "  siteCodeFr ="+siteCodeFr);

					path = CommonConstants.JBOSSHOME + File.separator + "sql" + File.separator + "oracle/relatePartyConsoleSupply.sql";
					String adpQuery = genericUtility.readFile(path);
					
					//insertQuery = "Insert Into Intercomp_Reco_Trace ( Tran_Id,Tran_Date,Ref_Ser,Ref_Id,Ref_Ser__To,Ref_Id__To,Site_Code,Site_Code__To,Amount,Chg_Date,Chg_User,Chg_Term) values (?,?,?,?, ?,?,?,?, ?,?,?,? )";
					valueXmlString.append("<Detail1>");
					valueXmlString.append("<from_tran_date><![CDATA[").append(fromTranDate).append("]]></from_tran_date>");
					valueXmlString.append("<to_tran_date><![CDATA[").append(toTranDate).append("]]></to_tran_date>");
					valueXmlString.append("<site_code_fr><![CDATA[").append(siteCodeFr).append("]]></site_code_fr>");
					valueXmlString.append("<site_code_to><![CDATA[").append(siteCodeTo).append("]]></site_code_to>");
					valueXmlString.append("<CHG_USER><![CDATA[").append(chgUser).append("]]></CHG_USER>");
					valueXmlString.append("<CHG_TERM><![CDATA[").append(chgTerm).append("]]></CHG_TERM>");
					valueXmlString.append("</Detail1>");
					valueXmlString.append("<Detail2>");
					valueXmlString.append(getXMLTransData(conn,adpQuery,"",objName,new StringBuffer(""),"CallByItemChange"));
					valueXmlString.append("</Detail2>");*/
				
				}
				break;
			}
			valueXmlString.append("</Root>"); 
		}
		catch(Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage()); 
			try {
				conn.rollback();				
			} catch (Exception d) {
				d.printStackTrace();
			}
			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();
	}

	@Override
	public String getData(String arg0, String arg1, String arg2, String arg3) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String getData(Document arg0, Document arg1, String arg2, String arg3) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String process() throws RemoteException, ITMException {
		return null;
	}

	@Override
	public void remove() {
	}

	@Override
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String process(Document dom, Document dom2, String windowName, String xtraParams) throws RemoteException, ITMException {
		return null;
	}
	
	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 InterCompReconcile Class : "+e.getMessage());
				e.printStackTrace();
			}
			catch(Exception ex)
			{
				System.out.println("Exception In getDBRowCount method of InterCompReconcile 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 InterCompReconcile.getDBRowCount() method is null");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}
	
	public String preSaveForm(String xmlString1,String reconcileType)throws ITMException
	{
		Document dom = null;
		System.out.println("@@@@@@@@@---------preSaveForm EJB called...");
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		String siteCodeFr = "", siteCodeTo = "";
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			System.out.println("@@@@@@@xmlString1["+xmlString1+"]@@@@@@@ reconcileType"+reconcileType);
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				
				chgUser = genericUtility.getColumnValue("chg_user",dom);
				chgTerm = genericUtility.getColumnValue("chg_term",dom);
				siteCodeFr = genericUtility.getColumnValue("site_code_fr",dom);
				siteCodeTo = genericUtility.getColumnValue("site_code_to",dom);
				
				siteCodeFr = siteCodeFr == null? "" : siteCodeFr.trim();
				siteCodeTo = siteCodeTo == null? "" : siteCodeTo.trim();
				
				String path = CommonConstants.JBOSSHOME + File.separator + "sql" + File.separator + "oracle/relatePartyConsoleSupply.sql";
				String adpQuery = genericUtility.readFile(path);
				String objName = "intercomp_reconcile";
				String insertQuery = "Insert Into Intercomp_Reco_Trace ( Tran_Id,Tran_Date,Ref_Ser,Ref_Id,Ref_Ser__To,Ref_Id__To,Site_Code,Site_Code__To,Amount,Chg_Date,Chg_User,Chg_Term,reconcile_type) values (?,?,?,?, ?,?,?,?, ?,?,?,?,? )";
				getXMLTransData(conn,adpQuery,insertQuery,objName,new StringBuffer(genericUtility.serializeDom(dom)),"CallByManually",reconcileType,siteCodeFr,siteCodeTo);
				
				path = null;
				adpQuery = null;
				objName = null;
				insertQuery = null;
			}			
		}
		catch(SQLException e) {
			System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
			e.printStackTrace();
		}
		catch(Exception ex) {
			System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
			ex.printStackTrace();
		} finally {
			try {
				if(conn != null) {
					conn.close(); 
				}
				conn = null;
				xmlString1 = null;
			} catch(Exception d) {
				d.printStackTrace(); 
			}
		}
		return "";
	}
	
	private StringBuffer getXMLTransData(Connection conn, String sql, String insertSql, String objName, StringBuffer stringBuffer, String callBy,String reconcileType,String siteCodeFr,String siteCodeTo) throws Exception
	{
		System.out.println("In getXMLTransData");
		ConnDriver connDriver1 = new ConnDriver();
		Connection conn1=null;
		StringBuffer valueXmlString = new StringBuffer("");
		HashMap<String,Double> issueSiteMap=new HashMap<String,Double>();
		HashMap<String,Double> reseiverSiteMap=new HashMap<String,Double>();
		HashMap<String,String> issueSiteRefMap=new HashMap<String,String>();
		HashMap<String,String> reseiverSiteRefMap=new HashMap<String,String>();
		ArrayList<ArrayList> arrayListData = null;
		ArrayList traceData = null;
		Document dom = null;
		String rType="";
		TransIDGenerator generator = null;
		if(conn!=null){
			ResultSet rs = null;
			PreparedStatement pstmt = null;
			
			int columnCnt = 0;
			int columnItr = 0, detailListCnt = 1;
			ResultSetMetaData resultSetMetaData = null;
			try {
				//Added by Dadaso pawar on 26-MAy-14 [Start]
				// check reconcile type if Auto then following query will executed otherwise 
				// manually--> then only those ref id compare or reconcile whose amount is matched. 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if ( rs != null ) {
					System.out.println("In First If");
					if ("CallByItemChange".equalsIgnoreCase(callBy)) {
						System.out.println("In CallByItemChange");
						resultSetMetaData = rs.getMetaData();
						columnCnt = resultSetMetaData.getColumnCount();
						while(rs.next())
						{
							valueXmlString.append("<Detail domID='"+ detailListCnt++ +"' selected = 'N'>\r\n");
							for (columnItr = 1;columnItr <= columnCnt;columnItr++) {
								valueXmlString.append("<");
								valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr));
								valueXmlString.append(">");
								valueXmlString.append("<![CDATA[");
								valueXmlString.append(rs.getString(columnItr) == null ? "" : rs.getString(columnItr).trim());
								valueXmlString.append("]]>");
								valueXmlString.append("</");
								valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr));
								valueXmlString.append(">\r\n");
								/*System.out.println("ColumnType:["+resultSetMetaData.getColumnType(columnItr)+"]");
								System.out.println("ColumnTypeName:["+resultSetMetaData.getColumnTypeName(columnItr)+"]");
								System.out.println("ColumnValue:["+rs.getString(columnItr)+"]");*/
							}
							valueXmlString.append("</Detail>\r\n");
						}
						pstmt = null;
						rs = null;
					} else if ("CallByManually".equalsIgnoreCase(callBy)) {
						System.out.println("In CallByManually");
						String refSer = "",refId = "",tranDate = "",gpNo = "",gpDate = "", amtStr = "",billNo = "",billDate = "";
						double dramt = 0,cramt = 0,tempAmt = 0,lamt = 0,ramt = 0;
						if("Automatically".equalsIgnoreCase(reconcileType)){
							System.out.println("In Automatically");
							rType ="A";
							while(rs.next()){
								if(siteCodeFr.equalsIgnoreCase(rs.getString("SITE_CODE"))) {
									refSer = rs.getString("REF_SER");
									refId = rs.getString("REF_ID");
									tranDate = rs.getString("TRAN_DATE");
									gpNo = rs.getString("GP_NO");
									gpDate = rs.getString("GP_DATE");
									dramt = rs.getDouble("DR_AMT");
									cramt = rs.getDouble("CR_AMT");
									tempAmt = dramt + cramt;
									System.out.println("refId["+refId+"],tranDate["+tranDate+"],gpNo["+gpNo+"],gpDate["+gpDate+"],dramt["+dramt+"],cramt["+cramt+"]");
									
									refSer = refSer == null ? "" : refSer.trim();
									refId = refId == null ? null : ("".equalsIgnoreCase(refId.trim()) ? null : refId);
									tranDate = tranDate == null ? null : ("".equalsIgnoreCase(tranDate.trim()) ? null : tranDate);
									gpNo = gpNo == null ? null : ("".equalsIgnoreCase(gpNo.trim()) ? null : gpNo);
									gpDate = gpDate == null ? null : ("".equalsIgnoreCase(gpDate.trim()) ? null : gpDate);
									if(refId != null && tranDate != null) {
										refId = refId.trim();
										tranDate = tranDate.trim();
										if(issueSiteMap.containsKey(siteCodeFr+":"+refId+":"+tranDate)) {
											issueSiteMap.put(siteCodeFr+":"+refId+":"+tranDate,issueSiteMap.get(siteCodeFr+":"+refId+":"+tranDate) + tempAmt);
											lamt += tempAmt;
										} else {
											issueSiteMap.put(siteCodeFr+":"+refId+":"+tranDate, tempAmt);
											issueSiteRefMap.put(siteCodeFr+":"+refId+":"+tranDate, refSer);
											lamt += tempAmt;
										}
										
									} else if (gpNo != null && gpDate != null) {
										gpNo = gpNo.trim();
										gpDate = gpDate.trim();
										if(issueSiteMap.containsKey(siteCodeFr+":"+gpNo+":"+gpDate)) {
											issueSiteMap.put(siteCodeFr+":"+gpNo+":"+gpDate,issueSiteMap.get(siteCodeFr+":"+gpNo+":"+gpDate) + tempAmt);
											lamt += tempAmt;
										} else {
											issueSiteMap.put(siteCodeFr+":"+gpNo+":"+gpDate, tempAmt);
											issueSiteRefMap.put(siteCodeFr+":"+gpNo+":"+gpDate, refSer);
											lamt += tempAmt;
										}
									}
								} else if(siteCodeTo.equalsIgnoreCase(rs.getString("SITE_CODE"))) {
									refSer = rs.getString("REF_SER");
									refId = rs.getString("REF_ID");
									tranDate = rs.getString("TRAN_DATE");
									billNo = rs.getString("BL_NO");
									billDate = rs.getString("BL_DATE");
									dramt = rs.getDouble("DR_AMT");
									cramt = rs.getDouble("CR_AMT");
									tempAmt = dramt + cramt;
									System.out.println("refId["+refId+"],tranDate["+tranDate+"],BillNo["+billNo+"],BillDate["+billDate+"],dramt["+dramt+"],cramt["+cramt+"]");
									
									refSer = refSer == null ? "" : refSer.trim();
									refId = refId == null ? null : ("".equalsIgnoreCase(refId.trim()) ? null : refId);
									tranDate = tranDate == null ? null : ("".equalsIgnoreCase(tranDate.trim()) ? null : tranDate);
									billNo = billNo == null ? null : ("".equalsIgnoreCase(billNo.trim()) ? null : billNo);
									billDate = billDate == null ? null : ("".equalsIgnoreCase(billDate.trim()) ? null : billDate);
									if(refId != null && tranDate != null) {
										refId = refId.trim();
										tranDate = tranDate.trim();
										if(reseiverSiteMap.containsKey(siteCodeTo+":"+refId+":"+tranDate)) {
											reseiverSiteMap.put(siteCodeTo+":"+refId+":"+tranDate,issueSiteMap.get(siteCodeTo+":"+refId+":"+tranDate) + tempAmt);
											ramt += tempAmt;
										} else {
											reseiverSiteMap.put(siteCodeTo+":"+refId+":"+tranDate, tempAmt);
											reseiverSiteRefMap.put(siteCodeTo+":"+refId+":"+tranDate, refSer);
											ramt += tempAmt;
										}
									} else if (billNo != null && billDate != null) {
										billNo = billNo.trim();
										billDate = billDate.trim();
										if(reseiverSiteMap.containsKey(siteCodeTo+":"+billNo+":"+billDate)) {
											reseiverSiteMap.put(siteCodeTo+":"+billNo+":"+billDate,reseiverSiteMap.get(siteCodeTo+":"+billNo+":"+billDate) + tempAmt);
											ramt += tempAmt;
										} else {
											reseiverSiteMap.put(siteCodeTo+":"+billNo+":"+billDate, tempAmt);
											reseiverSiteRefMap.put(siteCodeTo+":"+billNo+":"+billDate, refSer);
											ramt += tempAmt;
										}
									}
								}
							}
							pstmt = null;
							rs = null;
							if(lamt == ramt) {
								arrayListData =  buildTraceDataHM(issueSiteMap,reseiverSiteMap,siteCodeFr,siteCodeTo,rType,issueSiteRefMap,reseiverSiteRefMap);
							}
						} else if("Manually".equalsIgnoreCase(reconcileType)){
							System.out.println("In Manually");
							rType ="M";
							dom = GenericUtility.getInstance().parseString(stringBuffer.toString());
							NodeList nList = dom.getElementsByTagName("Detail");
	
							for (int temp = 0; temp < nList.getLength(); temp++) {
								Node nNode = nList.item(temp);
								System.out.println("\nCurrent Element :" + nNode.getNodeName());
								Element eElement = (Element) nNode;
								String siteCode = eElement.getElementsByTagName("SITE_CODE").item(0).getTextContent();
								siteCode = siteCode == null ? "" : siteCode.trim();
								
								if(siteCodeFr.equalsIgnoreCase(siteCode)) {
									refSer = eElement.getElementsByTagName("REF_SER").item(0).getTextContent();
									refId = eElement.getElementsByTagName("REF_ID").item(0).getTextContent();
									tranDate = eElement.getElementsByTagName("TRAN_DATE").item(0).getTextContent();
									gpNo = eElement.getElementsByTagName("GP_NO").item(0).getTextContent();
									gpDate = eElement.getElementsByTagName("GP_DATE").item(0).getTextContent();
									amtStr = eElement.getElementsByTagName("DR_AMT").item(0).getTextContent();
									dramt = amtStr == null ? 0.0 : Double.parseDouble(amtStr.trim());
									amtStr = eElement.getElementsByTagName("CR_AMT").item(0).getTextContent();
									cramt = amtStr == null ? 0.0 : Double.parseDouble(amtStr.trim());
									tempAmt = dramt + cramt;
									System.out.println("refId["+refId+"],tranDate["+tranDate+"],gpNo["+gpNo+"],gpDate["+gpDate+"],dramt["+dramt+"],cramt["+cramt+"]");
									
									refSer = refSer == null ? "" : refSer.trim();
									refId = refId == null ? null : ("".equalsIgnoreCase(refId.trim()) ? null : refId);
									tranDate = tranDate == null ? null : ("".equalsIgnoreCase(tranDate.trim()) ? null : tranDate);
									gpNo = gpNo == null ? null : ("".equalsIgnoreCase(gpNo.trim()) ? null : gpNo);
									gpDate = gpDate == null ? null : ("".equalsIgnoreCase(gpDate.trim()) ? null : gpDate);
									if (refId != null && tranDate != null) {
										refId = refId.trim();
										tranDate = tranDate.trim();
										if(issueSiteMap.containsKey(siteCodeFr+":"+refId+":"+tranDate)) {
											issueSiteMap.put(siteCodeFr+":"+refId+":"+tranDate,issueSiteMap.get(siteCodeFr+":"+refId+":"+tranDate) + tempAmt);
											lamt += tempAmt;
										} else {
											issueSiteMap.put(siteCodeFr+":"+refId+":"+tranDate, tempAmt);
											issueSiteRefMap.put(siteCodeFr+":"+refId+":"+tranDate, refSer);
											lamt += tempAmt;
										}
										
									} else if (gpNo != null && gpDate != null) {
										gpNo = gpNo.trim();
										gpDate = gpDate.trim();
										if(issueSiteMap.containsKey(siteCodeFr+":"+gpNo+":"+gpDate)) {
											issueSiteMap.put(siteCodeFr+":"+gpNo+":"+gpDate,issueSiteMap.get(siteCodeFr+":"+gpNo+":"+gpDate) + tempAmt);
											lamt += tempAmt;
										} else {
											issueSiteMap.put(siteCodeFr+":"+gpNo+":"+gpDate, tempAmt);
											issueSiteRefMap.put(siteCodeFr+":"+gpNo+":"+gpDate, refSer);
											lamt += tempAmt;
										}
									}
								} else if(siteCodeTo.equalsIgnoreCase(siteCode)) {
									refSer = eElement.getElementsByTagName("REF_SER").item(0).getTextContent();
									refId = eElement.getElementsByTagName("REF_ID").item(0).getTextContent();
									tranDate = eElement.getElementsByTagName("TRAN_DATE").item(0).getTextContent();
									billNo = eElement.getElementsByTagName("BIL_NO").item(0).getTextContent();
									billDate = eElement.getElementsByTagName("BIL_DATE").item(0).getTextContent();
									amtStr = eElement.getElementsByTagName("DR_AMT").item(0).getTextContent();
									dramt = amtStr == null ? 0.0 : Double.parseDouble(amtStr.trim());
									amtStr = eElement.getElementsByTagName("CR_AMT").item(0).getTextContent();
									cramt = amtStr == null ? 0.0 : Double.parseDouble(amtStr.trim());
									tempAmt = dramt + cramt;
									System.out.println("refId["+refId+"],tranDate["+tranDate+"],BillNo["+billNo+"],BillDate["+billDate+"],dramt["+dramt+"],cramt["+cramt+"]");
									
									refSer = refSer == null ? "" : refSer.trim();
									refId = refId == null ? null : ("".equalsIgnoreCase(refId.trim()) ? null : refId);
									tranDate = tranDate == null ? null : ("".equalsIgnoreCase(tranDate.trim()) ? null : tranDate);
									billNo = billNo == null ? null : ("".equalsIgnoreCase(billNo.trim()) ? null : billNo);
									billDate = billDate == null ? null : ("".equalsIgnoreCase(billDate.trim()) ? null : billDate);
									if(refId != null && tranDate != null) {
										refId = refId.trim();
										tranDate = tranDate.trim();
										if(reseiverSiteMap.containsKey(siteCodeTo+":"+refId+":"+tranDate)) {
											reseiverSiteMap.put(siteCodeTo+":"+refId+":"+tranDate,issueSiteMap.get(siteCodeTo+":"+refId+":"+tranDate) + tempAmt);
											ramt += tempAmt;
										} else {
											reseiverSiteMap.put(siteCodeTo+":"+refId+":"+tranDate, tempAmt);
											reseiverSiteRefMap.put(siteCodeTo+":"+refId+":"+tranDate, refSer);
											ramt += tempAmt;
										}
									} else if (billNo != null && billDate != null) {
										billNo = billNo.trim();
										billDate = billDate.trim();
										if(reseiverSiteMap.containsKey(siteCodeTo+":"+billNo+":"+billDate)) {
											reseiverSiteMap.put(siteCodeTo+":"+billNo+":"+billDate,reseiverSiteMap.get(siteCodeTo+":"+billNo+":"+billDate) + tempAmt);
											ramt += tempAmt;
										} else {
											reseiverSiteMap.put(siteCodeTo+":"+billNo+":"+billDate, tempAmt);
											reseiverSiteRefMap.put(siteCodeTo+":"+billNo+":"+billDate, refSer);
											ramt += tempAmt;
										}
									}
								}
							}
							if(lamt == ramt) {
								arrayListData =  buildTraceDataHM(issueSiteMap,reseiverSiteMap,siteCodeFr,siteCodeTo,rType,issueSiteRefMap,reseiverSiteRefMap);
							}
						}
					}
				}
				if(arrayListData != null && arrayListData.size() > 0)
				{
					System.out.println("In Second If");
					String keyString = null;
					sql = null;
					sql = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, "w_"+objName);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						keyString = rs.getString("KEY_STRING");
						rs.close();
					}
					pstmt = null;					
					
					
					/*generator = new TransIDGenerator(stringBuffer.toString(),userId,CommonConstants.DB_NAME);
					String uniqueKey = generator.generateTranSeqID("INCORE", "tran_id", keyString, conn);
					System.out.print("uniqueKey:["+uniqueKey+"]");
					String [] uniqueKeyArr = getUniqueIntValue(uniqueKey);
					int intS = Integer.parseInt(uniqueKeyArr[1]);*/
					System.out.println("@@@@@@@stringBuffer1["+stringBuffer+"]@@@@@@@,arrayListData.lenght["+arrayListData.size()+"]");
					System.out.println("arrayListData11---->["+arrayListData+"]");
					pstmt = conn.prepareStatement(insertSql);
					for (int itr = 0;itr < arrayListData.size();itr++) {
						traceData = arrayListData.get(itr);
						System.out.println("traceData1--"+itr+"--->>["+traceData+"]");
					//	String uniqueKey = String.valueOf(itr);
						
					generator = new TransIDGenerator(stringBuffer.toString(),userId,CommonConstants.DB_NAME);
					String uniqueKey = generator.generateTranSeqID("INCORE", "tran_id", keyString, conn);
					//	System.out.println("uniqueKey-->>["+uniqueKey+"]");
						
						
						//System.out.print("uniqueKeyERR:["+uniqueKeyArr[0].toString()+nf.format(intS)+"]");
						System.out.println("ListData=1:["+uniqueKey+"]2:["+traceData.get(0)+"]3:["+traceData.get(1)+"]"+
											"4:["+traceData.get(2)+"]5:["+traceData.get(3)+"]6:["+traceData.get(4)+"]"+
											"7:["+traceData.get(5)+"]8:["+traceData.get(6)+"]9:["+traceData.get(7)+"]");
						
						pstmt.setString(1, uniqueKey);
						pstmt.setTimestamp(2, (Timestamp)traceData.get(0));
						pstmt.setString(3, (String)traceData.get(1));
						pstmt.setString(4, (String)traceData.get(2));
						pstmt.setString(5, (String)traceData.get(3));
						pstmt.setString(6, (String)traceData.get(4));
						pstmt.setString(7, (String)traceData.get(5));
						pstmt.setString(8, (String)traceData.get(6));
						pstmt.setDouble(9, (Double)traceData.get(7));
						pstmt.setTimestamp(10, new Timestamp(System.currentTimeMillis()));
						pstmt.setString(11, chgUser);
						pstmt.setString(12, chgTerm);
						pstmt.setString(13, rType);
	
						pstmt.addBatch();
					}
					
					int rowsInsertedWorkOrder[] = pstmt.executeBatch();
					conn.commit();
					pstmt.close();
					//uniqueKey = null;
				}
			}
			catch(SQLException e) {
				System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
				e.printStackTrace();
				try{
					conn.rollback();
				}
				catch(Exception e1){
					e1.printStackTrace();
				}
			}
			catch(Exception ex) {
				System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
				try{
					conn.rollback();
				}
				catch(Exception e1){
					e1.printStackTrace();
				}
				ex.printStackTrace();
			} finally {
				try{
					if(pstmt!=null){
						pstmt.close();
						pstmt = null;
					}
					if(rs!=null){
						rs.close();
						rs = null;
					}
					if(conn1!=null){
						conn1.close();
						conn1=null;
					}
				}catch (SQLException se) {
					se.printStackTrace();
				}
				sql = null;
				insertSql = null;
				objName = null;
				stringBuffer = null;
			}
		}else{
			try {
				throw new SQLException("Connection passed to InterCompReconcileProcess.getXMLTransData() method is null");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return valueXmlString;
	}
	private ArrayList<ArrayList> buildTraceData(ArrayList<ArrayList> rs) throws Exception
	{
    	System.out.println("In buildTraceData");
    	ArrayList<ArrayList> arrayListData = new ArrayList<ArrayList>();
		try {
			ArrayList traceData = null;
			String refId = "",refDate = "";
			double refAmt = 0.0,tempAmt = 0.0,dramt = 0,cramt = 0;
			if ( rs != null && rs.size() > 0 ) {
				String siteCode = null;
				double amount = 0.0;
				for (int itr = 0;itr < rs.size();itr++)
				{
					dramt = rs.get(itr).get(5) == null ? 0.0 : Double.parseDouble(rs.get(itr).get(5).toString());
					cramt = rs.get(itr).get(6) == null ? 0.0 : Double.parseDouble(rs.get(itr).get(6).toString());
					tempAmt = dramt + cramt;
					/*System.out.println("refid["+refId+"],=["+rs.get(itr).get(2).toString().trim()+"]");
					System.out.println("refDate["+refDate+"],=["+rs.get(itr).get(0).toString().trim()+"]");
					System.out.println("refAmt["+refAmt+"],=["+tempAmt+"]");*/
					if(refId.equalsIgnoreCase(rs.get(itr).get(2).toString().trim()) && refDate.equalsIgnoreCase(rs.get(itr).get(0).toString().trim()) && refAmt == tempAmt)
					{
						traceData.add(rs.get(itr).get(1) == null ? "" : rs.get(itr).get(1));
						traceData.add(rs.get(itr).get(2) == null ? "" : rs.get(itr).get(2));
						traceData.add(siteCode);
						traceData.add(rs.get(itr).get(7) == null ? "" : rs.get(itr).get(7));
						traceData.add(amount > 0 ? amount : rs.get(itr).get(5) == null ? 0.0 : Double.parseDouble(rs.get(itr).get(5).toString()));
						
						arrayListData.add(traceData);
						refId = "";
						refDate = "";
						refAmt = 0;
					} else {
						refId = rs.get(itr).get(2).toString();
						refDate = rs.get(itr).get(0).toString();
						refAmt = tempAmt;
						traceData = null;
						traceData = new ArrayList<String>();
						java.util.Date date = new java.util.Date(rs.get(itr).get(0).toString());
						Timestamp time = new Timestamp(date.getTime());
						traceData.add(time);
						traceData.add(rs.get(itr).get(1) == null ? "" : rs.get(itr).get(1));
						traceData.add(rs.get(itr).get(2) == null ? "" : rs.get(itr).get(2));
						siteCode = rs.get(itr).get(7) == null ? "" : rs.get(itr).get(7).toString();
						amount = rs.get(itr).get(5) == null ? 0.0 : Double.parseDouble(rs.get(itr).get(5).toString());
					}
				}
				/*if ( arrayListData.size() % 2 > 0) {
					System.out.print(" 4:[ ]");
					System.out.print(" 5:[ ]");
					System.out.print(" 6:["+siteCode+"]");
					System.out.print(" 7:[ ]");
					System.out.print(" 8:["+amount+"]");
					traceData.add("");
					traceData.add("");
					traceData.add(siteCode);
					traceData.add("");
					traceData.add(amount);
					
					arrayListData.add(traceData);
				}*/
			}
			traceData = null;
			rs = null;
		} catch(Exception e) {
			System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
			e.printStackTrace();
			
		} finally {
			try{
			}catch (Exception se) {
				se.printStackTrace();
			}
		}
		return arrayListData;
	}
    private ArrayList<ArrayList> buildTraceDataHM(HashMap<String,Double> issueSiteMap,HashMap<String,Double> reseiverSiteMap,String siteCodeFr,String siteCodeTo,String rType,HashMap<String,String> issueSiteRefMap,HashMap<String,String> reseiverSiteRefMap) throws Exception
	{
    	System.out.println("In buildTraceDataHM");
    	System.out.println("issueSiteMap:["+issueSiteMap+"],reseiverSiteMap:["+reseiverSiteMap+"]");    	
    	rType=rType==null ? ""  : rType.trim();
    	System.out.println("rType--->>["+rType+"]");
    	ArrayList<ArrayList> arrayListData = new ArrayList<ArrayList>();
    	ArrayList<ArrayList> issuesList = null;
    	ArrayList<ArrayList> receiverList = null;
    	String key = "",firstRefSer = "",secondRefSer = "";
    	double firstSite = 0,secondSite = 0;
    	int leftcnt = 0, rightcnt = 0;
		try {
			ArrayList traceData = null;
			if ( issueSiteMap != null && reseiverSiteMap != null && issueSiteMap.size() > 0 && reseiverSiteMap.size() > 0 )
			{
				if("A".equalsIgnoreCase(rType))
				{
					Set<String> issueSet = issueSiteMap.keySet();
					Iterator itr = issueSet.iterator();
					while(itr.hasNext())
					{
						key = (String)itr.next();
						System.out.println("key["+key+"]");
						String[] keyArray = key.split(":");
						System.out.println("1:["+keyArray[0]+"]");
						System.out.println("2:["+keyArray[1]+"]");
						System.out.println("3:["+keyArray[2]+"]");
						if(reseiverSiteMap.containsKey(siteCodeTo+":"+keyArray[1].toString()+":"+keyArray[2].toString()))
						{
							firstSite = issueSiteMap.get(key);
							secondSite = reseiverSiteMap.get(siteCodeTo+":"+keyArray[1].toString()+":"+keyArray[2].toString());
							firstRefSer = issueSiteRefMap.get(siteCodeTo+":"+keyArray[1].toString()+":"+keyArray[2].toString());
							secondRefSer = reseiverSiteRefMap.get(siteCodeTo+":"+keyArray[1].toString()+":"+keyArray[2].toString());
							if(firstSite == secondSite)
							{
								System.out.println("Ala re ala");
								traceData = null;
								traceData = new ArrayList<String>();
								java.util.Date date = new java.util.Date(keyArray[2].toString());
								Timestamp time = new Timestamp(date.getTime());
								traceData.add(time);
								traceData.add(firstRefSer);
								traceData.add(keyArray[1].toString());
								traceData.add(secondRefSer);
								traceData.add(keyArray[1].toString());
								traceData.add(siteCodeFr);
								traceData.add(siteCodeTo);
								traceData.add(firstSite);
								arrayListData.add(traceData);
								
							}
						}
					}
				}
				else if("M".equalsIgnoreCase(rType))
				{
					issuesList = getArrayListHM(issueSiteMap,issueSiteRefMap);
			    	receiverList = getArrayListHM(reseiverSiteMap,reseiverSiteRefMap);
			    	
			    	firstSite = (Double)issuesList.get(0).get(3);
					secondSite = (Double)receiverList.get(0).get(3);
					while(true)
					{
						if(issuesList.size() == leftcnt && receiverList.size() == rightcnt)
						{
							break;
						}
						else if(firstSite == secondSite)
						{
							System.out.println("AAAAA:leftcnt["+leftcnt+"],rightcnt["+rightcnt+"],firstSite["+firstSite+"]");
							traceData = null;
							traceData = new ArrayList<String>();
							java.util.Date date = new java.util.Date(issuesList.get(leftcnt).get(2).toString());
							Timestamp time = new Timestamp(date.getTime());
							traceData.add(time);
							traceData.add(issuesList.get(leftcnt).get(4).toString());
							traceData.add(issuesList.get(leftcnt).get(1).toString());
							traceData.add(receiverList.get(rightcnt).get(4).toString());
							traceData.add(receiverList.get(rightcnt).get(1).toString());
							traceData.add(issuesList.get(leftcnt).get(0).toString());
							traceData.add(receiverList.get(rightcnt).get(0).toString());
							traceData.add(firstSite);
							arrayListData.add(traceData);
							
							leftcnt++;
							rightcnt++;
							if(leftcnt < issuesList.size() && rightcnt < receiverList.size()) {
								firstSite = (Double)issuesList.get(leftcnt).get(3);
								secondSite = (Double)receiverList.get(rightcnt).get(3);
							}
						}
						else if(firstSite > secondSite)
						{
							firstSite = firstSite - secondSite;
							System.out.println("BBBBB:leftcnt["+leftcnt+"],rightcnt["+rightcnt+"],secondSite["+secondSite+"]");
							traceData = null;
							traceData = new ArrayList<String>();
							java.util.Date date = new java.util.Date(issuesList.get(leftcnt).get(2).toString());
							Timestamp time = new Timestamp(date.getTime());
							traceData.add(time);
							traceData.add(issuesList.get(leftcnt).get(4).toString());
							traceData.add(issuesList.get(leftcnt).get(1).toString());
							traceData.add(receiverList.get(rightcnt).get(4).toString());
							traceData.add(receiverList.get(rightcnt).get(1).toString());
							traceData.add(issuesList.get(leftcnt).get(0).toString());
							traceData.add(receiverList.get(rightcnt).get(0).toString());
							traceData.add(secondSite);
							arrayListData.add(traceData);
							
							rightcnt++;
							if(rightcnt < receiverList.size()) {
								secondSite = (Double)receiverList.get(rightcnt).get(3);
							}
						}
						else if(firstSite < secondSite)
						{
							secondSite =  secondSite - firstSite;
							System.out.println("CCCCC:leftcnt["+leftcnt+"],rightcnt["+rightcnt+"],firstSite["+firstSite+"]");
							traceData = null;
							traceData = new ArrayList<String>();
							java.util.Date date = new java.util.Date(issuesList.get(leftcnt).get(2).toString());
							Timestamp time = new Timestamp(date.getTime());
							traceData.add(time);
							traceData.add(issuesList.get(leftcnt).get(4).toString());
							traceData.add(issuesList.get(leftcnt).get(1).toString());
							traceData.add(receiverList.get(rightcnt).get(4).toString());
							traceData.add(receiverList.get(rightcnt).get(1).toString());
							traceData.add(issuesList.get(leftcnt).get(0).toString());
							traceData.add(receiverList.get(rightcnt).get(0).toString());
							traceData.add(firstSite);
							arrayListData.add(traceData);
							
							leftcnt++;
							if(leftcnt < issuesList.size()) {
								firstSite = (Double)issuesList.get(leftcnt).get(3);
							}
						}
					}
				}
				issueSiteMap = null;
				reseiverSiteMap = null;
				siteCodeFr = null;
				siteCodeTo = null;
				traceData = null;
				issuesList = null;
				receiverList = null;
				issueSiteRefMap = null;
				reseiverSiteRefMap = null;
				key = null;
				firstRefSer = null;
				secondRefSer = null;
			}
		} catch(Exception e) {
			System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
			e.printStackTrace();
		} finally {
			try{
			}catch (Exception se) {
				se.printStackTrace();
			}
		}
		return arrayListData;
	}
    private String[] getUniqueIntValue(String uniqueNo)
    {
    	System.out.println("In getUniqueIntValue");
    	String [] returnVal = new String[2];
    	int strcnt = 0;
    	StringBuffer sbInt = new StringBuffer("");
		StringBuffer sbStr = new StringBuffer("");
		for(strcnt = 0;strcnt < uniqueNo.length();strcnt++)
		{
			if((int)uniqueNo.charAt(strcnt) >= 48 && (int)uniqueNo.charAt(strcnt) <= 57)
			{
				sbInt.append(uniqueNo.charAt(strcnt));
			} else {
				sbStr.append(uniqueNo.charAt(strcnt));
			}
		}
		returnVal[0] = sbStr.toString();
		returnVal[1] = sbInt.toString();
		sbInt.delete(0, sbInt.length());
		for(strcnt = 0;strcnt < 10 - sbStr.length();strcnt++)
		{
			sbInt.append("0");
		}
		nf = new DecimalFormat(sbInt.toString());
		sbInt = null;
		sbStr = null;
		
    	return returnVal;
    }
    private ArrayList<ArrayList> getArrayListHM(HashMap<String , Double> HMListAmount,HashMap<String , String> HMListRef)
    {
    	ArrayList<ArrayList> resultList = new ArrayList<ArrayList>();
    	ArrayList tempList = null;
    	Set set = null;
    	Iterator itr = null;
    	String key = "",refSer = "";
    	double amount = 0.0;
    	try{
    		set = HMListAmount.keySet();
			itr = set.iterator();
			while(itr.hasNext())
			{
				key = (String)itr.next();
				String[] keyArray = key.split(":");
				System.out.println("1:["+keyArray[0]+"]");
				System.out.println("2:["+keyArray[1]+"]");
				System.out.println("3:["+keyArray[2]+"]");
				amount = HMListAmount.get(key);
				refSer = HMListRef.get(key);
				
				tempList = null;
				tempList = new ArrayList();
				tempList.add(keyArray[0]);
				tempList.add(keyArray[1]);
				tempList.add(keyArray[2]);
				tempList.add(amount);
				tempList.add(refSer);
				
				resultList.add(tempList);
			}
			tempList = null;
			set = null;
	    	itr = null;
	    	key = null;
	    	refSer = null;
    	}
    	catch(Exception e) {
    		e.printStackTrace();
    	}
    	return resultList;
    }
    protected String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			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;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}

	@Override
	public String getValuesForPopHelp(String sundryType) {
		// TODO Auto-generated method stub
		return null;
	}
}