package ibase.webitm.ejb.dis;
import java.rmi.RemoteException;
import java.sql.*;

import org.w3c.dom.*;

import ibase.utility.CommonConstants;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.*;
import ibase.system.config.*;

import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.Iterator;

import javax.ejb.Stateless; // added for ejb3
import ibase.webitm.ejb.dis.adv.*;

@Stateless
public class InvHoldRelGen extends ProcessEJB implements InvHoldRelGenLocal, InvHoldRelGenRemote
{

	DistCommon distCommonObj = new DistCommon();
	GenericUtility genericUtility = GenericUtility.getInstance();
	ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	@Override
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	//getData Method
	@Override
	public String process(Document dom, Document dom2, String windowName,
			String xtraParams) throws RemoteException, ITMException {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;	
		
		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 :InvHoldRelGenEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
			throw new ITMException(e);
		
		}
		return rtrStr; 
	}//END OF GETDATA(1)
	
	public String blanknull(String s)
	{
		if( s==null )
			return " ";
		else
			return s;
	}
	
	
	@Override
	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{

		String errCode = "";
		String errString = "";
		String getDataSql= "" ;
        String lockType="";
		String resultString = "";
		ResultSet rs1 = null,descrs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		PreparedStatement pstmt = null,descpstmt = null,pstmt1 = null, pstmt2 = null;
		// 09/04/14 manoharan
		//StringBuffer retTabSepStrBuff = new StringBuffer();
	    StringBuffer retTabSepStrBuff = new StringBuffer("<?xml version = \"1.0\"?>");
		retTabSepStrBuff.append("<DocumentRoot>");
		retTabSepStrBuff.append("<description>").append("Datawindow Root").append("</description>");
		retTabSepStrBuff.append("<group0>");
		retTabSepStrBuff.append("<description>").append("Group0 description").append("</description>");
		retTabSepStrBuff.append("<Header0>");
		int cnt=0,count=0,i=7;
		String sql="";
		String subSql="";
		String lockCodeFrom = "",lockCodeTo = "",tranIdFrom = "",tranIdTo="",refIdFrom="",refIdTo="",autoConfirmed="",schRelDateFromstr="",schRelDateTostr="";
		Timestamp schRelDateFrom=null,schRelDateTo=null;
		String lotNoFrom="",lotNoTo="",invoiceNo="",holdQuantity="";		
				
				
		ConnDriver connDriver = new ConnDriver();
		Connection conn= null;
		try
		{
			System.out.println("mahendra testing@@@@@@@@@ 12-09-2014 !!!!");
			System.out.println("windowName!!!!! "+windowName);
			
			
			if(conn==null)
			{
				conn = connDriver.getConnectDB("DriverITM");
				 
				conn.setAutoCommit(false);	
			}
			DatabaseMetaData dbmd = conn.getMetaData();
			System.out.println("DriverName["+dbmd.getDriverName() + "]");
			System.out.println("DriverURI["+dbmd.getURL()  + "]");
			System.out.println("DriverUSER["+dbmd.getUserName() +"]");
					
			System.out.println("InvHoldRelGen : getData() Method Called");
			
			tranIdFrom = genericUtility.getColumnValue("tran_id__from",headerDom);
			tranIdTo = genericUtility.getColumnValue("tran_id__to",headerDom);
			schRelDateFromstr = genericUtility.getColumnValue("sch_rel_date__from",headerDom);
			schRelDateTostr = genericUtility.getColumnValue("sch_rel_date__to",headerDom);
			refIdFrom = genericUtility.getColumnValue("ref_id__from",headerDom);
			refIdTo = genericUtility.getColumnValue("ref_id__to",headerDom);
			lockCodeFrom = genericUtility.getColumnValue("lock_code__from",headerDom);    //added by Ritesh on 08/05/13
			lockCodeTo = genericUtility.getColumnValue("lock_code__to",headerDom);		 //added by Ritesh on 08/05/13
			autoConfirmed = genericUtility.getColumnValue("auto_confirmed",headerDom);
			lotNoFrom = genericUtility.getColumnValue("lot_no__from",headerDom);//Added by manoj dtd 15/04/2014 to getdata based on entered lotNo
			lotNoTo = genericUtility.getColumnValue("lot_no__to",headerDom);//Added by manoj dtd 15/04/2014 to getdata based on entered lotNo
			
			
			
			//SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			System.out.println("schRelDateFromstr : ["+schRelDateFromstr+"]:::schRelDateTostr:["+schRelDateTostr+"]");
			if(schRelDateFromstr != null)
			{
				System.out.println("schRelDateFromstr : ["+schRelDateFromstr);
				schRelDateFrom= Timestamp.valueOf(genericUtility.getValidDateString(schRelDateFromstr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
			}
			if(schRelDateTostr != null)
			{
				System.out.println(" :::schRelDateTostr:["+schRelDateTostr+"]");
				//schRelDateTo= Timestamp.valueOf(genericUtility.getValidDateString(schRelDateTostr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");//Commented by Manoj dtd 04/10/2013 to set hrs and minutes also
				schRelDateTo= Timestamp.valueOf(genericUtility.getValidDateString(schRelDateTostr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 23:59:59.0");
			}
			System.out.println("schRelDateFrom : ["+schRelDateFrom+"]:::schRelDateTo:["+schRelDateTo+"]");
			System.out.println("tranIdFrom=>"+tranIdFrom);
			System.out.println("tranIdTo=>"+tranIdTo);
			System.out.println("refIdFrom =>"+refIdFrom);
			System.out.println("refIdTo =>"+refIdTo);
			System.out.println("autoConfirmed =>"+autoConfirmed);
			System.out.println("lockCodeFrom=>"+lockCodeFrom);			  //added by Ritesh on 08/05/13
			System.out.println("lockCodeTo=>"+lockCodeTo);				 //added by Ritesh on 08/05/13
			
			System.out.println("lotNoFrom=>"+lotNoFrom);			 
			System.out.println("lotNoTo=>"+lotNoTo);
			
			/*if ( tranIdFrom == null || tranIdFrom.trim().length() == 0 )
			{
				System.out.println("tran id from is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMTRIDFRNU","","",conn);
				return errString;
			}
			else
			{*/
			/*
				sql = "select count(1) from inv_hold_det where tran_id ='" + tranIdFrom + "'";
			    pstmt = conn.prepareStatement(sql);
			    rs1 = pstmt.executeQuery();
			    if (rs1.next())
			    {
				cnt = rs1.getInt(1);
			    }
			    pstmt.close();
			    rs1.close();
			    pstmt = null;
			    rs1 = null;
			    	if (cnt == 0)
			    	{
			    		errString = itmDBAccessEJB.getErrorString("","TRIDFRINV","","",conn);
			    		return errString;
			    	}
			 */   	
			//}
			
			/*if ( tranIdTo == null || tranIdTo.trim().length() == 0 )
			{
				System.out.println("tran id to is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMTRIDTONU","","",conn);
				return errString;
			}
			else
			{*/
			/*	
				sql = "select count(1) from inv_hold_det where tran_id ='" + tranIdTo + "'";
			    pstmt = conn.prepareStatement(sql);
			    rs1 = pstmt.executeQuery();
			    if (rs1.next())
			    {
				cnt = rs1.getInt(1);
			    }
			    pstmt.close();
			    rs1.close();
			    pstmt = null;
			    rs1 = null;
			    	if (cnt == 0)
			    	{
			    		errString = itmDBAccessEJB.getErrorString("","TRIDTOINV","","",conn);
			    		return errString;
			    	}
			  */  	
			//}
			
			/*if ( schRelDateFrom == null )
			{
				System.out.println("schedule release date from is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMSCDTFRNU","","",conn);
				return errString;
			}
			else
			{
				if(schRelDateTo != null)
				{
					if(schRelDateFrom.after(schRelDateTo))
					{
						errString = itmDBAccessEJB.getErrorString("","SCDTTOINV","","",conn);
			    		return errString;
					}
				}
			}
			*/
			
			/*if ( schRelDateTo == null )
			{
				System.out.println("schedule release date to is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMSCDTTONU","","",conn);
				return errString;
			}
			else
			{
				if(schRelDateTo != null)
				{
					if(schRelDateTo.before(schRelDateFrom))
					{
						errString = itmDBAccessEJB.getErrorString("","SCDTTOINV","","",conn);
			    		return errString;
					}
				}
			}*/
			
			
			/*if ( refIdFrom == null )
			{
				//refIdFrom="0";
				//System.out.println("ref id from is Null...");
				//errString = itmDBAccessEJB.getErrorString("","VMRFIDFRNU","","",conn);
				//return errString;
			}
			else
			{*/
			/*
				sql = "select count(1) from inv_hold where ref_id ='" + refIdFrom + "'";
			    pstmt = conn.prepareStatement(sql);
			    rs1 = pstmt.executeQuery();
			    if (rs1.next())
			    {
				cnt = rs1.getInt(1);
			    }
			    pstmt.close();
			    rs1.close();
			    pstmt = null;
			    rs1 = null;
			    	if (cnt == 0)
			    	{
			    		errString = itmDBAccessEJB.getErrorString("","REFIDFRINV","","",conn);
			    		return errString;
			    	}
			*/   	
			//}
			
			
			/*if ( refIdTo == null )
			{
				//refIdTo="ZZ";
				//System.out.println("ref id to is Null...");
				//errString = itmDBAccessEJB.getErrorString("","VMRFIDTONU","","",conn);
				//return errString;
			}
			else
			{*/
			/*	
				sql = "select count(1) from inv_hold where ref_id ='" + refIdTo + "'";
			    pstmt = conn.prepareStatement(sql);
			    rs1 = pstmt.executeQuery();
			    if (rs1.next())
			    {
				cnt = rs1.getInt(1);
			    }
			    pstmt.close();
			    rs1.close();
			    pstmt = null;
			    rs1 = null;
			    	if (cnt == 0)
			    	{
			    		errString = itmDBAccessEJB.getErrorString("","REFIDTOINV","","",conn);
			    		return errString;
			    	}
			*/    	
			//}
		/*	
			if ( autoConfirmed == null )
			{
				System.out.println("auto Confirmed is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMAUTCONNU","","",conn);
				return errString;
			}
		*/	
			/*if ( lotNoFrom == null || lotNoFrom.trim().length() == 0 )
			{
				System.out.println("lotNoFrom is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMINVLTFR","","",conn);
				return errString;
			}
			if ( lotNoTo == null || lotNoTo.trim().length() == 0 )
			{
				System.out.println("lotNoTo is Null...");
				errString = itmDBAccessEJB.getErrorString("","VMINVLTTO","","",conn);
				return errString;
			}*/
			
			/*
			 * changes done by mahendra on 29-05-2014 
			 * [to get data by filtering lock_type]
			 * [value of lock_type is 0==warehouse lock,1==quality lock,2==Administrative lock,3==-System managed locks]
			 * [if menu is InvHoldRelGen then data filter with lock type 'warehouse lock'
			 * If menu is InvHoldRelGen For Quality then data filter with lock type 'quality lock'
			 * if menu is InvHoldRelGen For Administrative then data filter with lock type 'Administrative lock'  
			 * */
             
			
			if(windowName.equalsIgnoreCase("w_invholdrelgen_quality"))
			{
				System.out.println("windowName 1"+windowName);
				subSql = "and il.lock_type='1'";//Quality Lock
			}
			if(windowName.equalsIgnoreCase("w_invholdrelgen_adm"))
			{
				System.out.println("windowName 2"+windowName);
				subSql = "and il.lock_type='2'";//Administrative Lock
			}
			
			System.out.println("windowName !!! "+windowName +"lockType !!!!"+lockType);

			
			System.out.println("lockType :"+lockType);
			System.out.println("refIdFrom["+refIdFrom+"]:::::::refIdTo["+refIdTo+"]");
			//ih.lock_code ,ihd.lot_no,ihd.lot_sl,ihd.remarks  ADDED BY RITESH 
			getDataSql= " select ihd.tran_id,ihd.line_no,ihd.line_no_sl," +
						" case when ihd.hold_status is null then 'H' else ihd.hold_status end as hold_status ," +
						" ihd.item_code,ihd.loc_code,ihd.site_code,ihd.status_date,ihd.reas_code,ihd.sch_rel_date," +
						" ih.ref_ser,ih.ref_id,ih.ref_no, ih.confirmed, ih.lock_code ,ihd.lot_no,ihd.lot_sl,ihd.remarks  " +
						" from inv_hold_det ihd,inv_hold ih , inv_lock il where ( ihd.tran_id = ih.tran_id )  and (ih.lock_code=il.lock_code) " +
						" and ( ihd.tran_id >= ? and ihd.tran_id <= ? ) and ( ih.lock_code >= ? and ih.lock_code <= ? )" +
						" and ( ihd.sch_rel_date between ? and ? ) and ihd.hold_status <> 'R' "
					   +" and ( ihd.lot_no >= '"+lotNoFrom+"' and ihd.lot_no <= '"+lotNoTo+"' ) and ih.confirmed='Y' " //Added by Manoj dtd 15/04/2014 to retrieve data based on entered lot No range
						+ " and nvl((select count(1) from inv_hold_rel_det r where r.tran_id__hold = ihd.tran_id and  r.line_no__hold = ihd.line_no ),0) = 0   "+subSql; // 14/04/14 manoharan this condition added
					  
			//and ( ihd.lock_code between ? and ? )
			if(refIdFrom != null )	
			{	
				getDataSql = getDataSql + " and ( ih.ref_id >= ? or ih.ref_id is null ) ";
			}
			
			if(refIdTo != null )	
			{	
				getDataSql = getDataSql + " and ( ih.ref_id <= ?  or ih.ref_id is null ) ";
			}
			
			
			
			
			
			System.out.println("Sql Fired :::::"+getDataSql.trim().length());
			if (getDataSql.trim().length()>0)
			{
				System.out.println("Sql Fired :::::"+getDataSql);					
				pstmt = conn.prepareStatement(getDataSql);
				pstmt.setString(1,tranIdFrom);
				pstmt.setString(2,tranIdTo);
				pstmt.setString(3,lockCodeFrom); 						 //added by Ritesh on 08/05/13
				pstmt.setString(4,lockCodeTo);						    //added by Ritesh on 08/05/13				
				pstmt.setTimestamp(5,schRelDateFrom); 
				pstmt.setTimestamp(6,schRelDateTo);		
				//pstmt.setString(7,lockType);	
				
				if(refIdFrom != null )	
				{
					pstmt.setString(i,refIdFrom);
					i++;
				}
				
				if( refIdTo != null)	
				{
					pstmt.setString(i,refIdTo);
				}
				
				System.out.println("QUERY IS IN PROCESS..........");
				rs1 = pstmt.executeQuery();
				System.out.println("QUERY  PROCESS FINISED.......");
				while(rs1.next()) 
				{
					
					// 09/04/14 manoharan return xml
					retTabSepStrBuff.append("<Detail2>");
					
					System.out.println("ref_id !!!!!"+rs1.getString("ref_id"));
					System.out.println("site_code !!!!!"+rs1.getString("site_code"));
					/*----------------changes done by mahendra dated 01/AUG/2014----------------------*/
				    System.out.println("ref id ::: "+rs1.getString("ref_id"));
				    System.out.println("Tran id ::: "+rs1.getString("ref_ser"));
				    System.out.println("site code ::: "+rs1.getString("site_code"));
				    
				    
					if(rs1.getString("ref_id") != null)
					{
						sql = "select INVOICE_NO from porcp where tran_id=? and 'P-RCP'=?  and site_code=?";
					    pstmt1 = conn.prepareStatement(sql);
					    pstmt1.setString(1,rs1.getString("ref_id"));
					    pstmt1.setString(2,rs1.getString("ref_ser"));
					    pstmt1.setString(3,rs1.getString("site_code"));
					   
					    rs2 = pstmt1.executeQuery();
					    if (rs2.next())
					    {
						invoiceNo = rs2.getString(1);
					    }
					    pstmt1.close();
					    rs2.close();
					    pstmt1 = null;
					    rs2 = null;
						
					}
					else
					{
						invoiceNo="";
					}
					System.out.println("invoiceNo "+invoiceNo);
					System.out.println("item_code "+rs1.getString("item_code"));
					System.out.println("loc_code "+rs1.getString("loc_code"));
					System.out.println("lot_no "+rs1.getString("lot_no"));
					
					
					sql = "select hold_qty from stock where item_code=? and loc_code=? and lot_no=? and lot_sl=? and site_code=? ";
				    pstmt2 = conn.prepareStatement(sql);
				    pstmt2.setString(1,rs1.getString("item_code"));
				    pstmt2.setString(2,rs1.getString("loc_code"));
				    pstmt2.setString(3,rs1.getString("lot_no"));
				    pstmt2.setString(4,rs1.getString("lot_sl"));
				    pstmt2.setString(5,rs1.getString("site_code"));
				   
				    rs3 = pstmt2.executeQuery();
				    if (rs3.next())
				    {
					holdQuantity = rs3.getString(1);
					System.out.println("holdQuantity : "+holdQuantity);
				    }
				    pstmt2.close();
				    rs3.close();
				    pstmt2 = null;
				    rs3 = null;
					
					
					
					/*-------------------------------------------------------------------------------*/
						
					

					//tran_id
					//retTabSepStrBuff.append((rs1.getString(1)==null?" ":rs1.getString(1))).append("\t");
					//retTabSepStrBuff.append("<tran_id>").append("<![CDATA[" + (rs1.getString(1)==null?" ":rs1.getString(1)) +"]]>").append("</tran_id>");
					//line_no
					//retTabSepStrBuff.append((rs1.getString(2)==null?" ":rs1.getString(2))).append("\t");
					//retTabSepStrBuff.append("<line_no>").append("<![CDATA[" + (rs1.getString(2)==null?" ":rs1.getString(2)) +"]]>").append("</line_no>");
					//line_no_sl
					//retTabSepStrBuff.append((rs1.getString(3)==null?" ":rs1.getString(3))).append("\t");
					//retTabSepStrBuff.append("<line_no_sl>").append("<![CDATA[" + (rs1.getString(3)==null?" ":rs1.getString(3)) +"]]>").append("</line_no_sl>");
					//hold_status
					if(rs1.getString(4)!=null)
					{
						//retTabSepStrBuff.append((rs1.getString(4)==null?" ":rs1.getString(4))).append("\t");
						retTabSepStrBuff.append("<hold_status>").append("<![CDATA[" + (rs1.getString(4)==null?" ":rs1.getString(4)) +"]]>").append("</hold_status>");
					}
					else
					{
						//retTabSepStrBuff.append("H").append("\t");
						retTabSepStrBuff.append("<hold_status>").append("<![CDATA[H]]>").append("</hold_status>");
					}
					//item_code
					//retTabSepStrBuff.append((rs1.getString(5)==null?" ":rs1.getString(5))).append("\t");
					retTabSepStrBuff.append("<item_code>").append("<![CDATA[" + (rs1.getString(5)==null?" ":rs1.getString(5)) +"]]>").append("</item_code>");
			       
					//ADDED BY RITESH ON 25/DEC/13 FOR SET ITEM DESCR. AND LOC CODE DESC. START
					sql= " select descr from item where item_code = ?";
					descpstmt = conn.prepareStatement(sql);
					descpstmt.setString(1,rs1.getString(5));
					descrs = descpstmt.executeQuery();
					if(descrs.next())
					{
						//retTabSepStrBuff.append((descrs.getString(1)==null?" ":descrs.getString(1))).append("\t");
						retTabSepStrBuff.append("<item_descr>").append("<![CDATA[" + (descrs.getString(1)==null?" ":descrs.getString(1)) +"]]>").append("</item_descr>");
					}else{
						//retTabSepStrBuff.append("").append("\t");
						retTabSepStrBuff.append("<item_descr>").append("<![CDATA[]]>").append("</item_descr>");

					}
					descpstmt.close();
					descpstmt = null;
					descrs.close();
					descrs = null;
					
					//loc_code
					//retTabSepStrBuff.append((rs1.getString(6)==null?" ":rs1.getString(6))).append("\t");
					retTabSepStrBuff.append("<loc_code>").append("<![CDATA[" + (rs1.getString(6)==null?" ":rs1.getString(6)) +"]]>").append("</loc_code>");
					//lot_no
					//retTabSepStrBuff.append((rs1.getString(16)==null?" ":rs1.getString(16))).append("\t");			//added by ritesh on 25/12/13 
					retTabSepStrBuff.append("<lot_no>").append("<![CDATA[" + (rs1.getString(16)==null?" ":rs1.getString(16)) +"]]>").append("</lot_no>");
					//lot_sl
					//retTabSepStrBuff.append((rs1.getString(17)==null?" ":rs1.getString(17))).append("\t");			//added by ritesh on 25/12/13 
					retTabSepStrBuff.append("<lot_sl>").append("<![CDATA[" + (rs1.getString(17)==null?" ":rs1.getString(17)) +"]]>").append("</lot_sl>");
					//lock code
					//retTabSepStrBuff.append((rs1.getString(15)==null?" ":rs1.getString(15))).append("\t");
					retTabSepStrBuff.append("<lock_code>").append("<![CDATA[" + (rs1.getString(15)==null?" ":rs1.getString(15)) +"]]>").append("</lock_code>");

					sql= " select descr from inv_lock where lock_code = ?";
					descpstmt = conn.prepareStatement(sql);
					descpstmt.setString(1,rs1.getString(15));
					descrs = descpstmt.executeQuery();
					if(descrs.next())
					{
						//retTabSepStrBuff.append((descrs.getString(1)==null?" ":descrs.getString(1))).append("\t");
						retTabSepStrBuff.append("<lock_descr>").append("<![CDATA[" + (descrs.getString(1)==null?" ":descrs.getString(1)) +"]]>").append("</lock_descr>");
					}else{
						//retTabSepStrBuff.append("").append("\t");
						retTabSepStrBuff.append("<lock_descr>").append("<![CDATA[]]>").append("</lock_descr>");

					}
					descpstmt.close();
					descpstmt = null;
					descrs.close();
					descrs = null;
					
					//changed squence order of tran_id,line_no,line_no_sl y mahendra dated 16/APR/14
					retTabSepStrBuff.append("<tran_id>").append("<![CDATA[" + (rs1.getString(1)==null?" ":rs1.getString(1)) +"]]>").append("</tran_id>");
					//line_no
					//retTabSepStrBuff.append((rs1.getString(2)==null?" ":rs1.getString(2))).append("\t");
					retTabSepStrBuff.append("<line_no>").append("<![CDATA[" + (rs1.getString(2)==null?" ":rs1.getString(2)) +"]]>").append("</line_no>");
					//line_no_sl
					//retTabSepStrBuff.append((rs1.getString(3)==null?" ":rs1.getString(3))).append("\t");
					retTabSepStrBuff.append("<line_no_sl>").append("<![CDATA[" + (rs1.getString(3)==null?" ":rs1.getString(3)) +"]]>").append("</line_no_sl>");
					//hold_status
					
					//ADDED BY RITESH ON 25/DEC/13 FOR SET ITEM DESCR. AND LOCK CODE DESC. end	
					
					//site_code
					//retTabSepStrBuff.append((rs1.getString(7)==null?" ":rs1.getString(7))).append("\t");
					retTabSepStrBuff.append("<site_code>").append("<![CDATA[" + (rs1.getString(7)==null?" ":rs1.getString(7)) +"]]>").append("</site_code>");
					//status_date
					if(rs1.getTimestamp(8)!=null)
					{
						//retTabSepStrBuff.append(this.genericUtility.getValidDateString(rs1.getTimestamp(8).toString(),this.genericUtility.getDBDateFormat(),this.genericUtility.getApplDateFormat())).append("\t");
						retTabSepStrBuff.append("<status_date>").append("<![CDATA[" + (this.genericUtility.getValidDateString(rs1.getTimestamp(8).toString(),this.genericUtility.getDBDateFormat(),this.genericUtility.getApplDateFormat())) +"]]>").append("</status_date>");
					}
					else
					{
						//retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append("<status_date>").append("<![CDATA[]]>").append("</status_date>");
					}
					//reas_code
					//retTabSepStrBuff.append((rs1.getString(9)==null?" ":rs1.getString(9))).append("\t");
					retTabSepStrBuff.append("<reas_code>").append("<![CDATA[" + (rs1.getString(9)==null?" ":rs1.getString(9)) +"]]>").append("</reas_code>");
					//sch_rel_date
					if(rs1.getTimestamp(10)!=null)
					{
						//retTabSepStrBuff.append(this.genericUtility.getValidDateString(rs1.getTimestamp(10).toString(),this.genericUtility.getDBDateFormat(),this.genericUtility.getApplDateFormat())).append("\t");
						retTabSepStrBuff.append("<sch_rel_date>").append("<![CDATA[" + (this.genericUtility.getValidDateString(rs1.getTimestamp(10).toString(),this.genericUtility.getDBDateFormat(),this.genericUtility.getApplDateFormat())) +"]]>").append("</sch_rel_date>");
					}
					else
					{
							//retTabSepStrBuff.append(" ").append("\t");
						retTabSepStrBuff.append("<sch_rel_date>").append("<![CDATA[]]>").append("</sch_rel_date>");
					}
					//ref_ser
					//retTabSepStrBuff.append((rs1.getString(11)==null?" ":rs1.getString(11))).append("\t");
					retTabSepStrBuff.append("<ref_ser>").append("<![CDATA[" + (rs1.getString(11)==null?" ":rs1.getString(11)) +"]]>").append("</ref_ser>");
					//ref_id
					//retTabSepStrBuff.append((rs1.getString(12)==null?" ":rs1.getString(12))).append("\t");
					retTabSepStrBuff.append("<ref_id>").append("<![CDATA[" + (rs1.getString(12)==null?" ":rs1.getString(12)) +"]]>").append("</ref_id>");
					//ref_no
					//retTabSepStrBuff.append((rs1.getString(13)==null?" ":rs1.getString(13))).append("\t");			//added by ritesh on 27/05/13 
					retTabSepStrBuff.append("<ref_no>").append("<![CDATA[" + (rs1.getString(13)==null?" ":rs1.getString(13)) +"]]>").append("</ref_no>");
					
					// confirmed
					//retTabSepStrBuff.append((rs1.getString(14)==null?" ":rs1.getString(14))).append("\t");			//added by ritesh on 27/05/13 
					//remove confirmed column .changed by mahendra dated 01-AUG-2014
					//retTabSepStrBuff.append("<confirmed>").append("<![CDATA[" + (rs1.getString(14)==null?" ":rs1.getString(14)) +"]]>").append("</confirmed>");
					//remarks
					//retTabSepStrBuff.append((rs1.getString(18)==null?" ":rs1.getString(18))).append("\t");			//added by ritesh on 27/05/13 
					retTabSepStrBuff.append("<remarks>").append("<![CDATA[" + (rs1.getString(18)==null?" ":rs1.getString(18)) +"]]>").append("</remarks>");
					retTabSepStrBuff.append("<invoice_no>").append("<![CDATA[" + (invoiceNo==null?" ":invoiceNo) +"]]>").append("</invoice_no>");
					retTabSepStrBuff.append("<hold_quantity>").append("<![CDATA[" + (holdQuantity==null?" ":holdQuantity) +"]]>").append("</hold_quantity>");
					//retTabSepStrBuff.append("\n");				
					retTabSepStrBuff.append("</Detail2>");

					count++;
					System.out.println("#####Counter:["+count+"]");
				}//END WHILE
				rs1.close();
				pstmt.close();
				rs1=null;
				pstmt=null;
				retTabSepStrBuff.append("</Header0>");
				retTabSepStrBuff.append("</group0>");
				retTabSepStrBuff.append("</DocumentRoot>");

				if (count == 0 )
				{
					errString = itmDBAccessEJB.getErrorString("","VTNOREC1","","",conn);
				}
			}
			else
			{
				System.out.println("Sql:::::is Null ::::::::::::::");
				errString = itmDBAccessEJB.getErrorString("","VTNOREC1","","",conn);
			}
			
			System.out.println("retTabSepStrBuff:::["+retTabSepStrBuff.toString()+"]");
			
	
		
		}	//end of try	
		catch (SQLException e)
		{ 
			e.printStackTrace();
			System.out.println("SQLException :InvHoldRelGenEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :InvHoldRelGenEJB :getData(Document headerDom, Document detailDom, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		finally
		{		
			try
			{		
				conn.close();
				conn = null;
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		if (getDataSql.trim().length()>0 && count > 0)
		{
			return retTabSepStrBuff.toString();	
		}
		else 
		{
			errString = itmDBAccessEJB.getErrorString("","VTNOREC1","","",conn);
			return errString;
		}
	}	
	
	
	@Override
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		Connection conn=null;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			//Connection conn= null;
				if(conn==null)
				{
					conn = connDriver.getConnectDB("DriverITM");
					 
					conn.setAutoCommit(false);	
				}
				//DatabaseMetaData dbmd = conn.getMetaData();
			
				System.out.println("xmlString[process]::::::::::;;;"+xmlString);
				System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
				System.out.println("windowName[process]::::::::::;;;"+windowName);
				System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
				
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
		}
	
		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(xmlString, xmlString2, windowName, xtraParams,conn);
		    
		}
		catch (Exception e)
		{
			
			System.out.println("Exception : InvHoldRelGenEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			retStr = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
}//END OF PROCESS (1)
	
public String process(String xmlString, String xmlString2, String windowName, String xtraParams,Connection conn) throws RemoteException,ITMException
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	String retStr = "",tranId="",sql="",errString="",lockCode="",holdStatus="";//xmlString="",
	ResultSet rs = null;
	PreparedStatement pstmt = null;
	ConnDriver connDriver = new ConnDriver();
	//Connection conn= null;
	int cnt=0,updCnt=0,ctr=0,count=0;
	String invHoldRelSql = "",invHoldRelDetSql;
	PreparedStatement pRelHdr = null;
	PreparedStatement pRelDet = null;
	java.sql.Timestamp chgDate = null,currDate = null;
	Date date = null;
	String chgTerm = "",autoConfirmed="";
	String userId = "",siteCode="",confirmed="",empCode="",tranIdGenerate="",lineNo="",remark="",tranIdRel="",lineNoRel="",remarks="";
	Document dom = null,headerDom=null,detailDom=null;
	ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();

	int parentNodeListLength = 0;
	int childNodeListLength = 0;
	
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	Node childNode = null;
	String childNodeName = "";
	
	boolean isConn= false;   // added by cpatil on 29/05/13
	String lotNo="";//Added by manoj dtd 15/04/2014 to update lotNo in inv_hold_rel table
	
	try
	{	
		System.out.println("method : process(xmlString, xmlString2, windowName, xtraParams,conn) called");
		if(xmlString != null && xmlString.trim().length()!=0)
		{
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			headerDom = genericUtility.parseString(xmlString); 
			detailDom= genericUtility.parseString(xmlString2);
			//System.out.println("::::::headerDom :::::[" + headerDom+"]");
			
			tranId = genericUtility.getColumnValue("tran_id",detailDom);
			autoConfirmed = genericUtility.getColumnValue("auto_confirmed",headerDom);
			remarks = genericUtility.getColumnValue("remarks",headerDom);
			System.out.println("remarks  :::"+remarks);
			if(autoConfirmed == null || autoConfirmed.trim().length() == 0 )
			{
				autoConfirmed = "Y";
			}
			System.out.println("tranId:["+tranId+"]:::::autoConfirmed:["+autoConfirmed+"]");
			//System.out.println("INV_HOLD_REL sql ");
			invHoldRelSql = "INSERT INTO INV_HOLD_REL(TRAN_ID, TRAN_DATE, SITE_CODE, REMARKS, CONFIRMED, EMP_CODE__APRV,CHG_USER ,CHG_DATE, CHG_TERM) VALUES(?,?,?,?,?,?,?,?,?)";
			pRelHdr = conn.prepareStatement(invHoldRelSql);
			
			//System.out.println("INV_HOLD_REL_DET sql ");
			invHoldRelDetSql = "INSERT INTO INV_HOLD_REL_DET(TRAN_ID, LINE_NO, TRAN_ID__HOLD, LINE_NO__HOLD, REMARKS) VALUES(?,?,?,?,?)";
			pRelDet = conn.prepareStatement(invHoldRelDetSql);

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );
			siteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "site_code" );
			
			confirmed = "N";
			empCode = "";

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String currDateStr = sdfAppl.format(currDate);

			tranIdGenerate = generateTranId( "w_inv_hold_rel", siteCode, currDateStr, conn );

			pRelHdr.setString( 1, tranIdGenerate );
			pRelHdr.setTimestamp( 2, currDate );
			pRelHdr.setString( 3, siteCode );
			pRelHdr.setString( 4, remarks);
			pRelHdr.setString( 5, confirmed );
			pRelHdr.setString( 6, empCode );
			pRelHdr.setString( 7, userId );
			pRelHdr.setTimestamp( 8, currDate );
			pRelHdr.setString( 9, chgTerm );

			updCnt = pRelHdr.executeUpdate();
			pRelHdr.clearParameters();
			pRelHdr.close();
			pRelHdr=null;
			if( updCnt > 0 )
			{					
				System.out.println( updCnt + "INV_HOLD_REL rows inserted successfully" );
			}
			// 09/09/12 manoharan commented and added new logical code
			/*lineNo = genericUtility.getColumnValue("line_no",detailDom);
			sql = "select count(*) from inv_hold_det where tran_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;					

			if(count != 0)
			{
				ctr++;	
				sql = "select tran_id, line_no from inv_hold_det where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					tranIdRel = rs.getString("tran_id");
					lineNoRel = rs.getString("line_no");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;					

				pRelDet.setString( 1, tranIdGenerate );
				pRelDet.setString( 2,lineNo);
				pRelDet.setString( 3, tranIdRel );
				pRelDet.setString( 4, lineNoRel );
				pRelDet.setString( 5, remark );
				pRelDet.addBatch();
				//updCnt = pRelDet.executeUpdate();
				pRelDet.clearParameters();
				if( updCnt > 0 )
				{					
					System.out.println( updCnt + " INV_HOLD_REL_DET rows updated successfully" );
				}
			}
			*/
			// 09/09/12 manoharan commented upto here and added the following code
			/////////////////////////////////////////////////////////////////////////
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			//System.out.println("parentNodeListLength------------------->"+parentNodeListLength);
			HashSet<String> lotSet=new HashSet<String>();
			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("tran_id"))
					{
						tranId = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("line_no"))
					{
						lineNo = childNode.getFirstChild().getNodeValue();
					}
					if (childNodeName.equals("lot_no"))
					{
						lotNo = childNode.getFirstChild().getNodeValue();
						lotSet.add(lotNo);
					}
				}
				ctr++;
				pRelDet.setString( 1, tranIdGenerate );
				pRelDet.setInt( 2,ctr);
				pRelDet.setString( 3, tranId );
				pRelDet.setString( 4, lineNo );
				pRelDet.setString( 5, remark );
				pRelDet.addBatch();
				//updCnt = pRelDet.executeUpdate();
				pRelDet.clearParameters();
			}
			/////////////////////////////////////////////////////////////////////////
			pRelDet.executeBatch();
			pRelDet.clearBatch();
			Iterator<String> itr=lotSet.iterator();
			String strLotNo="";
			while(itr.hasNext())
			{
				strLotNo+=itr.next()+",";
			}
			if(strLotNo.trim().length()>0)
			{
				strLotNo=strLotNo.substring(0, strLotNo.length()-1);	
			}
			lotSet.clear();
			pRelHdr=conn.prepareStatement("UPDATE INV_HOLD_REL SET LOT_INFO='"+strLotNo+"' WHERE TRAN_ID=? ");
			pRelHdr.setString(1, tranIdGenerate);
			pRelHdr.executeUpdate();
			
			
			if("Y".equalsIgnoreCase(autoConfirmed))
			{
				InvHoldRelConf invHoldRelConf =  new InvHoldRelConf();
				
				errString = invHoldRelConf.confirm(tranIdGenerate,xtraParams, "N" , conn,isConn);   // modify by cpatil on 29/05/13
				invHoldRelConf = null;
				if (errString.indexOf("VTCNFSUCC") > -1)
				{
					errString = "";
				}
				//sql = " update inv_hold set confirmed = 'Y', conf_date = ? where tran_id = ?";
				/*sql = "update inv_hold_det set  hold_status = 'R', status_date = ? where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1,currDate);
				pstmt.setString(2,tranId);
				int rowcnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
				*/

			}
			if (errString == null || errString.trim().length() == 0)
			{
				conn.commit();
				System.out.println("inv_hold_det transaction hold_status release succesfully........");
			//	errString = itmDBAccessLocal.getErrorString("","VTMCONF2","");
				errString = itmDBAccessLocal.getErrorString("","VTCOMPL","");//Error message changed by Manoj dtd 02/09/2014
			}
			else
			{
				conn.rollback();
				System.out.println("Process failed........");
				//errString = itmDBAccessLocal.getErrorString("","VTDESNCONF","");
				errString = itmDBAccessLocal.getErrorString("","VTPRCERR","");//Error message changed by Manoj dtd 02/09/2014
			}
		}
		
	}
	catch (Exception e)
	{
		errString = e.getMessage();
		e.printStackTrace();
		try
		{
			conn.rollback();
		}
		catch(Exception e1)
		{
			e1.printStackTrace();
		}
		throw new ITMException(e);
	}
	finally
	{

		try
		{
			if(pRelHdr != null)
			{
				pRelHdr.close();
				pRelHdr = null;	
			}
			if(pRelDet != null)
			{
				pRelDet.close();
				pRelDet = null;
			}
			if(conn != null)
			{
				conn.close();
				conn = null;
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	return errString;
}

private String generateTranId( String windowName, String siteCode, String tranDateStr, Connection conn )throws ITMException
{
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String selSql = "";
	String tranId = "";
	String tranSer = "";
	String keyString = "";
	String keyCol = "";
	String xmlValues = "";
	String paySiteCode = "";
	String effectiveDate = "";
	java.sql.Date effDate = null;

	try
	{
		System.out.println("generateTranId() called");
		
		selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
		//System.out.println("selSql :"+selSql);
		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;
		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>" + tranDateStr + "</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)
		{
			//errString = e.getMessage();
			e.printStackTrace();
			throw new ITMException(e);
		}
	}
	return tranId;
 }



}
