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 PhyItemVerifyIC extends ValidatorEJB implements PhyItemVerifyICLocal, PhyItemVerifyICRemote
{
	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;
		double  quantityInput=0;
		String phyHanBasis="",unit="",toUnit="",mrfPartNo="";
		String sqlForCase="";
		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 reverify="";
		ArrayList errList = new ArrayList();
		ArrayList errFields = new ArrayList();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		String errFieldName = "";
		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 2:
			{
				parentNodeList = dom.getElementsByTagName("Detail2");
				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)
						{
							errList.add( "NULLITCODE" );
							errFields.add( childNodeName.toLowerCase() );
						}
						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)
							{
								errList.add( "VMITEM1" );
								errFields.add( childNodeName.toLowerCase() );
							}

						
						}
					}
					else if( childNodeName.equalsIgnoreCase( "lot_no" ))
					{
						if(childNode.getFirstChild() == null)
						{
							errList.add( "NULLLOTNO" );
							errFields.add( childNodeName.toLowerCase() );
						}
					}

					else if( childNodeName.equalsIgnoreCase( "lot_sl" ))
					{
						System.out.println("lot sl["+childNode.getNodeValue()+"]");
						if(childNode.getFirstChild() == null)
						{
							errList.add( "VLOT01" );
							errFields.add( childNodeName.toLowerCase() );
						}
					}
					else if(childNodeName.equalsIgnoreCase( "reverify" ))
					{
						reverify = genericUtility.getColumnValue("reverify", dom);
						System.out.println("reverify----"+reverify);
						if(!"N".equals(reverify.trim()) && !"Y".equals(reverify.trim()))
						{
							errList.add( "INVVERIFY" );
							errFields.add( childNodeName.toLowerCase() );
							
						}
					}
					else if( childNodeName.equalsIgnoreCase( "phy_input" ))
					{
						System.out.println("phy_input["+childNode.getNodeValue()+"]");
						
					
						System.out.println("phy input="+checkNull(genericUtility.getColumnValue("phy_input",dom)).trim());


						if( !isInteger( checkNull(genericUtility.getColumnValue("phy_input",dom)).trim()))
						{
							errList.add( "NOTANUMBER" );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							errString=validateQty(dom,dom2,conn,userId);
							System.out.println("errString----"+errString);
							if((errString.trim()).length()>0)
								{
								errList.add( errString );
								errFields.add( childNodeName.toLowerCase() );
								}
							
						}

					}
					
				}
			}
			} 
			int errListSize = errList.size();
			if (errListSize > 0)
			{
				for (int cnt = 0; cnt < errListSize; cnt++)
				{
					errCode = (String)errList.get(cnt);
					errFieldName = (String)errFields.get(cnt);
					errString = getErrorString(errFieldName, errCode, userId);
					errorType = errorType(conn, errCode);

					if (errString.length() > 0)
					{
						String bifurErrString = errString.substring(errString.indexOf("<Errors>") + 8, errString.indexOf("<trace>"));
						bifurErrString = bifurErrString + errString.substring(errString.indexOf("</trace>") + 8, errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						errString = "";
					}

					if (errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				errStringXml.append("</Errors></Root>\r\n");
			}
			else {
				errStringXml = new StringBuffer("");
			}
		}
		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 );
			}
		}
		errString = errStringXml.toString();
		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>");
			
			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)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 1 :
			{	
				siteCode=checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" ));
				System.out.println("siteCode---"+siteCode);
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					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;
					}	
					
				
					System.out.println("Checking for location Stock availability isStock---"+isStock);
					valueXmlString.append("<Detail1 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("</Detail1>");
			}	
			break;
			case 2 :
			{	  
				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"))
				{	
		
					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=? AND CASE WHEN S.VERIFIED IS NULL THEN 'N' ELSE S.VERIFIED END='N' " +
						" AND CASE WHEN S.REVERIFY IS NULL THEN 'Y' ELSE S.REVERIFY END='Y' ORDER BY PRIORITY_SEQ ASC";	
					pstmt=conn.prepareStatement(sSQL);
					pstmt.setTimestamp(1,date1);
					pstmt.setString(2, loginSiteCode);
					rs=pstmt.executeQuery();
					while(rs.next())
					{
						lineNo++;
						tranId=rs.getString("TRAN_ID");
						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+"]");

						valueXmlString.append("<Detail2 domID='" + lineNo+ "' selected=\"N\">\r\n");
						valueXmlString.append("<tran_id/>"); 
						valueXmlString.append( "<ref_id><![CDATA[" ).append( tranId).append( "]]></ref_id>\r\n" );
						valueXmlString.append( "<ref_line_no><![CDATA[" ).append( rs.getInt("LINE_NO")).append( "]]></ref_line_no>\r\n" );
						valueXmlString.append("<line_no><![CDATA[").append(lineNo).append("]]></line_no>\r\n");									
						valueXmlString.append("<item_code><![CDATA[").append(checkNullAndTrim(itemCode)).append("]]></item_code>\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><![CDATA[").append(quantity).append("]]></quantity>\r\n");														
						valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");														
						valueXmlString.append("<qty_physical><![CDATA[").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><![CDATA[").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("<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><![CDATA[").append("").append("]]></phy_input>\r\n");
						valueXmlString.append("<no_art><![CDATA[").append(Double.parseDouble(quantity)/convFact(itemCode, lotNo, lotSl, locCode, Double.parseDouble(quantity), conn,getValueFromXTRA_PARAMS(xtraParams,"loginCode"),date1)).append("]]></no_art>\r\n");
						/*if( "U".equalsIgnoreCase(phyHanBasis))
						{
							valueXmlString.append("<conv_fact><![CDATA[").append(checkNull(phyHanUOM)).append("]]></conv_fact>\r\n");
						}
						else
						{
							valueXmlString.append("<conv_fact><![CDATA[").append("").append("]]></conv_fact>\r\n");
						}*/
						valueXmlString.append("<status><![CDATA[").append("Y").append("]]></status>\r\n");
						valueXmlString.append("<reverify><![CDATA[").append("N").append("]]></reverify>\r\n");
						valueXmlString.append("</Detail2>\r\n");
					}
					rs.close();
					rs=null;
					pstmt.close();
					pstmt=null;
				}
			}
			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()



private String validateQty(Document dom,Document dom2,Connection conn,String userId)
{
	double quantityTest = 0.0;
	java.util.Date utilDate=new java.util.Date(); 
	SimpleDateFormat sdf =null;
	Timestamp date1 = null;
	System.out.println("tran date="+date1);
	String itemCode="";
	String lotNo="";
	String lotSl="";
	String sSQL="";
	String locCode="";
	PreparedStatement pstmt=null,pstmt1=null;
	ResultSet rs=null,rs2=null;
	String invStat="";
	double quantity=0;
	double quantityInput=0;
	String phyHanBasis="";
	String unit="";
	String toUnit="";
	String sqlForCase="";
	int shipperSize=0;
	String mrfPartNo="";
	double volumn=0, convFact = 1, meffQty = 0;
	String errString="";
	DistCommon discommon = new DistCommon();
	try
	{
		sdf =new SimpleDateFormat(genericUtility.getDBDateFormat());
		date1 =Timestamp.valueOf(sdf.format(utilDate).toString() + " 00:00:00.0");
		date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom2) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
			itemCode = genericUtility.getColumnValue("item_code", dom);
			lotNo = genericUtility.getColumnValue("lot_no", dom);
			lotSl = genericUtility.getColumnValue("lot_sl", dom);
			locCode = genericUtility.getColumnValue("loc_code", dom);
			System.out.println("locCode ="+locCode);
			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("@@@@@ 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();
			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");
		
			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 = ? AND CASE WHEN D.VERIFIED IS NULL THEN 'N' ELSE D.VERIFIED END='N' ";
		
			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"));
				toUnit = checkNull(rs.getString("UNIT"));
				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);
					errString = "SCANTYPE";
					return errString;
		
				}
		
				if("L".equalsIgnoreCase(phyHanBasis))
				{
					if( (shipperSize * quantityInput) != quantity)
					{
						System.out.println(" as per 'L' ");
						//errString = getErrorString("phy_input","NOTMATCH",userId);
						errString="NOTMATCH"; 
						return errString;
					}
				}
		
				else if("I".equalsIgnoreCase(phyHanBasis))
				{
					if( quantityInput != quantity)
					{
						System.out.println(" as per 'I' ");
						//errString = getErrorString("phy_input","NOTMATCH",userId);
						errString="NOTMATCH";
						return errString;
					}
				}
		
				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);
							errString="CONVQTYNFD";
							return errString;
						}
		
						if (volumn == 0)
						{
							volumn= Double.parseDouble(convQtyList.get(0).toString());
						}
		
					}
		
					else
					{
						meffQty = quantityInput ;
						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);
						errString="NOTMATCH";
						return errString;
					}
				}
		
				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);
						errString="NOTMATCH";
						return errString;
					}
				}
			}
		
			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
	}catch(Exception e)
	{
		e.printStackTrace();
	}
	return errString;
}
private double convFact(String itemCode,String lotNo,String lotSl,String locCode,double quantity,Connection conn,String userId,Timestamp date1)
{
	double quantityTest = 0.0;
	java.util.Date utilDate=new java.util.Date(); 
	SimpleDateFormat sdf =null;
	//Timestamp date1 = null;
	System.out.println("tran date="+date1);
	String sSQL="";
	PreparedStatement pstmt=null,pstmt1=null;
	ResultSet rs=null,rs2=null;
	String invStat="";
	double quantityInput=0;
	String phyHanBasis="";
	String unit="";
	String toUnit="";
	String sqlForCase="";
	int shipperSize=0;
	String mrfPartNo="";
	double volumn=0, convFact = 1, meffQty = 0;
	String errString="";
	DistCommon discommon = new DistCommon();
	try
	{
		sdf =new SimpleDateFormat(genericUtility.getDBDateFormat());
		//date1 =Timestamp.valueOf(sdf.format(utilDate).toString() + " 00:00:00.0");
		//date1 = Timestamp.valueOf(genericUtility.getValidDateString( genericUtility.getColumnValue("tran_date", dom2) , genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
			System.out.println("locCode ="+locCode);
			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("@@@@@ quantity validation modified.....");
			//quantity = Double.parseDouble(genericUtility.getColumnValue("quantity", dom) == null ?"0":genericUtility.getColumnValue("quantity", dom)); //chnage done by Kunal on 14/09/12
			
			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");
		
			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 = ? AND CASE WHEN D.VERIFIED IS NULL THEN 'N' ELSE D.VERIFIED END='N' ";
		
			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"));
				toUnit = checkNull(rs.getString("UNIT"));
				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))
				{
					convFact=0;
		
				}
		
				if("L".equalsIgnoreCase(phyHanBasis))
				{
					convFact=shipperSize;
					
				}
		
				else if("I".equalsIgnoreCase(phyHanBasis))
				{
					convFact=1;
				}
		
				else if ("U".equalsIgnoreCase(phyHanBasis))
				{
					System.out.println(" as per 'U' ");
					if(!unit.equalsIgnoreCase(toUnit))
					{
						if(volumn != 0)
						{
							convFact = volumn;
						}
					}
		
					else
					{
						convFact=1;
					}
		
					
				}
		
				else if ("P".equalsIgnoreCase(phyHanBasis))
				{											
					convFact = getPallet(itemCode,lotNo, quantity ,conn );
					
				}
			}
		
			if( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( pstmt != null)
			{
				pstmt.close();
				pstmt = null;
			}
	}catch(Exception e)
	{
		e.printStackTrace();
	}
	return convFact;
}
private double getPallet(String itemCode,String lotNo, double quantity ,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;

	

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




	} catch (SQLException e) {



		e.printStackTrace();

	}		

	return nosOfPallet; 

}
private String errorType( Connection conn , String errorCode ) throws ITMException
{
	String msgType = "";
	PreparedStatement pstmt = null ; 
	ResultSet rs = null;
	try
	{			
		String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";

		pstmt = conn.prepareStatement( sql );			
		pstmt.setString(1, errorCode);			
		rs = pstmt.executeQuery();
		while( rs.next() )
		{
			msgType = rs.getString("MSG_TYPE");
		}			
	}
	catch (Exception ex)
	{
		ex.printStackTrace();
		throw new ITMException(ex);
	}		
	finally
	{
		try
		{
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if ( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
	}		
	return msgType;
}



}


