/**
 *
 *  Window Name : w_receipt_adv
 *  Button Name : Default
 *	Action      : Adding Records in default
*/

package ibase.webitm.ejb.fin.adv;

import java.rmi.RemoteException;
import java.sql.*;
import org.w3c.dom.*;
import javax.ejb.*;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.system.config.*;
import java.text.SimpleDateFormat;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class ReceiptAdvAct extends ActionHandlerEJB implements ReceiptAdvActLocal,ReceiptAdvActRemote//SessionBean
{
	//updated by nisar on 04/01/08 for RID : SY6BAS0059
	GenericUtility genericUtility = GenericUtility.getInstance();//added by nisar on 04/01/08
	String act_type=null;

	/*public void ejbCreate() throws RemoteException, CreateException 
	{
	}

	public void ejbRemove()
	{
	}

	public void ejbActivate() 
	{
	}

	public void ejbPassivate() 
	{
	}*/

  public String actionHandler() throws RemoteException,ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString, String objContext, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("Calling actionHandler from ReceiptAdvEJB");
		Document dom = null;
		String  resString = null;
		
		try
		{
		   
			if ("actionDefaultSer".equalsIgnoreCase(actionType) || "Default".equalsIgnoreCase(actionType) )
			{ 
				if(xmlString != null && xmlString.trim().length()!=0)
				{
					GenericUtility genericUtility = GenericUtility.getInstance(); 
					System.out.println("XML String :"+xmlString);
					dom = genericUtility.parseString(xmlString); 
				}
				//resString = actionDefault(dom,objContext,xtraParams);
				// parameter actionType added by Mukesh  20/03/09
				resString = actionDefault(actionType,dom,objContext,xtraParams);
			}
			
			
			
			
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ReceiptAdvActEJB :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning from ReceiptAdvActEJB actionHandler"+resString);
	    return (resString);
	}

	public String actionHandler(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams) throws RemoteException,ITMException
	{
	   //Added by nisar on 04/01/08 for RID : SY6BAS0059
		Document dom = null;
		Document dom1 = null;
        
		String  retString = null;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String ===================>:"+xmlString);
				dom = genericUtility.parseString(xmlString); 
				System.out.println("dom :"+dom);
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				System.out.println("@@@@@XML String1 ===================>:"+xmlString1);
				dom1 = genericUtility.parseString(xmlString1);
				
			}
			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("GPList"))
			{
				retString = getGPListDetails(dom,dom1,objContext,xtraParams);//function to fetch data
			}
			
			
			 
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ModelHandler :actionHandler(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from ModelHandler : actionHandler"+retString);
	    return retString;
	}
	public String actionHandlerTransform(String actionType, String xmlString, String xmlString1, String objContext, String xtraParams, String selDataStr) throws RemoteException,ITMException
	{
		System.out.println("[ReceiptAdvActEJB] actionHandlerTransform is calling.............");
		Document dom = null;
		Document dom1 = null;
		Document selDataDom = null;

		String  retString = null;
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = GenericUtility.getInstance().parseString(xmlString1);
			}
			if(selDataStr != null && selDataStr.length() > 0)
			{
				selDataDom = GenericUtility.getInstance().parseString(selDataStr);
			}

			System.out.println("actionType:"+actionType+":");
						
			if (actionType.equalsIgnoreCase("GPList"))
			{
				retString = gpListTransform(dom, dom1, objContext, xtraParams, selDataDom);
			}
		}
	   	catch(Exception e)
		{
			System.out.println("Exception :ReceiptAdvActEJB :actionHandlerTransform(String xmlString):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		System.out.println("returning String from ReceiptAdvActEJB : actionHandlerTransform"+retString);
	    return retString;
	}

	private String actionDefault(String actionType,Document dom, String objContext, String xtraParams) throws RemoteException , ITMException
	{
		String siteCode = "";
		String currCode = "";
		String custCode = "";
		String exchRateHeader = "";
		String  chqAmt = "";
		String advAmt = "";
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		String detCnt = "0";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		String errCode = "" ,errString = "";
		String itemSer=null;
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		try
		{
			System.out.println("<---- IN RECEIPTADVACT IN TRY ----->");
			//detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); //Will be Uncommented later - Jiten
			
			if (detCnt.equals("0"))
			{
				System.out.println("<-----IN RECEIPTADVACT IN TRY detCnt equal 0----->");
				conn = connDriver.getConnectDB("DriverITM");
				siteCode = genericUtility.getColumnValue("site_code",dom);
				custCode = genericUtility.getColumnValue("cust_code",dom);
				exchRateHeader = genericUtility.getColumnValue("exch_rate",dom);
				itemSer = genericUtility.getColumnValue("item_ser",dom);
				chqAmt = genericUtility.getColumnValue("chq_amt",dom);
				currCode = genericUtility.getColumnValue("curr_code",dom);
				advAmt = genericUtility.getColumnValue("adv_amt",dom);
				
				if (chqAmt.equals("0"))
				{
					System.out.println("Cheque Amount is Zero.");
					valueXmlString.append("</Root>\r\n");
					return valueXmlString.toString(); 
				}
				//double balAmt = Double.parseDouble(advAmt);//Commented by Jiten 04/10/06
				//double balAmt1 = Double.parseDouble(advAmt);//Commented by Jiten 04/10/06
				double balAmt = Double.parseDouble(chqAmt);//Changed from advAmt to chqAmt as changed in PB
				double balAmt1 = Double.parseDouble(chqAmt);
				System.out.println("BalAmt :=" +balAmt);
				System.out.println("actionType [" +actionType + "] siteCode [" + siteCode + "] itemSer  [" + itemSer + "] custCode [" + custCode + "]" );
				if (objContext.equalsIgnoreCase("2"))
				{
				    if(actionType.equals("actionDefaultSer"))
					{
				
					        sql = "SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES "+
							" WHERE SITE_CODE = '"+siteCode+"' AND ITEM_SER='"+itemSer+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+currCode+"' AND TOT_AMT - ADJ_AMT > 0 ORDER BY DUE_DATE";
					
					}
					else if(actionType.equals("default"))
					{
					        sql = "SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES "+
							" WHERE SITE_CODE = '"+siteCode+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+currCode+"' AND TOT_AMT - ADJ_AMT > 0 ORDER BY DUE_DATE";
					
					
					}
				}	
				else if (objContext.equalsIgnoreCase("3"))
				{
					sql = " SELECT TRAN_SER, REF_NO, TOT_AMT, ADJ_AMT FROM RECEIVABLES WHERE SITE_CODE = '"+siteCode+"' AND CUST_CODE = '"+custCode+"' AND CURR_CODE = '"+ currCode+"' AND TOT_AMT - ADJ_AMT < 0 ORDER BY DUE_DATE";  
				}
				
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				double rcpAmt=0;
				double totAmt=0;
				double diffamtexch=0;
				double exchRate=0;
				double advAmt1=0;

				String tranSer = "";
				String refNo = "";
				String acctCode = "";
				String descrCurr = "";
				String descrAccnt = "";
				//aviprash Start 26/10/05
				java.sql.Date refDate = null;
				java.sql.Date dueDate = null;
				String refDate1 = "";
				String dueDate1 = "";
				//End
				while (rs.next() && balAmt1 > 0)
				{
				   
					tranSer = rs.getString(1);
					refNo = rs.getString(2);
					totAmt = rs.getDouble(3);
					advAmt1 = rs.getDouble(4);
					System.out.println("tranSer :="+tranSer);
					System.out.println("refNo :="+refNo);
					System.out.println("totAmt :="+totAmt);
					System.out.println("advAmt1 :="+advAmt1);
					rcpAmt = totAmt - advAmt1; // Take Receipt Amount
					if (tranSer.equalsIgnoreCase("R-ADV"))
					{
						rcpAmt = (rcpAmt * (-1));
					}
					if (rcpAmt > balAmt)
					{
						rcpAmt = balAmt;
					}
					balAmt1 = balAmt1 - rcpAmt;
					//balAmt = advAmt;
					
					sql1 = "SELECT TRAN_DATE, REF_DATE, EXCH_RATE, ACCT_CODE, CCTR_CODE, DUE_DATE, TOT_AMT, ADJ_AMT, DISCOUNT, TAX_AMT FROM RECEIVABLES WHERE TRAN_SER = '" + tranSer +"'" + " AND REF_NO = '"+ refNo +"'"; 
					
					rs1 = conn.createStatement().executeQuery(sql1);
					if (rs1.next())
					{
						acctCode = rs1.getString(4);
						advAmt1 =  rs1.getDouble(8);
						totAmt = rs1.getDouble(7);
						//Aviprash Start 26/10/05
						refDate = rs1.getDate(2);
						dueDate = rs1.getDate(6);
						SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
						refDate1 = sdf.format(refDate);
						dueDate1 = sdf.format(dueDate);
						
						//End
						exchRate = rs1.getDouble(3);
					}
					sql2 = "SELECT DESCR FROM CURRENCY WHERE CURR_CODE = '" + currCode +"'";				
					
					rs2 = conn.createStatement().executeQuery(sql2);
					if (rs2.next())
					{
						descrCurr = rs2.getString(1);
					}
					sql3 = "SELECT DESCR FROM ACCOUNTS WHERE ACCT_CODE = '" + acctCode +"'";
					
					rs3 = conn.createStatement().executeQuery(sql3);
					if (rs3.next())
					{
						descrAccnt = rs3.getString(1);
					}
					
					rcpAmt = totAmt	- advAmt1;
					
					if (tranSer.trim().equals("R_ADV"))
					{
						rcpAmt = (rcpAmt * (-1));
					}
					if (rcpAmt > balAmt)
					{
						rcpAmt = balAmt;
					}
					refNo = refNo.trim();
					//Aviprash add trim()26/10/05
					if (objContext.equalsIgnoreCase("2"))
					{
						
						valueXmlString.append("<Detail>\r\n");
						valueXmlString.append("<ref_ser isSrvCallOnChg='1'>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</ref_ser>\r\n");
						valueXmlString.append("<ref_no isSrvCallOnChg='1'>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
						valueXmlString.append("<rcp_amt>").append("<![CDATA[").append(rcpAmt).append("]]>").append("</rcp_amt>\r\n");
						valueXmlString.append("<curr_code>").append("<![CDATA[").append(currCode).append("]]>").append("</curr_code>\r\n");
						valueXmlString.append("<exch_rate>").append("<![CDATA[").append(exchRateHeader).append("]]>").append("</exch_rate>\r\n");
						valueXmlString.append("<exch_rate__rcv>").append("<![CDATA[").append(rs1.getString(3)).append("]]>").append("</exch_rate__rcv>\r\n");
						diffamtexch = ((rcpAmt * exchRate) - (rcpAmt * Double.parseDouble(exchRateHeader)));
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
						balAmt = balAmt - rcpAmt;
						valueXmlString.append("<disc_amt>").append("<![CDATA[").append(rs1.getString(9)).append("]]>").append("</disc_amt>\r\n");
						valueXmlString.append("<tax_amt>").append("<![CDATA[").append(rs1.getString(10)).append("]]>").append("</tax_amt>\r\n");
						valueXmlString.append("<net_amt>").append("<![CDATA[").append(rs1.getString(7)).append("]]>").append("</net_amt>\r\n");
						valueXmlString.append("<ref_date>").append("<![CDATA[").append(refDate1).append("]]>").append("</ref_date>\r\n");
						valueXmlString.append("<due_date>").append("<![CDATA[").append(dueDate1).append("]]>").append("</due_date>\r\n");
						valueXmlString.append("<acct_code__ar>").append("<![CDATA[").append(rs1.getString(4)).append("]]>").append("</acct_code__ar>\r\n");
						valueXmlString.append("<accounts_descr>").append("<![CDATA[").append(rs3.getString(1).trim()).append("]]>").append("</accounts_descr>\r\n");
						valueXmlString.append("<cctr_code__ar>").append("<![CDATA[").append(rs1.getString(5)).append("]]>").append("</cctr_code__ar>\r\n");
						valueXmlString.append("<cust_code>").append("<![CDATA[").append(custCode).append("]]>").append("</cust_code>\r\n");
						valueXmlString.append("<currency_descr>").append("<![CDATA[").append(rs2.getString(1).trim()).append("]]>").append("</currency_descr>\r\n");
						//End
					}
					else if (objContext.equalsIgnoreCase("3"))
					{
						
						rcpAmt = (rcpAmt * (-1));
						//Aviprash adds trim()26/10/05
						valueXmlString.append("<ref_ser isSrvCallOnChg='0'>").append("<![CDATA[").append(rs.getString(1).trim()).append("]]>").append("</ref_ser>\r\n");
						valueXmlString.append("<ref_no>").append("<![CDATA[").append(rs.getString(2).trim()).append("]]>").append("</ref_no>\r\n");
						valueXmlString.append("<curr_code>").append("<![CDATA[").append(currCode).append("]]>").append("</curr_code>\r\n");
						valueXmlString.append("<exch_rate__vouch>").append("<![CDATA[").append(rs1.getString(3)).append("]]>").append("</exch_rate__vouch>\r\n");
						valueXmlString.append("<tot_amt>").append("<![CDATA[").append(rs1.getString(7)).append("]]>").append("</tot_amt>\r\n");
						valueXmlString.append("<adj_amt>").append("<![CDATA[").append(rcpAmt).append("]]>").append("</adj_amt>\r\n");
						valueXmlString.append("<acct_code>").append("<![CDATA[").append(rs1.getString(4)).append("]]>").append("</acct_code>\r\n");
						valueXmlString.append("<cctr_code>").append("<![CDATA[").append(rs1.getString(5)).append("]]>").append("</cctr_code>\r\n");
						diffamtexch = ((advAmt1 * exchRate) - (advAmt1 * Double.parseDouble(exchRateHeader)));
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
						balAmt = (balAmt - (rcpAmt * (-1)));
						//End
					}					
					valueXmlString.append("</Detail>\r\n");
				}
				stmt.close();
				valueXmlString.append("</Root>\r\n");			
			}
			else
			{
				errCode = "VTDETCNT";
			}
			if (!errCode.equals(""))
			{
				errString = itmDBAccessEJB.getErrorString("",errCode,"","",conn);
				conn.close();
				conn = null;
				return errString;				
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception : ReceiptAdvActEJB : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}
			catch(Exception e){}
		}
		return valueXmlString.toString();
	}
	 
	 
	private String getGPListDetails(Document dom, Document dom1, String objContext, String xtraParams) throws RemoteException, ITMException
	{
	    //Added by nisar on 04/01/08 for RID : SY6BAS0059
		String siteCode = null;
		String custCode = null;
		String custCodeS = null;
		String detCnt = null;
		String tranSer = null;
		String sql = null;
		String exchRateHeader = null;
		String refNo = null;
		String gpNo = null;
		String remark = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		
		Timestamp dueDate = null,refDate=null;
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null,rs1 = null;
		Connection conn = null;
		
		double exchRateD = 0.0;
		double totAmt = 0.0,adjAmt = 0.0,balance = 0.0,taxAmt = 0.0;
		double discount = 0.0,rcptAmt= 0.0,diffamtexch=0.0;
		
		ConnDriver connDriver = new ConnDriver();		
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		String reasCode = "", custRefNo = "",reasDescr = "";
		Timestamp custRefDate = null, custPoDate = null;
		double custRefAmt = 0;

		try
		{
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			//detCnt = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"detCnt"); 
			
			//if (detCnt.equals("0"))
			//{
			conn = connDriver.getConnectDB("DriverITM");
			siteCode = genericUtility.getColumnValue("site_code",dom1);
			custCode = genericUtility.getColumnValue("cust_code",dom1);
			tranSer = genericUtility.getColumnValue("ref_ser",dom);
			exchRateHeader = genericUtility.getColumnValue("exch_rate",dom1);//from header
			exchRateD = Double.parseDouble(exchRateHeader==null?"0":exchRateHeader.trim());						
			//}

			System.out.println("[ReceiptAdvActEJB] siteCode==>"+siteCode);
			System.out.println("[ReceiptAdvActEJB] custCode==>"+custCode);
			System.out.println("[ReceiptAdvActEJB] tranSer==>"+tranSer);

//			//oracle syntax
//			sql="  SELECT case when receivables.ref_no is null then '' else receivables.ref_no end ,"
//				+" (case when despatch.gp_no is null then '' else despatch.gp_no end)as gp_no,receivables.due_date,"   
//				+" receivables.tot_amt,receivables.adj_amt,((receivables.tot_amt) - (receivables.adj_amt)) Balance,"   
//				+" fn_remark(ref_no,tran_ser) remark,receivables.cust_code,"
//				+"	receivables.tran_date, "
//				+"	receivables.ref_no, "
//				+"	receivables.ref_date, "
//				+"	receivables.curr_code, "
//				+"	currency.descr as curr_descr,"
//				+"	receivables.exch_rate, "
//				+"	receivables.acct_code, "
//				+"	accounts.descr as acct_descr,"
//				+"	receivables.cctr_code, "
//				+"	receivables.discount, "
//				+"	receivables.tax_amt, "
//				+"	receivables.bad_debt_amt "				
//				+" FROM invoice,despatch,receivables,customer,currency,accounts  "
//			    +" WHERE ( invoice.desp_id = despatch.desp_id (+)) and  "
//				+"	 ( invoice.invoice_id (+) = receivables.ref_no) and  "
//				+"	 ( receivables.cust_code = customer.cust_code ) and  "
//				+"((customer.group_code = ( select group_code from customer where cust_code = '"+custCode+"') ) AND"  
//				+"	 ( receivables.site_code = '"+siteCode+"' ) AND  "
//				+"	 ( receivables.tran_ser = '"+tranSer+"' ) AND  "
//				+"	 ( ( RECEIVABLES.TOT_AMT- RECEIVABLES.ADJ_AMT) <> 0 ) ) and "
//				+"	receivables.curr_code = currency.curr_code and "
//				+"	receivables.acct_code = accounts.acct_code ";
/*
			SELECT  receivables.ref_no ,           despatch.gp_no ,           receivables.due_date ,           
			receivables.tot_amt ,           receivables.adj_amt ,           
			((receivables.tot_amt) - (receivables.adj_amt)) Balance,           
			fn_remark(ref_no,tran_ser) remark,           receivables.cust_code     
			FROM invoice ,           despatch ,           receivables ,           customer     
			WHERE ( invoice.desp_id = despatch.desp_id (+)) 
			and          ( invoice.invoice_id (+) = receivables.ref_no) 
			and          ( receivables.cust_code = customer.cust_code ) 
			and          ( ( customer.group_code in ( select group_code from customer where cust_code = 'AD0108') ) 
			and          ( receivables.site_code = 'SA117' ) 
			and          ( receivables.tran_ser = 'R-IBCA' ) 
			and          ( ( RECEIVABLES.TOT_AMT- RECEIVABLES.ADJ_AMT) <> 0 ) )

			SELECT r.ref_no, r.tran_ser, d.gp_no, r.due_date,r.tot_amt,	r.adj_amt, ((r.tot_amt) - (r.adj_amt)) Balance, 
			fn_remark(ref_no, tran_ser) remark, 	r.tran_date, r.ref_no, r.ref_date, r.curr_code, 	c.descr as curr_descr, 
			r.exch_rate, r.acct_code, a.descr as acct_descr, 	r.cctr_code, r.discount, r.tax_amt, r.bad_debt_amt 	
			FROM ((((invoice i 	left outer join despatch d on i.desp_id = d.desp_id) 	
			left outer join receivables r on r.ref_no = i.invoice_id ) 	
			inner join customer cu on r.cust_code = cu.cust_code) 	
			inner join currency c on r.curr_code = c.curr_code) 	
			inner join accounts a on r.acct_code = a.acct_code 	
			WHERE cu.group_code in ( select group_code from customer where cust_code = 'AD0108') 	
		AND r.site_code = 'SA117'	AND r.tran_ser = 'R-IBCA' 	AND ( r.TOT_AMT- r.ADJ_AMT <> 0 )
*/
			// NEW SQL FOR GPLIST
			// 19/07/12 manoharan blank tran_ser to be allowed
			

			
			sql= "SELECT r.cust_code custcode,r.ref_no ref_no,r.tran_ser, r.gp_no dsp_gpno, r.due_date due_date,  "
				+ "	r.tot_amt,    r.adj_amt,((r.tot_amt) - (r.adj_amt)) Balance,fn_remark(ref_no, tran_ser) remark, "
				+ "	r.tran_date, r.ref_no, r.ref_date,r.curr_code,  c.descr as curr_descr, r.exch_rate, r.acct_code,  "
				+ "	a.descr as acct_descr,r.cctr_code, r.discount, r.tax_amt, r.bad_debt_amt, "
				+ "	  r.REAS_CODE, r.CUST_REF_NO,r.CUST_REF_DATE,r.CUST_REF_AMT  FROM "
				+ "	(( receivables r  inner join customer cu on r.cust_code = cu.cust_code) "
				+ "	inner join currency c on r.curr_code = c.curr_code) inner join accounts a on r.acct_code = a.acct_code "
				+ "	WHERE cu.group_code in ( select group_code from customer where cust_code = ?) "
				+ "	 AND r.site_code = ?  AND ( r.TOT_AMT- r.ADJ_AMT <> 0 )   ";
				if (tranSer != null && tranSer.trim().length() > 0)
				{
					sql= sql + " AND r.tran_ser = '"+tranSer+"' ";
				}
				sql= sql + "order by r.cust_code,r.ref_no,r.gp_no,r.due_date asc";
				

			System.out.println("[ReceiptAdvActEJB] sql==>"+sql);

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,custCode);
			pstmt.setString(2,siteCode);
			rs = pstmt.executeQuery();

			while(rs.next())
			{
				custCodeS = rs.getString("custcode");
				gpNo = rs.getString("dsp_gpno");
				dueDate = rs.getTimestamp("due_date");
				refDate = rs.getTimestamp("ref_date");
				totAmt = rs.getDouble("tot_amt");
				adjAmt = rs.getDouble("adj_amt");
				taxAmt = rs.getDouble("tax_amt");
				balance = rs.getDouble("Balance");
				remark = rs.getString("remark");
				discount = rs.getDouble("discount");
				rcptAmt = totAmt - adjAmt;
				// 19/07/12 added by manoharan
				reasCode = rs.getString("REAS_CODE");
				custRefNo = rs.getString("CUST_REF_NO");
				custRefDate = rs.getTimestamp("CUST_REF_DATE");
				custRefAmt = rs.getDouble("CUST_REF_AMT");
				tranSer = rs.getString("tran_ser");
				if((custRefNo == null || custRefNo.trim().length() == 0) && "S-INV".equalsIgnoreCase(tranSer.trim()))
				{
					// show cust po
					sql= "select s.cust_pord,s.pord_date from sorder s, invoice i where i.sale_order = s.sale_order and i.invoice_id = ? ";

					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,rs.getString("ref_no").trim());
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						custRefNo = rs1.getString("cust_pord");
						if (custRefDate == null)
						{
							custRefDate = rs1.getTimestamp("pord_date");
						}
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				}
				if (custRefNo == null || custRefNo.trim().length() == 0)
				{
					custRefNo = " ";
				}
				if (reasCode == null || reasCode.trim().length() == 0)
				{
					reasCode = " ";
				}
				if (reasCode != null || reasCode.trim().length() > 0)
				{
					sql= "select descr from gencodes where fld_name  = 'REAS_CODE' and fld_value = ? ";

					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1,reasCode.trim());
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						reasDescr = rs1.getString("descr");
					}
					rs1.close();
					rs1 = null;
					pstmt1.close();
					pstmt1 = null;
				}
				else
				{
					reasDescr = "";
				}
				if (reasDescr == null || reasDescr.trim().length() == 0)
				{
					reasDescr = "";
				}
				System.out.println("MANOHAR 19/07/12 tranSer==>"+tranSer);
				//  end 19/07/12 manoharan
				valueXmlString.append("<Detail>\r\n");
				valueXmlString.append("<ref_ser>").append("<![CDATA[").append(tranSer.trim()).append("]]>").append("</ref_ser>\r\n");				
				valueXmlString.append("<ref_no>").append("<![CDATA[").append(rs.getString("ref_no").trim()).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
				valueXmlString.append("<balance>").append("<![CDATA[").append(balance).append("]]>").append("</balance>\r\n");
				if (dueDate != null)
				{
					valueXmlString.append("<due_date>").append("<![CDATA[").append(sdf.format(dueDate)).append("]]>").append("</due_date>\r\n");
				}
				else
				{
					valueXmlString.append("<due_date>").append("<![CDATA[ ]]>").append("</due_date>");
				}

				//   19/07/12 manoharan
				valueXmlString.append("<cust_ref_no>").append("<![CDATA[").append(custRefNo).append("]]>").append("</cust_ref_no>");				
				valueXmlString.append("<reas_code>").append("<![CDATA[").append(reasCode).append("]]>").append("</reas_code>");				
				valueXmlString.append("<reas_descr>").append("<![CDATA[").append(reasDescr).append("]]>").append("</reas_descr>");				
				valueXmlString.append("<net_amt>").append("<![CDATA[").append(totAmt).append("]]>").append("</net_amt>\r\n");
				valueXmlString.append("<adj_amt>").append("<![CDATA[").append(adjAmt).append("]]>").append("</adj_amt>\r\n");
				if (custRefDate != null)
				{
					valueXmlString.append("<cust_ref_date>").append("<![CDATA[").append(sdf.format(custRefDate)).append("]]>").append("</cust_ref_date>\r\n");
				}
				else
				{
					valueXmlString.append("<cust_ref_date>").append("<![CDATA[]]>").append("</cust_ref_date>");
				}
				//  end 19/07/12 manoharan
								
				valueXmlString.append("<dsp_gpno>").append("<![CDATA[").append(gpNo==null?" ":gpNo.trim()).append("]]>").append("</dsp_gpno>\r\n");
				valueXmlString.append("<remark>").append("<![CDATA[").append(remark==null?" ":remark.trim()).append("]]>").append("</remark>\r\n");
				valueXmlString.append("<rcp_amt>").append("<![CDATA[").append(rcptAmt).append("]]>").append("</rcp_amt>\r\n");
				valueXmlString.append("<curr_code>").append("<![CDATA[").append(rs.getString("curr_code")).append("]]>").append("</curr_code>\r\n");
				valueXmlString.append("<exch_rate>").append("<![CDATA[").append(exchRateD).append("]]>").append("</exch_rate>\r\n");
				valueXmlString.append("<exch_rate__rcv>").append("<![CDATA[").append(rs.getDouble("exch_rate")).append("]]>").append("</exch_rate__rcv>\r\n");
				valueXmlString.append("<cust_code>").append("<![CDATA[").append(custCodeS).append("]]>").append("</cust_code>\r\n");
				
				System.out.println("MANOHAR 19/07/12 exchRateHeader  ["+exchRateHeader + "]");
				diffamtexch = ((rcptAmt * exchRateD) - (rcptAmt * Double.parseDouble(exchRateHeader)));
				
				valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(diffamtexch).append("]]>").append("</diff_amt__exch>\r\n");
				valueXmlString.append("<disc_amt>").append("<![CDATA[").append(discount).append("]]>").append("</disc_amt>\r\n");
				valueXmlString.append("<tax_amt>").append("<![CDATA[").append(taxAmt).append("]]>").append("</tax_amt>\r\n");
				
				if (refDate != null)
				{
					valueXmlString.append("<ref_date>").append("<![CDATA[").append(sdf.format(refDate)).append("]]>").append("</ref_date>\r\n");
				}
				else
				{
					valueXmlString.append("<ref_date>").append("<![CDATA[ ]]>").append("</ref_date>");
				}
				//valueXmlString.append("<due_date>").append("<![CDATA[").append(sdf.format(dueDate)).append("]]>").append("</due_date>\r\n");
				valueXmlString.append("<acct_code__ar>").append("<![CDATA[").append(rs.getString("acct_code")).append("]]>").append("</acct_code__ar>\r\n");
				valueXmlString.append("<accounts_descr>").append("<![CDATA[").append(rs.getString("acct_descr").trim()).append("]]>").append("</accounts_descr>\r\n");
				valueXmlString.append("<cctr_code__ar>").append("<![CDATA[").append(rs.getString("cctr_code")).append("]]>").append("</cctr_code__ar>\r\n");
				valueXmlString.append("<currency_descr>").append("<![CDATA[").append(rs.getString("curr_descr").trim()).append("]]>").append("</currency_descr>\r\n");			
				valueXmlString.append("</Detail>\r\n");

			}//end of while(rs.next();
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			valueXmlString.append("</Root>\r\n");		
		}
		catch(Exception e)
		{
			System.out.println("Exception : ReceiptAdvActEJB : actionHandler :(Document dom)" +e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection.....");
				conn.close();
				conn = null;
			}
			catch(Exception e){}
		}
		return valueXmlString.toString();		
	}//End of getGPListDetails

	private String gpListTransform(Document dom,Document dom1, String objContext, String xtraParams, Document selDataDom)throws ITMException
	{
		System.out.println("[ReceiptAdvActEJB] gpListTransform is calling.............");
		Connection conn = null;
		StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		Node currDetail = null, currDetail1 = null; 
		int count = 0;
		double stkQty = 0;
		ConnDriver connDriver = new ConnDriver();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = GenericUtility.getInstance();
		String objName = "";
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			
			// Added by akhilesh on 12/july/2012
			NodeList parentNodeList = dom.getElementsByTagName("Detail1");
			Node parentNode = parentNodeList.item(0);				
			objName = getObjName(parentNode);
			
			NodeList detailList = selDataDom.getElementsByTagName("Detail");
			int noOfDetails = detailList.getLength();
			for(int ctr = 0; ctr < noOfDetails; ctr++)
			{
				currDetail = detailList.item(ctr);
			}
			if (noOfDetails > 0)
			{
				for(int ctr = 0; ctr < noOfDetails ; ctr++)
				{
					valueXmlString.append("<Detail>");
					currDetail1 = detailList.item(ctr);
					valueXmlString.append("<ref_ser isSrvCallOnChg='1'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_ser", currDetail1)).append("]]>").append("</ref_ser>\r\n");
					valueXmlString.append("<ref_no isSrvCallOnChg='1'>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_no", currDetail1)).append("]]>").append("</ref_no>\r\n");//Added srvCallOnChng by jiten 04/10/06
					if(!"rcp_tr".equalsIgnoreCase(objName))
					{
						valueXmlString.append("<rcp_amt>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("rcp_amt", currDetail1)).append("]]>").append("</rcp_amt>\r\n");
						valueXmlString.append("<curr_code>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("curr_code", currDetail1)).append("]]>").append("</curr_code>\r\n");
						valueXmlString.append("<exch_rate>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("exch_rate", currDetail1)).append("]]>").append("</exch_rate>\r\n");
						valueXmlString.append("<exch_rate__rcv>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("exch_rate__rcv", currDetail1)).append("]]>").append("</exch_rate__rcv>\r\n");
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("diff_amt__exch", currDetail1)).append("]]>").append("</diff_amt__exch>\r\n");
						valueXmlString.append("<disc_amt>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("disc_amt", currDetail1)).append("]]>").append("</disc_amt>\r\n");
						valueXmlString.append("<tax_amt>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("tax_amt", currDetail1)).append("]]>").append("</tax_amt>\r\n");
						valueXmlString.append("<net_amt>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("net_amt", currDetail1)).append("]]>").append("</net_amt>\r\n");
						valueXmlString.append("<ref_date>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("ref_date", currDetail1)).append("]]>").append("</ref_date>\r\n");
						valueXmlString.append("<due_date>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("due_date", currDetail1)).append("]]>").append("</due_date>\r\n");
						valueXmlString.append("<acct_code__ar>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("acct_code__ar", currDetail1)).append("]]>").append("</acct_code__ar>\r\n");
						valueXmlString.append("<accounts_descr>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("accounts_descr", currDetail1)).append("]]>").append("</accounts_descr>\r\n");
						valueXmlString.append("<cctr_code__ar>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("cctr_code__ar", currDetail1)).append("]]>").append("</cctr_code__ar>\r\n");
						valueXmlString.append("<cust_code>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("cust_code", currDetail1)).append("]]>").append("</cust_code>\r\n");
						valueXmlString.append("<currency_descr>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("currency_descr", currDetail1)).append("]]>").append("</currency_descr>\r\n");			
						valueXmlString.append("<dsp_gpno>").append("<![CDATA[").append(genericUtility.getColumnValueFromNode("dsp_gpno", currDetail1)).append("]]>").append("</dsp_gpno>\r\n");			
					}
					valueXmlString.append("</Detail>");
					
				}// for end
			}// end if
			valueXmlString.append("</Root>");
		}// end try
		catch(ITMException itme)
		{
			throw itme;
		}
		catch(Exception e)
		{
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("Closing Connection...");
				conn.close();
				conn = null;
			}
			catch (Exception e){}
		}
		System.out.println("[ReceiptAdvActEJB] gpListTransform() : valueXmlString from :"+valueXmlString.toString());
		return valueXmlString.toString();
	}
	private String getObjName(Node node) throws Exception
	{
		String objName = null;
		NodeList nodeList = null;
		Node detaulNode = null;
		Node detailNode = null;
		nodeList = node.getChildNodes();
		NamedNodeMap attrMap = node.getAttributes();
		objName = attrMap.getNamedItem( "objName" ).getNodeValue();
		/*
		for(int ctr = 0; ctr < nodeList.getLength(); ctr++ )
		{
			detailNode = nodeList.item(ctr);
			if(detailNode.getNodeName().equalsIgnoreCase("attribute") )
			{
				objName = detailNode.getAttributes().getNamedItem("objName").getNodeValue();
			}
		}
		*/
		return "w_" + objName;

	}
	
	
	
	
	

}