package ibase.webitm.ejb.fin;

import ibase.system.config.ConnDriver;
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;

public class ThreeWayMatchCond {
	public String confirm(String tranId, String empCodeAprv, String xmlDataAll, String processId, String keyFlag) throws RemoteException, ITMException {
		System.out.println("confirm workflow invocation");
		System.out.println("Parameters ::: tranId[" + tranId + "], empCodeAprv[" + empCodeAprv + "], xmlDataAll[" + xmlDataAll + "],processId["+processId+"],keyFlag["+keyFlag+"]");
		String loginCode = "";
		String loginSiteCode = "";
		String loginEmpCode = "";
		String xtraParams = "loginCode=" + loginCode + "~~" + "loginSiteCode=" + loginSiteCode + "~~" + "loginEmpCode=" + loginEmpCode;
		String retString = "";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = null;
		try {
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);

			empCodeAprv = empCodeAprv == null ? "" : empCodeAprv.trim();
			
			if("LEVEL1".equalsIgnoreCase(keyFlag) || "LEVEL2".equalsIgnoreCase(keyFlag) || "LEVEL3".equalsIgnoreCase(keyFlag) || "LEVEL_HOD".equalsIgnoreCase(keyFlag)|| "LEVEL_HHODS".equalsIgnoreCase(keyFlag))
			{
				int resultCnt = 0;
				String signStatus = "", userCode = "", processInfo[] = null, activityId = "", roleCode = "";
			
				if("LEVEL1".equalsIgnoreCase(keyFlag)) {
					activityId = "SIGN_TRANS_FL"; roleCode = "ROL01";
				}
				else if("LEVEL2".equalsIgnoreCase(keyFlag)) {
					activityId = "SIGN_TRANS_SL"; roleCode = "ROL02";
				}
				
				else if("LEVEL3".equalsIgnoreCase(keyFlag)) {
					activityId = "SIGN_TRANS_TL"; roleCode = "ROL03";
				}
				
				else if("LEVEL_HOD".equalsIgnoreCase(keyFlag)) {
					activityId = "SIGN_TRANS_HL"; roleCode = "ESCHOD";
				}
				else if("LEVEL_HHODS".equalsIgnoreCase(keyFlag)) {
					activityId = "SIGN_TRANS_HHL"; roleCode = "ESCHODHODS";
				}
				
				sql = "select SIGN_STATUS from OBJ_SIGN_TRANS "
						+ "WHERE ltrim(rtrim(REF_SER)) = 'S-MCH' AND ltrim(rtrim(REF_ID)) = ? AND ltrim(rtrim(ROLE_CODE__SIGN)) = ? "
						+ "AND SIGN_STATUS = 'U'";
						
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setString(2, roleCode);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					signStatus = rs.getString("SIGN_STATUS");
				}
				System.out.println("signStatus = " + signStatus);

				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if("U".equalsIgnoreCase(signStatus)){
					sql = "select code from users where emp_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, empCodeAprv);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						userCode = rs.getString("code");
					}
					System.out.println("userCode = " + userCode);

					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					
					sql = "UPDATE OBJ_SIGN_TRANS "
							+ "SET SIGN_DATE = sysdate, USER_ID__SIGN = ?, SIGN_REMARKS = 'HR_SPAN is over', SIGN_STATUS = 'V', EMP_CODE = ? "
							+ "WHERE ltrim(rtrim(REF_SER)) = 'S-MCH' AND ltrim(rtrim(REF_ID)) = ? AND ltrim(rtrim(ROLE_CODE__SIGN)) = ? "
							+ "AND SIGN_STATUS = 'U'";
							
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, userCode);
					pstmt.setString(2, empCodeAprv);
					pstmt.setString(3, tranId);
					pstmt.setString(4, roleCode);
					resultCnt += pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					System.out.println("Update Successfully:"+resultCnt);
					
					processInfo = processId.split(":");
					sql = "UPDATE WF_PRC_STATUS "
							+ "SET STATUS_DATE = sysdate, PROCESS_STATUS = 4, ERR_STATUS = '0', "
							+ "STATUS_REMARKS='Escalated -> Completing :"+processId+" Invoked :"+processId+"' "
							+ "WHERE ltrim(rtrim(REF_SER)) = 'S-MCH' AND REF_ID = ? AND PROCESS_ID = ? AND INSTANCE_ID = ? AND ACTIVITY_ID = ? AND PROCESS_STATUS != 3";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					pstmt.setString(2, processInfo[0]);
					pstmt.setString(3, processInfo[1]);
					pstmt.setString(4, activityId);
					resultCnt += pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					System.out.println("Update Successfully:"+resultCnt);
					if(resultCnt > 1) {
						conn.commit();
						retString = "Y";
					} else {
						conn.rollback();
					}
				} else {
					retString = "N";
				}
			}
			
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			System.out.println("Exeption occured");

			throw new ITMException(e);
		} finally {
			if (conn != null) {
				try {
					conn.commit();
					conn.close();
					conn = null;
				} catch (SQLException e) {
					e.printStackTrace();
					System.out.println("Exeption in Finaly");
				}
			}
		}

		return retString;
	}
}
