package ibase.dashboard.scm.ejb;
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 javax.ejb.Stateless;
import org.json.simple.JSONObject;
@Stateless
public class Attrition extends ValidatorEJB implements AttritionRemote, AttritionLocal
{
		
	public Attrition()
	{}
	GenericUtility genericUtility = GenericUtility.getInstance();
	//1.This is the main method to show the parent graph 
	@SuppressWarnings("unchecked")		
	public JSONObject getAttritionDetails(String dataSourceName, String month) throws RemoteException, ITMException
	{
		
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear="";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();			
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			int count=0;
			if(month!=null && month.trim().length()>0)
			{
				System.out.println("month inside EJB Attrition.getAttritionDetail(String dataSourceName, String month) ==="+month);					
				String selectedMonth  = month.substring(4, 6);				
				if(selectedMonth.trim().equalsIgnoreCase("01") ||selectedMonth.trim().equalsIgnoreCase("02") ||selectedMonth.trim().equalsIgnoreCase("03"))
				{
			    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
					
				}
				else
				{
					finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
				}
				System.out.println("updates month and year from dcoding  =="+month+"finYear =="+finYear);
				
				sql = " SELECT div_no,fin_year,div_code,div_descr,attr_ss, ytd_attr, (CASE WHEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))>=4 THEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"' ,5,2))+9 END) AS nomn, ROUND( ytd_attr* 12 / (CASE WHEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))>=4 THEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"' ,5,2))+9 END) / attr_ss * 100 , 1) AS annul_attr FROM "
						+ " (SELECT div_no,fin_year,div_code,div_descr,attr_ss,"
						+ " (CASE WHEN SUBSTR( '"+month+"'   ,5,2)=4 THEN NVL(attr_apr,0)"
				        +"  WHEN SUBSTR( '"+month+"'   ,5,2)=5 THEN NVL(attr_apr,0)+NVL(attr_may,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=6 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)"
					    +"  WHEN SUBSTR( '"+month+"'   ,5,2)=7 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=8 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=9 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=10 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=11 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=12 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=1 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=2 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)+NVL(attr_feb,0)"
					    + " WHEN SUBSTR( '"+month+"'   ,5,2)=3 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)+NVL(attr_feb,0)+NVL(attr_mar,0) END ) AS Ytd_attr "
                        + " FROM SM_SALES_DIV_MST mst WHERE trim(mst.fin_year)= (CASE WHEN SUBSTR( '"+month+"'   ,5,2)<4 THEN SUBSTR( '"+month+"'   -100 ,1,4)||'-'||SUBSTR( '"+month+"'   ,3,2) ELSE SUBSTR( '"+month+"'   ,1,4)||'-' || SUBSTR( '"+month+"'   +100,3,2) END) AND '"+month+"'  =	'"+month+"' )"
                        +" UNION ALL"
                        +" SELECT '99' AS div_no,fin_year,'' AS div_code,'Total' AS div_descr, SUM(attr_ss) AS attr_ss, SUM(ytd_attr) AS ytd_attr, (CASE WHEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))>=4 THEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"' ,5,2))+9 END) AS nomn, ROUND(SUM( ytd_attr)* 12 / (CASE WHEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))>=4 THEN TO_NUMBER(SUBSTR('"+month+"' ,5,2))- 3 ELSE TO_NUMBER(SUBSTR('"+month+"' ,5,2))+9 END) / SUM(attr_ss) * 100 , 1) AS annul_attr FROM "
                        +" ( SELECT fin_year,attr_ss, (CASE WHEN SUBSTR( '"+month+"',5,2)=4 THEN NVL(attr_apr,0)"
				        +"  WHEN SUBSTR( '"+month+"'   ,5,2)=5 THEN NVL(attr_apr,0)+NVL(attr_may,0) "
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=6 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=7 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=8 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=9 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=10 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0) "
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=11 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0) "
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=12 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=1 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=2 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)+NVL(attr_feb,0)"
					    +" WHEN SUBSTR( '"+month+"'   ,5,2)=3 THEN NVL(attr_apr,0)+NVL(attr_may,0)+NVL(attr_jun,0)+NVL(attr_jul,0)+NVL(attr_aug,0)+NVL(attr_sep,0)+NVL(attr_oct,0)+NVL(attr_nov,0)+NVL(attr_dec,0)+NVL(attr_jan,0)+NVL(attr_feb,0)+NVL(attr_mar,0) END ) AS Ytd_attr"
				        +" FROM SM_SALES_DIV_MST mst WHERE trim(mst.fin_year)= (CASE WHEN SUBSTR( '"+month+"'   ,5,2)<4 THEN SUBSTR( '"+month+"'   -100 ,1,4)||'-'||SUBSTR( '"+month+"'   ,3,2) ELSE SUBSTR( '"+month+"'   ,1,4)||'-' || SUBSTR( '"+month+"'   +100,3,2) END) AND '"+month+"'  =	'"+month+"' ) GROUP BY fin_year,SUBSTR('"+month+"' ,5,2) ORDER BY div_no ";
								
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
								
				rowData = new JSONObject();
				rowData.put("div_code",       checkNull(rs.getString("div_code")));
				rowData.put("div_descr",      checkNull(rs.getString("div_descr")));
				rowData.put("attr_ss",        rs.getString("attr_ss"));
				rowData.put("ytd_attr",       rs.getString("ytd_attr"));
				rowData.put("nomn",           rs.getString("nomn"));
				rowData.put("annul_attr",     rs.getString("annul_attr"));
				rawDataJson.put(count, rowData);
				count++;
				

			}
		}

		} catch (Exception e)
		{
			e.printStackTrace();
			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 in :Attrition.getAttritionDetail(String dataSourceName, String month):" + d.getMessage());
				throw new ITMException(d);
			}
		}
	
		return rawDataJson;
	}
	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		
}
