package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.mfg.MfgCommon;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import javax.ejb.Stateless;

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

@Stateless
public class WorderCampaign extends ProcessEJB implements WorderCampaignLocal, WorderCampaignRemote {
    GenericUtility genericUtility = GenericUtility.getInstance();
    ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

    @Override
    public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
	System.out.println("^^^^^^^ Inside WorderCampaign -> GetData Method 1 ^^^^^^^");
	Document detailDom = null;
	Document headerDom = null;
	String retString = "";

	System.out.println("XMLSTRING ..> " + xmlString);
	System.out.println("XMLSTRING2 ..> " + xmlString2);

	try {
	    if (xmlString != null && xmlString.trim().length() != 0) {
		headerDom = genericUtility.parseString(xmlString);
	    }
	    if (xmlString2 != null && xmlString2.trim().length() != 0) {
		detailDom = genericUtility.parseString(xmlString2);
	    }
	    retString = getData(headerDom, detailDom, windowName, xtraParams);
	} catch (Exception e) {
	    System.out.println("Exception WorderCampaign GetData Method 1");
	    e.printStackTrace();
	    retString = itmDBAccessEJB.getErrorString("", "", "", 1); // failed to execute
	    throw new ITMException(e);
	}
	return retString;
    }

    @Override
    public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException, ITMException {
	System.out.println("===========Inside WorderCampaign -> GetData Method 2 ================");
	String retString = "";
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	//	Node childNode = null;
	ConnDriver connDriver = null;
	Connection conn = null;
	PreparedStatement pStmt = null;
	ResultSet rs = null;
	String sql = "",workOrdType="";
	String subQuery=" ";
	String workOrdType1="",resultWorkOrdType="",workOrdTypeSplit="";
	ITMDBAccessEJB itmDBAccessEJB = null;
	boolean isError = false;

	SimpleDateFormat sdf = null;
	StringBuffer resultData = new StringBuffer();
	boolean dataFound = false;

	String siteCode = "", fromDate = "", toDate = "";
	java.sql.Date fromDateDtSql = null, toDateDtSql = null;
	java.util.Date fromDateDtUtil = null, toDateDtUtil = null;
	String ordDate="",relDate="",dueDate="",workOrder="",tranID="",orderType="",itemCode="",status="",statDate="",unitStd="",tranMps="",
			acctWp="",cctrWp="",shdscr="",lotNo="",bomCode="",phyAtt1="",phyAtt2="",phyAtt3="",phyAtt4="",phyAtt5="",phyAtt6="",
			addUser="",addTerm="",addDate="",chgDate="",chgUser="",chgTerm="",valMfg="",campNo="",loginSiteCode="";
	Double quantity = 0.0,currQty=0.0,conQstd=0.0,stdQty=0.0,quanMps=0.0;
	

	try {
	    sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
	    connDriver = new ConnDriver();
	    conn = connDriver.getConnectDB("DriverITM");
	    conn.setAutoCommit(false);
	    connDriver = null;
	    itmDBAccessEJB = new ITMDBAccessEJB();

	    
	    loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
	    System.out.println("loginSiteCode :"+loginSiteCode);
	    parentNodeList = detailDom.getElementsByTagName("Detail1");
	    parentNode = parentNodeList.item(0);
	    childNodeList = parentNode.getChildNodes();
	    for (int i = 0; i < childNodeList.getLength(); i++) {
	
		
		fromDate = genericUtility.getColumnValue("date_from", headerDom);
		toDate = genericUtility.getColumnValue("date_to", headerDom);
		System.out.println(">>>>>>fromDate"+fromDate+">>>>D"+toDate);
		
			System.out.println(">>>>>>>>>>MNNNN");
	    fromDateDtUtil = sdf.parse(fromDate);
		fromDateDtSql = new java.sql.Date(fromDateDtUtil.getTime());
		
		
			System.out.println(">>>>>>>>>>MNNNN>>>");
		toDateDtUtil = sdf.parse(toDate);
		toDateDtSql = new java.sql.Date(toDateDtUtil.getTime());
		
		
	    }
	    //Start Added by chandrashekar on 12-Feb-15
	    workOrdType = new MfgCommon().getEnvMfg("999999","WORK_ORD_TYPE", conn);
	    System.out.println("workOrdType>>>>["+workOrdType+"]");
		if (workOrdType == null || "NULLFOUND".equalsIgnoreCase(workOrdType))
		{
			workOrdType = "";
		}
		if(workOrdType != null && (workOrdType.trim().length() > 0))
		{
			String[] arrStr =workOrdType.split(",");
			int len =arrStr.length;
			for(int i =0;i<len;i++)
			{
				 workOrdType1 =arrStr[i];
				 workOrdTypeSplit=workOrdTypeSplit+"'"+workOrdType1+"',";
			}
			resultWorkOrdType = workOrdTypeSplit.substring(0, workOrdTypeSplit.length() - 1);
			subQuery= " AND (WORKORDER.ORDER_TYPE in("+resultWorkOrdType+"))";
		}
		//End Added by chandrashekar on 12-Feb-15
	    sql ="SELECT WORKORDER.TRAN_ID,WORKORDER.WORK_ORDER,"
	    		+ "WORKORDER.ORDER_TYPE,"
	    		+ "WORKORDER.ITEM_CODE,WORKORDER.SITE_CODE,WORKORDER.QUANTITY,"
	    		+ "WORKORDER.ORD_DATE,WORKORDER.REL_DATE,WORKORDER.DUE_DATE,WORKORDER.STATUS,WORKORDER.STATUS_DATE,WORKORDER.ACCT_CODE__WP,"
	    		+ "WORKORDER.CCTR_CODE__WP,ITEM.SH_DESCR,WORKORDER.LOT_NO,WORKORDER.BOM_CODE,WORKORDER.CURRENT_BATCH_QTY,WORKORDER.VAL_MFG_TAXTRAN,"
	    		+ "WORKORDER.UNIT__STD,WORKORDER.CONV__QTY_STDUOM,WORKORDER.STD_QTY,WORKORDER.QUANTITY__MPS,"
	    		+ "ITEM.PHY_ATTRIB_1,ITEM.PHY_ATTRIB_2,ITEM.PHY_ATTRIB_3,"
	    		+ "ITEM.PHY_ATTRIB_4,ITEM.PHY_ATTRIB_5,ITEM.PHY_ATTRIB_6,"
	    		+ "WORKORDER.TRAN_ID__MPS,"
	    		+ "WORKORDER.ADD_DATE,WORKORDER.ADD_USER,WORKORDER.ADD_TERM,WORKORDER.CHG_DATE,"
	    		+ "WORKORDER.CHG_USER,WORKORDER.CHG_TERM,WORKORDER.CAMPGN_NO"
	    		+ " FROM"
	    		+ "  WORKORDER  WORKORDER,ITEM  ITEM  WHERE  ( WORKORDER.ITEM_CODE      = ITEM.ITEM_CODE   ) AND ( WORKORDER.ORD_DATE    >=      ? )  AND ( WORKORDER.ORD_DATE    <=      ? )" 
	    		//+ " AND WORKORDER.ORDER_TYPE='F' "
	    		+ " AND WORKORDER.STATUS IN('P','M','U') AND WORKORDER.SITE_CODE = ? " //login sitecode added by mahendra dated 07-JAN-2014
	    		+ " AND WORKORDER.CAMPGN_NO IS NULL"+subQuery;//subQuery condition added by chandrashekar on 12-feb-15
	    		
	    pStmt = conn.prepareStatement(sql);
	    pStmt.setDate(1, fromDateDtSql);
		pStmt.setDate(2, toDateDtSql);
		pStmt.setString(3, loginSiteCode);
	    rs = pStmt.executeQuery();
	    while (rs.next()) {
		dataFound = true;
		
		tranID = rs.getString("TRAN_ID") == null ? "" : rs.getString("TRAN_ID");
		workOrder = rs.getString("WORK_ORDER") == null ? "" : rs.getString("WORK_ORDER").trim();
		bomCode = rs.getString("BOM_CODE") == null ? "" : rs.getString("BOM_CODE").trim();
		itemCode = rs.getString("ITEM_CODE") == null ? "" : rs.getString("ITEM_CODE").trim();
		siteCode = rs.getString("SITE_CODE") == null ? "" : rs.getString("SITE_CODE").trim();
		shdscr = rs.getString("SH_DESCR") == null ? "" : rs.getString("SH_DESCR").trim();
		//lotNo = rs.getString("LOT_NO") == null ? "" : rs.getString("LOT_NO").trim();
		//quantity = rs.getString("QUANTITY") == null ? 0.0 : rs.getDouble("QUANTITY");
		//tranMps = rs.getString("TRAN_ID__MPS") == null ? "" : rs.getString("TRAN_ID__MPS");
		status = rs.getString("STATUS") == null ? "" : rs.getString("STATUS").trim();
		statDate = rs.getString("STATUS_DATE") == null ? "" : sdf.format(rs.getDate("STATUS_DATE"));
		//relDate = rs.getString("REL_DATE") == null ? "" : sdf.format(rs.getDate("REL_DATE"));
		//dueDate = rs.getString("DUE_DATE") == null ? "" : sdf.format(rs.getDate("DUE_DATE"));
		//orderType = rs.getString("ORDER_TYPE") == null ? "" : rs.getString("ORDER_TYPE").trim();
		//ordDate = rs.getString("ORD_DATE") == null ? "" : sdf.format(rs.getDate("ORD_DATE"));
		//acctWp = rs.getString("ACCT_CODE__WP") == null ? "" : rs.getString("ACCT_CODE__WP").trim();
		//cctrWp = rs.getString("CCTR_CODE__WP") == null ? "" : rs.getString("CCTR_CODE__WP").trim();
	   // currQty = rs.getString("CURRENT_BATCH_QTY") == null ? 0.0 : rs.getDouble("CURRENT_BATCH_QTY");
	   // valMfg = rs.getString("VAL_MFG_TAXTRAN") == null ? "" : rs.getString("VAL_MFG_TAXTRAN").trim();
	   // unitStd = rs.getString("UNIT__STD") == null ? "" : rs.getString("UNIT__STD").trim();
		//stdQty =   rs.getString("STD_QTY") == null ? 0.0 : rs.getDouble("STD_QTY");
		//conQstd =  rs.getString("CONV__QTY_STDUOM") == null ? 0.0 : rs.getDouble("CONV__QTY_STDUOM");
		//quanMps = rs.getString("QUANTITY__MPS") == null ? 0.0 : rs.getDouble("QUANTITY__MPS");
		//phyAtt1 = rs.getString("PHY_ATTRIB_1") == null ? "" : rs.getString("PHY_ATTRIB_1").trim();
		//phyAtt2 = rs.getString("PHY_ATTRIB_2") == null ? "" : rs.getString("PHY_ATTRIB_2").trim();
		//phyAtt3 = rs.getString("PHY_ATTRIB_3") == null ? "" : rs.getString("PHY_ATTRIB_3").trim();
		//phyAtt4 = rs.getString("PHY_ATTRIB_4") == null ? "" : rs.getString("PHY_ATTRIB_4").trim();
		//phyAtt5 = rs.getString("PHY_ATTRIB_5") == null ? "" : rs.getString("PHY_ATTRIB_5").trim();
	//	phyAtt6 = rs.getString("PHY_ATTRIB_6") == null ? "" : rs.getString("PHY_ATTRIB_6").trim();
	//	addDate = rs.getString("ADD_DATE") == null ? "" : sdf.format(rs.getDate("ADD_DATE"));
	   // addUser = rs.getString("ADD_USER") == null ? "" : rs.getString("ADD_USER").trim();
	  //  addTerm = rs.getString("ADD_TERM") == null ? "" : rs.getString("ADD_TERM").trim();
	    chgDate = rs.getString("CHG_DATE") == null ? "" : sdf.format(rs.getDate("CHG_DATE"));
	    chgUser = rs.getString("CHG_USER") == null ? "" : rs.getString("CHG_USER").trim();
		chgTerm = rs.getString("CHG_TERM") == null ? "" : rs.getString("CHG_TERM").trim();
		campNo = rs.getString("CAMPGN_NO") == null ? "" : rs.getString("CAMPGN_NO").trim();
		
		
		resultData.append(tranID).append("\t");
		resultData.append(workOrder).append("\t");
		resultData.append(bomCode).append("\t");
		resultData.append(itemCode).append("\t");
		resultData.append(shdscr).append("\t");
//		resultData.append(lotNo).append("\t");
//		resultData.append(quantity).append("\t");
//		resultData.append(tranMps).append("\t");
		resultData.append(status).append("\t");
		resultData.append(statDate).append("\t");
//		resultData.append(relDate).append("\t");
//		resultData.append(dueDate).append("\t");
//		resultData.append(orderType).append("\t");
//		resultData.append(ordDate).append("\t");
		resultData.append(siteCode).append("\t");
//		resultData.append(acctWp).append("\t");
//		resultData.append(cctrWp).append("\t");
//		resultData.append(currQty).append("\t");
//		resultData.append(valMfg).append("\t");
//		resultData.append(unitStd).append("\t");
//		resultData.append(stdQty).append("\t");
//		resultData.append(conQstd).append("\t");
//		resultData.append(phyAtt1).append("\t");
//		resultData.append(phyAtt2).append("\t");
//		resultData.append(phyAtt3).append("\t");
//		resultData.append(phyAtt4).append("\t");
//		resultData.append(phyAtt5).append("\t");
//		resultData.append(phyAtt6).append("\t");
		resultData.append(chgDate).append("\t");
		resultData.append(chgUser).append("\t");
		resultData.append(chgTerm).append("\t");
//		resultData.append(addDate).append("\t");
//		resultData.append(addUser).append("\t");
//		resultData.append(addTerm).append("\t");
		resultData.append(campNo).append("\t");
		
		
		
		resultData.append("\n");
	    }
	    if(!rs.next())
	    {
	    	System.out.println(">>>>>faildsssss");
		
		    retString = itmDBAccessEJB.getErrorString("", "", "", "", conn);
	    }
	    rs.close();
	    rs = null;
	    pStmt.close();
	    pStmt = null;
	} catch (Exception e) {
	    isError = true;
	    throw new ITMException(e);
	} finally {
	    try {
		if (conn != null) {
		    if (isError) {
			retString = itmDBAccessEJB.getErrorString("", "VTWOCFAIL", "", "", conn); // failed to execute 
		    } else {
			retString = resultData.toString();
			System.out.println("RETSTRING ..> " + retString);

			if (!dataFound) {
			    retString = itmDBAccessEJB.getErrorString("", "VTNOREC1", "", "", conn); // NO RECORD FOUND
			}
		    }
		    conn.close();
		    conn = null;
		}
		if (rs != null) {
		    rs.close();
		    rs = null;
		}
		if (pStmt != null) {
		    pStmt.close();
		    pStmt = null;
		}
	    } catch (Exception e) {
		retString = e.getMessage();
		throw new ITMException(e);
	    }
	}
	return retString;
    }

     @Override
    public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
	System.out.println("^^^^^^^ Inside process -> Process Method 1 ^^^^^^^");
	Document detailDom = null;
	Document headerDom = null;
	String retString = "";

	System.out.println("XMLSTRING ..> " + xmlString);
	System.out.println("XMLSTRING2 ..> " + xmlString2);

	try {
	    if (xmlString != null && xmlString.trim().length() != 0) {
		headerDom = genericUtility.parseString(xmlString);
	    }
	    if (xmlString2 != null && xmlString2.trim().length() != 0) {
		detailDom = genericUtility.parseString(xmlString2);
	    }
	    retString = process(headerDom, detailDom, windowName, xtraParams);
	} catch (Exception e) {
	    System.out.println("Exception process Process Method 1");
	    e.printStackTrace();
	    retString = itmDBAccessEJB.getErrorString("", "VTPFTEXE", "", 1); // failed to execute
	    throw new ITMException(e);
	}
	return retString;
    }
     
    @Override
    public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException, ITMException {
	System.out.println("^^^^^^^ Inside process -> Process Method 2 ^^^^^^^");
	String retString = "";
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	Node childNode = null;
	ConnDriver connDriver = null;
	Connection conn = null;
	PreparedStatement pStmt = null;
	ResultSet rs = null;
	String sql = "";
	String woItemCode = "";//Added by chandrashekar on 09-oct-14
	ITMDBAccessEJB itmDBAccessEJB = null;
	boolean isError = false;
	SimpleDateFormat sdf = null;

	ArrayList<String> headerList = new ArrayList<String>();
	String errCode = "";
	int childNodeListLength = 0;
	String childNodeName = "";
    String tranID="";
    PreparedStatement pstmt=null;
	String errString="",loginCode="",loginSite="",systemDate="";
	Calendar currentDate;
	SimpleDateFormat simpleDateFormatObj;
	java.sql.Timestamp currDate = null;
	String currAppdate="";
	Timestamp sysDate = null;
    int count=0;
	ITMDBAccessLocal itmdbAccess=new ITMDBAccessEJB();
	String wostatus="",workorder="",chgTerm="",chgUser="",utranid="";
	GenericUtility genutility = null;
	HashMap<String, String> comMap=new HashMap<String, String>();
	
	try {
	    connDriver = new ConnDriver();
	    conn = connDriver.getConnectDB("DriverITM");
	    conn.setAutoCommit(false);
	    connDriver = null;
	    itmDBAccessEJB = new ITMDBAccessEJB();
	    sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());

	    parentNodeList = detailDom.getElementsByTagName("Detail2");
	    for (int i = 0; i < parentNodeList.getLength(); i++) {
		parentNode = parentNodeList.item(i);
		childNodeList = parentNode.getChildNodes();
		childNodeListLength = childNodeList.getLength();
		for (int j = 0; j < childNodeListLength; j++) {
		    childNode = childNodeList.item(j);
		    childNodeName = childNode.getNodeName();
		    if (childNodeName.equals("work_order")) {
		    	workorder = childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
		    }
		}

		workorder = workorder == null ? "" : workorder.trim();
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_term");
		chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
		System.out.println(">>>>>>>chgTerm and  t"+chgTerm+">>>>> chgUser"+chgUser);
		currentDate = Calendar.getInstance();
	/*	simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
		systemDate = simpleDateFormatObj.format(currentDate.getTime());
		System.out.println("Now the date is :=>  " + systemDate);*/
		
		genutility=GenericUtility.getInstance();
		sdf=new SimpleDateFormat(genutility.getApplDateFormat());
		currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
		currAppdate = sdf.format(currDate);
		currAppdate = genericUtility.getValidDateString( currAppdate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat() );
		 sysDate = java.sql.Timestamp.valueOf( currAppdate + " 00:00:00.0" );
		/*loginCode=GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		loginCode=loginCode==null ? "" :loginCode.trim();*/
		loginSite = GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
		System.out.println("loginCode---->>["+loginCode+"]");
		conn.setAutoCommit(false);
		
		
		System.out.println("loginCode"+loginCode+"<<<<<<<loginSite"+loginSite);
		
		//Start Added by chandrashekar  on 09-oct-14
		sql = "select item_code from workorder where work_order = ?";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,workorder);
		rs = pstmt.executeQuery();
		if(rs.next())
		{
			woItemCode = rs.getString("item_code") == null ? "":rs.getString("item_code");
		}
		pstmt.close();
		rs.close();
		pstmt = null;
		rs = null;
		if(comMap.containsKey(woItemCode))
		{
			 utranid= comMap.get(woItemCode);
					
		}
		else
		{	utranid = generateTranId("w_wocampaign", loginSite, conn);
			System.out.println("GENERATED TRAN ID"+utranid);
			comMap.put(woItemCode, utranid);
			System.out.println("comMap[["+comMap+"]]");
		}
		//end by chandrashekar on on 09-oct-14
		
		/*if(utranid.trim().length() <= 0)
		{
		utranid = generateTranId("w_wocampaign", loginSite, conn);
	    }
		
		System.out.println("GENERATED TRAN ID"+utranid);*/
		
		
		System.out.println("WORK ORDER NO"+workorder);
		sql = "select status from workorder where work_order = ?";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1,workorder);
		rs = pstmt.executeQuery();
		if(rs.next())
		{
			wostatus = rs.getString(1) == null ? "":rs.getString(1);
		}
		pstmt.close();
		rs.close();
		pstmt = null;
		rs = null;
		System.out.println("wostatusWEWEWEE >>>>>>>>>>>>>>>>>>."+wostatus);
		if(wostatus.equalsIgnoreCase("P") || wostatus.equalsIgnoreCase("M") || wostatus.equalsIgnoreCase("U"))
		{   
			
			    System.out.println("ENTER IN STATUS CHECK >>>>>");
				sql = "UPDATE WORKORDER SET CAMPGN_NO = ? WHERE WORK_ORDER = ?";
                pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,utranid);
				pstmt.setString(2,workorder);
				count = pstmt.executeUpdate();
				if(count < 1)
			 	{
					System.out.println("updation of record is failed");
				}
				else
				{
					conn.commit();
				}
				
				pstmt.close(); 
				pstmt = null;  
				System.out.println("NO. OF RECORDS UPDATED FOR CURRENT PERIODE.. "+count);
			
			
			
		   
			sql = "INSERT INTO WORDER_CAMPAIGN (WORK_ORDER,CAMPGN_NO,CHG_DATE,CHG_USER,CHG_TERM) VALUES (?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,workorder);
			pstmt.setString(2,utranid);
			pstmt.setTimestamp(3,sysDate);
			pstmt.setString(4,chgUser);
			pstmt.setString(5,chgTerm);
			count = pstmt.executeUpdate();
			if(count < 1)
		 	{
				System.out.println("Insertion of record is failed");
			}
			else
			{
				
				conn.commit();
				System.out.println(">>>>>process completed>>>>VV>>>>");
				errCode = "VTWOCPRCGN"; // generated successfully
			    retString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
			}
			
			pstmt.close(); 
			pstmt = null;  
			System.out.println("NO. OF RECORDS INSERTED FOR CURRENT PERIODE.. "+count);
			   
			
		   
		
	    }
	    }
	}catch (Exception e) {
	    isError = true;
	    errCode = "VTPFTEXE"; // failed to execute
	    retString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
	    e.printStackTrace();
	    throw new ITMException(e);
	} finally {
	    try {
		if (conn != null) {
		    if (isError) {
			conn.rollback();
		    } else {
			conn.commit();
		    }
		    conn.close();
		    conn = null;
		}
		if (pStmt != null) {
		    pStmt.close();
		    pStmt = null;
		}
	    } catch (Exception e) {
		e.printStackTrace();
		throw new ITMException(e);
	    }
	}
	return retString;
    }

  
    
    public static String generateTranId(String windowName, String siteCode, Connection conn) throws ITMException {
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String selSql = "";
	String tranId = "";
	String tranSer = "";
	String keyString = "";
	String keyCol = "";
	String xmlValues = "";
	java.sql.Timestamp currDate = null;
	GenericUtility genericUtility = GenericUtility.getInstance();

	try {

	    SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
        currDate = new java.sql.Timestamp(System.currentTimeMillis());
	    String currDateStr = sdfAppl.format(currDate);
        selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
	    pstmt = conn.prepareStatement(selSql);
	    pstmt.setString(1, windowName);
	    rs = pstmt.executeQuery();
	    if (rs.next()) {
		keyString = rs.getString("KEY_STRING");
		keyCol = rs.getString("TRAN_ID_COL");
		tranSer = rs.getString("REF_SER");
	    }
	    rs.close();
	    rs = null;
	    pstmt.close();
	    pstmt = null;
	    //keyString = "seq10";
	    System.out.println("keyString :" + keyString);
	    System.out.println("keyCol :" + keyCol);
	    System.out.println("tranSer :" + tranSer);

	    xmlValues = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
	    xmlValues = xmlValues + "<Header></Header>";
	    xmlValues = xmlValues + "<Detail1>";
	    xmlValues = xmlValues + "<tran_id></tran_id>";
	    xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
	    xmlValues = xmlValues + "<tran_date>" + currDateStr + "</tran_date>";
	    xmlValues = xmlValues + "</Detail1></Root>";
	    System.out.println("xmlValues  :[" + xmlValues + "]");
	    TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
	    tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
	    System.out.println("tranId :" + tranId);
	} catch (SQLException ex) {
	    System.out.println("Exception ::" + selSql + ex.getMessage() + ":");
	    ex.printStackTrace();
	    throw new ITMException(ex);
	} catch (Exception e) {
	    System.out.println("Exception ::" + e.getMessage() + ":");
	    e.printStackTrace();
	    throw new ITMException(e);
	} finally {
	    try {
		if (rs != null) {
		    rs.close();
		    rs = null;
		}
		if (pstmt != null) {
		    pstmt.close();
		    pstmt = null;
		}
	    } catch (Exception e) {
	    }
	}
	return tranId;
    }
}