/********************************************************
	Title : Sales Budget Customer Plan[D14GSUN005]
	Date  : 15/01/2015
	Developer: Vishakha Dhoble
 ********************************************************/

package ibase.webitm.ejb.fin;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.utility.E12GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;

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

@Stateless
public class SalesBudgetCustIC extends ValidatorEJB implements SalesBudgetCustICLocal,SalesBudgetCustICRemote
{
	E12GenericUtility genericUtility = new E12GenericUtility();
	
	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("xmlString --->>>  [["+xmlString+"  ]]");
		System.out.println("xmlString1 --->>>  [["+xmlString1+"  ]]");
		System.out.println("xmlString2 --->>>  [["+xmlString2+"  ]]");
		System.out.println("editFlag --->>>  [["+editFlag+"  ]]");
		
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : SalesBudgetCustIC.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	
	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;
		int ctr=0;
		double quantity = 0.0,rate = 0.0 ;
		String childNodeName = null;
		String priceList = "",custCode = "",prdCode="",siteCode="",itemCode="",userId = "",sql="";
		String empCode="",cctrCode="",errCode="";
		String errString="",errorType="",tranDate="",acct_prd="",itemSer="";
		Connection conn = null;
		PreparedStatement pstmt = null;		
		ResultSet rs=null;
		int cnt1=0,cnt=0;		
		int currentFormNo=0;
		int childNodeListLength;
		java.util.Date tranDateL = null;
		ConnDriver connDriver = new ConnDriver();
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		
		try
		{		
			SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
			conn = connDriver.getConnectDB("DriverITM");			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId +"Edit Flag -->>: "+editFlag);
			
			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();
					System.out.println("Child name --->> "+childNodeName);				
					
					if(childNodeName.equalsIgnoreCase("tran_date")) //TRAN DATE
					{
						tranDate = checkNull(genericUtility.getColumnValue("tran_date",dom));
						System.out.println("tran_date--->["+tranDate+"]");
						
						if(tranDate.length() == 0){
						errCode = "VMTRNDNN"; 
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}
					if (childNodeName.equalsIgnoreCase("site_code")) //SITE CODE
					{
						tranDate = checkNull(genericUtility.getColumnValue("tran_date",dom));
						siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
						System.out.println("siteCode---->["+siteCode+"]");
						if (siteCode.length()==0)
						{
							errCode = "VTSITENN"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );								
						}
						else
						{	
							cnt1=getDBRowCount(conn,"site","site_code",siteCode);	
							System.out.println("Site  code cnt1--->"+cnt1);	
							if(cnt1 == 0){
							errCode = "VTSITEND"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
							}else{
								if(tranDate.length() > 0){
								tranDateL=simpleDateFormatObj.parse(tranDate);
								String code = checkNull(this.nfCheckPeriod("FIN", tranDateL, siteCode));
								System.out.println("Period check111--->>["+code+"]");
								if(code.length() > 0){
									errCode = code; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase() );	
								}
								}
							}
							
						}
					
					}
					
					if(childNodeName.equalsIgnoreCase("acct_prd")) //added new ACCT_PRD
					{
						acct_prd = checkNull(genericUtility.getColumnValue("acct_prd",dom));
						System.out.println("acct_prd--->["+acct_prd+"]");
						
						sql = "select count(*) from period where acct_prd = ? ";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,acct_prd);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for acct_prd--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VTACCTPRD"; //value of acct_prd should be in period master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
						
					}
					
					if (childNodeName.equalsIgnoreCase("item_ser")) //ITEM_SER
					{
						itemSer = checkNull(genericUtility.getColumnValue("item_ser",dom));
						System.out.println("Item Series--->["+itemSer+"]");
						
						if(itemSer == null || itemSer.length()==0)
						{
							errCode = "VTITEMSER5"; // item series cannot be blank
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						else{
							sql= "select count(*) from itemser where item_ser = ?";

						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemSer);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for Item Series--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VMITEMSER1"; // item series value must exist in item series
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}
					
					}	
				
					if (childNodeName.equalsIgnoreCase("price_list"))
					{
						priceList = checkNull(genericUtility.getColumnValue("price_list",dom));
						System.out.println("priceList--->["+priceList+"]");
						sql= "select count(*) from pricelist_mst where price_list = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,priceList);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for PriceList--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VTINVPLIST"; //pricelist value must exist in item series
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}	
					
				 } //end for loop  
				break;
				case 2 :
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("cust_code"))
					{
						custCode = checkNull(genericUtility.getColumnValue("cust_code",dom));
						System.out.println("custCode--->["+custCode+"]");
						sql= "select count(*) from customer where cust_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,custCode);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for custCode--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VTIVCC"; //customer code should be from customer master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}	
					if (childNodeName.equalsIgnoreCase("prd_code"))
					{
						prdCode = checkNull(genericUtility.getColumnValue("prd_code",dom));
						System.out.println("prd COde--->["+prdCode+"]");
						sql= "select count(*) from period where code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,prdCode);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for period code--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VTPRDTBL"; 
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}	
					if (childNodeName.equalsIgnoreCase("item_code"))
					{
						itemCode = checkNull(genericUtility.getColumnValue("item_code",dom));
						System.out.println("itemCode--->["+itemCode+"]");
						sql= "select count(*) from item where item_code = ? ";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						rs=pstmt.executeQuery();
						if(rs.next()){
							cnt1=rs.getInt(1);
						}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("Count value for itemCOde--->"+cnt1);	
						if(cnt1 == 0){
						errCode = "VTTASK4"; //item code should exist in item master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}	
					
					if (childNodeName.equalsIgnoreCase("quantity"))
					{
						quantity = Double.parseDouble(genericUtility.getColumnValue("quantity",dom) == null ? "0" : genericUtility.getColumnValue("quantity",dom));
						System.out.println("quantity--->["+quantity+"]");
						if(quantity < 0 || quantity == 0)
						{
						errCode = "INVQTY"; //quantity should not be zero or less than zero
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}
					if (childNodeName.equalsIgnoreCase("rate"))
					{
						rate = Double.parseDouble(genericUtility.getColumnValue("rate",dom) == null ? "0" : genericUtility.getColumnValue("rate",dom));
						System.out.println("rate--->["+rate+"]");
						if(rate < 0 ){
						errCode = "VTSUBCRATE"; //rate should not be less than zero
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
					}
				
				}
				break;
											
			} //end switch
			
			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);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}	
			
		} //end try
		catch (SQLException se) {
			System.out.println("SQLException ::"+se);
			se.printStackTrace();
            throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
            throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}	
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(connDriver!=null){
					connDriver = null;
				}	
				if(rs !=null)
				{
					rs.close();
					rs=null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();
		
	}
		
		
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		System.out.println("xmlString DOM-->>["+xmlString+"]");
		System.out.println("xmlString DOM1-->>["+xmlString1+"]");
		System.out.println("xmlString DOM2-->>["+xmlString2+"]");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [SalesBudgetCustIC ][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("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		double pickRate = 0.0,quantity = 0.0,netAmt = 0.0,rate = 0.0;
		E12GenericUtility genericUtility = new E12GenericUtility();
		ConnDriver connDriver = new ConnDriver();
		String confirm="N",loginSite="",siteDescr1="";
		int currentFormNo =0;
		String ItemDescr="",Itemcode="", OrderDate ="", Pricelist="",Itemser=""	,unitDescr="",unit="",custCode="",custDescr="",siteCode="";
		DistCommon distCommon = new DistCommon();
		String columnValue = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		
		try
		{	
			SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
		   Calendar currentDate = Calendar.getInstance();		
		   String tranDate = simpleDateFormatObj.format(currentDate.getTime());	
			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{	
				case 1 :
				valueXmlString.append("<Detail1>");				
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				System.out.println("editFlag =>" +editFlag);
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_defaultedit" ) )
				{
					System.out.println("Change Term>>>>>>>>"+genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm"));
				    String chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
				    String chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
				    
				        valueXmlString.append("<chg_date>").append("<![CDATA[" + tranDate + "]]>").append("</chg_date>");
						valueXmlString.append("<chg_term>").append("<![CDATA[" + chgTerm + "]]>").append("</chg_term>");
						valueXmlString.append("<chg_user>").append("<![CDATA[" + chgUser + "]]>").append("</chg_user>");

						System.out.println("The Value for Change term >>>>"+chgTerm+" and Change User>>>>>>>"+chgUser);
						
				}
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{					
					System.out.println("------------in itm_default--------->");
					currentDate = Calendar.getInstance();					
					tranDate = simpleDateFormatObj.format(currentDate.getTime());	
					String chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
					String chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
					
					valueXmlString.append("<add_date>").append("<![CDATA[" + tranDate + "]]>").append("</add_date>");
					valueXmlString.append("<add_user>").append("<![CDATA[" + chgUser + "]]>").append("</add_user>");
					valueXmlString.append("<add_term>").append("<![CDATA[" + chgTerm + "]]>").append("</add_term>");
				
					valueXmlString.append("<tran_date>").append("<![CDATA[" + tranDate + "]]>").append("</tran_date>");
					valueXmlString.append("<confirmed>").append("<![CDATA[" + confirm + "]]>").append("</confirmed>");
									 
					loginSite = checkNull(getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode"));					
					System.out.println("loginSite ----->>["+loginSite+"]");
					if(loginSite.length() > 0){
					siteDescr1=getColumnDescr(conn, "DESCR", "site", "SITE_CODE", loginSite);					
					}
					System.out.println("loginSite-->["+loginSite.length()+"]");
					System.out.println("tranDate-->["+tranDate.length()+"]");
					valueXmlString.append("<site_code>").append("<![CDATA[" + loginSite + "]]>").append("</site_code>");
					valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr1 + "]]>").append("</site_descr>");
				
				}			
			
				else if (currentColumn.trim().equalsIgnoreCase("site_code"))//FOR SITE CODE
				{
					siteCode = checkNull(genericUtility.getColumnValue("site_code",dom));
					System.out.println("siteCode11 ----->>["+siteCode+"]");
					if(siteCode.length() > 0){
					siteDescr1=getColumnDescr(conn, "DESCR", "site", "SITE_CODE", siteCode);
					}
					valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr1 + "]]>").append("</site_descr>");
					
				}
				else if (currentColumn.trim().equalsIgnoreCase("item_ser"))//FOR ITEM SER
				{
					Itemser = checkNull(genericUtility.getColumnValue("item_ser",dom));
					System.out.println("item_ser ----->>["+Itemser+"]");
					if(Itemser.length() > 0){
						ItemDescr=getColumnDescr(conn, "DESCR", "itemser", "ITEM_SER", Itemser);
					}
					System.out.println("ITEM DESCR>>>>"+ItemDescr);
					valueXmlString.append("<itemser_descr>").append("<![CDATA[" + ItemDescr + "]]>").append("</itemser_descr>");
					
				}
				valueXmlString.append("</Detail1>");
				  break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					int childListLength = childNodeList.getLength();	
					
				 if (currentColumn.trim().equalsIgnoreCase("item_code"))//FOR ITEM MASTER
					{
						Itemcode = checkNull(genericUtility.getColumnValue("item_code",dom));
						System.out.println("item_code ----->>["+Itemcode+"]");
						if(Itemcode.length() > 0){
						ItemDescr=getColumnDescr(conn, "DESCR", "item", "ITEM_CODE", Itemcode);
						}
						valueXmlString.append("<item_descr>").append("<![CDATA[" + ItemDescr + "]]>").append("</item_descr>");
					
						Pricelist = checkNull(genericUtility.getColumnValue("price_list",dom1));
						OrderDate = checkNull(genericUtility.getColumnValue("tran_date",dom1));
						pickRate = distCommon.pickRate(Pricelist, OrderDate,  Itemcode, conn);//For pickrate without quantity
						System.out.println("Pick Rate Value>>>>>>"+pickRate);
						valueXmlString.append("<rate>").append("<![CDATA[" + pickRate + "]]>").append("</rate>");
						netAmt = quantity * pickRate;
						System.out.println("Net Amount>>>>>>"+netAmt);
						valueXmlString.append("<net_amt>").append("<![CDATA[" +netAmt+ "]]>").append("</net_amt>");
						
					//setting value for unit from item master
						if(Itemcode.length() > 0){
							unit=getColumnDescr(conn, "UNIT", "item", "ITEM_CODE", Itemcode);
							}
						System.out.println("unit value ----->>["+unit+"]");
						valueXmlString.append("<unit>").append("<![CDATA[" + unit + "]]>").append("</unit>");
						if(unit.length() > 0){
							unitDescr =getColumnDescr(conn, "DESCR", "uom", "UNIT", unit);
						}
						valueXmlString.append("<uom_descr>").append("<![CDATA[" + unitDescr + "]]>").append("</uom_descr>");
					}
				 if (currentColumn.trim().equalsIgnoreCase("cust_code"))//FOR CUSTOMER MASTER
					{
						custCode = checkNull(genericUtility.getColumnValue("cust_code",dom));
						System.out.println("cust_code ----->>["+custCode+"]");
						if(custCode.length() > 0){
							custDescr =getColumnDescr(conn, "CUST_NAME", "customer", "CUST_CODE", custCode);
						}
						valueXmlString.append("<cust_name>").append("<![CDATA[" + custDescr + "]]>").append("</cust_name>");
						
					}
				 if (currentColumn.trim().equalsIgnoreCase("quantity"))//FOR quantity
					{
				    quantity = Double.parseDouble(genericUtility.getColumnValue("quantity",dom) == null ? "0" : genericUtility.getColumnValue("quantity",dom));
				    System.out.println("The value for quantity>>>>"+quantity);
				    rate = Double.parseDouble(genericUtility.getColumnValue("rate",dom) == null ? "0" : genericUtility.getColumnValue("rate",dom));
					netAmt = rate * quantity;
					
					valueXmlString.append("<quantity>").append("<![CDATA[" +quantity+ "]]>").append("</quantity>");
					System.out.println("Net Amount>>>>>>"+netAmt);
					valueXmlString.append("<net_amt>").append("<![CDATA[" +netAmt+ "]]>").append("</net_amt>");
				
					}
				 if (currentColumn.trim().equalsIgnoreCase("rate"))//rate
					{
					    rate = Double.parseDouble(genericUtility.getColumnValue("rate",dom) == null ? "0" : genericUtility.getColumnValue("rate",dom));
					    quantity = Double.parseDouble(genericUtility.getColumnValue("quantity",dom) == null ? "0" : genericUtility.getColumnValue("quantity",dom));
						System.out.println("rate VALUE----->>["+rate+"]");
                    	netAmt = quantity * rate;
						
						valueXmlString.append("<rate>").append("<![CDATA[" +rate+ "]]>").append("</rate>");
						System.out.println("Net Amount>>>>>>"+netAmt);
						valueXmlString.append("<net_amt>").append("<![CDATA[" +netAmt+ "]]>").append("</net_amt>");
						
					}
					valueXmlString.append("</Detail2>");
				}// end switch 
			valueXmlString.append("</Root>");			
			
			
		} //end try-------------------------------------------------------------
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());			
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
	}

	protected int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of SalesBudgetCustIC Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of SalesBudgetCustIC Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to SalesBudgetCustIC.getDBRowCount() method ");
				} catch (SQLException e) {
					e.printStackTrace();					
				}
			}
				
		return count;
	}	

	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, checkNull(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 checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input.trim();
	}
	public String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	
	

}
