/********************************************************
 Title : LoanPartyBank
 Date  : 07/08/2013
 Author: Swati Bade

 ********************************************************/

package ibase.webitm.ejb.fin;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
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.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.Node;
import javax.ejb.Stateless;

@Stateless
public class LoanPartyBank extends ValidatorEJB implements LoanPartyBankLocal,
		LoanPartyBankRemote {
	public LoanPartyBank() {
		System.out.println("inside loanparty");
	}

	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  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;
		String errString = "";
		String errCode = "";
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = "";
		String userId = "";
		String partyCode = "";
		String currCode="";//added by priyanka on 28/01/15

		try {
			System.out.println("wfValData>>>>>>>>>>");

			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			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 (childNodeName.equalsIgnoreCase("party_code")) {
						partyCode = genericUtility.getColumnValue("party_code",
								dom);
						partyCode = partyCode == null ? "" : partyCode.trim();
						if (partyCode.length() == 0) {
							errCode = "VTLPCDNL";
							errString = getErrorString("party_code", errCode,
									userId);
							break;
						} else {
							sql = "select count(1) from loanparty where party_code = ?";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, partyCode);
							rs = pStmt.executeQuery();

							if (rs.next()) {
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if (cnt == 0) {
								errCode = "VTLPCDNF";
								errString = getErrorString("party_code",
										errCode, userId);
								break;
							}
						}
					} else if (childNodeName.equalsIgnoreCase("bank_code__ben")) {
						String bankCodeBen = genericUtility.getColumnValue(
								"bank_code__ben", dom);
						partyCode = genericUtility.getColumnValue("party_code",
								dom);
						bankCodeBen = bankCodeBen == null ? "" : bankCodeBen
								.trim();
						System.out.println("EditFlag-->"+editFlag);
						if (bankCodeBen.length() == 0) {
							errCode = "VTBCBNNL";
							errString = getErrorString("bank_code__ben",
									errCode, userId);
							break;
						} 
						
						else if(!editFlag.trim().equalsIgnoreCase("E")) {
							sql = "select count(1) from loanparty_bank where "
								  +" party_code = ? and bank_code__ben= ? ";
							pStmt = conn.prepareStatement(sql);
							pStmt.setString(1, partyCode);
							pStmt.setString(2, bankCodeBen);
							rs = pStmt.executeQuery();

							if (rs.next()) {
								cnt = rs.getInt(1);
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if (cnt > 0) {
								errCode = "VMLPCABCE";
								errString = getErrorString("party_code",
										errCode, userId);
								break;
							}
						}

					} else if (childNodeName.equalsIgnoreCase("bank_name__ben")) {
						String bankNameBen = genericUtility.getColumnValue(
								"bank_name__ben", dom);
						bankNameBen = bankNameBen == null ? "" : bankNameBen
								.trim();
						if (bankNameBen.length() == 0) {
							errCode = "VTBNBNNL";
							errString = getErrorString("bank_name__ben",
									errCode, userId);
							break;
						}
					}
//					else if (childNodeName
//							.equalsIgnoreCase("bank_acct_no__ben")) {
//						String bankActNo = genericUtility.getColumnValue(
//								"bank_acct_no__ben", dom);
//						bankActNo = bankActNo == null ? "" : bankActNo.trim();
//						if (bankActNo.length() == 0) {
//							errCode = "VTBANNL";
//							errString = getErrorString("bank_acct_no__ben",
//									errCode, userId);
//							break;
//						}
//					} 
					else if (childNodeName.equalsIgnoreCase("active_yn")) {
						String active = genericUtility.getColumnValue(
								"active_yn", dom);
						active = active == null ? "" : active.trim();
						if (active.length() == 0) {
							errCode = "VTACTNL";
							errString = getErrorString("active_yn", errCode,
									userId);
							break;
						}
					} 
					//added by priyanka on 28/01/15 as per manoj sharma instruction
					else if (childNodeName.equalsIgnoreCase("curr_code")) 
					{
						
						currCode=genericUtility.getColumnValue("curr_code", dom);
						System.out.println("currCode from loan party bank===="+currCode);
						currCode = currCode == null ? "" : currCode.trim();
						if(currCode==null||currCode.trim().length()==0)
						{
							errCode = "VMCUR2";
							errString = getErrorString("curr_code", errCode,userId);
							break;
						}
						else
						{
							sql="select count(*) from currency where curr_code= ?";
							pStmt=conn.prepareStatement(sql);
							pStmt.setString(1,currCode);
							rs = pStmt.executeQuery();
							if (rs.next()) 
							{
								cnt = rs.getInt(1);
								System.out.println("Count from Curr_code===="+cnt);
							}
							rs.close();
							rs = null;
							pStmt.close();
							pStmt = null;
							if (cnt == 0) 
							{
								errCode = "VMCURR1";
								errString = getErrorString("curr_code",	errCode, userId);
								break;
							}
						
						}
					}
					
				}// end of for
			}// end of switch
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Exception ..> " + e.getMessage());
			errString = e.getMessage();
			throw new ITMException(e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				conn = null;
			} catch (Exception d) {
				d.printStackTrace();
				System.out.println("Exception ..> " + d);
			}
		}
		return errString;
	}

	// /CHANGES FOR ITMCHANGE

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

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

		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		String childNodeName = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		String sql = "", sql1 = "";
		int currentFormNo = 0;
		String columnValue = "", siteCode = "";
		String chgTerm = "", chgUser = "", siteCodePay = "";
		String bankCodeBen="",banknameben = "", add1Ben = "", add2Ben = "", add3Ben = "", cityBen = "", pinBen = "";
		String stateCodeBen = "", acctcode = "", swiftcode = "", countCodeBen = "";
		String bankNameInter = "", add1Inter = "", add2Inter = "", add3Inter = "";
		String cityInter = "", pinInter = "", stateCodeInter = "", countCodeInter = "";
		String siteCodePayDescr = "",  PartyCode = "",bankCodeInter="";
		String PartyName = "", currcode = "", swiftcodeint = "";
		int ctr = 0;

		try {
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			if (objContext != null && objContext.trim().length() > 0) {
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer(
					"<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,
					"chg_term");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,
					"chg_user");
			System.out.println("-------- currentFormNo : " + currentFormNo);
			switch (currentFormNo) {

			case 1:
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				ctr = 0;
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();

				if (currentColumn.trim().equalsIgnoreCase("itm_default")) {
					System.out .println("------------@@@@@@@@in itm_default---");
					Calendar currentDate = Calendar.getInstance();
					SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(
							genericUtility.getApplDateFormat());
					String systemDate = simpleDateFormatObj.format(currentDate
							.getTime());					

					valueXmlString.append("<site_code>").append("")
							.append("</site_code>");
					valueXmlString.append("<chg_date><![CDATA[")
							.append(systemDate).append("]]></chg_date>\r\n");
					valueXmlString.append("<chg_term><![CDATA[")
							.append(chgTerm).append("]]></chg_term>");
					valueXmlString.append("<chg_user><![CDATA[")
							.append(chgUser).append("]]></chg_user>");

				}

				if ((currentColumn.trim().equalsIgnoreCase("party_code"))) {
					String partyCode = genericUtility.getColumnValue(
							"party_code", dom);
					
					if (partyCode != null && partyCode.trim().length() > 0) {
						sql = " SELECT PARTY_CODE,PARTY_NAME FROM loanparty WHERE "
							 +" party_code = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, partyCode);
						rs = pstmt.executeQuery();
						while (rs.next()) {
							PartyCode = rs.getString(1) == null ? "" : rs
									.getString(1);
							PartyName = rs.getString(2) == null ? "" : rs
									.getString(2);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;

						valueXmlString
								.append("<party_name>")
								.append("<![CDATA[" + PartyName +
										 "]]>")
								.append("</party_name>");

					} else {
						valueXmlString.append("<party_name>")
								.append("<![CDATA[]]>").append("</party_name>");
					}
				}else if ((currentColumn.trim().equalsIgnoreCase("bank_code__ben"))) {
					
					bankCodeBen=genericUtility.getColumnValue("bank_code__ben", dom);
					if (bankCodeBen != null && bankCodeBen.trim().length() > 0) {
						sql = " SELECT BANK_NAME__BEN,ADDR1__BEN,ADDR2__BEN,ADDR3__BEN,CITY__BEN, "
							 +" PIN__BEN,STATE_CODE__BEN,COUNT_CODE__BEN "
							 +" FROM LOANPARTY_BANK WHERE BANK_CODE__BEN=? ";
								
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, bankCodeBen);
						rs = pstmt.executeQuery();
						while (rs.next()) {
							banknameben = rs.getString(1) == null ? "" : rs.getString(1);
							add1Ben = rs.getString(2) == null ? "" : rs.getString(2);
							add2Ben = rs.getString(3) == null ? "" : rs.getString(3);
							add3Ben = rs.getString(4) == null ? "" : rs.getString(4);
							cityBen = rs.getString(5) == null ? "" : rs.getString(5);
							pinBen  = rs.getString(6) == null ? "" : rs.getString(6);
							stateCodeBen = rs.getString(7) == null ? "" : rs.getString(7);
							countCodeBen = rs.getString(8) == null ? "" : rs.getString(8);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append("<bank_name__ben>").append("<![CDATA[" + banknameben 
								              + "]]>").append("</bank_name__ben>");
						valueXmlString.append("<addr1__ben>").append("<![CDATA[" + add1Ben 
					                          + "]]>").append("</addr1__ben>");
						valueXmlString.append("<addr2__ben>").append("<![CDATA[" + add2Ben 
		                          			  + "]]>").append("</addr2__ben>");
						valueXmlString.append("<addr3__ben>").append("<![CDATA[" + add3Ben 
		                          		  	  + "]]>").append("</addr3__ben>");
						valueXmlString.append("<city__ben>").append("<![CDATA[" + cityBen 
                    		  	  			  + "]]>").append("</city__ben>");
						valueXmlString.append("<pin__ben>").append("<![CDATA[" + pinBen 
                    		  	  			  + "]]>").append("</pin__ben>");
						valueXmlString.append("<state_code__ben>").append("<![CDATA[" + stateCodeBen 
                    		  	  			  + "]]>").append("</state_code__ben>");
						valueXmlString.append("<count_code__ben>").append("<![CDATA[" + countCodeBen 
                    		  	  			  + "]]>").append("</count_code__ben>");
					}
				}else if ((currentColumn.trim().equalsIgnoreCase("bank_code__inter"))) {
					
					bankCodeInter=genericUtility.getColumnValue("bank_code__inter", dom);
					if (bankCodeInter != null && bankCodeInter.trim().length() > 0) {
						sql = " SELECT BANK_NAME__INTER,ADDR1__INTER,ADDR2__INTER,ADDR3__INTER,"
							 +" CITY__INTER,PIN__INTER,STATE_CODE__INTER,COUNT_CODE__INTER "
							 +" FROM LOANPARTY_BANK WHERE BANK_CODE__INTER=? ";
								
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, bankCodeInter);
						rs = pstmt.executeQuery();
						while (rs.next()) {
							bankNameInter = rs.getString(1) == null ? "" : rs.getString(1);
							add1Inter = rs.getString(2) == null ? "" : rs.getString(2);
							add2Inter = rs.getString(3) == null ? "" : rs.getString(3);
							add3Inter = rs.getString(4) == null ? "" : rs.getString(4);
							cityInter = rs.getString(5) == null ? "" : rs.getString(5);
							pinInter  = rs.getString(6) == null ? "" : rs.getString(6);
							stateCodeInter = rs.getString(7) == null ? "" : rs.getString(7);
							countCodeInter = rs.getString(8) == null ? "" : rs.getString(8);
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						valueXmlString.append("<bank_name__inter>").append("<![CDATA[" + bankNameInter 
								              + "]]>").append("</bank_name__inter>");
						valueXmlString.append("<addr1__inter>").append("<![CDATA[" + add1Inter 
					                          + "]]>").append("</addr1__inter>");
						valueXmlString.append("<addr2__inter>").append("<![CDATA[" + add2Inter 
		                          			  + "]]>").append("</addr2__inter>");
						valueXmlString.append("<addr3__inter>").append("<![CDATA[" + add3Inter 
		                          		  	  + "]]>").append("</addr3__inter>");
						valueXmlString.append("<city__inter>").append("<![CDATA[" + cityInter 
                    		  	  			  + "]]>").append("</city__inter>");
						valueXmlString.append("<pin__inter>").append("<![CDATA[" + pinInter 
                    		  	  			  + "]]>").append("</pin__inter>");
						valueXmlString.append("<state_code__inter>").append("<![CDATA[" + stateCodeInter 
                    		  	  			  + "]]>").append("</state_code__inter>");
						valueXmlString.append("<count_code__inter>").append("<![CDATA[" + countCodeInter 
                    		  	  			  + "]]>").append("</count_code__inter>");
					}
				}
				
				valueXmlString.append("</Detail1>");
				
			}// end switch
			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();
				System.out.println("Exception ..> " + d.getMessage());
			}
		}

		return valueXmlString.toString();
	}

}