/**
	* @author : Chandni Shah 
	* @Version : 1.0
	* Date :17/02/11
*/

package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.sys.UtilMethods;
import java.util.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; // added for ejb3
import ibase.webitm.ejb.ITMDBAccessEJB;

@Stateless // added for ejb3

public class WoContract extends ValidatorEJB implements WoContractLocal, WoContractRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	String winName = null;
	UtilMethods utilMethods = UtilMethods.getInstance();
	public String wfValData(String xmlString, String xmlString1, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			errString = wfValData(dom,dom1,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		return (errString);
	}
	
	public String wfValData(Document dom, Document dom1, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr=0;
		String childNodeName = null;
		String errString = "" , errCode = "" ;
		Connection conn = null;
		PreparedStatement pstmt = null ;
		PreparedStatement pstmt1 = null ;
		ResultSet rs = null;
		ResultSet rs1 = null;
		String sql = "" ;
		String userId = "";
		String status = "";
		int cnt = 0;
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		String objName = null;
		String siteCode ="",workOrder = "",itemCode = "",contractNo = "",loginSite = "";
		String confirmed="";
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			
			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("site_code"))
					{
						siteCode = genericUtility.getColumnValue("site_code", dom);
						siteCode = siteCode == null ? "" : siteCode.trim();							
						if(siteCode != null && siteCode.trim().length() > 0)
						{
							sql = " select count(*) from site where site_code = ? ";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt =  rs.getInt(1);
								if(cnt == 0) 
								{
									errCode = "VMSITE1"; // Invalid site code.
									errString = itmDBAccessEJB.getErrorString("","VMSITE1",userId);
									break ;
								}									
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						else
						{
							errCode = "VSCTONUL";  //null site code
							errString = itmDBAccessEJB.getErrorString("","VSCTONUL",userId);
							break ;
						}
					}
					if(childNodeName.equalsIgnoreCase("work_order"))
					{
						workOrder = genericUtility.getColumnValue("work_order", dom);
						workOrder = workOrder == null ? "" : workOrder.trim();							
						if(workOrder != null && workOrder.trim().length() > 0)
						{
							sql = " select count(*) from workorder where  work_order = ?  ";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt =  rs.getInt(1);
								if(cnt == 0) 
								{
									errCode = "VTWORD2"; // Invalid work order.
									errString = itmDBAccessEJB.getErrorString("","VTWORD2",userId);
									break ;
								}	
								else
								{
									sql = " select status from workorder where work_order = ?  ";
									pstmt1 =  conn.prepareStatement(sql);
									pstmt1.setString(1,workOrder);
									rs1 = pstmt1.executeQuery();
									if(rs1.next())
									{
										status 	=  rs1.getString(1);
										System.out.println("status of Work order :::::::::"+status);
									}
									if (!("P".equalsIgnoreCase(status)))
									{
										errCode = "VTPEND1"; //Status is not pending
										errString = itmDBAccessEJB.getErrorString("","VTPEND1",userId);
										break ;
									}
									
									rs1.close();
									rs1 = null;
									pstmt1.close();
									pstmt1 = null;
								}
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						else
						{
							errCode = "VTNULLWO"; //null work order
							errString = itmDBAccessEJB.getErrorString("","VTNULLWO",userId);
							break ;
						}
					}
					if(childNodeName.equalsIgnoreCase("item_code"))
					{
						itemCode = genericUtility.getColumnValue("item_code", dom);
						itemCode = itemCode == null ? "" : itemCode.trim();							
						
						if(itemCode != null && itemCode.trim().length() > 0 && !"X".equals(itemCode.trim()))
						{
							sql = " select count(*) from bomdet,workorder "
								+ " where workorder.bom_code = bomdet.bom_code  "
								+ " and case when BOMDET.auto_ord is null then 'N' else BOMDET.auto_ord end = 'Y' "
								+ " and workorder.work_order = ? "
								+ " and bomdet.item_code = ? " ;
							
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,workOrder);
							pstmt.setString(2,itemCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt =  rs.getInt(1);
								if(cnt == 0) 
								{
									errCode = "VMITEM2"; //Item Not Found!
									errString = itmDBAccessEJB.getErrorString("","VMITEM2",userId);
									break ;
								}									
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							System.out.println("Contract_no validation editFlag ["+editFlag + "]");
							//if("A".equalsIgnoreCase(editFlag))
							//{
								sql = " select count(1) from wo_contract where  work_order= ? and item_code = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,workOrder.trim());
								pstmt.setString(2,itemCode.trim());
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt =  rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
							//}
							if (("A".equalsIgnoreCase(editFlag) && cnt > 0 ) || ("E".equalsIgnoreCase(editFlag) && cnt > 1 ) )
							{
								errCode = "VTDUPITEM "; //null item code
								errString = itmDBAccessEJB.getErrorString("","VTDUPITEM",userId);
								break ;
							}			

						}
						// 14/01/14 manoharan allow blank so that for all rubber stereo the sae contract will be used so commented
						else
						{
								sql = " select count(1) from wo_contract where  work_order= ? and item_code = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,workOrder.trim());
								pstmt.setString(2,itemCode.trim());
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt =  rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
							//}
							if (("A".equalsIgnoreCase(editFlag) && cnt > 0 ) || ("E".equalsIgnoreCase(editFlag) && cnt > 1 ) )
							{
								errCode = "VTDUPITEM "; //null item code
								errString = itmDBAccessEJB.getErrorString("","VTDUPITEM ",userId);
								break ;
							}			
						}
					}
					if(childNodeName.equalsIgnoreCase("contract_no"))
					{
						workOrder = genericUtility.getColumnValue("work_order", dom);
						workOrder = workOrder == null ? "" : workOrder.trim();			
						contractNo  = genericUtility.getColumnValue("contract_no", dom);
						contractNo = contractNo == null ? "" : contractNo.trim();	
						
						if(contractNo != null && contractNo.trim().length() > 0)
						{
							
							sql =  " SELECT count(1) "
								+ " FROM WORKORDER,BOMDET,PCONTRACT_HDR,PCONTRACT_DET "
								+ " WHERE WORKORDER.BOM_CODE = BOMDET.BOM_CODE "
								+ " AND BOMDET.ITEM_CODE = PCONTRACT_DET.ITEM_CODE "
								+ " and case when BOMDET.auto_ord is null then 'N' else BOMDET.auto_ord end = 'Y' "
								+ " and workorder.work_order = ? "
								+ " and PCONTRACT_DET.CONTRACT_NO = ? " ;
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,workOrder );
							pstmt.setString(2,contractNo);
							rs = pstmt.executeQuery ();
							if(rs.next())
							{
								cnt =  rs.getInt(1);
								if(cnt == 0) 
								{
									errCode = "VTINVCNTR"; //Invalid contract number
									errString = itmDBAccessEJB.getErrorString("","VTINVCNTR",userId);
									break ;
								}		
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							
							sql = " select confirmed from pcontract_hdr where contract_no = ? ";
							pstmt =  conn.prepareStatement(sql);
							pstmt.setString(1,contractNo);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								confirmed = rs.getString("confirmed");			
							}
							if ("Y".equalsIgnoreCase (confirmed))
							{
								sql = " select count(*) from pcontract_det where contract_no = ? ";
								pstmt =  conn.prepareStatement(sql);
								pstmt.setString(1,contractNo);
								rs = pstmt.executeQuery();		
								if(rs.next())
								{
									cnt =  rs.getInt(1);
									if(cnt == 0) 
									{
										errCode = "VMCONTCD1"; //Invalid Contact Code
										errString = itmDBAccessEJB.getErrorString("","VMCONTCD1",userId);
										break ;
									}									
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;			
								
							}
							else
							{
								errCode = "VTCONCONF"; //Unconfirmed Contract No. 
								errString = itmDBAccessEJB.getErrorString("","VTCONCONF",userId);
								break ;
							}
						}
						else
						{
							errCode = "VTCNTNNL"; //null Contract No
							errString = itmDBAccessEJB.getErrorString("","VTCNTNNL",userId);
							break ;
						}
					}
				} // end for
				break;					
			} //END switch
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
			errString=e.getMessage();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(rs != null )rs.close();
					rs = null;
					if(pstmt != null )pstmt.close();
					pstmt =null;
					conn.close();
				}
				conn = null;
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		System.out.println("ErrString ::"+errString);
		return errString;
	}//END OF VALIDATION
	
	public String itemChanged(String xmlString, String xmlString1, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			valueXmlString = itemChanged(dom,dom1,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [TrainingEJB][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
		return valueXmlString;
	}
	public String itemChanged(Document dom, Document dom1, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		StringBuffer valueXmlString = new StringBuffer();
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		int ctr=0;
		String childNodeName = null;
		String columnValue = null;
		String Col_name = "";
		int currentFormNo = 0 ,cnt = 0;
		String siteCode ="" ,siteDescr=" ",itemCode = "",itemCode1 = "",workOrder = "",itemDescr = ""; 
		String loginSite = "",userId = "";
		String sql = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(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>");
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("****************ITEMCHANGE FOR CASE"+currentFormNo+"*******************");
			switch(currentFormNo)
			{
				case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0; 
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if(childNodeName.equals(currentColumn))
					{
						if (childNode.getFirstChild()!= null)
						{
							columnValue = childNode.getFirstChild().getNodeValue().trim();
						}
					}
					ctr++;
				}
				while(ctr < childNodeListLength  && !childNodeName.equals(currentColumn));
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				if((currentColumn.trim().equalsIgnoreCase("itm_default")))
				{
					siteCode = genericUtility.getColumnValue("site_code",dom);
					if (siteCode == null || siteCode.trim().length() == 0)
					{
						siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
						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())
					{
						siteDescr = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append("<site_descr>").append("<![CDATA["+siteDescr+"]]>").append("</site_descr>");	
					// 14/01/14 manoharan set default as blank so that for all rubber stereo the same contract wil be used
					valueXmlString.append("<item_code>").append("<![CDATA[X]]>").append("</item_code>");
					valueXmlString.append("<item_descr>").append("<![CDATA[ALL ITEMS]]>").append("</item_descr>");					
				}
				else if (currentColumn.trim().equalsIgnoreCase("site_code"))
				{
					siteCode = genericUtility.getColumnValue("site_code",dom);
					sql = " select descr from site where site_code = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,siteCode.trim());
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						siteDescr = rs.getString(1);
					}
					valueXmlString.append("<site_descr>").append("<![CDATA["+siteDescr+"]]>").append("</site_descr>");						
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				}				
				else if(currentColumn.trim().equalsIgnoreCase("item_code"))
				{
					itemCode = genericUtility.getColumnValue("item_code",dom) ;
					if (itemCode != null && !"X".equals(itemCode))
					{
						sql = " select descr from item where item_code = ?  ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,itemCode.trim());
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							itemDescr = rs.getString(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append("<item_descr>").append("<![CDATA[" + itemDescr + "]]>").append("</item_descr>");
					}
					else
					{
						valueXmlString.append("<item_code>").append("<![CDATA[X]]>").append("</item_code>");
						valueXmlString.append("<item_descr>").append("<![CDATA[ALL ITEMS]]>").append("</item_descr>");
					
					}
					
				}
				else if((currentColumn.trim().equalsIgnoreCase("itm_defaultedit")))
				{
					siteCode = genericUtility.getColumnValue("site_code",dom);
					System.out.println("site code :::  "+ siteCode);
					itemCode = genericUtility.getColumnValue("item_code",dom) ;
					System.out.println("item code :::  "+ itemCode);
					valueXmlString.append("<site_code protect = \"1\">").append("<![CDATA["+siteCode+"]]>").append("</site_code>");	
					valueXmlString.append("<item_code protect = \"1\">").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
				}
				valueXmlString.append("</Detail1>");
				break;
			}
			valueXmlString.append("</Root>");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
					conn = null;
				}
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		return valueXmlString.toString();
	}//End of itemChanged	 
		 
}// END OF MAIN CLASS
		