/*
 * MPhyItemScanIC.java
 * Developer - Mahesh Patidar
 * Date - 03/08/12 
 */

package ibase.webitm.ejb.wms;

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.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 MPhyItemScanIC extends ValidatorEJB implements MPhyItemScanICLocal, MPhyItemScanICRemote
{
	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 = "" ;
		String userId = "";
		int currentFormNo=0;
		int childNodeListLength;
		double quantity = 0;
		double qty = 0;
		ConnDriver connDriver = new ConnDriver();

		String columnValue ="", sSQL = "",  itemCode = "", lotNo = "", lotSl ="";
		String locCode = "";
		String siteCode = "";
		String invStat = "",isEmpty;
		//  added by cpatil start 06-sept-12
		double  quantityInput=0;
		String phyHanBasis="",unit="",toUnit="",mrfPartNo="";
		String sqlForCase="",prdCode = "" , errCode = "";
		java.util.Date tranDate = null;
		PreparedStatement pstmt1= null;
		ResultSet rs2 = null;
		double shipperSize = 0, volumn = 0, convFact = 1, meffQty = 0;
		DistCommon discommon = new DistCommon();
		Timestamp date1 = null;
		String remarks="";
		String isStock="";
		SimpleDateFormat sdf=null;
		//java.util.Date currDate=null;
		//String currDateStr="";
		java.sql.Timestamp dts=null;
		
		//  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:
			{
				sdf= new SimpleDateFormat(genericUtility.getDBDateFormat());
				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( "loc_code" ))
						{
							System.out.println("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;								
								}
								else
								{
									count=0;
									
									sql = "select count(*) from physcan h , physcandet d where h.tran_id = d.tran_id  and h.tran_date = ?  "
										  +" and d.loc_code= ?   ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setTimestamp(1, dts);
									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("loc_code","ALLRDSCN",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,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("site_code","INVSITECOD",userId);
								break;
							}
							else //added by kunal on 15-feb-14 for period code
							{
								siteCode = genericUtility.getColumnValue("site_code", dom);
								System.out.println("364 site code ="+siteCode);
								if (genericUtility.getColumnValue("tran_date", dom) != null)
								{
									tranDate = sdf1.parse(genericUtility.getColumnValue("tran_date", dom));
									sql = "select code from period where ?  between fr_date and to_date";
									pstmt = conn.prepareStatement(sql);
									pstmt.setDate(1, new java.sql.Date(tranDate.getTime()));
									rs = pstmt.executeQuery();
									if (rs.next())
									{
										prdCode = rs.getString(1);

									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;

									sql = "select count(*) from period_stat where site_code = ? and prd_code = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, siteCode);
									pstmt.setString(2, prdCode);
									rs = pstmt.executeQuery();
									if (rs.next())
									{
										count = rs.getInt(1);

									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
									if (count == 0)
									{
										errString = getErrorString("site_code","VTSITEPD",userId);
										break;
									}
									// nvo_functions_adv.nf_check_period('SAL',mdate1,msite_code)
									errCode = nfCheckPeriod("IC", tranDate, siteCode);
									System.out.println("425 Error Code = " + errCode);
									if (errCode != null && errCode.trim().length() > 0)
									{
										errString = getErrorString("site_code",errCode,userId);
										break;
									}
								} 							 
							}//end 

						}
					}
					else if( childNodeName.equalsIgnoreCase( "tran_date" ))
					{
						if(childNode.getFirstChild() == null)
						{
							errString = getErrorString("tran_date","NULLTRANDT",userId);
							break;								
						}
					}
					else if( childNodeName.equalsIgnoreCase( "loc_code" ))
					{
						System.out.println("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;								
							}
							//COMMENT by Kunal on 18/09/12 
							/*

							sSQL = "SELECT COUNT(*) FROM STOCK 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( "isempty" )) //added by Kunal on 20/19/12
					{
						isEmpty = checkNull(genericUtility.getColumnValue("isempty", dom));
						System.out.println("isEmpty = "+isEmpty);
						if(!"N".equalsIgnoreCase(isEmpty.trim()) && !"Y".equalsIgnoreCase(isEmpty.trim()))
						{
							errString = getErrorString("isempty","INVISEMTY",userId);
							break;
						}
					}
					else if( childNodeName.equalsIgnoreCase( "remarks" ))
					{
						remarks=checkNull(genericUtility.getColumnValue("remarks", dom));
						isStock=checkNull(genericUtility.getColumnValue("isstock", dom));
						isEmpty=checkNull(genericUtility.getColumnValue("isempty", dom));
						if("N".equalsIgnoreCase(isStock) && "N".equalsIgnoreCase(isEmpty) && remarks.trim().length()==0)
						{
							errString = getErrorString("remarks","EMPTYRMK",userId);
							break;
						}
						if("Y".equalsIgnoreCase(isStock) && remarks.trim().length()>0)
						{
							errString = getErrorString("remarks","EMPTYRMK1",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 second form validation");
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();

					if( childNodeName.equalsIgnoreCase( "item_code" ))								
					{
						if(childNode.getFirstChild() == null)
						{
							errString = getErrorString("item_code","NULLITCODE",userId);
							break;								
						}
						else
						{
							locCode = genericUtility.getColumnValue("loc_code", dom1);
							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 STOCK WHERE ITEM_CODE = ? AND LOC_CODE = ? and site_code = ? "; //added  by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction 
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1, itemCode);
							pstmt.setString(2, locCode);
							pstmt.setString(3, checkNull(genericUtility.getColumnValue("site_code", dom)));
							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", dom1);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);

							sSQL = "SELECT COUNT(*) FROM STOCK WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? and site_code = ? "; //added  by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1, itemCode);
							pstmt.setString(2, locCode);
							pstmt.setString(3, lotNo);
							pstmt.setString(4, checkNull(genericUtility.getColumnValue("site_code", dom)));
							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", dom1);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							lotSl = genericUtility.getColumnValue("lot_sl", dom);

							sSQL = "SELECT COUNT(*) FROM STOCK WHERE ITEM_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? and site_code = ? "; //added  by kunal add site code in cond. on 18/09/12 as par Manoj Sharma instruction
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1, itemCode);
							pstmt.setString(2, locCode);
							pstmt.setString(3, lotNo);
							pstmt.setString(4, lotSl);
							pstmt.setString(5, checkNull(genericUtility.getColumnValue("site_code", dom)));
							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()+"]");
						locCode = genericUtility.getColumnValue("loc_code", dom);
						System.out.println("locCode ="+locCode);
						double quantityTest = 0.0;
						
                        //added by Kunal on 10/10/12 start
						date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom2) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
						System.out.println("tran date="+date1);
						
						itemCode = genericUtility.getColumnValue("item_code", dom);
						lotNo = genericUtility.getColumnValue("lot_no", dom);
						lotSl = genericUtility.getColumnValue("lot_sl", dom);
						
						/*sql = "select count(*) from physcan h , physcandet d where h.tran_id = d.tran_id  and h.tran_date = ?  "
							  +" and d.loc_code= ?  and d.lot_no = ?  and d.lot_sl = ?  and d.item_code = ? ";*/
							  //Condition changed for Already scanned transaction
						//Commented by manoj dtd 05/02/2013 to add validation while entering location at beginning
						/*sql = "select count(*) from physcan h , physcandet d where h.tran_id = d.tran_id  and h.tran_date = ?  "
							  +" and d.loc_code= ?  ";//and d.lot_no = ?  and d.lot_sl = ?  and d.item_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1, date1);
						pstmt.setString(2, locCode);
						//pstmt.setString(3, lotNo);
						//pstmt.setString(4, lotSl);
						//pstmt.setString(5, itemCode);
						rs = pstmt.executeQuery();							
						if(rs.next())
						{  
							count = rs.getInt(1);
						}
						pstmt.close();
						pstmt = null;
						rs.close();
						rs = null;
						if(count > 0)
						{
							errString = getErrorString("phy_input","ALLRDSCN",userId);
							break;
						}*/
						//added by Kunal on 10/10/12 end

						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;
						System.out.println("phy input="+checkNull(genericUtility.getColumnValue("phy_input",dom)).trim());


						if( !isInteger( checkNull(genericUtility.getColumnValue("phy_input",dom)).trim()))
						{
							errString = getErrorString("phy_input","NOTANUMBER",userId);
							break;
						}
						else
						{
							/*	locCode = genericUtility.getColumnValue("loc_code", dom1);
									itemCode = genericUtility.getColumnValue("item_code", dom);
									lotNo = genericUtility.getColumnValue("lot_no", dom);
									siteCode = genericUtility.getColumnValue("site_code", dom2);
									lotSl = genericUtility.getColumnValue("lot_sl", dom);
									quantity =  Double.parseDouble(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 != quantity)
									{
										errString = getErrorString("phy_input","NOTMATCH",userId);
										break;								
									}
							 */
							// added by cpatil on 07-sept-12 start
							System.out.println("@@@@@ quantity validation modified.....");
							quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom) == null ?"0":genericUtility.getColumnValue("quantity", dom)); //chnage done by Kunal on 14/09/12
							quantityInput = Double.parseDouble(genericUtility.getColumnValue("phy_input", dom) == null ? "0":genericUtility.getColumnValue("phy_input", dom) ); //chnage done by Kunal on 14/09/12
							itemCode = checkNull(genericUtility.getColumnValue("item_code",dom)).trim();
							lotNo = checkNull(genericUtility.getColumnValue("lot_no",dom)).trim();
							lotSl = checkNull(genericUtility.getColumnValue("lot_sl",dom)).trim();
							locCode = checkNull(genericUtility.getColumnValue("loc_code",dom)).trim();
							System.out.println("quantity ["+quantity+"] quantityInput ["+quantityInput+"] itemCode ["+itemCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom2) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
							//Validation Changed by Manoj dtd 28/03/2014 to exclude physcandet table
							sSQL="SELECT PHY_HAN_BASIS,PHY_HAN_UOM FROM LOCATION WHERE LOC_CODE=?";
							pstmt=conn.prepareStatement(sSQL);
							pstmt.setString(1, locCode);
							rs=pstmt.executeQuery();
							if(rs.next())
							{
							/*
							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   "								   
									+" 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.setTimestamp(1, date1);								
							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"));
								pstmt1=conn.prepareStatement("SELECT UNIT FROM ITEM WHERE ITEM_CODE=?");
								pstmt1.setString(1, itemCode);
								rs2=pstmt1.executeQuery();
								if(rs2.next())
								{
									toUnit = checkNull(rs2.getString("UNIT"));
								}
								rs2.close();
								rs2=null;
								pstmt1.close();
								pstmt1=null;
								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");
									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 = quantityInput ; //change done by Kunal on 04/09/12
										if (volumn == 0) 
										{
											volumn=1;
										}
									}

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

									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;
							}
							//	cpatil end
						}

					}
					
				}
			}
			} 
		}
		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("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
	{
		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 = "";
		int cnt = 0;
		java.sql.Date wDate=null;
		int days=0;
		int totHdays=0;
		String status = "";
		String cycleCriteria = "";
		String analysisClass = "";
		String itemCode = "",locCode = "", lotNo = "", lotSl = "", quantity = "", unit = "";
		String currAppdate= "", empCode = "",loginSiteCode = "",isEmpty = "";
		String phyHanBasis = "", phyHanBasisDesc = "";
		Timestamp currDateNew = null;
		Timestamp date2 = null;
		java.util.Date currDate = null;
		java.util.Date todayDate=new java.util.Date();
		String currDateStr = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		String phyHanUOM = "";
		java.util.Date utilDate = new java.util.Date();
		String isStock="";

		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();

			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>");
			//Changes done by manoj dtd 04102012 to avail date for all cases
			sSQL = "select work_date from work_calendar where work_date < ? and working='N' order by work_date desc ";
			pstmt=conn.prepareStatement(sSQL);
			pstmt.setTimestamp(1,date1);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				date2=rs.getTimestamp(1);
				wDate=new java.sql.Date(date2.getTime());
				if(wDate.before(todayDate))
				{
					System.out.println("days-----"+days);
					//days=(int) (todayDate.getTime() - wDate.getTime())/(1000 * 60 * 60 * 24);
					days = (int)countDaysBetween(wDate,todayDate);
					System.out.println("days-----"+days);
					if(days==1)
					{
						todayDate=wDate;
						totHdays++;
					}
				}
			}
			if (pstmt != null) 
			{
				pstmt.close();
				pstmt = null;
			}

			if (rs != null) 
			{
				rs.close();
				rs = null;
			}
			System.out.println("totHdays---"+totHdays);
			System.out.println("days-----"+days);
			date2.setTime(date1.getTime() - (totHdays*(1000 * 60 * 60 * 24)));

			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"))
				{
					isEmpty = checkNull(genericUtility.getColumnValue("isempty", dom));
					locCode = genericUtility.getColumnValue("loc_code", dom1);
					System.out.println("locCode---"+locCode);
					System.out.println("isempty h = "+isEmpty);
					currDate = new java.util.Date();
					sdf = new SimpleDateFormat(applDateFormat);
					currDateStr = sdf.format(currDate);
					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;
					}	
					/*

					sql = "SELECT TRAN_ID FROM PHYSCAN WHERE TRAN_DATE = ?";
					pstmt = conn.prepareStatement( sql );
					pstmt.setTimestamp(1, date1);						
					rs = pstmt.executeQuery();	
					if( rs.next() )
					{
						tranId = rs.getString("TRAN_ID");
						valueXmlString.append("<Detail1 domID='1' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");
						valueXmlString.append( "<tran_id><![CDATA[" ).append( tranId ).append( "]]></tran_id>\r\n" );
					}
					else
					{
						valueXmlString.append("<Detail1 domID='1' selected=\"N\">\r\n");
						valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\" />\r\n");
					}
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}
					 */	
					sSQL = "SELECT  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 , M.CYCLE_CRITERIA, M.ANALYSIS_CLASS"
						+ " FROM STOCK S,ITEM M,LOCATION L "
						+ " WHERE  S.ITEM_CODE = M.ITEM_CODE AND (S.QUANTITY > 0 ) AND S.LOC_CODE = L.LOC_CODE "
						+ " AND L.LOC_CODE = ? AND S.SITE_CODE = ?  ORDER BY S.LOC_CODE";
	
					System.out.println("** sSQL **[" + sSQL + "]");
					pstmt = conn.prepareStatement(sSQL);
					//pstmt.setTimestamp(1,date2);
					pstmt.setString(1,locCode);
					pstmt.setString(2, siteCode);//added by Kunal on 18/09/12
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
					isStock="Y";	
					}
					else 
					{
						isStock="N";
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
					System.out.println("Checking for location Stock availability isStock---"+isStock);
					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" ));
					valueXmlString.append("<confirmed protect=\"1\">").append("<![CDATA[N]]>").append("</confirmed>");
					valueXmlString.append( "<tran_date><![CDATA[" ).append( currDateStr ).append( "]]></tran_date>\r\n" );
					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" );

					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_type><![CDATA[" ).append("M").append( "]]></tran_type>\r\n" );
					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( "<loc_code><![CDATA[" ).append( locCode ).append( "]]></loc_code>\r\n" );
					valueXmlString.append( "<isstock><![CDATA[" ).append( isStock).append( "]]></isstock>\r\n" );
					valueXmlString.append( "<isempty><![CDATA[" ).append( isEmpty).append( "]]></isempty>\r\n" );
					if("Y".equalsIgnoreCase(isStock))
					{
						valueXmlString.append( "<remarks protect = \"1\"><![CDATA[" ).append( isEmpty).append( "]]></remarks>\r\n" );
					}
					

				}

				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+"]");
				tranId = genericUtility.getColumnValue("tran_id", dom1);
				System.out.println("tran Id in Detail --->["+tranId+"]");
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{	
					isEmpty = checkNull(genericUtility.getColumnValue("isempty", dom2)).trim();
					isStock = checkNull(genericUtility.getColumnValue("isstock", dom2)).trim();
					System.out.println("isempty d = "+isEmpty);
					System.out.println("isStock d = "+isStock);
					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)));
					if(("Y".equalsIgnoreCase(isEmpty) && "N".equalsIgnoreCase(isStock)) || ("N".equalsIgnoreCase(isEmpty) && "N".equalsIgnoreCase(isStock) ) )
					{
						valueXmlString.append("<Detail3 domID='1' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
						valueXmlString.append("<tran_id/>"); 
						valueXmlString.append("<line_no>").append("1").append("</line_no>\r\n");		
						//valueXmlString.append("<item_code>").append(checkNullAndTrim(itemCode)).append("</item_code>\r\n");
						//valueXmlString.append("<descr>").append(itemDescr).append("</descr>\r\n");
						//change done by kunal on 28/01/13 add CDATA
						valueXmlString.append("<site_code><![CDATA[").append(genericUtility.getColumnValueFromNode("site_code", dom2.getElementsByTagName("Detail2").item(0))).append("]]></site_code>\r\n");
						valueXmlString.append("<loc_code><![CDATA[").append(checkNullAndTrim(genericUtility.getColumnValue("loc_code", dom1))).append("]]></loc_code>\r\n");
						//valueXmlString.append("<location_descr>").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("0").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("<priority_seq>").append(prioritySeq+1).append("</priority_seq>\r\n");
						valueXmlString.append("<emp_code__user><![CDATA[").append(genericUtility.getColumnValueFromNode("emp_code__user", dom2.getElementsByTagName("Detail2").item(0))).append("]]></emp_code__user>\r\n"); //change done by Kunal on 14/09/12
						//valueXmlString.append("<emp_code__user>").append(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" )).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("").append("</phy_input>\r\n");
						//valueXmlString.append("<conv_fact>").append(checkNull(phyHanUOM)).append("</conv_fact>\r\n");
						
						valueXmlString.append("</Detail3>\r\n");
						
						/*
						tranId=generateTranId("w_phy_item_mscan",conn);
						sql="insert into physcan (tran_id,tran_date,site_code,chg_date,chg_user,chg_term) values(?,?,?,?,?,?)";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,tranId);
						pstmt.setTimestamp(2,date1);
						pstmt.setString(3,genericUtility.getColumnValue("site_code", dom1));
						pstmt.setTimestamp(4,date1);
						pstmt.setString(5,genericUtility.getColumnValue("chg_user", dom1));
						pstmt.setString(6,genericUtility.getColumnValue("chg_term", dom1));
						pstmt.executeUpdate();
						pstmt.close();
						pstmt=null;
						
						sql = "insert into PHYSCANDET (TRAN_ID,LINE_NO,SITE_CODE,LOC_CODE,QUANTITY," +
								"EMP_CODE__USER) " +
						"values(?,?,?,?,?,?)";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranId);
						pstmt.setInt(2,1);
						pstmt.setString(3,genericUtility.getColumnValue("site_code", dom1));
						pstmt.setString(4,genericUtility.getColumnValue("loc_code", dom1));
						pstmt.setDouble(5,0);
						pstmt.setString(6,genericUtility.getColumnValue("chg_user", dom1));
						pstmt.executeUpdate();
						pstmt.close();
						pstmt=null;
						conn.commit();
						*/
					}
					if((("N".equalsIgnoreCase(isEmpty)&& "Y".equalsIgnoreCase(isStock)) || ("Y".equalsIgnoreCase(isEmpty) && "Y".equalsIgnoreCase(isStock))) )
					{

//						sSQL = "select work_date from work_calendar where work_date < ? and working='N' order by work_date desc ";
//						pstmt=conn.prepareStatement(sSQL);
//						pstmt.setTimestamp(1,date1);
//						rs=pstmt.executeQuery();
//						while(rs.next())
//						{
//							date2=rs.getTimestamp(1);
//							wDate=new java.sql.Date(date2.getTime());
//							if(wDate.before(todayDate))
//							{
//								System.out.println("days-----"+days);
//								//days=(int) (todayDate.getTime() - wDate.getTime())/(1000 * 60 * 60 * 24);
//								days = (int)countDaysBetween(wDate,todayDate);
//								System.out.println("days-----"+days);
//								if(days==1)
//								{
//									todayDate=wDate;
//									totHdays++;
//								}
//							}
//						}
//						if (pstmt != null) 
//						{
//							pstmt.close();
//							pstmt = null;
//						}
//
//						if (rs != null) 
//						{
//							rs.close();
//							rs = null;
//						}
//						System.out.println("totHdays---"+totHdays);
//						System.out.println("days-----"+days);
//						date2.setTime(date1.getTime() - (totHdays*(1000 * 60 * 60 * 24)));
						/*if (tranId != null && tranId.trim().length() != 0) 
					{
						sql = "SELECT count(*) FROM PHYSCANDET WHERE TRAN_ID = ? AND LOC_CODE = ? ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, tranId );					
						pstmt.setString( 2, genericUtility.getColumnValue("loc_code", dom1) );						
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							cnt = rs.getInt(1);
						}
						if(rs != null)
						{	
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}
						sql = "SELECT MAX(LINE_NO),MAX(PRIORITY_SEQ) FROM PHYSCANDET WHERE TRAN_ID = ?";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, tranId );						
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							lineNo = rs.getInt(1);
							prioritySeq = rs.getInt(2);
						}
						if(rs != null)
						{	
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}	
						if(cnt > 0)
						{
							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"
									+ " 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 S.LOC_CODE=? ORDER BY LINE_NO";

							System.out.println("** sSQL **[" + sSQL + "]");
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setString(1,tranId);
							pstmt.setString(2,genericUtility.getColumnValue("loc_code", dom1));
							rs = pstmt.executeQuery();
							System.out.println("Rs size ["+rs.getRow()+"]");
						}
						else
						{
							sSQL = "SELECT 'S' STATUS, 0 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 , M.CYCLE_CRITERIA, M.ANALYSIS_CLASS "
									+ " FROM STOCK S,ITEM M,LOCATION L "
									+ " WHERE  S.ITEM_CODE = M.ITEM_CODE AND (S.QUANTITY > 0 OR (S.QUANTITY=0 AND S.LTRAN_DATE = ?)) AND S.LOC_CODE = L.LOC_CODE "
									+ " AND L.LOC_CODE = ?  and s.site_code =  ? ORDER BY S.LOC_CODE  ";

							System.out.println("** sSQL **[" + sSQL + "]");
							pstmt = conn.prepareStatement(sSQL);
							pstmt.setTimestamp(1,date2);
							pstmt.setString(2,genericUtility.getColumnValue("loc_code", dom1));
							pstmt.setString(3, loginSiteCode);//added by Kunal on 18/09/12
							rs = pstmt.executeQuery();
						}
						if(rs.next())
						{
							if(rs.getInt(2) ==0)
							{
								lineNo++;
							}
							else
							{
								lineNo = rs.getInt(2);
							}
							System.out.println("Line_no["+lineNo+"]");
							status = rs.getString("STATUS");
							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 = rs.getString("CYCLE_CRITERIA");
							analysisClass = rs.getString("ANALYSIS_CLASS");
							System.out.println("phyHanBasis - >[" + phyHanBasis	+ "] phyHanUOM [" + phyHanUOM + "]");

							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+"]");

							if("Y".equalsIgnoreCase(status))
							{
								valueXmlString.append("<Detail2 domID='" + lineNo+ "' selected=\"Y\">\r\n");
								valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
								valueXmlString.append("<status>").append(status).append("</status>\r\n");
								valueXmlString.append("<priority_seq>").append(prioritySeq).append("</priority_seq>\r\n");
							}
							else if("S".equalsIgnoreCase(status))
							{
								valueXmlString.append("<Detail2 domID='" + lineNo+ "' selected=\"N\">\r\n");
								valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
								valueXmlString.append("<priority_seq>").append(prioritySeq+1).append("</priority_seq>\r\n");
							}
							else
							{
								valueXmlString.append("<Detail2 domID='" + lineNo+ "' selected=\"N\">\r\n");
								valueXmlString.append("<attribute selected=\"Y\" updateFlag=\"E\" status=\"O\" pkNames=\"\"/>\r\n");
								valueXmlString.append("<status>").append("N").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");
							valueXmlString.append("<descr>").append(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("<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("").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("</Detail2>\r\n");
						}
						if (pstmt != null) 
						{
							pstmt.close();
							pstmt = null;
						}

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


						System.out.println(genericUtility.getColumnValue("loc_code", dom1));
						sSQL = "SELECT  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 , M.CYCLE_CRITERIA, M.ANALYSIS_CLASS"
								+ " FROM STOCK S,ITEM M,LOCATION L "
								//+ " WHERE  S.ITEM_CODE = M.ITEM_CODE AND (S.QUANTITY > 0 OR (S.QUANTITY=0 AND S.LTRAN_DATE = ?)) AND S.LOC_CODE = L.LOC_CODE "
								+ " WHERE  S.ITEM_CODE = M.ITEM_CODE AND (S.QUANTITY > 0 ) AND S.LOC_CODE = L.LOC_CODE "
								+ " AND L.LOC_CODE = ? AND S.SITE_CODE = ?  ORDER BY S.LOC_CODE";

						System.out.println("** sSQL **[" + sSQL + "]");
						pstmt = conn.prepareStatement(sSQL);
						//pstmt.setTimestamp(1,date2);
						pstmt.setString(1,genericUtility.getColumnValue("loc_code", dom1));
						pstmt.setString(2, loginSiteCode);//added by Kunal on 18/09/12
						rs = pstmt.executeQuery();
						while (rs.next()) 
						{
							lineNo++;
							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 = rs.getString("CYCLE_CRITERIA");
							analysisClass = rs.getString("ANALYSIS_CLASS");
							System.out.println("phyHanBasis - >[" + phyHanBasis	+ "] phyHanUOM [" + phyHanUOM + "]");

							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+"]");

							
							if("N".equalsIgnoreCase(isEmpty)&& "Y".equalsIgnoreCase(isStock))
								valueXmlString.append("<Detail3 domID='" + lineNo+ "' selected=\"N\">\r\n");
								
							if("Y".equalsIgnoreCase(isEmpty) && "Y".equalsIgnoreCase(isStock)) 
							{
								valueXmlString.append("<Detail3 domID='" + lineNo+ "' selected=\"Y\">\r\n");
								 
							}
							
							valueXmlString.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>\r\n");
							if("Y".equalsIgnoreCase(isEmpty) && "Y".equalsIgnoreCase(isStock)) 
							{
								
								valueXmlString.append("<tran_id/>"); 
								valueXmlString.append("<status>").append("Y").append("</status>\r\n");	
							}
							valueXmlString.append("<line_no>").append(lineNo).append("</line_no>\r\n");									
							valueXmlString.append("<item_code><![CDATA[").append(checkNullAndTrim(itemCode)).append("]]></item_code>\r\n");
							//Changed by sankara on 10/19/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><![CDATA[").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(checkNullAndTrim(locDescr)).append("]]></location_descr>\r\n");
							valueXmlString.append("<lot_no><![CDATA[").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><![CDATA[").append(unit).append("]]></unit>\r\n");														
							valueXmlString.append("<qty_physical>").append(("0")).append("</qty_physical>\r\n");
							valueXmlString.append("<phy_han_basis><![CDATA[").append(checkNull(phyHanBasis)).append("]]></phy_han_basis>\r\n");
							valueXmlString.append("<phy_input_scan><![CDATA[").append(phyHanBasisDesc).append("]]></phy_input_scan>\r\n");
							valueXmlString.append("<priority_seq>").append(prioritySeq+1).append("</priority_seq>\r\n");
							valueXmlString.append("<emp_code__user><![CDATA[").append(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" )).append("]]></emp_code__user>\r\n"); //change done by Kunal on 14/09/12
							//valueXmlString.append("<emp_code__user>").append(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" )).append("</emp_code__user>\r\n");
							valueXmlString.append("<analysis_class><![CDATA[").append(analysisClass).append("]]></analysis_class>\r\n");
							valueXmlString.append("<cycle_criteria><![CDATA[").append(cycleCriteria).append("]]></cycle_criteria>\r\n");
							valueXmlString.append("<phy_input>").append("").append("</phy_input>\r\n");
							if( "U".equalsIgnoreCase(phyHanBasis))
							{
								valueXmlString.append("<conv_fact><![CDATA[").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());
			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 boolean isInteger( String input )  
	{  
		try  
		{  
			System.out.println(" Value of input ["+input+"]");
			Integer.parseInt( input );  
			return true;  
		}  
		catch( Exception e)  
		{  
			return false;  
		}  
	}

	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()




}

