/*
 * 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 "";
	}
	////Changed by Pragyan on 03-NOV-14
	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); 
			}
			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;
	}
	//Changed by Pragyan on 03-NOV-14
	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 ;
		PreparedStatement pstmt1 = null;
		ResultSet rs1 = null ;
		String sql = "";
		int currentFormNo = 0 ;
		int lineNo = 0 ;
		String siteCode = "";
		String palletNo = "";
		String chgTerm = "";
		String descr = "";
		int lineNoPick = 0;
		String pickOrder = "";
		String ptcn = "";
		String cartonNo = "";
		String itemCode = "",lotNo = "", lotSl = "";
		java.util.Date currDate = null;
		String currDateStr = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			
			String applDateFormat = genericUtility.getApplDateFormat();
	    	currDate = new java.util.Date();
	    				
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			
			switch(currentFormNo)
			{
				case 1 :
				{
					valueXmlString.append("<Detail1 domID='1' selected=\"N\">\r\n");
					valueXmlString.append("</Detail1>");
				  }	
	              break;
				  case 2:
				  {	  
					  valueXmlString.append("<Detail2 domID='1' selected=\"N\">\r\n");
					  valueXmlString.append("</Detail2>");
				  }
				  break;

			} 
			valueXmlString.append("</Root>");
			System.out.println("=================["+valueXmlString+"]");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(pstmt != null)
					{	
						pstmt.close();
						pstmt = null;
					}	
					if(rs != null)
					{	
						rs.close();
						rs = null;
					}	
					if(conn != null)
					{	
						conn.close();
						conn = null;
					}	
				}
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}
	
	//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.
}
		
		