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.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class SalesPersonSales
 */
@Stateless
public class SalesPerformance extends ValidatorEJB implements SalesPerformanceRemote, SalesPerformanceLocal
{
	/**
	 * Default constructor.
	 */
	private JSONObject jsonMetaData = null;
	private JSONObject jsonData = null;

	public SalesPerformance()
	{

	}
	
	public JSONObject getGridData(String dataSourceName, String year, String month) throws RemoteException, ITMException
	{
        
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		List<String> dataList = new ArrayList<String>();
		String sql = "";
		sql = " SELECT a.divGRP,b.div_no||'. '||b.div_descr AS DIVISION, " 
			+ " ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, " 
			+ " ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES,  "
			+ " (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_GR, " 
			+ " ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, " 
			+ " ROUND(a.trgt_mth_cy/100000,2) AS MTH_FLD_TRGT, "
			+ " (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_HO_ACH, " 
			+ " (CASE WHEN a.trgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.trgt_mth_cy*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_FLD_ACH," 
			+ " ROUND(a.sale_ytd_cy/100000,2) AS YTD_CY_SALES, " 
			+ " ROUND(a.sale_ytd_ly/100000,2) AS YTD_LY_SALES,  " 
			+ " (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_GR, " 
			+ " ROUND(a.hotrgt_ytd_cy/100000,2) AS YTD_HO_TRGT, " 
			+ " ROUND(a.trgt_ytd_cy/100000,2) AS YTD_FLD_TRGT, " 
			+ " (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1)||'%'    " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_HO_ACH, " 
			+ " (CASE WHEN a.trgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.trgt_ytd_cy*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_FLD_ACH,  " 
			+ " ROUND(a.sale_Qrt1_cy/100000,2) AS Q1," 
			+ " ROUND(a.sale_Qrt2_cy/100000,2) AS Q2,  " 
			+ " ROUND(a.sale_Qrt3_cy/100000,2) AS Q3,  " 
			+ " ROUND(a.sale_Qrt4_cy/100000,2) AS Q4,  " 
			+ " ROUND(a.sale_ytd_cy/100000/    " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR( '"+month+"',5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " 
			+ " ,2) AS PMPM,  " + " b.pmpm_ly AS PMPM_LY, " 
			+ " ROUND(a.sale_ytd_cy/100000/ " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) - b.pmpm_ly  AS INC_PMPM, " 
			+ " ROUND((a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " 
			+ " ROUND(a.sale_ytd_cy/ " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 " 
			+ " ELSE SUBSTR('"+month+"',5,2)-3 END) " 
			+ " /100000,2) AS AVG_MTHLY,  " 
			+ " ( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) <3 THEN ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ "
			+ " (12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) " + " ELSE ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " 
			+ " (12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) " 
			+ " END ) AS REQD_MTHLY, " 
			+ " ROUND(a.hotrgt_annual_cy/100000,2) AS ANNUAL_TRGT, " 
			+ " ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD," 
			+ " (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' " 
			+ " ELSE ROUND(9.99*100,1)||'%' END) AS RETU_YTD_PRCT " 
			+ " from " 
			+ " ( SELECT brnd.div_code as divGRP,dwh.fin_year , dwh.item_ser, " 
			+ " SUM(sale_mth_cy) AS sale_mth_cy, " 
			+ " SUM(sale_mth_ly) AS sale_mth_ly, " 
			+ " SUM(trgt_mth_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,  " 
			+ " SUM(trgt_mth_cy) AS trgt_mth_cy, " 
			+ " SUM(sale_YTD_cy) AS sale_YTD_cy, " 
			+ " SUM(sale_YTD_ly) AS sale_YTD_ly, " 
			+ " SUM(trgt_YTD_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, " 
			+ " SUM(trgt_YTD_cy) AS trgt_YTD_cy,  " 
			+ " SUM(sale_Qrt1_cy) AS sale_Qrt1_cy, " 
			+ " SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " 
			+ " SUM(sale_Qrt3_cy) AS sale_Qrt3_cy, " 
			+ " SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " 
			+ " SUM(trgt_annual_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_annual_cy,  "
			+ " SUM(retu_ytd_cy) AS retu_ytd_cy " 
			+ "  from " 
			+ " (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,  " 
			+ " sales_item.item_sub_group AS sub_grp, " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) "
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy,  " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP)" 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, " 
			+ " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) " 
			+ " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, " 
			+ " (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, " + " SUM(CY_SALE_YTD_JUN) AS sale_Qrt1_cy, " + " SUM(CY_SALE_YTD_SEP)-SUM(CY_SALE_YTD_JUN) AS sale_Qrt2_cy,  " + " SUM(CY_SALE_YTD_DEC)-SUM(CY_SALE_YTD_SEP) AS sale_Qrt3_cy, " + " SUM(CY_SALE_YTD_MAR)-SUM(CY_SALE_YTD_DEC) AS sale_Qrt4_cy,  " + " SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_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 " + " ) 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    " + " GROUP BY dwh.fin_year, dwh.item_ser,brnd.div_code " + " ) a, " + " SM_SALES_DIV_MST b " + " WHERE trim(a.item_ser)=trim (b.div_code)  " + " and B.FIN_YEAR=a.FIN_YEAR "
		// +" and b.div_group=a.divGRP "
		// +" --and TRIM(a.ITEM_SER)=TRIM(C.TABLE_NO) "
		// +" --and C.FINYR = SUBSTR(a.FIN_YEAR,3,2)||SUBSTR(a.FIN_YEAR,6,2) "
		+ "  ORDER BY division ";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;
			ArrayList arr = new ArrayList();
			arr.add("CVN");
			arr.add("CVN");
			int count1 = 0;
			while (rs.next())
			{
				count1++;
				rowData = new JSONObject();
				dataList = new ArrayList<String>();
				rowData.put("division_grp", checkNull(rs.getString("divGRP")));
				rowData.put("item_ser",    checkNull(rs.getString("DIVISION")));
				dataList.add(rs.getString("MTH_CY_SALES"));
				dataList.add(rs.getString("MTH_LY_SALES"));
				dataList.add(rs.getString("MTH_GR"));
				dataList.add(rs.getString("MTH_HO_TRGT"));
				dataList.add(rs.getString("MTH_FLD_TRGT"));
				dataList.add(rs.getString("MTH_HO_ACH"));
				dataList.add(rs.getString("MTH_FLD_ACH"));
				rowData.put("month", dataList);
				dataList = new ArrayList<String>();
				dataList.add(rs.getString("YTD_CY_SALES"));
				dataList.add(rs.getString("YTD_LY_SALES"));
				dataList.add(rs.getString("YTD_GR"));
				dataList.add(rs.getString("YTD_HO_TRGT"));
				dataList.add(rs.getString("YTD_FLD_TRGT"));
				dataList.add(rs.getString("YTD_HO_ACH"));
				dataList.add(rs.getString("YTD_FLD_ACH"));
				rowData.put("ytd1", dataList);
				dataList = new ArrayList<String>();
				dataList.add(rs.getString("Q1"));
				dataList.add(rs.getString("Q2"));
				dataList.add(rs.getString("Q3"));
				dataList.add(rs.getString("Q4"));
				rowData.put("sales", dataList);

				dataList = new ArrayList<String>();
				dataList.add(rs.getString("Q4"));
				dataList.add(rs.getString("PMPM"));
				dataList.add(rs.getString("PMPM_LY"));
				dataList.add(rs.getString("INC_PMPM"));
				dataList.add(rs.getString("YTD_ACH_EXTRA"));
				rowData.put("ytd2", dataList);
				rowData.put("avg_month_sal", rs.getString("AVG_MTHLY"));
				rowData.put("req_month_sal", rs.getString("REQD_MTHLY"));
				rowData.put("anu_ho_tar", rs.getString("ANNUAL_TRGT"));
				rowData.put("sal_ret_ytd", rs.getString("RETU_YTD"));
				rowData.put("sal_ret_ytd_per", rs.getString("RETU_YTD_PRCT"));

				rawDataJson.put(count, rowData);
				count++;
			}
			System.out.println("fetch Data for grid rawjson====" + rawDataJson.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:getGridData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataJson;
	}

	/*** 2 - This method will contains final data for rendering Sales graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getSalesGraphData(String dataSourceName, String finYear, String month) throws RemoteException, ITMException
	{
		
		System.out.println("Financial Yeay ====" + finYear+"month====="+month);
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		ArrayList<String> salesList = new ArrayList<String>();
		ArrayList<String> hoList = new ArrayList<String>();
		ArrayList<String> grList = new ArrayList<String>();
		ArrayList<String> monthNameList = null;
		ArrayList<String> keyList = new ArrayList<String>();
		ArrayList<String> dataList = null;
		Map<String, ArrayList<String>> hashMap = new HashMap<String, ArrayList<String>>();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			if(finYear!=null && finYear.trim().length()>0)
			{
			sql = " SELECT TRAN, APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,JAN,FEB,MAR " + " FROM (SELECT '1.SALES' AS TRAN, ROUND(SUM(CY_SALE_MTH_APR)/10000000,2) AS APR, " + " ROUND(SUM(CY_SALE_MTH_MAY)/10000000,2) AS MAY,ROUND(SUM(CY_SALE_MTH_JUN)/10000000,2) AS JUN," + " ROUND(SUM(CY_SALE_MTH_JUL)/10000000,2) AS JUL,ROUND(SUM(CY_SALE_MTH_AUG)/10000000,2) AS AUG," + " ROUND(SUM(CY_SALE_MTH_SEP)/10000000,2) AS SEP,ROUND(SUM(CY_SALE_MTH_OCT)/10000000,2) AS OCT," + " ROUND(SUM(CY_SALE_MTH_NOV)/10000000,2) AS NOV,ROUND(SUM(CY_SALE_MTH_DEC)/10000000,2) AS DEC," + " ROUND(SUM(CY_SALE_MTH_JAN)/10000000,2) AS JAN,ROUND(SUM(CY_SALE_MTH_FEB)/10000000,2) AS FEB, " + " ROUND(SUM(CY_SALE_MTH_MAR)/10000000,2) AS MAR FROM SM_SALES_SUMM_WIDE, SALES_ITEM, " + " SM_SALES_BRAND_MST BRND WHERE TRIM(SM_SALES_SUMM_WIDE.FIN_YEAR)= '"+finYear+"' " + " AND SM_SALES_SUMM_WIDE.ITEM_CODE= SALES_ITEM.ITEM_CODE AND BRND.BRAND_CODE(+) = SALES_ITEM.ITEM_SUB_GROUP " + " AND BRND.DIV_CODE(+) = SALES_ITEM.ITEM_SER AND BRND.FIN_YEAR(+) = '"+finYear+"' " + " AND SALES_ITEM.ITEM_SUB_GROUP NOT IN ('CU','BJ')	UNION ALL " + " SELECT '2.HO ACH%' AS TRAN,(CASE WHEN SUM(FLD_TRGT_MTH_APR) <>0 THEN " + " ROUND( SUM(CY_SALE_MTH_APR)/ SUM(FLD_TRGT_MTH_APR * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100)* 100 ,1) " + " ELSE ROUND(0*100,1) END) AS APR,(CASE WHEN SUM(FLD_TRGT_MTH_MAY) <>0 THEN ROUND( SUM(CY_SALE_MTH_MAY) " + " / SUM(FLD_TRGT_MTH_MAY * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) ELSE ROUND(0*100,1) END) AS MAY, " + " (CASE WHEN SUM(FLD_TRGT_MTH_JUN) <>0 THEN ROUND( SUM(CY_SALE_MTH_JUN)/ SUM(FLD_TRGT_MTH_JUN * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) " + " * 100 ,1) ELSE ROUND(0*100,1) END) AS JUN,(CASE WHEN SUM(FLD_TRGT_MTH_JUL) <>0 THEN ROUND( SUM(CY_SALE_MTH_JUL) " + " / SUM(FLD_TRGT_MTH_JUL * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) ELSE ROUND(0*100,1) END) AS JUL, " + " (CASE WHEN SUM(FLD_TRGT_MTH_AUG) <>0 THEN ROUND( SUM(CY_SALE_MTH_AUG)/ SUM(FLD_TRGT_MTH_AUG * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) " + " ELSE ROUND(0*100,1) END) AS AUG,(CASE WHEN SUM(FLD_TRGT_MTH_SEP) <>0 THEN ROUND( SUM(CY_SALE_MTH_SEP) " + " / SUM(FLD_TRGT_MTH_SEP * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100)  * 100 ,1) " + " ELSE ROUND(0*100,1) END) AS SEP,(CASE WHEN SUM(FLD_TRGT_MTH_OCT) <>0 THEN ROUND( SUM(CY_SALE_MTH_OCT) " + " / SUM(FLD_TRGT_MTH_OCT * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) " + " ELSE ROUND(0*100,1) END) AS OCT,(CASE WHEN SUM(FLD_TRGT_MTH_NOV) <>0 THEN ROUND( SUM(CY_SALE_MTH_NOV) " + " / SUM(FLD_TRGT_MTH_NOV * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) ELSE ROUND(0*100,1) END) AS NOV, " + " (CASE WHEN SUM(FLD_TRGT_MTH_DEC) <>0 THEN ROUND( SUM(CY_SALE_MTH_DEC)/ SUM(FLD_TRGT_MTH_DEC * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) " + " * 100 ,1) ELSE ROUND(0*100,1) END) AS DEC,(CASE WHEN SUM(FLD_TRGT_MTH_JAN) <>0 THEN ROUND( SUM(CY_SALE_MTH_JAN) " + " / SUM(FLD_TRGT_MTH_JAN * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) ELSE ROUND(0*100,1) END) AS JAN," + " (CASE WHEN SUM(FLD_TRGT_MTH_FEB) <>0 THEN ROUND( SUM(CY_SALE_MTH_FEB)/ SUM(FLD_TRGT_MTH_FEB * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) " + " ELSE ROUND(0*100,1) END) AS FEB,(CASE WHEN SUM(FLD_TRGT_MTH_MAR) <>0 THEN ROUND( SUM(CY_SALE_MTH_MAR) " + " / SUM(FLD_TRGT_MTH_MAR * (100+ NVL(BRND.HO_TRGT_INCR_PRCNT,0)) /100) * 100 ,1) ELSE ROUND(0*100,1) END) AS MAR " + " FROM SM_SALES_SUMM_WIDE, SALES_ITEM, SM_SALES_BRAND_MST BRND WHERE TRIM(SM_SALES_SUMM_WIDE.FIN_YEAR)= '"+finYear+"' " + " AND SM_SALES_SUMM_WIDE.ITEM_CODE= SALES_ITEM.ITEM_CODE AND BRND.BRAND_CODE(+) = SALES_ITEM.ITEM_SUB_GROUP " + " AND BRND.DIV_CODE(+) = SALES_ITEM.ITEM_SER AND BRND.FIN_YEAR(+) = '"+finYear+"' AND SALES_ITEM.ITEM_SUB_GROUP NOT IN ('CU','BJ') " + " UNION ALL SELECT '3.GR %' AS TRAN,(CASE WHEN SUM(LY_SALE_MTH_APR) <>0  AND SUM(CY_SALE_MTH_APR)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_APR) -SUM(LY_SALE_MTH_APR)) / SUM(LY_SALE_MTH_APR) *100 ,1) " + " ELSE ROUND(0*100,1) END) AS APR,(CASE WHEN SUM(LY_SALE_MTH_MAY) <>0 AND SUM(CY_SALE_MTH_MAY)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_MAY) -SUM(LY_SALE_MTH_MAY))/ SUM(LY_SALE_MTH_MAY) *100 ,1) ELSE ROUND(0*100,1) END) AS MAY, " + " (CASE WHEN SUM(LY_SALE_MTH_JUN) <>0 AND SUM(CY_SALE_MTH_JUN)<> 0 THEN ROUND( (SUM(CY_SALE_MTH_JUN) -SUM(LY_SALE_MTH_JUN)) " + " / SUM(LY_SALE_MTH_JUN) *100 ,1) ELSE ROUND(0*100,1) END) AS JUN,(CASE WHEN SUM(LY_SALE_MTH_JUL) <>0 AND SUM(CY_SALE_MTH_JUL)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_JUL) -SUM(LY_SALE_MTH_JUL)) / SUM(LY_SALE_MTH_JUL) *100 ,1) ELSE ROUND(0*100,1) END) AS JUL, " + " (CASE WHEN SUM(LY_SALE_MTH_AUG) <>0 AND SUM(CY_SALE_MTH_AUG)<> 0 THEN ROUND( (SUM(CY_SALE_MTH_AUG) -SUM(LY_SALE_MTH_AUG)) " + " / SUM(LY_SALE_MTH_AUG) *100 ,1) ELSE ROUND(0*100,1) END) AS AUG,(CASE WHEN SUM(LY_SALE_MTH_SEP) <>0 AND SUM(CY_SALE_MTH_SEP)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_SEP) -SUM(LY_SALE_MTH_SEP)) / SUM(LY_SALE_MTH_SEP) *100 ,1) ELSE ROUND(0*100,1) END) AS SEP, " + " (CASE WHEN SUM(LY_SALE_MTH_OCT) <>0 AND SUM(CY_SALE_MTH_OCT)<> 0 THEN ROUND( (SUM(CY_SALE_MTH_OCT) -SUM(LY_SALE_MTH_OCT)) " + " / SUM(LY_SALE_MTH_OCT) *100 ,1)ELSE ROUND(0*100,1) END) AS OCT,(CASE WHEN SUM(LY_SALE_MTH_NOV) <>0 AND SUM(CY_SALE_MTH_NOV)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_NOV) -SUM(LY_SALE_MTH_NOV))/ SUM(LY_SALE_MTH_NOV) *100 ,1) ELSE ROUND(0*100,1) END) AS NOV, " + " (CASE WHEN SUM(LY_SALE_MTH_DEC) <>0 AND SUM(CY_SALE_MTH_DEC)<> 0 THEN ROUND( (SUM(CY_SALE_MTH_DEC) -SUM(LY_SALE_MTH_DEC)) " + " / SUM(LY_SALE_MTH_DEC) *100 ,1) ELSE ROUND(0*100,1) END) AS DEC,(CASE WHEN SUM(LY_SALE_MTH_JAN) <>0 AND SUM(CY_SALE_MTH_JAN)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_JAN) -SUM(LY_SALE_MTH_JAN)) / SUM(LY_SALE_MTH_JAN) *100 ,1) ELSE ROUND(0*100,1) END) AS JAN, " + " (CASE WHEN SUM(LY_SALE_MTH_FEB) <>0 AND SUM(CY_SALE_MTH_FEB)<> 0 THEN ROUND( (SUM(CY_SALE_MTH_FEB) -SUM(LY_SALE_MTH_FEB)) " + " / SUM(LY_SALE_MTH_FEB) *100 ,1) ELSE ROUND(0*100,1) END) AS FEB,(CASE WHEN SUM(LY_SALE_MTH_MAR) <>0 AND SUM(CY_SALE_MTH_MAR)<> 0 THEN " + " ROUND( (SUM(CY_SALE_MTH_MAR) -SUM(LY_SALE_MTH_MAR))/ SUM(LY_SALE_MTH_MAR) *100 ,1) ELSE ROUND(0*100,1) END) AS MAR " + " FROM SM_SALES_SUMM_WIDE, SALES_ITEM, SM_SALES_BRAND_MST BRND WHERE TRIM(SM_SALES_SUMM_WIDE.FIN_YEAR)= '"+finYear+"' " + " AND SM_SALES_SUMM_WIDE.ITEM_CODE= SALES_ITEM.ITEM_CODE AND BRND.BRAND_CODE(+) = SALES_ITEM.ITEM_SUB_GROUP " + " AND BRND.DIV_CODE(+) = SALES_ITEM.ITEM_SER AND BRND.FIN_YEAR(+) = '"+finYear+"' AND SALES_ITEM.ITEM_SUB_GROUP NOT IN ('CU','BJ') " + " )WHERE '"+finYear+"' = '"+finYear+"'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				dataList = new ArrayList<String>();
				String tran =  checkNull(rs.getString("tran"));
				keyList.add(tran);
				dataList.add(checkNull(rs.getString("APR")));
				dataList.add(checkNull(rs.getString("MAY")));
				dataList.add(checkNull(rs.getString("JUN")));
				dataList.add(checkNull(rs.getString("JUL")));
				dataList.add(checkNull(rs.getString("AUG")));
				dataList.add(checkNull(rs.getString("SEP")));
				dataList.add(checkNull(rs.getString("OCT")));
				dataList.add(checkNull(rs.getString("NOV")));
				dataList.add(checkNull(rs.getString("DEC")));
				dataList.add(checkNull(rs.getString("JAN")));
				dataList.add(checkNull(rs.getString("FEB")));
				dataList.add(checkNull(rs.getString("MAR")));
				hashMap.put(tran, dataList);
				

			}
		}

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

		if (keyList.size()== 3)
		{
			System.out.println("keyList.toString() =" + keyList.toString());
			System.out.println("keyList.size() =" + keyList.size());
			salesList = hashMap.get(keyList.get(0));
			hoList = hashMap.get(keyList.get(1));
			grList = hashMap.get(keyList.get(2));

			int count = 0;
			monthNameList=		this.getMonthList();
			for (int i = 0; i < salesList.size(); i++)
			{
				rowData = new JSONObject();
				rowData.put("month", monthNameList.get(i));
				rowData.put("sales", salesList.get(i));
				rowData.put("ho_per", hoList.get(i));
				rowData.put("gr_per", grList.get(i));
				rawDataJson.put(count, rowData);
				count++;
			}
		}
		System.out.println("rawDataJson is =" + rawDataJson);

		return rawDataJson;
	}

	/*** End of method getSalesGraphData() ***/

	/*** 3 - This method will contains final data for rendering Profit graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getProfitGraphData(String dataSourceName, String finYear) throws RemoteException, ITMException
	{
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";

		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		ArrayList<String> profitList = new ArrayList<String>();
		ArrayList<String> toSalList = new ArrayList<String>();
		ArrayList<String> monthNameList = null;
		ArrayList<String> keyList = new ArrayList<String>();
		ArrayList<String> dataList = null;
		Map<String, ArrayList<String>> hashMap = new HashMap<String, ArrayList<String>>();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			if(finYear!=null && finYear.trim().length()>0)
			{
			sql = "SELECT tran, apr,may,jun,jul,aug,sep,oct,nov,DEC,jan,feb,mar FROM (SELECT '1.PROFIT' AS tran,ROUND(SUM(CY_SALE_MTH_apr)/100000,2) - SUM(NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0)) AS apr,ROUND(SUM(CY_SALE_MTH_may)/100000,2) - SUM(NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0)) AS may, ROUND(SUM(CY_SALE_MTH_jun)/100000,2) - SUM(NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0)) AS jun,ROUND(SUM(CY_SALE_MTH_jul)/100000,2) - SUM(NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0)) AS jul,ROUND(SUM(CY_SALE_MTH_aug)/100000,2) - SUM(NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0)) AS aug,ROUND(SUM(CY_SALE_MTH_sep)/100000,2) - SUM(NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0)) AS sep,ROUND(SUM(CY_SALE_MTH_oct)/100000,2) - SUM(NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0)) AS oct,ROUND(SUM(CY_SALE_MTH_nov)/100000,2) - SUM(NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0)) AS nov, ROUND(SUM(CY_SALE_MTH_dec)/100000,2) - SUM(NVL(COGS_MTH_dec,0)+NVL(EXPENSE_MTH_dec,0)) AS DEC,ROUND(SUM(CY_SALE_MTH_jan)/100000,2) - SUM(NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0)) AS jan,ROUND(SUM(CY_SALE_MTH_feb)/100000,2) - SUM(NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0)) AS feb,ROUND(SUM(CY_SALE_MTH_mar)/100000,2) - SUM(NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0)) AS mar FROM SM_SALES_SUMM_WIDE, sales_item WHERE trim(SM_SALES_SUMM_WIDE.fin_year)='"+finYear+"' AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code AND sales_item.item_sub_group NOT IN ('CU','BJ') UNION ALL SELECT '2.% to SALE' AS tran,( CASE WHEN SUM(CY_SALE_MTH_apr)=0 THEN 0 ELSE  ROUND(  ( SUM(CY_SALE_MTH_apr) - (SUM( NVL(COGS_MTH_APR,0)+NVL(EXPENSE_MTH_APR,0) )*100000)  )/SUM(CY_SALE_MTH_apr)* 100, 2) END ) AS apr,( CASE WHEN SUM(CY_SALE_MTH_may) =0 THEN 0 ELSE  ROUND(  ( SUM(CY_SALE_MTH_may) - (SUM( NVL(COGS_MTH_may,0)+NVL(EXPENSE_MTH_may,0) )*100000)  )/SUM(CY_SALE_MTH_may)* 100, 2) END ) AS may,( CASE WHEN SUM(CY_SALE_MTH_jun) =0 THEN 0 ELSE  ROUND(  ( SUM(CY_SALE_MTH_jun) - (SUM( NVL(COGS_MTH_jun,0)+NVL(EXPENSE_MTH_jun,0) )*100000)  )/SUM(CY_SALE_MTH_jun)* 100, 2) END ) AS jun,( CASE WHEN SUM(CY_SALE_MTH_jul) =0 THEN 0 ELSE  ROUND(  ( SUM(CY_SALE_MTH_jul) - (SUM( NVL(COGS_MTH_jul,0)+NVL(EXPENSE_MTH_jul,0) )*100000)  )/SUM(CY_SALE_MTH_jul)* 100, 2) END ) AS jul,( CASE WHEN SUM(CY_SALE_MTH_aug) =0 THEN 0 ELSE  ROUND( ( SUM(CY_SALE_MTH_aug) - (SUM( NVL(COGS_MTH_aug,0)+NVL(EXPENSE_MTH_aug,0) )*100000)  )/SUM(CY_SALE_MTH_aug) * 100, 2) END ) AS aug," + "( CASE WHEN SUM(CY_SALE_MTH_sep) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_sep) -(SUM( NVL(COGS_MTH_sep,0)+NVL(EXPENSE_MTH_sep,0) )*100000))/SUM(CY_SALE_MTH_sep)* 100,2) END ) AS sep,( CASE WHEN SUM(CY_SALE_MTH_oct) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_oct) - (SUM( NVL(COGS_MTH_oct,0)+NVL(EXPENSE_MTH_oct,0) )*100000))/SUM(CY_SALE_MTH_oct)* 100, 2) END ) AS oct,( CASE WHEN SUM(CY_SALE_MTH_nov) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_nov) - (SUM( NVL(COGS_MTH_nov,0)+NVL(EXPENSE_MTH_nov,0) )*100000))/SUM(CY_SALE_MTH_nov) * 100, 2) END ) AS nov,( CASE WHEN SUM(CY_SALE_MTH_DEC) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_DEC) - (SUM( NVL(COGS_MTH_DEC,0)+NVL(EXPENSE_MTH_DEC,0) )*100000))/SUM(CY_SALE_MTH_dec) * 100, 2) END ) AS DEC,( CASE WHEN SUM(CY_SALE_MTH_jan) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_jan) - (SUM( NVL(COGS_MTH_jan,0)+NVL(EXPENSE_MTH_jan,0) )*100000)  )/SUM(CY_SALE_MTH_jan) * 100, 2) END ) AS jan,( CASE WHEN SUM(CY_SALE_MTH_feb) =0 THEN 0 ELSE  ROUND(  ( SUM(CY_SALE_MTH_feb) - (SUM( NVL(COGS_MTH_feb,0)+NVL(EXPENSE_MTH_feb,0) )*100000)  )/SUM(CY_SALE_MTH_feb) * 100, 2) END ) AS feb,( CASE WHEN SUM(CY_SALE_MTH_mar) =0 THEN 0 ELSE  ROUND(( SUM(CY_SALE_MTH_mar) - (SUM( NVL(COGS_MTH_mar,0)+NVL(EXPENSE_MTH_mar,0) )*100000)  )/SUM(CY_SALE_MTH_mar)* 100, 2) END ) AS mar  FROM SM_SALES_SUMM_WIDE, sales_item WHERE trim(SM_SALES_SUMM_WIDE.fin_year)='"+finYear+"' AND SM_SALES_SUMM_WIDE.item_code= sales_item.item_code AND sales_item.item_sub_group NOT IN ('CU','BJ'))WHERE '"+finYear+"' = '"+finYear+"'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				dataList = new ArrayList<String>();
				String tran = rs.getString("tran");
				keyList.add(tran);								
				dataList.add(checkNull(rs.getString("APR")));
				dataList.add(checkNull(rs.getString("MAY")));
				dataList.add(checkNull(rs.getString("JUN")));
				dataList.add(checkNull(rs.getString("JUL")));
				dataList.add(checkNull(rs.getString("AUG")));
				dataList.add(checkNull(rs.getString("SEP")));
				dataList.add(checkNull(rs.getString("OCT")));
				dataList.add(checkNull(rs.getString("NOV")));
				dataList.add(checkNull(rs.getString("DEC")));
				dataList.add(checkNull(rs.getString("JAN")));
				dataList.add(checkNull(rs.getString("FEB")));
				dataList.add(checkNull(rs.getString("MAR")));
				hashMap.put(tran, dataList);
				System.out.println("keyList ProfitGraph====" + keyList);

			}
		}
		} 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:getProfitGraph:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		
		if (keyList.size() == 2)
		{
			System.out.println("keyList.toString()ProfitGraph =" + keyList.toString());
			System.out.println("keyList.size()ProfitGraph =" + keyList.size());
			profitList = hashMap.get(keyList.get(0));
			toSalList = hashMap.get(keyList.get(1));

			int count = 0;
			monthNameList =this.getMonthList();
			for (int i = 0; i < profitList.size(); i++)
			{
				rowData = new JSONObject();
				rowData.put("month", monthNameList.get(i));
				rowData.put("profits", profitList.get(i));
				rowData.put("to_sal", toSalList.get(i));
				rawDataJson.put(count, rowData);
				count++;
			}
		}
		System.out.println("rawDataJson ProfitGraph is =" + rawDataJson);

		return rawDataJson;
	}

	/*** End of method getProfitGraphData() ***/

	/*** 4 - This method will contains final data for rendering Expense graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getExpenseGraphData(String dataSourceName, String finYear) throws RemoteException, ITMException
	{
		JSONObject rawDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		ArrayList<String> expenceList = new ArrayList<String>();
		ArrayList<String> toSalList = new ArrayList<String>();
		ArrayList<String> monthNameList =null;
		ArrayList<String> keyList = new ArrayList<String>();
		ArrayList<String> dataList = null;
		Map<String, ArrayList<String>> hashMap = new HashMap<String, ArrayList<String>>();
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			if(finYear!=null && finYear.trim().length()>0)
			{
				sql = "SELECT tran, apr,may,jun,jul,aug,sep,oct,nov,DEC,jan,feb,mar FROM "
					+" (SELECT '1.EXPENSE' AS tran,sum(NVL(EXPENSE_MTH_APR,0)) AS apr, "
					+" SUM(NVL(EXPENSE_MTH_may,0)) AS may,SUM(NVL(EXPENSE_MTH_jun,0)) AS jun, "
					+" SUM(NVL(EXPENSE_MTH_jul,0)) AS jul,SUM(NVL(EXPENSE_MTH_aug,0)) AS aug, "
					+" SUM(NVL(EXPENSE_MTH_sep,0)) AS sep,SUM(NVL(EXPENSE_MTH_oct,0)) AS oct, "
					+" SUM(NVL(EXPENSE_MTH_nov,0)) AS nov,SUM(NVL(EXPENSE_MTH_dec,0)) AS DEC, "
					+" SUM(NVL(EXPENSE_MTH_jan,0)) AS jan,SUM(NVL(EXPENSE_MTH_feb,0)) AS feb, "
					+" SUM(NVL(EXPENSE_MTH_mar,0)) AS mar FROM SM_SALES_SUMM_WIDE WHERE "
					+" trim(SM_SALES_SUMM_WIDE.fin_year)='"+finYear+"' UNION ALL SELECT '2.% to SALE' AS tran, "
					+" ( CASE WHEN SUM(CY_SALE_MTH_apr)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_APR,0)) "
					+" *100000 /SUM(CY_SALE_MTH_apr) * 100, 2) END ) AS apr,( CASE WHEN SUM(CY_SALE_MTH_may)=0 "
					+" THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_may,0)) *100000 /SUM(CY_SALE_MTH_may) * 100, 2) END ) "
					+" AS may,(CASE WHEN SUM(CY_SALE_MTH_jun)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_jun,0)) "
					+" *100000 /SUM(CY_SALE_MTH_jun) * 100, 2) END ) AS jun,( CASE WHEN SUM(CY_SALE_MTH_jul)=0 THEN "
					+" 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_jul,0)) *100000 /SUM(CY_SALE_MTH_jul) * 100, 2) END ) AS jul, "
					+" ( CASE WHEN SUM(CY_SALE_MTH_aug)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_aug,0)) "
					+" *100000 /SUM(CY_SALE_MTH_aug) * 100, 2) END ) AS aug,( CASE WHEN SUM(CY_SALE_MTH_sep)=0 THEN 0 ELSE "
					+" ROUND( SUM(NVL(EXPENSE_MTH_sep,0)) *100000 /SUM(CY_SALE_MTH_sep) * 100, 2) END ) AS sep, "
					+" (CASE WHEN SUM(CY_SALE_MTH_oct)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_oct,0)) *100000 "
					+" /SUM(CY_SALE_MTH_oct) * 100, 2) END ) AS oct,(CASE WHEN SUM(CY_SALE_MTH_nov)=0 THEN 0 ELSE  "
					+" ROUND( SUM(NVL(EXPENSE_MTH_nov,0)) *100000 /SUM(CY_SALE_MTH_nov) * 100, 2) END ) AS nov, "
					+" (CASE WHEN SUM(CY_SALE_MTH_dec)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_dec,0)) *100000  "
					+" /SUM(CY_SALE_MTH_dec) * 100, 2) END ) AS DEC,(CASE WHEN SUM(CY_SALE_MTH_jan)=0 THEN 0 ELSE  "
					+" ROUND( SUM(NVL(EXPENSE_MTH_jan,0)) *100000 /SUM(CY_SALE_MTH_jan) * 100, 2) END ) AS jan, "
					+" (CASE WHEN SUM(CY_SALE_MTH_feb)=0 THEN 0 ELSE  ROUND( SUM(NVL(EXPENSE_MTH_feb,0)) *100000 "
					+" /SUM(CY_SALE_MTH_feb) * 100, 2) END ) AS feb,( CASE WHEN SUM(CY_SALE_MTH_mar)=0 THEN 0 ELSE "
					+" ROUND( SUM(NVL(EXPENSE_MTH_mar,0)) *100000 /SUM(CY_SALE_MTH_mar) * 100, 2) END ) AS mar "
					+" FROM SM_SALES_SUMM_WIDE WHERE trim(SM_SALES_SUMM_WIDE.fin_year)= '"+finYear+"')";
				pstmt = conn.prepareStatement(sql);
				//year = "2014-15";
				rs = pstmt.executeQuery();
				while (rs.next())
				 {
					dataList = new ArrayList<String>();
					String tran = rs.getString("tran");
					keyList.add(tran);
					dataList.add(checkNull(rs.getString("APR")));
					dataList.add(checkNull(rs.getString("MAY")));
					dataList.add(checkNull(rs.getString("JUN")));
					dataList.add(checkNull(rs.getString("JUL")));
					dataList.add(checkNull(rs.getString("AUG")));
					dataList.add(checkNull(rs.getString("SEP")));
					dataList.add(checkNull(rs.getString("OCT")));
					dataList.add(checkNull(rs.getString("NOV")));
					dataList.add(checkNull(rs.getString("DEC")));
					dataList.add(checkNull(rs.getString("JAN")));
					dataList.add(checkNull(rs.getString("FEB")));
					dataList.add(checkNull(rs.getString("MAR")));
					hashMap.put(tran, dataList);
					System.out.println("keyList getExpenseGraphData====" + keyList);
			     }
			}

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

		if (keyList.size() == 2)
		{
			System.out.println("keyList.toString()getExpenseGraphData =" + keyList.toString());
			System.out.println("keyList.size()getExpenseGraphData =" + keyList.size());
			expenceList = hashMap.get(keyList.get(0));
			toSalList = hashMap.get(keyList.get(1));

			int count = 0;
			monthNameList  =this.getMonthList();
			for (int i = 0; i < expenceList.size(); i++)
			{
				rowData = new JSONObject();
				rowData.put("month", monthNameList.get(i));
				rowData.put("expense", expenceList.get(i));
				rowData.put("to_sal", toSalList.get(i));
				rawDataJson.put(count, rowData);
				count++;
			}
		}
		System.out.println("rawDataJson tExpenseGraph is =" + rawDataJson);

		return rawDataJson;
	}

	/*** End of method getProfitGraphData() 
	 * @author base
	 * @param  metedata as xml file by client side
	 * @return JSONObject
	 * @throws RemoteException, ITMException
	 * @
	 * ***/
	
	@SuppressWarnings("unchecked")
	public JSONObject getMetaData(String fileName) throws RemoteException, ITMException
	{
		String isCurrentLine = "";
		jsonMetaData = new JSONObject();
		StringBuffer metaData = new StringBuffer();
		
		String filePath = CommonConstants.JBOSSHOME + File.separator + "server" + File.separator + "default" + File.separator + "deploy" + File.separator + "ibase.ear" + File.separator + "ibase.war" + File.separator + "dashboard" + File.separator + "salesperson" + File.separator +fileName+".xml";
		System.out.println("Final File Path =" + filePath);
		try
		{
			BufferedReader bufferedReader = new BufferedReader(new FileReader(filePath));
			while ((isCurrentLine = bufferedReader.readLine()) != null)
			{
				System.out.println("isCurrentLine =" + isCurrentLine.trim());
				if (isCurrentLine.trim().length() > 0)
				{
					metaData.append(isCurrentLine.trim());
				}
			}
		} 
		catch (FileNotFoundException e)
		{
			System.out.println("SalesPersonSale:getMetaData(int selOption)= ");
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(" metaData.toString().replace :=" + metaData.toString().replace("//", "").trim());
		jsonMetaData.put("metaData", metaData.toString().replace("//", "").trim());
		System.out.println("Note: Json object for metadata===" + jsonMetaData.toString());
		return jsonMetaData;
	}

	private ArrayList<String> getMonthList()
	{

		ArrayList<String> monthNameList = new ArrayList<String>();

		monthNameList.add("APR");
		monthNameList.add("MAY");
		monthNameList.add("JUN");
		monthNameList.add("JUL");
		monthNameList.add("AUG");
		monthNameList.add("SEP");
		monthNameList.add("OCT");
		monthNameList.add("NOV");
		monthNameList.add("DEC");
		monthNameList.add("JAN");
		monthNameList.add("FEB");
		monthNameList.add("MAR");
		return monthNameList;
	}

	//Add this code by swati on dated 5Jul
	
	
	
	  /*** 1 - This method will contains final data for rendering Monthly Sales graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getMnthlySalesGraphData(String dataSourceName, String finYear, String month) throws RemoteException, ITMException
	{
		System.out.println("Final getMnthlySalesGraphData==="+finYear +"month="+month);
		JSONObject rawDataMthlySalesJson = new JSONObject();
		JSONObject rowData = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		List<String> dataList = new ArrayList<String>();
		String sql = "";
		try
		{
		    if(month!=null && month.trim().length()>0)
			{
		  sql = " SELECT a.divGRP,b.div_no||'. '||b.div_descr AS DIVISION, " + " ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, " + " ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES,  " + " (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_GR, " + " ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, " + " ROUND(a.trgt_mth_cy/100000,2) AS MTH_FLD_TRGT, " + " (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_HO_ACH, " + " (CASE WHEN a.trgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.trgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_FLD_ACH," + " ROUND(a.sale_ytd_cy/100000,2) AS YTD_CY_SALES, " + " ROUND(a.sale_ytd_ly/100000,2) AS YTD_LY_SALES,  " + " (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_GR, " + " ROUND(a.hotrgt_ytd_cy/100000,2) AS YTD_HO_TRGT, " + " ROUND(a.trgt_ytd_cy/100000,2) AS YTD_FLD_TRGT, " + " (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1)||'%'    " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_HO_ACH, " + " (CASE WHEN a.trgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.trgt_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_FLD_ACH,  " + " ROUND(a.sale_Qrt1_cy/100000,2) AS Q1," + " ROUND(a.sale_Qrt2_cy/100000,2) AS Q2,  " + " ROUND(a.sale_Qrt3_cy/100000,2) AS Q3,  " + " ROUND(a.sale_Qrt4_cy/100000,2) AS Q4,  " + " ROUND(a.sale_ytd_cy/100000/    " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR( '"+month+"',5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) AS PMPM,  " + " b.pmpm_ly AS PMPM_LY, " + " ROUND(a.sale_ytd_cy/100000/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) - b.pmpm_ly  AS INC_PMPM, " + " ROUND((a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " + " ROUND(a.sale_ytd_cy/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 " + " ELSE SUBSTR('"+month+"',5,2)-3 END) " + " /100000,2) AS AVG_MTHLY,  " + " ( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) " + " WHEN SUBSTR('"+month+"' ,5,2) <3 THEN ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) " + " ELSE ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) " + " END ) AS REQD_MTHLY, " + " ROUND(a.hotrgt_annual_cy/100000,2) AS ANNUAL_TRGT, " + " ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD," + " (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS RETU_YTD_PRCT " + " from " + " ( SELECT brnd.div_code as divGRP,dwh.fin_year , dwh.item_ser, " + " SUM(sale_mth_cy) AS sale_mth_cy, " + " SUM(sale_mth_ly) AS sale_mth_ly, " + " SUM(trgt_mth_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,  " + " SUM(trgt_mth_cy) AS trgt_mth_cy, " + " SUM(sale_YTD_cy) AS sale_YTD_cy, " + " SUM(sale_YTD_ly) AS sale_YTD_ly, " + " SUM(trgt_YTD_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, " + " SUM(trgt_YTD_cy) AS trgt_YTD_cy,  " + " SUM(sale_Qrt1_cy) AS sale_Qrt1_cy, " + " SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " + " SUM(sale_Qrt3_cy) AS sale_Qrt3_cy, " + " SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " + " SUM(trgt_annual_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_annual_cy,  " + " SUM(retu_ytd_cy) AS retu_ytd_cy " + "  from " + " (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,  " + " sales_item.item_sub_group AS sub_grp, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP)" + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, " + " (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, " + " SUM(CY_SALE_YTD_JUN) AS sale_Qrt1_cy, " + " SUM(CY_SALE_YTD_SEP)-SUM(CY_SALE_YTD_JUN) AS sale_Qrt2_cy,  " + " SUM(CY_SALE_YTD_DEC)-SUM(CY_SALE_YTD_SEP) AS sale_Qrt3_cy, " + " SUM(CY_SALE_YTD_MAR)-SUM(CY_SALE_YTD_DEC) AS sale_Qrt4_cy,  " + " SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_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 " + " ) 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    " + " GROUP BY dwh.fin_year, dwh.item_ser,brnd.div_code " + " ) a, " + " SM_SALES_DIV_MST b " + " WHERE trim(a.item_ser)=trim (b.div_code)  " + " and B.FIN_YEAR=a.FIN_YEAR "		+ "  ORDER BY division ";
		    	/*sql =" SELECT b.div_group,( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2)<3 THEN ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy) "
		    		+" /(12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) ELSE ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy) "
		    		+" /(12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) END ) AS REQD_MTHLY, "
		    		+" ROUND(SUM(a.sale_ytd_cy)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN "
		    		+" SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END)/100000,2) "
		    		+" AS AVG_MTHLY,ROUND(SUM(a.sale_mth_cy)/100000,2) AS MTH_CY_SALES, " 
		    		+" ROUND(SUM(a.hotrgt_mth_cy)/100000,2) AS MTH_HO_TRGT,ROUND(SUM (a.sale_mth_ly)/	SUM (a.hotrgt_mth_cy)* 100,2) AS MTH_ACH_PER,(CASE WHEN SUM(a.sale_mth_ly)<>0 "
		    		+" THEN ROUND(SUM(a.sale_mth_cy - a.sale_mth_ly)/SUM(a.sale_mth_ly)*100,1) "
		    		+" ELSE ROUND(9.99*100,1)  END) AS MTH_GR,ROUND(SUM(a.sale_ytd_cy)/100000,2) "
		    		+" AS YTD_CY_SALES,ROUND(SUM(a.sale_ytd_ly)/100000,2) AS YTD_LY_SALES, "
		    		+" ROUND(SUM(a.hotrgt_ytd_cy)/100000,2) AS YTD_HO_TRGT,(CASE WHEN SUM(a.sale_ytd_ly)<>0 "
		    		+" THEN ROUND(SUM(a.sale_ytd_cy - a.sale_ytd_ly)/SUM(a.sale_ytd_ly)*100,1) "
		    		+" ELSE ROUND(9.99*100,1)  END) AS YTD_GR,ROUND(SUM(a.sale_Qrt1_cy)/100000,2) AS Q1, " 
		    		+" ROUND(SUM(a.sale_Qrt2_cy)/100000,2) AS Q2,ROUND(SUM(a.sale_Qrt3_cy)/100000,2) AS Q3, " 
		    		+" ROUND(SUM(a.sale_Qrt4_cy)/100000,2) AS Q4,ROUND(SUM(a.sale_ytd_cy)/100000/"
		    		+" SUM(c.psr)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 "
		    		+" ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2) AS PMPM,SUM(b.pmpm_ly) AS PMPM_LY, "
		    		+" ROUND(SUM(a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " 
		    		+" (CASE WHEN SUM(a.sale_ytd_cy)<>0 THEN ROUND(SUM(a.retu_ytd_cy)/" 
		    		+" SUM(a.sale_ytd_cy)*100,1) ELSE ROUND(9.99*100,1) END) AS RETU_YTD_PRCT "
		    		+" FROM (SELECT dwh.fin_year , dwh.item_ser,SUM(sale_mth_cy) AS sale_mth_cy, "
		    		+" SUM(sale_mth_ly) AS sale_mth_ly,SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))"
		    		+" /100) AS hotrgt_mth_cy,SUM(sale_YTD_cy) AS sale_YTD_cy, "
		    		+" SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) "
		    		+" /100) AS hotrgt_ytd_cy,SUM(sale_Qrt1_cy) AS sale_Qrt1_cy,SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " 
		    		+" SUM(sale_Qrt3_cy) AS sale_Qrt3_cy,SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " 
		    		+" SUM(trgt_annual_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) "
		    		+" AS hotrgt_annual_cy,SUM(retu_ytd_cy) AS retu_ytd_cy "
		    		+" FROM(SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,"  
		    		+" sales_item.item_sub_group AS sub_grp,(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' "
		    		+" THEN SUM(CY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, "
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) "	         
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly,"
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY)"
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN)"
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) "
		    		+" AS trgt_mth_cy, "
		    		+" (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, "
		    		+" (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) "
		    		+" ELSE SUM(CY_SALE_YTD_JUN)  END ) AS sale_Qrt1_cy, "
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='06' "
		    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) - SUM(CY_SALE_YTD_JUN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) - SUM(CY_SALE_YTD_JUN) "
		    		+" ELSE  SUM(CY_SALE_YTD_SEP) - SUM(CY_SALE_YTD_JUN) END ) AS sale_Qrt2_cy,"
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='09' "
		    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) - "
		    		+" SUM(CY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='11' "
		    		+" THEN SUM(CY_SALE_YTD_NOV) - SUM(CY_SALE_YTD_SEP) ELSE "
		    		+" SUM(CY_SALE_YTD_DEC) - SUM(CY_SALE_YTD_SEP) END ) AS sale_Qrt3_cy, " 	
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='12' "
		    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) -"
		    		+" SUM(CY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='02' "
		    		+" THEN SUM(CY_SALE_YTD_FEB) - SUM(CY_SALE_YTD_DEC) ELSE "
		    		+" SUM(CY_SALE_YTD_MAR) - SUM(CY_SALE_YTD_DEC) END ) AS sale_Qrt4_cy,"
		    		+" SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy, "
		    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) "	
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL) "	
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB) "
		    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) "
		    		+" AS retu_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 "     
		    		+" ) 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 "  
		    		+" GROUP BY dwh.fin_year, dwh.item_ser ) a, " 
		    		+" SM_SALES_DIV_MST b,( SELECT finyr, table_no, SUM(psr) AS psr " 
		    		+" FROM ( SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code, "
		    		+" TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR FROM starclub.emp_sales "
		    		+" WHERE LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code ) "
		    		+" GROUP BY finyr, table_no ) c "
		    		+" WHERE trim(a.item_ser)=trim(b.div_code) AND b.fin_year=a.fin_year "
		    		+" AND trim(a.item_ser)=trim(c.table_no) "
		    		+" AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2) "
		    		+" AND b.div_group <> 'SPL CARE' GROUP BY b.div_group ORDER BY div_group ";
		         */
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;		
			while (rs.next())
			{
				rowData = new JSONObject();
                 //raw data json for Monthly Sales graph
				rowData.put("division_grp",  checkNull(rs.getString("divGRP")));
				//rowData.put("division_grp",  checkNull(rs.getString("div_group")));
				rowData.put("mth_sales_val", rs.getString("mth_cy_sales"));
				rowData.put("avg_month_sal", rs.getString("avg_mthly"));
				rowData.put("req_month_sal", rs.getString("reqd_mthly"));
				rowData.put("growt_perc",    rs.getString("mth_gr"));
				rowData.put("ho_ach", rs.getString("MTH_HO_ACH"));
				//rowData.put("ho_ach", rs.getString("MTH_ACH_PER"));
				rawDataMthlySalesJson.put(count, rowData);
				count++;
			}
		}
			System.out.println("fetch Data for Mnthly Sales Graph 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:getMnthlySalesGraphData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMthlySalesJson;
	}
    
	/*** 2 - This method will contains final data for rendering YTD Sales graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getYTDSalesGraphData(String dataSourceName, String finYear, String month) throws RemoteException, ITMException
	{
		
		System.out.println("Final year in getYTDSalesGraphData===month="+month);
		
		JSONObject rawDataYTDSalesJson = new JSONObject();
		JSONObject rowData = null;
		List<String> dataList = new ArrayList<String>();
		String sql = "";
		String sql1 = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
			if(month!=null && month.trim().length()>0)
			{
		  sql = " SELECT a.divGRP,b.div_no||'. '||b.div_descr AS DIVISION, " + " ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, " + " ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES,  " + " (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_GR, " + " ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, " + " ROUND(a.trgt_mth_cy/100000,2) AS MTH_FLD_TRGT, " + " (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_HO_ACH, " + " (CASE WHEN a.trgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.trgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_FLD_ACH," + " ROUND(a.sale_ytd_cy/100000,2) AS YTD_CY_SALES, " + " ROUND(a.sale_ytd_ly/100000,2) AS YTD_LY_SALES,  " + " (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_GR, " + " ROUND(a.hotrgt_ytd_cy/100000,2) AS YTD_HO_TRGT, " + " ROUND(a.trgt_ytd_cy/100000,2) AS YTD_FLD_TRGT, " + " (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1)||'%'    " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_HO_ACH, " + " (CASE WHEN a.trgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.trgt_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_FLD_ACH,  " + " ROUND(a.sale_Qrt1_cy/100000,2) AS Q1," + " ROUND(a.sale_Qrt2_cy/100000,2) AS Q2,  " + " ROUND(a.sale_Qrt3_cy/100000,2) AS Q3,  " + " ROUND(a.sale_Qrt4_cy/100000,2) AS Q4,  " + " ROUND(a.sale_ytd_cy/100000/    " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR( '"+month+"',5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) AS PMPM,  " + " b.pmpm_ly AS PMPM_LY, " + " ROUND(a.sale_ytd_cy/100000/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) - b.pmpm_ly  AS INC_PMPM, " + " ROUND((a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " + " ROUND(a.sale_ytd_cy/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 " + " ELSE SUBSTR('"+month+"',5,2)-3 END) " + " /100000,2) AS AVG_MTHLY,  " + " ( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) " + " WHEN SUBSTR('"+month+"' ,5,2) <3 THEN ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) " + " ELSE ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) " + " END ) AS REQD_MTHLY, " + " ROUND(a.hotrgt_annual_cy/100000,2) AS ANNUAL_TRGT, " + " ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD," + " (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS RETU_YTD_PRCT " + " from " + " ( SELECT brnd.div_code as divGRP,dwh.fin_year , dwh.item_ser, " + " SUM(sale_mth_cy) AS sale_mth_cy, " + " SUM(sale_mth_ly) AS sale_mth_ly, " + " SUM(trgt_mth_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,  " + " SUM(trgt_mth_cy) AS trgt_mth_cy, " + " SUM(sale_YTD_cy) AS sale_YTD_cy, " + " SUM(sale_YTD_ly) AS sale_YTD_ly, " + " SUM(trgt_YTD_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, " + " SUM(trgt_YTD_cy) AS trgt_YTD_cy,  " + " SUM(sale_Qrt1_cy) AS sale_Qrt1_cy, " + " SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " + " SUM(sale_Qrt3_cy) AS sale_Qrt3_cy, " + " SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " + " SUM(trgt_annual_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_annual_cy,  " + " SUM(retu_ytd_cy) AS retu_ytd_cy " + "  from " + " (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,  " + " sales_item.item_sub_group AS sub_grp, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP)" + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, " + " (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, " + " SUM(CY_SALE_YTD_JUN) AS sale_Qrt1_cy, " + " SUM(CY_SALE_YTD_SEP)-SUM(CY_SALE_YTD_JUN) AS sale_Qrt2_cy,  " + " SUM(CY_SALE_YTD_DEC)-SUM(CY_SALE_YTD_SEP) AS sale_Qrt3_cy, " + " SUM(CY_SALE_YTD_MAR)-SUM(CY_SALE_YTD_DEC) AS sale_Qrt4_cy,  " + " SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_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 " + " ) 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    " + " GROUP BY dwh.fin_year, dwh.item_ser,brnd.div_code " + " ) a, " + " SM_SALES_DIV_MST b " + " WHERE trim(a.item_ser)=trim (b.div_code)  " + " and B.FIN_YEAR=a.FIN_YEAR   ORDER BY division ";
				/*sql =" SELECT b.div_group,( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2)<3 THEN ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy) "
	    		+" /(12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) ELSE ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy) "
	    		+" /(12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) END ) AS REQD_MTHLY, "
	    		+" ROUND(SUM(a.sale_ytd_cy)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN "
	    		+" SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END)/100000,2) "
	    		+" AS AVG_MTHLY,ROUND(SUM(a.sale_mth_cy)/100000,2) AS MTH_CY_SALES, " 
	    		+" ROUND(SUM(a.hotrgt_mth_cy)/100000,2) AS MTH_HO_TRGT,(CASE WHEN SUM(a.sale_mth_ly)<>0 "
	    		+" THEN ROUND(SUM(a.sale_mth_cy - a.sale_mth_ly)/SUM(a.sale_mth_ly)*100,1) "
	    		+" ELSE ROUND(9.99*100,1)  END) AS MTH_GR,ROUND(SUM(a.sale_ytd_cy)/100000,2) "
	    		+" AS YTD_CY_SALES,ROUND(SUM(a.sale_ytd_ly)/100000,2) AS YTD_LY_SALES, "
	    		+" ROUND(SUM(a.hotrgt_ytd_cy)/100000,2) AS YTD_HO_TRGT,ROUND(SUM (a.sale_ytd_ly)/	SUM (a.hotrgt_ytd_cy)* 100,2) AS YTD_ACH_PER,(CASE WHEN SUM(a.sale_ytd_ly)<>0 "
	    		+" THEN ROUND(SUM(a.sale_ytd_cy - a.sale_ytd_ly)/SUM(a.sale_ytd_ly)*100,1) "
	    		+" ELSE ROUND(9.99*100,1)  END) AS YTD_GR,ROUND(SUM(a.sale_Qrt1_cy)/100000,2) AS Q1, " 
	    		+" ROUND(SUM(a.sale_Qrt2_cy)/100000,2) AS Q2,ROUND(SUM(a.sale_Qrt3_cy)/100000,2) AS Q3, " 
	    		+" ROUND(SUM(a.sale_Qrt4_cy)/100000,2) AS Q4,ROUND(SUM(a.sale_ytd_cy)/100000/"
	    		+" SUM(c.psr)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 "
	    		+" ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2) AS PMPM,SUM(b.pmpm_ly) AS PMPM_LY, "
	    		+" ROUND(SUM(a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " 
	    		+" (CASE WHEN SUM(a.sale_ytd_cy)<>0 THEN ROUND(SUM(a.retu_ytd_cy)/" 
	    		+" SUM(a.sale_ytd_cy)*100,1) ELSE ROUND(9.99*100,1) END) AS RETU_YTD_PRCT "
	    		+" FROM (SELECT dwh.fin_year , dwh.item_ser,SUM(sale_mth_cy) AS sale_mth_cy, "
	    		+" SUM(sale_mth_ly) AS sale_mth_ly,SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0))"
	    		+" /100) AS hotrgt_mth_cy,SUM(sale_YTD_cy) AS sale_YTD_cy, "
	    		+" SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) "
	    		+" /100) AS hotrgt_ytd_cy,SUM(sale_Qrt1_cy) AS sale_Qrt1_cy,SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " 
	    		+" SUM(sale_Qrt3_cy) AS sale_Qrt3_cy,SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " 
	    		+" SUM(trgt_annual_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) "
	    		+" AS hotrgt_annual_cy,SUM(retu_ytd_cy) AS retu_ytd_cy "
	    		+" FROM(SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,"  
	    		+" sales_item.item_sub_group AS sub_grp,(CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' "
	    		+" THEN SUM(CY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, "
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) "	         
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly,"
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY)"
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN)"
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) "
	    		+" AS trgt_mth_cy, "
	    		+" (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, "
	    		+" (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) "
	    		+" ELSE SUM(CY_SALE_YTD_JUN)  END ) AS sale_Qrt1_cy, "
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='06' "
	    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) - SUM(CY_SALE_YTD_JUN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) - SUM(CY_SALE_YTD_JUN) "
	    		+" ELSE  SUM(CY_SALE_YTD_SEP) - SUM(CY_SALE_YTD_JUN) END ) AS sale_Qrt2_cy,"
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='09' "
	    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) - "
	    		+" SUM(CY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='11' "
	    		+" THEN SUM(CY_SALE_YTD_NOV) - SUM(CY_SALE_YTD_SEP) ELSE "
	    		+" SUM(CY_SALE_YTD_DEC) - SUM(CY_SALE_YTD_SEP) END ) AS sale_Qrt3_cy, " 	
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='12' "
	    		+" THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) -"
	    		+" SUM(CY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='02' "
	    		+" THEN SUM(CY_SALE_YTD_FEB) - SUM(CY_SALE_YTD_DEC) ELSE "
	    		+" SUM(CY_SALE_YTD_MAR) - SUM(CY_SALE_YTD_DEC) END ) AS sale_Qrt4_cy,"
	    		+" SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy, "
	    		+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY) "	
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL) "	
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB) "
	    		+" WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) "
	    		+" AS retu_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 "     
	    		+" ) 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 "  
	    		+" GROUP BY dwh.fin_year, dwh.item_ser ) a, " 
	    		+" SM_SALES_DIV_MST b,( SELECT finyr, table_no, SUM(psr) AS psr " 
	    		+" FROM ( SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code, "
	    		+" TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR FROM starclub.emp_sales "
	    		+" WHERE LEVEL_NO = 4 GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code ) "
	    		+" GROUP BY finyr, table_no ) c "
	    		+" WHERE trim(a.item_ser)=trim(b.div_code) AND b.fin_year=a.fin_year "
	    		+" AND trim(a.item_ser)=trim(c.table_no) "
	    		+" AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2) "
	    		+" AND b.div_group <> 'SPL CARE' GROUP BY b.div_group ORDER BY div_group ";
		         */
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;
			
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("division_grp", checkNull(rs.getString("divGRP")));
				//rowData.put("division_grp", checkNull(rs.getString("div_group")));
				rowData.put("sal", rs.getString("ytd_cy_sales"));
				rowData.put("gr", rs.getString("ytd_gr"));
				rowData.put("ho_ach", rs.getString("MTH_HO_ACH"));
				//rowData.put("ho_ach", rs.getString("YTD_ACH_PER"));
				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 YTD Sales graph ***/
	@SuppressWarnings("unchecked")
	public JSONObject getMnthSalesDivWiseGraphData(String dataSourceName, String finYear, String month) throws RemoteException, ITMException
	{
		
		JSONObject rawDataMnthDivWiseSalesJson = new JSONObject();
		JSONObject rowData = null;
		List<String> dataList = new ArrayList<String>();
		String sql = "";
		String sql1 = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		try
		{
		
			if(month!=null && month.trim().length()>0)
			{
		sql = " SELECT a.divGRP,b.div_no||'. '||b.div_descr AS DIVISION, " 			+ " ROUND(a.sale_mth_cy/100000,2) AS MTH_CY_SALES, " 			+ " ROUND(a.sale_mth_ly/100000,2) AS MTH_LY_SALES,  " 			+ " (CASE WHEN a.sale_mth_ly<>0 THEN ROUND((a.sale_mth_cy - a.sale_mth_ly)/a.sale_mth_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_GR, " + " ROUND(a.hotrgt_mth_cy/100000,2) AS MTH_HO_TRGT, " + " ROUND(a.trgt_mth_cy/100000,2) AS MTH_FLD_TRGT, " + " (CASE WHEN a.hotrgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.hotrgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_HO_ACH, " + " (CASE WHEN a.trgt_mth_cy<>0 THEN ROUND(a.sale_mth_cy/a.trgt_mth_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS MTH_FLD_ACH," + " ROUND(a.sale_ytd_cy/100000,2) AS YTD_CY_SALES, " + " ROUND(a.sale_ytd_ly/100000,2) AS YTD_LY_SALES,  " + " (CASE WHEN a.sale_ytd_ly<>0 THEN ROUND((a.sale_ytd_cy - a.sale_ytd_ly)/a.sale_ytd_ly*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_GR, " + " ROUND(a.hotrgt_ytd_cy/100000,2) AS YTD_HO_TRGT, " + " ROUND(a.trgt_ytd_cy/100000,2) AS YTD_FLD_TRGT, " + " (CASE WHEN a.hotrgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.hotrgt_ytd_cy*100,1)||'%'    " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_HO_ACH, " + " (CASE WHEN a.trgt_ytd_cy<>0 THEN ROUND(a.sale_ytd_cy/a.trgt_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS YTD_FLD_ACH,  " + " ROUND(a.sale_Qrt1_cy/100000,2) AS Q1," + " ROUND(a.sale_Qrt2_cy/100000,2) AS Q2,  " + " ROUND(a.sale_Qrt3_cy/100000,2) AS Q3,  " + " ROUND(a.sale_Qrt4_cy/100000,2) AS Q4,  " + " ROUND(a.sale_ytd_cy/100000/    " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR( '"+month+"',5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) AS PMPM,  " + " b.pmpm_ly AS PMPM_LY, " + " ROUND(a.sale_ytd_cy/100000/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) " + " ,2) - b.pmpm_ly  AS INC_PMPM, " + " ROUND((a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA, " + " ROUND(a.sale_ytd_cy/ " + " (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 " + " ELSE SUBSTR('"+month+"',5,2)-3 END) " + " /100000,2) AS AVG_MTHLY,  " + " ( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) " + " WHEN SUBSTR('"+month+"' ,5,2) <3 THEN ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)-9) /100000,2) " + " ELSE ROUND((a.hotrgt_annual_cy-a.sale_ytd_cy)/ " + " (12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) " + " END ) AS REQD_MTHLY, " + " ROUND(a.hotrgt_annual_cy/100000,2) AS ANNUAL_TRGT, " + " ROUND(a.retu_ytd_cy/100000,2) AS RETU_YTD," + " (CASE WHEN a.sale_ytd_cy<>0 THEN ROUND(a.retu_ytd_cy/a.sale_ytd_cy*100,1)||'%' " + " ELSE ROUND(9.99*100,1)||'%' END) AS RETU_YTD_PRCT " + " from " + " ( SELECT brnd.div_code as divGRP,dwh.fin_year , dwh.item_ser, " + " SUM(sale_mth_cy) AS sale_mth_cy, " + " SUM(sale_mth_ly) AS sale_mth_ly, " + " SUM(trgt_mth_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,  " + " SUM(trgt_mth_cy) AS trgt_mth_cy, " + " SUM(sale_YTD_cy) AS sale_YTD_cy, " + " SUM(sale_YTD_ly) AS sale_YTD_ly, " + " SUM(trgt_YTD_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy, " + " SUM(trgt_YTD_cy) AS trgt_YTD_cy,  " + " SUM(sale_Qrt1_cy) AS sale_Qrt1_cy, " + " SUM(sale_Qrt2_cy) AS sale_Qrt2_cy, " + " SUM(sale_Qrt3_cy) AS sale_Qrt3_cy, " + " SUM(sale_Qrt4_cy) AS sale_Qrt4_cy, " + " SUM(trgt_annual_cy * (100+ nvl(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_annual_cy,  " + " SUM(retu_ytd_cy) AS retu_ytd_cy " + "  from " + " (SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser,  " + " sales_item.item_sub_group AS sub_grp, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP)" + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, " + " (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, " + " SUM(CY_SALE_YTD_JUN) AS sale_Qrt1_cy, " + " SUM(CY_SALE_YTD_SEP)-SUM(CY_SALE_YTD_JUN) AS sale_Qrt2_cy,  " + " SUM(CY_SALE_YTD_DEC)-SUM(CY_SALE_YTD_SEP) AS sale_Qrt3_cy, " + " SUM(CY_SALE_YTD_MAR)-SUM(CY_SALE_YTD_DEC) AS sale_Qrt4_cy,  " + " SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy,  " + " (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR) " + " WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB)  " + " WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_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 " + " ) 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    " + " GROUP BY dwh.fin_year, dwh.item_ser,brnd.div_code " + " ) a, " + " SM_SALES_DIV_MST b " + " WHERE trim(a.item_ser)=trim (b.div_code)  " + " and B.FIN_YEAR=a.FIN_YEAR "		+ "  ORDER BY division ";
				/*sql =" SELECT b.DIV_DESCR,( CASE WHEN SUBSTR('"+month+"' ,5,2) =3 THEN ROUND(0 ,2) WHEN SUBSTR('"+month+"' ,5,2) <3 THEN ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy)/(12- SUBSTR('"+month+"' ,5,2)-9) /100000,2)       ELSE ROUND(SUM(a.hotrgt_annual_cy-a.sale_ytd_cy)/(12- SUBSTR('"+month+"' ,5,2)+3) /100000,2) END ) AS REQD_MTHLY,ROUND(SUM(a.sale_ytd_cy)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END)/100000,2) AS AVG_MTHLY,ROUND(SUM(a.sale_mth_cy)/100000,2) AS MTH_CY_SALES,ROUND(SUM(a.hotrgt_mth_cy)/100000,2) AS MTH_HO_TRGT,(CASE WHEN SUM(a.sale_mth_ly)<>0 THEN ROUND(SUM(a.sale_mth_cy - a.sale_mth_ly)/SUM(a.sale_mth_ly)*100,1) ELSE ROUND(9.99*100,1)  END) AS MTH_GR,ROUND(SUM(a.sale_ytd_cy)/100000,2) AS YTD_CY_SALES,ROUND(SUM(a.sale_ytd_ly)/100000,2) AS YTD_LY_SALES,ROUND(SUM(a.hotrgt_ytd_cy)/100000,2) AS YTD_HO_TRGT, (CASE WHEN SUM(a.sale_ytd_ly)<>0 THEN ROUND(SUM(a.sale_ytd_cy - a.sale_ytd_ly)/SUM(a.sale_ytd_ly)*100,1) ELSE ROUND(9.99*100,1)  END) AS YTD_GR, ROUND(SUM(a.sale_Qrt1_cy)/100000,2) AS Q1,ROUND(SUM(a.sale_Qrt2_cy)/100000,2) AS Q2,ROUND(SUM(a.sale_Qrt3_cy)/100000,2) AS Q3,ROUND(SUM(a.sale_Qrt4_cy)/100000,2) AS Q4,ROUND(SUM(a.sale_ytd_cy)/100000/SUM(c.psr)/ (CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END) ,2) AS PMPM,SUM(b.pmpm_ly) AS PMPM_LY,ROUND(SUM(a.sale_ytd_cy)/100000/SUM(c.psr)/(CASE WHEN SUBSTR('"+month+"' ,5,2)<4 THEN SUBSTR('"+month+"' ,5,2)+9 ELSE SUBSTR('"+month+"' ,5,2)-3 END),2) - SUM(b.pmpm_ly ) AS INC_PMPM,ROUND(SUM(a.sale_ytd_cy-a.hotrgt_ytd_cy)/100000,2) AS YTD_ACH_EXTRA,(CASE WHEN SUM(a.sale_ytd_cy)<>0 THEN ROUND(SUM(a.retu_ytd_cy)/SUM(a.sale_ytd_cy)*100,1) ELSE ROUND(9.99*100,1) END) AS RETU_YTD_PRCT FROM (SELECT dwh.fin_year , dwh.item_ser,SUM(sale_mth_cy) AS sale_mth_cy,SUM(sale_mth_ly) AS sale_mth_ly,SUM(trgt_mth_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_mth_cy,SUM(sale_YTD_cy) AS sale_YTD_cy,SUM(sale_YTD_ly) AS sale_YTD_ly,SUM(trgt_YTD_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_ytd_cy,SUM(sale_Qrt1_cy) AS sale_Qrt1_cy, "
					+" SUM(sale_Qrt2_cy) AS sale_Qrt2_cy,SUM(sale_Qrt3_cy) AS sale_Qrt3_cy,SUM(sale_Qrt4_cy) AS sale_Qrt4_cy,SUM(trgt_annual_cy * (100+ NVL(brnd.HO_TRGT_INCR_PRCNT,0)) /100) AS hotrgt_annual_cy,SUM(retu_ytd_cy) AS retu_ytd_cy FROM(SELECT fin_year , SM_SALES_SUMM_WIDE.item_ser, "  
					+" sales_item.item_sub_group AS sub_grp, (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_SALE_MTH_JUN)WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_SALE_MTH_MAR) END ) AS sale_mth_cy, "
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(LY_SALE_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(LY_SALE_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(LY_SALE_MTH_JUN)  WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(lY_SALE_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(lY_SALE_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(lY_SALE_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(lY_SALE_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(lY_SALE_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(lY_SALE_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(lY_SALE_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(lY_SALE_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(lY_SALE_MTH_MAR) END ) AS sale_mth_ly, "
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(FLD_TRGT_MTH_APR) WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(FLD_TRGT_MTH_MAY) WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(FLD_TRGT_MTH_JUN)WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(FLD_TRGT_MTH_JUL) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(FLD_TRGT_MTH_AUG) WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(FLD_TRGT_MTH_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(FLD_TRGT_MTH_OCT) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(FLD_TRGT_MTH_NOV) WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(FLD_TRGT_MTH_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(FLD_TRGT_MTH_JAN) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(FLD_TRGT_MTH_FEB) WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(FLD_TRGT_MTH_MAR) END ) AS trgt_mth_cy, "
					+" (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, "
					+" (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)  ELSE  SUM(CY_SALE_YTD_JUN)  END ) AS sale_Qrt1_cy, "
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='06' THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_SALE_YTD_JUL) - SUM(CY_SALE_YTD_JUN) WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_SALE_YTD_AUG) - SUM(CY_SALE_YTD_JUN) ELSE  SUM(CY_SALE_YTD_SEP) - SUM(CY_SALE_YTD_JUN) END ) AS sale_Qrt2_cy, "  
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='09' THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_SALE_YTD_OCT) - SUM(CY_SALE_YTD_SEP) WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_SALE_YTD_NOV) - SUM(CY_SALE_YTD_SEP) ELSE  SUM(CY_SALE_YTD_DEC) - SUM(CY_SALE_YTD_SEP) END ) AS sale_Qrt3_cy, "          
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) >='04' AND  SUBSTR('"+month+"' ,5,2) <='12' THEN 0 WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_SALE_YTD_JAN) - SUM(CY_SALE_YTD_DEC) WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_SALE_YTD_FEB) - SUM(CY_SALE_YTD_DEC) ELSE  SUM(CY_SALE_YTD_MAR) - SUM(CY_SALE_YTD_DEC) END ) AS sale_Qrt4_cy, "
					+" SUM(FLD_TRGT_YTD_MAR) AS trgt_annual_cy, "
					+" (CASE WHEN SUBSTR('"+month+"' ,5,2) ='04' THEN SUM(CY_RETU_YTD_APR)                  WHEN SUBSTR('"+month+"' ,5,2) ='05' THEN SUM(CY_RETU_YTD_MAY)                          WHEN SUBSTR('"+month+"' ,5,2) ='06' THEN SUM(CY_RETU_YTD_JUN)                          WHEN SUBSTR('"+month+"' ,5,2) ='07' THEN SUM(CY_RETU_YTD_JUL)                          WHEN SUBSTR('"+month+"' ,5,2) ='08' THEN SUM(CY_RETU_YTD_AUG)                          WHEN SUBSTR('"+month+"' ,5,2) ='09' THEN SUM(CY_RETU_YTD_SEP)                          WHEN SUBSTR('"+month+"' ,5,2) ='10' THEN SUM(CY_RETU_YTD_OCT)                          WHEN SUBSTR('"+month+"' ,5,2) ='11' THEN SUM(CY_RETU_YTD_NOV)                          WHEN SUBSTR('"+month+"' ,5,2) ='12' THEN SUM(CY_RETU_YTD_DEC)                          WHEN SUBSTR('"+month+"' ,5,2) ='01' THEN SUM(CY_RETU_YTD_JAN)                          WHEN SUBSTR('"+month+"' ,5,2) ='02' THEN SUM(CY_RETU_YTD_FEB)                          WHEN SUBSTR('"+month+"' ,5,2) ='03' THEN SUM(CY_RETU_YTD_MAR) END ) AS retu_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 "    
					+" ) 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 "  
					+" GROUP BY dwh.fin_year, dwh.item_ser ) a, "
					+" SM_SALES_DIV_MST b,( SELECT finyr, table_no, SUM(psr) AS psr "
					+" FROM ( SELECT SUBSTR(version_id,3,4) AS finyr ,table_no, pool_code,TO_NUMBER(MAX(NO_PSR_TERR)) AS PSR "
					+" FROM starclub.emp_sales WHERE LEVEL_NO = 4 "
					+" GROUP BY SUBSTR(version_id,3,4) ,table_no, pool_code ) GROUP BY finyr, table_no ) c "
					+" WHERE trim(a.item_ser)=trim(b.div_code) AND b.fin_year=a.fin_year AND trim(a.item_ser)=trim(c.table_no) "
					+" AND c.finyr = SUBSTR(a.fin_year,3,2)||SUBSTR(a.fin_year,6,2) AND b.DIV_DESCR <> 'SPL CARE' GROUP BY b.DIV_DESCR ORDER BY DIV_DESCR ";
		   */
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int count = 0;
			
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("item_ser", checkNull(rs.getString("DIVISION")));
				//rowData.put("item_ser", checkNull(rs.getString("DIV_DESCR")));
				rowData.put("mth_sales_val", rs.getString("MTH_CY_SALES"));
				rowData.put("pmpm", rs.getString("PMPM"));
				rowData.put("inc_pmpm", rs.getString("INC_PMPM"));
				rawDataMnthDivWiseSalesJson.put(count, rowData);
				count++;
			}
		}
			System.out.println("fetch Data for Mnth Sales Div Wise Graph 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:getMnthSalesDivWiseGraphData:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMnthDivWiseSalesJson;
	}
	//end code by swati on dated 5Jul-14	
	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		
}
