/*
Request Id:F14LSUN003
developer : Chandrakant/mahendra
*/
package ibase.webitm.ejb.fin;
import ibase.system.config.ConnDriver;
import ibase.utility.E12GenericUtility;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; 
import java.sql.Timestamp;

import ibase.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.rmi.RemoteException;
import java.util.*;
import java.io.*;
import javax.naming.InitialContext;
import org.w3c.dom.*;
import javax.xml.parsers.*;


import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;
import ibase.webitm.utility.*;
import ibase.webitm.utility.wiz.*;
import ibase.utility.*;
import javax.naming.InitialContext;
import javax.sql.*;
import ibase.utility.UserInfoBean;


@Stateless  

public class MilestonePayGenPrc extends ValidatorEJB implements MilestonePayGenPrcLocal, MilestonePayGenPrcRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	E12GenericUtility e12GenericUtility = new E12GenericUtility();
	

	//method for validation
	public String wfValData() throws RemoteException,ITMException
	{
		System.out.println("wfValData()...");
		return "";
	}
	
	
	public String wfValData(String xmlString, String xmlString1,String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String  errString = "";
		System.out.println("wfValData(String)>>>");
		try
		{
			dom = parseString(xmlString);
			System.out.println("MilestonePayGenPrc : wfValData(String xmlString) : ==>\n" + xmlString );
			System.out.println("MilestonePayGenPrc : wfValData(String xmlString1) : ==>\n" + xmlString1 );
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0 )
			{
				dom2 = parseString("<Root>" + xmlString2+ "</Root>");
			}
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : MilestonePayGenPrc : wfValData(String xmlString) : ==>\n"+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		return (errString);
	}
	
	
	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "",errString="";
		String siteCodeFrom = "",purcOrder="",projCode="",processDate="",relAgnst="";
		String errCode = "",sql="",errCode1="",userId="",errorType="";
	    int ctr,currentFormNo=0;
		int childNodeListLength=0;
		int count = 0,cnt=0;
        Connection conn = null;
	    SimpleDateFormat simpleDateFormat=null;
        ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Date processDate1=null;
		try
		{
			System.out.println("wfValData called in MilestonePayGenPrc");
			Date currentDate = new Date();
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			conn = getConnection();
			userId = getValueFromXTRA_PARAMS(xtraParams,"userId");
			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();

							//int cnt;
						System.out.println("childNodeName: " + childNodeName);
						
						if (childNodeName.equals("purc_order"))
						{
							
							System.out.println("purc_order :"+genericUtility.getColumnValue("purc_order",dom));
							purcOrder=genericUtility.getColumnValue("purc_order",dom);
							relAgnst=genericUtility.getColumnValue("release_against",dom);
							System.out.println("relAgnst :"+relAgnst);
							
						
							if(purcOrder != null)
							{
								//sql = "SELECT COUNT(*) FROM PORD_PAY_TERM  WHERE PURC_ORDER = ? AND  VOUCH_CREATED = 'N'";
								sql = "SELECT COUNT(*) FROM PORD_PAY_TERM  WHERE PURC_ORDER = ? AND " +
										" CASE WHEN VOUCH_CREATED IS NULL THEN 'N' ELSE VOUCH_CREATED END='N' and rel_agnst=? and task_code is not null ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, purcOrder.trim());
								pstmt.setString(2, relAgnst);
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									cnt = rs.getInt(1);
								}
								System.out.println("cnt ::"+cnt);
								if (cnt == 0)
								{
									errCode = "VTINVPUORD";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
									break;
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
							}
							
							//count = 0;
						}
						
						if(childNodeName.equals("proj_code"))
						{
							System.out.println("proj_code :"+genericUtility.getColumnValue("proj_code",dom));
							projCode=genericUtility.getColumnValue("proj_code",dom);
							
							if(genericUtility.getColumnValue("purc_order",dom) == null && projCode == null)
							{
								System.out.println("purchase order or project code should not be null!! ");
								errCode = "VTEMPPROCD";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
								break;
								
							}
							if(projCode != null)
							{
								
								sql = "select count(*) from porddet where proj_code = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, projCode.trim());
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									cnt = rs.getInt(1);
								}
								System.out.println("cnt ::"+cnt);
								if (cnt == 0)
								{
									errCode = "VTPRJCDINV";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
									break;
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								
								
							}
							
							
							
						}
					
						if(childNodeName.equals("process_date"))
						{
							processDate=genericUtility.getColumnValue("process_date",dom);
							System.out.println("processDate :"+processDate);
							
							Timestamp processDt = Timestamp.valueOf(genericUtility.getValidDateString(processDate, genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00.0");
							System.out.println("processDt :"+processDt);
							System.out.println("currentDate :"+currentDate);
							System.out.println("processDt.compareTo(currentDate) :"+processDt.compareTo(currentDate));
							System.out.println("currentDate.compareTo(processDt) :"+currentDate.compareTo(processDt));
							if (currentDate.compareTo(processDt) <= 0)
							{
								errCode = "VTPROCINV";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
								break;
							}
							
						}
					}
				}
			}
			
		
			int errListSize = errList.size();
			System.out.println("errListSize :"+errListSize);
			cnt = 0;
			String errFldName = null;
			if (errList != null && errListSize > 0)
			{
				
				for (cnt = 0; cnt < errListSize; cnt++)
				{
					errCode = errList.get(cnt);
					errFldName = errFields.get(cnt);
					System.out.println("errCode .........." + errCode);
					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("");
			}	
			
			
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}catch(Exception se){}
		}
		errString = errStringXml.toString();
		return errString;
	}
	
	
	
	

	// method for item change
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		System.out.println("itemChanged() called for MilestonePayGenPrc");
		String valueXmlString = "";
		try
		{   
			System.out.println("xmlString:::"+xmlString);
			System.out.println("xmlString1:::"+xmlString1);
			System.out.println("xmlString2:::"+xmlString2);
			System.out.println("objContext:::"+objContext);

			if(xmlString != null && xmlString.trim().length() > 0)
			{
				dom = parseString(xmlString);
			}
			if(xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom1 = parseString(xmlString1);
			}
			if(xmlString2 != null && xmlString2.trim().length() > 0)
			{
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [MilestonePayGenPrc][itemChanged( String, String )] :==>\n" + e.getMessage());
		}
		return valueXmlString;
	}

	// method for item change
	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		String childNodeName = null;
		String sql = "";
		StringBuffer valueXmlString = new StringBuffer();
		int ctr = 0 , currentFormNo = 0 ,lineNo = 0 ,domID = 0;
		java.sql.Timestamp dateTo =  null ,dateFr = null;
		Document argDom = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		SimpleDateFormat sdf = null;

		String projCode="",purcOrder="",releaseAgainst="",sysDate="",processDate="",resultString="",getDataSql="",processDt="";
		//Timestamp processDate1 = new Timestamp();
		java.sql.Timestamp processDate1 =  null ;
		ArrayList<String> purcOrderList = new ArrayList<String>();
		Date processDate2=null; 
		
		
		try
		{   
			System.out.println("itemChanged called in MilestonePayGenPrc ");
			System.out.println("objContext:::"+objContext);
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			 sdf = new SimpleDateFormat(e12GenericUtility.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>");

			//loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"); 

			switch(currentFormNo)
			{

			case 1 :

				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				System.out.println("currentColumn in case 1===="+currentColumn);
				if(currentColumn.trim().equals("itm_default"))
				{
					/*Calendar cal = Calendar.getInstance();
					Date d = new Date();
					cal.setTime(d);
					cal.set(Calendar.DATE,1);
					d = cal.getTime();
					String sysDate = simpleDateFormat.format(d);
					System.out.println("sysDate[" + sysDate + "]");*/
					
					Date d = new Date();
				    sysDate = simpleDateFormat.format(d);
					System.out.println("sysDate:" + sysDate + "]");
					
					valueXmlString.append("<proj_code>").append("").append("</proj_code>"); 
					valueXmlString.append("<purc_order>").append("").append("</purc_order>");
					valueXmlString.append("<release_against>").append("").append("</release_against>");
					valueXmlString.append("<process_date>").append(sysDate).append("</process_date>");
				}

				valueXmlString.append("</Detail1>");
				break;
			case 2 : 
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
				System.out.println("currentColumn in case 2===="+currentColumn);
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					projCode = checkNull(genericUtility.getColumnValue("proj_code",dom1));
					purcOrder = checkNull(genericUtility.getColumnValue("purc_order",dom1));
					releaseAgainst = checkNull(genericUtility.getColumnValue("release_against",dom1));
					processDate = checkNull(genericUtility.getColumnValue("process_date",dom1));
					System.out.println("processDate :"+processDate);
				
					
					System.out.println("processDt :"+processDt);
					System.out.println("projCode["+projCode+"]purcOrder["+purcOrder+"]releaseAgainst["+releaseAgainst+"]processDate["+processDate+"]");

				//	processDate1 = java.sql.Timestamp.valueOf(genericUtility.getValidDateString(genericUtility.getColumnValue("processDate",dom1), genericUtility.getApplDateFormat(), genericUtility.getDBDateFormat()) + " 00:00:00");
			
				sql = " select distinct purc_order from porddet where proj_code = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, projCode);
				rs = pstmt.executeQuery();
				while ( rs.next() )
				{
					purcOrderList.add(rs.getString(1));
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if( purcOrder != null && purcOrder.trim().length() > 0 && (!purcOrderList.contains(purcOrder)) )
				{
					purcOrderList.add(purcOrder);
				}

				System.out.println("@@@@@@@@@@@ purcOrderList ["+purcOrderList+"]");
				for( int ListCtr = 0 ; ListCtr < purcOrderList.size()  ; ListCtr++  )
				{

					getDataSql = "SELECT  PURC_ORDER, LINE_NO , TYPE ,REL_AGNST ,AMT_TYPE ,REL_AMT , " +
							" REL_AFTER ,VOUCH_CREATED ,TASK_CODE ,DUE_DATE , ADJ_METHOD ,ACCT_CODE ,CCTR_CODE ,TAX_CLASS ," +
							" TAX_CHAP , TAX_ENV ,RETENTION_PERC ,SITE_CODE__ADV ,ADJ_PERC " +
							" FROM PORD_PAY_TERM  WHERE PURC_ORDER = ? AND  VOUCH_CREATED = 'N'  and rel_agnst in ('05','06')  ";
					pstmt = conn.prepareStatement(getDataSql);
					pstmt.setString(1,purcOrderList.get(ListCtr));
					rs = pstmt.executeQuery();
					
					while(rs.next())
					{					

						domID ++;
						System.out.println("domID :"+domID);
						valueXmlString.append( "<Detail2 domID='"+ domID +"' selected = 'N'>\r\n" );
						//SALE_ORDER
						valueXmlString.append("<purc_order>").append("<![CDATA[" + checkNull(rs.getString("PURC_ORDER")) +"]]>").append("</purc_order>\r\n\r\n");
						//LINE_NO
						valueXmlString.append("<line_no>").append("<![CDATA[" + checkNull(rs.getString("LINE_NO")) +"]]>").append("</line_no>\r\n");
						//TYPE 
						valueXmlString.append("<type>").append("<![CDATA[" + checkNull(rs.getString("TYPE")) +"]]>").append("</type>\r\n");
						//REL_AGNST 
						valueXmlString.append("<rel_agnst>").append("<![CDATA[" + checkNull(rs.getString("REL_AGNST")) +"]]>").append("</rel_agnst>\r\n");
						//AMT_TYPE 
						valueXmlString.append("<amt_type>").append("<![CDATA[" + checkNull(rs.getString("AMT_TYPE")) +"]]>").append("</amt_type>\r\n");
						//REL_AMT 
						valueXmlString.append("<rel_amt>").append("<![CDATA[" + checkNull(rs.getString("REL_AMT")) +"]]>").append("</rel_amt>\r\n");
						//REL_AFTER 
						valueXmlString.append("<rel_after>").append("<![CDATA[" + checkNull(rs.getString("REL_AFTER")) +"]]>").append("</rel_after>\r\n");
						//VOUCH_CREATED 
						valueXmlString.append("<vouch_created>").append("<![CDATA[" + checkNull(rs.getString("VOUCH_CREATED")) +"]]>").append("</vouch_created>\r\n");
						//TASK_CODE 
						
						
						
						valueXmlString.append("<task_code>").append("<![CDATA[" + checkNull(rs.getString("TASK_CODE")) +"]]>").append("</task_code>\r\n");
						//DUE_DATE 
						if(rs.getDate("DUE_DATE") == null)
						{
							valueXmlString.append("<due_date>").append("<![CDATA[]]>").append("</due_date>\r\n");
						}
						else
						{
							valueXmlString.append("<due_date>").append("<![CDATA[" + checkNull(sdf.format(rs.getDate("DUE_DATE"))) +"]]>").append("</due_date>\r\n");
						}
						
						
						
						// ADJ_METHOD 
						valueXmlString.append("<adj_method>").append("<![CDATA[" + checkNull(rs.getString("ADJ_METHOD")) +"]]>").append("</adj_method>\r\n");
						//ACCT_CODE 
						valueXmlString.append("<acct_code>").append("<![CDATA[" + checkNull(rs.getString("ACCT_CODE")) +"]]>").append("</acct_code>\r\n");
						//CCTR_CODE 
						valueXmlString.append("<cctr_code>").append("<![CDATA[" + checkNull(rs.getString("CCTR_CODE")) +"]]>").append("</cctr_code>\r\n");
						//TAX_CLASS 
						valueXmlString.append("<tax_class>").append("<![CDATA[" + checkNull(rs.getString("TAX_CLASS")) +"]]>").append("</tax_class>\r\n");
						//TAX_CHAP 
						valueXmlString.append("<tax_chap>").append("<![CDATA[" + checkNull(rs.getString("TAX_CHAP")) +"]]>").append("</tax_chap>\r\n");
						// TAX_ENV 
						valueXmlString.append("<tax_env>").append("<![CDATA[" + checkNull(rs.getString("TAX_ENV")) +"]]>").append("</tax_env>\r\n");
						//RETENTION_PERC 
						valueXmlString.append("<retention_perc>").append("<![CDATA[" + checkNull(rs.getString("RETENTION_PERC")) +"]]>").append("</retention_perc>\r\n");
						//SITE_CODE__ADV 
						valueXmlString.append("<site_code__adv>").append("<![CDATA[" + checkNull(rs.getString("SITE_CODE__ADV")) +"]]>").append("</site_code__adv>\r\n");
						//ADJ_PERC
						valueXmlString.append("<adj_perc>").append("<![CDATA[" + checkNull(rs.getString("ADJ_PERC")) +"]]>").append("</adj_perc>\r\n");
						
						valueXmlString.append("<process_date>").append("<![CDATA[" + processDate +"]]>").append("</process_date>\r\n");

						valueXmlString.append( "</Detail2>\r\n" );  

					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
				}
				}
				break;


			}
			valueXmlString.append("</Root>");
			System.out.println("valueXmlString :"+valueXmlString);
		}
		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();
	}


	
	private String checkNull(String input)
	{
		if(input == null) 
		{
			input = "";
		}
		return input; 
	}


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

/*---------------------------------------------------------------------------------------------------------------*/
	
	
	public String getPurcOrderList(String selectedActCode)
	{
		
		String purcOrdData = "";
		try
		{
			System.out.println("getPurcOrderList for popHelp(purc_order)");
		
			
			purcOrdData = getPurcOrderList1(selectedActCode);
			
			String xslFileName = getXSLFileName("milestone_purcOrder_pophelp.xsl");
			System.out.println("xslFileName !!! "+xslFileName);
			System.out.println("purcOrdData@@@@:"+purcOrdData);
			System.out.println("CommonConstants.APPLICATION_CONTEXT@@@@:"+CommonConstants.APPLICATION_CONTEXT);
			System.out.println("File.separator@@@@:"+File.separator);
			purcOrdData = new E12GenericUtility().transformToString(xslFileName, purcOrdData, CommonConstants.APPLICATION_CONTEXT + File.separator + "temp", "Output", ".html" );
			System.out.println("purcOrdData :"+purcOrdData);
		}
		catch ( Exception e  )
		{
			e.printStackTrace();
		}
		
		return purcOrdData;
	}
	
	
	

	public String getPurcOrderList1(String selectedActCode) throws ITMException 
	{
		String sql = "";
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		Connection connectionObject = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		E12GenericUtility genericUtility = new E12GenericUtility();
		try 
		{
			System.out.println("getPurcOrderList1!!!!!!!!!!!");
			ConnDriver connDriver = new ConnDriver();
			connectionObject = connDriver.getConnectDB("DriverITM");
			
			//sql = "select purc_order,line_no,rel_agnst,rel_amt,vouch_created from pord_pay_term where vouch_created='N' ";
			
			sql = " select purc_order,line_no,rel_agnst,rel_amt,vouch_created from pord_pay_term where " +
					" vouch_created='N' and rel_agnst in ('05','06') and task_code is not null order by purc_order desc  ";
			
			System.out.println(" selectteam sql [" + sql + "]");
			pstmt = connectionObject.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int num = 1;
			while (rs.next()) 
			{
				String actCode = rs.getString("purc_order");
				System.out.println("purc_order :"+actCode);
				selectedActCode = (selectedActCode != null) ? selectedActCode.trim() : selectedActCode;
				System.out.println("selectedActCode :"+selectedActCode);
				System.out.println("actCode :"+actCode);
				actCode = (actCode != null) ? actCode.trim() : actCode;
				if (selectedActCode.equals(actCode)) 
				{
					valueXmlString.append("<ACTIVITY domID='" + num	+ "' selected = 'Y'>\r\n");
				} 
				else 
				{
					valueXmlString.append("<ACTIVITY domID='" + num	+ "' selected = 'N'>\r\n");
				}

				valueXmlString.append("<purc_order><![CDATA[").append(rs.getString("purc_order")).append("]]></purc_order>\r\n");
				valueXmlString.append("<line_no><![CDATA[").append(rs.getString("line_no")).append("]]></line_no>\r\n");
				valueXmlString.append("<rel_agnst><![CDATA[").append(rs.getString("rel_agnst")).append("]]></rel_agnst>\r\n");
				valueXmlString.append("<rel_amt><![CDATA[").append(rs.getString("rel_amt")).append("]]></rel_amt>\r\n");
				valueXmlString.append("<vouch_created><![CDATA[").append(rs.getString("vouch_created")).append("]]></vouch_created>\r\n");
				valueXmlString.append("</ACTIVITY>\r\n");
				num++;
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception :getPurcOrderList1(String):"	+ e.getMessage() + ":");
			valueXmlString = valueXmlString.append(genericUtility.createErrorString(e));
		} 
		finally 
		{
			try 
			{
				if (connectionObject != null && !connectionObject.isClosed()) 
				{					
					connectionObject.close();
					connectionObject = null;
				}
			} 
			catch (Exception e) 
			{
				System.out.println("Exception :getSalesPersTeamList(String) :==>\n"+ e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append("</Root>\r\n");
		System.out.println("\n****ValueXmlString :" + valueXmlString.toString()	+ ":********");
		return valueXmlString.toString();
	}
	
	
	
	public String getProjectCdList(String selectedActCode)
	{
		
		String projectData = "";
		try
		{
			System.out.println("getProjectCdList=[");
			
			projectData = getProjectCdList1(selectedActCode);
				
			String xslFileName = getXSLFileName("milestone_projectCd_pophelp.xsl");
			System.out.println("xslFileName !!! "+xslFileName);
			System.out.println("purcOrdData@@@@:"+projectData);
			System.out.println("CommonConstants.APPLICATION_CONTEXT@@@@:"+CommonConstants.APPLICATION_CONTEXT);
			System.out.println("File.separator@@@@:"+File.separator);
			projectData = new E12GenericUtility().transformToString(xslFileName, projectData, CommonConstants.APPLICATION_CONTEXT + File.separator + "temp", "Output", ".html" );
			System.out.println("projectData :"+projectData);
		}
		catch ( Exception e  )
		{
			e.printStackTrace();
		}
		
		return projectData;
	}
	
	
	public String getProjectCdList1(String selectedActCode) throws ITMException 
	{
		String sql = "";
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		Connection connectionObject = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		E12GenericUtility genericUtility = new E12GenericUtility();
		try 
		{
			System.out.println("getProjectCdList1!!!!!!!!!!!");
			ConnDriver connDriver = new ConnDriver();
			connectionObject = connDriver.getConnectDB("DriverITM");
			
		
			sql = " select distinct(PROJ_CODE) from porddet ";
			
			
			System.out.println(" selectteam sql [" + sql + "]");
			pstmt = connectionObject.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int num = 1;
			while (rs.next()) 
			{
				String actCode = rs.getString("proj_code");
				System.out.println("purc_order :"+actCode);
				//String actCode = rs.getString("ACTIVITY_CODE");
				selectedActCode = (selectedActCode != null) ? selectedActCode.trim() : selectedActCode;
				System.out.println("selectedActCode :"+selectedActCode);
				System.out.println("actCode :"+actCode);
				actCode = (actCode != null) ? actCode.trim() : actCode;
				if (selectedActCode.equals(actCode)) 
				{
					valueXmlString.append("<ACTIVITY domID='" + num	+ "' selected = 'Y'>\r\n");
				} 
				else 
				{
					valueXmlString.append("<ACTIVITY domID='" + num	+ "' selected = 'N'>\r\n");
				}

				valueXmlString.append("<proj_code><![CDATA[").append(rs.getString("proj_code")).append("]]></proj_code>\r\n");
				valueXmlString.append("</ACTIVITY>\r\n");
				num++;
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception :getProjectCdList1(String):"	+ e.getMessage() + ":");
			valueXmlString = valueXmlString.append(genericUtility.createErrorString(e));
		} 
		finally 
		{
			try 
			{
				if (connectionObject != null && !connectionObject.isClosed()) 
				{					
					connectionObject.close();
					connectionObject = null;
				}
			} 
			catch (Exception e) 
			{
				System.out.println("Exception :ITMWizardStatefulEJB :getSalesPersTeamList(String) :==>\n"+ e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append("</Root>\r\n");
		System.out.println("\n****ValueXmlString :" + valueXmlString.toString()	+ ":********");
		return valueXmlString.toString();
	}
	
	
	
	
	
	
	private String getXSLFileName( String xslFileName )throws ITMException
	{   
		String retFileName = null;
		try
		{
			String defaultPath = null;
			if( CommonConstants.APPLICATION_CONTEXT != null )
			{
				defaultPath = CommonConstants.APPLICATION_CONTEXT + CommonConstants.ITM_CONTEXT + File.separator;
			}
			else
			{
				defaultPath = ".." + File.separator + "webapps" + File.separator + "ibase" + File.separator + CommonConstants.ITM_CONTEXT + File.separator;
			}
			File xslPath = new File( defaultPath + File.separator  + "xsl" + File.separator + CommonConstants.THEME + File.separator + "WIZARD");
			if ( !xslPath.exists() )
			{
				xslPath.mkdir();
			}
			System.out.println( " xslPath [" + xslPath +"] xslFileName ["+xslFileName +"]");
			File xslFile = new File(xslPath , xslFileName);
			if( xslFile.exists() )
			{
				retFileName = xslFile.getAbsolutePath();
			}
			else
			{
				throw new ITMException( new Exception( retFileName + " Wizard XSL file Not Found") );	
			}
		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return retFileName;
	}
	
	
	
	
}