
/*
    Window Name : w_bank_reco
	Button Name : Process
	Action		: It Select data from BANKTRAN_LOG and after that 
				  Insert into BANK_STATEMENT,BANKRECO_TRACE Also 
				  Update STATUS Field of BANKTRAN_LOG Table. 	              
*/ 
package ibase.webitm.ejb.fin.adv;

import java.rmi.RemoteException;
import java.util.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import ibase.system.config.*;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class BankRecoPrc extends ProcessEJB implements BankRecoPrcLocal,BankRecoPrcRemote //SessionBean
{
  	/*public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			System.out.println("ProcessEJB ejbCreate called.........");
			
		}
		catch (Exception e)
		{
			System.out.println("Exception :ProcessEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}*/
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document dom = null;
		Document dom1 = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				dom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				dom1 = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(dom, dom1, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :BankRecoEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		return rtrStr; 
	}
	public String getData(Document dom, Document dom2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String getDataSql = "";
		String bankCode = "";
		String effDate = "";
		String resultString = "";
		String errString = "";
		String errCode = "", entryBatchNo = "";
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		int cnt = 0;
		StringBuffer retTabSepStrBuff = new StringBuffer();
	    ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			bankCode = genericUtility.getColumnValue("bank_code",dom);
			effDate = genericUtility.getColumnValue("eff_date",dom);
			getDataSql ="SELECT BANKTRAN_LOG.TRAN_ID,"   
						 +"BANKTRAN_LOG.TRAN_TYPE,"   
						 +"BANKTRAN_LOG.TRAN_DATE,"   
						 +"BANKTRAN_LOG.BANK_CODE,"   
						 +"BANKTRAN_LOG.SITE_CODE,"   
						 +"BANKTRAN_LOG.TRAN_SER,"   
						 +"BANKTRAN_LOG.TRAN_NO,"   
						 +"BANKTRAN_LOG.REF_NO,"   
						 +"BANKTRAN_LOG.REF_DATE,"   
						 +"BANKTRAN_LOG.REF_MODE,"   
						 +"BANKTRAN_LOG.AMOUNT,"   
						 +"BANKTRAN_LOG.SUNDRY_CODE,"   
						 +"BANKTRAN_LOG.ACCT_CODE,"   
						 +"BANKTRAN_LOG.CCTR_CODE,"   
						 +"BANKTRAN_LOG.STATUS,"   
						 +"BANKTRAN_LOG.AMT_ADJ,"   
						 +"BANKTRAN_LOG.REMARKS,"   
						 +"BANKTRAN_LOG.EXCH_RATE,"   
						 +"BANKTRAN_LOG.CURR_CODE,"   
						 +"BANKTRAN_LOG.ENTRY_BATCH_NO "  
						 +"FROM BANKTRAN_LOG "  
						 +"WHERE (BANKTRAN_LOG.BANK_CODE = '"+ bankCode +"'"+") AND( BANKTRAN_LOG.STATUS IN ( 'P','E' ) ) ORDER BY TRAN_ID";    
			System.out.println("The getDataSql becomes :"+getDataSql);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(getDataSql);
			if (rs.next())
			{
				do
				{
					retTabSepStrBuff.append(rs.getString(1)).append("\t");
					retTabSepStrBuff.append(rs.getString(2)).append("\t");
					retTabSepStrBuff.append(rs.getString(3)).append("\t");
					retTabSepStrBuff.append(rs.getString(4)).append("\t");
					retTabSepStrBuff.append(rs.getString(5)).append("\t");
					retTabSepStrBuff.append(rs.getString(6)).append("\t");
					retTabSepStrBuff.append(rs.getString(7)).append("\t");
					retTabSepStrBuff.append(rs.getString(8)).append("\t");
					retTabSepStrBuff.append(rs.getString(9)).append("\t");
					retTabSepStrBuff.append(rs.getString(10)).append("\t");
					retTabSepStrBuff.append(rs.getDouble(11)).append("\t");
					retTabSepStrBuff.append(rs.getString(12)).append("\t");
					retTabSepStrBuff.append(rs.getString(13)).append("\t");
					retTabSepStrBuff.append(rs.getString(14)).append("\t");
					retTabSepStrBuff.append(rs.getString(15)).append("\t");
					retTabSepStrBuff.append(rs.getDouble(16)).append("\t");
					retTabSepStrBuff.append(rs.getString(17)).append("\t");
					retTabSepStrBuff.append(rs.getDouble(18)).append("\t");
					retTabSepStrBuff.append(rs.getString(19)).append("\t");
					//retTabSepStrBuff.append(rs.getString(20)).append("\n"); //Gulzar 20/12/06
					//Added by Gulzar 20/12/06
					entryBatchNo = rs.getString(20);
					if (entryBatchNo == null)
					{
						entryBatchNo = " ";
					}
					retTabSepStrBuff.append(entryBatchNo).append("\n");
					//End Addition Gulzar 20/12/06
					cnt++;

				}while(rs.next());
				resultString = retTabSepStrBuff.toString();
			}
			else
			{
				errCode ="VTNOREC2";
			}
			if (errCode != null && errCode.trim().length()>0)
			{
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				resultString = errString;
				System.out.println("errString: "+errString);
			}
		}
		catch (SQLException e)
		{
			System.out.println("Exception :BankRecoEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception :BankRecoEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}catch(Exception e){}			
		}
		return resultString;	
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{	
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("Detail XML String  :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 
			}
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("Header XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 
			}
		   retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :BankRecoEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		return retStr;
	}

	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		double amount = 0;
		double amount1 = 0;
		double amtAdj = 0;
		double totalAmtBc = 0;
		double totalAmtCurr = 0;
		int cnt = 0;
		double exchRate = 0; 
		String currCode = "";
		String exchRate1 = "";
		String status = "";
		String tranId = "";
		String tranSer = "";
		String tranNo = "";
		String sql = "";
		String xmlStr = "";
		String errCode = "";
		String childNodeName = "";
		String errString = "";
		ResultSet rs = null;
		Connection conn = null; 
		Statement stmt = null;
		Document dom1;
		int childNodeListLength = 0;		
		int parentNodeListLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{	
			conn = connDriver.getConnectDB("DriverITM");
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			for (int header = 0; header < parentNodeListLength; header++)
			{
				parentNode = parentNodeList.item(header);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for (int cntr = 0; cntr < childNodeListLength; cntr++)
				{
					childNode = childNodeList.item(cntr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals("tran_id"))
					{
						tranId = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("tran_ser"))
					{
						tranSer = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("tran_no"))
					{
						tranNo = childNode.getFirstChild().getNodeValue(); 						
					}
				}
				sql = "SELECT AMOUNT, AMT_ADJ, STATUS, EXCH_RATE FROM BANKTRAN_LOG WHERE TRAN_ID = '"+tranId+"'";
				System.out.println("The sql becomes :"+sql);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					amount = rs.getDouble(1);
					amtAdj = rs.getDouble(2);
					status = rs.getString(3);
					exchRate = rs.getDouble(4);
					if (exchRate == 0)
					{
						xmlStr = getBanklogExchRate(tranSer,tranNo);
						dom1 = genericUtility.parseString(xmlStr); 
						currCode = genericUtility.getColumnValue("curr_code",dom1);				
						exchRate1 = genericUtility.getColumnValue("exch_rate",dom1);						
					}
					if (!status.equals("R"))
					{
						amount = amount - amtAdj;
						amount1 = amount1 + amount;
						totalAmtBc = amount1 * exchRate; 
						totalAmtCurr = amount1 / exchRate;						
						cnt++;
					}
					else
					{
						errCode = "VTRECON1";
						break;
					}
				}//if end
				
			}//outer for end
			System.out.println("cnt :"+cnt);
			if (cnt <= 0)
			{
				return "";
			}
			if (errCode != null  && errCode.trim().length() > 0)
			{
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				conn.close();
				conn = null;
				return errString;
			}
			errCode = updateTable(cnt,headerDom,detailDom);
			if (errCode != null || errCode.trim().length()>0)
			{
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("errString becomes: "+errString);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :BankRecoEJB :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}catch(Exception se){}
		}
		return errString;
	}

	private String updateTable(int cnt, Document headerDom, Document detailDom) throws RemoteException, ITMException
	{
		Document dom1 = null;
		System.out.println("Counter :"+ cnt + "\n detailDom :"+detailDom + "\n headerDom :"+headerDom);
		String errCode = "";
		String errString = "";
		String childNodeName = null;
		String tranId = "";
		String tranType = "";
		String tranDate = "";
		String bankCode = "";
		String siteCode = "";
		String tranSer = "";
		String tranNo = "";
		String refNo = "";
		String refDate = "";
		String refMode = "";
		String amount = "";
		String sundryCode = "";
		String acctCode = "";
		String cctrCode = "";
		String amtAdj = "";
		String remarks = "";
		String currCode = "";
		String exchRate = "";
		String xmlStr = "";
		String status = "";
		ArrayList tranIdList = new ArrayList();
		ArrayList amountList = new ArrayList();
		ArrayList amtAdjList = new ArrayList();
		Connection conn = null;
		ResultSet rSet = null;
		Statement stmt = null;
		String keyStringQuery = "";
		double amt = 0;
		int childNodeListLength = 0;		
		int parentNodeListLength = 0;
		int counter = 0;
		java.sql.Date currDate = null;
		double exchRateValue = 0;
		double amtAdjBase = 0;
		double amtCheck = 0;
		double amount1 = 0;
		double amt2 = 0;
		double amt3 = 0;
		double exch = 0;
		double adjAmt = 0;
		double amtLog = 0;
		double amtAdj1 = 0;
		int comp = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
	    ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{
			//conn = getConnection();
			conn = connDriver.getConnectDB("DriverITM");
			/*--- Commented by Gulzar as the logic to process single and multiple record should same 15/11/06
			if (cnt == 1)
			{
				parentNodeList = detailDom.getElementsByTagName("Detail2");
				parentNodeListLength = parentNodeList.getLength();
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for (int cntr = 0; cntr < childNodeListLength; cntr++)
				{
					childNode = childNodeList.item(cntr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals("tran_id"))
					{
						tranId = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("tran_type"))
					{
						tranType = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("tran_date"))
					{
						tranDate = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("bank_code"))
					{
						bankCode = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("site_code"))
					{
						siteCode = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("tran_ser"))
					{
						tranSer = childNode.getFirstChild().getNodeValue();						
					}
					else if (childNodeName.equals("tran_no"))
					{
						tranNo = childNode.getFirstChild().getNodeValue();						
					}
					else if (childNodeName.equals("ref_no"))
					{
						refNo = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("ref_date"))
					{
						refDate = childNode.getFirstChild().getNodeValue(); 
					}
					else if (childNodeName.equals("ref_mode"))
					{
						refMode = childNode.getFirstChild().getNodeValue(); 						
					}
					else if (childNodeName.equals("amount"))
					{
						amount = childNode.getFirstChild().getNodeValue();						
					}
					else if (childNodeName.equals("sundry_code"))
					{
						sundryCode = childNode.getFirstChild().getNodeValue(); 					
					}
					else if (childNodeName.equals("acct_code"))
					{
						acctCode = childNode.getFirstChild().getNodeValue(); 					
					}
					else if (childNodeName.equals("cctr_code"))
					{
						cctrCode = childNode.getFirstChild().getNodeValue();					
					}
					else if (childNodeName.equals("amt_adj"))
					{
						amtAdj = childNode.getFirstChild().getNodeValue(); 					
					}
					else if (childNodeName.equals("remarks"))
					{
						remarks = childNode.getFirstChild().getNodeValue();					
					}
					else if (childNodeName.equals("curr_code"))
					{
						currCode = childNode.getFirstChild().getNodeValue();					
					}
					else if (childNodeName.equals("exch_rate"))
					{
						exchRate = childNode.getFirstChild().getNodeValue();					
					}
				} //End for()
				if (exchRate.equals(""))
				{
					xmlStr = getBanklogExchRate(tranSer, tranNo);
					dom1 = genericUtility.parseString(xmlStr); 
					currCode = genericUtility.getColumnValue("curr_code",dom1);				
					exchRate = genericUtility.getColumnValue("exch_rate",dom1);					
				}
				amtAdjBase = Double.parseDouble(amtAdj) * Double.parseDouble(exchRate);
				amtCheck = 	Double.parseDouble(amtAdj) + Double.parseDouble(amtAdj);
				amount1 = Double.parseDouble(amount);
				//comp = Double.compare(amount1, amtCheck);//Commented as it is not supported in jdk.3
				//if (comp == 0)
				if (amount1 == amtCheck)
				{
					status = "R";
				}
				else
				{
					status = "P";
				}
			}//End of If()
			else
			{*/ //End Comment Gulzar 15/11/06
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			for (int cntr = 0; cntr < parentNodeListLength; cntr++)
			{
				parentNode = parentNodeList.item(cntr);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for (int cnt2 = 0; cnt2 < childNodeListLength; cnt2++)
				{
					childNode = childNodeList.item(cnt2);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals("tran_id"))
					{
						 tranId = childNode.getFirstChild().getNodeValue(); 
						 tranIdList.add(cntr,tranId);
					}
					if (childNodeName.equals("amount"))
					{
						amount = childNode.getFirstChild().getNodeValue(); 
						amountList.add(cntr,amount);
					}
					if (childNodeName.equals("amt_adj"))
					{
						amtAdj = childNode.getFirstChild().getNodeValue(); 		
						amtAdjList.add(cntr,amtAdj); //Gulzar 15/11/06
					}
				}//end inner for
			counter++;
			}//end outer for
			int loop = amountList.size();
			/* --Commented Changes below by Gulzar 15/11/06
			for (int c = 0; c < loop; c++)
			{
				amt2 = Double.parseDouble(amountList.get(c).toString()) - Double.parseDouble(amtAdj);
				amountList.add(c, new Double(amt2));
				amt = amt + Double.parseDouble(amountList.get(c).toString());					
			}
			*///End Comment Gulzar 15/11/06
			//Added By Gulzar 15/11/06
			for (int c = 0; c < loop; c++)
			{
				amt = amt + Double.parseDouble(amountList.get(c).toString());	//Sum the original amount
				amt2 = Double.parseDouble(amountList.get(c).toString()) - Double.parseDouble(amtAdjList.get(c).toString()); 
				amountList.set(c, new Double(amt2));
				amt3 = amt3 + Double.parseDouble(amountList.get(c).toString());	//Sum the amount after adjusted				
			} 
			//End Addition gulzar 15/11/06
			System.out.println("Sum Of Original Amount (amt) is : " + amt);
			System.out.println("Sum Of Amount After adjusted (amt3) is : " + amt3);
			System.out.println("Amount in amountList after update : " + amountList);
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			childNodeListLength = childNodeList.getLength();
			for (int cnt1 = 0; cnt1 < childNodeListLength; cnt1++)
			{
				childNode = childNodeList.item(cnt1);
				childNodeName = childNode.getNodeName();
				if (childNodeName.equals("tran_type"))
				{
					tranType = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("tran_date"))
				{
					tranDate = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("bank_code"))
				{
					bankCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("site_code"))
				{
					siteCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("tran_ser"))
				{
					tranSer = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("tran_no"))
				{
					tranNo = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("ref_no"))
				{
					refNo = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("ref_date"))
				{
					refDate = childNode.getFirstChild().getNodeValue(); 						
				}
				else if (childNodeName.equals("ref_mode"))
				{
					refMode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("sundry_code"))
				{
					sundryCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("acct_code"))
				{
					acctCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("cctr_code"))
				{
					cctrCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("remarks"))
				{
					remarks = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("curr_code"))
				{
					currCode = childNode.getFirstChild().getNodeValue(); 
				}
				else if (childNodeName.equals("exch_rate"))
				{
					exchRate = childNode.getFirstChild().getNodeValue(); 
				}
			}//for end
			status = "R";

			if (Double.parseDouble(exchRate) == 0)
			{
				xmlStr = getBanklogExchRate(tranSer, tranNo);
				dom1 = genericUtility.parseString(xmlStr); 
				currCode = genericUtility.getColumnValue("curr_code",dom1);
				exchRate = genericUtility.getColumnValue("exch_rate",dom1);					
			}
			amtAdjBase = Double.parseDouble(amtAdj) * Double.parseDouble(exchRate);				
			//}//End of Else() //Gulzar : removed the if block 

			/* insert data into the 'bank_statement' table */
			currDate = new java.sql.Date(System.currentTimeMillis());
			String effDate = genericUtility.getColumnValue("eff_date", headerDom);
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'W_BANK_STATEMENT'";
			stmt = conn.createStatement();
			rSet = stmt.executeQuery(keyStringQuery);
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			String uniqueKey = null;
			String insertSql = "";
			String updateSql = "";
			int insertCnt = 0;
			int updateCnt = 0;
			if (rSet.next())
			{
				keyString = rSet.getString(1);
				keyCol = rSet.getString(2);
				tranSer1 = rSet.getString(3);				
			}
			else
			{
				errCode = "VTSEQ";
				return errCode;
			}
			String xmlValues = "";
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", "");
			uniqueKey = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("uniqueKey :"+uniqueKey);
			if (uniqueKey.equals("ERROR"))
			{
				errCode = "VTTRANID";
				return errCode;
			}
			if (remarks == null)
			{
				remarks = " ";
			}
			/* Gulzar 15/11/06
			if (cnt > 1)
			{
				amtAdj = Double.toString(amt);
			}*///Gulzar 15/11/06
			//double amtAdj1 = Double.parseDouble(amtAdj); //Gulzar 15/11/06
			java.sql.Date effDate1 = java.sql.Date.valueOf(genericUtility.getValidDateString(effDate,"dd/MM/yy",genericUtility.getDBDateFormat()));
			System.out.println("effDate1 :"+effDate1);
			System.out.println("refDate :"+new java.sql.Date(System.currentTimeMillis()));
			String refDate1 = refDate.substring(0,10);
			java.sql.Date  refDate2 = java.sql.Date.valueOf(refDate1);
			System.out.println("refDate2 :"+refDate2);
			insertSql ="INSERT INTO BANK_STATEMENT(TRAN_ID, BANK_CODE, SITE_CODE, REF_NO, REF_DATE,"
					   +" REF_MODE, AMOUNT, TRAN_TYPE, EDI_REF, REMARKS, STATUS, CHG_DATE, CHG_USER, CHG_TERM,"
					   +" AMT_ADJ, EFF_DATE, AUTO_ENTRY, STATUS_DATE, EXCH_RATE, CURR_CODE , STAT_DATE)"
					   +" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement pstmt = conn.prepareStatement(insertSql);
			pstmt.setString(1, uniqueKey);
			pstmt.setString(2, bankCode);
			pstmt.setString(3, siteCode);
			pstmt.setString(4, refNo);
			pstmt.setDate(5, refDate2);
			pstmt.setString(6, refMode);
			pstmt.setDouble(7, amt);
			pstmt.setString(8, tranType);
			pstmt.setString(9, " ");
			pstmt.setString(10, remarks);
			pstmt.setString(11, status);
			pstmt.setDate(12, currDate);
			pstmt.setString(13, "Base");
			pstmt.setString(14, "Base");
			pstmt.setDouble(15, amt3);
			pstmt.setDate(16, effDate1); 
			pstmt.setString(17, "Y");
			pstmt.setDate(18, currDate);
			pstmt.setString(19, exchRate);
			pstmt.setString(20, currCode);
			pstmt.setDate(21, effDate1); 
			insertCnt = pstmt.executeUpdate();
			System.out.println("insertSql is :"+insertSql);
			System.out.println("insertCnt :"+insertCnt);
			System.out.println("counter :"+counter);
			if (insertCnt != 0)
			{
				for (int intcnt = 0; intcnt < counter; intcnt++)
				{
					/* -- Commented and Changes below by Gulzar 15/11/06
				   if (cnt == 1)
				   {
					   amt = Double.parseDouble(amtAdj);
					   System.out.println("amt from if :"+amt);
				   }
				   else
				   {
					   amt = Double.parseDouble(amountList.get(intcnt).toString());
					   System.out.println("amt from else :"+amt);
				   }*///End Comment Gulzar 15/11/06
					
					amt = Double.parseDouble(amountList.get(intcnt).toString());
					System.out.println("amt while inseting into bankreco_trace is :"+amt);
					
					insertSql = "insert into bankreco_trace(tran_id__stat, tran_id__log, amount) values (?, ?, ?)";
					System.out.println("insertSql :"+insertSql);
					System.out.println("tranIdList.get(intcnt).toString() :"+tranIdList.get(intcnt).toString());

					PreparedStatement pstmt1 = conn.prepareStatement(insertSql);
					pstmt1.setString(1, uniqueKey);
					pstmt1.setString(2, tranIdList.get(intcnt).toString());
					pstmt1.setDouble(3, amt);

					insertCnt = pstmt1.executeUpdate();
					System.out.println("insertCnt :"+insertCnt);
					System.out.println("cnt :"+cnt);
					/* -- Commented and Changes below by Gulzar 15/11/06
					if (cnt ==1)
					{
						amtAdj1 = amtAdj1 + amtAdj1;
						System.out.println("amtAdj1 :"+amtAdj1);

					}
					else
					{
						amtAdj1 = Double.parseDouble(amountList.get(intcnt).toString());
						System.out.println("amtAdj1 :"+amtAdj1);
					}*///End Comment Gulzar 15/11/06
					
					updateSql = "UPDATE BANKTRAN_LOG SET STATUS = ?, AMT_ADJ = AMT_ADJ + ?, EXCH_RATE = ?, CURR_CODE = ?"
								+" WHERE TRAN_ID = ?";
					System.out.println("updateSql  :"+updateSql);
					PreparedStatement pstmt2 = conn.prepareStatement(updateSql);
					pstmt2.setString(1, status);
					pstmt2.setDouble(2, amt);
					pstmt2.setDouble(3, Double.parseDouble(exchRate));
					pstmt2.setString(4, currCode);
					pstmt2.setString(5, tranIdList.get(intcnt).toString());
					updateCnt = pstmt2.executeUpdate();
					System.out.println("updateCnt :"+updateCnt);
				}//for end
				conn.commit();
				// 12/05/09 manoharan changed the message
				//errCode = "VMSUCC";
				errCode = "VTPRC1";
			}
			else
			{
				errCode = "VTPROCUS";
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		// 12/05/09 manoharan passed connection not to be closed
		/*finally
		{
			try
			{
				System.out.println("Closing Connection......");
				conn.close();
				conn = null;
			}
			catch(SQLException se){}
		}*/
		System.out.println("errString: "+errCode);
		return errCode;
	}
	
	private String getBanklogExchRate(String tranSer, String tranNo) throws RemoteException,ITMException		
	{
		System.out.println("TranSer :"+tranSer);
		String currCode = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		double exchRate = 0;
		Statement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		boolean isSql = false; 
		ConnDriver connDriver = new ConnDriver();
		String sql = "";
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			stmt = conn.createStatement();
			if (tranSer.equals("E-PAY"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM PAYMENT_EXP WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("F-XFR"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM FUNDTRANSFER WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			} 
			else if (tranSer.equals("M-PAY"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM MISC_PAYMENT WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("M-RCP"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM MISC_RECEIPT WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("MPCANC"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM MISC_PAYMENT_CANC WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("R-DIS"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM RCPDISHNR WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("RCP"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM RECEIPT WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			else if (tranSer.equals("E-FUND"))
			{
				sql = "SELECT CURR_CODE, EXCH_RATE FROM EXPORT_FUND WHERE TRAN_ID = '"+ tranNo +"'";
				isSql = true;
			}
			if (isSql)
			{
				rs = stmt.executeQuery(sql);	
				if (rs.next())
				{
					valueXmlString.append("<Detail>\r\n");
					valueXmlString.append("<curr_code>").append(rs.getString(1)).append("</curr_code>\r\n")	;
					valueXmlString.append("<exch_rate>").append(rs.getString(2)).append("</exch_rate>\r\n");
					valueXmlString.append("</Detail>\r\n");
				}
			}
			stmt.close();
			valueXmlString.append("</Root>\r\n");			
		}
		catch(Exception e)
		{
			System.out.println("Exception :ITMDBAccessEJB :getBanklogExchRate(String xmlString, String xmlString2):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.......");
				conn.close();
				conn = null;
			}catch(Exception se){}
		}
		System.out.println("valueXmlString.toString() :"+valueXmlString.toString());
		return valueXmlString.toString();
	}
}