package ibase.dashboard.ejb;

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

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class SalesPersonSales
 */
@Stateless
public class SalesPerformanceTwo extends ValidatorEJB 
{
	/**
	 * Default constructor.
	 */
	private JSONObject jsonMetaData = null;

	public SalesPerformanceTwo()
	{

	}
	/*** 1 - This method will contains final data for rendering Top Brands graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getMnthlyTopBrandGraphData(String dataSourceName, String month, String topNos) throws RemoteException, ITMException
	{
        
		JSONObject rawDataMthlySalesJson = new JSONObject();
		JSONObject rowData = null;
		String sql=null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		try
		{
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			if(month!=null && month.trim().length()>0)
			{
				connDriver = null;
				sql =" SELECT sr_no, brand_name, divmst.div_descr, ROUND(sale_YTD_cy/100000,2) AS sale_YTD_cy, " 
					+" ROUND(sale_YTD_ly/100000,2) AS sale_YTD_ly,(case when nvl(sale_YTD_ly,0) = 0 then 900 "
					+" else ROUND((sale_YTD_cy -sale_YTD_ly)/ sale_YTD_ly* 100,1) end) AS gr_prcnt, ROUND(hotrgt_ytd_cy/100000,2) AS hotrgt_ytd, "
					+" ROUND(trgt_ytd_cy/100000,2) AS Fldtrgt_ytd,(case when nvl(hotrgt_ytd_cy,0) = 0 then 0 "
					+" else ROUND(sale_YTD_cy/hotrgt_ytd_cy * 100,1) end ) AS ho_ach_prcnt,(case when nvl(trgt_ytd_cy,0) = 0 then 0 "
					+" else ROUND(sale_YTD_cy/trgt_ytd_cy * 100,1) end ) AS fld_ach_prcnt,totterr, terr100, ROUND(terr100/totterr*100,1) AS terr100prcnt "
					+" FROM (SELECT row_number() OVER( ORDER BY sale_YTD_cy DESC ) AS sr_no, "
					+" fin_year, item_ser, sub_grp AS brand_name, sale_YTD_cy, sale_YTD_ly, trgt_YTD_cy, hotrgt_ytd_cy, "
					+" totterr, terr100 FROM (SELECT fin_year, item_ser, sub_grp,SUM(sale_YTD_cy) AS sale_YTD_cy, SUM(sale_YTD_ly) AS sale_YTD_ly, "
					+" SUM(trgt_YTD_cy) AS trgt_YTD_cy, SUM(hotrgt_ytd_cy) AS hotrgt_ytd_cy,SUM(terrcnt) AS totterr, SUM(terr100cnt) AS terr100 "
					+" FROM(SELECT dwh.fin_year , dwh.item_ser,dwh.grp_nm AS sub_grp,dwh.terr_code,SUM(sale_YTD_cy) AS sale_YTD_cy, " 
					+" SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy) AS trgt_YTD_cy,ROUND(SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100),2) AS hotrgt_ytd_cy, " 
					+" 1 AS terrcnt,(CASE when SUM(trgt_YTD_cy) =0 then 1 WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy * " 
	                +" (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN 1 ELSE 0 END ) AS terr100cnt "
	                +" FROM (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group AS sub_grp, "
	                +" sales_item.item_sub_group_descr AS grp_nm,SM_SALES_SUMM_WIDE.terr_code AS terr_code, "
	                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, " 
	                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) " 
	                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, "
	                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) "
	                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy "
	                +" FROM SM_SALES_SUMM_WIDE, sales_item "
	                +" WHERE trim(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+" AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code "
	                +" AND sales_item.item_sub_group NOT IN ('CU','BJ') "
	                +" GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group, "
	                +" sales_item.item_sub_group_descr, SM_SALES_SUMM_WIDE.terr_code ) dwh, "
	                +" SM_SALES_BRAND_MST brnd "
	                +" WHERE brnd.brand_code(+) = dwh.sub_grp "
	                +" AND brnd.div_code(+) = dwh.item_ser "
	                +" AND brnd.fin_year(+) = dwh.fin_year "
	                +" and dwh.sale_ytd_cy+ dwh.trgt_YTD_cy <>0 "
	                +" GROUP BY dwh.fin_year, dwh.item_ser, dwh.grp_nm,dwh.terr_code "
	                +" )GROUP BY fin_year, item_ser, sub_grp) ) dwh, "
	                +" SM_SALES_DIV_MST divmst "
	                +" WHERE dwh.item_ser= divmst.div_code "
	                +" AND dwh.fin_year= divmst.fin_year "
	                +" AND dwh.sr_no <=:No_of_TOP_Brands "
	                +" order by sr_no";
		
				
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					int count = 0;
					
					while (rs.next())
					{
						rowData = new JSONObject();
						rowData.put("brand_name", rs.getString("BRAND_NAME"));
						rowData.put("sales_ytd_cy", rs.getString("SALES_YTD_CY"));
						rowData.put("gr_prcnt", rs.getString("GR_PRCNT"));
						rowData.put("ho_ach_prcnt", rs.getString("HO_ACH_PRCNT"));
						rawDataMthlySalesJson.put(count, rowData);
						count++;
					}
				}
				System.out.println("fetch Data for Mnthly Top Brand Graph Data rawjson====" + rawDataMthlySalesJson.toString());

		} 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 :SalesPersonSales:getMnthlyTopBrandGraphData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMthlySalesJson;
	}
    
	/*** 2 - This method will contains final data for rendering Akuna graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getAkunaTopBrandGraphData(String dataSourceName,String month ,String topNos) throws RemoteException, ITMException
	{
       
		JSONObject rawDataYTDSalesJson = new JSONObject();
		JSONObject rowData = null;
		String sql=null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
		    sql = " SELECT sr_no, brand_name, divmst.div_descr, ROUND(sale_YTD_cy/100000,2) AS sale_YTD_cy, " 
				+" ROUND(sale_YTD_ly/100000,2) AS sale_YTD_ly,(case when nvl(sale_YTD_ly,0) = 0 then 900 "
				+" else ROUND((sale_YTD_cy -sale_YTD_ly)/ sale_YTD_ly* 100,1) end) AS gr_prcnt, ROUND(hotrgt_ytd_cy/100000,2) AS hotrgt_ytd, "
				+" ROUND(trgt_ytd_cy/100000,2) AS Fldtrgt_ytd,(case when nvl(hotrgt_ytd_cy,0) = 0 then 0 "
				+" else ROUND(sale_YTD_cy/hotrgt_ytd_cy * 100,1) end ) AS ho_ach_prcnt,(case when nvl(trgt_ytd_cy,0) = 0 then 0 "
				+" else ROUND(sale_YTD_cy/trgt_ytd_cy * 100,1) end ) AS fld_ach_prcnt,totterr, terr100, ROUND(terr100/totterr*100,1) AS terr100prcnt "
				+" FROM (SELECT row_number() OVER( ORDER BY sale_YTD_cy DESC ) AS sr_no, "
				+" fin_year, item_ser, sub_grp AS brand_name, sale_YTD_cy, sale_YTD_ly, trgt_YTD_cy, hotrgt_ytd_cy, "
				+" totterr, terr100 FROM (SELECT fin_year, item_ser, sub_grp,SUM(sale_YTD_cy) AS sale_YTD_cy, SUM(sale_YTD_ly) AS sale_YTD_ly, "
				+" SUM(trgt_YTD_cy) AS trgt_YTD_cy, SUM(hotrgt_ytd_cy) AS hotrgt_ytd_cy,SUM(terrcnt) AS totterr, SUM(terr100cnt) AS terr100 "
				+" FROM(SELECT dwh.fin_year , dwh.item_ser,dwh.grp_nm AS sub_grp,dwh.terr_code,SUM(sale_YTD_cy) AS sale_YTD_cy, " 
				+" SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy) AS trgt_YTD_cy,ROUND(SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100),2) AS hotrgt_ytd_cy, " 
				+" 1 AS terrcnt,(CASE when SUM(trgt_YTD_cy) =0 then 1 WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy * " 
                +" (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN 1 ELSE 0 END ) AS terr100cnt "
                +" FROM (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group AS sub_grp, "
                +" sales_item.item_sub_group_descr AS grp_nm,SM_SALES_SUMM_WIDE.terr_code AS terr_code, "
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, " 
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) "
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) "
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) "
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) "
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) "
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) "
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) "
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) "
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, "
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) "
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) "
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) "
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) "
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) "
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) "
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV) "
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) "
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) "
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy "
                +" FROM SM_SALES_SUMM_WIDE, sales_item "
                +" WHERE trim(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+" AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code "
                +" AND sales_item.item_sub_group NOT IN ('CU','BJ') "
                +" GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group, "
                +" sales_item.item_sub_group_descr, SM_SALES_SUMM_WIDE.terr_code ) dwh, "
                +" SM_SALES_BRAND_MST brnd "
                +" WHERE brnd.brand_code(+) = dwh.sub_grp "
                +" AND brnd.div_code(+) = dwh.item_ser "
                +" AND brnd.fin_year(+) = dwh.fin_year "
                +" and dwh.sale_ytd_cy+ dwh.trgt_YTD_cy <>0 "
                +" GROUP BY dwh.fin_year, dwh.item_ser, dwh.grp_nm,dwh.terr_code "
                +" )GROUP BY fin_year, item_ser, sub_grp) ) dwh, "
                +" SM_SALES_DIV_MST divmst "
                +" WHERE dwh.item_ser= divmst.div_code "
                +" AND dwh.fin_year= divmst.fin_year "
                +" AND dwh.sr_no <=:No_of_TOP_Brands "
                +" order by sr_no";
		
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;
			
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("brand_name", rs.getString("BRAND_NAME"));
				rowData.put("sales_ytd_cy", rs.getString("SALES_YTD_CY"));
				rowData.put("gr_prcnt", rs.getString("GR_PRCNT"));
				rowData.put("ho_ach_prcnt", rs.getString("HO_ACH_PRCNT"));
				rawDataYTDSalesJson.put(count, rowData);
				count++;
			}
			System.out.println("fetch Data for YTD Sales Graph rawjson====" + rawDataYTDSalesJson.toString());

		} 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 :SalesPersonSales:getYTDSalesGraphData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataYTDSalesJson;
	}
	
	/*** 3 - This method will contains final data for rendering Azura graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getAzuraTopBrandGraphData(String dataSourceName, String month, String topNos) throws RemoteException, ITMException
	{
       
		JSONObject rawDataMnthDivWiseSalesJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
		   sql = " SELECT sr_no, brand_name, divmst.div_descr, ROUND(sale_YTD_cy/100000,2) AS sale_YTD_cy, " 
				+" ROUND(sale_YTD_ly/100000,2) AS sale_YTD_ly,(case when nvl(sale_YTD_ly,0) = 0 then 900 "
				+" else ROUND((sale_YTD_cy -sale_YTD_ly)/ sale_YTD_ly* 100,1) end) AS gr_prcnt, ROUND(hotrgt_ytd_cy/100000,2) AS hotrgt_ytd, "
				+" ROUND(trgt_ytd_cy/100000,2) AS Fldtrgt_ytd,(case when nvl(hotrgt_ytd_cy,0) = 0 then 0 "
				+" else ROUND(sale_YTD_cy/hotrgt_ytd_cy * 100,1) end ) AS ho_ach_prcnt,(case when nvl(trgt_ytd_cy,0) = 0 then 0 "
				+" else ROUND(sale_YTD_cy/trgt_ytd_cy * 100,1) end ) AS fld_ach_prcnt,totterr, terr100, ROUND(terr100/totterr*100,1) AS terr100prcnt "
				+" FROM (SELECT row_number() OVER( ORDER BY sale_YTD_cy DESC ) AS sr_no, "
				+" fin_year, item_ser, sub_grp AS brand_name, sale_YTD_cy, sale_YTD_ly, trgt_YTD_cy, hotrgt_ytd_cy, "
				+" totterr, terr100 FROM (SELECT fin_year, item_ser, sub_grp,SUM(sale_YTD_cy) AS sale_YTD_cy, SUM(sale_YTD_ly) AS sale_YTD_ly, "
				+" SUM(trgt_YTD_cy) AS trgt_YTD_cy, SUM(hotrgt_ytd_cy) AS hotrgt_ytd_cy,SUM(terrcnt) AS totterr, SUM(terr100cnt) AS terr100 "
				+" FROM(SELECT dwh.fin_year , dwh.item_ser,dwh.grp_nm AS sub_grp,dwh.terr_code,SUM(sale_YTD_cy) AS sale_YTD_cy, " 
				+" SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy) AS trgt_YTD_cy,ROUND(SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100),2) AS hotrgt_ytd_cy, " 
				+" 1 AS terrcnt,(CASE when SUM(trgt_YTD_cy) =0 then 1 WHEN ROUND(SUM( sale_YTD_cy) / SUM(trgt_YTD_cy * " 
                +" (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))/100) *100,1)>= 100 THEN 1 ELSE 0 END ) AS terr100cnt "
                +" FROM (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group AS sub_grp, "
                +" sales_item.item_sub_group_descr AS grp_nm,SM_SALES_SUMM_WIDE.terr_code AS terr_code, "
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(CY_SALE_YTD_APR) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(CY_SALE_YTD_MAY) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(CY_SALE_YTD_JUN) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(CY_SALE_YTD_SEP) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(CY_SALE_YTD_DEC) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(CY_SALE_YTD_MAR) END ) AS sale_YTD_cy, " 
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(lY_SALE_YTD_APR) " 
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(lY_SALE_YTD_MAY) "
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(lY_SALE_YTD_JUN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(lY_SALE_YTD_JUL) "
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(lY_SALE_YTD_AUG) "
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(lY_SALE_YTD_SEP) "
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(lY_SALE_YTD_OCT) "
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(lY_SALE_YTD_NOV) "
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(lY_SALE_YTD_DEC) "
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(lY_SALE_YTD_JAN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(lY_SALE_YTD_FEB) "
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(lY_SALE_YTD_MAR) END ) AS sale_YTD_ly, "
                +" (CASE WHEN SUBSTR("+month+" ,5,2) ='04' THEN SUM(FLD_TRGT_YTD_APR) "
                +" WHEN SUBSTR("+month+" ,5,2) ='05' THEN SUM(FLD_TRGT_YTD_MAY) "
                +" WHEN SUBSTR("+month+" ,5,2) ='06' THEN SUM(FLD_TRGT_YTD_JUN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='07' THEN SUM(FLD_TRGT_YTD_JUL) "
                +" WHEN SUBSTR("+month+" ,5,2) ='08' THEN SUM(FLD_TRGT_YTD_AUG) "
                +" WHEN SUBSTR("+month+" ,5,2) ='09' THEN SUM(FLD_TRGT_YTD_SEP) "
                +" WHEN SUBSTR("+month+" ,5,2) ='10' THEN SUM(FLD_TRGT_YTD_OCT) "
                +" WHEN SUBSTR("+month+" ,5,2) ='11' THEN SUM(FLD_TRGT_YTD_NOV) "
                +" WHEN SUBSTR("+month+" ,5,2) ='12' THEN SUM(FLD_TRGT_YTD_DEC) "
                +" WHEN SUBSTR("+month+" ,5,2) ='01' THEN SUM(FLD_TRGT_YTD_JAN) "
                +" WHEN SUBSTR("+month+" ,5,2) ='02' THEN SUM(FLD_TRGT_YTD_FEB) "
                +" WHEN SUBSTR("+month+" ,5,2) ='03' THEN SUM(FLD_TRGT_YTD_MAR) END ) AS trgt_YTD_cy "
                +" FROM SM_SALES_SUMM_WIDE, sales_item "
                +" WHERE trim(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+" AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code "
                +" AND sales_item.item_sub_group NOT IN ('CU','BJ') "
                +" GROUP BY fin_year ,SM_SALES_SUMM_WIDE.item_ser, sales_item.item_sub_group, "
                +" sales_item.item_sub_group_descr, SM_SALES_SUMM_WIDE.terr_code ) dwh, "
                +" SM_SALES_BRAND_MST brnd "
                +" WHERE brnd.brand_code(+) = dwh.sub_grp "
                +" AND brnd.div_code(+) = dwh.item_ser "
                +" AND brnd.fin_year(+) = dwh.fin_year "
                +" and dwh.sale_ytd_cy+ dwh.trgt_YTD_cy <>0 "
                +" GROUP BY dwh.fin_year, dwh.item_ser, dwh.grp_nm,dwh.terr_code "
                +" )GROUP BY fin_year, item_ser, sub_grp) ) dwh, "
                +" SM_SALES_DIV_MST divmst "
                +" WHERE dwh.item_ser= divmst.div_code "
                +" AND dwh.fin_year= divmst.fin_year "
                +" AND dwh.sr_no <=:No_of_TOP_Brands "
                +" order by sr_no";
		
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;
			
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("brand_name", rs.getString("BRAND_NAME"));
				rowData.put("sales_ytd_cy", rs.getString("SALES_YTD_CY"));
				rowData.put("gr_prcnt", rs.getString("GR_PRCNT"));
				rowData.put("ho_ach_prcnt", rs.getString("HO_ACH_PRCNT"));
				rawDataMnthDivWiseSalesJson.put(count, rowData);
				count++;
			}
			System.out.println("fetch Data for Azura Top Brand Graph Data rawjson====" + rawDataMnthDivWiseSalesJson.toString());

		} 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 :SalesPersonSales:getAzuraTopBrandGraphData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMnthDivWiseSalesJson;
	}
	
	

	
}
