/*
 * PalletMergeIC.java
 * Developer - Mahesh Patidar
 * Date - 21/08/12 
 */

package ibase.webitm.ejb.wms;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

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

@Stateless 
public class PalletMergeIC extends ValidatorEJB implements PalletMergeICLocal, PalletMergeICRemote
{
	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 = "",sqlCarton = "" ;
		String userId = "";
		int currentFormNo=0;
		int childNodeListLength;
        ConnDriver connDriver = new ConnDriver();
		String columnValue ="";
		String palletNo = "";
		//Changed by sumit on 16/11/12 
		String objName = "";

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
				System.out.println(" sumit : ["+currentFormNo+"]");
			}
			objName = getObjName(dom1);
			switch(currentFormNo)
			{
				case 1 :
				{	
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					//changed by sumit on 16/11/12  getting obj_name for validation.
					//objName = getObjName(dom); 	//childNode.getAttributes().getNamedItem("objName").getNodeValue();
					System.out.println(" obj name ->["+objName+"]");
					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( "pallet_no" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								//Changed by sumit on 16/11/12 differentiate error message as this component is share by two screen start
//								/errString = getErrorString("pallet_no","NULLPLTNO",userId);
								if("pelletized_carton".equalsIgnoreCase(objName))
								{
									errString = getErrorString("pallet_no","NULLCARNO",userId);
								}
								else
								{
									errString = getErrorString("pallet_no","NULLPLTNO",userId);
								}
								//Changed by sumit on 16/11/12 differentiate error message as this component is share by two screen end								
								break;								
							}
							else
							{	
								palletNo = genericUtility.getColumnValue("pallet_no",dom);
								//Changed by sumit on 16/11/12 counting pallet no in case of obj name  pelletized_carton start.
								//sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ?";
								if("pelletized_carton".equalsIgnoreCase(objName))
								{
									sql = "SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE PALLET_NO = ?";
								}
								else
								{
									sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ?";
								}
								//Changed by sumit on 16/11/12 counting pallet no in case of obj name  pelletized_carton start.
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									count = rs.getInt("COUNT");
								 }
								 if( count == 0 )
								 {
									 errString = getErrorString("pallet_no","INVPLTNOM",userId);
									 break;
								 }
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }
								 
								 sql = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE PALLET_NO = ? AND DESP_ID IN (SELECT DESP_ID FROM DESPATCH WHERE CONFIRMED != 'Y')";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									count = rs.getInt("COUNT");
								 }
								 if( count == 0 )
								 {
									 errString = getErrorString("pallet_no","INVTRANSTG",userId);
									 break;
								 }
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }
							 }
					    }
					}
				}
			    break;
				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 ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						
						if( childNodeName.equalsIgnoreCase( "pallet_no__new" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("pallet_no__new","NULLPLTNO",userId);
								break;								
							}
							else
							{	
								 palletNo = genericUtility.getColumnValue("pallet_no__new",dom);
								 String palletNoOld =  genericUtility.getColumnValueFromNode("pallet_no", dom1.getElementsByTagName("Detail1").item(0));
								 System.out.println("Old Pallet No ["+palletNoOld+"]New Pallet No["+palletNo+"]");
								 
								 //Changed by sumit on 13/dec/12 validation for pallet not present in master start.
							     sql = "SELECT PALLET_NO FROM PALLET_NO WHERE PALLET_NO = ? ";								
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);								 
								 rs = pstmt.executeQuery();
								 if( !rs.next() )
								 {
									 errString = getErrorString("pallet_no__new","INVPLTNOM",userId);
									 break;
								 }								 
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }
								 //Changed by sumit on 13/dec/12 validation for pallet not present in master end.			 

								 if("palletmerge".equalsIgnoreCase(objName))
								 {
									 //Changed by sumit on 16/11/12 adding status condition 
									 //sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ?";
									  sql = "SELECT COUNT(*) AS COUNT FROM DESPATCHDET WHERE DESP_ID IN(SELECT DESP_ID " +
									  		" FROM DESPATCHDET WHERE PALLET_NO = ?) AND PALLET_NO = ?";
									  //Changed by sumit on 13/12/12 changing status parameter
									  //sqlCarton =  "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ? AND STATUS = 'A'";
									  sqlCarton =  "SELECT STATUS FROM PALLET_NO WHERE PALLET_NO = ?";
								 }
								 else
								 {
									 sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ? AND STATUS = 'A'";
								 }
								 System.out.println(" sql sumit ->["+sql+"]");
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 if("palletmerge".equalsIgnoreCase(objName))
								 {
									 pstmt.setString(2,palletNoOld);
								 }
								 
								 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 )
								 {
									 if("palletmerge".equalsIgnoreCase(objName))
									 {
										 int cnt = 0;
										 pstmt = conn.prepareStatement(sqlCarton);
										 pstmt.setString(1,palletNo);
										 rs = pstmt.executeQuery();
										 if( rs.next() )
										 {
											 //Changed by sumit on 14/12/12 adding validation in case of status 'U' start
											 //cnt = rs.getInt("COUNT");
											 if("U".equalsIgnoreCase(rs.getString("STATUS")))
											 {
												 errString = getErrorString("pallet_no__new","INVPLTDESP",userId);
												 break;
											 }
											//Changed by sumit on 14/12/12 adding validation in case of status 'U' end
										 }
										 if(pstmt != null)
										 {
											 pstmt.close();
											 pstmt = null;									 
										 }
										 
										 if(rs != null)
										 {
											 rs.close();
											 rs = null;
										 }								
										 
										/*if(cnt == 0 && count == 0)
										 {
											 errString = getErrorString("pallet_no__new","INVPLTDESP",userId);
											 break;
										 }*/										
										
										/*if(cnt == 1 && )
										 {
											 errString = getErrorString("pallet_no__new","INVPLTMST",userId);
											 break;
										 }*/
									 }
									 else
									 {
										 errString = getErrorString("pallet_no__new","INVPLTMST",userId);
										 break;
									 }
									
								 }
								/* if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }*/
							 }
					    }
					}
				}
			} 
		}
		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

	{
		return "";
	}
	
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		return "";
	}
	
	//Changed by sumit on 16/11/12 Getting obj_name from dom start.
	private String getObjName(Document dom) throws ITMException
	{		
		NodeList parentNodeList = null;		
		int parentNodeListLength ;		
		String objName = "";
		try
		{
			
			parentNodeList = dom.getElementsByTagName("Detail1");
			parentNodeListLength = parentNodeList.getLength();				
			for(int row = 0; row < parentNodeListLength; row++)
			{
				Node childNode = parentNodeList.item(row);
				//nodeName = childNode.getNodeName();					
				objName = childNode.getAttributes().getNamedItem("objName").getNodeValue();										
			}				
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return objName;
	}
	//Changed by sumit on 16/11/12 Getting obj_name from dom end.
}
		
		