/********************************************************
	Title : EcollectionIC[F14ISUN014]
	Date  : 17/02/15
	Developer: Chandrashekar

 ********************************************************/
package ibase.webitm.ejb.fin;

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

import java.text.SimpleDateFormat;
import java.util.*;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import ibase.utility.E12GenericUtility;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.ejb.Stateless; 
@Stateless 

public class EcollectionIC extends ValidatorEJB implements EcollectionICLocal, EcollectionICRemote
{
	E12GenericUtility genericUtility= new  E12GenericUtility();

	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
		{
			if (xmlString != null && xmlString.trim().length() > 0 )
			{
				dom = parseString(xmlString);
				System.out.println("xmlString["+xmlString+"]");
			}
			if (xmlString1 != null && xmlString1.trim().length() > 0 )
			{
				dom1 = parseString(xmlString1);
				System.out.println("xmlString1["+xmlString1+"]");
			}
			if (xmlString2 != null && xmlString2.trim().length() > 0 )
			{
				dom2 = parseString(xmlString2);
				System.out.println("xmlString2["+xmlString2+"]");				
			}
			
			errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [EcollectionIC][wfValData( String, String )] :==>\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
	{
			String childNodeName = null;
			String errString = "";
			String errCode = "";
			String userId = "";
			String sql = "";
			String errorType = "";
			String custCode="",bankCode="",lineNo="";
			int cnt = 0;
			int ctr=0;
			int childNodeListLength;
			NodeList parentNodeList = null;
			NodeList childNodeList = null;	
			Node parentNode = null;
			Node childNode = null;
			ArrayList<String> errList = new ArrayList<String>();
			ArrayList<String> errFields = new ArrayList<String>();
			Connection conn = null;
			PreparedStatement pstmt = null ;
			ResultSet rs = null;
			ConnDriver connDriver = new ConnDriver();
			StringBuffer errStringXml = new StringBuffer("<?xml version = \"1.0\"?> \r\n <Root> <Errors>");
			int currentFormNo =0;
			String siteCode="",rcpAmtStr="",refSer="",refNo="",prdCode="";
			java.util.Date tranDate = null;
			double rcpAmt=0,rcpAmtNew=0;
			double  isInvAmt=0;
			SimpleDateFormat dateFormat2 = null;
			try
			{	dateFormat2 = new SimpleDateFormat(genericUtility.getApplDateFormat());
				System.out.println("@@@@@@@@ wfvaldata called");
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				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("site_code"))
						{
							siteCode = this.genericUtility.getColumnValue("site_code", dom);
							if(siteCode != null && siteCode.trim().length() > 0 )
							{
								sql = "select count(*) from site where site_code = ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, siteCode.trim());
								rs = pstmt.executeQuery();
								if (rs.next())
								{
									cnt = rs.getInt(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								if (cnt == 0)
								{
									errCode = "VMSITE1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								if(genericUtility.getColumnValue("eff_date", dom) == null)
								{
									tranDate = null;
								}
								else
								{
									tranDate = dateFormat2.parse(genericUtility.getColumnValue("eff_date",dom));

									sql = "select code from period where ?  between fr_date and to_date";
									pstmt =  conn.prepareStatement(sql);
									pstmt.setDate(1,new java.sql.Date(tranDate.getTime()));
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										prdCode =  rs.getString(1);

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

									sql = "select count(*) from period_stat where site_code = ? and prd_code = ? ";
									pstmt =  conn.prepareStatement(sql);
									pstmt.setString(1,siteCode);
									pstmt.setString(2,prdCode);
									rs = pstmt.executeQuery();
									if(rs.next())
									{
										cnt =  rs.getInt(1);

									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
									if(cnt == 0)
									{
										errCode = "VTSITEPD";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
									errCode = nfCheckPeriod("FIN",tranDate,siteCode);
									if(errCode != null && errCode.trim().length() > 0)
									{
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								
								}
							}
							else
							{
								errCode = "VMSITENUL";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else if (childNodeName.equalsIgnoreCase("cust_code"))
						{
							custCode = this.genericUtility.getColumnValue("cust_code", dom);
							if(custCode != null && custCode.trim().length() > 0 )
							{
								sql = "SELECT COUNT(*) AS COUNT FROM CUSTOMER WHERE CUST_CODE = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,custCode);
								rs = pstmt.executeQuery();
								if( rs.next())
								{
									cnt = rs.getInt("COUNT");
								}
								rs.close(); 
								rs = null;
								pstmt.close();
								pstmt = null;
								if( cnt == 0 )
								{
									errCode = "VTCUSTCD1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								
							}

							else
							{
								errCode = "VMCUSTCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
						else if (childNodeName.equalsIgnoreCase("bank_code"))
						{
							bankCode = this.genericUtility.getColumnValue("bank_code", dom);
							if(bankCode != null && bankCode.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) AS COUNT FROM BANK WHERE BANK_CODE = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,bankCode);
								rs = pstmt.executeQuery();
								if( rs.next())
								{
									cnt = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if( cnt == 0 )
								{
									errCode = "VMBANKMIS1";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								
							}
							else
							{
								errCode = "VMBANKCD1";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}
				}// end for
					break;  // case 1 end
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					childNodeListLength = childNodeList.getLength();
					System.out.println("@@@@@@@@@@@@childNodeListLength["+childNodeListLength+"]");
					for(ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = childNodeList.item(ctr);
						childNodeName = childNode.getNodeName();
						 if (childNodeName.equalsIgnoreCase("ref_ser"))
						{
							refSer = this.genericUtility.getColumnValue("ref_ser", dom);
							if(refSer != null && refSer.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) AS COUNT FROM RECEIVABLES WHERE TRAN_SER = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,refSer);
								rs = pstmt.executeQuery();
								if( rs.next())
								{
									cnt = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								
								if( cnt == 0 )
								{
									errCode = "VTINVRESER";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
							}
							else
							{
								errCode = "VTNULRESER";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else if (childNodeName.equalsIgnoreCase("ref_no"))
						{
							refNo = this.genericUtility.getColumnValue("ref_no", dom);
							lineNo = genericUtility.getColumnValue("line_no",dom);
							refSer = genericUtility.getColumnValue("ref_ser",dom);
							if(refNo != null && refNo.trim().length() > 0)
							{
								sql = "SELECT COUNT(*) AS COUNT FROM RECEIVABLES WHERE REF_NO = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,refNo);
								rs = pstmt.executeQuery();
								if( rs.next())
								{
									cnt = rs.getInt("COUNT");
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								if( cnt == 0 )
								{
									errCode = "VTINVREFNO";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								else
								{
									if (isDulplicateFrmDom(dom2,refNo,lineNo,refSer))
									{
										errCode = "VTDUPREFNO";
										errList.add(errCode);
										errFields.add(childNodeName.toLowerCase());
									}
								}
								
							}
							else
							{
								errCode = "VTNULREFNO";
								errList.add(errCode);
								errFields.add(childNodeName.toLowerCase());
							}
						}else if (childNodeName.equalsIgnoreCase("rcp_amt"))
						{
								rcpAmtStr = genericUtility.getColumnValue("rcp_amt",dom);
								refSer = genericUtility.getColumnValue("ref_ser",dom);
								refNo = genericUtility.getColumnValue("ref_no",dom);
								lineNo = genericUtility.getColumnValue("line_no",dom);
								custCode = this.genericUtility.getColumnValue("cust_code", dom1);
								if (rcpAmtStr != null && rcpAmtStr.trim().length() > 0)
								{
									try
									{
										rcpAmtNew = Double.parseDouble(rcpAmtStr);
									} catch (NumberFormatException n)
									{
										rcpAmtNew = 0;
									}
								}
								System.out.println("rcpAmtNew["+rcpAmtNew+"]");
								if(rcpAmtNew <= 0)
								{
									errCode = "VTRCPAMT";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
								
								sql = "SELECT (TOT_AMT - ADJ_AMT) AS AMT FROM RECEIVABLES WHERE TRAN_SER = ? AND REF_NO = ?  AND cust_code = ? " ;
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1,checkNull(genericUtility.getColumnValue("ref_ser",dom)));
								pstmt.setString(2,checkNull(genericUtility.getColumnValue("ref_no",dom)));
								pstmt.setString(3,custCode);
								rs = pstmt.executeQuery();
								if( rs.next())
								{
									rcpAmt=rs.getDouble(1);
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
								System.out.println("rcpAmtNew>>>["+rcpAmtNew+"]rcpAmt["+rcpAmt+"]");
								if(rcpAmt < rcpAmtNew)
								{
									errCode = "VTINVTLAMT";
									errList.add(errCode);
									errFields.add(childNodeName.toLowerCase());
								}
						}
					} // end for
					break;  // case 2 end
				}		
	
				int errListSize = errList.size();
				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)
			{
				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;
		}

	//end of validation

	// 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;
		String valueXmlString = "";
		System.out.println("xmlString............."+xmlString);
		System.out.println("xmlString1............"+xmlString);
		System.out.println("xmlString2............"+xmlString);
		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 : [EcollectionIC][itemChanged( String, String )] :==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		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 sql = "";
	StringBuffer valueXmlString = new StringBuffer();
	int ctr = 0;
	NodeList parentNodeList = null;
	NodeList childNodeList = null;
	Node parentNode = null;
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null ;
	E12GenericUtility genericUtility= new  E12GenericUtility();
	ConnDriver connDriver = new ConnDriver();
	
	int currentFormNo =0, childNodeListLength=0;
	String columnValue="";
	
	String loginSite="",loginSiteDesc="",siteCodeDesc="",hdrCustCode="",hdrSiteCode="";
	String bankCode="",siteCode="",custCode="",bankName="",custName="",refNo="",refSer="";
	double rcpAmt1=0;
	
	try
	{
		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
		String sysDate = sdf.format(currentDate.getTime());		
		conn = connDriver.getConnectDB("DriverITM");
		conn.setAutoCommit(false);
		connDriver = null;

		if(objContext != null && objContext.trim().length()>0)
		{
			currentFormNo = Integer.parseInt(objContext.trim());
		}
		
		System.out.println("Now the date is :=>  " + sysDate);

		valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
		valueXmlString.append(editFlag).append("</editFlag> </header>");

		System.out.println("**********ITEMCHANGE FOR CASE"+currentFormNo+"**************");
		switch(currentFormNo)
		{
		case 1 :
			parentNodeList = dom.getElementsByTagName("Detail1");
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			ctr = 0; 
			childNodeListLength = childNodeList.getLength();
			System.out.println("currentColumn[" + currentColumn + "]columnValue ==> '" + columnValue + "'");
			valueXmlString.append("<Detail1>");
			if ( currentColumn.trim().equalsIgnoreCase( "itm_default" ))
				{
					loginSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSite");

					sql = "SELECT DESCR,BANK_CODE FROM SITE WHERE SITE_CODE = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, loginSite);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						loginSiteDesc = rs.getString("DESCR");
						bankCode = rs.getString("BANK_CODE");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					sql = "SELECT BANK_NAME FROM BANK WHERE BANK_CODE =?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, bankCode);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						bankName = checkNull(rs.getString("BANK_NAME"));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append("<tran_date><![CDATA[").append(checkNull(sysDate)).append("]]></tran_date>\r\n");
					valueXmlString.append("<eff_date><![CDATA[").append(checkNull(sysDate)).append("]]></eff_date>\r\n");
					valueXmlString.append("<site_code><![CDATA[").append(loginSite).append("]]></site_code>\r\n");
					valueXmlString.append("<descr><![CDATA[").append(loginSiteDesc).append("]]></descr>\r\n");
					valueXmlString.append("<bank_code><![CDATA[").append(bankCode).append("]]></bank_code>\r\n");
					valueXmlString.append("<status><![CDATA[").append("E").append("]]></status>\r\n");
					valueXmlString.append("<tran_type><![CDATA[").append("E").append("]]></tran_type>\r\n");
					valueXmlString.append("<bank_name>").append("<![CDATA["+bankName+"]]>").append("</bank_name>\r\n");

				}
			else if( currentColumn.trim().equalsIgnoreCase( "bank_code" ) )
			{  
				bankCode = checkNull(genericUtility.getColumnValue("bank_code",dom));

				sql = "SELECT BANK_NAME FROM BANK WHERE BANK_CODE =?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bankCode);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					bankName = checkNull(rs.getString( "BANK_NAME" ));
				}
				rs.close(); 
				rs = null;
				pstmt.close();
				pstmt = null;
				valueXmlString.append("<bank_name>").append("<![CDATA["+bankName+"]]>").append("</bank_name>\r\n");

				
			}else if( currentColumn.trim().equalsIgnoreCase( "cust_code" ) )
			{  
				custCode = checkNull(genericUtility.getColumnValue("cust_code",dom));
				sql = "SELECT CUST_NAME FROM CUSTOMER WHERE CUST_CODE = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, custCode);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					custName = checkNull(rs.getString( "CUST_NAME" ));
				}
				rs.close(); 
				rs = null;
				pstmt.close();
				pstmt = null;
				System.out.println("custName["+custName+"]");
				valueXmlString.append("<cust_name><![CDATA[").append(custName).append("]]></cust_name>\r\n");			  						

				

			}else if( currentColumn.trim().equalsIgnoreCase( "site_code" ) )
			{
				siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));

				sql = "SELECT DESCR,BANK_CODE FROM SITE WHERE SITE_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, siteCode);
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					siteCodeDesc = checkNull(rs.getString( "DESCR" ));
					bankCode = rs.getString("BANK_CODE");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				sql = "SELECT BANK_NAME FROM BANK WHERE BANK_CODE =?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, bankCode);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					bankName = checkNull(rs.getString("BANK_NAME"));
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				valueXmlString.append("<descr><![CDATA[").append(siteCodeDesc).append("]]></descr>\r\n");
				valueXmlString.append("<bank_code><![CDATA[").append(bankCode).append("]]></bank_code>\r\n");
				valueXmlString.append("<bank_name><![CDATA[").append(bankName).append("]]></bank_name>\r\n");
			}
			valueXmlString.append("</Detail1>");
			break;
		case 2 :
			parentNodeList = dom.getElementsByTagName("Detail2");
			parentNode = parentNodeList.item(0);
			childNodeList = parentNode.getChildNodes();
			ctr = 0; 
			valueXmlString.append("<Detail2>");
			childNodeListLength = childNodeList.getLength();
			System.out.println("currentColumn[" + currentColumn + "] ==> '" + columnValue + "'");
			if ( currentColumn.trim().equalsIgnoreCase("itm_default") )
			{	
				hdrCustCode = this.genericUtility.getColumnValue("cust_code", dom1);
				hdrSiteCode = this.genericUtility.getColumnValue("site_code", dom1);
				valueXmlString.append("<line_no__ref><![CDATA[" ).append("1").append( "]]></line_no__ref>\r\n");
				//valueXmlString.append("<udf_str1><![CDATA[").append( hdrSiteCode ).append("]]></udf_str1>\r\n");
				//valueXmlString.append("<udf_str2><![CDATA[").append( hdrCustCode ).append("]]></udf_str2>\r\n");
				
			}else if( currentColumn.trim().equalsIgnoreCase( "ref_no" ) ||currentColumn.trim().equalsIgnoreCase( "ref_ser" ) )
			{
				refNo = checkNull(genericUtility.getColumnValue("ref_no",dom));
				refSer = checkNull(genericUtility.getColumnValue("ref_ser",dom));
				
				sql = "SELECT (TOT_AMT - ADJ_AMT) AS AMT FROM RECEIVABLES  WHERE TRAN_SER = ? AND REF_NO = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, refSer);
				pstmt.setString(2, refNo);
				rs = pstmt.executeQuery();
				
				if( rs.next() )
				{
					rcpAmt1=rs.getDouble(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				valueXmlString.append("<rcp_amt><![CDATA[").append( rcpAmt1 ).append("]]></rcp_amt>\r\n");
			}
				
			valueXmlString.append("</Detail2>");
			break;	
		}	
		valueXmlString.append("</Root>");
	}
	catch(Exception e)
	{
		e.printStackTrace();
		System.out.println("Exception ::"+ e.getMessage());
		throw new ITMException(e);
	}
	finally
	{
		try
		{
			if(conn != null)
			{
				if(rs != null) 
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null) 
				{
					pstmt.close();
					pstmt = null;

				}
				conn.close();
			}
			conn = null;	
		}
		catch(Exception d)
		{
			d.printStackTrace();
		}			
	}
	return valueXmlString.toString();
	}
	
	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;
	}
	private String errorType(Connection conn , String errorCode) throws ITMException
	{
		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();
			if(rs.next())
			{
				msgType = rs.getString("MSG_TYPE");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			throw new ITMException(ex);
		}		
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return msgType;
	}
	
	private boolean isDulplicateFrmDom(Document dom,String refNo, String lineNo,String refSer) throws ITMException
    {
		NodeList parentList = null;
		NodeList childList = null;
		Node parentNode = null;
		Node childNode = null;

		String lineNoDom = "";
		boolean isDulplicate = false;
		String refNoDom= "",refSerDom="";
		System.out.println("---inside isDulplicateFrmDom--");
		try
		{

			parentList = dom.getElementsByTagName("Detail2");
			int parentNodeListLength = parentList.getLength();
			for (int prntCtr = parentNodeListLength; prntCtr > 0; prntCtr-- )
			{	
				parentNode = parentList.item(prntCtr-1);
				childList = parentNode.getChildNodes();
				for (int ctr = 0; ctr < childList.getLength(); ctr++)
				{
					childNode = childList.item(ctr);
					
					if(childNode != null &&  childNode.getNodeName().equalsIgnoreCase("attribute"))
					{
						String updateFlag = "";
						updateFlag = childNode.getAttributes().getNamedItem("updateFlag").getNodeValue();
						if (updateFlag.equalsIgnoreCase("D"))
						{
							System.out.println("Break from here as the record is deleted");
							break;
						}
					}	
					if ( childNode != null && childNode.getFirstChild() != null &&  
					childNode.getNodeName().equalsIgnoreCase("line_no") )
					{
						lineNoDom = childNode.getFirstChild().getNodeValue().trim();
						System.out.println("lineNo["+lineNo+"]lineNoDom["+lineNoDom+"]");
						if (lineNo.equalsIgnoreCase(lineNoDom))
						{
							System.out.println("Break from here as line No match");
							break;
						}	
					}

					if ( childNode != null && childNode.getFirstChild() != null &&  
					childNode.getNodeName().equalsIgnoreCase("REF_NO") )
					{
						refNoDom = childNode.getFirstChild().getNodeValue().trim();
					}
					if ( childNode != null && childNode.getFirstChild() != null &&  
							childNode.getNodeName().equalsIgnoreCase("ref_ser") )
							{
								refSerDom = childNode.getFirstChild().getNodeValue().trim();
							}
					
				}
				if (refSer.trim().equalsIgnoreCase(refSerDom.trim())  && refNo.trim().equalsIgnoreCase(refNoDom.trim()) )
				{
					isDulplicate = true;
					break;
				}
				
			}//for loop
			
		
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}	
		System.out.println("isDulplicate>>>>>> ["+isDulplicate+"]");
		return isDulplicate;
	}	

}	
