/********************************************************
	Title : InterCompReconcile EJB
	Date  : 18 - JAN - 2014
	Author: Sandeep B. Kumbhar

 ********************************************************/
package ibase.webitm.ejb.fin;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.io.File;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import javax.ejb.Stateless;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class InterCompReconcile extends ValidatorEJB implements InterCompReconcileProcessLocal, InterCompReconcileProcessRemote {

	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = null;
	String chgUser = null;
	String chgTerm = null;
	
	public InterCompReconcile() {
		System.out.println("^^^^^^^ inside Inter Compompany Reconciliation ^^^^^^^");
	}

	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = "";

		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		String childNodeName = "";

		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString("<Root>" + xmlString2 + "</Root>");
			}
			if (objContext != null && Integer.parseInt(objContext) == 1) {
				parentNodeList = dom2.getElementsByTagName("Header0");
				parentNode = parentNodeList.item(1);
				childNodeList = parentNode.getChildNodes();
				for (int x = 0; x < childNodeList.getLength(); x++) {
					childNode = childNodeList.item(x);
					childNodeName = childNode.getNodeName();
					if (childNodeName.equalsIgnoreCase("Detail1")) {
						errString = wfValData(dom, dom1, dom2, "1", editFlag, xtraParams);
						if (errString != null && errString.trim().length() > 0)
							break;
					} else if (childNodeName.equalsIgnoreCase("Detail2")) {
						errString = wfValData(dom, dom1, dom2, "2", editFlag, xtraParams);
						break;
					}
				}
			} else {
				errString = wfValData(dom, dom1, dom2, objContext, editFlag, xtraParams);
			}
		} catch (Exception e) {
			System.out.println("Exception : Inside DocumentMaster wfValData Method ..> " + e.getMessage());
			throw new ITMException(e);
		}
		return (errString);
	}

	public String wfValData(Document dom, Document dom1, Document dom2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException {
		GenericUtility genericUtility;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0, currentFormNo = 0, childNodeListLength = 0, cnt = 0;
		String childNodeName = null;
		SimpleDateFormat simpleDateFormat = null;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		String userId = "",firstValue = "",secondValue = "";
		Date tranDate1,tranDate2;

		try {
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			userId = getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			genericUtility = GenericUtility.getInstance();
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());

			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			switch (currentFormNo) {
			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();

				for (ctr = 0; ctr < childNodeListLength; ctr++) {
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					
					if ("site_code_fr".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("site_code_fr", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						if (firstValue.length() == 0) {
						    errCode = "NULLSITECD";
						    errString = getErrorString("site_code_fr", errCode, userId);
						    break;
						} else {
							cnt = getDBRowCount(conn, "site", "site_code", firstValue);
							if (cnt == 0) {
								errCode = "INVDSITECD";
								errString = getErrorString("site_code_fr", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("site_code_to".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("site_code_to", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						if (firstValue.length() == 0) {
						    errCode = "NULLSITECD";
						    errString = getErrorString("site_code_to", errCode, userId);
						    break;
						} else {
							cnt = getDBRowCount(conn, "site", "site_code", firstValue);
							if (cnt == 0) {
								errCode = "INVDSITECD";
								errString = getErrorString("site_code_to", errCode, userId);
								break;
							}
						}
						firstValue = null;
					} else if ("from_tran_date".equalsIgnoreCase(childNodeName) || "to_tran_date".equalsIgnoreCase(childNodeName)) {
						firstValue = genericUtility.getColumnValue("from_tran_date", dom);
						secondValue = genericUtility.getColumnValue("to_tran_date", dom);
						firstValue = firstValue == null ? "" : firstValue.trim();
						secondValue = secondValue == null ? "" : secondValue.trim();
					    
						if (firstValue.length() == 0) {
							errCode = "VMDTNAF";
							errString = getErrorString("from_tran_date", errCode, userId);
							break;
					    } else if (secondValue.length() == 0) {
							errCode = "VMDTNAT";
							errString = getErrorString("to_tran_date", errCode, userId);
							break;
					    } else {
					    	tranDate1 = simpleDateFormat.parse(firstValue);
					    	tranDate2 = simpleDateFormat.parse(secondValue);
					    	if (tranDate1.after(tranDate2)) {
							    errCode = "VMDTGTR";
							    errString = getErrorString("from_tran_date", errCode, userId);
							    break;
							}
					    }
						firstValue = null;
						secondValue = null;
						tranDate1 = null;
						tranDate2 = null;
					}
				}

				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				conn = null;
			} catch (Exception d) {
				d.printStackTrace();
			}
		}
		return errString;
	}

	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException {
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try {
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			if (xmlString2.trim().length() > 0) {
				dom2 = parseString(xmlString2);
			}
			valueXmlString = itemChanged(dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams);
		} catch (Exception e) {
			System.out.println("Exception : [itemChanged(String,String)] :==>\n" + e.getMessage());
			throw new ITMException(e);
		}
		return valueXmlString;
	}

	public String itemChanged(Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException {

		String siteCodeTo = "", siteCodeFr = "", fromTranDate = "", toTranDate = "", insertQuery = "", path = "", objName = "intercomp_reconcile";
		StringBuffer valueXmlString = new StringBuffer();
		int currentFormNo = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		try
		{   
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;
			simpleDateFormat = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext);
			}
			valueXmlString = new StringBuffer("<?xml version = \"1.0\"?> <Root> <header> <editFlag>");
			valueXmlString.append(editFlag).append("</editFlag> </header>");
			switch (currentFormNo) {
			case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail1>");
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");
				//int childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equals("itm_default")) {
					Calendar cal = Calendar.getInstance();
					Date d = new Date();
					cal.setTime(d);
					cal.set(Calendar.DATE,1);
					d = cal.getTime();
					String fromDate = simpleDateFormat.format(d);
					cal.setTime(d);
					cal.add(Calendar.MONTH,3);
					cal.set(Calendar.DATE,0);
					d = cal.getTime();
					String toDate = simpleDateFormat.format(d);
					System.out.println("From Date :" + fromDate + " To Date :" + toDate);
					valueXmlString.append("<from_tran_date>").append(fromDate).append("</from_tran_date>");
					valueXmlString.append("<to_tran_date>").append(toDate).append("</to_tran_date>");
					valueXmlString.append("<site_code_fr>").append("00").append("</site_code_fr>");
					valueXmlString.append("<site_code_to>").append("ZZ").append("</site_code_to>");
				}
				valueXmlString.append("</Detail1>");
				break;
			case 2 : 
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				System.out.println("DOM2 Elements["+genericUtility.serializeDom(dom2).toString()+"]");
				//childNodeListLength = childNodeList.getLength();
				if(currentColumn.trim().equalsIgnoreCase("itm_default"))
				{
					siteCodeTo = genericUtility.getColumnValue("site_code_to",dom1);
					siteCodeFr = genericUtility.getColumnValue("site_code_fr",dom1);
					fromTranDate = genericUtility.getColumnValue("from_tran_date", dom);
			    	toTranDate = genericUtility.getColumnValue("to_tran_date", dom);
			    	System.out.println("siteCodeTo="+siteCodeTo+  "  siteCodeFr ="+siteCodeFr);

					path = CommonConstants.JBOSSHOME + File.separator + "sql" + File.separator + "oracle/relatePartyConsoleSupply.sql";
					String adpQuery = genericUtility.readFile(path);
					
					insertQuery = "Insert Into Intercomp_Reco_Trace ( Tran_Id,Tran_Date,Ref_Ser,Ref_Id,Ref_Ser__To,Ref_Id__To,Site_Code,Site_Code__To,Amount,Chg_Date,Chg_User,Chg_Term) values (?,?,?,?, ?,?,?,?, ?,?,?,? )";
					valueXmlString.append("<site_code_fr><![CDATA[").append(siteCodeFr).append("]]></site_code_fr>");
					valueXmlString.append("<site_code_to><![CDATA[").append(siteCodeTo).append("]]></site_code_to>");
					valueXmlString.append("<Detail2>");
					valueXmlString.append(getXMLTransData(conn,adpQuery,insertQuery,objName,new StringBuffer(genericUtility.serializeDom(dom))));
					valueXmlString.append("</Detail2>");
				
				}
				break;
			}
			valueXmlString.append("</Root>"); 
		}
		catch(Exception e) 
		{
			e.printStackTrace();
			System.out.println("Exception ::"+ e.getMessage()); 
			throw new ITMException(e); 
		}
		finally 
		{
			try
			{
				if(conn != null)
				{
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close(); 
				}
				conn = null;
			}
			catch(Exception d)
			{
				d.printStackTrace(); 
			}
		}
		return valueXmlString.toString();
	}

	@Override
	public String getData(String arg0, String arg1, String arg2, String arg3) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String getData(Document arg0, Document arg1, String arg2, String arg3) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String process() throws RemoteException, ITMException {
		return null;
	}

	@Override
	public void remove() {
	}

	@Override
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
		return null;
	}

	@Override
	public String process(Document dom, Document dom2, String windowName, String xtraParams) throws RemoteException, ITMException {
		return null;
	}
	
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
		int count=-1;
		
		if(conn!=null){
			
			ResultSet rs=null;
			PreparedStatement pstmt = null;
			
			String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
			//System.out.println("SQL in getDBRowCount method : "+sql);
			try
			{
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,whrCondVal);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}
			}
			catch(SQLException e)
			{
				System.out.println("SQL Exception In getDBRowCount method of InterCompReconcile Class : "+e.getMessage());
				e.printStackTrace();
			}
			catch(Exception ex)
			{
				System.out.println("Exception In getDBRowCount method of InterCompReconcile Class : "+ex.getMessage());
				ex.printStackTrace();
			}finally{
				
				try{
					
					if(pstmt!=null){
						pstmt.close();
						pstmt = null;
					}
					if(rs!=null){
						rs.close();
						rs = null;
					}
				}catch (SQLException se) {
					se.printStackTrace();
				}
			}
		}else{
			try {
				throw new SQLException("Connection passed to InterCompReconcile.getDBRowCount() method is null");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}
	private StringBuffer getXMLTransData(Connection conn, String sql, String insertSql, String objName, StringBuffer stringBuffer) throws Exception
	{
    	StringBuffer valueXmlString = new StringBuffer("");
    	TransIDGenerator generator = null;
		if(conn!=null){
			ResultSet rs=null;
			PreparedStatement pstmt = null;
			
			int columnCnt = 0;
			int columnItr = 0, detailListCnt = 1;
			ResultSetMetaData resultSetMetaData = null;
			ArrayList<ArrayList<String>> arrayListData = null;
			ArrayList<String> traceData = null;
			try {
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if ( rs != null ) {
					resultSetMetaData = rs.getMetaData();
					columnCnt = resultSetMetaData.getColumnCount();
					while(rs.next())
					{
						valueXmlString.append("<Detail domID='"+ detailListCnt++ +"' selected = 'N'>\r\n");
						for (columnItr = 1;columnItr <= columnCnt;columnItr++) {
							valueXmlString.append("<");
							valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr));
							valueXmlString.append(">");
							valueXmlString.append("<![CDATA[");
							valueXmlString.append(rs.getString(columnItr) == null ? "" : rs.getString(columnItr).trim());
							valueXmlString.append("]]>");
							valueXmlString.append("</");
							valueXmlString.append(resultSetMetaData.getColumnLabel(columnItr));
							valueXmlString.append(">\r\n");
						}
						valueXmlString.append("</Detail>\r\n");
					}
					arrayListData =  buildTraceData(rs);
					pstmt = null;
					rs = null;
				}
				if(arrayListData != null && arrayListData.size() > 0)
				{
					String keyString = null;
					sql = null;
					sql = "SELECT KEY_STRING FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, "w_"+objName);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						keyString = rs.getString("KEY_STRING");
						rs.close();
					}
					pstmt = null;
					
					pstmt = conn.prepareStatement(insertSql);
					generator = new TransIDGenerator(stringBuffer.toString(),userId,CommonConstants.DB_NAME);
					String uniqueKey = generator.generateTranSeqID("INCORE", "tran_id", keyString, conn);
					
					for (int itr = 0;itr < arrayListData.size();itr++) {
						traceData = arrayListData.get(itr);

						pstmt.setString(1, uniqueKey);
						for (int cnt = 0;cnt < traceData.size();cnt++) {
							pstmt.setString(cnt+2, traceData.get(cnt));
						}
						pstmt.addBatch();
					}
					
					int rowsInsertedWorkOrder[] = pstmt.executeBatch();
					pstmt.close();
					conn.commit();
					uniqueKey = null;
				}
			}
			catch(SQLException e) {
				System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
				e.printStackTrace();
			}
			catch(Exception ex) {
				System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
				ex.printStackTrace();
			} finally {
				try{
					if(pstmt!=null){
						pstmt.close();
						pstmt = null;
					}
					if(rs!=null){
						rs.close();
						rs = null;
					}
				}catch (SQLException se) {
					se.printStackTrace();
				}
				sql = null;
				insertSql = null;
				objName = null;
				stringBuffer = null;
			}
		}else{
			try {
				throw new SQLException("Connection passed to InterCompReconcileProcess.getXMLTransData() method is null");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return valueXmlString;
	}
    private ArrayList<ArrayList<String>> buildTraceData(ResultSet rs) throws Exception
	{
    	ArrayList<ArrayList<String>> arrayListData = new ArrayList<ArrayList<String>>();
		try {
			ArrayList<String> traceData = null;
			if ( rs != null ) {
				//rs.first();
				//System.out.println("Position of RS["+rs.getRow()+"]");
				while(rs.next())
				{
					traceData = new ArrayList<String>();
					
					arrayListData.add(traceData);
				}
			}
			traceData = null;
		} catch(SQLException e) {
			System.out.println("SQL Exception In getXMLTransData method of InterCompReconcileProcess Class : "+e.getMessage());
			e.printStackTrace();
		} catch(Exception ex) {
			System.out.println("Exception In getXMLTransData method of InterCompReconcileProcess Class : "+ex.getMessage());
			ex.printStackTrace();
		} finally {
			try{
				if(rs!=null){
					rs.close();
					rs = null;
				}
			}catch (SQLException se) {
				se.printStackTrace();
			}
		}
		return arrayListData;
	}
}