/**
 * @author : Akhilesh Sikarwar 
 * @Version : 1.0
 * Date : 05/02/13
 */
package ibase.webitm.ejb.fin.adv;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
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.text.*;



import javax.ejb.Stateless;
@Stateless
public class DrCrInvConf extends ActionHandlerEJB implements DrCrInvConfLocal,DrCrInvConfRemote 
{
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{
		String retString = "";
		boolean isConn= false;
		Connection conn = null;

		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag, conn );

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch(Exception exception)
		{
			retString = "ERROR";
			System.out.println("Exception in [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	
	public String confirm(String tranId,String xtraParams,String forcedFlag, Connection conn) throws RemoteException,ITMException
	{
		System.out.println("confirm called........");
		String sql = "";
		String confirm = "";
		String siteCode = "";
		String errString = "" ;
		String tranSer = "";
		String loginEmpCode = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		FinCommon finCommon = null;
		GenericUtility genericUtility = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		ValidatorEJB validatorEJB = null;
		System.out.println("tran id = "+tranId);
		boolean connStatus=false;
		String chgUser="";
		try
		{
			if ( conn == null )
			{
				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 confirmed ,site_code,chg_user  from drcr_inv where tran_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				confirm = rs.getString("confirmed") == null ?"N":rs.getString("confirmed");
				siteCode = rs.getString("site_code") == null ?" ":rs.getString("site_code");
				chgUser = rs.getString("chg_user") == null ?" ":rs.getString("chg_user");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if(chgUser==null || chgUser.trim().length()==0)
			{
				chgUser="SYSTEM";
			}
			if("null".equalsIgnoreCase(loginEmpCode) || loginEmpCode==null || loginEmpCode.trim().length()==0)
			{
				sql="select emp_code from users where code=?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,chgUser);
				
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					loginEmpCode=rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				System.out.println("Printing loginEmpCode---2-----"+loginEmpCode+"--");
				
			}
			if(confirm != null  && confirm.equalsIgnoreCase("N"))
			{
				errString = gbfRetrieveDrCr(tranId,siteCode,xtraParams,conn);
				System.out.println("err String from gdfRetrieveMiscRcp ="+errString);
				if(errString != null && errString.trim().length() > 0)
				{
					return errString;
				}
				if(errString != null && errString.trim().length() > 0)
				{
					return errString;
				}
				
				if(errString == null || errString.trim().length() == 0)
				{
					if("null".equalsIgnoreCase(loginEmpCode) || loginEmpCode==null || loginEmpCode.trim().length()==0)
					{
						loginEmpCode="E03952";
					}
					System.out.println("loginEmpCode--3-"+loginEmpCode);
					sql = "update drcr_inv set confirmed = 'Y', conf_date = ?,emp_code__aprv = ? where tran_id = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
					pstmt.setString(2, loginEmpCode);
					pstmt.setString(3, tranId);
					int updateCoount = pstmt.executeUpdate();
					System.out.println("no of row update = "+updateCoount);
					pstmt.close();
					pstmt = null;
					if(updateCoount > 0)
					{
						errString = itmDBAccessEJB.getErrorString("","VTCICONF3","","",conn);
					}
				}
				else 
				{
					//errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
					if(errString.indexOf("<?xml version") == -1)
					{
						errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
					}
					return errString;
				}
			}
			else
			{
				System.out.println("The Selected transaction is already confirmed");
				errString = itmDBAccessEJB.getErrorString("","VTMCONF1","","",conn);
				return errString;
			}
			System.out.println("Error String from confirm method.....[" + errString + "]");
		} 
		catch( Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			
				try
				{
					
					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;
					}
				}
				catch(Exception e)
				{
					//System.out.println("Exception : "+e);
					//e.printStackTrace();
					throw new ITMException(e);
				}
			
		}
		return errString;
	} //end of confirm method

	private String gbfRetrieveDrCr(String tranId,String siteCode,String xtraParams,Connection conn)throws RemoteException,ITMException
	{
		String errCode = "",keyFld = "" , expHdr = "",expDet = "", winName = "", ledgPostconf= "" , ledgPostConf = "",loginEmpCode = "",
				sql = "" , sql1 = "" ,winname ="",acctCode = "",cctrCode= "",invoiceId = "", tranSer = "";
		//String currCode[],custCode[],acctCode[],cctrCode[];
		String acctReco = "", cctrReco = "", taxRecoAcct = "", taxRecoCctr = "", acctExch = "", cctrExch = "", acctRnd = "", cctrRnd = "";



		double recoAmount = 0,diffExchAmt = 0,roundAdj = 0,amountMap = 0, exchRateInv = 0, exchRatehdr = 0 ;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		HashMap HdrMap = null;
		HashMap DetMap = null;
		int lineNo = 0,findIndex = -1;
		ArrayList DetList = new ArrayList();
		ITMDBAccessEJB itmDBAccessEJB = null;
		itmDBAccessEJB = new ITMDBAccessEJB();
		GenericUtility genericUtility = null;
		int cntrHdr = 0,cntr = 0,insertedRow = 0,rows = 0;
		java.sql.Timestamp tranDate = null, today;
		FinCommon finCommon = null;
		try
		{
		
			genericUtility = new GenericUtility();
			finCommon = new FinCommon();
			if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ))
			{
				sql1 = "SELECT tran_id FROM drcr_inv  WHERE tran_id = ? for update ";
			}

			else if ( "mssql".equalsIgnoreCase(CommonConstants.DB_NAME ))
			{
				sql1 = "SELECT tran_id FROM drcr_inv (updlock) WHERE tran_id = ? " ;
			}
			else
			{
				sql1 =" SELECT tran_id  FROM drcr_inv WHERE tran_id =  ? for update nowait" ;
			}
			pstmt1 = conn.prepareStatement(sql1);
			pstmt1.setString(1,tranId);
			rs1 = pstmt1.executeQuery();
			if(rs1.next())
			{
				keyFld = rs1.getString("tran_id") == null ? " ":rs1.getString("tran_id");

			}
			rs1.close();
			rs1 = null;
			pstmt1.close();
			pstmt1 = null;
			today = new java.sql.Timestamp(System.currentTimeMillis()) ;

			winname = "W_DRCRINV";

			sql = "select ledg_post_conf from transetup where upper(tran_window) = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,"W_DRCRINV");
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				ledgPostConf = rs.getString(1);
			}
			else
			{
				errCode = itmDBAccessEJB.getErrorString("","VTSEQ","","",conn);
				return errCode;
			}

			if("Y".equalsIgnoreCase(ledgPostConf))
			{
				SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
				tranDate = java.sql.Timestamp.valueOf(sdf.format(today) + " 00:00:00.000");
				
				
				sql = " update drcr_inv set tran_date = ? where tran_id   = ?" ;
				pstmt = conn.prepareStatement(sql);
				pstmt.setTimestamp(1, tranDate);
				pstmt.setString(2,tranId);
				pstmt.executeUpdate();		
				
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			}
			
			sql = "select tran_id ,tran_date ,fin_entity ,acct_code , cctr_code ,eff_date , invoice_id , amount , curr_code , exch_rate , remarks , " +
					"site_code,  anal_code ,  drcr_flag  ,  tran_id__rcv,  confirmed,conf_date ,emp_code__aprv, tran_type , tran_ser ,   " +
					"due_date ,cr_term ,amount__bc, item_ser , round_adj , sundry_type , sundry_code ,  " +
					"  acct_code__cf , cctr_code__cf  , diff_amt_exch ,voucher_no , invvouc_flag , parent__tran_id , rev__tran " +
					" from drcr_inv where tran_id = ?";


			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				HdrMap = new HashMap();
				HdrMap.put("tran_id",tranId);
				HdrMap.put("tran_date",rs.getTimestamp("tran_date"));
				HdrMap.put("fin_entity",rs.getString("fin_entity"));
				HdrMap.put("acct_code",rs.getString("acct_code"));
				cctrCode = rs.getString("cctr_code");
				if (cctrCode == null || "null".equals(cctrCode))
				{
					cctrCode = "    ";
				}

				HdrMap.put("cctr_code",cctrCode);
				HdrMap.put("eff_date",rs.getTimestamp("eff_date"));
				HdrMap.put("invoice_id",rs.getString("invoice_id"));
				HdrMap.put("amount",rs.getDouble("amount"));
				HdrMap.put("curr_code",rs.getString("curr_code"));
				exchRatehdr = rs.getDouble("exch_rate");
				if (exchRatehdr == 0 )
				{
					exchRatehdr = 1;
				}
				HdrMap.put("exch_rate",exchRatehdr);
				HdrMap.put("remarks",rs.getString("remarks"));
				HdrMap.put("site_code",rs.getString("site_code"));
				HdrMap.put("anal_code",rs.getString("anal_code"));
				HdrMap.put("drcr_flag",rs.getString("drcr_flag"));
				HdrMap.put("tran_id__rcv",rs.getString("tran_id__rcv"));
				HdrMap.put("confirmed",rs.getString("confirmed"));
				HdrMap.put("conf_date",rs.getTimestamp("conf_date"));
				HdrMap.put("emp_code__aprv",rs.getString("emp_code__aprv"));
				HdrMap.put("tran_type",rs.getString("tran_type"));
				HdrMap.put("tran_ser",rs.getString("tran_ser"));
				tranSer = rs.getString("tran_ser");
				HdrMap.put("due_date",rs.getTimestamp("due_date"));
				HdrMap.put("cr_term",rs.getString("cr_term"));
				HdrMap.put("amount__bc",rs.getDouble("amount__bc"));
				HdrMap.put("item_ser",rs.getString("item_ser"));
				HdrMap.put("round_adj",rs.getDouble("round_adj"));
				HdrMap.put("sundry_type",rs.getString("sundry_type"));
				HdrMap.put("sundry_code",rs.getString("sundry_code"));
				HdrMap.put("acct_code__cf",rs.getString("acct_code__cf"));
				cctrCode = rs.getString("cctr_code__cf");
				if (cctrCode == null || "null".equals(cctrCode))
				{
					cctrCode = "    ";
				}
				HdrMap.put("cctr_code__cf",cctrCode);
				HdrMap.put("diff_amt_exch",rs.getDouble("diff_amt_exch"));
				HdrMap.put("voucher_no",rs.getString("voucher_no"));
				HdrMap.put("invvouc_flag",rs.getString("invvouc_flag"));
				HdrMap.put("parent__tran_id",rs.getString("parent__tran_id"));
				HdrMap.put("rev__tran",rs.getString("rev__tran"));

				System.out.println(" Header Map..........");
				System.out.println(HdrMap.toString());
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = " select acct_code, cctr_code, sum(tax_amt) as tax_amt, case when sum(reco_amount) is null then 0 else sum(reco_amount) end as reco_amt "
				+ " from taxtran where tran_code = ? and tran_id = ? and tax_amt <> 0 and (case when reco_amount is null then 0 else reco_amount end) <> 0 and effect <> 'N' group by acct_code, cctr_code";              
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, (String) HdrMap.get("tran_ser"));
			pstmt.setString(2,tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				acctCode = rs.getString("acct_code");
				acctCode = rs.getString("cctr_code");
				if (acctCode == null)
				{
					if (!"CRNVOU".equals(tranSer) )
					{
						sql = " select acct_code__sal, cctr_code__sal  "
							+ " from invoice  where invoice_id = ? ";              
						pstmt1 = conn.prepareStatement(sql);
						
						pstmt1.setString(1, (String) HdrMap.get("invoice_id"));
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							acctCode = rs1.getString("acct_code__sal");
							acctCode = rs1.getString("cctr_code__sal");
						}
						else
						{
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
							errCode = "VTINVCD1";
							break;
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					else
					{
						errCode = "VTACTAX"; 
						break;
					}
				}
				if (cctrCode == null || cctrCode.trim().length() == 0 )
				{
					cctrCode = (String) HdrMap.get("cctr_code");
				}
				if (cctrCode == null || "null".equals(cctrCode))
				{
					cctrCode = "    ";
				}
				
				/*findIndex = -1;
				findIndex = findExistingIndex(DetList,acctCode, cctrCode, (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));
				if (findIndex > -1) 
				{
					DetMap = (HashMap) DetList.get(findIndex);
					amountMap = (double) Double.parseDouble( "" + DetMap.get("amount"));
					DetMap.put("amount", amountMap + rs.getDouble("tax_amt") - rs.getDouble("reco_amt") );
					DetList.set(findIndex, DetMap);
				}
				else
				{*/
					DetMap = new HashMap();
					DetMap.put("tran_id",(String) HdrMap.get("tran_id"));
					lineNo++;
					DetMap.put("line_no",(String) "" +lineNo);
					DetMap.put("acct_code",acctCode);
					DetMap.put("cctr_code",cctrCode);
					DetMap.put("amount", rs.getDouble("tax_amt") - rs.getDouble("reco_amt"));
					DetMap.put("emp_code","");
					DetMap.put("anal_code","");
					DetMap.put("exch_rate", (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));
					DetList.add(DetMap);
				//}
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			if (errCode != null && errCode.trim().length() > 0 )
			{
				return errCode;
			}
			recoAmount = 0;
			taxRecoCctr = finCommon.getAcctDetrTtype(" ", " ", "TAXRECO", (String) HdrMap.get("tran_type"), conn);
			if (taxRecoCctr != null && taxRecoCctr.trim().length() > 0)
			{
				taxRecoAcct = taxRecoCctr.substring(0,taxRecoCctr.indexOf(","));
				taxRecoCctr = taxRecoCctr.substring(taxRecoCctr.indexOf(",")+1);
			}

			sql = " select acct_code__reco,	cctr_code__reco, case when sum(reco_amount) is null then 0 else sum(reco_amount) end reco_amount "
				+ " from taxtran where tran_code = ? and tran_id = ? and tax_amt <> 0 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";              
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, (String) HdrMap.get("tran_ser"));
			pstmt.setString(2,tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
			
				acctReco = rs.getString("acct_code__reco");
				cctrReco = rs.getString("cctr_code__reco");
				recoAmount = rs.getDouble("reco_amount");
				if (acctReco == null || acctReco.trim().length() == 0 )
				{
					cctrReco = taxRecoCctr;
					acctReco = taxRecoAcct;
				}
			
				// If ACCT Code is NULL/SPACE Then Exit
				if (acctReco == null || acctReco.trim().length() == 0 )
				{
					errCode = "VTACCTRECO"; 
					break;
				}
				if (cctrReco == null || "null".equals(cctrReco))
				{
					cctrReco = "    ";
				}
				/*findIndex = -1;
				findIndex = findExistingIndex(DetList,acctReco, cctrReco, (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));
				if (findIndex > -1) 
				{
					DetMap = (HashMap) DetList.get(findIndex);
					amountMap = (double) Double.parseDouble( "" + DetMap.get("amount"));
					DetMap.put("amount", amountMap + recoAmount);
					DetList.set(findIndex, DetMap);
				}
				else
				{*/
					DetMap = new HashMap();
					DetMap.put("tran_id",(String) HdrMap.get("tran_id"));
					lineNo++;
					DetMap.put("line_no",(String) "" +lineNo);
					DetMap.put("acct_code",acctReco);
					DetMap.put("cctr_code",cctrReco);
					DetMap.put("amount", recoAmount);
					DetMap.put("emp_code","");
					DetMap.put("anal_code","");
					DetMap.put("exch_rate",(double) Double.parseDouble( "" + HdrMap.get("exch_rate")));
					DetList.add(DetMap);
				//}
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

			if (errCode != null && errCode.trim().length() > 0 )
			{
				return errCode;
			}
			
			sql = " select tran_id ,line_no ,acct_code,cctr_code ,drcr_amt,exch_rate__inv from drcr_invdet where tran_id = ? and drcr_amt <> 0";              
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				findIndex = -1;
				findIndex = findExistingIndex(DetList,rs.getString("acct_code"), rs.getString("cctr_code"), rs.getDouble("exch_rate__inv"));
				System.out.println(" 16/06/14 findExistingIndex [" + findIndex + "] Map [" + DetList.toString() + "]");
				if (findIndex > -1) 
				{
					DetMap = (HashMap) DetList.get(findIndex);
					amountMap = (double) Double.parseDouble( "" + DetMap.get("amount"));
					System.out.println(" 16/06/14 findExistingIndex [" + findIndex + "] amountMap [" + amountMap + "] amount [" + rs.getDouble("drcr_amt") + "]");
					DetMap.put("amount", amountMap + rs.getDouble("drcr_amt"));
					DetList.set(findIndex, DetMap);
				}
				else
				{
					DetMap = new HashMap();
					DetMap.put("tran_id", rs.getString("tran_id"));
					lineNo++;
					DetMap.put("line_no",(String) "" +lineNo);
					DetMap.put("amount", rs.getDouble("drcr_amt"));
					DetMap.put("emp_code", "");
					DetMap.put("anal_code", "");
					DetMap.put("acct_code", rs.getString("acct_code"));
					
					cctrCode = rs.getString("cctr_code");
					
					if (cctrCode == null || "null".equals(cctrCode))
					{
						cctrCode = "    ";
					}

					
					DetMap.put("cctr_code", cctrCode );
					exchRateInv = rs.getDouble("exch_rate__inv");
					if (exchRateInv == 0)
					{
						exchRateInv = (double) Double.parseDouble( "" + HdrMap.get("exch_rate"));
					}
					DetMap.put("exch_rate",exchRateInv);
					DetList.add(DetMap);
				}
				System.out.println(DetMap.toString());
			}    
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			diffExchAmt = (double) Double.parseDouble( "" +HdrMap.get("diff_amt_exch"));
			if (diffExchAmt != 0 )
			{
				acctExch = (String) HdrMap.get("acct_code__cf"); 
				cctrExch = (String) HdrMap.get("cctr_code__cf");
				if (cctrExch == null || "null".equals(cctrExch))
				{
					cctrExch = "    ";
				}

				DetMap = new HashMap();
				DetMap.put("tran_id", rs.getString("tran_id"));
				lineNo++;
				DetMap.put("line_no",(String) "" +lineNo);
				DetMap.put("amount", diffExchAmt);
				DetMap.put("emp_code", "");
				DetMap.put("anal_code", "");
				DetMap.put("acct_code", acctExch);
				DetMap.put("cctr_code", cctrExch);
				DetMap.put("exch_rate", 1);
				DetList.add(DetMap);
				
			}
			
			roundAdj = (double) Double.parseDouble( "" +HdrMap.get("round_adj"));
			if ( roundAdj != 0 )
			{
				
				cctrRnd = finCommon.getFinparams("999999", "DRCR_ACCT_RND", conn);
				if (cctrRnd != null && cctrRnd.trim().length() > 0)
				{
					acctRnd = cctrRnd.substring(0,cctrRnd.indexOf(";"));
					cctrRnd = cctrRnd.substring(cctrRnd.indexOf(";")+1);
				}
				if (cctrRnd == null || "null".equals(cctrRnd))
				{
					cctrRnd = "    ";
				}
		
				DetMap = new HashMap();
				DetMap.put("tran_id", rs.getString("tran_id"));
				lineNo++;
				DetMap.put("line_no",(String) "" +lineNo);
				DetMap.put("amount", roundAdj);
				DetMap.put("emp_code", "");
				DetMap.put("anal_code", "");
				DetMap.put("acct_code", acctRnd);
				DetMap.put("cctr_code", cctrRnd);
				DetMap.put("exch_rate", 1);
				DetList.add(DetMap);
			}
			// insert the drcr_acct before posting
			//sql = "Insert into drcr_invacct (TRAN_ID,LINE_NO,ACCT_CODE,CCTR_CODE,EXCH_RATE__INV,AMOUNT,EMP_CODE,ANAL_CODE) values (?,?,?,?,?,?,?,?)";
			sql = "Insert into drcr_invacct (TRAN_ID,LINE_NO,ACCT_CODE,CCTR_CODE,EXCH_RATE__INV,AMOUNT) values (?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			int maxline = 0;
			System.out.println("08/01/15 DetList ["+DetList.toString() + "]");
			for(int i = 0 ; i < DetList.size() ; i++)
			{
			
				double tempAmt = (double) Double.parseDouble( "" +DetMap.get("amount"));
				if (tempAmt != 0)
				{
					maxline++;
					DetMap = (HashMap) DetList.get(i);
					System.out.println(" Insert in drcr_invacct ctr [" + i + "] Map [" + HdrMap.toString() + "]");
					pstmt.setString(1,(String) DetMap.get("tran_id"));
					//pstmt.setString(2,String.valueOf(maxline));
					pstmt.setInt(2,maxline);
					pstmt.setString(3,(String) DetMap.get("acct_code"));
					cctrCode = (String) DetMap.get("cctr_code");
					if (cctrCode == null || "null".equals(cctrCode))
					{
						cctrCode = "    ";
					}
					pstmt.setString(4,cctrCode);
					pstmt.setDouble(5,(double) Double.parseDouble( "" +DetMap.get("exch_rate")));
					pstmt.setDouble(6,(double) Double.parseDouble( "" +DetMap.get("amount")));
					//pstmt.setString(7,(String)	DetMap.get("emp_code"));
					//pstmt.setString(8,(String)	DetMap.get("anal_code"));
					pstmt.addBatch();
					pstmt.clearParameters();
				}
			}
			if (maxline > 0 ) //DetList.size() > 0)
			{
				pstmt.executeBatch();
			}
			pstmt.close();
			pstmt = null;

			errCode = gbfPostDrCr(tranId, HdrMap,DetList,xtraParams,conn);
			/*if (errCode == null && errCode.trim().length() == 0 )
			{
				sql = "update drcr_inv set confirmed = 'Y', conf_date = ? ,emp_code__aprv = ? where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
				pstmt.setString(2, loginEmpCode);
				pstmt.setString(3, tranId);
				int updateCoount = pstmt.executeUpdate();
				System.out.println("no of row update = "+updateCoount);
				pstmt.close();
				pstmt = null;
				if(updateCoount > 0)
				{
					errString= itmDBAccessEJB.getErrorString("","VTCICONF3 ","","",conn);
					return errString;
				}
			}*/
			
		}
		catch(SQLException sqx)
		{
			//System.out.println(e.getMessage());
			//System.out.println("Exception : "+e);
			//e.printStackTrace();
			SQLException ex ;
			
			while (sqx != null)
			{
				if((CommonConstants.DB_NAME).equalsIgnoreCase("oracle") && (sqx).toString().indexOf("ORA-00054") > -1)
				{
					errCode = "VTLCKERR";
					  
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate]  Exception[Recod is locked try after some time]  ["+sqx.toString() + "]");
				}
				else
				{
					errCode = "ERROR";
					System.out.println("The SQLException occurs in UpdatStock [Stockupdate]  Exception ["+sqx.toString() + "]");
				}
				//Change by Rohan on 18-02-13 for throwing exception
				ex = sqx;
				
				sqx.printStackTrace();
				sqx =  sqx.getNextException();
				//Change by Rohan on 18-02-13 for throwing exception.start
				if(sqx == null)
				{
					sqx = ex;
					break;
				}
				//Change by Rohan on 18-02-13 for throwing exception.end
			}
			throw new ITMException(sqx);
		}
		catch(Exception se12){
		    System.out.println("Exception in UpdatStock [Stockupdate]"+se12);
            se12.printStackTrace();
            throw new ITMException(se12);
			
		}
		return errCode;
	}
	// 26/09/13 manoharan to find existing detail
	private int findExistingIndex(ArrayList detList, String acctCode, String cctrCode, double exchRate)throws RemoteException,ITMException
	{
		int findIndex  = -1;
		HashMap detMap = null;
		String acctCodeMap = null, cctrCodeMap = null;
		double exchRateMap = 0;
		try
		{
			for(int ctr=0 ; ctr < detList.size() ; ctr++)
			{
				detMap = (HashMap) detList.get(ctr);
				acctCodeMap = (String) detMap.get("acct_code");
				cctrCodeMap = (String) detMap.get("cctr_code");
				exchRateMap = (double) Double.parseDouble( "" + detMap.get("exch_rate"));
				if (acctCodeMap.trim().equalsIgnoreCase(acctCode.trim()) && cctrCodeMap.trim().equalsIgnoreCase(cctrCode.trim()) && exchRateMap == exchRate)
				{
					findIndex = ctr;
					break;
				}
				
			}
		}
		catch(Exception e)
		{
			//System.out.println(e.getMessage());
			//System.out.println("Exception : "+e);
			//e.printStackTrace();
			throw new ITMException(e);
		}
		return findIndex;
	}

	private String gbfPostDrCr(String tranId, HashMap HdrMap,ArrayList DetList, String xtraParams, Connection conn) throws RemoteException,ITMException
	{
		String errCode = "";
		FinCommon finCommon = null;
		try
		{
			finCommon = new  FinCommon();
			errCode = gbfPostDrcrInvhdr(tranId, HdrMap, xtraParams,conn);
			System.out.println("16/06/14 after gbfPostDrcrInvhdr errCode [" + errCode + "]");
			if(errCode == null || errCode.trim().length() ==0)
			{
				errCode = gbfPostDrcrInvdet(tranId, HdrMap,DetList, xtraParams,conn);
				System.out.println("16/06/14 after gbfPostDrcrInvdet errCode [" + errCode + "]");
				if(errCode != null && errCode.trim().length() >0)
				{
					return errCode;
				}
			}
			System.out.println("16/06/14 after gbfPostDrcrInvdet errCode [" + errCode + "]");
			errCode = finCommon.checkGlTranDrCr("CRNINV",tranId,conn);

		}
		catch (Exception e)
		{
			throw new ITMException(e);
		}
		return errCode ;

	}
	private String gbfPostDrcrInvhdr(String tranId,HashMap HdrMap,String xtraParams,Connection conn)throws RemoteException,ITMException
	{
		System.out.println("gbfPostDrcrinv called..............");
		String errString = "";
		String currCode = "";
		String siteCode = "";
		String siteCodeFor = "";
		String finEntity = "";
		String acctCode = "";
		String cctrCode = ""; 
		String recoAcctCode = "";
		String recoCctrCode = "";
		String taxAcctCode = "";
		String taxCctrCode = "";
		String acctCode1 = "";
		String cctrCode1 = "";
		String acctCodeAr = "";
		String cctrCodeAr = "";
		String projectCode = "";
		String analCode = "";
		String bankCode = "";
		String remarks = "";
		String sundryType = "";
		String contactCode = "";
		String sundryCode = "";
		String sundryTypeFor = "";
		String sundryCodeFor = "";
		String tranType = "";
		String empCode = "";
		String refNO = "";
		String payMode = "";
		String rcpMode = "";
		String batchNo = "";
		String confirm = "";
		String loginEmpCode = "";
		String winName = "";
		String editOption = "";
		String sql = "";
		String hcurrCode = "";
		String errorType = "";
		String payslip = "";
		String partyDocRef = "";
		String refSer = "";
		String refNo = "";
		String salesPers = "";
		String status = "";
		String lineNoPef = "";

		String tranSer = "",drcrFlag = "";
		int count = 0;
		int lineNo = 0;
		double exchRate = 0.0;
		double hexchRate = 0.0;
		double netAmount = 0.0;
		double amount = 0.0;
		double taxAmount = 0.0;
		double recoAmount = 0.0;
		double debit  = 0.0;
		double credit = 0.0;
		double totAmt = 0.0;
		double advAmt = 0.0;
		double adjAmt = 0.0;
		double advAdj = 0.0;
		double recoverAmt = 0.0;
		java.sql.Timestamp effDate = null;
		java.sql.Timestamp tranDate = null;
		java.sql.Timestamp refDate = null;
		java.sql.Timestamp billDate = null;
		java.sql.Timestamp dueDate = null;


		HashMap DetMap = null;
		HashMap miscPayMap = null;
		HashMap glTraceMap = null;
		HashMap sundryBalMap = null;
		HashMap bankTranLogMap = null;
		HashMap detListMap = null;
		HashMap recofiltMap = null;
		HashMap recodataMap = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		FinCommon finCommon = null;
		DistCommon distCommon = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		ValidatorEJB validatorEJB = new ValidatorEJB();
		GenericUtility genericUtility = new GenericUtility();
		try
		{
			finCommon = new  FinCommon();
			distCommon = new DistCommon();
			itmDBAccessEJB = new ITMDBAccessEJB();
			loginEmpCode = validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginCode");

			//tranId = (String)HdrMap.get("tran_id");
			tranSer = (String)HdrMap.get("tran_ser");

			tranDate = (Timestamp)HdrMap.get("tran_date");
			effDate = (Timestamp)HdrMap.get("eff_date");
			finEntity = (String)HdrMap.get("fin_entity");
			siteCode = (String)HdrMap.get("site_code");
			siteCodeFor = (String)HdrMap.get("site_code__for");
			projectCode = (String)HdrMap.get("proj_code");
			hcurrCode = (String)HdrMap.get("curr_code");
			hexchRate = (double) Double.parseDouble( "" + HdrMap.get("exch_rate"));
			remarks = (String)HdrMap.get("remarks");
			confirm = (String)HdrMap.get("confirm");
			dueDate = (Timestamp)HdrMap.get("due_date");
			
			sundryType = (String)HdrMap.get("sundry_type");
			sundryCode = (String)HdrMap.get("sundry_code");
			amount = (double) Double.parseDouble( "" + HdrMap.get("amount")); 
			remarks = (String)HdrMap.get("remarks");
			
			glTraceMap = new HashMap();
			glTraceMap.put("tran_date",new Timestamp(tranDate.getTime()));
			glTraceMap.put("eff_date",new Timestamp(effDate.getTime()));
			glTraceMap.put("fin_entity", finEntity);
			glTraceMap.put("site_code", siteCode);
			glTraceMap.put("sundry_type",sundryType);
			glTraceMap.put("sundry_code",sundryCode);
			glTraceMap.put("acct_code", (String)HdrMap.get("acct_code"));

			glTraceMap.put("cctr_code", (String)HdrMap.get("cctr_code")==null?"":(String)HdrMap.get("cctr_code"));
			glTraceMap.put("emp_code", "");
			glTraceMap.put("anal_code", (String)HdrMap.get("anal_code"));
			glTraceMap.put("curr_code", (String)HdrMap.get("curr_code")==null?hcurrCode:(String)HdrMap.get("curr_code"));

			glTraceMap.put("exch_rate", (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));
			drcrFlag = (String) HdrMap.get("drcr_flag");
			System.out.println("gltrace  drcrFlag ["+ drcrFlag + "]amount [" + amount+ "]");
			if("D".equals(drcrFlag))
			{
				glTraceMap.put("dr_amt",amount);
				glTraceMap.put("cr_amt",0.0);
			}
			else
			{
				glTraceMap.put("dr_amt", 0.0);
				glTraceMap.put("cr_amt",amount);
			}
			glTraceMap.put("ref_type","F");
			glTraceMap.put("ref_ser", tranSer);
			glTraceMap.put("ref_id", tranId);
			glTraceMap.put("remarks",remarks);

			System.out.println("848 glTraceUpdate Called glTraceMap[" + glTraceMap.toString() + "]");
			errString = finCommon.glTraceUpdate(glTraceMap,conn);
			if(errString != null && errString.trim().length() > 0)
			{
				System.out.println("ERROR IN GLTRACE ...........");

				return errString;
			}

			// Populate sundry balance structure
			sundryBalMap = new HashMap();
			sundryBalMap.put("tran_date",new Timestamp(tranDate.getTime()));
			sundryBalMap.put("eff_date",new Timestamp(effDate.getTime()));
			sundryBalMap.put("fin_entity", finEntity);
			sundryBalMap.put("site_code", siteCode);
			sundryBalMap.put("sundry_type", sundryType);
			sundryBalMap.put("sundry_code", sundryCode);
			sundryBalMap.put("acct_code", (String)HdrMap.get("acct_code"));
			sundryBalMap.put("cctr_code", (String)HdrMap.get("cctr_code")==null?"":(String)HdrMap.get("cctr_code"));
			sundryBalMap.put("curr_code", (String)HdrMap.get("curr_code"));
			sundryBalMap.put("exch_rate", (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));

			if("D".equals(drcrFlag))
			{
				sundryBalMap.put("dr_amt", amount);
				sundryBalMap.put("cr_amt", 0.0);
			}

			else
			{
				sundryBalMap.put("dr_amt", 0.0);
				sundryBalMap.put("cr_amt", amount);
			}


			sundryBalMap.put("adv_amt", 0.0);

			System.out.println("2366 sundryBaleUpdate Called..............");
			errString = finCommon.gbf_sundrybal_upd(sundryBalMap, conn);
			if(errString != null && errString.trim().length() > 0)
			{
				System.out.println("error is sundryBaleUpdate ..........."+errString);

				return errString;
			}


			///Populate the misc_payables structure				/////
			// insert into misc_payable	
			miscPayMap = new HashMap();
			miscPayMap.put("tran_date",new Timestamp(tranDate.getTime()));
			miscPayMap.put("tran_ser",(String)HdrMap.get("tran_ser"));
			miscPayMap.put("ref_no",(String)HdrMap.get("ref_no"));
			miscPayMap.put("ref_date", new Timestamp(tranDate.getTime()));			
			//miscPayMap.put("bill_no", (String)HdrMap.get("bill_no"));
			//miscPayMap.put("bill_date", new Timestamp(billDate.getTime()));
			miscPayMap.put("sundry_type",(String)HdrMap.get("sundry_type")==null?"O":(String)HdrMap.get("sundry_type"));
			miscPayMap.put("sundry_code", (String)HdrMap.get("sundry_code")==null?"O":(String)HdrMap.get("sundry_code"));
			miscPayMap.put("acct_code", (String)HdrMap.get("acct_code"));
			miscPayMap.put("cctr_code", (String)HdrMap.get("cctr_code")==null?"":(String)HdrMap.get("cctr_code"));
			miscPayMap.put("curr_code", (String)HdrMap.get("curr_code"));
			miscPayMap.put("exch_rate", (double) Double.parseDouble( "" + HdrMap.get("exch_rate")));


			miscPayMap.put("due_date", new Timestamp(dueDate.getTime()));
			miscPayMap.put("site_code",siteCode);
			miscPayMap.put("fin_entity",finEntity);
			if("C".equals(drcrFlag))
			{
				miscPayMap.put("tot_amt", amount);
			}
			else
			{
				miscPayMap.put("tot_amt", -1 * amount);
			}
			sql = "select bank_code   from site where site_code = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteCode);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				bankCode = rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql = "select pay_mode from sales_pers where sales_pers = ?" ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, salesPers);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				payMode = rs.getString(1)==null?"Q":rs.getString(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			miscPayMap.put("bank_code", bankCode);
			miscPayMap.put("auto_pay", "Y");
			miscPayMap.put("pay_mode", payMode);
			miscPayMap.put("paid", "N");
			miscPayMap.put("adv_amt", 0.0);
			miscPayMap.put("hold_amt", 0.0);
			miscPayMap.put("supp_bill_amt","0.0");
			miscPayMap.put("tds_amt","0.0");


			System.out.println("884 sundryBaleUpdate Called..............");
			errString = finCommon.gbfMiscPayUpd(miscPayMap, conn);
			if(errString != null && errString.trim().length() > 0)
			{
				return errString;
			}
			if(confirm == null || "null".equals(confirm))
			{
				confirm = "N";
			}
			/*if(!"Y".equals(confirm))
			{
				sql = "update drcr_inv set confirmed = 'Y', conf_date = ? ,emp_code__aprv = ? where tran_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
				pstmt.setString(2, loginEmpCode);
				pstmt.setString(3, tranId);
				int updateCoount = pstmt.executeUpdate();
				System.out.println("no of row update = "+updateCoount);
				pstmt.close();
				pstmt = null;
				if(updateCoount > 0)
				{
					errString= itmDBAccessEJB.getErrorString("","VTCICONF3 ","","",conn);
					return errString;
				}
			}*/



		}
		catch(Exception e)
		{
			//System.out.println(e.getMessage());
			//System.out.println("Exception : "+e);
			//e.printStackTrace();
			errString = "ERROR";
			throw new ITMException(e);
		}
		return errString;
	}

	private String gbfPostDrcrInvdet(String tranId,HashMap HdrMap,ArrayList DetList,String xtraParams,Connection conn)throws RemoteException,ITMException
	{
		System.out.println("gbfPostDrcrInvdet called........");
		String errString = "";
		String linkType = "";
		String currCode = "";
		String siteCode = "";
		String siteCodeFor = "";
		String finEntity = "";
		HashMap DetMap = null;
		String acctCode = "";
		String cctrCode = "";
		String acctCodePay = "";
		String cctrCodePay = "";
		String acctCodeAr = "";
		String cctrCodeAr = "";
		String tranSer = "";
		String projectCode = "";
		String analCode = "";
		String bankCode = "";
		String remarks = "";
		String sundryType = "";
		String sundryCode = "";
		String sundryTypeFor = "";
		String sundryCodeFor = "";
		String contactCode = "";
		String empCode = "";
		String refNO = "";
		String rcpMode = "";
		String batchNo = "";
		String tranType = "";
		String confirm = "";
		String loginEmpCode = "";
		String winName = "";
		String editOption = "";
		String confirmed = "";
		String sql = "";
		String errorType = "";
		String bdFluctuationCf = "";
		String acctCodeBal = "";
		String cctrCodeBal = "";
		String basecurrCode = "";
		String projCode = "";
		String custCode = "";
		String salesPers = "";
		String refSer = "";
		String refNo = "";
		String acctCodeCf = "";
		String payslip = "";
		//String tranId = "";
		boolean isInBaseCurr =false;
		int count = 0;
		int cnt1 = 0 ;
		int cnt = 0;
		double exchRate = 0.0;
		double netAmount = 0.0;
		double amount = 0.0;
		double advAmt = 0.0;
		double netAmt = 0.0;
		double chqAmt = 0.0;
		double debit  = 0.0;
		double credit = 0.0;
		double totAmt = 0.0;
		double ediOption = 0.0;
		double netAmountBc = 0.0;
		Date effDate = null;
		Date tranDate = null;
		Date refDate = null;
		HashMap detListMap = null;
		HashMap glTraceMap = null;
		HashMap sundryBalMap = null;
		HashMap bankTranLogMap = null;
		HashMap receivablesMap = null;
		HashMap epcadjMap = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		FinCommon finCommon = null;
		ValidatorEJB validatorEJB = new ValidatorEJB();
		GenericUtility genericUtility = new GenericUtility();
		ITMDBAccessEJB itmDBAccessEJB = null;
		try
		{
			finCommon =new  FinCommon();

			loginEmpCode = validatorEJB.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			
			java.util.Date today=	new java.sql.Date(new java.util.Date().getTime());
			
			itmDBAccessEJB = new ITMDBAccessEJB();

			linkType = (String)HdrMap.get("link_type");
			//tranId = (String)HdrMap.get("tran_id");

			tranDate = (Date)HdrMap.get("tran_date");
			refDate = (Date)HdrMap.get("ref_date");
			effDate = (Date)HdrMap.get("eff_date");
			remarks = (String)HdrMap.get("remarks");

			finEntity = (String)HdrMap.get("fin_entity");
			siteCode = (String)HdrMap.get("site_code");
			tranSer = (String)HdrMap.get("tran_ser");
			bankCode = (String)HdrMap.get("bank_code");

			acctCode = (String)HdrMap.get("acct_code");
			cctrCode = (String)HdrMap.get("cctr_code");

			currCode = (String)HdrMap.get("curr_code");
			exchRate = (double) Double.parseDouble( "" + HdrMap.get("exch_rate"));

			System.out.println("DetList SIZE "+DetList.size());
			System.out.println("DetList SIZE ["+DetList.toString() + "]");

			for (int ctr =0; ctr < DetList.size(); ctr++)
			{

				System.out.println("no of times "+ctr);
				DetMap = new HashMap();
				DetMap = (HashMap) DetList.get(ctr);


				sundryCode = (String)DetMap.get("sundry_code");
				sundryType = (String)DetMap.get("sundry_type");

				acctCode = (String)DetMap.get("acct_code");
				cctrCode = (String)DetMap.get("cctr_code");
				//empCode = (String)DetMap.get("emp_code");
				//analCode = (String)DetMap.get("anal_code");

				exchRate = (double) Double.parseDouble( "" + DetMap.get("exch_rate"));
				analCode = (String)DetMap.get("anal_code");
				analCode = (String)DetMap.get("anal_code");
				amount = (double) Double.parseDouble( "" + DetMap.get("amount"));


				System.out.println("DetMap SIZE "+DetMap);

				glTraceMap = new HashMap();
				glTraceMap.put("tran_date", new Timestamp(tranDate.getTime()));
				glTraceMap.put("eff_date",new Timestamp(effDate.getTime()));
				glTraceMap.put("fin_entity", finEntity);
				glTraceMap.put("site_code", siteCode);
				glTraceMap.put("sundry_type", sundryType);
				glTraceMap.put("sundry_code", sundryCode);
				glTraceMap.put("acct_code", acctCode);
				glTraceMap.put("cctr_code", cctrCode);
				//glTraceMap.put("emp_code", empCode);
				//glTraceMap.put("anal_code", analCode);
				glTraceMap.put("curr_code", currCode);
				glTraceMap.put("exch_rate", exchRate);
				if("D".equals(HdrMap.get("drcr_flag")))
				{
					if(amount > 0 )
					{
						glTraceMap.put("dr_amt",0.0);
						glTraceMap.put("cr_amt",amount);
					}
					else
					{
						glTraceMap.put("dr_amt", -1 * amount);
						glTraceMap.put("cr_amt",0.0);
					}
				}
				else
				{
					if(amount > 0 )
					{
						glTraceMap.put("dr_amt", amount);
						glTraceMap.put("cr_amt", 0.0 );
					}
					else
					{
						glTraceMap.put("dr_amt", 0.0);
						glTraceMap.put("cr_amt",  -1 * amount);
					}
				}

				glTraceMap.put("ref_type","F");
				glTraceMap.put("ref_ser", tranSer);
				glTraceMap.put("ref_id", tranId);
				glTraceMap.put("remarks", remarks);

				System.out.println("1766 glTraceUpdate Called..drcrflag [" + HdrMap.get("drcr_flag") + "] glTraceMap [" + glTraceMap + "]");
				errString = finCommon.glTraceUpdate(glTraceMap,conn); 
				if(errString != null && errString.trim().length() > 0)
				{
					return errString;
				}
			}
		}
		catch(Exception e)
		{
			//e.printStackTrace();
			errString = "ERROR";
			throw new ITMException(e);
		}
		return errString;

	}
	
	public double getReqDecimal(double actVal, int prec)
	{
		String fmtStr = "############0";
		String strValue = null;
		double retVal = 0;
		if (prec > 0)
		{
			fmtStr = fmtStr + "." + "000000000".substring(0, prec);
		}
		DecimalFormat decFormat = new DecimalFormat(fmtStr);
		retVal = Double.parseDouble(decFormat.format(actVal));
		if (Math.abs(retVal) == 0)
		{
			retVal = 0;
		}
		return retVal;
	}


}
