/********************************************************
	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);
				System.out.println("sundryTypeFrDB2----->>["+sundryTypeFr+"]");
				if(sundryTypeFr.length() > 0){
					if("customer".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="C";
					else if("supplier".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="S";
					else if("employee".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="E";
					else if("loan_party".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="L";
					else if("tax_authority".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="X";
					else if("transporter".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="T";
					else if("sales_pers".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="P";
					else if("strg_customer".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="R";
				}
				System.out.println("sundryTypeFr After DB2----->>["+sundryTypeFr+"]");
				System.out.println("sundryTypeTo----->>["+sundryTypeTo+"]");
				if(sundryTypeFr.length() > 0){
					if("customer".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="C";
					else if("supplier".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="S";
					else if("employee".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="E";
					else if("loan_party".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="L";
					else if("tax_authority".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="X";
					else if("transporter".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="T";
					else if("sales_pers".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="P";
					else if("strg_customer".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="R";
					}
				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);
				
				
				System.out.println("sundryTypeFr Oracle----->>["+sundryTypeFr+"]");
				if(sundryTypeFr.length() > 0 && sundryTypeTo.length() > 0){
					if("customer".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="C";
					else if("supplier".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="S";
					else if("employee".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="E";
					else if("loan_party".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="L";
					else if("tax_authority".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="X";
					else if("transporter".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="T";
					else if("sales_pers".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="P";
					else if("strg_customer".equalsIgnoreCase(sundryTypeFr))
						sundryTypeFr="R";
				}
				System.out.println("sundryTypeFr After Oracle----->>["+sundryTypeFr+"]");
				System.out.println("sundryTypeTo----->>["+sundryTypeTo+"]");
				if(sundryTypeTo.length() > 0){
					if("customer".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="C";
					else if("supplier".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="S";
					else if("employee".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="E";
					else if("loan_party".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="L";
					else if("tax_authority".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="X";
					else if("transporter".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="T";
					else if("sales_pers".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="P";
					else if("strg_customer".equalsIgnoreCase(sundryTypeTo))
						sundryTypeTo="R";
					}
				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);
		}
	}
    public String getValuesForPopHelp(String sundryType){
    	System.out.println("in getValuesForPopHelp EJB called222---------------");
    	System.out.println("SundryType222--->>["+sundryType+"]");
    	String sql="",sundry;
    	Connection conn1 = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		//StringBuffer valueXmlString = new StringBuffer();
		
		/*valueXmlString.append("<?xml version='1.0' encoding='ISO-8859-1'?>" + 
		  "<?xml-stylesheet type=\"text/xsl\" href=\"../xsl/popup.xsl\"?>" + 
		  "<?xml-stylesheet type=\"text/css\" href=\"../css/xtree.css\"?>"); */
		StringBuffer valueXmlString = new StringBuffer("<Root>\r\n");
		
		try 
		{
			ConnDriver connDriver = new ConnDriver();
			conn1 = connDriver.getConnectDB("DriverITM");
			if("customer".equalsIgnoreCase(sundryType))
				sql = "select cust_code,cust_name from customer";
			else if("supplier".equalsIgnoreCase(sundryType))
				sql="select supp_code,sh_name from supplier";
			else if("employee".equalsIgnoreCase(sundryType))
				sql="select emp_code,emp_fname || ' ' || emp_lname from employee";
			else if("loan_party".equalsIgnoreCase(sundryType))
				sql="select party_code,party_name from loanparty";
			else if("tax_authority".equalsIgnoreCase(sundryType))
				sql="select tauth_code,tauth_name from tax_authority";
			else if("transporter".equalsIgnoreCase(sundryType))
				sql="select tran_code,tran_name from transporter";
			else if("sales_pers".equalsIgnoreCase(sundryType))
				sql="select sales_pers,sp_name from sales_pers";
			else if("strg_customer".equalsIgnoreCase(sundryType))
				sql="select sc_code,first_name || ' ' || last_name from strg_customer";
			
			System.out.println("Sql getValuesForPopHelp-->>["+sql+"]");
			pstmt = conn1.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int num = 1;
			while (rs.next()) 
			{
				valueXmlString.append("<Detail domID='" + num	+ "'>\r\n");
				valueXmlString.append("<sundry_code><![CDATA[").append(rs.getString(1)).append("]]></sundry_code>\r\n");
				valueXmlString.append("<sundry_name><![CDATA[").append(rs.getString(2)).append("]]></sundry_name>\r\n");				
				
				valueXmlString.append("</Detail>\r\n");
				num++;
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null ;
			
		}
		catch(Exception e){
			System.out.println("Exception in getValuesForPopHelp.............");
			e.printStackTrace();			
		}
		finally{
			try{
			if(conn1!=null){
				conn1.close();
			}
			}
			catch(Exception e){
				e.printStackTrace();
			}
		}
		valueXmlString.append("</Root>\r\n");
		System.out.println("\n****ValueXmlString ::[[" + valueXmlString.toString()	+"]]:********");
		return valueXmlString.toString();
    	
    }
}