/*
 * Author:Wasim Ansari
 * Date:10-Mar-15
 * Request ID:D14JKAT004 (Delivery Date Ammendment)
 */

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.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.ejb.Stateless;

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

@Stateless

public class DDPordWizAmdEJB extends ValidatorEJB implements DDPordWizAmdEJBRemote, DDPordWizAmdEJBLocal 
{

	GenericUtility genericUtility = GenericUtility.getInstance();
	@Override
	public String wfValData(String xmlString, String xmlString1,
			String xmlString2, String objContext, String editFlag,
			String xtraParams) throws RemoteException, ITMException
	{
		String errString = "";
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			System.out.println("xmlString:::"+xmlString);
			
			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); 
			}
			
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : Cpp : wfValData(String xmlString) : ==>\n"+e.getMessage());
		}
		
		return(errString);
	}
	
	public String wfValData(Document dom, Document dom1, Document dom2,	String objContext, String editFlag, String xtraParams)throws RemoteException, ITMException
	{
		String errString = "";
		
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>"); 
		
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		ResultSet rs = null;
		PreparedStatement pstmt = null ;
		
		String userId = "";
		int currentFormNo = 0;
		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int childNodeListLength;
		int ctr = 0;
		long cnt = 0;
		String childNodeName = null;
		
		String errCode = "";
		String errorType = "";
		String status = "";
		String confirmed = "";
		
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		
	     try
		{
			conn = connDriver.getConnectDB("DriverITM");	
			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 (childNodeName.equalsIgnoreCase("purc_order")) 
						{
							int paramName = 0;
							String purc_order = genericUtility.getColumnValue("purc_order", dom);
							
							/*if(purc_order != null)
							{
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(PURC_ORDER) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, purc_order);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName == 0) 
								{
									errCode = "VTPONOTPRE";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}*/
							if(purc_order != null)
							{
								String countSerSql = "	select status, confirmed from porder where purc_order = ? ";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, purc_order);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								int counter=0;
								if(rsCnt.next())
								{   
									counter++;
									status = rsCnt.getString("status");
									confirmed = rsCnt.getString("confirmed");
								}
								rsCnt.close();
								rs = null;
								pstmtParamName.close(); 
								pstmt = null;
	
								if( counter == 0 )
								{
									errCode = "VTPORD1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								} 
								else if( "C".equalsIgnoreCase(status) || "X".equalsIgnoreCase(status))
								{
									errCode = "VTCLOSE1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}	
								else if (!("Y".equalsIgnoreCase(confirmed)))
								{
									errCode = "VTNOTCONF";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}	
						}
						
						else if (childNodeName.equalsIgnoreCase("supp_code")) 
						{
							int paramName = 0;
							String supp_code = genericUtility.getColumnValue("supp_code", dom);
							
							if(supp_code != null)
							{
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM SUPPLIER WHERE TRIM(SUPP_CODE) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, supp_code);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName == 0) 
								{
									errCode = "VMSUPPCDX";
									//errCode = "VPOERR";    
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								else
								{
									countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(SUPP_CODE) = ?";
									pstmtParamName = conn.prepareStatement(countSerSql);
									pstmtParamName.setString(1, supp_code);
									rsCnt = pstmtParamName.executeQuery();
									
									System.out.println("countSerSql" + countSerSql);
									if (rsCnt.next()) 
									{
										paramName = rsCnt.getInt("COUNT");
									}
									
									if (paramName == 0) 
									{
										//errCode = "VMSUPPCDX";
										errCode = "VPOERR";    
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							
							}	
						}
						
						else if (childNodeName.equalsIgnoreCase("item_parnt")) 
						{
							int paramName = 0;
							String item_parnt = genericUtility.getColumnValue("item_parnt", dom);
							
							if(item_parnt != null)
							{	
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM ITEM WHERE TRIM(ITEM_PARNT) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, item_parnt);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName == 0) 
								{
									errCode = "VTINITEM";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								else
								{
									countSerSql = "select COUNT(1) AS COUNT from porddet where item_code in"
											+ " (select item_code from item where item_parnt = ? ) and status !='C' ";
									pstmtParamName = conn.prepareStatement(countSerSql);
									pstmtParamName.setString(1, item_parnt);
									rsCnt = pstmtParamName.executeQuery();
									
									System.out.println("countSerSql" + countSerSql);
									if (rsCnt.next()) 
									{
										paramName = rsCnt.getInt("COUNT");
									}
									
									if (paramName == 0) 
									{
										errCode = "VPOERR";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							}	
						}
					}
					
				break;	
			
				case 2:
						String dlvDateStr = "";
						String ordDateStr = "";
						String purcOrder = "";
						Timestamp dlvDate = null;
						Timestamp ordDate = null;
						
						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(childNodeName.equalsIgnoreCase("dlv_date") )
							{
								dlvDateStr  = checkNullAndTrim((genericUtility.getColumnValue("dlv_date", dom1)));
								purcOrder   = checkNullAndTrim((genericUtility.getColumnValue("purc_order", dom1)));
							  //ordDateStr  = checkNullAndTrim((genericUtility.getColumnValue("ord_date", dom1)));
								
								String countSerSql = "select ord_date from porder where purc_order = ? ";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, purcOrder);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
		
								if(rsCnt.next())
								{   
									ordDateStr = checkNullAndTrim(rsCnt.getString("ord_date"));
								}
								rsCnt.close();
								rs = null;
								pstmtParamName.close(); 
								pstmt = null;
								
								
								if (( dlvDateStr != null && dlvDateStr.length() > 0 ) && ( ordDateStr != null && ordDateStr.length() > 0 )) 
								{
									dlvDate= Timestamp.valueOf(genericUtility.getValidDateString(dlvDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
									ordDate= Timestamp.valueOf(genericUtility.getValidDateString(ordDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
		
									if (dlvDate.before(ordDate))
									{
										errCode = "VTPODLVDT";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
							}
							
						}
					
					break;
				}
					
			int errListSize = errList.size();
			cnt = 0;
			String errFldName = null;
			if(errList != null && errListSize > 0)
			{
				for(cnt = 0; cnt < errListSize; cnt ++)
				{
					errCode = errList.get((int) cnt);
					errFldName = errFields.get((int) cnt);
					errString = getErrorString(errFldName, 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("");
			}
			
			
		}//end of try
		
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				connDriver = null;
				if(rs != null) 
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null) 
				{
					pstmt.close();
					pstmt = null;

				}
				if (conn != null && !conn.isClosed()) 
				{					
					conn.close();
					conn = null;
				}
				
			} 
			catch(Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
		
		errString = errStringXml.toString();
		return errString;  
	}
	
	public String itemChanged(String xmlString, String xmlString1,
			String xmlString2, String objContext, String currentColumn,
			String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom1 = null;
		Document dom = null;
		Document dom2 = null;
		String valueXmlString = "";
		String errString="";
		
		try
		{
			System.out.println("xmlString["+xmlString+"]");
			System.out.println("xmlString1["+xmlString1+"]");
			System.out.println("xmlString2["+xmlString2+"]");
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("xmlString["+xmlString+"]");
				
				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 :DDProductWizEJB:itemChanged(String,String,String,String,String,String):" + e.getMessage() + ":");
			valueXmlString = genericUtility.createErrorString(e);
			e.printStackTrace();
		}
		
		return valueXmlString;
	}

	@Override
	public String itemChanged(Document dom, Document dom1, Document dom2,String objContext, String currentColumn, String editFlag,String xtraParams) throws RemoteException, ITMException
	{
		String errString="";
		
		int currentFormNo = 0;
		
		StringBuffer valueXmlString = new StringBuffer();
		
		String userId = "";
		String currDate = "";
		
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null ;
		Connection conn = null;
		
		ConnDriver connDriver = new ConnDriver();
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			
			if(objContext != null && objContext.trim().length() > 0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?>\r\n<Root>\r\n<header>\r\n<editFlag>");
			valueXmlString.append(editFlag).append("</editFlag>\r\n</header>\r\n");
			
			//SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			//currDate = sdf.format(new java.util.Date());
			
			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			String chgTerm = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "chgTerm" );
			String siteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );
			String empCode =  genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
			
			System.out.println("currentColumn["+currentColumn+"]");
			System.out.println("objContext["+objContext+"]");
			
					
			switch(currentFormNo)
			{
				case 1: 
				{	
					System.out.println("INSIDE CASE1");
					
					valueXmlString.append("<Detail1 dbID='1' selected = 'N'>\r\n");
					
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
					{
						valueXmlString.append("<supp_code>").append("<![CDATA[]]>").append("</supp_code>\r\n");
						valueXmlString.append("<purc_order>").append("<![CDATA[]]>").append("</purc_order>\r\n");
						valueXmlString.append("<item_parnt>").append("<![CDATA[]]>").append("</item_parnt>\r\n");
						valueXmlString.append("<site_code>").append("<![CDATA["+siteCode+"]]>").append("</site_code>\r\n");
					}
					
					valueXmlString.append("</Detail1>\r\n");
					break;
				}
				case 2:
				{
					String suppCode 		= genericUtility.getColumnValue("supp_code", dom1);
					String purcOrder 	    = genericUtility.getColumnValue("purc_order",dom1);
					String itemParnt 	    = genericUtility.getColumnValue("item_parnt",dom1);
					siteCode 	   		    = genericUtility.getColumnValue("site_code",dom1);
					
					String quantity = "";
					Date dlvDate = new Date();
					String itemCode = "";
					String lineNumber = "";
					
					//valueXmlString.append("<Detail2 domID='"+1+"' selected = 'N'>\r\n");
					
					/*if( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
					{
						valueXmlString.append("<supp_code>").append("<![CDATA["+ checkNullAndTrim(suppCode) +"]]>").append("</supp_code>\r\n");
						valueXmlString.append("<purc_order>").append("<![CDATA["+ checkNullAndTrim(purcOrder) +"]]>").append("</purc_order>\r\n");
						valueXmlString.append("<item_parnt>").append("<![CDATA["+ checkNullAndTrim(itemParnt) +"]]>").append("</item_parnt>\r\n");
						valueXmlString.append("<emp_code>").append("<![CDATA["+ checkNullAndTrim(empCode) +"]]>").append("</emp_code>\r\n");
					}*/
					
					//sql = "select item_code,quantity,dlv_date from porddet where purc_order = ? ";
					if(purcOrder != null)
					{	
						//sql = "select item_code,quantity,dlv_date from porddet where item_code in (select item_code from supplieritem where supp_code= ? "+ 
						//		"and item_code in (select item_code from item where item_parnt = ? ) ) and purc_order= ? ";
						sql = "select purc_order,item_code,quantity,dlv_date,line_no from porddet where purc_order= ? and status !='C' ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,purcOrder);
						rs = pstmt.executeQuery();
					}	
					
					else if(suppCode != null && itemParnt != null)
					{
						sql = "select purc_order,item_code,quantity,dlv_date,line_no from porddet where item_code in (select item_code from supplieritem where supp_code= ? "+ 
								"and item_code in (select item_code from item where item_parnt = ? ) ) and status !='C' ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,suppCode);
						pstmt.setString(2,itemParnt);
						rs = pstmt.executeQuery();
					}
					
					else if(suppCode != null && itemParnt == null)
					{
						sql = "select purc_order,item_code,quantity,dlv_date,line_no from porddet where purc_order in "
								+ "(select purc_order from porder where supp_code = ? ) and status !='C' ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,suppCode);
						rs = pstmt.executeQuery();
					}
					
					else if(itemParnt != null && suppCode == null)
					{
						sql = "select purc_order,item_code,quantity,dlv_date,line_no from porddet where item_code in "
								+ "(select item_code from item where item_parnt = ? ) and status !='C' ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,itemParnt);
						rs = pstmt.executeQuery();
					}
					
					int num = 1;
					while (rs.next())
					{
						purcOrder = checkNullAndTrim(rs.getString("purc_order"));
						itemCode = checkNullAndTrim(rs.getString("item_code"));
						quantity = checkNullAndTrim(rs.getString("quantity"));
						dlvDate  = rs.getDate("dlv_date");
						lineNumber = checkNullAndTrim(rs.getString("line_no"));
						
						SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yy");
						String date = simpleDateFormat.format(dlvDate);
								
						valueXmlString.append("<Detail2 domID='"+num+"' selected = 'N'>\r\n");
						
						valueXmlString.append("<purc_order>").append("<![CDATA["+ checkNullAndTrim(purcOrder) +"]]>").append("</purc_order>\r\n");
						valueXmlString.append("<supp_code>").append("<![CDATA["+ checkNullAndTrim(suppCode) +"]]>").append("</supp_code>\r\n");
						valueXmlString.append("<item_code>").append("<![CDATA["+ checkNullAndTrim(itemCode) +"]]>").append("</item_code>\r\n");
						valueXmlString.append("<quantity>").append("<![CDATA["+ checkNullAndTrim(quantity) +"]]>").append("</quantity>\r\n");
						valueXmlString.append("<dlv_date>").append("<![CDATA["+ date +"]]>").append("</dlv_date>\r\n");
						valueXmlString.append("<line_no>").append("<![CDATA["+ checkNullAndTrim(lineNumber) +"]]>").append("</line_no>\r\n");
						valueXmlString.append("<emp_code>").append("<![CDATA["+ checkNullAndTrim(empCode) +"]]>").append("</emp_code>\r\n");
						valueXmlString.append("<extra1>").append("<![CDATA["+ checkNullAndTrim(userId) +"]]>").append("</extra1>\r\n");
						valueXmlString.append("<site_code>").append("<![CDATA["+ checkNullAndTrim(siteCode) +"]]>").append("</site_code>\r\n");
						
						valueXmlString.append("</Detail2>\r\n");
						
						num++;
					}
					
					if (rs != null) 
					{					
						rs.close();
						rs = null;
					}
					if (pstmt != null ) 
					{					
						pstmt.close();
						pstmt = null;
					}
					if (conn != null && !conn.isClosed()) 
					{					
						conn.close();
						conn = null;
					}
					
					break;	
				}
			}
			
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if (conn != null && !conn.isClosed()) 
				{					
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		valueXmlString.append( "</Root>\r\n" );	
		System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
		
		return valueXmlString.toString();
	}
	
	private static String checkNullAndTrim(String input)
	{
		if (input==null)
		{
			input="";
		}
		return input.trim();
	}
	
	private String errorType(Connection conn , String errorCode)
	{
		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();
		}		
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
}
