package ibase.webitm.ejb.fin;


import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import javax.ejb.Stateless;

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

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.sys.UtilMethods;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

@Stateless

public class CommDrCrIC extends ValidatorEJB implements CommDrCrICLocal,CommDrCrICRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance(); 
	FinCommon finCommon = null;
	public String wfValData() throws RemoteException,ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------@ wfvalData method called-----------------");
		System.out.println("Xml String : ["+xmlString+"]");
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{
			
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
				System.out.println("xmlString d" + xmlString);
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
				System.out.println("xmlString1 f" + xmlString1);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
				System.out.println("xmlString2 f" + xmlString2);
			}			

			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : CommDrCrIC.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			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;
		StringBuffer valueXmlString = new StringBuffer();
		int ctr=0;
		String childNodeName = null;
		String errString = "";
		String sql="";
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		Connection conn = null;
		PreparedStatement pstmt = null ;
		ResultSet rs = null;		
		int cnt=0;
		double linenoinv=0,drcramt=0;
		int currentFormNo=0;
		int childNodeListLength;
		java.util.Date tranDate = null;
		java.util.Date mdate1 = null;
		String empCode="";
		String errorType = "";
		//Timestamp TranDate = null,mdate1=null;
		String Transtr="",siteCode="",mVal="",mVal1="",sundrytype="",sundrycode="",acctcode="",refSer="",cctrcode="",currcode="";
		String finentity="",analcode="",crterm="",drcrfla="",tranSer="";
		ConnDriver connDriver = new ConnDriver();		
		String userId="",errCode="";
		String acctcode1="",analcode1="";
		SimpleDateFormat dateFormat = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		
		StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
        try
		   {
        	
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId);
			finCommon = new FinCommon();
			dateFormat=new SimpleDateFormat(genericUtility.getApplDateFormat());
			
		
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			switch(currentFormNo)
			{
				case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				childNodeListLength = childNodeList.getLength();
			
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("tran_date"))
					{
						//tranDate=dateFormat.parse(this.genericUtility.getColumnValue("tran_date", dom));
						//Transtr = this.genericUtility.getColumnValue("TranDate", dom);
						siteCode =this.genericUtility.getColumnValue("site_code", dom);
						tranDate = dateFormat.parse(this.genericUtility.getColumnValue("tran_date", dom));
						errCode = this.nfCheckPeriod("FIN", tranDate, siteCode);
						if(errCode != null && errCode.trim().length() >0)
						{
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
						System.out.println("@@@@ Tran_date[" + Transtr + "]");
						/*if(tranDate==null)
						{
							errCode = "VMTRANDT";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}*/
					}
					else if (childNodeName.equalsIgnoreCase("invoice_id"))
						
					{
						tranDate=dateFormat.parse(this.genericUtility.getColumnValue("tran_date", dom));
						mVal=genericUtility.getColumnValue("invoice_id", dom);
						//TranDate = Timestamp.valueOf(genericUtility.getValidDateString(Transtr,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat())+ " 00:00:00.0");
						//mdate1=Timestamp.valueOf(genericUtility.getValidDateString(mdate11,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat())+ " 00:00:00.0");
						if (mVal != null && mVal.trim().length() > 0) 
						{
							sql="select count(*) from invoice where invoice_id = ? and confirmed = 'Y'";
							
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, mVal);
							System.out.println("value1111111"+mVal);
							rs = pstmt.executeQuery();

							if (rs.next()) 
							{
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
							if (cnt == 0) 
							{
								errCode = "VMVOUC4";
							}
							else
							{
								sql= "select tran_date from invoice where invoice_id =?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, mVal);
								System.out.println("valueeee22222222222"+mVal);
								rs = pstmt.executeQuery();
								if (rs.next()) 
								{
									mdate1=rs.getTimestamp("tran_date");	
									if (mdate1.after(tranDate))
									{
										errCode = "VTDRCR1";
								
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
										
								}
							}
						}
					}
					else if (childNodeName.equalsIgnoreCase("voucher_no"))
						{
						tranDate=dateFormat.parse(this.genericUtility.getColumnValue("tran_date", dom));
						mVal1=genericUtility.getColumnValue("voucher_no", dom);
						if (mVal1 != null && mVal1.trim().length() > 0) 
						{
						sql="select count(*) from voucher where tran_id = ? and confirmed = 'Y'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, mVal1);
						rs = pstmt.executeQuery();

						if (rs.next()) 
						{
							cnt = rs.getInt(1);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if (cnt == 0) 
						{
							errCode = "VMVOUC4";
						}
						else
						{
							sql= "select tran_date from voucher where tran_id=?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, mVal1);
							rs = pstmt.executeQuery();

							if (rs.next()) 
							{
								mdate1=rs.getTimestamp("tran_date");
								if (mdate1.after(tranDate))
								{
									errCode = "VTDRCR1";
							
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
									
							}
						
						}
						}
				}
					else if(childNodeName.equalsIgnoreCase("sundry_code"))
					{

						sundrycode = genericUtility.getColumnValue("sundry_code", dom);
						if(sundrycode != null && sundrycode.trim().length() >0)
						{
					    siteCode=genericUtility.getColumnValue("site_code", dom);
					    sundrytype=genericUtility.getColumnValue("sundry_type", dom);
					    errCode = finCommon.isSundryCode(siteCode,  sundrytype,  sundrycode, refSer,conn);
						if(errCode != null && errCode.trim().length() > 0)
						{
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
						}
					}
					else if(childNodeName.equalsIgnoreCase("acct_code"))
					{

						/*acctcode = this.genericUtility.getColumnValue("acct_code", dom);
						if(acctcode != null && acctcode.trim().length() >0)
						{
							refSer = this.genericUtility.getColumnValue("ref_ser", dom);
							siteCode = this.genericUtility.getColumnValue("site_code", dom);
							errCode =finCommon.isAnalysis(siteCode, acctcode,refSer, conn);
							if(errCode != null && errCode.trim().length() > 0)
							{
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}*/
						acctcode = this.genericUtility.getColumnValue("acct_code", dom);
						System.out.println("ACCT CODE "+cctrcode);
						if(acctcode != null && acctcode.trim().length() > 0)
						{
							siteCode = this.genericUtility.getColumnValue("site_code", dom);
							//if(acctCode == null || acctCode.trim().length() == 0){acctCode="";}
							errCode = finCommon.isAcctCode(siteCode, acctcode, "FIN", conn);
							if(errCode != null && errCode.trim().length() > 0)
							{
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("cctr_code"))
					{

						/*cctrcode = this.genericUtility.getColumnValue("cctr_code", dom);
						if(cctrcode != null && cctrcode.trim().length() >0)
						{
						acctcode=this.genericUtility.getColumnValue("acct_code", dom);
					    //errCode = this.isCctrCode(acctcode,cctrcode,refSer);
						errCode =finCommon.isAnalysis(acctcode, cctrcode,refSer, conn);
					    
						}*/
						cctrcode = this.genericUtility.getColumnValue("cctr_code", dom);
						System.out.println("CTTC CODE "+cctrcode);
						if(cctrcode != null && cctrcode.trim().length() > 0)
						{
							acctcode = this.genericUtility.getColumnValue("acct_code", dom);
							//if(acctCode == null || acctCode.trim().length() == 0){acctCode="";}
							System.out.println("CTTC CODE 111["+cctrcode+""+acctcode);
							errCode = finCommon.isCctrCode(acctcode, cctrcode, "FIN", conn);
							if(errCode != null && errCode.trim().length() > 0)
							{
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
					   
					}
					}
					else if(childNodeName.equalsIgnoreCase("curr_code"))
					{
						currcode = this.genericUtility.getColumnValue("curr_code", dom);
						System.out.println("Curr code"+currcode);
						if(currcode != null && currcode.trim().length() > 0)
						{
							sql = "select count(*) from currency where curr_code = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, currcode);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt(1);
							}
							if (cnt == 0)
							{
								errCode = "VMCUR1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}

							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						else
						{

							errCode = "VMCURRCD1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}
					else if(childNodeName.equalsIgnoreCase("fin_entity"))
					{
						finentity=this.genericUtility.getColumnValue("fin_entity", dom);
						System.out.println("FIn Entity@@@@@@@@@@@@@@@"+finentity);
						if(finentity != null && finentity.trim().length() > 0)
						{
							sql = "select count(*) from finent where fin_entity = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, finentity);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt(1);
							}
							if (cnt == 0)
							{
								errCode = "VMFENTY1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}					
						
					}
					else if(childNodeName.equalsIgnoreCase("site_code"))
					{
						siteCode=this.genericUtility.getColumnValue("site_code", dom);
						System.out.println("SIte Code"+siteCode);
						if(siteCode != null && siteCode.trim().length() > 0 )
						{
							sql = "select count(*) from site where site_code = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, siteCode);
							rs = pstmt.executeQuery();
							if (rs.next())
							{
								cnt = rs.getInt(1);
							}
							if (cnt == 0)
							{
								errCode = "VMSITE1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							rs.close();
							rs = null;
							pstmt.close();
							pstmt = null;
						}
						else
						{
							errCode = "VMSITSUPP";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
						if ((errCode == null) ||  (errCode.trim().length() == 0))
						{
							System.out.println("prioddddddddd");

							tranDate = dateFormat.parse(this.genericUtility.getColumnValue("tran_date", dom));
							errCode = this.nfCheckPeriod("FIN", tranDate, siteCode);
							if(errCode != null && errCode.trim().length() >0)
							{
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("anal_code"))
					{
						
						analcode = this.genericUtility.getColumnValue("anal_code", dom);
						if(analcode != null && analcode.trim().length() >0)
						{
							refSer = this.genericUtility.getColumnValue("ref_ser", dom);
							acctcode = this.genericUtility.getColumnValue("acct_code", dom);
							errCode =finCommon.isAnalysis(acctcode, analcode,refSer, conn);
							if(errCode != null && errCode.trim().length() > 0)
							{
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("cr_term"))
					{
						crterm = genericUtility.getColumnValue("cr_term", dom);
						sql="select count(*) from crterm where cr_term = ?" ;
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, crterm);
						rs = pstmt.executeQuery();

						if (rs.next()) 
						{
							cnt = rs.getInt(1);
							System.out.println("COUNT VALUEEEEE"+ cnt );
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						if (cnt == 0) 
						{
							errCode = "VMCRTER1";
							errList.add(errCode);
							errFields.add(childNodeName.toLowerCase());
						}
					}

					else if(childNodeName.equalsIgnoreCase("tran_ser"))
					{
						tranSer=genericUtility.getColumnValue("tran_ser", dom);
						drcrfla= genericUtility.getColumnValue("drcr_flag", dom);
						if(tranSer == "CRNINV" && drcrfla=="D")
						{
							errCode = "VTMATCH";
						}
				    }
				}
				valueXmlString.append("</Detail1>");
				break;
				
				case 2 :
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					childNodeListLength = childNodeList.getLength();
					
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						if (childNodeName.equalsIgnoreCase("line_no__inv")) 
						{
							mVal=genericUtility.getColumnValue("invoice_id", dom);	
				
							String lineno = genericUtility.getColumnValue("line_no__inv", dom);
							lineno = lineno == null ? "0" : lineno.trim();
							linenoinv = Double.parseDouble(lineno);
							 sql="select count(*)  from invdet where invoice_id = ?and line_no = ?" ;
							 pstmt = conn.prepareStatement(sql);
							 pstmt.setString(1, mVal);
							 pstmt.setDouble(2, linenoinv);
							 rs = pstmt.executeQuery();

								if (rs.next()) 
								{
									cnt = rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if (cnt == 0) 
								{
									errCode = "VTINVLN1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
						}
						else if(childNodeName.equalsIgnoreCase("drcr_amt"))
						{
							String amt = genericUtility.getColumnValue("drcr_amt", dom);
							amt = amt == null ? "0" : amt.trim();
							drcramt = Double.parseDouble(amt);
							if(drcramt<0)
							{
								errCode = "VMAMT1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
							
						}
						else if(childNodeName.equalsIgnoreCase("acct_code"))
						{
							acctcode = this.genericUtility.getColumnValue("acct_code", dom);
							System.out.println("ACCT CODE "+acctcode);
							if(acctcode != null && acctcode.trim().length() > 0)
							{
								siteCode = this.genericUtility.getColumnValue("site_code", dom1);
								errCode = finCommon.isAcctCode(siteCode, acctcode, "FIN", conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}
						else if(childNodeName.equalsIgnoreCase("cctr_code"))
						{
							cctrcode = this.genericUtility.getColumnValue("cctr_code", dom);
							System.out.println("CTTC CODE "+cctrcode);
							if(cctrcode != null && cctrcode.trim().length() > 0)
							{
								acctcode = this.genericUtility.getColumnValue("acct_code", dom);
								System.out.println("CTTC CODE 111["+cctrcode+""+acctcode);
								errCode = finCommon.isCctrCode(acctcode, cctrcode, "FIN", conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
						   
						}
						}
					}
					valueXmlString.append("</Detail2>");
					break;
					
				case 3 :
					parentNodeList = dom.getElementsByTagName("Detail3");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail3>");
					childNodeListLength = childNodeList.getLength();
					
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
					    if(childNodeName.equalsIgnoreCase("acct_code"))
						{
							acctcode = this.genericUtility.getColumnValue("acct_code", dom);
							System.out.println("ACCT CODE "+acctcode);
							if(acctcode != null && acctcode.trim().length() > 0)
							{
								siteCode = this.genericUtility.getColumnValue("site_code", dom1);
								errCode = finCommon.isAcctCode(siteCode, acctcode, "FIN", conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}

					    else if(childNodeName.equalsIgnoreCase("cctr_code"))
						{
							cctrcode = this.genericUtility.getColumnValue("cctr_code", dom);
							System.out.println("CTTC CODE "+cctrcode);
							if(cctrcode != null && cctrcode.trim().length() > 0)
							{
								acctcode = this.genericUtility.getColumnValue("acct_code", dom);
								System.out.println("CTTC CODE 111["+cctrcode+""+acctcode);
								errCode = finCommon.isCctrCode(acctcode, cctrcode, "FIN", conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
						   
						}
						}
						else if(childNodeName.equalsIgnoreCase("emp_code"))
						{
							refSer = this.genericUtility.getColumnValue("ref_ser", dom);
							empCode = this.genericUtility.getColumnValue("emp_code", dom);
							if(empCode != null && empCode.trim().length() > 0)
							{
								siteCode = this.genericUtility.getColumnValue("site_code", dom1);
								errCode = finCommon.isEmployee(siteCode, empCode, refSer, conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}

						}
						else if(childNodeName.equalsIgnoreCase("anal_code"))
						{
							analcode1 = this.genericUtility.getColumnValue("anal_code", dom);
							if(analcode1 != null && analcode1.trim().length() >0)
							{
								refSer = this.genericUtility.getColumnValue("ref_ser", dom);
								acctcode1 = this.genericUtility.getColumnValue("acct_code", dom);
								errCode =finCommon.isAnalysis(acctcode1, analcode1,refSer, conn);
								if(errCode != null && errCode.trim().length() > 0)
								{
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
						}
						
					}
					valueXmlString.append("</Detail3>");
			} //end switch
			int errListSize = errList.size();
			cnt = 0;
			String errFldName = null;
			System.out.println("errListSize [" + errListSize + "] errFields size [" + errFields.size() + "]");
			if ((errList != null) && (errListSize > 0))
			{
				for (cnt = 0; cnt < errListSize; cnt++)
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println(" cnt [" + cnt + "] errCode [" + errCode + "] errFldName [" + errFldName + "]");
					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;
					}
				}

				errStringXml.append("</Errors> </Root> \r\n");
			}
			else
			{
				errStringXml = new StringBuffer("");
			}

		   }//end try
        
        
        catch (Exception e)
		{
			e.printStackTrace();
			errString = 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();
				throw new ITMException(d);
			}
		}
		errString = errStringXml.toString();
		return errString;
}
	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();
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}
		return msgType;
	}



	/**
	 * @param args
	 */

	public String itemChanged(String xmlString, String xmlString1,String xmlString2, String objContext, String currentColumn,String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("@@@@@@@ CommDrCrIC itemChanged called");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{
			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 :[itemChanged( String, String )] :==>\n"+ e.getMessage());
		}
		return valueXmlString;

	}
	
	public String itemChanged(Document dom, Document dom1, Document dom2,String objContext, String currentColumn, String editFlag,String xtraParams) throws RemoteException, ITMException
	{
		System.out.println("dom["+GenericUtility.getInstance().serializeDom(dom)+"]");
		System.out.println("dom1["+GenericUtility.getInstance().serializeDom(dom1)+"]");
		System.out.println("dom2["+GenericUtility.getInstance().serializeDom(dom2)+"]");
		System.out.println("ObjContext" + objContext);
		System.out.println("Current Column" + currentColumn );
		System.out.println("editFlag " + editFlag);
		System.out.println("xtraParams" +xtraParams);
		
		String colName ="", mCode = "" , mDescr ="", mDescr1="", mDescr2 ="", mDescr3 ="", mDescr4 ="",mDescr5 ="", mDescr6 ="",itemSer ="",sType ="",invId ="", acctCode ="",descr="", mFinent ="", cctrCode ="",tranSer = "";
		//String ls_var[] =,
		String taxClass ="",taxEnv ="" ,taxChap ="" ,itemCode = "";
		String exchRateStr = "",amountStr = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String  errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		int childNodeListLength = 0;
		double mNum =0d, drCr = 0d, tax = 0d, Net = 0d,exchRate = 0d;
		int crDays=0,lineInv = 0;
		long lineNo = 0;
		int currentFormNo = 0;
		int ctr = 0;
		Connection conn = null;
		java.util.Date tranDate = null,DueDate = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		UtilMethods utilMethods = UtilMethods.getInstance();
		String childNodeName = null;
		StringBuffer valueXmlString = new StringBuffer();
		SimpleDateFormat dateFormat = null;	
		//Date TranDate =null, DueDate = null, Today =null;
		String mdrcrFlag = "C";
    	 String sql = "",sql1 = "",sql2 = "",sql3 = "";
    		  ConnDriver connDriver = new ConnDriver();
    		  try
    		  {
    			  Calendar currentDate = Calendar.getInstance();
    				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
    				String today = sdf.format(currentDate.getTime());	
    				dateFormat=new SimpleDateFormat(genericUtility.getApplDateFormat());
    		  //colName = lower(as_focus_col)
    			  
    			  conn = connDriver.getConnectDB("DriverITM");
    				conn.setAutoCommit(false);
    				connDriver = null;
    				  
    				//today = new java.util.Date(System.currentTimeMillis());
    				  
    				  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>");
    					switch(currentFormNo)
    					{
    					
    					case 1:
    						parentNodeList = dom.getElementsByTagName("Detail1");
    						parentNode = parentNodeList.item(0);
    						childNodeList = parentNode.getChildNodes();
    						valueXmlString.append("<Detail1>");
    						childNodeListLength = childNodeList.getLength();
    						do
    						{   
    							childNode = childNodeList.item(ctr);
    							childNodeName = childNode.getNodeName();
    							ctr ++;
    						}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
    						
    						if(currentColumn.trim().equalsIgnoreCase("itm_default"))
    						{
    							valueXmlString.append("<drcr_flag>").append("<![CDATA[" + mdrcrFlag +"]]>").append("</drcr_flag>");
    							valueXmlString.append("<tran_ser>").append("<![CDATA["+ tranSer + " ]]>").append("</tran_ser>");
    							valueXmlString.append("<tran_date><![CDATA[").append(checkNull(today)).append("]]></tran_date>\r\n");
    							valueXmlString.append("<eff_date>").append("<![CDATA[" + today + "]]>").append("</eff_date>");
    							valueXmlString.append("<voucher_no protect='1'>").append("<![CDATA[]]>").append("</voucher_no>");
    							valueXmlString.append("<invoice_id protect='0'>").append("<![CDATA[]]>").append("</invoice_id>");
    						}
    						if(currentColumn.trim().equalsIgnoreCase("itm_default_edit"))
    						{
    							
    							valueXmlString.append("<voucher_no protect='1'>").append("<![CDATA[]]>").append("</voucher_no>");
    							valueXmlString.append("<invoice_id protect='0'>").append("<![CDATA[]]>").append("</invoice_id>");
    						}
    						if( currentColumn.trim().equalsIgnoreCase( "invoice_id" ) )
    						{
    							mCode  = checkNull(genericUtility.getColumnValue("invoice_id", dom));
    							sql="select cust_code, site_code, acct_code__ar, cctr_code__ar,fin_entity, anal_code, curr_code, " +
    									"exch_rate, item_ser from invoice where invoice_id = ? AND confirmed = 'Y'";
    							pstmt =  conn.prepareStatement(sql);
    							pstmt.setString(1,mCode);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr  = rs.getString("cust_code");
    								mDescr1 = rs.getString("site_code");
    								mDescr2 = rs.getString("acct_code__ar");
    								mDescr3 = rs.getString("cctr_code__ar");
    								mDescr4 = rs.getString("fin_entity");
    								mDescr5 = checkNull(rs.getString("anal_code"));
    								mDescr6 = rs.getString("curr_code");
    								mNum    = rs.getDouble("exch_rate");
    								itemSer = rs.getString("item_ser");
    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							
    							valueXmlString.append("<site_code>").append("<![CDATA[" + mDescr1 +"]]>").append("</site_code>");
    							valueXmlString.append("<acct_code>").append("<![CDATA[" + mDescr2 +"]]>").append("</acct_code>");
    							valueXmlString.append("<cctr_code>").append("<![CDATA[" + mDescr3 +"]]>").append("</cctr_code>");
    							valueXmlString.append("<fin_entity>").append("<![CDATA["+ mDescr4 +"]]>").append("</fin_entity>");
    							valueXmlString.append("<anal_code>").append("<![CDATA[" + mDescr5 +"]]>").append("</anal_code>");
    							valueXmlString.append("<curr_code>").append("<![CDATA[" + mDescr6 +"]]>").append("</curr_code>");
    							valueXmlString.append("<exch_rate>").append("<![CDATA[" + mNum     +"]]>").append("</exch_rate>");
    							valueXmlString.append("<item_ser>").append("<![CDATA[" +  itemSer  +"]]>").append("</item_ser>");
    							
    							sql1=" select cust_name,cr_term from customer where cust_code = ?";
    							pstmt =  conn.prepareStatement(sql1);
    							pstmt.setString(1,mDescr);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr1  = rs.getString("cust_name");
    								mDescr5 = rs.getString("cr_term");
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							valueXmlString.append("<cr_term>").append("<![CDATA[" +  mDescr5  +"]]>").append("</cr_term>");
    							
    							sql2="select descr,cr_days from crterm where cr_term =? ";
    							pstmt =  conn.prepareStatement(sql2);
    							pstmt.setString(1,mDescr5);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr3  = rs.getString("descr");
    								crDays   = rs.getInt("cr_days");
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							tranDate = dateFormat.parse(genericUtility.getColumnValue("eff_date", dom));
    							DueDate =utilMethods.RelativeDate(tranDate,crDays);
    							valueXmlString.append("<crterm_descr>").append("<![CDATA[" +  mDescr3  +"]]>").append("</crterm_descr>");
    							valueXmlString.append("<due_date>").append("<![CDATA[" + sdf.format(DueDate) +"]]>").append("</due_date>");
    							
    							sql3="select descr from accounts where acct_code = ?";
    							pstmt =  conn.prepareStatement(sql3);
    							pstmt.setString(1,mDescr2);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr1  = rs.getString("descr");
    								   								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							valueXmlString.append("<accounts_descr>").append("<![CDATA[" +  mDescr1  +"]]>").append("</accounts_descr>");
    							
    						}
    						if( currentColumn.trim().equalsIgnoreCase( "voucher_no" ) )
    						{
    							mCode  = checkNull(genericUtility.getColumnValue("voucher_no", dom));
    							sql="select supp_code, site_code, acct_code," +
    								"cctr_code,fin_entity, anal_code, curr_code, exch_rate from voucher where tran_id =? and confirmed = 'Y' ";
    							pstmt =  conn.prepareStatement(sql);
    							pstmt.setString(1,mCode);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr  = rs.getString("supp_code");
    								mDescr1 = rs.getString("site_code");
    								mDescr2 = rs.getString("acct_code");
    								mDescr3 = rs.getString("cctr_code");
    								mDescr4 = rs.getString("fin_entity");
    								mDescr5 = checkNull(rs.getString("anal_code"));
    								mDescr6 = rs.getString("curr_code");
    								mNum    = rs.getInt("exch_rate");
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							
    							valueXmlString.append("<site_code>").append("<![CDATA[" +  mDescr1  +"]]>").append("</site_code>");
    							valueXmlString.append("<acct_code>").append("<![CDATA[" +  mDescr2  +"]]>").append("</acct_code>");
    							valueXmlString.append("<cctr_code>").append("<![CDATA[" +  mDescr3  +"]]>").append("</cctr_code>");
    							valueXmlString.append("<fin_entity>").append("<![CDATA[" + mDescr4  +"]]>").append("</fin_entity>");
    							valueXmlString.append("<anal_code>").append("<![CDATA[" +  mDescr5  +"]]>").append("</anal_code>");
    							valueXmlString.append("<curr_code>").append("<![CDATA[" +  mDescr5  +"]]>").append("</curr_code>");
    							valueXmlString.append("<exch_rate>").append("<![CDATA[" +  mDescr5  +"]]>").append("</exch_rate>");
    							
    							
    							sql1="select supp_name,cr_term from supplier where supp_code = ?";
    							pstmt =  conn.prepareStatement(sql1);
    							pstmt.setString(1,mDescr);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr1  = rs.getString("supp_name");
    								mDescr5 = rs.getString("cr_term");
    								
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							valueXmlString.append("<cr_term>").append("<![CDATA[" +  mDescr5  +"]]>").append("</cr_term>");
    							
    							
    							sql2="select descr,cr_days from crterm where cr_term = ?";
    							pstmt =  conn.prepareStatement(sql2);
    							pstmt.setString(1,mDescr5);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr3  = rs.getString("descr");
    								crDays   = rs.getInt("cr_days");
    								
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							tranDate = dateFormat.parse(genericUtility.getColumnValue("eff_date", dom));
    							DueDate =relativeDate(tranDate,crDays);
    							valueXmlString.append("<crterm_descr>").append("<![CDATA[" +  mDescr3  +"]]>").append("</crterm_descr>");
    							valueXmlString.append("<due_date>").append("<![CDATA[" + sdf.format(DueDate) +"]]>").append("</due_date>");
    							
    							sql3="select descr from accounts where acct_code = ?";
    							pstmt =  conn.prepareStatement(sql3);
    							pstmt.setString(1,mDescr2);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr1  = rs.getString("descr");
    								    								
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							valueXmlString.append("<accounts_descr>").append("<![CDATA[" +  mDescr1  +"]]>").append("</accounts_descr>");
    							
    							
    						}
    						
    						if( currentColumn.trim().equalsIgnoreCase( "tran_date" ) )
    						{
    							tranDate = dateFormat.parse(genericUtility.getColumnValue("eff_date", dom));
    							valueXmlString.append("<eff_date>").append("<![CDATA[" +  sdf.format(tranDate)  +"]]>").append("</eff_date>");
    						}
    							
    						
    						if( currentColumn.trim().equalsIgnoreCase( "eff_date" ) )
    						{
    							mDescr5  = checkNull(genericUtility.getColumnValue("cr_term", dom));
    							sql="select cr_days from crterm where cr_term = ?";
    							pstmt =  conn.prepareStatement(sql);
    							pstmt.setString(1,mDescr5);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								crDays  = rs.getInt("cr_days");
    								    								
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							tranDate = dateFormat.parse(genericUtility.getColumnValue("eff_date", dom));
                                System.out.println("Effffffffffffffffffffffff Date"+tranDate);
                                
                              //  DueDate =relativeDate(TranDate,crDays);
                                DueDate=utilMethods.RelativeDate(tranDate,crDays);
                                System.out.println("DUeDate is"+DueDate);
                                valueXmlString.append("<crterm_descr>").append("<![CDATA[" +  mDescr3  +"]]>").append("</crterm_descr>");
                                valueXmlString.append("<due_date>").append("<![CDATA[" + sdf.format(DueDate) +"]]>").append("</due_date>");
    							//tranDate = genericUtility.getDateTimeObject("eff_date");
    							//DueDate =relativeDate(tranDate,crDays);
    							//valueXmlString.append("<due_date>").append("<![CDATA[" +  DueDate  +"]]>").append("</due_date>");
    						}
    						if( currentColumn.trim().equalsIgnoreCase( "acct_code" ) )
    						{
    							mCode  = checkNull(genericUtility.getColumnValue("acct_code", dom));
    							sql="select descr from accounts where acct_code = ?";
    							pstmt =  conn.prepareStatement(sql);
    							pstmt.setString(1,mCode);
    							rs = pstmt.executeQuery();
    							if(rs.next())
    							{
    								mDescr  = rs.getString("descr");
    								    								
    								    								
    							}
    							rs.close();
    							rs = null;
    							pstmt.close();
    							pstmt = null;
    							valueXmlString.append("<accounts_descr>").append("<![CDATA[" +  mDescr  +"]]>").append("</accounts_descr>");
    						}
    							if( currentColumn.trim().equalsIgnoreCase( "cr_term" ) )
        						{
        							mCode  = checkNull(genericUtility.getColumnValue("cr_term", dom));
        							sql="select descr,cr_days from crterm where cr_term =?";
        							pstmt =  conn.prepareStatement(sql);
        							pstmt.setString(1,mCode);
        							rs = pstmt.executeQuery();
        							if(rs.next())
        							{
        								mDescr  = rs.getString("descr");
        								crDays  = rs.getInt("cr_days");
        								
        								    								
        								    								
        							}
        							rs.close();
        							rs = null;
        							pstmt.close();
        							pstmt = null;
        							tranDate = dateFormat.parse(genericUtility.getColumnValue("eff_date", dom));
                                    System.out.println("Effffffffffffffffffffffff Date"+tranDate);
                                    
                                  //  DueDate =relativeDate(TranDate,crDays);
                                    DueDate=utilMethods.RelativeDate(tranDate,crDays);
                                    System.out.println("DUeDate is"+DueDate);
        							/*tranDate = genericUtility.getDateTimeObject("eff_date");
        							DueDate =relativeDate(tranDate,crDays);*/
        							valueXmlString.append("<crterm_descr>").append("<![CDATA[" +  mDescr  +"]]>").append("</crterm_descr>");
        							valueXmlString.append("<due_date>").append("<![CDATA[" + sdf.format(DueDate) +"]]>").append("</due_date>");
    							
                
        						}
    							if( currentColumn.trim().equalsIgnoreCase( "amount" ) )
    							{
    								amountStr  = (genericUtility.getColumnValue("amount", dom));
    								mNum = amountStr == null ? 0: Double.parseDouble(amountStr);
    								exchRateStr= (genericUtility.getColumnValue("exch_rate", dom));
    								exchRate = exchRateStr == null ? 0: Double.parseDouble(exchRateStr);
    								mNum = mNum * exchRate;
        							sql="select descr,cr_days from crterm where cr_term =?";
        							pstmt =  conn.prepareStatement(sql);
        							pstmt.setString(1,mCode);
        							rs = pstmt.executeQuery();
        							if(rs.next())
        							{
        								mDescr  = rs.getString("descr");
        								crDays  = rs.getInt("cr_days");
        								
        								    								
        								    								
        							}
        							rs.close();
        							rs = null;
        							pstmt.close();
        							pstmt = null;
        							valueXmlString.append("<amount__bc>").append("<![CDATA[" +  mNum  +"]]>").append("</amount__bc>");
        							
    							}
    							if( currentColumn.trim().equalsIgnoreCase( "sundry_code" ) )
    							{
    								mCode  = (genericUtility.getColumnValue("sundry_code", dom));
    								sType  = (genericUtility.getColumnValue("sundry_type", dom));
    								
    								if(sType.trim().equalsIgnoreCase("P"))
    								{
    									sql = "select acct_code__ap, cctr_code__ap from sales_pers where sales_pers =?";
    									pstmt =  conn.prepareStatement(sql);
    									pstmt.setString(1,mCode);
            							rs = pstmt.executeQuery();
            							if(rs.next())
            							{
            								acctCode  = rs.getString("acct_code__ap");
            								cctrCode = rs.getString("cctr_code__ap");
            								
            								    								
            								    								
            							}
            							rs.close();
            							rs = null;
            							pstmt.close();
            							pstmt = null;
    								} 
    								
    								sql1="SELECT FN_SUNDRY_NAME (?, ? , 'N') AS SUNDRY_NAME FROM DUAL";
    								pstmt = conn.prepareStatement(sql1);
    								pstmt.setString(1,sType);
    								pstmt.setString(2,mCode);
    								rs=pstmt.executeQuery();
    								if(rs.next())
    								{
    									mDescr=checkNull(rs.getString("sundry_name"));
    								}
    								rs.close();
    								rs=null;
    								pstmt.close();
    								pstmt=null;
    								valueXmlString.append("<sundry_name>").append("<![CDATA[" +  mDescr  +"]]>").append("</sundry_name>");
    										
    							
    								
    							}
    							if(currentColumn.trim().equalsIgnoreCase( "site_code" ))
    							{
    								mCode  = (genericUtility.getColumnValue("site_code", dom));
    								sql= "select fin_entity from site where site_code = ?";
    								pstmt = conn.prepareStatement(sql);
    								pstmt.setString(1,mCode);
    								
    								rs=pstmt.executeQuery();
    								if(rs.next())
    								{
    									mFinent=rs.getString("fin_entity");
    								}
    								rs.close();
    								rs=null;
    								pstmt.close();
    								pstmt=null;
    								
    								valueXmlString.append("<fin_entity>").append("<![CDATA[" +  mFinent  +"]]>").append("</fin_entity>");
    							}
    							valueXmlString.append("</Detail1>");
    						break;//end case1
    						
    					case 2:
    						parentNodeList = dom.getElementsByTagName("Detail2");
    						parentNode = parentNodeList.item(0);
    						childNodeList = parentNode.getChildNodes();
    						valueXmlString.append("<Detail2>");
    						childNodeListLength = childNodeList.getLength();
    						do
    						{   
    							childNode = childNodeList.item(ctr);
    							childNodeName = childNode.getNodeName();
    							ctr ++;
    						}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
    						
    						if(currentColumn.trim().equalsIgnoreCase("itm_default"))
    						{
    							valueXmlString.append("<voucher_no protect='1'>").append("<![CDATA["+""+"]]>").append("</voucher_no>");
    							valueXmlString.append("<invoice_id protect='0'>").append("<![CDATA["+""+"]]>").append("</invoice_id>");
    							
    							//String lineNoStr  =(genericUtility.getColumnValue("line_no", dom));
    							//lineNo = lineNoStr == null ? 0: Long.parseLong(lineNoStr);
    							/*if(lineNo>0)
    							{
    								valueXmlString.append("<line_no>").append("<![CDATA["+  lineNo  +"]]>").append("</line_no>");
    							}
    							valueXmlString.append("<tran_id>").append("<![CDATA[" + checkNull(genericUtility.getColumnValue("tran_id", dom)) + "]]>").append("</tran_id>");*/
    							valueXmlString.append("<invoice_id>").append("<![CDATA[" + checkNull(genericUtility.getColumnValue("invoice_id", dom)) + "]]>").append("</invoice_id>");
    							valueXmlString.append("<exch_rate__inv>").append("<![CDATA[" + checkNull(genericUtility.getColumnValue("exch_rate", dom)) + "]]>").append("</exch_rate__inv>");
    							
    						}
    						if(currentColumn.trim().equalsIgnoreCase("itm_defaultedit"))
    						{
    							valueXmlString.append("<voucher_no protect='1'>").append("<![CDATA["+""+"]]>").append("</voucher_no>");
    							valueXmlString.append("<invoice_id protect='0'>").append("<![CDATA["+""+"]]>").append("</invoice_id>");
    						}
    						if(currentColumn.trim().equalsIgnoreCase("line_no__inv"))
    						{
    							if(tranSer == "CRNINV")
    							{
    								invId  = (genericUtility.getColumnValue("invoice_id", dom));
    								String lineInvStr  = (genericUtility.getColumnValue("line_no__inv", dom));
    								lineInv = lineInvStr == null ? 0: Integer.parseInt(lineInvStr);
    								sql="select item_code, tax_class, tax_chap, tax_env from invdet where invoice_id = ? and line_no = ? ";
    								pstmt = conn.prepareStatement(sql);
    								pstmt.setString(1,invId);
    								pstmt.setInt(2,lineInv);
    								rs=pstmt.executeQuery();
    								if(rs.next())
    								{
    									itemCode = rs.getString("item_code");
    									taxClass = rs.getString("tax_class");
    									taxChap  = rs.getString("tax_chap");
    									taxEnv   = rs.getString("tax_env");
    								}
    								rs.close();
    								rs=null;
    								pstmt.close();
    								pstmt=null;
    							}
    							else
    							{
    								invId  = (genericUtility.getColumnValue("voucher_no", dom));
    								String lineInvStr  = (genericUtility.getColumnValue("line_no__inv", dom));
    								lineInv = lineInvStr == null ? 0: Integer.parseInt(lineInvStr);
    								sql1="select item_code, tax_class, tax_chap, tax_env from vouchrcp where tran_id = ? and line_no = ?";
    								pstmt = conn.prepareStatement(sql1);
    								pstmt.setString(1,invId);
    								pstmt.setInt(2,lineInv);
    								rs=pstmt.executeQuery();
    								if(rs.next())
    								{
    									itemCode = rs.getString("item_code");
    									taxClass = rs.getString("tax_class");
    									taxChap  = rs.getString("tax_chap");
    									taxEnv   = rs.getString("tax_env");
    								}
    								rs.close();
    								rs=null;
    								pstmt.close();
    								pstmt=null;
    								
    							}
    							valueXmlString.append("<item_code>").append("<![CDATA[" +  itemCode  +"]]>").append("</item_code>");
    							valueXmlString.append("<tax_class>").append("<![CDATA[" +  taxClass  +"]]>").append("</tax_class>");
    							valueXmlString.append("<tax_chap>").append("<![CDATA[" +   taxChap  +"]]>").append("</tax_chap>");
    							valueXmlString.append("<tax_env>").append("<![CDATA[" +    taxEnv  +"]]>").append("</tax_env>");
    							
    							
    						}
    						if(currentColumn.trim().equalsIgnoreCase("tax_amt"))
    						{
    							String drCrStr  = (genericUtility.getColumnValue("lc_drcr", dom));
    							drCr = drCrStr == null ? 0: Double.parseDouble(drCrStr);
    							String taxStr = (genericUtility.getColumnValue("tax_amt", dom));
    							tax = taxStr == null ? 0:Double.parseDouble(taxStr);
    							Net = tax+drCr;
    							valueXmlString.append("<net_amt>").append("<![CDATA[" +  Net  +"]]>").append("</net_amt>");
    						}
    						if(currentColumn.trim().equalsIgnoreCase("acct_code"))
    						{
    							acctCode  = (genericUtility.getColumnValue("acct_code", dom));
    							sql="Select descr from accounts where acct_code = ?";
    							pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,acctCode);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									descr = rs.getString("descr");
									
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;
								valueXmlString.append("<accounts_descr>").append("<![CDATA[" +  descr  +"]]>").append("</accounts_descr>");
    							
    						}
    						if(currentColumn.trim().equalsIgnoreCase("cctr_code"))
    						{
    							mCode  = (genericUtility.getColumnValue("cctr_code", dom));
    							sql="select descr  from costctr where cctr_code = ?";
    							pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,mCode);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									mDescr = rs.getString("descr");
									
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;
								valueXmlString.append("<costctr_descr>").append("<![CDATA[" +  mDescr  +"]]>").append("</costctr_descr>");
    							
    						}
    						valueXmlString.append("</Detail2>");
    						break;//end case 2
    						
    						
    					case 3:
    						parentNodeList = dom.getElementsByTagName("Detail3");
    						parentNode = parentNodeList.item(0);
    						childNodeList = parentNode.getChildNodes();
    						valueXmlString.append("<Detail3>");
    						childNodeListLength = childNodeList.getLength();
    						do
    						{   
    							childNode = childNodeList.item(ctr);
    							childNodeName = childNode.getNodeName();
    							ctr ++;
    						}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));
    						
    						if(currentColumn.trim().equalsIgnoreCase("acct_code"))
    						{
    							
    							mCode  = (genericUtility.getColumnValue("acct_code", dom));
    							sql="select descr  from accounts where acct_code = ?";
    							pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,mCode);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									mDescr = rs.getString("descr");
									
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;
								valueXmlString.append("<accounts_descr>").append("<![CDATA[" +  mDescr  +"]]>").append("</accounts_descr>");
    						}
    						valueXmlString.append("</Detail3>");
    						break;//end of case 3
    					}
    						
    					valueXmlString.append("</Root>");
    					}
    		  catch(Exception e)
  			{
  				e.printStackTrace();
  				System.out.println("Exception ::"+ e.getMessage());
  				throw new ITMException(e);
  				
  			}
    		  finally {
    				try {
    					if (conn != null) {
    						if (rs != null) {
    							rs.close();
    							rs = null;
    						}
    						if (pstmt != null) {
    							pstmt.close();
    							pstmt = null;

    						}
    						conn.close();
    					}
    					conn = null;
    				} catch (Exception d) {
    					d.printStackTrace();
    				}
    			}
    			return valueXmlString.toString();
	}
	
    	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	public static java.util.Date relativeDate(java.util.Date date, int days) 
	{
		java.util.Date calculatedDate = null;
		if (date != null) 
		{
			final GregorianCalendar calendar = new GregorianCalendar();
			calendar.setTime(date);
			calendar.add(Calendar.DATE,days);
			calculatedDate = new java.util.Date(calendar.getTime().getTime());
		}		
		return calculatedDate;
	}
}
		// TODO Auto-generated method stub
		
	



