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

import ibase.webitm.ejb.dis.*;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import java.io.*;
import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.text.SimpleDateFormat; 
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.ejb.*;
import ibase.system.config.*;
import java.util.*;
import java.lang.*; 
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class BankReconPrc extends ProcessEJB implements BankReconPrcLocal,BankReconPrcRemote // SessionBean
{
	Connection conn = null;
	ITMDBAccessEJB itmDBAccessEJB = null;
	CommonConstants commonConstants;
	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = "";
	String logInSite = "";
	int tran_sl = 0;
	String win_name= "";
	String batch_id = "";
	String xtraParam = "";
 	/*public void ejbCreate() throws RemoteException, CreateException
	{
		try
		{
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
 			conn = conndriver.getConnectDB("DriverITM");
 			conn.setAutoCommit(false);
 			
			System.out.println("BankReconPrcEJB ejbCreate called.........");
		}
		catch (Exception e)
		{
			System.out.println("Exception :BankReconPrcEJB :ejbCreate :==>"+e);
			throw new CreateException();
		}
	}
	public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}*/
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	//process()
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("xmlString:::::::::::"+xmlString);
		System.out.println("xmlString:::::::::::"+xmlString2);
		
		xtraParam = xtraParams;
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);  
		}
		catch (Exception e)
		{
			System.out.println("Exception :BankReconPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String childNodeName = "";
		String errCode = "";
		String errString = "";
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		java.io.File file = null;
		Node parentNode = null;
		Node childNode = null;	
		String siteCode = "";
		String bankCode = "";
		xtraParam = xtraParams;
		userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userId");
		logInSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		try
		{
			parentNodeList = headerDom.getElementsByTagName("Detail1");
			parentNodeListLength = parentNodeList.getLength(); 
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				parentNode = parentNodeList.item(selectedRow);
				childNodeList = parentNode.getChildNodes();
 				childNodeListLength = childNodeList.getLength();
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					System.out.println("Inside for loop...........");
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equals("site_code"))
					{
						siteCode = (childNode.getFirstChild().getNodeValue()).trim();
						System.out.println("siteCode::::::::::: "+ siteCode);	
					}		
					if (childNodeName.equals("bank_code"))
					{
						bankCode = (childNode.getFirstChild().getNodeValue()).trim();
						System.out.println("bankCode:::::::: "+ bankCode);
					}
				}													
			}
			reconcile(siteCode,bankCode);
	}
	catch(Exception excep)
	{
		System.out.println("Exception3[BankReconPrcEJB][reconcile(1)][Excuting Query Failed(count tot transl from batchload)] ::"+excep.getMessage());
		excep.printStackTrace();
	}
	if (errString == null || errString.trim().length() == 0)
	{
		errString="VTCOMPL";
		errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
		return errString;
	}
	else
	{
		errString="VTPRCERR";
		errString = itmDBAccessEJB.getErrorString("",errString,"","",conn);
		return errString;
	}

}	

//FUNCTION GFCREATEBATCH
	public void  reconcile(String siteCode,String bankCode)throws RemoteException,ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs1 = null;
		long start = System.currentTimeMillis();
		System.out.println("Reconcile in Process...................");
		ArrayList bankStList = new ArrayList();
		ArrayList bankTranList = new ArrayList();
		String refNo = "";
		double amt = 0.0;
		double adjAmt = 0.0;
		double bal = 0.0;
		Vector vrefNo = new Vector();
		String tranIdBT ="";
		String tranIdBS = "";
		double amtBT = 0.0;
		double adjAmtBT =0.0;
		String statusBT ="";
		double balBT = 0.0;
		double amtBS = 0.0;
		double adjAmtBS =0.0;
		String statusBS ="";
		double balBS = 0.0;
		double  amtAdjust = 0.0;
		String sql = "";
		
 		
		//collecting the data from bankstatement
		//comment by rajendra on 12/24/2007
		//sql = " SELECT REF_NO,SUM(AMOUNT),SUM(AMT_ADJ) from bank_statement where site_code ='"+siteCode.trim()+"' and bank_code ='"+bankCode.trim()+"'  and STATUS ='P'  OR STATUS IS NULL GROUP BY ref_no  ";
		//added by rajendra on 12/24/2007 for adding OR STATUS ='E'
		sql = " SELECT REF_NO,SUM(AMOUNT),SUM(AMT_ADJ) from bank_statement where site_code ='"+siteCode.trim()+"' and bank_code ='"+bankCode.trim()+"'  and ( STATUS ='P' OR STATUS ='E'  OR STATUS IS NULL ) GROUP BY ref_no  ";
		System.out.println("query executed(bank_statement):::"+sql);
		try
		{
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
	 		conn = conndriver.getConnectDB("DriverITM");
	 		conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1,siteCode.trim()); 
			//pstmt.setString(2,siteCode.trim()); 
			rs = pstmt.executeQuery(); 
			while(rs.next())
			{
				refNo = rs.getString(1);
				amt = rs.getDouble(2);
				adjAmt =rs.getDouble(3);
				bal = amt - adjAmt;
				BankStBean bsbo = new BankStBean();
				bsbo.setRefNo(refNo);
				bsbo.setAmt(amt);
				bsbo.setAdjAmt(adjAmt);
				bsbo.setBal(bal);
				bankStList.add(bsbo);
				System.out.println("       BANK STATEMENTS     ");
				System.out.println("____________________________");
				System.out.println("RefNo:::"+refNo);
				System.out.println("amt:::"+amt);
				System.out.println("adjAmt:::"+adjAmt);
			}
			rs.close();
			pstmt.close();
		}
		catch(SQLException se)
		{
			System.out.println("SQLException :[BankReconPrcEJB][reconcile(1)][Excuting Query Failed(count tot transl from batchload)]" + sql + se.getMessage());
		}
		catch(Exception ex)
		{
			System.out.println("Exception[BankReconPrcEJB][reconcile(1)][Excuting Query Failed(count tot transl from batchload)] ::"+sql +ex.getMessage());
			ex.printStackTrace();
		}
		//collecting data from banktranlog
		//added by rajendra on 12/24/2007 for adding OR STATUS ='E'

		//Commented and Changes below - Gulzar - 16/01/09(FI89PHE023) - remove a white space after siteCode.trim() and added a white space before and bank_code
		//sql = " SELECT REF_NO,SUM(AMOUNT),SUM(AMT_ADJ) from banktran_log where site_code ='"+siteCode.trim()+" 'and bank_code ='"+bankCode.trim()+"'  and ( STATUS ='P' OR STATUS ='E' OR STATUS IS NULL ) GROUP BY ref_no ";
		sql = " SELECT REF_NO,SUM(AMOUNT),SUM(AMT_ADJ) from banktran_log where site_code ='"+siteCode.trim()+"'  and bank_code ='"+bankCode.trim()+"'  and ( STATUS ='P' OR STATUS ='E' OR STATUS IS NULL ) GROUP BY ref_no ";
		//End Changes - Gulzar - 16/01/09 (FI8PHE023)

		System.out.println("query executed(banktran_log):::"+sql);
		try
		{
			pstmt = conn.prepareStatement(sql);
			//pstmt.setString(1,siteCode.trim()); 
			//pstmt.setString(2,siteCode.trim()); 
			rs = pstmt.executeQuery(); 
			while(rs.next())
			{
				refNo = rs.getString(1);
				amt = rs.getDouble(2);
				adjAmt =rs.getDouble(3);
				bal = amt - adjAmt;
				BankTranBean btbo = new BankTranBean();
				btbo.setRefNo(refNo);
				btbo.setAmt(amt);
				btbo.setAdjAmt(adjAmt);
				btbo.setBal(bal);
				bankTranList.add(btbo);
				System.out.println("         BANK TRAN LOG      ");
				System.out.println("____________________________");
				System.out.println("RefNo:::"+refNo);
				System.out.println("amt:::"+amt);
				System.out.println("adjAmt:::"+adjAmt);
				System.out.println("____________________________");
			}
			rs.close();
			pstmt.close();
		}
		catch(SQLException se1)
		{
			System.out.println("SQLException :[BankReconPrcEJB][reconcile(2)][Excuting Query Failed(count tot transl from batchload)]" + sql + se1.getMessage());
		}
		catch(Exception ex1)
		{
			System.out.println("Exception3[BankReconPrcEJB][reconcile(2)][Excuting Query Failed(count tot transl from batchload)] ::"+sql +ex1.getMessage());
			ex1.printStackTrace();
		}
		//DETERMINE THE REFERENCE NO FOR WHICH BALANCE IS SAME
		System.out.println("bankStList:::"+bankStList.size());
		System.out.println("bankTranList:::"+bankTranList.size());
		for(int bsCounter = 0;bsCounter< bankStList.size();bsCounter++)
		{
			BankStBean bsb  = (BankStBean)bankStList.get(bsCounter);
			System.out.println("Retriving the reference no.......");
			double balS  = bsb.getBal();
			String refNoBS =""; 
			refNoBS= bsb.getRefNo();
			if(refNoBS ==null)
			{
				refNoBS ="";
			}
			for(int btCounter = 0;btCounter< bankTranList.size();btCounter++)
			{
				BankTranBean btb = (BankTranBean)bankTranList.get(btCounter);
				double balT = btb.getBal();
				String refNoBT =""; 
				refNoBT	= btb.getRefNo();
				if(refNoBT == null)
				{
					refNoBT = "";
				}
				System.out.println("refNoBT....................."+refNoBT);
				System.out.println("refNoBS....................."+refNoBS);
				System.out.println("balT....................."+balT);
				System.out.println("balS....................."+balS);
				if((balT==balS)  && (refNoBT.trim().equals(refNoBS.trim())))
				{
					vrefNo.add(bsb.getRefNo()); 
					System.out.println("Reconcile ref No adding to vector:::"+bsb.getRefNo());
				}
			}
	
		}
		for(int refCounter = 0;refCounter <vrefNo.size();refCounter++)
		{
			String refnoTemp = (String)vrefNo.get(refCounter);
//**************** status <> "R"			
			String sqlBS = " SELECT TRAN_ID,AMOUNT,AMT_ADJ,STATUS from bank_statement where site_code ='"+siteCode.trim()+"' and bank_code ='"+bankCode.trim()+"'  and REF_NO = '"+refnoTemp+"' AND status <> 'R' order by tran_id ";
			try
			{
				pstmt = conn.prepareStatement(sqlBS);
				//pstmt.setString(1,siteCode.trim()); 
				//pstmt.setString(2,siteCode.trim());  
				rs = pstmt.executeQuery(); 
				while(rs.next())
				{
					balBS = 0.0;
					tranIdBS = rs.getString(1);
					amtBS	= rs.getDouble(2);
					adjAmtBS	 =rs.getDouble(3);
					statusBS = rs.getString(4);
					balBS = amtBS - adjAmtBS;
					System.out.println("Bbalance in bank_statement"+balBS);
					String sqlBT = " SELECT TRAN_ID,AMOUNT,AMT_ADJ,STATUS from banktran_log where site_code ='"+siteCode.trim()+"' and bank_code ='"+bankCode.trim()+"'  and REF_NO = '"+refnoTemp+"' and status <>'R' order by tran_id ";
					try
					{
						pstmt1 = conn.prepareStatement(sqlBT);
						//pstmt.setString(1,siteCode.trim()); 
						//pstmt.setString(2,siteCode.trim()); 
						rs1 = pstmt1.executeQuery(); 
						while(rs1.next())
						{
							balBT = 0.0;
							tranIdBT = rs1.getString(1);
							amtBT = rs1.getDouble(2);
							adjAmtBT =rs1.getDouble(3);
							statusBT = rs1.getString(4);
							balBT = amtBT - adjAmtBT;
							System.out.println("Balance in banktran_log"+balBT);
							if(balBS ==balBT)
							{
								amtAdjust = balBS;
								statusBS = "R"; 
								statusBT = "R";
								System.out.println("AmtAdjust To be adjust :::"+amtAdjust);
							}
							else if(balBS >balBT)
							{
								amtAdjust = balBT;
								statusBT = "R";	
								statusBS = "P"; 
								System.out.println("AmtAdjust To be adjust :::"+amtAdjust);
							}
							else if (balBS < balBT)
							{
								amtAdjust = balBS;
								statusBS = "R";
								statusBT = "P";	
								System.out.println("AmtAdjust To be adjust :::"+amtAdjust);
							}
								adjAmtBT = adjAmtBT + amtAdjust;
								adjAmtBS = adjAmtBS + amtAdjust;
								insertBankRecoTrace(tranIdBS,tranIdBT,amtAdjust);
								updateBankTranLog(tranIdBT,adjAmtBT,statusBT,refnoTemp);
								updateBankStatement(tranIdBS,adjAmtBS,statusBS,refnoTemp);
								balBS = balBS - amtAdjust;
								System.out.println("DIFFERENT :::"+balBS);
								if(balBS<= 0)
								break;
						}
						rs1.close();
						pstmt1.close();
					}
					catch(SQLException se3)
					{
						System.out.println("SQLException :[BankReconPrcEJB][reconcile(4)][Excuting Query Failed]" + sqlBT + se3.getMessage());
					}
					catch(Exception ex3)
					{
						System.out.println("Exception3[BankReconPrcEJB][reconcile(4)][Excuting Query Failed] ::"+sqlBT +ex3.getMessage());
						ex3.printStackTrace();
					}
				}
			rs.close();
			pstmt.close();
		}
		catch(SQLException se2)
		{
			System.out.println("SQLException :[BankReconPrcEJB][reconcile(5)][Excuting Query Failed]" + sqlBS + se2.getMessage());
		}
		catch(Exception ex2)
		{
			System.out.println("Exception[BankReconPrcEJB][reconcile(5)][Excuting Query Failed ::"+sqlBS +ex2.getMessage());
			ex2.printStackTrace();
		}
    }
}//END OF reconcile
	//updateBankStatement
	private void 	updateBankStatement(String tranId,double adjAmt,String status,String refno)
	{
		PreparedStatement upstmt = null;
		String 	update ="";
		System.out.println("update BankStatement.....");
		System.out.println("TranId:::"+tranId);
		System.out.println("Status:::"+status);
		System.out.println("Refno:::"+refno);
		System.out.println("AdjAmt:::"+adjAmt);
		
		try
		{ 
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
			conn = conndriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
		 	update = " UPDATE BANK_STATEMENT SET STATUS = '"+status+"', AMT_ADJ = "+adjAmt+" WHERE TRAN_ID = '"+tranId+"' AND REF_NO ='"+refno+"' ";		
			upstmt = conn.prepareStatement(update);
			//upstmt.setString(1,status);
			//upstmt.setDouble(2,adjAmt);	
			//upstmt.setString(3,tranId.trim());			
			//upstmt.setString(4,refno.trim());
			int u = upstmt.executeUpdate();
			System.out.println("No of record update in BankStatement:::"+u);
			upstmt.close();
			conn.commit();
		}
		catch(SQLException seUpdate)
		{
				System.out.println("SQLException(Update BankStatement) :[Excuting Query Failed ]" + update + seUpdate.getMessage());
		}
		catch(Exception exUpdate)
		{
			System.out.println("Exception3(Update BankStatement) ::"+update +exUpdate.getMessage());
			exUpdate.printStackTrace();
		}
	}
	
	//updateBankTranLog
	private void updateBankTranLog(String tranId,double adjAmt,String status,String refno)
	{
		PreparedStatement upstmt = null;
		String 	update ="";
		System.out.println("update BankTranLog.....");
		System.out.println("TranId:::"+tranId);
		System.out.println("Status:::"+status);
		System.out.println("Refno:::"+refno);
		System.out.println("AdjAmt:::"+adjAmt);
		
		try
		{ 
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
			conn = conndriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			update = " UPDATE BANKTRAN_LOG SET STATUS = '"+status+"', AMT_ADJ = "+adjAmt+" WHERE TRAN_ID = '"+tranId+"' AND REF_NO ='"+refno+"' ";		
			upstmt = conn.prepareStatement(update);
			//upstmt.setString(1,status);
			//upstmt.setDouble(2,adjAmt);				
			//upstmt.setString(3,tranId.trim());	
			//upstmt.setString(4,refno.trim());	
			int u = upstmt.executeUpdate();
			System.out.println("No of record update in banktran_log:::"+u);
			upstmt.close();
			conn.commit();
		}
		catch(SQLException seUpdate)
		{
				System.out.println("SQLException(Update BankTranLog) :[Excuting Query Failed ]" + update + seUpdate.getMessage());
		}
		catch(Exception exUpdate)
		{
			System.out.println("Exception3(Update BankTranLog) ::"+update +exUpdate.getMessage());
			exUpdate.printStackTrace();
		}
	}
	
	private void insertBankRecoTrace(String tranIdBS,String tranIdBT,double adjAmount)
	{
		PreparedStatement ipstmt = null;
		String 	insert = "";
		System.out.println("insert a record in trace.....");
		System.out.println("TranId(BankStatement):::"+tranIdBS);
		System.out.println("TranId(BankStatement):::"+tranIdBT);
		System.out.println("AdjAmt:::"+adjAmount);
		
		try
		{ 
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
			conn = conndriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
		 	insert = " INSERT INTO BANKRECO_TRACE (TRAN_ID__STAT, TRAN_ID__LOG, AMOUNT ) VALUES (?,?,?)";
			ipstmt = conn.prepareStatement(insert);
			ipstmt.setString(1,tranIdBS);
			ipstmt.setString(2,tranIdBT);				
			ipstmt.setDouble(3,adjAmount);	
			ipstmt.executeUpdate();
			ipstmt.close();
			conn.commit();
		}
		catch(SQLException seinsert)
		{
				System.out.println("SQLException(insert in to Trace) :[Excuting Query Failed ]" + insert + seinsert.getMessage());
		}
		catch(Exception exinsert)
		{
			System.out.println("Exception(insert in to Trace) ::"+insert +exinsert.getMessage());
			exinsert.printStackTrace();
		}
	}
}//END OF EJB