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

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

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 javax.ejb.Stateless;

import org.w3c.dom.Document;
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;
	
	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 {
		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 {
		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 ("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();
			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";
		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")) {
					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,3);
					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>").append(fromDate).append("</from_tran_date>");
					valueXmlString.append("<to_tran_date>").append(toDate).append("</to_tran_date>");
					valueXmlString.append("<site_code_fr>").append("00").append("</site_code_fr>");
					valueXmlString.append("<site_code_to>").append("ZZ").append("</site_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"))
				{
					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;
			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()); 
			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 )throws ITMException
	{
		Document dom = null;
		System.out.println("@@@@@@@@@---------preSaveForm EJB called...");
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			System.out.println("@@@@@@@xmlString1["+xmlString1+"]@@@@@@@");
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				
				chgUser = genericUtility.getColumnValue("chg_user",dom);
				chgTerm = genericUtility.getColumnValue("chg_term",dom);
				
				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) values (?,?,?,?, ?,?,?,?, ?,?,?,? )";
				getXMLTransData(conn,adpQuery,insertQuery,objName,new StringBuffer(genericUtility.serializeDom(dom)),"CallByManually");
				
				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) throws Exception
	{
		System.out.println("In getXMLTransData");
    	StringBuffer valueXmlString = new StringBuffer("");
    	TransIDGenerator generator = null;
		if(conn!=null){
			ResultSet rs=null;
			PreparedStatement pstmt = null;
			
			int columnCnt = 0;
			int columnItr = 0, detailListCnt = 1;
			ResultSetMetaData resultSetMetaData = null;
			ArrayList<ArrayList> arrayListData = null;
			ArrayList traceData = null;
			ArrayList<ArrayList> queryData = new ArrayList<ArrayList>();
			try {
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if ( rs != null ) {
					System.out.println("In First If");
					resultSetMetaData = rs.getMetaData();
					columnCnt = resultSetMetaData.getColumnCount();
					while(rs.next())
					{
						traceData = new ArrayList<String>();
						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)+"]");*/
							traceData.add(rs.getString(columnItr) == null ? "" : rs.getString(columnItr).trim());
						}
						valueXmlString.append("</Detail>\r\n");
						queryData.add(traceData);
						traceData = null;
					}
					if ("CallByManually".equalsIgnoreCase(callBy)) {
						arrayListData =  buildTraceData(queryData);
					}
					pstmt = null;
					rs = null;
					traceData = null;
				}
				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()+"]");
					
					pstmt = conn.prepareStatement(insertSql);
					for (int itr = 0;itr < arrayListData.size();itr++) {
						traceData = arrayListData.get(itr);
						
						generator = new TransIDGenerator(stringBuffer.toString(),userId,CommonConstants.DB_NAME);
						String uniqueKey = generator.generateTranSeqID("INCORE", "tran_id", keyString, conn);
						
						//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.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();
			}
			catch(Exception ex) {
				System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess 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();
				}
				sql = null;
				insertSql = null;
				objName = null;
				stringBuffer = null;
				queryData = 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 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;
    }
}