/*
 * PhyScanVerifyIC.java
 * Developer - Mahesh Patidar
 * Date - 11/08/12 
 */

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
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 PhyScanVerifyIC extends ValidatorEJB implements PhyScanVerifyICLocal, PhyScanVerifyICRemote
{
	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 , rs1 = null;
		String sql = "" ;
		Timestamp date1 = null;
		String userId = "";
		int currentFormNo=0;
		int childNodeListLength;
		double quantity = 0;
		double qty = 0;
		int cnt = 0;
        ConnDriver connDriver = new ConnDriver();
		
		String columnValue ="", sSQL = "",  itemCode = "", lotNo = "", lotSl ="";
		String locCode = "";
		String siteCode = "",invStat = "";
		
		java.util.Date utilDate = new java.util.Date();
		java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		PreparedStatement pstmt1= null;
		ResultSet rs2 = null;
		double shipperSize = 0,quantityInput = 0,volumn = 0, convFact = 1, meffQty = 0;
		DistCommon discommon = new DistCommon();
		String subCond = "";
		String scanVal = "",phyHanBasis = "", unit = "", mrfPartNo ="", toUnit = "" ,sqlForCase = "";

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
				{	
					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( "tran_date" ))
						{
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("tran_date","NULLTRANDT",userId);
								break;								
							}
							else
							{
								if(genericUtility.getColumnValue("tran_date", dom) != null)
								{
									date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom1) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
									sql = "SELECT count(*) FROM PHYSCAN WHERE TRAN_DATE = ? ";
							    	pstmt = conn.prepareStatement( sql );
									pstmt.setTimestamp(1, date1);						
									rs = pstmt.executeQuery();	
									if( rs.next() )
									{
										cnt = rs.getInt(1);
									}
									if(rs != null)
									{	
										rs.close();
										rs = null;
									}
									if(pstmt != null)
									{	
										pstmt.close();
										pstmt = null;
									}
									if(cnt == 0)
									{
										errString = getErrorString("tran_date","INVDT1",userId);
										break;						
									}
								}
							}
						}
					}
				}
			    break;
				case 2 :
				{	
					parentNodeList = dom.getElementsByTagName("Detail2");
					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( "site_code" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("site_code","VMSITSUPP",userId);
								break;								
							}
							else
							{	
								 sql = "SELECT COUNT(*) AS COUNT FROM SITE WHERE SITE_CODE = ?";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,genericUtility.getColumnValue("site_code",dom));
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									count = rs.getInt("COUNT");
								 }
								 if( count == 0 )
								 {
									 errString = getErrorString("site_code","INVSITECOD",userId);
									 break;
								 }
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }
							 }
						}
						else if( childNodeName.equalsIgnoreCase( "tran_date" ))
						{
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("tran_date","NULLTRANDT",userId);
								break;								
							}
							else
							{
								if(genericUtility.getColumnValue("tran_date", dom) != null)
								{
									date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom1) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
									sql = "SELECT count(*) FROM PHYSCAN WHERE TRAN_DATE = ? ";
							    	pstmt = conn.prepareStatement( sql );
									pstmt.setTimestamp(1, date1);						
									rs = pstmt.executeQuery();	
									if( rs.next() )
									{
										cnt = rs.getInt(1);
									}
									if(rs != null)
									{	
										rs.close();
										rs = null;
									}
									if(pstmt != null)
									{	
										pstmt.close();
										pstmt = null;
									}
									if(cnt == 0)
									{
										errString = getErrorString("tran_date","INVDT1",userId);
										break;						
									}
								}
							}
						}
						/*
						else if( childNodeName.equalsIgnoreCase( "phy_input" )) //added by Kunal on 07/09/12
						{
							//CommentedBy Manoj dtd 04092012 not required

//							if(childNode.getFirstChild() == null)
//
//							{
//
//								errString = getErrorString("phy_input","NULLCASECT",userId);
//
//								break;								
//
//							}
//
//							else
//
//							{
								String phyInput=checkNull(genericUtility.getColumnValue("phy_input",dom).trim());
								if(phyInput==null || phyInput.length()==0)
								{
									phyInput="0";
								}

								if( !isInteger(phyInput))

								{

									errString = getErrorString("case_count","NOTANUMBER",userId);

									break;

								}

								else

								{

									quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom));

									quantityInput = Double.parseDouble(genericUtility.getColumnValue("phy_input", dom));

									itemCode = genericUtility.getColumnValue("item_code",dom).trim();

									lotNo = genericUtility.getColumnValue("lot_no",dom).trim();

									lotSl = genericUtility.getColumnValue("lot_sl",dom).trim();

									System.out.println("quantity ["+quantity+"] quantityInput ["+quantityInput+"] itemCode ["+itemCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");

									sSQL = "SELECT L.PHY_HAN_BASIS, L.PHY_HAN_UOM , D.UNIT FROM PHYSCAN H,PHYSCANDET D,ITEM M,LOCATION L "

										   +" WHERE H.TRAN_DATE = ? AND  " 

										   +" D.ITEM_CODE = M.ITEM_CODE   "	+subCond								   

									       +" AND D.LOC_CODE = L.LOC_CODE  AND L.INV_STAT = ? "

									       +" AND D.LOT_NO = ? AND D.ITEM_CODE = ? AND D.LOT_SL = ?  ";
									

									System.out.println("** sSQL **["+sSQL+"]");

									pstmt = conn.prepareStatement(sSQL);

									pstmt.setDate(1, sqlDate);								

									pstmt.setString(2, genericUtility.getColumnValue("inv_stat",dom1));	

									pstmt.setString(3, lotNo);

									pstmt.setString(4, itemCode);

									pstmt.setString(5, lotSl);

									rs = pstmt.executeQuery();
									if(rs.next())

									{

										phyHanBasis = rs.getString("PHY_HAN_BASIS");

										unit = checkNull( rs.getString("PHY_HAN_UOM")); //change done by Kunal on 07/09/12

										toUnit = checkNull( rs.getString("UNIT")); //change done by Kunal on 07/09/12

										System.out.println(" Physical handel basis is ["+phyHanBasis+"]");

										phyHanBasis = checkNull(phyHanBasis);

										

										sqlForCase =" SELECT ILP.SHIPPER_SIZE, I.UNIT, I.MFR_PART_NO, I.VOLUME FROM ITEM I,  ITEM_LOT_PACKSIZE ILP " +

													" WHERE ILP.ITEM_CODE = ? " +

													" AND I.ITEM_CODE = ILP.ITEM_CODE "+

													" AND ? BETWEEN LOT_NO__FROM AND LOT_NO__TO ";

										

										pstmt1 = conn.prepareStatement(sqlForCase);

										pstmt1.setString(1, itemCode);

										pstmt1.setString(2, lotNo);

										rs2 = pstmt1.executeQuery();

										if( rs2.next())

										{

											shipperSize = rs2.getInt("SHIPPER_SIZE");

											//unit = rs2.getString("UNIT");

											mrfPartNo = rs2.getString("MFR_PART_NO");

											volumn = rs2.getDouble("VOLUME");

										}

										rs2.close();rs2 = null;

										pstmt1.close();pstmt1 = null;

										if( "".equalsIgnoreCase(phyHanBasis))

										{

											errString = getErrorString("phy_input","SCANTYPE",userId);

											break;

										}

										if("L".equalsIgnoreCase(phyHanBasis))

										{

											if( (shipperSize * quantityInput) != quantity)

											{

												//System.out.println(" as per 'L' ");

												errString = getErrorString("phy_input","NOTMATCH",userId);

												break;

											}

										}

										else if("I".equalsIgnoreCase(phyHanBasis))

										{

											if( quantityInput != quantity)

											{

												//System.out.println(" as per 'I' ");

												errString = getErrorString("phy_input","NOTMATCH",userId);

												break;

											}

										}

										else if ("U".equalsIgnoreCase(phyHanBasis))

										{

											System.out.println(" as per 'U' ");

																						

											if(!unit.equalsIgnoreCase(toUnit))

											{

												if(volumn != 0)

												{

													convFact = volumn;

												}

												ArrayList convQtyList = discommon.getConvQuantityFact(unit, toUnit, itemCode, quantityInput, convFact,conn);

												System.out.println("convQtyList = "+convQtyList);

												meffQty = Double.parseDouble(convQtyList.get(1).toString());

												System.out.println("meffQty ["+meffQty+"]");
												

												if(meffQty == -999999999)

												{

													meffQty = 0;	

													errString = getErrorString("phy_input","CONVQTYNFD",userId);

													break;

												}

												if (volumn == 0)

												{

													volumn= Double.parseDouble(convQtyList.get(0).toString());

												}

												

											}

											else

											{

												meffQty = quantity ;

												if (volumn == 0) 

												{

													volumn=1;

												}

												

											}

											System.out.println("meffQty ["+meffQty+"] and quantity ["+quantity+"]");

											if( meffQty != quantity )

											{

												//System.out.println(" in side (meffQty != quantity) ");

												errString = getErrorString("phy_input","NOTMATCH",userId);

												break;

											}

										}

										else if ("P".equalsIgnoreCase(phyHanBasis))

										{											

											quantityInput = quanitityPerPallet(itemCode,lotNo, quantityInput ,conn );

											if( quantityInput != quantity)

											{

												//System.out.println(" as per 'P' ");

												errString = getErrorString("phy_input","NOTMATCH",userId);

												break;

											}

										}

									}

									if( rs != null )

									{

										rs.close();

										rs = null;

									}

									if( pstmt != null)

									{

										pstmt.close();

										pstmt = null;

									}

								}

							//}

						}
						*/
					}
				}
			    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 second form validation");
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();

						if( childNodeName.equalsIgnoreCase( "loc_code" ))								
						{
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("loc_code","VTLOCN2",userId);
								break;								
							}
							else
							{
								locCode = genericUtility.getColumnValue("loc_code", dom);
								sSQL = "SELECT COUNT(*) FROM LOCATION WHERE LOC_CODE = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, locCode);
								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","VTLOCCD",userId);
									break;								
								}

								sSQL = "SELECT COUNT(*) FROM PHYSCANDET WHERE LOC_CODE = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, locCode);
								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","VTINVLOC",userId);
									break;								
								}
							}
						}

						else if( childNodeName.equalsIgnoreCase( "item_code" ))								
						{
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("item_code","NULLITCODE",userId);
								break;								
							}
							else
							{
								locCode = genericUtility.getColumnValue("loc_code", dom);
								itemCode = genericUtility.getColumnValue("item_code", dom);
								sSQL = "SELECT COUNT(*) FROM item WHERE ITEM_CODE = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, itemCode);
								rs = pstmt.executeQuery();							
								if(rs.next())
								{  
									count = rs.getInt(1);
								}
								pstmt.close();
								pstmt = null;
								rs.close();
								rs = null;
								
								if(count == 0)
								{
									errString = getErrorString("item_code","VMITEM1",userId);
									break;								
								}

								sSQL = "SELECT COUNT(*) FROM PHYSCANDET WHERE ITEM_CODE = ? AND LOC_CODE = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, locCode);
								rs = pstmt.executeQuery();							
								if(rs.next())
								{  
									count = rs.getInt(1);
								}
								pstmt.close();
								pstmt = null;
								rs.close();
								rs = null;
								
								if(count == 0)
								{
									errString = getErrorString("item_code","VTINVITM",userId);
									break;								
								}
							}
						}
						
						else if( childNodeName.equalsIgnoreCase( "lot_no" ))
						{
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("lot_no","NULLLOTNO",userId);
								break;								
							}
							else
							{
								locCode = genericUtility.getColumnValue("loc_code", dom);
								itemCode = genericUtility.getColumnValue("item_code", dom);
								lotNo = genericUtility.getColumnValue("lot_no", dom);
								
								sSQL = "SELECT COUNT(*) FROM PHYSCANDET WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, locCode);
								pstmt.setString(3, lotNo);
								rs = pstmt.executeQuery();							
								if(rs.next())
								{  
									count = rs.getInt(1);
								}
								pstmt.close();
								pstmt = null;
								rs.close();
								rs = null;
								
								if(count == 0)
								{
									errString = getErrorString("lot_no","VTINVLOT",userId);
									break;								
								}
							}
						}

						else if( childNodeName.equalsIgnoreCase( "lot_sl" ))
						{
							System.out.println("lot sl["+childNode.getNodeValue()+"]");
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("lot_sl","VLOT01",userId);
								break;								
							}
							else
							{
								locCode = genericUtility.getColumnValue("loc_code", dom);
								itemCode = genericUtility.getColumnValue("item_code", dom);
								lotNo = genericUtility.getColumnValue("lot_no", dom);
								lotSl = genericUtility.getColumnValue("lot_sl", dom);
								
								sSQL = "SELECT COUNT(*) FROM PHYSCANDET WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, locCode);
								pstmt.setString(3, lotNo);
								pstmt.setString(4, lotSl);
								rs = pstmt.executeQuery();							
								if(rs.next())
								{  
									count = rs.getInt(1);
								}
								pstmt.close();
								pstmt = null;
								rs.close();
								rs = null;
								
								if(count == 0)
								{
									errString = getErrorString("lot_sl","VTLPNNTVAL",userId);
									break;								
								}
							}
						}

						else if( childNodeName.equalsIgnoreCase( "phy_input" ))
						{
							System.out.println("phy_input["+childNode.getNodeValue()+"]");
							String phyInput=checkNull(genericUtility.getColumnValue("phy_input",dom));
							System.out.println("phy input ="+phyInput);
							locCode = genericUtility.getColumnValue("loc_code", dom);
							System.out.println("locCode ="+locCode);
							double quantityTest = 0.0;  

							sSQL = "select inv_stat from location  where loc_code = ? ";
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1, locCode);
							rs = pstmt.executeQuery();							
							if(rs.next())
							{  
								invStat = rs.getString(1);
							}
							pstmt.close();
							pstmt = null;
							rs.close();
							rs = null;
							
							if(childNode.getFirstChild() != null)
							{
								if( !isDouble(genericUtility.getColumnValue("phy_input",dom)))
								{
									errString = getErrorString("phy_input","NOTANUMBER",userId);
									break;
								}
								else
								{
									/***
									locCode = genericUtility.getColumnValue("loc_code", dom);
									itemCode = genericUtility.getColumnValue("item_code", dom);
									lotNo = genericUtility.getColumnValue("lot_no", dom);
									siteCode = genericUtility.getColumnValue("site_code", dom2);
									lotSl = genericUtility.getColumnValue("lot_sl", dom);
									quantityInput =  Double.parseDouble(genericUtility.getColumnValue("phy_input", dom) == null ?"0":genericUtility.getColumnValue("phy_input", dom));
									
									sSQL = "SELECT quantity FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, itemCode);
									pstmt.setString(2, siteCode);
									pstmt.setString(3, locCode);
									pstmt.setString(4, lotNo);
									pstmt.setString(5, lotSl);
									rs = pstmt.executeQuery();							
									if(rs.next())
									{  
										qty = rs.getDouble(1);
									}
									pstmt.close();
									pstmt = null;
									rs.close();
									rs = null;
									
									if(qty != quantityInput)
									{
										errString = getErrorString("phy_input","NOTMATCH",userId);
										break;								
									}
									*/
									if(phyInput==null || phyInput.length()==0)
									{
										phyInput="0";
									}

									if( !isInteger(phyInput))

									{

										errString = getErrorString("case_count","NOTANUMBER",userId);

										break;

									}

									else

									{

										quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom) == null?"0":genericUtility.getColumnValue("quantity", dom) );
										quantityInput = Double.parseDouble(genericUtility.getColumnValue("phy_input", dom) == null ? "0":genericUtility.getColumnValue("phy_input", dom));
										itemCode = genericUtility.getColumnValue("item_code",dom).trim();
										lotNo = genericUtility.getColumnValue("lot_no",dom).trim();
										lotSl = genericUtility.getColumnValue("lot_sl",dom).trim();
										System.out.println("quantity ["+quantity+"] quantityInput ["+quantityInput+"] itemCode ["+itemCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
										sSQL = "SELECT L.PHY_HAN_BASIS, L.PHY_HAN_UOM , D.UNIT FROM PHYSCAN H,PHYSCANDET D,ITEM M,LOCATION L "

											   +" WHERE H.TRAN_DATE = ? AND  " 

											   +" D.ITEM_CODE = M.ITEM_CODE   "	+subCond								   

										       +" AND D.LOC_CODE = L.LOC_CODE  AND L.INV_STAT = ? "

										       +" AND D.LOT_NO = ? AND D.ITEM_CODE = ? AND D.LOT_SL = ?  ";
										
										System.out.println("** sSQL **["+sSQL+"]");

										pstmt = conn.prepareStatement(sSQL);

										pstmt.setDate(1, sqlDate);								

										pstmt.setString(2, invStat);	

										pstmt.setString(3, lotNo);

										pstmt.setString(4, itemCode);

										pstmt.setString(5, lotSl);

										rs = pstmt.executeQuery();
										if(rs.next())
										{

											phyHanBasis = rs.getString("PHY_HAN_BASIS");
											unit = checkNull( rs.getString("PHY_HAN_UOM")); //change done by Kunal on 07/09/12
											toUnit = checkNull( rs.getString("UNIT")); //change done by Kunal on 07/09/12
											System.out.println(" Physical handel basis is ["+phyHanBasis+"]");

											phyHanBasis = checkNull(phyHanBasis);

											sqlForCase =" SELECT ILP.SHIPPER_SIZE, I.UNIT, I.MFR_PART_NO, I.VOLUME FROM ITEM I,  ITEM_LOT_PACKSIZE ILP " +

														" WHERE ILP.ITEM_CODE = ? " +

														" AND I.ITEM_CODE = ILP.ITEM_CODE "+

														" AND ? BETWEEN LOT_NO__FROM AND LOT_NO__TO ";

											pstmt1 = conn.prepareStatement(sqlForCase);

											pstmt1.setString(1, itemCode);

											pstmt1.setString(2, lotNo);

											rs2 = pstmt1.executeQuery();

											if( rs2.next())

											{

												shipperSize = rs2.getInt("SHIPPER_SIZE");

												//unit = rs2.getString("UNIT");

												mrfPartNo = rs2.getString("MFR_PART_NO");

												volumn = rs2.getDouble("VOLUME");

											}

											rs2.close();rs2 = null;

											pstmt1.close();pstmt1 = null;

											if( "".equalsIgnoreCase(phyHanBasis))

											{

												errString = getErrorString("phy_input","SCANTYPE",userId);

												break;

											}

											if("L".equalsIgnoreCase(phyHanBasis))
											{
												quantityTest = shipperSize * quantityInput;
												if( (shipperSize * quantityInput) != quantity)
												{
													System.out.println(" as per 'L' ");
													errString = getErrorString("phy_input","NOTMATCH",userId);
													break;
												}
											}
											

											else if("I".equalsIgnoreCase(phyHanBasis))
											{
												quantityTest = quantityInput;
												if( quantityInput != quantity)
												{
													System.out.println(" as per 'I' ");
													errString = getErrorString("phy_input","NOTMATCH",userId);
													break;
												}
											}

											else if ("U".equalsIgnoreCase(phyHanBasis))
											{

												System.out.println(" as per 'U' ");

												if(!unit.equalsIgnoreCase(toUnit))

												{
													if(volumn != 0)
													{

														convFact = volumn;

													}

													ArrayList convQtyList = discommon.getConvQuantityFact(unit, toUnit, itemCode, quantityInput, convFact,conn);

													System.out.println("convQtyList = "+convQtyList);

													meffQty = Double.parseDouble(convQtyList.get(1).toString());

													System.out.println("meffQty ["+meffQty+"]");
													

													if(meffQty == -999999999)

													{

														meffQty = 0;	

														errString = getErrorString("phy_input","CONVQTYNFD",userId);

														break;

													}

													if (volumn == 0)

													{

														volumn= Double.parseDouble(convQtyList.get(0).toString());

													}

													

												}

												else

												{

													meffQty = quantityInput ; //added by Kunal on 12/09/12 

													if (volumn == 0) 

													{

														volumn=1;

													}

													

												}

												System.out.println("meffQty ["+meffQty+"] and quantity ["+quantity+"]");
												quantityTest = meffQty;

												if( meffQty != quantity )

												{

													//System.out.println(" in side (meffQty != quantity) ");

													errString = getErrorString("phy_input","NOTMATCH",userId);

													break;

												}

											}

											else if ("P".equalsIgnoreCase(phyHanBasis))

											{											

												quantityInput = quanitityPerPallet(itemCode,lotNo, quantityInput ,conn );
												quantityTest = quantityInput;

												if( quantityInput != quantity)

												{
													//System.out.println(" as per 'P' ");
													errString = getErrorString("phy_input","NOTMATCH",userId);
													break;

												}

											}

										}

										if( rs != null )

										{

											rs.close();

											rs = null;

										}

										if( pstmt != null)

										{

											pstmt.close();

											pstmt = null;

										}

									}
									
									locCode = genericUtility.getColumnValue("loc_code", dom);
									itemCode = genericUtility.getColumnValue("item_code", dom);
									lotNo = genericUtility.getColumnValue("lot_no", dom);
									siteCode = genericUtility.getColumnValue("site_code", dom2);
									lotSl = genericUtility.getColumnValue("lot_sl", dom);
									//quantityInput =  Double.parseDouble(genericUtility.getColumnValue("phy_input", dom) == null ?"0":genericUtility.getColumnValue("phy_input", dom));
									
									sSQL = "SELECT quantity FROM STOCK WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
									pstmt = conn.prepareStatement(sSQL);
									pstmt.setString(1, itemCode);
									pstmt.setString(2, siteCode);
									pstmt.setString(3, locCode);
									pstmt.setString(4, lotNo);
									pstmt.setString(5, lotSl);
									rs = pstmt.executeQuery();							
									if(rs.next())
									{  
										qty = rs.getDouble(1);
									}
									pstmt.close();
									pstmt = null;
									rs.close();
									rs = null;
									System.out.println("quantity Test in stock"+quantityTest);
									if(qty != quantityTest)
									{
										errString = getErrorString("phy_input","NOTMATCH",userId);
										break;								
									}
									
								}
							}
						}
					}
				}
			} 
		}
		catch(Exception e)
		{
			e.printStackTrace();			
			errString=e.getMessage();			
			throw new ITMException( e );
		}
		finally
		{
			try
			{
				
				if(conn!=null)
				{
					if(rs != null )rs.close();
					if(rs1 != null )rs.close();
					rs = null;
					rs1 = 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
	{
		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("xml str1 in Detail --->["+xmlString+"]");

			  System.out.println("xml str2 in Detail --->["+xmlString1+"]");

			  System.out.println("xml str3 in Detail --->["+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
	{
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		String  sSQL = "";
		String sql = "";
		int currentFormNo = 0 ;
		int lineNo = 0 ;
		int prioritySeq = 0;
		String siteCode = "";
		String loginCode = "";
		String chgTerm = "";
		String siteDescr = "";
		String itemDescr = "";
		String locDescr = "";
		String tranId = "";
		String confirmed = "";
		int cnt = 0;
		String cycleCriteria = "";
		String analysisClass = "";
		String itemCode = "",locCode = "", lotNo = "", lotSl = "", quantity = "", unit = "";
		String currAppdate= "", empCode = "";
		String phyHanBasis = "", phyHanBasisDesc = "";
		Timestamp currDateNew = null;
		java.util.Date currDate = null;
		String currDateStr = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		String phyHanUOM = "";
        java.util.Date utilDate = new java.util.Date();
		
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp date1 = Timestamp.valueOf(sdf.format(utilDate).toString() + " 00:00:00.0");
			String applDateFormat = genericUtility.getApplDateFormat();
			currDate = new java.util.Date();
			sdf = new SimpleDateFormat(applDateFormat);
			currDateStr = sdf.format(currDate);
			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 1 :
				{
					valueXmlString.append("<Detail1 domID='1' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
					if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						valueXmlString.append( "<tran_date><![CDATA[" ).append( currDateStr ).append( "]]></tran_date>\r\n" );
						date1 = Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
						sql = "SELECT TRAN_ID FROM PHYSCAN WHERE TRAN_DATE = ?  and tran_type = 'A'";
				    	pstmt = conn.prepareStatement( sql );
						pstmt.setTimestamp(1, date1);						
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							tranId = rs.getString("TRAN_ID");
							//valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
						}
						if(rs != null)
						{	
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}
					}
					valueXmlString.append("</Detail1>\r\n");
				}
				break;
				case 2 :
				{	
					valueXmlString.append("<Detail2 domID='1' selected=\"Y\">\r\n");
					valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
					siteCode =(genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"));
				    if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
				    	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("<site_code>").append("<![CDATA["+siteCode+"]]>").append("</site_code>");
						
						currDateNew = new java.sql.Timestamp(System.currentTimeMillis()) ;
						currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDateNew).toString();	
						currAppdate = genericUtility.getValidDateString(currAppdate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat());
						valueXmlString.append( "<emp_code__user><![CDATA[" ).append( empCode ).append( "]]></emp_code__user>\r\n" );
						chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
						siteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));

						if(genericUtility.getColumnValue("tran_date", dom1) != null)
						{
							date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom1) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
							sql = "SELECT TRAN_ID,CONFIRMED FROM PHYSCAN WHERE TRAN_DATE = ?  AND SITE_CODE=?";
					    	pstmt = conn.prepareStatement( sql );
							pstmt.setTimestamp(1, date1);
							pstmt.setString(2, siteCode);
							rs = pstmt.executeQuery();	
							if( rs.next() )
							{
								tranId = rs.getString("TRAN_ID");
								confirmed = rs.getString("CONFIRMED");
								//Commented by manoj dtd 05/10/2012 not required
								//valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
								
								valueXmlString.append( "<tran_date><![CDATA[" ).append( genericUtility.getColumnValue("tran_date", dom1) ).append( "]]></tran_date>\r\n" );
								//valueXmlString.append( "<tran_date><![CDATA[" ).append( genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom1) , genericUtility.getApplDateFormat()) ).append( "]]></tran_date>\r\n" );
								//valueXmlString.append("<confirmed protect=\"1\">").append("<![CDATA["+confirmed+"]]>").append("</confirmed>");
							}
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
						}
						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( "<descr><![CDATA[" ).append( checkNull( siteDescr )).append( "]]></descr>\r\n" );
						//valueXmlString.append( "<chg_user><![CDATA[" ).append( loginCode ).append( "]]></chg_user>\r\n" );
						//valueXmlString.append( "<chg_date><![CDATA[" ).append( currDateStr ).append( "]]></chg_date>\r\n" );
						//valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
	                }
					valueXmlString.append("</Detail2>");
				  }	
	              break;
				  case 3 :
				  {	  SimpleDateFormat sdf1=new SimpleDateFormat(genericUtility.getApplDateFormat());
				      //date1=Timestamp.valueOf(sdf1.format(sdf1.parse(genericUtility.getColumnValue("tran_date", dom2))));
					  loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
					  System.out.println("loginCode --->["+loginCode+"]");
					  tranId = genericUtility.getColumnValueFromNode("tran_id", dom2.getElementsByTagName("Detail2").item(0));
					  date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom2) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
					  //date1=Timestamp.valueOf(sd)
					  System.out.println("date1 --->["+date1+"]");
					  siteCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
					  if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					  {
						  
						  //Commented by manoj dtd05/10/2012 tran_id not required
						    /*sql = "SELECT count(*) FROM PHYSCANDET WHERE TRAN_ID = ?  ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString( 1, tranId );
							
							rs = pstmt.executeQuery();	
							if( rs.next() )
							{
								cnt = rs.getInt(1);
							}
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
							*/
						  //End Commented by manoj dtd05/10/2012 tran_id not required
							
								sSQL = "SELECT H.TRAN_ID,S.STATUS , S.LINE_NO, S.ITEM_CODE,S.SITE_CODE,S.LOC_CODE,S.LOT_NO,S.LOT_SL, S.QUANTITY,S.UNIT,M.DESCR AS ITEM_DESCR, "
									+ " L.DESCR AS LOC_DESCR, L.PHY_HAN_BASIS, L.PHY_HAN_UOM , S.CYCLE_CRITERIA, S.ANALYSIS_CLASS , S.PHY_INPUT, S.PRIORITY_SEQ"
									+ " ,CASE WHEN S.PHY_INPUT IS NULL THEN 0 ELSE S.PHY_INPUT END PHY_INPUT "
									+ " FROM PHYSCANDET S,ITEM M,LOCATION L,PHYSCAN H "
									+ " WHERE H.TRAN_ID=S.TRAN_ID AND  S.ITEM_CODE = M.ITEM_CODE AND S.LOC_CODE = L.LOC_CODE "
									+ " AND H.tran_date = ? AND " +
									" (CASE WHEN S.QUANTITY IS NULL THEN 0 ELSE S.QUANTITY END ) !=(CASE WHEN  S.QTY_PHYSICAL IS NULL THEN 0 ELSE  S.QTY_PHYSICAL END ) " + //change done by kunal  add QTY_PHYSICAL for PHY_INPUT
									" AND S.STATUS = 'Y' AND H.SITE_CODE=? ORDER BY PRIORITY_SEQ ASC";
								
//								sSQL = "SELECT S.STATUS , S.LINE_NO, S.ITEM_CODE,S.SITE_CODE,S.LOC_CODE,S.LOT_NO,S.LOT_SL, S.QUANTITY,S.UNIT,M.DESCR AS ITEM_DESCR, "
//										+ " L.DESCR AS LOC_DESCR, L.PHY_HAN_BASIS, L.PHY_HAN_UOM , S.CYCLE_CRITERIA, S.ANALYSIS_CLASS , S.PHY_INPUT, S.PRIORITY_SEQ"
//										+ " ,CASE WHEN S.PHY_INPUT IS NULL THEN 0 ELSE S.PHY_INPUT END PHY_INPUT "
//										+ " FROM PHYSCANDET S,ITEM M,LOCATION L "
//										+ " WHERE  S.ITEM_CODE = M.ITEM_CODE AND S.LOC_CODE = L.LOC_CODE "
//										+ " AND s.tran_id = ? AND" +
//										" (CASE WHEN S.QUANTITY IS NULL THEN 0 ELSE S.QUANTITY END ) !=(CASE WHEN  S.QTY_PHYSICAL IS NULL THEN 0 ELSE  S.QTY_PHYSICAL END ) " + //change done by kunal  add QTY_PHYSICAL for PHY_INPUT
//										" AND S.STATUS = 'Y' ORDER BY PRIORITY_SEQ ASC";
		
								System.out.println("** sSQL **[" + sSQL + "]");
								pstmt = conn.prepareStatement(sSQL);
								pstmt.setTimestamp(1,date1);
								pstmt.setString(2,siteCode);
								rs = pstmt.executeQuery();
								while (rs.next()) 
								{
									lineNo = rs.getInt("LINE_NO");
									System.out.println("Line_no["+lineNo+"]");
									itemCode = rs.getString("ITEM_CODE");
									itemDescr = rs.getString("ITEM_DESCR");
									siteCode = rs.getString("SITE_CODE");
									locCode = rs.getString("LOC_CODE");
									locDescr = rs.getString("LOC_DESCR");
									lotNo = rs.getString("LOT_NO");
									lotSl = rs.getString("LOT_SL");
									quantity = rs.getString("QUANTITY");
									unit = rs.getString("UNIT");
									phyHanBasis = rs.getString("PHY_HAN_BASIS");
									phyHanUOM = rs.getString("PHY_HAN_UOM");
									cycleCriteria = checkNullAndTrim(rs.getString("CYCLE_CRITERIA"));
									analysisClass = checkNullAndTrim(rs.getString("ANALYSIS_CLASS"));
									prioritySeq = rs.getInt("PRIORITY_SEQ");
									System.out.println("phyHanBasis - >[" + phyHanBasis	+ "] phyHanUOM [" + phyHanUOM + "]");
									tranId=rs.getString("TRAN_ID");
									phyHanBasisDesc = "";
									if (phyHanBasis != null ) 
									{
										if ("L".equalsIgnoreCase(phyHanBasis)) {
											phyHanBasisDesc = "Lot Wise Shipper";
										} else if ("I".equalsIgnoreCase(phyHanBasis)) 
										{
											phyHanBasisDesc = " Integral Quantity";
										} else if ("U".equalsIgnoreCase(phyHanBasis)) 
										{
											phyHanBasisDesc = "UOM";
											
										} else if ("P".equalsIgnoreCase(phyHanBasis)) 
										{
											phyHanBasisDesc = "Pallet Basis";
										}
									}
									System.out.println(" phyHanBasisDesc ["+phyHanBasisDesc+"]");
									
									valueXmlString.append("<Detail3 domID='" + lineNo+ "' selected=\"N\">\r\n");
									valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
									valueXmlString.append("<line_no>").append(lineNo).append("</line_no>\r\n");
									valueXmlString.append("<status>").append("Y").append("</status>\r\n");
									valueXmlString.append("<priority_seq>").append(prioritySeq).append("</priority_seq>\r\n");
								
									valueXmlString.append("<tran_id>").append(tranId).append("</tran_id>\r\n");
									valueXmlString.append("<line_no>").append(lineNo).append("</line_no>\r\n");	
									valueXmlString.append("<item_code>").append(checkNullAndTrim(itemCode)).append("</item_code>\r\n");
									//Changed by sankara on 19/10/2012 to append descr in CDATA format
									//valueXmlString.append("<descr>").append(itemDescr).append("</descr>\r\n");
									valueXmlString.append( "<descr><![CDATA[" ).append( checkNull(itemDescr)).append( "]]></descr>\r\n" );
									valueXmlString.append("<site_code>").append(siteCode).append("</site_code>\r\n");
									valueXmlString.append("<loc_code><![CDATA[").append(checkNullAndTrim(locCode)).append("]]></loc_code>\r\n");
									//valueXmlString.append("<location_descr>").append(locDescr).append("</location_descr>\r\n");
									valueXmlString.append("<location_descr><![CDATA[").append(locDescr).append("]]></location_descr>\r\n");
									valueXmlString.append("<lot_no>").append(checkNullAndTrim(lotNo)).append("</lot_no>\r\n");
									valueXmlString.append("<lot_sl><![CDATA[").append(checkNullAndTrim(lotSl)).append("]]></lot_sl>\r\n");
									valueXmlString.append("<quantity>").append(quantity).append("</quantity>\r\n");														
									valueXmlString.append("<unit>").append(unit).append("</unit>\r\n");														
									valueXmlString.append("<qty_physical>").append(("0")).append("</qty_physical>\r\n");
									valueXmlString.append("<phy_han_basis>").append(checkNull(phyHanBasis)).append("</phy_han_basis>\r\n");
									valueXmlString.append("<phy_input_scan>").append(phyHanBasisDesc).append("</phy_input_scan>\r\n");
									valueXmlString.append("<emp_code__user>").append(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" )).append("</emp_code__user>\r\n");
									valueXmlString.append("<analysis_class>").append(analysisClass).append("</analysis_class>\r\n");
									valueXmlString.append("<cycle_criteria>").append(cycleCriteria).append("</cycle_criteria>\r\n");
									valueXmlString.append("<phy_input>").append(rs.getInt("PHY_INPUT")).append("</phy_input>\r\n");
									if( "U".equalsIgnoreCase(phyHanBasis))
									{
										valueXmlString.append("<conv_fact>").append(checkNull(phyHanUOM)).append("</conv_fact>\r\n");
									}
									else
									{
										valueXmlString.append("<conv_fact>").append("").append("</conv_fact>\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());
			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 boolean isDouble( String input )  
    {  
       try  
       {  
    	   System.out.println(" Value of input ["+input+"]");
          Double.parseDouble( input );  
          return true;  
       }  
       catch( Exception e)  
       {  
          return false;  
       }  
    }
	
	private String checkNullAndTrim( String inputVal )
	{
		System.out.println("value["+inputVal+"]");
		if ( inputVal == null || inputVal.trim().equalsIgnoreCase("null") )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}
	private String checkNull( String inputVal )
	{
		if ( inputVal == null || inputVal.trim().equalsIgnoreCase("null") )
		{
			inputVal = "";
		}
		return inputVal;
	}
	
	private boolean isInteger( String input )  
    {  
       try  
       {  
    	   System.out.println(" Value of input ["+input+"]");
          Integer.parseInt( input );  
          return true;  
       }  
       catch( Exception e)  
       {  
          return false;  
       }  
    }
	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; 

	}
}
		
		