package ibase.dashboard.scm.ejb;
import ibase.dashboard.Report.Utility.DashboardUtility;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;
@Stateless
public class StateWiseStock extends ValidatorEJB implements StateWiseStockRemote, StateWiseStockLocal
{
		
	public StateWiseStock()
	{}
	GenericUtility genericUtility = GenericUtility.getInstance();
	@SuppressWarnings("unchecked")		
	public JSONObject getIndiaMap(String dataSourceName, String month, String brand,String division, String viewOptionSelectd) throws RemoteException, ITMException
	{
		System.out.println(" in getIndiaMap");
		JSONObject rawDataIndiaMapJson = new JSONObject();
		JSONObject rowData = null;
		DashboardUtility dashbordUtil = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		String brandStr = "";
		String divisionStr = "";
		int count = 0;
		String previousStateId = "";
		StringBuffer toolTextStr = new StringBuffer(); 
		double siteInvtry = 0.0;
		String stateId =""; 
		double avgValue = 0.0;
		double maxValue = 0.0;
		ArrayList<Double>  maxValList = new ArrayList<Double>();
		HashMap<String,Double> maxValMap = new HashMap<String,Double>();
		DecimalFormat df = new DecimalFormat("##############.##");
		
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			
			dashbordUtil = new DashboardUtility();
			System.out.println("in StateWiseStock  month :"+month+"-----brand :"+brand+"-----division : "+division+"----viewOptionSelectd :"+viewOptionSelectd);
			
			if(brand!=null && brand.trim().length()>0 && !brand.equals(""))
		    {
		        brand     = dashbordUtil.getCommaSeparated(brand);
		        brandStr  ="AND trim(i.item_sub_group_descr) in ("+brand+")";
		        System.out.println("in brand block :: brand-->"+brandStr);
		    }
			if(division!=null && division.trim().length()>0 && !division.equals(""))
		    {
				division     = dashbordUtil.getCommaSeparated(division);
				divisionStr  ="AND trim(i.item_ser) IN ("+division+")";
		        System.out.println("in divisionStr block :: divisionStr-->"+divisionStr);
		    }
			
			/*sql = " SELECT st.internal_code AS code,st.state_code AS STATE, a.city AS SITE, "
			+ "(CASE WHEN a.site_invtry IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL "
			+ "ELSE 0 END ELSE a.site_invtry END ) AS site_invtry, a.near_exp, a.long_exp, a.nrexp_val,"
			+ " a.long_expval, a.avg_rate, a.remark FROM mthqty.state st, ( SELECT s.state_code , s.city , "
			+ "SUM(stk.cl_stk) AS site_invtry, SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp,"
			+ " ROUND(SUM(stk.cl_val)/100000,0) AS site_invtval, ROUND(SUM(stk.nrexp_val)/100000,0) AS nrexp_val,"
			+ " ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,0) AS long_expval, ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate,"
			+ " s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS remark "
			+ "FROM  appl.SITE s, (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp,"
			+ " SUM(cl_val) AS cl_val, SUM(nrexp_val) AS nrexp_val, ROUND(SUM(cl_val)/SUM(cl_stk),2) AS avg_rate"
			+ " FROM (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) "
			+ "AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, "
			+ "LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
			+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life "
			+ "THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,"
			+ "sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code "
			+ "IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' "
			+ "AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " "
			+ "GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date "
			+ "UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, "
			+ "SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, "
			+ "(CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life"
			+ " THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, "
			+ "LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate)"
			+ " ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_utl v "
			+ "WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code "
			+ "IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3'"
			+ " AND i.item_sub_group NOT IN ('CU','BJ') "+brandStr + ""
			+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life,"
			+ " v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,"
			+ " v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val,"
			+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life "
			+ "THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, "
			+ "LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) "
			+ "ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_spl v WHERE "
			+ "v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND "
			+ "v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT "
			+ "IN ('CU','BJ') "+brandStr + " GROUP BY v.item_code, "
			+ "trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) "
			+ "GROUP BY item_code, brand,site_code ) stk WHERE s.site_code= stk.site_code "
			+ "GROUP BY s.state_code , s.city ) a WHERE  st.count_code='IND' AND st.internal_code IS NOT NULL "
			+ "AND trim(st.state_code)=trim(a.state_code(+)) ORDER BY code,state,SITE ";*/
			
			
			/*sql = "SELECT lnstk.code,lnstk.state, lnstk.SITE, lnstk.site_invtry, lnstk.near_exp, lnstk.long_exp, lnstk.site_invtval, lnstk.nrexp_val, "
					+ "lnstk.long_expval, lnstk.avg_rate, lnstk.remark, totstk.max_stk AS max_stk, ROUND(totstk.tot_stk/36,0) AS avg_stk "
					+ "FROM ( SELECT st.internal_code AS code,A.state_code AS STATE, A.city AS SITE, (CASE WHEN a.site_invtry IS NULL THEN "
					+ "CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtry END ) AS site_invtry, A.near_exp, A.long_exp,"
					+ " A.site_invtval, A.nrexp_val, A.long_expval, A.Avg_rate, A.remark FROM (SELECT s.state_code , s.city , SUM(stk.cl_stk) AS site_invtry,"
					+ " SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp, ROUND(SUM(stk.cl_val)/100000,0) AS site_invtval,"
					+ " ROUND(SUM(stk.nrexp_val)/100000,0) AS nrexp_val, ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,0) AS long_expval, "
					+ "ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate, s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS remark "
					+ "FROM  appl.SITE s, (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp, SUM(cl_val) AS cl_val,"
					+ " SUM(nrexp_val) AS nrexp_val, ROUND(SUM(cl_val)/SUM(cl_stk),2) AS avg_rate FROM (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,"
					+ "v.site_code,SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, "
					+ "(CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val "
					+ "FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code "
					+ "IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT "
					+ "IN ('CU','BJ') " +brandStr+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code,"
					+ " i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk,"
					+ " SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life "
					+ "THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val "
					+ "FROM sales_item i,sun.inventory_utl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " "
					+ "GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,"
					+ " v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, "
					+ "(CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val "
					+ "FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code "
					+ "IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' "
					+ "AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " GROUP BY v.item_code, "
					+ "trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) stk "
					+ "WHERE s.site_code= stk.site_code GROUP BY s.state_code,s.city ) a, mthqty.state st WHERE  st.count_code='IND' AND st.internal_code "
					+ "IS NOT NULL AND trim(st.state_code)=trim(a.state_code(+)) )LNSTK, ( SELECT MAX(cl_stk) AS max_stk, SUM(cl_stk) AS tot_stk "
					+ "FROM ( SELECT st.internal_code AS code, SUM(cl_stk) AS cl_stk FROM appl.SITE s, mthqty.state st, (SELECT v.site_code, SUM(v.cl_stk_qty) AS cl_stk"
					+ " FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) "
					+ "AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' "
					+ "AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " GROUP BY v.site_code "
					+ "UNION ALL SELECT v.site_code, SUM(v.cl_stk_qty) AS cl_stk FROM sales_item i,sun.inventory_utl v "
					+ "WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') "
					+ " " +brandStr+ " GROUP BY v.site_code UNION ALL SELECT v.site_code, SUM(v.cl_stk_qty) AS cl_stk"
					+ " FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code "
					+ "IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' "
					+ "AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " GROUP BY v.site_code ) a "
					+ "WHERE s.site_code=a.site_code AND trim(st.state_code)=trim(s.state_code) GROUP BY st.internal_code ) ) totstk "
					+ "ORDER BY code,state,SITE";*/
			
			//sql="WITH stk AS (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp, SUM(cl_val) AS cl_val, SUM(nrexp_val) AS nrexp_val, ROUND(SUM(cl_val)/SUM(cl_stk),2) AS avg_rate FROM (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) = 'AZTOR' GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_utl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) = 'AZTOR' GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) = 'AZTOR' GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) SELECT dat.code,dat.state_code,dat.city, CASE WHEN st.opt_1='QTY' AND st.opt_2='INVT' THEN dat.site_invtry WHEN st.opt_1='VAL' AND st.opt_2='INVT' THEN dat.site_invtval END AS amount, CASE WHEN st.opt_1='QTY' AND st.opt_2='INVT' THEN dat.q_remark WHEN st.opt_1='VAL' AND st.opt_2='INVT' THEN dat.v_remark END AS rem, CASE WHEN st.opt_1='QTY' AND st.opt_2='INVT' THEN dat.max_stk WHEN st.opt_1='VAL' AND st.opt_2='INVT' THEN dat.max_val END AS MAXamt, CASE WHEN st.opt_1='QTY' AND st.opt_2='INVT' THEN dat.avg_stk WHEN st.opt_1='VAL' AND st.opt_2='INVT' THEN dat.avg_val END AS avgamt, st.opt_1, st.opt_2 FROM ( SELECT lnstk.internal_code AS code, lnstk.state_code, lnstk.city, lnstk.site_invtry, lnstk.q_remark, maxstk.max_stk AS max_stk, ROUND(totstk.tot_stk/36,0) AS avg_stk, lnstk.site_invtval, lnstk.v_remark, ROUND(maxstk.max_val/100000,0) AS max_val, ROUND(totstk.tot_val/100000/36,0) AS avg_val FROM ( SELECT st.internal_code, st.state_code, a.city, (CASE WHEN a.site_invtry IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtry END ) AS site_invtry, (CASE WHEN a.site_invtval IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtval END ) AS site_invtval, A.q_remark,a.v_remark FROM  mthqty.state st, (SELECT s.state_code  , s.city , SUM(stk.cl_stk) AS site_invtry, SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp, ROUND(SUM(stk.cl_val)/100000,2) AS site_invtval, ROUND(SUM(stk.nrexp_val)/100000,2) AS nrexp_val, ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,2) AS long_expval, ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate, s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS q_remark, s.city||'-Invtry Val '||ROUND(SUM(stk.cl_val)/100000,2)||'('||ROUND(SUM(stk.nrexp_val)/100000,2) ||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS v_remark FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code,s.city ) a WHERE  st.count_code='IND' AND st.internal_code IS NOT NULL AND trim(st.state_code)=trim(a.state_code(+)) ) lnstk , ( SELECT SUM(stk.cl_stk) AS tot_stk, SUM(stk.cl_val) AS tot_val FROM stk ) totstk, ( SELECT MAX(cl_stk) AS max_stk, MAX(cl_val) AS max_val FROM ( SELECT s.state_code, SUM(stk.cl_stk) AS cl_stk, SUM(stk.cl_val) AS cl_val FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code ) ) maxstk ) dat, ( SELECT internal_code, 'QTY' AS opt_1, 'INVT' AS opt_2 FROM mthqty.state WHERE count_code='IND' AND internal_code IS NOT NULL UNION ALL SELECT internal_code, 'VAL' AS opt_1, 'INVT' AS opt_2 FROM mthqty.state WHERE count_code='IND' AND internal_code IS NOT NULL ) st WHERE st.internal_code=dat.code ORDER BY opt_1,opt_2, code,city ";
			
			//sql="WITH stk AS (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp, SUM(cl_val) AS cl_val, SUM(nrexp_val) AS nrexp_val, ROUND(SUM(cl_val)/SUM(cl_stk),2) AS avg_rate FROM (  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) IN ('AZTOR','AQUAZIDE') AND trim(i.item_ser) IN ('AV','RN') GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_utl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) IN ('AZTOR','AQUAZIDE') AND trim(i.item_ser) IN ('AV','RN') GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp, (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') AND trim(i.item_sub_group_descr) IN ('AZTOR','AQUAZIDE') AND trim(i.item_ser) IN ('AV','RN') GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) SELECT lnstk.internal_code AS code, lnstk.state_code, lnstk.city, lnstk.site_invtry AS q_invt, lnstk.q_remark AS q_invt_rem, maxstk.max_stk AS q_invt_max, ROUND(totstk.tot_stk/36,0) AS q_invt_avg, lnstk.site_invtval AS v_invt, lnstk.v_remark AS v_invt_rem, ROUND(maxstk.max_val/100000,0) AS v_invt_max, ROUND(totstk.tot_val/100000/36,0) AS v_invt_avg FROM ( SELECT st.internal_code, st.state_code, a.city, (CASE WHEN a.site_invtry IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtry END ) AS site_invtry, (CASE WHEN a.site_invtval IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtval END ) AS site_invtval, A.q_remark,a.v_remark FROM  mthqty.state st, (SELECT s.state_code  , s.city , SUM(stk.cl_stk) AS site_invtry, SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp, ROUND(SUM(stk.cl_val)/100000,2) AS site_invtval, ROUND(SUM(stk.nrexp_val)/100000,2) AS nrexp_val, ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,2) AS long_expval, ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate, s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS q_remark, s.city||'-Invtry Val '||ROUND(SUM(stk.cl_val)/100000,2)||'('||ROUND(SUM(stk.nrexp_val)/100000,2) ||') -Rate-'||ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS v_remark FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code,s.city ) a WHERE  st.count_code='IND' AND st.internal_code IS NOT NULL AND trim(st.state_code)=trim(a.state_code(+)) ) lnstk , ( SELECT SUM(stk.cl_stk) AS tot_stk, SUM(stk.cl_val) AS tot_val FROM stk ) totstk, ( SELECT MAX(cl_stk) AS max_stk, MAX(cl_val) AS max_val FROM ( SELECT s.state_code, SUM(stk.cl_stk) AS cl_stk, SUM(stk.cl_val) AS cl_val FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code ) ) maxstk ORDER BY code,city ";
			sql = "WITH stk AS (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp, SUM(cl_val) AS cl_val,"
					+ " SUM(nrexp_val) AS nrexp_val, (CASE WHEN SUM(cl_stk)=0 THEN 0 ELSE ROUND(SUM(cl_val)/SUM(cl_stk),2) END) AS avg_rate FROM "
					+ "(  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val"
					+ " FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND trim(i.item_ser) IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) "
					+ "" +divisionStr+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date "
					+ "UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val"
					+ " FROM sales_item i,sun.inventory_utl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND trim(i.item_ser) "
					+ "IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) " +divisionStr+ ""
					+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code,"
					+ " trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, "
					+ "(CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val "
					+ "FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND"
					+ " trim(i.item_ser) IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) " +divisionStr+ " GROUP BY v.item_code, "
					+ "trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) SELECT st.internal_code AS code, "
					+ "st.state_code, a.city, (CASE WHEN a.site_invtry IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtry END ) AS q_invt,"
					+ " A.q_remark AS q_invt_rem, (CASE WHEN a.site_invtval IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtval END ) AS v_invt,"
					+ " a.v_remark AS v_invt_rem, (CASE WHEN a.near_exp IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.near_exp END ) AS q_nr, "
					+ "A.nq_remark AS q_nr_rem, (CASE WHEN a.nrexp_val IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.nrexp_val END ) AS v_nr,"
					+ " a.nv_remark AS v_nr_rem, (CASE WHEN a.near_exp IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE (CASE WHEN a.site_invtry=0 "
					+ "THEN 0 ELSE ROUND(a.near_exp/a.site_invtry*100,1) END) END ) AS q_invtnr, a.city||'-Near Exp Qty '||a.near_exp||' -Prop.:'|| (CASE WHEN a.site_invtry=0 "
					+ "THEN 0 ELSE ROUND(a.near_exp/a.site_invtry*100,1) END)||'%' AS q_invtnr_rem, (CASE WHEN a.nrexp_val IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') "
					+ "THEN NULL ELSE 0 END ELSE (CASE WHEN a.site_invtval=0 THEN 0 ELSE ROUND(a.nrexp_val/a.site_invtval*100,1) END) END ) AS v_invtnr, "
					+ "a.city||'-Near Exp Val '||a.nrexp_val||' -Prop.:'|| (CASE WHEN a.site_invtval=0 THEN 0 ELSE ROUND(a.nrexp_val/a.site_invtval*100,1) END)||'%'  AS v_invtnr_rem"
					+ " FROM  mthqty.state st, (SELECT s.state_code  , s.city , SUM(stk.cl_stk) AS site_invtry, SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp,"
					+ " ROUND(SUM(stk.cl_val)/100000,2) AS site_invtval, ROUND(SUM(stk.nrexp_val)/100000,2) AS nrexp_val, ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,2) AS long_expval, "
					+ "ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate, s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate :'|| (CASE WHEN SUM(stk.cl_stk)=0 THEN 0 ELSE "
					+ "ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) END) AS q_remark, s.city||'-Invtry Val '||ROUND(SUM(stk.cl_val)/100000,2)||'L ('||ROUND(SUM(stk.nrexp_val)/100000,2)||'L) -Rate :'|| (CASE WHEN SUM(stk.cl_stk)=0 "
					+ "THEN 0 ELSE ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) END) AS v_remark, s.city||'-Near Exp Qty '||SUM(stk.near_exp)||' -Rate :'|| (CASE WHEN SUM(stk.near_exp)=0"
					+ " THEN 0 ELSE ROUND(SUM(stk.nrexp_val)/SUM(stk.near_exp),2) END) AS nq_remark, s.city||'-Near Exp Val '||ROUND(SUM(stk.nrexp_val)/100000,2)||'L -Rate :'|| "
					+ "(CASE WHEN SUM(stk.near_exp)=0 THEN 0 ELSE ROUND(SUM(stk.nrexp_val)/SUM(stk.near_exp),2) END) AS nv_remark FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code,s.city ) a"
					+ " WHERE  st.count_code='IND' AND st.internal_code IS NOT NULL AND trim(st.state_code)=trim(a.state_code(+)) ORDER BY code,city";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while (rs.next())
			{
				
				stateId = rs.getString("CODE");
				
				if(!previousStateId.equals(stateId))
				{
					
					siteInvtry= 0;
					if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory")) //1
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales")) //2
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >>  Avg Sales")) //3
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_AVGSALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry >> Inventory"))  //4
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVTNR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry"))  //5
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_NR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >> Pending Orders"))  //6
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT_PENDORD");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales Return >> Sales"))  //7
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_SR_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory")) //8
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales")) //9
					{
						siteInvtry = siteInvtry + rs.getDouble("V_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >>  Avg Sales")) //10
					{
						siteInvtry = siteInvtry + rs.getDouble("V_AVGSALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry >> Inventory"))  //11
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVTNR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry"))  //12
					{
						siteInvtry = siteInvtry + rs.getDouble("V_NR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >> Pending Orders"))  //13
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVT_PENDORD");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales Return >> Sales"))  //14
					{
						siteInvtry = siteInvtry + rs.getDouble("V_SR_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					previousStateId = rs.getString("CODE");
					
				}else if(previousStateId.equals(stateId))
				{
					if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory")) //1
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales"))  //2
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >>  Avg Sales")) //3
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_AVGSALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry >> Inventory")) //4
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVTNR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry"))  //5
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_NR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >> Pending Orders"))  //6
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT_PENDORD");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales Return >> Sales"))  //7
					{
						siteInvtry = siteInvtry + rs.getDouble("Q_SR_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory")) //8
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales")) //9
					{
						siteInvtry = siteInvtry + rs.getDouble("V_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >>  Avg Sales")) //10
					{
						siteInvtry = siteInvtry + rs.getDouble("V_AVGSALES");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry >> Inventory"))  //11
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVTNR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry"))  //12
					{
						siteInvtry = siteInvtry + rs.getDouble("V_NR");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >> Pending Orders"))  //13
					{
						siteInvtry = siteInvtry + rs.getDouble("V_INVT_PENDORD");
						maxValMap.put(stateId, siteInvtry);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales Return >> Sales"))  //14
					{
						siteInvtry = siteInvtry + rs.getDouble("V_SR_SALES");
						maxValMap.put(stateId, siteInvtry);
					}
				}
			}
			System.out.println("maxValMap------->"+maxValMap);
			for (Map.Entry<String, Double> entry : maxValMap.entrySet()) {
			    System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
			    maxValList.add(entry.getValue());
			}
			System.out.println("maxValList------->"+maxValList);
			
			for(double avgVal : maxValList)
			{
				avgValue = avgValue + avgVal;
			}
			avgValue = avgValue/36;
			avgValue = Double.parseDouble(df.format(avgValue));
			System.out.println("avgValue------->"+df.format(avgValue));
	
			sql = "WITH stk AS (SELECT item_code, brand, site_code, SUM(cl_stk) AS cl_stk, SUM(near_exp) AS near_exp, SUM(cl_val) AS cl_val,"
					+ " SUM(nrexp_val) AS nrexp_val, (CASE WHEN SUM(cl_stk)=0 THEN 0 ELSE ROUND(SUM(cl_val)/SUM(cl_stk),2) END) AS avg_rate FROM "
					+ "(  SELECT v.item_code, trim(i.item_sub_group_descr) AS brand,v.site_code,SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val"
					+ " FROM sales_item i,sun.inventory v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND trim(i.item_ser) IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) "
					+ "" +divisionStr+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date "
					+ "UNION ALL SELECT v.item_code, trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val"
					+ " FROM sales_item i,sun.inventory_utl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND trim(i.item_ser) "
					+ "IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) " +divisionStr+ ""
					+ " GROUP BY v.item_code, trim(i.item_sub_group_descr), v.site_code, i.min_shelf_life, v.exp_date UNION ALL SELECT v.item_code,"
					+ " trim(i.item_sub_group_descr) AS brand, v.site_code, SUM(v.cl_stk_qty) AS cl_stk, SUM(v.cl_stk_qty*v.prcp_rate) AS cl_val, "
					+ "(CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty) ELSE 0 END) AS near_exp,"
					+ " (CASE WHEN MONTHS_BETWEEN(v.exp_date, LAST_DAY(TO_DATE('"+month+"','yyyymm'))) <= i.min_shelf_life THEN SUM(v.cl_stk_qty*v.prcp_rate) ELSE 0 END) AS nrexp_val "
					+ "FROM sales_item i,sun.inventory_spl v WHERE v.inv_date= LAST_DAY(TO_DATE('"+month+"','yyyymm')) AND v.loc_code IN ( SELECT loc_code FROM LOC_SALABLE) "
					+ "AND v.item_code= i.item_code AND SUBSTR(v.site_code,3,1) <>'3' AND i.item_sub_group NOT IN ('CU','BJ') " +brandStr+ " AND"
					+ " trim(i.item_ser) IN (SELECT trim(item_ser) FROM ITEMSER WHERE PRODUCT_LINE ='DOM' AND grp_code=1) " +divisionStr+ " GROUP BY v.item_code, "
					+ "trim(i.item_sub_group_descr), v.site_code,i.min_shelf_life, v.exp_date ) GROUP BY item_code, brand,site_code ) SELECT st.internal_code AS code, "
					+ "st.state_code, a.city, (CASE WHEN a.site_invtry IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtry END ) AS q_invt,"
					+ " A.q_remark AS q_invt_rem, (CASE WHEN a.site_invtval IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.site_invtval END ) AS v_invt,"
					+ " a.v_remark AS v_invt_rem, (CASE WHEN a.near_exp IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.near_exp END ) AS q_nr, "
					+ "A.nq_remark AS q_nr_rem, (CASE WHEN a.nrexp_val IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE a.nrexp_val END ) AS v_nr,"
					+ " a.nv_remark AS v_nr_rem, (CASE WHEN a.near_exp IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') THEN NULL ELSE 0 END ELSE (CASE WHEN a.site_invtry=0 "
					+ "THEN 0 ELSE ROUND(a.near_exp/a.site_invtry*100,1) END) END ) AS q_invtnr, a.city||'-Near Exp Qty '||a.near_exp||' -Prop.:'|| (CASE WHEN a.site_invtry=0 "
					+ "THEN 0 ELSE ROUND(a.near_exp/a.site_invtry*100,1) END)||'%' AS q_invtnr_rem, (CASE WHEN a.nrexp_val IS NULL THEN CASE WHEN st.state_code IN ('AN','LD') "
					+ "THEN NULL ELSE 0 END ELSE (CASE WHEN a.site_invtval=0 THEN 0 ELSE ROUND(a.nrexp_val/a.site_invtval*100,1) END) END ) AS v_invtnr, "
					+ "a.city||'-Near Exp Val '||a.nrexp_val||' -Prop.:'|| (CASE WHEN a.site_invtval=0 THEN 0 ELSE ROUND(a.nrexp_val/a.site_invtval*100,1) END)||'%'  AS v_invtnr_rem"
					+ " FROM  mthqty.state st, (SELECT s.state_code  , s.city , SUM(stk.cl_stk) AS site_invtry, SUM(stk.near_exp) AS near_exp, SUM(stk.cl_stk)-SUM(stk.near_exp) AS long_exp,"
					+ " ROUND(SUM(stk.cl_val)/100000,2) AS site_invtval, ROUND(SUM(stk.nrexp_val)/100000,2) AS nrexp_val, ROUND((SUM(stk.cl_val)-SUM(stk.nrexp_val))/100000,2) AS long_expval, "
					+ "ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) AS avg_rate, s.city||'-Invtry Qty '||SUM(stk.cl_stk)||'('||SUM(stk.near_exp)||') -Rate :'|| (CASE WHEN SUM(stk.cl_stk)=0 THEN 0 ELSE "
					+ "ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) END) AS q_remark, s.city||'-Invtry Val '||ROUND(SUM(stk.cl_val)/100000,2)||'L ('||ROUND(SUM(stk.nrexp_val)/100000,2)||'L) -Rate :'|| (CASE WHEN SUM(stk.cl_stk)=0 "
					+ "THEN 0 ELSE ROUND(SUM(stk.cl_val)/SUM(stk.cl_stk),2) END) AS v_remark, s.city||'-Near Exp Qty '||SUM(stk.near_exp)||' -Rate :'|| (CASE WHEN SUM(stk.near_exp)=0"
					+ " THEN 0 ELSE ROUND(SUM(stk.nrexp_val)/SUM(stk.near_exp),2) END) AS nq_remark, s.city||'-Near Exp Val '||ROUND(SUM(stk.nrexp_val)/100000,2)||'L -Rate :'|| "
					+ "(CASE WHEN SUM(stk.near_exp)=0 THEN 0 ELSE ROUND(SUM(stk.nrexp_val)/SUM(stk.near_exp),2) END) AS nv_remark FROM  appl.SITE s, 	stk WHERE s.site_code= stk.site_code GROUP BY s.state_code,s.city ) a"
					+ " WHERE  st.count_code='IND' AND st.internal_code IS NOT NULL AND trim(st.state_code)=trim(a.state_code(+)) ORDER BY code,city";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			count = 0;
			previousStateId = "";
			toolTextStr = new StringBuffer(); 
			siteInvtry = 0;
			stateId =""; 
			maxValue = Collections.max(maxValList);
			maxValue = Double.parseDouble(df.format(maxValue));
			System.out.println("maxValue------->"+df.format(maxValue));
			while (rs.next())
			{
				
				stateId = rs.getString("CODE");
				
				if(!previousStateId.equals(stateId))
				{
					
					toolTextStr = new StringBuffer();
					siteInvtry= 0;
					rowData = new JSONObject();	
					if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory")) //1
					{
						System.out.println("Qty Invtentory Block");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_INVT"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_INVT_REM")));
						rowData.put("maxval", maxValue); 
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales")) //2
					{
						System.out.println("Qty Sales Block");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_SALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_SALES_REM")));
						rowData.put("maxval", maxValue); 
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_SALES");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >>  Avg Sales")) //3
					{
						System.out.println("Qty Inventory >> Avg Sales");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_AVGSALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_AVGSALES_REM")));
						rowData.put("maxval", maxValue); 
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_AVGSALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_AVGSALES");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry >> Inventory"))  //4
					{
						System.out.println("Qty Inventory >> Near Expiry");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_INVTNR"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_INVTNR_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_INVTNR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVTNR");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry"))  //5
					{
						System.out.println("Qty Near Expiry");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_NR"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_NR_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_NR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_NR");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >> Pending Orders"))  //6
					{
						System.out.println("Qty Inventory >> Pending Order");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_INVT_PENDORD"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_INVT_PENDORD_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_INVT_PENDORD_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT_PENDORD");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales Return >> Sales"))  //7
					{
						System.out.println("Qty Sales Return >> Sales");
						rowData.put(""+rs.getString("CODE"), rs.getString("Q_SR_SALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("Q_SR_SALES_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("Q_SR_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_SR_SALES");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory")) //8
					{
						System.out.println("Val Invtentory Block");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_INVT"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_INVT_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales")) //9
					{
						System.out.println("Val Sales Block");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_SALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_SALES_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_SALES");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >>  Avg Sales")) //10
					{
						System.out.println("Val Inventory >> Avg Sales");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_AVGSALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_AVGSALES_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_AVGSALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_AVGSALES");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry >> Inventory"))  //11
					{
						System.out.println("Val Inventory >> Near Expiry");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_INVTNR"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_INVTNR_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_INVTNR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVTNR");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry"))  //12
					{
						System.out.println("Val Near Expiry");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_NR"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_NR_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_NR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_NR");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >> Pending Orders"))  //13
					{
						System.out.println("Val Inventory >> Pending Order");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_INVT_PENDORD"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_INVT_PENDORD_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_INVT_PENDORD_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT_PENDORD");
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales Return >> Sales"))  //14
					{
						System.out.println("Val Sales Return >> Sales");
						rowData.put(""+rs.getString("CODE"), rs.getString("V_SR_SALES"));
						rowData.put("tooltextlbl", checkNull(rs.getString("V_SR_SALES_REM")));
						rowData.put("maxval", maxValue);
						rowData.put("avgval", avgValue);
						toolTextStr.append(checkNull(rs.getString("V_SR_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_SR_SALES");
					}
					
					rowData.put("near_exp", "0");
					rowData.put("long_exp", "100");
					previousStateId = rs.getString("CODE");
					rawDataIndiaMapJson.put(count, rowData);
					count++;
					
				}else if(previousStateId.equals(stateId))
				{
					if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory")) //1
					{
						System.out.println("Qty Invtentory Block");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales"))  //2
					{
						System.out.println("Qty Sales Block");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_SALES");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >>  Avg Sales")) //3
					{
						System.out.println("Qty Inventory >> Avg Sales");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_AVGSALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_AVGSALES");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry >> Inventory")) //4
					{
						System.out.println("Qty Inventory >> Near Expiry");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_INVTNR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVTNR");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Near Expiry"))  //5
					{
						System.out.println("Qty Near Expiry");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_NR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_NR");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Inventory >> Pending Orders"))  //6
					{
						System.out.println("Qty Inventory >> Pending Order");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_INVT_PENDORD_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_INVT_PENDORD");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Qty : Sales Return >> Sales"))  //7
					{
						System.out.println("Qty Sales Return >> Sales");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("Q_SR_SALES_REM")));
						siteInvtry = siteInvtry + rs.getDouble("Q_SR_SALES");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory")) //8
					{
						System.out.println("Val Invtentory Block");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales")) //9
					{
						System.out.println("Val Sales Block");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >>  Avg Sales")) //10
					{
						System.out.println("Val Inventory >> Avg Sales");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry >> Inventory"))  //11
					{
						System.out.println("Val Inventory >> Near Expiry");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVTNR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVTNR");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Near Expiry"))  //12
					{
						System.out.println("Val Near Expiry");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_NR_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_NR");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Inventory >> Pending Orders"))  //13
					{
						System.out.println("Val Inventory >> Pending Order");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					else if(viewOptionSelectd.trim().equalsIgnoreCase("Val : Sales Return >> Sales"))  //14
					{
						System.out.println("Val Sales Return >> Sales");
						toolTextStr.append("{br}");
						toolTextStr.append(checkNull(rs.getString("V_INVT_REM")));
						siteInvtry = siteInvtry + rs.getDouble("V_INVT");
						rowData.put(""+rs.getString("CODE"), ""+siteInvtry);
						rowData.put("tooltextlbl", ""+toolTextStr);
					}
					rawDataIndiaMapJson.put(count, rowData);
				}
			}
			
			System.out.println("rawDataIndiaMapJson-------->"+rawDataIndiaMapJson.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 :StateWiseStock.getIndiaMap(String dataSourceName, String month, String brand):" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataIndiaMapJson;
	}
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}	
}
