/**
 DEVELOPED BY  : RITESH ON 10/SEP/2014
 PURPOSE : STOCK TRANSEFR FROM WIP LOCATION THROUGH WIZARD (REG : M14ESUN002)
 */

package ibase.webitm.ejb.mfg;

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

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.sql.Timestamp;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless;

@Stateless 
public class  WoTrfWizIC extends ValidatorEJB implements WoTrfWizICLocal,  WoTrfWizICRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();	

	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			System.out.println("Val xmlString :: " + xmlString );
			System.out.println("Val xmlString1 :: " + xmlString1 );
			System.out.println("Val xmlString2 :: " + xmlString2 );
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);

			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			throw new ITMException( e );
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr=0;
		int count = 0;
		String childNodeName = null;
		String errString = "";
		Connection conn = null;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = "" ;
		String userId = "";
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();
		String columnValue ="";
		String campNo = "";
		String locCodeTo = "";
		
		//  added by cpatil end 06-sept-12

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
			case 1:
			{
//				dts=java.sql.Timestamp.valueOf(sdf.format(new java.util.Date()).toString() + " 00:00:00.0");
				
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
	
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if( childNodeName.equalsIgnoreCase( "campaign_no" ))
						{
							System.out.println("campaign_no ");
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("campaign_no","VTCAMPNO2",userId);
								break;								
							}
							else
							{
								campNo = genericUtility.getColumnValue("campaign_no", dom);
								sql = "SELECT COUNT(*) FROM WORKORDER WHERE CAMPGN_NO  = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, campNo);
								rs = pstmt.executeQuery();							
								if(rs.next())
								{  
									count = rs.getInt(1);
								}
								pstmt.close();
								pstmt = null;
								rs.close();
								rs = null;
		
								if(count == 0)
								{
									errString = getErrorString("campaign_no","VTCAMPNO1",userId);
									break;								
								}
							}
						}
					}
			}
			break;
			case 2 :
			{	
				System.out.println("case2 wfvalData()::");
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					System.out.println("case2 wfvalData()::inside for ::"+ctr);
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();

					if ( childNode != null && childNode.getFirstChild() != null )
					{
						columnValue = childNode.getFirstChild().getNodeValue();
					}
					System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
					if( childNodeName.equalsIgnoreCase( "item_ser" ))
					{ 
						String itemSer = genericUtility.getColumnValue("item_ser", dom);
						System.out.println("childNodeName.equalsIgnoreCase( item_ser )::"+itemSer);
						if(childNode.getFirstChild() == null)
						{
							errString = getErrorString("item_ser","VMITMSER1",userId);
							break;								
						}
						else
						{	
							sql = "SELECT COUNT(*) AS COUNT FROM ITEMSER WHERE ITEM_SER = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,columnValue);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								count = rs.getInt("COUNT");
							}
							if(pstmt != null)
							{
								pstmt.close();
								pstmt = null;									 
							}

							if(rs != null)
							{
								rs.close();
								rs = null;
							}
							if( count == 0 )
							{
								errString = getErrorString("item_ser","VMITMSER2",userId);
								break;
							}

						}
					}
					if( childNodeName.equalsIgnoreCase( "ref_ser__for" ))
					{ 
						String refSerFor = genericUtility.getColumnValue("ref_ser__for", dom);
						System.out.println("childNodeName.equalsIgnoreCase( item_ser )::"+refSerFor);
						if(childNode.getFirstChild() == null)
						{
							errString = getErrorString("ref_ser__for","VMREFSER1",userId);
							break;								
						}
						else
						{	
							
							sql = "SELECT COUNT(*) AS COUNT FROM refser WHERE ref_ser = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,refSerFor);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								count = rs.getInt("COUNT");
							}
							if(pstmt != null)
							{
								pstmt.close();
								pstmt = null;									 
							}

							if(rs != null)
							{
								rs.close();
								rs = null;
							}
							if( count == 0 )
							{
								errString = getErrorString("item_ser","VMREFSER2",userId);
								break;
							}

						}
					}
				}
			}
			break;
			case 3:
			{
				parentNodeList = dom.getElementsByTagName("Detail3");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					System.out.println("Enter into detail3 validation");
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName ::"+childNodeName);

					if( childNodeName.equalsIgnoreCase( "loc_code__to" ))
					{
						System.out.println("loc_code__to["+childNode.getNodeValue()+"]");
						if(childNode.getFirstChild() == null)
						{
							errString = getErrorString("loc_code__to","VTLOCTO1",userId);
							break;								
						}
						else
						{
//							locCodeTo = genericUtility.getColumnValue("loc_code__to", dom1);
							locCodeTo = genericUtility.getColumnValue("loc_code__to", dom);

//							locCodeTo= ((genericUtility.getColumnValueFromNode("loc_code__to",dom.getElementsByTagName("Detail3").item(ctr))).trim());

							sql = "SELECT COUNT(*) FROM location WHERE LOC_CODE = ?  "; 
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, locCodeTo);
							rs = pstmt.executeQuery();							
							if(rs.next())
							{  
								count = rs.getInt(1);
							}
							pstmt.close();
							pstmt = null;
							rs.close();
							rs = null;

							if(count == 0)
							{
								errString = getErrorString("loc_code__to","VTLOCTO2",userId);
								break;								
							}
						}
					}

					
				}
			}
			} 
		}
		catch(Exception e)
		{
			e.printStackTrace();			
			errString=e.getMessage();			
			throw new ITMException( e );
		}
		finally
		{
			try
			{

				if(conn!=null)
				{
					if(rs != null )rs.close();
					rs = null;
					if(pstmt != null )pstmt.close();
					pstmt =null;
					conn.close();
					conn = null;
				}
				conn = null;
			}catch(Exception d)
			{
				d.printStackTrace();
				throw new ITMException( d );
			}
		}

		return errString;
	}

	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}

	public String itemChanged(String xmlString, String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("itemChanged() called @@@@@@@@@@@@");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			System.out.println("xmlString---"+xmlString);
			System.out.println("xmlString1---"+xmlString1);
			System.out.println("xmlString2---"+xmlString2);
			valueXmlString = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
		}
		catch(Exception e)
		{
			System.out.println("Exception : [TrainingEJB][itemChanged(String,String)] :==>\n"+e.getMessage());
			throw new ITMException( e );
		}
		return valueXmlString;
	}

	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("itemChanged() called @@@@@@@@@@@@ 2- 110914");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null,pstmt1=null;;
		ResultSet rs = null,rs1 = null ;
		String  sSQL = "";
		String sql = "";
		int currentFormNo = 0 ;
		int lineNo = 0 ;
		String siteCode = "";
		String loginCode = "";
		String chgTerm = "";
		String siteDescr = "";
		int cnt = 0;
		String empCode ="", loginSiteCode = "";
		Timestamp currDateNew = null;
//		java.util.Date currDate = null;
		String currDateStr = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		java.util.Date utilDate = new java.util.Date();
		String remarks="",reasCode="",explev="",campaignNo = "";
		double issQty=0.0,integralQty=0.0,potencyadj=0.0;
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			String applDateFormat = genericUtility.getApplDateFormat();

			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}

			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
		

			switch(currentFormNo)
			{
			case 2 :
			{	
				siteCode=checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
				System.out.println("siteCode---"+siteCode);
				//siteCode =(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"));
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
					SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
					currDateNew =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
//					currDate = new java.util.Date();
					sdf = new SimpleDateFormat(applDateFormat);
					currDateStr = sdf.format(currDateNew);
					loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
					System.out.println("********* login code ["+loginCode+"]");
					sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ?";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1, loginCode);						
					rs = pstmt.executeQuery();	
					if( rs.next() )
					{
						empCode = rs.getString("EMP_CODE");
					}
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}	
					valueXmlString.append("<Detail2 domID='1' selected=\"N\">\r\n");
//					valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\" />\r\n");

					chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
					siteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));

					sql = "SELECT DESCR FROM SITE WHERE SITE_CODE = ?";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString( 1, siteCode );						
					rs = pstmt.executeQuery();	
					if( rs.next() )
					{
						siteDescr = rs.getString("DESCR");
					}
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}	
					valueXmlString.append("<tran_date protect = '1'><![CDATA[" ).append( currDateStr ).append( "]]></tran_date>\r\n" );
//					valueXmlString.append("<site_code protect = '1'>").append("<![CDATA["+"PL001"+"]]>").append("</site_code>");
					valueXmlString.append("<site_code protect = '1'>").append("<![CDATA["+siteCode+"]]>").append("</site_code>");
					valueXmlString.append("<site_descr><![CDATA[" ).append( checkNull( siteDescr )).append( "]]></site_descr>\r\n" );
					valueXmlString.append("<item_ser><![CDATA[" ).append( "NT" ).append( "]]></item_ser>\r\n" );
					valueXmlString.append("<ref_ser__for><![CDATA[" ).append( "XFRX" ).append( "]]></ref_ser__for>\r\n" );
//					valueXmlString.append("<emp_codpstmt != nulle__aprv protect = '1'><![CDATA["+ empCode +"]]></emp_code__aprv>");
				}
				valueXmlString.append("</Detail2>");
			}	
			break;
			case 3 :
			{	  
				loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
				loginSiteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
				System.out.println("loginCode --->["+loginCode+"]");
//				campaignNo = genericUtility.getColumnValue("campaign_no", dom);
//				System.out.println(" campaignNo in Detail --->["+campaignNo+"]");
				campaignNo = genericUtility.getColumnValue("campaign_no", dom1);
				System.out.println(" campaignNo in Detail1 --->["+campaignNo+"]");
				DecimalFormat df = new DecimalFormat("0.000");
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{	
					System.out.println("siteCode set in case 3---"+genericUtility.getColumnValueFromNode("site_code", dom2.getElementsByTagName("Detail2").item(0)));
					System.out.println("emp_code__user set in case 3---"+genericUtility.getColumnValueFromNode("emp_code__user", dom2.getElementsByTagName("Detail2").item(0)));
					System.out.println(genericUtility.getColumnValue("loc_code", dom1));
					sql = " select item_code,lot_no,lot_sl,loc_code,sum(quantity) as quantity  "
				    		+" from inv_alloc_det  where  (case when alloc_qty is null then 0 else alloc_qty end - case when dealloc_qty is null then 0 else dealloc_qty end) > 0 and  tran_id IN (SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  IN (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? AND STATUS = 'R')) "
				    		+" GROUP BY  item_code, lot_no, lot_sl, loc_code ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,campaignNo);

					System.out.println("** SQL **[" + sql + "]");
					rs = pstmt.executeQuery();
					while (rs.next()) 
					{
							lineNo++;
							issQty = rs.getDouble("quantity");
							sSQL = " select site_code,remarks,reas_code,potency_adj,line_no,exp_lev  " +
									" from inv_alloc_det where  tran_id in ( SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  IN (SELECT WORK_ORDER FROM WORKORDER WHERE CAMPGN_NO = ? )) and item_code = ? and lot_no = ? and lot_sl = ? and loc_code = ?  ";
							pstmt1 = conn.prepareStatement(sSQL);
							pstmt1.setString(1, campaignNo);
							pstmt1.setString(2,  rs.getString("item_code"));
							pstmt1.setString(3,  rs.getString("lot_no"));
							pstmt1.setString(4,  rs.getString("lot_sl"));
							pstmt1.setString(5,  rs.getString("loc_code"));
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								siteCode = rs1.getString("site_code");
								remarks = rs1.getString("remarks");
								reasCode = rs1.getString("reas_code");
								potencyadj = rs1.getDouble("potency_adj");
//								lineno = rs1.getInt("line_no");
								explev = rs1.getString("exp_lev");
							}
							if(rs1 != null)
								rs1.close();
							rs1 = null;
							if(pstmt1 != null)
								pstmt1.close();
							pstmt1 = null;
							
							sql = "select INTEGRAL_QTY from item where item_code = ?";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,  rs.getString("item_code"));
							rs1 = pstmt1.executeQuery();
							if ( rs1.next() )
							{
								integralQty = rs1.getDouble(1);
							}
							if(rs1 != null)
								rs1.close();
							rs1 = null;
							if(pstmt1 != null)
								pstmt1.close();
							pstmt1 = null;
							
							valueXmlString.append("<Detail3 domID='" + lineNo+ "' selected=\"N\">\r\n");
								
//							valueXmlString.append("<Detail3 domID='" + lineNo+ "' selected=\"Y\">\r\n");
							
							valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
//							valueXmlString.append("<tran_id/>"); 
							valueXmlString.append("<site_code><![CDATA[").append(checkNullAndTrim(siteCode)).append("]]></site_code>\r\n");
							valueXmlString.append("<item_code><![CDATA[").append(checkNullAndTrim(rs.getString("item_code"))).append("]]></item_code>\r\n");
							valueXmlString.append("<loc_code><![CDATA[").append(checkNullAndTrim( rs.getString("loc_code"))).append("]]></loc_code>\r\n");
							valueXmlString.append("<lot_no><![CDATA[").append(checkNullAndTrim(rs.getString("lot_no"))).append("]]></lot_no>\r\n");
							valueXmlString.append("<lot_sl><![CDATA[").append(checkNullAndTrim(rs.getString("lot_sl"))).append("]]></lot_sl>\r\n");
							valueXmlString.append("<allocated_qty><![CDATA[").append(0.0).append("]]></allocated_qty>\r\n");														
							if(integralQty > 0)
							{
								if(issQty > integralQty ){
									if(issQty % integralQty != 0 && integralQty != 0)
									{
										valueXmlString.append("<quantity>").append("<![CDATA[" + df.format((integralQty - (issQty % integralQty) + issQty)) +"]]>").append("</quantity>");
									}
									else
									{
										valueXmlString.append("<quantity>").append("<![CDATA[" + df.format(issQty) +"]]>").append("</quantity>");
									}
								}else{
									valueXmlString.append("<quantity>").append("<![CDATA[" + df.format(integralQty) +"]]>").append("</quantity>");
								}
							}
							else if(integralQty == 0)
							{
								valueXmlString.append("<quantity>").append("<![CDATA[" + df.format(issQty) +"]]>").append("</quantity>");

							}
							
							valueXmlString.append("<actual_qty><![CDATA[").append(df.format(issQty)).append("]]></actual_qty>\r\n");
							valueXmlString.append("<exp_lev><![CDATA[").append(explev).append("]]></exp_lev>\r\n");
							valueXmlString.append("<potency_adj><![CDATA[").append(potencyadj).append("]]></potency_adj>\r\n");
							valueXmlString.append("</Detail3>\r\n");
						}
						if (pstmt != null) 
						{
							pstmt.close();
							pstmt = null;
						}

						if (rs != null) 
						{
							rs.close();
							rs = null;
						}

						System.out.println("Detail 3 String ["+valueXmlString.toString()+"]");
						System.out.println("\n$$$$$$$$$\n");
				}
			}
			break;
			} 
			valueXmlString.append("</Root>");
			System.out.println("=================["+valueXmlString+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}	
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}	
					if(conn != null)
					{	
						conn.close();
						conn = null;
					}	
				}

			}
			catch(Exception d)
			{
				d.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}

	private String checkNullAndTrim( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}
	private String checkNull( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		return inputVal;
	}

	private double quanitityPerPallet(String itemCode,String lotNo, double pallet ,Connection conn)

	{

		int findNosOfPallet=0;

		int nosOfPalletPerVolume=0;

		int nosOfPalletPerWeight=0;

		int perPalletArticle=0;



		String sql="";

		PreparedStatement pstmt = null;

		ResultSet rs =null;

		double shipperSize =0.0;

		double grossWeight =0.0;

		double netWeight =0.0;

		double length=0.0;

		double width=0.0;

		double height=0.0;

		Double nosOfPallet=0.0;

		double quantity = 0.0;

		DistCommon distComm = new DistCommon();



		sql= "SELECT SHIPPER_SIZE, GROSS_WEIGHT, NET_WEIGHT, LENGTH, WIDTH ,HEIGHT FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ? " +

				" AND ? BETWEEN LOT_NO__FROM AND LOT_NO__TO "; 

		try {

			pstmt =conn.prepareStatement(sql);

			pstmt.setString(1,itemCode);

			pstmt.setString(2,lotNo);

			rs=pstmt.executeQuery();

			if(rs.next())

			{

				shipperSize=rs.getDouble(1);

				grossWeight =rs.getDouble(2);

				netWeight=rs.getDouble(3);

				length=rs.getDouble(4);

				width=rs.getDouble(5);

				height=rs.getDouble(6);

			}

			rs.close();

			pstmt.close();

			rs=null;

			pstmt =null;

			double perPallateVolume = Double.parseDouble(distComm.getDisparams("999999","PER_PALLET__VOLUME",conn));

			System.out.println("shipperSize ["+shipperSize+"] grossWeight ["+grossWeight+"] netWeight ["+netWeight+"] length ["+length+"] width ["+width+"] height ["+height+"]");

			//nosOfPallet=(quantity*length*width*height/shipperSize)/(Double.parseDouble(distComm.getDisparams("999999","PER_PALLET__VOLUME",conn)));

			double newValue = Math.floor((pallet * perPallateVolume)/(length*width*height));

			System.out.println(" before Math.floor -->["+newValue+"]");

			quantity = newValue * shipperSize;			

			System.out.println("<!@#>QUANTITY  " +quantity);

			/*//nosOfPalletPerVolume=(new Double((Math.round(nosOfPallet)))).intValue();

			System.out.println("<!@#>nosOfPalletPerVolume" +nosOfPalletPerVolume);*/						

			//System.out.println("<!@#>findNosOfPallet" +findNosOfPallet);



		} catch (SQLException e) {



			e.printStackTrace();

		}		

		return quantity; 

	}
	public  long countDaysBetween(java.sql.Date startdate, java.util.Date enddate)
	{
			   
			    //reset all hours mins and secs to zero on start date
			    Calendar startCal = GregorianCalendar.getInstance();
			    startCal.setTime(startdate);
			    startCal.set(Calendar.HOUR_OF_DAY, 0);
			    startCal.set(Calendar.MINUTE, 0);
			    startCal.set(Calendar.SECOND, 0);
			    long startTime = startCal.getTimeInMillis();

			    //reset all hours mins and secs to zero on end date
			    Calendar endCal = GregorianCalendar.getInstance();
			    endCal.setTime(enddate);
			    endCal.set(Calendar.HOUR_OF_DAY, 0);
			    endCal.set(Calendar.MINUTE, 0);
			    endCal.set(Calendar.SECOND, 0);
			    long endTime = endCal.getTimeInMillis();

			    return (endTime - startTime)/(1000 * 60 * 60 * 24) ;
	}
	private String generateTranId( String windowName, Connection conn )throws
	ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";

		java.sql.Date effDate = null;

		try
		{
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE	TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE",
					CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e){}
		}
		return tranId;
	}//generateTranTd()




}

