/********************************************************
	Title : ReconciliationSyatemProcess 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.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.w3c.dom.Document;
import javax.ejb.Stateless;

@Stateless
public class InterCompReconcileProcess extends ProcessEJB implements InterCompReconcileProcessLocal, InterCompReconcileProcessRemote {

	String errString = "";
	Connection conn = null;
    GenericUtility genericUtility = GenericUtility.getInstance();
    String userId = null;
	String chgUser = null;
	String chgTerm = null;
    
    public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ Inside InterCompReconcileProcessEJB -> Process Method 1 ^^^^^^^");
		Document detailDom = null;
		Document headerDom = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		String retString = "";
	
		try {
		    if (xmlString != null && xmlString.trim().length() != 0) {
			headerDom = genericUtility.parseString(xmlString);
		    }
		    if (xmlString2 != null && xmlString2.trim().length() != 0) {
			detailDom = genericUtility.parseString(xmlString2);
		    }
		    retString = process(headerDom, detailDom, windowName, xtraParams);
		} catch (Exception e) {
		    System.out.println("Exception InterCompReconcileProcess Method 1");
		    e.printStackTrace();
		    retString = itmDBAccessEJB.getErrorString("", "VTCBFAIL", "", 1);
		    throw new ITMException(e);
		}
	    System.out.println("Exception InterCompReconcileProcess ******* retString ******* " + retString);
		return retString;
    }

    public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException, ITMException {
		System.out.println("^^^^^^^ Inside InterCompReconcileProcessEJB -> Process Method 2 ^^^^^^^");
	
		String retString = "";
		
		PreparedStatement pStmt = null;
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ConnDriver connDriver = new ConnDriver();
		boolean isError = false;
		try {
			System.out.println("getting==================>");
			conn = connDriver.getConnectDB("DriverITM");
			System.out.println("get==================>");
		    conn.setAutoCommit(false);
		    connDriver = null;
	
		    retString = populateSQLQuery(headerDom,genericUtility);
		    userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "userId");

			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			CommonConstants.setIBASEHOME();

		} catch (Exception e) {
		    isError = true;
		    retString = itmDBAccessEJB.getErrorString("", "VTCBFAIL", "", "", conn);
		    throw new ITMException(e);
		} finally {
		    try {
			if (conn != null) {
			    if (isError) {
					conn.rollback();
					retString = itmDBAccessEJB.getErrorString("", "VTCBFAIL", "", "", conn);
			    }
			    conn.close();
			    conn = null;
			}
			if (pStmt != null) {
			    pStmt.close();
			    pStmt = null;
			}

		    } catch (Exception e) {
			throw new ITMException(e);
		    }
		}
		return retString;
	}
    
    private String populateSQLQuery(Document dom,GenericUtility genericUtility) throws Exception
	{
    	
		ResultSet rs = null;
		errString = "";
		String adpQuery = null,fromSiteCode="",toSiteCode="",fromTranDate="",toTranDate="",
				sundryTypeFr="",sundryTypeTo="",sundryCodeFr="",sundryCodeTo="";
		PreparedStatement pstmt = null;
		StringBuffer valueXmlString = new StringBuffer("");
		try
		{
			 fromSiteCode = genericUtility.getColumnValue("site_code_fr", dom);
			 toSiteCode = genericUtility.getColumnValue("site_code_to", dom);
	    	 fromTranDate = genericUtility.getColumnValue("from_tran_date", dom);
	    	 toTranDate = genericUtility.getColumnValue("to_tran_date", dom);
	    	 //Added by Dadaso pawar on 20/MAY/14 [Start]
	    	 sundryTypeFr = genericUtility.getColumnValue("sundry_type_fr", dom);
	    	 sundryTypeTo = genericUtility.getColumnValue("sundry_type_to", dom);
	    	 sundryCodeFr = genericUtility.getColumnValue("sundry_code_fr", dom);
	    	 sundryCodeTo = genericUtility.getColumnValue("sundry_code_to", dom);
	    	
	    	 sundryTypeFr = sundryTypeFr == null ? "" : sundryTypeFr.trim();
	    	 sundryTypeTo = sundryTypeTo == null ? "" : sundryTypeTo.trim();
	    	 sundryCodeFr = sundryCodeFr == null ? "" : sundryCodeFr.trim();
	    	 sundryCodeTo = sundryCodeTo == null ? "" : sundryCodeTo.trim();
	    	 
	    	 //Added by Dadaso pawar on 20/MAY/14 [Start]
	    	 
	    	fromSiteCode = fromSiteCode == null ? "" : fromSiteCode.trim();
	    	toSiteCode = toSiteCode == null ? "" : toSiteCode.trim();
	    	fromTranDate = fromTranDate == null ? "" : fromTranDate.trim();
	    	toTranDate = toTranDate == null ? "" : toTranDate.trim();
	    	
	    	String path = null,sqlFileName = null;
	    	path = CommonConstants.JBOSSHOME + File.separator + "sql" + File.separator;
	    	sqlFileName =  "relatePartyConsoleSupply";
	    	
			if ((CommonConstants.DB_NAME).equalsIgnoreCase("db2"))
			{
				String adpQueryOriginal = readFile(path + sqlFileName + "DB2.sql");
				
				/*String strDateFrom = dateFrom.toString();
				strDateFrom = strDateFrom.replaceAll(" ", "-");
				strDateFrom = strDateFrom.replaceAll(":", ".");
				String strDateTo = dateTo.toString();
				strDateTo = strDateTo.replaceAll(" ", "-");
				strDateTo = strDateTo.replaceAll(":", ".");*/
				
				adpQuery = adpQueryOriginal;
				adpQuery = adpQuery.replaceAll("@sitecodefrom@", fromSiteCode);
				adpQuery = adpQuery.replaceAll("@sitecodeto@", toSiteCode);
				adpQuery = adpQuery.replaceAll("@trandateupto@", toTranDate);
				adpQuery = adpQuery.replaceAll("@trandatefrom@", fromTranDate);
				
				
				adpQuery = adpQuery.replaceAll("@sundrytypefrom@", sundryTypeFr);
				adpQuery = adpQuery.replaceAll("@sundrytypeto@", sundryTypeTo);				
				adpQuery = adpQuery.replaceAll("@sundrycodefrom@", sundryCodeFr);
				adpQuery = adpQuery.replaceAll("@sundrycodeto@", sundryCodeTo);
				
			} else if ((CommonConstants.DB_NAME).equalsIgnoreCase("oracle"))
			{
				String adpQueryOriginal = readFile(path + sqlFileName + "Oracle.sql");
				
				adpQuery = adpQueryOriginal;
				adpQuery = adpQuery.replaceAll("@sitecodefrom@", fromSiteCode);
				adpQuery = adpQuery.replaceAll("@sitecodeto@", toSiteCode);
				adpQuery = adpQuery.replaceAll("@trandateupto@", toTranDate);
				adpQuery = adpQuery.replaceAll("@trandatefrom@", fromTranDate);
				
				adpQuery = adpQuery.replaceAll("@sundrytypefrom@", sundryTypeFr);
				adpQuery = adpQuery.replaceAll("@sundrytypeto@", sundryTypeTo);				
				adpQuery = adpQuery.replaceAll("@sundrycodefrom@", sundryCodeFr);
				adpQuery = adpQuery.replaceAll("@sundrycodeto@", sundryCodeTo);
			}
			
			writeFile(adpQuery,path + "oracle/relatePartyConsoleSupply.sql");
			
		} catch (SQLException se)
		{
			System.out.println("SQLException :InterCompReconcileProcess : populateSQLQuery(ADPElement).." + se.getMessage());
			System.out.println(adpQuery);
			se.printStackTrace();
			errString = se.getMessage();
			throw new ITMException(se);
		} catch (Exception e)
		{
			System.out.println("Exception :InterCompReconcileProcess : populateSQLQuery(ADPElement):...." + e.getMessage() + ":");
			this.errString = genericUtility.createErrorString(e);
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		} finally
		{
			if (pstmt != null)
			{
				pstmt = null;
			}
			if (rs != null)
			{
				rs = null;
			}
		}
		return valueXmlString.toString();
	}
    private String readFile(String file) throws Exception
	{
		String sqlQry = "";
		try
		{
			System.out.println("File "+file);
			FileInputStream fin = new FileInputStream(file);
			BufferedReader br = new BufferedReader(new InputStreamReader(fin));
			String tempStr = "";
			while ((tempStr = br.readLine()) != null)
			{
				sqlQry = sqlQry + tempStr;
			}
			tempStr = null;
			br = null;
			fin.close();
		} catch (FileNotFoundException fe)
		{
			System.out.println("FileNotFoundException :InterCompReconcileProcess :readFile(String).." + fe.getMessage());
			fe.printStackTrace();
			errString = fe.getMessage();
			throw new ITMException(fe);
		}

		catch (Exception e)
		{
			System.out.println("Exception :InterCompReconcileProcess :readFile(String).." + e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
		return sqlQry;
	}
    private void writeFile(String adpQuery,String fileName) throws Exception
	{
		try
		{
			System.out.println("fileName "+fileName);
			FileOutputStream fout = null;
			BufferedWriter bw = null;
			fout = new FileOutputStream(fileName);
			bw = new BufferedWriter(new OutputStreamWriter(fout));
			bw.write(adpQuery);
			bw.close();
			fout.close();
			
		} catch (FileNotFoundException fe)
		{
			System.out.println("FileNotFoundException :InterCompReconcileProcess :writeFile(String,String).." + fe.getMessage());
			fe.printStackTrace();
			errString = fe.getMessage();
			throw new ITMException(fe);
		}

		catch (Exception e)
		{
			System.out.println("Exception :InterCompReconcileProcess :writeFile(String,String).." + e.getMessage());
			e.printStackTrace();
			errString = e.getMessage();
			throw new ITMException(e);
		}
	}
}