/*
    Window Name : w_wavegen
*/

package ibase.webitm.ejb.wms;

import ibase.bi.ejb.session.TotalManager;
import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ITMDBAccessLocal;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.reports.utility.JasperReportGenerator;
import ibase.webitm.reports.utility.XSDParser;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.awt.print.PrinterJob;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
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.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeMap;
import java.util.TreeSet;

import javax.mail.Flags.Flag;
import javax.naming.InitialContext;
import javax.print.PrintService;

//import net.sf.jasperreports.governors.GovernorException;

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

@javax.ejb.Stateless
public class DeallocArtPrc extends ProcessEJB implements WaveGenerationPrcLocal,WaveGenerationPrcRemote //SessionBean
{
	
	
	InvAllocTraceBean invAllocTrace = new InvAllocTraceBean();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();	
	CommonConstants commonConstants = new CommonConstants();
	DistCommon discommon = new DistCommon();
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :DeallocArtPrc :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
  
		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
		String sql= "" ;
		String resultString = "";
		String ptcn ="";
		String lineNoSord = "";	
		String siteCode = "";
		double quantity = 0.0;
		
		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement pstmt = null;
		Statement st = null;
		
		StringBuffer retTabSepStrBuff = new StringBuffer();			
		
		try
		{
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;			
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			ptcn = genericUtility.getColumnValue("ptcn",headerDom);
			if ( ptcn == null || ptcn.trim().length() == 0 )
			{
				ptcn = "";
				System.out.println("PTCN From is Null...");
				errString = itmDBAccessEJB.getErrorString("","VTMRPPARM","","",conn);
				return errString;
			} 
			
			getDataSql = " SELECT CM.CARTON_NO, POD.SALE_ORDER, POD.LINE_NO__SORD, CM.PICK_ORDER, CM.LINE_NO__PICK, " +
						" POD.ITEM_CODE,  POD.LOC_CODE, POD.LOT_NO, POD.LOT_SL, WTD.WAVE_ID, POH.PICK_TYPE " +
						" FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD WHERE CM.PTCN = ? " +
						" AND CM.PICK_ORDER = POD.PICK_ORDER AND POH.PICK_ORDER = POD.PICK_ORDER " +
						" AND CM.LINE_NO__PICK = POD.LINE_NO AND WTD.PTCN = CM.PTCN " +
						" AND WTD.REF_ID = CM.PICK_ORDER  AND POH.PICK_TYPE IN (?,?) " +
						" AND ( FN_CHECK_DEALLOC_ARTDET(CM.PTCN, CM.CARTON_NO ) = 0 ) "+					
						" AND CM.STATUS <> ? AND CM.CARTON_TYPE = 'C' ORDER BY CM.CARTON_NO ";		
				
			pstmt = conn.prepareStatement(getDataSql);		
			pstmt.setString(1, ptcn);		
			pstmt.setString(2, "C");//FOR CASE PICK
			pstmt.setString(3, "M");// FOR MASTER PICK
			pstmt.setString(4, "R");
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				do
				{					
					lineNoSord = rs.getString("LINE_NO__SORD");
					
					//PTCN 
					retTabSepStrBuff.append(ptcn).append("\t");							
					
					//WAVE_ID
					retTabSepStrBuff.append(rs.getString("WAVE_ID")).append("\t");							
					
					//SITE_CODE
					retTabSepStrBuff.append(siteCode).append("\t");
					
					//SALE_ORDER
					retTabSepStrBuff.append(rs.getString("SALE_ORDER")).append("\t");					
					
					//LINE_NO__SORD
					lineNoSord = "   " + lineNoSord; 
					lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
					System.out.println(" sord line no ["+lineNoSord+"]");
					retTabSepStrBuff.append(lineNoSord).append("\t");				
					
					//CARTON_NO
					retTabSepStrBuff.append(rs.getString("CARTON_NO")).append("\t");					
					
					//PICK_ORDER
					retTabSepStrBuff.append(rs.getString("PICK_ORDER")).append("\t");					
					
					//LINE_NO__PICK
					retTabSepStrBuff.append(rs.getString("LINE_NO__PICK")).append("\t");
					
					//ITEM_CODE
					retTabSepStrBuff.append(rs.getString("ITEM_CODE")).append("\t");					
					
					//QUANTITY
					HashMap itmVolumeMap = getItemVoumeMap(rs.getString("ITEM_CODE"),"",conn);
					double packSizeActive = (Double)itmVolumeMap.get("PACK_SIZE");
					retTabSepStrBuff.append(packSizeActive).append("\t");					
					
					//PICK_TYPE
					retTabSepStrBuff.append(rs.getString("PICK_TYPE") ).append("\n");
					
				 }while(rs.next());	
				
				
			}			
			else
			{
				errCode ="VTNOREC2";
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;			
			resultString = retTabSepStrBuff.toString();
			System.out.println(" -- ResultString....." + resultString);			
			if (!errCode.equals(""))
			{
				resultString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn);
				System.out.println("resultString: "+resultString);
			}		
			
		}
		catch (SQLException e)
		{
			System.out.println("SQLException : :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			System.out.println("Exception : :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{		

			try
			{				
				if(conn != null)
				{		
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(rs1 != null)
					{
						rs1.close();
						rs1 = null;
					}
					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt=null;
					}
					
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return resultString;	
	}
	//Process Method

	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();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
									
		    retStr = process(headerDom, detailDom, windowName, xtraParams);
		    
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
		
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("detailDom------------------->"+detailDom);
		String childNodeName = "";
		String errCode = "";
		//String siteCode = this.siteCode;
		String waveId = "";
		//String saleOrder = null;
		String ptcn = "";
		String itemCode = null;
		String lineNo = null;
		String pickOrder = "";
		String cartonNo= "";
		String lineNoPick = "";
		String pickType = "";
		String sql = "";
		double quantity = 0;		
		String errString = "";	
		String chgUser = "";
		String chgTerm = "";
		String siteCode = "";
		String tranId = "";
		int tranLineNo = 0;
		java.sql.Date currDate = new java.sql.Date( System.currentTimeMillis() );	
		
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;		
		
		int parentNodeListLength = 0;
		int childNodeListLength = 0;		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;	
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		DeallocArtConfLocal deallocArtConfLocal = null;
		try
		{
			System.out.println(" xtraParams -->"+xtraParams);
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			ptcn = genericUtility.getColumnValue("ptcn",headerDom);
			
			System.out.println(" chgUser ["+chgUser+"] chgTerm["+chgTerm+"] siteCode["+siteCode+"] ptcn["+ptcn+"]");
			
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			
			sql = " SELECT DISTINCT WTD.WAVE_ID FROM CARTON_MASTER CM, PICK_ORD_DET POD , PICK_ORD_HDR POH, WAVE_TASK_DET WTD " +
					" WHERE CM.PTCN = ? AND CM.PICK_ORDER = POD.PICK_ORDER AND POH.PICK_ORDER = POD.PICK_ORDER " +
					" AND CM.LINE_NO__PICK = POD.LINE_NO AND WTD.PTCN = CM.PTCN AND WTD.REF_ID = CM.PICK_ORDER " +
					" AND POH.PICK_TYPE IN (?,?) AND CM.CARTON_TYPE = 'C'";
			pstmt =conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			pstmt.setString(2, "C");
			pstmt.setString(3, "M");
			rs =pstmt.executeQuery();
			if( rs.next() )
			{
				waveId = rs.getString("WAVE_ID");
			}
			
			tranId = getTranId("w_dealloc_art", siteCode, conn);
			sql = "";
			sql = " INSERT INTO DEALLOC_ART (TRAN_ID, SITE_CODE, PTCN ,WAVE_ID, CONFIRMED, CHG_DATE, CHG_USER, CHG_TERM) " +
					" VALUES (?,?,?,?,?,?,?,?)";  
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			pstmt.setString(2, siteCode);
			pstmt.setString(3, ptcn);
			pstmt.setString(4, waveId);
			pstmt.setString(5, "N");
			pstmt.setDate(6, currDate);
			pstmt.setString(7, chgUser );
			pstmt.setString(8, chgTerm );
			if( pstmt.executeUpdate() > 0)
			{
				System.out.println(" insertion in DEALLOC_ART successfully ");
			}			
			pstmt.close();pstmt = null;
			
			sql = "";
			sql = "INSERT INTO DEALLOC_ART_DET (TRAN_ID, LINE_NO, CARTON_NO, PICK_ORDER, LINE_NO__PICK, ITEM_CODE, QUANTITY, PICK_TYPE )   " +
					" VALUES (?,?,?,?,?,?,?,?)";  
			pstmt = conn.prepareStatement(sql);
 
			
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			System.out.println("parentNodeListLength------------------->"+parentNodeListLength);		
			
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				
				parentNode = parentNodeList.item(selectedRow);			
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("childNodeListLength---->>> "+ childNodeListLength);
				
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
 					System.out.println("childNodeName---->>> "+ childNodeName);
					
					if (childNodeName.equals("carton_no"))
					{
						cartonNo = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("pick_order"))
					{
						pickOrder = childNode.getFirstChild().getNodeValue();
					}

					if (childNodeName.equals("line_no__pick"))
					{
						lineNoPick = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("item_code"))
					{
						itemCode = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("pick_type"))
					{
						pickType = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("quantity"))
					{
					    
						quantity = Double.parseDouble(childNode.getFirstChild().getNodeValue());
						System.out.println("quantity *======>"+quantity);
					}						
			
				}
				
				pstmt.setString(1, tranId);
				pstmt.setInt(2, ++tranLineNo);
				pstmt.setString(3, cartonNo);
				pstmt.setString(4, pickOrder);
				pstmt.setString(5, lineNoPick);
				pstmt.setString(6, itemCode);
				pstmt.setDouble(7, quantity);
				pstmt.setString(8, pickType);				
				if( pstmt.executeUpdate() > 0 )
				{
					System.out.println(" insert in DEALLOC_ART_DET succesfully done ");
				}
				else
				{
					throw new Exception("Insertion fail in DEALLOC_ART_DET table");
				}
				pstmt.clearParameters();
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			conn.commit();
			
			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			deallocArtConfLocal = (DeallocArtConfLocal)ctx.lookup("ibase/DeallocArtConf/local"); 
			errString = deallocArtConfLocal.confirm(tranId, xtraParams, "");
			System.out.println(" ************ errString ******** ["+errString+"]");		
					
		}//try end	
		catch(Exception e)
		{
			e.printStackTrace();
			try
			{
				if ( conn != null && !conn.isClosed() )
				{
					conn.rollback();
				}
			}
			catch(Exception se )
			{
				se.printStackTrace();
				e = se;
			}			
			errString = e.getMessage();			
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("Closing Connection....");
			try
			{
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				return errString ;
			
			}
			if (errString == null || errString.trim().length() == 0 || errString.contains("VTSUCC1"))
			{				
				//errString="VTCOMPL";
				errString="VTSUCC1";
				errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				//return errString;				
			}			
			else
			{
				System.out.println("errString.indexOf(<Errors>)---------------"+errString.indexOf("<Errors>"));
				if(errString.indexOf("<Errors>") == -1 )
				{
					if( errString.trim().length() > 0)
					{
						System.out.println("Inside Block for ErrOr Not Found---------------");
						errString="VTPRCERR";
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					}
				}
				
				return errString;
				
			}
			
		}
		
		return errString;	
	}	
	
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}
			
			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			/*itemSize = Math.floor(itmLen * itmWidth * itmHeight);
			lotSize = Math.floor((lotHeight * lotWidth * lotLen));*/
			itemSize = itmLen * itmWidth * itmHeight;
			lotSize = lotHeight * lotWidth * lotLen;
			
			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {			

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	private String getTranId(String windowName, String siteCode, Connection conn) throws ITMException
	{
		String keyStringQuery = "";
		String xmlValues = "";
		String tranId = "";	
		ResultSet rSet = null ;
		PreparedStatement pstmt = null;
		CommonConstants commonConstants = new CommonConstants();

		java.sql.Timestamp currDate = null;

		try
		{
			
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ?";
	    	System.out.println("keyStringQuery--------->>"+keyStringQuery);
	    	
	    	pstmt = conn.prepareStatement(keyStringQuery);
	    	pstmt.setString(1,windowName);
	    	rSet = pstmt.executeQuery();	    	
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString("KEY_STRING");
				keyCol = rSet.getString("TRAN_ID_COL");
				tranSer1 = rSet.getString("REF_SER");
			}
			

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);
			//End Changes by Gulzar on 5/12/2012
			
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);
			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 +		"<desp_date>" + currDateStr + "</desp_date>"; 
			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);
			// Creating wave_id 
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			
			if(rSet != null)
			{
				rSet.close();
				rSet=null;
			}	
			if(pstmt != null)
			{
				pstmt.close();
				pstmt=null;
			}			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{	
				if(rSet != null)
				{
					rSet.close();
					rSet=null;
				}	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt=null;
				}				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return checkNull( tranId);
	}
}