package ibase.webitm.ejb.fin;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.StringTokenizer;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

public class MiscPayGltracePreview extends ActionHandlerEJB implements MiscPayGltracePreviewLocal,MiscPayGltracePreviewRemote
{
	String errString = "";
	
	
	public String preview(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		System.out.println("Preview Method is called for Purchase Voucher");
		String retString = "";
	    boolean adj_amt_flag = true,amt_upd_flag = true,retenamt_flag = true,hold_tax_flag = true;
		Connection conn = null;
		String sql = "",sql1 = "";
		PreparedStatement pstmt = null,pstmt1 = null,pstmt2 = null;
		ResultSet rs = null,rs1 = null,rs2 = null;
		FinCommon finCommon = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		ValidatorEJB validatorEJB = null;
		HashMap hdrmap = new HashMap();
		Date effDate = null;
		Date tranDate = null;
		Date refDate = null;
		String tranid,finentity = "",sitecode = "",sundaryType = "",sundrycode = "",acct_code = "",cctr_code = "",empcode = "",analcode = "",currCode = "";
		Double exchRate = 0.0,drAmt  = 0.0,bankCharges = 0.0,Amt = 0.0,bnkChg = 0.0,exchRateDet = 0.0,exchRateBc = 0.0;
		Double payAmt = 0.0,discountAmt = 0.0,netAmt = 0.0, taxAmt = 0.0,totNetAmt = 0.0,discAmt = 0.0,discountAmtDet = 0.0,diffAmtExch = 0.0;
		String acctCodeDisc = "",cctrCodeDisc = "",acctCodeCf = "",cctrCodeCf = "",cctrCodeAp = "",tranType = "";
		String lineNoDet = "";
		String loginEmpCode = "",currCodeBal = "",remarks = "",acctCodeBc = "",cctrCodeBc = "",currCodeBc = "",acctCodeAp = "";

		String tranSer = "", ref = "", party_doc_ref = "", acctCodeTran = "", cctrCodeTran = "", tax_reco_cctr = "", acctCodeReco = "", cctrCodeReco = "";
		String[] tax_reco_acct;
		String acct_code__revr = "", cctr_code__revr = "";
		Double miscAmt = 0.0;
		Double payAmtDet = 0.0, discAmtDet = 0.0, taxAmtDet = 0.0, reco_amount = 0.0,AmountDet =0.0;
		
		
		
		
		
		System.out.println("tran id = "+tranID);
		boolean connStatus=false;
		boolean bchg = false;
		try{
			if ( conn == null )//changed by Gulzar - 25/11/11
			{
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				conn.setAutoCommit(false);
				connDriver = null;
				connStatus = true;
			}
			
			finCommon = new FinCommon();
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			validatorEJB = new ValidatorEJB();
			loginEmpCode = validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			System.out.println("Printing loginEmpCode---1-----"+loginEmpCode+"--");
	
			sql = "select tran_id,tran_Date,ref_date,tran_type,fin_entity,site_code,bank_code,acct_code__bal,cctr_code__bal,curr_code__bc,curr_code,exch_rate,bank_charges,discount_amt,acct_code__bc,cctr_code__bc,curr_code__bc,exch_rate__bc,diff_amt__exch,acct_code__cf,cctr_code__cf from misc_payment where tran_id = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranID);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				tranid = rs.getString("tran_id");
				tranDate = new Timestamp(rs.getDate("tran_Date").getTime());
				tranType = rs.getString("tran_type");
				effDate = new Timestamp(rs.getDate("ref_date").getTime());
				finentity = rs.getString("fin_entity");
				sitecode = rs.getString("site_code");
				sundaryType = "B";
				sundrycode = rs.getString("bank_code");
				acct_code = rs.getString("acct_code__bal");
				cctr_code = rs.getString("cctr_code__bal");
				currCodeBal = rs.getString("curr_code__bc");
				
				currCode = rs.getString("curr_code");
				exchRate = rs.getDouble("exch_rate");
				bankCharges = rs.getDouble("bank_charges");
			    discAmt = rs.getDouble("discount_amt");
			    acctCodeBc = rs.getString("acct_code__bc");
			    cctrCodeBc = rs.getString("cctr_code__bc");
			    currCodeBc = rs.getString("curr_code__bc");
			    exchRateBc = rs.getDouble("exch_rate__bc");
			    diffAmtExch = rs.getDouble("diff_amt__exch");
			    acctCodeCf = rs.getString("acct_code__cf");
			    cctrCodeCf = rs.getString("cctr_code__cf");
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			
			
			
			
			//************fetching records from detail for calculation
			sql1 = "select line_no from misc_paydet where tran_id = ?";
			pstmt1 = conn.prepareStatement(sql1);
			pstmt1.setString(1,tranID);
			rs1 = pstmt1.executeQuery();
			while(rs1.next())
			{
				lineNoDet = rs1.getString("line_no");
			    System.out.println("Line NO Detail***************"+lineNoDet);
				
				sql = "select (case when exch_rate is null then 0 else exch_rate end) as exchRateDet,pay_amt,(case when tax_amt is null then 0 else tax_amt end) as taxAmt, (case when discount_amt is null then 0 else discount_amt end) as discountAmt  from misc_paydet where tran_id = ? and line_no = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,tranID);
				pstmt.setString(2,lineNoDet);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					exchRateDet = rs.getDouble("exchRateDet");
					if(exchRateDet == 0.0)
					{
						exchRateDet = exchRate;
					}
					payAmt = rs.getDouble("pay_amt");
					taxAmt = rs.getDouble("taxAmt");
					discountAmt = rs.getDouble("discountAmt");
					netAmt = (payAmt + taxAmt + discountAmt) * (exchRateDet/exchRate );
					totNetAmt = totNetAmt + netAmt;
					

				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
			}
			rs1.close();
			rs1=null;
			pstmt1.close();
			pstmt1=null;
			
			lineNoDet = "    " + lineNoDet;
			lineNoDet = lineNoDet.substring(lineNoDet.length() - 3,lineNoDet.length());
		
		
			//Header coding ********************************************************************************************
			 
		                hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",sundaryType);
						hdrmap.put("sundry_code",sundrycode);
						hdrmap.put("acct_code",acct_code);
						hdrmap.put("cctr_code",cctr_code);
						hdrmap.put("emp_code",empcode);
						hdrmap.put("anal_code",analcode);
						hdrmap.put("curr_code", currCode);
						hdrmap.put("exch_rate",exchRate);
						hdrmap.put("dr_amt",0.0);
						if(bankCharges > 0.0)
						{
							sql = "select sum(case when net_pay_amt is null then 0 else net_pay_amt end) as Amt,sum(case when bank_charges is null then 0 else bank_charges end) as bnkChg from misc_paydet where tran_id = ?";
							
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranID);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								Amt = rs1.getDouble("Amt");
								bnkChg = rs1.getDouble("bnkChg");
								
							}
							rs1.close();
							rs1=null;
							pstmt1.close();
							pstmt1=null;
							if(currCode == currCodeBal)
							{
								hdrmap.put("cr_amt",(Amt + bnkChg));
							}
							else{
								hdrmap.put("cr_amt",(Amt));
							}
							bchg = true;
						}
						
						else{
							
							hdrmap.put("cr_amt",(totNetAmt));
						}
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","M-PAY");
						hdrmap.put("ref_id",tranID);
						hdrmap.put("remarks",remarks);
						
						System.out.println("calling Gltrace Update for Header******** 1*******************");
						
						errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
						if(errString != null && errString.trim().length() > 0)
						{
							return errString;
						}
						
						if(discAmt > 0.0)
						{
							System.out.println("discAmt**************"+discAmt);
							sql = "select sum(case when discount_amt is null then 0 else discount_amt end) as disc_amt, acct_code__disc as acct_code_disc,cctr_code__disc as cctr_code_disc,cctr_code__ap,acct_code__ap,sundry_type,sundry_code from misc_paydet where tran_id = ?  group by acct_code__disc,cctr_code__disc";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranID);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
						      discountAmtDet = rs.getDouble("disc_amt");
						      acctCodeDisc = rs.getString("acct_code_disc");
						      cctrCodeDisc = rs.getString("cctr_code_disc");
						      cctrCodeAp = rs.getString("cctr_code__ap");
						      acctCodeAp = rs.getString("acct_code__ap");
							}
						
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
						
							if(discountAmtDet != 0.0)
							{
								hdrmap.put("acct_code",acctCodeDisc);
								hdrmap.put("cctr_code",cctrCodeDisc);
								hdrmap.put("dr_amt",0.0);
								hdrmap.put("cr_amt",(discountAmtDet));
								hdrmap.put("sundry_type","O");
								System.out.println("calling Gltrace Update for Header******** 2*******************");
								
								errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
								System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
								if(errString != null && errString.trim().length() > 0)
								{
									return errString;
								}
							}
						}
						
						//*******************Debit Side Entry 
					if(discAmt > 0.0)
					{
						
						sql = "select sum(case when discount_amt is null then 0 else discount_amt end) as disc_amt, acct_code__disc as acct_code_disc,cctr_code__disc as cctr_code_disc from misc_paydet where tran_id = ?  group by acct_code__disc,cctr_code__disc";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,tranID);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
					      discountAmtDet = rs.getDouble("disc_amt");
					      acctCodeDisc = rs.getString("acct_code_disc");
					      cctrCodeDisc = rs.getString("cctr_code_disc");
						}
					
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						if(discountAmtDet != 0.0)
						{
							System.out.println("discountAmtDet*************"+discountAmtDet);
							hdrmap.put("acct_code",acctCodeDisc);
							hdrmap.put("cctr_code",cctrCodeDisc);
							hdrmap.put("remarks", "Discount Receivable");
							hdrmap.put("dr_amt",discountAmtDet);
							hdrmap.put("cr_amt",0.0);
							hdrmap.put("sundry_type","O");
							System.out.println("calling Gltrace Update for Header******** 3*******************");
							
							errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
							System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
						}
						
					}
					// update bank charges into gltrace
					if(bankCharges > 0.0)
					{
						    hdrmap.put("tran_date",tranDate);
							hdrmap.put("eff_date", effDate);
							hdrmap.put("fin_entity",finentity);
							hdrmap.put("site_code",sitecode);
							hdrmap.put("sundry_type","O");
							hdrmap.put("sundry_code","");
							hdrmap.put("acct_code",acctCodeBc);
							hdrmap.put("cctr_code",cctrCodeBc);
							hdrmap.put("emp_code",empcode);
							hdrmap.put("anal_code",analcode);
							hdrmap.put("curr_code", currCodeBc);
							hdrmap.put("exch_rate",exchRateBc);
							hdrmap.put("dr_amt",bankCharges);
							hdrmap.put("cr_amt", 0.0);
							hdrmap.put("ref_type","F");
							hdrmap.put("ref_ser","M-PAY");
							hdrmap.put("ref_id",tranID);
							hdrmap.put("remarks",remarks);
							
                            System.out.println("calling Gltrace Update for Header******** 4 *******************");
							errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
							System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
					}
					
					// update currency fluctuation to gltrace
					if(diffAmtExch != 0.0)
					{
						    hdrmap.put("tran_date",tranDate);
							hdrmap.put("eff_date", effDate);
							hdrmap.put("fin_entity",finentity);
							hdrmap.put("site_code",sitecode);
							hdrmap.put("sundry_type","O");
							hdrmap.put("sundry_code","");
							hdrmap.put("acct_code",acctCodeCf);
							if(cctrCodeCf == null || cctrCodeCf.trim().length() == 0)
							{
								hdrmap.put("cctr_code",cctrCodeAp);

							}
							else{
								hdrmap.put("cctr_code",cctrCodeCf);
							}
							hdrmap.put("emp_code",empcode);
							hdrmap.put("anal_code",analcode);
							hdrmap.put("curr_code", currCode);
							hdrmap.put("exch_rate",1);
							
							if(diffAmtExch > 0.0)
							{
								hdrmap.put("dr_amt",0.0);
								hdrmap.put("cr_amt",diffAmtExch);
							}
							else{
								hdrmap.put("dr_amt",0.0 - diffAmtExch);
								hdrmap.put("cr_amt",0.0);
							}
							hdrmap.put("ref_type","F");
							hdrmap.put("ref_ser","M-PAY");
							hdrmap.put("ref_id",tranID);
							hdrmap.put("remarks",remarks);
							
                            System.out.println("calling Gltrace Update for Header******** 5 *******************");
							errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
							System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
	
					}
					// if bank charges in diff. currency then insert a rec. in gltrace,
					// update sundry bal and insert into banktran_log
					
					if(bchg)
					{
						hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type","B");
						hdrmap.put("sundry_code",sundrycode);
			
						hdrmap.put("acct_code",acct_code);
						hdrmap.put("cctr_code",cctr_code);
						hdrmap.put("emp_code",empcode);
						hdrmap.put("anal_code",analcode);
						hdrmap.put("curr_code", currCodeBc);
						hdrmap.put("exch_rate",exchRateBc);
						hdrmap.put("dr_amt",0.0);
						hdrmap.put("cr_amt",bankCharges);
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","M-PAY");
						hdrmap.put("ref_id",tranID);
						hdrmap.put("remarks",remarks);
						
                        System.out.println("calling Gltrace Update for Header******** 6 *******************");
						errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
						System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
						if(errString != null && errString.trim().length() > 0)
						{
							return errString;
						}
					}
					
					//********************************Detail Coding**************************
					//************fetching records from detail
					sql = "select line_no from misc_paydet where tran_id = ?";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1,tranID);
					rs2 = pstmt2.executeQuery();
					while(rs2.next())
					{
						lineNoDet = rs2.getString("line_no");
						
						System.out.println("lineNoDet***"+lineNoDet);
						
						
					sql = "select sundry_type,sundry_code,tran_ser,vouch_no,curr_code,exch_rate__vouch,pay_amt,(case when discount_amt is null then 0 else discount_amt end) as discount_amt,acct_code__ap,acct_code__disc,cctr_code__disc,cctr_code__ap   from misc_paydet where tran_id = ? and line_no = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranID);
					pstmt.setString(2,lineNoDet);
					rs = pstmt.executeQuery();
					while(rs.next())
					{
						tranSer = rs.getString("tran_ser");
						ref = rs.getString("vouch_no");
						// for each detail do the posting
						hdrmap.put("tran_date",tranDate);
						hdrmap.put("eff_date", effDate);
						hdrmap.put("fin_entity",finentity);
						hdrmap.put("site_code",sitecode);
						hdrmap.put("sundry_type",rs.getString("sundry_type"));
						hdrmap.put("sundry_code",rs.getString("sundry_code"));
						hdrmap.put("acct_code",rs.getString("acct_code__ap"));
						hdrmap.put("cctr_code",rs.getString("cctr_code__ap"));
						hdrmap.put("emp_code",empcode);
						hdrmap.put("anal_code",analcode);
						hdrmap.put("curr_code", rs.getString("curr_code"));
						hdrmap.put("exch_rate",rs.getString("exch_rate__vouch"));
						payAmtDet = rs.getDouble("pay_amt");
						discAmtDet = rs.getDouble("discount_amt");
						if(payAmtDet > 0.0)
						{
							if(discAmtDet > 0.0)
							{
								hdrmap.put("dr_amt",(payAmtDet - discAmtDet ));
							}
							else {
								hdrmap.put("dr_amt",payAmtDet);
							}
							hdrmap.put("cr_amt",0.0);
						}
						else {
							hdrmap.put("dr_amt",0.0);
							if(discAmtDet > 0.0)
							{
								hdrmap.put("cr_amt",0.0 - (payAmtDet - discAmtDet ));
							}
							else {
								hdrmap.put("cr_amt",0.0 - payAmtDet);
							}
						}
						
						hdrmap.put("ref_type","F");
						hdrmap.put("ref_ser","M-PAY");
						hdrmap.put("ref_id",tranID);
						if(remarks.equalsIgnoreCase("Automatic Payment"))
						{
							hdrmap.put("remarks","Against voucher no. : "+ref+ " dated : "+new Timestamp(rs.getDate("vouch_date").getTime()));
						}
						else {
							hdrmap.put("remarks",remarks);
						}
						party_doc_ref = finCommon.gfGetPartyDocRef(tranSer,ref,conn);//transer and vouch_no
						hdrmap.put("party_doc_ref",party_doc_ref);
						
						 System.out.println("calling Gltrace Update for Detail******** 1 *******************");
							errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
							System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
							if(errString != null && errString.trim().length() > 0)
							{
								return errString;
							}
							
							//***********
							sql = "	select sum(case when tax_amt is null then 0 else tax_amt end) as tax_amt, acct_code, cctr_code ,case when sum(reco_amount) is null then 0 else sum(reco_amount) end from taxtran where tran_code = 'M-PAY' and tran_id = ?  and line_no = ?	and effect <> 'N' group by acct_code, cctr_code  ";
							
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranID);
							pstmt1.setString(2,lineNoDet);
							rs1 = pstmt1.executeQuery();
							while(rs1.next())
							{
							taxAmtDet = rs1.getDouble("tax_amt");
							acctCodeTran = rs1.getString("acct_code");
							cctrCodeTran = rs1.getString("cctr_code");
							reco_amount = rs1.getDouble("reco_amount");
							}
							rs1.close();
				            rs1=null;
				            pstmt1.close();
				            pstmt1=null;
				            
				           if(taxAmtDet != 0.0) 
				           {
				        	   if(cctr_code == null)
				        	   {
				        		   cctr_code = cctrCodeAp;
				        	   }
				        	    hdrmap.put("tran_date",tranDate);
								hdrmap.put("eff_date", effDate);
								hdrmap.put("fin_entity",finentity);
								hdrmap.put("site_code",sitecode);
								hdrmap.put("sundry_type","O");
								hdrmap.put("sundry_code","");
								hdrmap.put("acct_code",acctCodeTran);
								hdrmap.put("cctr_code",cctrCodeTran);
								hdrmap.put("curr_code", currCode);
								hdrmap.put("exch_rate",exchRate);
								hdrmap.put("ref_type","F");
								hdrmap.put("ref_ser","M-PAY");
								hdrmap.put("ref_id",tranID);
								if(taxAmtDet > 0.0)
								{
									hdrmap.put("dr_amt",taxAmtDet - reco_amount);
									hdrmap.put("cr_amt",0.0);
								}
								else {
									hdrmap.put("dr_amt",0.0);
									hdrmap.put("cr_amt",0.0 - (taxAmtDet - reco_amount));
								}
								party_doc_ref = finCommon.gfGetPartyDocRef(tranSer,ref,conn);//transer and vouch_no
								hdrmap.put("party_doc_ref",party_doc_ref);
								
								 System.out.println("calling Gltrace Update for Detail******** 2 *******************");
									errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
									System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
									if(errString != null && errString.trim().length() > 0)
									{
										return errString;
									}
									
				           }
				           
				           reco_amount = 0.0;
				           tax_reco_cctr = finCommon.getAcctDetrTtype("", "", "TAXRECO", tranType, conn);
				           tax_reco_acct = getTokens(tax_reco_cctr, "~t");
				            
				            sql = "select acct_code__reco,	cctr_code__reco, case when sum(reco_amount) is null then 0 else sum(reco_amount) end from taxtran where tran_code = 'M-PAY' and tran_id	 = ? and line_no   = ? and (case when reco_amount is null then 0 else reco_amount end) <> 0 and effect <> 'N' group by acct_code__reco, cctr_code__reco ";
				            
				        	pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1,tranID);
							pstmt1.setString(2,lineNoDet);
							rs1 = pstmt1.executeQuery();
							while(rs1.next())
							{
								acctCodeReco = rs1.getString("acct_code__reco");
								cctrCodeReco = rs1.getString("cctr_code__reco");
								reco_amount = rs1.getDouble("reco_amount");
							}
							rs1.close();
				            rs1=null;
				            pstmt1.close();
				            pstmt1=null;
				             if(acctCodeReco == null || acctCodeReco.trim().length() == 0 )
				             {
				            	 acctCodeReco = tax_reco_acct.toString();
				            	 cctrCodeReco = tax_reco_cctr;
				             }
				            if(cctrCodeReco == null || cctrCodeReco.trim().length() == 0)
				            {
				            	cctrCodeReco = "";
				            	hdrmap.put("tran_date",tranDate);
								hdrmap.put("eff_date", effDate);
								hdrmap.put("fin_entity",finentity);
								hdrmap.put("site_code",sitecode);
								hdrmap.put("sundry_type","O");
								hdrmap.put("sundry_code","");
								hdrmap.put("acct_code",acctCodeReco);
								hdrmap.put("cctr_code",cctrCodeReco);
								hdrmap.put("curr_code", currCode);
								hdrmap.put("exch_rate",exchRate);
								hdrmap.put("ref_type","F");
								hdrmap.put("ref_ser","M-PAY");
								hdrmap.put("ref_id",tranID);
								if(reco_amount == null)
								{
									reco_amount = 0.0;
								}
								if(reco_amount > 0.0)
								{
									hdrmap.put("dr_amt",reco_amount);
									hdrmap.put("cr_amt",0.0);
								}
								else {
									hdrmap.put("dr_amt",0.0);
									hdrmap.put("cr_amt",0.0 - (reco_amount));
								}
								party_doc_ref = finCommon.gfGetPartyDocRef(tranSer,ref,conn);//transer and vouch_no
								hdrmap.put("party_doc_ref",party_doc_ref);
								
								 System.out.println("calling Gltrace Update for Detail******** 3 *******************");
									errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
									System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
									if(errString != null && errString.trim().length() > 0)
									{
										return errString;
									}
				            }
				            
				           
				            
				            
				       sql = " select acct_code__revr,cctr_code__revr ,sum(misc_vouchdet.amount) as miscAmt from misc_vouchdet,misc_voucher where misc_vouchdet.tran_id =misc_voucher.tran_id and misc_voucher.vouch_type = 'T' and misc_voucher.tran_id in (select vouch_no from misc_paydet where tran_id = ? and tran_ser = 'M-VOUC') and acct_code__revr is not null group by acct_code__revr,cctr_code__revr";
				        pstmt1 = conn.prepareStatement(sql);
						pstmt1.setString(1,tranID);
						
						rs1 = pstmt1.executeQuery();
						while(rs1.next())
						{
							acct_code__revr = rs1.getString("acct_code__revr");
							cctr_code__revr = rs1.getString("acct_code__revr");
							miscAmt = rs1.getDouble("miscAmt");
							
						}
						rs1.close();
			            rs1=null;
			            pstmt1.close();
			            pstmt1=null;
				           if(acct_code__revr != null && acct_code__revr.trim().length() > 0) 
				           {
				        	   if(cctr_code == null)
				        	   {
				        		   hdrmap.put("cctr_code","");
				        	   }
				        	    hdrmap.put("acct_code",acct_code__revr);
								hdrmap.put("cctr_code",cctr_code__revr);
								hdrmap.put("tran_date",tranDate);
								hdrmap.put("eff_date", effDate);
								hdrmap.put("fin_entity",finentity);
								hdrmap.put("site_code",sitecode);
								hdrmap.put("sundry_type","O");
								hdrmap.put("sundry_code","");
								hdrmap.put("curr_code", currCode);
								hdrmap.put("exch_rate",exchRate);
								hdrmap.put("ref_type","F");
								hdrmap.put("ref_ser","M-PAY");
								hdrmap.put("ref_id",tranID);
				        	   AmountDet  = rs.getDouble("amount") ;
				        	   if(AmountDet == null){
				        		   AmountDet = 0.0;
				        		   }
				        		   if(AmountDet > 0.0)
						           {
							        hdrmap.put("dr_amt", 0.0);
							        hdrmap.put("cr_amt", 0.0 - AmountDet);
						           } 
				        		   else {
							        hdrmap.put("dr_amt", 0.0 - AmountDet);
							        hdrmap.put("cr_amt", 0.0);
						                  }
				        		   
				        		   party_doc_ref = finCommon.gfGetPartyDocRef(tranSer,ref,conn);//transer and vouch_no
									hdrmap.put("party_doc_ref",party_doc_ref);
									
									 System.out.println("calling Gltrace Update for Detail******** 4 *******************");
										errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
										System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
										if(errString != null && errString.trim().length() > 0)
										{
											return errString;
										}
										// posting of tax revarsal charge to Recovary account
										  hdrmap.put("acct_code",acct_code__revr);
										  
										  if(AmountDet > 0.0)
								           {
									        hdrmap.put("dr_amt",0.0 - AmountDet);
									        hdrmap.put("cr_amt", 0.0);
								           } 
						        		   else {
									        hdrmap.put("dr_amt",0.0);
									        hdrmap.put("cr_amt", 0.0 - AmountDet);
								                  }
										  System.out.println("calling Gltrace Update for Detail******** 5 *******************");
											errString = finCommon.glTraceUpdate(hdrmap,conn,true); 
											System.out.println("ERROR STRING>>>>>>>>>>>>>>>>>>>>>>["+errString+"]");
											if(errString != null && errString.trim().length() > 0)
											{
												return errString;
											}
				        	   
				           }
				            
					}
	
		            rs.close();
		            rs=null;
		            pstmt.close();
		            pstmt=null;
					}
					rs2.close();
					rs2=null;
					pstmt2.close();
					pstmt2=null;
					
		}
		catch( Exception e)
		{
			if(conn!=null)
			{
				try {
					conn.rollback();
				} catch (SQLException ex) {

					System.out.println("Exception : "+e);
					e.printStackTrace();
					throw new ITMException(e);

				}
			}
			System.out.println("Exception : "+e);
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			
				try
				{
					
					if(errString != null && errString.trim().length() > 0)
					{
						if( conn != null && !conn.isClosed() && connStatus ) 
						{
							if(errString.indexOf("VTCICONF3") > -1)
							{
								conn.commit();
							}
							else
							{
								conn.rollback();
							}
							conn.close();
							conn = null;
						}
					}
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					//conn.close();
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			
		}
		
		
		
		
		return errString;
}
	
	private String[] getTokens( String str, String seperator )
	{
		String val = null;
		ArrayList sArr = new ArrayList();

		StringTokenizer st = new StringTokenizer( str, seperator );
	    while (st.hasMoreTokens()) 
	    {
		  val = st.nextToken();
	      sArr.add( val );
	    }
		st = null;
		int lenArr = sArr.size();
		String str1[] = new String[ lenArr ];
		for( int cnt = 0; cnt < lenArr ; cnt++ )
		{
			str1[ cnt ] = ( String ) sArr.get( cnt );
		}
		return str1;
	}
	
}
