/* 
Window Name : w_misc_vch_emp
*/
package ibase.webitm.ejb.fin.adv;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.ejb.sys.UtilMethods;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.WriteFldMapping.HearderLabel;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Random;
import java.util.TreeSet;

import javax.ejb.Stateless;
import javax.naming.InitialContext;
// added for ejb3

@Stateless // added for ejb3
public class MiscVchEmpIConf extends ActionHandlerEJB implements MiscVchEmpIConfLocal, MiscVchEmpIConfRemote
{
GenericUtility genericUtility = GenericUtility.getInstance();
UtilMethods utilMethods = UtilMethods.getInstance();
Random rnd = new Random();
ArrayList<String> miscVoucherTranID=new ArrayList<String>();
public String confirm(String tranId, String xtraParams, String forcedFlag) throws RemoteException,ITMException
{
	System.out.println("--------------confirm method of MiscVchEmpIConf ------------- : ");
	String retString = "";		
	try
	{
					
			retString = confirmMiscVchEmp(tranId,xtraParams,forcedFlag);
		
	}
	catch(Exception e)
	{
		System.out.println("Exception :MiscVchEmpIConf :confirm():" + e.getMessage() + ":");
		retString = GenericUtility.getInstance().createErrorString(e);
		e.printStackTrace();
	}
	
	return retString;
}

public String confirmMiscVchEmp(String tranId,String xtraParams,String forcedFlag) throws ITMException
{
	String retString = "";
	String sql = "";
	StringBuffer valueXmlString = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Root>\r\n");
	Connection conn = null;
	PreparedStatement pstmt = null;
	ITMDBAccessEJB itmdbAccess = null;
	String tranIds = tranId;
	String xtraParam = xtraParams;
	String forcedF = forcedFlag;
	String errString = null;
	ResultSet rs = null;
	ibase.webitm.utility.GenericUtility genericUtility = null;
	System.out.println("Confirm Action Called:::");
	String confirmed = "";
	String approverCode = "";
	try
	{	
		itmdbAccess = new ITMDBAccessEJB();
		genericUtility = ibase.webitm.utility.GenericUtility.getInstance();
		ConnDriver connDriver = null;
		connDriver = new ConnDriver();
		conn = connDriver.getConnectDB("DriverITM");
		conn.setAutoCommit(false);
		connDriver=null;
		
		sql = "Select confirmed from misc_vch_emp where tran_id = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,tranIds);
		rs = pstmt.executeQuery();
		if(rs.next())
		{
			confirmed = rs.getString(1)==null? "N" :rs.getString(1);			
		}
		if( pstmt != null ){
			pstmt.close();
			pstmt = null;
		}
		if ( rs != null ){
			rs.close();
			rs = null;
		}
		if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
		{
			//if already confirm then show error
			errString = itmdbAccess.getErrorString("", "VTDIST26", "", "", conn);
			return errString;
		}
		if( errString == null || errString.trim().length() == 0 )
		{
			approverCode = genericUtility.getValueFromXTRA_PARAMS(xtraParam,"loginEmpCode");
			System.out.println("approverCode-->["+approverCode+"]");
			retString = generateMiscVoucher(tranIds, xtraParam, forcedF,conn);
			System.out.println("generateMiscVoucher return String--->["+retString+"]");
			if("Success".equalsIgnoreCase(retString)){
				sql =  "update misc_vch_emp set CONFIRMED = 'Y',CONF_DATE = sysdate,EMP_CODE__APRV = ? where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
			
				pstmt.setString(1,approverCode);
				pstmt.setString(2,tranIds);
			
				int rowcnt = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
				if(rowcnt > 0){
				conn.commit();						
				errString = itmdbAccess.getErrorString("", "VMTRNCONS", "", "", conn);
				System.out.println("Voucher Confirmed Successfully.......");
				return errString;					
				}else{
				conn.rollback();
				errString = itmdbAccess.getErrorString("", "VMTRNCONN", "", "", conn);
				System.out.println("Voucher Confirmed Sucessfully.......");
				return errString;
				}
				
			}else if("Error".equalsIgnoreCase(retString)){
				errString = itmdbAccess.getErrorString("", "VMTRNCONN", "", "", conn);
				conn.rollback();
				return errString;
			}else{
				return retString;
			}
			
		}
		
	}
	catch( Exception e)
	{
		e.printStackTrace();
		throw new ITMException(e);
	}
    finally{
    	try{
    	if(rs!=null){
    		rs.close();
    		rs=null;
    	}
    	if(pstmt!=null){
    		pstmt.close();
    		pstmt=null;
    	}
    	if(conn !=null){
    		conn.close();
    		conn=null;
    	}
    	}
    	catch(Exception e){
    		e.printStackTrace();
    	}
    }
	return retString;
}

private String generateMiscVoucher(String tranId, String xtraParams, String forcedFlag ,Connection conn) throws RemoteException, ITMException
{
	FinCommon finCommon = new FinCommon();
	DistCommon disC = new DistCommon();
	String retSting = ""; 
	ConnDriver connDriver = null;
	String sql = "";
	StringBuffer xmlBuff=null;
//	Connection conn = null;
	PreparedStatement pStmt = null,pStmt1=null;
	String tranIds = tranId;
	String xtraParam = xtraParams;
	String forcedF = forcedFlag;
	ResultSet rs = null,rs1 = null;
	String siteCode = "",empCode="",acctCode="",cctrCode="",taxApply="",deptCode="",sAmount="",crTerm="",effDate="";
	String tranID = "",vouchType="",tranType="",sundryCode="",sundryType="",analCode="",remarks="",acctCodeAp="",cctrCodeAp="";
	Timestamp tranDate = null;	
	String currCode = "",exchRate="",bankCode="",projCode="",autoPay="",taxClass="",taxChap="",taxEnv="",finEntity="";
	String sundryTypeD="",sundryCodeD="",acctCodeD="",cctrCodeD="",acctCodeAdvD="",cctrCodeAdvD="",analCodeD="";
	String remarksD="",taxChapD="",taxClassD="",taxEnvD="",empCodeD="",miscVchTranId="",payMode="";
	double amountD=0.0;
	Timestamp taxDate = null,dueDate=null;	
	String xmlString = null;
	String tranDate1 ="",dueDate1="",taxDate1="",retString=null,errorStringMisc="";	
	double amount=0.0,totalAmt=0.0;
	int lineNo=0,billAmt=0;	
	try
	{
	    if(miscVoucherTranID != null){
	    	miscVoucherTranID.clear();
	    }
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
		effDate=simpleDateFormat.format(new java.util.Date()).toString();
		crTerm = finCommon.getFinparams("999999", "DEF_CR_TERM", conn);
		
		
		sql="select  tran_date,vouch_type,tran_type,sundry_type,site_code,anal_code,remarks,acct_code__ap,"
				+ "cctr_code__ap,curr_code,exch_rate,bank_code,proj_code,auto_pay,tax_class,tax_chap,tax_env,"
				+ "tax_date,due_date,total_amount,pay_mode from misc_vch_emp where tran_id=?";
		
		/*sql = "select  eevch.TRAN_DATE,eevch.VOUCH_TYPE,eevch.TRAN_TYPE,eevch.SUNDRY_TYPE,"          
				+"eevch.SUNDRY_CODE,eevch.SITE_CODE,eevch.ANAL_CODE,eevch.REMARKS,eevch.ACCT_CODE__AP,"       
				+"eevch.CCTR_CODE__AP,eevch.CURR_CODE,eevch.EXCH_RATE,eevch.BANK_CODE,eevch.PROJ_CODE,"        
				+"eevch.AUTO_PAY,eevch.TAX_CLASS,eevch.TAX_CHAP,eevch.TAX_ENV,eevch.TAX_DATE,eevch.DUE_DATE,"         
				+"eevch.TOTAL_AMOUNT "        
				+"from misc_vch_emp eevch where tran_id=? ";*/
		
		
		pStmt = conn.prepareStatement(sql);
		pStmt.setString(1,tranIds);
		rs = pStmt.executeQuery();
		
		if(rs.next())
		{
	
			 tranDate = rs.getTimestamp(1);
			 vouchType = checkNull(rs.getString(2));
			 tranType = checkNull(rs.getString(3));
			 sundryType = checkNull(rs.getString(4));
			 
			 siteCode=checkNull(rs.getString(5));
			 analCode = checkNull(rs.getString(6));
			 remarks = checkNull(rs.getString(7));
			 acctCodeAp =checkNull( rs.getString(8));
			 cctrCodeAp = checkNull(rs.getString(9));
			 currCode = checkNull(rs.getString(10));
			 exchRate = checkNull(rs.getString(11));
			 bankCode = checkNull(rs.getString(12));
			 projCode = checkNull(rs.getString(13));
			 autoPay = checkNull(rs.getString(14));
			 taxClass = checkNull(rs.getString(15));
			 taxChap = checkNull(rs.getString(16));
			 taxEnv = checkNull(rs.getString(17));
			 taxDate = rs.getTimestamp(18);
			 dueDate = rs.getTimestamp(19);
			 totalAmt = rs.getDouble(20);
			 payMode = checkNull(rs.getString(21));
			 
		}
		rs.close();
		rs = null;
		pStmt.close();
		pStmt = null;
		
		sql = " SELECT B.CURR_CODE,A.FIN_ENTITY FROM SITE A, FINENT B WHERE A.FIN_ENTITY = B.FIN_ENTITY "
				+ " AND A.SITE_CODE = ? " ;
		pStmt1 = conn.prepareStatement(sql);
		System.out.println("sql for currCode and finEntity : " + sql);
		pStmt1.setString(1,siteCode);
		rs1 = pStmt1.executeQuery();
		if (rs1.next())
		{
			//currCode=rs1.getString(1);
			finEntity = rs1.getString(2);
		}
		rs1.close(); rs1 = null;
		pStmt1.close(); pStmt1 = null;
		System.out.println("tran type ::"+tranType);
		
		if(tranDate !=null){
		tranDate1=simpleDateFormat.format( tranDate ).toString();
		System.out.println("tranDate1 ::"+tranDate1);
		}
		
		if(dueDate !=null){
		dueDate1=simpleDateFormat.format(dueDate).toString();
		System.out.println("dueDate1 ::"+dueDate1);
		}
		if(taxDate !=null){
		taxDate1=simpleDateFormat.format(taxDate).toString();
		System.out.println("taxDate1 ::"+taxDate1);
		}
	
		sql="select sundry_type,sundry_code,acct_code,cctr_code,acct_code__adv,cctr_code__adv,amount,anal_code,"
				+ "remarks,tax_chap,tax_class,tax_env,emp_code,line_no from misc_vch_empdet where tran_id = ?";
		
		pStmt1=conn.prepareStatement(sql);
		pStmt1.setString(1, tranId);
		rs1=pStmt1.executeQuery();	
		
		while(rs1.next()){
			xmlString="";
			xmlBuff = new StringBuffer();
			sundryTypeD=checkNull(rs1.getString(1));
			sundryCodeD=checkNull(rs1.getString(2));
			acctCodeD=checkNull(rs1.getString(3));
			cctrCodeD=checkNull(rs1.getString(4));
			acctCodeAdvD=checkNull(rs1.getString(5));
			cctrCodeAdvD=checkNull(rs1.getString(6));
			amountD=rs1.getDouble(7);
			analCodeD=checkNull(rs1.getString(8));
			
			
			remarksD=checkNull(rs1.getString(9));
			taxChapD=checkNull(rs1.getString(10));
			taxClassD=checkNull(rs1.getString(11));
			taxEnvD=checkNull(rs1.getString(12));
			empCodeD =checkNull(rs1.getString(13));
			lineNo=rs1.getInt(14);
			
			
			xmlBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
			xmlBuff.append("<DocumentRoot>");
			xmlBuff.append("<description>").append("Datawindow Root").append("</description>");
			xmlBuff.append("<group0>");
			xmlBuff.append("<description>").append("Group0 description").append("</description>");
			xmlBuff.append("<Header0>");
			xmlBuff.append("<objName><![CDATA[").append("misc_voucher").append("]]></objName>");
			xmlBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
			xmlBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
			xmlBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
			xmlBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
			xmlBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
			xmlBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
			xmlBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
			xmlBuff.append("<taxKeyValue><![CDATA[").append("	").append("]]></taxKeyValue>");
			xmlBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
			xmlBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
			xmlBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
			xmlBuff.append("<description>").append("Header0 members").append("</description>");
			
			xmlBuff.append("<Detail1 objContext =\"1\"").append(" objName=\"misc_voucher\" domID=\"1\" dbID=\"\">");
			xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
			xmlBuff.append("<tran_id/>"); 
			xmlBuff.append("<tran_type><![CDATA[").append(tranType).append("]]></tran_type>");
			xmlBuff.append("<tran_mode><![CDATA[").append("A").append("]]></tran_mode>");
			xmlBuff.append("<tran_date><![CDATA[").append(tranDate1).append("]]></tran_date>");
			xmlBuff.append("<vouch_type><![CDATA[").append("A").append("]]></vouch_type>");
			xmlBuff.append("<eff_date><![CDATA[").append(tranDate1).append("]]></eff_date>");
			xmlBuff.append("<sundry_type><![CDATA[").append(sundryType).append("]]></sundry_type>");
			xmlBuff.append("<sundry_code><![CDATA[").append(sundryCodeD).append("]]></sundry_code>");
			xmlBuff.append("<acct_code__ap><![CDATA[").append(acctCodeAp).append("]]></acct_code__ap>");  
			xmlBuff.append("<cctr_code__ap><![CDATA[").append(cctrCodeAp).append("]]></cctr_code__ap>"); 
			
			xmlBuff.append("<acct_code__adv><![CDATA[").append(acctCodeAdvD).append("]]></acct_code__adv>");
			xmlBuff.append("<cctr_code__adv><![CDATA[").append(cctrCodeAdvD).append("]]></cctr_code__adv>");
			xmlBuff.append("<sundry_type__pay><![CDATA[").append("E").append("]]></sundry_type__pay>");
			xmlBuff.append("<sundry_code__pay><![CDATA[").append(sundryCodeD).append("]]></sundry_code__pay>");
			xmlBuff.append("<acct_code__pay><![CDATA[").append(acctCodeAp).append("]]></acct_code__pay>");
			xmlBuff.append("<cctr_code__pay><![CDATA[").append(cctrCodeAp).append("]]></cctr_code__pay>");
			xmlBuff.append("<site_code><![CDATA[").append(siteCode).append("]]></site_code>");
			xmlBuff.append("<site_code__pay><![CDATA[").append(siteCode).append("]]></site_code__pay>");
			xmlBuff.append("<fin_entity><![CDATA[").append(finEntity).append("]]></fin_entity>");
			xmlBuff.append("<curr_code><![CDATA[").append(currCode).append("]]></curr_code>");
			xmlBuff.append("<exch_rate><![CDATA[").append(exchRate).append("]]></exch_rate>");
			xmlBuff.append("<cr_term><![CDATA[").append(crTerm==null?"":crTerm).append("]]></cr_term>");
			xmlBuff.append("<proj_code><![CDATA[").append(projCode).append("]]></proj_code>");
			xmlBuff.append("<tax_class><![CDATA[").append(taxClass).append("]]></tax_class>");
			xmlBuff.append("<tax_chap><![CDATA[").append(taxChap).append("]]></tax_chap>");
			xmlBuff.append("<tax_env><![CDATA[").append(taxEnv).append("]]></tax_env>");
			xmlBuff.append("<tax_date><![CDATA[").append(taxDate1==null?"":taxDate1).append("]]></tax_date>");
			xmlBuff.append("<due_date><![CDATA[").append(dueDate1==null?"":dueDate1).append("]]></due_date>");
			xmlBuff.append("<auto_pay><![CDATA[").append(autoPay).append("]]></auto_pay>");
			xmlBuff.append("<pay_mode><![CDATA[").append(payMode).append("]]></pay_mode>");
		    xmlBuff.append("<bank_code><![CDATA[").append(bankCode).append("]]></bank_code>");
			xmlBuff.append("<confirmed><![CDATA[").append("N").append("]]></confirmed>");
			xmlBuff.append("<remarks><![CDATA[").append(remarks).append("]]></remarks>");
			xmlBuff.append("<adv_amt><![CDATA[").append("0").append("]]></adv_amt>");
			xmlBuff.append("<net_amt><![CDATA[").append("0").append("]]></net_amt>");
			xmlBuff.append("<tot_amt><![CDATA[").append("0").append("]]></tot_amt>");
			xmlBuff.append("<bill_amt><![CDATA[").append("0").append("]]></bill_amt>");
			xmlBuff.append("<chg_user/>");
			xmlBuff.append("<chg_date/>");
			xmlBuff.append("<chg_term/>");
			xmlBuff.append("</Detail1>");
			
			
			xmlBuff.append("<Detail3 objContext=\"3\" objName=\"misc_voucher\" domID=\"1\" dbID=''>");
			xmlBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
			xmlBuff.append("<tran_id/>");
			xmlBuff.append("<line_no><![CDATA["+lineNo+"]]></line_no>");
			xmlBuff.append("<acct_code><![CDATA[").append(finCommon.getFinparams("999999","ACCT_CODE_PE",conn)).append("]]></acct_code>");
			xmlBuff.append("<cctr_code><![CDATA[").append(cctrCodeAp).append("]]></cctr_code>");
			xmlBuff.append("<amount><![CDATA[").append(amountD).append("]]></amount>");
			xmlBuff.append("<emp_code><![CDATA[").append(sundryCodeD).append("]]></emp_code>");
			xmlBuff.append("<tax_class><![CDATA[").append(taxClassD).append("]]></tax_class>");
			xmlBuff.append("<tax_chap><![CDATA[").append(taxChapD).append("]]></tax_chap>");
			xmlBuff.append("<tax_env><![CDATA[").append(taxEnvD).append("]]></tax_env>");
			xmlBuff.append("<anal_code><![CDATA[").append(analCodeD).append("]]></anal_code>");
			xmlBuff.append("<remarks><![CDATA[").append(remarksD).append("]]></remarks>");
			xmlBuff.append("</Detail3>");
			
			System.out.println("Payment Mode----->>["+payMode+"]");
			xmlBuff.append("</Header0>");
			xmlBuff.append("</group0>");
			xmlBuff.append("</DocumentRoot>");	
			
				xmlString = xmlBuff.toString();
				System.out.println("Final xmlString---->>["+xmlString+"]");
				retString = saveData(siteCode,xmlString,conn);
				System.out.println("After save retString---->>["+retString+"]");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("retString.indexOf(Success) > -1)");
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");
					miscVchTranId = arrayForTranId[1].substring(0,endIndex);					
					System.out.println("miscVchTranId12345------>>>["+miscVchTranId+"]");
					if(miscVchTranId != null && miscVchTranId.trim().length() > 0)
					{						
						MiscValConf MiscValConfObj=new MiscValConf();
						errorStringMisc=MiscValConfObj.confirm(miscVchTranId, xtraParams, forcedFlag);	
						miscVoucherTranID.add(checkNull(miscVchTranId));
						System.out.println("Return String from MiscValConf confirm---->["+errorStringMisc+"]");
						if(!(errorStringMisc.indexOf("CONFSUCCES") > -1)){
							return errorStringMisc;
						}
						
					}	
				}
				else
				{
					conn.rollback();
					System.out.println("VOUCHER NOT GENERATED FOR SUPPLIER");	
					return retString;					
				}				
			
			
		} //end while
		
		if(errorStringMisc.indexOf("CONFSUCCES") > -1){
			String str=generateAutoPayment(miscVoucherTranID, payMode,siteCode,currCode,tranType,conn);
			System.out.println("after generateAutoPayment-->["+str+"]");
			if (retString.indexOf("Success") > -1)
			{
				return "Success";
			}else
				return "Error";
			
		}	
		
	
	}
	catch(Exception e)
	{
		System.out.println("Exception : MiscVchEmpICConf : CONFIRM :" +e.getMessage());
		e.printStackTrace();
		return "Error";
	//	throw new ITMException(e);
	}
	return retSting;
}



public String saveData(String siteCode,String xmlString, Connection conn) throws ITMException
{
    System.out.println("saving data...........");
    InitialContext ctx = null;
    String retString = null;
    MasterStatefulLocal masterStateful = null; // for ejb3
    try
    {
        AppConnectParm appConnect = new AppConnectParm();
        ctx = new InitialContext(appConnect.getProperty());
        masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");
        String [] authencate = new String[2];
        authencate[0] = "";
        authencate[1] = "";
        System.out.println("xmlString :::: " + xmlString+"END END END");
        retString = masterStateful.processRequest(authencate, siteCode, true, xmlString,true,conn);
		masterStateful = null;
     }
     catch(ITMException itme)
     {
        System.out.println("ITMException : saveData :==>");
        itme.printStackTrace();
        throw itme;
     }
     catch(Exception e)
     {
        System.out.println("Exception : saveData :==>");
        e.printStackTrace();
        throw new ITMException(e);
     }
       return retString;
}

/*public String getMiscVouchXml(Connection conn,String tranId,String payMode )
{
	ResultSet rs=null,ts1=null;
	PreparedStatement pstmt=null,pstmt1=null;
	 try{
		 
	 }
	 catch(Exception e)
	 {
	   e.printStackTrace();	 
	 }
}*/
private String generateAutoPayment(ArrayList<String> miscTranID,String payMode,String siteCode,String currCode,String tranType,Connection conn)
{
	System.out.println("-----in generateAutoPayment method-----");
	System.out.println("payMode ---->>["+payMode+"]");
	System.out.println("Misc.voucher Tranid List----->>["+miscTranID+"]");
	ResultSet rs=null,rs1=null;
	PreparedStatement pstmt=null,pstmt1=null;
	GenericUtility GenericUtilityObj=GenericUtility.getInstance();
	
	String sql="",sundryType="",sundryCode="",vouchNoFrom="",vouchNoTo="",entryBatchNo=" ",
			dueDateS="",errorString="",xmlString="",retString="",miscPayTranId="";
	String sundryCodeL="",payModeL="",bankCodeL="",currCodeL="",siteCodeL="",finEntityL="",acctCodeL="",
			cctrCodeL="",autoPay="",sundryTypeL="",refNo="",headerStr="";
	double totAmt=0,exchRateL=0;
	StringBuffer xmlStringBuff = new StringBuffer();
	Timestamp dueDate=null;
	 TreeSet treeObj = new TreeSet(); 
	int count=0,countRcd=0;				
	try{
		SimpleDateFormat sdf=new SimpleDateFormat(GenericUtilityObj.getApplDateFormat());
		Calendar currentDate = Calendar.getInstance();					
		String tranDate = sdf.format(currentDate.getTime());
		payMode=checkNull(payMode);	
		xmlStringBuff.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
		xmlStringBuff.append("<DocumentRoot>");
		xmlStringBuff.append("<description>").append("Datawindow Root").append("</description>");
		xmlStringBuff.append("<group0>");
		xmlStringBuff.append("<description>").append("Group0 description").append("</description>");
		xmlStringBuff.append("<Header0>");
		xmlStringBuff.append("<objName><![CDATA[").append("misc_pay").append("]]></objName>");
		xmlStringBuff.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
		xmlStringBuff.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
		xmlStringBuff.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
		xmlStringBuff.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
		xmlStringBuff.append("<action><![CDATA[").append("SAVE").append("]]></action>");
		xmlStringBuff.append("<elementName><![CDATA[").append("").append("]]></elementName>");
		xmlStringBuff.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
		xmlStringBuff.append("<taxKeyValue><![CDATA[").append("	").append("]]></taxKeyValue>");
		xmlStringBuff.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
		xmlStringBuff.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
		xmlStringBuff.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
		xmlStringBuff.append("<description>").append("Header0 members").append("</description>");	
		headerStr=xmlStringBuff.toString();
		if(miscTranID !=null){
			treeObj.addAll(miscTranID);
			sql="select sundry_code,pay_mode,bank_code,tot_amt,curr_code,exch_rate,site_code,fin_entity,acct_code,"
					+ "cctr_code,auto_pay,sundry_type,ref_no FROM misc_payables "
					+ "where site_code= ? and sundry_type= ? and "
					+ "ref_no >= ? and ref_no <= ? "
					+ "and pay_mode = ? and curr_code = ? and tran_ser= ?";
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			pstmt.setString(2, "E");
			//pstmt.setString(3, sundryCode);
			//pstmt.setString(4, dueDateS);
			//pstmt.setString(5, dueDateS);				
			pstmt.setString(3, (treeObj.first()).toString());
			pstmt.setString(4, (treeObj.last()).toString());				
			pstmt.setString(5, payMode);						
			pstmt.setString(6, currCode);
			pstmt.setString(7, "M-VOUC");
			rs=pstmt.executeQuery();
			if("T".equalsIgnoreCase(payMode)){
				while(rs.next()){
					countRcd++;
					sundryCodeL=checkNull(rs.getString("sundry_code"));
					sundryTypeL=checkNull(rs.getString("sundry_type"));
					payModeL=checkNull(rs.getString("pay_mode"));
					bankCodeL=checkNull(rs.getString("bank_code"));
					totAmt=rs.getDouble("tot_amt");
					currCodeL=checkNull(rs.getString("curr_code"));
					exchRateL=rs.getDouble("exch_rate");
					siteCodeL=checkNull(rs.getString("site_code"));					
					finEntityL=rs.getString("fin_entity");
					acctCodeL=checkNull(rs.getString("acct_code"));
					cctrCodeL=checkNull(rs.getString("cctr_code"));
					autoPay=rs.getString("auto_pay");		
					refNo=checkNull(rs.getString("ref_no"));
					
					if(countRcd == 1){
						xmlStringBuff.append("<Detail1 dbID='' domID=\"1\" objName=\"misc_pay\" objContext=\"1\">");
						xmlStringBuff.append("<attribute pkNames=\"tran_id:\" status=\"N\" updateFlag=\"A\" selected=\"N\" />");
						xmlStringBuff.append("<tran_id><![CDATA[]]></tran_id>");
						xmlStringBuff.append("<tran_date><![CDATA[" + tranDate + "]]></tran_date>");				
						xmlStringBuff.append("<tran_type><![CDATA["+tranType+"]]></tran_type>");
						
						xmlStringBuff.append("<sundry_type><![CDATA[" + sundryTypeL + "]]></sundry_type>");
						xmlStringBuff.append("<sundry_code><![CDATA[" + sundryCodeL + "]]></sundry_code>");
						xmlStringBuff.append("<site_code><![CDATA[" + siteCodeL + "]]></site_code>");				
						xmlStringBuff.append("<fin_entity><![CDATA[" + finEntityL + "]]></fin_entity>");				
						xmlStringBuff.append("<bank_code><![CDATA[" + bankCodeL + "]]></bank_code>");				
						xmlStringBuff.append("<exch_rate><![CDATA[" + exchRateL + "]]></exch_rate>");
						xmlStringBuff.append("<curr_code><![CDATA[" + currCodeL + "]]></curr_code>");
						xmlStringBuff.append("<ref_no><![CDATA[1234]]></ref_no>");
						String remarks="Payment gen. Multi Sundry Misc. Voucher transaction";
						 
						xmlStringBuff.append("<remarks><![CDATA[" + remarks + "]]></remarks>");
						//xmlStringBuff.append("<net_amt><![CDATA[" + totAmt + "]]></net_amt>");
						//xmlStringBuff.append("<net_amt__bc><![CDATA[" + totAmt + "]]></net_amt__bc>");
						
						xmlStringBuff.append("<acct_code__bal><![CDATA[" + acctCodeL + "]]></acct_code__bal>");
						xmlStringBuff.append("<cctr_code__bal><![CDATA[" + cctrCodeL + "]]></cctr_code__bal>");
						xmlStringBuff.append("<acct_code__bc><![CDATA[" + acctCodeL + "]]></acct_code__bc>");
						xmlStringBuff.append("<cctr_code__bc><![CDATA[" + cctrCodeL + "]]></cctr_code__bc>");
						xmlStringBuff.append("<curr_code__bc><![CDATA[" + currCodeL + "]]></curr_code__bc>");
						xmlStringBuff.append("<exch_rate__bc><![CDATA[" + exchRateL + "]]></exch_rate__bc>");
						xmlStringBuff.append("<auto_paid><![CDATA[Y]]></auto_paid>");
						
						xmlStringBuff.append("<chg_user/>");
						xmlStringBuff.append("<chg_date/>");
						xmlStringBuff.append("<chg_term/>");
						xmlStringBuff.append("</Detail1>");
					}
					
					xmlStringBuff.append("<Detail2 objContext=\"2\" objName=\"misc_pay\" domID=\"1\" dbID=''>");
					xmlStringBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
					xmlStringBuff.append("<tran_id/>");
					xmlStringBuff.append("<line_no><![CDATA[1]]></line_no>");
					xmlStringBuff.append("<tran_ser><![CDATA[M-VOUC]]></tran_ser>");				
					xmlStringBuff.append("<vouch_no><![CDATA["+refNo+"]]></vouch_no>");
					xmlStringBuff.append("<net_amt><![CDATA["+totAmt+"]]></net_amt>");
					xmlStringBuff.append("<pay_amt><![CDATA["+totAmt+"]]></pay_amt>");
					xmlStringBuff.append("<curr_code><![CDATA["+currCodeL+"]]></curr_code>");
					xmlStringBuff.append("<exch_rate__vouch><![CDATA["+exchRateL+"]]></exch_rate__vouch>");				
					xmlStringBuff.append("<diff_amt__exch><![CDATA[0]]></diff_amt__exch>");
					xmlStringBuff.append("<acct_code__ap><![CDATA["+acctCodeL+"]]></acct_code__ap>");
					xmlStringBuff.append("<cctr_code__ap><![CDATA["+cctrCodeL+"]]></cctr_code__ap>");
					xmlStringBuff.append("</Detail2>");
					
				} //end while
			}else{
				System.out.println("Payment mode not Eletronic12345.............");
				
				while(rs.next()){
					xmlStringBuff = new StringBuffer();					
					countRcd++;
					sundryCodeL=checkNull(rs.getString("sundry_code"));
					sundryTypeL=checkNull(rs.getString("sundry_type"));
					payModeL=checkNull(rs.getString("pay_mode"));
					bankCodeL=checkNull(rs.getString("bank_code"));
					totAmt=rs.getDouble("tot_amt");
					currCodeL=checkNull(rs.getString("curr_code"));
					exchRateL=rs.getDouble("exch_rate");
					siteCodeL=checkNull(rs.getString("site_code"));					
					finEntityL=rs.getString("fin_entity");
					acctCodeL=checkNull(rs.getString("acct_code"));
					cctrCodeL=checkNull(rs.getString("cctr_code"));
					autoPay=rs.getString("auto_pay");		
					refNo=checkNull(rs.getString("ref_no"));
					
					xmlStringBuff.append(headerStr);			
					xmlStringBuff.append("<Detail1 dbID='' domID=\"1\" objName=\"misc_pay\" objContext=\"1\">");
					xmlStringBuff.append("<attribute pkNames=\"tran_id:\" status=\"N\" updateFlag=\"A\" selected=\"N\" />");
					xmlStringBuff.append("<tran_id><![CDATA[]]></tran_id>");
					xmlStringBuff.append("<tran_date><![CDATA[" + tranDate + "]]></tran_date>");				
					xmlStringBuff.append("<tran_type><![CDATA["+tranType+"]]></tran_type>");
					
					xmlStringBuff.append("<sundry_type><![CDATA[" + sundryTypeL + "]]></sundry_type>");
					xmlStringBuff.append("<sundry_code><![CDATA[" + sundryCodeL + "]]></sundry_code>");
					xmlStringBuff.append("<site_code><![CDATA[" + siteCodeL + "]]></site_code>");				
					xmlStringBuff.append("<fin_entity><![CDATA[" + finEntityL + "]]></fin_entity>");				
					xmlStringBuff.append("<bank_code><![CDATA[" + bankCodeL + "]]></bank_code>");				
					xmlStringBuff.append("<exch_rate><![CDATA[" + exchRateL + "]]></exch_rate>");
					xmlStringBuff.append("<curr_code><![CDATA[" + currCodeL + "]]></curr_code>");
					xmlStringBuff.append("<ref_no><![CDATA[1234]]></ref_no>");
					String remarks="Payment gen. Multi Sundry Misc. Voucher transaction";
					 
					xmlStringBuff.append("<remarks><![CDATA[" + remarks + "]]></remarks>");
					//xmlStringBuff.append("<net_amt><![CDATA[" + totAmt + "]]></net_amt>");
					//xmlStringBuff.append("<net_amt__bc><![CDATA[" + totAmt + "]]></net_amt__bc>");
					
					xmlStringBuff.append("<acct_code__bal><![CDATA[" + acctCodeL + "]]></acct_code__bal>");
					xmlStringBuff.append("<cctr_code__bal><![CDATA[" + cctrCodeL + "]]></cctr_code__bal>");
					xmlStringBuff.append("<acct_code__bc><![CDATA[" + acctCodeL + "]]></acct_code__bc>");
					xmlStringBuff.append("<cctr_code__bc><![CDATA[" + cctrCodeL + "]]></cctr_code__bc>");
					xmlStringBuff.append("<curr_code__bc><![CDATA[" + currCodeL + "]]></curr_code__bc>");
					xmlStringBuff.append("<exch_rate__bc><![CDATA[" + exchRateL + "]]></exch_rate__bc>");
					xmlStringBuff.append("<auto_paid><![CDATA[Y]]></auto_paid>");
					
					xmlStringBuff.append("<chg_user/>");
					xmlStringBuff.append("<chg_date/>");
					xmlStringBuff.append("<chg_term/>");
					xmlStringBuff.append("</Detail1>");
					
					
					xmlStringBuff.append("<Detail2 objContext=\"2\" objName=\"misc_pay\" domID=\"1\" dbID=''>");
					xmlStringBuff.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
					xmlStringBuff.append("<tran_id/>");
					xmlStringBuff.append("<line_no><![CDATA[1]]></line_no>");
					xmlStringBuff.append("<tran_ser><![CDATA[M-VOUC]]></tran_ser>");				
					xmlStringBuff.append("<vouch_no><![CDATA["+refNo+"]]></vouch_no>");
					xmlStringBuff.append("<net_amt><![CDATA["+totAmt+"]]></net_amt>");
					xmlStringBuff.append("<pay_amt><![CDATA["+totAmt+"]]></pay_amt>");
					xmlStringBuff.append("<curr_code><![CDATA["+currCodeL+"]]></curr_code>");
					xmlStringBuff.append("<exch_rate__vouch><![CDATA["+exchRateL+"]]></exch_rate__vouch>");				
					xmlStringBuff.append("<diff_amt__exch><![CDATA[0]]></diff_amt__exch>");
					xmlStringBuff.append("<acct_code__ap><![CDATA["+acctCodeL+"]]></acct_code__ap>");
					xmlStringBuff.append("<cctr_code__ap><![CDATA["+cctrCodeL+"]]></cctr_code__ap>");
					xmlStringBuff.append("</Detail2>");
					
					xmlStringBuff.append("</Header0>");
					xmlStringBuff.append("</group0>");
					xmlStringBuff.append("</DocumentRoot>");
				
					xmlString = xmlStringBuff.toString();
					xmlStringBuff=null;
					System.out.println("Final xmlString misc. payments---->>["+xmlString+"]");
					retString = saveData(siteCodeL,xmlString,conn);
					System.out.println("After save retString---->>["+retString+"]");
					if (retString.indexOf("Success") > -1)
					{
						System.out.println("retString.indexOf(Success) > -1)");
						String[] arrayForTranId = retString.split("<TranID>");
						int endIndex = arrayForTranId[1].indexOf("</TranID>");
						miscPayTranId = arrayForTranId[1].substring(0,endIndex);					
						System.out.println("miscPayTranId12345------>>>["+miscPayTranId+"]");							
					}else{
						conn.rollback();
						System.out.println("payment NOT GENERATED FOR Employee");	
						return retString;					
					}
					
					
				} //end while
			} //end pay mode
				
				
				
					
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt !=null){
				pstmt.close();
				pstmt=null;
				}
				
				if("T".equalsIgnoreCase(payMode)){
				xmlStringBuff.append("</Header0>");
				xmlStringBuff.append("</group0>");
				xmlStringBuff.append("</DocumentRoot>");
			
				xmlString = xmlStringBuff.toString();
				System.out.println("Final xmlString misc. payments Eletronic Mode---->>["+xmlString+"]");
				retString = saveData(siteCodeL,xmlString,conn);
				System.out.println("After save retString---->>["+retString+"]");
				if (retString.indexOf("Success") > -1)
				{
					System.out.println("retString.indexOf(Success) > -1)");
					String[] arrayForTranId = retString.split("<TranID>");
					int endIndex = arrayForTranId[1].indexOf("</TranID>");
					miscPayTranId = arrayForTranId[1].substring(0,endIndex);					
					System.out.println("miscPayTranId12345------>>>["+miscPayTranId+"]");					
				}else{
					conn.rollback();
					System.out.println("payment NOT GENERATED FOR Employee");	
					return retString;					
				}
				}
			
			
		} //end miscTranID
		
		
	}
	catch(Exception e)
	{
		System.out.println("Exception in generateAutoPayment method........");
		e.printStackTrace();
		return "Error";
	}
	return retString;
}


public String checkNull(String input)	
{
	if (input == null)
	{
		input="";
	}
	return input.trim();
}

}
