package ibase.webitm.ejb.dis;
/*
** Purpose : To calculate various type of taxes
*/
import java.rmi.RemoteException;
import java.util.Date;
import java.util.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.TransIDGenerator;
import java.text.*;
import javax.naming.InitialContext;
import ibase.webitm.utility.GenericUtility;
import ibase.ejb.*;
import ibase.system.config.*;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3
public class TaxSch extends ProcessEJB implements TaxSchLocal, TaxSchRemote
{
	String errString = "";
	transient GenericUtility genericUtility = null;
	transient HashMap globalVarMap = null;
	transient ArrayList defVarList = null;
	private transient String sundryCodeSelSQL = "SELECT DI.SUNDRY_TYPE, DI.SUNDRY_CODE FROM DISTORDER DI, DISTORD_RCP DRCP WHERE DI.DIST_ORDER=DRCP.DIST_ORDER AND DRCP.TRAN_ID = ?";
	private transient String exciseRefSelSQL = "SELECT EXCISE_REF FROM SUPPLIER WHERE SUPP_CODE = ?";
	private transient String excCapitalSelSQL = "SELECT VAR_VALUE FROM DISPARM WHERE PRD_CODE = '999999' AND VAR_NAME='EXC_CAPITAL'";
	private transient String taxChapterSelSQL = "SELECT CASE WHEN TAX_CHAP__REP IS NULL THEN (CASE WHEN TAX_CHAP IS NULL THEN '' ELSE TAX_CHAP END) ELSE TAX_CHAP__REP END AS TAX_CHAP FROM ITEM WHERE ITEM_CODE=?";
	private transient String chapterHeadingSelSQL = "SELECT CHAP_HEADING FROM TAXCHAP WHERE TAX_CHAP=?";
	private transient String countTaxChapSelSQL = "SELECT COUNT(1) FROM TAXCHAP WHERE TAX_CHAP=? AND CATEGORY=?";
	private transient String matCategorySelSQL = "SELECT CASE WHEN ITEMSER.MATERIAL_CATEGORY IS NULL THEN 'M' ELSE ITEMSER.MATERIAL_CATEGORY END AS MATERIAL_CATEGORY FROM ITEMSER, ITEM WHERE ITEM.ITEM_SER = ITEMSER.ITEM_SER AND ITEM.ITEM_CODE=?";
	/*public void Create() throws RemoteException, CreateException
	{
	}

	public void Remove()
	{
	}

	public void Activate()
	{
	}

	public void Passivate()
	{
	}*/

	private void initialize()throws Exception
	{
		this.genericUtility = GenericUtility.getInstance();
		this.globalVarMap = new HashMap();
		this.defVarList = new ArrayList();
		this.defVarList.add("site_code");
		this.defVarList.add("bal_grp__fr");
		//this.defVarList.add("bal_grp__to"); //commented by rajendra on 26/12/07
		//this.defVarList.add("bal_grp");	   //added by rajendra on 26/12/07
		this.defVarList.add("tran_date__fr");
		this.defVarList.add("tran_date__to");
		this.defVarList.add("drcr_type");
		this.defVarList.add("bal_group__adj");
		this.defVarList.add("adj_amt");
		// 10-08-2006 manoharan
		this.defVarList.add("proc_date");
		// end 10-08-2006 manoharan
	/*
		//chandni
		this.defVarList.add("retrive");
		//end chandni
	*/
	}

	public String getData(String xmlString,String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("Called getData(string, string).ejb..ejb..");
		String resultString = null;
		Document dom=null;
		Document dom2 = null;
		try
		{
			System.out.println("xmlString :" + xmlString);
			System.out.println("xmlString2 :" + xmlString2);
			System.out.println("windowName :" + windowName);
			System.out.println("xtraParams :" + xtraParams);
			initialize();
			dom = this.genericUtility.parseString(xmlString); //returns the DOM Object for the passed XML Stirng
			dom2 = this.genericUtility.parseString(xmlString2);
			resultString = getData(dom, dom2, windowName, xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception :TaxSch :getData(String xmlString,String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			resultString = this.genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
		return resultString;
	}

	public String getData(Document dom, Document dom2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("Get records from the user defined select statement and insert it into tax_processed_mp table of tax(dom, string).ejb..ejb.RBHO.");
		String resultString = null;
		// 27-06-2007 manoharan
		String partyName = null;
		String partyCode = null;
		String colName = null;
		String refId = null;
		String sundryType = null;
		String balGroup ="";
		String refDateStr = "";
		String refDocStr = "";
		String drAmt = "";
		String crAmt = "";
		String clsStk = "";
		String taxAmt = "";
		String adjAmt = "";
		String balAmt = "";
		String netAmt = "";
		String qtyRecd = "";

		ResultSet partyNameRS = null;
		ResultSet excCapitalResultSet = null;
		ResultSet taxBalResultSet = null;
		ResultSet sqlExprResultSet = null;
		ResultSet sundryCodeResultSet = null;
		ResultSet exciseRefResultSet = null;
		ResultSet siteCodeShipRS = null;
		ResultSet taxChapterResultSet = null;
		ResultSet chapterHeadingResultSet = null;
		ResultSet countTaxChapResultSet = null;
		ResultSet matCategoryResultSet = null;
		ResultSet sundryTypeRS = null;

		ResultSetMetaData sqlExprRSMetaData = null;

		PreparedStatement partyNamePstmt = null;
		PreparedStatement taxBalSelPstmt = null;
		PreparedStatement sundryCodeSelPstmt = null;
		PreparedStatement exciseRefSelPstmt = null;
		PreparedStatement taxChapterSelPstmt = null;
		PreparedStatement chapterHeadingSelPstmt = null;
		PreparedStatement countTaxChapSelPstmt = null;
		PreparedStatement matCategorySelPstmt = null;
		PreparedStatement sqlExprSelPstmt = null;
		PreparedStatement siteCodeShipPstmt = null;
		PreparedStatement sundryTypePstmt = null;

		Statement excCapitalSelStmt = null;

		// end 27-06-2007 manoharan
		String siteCodeShip = null;

		ITMDBAccessLocal itmDBAccess = null;
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		try
		{
			initialize();
			setGlobalVarMap(dom);
			setGlobalVarMap(xtraParams);
			String dataStr = null;
			System.out.println("global var map :"+this.globalVarMap+":");
			// 16/03/10 manoharan
			//itmDBAccess = getITMAccess();
			//conn = itmDBAccess.getConnection(); //This function is to connect with oracle.ejb..ejb.
			//conn.setAutoCommit(false);
			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			// end 16/03/10 manoharan


			
			StringBuffer retTabSepStrBuff = new StringBuffer();
			StringBuffer inSqlBuff =new StringBuffer();
			StringBuffer sqlBuff =new StringBuffer();
			int ctr=0;
			//D prepared statements to be closed
			sundryCodeSelPstmt = conn.prepareStatement(sundryCodeSelSQL);
			exciseRefSelPstmt = conn.prepareStatement(exciseRefSelSQL);
			String excCapital = "";
			String balGroupadj = "";
			String drcrType = null;
			excCapitalSelStmt = conn.createStatement();
			excCapitalResultSet = excCapitalSelStmt.executeQuery(excCapitalSelSQL);
			if(excCapitalResultSet.next())
			{
				String tempString = excCapitalResultSet.getString("VAR_VALUE");
				excCapital = (tempString != null && !"null".equalsIgnoreCase(tempString))?tempString:"";
				//excCapitalResultSet.getString("VAR_VALUE");
			}
			excCapitalResultSet.close(); // 17/03/08 manoharan
			excCapitalResultSet = null; // 17/03/08 manoharan
			/*
			//chandni
			String retrive = (String)this.globalVarMap.get("retrive");
			System.out.println("Chandni retrive is["+retrive+"]");
			//end chandni
			*/
			drcrType = (String)this.globalVarMap.get("drcr_type");

			taxChapterSelPstmt = conn.prepareStatement(taxChapterSelSQL);
			chapterHeadingSelPstmt = conn.prepareStatement(chapterHeadingSelSQL);
			countTaxChapSelPstmt = conn.prepareStatement(countTaxChapSelSQL);
			matCategorySelPstmt = conn.prepareStatement(matCategorySelSQL);

			//commented by rajendra on 29/12/07
			//PreparedStatement taxBalSelPstmt = conn.prepareStatement("SELECT A.BAL_GROUP,A.BAL_TYPE,A.BAL_PRD,B.REF_SER,B.DRCR_FLAG,B.SQL_EXPR,B.SQL_EXPR1,B.SQL_INPUT FROM TAX_BALANCE_GRP A, TAX_BAL_GRP_DET B WHERE A.BAL_GROUP=B.BAL_GROUP AND A.BAL_GROUP BETWEEN ? AND ? ORDER BY A.BAL_GROUP, B.REF_SER");
			//System.out.println("Selecting BAL GROUP info based on specified BAL GROUP range Query :[SELECT A.BAL_GROUP,A.BAL_TYPE,A.BAL_PRD,B.REF_SER,B.DRCR_FLAG,B.SQL_EXPR,B.SQL_EXPR1,B.SQL_INPUT FROM TAX_BALANCE_GRP A, TAX_BAL_GRP_DET B WHERE A.BAL_GROUP=B.BAL_GROUP AND A.BAL_GROUP BETWEEN ? AND ? ORDER BY A.BAL_GROUP, B.REF_SER]");


			//taxBalSelPstmt.setString(1, (String)this.globalVarMap.get("bal_grp__fr"));
			//System.out.println("Setting value to BAL_GROUP from :["+(String)this.globalVarMap.get("bal_grp__fr")+"]");
			//taxBalSelPstmt.setString(2, (String)this.globalVarMap.get("bal_grp__to"));
			//System.out.println("Setting value to BAL_GROUP to :["+(String)this.globalVarMap.get("bal_grp__to")+"]");
				balGroup = (String)this.globalVarMap.get("bal_grp__fr");
				//System.out.println("balGroup.ejb..ejb..ejb..ejb..ejb..ejb..ejb."+balGroup);
				StringTokenizer st = new StringTokenizer(balGroup,",");
				int cnt=st.countTokens();
				//System.out.println("cnt.ejb..ejb..ejb..ejb..ejb..ejb..ejb."+cnt);

				while (st.hasMoreTokens())
				{
					String token=st.nextToken();
					inSqlBuff.append("'");
					inSqlBuff.append(token);
					inSqlBuff.append("'");
					if(cnt != 1)
					{
						inSqlBuff.append(",");
					}
					//System.out.println("token.ejb..ejb..ejb..ejb..ejb..ejb..ejb."+token);
					cnt--;
				 }
				 //System.out.println("inSqlBuff.ejb..ejb..ejb..ejb..ejb..ejb..ejb."+inSqlBuff);

			   //ended by rajendra on 29/12/07

			 sqlBuff.append("SELECT A.BAL_GROUP,A.BAL_TYPE,A.BAL_PRD,B.REF_SER,B.DRCR_FLAG,B.SQL_EXPR,B.SQL_EXPR1,B.SQL_INPUT FROM TAX_BALANCE_GRP A, TAX_BAL_GRP_DET B WHERE A.BAL_GROUP=B.BAL_GROUP AND A.BAL_GROUP IN ("+inSqlBuff+") ORDER BY A.BAL_GROUP, B.REF_SER");
			 System.out.println("sqlBuff.ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb..ejb."+sqlBuff.toString());
			 taxBalSelPstmt = conn.prepareStatement(sqlBuff.toString());
			taxBalResultSet = taxBalSelPstmt.executeQuery();
			while(taxBalResultSet.next())
			{
				
				String balGrpStd = taxBalResultSet.getString("BAL_GROUP");
				balGrpStd = (balGrpStd != null && !"null".equalsIgnoreCase(balGrpStd))?balGrpStd:"";
				String balTypeStd = taxBalResultSet.getString("BAL_TYPE");
				balTypeStd = (balTypeStd != null && !"null".equalsIgnoreCase(balTypeStd))?balTypeStd:"";
				String balPrdStd = taxBalResultSet.getString("BAL_PRD");
				balPrdStd = (balPrdStd != null && !"null".equalsIgnoreCase(balPrdStd))?balPrdStd:"";
				String refSerStd = taxBalResultSet.getString("REF_SER");
				refSerStd = (refSerStd != null && !"null".equalsIgnoreCase(refSerStd))?refSerStd:"";
				String drcrFlagStd = taxBalResultSet.getString("DRCR_FLAG");
				drcrFlagStd = (drcrFlagStd != null && !"null".equalsIgnoreCase(drcrFlagStd))?drcrFlagStd:"";
			
				String inpParamStr = taxBalResultSet.getString("SQL_INPUT");
				inpParamStr = (inpParamStr != null && !"null".equalsIgnoreCase(inpParamStr))?inpParamStr:"";
				//D uncomment and implement getInputValueList(inpParamStr, inpParamTypeStr) for dynamic setting of inout params
				//ArrayList inpValueList = getInputValueList(inpParamStr, inpParamTypeStr);
				String sqlExpr = taxBalResultSet.getString("SQL_EXPR");
				sqlExpr = (sqlExpr != null && !"null".equalsIgnoreCase(sqlExpr))?sqlExpr:"";
				String sqlExpr1 = taxBalResultSet.getString("SQL_EXPR1");
				sqlExpr1 = (sqlExpr1 != null && !"null".equalsIgnoreCase(sqlExpr1))?sqlExpr1:"";
				//String sqlExpr2 = "and adj_amt != 0 ";
				String concatSQL = sqlExpr + " " + sqlExpr1;
				System.out.println("concatSQL :["+concatSQL+"]");
				sqlExprSelPstmt = conn.prepareStatement(concatSQL);
				// 09-08-2006 manoharan from pb date is coming with format dd/mm/yy hh:mm:ss
				//String dbDate = this.genericUtility.getValidDateString((String)this.globalVarMap.get("tran_date__fr"), this.genericUtility.getApplDateFormat(), this.genericUtility.getDBDateFormat());
				String sdate = (String)this.globalVarMap.get("tran_date__fr");
				//System.out.println("sdate before :["+sdate+"]");
				if (this.genericUtility.getApplDateFormat().length() == 8)
				{
					sdate = sdate.substring(0,8);
				}
				else
				{
					sdate = sdate.substring(0,10);
				}

				if (genericUtility.getApplDateFormat().indexOf("/") != -1)
				{
					sdate = sdate.replace('-', '/');

				}

				//System.out.println("sdate after :["+sdate+"]");

				//System.out.println("getApplDateFormat :["+this.genericUtility.getApplDateFormat()+"]");
				//System.out.println("getDBDateFormat :["+this.genericUtility.getDBDateFormat()+"]");

				String dbDate = this.genericUtility.getValidDateString(sdate, this.genericUtility.getApplDateFormat(), this.genericUtility.getDBDateFormat());
				//sachin 25-07-05
				//sqlExprSelPstmt.setDate(1, java.sql.Date.valueOf(dbDate));
				java.sql.Timestamp ts = java.sql.Timestamp.valueOf(dbDate + " 00:00:00");
				//System.out.println("From date :["+dbDate+ " 00:00:00]");
				//System.out.println("From date (timestamp) : "+ts);
				//sqlExprSelPstmt.setTimestamp(1, java.sql.Timestamp.valueOf(dbDate + " 00:00:00"));
				sqlExprSelPstmt.setTimestamp(1, ts);
				// 09-08-2006 manoharan from pb date is coming with format dd/mm/yy hh:mm:ss
				//dbDate = this.genericUtility.getValidDateString((String)this.globalVarMap.get("tran_date__to"), this.genericUtility.getApplDateFormat(), this.genericUtility.getDBDateFormat());
				sdate = (String)this.globalVarMap.get("tran_date__to");
				//System.out.println("To date (sdate before) :["+sdate+"]");
				if (this.genericUtility.getApplDateFormat().length() == 8)
				{
					sdate = sdate.substring(0,8);
				}
				else
				{
					sdate = sdate.substring(0,10);
				}
				//System.out.println("To date (sdate before substr 8 ) :["+sdate+"]");
				if (genericUtility.getApplDateFormat().indexOf("/") != -1)
				{
					sdate = sdate.replace('-', '/');

				}
				dbDate = this.genericUtility.getValidDateString(sdate, this.genericUtility.getApplDateFormat(), this.genericUtility.getDBDateFormat());
				//sachin 25-07-05
				//System.out.println("To date (dbDate) : "+dbDate + " 00:00:00");
				//sqlExprSelPstmt.setTimestamp(2, java.sql.Timestamp.valueOf(dbDate + " 00:00:00"));	//commented by rajendra on 27/12/07
				ts = java.sql.Timestamp.valueOf(dbDate + " 00:00:00");
				//System.out.println("To date after  :["+dbDate+"]");
				//System.out.println("To date (timestamp) : "+ts);
				java.sql.Date tranDateToDate = java.sql.Date.valueOf(dbDate);
				//sachin 25-07-05
				//sqlExprSelPstmt.setDate(2, tranDateToDate);//sachin
				sqlExprSelPstmt.setTimestamp(2, ts);  //added by rajendra on 27/12/07
				sqlExprSelPstmt.setString(3, (String)this.globalVarMap.get("site_code"));
				//System.out.println("site :["+this.globalVarMap.get("site_code")+"]");
				sqlExprResultSet = sqlExprSelPstmt.executeQuery();
				sqlExprRSMetaData = sqlExprResultSet.getMetaData();
				int noOfDynCols = sqlExprRSMetaData.getColumnCount();
				//System.out.println("noOfDynCols.ejb..ejb..ejb..ejb..ejb..ejb.. : "+noOfDynCols);

				if(drcrType.equalsIgnoreCase("D"))
				{//added by nisar on 04/02/08
					//checkfor addl_excise column is found
					for(int temp = 1; temp <= noOfDynCols; temp++)
					{
						colName = sqlExprRSMetaData.getColumnName(temp);
						//System.out.println("Counter.ejb.. " + temp + " Column Name.ejb..ejb. " + colName);
						if (colName.trim().equalsIgnoreCase("ADDL_EXCISE"))
						{
							//System.out.println("noOfDynCols.ejb..ejb.(ADDL_EXCISE) " + noOfDynCols);
							noOfDynCols--;
							break;
						}
					}//end of for
				}
				else if (drcrType.equalsIgnoreCase("C"))
				{
					noOfDynCols--;
				}
				while(sqlExprResultSet.next())
				{
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.start
					/*
					retTabSepStrBuff.append(balGrpStd).append("\t");
					retTabSepStrBuff.append(balTypeStd).append("\t");
					retTabSepStrBuff.append(balPrdStd).append("\t");
					retTabSepStrBuff.append(refSerStd).append("\t");
					retTabSepStrBuff.append(drcrFlagStd).append("\t");
					
					System.out.println("balGrpStd"+balGrpStd+"balTypeStd"+balTypeStd+"balPrdStd"+balPrdStd);
					
					retTabSepStrBuff.append((String)this.globalVarMap.get("drcr_type")).append("\t");
					String tempString = (String)this.globalVarMap.get("bal_group__adj");
					balGroupadj = (tempString != null && !"null".equalsIgnoreCase(tempString))?tempString:"";
					retTabSepStrBuff.append(balGroupadj).append("\t");
					ctr = ctr + 7 ;
					System.out.println("refSerStd"+refSerStd+"drcrFlagStd"+drcrFlagStd+"balPrdStd"+balPrdStd+"balGroupadj"+balGroupadj);
					// 04-01-2007 manoharan add_exc should not be appended now (new column in sql)
					//for(int colCtr = 1; colCtr <= noOfDynCols; colCtr++)
					//System.out.println("Types.DATE:["+Types.DATE+"] -- Types.TIMESTAMP:["+Types.TIMESTAMP+"]");

					for(int colCtr = 1; colCtr <= noOfDynCols; colCtr++)
					{
						dataStr = sqlExprResultSet.getString(colCtr);
						dataStr = (dataStr != null && !"null".equalsIgnoreCase(dataStr))?dataStr:"";
						//added on 08112006
						//System.out.println("sqlExprRSMetaData.getColumnType(colCtr):["+sqlExprRSMetaData.getColumnType(colCtr)+"]");
						if(sqlExprRSMetaData.getColumnType(colCtr) == Types.DATE || sqlExprRSMetaData.getColumnType(colCtr)==Types.TIMESTAMP )
						{
							if (!dataStr.equalsIgnoreCase(""))
							{
								//System.out.println("Date Field Encounter::::");
								//System.out.println("Date Field Before Pass To Generic Utility:["+dataStr+"]");
								dataStr = genericUtility.getValidDateString(dataStr, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat());
								//System.out.println("Date Field After Generic Utility:["+dataStr+"]");
							}

						}
						System.out.println("dataStr::for loop enter"+colCtr+"=="+dataStr);
						retTabSepStrBuff.append(dataStr).append("\t");
						ctr++;
						//System.out.println("ctr : "+ ctr + " " + sqlExprRSMetaData.getColumnName(colCtr) + " [" + dataStr + "]");
					}
					//String refSer = sqlExprResultSet.getString("REF_SER");
					/* chandni
					/*
					Double adjAmt1 = sqlExprResultSet.getDouble("adj_amt");
					System.out.println("Chandni adjAmt1 is equal["+adjAmt1+"]");
					if ("A".equalsIgnoreCase(retrive))
					{
						System.out.println("Inside A");
						if(adjAmt1 == 0)
						{
							System.out.println("Inside A:: ADJAMT1");
							sqlExprResultSet = sqlExprSelPstmt.executeQuery();
						}
					}
					else if ("U".equalsIgnoreCase(retrive))
					{
						System.out.println("Inside U");
						if(adjAmt1 == 0)
						{
							System.out.println("Inside U:: ADJAMT1");
							sqlExprResultSet = sqlExprSelPstmt.executeQuery();
						}
					}
					else
					{
						System.out.println("Inside B");
						if(adjAmt1 == 0)
						{
							System.out.println("Inside B:: ADJAMT1");
							sqlExprResultSet = sqlExprSelPstmt.executeQuery();
						}
					}
					// end chandni*/
					refId = sqlExprResultSet.getString("REF_ID");
					refId = (refId != null && !"null".equalsIgnoreCase(refId))?refId:"";
					
					retTabSepStrBuff.append(refId).append("\t");   //ref id
					//retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("REF_DATE"))).append("\t");  //ref date
					
					refDateStr = checkNull(sqlExprResultSet.getString("REF_DATE"));
				
					if(!"".equalsIgnoreCase(refDateStr.trim()) && refDateStr != null)
					{	
						refDateStr = genericUtility.getValidDateString(refDateStr, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat());
					}	
					
					/*
					if(!"".equalsIgnoreCase(refDateStr.trim()) && refDateStr != null)
					{	
						//if (this.genericUtility.getApplDateFormat().length() == 10)
						//{
							refDateStr = refDateStr.substring(0,10);
							System.out.println("refDateStr11##"+refDateStr);
						//}
						//else
						//{
							//refDateStr = refDateStr.substring(0,8);
							//System.out.println("refDateStr11##()(_)"+refDateStr);
						//}
					}	
					
					// 04-01-2007 manoharan new column for additional excise (cess)
					//retTabSepStrBuff.append(sprocDate).append("\n");
					//System.out.println("sprocDate before :"+sprocDate);
					//sprocDate = genericUtility.getValidDateString(sprocDate, genericUtility.getApplDateFormat(), genericUtility.getApplDateFormat());
					System.out.println("refDateStr>>>"+refDateStr);
					if (genericUtility.getApplDateFormat().indexOf("/") != -1)
					{
						refDateStr = refDateStr.replace('-', '/');

					}
					*/
					
					retTabSepStrBuff.append(refDateStr.trim()).append("\t");
					if ( refSerStd.trim().equalsIgnoreCase("CRNRCP") || refSerStd.trim().equalsIgnoreCase("S-DSP") || refSerStd.trim().equalsIgnoreCase("S-RET"))
					{
						sundryType = "C";
					}
					else if ( refSerStd.trim().equalsIgnoreCase("D-RCP") )
					{
						sundryType = "I";
					}
					else if ( refSerStd.trim().equalsIgnoreCase("P-RCP") )
					{
						sundryType = "S";

					}
					else if ( refSerStd.trim().equalsIgnoreCase("D-EXC") || refSerStd.trim().equalsIgnoreCase("M-VOUC") || refSerStd.trim().equalsIgnoreCase("E-PAY") || refSerStd.trim().equalsIgnoreCase("DIRCT"))
					{
						String sundryTypeSql = null;
						if (refSerStd.trim().equalsIgnoreCase("D-EXC") || refSerStd.trim().equalsIgnoreCase("DIRCT"))
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM TAX_OTH_CREDITS WHERE TRAN_ID = ?";
						}
						else if (refSerStd.trim().equalsIgnoreCase("M-VOUC"))
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM MISC_VOUCHER WHERE TRAN_ID = ?";
						}
						else if (refSerStd.trim().equalsIgnoreCase("E-PAY"))
						{
							sundryTypeSql = "SELECT SUNDRY_TYPE FROM PAYMENT_EXP WHERE TRAN_ID = ?";
						}
						else
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM TAX_OTH_CREDITS WHERE TRAN_ID = ?";
						}

						sundryTypePstmt = conn.prepareStatement(sundryTypeSql);
						sundryTypePstmt.setString(1, refId);
						sundryTypeRS = sundryTypePstmt.executeQuery();
						sundryType = null;
						if(sundryTypeRS.next())
						{
							sundryType = sundryTypeRS.getString("SUNDRY_TYPE");
							sundryType = (sundryType != null && !"null".equalsIgnoreCase(sundryType))?sundryType:"S";
						}
						sundryTypeRS.close();
						sundryTypePstmt.close();
						sundryTypeRS = null;
						sundryTypePstmt = null;
					}
					else
					{
						// ADJISS
						sundryType = "S";
					}
					// 26/02/08 manoharan additional types added
					/*B	Bank
					C	Customer
					E	Employee
					S	Supplier
					X	Tax Authority
					L	Loan Party
					O	Others
					T	Transporter
					P	Sales person
					R	Strategic Customer
					I   Site
					*/
					// 18/07/08 manoharan checking added for null / empty sundry type
				
					if (sundryType != null && sundryType.trim().length() >= 0)
					{

						if ( sundryType.equalsIgnoreCase("C"))
						{
							partyNamePstmt = conn.prepareStatement("SELECT CUST_NAME AS PARTY_NAME FROM CUSTOMER WHERE CUST_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("E") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT EMP_FNAME AS PARTY_NAME FROM EMPLOYEE WHERE EMP_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("S") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT SUPP_NAME AS PARTY_NAME FROM SUPPLIER WHERE SUPP_CODE = ?");

						}
						else if ( sundryType.equalsIgnoreCase("X") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT TAUTH_NAME AS PARTY_NAME FROM TAX_AUTHORITY WHERE TAUTH_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("L") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT PARTY_NAME AS PARTY_NAME FROM LOANPARTY WHERE PARTY_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("T") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT TRAN_NAME AS PARTY_NAME FROM TRANSPORTER WHERE  TRAN_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("P") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT SP_NAME AS PARTY_NAME FROM SALES_PERS WHERE SALES_PERS = ?");
						}
						else if ( sundryType.equalsIgnoreCase("R") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT FIRST_NAME AS PARTY_NAME FROM STRG_CUSTOMER WHERE SC_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("I") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT DESCR AS PARTY_NAME FROM SITE WHERE SITE_CODE = ?");
						}
						else
						{
							partyNamePstmt = conn.prepareStatement("SELECT SUPP_NAME FROM SUPPLIER WHERE SUPP_CODE = ?");
						}

						partyCode = null;
						int noOfSupCols = sqlExprRSMetaData.getColumnCount();
						for(int suppCtr = 1; suppCtr <= noOfSupCols; suppCtr++)
						{
							colName = sqlExprRSMetaData.getColumnName(suppCtr);
							//System.out.println("Counter.ejb.. " + suppCtr + " Column Name.ejb..ejb. " + colName);
							if (colName.trim().equalsIgnoreCase("SUPP_CODE"))
							{
								partyCode = sqlExprResultSet.getString(suppCtr);
								partyCode = (partyCode != null && !"null".equalsIgnoreCase(partyCode))?partyCode:"";
							}
						}
						// 09/07/08 manoharan
						if ( refSerStd.trim().equalsIgnoreCase("D-RCP") )//&& partyCode == null )
						{
							//System.out.println("siteCodeShip ######## assigned to partyCode - sundryType [" + sundryType + "]");
							partyCode = siteCodeShip;
						}
						// end 09/07/08 manoharan
						//System.out.println("Supp Code.ejb..ejb. " + partyCode);

						if (partyCode != null && partyCode.trim().length() > 0 )
						{


							partyNamePstmt.setString(1, partyCode);
							partyNameRS = partyNamePstmt.executeQuery();
							partyName = null;
							if(partyNameRS.next())
							{
								partyName = partyNameRS.getString("PARTY_NAME");
							}
							partyNameRS.close();
							partyNamePstmt.close();

							if (partyName == null)
							{
								partyName = " " ;
							}
							System.out.println("Supp Name.ejb..ejb. " + partyName);
						}
						else
						{
							//Changed by Rohan on 03-04-13 for setting party name is equals to blank.start
							System.out.println("******PARTY NAME ELSE************");
							if (partyName == null)
							{
								partyName = " " ;
							}
							//Changed by Rohan on 03-04-13 for setting party name is equals to blank.end
						}
					}
					else
					{
						partyName = " ";
					}
					// end 18/07/08 manoharan checking added for null / empty sundry type
					
					retTabSepStrBuff.append(partyName).append("\t"); //supplier name
					/*
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("TAX_AMT"))).append("\t");//tax amt
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("ADJ_AMT"))).append("\t");//Adjusted amount
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("BAL_AMT"))).append("\t");//Balance amount
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("NET_AMT"))).append("\t");//net amount
					*/
				
					
					taxAmt = sqlExprResultSet.getString("TAX_AMT");
					adjAmt = sqlExprResultSet.getString("ADJ_AMT");
					balAmt = sqlExprResultSet.getString("BAL_AMT");
					netAmt = sqlExprResultSet.getString("NET_AMT");
					
					taxAmt = (taxAmt != null && !"null".equalsIgnoreCase(taxAmt))?taxAmt:"0";
					adjAmt = (adjAmt != null && !"null".equalsIgnoreCase(adjAmt))?adjAmt:"0";
					balAmt = (balAmt != null && !"null".equalsIgnoreCase(balAmt))?balAmt:"0";
					netAmt = (netAmt != null && !"null".equalsIgnoreCase(netAmt))?netAmt:"0";
					retTabSepStrBuff.append(taxAmt).append("\t");//tax amt
					retTabSepStrBuff.append(adjAmt).append("\t");//Adjusted amount
					retTabSepStrBuff.append(balAmt).append("\t");//Balance amount
					retTabSepStrBuff.append(netAmt).append("\t");//net amount
					
					//retTabSepStrBuff.append(sqlExprResultSet.getString("SUPP_CODE")).append("\t");//supplier code
					String sundryCode = "";
					String exciseRef = "";
					siteCodeShip = null;
					if (refSerStd != null && refSerStd.trim().equals("D-RCP"))
					{
						sundryCodeSelPstmt.setString(1, refId);
						sundryCodeResultSet = sundryCodeSelPstmt.executeQuery();
						if(sundryCodeResultSet.next())
						{
							sundryCode = sundryCodeResultSet.getString("SUNDRY_CODE");
							sundryCode = (sundryCode != null && !"null".equalsIgnoreCase(sundryCode))?sundryCode:"";
							sundryType = sundryCodeResultSet.getString("SUNDRY_TYPE");
							sundryType = (sundryType != null && !"null".equalsIgnoreCase(sundryType))?sundryType:"";
							if("S".equalsIgnoreCase(sundryType))
							{
								exciseRefSelPstmt.setString(1, sundryCode);
								exciseRefResultSet = exciseRefSelPstmt.executeQuery();

								if(exciseRefResultSet.next())
								{
									exciseRef = exciseRefResultSet.getString("EXCISE_REF");
									exciseRef = (exciseRef != null && !"null".equalsIgnoreCase(exciseRef))?exciseRef:"";
								}
							}
						}
						sundryCodeResultSet.close(); // 17/03/08 manoharan
						sundryCodeResultSet = null; // 17/03/08 manoharan

						// 16-08-2006 manoharan in case of D-RCP take shipping site
						//retTabSepStrBuff.append(sundryCode).append("\t");
						siteCodeShipPstmt = conn.prepareStatement("SELECT DI.SITE_CODE__SHIP FROM DISTORDER DI, DISTORD_RCP DRCP WHERE DI.DIST_ORDER=DRCP.DIST_ORDER AND DRCP.TRAN_ID = ?");
						siteCodeShipPstmt.setString(1, refId);
						siteCodeShipRS = siteCodeShipPstmt.executeQuery();

						if(siteCodeShipRS.next())
						{
							siteCodeShip = siteCodeShipRS.getString("SITE_CODE__SHIP");
							siteCodeShip = (siteCodeShip != null && !"null".equalsIgnoreCase(siteCodeShip))?siteCodeShip:"";
							
						}
						siteCodeShipRS.close();
						siteCodeShipPstmt.close();
						siteCodeShipRS = null;
						siteCodeShipPstmt = null;
					
						//retTabSepStrBuff.append(siteCodeShip).append("\t"); //site code ship
					
						// end 16-08-2006 manoharan
						
						
					}//if (refSerStd != null && refSerStd.trim().equals("D-RCP"))
					/*
					else
					{	
						siteCodeShip = "";
						retTabSepStrBuff.append(siteCodeShip).append("\t"); //site code ship
						System.out.println("siteCodeShip"+siteCodeShip);
					}
					*/
					//genericUtility.getValidDateString(sprocDate, genericUtility.getApplDateFormat(), genericUtility.getApplDateFormat());
					siteCodeShip = (siteCodeShip != null && !"null".equalsIgnoreCase(siteCodeShip))?siteCodeShip:"";
					retTabSepStrBuff.append(siteCodeShip).append("\t"); //site code ship
					
					/*
					String qtyRecd = sqlExprResultSet.getString("QTY_RECD");
					qtyRecd = (qtyRecd != null && !"null".equalsIgnoreCase(qtyRecd))?qtyRecd:"0";
					System.out.println("qty received"+qtyRecd);
					
					retTabSepStrBuff.append(qtyRecd).append("\t");//qty requried
					*/
				
					qtyRecd = checkNull(sqlExprResultSet.getString("QTY_RECD"));
					
					retTabSepStrBuff.append(qtyRecd.trim()).append("\t");
					
					//retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("QTY_RECD"))).append("\t");//ref_doc_no
					retTabSepStrBuff.append(balGrpStd).append("\t");//Balance Group
					
					String tempString = (String)this.globalVarMap.get("bal_group__adj");
					balGroupadj = (tempString != null && !"null".equalsIgnoreCase(tempString))?tempString:"";
					retTabSepStrBuff.append(balGroupadj).append("\t"); //Balance Group Adjusted
					
					//retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("REF_DOC_DATE"))).append("\t");//ref doc date
					refDocStr = checkNull(sqlExprResultSet.getString("REF_DOC_DATE"));
				
					if(!"".equalsIgnoreCase(refDocStr.trim()))
					{	
						
						refDocStr = genericUtility.getValidDateString(refDocStr, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat());
					}	
					/*
					if(!"".equalsIgnoreCase(refDocStr.trim()) && refDocStr != null)
					{	
						//if (this.genericUtility.getApplDateFormat().length() == 10)
						//{
							refDocStr = refDocStr.substring(0,10);
						//}
						//else
						//{
							//refDocStr = refDocStr.substring(0,8);
						//}
					}
					// 04-01-2007 manoharan new column for additional excise (cess)
					//retTabSepStrBuff.append(sprocDate).append("\n");
					//System.out.println("sprocDate before :"+sprocDate);
					//sprocDate = genericUtility.getValidDateString(sprocDate, genericUtility.getApplDateFormat(), genericUtility.getApplDateFormat());
					if (genericUtility.getApplDateFormat().indexOf("/") != -1)
					{
						refDocStr = refDocStr.replace('-', '/');
						System.out.println("refDocStr;;;"+refDocStr);

					}
					*/
					
					retTabSepStrBuff.append(refDocStr.trim()).append("\t");//ref doc date
					
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("ITEM_CODE"))).append("\t");//item code
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("REF_DOC_NO"))).append("\t");//ref_doc_no
					retTabSepStrBuff.append(refSerStd).append("\t");							//ref_series
					
					
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.end
					
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.start
					/*
					String sundryCode = "";
					String exciseRef = "";
					siteCodeShip = null;
					if (refSerStd != null && refSerStd.trim().equals("D-RCP"))
					{
						sundryCodeSelPstmt.setString(1, refId);
						sundryCodeResultSet = sundryCodeSelPstmt.executeQuery();
						if(sundryCodeResultSet.next())
						{
							sundryCode = sundryCodeResultSet.getString("SUNDRY_CODE");
							sundryCode = (sundryCode != null && !"null".equalsIgnoreCase(sundryCode))?sundryCode:"";
							sundryType = sundryCodeResultSet.getString("SUNDRY_TYPE");
							sundryType = (sundryType != null && !"null".equalsIgnoreCase(sundryType))?sundryType:"";
							if("S".equalsIgnoreCase(sundryType))
							{
								exciseRefSelPstmt.setString(1, sundryCode);
								exciseRefResultSet = exciseRefSelPstmt.executeQuery();

								if(exciseRefResultSet.next())
								{
									exciseRef = exciseRefResultSet.getString("EXCISE_REF");
									exciseRef = (exciseRef != null && !"null".equalsIgnoreCase(exciseRef))?exciseRef:"";
								}
							}
						}
						sundryCodeResultSet.close(); // 17/03/08 manoharan
						sundryCodeResultSet = null; // 17/03/08 manoharan

						// 16-08-2006 manoharan in case of D-RCP take shipping site
						//retTabSepStrBuff.append(sundryCode).append("\t");
						siteCodeShipPstmt = conn.prepareStatement("SELECT DI.SITE_CODE__SHIP FROM DISTORDER DI, DISTORD_RCP DRCP WHERE DI.DIST_ORDER=DRCP.DIST_ORDER AND DRCP.TRAN_ID = ?");
						siteCodeShipPstmt.setString(1, refId);
						siteCodeShipRS = siteCodeShipPstmt.executeQuery();

						if(siteCodeShipRS.next())
						{
							siteCodeShip = siteCodeShipRS.getString("SITE_CODE__SHIP");
							siteCodeShip = (siteCodeShip != null && !"null".equalsIgnoreCase(siteCodeShip))?siteCodeShip:"";
							System.out.println("siteCodeShip ######## "+siteCodeShip);
						}
						siteCodeShipRS.close();
						siteCodeShipPstmt.close();
						siteCodeShipRS = null;
						siteCodeShipPstmt = null;
						System.out.println("siteCodeShip"+siteCodeShip+"exciseRef:"+exciseRef);
						retTabSepStrBuff.append(siteCodeShip).append("\t");
						// end 16-08-2006 manoharan
						retTabSepStrBuff.append(exciseRef).append("\t");
						ctr = ctr + 2;
					}//if (refSerStd != null && refSerStd.trim().equals("D-RCP"))
					*/
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.end
					
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.start
					String itemCode = sqlExprResultSet.getString("ITEM_CODE");
					itemCode = (itemCode != null && !"null".equalsIgnoreCase(itemCode))?itemCode:"";
					String taxChapter = "";
					String chapHeading = "";
					String tranType = null;
					String matCategory = "";
					// 25-10-2006 manoharan to take the column TRAN_TYPE_TRANSACTION value from sql output and set as tran_type
					tranType = sqlExprResultSet.getString("TRAN_TYPE_TRANSACTION");
					if (tranType != null && tranType.trim().length() > 0)
					{
						// nothing to be done it should considered as it is
					}
					else
					{
						// apply old logic
						tranType = "A";
						if(excCapital != null && excCapital.trim().length() > 0)
						{
							//DCheck whether this is invariably reqd. or only if excCapital != null
							taxChapterSelPstmt.setString(1, itemCode);
							taxChapterResultSet = taxChapterSelPstmt.executeQuery();
							if(taxChapterResultSet.next())
							{
								taxChapter = taxChapterResultSet.getString("TAX_CHAP");
								taxChapter = (taxChapter != null && !"null".equalsIgnoreCase(taxChapter))?taxChapter:"";
							}
							taxChapterResultSet.close(); // 17/03/08 manoharan
							taxChapterResultSet = null; // 17/03/08 manoharan
							chapterHeadingSelPstmt.setString(1, taxChapter);
							chapterHeadingResultSet = chapterHeadingSelPstmt.executeQuery();
							if(chapterHeadingResultSet.next())
							{
								chapHeading = chapterHeadingResultSet.getString("CHAP_HEADING");
								chapHeading = (chapHeading != null && !"null".equalsIgnoreCase(chapHeading))?chapHeading:"";
							}
							chapterHeadingResultSet.close(); // 17/03/08 manoharan
							chapterHeadingResultSet = null; // 17/03/08 manoharan
							countTaxChapSelPstmt.setString(1, taxChapter);
							countTaxChapSelPstmt.setString(2, excCapital);
							countTaxChapResultSet = countTaxChapSelPstmt.executeQuery();
							if(countTaxChapResultSet.next())
							{
								int taxChapCount = countTaxChapResultSet.getInt(1);
								if(taxChapCount > 0)
								{
									tranType = "C";
								}
							}
							countTaxChapResultSet.close(); // 17/03/08 manoharan
							countTaxChapResultSet = null; // 17/03/08 manoharan
						}//if(excCapital != null && excCapital.length() > 0)
						else
						{
							matCategory = "M";
							matCategorySelPstmt.setString(1, itemCode);
							matCategoryResultSet = matCategorySelPstmt.executeQuery();
							if(matCategoryResultSet.next())
							{
								matCategory = matCategoryResultSet.getString(1);
								matCategory = (matCategory != null && !"null".equalsIgnoreCase(matCategory))?matCategory:"";
								if("C".equalsIgnoreCase(matCategory))
								{
									tranType = "C";
								}
							}
							matCategoryResultSet.close(); // 17/03/08 manoharan
							matCategoryResultSet = null; // 17/03/08 manoharan
						}
					}
					// end 25-10-2006 manoharan
					retTabSepStrBuff.append(tranType).append("\t");    //tran type
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("DOC_TYPE"))).append("\t"); // doc type
					retTabSepStrBuff.append(balTypeStd).append("\t"); //balance type std
					retTabSepStrBuff.append(balPrdStd).append("\t"); //Balancve period
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("REF_LINE"))).append("\t"); // Reference line
					retTabSepStrBuff.append(matCategory).append("\t");
					retTabSepStrBuff.append(taxChapter).append("\t");
					retTabSepStrBuff.append(chapHeading).append("\t");
					retTabSepStrBuff.append(sqlExprResultSet.getString("SITE_CODE")).append("\t"); // site code supplier
					//retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("CLOSING_STOCK"))).append("\t"); // closing stock
					
					clsStk = checkNull(sqlExprResultSet.getString("CLOSING_STOCK"));
					//clsStk = (clsStk != null && !"null".equalsIgnoreCase(clsStk))?clsStk:"0";
					retTabSepStrBuff.append(clsStk.trim()).append("\t"); // closing stock
					
					retTabSepStrBuff.append(exciseRef).append("\t");
					
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("SITE_CODE__SUPP"))).append("\t"); // site code supplier
				
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("SITE_CODE__TRAN"))).append("\t"); // site code supplier
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("TRAN_TYPE_TRANSACTION"))).append("\t"); // tran type of transaction
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("LOT_NO"))).append("\t"); //lot no
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("INVOICE_NO"))).append("\t"); //invoice no
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("TAX_CODE"))).append("\t"); //tax code
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("LOT_SL"))).append("\t"); //tax code
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("PROCESS_YN"))).append("\t"); //tax code
					
					retTabSepStrBuff.append(drcrFlagStd).append("\t"); //drcr flag
					
					drAmt = checkNull(sqlExprResultSet.getString("DR_AMT"));
					crAmt = checkNull(sqlExprResultSet.getString("CR_AMT"));
				
					retTabSepStrBuff.append(drAmt.trim()).append("\t"); //dr amt
					retTabSepStrBuff.append(crAmt.trim()).append("\t"); //cr amt
					
					/*
					drAmt = sqlExprResultSet.getString("DR_AMT");
					crAmt = sqlExprResultSet.getString("CR_AMT");
				
					drAmt = (drAmt != null && !"null".equalsIgnoreCase(drAmt))?drAmt:"0";
					crAmt = (crAmt != null && !"null".equalsIgnoreCase(crAmt))?crAmt:"0";
					retTabSepStrBuff.append(drAmt).append("\t"); //dr amt
					retTabSepStrBuff.append(crAmt).append("\t"); //cr amt
					System.out.println("dr Amt"+drAmt+"crAmt"+crAmt);
					*/
				
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.end
					if(balPrdStd.trim().equalsIgnoreCase("D"))
					{
						dataStr = (String) this.globalVarMap.get("tran_date__to");
						//System.out.println("calDate 1 before :"+dataStr);
						//retTabSepStrBuff.append((String)this.globalVarMap.get("tran_date__to")).append("\t");
						//dataStr = genericUtility.getValidDateString(dataStr, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat());
						if (genericUtility.getApplDateFormat().indexOf("/") != -1)
						{
							dataStr = dataStr.replace('-', '/');

						}
						//System.out.println("calDate 1 after : " +dataStr);
						retTabSepStrBuff.append(dataStr).append("\t");
						System.out.println("dataStr:D form::"+dataStr);
						ctr++;
					}
					else
					{
						Calendar calendar = Calendar.getInstance();
						// 13-08-2007 manoharan deprecated method to be avoided
						//calendar.set((tranDateToDate.getYear() + 1900), tranDateToDate.getMonth(), tranDateToDate.getDate());
						calendar.setTime(tranDateToDate);
						// end 13-08-2007 manoharan
						//System.out.println("tranDateToDate :["+tranDateToDate+"]");
						//System.out.println("tranDateToDate :["+tranDateToDate.getYear()+"]["+tranDateToDate.getMonth()+"]["+tranDateToDate.getDate()+"]");
						int lastDayOfMonth = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
						////System.out.println("lastDayOfMonth :["+lastDayOfMonth+"]");
						// 13-08-2007 manoharan deprecated method to be avoided
						//calendar.set((tranDateToDate.getYear() + 1900), tranDateToDate.getMonth(), lastDayOfMonth);
						calendar.set(Calendar.DAY_OF_MONTH, lastDayOfMonth);
						// end 13-08-207 manoharan
						//System.out.println("Final calendar.getTime() :["+new java.sql.Date(calendar.getTime().getTime())+"]");

						dataStr = (new java.sql.Date(calendar.getTime().getTime())).toString();
						//System.out.println("calDate 2 before :"+dataStr);
						//retTabSepStrBuff.append((String)this.globalVarMap.get("tran_date__to")).append("\t");
						dataStr = genericUtility.getValidDateString(dataStr, genericUtility.getDBDateFormat(), genericUtility.getApplDateFormat());
						//retTabSepStrBuff.append(new java.sql.Date(calendar.getTime().getTime())).append("\t");
						retTabSepStrBuff.append(dataStr).append("\t");
						//System.out.println("calDate 2 after >>>>:"+dataStr);
						ctr++;
					}
					
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.start
					
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("ITEM_REF"))).append("\t"); //item reference
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("PARTY_REF"))).append("\t"); //party reference
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("BANK_CODE"))).append("\t"); //Bank code
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("TAX_REF"))).append("\t"); //tax reference
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("DOC_DESCR"))).append("\t"); //doc desccr
					retTabSepStrBuff.append(checkNull(sqlExprResultSet.getString("DOC_TYPE"))).append("\t"); //doc typr
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.end
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.start
					//retTabSepStrBuff.append(matCategory).append("\t");
					// 10-08-2006 manoharan
					//retTabSepStrBuff.append(tranType).append("\n");
					//System.out.println("Tran Type Transaction 25-10-2006 :: "+tranType);
					//retTabSepStrBuff.append(tranType).append("\t");
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality.end
					
					String sprocDate = (String)this.globalVarMap.get("proc_date");
					if (this.genericUtility.getApplDateFormat().length() == 8)
					{
						sprocDate = sprocDate.substring(0,8);
					}
					else
					{
						sprocDate = sprocDate.substring(0,10);
					}
					// 04-01-2007 manoharan new column for additional excise (cess)
					//retTabSepStrBuff.append(sprocDate).append("\n");
					//System.out.println("sprocDate before :"+sprocDate);
					//sprocDate = genericUtility.getValidDateString(sprocDate, genericUtility.getApplDateFormat(), genericUtility.getApplDateFormat());
					if (genericUtility.getApplDateFormat().indexOf("/") != -1)
					{
						sprocDate = sprocDate.replace('-', '/');

					}

					//System.out.println("sprocDate after :"+sprocDate);
					retTabSepStrBuff.append(sprocDate).append("\t");
					// end 10-08-2006 manoharan
					String addExcise = "0";
					if (drcrType.equalsIgnoreCase("C"))
					{
						addExcise = sqlExprResultSet.getString("ADDL_EXCISE");
						addExcise = (addExcise != null && !"null".equalsIgnoreCase(addExcise))?addExcise:"0";
					}
					// 27-06-2007 manoharan new column supp_name added
					//retTabSepStrBuff.append(addExcise).append("\n");
					retTabSepStrBuff.append(addExcise).append("\n");
					// end 04-01-2007 manoharan
					//String partyName = null;
					//String partyCode = null;
					//partyCode = sqlExprResultSet.getString("SUPP_CODE");
					//Changed by Rohan on 08-04-13 for maintain selecting sequence & display sequecnce same to support sorting functionality
					/*
					if ( refSerStd.trim().equalsIgnoreCase("CRNRCP") || refSerStd.trim().equalsIgnoreCase("S-DSP") || refSerStd.trim().equalsIgnoreCase("S-RET"))
					{
						sundryType = "C";
					}
					else if ( refSerStd.trim().equalsIgnoreCase("D-RCP") )
					{
						sundryType = "I";
					}
					else if ( refSerStd.trim().equalsIgnoreCase("P-RCP") )
					{
						sundryType = "S";

					}
					else if ( refSerStd.trim().equalsIgnoreCase("D-EXC") || refSerStd.trim().equalsIgnoreCase("M-VOUC") || refSerStd.trim().equalsIgnoreCase("E-PAY") || refSerStd.trim().equalsIgnoreCase("DIRCT"))
					{
						String sundryTypeSql = null;
						if (refSerStd.trim().equalsIgnoreCase("D-EXC") || refSerStd.trim().equalsIgnoreCase("DIRCT"))
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM TAX_OTH_CREDITS WHERE TRAN_ID = ?";
						}
						else if (refSerStd.trim().equalsIgnoreCase("M-VOUC"))
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM MISC_VOUCHER WHERE TRAN_ID = ?";
						}
						else if (refSerStd.trim().equalsIgnoreCase("E-PAY"))
						{
							sundryTypeSql = "SELECT SUNDRY_TYPE FROM PAYMENT_EXP WHERE TRAN_ID = ?";
						}
						else
						{
							sundryTypeSql = "SELECT  SUNDRY_TYPE FROM TAX_OTH_CREDITS WHERE TRAN_ID = ?";
						}

						sundryTypePstmt = conn.prepareStatement(sundryTypeSql);
						sundryTypePstmt.setString(1, refId);
						sundryTypeRS = sundryTypePstmt.executeQuery();
						sundryType = null;
						if(sundryTypeRS.next())
						{
							sundryType = sundryTypeRS.getString("SUNDRY_TYPE");
							sundryType = (sundryType != null && !"null".equalsIgnoreCase(sundryType))?sundryType:"S";
						}
						sundryTypeRS.close();
						sundryTypePstmt.close();
						sundryTypeRS = null;
						sundryTypePstmt = null;
					}
					else
					{
						// ADJISS
						sundryType = "S";
					}
					// 26/02/08 manoharan additional types added
					/*B	Bank
					C	Customer
					E	Employee
					S	Supplier
					X	Tax Authority
					L	Loan Party
					O	Others
					T	Transporter
					P	Sales person
					R	Strategic Customer
					I   Site
					*/
					// 18/07/08 manoharan checking added for null / empty sundry type
					//System.out.println("Supplier Code:=====>: Sundry Type"+sundryType);
					/*
					if (sundryType != null && sundryType.trim().length() >= 0)
					{

						if ( sundryType.equalsIgnoreCase("C"))
						{
							partyNamePstmt = conn.prepareStatement("SELECT CUST_NAME AS PARTY_NAME FROM CUSTOMER WHERE CUST_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("E") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT EMP_FNAME AS PARTY_NAME FROM EMPLOYEE WHERE EMP_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("S") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT SUPP_NAME AS PARTY_NAME FROM SUPPLIER WHERE SUPP_CODE = ?");

						}
						else if ( sundryType.equalsIgnoreCase("X") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT TAUTH_NAME AS PARTY_NAME FROM TAX_AUTHORITY WHERE TAUTH_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("L") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT PARTY_NAME AS PARTY_NAME FROM LOANPARTY WHERE PARTY_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("T") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT TRAN_NAME AS PARTY_NAME FROM TRANSPORTER WHERE  TRAN_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("P") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT SP_NAME AS PARTY_NAME FROM SALES_PERS WHERE SALES_PERS = ?");
						}
						else if ( sundryType.equalsIgnoreCase("R") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT FIRST_NAME AS PARTY_NAME FROM STRG_CUSTOMER WHERE SC_CODE = ?");
						}
						else if ( sundryType.equalsIgnoreCase("I") )
						{
							partyNamePstmt = conn.prepareStatement("SELECT DESCR AS PARTY_NAME FROM SITE WHERE SITE_CODE = ?");
						}
						else
						{
							partyNamePstmt = conn.prepareStatement("SELECT SUPP_NAME FROM SUPPLIER WHERE SUPP_CODE = ?");
						}

						partyCode = null;
						int noOfSupCols = sqlExprRSMetaData.getColumnCount();
						for(int suppCtr = 1; suppCtr <= noOfSupCols; suppCtr++)
						{
							colName = sqlExprRSMetaData.getColumnName(suppCtr);
							//System.out.println("Counter.ejb.. " + suppCtr + " Column Name.ejb..ejb. " + colName);
							if (colName.trim().equalsIgnoreCase("SUPP_CODE"))
							{
								partyCode = sqlExprResultSet.getString(suppCtr);
								partyCode = (partyCode != null && !"null".equalsIgnoreCase(partyCode))?partyCode:"";
							}
						}
						// 09/07/08 manoharan
						if ( refSerStd.trim().equalsIgnoreCase("D-RCP") )//&& partyCode == null )
						{
							//System.out.println("siteCodeShip ######## assigned to partyCode - sundryType [" + sundryType + "]");
							partyCode = siteCodeShip;
						}
						// end 09/07/08 manoharan
						//System.out.println("Supp Code.ejb..ejb. " + partyCode);

						if (partyCode != null && partyCode.trim().length() > 0 )
						{


							partyNamePstmt.setString(1, partyCode);
							partyNameRS = partyNamePstmt.executeQuery();
							partyName = null;
							if(partyNameRS.next())
							{
								partyName = partyNameRS.getString("PARTY_NAME");
							}
							partyNameRS.close();
							partyNamePstmt.close();

							if (partyName == null)
							{
								partyName = " " ;
							}
							System.out.println("Supp Name.ejb..ejb. " + partyName);
						}
						else
						{
							//Changed by Rohan on 03-04-13 for setting party name is equals to blank.start
							System.out.println("******PARTY NAME ELSE************");
							if (partyName == null)
							{
								partyName = " " ;
							}
							//Changed by Rohan on 03-04-13 for setting party name is equals to blank.end
						}
					}
					else
					{
						partyName = " ";
					}
					// end 18/07/08 manoharan checking added for null / empty sundry type
					retTabSepStrBuff.append(partyName).append("\n");
					*/
					
					// end 27-06-2007 manoharan
					//ctr = ctr + 6;
					//System.out.println("xxxxxxxxxxxxxxxxxxxxxxxxxx ctr :"+ctr);
					
				}//while(sqlExprResultSet.next())
				sqlExprResultSet.close(); // 17/03/08 manoharan
				sqlExprResultSet = null; // 17/03/08 manoharan
				//System.out.println("xxxxxxxxxxxxxxxxxxxxxxxxxx ctr :"+ctr);
			}//while(taxBalResultSet.next())
			taxBalResultSet.close(); // 17/03/08 manoharan
			taxBalResultSet = null; // 17/03/08 manoharan
			//sqlExprRSMetaData.close();
			
					
			sqlExprRSMetaData = null;
			resultString = retTabSepStrBuff.toString();
			// 28-06-2007 manoharan
			partyNameRS = null;
			// 16/03/10 manoharan
			if (partyNamePstmt != null && !partyNamePstmt.isClosed())
			{
				partyNamePstmt.close();
			}
			partyNamePstmt = null;
			if (taxBalSelPstmt != null && !taxBalSelPstmt.isClosed())
			{
				taxBalSelPstmt.close();
			}
			taxBalSelPstmt = null;
			if (sundryCodeSelPstmt != null && !sundryCodeSelPstmt.isClosed())
			{
				sundryCodeSelPstmt.close();
			}
			sundryCodeSelPstmt = null;
			if (exciseRefSelPstmt != null && !exciseRefSelPstmt.isClosed())
			{
				exciseRefSelPstmt.close();
			}
			exciseRefSelPstmt = null;
			if (taxChapterSelPstmt != null && !taxChapterSelPstmt.isClosed())
			{
				taxChapterSelPstmt.close();
			}
			taxChapterSelPstmt = null;
			if (chapterHeadingSelPstmt != null && !chapterHeadingSelPstmt.isClosed())
			{
				chapterHeadingSelPstmt.close();
			}
			chapterHeadingSelPstmt = null;
			if (countTaxChapSelPstmt != null && !countTaxChapSelPstmt.isClosed())
			{
				countTaxChapSelPstmt.close();
			}
			countTaxChapSelPstmt = null;
			if (matCategorySelPstmt != null && !matCategorySelPstmt.isClosed())
			{
				matCategorySelPstmt.close();
			}
			matCategorySelPstmt = null;
			if (sqlExprSelPstmt != null && !sqlExprSelPstmt.isClosed())
			{
				sqlExprSelPstmt.close();
			}
			sqlExprSelPstmt = null;
			if (siteCodeShipPstmt != null && !siteCodeShipPstmt.isClosed())
			{
				siteCodeShipPstmt.close();
			}
			siteCodeShipPstmt = null;
			if (sundryTypePstmt != null && !sundryTypePstmt.isClosed())
			{
				sundryTypePstmt.close();
			}
			sundryTypePstmt = null;
			// end 16/03/10 manoharan
			// end 28/-06-2007 manoharan
			// added by manazir on 26/11/09 since conn is  not close
			if(conn != null)
			{
				conn.close();
				conn=null;			
			}
			// end of code 

		}
		catch (Exception e)
		{
			System.out.println("Exception :TaxSch :getData(Document dom, Document dom2, String windowName, String xtraParams):" + e.getMessage() + ":");
			resultString = this.genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{

			try
			{
				if ( partyNameRS != null)
				{
					partyNameRS.close();
					partyNameRS = null;
				}
				if ( excCapitalResultSet != null)
				{
					excCapitalResultSet.close();
					excCapitalResultSet = null;
				}
				if ( taxBalResultSet != null)
				{
					taxBalResultSet.close();
					taxBalResultSet = null;
				}
				if ( sqlExprResultSet != null)
				{
					sqlExprResultSet.close();
					sqlExprResultSet = null;
				}
				if ( sundryCodeResultSet != null)
				{
					sundryCodeResultSet.close();
					sundryCodeResultSet = null;
				}
				if ( exciseRefResultSet != null)
				{
					exciseRefResultSet.close();
					exciseRefResultSet = null;
				}
				if ( siteCodeShipRS != null)
				{
					siteCodeShipRS.close();
					siteCodeShipRS = null;
				}
				if ( taxChapterResultSet != null)
				{
					taxChapterResultSet.close();
					taxChapterResultSet = null;
				}
				if ( chapterHeadingResultSet != null)
				{
					chapterHeadingResultSet.close();
					chapterHeadingResultSet = null;
				}
				if ( countTaxChapResultSet != null)
				{
					countTaxChapResultSet.close();
					countTaxChapResultSet = null;
				}
				if ( matCategoryResultSet != null)
				{
					matCategoryResultSet.close();
					matCategoryResultSet = null;
				}
				if ( sundryTypeRS != null)
				{
					sundryTypeRS.close();
					sundryTypeRS = null;
				}
				if ( sqlExprRSMetaData != null)
				{
					//sqlExprRSMetaData.close();
					sqlExprRSMetaData = null;
				}
				if ( partyNamePstmt != null)
				{
					partyNamePstmt.close();
					partyNamePstmt = null;
				}
				if ( taxBalSelPstmt != null)
				{
					taxBalSelPstmt.close();
					taxBalSelPstmt = null;
				}
				if ( sundryCodeSelPstmt != null)
				{
					sundryCodeSelPstmt.close();
					sundryCodeSelPstmt = null;
				}
				if ( exciseRefSelPstmt != null)
				{
					exciseRefSelPstmt.close();
					exciseRefSelPstmt = null;
				}
				if ( taxChapterSelPstmt != null)
				{
					taxChapterSelPstmt.close();
					taxChapterSelPstmt = null;
				}
				if ( chapterHeadingSelPstmt != null)
				{
					chapterHeadingSelPstmt.close();
					chapterHeadingSelPstmt = null;
				}
				if ( countTaxChapSelPstmt != null)
				{
					countTaxChapSelPstmt.close();
					countTaxChapSelPstmt = null;
				}
				if ( matCategorySelPstmt != null)
				{
					matCategorySelPstmt.close();
					matCategorySelPstmt = null;
				}
				if ( sqlExprSelPstmt != null)
				{
					sqlExprSelPstmt.close();
					sqlExprSelPstmt = null;
				}
				if ( siteCodeShipPstmt != null)
				{
					siteCodeShipPstmt.close();
					siteCodeShipPstmt = null;
				}
				if ( sundryTypePstmt != null)
				{
					sundryTypePstmt.close();
					sundryTypePstmt = null;
				}
				if ( excCapitalSelStmt != null)
				{
					excCapitalSelStmt.close();
					excCapitalSelStmt = null;
				}
				//return resultString;
			}

			catch(Exception sqle)
			{
				throw new ITMException(sqle);
			}

		}
		System.out.println("Final return String length :["+resultString+"]");
		return resultString;
	}

	private void setGlobalVarMap(Document dataDom)throws ITMException
	{
		try
		{
			NodeList parentList = dataDom.getElementsByTagName("Detail1");
			Node parentNode = parentList.item(0);
			NodeList childList = parentNode.getChildNodes();
			for(int ctr = 0; ctr < childList.getLength(); ctr++)
			{
				Node childNode = childList.item(ctr);
				String childNodeName = childNode.getNodeName();
				if(this.defVarList.contains(childNodeName))
				{
					if(childNode.getFirstChild() != null)
					{
						this.globalVarMap.put(childNodeName, childNode.getFirstChild().getNodeValue());
					}
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :TaxSch :setGlobalVarMap(Document dom) :" + e.getMessage() + ":");
			this.errString = this.genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
	}

	private void setGlobalVarMap(String dataString)throws ITMException
	{
		try
		{
			String loginCode = this.genericUtility.getValueFromXTRA_PARAMS(dataString, "loginCode");
			if(loginCode != null)
			{
				this.globalVarMap.put("loginCode", loginCode);
			}
			String chgUser = this.genericUtility.getValueFromXTRA_PARAMS(dataString, "chgUser");
			if(chgUser != null)
			{
				this.globalVarMap.put("chgUser", chgUser);
			}
			String chgTerm = this.genericUtility.getValueFromXTRA_PARAMS(dataString, "termId");
			if(chgTerm != null)
			{
				this.globalVarMap.put("chgTerm", chgTerm);
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception :TaxSch :setGlobalVarMap(String dataString) :" + e.getMessage() + ":");
			this.errString = this.genericUtility.createErrorString(e);
			throw new ITMException(e);
		}
	}

	private ITMDBAccessLocal getITMAccess()throws ITMException
	{
		ITMDBAccessLocal itmDBAccess = null;
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			 itmDBAccess = (ITMDBAccessLocal)ctx.lookup("ibase/ITMDBAccessEJB/local");
			//itmDBAccess = itmDBAccessHome.create();
		}
		catch(Exception e)
		{
			this.errString = this.genericUtility.createErrorString(e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		return itmDBAccess;
	}
	//Changed by Rohan on 09-04-13 for checking null.start
	private String checkNull( String input )
	{
		if ( input == null || input.equalsIgnoreCase("null"))
		{
			input = " ";
		}
		return input;
	}
	//Changed by Rohan on 09-04-13 for checking null.end
}