/*
    Window Name : w_gen_label
    Author: Sumit Sarkar
    Purpose: To generate new label if require.
*/

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;


import java.awt.image.BufferedImage;
import java.awt.print.PrinterJob;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.TreeMap;

import javax.imageio.ImageIO;
import javax.print.PrintService;

import org.omg.CORBA.Object;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import com.adsionline.ADSIShippingWS.*;

import org.w3c.dom.Element;
import org.apache.axis.Constants;
import org.apache.axis.description.ParameterDesc;
import org.apache.poi.hssf.record.formula.functions.Reftext;
import org.xml.sax.helpers.DefaultHandler;
import sun.misc.BASE64Decoder;

@javax.ejb.Stateless
public class GenerateLabelsPrc extends ProcessEJB implements GenerateLabelsPrcLocal, GenerateLabelsPrcRemote //SessionBean
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	CommonConstants commonConstants = new CommonConstants();	
	DistCommon discommon = new DistCommon();	
	String chgUser = null;
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
									
		    retStr = process(headerDom, detailDom, windowName, xtraParams);
		    
		}
		catch (Exception e)
		{
			System.out.println("Exception :GenerateLabels :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
		
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("detailDom------------------->"+detailDom);
		String childNodeName = "";
		String errCode = "";		
		String errString = "";	
		String ptcn = "",waveId = "";
		String sql = "",upddCartonsql = "" ,sqlChgStatus = "",sqlPrintStatus = "",
		sqlMaster = "",sqlCrtnChg = "",sqlGetTrack = "";
		String printStatus = "";
		String confirmed = "";
		String adsiShippingURL = "",imagePath = "";
		String masterCarton = "",itemCode = "",trackingNo = "",parcelCartonNo = "",
		cartonStatus = "",chgStatus = "";
	
		String tempItemCode = "";
		String value = "",outerCartonNo = "",trackNo = "",pckgId = "";
		long msnNo = 0;
		String packTranId = "",serviceCode = "",saleOrder = "";
		String printer = "";
		long oldMsnNo = 0;
		String oldCarrierSymbol = "",carrierSymbol ="";
		String oldCarrierFriendlyName = "",carrierFriendlyName = "";
		String response = "",refType = "",frtTerm = "",sSQL = "";
		Connection conn = null;		
		
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		int count = 0,noArt = 0;
		int package2ArrSize = 0;
		
		double packSize = 0.0;
		double weight = 0.0,actWeight = 0.0,itemWeght = 0.0,shipQty = 0.0;	
		double totalFrtAmt = 0.0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
				
		PreparedStatement pstmt = null,pstmt1 = null,ptcnPSTMT = null,
		pstmtUpdCarton = null,pstmtChg = null, pstmtPrintStatus = null,
		pstmtMaster = null,pstmtCrtnChg = null,pstmtGetTrack = null,pstmtDesp = null;	
		ResultSet rs = null,rs1 = null,ptcnRS = null ,rsPrintStatus = null,
		rsMaster = null,rsGetTrack = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ADSIShippingLocator locator = null;
		ADSIShippingSoap  soap = null;
		List arryOuterCartonNo = new ArrayList();
		LinkedHashMap linkedMap = new LinkedHashMap();
		ArrayList ptcnList = new ArrayList();
		int pUpd = 0;
		String glblPTCN = "";
		
		try
		{
						
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;	
			ptcn = checkNull(genericUtility.getColumnValue("ptcn",headerDom));
			glblPTCN = ptcn;
			waveId = checkNull(genericUtility.getColumnValue("wave_id",headerDom));
			printer = checkNull(genericUtility.getColumnValue("printer_name",headerDom));
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			System.out.println(" ptcn ["+ptcn+"] chgUser["+chgUser+"]");
			
			upddCartonsql = "UPDATE CARTON_MASTER SET TRACKING_NO = ?,MSN_NO = ? ,CARRIER_SYMBOL = ?,CARRIER_FRIENDLYNAME = ? WHERE CARTON_NO = ?";
			pstmtUpdCarton = conn.prepareStatement(upddCartonsql);
			
			//sqlChgStatus = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ? WHERE PTCN = ?";
			sqlChgStatus = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ?,TRAN_CODE__NEW =(SELECT TRAN_CODE FROM DESPATCH WHERE PTCN =?) WHERE PTCN = ?";
			pstmtChg = conn.prepareStatement(sqlChgStatus);
			
			sqlPrintStatus = "SELECT DISTINCT PRINT_STATUS FROM WAVE_STATUS_ORG WHERE PTCN = ?";
			pstmtPrintStatus = conn.prepareStatement(sqlPrintStatus);
			
			//sqlMaster = "SELECT MASTER_CARTON,TRACKING_NO,CARTON_NO,STATUS FROM CARTON_MASTER WHERE PTCN = ? AND CARTON_TYPE <> ? ORDER BY CARTON_NO,MASTER_CARTON";
			sqlMaster = "SELECT MASTER_CARTON,TRACKING_NO,CARTON_NO,STATUS,CHANGE_STATUS,MSN_NO,CARRIER_SYMBOL,CARRIER_FRIENDLYNAME,CARTON_TYPE FROM CARTON_MASTER WHERE PTCN = ? AND CARTON_TYPE <> ? ORDER BY CARTON_NO,MASTER_CARTON";
			pstmtMaster = conn.prepareStatement(sqlMaster);
			
			sqlCrtnChg = "UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE PTCN = ?";
			pstmtCrtnChg = conn.prepareStatement(sqlCrtnChg);
			
			sqlGetTrack = 	" SELECT TRACKING_NO,MSN_NO,CARRIER_SYMBOL , CARRIER_FRIENDLYNAME FROM CARTON_MASTER WHERE CARTON_NO = ?";
    		pstmtGetTrack = conn.prepareStatement(sqlGetTrack);
			
			long timeBefore = System.currentTimeMillis();
			adsiShippingURL = discommon.getDisparams("999999","WMS_ADSI_SOAP_URL",conn);
			imagePath = discommon.getDisparams("999999","SHIP_WITH_PATH",conn);	
			System.out.println("adsiShippingURL -->"+adsiShippingURL);
			System.out.println("imagePath -->"+imagePath);
			locator = new ADSIShippingLocator();
			System.out.println(" Connecting ADSI server ................");
    		soap = locator.getADSIShippingSoap(new URL(adsiShippingURL));
    		long timeAfter = System.currentTimeMillis();
    		System.out.println(" is server active ["+soap.testADSIServer()+"]");    			    		
    		if(!soap.testADSIServer())
    		{
    			errString = itmDBAccessEJB.getErrorString("","ADSISRVERR","","",conn);
    			return errString;
    		}   
			
			if( ptcn.trim().length() > 0 && waveId.trim().length() == 0)
			{
				sql = "SELECT WAVE_ID FROM WAVE_TASK_DET WHERE PTCN = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, ptcn);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					waveId = rs.getString("WAVE_ID");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
			}
			if( waveId.trim().length() > 0 && ptcn.trim().length() == 0)
			{
				sql = " SELECT DISTINCT PTCN,SALE_ORDER,REF_TYPE FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_TYPE IN (?,?,?)";
				ptcnPSTMT = conn.prepareStatement(sql);
				ptcnPSTMT.setString(1, waveId);
				ptcnPSTMT.setString(2, "M");
				ptcnPSTMT.setString(3, "P");
				ptcnPSTMT.setString(4, "A");
				ptcnRS = ptcnPSTMT.executeQuery();
			}
			else if( waveId.trim().length() > 0 && ptcn.trim().length() > 0)
			{
				sql = " SELECT DISTINCT PTCN,SALE_ORDER,REF_TYPE FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND PTCN = ? AND REF_TYPE IN (?,?,?) ";
				ptcnPSTMT = conn.prepareStatement(sql);
				ptcnPSTMT.setString(1, waveId);
				ptcnPSTMT.setString(2, ptcn);
				ptcnPSTMT.setString(3, "M");
				ptcnPSTMT.setString(4, "P");
				ptcnPSTMT.setString(5, "A");
				ptcnRS = ptcnPSTMT.executeQuery();
			}
			
			while(ptcnRS.next())
			{
				saleOrder = ptcnRS.getString("SALE_ORDER");
				ptcn = ptcnRS.getString("PTCN");
				refType = ptcnRS.getString("REF_TYPE");
				//This check is not required for testing.start
				/*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 AND D.CONFIRMED = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, ptcn);
				pstmt.setString(2, "N");
				rs = pstmt.executeQuery();
				if(!rs.next())
				{
					errString = itmDBAccessEJB.getErrorString("","DESPCONFIR","","",conn);
					return errString;
					continue;
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;				*/
				//This check is not required for testing.end
				
				if(ptcnList.contains(ptcn))
				{
					continue;
				}
				else
				{
					ptcnList.add(ptcn);
				}
				
				
						pstmtPrintStatus.setString(1, ptcn);
						rsPrintStatus = pstmtPrintStatus.executeQuery();
						if(rsPrintStatus.next())
						{
							printStatus = rsPrintStatus.getString("PRINT_STATUS");
						}
						
						
						serviceCode = isTranCodeChanged(ptcn, conn);					
						//serviceCode = "UPSGND";
						System.out.println("printStatus ["+printStatus+"] serviceCode ["+serviceCode+"]");			
						if( "Y".equalsIgnoreCase(printStatus) && "NO".equalsIgnoreCase(serviceCode))
						{
							/*errString = itmDBAccessEJB.getErrorString("","ALLREPRINT","","",conn);
							return errString;*/
							continue;
						}			
						
						
						File file = new File(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+"Ship"+".ser" );
			    		if(!file.exists())
						{
							errString = itmDBAccessEJB.getErrorString("","FILENFOUND","");
			    			return errString;
						}			
						
							
			    		
			    		FileInputStream fis = new FileInputStream(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+"Ship"+".ser" );
						ObjectInputStream ois = new ObjectInputStream(fis);
						System.out.println(" file loaded ");
						Shipment2 shipment2 = (Shipment2) ois.readObject();   	    
						System.out.println("objReturn2 ["+shipment2.getPackages2().length+"]" );
						
						package2ArrSize = shipment2.getPackages2().length;
						System.out.println("package2ArrSize ["+package2ArrSize+"]");			
						Package2[] packages2 = new Package2[package2ArrSize];
						packages2 = shipment2.getPackages2();
						
						com.adsionline.ADSIShippingWS.Service service = new com.adsionline.ADSIShippingWS.Service();
						service = shipment2.getService();				
						
						if( !"NO".equalsIgnoreCase(serviceCode))
						{
							System.out.println(" old service code ["+service.getFriendlyName());
							System.out.println(" new service code ["+serviceCode+"]");
							service.setFriendlyName(serviceCode);
						}
						
					
						if( "N".equalsIgnoreCase(printStatus) || !"NO".equalsIgnoreCase(serviceCode) )
						{
		
							pstmtMaster.setString(1, ptcn);
							pstmtMaster.setString(2, "C");
							rsMaster = pstmtMaster.executeQuery();
							
							while(rsMaster.next())
							{
								
								
								masterCarton = rsMaster.getString("MASTER_CARTON");
								System.out.println("masterCarton ["+masterCarton+"]");
								parcelCartonNo  = rsMaster.getString("CARTON_NO");
								cartonStatus = rsMaster.getString("STATUS");
								chgStatus = rsMaster.getString("CHANGE_STATUS");
								oldMsnNo =  rsMaster.getLong("MSN_NO");
								oldCarrierSymbol =  rsMaster.getString("CARRIER_SYMBOL");
								refType =  rsMaster.getString("CARTON_TYPE");
								
								System.out.println("parcelCartonNo ["+parcelCartonNo+"]CARRIER_SYMBOL["+oldCarrierSymbol+"]["+oldMsnNo+"]Change Status["+chgStatus+"]");
								
								if("M".equalsIgnoreCase(refType))
								{
									boolean isExist = false;
									tempItemCode = "";
									int itemCount = 0;
									//LinkedList liklist = new LinkedList();
									
									/*if( !"NO".equalsIgnoreCase(serviceCode) )
									{
										sql = 	" SELECT CM.ITEM_CODE,WOD.WEIGHT FROM CARTON_MASTER CM, WAVE_STATUS_ORG_DET WOD WHERE CM.PTCN = WOD.PTCN " +
												" AND CM.CARTON_NO = WOD.CARTON_NO AND CM.MASTER_CARTON = ? AND CM.CARTON_TYPE = ? AND CM.STATUS <> ? " +
												" ORDER BY CM.ITEM_CODE";
										pstmt1 = conn.prepareStatement(sql);
										pstmt1.setString(1, masterCarton);
										pstmt1.setString(2, "C");
										pstmt1.setString(3, "R");							
									}
									else
									{
										sql = 	" SELECT CM.ITEM_CODE,WOD.WEIGHT FROM CARTON_MASTER CM, WAVE_STATUS_ORG_DET WOD WHERE CM.PTCN = WOD.PTCN " +
												" AND CM.CARTON_NO = WOD.CARTON_NO AND CM.MASTER_CARTON = ? AND CM.CARTON_TYPE = ? AND CM.STATUS <> ? " +
												" AND CM.CHANGE_STATUS = ? ORDER BY CM.ITEM_CODE";
										pstmt1 = conn.prepareStatement(sql);
										pstmt1.setString(1, masterCarton);
										pstmt1.setString(2, "C");
										pstmt1.setString(3, "R");
										pstmt1.setString(4, "Y");
									}*/
									
									sql = 	" SELECT CM.ITEM_CODE,WOD.WEIGHT FROM CARTON_MASTER CM, WAVE_STATUS_ORG_DET WOD WHERE CM.PTCN = WOD.PTCN " +
											" AND CM.CARTON_NO = WOD.CARTON_NO AND CM.MASTER_CARTON = ? AND CM.CARTON_TYPE = ? AND CM.STATUS <> ? " +
											" ORDER BY CM.ITEM_CODE";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, masterCarton);
									pstmt1.setString(2, "C");
									pstmt1.setString(3, "R");
									
									rs1 = pstmt1.executeQuery();
									
									while( rs1.next())
									{
										isExist = true;							
										packSize = rs1.getDouble("WEIGHT");
										itemCode = rs1.getString("ITEM_CODE");
										
										/*if( !tempItemCode.equalsIgnoreCase(itemCode))
										{
											tempItemCode = itemCode;								
											liklist.add(tempItemCode);
											//linkedMap.put(arg0, arg1);
											
										}
										 */										
										weight = weight + packSize;
										linkedMap.put(itemCode, weight);
									}
									
									rs1.close(); rs1 = null;
									pstmt1.close(); pstmt1 = null;
									
									if("R".equalsIgnoreCase(cartonStatus))
									{						
										packages2[count] = null;
										
										System.out.println("CHANGES NOT EXIST[" +count+"]Master Carton ["+masterCarton+"]chgStatus["+chgStatus+"]");

											if("Y".equalsIgnoreCase(chgStatus))
											{
												StringReturn testReturn = soap.voidPackage(oldCarrierSymbol,oldMsnNo);
								    			response =  testReturn.getResponse();
								    			System.out.println("response  Master Carton void----["+response+"] for master carton["+masterCarton+"]");	
											}

										/*//Changed By Pragyan.start
										else
										{
											arryOuterCartonNo.add(masterCarton);
											Package2[] oldPkg = shipment2.getPackages2();
											String key1,val1 = "";
											boolean elmentFound = false;
											for(int cPtr = 0 ;cPtr <oldPkg.length;cPtr++)
											{
												Package2 PKG = oldPkg[cPtr];
												for(int Cadd=0;Cadd<PKG.getAdditionalFields().length;Cadd++)
												{
													key1 = PKG.getAdditionalFields()[Cadd].getName();
													val1 = PKG.getAdditionalFields()[Cadd].getValue();
													System.out.println("key1"+key1);
													System.out.println("val1"+val1);
													
													if(masterCarton.equalsIgnoreCase(val1))
													{
														elmentFound = true;
														break;
													}
													
													
													
												}
												
												
												if(elmentFound)
												{
													packages2[count] = PKG;
												}	
												
												
											}
											
											
											System.out.println("IF PACKAGE EXIST["+oldPkg[count].getAdditionalFields().toString());
											
										}*/
										count++;
										//Changed By Pragyan.end
										continue;
									}
									else
									{
										
										if( weight > 0.0)
										{
											arryOuterCartonNo.add(masterCarton);
											CommodityInfo[] comInfo = new CommodityInfo[linkedMap.size()];
											comInfo = setCommodityInfos(linkedMap, masterCarton, saleOrder, soap,refType, conn);				
											System.out.println("old weight ["+packages2[count].getWeight()+"]");
					    					System.out.println("new weight ["+weight+"]");
					    					packages2[count].setWeight(weight);
					    					System.out.println("old comodity info length ["+packages2[count].getCommodityInfos().length+"]");
					    					System.out.println("new comodity info length ["+comInfo.length+"]");
					    					packages2[count].setCommodityInfos(comInfo);
										}
										else
										{
												
												
												System.out.println("In case of weight zero ["+weight+"]");
												packages2[count] = null;
											
												System.out.println("ON ELSE BLOCK CHANGES NOT EXIST[" +count+"]Master Carton ["+masterCarton+"]chgStatus["+chgStatus+"]");

												if("Y".equalsIgnoreCase(chgStatus))
												{
													StringReturn testReturn = soap.voidPackage(oldCarrierSymbol,oldMsnNo);
									    			response =  testReturn.getResponse();
									    			System.out.println("on else response  Master Carton void----["+response+"] for master carton["+masterCarton+"]");	
												}

										}
										count++;
									}
									weight = 0;
									
									System.out.println(" arryOuterCartonNo.size ["+arryOuterCartonNo.size()+"]");
					    						
								}
								else
								{
									
									/*sql = 	" SELECT CM.ITEM_CODE,WOD.WEIGHT,CM.MSN_NO,CM.CARRIER_SYMBOL , CM.CARRIER_FRIENDLYNAME FROM CARTON_MASTER CM, " +
										" WAVE_STATUS_ORG_DET WOD WHERE CM.PTCN = WOD.PTCN " +
										" AND CM.CARTON_NO = WOD.CARTON_NO AND CM.CARTON_NO = ? AND CM.CARTON_TYPE = ? AND CM.STATUS = ? " +
										" ORDER BY CM.ITEM_CODE";
									pstmt1 = conn.prepareStatement(sql);
									pstmt1.setString(1, parcelCartonNo);
									pstmt1.setString(2, "P");
									pstmt1.setString(3, "R");
									
									rs1 = pstmt1.executeQuery();
									
									if( rs1.next())
									{
													
										packages2[count] = null;
										
										
										packSize = rs1.getDouble("WEIGHT");
										itemCode = rs1.getString("ITEM_CODE");
										oldMsnNo = rs1.getLong("MSN_NO");
						    			oldCarrierSymbol = rs1.getString("CARRIER_SYMBOL");
						    			oldCarrierFriendlyName = rs1.getString("CARRIER_FRIENDLYNAME");
						    			
						    			StringReturn testReturn = soap.voidPackage(oldCarrierSymbol,oldMsnNo);
						    			response =  testReturn.getResponse();
						    			System.out.println("response  Parcel void----["+response+"]");						
									}
									else
									{
										arryOuterCartonNo.add(parcelCartonNo);
									}*/
									
									if("R".equalsIgnoreCase(cartonStatus))
									{
										packages2[count] = null;
										StringReturn testReturn = soap.voidPackage(oldCarrierSymbol,oldMsnNo);
						    			response =  testReturn.getResponse();
						    			System.out.println("response  Parcel void----["+response+"]");		
									}
									else
									{
										arryOuterCartonNo.add(parcelCartonNo);
										
										if("A".equalsIgnoreCase(refType) && "Y".equalsIgnoreCase(chgStatus))
										{
											
											sql = 	"SELECT ITEM_CODE,SUM(QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE CASE WHEN DEALLOC_QTY IS NULL"
													+" THEN 0 ELSE DEALLOC_QTY END END) AS QTY FROM PICK_ORD_DET WHERE CARTON_NO = ? GROUP BY ITEM_CODE";
											pstmt1 = conn.prepareStatement(sql);
											pstmt1.setString(1, parcelCartonNo);
											rs1 = pstmt1.executeQuery();
											
											while( rs1.next())
											{
																	
												itemCode = rs1.getString("ITEM_CODE");	
												shipQty = rs1.getDouble("QTY");	
												HashMap itmVolumeMap = getItemVoumeMap(itemCode, "", conn);
												itemWeght = (Double)itmVolumeMap.get("ITEM_WEIGHT");
												weight = weight + itemWeght * shipQty;
												linkedMap.put(itemCode, shipQty);
											}
											
											rs1.close(); rs1 = null;
											pstmt1.close(); pstmt1 = null;
																			
											
											CommodityInfo[] comInfo = new CommodityInfo[linkedMap.size()];
											comInfo = setCommodityInfos(linkedMap, masterCarton, saleOrder, soap,refType, conn);				
											System.out.println("old weight ["+packages2[count].getWeight()+"]");
					    					System.out.println("new weight ["+weight+"]");
					    					packages2[count].setWeight(weight);
					    					System.out.println("old comodity info length ["+packages2[count].getCommodityInfos().length+"]");
					    					System.out.println("new comodity info length ["+comInfo.length+"]");
					    					packages2[count].setCommodityInfos(comInfo);
										}
									}
									
									oldMsnNo = 0;oldCarrierSymbol = "";
									oldCarrierFriendlyName = "";
									/*rs1.close(); rs1 = null;
									pstmt1.close(); pstmt1 = null;*/
									count++;
								}
								
				    							
							}//while(rsMaster.next())
							pstmtMaster.clearParameters();
							if( rsMaster != null)
							{
								rsMaster.close(); 
								rsMaster = null;
							}
												
							
						}
						
						if( arryOuterCartonNo.size() == 0)
						{
							
							pstmtCrtnChg.setString(1, "N");
							pstmtCrtnChg.setString(2, ptcn);
							pstmtCrtnChg.executeUpdate();
							pstmtChg.setString(1, "Y");
							pstmtChg.setString(2, ptcn);
							pstmtChg.setString(3, ptcn);
							int test = pstmtChg.executeUpdate();
						
							errString = itmDBAccessEJB.getErrorString("Void Records Successfully","NOTHING2PR","");
							System.out.println("Only Voiding the Error ["+errString+"]");
							errString = "";
			    			//return errString;
						}
						else
						{
						
								
								Collections.sort(arryOuterCartonNo);
								shipment2.setPackages2(packages2); 
								
								///Changed By Pragyan To change the date and set current date as of shipment date.start
								java.util.Date date = new java.util.Date();
				        		Calendar calendar = Calendar.getInstance();
				        		calendar.setTime(date);
				        		shipment2.setShipDate(calendar);
				        		////Changed By Pragyan To change the date and set current date as of shipment date.end
				        		///Changed By Pragyan To change the date.end
				        		System.out.println("Generating Label for the date ["+calendar.getTime().getTime()+"]");
								//Changed By Pragyan as 11-jul-13 suggested by brian change it to default rotation
					    		//ShipmentResultReturn2 objReturn2=soap.shipWithLabel(shipment2, ShipWithLabelType.PNG, ImageRotation.Rotation_180);
								ShipmentResultReturn2 objReturn2=soap.shipWithLabel(shipment2, ShipWithLabelType.PNG, ImageRotation.Rotation_Default);
					    		System.out.println("Is successfully Executed ["+objReturn2.isSuccess()+"]");
					    		System.out.println("Return Messages ["+objReturn2.getMessage()+"]");
					    		if(!objReturn2.isSuccess())
					    		{
					    			
					    			//errString = itmDBAccessEJB.getErrorString(objReturn2.getMessage(),"SOAPURLUAL","");
					    			errString = getError(objReturn2.getMessage(),"SOAPURLUAL", conn);
					    			return errString;
					    		}
					    		else
					    		{
					
					    			//Updating ADSI getting the images.start
									ShipmentResult2 objShipResult = objReturn2.getShipmentResultResponse2();
									int  lenPacakges =  objShipResult.getPackageResults2().length;		
									
									carrierSymbol= objShipResult.getServiceSymbol();
									System.out.println("serviceSymbol["+carrierSymbol+"]");
									carrierSymbol = carrierSymbol.substring(0,carrierSymbol.lastIndexOf(".") );
						   	    	System.out.println("Carrier Symbol ["+carrierSymbol+"]");
									
									Carrier[] carr = soap.getCarriers().getCarrierList();
					             	for(int j = 0;j<carr.length;j++)
					             	{
					             		Carrier carr1 = carr[j];
					             		System.out.println("Carrier Symbol["+carr1.getSymbol());
					             		System.out.println("Friendly Name["+carr1.getFriendlyName());
					             		System.out.println("Short Name["+carr1.getShortName());
					             		
					             		if(carr1.getSymbol().equalsIgnoreCase(carrierSymbol))
					             		{
					             			carrierFriendlyName = carr1.getFriendlyName();
					             			break;
					             		}
					        
				
					             	}
									
									for(int pCtr=0;pCtr < lenPacakges;pCtr++)
									{
										PackageResult2 pkg2frm =  (PackageResult2)objShipResult.getPackageResults2()[pCtr];	
										value = pkg2frm.getBase64Label();
										pckgId = pkg2frm.getPackageID();
										//change by rekha on 23-05-13.start
										pkg2frm.getAdditionalFields();
										trackNo = pkg2frm.getTrackingNumber();
										msnNo =  pkg2frm.getMSN();
										for(int Cadd=0;Cadd<pkg2frm.getAdditionalFields().length;Cadd++)
										{
										System.out.println("additional fields are Name:"+pkg2frm.getAdditionalFields()[Cadd].getName());
										System.out.println("additional fields are Value:"+pkg2frm.getAdditionalFields()[Cadd].getValue());
										}
										//change by rekha on 23-05-13. end
										String cnumber = arryOuterCartonNo.get(pCtr).toString();
										System.out.println("cnumber is :"+cnumber);
										System.out.println("Package ID["+pckgId+"]");
										System.out.println("New Tracking No --------------["+pkg2frm.getTrackingNumber()+"]");
										System.out.println("NeW MSN No -------------------["+msnNo+"]");
										System.out.println("BASE64lABEL Length["+value.length()+"]");
										BASE64Decoder decoder = new BASE64Decoder(); 
										System.out.println("Package id for carton no is :[" +decoder.decodeBuffer(pckgId) +"]");
								
										byte[] imgBytes = decoder.decodeBuffer(value);    
										System.out.println("IMAGE Byte Length ["+imgBytes.length+"]");
										BufferedImage bufImg = ImageIO.read(new ByteArrayInputStream(imgBytes)); 						
										File imgOutFile = new File(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+cnumber +".png");
										File file1 = new File(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+cnumber +".png");
										//Renaming existing file
										if(!file1.exists())
										{
											//errString = itmDBAccessEJB.getErrorString("","FILENFOUND","");
							    			//return errString;
										}
										else
										{
											File oldFile = new File(imagePath+ File.separator+"Ship_Label"+File.separator+ ptcn + File.separator + ptcn+"_"+cnumber +"_old.png");
											if(!oldFile.exists())
											{
												file1.renameTo(oldFile);
											}
										}
										String path = imgOutFile.getPath();
										System.out.println("Get Path ["+path+"]");
										path = path.substring(0,path.lastIndexOf(File.separator));
										System.out.println("after Path ["+path+"]");
										File createFolder = new File(path);
										if(!createFolder.exists())
										{
											createFolder.mkdirs();
										}
										
										
										
									
							    		pstmtGetTrack.setString(1, cnumber);
							    		rsGetTrack = pstmtGetTrack.executeQuery();
							    		
							    		if( rsGetTrack.next() )
							    		{
							    			oldMsnNo = rsGetTrack.getLong("MSN_NO");
							    			oldCarrierSymbol = rsGetTrack.getString("CARRIER_SYMBOL");
							    			oldCarrierFriendlyName = rsGetTrack.getString("CARRIER_FRIENDLYNAME");
							    			
							    			if(oldMsnNo != 0 && oldMsnNo != msnNo)
							    			{
								    			StringReturn testReturn = soap.voidPackage(oldCarrierSymbol,oldMsnNo);
								    			response =  testReturn.getResponse();
								    			System.out.println("response delete not old msn as new msn["+response+"]");
								    			
												 if(!response.contains("No Error"))
												 {
													 //errString = itmDBAccessEJB.getErrorString(response,"NTVOIDPKGS","");
													 //errString =  getError(response, "NTVOIDPKGS", conn);
													 //return errString;
													 
												 }
							    			}
							    		}
							    		
							    		pstmtGetTrack.clearParameters();
										//UPDATE CARTON MASTER TRACKING NO , IMAGENAME PATH,
										//sql = "UPDATE CARTON_MASTER SET TRACKING_NO = ? WHERE CARTON_NO = ?";									
										
										pstmtUpdCarton.setString(1, trackNo);		
										pstmtUpdCarton.setLong(2, msnNo);		
										pstmtUpdCarton.setString(3, carrierSymbol);
										pstmtUpdCarton.setString(4, carrierFriendlyName);
										pstmtUpdCarton.setString(5, cnumber);
										int test = pstmtUpdCarton.executeUpdate();
										System.out.println("update statement is :"+test);
										if(test==1)
										{
											System.out.println("updated");						
										}
										else
										{
											System.out.println("not updated");
										}								
											
										//cartonNoarr.get(masterCartonNo);
										imgOutFile.createNewFile();
										ImageIO.write(bufImg, "png", imgOutFile);
										System.out.println("Write File Complete----");				
										
									}
									
									
									pstmtChg.setString(1, "Y");
									pstmtChg.setString(2, ptcn);		
									pstmtChg.setString(3, ptcn);		
									int test = pstmtChg.executeUpdate();
									if( test > 0)
									{
										System.out.println(" WAVE_STATUS_ORG table updated ");
									}
									else
									{				
										throw new Exception("WAVE_STATUS_ORG table table updated");					
									}
												
								
									pstmtCrtnChg.setString(1, "N");
									pstmtCrtnChg.setString(2, ptcn);
									
									test = pstmtCrtnChg.executeUpdate();
									
									if( test > 0)
									{
										System.out.println("Carton Master table updated ");
									}
									else
									{				
										throw new Exception("Carton Master  updated");					
									}
									
									HashMap sordMap = getSordInfo(saleOrder,conn);
									frtTerm = (String)sordMap.get("FRT_TERM");
				    				totalFrtAmt = objShipResult.getTotal();
				    				System.out.println("Update in desp update freight Amt totalFrtAmt ["+totalFrtAmt+"] For frtTerm["+frtTerm+"]saleOrder["+saleOrder+"]");
				    				
				    				if("B".equalsIgnoreCase(frtTerm))
				    				{
				    					sSQL = "UPDATE DESPATCH SET FREIGHT_AMT_ADD = ? WHERE PTCN = ?";
				    					pstmtDesp = conn.prepareStatement(sSQL);
				    					pstmtDesp.setDouble(1, totalFrtAmt);
				    					pstmtDesp.setString(2, ptcn);
				    					pUpd = pstmtDesp.executeUpdate();
				    					
				    					System.out.println("Update in desp update freight Amt upd ["+pUpd+"] For");    					
				    					
				    					if(pstmtDesp != null)
				    					{
				    						pstmtDesp.close();
				    						pstmtDesp = null;
				    						
				    						
				    					}
				    					
				    					sSQL = "UPDATE SHIP_DOCS SET FREIGHT_AMT_ADD = ? WHERE PTCN = ?";
				    					pstmtDesp = conn.prepareStatement(sSQL);
				    					pstmtDesp.setDouble(1, totalFrtAmt);
				    					pstmtDesp.setString(2, ptcn);	    					
				    					pUpd = pstmtDesp.executeUpdate();
				    						    					
				    					System.out.println("Update in Ship Docs update freight Amt upd ["+pUpd+"] For");
				    					
				    					if(pstmtDesp != null)
				    					{
				    						pstmtDesp.close();
				    						pstmtDesp = null;
				    						
				    					}
				    				}
							
					    	}//else if(!objReturn2.isSuccess())
			    		
						}
				
			}
			
			if(ptcnRS != null)
			{
				ptcnRS.close();
				ptcnRS = null;
			}
			
			if(ptcnPSTMT != null)
			{
				ptcnPSTMT.close();
				ptcnPSTMT = null;
			}
			
			if(pstmtUpdCarton != null)
			{
				pstmtUpdCarton.close();
				pstmtUpdCarton = null;
			}
			
			if(pstmtChg != null)
			{
				pstmtChg.close();
				pstmtChg = null;
			}
			
			if(rsPrintStatus != null)
			{
				rsPrintStatus.close();
				rsPrintStatus = null;
			}
			
			if(pstmtPrintStatus != null)
			{
				pstmtPrintStatus.close();
				pstmtPrintStatus = null;
			}
			
			if( rsMaster != null)
			{
				rsMaster.close(); 
				rsMaster = null;
			}
			
			if(pstmtCrtnChg != null)
			{
				pstmtCrtnChg.close(); 
				pstmtCrtnChg = null;
			}
			
			if(pstmtMaster != null)
			{
				pstmtMaster.close();
				pstmtMaster = null;
			}
			
			if(pstmtGetTrack != null)
			{
				pstmtGetTrack.close();
				pstmtGetTrack = null;
			}
			
			if(rsGetTrack != null)
			{
				rsGetTrack.close();
				rsGetTrack = null;
			}
			
			
			//uncommented by sankara on 03/04/14 for generate labels
			 errString =	generatedLabelPrint(waveId,glblPTCN, printer, conn);
			
	    	
			
			
					
		}//try end	
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch(Exception es)
			{
				es.printStackTrace();
				errString = es.getMessage();			
				throw new ITMException(es);
			}
			e.printStackTrace();
			System.out.println("Exception :GenerateLabels :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			errString = e.getMessage();			
			throw new ITMException(e);
		}
		finally
		{
			//System.out.println("Closing Connection....");
			try
			{				
	    		if(rs1 != null)
	    		{
	    			rs1.close(); rs1 = null;
	    		}
	    		if(pstmt1 != null)
	    		{
	    			pstmt1.close(); pstmt1 = null;
	    		}
	    		
	    		if( rs != null)
	    		{
	    			rs.close(); rs = null;
	    		}
	    		if( pstmt != null)
	    		{
	    			pstmt.close(); pstmt = null;
	    		}
	    		    		
	    		
	    		if(ptcnRS != null)
				{
					ptcnRS.close();
					ptcnRS = null;
				}
				
				if(ptcnPSTMT != null)
				{
					ptcnPSTMT.close();
					ptcnPSTMT = null;
				}
				
				if(pstmtUpdCarton != null)
				{
					pstmtUpdCarton.close();
					pstmtUpdCarton = null;
				}
				
				if(pstmtChg != null)
				{
					pstmtChg.close();
					pstmtChg = null;
				}
				
				if(rsPrintStatus != null)
				{
					rsPrintStatus.close();
					rsPrintStatus = null;
				}
				
				if(pstmtPrintStatus != null)
				{
					pstmtPrintStatus.close();
					pstmtPrintStatus = null;
				}
				
				if( rsMaster != null)
				{
					rsMaster.close(); 
					rsMaster = null;
				}
				
				if(pstmtCrtnChg != null)
				{
					pstmtCrtnChg.close(); 
					pstmtCrtnChg = null;
				}
				
				if(pstmtMaster != null)
				{
					pstmtMaster.close();
					pstmtMaster = null;
				}
	    		
	    		
				if (errString != null && errString.trim().length() > 0)
				{
					conn.rollback();
					System.out.println("errString ::::::::::::::::::::"+errString);
					return errString;
				}			
				else
				{
					conn.commit();				
				}
				
				if( conn != null )
	    		{
	    			conn.close(); conn = null;
	    		}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
				throw new ITMException(e);			
			}
			finally
			{
				if( errString.trim().length() == 0 )
				{
					errString="VTCOMPL";
					errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
				}
				//return errString;
			}
			
		}
		return errString;
	
	}
	
	private  String getError(String saleOrder,String Code,Connection conn)  throws ITMException, Exception
    {
        String mainStr ="";
        
        try
        {
        	String errString = "";
        	errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Invalid Data"+"</message><description>";
			mainStr= mainStr+"Sale Order ="+saleOrder+""+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new ITMException(e);
        }
        return mainStr;
    }
	
	
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}	
	
	public String isTranCodeChanged(String ptcn, Connection conn) throws ITMException
	{
		String sql = "";		
		String shipTranCode = "",cartonMasterTranCode = "";	
		String errString = "";
		String serviceCode = "";
		
		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,T.SERVICE_CODE FROM SHIPMENT S , SHIP_DOCS SD, TRANSPORTER T WHERE SD.PTCN = ? " +
					" AND S.SHIPMENT_ID = SD.SHIPMENT_ID AND S.TRAN_CODE = T.TRAN_CODE ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ptcn);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				shipTranCode = rs.getString("TRAN_CODE");
				serviceCode = rs.getString("SERVICE_CODE");
			}
			else
			{
				
				sql = "SELECT T.TRAN_CODE,T.SERVICE_CODE FROM DESPATCH D,WAVE_TASK_DET W,TRANSPORTER T "
				+" WHERE D.DESP_ID = W.REF_ID AND W.REF_SER='S-DSP'" 
				+" AND T.TRAN_CODE = D.TRAN_CODE AND W.PTCN = ?";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, ptcn);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					shipTranCode = rs.getString("TRAN_CODE");
					serviceCode = rs.getString("SERVICE_CODE");
				}
				else
				{
					return "NO";
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			if( cartonMasterTranCode.equalsIgnoreCase(shipTranCode))
			{
				return "NO";				
			}
			else
			{
				return serviceCode;
			}
			
		}
		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;
	}
	
	private CommodityInfo[] setCommodityInfos(LinkedHashMap linkMap,String masterCarton,String saleOrder,ADSIShippingSoap  soap,String cartonType ,Connection conn) throws ITMException
	{
		String itemCode = "";
		String harmonizDescr = "";
		String harmonizCode = "";
		String originCountry = "";
		String nafFlag = "";
		String shipCountry = "";
		
		double unitValue = 0,caseWeight = 0,itemWeight = 0;
		double packSizeCase = 0,noArt =0;
		CommodityInfo[] commoList;
		int cCtr = 0;
		try
		{
			
			System.out.println("GenerateLabelsPrc.setCommodityInfos()");
			System.out.println("list.size ["+linkMap.size()+"]");
			commoList = new CommodityInfo[linkMap.size()];
			nafFlag = discommon.getDisparams("999999","NAFTA_FLAG_COUNT",conn);
			CommodityInfo comInfo = new CommodityInfo();
			Iterator it = linkMap.keySet().iterator();
			
			//for( int i = 0 ; i < list.size() ; i++)
			while(it.hasNext())
			{
				itemCode = (String)it.next();
				comInfo = new CommodityInfo();
				System.out.println(" item_code ["+itemCode+"]");
				
				if(!"A".equalsIgnoreCase(cartonType))
				{
					HashMap itmVolumeMap = getItemVoumeMap(itemCode, "", conn);
					harmonizDescr = (String)itmVolumeMap.get("HARMONIZATION_DESCR");
					harmonizCode = (String)itmVolumeMap.get("HARMONIZATION_NO");
					caseWeight = (Double)itmVolumeMap.get("PACK_WEIGHT");
					originCountry = (String)itmVolumeMap.get("ORIGIN_COUNTRY");
					packSizeCase = (Double)itmVolumeMap.get("PACK_SIZE");
					noArt = getItemCount(masterCarton, itemCode, conn);
					comInfo.setQuantity(noArt * packSizeCase);
				}
				else
				{
					noArt = (Double)linkMap.get(itemCode);
					comInfo.setQuantity(noArt);
				}
				
				unitValue = getRateStdoum(saleOrder, itemCode, conn);				
				comInfo.setDescription(harmonizDescr);//As per taro Harmonization description
					
				
				//comInfo.setQuantity();
				comInfo.setHarmonizedCode(harmonizCode);//As per Taro Haromonization code
				comInfo.setUnitValue(unitValue);
				comInfo.setUnitWeight(caseWeight);
				Country originCountryMfg = soap.getCountry("ISO3", originCountry).getResponseCountry();
				comInfo.setOriginCountry(originCountryMfg);
    			//comInfo.setNaftaFlag(false);//False as Per Taro Need To change canda/Mexico as true.
				HashMap sordMap = getSordInfo(saleOrder,conn);
				shipCountry = sordMap.get("COUNT_CODE").toString();
				System.out.println(" shipCountry  ["+shipCountry+"]");
				if(nafFlag.indexOf(shipCountry) != -1)
				{
					comInfo.setNaftaFlag(true);
					System.out.println("available in disparm");
				}
				else
				{
					System.out.println("not in disparm");
					comInfo.setNaftaFlag(false);
				}
				
				Calendar calendar = Calendar.getInstance();
				comInfo.setExportNumberExpireDate(calendar);
				comInfo.setUnitOfMeasure(EnumUnitOfMeasure.uomPCS);
				commoList[cCtr] = comInfo;
				cCtr++;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		
		return commoList;
	}
	
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;		
		String sql="",hazardous = "";		
		String harmonizCode = "",harmonizDescr = "",originCountry = "";
		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,I.HAZARDOUS HAZARD, " 
				  +" I.HARMONIZATION_NO HARMONIZATION_NO,I.HARMONIZATION_DESCR HARMONIZATION_DESCR,I.COUNT_CODE__MFG ORIGIN_COUNTRY,"
				  +" 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");
				//Changed By Pragyan 12/03/13 To set hazardous item check
				hazardous = rs.getString("HAZARD");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");
				harmonizCode = rs.getString("HARMONIZATION_NO");
				harmonizDescr = rs.getString("HARMONIZATION_DESCR");
				originCountry = rs.getString("ORIGIN_COUNTRY");
			}
			
			//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);
			//Changed By Pragyan 12/03/13 To set hazardous item check
			dataVolumeMap.put("HAZARDOUS", hazardous);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			dataVolumeMap.put("HARMONIZATION_NO", harmonizCode);
			dataVolumeMap.put("HARMONIZATION_DESCR", harmonizDescr);
			dataVolumeMap.put("ORIGIN_COUNTRY", originCountry);
			dataVolumeMap.put("ITEM_LEN", itmLen);
			dataVolumeMap.put("ITEM_WID", itmWidth);
			dataVolumeMap.put("ITEM_HEIGHT", itmHeight);
			dataVolumeMap.put("LITEM_LEN", lotLen);
			dataVolumeMap.put("LITEM_WID", lotWidth);
			dataVolumeMap.put("LITEM_HEIGHT", lotHeight);
		
			
			
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
			
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
			
				}
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		
		return dataVolumeMap;
	}	
	private double getRateStdoum(String saleOrder,String itemCode,Connection conn) throws ITMException
	{
		 String sql = "";
		 double rateStdOum = 0.0;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 
		 try
		 {
			 
			 sql =   " SELECT RATE__STDUOM FROM SORDDET WHERE  SALE_ORDER = ? AND ITEM_CODE = ?";
					 
			 pstmt = conn.prepareStatement(sql);
			 pstmt.setString(1, saleOrder);
			 pstmt.setString(2, itemCode);
			 
			 
			 rs = pstmt.executeQuery();
			 if( rs.next())
			 {
				 rateStdOum = rs.getDouble(1);
				
			 }
			 rs.close(); rs = null;
			 pstmt.close(); pstmt = null;	 
			 
		 }
		 catch(Exception ex)
		 {
			 ex.printStackTrace();
			 throw new ITMException(ex);
		 }
		 return rateStdOum;
	 }
	
	private double getItemCount(String masterCarton,String itemCode, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double count = 0;
		
		try
		{
			sql = "SELECT COUNT(*) FROM CARTON_MASTER WHERE MASTER_CARTON = ? AND CARTON_TYPE = ? AND ITEM_CODE = ? AND STATUS <> ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, masterCarton);
			pstmt.setString(2, "C");
			pstmt.setString(3, itemCode);
			pstmt.setString(4, "R");
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				count = rs.getDouble(1);
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			System.out.println(" item count ["+count+"]");
		}
		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;
				}
			}
			catch(Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		
		return count;
	}
	
	private HashMap getSordInfo(String saleOrder,Connection conn) throws ITMException
	{
		String sql = "";
		ResultSet rs = null;

		PreparedStatement pstmt = null;
		HashMap sordInfoMap = new HashMap();
		String custCode = "",stanCode = "",siteCodeShip = "",stateCodeDlv = "",shipConsGrp = ""
		//Changed By Rekha to get additional fields for ADSI
		,custName="",custShName = "",shipCity="",shipCountry = "",shipAdd1 = "",shipAdd2 = "",shipAdd3 = "",
		shipPh="",shipFax ="",shipMailId = "",custPord = "",frtTerm="";
		
		try
		{
									
			//Change By Rekha To get the required rcpnt value from customer
			//Changed By Pragyan To get customer ship consigee group
			/*sql = "SELECT C.CUST_CODE AS CUST_CODE,C.STAN_CODE AS STAN_CODE,S.SITE_CODE__SHIP AS SHIP_CODE,C.STATE_CODE AS STATE_DLV  FROM SORDER S,CUSTOMER C "
				+ " WHERE S.CUST_CODE__DLV = C.CUST_CODE "
				+ " AND S.SALE_ORDER = ? ";*/
			/*sql = "SELECT C.CUST_CODE AS CUST_CODE,C.STAN_CODE AS STAN_CODE,S.SITE_CODE__SHIP AS SHIP_CODE,C.STATE_CODE AS STATE_DLV,C.SHIP_CONS_GRP AS  SHIP_CONS_GRP FROM SORDER S,CUSTOMER C "
				+ " WHERE S.CUST_CODE__DLV = C.CUST_CODE "
				+ " AND S.SALE_ORDER = ? ";*/
			sql = "SELECT C.CUST_CODE AS CUST_CODE," 
				+" C.STAN_CODE AS STAN_CODE," 
				+" S.SITE_CODE__SHIP AS SHIP_CODE," 
				+" S.CUST_PORD AS CUST_PORD," 
				+" C.STATE_CODE AS STATE_DLV," 
				+" C.SHIP_CONS_GRP AS  SHIP_CONS_GRP," 
				+" C.CUST_NAME AS  CUST_NAME,"
				+" C.SH_NAME AS  SH_NAME,"
				+" C.ADDR1 AS  ADDR1,"
				+" C.ADDR2 AS  ADDR2,"
				+" C.ADDR3 AS  ADDR3,"
				+" C.CITY AS  CITY,"
				+" C.COUNT_CODE AS  COUNT_CODE,"
				+" C.TELE1 AS  PHONE,"
				+" C.FAX AS  FAX,"
				+" C.EMAIL_ADDR AS  EMAIL_ID,"
				//changed by Pragyan on 23/12/13 for getting FRT TERM.
				+" S.FRT_TERM AS  FRT_TERM"
				+" FROM SORDER S,CUSTOMER C "
				+ " WHERE S.CUST_CODE__DLV = C.CUST_CODE "
				+ " AND S.SALE_ORDER = ? ";
	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,saleOrder);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				custCode = rs.getString("CUST_CODE");
				stanCode = rs.getString("STAN_CODE");
				siteCodeShip = rs.getString("SHIP_CODE");
				stateCodeDlv = rs.getString("STATE_DLV");
				shipConsGrp = rs.getString("SHIP_CONS_GRP");
				custName = rs.getString("CUST_NAME");
				custShName = rs.getString("SH_NAME");
				shipCity = rs.getString("CITY");
				shipAdd1 = rs.getString("ADDR1");
				shipAdd2 = rs.getString("ADDR2");
				shipAdd3 = rs.getString("ADDR3");
				shipCountry = rs.getString("COUNT_CODE");
				shipPh = rs.getString("PHONE");
				shipMailId = rs.getString("EMAIL_ID");
				shipFax = rs.getString("FAX");
				custPord = rs.getString("CUST_PORD");
				frtTerm = rs.getString("FRT_TERM");
			}
			
			sordInfoMap.put("CUST_CODE", custCode);
			sordInfoMap.put("STAN_CODE", stanCode);
			sordInfoMap.put("SHIP_CODE", siteCodeShip);
			sordInfoMap.put("STATE_DLV", stateCodeDlv);
			sordInfoMap.put("CUST_NAME", custName);
			sordInfoMap.put("SH_NAME", custShName);
			sordInfoMap.put("CITY", shipCity);
			sordInfoMap.put("ADDR1", shipAdd1);
			sordInfoMap.put("ADDR2", shipAdd2);
			sordInfoMap.put("ADDR3", shipAdd3);
			sordInfoMap.put("COUNT_CODE", shipCountry);
			sordInfoMap.put("PHONE", shipPh);
			sordInfoMap.put("EMAIL_ID", shipMailId);
			sordInfoMap.put("FAX", shipFax);
			sordInfoMap.put("CUST_PORD", custPord);
			
			
			if(shipConsGrp != null && shipConsGrp.length() > 0 )
			{
				sordInfoMap.put("SHIP_CONS_GRP", shipConsGrp);
			}
			else
			{
				sordInfoMap.put("SHIP_CONS_GRP", custCode);
			}
			sordInfoMap.put("FRT_TERM", frtTerm);
			
			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
		
		}
		catch(Exception e)
		{
			System.out.println("Exception in  == >"+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e1)
			{
				throw new ITMException(e1);
			}
		}
	 return sordInfoMap;	
	}
	
	private String generatedLabelPrint(String waveId,String ptcn, String printer, Connection conn) throws ITMException
	{
		
			String errString ="";
	    	String printMode = "";
	    	String outputFilename = "";
	    	TreeMap argMap = null;
	    	String isReplenishment = "N";
	    	String saleOrder = "";
	    	PreparedStatement pstmt = null;
	    	ResultSet rs = null;
	    	
	    	try
	    	{    		
	    		
				String reportObjName = discommon.getDisparams("999999","WAVE_LABEL_OBJ_NAME",conn);			
				System.out.println("Printer *->["+printer.trim()+"]  report_obj_name ["+reportObjName+"] wave id ->["+waveId+"]");		
				ibase.webitm.reports.utility.JasperReportGenerator jrGenerator = new ibase.webitm.reports.utility.JasperReportGenerator();
				String xsdString ="<Arguments>" +
							"<Argument id=\"WAVE_ID\" name=\"WAVE_ID\">" +
							"<argType>java.lang.String</argType>" +								
							"<value>"+waveId+"</value>" +
							"</Argument>";
				
				if(ptcn != null && ptcn.length() > 0)
				{
					xsdString = xsdString +
							"<Argument id=\"PTCN\" name=\"PTCN\">" +
							"<argType>java.lang.String</argType>" +								
							"<value>"+ptcn+"</value>" +
							"</Argument>";
				}
							
				xsdString = xsdString +  
							"<Argument id=\"PRINTVAL\" name=\"PRINTVAL\">" +
							"<argType>java.lang.String</argType>" +
							//"<value>"+ptcnList.get(i)+"</value>" +
							"<value>"+isReplenishment+"</value>" +
							"</Argument>" +
							"<Argument id=\"x_path\" name=\"x_path\">" +
							"<argType>java.lang.String</argType>" +
							"<value>/DocumentRoot/pick_label</value>" +
							"</Argument>" +
							"<Argument id=\"design_source\" name=\"design_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>pick_label.jrxml</value>" +
							"</Argument>" +
							"<Argument id=\"data_source\" name=\"data_source\">" +
							"<argType>java.lang.String</argType>" +
							"<value>SQL</value>" +
							"</Argument>" +
							"<Argument id=\"bind_type\" name=\"bind_type\">" +
							"<argType>java.lang.Integer</argType>" +
							"<value>0</value>" +
							"</Argument>" +
							"<Argument id=\"report_type\" name=\"report_type\">" +
							"<argType>java.lang.String</argType>" +
							"<value>JASPER</value>" +
							"</Argument>" +
							"<Argument id=\"report_save_type\" name=\"report_save_type\">" +
							"<argType>java.lang.String</argType>" +									
							"<value>NATIVE</value>" +									
							"</Argument>" +
							"</Arguments>";
				System.out.println(" XSD parser **->["+xsdString+"]");									
				errString = jrGenerator.printReport(reportObjName, xsdString, chgUser, printer.trim(), 1, conn);			
				System.out.println(" PRINTING task completed--------**------->");		
					
				//}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return errString;
	}
	
}


