package ibase.dashboard.sfa.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;

import javax.ejb.Stateless;

/**
 * Session Bean implementation class ImpDocMissed
 */
@Stateless
public class ImpDocMissed extends ValidatorEJB implements ImpDocMissedRemote, ImpDocMissedLocal
{
	/**
	 * Default constructor.
	 */
	String SalePersn = "";

	public String getSalePersn()
	{
		return SalePersn;
	}

	public void setSalePersn(String salePersn)
	{
		SalePersn = salePersn;
	}

	public ImpDocMissed()
	{
		// TODO Auto-generated constructor stub
	}

	@Override
	public String getData() throws RemoteException, ITMException
	{
		return "";
	}

	@Override
	public String getData(String loginCode) throws RemoteException, ITMException
	{
		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;

			//sql (old Sql)= "SELECT initcap(SALES_PERS.SP_NAME) AS SALESPERSON_NAME, SYSDATE AS CURRENT_DATE, FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,SYSDATE,STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) AS ACTUAL_VISITS, FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) AS ACTUAL_CHECK, (CASE WHEN (FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,SYSDATE,STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE)) < FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) THEN initcap(STRG_CUSTOMER.FIRST_NAME||' '||STRG_CUSTOMER.LAST_NAME ) ELSE ' ' END) AS DOCTORS_NAME, FN_R_DAYS_SINCE_NOT_MET(STRG_SERIES.SALES_PERS,SYSDATE,STRG_SERIES.SC_CODE) AS DAYS_SINCE_NOT_MET_n, (case when (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) - FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) <0 then 0 else (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) -FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) end ) - (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END) as VISITS_MISSED, STRG_SERIES.NO_VISIT - FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) + (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END) AS Pending_Visits_for_the_Month FROM STRG_SERIES, SALES_PERS, STRG_CUSTOMER WHERE STRG_SERIES.SALES_PERS=SALES_PERS.SALES_PERS AND STRG_SERIES.SC_CODE=STRG_CUSTOMER.SC_CODE AND STRG_SERIES.CUST_TYPE = 'D' AND STRG_SERIES.STATUS = 'Y' AND STRG_SERIES.CLASS_CODE = 'A' AND STRG_SERIES.SALES_PERS IN ( select emp_code from employee start with emp_code = (select emp_code from users where code= ?) connect by prior emp_code = report_to ) and (case when (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) - FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) <0 then 0 else (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) -FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) end ) - (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END)<>0 ";
			
			sql = "SELECT initcap(SALES_PERS.SP_NAME) AS SALESPERSON_NAME, SYSDATE AS CURRENT_DATE, FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) AS ACTUAL_VISITS, FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) AS ACTUAL_CHECK, initcap(STRG_CUSTOMER.FIRST_NAME||' '||STRG_CUSTOMER.LAST_NAME) AS DOCTORS_NAME, FN_R_DAYS_SINCE_NOT_MET(STRG_SERIES.SALES_PERS,SYSDATE,STRG_SERIES.SC_CODE) AS DAYS_SINCE_NOT_MET_n, (case when (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) - FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) <0 then 0 else (FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) -FN_R_CHECK_PRINT2(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE) ) end ) - (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END) as VISITS_MISSED, STRG_SERIES.NO_VISIT - FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) + (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END) AS Pending_Visits_for_the_Month FROM STRG_SERIES, SALES_PERS, STRG_CUSTOMER WHERE STRG_SERIES.SALES_PERS=SALES_PERS.SALES_PERS AND STRG_SERIES.SC_CODE=STRG_CUSTOMER.SC_CODE AND STRG_SERIES.CUST_TYPE = 'D' AND STRG_SERIES.STATUS = 'Y' AND STRG_SERIES.CLASS_CODE = 'A' AND STRG_SERIES.SALES_PERS IN ( select emp_code from employee start with emp_code = (select emp_code from users where code=? ) connect by prior emp_code = report_to ) and ( STRG_SERIES.NO_VISIT - FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT) + (CASE WHEN FN_R_CHECK_PRINT2_NEW(STRG_SERIES.SALES_PERS,To_Date(SYSDATE),STRG_SERIES.NO_VISIT,STRG_SERIES.SC_CODE,FN_R_CHECK_COUNT(SYSDATE,STRG_SERIES.NO_VISIT)) = 0 THEN 1 ELSE 0 END) )<>0 ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, loginCode);
			rs = pstmt.executeQuery();
			xmlData = new StringBuffer("<?xml version=\"1.0\"?><Root>");
			while (rs.next())
			{
				this.setSalePersn(rs.getString("SALESPERSON_NAME"));
				xmlData.append("<Detail>");
				xmlData.append("<doctors_name >").append("<![CDATA[" + checkNull(rs.getString("DOCTORS_NAME")) + "]]>").append("</doctors_name>");
				xmlData.append("<days_since_not_met>").append("<![CDATA[" + checkNull(rs.getString("DAYS_SINCE_NOT_MET_n")) + "]]>").append("</days_since_not_met>");
				xmlData.append("<visits_missed>").append("<![CDATA[" + checkNull(rs.getString("VISITS_MISSED")) + "]]>").append("</visits_missed>");
				xmlData.append("<pending_visits>").append("<![CDATA[" + checkNull(rs.getString("Pending_Visits_for_the_Month")) + "]]>").append("</pending_visits>");
				xmlData.append("</Detail>");
			}
			xmlData.append("</Root>");
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
		} catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception : ImpDocMissed:" + 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;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				System.out.println("Exception :ImpDocMissed:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return xmlData.toString();
	}

	private String checkNull(String input)
	{
		if (input == null)
		{
			input = "";
		} else
		{
			input = input.trim();
		}
		return input;
	}
}
