/*
 * Author:Wasim Ansari
 * Date:02-FEB-15
 * Request ID:D14IKAT004 (PO Wizard)
 */

package ibase.webitm.ejb.wms;
import java.io.IOException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import javax.ejb.Stateless;
import javax.servlet.ServletException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

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

@Stateless
public class DDPorderWizEJB extends ValidatorEJB implements DDPorderWizEJBRemote, DDPorderWizEJBLocal
{
	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);
	}

	@Override
	public String wfValData(Document dom, Document dom1, Document dom2,	String objContext, String editFlag, String xtraParams)throws RemoteException, ITMException
	{
		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 errString = "";
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>"); 
		
		/* try
		{
			conn = connDriver.getConnectDB("DriverITM");	
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			
			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("ord_date")) 
						{
							int paramName = 0;
							String ord_date = genericUtility.getColumnValue("ord_date", dom);
							String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(ORD_DATE) = ?";
							PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
							pstmtParamName.setString(1, ord_date);
							ResultSet rsCnt = pstmtParamName.executeQuery();
							
							System.out.println("countSerSql" + countSerSql);
							if (rsCnt.next()) 
							{
								paramName = rsCnt.getInt("COUNT");
							}
							
							if (paramName != 0) 
							{
								errCode = "VTQDTNV";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else if (childNodeName.equalsIgnoreCase("ind_no")) 
						{
							int paramName = 0;
							String ind_no = genericUtility.getColumnValue("ind_no", dom);
							String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(IND_NO) = ?";
							PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
							pstmtParamName.setString(1, ind_no);
							ResultSet rsCnt = pstmtParamName.executeQuery();
							
							System.out.println("countSerSql" + countSerSql);
							if (rsCnt.next()) 
							{
								paramName = rsCnt.getInt("COUNT");
							}
							
							if (paramName != 0) 
							{
								errCode = "DUPITEMCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else if (childNodeName.equalsIgnoreCase("item_ser")) 
						{
							int paramName = 0;
							String item_ser = genericUtility.getColumnValue("item_ser", dom);
							String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(ITEM_SER) = ?";
							PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
							pstmtParamName.setString(1, item_ser);
							ResultSet rsCnt = pstmtParamName.executeQuery();
							
							System.out.println("countSerSql" + countSerSql);
							if (rsCnt.next()) 
							{
								paramName = rsCnt.getInt("COUNT");
							}
							
							if (paramName != 0) 
							{
								errCode = "DUPITEMCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else if (childNodeName.equalsIgnoreCase("cr_term")) 
						{
							int paramName = 0;
							String cr_term = genericUtility.getColumnValue("cr_term", dom);
							String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(CR_TERM) = ?";
							PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
							pstmtParamName.setString(1, cr_term);
							ResultSet rsCnt = pstmtParamName.executeQuery();
							
							System.out.println("countSerSql" + countSerSql);
							if (rsCnt.next()) 
							{
								paramName = rsCnt.getInt("COUNT");
							}
							
							if (paramName != 0) 
							{
								errCode = "DUPITEMCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						
						
					}
					
				break;	
			
				case 2:
						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();
						}
						
						for(ctr=0;ctr<childNodeListLength;ctr++)
						{
							childNode = childNodeList.item(ctr);
							childNodeName = childNode.getNodeName();
							
							if (childNodeName.equalsIgnoreCase("item_code")) 
							{
								int paramName = 0;
								String item_code = genericUtility.getColumnValue("item_code", dom);
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(ITEM_CODE) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, item_code);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName != 0) 
								{
									errCode = "DUPITEMCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
							
							else if (childNodeName.equalsIgnoreCase("rate")) 
							{
								int paramName = 0;
								String rate = genericUtility.getColumnValue("rate", dom);
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(RATE) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, rate);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName != 0) 
								{
									errCode = "DUPITEMCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
							
							else if (childNodeName.equalsIgnoreCase("quantity")) 
							{
								int paramName = 0;
								String quantity = genericUtility.getColumnValue("quantity", dom);
								String countSerSql = "SELECT COUNT(1) AS COUNT FROM PORDER WHERE TRIM(QUANTITY) = ?";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, quantity);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								System.out.println("countSerSql" + countSerSql);
								if (rsCnt.next()) 
								{
									paramName = rsCnt.getInt("COUNT");
								}
								
								if (paramName != 0) 
								{
									errCode = "DUPITEMCD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}
				
				}
					
			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(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();
				throw new ITMException(d);
			}
		}
		*/
		errString = errStringXml.toString();
		return errString;  
	}

	@Override
	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="";
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo = 0;
		StringBuffer valueXmlString = new StringBuffer();
		String userId = "";
		String currDate = "";
		
		ResultSet rs = null;
		PreparedStatement pstmt = null ;
		
		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("EMPCODE="+empCode);
			
			System.out.println("currentColumn["+currentColumn+"]");
			System.out.println("objContext["+objContext+"]");
			
					
			switch(currentFormNo)
			{
				case 1: 
				{	
					valueXmlString.append("<Detail1 domID='1' selected = 'N'>\r\n");
					
					if( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
					{
						valueXmlString.append("<tran_date>").append("<![CDATA["+currDate+"]]>").append("</tran_date>\r\n");	
						valueXmlString.append("<purc_order>").append("<![CDATA[]]>").append("</purc_order>\r\n");
						valueXmlString.append("<ord_date>").append("<![CDATA["+currDate+"]]>").append("</ord_date>\r\n");	
						valueXmlString.append("<ind_no>").append("<![CDATA[]]>").append("</ind_no>\r\n");
						valueXmlString.append("<item_ser>").append("<![CDATA[]]>").append("</item_ser>\r\n");
						valueXmlString.append("<supp_code>").append("<![CDATA[]]>").append("</supp_code>\r\n");
						valueXmlString.append("<supp_name>").append("<![CDATA[]]>").append("</supp_name>\r\n");	
						valueXmlString.append("<cr_term>").append("<![CDATA[]]>").append("</cr_term>\r\n");
						valueXmlString.append("<crterm_descr>").append("<![CDATA[]]>").append("</crterm_descr>\r\n");	
						valueXmlString.append("<remarks>").append("<![CDATA[]]>").append("</remarks>\r\n");
						valueXmlString.append("<confirmed>").append("<![CDATA[N]]>").append("</confirmed>\r\n");
						valueXmlString.append("<dlv_term>").append("<![CDATA[]]>").append("</dlv_term>\r\n");
						valueXmlString.append("<tran_code>").append("<![CDATA[]]>").append("</tran_code>\r\n");
						valueXmlString.append("<tran_name>").append("<![CDATA[]]>").append("</tran_name>\r\n");
						valueXmlString.append("<price_list>").append("<![CDATA[]]>").append("</price_list>\r\n");
						valueXmlString.append("<conf_date>").append("<![CDATA["+currDate+"]]>").append("</conf_date>\r\n");	
						valueXmlString.append("<exch_rate>").append("<![CDATA[]]>").append("</exch_rate>\r\n");
						valueXmlString.append("<dept_code>").append("<![CDATA[]]>").append("</dept_code>\r\n");
						valueXmlString.append("<department_descr>").append("<![CDATA[]]>").append("</department_descr>\r\n");
						valueXmlString.append("<trans_mode>").append("<![CDATA[]]>").append("</trans_mode>\r\n");	
						valueXmlString.append("<item_code>").append("<![CDATA[]]>").append("</item_code>\r\n");	
						valueXmlString.append("<site_code__dlv>").append("<![CDATA["+siteCode+"]]>").append("</site_code__dlv>\r\n");
						valueXmlString.append("<pord_type>").append("<![CDATA[]]>").append("</pord_type>\r\n");		
						valueXmlString.append("<site_add1>").append("<![CDATA[]]>").append("</site_add1>\r\n");
						valueXmlString.append("<emp_code>").append("<![CDATA["+empCode+"]]>").append("</emp_code>\r\n");	
					}
					
					valueXmlString.append("</Detail1>\r\n");
					break;
				}
				case 2:
				{
					String purc_order 		= genericUtility.getColumnValue("purc_order", dom1,"1");
					String ord_date 	    = genericUtility.getColumnValue("ord_date",dom1,"1");
					String ind_no 		    = genericUtility.getColumnValue("ind_no",dom1,"1");
					String item_ser 		= genericUtility.getColumnValue("item_ser", dom1,"1");
					String supp_code 	    = genericUtility.getColumnValue("supp_code",dom1,"1");
					String suppName 	    = genericUtility.getColumnValue("supp_name",dom1,"1");
					String cr_term 		    = genericUtility.getColumnValue("cr_term",dom1,"1");
					String crDescr		    = genericUtility.getColumnValue("crterm_descr",dom1,"1");
					String remarks 	        = genericUtility.getColumnValue("remarks", dom1,"1");
					String confirmed 	    = genericUtility.getColumnValue("confirmed", dom1,"1");
					String dlv_term 	    = genericUtility.getColumnValue("dlv_term", dom1,"1");
					String tran_code 	    = genericUtility.getColumnValue("tran_code", dom1,"1");
					String tranName	    	= genericUtility.getColumnValue("tran_name", dom1,"1");
					String price_list 	    = genericUtility.getColumnValue("price_list", dom1,"1");
					String conf_date 		= genericUtility.getColumnValue("conf_date", dom1,"1");
					String exch_rate 	    = genericUtility.getColumnValue("exch_rate", dom1,"1");
					String dept_code 		= genericUtility.getColumnValue("dept_code", dom1,"1");
					String deptDescr 		= genericUtility.getColumnValue("department_descr", dom1,"1");
					String trans_mode 	    = genericUtility.getColumnValue("trans_mode", dom1,"1");
					String item_code 	    = genericUtility.getColumnValue("item_code", dom1,"1");
					String tran_date	    = genericUtility.getColumnValue("tran_date", dom1,"1");
					String pordType	        = genericUtility.getColumnValue("pord_type", dom1,"1");
					String sitecode	        = genericUtility.getColumnValue("site_code__dlv", dom1,"1");
					String siteAdd			= genericUtility.getColumnValue("site_add1", dom1,"1");
					
					String itemParent = "",itemDescr = "";
					
					valueXmlString.append("<Detail2 domID='"+1+"' selected = 'N'>\r\n");
					
					if("itm_default".equalsIgnoreCase(currentColumn))
					{ 
						valueXmlString.append("<purc_order>").append("<![CDATA["+ checkNullAndTrim(purc_order) +"]]>").append("</purc_order>\r\n");
						valueXmlString.append("<tran_date>").append("<![CDATA["+ checkNullAndTrim(tran_date) +"]]>").append("</tran_date>\r\n");
						valueXmlString.append("<ord_date>").append("<![CDATA["+ord_date+"]]>").append("</ord_date>\r\n");
						valueXmlString.append("<ind_no>").append("<![CDATA["+ checkNullAndTrim(ind_no) +"]]>").append("</ind_no>\r\n");
						valueXmlString.append("<item_ser>").append("<![CDATA["+ checkNullAndTrim(item_ser) +"]]>").append("</item_ser>\r\n");
						valueXmlString.append("<supp_code>").append("<![CDATA["+supp_code+"]]>").append("</supp_code>\r\n");
						valueXmlString.append("<supp_name>").append("<![CDATA["+suppName+"]]>").append("</supp_name>\r\n");
						valueXmlString.append("<cr_term>").append("<![CDATA["+ checkNullAndTrim(cr_term) +"]]>").append("</cr_term>\r\n");
						valueXmlString.append("<crterm_descr>").append("<![CDATA["+ checkNullAndTrim(crDescr) +"]]>").append("</crterm_descr>\r\n");
						valueXmlString.append("<remarks>").append("<![CDATA["+ checkNullAndTrim(remarks) +"]]>").append("</remarks>\r\n");
						valueXmlString.append("<confirmed>").append("<![CDATA["+ checkNullAndTrim(confirmed) +"]]>").append("</confirmed>\r\n");
						valueXmlString.append("<dlv_term>").append("<![CDATA["+ checkNullAndTrim(dlv_term) +"]]>").append("</dlv_term>\r\n");
						valueXmlString.append("<tran_code>").append("<![CDATA["+ checkNullAndTrim(tran_code) +"]]>").append("</tran_code>\r\n");
						valueXmlString.append("<tran_name>").append("<![CDATA["+ checkNullAndTrim(tranName) +"]]>").append("</tran_name>\r\n");
						valueXmlString.append("<price_list>").append("<![CDATA["+ checkNullAndTrim(price_list) +"]]>").append("</price_list>\r\n");
						valueXmlString.append("<conf_date>").append("<![CDATA["+ checkNullAndTrim(conf_date) +"]]>").append("</conf_date>\r\n");
						valueXmlString.append("<exch_rate>").append("<![CDATA["+ checkNullAndTrim(exch_rate) +"]]>").append("</exch_rate>\r\n");
						valueXmlString.append("<dept_code>").append("<![CDATA["+ checkNullAndTrim(dept_code) +"]]>").append("</dept_code>\r\n");
						valueXmlString.append("<department_descr>").append("<![CDATA["+ checkNullAndTrim(deptDescr) +"]]>").append("</department_descr>\r\n");
						valueXmlString.append("<trans_mode>").append("<![CDATA["+ checkNullAndTrim(trans_mode) +"]]>").append("</trans_mode>\r\n");
						valueXmlString.append("<dlv_date>").append("<![CDATA["+ checkNullAndTrim(currDate) +"]]>").append("</dlv_date>\r\n");
						valueXmlString.append("<pord_type>").append("<![CDATA["+ checkNullAndTrim(pordType) +"]]>").append("</pord_type>\r\n");
						valueXmlString.append("<site_code__dlv>").append("<![CDATA["+ checkNullAndTrim(siteCode) +"]]>").append("</site_code__dlv>\r\n");
						valueXmlString.append("<site_add1>").append("<![CDATA["+ checkNullAndTrim(siteAdd) +"]]>").append("</site_add1>\r\n");
						valueXmlString.append("<emp_code>").append("<![CDATA["+ checkNullAndTrim(empCode) +"]]>").append("</emp_code>\r\n");
					}
				
					valueXmlString.append("</Detail2>\r\n");
						
					break;	
				}
				
				case 3:
				{
					System.out.println("*****Case 3*****");
					
					String itemCode = "",quantity = "",detail3Str = "", dlvDate = "";
					NodeList detail2NodeList = dom2.getElementsByTagName("Detail2");
					
					
					int detail2NodeListlen = detail2NodeList.getLength();
					if("itm_default".equalsIgnoreCase(currentColumn))
					{
						for(int ctr = 0 ;ctr < detail2NodeListlen;ctr++)
						{
								
								Node childNode = detail2NodeList.item(ctr);
								
								if(childNode != null && childNode.getNodeType() != childNode.ELEMENT_NODE)
								{
									continue;
								}
								
								NodeList childList = childNode.getChildNodes();
								
								for(int ctrCh = 0; ctrCh < childList.getLength(); ctrCh++)
								{
									Node itmNode = childList.item(ctrCh);
									
									System.out.println("Current Node Name ["+itmNode.getNodeName()+"]");
									if(itmNode !=null && ("item_code").equalsIgnoreCase(itmNode.getNodeName()))
									{
										itemCode = itmNode.getFirstChild().getNodeValue();
									}
									else if(itmNode !=null && ("quantity").equalsIgnoreCase(itmNode.getNodeName()))
									{
										quantity = itmNode.getFirstChild().getNodeValue();
									}
									else if(itmNode !=null && ("dlv_date").equalsIgnoreCase(itmNode.getNodeName()))
									{
										dlvDate = itmNode.getFirstChild().getNodeValue();
									}
									
								}
								
								System.out.println("ItemCode ["+itemCode+"]quantity["+quantity+"]dlv_date["+dlvDate+"]");
								if(itemCode != null && itemCode.length() > 0)
								{
									detail3Str = getPorderDetails(dom1, dom2, itemCode, quantity, dlvDate, ctr + 1, conn);
									System.out.println("detail3Str["+detail3Str+"]");
									valueXmlString.append(detail3Str+"\r\n");
								}
						}
						
							
							
					}

					break;
				}
				
			}
			
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				connDriver = 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();
				throw new ITMException(d);
			}			
		}
		
		valueXmlString.append( "</Root>\r\n" );	
		System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
		
		return valueXmlString.toString();
	}
	
	public String getList(String field,String extra)	 throws RemoteException, ITMException 
	{
		String sql = "",sql2 = "";
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		Connection conn = null, conn2 = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
	    
		try 
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			
			if (field.equals("tran_code"))
			{
				//sql = "SELECT TRAN_CODE, TRAN_NAME FROM TRANSPORTER";
				
				sql="SELECT transporter.tran_code, transporter.tran_name,transporter_mode.trans_mode FROM transporter INNER JOIN transporter_mode ON transporter.tran_code=transporter_mode.tran_code";
				
			}
			
			else if (field.equals("supp_code"))
			{
				sql = "SELECT SUPP_CODE,SUPP_NAME,CR_TERM,PRICE_LIST,DLV_TERM FROM SUPPLIER";
			}
			
			else if (field.equals("item_ser"))
			{
				sql = "SELECT ITEM_SER,DESCR FROM ITEMSER";
			}
			
			else if (field.equals("cr_term"))
			{
				sql = "SELECT CR_TERM,DESCR FROM CRTERM";
			}
			
			else if (field.equals("dept_code"))
			{
				sql = "SELECT DEPT_CODE,DESCR FROM DEPARTMENT";
			}
			
			else if (field.equals("price_list"))
			{
				sql = "SELECT PRICE_LIST,RATE FROM PRICELIST";
			}
			
			else if (field.equals("item_code"))
			{
				//sql = "SELECT ITEM_CODE,DESCR FROM ITEM WHERE ITEM_PARNT IS NULL";
			      sql =" select item_code,descr from item where item_code"
						+" in (select  distinct item_parnt from item where item_code in " 
					    +" (select item_code from supplieritem where supp_code = ? )) ";
			}
			
			else if (field.equals("type"))
			{
				
			      sql ="SELECT RTRIM(FLD_VALUE),DESCR FROM GENCODES WHERE UPPER(RTRIM(FLD_NAME)) = "
			      	+ "'PORD_TYPE' AND MOD_NAME= 'W_PORDER' AND ACTIVE= 'Y' ";
			}
				
			else if (field.equals("site_code_dlv"))
			{
				
			      sql =" SELECT SITE_CODE, DESCR FROM SITE ";
			}
			
			pstmt = conn.prepareStatement(sql);
			
			if(field.equals("item_code"))
			{
				pstmt.setString(1,extra);
			}
			rs = pstmt.executeQuery();
			
			String otherField  = "";
			String otherField1 = "";
			String otherField2 = "";
			String otherField3 = "";
			
			String descr1	   = "";
			
			String fieldId     = "";
			String fieldValue  = "";
			
			int num = 1;
			while (rs.next())
			{
				fieldId = checkNullAndTrim(rs.getString(1));
			    fieldValue = checkNullAndTrim(rs.getString(2));
				
				
				if (field.equals("tran_code"))
				{
					otherField = checkNullAndTrim(rs.getString(3));
				}
				else if(field.equals("supp_code"))
				{
					otherField1 = checkNullAndTrim(rs.getString(3));
					
					conn2 = connDriver.getConnectDB("DriverITM");
					
					sql2 = " SELECT DESCR FROM CRTERM WHERE CR_TERM = ? ";
					pstmt2 = conn2.prepareStatement(sql2);
					pstmt2.setString(1,otherField1);
					rs2 = pstmt2.executeQuery();
					
					while (rs2.next())
					{
						descr1 = checkNullAndTrim(rs2.getString("DESCR"));
					}
					
					if (rs2 != null) 
					{					
						rs2.close();
						rs2 = null;
					}
					if (pstmt2 != null ) 
					{					
						pstmt2.close();
						pstmt2 = null;
					}
					
					if (conn2 != null && !conn2.isClosed()) 
					{					
						conn2.close();
						conn2 = null;
					}
					
					otherField2 = checkNullAndTrim(rs.getString(4));
					otherField3 = checkNullAndTrim(rs.getString(5));
				}
			
				valueXmlString.append("<supp_code domID='" + num + "'>\r\n");
				valueXmlString.append("<fieldId><![CDATA[").append(fieldId).append("]]></fieldId>\r\n");
				valueXmlString.append("<fieldValue><![CDATA[").append(fieldValue).append("]]></fieldValue>\r\n");
				
				valueXmlString.append("<otherField><![CDATA[").append(otherField).append("]]></otherField>\r\n");
				
				valueXmlString.append("<otherField1><![CDATA[").append(otherField1).append("]]></otherField1>\r\n");
				valueXmlString.append("<otherField2><![CDATA[").append(otherField2).append("]]></otherField2>\r\n");
				valueXmlString.append("<otherField3><![CDATA[").append(otherField3).append("]]></otherField3>\r\n");
				valueXmlString.append("<descr1><![CDATA[").append(descr1).append("]]></descr1>\r\n");
				valueXmlString.append("</supp_code>\r\n");
				num++;
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception :ITMWizardStatefulEJB :getList(String):"	+ e.getMessage() + ":");
			valueXmlString = valueXmlString.append(genericUtility.createErrorString(e));
		} 
		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) 
			{
				System.out.println("Exception :ITMWizardStatefulEJB :getProdTypeList(String) :==>\n"+ e.getMessage());
				throw new ITMException(e);
			}
		}
		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();
	}
	
	/**
	 * 
	 * @param arr
	 * @param targetValue
	 * @return
	 */
	public static String useLoop(String[] arr, String targetValue)
	{
		for(String s: arr)
		{
			String str = checkNullAndTrim(s);
			if(str.equals(targetValue))
				return "Y";
		}
		
		return "N";
	}

	@Override
	public String getPorderDetails(Document dom1,Document dom2,String itemCode,String quantity,String dlvDate, int ctr ,Connection conn)
	{
		System.out.println("********Inside Porder Details***********");
		
		
		GenericUtility genericUtility = GenericUtility.getInstance();
		Document dom=null;
		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		NodeList childNodeList2 = null;
		Node parentNode = null;
		Node childNode = null;
		Node childNode2 = null;
		int childNodeListLength;
		String childNodeName = null;
		PreparedStatement pstmt = null, pstmt1 = null,pstmtItemLotPack = null,pstmtUpdate = null;
		ResultSet rs = null, rs1=null, rsItemLotPack = null;
		String sql="", sql1="";
		
		
	
		StringBuffer valueXmlString = new StringBuffer("");
		String suppCode = "";
		String ordDate = "";
		String deptCode = "";
		String rate1 = "";
		String empCode = "";
		
		
		String pOrder = "";
		int lineNo = 0;
		String unit = "";
		String locCode = "";
		String packCode = "";
		int purLeadTime = 0;
		Date dueDate = null;
		Date currDate = null;
		String itemAcctDetr = "";
		String itemSer = "";
		String acctCodeDr = "";
		String cctrCodeDr = "";
		String cctrCodeCr = "";
		String invAcctVal = "";
		String invAcctQcVal = "";
		String acctCodeCr = "";
		String acctCodeAp = "";
		String cctrCodeAp = "";
		String indNo = "";
		String crTerm = "";
		String remarks = "";
		String itemDescr = "";
		double rate = 0d;
		String priceList = "";
		String pricelistClg="";
		String priceListStd = "";
		DistCommon distComm = new DistCommon();
		String sysDateStr = "";
		String postType = "";
		double rateStd = 0d;
		double rateClg = 0d;
		double shipperSize = 0d, grossWeight = 0d, netWeight = 0d, tareWeight = 0d;
		String lotNo = "";
		int noArt = 0;
		String lineNoOrd = "";
		String siteCode = "";
		SimpleDateFormat simpleDateFormat1 = null;
		FinCommon finCommon = null;
		
		String detailDomStr = "";
		
		double amount;
		double quan=Double.parseDouble(quantity);
		
		try
		{
			finCommon = new FinCommon();
				
			 
			 ordDate		= genericUtility.getColumnValue("ord_date", dom1,"1");
			 indNo 		    = genericUtility.getColumnValue("ind_no", dom1,"1");
			 itemSer 		= genericUtility.getColumnValue("item_ser", dom1,"1");
			 suppCode 	    = genericUtility.getColumnValue("supp_code", dom1,"1");
			 crTerm 	    = genericUtility.getColumnValue("cr_term", dom1,"1");
			 remarks 	    = genericUtility.getColumnValue("remarks", dom1,"1");
			 deptCode 	    = genericUtility.getColumnValue("dept_code", dom1,"1");
			 siteCode 	    = genericUtility.getColumnValue("site_code__dlv", dom1,"1");
			 pOrder 	    = genericUtility.getColumnValue("pord_type", dom1,"1");
			 priceList 	    = genericUtility.getColumnValue("price_list", dom1,"1");
			 empCode 	    = genericUtility.getColumnValue("emp_code", dom1,"1");
			
			 /******************************/
			 sql = " select rate from pricelist where item_code = ? "
						+"and price_list in (select price_list from supplier where supp_code = ? )";
			 
			 
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				pstmt.setString(2, suppCode);
				
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					rate = Double.parseDouble(checkNullAndTrim(rs.getString("rate")));
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
				
				System.out.println("Rate="+rate);
			 
			 /*****************************/
			 
				sql = "SELECT ACCT_CODE__AP, CCTR_CODE__AP FROM SUPPLIER WHERE SUPP_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, suppCode);
				rs = pstmt.executeQuery();
				if ( rs.next() )
				{
					acctCodeAp = checkNullAndTrim(rs.getString("ACCT_CODE__AP"));
					cctrCodeAp = checkNullAndTrim(rs.getString("CCTR_CODE__AP"));
				}
				rs.close();rs = null;
				pstmt.close();pstmt = null;
			
				valueXmlString.append("<Detail3 domID='"+ctr+"' selected = 'Y'>\r\n");
							
				sql = "SELECT  I.UNIT,I.DESCR, I.LOC_CODE, I.ITEM_SER, I.PACK_CODE,I.GROSS_WEIGHT,I.NET_WEIGHT "
						+ " FROM ITEM I WHERE  I.ITEM_CODE = ? ";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					rs = pstmt.executeQuery();
					int domID = 0;
					
					if( rs.next())
					{
						domID++;
						lineNo = domID;
						
						unit = rs.getString("UNIT");
						locCode = rs.getString("LOC_CODE");
						itemDescr = checkNullAndTrim("DESCR");
						shipperSize =  0;//Will be taken from user as it is not defined.
						grossWeight =  rs.getDouble("GROSS_WEIGHT");
						grossWeight = grossWeight * Double.parseDouble(quantity);
						netWeight =  rs.getDouble("NET_WEIGHT");
						netWeight = netWeight * Double.parseDouble(quantity);
						tareWeight = grossWeight - netWeight;
						
						packCode = checkNullAndTrim(rs.getString("PACK_CODE"));
						
						sql1 = " SELECT PUR_LEAD_TIME FROM ITEM WHERE ITEM_CODE = ?";
						
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1, itemCode);
						rs1 = pstmt1.executeQuery();
						if( rs1.next() )
						{
							purLeadTime = rs1.getInt( "PUR_LEAD_TIME" );
							System.out.println("PUR_LEAD_TIME ="+purLeadTime);
						}
						rs1.close();
						pstmt1.close();
						pstmt1 = null;
						rs1 = null;
						dueDate = addDays(currDate, purLeadTime) ;
						System.out.println("dueDate="+dueDate);
						
						
						
						itemAcctDetr  = finCommon.getFromAcctDetr(itemCode, itemSer, "IN", conn);
					
						String tokens [] = itemAcctDetr.split(",");
					
						if ( tokens.length >= 2)
						{
							acctCodeDr = tokens[0];
							cctrCodeDr = tokens[1];
						}
						else
						{
							acctCodeDr = itemAcctDetr.substring(0,itemAcctDetr.indexOf(",") -1);
							cctrCodeDr = itemAcctDetr.substring(itemAcctDetr.indexOf(",") + 1);
						}
						
						tokens = null;

						invAcctVal = finCommon.getFinparams("999999", "INV_ACCT_PORCP", conn);
						if ( "NULLFOUND".equals(invAcctVal.trim()) || invAcctVal.trim().length() == 0 )
						{
							invAcctVal = "N";
						}

						invAcctQcVal = finCommon.getFinparams("999999", "INV_ACCT_QCORDER", conn);
						if ( "NULLFOUND".equals(invAcctQcVal.trim()) || invAcctQcVal.trim().length() == 0 )
						{
							invAcctQcVal = "N";
						}
						if ( "Y".equals(invAcctVal) && !"Y".equals(invAcctQcVal) )
						{
							itemAcctDetr  = finCommon.getFromAcctDetr(itemCode, itemSer, "PO", conn);
						}
						else
						{
							itemAcctDetr  = finCommon.getFromAcctDetr(itemCode, itemSer, "PORCP", conn);
						}
						
						tokens = itemAcctDetr.split(",");

						if ( tokens.length >= 2)
						{
							acctCodeCr = tokens[0];
							cctrCodeCr = tokens[1];
						}
						else
						{
							acctCodeCr = itemAcctDetr.substring(0,itemAcctDetr.indexOf(",") -1+ 1);
							cctrCodeCr = itemAcctDetr.substring(itemAcctDetr.indexOf(",") + 1);
						}
						
						if ( acctCodeCr == null || acctCodeCr.trim().length() == 0 )
						{
							acctCodeCr = acctCodeAp;
							cctrCodeCr = cctrCodeAp;
						}

						//rate = distComm.pickRate( priceList, sysDateStr, itemCode, " ", "L", conn );
						//System.out.println("Rate="+rate);
						
						if (cctrCodeCr == null || "null".equals(cctrCodeCr) || cctrCodeCr.trim().length() == 0)
						{
							cctrCodeCr = "    ";
						}
						if (cctrCodeDr == null || "null".equals(cctrCodeDr) || cctrCodeDr.trim().length() == 0)
						{
							cctrCodeDr = "    ";
						}
					
						priceListStd = "";
						if("S".equalsIgnoreCase(postType.trim()))
						{
							priceListStd = checkNullAndTrim(distComm.getDisparams("999999", "STD_PO_PL", conn));
							if( priceListStd.trim().length() > 0 )
							{
								rateStd = distComm.pickRate( priceListStd, sysDateStr, itemCode, " ", "L", conn );
							}
						}
						if ( rateStd == 0 )
						{
							rateStd = rate;
						}
						
						if( pricelistClg.trim().length() > 0 )
						{
							rateClg = distComm.pickRate( pricelistClg, sysDateStr, itemCode, " ", "L", conn );
						}
						if ( rateClg == 0 )
						{
							rateClg = rate;
						}
						
						amount= quan * rate;
						
						lineNoOrd = "   " + ctr;
						lineNoOrd = lineNoOrd.substring( lineNoOrd.length()-3 );
						valueXmlString.append("<item_code><![CDATA[").append(itemCode).append("]]></item_code>\r\n");
						valueXmlString.append("<descr><![CDATA[").append(itemDescr).append("]]></descr>\r\n");
						valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n");
						valueXmlString.append("<dlv_date><![CDATA[").append(dlvDate).append("]]></dlv_date>\r\n");
						valueXmlString.append("<supp_code><![CDATA[").append(suppCode).append("]]></supp_code>\r\n");
						valueXmlString.append("<ord_date><![CDATA[").append(ordDate).append("]]></ord_date>\r\n");
						valueXmlString.append("<dept_code><![CDATA[").append( checkNullAndTrim(deptCode) ).append("]]></dept_code>\r\n");
						valueXmlString.append("<item_ser><![CDATA[").append( checkNullAndTrim(itemSer) ).append("]]></item_ser>\r\n");
						valueXmlString.append("<line_no><![CDATA[").append(lineNoOrd).append("]]></line_no>");
						valueXmlString.append("<status><![CDATA[").append("O").append("]]></status>");
						valueXmlString.append("<status_date><![CDATA[").append(sysDateStr).append("]]></status_date>");
						valueXmlString.append("<quantity__stduom><![CDATA[").append(quantity).append("]]></quantity__stduom>");
						valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>");
						valueXmlString.append("<unit__std><![CDATA[").append(unit).append("]]></unit__std>");
						valueXmlString.append("<unit__rate><![CDATA[").append(unit).append("]]></unit__rate>");
						valueXmlString.append("<rate><![CDATA[").append( rate ).append("]]></rate>");
						valueXmlString.append("<discount><![CDATA[0]]></discount>");
						valueXmlString.append("<tax_amt><![CDATA[0]]></tax_amt>");
						valueXmlString.append("<dlv_qty><![CDATA[0]]></dlv_qty>");
	                    if(dueDate != null)
	                    {
	                    	valueXmlString.append("<dlv_date><![CDATA["+simpleDateFormat1.format(dueDate)+"]]></dlv_date>");
	                    }
	                    valueXmlString.append("<rate__stduom><![CDATA[").append(rate).append("]]></rate__stduom>");
	                    valueXmlString.append("<pack_code><![CDATA[").append(packCode).append("]]></pack_code>");
	                    valueXmlString.append("<rate__clg><![CDATA[").append(rateClg).append("]]></rate__clg>"); 
	                    valueXmlString.append("<std_rate><![CDATA[").append( rateStd ).append("]]></std_rate>");
	                    valueXmlString.append("<conv__qty_stduom><![CDATA[").append("1").append("]]></conv__qty_stduom>");
	                    valueXmlString.append("<conv__rtuom_stduom><![CDATA[").append("1").append("]]></conv__rtuom_stduom>");
	                    valueXmlString.append("<req_date><![CDATA[").append(sysDateStr).append("]]></req_date>");
	                    valueXmlString.append("<loc_code><![CDATA[").append(locCode).append("]]></loc_code>");
	                    valueXmlString.append("<acct_code__dr><![CDATA[").append(acctCodeDr).append("]]></acct_code__dr>");
	                    valueXmlString.append("<cctr_code__dr><![CDATA[").append(cctrCodeDr).append("]]></cctr_code__dr>");
	                    valueXmlString.append("<acct_code__cr><![CDATA[").append(acctCodeCr).append("]]></acct_code__cr>");
	                    valueXmlString.append("<cctr_code__cr><![CDATA[").append(cctrCodeCr).append("]]></cctr_code__cr>");
	                    valueXmlString.append("<operation><![CDATA[").append("0").append("]]></operation>");
	                    valueXmlString.append("<git_qty><![CDATA[").append("0").append("]]></git_qty>");
	                    valueXmlString.append("<adj_qty><![CDATA[").append("0").append("]]></adj_qty>");
						valueXmlString.append("<quantity__fc><![CDATA[").append("0").append("]]></quantity__fc>");
						valueXmlString.append("<tot_amt><![CDATA[").append(amount).append("]]></tot_amt>");
						valueXmlString.append("<site_code__dlv><![CDATA[").append(siteCode).append("]]></site_code__dlv>");
						valueXmlString.append("<pord_type><![CDATA[").append(pOrder).append("]]></pord_type>");
						valueXmlString.append("<emp_code><![CDATA[").append(empCode).append("]]></emp_code>");
					}	
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					if ( rs != null )
					{
						rs.close();
						rs = null;
					}
					valueXmlString.append("</Detail3>\r\n");
					
	

		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
					
		return valueXmlString.toString();
	}
	
	public static java.util.Date addDays(java.util.Date date, int days)
	{
		java.sql.Date calculatedDate = null;
		if (date != null) 
		{
			final GregorianCalendar calendar = new GregorianCalendar();
			calendar.setTime(date);
			calendar.add(Calendar.DATE,days);
			calculatedDate = new java.sql.Date(calendar.getTime().getTime());
		}

		return calculatedDate;
	}
	
	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;
	}
	
}
	

