/**
 *  PURPOSE  : DefaultItemChanged for wave generation process
 *  AUTHOR   : Changed By Sumit On 28/11/2012 WM1ESUN006 
 *  Obj_name : w_wavegen
 */


package ibase.webitm.ejb.wms;

//changed by sumit on 03/10/12 imported class to find printer in system. start.
import java.awt.print.PrinterJob;
import java.io.File;

import javax.print.PrintService;
//changed by sumit on 03/10/12 imported class to find printer in system. end.
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;



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

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.SaleOrdItmGen;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

@javax.ejb.Stateless
public class GenerateLabelsIC extends ValidatorEJB implements GenerateLabelsICRemote, GenerateLabelsICLocal{
	
	/**
	* The method defined with no parameter and returns nothing
	*/
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	
	
	/**
	 * The public method is used for itemchange of required fields which return called overloaded method
	 * Returns itemchange string in XML format
	 * @param currXmlDataStr contains the current form data in XML format
	 * @param hdrXmlDataStr containspoic_wavegen_default_data always header form data in XML format
	 * @param allXmlDataStr contains all forms data in XML format 
	 * @param objContext represents form no
	 * @param currentColumn represent Current COlumn
	 * @param editFlag the mode of the transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm 
etc
	*/
	public String itemChanged(String currXmlDataStr, String hdrXmlDataStr, String allXmlDataStr, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document currDom = null;
		Document hdrDom = null;
		Document allDom = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if (currXmlDataStr != null && currXmlDataStr.trim().length()!=0)
			{
				currDom = genericUtility.parseString(currXmlDataStr); 
			}
			if (hdrXmlDataStr != null && hdrXmlDataStr.trim().length()!=0)
			{
				hdrDom = genericUtility.parseString(hdrXmlDataStr); 
			}
			if (allXmlDataStr != null && allXmlDataStr.trim().length()!=0)
			{
				allDom = genericUtility.parseString(allXmlDataStr); 
			}
			errString = defaultItemChanged( currDom, hdrDom, allDom, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :WaveGenerationIC :itemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
		}
		System.out.println ( "returning from WaveGenerationIC itemChanged" );
		return errString;
	}// end ItemChanged Method
	
	/**
	 * The public overloaded method is used for itemchange of required fields 
	 * Returns itemchange string in XML format
	 * @param currDom contains the current form data 
	 * @param hdrDom contains always header form data
	 * @param allDom contains all forms data 
	 * @param objContext represents form no
	 * @param editFlag the mode of the transaction(A-Add or E-Edit)
	 * @param xtraParams contains additional information such as loginEmpCode,loginCode,chgTerm etc
    */
	public String defaultItemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		
		String siteCode = "";
		String errString = "";
		int currentFormNo = 0;
		String columnValue = "";
		String sql = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n" );
		GenericUtility genericUtility = GenericUtility.getInstance();
		java.sql.Date dateFrom = null;
		String waveId ="";		
		try
		{
			String applDateFmt = genericUtility.getApplDateFormat();
			String dbDateFmt = genericUtility.getDBDateFormat();
			
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			System.out.println("site code ->["+siteCode+"]");
			
			System.out.println("Current Column> > > > ->["+currentColumn+"]");
			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			
			valueXmlString.append("</Header>\r\n" );
			
			switch ( currentFormNo )
			{
			  	case 1:
				{
								
					if ( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
					{
						System.out.println("--------------item default --------------");
						valueXmlString.append( "<Detail1>\r\n" );
												
						valueXmlString.append( "</Detail1>\r\n" );
					}					
					else if ( currentColumn.trim().equalsIgnoreCase( "wave_id" ))
					{
							
						columnValue = genericUtility.getColumnValue("wave_id",currDom);							
						System.out.println("wave_id ["+columnValue+"] ");							
						valueXmlString.append( "<Detail1>\r\n" );
						
						if( (columnValue !=null && columnValue.trim().length() > 0) )
						{						
							
							sql = " SELECT WAVE_ID FROM WAVE_TASK WHERE WAVE_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, waveId);
							rs = pstmt.executeQuery();								
							/*if(rs.next())
							{
								valueXmlString.append( "<ptcn protect ='1' ><![CDATA[" ).append( "" ).append( "]]></ptcn>\r\n" );									
							}
							else
							{
								valueXmlString.append( "<ptcn protect ='0' ><![CDATA[" ).append( "" ).append( "]]></ptcn>\r\n" );
							}*/
							
						}
						/*else
						{
							valueXmlString.append( "<ptcn protect ='0' ><![CDATA[" ).append( "" ).append( "]]></ptcn>\r\n" );
						}*/
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
						valueXmlString.append( "</Detail1>\r\n" );
						
					}
					else if ( currentColumn.trim().equalsIgnoreCase( "ptcn" ))
					{
							
						columnValue = genericUtility.getColumnValue("ptcn",currDom);							
						System.out.println("ptcn ["+columnValue+"] ");							
						valueXmlString.append( "<Detail1>\r\n" );
						
						if( (columnValue !=null && columnValue.trim().length() > 0) )
						{								
							sql = " SELECT PTCN FROM WAVE_TASK_DET WHERE PTCN = ? ";								
							System.out.println("queryString="+sql);
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,columnValue);
							rs =pstmt.executeQuery();						
							if(rs.next())
							{
								valueXmlString.append( "<wave_id protect ='1' ><![CDATA[" ).append( "" ).append( "]]></wave_id>\r\n" );
							}
							else
							{
								valueXmlString.append( "<wave_id protect ='0' ><![CDATA[" ).append( "" ).append( "]]></wave_id>\r\n" );
							}				
						}
						else
						{
							valueXmlString.append( "<wave_id protect ='0' ><![CDATA[" ).append( "" ).append( "]]></wave_id>\r\n" );
						}
						valueXmlString.append( "</Detail1>\r\n" );				
					}			
				}
				break;
			}
			valueXmlString.append( "</Root>\r\n" );
		}
		catch(Exception e)
		{
			System.out.println("Exception: WaveGenerationIC: defaultItemChanged(Document dom): " + e.getMessage() + ":");
			e.printStackTrace();
			errString = genericUtility.createErrorString(e);
			
		}		
		finally
		{
			try
			{
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception es)
			{
				es.printStackTrace();
				errString = genericUtility.createErrorString(es);
			}
		}
		//Changed by sumit on 13/05/13 closing connection end.
		return valueXmlString.toString();		
	}
	
	private String checkNull( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}
	
	/**
	 * The method is defined without any parameter and returns blank string
	 */
	@Override
	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
	 */	
	@Override
	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
	 */
	@Override
	public String wfValData(Document currFormDataDom, Document hdrDataDom, Document allFormDataDom, String objContext, String xtraParams) throws RemoteException, ITMException
	{  
		System.out.println("GenerateLabelsIC.wfValData()");
		String columnValue ="";
		String errString = "";
		String sql ="";
		String waveId ="";
		String ptcn = "";		
		String printer = "";
		String printLabel = "";
		String printStatus = "";
		String tranCode = "";
		String imagePath = "";
		int count = 0, cnt = 0;
		//int rowCount = 0;
		int noOfChilds = 0;
		int noOfParent = 0;	

		GenericUtility genericUtility = GenericUtility.getInstance();		

		Connection conn = null;
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null, rs1 = null;		

		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		Set errSet = null;
		
		DistCommon discommon = new DistCommon();	
		
		try
		{	
			int currentFormNo = 0;				
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB( "DriverITM" );
			connDriver = null;

			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 ( "wave_id".equalsIgnoreCase( childNodeName ) )
					{
						waveId = checkNull(genericUtility.getColumnValue( "wave_id", currFormDataDom ));
						ptcn = checkNull(genericUtility.getColumnValue( "ptcn", currFormDataDom ));
						System.out.println(" wave_id ["+waveId+"]");
						if(waveId.trim().length() > 0 )
						{
							sql = " SELECT HOLD_STATUS,CANCEL FROM WAVE_TASK WHERE WAVE_ID = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, waveId);
							rs = pstmt.executeQuery();
							if( rs.next())
							{
								if( "Y".equalsIgnoreCase(rs.getString("HOLD_STATUS")) || "Y".equalsIgnoreCase(rs.getString("CANCEL")))
								{
									errFields.add( childNodeName.toLowerCase() );
									errList.add( "WAVECAORHO" );									
								}
								else if( ptcn.trim().length() > 0 )
								{
									sql = "SELECT DISTINCT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN = ?";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, ptcn);
									rs1 = pstmt1.executeQuery();
									if( rs1.next())
									{
										System.out.println(" wave_id ["+rs1.getString("WAVE_ID"));
										if( !waveId.trim().equalsIgnoreCase(rs1.getString("WAVE_ID").trim()))
										{
											errFields.add( childNodeName.toLowerCase() );
											errList.add( "WAVEAPTNSM" );
										}
									}
									rs1.close(); rs1 = null;
									pstmt1.close(); pstmt1 = null;
								}
								
								if( "N".equalsIgnoreCase(rs.getString("HOLD_STATUS")) && "N".equalsIgnoreCase(rs.getString("CANCEL")))
								{
									
									sql = "SELECT REF_TYPE FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_TYPE IN (?,?)";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, waveId);
									pstmt1.setString(2, "M");
									pstmt1.setString(3, "P");
									rs1 = pstmt1.executeQuery();
									if( !rs1.next())
									{										
										errFields.add( childNodeName.toLowerCase() );
										errList.add( "INVAWAVE11" );									
									}
									rs1.close(); rs1 = null;
									pstmt1.close(); pstmt1 = null;
									
									/*sql = "SELECT COUNT(DISTINCT PTCN) FROM WAVE_TASK_DET WHERE WAVE_ID = ?";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, waveId);*/
									
								}
								
							}
							else
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "INVAWAVEID" );								
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
														
						}
						else
						{
							ptcn = checkNull(genericUtility.getColumnValue( "ptcn", currFormDataDom ));
							if( (ptcn ==null || ptcn.trim().length() == 0) && (waveId ==null || waveId.trim().length() == 0) )
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "NULLBOTHFD" );
								/*errString = itmDBAccessEJB.getErrorString("","NULLBOTHFD","","",conn);
								return errString;*/
							}
						}
					}
					else if ( "ptcn".equalsIgnoreCase( childNodeName ) )
					{
						ptcn = checkNull(genericUtility.getColumnValue( "ptcn", currFormDataDom ));
						waveId = checkNull(genericUtility.getColumnValue( "wave_id", currFormDataDom ));
						
						System.out.println(" ptcn ["+ptcn+"] waveId ["+waveId+"]");
						if(ptcn.trim().length() > 0 )
						{
							sql = " SELECT CARTON_TYPE FROM CARTON_MASTER WHERE PTCN = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, ptcn);
							rs = pstmt.executeQuery();
							boolean isRecordExist = false; 
							boolean isMasterOrParcelPresent  = false;
							while( rs.next())
							{
								isRecordExist = true; 
								if( "P".equalsIgnoreCase(rs.getString("CARTON_TYPE")) || "M".equalsIgnoreCase(rs.getString("CARTON_TYPE")))
								{
									isMasterOrParcelPresent = true;
									continue;
								}								
							}							
							if( !isRecordExist)
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "INVAPTCN09" );
							}
							else if( isRecordExist && !isMasterOrParcelPresent )
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "INVAPTCN10" );
							}								
							rs.close();rs = null;
							pstmt.close();pstmt = null;	
							
							imagePath = discommon.getDisparams("999999","SHIP_WITH_PATH",conn);	
							System.out.println("imagePath -->"+imagePath);
							File file = new File(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+"Ship"+".ser" );
				    		if(!file.exists())
							{								
				    			errFields.add( childNodeName.toLowerCase() );
								errList.add( "FILENFOUND" );
							}							
							
							sql = "SELECT DISTINCT PRINT_STATUS FROM WAVE_STATUS_ORG WHERE PTCN = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, ptcn);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								printStatus = rs.getString("PRINT_STATUS");								
							}
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							tranCode = isTranCodeChanged(ptcn, conn);					
							System.out.println("printStatus ["+printStatus+"] tranCode ["+tranCode+"]");
							if( "Y".equalsIgnoreCase(printStatus) && "NO".equalsIgnoreCase(tranCode))
							{								
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "ALLREPRINT" );
							}
							
							if(isRecordExist)
							{
								sql = " SELECT D.CONFIRMED FROM DESPATCH D, WAVE_TASK_DET WTD WHERE WTD.PTCN = ? AND WTD.WAVE_ID = D.WAVE_ID " +
										" AND WTD.SALE_ORDER = D.SORD_NO AND WTD.REF_ID = D.DESP_ID ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, ptcn);
								//pstmt.setString(2, "N");
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									if( "Y".equalsIgnoreCase(rs.getString("CONFIRMED")))
									{
										errFields.add( childNodeName.toLowerCase() );
										errList.add( "DESPCONFIR" );
									}
								}
								rs.close(); rs = null;
								pstmt.close(); pstmt = null;
							}							
							
						}
						else
						{
							
							if( (ptcn ==null || ptcn.trim().length() == 0) && (waveId ==null || waveId.trim().length() == 0) )
							{
								errFields.add( childNodeName.toLowerCase() );
								errList.add( "NULLBOTHFD" );
								/*errString = itmDBAccessEJB.getErrorString("","NULLBOTHFD","","",conn);
								return errString;*/
							}
							
						}
						
					}																	
					else if ( "printer_name".equalsIgnoreCase( childNodeName ) )
					{
						
						printer = checkNull(genericUtility.getColumnValue( "printer_name", currFormDataDom ));						
						printLabel = checkNull(genericUtility.getColumnValue( "print_label", currFormDataDom ));
						System.out.println(" printer from input parameter ["+printer+"] and print label ->["+printLabel+"]");
						PrintService[] printServices = PrinterJob.lookupPrintServices();
						ArrayList<String> arry = new ArrayList<String>();	
						
						if(printer.trim().length() == 0 )
						{
							errFields.add( childNodeName.toLowerCase() );
							errList.add( "NLLPRINTER" );
						}
						else
						{
							System.out.println(" in inside if");
							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();				           
						}
					}					
				}
			} //End of case 1 Validation
			break;				
					
			}//End of switch statement
			errSet = new HashSet(errList);
			errList = new ArrayList(errSet);
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);					
					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: WaveGenerationIC: wfValData( Document currFormDataDom ): " + e.getMessage() + ":" );
			//throw new ITMException(e);
			e.printStackTrace();			
		}
		finally
		{
			try
			{
				if (rs!=null)
				{
					rs.close();
					rs = null;
				}				
				
				if( conn != null  )
				{
					conn.close(); conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception : WaveGenerationIC:wfValData : " + e.getMessage() );
				throw new ITMException(e);
			}
		}
		System.out.println( "errString>>>>>>>::"+errString );
		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;
	}
	
	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) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	
	public String isTranCodeChanged(String ptcn, Connection conn) throws ITMException
	{
		String sql = "";		
		String shipTranCode = "",cartonMasterTranCode = "";	
		String errString = "";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			sql = "SELECT TRAN_CODE FROM WAVE_STATUS_ORG WHERE PTCN = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cartonMasterTranCode = rs.getString("TRAN_CODE");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			
			sql = "SELECT S.TRAN_CODE FROM SHIPMENT S , SHIP_DOCS SD WHERE SD.PTCN = ? AND S.SHIPMENT_ID = SD.SHIPMENT_ID ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				shipTranCode = rs.getString("TRAN_CODE");
			}
			else
			{
				return "NO";
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( cartonMasterTranCode.equalsIgnoreCase(shipTranCode))
			{
				return "NO";				
			}
			else
			{
				return cartonMasterTranCode;
			}
			
		}
		catch(Exception e)
		{
			errString = 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)
			{
				
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		
		///return cartonMasterTranCode;
	}
	
}
