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..");
		String resultString = null;
		// 27-06-2007 manoharan
		String partyName = null;
		String partyCode = null;
		String colName = null;
		String refId = null;
		String sundryType = null;
		String balGroup ="";

		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())
			{
				System.out.println("Records found!!!!!!!!!!!!!!!!!!!!!!!!!!");
				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())
				{
					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");
					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 ;
					// 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+"]");
							}

						}

						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:"";
					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;
						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"))

					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

					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");
						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++;
					}
					retTabSepStrBuff.append(taxChapter).append("\t");
					retTabSepStrBuff.append(chapHeading).append("\t");
					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");
					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("\t");
					// end 04-01-2007 manoharan
					//String partyName = null;
					//String partyCode = null;
					//partyCode = sqlExprResultSet.getString("SUPP_CODE");

					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
					{
						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();
			System.out.println("resultString [" + resultString + "]");
			// 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;
	}
}