/**
 * PURPOSE : Validation and Itemchange for Check sheet verification
 * AUTHOR : Kunal Mandhre
 * DATE : 11/06/2012
 */

package ibase.webitm.ejb.wms;

import java.awt.print.PrinterJob;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import javax.print.PrintService;

import org.w3c.dom.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

@javax.ejb.Stateless
public class CheckSheetIC extends ValidatorEJB implements CheckSheetICLocal,CheckSheetICRemote 
{
	/**
	 * The method is defined without any parameter and returns blank string
	 */
	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(DOM)
	 * The dom is then given as argument to the overloaded function wfValData to perform validation
	 * Returns validation string if exists else returns null in XML format
	 * @param xmlString contains the current form data in XML format
	 * @param xmlString1 contains all the header information in the XML format
	 * @param xmlString2 contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */	
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext,String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currFormDataDom = null;
		Document hdrDataDom = null;
		Document allFormDataDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();    

		try
		{
			System.out.println( "xmlString ::" + xmlString);
			System.out.println( "xmlString1 ::" + xmlString1);
			System.out.println( "xmlString2 ::" + xmlString2);
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				currFormDataDom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				hdrDataDom = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				allFormDataDom = genericUtility.parseString(xmlString2); 
			}
			errString = wfValData( currFormDataDom, hdrDataDom, allFormDataDom, objContext, xtraParams);
			System.out.println( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return (errString); 
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of required fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currFormDataDom contains the current form data as a document object model
	 * @param hdrDataDom contains all the header information
	 * @param allFormDataDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	public String wfValData(Document currFormDataDom, Document hdrDataDom, Document allFormDataDom, String objContext, String xtraParams) throws RemoteException, ITMException
	{   				
		//String locCode = "";
		String shipmentId = "";
		String cartonNo = "";
		String replOrder = "";
		String columnValue = "";			
		String errString = "";
		String sql = "";
		//Changed by sumit on 04/10/12 created variable confirmed
		String confirmed = "";
		int count = 0;
		int noOfChilds = 0;
		int noOfParent = 0;
		int cnt = 0;
		//Changed by sumit on 16/11/12 created variable printer
		String printer = "";
		//changed by sumit on 07/03/13 
		String palletNo = "";

		GenericUtility genericUtility = GenericUtility.getInstance();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();


		try
		{	
			int currentFormNo = 0;				
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverValidator" );

			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );

			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			NodeList parentList = currFormDataDom.getElementsByTagName( "Detail" + currentFormNo );
			NodeList childList = null;	
			noOfParent = parentList.getLength();						

			switch ( currentFormNo )  // Switch Case for form number
			{
			case 1:
			{	
				childList = parentList.item(0).getChildNodes();
				noOfChilds = childList.getLength();

				for ( int ctr = 0; ctr < noOfChilds; ctr++ ) //Loop for each node of current detail
				{	
					Node childNode = childList.item( ctr );
					if( childNode.getNodeType() != Node.ELEMENT_NODE )
					{
						continue;
					}
					String childNodeName = childNode.getNodeName();
					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					if ( "shipment_id".equalsIgnoreCase( childNodeName ) )
					{
						shipmentId = genericUtility.getColumnValue( "shipment_id", currFormDataDom );
						if( shipmentId == null || shipmentId.trim().length() == 0 )
						{
							errList.add( "NULLSHIPID" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							//Changed by sumit on 04/10/12 changing sql for getting confirmed value
							//sql = "SELECT COUNT(*) AS COUNT FROM SHIPMENT  WHERE SHIPMENT_ID = ?";
							sql = "SELECT CONFIRMED FROM SHIPMENT S, SHIP_DOCS SD  WHERE S.SHIPMENT_ID = ? " +
									" AND S.SHIPMENT_ID = SD.SHIPMENT_ID "+
									" AND SD.REF_SER = ?";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, shipmentId );
							pstmt.setString( 2, "S-DSP" );
							rs = pstmt.executeQuery();

							if( rs.next() )
							{
								//changed by sumit on 04/10/12 for validation of confirmed shipment id start.
								//count = rs.getInt("COUNT");
								confirmed = rs.getString("CONFIRMED");
								if( "Y".contentEquals(confirmed))
								{
									errList.add( "VMSHIPACON" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							else
							{
								errList.add( "INVSHIPID" );
								errFields.add( childNodeName.toLowerCase() );
							}
							//changed by sumit on 04/10/12 for validation of confirmed shipment id end.
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							//Changed by sumit on 04/10/12 written this code above start.
							/*if( count <= 0 )
							{
								errList.add( "INVSHIPID" );
								errFields.add( childNodeName.toLowerCase() );
							}*/
							//Changed by sumit on 04/10/12 written this code above end.
							//Changed by sumit on 04/06/13 adding validation in case of label not reprinted. start.
							String printStatus = "";
							sql = "SELECT WSO.PRINT_STATUS FROM SHIP_DOCS SD, WAVE_STATUS_ORG WSO WHERE SD.SHIPMENT_ID = ? AND SD.PTCN = WSO.PTCN";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, shipmentId );							
							rs = pstmt.executeQuery();
							while( rs.next() )
							{
								printStatus = rs.getString("PRINT_STATUS");
								if("N".equalsIgnoreCase(printStatus))
								{
									errList.add( "INVPRINTST" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							rs.close();	rs = null;
							pstmt.close(); pstmt = null;
							//Changed by sumit on 04/06/13 adding validation in case of label not reprinted. end.
						}
					}
					//Changed by sumit on 16/11/12 validating printer service name with existing printer in system start.
					else if ( "printer".equalsIgnoreCase( childNodeName ) )
					{
						printer = checkNull(genericUtility.getColumnValue( "printer_name", currFormDataDom ));
						System.out.println(" printer from input parameter ["+printer+"]");
						PrintService[] printServices = PrinterJob.lookupPrintServices();
						ArrayList<String> arry = new ArrayList<String>();
						if(printer.trim().length() > 0)
						{							
							for (PrintService printService : printServices) 
							{
					            String name = printService.getName();
					            System.out.println("printer service name --> "+name);
					            arry.add(name);					            
					        }
							if( !arry.contains(printer.trim()))
				            {
				            	errFields.add( childNodeName.toLowerCase() );
								errList.add( "INVPRINTER" );
				            }							
							arry.clear();				           
						}
					}
					//Changed by sumit on 16/11/12 validating printer service name with existing printer in system end.
				}
			} //End of case 1 Validation
			break;
			case 2:
			{
				childList = parentList.item(0).getChildNodes();
				noOfChilds = childList.getLength();
				for ( int ctr = 0; ctr < noOfChilds; ctr++ )  //Loop for each node of current detail
				{	
					Node childNode = childList.item( ctr );
					if( childNode.getNodeType() != Node.ELEMENT_NODE )
					{
						continue;
					}
					String childNodeName = childNode.getNodeName();
					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" -columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					
					//Changed by sumit on 07/03/13 validation for pallet_no start.
					if ( "pallet_no".equalsIgnoreCase( childNodeName ) )
					{
						palletNo = genericUtility.getColumnValue( "pallet_no", currFormDataDom );
						if( palletNo == null || palletNo.trim().length() == 0 )
						{
							errList.add( "NULLPALLET" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							sql = " SELECT PALLET_NO FROM PALLET_NO WHERE PALLET_NO = ? AND STATUS = 'U'";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, palletNo);
							rs = pstmt.executeQuery();
							if( !rs.next())
							{
								errList.add( "INVPALLET" );
								errFields.add( childNodeName.toLowerCase() );
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
						}
					}
					//Changed by sumit on 07/03/13 validation for pallet_no end.

				}
			} //End of Case 2 Validation		
			}//End of switch statement
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = errList.get(cnt);
					errFldName = errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					//String errMsg = hashMap.get(errCode)!=null ? hashMap.get(errCode).toString():"";
					//System.out.println("errMsg .........."+errMsg);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn , errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString;//+"<trace>"+errMsg+"</trace>";
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;

				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}
			errString = errStringXml.toString();
		}
		catch ( Exception e )
		{
			System.out.println ( "Exception: CheckSheetIC: wfValData( Document currFormDataDom ): " + e.getMessage() + ":" );
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs!=null)
				{
					rs.close();
					rs = null;
				}				
				if (pstmt!=null)
				{
					pstmt.close();
					pstmt = null;
				}
				if( conn != null  )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception : CheckSheetIC:wfValData : " + e.getMessage() );
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";

			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, errorCode);			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}

	/**
	 * The public method is defined without any parameters and returns blank string
	 */
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}

	/**
	 * The public method is used for converting the current form data into a document(dom)
	 * The dom is then given as argument to the overloaded function wfValData to perform validation
	 * Returns validation string if exists else returns null in XML format
	 * @param xmlString contains the current form data in XML format
	 * @param xmlString1 contains all the header information in the XML format
	 * @param xmlString2 contains the data of all the forms in XML format
	 * @param objContext represents the form number
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
	 */
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currFormDataDom = null;
		Document hdrDataDom = null;
		Document allFormDataDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("xmlString ["+xmlString+"]");
		System.out.println("xmlString1 ["+xmlString1+"]");
		System.out.println("xmlString2 ["+xmlString2+"]");
		try
		{
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				currFormDataDom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				hdrDataDom = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				allFormDataDom = genericUtility.parseString(xmlString2); 
			}
			System.out.println ( "Calling  itemChanged( currFormDataDom, hdrDataDom, allFormDataDom, objContext, currentColumn, editFlag, xtraParams )");
			errString = itemChanged( currFormDataDom, hdrDataDom, allFormDataDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :CheckSheetIC :itemChanged(String,String):" + e.getMessage() + ":" );
			throw new ITMException(e);
		}
		System.out.println ( "returning from CheckSheetIC itemChanged \n[" + errString + "]" );

		return errString;
	}

	/**
	 * The public overloaded method takes a document as input and is used for the validation of required fields 
	 * Returns validation string if exist otherwise returns null in XML format
	 * @param currFormDataDom contains the current form data as a document object model
	 * @param hdrDataDom contains all the header information
	 * @param allFormDataDom contains the field data of all the forms 
	 * @param objContext represents form number
	 * @param currentColumn represents the current field 
	 * @param editFlag represents the mode of transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information
	 */
	public String itemChanged( Document currFormDataDom, Document hdrDataDom, Document allFormDataDom, String objContext, String currentColumn,String editFlag, String xtraParams ) throws RemoteException,ITMException
	{	
		String shipmentId = "";
		String cartonNo = "";
		String pickOrder = "";
		String retString = "";		
		String columnValue = "";
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sqlRes = "";
		String waveId = "";
		String palletNo = "";
		String refId = "";
		String pickType = "";
		String orderType = "";
		String replOrder = "";
		
		String ptcn = "";

		int currentFormNo = 0;
		int domID = 0;
		

		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmtRes = null;
		ResultSet rs = null ;
		ResultSet rs1 = null ;
		ResultSet rs2 = null ;
		ResultSet rsRes = null ;
		StringBuffer valueXmlString;
		System.out.println("xtraParams=["+xtraParams+"]");
		String objName = checkNullAndTrim(getObjNameFromDom( currFormDataDom, "objName", objContext ));
		//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.start
		/*//Changed by sumit sarkar on 27/09/12 start
		StringBuffer strBuffer1 = null;
		StringBuffer strBuffer2 = null;
		PreparedStatement pstmt3 = null;
		ResultSet rs3 = null;
		String sql3 = "";
		String string = "";*/
		//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.end
		
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );			
			columnValue = genericUtility.getColumnValue( currentColumn, currFormDataDom );
			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			System.out.println("current form no["+currentFormNo+"]");
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>").append( editFlag );
			valueXmlString.append("</editFlag>\r\n</Header>\r\n");
			switch ( currentFormNo )  // Switch statement for performing item change on detail forms
			{		
			

			case 2:  //Case 2. Start
			{
				System.out.println("Inside case 2 current form no is ="+currentFormNo);
				System.out.println("current column = "+currentColumn);
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("Inside if........");
					shipmentId = checkNull(genericUtility.getColumnValue( "shipment_id", hdrDataDom ));
					System.out.println("shipment id = "+shipmentId);
					if(shipmentId != null && shipmentId.trim().length() > 0)
					{
						
						//Changed By Pragyan 17-Sep-12 as Carton no taken from despatch details.start
						//sql = "select ref_id from ship_docs where  shipment_id = ? ";
						sql = "SELECT DISTINCT S.PALLET_NO AS PLTNO FROM DESPATCHDET S WHERE "
							+" S.DESP_ID IN (SELECT REF_ID FROM SHIP_DOCS WHERE  SHIPMENT_ID = ? AND DESP_CONFIRMED = ? AND REF_SER = ? )"							
							+" ORDER BY S.PALLET_NO ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, shipmentId);
						pstmt.setString(2, "N");
						pstmt.setString(3, "S-DSP");						
						rs = pstmt.executeQuery();
						
						while ( rs.next() )
						{
							cartonNo = rs.getString("PLTNO");
							domID++;
							valueXmlString.append( "<Detail2 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='"+objName+"' selected=\"N\">\r\n" );
							valueXmlString.append( "<pallet_no><![CDATA[" ).append(cartonNo).append( "]]></pallet_no>\r\n" );
							valueXmlString.append("</Detail2>\r\n");
							
							/*refId = rs.getString("ref_id");
							System.out.println("REF ID = "+refId);
							sqlRes = "select wave_id ,ptcn from wave_task_det where  ref_id = ? and ref_ser = 'S-DSP'";
							pstmtRes = conn.prepareStatement( sqlRes );
							pstmtRes.setString( 1, refId);
							rsRes = pstmtRes.executeQuery();
							while ( rsRes.next() )
							{
								
								waveId = rsRes.getString("wave_id");
								ptcn = rsRes.getString("ptcn");
								System.out.println("wave id = "+waveId);								
								//for pick iss
								sql1 = "select pick_order,pick_type from pick_ord_hdr p,wave_task_det w where p.wave_id = w.wave_id and w.ptcn = ? and w.ref_id = p.pick_order and p.wave_id = ?";
								pstmt1 = conn.prepareStatement( sql1 );
								pstmt1.setString( 1, ptcn);
								pstmt1.setString( 2, waveId);
								rs1 = pstmt1.executeQuery();
								while ( rs1.next() )
								{
									pickOrder = rs1.getString("pick_order");
									pickType = rs1.getString("pick_type") ;							
									
									sql2 = "select pallet_no from pick_iss_hdr where pick_order = ?";
									pstmt2 = conn.prepareStatement( sql2 );
									pstmt2.setString( 1, pickOrder);
									rs2 = pstmt2.executeQuery();
									while ( rs2.next() )
									{
										palletNo = rs2.getString("pallet_no");
										//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.start
										strBuffer1 = new StringBuffer();
										sql3 =  " SELECT DISTINCT I.DESCR, PD.ITEM_CODE, PD.LOT_NO, PD.LOT_SL, PD.QUANTITY " +
												" FROM PICK_ISS_DET PD, PICK_ISS_HDR PH, ITEM I "+
												" WHERE PD.TRAN_ID = PH.TRAN_ID "+
												" AND PD.ITEM_CODE = I.ITEM_CODE "+
												" AND PH.PALLET_NO = ? ";
										pstmt3 = conn.prepareStatement( sql3 );
										pstmt3.setString( 1, palletNo);
										rs3 = pstmt3.executeQuery();
										while(rs3.next())
										{
											//System.out.println("&&&&& ->["+palletNo+"]");
											strBuffer1.append("(");
											strBuffer1.append(rs3.getString("DESCR")).append("(").append(rs3.getString("ITEM_CODE")).append("),");
											strBuffer1.append(rs3.getString("LOT_NO")).append(",");
											strBuffer1.append(rs3.getString("LOT_SL")).append(",");
											strBuffer1.append(rs3.getString("QUANTITY")).append("");
											strBuffer1.append(")");
											strBuffer1.append(",");										
										}
										rs3.close(); rs3 = null;
										pstmt3.close(); pstmt3 = null;
										if( strBuffer1.toString().length() > 0)
										{
											string = strBuffer1.toString().substring(0, strBuffer1.toString().length() -1);	
										}									
										//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.end
										 	
										domID++;
										valueXmlString.append( "<Detail2 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='"+objName+"' selected=\"N\">\r\n" );
										valueXmlString.append( "<pallet_no><![CDATA[" ).append(palletNo).append( "]]></pallet_no>\r\n" );
										//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta
										//Changed by sumit on 27/07/12 displaying above item_code, lot_no, lot_sl, quantity 
										//valueXmlString.append( "<item_list><![CDATA[" ).append(string).append( "]]></item_list>\r\n" );
										valueXmlString.append("</Detail2>\r\n");
									}
									rs2.close(); rs2 = null;
									pstmt2.close(); pstmt2 = null;

								}
								rs1.close(); rs1 = null;
								pstmt1.close(); pstmt1 = null;


								//for repl iss
								sql1 = "select repl_order,order_type from repl_ord_hdr r,wave_task_det w where r.wave_id = w.wave_id and w.ptcn = ? and w.ref_id = r.repl_order and r.wave_id = ? ";
								pstmt1 = conn.prepareStatement( sql1 );
								pstmt1.setString( 1, ptcn);
								pstmt1.setString( 2, waveId);
								rs1 = pstmt1.executeQuery();
								while ( rs1.next() )
								{
									replOrder = rs1.getString("repl_order");
									orderType = rs1.getString("order_type") ;									
															
									sql2 = "select pallet_no from pick_iss_hdr where pick_order = ?";
									sql2 = "select carton_no from repl_iss_hdr where repl_order = ?";
									pstmt2 = conn.prepareStatement( sql2 );
									pstmt2.setString( 1, replOrder);
									rs2 = pstmt2.executeQuery();
									while ( rs2.next() )
									{
										
										if("D".equalsIgnoreCase(orderType) || "L".equalsIgnoreCase(orderType))
										{
											cartonNo = rs2.getString("carton_no");
											
											
											//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.start
											//Changed by sumit sarkar on 27/09/12 getting detail of coming pick order to display start. 
											strBuffer2 = new StringBuffer();
											sql3 = 	" SELECT DISTINCT I.DESCR, PID.ITEM_CODE, PID.LOT_NO, PID.LOT_SL, PID.QUANTITY " +
													" FROM PICK_ISS_DET PID, REPL_ORD_DET ROD , ITEM I " +
													" WHERE PID.PICK_ORDER = ROD.PICK_ORDER " +
													" AND PID.ITEM_CODE = I.ITEM_CODE "+
													" AND ROD.CARTON_NO = ? "; 
											
											pstmt3 = conn.prepareStatement( sql3 );
											pstmt3.setString( 1, cartonNo);
											rs3 = pstmt3.executeQuery();
											while(rs3.next())
											{
												strBuffer2.append("(");
												strBuffer2.append(rs3.getString("DESCR")).append("(").append(rs3.getString("ITEM_CODE")).append("),");
												strBuffer2.append(rs3.getString("LOT_NO")).append(",");
												strBuffer2.append(rs3.getString("LOT_SL")).append(",");
												strBuffer2.append(rs3.getString("QUANTITY")).append("");
												strBuffer2.append(")");
												strBuffer2.append(",");												
											}
											rs3.close(); rs3 = null;
											pstmt3.close(); pstmt3 = null;
											string = "";
											if( strBuffer2.toString().length() > 0)
											{
												string = strBuffer2.toString().substring(0, strBuffer2.toString().length() -1);
											}											
											//Changed by sumit sarkar on 27/09/12 getting detail of coming pick order to display end. 
											 											
											//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta.end
											domID++;
											valueXmlString.append( "<Detail2 domID='"+ domID +"' objContext = '"+currentFormNo+"' objName='"+objName+"' selected=\"N\">\r\n" );
											valueXmlString.append( "<pallet_no><![CDATA[" ).append(cartonNo).append( "]]></pallet_no>\r\n" );
											//Changed By Pragyan 14-SEP-12 Not Required this item change as discussed with KB & Ashish Mehta
											//Changed by sumit on 27/07/12 displaying above item_code, lot_no, lot_sl, quantity 
											//valueXmlString.append( "<item_list><![CDATA[" ).append(string).append( "]]></item_list>\r\n" );
											valueXmlString.append("</Detail2>\r\n");
										}
									}
									rs2.close(); rs2 = null;
									pstmt2.close(); pstmt2 = null;
								}
								rs1.close(); rs1 = null;
								pstmt1.close(); pstmt1 = null;
								
							}
							rsRes.close(); rsRes = null;
							pstmtRes.close(); pstmtRes = null;*/
							//Changed By Pragyan 17-Sep-12 as Carton no taken from despatch details.end
						}
						if(rs != null)
						{
							rs.close(); 
							rs = null;
						}
						if(pstmt != null)
						{
							pstmt.close();
							pstmt = null;
						}
					}
				}//itm_detault

			}  //Case 2. End
			}
		}
		catch (Exception e)
		{				
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{	
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if ( conn != null )
				{
					conn.close();
					conn = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		valueXmlString.append( "</Root>\r\n" );	
		return valueXmlString.toString();
	}
	private String getObjNameFromDom( Document dom, String attribute, String objContext ) throws RemoteException,ITMException
	{
		NodeList detailList = null;
		Node currDetail = null,reqDetail = null;
		String objName = "";
		int	detailListLength = 0;

		try
		{
			detailList = dom.getElementsByTagName("Detail"+objContext);
			detailListLength = detailList.getLength();
			for (int ctr = 0; ctr < detailListLength; ctr++)
			{
				currDetail = detailList.item(ctr);
				objName = currDetail.getAttributes().getNamedItem(attribute).getNodeValue();
			}
		}
		catch ( Exception e )
		{
			throw new ITMException(e);
		}
		return objName;
	}
	private String checkNullAndTrim( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}

	/**
	 * The function checks whether the input string is null. 
	 * If null, the function returns blank string.  
	 * @param input
	 */
	private String checkNull( String input )	
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}		
	/**
	 * The function checks whether the input string is null. 
	 * If null, the function returns blank string otherwise trim the input string and return.  
	 * @param input
	 */


}
