/*
 * CaseTransferIC.java
 * Developer - Mahesh Patidar
 * Date - 23/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.text.SimpleDateFormat;

import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless 
public class CaseTransferIC extends ValidatorEJB implements CaseTransferICLocal, CaseTransferICRemote
{
	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, pstmt1 = null;
		ResultSet rs = null , rs1 = null;
		String sql = "" ;
		String userId = "";
		String pickOrder = "";
		String ptcn = "";
		String ptcn1 = "";
		int currentFormNo=0;
		int childNodeListLength;
        ConnDriver connDriver = new ConnDriver();
		String columnValue ="";
		String palletNo = "";

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
				{	
					parentNodeList = dom.getElementsByTagName("Detail1");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
					    childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						System.out.println(" columnName [" + childNodeName + "] columnValue [" + columnValue + "]");
						if( childNodeName.equalsIgnoreCase( "pallet_no__fr" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("pallet_no__fr","NULLPLTNO",userId);
								break;								
							}
							else
							{	
								palletNo = genericUtility.getColumnValue("pallet_no__fr",dom);
								sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ?";
								 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__fr","INVPLTNOM",userId);
									 break;
								 }
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }

								 //changed by sankara on 06/JUN/14 for changed joins getting ptcn from despatch query start.
								 /*sql = "SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID IN (SELECT DESP_ID FROM DESPATCH WHERE CONFIRMED != 'Y') ";
								 pstmt = conn.prepareStatement(sql);
								 rs = pstmt.executeQuery();
								 while( rs.next() )
								 { */
								 sql = " SELECT D.PTCN AS PTCN FROM DESPATCH D, DESPATCHDET DT WHERE D.DESP_ID = DT.DESP_ID AND D.CONFIRMED <> 'Y' AND DT.PALLET_NO = ? ";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 rs = pstmt.executeQuery();
								//changed by sankara on 06/JUN/14 for changed joins getting ptcn from despatch query end.	 
								 if( rs.next() )
								 {
									ptcn = checkNull(rs.getString("PTCN"));
									sql = "SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE PICK_ORDER IN (SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN = ?) AND PALLET_NO = ?";
									 pstmt1 = conn.prepareStatement(sql);
									 pstmt1.setString(1,ptcn);
									 pstmt1.setString(2,palletNo);
									 rs1 = pstmt1.executeQuery();
									 if( rs1.next() )
									 {
										 if(count == 0 )
										 	count = rs1.getInt("COUNT");
									 }
									 pstmt1.close();
									 pstmt1 = null;									 
									 rs1.close();
									 rs1 = null;
								 }
								 pstmt.close();
								 pstmt = null;									 
								 rs.close();
								 rs = null;
								 
								 if( count == 0 )
								 {
									 errString = getErrorString("pallet_no__fr","VTPLTNO",userId);
									 break;
								 }
							 }
					    }
					}
				}
			    break;
				case 2:
					break;
				case 3:
				{
					parentNodeList = dom.getElementsByTagName("Detail3");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					//String palletNoFr = checkNull(genericUtility.getColumnValue("pallet_no__fr", dom, "1" ));
					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__to" ))
						{ 
							if(childNode.getFirstChild() == null)
							{
								errString = getErrorString("pallet_no__to","NULLPLTNO",userId);
								break;								
							}
							else
							{	

								 palletNo = genericUtility.getColumnValue("pallet_no__to",dom);
								 String palletNoFr = genericUtility.getColumnValue("pallet_no__fr",dom);
								 sql = "SELECT COUNT(*) AS COUNT FROM PALLET_NO WHERE PALLET_NO = ?";
								 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__to","INVPLTNOM",userId);
									 break;
								 }
								 if(pstmt != null)
								 {
									 pstmt.close();
									 pstmt = null;									 
								 }
								 
								 if(rs != null)
								 {
									 rs.close();
									 rs = null;
								 }
								 //changed by sankara on 06/JUN/14 comapreing ptcn from despatch start.
								/* pickOrder = genericUtility.getColumnValueFromNode("pick_order",dom2.getElementsByTagName("Detail2").item(0));
								 
								 sql = "SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID = ?";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,pickOrder);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									ptcn = checkNull(rs.getString("PTCN"));
								 }
								 pstmt.close();
								 pstmt = null;									 
								 rs.close();
								 rs = null;
								 
								 pickOrder = "";
								 
								 
								 sql = "SELECT PICK_ORDER FROM PICK_ISS_HDR WHERE PALLET_NO = ?";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									pickOrder = rs.getString("PICK_ORDER");
								 }
								 pstmt.close();
								 pstmt = null;									 
								 rs.close();
								 rs = null;
								 if (pickOrder != null && pickOrder.trim().length() > 0 )
								 {
									 sql = "SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID = ?";
									 pstmt = conn.prepareStatement(sql);
									 pstmt.setString(1,pickOrder);
									 rs = pstmt.executeQuery();
									 if( rs.next() )
									 {
										ptcn1 = checkNull(rs.getString("PTCN"));
										 if(! ptcn.equalsIgnoreCase(ptcn1))
										 {
											 //Changed by sumit on 05/12/12 change message id
											 //errString = getErrorString("pallet_no__to","INVPLTNOM",userId);
											 errString = getErrorString("pallet_no__to","INVPLTDESP",userId);
											 break;
										 }
									 }
									 pstmt.close();
									 pstmt = null;									 
									 rs.close();
									 rs = null;
								}*/
								 sql = " SELECT PTCN FROM DESPATCH WHERE DESP_ID IN (SELECT DESP_ID FROM DESPATCHDET WHERE PALLET_NO = ? ) ";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNoFr);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									 ptcn = checkNull(rs.getString("PTCN"));
								 }
								 pstmt.close();pstmt = null;	
								 rs.close();rs = null;
								 
								 sql = " SELECT PTCN FROM DESPATCH WHERE DESP_ID IN (SELECT DESP_ID FROM DESPATCHDET WHERE PALLET_NO = ? ) ";
								 pstmt = conn.prepareStatement(sql);
								 pstmt.setString(1,palletNo);
								 rs = pstmt.executeQuery();
								 if( rs.next() )
								 {
									 ptcn1 = checkNull(rs.getString("PTCN"));
								 }
								 pstmt.close();pstmt = null;	
								 rs.close();rs = null;
								 
								 if(! ptcn.equalsIgnoreCase(ptcn1))
								 {
									 errString = getErrorString("pallet_no__to","INVPLTDESP",userId);
									 break;
								 }
								 //changed by sankara on 06/JUN/14 comapreing ptcn from despatch 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;
	}
	
	private String checkNull(String string) 
	{
		if(string == null)
		{
			string = "";
		}
		return string;
	}
	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;
	}
	
	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();
	    	SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			currDateStr = sdf.format(currDate);
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			chgTerm = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" ));
			switch(currentFormNo)
			{
				case 1 :
				{
					valueXmlString.append("<Detail1 domID='1' selected=\"N\">\r\n");
					valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\" />\r\n");
				    if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					{
						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>");
						
						sql = "SELECT DESCR FROM SITE WHERE SITE_CODE = ?";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, siteCode );						
						rs = pstmt.executeQuery();	
						if( rs.next() )
						{
							descr = rs.getString("DESCR");
						}
						if(rs != null)
						{	
							rs.close();
							rs = null;
						}
						if(pstmt != null)
						{	
							pstmt.close();
							pstmt = null;
						}	
						valueXmlString.append( "<site_descr><![CDATA[" ).append( checkNull( descr )).append( "]]></site_descr>\r\n" );
						valueXmlString.append( "<chg_user><![CDATA[" ).append( genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" ) ).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:
				  {	  
					  
					  if(currentColumn.trim().equalsIgnoreCase("itm_default"))
					  {
							palletNo = genericUtility.getColumnValue("pallet_no__fr", dom1);
							System.out.println("pallet_no_fr in Detail2 --->["+palletNo+"]");
							//changed by sankara on 06/JUN/14 for chnaged query joins it will take lot of time start.
							/* sql = "SELECT PTCN FROM WAVE_TASK_DET WHERE REF_ID IN (SELECT DESP_ID FROM DESPATCH WHERE CONFIRMED != 'Y')";
							 pstmt = conn.prepareStatement(sql);
							 rs = pstmt.executeQuery();
							 while( rs.next() )
							 {*/
							 sql = " SELECT D.PTCN AS PTCN FROM DESPATCH D, DESPATCHDET DT WHERE D.DESP_ID = DT.DESP_ID AND D.CONFIRMED <> 'Y' AND DT.PALLET_NO = ? ";
							 pstmt = conn.prepareStatement(sql);
							 pstmt.setString(1,palletNo);
							 rs = pstmt.executeQuery();
							 if( rs.next() )
							 {
							//changed by sankara on 06/JUN/14 for chnaged query joins it will take lot of time end.	 
								 ptcn = checkNull(rs.getString("PTCN"));
								 sql = "SELECT A.CARTON_NO, C.PICK_ORDER, C.LINE_NO, C.ITEM_CODE, C.LOT_NO, C.LOT_SL, I.DESCR " +
								 		" FROM CARTON_MASTER A, PICK_ORD_DET C, ITEM I WHERE A.PICK_ORDER = C.PICK_ORDER  " +
								 		" AND C.LINE_NO = A.LINE_NO__PICK AND A.CARTON_TYPE = 'C' " +
								 		" AND A.PALLET_NO =? AND C.ITEM_CODE = I.ITEM_CODE AND " +
								 		" C.PICK_ORDER IN (SELECT T.REF_ID FROM WAVE_TASK_DET T WHERE T.PTCN = ? )";
								pstmt1 = conn.prepareStatement( sql );
								pstmt1.setString( 1, palletNo );
								pstmt1.setString( 2, ptcn );						
								rs1 = pstmt1.executeQuery();	
								while( rs1.next() )
								{
									lineNo++;
									cartonNo = rs1.getString("CARTON_NO");
									pickOrder = rs1.getString("PICK_ORDER");
									lineNoPick = rs1.getInt("LINE_NO");
									itemCode = rs1.getString("ITEM_CODE");
									lotNo = rs1.getString("LOT_NO");
									lotSl = rs1.getString("LOT_SL");
									descr = rs1.getString("DESCR");
									
									valueXmlString.append("<Detail2 domID='"+lineNo+"' selected=\"N\">\r\n");
									valueXmlString.append("<attribute  selected=\"N\" updateFlag=\"A\"  status=\"N\" pkNames=\"\" />\r\n");
									valueXmlString.append( "<carton_no><![CDATA[" ).append( checkNull( cartonNo )).append( "]]></carton_no>\r\n" );
									valueXmlString.append( "<line_no><![CDATA[" ).append(lineNo).append( "]]></line_no>\r\n" );
									valueXmlString.append( "<pick_order><![CDATA[" ).append( checkNull( pickOrder )).append( "]]></pick_order>\r\n" );
									valueXmlString.append( "<line_no__pick><![CDATA[" ).append( lineNoPick).append( "]]></line_no__pick>\r\n" );
									valueXmlString.append( "<item_code><![CDATA[" ).append( checkNull( itemCode )).append( "]]></item_code>\r\n" );
									valueXmlString.append( "<item_descr><![CDATA[" ).append( checkNull( descr )).append( "]]></item_descr>\r\n" );
									valueXmlString.append( "<lot_no><![CDATA[" ).append( checkNull( lotNo )).append( "]]></lot_no>\r\n" );
									valueXmlString.append( "<lot_sl><![CDATA[" ).append( checkNull( lotSl )).append( "]]></lot_sl>\r\n" );
									valueXmlString.append( "<chg_user><![CDATA[" ).append( genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" ) ).append( "]]></chg_user>\r\n" );
									valueXmlString.append( "<chg_date><![CDATA[" ).append( currDateStr ).append( "]]></chg_date>\r\n" );
									valueXmlString.append( "<chg_term><![CDATA[" ).append( chgTerm ).append( "]]></chg_term>\r\n" );
									valueXmlString.append("</Detail2>");	
								}
								if(rs1 != null)
								{	
									rs1.close();
									rs1 = null;
								}
								if(pstmt1 != null)
								{	
									pstmt1.close();
									pstmt1 = null;
								}
							 }
							if(rs != null)
							{	
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{	
								pstmt.close();
								pstmt = null;
							}
					  }
				  }
				  break;
				  case 3:
				  {
					    valueXmlString.append("<Detail3 domID='1' selected=\"Y\">\r\n");
						valueXmlString.append("<attribute  selected=\"Y\" updateFlag=\"E\"  status=\"O\" pkNames=\"\" />\r\n");

						if(currentColumn.trim().equalsIgnoreCase("itm_default"))
						{
					    	palletNo = genericUtility.getColumnValueFromNode("pallet_no__fr", dom2.getElementsByTagName("Detail1").item(0));
					    	System.out.println("Pallet No [ "+palletNo+"]");
					    	valueXmlString.append( "<pallet_no__fr><![CDATA[" ).append( checkNull( palletNo )).append( "]]></pallet_no__fr>\r\n" );
		                }
						valueXmlString.append("</Detail3>");
				  }
				 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();
	}
}
		
		