package ibase.webitm.ejb.fin.adv;

import ibase.system.config.ConnDriver;
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 PayAprvActivityStatus 
{
  
public String confirm(String tranId, String empCodeAprv, String xmlDataAll, String processId, String keyFlag) throws RemoteException, ITMException 
	{
		System.out.println("Activity Check JB");
		System.out.println("Parameters ::: tranId[" + tranId + "], empCodeAprv[" + empCodeAprv + "], xmlDataAll[" + xmlDataAll + "],processId["+processId+"],keyFlag["+keyFlag+"]");
		String retString = "";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		ConnDriver connDriver = new ConnDriver();
		try 
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			long counter= 0;
			String role_code__sign = "";
			empCodeAprv = empCodeAprv == null ? "" : empCodeAprv.trim();
			
			
			if("CHKUSRCOND".equalsIgnoreCase(keyFlag)) 
			{
				sql = "select ROLE_CODE__SIGN from OBJ_SIGN_TRANS "
						+ "WHERE ltrim(rtrim(REF_SER)) = 'M-PAY' AND ltrim(rtrim(REF_ID)) = ? AND ltrim(rtrim(ROLE_CODE__SIGN)) IN('RLCDJ2L5KF','RLCDJ2L5KS','RLCDJ2L1F','RLCDJ2L1S','RLCDJ2L2F','RLCDJ2L2S','RLCDJ2L5F','RLCDJ2L5S')";
						
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				if (!rs.next()) 
				{
					retString = "1";
					
				}
				else
				{
					retString = "0";
				}
				System.out.println("retString = " + retString);
	
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	
			}
			else if ("BOTHAPPROVAL".equalsIgnoreCase(keyFlag)) 
			{
						
				sql = "select count(s.ref_id) counter,s3.role_code__sign from obj_sign_trans s join " +
					"(select s1.ref_id,s1.role_code__sign from obj_sign_trans s1 " +
					"where ltrim(rtrim(s1.REF_SER)) = 'M-PAY' AND ltrim(rtrim(s1.REF_ID)) = ? and s1.sign_status = 'S' " +
					"and s1.sign_date = (select max(s2.sign_date) from obj_sign_trans s2 " +
					"where s2.ref_id = s1.ref_id and s2.sign_status = s1.sign_status)) s3 on s3.ref_id = s.ref_id " +
					"where ltrim(rtrim(s.REF_SER)) = 'M-PAY' AND ltrim(rtrim(s.REF_ID)) = ? and " +
					"s.sign_status = 'S' group by s3.role_code__sign";
			
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setString(2, tranId);
				rs = pstmt.executeQuery();
				if (rs.next()) 
				{
					 counter = rs.getLong("counter");
					 role_code__sign = rs.getString("role_code__sign");
				}
				System.out.println("counter = " + counter + "role_code__sign = " + role_code__sign );

				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if(counter == 2)
				{
					retString = role_code__sign;
				} 
				else 
				{
					retString = "NA";
				}
			}
			else 
			{

				int resultCnt = 0;
				String signStatus = "", userCode = "", processInfo[] = null, activityId = "", roleCode = "";
						
				if("J2".equalsIgnoreCase(keyFlag)) 
				{
					activityId = "J2_SIGN"; roleCode = "RLCDJ2F";
					
				}
				else if("J2L5K".equalsIgnoreCase(keyFlag)) 
				{
					activityId = "J2L5K_SIGN"; roleCode = "RLCDJ2L5KF";
					
				}
				else if("J2L1".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L1_SIGN"; roleCode = "RLCDJ2L1F";
					
				}
				else if("J2L2".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L2_SIGN"; roleCode = "RLCDJ2L2F";
					
				}
				else if("J2L5".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L5_SIGN"; roleCode = "RLCDJ2L5F";
					
				}
				else if("J21".equalsIgnoreCase(keyFlag)) 
				{
					activityId = "J21_SIGN"; roleCode = "RLCDJ2S";
				}
				else if("J2L5K1".equalsIgnoreCase(keyFlag)) 
				{
					activityId = "J2L5K1_SIGN"; roleCode = "RLCDJ2L5KS";
				}
				else if("J2L11".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L11_SIGN"; roleCode = "RLCDJ2L1S";
				}
				else if("J2L21".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L21_SIGN"; roleCode = "RLCDJ2L2S";
				}
				else if("J2L51".equalsIgnoreCase(keyFlag))
				{
					activityId = "J2L51_SIGN"; roleCode = "RLCDJ2L5S";
				}
				else if("HOD".equalsIgnoreCase(keyFlag))
				{
					activityId = "HOD_SIGN"; roleCode = "ESCHOD";
				}
				else 
				{
					activityId = "HODS_SIGN"; roleCode = "ESCHODHODS";
				}
				
				System.out.println("activityId["+activityId+"], roleCode["+roleCode+"]");
				sql = "select SIGN_STATUS from OBJ_SIGN_TRANS "
						+ "WHERE ltrim(rtrim(REF_SER)) = 'M-PAY' 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)) = 'M-PAY' AND ltrim(rtrim(REF_ID)) = ? AND ltrim(rtrim(ROLE_CODE__SIGN)) = ? "
							+ "AND SIGN_STATUS = 'U' AND LINE_NO = ( "
							+ "SELECT MAX(LINE_NO) LINE_NO "
							+ "FROM OBJ_SIGN_TRANS "
							+ "WHERE ltrim(rtrim(REF_SER)) = 'M-PAY' 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);
					pstmt.setString(5, tranId);
					pstmt.setString(6, 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)) = 'M-PAY' AND REF_ID = ? AND PROCESS_ID = ? AND INSTANCE_ID = ? AND ACTIVITY_ID = ? "
							+ "AND PROCESS_STATUS != 3 AND SEQ_ID = ( "
							+ "SELECT MAX(SEQ_ID) SEQ_ID "
							+ "FROM WF_PRC_STATUS "
							+ "WHERE ltrim(rtrim(REF_SER)) = 'M-PAY' 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);
					pstmt.setString(5, tranId);
					pstmt.setString(6, processInfo[0]);
					pstmt.setString(7, processInfo[1]);
					pstmt.setString(8, 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 Finally");
				}
			}
		}

		return retString;
	}
}
