/*
 * 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 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);
							String supp_code  = genericUtility.getColumnValue("supp_code", dom);
							String purcOrder  = genericUtility.getColumnValue("purc_order", dom);
							
							if(item_parnt != null && supp_code != null && purcOrder == null)
							{
								String countSerSql = "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' ";
								PreparedStatement pstmtParamName = conn.prepareStatement(countSerSql);
								pstmtParamName.setString(1, supp_code);
								pstmtParamName.setString(2, item_parnt);
								ResultSet rsCnt = pstmtParamName.executeQuery();
								
								if (rsCnt.next()) 
								{
									paramName++;
								}
								else if (paramName == 0) 
								{
									errCode = "VPOERR";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								
							}	
							else
							{	
								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;
						Timestamp ordDate1 = 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", dom2)));
								purcOrder   = checkNullAndTrim((genericUtility.getColumnValue("purc_order", dom2)));
							  //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"));
									ordDate1 = rsCnt.getTimestamp("ord_date");
									System.out.println("Order Date="+ordDate1);
									System.out.println("Delivery Date="+dlvDateStr);
								}
								rsCnt.close();
								rs = null;
								pstmtParamName.close(); 
								pstmt = null;
								
								dlvDate= Timestamp.valueOf(genericUtility.getValidDateString(dlvDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");
								System.out.println("New Delivery Date="+dlvDate);
								
								if (( dlvDateStr != null && dlvDateStr.length() > 0 ) ) 
								{
									
									if (dlvDate.before(ordDate1))
									{
										System.out.println("Delivery Date is before Order Date");
										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("<extra3>").append("<![CDATA[]]>").append("</extra3>\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 extra3 	        = genericUtility.getColumnValue("extra3",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("<item_parnt>").append("<![CDATA["+ checkNullAndTrim(itemParnt) +"]]>").append("</item_parnt>\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("<extra3>").append("<![CDATA["+ checkNullAndTrim(extra3) +"]]>").append("</extra3>\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;
	}

	@Override
	public String getData(String field, String extra) throws ITMException 
	{
		String sql = "";
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
	    
		try 
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			
			if (field.equals("supp_code"))
			{
				  sql = "SELECT SUPP_CODE,SUPP_NAME FROM SUPPLIER";
			}
			
			else if (field.equals("purc_order"))
			{
				//sql = "SELECT PURC_ORDER,STATUS FROM PORDER";
				  sql = " SELECT PURC_ORDER,STATUS FROM PORDER WHERE STATUS !='C' AND CONFIRMED='Y' ";
			}
			
			else if (field.equals("item_parnt"))
			{
				sql = "select item_code,descr from item where item_parnt is null";
			}
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			String otherField  = "";
			String otherField1 = "";
			String otherField2 = "";
			String otherField3 = "";
			String otherField4 = "";
			String exchRate = "";
		
			String descr1	   = "";
			String fieldId     = "";
			String fieldValue  = "";
			
			int num = 1;
			while (rs.next())
			{
				fieldId = checkNullAndTrim(rs.getString(1));
			    fieldValue = checkNullAndTrim(rs.getString(2));
				
				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("<otherField4><![CDATA[").append(otherField4).append("]]></otherField4>\r\n");
				valueXmlString.append("<otherField5><![CDATA[").append(exchRate).append("]]></otherField5>\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();
	}
}
